From 4ebd772e57469345120187fe01d956a3295d1468 Mon Sep 17 00:00:00 2001
From: René 'Necoro' Neumann <necoro@necoro.eu>
Date: Sun, 25 Feb 2024 21:15:40 +0100
Subject: Small model changes / Reformatting

---
 sql/ddl/pgsql.sql | 74 +++++++++++++++++++++++++------------------------------
 1 file changed, 34 insertions(+), 40 deletions(-)

(limited to 'sql/ddl')

diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql
index 18c0661..09c7133 100644
--- a/sql/ddl/pgsql.sql
+++ b/sql/ddl/pgsql.sql
@@ -1,51 +1,45 @@
-create table users
-(
-    id          serial primary key,
-    name        text not null,
-    pwd         text not null,
-    description text
+CREATE TABLE users (
+    id          SERIAL PRIMARY KEY,
+    name        TEXT NOT NULL UNIQUE,
+    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 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 parent_id
+    ON categories (parent_id);
 
-create index user_id
-    on categories (user_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 const_expenses (
+    id          SERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    duration    INTERVAL       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 TABLE single_expenses (
+    id          BIGSERIAL PRIMARY KEY,
+    description TEXT,
+    expense     NUMERIC(10, 2) NOT NULL,
+    date        DATE           NOT NULL,
+    -- we need the cast to timestamp, because it is only considered immutable then
+    corr_month  DATE GENERATED ALWAYS AS (DATE_TRUNC('month', date::timestamp)) STORED,
+    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
+CREATE INDEX idx_single_month ON single_expenses (user_id, corr_month);
\ No newline at end of file
-- 
cgit v1.2.3-70-g09d2