-- This file is auto-generated by rst-literals. Do not modify.

CREATE TABLE security (

  -- Unique symbol, type and a name/description.
  symbol VARCHAR(16),
  name TEXT NOT NULL,

  PRIMARY KEY (symbol)
);


CREATE TABLE account (

  -- A unique and a long name/description.
  id SERIAL,
  name TEXT,

  -- The parent account to which this account belongs.
  parent_id INTEGER REFERENCES account(id),

  -- The security that this account is denominated in.
  sec VARCHAR(16) NOT NULL REFERENCES security(symbol),

  -- Whether the account is a credit (True) or debug (False)
  -- account.
  isdebit BOOLEAN,

  PRIMARY KEY (id),
  UNIQUE (parent_id, name)
);


CREATE TABLE transaction (

  -- A unique id to refer to the transaction.
  id SERIAL,

  -- An optional description that applies to the set of entries.
  description TEXT,

  -- A unqiue time for this set of transactions.
  timestamp TIMESTAMP WITHOUT TIME ZONE,

  PRIMARY KEY(id)
);


CREATE TABLE journal (

  -- A unique id to refer to the transaction.
  id SERIAL,

  -- Timestamp.
  timestamp TIMESTAMP WITHOUT TIME ZONE,

  -- The account to which this transaction belongs.
  account_id INTEGER NOT NULL REFERENCES account(id)
                              ON DELETE SET NULL,

  -- Which entry this item belongs to (possibly NULL).
  trans_id INTEGER DEFAULT NULL REFERENCES transaction(id)
                   ON DELETE SET NULL,

  -- The underlying currency or security.
  symbol VARCHAR(16) REFERENCES security(symbol),

  -- Amount of the transaction, in units of its currency.
  -- Note that the sign indicates whether to increase or decrease
  -- the owning account.
  amount NUMERIC(16, 6),

  -- Some text to identify this item. This is meant to be either
  -- imported from some data file or entered manually by the user.
  -- It may entirely be left empty, as it is not otherwise used by
  -- the system.
  description TEXT,
  memo TEXT,

  -- An optional unique GUID used at import time, to insure that
  -- journal entries may not be imported twice.
  guid VARCHAR(32) UNIQUE,

  PRIMARY KEY(id)
);


insert into security (symbol, name) values ('USD', 'US Dollar');
insert into security (symbol, name) values ('CAD', 'Canadian Dollar');
insert into security (symbol, name) values ('AUD', 'Australian Dollar');
insert into security (symbol, name) values ('JPY', 'Japanese Yen');

