From b3485c78114e505b7bc9c6969afa108ea3f4755c Mon Sep 17 00:00:00 2001
From: René 'Necoro' Neumann <necoro@necoro.eu>
Date: Wed, 14 Feb 2024 23:38:53 +0100
Subject: Remove sqlite support: the differences with sqlc just get too
 cumbersome

---
 sql/ddl/mysql.sql | 91 ++++++++++++++++++++++++++++++++++---------------------
 1 file changed, 57 insertions(+), 34 deletions(-)

(limited to 'sql')

diff --git a/sql/ddl/mysql.sql b/sql/ddl/mysql.sql
index 47315f9..5b867c7 100644
--- a/sql/ddl/mysql.sql
+++ b/sql/ddl/mysql.sql
@@ -1,41 +1,56 @@
 create table users
 (
-    id          INTEGER      not null
+    id          int auto_increment
         primary key,
-    name        VARCHAR(50)  not null
-        unique,
-    pwd         VARCHAR(255) not null,
-    description VARCHAR(100)
+    name        varchar(50)  not null,
+    pwd         varchar(255) not null,
+    description varchar(100) null,
+    constraint name
+        unique (name)
 );
 
 create table categories
 (
-    id        INTEGER     not null
+    id        int auto_increment
         primary key,
-    name      VARCHAR(50) not null,
-    parent_id INTEGER
-        references categories,
-    user_id   INTEGER     not null
-        references users
+    name      varchar(50) not null,
+    parent_id int         null,
+    user_id   int         not null,
+    constraint categories_ibfk_1
+        foreign key (parent_id) references categories (id),
+    constraint categories_ibfk_2
+        foreign key (user_id) references users (id)
 );
 
+create index parent_id
+    on categories (parent_id);
+
+create index user_id
+    on categories (user_id);
+
 create table const_expenses
 (
-    id          INTEGER        not null
+    id          int auto_increment
         primary key,
-    description VARCHAR(50),
-    expense     NUMERIC(10, 2) not null,
-    months      SMALLINT       not null,
-    start       DATE           not null,
-    end         DATE           not null,
-    prev_id     INTEGER
-        references const_expenses,
-    user_id     INTEGER        not null
-        references users,
-    category_id INTEGER        not null
-        references categories
+    description varchar(50)      null,
+    expense     decimal(10, 2)   not null,
+    months      tinyint unsigned not null,
+    start       date             not null,
+    end         date             not null,
+    prev_id     int              null,
+    category_id int              not null,
+    user_id     int              not null,
+    constraint const_expenses_ibfk_1
+        foreign key (prev_id) references const_expenses (id),
+    constraint const_expenses_ibfk_2
+        foreign key (category_id) references categories (id),
+    constraint const_expenses_ibfk_3
+        foreign key (user_id) references users (id)
 );
 
+create index category_id
+    on const_expenses (category_id);
+
 create index idx_start_end
     on const_expenses (user_id, start, end);
 
@@ -45,20 +60,28 @@ create index ix_constexpense_end
 create index ix_constexpense_start
     on const_expenses (start);
 
+create index prev_id
+    on const_expenses (prev_id);
+
 create table single_expenses
 (
-    id          INTEGER        not null
+    id          int auto_increment
         primary key,
-    description VARCHAR(50),
-    expense     NUMERIC(10, 2) not null,
-    year        INTEGER        not null,
-    month       SMALLINT       not null,
-    day         SMALLINT       not null,
-    user_id     INTEGER        not null
-        references users,
-    category_id INTEGER        not null
-        references categories
+    description varchar(50)         null,
+    expense     decimal(10, 2)      not null,
+    year        year                not null,
+    month       tinyint(6) unsigned not null,
+    day         tinyint(6) unsigned not null,
+    category_id int                 not null,
+    user_id     int                 not null,
+    constraint single_expenses_ibfk_1
+        foreign key (category_id) references categories (id),
+    constraint single_expenses_ibfk_2
+        foreign key (user_id) references users (id)
 );
 
+create index category_id
+    on single_expenses (category_id);
+
 create index idx_single_date
-    on single_expenses (user_id, year, month);
+    on single_expenses (user_id, year, month);
\ No newline at end of file
-- 
cgit v1.2.3-70-g09d2