diff options
Diffstat (limited to 'fracture/db.py')
-rw-r--r-- | fracture/db.py | 43 |
1 files changed, 29 insertions, 14 deletions
diff --git a/fracture/db.py b/fracture/db.py index 2611ee9..56a268d 100644 --- a/fracture/db.py +++ b/fracture/db.py @@ -5,18 +5,25 @@ def create(dbname): c = conn.cursor() c.execute('''CREATE TABLE products - ( id INTEGER PRIMARY KEY, - invoice_id INTEGER, - description TEXT NOT NULL, - units REAL NOT NULL, - price_unit REAL NOT NULL, - vat REAL, - FOREIGN KEY(invoice_id) REFERENCES invoices(id) + ( invoice_id INTEGER, + invoice_type TEXT, + invoice_series INTEGER, + description TEXT NOT NULL, + units REAL NOT NULL, + price_unit REAL NOT NULL, + vat REAL, + PRIMARY KEY(description, + invoice_id, + invoice_type, + invoice_series) + FOREIGN KEY(invoice_id) REFERENCES invoices(id) + FOREIGN KEY(invoice_type) REFERENCES invoices(type) + FOREIGN KEY(invoice_series) REFERENCES invoices(series) ON DELETE CASCADE )''') # date is %Y-%m-%d c.execute('''CREATE TABLE invoices - ( id INTEGER PRIMARY KEY, + ( id INTEGER NOT NULL, type TEXT NOT NULL, series INTEGER NOT NULL, date TEXT NOT NULL, @@ -24,15 +31,23 @@ def create(dbname): customer_id TEXT, customer_name TEXT, - customer_address TEXT + customer_address TEXT, + PRIMARY KEY(type, series, id) )''') c.execute('''CREATE TABLE taxes - ( name TEXT NOT NULL, - invoice_id INTEGER, - ratio REAL, - PRIMARY KEY(name, invoice_id) - FOREIGN KEY(invoice_id) REFERENCES invoices(id) + ( name TEXT NOT NULL, + invoice_id INTEGER, + invoice_type TEXT, + invoice_series INTEGER, + ratio REAL, + PRIMARY KEY(name, + invoice_id, + invoice_type, + invoice_series) + FOREIGN KEY(invoice_id) REFERENCES invoices(id) + FOREIGN KEY(invoice_type) REFERENCES invoices(type) + FOREIGN KEY(invoice_series) REFERENCES invoices(series) ON DELETE CASCADE )''') conn.commit() |