diff options
Diffstat (limited to '')
| -rw-r--r-- | sql/ddl/mysql.sql | 91 | ||||
| -rw-r--r-- | sqlc.yaml | 22 | ||||
| -rw-r--r-- | sqlite.go | 46 | ||||
| -rw-r--r-- | sqlite_linux.go | 9 | ||||
| -rw-r--r-- | sqlite_win.go | 9 |
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 @@ -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" |