diff options
Diffstat (limited to 'sql')
-rw-r--r-- | sql/ddl/pgsql.sql | 51 | ||||
-rw-r--r-- | sql/sexps.sql | 2 | ||||
-rw-r--r-- | sql/users.sql | 4 |
3 files changed, 54 insertions, 3 deletions
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 diff --git a/sql/sexps.sql b/sql/sexps.sql index ae387ee..8279326 100644 --- a/sql/sexps.sql +++ b/sql/sexps.sql @@ -1,4 +1,4 @@ -- name: GetSingleExpenses :many SELECT id, description FROM single_expenses - WHERE user_id = ?;
\ No newline at end of file + WHERE user_id = $1;
\ No newline at end of file diff --git a/sql/users.sql b/sql/users.sql index 9f1b5ef..2e1d997 100644 --- a/sql/users.sql +++ b/sql/users.sql @@ -5,9 +5,9 @@ SELECT * -- name: GetUserByName :one SELECT * FROM users - WHERE LOWER(name) = LOWER(?); + WHERE LOWER(name) = LOWER($1); -- name: GetUserById :one SELECT * FROM users - WHERE id = ?;
\ No newline at end of file + WHERE id = $1;
\ No newline at end of file |