Difference between revisions of "SQL"

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
);