Remove sqlite support: the differences with sqlc just get too cumbersome

5 files changed, 63 insertions, 114 deletions

diff --git a/sql/ddl/mysql.sql b/sql/ddl/mysql.sql
index 47315f9..5b867c7 100644
--- a/sql/ddl/mysql.sql
+++ b/sql/ddl/mysql.sql
@@ -1,41 +1,56 @@
 create table users
 (
-    id          INTEGER      not null
+    id          int auto_increment
         primary key,
-    name        VARCHAR(50)  not null
-        unique,
-    pwd         VARCHAR(255) not null,
-    description VARCHAR(100)
+    name        varchar(50)  not null,
+    pwd         varchar(255) not null,
+    description varchar(100) null,
+    constraint name
+        unique (name)
 );
 
 create table categories
 (
-    id        INTEGER     not null
+    id        int auto_increment
         primary key,
-    name      VARCHAR(50) not null,
-    parent_id INTEGER
-        references categories,
-    user_id   INTEGER     not null
-        references users
+    name      varchar(50) not null,
+    parent_id int         null,
+    user_id   int         not null,
+    constraint categories_ibfk_1
+        foreign key (parent_id) references categories (id),
+    constraint categories_ibfk_2
+        foreign key (user_id) references users (id)
 );
 
+create index parent_id
+    on categories (parent_id);
+
+create index user_id
+    on categories (user_id);
+
 create table const_expenses
 (
-    id          INTEGER        not null
+    id          int auto_increment
         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
+    description varchar(50)      null,
+    expense     decimal(10, 2)   not null,
+    months      tinyint unsigned not null,
+    start       date             not null,
+    end         date             not null,
+    prev_id     int              null,
+    category_id int              not null,
+    user_id     int              not null,
+    constraint const_expenses_ibfk_1
+        foreign key (prev_id) references const_expenses (id),
+    constraint const_expenses_ibfk_2
+        foreign key (category_id) references categories (id),
+    constraint const_expenses_ibfk_3
+        foreign key (user_id) references users (id)
 );
 
+create index category_id
+    on const_expenses (category_id);
+
 create index idx_start_end
     on const_expenses (user_id, start, end);
 
@@ -45,20 +60,28 @@ create index ix_constexpense_end
 create index ix_constexpense_start
     on const_expenses (start);
 
+create index prev_id
+    on const_expenses (prev_id);
+
 create table single_expenses
 (
-    id          INTEGER        not null
+    id          int auto_increment
         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
+    description varchar(50)         null,
+    expense     decimal(10, 2)      not null,
+    year        year                not null,
+    month       tinyint(6) unsigned not null,
+    day         tinyint(6) unsigned not null,
+    category_id int                 not null,
+    user_id     int                 not null,
+    constraint single_expenses_ibfk_1
+        foreign key (category_id) references categories (id),
+    constraint single_expenses_ibfk_2
+        foreign key (user_id) references users (id)
 );
 
+create index category_id
+    on single_expenses (category_id);
+
 create index idx_single_date
-    on single_expenses (user_id, year, month);
+    on single_expenses (user_id, year, month);
\ No newline at end of file
diff --git a/sqlc.yaml b/sqlc.yaml
index 2ada86d..df11e1b 100644
--- a/sqlc.yaml
+++ b/sqlc.yaml
@@ -1,17 +1,5 @@
 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"
@@ -19,7 +7,9 @@ sql:
       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
+        overrides:
+          - db_type: "year"
+            go_type: "uint16"
+          - db_type: "tinyint"
+            unsigned: true
+            go_type: "uint8"
\ No newline at end of file
diff --git a/sqlite.go b/sqlite.go
deleted file mode 100644
index 8ee6325..0000000
--- a/sqlite.go
+++ /dev/null
@@ -1,46 +0,0 @@
-//go:build sqlite
-
-package main
-
-import (
-	"context"
-	"database/sql"
-	"log"
-	"log/slog"
-
-	sqldblogger "github.com/simukti/sqldb-logger"
-)
-
-type logger struct {
-}
-
-func (l logger) Log(ctx context.Context, level sqldblogger.Level, msg string, data map[string]interface{}) {
-	attrs := make([]slog.Attr, 0, len(data))
-
-	for k, v := range data {
-		attrs = append(attrs, slog.Any(k, v))
-	}
-
-	var lvl slog.Level
-	switch level {
-	case sqldblogger.LevelDebug, sqldblogger.LevelTrace:
-		lvl = slog.LevelDebug
-	case sqldblogger.LevelInfo:
-		lvl = slog.LevelInfo
-	case sqldblogger.LevelError:
-		lvl = slog.LevelError
-	}
-
-	slog.LogAttrs(ctx, lvl, msg, attrs...)
-}
-
-func openDB(dsn string) *sql.DB {
-	db, err := sql.Open(driverName, dsn)
-	if err != nil {
-		log.Fatal(err)
-	}
-	slog.SetLogLoggerLevel(slog.LevelDebug)
-	return sqldblogger.OpenDriver(dsn, db.Driver(), logger{},
-		sqldblogger.WithSQLQueryAsMessage(false),
-		sqldblogger.WithMinimumLevel(sqldblogger.LevelTrace))
-}
diff --git a/sqlite_linux.go b/sqlite_linux.go
deleted file mode 100644
index cf65cb2..0000000
--- a/sqlite_linux.go
+++ /dev/null
@@ -1,9 +0,0 @@
-//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
deleted file mode 100644
index f01541a..0000000
--- a/sqlite_win.go
+++ /dev/null
@@ -1,9 +0,0 @@
-//go:build sqlite && windows
-
-package main
-
-import (
-	_ "modernc.org/sqlite"
-)
-
-const driverName = "sqlite"