summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
authorRené 'Necoro' Neumann <necoro@necoro.eu>2024-02-14 23:38:53 +0100
committerRené 'Necoro' Neumann <necoro@necoro.eu>2024-02-14 23:38:53 +0100
commitb3485c78114e505b7bc9c6969afa108ea3f4755c (patch)
tree440b6ac6d2f29074945294a66929d8a12fe30505 /sql
parent90acc67af9a7d372be5fa9cab7a34412ce4ad824 (diff)
downloadgosten-b3485c78114e505b7bc9c6969afa108ea3f4755c.tar.gz
gosten-b3485c78114e505b7bc9c6969afa108ea3f4755c.tar.bz2
gosten-b3485c78114e505b7bc9c6969afa108ea3f4755c.zip
Remove sqlite support: the differences with sqlc just get too cumbersome
Diffstat (limited to 'sql')
-rw-r--r--sql/ddl/mysql.sql91
1 files changed, 57 insertions, 34 deletions
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