summaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--sql/ddl/pgsql.sql74
1 files changed, 34 insertions, 40 deletions
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