Migrate database at startup

5 files changed, 0 insertions, 312 deletions

diff --git a/sql/migrations/1.sql b/sql/migrations/1.sql
deleted file mode 100644
index d7c300e..0000000
--- a/sql/migrations/1.sql
+++ /dev/null
@@ -1,65 +0,0 @@
-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
deleted file mode 100644
index c1d502f..0000000
--- a/sql/migrations/2.sql
+++ /dev/null
@@ -1,44 +0,0 @@
--- 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
deleted file mode 100644
index a3d8a13..0000000
--- a/sql/migrations/3.sql
+++ /dev/null
@@ -1,5 +0,0 @@
-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
deleted file mode 100644
index ec386cb..0000000
--- a/sql/migrations/4.sql
+++ /dev/null
@@ -1,91 +0,0 @@
--- 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;
diff --git a/sql/migrations/5.sql b/sql/migrations/5.sql
deleted file mode 100644
index cf7ef4b..0000000
--- a/sql/migrations/5.sql
+++ /dev/null
@@ -1,107 +0,0 @@
--- 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;