Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: brmbar3
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 82 lines (60 sloc) 2.777 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81
CREATE SEQUENCE currencies_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE currencies (
id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('currencies_id_seq'::regclass),
name VARCHAR(128) NOT NULL,
UNIQUE(name)
);
INSERT INTO currencies (name) VALUES ('Kč');

CREATE TYPE exchange_rate_direction AS ENUM ('source_to_target', 'target_to_source');
CREATE TABLE exchange_rates (
valid_since TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,

target INTEGER NOT NULL,
        FOREIGN KEY (target) REFERENCES currencies (id),

source INTEGER NOT NULL,
        FOREIGN KEY (source) REFERENCES currencies (id),

rate DECIMAL(12,2) NOT NULL,
rate_dir exchange_rate_direction NOT NULL
);


-- brmbar users have 'debt' accounts; a negative balance means that
-- we have debt to the users, i.e. they are positive relative to the brmbar.
-- Positive balance means that the users have debt in brmbar.
-- Therefore, user's balance must always be shown negated.
CREATE SEQUENCE accounts_id_seq START WITH 1 INCREMENT BY 1;
CREATE TYPE account_type AS ENUM ('cash', 'debt', 'inventory', 'income', 'expense', 'starting_balance', 'ending_balance');
CREATE TABLE accounts (
id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('accounts_id_seq'::regclass),

name VARCHAR(128) NOT NULL,
UNIQUE (name),

currency INTEGER NOT NULL,
        FOREIGN KEY (currency) REFERENCES currencies (id),

acctype account_type NOT NULL
);
INSERT INTO accounts (name, currency, acctype) VALUES ('BrmBar Cash', (SELECT id FROM currencies WHERE name='Kč'), 'cash');
INSERT INTO accounts (name, currency, acctype) VALUES ('BrmBar Profits', (SELECT id FROM currencies WHERE name='Kč'), 'income');


CREATE SEQUENCE barcodes_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE barcodes (
barcode VARCHAR(128) PRIMARY KEY NOT NULL,

account INTEGER NOT NULL,
        FOREIGN KEY (account) REFERENCES accounts (id)
);


CREATE SEQUENCE transactions_id_seq START WITH 1 INCREMENT BY 1;
CREATE TABLE transactions (
id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('transactions_id_seq'::regclass),
time TIMESTAMP DEFAULT NOW() NOT NULL,

responsible INTEGER,
FOREIGN KEY (responsible) REFERENCES accounts (id),
-- FIXME: imperfect constraint to assure this is a user

description TEXT
);

CREATE SEQUENCE transaction_splits_id_seq START WITH 1 INCREMENT BY 1;
CREATE TYPE transaction_split_side AS ENUM ('credit', 'debit');
CREATE TABLE transaction_splits (
id INTEGER PRIMARY KEY NOT NULL DEFAULT NEXTVAL('transaction_splits_id_seq'::regclass),

transaction INTEGER NOT NULL,
FOREIGN KEY (transaction) REFERENCES transactions (id),

side transaction_split_side NOT NULL,

account INTEGER NOT NULL,
        FOREIGN KEY (account) REFERENCES accounts (id),
amount DECIMAL(12,2) NOT NULL,

memo TEXT
);
Something went wrong with that request. Please try again.