网站的建设书籍,网站的目的和意义,网站建设与维护考试,做啥网站赚钱本文使用 Golang 对 sqlite3 数据库进行操作。 概述
Golang 操作数据库有统一的接口#xff0c;当然也有xorm这样的库#xff0c;笔者接触的项目不大#xff0c;对sql自由组装有要求#xff0c;同时也会将这些sql用于数据库客户端查询#xff0c;因此偏向于使用原生的sql…本文使用 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/sqlerrorsfmtosstringstestingtimewebdemo/pkg/com_ github.com/mattn/go-sqlite3
)var (// 数据库文件名及表名dbServer string foobar.db3tableVersion string myVersiontableList 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 100tmp.Name lateleetmp.City 梧州gxList append(gxList, tmp)tmp InfoList_t{}tmp.Id 250tmp.Version 250tmp.Name lateleetmp.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 : 20231202if myVer version {data : makeData()err : insertDBBatch(data, myVer)fmt.Println(insert result: , err)} else {fmt.Println(db is newest, do nothing)}}
