First SQL setup

12 files changed, 343 insertions, 2 deletions

diff --git a/.editorconfig b/.editorconfig
index 54a0aea..7a19299 100644
--- a/.editorconfig
+++ b/.editorconfig
@@ -2,12 +2,15 @@ root = true
 
 [*]
 charset = utf-8
-end_of_line = crlf
+end_of_line = lf
 indent_size = 4
 indent_style = space
 insert_final_newline = false
 max_line_length = 120
 tab_width = 4
 
+[{*.yml,*.yaml}]
+indent_size = 2
+
 [{*.go,*.go2}]
 indent_style = tab
diff --git a/.gitignore b/.gitignore
index 3849af4..881a4c7 100644
--- a/.gitignore
+++ b/.gitignore
@@ -2,4 +2,6 @@
 *.iml
 out
 gen
-!/.gitconfig
+/test.sqlite
+*.exe
+/gosten
\ No newline at end of file
diff --git a/go.mod b/go.mod
index a1a2a0f..123ff19 100644
--- a/go.mod
+++ b/go.mod
@@ -1,3 +1,29 @@
 module gosten
 
 go 1.22
+
+require (
+	github.com/mattn/go-sqlite3 v1.14.22
+	modernc.org/sqlite v1.28.0
+)
+
+require (
+	github.com/dustin/go-humanize v1.0.1 // indirect
+	github.com/google/uuid v1.3.0 // indirect
+	github.com/kballard/go-shellquote v0.0.0-20180428030007-95032a82bc51 // indirect
+	github.com/mattn/go-isatty v0.0.16 // indirect
+	github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec // indirect
+	golang.org/x/mod v0.3.0 // indirect
+	golang.org/x/sys v0.9.0 // indirect
+	golang.org/x/tools v0.0.0-20201124115921-2c860bdd6e78 // indirect
+	golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 // indirect
+	lukechampine.com/uint128 v1.2.0 // indirect
+	modernc.org/cc/v3 v3.40.0 // indirect
+	modernc.org/ccgo/v3 v3.16.13 // indirect
+	modernc.org/libc v1.29.0 // indirect
+	modernc.org/mathutil v1.6.0 // indirect
+	modernc.org/memory v1.7.2 // indirect
+	modernc.org/opt v0.1.3 // indirect
+	modernc.org/strutil v1.1.3 // indirect
+	modernc.org/token v1.0.1 // indirect
+)
diff --git a/go.sum b/go.sum
new file mode 100644
index 0000000..17ade58
--- /dev/null
+++ b/go.sum
@@ -0,0 +1,73 @@
+github.com/dustin/go-humanize v1.0.1 h1:GzkhY7T5VNhEkwH0PVJgjz+fX1rhBrR7pRT3mDkpeCY=
+github.com/dustin/go-humanize v1.0.1/go.mod h1:Mu1zIs6XwVuF/gI1OepvI0qD18qycQx+mFykh5fBlto=
+github.com/google/go-cmp v0.5.9 h1:O2Tfq5qg4qc4AmwVlvv0oLiVAGB7enBSJ2x2DqQFi38=
+github.com/google/go-cmp v0.5.9/go.mod h1:17dUlkBOakJ0+DkrSSNjCkIjxS6bF9zb3elmeNGIjoY=
+github.com/google/pprof v0.0.0-20221118152302-e6195bd50e26 h1:Xim43kblpZXfIBQsbuBVKCudVG457BR2GZFIz3uw3hQ=
+github.com/google/pprof v0.0.0-20221118152302-e6195bd50e26/go.mod h1:dDKJzRmX4S37WGHujM7tX//fmj1uioxKzKxz3lo4HJo=
+github.com/google/uuid v1.3.0 h1:t6JiXgmwXMjEs8VusXIJk2BXHsn+wx8BZdTaoZ5fu7I=
+github.com/google/uuid v1.3.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo=
+github.com/kballard/go-shellquote v0.0.0-20180428030007-95032a82bc51 h1:Z9n2FFNUXsshfwJMBgNA0RU6/i7WVaAegv3PtuIHPMs=
+github.com/kballard/go-shellquote v0.0.0-20180428030007-95032a82bc51/go.mod h1:CzGEWj7cYgsdH8dAjBGEr58BoE7ScuLd+fwFZ44+/x8=
+github.com/mattn/go-isatty v0.0.16 h1:bq3VjFmv/sOjHtdEhmkEV4x1AJtvUvOJ2PFAZ5+peKQ=
+github.com/mattn/go-isatty v0.0.16/go.mod h1:kYGgaQfpe5nmfYZH+SKPsOc2e4SrIfOl2e/yFXSvRLM=
+github.com/mattn/go-sqlite3 v1.14.22 h1:2gZY6PC6kBnID23Tichd1K+Z0oS6nE/XwU+Vz/5o4kU=
+github.com/mattn/go-sqlite3 v1.14.22/go.mod h1:Uh1q+B4BYcTPb+yiD3kU8Ct7aC0hY9fxUwlHK0RXw+Y=
+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/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec h1:W09IVJc94icq4NjY3clb7Lk8O1qJ8BdBEF8z0ibU0rE=
+github.com/remyoudompheng/bigfft v0.0.0-20230129092748-24d4a6f8daec/go.mod h1:qqbHyh8v60DhA7CoWK5oRCqLrMHRGoxYCSS9EjAz6Eo=
+github.com/yuin/goldmark v1.2.1/go.mod h1:3hX8gzYuyVAZsxl0MRgGTJEmQBFcNTphYh9decYSb74=
+golang.org/x/crypto v0.0.0-20190308221718-c2843e01d9a2/go.mod h1:djNgcEr1/C05ACkg1iLfiJU5Ep61QUkGW8qpdssI0+w=
+golang.org/x/crypto v0.0.0-20191011191535-87dc89f01550/go.mod h1:yigFU9vqHzYiE8UmvKecakEJjdnWj3jj499lnFckfCI=
+golang.org/x/crypto v0.0.0-20200622213623-75b288015ac9/go.mod h1:LzIPMQfyMNhhGPhUkYOs5KpL4U8rLKemX1yGLhDgUto=
+golang.org/x/mod v0.3.0 h1:RM4zey1++hCTbCVQfnWeKs9/IEsaBLA8vTkd0WVtmH4=
+golang.org/x/mod v0.3.0/go.mod h1:s0Qsj1ACt9ePp/hMypM3fl4fZqREWJwdYDEqhRiZZUA=
+golang.org/x/net v0.0.0-20190404232315-eb5bcb51f2a3/go.mod h1:t9HGtf8HONx5eT2rtn7q6eTqICYqUVnKs3thJo3Qplg=
+golang.org/x/net v0.0.0-20190620200207-3b0461eec859/go.mod h1:z5CRVTTTmAJ677TzLLGU+0bjPO0LkuOLi4/5GtJWs/s=
+golang.org/x/net v0.0.0-20201021035429-f5854403a974/go.mod h1:sp8m0HH+o8qH0wwXwYZr8TS3Oi6o0r6Gce1SSxlDquU=
+golang.org/x/sync v0.0.0-20190423024810-112230192c58/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/sync v0.0.0-20201020160332-67f06af15bc9/go.mod h1:RxMgew5VJxzue5/jJTE5uejpjVlOe/izrB70Jof72aM=
+golang.org/x/sys v0.0.0-20190215142949-d0b11bdaac8a/go.mod h1:STP8DvDyc/dI5b8T5hshtkjS+E42TnysNCUPdjciGhY=
+golang.org/x/sys v0.0.0-20190412213103-97732733099d/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20200930185726-fdedc70b468f/go.mod h1:h1NjWce9XRLGQEsW7wpKNCjG9DtNlClVuFLEZdDNbEs=
+golang.org/x/sys v0.0.0-20220811171246-fbc7d0a398ab/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
+golang.org/x/sys v0.9.0 h1:KS/R3tvhPqvJvwcKfnBHJwwthS11LRhmM5D59eEXa0s=
+golang.org/x/sys v0.9.0/go.mod h1:oPkhp1MJrh7nUepCBck5+mAzfO9JrbApNNgaTdGDITg=
+golang.org/x/text v0.3.0/go.mod h1:NqM8EUOU14njkJ3fqMW+pc6Ldnwhi/IjpwHt7yyuwOQ=
+golang.org/x/text v0.3.3/go.mod h1:5Zoc/QRtKVWzQhOtBMvqHzDpF6irO9z98xDceosuGiQ=
+golang.org/x/tools v0.0.0-20180917221912-90fa682c2a6e/go.mod h1:n7NCudcB/nEzxVGmLbDWY5pfWTLqBcC2KZ6jyYvM4mQ=
+golang.org/x/tools v0.0.0-20191119224855-298f0cb1881e/go.mod h1:b+2E5dAYhXwXZwtnZ6UAqBI28+e2cm9otk0dWdXHAEo=
+golang.org/x/tools v0.0.0-20201124115921-2c860bdd6e78 h1:M8tBwCtWD/cZV9DZpFYRUgaymAYAr+aIUTWzDaM3uPs=
+golang.org/x/tools v0.0.0-20201124115921-2c860bdd6e78/go.mod h1:emZCQorbCU4vsT4fOWvOPXz4eW1wZW4PmDk9uLelYpA=
+golang.org/x/xerrors v0.0.0-20190717185122-a985d3407aa7/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
+golang.org/x/xerrors v0.0.0-20191011141410-1b5146add898/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
+golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1 h1:go1bK/D/BFZV2I8cIQd1NKEZ+0owSTG1fDTci4IqFcE=
+golang.org/x/xerrors v0.0.0-20200804184101-5ec99f83aff1/go.mod h1:I/5z698sn9Ka8TeJc9MKroUUfqBBauWjQqLJ2OPfmY0=
+lukechampine.com/uint128 v1.2.0 h1:mBi/5l91vocEN8otkC5bDLhi2KdCticRiwbdB0O+rjI=
+lukechampine.com/uint128 v1.2.0/go.mod h1:c4eWIwlEGaxC/+H1VguhU4PHXNWDCDMUlWdIWl2j1gk=
+modernc.org/cc/v3 v3.40.0 h1:P3g79IUS/93SYhtoeaHW+kRCIrYaxJ27MFPv+7kaTOw=
+modernc.org/cc/v3 v3.40.0/go.mod h1:/bTg4dnWkSXowUO6ssQKnOV0yMVxDYNIsIrzqTFDGH0=
+modernc.org/ccgo/v3 v3.16.13 h1:Mkgdzl46i5F/CNR/Kj80Ri59hC8TKAhZrYSaqvkwzUw=
+modernc.org/ccgo/v3 v3.16.13/go.mod h1:2Quk+5YgpImhPjv2Qsob1DnZ/4som1lJTodubIcoUkY=
+modernc.org/ccorpus v1.11.6 h1:J16RXiiqiCgua6+ZvQot4yUuUy8zxgqbqEEUuGPlISk=
+modernc.org/ccorpus v1.11.6/go.mod h1:2gEUTrWqdpH2pXsmTM1ZkjeSrUWDpjMu2T6m29L/ErQ=
+modernc.org/httpfs v1.0.6 h1:AAgIpFZRXuYnkjftxTAZwMIiwEqAfk8aVB2/oA6nAeM=
+modernc.org/httpfs v1.0.6/go.mod h1:7dosgurJGp0sPaRanU53W4xZYKh14wfzX420oZADeHM=
+modernc.org/libc v1.29.0 h1:tTFRFq69YKCF2QyGNuRUQxKBm1uZZLubf6Cjh/pVHXs=
+modernc.org/libc v1.29.0/go.mod h1:DaG/4Q3LRRdqpiLyP0C2m1B8ZMGkQ+cCgOIjEtQlYhQ=
+modernc.org/mathutil v1.6.0 h1:fRe9+AmYlaej+64JsEEhoWuAYBkOtQiMEU7n/XgfYi4=
+modernc.org/mathutil v1.6.0/go.mod h1:Ui5Q9q1TR2gFm0AQRqQUaBWFLAhQpCwNcuhBOSedWPo=
+modernc.org/memory v1.7.2 h1:Klh90S215mmH8c9gO98QxQFsY+W451E8AnzjoE2ee1E=
+modernc.org/memory v1.7.2/go.mod h1:NO4NVCQy0N7ln+T9ngWqOQfi7ley4vpwvARR+Hjw95E=
+modernc.org/opt v0.1.3 h1:3XOZf2yznlhC+ibLltsDGzABUGVx8J6pnFMS3E4dcq4=
+modernc.org/opt v0.1.3/go.mod h1:WdSiB5evDcignE70guQKxYUl14mgWtbClRi5wmkkTX0=
+modernc.org/sqlite v1.28.0 h1:Zx+LyDDmXczNnEQdvPuEfcFVA2ZPyaD7UCZDjef3BHQ=
+modernc.org/sqlite v1.28.0/go.mod h1:Qxpazz0zH8Z1xCFyi5GSL3FzbtZ3fvbjmywNogldEW0=
+modernc.org/strutil v1.1.3 h1:fNMm+oJklMGYfU9Ylcywl0CO5O6nTfaowNsh2wpPjzY=
+modernc.org/strutil v1.1.3/go.mod h1:MEHNA7PdEnEwLvspRMtWTNnp2nnyvMfkimT1NKNAGbw=
+modernc.org/tcl v1.15.2 h1:C4ybAYCGJw968e+Me18oW55kD/FexcHbqH2xak1ROSY=
+modernc.org/tcl v1.15.2/go.mod h1:3+k/ZaEbKrC8ePv8zJWPtBSW0V7Gg9g8rkmhI1Kfs3c=
+modernc.org/token v1.0.1 h1:A3qvTqOwexpfZZeyI0FeGPDlSWX5pjZu9hF4lU+EKWg=
+modernc.org/token v1.0.1/go.mod h1:UGzOrNV1mAFSEB63lOFHIpNRUVMvYTc6yu1SMY/XTDM=
+modernc.org/z v1.7.3 h1:zDJf6iHjrnB+WRD88stbXokugjyc0/pB91ri1gO6LZY=
+modernc.org/z v1.7.3/go.mod h1:Ipv4tsdxZRbQyLq9Q1M6gdbkxYzdlrciF2Hi/lS7nWE=
diff --git a/main.go b/main.go
new file mode 100644
index 0000000..92ae59e
--- /dev/null
+++ b/main.go
@@ -0,0 +1,54 @@
+package main
+
+import (
+	"context"
+	"database/sql"
+	"flag"
+	"fmt"
+	"gosten/model"
+	"log"
+	"net"
+	"net/http"
+	"strconv"
+)
+
+// flags
+var (
+	port uint64
+	host string
+)
+
+func init() {
+	flag.StringVar(&host, "h", "localhost", "address to listen on")
+	flag.Uint64Var(&port, "p", 8080, "port to listen on")
+}
+
+func main() {
+	flag.Parse()
+
+	db, err := sql.Open(driverName, "test.sqlite")
+	if err != nil {
+		log.Fatal(err)
+	}
+
+	queries := model.New(db)
+
+	helloHandler := func(w http.ResponseWriter, req *http.Request) {
+		fmt.Fprintln(w, "Hello, world!")
+		fmt.Fprintf(w, "Running on %s:%d\n", host, port)
+
+		users, err := queries.GetUsers(context.Background())
+		if err != nil {
+			log.Fatal(err)
+		}
+
+		fmt.Fprintln(w, "\nUSERS")
+		for _, u := range users {
+			fmt.Fprintln(w, u.Name)
+		}
+	}
+	http.HandleFunc("/", helloHandler)
+
+	address := net.JoinHostPort(host, strconv.FormatUint(port, 10))
+	log.Fatal(http.ListenAndServe(address, nil))
+}
diff --git a/sql/ddl/mysql.sql b/sql/ddl/mysql.sql
new file mode 100644
index 0000000..47315f9
--- /dev/null
+++ b/sql/ddl/mysql.sql
@@ -0,0 +1,64 @@
+create table users
+(
+    id          INTEGER      not null
+        primary key,
+    name        VARCHAR(50)  not null
+        unique,
+    pwd         VARCHAR(255) not null,
+    description VARCHAR(100)
+);
+
+create table categories
+(
+    id        INTEGER     not null
+        primary key,
+    name      VARCHAR(50) not null,
+    parent_id INTEGER
+        references categories,
+    user_id   INTEGER     not null
+        references users
+);
+
+create table const_expenses
+(
+    id          INTEGER        not null
+        primary key,
+    description VARCHAR(50),
+    expense     NUMERIC(10, 2) not null,
+    months      SMALLINT       not null,
+    start       DATE           not null,
+    end         DATE           not null,
+    prev_id     INTEGER
+        references const_expenses,
+    user_id     INTEGER        not null
+        references users,
+    category_id INTEGER        not null
+        references categories
+);
+
+create index idx_start_end
+    on const_expenses (user_id, start, end);
+
+create index ix_constexpense_end
+    on const_expenses (end);
+
+create index ix_constexpense_start
+    on const_expenses (start);
+
+create table single_expenses
+(
+    id          INTEGER        not null
+        primary key,
+    description VARCHAR(50),
+    expense     NUMERIC(10, 2) not null,
+    year        INTEGER        not null,
+    month       SMALLINT       not null,
+    day         SMALLINT       not null,
+    user_id     INTEGER        not null
+        references users,
+    category_id INTEGER        not null
+        references categories
+);
+
+create index idx_single_date
+    on single_expenses (user_id, year, month);
diff --git a/sql/ddl/sqlite.sql b/sql/ddl/sqlite.sql
new file mode 100644
index 0000000..47315f9
--- /dev/null
+++ b/sql/ddl/sqlite.sql
@@ -0,0 +1,64 @@
+create table users
+(
+    id          INTEGER      not null
+        primary key,
+    name        VARCHAR(50)  not null
+        unique,
+    pwd         VARCHAR(255) not null,
+    description VARCHAR(100)
+);
+
+create table categories
+(
+    id        INTEGER     not null
+        primary key,
+    name      VARCHAR(50) not null,
+    parent_id INTEGER
+        references categories,
+    user_id   INTEGER     not null
+        references users
+);
+
+create table const_expenses
+(
+    id          INTEGER        not null
+        primary key,
+    description VARCHAR(50),
+    expense     NUMERIC(10, 2) not null,
+    months      SMALLINT       not null,
+    start       DATE           not null,
+    end         DATE           not null,
+    prev_id     INTEGER
+        references const_expenses,
+    user_id     INTEGER        not null
+        references users,
+    category_id INTEGER        not null
+        references categories
+);
+
+create index idx_start_end
+    on const_expenses (user_id, start, end);
+
+create index ix_constexpense_end
+    on const_expenses (end);
+
+create index ix_constexpense_start
+    on const_expenses (start);
+
+create table single_expenses
+(
+    id          INTEGER        not null
+        primary key,
+    description VARCHAR(50),
+    expense     NUMERIC(10, 2) not null,
+    year        INTEGER        not null,
+    month       SMALLINT       not null,
+    day         SMALLINT       not null,
+    user_id     INTEGER        not null
+        references users,
+    category_id INTEGER        not null
+        references categories
+);
+
+create index idx_single_date
+    on single_expenses (user_id, year, month);
diff --git a/sql/sexps.sql b/sql/sexps.sql
new file mode 100644
index 0000000..ae387ee
--- /dev/null
+++ b/sql/sexps.sql
@@ -0,0 +1,4 @@
+-- name: GetSingleExpenses :many
+SELECT id, description
+  FROM single_expenses
+ WHERE user_id = ?;
\ No newline at end of file
diff --git a/sql/users.sql b/sql/users.sql
new file mode 100644
index 0000000..d16492a
--- /dev/null
+++ b/sql/users.sql
@@ -0,0 +1,8 @@
+-- name: GetUsers :many
+SELECT *
+  FROM users;
+
+-- name: GetUser :one
+SELECT *
+  FROM users
+ WHERE name = ?;
\ No newline at end of file
diff --git a/sqlc.yaml b/sqlc.yaml
new file mode 100644
index 0000000..2ada86d
--- /dev/null
+++ b/sqlc.yaml
@@ -0,0 +1,25 @@
+version: "2"
+sql:
+  - engine: "sqlite"
+    queries: "sql/*.sql"
+    schema: "sql/ddl/sqlite.sql"
+    gen:
+      go:
+        out: "model"
+        emit_sql_as_comment: true
+        build_tags: "sqlite"
+        output_db_file_name: "db_sqlite.go"
+        output_models_file_name: "models_sqlite.go"
+        output_files_suffix: ".sqlite"
+
+  - engine: "mysql"
+    queries: "sql/*.sql"
+    schema: "sql/ddl/mysql.sql"
+    gen:
+      go:
+        out: "model"
+        emit_sql_as_comment: true
+        build_tags: "!sqlite"
+        output_db_file_name: "db_mysql.go"
+        output_models_file_name: "models_mysql.go"
+        output_files_suffix: ".mysql"
\ No newline at end of file
diff --git a/sqlite.go b/sqlite.go
new file mode 100644
index 0000000..cf65cb2
--- /dev/null
+++ b/sqlite.go
@@ -0,0 +1,9 @@
+//go:build sqlite && !windows
+
+package main
+
+import (
+	_ "github.com/mattn/go-sqlite3"
+)
+
+const driverName = "sqlite3"
diff --git a/sqlite_win.go b/sqlite_win.go
new file mode 100644
index 0000000..f01541a
--- /dev/null
+++ b/sqlite_win.go
@@ -0,0 +1,9 @@
+//go:build sqlite && windows
+
+package main
+
+import (
+	_ "modernc.org/sqlite"
+)
+
+const driverName = "sqlite"