Golang对sqlite3数据库进行操作实践记录

2024-04-18 0 274
目录
  • 概述
  • 技术小结
  • 设计
  • 源码分析
    • 连接数据库
    • 读取版本号
    • 以事务方式入库
  • 测试
      • 完整代码
    • 总结

      本文使用 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 := 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
      }

      以事务方式入库

      // 入库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.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()
      }
      // 延时一会,关闭
      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 test

      import (
      \”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 := 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 制作锁住,制作语法错误
      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个表,以事务方式
      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()
      }
      // 延时一会,关闭
      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
      }

      // 读取基础信息,尝试创建表
      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\”)
      }

      }

      总结

      到此这篇关于Golang对sqlite3数据库进行操作的文章就介绍到这了,更多相关Golang使用sqlite内容请搜索悠久资源网以前的文章或继续浏览下面的相关文章希望大家以后多多支持悠久资源网!

      您可能感兴趣的文章:

      • Golang操作sqlite3数据库的详细教程
      • golang通过gorm操作sqlite设置主键自增的步骤

      收藏 (0) 打赏

      感谢您的支持,我会继续努力的!

      打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
      点赞 (0)

      悠久资源 Golang Golang对sqlite3数据库进行操作实践记录 https://www.u-9.cn/jiaoben/golang/187055.html

      常见问题

      相关文章

      发表评论
      暂无评论
      官方客服团队

      为您解决烦忧 - 24小时在线 专业服务