From cd1a6cbd06d3b75adf708fcd2a7974bf94452248 Mon Sep 17 00:00:00 2001
From: René 'Necoro' Neumann <necoro@necoro.eu>
Date: Thu, 22 Feb 2024 22:59:53 +0100
Subject: Switch from mysql to postgres

---
 sql/ddl/pgsql.sql | 51 +++++++++++++++++++++++++++++++++++++++++++++++++++
 sql/sexps.sql     |  2 +-
 sql/users.sql     |  4 ++--
 3 files changed, 54 insertions(+), 3 deletions(-)
 create mode 100644 sql/ddl/pgsql.sql

(limited to 'sql')

diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
new file mode 100644
index 0000000..18c0661
--- /dev/null
+++ b/sql/ddl/pgsql.sql
@@ -0,0 +1,51 @@
+create table users
+(
+    id          serial primary key,
+    name        text not null,
+    pwd         text not null,
+    description text
+);
+
+create unique index users_name ON users(name);
+
+create table categories
+(
+    id        serial primary key,
+    name      text not null,
+    parent_id integer references categories,
+    user_id   integer not null references users
+);
+
+create index parent_id
+    on categories (parent_id);
+
+create index user_id
+    on categories (user_id);
+
+
+create table const_expenses
+(
+    id          serial primary key,
+    description text,
+    expense     numeric(10, 2) not null,
+    months      smallint not null,
+    start       date not null,
+    "end"       date not null,
+    prev_id     integer references const_expenses,
+    category_id integer not null references categories,
+    user_id     integer not null references users
+);
+
+create table single_expenses
+(
+    id          bigserial primary key,
+    description text,
+    expense     numeric(10, 2) not null,
+    year        smallint not null,
+    month       smallint not null,
+    day         smallint not null,
+    category_id integer not null references categories,
+    user_id     integer not null references users
+);
+
+CREATE INDEX idx_single_date ON single_expenses(user_id, year, month);
\ No newline at end of file
diff --git a/sql/sexps.sql b/sql/sexps.sql
index ae387ee..8279326 100644
--- a/sql/sexps.sql
+++ b/sql/sexps.sql
@@ -1,4 +1,4 @@
 -- name: GetSingleExpenses :many
 SELECT id, description
   FROM single_expenses
- WHERE user_id = ?;
\ No newline at end of file
+ WHERE user_id = $1;
\ No newline at end of file
diff --git a/sql/users.sql b/sql/users.sql
index 9f1b5ef..2e1d997 100644
--- a/sql/users.sql
+++ b/sql/users.sql
@@ -5,9 +5,9 @@ SELECT *
 -- name: GetUserByName :one
 SELECT *
   FROM users
- WHERE LOWER(name) = LOWER(?);
+ WHERE LOWER(name) = LOWER($1);
 
 -- name: GetUserById :one
 SELECT *
   FROM users
- WHERE id = ?;
\ No newline at end of file
+ WHERE id = $1;
\ No newline at end of file
-- 
cgit v1.2.3-70-g09d2