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 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 51 insertions(+)
 create mode 100644 sql/ddl/pgsql.sql

(limited to 'sql/ddl')

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
-- 
cgit v1.2.3-70-g09d2