go-gorm / sqlite Goto Github PK

Description

According to
https://www.sqlitetutorial.net/sqlite-autoincrement/
https://www.sqlite.org/autoinc.html
AUTOINCREMENT should be used in sqlite if one want's ID values not to be reused (i.e. after row deletion).

With table definition

ID uint64   `gorm:"column:id;not null;primaryKey;autoIncrement"`

GORM creates column without autoincrement:

CREATE TABLE `[...]` (`id` integer NOT NULL,[...],PRIMARY KEY (`id`));

but should add autoincrement also.

According to #126 one can create primary key + autoincrement with autoIncrement tag only but this looks like dirty hack and error

failed to create database schema: table "[...]" has more than one primary key

is thrown when trying to create table with

	IDH uint64 `gorm:"column:idh;not null;autoIncrement"` // Tried to create as PK+autoincrement.
	ID  uint64 `gorm:"column:id;not null"`                 // Tried to create as foreign key with ID name (makes sense in history table for us).

because GORM tries ID as PK by default (because of col name) and adds PK also to IDH (because of wrong handling of tags like above).

Expected behaviour

Column defined with gorm:"[...];primaryKey;autoIncrement" should be created as PK with autoincrement in sqlite db.

Description

CVE-2023-7104 has been reported for github.com/mattn/go-sqlite3 package, versions <1.14.18
as reported by Snyk
https://security.snyk.io/vuln/SNYK-GOLANG-GITHUBCOMMATTNGOSQLITE3-6139875

This dependency update has an already existing PR created by dependabot
#175

SQLite does not understand OFFSET without a LIMIT clause.

Look onto the following code:

[0.000ms] [rows:0] SELECT `queue_records`.`queue_id`,`queue_records`.`id`,`queue_records`.`journal_id` FROM `queue_records` WHERE (status = "put" AND queue_id = "a08a10b7-16ee-11ec-a4bb-482ae31ffe87") AND `queue
_records`.`deleted_at` IS NULL ORDER BY rec_time DESC OFFSET 1
?[1mSTEP?[0m: creating 2 queues
?[1mSTEP?[0m: creating queue 1
?[1mSTEP?[0m: creating queue 2
?[1mSTEP?[0m: inserting a bulk of queue records
?[1mSTEP?[0m: updating queue
+ Failure [0.004 seconds]
queues tests
qservice/mcstorage/sqlite/queues_test.go:19
  Queue Records
  qservice/mcstorage/sqlite/queues_test.go:338
    various delete funcs
    qservice/mcstorage/sqlite/queues_test.go:711
      QueueDeletePutRecords
      qservice/mcstorage/sqlite/queues_test.go:727
        checks if queue.Max = 1 [It]
        qservice/mcstorage/sqlite/queues_test.go:742

        Unexpected error:
            <sqlite3.Error>: {
                Code: 1,
                ExtendedCode: 1,
                SystemErrno: 0x0,
                err: "near \"OFFSET\": syntax error",
            }
            near "OFFSET": syntax error
        occurred

        qservice/mcstorage/sqlite/queues_test.go:751

Consider also looking the diagrams: https://www.sqlite.org/syntaxdiagrams.html

Workaround is to rewrite ClauseBuilders["LIMIT"] with valid implementation inside *gorm.DB entity.

Sample Reproduction Code

I havent handled errors in the below program. I have written it with objective of reproducing the issue.

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/postgres"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
)

// Defining Models
type Environment struct {
	gorm.Model
	Name        string `gorm:"not null;default:null"`
	Description string `gorm:"not null;default:null"`
}

type Node struct {
	gorm.Model
	Name          string       `gorm:"not null;default:null"`
	Description   string       `gorm:"not null;default:null"`
	Environment   *Environment `gorm:"foreignKey:EnvironmentID;not null;"`
	EnvironmentID uint
}

// Function handling migrations and connection to DB instance
func ConnectDatabase(dbType string, dbName string, dbHost string, dbPort uint64, dbSSLMode string, dbTimeZone string, dbUser string, dbPass string, moduleModels []interface{}) gorm.DB {

	var database *gorm.DB
	var err error
	if dbType == "sqlite3" {
		database, err = gorm.Open(sqlite.Open(fmt.Sprint(dbName+".db")), &gorm.Config{})
	} else if dbType == "postgres" {
		dsn := fmt.Sprintf("host=%v user=%v password=%v dbname=%v port=%v sslmode=%v TimeZone=%v",
			dbHost, dbUser, dbPass, dbName, dbPort, dbSSLMode, dbTimeZone)
		database, err = gorm.Open(postgres.Open(dsn), &gorm.Config{})
	}

	if err != nil {
		panic("Failed to connect to database!")
	}
	database.AutoMigrate(moduleModels...)

	DB := *database
	return DB
}

// Function to create node
func CreateNode(db *gorm.DB, nodeName string, nodeDescription string, environmentID uint) {
	node := Node{Name: nodeName, Description: nodeDescription, EnvironmentID: environmentID}
	(*db).Debug().Create(&node)
}

func main() {

	models := make([]interface{}, 0)
	models = append(models,
		Environment{},
		Node{})

        // Run a docker based postgresql container before executing the program
	postgresDB := ConnectDatabase("postgres", "test_db", "localhost", 5432, "disable", "Asia/Kolkata", "root", "root", models)
	log.Println("Testing with Postgresql !!")
	CreateNode(&postgresDB, "testpostgres", "testpostgres", 2)

	sqlite3DB := ConnectDatabase("sqlite3", "test_db", "", 0, "", "", "", "", models)
	log.Println("\n\nTesting with SQLite3 !!")
	CreateNode(&sqlite3DB, "testsqlite3", "testsqlite3", 2)
}

Output

test@lab:~/Garage/gorm-test $ go run main.go 
2021/12/31 12:04:56 Testing with Postgresql !!

2021/12/31 12:04:56 /home/test/Garage/gorm-test/main.go:52 ERROR: insert or update on table "nodes" violates foreign key constraint "fk_nodes_environment" (SQLSTATE 23503)
[1.363ms] [rows:0] INSERT INTO "nodes" ("created_at","updated_at","deleted_at","environment_id","name","description") VALUES ('2021-12-31 12:04:56.886','2021-12-31 12:04:56.886',NULL,2,'testpostgres','testpostgres') RETURNING "id","name","description"
2021/12/31 12:04:56 

Testing with SQLite3 !!

2021/12/31 12:04:56 /home/test/Garage/gorm-test/main.go:52
[8.966ms] [rows:1] INSERT INTO `nodes` (`created_at`,`updated_at`,`deleted_at`,`environment_id`,`name`,`description`) VALUES ("2021-12-31 12:04:56.923","2021-12-31 12:04:56.923",NULL,2,"testsqlite3","testsqlite3") RETURNING `id`,`name`,`description`

Expectation

SQLITE3 should raise foreign key violation error just as postgresql.

Description

SQLite3 doesnt seem to honor foreign key constraint when provided with non existing foreign primary key id through gorm. However , postgres honors it.
The above code tries to create a Node record with invalid Environment Id in sqlite3 and postgres database.

When the same is tried through the sqlite browser, it gives the error. Screenshot below :- :

The PRAGMA Foreign Keys is set to true. Screenshot from SQLITE3 browser below :-

Raising the issue here since there is not much documentation that I could find pertaining to the above issue.

I have referred articles related to sqlite3 foreign key handling in gorm. Most of them pertained to old version and there was'nt much pertaining to v2 gorm. Hence raising the issue here.

Description

Sqlite coumns that are neither NOT NULL or NULL are nullable by default. However, .Nullable() defaults to false and is only set true if the CREATE TABLE sql explicitly denotes the column as NULL

columnType := migrator.ColumnType{
    <snip>
    NullableValue:     sql.NullBool{Valid: true},    //<--- .Bool implicitly false, i.e. NOT NULL 
    <snip>
}

matchUpper := strings.ToUpper(matches[3])
if strings.Contains(matchUpper, " NOT NULL") {
	columnType.NullableValue = sql.NullBool{Bool: false, Valid: true}
} else if strings.Contains(matchUpper, " NULL") {
	columnType.NullableValue = sql.NullBool{Bool: true, Valid: true}
}
...

GORM Playground Link

go-gorm/playground#1

Description

.....
	if err := db.DB.AutoMigrate(&sys.RoleMenu{}); err != nil {
		log.Errorf("%s", err.Error())
	}
......

log

[0.455ms] [rows:0] CREATE TABLE `tb_sys_role_menu__temp` (
  "id" integer  PRIMARY KEY AUTOINCREMENT,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `created_by` integer NOT NULL DEFAULT 0,
  `updated_by` integer NOT NULL DEFAULT 0,
  `role_id` integer NOT NULL,
  "menu_id" bigint NOT NULL
)

pkg/mod/gorm.io/driver/[email protected]/migrator.go:411 near ")": syntax error
[0.011ms] [rows:0] INSERT INTO `tb_sys_role_menu__temp`() SELECT  FROM `tb_sys_role_menu`
2022-06-01T09:45:25.751+0800	ERROR	models/base.go:54	创建RoleMenu表异常:near ")": syntax error

github.com/mattn/go-sqlite3
cc1.exe: sorry, unimplemented: 64-bit mode not compiled in

func Open(dsn string) gorm.Dialector {
	return &Dialector{DSN: dsn}
}

Open is the factory func of sqlite driver to generate a gorm.Dialector, but not open a db connection in actual. Maybe named New is more reasonable.

The same as other drivers.

Description

Attempting to use a net.IP field in a gorm model causes a sqlite row value misued error on INSERT.

To Reproduce

I did not create a playground PR because this is so simple to reproduce:

package main

import (
	"net"
	"gorm.io/gorm"
	"gorm.io/driver/sqlite"
)

type Client struct {
	gorm.Model
	SourceIP net.IP
}

func main() {
	db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
	if err != nil {
		panic("Failed to connect to db")
	}

	db.AutoMigrate(&Client{})

	db.Create(&Client{
		SourceIP: net.ParseIP("192.168.1.1"),
	})
}

Output:

2021/03/21 00:33:35 gorm-sqlite-netIP/main.go:22 row value misused
[0.134ms] [rows:0] INSERT INTO `clients` (`created_at`,`updated_at`,`deleted_at`,`source_ip`) VALUES ("2021-03-21 00:33:35.378","2021-03-21 00:33:35.378",NULL,(0,0,0,0,0,0,0,0,0,0,255,255,192,168,1,1))

This happens in the sqlite3 CLI also:

sqlite> INSERT INTO `clients` (`created_at`,`updated_at`,`deleted_at`,`source_ip`) VALUES ("2021-03-21 00:33:35.378","2021-03-21 00:33:35.378",NULL,(0,0,0,0,0,0,0,0,0,0,255,255,192,168,1,1));
Error: row value misused

It seems that the nested parenthesis syntax for a BLOB is not correct.

Your Question

I wanna use this extension:

https://github.com/wangfenjin/simple/blob/master/examples/go/main.go

But It can not be loaded by my code:

	sql.Register("sqlite3_simple",
		&sqlite3.SQLiteDriver{
			Extensions: []string{
				"lib/libsimple",
			},
		})

       db, err = gorm.Open(sqlite.Open(dsn), &gorm.Config{})

output:

query error: no such function: simple_query

Describe the feature

It would be nice to have an encrypted database like this lib:

https://github.com/jackfr0st13/gorm-sqlite-cipher

Thanks,

Motivation

...

Related Issues

glebarez#55

GORM Playground Link

I did not provide a playground link, because I was able to pinpoint the issue and will provide a PR to fix it.
This report rather serves as a reference and documentation.

tl;dr

AutoMigrate with gorm did not work for me on my sqlite db. It tried to create a table that already existed. The reason were two regexes in the sqlite connector that did not cover a specific edge case.

Description

I recently upgraded gorm to the latest version in my project and found that it displayed an error message to me:

SQL logic error: table "users" already exists (1)

It tried to create a new table which already existed.

This was weird because of two reasons. First, it worked with an older version of gorm and second, even if the table exists, gorm should just update its fields by creating a temporary table and copying over the contents.

Narrowing down the issue

So I did some investigations and found out that the actual breaking commit was go-gorm/gorm@93986de. Every version released before this commit worked, every version after did not. The problematic lines in the commit were these ones:
https://github.com/go-gorm/gorm/blob/93986de8e43bc9af6864621c9a4855f0f860cde2/migrator/migrator.go#L456-L459

When using AutoMigrate with my table, line 458 got executed. That shows that gorm did not manage to read the default value from the table schema and thought it needs to create a default value for a column in my table. But instead the database already had a default value set.

I assumed that it was due to a parsing issue, so I checked the schema and the parsing code. The schema was pretty weirdly formatted but valid sql.

CREATE TABLE "users"
(
    id         integer
        primary key
        unique,
    created_at datetime,
    updated_at datetime,
    username   text,
    first_name text,
    last_name  text,
    lang_code  text
, `dark_mode` numeric DEFAULT true);

Parsing issue nr. 1

There is indeed a parsing issue right here:

tableRegex = (?is)(CREATE TABLE [`|\"|'|\t]?[\\w\\d-]+[`|\"|'|\t]?)(?: \\((.*)\\))?

The tableRegex wasn't able to parse the above sql statement, because of the line break right after "users". That's the reason why the returned sqlDDL did not contain any fields/columns. The part of the tableRegex (?: \\((.*)\\))? expected a space (0x20) after the quotes around the table name. In my case the SQL did not contain a space but a newline.

Side note: The regex isn't very good overall, because it also matches things like CREATE TABLE |users| because | is being used in the character set for "separators".

That regex issue leads to the columnTypes return value being filled with only the rawColumnTypes (SELECTed from the actual table) and hence contain no information about e.g. autoincrement, default values, etc.
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L116-L118

Then the MigrateColumn code gets executed and for any column that has additional properties (like uniqueness, nullability, default values) we will at some point set the alterColumn variable to True. In my case the dark_mode column caused the issue due to a default value being used.
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L503-L506

Since alterColumn was set to true, gorm now executed the AlterColumn method of the sqlite module, where we finally call m.recreateTable().
https://github.com/go-gorm/gorm/blob/b444011d094db7444f87f442c33860365f55770a/migrator/migrator.go#L524-L526

AlterColumn Method:

Parsing issue nr. 2

In the recreateTable method, we want to create a temporary table name (e.g. users__temp) in order to copy over all existing fields from the old table to a newly created one, to migrate to a new db schema.

But again we have some regex issue here: " ('|`|\"| )" + table + "('|`|\"| ) " - at least one space before and after the table's name is expected. This does not work when there is a newline right after the table name (which is valid sqlite syntax). That finally lead to the case where the actual createSQL was CREATE TABLE users [...] instead of CREATE TABLE users__temp.

I'll create a PR to fix these two regex parsing issues. While debugging I also came across yet another issue, so stay tuned for another (much shorter) report :)

Best regards,
Rico

Describe the feature

github.com/mattn/go-sqlite3 this lib using c, cgo is not support CROSS_COMPILE

Motivation

Here is the explanation of the mechanism - https://www.sqlite.org/fts5.html

But somehow the FTS5 module should be installed.
How to do it for the gorm SQLite driver?

Describe the feature

Support CGO-free SQLite driver: https://pkg.go.dev/modernc.org/sqlite

Motivation

CGO-free

Description

Sample code below

type Message struct {
   ID int
}

I need set the initial auto increment value of message's id, and the sqlite's doc described the sqlite_sequence table. But when i try to manipulate it, i got an error, the table doesnt exists.

So i read the migrator's code, the DataTypeOf() method checks if the field is auto increment and not primary key field, migrator will generate proper statement to support auto increment.

But the ID field defaults handled as primary key, the problem seems can't fixed.

Gorm docs Conventions section say, we can use tag primaryKey to set other field as primary key, but migrator reports an error table device has more than one primary key.

How to fix it? Thanks for reply.

My Question

I try to run the demo code. After go build, I got this error!

../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:100:22: cannot convert 0 (untyped int constant) to *int
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:101:24: cannot convert 0 (untyped int constant) to *int
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:102:21: cannot use -1 (untyped int constant) as *int value in assignment
../../../../go/pkg/mod/gorm.io/driver/[email protected]/sqlite.go:104:50: cannot use limit.Limit (variable of type *int) as type int in argument to strconv.Itoa

My Platform

OS: macos 12.5.1 / arm64
Pkg Vesion: gorm.io/driver/[email protected]

Please check https://github.com/go-gorm/sqlite/blob/master/ddlmod.go#L29 at line 120. If a column is defined without "NOT NULL", it should be nullable by default. So line 120 needs to be modified as:
NullableValue: sql.NullBool{Bool: true, Valid: true}

Describe the feature

It would be awesome if this package could be independent of go-sqlite3 and instead, use go-sqlite3 (sqlite3) when CGO_ENABLED=1 and use modernc.org/sqlite when not.

Motivation

This allows the user to choose between either go-sqlite3 (sqlite3) or modernc.org/sqlite (sqlite) pure-go implementation. go-sqlite3 is excellent, but CGO is a lot to deal with.

The https://github.com/glebarez/sqlite driver is behind on many commits, it would be much simpler to maintain one codebase for multiple drivers rather than two, especially since they are almost the same.

Related Issues

#77 #72 #93 #35

Describe the feature

JSON Operations

• JSON_EXTRACT
• JSON_UNQUOTE
• JSON_KEYS
• ...

Motivation

Related Issues

sqlite file cannot be closed

sqlDB, _ = db.DB()
fmt.Println(sqlDB.Ping())
fmt.Println(sqlDB.Close())
fmt.Println(sqlDB.Ping())

output:

<nil>
<nil>
sql: database is closed

but when I use the lsof -p command, I can still see that many database files are opened

Sorry for skipping the playground PR, but I think the below snippet should explain the issue well enough.

Description

It took me a couple of hours to figure out the following issue. When parsing the DDL as part of AutoMigrate, an error will occur if the original SQL statement wasn't fully uppercase.

package main

import (
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

type Foo struct {
	ID     uint64 `gorm:"primary_key"`
	Field1 string
	Field2 string
}

func main() {
	db, err := gorm.Open(sqlite.Open("gorm_db"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		panic(err)
	}

	if err := db.AutoMigrate(&Foo{}); err != nil {
		panic(err)
	}

	// note "on" vs "ON" here
	if err := db.Exec("CREATE INDEX idx_field1_field2 on foos (field1, field2)").Error; err != nil {
		panic(err)
	}

	if err := db.AutoMigrate(&Foo{}); err != nil {
		panic(err)
	}
}

Here's the output:

2022/04/03 17:07:23 /home/ferdinand/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:33
[0.025ms] [rows:-] SELECT count(*) FROM sqlite_master WHERE type='table' AND name="foos"

2022/04/03 17:07:23 /home/ferdinand/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:106
[0.062ms] [rows:2] SELECT sql FROM sqlite_master WHERE type IN ("table","index") AND tbl_name = "foos" AND sql IS NOT NULL order by type = "table" desc

2022/04/03 17:07:23 /tmp/gorm-bug/main.go:23 duplicate column name: id
[0.012ms] [rows:0] ALTER TABLE `foos` ADD `id` integer
panic: duplicate column name: id

goroutine 1 [running]:
main.main()
        /tmp/gorm-bug/main.go:24 +0x23a

Process finished with the exit code 2

So when manually running a DDL statement that is valid, yet not correctly formatted, auto migrations for that table will break forever until you explicitly fix it.

Would be very helpful if the regexes were case-insensitive and only check for correct syntax instead.

go.mod

module gorm-bug

go 1.18

require (
	gorm.io/driver/sqlite v1.3.1
	gorm.io/gorm v1.23.4
)

require (
	github.com/jinzhu/inflection v1.0.0 // indirect
	github.com/jinzhu/now v1.1.4 // indirect
	github.com/mattn/go-sqlite3 v2.0.3+incompatible // indirect
)

r如题，请问大家之前是否遇到过？ 当文件一直增大的情况下 执行程序占用内存也会变大

SqLite Error during build.
Tested on docker alpine 3.17, RHEL Linux 8 and Suse Linux 12.5
Cannot replicate in playground as it fails in build.

[builder 7/7] RUN go build -o main:
#0 11.77 # gorm.io/driver/sqlite
#0 11.77 /go/pkg/mod/gorm.io/driver/[email protected]/error_translator.go:9:35: undefined: sqlite3.ErrNoExtended
#0 11.77 /go/pkg/mod/gorm.io/driver/[email protected]/error_translator.go:14:36: undefined: sqlite3.Error

The following code yields a different result when used with the mysql instead of the sqlite driver.

Instead of using ON CONFLICT DO NOTHING RETURNING `id`,`id`;, this driver should do a ON CONFLICT DO UPDATE SET `id`=`id` RETURNING `id`,`id`; just as the mysql driver does.

Output when using sqlite:

2023/01/25 00:34:39 User #0: Peter1, country: New Zealand
2023/01/25 00:34:39 User #1: Peter2, country: [EMPTY]

(Correct) output when using mysql:

2023/01/25 00:34:39 User #0: Peter1, country: New Zealand
2023/01/25 00:34:39 User #1: Peter2, country: Germany

package main

import (
	"fmt"
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/clause"
)

type User struct {
	gorm.Model

	ID uint `gorm:"column:id; primaryKey; unique; not null"`

	Name string `gorm:"column:name; unique; not null"`

	Country   *Country
	CountryID *uint `gorm:"column:country_id"`
}

type Country struct {
	gorm.Model

	ID uint `gorm:"column:id; primaryKey; unique; not null"`

	Name string `gorm:"column:name; unique; not null"`
}

func run() error {
	const (
		// dbFile = "db.sqlite"
		dbFile = "file:memdb?mode=memory"
	)

	db, err := gorm.Open(
		sqlite.Open(dbFile),
		&gorm.Config{},
	)
	if err != nil {
		return fmt.Errorf("failed to open GORM: %w", err)
	}

	if err := db.Debug().AutoMigrate(
		&User{},
		&Country{},
	); err != nil {
		return fmt.Errorf("failed to auto migrate: %w", err)
	}

	// Create countries
	{
		tx := db.Debug().
			Create(&Country{Name: "Germany"})
		if err := tx.Error; err != nil {
			return fmt.Errorf("failed to create country: %w", err)
		}
	}

	// Create users
	{
		tx := db.Debug().
			Create(&User{Name: "Peter1", Country: &Country{Name: "New Zealand"}})
		if err := tx.Error; err != nil {
			return fmt.Errorf("failed to create user: %w", err)
		}
	}
	{
		tx := db.Debug().
			Create(&User{Name: "Peter2", Country: &Country{Name: "Germany"}})
		if err := tx.Error; err != nil {
			return fmt.Errorf("failed to create user: %w", err)
		}
	}

	// Retrieve users
	{
		var ms []*User
		tx := db.Debug().Preload(clause.Associations).
			Find(&ms)
		if err := tx.Error; err != nil {
			return fmt.Errorf("failed to find: %w", err)
		}

		for i, m := range ms {
			country := "[EMPTY]"
			if m.Country != nil {
				country = m.Country.Name
			}
			log.Printf("User #%d: %s, country: %s\n", i, m.Name, country)
		}
	}

	return nil
}

func main() {
	if err := run(); err != nil {
		log.Fatal(err)
	}
}

func (dialector Dialector) DataTypeOf(field *schema.Field) string { switch field.DataType { case schema.Bool: return "numeric" case schema.Int, schema.Uint: if field.AutoIncrement && !field.PrimaryKey { // https://www.sqlite.org/autoinc.html return "integer PRIMARY KEY AUTOINCREMENT" } else { return "integer" }

why not field.AutoIncrement && field.PrimaryKey => integer PRIMARY KEY AUTOINCREMENT

Please bump this dependency version

This fixes mattn/go-sqlite3#1164 - can't build on Alpine 3.19 (?)

For anyone else hitting this, a temporary workaround is to set CGO_CFLAGS="-D_LARGEFILE64_SOURCE"

Description

With the version 1.4.1 I have the following error:

C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:101:23: invalid operation: limit.Limit != nil (mismatched types int and untyped nil)
C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:101:31: invalid operation: cannot indirect limit.Limit (variable of type int)
C:\Users\***\go\pkg\mod\gorm.io\driver\[email protected]\sqlite.go:102:13: invalid operation: cannot indirect limit.Limit (variable of type int)

Probably due to this commit: ea59bcf

Revert to1.3.6 temporary fix the problem.

GORM Playground Link

go-gorm/playground#660

Description

I'm using the GORM ORM framework for Golang and have encountered an issue with composite unique indexes. When using the following structure that includes a composite unique index, I noticed that the recreateTable process (which includes creating a temporary table, migrating data, deleting the old table, and renaming the table) is executed every time AutoMigrate is called, even if the model has not changed.

type Gateway struct {
  IP string    `gorm:"index:uniq_vip,unique"`
  UIN string `gorm:"index:uniq_vip,unique"`
}

However, there is no issue when using a single-field unique index like the following model:

type Gateway struct {
  IP string    `gorm:"index:uniq_vip,unique"`
  UIN string 
}

After investigating, I found that the alterColumn = true is set in the code at https://github.com/go-gorm/gorm/blob/v1.25.5/migrator/migrator.go#L500, because field.Unique is false while unique is true.

	// check unique
	if unique, ok := columnType.Unique(); ok && unique != field.Unique {
		// not primary key
		if !field.PrimaryKey {
			alterColumn = true
		}
	}

Further investigation revealed that the problematic code for the sqlite-driver is at https://github.com/go-gorm/sqlite/blob/v1.5.4/ddlmod.go#L165:

for _, column := range getAllColumns(matches[1]) {
				for idx, c := range result.columns {
					if c.NameValue.String == column {
						c.UniqueValue = sql.NullBool{Bool: strings.ToUpper(strings.Fields(str)[1]) == "UNIQUE", Valid: true}
						result.columns[idx] = c
					}
				}
			}

Since it is a composite unique index, getAllColumns(matches[1]) returns a length of 2, containing the column names IP and UIN. Neither of these individual fields is unique, but they form a composite unique index. The condition strings.Fields(str)[1]) == "UNIQUE" is not strict enough, causing each field to be set as unique.

I expect the check should be something like strings.Fields(str)[1]) == "UNIQUE" && len(getAllColumns(matches[1])) == 1, so that the unique attribute is only set for fields when there is only one field in the unique index, rather than setting unique for each field in the composite unique index. After modifying the condition and testing, the behavior is as expected.

ld: warning: -no_pie is deprecated when targeting new OS versions
ld: warning: non-standard -pagezero_size is deprecated when targeting macOS 13.0 or later

gorm.io/driver/sqlite v1.4.4
Mac OS 13.3.1

GORM Playground Link

go-gorm/playground#535

Description

As demonstrated in the playground, dropping a column fails when foreign keys are enabled

1. gorm.io/driver/sqlite v1.5.3 normal operation
2. gorm.io/driver/sqlite v1.5.4 Abnormal operation: Create Table Exception
3. View sqlite/v1.5.4 branch and run project replication issues
4. An error PR: xuxiaowei-com-cn/sqlite-err#1

Description

HasColumn 的判断规则是:

"SELECT count(*) FROM sqlite_master WHERE type = ? AND tbl_name = ? AND (sql LIKE ? OR sql LIKE ? OR sql LIKE ?)",
"table", stmt.Table, `%"`+name+`" %`, `%`+name+` %`, "%`"+name+"`%",

但 Sqlite 的工具创建或修改表结构后的 sqlite_master.sql 格式并不一致, 造成 hasColumn 无法正确判断.
我遇到其中两个例外情况:

1. column 后使用 tab 而非 space 的.

CREATE TABLE posts (
  id	integer PRIMARY KEY, 
  created_at	datetime);

这个是在 mac 上的一个 sqlite gui 工具修改后的结果.

2. 使用 [] 作为分隔符.

CREATE TABLE [posts](
  [id] integer PRIMARY KEY, 
  [created_at] datetime);

这是 win 上 Sqlite Expert 修改后的结果.

最近在使用该模块，发现一个问题：

1. 在指定sqlite的指定有效时间后（sqliteDb.SetConnMaxLifetime(1 * time.Minute)），发现超过指定的时间再次请求数据里的连接是无数据的，这个设计有点不理解，因为内存模式不应该是数据连接池共享的吗，那么新的连接应该也可以获得数据才对；
2. 针对上面的问题如果不设置有效期，我设置了连接池的活跃连接数大小如100，那么从设计角度来讲内存表模式，而且不存在网络异常问题，是不是这100个连接会一直长连接，然后 这100个连接都对应自己的缓存，没有共享内存数据，那么这个连接池的意义没有了。
   上面的问题很困惑，是否可以在未来版本支持。还请帮忙解答一下。

Describe the feature

The feature which I am happy to do a PR for is to remove the use of the existing CGO based SQLite implementation with a pure Golang based implementation here.

• https://github.com/zombiezen/go-sqlite

Motivation

The CGO libraries are a HUGE pain with Windows machines and even other machines without the GCC binaries and specific settings. It does not compile well in many scenarios. This conversion appears to be solid and is a direct conversion of the C libraries of the original implementation.

Related Issues

Describe the feature

Add a way to override the return value of Dialector.DefaultValueOf()

Motivation

For our project we use sqlite driver for the tests.
When we insert a default value with gorm:default:null it inserts DEFAULT instead of NULL. The field is a primary key and has AutoIncrement disabled because we are using snowflake.
We couldn't find any solution except by modifying the driver code which is not satisfying

GORM Playground Link

go-gorm/playground#488

Description

As we can see in the playground，AutoMigrate() fails with error invalid DDL if a table with - in name exists.

GORM Playground Link

go-gorm/playground#407

Description

SQLite driver does not return foreign key violation error since introducing support for RETURNING clause.
The problem is due to specific behavior of SQLite and underlying driver, as described here: mattn/go-sqlite3#986 (comment)

GORM Playground Link

type Product struct {
    Price float64 `gorm:"type:decimal(10,2);not null"`
}

...

DB.AutoMigrate(Product{})

Description

The database is initialized normally for the first time, but it crashes after restarting the application, returning the error invaild DDL, unbalanced brackets

According to my repeated debugging, the problem appears in migrate.go
At line 89, it would replace decimal (10,2) to ?, 2)