test/sqlite/sqlite_test.go

package sqlite_test

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"math/rand"
	"reflect"
	"runtime"
	"strconv"
	"strings"
	"testing"
	"time"

	"gitee.com/wecisecode/util/cast"
)

func TestSQLiteBasic(t *testing.T) {
	// sqlite 内存表结构中字段类型只有 int64 float64 string, string没有长度限制
	// create table 中指定的类型没有意义，不影响数据插入
	// 实际数据类型是 由 第一次插入的数据的类型 决定的
	// 查询时，后续数据会强制转换为该类型，转不过去会报错，如将非数字字符串转换为数值
	mdb, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	stmt, err := mdb.Prepare(`CREATE TABLE TEST(
		ID             INT     PRIMARY KEY     NOT NULL,
		NAME           TEXT                    NOT NULL,
		VALUE          REAL                    NOT NULL,
		INTVALUE       INT                     NOT NULL,
		DESC           CHAR(1)
	 )`)
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rslt, err := stmt.Exec()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
	stmt, err = mdb.Prepare("insert into test(id,name,intvalue,value,desc) values(?,?,?,?,?)")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	for i := 1; i < 10; i++ {
		id := string([]byte{})
		for n := 0; n < 16; n++ {
			id = id + string('a'+byte(n+i-1))
		}
		name := int64(30 + i)
		floatvalue := int64(12345)
		var intvalue interface{}
		intvalue = 1.2345 * float32(i)
		if i%3 == 0 {
			intvalue = "--12345--"
		} else if i%3 == 2 {
			intvalue = 123450 + i
		}
		desc := string("........................................................................................")
		rslt, err = stmt.Exec(id, name, intvalue, floatvalue, desc)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
	}
	stmt, err = mdb.Prepare("select * from test")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rows, err := stmt.Query()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	defer func() { _ = rows.Close() }()
	cols, err := rows.Columns()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	types := make([]reflect.Type, len(colTypes))
	for i, tp := range colTypes {
		st := tp.ScanType()
		if st == nil {
			continue
		}
		types[i] = st
	}
	for rows.Next() {
		values := make([]interface{}, len(cols))
		for i := range values {
			values[i] = reflect.New(types[i]).Interface()
		}
		if err = rows.Scan(values...); err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		m := make(map[string]interface{})
		for i, v := range values {
			if v == nil {
				//values[i] = nil
				m[colTypes[i].Name()] = nil
			} else {
				n, ok := colTypes[i].Length()
				m[colTypes[i].Name()] = fmt.Sprint(types[i], "[", n, "]", ok, ":", reflect.ValueOf(v).Elem().Interface())
			}
		}
		bs, _ := json.MarshalIndent(m, "", "  ")
		println("values", string(bs))
	}
}

// GOMAXPROCS  1
// Fields count  104
// Empty Fields count  100
// VarFields data length  32
// Data count  10000
// sqlite create table use time  1.6279ms
// sqlite create index use time  0s
// generate maps  10000  use time  1.1913207s
// sqlite insert with transaction  10000  use time  1.9912916s
// sqlite insert with transaction  10000  use time  1.9434772s
// sqlite insert immediately prepare  10000  use time  2.0023365s
// sqlite insert immediately  10000  use time  6.1372897s
// sqlite select query all  use time  746.4µs
// sqlite fetch data  40000  use time  9.9554222s
// put id-data maps  10000  use time  17.2197ms
// fetch maps data ids  10000  use time  6.8757ms
// fetch maps data  10000  use time  1.0318ms
//
// GOMAXPROCS  4
// Fields count  104
// Empty Fields count  100
// VarFields data length  32
// Data count  10000
// sqlite create table use time  2.6123ms
// sqlite create index use time  0s
// generate maps  10000  use time  932.7881ms
// sqlite insert with transaction  10000  use time  1.9001722s
// sqlite insert with transaction  10000  use time  1.887692s
// sqlite insert immediately prepare  10000  use time  2.0469167s
// sqlite insert immediately  10000  use time  3.6564142s
// sqlite select query all  use time  432.8µs
// sqlite fetch data  40000  use time  8.4194779s
// put id-data maps  10000  use time  15.1188ms
// fetch maps data ids  10000  use time  5.1524ms
// fetch maps data  10000  use time  167.9µs
//
// GOMAXPROCS  8
// Fields count  104
// Empty Fields count  100
// VarFields data length  32
// Data count  10000
// sqlite create table use time  2.1335ms
// sqlite create index use time  0s
// generate maps  10000  use time  874.8548ms
// sqlite insert with transaction  10000  use time  1.8495912s
// sqlite insert with transaction  10000  use time  1.773086s
// sqlite insert immediately prepare  10000  use time  1.9181581s
// sqlite insert immediately  10000  use time  3.2221547s
// sqlite select query all  use time  235.7µs
// sqlite fetch data  40000  use time  7.541716s
// put id-data maps  10000  use time  14.8666ms
// fetch maps data ids  10000  use time  5.7673ms
// fetch maps data  10000  use time  81.5µs
func TestSQLiteProfmance(t *testing.T) {
	runtime.GOMAXPROCS(4)
	println("GOMAXPROCS ", runtime.GOMAXPROCS(0))
	varfieldscount := 100
	println("Fields count ", varfieldscount+4)
	emptyfieldscount := 100
	println("Empty Fields count ", emptyfieldscount)
	vardatalength := 32
	println("VarFields data length ", vardatalength)
	datacount := 10000
	println("Data count ", datacount)
	st := time.Now()
	mdb, err := sql.Open("sqlite", ":memory:")
	// mdb, err := sql.Open("sqlite", "file::memory:?cache=shared")
	if err != nil {
		println("err", fmt.Sprint(err))
	}
	updatefields := "name=?,value=?,desc=?"
	fields := "id,name,value,desc"
	askmarks := "?,?,?,?"
	for i := 1; i <= varfieldscount; i++ {
		fields += ",var" + strconv.Itoa(i)
		askmarks += ",?"
		updatefields += ",var" + strconv.Itoa(i) + "=?"
	}
	allfields := fields
	for i := varfieldscount + 1; i <= varfieldscount+emptyfieldscount; i++ {
		allfields += ",var" + strconv.Itoa(i)
	}
	stmt, err := mdb.Prepare("create table if not exists test(" + allfields + ")")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rslt, err := stmt.Exec()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	if rslt == nil {
		println("sqlite init table return nil")
	} else {
		id, err := rslt.LastInsertId()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		ra, err := rslt.RowsAffected()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		if id != 0 || ra != 0 {
			println("sqlite init table return ", id, ra)
		}
	}
	println("sqlite create table use time ", time.Since(st).String())
	st = time.Now()
	stmt, err = mdb.Prepare("create index test_id_1 on test (id)")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rslt, err = stmt.Exec()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	if rslt == nil {
		println("sqlite init table index return nil")
	} else {
		id, err := rslt.LastInsertId()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		ra, err := rslt.RowsAffected()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		if id != 0 || ra != 0 {
			println("sqlite init table index return ", id, ra)
		}
	}
	println("sqlite create index use time ", time.Since(st).String())
	st = time.Now()
	data := []map[string]interface{}{}
	for i := 1; i <= datacount; i++ {
		m := map[string]interface{}{}
		m["id"] = i
		m["name"] = fmt.Sprint("name", i)
		m["value"] = 1.23456789 * float64(i)
		m["desc"] = strings.Repeat(fmt.Sprint("desc", i, "."), 10)
		for vi := 1; vi <= varfieldscount; vi++ {
			bs := make([]byte, vardatalength+rand.Intn(10)-5)
			for bsi := 0; bsi < len(bs); bsi++ {
				bs[bsi] = byte(32 + rand.Intn(95))
			}
			m["var"+strconv.Itoa(vi)] = string(bs)
		}
		data = append(data, m)
		// if i%10000 == 0 {
		// 	println("generate maps ", i, " use time ", time.Since(st).String())
		// }
	}
	println("generate maps ", datacount, " use time ", time.Since(st).String())
	st = time.Now()
	{
		mdb.Exec("PRAGMA synchronous = OFF") // 这个跟写文件相关，内存模式没用
		mdb.Exec("begin")
		stmt, err = mdb.Prepare("insert into test(" + fields + ") values(" + askmarks + ")")
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}

		for _, d := range data {
			args := []interface{}{
				d["id"],
				d["name"],
				d["value"],
				d["desc"]}
			for vi := 1; vi <= varfieldscount; vi++ {
				args = append(args, d["var"+strconv.Itoa(vi)])
			}
			rslt, err = stmt.Exec(args...)
			if err != nil {
				println("err", fmt.Sprint(err))
			}
			// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
			// if (i+1)%10000 == 0 {
			// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
			// }
		}
		mdb.Exec("commit")
		mdb.Exec("PRAGMA synchronous = NORMAL") // 这个跟写文件相关，内存模式没用
		println("sqlite insert with transaction ", len(data), " use time ", time.Since(st).String())
	}
	st = time.Now()
	tx, err := mdb.Begin()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	stmt, err = tx.Prepare("insert into test(" + fields + ") values(" + askmarks + ")")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}

	for _, d := range data {
		args := []interface{}{
			d["id"],
			d["name"],
			d["value"],
			d["desc"]}
		for vi := 1; vi <= varfieldscount; vi++ {
			args = append(args, d["var"+strconv.Itoa(vi)])
		}
		rslt, err = stmt.Exec(args...)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
		// if (i+1)%10000 == 0 {
		// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
		// }
	}
	tx.Commit()
	println("sqlite insert with transaction ", len(data), " use time ", time.Since(st).String())

	st = time.Now()
	stmt, err = mdb.Prepare("insert into test(" + fields + ") values(" + askmarks + ")")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}

	for _, d := range data {
		args := []interface{}{
			d["id"],
			d["name"],
			d["value"],
			d["desc"]}
		for vi := 1; vi <= varfieldscount; vi++ {
			args = append(args, d["var"+strconv.Itoa(vi)])
		}
		rslt, err = stmt.Exec(args...)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
		// if (i+1)%10000 == 0 {
		// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
		// }
	}
	println("sqlite insert immediately prepare ", len(data), " use time ", time.Since(st).String())

	st = time.Now()

	for _, d := range data {
		args := []interface{}{
			d["id"],
			d["name"],
			d["value"],
			d["desc"]}
		for vi := 1; vi <= varfieldscount; vi++ {
			args = append(args, d["var"+strconv.Itoa(vi)])
		}
		sql := "insert into test(" + fields + ") values("
		for i, arg := range args {
			if i > 0 {
				sql += ","
			}
			switch a := arg.(type) {
			case string:
				sql += "'" + strings.ReplaceAll(a, "'", "''") + "'"
			default:
				sql += fmt.Sprint(arg)
			}
		}
		sql += ")"
		rslt, err = mdb.Exec(sql)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
		// if (i+1)%10000 == 0 {
		// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
		// }
	}
	println("sqlite insert immediately ", len(data), " use time ", time.Since(st).String())

	st = time.Now()
	upsertSql := `BEGIN TRANSACTION;
DELETE FROM test WHERE id=?;
INSERT INTO test (` + fields + `) VALUES (` + askmarks + `);
COMMIT;`
	upsertstmt, err := mdb.Prepare(upsertSql)
	if err != nil {
		println("err", fmt.Sprint(err))
	}

	for _, d := range data {
		args := []interface{}{
			d["id"],
			d["id"],
			d["name"],
			d["value"],
			d["desc"]}
		for vi := 1; vi <= varfieldscount; vi++ {
			args = append(args, d["var"+strconv.Itoa(vi)])
		}
		rslt, err = upsertstmt.Exec(args...)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
		// if (i+1)%10000 == 0 {
		// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
		// }
	}
	println("sqlite upsert(del+insert) immediately prepare ", len(data), " use time ", time.Since(st).String())

	st = time.Now()
	updateSql := `UPDATE test SET ` + updatefields + ` WHERE id=?`
	updatestmt, err := mdb.Prepare(updateSql)
	if err != nil {
		println("err", fmt.Sprint(err))
	}

	for _, d := range data {
		args := []interface{}{
			d["name"],
			d["value"],
			d["desc"]}
		for vi := 1; vi <= varfieldscount; vi++ {
			args = append(args, d["var"+strconv.Itoa(vi)])
		}
		args = append(args, d["id"])
		rslt, err = updatestmt.Exec(args...)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
		// if (i+1)%10000 == 0 {
		// 	println("sqlite insert ", (i + 1), " use time ", time.Since(st).String())
		// }
	}
	println("sqlite update immediately prepare ", len(data), " use time ", time.Since(st).String())

	st = time.Now()
	stmt, err = mdb.Prepare("select * from test")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rows, err := stmt.Query()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	println("sqlite select query all", " use time ", time.Since(st).String())
	defer func() { _ = rows.Close() }()
	cols, err := rows.Columns()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	types := make([]reflect.Type, len(colTypes))
	for i, tp := range colTypes {
		st := tp.ScanType()
		if st == nil {
			continue
		}
		types[i] = st
	}
	data = []map[string]interface{}{}
	for rows.Next() {
		values := make([]interface{}, len(cols))
		for i := range values {
			if types[i] == nil {
				values[i] = &[]byte{}
			} else {
				values[i] = reflect.New(types[i]).Interface()
			}
		}
		if err = rows.Scan(values...); err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		m := make(map[string]interface{})
		for i, v := range values {
			if v == nil {
				//values[i] = nil
				m[colTypes[i].Name()] = nil
			} else {
				n, ok := colTypes[i].Length()
				m[colTypes[i].Name()] = fmt.Sprint(types[i], "[", n, "]", ok, ":", reflect.ValueOf(v).Elem().Interface())
			}
		}
		data = append(data, m)
		// if len(data)%10000 == 0 {
		// 	println("sqlite fetch data ", len(data), " use time ", time.Since(st).String())
		// }
	}
	println("sqlite fetch data ", len(data), " use time ", time.Since(st).String())
	st = time.Now()
	memdataset := map[string]map[string]interface{}{}
	for _, d := range data {
		id := cast.ToString(d["id"])
		memdataset[id] = d
	}
	println("put id-data maps ", len(memdataset), " use time ", time.Since(st).String())
	st = time.Now()
	ids := []string{}
	for id, d := range memdataset {
		if id == d["id"] {
			ids = append(ids, id)
		}
	}
	println("fetch maps data ids ", len(ids), " use time ", time.Since(st).String())
	st = time.Now()
	data = []map[string]interface{}{}
	for _, id := range ids {
		d := memdataset[id]
		data = append(data, d)
	}
	println("fetch maps data ", len(data), " use time ", time.Since(st).String())
}

func TestSQLiteJSON(t *testing.T) {
	// sqlite 内存表结构中字段类型只有 int64 float64 string, string没有长度限制
	// create table 中指定的类型没有意义，不影响数据插入
	// 实际数据类型是 由 第一次插入的数据的类型 决定的
	// 查询时，后续数据会强制转换为该类型，转不过去会报错，如将非数字字符串转换为数值
	mdb, err := sql.Open("sqlite", ":memory:")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	stmt, err := mdb.Prepare(`CREATE TABLE TEST(
		ID             INT     PRIMARY KEY     NOT NULL,
		NAME           TEXT                    NOT NULL,
		VALUE          REAL                    NOT NULL,
		INTVALUE       INT                     NOT NULL,
		DESC           CHAR(1)
	 )`)
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rslt, err := stmt.Exec()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
	stmt, err = mdb.Prepare("insert into test(id,name,intvalue,value,desc) values(?,?,?,?,?)")
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	for i := 1; i < 10; i++ {
		id := string([]byte{})
		for n := 0; n < 16; n++ {
			id = id + string('a'+byte(n+i-1))
		}
		name := int64(30 + i)
		floatvalue := 12345 + (i-1)*10000
		var intvalue interface{}
		intvalue = 1.2345 * float32(i)
		if i%3 == 1 {
			intvalue = "--12345--"
		} else if i%3 == 2 {
			intvalue = 123450 + i
		}
		bs, _ := json.MarshalIndent(map[string]interface{}{
			"value": floatvalue,
		}, "", "  ")
		desc := string(bs)
		rslt, err = stmt.Exec(id, name, intvalue, floatvalue, desc)
		if err != nil {
			println("err", fmt.Sprint(err))
		}
		println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
	}
	{
		stmt, err = mdb.Prepare(`select * from test`)
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		rows, err := stmt.Query()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		defer func() { _ = rows.Close() }()
		cols, err := rows.Columns()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		colTypes, err := rows.ColumnTypes()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		types := make([]reflect.Type, len(colTypes))
		for i, tp := range colTypes {
			st := tp.ScanType()
			if st == nil {
				continue
			}
			types[i] = st
		}
		for rows.Next() {
			values := make([]interface{}, len(cols))
			for i := range values {
				values[i] = reflect.New(types[i]).Interface()
			}
			if err = rows.Scan(values...); err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			m := make(map[string]interface{})
			for i, v := range values {
				if v == nil {
					//values[i] = nil
					m[colTypes[i].Name()] = nil
				} else {
					n, ok := colTypes[i].Length()
					m[colTypes[i].Name()] = fmt.Sprint(types[i], "[", n, "]", ok, ":", reflect.ValueOf(v).Elem().Interface())
				}
			}
			bs, _ := json.MarshalIndent(m, "", "  ")
			println("values", string(bs))
		}
	}
	stmt, err = mdb.Prepare(`select json_group_array(json_object(
			'id', id, 
			'name', name,
			'value', value)) as json
		from test
		where json_extract(desc, '$.value') > 20000 and json_extract(desc, '$.value') < 50000`)
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	rows, err := stmt.Query()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	defer func() { _ = rows.Close() }()
	cols, err := rows.Columns()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	colTypes, err := rows.ColumnTypes()
	if err != nil {
		println("err", fmt.Sprint(err))
		return
	}
	types := make([]reflect.Type, len(colTypes))
	for i, tp := range colTypes {
		st := tp.ScanType()
		if st == nil {
			continue
		}
		types[i] = st
	}
	for rows.Next() {
		values := make([]interface{}, len(cols))
		for i := range values {
			values[i] = reflect.New(types[i]).Interface()
		}
		if err = rows.Scan(values...); err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		m := make(map[string]interface{})
		for i, v := range values {
			if v == nil {
				//values[i] = nil
				m[colTypes[i].Name()] = nil
			} else {
				n, ok := colTypes[i].Length()
				m[colTypes[i].Name()] = fmt.Sprint(types[i], "[", n, "]", ok, ":", reflect.ValueOf(v).Elem().Interface())
			}
		}
		bs, _ := json.MarshalIndent(m, "", "  ")
		println("values", string(bs))
	}
}

func TestSQLiteProfmanceMultiTable(t *testing.T) {
	runtime.GOMAXPROCS(4)
	println("GOMAXPROCS ", runtime.GOMAXPROCS(0))
	tablecount := 10000
	println("Tables count ", tablecount)
	varfieldscount := 100
	println("VarFields count ", varfieldscount+4)
	emptyfieldscount := 100
	println("EmptyFields count ", emptyfieldscount)
	vardatalength := 32
	println("VarFields data length ", vardatalength)
	tableindexcount := 100
	println("TableIndex count ", tableindexcount)
	datacount := 10000
	println("Data count ", datacount)
	mdb, err := sql.Open("sqlite", ":memory:")
	// mdb, err := sql.Open("sqlite", "file::memory:?cache=shared")
	if err != nil {
		println("err", fmt.Sprint(err))
	}
	st := time.Now()
	for ti := 1; ti < tablecount; ti++ {
		tablename := fmt.Sprint("test", ti)
		fields := []string{"id", "name", "value", "desc"}
		varfields := strings.Join(fields, ",")
		askmarks := "?" + strings.Repeat(",?", len(fields)-1)
		for i := 1; i <= varfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			varfields += ",var" + strconv.Itoa(i)
			askmarks += ",?"
		}
		allfields := varfields
		for i := varfieldscount + 1; i <= varfieldscount+emptyfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			allfields += ",var" + strconv.Itoa(i)
		}
		st := time.Now()
		stmt, err := mdb.Prepare("create table if not exists " + tablename + "(" + allfields + ")")
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		rslt, err := stmt.Exec()
		if err != nil {
			println("err", fmt.Sprint(err))
			return
		}
		if rslt == nil {
			println("sqlite init table return nil")
		} else {
			id, err := rslt.LastInsertId()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			ra, err := rslt.RowsAffected()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			if id != 0 || ra != 0 {
				println("sqlite init table return ", id, ra)
			}
		}
		println("sqlite create table", tablename, "use time ", time.Since(st).String())
	}
	println("sqlite create table", tablecount, "done, use time ", time.Since(st).String())
	st = time.Now()
	for ti := 1; ti < tablecount; ti++ {
		tablename := fmt.Sprint("test", ti)
		fields := []string{"id", "name", "value", "desc"}
		varfields := strings.Join(fields, ",")
		askmarks := "?" + strings.Repeat(",?", len(fields)-1)
		for i := 1; i <= varfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			varfields += ",var" + strconv.Itoa(i)
			askmarks += ",?"
		}
		allfields := varfields
		for i := varfieldscount + 1; i <= varfieldscount+emptyfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			allfields += ",var" + strconv.Itoa(i)
		}
		st = time.Now()
		for i, field := range fields {
			if i > tableindexcount {
				break
			}
			stmt, err := mdb.Prepare("create index " + tablename + "_" + field + " on " + tablename + " (" + field + ")")
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			rslt, err := stmt.Exec()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			if rslt == nil {
				println("sqlite init table index return nil")
			} else {
				id, err := rslt.LastInsertId()
				if err != nil {
					println("err", fmt.Sprint(err))
					return
				}
				ra, err := rslt.RowsAffected()
				if err != nil {
					println("err", fmt.Sprint(err))
					return
				}
				if id != 0 || ra != 0 {
					println("sqlite init table index return ", id, ra)
				}
			}
		}
		println("sqlite create index for table "+tablename+" use time ", time.Since(st).String())
	}
	println("sqlite create table index", tablecount, "done, use time ", time.Since(st).String())
	st = time.Now()
	data := []map[string]interface{}{}
	{
		st = time.Now()
		for i := 1; i <= datacount; i++ {
			m := map[string]interface{}{}
			m["id"] = i
			m["name"] = fmt.Sprint("name", i)
			m["value"] = 1.23456789 * float64(i)
			m["desc"] = strings.Repeat(fmt.Sprint("desc", i, "."), 10)
			for vi := 1; vi <= varfieldscount; vi++ {
				bs := make([]byte, vardatalength+rand.Intn(10)-5)
				for bsi := 0; bsi < len(bs); bsi++ {
					bs[bsi] = byte(32 + rand.Intn(95))
				}
				m["var"+strconv.Itoa(vi)] = string(bs)
			}
			data = append(data, m)
			if i%1000 == 0 {
				println("generate maps ", i, " use time ", time.Since(st).String())
			}
		}
		println("generate maps ", datacount, " use time ", time.Since(st).String())
		st = time.Now()
		memdataset := map[string]map[string]interface{}{}
		for _, d := range data {
			id := cast.ToString(d["id"])
			memdataset[id] = d
		}
		println("put id-data maps ", len(memdataset), " use time ", time.Since(st).String())
		st = time.Now()
		ids := []string{}
		for id, d := range memdataset {
			if id == cast.ToString(d["id"]) {
				ids = append(ids, id)
			}
		}
		println("fetch maps data ids ", len(ids), " use time ", time.Since(st).String())
		st = time.Now()
		data = []map[string]interface{}{}
		for _, id := range ids {
			d := memdataset[id]
			data = append(data, d)
		}
		println("fetch maps data ", len(data), " use time ", time.Since(st).String())
	}
	for ti := 1; ti < tablecount; ti++ {
		tablename := fmt.Sprint("test", ti)
		fields := []string{"id", "name", "value", "desc"}
		varfields := strings.Join(fields, ",")
		askmarks := "?" + strings.Repeat(",?", len(fields)-1)
		for i := 1; i <= varfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			varfields += ",var" + strconv.Itoa(i)
			askmarks += ",?"
		}
		allfields := varfields
		for i := varfieldscount + 1; i <= varfieldscount+emptyfieldscount; i++ {
			fields = append(fields, "var"+strconv.Itoa(i))
			allfields += ",var" + strconv.Itoa(i)
		}
		{ // insert
			st = time.Now()
			stmt, err := mdb.Prepare("insert into " + tablename + "(" + varfields + ") values(" + askmarks + ")")
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			i := 0
			for _, d := range data {
				i = i + 1
				args := []interface{}{
					d["id"],
					d["name"],
					d["value"],
					d["desc"]}
				for vi := 1; vi <= varfieldscount; vi++ {
					args = append(args, d["var"+strconv.Itoa(vi)])
				}
				_, err := stmt.Exec(args...)
				if err != nil {
					println("err", fmt.Sprint(err))
				}
				// println("rslt", fmt.Sprint(rslt.LastInsertId()), fmt.Sprint(rslt.RowsAffected()))
				if i%1000 == 0 {
					println("sqlite insert ", i, " use time ", time.Since(st).String())
				}
			}
			println("sqlite insert ", tablename, len(data), " use time ", time.Since(st).String())
		}
		{ // select
			st = time.Now()
			stmt, err := mdb.Prepare("select " + varfields + " from " + tablename)
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			rows, err := stmt.Query()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			println("sqlite select query all", " use time ", time.Since(st).String())
			defer func() { _ = rows.Close() }()
			cols, err := rows.Columns()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			colTypes, err := rows.ColumnTypes()
			if err != nil {
				println("err", fmt.Sprint(err))
				return
			}
			types := make([]reflect.Type, len(colTypes))
			for i, tp := range colTypes {
				st := tp.ScanType()
				if st == nil {
					continue
				}
				types[i] = st
			}
			data := []map[string]interface{}{}
			for rows.Next() {
				values := make([]interface{}, len(cols))
				for i := range values {
					if types[i] == nil {
						values[i] = &[]byte{}
					} else {
						values[i] = reflect.New(types[i]).Interface()
					}
				}
				if err = rows.Scan(values...); err != nil {
					println("err", fmt.Sprint(err))
					return
				}
				m := make(map[string]interface{})
				for i, v := range values {
					if v == nil {
						//values[i] = nil
						m[colTypes[i].Name()] = nil
					} else {
						n, ok := colTypes[i].Length()
						m[colTypes[i].Name()] = fmt.Sprint(types[i], "[", n, "]", ok, ":", reflect.ValueOf(v).Elem().Interface())
					}
				}
				data = append(data, m)
				// if len(data)%10000 == 0 {
				// 	println("sqlite fetch data ", len(data), " use time ", time.Since(st).String())
				// }
			}
			println("sqlite fetch data ", len(data), " use time ", time.Since(st).String())
		}
	}
}