diff options
Diffstat (limited to '')
| -rw-r--r-- | sql/ddl/pgsql.sql | 51 | ||||
| -rw-r--r-- | sql/sexps.sql | 2 | ||||
| -rw-r--r-- | sql/users.sql | 4 | ||||
| -rw-r--r-- | sqlc.yaml | 11 |
4 files changed, 57 insertions, 11 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 @@ -1,15 +1,10 @@ version: "2" sql: - - engine: "mysql" + - engine: "postgresql" queries: "sql/*.sql" - schema: "sql/ddl/mysql.sql" + schema: "sql/ddl/pgsql.sql" gen: go: out: "model" emit_sql_as_comment: true - overrides: - - db_type: "year" - go_type: "uint16" - - db_type: "tinyint" - unsigned: true - go_type: "uint8"
\ No newline at end of file + sql_package: "pgx/v5"
\ No newline at end of file |