First SQL setup

4 files changed, 140 insertions, 0 deletions

diff --git a/sql/ddl/mysql.sql b/sql/ddl/mysql.sql
new file mode 100644
index 0000000..47315f9
--- /dev/null
+++ b/sql/ddl/mysql.sql
@@ -0,0 +1,64 @@
+create table users
+(
+    id          INTEGER      not null
+        primary key,
+    name        VARCHAR(50)  not null
+        unique,
+    pwd         VARCHAR(255) not null,
+    description VARCHAR(100)
+);
+
+create table categories
+(
+    id        INTEGER     not null
+        primary key,
+    name      VARCHAR(50) not null,
+    parent_id INTEGER
+        references categories,
+    user_id   INTEGER     not null
+        references users
+);
+
+create table const_expenses
+(
+    id          INTEGER        not null
+        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
+);
+
+create index idx_start_end
+    on const_expenses (user_id, start, end);
+
+create index ix_constexpense_end
+    on const_expenses (end);
+
+create index ix_constexpense_start
+    on const_expenses (start);
+
+create table single_expenses
+(
+    id          INTEGER        not null
+        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
+);
+
+create index idx_single_date
+    on single_expenses (user_id, year, month);
diff --git a/sql/ddl/sqlite.sql b/sql/ddl/sqlite.sql
new file mode 100644
index 0000000..47315f9
--- /dev/null
+++ b/sql/ddl/sqlite.sql
@@ -0,0 +1,64 @@
+create table users
+(
+    id          INTEGER      not null
+        primary key,
+    name        VARCHAR(50)  not null
+        unique,
+    pwd         VARCHAR(255) not null,
+    description VARCHAR(100)
+);
+
+create table categories
+(
+    id        INTEGER     not null
+        primary key,
+    name      VARCHAR(50) not null,
+    parent_id INTEGER
+        references categories,
+    user_id   INTEGER     not null
+        references users
+);
+
+create table const_expenses
+(
+    id          INTEGER        not null
+        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
+);
+
+create index idx_start_end
+    on const_expenses (user_id, start, end);
+
+create index ix_constexpense_end
+    on const_expenses (end);
+
+create index ix_constexpense_start
+    on const_expenses (start);
+
+create table single_expenses
+(
+    id          INTEGER        not null
+        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
+);
+
+create index idx_single_date
+    on single_expenses (user_id, year, month);
diff --git a/sql/sexps.sql b/sql/sexps.sql
new file mode 100644
index 0000000..ae387ee
--- /dev/null
+++ b/sql/sexps.sql
@@ -0,0 +1,4 @@
+-- name: GetSingleExpenses :many
+SELECT id, description
+  FROM single_expenses
+ WHERE user_id = ?;
\ No newline at end of file
diff --git a/sql/users.sql b/sql/users.sql
new file mode 100644
index 0000000..d16492a
--- /dev/null
+++ b/sql/users.sql
@@ -0,0 +1,8 @@
+-- name: GetUsers :many
+SELECT *
+  FROM users;
+
+-- name: GetUser :one
+SELECT *
+  FROM users
+ WHERE name = ?;
\ No newline at end of file