diff options
-rw-r--r-- | go.mod | 11 | ||||
-rw-r--r-- | go.sum | 28 | ||||
-rw-r--r-- | main.go | 10 | ||||
-rw-r--r-- | model/db.go | 13 | ||||
-rw-r--r-- | model/models.go | 31 | ||||
-rw-r--r-- | model/sexps.sql.go | 16 | ||||
-rw-r--r-- | model/users.sql.go | 17 | ||||
-rw-r--r-- | mysql.go | 17 | ||||
-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 |
12 files changed, 136 insertions, 75 deletions
@@ -4,14 +4,21 @@ go 1.22 require ( github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e - github.com/go-sql-driver/mysql v1.7.1 github.com/gorilla/csrf v1.7.2 github.com/gorilla/handlers v1.5.2 github.com/gorilla/schema v1.2.1 github.com/gorilla/securecookie v1.1.2 github.com/gorilla/sessions v1.2.2 + github.com/jackc/pgx/v5 v5.5.3 github.com/joho/godotenv v1.5.1 golang.org/x/crypto v0.19.0 ) -require github.com/felixge/httpsnoop v1.0.3 // indirect +require ( + github.com/felixge/httpsnoop v1.0.3 // indirect + github.com/jackc/pgpassfile v1.0.0 // indirect + github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a // indirect + github.com/jackc/puddle/v2 v2.2.1 // indirect + golang.org/x/sync v0.1.0 // indirect + golang.org/x/text v0.14.0 // indirect +) @@ -1,9 +1,10 @@ github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e h1:v3DDTGBMt9pclCdG7jRyNAABmtJw3uky/Xoi/DfbWNs= github.com/Necoro/form v0.0.0-20240211223301-6fa9f8196e1e/go.mod h1:JxpmgZ5hjL6fyhBoZ4HAUadkp7DNqWlHbFL7l8oic4Y= +github.com/davecgh/go-spew v1.1.0/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= +github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= +github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/felixge/httpsnoop v1.0.3 h1:s/nj+GCswXYzN5v2DpNMuMQYe+0DDwt5WVCU6CWBdXk= github.com/felixge/httpsnoop v1.0.3/go.mod h1:m8KPJKqk1gH5J9DgRY2ASl2lWCfGKXixSwevea8zH2U= -github.com/go-sql-driver/mysql v1.7.1 h1:lUIinVbN1DY0xBg0eMOzmmtGoHwWBbvnWubQUrtU8EI= -github.com/go-sql-driver/mysql v1.7.1/go.mod h1:OXbVy3sEdcQ2Doequ6Z5BW6fXNQTmx+9S1MCJN5yJMI= github.com/google/go-cmp v0.6.0 h1:ofyhxvXcZhMsU5ulbFiLKl/XBFqE1GSq7atu8tAmTRI= github.com/google/go-cmp v0.6.0/go.mod h1:17dUlkBOakJ0+DkrSSNjCkIjxS6bF9zb3elmeNGIjoY= github.com/google/gofuzz v1.2.0 h1:xRy4A+RhZaiKjJ1bPfwQ8sedCA+YS2YcCHW6ec7JMi0= @@ -18,7 +19,30 @@ github.com/gorilla/securecookie v1.1.2 h1:YCIWL56dvtr73r6715mJs5ZvhtnY73hBvEF8kX github.com/gorilla/securecookie v1.1.2/go.mod h1:NfCASbcHqRSY+3a8tlWJwsQap2VX5pwzwo4h3eOamfo= github.com/gorilla/sessions v1.2.2 h1:lqzMYz6bOfvn2WriPUjNByzeXIlVzURcPmgMczkmTjY= github.com/gorilla/sessions v1.2.2/go.mod h1:ePLdVu+jbEgHH+KWw8I1z2wqd0BAdAQh/8LRvBeoNcQ= +github.com/jackc/pgpassfile v1.0.0 h1:/6Hmqy13Ss2zCq62VdNG8tM1wchn8zjSGOBJ6icpsIM= +github.com/jackc/pgpassfile v1.0.0/go.mod h1:CEx0iS5ambNFdcRtxPj5JhEz+xB6uRky5eyVu/W2HEg= +github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a h1:bbPeKD0xmW/Y25WS6cokEszi5g+S0QxI/d45PkRi7Nk= +github.com/jackc/pgservicefile v0.0.0-20221227161230-091c0ba34f0a/go.mod h1:5TJZWKEWniPve33vlWYSoGYefn3gLQRzjfDlhSJ9ZKM= +github.com/jackc/pgx/v5 v5.5.3 h1:Ces6/M3wbDXYpM8JyyPD57ivTtJACFZJd885pdIaV2s= +github.com/jackc/pgx/v5 v5.5.3/go.mod h1:ez9gk+OAat140fv9ErkZDYFWmXLfV+++K0uAOiwgm1A= +github.com/jackc/puddle/v2 v2.2.1 h1:RhxXJtFG022u4ibrCSMSiu5aOq1i77R3OHKNJj77OAk= +github.com/jackc/puddle/v2 v2.2.1/go.mod h1:vriiEXHvEE654aYKXXjOvZM39qJ0q+azkZFrfEOc3H4= github.com/joho/godotenv v1.5.1 h1:7eLL/+HRGLY0ldzfGMeQkb7vMd0as4CfYvUVzLqw0N0= github.com/joho/godotenv v1.5.1/go.mod h1:f4LDr5Voq0i2e/R5DDNOoa2zzDfwtkZa6DnEwAbqwq4= +github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= +github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= +github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME= +github.com/stretchr/testify v1.3.0/go.mod h1:M5WIy9Dh21IEIfnGCwXGc5bZfKNJtfHm1UVUgZn+9EI= +github.com/stretchr/testify v1.7.0/go.mod h1:6Fq8oRcR53rry900zMqJjRRixrwX3KX962/h/Wwjteg= +github.com/stretchr/testify v1.8.1 h1:w7B6lhMri9wdJUVmEZPGGhZzrYTPvgJArz7wNPgYKsk= +github.com/stretchr/testify v1.8.1/go.mod h1:w2LPCIKwWwSfY2zedu0+kehJoqGctiVI29o6fzry7u4= golang.org/x/crypto v0.19.0 h1:ENy+Az/9Y1vSrlrvBSyna3PITt4tiZLf7sgCjZBX7Wo= golang.org/x/crypto v0.19.0/go.mod h1:Iy9bg/ha4yyC70EfRS8jz+B6ybOBKMaSxLj6P6oBDfU= +golang.org/x/sync v0.1.0 h1:wsuoTGHzEhffawBOhz5CYhcrV4IdKZbEyZjBMuTp12o= +golang.org/x/sync v0.1.0/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM= +golang.org/x/text v0.14.0 h1:ScX5w1eTa3QqT8oi6+ziP7dTV1S2+ALU0bI+0zXKWiQ= +golang.org/x/text v0.14.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU= +gopkg.in/check.v1 v0.0.0-20161208181325-20d25e280405/go.mod h1:Co6ibVJAznAaIkqp8huTwlJQCZ016jof/cbN4VW5Yz0= +gopkg.in/yaml.v3 v3.0.0-20200313102051-9f266ea9e77c/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= +gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= +gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= @@ -1,11 +1,13 @@ package main import ( + "context" "log" "net/http" "os" "github.com/gorilla/handlers" + "github.com/jackc/pgx/v5/pgxpool" "github.com/joho/godotenv" "gosten/model" @@ -38,10 +40,14 @@ func main() { checkEnv() - db := openDB(os.Getenv("GOSTEN_DSN")) - if err := db.Ping(); err != nil { + db, err := pgxpool.New(context.Background(), os.Getenv("GOSTEN_DSN")) + if err != nil { log.Fatal(err) } + if err := db.Ping(context.Background()); err != nil { + log.Fatal(err) + } + defer db.Close() Q = model.New(db) diff --git a/model/db.go b/model/db.go index 2e42192..5b6feb0 100644 --- a/model/db.go +++ b/model/db.go @@ -6,14 +6,15 @@ package model import ( "context" - "database/sql" + + "github.com/jackc/pgx/v5" + "github.com/jackc/pgx/v5/pgconn" ) type DBTX interface { - ExecContext(context.Context, string, ...interface{}) (sql.Result, error) - PrepareContext(context.Context, string) (*sql.Stmt, error) - QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error) - QueryRowContext(context.Context, string, ...interface{}) *sql.Row + Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error) + Query(context.Context, string, ...interface{}) (pgx.Rows, error) + QueryRow(context.Context, string, ...interface{}) pgx.Row } func New(db DBTX) *Queries { @@ -24,7 +25,7 @@ type Queries struct { db DBTX } -func (q *Queries) WithTx(tx *sql.Tx) *Queries { +func (q *Queries) WithTx(tx pgx.Tx) *Queries { return &Queries{ db: tx, } diff --git a/model/models.go b/model/models.go index be09076..eba1207 100644 --- a/model/models.go +++ b/model/models.go @@ -5,36 +5,35 @@ package model import ( - "database/sql" - "time" + "github.com/jackc/pgx/v5/pgtype" ) type Category struct { ID int32 Name string - ParentID sql.NullInt32 + ParentID pgtype.Int4 UserID int32 } type ConstExpense struct { ID int32 - Description sql.NullString - Expense string - Months uint8 - Start time.Time - End time.Time - PrevID sql.NullInt32 + Description pgtype.Text + Expense pgtype.Numeric + Months int16 + Start pgtype.Date + End pgtype.Date + PrevID pgtype.Int4 CategoryID int32 UserID int32 } type SingleExpense struct { - ID int32 - Description sql.NullString - Expense string - Year uint16 - Month uint8 - Day uint8 + ID int64 + Description pgtype.Text + Expense pgtype.Numeric + Year int16 + Month int16 + Day int16 CategoryID int32 UserID int32 } @@ -43,5 +42,5 @@ type User struct { ID int32 Name string Pwd string - Description sql.NullString + Description pgtype.Text } diff --git a/model/sexps.sql.go b/model/sexps.sql.go index 1108e55..8a54443 100644 --- a/model/sexps.sql.go +++ b/model/sexps.sql.go @@ -7,27 +7,28 @@ package model import ( "context" - "database/sql" + + "github.com/jackc/pgx/v5/pgtype" ) const getSingleExpenses = `-- name: GetSingleExpenses :many SELECT id, description FROM single_expenses - WHERE user_id = ? + WHERE user_id = $1 ` type GetSingleExpensesRow struct { - ID int32 - Description sql.NullString + ID int64 + Description pgtype.Text } // GetSingleExpenses // // SELECT id, description // FROM single_expenses -// WHERE user_id = ? +// WHERE user_id = $1 func (q *Queries) GetSingleExpenses(ctx context.Context, userID int32) ([]GetSingleExpensesRow, error) { - rows, err := q.db.QueryContext(ctx, getSingleExpenses, userID) + rows, err := q.db.Query(ctx, getSingleExpenses, userID) if err != nil { return nil, err } @@ -40,9 +41,6 @@ func (q *Queries) GetSingleExpenses(ctx context.Context, userID int32) ([]GetSin } items = append(items, i) } - if err := rows.Close(); err != nil { - return nil, err - } if err := rows.Err(); err != nil { return nil, err } diff --git a/model/users.sql.go b/model/users.sql.go index 87ae961..e63dacc 100644 --- a/model/users.sql.go +++ b/model/users.sql.go @@ -12,16 +12,16 @@ import ( const getUserById = `-- name: GetUserById :one SELECT id, name, pwd, description FROM users - WHERE id = ? + WHERE id = $1 ` // GetUserById // // SELECT id, name, pwd, description // FROM users -// WHERE id = ? +// WHERE id = $1 func (q *Queries) GetUserById(ctx context.Context, id int32) (User, error) { - row := q.db.QueryRowContext(ctx, getUserById, id) + row := q.db.QueryRow(ctx, getUserById, id) var i User err := row.Scan( &i.ID, @@ -35,16 +35,16 @@ func (q *Queries) GetUserById(ctx context.Context, id int32) (User, error) { const getUserByName = `-- name: GetUserByName :one SELECT id, name, pwd, description FROM users - WHERE LOWER(name) = LOWER(?) + WHERE LOWER(name) = LOWER($1) ` // GetUserByName // // SELECT id, name, pwd, description // FROM users -// WHERE LOWER(name) = LOWER(?) +// WHERE LOWER(name) = LOWER($1) func (q *Queries) GetUserByName(ctx context.Context, lower string) (User, error) { - row := q.db.QueryRowContext(ctx, getUserByName, lower) + row := q.db.QueryRow(ctx, getUserByName, lower) var i User err := row.Scan( &i.ID, @@ -65,7 +65,7 @@ SELECT id, name, pwd, description // SELECT id, name, pwd, description // FROM users func (q *Queries) GetUsers(ctx context.Context) ([]User, error) { - rows, err := q.db.QueryContext(ctx, getUsers) + rows, err := q.db.Query(ctx, getUsers) if err != nil { return nil, err } @@ -83,9 +83,6 @@ func (q *Queries) GetUsers(ctx context.Context) ([]User, error) { } items = append(items, i) } - if err := rows.Close(); err != nil { - return nil, err - } if err := rows.Err(); err != nil { return nil, err } diff --git a/mysql.go b/mysql.go deleted file mode 100644 index 2f35b19..0000000 --- a/mysql.go +++ /dev/null @@ -1,17 +0,0 @@ -package main - -import ( - "database/sql" - "log" - - _ "github.com/go-sql-driver/mysql" -) - -func openDB(dsn string) *sql.DB { - db, err := sql.Open("mysql", dsn) - if err != nil { - log.Fatal(err) - } - - return db -} 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 |