From 805a9fc89834735cd0e2f979695e736b7cb4b292 Mon Sep 17 00:00:00 2001 From: anqiansong Date: Tue, 9 Aug 2022 10:04:49 +0800 Subject: [PATCH 1/5] Add sql --- example/sql/create/example.sql | 16 +++ example/sql/create/scanner.go | 8 ++ example/sql/create/user_model.gen.go | 55 ++++++++ example/sql/create/user_model.go | 8 ++ example/sql/delete/example.sql | 28 ++++ example/sql/read/example.sql | 200 +++++++++++++++++++++++++++ example/sql/update/example.sql | 41 ++++++ internal/gen/sql/scanner.tpl | 8 ++ internal/gen/sql/sql.go | 69 ++++++++- internal/gen/sql/sql_custom.tpl | 9 ++ internal/gen/sql/sql_gen.tpl | 66 +++++++++ internal/spec/table.go | 3 + 12 files changed, 509 insertions(+), 2 deletions(-) create mode 100644 example/sql/create/example.sql create mode 100644 example/sql/create/scanner.go create mode 100644 example/sql/create/user_model.gen.go create mode 100644 example/sql/create/user_model.go create mode 100644 example/sql/delete/example.sql create mode 100644 example/sql/read/example.sql create mode 100644 example/sql/update/example.sql create mode 100644 internal/gen/sql/scanner.tpl create mode 100644 internal/gen/sql/sql_custom.tpl create mode 100644 internal/gen/sql/sql_gen.tpl diff --git a/example/sql/create/example.sql b/example/sql/create/example.sql new file mode 100644 index 0000000..e492c51 --- /dev/null +++ b/example/sql/create/example.sql @@ -0,0 +1,16 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; \ No newline at end of file diff --git a/example/sql/create/scanner.go b/example/sql/create/scanner.go new file mode 100644 index 0000000..fd4a6a2 --- /dev/null +++ b/example/sql/create/scanner.go @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows []*sql.Row, v interface{}) error +} diff --git a/example/sql/create/user_model.gen.go b/example/sql/create/user_model.gen.go new file mode 100644 index 0000000..2f9de4f --- /dev/null +++ b/example/sql/create/user_model.gen.go @@ -0,0 +1,55 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" +) + +// UserModel represents a user model. +type UserModel struct { + scanner Scanner + db sql.Conn +} + +// User represents a user struct data. +type User struct { + Id uint64 + Name string + Password string + Mobile string + Gender string + Nickname string + Type int8 + CreateTime time.Time + UpdateTime time.Time +} + +func (m *UserModel) SetScanner(scanner Scanner) { + m.scanner = scanner +} + +// Insert creates user data. +func (m *UserModel) Insert(ctx context.Context, data ...*User) error { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + stmt, err := m.db.PrepareContext(ctx, "INSERT INTO `user` (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") + if err != nil { + return err + } + defer stmt.Close() + + for _, v := range data { + _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) + if err != nil { + return err + } + } + + return nil +} diff --git a/example/sql/create/user_model.go b/example/sql/create/user_model.go new file mode 100644 index 0000000..8528c27 --- /dev/null +++ b/example/sql/create/user_model.go @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *UserModel) Customize(ctx context.Context, args ...any) { + +} diff --git a/example/sql/delete/example.sql b/example/sql/delete/example.sql new file mode 100644 index 0000000..e63b1c6 --- /dev/null +++ b/example/sql/delete/example.sql @@ -0,0 +1,28 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: delete by primary key +-- fn: Delete +delete from user where id = ?; + +-- example2: delete by unique key +-- fn: DeleteByName +delete from user where name = ?; + +-- example3: delete by unique keys +-- fn: DeleteByNameAndMobile +delete from user where name = ? and mobile = ?; \ No newline at end of file diff --git a/example/sql/read/example.sql b/example/sql/read/example.sql new file mode 100644 index 0000000..c190d3f --- /dev/null +++ b/example/sql/read/example.sql @@ -0,0 +1,200 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: find one by primary key +-- if you want to find one result, you have to explicitly declare limit 1 statement. +-- fn: FindOne +select * +from user +where id = ? limit 1; + +-- example2: find one by unique key +-- fn: FindByName +select * +from user +where name = ? limit 1; + +-- example3: find part of fields by primary key +-- fn: FindOnePart +select id, name, nickname +from user +where id = ? limit 1; + +-- example4: find part of fields by unique key +-- fn: FindByNamePart +select id, name, nickname +from user +where name = ? limit 1; + +-- example5: find all +-- fn: FindAll +select * +from user; + +-- example6: find all count, if call function, you must use AS keyword to alias result. +-- fn: FindAllCount +select count(*) AS count +from user; + +-- example7: find all part of fields +-- fn: FindAllPart +select id, name, nickname +from user; + +-- example8: find all part of fields count, if call function, you must use AS keyword to alias result. +-- fn: FindAllPartCount +select count(id) AS count +from user; + +-- example9: find one by name and password +-- fn: FindOneByNameAndPassword +select * +from user +where name = ? + and password = ? limit 1; + +-- example10: list user by primary key, group by name +-- fn: ListUserByNameAsc +select * +from user +where id > ? +group by name; + +-- example11: list user by primary key, group by name asc, having count(type) > ? +-- having clause must be a alias, do not use function expression, for example: +-- select * from user where id > ? group by name asc having count(type) > ?; in this +-- statement, count(type) is a function expression, it will not work, you can use +-- select *,count(type) AS typeCount from user where id > ? group by name asc having typeCount > ?; instead. +-- fn: ListUserByNameAscHavingCountTypeGt +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ?; + +-- example13: list user by primary key, group by name desc, having count(type) > ?, order by id desc +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDesc +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc; + +-- example14: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10; + +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, offset 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10 +offset 10; + +-- example16: find one by name like +-- fn: FindOneByNameLike +select * +from user +where name like ? limit 1; + +-- example17: find all by name not like +-- fn: FindAllByNameNotLike +select * +from user +where name not like ?; + +-- example18: find all by id in +-- fn: FindAllByIdIn +select * +from user +where id in (?); + +-- example19: find all by id not in +-- fn: FindAllByIdNotIn +select * +from user +where id not in (?); + +-- example20: find all by id between +-- fn: FindAllByIdBetween +select * +from user +where id between ? and ?; + +-- example21: find all by id not between +-- fn: FindAllByIdNotBetween +select * +from user +where id not between ? and ?; + +-- example22: find all by id greater than or equal to +-- fn: FindAllByIdGte +select * +from user +where id >= ?; + +-- example23: find all by id less than or equal to +-- fn: FindAllByIdLte +select * +from user +where id <= ?; + +-- example24: find all by id not equal to +-- fn: FindAllByIdNeq +select * +from user +where id != ?; + +-- example25: find all by id in, or, not in +-- fn: FindAllByIdInOrNotIn +select * +from user +where id in (?) + or id not in (?); + +-- example26: complex query +-- fn: ComplexQuery +select * +from user +where id > ? + and id < ? + and id != ? + and id in (?) + and id not in (?) + and id between ? and ? + and id not between ? and ? + and id >= ? + and id <= ? + and id != ? + and name like ? + and name not like ? + and name in (?) + and name not in (?) + and name between ? + and ? and name not between ? and ? + and name >= ? + and name <= ? + and name != ?; diff --git a/example/sql/update/example.sql b/example/sql/update/example.sql new file mode 100644 index 0000000..3d56072 --- /dev/null +++ b/example/sql/update/example.sql @@ -0,0 +1,41 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: update by primary key +-- fn: Update +update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; + +-- example2: update by unique key +-- fn: UpdateByName +update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; + +-- example3: update part columns by primary key +-- fn: UpdatePart +update user set name = ?, nickname = ? where id = ?; + +-- example4: update part columns by unique key +-- fn: UpdatePartByName +update user set name = ?, nickname = ? where name = ?; + +-- example5: update name limit ? +-- fn: UpdateNameLimit +update user set name = ? where id > ? limit ?; + +-- example6: update name limit ? order by id desc +-- fn: UpdateNameLimitOrder +update user set name = ? where id > ? order by id desc limit ?; + diff --git a/internal/gen/sql/scanner.tpl b/internal/gen/sql/scanner.tpl new file mode 100644 index 0000000..1331d5b --- /dev/null +++ b/internal/gen/sql/scanner.tpl @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows []*sql.Row, v interface{}) error +} \ No newline at end of file diff --git a/internal/gen/sql/sql.go b/internal/gen/sql/sql.go index b6416ae..99177ed 100644 --- a/internal/gen/sql/sql.go +++ b/internal/gen/sql/sql.go @@ -1,7 +1,72 @@ package sql -import "github.com/anqiansong/sqlgen/internal/spec" +import ( + _ "embed" + "fmt" + "path/filepath" + "strings" + "text/template" + + "github.com/iancoleman/strcase" + + "github.com/anqiansong/sqlgen/internal/spec" + "github.com/anqiansong/sqlgen/internal/templatex" +) + +//go:embed sql_gen.tpl +var sqlGenTpl string + +//go:embed sql_custom.tpl +var sqlCustomTpl string + +//go:embed scanner.tpl +var sqlScannerTpl string func Run(list []spec.Context, output string) error { - panic("not implemented") + for _, ctx := range list { + var genFilename = filepath.Join(output, fmt.Sprintf("%s_model.gen.go", ctx.Table.Name)) + var customFilename = filepath.Join(output, fmt.Sprintf("%s_model.go", ctx.Table.Name)) + var scannerFilename = filepath.Join(output, "scanner.go") + gen := templatex.New() + var columns, parameter, args []string + for _, c := range ctx.Table.Columns { + if c.AutoIncrement { + continue + } + columns = append(columns, fmt.Sprintf("`%s`", c.Name)) + parameter = append(parameter, "?") + args = append(args, strcase.ToCamel(c.Name)) + } + gen.AppendFuncMap(template.FuncMap{ + "IsPrimary": func(name string) bool { + return ctx.Table.IsPrimary(name) + }, + "InsertSQL": func() string { + return fmt.Sprintf(`"INSERT INTO %s (%s) VALUES (%s)"`, fmt.Sprintf("`%s`", ctx.Table.Name), strings.Join(columns, ", "), strings.Join(parameter, ", ")) + }, + "InsertSQLArgs": func(pkg string) string { + return pkg + "." + strings.Join(args, fmt.Sprintf(", %s.", pkg)) + }, + "HavingSprintf": func(format string) string { + format = strings.ReplaceAll(format, "?", "%v") + return format + }, + }) + scanner := templatex.New() + scanner.MustParse(sqlScannerTpl) + scanner.MustExecute(ctx) + scanner.MustSave(scannerFilename, true) + + gen.MustParse(sqlGenTpl) + gen.MustExecute(ctx) + gen.MustSaveAs(genFilename, true) + + custom := templatex.New() + custom.MustParse(sqlCustomTpl) + custom.MustExecute(ctx) + custom.MustSave(customFilename, true) + + } + + return nil } diff --git a/internal/gen/sql/sql_custom.tpl b/internal/gen/sql/sql_custom.tpl new file mode 100644 index 0000000..f110e53 --- /dev/null +++ b/internal/gen/sql/sql_custom.tpl @@ -0,0 +1,9 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *{{UpperCamel $.Table.Name}}Model)Customize(ctx context.Context, args...any) { + +} + diff --git a/internal/gen/sql/sql_gen.tpl b/internal/gen/sql/sql_gen.tpl new file mode 100644 index 0000000..2619329 --- /dev/null +++ b/internal/gen/sql/sql_gen.tpl @@ -0,0 +1,66 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" + + + "github.com/shopspring/decimal" +) + +// {{UpperCamel $.Table.Name}}Model represents a {{$.Table.Name}} model. +type {{UpperCamel $.Table.Name}}Model struct { + scanner Scanner + db sql.Conn +} + +// {{UpperCamel $.Table.Name}} represents a {{$.Table.Name}} struct data. +type {{UpperCamel $.Table.Name}} struct { {{range $.Table.Columns}} +{{UpperCamel .Name}} {{.GoType}}{{end}} +} + +{{range $stmt := .SelectStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Having.IsValid}}{{$stmt.Having.ParameterStructure "Having"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}}{{$stmt.ReceiverStructure}} +{{end}} + +{{range $stmt := .UpdateStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}} +{{end}} + +{{range $stmt := .DeleteStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}} +{{end}} + +func (m *{{UpperCamel $.Table.Name}}Model) SetScanner(scanner Scanner) { + m.scanner = scanner +} + +// Insert creates {{$.Table.Name}} data. +func (m *{{UpperCamel $.Table.Name}}Model) Insert(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) error { + if len(data)==0{ + return fmt.Errorf("data is empty") + } + + stmt,err := m.db.PrepareContext(ctx, {{InsertSQL}}) + if err != nil{ + return err + } + defer stmt.Close() + + for _,v := range data{ + _,err = stmt.ExecContext(ctx, {{InsertSQLArgs "v"}}) + if err != nil{ + return err + } + } + + return nil +} \ No newline at end of file diff --git a/internal/spec/table.go b/internal/spec/table.go index ec7daa8..33f0cee 100644 --- a/internal/spec/table.go +++ b/internal/spec/table.go @@ -115,6 +115,9 @@ func (t *Table) PrimaryColumnList() Columns { // PrimaryColumn returns the primary column. func (t *Table) PrimaryColumn() Column { list := t.PrimaryColumnList() + if len(list) == 0 { + return Column{} + } return list[0] } From 8eefe3bccc389f08a5eca86b107525bc2ffc2a81 Mon Sep 17 00:00:00 2001 From: anqiansong Date: Tue, 9 Aug 2022 22:49:00 +0800 Subject: [PATCH 2/5] Update example --- cmd/cmd.go | 10 +- example/gorm/read/example.sql | 5 +- example/gorm/read/user_model.gen.go | 2 +- example/sql/create/example.sql | 16 --- example/sql/create/scanner.go | 8 -- example/sql/create/user_model.gen.go | 55 -------- example/sql/create/user_model.go | 8 -- example/sql/delete/example.sql | 28 ---- example/sql/read/example.sql | 200 --------------------------- example/sql/update/example.sql | 41 ------ example/xorm/read/example.sql | 5 +- example/xorm/read/user_model.gen.go | 2 +- internal/gen/flags/flags.go | 5 +- internal/gen/gorm/gorm_gen.tpl | 2 +- internal/gen/sql/scanner.tpl | 8 -- internal/gen/sql/sql.go | 72 ---------- internal/gen/sql/sql_custom.tpl | 9 -- internal/gen/sql/sql_gen.tpl | 66 --------- internal/gen/xorm/xorm_gen.tpl | 2 +- internal/parser/select.go | 2 +- 20 files changed, 11 insertions(+), 535 deletions(-) delete mode 100644 example/sql/create/example.sql delete mode 100644 example/sql/create/scanner.go delete mode 100644 example/sql/create/user_model.gen.go delete mode 100644 example/sql/create/user_model.go delete mode 100644 example/sql/delete/example.sql delete mode 100644 example/sql/read/example.sql delete mode 100644 example/sql/update/example.sql delete mode 100644 internal/gen/sql/scanner.tpl delete mode 100644 internal/gen/sql/sql.go delete mode 100644 internal/gen/sql/sql_custom.tpl delete mode 100644 internal/gen/sql/sql_gen.tpl diff --git a/cmd/cmd.go b/cmd/cmd.go index f921f7f..b767598 100644 --- a/cmd/cmd.go +++ b/cmd/cmd.go @@ -14,14 +14,6 @@ var rootCmd = &cobra.Command{ Use: "sqlgen", Short: "A cli for mysql generator", } -var sqlCmd = &cobra.Command{ - Use: "sql", - Short: "Generate SQL model", - Run: func(cmd *cobra.Command, args []string) { - arg.Mode = flags.SQL - flags.Run(arg) - }, -} var gormCmd = &cobra.Command{ Use: "gorm", @@ -68,7 +60,7 @@ func init() { persistentFlags.StringVarP(&arg.Output, "output", "o", ".", "The output directory") // sub commands init - rootCmd.AddCommand(sqlCmd) + rootCmd.AddCommand(bunCmd) rootCmd.AddCommand(gormCmd) rootCmd.AddCommand(xormCmd) rootCmd.AddCommand(sqlxCmd) diff --git a/example/gorm/read/example.sql b/example/gorm/read/example.sql index c190d3f..ffa80f4 100644 --- a/example/gorm/read/example.sql +++ b/example/gorm/read/example.sql @@ -104,15 +104,14 @@ group by name having typeCount > ? order by id desc limit 10; --- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, offset 10 +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, 10 -- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? -order by id desc limit 10 -offset 10; +order by id desc limit 10, 10; -- example16: find one by name like -- fn: FindOneByNameLike diff --git a/example/gorm/read/user_model.gen.go b/example/gorm/read/user_model.gen.go index fc21196..6f02d44 100644 --- a/example/gorm/read/user_model.gen.go +++ b/example/gorm/read/user_model.gen.go @@ -433,7 +433,7 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 is generated from sql: -// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10 offset 10; +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10, 10; func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10(ctx context.Context, where ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter, having ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter, limit ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter) ([]*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result, error) { var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result var db = m.db.WithContext(ctx) diff --git a/example/sql/create/example.sql b/example/sql/create/example.sql deleted file mode 100644 index e492c51..0000000 --- a/example/sql/create/example.sql +++ /dev/null @@ -1,16 +0,0 @@ --- 用户表 -- -CREATE TABLE `user` -( - `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, - `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', - `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', - `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', - `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', - `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', - `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', - `create_time` timestamp NULL, - `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - UNIQUE KEY `name_index` (`name`), - UNIQUE KEY `type_index` (`type`), - UNIQUE KEY `mobile_index` (`mobile`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; \ No newline at end of file diff --git a/example/sql/create/scanner.go b/example/sql/create/scanner.go deleted file mode 100644 index fd4a6a2..0000000 --- a/example/sql/create/scanner.go +++ /dev/null @@ -1,8 +0,0 @@ -package model - -import "database/sql" - -type Scanner interface { - ScanRow(row *sql.Row, v interface{}) error - ScanRows(rows []*sql.Row, v interface{}) error -} diff --git a/example/sql/create/user_model.gen.go b/example/sql/create/user_model.gen.go deleted file mode 100644 index 2f9de4f..0000000 --- a/example/sql/create/user_model.gen.go +++ /dev/null @@ -1,55 +0,0 @@ -// Code generated by sqlgen. DO NOT EDIT! - -package model - -import ( - "context" - "database/sql" - "fmt" - "time" -) - -// UserModel represents a user model. -type UserModel struct { - scanner Scanner - db sql.Conn -} - -// User represents a user struct data. -type User struct { - Id uint64 - Name string - Password string - Mobile string - Gender string - Nickname string - Type int8 - CreateTime time.Time - UpdateTime time.Time -} - -func (m *UserModel) SetScanner(scanner Scanner) { - m.scanner = scanner -} - -// Insert creates user data. -func (m *UserModel) Insert(ctx context.Context, data ...*User) error { - if len(data) == 0 { - return fmt.Errorf("data is empty") - } - - stmt, err := m.db.PrepareContext(ctx, "INSERT INTO `user` (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") - if err != nil { - return err - } - defer stmt.Close() - - for _, v := range data { - _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) - if err != nil { - return err - } - } - - return nil -} diff --git a/example/sql/create/user_model.go b/example/sql/create/user_model.go deleted file mode 100644 index 8528c27..0000000 --- a/example/sql/create/user_model.go +++ /dev/null @@ -1,8 +0,0 @@ -package model - -import "context" - -// TODO(sqlgen): Add your own customize code here. -func (m *UserModel) Customize(ctx context.Context, args ...any) { - -} diff --git a/example/sql/delete/example.sql b/example/sql/delete/example.sql deleted file mode 100644 index e63b1c6..0000000 --- a/example/sql/delete/example.sql +++ /dev/null @@ -1,28 +0,0 @@ --- 用户表 -- -CREATE TABLE `user` -( - `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, - `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', - `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', - `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', - `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', - `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', - `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', - `create_time` timestamp NULL, - `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - UNIQUE KEY `name_index` (`name`), - UNIQUE KEY `type_index` (`type`), - UNIQUE KEY `mobile_index` (`mobile`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; - --- example1: delete by primary key --- fn: Delete -delete from user where id = ?; - --- example2: delete by unique key --- fn: DeleteByName -delete from user where name = ?; - --- example3: delete by unique keys --- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file diff --git a/example/sql/read/example.sql b/example/sql/read/example.sql deleted file mode 100644 index c190d3f..0000000 --- a/example/sql/read/example.sql +++ /dev/null @@ -1,200 +0,0 @@ --- 用户表 -- -CREATE TABLE `user` -( - `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, - `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', - `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', - `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', - `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', - `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', - `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', - `create_time` timestamp NULL, - `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - UNIQUE KEY `name_index` (`name`), - UNIQUE KEY `type_index` (`type`), - UNIQUE KEY `mobile_index` (`mobile`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; - --- example1: find one by primary key --- if you want to find one result, you have to explicitly declare limit 1 statement. --- fn: FindOne -select * -from user -where id = ? limit 1; - --- example2: find one by unique key --- fn: FindByName -select * -from user -where name = ? limit 1; - --- example3: find part of fields by primary key --- fn: FindOnePart -select id, name, nickname -from user -where id = ? limit 1; - --- example4: find part of fields by unique key --- fn: FindByNamePart -select id, name, nickname -from user -where name = ? limit 1; - --- example5: find all --- fn: FindAll -select * -from user; - --- example6: find all count, if call function, you must use AS keyword to alias result. --- fn: FindAllCount -select count(*) AS count -from user; - --- example7: find all part of fields --- fn: FindAllPart -select id, name, nickname -from user; - --- example8: find all part of fields count, if call function, you must use AS keyword to alias result. --- fn: FindAllPartCount -select count(id) AS count -from user; - --- example9: find one by name and password --- fn: FindOneByNameAndPassword -select * -from user -where name = ? - and password = ? limit 1; - --- example10: list user by primary key, group by name --- fn: ListUserByNameAsc -select * -from user -where id > ? -group by name; - --- example11: list user by primary key, group by name asc, having count(type) > ? --- having clause must be a alias, do not use function expression, for example: --- select * from user where id > ? group by name asc having count(type) > ?; in this --- statement, count(type) is a function expression, it will not work, you can use --- select *,count(type) AS typeCount from user where id > ? group by name asc having typeCount > ?; instead. --- fn: ListUserByNameAscHavingCountTypeGt -select *, count(type) AS typeCount -from user -where id > ? -group by name -having typeCount > ?; - --- example13: list user by primary key, group by name desc, having count(type) > ?, order by id desc --- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDesc -select *, count(type) AS typeCount -from user -where id > ? -group by name -having typeCount > ? -order by id desc; - --- example14: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10 --- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 -select *, count(type) AS typeCount -from user -where id > ? -group by name -having typeCount > ? -order by id desc limit 10; - --- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, offset 10 --- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 -select *, count(type) AS typeCount -from user -where id > ? -group by name -having typeCount > ? -order by id desc limit 10 -offset 10; - --- example16: find one by name like --- fn: FindOneByNameLike -select * -from user -where name like ? limit 1; - --- example17: find all by name not like --- fn: FindAllByNameNotLike -select * -from user -where name not like ?; - --- example18: find all by id in --- fn: FindAllByIdIn -select * -from user -where id in (?); - --- example19: find all by id not in --- fn: FindAllByIdNotIn -select * -from user -where id not in (?); - --- example20: find all by id between --- fn: FindAllByIdBetween -select * -from user -where id between ? and ?; - --- example21: find all by id not between --- fn: FindAllByIdNotBetween -select * -from user -where id not between ? and ?; - --- example22: find all by id greater than or equal to --- fn: FindAllByIdGte -select * -from user -where id >= ?; - --- example23: find all by id less than or equal to --- fn: FindAllByIdLte -select * -from user -where id <= ?; - --- example24: find all by id not equal to --- fn: FindAllByIdNeq -select * -from user -where id != ?; - --- example25: find all by id in, or, not in --- fn: FindAllByIdInOrNotIn -select * -from user -where id in (?) - or id not in (?); - --- example26: complex query --- fn: ComplexQuery -select * -from user -where id > ? - and id < ? - and id != ? - and id in (?) - and id not in (?) - and id between ? and ? - and id not between ? and ? - and id >= ? - and id <= ? - and id != ? - and name like ? - and name not like ? - and name in (?) - and name not in (?) - and name between ? - and ? and name not between ? and ? - and name >= ? - and name <= ? - and name != ?; diff --git a/example/sql/update/example.sql b/example/sql/update/example.sql deleted file mode 100644 index 3d56072..0000000 --- a/example/sql/update/example.sql +++ /dev/null @@ -1,41 +0,0 @@ --- 用户表 -- -CREATE TABLE `user` -( - `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, - `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', - `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', - `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', - `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', - `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', - `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', - `create_time` timestamp NULL, - `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, - UNIQUE KEY `name_index` (`name`), - UNIQUE KEY `type_index` (`type`), - UNIQUE KEY `mobile_index` (`mobile`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; - --- example1: update by primary key --- fn: Update -update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; - --- example2: update by unique key --- fn: UpdateByName -update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; - --- example3: update part columns by primary key --- fn: UpdatePart -update user set name = ?, nickname = ? where id = ?; - --- example4: update part columns by unique key --- fn: UpdatePartByName -update user set name = ?, nickname = ? where name = ?; - --- example5: update name limit ? --- fn: UpdateNameLimit -update user set name = ? where id > ? limit ?; - --- example6: update name limit ? order by id desc --- fn: UpdateNameLimitOrder -update user set name = ? where id > ? order by id desc limit ?; - diff --git a/example/xorm/read/example.sql b/example/xorm/read/example.sql index c190d3f..ffa80f4 100644 --- a/example/xorm/read/example.sql +++ b/example/xorm/read/example.sql @@ -104,15 +104,14 @@ group by name having typeCount > ? order by id desc limit 10; --- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, offset 10 +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, 10 -- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? -order by id desc limit 10 -offset 10; +order by id desc limit 10, 10; -- example16: find one by name like -- fn: FindOneByNameLike diff --git a/example/xorm/read/user_model.gen.go b/example/xorm/read/user_model.gen.go index 4b537fd..0269fd9 100644 --- a/example/xorm/read/user_model.gen.go +++ b/example/xorm/read/user_model.gen.go @@ -433,7 +433,7 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 is generated from sql: -// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10 offset 10; +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10, 10; func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10(ctx context.Context, where ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter, having ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter, limit ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter) ([]*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result, error) { var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result var session = m.engine.Context(ctx) diff --git a/internal/gen/flags/flags.go b/internal/gen/flags/flags.go index f12161f..c8a3f8b 100644 --- a/internal/gen/flags/flags.go +++ b/internal/gen/flags/flags.go @@ -7,7 +7,6 @@ import ( "github.com/anqiansong/sqlgen/internal/gen/bun" "github.com/anqiansong/sqlgen/internal/gen/gorm" - "github.com/anqiansong/sqlgen/internal/gen/sql" "github.com/anqiansong/sqlgen/internal/gen/sqlx" "github.com/anqiansong/sqlgen/internal/gen/xorm" "github.com/anqiansong/sqlgen/internal/log" @@ -21,8 +20,7 @@ const sqlExt = ".sql" type Mode int const ( - SQL Mode = iota - GORM + GORM Mode = iota XORM SQLX BUN @@ -108,7 +106,6 @@ func runFromDSN(arg RunArg) error { } var funcMap = map[Mode]func(context []spec.Context, output string) error{ - SQL: sql.Run, GORM: gorm.Run, XORM: xorm.Run, SQLX: sqlx.Run, diff --git a/internal/gen/gorm/gorm_gen.tpl b/internal/gen/gorm/gorm_gen.tpl index 4a9a3c7..da132b5 100644 --- a/internal/gen/gorm/gorm_gen.tpl +++ b/internal/gen/gorm/gorm_gen.tpl @@ -62,7 +62,7 @@ func (m *{{UpperCamel $.Table.Name}}Model) Create(ctx context.Context, data ...* func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Having.IsValid}}, having {{$stmt.Having.ParameterStructureName "Having"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}})({{if $stmt.Limit.One}}*{{$stmt.ReceiverName}}, {{else}}[]*{{$stmt.ReceiverName}}, {{end}} error){ var result {{if $stmt.Limit.One}} = new({{$stmt.ReceiverName}}){{else}}[]*{{$stmt.ReceiverName}}{{end}} var db = m.db.WithContext(ctx) - db.Select({{$stmt.SelectSQL}}) + db.Select(`{{$stmt.SelectSQL}}`) {{if $stmt.Where.IsValid}}db.Where({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}}) {{end }}{{if $stmt.GroupBy.IsValid}}db.Group({{$stmt.GroupBy.SQL}}) {{end}}{{if $stmt.Having.IsValid}}db.Having({{$stmt.Having.SQL}}, {{$stmt.Having.Parameters "having"}}) diff --git a/internal/gen/sql/scanner.tpl b/internal/gen/sql/scanner.tpl deleted file mode 100644 index 1331d5b..0000000 --- a/internal/gen/sql/scanner.tpl +++ /dev/null @@ -1,8 +0,0 @@ -package model - -import "database/sql" - -type Scanner interface { - ScanRow(row *sql.Row, v interface{}) error - ScanRows(rows []*sql.Row, v interface{}) error -} \ No newline at end of file diff --git a/internal/gen/sql/sql.go b/internal/gen/sql/sql.go deleted file mode 100644 index 99177ed..0000000 --- a/internal/gen/sql/sql.go +++ /dev/null @@ -1,72 +0,0 @@ -package sql - -import ( - _ "embed" - "fmt" - "path/filepath" - "strings" - "text/template" - - "github.com/iancoleman/strcase" - - "github.com/anqiansong/sqlgen/internal/spec" - "github.com/anqiansong/sqlgen/internal/templatex" -) - -//go:embed sql_gen.tpl -var sqlGenTpl string - -//go:embed sql_custom.tpl -var sqlCustomTpl string - -//go:embed scanner.tpl -var sqlScannerTpl string - -func Run(list []spec.Context, output string) error { - for _, ctx := range list { - var genFilename = filepath.Join(output, fmt.Sprintf("%s_model.gen.go", ctx.Table.Name)) - var customFilename = filepath.Join(output, fmt.Sprintf("%s_model.go", ctx.Table.Name)) - var scannerFilename = filepath.Join(output, "scanner.go") - gen := templatex.New() - var columns, parameter, args []string - for _, c := range ctx.Table.Columns { - if c.AutoIncrement { - continue - } - columns = append(columns, fmt.Sprintf("`%s`", c.Name)) - parameter = append(parameter, "?") - args = append(args, strcase.ToCamel(c.Name)) - } - gen.AppendFuncMap(template.FuncMap{ - "IsPrimary": func(name string) bool { - return ctx.Table.IsPrimary(name) - }, - "InsertSQL": func() string { - return fmt.Sprintf(`"INSERT INTO %s (%s) VALUES (%s)"`, fmt.Sprintf("`%s`", ctx.Table.Name), strings.Join(columns, ", "), strings.Join(parameter, ", ")) - }, - "InsertSQLArgs": func(pkg string) string { - return pkg + "." + strings.Join(args, fmt.Sprintf(", %s.", pkg)) - }, - "HavingSprintf": func(format string) string { - format = strings.ReplaceAll(format, "?", "%v") - return format - }, - }) - scanner := templatex.New() - scanner.MustParse(sqlScannerTpl) - scanner.MustExecute(ctx) - scanner.MustSave(scannerFilename, true) - - gen.MustParse(sqlGenTpl) - gen.MustExecute(ctx) - gen.MustSaveAs(genFilename, true) - - custom := templatex.New() - custom.MustParse(sqlCustomTpl) - custom.MustExecute(ctx) - custom.MustSave(customFilename, true) - - } - - return nil -} diff --git a/internal/gen/sql/sql_custom.tpl b/internal/gen/sql/sql_custom.tpl deleted file mode 100644 index f110e53..0000000 --- a/internal/gen/sql/sql_custom.tpl +++ /dev/null @@ -1,9 +0,0 @@ -package model - -import "context" - -// TODO(sqlgen): Add your own customize code here. -func (m *{{UpperCamel $.Table.Name}}Model)Customize(ctx context.Context, args...any) { - -} - diff --git a/internal/gen/sql/sql_gen.tpl b/internal/gen/sql/sql_gen.tpl deleted file mode 100644 index 2619329..0000000 --- a/internal/gen/sql/sql_gen.tpl +++ /dev/null @@ -1,66 +0,0 @@ -// Code generated by sqlgen. DO NOT EDIT! - -package model - -import ( - "context" - "database/sql" - "fmt" - "time" - - - "github.com/shopspring/decimal" -) - -// {{UpperCamel $.Table.Name}}Model represents a {{$.Table.Name}} model. -type {{UpperCamel $.Table.Name}}Model struct { - scanner Scanner - db sql.Conn -} - -// {{UpperCamel $.Table.Name}} represents a {{$.Table.Name}} struct data. -type {{UpperCamel $.Table.Name}} struct { {{range $.Table.Columns}} -{{UpperCamel .Name}} {{.GoType}}{{end}} -} - -{{range $stmt := .SelectStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} -{{end}}{{if $stmt.Having.IsValid}}{{$stmt.Having.ParameterStructure "Having"}} -{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} -{{end}}{{$stmt.ReceiverStructure}} -{{end}} - -{{range $stmt := .UpdateStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} -{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} -{{end}} -{{end}} - -{{range $stmt := .DeleteStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} -{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} -{{end}} -{{end}} - -func (m *{{UpperCamel $.Table.Name}}Model) SetScanner(scanner Scanner) { - m.scanner = scanner -} - -// Insert creates {{$.Table.Name}} data. -func (m *{{UpperCamel $.Table.Name}}Model) Insert(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) error { - if len(data)==0{ - return fmt.Errorf("data is empty") - } - - stmt,err := m.db.PrepareContext(ctx, {{InsertSQL}}) - if err != nil{ - return err - } - defer stmt.Close() - - for _,v := range data{ - _,err = stmt.ExecContext(ctx, {{InsertSQLArgs "v"}}) - if err != nil{ - return err - } - } - - return nil -} \ No newline at end of file diff --git a/internal/gen/xorm/xorm_gen.tpl b/internal/gen/xorm/xorm_gen.tpl index 29ceeec..778934a 100644 --- a/internal/gen/xorm/xorm_gen.tpl +++ b/internal/gen/xorm/xorm_gen.tpl @@ -63,7 +63,7 @@ func (m *{{UpperCamel $.Table.Name}}Model) Insert(ctx context.Context, data ...* func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Having.IsValid}}, having {{$stmt.Having.ParameterStructureName "Having"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}})({{if $stmt.Limit.One}}*{{$stmt.ReceiverName}}, {{else}}[]*{{$stmt.ReceiverName}}, {{end}} error){ var result {{if $stmt.Limit.One}} = new({{$stmt.ReceiverName}}){{else}}[]*{{$stmt.ReceiverName}}{{end}} var session = m.engine.Context(ctx) - session.Select({{$stmt.SelectSQL}}) + session.Select(`{{$stmt.SelectSQL}}`) {{if $stmt.Where.IsValid}}session.Where({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}}) {{end }}{{if $stmt.GroupBy.IsValid}}session.GroupBy({{$stmt.GroupBy.SQL}}) {{end}}{{if $stmt.Having.IsValid}}session.Having(fmt.Sprintf({{HavingSprintf $stmt.Having.SQL}}, {{$stmt.Having.Parameters "having"}})) diff --git a/internal/parser/select.go b/internal/parser/select.go index 582de62..56a8d92 100644 --- a/internal/parser/select.go +++ b/internal/parser/select.go @@ -87,7 +87,7 @@ func parseSelect(stmt *ast.SelectStmt) (*spec.SelectStmt, error) { } } ret.From = tableName - ret.SelectSQL = fmt.Sprintf("`%s`", selectFieldSQL) + ret.SelectSQL = fmt.Sprintf("%s", selectFieldSQL) ret.Distinct = stmt.Distinct ret.Action = spec.ActionRead ret.SQL = sql From 91c8e120bccb1d88ca11fa99072ec65dd082c806 Mon Sep 17 00:00:00 2001 From: anqiansong Date: Wed, 10 Aug 2022 21:15:18 +0800 Subject: [PATCH 3/5] Add sql --- cmd/cmd.go | 12 +- example/bun/create/example.sql | 16 +++ example/bun/delete/example.sql | 28 ++++ example/bun/read/example.sql | 199 +++++++++++++++++++++++++++ example/bun/update/example.sql | 41 ++++++ example/sql/create/example.sql | 16 +++ example/sql/create/user_model.gen.go | 56 ++++++++ example/sql/create/user_model.go | 8 ++ example/sql/delete/example.sql | 28 ++++ example/sql/read/example.sql | 199 +++++++++++++++++++++++++++ example/sql/update/example.sql | 41 ++++++ example/sqlx/create/example.sql | 16 +++ example/sqlx/delete/example.sql | 28 ++++ example/sqlx/read/example.sql | 199 +++++++++++++++++++++++++++ example/sqlx/update/example.sql | 41 ++++++ internal/gen/flags/flags.go | 5 +- internal/gen/sql/sql.go | 66 +++++++++ internal/gen/sql/sql_custom.tpl | 8 ++ internal/gen/sql/sql_gen.tpl | 60 ++++++++ internal/spec/clause.go | 10 +- 20 files changed, 1074 insertions(+), 3 deletions(-) create mode 100644 example/bun/create/example.sql create mode 100644 example/bun/delete/example.sql create mode 100644 example/bun/read/example.sql create mode 100644 example/bun/update/example.sql create mode 100644 example/sql/create/example.sql create mode 100644 example/sql/create/user_model.gen.go create mode 100644 example/sql/create/user_model.go create mode 100644 example/sql/delete/example.sql create mode 100644 example/sql/read/example.sql create mode 100644 example/sql/update/example.sql create mode 100644 example/sqlx/create/example.sql create mode 100644 example/sqlx/delete/example.sql create mode 100644 example/sqlx/read/example.sql create mode 100644 example/sqlx/update/example.sql create mode 100644 internal/gen/sql/sql.go create mode 100644 internal/gen/sql/sql_custom.tpl create mode 100644 internal/gen/sql/sql_gen.tpl diff --git a/cmd/cmd.go b/cmd/cmd.go index b767598..034ed2b 100644 --- a/cmd/cmd.go +++ b/cmd/cmd.go @@ -15,6 +15,15 @@ var rootCmd = &cobra.Command{ Short: "A cli for mysql generator", } +var sqlCmd = &cobra.Command{ + Use: "sql", + Short: "Generate sql model", + Run: func(cmd *cobra.Command, args []string) { + arg.Mode = flags.SQL + flags.Run(arg) + }, +} + var gormCmd = &cobra.Command{ Use: "gorm", Short: "Generate gorm model", @@ -62,8 +71,9 @@ func init() { // sub commands init rootCmd.AddCommand(bunCmd) rootCmd.AddCommand(gormCmd) - rootCmd.AddCommand(xormCmd) + rootCmd.AddCommand(sqlCmd) rootCmd.AddCommand(sqlxCmd) + rootCmd.AddCommand(xormCmd) } func Execute() { diff --git a/example/bun/create/example.sql b/example/bun/create/example.sql new file mode 100644 index 0000000..e492c51 --- /dev/null +++ b/example/bun/create/example.sql @@ -0,0 +1,16 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; \ No newline at end of file diff --git a/example/bun/delete/example.sql b/example/bun/delete/example.sql new file mode 100644 index 0000000..e63b1c6 --- /dev/null +++ b/example/bun/delete/example.sql @@ -0,0 +1,28 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: delete by primary key +-- fn: Delete +delete from user where id = ?; + +-- example2: delete by unique key +-- fn: DeleteByName +delete from user where name = ?; + +-- example3: delete by unique keys +-- fn: DeleteByNameAndMobile +delete from user where name = ? and mobile = ?; \ No newline at end of file diff --git a/example/bun/read/example.sql b/example/bun/read/example.sql new file mode 100644 index 0000000..ffa80f4 --- /dev/null +++ b/example/bun/read/example.sql @@ -0,0 +1,199 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: find one by primary key +-- if you want to find one result, you have to explicitly declare limit 1 statement. +-- fn: FindOne +select * +from user +where id = ? limit 1; + +-- example2: find one by unique key +-- fn: FindByName +select * +from user +where name = ? limit 1; + +-- example3: find part of fields by primary key +-- fn: FindOnePart +select id, name, nickname +from user +where id = ? limit 1; + +-- example4: find part of fields by unique key +-- fn: FindByNamePart +select id, name, nickname +from user +where name = ? limit 1; + +-- example5: find all +-- fn: FindAll +select * +from user; + +-- example6: find all count, if call function, you must use AS keyword to alias result. +-- fn: FindAllCount +select count(*) AS count +from user; + +-- example7: find all part of fields +-- fn: FindAllPart +select id, name, nickname +from user; + +-- example8: find all part of fields count, if call function, you must use AS keyword to alias result. +-- fn: FindAllPartCount +select count(id) AS count +from user; + +-- example9: find one by name and password +-- fn: FindOneByNameAndPassword +select * +from user +where name = ? + and password = ? limit 1; + +-- example10: list user by primary key, group by name +-- fn: ListUserByNameAsc +select * +from user +where id > ? +group by name; + +-- example11: list user by primary key, group by name asc, having count(type) > ? +-- having clause must be a alias, do not use function expression, for example: +-- select * from user where id > ? group by name asc having count(type) > ?; in this +-- statement, count(type) is a function expression, it will not work, you can use +-- select *,count(type) AS typeCount from user where id > ? group by name asc having typeCount > ?; instead. +-- fn: ListUserByNameAscHavingCountTypeGt +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ?; + +-- example13: list user by primary key, group by name desc, having count(type) > ?, order by id desc +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDesc +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc; + +-- example14: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10; + +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10, 10; + +-- example16: find one by name like +-- fn: FindOneByNameLike +select * +from user +where name like ? limit 1; + +-- example17: find all by name not like +-- fn: FindAllByNameNotLike +select * +from user +where name not like ?; + +-- example18: find all by id in +-- fn: FindAllByIdIn +select * +from user +where id in (?); + +-- example19: find all by id not in +-- fn: FindAllByIdNotIn +select * +from user +where id not in (?); + +-- example20: find all by id between +-- fn: FindAllByIdBetween +select * +from user +where id between ? and ?; + +-- example21: find all by id not between +-- fn: FindAllByIdNotBetween +select * +from user +where id not between ? and ?; + +-- example22: find all by id greater than or equal to +-- fn: FindAllByIdGte +select * +from user +where id >= ?; + +-- example23: find all by id less than or equal to +-- fn: FindAllByIdLte +select * +from user +where id <= ?; + +-- example24: find all by id not equal to +-- fn: FindAllByIdNeq +select * +from user +where id != ?; + +-- example25: find all by id in, or, not in +-- fn: FindAllByIdInOrNotIn +select * +from user +where id in (?) + or id not in (?); + +-- example26: complex query +-- fn: ComplexQuery +select * +from user +where id > ? + and id < ? + and id != ? + and id in (?) + and id not in (?) + and id between ? and ? + and id not between ? and ? + and id >= ? + and id <= ? + and id != ? + and name like ? + and name not like ? + and name in (?) + and name not in (?) + and name between ? + and ? and name not between ? and ? + and name >= ? + and name <= ? + and name != ?; diff --git a/example/bun/update/example.sql b/example/bun/update/example.sql new file mode 100644 index 0000000..3d56072 --- /dev/null +++ b/example/bun/update/example.sql @@ -0,0 +1,41 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: update by primary key +-- fn: Update +update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; + +-- example2: update by unique key +-- fn: UpdateByName +update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; + +-- example3: update part columns by primary key +-- fn: UpdatePart +update user set name = ?, nickname = ? where id = ?; + +-- example4: update part columns by unique key +-- fn: UpdatePartByName +update user set name = ?, nickname = ? where name = ?; + +-- example5: update name limit ? +-- fn: UpdateNameLimit +update user set name = ? where id > ? limit ?; + +-- example6: update name limit ? order by id desc +-- fn: UpdateNameLimitOrder +update user set name = ? where id > ? order by id desc limit ?; + diff --git a/example/sql/create/example.sql b/example/sql/create/example.sql new file mode 100644 index 0000000..e492c51 --- /dev/null +++ b/example/sql/create/example.sql @@ -0,0 +1,16 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; \ No newline at end of file diff --git a/example/sql/create/user_model.gen.go b/example/sql/create/user_model.gen.go new file mode 100644 index 0000000..f023197 --- /dev/null +++ b/example/sql/create/user_model.gen.go @@ -0,0 +1,56 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" +) + +// UserModel represents a user model. +type UserModel struct { + db sql.Conn +} + +// User represents a user struct data. +type User struct { + Id uint64 `gorm:"primaryKey;autoIncrement;column:id" json:"id"` + Name string `gorm:"column:name" json:"name"` + Password string `gorm:"column:password" json:"password"` + Mobile string `gorm:"column:mobile" json:"mobile"` + Gender string `gorm:"column:gender" json:"gender"` + Nickname string `gorm:"column:nickname" json:"nickname"` + Type int8 `gorm:"column:type" json:"type"` + CreateTime time.Time `gorm:"column:create_time" json:"createTime"` + UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` +} + +// TableName returns the table name. it implemented by gorm.Tabler. +func (User) TableName() string { + return "user" +} + +// Create creates user data. +func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + var stmt *sql.Stmt + stmt, err = m.db.PrepareContext(ctx, "INSERT INTO user (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") + if err != nil { + return + } + defer func() { + err = stmt.Close() + }() + for _, v := range data { + _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) + if err != nil { + return + } + } + return +} diff --git a/example/sql/create/user_model.go b/example/sql/create/user_model.go new file mode 100644 index 0000000..8528c27 --- /dev/null +++ b/example/sql/create/user_model.go @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *UserModel) Customize(ctx context.Context, args ...any) { + +} diff --git a/example/sql/delete/example.sql b/example/sql/delete/example.sql new file mode 100644 index 0000000..e63b1c6 --- /dev/null +++ b/example/sql/delete/example.sql @@ -0,0 +1,28 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: delete by primary key +-- fn: Delete +delete from user where id = ?; + +-- example2: delete by unique key +-- fn: DeleteByName +delete from user where name = ?; + +-- example3: delete by unique keys +-- fn: DeleteByNameAndMobile +delete from user where name = ? and mobile = ?; \ No newline at end of file diff --git a/example/sql/read/example.sql b/example/sql/read/example.sql new file mode 100644 index 0000000..ffa80f4 --- /dev/null +++ b/example/sql/read/example.sql @@ -0,0 +1,199 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: find one by primary key +-- if you want to find one result, you have to explicitly declare limit 1 statement. +-- fn: FindOne +select * +from user +where id = ? limit 1; + +-- example2: find one by unique key +-- fn: FindByName +select * +from user +where name = ? limit 1; + +-- example3: find part of fields by primary key +-- fn: FindOnePart +select id, name, nickname +from user +where id = ? limit 1; + +-- example4: find part of fields by unique key +-- fn: FindByNamePart +select id, name, nickname +from user +where name = ? limit 1; + +-- example5: find all +-- fn: FindAll +select * +from user; + +-- example6: find all count, if call function, you must use AS keyword to alias result. +-- fn: FindAllCount +select count(*) AS count +from user; + +-- example7: find all part of fields +-- fn: FindAllPart +select id, name, nickname +from user; + +-- example8: find all part of fields count, if call function, you must use AS keyword to alias result. +-- fn: FindAllPartCount +select count(id) AS count +from user; + +-- example9: find one by name and password +-- fn: FindOneByNameAndPassword +select * +from user +where name = ? + and password = ? limit 1; + +-- example10: list user by primary key, group by name +-- fn: ListUserByNameAsc +select * +from user +where id > ? +group by name; + +-- example11: list user by primary key, group by name asc, having count(type) > ? +-- having clause must be a alias, do not use function expression, for example: +-- select * from user where id > ? group by name asc having count(type) > ?; in this +-- statement, count(type) is a function expression, it will not work, you can use +-- select *,count(type) AS typeCount from user where id > ? group by name asc having typeCount > ?; instead. +-- fn: ListUserByNameAscHavingCountTypeGt +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ?; + +-- example13: list user by primary key, group by name desc, having count(type) > ?, order by id desc +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDesc +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc; + +-- example14: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10; + +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10, 10; + +-- example16: find one by name like +-- fn: FindOneByNameLike +select * +from user +where name like ? limit 1; + +-- example17: find all by name not like +-- fn: FindAllByNameNotLike +select * +from user +where name not like ?; + +-- example18: find all by id in +-- fn: FindAllByIdIn +select * +from user +where id in (?); + +-- example19: find all by id not in +-- fn: FindAllByIdNotIn +select * +from user +where id not in (?); + +-- example20: find all by id between +-- fn: FindAllByIdBetween +select * +from user +where id between ? and ?; + +-- example21: find all by id not between +-- fn: FindAllByIdNotBetween +select * +from user +where id not between ? and ?; + +-- example22: find all by id greater than or equal to +-- fn: FindAllByIdGte +select * +from user +where id >= ?; + +-- example23: find all by id less than or equal to +-- fn: FindAllByIdLte +select * +from user +where id <= ?; + +-- example24: find all by id not equal to +-- fn: FindAllByIdNeq +select * +from user +where id != ?; + +-- example25: find all by id in, or, not in +-- fn: FindAllByIdInOrNotIn +select * +from user +where id in (?) + or id not in (?); + +-- example26: complex query +-- fn: ComplexQuery +select * +from user +where id > ? + and id < ? + and id != ? + and id in (?) + and id not in (?) + and id between ? and ? + and id not between ? and ? + and id >= ? + and id <= ? + and id != ? + and name like ? + and name not like ? + and name in (?) + and name not in (?) + and name between ? + and ? and name not between ? and ? + and name >= ? + and name <= ? + and name != ?; diff --git a/example/sql/update/example.sql b/example/sql/update/example.sql new file mode 100644 index 0000000..3d56072 --- /dev/null +++ b/example/sql/update/example.sql @@ -0,0 +1,41 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: update by primary key +-- fn: Update +update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; + +-- example2: update by unique key +-- fn: UpdateByName +update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; + +-- example3: update part columns by primary key +-- fn: UpdatePart +update user set name = ?, nickname = ? where id = ?; + +-- example4: update part columns by unique key +-- fn: UpdatePartByName +update user set name = ?, nickname = ? where name = ?; + +-- example5: update name limit ? +-- fn: UpdateNameLimit +update user set name = ? where id > ? limit ?; + +-- example6: update name limit ? order by id desc +-- fn: UpdateNameLimitOrder +update user set name = ? where id > ? order by id desc limit ?; + diff --git a/example/sqlx/create/example.sql b/example/sqlx/create/example.sql new file mode 100644 index 0000000..e492c51 --- /dev/null +++ b/example/sqlx/create/example.sql @@ -0,0 +1,16 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; \ No newline at end of file diff --git a/example/sqlx/delete/example.sql b/example/sqlx/delete/example.sql new file mode 100644 index 0000000..e63b1c6 --- /dev/null +++ b/example/sqlx/delete/example.sql @@ -0,0 +1,28 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: delete by primary key +-- fn: Delete +delete from user where id = ?; + +-- example2: delete by unique key +-- fn: DeleteByName +delete from user where name = ?; + +-- example3: delete by unique keys +-- fn: DeleteByNameAndMobile +delete from user where name = ? and mobile = ?; \ No newline at end of file diff --git a/example/sqlx/read/example.sql b/example/sqlx/read/example.sql new file mode 100644 index 0000000..ffa80f4 --- /dev/null +++ b/example/sqlx/read/example.sql @@ -0,0 +1,199 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: find one by primary key +-- if you want to find one result, you have to explicitly declare limit 1 statement. +-- fn: FindOne +select * +from user +where id = ? limit 1; + +-- example2: find one by unique key +-- fn: FindByName +select * +from user +where name = ? limit 1; + +-- example3: find part of fields by primary key +-- fn: FindOnePart +select id, name, nickname +from user +where id = ? limit 1; + +-- example4: find part of fields by unique key +-- fn: FindByNamePart +select id, name, nickname +from user +where name = ? limit 1; + +-- example5: find all +-- fn: FindAll +select * +from user; + +-- example6: find all count, if call function, you must use AS keyword to alias result. +-- fn: FindAllCount +select count(*) AS count +from user; + +-- example7: find all part of fields +-- fn: FindAllPart +select id, name, nickname +from user; + +-- example8: find all part of fields count, if call function, you must use AS keyword to alias result. +-- fn: FindAllPartCount +select count(id) AS count +from user; + +-- example9: find one by name and password +-- fn: FindOneByNameAndPassword +select * +from user +where name = ? + and password = ? limit 1; + +-- example10: list user by primary key, group by name +-- fn: ListUserByNameAsc +select * +from user +where id > ? +group by name; + +-- example11: list user by primary key, group by name asc, having count(type) > ? +-- having clause must be a alias, do not use function expression, for example: +-- select * from user where id > ? group by name asc having count(type) > ?; in this +-- statement, count(type) is a function expression, it will not work, you can use +-- select *,count(type) AS typeCount from user where id > ? group by name asc having typeCount > ?; instead. +-- fn: ListUserByNameAscHavingCountTypeGt +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ?; + +-- example13: list user by primary key, group by name desc, having count(type) > ?, order by id desc +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDesc +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc; + +-- example14: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10; + +-- example15: list user by primary key, group by name desc, having count(type) > ?, order by id desc, limit 10, 10 +-- fn: ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 +select *, count(type) AS typeCount +from user +where id > ? +group by name +having typeCount > ? +order by id desc limit 10, 10; + +-- example16: find one by name like +-- fn: FindOneByNameLike +select * +from user +where name like ? limit 1; + +-- example17: find all by name not like +-- fn: FindAllByNameNotLike +select * +from user +where name not like ?; + +-- example18: find all by id in +-- fn: FindAllByIdIn +select * +from user +where id in (?); + +-- example19: find all by id not in +-- fn: FindAllByIdNotIn +select * +from user +where id not in (?); + +-- example20: find all by id between +-- fn: FindAllByIdBetween +select * +from user +where id between ? and ?; + +-- example21: find all by id not between +-- fn: FindAllByIdNotBetween +select * +from user +where id not between ? and ?; + +-- example22: find all by id greater than or equal to +-- fn: FindAllByIdGte +select * +from user +where id >= ?; + +-- example23: find all by id less than or equal to +-- fn: FindAllByIdLte +select * +from user +where id <= ?; + +-- example24: find all by id not equal to +-- fn: FindAllByIdNeq +select * +from user +where id != ?; + +-- example25: find all by id in, or, not in +-- fn: FindAllByIdInOrNotIn +select * +from user +where id in (?) + or id not in (?); + +-- example26: complex query +-- fn: ComplexQuery +select * +from user +where id > ? + and id < ? + and id != ? + and id in (?) + and id not in (?) + and id between ? and ? + and id not between ? and ? + and id >= ? + and id <= ? + and id != ? + and name like ? + and name not like ? + and name in (?) + and name not in (?) + and name between ? + and ? and name not between ? and ? + and name >= ? + and name <= ? + and name != ?; diff --git a/example/sqlx/update/example.sql b/example/sqlx/update/example.sql new file mode 100644 index 0000000..3d56072 --- /dev/null +++ b/example/sqlx/update/example.sql @@ -0,0 +1,41 @@ +-- 用户表 -- +CREATE TABLE `user` +( + `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT primary key, + `name` varchar(255) COLLATE utf8mb4_general_ci NULL COMMENT '用户\t名称', + `password` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户\n密码', + `mobile` varchar(255) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '手机号', + `gender` char(5) COLLATE utf8mb4_general_ci NOT NULL COMMENT '男｜女｜未公\r开', + `nickname` varchar(255) COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '用户昵称', + `type` tinyint(1) COLLATE utf8mb4_general_ci DEFAULT 0 COMMENT '用户类型', + `create_time` timestamp NULL, + `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, + UNIQUE KEY `name_index` (`name`), + UNIQUE KEY `type_index` (`type`), + UNIQUE KEY `mobile_index` (`mobile`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; + +-- example1: update by primary key +-- fn: Update +update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; + +-- example2: update by unique key +-- fn: UpdateByName +update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; + +-- example3: update part columns by primary key +-- fn: UpdatePart +update user set name = ?, nickname = ? where id = ?; + +-- example4: update part columns by unique key +-- fn: UpdatePartByName +update user set name = ?, nickname = ? where name = ?; + +-- example5: update name limit ? +-- fn: UpdateNameLimit +update user set name = ? where id > ? limit ?; + +-- example6: update name limit ? order by id desc +-- fn: UpdateNameLimitOrder +update user set name = ? where id > ? order by id desc limit ?; + diff --git a/internal/gen/flags/flags.go b/internal/gen/flags/flags.go index c8a3f8b..f12161f 100644 --- a/internal/gen/flags/flags.go +++ b/internal/gen/flags/flags.go @@ -7,6 +7,7 @@ import ( "github.com/anqiansong/sqlgen/internal/gen/bun" "github.com/anqiansong/sqlgen/internal/gen/gorm" + "github.com/anqiansong/sqlgen/internal/gen/sql" "github.com/anqiansong/sqlgen/internal/gen/sqlx" "github.com/anqiansong/sqlgen/internal/gen/xorm" "github.com/anqiansong/sqlgen/internal/log" @@ -20,7 +21,8 @@ const sqlExt = ".sql" type Mode int const ( - GORM Mode = iota + SQL Mode = iota + GORM XORM SQLX BUN @@ -106,6 +108,7 @@ func runFromDSN(arg RunArg) error { } var funcMap = map[Mode]func(context []spec.Context, output string) error{ + SQL: sql.Run, GORM: gorm.Run, XORM: xorm.Run, SQLX: sqlx.Run, diff --git a/internal/gen/sql/sql.go b/internal/gen/sql/sql.go new file mode 100644 index 0000000..fccad82 --- /dev/null +++ b/internal/gen/sql/sql.go @@ -0,0 +1,66 @@ +package sql + +import ( + _ "embed" + "fmt" + "path/filepath" + "strings" + "text/template" + + "github.com/iancoleman/strcase" + + "github.com/anqiansong/sqlgen/internal/spec" + "github.com/anqiansong/sqlgen/internal/templatex" +) + +//go:embed sql_gen.tpl +var sqlGenTpl string + +//go:embed sql_custom.tpl +var sqlCustomTpl string + +func Run(list []spec.Context, output string) error { + for _, ctx := range list { + var genFilename = filepath.Join(output, fmt.Sprintf("%s_model.gen.go", ctx.Table.Name)) + var customFilename = filepath.Join(output, fmt.Sprintf("%s_model.go", ctx.Table.Name)) + gen := templatex.New() + var insertQuery, insertQuotes []string + for _, v := range ctx.Table.Columns { + if v.AutoIncrement { + continue + } + insertQuery = append(insertQuery, fmt.Sprintf("`%s`", v.Name)) + insertQuotes = append(insertQuotes, "?") + } + gen.AppendFuncMap(template.FuncMap{ + "IsPrimary": func(name string) bool { + return ctx.Table.IsPrimary(name) + }, + "InsertSQL": func() string { + return strings.Join(insertQuery, ", ") + }, + "InsertQuotes": func() string { + return strings.Join(insertQuotes, ", ") + }, + "InsertValues": func(pkg string) string { + var values []string + for _, v := range ctx.Table.Columns { + if v.AutoIncrement { + continue + } + values = append(values, fmt.Sprintf("%s.%s", pkg, strcase.ToCamel(v.Name))) + } + return strings.Join(values, ", ") + }, + }) + gen.MustParse(sqlGenTpl) + gen.MustExecute(ctx) + gen.MustSaveAs(genFilename, true) + + custom := templatex.New() + custom.MustParse(sqlCustomTpl) + custom.MustExecute(ctx) + custom.MustSave(customFilename, true) + } + return nil +} diff --git a/internal/gen/sql/sql_custom.tpl b/internal/gen/sql/sql_custom.tpl new file mode 100644 index 0000000..978b982 --- /dev/null +++ b/internal/gen/sql/sql_custom.tpl @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *{{UpperCamel $.Table.Name}}Model)Customize(ctx context.Context, args...any) { + +} \ No newline at end of file diff --git a/internal/gen/sql/sql_gen.tpl b/internal/gen/sql/sql_gen.tpl new file mode 100644 index 0000000..1fc30ec --- /dev/null +++ b/internal/gen/sql/sql_gen.tpl @@ -0,0 +1,60 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "fmt" + "time" + + "xorm.io/builder" + "github.com/shopspring/decimal" +) + +// {{UpperCamel $.Table.Name}}Model represents a {{$.Table.Name}} model. +type {{UpperCamel $.Table.Name}}Model struct { + db sql.Conn +} + +// {{UpperCamel $.Table.Name}} represents a {{$.Table.Name}} struct data. +type {{UpperCamel $.Table.Name}} struct { {{range $.Table.Columns}} +{{UpperCamel .Name}} {{.GoType}} `json:"{{LowerCamel .Name}}"`{{end}} +} +{{range $stmt := .SelectStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Having.IsValid}}{{$stmt.Having.ParameterStructure "Having"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}}{{$stmt.ReceiverStructure}} +{{end}} + +{{range $stmt := .UpdateStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}} +{{end}} + +{{range $stmt := .DeleteStmt}}{{if $stmt.Where.IsValid}}{{$stmt.Where.ParameterStructure "Where"}} +{{end}}{{if $stmt.Limit.Multiple}}{{$stmt.Limit.ParameterStructure}} +{{end}} +{{end}} + +// Create creates {{$.Table.Name}} data. +func (m *{{UpperCamel $.Table.Name}}Model) Create(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) (err error) { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + var stmt *sql.Stmt + stmt, err = m.db.PrepareContext(ctx, "INSERT INTO {{$.Table.Name}} ({{InsertSQL}}) VALUES ({{InsertQuotes}})") + if err != nil { + return + } + defer func() { + err = stmt.Close() + }() + for _, v := range data { + _, err = stmt.ExecContext(ctx, {{InsertValues "v"}}) + if err != nil { + return + } + } + return +} diff --git a/internal/spec/clause.go b/internal/spec/clause.go index a06d452..7b232cd 100644 --- a/internal/spec/clause.go +++ b/internal/spec/clause.go @@ -162,13 +162,21 @@ func (c *Clause) marshal() (sql string, parameters parameter.Parameters, err err } sql = strings.Join(sqlList, " "+Operator[c.OP]+" ") - case EQ, GE, GT, In, LE, LT, Like, NE, Not, NotIn, NotLike: + case EQ, GE, GT, LE, LT, Like, NE, Not, NotLike: sql = fmt.Sprintf("%s %s ?", c.Column, Operator[c.OP]) p, err := c.ColumnInfo.DataType() if err != nil { return "", nil, err } + ps.Add(p) + case In, NotIn: + sql = fmt.Sprintf("%s %s (?)", c.Column, Operator[c.OP]) + p, err := c.ColumnInfo.DataType() + if err != nil { + return "", nil, err + } + ps.Add(p) case Between, NotBetween: sql = fmt.Sprintf("%s %s ? AND ?", c.Column, Operator[c.OP]) From f148bfaeea923d959001de20827854321e5e0a6d Mon Sep 17 00:00:00 2001 From: anqiansong Date: Thu, 11 Aug 2022 17:44:47 +0800 Subject: [PATCH 4/5] Add sql --- example/go.mod | 2 +- example/gorm/create/user_model.gen.go | 5 + example/gorm/delete/user_model.gen.go | 5 + example/gorm/read/user_model.gen.go | 13 +- example/gorm/update/user_model.gen.go | 5 + example/sql/build.sh | 30 ++ example/sql/create/scanner.go | 8 + example/sql/create/user_model.gen.go | 30 +- example/sql/delete/scanner.go | 8 + example/sql/delete/user_model.gen.go | 76 +++ example/sql/delete/user_model.go | 8 + example/sql/read/example.sql | 2 +- example/sql/read/scanner.go | 8 + example/sql/read/user_model.gen.go | 717 ++++++++++++++++++++++++++ example/sql/read/user_model.go | 8 + example/sql/update/scanner.go | 8 + example/sql/update/user_model.gen.go | 216 ++++++++ example/sql/update/user_model.go | 8 + example/xorm/create/user_model.gen.go | 7 +- example/xorm/delete/user_model.gen.go | 7 +- example/xorm/read/user_model.gen.go | 15 +- example/xorm/update/user_model.gen.go | 13 +- internal/gen/gorm/gorm_gen.tpl | 5 + internal/gen/sql/scanner.tpl | 8 + internal/gen/sql/sql.go | 13 + internal/gen/sql/sql_gen.tpl | 61 ++- internal/gen/xorm/xorm_gen.tpl | 8 +- 27 files changed, 1258 insertions(+), 36 deletions(-) create mode 100644 example/sql/build.sh create mode 100644 example/sql/create/scanner.go create mode 100644 example/sql/delete/scanner.go create mode 100644 example/sql/delete/user_model.gen.go create mode 100644 example/sql/delete/user_model.go create mode 100644 example/sql/read/scanner.go create mode 100644 example/sql/read/user_model.gen.go create mode 100644 example/sql/read/user_model.go create mode 100644 example/sql/update/scanner.go create mode 100644 example/sql/update/user_model.gen.go create mode 100644 example/sql/update/user_model.go create mode 100644 internal/gen/sql/scanner.tpl diff --git a/example/go.mod b/example/go.mod index 30f900c..454974c 100644 --- a/example/go.mod +++ b/example/go.mod @@ -4,6 +4,7 @@ go 1.18 require ( gorm.io/gorm v1.23.8 + xorm.io/builder v0.3.11-0.20220531020008-1bd24a7dc978 xorm.io/xorm v1.3.1 ) @@ -17,5 +18,4 @@ require ( github.com/modern-go/concurrent v0.0.0-20180306012644-bacd9c7ef1dd // indirect github.com/modern-go/reflect2 v1.0.2 // indirect github.com/syndtr/goleveldb v1.0.0 // indirect - xorm.io/builder v0.3.11-0.20220531020008-1bd24a7dc978 // indirect ) diff --git a/example/gorm/create/user_model.gen.go b/example/gorm/create/user_model.gen.go index 2566934..e065aca 100644 --- a/example/gorm/create/user_model.gen.go +++ b/example/gorm/create/user_model.gen.go @@ -33,6 +33,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(db gorm.DB) *UserModel { + return &UserModel{db: db} +} + // Create creates user data. func (m *UserModel) Create(ctx context.Context, data ...*User) error { if len(data) == 0 { diff --git a/example/gorm/delete/user_model.gen.go b/example/gorm/delete/user_model.gen.go index fcab56b..5a547e3 100644 --- a/example/gorm/delete/user_model.gen.go +++ b/example/gorm/delete/user_model.gen.go @@ -49,6 +49,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(db gorm.DB) *UserModel { + return &UserModel{db: db} +} + // Create creates user data. func (m *UserModel) Create(ctx context.Context, data ...*User) error { if len(data) == 0 { diff --git a/example/gorm/read/user_model.gen.go b/example/gorm/read/user_model.gen.go index 6f02d44..1a58d3e 100644 --- a/example/gorm/read/user_model.gen.go +++ b/example/gorm/read/user_model.gen.go @@ -261,6 +261,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(db gorm.DB) *UserModel { + return &UserModel{db: db} +} + // Create creates user data. func (m *UserModel) Create(ctx context.Context, data ...*User) error { if len(data) == 0 { @@ -476,7 +481,7 @@ func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereP var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id IN ?`, where.Id) + db.Where(`id IN (?)`, where.Id) db.Find(&result) return result, db.Error } @@ -487,7 +492,7 @@ func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotIn var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id NOT IN ?`, where.Id) + db.Where(`id NOT IN (?)`, where.Id) db.Find(&result) return result, db.Error } @@ -553,7 +558,7 @@ func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdI var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id IN ? OR id NOT IN ?`, where.Id, where.Id1) + db.Where(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1) db.Find(&result) return result, db.Error } @@ -564,7 +569,7 @@ func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWherePar var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id > ? AND id < ? AND id != ? AND id IN ? AND id NOT IN ? AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN ? AND name NOT IN ? AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) + db.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) db.Find(&result) return result, db.Error } diff --git a/example/gorm/update/user_model.gen.go b/example/gorm/update/user_model.gen.go index 2764f2f..fa08b49 100644 --- a/example/gorm/update/user_model.gen.go +++ b/example/gorm/update/user_model.gen.go @@ -73,6 +73,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(db gorm.DB) *UserModel { + return &UserModel{db: db} +} + // Create creates user data. func (m *UserModel) Create(ctx context.Context, data ...*User) error { if len(data) == 0 { diff --git a/example/sql/build.sh b/example/sql/build.sh new file mode 100644 index 0000000..6f43e5e --- /dev/null +++ b/example/sql/build.sh @@ -0,0 +1,30 @@ +#!/bin/bash + +wd=$(pwd) + +# generate create code +cd "$wd/create" +rm -rf "user_*.go" +sqlgen sql -f "example.sql" -o . + +# generate delete code +cd "$wd/delete" +rm -rf "user_*.go" +sqlgen sql -f "example.sql" -o . + +# generate read code +cd "$wd/read" +rm -rf "user_*.go" +sqlgen sql -f "example.sql" -o . + +# generate update code +cd "$wd/update" +rm -rf "user_*.go" +sqlgen sql -f "example.sql" -o . + +cd "$wd" + +# go mod tidy +go mod tidy + +go test ./... \ No newline at end of file diff --git a/example/sql/create/scanner.go b/example/sql/create/scanner.go new file mode 100644 index 0000000..7306a65 --- /dev/null +++ b/example/sql/create/scanner.go @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows *sql.Rows, v interface{}) error +} diff --git a/example/sql/create/user_model.gen.go b/example/sql/create/user_model.gen.go index f023197..8f71376 100644 --- a/example/sql/create/user_model.gen.go +++ b/example/sql/create/user_model.gen.go @@ -11,25 +11,29 @@ import ( // UserModel represents a user model. type UserModel struct { - db sql.Conn + db *sql.Conn + scanner Scanner } // User represents a user struct data. type User struct { - Id uint64 `gorm:"primaryKey;autoIncrement;column:id" json:"id"` - Name string `gorm:"column:name" json:"name"` - Password string `gorm:"column:password" json:"password"` - Mobile string `gorm:"column:mobile" json:"mobile"` - Gender string `gorm:"column:gender" json:"gender"` - Nickname string `gorm:"column:nickname" json:"nickname"` - Type int8 `gorm:"column:type" json:"type"` - CreateTime time.Time `gorm:"column:create_time" json:"createTime"` - UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` + Id uint64 `json:"id"` + Name string `json:"name"` + Password string `json:"password"` + Mobile string `json:"mobile"` + Gender string `json:"gender"` + Nickname string `json:"nickname"` + Type int8 `json:"type"` + CreateTime time.Time `json:"createTime"` + UpdateTime time.Time `json:"updateTime"` } -// TableName returns the table name. it implemented by gorm.Tabler. -func (User) TableName() string { - return "user" +// NewUserModel creates a new user model. +func NewUserModel(db *sql.Conn, scanner Scanner) *UserModel { + return &UserModel{ + db: db, + scanner: scanner, + } } // Create creates user data. diff --git a/example/sql/delete/scanner.go b/example/sql/delete/scanner.go new file mode 100644 index 0000000..7306a65 --- /dev/null +++ b/example/sql/delete/scanner.go @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows *sql.Rows, v interface{}) error +} diff --git a/example/sql/delete/user_model.gen.go b/example/sql/delete/user_model.gen.go new file mode 100644 index 0000000..eeba285 --- /dev/null +++ b/example/sql/delete/user_model.gen.go @@ -0,0 +1,76 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" +) + +// UserModel represents a user model. +type UserModel struct { + db *sql.Conn + scanner Scanner +} + +// User represents a user struct data. +type User struct { + Id uint64 `json:"id"` + Name string `json:"name"` + Password string `json:"password"` + Mobile string `json:"mobile"` + Gender string `json:"gender"` + Nickname string `json:"nickname"` + Type int8 `json:"type"` + CreateTime time.Time `json:"createTime"` + UpdateTime time.Time `json:"updateTime"` +} + +// DeleteWhereParameter is a where parameter structure. +type DeleteWhereParameter struct { + Id uint64 +} + +// DeleteByNameWhereParameter is a where parameter structure. +type DeleteByNameWhereParameter struct { + Name string +} + +// DeleteByNameAndMobileWhereParameter is a where parameter structure. +type DeleteByNameAndMobileWhereParameter struct { + Name string + Mobile string +} + +// NewUserModel creates a new user model. +func NewUserModel(db *sql.Conn, scanner Scanner) *UserModel { + return &UserModel{ + db: db, + scanner: scanner, + } +} + +// Create creates user data. +func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + var stmt *sql.Stmt + stmt, err = m.db.PrepareContext(ctx, "INSERT INTO user (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") + if err != nil { + return + } + defer func() { + err = stmt.Close() + }() + for _, v := range data { + _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) + if err != nil { + return + } + } + return +} diff --git a/example/sql/delete/user_model.go b/example/sql/delete/user_model.go new file mode 100644 index 0000000..8528c27 --- /dev/null +++ b/example/sql/delete/user_model.go @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *UserModel) Customize(ctx context.Context, args ...any) { + +} diff --git a/example/sql/read/example.sql b/example/sql/read/example.sql index ffa80f4..20d1346 100644 --- a/example/sql/read/example.sql +++ b/example/sql/read/example.sql @@ -20,7 +20,7 @@ CREATE TABLE `user` -- fn: FindOne select * from user -where id = ? limit 1; +where id = ? and name in (?,?,?) limit 1; -- example2: find one by unique key -- fn: FindByName diff --git a/example/sql/read/scanner.go b/example/sql/read/scanner.go new file mode 100644 index 0000000..7306a65 --- /dev/null +++ b/example/sql/read/scanner.go @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows *sql.Rows, v interface{}) error +} diff --git a/example/sql/read/user_model.gen.go b/example/sql/read/user_model.gen.go new file mode 100644 index 0000000..610e6b7 --- /dev/null +++ b/example/sql/read/user_model.gen.go @@ -0,0 +1,717 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" + + "xorm.io/builder" +) + +// UserModel represents a user model. +type UserModel struct { + db *sql.Conn + scanner Scanner +} + +// User represents a user struct data. +type User struct { + Id uint64 `json:"id"` + Name string `json:"name"` + Password string `json:"password"` + Mobile string `json:"mobile"` + Gender string `json:"gender"` + Nickname string `json:"nickname"` + Type int8 `json:"type"` + CreateTime time.Time `json:"createTime"` + UpdateTime time.Time `json:"updateTime"` +} + +// FindOneWhereParameter is a where parameter structure. +type FindOneWhereParameter struct { + Id uint64 + Name string +} + +// FindByNameWhereParameter is a where parameter structure. +type FindByNameWhereParameter struct { + Name string +} + +// FindOnePartWhereParameter is a where parameter structure. +type FindOnePartWhereParameter struct { + Id uint64 +} + +// FindByNamePartWhereParameter is a where parameter structure. +type FindByNamePartWhereParameter struct { + Name string +} + +// FindAllCountResult is a find all count result. +type FindAllCountResult struct { + Count int64 `gorm:"column:count" json:"count"` +} + +// FindAllPartCountResult is a find all part count result. +type FindAllPartCountResult struct { + Count uint64 `gorm:"column:count" json:"count"` +} + +// FindOneByNameAndPasswordWhereParameter is a where parameter structure. +type FindOneByNameAndPasswordWhereParameter struct { + Name string + Password string +} + +// ListUserByNameAscWhereParameter is a where parameter structure. +type ListUserByNameAscWhereParameter struct { + Id uint64 +} + +// ListUserByNameAscHavingCountTypeGtWhereParameter is a where parameter structure. +type ListUserByNameAscHavingCountTypeGtWhereParameter struct { + Id uint64 +} + +// ListUserByNameAscHavingCountTypeGtHavingParameter is a having parameter structure. +type ListUserByNameAscHavingCountTypeGtHavingParameter struct { + TypeCount int8 +} + +// ListUserByNameAscHavingCountTypeGtResult is a list user by name asc having count type gt result. +type ListUserByNameAscHavingCountTypeGtResult struct { + Id uint64 `gorm:"column:id" json:"id"` + Name string `gorm:"column:name" json:"name"` + Password string `gorm:"column:password" json:"password"` + Mobile string `gorm:"column:mobile" json:"mobile"` + Gender string `gorm:"column:gender" json:"gender"` + Nickname string `gorm:"column:nickname" json:"nickname"` + Type int8 `gorm:"column:type" json:"type"` + CreateTime time.Time `gorm:"column:create_time" json:"createTime"` + UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` + TypeCount int8 `gorm:"column:typeCount" json:"typeCount"` +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter is a where parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter struct { + Id uint64 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter is a having parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter struct { + TypeCount int8 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescResult is a list user by name desc having count type gt order by id desc result. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescResult struct { + Id uint64 `gorm:"column:id" json:"id"` + Name string `gorm:"column:name" json:"name"` + Password string `gorm:"column:password" json:"password"` + Mobile string `gorm:"column:mobile" json:"mobile"` + Gender string `gorm:"column:gender" json:"gender"` + Nickname string `gorm:"column:nickname" json:"nickname"` + Type int8 `gorm:"column:type" json:"type"` + CreateTime time.Time `gorm:"column:create_time" json:"createTime"` + UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` + TypeCount int8 `gorm:"column:typeCount" json:"typeCount"` +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter is a where parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter struct { + Id uint64 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter is a having parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter struct { + TypeCount int8 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter is a limit parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter struct { + Count int +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result is a list user by name desc having count type gt order by id desc limit 10 result. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result struct { + Id uint64 `gorm:"column:id" json:"id"` + Name string `gorm:"column:name" json:"name"` + Password string `gorm:"column:password" json:"password"` + Mobile string `gorm:"column:mobile" json:"mobile"` + Gender string `gorm:"column:gender" json:"gender"` + Nickname string `gorm:"column:nickname" json:"nickname"` + Type int8 `gorm:"column:type" json:"type"` + CreateTime time.Time `gorm:"column:create_time" json:"createTime"` + UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` + TypeCount int8 `gorm:"column:typeCount" json:"typeCount"` +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter is a where parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter struct { + Id uint64 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter is a having parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter struct { + TypeCount int8 +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter is a limit parameter structure. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter struct { + Count int + Offset int +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result is a list user by name desc having count type gt order by id desc limit 10 offset 10 result. +type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result struct { + Id uint64 `gorm:"column:id" json:"id"` + Name string `gorm:"column:name" json:"name"` + Password string `gorm:"column:password" json:"password"` + Mobile string `gorm:"column:mobile" json:"mobile"` + Gender string `gorm:"column:gender" json:"gender"` + Nickname string `gorm:"column:nickname" json:"nickname"` + Type int8 `gorm:"column:type" json:"type"` + CreateTime time.Time `gorm:"column:create_time" json:"createTime"` + UpdateTime time.Time `gorm:"column:update_time" json:"updateTime"` + TypeCount int8 `gorm:"column:typeCount" json:"typeCount"` +} + +// FindOneByNameLikeWhereParameter is a where parameter structure. +type FindOneByNameLikeWhereParameter struct { + Name string +} + +// FindAllByNameNotLikeWhereParameter is a where parameter structure. +type FindAllByNameNotLikeWhereParameter struct { + Name string +} + +// FindAllByIdInWhereParameter is a where parameter structure. +type FindAllByIdInWhereParameter struct { + Id []uint64 +} + +// FindAllByIdNotInWhereParameter is a where parameter structure. +type FindAllByIdNotInWhereParameter struct { + Id []uint64 +} + +// FindAllByIdBetweenWhereParameter is a where parameter structure. +type FindAllByIdBetweenWhereParameter struct { + IdBetweenStart uint64 + IdBetweenEnd uint64 +} + +// FindAllByIdNotBetweenWhereParameter is a where parameter structure. +type FindAllByIdNotBetweenWhereParameter struct { + IdNotBetweenStart uint64 + IdNotBetweenEnd uint64 +} + +// FindAllByIdGteWhereParameter is a where parameter structure. +type FindAllByIdGteWhereParameter struct { + Id uint64 +} + +// FindAllByIdLteWhereParameter is a where parameter structure. +type FindAllByIdLteWhereParameter struct { + Id uint64 +} + +// FindAllByIdNeqWhereParameter is a where parameter structure. +type FindAllByIdNeqWhereParameter struct { + Id uint64 +} + +// FindAllByIdInOrNotInWhereParameter is a where parameter structure. +type FindAllByIdInOrNotInWhereParameter struct { + Id uint64 + Id1 uint64 +} + +// ComplexQueryWhereParameter is a where parameter structure. +type ComplexQueryWhereParameter struct { + Id uint64 + Id1 uint64 + Id2 uint64 + Id3 uint64 + Id4 uint64 + IdBetweenStart uint64 + IdBetweenEnd uint64 + IdNotBetweenStart uint64 + IdNotBetweenEnd uint64 + Id5 uint64 + Id6 uint64 + Id7 uint64 + Name string + Name1 string + Name2 string + Name3 string + NameBetweenStart string + NameBetweenEnd string + NameNotBetweenStart string + NameNotBetweenEnd string + Name4 string + Name5 string + Name6 string +} + +// NewUserModel creates a new user model. +func NewUserModel(db *sql.Conn, scanner Scanner) *UserModel { + return &UserModel{ + db: db, + scanner: scanner, + } +} + +// Create creates user data. +func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + var stmt *sql.Stmt + stmt, err = m.db.PrepareContext(ctx, "INSERT INTO user (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") + if err != nil { + return + } + defer func() { + err = stmt.Close() + }() + for _, v := range data { + _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) + if err != nil { + return + } + } + return +} + +// FindOne is generated from sql: +// select * from user where id = ? and name in (?) limit 1; +func (m *UserModel) FindOne(ctx context.Context, where FindOneWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id = ? AND name IN (?)`, where.Id, where.Name)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindByName is generated from sql: +// select * from user where name = ? limit 1; +func (m *UserModel) FindByName(ctx context.Context, where FindByNameWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`name = ?`, where.Name)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindOnePart is generated from sql: +// select id, name, nickname from user where id = ? limit 1; +func (m *UserModel) FindOnePart(ctx context.Context, where FindOnePartWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`id, name, nickname`) + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.Id)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindByNamePart is generated from sql: +// select id, name, nickname from user where name = ? limit 1; +func (m *UserModel) FindByNamePart(ctx context.Context, where FindByNamePartWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`id, name, nickname`) + b.From("`user`") + b.Where(builder.Expr(`name = ?`, where.Name)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindAll is generated from sql: +// select * from user; +func (m *UserModel) FindAll(ctx context.Context) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllCount is generated from sql: +// select count(*) AS count from user; +func (m *UserModel) FindAllCount(ctx context.Context) (*FindAllCountResult, error) { + var result = new(FindAllCountResult) + b := builder.Select(`count(1) AS count`) + b.From("`user`") + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindAllPart is generated from sql: +// select id, name, nickname from user; +func (m *UserModel) FindAllPart(ctx context.Context) ([]*User, error) { + var result []*User + b := builder.Select(`id, name, nickname`) + b.From("`user`") + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllPartCount is generated from sql: +// select count(id) AS count from user; +func (m *UserModel) FindAllPartCount(ctx context.Context) (*FindAllPartCountResult, error) { + var result = new(FindAllPartCountResult) + b := builder.Select(`count(id) AS count`) + b.From("`user`") + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindOneByNameAndPassword is generated from sql: +// select * from user where name = ? and password = ? limit 1; +func (m *UserModel) FindOneByNameAndPassword(ctx context.Context, where FindOneByNameAndPasswordWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`name = ? AND password = ?`, where.Name, where.Password)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// ListUserByNameAsc is generated from sql: +// select * from user where id > ? group by name; +func (m *UserModel) ListUserByNameAsc(ctx context.Context, where ListUserByNameAscWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.GroupBy(`name`) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// ListUserByNameAscHavingCountTypeGt is generated from sql: +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ?; +func (m *UserModel) ListUserByNameAscHavingCountTypeGt(ctx context.Context, where ListUserByNameAscHavingCountTypeGtWhereParameter, having ListUserByNameAscHavingCountTypeGtHavingParameter) ([]*ListUserByNameAscHavingCountTypeGtResult, error) { + var result []*ListUserByNameAscHavingCountTypeGtResult + b := builder.Select(`*, count(type) AS typeCount`) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.GroupBy(`name`) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDesc is generated from sql: +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc; +func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDesc(ctx context.Context, where ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter, having ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter) ([]*ListUserByNameDescHavingCountTypeGtOrderByIdDescResult, error) { + var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescResult + b := builder.Select(`*, count(type) AS typeCount`) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.GroupBy(`name`) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.OrderBy(`id desc`) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10 is generated from sql: +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10; +func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx context.Context, where ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter, having ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter, limit ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter) ([]*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result, error) { + var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result + b := builder.Select(`*, count(type) AS typeCount`) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.GroupBy(`name`) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.OrderBy(`id desc`) + b.Limit(limit.Count) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10 is generated from sql: +// select *, count(type) AS typeCount from user where id > ? group by name having typeCount > ? order by id desc limit 10, 10; +func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10(ctx context.Context, where ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter, having ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter, limit ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter) ([]*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result, error) { + var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result + b := builder.Select(`*, count(type) AS typeCount`) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.GroupBy(`name`) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.OrderBy(`id desc`) + b.Limit(limit.Count, limit.Offset) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindOneByNameLike is generated from sql: +// select * from user where name like ? limit 1; +func (m *UserModel) FindOneByNameLike(ctx context.Context, where FindOneByNameLikeWhereParameter) (*User, error) { + var result = new(User) + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`name LIKE ?`, where.Name)) + b.Limit(1) + query, args, err := b.ToSQL() + row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + +} + +// FindAllByNameNotLike is generated from sql: +// select * from user where name not like ?; +func (m *UserModel) FindAllByNameNotLike(ctx context.Context, where FindAllByNameNotLikeWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`name NOT LIKE ?`, where.Name)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdIn is generated from sql: +// select * from user where id in (?); +func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id IN (?)`, where.Id)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdNotIn is generated from sql: +// select * from user where id not in (?); +func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotInWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id NOT IN (?)`, where.Id)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdBetween is generated from sql: +// select * from user where id between ? and ?; +func (m *UserModel) FindAllByIdBetween(ctx context.Context, where FindAllByIdBetweenWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id BETWEEN ? AND ?`, where.IdBetweenStart, where.IdBetweenEnd)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdNotBetween is generated from sql: +// select * from user where id not between ? and ?; +func (m *UserModel) FindAllByIdNotBetween(ctx context.Context, where FindAllByIdNotBetweenWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id NOT BETWEEN ? AND ?`, where.IdNotBetweenStart, where.IdNotBetweenEnd)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdGte is generated from sql: +// select * from user where id >= ?; +func (m *UserModel) FindAllByIdGte(ctx context.Context, where FindAllByIdGteWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id >= ?`, where.Id)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdLte is generated from sql: +// select * from user where id <= ?; +func (m *UserModel) FindAllByIdLte(ctx context.Context, where FindAllByIdLteWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id <= ?`, where.Id)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdNeq is generated from sql: +// select * from user where id != ?; +func (m *UserModel) FindAllByIdNeq(ctx context.Context, where FindAllByIdNeqWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id != ?`, where.Id)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// FindAllByIdInOrNotIn is generated from sql: +// select * from user where id in (?) or id not in (?); +func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdInOrNotInWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} + +// ComplexQuery is generated from sql: +// select * from user where id > ? and id < ? and id != ? and id in (?) and id not in (?) and id between ? and ? and id not between ? and ? and id >= ? and id <= ? and id != ? and name like ? and name not like ? and name in (?) and name not in (?) and name between ? and ? and name not between ? and ? and name >= ? and name <= ? and name != ?; +func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWhereParameter) ([]*User, error) { + var result []*User + b := builder.Select(`*`) + b.From("`user`") + b.Where(builder.Expr(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6)) + query, args, err := b.ToSQL() + rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err +} diff --git a/example/sql/read/user_model.go b/example/sql/read/user_model.go new file mode 100644 index 0000000..8528c27 --- /dev/null +++ b/example/sql/read/user_model.go @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *UserModel) Customize(ctx context.Context, args ...any) { + +} diff --git a/example/sql/update/scanner.go b/example/sql/update/scanner.go new file mode 100644 index 0000000..7306a65 --- /dev/null +++ b/example/sql/update/scanner.go @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows *sql.Rows, v interface{}) error +} diff --git a/example/sql/update/user_model.gen.go b/example/sql/update/user_model.gen.go new file mode 100644 index 0000000..0963999 --- /dev/null +++ b/example/sql/update/user_model.gen.go @@ -0,0 +1,216 @@ +// Code generated by sqlgen. DO NOT EDIT! + +package model + +import ( + "context" + "database/sql" + "fmt" + "time" + + "xorm.io/builder" +) + +// UserModel represents a user model. +type UserModel struct { + db *sql.Conn + scanner Scanner +} + +// User represents a user struct data. +type User struct { + Id uint64 `json:"id"` + Name string `json:"name"` + Password string `json:"password"` + Mobile string `json:"mobile"` + Gender string `json:"gender"` + Nickname string `json:"nickname"` + Type int8 `json:"type"` + CreateTime time.Time `json:"createTime"` + UpdateTime time.Time `json:"updateTime"` +} + +// UpdateWhereParameter is a where parameter structure. +type UpdateWhereParameter struct { + Id uint64 +} + +// UpdateByNameWhereParameter is a where parameter structure. +type UpdateByNameWhereParameter struct { + Name string +} + +// UpdatePartWhereParameter is a where parameter structure. +type UpdatePartWhereParameter struct { + Id uint64 +} + +// UpdatePartByNameWhereParameter is a where parameter structure. +type UpdatePartByNameWhereParameter struct { + Name string +} + +// UpdateNameLimitWhereParameter is a where parameter structure. +type UpdateNameLimitWhereParameter struct { + Id uint64 +} + +// UpdateNameLimitLimitParameter is a limit parameter structure. +type UpdateNameLimitLimitParameter struct { + Count int +} + +// UpdateNameLimitOrderWhereParameter is a where parameter structure. +type UpdateNameLimitOrderWhereParameter struct { + Id uint64 +} + +// UpdateNameLimitOrderLimitParameter is a limit parameter structure. +type UpdateNameLimitOrderLimitParameter struct { + Count int +} + +// NewUserModel creates a new user model. +func NewUserModel(db *sql.Conn, scanner Scanner) *UserModel { + return &UserModel{ + db: db, + scanner: scanner, + } +} + +// Create creates user data. +func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { + if len(data) == 0 { + return fmt.Errorf("data is empty") + } + + var stmt *sql.Stmt + stmt, err = m.db.PrepareContext(ctx, "INSERT INTO user (`name`, `password`, `mobile`, `gender`, `nickname`, `type`, `create_time`, `update_time`) VALUES (?, ?, ?, ?, ?, ?, ?, ?)") + if err != nil { + return + } + defer func() { + err = stmt.Close() + }() + for _, v := range data { + _, err = stmt.ExecContext(ctx, v.Name, v.Password, v.Mobile, v.Gender, v.Nickname, v.Type, v.CreateTime, v.UpdateTime) + if err != nil { + return + } + } + return +} + +// Update is generated from sql: +// update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; +func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWhereParameter) error { + b := builder.Update(builder.Eq{ + "name": data.Name, + "password": data.Password, + "mobile": data.Mobile, + "gender": data.Gender, + "nickname": data.Nickname, + "type": data.Type, + "create_time": data.CreateTime, + "update_time": data.UpdateTime, + }) + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.Id)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// UpdateByName is generated from sql: +// update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; +func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateByNameWhereParameter) error { + b := builder.Update(builder.Eq{ + "password": data.Password, + "mobile": data.Mobile, + "gender": data.Gender, + "nickname": data.Nickname, + "type": data.Type, + "create_time": data.CreateTime, + "update_time": data.UpdateTime, + }) + b.From("`user`") + b.Where(builder.Expr(`name = ?`, where.Name)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// UpdatePart is generated from sql: +// update user set name = ?, nickname = ? where id = ?; +func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePartWhereParameter) error { + b := builder.Update(builder.Eq{ + "name": data.Name, + "nickname": data.Nickname, + }) + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.Id)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// UpdatePartByName is generated from sql: +// update user set name = ?, nickname = ? where name = ?; +func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where UpdatePartByNameWhereParameter) error { + b := builder.Update(builder.Eq{ + "name": data.Name, + "nickname": data.Nickname, + }) + b.From("`user`") + b.Where(builder.Expr(`name = ?`, where.Name)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// UpdateNameLimit is generated from sql: +// update user set name = ? where id > ? limit ?; +func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where UpdateNameLimitWhereParameter, limit UpdateNameLimitLimitParameter) error { + b := builder.Update(builder.Eq{ + "name": data.Name, + }) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.Limit(limit.Count) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// UpdateNameLimitOrder is generated from sql: +// update user set name = ? where id > ? order by id desc limit ?; +func (m *UserModel) UpdateNameLimitOrder(ctx context.Context, data *User, where UpdateNameLimitOrderWhereParameter, limit UpdateNameLimitOrderLimitParameter) error { + b := builder.Update(builder.Eq{ + "name": data.Name, + }) + b.From("`user`") + b.Where(builder.Expr(`id > ?`, where.Id)) + b.OrderBy(`id desc`) + b.Limit(limit.Count) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} diff --git a/example/sql/update/user_model.go b/example/sql/update/user_model.go new file mode 100644 index 0000000..8528c27 --- /dev/null +++ b/example/sql/update/user_model.go @@ -0,0 +1,8 @@ +package model + +import "context" + +// TODO(sqlgen): Add your own customize code here. +func (m *UserModel) Customize(ctx context.Context, args ...any) { + +} diff --git a/example/xorm/create/user_model.gen.go b/example/xorm/create/user_model.gen.go index 9ecfdb7..6f8eeb6 100644 --- a/example/xorm/create/user_model.gen.go +++ b/example/xorm/create/user_model.gen.go @@ -12,7 +12,7 @@ import ( // UserModel represents a user model. type UserModel struct { - engine *xorm.Engine + engine xorm.EngineInterface } // User represents a user struct data. @@ -32,6 +32,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(engine xorm.EngineInterface) *UserModel { + return &UserModel{engine: engine} +} + // Insert creates user data. func (m *UserModel) Insert(ctx context.Context, data ...*User) error { if len(data) == 0 { diff --git a/example/xorm/delete/user_model.gen.go b/example/xorm/delete/user_model.gen.go index 7e4d453..c9713d8 100644 --- a/example/xorm/delete/user_model.gen.go +++ b/example/xorm/delete/user_model.gen.go @@ -12,7 +12,7 @@ import ( // UserModel represents a user model. type UserModel struct { - engine *xorm.Engine + engine xorm.EngineInterface } // User represents a user struct data. @@ -48,6 +48,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(engine xorm.EngineInterface) *UserModel { + return &UserModel{engine: engine} +} + // Insert creates user data. func (m *UserModel) Insert(ctx context.Context, data ...*User) error { if len(data) == 0 { diff --git a/example/xorm/read/user_model.gen.go b/example/xorm/read/user_model.gen.go index 0269fd9..d10e67b 100644 --- a/example/xorm/read/user_model.gen.go +++ b/example/xorm/read/user_model.gen.go @@ -12,7 +12,7 @@ import ( // UserModel represents a user model. type UserModel struct { - engine *xorm.Engine + engine xorm.EngineInterface } // User represents a user struct data. @@ -260,6 +260,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(engine xorm.EngineInterface) *UserModel { + return &UserModel{engine: engine} +} + // Insert creates user data. func (m *UserModel) Insert(ctx context.Context, data ...*User) error { if len(data) == 0 { @@ -476,7 +481,7 @@ func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereP var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id IN ?`, where.Id) + session.Where(`id IN (?)`, where.Id) err := session.Find(&result) return result, err } @@ -487,7 +492,7 @@ func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotIn var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id NOT IN ?`, where.Id) + session.Where(`id NOT IN (?)`, where.Id) err := session.Find(&result) return result, err } @@ -553,7 +558,7 @@ func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdI var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id IN ? OR id NOT IN ?`, where.Id, where.Id1) + session.Where(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1) err := session.Find(&result) return result, err } @@ -564,7 +569,7 @@ func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWherePar var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id > ? AND id < ? AND id != ? AND id IN ? AND id NOT IN ? AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN ? AND name NOT IN ? AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) + session.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) err := session.Find(&result) return result, err } diff --git a/example/xorm/update/user_model.gen.go b/example/xorm/update/user_model.gen.go index 636550a..e4583df 100644 --- a/example/xorm/update/user_model.gen.go +++ b/example/xorm/update/user_model.gen.go @@ -12,7 +12,7 @@ import ( // UserModel represents a user model. type UserModel struct { - engine *xorm.Engine + engine xorm.EngineInterface } // User represents a user struct data. @@ -72,6 +72,11 @@ func (User) TableName() string { return "user" } +// NewUserModel returns a new user model. +func NewUserModel(engine xorm.EngineInterface) *UserModel { + return &UserModel{engine: engine} +} + // Insert creates user data. func (m *UserModel) Insert(ctx context.Context, data ...*User) error { if len(data) == 0 { @@ -92,7 +97,6 @@ func (m *UserModel) Insert(ctx context.Context, data ...*User) error { // update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWhereParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`id = ?`, where.Id) _, err := session.Update(map[string]interface{}{ "name": data.Name, @@ -111,7 +115,6 @@ func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWherePar // update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateByNameWhereParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`name = ?`, where.Name) _, err := session.Update(map[string]interface{}{ "password": data.Password, @@ -129,7 +132,6 @@ func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateBy // update user set name = ?, nickname = ? where id = ?; func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePartWhereParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`id = ?`, where.Id) _, err := session.Update(map[string]interface{}{ "name": data.Name, @@ -142,7 +144,6 @@ func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePart // update user set name = ?, nickname = ? where name = ?; func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where UpdatePartByNameWhereParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`name = ?`, where.Name) _, err := session.Update(map[string]interface{}{ "name": data.Name, @@ -155,7 +156,6 @@ func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where Upda // update user set name = ? where id > ? limit ?; func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where UpdateNameLimitWhereParameter, limit UpdateNameLimitLimitParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`id > ?`, where.Id) session.Limit(limit.Count) _, err := session.Update(map[string]interface{}{ @@ -168,7 +168,6 @@ func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where Updat // update user set name = ? where id > ? order by id desc limit ?; func (m *UserModel) UpdateNameLimitOrder(ctx context.Context, data *User, where UpdateNameLimitOrderWhereParameter, limit UpdateNameLimitOrderLimitParameter) error { var session = m.engine.Context(ctx) - session.Table(&User{}) session.Where(`id > ?`, where.Id) session.OrderBy(`id desc`) session.Limit(limit.Count) diff --git a/internal/gen/gorm/gorm_gen.tpl b/internal/gen/gorm/gorm_gen.tpl index da132b5..0f4be9b 100644 --- a/internal/gen/gorm/gorm_gen.tpl +++ b/internal/gen/gorm/gorm_gen.tpl @@ -42,6 +42,11 @@ func ({{UpperCamel $.Table.Name}}) TableName() string { return "{{$.Table.Name}}" } +// New{{UpperCamel $.Table.Name}}Model returns a new {{$.Table.Name}} model. +func New{{UpperCamel $.Table.Name}}Model (db gorm.DB) *{{UpperCamel $.Table.Name}}Model { + return &{{UpperCamel $.Table.Name}}Model{db: db} +} + // Create creates {{$.Table.Name}} data. func (m *{{UpperCamel $.Table.Name}}Model) Create(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) error { if len(data)==0{ diff --git a/internal/gen/sql/scanner.tpl b/internal/gen/sql/scanner.tpl new file mode 100644 index 0000000..858a4c3 --- /dev/null +++ b/internal/gen/sql/scanner.tpl @@ -0,0 +1,8 @@ +package model + +import "database/sql" + +type Scanner interface { + ScanRow(row *sql.Row, v interface{}) error + ScanRows(rows *sql.Rows, v interface{}) error +} \ No newline at end of file diff --git a/internal/gen/sql/sql.go b/internal/gen/sql/sql.go index fccad82..8816ac8 100644 --- a/internal/gen/sql/sql.go +++ b/internal/gen/sql/sql.go @@ -19,7 +19,16 @@ var sqlGenTpl string //go:embed sql_custom.tpl var sqlCustomTpl string +//go:embed scanner.tpl +var scannerTpl string + func Run(list []spec.Context, output string) error { + var scannerFilename = filepath.Join(output, "scanner.go") + scanner := templatex.New() + scanner.MustParse(scannerTpl) + scanner.MustExecute(nil) + scanner.MustSave(scannerFilename, true) + for _, ctx := range list { var genFilename = filepath.Join(output, fmt.Sprintf("%s_model.gen.go", ctx.Table.Name)) var customFilename = filepath.Join(output, fmt.Sprintf("%s_model.go", ctx.Table.Name)) @@ -52,6 +61,10 @@ func Run(list []spec.Context, output string) error { } return strings.Join(values, ", ") }, + "HavingSprintf": func(format string) string { + format = strings.ReplaceAll(format, "?", "%v") + return format + }, }) gen.MustParse(sqlGenTpl) gen.MustExecute(ctx) diff --git a/internal/gen/sql/sql_gen.tpl b/internal/gen/sql/sql_gen.tpl index 1fc30ec..11080b3 100644 --- a/internal/gen/sql/sql_gen.tpl +++ b/internal/gen/sql/sql_gen.tpl @@ -5,6 +5,7 @@ package model import ( "context" "fmt" + scanner2 "text/scanner" "time" "xorm.io/builder" @@ -13,7 +14,8 @@ import ( // {{UpperCamel $.Table.Name}}Model represents a {{$.Table.Name}} model. type {{UpperCamel $.Table.Name}}Model struct { - db sql.Conn + db *sql.Conn + scanner Scanner } // {{UpperCamel $.Table.Name}} represents a {{$.Table.Name}} struct data. @@ -36,6 +38,15 @@ type {{UpperCamel $.Table.Name}} struct { {{range $.Table.Columns}} {{end}} {{end}} + +// New{{UpperCamel $.Table.Name}}Model creates a new {{$.Table.Name}} model. +func New{{UpperCamel $.Table.Name}}Model(db *sql.Conn, scanner Scanner) *{{UpperCamel $.Table.Name}}Model { + return &{{UpperCamel $.Table.Name}}Model{ + db: db, + scanner: scanner, + } +} + // Create creates {{$.Table.Name}} data. func (m *{{UpperCamel $.Table.Name}}Model) Create(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) (err error) { if len(data) == 0 { @@ -58,3 +69,51 @@ func (m *{{UpperCamel $.Table.Name}}Model) Create(ctx context.Context, data ...* } return } +{{range $stmt := .SelectStmt}} +// {{.FuncName}} is generated from sql: +// {{$stmt.SQL}} +func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Having.IsValid}}, having {{$stmt.Having.ParameterStructureName "Having"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}})({{if $stmt.Limit.One}}*{{$stmt.ReceiverName}}, {{else}}[]*{{$stmt.ReceiverName}}, {{end}} error){ + var result {{if $stmt.Limit.One}} = new({{$stmt.ReceiverName}}){{else}}[]*{{$stmt.ReceiverName}}{{end}} + b := builder.Select(`{{$stmt.SelectSQL}}`) + b.From("`{{$.Table.Name}}`") + {{if $stmt.Where.IsValid}}b.Where(builder.Expr({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}})) + {{end }}{{if $stmt.GroupBy.IsValid}}b.GroupBy({{$stmt.GroupBy.SQL}}) + {{end}}{{if $stmt.Having.IsValid}}b.Having(fmt.Sprintf({{HavingSprintf $stmt.Having.SQL}}, {{$stmt.Having.Parameters "having"}})) + {{end}}{{if $stmt.OrderBy.IsValid}}b.OrderBy({{$stmt.OrderBy.SQL}}) + {{end}}{{if $stmt.Limit.IsValid}}b.Limit({{if $stmt.Limit.One}}1{{else}}{{$stmt.Limit.LimitParameter "limit"}}{{end}}{{if gt $stmt.Limit.Offset 0}}, {{$stmt.Limit.OffsetParameter "limit"}}{{end}}) + {{end}}query, args, err := b.ToSQL() + {{if $stmt.Limit.One}}row := m.db.QueryRowContext(ctx, query, args...) + if err := row.Err(); err != nil { + return nil, err + } + err = m.scanner.ScanRow(row, result) + return result, err + {{else}}rows, err := m.db.QueryContext(ctx, query, args...) + if err != nil { + return nil, err + } + err = m.scanner.ScanRows(rows, result) + return result, err{{end}} +} +{{end}} + +{{range $stmt := .UpdateStmt}} +// {{.FuncName}} is generated from sql: +// {{$stmt.SQL}} +func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context, data *{{UpperCamel $.Table.Name}}{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}}) error { + b := builder.Update(builder.Eq{ + {{range $name := $stmt.Columns}}"{{$name}}": data.{{UpperCamel $name}}, + {{end}} + }) + b.From("`{{$.Table.Name}}`") + {{if $stmt.Where.IsValid}}b.Where(builder.Expr({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}})) + {{end}}{{if $stmt.OrderBy.IsValid}}b.OrderBy({{$stmt.OrderBy.SQL}}) + {{end}}{{if $stmt.Limit.IsValid}}b.Limit({{if $stmt.Limit.One}}1{{else}}{{$stmt.Limit.LimitParameter "limit"}}{{end}}{{if gt $stmt.Limit.Offset 0}}, {{$stmt.Limit.OffsetParameter "limit"}}{{end}}) + {{end}}query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} +{{end}} diff --git a/internal/gen/xorm/xorm_gen.tpl b/internal/gen/xorm/xorm_gen.tpl index 778934a..a85c040 100644 --- a/internal/gen/xorm/xorm_gen.tpl +++ b/internal/gen/xorm/xorm_gen.tpl @@ -14,7 +14,7 @@ import ( // {{UpperCamel $.Table.Name}}Model represents a {{$.Table.Name}} model. type {{UpperCamel $.Table.Name}}Model struct { - engine *xorm.Engine + engine xorm.EngineInterface } // {{UpperCamel $.Table.Name}} represents a {{$.Table.Name}} struct data. @@ -42,6 +42,11 @@ func ({{UpperCamel $.Table.Name}}) TableName() string{ return "{{$.Table.Name}}" } +// New{{UpperCamel $.Table.Name}}Model returns a new {{$.Table.Name}} model. +func New{{UpperCamel $.Table.Name}}Model (engine xorm.EngineInterface) *{{UpperCamel $.Table.Name}}Model { + return &{{UpperCamel $.Table.Name}}Model{engine: engine} +} + // Insert creates {{$.Table.Name}} data. func (m *{{UpperCamel $.Table.Name}}Model) Insert(ctx context.Context, data ...*{{UpperCamel $.Table.Name}}) error { if len(data)==0{ @@ -79,7 +84,6 @@ func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if // {{$stmt.SQL}} func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context, data *{{UpperCamel $.Table.Name}}{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}}) error { var session = m.engine.Context(ctx) - session.Table(&{{UpperCamel $.Table.Name}}{}) {{if $stmt.Where.IsValid}}session.Where({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}}) {{end}}{{if $stmt.OrderBy.IsValid}}session.OrderBy({{$stmt.OrderBy.SQL}}) {{end}}{{if $stmt.Limit.IsValid}}session.Limit({{if $stmt.Limit.One}}1{{else}}{{$stmt.Limit.LimitParameter "limit"}}{{end}}{{if gt $stmt.Limit.Offset 0}}, {{$stmt.Limit.OffsetParameter "limit"}}{{end}}) From 392f21314fa6cb8a9b039d4a21848d292f5fbb06 Mon Sep 17 00:00:00 2001 From: anqiansong Date: Thu, 11 Aug 2022 18:30:48 +0800 Subject: [PATCH 5/5] Add sql --- example/bun/delete/example.sql | 10 +- example/gorm/delete/example.sql | 10 +- example/gorm/delete/user_model.gen.go | 50 ++++++++-- example/gorm/read/user_model.gen.go | 124 ++++++++++++------------- example/gorm/update/user_model.gen.go | 24 ++--- example/sql/delete/example.sql | 10 +- example/sql/delete/user_model.gen.go | 98 +++++++++++++++++++- example/sql/read/user_model.gen.go | 128 +++++++++++++------------- example/sql/update/user_model.gen.go | 24 ++--- example/sqlx/delete/example.sql | 10 +- example/xorm/delete/example.sql | 10 +- example/xorm/delete/user_model.gen.go | 50 ++++++++-- example/xorm/read/user_model.gen.go | 124 ++++++++++++------------- example/xorm/update/user_model.gen.go | 24 ++--- internal/gen/sql/sql_gen.tpl | 18 ++++ internal/gen/xorm/xorm_gen.tpl | 2 +- internal/parser/parser_test.go | 2 +- internal/parser/test.sql | 25 +---- internal/spec/clause.go | 20 ++-- 19 files changed, 483 insertions(+), 280 deletions(-) diff --git a/example/bun/delete/example.sql b/example/bun/delete/example.sql index e63b1c6..5a8cd81 100644 --- a/example/bun/delete/example.sql +++ b/example/bun/delete/example.sql @@ -25,4 +25,12 @@ delete from user where name = ?; -- example3: delete by unique keys -- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file +delete from user where name = ? and mobile = ?; + +-- example4: delete by id order by id +-- fn: DeleteOrderByID +delete from user where id = ? order by id desc; + +-- example5 delete by id order by id limit 10 +-- fn: DeleteOrderByIDLimit +delete from user where id = ? order by id desc limit 10; \ No newline at end of file diff --git a/example/gorm/delete/example.sql b/example/gorm/delete/example.sql index e63b1c6..5a8cd81 100644 --- a/example/gorm/delete/example.sql +++ b/example/gorm/delete/example.sql @@ -25,4 +25,12 @@ delete from user where name = ?; -- example3: delete by unique keys -- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file +delete from user where name = ? and mobile = ?; + +-- example4: delete by id order by id +-- fn: DeleteOrderByID +delete from user where id = ? order by id desc; + +-- example5 delete by id order by id limit 10 +-- fn: DeleteOrderByIDLimit +delete from user where id = ? order by id desc limit 10; \ No newline at end of file diff --git a/example/gorm/delete/user_model.gen.go b/example/gorm/delete/user_model.gen.go index 5a547e3..7699366 100644 --- a/example/gorm/delete/user_model.gen.go +++ b/example/gorm/delete/user_model.gen.go @@ -30,18 +30,33 @@ type User struct { // DeleteWhereParameter is a where parameter structure. type DeleteWhereParameter struct { - Id uint64 + IdEqual uint64 } // DeleteByNameWhereParameter is a where parameter structure. type DeleteByNameWhereParameter struct { - Name string + NameEqual string } // DeleteByNameAndMobileWhereParameter is a where parameter structure. type DeleteByNameAndMobileWhereParameter struct { - Name string - Mobile string + NameEqual string + MobileEqual string +} + +// DeleteOrderByIDWhereParameter is a where parameter structure. +type DeleteOrderByIDWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitWhereParameter is a where parameter structure. +type DeleteOrderByIDLimitWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitLimitParameter is a limit parameter structure. +type DeleteOrderByIDLimitLimitParameter struct { + Count int } // TableName returns the table name. it implemented by gorm.Tabler. @@ -73,7 +88,7 @@ func (m *UserModel) Create(ctx context.Context, data ...*User) error { // delete from user where id = ?; func (m *UserModel) Delete(ctx context.Context, where DeleteWhereParameter) error { var db = m.db.WithContext(ctx) - db.Where(`id = ?`, where.Id) + db.Where(`id = ?`, where.IdEqual) db.Delete(&User{}) return db.Error } @@ -82,7 +97,7 @@ func (m *UserModel) Delete(ctx context.Context, where DeleteWhereParameter) erro // delete from user where name = ?; func (m *UserModel) DeleteByName(ctx context.Context, where DeleteByNameWhereParameter) error { var db = m.db.WithContext(ctx) - db.Where(`name = ?`, where.Name) + db.Where(`name = ?`, where.NameEqual) db.Delete(&User{}) return db.Error } @@ -91,7 +106,28 @@ func (m *UserModel) DeleteByName(ctx context.Context, where DeleteByNameWherePar // delete from user where name = ? and mobile = ?; func (m *UserModel) DeleteByNameAndMobile(ctx context.Context, where DeleteByNameAndMobileWhereParameter) error { var db = m.db.WithContext(ctx) - db.Where(`name = ? AND mobile = ?`, where.Name, where.Mobile) + db.Where(`name = ? AND mobile = ?`, where.NameEqual, where.MobileEqual) + db.Delete(&User{}) + return db.Error +} + +// DeleteOrderByID is generated from sql: +// delete from user where id = ? order by id desc; +func (m *UserModel) DeleteOrderByID(ctx context.Context, where DeleteOrderByIDWhereParameter) error { + var db = m.db.WithContext(ctx) + db.Where(`id = ?`, where.IdEqual) + db.Order(`id desc`) + db.Delete(&User{}) + return db.Error +} + +// DeleteOrderByIDLimit is generated from sql: +// delete from user where id = ? order by id desc limit 10; +func (m *UserModel) DeleteOrderByIDLimit(ctx context.Context, where DeleteOrderByIDLimitWhereParameter, limit DeleteOrderByIDLimitLimitParameter) error { + var db = m.db.WithContext(ctx) + db.Where(`id = ?`, where.IdEqual) + db.Order(`id desc`) + db.Limit(limit.Count) db.Delete(&User{}) return db.Error } diff --git a/example/gorm/read/user_model.gen.go b/example/gorm/read/user_model.gen.go index 1a58d3e..b464e4e 100644 --- a/example/gorm/read/user_model.gen.go +++ b/example/gorm/read/user_model.gen.go @@ -30,22 +30,22 @@ type User struct { // FindOneWhereParameter is a where parameter structure. type FindOneWhereParameter struct { - Id uint64 + IdEqual uint64 } // FindByNameWhereParameter is a where parameter structure. type FindByNameWhereParameter struct { - Name string + NameEqual string } // FindOnePartWhereParameter is a where parameter structure. type FindOnePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // FindByNamePartWhereParameter is a where parameter structure. type FindByNamePartWhereParameter struct { - Name string + NameEqual string } // FindAllCountResult is a find all count result. @@ -60,23 +60,23 @@ type FindAllPartCountResult struct { // FindOneByNameAndPasswordWhereParameter is a where parameter structure. type FindOneByNameAndPasswordWhereParameter struct { - Name string - Password string + NameEqual string + PasswordEqual string } // ListUserByNameAscWhereParameter is a where parameter structure. type ListUserByNameAscWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtWhereParameter is a where parameter structure. type ListUserByNameAscHavingCountTypeGtWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtHavingParameter is a having parameter structure. type ListUserByNameAscHavingCountTypeGtHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameAscHavingCountTypeGtResult is a list user by name asc having count type gt result. @@ -95,12 +95,12 @@ type ListUserByNameAscHavingCountTypeGtResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescResult is a list user by name desc having count type gt order by id desc result. @@ -119,12 +119,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter is a limit parameter structure. @@ -148,12 +148,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter is a limit parameter structure. @@ -178,22 +178,22 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result struc // FindOneByNameLikeWhereParameter is a where parameter structure. type FindOneByNameLikeWhereParameter struct { - Name string + NameLike string } // FindAllByNameNotLikeWhereParameter is a where parameter structure. type FindAllByNameNotLikeWhereParameter struct { - Name string + NameNotLike string } // FindAllByIdInWhereParameter is a where parameter structure. type FindAllByIdInWhereParameter struct { - Id []uint64 + IdIn []uint64 } // FindAllByIdNotInWhereParameter is a where parameter structure. type FindAllByIdNotInWhereParameter struct { - Id []uint64 + IdNotIn []uint64 } // FindAllByIdBetweenWhereParameter is a where parameter structure. @@ -210,50 +210,50 @@ type FindAllByIdNotBetweenWhereParameter struct { // FindAllByIdGteWhereParameter is a where parameter structure. type FindAllByIdGteWhereParameter struct { - Id uint64 + IdGE uint64 } // FindAllByIdLteWhereParameter is a where parameter structure. type FindAllByIdLteWhereParameter struct { - Id uint64 + IdLE uint64 } // FindAllByIdNeqWhereParameter is a where parameter structure. type FindAllByIdNeqWhereParameter struct { - Id uint64 + IdNE uint64 } // FindAllByIdInOrNotInWhereParameter is a where parameter structure. type FindAllByIdInOrNotInWhereParameter struct { - Id uint64 - Id1 uint64 + IdIn []uint64 + IdNotIn []uint64 } // ComplexQueryWhereParameter is a where parameter structure. type ComplexQueryWhereParameter struct { - Id uint64 - Id1 uint64 - Id2 uint64 - Id3 uint64 - Id4 uint64 + IdGT uint64 + IdLT uint64 + IdNE uint64 + IdIn []uint64 + IdNotIn []uint64 IdBetweenStart uint64 IdBetweenEnd uint64 IdNotBetweenStart uint64 IdNotBetweenEnd uint64 - Id5 uint64 - Id6 uint64 - Id7 uint64 - Name string - Name1 string - Name2 string - Name3 string + IdGE uint64 + IdLE uint64 + IdNE1 uint64 + NameLike string + NameNotLike string + NameIn []string + NameNotIn []string NameBetweenStart string NameBetweenEnd string NameNotBetweenStart string NameNotBetweenEnd string - Name4 string - Name5 string - Name6 string + NameGE string + NameLE string + NameNE string } // TableName returns the table name. it implemented by gorm.Tabler. @@ -287,7 +287,7 @@ func (m *UserModel) FindOne(ctx context.Context, where FindOneWhereParameter) (* var result = new(User) var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id = ?`, where.Id) + db.Where(`id = ?`, where.IdEqual) db.Limit(1) db.Find(result) return result, db.Error @@ -299,7 +299,7 @@ func (m *UserModel) FindByName(ctx context.Context, where FindByNameWhereParamet var result = new(User) var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`name = ?`, where.Name) + db.Where(`name = ?`, where.NameEqual) db.Limit(1) db.Find(result) return result, db.Error @@ -311,7 +311,7 @@ func (m *UserModel) FindOnePart(ctx context.Context, where FindOnePartWhereParam var result = new(User) var db = m.db.WithContext(ctx) db.Select(`id, name, nickname`) - db.Where(`id = ?`, where.Id) + db.Where(`id = ?`, where.IdEqual) db.Limit(1) db.Find(result) return result, db.Error @@ -323,7 +323,7 @@ func (m *UserModel) FindByNamePart(ctx context.Context, where FindByNamePartWher var result = new(User) var db = m.db.WithContext(ctx) db.Select(`id, name, nickname`) - db.Where(`name = ?`, where.Name) + db.Where(`name = ?`, where.NameEqual) db.Limit(1) db.Find(result) return result, db.Error @@ -377,7 +377,7 @@ func (m *UserModel) FindOneByNameAndPassword(ctx context.Context, where FindOneB var result = new(User) var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`name = ? AND password = ?`, where.Name, where.Password) + db.Where(`name = ? AND password = ?`, where.NameEqual, where.PasswordEqual) db.Limit(1) db.Find(result) return result, db.Error @@ -389,7 +389,7 @@ func (m *UserModel) ListUserByNameAsc(ctx context.Context, where ListUserByNameA var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Group(`name`) db.Find(&result) return result, db.Error @@ -401,9 +401,9 @@ func (m *UserModel) ListUserByNameAscHavingCountTypeGt(ctx context.Context, wher var result []*ListUserByNameAscHavingCountTypeGtResult var db = m.db.WithContext(ctx) db.Select(`*, count(type) AS typeCount`) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Group(`name`) - db.Having(`typeCount > ?`, having.TypeCount) + db.Having(`typeCount > ?`, having.TypeCountGT) db.Find(&result) return result, db.Error } @@ -414,9 +414,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDesc(ctx context var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescResult var db = m.db.WithContext(ctx) db.Select(`*, count(type) AS typeCount`) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Group(`name`) - db.Having(`typeCount > ?`, having.TypeCount) + db.Having(`typeCount > ?`, having.TypeCountGT) db.Order(`id desc`) db.Find(&result) return result, db.Error @@ -428,9 +428,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result var db = m.db.WithContext(ctx) db.Select(`*, count(type) AS typeCount`) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Group(`name`) - db.Having(`typeCount > ?`, having.TypeCount) + db.Having(`typeCount > ?`, having.TypeCountGT) db.Order(`id desc`) db.Limit(limit.Count) db.Find(&result) @@ -443,9 +443,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offse var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result var db = m.db.WithContext(ctx) db.Select(`*, count(type) AS typeCount`) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Group(`name`) - db.Having(`typeCount > ?`, having.TypeCount) + db.Having(`typeCount > ?`, having.TypeCountGT) db.Order(`id desc`) db.Offset(limit.Offset).Limit(limit.Count) db.Find(&result) @@ -458,7 +458,7 @@ func (m *UserModel) FindOneByNameLike(ctx context.Context, where FindOneByNameLi var result = new(User) var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`name LIKE ?`, where.Name) + db.Where(`name LIKE ?`, where.NameLike) db.Limit(1) db.Find(result) return result, db.Error @@ -470,7 +470,7 @@ func (m *UserModel) FindAllByNameNotLike(ctx context.Context, where FindAllByNam var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`name NOT LIKE ?`, where.Name) + db.Where(`name NOT LIKE ?`, where.NameNotLike) db.Find(&result) return result, db.Error } @@ -481,7 +481,7 @@ func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereP var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id IN (?)`, where.Id) + db.Where(`id IN (?)`, where.IdIn) db.Find(&result) return result, db.Error } @@ -492,7 +492,7 @@ func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotIn var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id NOT IN (?)`, where.Id) + db.Where(`id NOT IN (?)`, where.IdNotIn) db.Find(&result) return result, db.Error } @@ -525,7 +525,7 @@ func (m *UserModel) FindAllByIdGte(ctx context.Context, where FindAllByIdGteWher var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id >= ?`, where.Id) + db.Where(`id >= ?`, where.IdGE) db.Find(&result) return result, db.Error } @@ -536,7 +536,7 @@ func (m *UserModel) FindAllByIdLte(ctx context.Context, where FindAllByIdLteWher var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id <= ?`, where.Id) + db.Where(`id <= ?`, where.IdLE) db.Find(&result) return result, db.Error } @@ -547,7 +547,7 @@ func (m *UserModel) FindAllByIdNeq(ctx context.Context, where FindAllByIdNeqWher var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id != ?`, where.Id) + db.Where(`id != ?`, where.IdNE) db.Find(&result) return result, db.Error } @@ -558,7 +558,7 @@ func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdI var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1) + db.Where(`id IN (?) OR id NOT IN (?)`, where.IdIn, where.IdNotIn) db.Find(&result) return result, db.Error } @@ -569,7 +569,7 @@ func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWherePar var result []*User var db = m.db.WithContext(ctx) db.Select(`*`) - db.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) + db.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.IdGT, where.IdLT, where.IdNE, where.IdIn, where.IdNotIn, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.IdGE, where.IdLE, where.IdNE1, where.NameLike, where.NameNotLike, where.NameIn, where.NameNotIn, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.NameGE, where.NameLE, where.NameNE) db.Find(&result) return result, db.Error } diff --git a/example/gorm/update/user_model.gen.go b/example/gorm/update/user_model.gen.go index fa08b49..2af3139 100644 --- a/example/gorm/update/user_model.gen.go +++ b/example/gorm/update/user_model.gen.go @@ -30,27 +30,27 @@ type User struct { // UpdateWhereParameter is a where parameter structure. type UpdateWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdateByNameWhereParameter is a where parameter structure. type UpdateByNameWhereParameter struct { - Name string + NameEqual string } // UpdatePartWhereParameter is a where parameter structure. type UpdatePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdatePartByNameWhereParameter is a where parameter structure. type UpdatePartByNameWhereParameter struct { - Name string + NameEqual string } // UpdateNameLimitWhereParameter is a where parameter structure. type UpdateNameLimitWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitLimitParameter is a limit parameter structure. @@ -60,7 +60,7 @@ type UpdateNameLimitLimitParameter struct { // UpdateNameLimitOrderWhereParameter is a where parameter structure. type UpdateNameLimitOrderWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitOrderLimitParameter is a limit parameter structure. @@ -98,7 +98,7 @@ func (m *UserModel) Create(ctx context.Context, data ...*User) error { func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWhereParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`id = ?`, where.Id) + db.Where(`id = ?`, where.IdEqual) db.Updates(map[string]interface{}{ "name": data.Name, "password": data.Password, @@ -117,7 +117,7 @@ func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWherePar func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateByNameWhereParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`name = ?`, where.Name) + db.Where(`name = ?`, where.NameEqual) db.Updates(map[string]interface{}{ "password": data.Password, "mobile": data.Mobile, @@ -135,7 +135,7 @@ func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateBy func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePartWhereParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`id = ?`, where.Id) + db.Where(`id = ?`, where.IdEqual) db.Updates(map[string]interface{}{ "name": data.Name, "nickname": data.Nickname, @@ -148,7 +148,7 @@ func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePart func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where UpdatePartByNameWhereParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`name = ?`, where.Name) + db.Where(`name = ?`, where.NameEqual) db.Updates(map[string]interface{}{ "name": data.Name, "nickname": data.Nickname, @@ -161,7 +161,7 @@ func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where Upda func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where UpdateNameLimitWhereParameter, limit UpdateNameLimitLimitParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Limit(limit.Count) db.Updates(map[string]interface{}{ "name": data.Name, @@ -174,7 +174,7 @@ func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where Updat func (m *UserModel) UpdateNameLimitOrder(ctx context.Context, data *User, where UpdateNameLimitOrderWhereParameter, limit UpdateNameLimitOrderLimitParameter) error { var db = m.db.WithContext(ctx) db.Model(&User{}) - db.Where(`id > ?`, where.Id) + db.Where(`id > ?`, where.IdGT) db.Order(`id desc`) db.Limit(limit.Count) db.Updates(map[string]interface{}{ diff --git a/example/sql/delete/example.sql b/example/sql/delete/example.sql index e63b1c6..5a8cd81 100644 --- a/example/sql/delete/example.sql +++ b/example/sql/delete/example.sql @@ -25,4 +25,12 @@ delete from user where name = ?; -- example3: delete by unique keys -- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file +delete from user where name = ? and mobile = ?; + +-- example4: delete by id order by id +-- fn: DeleteOrderByID +delete from user where id = ? order by id desc; + +-- example5 delete by id order by id limit 10 +-- fn: DeleteOrderByIDLimit +delete from user where id = ? order by id desc limit 10; \ No newline at end of file diff --git a/example/sql/delete/user_model.gen.go b/example/sql/delete/user_model.gen.go index eeba285..4041774 100644 --- a/example/sql/delete/user_model.gen.go +++ b/example/sql/delete/user_model.gen.go @@ -7,6 +7,8 @@ import ( "database/sql" "fmt" "time" + + "xorm.io/builder" ) // UserModel represents a user model. @@ -30,18 +32,33 @@ type User struct { // DeleteWhereParameter is a where parameter structure. type DeleteWhereParameter struct { - Id uint64 + IdEqual uint64 } // DeleteByNameWhereParameter is a where parameter structure. type DeleteByNameWhereParameter struct { - Name string + NameEqual string } // DeleteByNameAndMobileWhereParameter is a where parameter structure. type DeleteByNameAndMobileWhereParameter struct { - Name string - Mobile string + NameEqual string + MobileEqual string +} + +// DeleteOrderByIDWhereParameter is a where parameter structure. +type DeleteOrderByIDWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitWhereParameter is a where parameter structure. +type DeleteOrderByIDLimitWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitLimitParameter is a limit parameter structure. +type DeleteOrderByIDLimitLimitParameter struct { + Count int } // NewUserModel creates a new user model. @@ -74,3 +91,76 @@ func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { } return } + +// Delete is generated from sql: +// delete from user where id = ?; +func (m *UserModel) Delete(ctx context.Context, where DeleteWhereParameter) error { + b := builder.Delete() + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.IdEqual)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// DeleteByName is generated from sql: +// delete from user where name = ?; +func (m *UserModel) DeleteByName(ctx context.Context, where DeleteByNameWhereParameter) error { + b := builder.Delete() + b.From("`user`") + b.Where(builder.Expr(`name = ?`, where.NameEqual)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// DeleteByNameAndMobile is generated from sql: +// delete from user where name = ? and mobile = ?; +func (m *UserModel) DeleteByNameAndMobile(ctx context.Context, where DeleteByNameAndMobileWhereParameter) error { + b := builder.Delete() + b.From("`user`") + b.Where(builder.Expr(`name = ? AND mobile = ?`, where.NameEqual, where.MobileEqual)) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// DeleteOrderByID is generated from sql: +// delete from user where id = ? order by id desc; +func (m *UserModel) DeleteOrderByID(ctx context.Context, where DeleteOrderByIDWhereParameter) error { + b := builder.Delete() + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.IdEqual)) + b.OrderBy(`id desc`) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} + +// DeleteOrderByIDLimit is generated from sql: +// delete from user where id = ? order by id desc limit 10; +func (m *UserModel) DeleteOrderByIDLimit(ctx context.Context, where DeleteOrderByIDLimitWhereParameter, limit DeleteOrderByIDLimitLimitParameter) error { + b := builder.Delete() + b.From("`user`") + b.Where(builder.Expr(`id = ?`, where.IdEqual)) + b.OrderBy(`id desc`) + b.Limit(limit.Count) + query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} diff --git a/example/sql/read/user_model.gen.go b/example/sql/read/user_model.gen.go index 610e6b7..1b9852f 100644 --- a/example/sql/read/user_model.gen.go +++ b/example/sql/read/user_model.gen.go @@ -32,23 +32,23 @@ type User struct { // FindOneWhereParameter is a where parameter structure. type FindOneWhereParameter struct { - Id uint64 - Name string + IdEqual uint64 + NameIn []string } // FindByNameWhereParameter is a where parameter structure. type FindByNameWhereParameter struct { - Name string + NameEqual string } // FindOnePartWhereParameter is a where parameter structure. type FindOnePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // FindByNamePartWhereParameter is a where parameter structure. type FindByNamePartWhereParameter struct { - Name string + NameEqual string } // FindAllCountResult is a find all count result. @@ -63,23 +63,23 @@ type FindAllPartCountResult struct { // FindOneByNameAndPasswordWhereParameter is a where parameter structure. type FindOneByNameAndPasswordWhereParameter struct { - Name string - Password string + NameEqual string + PasswordEqual string } // ListUserByNameAscWhereParameter is a where parameter structure. type ListUserByNameAscWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtWhereParameter is a where parameter structure. type ListUserByNameAscHavingCountTypeGtWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtHavingParameter is a having parameter structure. type ListUserByNameAscHavingCountTypeGtHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameAscHavingCountTypeGtResult is a list user by name asc having count type gt result. @@ -98,12 +98,12 @@ type ListUserByNameAscHavingCountTypeGtResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescResult is a list user by name desc having count type gt order by id desc result. @@ -122,12 +122,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter is a limit parameter structure. @@ -151,12 +151,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter is a limit parameter structure. @@ -181,22 +181,22 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result struc // FindOneByNameLikeWhereParameter is a where parameter structure. type FindOneByNameLikeWhereParameter struct { - Name string + NameLike string } // FindAllByNameNotLikeWhereParameter is a where parameter structure. type FindAllByNameNotLikeWhereParameter struct { - Name string + NameNotLike string } // FindAllByIdInWhereParameter is a where parameter structure. type FindAllByIdInWhereParameter struct { - Id []uint64 + IdIn []uint64 } // FindAllByIdNotInWhereParameter is a where parameter structure. type FindAllByIdNotInWhereParameter struct { - Id []uint64 + IdNotIn []uint64 } // FindAllByIdBetweenWhereParameter is a where parameter structure. @@ -213,50 +213,50 @@ type FindAllByIdNotBetweenWhereParameter struct { // FindAllByIdGteWhereParameter is a where parameter structure. type FindAllByIdGteWhereParameter struct { - Id uint64 + IdGE uint64 } // FindAllByIdLteWhereParameter is a where parameter structure. type FindAllByIdLteWhereParameter struct { - Id uint64 + IdLE uint64 } // FindAllByIdNeqWhereParameter is a where parameter structure. type FindAllByIdNeqWhereParameter struct { - Id uint64 + IdNE uint64 } // FindAllByIdInOrNotInWhereParameter is a where parameter structure. type FindAllByIdInOrNotInWhereParameter struct { - Id uint64 - Id1 uint64 + IdIn []uint64 + IdNotIn []uint64 } // ComplexQueryWhereParameter is a where parameter structure. type ComplexQueryWhereParameter struct { - Id uint64 - Id1 uint64 - Id2 uint64 - Id3 uint64 - Id4 uint64 + IdGT uint64 + IdLT uint64 + IdNE uint64 + IdIn []uint64 + IdNotIn []uint64 IdBetweenStart uint64 IdBetweenEnd uint64 IdNotBetweenStart uint64 IdNotBetweenEnd uint64 - Id5 uint64 - Id6 uint64 - Id7 uint64 - Name string - Name1 string - Name2 string - Name3 string + IdGE uint64 + IdLE uint64 + IdNE1 uint64 + NameLike string + NameNotLike string + NameIn []string + NameNotIn []string NameBetweenStart string NameBetweenEnd string NameNotBetweenStart string NameNotBetweenEnd string - Name4 string - Name5 string - Name6 string + NameGE string + NameLE string + NameNE string } // NewUserModel creates a new user model. @@ -291,12 +291,12 @@ func (m *UserModel) Create(ctx context.Context, data ...*User) (err error) { } // FindOne is generated from sql: -// select * from user where id = ? and name in (?) limit 1; +// select * from user where id = ? and name in (?,?,?) limit 1; func (m *UserModel) FindOne(ctx context.Context, where FindOneWhereParameter) (*User, error) { var result = new(User) b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id = ? AND name IN (?)`, where.Id, where.Name)) + b.Where(builder.Expr(`id = ? AND name IN (?)`, where.IdEqual, where.NameIn)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -314,7 +314,7 @@ func (m *UserModel) FindByName(ctx context.Context, where FindByNameWhereParamet var result = new(User) b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`name = ?`, where.Name)) + b.Where(builder.Expr(`name = ?`, where.NameEqual)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -332,7 +332,7 @@ func (m *UserModel) FindOnePart(ctx context.Context, where FindOnePartWhereParam var result = new(User) b := builder.Select(`id, name, nickname`) b.From("`user`") - b.Where(builder.Expr(`id = ?`, where.Id)) + b.Where(builder.Expr(`id = ?`, where.IdEqual)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -350,7 +350,7 @@ func (m *UserModel) FindByNamePart(ctx context.Context, where FindByNamePartWher var result = new(User) b := builder.Select(`id, name, nickname`) b.From("`user`") - b.Where(builder.Expr(`name = ?`, where.Name)) + b.Where(builder.Expr(`name = ?`, where.NameEqual)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -432,7 +432,7 @@ func (m *UserModel) FindOneByNameAndPassword(ctx context.Context, where FindOneB var result = new(User) b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`name = ? AND password = ?`, where.Name, where.Password)) + b.Where(builder.Expr(`name = ? AND password = ?`, where.NameEqual, where.PasswordEqual)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -450,7 +450,7 @@ func (m *UserModel) ListUserByNameAsc(ctx context.Context, where ListUserByNameA var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.GroupBy(`name`) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) @@ -467,9 +467,9 @@ func (m *UserModel) ListUserByNameAscHavingCountTypeGt(ctx context.Context, wher var result []*ListUserByNameAscHavingCountTypeGtResult b := builder.Select(`*, count(type) AS typeCount`) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.GroupBy(`name`) - b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -485,9 +485,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDesc(ctx context var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescResult b := builder.Select(`*, count(type) AS typeCount`) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.GroupBy(`name`) - b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) b.OrderBy(`id desc`) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) @@ -504,9 +504,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result b := builder.Select(`*, count(type) AS typeCount`) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.GroupBy(`name`) - b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) b.OrderBy(`id desc`) b.Limit(limit.Count) query, args, err := b.ToSQL() @@ -524,9 +524,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offse var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result b := builder.Select(`*, count(type) AS typeCount`) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.GroupBy(`name`) - b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + b.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) b.OrderBy(`id desc`) b.Limit(limit.Count, limit.Offset) query, args, err := b.ToSQL() @@ -544,7 +544,7 @@ func (m *UserModel) FindOneByNameLike(ctx context.Context, where FindOneByNameLi var result = new(User) b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`name LIKE ?`, where.Name)) + b.Where(builder.Expr(`name LIKE ?`, where.NameLike)) b.Limit(1) query, args, err := b.ToSQL() row := m.db.QueryRowContext(ctx, query, args...) @@ -562,7 +562,7 @@ func (m *UserModel) FindAllByNameNotLike(ctx context.Context, where FindAllByNam var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`name NOT LIKE ?`, where.Name)) + b.Where(builder.Expr(`name NOT LIKE ?`, where.NameNotLike)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -578,7 +578,7 @@ func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereP var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id IN (?)`, where.Id)) + b.Where(builder.Expr(`id IN (?)`, where.IdIn)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -594,7 +594,7 @@ func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotIn var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id NOT IN (?)`, where.Id)) + b.Where(builder.Expr(`id NOT IN (?)`, where.IdNotIn)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -642,7 +642,7 @@ func (m *UserModel) FindAllByIdGte(ctx context.Context, where FindAllByIdGteWher var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id >= ?`, where.Id)) + b.Where(builder.Expr(`id >= ?`, where.IdGE)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -658,7 +658,7 @@ func (m *UserModel) FindAllByIdLte(ctx context.Context, where FindAllByIdLteWher var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id <= ?`, where.Id)) + b.Where(builder.Expr(`id <= ?`, where.IdLE)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -674,7 +674,7 @@ func (m *UserModel) FindAllByIdNeq(ctx context.Context, where FindAllByIdNeqWher var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id != ?`, where.Id)) + b.Where(builder.Expr(`id != ?`, where.IdNE)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -690,7 +690,7 @@ func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdI var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1)) + b.Where(builder.Expr(`id IN (?) OR id NOT IN (?)`, where.IdIn, where.IdNotIn)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { @@ -706,7 +706,7 @@ func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWherePar var result []*User b := builder.Select(`*`) b.From("`user`") - b.Where(builder.Expr(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6)) + b.Where(builder.Expr(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.IdGT, where.IdLT, where.IdNE, where.IdIn, where.IdNotIn, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.IdGE, where.IdLE, where.IdNE1, where.NameLike, where.NameNotLike, where.NameIn, where.NameNotIn, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.NameGE, where.NameLE, where.NameNE)) query, args, err := b.ToSQL() rows, err := m.db.QueryContext(ctx, query, args...) if err != nil { diff --git a/example/sql/update/user_model.gen.go b/example/sql/update/user_model.gen.go index 0963999..bc17da8 100644 --- a/example/sql/update/user_model.gen.go +++ b/example/sql/update/user_model.gen.go @@ -32,27 +32,27 @@ type User struct { // UpdateWhereParameter is a where parameter structure. type UpdateWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdateByNameWhereParameter is a where parameter structure. type UpdateByNameWhereParameter struct { - Name string + NameEqual string } // UpdatePartWhereParameter is a where parameter structure. type UpdatePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdatePartByNameWhereParameter is a where parameter structure. type UpdatePartByNameWhereParameter struct { - Name string + NameEqual string } // UpdateNameLimitWhereParameter is a where parameter structure. type UpdateNameLimitWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitLimitParameter is a limit parameter structure. @@ -62,7 +62,7 @@ type UpdateNameLimitLimitParameter struct { // UpdateNameLimitOrderWhereParameter is a where parameter structure. type UpdateNameLimitOrderWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitOrderLimitParameter is a limit parameter structure. @@ -115,7 +115,7 @@ func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWherePar "update_time": data.UpdateTime, }) b.From("`user`") - b.Where(builder.Expr(`id = ?`, where.Id)) + b.Where(builder.Expr(`id = ?`, where.IdEqual)) query, args, err := b.ToSQL() if err != nil { return err @@ -137,7 +137,7 @@ func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateBy "update_time": data.UpdateTime, }) b.From("`user`") - b.Where(builder.Expr(`name = ?`, where.Name)) + b.Where(builder.Expr(`name = ?`, where.NameEqual)) query, args, err := b.ToSQL() if err != nil { return err @@ -154,7 +154,7 @@ func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePart "nickname": data.Nickname, }) b.From("`user`") - b.Where(builder.Expr(`id = ?`, where.Id)) + b.Where(builder.Expr(`id = ?`, where.IdEqual)) query, args, err := b.ToSQL() if err != nil { return err @@ -171,7 +171,7 @@ func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where Upda "nickname": data.Nickname, }) b.From("`user`") - b.Where(builder.Expr(`name = ?`, where.Name)) + b.Where(builder.Expr(`name = ?`, where.NameEqual)) query, args, err := b.ToSQL() if err != nil { return err @@ -187,7 +187,7 @@ func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where Updat "name": data.Name, }) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.Limit(limit.Count) query, args, err := b.ToSQL() if err != nil { @@ -204,7 +204,7 @@ func (m *UserModel) UpdateNameLimitOrder(ctx context.Context, data *User, where "name": data.Name, }) b.From("`user`") - b.Where(builder.Expr(`id > ?`, where.Id)) + b.Where(builder.Expr(`id > ?`, where.IdGT)) b.OrderBy(`id desc`) b.Limit(limit.Count) query, args, err := b.ToSQL() diff --git a/example/sqlx/delete/example.sql b/example/sqlx/delete/example.sql index e63b1c6..5a8cd81 100644 --- a/example/sqlx/delete/example.sql +++ b/example/sqlx/delete/example.sql @@ -25,4 +25,12 @@ delete from user where name = ?; -- example3: delete by unique keys -- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file +delete from user where name = ? and mobile = ?; + +-- example4: delete by id order by id +-- fn: DeleteOrderByID +delete from user where id = ? order by id desc; + +-- example5 delete by id order by id limit 10 +-- fn: DeleteOrderByIDLimit +delete from user where id = ? order by id desc limit 10; \ No newline at end of file diff --git a/example/xorm/delete/example.sql b/example/xorm/delete/example.sql index e63b1c6..5a8cd81 100644 --- a/example/xorm/delete/example.sql +++ b/example/xorm/delete/example.sql @@ -25,4 +25,12 @@ delete from user where name = ?; -- example3: delete by unique keys -- fn: DeleteByNameAndMobile -delete from user where name = ? and mobile = ?; \ No newline at end of file +delete from user where name = ? and mobile = ?; + +-- example4: delete by id order by id +-- fn: DeleteOrderByID +delete from user where id = ? order by id desc; + +-- example5 delete by id order by id limit 10 +-- fn: DeleteOrderByIDLimit +delete from user where id = ? order by id desc limit 10; \ No newline at end of file diff --git a/example/xorm/delete/user_model.gen.go b/example/xorm/delete/user_model.gen.go index c9713d8..7bca79e 100644 --- a/example/xorm/delete/user_model.gen.go +++ b/example/xorm/delete/user_model.gen.go @@ -30,18 +30,33 @@ type User struct { // DeleteWhereParameter is a where parameter structure. type DeleteWhereParameter struct { - Id uint64 + IdEqual uint64 } // DeleteByNameWhereParameter is a where parameter structure. type DeleteByNameWhereParameter struct { - Name string + NameEqual string } // DeleteByNameAndMobileWhereParameter is a where parameter structure. type DeleteByNameAndMobileWhereParameter struct { - Name string - Mobile string + NameEqual string + MobileEqual string +} + +// DeleteOrderByIDWhereParameter is a where parameter structure. +type DeleteOrderByIDWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitWhereParameter is a where parameter structure. +type DeleteOrderByIDLimitWhereParameter struct { + IdEqual uint64 +} + +// DeleteOrderByIDLimitLimitParameter is a limit parameter structure. +type DeleteOrderByIDLimitLimitParameter struct { + Count int } func (User) TableName() string { @@ -73,7 +88,7 @@ func (m *UserModel) Insert(ctx context.Context, data ...*User) error { // delete from user where id = ?; func (m *UserModel) Delete(ctx context.Context, where DeleteWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`id = ?`, where.Id) + session.Where(`id = ?`, where.IdEqual) _, err := session.Delete(&User{}) return err } @@ -82,7 +97,7 @@ func (m *UserModel) Delete(ctx context.Context, where DeleteWhereParameter) erro // delete from user where name = ?; func (m *UserModel) DeleteByName(ctx context.Context, where DeleteByNameWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`name = ?`, where.Name) + session.Where(`name = ?`, where.NameEqual) _, err := session.Delete(&User{}) return err } @@ -91,7 +106,28 @@ func (m *UserModel) DeleteByName(ctx context.Context, where DeleteByNameWherePar // delete from user where name = ? and mobile = ?; func (m *UserModel) DeleteByNameAndMobile(ctx context.Context, where DeleteByNameAndMobileWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`name = ? AND mobile = ?`, where.Name, where.Mobile) + session.Where(`name = ? AND mobile = ?`, where.NameEqual, where.MobileEqual) + _, err := session.Delete(&User{}) + return err +} + +// DeleteOrderByID is generated from sql: +// delete from user where id = ? order by id desc; +func (m *UserModel) DeleteOrderByID(ctx context.Context, where DeleteOrderByIDWhereParameter) error { + var session = m.engine.Context(ctx) + session.Where(`id = ?`, where.IdEqual) + session.OrderBy(`id desc`) + _, err := session.Delete(&User{}) + return err +} + +// DeleteOrderByIDLimit is generated from sql: +// delete from user where id = ? order by id desc limit 10; +func (m *UserModel) DeleteOrderByIDLimit(ctx context.Context, where DeleteOrderByIDLimitWhereParameter, limit DeleteOrderByIDLimitLimitParameter) error { + var session = m.engine.Context(ctx) + session.Where(`id = ?`, where.IdEqual) + session.OrderBy(`id desc`) + session.Limit(limit.Count) _, err := session.Delete(&User{}) return err } diff --git a/example/xorm/read/user_model.gen.go b/example/xorm/read/user_model.gen.go index d10e67b..40f8362 100644 --- a/example/xorm/read/user_model.gen.go +++ b/example/xorm/read/user_model.gen.go @@ -30,22 +30,22 @@ type User struct { // FindOneWhereParameter is a where parameter structure. type FindOneWhereParameter struct { - Id uint64 + IdEqual uint64 } // FindByNameWhereParameter is a where parameter structure. type FindByNameWhereParameter struct { - Name string + NameEqual string } // FindOnePartWhereParameter is a where parameter structure. type FindOnePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // FindByNamePartWhereParameter is a where parameter structure. type FindByNamePartWhereParameter struct { - Name string + NameEqual string } // FindAllCountResult is a find all count result. @@ -60,23 +60,23 @@ type FindAllPartCountResult struct { // FindOneByNameAndPasswordWhereParameter is a where parameter structure. type FindOneByNameAndPasswordWhereParameter struct { - Name string - Password string + NameEqual string + PasswordEqual string } // ListUserByNameAscWhereParameter is a where parameter structure. type ListUserByNameAscWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtWhereParameter is a where parameter structure. type ListUserByNameAscHavingCountTypeGtWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameAscHavingCountTypeGtHavingParameter is a having parameter structure. type ListUserByNameAscHavingCountTypeGtHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameAscHavingCountTypeGtResult is a list user by name asc having count type gt result. @@ -95,12 +95,12 @@ type ListUserByNameAscHavingCountTypeGtResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescWhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescHavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescResult is a list user by name desc having count type gt order by id desc result. @@ -119,12 +119,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescResult struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10LimitParameter is a limit parameter structure. @@ -148,12 +148,12 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result struct { // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter is a where parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10WhereParameter struct { - Id uint64 + IdGT uint64 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter is a having parameter structure. type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10HavingParameter struct { - TypeCount int8 + TypeCountGT int8 } // ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10LimitParameter is a limit parameter structure. @@ -178,22 +178,22 @@ type ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result struc // FindOneByNameLikeWhereParameter is a where parameter structure. type FindOneByNameLikeWhereParameter struct { - Name string + NameLike string } // FindAllByNameNotLikeWhereParameter is a where parameter structure. type FindAllByNameNotLikeWhereParameter struct { - Name string + NameNotLike string } // FindAllByIdInWhereParameter is a where parameter structure. type FindAllByIdInWhereParameter struct { - Id []uint64 + IdIn []uint64 } // FindAllByIdNotInWhereParameter is a where parameter structure. type FindAllByIdNotInWhereParameter struct { - Id []uint64 + IdNotIn []uint64 } // FindAllByIdBetweenWhereParameter is a where parameter structure. @@ -210,50 +210,50 @@ type FindAllByIdNotBetweenWhereParameter struct { // FindAllByIdGteWhereParameter is a where parameter structure. type FindAllByIdGteWhereParameter struct { - Id uint64 + IdGE uint64 } // FindAllByIdLteWhereParameter is a where parameter structure. type FindAllByIdLteWhereParameter struct { - Id uint64 + IdLE uint64 } // FindAllByIdNeqWhereParameter is a where parameter structure. type FindAllByIdNeqWhereParameter struct { - Id uint64 + IdNE uint64 } // FindAllByIdInOrNotInWhereParameter is a where parameter structure. type FindAllByIdInOrNotInWhereParameter struct { - Id uint64 - Id1 uint64 + IdIn []uint64 + IdNotIn []uint64 } // ComplexQueryWhereParameter is a where parameter structure. type ComplexQueryWhereParameter struct { - Id uint64 - Id1 uint64 - Id2 uint64 - Id3 uint64 - Id4 uint64 + IdGT uint64 + IdLT uint64 + IdNE uint64 + IdIn []uint64 + IdNotIn []uint64 IdBetweenStart uint64 IdBetweenEnd uint64 IdNotBetweenStart uint64 IdNotBetweenEnd uint64 - Id5 uint64 - Id6 uint64 - Id7 uint64 - Name string - Name1 string - Name2 string - Name3 string + IdGE uint64 + IdLE uint64 + IdNE1 uint64 + NameLike string + NameNotLike string + NameIn []string + NameNotIn []string NameBetweenStart string NameBetweenEnd string NameNotBetweenStart string NameNotBetweenEnd string - Name4 string - Name5 string - Name6 string + NameGE string + NameLE string + NameNE string } func (User) TableName() string { @@ -287,7 +287,7 @@ func (m *UserModel) FindOne(ctx context.Context, where FindOneWhereParameter) (* var result = new(User) var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id = ?`, where.Id) + session.Where(`id = ?`, where.IdEqual) session.Limit(1) _, err := session.Get(result) return result, err @@ -299,7 +299,7 @@ func (m *UserModel) FindByName(ctx context.Context, where FindByNameWhereParamet var result = new(User) var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`name = ?`, where.Name) + session.Where(`name = ?`, where.NameEqual) session.Limit(1) _, err := session.Get(result) return result, err @@ -311,7 +311,7 @@ func (m *UserModel) FindOnePart(ctx context.Context, where FindOnePartWhereParam var result = new(User) var session = m.engine.Context(ctx) session.Select(`id, name, nickname`) - session.Where(`id = ?`, where.Id) + session.Where(`id = ?`, where.IdEqual) session.Limit(1) _, err := session.Get(result) return result, err @@ -323,7 +323,7 @@ func (m *UserModel) FindByNamePart(ctx context.Context, where FindByNamePartWher var result = new(User) var session = m.engine.Context(ctx) session.Select(`id, name, nickname`) - session.Where(`name = ?`, where.Name) + session.Where(`name = ?`, where.NameEqual) session.Limit(1) _, err := session.Get(result) return result, err @@ -377,7 +377,7 @@ func (m *UserModel) FindOneByNameAndPassword(ctx context.Context, where FindOneB var result = new(User) var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`name = ? AND password = ?`, where.Name, where.Password) + session.Where(`name = ? AND password = ?`, where.NameEqual, where.PasswordEqual) session.Limit(1) _, err := session.Get(result) return result, err @@ -389,7 +389,7 @@ func (m *UserModel) ListUserByNameAsc(ctx context.Context, where ListUserByNameA var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.GroupBy(`name`) err := session.Find(&result) return result, err @@ -401,9 +401,9 @@ func (m *UserModel) ListUserByNameAscHavingCountTypeGt(ctx context.Context, wher var result []*ListUserByNameAscHavingCountTypeGtResult var session = m.engine.Context(ctx) session.Select(`*, count(type) AS typeCount`) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.GroupBy(`name`) - session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) err := session.Find(&result) return result, err } @@ -414,9 +414,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDesc(ctx context var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescResult var session = m.engine.Context(ctx) session.Select(`*, count(type) AS typeCount`) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.GroupBy(`name`) - session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) session.OrderBy(`id desc`) err := session.Find(&result) return result, err @@ -428,9 +428,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10(ctx var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Result var session = m.engine.Context(ctx) session.Select(`*, count(type) AS typeCount`) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.GroupBy(`name`) - session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) session.OrderBy(`id desc`) session.Limit(limit.Count) err := session.Find(&result) @@ -443,9 +443,9 @@ func (m *UserModel) ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offse var result []*ListUserByNameDescHavingCountTypeGtOrderByIdDescLimit10Offset10Result var session = m.engine.Context(ctx) session.Select(`*, count(type) AS typeCount`) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.GroupBy(`name`) - session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCount)) + session.Having(fmt.Sprintf(`typeCount > %v`, having.TypeCountGT)) session.OrderBy(`id desc`) session.Limit(limit.Count, limit.Offset) err := session.Find(&result) @@ -458,7 +458,7 @@ func (m *UserModel) FindOneByNameLike(ctx context.Context, where FindOneByNameLi var result = new(User) var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`name LIKE ?`, where.Name) + session.Where(`name LIKE ?`, where.NameLike) session.Limit(1) _, err := session.Get(result) return result, err @@ -470,7 +470,7 @@ func (m *UserModel) FindAllByNameNotLike(ctx context.Context, where FindAllByNam var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`name NOT LIKE ?`, where.Name) + session.Where(`name NOT LIKE ?`, where.NameNotLike) err := session.Find(&result) return result, err } @@ -481,7 +481,7 @@ func (m *UserModel) FindAllByIdIn(ctx context.Context, where FindAllByIdInWhereP var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id IN (?)`, where.Id) + session.Where(`id IN (?)`, where.IdIn) err := session.Find(&result) return result, err } @@ -492,7 +492,7 @@ func (m *UserModel) FindAllByIdNotIn(ctx context.Context, where FindAllByIdNotIn var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id NOT IN (?)`, where.Id) + session.Where(`id NOT IN (?)`, where.IdNotIn) err := session.Find(&result) return result, err } @@ -525,7 +525,7 @@ func (m *UserModel) FindAllByIdGte(ctx context.Context, where FindAllByIdGteWher var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id >= ?`, where.Id) + session.Where(`id >= ?`, where.IdGE) err := session.Find(&result) return result, err } @@ -536,7 +536,7 @@ func (m *UserModel) FindAllByIdLte(ctx context.Context, where FindAllByIdLteWher var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id <= ?`, where.Id) + session.Where(`id <= ?`, where.IdLE) err := session.Find(&result) return result, err } @@ -547,7 +547,7 @@ func (m *UserModel) FindAllByIdNeq(ctx context.Context, where FindAllByIdNeqWher var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id != ?`, where.Id) + session.Where(`id != ?`, where.IdNE) err := session.Find(&result) return result, err } @@ -558,7 +558,7 @@ func (m *UserModel) FindAllByIdInOrNotIn(ctx context.Context, where FindAllByIdI var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id IN (?) OR id NOT IN (?)`, where.Id, where.Id1) + session.Where(`id IN (?) OR id NOT IN (?)`, where.IdIn, where.IdNotIn) err := session.Find(&result) return result, err } @@ -569,7 +569,7 @@ func (m *UserModel) ComplexQuery(ctx context.Context, where ComplexQueryWherePar var result []*User var session = m.engine.Context(ctx) session.Select(`*`) - session.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.Id, where.Id1, where.Id2, where.Id3, where.Id4, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.Id5, where.Id6, where.Id7, where.Name, where.Name1, where.Name2, where.Name3, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.Name4, where.Name5, where.Name6) + session.Where(`id > ? AND id < ? AND id != ? AND id IN (?) AND id NOT IN (?) AND id BETWEEN ? AND ? AND id NOT BETWEEN ? AND ? AND id >= ? AND id <= ? AND id != ? AND name LIKE ? AND name NOT LIKE ? AND name IN (?) AND name NOT IN (?) AND name BETWEEN ? AND ? AND name NOT BETWEEN ? AND ? AND name >= ? AND name <= ? AND name != ?`, where.IdGT, where.IdLT, where.IdNE, where.IdIn, where.IdNotIn, where.IdBetweenStart, where.IdBetweenEnd, where.IdNotBetweenStart, where.IdNotBetweenEnd, where.IdGE, where.IdLE, where.IdNE1, where.NameLike, where.NameNotLike, where.NameIn, where.NameNotIn, where.NameBetweenStart, where.NameBetweenEnd, where.NameNotBetweenStart, where.NameNotBetweenEnd, where.NameGE, where.NameLE, where.NameNE) err := session.Find(&result) return result, err } diff --git a/example/xorm/update/user_model.gen.go b/example/xorm/update/user_model.gen.go index e4583df..e7f3c8e 100644 --- a/example/xorm/update/user_model.gen.go +++ b/example/xorm/update/user_model.gen.go @@ -30,27 +30,27 @@ type User struct { // UpdateWhereParameter is a where parameter structure. type UpdateWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdateByNameWhereParameter is a where parameter structure. type UpdateByNameWhereParameter struct { - Name string + NameEqual string } // UpdatePartWhereParameter is a where parameter structure. type UpdatePartWhereParameter struct { - Id uint64 + IdEqual uint64 } // UpdatePartByNameWhereParameter is a where parameter structure. type UpdatePartByNameWhereParameter struct { - Name string + NameEqual string } // UpdateNameLimitWhereParameter is a where parameter structure. type UpdateNameLimitWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitLimitParameter is a limit parameter structure. @@ -60,7 +60,7 @@ type UpdateNameLimitLimitParameter struct { // UpdateNameLimitOrderWhereParameter is a where parameter structure. type UpdateNameLimitOrderWhereParameter struct { - Id uint64 + IdGT uint64 } // UpdateNameLimitOrderLimitParameter is a limit parameter structure. @@ -97,7 +97,7 @@ func (m *UserModel) Insert(ctx context.Context, data ...*User) error { // update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`id = ?`, where.Id) + session.Where(`id = ?`, where.IdEqual) _, err := session.Update(map[string]interface{}{ "name": data.Name, "password": data.Password, @@ -115,7 +115,7 @@ func (m *UserModel) Update(ctx context.Context, data *User, where UpdateWherePar // update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateByNameWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`name = ?`, where.Name) + session.Where(`name = ?`, where.NameEqual) _, err := session.Update(map[string]interface{}{ "password": data.Password, "mobile": data.Mobile, @@ -132,7 +132,7 @@ func (m *UserModel) UpdateByName(ctx context.Context, data *User, where UpdateBy // update user set name = ?, nickname = ? where id = ?; func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePartWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`id = ?`, where.Id) + session.Where(`id = ?`, where.IdEqual) _, err := session.Update(map[string]interface{}{ "name": data.Name, "nickname": data.Nickname, @@ -144,7 +144,7 @@ func (m *UserModel) UpdatePart(ctx context.Context, data *User, where UpdatePart // update user set name = ?, nickname = ? where name = ?; func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where UpdatePartByNameWhereParameter) error { var session = m.engine.Context(ctx) - session.Where(`name = ?`, where.Name) + session.Where(`name = ?`, where.NameEqual) _, err := session.Update(map[string]interface{}{ "name": data.Name, "nickname": data.Nickname, @@ -156,7 +156,7 @@ func (m *UserModel) UpdatePartByName(ctx context.Context, data *User, where Upda // update user set name = ? where id > ? limit ?; func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where UpdateNameLimitWhereParameter, limit UpdateNameLimitLimitParameter) error { var session = m.engine.Context(ctx) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.Limit(limit.Count) _, err := session.Update(map[string]interface{}{ "name": data.Name, @@ -168,7 +168,7 @@ func (m *UserModel) UpdateNameLimit(ctx context.Context, data *User, where Updat // update user set name = ? where id > ? order by id desc limit ?; func (m *UserModel) UpdateNameLimitOrder(ctx context.Context, data *User, where UpdateNameLimitOrderWhereParameter, limit UpdateNameLimitOrderLimitParameter) error { var session = m.engine.Context(ctx) - session.Where(`id > ?`, where.Id) + session.Where(`id > ?`, where.IdGT) session.OrderBy(`id desc`) session.Limit(limit.Count) _, err := session.Update(map[string]interface{}{ diff --git a/internal/gen/sql/sql_gen.tpl b/internal/gen/sql/sql_gen.tpl index 11080b3..3f88e05 100644 --- a/internal/gen/sql/sql_gen.tpl +++ b/internal/gen/sql/sql_gen.tpl @@ -117,3 +117,21 @@ func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context, dat return err } {{end}} + +{{range $stmt := .DeleteStmt}} +// {{.FuncName}} is generated from sql: +// {{$stmt.SQL}} +func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}}) error { + b := builder.Delete() + b.From("`{{$.Table.Name}}`") + {{if $stmt.Where.IsValid}}b.Where(builder.Expr({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}})) + {{end}}{{if $stmt.OrderBy.IsValid}}b.OrderBy({{$stmt.OrderBy.SQL}}) + {{end}}{{if $stmt.Limit.IsValid}}b.Limit({{if $stmt.Limit.One}}1{{else}}{{$stmt.Limit.LimitParameter "limit"}}{{end}}{{if gt $stmt.Limit.Offset 0}}, {{$stmt.Limit.OffsetParameter "limit"}}{{end}}) + {{end}}query, args, err := b.ToSQL() + if err != nil { + return err + } + _, err = m.db.ExecContext(ctx, query, args...) + return err +} +{{end}} \ No newline at end of file diff --git a/internal/gen/xorm/xorm_gen.tpl b/internal/gen/xorm/xorm_gen.tpl index a85c040..99a52cb 100644 --- a/internal/gen/xorm/xorm_gen.tpl +++ b/internal/gen/xorm/xorm_gen.tpl @@ -101,7 +101,7 @@ func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context, dat func (m *{{UpperCamel $.Table.Name}}Model){{.FuncName}}(ctx context.Context{{if $stmt.Where.IsValid}}, where {{$stmt.Where.ParameterStructureName "Where"}}{{end}}{{if $stmt.Limit.Multiple}}, limit {{$stmt.Limit.ParameterStructureName}}{{end}}) error { var session = m.engine.Context(ctx) {{if $stmt.Where.IsValid}}session.Where({{$stmt.Where.SQL}}, {{$stmt.Where.Parameters "where"}}) - {{end}}{{if $stmt.OrderBy.IsValid}}session.Order({{$stmt.OrderBy.SQL}}) + {{end}}{{if $stmt.OrderBy.IsValid}}session.OrderBy({{$stmt.OrderBy.SQL}}) {{end}}{{if $stmt.Limit.IsValid}}session.Limit({{if $stmt.Limit.One}}1{{else}}{{$stmt.Limit.LimitParameter "limit"}}{{end}}{{if gt $stmt.Limit.Offset 0}}, {{$stmt.Limit.OffsetParameter "limit"}}{{end}}) {{end}}_, err := session.Delete(&{{UpperCamel $.Table.Name}}{}) return err diff --git a/internal/parser/parser_test.go b/internal/parser/parser_test.go index a81e569..79224a8 100644 --- a/internal/parser/parser_test.go +++ b/internal/parser/parser_test.go @@ -27,7 +27,7 @@ func TestParse(t *testing.T) { ctxOne := ctx[0] selectOne := ctxOne.SelectStmt[0] - fmt.Println(selectOne) + selectOne.Where.ParameterStructure("test") } func TestFrom(t *testing.T) { diff --git a/internal/parser/test.sql b/internal/parser/test.sql index 3d56072..d8401f2 100644 --- a/internal/parser/test.sql +++ b/internal/parser/test.sql @@ -15,27 +15,6 @@ CREATE TABLE `user` UNIQUE KEY `mobile_index` (`mobile`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '用户表' COLLATE=utf8mb4_general_ci; --- example1: update by primary key --- fn: Update -update user set name = ?, password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where id = ?; - --- example2: update by unique key --- fn: UpdateByName -update user set password = ?, mobile = ?, gender = ?, nickname = ?, type = ?, create_time = ?, update_time = ? where name = ?; - --- example3: update part columns by primary key --- fn: UpdatePart -update user set name = ?, nickname = ? where id = ?; - --- example4: update part columns by unique key --- fn: UpdatePartByName -update user set name = ?, nickname = ? where name = ?; - --- example5: update name limit ? --- fn: UpdateNameLimit -update user set name = ? where id > ? limit ?; - --- example6: update name limit ? order by id desc --- fn: UpdateNameLimitOrder -update user set name = ? where id > ? order by id desc limit ?; +-- fn: test +select * from user where id = ? and name in (?) limit 1; diff --git a/internal/spec/clause.go b/internal/spec/clause.go index 7b232cd..5594275 100644 --- a/internal/spec/clause.go +++ b/internal/spec/clause.go @@ -72,12 +72,7 @@ func (c *Clause) ParameterStructure(identifier string) (string, error) { writer.Write(`// %s is a %s parameter structure.`, c.ParameterStructureName(identifier), strcase.ToDelimited(identifier, ' ')) writer.Write(`type %s struct {`, c.ParameterStructureName(identifier)) for _, v := range parameters { - if c.OP == In || c.OP == NotIn { - writer.Write("%s []%s", v.Column, v.Type) - } else { - writer.Write("%s %s", v.Column, v.Type) - } - + writer.Write("%s %s", v.Column, v.Type) } writer.Write(`}`) @@ -169,7 +164,11 @@ func (c *Clause) marshal() (sql string, parameters parameter.Parameters, err err return "", nil, err } - ps.Add(p) + ps.Add(parameter.Parameter{ + Column: p.Column + OpName[c.OP], + Type: p.Type, + ThirdPkg: p.ThirdPkg, + }) case In, NotIn: sql = fmt.Sprintf("%s %s (?)", c.Column, Operator[c.OP]) p, err := c.ColumnInfo.DataType() @@ -177,7 +176,12 @@ func (c *Clause) marshal() (sql string, parameters parameter.Parameters, err err return "", nil, err } - ps.Add(p) + p.Type = fmt.Sprintf("[]%s", p.Type) + ps.Add(parameter.Parameter{ + Column: p.Column + OpName[c.OP], + Type: p.Type, + ThirdPkg: p.ThirdPkg, + }) case Between, NotBetween: sql = fmt.Sprintf("%s %s ? AND ?", c.Column, Operator[c.OP]) p, err := c.ColumnInfo.DataType()