SQL backend for GnuCash 2

[Phil Longstaff]

On Mon, 2006-23-10 at 22:38 -0400, Josh Sled wrote:
> It'll probably be the case that the SQL DDL statements will be easier to
> work with than these pictures.  Just plain text like...
> 
>     CREATE TABLE accounts
>     (
>       id int NOT NULL,
>       name String,
>       parent int NOT NULL, -- fk(Account.id)
>       [...]
>     )
> 
> ...is better.

I've attached a first cut at DDL for the core stuff.  It doesn't include
slots, lots, some sched txn stuff.  I based it on the code which writes
out XML.  I don't understand some of the interactions with pieces of the
sched txn stuff or lots.  The contents of slots seems to me to be object
dependent i.e budget slots will be different than account slots.  I also
haven't looked too much at the business stuff yet.  I imagine I will
tackle it in a similar manner to what I do now i.e. convert XML
definitions to table definitions.  However, I don't use the business
features, so won't really be able to test it.

Phil
-------------- next part --------------
CREATE TABLE account_types (
	account_type_id int NOT NULL,
	name string NOT NULL,

	PRIMARY KEY(account_type_id)
);


CREATE TABLE accounts (
	account_id int NOT NULL,
	name string NOT NULL,
	account_type_id int NOT NULL,
	commodity_id int NOT NULL,
	parent_id int,
	code string,
	description string,

	slots ???
	lots ???

	PRIMARY KEY(account_id),
	CONSTRAINT parent_id REFERENCES accounts(account_id),
	CONSTRAINT commodity_id REFERENCES commodities(commodity_id),
	CONSTRAINT type_id REFERENCES account_types(account_type_id)
);

CREATE TABLE budgets (
	budget_id int NOT NULL,
	name string NOT NULL,
	description string,
	num_periods int,
	
	recurrence ???
	slots ???

	PRIMARY KEY(budget_id)
);

CREATE TABLE commodities (
	commodity_id int NOT NULL,
	namespace_id int NOT NULL,
	mnemonic string NOT NULL,
	fullname string,
	cusip string,
	fraction int,
	quote_source string,
	quote_tz string,

	PRIMARY KEY(commodity_id),
	CONSTRAINT namespace_id REFERENCES namespaces(namespace_id)
);

CREATE TABLE namespaces (
	namespace_id int NOT NULL,
	name string NOT NULL,

	PRIMARY KEY(namespace_id)
);

CREATE TABLE prices (
	price_id int NOT NULL,
	commodity_id int NOT NULL,
	currency_id int NOT NULL,
	time date NOT NULL,
	source string,
	type string,
	value_num int,
	value_denom int,

	PRIMARY KEY(price_id),
	CONSTRAINT commodity_id REFERENCES commodities(commodity_id),
	CONSTRAINT currency_id REFERENCES commodities(commodity_id)
);

CREATE TABLE sched_transactions(
	sched_trans_id int NOT NULL,
	name string 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,

	template account ???
	freq spec ???
	deferred instances ???
	slots ???

	PRIMARY KEY(sched_trans_id)
);

CREATE TABLE transactions (
	trans_id int NOT NULL,
	currency_id int NOT NULL,
	num string,
	post_date date,
	enter_date date,
	description string,

	slots ???

	PRIMARY KEY(trans_id),
	CONSTRAINT currency_id REFERENCES commodities(commodity_id)
);

CREATE TABLE splits (
	split_id int NOT NULL,
	trans_id int NOT NULL,
	memo string,
	action string,
	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_id int NOT NULL,

	lots ????????
	slots ???????

	PRIMARY KEY(split_id),
	CONSTRAINT trans_id REFERENCES transactions(trans_id),
	CONSTRAINT account_id REFERENCES accounts(account_id)
);