github.com/angenalZZZ/gofunc@v0.0.0-20210507121333-48ff1be3917b/data/db_test.go (about)

     1  package data_test
     2  
     3  import (
     4  	"errors"
     5  	"strings"
     6  	"sync"
     7  	"testing"
     8  	"time"
     9  
    10  	"github.com/angenalZZZ/gofunc/configfile"
    11  	"github.com/angenalZZZ/gofunc/data"
    12  	"github.com/angenalZZZ/gofunc/data/id"
    13  	"github.com/angenalZZZ/gofunc/data/random"
    14  	"github.com/angenalZZZ/gofunc/f"
    15  	"github.com/jmoiron/sqlx"
    16  
    17  	_ "github.com/denisenkom/go-mssqldb"
    18  	_ "github.com/go-sql-driver/mysql"
    19  	_ "github.com/mattn/go-sqlite3"
    20  )
    21  
    22  func initDbSqlite3(t *testing.T, dbType string) (db *sqlx.DB, err error) {
    23  	var config map[interface{}]interface{}
    24  	config, err = configfile.YamlToMap("../test/config/database.yaml")
    25  	if err != nil {
    26  		return nil, err
    27  	}
    28  	conn, ok := config["database"].(map[interface{}]interface{})
    29  	if !ok {
    30  		return nil, errors.New("database config error")
    31  	}
    32  
    33  	data.DbType = dbType
    34  	dbConn := conn[dbType].(string)
    35  	db, err = sqlx.Open(dbType, dbConn)
    36  	if err == nil {
    37  		t.Logf("[%s] %s", dbType, dbConn)
    38  	}
    39  	if db == nil {
    40  		return nil, errors.New("database config error")
    41  	}
    42  	db.SetConnMaxLifetime(10 * time.Second)
    43  	db.SetMaxOpenConns(2)
    44  	db.SetMaxIdleConns(2)
    45  	return
    46  }
    47  
    48  func TestDb_test_mysql(t *testing.T) {
    49  	dbo, err := initDbSqlite3(t, "mysql")
    50  	if err != nil {
    51  		t.Fatal(err)
    52  	}
    53  	defer func() { _ = dbo.Close() }()
    54  
    55  	err = dbo.Ping()
    56  	if err != nil {
    57  		t.Fatal(err)
    58  	}
    59  
    60  	for i := 0; i < 3; i++ {
    61  		time.Sleep(10 * time.Second)
    62  		tbl := "logtest"
    63  		sql := `INSERT INTO logtest(Code,Type,Message,Account,CreateTime,CreateUser) VALUES(?,2,?,?,CURRENT_TIMESTAMP(),?)`
    64  		if res, err := dbo.Exec(sql, random.AlphaNumber(6), random.AlphaNumber(100), random.AlphaNumber(6), id.L36()); err != nil {
    65  			t.Fatal(err)
    66  		} else {
    67  			num, _ := res.LastInsertId()
    68  			t.Logf(`[%s] %q inserted rows [Id=%d]`, data.DbType, tbl, num)
    69  		}
    70  	}
    71  }
    72  
    73  func TestDb_test_sqlite3(t *testing.T) {
    74  	dbo, err := initDbSqlite3(t, "sqlite3")
    75  	if err != nil {
    76  		t.Fatal(err)
    77  	}
    78  	defer func() { _ = dbo.Close() }()
    79  
    80  	err = dbo.Ping()
    81  	if err != nil {
    82  		t.Fatal(err)
    83  	}
    84  
    85  	var num int64
    86  	sql, tbl := `SELECT * FROM sqlite_master WHERE type='table' AND name=?`, "logtest"
    87  	if rows, err := dbo.Queryx(sql, tbl); err != nil {
    88  		t.Fatal(err)
    89  	} else {
    90  		for rows.Next() {
    91  			num++
    92  			dest := make(map[string]interface{})
    93  			_ = rows.MapScan(dest)
    94  			t.Logf("[%s] %q table is exists", data.DbType, dest["name"])
    95  		}
    96  		if num == 0 {
    97  			if buf, err := f.ReadFile("../test/sql/logtest-sqlite.sql"); err != nil {
    98  				t.Fatal(err)
    99  			} else {
   100  				sql = strings.TrimSpace(f.String(buf))
   101  				if res, err := dbo.Exec(sql); err != nil {
   102  					t.Fatal(err)
   103  				} else {
   104  					num, _ = res.RowsAffected()
   105  					t.Logf(`[%s] %q create table, rows affected %d , created by "logtest-sqlite.sql"`, data.DbType, tbl, num)
   106  				}
   107  			}
   108  		}
   109  	}
   110  
   111  	sql = `INSERT INTO logtest(Code,Type,Message,Account,CreateTime,CreateUser) VALUES(?,2,?,?,DATETIME(),?)`
   112  	if res, err := dbo.Exec(sql, random.AlphaNumber(6), random.AlphaNumber(100), random.AlphaNumber(6), id.L36()); err != nil {
   113  		t.Fatal(err)
   114  	} else {
   115  		num, _ = res.LastInsertId()
   116  		t.Logf(`[%s] %q inserted rows [Id=%d]`, data.DbType, tbl, num)
   117  	}
   118  }
   119  
   120  func TestBenchDb_insert_sqlite3(t *testing.T) {
   121  	dbs := make([]*sqlx.DB, 2)
   122  	// sets number
   123  	dbN, number := len(dbs), 100
   124  
   125  	for i := 0; i < dbN; i++ {
   126  		dbo, err := initDbSqlite3(t, "sqlite3")
   127  		if err != nil {
   128  			t.Fatal(err)
   129  		}
   130  		dbs[i] = dbo
   131  	}
   132  
   133  	wg := new(sync.WaitGroup)
   134  	wg.Add(dbN)
   135  
   136  	// start benchmark test
   137  	t1 := time.Now()
   138  
   139  	for i := 0; i < dbN; i++ {
   140  		go func(idx, num int) {
   141  			dbo := dbs[idx]
   142  			defer func() {
   143  				_ = dbo.Close()
   144  				wg.Done()
   145  			}()
   146  			for i := 0; i < num; i++ {
   147  				sql := `INSERT INTO logtest(Code,Type,Message,Account,CreateTime,CreateUser) VALUES(?,2,?,?,DATETIME(),?)`
   148  				if _, err := dbo.Exec(sql, random.AlphaNumber(6), random.AlphaNumber(100), random.AlphaNumber(6), id.L36()); err != nil {
   149  					t.Fatal(err)
   150  				}
   151  			}
   152  		}(i, number/dbN)
   153  	}
   154  
   155  	wg.Wait()
   156  	t2 := time.Now()
   157  	ts := t2.Sub(t1)
   158  	time.Sleep(time.Millisecond)
   159  
   160  	t.Logf("Take time %s, handle sql records %d qps", ts, 1000*int64(number)/ts.Milliseconds())
   161  }