3
"-- table-create.sql \n"
6
"-- Define the tables needed to initialize a new GnuCash database \n"
8
"-- These tables roughly mirror the c structs in \n"
9
"-- TransactionP.h, AccountP.h, gnc-commodity.c \n"
10
"-- Please refer to the C files to get the right level of documentation. \n"
12
"-- If these tables are changed or added to, a correspionding \n"
13
"-- audit-trail table (in table-audit.sql) must be updated as well. \n"
15
"-- These tables are specifically designed for the \n"
16
"-- postgres database server, but are hopefull relatively portable. \n"
18
"-- These tables are hand-built, but maybe they should be \n"
19
"-- auto-built with the m4 macros ... \n"
22
"-- Copyright (C) 2000, 2001 Linas Vepstas \n"
25
"CREATE TABLE gncVersion ( \n"
26
" major INT NOT NULL, \n"
27
" minor INT NOT NULL, \n"
28
" rev INT DEFAULT '0', \n"
29
" name TEXT UNIQUE NOT NULL CHECK (name <> ''), \n"
30
" date TIMESTAMP WITH TIME ZONE DEFAULT 'NOW' \n"
33
"-- Commodity structure \n"
34
"-- Store currency, security types. Namespace includes \n"
35
"-- ISO4217 for currencies, NASDAQ, AMEX, NYSE, EUREX for \n"
36
"-- stocks. See the C documentation for details. \n"
38
"CREATE TABLE gncCommodity ( \n"
39
" commodity TEXT PRIMARY KEY, \n"
41
" namespace TEXT NOT NULL, \n"
42
" mnemonic TEXT NOT NULL, \n"
44
" fraction INT DEFAULT '100' \n"
47
"CREATE TABLE gncBook ( \n"
48
" bookGuid CHAR(32) PRIMARY KEY, \n"
49
" book_open CHAR DEFAULT 'n', \n"
50
" version INT4 NOT NULL, \n"
51
" iguid INT4 DEFAULT 0 \n"
54
"-- Account structure -- parentGUID points to parent account \n"
55
"-- guid. There is no supports for Groups in this schema. \n"
56
"-- (there seems to be no strong need to have groups in the DB.) \n"
58
"CREATE TABLE gncAccount ( \n"
59
" accountGuid CHAR(32) PRIMARY KEY, \n"
60
" parentGuid CHAR(32) NOT NULL, \n"
61
" bookGuid CHAR(32) NOT NULL, \n"
62
" accountName TEXT NOT NULL CHECK (accountName <> ''), \n"
63
" accountCode TEXT, \n"
64
" description TEXT, \n"
65
" type TEXT NOT NULL, \n"
66
" commodity TEXT NOT NULL CHECK (commodity <>''), \n"
67
" version INT4 NOT NULL, \n"
68
" iguid INT4 DEFAULT 0 \n"
71
"CREATE TABLE gncTransaction ( \n"
72
" transGuid CHAR(32) PRIMARY KEY, \n"
73
" last_modified TIMESTAMP WITH TIME ZONE DEFAULT 'NOW', \n"
74
" date_entered TIMESTAMP WITH TIME ZONE, \n"
75
" date_posted TIMESTAMP WITH TIME ZONE, \n"
77
" description TEXT, \n"
78
" currency TEXT NOT NULL CHECK (currency <> ''), \n"
79
" version INT4 NOT NULL, \n"
80
" iguid INT4 DEFAULT 0 \n"
83
"-- a gncSplit is what we call 'Split' elsewhere in the engine \n"
84
"-- Here, we call it a 'journal split' \n"
86
"CREATE TABLE gncSplit ( \n"
87
" splitGuid CHAR(32) PRIMARY KEY, \n"
88
" accountGuid CHAR(32) NOT NULL, \n"
89
" transGuid CHAR(32) NOT NULL, \n"
92
" reconciled CHAR DEFAULT 'n', \n"
93
" date_reconciled TIMESTAMP WITH TIME ZONE, \n"
94
" amount INT8 DEFAULT '0', \n"
95
" value INT8 DEFAULT '0', \n"
96
" iguid INT4 DEFAULT 0 \n"
99
"-- The checkpoint table provides balance information \n"
100
"-- The balance is provided in the indicated currency; \n"
101
"-- this allows the potential of maintaining balance information \n"
102
"-- in multiple currencies. \n"
103
"-- (e.g. report stock account balances in shares of stock, \n"
104
"-- and in dollars) \n"
105
"-- the 'type' field indicates what type of balance this is \n"
106
"-- (simple, FIFO, LIFO, or other accounting method) \n"
108
"CREATE TABLE gncCheckpoint ( \n"
109
" accountGuid CHAR(32) NOT NULL, \n"
110
" date_start TIMESTAMP WITH TIME ZONE NOT NULL, \n"
111
" date_end TIMESTAMP WITH TIME ZONE NOT NULL, \n"
112
" commodity TEXT NOT NULL CHECK (commodity <>''), \n"
113
" type TEXT DEFAULT 'simple', \n"
114
" balance INT8 DEFAULT '0', \n"
115
" cleared_balance INT8 DEFAULT '0', \n"
116
" reconciled_balance INT8 DEFAULT '0', \n"
118
" PRIMARY KEY (accountGuid, date_start, commodity) \n"
121
"-- The price table stores the price of 'commodity' valued \n"
122
"-- in units of 'currency' \n"
123
"CREATE TABLE gncPrice ( \n"
124
" priceGuid CHAR(32) PRIMARY KEY, \n"
125
" bookGuid CHAR(32) NOT NULL, \n"
126
" commodity TEXT NOT NULL CHECK (commodity <>''), \n"
127
" currency TEXT NOT NULL CHECK (commodity <>''), \n"
128
" time TIMESTAMP WITH TIME ZONE, \n"
131
" valueNum INT8 DEFAULT '0', \n"
132
" valueDenom INT4 DEFAULT '100', \n"
133
" version INT4 NOT NULL \n"
137
"-- The session directory serves several purposes. First and formost, \n"
138
"-- it notes the database access type. There are three modes: \n"
139
"-- o 'Single User' -- Only one user can have access to the database \n"
141
"-- o 'Multi-User Polled' -- multiple users \n"
142
"-- o 'Muilti-User Event Driven' \n"
143
"-- See Design.txt for more info. \n"
144
"-- Note that a client can lie about its identity, sign-on time, etc. \n"
145
"-- so these records aren't really sufficient for a true audit. \n"
147
"CREATE TABLE gncSession ( \n"
148
" sessionGuid CHAR(32) PRIMARY KEY, \n"
149
" session_mode CHAR(16) NOT NULL, \n"
151
" login_name TEXT, \n"
153
" time_on TIMESTAMP WITH TIME ZONE NOT NULL, \n"
154
" time_off TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT 'INFINITY' \n"
158
"-- The kvp path-cache replaces a long path name with a single unique \n"
159
"-- number. The guid-cache replaces a 32-byte guid with a shorter \n"
160
"-- 4-byte identifier. The KVP Value table stores the actual values. \n"
162
"CREATE TABLE gncPathCache ( \n"
163
" ipath SERIAL PRIMARY KEY, \n"
167
"CREATE SEQUENCE gnc_iguid_seq START 1; \n"
169
"CREATE TABLE gncKVPvalue ( \n"
174
" PRIMARY KEY (iguid, ipath) \n"
177
"-- Add primary keys to each kvp table ... because key inheritance \n"
178
"-- is ambiguously defined and thus not implemented in postgres. \n"
179
"-- Note, however, adding these keys degrades performance by 20% \n"
180
"-- (even after a vacuum analyze), and adding indexes degrades \n"
181
"-- an additional 15% !! I find this result surprising, so I \n"
182
"-- simply leave these commented out ... (as of postgres 7.1.2) \n"
183
"-- Note, indexex on the main, non-inherited tables *are* important \n"
184
"-- for ensuring good performance, so this effect seems to be related \n"
185
"-- to inheritance \n"
187
"CREATE TABLE gncKVPvalue_int64 ( \n"
189
"-- PRIMARY KEY (iguid, ipath) \n"
190
") INHERITS (gncKVPvalue); \n"
192
"CREATE TABLE gncKVPvalue_dbl ( \n"
194
"-- PRIMARY KEY (iguid, ipath) \n"
195
") INHERITS (gncKVPvalue); \n"
197
"CREATE TABLE gncKVPvalue_numeric ( \n"
200
"-- PRIMARY KEY (iguid, ipath) \n"
201
") INHERITS (gncKVPvalue); \n"
203
"CREATE TABLE gncKVPvalue_str ( \n"
205
"-- PRIMARY KEY (iguid, ipath) \n"
206
") INHERITS (gncKVPvalue); \n"
208
"CREATE TABLE gncKVPvalue_guid ( \n"
210
"-- PRIMARY KEY (iguid, ipath) \n"
211
") INHERITS (gncKVPvalue); \n"
213
"CREATE TABLE gncKVPvalue_timespec ( \n"
214
" data TIMESTAMP WITH TIME ZONE \n"
215
"-- PRIMARY KEY (iguid, ipath) \n"
216
") INHERITS (gncKVPvalue); \n"
218
"CREATE TABLE gncKVPvalue_list ( \n"
220
"-- PRIMARY KEY (iguid, ipath) \n"
221
") INHERITS (gncKVPvalue); \n"
223
"-- CREATE INDEX gncAccount_pg_idx ON gncAccount (parentGuid); \n"
224
"CREATE INDEX gncTransaction_posted_idx ON gncTransaction (date_posted); \n"
225
"CREATE INDEX gncSplit_acc_idx ON gncSplit (accountGuid); \n"
226
"CREATE INDEX gncSplit_trn_idx ON gncSplit (transGuid); \n"
228
"-- The indexes are commented out due to the performance degradation\n"
229
"-- they cause, based on the inheritance problems\n"
230
"-- CREATE INDEX gncKVPvalue_iguid_idx ON gncKVPvalue (iguid); \n"
231
"-- CREATE INDEX gncKVPvalue_int64_iguid_idx ON gncKVPvalue_int64 (iguid); \n"
232
"-- CREATE INDEX gncKVPvalue_dbl_iguid_idx ON gncKVPvalue_dbl (iguid); \n"
233
"-- CREATE INDEX gncKVPvalue_numeric_iguid_idx ON gncKVPvalue_numeric (iguid); \n"
234
"-- CREATE INDEX gncKVPvalue_str_iguid_idx ON gncKVPvalue_str (iguid); \n"
235
"-- CREATE INDEX gncKVPvalue_guid_iguid_idx ON gncKVPvalue_guid (iguid); \n"
236
"-- CREATE INDEX gncKVPvalue_timespec_iguid_idx ON gncKVPvalue_timespec (iguid); \n"
237
"-- CREATE INDEX gncKVPvalue_list_iguid_idx ON gncKVPvalue_list (iguid); \n"