"-- DO NOT EDIT THIS FILE. IT IS AUTOGENERATED."
"-- \n"
"-- FILE: \n"
"-- table-create.sql \n"
"-- \n"
"-- FUNCTION: \n"
"-- Define the tables needed to initialize a new GnuCash database \n"
"-- \n"
"-- These tables roughly mirror the c structs in  \n"
"-- TransactionP.h,  AccountP.h, gnc-commodity.c \n"
"-- Please refer to the C files to get the right level of documentation. \n"
"-- \n"
"-- If these tables are changed or added to, a correspionding \n"
"-- audit-trail table (in table-audit.sql) must be updated as well. \n"
"-- \n"
"-- These tables are specifically designed for the  \n"
"-- postgres database server, but are hopefull relatively portable. \n"
"-- \n"
"-- These tables are hand-built, but maybe they should be  \n"
"-- auto-built with the m4 macros ... \n"
"-- \n"
"-- HISTORY: \n"
"-- Copyright (C) 2000, 2001 Linas Vepstas \n"
"-- \n"
" \n"
"CREATE TABLE gncVersion ( \n"
"	major	INT NOT NULL, \n"
"	minor	INT NOT NULL, \n"
"	rev	INT DEFAULT '0', \n"
"	name	TEXT UNIQUE NOT NULL CHECK (name <> ''), \n"
"	date	TIMESTAMP DEFAULT 'NOW' \n"
"); \n"
" \n"
"-- Commodity structure \n"
"-- Store currency, security types.  Namespace includes \n"
"-- ISO4217 for currencies, NASDAQ, AMEX, NYSE, EUREX for  \n"
"-- stocks.   See the C documentation for details. \n"
" \n"
"CREATE TABLE gncCommodity ( \n"
"        commodity	TEXT PRIMARY KEY, \n"
"	fullname	TEXT, \n"
"	namespace	TEXT NOT NULL, \n"
"	mnemonic	TEXT NOT NULL, \n"
"	code		TEXT, \n"
"	fraction	INT DEFAULT '100' \n"
"); \n"
" \n"
"CREATE TABLE gncBook ( \n"
"	bookGuid	CHAR(32) PRIMARY KEY, \n"
"	book_open	CHAR DEFAULT 'n', \n"
"	version		INT4 NOT NULL, \n"
"	iguid		INT4 DEFAULT 0 \n"
"); \n"
" \n"
"-- Account structure -- parentGUID points to parent account \n"
"-- guid. There is no supports for Groups in this schema. \n"
"-- (there seems to be no strong need to have groups in the DB.) \n"
" \n"
"CREATE TABLE gncAccount ( \n"
"	accountGuid	CHAR(32) PRIMARY KEY, \n"
"	parentGuid	CHAR(32) NOT NULL, \n"
"	bookGuid	CHAR(32) NOT NULL, \n"
"	accountName 	TEXT NOT NULL CHECK (accountName <> ''), \n"
"	accountCode 	TEXT, \n"
"	description 	TEXT, \n"
"	type		TEXT NOT NULL, \n"
"	commodity	TEXT NOT NULL CHECK (commodity <>''), \n"
"	version		INT4 NOT NULL, \n"
"	iguid		INT4 DEFAULT 0 \n"
"); \n"
" \n"
"-- CREATE INDEX gncAccount_pg_idx ON gncAccount (parentGuid); \n"
" \n"
"CREATE TABLE gncTransaction ( \n"
"	transGuid	CHAR(32) PRIMARY KEY, \n"
"	last_modified 	TIMESTAMP DEFAULT 'NOW', \n"
"	date_entered 	TIMESTAMP, \n"
"	date_posted 	TIMESTAMP, \n"
"	num		TEXT, \n"
"	description	TEXT, \n"
"        currency	TEXT NOT NULL CHECK (currency <> ''), \n"
"	version		INT4 NOT NULL, \n"
"	iguid		INT4 DEFAULT 0 \n"
"); \n"
" \n"
"CREATE INDEX gncTransaction_posted_idx ON gncTransaction (date_posted); \n"
" \n"
"-- a gncEntry is what we call 'Split' elsewhere in the engine \n"
"-- Here, we call it a 'journal entry' \n"
" \n"
"CREATE TABLE gncEntry ( \n"
"	entryGuid		CHAR(32) PRIMARY KEY, \n"
"	accountGuid		CHAR(32) NOT NULL, \n"
"	transGuid		CHAR(32) NOT NULL, \n"
"	memo			TEXT, \n"
"	action			TEXT, \n"
"	reconciled		CHAR DEFAULT 'n', \n"
"	date_reconciled 	TIMESTAMP, \n"
"	amount			INT8 DEFAULT '0', \n"
"	value			INT8 DEFAULT '0', \n"
"	iguid			INT4 DEFAULT 0 \n"
"); \n"
" \n"
"CREATE INDEX gncEntry_acc_idx ON gncEntry (accountGuid); \n"
"CREATE INDEX gncEntry_trn_idx ON gncEntry (transGuid); \n"
" \n"
"-- The checkpoint table provides balance information \n"
"-- The balance is provided in the indicated currency;  \n"
"-- this allows the potential of maintaining balance information \n"
"-- in multiple currencies.   \n"
"-- (e.g. report stock account balances in shares of stock,  \n"
"-- and in dollars) \n"
"-- the 'type' field indicates what type of balance this is \n"
"-- (simple, FIFO, LIFO, or other accounting method) \n"
" \n"
"CREATE TABLE gncCheckpoint ( \n"
"	accountGuid		CHAR(32) NOT NULL, \n"
"	date_start	 	TIMESTAMP NOT NULL, \n"
" 	date_end	 	TIMESTAMP NOT NULL, \n"
"	commodity		TEXT NOT NULL CHECK (commodity <>''), \n"
"	type			TEXT DEFAULT 'simple', \n"
"	balance			INT8 DEFAULT '0', \n"
"	cleared_balance		INT8 DEFAULT '0', \n"
"	reconciled_balance	INT8 DEFAULT '0', \n"
" \n"
"        PRIMARY KEY (accountGuid, date_start, commodity) \n"
"); \n"
" \n"
"-- The price table stores the price of 'commodity' valued \n"
"-- in units of 'currency' \n"
"CREATE TABLE gncPrice ( \n"
"	priceGuid	CHAR(32) PRIMARY KEY, \n"
"	commodity	TEXT NOT NULL CHECK (commodity <>''), \n"
"	currency	TEXT NOT NULL CHECK (commodity <>''), \n"
"	time		TIMESTAMP, \n"
"	source		TEXT, \n"
"	type		TEXT, \n"
"	valueNum	INT8 DEFAULT '0', \n"
"	valueDenom	INT4 DEFAULT '100', \n"
"	version		INT4 NOT NULL, \n"
"	bookGuid	CHAR(32) NOT NULL \n"
"); \n"
" \n"
" \n"
"-- The session directory serves several purposes.  First and formost, \n"
"-- it notes the database access type.  There are three modes: \n"
"--  o 'Single User' -- Only one user can have access to the database \n"
"--                     at a time.  \n"
"--  o 'Multi-User Polled' -- multiple users \n"
"--  o 'Muilti-User Event Driven' \n"
"--  See Design.txt for more info. \n"
"-- Note that a client can lie about its identity, sign-on time, etc. \n"
"-- so these records aren't really sufficient for a true audit. \n"
" \n"
"CREATE TABLE gncSession ( \n"
"	sessionGuid		CHAR(32) PRIMARY KEY, \n"
"	session_mode		CHAR(16) NOT NULL, \n"
"	hostname		TEXT, \n"
"	login_name		TEXT, \n"
"	gecos			TEXT, \n"
"	time_on			TIMESTAMP NOT NULL, \n"
"	time_off		TIMESTAMP NOT NULL DEFAULT 'INFINITY' \n"
"); \n"
" \n"
" \n"
"-- The kvp path-cache replaces a long path name with a single unique \n"
"-- number.  The guid-cache replaces a 32-byte guid with a shorter  \n"
"-- 4-byte identifier.  The KVP Value table stores the actual values. \n"
" \n"
"CREATE TABLE gncPathCache ( \n"
"	ipath		SERIAL PRIMARY KEY, \n"
"	path		TEXT \n"
"); \n"
" \n"
"CREATE SEQUENCE gnc_iguid_seq START 1; \n"
" \n"
"CREATE TABLE gncKVPvalue ( \n"
"	iguid		INT4, \n"
"	ipath		INT4, \n"
"	type		char(4), \n"
" \n"
"        PRIMARY KEY (iguid, ipath) \n"
"); \n"
" \n"
"--  CREATE INDEX gncKVPvalue_iguid_idx ON gncKVPvalue (iguid); \n"
" \n"
"-- Add primary keys to each kvp table ... because key inheritance  \n"
"-- is ambiguously defined and thus not implemented in postgres. \n"
"-- Note, however, adding these keys degrades performance by 20% \n"
"-- (even after a vacuum analyze), and adding indexes degrades \n"
"-- an additional 15% !!  I find this result surprising, so I \n"
"-- simply leave these commented out ... (as of postgres 7.1.2) \n"
"-- Note, indexex on the main, non-inherited tables *are* important \n"
"-- for ensuring good performance, so this effect seems to be related \n"
"-- to inheritance \n"
" \n"
"CREATE TABLE gncKVPvalue_int64 ( \n"
"	data		INT8 \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_int64_iguid_idx ON gncKVPvalue_int64 (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_dbl ( \n"
"	data		FLOAT8 \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_dbl_iguid_idx ON gncKVPvalue_dbl (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_numeric ( \n"
"	num		INT8, \n"
"	denom		INT8 \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_numeric_iguid_idx ON gncKVPvalue_numeric (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_str ( \n"
"	data		TEXT \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_str_iguid_idx ON gncKVPvalue_str (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_guid ( \n"
"	data		CHAR(32) \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_guid_iguid_idx ON gncKVPvalue_guid (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_timespec ( \n"
"	data		TIMESTAMP \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_timespec_iguid_idx ON gncKVPvalue_timespec (iguid); \n"
" \n"
"CREATE TABLE gncKVPvalue_list ( \n"
"	data		TEXT[] \n"
"--        PRIMARY KEY (iguid, ipath) \n"
") INHERITS (gncKVPvalue); \n"
" \n"
"-- CREATE INDEX gncKVPvalue_list_iguid_idx ON gncKVPvalue_list (iguid); \n"
" \n"
"-- end of file";