C4BJCb3g

1package dbinit
2
3import "database/sql"
4
5const managersDDL = `
6CREATE TABLE IF NOT EXISTS managers
7(
8    id      INTEGER PRIMARY KEY AUTOINCREMENT,
9    name    TEXT    NOT NULL,
10    login   TEXT    NOT NULL UNIQUE,
11    password TEXT NOT NULL,
12    salary  INTEGER NOT NULL CHECK ( salary > 0 ),
13    plan    INTEGER NOT NULL CHECK ( plan >= 0 ),
14    unit 	TEXT,
15    boss_id INTEGER REFERENCES managers
16);`
17
18const productsDDL = `
19CREATE TABLE IF NOT EXISTS products
20(
21    id    INTEGER PRIMARY KEY AUTOINCREMENT,
22    name  TEXT    NOT NULL UNIQUE,
23    price INTEGER NOT NULL CHECK ( price > 0 ),
24    qty INTEGER NOT NULL CHECK ( qty > 0 )
25);`
26
27const salesDDL = `
28CREATE TABLE IF NOT EXISTS sales (
29    id INTEGER PRIMARY KEY AUTOINCREMENT,
30    manager_id INTEGER NOT NULL REFERENCES managers,
31    product_id INTEGER NOT NULL REFERENCES products,
32    qty INTEGER NOT NULL CHECK ( qty > 0 ),
33    price INTEGER NOT NULL CHECK ( price > 0 )
34);`
35
36const managersInitialData = `INSERT INTO managers
37VALUES (1, 'Vasya', 'vasya', 'secret', 100000, 0, NULL, NULL),
38       (2, 'Petya', 'petya', 'secret', 90000, 90000, 'boys', 1),
39       (3, 'Vanya', 'vanya', 'secret', 80000, 80000, 'boys', 2),
40       (4, 'Masha', 'masha', 'secret', 80000, 80000, 'girls', 1),
41       (5, 'Dasha', 'dasha', 'secret', 60000, 60000, 'girls', 4),
42       (6, 'Sasha', 'sasha', 'secret', 40000, 40000, 'girls', 5)
43       ON CONFLICT DO NOTHING;`
44
45const productsInitialData = `INSERT INTO products(id, name, price, qty)
46VALUES (1, 'Big Mac', 200, 10),       -- 1
47       (2, 'Chicken Mac', 150, 15),   -- 2
48       (3, 'Cheese Burger', 100, 20), -- 3
49       (4, 'Tea', 50, 10),            -- 4
50       (5, 'Coffee', 80, 10),         -- 5
51       (6, 'Cola', 100, 20)           -- 6
52       ON CONFLICT DO NOTHING;`
53
54func Init(db *sql.DB) (err error) {
55	ddls := []string{managersDDL, productsDDL, salesDDL}
56	for _, ddl := range ddls {
57		_, err = db.Exec(ddl)
58		if err != nil {
59			return err
60		}
61	}
62
63	initialData := []string{managersInitialData, productsInitialData}
64	for _, datum := range initialData {
65		_, err = db.Exec(datum)
66		if err != nil {
67			return err
68		}
69	}
70
71	return nil
72}