快捷導(dǎo)航

Golang對(duì)sqlite3數(shù)據(jù)庫進(jìn)行操作實(shí)踐記錄

更新時(shí)間：2024年03月13日 09:27:22 作者：李遲

sqlite是嵌入式關(guān)系型數(shù)據(jù)庫引擎,官方描述為自包含的、無服務(wù)的、零配置并支持事務(wù)的關(guān)系型數(shù)據(jù)庫引擎,下面這篇文章主要給大家介紹了關(guān)于Golang對(duì)sqlite3數(shù)據(jù)庫進(jìn)行操作的相關(guān)資料,需要的朋友可以參考下

概述

Golang 操作數(shù)據(jù)庫有統(tǒng)一的接口，當(dāng)然也有xorm這樣的庫，筆者接觸的項(xiàng)目不大，對(duì)sql自由組裝有要求，同時(shí)也會(huì)將這些sql用于數(shù)據(jù)庫客戶端查詢，因此偏向于使用原生的sql。

為方便起見，本文只針對(duì)sqlite進(jìn)行連接、讀寫、事務(wù)的測(cè)試。理論上可以擴(kuò)展到其它數(shù)據(jù)庫的操作。

技術(shù)小結(jié)

引入的包有"database/sql"、_ "github.com/mattn/go-sqlite3"。
使用sql.Open打開數(shù)據(jù)庫，對(duì)于sqlite3，不存在目標(biāo)文件時(shí)，會(huì)自創(chuàng)并使用。
事務(wù)相關(guān)接口有：開始SQLDB.Begin()、提交tx.Commit()、回滾tx.Rollback()、結(jié)束SQLDB.Close()。

設(shè)計(jì)

為讓測(cè)試代碼接近業(yè)務(wù)邏輯，設(shè)計(jì)場(chǎng)景如下：

設(shè)2個(gè)數(shù)據(jù)表：一為版本號(hào)表，一為信息明細(xì)表。
版本號(hào)更新了（如通過http下載數(shù)據(jù)，數(shù)據(jù)中有版本號(hào)），才更新明細(xì)表。程序通過讀取數(shù)據(jù)庫表的版本號(hào)進(jìn)行判斷。
允許上述數(shù)據(jù)表為空或不存在，由于sqlite3是基于文件的，也允許sqlite文件不存在。
同時(shí)寫上述2個(gè)數(shù)據(jù)表，同時(shí)成功了方認(rèn)為成功，因此使用到事務(wù)機(jī)制。

源碼分析

完整代碼見文后，本節(jié)按實(shí)現(xiàn)功能列出要點(diǎn)。

連接數(shù)據(jù)庫

func CreateSqlite3(dbname string, create bool) (sqldb *sql.DB, err error) {
    if create == false && !IsExist(dbname) {
        return nil, errors.New("open database failed: " + dbname + " not found")
    }
    sqldb, err = sql.Open("sqlite3", dbname)
    if err != nil {
        return nil, errors.New("open database failed: " + err.Error())
    }
    err = sqldb.Ping()
    if err != nil {
        return nil, errors.New("connect database failed: " + err.Error())
    }
    fmt.Println("connect to ", dbname, "ok")

    return
}

讀取版本號(hào)

讀取版本號(hào)，如果不存在，則創(chuàng)建對(duì)應(yīng)的表。

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

    if !needCreate {
        for results.Next() {
            var item1, item2 sql.NullString
            err := results.Scan(&item1, &item2)
            if err != nil {
                fmt.Println("scan error: ", err)
                break
            }
            if !item1.Valid || !item2.Valid {
                continue
            }
            version = item1.String
            updateTime = item2.String
        }

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

    return
}

以事務(wù)方式入庫

// 入庫2個(gè)表，以事務(wù)方式
func insertDBBatch(gxList []InfoList_t, version string) (err error) {
    SQLDB, err := CreateSqlite3(dbServer, false)
    if err != nil {
        // fmt.Println(err.Error())
        return err
    }

    var tx *sql.Tx
    tx, err = SQLDB.Begin()
    if err != nil {
        err = errors.New("begin sql error: " + err.Error())
        return err
    }

    defer func() {
        if err != nil {
            err = errors.New("exec sql failed rollback: " + err.Error())
            tx.Rollback()
        } else {
            err = nil
            tx.Commit()
        }
        // 延時(shí)一會(huì)，關(guān)閉
        Sleep(1000)
        SQLDB.Close()
    }()

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

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

    return
}

函數(shù)開始時(shí)，先調(diào)用SQLDB.Begin()開始事務(wù)，分別調(diào)用insertDBVersion和insertDBDetail入庫，只有2者同時(shí)成功，才調(diào)用tx.Commit()提交事務(wù)，否則調(diào)用tx.Rollback()回滾。提交事務(wù)或回滾，通過Golang的defer機(jī)制實(shí)現(xiàn)，邏輯較清晰。

測(cè)試

測(cè)試日志如下：

go test -v -run TestSqlite

沒有數(shù)據(jù)庫文件
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:  <nil>
--- PASS: TestSqlite (1.04s)
PASS

已有數(shù)據(jù)但版本較新
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:  <nil>
--- PASS: TestSqlite (1.03s)
PASS

附

完整代碼

package test

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

    _ "github.com/mattn/go-sqlite3"
)

var (
    // 數(shù)據(jù)庫文件名及表名
    dbServer     string = "foobar.db3"
    tableVersion string = "myVersion"
    tableList    string = "myList"
)

// 信息表 結(jié)構(gòu)體可對(duì)于json風(fēng)格數(shù)據(jù)傳輸解析
type InfoList_t struct {
    Id         int    `json:"-"`
    Version    string `json:"-"`
    Name       string `json:"-"`
    City       string `json:"-"`
    UpdateTime string `json:"-"`
}

var sqlarr []string = []string{
    // 版本號(hào)
    `CREATE TABLE "myVersion" (
        "version" VARCHAR(20) NOT NULL,
        "updateTime" datetime DEFAULT "",
        PRIMARY KEY ("version")
    );`,

    // 信息表
    `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 := os.Stat(path)
    return err == nil || os.IsExist(err)
}

func Sleep(ms int) {
    time.Sleep(time.Duration(ms) * time.Millisecond)
}

func CreateSqlite3(dbname string, create bool) (sqldb *sql.DB, err error) {
    if create == false && !IsExist(dbname) {
        return nil, errors.New("open database failed: " + dbname + " not found")
    }
    sqldb, err = sql.Open("sqlite3", dbname)
    if err != nil {
        return nil, errors.New("open database failed: " + err.Error())
    }
    err = sqldb.Ping()
    if err != nil {
        return nil, errors.New("connect database failed: " + err.Error())
    }
    fmt.Println("connect to ", dbname, "ok")

    return
}

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

    if !needCreate {
        for results.Next() {
            var item1, item2 sql.NullString
            err := results.Scan(&item1, &item2)
            if err != nil {
                fmt.Println("scan error: ", err)
                break
            }
            if !item1.Valid || !item2.Valid {
                continue
            }
            version = item1.String
            updateTime = item2.String
        }

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

    return
}

func insertDBDetail(tx *sql.Tx, gxList []InfoList_t, version string) (err error) {
    tablename := tableList
    sqlstr := fmt.Sprintf(`DELETE FROM %v`, tablename)
    stmt, err := tx.Prepare(sqlstr)
    if err != nil {
        err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error())
        return
    }
    _, err = stmt.Exec()
    if err != nil {
        err = errors.New("delete " + tablename + "failed: " + err.Error())
        return
    }

    sqlstr = fmt.Sprintf(`INSERT OR REPLACE INTO %v 
(id, version, name, city, updateTime) 
VALUES (?, ?, ?, ?, ?)`,
        tablename)
    stmt, _ = tx.Prepare(sqlstr)
    for _, item := range gxList {
        // item.Id = idx
        item.Version = version
        item.UpdateTime = com.GetNowDateTime("YYYY-MM-DD HH:mm:ss")
        _, err = stmt.Exec(item.Id, item.Version, item.Name, item.City, item.UpdateTime)
        if err != nil {
            err = errors.New("insert " + tablename + "failed: " + err.Error())
            return
        }
    }

    return
    // debug 制作bug
    // TODO 制作鎖住，制作語法錯(cuò)誤
    err = errors.New("database is locked")

    return
}

func insertDBVersion(tx *sql.Tx, version string) (err error) {
    tablename := tableVersion
    sqlstr := fmt.Sprintf(`DELETE FROM %v`, tablename)
    stmt, err := tx.Prepare(sqlstr)
    if err != nil {
        err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error())
        return
    }
    _, err = stmt.Exec()
    if err != nil {
        err = errors.New("delete " + tablename + " failed: " + err.Error())
        return
    }

    sqlstr = fmt.Sprintf(`INSERT OR REPLACE INTO %v (version, updateTime) VALUES (?, ?)`, tablename)
    stmt, err = tx.Prepare(sqlstr)
    if err != nil {
        err = errors.New("prepare for [" + sqlstr + "] failed: " + err.Error())
        return
    }
    updateTime := com.GetNowDateTime("YYYY-MM-DD HH:mm:ss")
    fmt.Printf("insert db version [%v] at: [%v]\n", version, updateTime)
    _, err = stmt.Exec(version, updateTime)
    if err != nil {
        err = errors.New("insert " + tablename + "failed: " + err.Error())
        return
    }

    return
}

// 入庫2個(gè)表，以事務(wù)方式
func insertDBBatch(gxList []InfoList_t, version string) (err error) {
    SQLDB, err := CreateSqlite3(dbServer, false)
    if err != nil {
        // fmt.Println(err.Error())
        return err
    }

    var tx *sql.Tx
    tx, err = SQLDB.Begin()
    if err != nil {
        err = errors.New("begin sql error: " + err.Error())
        return err
    }

    defer func() {
        if err != nil {
            err = errors.New("exec sql failed rollback: " + err.Error())
            tx.Rollback()
        } else {
            err = nil
            tx.Commit()
        }
        // 延時(shí)一會(huì)，關(guān)閉
        Sleep(1000)
        SQLDB.Close()
    }()

    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
    tmp.Id = 100
    tmp.Version = "100"
    tmp.Name = "latelee"
    tmp.City = "梧州"
    gxList = append(gxList, tmp)

    tmp = InfoList_t{}
    tmp.Id = 250
    tmp.Version = "250"
    tmp.Name = "latelee"
    tmp.City = "岑溪"
    gxList = append(gxList, tmp)

    return
}

// 讀取基礎(chǔ)信息，嘗試創(chuàng)建表
func readDBVersion() (version, datetime string) {
    SQLDB, err := CreateSqlite3(dbServer, true)
    if err != nil {
        fmt.Println(err.Error())
        return
    }
    version, datetime = readOrCreateDBTable(SQLDB)
    SQLDB.Close()

    return
}
func TestSqlite(t *testing.T) {
    fmt.Println("test of sqlte3...")

    // 1 嘗試獲取數(shù)據(jù)表的版本號(hào)（可能為空）
    version, datetime := readDBVersion()
    fmt.Printf("got db version [%v] update time [%v]\n", version, datetime)

    // 2 模擬業(yè)務(wù)：自定義版本號(hào)，較新時(shí)，才入庫
    myVer := "20231202"
    if myVer > version {
        data := makeData()
        err := insertDBBatch(data, myVer)
        fmt.Println("insert result: ", err)
    } else {
        fmt.Println("db is newest, do nothing")
    }

}