diff options
Diffstat (limited to '')
| -rw-r--r-- | sql/categories.sql | 5 | ||||
| -rw-r--r-- | sql/ddl/pgsql.sql | 74 | ||||
| -rw-r--r-- | sql/rexps.sql | 4 | ||||
| -rw-r--r-- | sql/sexps.sql | 2 | ||||
| -rw-r--r-- | sql/users.sql | 12 |
5 files changed, 55 insertions, 42 deletions
diff --git a/sql/categories.sql b/sql/categories.sql new file mode 100644 index 0000000..2694adc --- /dev/null +++ b/sql/categories.sql @@ -0,0 +1,5 @@ +-- name: GetCategoriesOrdered :many +SELECT * + FROM categories + WHERE user_id = $1 + ORDER BY name ASC;
\ No newline at end of file diff --git a/sql/ddl/pgsql.sql b/sql/ddl/pgsql.sql index 18c0661..18dc388 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 recur_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 recur_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 diff --git a/sql/rexps.sql b/sql/rexps.sql new file mode 100644 index 0000000..e76c809 --- /dev/null +++ b/sql/rexps.sql @@ -0,0 +1,4 @@ +-- name: GetRecurExpenses :many +SELECT * + FROM recur_expenses + WHERE user_id = $1;
\ No newline at end of file diff --git a/sql/sexps.sql b/sql/sexps.sql index 8279326..884e1f3 100644 --- a/sql/sexps.sql +++ b/sql/sexps.sql @@ -1,4 +1,4 @@ -- name: GetSingleExpenses :many -SELECT id, description +SELECT * FROM single_expenses WHERE user_id = $1;
\ No newline at end of file diff --git a/sql/users.sql b/sql/users.sql index 2e1d997..e37e782 100644 --- a/sql/users.sql +++ b/sql/users.sql @@ -10,4 +10,14 @@ SELECT * -- name: GetUserById :one SELECT * FROM users - WHERE id = $1;
\ No newline at end of file + WHERE id = $1; + +-- name: GetPwdById :one + SELECT pwd + FROM users + WHERE id = $1; + +-- name: UpdatePwd :exec + UPDATE users + SET pwd = $1 + WHERE id = $2;
\ No newline at end of file |