create-db.sql 12 KB
Newer Older
Hugo Hörnquist's avatar
Hugo Hörnquist committed
1
2
PRAGMA FOREIGN_KEYS = ON; -- do this for all database connections

3
4
-- ============================== Tables =================================

Hugo Hörnquist's avatar
Hugo Hörnquist committed
5
CREATE TABLE products ( -- products available
Hugo Hörnquist's avatar
Hugo Hörnquist committed
6
7
8
9
10
11
12
    id INTEGER PRIMARY KEY NOT NULL,
    sorting_id INTEGER DEFAULT 0, -- for ui sorting
    buy_id INTEGER, -- Systembolagets id, om behov skulle finnas
    bar_code TEXT UNIQUE, -- för framtiden
    name TEXT DEFAULT "CHANGE ME" NOT NULL,
    price INTEGER DEFAULT 0 NOT NULL,
    sale_status INTEGER DEFAULT 1
Hugo Hörnquist's avatar
Hugo Hörnquist committed
13
    -- -1: system, not currently used
Hugo Hörnquist's avatar
Hugo Hörnquist committed
14
15
16
    -- 0: For Sale
    -- 1: Not For Sale
    -- 2: Hidden
Hugo Hörnquist's avatar
Hugo Hörnquist committed
17
18
19
);

CREATE TABLE stock ( -- how much we have of each product
Hugo Hörnquist's avatar
Hugo Hörnquist committed
20
21
    product_id INTEGER PRIMARY KEY NOT NULL,
    active INTEGER DEFAULT 0,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
22
    -- extra storage places can be added here
23
24
    -- example: (TODO actually remove this column, but check the front end first)
    storage INTEGER DEFAULT 0,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
25

Hugo Hörnquist's avatar
Hugo Hörnquist committed
26
    FOREIGN KEY (product_id) REFERENCES products(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
27
28
);

29
CREATE TABLE log (
Hugo Hörnquist's avatar
Hugo Hörnquist committed
30
    id INTEGER PRIMARY KEY NOT NULL,
31
    product_id INTEGER NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
32
33
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    price INTEGER NOT NULL,
34
    account INTEGER NOT NULL REFERENCES money(id),
Hugo Hörnquist's avatar
Hugo Hörnquist committed
35

Hugo Hörnquist's avatar
Hugo Hörnquist committed
36
    FOREIGN KEY (product_id) REFERENCES products(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
37
38
);

Hugo Hörnquist's avatar
Hugo Hörnquist committed
39
CREATE TABLE money ( -- variable table
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
40
    id INTEGER PRIMARY KEY NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
41
42
    name TEXT UNIQUE,
    amount INTEGER DEFAULT 0
Hugo Hörnquist's avatar
Hugo Hörnquist committed
43
);
Hugo Hörnquist's avatar
Hugo Hörnquist committed
44
45

CREATE TABLE money_diffs (
Hugo Hörnquist's avatar
Hugo Hörnquist committed
46
    id INTEGER PRIMARY KEY NOT NULL,
47
48
    expected INTEGER NOT NULL,
    actual INTEGER NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
49
    account INTEGER NOT NULL DEFAULT 0,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
50
51
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,

Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
52
    FOREIGN KEY (account) REFERENCES money(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
53
54
55
);

CREATE TABLE money_transfers (
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
56
    id INTEGER PRIMARY KEY NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
57
    change INTEGER NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
58
    -- the two accounts can be set to 0 for the "outside world"
59
60
    from_acc INTEGER NOT NULL REFERENCES money(id),
    to_acc INTEGER NOT NULL REFERENCES money(id),
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
61
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
62
    note TEXT
Hugo Hörnquist's avatar
Hugo Hörnquist committed
63
);
Hugo Hörnquist's avatar
Hugo Hörnquist committed
64
65

CREATE TABLE big_buy ( -- table for temporary storage of current items to buy
Hugo Hörnquist's avatar
Hugo Hörnquist committed
66
67
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
68

Hugo Hörnquist's avatar
Hugo Hörnquist committed
69
    FOREIGN KEY (product_id) REFERENCES products(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
70
71
);

Hugo Hörnquist's avatar
Hugo Hörnquist committed
72
73
74
75
-- för inköp från leverantör
-- Alla objekt vid ett inköpstillfälle kommer att ha samma 'time'
--     Och formar då ett "kvitto"
CREATE TABLE acquisitions (
Hugo Hörnquist's avatar
Hugo Hörnquist committed
76
77
78
79
80
    id INTEGER PRIMARY KEY NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    product_id INTEGER NOT NULL,
    item_price INTEGER DEFAULT 0, -- price of each unit
    amount INTEGER DEFAULT 0,
81
    account INTEGER NOT NULL DEFAULT 0 REFERENCES money(id),
Hugo Hörnquist's avatar
Hugo Hörnquist committed
82

Hugo Hörnquist's avatar
Hugo Hörnquist committed
83
    FOREIGN KEY (product_id) REFERENCES products(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
84
);
85

86
-- temp table used in frontend
Hugo Hörnquist's avatar
Hugo Hörnquist committed
87
CREATE TABLE acquisitions_temp (
Hugo Hörnquist's avatar
Hugo Hörnquist committed
88
89
90
91
92
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER UNIQUE NOT NULL,
    amount INTEGER DEFAULT 0,
    price INTEGER DEFAULT 0,
    total_price INTEGER DEFAULT 0,
93

Hugo Hörnquist's avatar
Hugo Hörnquist committed
94
    FOREIGN KEY (product_id) REFERENCES products(id)
95
);
Hugo Hörnquist's avatar
Hugo Hörnquist committed
96

Hugo Hörnquist's avatar
Hugo Hörnquist committed
97
CREATE TABLE stock_diff (
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
98
99
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
100
101
    expected INTEGER NOT NULL DEFAULT 0,
    actual INTEGER NOT NULL DEFAULT 0,
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
102
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
103

Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
104
    FOREIGN KEY (product_id) REFERENCES products(id)
Hugo Hörnquist's avatar
Hugo Hörnquist committed
105
106
);

107
108
109
110
111
112
113
114
CREATE TABLE diff_help (
    id INTEGER PRIMARY KEY NOT NULL,
    product_id INTEGER NOT NULL,
    amount INTEGER,

    FOREIGN KEY (product_id) REFERENCES products(id)
);

115
CREATE TABLE drainage (
116
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
117
118
119
120
121
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    end_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    decrease INTEGER DEFAULT 2 NOT NULL,
    how_often_minutes INTEGER DEFAULT 60 NOT NULL,
    min_price INTEGER DEFAULT 5 NOT NULL
122
123
);

Hugo Hörnquist's avatar
Hugo Hörnquist committed
124
125
CREATE TABLE my_db_settings (
    id INTEGER PRIMARY KEY NOT NULL,
126
    name TEXT UNIQUE NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
127
128
129
    value
);

130
131
132
133
134
135
CREATE TABLE sql_queries (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT NOT NULL UNIQUE,
    query TEXT DEFAULT "" NOT NULL
);

136
137
138
139
-- ============================== Views ==================================

CREATE VIEW drainage_change AS -- how prices have changed due to the drainage, or null
SELECT min_price,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
140
141
142
143
144
145
146
147
        ( (strftime('%s', 'now') - strftime('%s', start_time))
            / (60 * how_often_minutes)
        ) * decrease AS change
FROM drainage
WHERE CURRENT_TIMESTAMP BETWEEN start_time AND end_time;

-- The same layout as products, but with drainage prices when those are applicable
CREATE VIEW current_products AS
148
149
150
151
152
SELECT p.id,
       p.sorting_id,
       p.buy_id,
       p.bar_code,
       p.name,
153
       ifnull(MIN(p.price, MAX(d.min_price, p.price - d.change)), p.price) AS price,
154
155
156
157
158
       p.sale_status
FROM products p LEFT JOIN drainage_change d;

-- this is a view because the change labels also use it
CREATE VIEW full_big_buy AS
Hugo Hörnquist's avatar
Hugo Hörnquist committed
159
160
161
SELECT p.id AS product_id,
       b.id AS buy_id,
       count(b.product_id) AS amount,
162
163
       p.name,
       p.price,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
164
       count(b.product_id) * p.price AS total
Hugo Hörnquist's avatar
Hugo Hörnquist committed
165
FROM big_buy b
Hugo Hörnquist's avatar
Hugo Hörnquist committed
166
LEFT JOIN current_products p
Hugo Hörnquist's avatar
Hugo Hörnquist committed
167
168
169
   ON b.product_id = p.id
GROUP BY p.id;

170
171
172
173
174
175
-- this could be replaced with view delegates in the GUI
CREATE VIEW display_big_buy AS
SELECT product_id,
       buy_id,
       amount,
       name,
176
177
       price / 100 AS price_kr,
       total / 100 AS total_kr
178
179
FROM full_big_buy;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
180
-- this was needed to have price in crown without delegate in product list
181
-- also, possibly rename this to product_listings
Hugo Hörnquist's avatar
Hugo Hörnquist committed
182
CREATE VIEW product_list AS -- list of products for sale
183
SELECT p.id, p.bar_code, p.sorting_id, p.name, p.price / 100 AS price, s.active
Hugo Hörnquist's avatar
Hugo Hörnquist committed
184
185
186
FROM current_products p
LEFT JOIN stock s ON p.id = s.product_id
WHERE p.sale_status = 0
187
188
GROUP BY p.id;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
189
190
-- views make requerying data so much easier!
CREATE VIEW total_log AS
191
SELECT count(products.name) AS amount, products.name
Hugo Hörnquist's avatar
Hugo Hörnquist committed
192
193
194
195
196
197
198
199
200
FROM log INNER JOIN products
ON log.product_id = products.id
WHERE datetime(log.time, 'localtime') BETWEEN
    datetime('now', 'localtime', '-12 hours', 'start of day', '+12 hours')
AND datetime('now', 'localtime', '-12 hours', 'start of day', '+36 hours')
GROUP BY products.id
ORDER BY products.name;

CREATE VIEW transaction_log AS
201
202
SELECT log.id,
        products.name,
203
204
        log.price / 100 AS price,
        datetime(log.time, 'localtime') AS sold_time
Hugo Hörnquist's avatar
Hugo Hörnquist committed
205
206
207
208
209
210
211
FROM log INNER JOIN products
ON log.product_id = products.id
WHERE datetime(log.time, 'localtime') BETWEEN
    datetime('now', 'localtime', '-12 hours', 'start of day', '+12 hours')
AND datetime('now', 'localtime', '-12 hours', 'start of day', '+36 hours')
ORDER BY log.time;

212
213
214
215
216
217
218
219
CREATE VIEW diff_view AS
SELECT d.id AS id,
       p.id AS product_id,
       p.sale_status AS sale_status,
       -- visible border
       p.name AS name,
       s.active AS expected,
       d.amount AS actual,
220
       d.amount - s.active AS diff
221
222
223
224
225
226
FROM products p
INNER JOIN stock s ON p.id = s.product_id
INNER JOIN diff_help d ON p.id = d.product_id;
-- WHERE p.sale_status = 0 OR s.active != 0;
-- where p.sale_status in (0, 1);

227
228
229
230
231
CREATE VIEW money_simple AS
SELECT name,
       printf("%.2f", amount / 100.0) AS amount
FROM money WHERE id != 0;

232
233
234
235
236
237
238
239
240
241
242
243
CREATE VIEW money_transfers_simple AS
SELECT
    t.time AS time,
    m.name AS from_acc,
    n.name AS to_acc,
    printf("%.2f", t.change / 100.0) AS amount,
    t.note AS note
FROM money_transfers t
LEFT JOIN money m ON t.from_acc = m.id
LEFT JOIN money n ON t.to_acc = n.id
ORDER by time DESC;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
244
245
-- ============================== Triggers ===============================

Hugo Hörnquist's avatar
Hugo Hörnquist committed
246
247
-- some of these would benefit from being FOR EACH STATEMENT instead of
-- FOR EACH ROW, but sqlite doesn't support that yet.
Hugo Hörnquist's avatar
Hugo Hörnquist committed
248

Hugo Hörnquist's avatar
Hugo Hörnquist committed
249
250
251
252
-- Delete this to enable optional stock
-- Do note that the ui is not really set up for it (yet)
CREATE TRIGGER add_new_stock
AFTER INSERT ON products
Hugo Hörnquist's avatar
Hugo Hörnquist committed
253
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
254
255
256
257
258
259
BEGIN
    INSERT INTO stock (product_id) VALUES ((SELECT NEW.id));
END;

CREATE TRIGGER before_acquisitions_transfer
BEFORE DELETE ON acquisitions_temp
Hugo Hörnquist's avatar
Hugo Hörnquist committed
260
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
261
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
Hugo Hörnquist's avatar
Hugo Hörnquist committed
262
BEGIN
263
264
265
    INSERT INTO acquisitions (product_id, item_price, amount, account)
    SELECT OLD.product_id, OLD.price, OLD.amount,
        (SELECT value FROM my_db_settings WHERE name = 'active_money_account');
Hugo Hörnquist's avatar
Hugo Hörnquist committed
266
267
268
269
270

    UPDATE stock SET active = active + OLD.amount
    WHERE product_id = OLD.product_id;

    UPDATE money SET amount = amount - (OLD.price * OLD.amount)
271
    WHERE id = (SELECT value FROM my_db_settings WHERE name = 'active_money_account');
Hugo Hörnquist's avatar
Hugo Hörnquist committed
272
273
END;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
274
/* This is already done in the before trigger
Hugo Hörnquist's avatar
Hugo Hörnquist committed
275
276
CREATE TRIGGER after_acquisitions_transfer
AFTER DELETE ON acquisitions_temp
Hugo Hörnquist's avatar
Hugo Hörnquist committed
277
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
278
279
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
BEGIN
Hugo Hörnquist's avatar
Hugo Hörnquist committed
280
    UPDATE money SET amount = amount - OLD.price * OLD.amount
Hugo Hörnquist's avatar
Hugo Hörnquist committed
281
    WHERE name = "Cash Drawer";
Hugo Hörnquist's avatar
Hugo Hörnquist committed
282
END;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
283
*/
Hugo Hörnquist's avatar
Hugo Hörnquist committed
284
285
286

CREATE TRIGGER after_log_del
AFTER DELETE ON log
Hugo Hörnquist's avatar
Hugo Hörnquist committed
287
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
288
BEGIN
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
289
    UPDATE money SET amount = amount - OLD.price WHERE name = 'Cash Drawer';
Hugo Hörnquist's avatar
Hugo Hörnquist committed
290
291
292
293
294
295

    UPDATE stock SET active = active + 1 WHERE product_id = OLD.product_id;
END;

CREATE TRIGGER after_log_add
AFTER INSERT ON log
Hugo Hörnquist's avatar
Hugo Hörnquist committed
296
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
297
BEGIN
Hugo Hörnquist's avatar
retab    
Hugo Hörnquist committed
298
    UPDATE money SET amount = amount + NEW.price WHERE name = 'Cash Drawer';
Hugo Hörnquist's avatar
Hugo Hörnquist committed
299
300
301
302

    UPDATE stock SET active = active - 1 WHERE product_id = NEW.product_id;
END;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
303
304
CREATE TRIGGER after_big_buy_delete
AFTER DELETE ON big_buy
Hugo Hörnquist's avatar
Hugo Hörnquist committed
305
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
306
307
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'big_buy_transfer')
BEGIN
308
309
310
311
    -- A regular "buy" is currently always done to the default
    -- account.
    INSERT INTO log (product_id, price, account)
    SELECT id, price, 1 FROM current_products WHERE id = OLD.product_id;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
312
313
END;

314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
/*
 * delete from the view to transfer,
 * delete from the underlying table (diff_help) if
 * a transfer is not desired.
 */

CREATE TRIGGER after_diff_help_delete_last
AFTER DELETE ON diff_help
FOR EACH ROW
WHEN (SELECT count (1) = 0 FROM diff_help)
BEGIN
    INSERT INTO diff_help (product_id)
    SELECT id FROM products p;
END;

CREATE TRIGGER diff_view_transfer
INSTEAD OF DELETE ON diff_view
BEGIN
    UPDATE stock SET active = OLD.actual
    WHERE OLD.actual IS NOT NULL
      AND product_id = OLD.product_id;

    INSERT INTO stock_diff (product_id, expected, actual)
    SELECT OLD.product_id, OLD.expected, OLD.actual
     WHERE OLD.actual IS NOT NULL;

    DELETE FROM diff_help WHERE id = OLD.id;
END;

CREATE TRIGGER update_view_trigger
INSTEAD OF UPDATE ON diff_view
BEGIN
    UPDATE diff_help SET amount = NEW.actual WHERE id = NEW.id;
END;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
349
350
351
352
353
354
355
356
357
358
359
360
CREATE TRIGGER before_money_transfer
BEFORE INSERT ON money_transfers
FOR EACH ROW
BEGIN
    UPDATE money SET amount = amount - NEW.change WHERE id = NEW.from_acc;
    UPDATE money SET amount = amount + NEW.change WHERE id = NEW.to_acc;
END;

CREATE TRIGGER before_money_diff
BEFORE INSERT ON money_diffs
FOR EACH ROW
BEGIN
361
    UPDATE money SET amount = NEW.actual WHERE id = NEW.account;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
362
363
END;

364
CREATE TRIGGER drainage_constraint
Hugo Hörnquist's avatar
Hugo Hörnquist committed
365
366
367
368
BEFORE INSERT ON drainage
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, "Drainages can't overlap")
369
370
371
    WHERE (SELECT SUM(NEW.start_time BETWEEN start_time AND end_time
                   OR NEW.end_time   BETWEEN start_time AND end_time)
               FROM drainage);
Hugo Hörnquist's avatar
Hugo Hörnquist committed
372

373
374
375
376
377
378
    SELECT RAISE(ABORT, "New drainages can't end in the past")
    WHERE (SELECT NEW.end_time < CURRENT_TIMESTAMP);

    SELECT RAISE(ABORT, "Drainages need to have a positive time")
    WHERE (SELECT NEW.start_time > NEW.end_time);
END;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
379

Hugo Hörnquist's avatar
Hugo Hörnquist committed
380
-- ============================== Default Queries =======================
381

Hugo Hörnquist's avatar
Hugo Hörnquist committed
382
383
384
INSERT INTO money (id, name, amount)
VALUES (0, "no account", NULL);

385
386
387
INSERT INTO money (name)
VALUES ("Cash Drawer"), ("Vault");

Hugo Hörnquist's avatar
Hugo Hörnquist committed
388
INSERT INTO my_db_settings (name, value)
389
VALUES ("big_buy_transfer", 1),
390
391
392
393
394
395
       ("acquisitions_temp_transfer", 1),
       -- Not actually a setting, but a DB technicality
       ("active_money_account", 1),
       -- Account which money is drained from by default.
       -- Should be possible for the user to set (from the GUI).
       ("default_money_account", 1);
396

397
398
399
400
-- This is mostly so that diff_help have something to insert
INSERT INTO products (name)
VALUES ("Default Product");

401
-- diff_help gets it's data when the last record is deleted,
402
-- so insert *any* data and delete it.
403
404
405
406

INSERT INTO diff_help (product_id)
SELECT id FROM products LIMIT 1;
DELETE FROM diff_help;