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
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
-- Activate strict mode
-- Start with users and categories, as it’s referenced in other tables.
-- Otherwise, the reference is set to the renamed non strict table.
-- Users
ALTER TABLE "users" RENAME TO "users_non_strict";
CREATE TABLE IF NOT EXISTS "users"(
"id" INTEGER PRIMARY KEY,
"email" TEXT NOT NULL,
"name" TEXT NOT NULL,
"password" TEXT NOT NULL,
"login_token" TEXT NULL,
"created_at" TEXT NULL DEFAULT (datetime('now')),
"updated_at" TEXT NULL,
"deleted_at" TEXT NULL,
CONSTRAINT "uniq_user_email" UNIQUE ("email"),
CONSTRAINT "uniq_user_name" UNIQUE ("name")
) STRICT;
INSERT INTO users (id, created_at, email, name, password, login_token)
SELECT id, created_at, email, name, password, login_token
FROM users_non_strict;
DROP TABLE users_non_strict;
-- Categories
ALTER TABLE "categories" RENAME TO "categories_non_strict";
CREATE TABLE IF NOT EXISTS "categories"(
"id" INTEGER PRIMARY KEY,
"name" TEXT NOT NULL,
"color" TEXT NOT NULL,
"created_at" TEXT NULL DEFAULT (datetime('now')),
"updated_at" TEXT NULL,
"deleted_at" TEXT NULL
) STRICT;
INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
SELECT id, name, color, created_at, updated_at, deleted_at
FROM categories_non_strict;
DROP TABLE categories_non_strict;
-- Payments
ALTER TABLE "payments" RENAME TO "payments_non_strict";
CREATE TABLE IF NOT EXISTS "payments"(
"id" INTEGER PRIMARY KEY,
"user_id" INTEGER NOT NULL REFERENCES "users",
"name" TEXT NOT NULL,
"cost" INTEGER NOT NULL,
"date" TEXT NOT NULL,
"frequency" TEXT NOT NULL,
"category_id" INTEGER NOT NULL REFERENCES "categories",
"created_at" TEXT NULL DEFAULT (datetime('now')),
"updated_at" TEXT NULL,
"deleted_at" TEXT NULL
) STRICT;
DROP INDEX IF EXISTS payment_date;
CREATE INDEX payment_date ON payments(date);
INSERT INTO payments (id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at)
SELECT id, user_id, name, cost, date, frequency, category_id, created_at, updated_at, deleted_at
FROM payments_non_strict;
DROP TABLE payments_non_strict;
-- Jobs
ALTER TABLE "jobs" RENAME TO "jobs_non_strict";
CREATE TABLE IF NOT EXISTS "jobs"(
"name" TEXT PRIMARY KEY,
"last_execution" TEXT NOT NULL DEFAULT (datetime('now'))
) STRICT;
INSERT INTO jobs (name, last_execution)
SELECT name, last_execution
FROM jobs_non_strict;
DROP TABLE jobs_non_strict;
-- Incomes
ALTER TABLE "incomes" RENAME TO "incomes_non_strict";
CREATE TABLE IF NOT EXISTS "incomes"(
"id" INTEGER PRIMARY KEY,
"user_id" INTEGER NOT NULL REFERENCES "users",
"date" TEXT NOT NULL,
"amount" INTEGER NOT NULL,
"created_at" TEXT NULL DEFAULT (datetime('now')),
"updated_at" TEXT NULL,
"deleted_at" TEXT NULL
) STRICT;
INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
SELECT id, user_id, date, amount, created_at, updated_at, deleted_at
FROM incomes_non_strict;
DROP TABLE incomes_non_strict;
|
