package zorm_tdengine_3_test
import (
"context"
"fmt"
"gitee.com/chunanyong/zorm"
_ "github.com/taosdata/driver-go/v3/taosRestful"
"testing"
"time"
)
/**
* @ Test environment
* TDengine Version：3.0.1.5
* Drive Version: github.com/taosdata/driver-go/v2 v3.0.2
* ZORM Version: 1.6.4
* use go module
*
* @ Illustrate：
* 因为TDengine的驱动在对占位符拼接的时候不会对字符串进行单引号拼接。
* 提了issue,官方解答说 "是为兼容之前设计，需要手动加上单引号"
* 所以我们把Sql语句进行处理,
* issues地址:https://github.com/taosdata/TDengine/issues/15393
*
* @ Official website: www.zorm.cn
* @ Project address: https://gitee.com/chunanyong/zorm
* @ Tdengine Incoming address: https://github.com/taosdata/awesome-tdengine
*
*/
//SuperTable nme and Database name.
//库名与超级表名称
const (
DBName = "ZORM"
SuperTableBinary = "SuperTableBinary"
SuperTableDouble = "SuperTableDouble"
SuperTableBigInt = "SuperTableBigInt"
SuperTableBool = "SuperTableBool"
)
type Demo struct {
zorm.EntityStruct
Time time.Time `column:"time"`
Value float64 `column:"current"`
TableName string
}
type TestBinary struct {
zorm.EntityStruct
Time time.Time `column:"time"`
Value string `column:"current"`
TableName string
}
func (entity *Demo) GetTableName() string {
return entity.TableName
}
func (entity *Demo) GetPKColumnName() string {
return ""
}
func (entity *TestBinary) GetTableName() string {
return entity.TableName
}
func (entity *TestBinary) GetPKColumnName() string {
return ""
}
var dbDao *zorm.DBDao
var ctx = context.Background()
// Initialize connection
// 初始化连接
// 本次只展示配合客服端的官方驱动包
func init() {
dbDaoConfig := zorm.DataSourceConfig{
//DSN 数据库的连接字符串
DSN: "root:taosdata@http(127.0.0.1:6041)/",
//数据库驱动名称:mysql,postgres,oci8,sqlserver,sqlite3,clickhouse,dm,kingbase,aci 和Dialect对应,处理数据库有多个驱动
//sql.Open(DriverName,DSN) DriverName就是驱动的sql.Open第一个字符串参数,根据驱动实际情况获取
DriverName: "taosRestful",
//数据库方言:mysql,postgresql,oracle,mssql,sqlite,clickhouse,dm,kingbase,shentong 和 DriverName 对应,处理数据库有多个驱动
Dialect: "tdengine",
//MaxOpenConns 数据库最大连接数 默认50
MaxOpenConns: 10,
//MaxIdleConns 数据库最大空闲连接数 默认50
MaxIdleConns: 10,
//ConnMaxLifetimeSecond 连接存活秒时间. 默认600(10分钟)后连接被销毁重建.避免数据库主动断开连接,造成死连接.MySQL默认wait_timeout 28800秒(8小时)
ConnMaxLifetimeSecond: 600,
DisableTransaction: true, // 禁用全局事务
// TDengineInsertsColumnName TDengine批量insert语句中是否有列名.默认false没有列名,插入值和数据库列顺序保持一致,减少语句长度
// TDengineInsertsColumnName :false,
}
dbDao, _ = zorm.NewDBDao(&dbDaoConfig)
}
// Test_Create Create DB and table.
// Test_Create 建库建st表
func Test_create_st(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
//finder.InjectionCheck = false
finder.Append(fmt.Sprintf("create database if not exists %s precision ? keep ?", DBName), "us", 365)
_, err := zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create database, err: %v", err)
}
finder = zorm.NewFinder()
// 艰难取舍
// 因为会对string进行加单引号 所以在创建数据类型的时候请不要使用占位符 (毕竟创建超级表的地方不算多)
finder.Append(fmt.Sprintf("CREATE STABLE if not exists %s.%s (Time TIMESTAMP, current Bool) TAGS (model_id BINARY(64), model_name BINARY(64))", DBName, SuperTableBool))
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create table, err: %v", err)
}
//如果使用循环创建方式可以这样操作
tables := make(map[string]string, 3)
tables[SuperTableBinary] = "BINARY(64)"
tables[SuperTableBigInt] = "BIGINT"
tables[SuperTableDouble] = "Double"
for tableName, tableType := range tables {
sql := fmt.Sprintf("create table if not exists %s.%s (Time TIMESTAMP, current %s) TAGS (model_id BINARY(64), model_name BINARY(64))", DBName, tableName, tableType)
finder = zorm.NewFinder()
finder.Append(sql)
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create table, err: %v", err)
}
}
//或者
//tables := make(map[string]string, 3)
//tables[SuperTableBinary] = "BINARY(64)"
//tables[SuperTableBigInt] = "BIGINT"
//tables[SuperTableDouble] = "Double"
//for tableName, tableType := range tables {
// sql := fmt.Sprintf("create table if not exists ?.? (Time TIMESTAMP, Value %s) TAGS (model_id BINARY(64), model_name BINARY(64))", tableType)
// finder = zorm.NewFinder()
// finder.Append(sql, DBName, tableName)
// _, err = zorm.UpdateFinder(ctx, finder)
// if err != nil {
// t.Errorf("failed to create table, err: %v", err)
// }
//}
}
//创建子表
//Create tables
func Test_create_tables(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm001", DBName, SuperTableBigInt), "001", "zorm")
_, err := zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableBigint, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm002", DBName, SuperTableDouble), "002", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableDouble, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm003", DBName, SuperTableBinary), "003", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableBinary, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm004", DBName, SuperTableBool), "004", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableBool, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm005", DBName, SuperTableBinary), "005", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableBinary, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm006", DBName, SuperTableDouble), "006", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableDouble, err: %v", err)
}
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf(`create table if not exists %s.%s using %s.%s TAGS(?,?) `, DBName, "zorm007", DBName, SuperTableBigInt), "007", "zorm")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to create SuperTableBigInt, err: %v", err)
}
}
//结构体插入单条
func Test_inset_struct(t *testing.T) {
defer dbDao.CloseDB()
//因为td选择库会丢失 （实际使用中会报未选择库）
//在实际应用中可能会有跨库操作的需求 因此需要自己添加上库名前缀
//库名.表名
demo := Demo{Time: time.Now(), Value: 927, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
num, err := zorm.Insert(ctx, &demo)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("插入%d条", num)
}
//Test_bulk_inset_struct 结构体插入单条
func Test_bulk_inset_struct(t *testing.T) {
/**
插入数据时请确保使用的时候同一结构体
不支持 insert into tableName1 values(v1,v2) tableName2 values(v1,v2,v3)
请确保结构体属性与表字段 顺序一致很重要
考虑到插入长度限制的效率问题 我们将sql 拼接成了
insert into tableName1 values(?,?) tableName2 values(?,?)
并没有拼成 insert into tableName (column1,column2) values (?,?)
这样能保证批量插入的效率
所以请确保 结构体属性与表字段的顺序一致
*/
defer dbDao.CloseDB()
var demos = make([]zorm.IEntityStruct, 0)
//相同结构的的子表（同一超级表下子表,如果不是必须保证类型一致）
//tableName 是可以替换的 demo定义的是超级表结构
demo1 := Demo{Time: time.Now().Add(1 * time.Second), Value: 188, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
demo2 := Demo{Time: time.Now().Add(2 * time.Second), Value: 189, TableName: fmt.Sprintf("%s.%s", DBName, "zorm006")}
demo3 := Demo{Time: time.Now().Add(3 * time.Second), Value: 190, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
demo4 := Demo{Time: time.Now().Add(4 * time.Second), Value: 191, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
demo5 := Demo{Time: time.Now().Add(5 * time.Second), Value: 192, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
demo6 := Demo{Time: time.Now().Add(6 * time.Second), Value: 193, TableName: fmt.Sprintf("%s.%s", DBName, "zorm002")}
demo7 := Demo{Time: time.Now().Add(7 * time.Second), Value: 194, TableName: fmt.Sprintf("%s.%s", DBName, "zorm006")}
demos = append(demos, &demo1, &demo2, &demo3, &demo4, &demo5, &demo6, &demo7) //注意需要指针传递
num, err := zorm.InsertSlice(ctx, demos)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("插入%d条", num)
}
//Test_insert_map 插入单个map
func Test_insert_map(t *testing.T) {
defer dbDao.CloseDB()
entityMap := zorm.NewEntityMap(fmt.Sprintf("%s.%s", DBName, "zorm005"))
entityMap.PkColumnName = "" //不写此行也可以 但是会打印log
entityMap.Set("time", time.Now())
entityMap.Set("current", "hello")
num, err := zorm.InsertEntityMap(ctx, entityMap)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("插入%d条", num)
entityMap = zorm.NewEntityMap(fmt.Sprintf("%s.%s", DBName, "zorm001"))
entityMap.PkColumnName = ""
entityMap.Set("time", time.Now())
entityMap.Set("current", 365)
num, err = zorm.InsertEntityMap(ctx, entityMap)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("插入%d条", num)
}
//Test_use_updateFinder 使用updateFinder 进行插入
func Test_use_updateFinder(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
finder.Append(fmt.Sprintf("insert into %s.%s values (?,?) ", DBName, "zorm001"), time.Now(), 100)
_, err := zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to insert zorm001, err: %v", err)
}
finder = zorm.NewFinder()
sql := fmt.Sprintf("insert into %s.%s values (%s,?)", DBName, "zorm005", "now")
finder.Append(sql, "ZORM")
_, err = zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("failed to insert zorm005, err: %v", err)
}
}
//查询单条到结构体
func Test_query(t *testing.T) {
defer dbDao.CloseDB()
var demo Demo
finder := zorm.NewFinder()
//请确保查询结构只有一条
finder.Append(fmt.Sprintf("select time ,current from %s.%s where current = ?", DBName, "zorm002"), 188)
_, err := zorm.QueryRow(ctx, finder, &demo)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("time:%v value :%v", demo.Time, demo.Value)
var testBinary TestBinary
finder = zorm.NewFinder()
finder.Append(fmt.Sprintf("select time ,current from %s.%s where current = ?", DBName, "zorm005"), "ZORM")
_, err = zorm.QueryRow(ctx, finder, &testBinary)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("time:%v value :%v", testBinary.Time, testBinary.Value)
}
//Test_Query_rows 查询多条到结构体
func Test_query_rows(t *testing.T) {
defer dbDao.CloseDB()
var demos = make([]Demo, 0)
finder := zorm.NewFinder()
finder.Append(fmt.Sprintf("select time ,current from %s.%s order by time desc", DBName, "zorm002"))
page := zorm.NewPage()
page.PageSize = 2
page.PageNo = 1
// 如果是全部查询传nil
err := zorm.Query(ctx, finder, &demos, page)
//err := zorm.Query(ctx, finder, &demos, nil)
if err != nil {
t.Errorf("%v", err)
}
for _, v := range demos {
t.Logf("time:%v value :%v", v.Time, v.Value)
}
}
//Test_query_map 查询单条到map
func Test_query_map(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
finder.Append(fmt.Sprintf("select time ,current from %s.%s where current = ?", DBName, "zorm002"), 188)
demos, err := zorm.QueryRowMap(ctx, finder)
if err != nil {
t.Errorf("%v", err)
}
t.Logf("time:%v value :%v", demos["time"], demos["value"])
}
//Test_query_maps查询多条到map
func Test_query_maps(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
finder.Append(fmt.Sprintf("select time ,current from %s.%s order by time desc", DBName, "zorm002"))
demos, err := zorm.QueryMap(ctx, finder, nil)
if err != nil {
t.Errorf("%v", err)
}
for _, v := range demos {
t.Logf("time:%v value :%v", v["time"], v["value"])
}
}
// Test_drop_database 删除数据库
func Test_drop_database(t *testing.T) {
defer dbDao.CloseDB()
finder := zorm.NewFinder()
finder.Append("drop database zorm")
_, err := zorm.UpdateFinder(ctx, finder)
if err != nil {
t.Errorf("%v", err)
}
}
zorm_tdengine_3.0_test