create-db.sql 10.9 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,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
34

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

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

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

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

CREATE TABLE money_transfers (
Hugo Hörnquist's avatar
retab  
Hugo Hörnquist committed
55
    id INTEGER PRIMARY KEY NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
56
    change INTEGER NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
57
    -- the two accounts can be set to 0 for the "outside world"
58 59
    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
60
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
61
    note TEXT
Hugo Hörnquist's avatar
Hugo Hörnquist committed
62
);
Hugo Hörnquist's avatar
Hugo Hörnquist committed
63 64

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

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

Hugo Hörnquist's avatar
Hugo Hörnquist committed
71 72 73 74
-- 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
75 76 77 78 79
    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,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
80

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

84
-- temp table used in frontend
Hugo Hörnquist's avatar
Hugo Hörnquist committed
85
CREATE TABLE acquisitions_temp (
Hugo Hörnquist's avatar
Hugo Hörnquist committed
86 87 88 89 90
    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,
91

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

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

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

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

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

113
CREATE TABLE drainage (
114
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
115 116 117 118 119
    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
120 121
);

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

128 129 130 131
-- ============================== 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
132 133 134 135 136 137 138 139
        ( (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
140 141 142 143 144
SELECT p.id,
       p.sorting_id,
       p.buy_id,
       p.bar_code,
       p.name,
145
       ifnull(MIN(p.price, MAX(d.min_price, p.price - d.change)), p.price) AS price,
146 147 148 149 150
       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
151 152 153
SELECT p.id AS product_id,
       b.id AS buy_id,
       count(b.product_id) AS amount,
154 155
       p.name,
       p.price,
Hugo Hörnquist's avatar
Hugo Hörnquist committed
156
       count(b.product_id) * p.price AS total
Hugo Hörnquist's avatar
Hugo Hörnquist committed
157
FROM big_buy b
Hugo Hörnquist's avatar
Hugo Hörnquist committed
158
LEFT JOIN current_products p
Hugo Hörnquist's avatar
Hugo Hörnquist committed
159 160 161
   ON b.product_id = p.id
GROUP BY p.id;

162 163 164 165 166 167
-- this could be replaced with view delegates in the GUI
CREATE VIEW display_big_buy AS
SELECT product_id,
       buy_id,
       amount,
       name,
168 169
       price / 100 AS price_kr,
       total / 100 AS total_kr
170 171
FROM full_big_buy;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
172
-- this was needed to have price in crown without delegate in product list
173
-- also, possibly rename this to product_listings
Hugo Hörnquist's avatar
Hugo Hörnquist committed
174
CREATE VIEW product_list AS -- list of products for sale
175
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
176 177 178
FROM current_products p
LEFT JOIN stock s ON p.id = s.product_id
WHERE p.sale_status = 0
179 180
GROUP BY p.id;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
181 182
-- views make requerying data so much easier!
CREATE VIEW total_log AS
183
SELECT count(products.name) AS amount, products.name
Hugo Hörnquist's avatar
Hugo Hörnquist committed
184 185 186 187 188 189 190 191 192
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
193 194
SELECT log.id,
        products.name,
195 196
        log.price / 100 AS price,
        datetime(log.time, 'localtime') AS sold_time
Hugo Hörnquist's avatar
Hugo Hörnquist committed
197 198 199 200 201 202 203
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;

204 205 206 207 208 209 210 211
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,
212
       d.amount - s.active AS diff
213 214 215 216 217 218
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);

Hugo Hörnquist's avatar
Hugo Hörnquist committed
219 220
-- ============================== Triggers ===============================

Hugo Hörnquist's avatar
Hugo Hörnquist committed
221 222
-- 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
223

Hugo Hörnquist's avatar
Hugo Hörnquist committed
224 225 226 227
-- 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
228
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
229 230 231 232 233 234
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
235
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
236
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
Hugo Hörnquist's avatar
Hugo Hörnquist committed
237 238 239
BEGIN
    INSERT INTO acquisitions (product_id, item_price, amount)
    SELECT OLD.product_id, OLD.price, OLD.amount;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
240 241 242 243 244 245 246 247

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

    UPDATE money SET amount = amount - (OLD.price * OLD.amount)
    WHERE name = "Cash Drawer";
END;

Hugo Hörnquist's avatar
Hugo Hörnquist committed
248
/* This is already done in the before trigger
Hugo Hörnquist's avatar
Hugo Hörnquist committed
249 250
CREATE TRIGGER after_acquisitions_transfer
AFTER DELETE ON acquisitions_temp
Hugo Hörnquist's avatar
Hugo Hörnquist committed
251
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
252 253
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
BEGIN
Hugo Hörnquist's avatar
Hugo Hörnquist committed
254
    UPDATE money SET amount = amount - OLD.price * OLD.amount
Hugo Hörnquist's avatar
Hugo Hörnquist committed
255
    WHERE name = "Cash Drawer";
Hugo Hörnquist's avatar
Hugo Hörnquist committed
256
END;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
257
*/
Hugo Hörnquist's avatar
Hugo Hörnquist committed
258 259 260

CREATE TRIGGER after_log_del
AFTER DELETE ON log
Hugo Hörnquist's avatar
Hugo Hörnquist committed
261
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
262
BEGIN
Hugo Hörnquist's avatar
retab  
Hugo Hörnquist committed
263
    UPDATE money SET amount = amount - OLD.price WHERE name = 'Cash Drawer';
Hugo Hörnquist's avatar
Hugo Hörnquist committed
264 265 266 267 268 269

    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
270
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
271
BEGIN
Hugo Hörnquist's avatar
retab  
Hugo Hörnquist committed
272
    UPDATE money SET amount = amount + NEW.price WHERE name = 'Cash Drawer';
Hugo Hörnquist's avatar
Hugo Hörnquist committed
273 274 275 276

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

Hugo Hörnquist's avatar
Hugo Hörnquist committed
277 278
CREATE TRIGGER after_big_buy_delete
AFTER DELETE ON big_buy
Hugo Hörnquist's avatar
Hugo Hörnquist committed
279
FOR EACH ROW
Hugo Hörnquist's avatar
Hugo Hörnquist committed
280 281
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'big_buy_transfer')
BEGIN
282 283
    INSERT INTO log (product_id, price)
    SELECT id, price FROM current_products WHERE id = OLD.product_id;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
284 285
END;

286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320
/*
 * 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
321 322 323 324 325 326 327 328 329 330 331 332
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
333
    UPDATE money SET amount = NEW.actual WHERE id = NEW.account;
Hugo Hörnquist's avatar
Hugo Hörnquist committed
334 335
END;

336
CREATE TRIGGER drainage_constraint
Hugo Hörnquist's avatar
Hugo Hörnquist committed
337 338 339 340
BEFORE INSERT ON drainage
FOR EACH ROW
BEGIN
    SELECT RAISE(ABORT, "Drainages can't overlap")
341 342 343
    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
344

345 346 347 348 349 350
    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
351

Hugo Hörnquist's avatar
Hugo Hörnquist committed
352
-- ============================== Default Queries =======================
353

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

357 358 359
INSERT INTO money (name)
VALUES ("Cash Drawer"), ("Vault");

Hugo Hörnquist's avatar
Hugo Hörnquist committed
360
INSERT INTO my_db_settings (name, value)
361
VALUES ("big_buy_transfer", 1),
Hugo Hörnquist's avatar
Hugo Hörnquist committed
362
       ("acquisitions_temp_transfer", 1);
363

364 365 366 367
-- This is mostly so that diff_help have something to insert
INSERT INTO products (name)
VALUES ("Default Product");

368
-- diff_help gets it's data when the last record is deleted,
369
-- so insert *any* data and delete it.
370 371 372 373

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