summaryrefslogtreecommitdiff
path: root/fracture/db.py
blob: 56a268d8166f33c4b738df799645019afcc755c5 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
import sqlite3

def create(dbname):
    conn = sqlite3.connect(dbname)
    c = conn.cursor()

    c.execute('''CREATE TABLE products
                 ( 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 NOT NULL,
                   type     TEXT NOT NULL,
                   series   INTEGER NOT NULL,
                   date     TEXT NOT NULL,
                   notes    TEXT,

                   customer_id      TEXT,
                   customer_name    TEXT,
                   customer_address TEXT,
                   PRIMARY KEY(type, series, id)
                 )''')

    c.execute('''CREATE TABLE taxes
                 ( 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()
    conn.close()