patch.sql 1.99 KB
Newer Older
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
PRAGMA FOREIGN_KEYS = 0;
BEGIN TRANSACTION;

-- Add new columns to tables.

ALTER TABLE log ADD COLUMN
    account INTEGER NOT NULL DEFAULT 1 REFERENCES money(id);
ALTER TABLE acquisitions ADD COLUMN
    account INTEGER NOT NULL DEFAULT 1 REFERENCES money(id);

-- Set values in new fields

UPDATE log SET account = 1;
UPDATE acquisitions SET account = 1;

-- Add new db_settings

DROP TABLE my_db_settings;

CREATE TABLE my_db_settings (
    id INTEGER PRIMARY KEY NOT NULL,
    name TEXT UNIQUE NOT NULL,
    value
);

INSERT INTO my_db_settings (name, value)
VALUES ("big_buy_transfer", 1),
       ("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);

-- Recreate triggers

DROP TRIGGER IF EXISTS before_acquisitions_transfer;

CREATE TRIGGER before_acquisitions_transfer
BEFORE DELETE ON acquisitions_temp
FOR EACH ROW
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'acquisitions_temp_transfer')
BEGIN
    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');

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

    UPDATE money SET amount = amount - (OLD.price * OLD.amount)
    WHERE id = (SELECT value FROM my_db_settings WHERE name = 'active_money_account');
END;

DROP TRIGGER IF EXISTS after_big_buy_delete;

CREATE TRIGGER after_big_buy_delete
AFTER DELETE ON big_buy
FOR EACH ROW
WHEN (SELECT value = 1 FROM my_db_settings WHERE name = 'big_buy_transfer')
BEGIN
    INSERT INTO log (product_id, price, account)
    SELECT id, price, (SELECT value FROM my_db_settings WHERE name = 'default_money_account')
    FROM current_products WHERE id = OLD.product_id;
END;


COMMIT;
PRAGMA FOREIGN_KEYS = 1;