Difference between revisions of "SQL"

From GnuCash
Jump to: navigation, search
(Change "SQLite3" to "src/backends/sql"; add GNCLOCK table schema)
(Add ENTRIES table)
Line 109: Line 109:
 
     value_num          integer NOT NULL,
 
     value_num          integer NOT NULL,
 
     value_denom        integer NOT NULL
 
     value_denom        integer NOT NULL
 +
);
 +
CREATE TABLE entries (
 +
    guid text(32) PRIMARY KEY NOT NULL,
 +
    date text(14) NOT NULL,
 +
    date_entered text(14),
 +
    description text(2048),
 +
    action text(2048),
 +
    notes text(2048),
 +
    quantity_num bigint,
 +
    quantity_denom bigint,
 +
    i_acct text(32),
 +
    i_price_num bigint,
 +
    i_price_denom bigint,
 +
    i_discount_num bigint,
 +
    i_discount_denom bigint,
 +
    invoice text(32),
 +
    i_disc_type text(2048),
 +
    i_disc_how text(2048),
 +
    i_taxable integer,
 +
    i_taxincluded integer,
 +
    i_taxtable text(32),
 +
    b_acct text(32),
 +
    b_price_num bigint,
 +
    b_price_denom bigint,
 +
    bill text(32),
 +
    b_taxable integer,
 +
    b_taxincluded integer,
 +
    b_taxtable text(32),
 +
    b_paytype integer,
 +
    billable integer,
 +
    billto_type integer,
 +
    billto_guid text(32),
 +
    order_guid text(32)
 
);
 
);
 
CREATE TABLE books (
 
CREATE TABLE books (

Revision as of 20:35, 5 December 2010

This page is for information about a SQL backend for GnuCash.

Here is the data model created by src/backends/SQL:

CREATE TABLE gnc_lock (
    Hostname varchar(255), 
    PID int
);
CREATE TABLE versions (
    table_name      text(50),
    table_version   integer
);
CREATE TABLE transactions (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    currency_guid   CHAR(32) NOT NULL,
    num             text(2048) NOT NULL,
    post_date       CHAR(14) NOT NULL,
    enter_date      CHAR(14) NOT NULL,
    description     text(2048)
);
CREATE TABLE splits (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    tx_guid         CHAR(32) NOT NULL,
    account_guid    CHAR(32) NOT NULL,
    memo            text(2048) NOT NULL,
    action          text(2048) NOT NULL,
    reconcile_state text(1) NOT NULL,
    reconcile_date  CHAR(14) NOT NULL,
    value_num       integer NOT NULL,
    value_denom     integer NOT NULL,
    quantity_num    integer NOT NULL,
    quantity_denom  integer NOT NULL,
    lot_guid        CHAR(32)
);
CREATE TABLE lots (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    account_guid    CHAR(32),
    is_closed       integer NOT NULL
);
CREATE TABLE budgets (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    name            text(2048) NOT NULL,
    description     text(2048),
    num_periods     integer NOT NULL
);
CREATE TABLE recurrences (
    obj_guid                CHAR(32) NOT NULL,
    recurrence_mult         integer NOT NULL,
    recurrence_period_type  text(2048) NOT NULL,
    recurrence_period_start CHAR(8) NOT NULL
);
CREATE TABLE commodities (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    namespace       text(2048) NOT NULL,
    mnemonic        text(2048) NOT NULL,
    fullname        text(2048),
    cusip           text(2048),
    fraction        integer NOT NULL,
    quote_flag      integer NOT NULL,
    quote_source    text(2048),
    quote_tz        text(2048)
);
CREATE TABLE accounts (
    guid            CHAR(32) PRIMARY KEY NOT NULL,
    name            text(2048) NOT NULL,
    account_type    text(2048) NOT NULL,
    commodity_guid  CHAR(32) NOT NULL,
    commodity_scu   integer NOT NULL,
    non_std_scu     integer NOT NULL,
    parent_guid     CHAR(32),
    code            text(2048),
    description     text(2048)
);
CREATE TABLE slots (
    obj_guid            CHAR(32) NOT NULL,
    name                text(4096) NOT NULL,
    slot_type           integer NOT NULL,
    int64_val           integer,
    string_val          text(4096),
    double_val          real,
    timespec_val        CHAR(14),
    guid_val            CHAR(32),
    numeric_val_num     integer,
    numeric_val_denom   integer
);
CREATE TABLE schedxactions (
    guid                CHAR(32) PRIMARY KEY NOT NULL,
    name                text(2048),
    enabled             integer NOT NULL,
    start_date          CHAR(8) NOT NULL,
    last_occur          CHAR(8),
    num_occur           integer NOT NULL,
    rem_occur           integer NOT NULL,
    auto_create         integer NOT NULL,
    auto_notify         integer NOT NULL,
    adv_creation        integer NOT NULL,
    adv_notify          integer NOT NULL,
    instance_count      integer NOT NULL,
    template_act_guid   CHAR(32) NOT NULL
);
CREATE TABLE prices (
    guid                CHAR(32) PRIMARY KEY NOT NULL,
    commodity_guid      CHAR(32) NOT NULL,
    currency_guid       CHAR(32) NOT NULL,
    date                CHAR(14) NOT NULL,
    source              text(2048),
    type                text(2048),
    value_num           integer NOT NULL,
    value_denom         integer NOT NULL
);
CREATE TABLE entries (
     guid text(32) PRIMARY KEY NOT NULL,
     date text(14) NOT NULL,
     date_entered text(14),
     description text(2048),
     action text(2048),
     notes text(2048),
     quantity_num bigint,
     quantity_denom bigint,
     i_acct text(32),
     i_price_num bigint,
     i_price_denom bigint,
     i_discount_num bigint,
     i_discount_denom bigint,
     invoice text(32),
     i_disc_type text(2048),
     i_disc_how text(2048),
     i_taxable integer,
     i_taxincluded integer,
     i_taxtable text(32),
     b_acct text(32),
     b_price_num bigint,
     b_price_denom bigint,
     bill text(32),
     b_taxable integer,
     b_taxincluded integer,
     b_taxtable text(32),
     b_paytype integer,
     billable integer,
     billto_type integer,
     billto_guid text(32),
     order_guid text(32)
);
CREATE TABLE books (
    guid                CHAR(32) PRIMARY KEY NOT NULL,
    root_account_guid   CHAR(32) NOT NULL,
    root_template_guid  CHAR(32) NOT NULL
);