Golang performs operational practice records of sqlite3 database

This article uses Golang to operate on the sqlite3 database.

Overview

Golang has a unified interface for operating databases, and of course, there arexormI have not been exposed to such a library, and I have requirements for free assembly of SQL. At the same time, these SQLs will also be used for database client queries, so I prefer to use native SQL.

For convenience, this article only tests connection, reading, writing, and transactions for SQLite. In theory, it can be extended to other database operations.

Technical summary

The packages introduced are"database/sql"、_ "/mattn/go-sqlite3"。
useOpen the database. For SQLite3, if the target file does not exist, it will be created and used.
Transaction-related interfaces are: Start(),submit(),rollback(),Finish()。

design

In order to get the test code closer to business logic, the design scenario is as follows:

Suppose two data tables: one is the version number table and the other is the information list.
The version number is updated (such as downloading data through http and there is a version number in the data), and the detailed list will be updated. The program judges by reading the version number of the database table.
The above data table is allowed to be empty or non-existent, because sqlite3 is file-based, and the sqlite file is also allowed to not exist.
The above two data tables are written at the same time, and the successful one is considered successful, so the transaction mechanism is used.

Source code analysis

After the complete code is found in the article, this section lists the key points according to the implementation function.

Connect to the database

func CreateSqlite3(dbname string, create bool) (sqldb *, err error) {
    if create == false && !IsExist(dbname) {
        return nil, ("open database failed: " + dbname + " not found")
    }
    sqldb, err = ("sqlite3", dbname)
    if err != nil {
        return nil, ("open database failed: " + ())
    }
    err = ()
    if err != nil {
        return nil, ("connect database failed: " + ())
    }
    ("connect to ", dbname, "ok")

    return
}

Read version number

Read the version number and create the corresponding table if it does not exist.

func readOrCreateDBTable(sqldb *) (version, updateTime string) {
    needCreate := false
    sqlstr := (`select version, updateTime from %v order by version desc limit 1`,
        tableVersion)
    ("run sql: [%v]\n", sqlstr)
    results, err := (sqlstr)
    if err != nil {
        if ((), "no such table") {
            needCreate = true
        } else {
            ("query error: ", err)
            return
        }
    }

    if !needCreate {
        for () {
            var item1, item2 
            err := (&item1, &item2)
            if err != nil {
                ("scan error: ", err)
                break
            }
            if ! || ! {
                continue
            }
            version = 
            updateTime = 
        }

        defer ()
    } else {
        ("not found table, will create it.")
        for _, item := range sqlarr {
            _, err := (item)
            if err != nil {
                ("Exec sql failed: [%v] [%v] \n", err, item)
            }
        }
    }

    return
}

Initialize transactionally

// 2 tables are stored in transactional formfunc insertDBBatch(gxList []InfoList_t, version string) (err error) {
    SQLDB, err := CreateSqlite3(dbServer, false)
    if err != nil {
        // (())
        return err
    }

    var tx *
    tx, err = ()
    if err != nil {
        err = ("begin sql error: " + ())
        return err
    }

    defer func() {
        if err != nil {
            err = ("exec sql failed rollback: " + ())
            ()
        } else {
            err = nil
            ()
        }
        // Delay for a while, close        Sleep(1000)
        ()
    }()

    err = insertDBVersion(tx, version)
    if err != nil {
        return
    }

    err = insertDBDetail(tx, gxList, version)
    if err != nil {
        return
    }

    return
}

When the function starts, call it first()Start the transaction and call it separatelyinsertDBVersionandinsertDBDetailEnter the database, only if two people succeed at the same time will be called()Submit transaction, otherwise call()rollback. Submit transaction or rollback, through Golang'sdeferThe mechanism is implemented and the logic is clear.

test

The test log is as follows:

go test -v -run TestSqlite

No database file
test of sqlte3...
connect to  foobar.db3 ok
run sql:
select version, updateTime from myVersion order by version desc limit 1
not found table, will create it.
got db version [] update time []
connect to  foobar.db3 ok
insert db version [] at: [2023-12-02 10:42:18]
insert result:  &lt;nil&gt;
--- PASS: TestSqlite (1.04s)
PASS

There is already data but the version is newer
test of sqlte3...
connect to  foobar.db3 ok
run sql: [select version, updateTime from myVersion order by version desc limit 1]
got db version [20231202] update time [2023-12-02T10:48:20Z]
connect to  foobar.db3 ok
insert db version [20231203] at: [2023-12-02 10:48:47]
insert result:  &lt;nil&gt;
--- PASS: TestSqlite (1.03s)
PASS

Attached

Complete code

package test

import (
    "database/sql"
    "errors"
    "fmt"
    "os"
    "strings"
    "testing"
    "time"
    "webdemo/pkg/com"

    _ "/mattn/go-sqlite3"
)

var (
    // Database file name and table name    dbServer     string = "foobar.db3"
    tableVersion string = "myVersion"
    tableList    string = "myList"
)

// Information table structure can be analyzed for json-style data transmissiontype InfoList_t struct {
    Id         int    `json:"-"`
    Version    string `json:"-"`
    Name       string `json:"-"`
    City       string `json:"-"`
    UpdateTime string `json:"-"`
}

var sqlarr []string = []string{
    // Version number    `CREATE TABLE "myVersion" (
        "version" VARCHAR(20) NOT NULL,
        "updateTime" datetime DEFAULT "",
        PRIMARY KEY ("version")
    );`,

    // Information table    `CREATE TABLE "myList" (
        "id" int NOT NULL,
        "version" VARCHAR(20) NOT NULL,
        "name" VARCHAR(20) NOT NULL,
        "city" VARCHAR(20) NOT NULL,
        "updateTime" datetime DEFAULT "",
        PRIMARY KEY ("id")
    );`,
}

func IsExist(path string) bool {
    _, err := (path)
    return err == nil || (err)
}

func Sleep(ms int) {
    ((ms) * )
}

func CreateSqlite3(dbname string, create bool) (sqldb *, err error) {
    if create == false &amp;&amp; !IsExist(dbname) {
        return nil, ("open database failed: " + dbname + " not found")
    }
    sqldb, err = ("sqlite3", dbname)
    if err != nil {
        return nil, ("open database failed: " + ())
    }
    err = ()
    if err != nil {
        return nil, ("connect database failed: " + ())
    }
    ("connect to ", dbname, "ok")

    return
}

func readOrCreateDBTable(sqldb *) (version, updateTime string) {
    needCreate := false
    sqlstr := (`select version, updateTime from %v order by version desc limit 1`,
        tableVersion)
    ("run sql: [%v]\n", sqlstr)
    results, err := (sqlstr)
    if err != nil {
        if ((), "no such table") {
            needCreate = true
        } else {
            ("query error: ", err)
            return
        }
    }

    if !needCreate {
        for () {
            var item1, item2 
            err := (&amp;item1, &amp;item2)
            if err != nil {
                ("scan error: ", err)
                break
            }
            if ! || ! {
                continue
            }
            version = 
            updateTime = 
        }

        defer ()
    } else {
        ("not found table, will create it.")
        for _, item := range sqlarr {
            _, err := (item)
            if err != nil {
                ("Exec sql failed: [%v] [%v] \n", err, item)
            }
        }
    }

    return
}

func insertDBDetail(tx *, gxList []InfoList_t, version string) (err error) {
    tablename := tableList
    sqlstr := (`DELETE FROM %v`, tablename)
    stmt, err := (sqlstr)
    if err != nil {
        err = ("prepare for [" + sqlstr + "] failed: " + ())
        return
    }
    _, err = ()
    if err != nil {
        err = ("delete " + tablename + "failed: " + ())
        return
    }

    sqlstr = (`INSERT OR REPLACE INTO %v 
(id, version, name, city, updateTime) 
VALUES (?, ?, ?, ?, ?)`,
        tablename)
    stmt, _ = (sqlstr)
    for _, item := range gxList {
        //  = idx
         = version
         = ("YYYY-MM-DD HH:mm:ss")
        _, err = (, , , , )
        if err != nil {
            err = ("insert " + tablename + "failed: " + ())
            return
        }
    }

    return
    // debug create bug    // TODO production lock, production syntax error    err = ("database is locked")

    return
}

func insertDBVersion(tx *, version string) (err error) {
    tablename := tableVersion
    sqlstr := (`DELETE FROM %v`, tablename)
    stmt, err := (sqlstr)
    if err != nil {
        err = ("prepare for [" + sqlstr + "] failed: " + ())
        return
    }
    _, err = ()
    if err != nil {
        err = ("delete " + tablename + " failed: " + ())
        return
    }

    sqlstr = (`INSERT OR REPLACE INTO %v (version, updateTime) VALUES (?, ?)`, tablename)
    stmt, err = (sqlstr)
    if err != nil {
        err = ("prepare for [" + sqlstr + "] failed: " + ())
        return
    }
    updateTime := ("YYYY-MM-DD HH:mm:ss")
    ("insert db version [%v] at: [%v]\n", version, updateTime)
    _, err = (version, updateTime)
    if err != nil {
        err = ("insert " + tablename + "failed: " + ())
        return
    }

    return
}

// 2 tables are stored in transactional formfunc insertDBBatch(gxList []InfoList_t, version string) (err error) {
    SQLDB, err := CreateSqlite3(dbServer, false)
    if err != nil {
        // (())
        return err
    }

    var tx *
    tx, err = ()
    if err != nil {
        err = ("begin sql error: " + ())
        return err
    }

    defer func() {
        if err != nil {
            err = ("exec sql failed rollback: " + ())
            ()
        } else {
            err = nil
            ()
        }
        // Delay for a while, close        Sleep(1000)
        ()
    }()

    err = insertDBVersion(tx, version)
    if err != nil {
        return
    }

    err = insertDBDetail(tx, gxList, version)
    if err != nil {
        return
    }

    return
}

//
func makeData() (gxList []InfoList_t) {
    var tmp InfoList_t
     = 100
     = "100"
     = "latelee"
     = "Wuzhou"
    gxList = append(gxList, tmp)

    tmp = InfoList_t{}
     = 250
     = "250"
     = "latelee"
     = "Cenxi"
    gxList = append(gxList, tmp)

    return
}

// Read the basic information and try to create a tablefunc readDBVersion() (version, datetime string) {
    SQLDB, err := CreateSqlite3(dbServer, true)
    if err != nil {
        (())
        return
    }
    version, datetime = readOrCreateDBTable(SQLDB)
    ()

    return
}
func TestSqlite(t *) {
    ("test of sqlte3...")

    // 1 Try to get the version number of the data table (maybe empty)    version, datetime := readDBVersion()
    ("got db version [%v] update time [%v]\n", version, datetime)

    // 2 Simulation business: Custom version number, only enter the library when it is newer    myVer := "20231202"
    if myVer &gt; version {
        data := makeData()
        err := insertDBBatch(data, myVer)
        ("insert result: ", err)
    } else {
        ("db is newest, do nothing")
    }

}

Summarize

This is the end of this article about Golang's operation of the SQLite3 database. For more information about Golang's use of SQLite, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!