当前位置：首页 > news >正文

Golang实践录：sqlite的使用

news 来源：原创 2024/9/30 0:13:55

本文使用 Golang 对 sqlite3 数据库进行操作。

概述

Golang 操作数据库有统一的接口，当然也有xorm这样的库，笔者接触的项目不大，对sql自由组装有要求，同时也会将这些sql用于数据库客户端查询，因此偏向于使用原生的sql。

为方便起见，本文只针对sqlite进行连接、读写、事务的测试。理论上可以扩展到其它数据库的操作。

技术小结

引入的包有"database/sql"、_ "github.com/mattn/go-sqlite3"。
使用sql.Open打开数据库，对于sqlite3，不存在目标文件时，会自创并使用。
事务相关接口有：开始SQLDB.Begin()、提交tx.Commit()、回滚tx.Rollback()、结束SQLDB.Close()。

设计

为让测试代码接近业务逻辑，设计场景如下：

设2个数据表：一为版本号表，一为信息明细表。
版本号更新了（如通过http下载数据，数据中有版本号），才更新明细表。程序通过读取数据库表的版本号进行判断。
允许上述数据表为空或不存在，由于sqlite3是基于文件的，也允许sqlite文件不存在。
同时写上述2个数据表，同时成功了方认为成功，因此使用到事务机制。

源码分析

完整代码见文后，本节按实现功能列出要点。

连接数据库

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 := falsesqlstr := 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.NullStringerr := results.Scan(&item1, &item2)if err != nil {fmt.Println("scan error: ", err)break}if !item1.Valid || !item2.Valid {continue}version = item1.StringupdateTime = 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
}

以事务方式入库

// 入库2个表，以事务方式
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.Txtx, 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 = niltx.Commit()}// 延时一会，关闭Sleep(1000)SQLDB.Close()}()err = insertDBVersion(tx, version)if err != nil {return}err = insertDBDetail(tx, gxList, version)if err != nil {return}return
}

函数开始时，先调用SQLDB.Begin()开始事务，分别调用insertDBVersion和insertDBDetail入库，只有2者同时成功，才调用tx.Commit()提交事务，否则调用tx.Rollback()回滚。提交事务或回滚，通过Golang的defer机制实现，逻辑较清晰。

测试

测试日志如下：

go test -v -run TestSqlite没有数据库文件
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已有数据但版本较新
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 testimport ("database/sql""errors""fmt""os""strings""testing""time""webdemo/pkg/com"_ "github.com/mattn/go-sqlite3"
)var (// 数据库文件名及表名dbServer     string = "foobar.db3"tableVersion string = "myVersion"tableList    string = "myList"
)// 信息表 结构体可对于json风格数据传输解析
type InfoList_t struct {Id         int    `json:"-"`Version    string `json:"-"`Name       string `json:"-"`City       string `json:"-"`UpdateTime string `json:"-"`
}var sqlarr []string = []string{// 版本号`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 := falsesqlstr := 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.NullStringerr := results.Scan(&item1, &item2)if err != nil {fmt.Println("scan error: ", err)break}if !item1.Valid || !item2.Valid {continue}version = item1.StringupdateTime = 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 := tableListsqlstr := 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 = idxitem.Version = versionitem.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 制作锁住，制作语法错误err = errors.New("database is locked")return
}func insertDBVersion(tx *sql.Tx, version string) (err error) {tablename := tableVersionsqlstr := 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个表，以事务方式
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.Txtx, 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 = niltx.Commit()}// 延时一会，关闭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_ttmp.Id = 100tmp.Version = "100"tmp.Name = "latelee"tmp.City = "梧州"gxList = append(gxList, tmp)tmp = InfoList_t{}tmp.Id = 250tmp.Version = "250"tmp.Name = "latelee"tmp.City = "岑溪"gxList = append(gxList, tmp)return
}// 读取基础信息，尝试创建表
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 尝试获取数据表的版本号（可能为空）version, datetime := readDBVersion()fmt.Printf("got db version [%v] update time [%v]\n", version, datetime)// 2 模拟业务：自定义版本号，较新时，才入库myVer := "20231202"if myVer > version {data := makeData()err := insertDBBatch(data, myVer)fmt.Println("insert result: ", err)} else {fmt.Println("db is newest, do nothing")}}