diff options
| -rw-r--r-- | main.go | 2 | ||||
| -rw-r--r-- | model/models.go | 7 | ||||
| -rw-r--r-- | sql/ddl/pgsql.sql | 74 |
3 files changed, 38 insertions, 45 deletions
@@ -82,6 +82,6 @@ func indexPage() http.HandlerFunc { return func(w http.ResponseWriter, r *http.Request) { uid := userId(r) u, _ := Q.GetUserById(r.Context(), uid) - showTemplate(w, "index", u.Name) + showTemplate(w, "content", u.Name) } } diff --git a/model/models.go b/model/models.go index eba1207..2ac5a4b 100644 --- a/model/models.go +++ b/model/models.go @@ -19,7 +19,7 @@ type ConstExpense struct { ID int32 Description pgtype.Text Expense pgtype.Numeric - Months int16 + Duration pgtype.Interval Start pgtype.Date End pgtype.Date PrevID pgtype.Int4 @@ -31,9 +31,8 @@ type SingleExpense struct { ID int64 Description pgtype.Text Expense pgtype.Numeric - Year int16 - Month int16 - Day int16 + Date pgtype.Date + CorrMonth pgtype.Date CategoryID int32 UserID int32 } 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 |