mirror of https://github.com/allinssl/allinssl
1101 lines
23 KiB
Go
1101 lines
23 KiB
Go
package public
|
||
|
||
import (
|
||
"database/sql"
|
||
"errors"
|
||
"fmt"
|
||
_ "modernc.org/sqlite"
|
||
"os"
|
||
"strconv"
|
||
"strings"
|
||
)
|
||
|
||
/*
|
||
*
|
||
|
||
- @brief Sqlite对象
|
||
示例：
|
||
fmt.Println(data)
|
||
*/
|
||
type Sqlite struct {
|
||
DbFile string
|
||
PreFix string
|
||
TableName string
|
||
Conn *sql.DB
|
||
JoinTable []string
|
||
JoinOn []string
|
||
JoinType []string
|
||
JoinParam []interface{}
|
||
OptField []string
|
||
OptLimit string
|
||
OptWhere string
|
||
OptOrder string
|
||
OptParam []interface{}
|
||
OptGroupBy string
|
||
OptHaving string
|
||
Tx *sql.Tx
|
||
TxErr error
|
||
Sql string
|
||
closed bool
|
||
}
|
||
|
||
/**
|
||
* @brief 实例化Sqlite对象
|
||
* @param DbFile string 数据库文件
|
||
* @param PreFix string 表前缀
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
* @return error 错误信息
|
||
*/
|
||
func NewSqlite(DbFile string, PreFix string) (*Sqlite, error) {
|
||
s := Sqlite{}
|
||
s.DbFile = DbFile
|
||
s.PreFix = PreFix
|
||
s.closed = true
|
||
if !FileExists(DbFile) {
|
||
return nil, errors.New("错误：指定数据库文件不存在")
|
||
}
|
||
err := s.Connect()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
_, err = s.Conn.Exec("PRAGMA busy_timeout = 5000;")
|
||
if err != nil {
|
||
s.Close()
|
||
return nil, fmt.Errorf("设置PRAGMA busy_timeout失败: %w", err)
|
||
}
|
||
|
||
return &s, nil
|
||
}
|
||
|
||
func FileExists(file string) bool {
|
||
_, err := os.Stat(file)
|
||
if err == nil {
|
||
return true
|
||
}
|
||
return false
|
||
}
|
||
|
||
/**
|
||
* @brief 连接数据库
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Connect() error {
|
||
conn, err := sql.Open("sqlite", s.DbFile)
|
||
if err == nil {
|
||
s.Conn = conn
|
||
s.closed = false
|
||
|
||
}
|
||
return err
|
||
}
|
||
|
||
/**
|
||
* @brief 关闭数据库连接
|
||
*/
|
||
func (s *Sqlite) Close() {
|
||
s.Conn.Close()
|
||
s.closed = true
|
||
s.Tx = nil
|
||
}
|
||
|
||
/**
|
||
* @brief 设置数据库主机地址
|
||
* @param Host string 主机地址
|
||
* @param Port int 端口号
|
||
* @return void
|
||
*/
|
||
func (s *Sqlite) SetDbFile(DbFile string) {
|
||
if !FileExists(DbFile) {
|
||
panic("错误：指定数据库文件不存在")
|
||
}
|
||
s.DbFile = DbFile
|
||
}
|
||
|
||
/**
|
||
* @brief 设置要操作的表名
|
||
* @param tableName string 表名
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Table(tableName string) *Sqlite {
|
||
s.TableName = s.PreFix + tableName
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置要返回的字段
|
||
* @param field []string 字段名数组
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Field(field []string) *Sqlite {
|
||
s.OptField = field
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置取回行数
|
||
* @param limit []int64 ,limit[0]为起始行数，limit[1]为取回行数
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Limit(limit []int64) *Sqlite {
|
||
last_limit := " LIMIT "
|
||
limit_len := len(limit)
|
||
if limit_len == 0 {
|
||
s.OptLimit = ""
|
||
} else if limit_len == 1 {
|
||
s.OptLimit = last_limit + strconv.FormatInt(limit[0], 10)
|
||
} else if limit_len >= 2 {
|
||
s.OptLimit = last_limit + strconv.FormatInt(limit[0], 10) + "," + strconv.FormatInt(limit[1], 10)
|
||
} else {
|
||
s.OptLimit = ""
|
||
}
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置排序
|
||
* @param fieldName string 字段名
|
||
* @param sortOrder string 排序方式，ASC为升序，DESC为降序
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Order(fieldName string, sortOrder string) *Sqlite {
|
||
sortOrder = strings.ToUpper(sortOrder)
|
||
if sortOrder != "ASC" && sortOrder != "DESC" {
|
||
sortOrder = "ASC"
|
||
}
|
||
s.OptOrder = " ORDER BY " + fieldName + " " + sortOrder
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置排序
|
||
* @param fieldName string 字段名
|
||
* @param sortOrder string 排序方式，ASC为升序，DESC为降序
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Sort(fieldName string, sortOrder string) *Sqlite {
|
||
return s.Order(fieldName, sortOrder)
|
||
}
|
||
|
||
/**
|
||
* @brief 设置查询条件
|
||
* @param where string 查询条件
|
||
* @param param []interface{} 查询条件参数
|
||
* @return *Sqlite 实例化的Sqlite对象
|
||
*/
|
||
func (s *Sqlite) Where(where string, param []interface{}) *Sqlite {
|
||
s.OptWhere = " WHERE " + where
|
||
s.OptParam = param
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 获取查询字段
|
||
* @return string 查询字段
|
||
*/
|
||
func (s *Sqlite) getField() string {
|
||
field := "*"
|
||
if len(s.OptField) > 0 {
|
||
field = strings.Join(s.OptField, ",")
|
||
}
|
||
return field
|
||
}
|
||
|
||
func (s *Sqlite) getJoinOn(sql string) string {
|
||
// 处理JOIN
|
||
for i := 0; i < len(s.JoinTable); i++ {
|
||
if s.JoinTable[i] == "" {
|
||
continue
|
||
}
|
||
|
||
// 处理JOIN类型
|
||
if s.JoinType[i] != "" {
|
||
s.JoinType[i] = " " + s.JoinType[i]
|
||
}
|
||
|
||
// 拼接JOIN ON
|
||
sql += " " + s.JoinType[i] + " JOIN " + s.JoinTable[i] + " ON " + s.JoinOn[i]
|
||
}
|
||
|
||
// 处理JOIN参数
|
||
if s.JoinParam != nil {
|
||
joinParamLen := len(s.JoinParam)
|
||
optParamLen := len(s.OptParam)
|
||
params := make([]interface{}, 0)
|
||
if joinParamLen > 0 {
|
||
params = append(params, s.JoinParam...)
|
||
}
|
||
if optParamLen > 0 {
|
||
params = append(params, s.JoinParam...)
|
||
}
|
||
|
||
s.OptParam = params
|
||
}
|
||
return sql
|
||
}
|
||
|
||
/**
|
||
* @brief 获取查询条件
|
||
* @param sql string sql语句
|
||
* @return string 拼接查询条件后的sql语句
|
||
*/
|
||
func (s *Sqlite) getWhere(sql string) string {
|
||
if s.OptWhere != "" {
|
||
sql += s.OptWhere
|
||
}
|
||
return sql
|
||
}
|
||
|
||
func (s *Sqlite) getGroup(sql string) string {
|
||
if s.OptGroupBy != "" {
|
||
sql += " " + s.OptGroupBy + " "
|
||
}
|
||
return sql
|
||
}
|
||
|
||
/**
|
||
* @brief 获取排序
|
||
* @param sql string sql语句
|
||
* @return string 拼接排序后的sql语句
|
||
*/
|
||
func (s *Sqlite) getOrder(sql string) string {
|
||
if s.OptOrder != "" {
|
||
sql += s.OptOrder
|
||
}
|
||
return sql
|
||
}
|
||
|
||
/**
|
||
* @brief 获取行数
|
||
* @param sql string sql语句
|
||
* @return string 拼接行数后的sql语句
|
||
*/
|
||
func (s *Sqlite) getLimit(sql string) string {
|
||
if s.OptLimit != "" {
|
||
sql += s.OptLimit
|
||
}
|
||
return sql
|
||
}
|
||
|
||
func (s *Sqlite) checkParam(sql string, param []interface{}) error {
|
||
// 检查参数数量
|
||
sqlCount := strings.Count(sql, "?")
|
||
paramCount := len(param)
|
||
|
||
if sqlCount != paramCount {
|
||
return errors.New("参数数量不匹配，要求绑定" + strconv.Itoa(sqlCount) + "个参数，实际绑定" + strconv.Itoa(paramCount) + "个参数")
|
||
}
|
||
return nil
|
||
}
|
||
|
||
/**
|
||
* @brief 获取数据集
|
||
* @return []map[string]interface{} 数据集
|
||
*/
|
||
func (s *Sqlite) Select() ([]map[string]interface{}, error) {
|
||
if s.TableName == "" {
|
||
return nil, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
field := s.getField()
|
||
|
||
s.Sql = "SELECT " + field + " FROM " + s.TableName
|
||
|
||
s.Sql = s.getJoinOn(s.Sql)
|
||
s.Sql = s.getWhere(s.Sql)
|
||
s.Sql = s.getGroup(s.Sql)
|
||
s.Sql = s.getOrder(s.Sql)
|
||
s.Sql = s.getLimit(s.Sql)
|
||
defer s.clearOpt()
|
||
// fmt.Println(s.Sql, s.OptParam)
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// 清空参数
|
||
rows, err := s.Conn.Query(s.Sql, s.OptParam...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
columns, _ := rows.Columns()
|
||
count := len(columns)
|
||
values := make([]interface{}, count)
|
||
valuePtrs := make([]interface{}, count)
|
||
var result []map[string]interface{}
|
||
for rows.Next() {
|
||
for i := 0; i < count; i++ {
|
||
valuePtrs[i] = &values[i]
|
||
}
|
||
rows.Scan(valuePtrs...)
|
||
row := make(map[string]interface{})
|
||
for i, col := range columns {
|
||
var v interface{}
|
||
val := values[i]
|
||
b, ok := val.([]byte)
|
||
if ok {
|
||
v = string(b)
|
||
} else {
|
||
v = val
|
||
}
|
||
row[col] = v
|
||
}
|
||
result = append(result, row)
|
||
}
|
||
return result, err
|
||
}
|
||
|
||
/**
|
||
* @brief 插入数据
|
||
* @param data map[string]interface{} 要插入的数据
|
||
* @return int64 插入的数据ID
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Insert(data map[string]interface{}) (int64, error) {
|
||
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
|
||
var keys []string
|
||
var values []interface{}
|
||
var placeholders []string
|
||
for k, v := range data {
|
||
keys = append(keys, k)
|
||
values = append(values, v)
|
||
placeholders = append(placeholders, "?")
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
s.Sql = "INSERT INTO " + s.TableName + " (" + strings.Join(keys, ",") + ") VALUES (" + strings.Join(placeholders, ",") + ")"
|
||
stmt, err := s.Conn.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer stmt.Close()
|
||
res, err := stmt.Exec(values...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
id, err := res.LastInsertId()
|
||
|
||
return id, err
|
||
}
|
||
|
||
/**
|
||
* @brief 更新数据
|
||
* @param data map[string]interface{} 要更新的数据
|
||
* @return int64 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Update(data map[string]interface{}) (int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
var keys []string
|
||
var values []interface{}
|
||
for k, v := range data {
|
||
keys = append(keys, k)
|
||
values = append(values, v)
|
||
}
|
||
s.Sql = "UPDATE " + s.TableName + " SET " + strings.Join(keys, "=?,") + "=?"
|
||
if s.OptWhere != "" {
|
||
s.Sql += s.OptWhere
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
stmt, err := s.Conn.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer stmt.Close()
|
||
values = append(values, s.OptParam...)
|
||
|
||
// 检查参数数量
|
||
err = s.checkParam(s.Sql, values)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
res, err := stmt.Exec(values...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
return res.RowsAffected()
|
||
}
|
||
|
||
/**
|
||
* @brief 插入更新
|
||
* @param data map[string]interface{} 要更新的数据
|
||
* @param uniqueIdx 依据的唯一索引的字段信息，如果数据库中不存在该索引，则会出现报错 如：(date, uri_id)
|
||
* @param updateFormula 更新字段的表达式，如: request = request + excluded.request
|
||
* @return int64 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Upsert(uniqueIdx []string, data map[string]interface{}, updateFormula string) (int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
|
||
var keys []string
|
||
var values []interface{}
|
||
placeholdersBuilder := strings.Builder{}
|
||
var first = true
|
||
for k, v := range data {
|
||
keys = append(keys, k)
|
||
values = append(values, v)
|
||
if first {
|
||
placeholdersBuilder.Write([]byte("?"))
|
||
first = false
|
||
} else {
|
||
placeholdersBuilder.Write([]byte(",?"))
|
||
}
|
||
}
|
||
placeholders := placeholdersBuilder.String()
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
s.Sql = fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s) ON CONFLICT (%s) DO UPDATE SET %s",
|
||
s.TableName, strings.Join(keys, ","), placeholders, strings.Join(uniqueIdx, ","), updateFormula)
|
||
stmt, err := s.Conn.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer stmt.Close()
|
||
res, err := stmt.Exec(values...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
id, err := res.LastInsertId()
|
||
|
||
return id, err
|
||
}
|
||
|
||
/**
|
||
* @brief 删除数据
|
||
* @return int64 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Delete() (int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
s.Sql = "DELETE FROM " + s.TableName
|
||
if s.OptWhere != "" {
|
||
s.Sql += s.OptWhere
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
// 预编译SQL语句
|
||
stmt, err := s.Conn.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer stmt.Close()
|
||
|
||
// 执行SQL语句
|
||
res, err := stmt.Exec(s.OptParam...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
return res.RowsAffected()
|
||
}
|
||
|
||
/**
|
||
* @brief 获取一条数据
|
||
* @return sap[string]interface{} 数据集
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Find() (map[string]interface{}, error) {
|
||
s.Limit([]int64{1})
|
||
result, err := s.Select()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if len(result) > 0 {
|
||
return result[0], nil
|
||
}
|
||
|
||
return nil, errors.New("not found")
|
||
}
|
||
|
||
/**
|
||
* @brief 获取总数
|
||
* @return int64 总数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Count() (int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
s.Sql = "SELECT COUNT(*) FROM " + s.TableName
|
||
if s.OptWhere != "" {
|
||
s.Sql += s.OptWhere
|
||
}
|
||
defer s.clearOpt()
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
// 执行SQL语句
|
||
rows, err := s.Conn.Query(s.Sql, s.OptParam...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer rows.Close()
|
||
var count int64
|
||
for rows.Next() {
|
||
rows.Scan(&count)
|
||
}
|
||
return count, err
|
||
}
|
||
|
||
// CountField 获取指定字段的总数
|
||
func (s *Sqlite) CountField(field string) (int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
if field == "" {
|
||
field = "*"
|
||
}
|
||
s.Sql = "SELECT COUNT(" + field + ") FROM " + s.TableName
|
||
if s.OptWhere != "" {
|
||
s.Sql += s.OptWhere
|
||
}
|
||
defer s.clearOpt()
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
// 执行SQL语句
|
||
rows, err := s.Conn.Query(s.Sql, s.OptParam...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer rows.Close()
|
||
var count int64
|
||
for rows.Next() {
|
||
rows.Scan(&count)
|
||
}
|
||
return count, err
|
||
}
|
||
|
||
// CountFields 获取多个字段的统计总数
|
||
func (s *Sqlite) CountFields(fields []string) ([]int64, error) {
|
||
if s.TableName == "" {
|
||
return nil, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
countStrBuilder := strings.Builder{}
|
||
if len(fields) == 0 {
|
||
return nil, errors.New("错误：未指定要统计的字段")
|
||
} else {
|
||
for _, field := range fields {
|
||
if countStrBuilder.Len() > 0 {
|
||
countStrBuilder.WriteByte(',')
|
||
}
|
||
countStrBuilder.WriteString(fmt.Sprintf("COUNT(%s)", field))
|
||
}
|
||
}
|
||
s.Sql = "SELECT " + countStrBuilder.String() + " FROM " + s.TableName
|
||
if s.OptWhere != "" {
|
||
s.Sql += s.OptWhere
|
||
}
|
||
defer s.clearOpt()
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// 执行SQL语句
|
||
rows, err := s.Conn.Query(s.Sql, s.OptParam...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
countList := make([]int64, len(fields))
|
||
tmpList := make([]any, len(fields))
|
||
for i := 0; i < len(fields); i++ {
|
||
tmpList[i] = &countList[i]
|
||
}
|
||
|
||
for rows.Next() {
|
||
rows.Scan(tmpList...)
|
||
}
|
||
return countList, err
|
||
}
|
||
|
||
/**
|
||
* @brief 获取指定字段的值
|
||
* @param field string 字段名
|
||
* @return interface{} 字段值
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Value(field string) (interface{}, error) {
|
||
s.Field([]string{field})
|
||
s.Limit([]int64{1})
|
||
result, err := s.Select()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if len(result) > 0 {
|
||
return result[0][field], nil
|
||
}
|
||
|
||
return nil, errors.New("not found")
|
||
}
|
||
|
||
/**
|
||
* @brief 设置指定字段的值
|
||
* @param field string 字段名
|
||
* @param value interface{} 字段值
|
||
* @return int64 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) SetValue(field string, value interface{}) (int64, error) {
|
||
data := make(map[string]interface{})
|
||
data[field] = value
|
||
return s.Update(data)
|
||
}
|
||
|
||
/**
|
||
* @brief 启动事务
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Begin() (*sql.Tx, error) {
|
||
s.Tx, s.TxErr = s.Conn.Begin()
|
||
return s.Tx, s.TxErr
|
||
}
|
||
|
||
/**
|
||
* @brief 使用事务批量插入数据
|
||
* @param values [][]interface{} 要插入的数据 二维数组,每个数组为一条数据，数组中的值为字段值，顺序与keys对应
|
||
* @param keys []string 字段名
|
||
* @return int 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) InsertBegin(values [][]interface{}, keys []string) (int, []int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, nil, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
var placeholders []string
|
||
// 遍历数据
|
||
for range keys {
|
||
placeholders = append(placeholders, "?")
|
||
}
|
||
|
||
// 启动事务
|
||
s.Tx, s.TxErr = s.Conn.Begin()
|
||
if s.TxErr != nil {
|
||
return 0, nil, s.TxErr
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
// 预编译SQL语句
|
||
s.Sql = "INSERT INTO " + s.TableName + " (" + strings.Join(keys, ",") + ") VALUES (" + strings.Join(placeholders, ",") + ")"
|
||
stmt, err := s.Tx.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, nil, err
|
||
}
|
||
defer func(stmt *sql.Stmt) {
|
||
err := stmt.Close()
|
||
if err != nil {
|
||
println("结束报错", err.Error())
|
||
}
|
||
}(stmt)
|
||
|
||
// 执行SQL语句
|
||
insertIds := make([]int64, 0, len(values))
|
||
ok := 0
|
||
for _, val := range values {
|
||
tmp, err := stmt.Exec(val...)
|
||
if err != nil {
|
||
// 显示回滚任务， 避免数据库事务未完成导致资源占
|
||
err2 := s.Tx.Rollback()
|
||
s.Tx = nil
|
||
if err2 != nil {
|
||
fmt.Println("回滚报错", err2.Error())
|
||
}
|
||
return 0, nil, err
|
||
}
|
||
tmpId, _ := tmp.LastInsertId()
|
||
insertIds = append(insertIds, tmpId)
|
||
ok++
|
||
}
|
||
|
||
// 提交事务
|
||
err = s.Tx.Commit()
|
||
s.Tx = nil
|
||
|
||
return ok, insertIds, err
|
||
}
|
||
|
||
/**
|
||
* @brief 使用事务批量插入数据 并忽略 可能出现的唯一索引错误的问题
|
||
* @param values [][]interface{} 要插入的数据 二维数组,每个数组为一条数据，数组中的值为字段值，顺序与keys对应
|
||
* @param keys []string 字段名
|
||
* @return int 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) InsertOrIgnoreBegin(values [][]interface{}, keys []string) (int, []int64, error) {
|
||
if s.TableName == "" {
|
||
return 0, nil, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
var placeholders []string
|
||
// 遍历数据
|
||
for range keys {
|
||
placeholders = append(placeholders, "?")
|
||
}
|
||
|
||
// 启动事务
|
||
s.Tx, s.TxErr = s.Conn.Begin()
|
||
if s.TxErr != nil {
|
||
return 0, nil, s.TxErr
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
// 预编译SQL语句
|
||
s.Sql = "INSERT OR IGNORE INTO " + s.TableName + " (" + strings.Join(keys, ",") + ") VALUES (" + strings.Join(placeholders, ",") + ")"
|
||
stmt, err := s.Tx.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, nil, err
|
||
}
|
||
defer func(stmt *sql.Stmt) {
|
||
err := stmt.Close()
|
||
if err != nil {
|
||
println("结束报错", err.Error())
|
||
}
|
||
}(stmt)
|
||
|
||
// 执行SQL语句
|
||
insertIds := make([]int64, 0, len(values))
|
||
ok := 0
|
||
for _, val := range values {
|
||
tmp, err := stmt.Exec(val...)
|
||
if err != nil {
|
||
// 显示回滚任务， 避免数据库事务未完成导致资源占
|
||
err2 := s.Tx.Rollback()
|
||
s.Tx = nil
|
||
if err2 != nil {
|
||
fmt.Println("回滚报错", err2.Error())
|
||
}
|
||
return 0, nil, err
|
||
}
|
||
tmpId, _ := tmp.LastInsertId()
|
||
insertIds = append(insertIds, tmpId)
|
||
ok++
|
||
}
|
||
|
||
// 提交事务
|
||
err = s.Tx.Commit()
|
||
s.Tx = nil
|
||
|
||
return ok, insertIds, err
|
||
}
|
||
|
||
/**
|
||
* @brief 使用事务批量插入更新数据
|
||
* @param values [][]interface{} 要插入的数据 二维数组,每个数组为一条数据，数组中的值为字段值，顺序与keys对应
|
||
* @param keys []string 字段名
|
||
* @param uniqueIdx 依据的唯一索引的字段信息，如果数据库中不存在该索引，则会出现报错 如：(date, uri_id)
|
||
* @param updateFormula 更新字段的表达式，如: request = request + excluded.request
|
||
* @return int 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) UpsertBegin(values [][]interface{}, keys []string, uniqueIdx []string, updateFormula string) (int, error) {
|
||
if s.TableName == "" {
|
||
return 0, errors.New("错误：未指定要操作的表名")
|
||
}
|
||
placeholdersBuilder := strings.Builder{}
|
||
var first = true
|
||
for range keys {
|
||
if first {
|
||
placeholdersBuilder.Write([]byte("?"))
|
||
first = false
|
||
} else {
|
||
placeholdersBuilder.Write([]byte(",?"))
|
||
}
|
||
}
|
||
placeholders := placeholdersBuilder.String()
|
||
|
||
// 启动事务
|
||
s.Tx, s.TxErr = s.Conn.Begin()
|
||
if s.TxErr != nil {
|
||
return 0, s.TxErr
|
||
}
|
||
|
||
// 清空参数
|
||
defer s.clearOpt()
|
||
|
||
// 预编译SQL语句
|
||
s.Sql = fmt.Sprintf("INSERT INTO %s (%s) VALUES (%s) ON CONFLICT (%s) DO UPDATE SET %s",
|
||
s.TableName, strings.Join(keys, ","), placeholders, strings.Join(uniqueIdx, ","), updateFormula)
|
||
|
||
stmt, err := s.Tx.Prepare(s.Sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer func(stmt *sql.Stmt) {
|
||
err := stmt.Close()
|
||
if err != nil {
|
||
println("结束报错", err.Error())
|
||
}
|
||
}(stmt)
|
||
|
||
// 执行SQL语句
|
||
ok := 0
|
||
for _, val := range values {
|
||
_, err = stmt.Exec(val...)
|
||
if err != nil {
|
||
// 显示回滚任务， 避免数据库事务未完成导致资源占
|
||
err2 := s.Tx.Rollback()
|
||
s.Tx = nil
|
||
if err2 != nil {
|
||
fmt.Println("回滚报错", err2.Error())
|
||
}
|
||
return 0, err
|
||
}
|
||
ok++
|
||
}
|
||
|
||
// 提交事务
|
||
err = s.Tx.Commit()
|
||
s.Tx = nil
|
||
|
||
return ok, err
|
||
}
|
||
|
||
/**
|
||
* @brief 回滚事务
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Rollback() error {
|
||
err := s.Tx.Rollback()
|
||
s.Tx = nil
|
||
return err
|
||
}
|
||
|
||
/**
|
||
* @brief 提交事务
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Commit() error {
|
||
err := s.Tx.Commit()
|
||
s.Tx = nil
|
||
return err
|
||
}
|
||
|
||
/**
|
||
* @brief 设置JOIN查询
|
||
* @param joinType string JOIN类型
|
||
* @param table string JOIN表名
|
||
* @param on string JOIN条件
|
||
* @param param []interface{} JOIN参数
|
||
* @return *Sqlite
|
||
*/
|
||
func (s *Sqlite) Join(joinType string, table string, on string, param []interface{}) *Sqlite {
|
||
joinType = strings.ToUpper(joinType)
|
||
if joinType != "LEFT" && joinType != "RIGHT" && joinType != "INNER" && joinType != "OUTER" {
|
||
joinType = "LEFT"
|
||
}
|
||
|
||
if s.JoinTable == nil {
|
||
s.JoinTable = make([]string, 0)
|
||
}
|
||
s.JoinTable = append(s.JoinTable, s.PreFix+table)
|
||
|
||
if s.JoinOn == nil {
|
||
s.JoinOn = make([]string, 0)
|
||
}
|
||
s.JoinOn = append(s.JoinOn, on)
|
||
|
||
if s.JoinType == nil {
|
||
s.JoinType = make([]string, 0)
|
||
}
|
||
s.JoinType = append(s.JoinType, joinType)
|
||
|
||
if s.JoinParam == nil {
|
||
s.JoinParam = make([]interface{}, 0)
|
||
}
|
||
|
||
if len(param) > 0 {
|
||
if param[0] != nil {
|
||
s.JoinParam = append(s.JoinParam, param...)
|
||
}
|
||
}
|
||
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置GROUP BY
|
||
* @param groupBy string GROUP BY
|
||
* @return *Sqlite
|
||
*/
|
||
func (s *Sqlite) GroupBy(groupBy string) *Sqlite {
|
||
s.OptGroupBy = " GROUP BY " + groupBy
|
||
return s
|
||
}
|
||
|
||
/**
|
||
* @brief 设置HAVING
|
||
* @param having string HAVING
|
||
* @return *Sqlite
|
||
*/
|
||
func (s *Sqlite) Having(having string) *Sqlite {
|
||
s.OptHaving = " HAVING " + having
|
||
return s
|
||
}
|
||
|
||
// /**
|
||
// * @brief 设置Group分组
|
||
// * @param group string GROUP
|
||
// * @return *Sqlite
|
||
// */
|
||
// func (s *Sqlite) Group(group string) *Sqlite {
|
||
// s.OptGroupBy = " " + group
|
||
// return s
|
||
// }
|
||
|
||
/**
|
||
* @brief 使用SQL语句查询
|
||
* @param sql string SQL语句
|
||
* @param param []interface{} 绑定参数
|
||
* @return []map[string]interface{} 数据集
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Query(sql string, param ...interface{}) ([]map[string]interface{}, error) {
|
||
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
// 执行SQL语句
|
||
rows, err := s.Conn.Query(sql, param...)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
defer rows.Close()
|
||
|
||
columns, err := rows.Columns()
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
count := len(columns)
|
||
values := make([]interface{}, count)
|
||
valuePtrs := make([]interface{}, count)
|
||
|
||
var result []map[string]interface{}
|
||
for rows.Next() {
|
||
for i := 0; i < count; i++ {
|
||
valuePtrs[i] = &values[i]
|
||
}
|
||
|
||
rows.Scan(valuePtrs...)
|
||
|
||
row := make(map[string]interface{})
|
||
for i, col := range columns {
|
||
var v interface{}
|
||
val := values[i]
|
||
|
||
b, ok := val.([]byte)
|
||
if ok {
|
||
v = string(b)
|
||
} else {
|
||
v = val
|
||
}
|
||
|
||
row[col] = v
|
||
}
|
||
|
||
result = append(result, row)
|
||
}
|
||
|
||
return result, nil
|
||
}
|
||
|
||
/**
|
||
* @brief 执行SQL语句
|
||
* @param sql string SQL语句
|
||
* @param param []interface{} 绑定参数
|
||
* @return int64 影响的行数
|
||
* @return error 错误信息
|
||
*/
|
||
func (s *Sqlite) Exec(sql string, param ...interface{}) (int64, error) {
|
||
// 检查参数数量
|
||
err := s.checkParam(s.Sql, s.OptParam)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
// 预编译SQL语句
|
||
stmt, err := s.Conn.Prepare(sql)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
defer stmt.Close()
|
||
|
||
// 执行SQL语句
|
||
res, err := stmt.Exec(param...)
|
||
if err != nil {
|
||
return 0, err
|
||
}
|
||
|
||
// 返回影响的行数
|
||
return res.RowsAffected()
|
||
}
|
||
|
||
/**
|
||
* @brief 清空查询条件
|
||
* @return void
|
||
*/
|
||
func (s *Sqlite) clearOpt() {
|
||
s.OptField = nil
|
||
s.OptGroupBy = ""
|
||
s.OptWhere = ""
|
||
s.OptOrder = ""
|
||
s.OptLimit = ""
|
||
s.OptParam = nil
|
||
s.JoinOn = nil
|
||
s.JoinTable = nil
|
||
s.JoinType = nil
|
||
s.JoinParam = nil
|
||
s.Sql = ""
|
||
}
|
||
|
||
func (s *Sqlite) IsClosed() bool {
|
||
return s.closed
|
||
}
|