aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/fixtures.sql45
-rw-r--r--sql/migrations/1.sql65
-rw-r--r--sql/migrations/2.sql44
-rw-r--r--sql/migrations/3.sql5
-rw-r--r--sql/migrations/4.sql91
5 files changed, 250 insertions, 0 deletions
diff --git a/sql/fixtures.sql b/sql/fixtures.sql
new file mode 100644
index 0000000..61ff934
--- /dev/null
+++ b/sql/fixtures.sql
@@ -0,0 +1,45 @@
+INSERT INTO
+ users(email, name, password)
+VALUES
+ ('john@mail.com', 'John', '$2b$10$Qy8lqrTqHdzwLZwsqvO09eMwehA.vti.AGwPVj/pZYL94Ni6zozT2'),
+ ('lisa@mail.com', 'Lisa', '$2b$10$Qy8lqrTqHdzwLZwsqvO09eMwehA.vti.AGwPVj/pZYL94Ni6zozT2');
+
+INSERT INTO
+ incomes(user_id, date, amount)
+VALUES
+ (1, '2020-01-01', 1500),
+ (2, '2020-01-01', 2000);
+
+INSERT INTO
+ categories(name, color)
+VALUES
+ ('Habitation', '#aa0000'),
+ ('Alimentation', '#00aa00'),
+ ('Animaux', '#0000aa');
+
+INSERT INTO
+ payments(user_id, name, cost, date, frequency, category_id)
+VALUES
+ (1, 'Loyer', 600, '2021-01-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-02-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-03-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-04-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-05-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-06-01', 'Punctual', 1),
+ (1, 'Loyer', 600, '2020-07-01', 'Punctual', 1),
+ (1, 'Loyer', 600, '2020-08-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-09-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-10-01', 'Punctual', 1),
+ (2, 'Loyer', 600, '2020-11-01', 'Punctual', 1),
+ (1, 'Loyer', 600, '2020-12-01', 'Punctual', 1),
+ (1, 'Loyer', 600, '2020-01-01', 'Punctual', 1),
+ (1, 'Marché', 55, '2021-01-02', 'Punctual', 2),
+ (2, 'Restaurant', 60, '2020-12-10', 'Punctual', 2),
+ (1, 'Vétérinaire', 105, '2020-11-18', 'Punctual', 3),
+ (1, 'Magasin', 150, '2020-10-29', 'Punctual', 2);
+
+INSERT INTO
+ jobs(name)
+VALUES
+ ('MonthlyPayment'),
+ ('WeeklyReport');
diff --git a/sql/migrations/1.sql b/sql/migrations/1.sql
new file mode 100644
index 0000000..d7c300e
--- /dev/null
+++ b/sql/migrations/1.sql
@@ -0,0 +1,65 @@
+CREATE TABLE IF NOT EXISTS "user" (
+ "id" INTEGER PRIMARY KEY,
+ "creation" TIMESTAMP NOT NULL,
+ "email" VARCHAR NOT NULL,
+ "name" VARCHAR NOT NULL,
+ CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+ CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+CREATE TABLE IF NOT EXISTS "job" (
+ "id" INTEGER PRIMARY KEY,
+ "kind" VARCHAR NOT NULL,
+ "last_execution" TIMESTAMP NULL,
+ "last_check" TIMESTAMP NULL,
+ CONSTRAINT "uniq_job_kind" UNIQUE ("kind")
+);
+
+CREATE TABLE IF NOT EXISTS "sign_in"(
+ "id" INTEGER PRIMARY KEY,
+ "token" VARCHAR NOT NULL,
+ "creation" TIMESTAMP NOT NULL,
+ "email" VARCHAR NOT NULL,
+ "is_used" BOOLEAN NOT NULL,
+ CONSTRAINT "uniq_sign_in_token" UNIQUE ("token")
+);
+
+CREATE TABLE IF NOT EXISTS "payment"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "user",
+ "name" VARCHAR NOT NULL,
+ "cost" INTEGER NOT NULL,
+ "date" DATE NOT NULL,
+ "frequency" VARCHAR NOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "income"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "user",
+ "date" DATE NOT NULL,
+ "amount" INTEGERNOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "category"(
+ "id" INTEGER PRIMARY KEY,
+ "name" VARCHAR NOT NULL,
+ "color" VARCHAR NOT NULL,
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ "deleted_at" TIMESTAMP NULL
+);
+
+CREATE TABLE IF NOT EXISTS "payment_category"(
+ "id" INTEGER PRIMARY KEY,
+ "name" VARCHAR NOT NULL,
+ "category" INTEGER NOT NULL REFERENCES "category",
+ "created_at" TIMESTAMP NOT NULL,
+ "edited_at" TIMESTAMP NULL,
+ CONSTRAINT "uniq_payment_category_name" UNIQUE ("name")
+);
diff --git a/sql/migrations/2.sql b/sql/migrations/2.sql
new file mode 100644
index 0000000..c1d502f
--- /dev/null
+++ b/sql/migrations/2.sql
@@ -0,0 +1,44 @@
+-- Add payment categories with accents from payment with accents
+
+INSERT INTO
+ payment_category (name, category, created_at)
+SELECT
+ DISTINCT lower(payment.name), payment_category.category, datetime('now')
+FROM
+ payment
+INNER JOIN
+ payment_category
+ON
+ replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(lower(payment.name), 'é', 'e'), 'è', 'e'), 'à', 'a'), 'û', 'u'), 'â', 'a'), 'ê', 'e'), 'â', 'a'), 'î', 'i'), 'ï', 'i'), 'ô', 'o'), 'ë', 'e') = payment_category.name
+WHERE
+ payment.name
+IN
+ (SELECT DISTINCT payment.name FROM payment WHERE lower(payment.name) NOT IN (SELECT payment_category.name FROM payment_category) AND payment.deleted_at IS NULL);
+
+-- Remove unused payment categories
+
+DELETE FROM
+ payment_category
+WHERE
+ name NOT IN (SELECT DISTINCT lower(name) FROM payment);
+
+-- Add category id to payment table
+
+PRAGMA foreign_keys = 0;
+
+ALTER TABLE payment ADD COLUMN "category" INTEGER NOT NULL REFERENCES "category" DEFAULT -1;
+
+PRAGMA foreign_keys = 1;
+
+UPDATE
+ payment
+SET
+ category = (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name))
+WHERE
+ EXISTS (SELECT category FROM payment_category WHERE payment_category.name = LOWER(payment.name));
+
+DELETE FROM payment WHERE category = -1;
+
+-- Remove
+
+DROP TABLE payment_category;
diff --git a/sql/migrations/3.sql b/sql/migrations/3.sql
new file mode 100644
index 0000000..a3d8a13
--- /dev/null
+++ b/sql/migrations/3.sql
@@ -0,0 +1,5 @@
+DROP TABLE sign_in;
+
+ALTER TABLE user ADD COLUMN "password" TEXT NOT NULL DEFAULT "password";
+
+ALTER TABLE user ADD COLUMN "sign_in_token" TEXT NULL;
diff --git a/sql/migrations/4.sql b/sql/migrations/4.sql
new file mode 100644
index 0000000..ec386cb
--- /dev/null
+++ b/sql/migrations/4.sql
@@ -0,0 +1,91 @@
+-- Payments
+
+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" DATE NOT NULL,
+ "frequency" TEXT NOT NULL,
+ "category_id" INTEGER NOT NULL REFERENCES "categories",
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+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, created_at, edited_at, deleted_at
+ FROM payment;
+
+DROP TABLE payment;
+
+CREATE INDEX payment_date ON payments(date);
+
+-- Categories
+
+CREATE TABLE IF NOT EXISTS "categories"(
+ "id" INTEGER PRIMARY KEY,
+ "name" TEXT NOT NULL,
+ "color" TEXT NOT NULL,
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+INSERT INTO categories (id, name, color, created_at, updated_at, deleted_at)
+ SELECT id, name, color, created_at, edited_at, deleted_at
+ FROM category;
+
+DROP TABLE category;
+
+-- Users
+
+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" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL,
+ CONSTRAINT "uniq_user_email" UNIQUE ("email"),
+ CONSTRAINT "uniq_user_name" UNIQUE ("name")
+);
+
+INSERT INTO users (id, created_at, email, name, password, login_token)
+ SELECT id, creation, email, name, password, sign_in_token
+ FROM user;
+
+DROP TABLE user;
+
+-- Jobs
+
+CREATE TABLE IF NOT EXISTS "jobs"(
+ "name" TEXT PRIMARY KEY,
+ "last_execution" DATE NOT NULL DEFAULT (datetime('now'))
+);
+
+INSERT INTO jobs (name, last_execution)
+ SELECT kind, last_execution
+ FROM job;
+
+DROP TABLE job;
+
+-- Incomes
+
+CREATE TABLE IF NOT EXISTS "incomes"(
+ "id" INTEGER PRIMARY KEY,
+ "user_id" INTEGER NOT NULL REFERENCES "users",
+ "date" DATE NOT NULL,
+ "amount" INTEGER NOT NULL,
+ "created_at" DATE NULL DEFAULT (datetime('now')),
+ "updated_at" DATE NULL,
+ "deleted_at" DATE NULL
+);
+
+INSERT INTO incomes (id, user_id, date, amount, created_at, updated_at, deleted_at)
+ SELECT id, user_id, date, amount, created_at, edited_at, deleted_at
+ FROM income;
+
+DROP TABLE income;