Go-sqlite3: How to get current connection name

[jacentsao]

Hi mattn,
Recently i changed our journal_mode from DELETE to WAL due to database lock problem, after this i meet backup problem, we used to just copy file to backup the sqlite database, it's okay to do this. But using wal it cannot doing so, and using the backup function in hook. Here is my code:

    destDb, err := sql.Open(driverName,bakf)
    if err != nil {
        beego.Error(err)
    }
    defer destDb.Close()
    destDb.Ping()
    bk, err := sqlite3conn[1].Backup("main",sqlite3conn[0], "main")
    if err != nil {
        beego.Error(err)
    }
    _, err = bk.Step(-1)
    if err != nil {
        beego.Error(err)
    }
    bk.Finish()

But in our app we will dynamic open many db file , and in some situation i cannot just using index to backup the database, is there any function to distinguish the connection file name or something else. Many thanks

[GJRTimmer]

We could look into activating the SQLITE_ENABLE_COLUMN_METADATA Compile option.
This option will activate the following functions:

• sqlite3_column_database_name()
• sqlite3_column_database_name16()
• sqlite3_column_table_name()
• sqlite3_column_table_name16()
• sqlite3_column_origin_name()

@jacentsao can you look at https://www.sqlite.org/c3ref/column_database_name.html and find out of these functions would help with your problem ?

@mattn Does this require a new compilation of the amalgamation I personally don't we need to because we are activating code which should already be present in the amalgamation.

We do need however make new golang C bindings for these functions.
sqlite3_column_origin_name16()

[jacentsao]

@GJRTimmer Thanks for your kindly reply, finally i solved my problem in other way. Every time i want to backup my db, i will clear sqlite3conn and reopen the target db and source db, so i can use the index to backup the database. Of course, if can be distinguished by the name is better, and after reading the doc below i think it's okay for me. Thank you very much.

[GJRTimmer]

@jacentsao Check out the new master. Check README together with mattn we added a lot of new PRAGMA's to the DSN connection settings. Tip: run VACUUM command before backup / set _auto_vacuum on your connection.

[jacentsao]

@GJRTimmer @mattn Hi guys, sorry for not response to GJRTimmer because i meet another problem. I just follows the hook.go to backup my database, but when id close all the connection after backup the database is still in connection because i still see the -wal -shm file and using os.Remove() returns err like below.

The process cannot access the file because it is being used by another process. 

Here is my sample code below

func main() {
    sqlite3conn := []*sqlite3.SQLiteConn{}
    sql.Register("sqlite3_with_hook_example",
        &sqlite3.SQLiteDriver{
            ConnectHook: func(conn *sqlite3.SQLiteConn) error {
                sqlite3conn = append(sqlite3conn, conn)
                conn.RegisterUpdateHook(func(op int, db string, table string, rowid int64) {
                    switch op {
                    case sqlite3.SQLITE_INSERT:
                        log.Println("Notified of insert on db", db, "table", table, "rowid", rowid)
                    }
                })
                return nil
            },
        })
    os.Remove("./foo.db")
    os.Remove("./bar.db")

    srcDb, err := sql.Open("sqlite3_with_hook_example", "./foo.db")

    srcDb.Exec("PRAGMA journal_mode = WAL")

    if err != nil {
        log.Fatal(err)
    }

    srcDb.Ping()

    _, err = srcDb.Exec("create table foo(id int, value text)")
    if err != nil {
        log.Fatal(err)
    }
    _, err = srcDb.Exec("insert into foo values(1, 'foo')")
    if err != nil {
        log.Fatal(err)
    }
    _, err = srcDb.Exec("insert into foo values(2, 'bar')")
    if err != nil {
        log.Fatal(err)
    }
    _, err = srcDb.Query("select * from foo")
    if err != nil {
        log.Fatal(err)
    }
    destDb, err := sql.Open("sqlite3_with_hook_example", "./bar.db")
    if err != nil {
        log.Fatal(err)
    }
    defer func() {
        destDb.Close()
        srcDb.Close()
        log.Println(len(sqlite3conn))
        if err = os.Remove("./foo.db"); err != nil {
            log.Fatal(err.Error())
        }
    }()
    destDb.Ping()

    bk, err := sqlite3conn[1].Backup("main", sqlite3conn[0], "main")
    if err != nil {
        log.Fatal(err)
    }

    _, err = bk.Step(-1)
    if err != nil {
        log.Fatal(err)
    }
    _, err = destDb.Query("select * from foo")
    if err != nil {
        log.Fatal(err)
    }
    _, err = destDb.Exec("insert into foo values(3, 'bar')")
    if err != nil {
        log.Fatal(err)
    }

    bk.Finish()
}

and output is

2018/06/01 14:30:13 Notified of insert on db main table foo rowid 1
2018/06/01 14:30:13 Notified of insert on db main table foo rowid 2
2018/06/01 14:30:13 Notified of insert on db main table foo rowid 3
2018/06/01 14:30:13 3
2018/06/01 14:30:13 remove ./foo.db: The process cannot access the file because it is being used by another process.
exit status 1

How should i solve this problem, Thanks

[GJRTimmer]

@jacentsao Could this because []*sqlite3.SQLiteConn{} still holds references to connections which causes the connection to remain available not being cleaned up ?

Can you add some code in the defer func(...) which also cleans the connection references you have saved ?

[jacentsao]

@GJRTimmer here is my code and it still have the same problem

defer func() {
        destDb.Close()
        srcDb.Close()
        log.Println(len(sqlite3conn))
        for _, conn := range sqlite3conn {
            conn.Close()
        }
        sqlite3conn = sqlite3conn[:0]
        time.Sleep(3 * time.Second)
        if err = os.Remove("./foo.db"); err != nil {
            log.Fatal(err.Error())
        }
    }()

I searched for times and get the links below, but don't know how to make it in golang
https://www.sqlite.org/c3ref/close.html
https://stackoverflow.com/questions/8511901/system-data-sqlite-close-not-releasing-database-file