Skip to content

Commit

Permalink
fix: query helper
Browse files Browse the repository at this point in the history
  • Loading branch information
@Seann-Moser
Seann-Moser committed Nov 12, 2024
1 parent 25c40ff commit 4ef7cb0
Show file tree
Hide file tree
Showing 10 changed files with 509 additions and 292 deletions.
18 changes: 12 additions & 6 deletions cache_test.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -20,28 +20,34 @@ func TestCacheTest(t *testing.T) {
go ctx_cache.GlobalCacheMonitor.Start(ctx)
table, err := NewTable[AccountUserRole]("test", "")
if err != nil {
t.Fatalf(err.Error())
t.Fatal(err.Error())
}

q := QueryTable[AccountUserRole](table)
//if !isSuperAdmin {
q.Where(q.Column("user_id"), "=", "AND", 0, "")
q.UseCache()
q.SetName("rbac-accounts-for-user")
q.Run(ctx, nil)

_, err = q.Run(ctx, nil)
if err != nil {
t.Error(err.Error())
}
aur := &AccountUserRole{
AccountID: "",
UserID: "",
RoleID: "",
}
_, err = table.Insert(ctx, nil, *aur)

if err != nil {
t.Error(err.Error())
}
qt := QueryTable[AccountUserRole](table)
//if !isSuperAdmin {
qt.Where(q.Column("user_id"), "=", "AND", 0, "")
qt.UseCache()
qt.SetName("rbac-accounts-for-user")
qt.Run(ctx, nil)

_, err = qt.Run(ctx, nil)
if err != nil {
t.Error(err.Error())
}
}
74 changes: 43 additions & 31 deletions column.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -48,6 +48,49 @@ type Column struct {
Decrypt bool `json:"decrypt"`
}

// GetDefinition Adjust the Column methods accordingly
func (col *Column) GetDefinition() string {
// Properly quote the column name
name := fmt.Sprintf("`%s`", col.Name)
// Build the type and default value
definition := fmt.Sprintf("%s %s", name, col.Type)
if !col.Null {
definition += " NOT NULL"
}
switch col.Default {
case "created_timestamp":
definition += " DEFAULT NOW()"
case "updated_timestamp":
definition += " DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
default:
if col.Default != "" {
// Ensure the default value is compatible with MySQL 8
definition += fmt.Sprintf(" DEFAULT %s", col.Default)
}
}

if col.AutoGenerateID {
definition += " AUTO_INCREMENT"
}
return definition
}

func (col *Column) HasFK() bool {
if len(col.ForeignKey) > 0 && len(col.ForeignTable) > 0 {
return col.ForeignKey != ""
}
return false
}

func (col *Column) GetFK() (string, error) {
// Properly quote identifiers
constraintName := fmt.Sprintf("`FK_%s_%s`", col.Table, col.Name)
columnName := fmt.Sprintf("`%s`", col.Name)
foreignTable := fmt.Sprintf("`%s`.`%s`", col.ForeignSchema, col.ForeignTable)
foreignColumn := fmt.Sprintf("`%s`", col.ForeignKey)
return fmt.Sprintf("\n\tCONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)", constraintName, columnName, foreignTable, foreignColumn), nil
}

func (c Column) Wrap(wrap string) Column {
c.Wrapper = wrap
return c
Expand All @@ -68,30 +111,6 @@ func (c Column) As(as string) Column {
return c
}

func (c *Column) GetDefinition() string {
elementString := fmt.Sprintf("\n\t%s %s", c.Name, c.Type)
if !c.Null {
elementString += " NOT NULL"
}
switch c.Default {
case "created_timestamp":
elementString += " DEFAULT NOW()"
case "updated_timestamp":
elementString += " DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"
case "":
if c.Null {
elementString += " DEFAULT NULL"
}
default:
elementString += fmt.Sprintf(" DEFAULT %s", c.Default)
}
return elementString
}

func (c *Column) HasFK() bool {
return len(c.ForeignKey) > 0 && len(c.ForeignTable) > 0
}

func (c *Column) FullName(groupBy bool, inSelect bool) string {
name := fmt.Sprintf("%s.%s", c.Table, c.Name)
if c.Wrapper != "" && inSelect {
Expand Down Expand Up @@ -134,13 +153,6 @@ func (c *Column) GetFKReference() string {
return reference
}

func (c *Column) GetFK() string {
if c.HasFK() {
return fmt.Sprintf("\n\tFOREIGN KEY (%s) REFERENCES %s ON DELETE CASCADE on update cascade", c.Name, c.GetFKReference())
}
return ""
}

func (c *Column) GetUpdateStmt(add bool) string {
if add {
return fmt.Sprintf("ADD %s", c.GetDefinition())
Expand Down
149 changes: 149 additions & 0 deletions create_table_test.go
View file
Original file line number Diff line number Diff line change
@@ -0,0 +1,149 @@
package QueryHelper

import (
"testing"

"github.com/stretchr/testify/assert"
)

// Assume MissingPrimaryKeyErr is defined somewhere
func TestBuildCreateTableQueries(t *testing.T) {
// Test cases
tests := []struct {
name string
dataset string
table string
columns map[string]Column
expectedSchemaSQL string
expectedCreateTableSQL string
expectError bool
errorMessage string
}{
{
name: "Create table with single primary key",
dataset: "test_schema",
table: "test_table",
columns: map[string]Column{
"id": {
Name: "id",
Type: "INT",
Primary: true,
Null: false,
},
"name": {
Name: "name",
Type: "VARCHAR(255)",
Null: false,
},
},
expectedSchemaSQL: "CREATE SCHEMA IF NOT EXISTS `test_schema`",
expectedCreateTableSQL: "CREATE TABLE IF NOT EXISTS `test_schema`.`test_table` (`id` INT NOT NULL,`name` VARCHAR(255) NOT NULL,\n\tPRIMARY KEY(`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
expectError: false,
},
{
name: "Create table with composite primary key",
dataset: "test_schema",
table: "test_table",
columns: map[string]Column{
"id": {
Name: "id",
Type: "INT",
Primary: true,
Null: false,
},
"secondary_id": {
Name: "secondary_id",
Type: "INT",
Primary: true,
Null: false,
},
"name": {
Name: "name",
Type: "VARCHAR(255)",
Null: false,
},
},
expectedSchemaSQL: "CREATE SCHEMA IF NOT EXISTS `test_schema`",
expectedCreateTableSQL: "CREATE TABLE IF NOT EXISTS `test_schema`.`test_table` (`id` INT NOT NULL,`secondary_id` INT NOT NULL,`name` VARCHAR(255) NOT NULL,\n\tCONSTRAINT `PK_test_schema_test_table` PRIMARY KEY (`id`,`secondary_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
expectError: false,
},
{
name: "Create table with foreign key",
dataset: "test_schema",
table: "orders",
columns: map[string]Column{
"order_id": {
Name: "order_id",
Type: "INT",
Primary: true,
Null: false,
},
"customer_id": {
Name: "customer_id",
Type: "INT",
Table: "orders",
Null: false,
ForeignKey: "id",
ForeignTable: "customers",
ForeignSchema: "test_schema",
},
},
expectedSchemaSQL: "CREATE SCHEMA IF NOT EXISTS `test_schema`",
expectedCreateTableSQL: "CREATE TABLE IF NOT EXISTS `test_schema`.`orders` (`order_id` INT NOT NULL,`customer_id` INT NOT NULL,\n\tPRIMARY KEY(`order_id`),\n\tCONSTRAINT `FK_orders_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `test_schema`.`customers` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
expectError: false,
},
{
name: "Missing primary key",
dataset: "test_schema",
table: "test_table",
columns: map[string]Column{
"name": {
Name: "name",
Type: "VARCHAR(255)",
Null: false,
},
},
expectError: true,
errorMessage: MissingPrimaryKeyErr.Error(),
},
{
name: "Column with default value",
dataset: "test_schema",
table: "products",
columns: map[string]Column{
"product_id": {
Name: "product_id",
Type: "INT",
Primary: true,
Null: false,
},
"price": {
Name: "price",
Type: "DECIMAL(10,2)",
Null: false,
Default: "0.00",
},
},
expectedSchemaSQL: "CREATE SCHEMA IF NOT EXISTS `test_schema`",
expectedCreateTableSQL: "CREATE TABLE IF NOT EXISTS `test_schema`.`products` (`product_id` INT NOT NULL,`price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,\n\tPRIMARY KEY(`product_id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
expectError: false,
},
}

for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
// Call the function under test
s := &SqlDB{}
schemaSQL, createTableSQL, err := s.BuildCreateTableQueries(tt.dataset, tt.table, tt.columns)

if tt.expectError {
assert.Error(t, err)
assert.EqualError(t, err, tt.errorMessage)
} else {
assert.NoError(t, err)
assert.Equal(t, tt.expectedSchemaSQL, schemaSQL)
assert.Equal(t, tt.expectedCreateTableSQL, createTableSQL)
}
})
}
}
4 changes: 2 additions & 2 deletions ctx.go
View file
Original file line number Diff line number Diff line change
Expand Up @@ -119,15 +119,15 @@ func DeleteCtx[T any](ctx context.Context, data *T, suffix ...string) error {
return table.Delete(ctx, nil, *data)
}

func UpdateCtx[T any](ctx context.Context, old, data *T, allowEmpty bool, suffix ...string) error {
func UpdateCtx[T any](ctx context.Context, data *T, suffix ...string) error {
table, err := GetTableCtx[T](ctx, suffix...)
if err != nil {
return err
}
if data == nil {
return fmt.Errorf("no data provided")
}
return table.Update(ctx, nil, *old, *data, allowEmpty)
return table.Update(ctx, nil, *data)
}

func ListCtx[T any](ctx context.Context, stmt ...*WhereStmt) ([]*T, error) {
Expand Down
3 changes: 3 additions & 0 deletions go.mod
View file
Original file line number Diff line number Diff line change
Expand Up @@ -9,6 +9,7 @@ require (
github.com/jmoiron/sqlx v1.4.0
github.com/spf13/pflag v1.0.5
github.com/spf13/viper v1.19.0
github.com/stretchr/testify v1.9.0
github.com/xwb1989/sqlparser v0.0.0-20180606152119-120387863bf2
go.opencensus.io v0.24.0
go.opentelemetry.io/otel v1.30.0
Expand All @@ -17,6 +18,7 @@ require (

require (
github.com/cespare/xxhash/v2 v2.3.0 // indirect
github.com/davecgh/go-spew v1.1.2-0.20180830191138-d8f796af33cc // indirect
github.com/dgryski/go-rendezvous v0.0.0-20200823014737-9f7001d12a5f // indirect
github.com/fsnotify/fsnotify v1.7.0 // indirect
github.com/go-logr/logr v1.4.2 // indirect
Expand All @@ -34,6 +36,7 @@ require (
github.com/orijtech/gomemcache v0.0.1 // indirect
github.com/patrickmn/go-cache v2.1.0+incompatible // indirect
github.com/pelletier/go-toml/v2 v2.2.3 // indirect
github.com/pmezard/go-difflib v1.0.1-0.20181226105442-5d4384ee4fb2 // indirect
github.com/sagikazarmark/locafero v0.6.0 // indirect
github.com/sagikazarmark/slog-shim v0.1.0 // indirect
github.com/sourcegraph/conc v0.3.0 // indirect
Expand Down
Loading

0 comments on commit 4ef7cb0

Please sign in to comment.