Switch from mysql to postgres

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
diff --git a/sqlc.yaml b/sqlc.yaml
index df11e1b..e73d019 100644
--- a/sqlc.yaml
+++ b/sqlc.yaml
@@ -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