Updated DDL for SQL backend

[Phil Longstaff]

I've attached an updated DDL for the proposed SQL backend.  I used MySQL
to test, and it creates the tables correctly.

Some notes:

1) This only covers the core objects i.e. the DDL is based on
src/backend/file/*-v2.c.

2) The namespace for the commodities table is just a string.  I'm unsure
about whether it should really be a table.  Currently, only 2 namespaces
are defined and they are enforced by GC.

3) All object ids are (or should be) GUIDs.  A GUID is represented by a
set of 4 int fields.   I use this instead of varbinary because it isn't
variable length.  I don't use any text type because I don't want any
issues with 0x00 as a char in a GUID.  Note that some tables DO have
just int primary keys, but they are ones (e.g. slots) which are really
part of another table.

4) All string fields have type 'text'.  This may be too wasteful of
space.  Are such things as maximum length of an account name segment
defined?

5) All numerics have 2 fields, xxx_num and xxx_denom.

6) I haven't looked too deeply into how a scheduled transaction is
stored in XML and how that reflects its internal structure.  It appears
as though there is a gnc:schedxaction which stores the scheduled
transaction, and that the transaction which is converted into a
scheduled transaction has slots attached to its splits.  I've redone
this by adding a sched_tx_splits table which is similar to the splits
table, except that the values are strings instead of numerics.

I'm taking on this project to scratch a few particular itches: to have
an SQL backend to speed up app startup and remove the need for saves.  I
plan to use a db abstraction layer (probably libdbi or libgda), but I
don't plan to test on more than mysql and SQLite.  I don't know which I
will use for "production" usage.  I will add transactions to provide
some data integrity, but I don't use a multi-user environment, and that
isn't my main goal.  I have a 'books' table which will have 1 row, and
which has an 'is_open' field which will serve as the lock for the
database to replace the current lck file.  The db abstraction *should*
allow a port to postgreSQL or other db's, but I don't plan to test them.
Since different dbs differ data types and support for features like
triggers, stored procedures, etc., I will target SQLite and mysql.  It
might be worthwhile to have a way of using these features if the
database supports them.  I'm happy to listen to design ideas on how to
do this.  I plan to allow the business backend to tie into the SQL
database in the same way that the XML backends tie together.  I will
implement the business backend, but it will not receive extensive
testing because I don't use the business piece of GC.  Note that I am
saying what I will/won't implement.  I'm happy to work with any other
volunteer who wants to join in and help with any piece.

Phil
-------------- next part --------------
# ver 0.03

-- Commodities table - stores currencies and stocks/mutual funds
CREATE TABLE commodities (
	commodity_id int NOT NULL,
	namespace text NOT NULL,
	mnemonic text NOT NULL,
	fullname text,
	cusip text,
	fraction int,
	quote_source text,
	quote_tz text,

	PRIMARY KEY(commodity_id)
);

-- Slots table to store all slots
CREATE TABLE slots (
	slot_id int NOT NULL,

-- What type of object, and what guid, does this slot belong to
	object_type int NOT NULL,
	obj_guid_1 int NOT NULL,
	obj_guid_2 int NOT NULL,
	obj_guid_3 int NOT NULL,
	obj_guid_4 int NOT NULL,

-- Slot type and value.
	slot_type int NOT NULL,
	int64_val int(64),
	string_val text,
	timespec_val date,
	guid_val_1 int,
	guid_val_2 int,
	guid_val_3 int,
	guid_val_4 int,
	num_val_num int,
	num_val_denom int,
	double_val double,
	binary_val blob,

-- Parent_id provides the tie to the parent for either a member of a list or subframe
	parent_id int,

	PRIMARY KEY(slot_id),
	CONSTRAINT FOREIGN KEY(parent_id) REFERENCES slots(slot_id)
);

-- Lots are tied to accounts.  The contents are slots
CREATE TABLE lots (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,

	account_guid_1 int NOT NULL,
	account_guid_2 int NOT NULL,
	account_guid_3 int NOT NULL,
	account_guid_4 int NOT NULL,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(account_guid_1,account_guid_2,account_guid_3,account_guid_4) REFERENCES accounts(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE accounts (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	name text NOT NULL,
	account_type_id int NOT NULL,
	commodity_id int NOT NULL,
	parent_guid_1 int,
	parent_guid_2 int,
	parent_guid_3 int,
	parent_guid_4 int,
	code text,
	description text,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(parent_guid_1,parent_guid_2,parent_guid_3,parent_guid_4) REFERENCES accounts(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(commodity_id) REFERENCES commodities(commodity_id)
);

CREATE TABLE budgets (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	name text NOT NULL,
	description text,
	num_periods int NOT NULL,
	recur_mult int NOT NULL,
	recur_type int NOT NULL,
	recur_date date NOT NULL,
	
	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE prices (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	commodity_id int NOT NULL,
	currency_id int NOT NULL,
	time date NOT NULL,
	source text,
	type text,
	value_num int,
	value_denom int,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(commodity_id) REFERENCES commodities(commodity_id),
	CONSTRAINT FOREIGN KEY(currency_id) REFERENCES commodities(commodity_id)
);

CREATE TABLE freq_specs (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,

	fs_type int,
	fs_interval int,
	fs_offset int,
	day int,
	occurrence int,

# Parent used for composite fs's
	parent_guid_1 int,
	parent_guid_2 int,
	parent_guid_3 int,
	parent_guid_4 int,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(parent_guid_1,parent_guid_2,parent_guid_3,parent_guid_4) REFERENCES freq_specs(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE sched_transactions (
	sched_tx_id int NOT NULL,
	name text NOT NULL,
	autocreate boolean,
	notify boolean,
	create_days_in_advance int,
	remind_days_in_advance int,
	instance_count int,
	start_date date,
	last_occur_date date,
	num_occur int,
	rem_occur int,
	end_date date,

	fs_guid_1 int,
	fs_guid_2 int,
	fs_guid_3 int,
	fs_guid_4 int,

	PRIMARY KEY(sched_tx_id),
	CONSTRAINT FOREIGN KEY(fs_guid_1,fs_guid_2,fs_guid_3,fs_guid_4) REFERENCES freq_specs(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE sched_tx_deferred_instances (
	id int NOT NULL,
	sched_tx_id int NOT NULL,
	last_date date,
	num_occur_rem int,
	num_inst int,

	PRIMARY KEY(id),
	CONSTRAINT FOREIGN KEY(sched_tx_id) REFERENCES sched_transactions(sched_tx_id)
);

CREATE TABLE sched_tx_splits (
	sched_tx_split_id int NOT NULL,
	sched_tx_id int NOT NULL,
	memo text,
	action text,
	account_guid_1 int NOT NULL,
	account_guid_2 int NOT NULL,
	account_guid_3 int NOT NULL,
	account_guid_4 int NOT NULL,
	value text,

	PRIMARY KEY(sched_tx_split_id),
	CONSTRAINT FOREIGN KEY(sched_tx_id) REFERENCES sched_transations(sched_tx_id),
	CONSTRAINT FOREIGN KEY(account_guid_1,account_guid_2,account_guid_3,account_guid_4) REFERENCES accounts(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE transactions (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	currency_id int NOT NULL,
	num text,
	post_date date,
	enter_date date,
	description text,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(currency_id) REFERENCES commodities(commodity_id)
);

CREATE TABLE splits (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	trans_guid_1 int NOT NULL,
	trans_guid_2 int NOT NULL,
	trans_guid_3 int NOT NULL,
	trans_guid_4 int NOT NULL,
	memo text,
	action text,
	reconcile_state char NOT NULL,
	reconcile_date date,
	value_num int NOT NULL,
	value_denom int NOT NULL,
	quantity_num int NOT NULL,
	quantity_denom int NOT NULL,
	account_guid_1 int NOT NULL,
	account_guid_2 int NOT NULL,
	account_guid_3 int NOT NULL,
	account_guid_4 int NOT NULL,

	lot_guid_1 int,
	lot_guid_2 int,
	lot_guid_3 int,
	lot_guid_4 int,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(trans_guid_1,trans_guid_2,trans_guid_3,trans_guid_4) REFERENCES transactions(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(account_guid_1,account_guid_2,account_guid_3,account_guid_4) REFERENCES accounts(guid_1,guid_2,guid_3,guid_4),
	CONSTRAINT FOREIGN KEY(lot_guid_1,lot_guid_2,lot_guid_3,lot_guid_4) REFERENCES lots(guid_1,guid_2,guid_3,guid_4)
);

CREATE TABLE books (
	guid_1 int NOT NULL,
	guid_2 int NOT NULL,
	guid_3 int NOT NULL,
	guid_4 int NOT NULL,
	is_open boolean,

	PRIMARY KEY(guid_1,guid_2,guid_3,guid_4)
);