Hi all When I have a database connection with a temp table of the same name as a table in main, I am getting what I feel is unexpected behavior from the session extension.
Most succinctly, I start a session on a connection on the main db with a table with an INTEGER PRIMARY KEY, attach it to all tables by passing NULL, make a change such as a single INSERT in aforementioned table, and then record a changeset. Then I invert this changeset, and apply that inverse to the same connection, to reverse the changes just made. Normally this works flawlessly. However, if a table by the same name exists in the temp database on that connection, I run into odd behavrior. If the temp table has different rows than the main table, a very odd changeset conflict occurs. The conflict type is SQLITE_CHANGESET_DATA but when I examine the changeset iter the values for the columns, they match the conflicting values. The inverse of an INSERT is a DELETE so this conflict type indicates that one of the non-primary key column values doesn't match, but in every examination of the database and the changeset I have performed they do match. The primary key exists and matches as well. If the temp table has the same rows at the main table (what the changeset expects), the changeset_apply does not raise a conflict, but it takes no affect on the main table. This can all be avoided by being explicit about the database when calling sqlite3session_attach. For example passing "*" or "main.*" or "main.<table>" avoids the issue. But it occurs if NULL is passed. This is not obvious and arguably not expected given that the documentation about the Session extension makes it seem like it should be already scoped to a single database on a connection. After all, sqlite3session_create accepts the database as an argument. Also, sqlite3_changeset_apply appears to be specific to the "main" database. So it shouldn't care about whats in the temp database at all. From all of the docs I have read on the Session extension this seems like misbehavior at the level of sqlite3session_attach being passed NULL for the zTab argument. At the minimum perhaps a note in the documentation of sqlite3session_attach would be appropriate. Below is a very simple example that can reproduce this behavior. It has a number of lines commented out that describe how the bug can be avoided to caused. Its written in Golang, but the library it uses is just a very thin wrapper around the C interface. It is using the latest 3.31.1 amalgamation. I help maintain the SQLite library that it uses and can confirm that everything being called here is a very thin wrapper around the C interface. I could write a C example but it would take me more work. The library it calls to print the SQL of the changeset is a utility I wrote that is just using a thin wrapper around the changeset_iter to parse the changeset into human readable SQL. It of course is not aware of database names so it doesn't print "main" but this SQL is just for displaying to the user. The values returned by sqlite3changeset_conflict are formatted into the comments of that printed SQL. The code can be more easily downloaded here: https://github.com/AdamSLevy/sqlite-session-bug // main.go package main import ( "bytes" "fmt" "io" "log" "crawshaw.io/sqlite" "crawshaw.io/sqlite/sqlitex" "github.com/AdamSLevy/sqlitechangeset" ) func run() error { conn, err := sqlite.OpenConn(":memory:", 0) if err != nil { return fmt.Errorf("sqlite open: %w", err) } defer conn.Close() fmt.Println("Creating tables...") err = sqlitex.ExecScript(conn, ` CREATE TABLE main.t(id INTEGER PRIMARY KEY, a,b,c); CREATE TABLE temp.t(id INTEGER PRIMARY KEY, a,b,c) --- remove this line or rename the table to avoid issue; `) if err != nil { return err } fmt.Println("Starting session on main...") sess, err := conn.CreateSession("main") if err != nil { return err } defer sess.Delete() // An empty string will pass NULL to sqlite3session_attach and allow // the bug. var attach string // Any of these prevent the bug. //attach = "*" //attach = "main.*" //attach = "main.t" fmt.Printf("Attaching to %s ...\n", attach) if err := sess.Attach(attach); err != nil { return err } fmt.Println("Inserting into main.t ...") commit := sqlitex.Save(conn) err = sqlitex.ExecScript(conn, ` INSERT INTO main.t(a,b,c) VALUES (1,2,3); INSERT INTO temp.t(a,b,c) VALUES (1,2,3) --- This line avoids the conflict, but the changeset doesn't apply to main.t like expected; `) if err != nil { return err } commit(&err) sql, err := sqlitechangeset.SessionToSQL(conn, sess) if err != nil { return err } fmt.Println("changeset:", sql) chgset := bytes.NewBuffer(nil) if err := sess.Changeset(chgset); err != nil { return err } invrt := bytes.NewBuffer(nil) invrtCp := bytes.NewBuffer(nil) if err := sqlite.ChangesetInvert( io.MultiWriter(invrt, invrtCp), chgset); err != nil { return err } sql, err = sqlitechangeset.ToSQL(conn, invrtCp) if err != nil { return err } fmt.Println("inverted changeset:", sql) /*// Dropping the temp table avoids the issue... fmt.Println("Dropping temp table...") err = sqlitex.ExecScript(conn, ` DROP TABLE temp.t; `) if err != nil { return err } */ fmt.Println("applying inverted changeset...") conflictFn := func(cType sqlite.ConflictType, iter sqlite.ChangesetIter) sqlite.ConflictAction { fmt.Println("ConflictType:", cType) sql, err := sqlitechangeset.ConflictChangesetIterToSQL(conn, iter) if err != nil { fmt.Println(err) } fmt.Println("Conflict:", sql) return sqlite.SQLITE_CHANGESET_ABORT } if err := conn.ChangesetApply(invrt, filterFn, conflictFn); err != nil { return err } fmt.Println("done.") sql, err = sqlitechangeset.SessionToSQL(conn, sess) if err != nil { return err } if len(sql) > 0 { return fmt.Errorf("Error: current session changeset not empty: %v", sql) } fmt.Println("success") return nil } func filterFn(string) bool { return true } func main() { if err := run(); err != nil { log.Fatal(err) } } _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
