github.com/insionng/yougam@v0.0.0-20170714101924-2bc18d833463/libraries/go-xorm/core/db_test.go (about)

     1  package core
     2  
     3  import (
     4  	"errors"
     5  	"fmt"
     6  	"os"
     7  	"testing"
     8  	"time"
     9  
    10  	_ "github.com/go-sql-driver/mysql"
    11  	_ "github.com/mattn/go-sqlite3"
    12  )
    13  
    14  var (
    15  	//dbtype         string = "sqlite3"
    16  	dbtype         string = "mysql"
    17  	createTableSql string
    18  )
    19  
    20  type User struct {
    21  	Id       int64
    22  	Name     string
    23  	Title    string
    24  	Age      float32
    25  	Alias    string
    26  	NickName string
    27  	Created  NullTime
    28  }
    29  
    30  func init() {
    31  	switch dbtype {
    32  	case "sqlite3":
    33  		createTableSql = "CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `name` TEXT NULL, " +
    34  			"`title` TEXT NULL, `age` FLOAT NULL, `alias` TEXT NULL, `nick_name` TEXT NULL, `created` datetime);"
    35  	case "mysql":
    36  		createTableSql = "CREATE TABLE IF NOT EXISTS `user` (`id` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL, `name` TEXT NULL, " +
    37  			"`title` TEXT NULL, `age` FLOAT NULL, `alias` TEXT NULL, `nick_name` TEXT NULL, `created` datetime);"
    38  	default:
    39  		panic("no db type")
    40  	}
    41  }
    42  
    43  func testOpen() (*DB, error) {
    44  	switch dbtype {
    45  	case "sqlite3":
    46  		os.Remove("./test.db")
    47  		return Open("sqlite3", "./test.db")
    48  	case "mysql":
    49  		return Open("mysql", "root:@/core_test?charset=utf8")
    50  	default:
    51  		panic("no db type")
    52  	}
    53  }
    54  
    55  func BenchmarkOriQuery(b *testing.B) {
    56  	b.StopTimer()
    57  	db, err := testOpen()
    58  	if err != nil {
    59  		b.Error(err)
    60  	}
    61  	defer db.Close()
    62  
    63  	_, err = db.Exec(createTableSql)
    64  	if err != nil {
    65  		b.Error(err)
    66  	}
    67  
    68  	for i := 0; i < 50; i++ {
    69  		_, err = db.Exec("insert into user (`name`, title, age, alias, nick_name, created) values (?,?,?,?,?, ?)",
    70  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
    71  		if err != nil {
    72  			b.Error(err)
    73  		}
    74  	}
    75  
    76  	b.StartTimer()
    77  
    78  	for i := 0; i < b.N; i++ {
    79  		rows, err := db.Query("select * from user")
    80  		if err != nil {
    81  			b.Error(err)
    82  		}
    83  
    84  		for rows.Next() {
    85  			var Id int64
    86  			var Name, Title, Alias, NickName string
    87  			var Age float32
    88  			var Created NullTime
    89  			err = rows.Scan(&Id, &Name, &Title, &Age, &Alias, &NickName, &Created)
    90  			if err != nil {
    91  				b.Error(err)
    92  			}
    93  			//fmt.Println(Id, Name, Title, Age, Alias, NickName)
    94  		}
    95  		rows.Close()
    96  	}
    97  }
    98  
    99  func BenchmarkStructQuery(b *testing.B) {
   100  	b.StopTimer()
   101  
   102  	db, err := testOpen()
   103  	if err != nil {
   104  		b.Error(err)
   105  	}
   106  	defer db.Close()
   107  
   108  	_, err = db.Exec(createTableSql)
   109  	if err != nil {
   110  		b.Error(err)
   111  	}
   112  
   113  	for i := 0; i < 50; i++ {
   114  		_, err = db.Exec("insert into user (`name`, title, age, alias, nick_name, created) values (?,?,?,?,?, ?)",
   115  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   116  		if err != nil {
   117  			b.Error(err)
   118  		}
   119  	}
   120  
   121  	b.StartTimer()
   122  
   123  	for i := 0; i < b.N; i++ {
   124  		rows, err := db.Query("select * from user")
   125  		if err != nil {
   126  			b.Error(err)
   127  		}
   128  
   129  		for rows.Next() {
   130  			var user User
   131  			err = rows.ScanStructByIndex(&user)
   132  			if err != nil {
   133  				b.Error(err)
   134  			}
   135  			if user.Name != "xlw" {
   136  				fmt.Println(user)
   137  				b.Error(errors.New("name should be xlw"))
   138  			}
   139  		}
   140  		rows.Close()
   141  	}
   142  }
   143  
   144  func BenchmarkStruct2Query(b *testing.B) {
   145  	b.StopTimer()
   146  
   147  	db, err := testOpen()
   148  	if err != nil {
   149  		b.Error(err)
   150  	}
   151  	defer db.Close()
   152  
   153  	_, err = db.Exec(createTableSql)
   154  	if err != nil {
   155  		b.Error(err)
   156  	}
   157  
   158  	for i := 0; i < 50; i++ {
   159  		_, err = db.Exec("insert into user (`name`, title, age, alias, nick_name, created) values (?,?,?,?,?,?)",
   160  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   161  		if err != nil {
   162  			b.Error(err)
   163  		}
   164  	}
   165  
   166  	db.Mapper = NewCacheMapper(&SnakeMapper{})
   167  	b.StartTimer()
   168  
   169  	for i := 0; i < b.N; i++ {
   170  		rows, err := db.Query("select * from user")
   171  		if err != nil {
   172  			b.Error(err)
   173  		}
   174  
   175  		for rows.Next() {
   176  			var user User
   177  			err = rows.ScanStructByName(&user)
   178  			if err != nil {
   179  				b.Error(err)
   180  			}
   181  			if user.Name != "xlw" {
   182  				fmt.Println(user)
   183  				b.Error(errors.New("name should be xlw"))
   184  			}
   185  		}
   186  		rows.Close()
   187  	}
   188  }
   189  
   190  func BenchmarkSliceInterfaceQuery(b *testing.B) {
   191  	b.StopTimer()
   192  
   193  	db, err := testOpen()
   194  	if err != nil {
   195  		b.Error(err)
   196  	}
   197  	defer db.Close()
   198  
   199  	_, err = db.Exec(createTableSql)
   200  	if err != nil {
   201  		b.Error(err)
   202  	}
   203  
   204  	for i := 0; i < 50; i++ {
   205  		_, err = db.Exec("insert into user (`name`, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   206  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   207  		if err != nil {
   208  			b.Error(err)
   209  		}
   210  	}
   211  
   212  	b.StartTimer()
   213  
   214  	for i := 0; i < b.N; i++ {
   215  		rows, err := db.Query("select * from user")
   216  		if err != nil {
   217  			b.Error(err)
   218  		}
   219  
   220  		cols, err := rows.Columns()
   221  		if err != nil {
   222  			b.Error(err)
   223  		}
   224  
   225  		for rows.Next() {
   226  			slice := make([]interface{}, len(cols))
   227  			err = rows.ScanSlice(&slice)
   228  			if err != nil {
   229  				b.Error(err)
   230  			}
   231  			fmt.Println(slice)
   232  			if *slice[1].(*string) != "xlw" {
   233  				fmt.Println(slice)
   234  				b.Error(errors.New("name should be xlw"))
   235  			}
   236  		}
   237  
   238  		rows.Close()
   239  	}
   240  }
   241  
   242  /*func BenchmarkSliceBytesQuery(b *testing.B) {
   243  	b.StopTimer()
   244  	os.Remove("./test.db")
   245  	db, err := Open("sqlite3", "./test.db")
   246  	if err != nil {
   247  		b.Error(err)
   248  	}
   249  	defer db.Close()
   250  
   251  	_, err = db.Exec(createTableSql)
   252  	if err != nil {
   253  		b.Error(err)
   254  	}
   255  
   256  	for i := 0; i < 50; i++ {
   257  		_, err = db.Exec("insert into user (name, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   258  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   259  		if err != nil {
   260  			b.Error(err)
   261  		}
   262  	}
   263  
   264  	b.StartTimer()
   265  
   266  	for i := 0; i < b.N; i++ {
   267  		rows, err := db.Query("select * from user")
   268  		if err != nil {
   269  			b.Error(err)
   270  		}
   271  
   272  		cols, err := rows.Columns()
   273  		if err != nil {
   274  			b.Error(err)
   275  		}
   276  
   277  		for rows.Next() {
   278  			slice := make([][]byte, len(cols))
   279  			err = rows.ScanSlice(&slice)
   280  			if err != nil {
   281  				b.Error(err)
   282  			}
   283  			if string(slice[1]) != "xlw" {
   284  				fmt.Println(slice)
   285  				b.Error(errors.New("name should be xlw"))
   286  			}
   287  		}
   288  
   289  		rows.Close()
   290  	}
   291  }
   292  */
   293  
   294  func BenchmarkSliceStringQuery(b *testing.B) {
   295  	b.StopTimer()
   296  	db, err := testOpen()
   297  	if err != nil {
   298  		b.Error(err)
   299  	}
   300  	defer db.Close()
   301  
   302  	_, err = db.Exec(createTableSql)
   303  	if err != nil {
   304  		b.Error(err)
   305  	}
   306  
   307  	for i := 0; i < 50; i++ {
   308  		_, err = db.Exec("insert into user (name, title, age, alias, nick_name, created) values (?,?,?,?,?,?)",
   309  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   310  		if err != nil {
   311  			b.Error(err)
   312  		}
   313  	}
   314  
   315  	b.StartTimer()
   316  
   317  	for i := 0; i < b.N; i++ {
   318  		rows, err := db.Query("select * from user")
   319  		if err != nil {
   320  			b.Error(err)
   321  		}
   322  
   323  		cols, err := rows.Columns()
   324  		if err != nil {
   325  			b.Error(err)
   326  		}
   327  
   328  		for rows.Next() {
   329  			slice := make([]*string, len(cols))
   330  			err = rows.ScanSlice(&slice)
   331  			if err != nil {
   332  				b.Error(err)
   333  			}
   334  			if (*slice[1]) != "xlw" {
   335  				fmt.Println(slice)
   336  				b.Error(errors.New("name should be xlw"))
   337  			}
   338  		}
   339  
   340  		rows.Close()
   341  	}
   342  }
   343  
   344  func BenchmarkMapInterfaceQuery(b *testing.B) {
   345  	b.StopTimer()
   346  
   347  	db, err := testOpen()
   348  	if err != nil {
   349  		b.Error(err)
   350  	}
   351  	defer db.Close()
   352  
   353  	_, err = db.Exec(createTableSql)
   354  	if err != nil {
   355  		b.Error(err)
   356  	}
   357  
   358  	for i := 0; i < 50; i++ {
   359  		_, err = db.Exec("insert into user (name, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   360  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   361  		if err != nil {
   362  			b.Error(err)
   363  		}
   364  	}
   365  
   366  	b.StartTimer()
   367  
   368  	for i := 0; i < b.N; i++ {
   369  		rows, err := db.Query("select * from user")
   370  		if err != nil {
   371  			b.Error(err)
   372  		}
   373  
   374  		for rows.Next() {
   375  			m := make(map[string]interface{})
   376  			err = rows.ScanMap(&m)
   377  			if err != nil {
   378  				b.Error(err)
   379  			}
   380  			if m["name"].(string) != "xlw" {
   381  				fmt.Println(m)
   382  				b.Error(errors.New("name should be xlw"))
   383  			}
   384  		}
   385  
   386  		rows.Close()
   387  	}
   388  }
   389  
   390  /*func BenchmarkMapBytesQuery(b *testing.B) {
   391  	b.StopTimer()
   392  	os.Remove("./test.db")
   393  	db, err := Open("sqlite3", "./test.db")
   394  	if err != nil {
   395  		b.Error(err)
   396  	}
   397  	defer db.Close()
   398  
   399  	_, err = db.Exec(createTableSql)
   400  	if err != nil {
   401  		b.Error(err)
   402  	}
   403  
   404  	for i := 0; i < 50; i++ {
   405  		_, err = db.Exec("insert into user (name, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   406  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   407  		if err != nil {
   408  			b.Error(err)
   409  		}
   410  	}
   411  
   412  	b.StartTimer()
   413  
   414  	for i := 0; i < b.N; i++ {
   415  		rows, err := db.Query("select * from user")
   416  		if err != nil {
   417  			b.Error(err)
   418  		}
   419  
   420  		for rows.Next() {
   421  			m := make(map[string][]byte)
   422  			err = rows.ScanMap(&m)
   423  			if err != nil {
   424  				b.Error(err)
   425  			}
   426  			if string(m["name"]) != "xlw" {
   427  				fmt.Println(m)
   428  				b.Error(errors.New("name should be xlw"))
   429  			}
   430  		}
   431  
   432  		rows.Close()
   433  	}
   434  }
   435  */
   436  /*
   437  func BenchmarkMapStringQuery(b *testing.B) {
   438  	b.StopTimer()
   439  	os.Remove("./test.db")
   440  	db, err := Open("sqlite3", "./test.db")
   441  	if err != nil {
   442  		b.Error(err)
   443  	}
   444  	defer db.Close()
   445  
   446  	_, err = db.Exec(createTableSql)
   447  	if err != nil {
   448  		b.Error(err)
   449  	}
   450  
   451  	for i := 0; i < 50; i++ {
   452  		_, err = db.Exec("insert into user (name, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   453  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   454  		if err != nil {
   455  			b.Error(err)
   456  		}
   457  	}
   458  
   459  	b.StartTimer()
   460  
   461  	for i := 0; i < b.N; i++ {
   462  		rows, err := db.Query("select * from user")
   463  		if err != nil {
   464  			b.Error(err)
   465  		}
   466  
   467  		for rows.Next() {
   468  			m := make(map[string]string)
   469  			err = rows.ScanMap(&m)
   470  			if err != nil {
   471  				b.Error(err)
   472  			}
   473  			if m["name"] != "xlw" {
   474  				fmt.Println(m)
   475  				b.Error(errors.New("name should be xlw"))
   476  			}
   477  		}
   478  
   479  		rows.Close()
   480  	}
   481  }*/
   482  
   483  func BenchmarkExec(b *testing.B) {
   484  	b.StopTimer()
   485  
   486  	db, err := testOpen()
   487  	if err != nil {
   488  		b.Error(err)
   489  	}
   490  	defer db.Close()
   491  
   492  	_, err = db.Exec(createTableSql)
   493  	if err != nil {
   494  		b.Error(err)
   495  	}
   496  
   497  	b.StartTimer()
   498  
   499  	for i := 0; i < b.N; i++ {
   500  		_, err = db.Exec("insert into user (`name`, title, age, alias, nick_name,created) values (?,?,?,?,?,?)",
   501  			"xlw", "tester", 1.2, "lunny", "lunny xiao", time.Now())
   502  		if err != nil {
   503  			b.Error(err)
   504  		}
   505  	}
   506  }
   507  
   508  func BenchmarkExecMap(b *testing.B) {
   509  	b.StopTimer()
   510  
   511  	db, err := testOpen()
   512  	if err != nil {
   513  		b.Error(err)
   514  	}
   515  	defer db.Close()
   516  
   517  	_, err = db.Exec(createTableSql)
   518  	if err != nil {
   519  		b.Error(err)
   520  	}
   521  
   522  	b.StartTimer()
   523  
   524  	mp := map[string]interface{}{
   525  		"name":      "xlw",
   526  		"title":     "tester",
   527  		"age":       1.2,
   528  		"alias":     "lunny",
   529  		"nick_name": "lunny xiao",
   530  		"created":   time.Now(),
   531  	}
   532  
   533  	for i := 0; i < b.N; i++ {
   534  		_, err = db.ExecMap("insert into user (`name`, title, age, alias, nick_name, created) "+
   535  			"values (?name,?title,?age,?alias,?nick_name,?created)",
   536  			&mp)
   537  		if err != nil {
   538  			b.Error(err)
   539  		}
   540  	}
   541  }
   542  
   543  func TestExecMap(t *testing.T) {
   544  	db, err := testOpen()
   545  	if err != nil {
   546  		t.Error(err)
   547  	}
   548  	defer db.Close()
   549  
   550  	_, err = db.Exec(createTableSql)
   551  	if err != nil {
   552  		t.Error(err)
   553  	}
   554  
   555  	mp := map[string]interface{}{
   556  		"name":      "xlw",
   557  		"title":     "tester",
   558  		"age":       1.2,
   559  		"alias":     "lunny",
   560  		"nick_name": "lunny xiao",
   561  		"created":   time.Now(),
   562  	}
   563  
   564  	_, err = db.ExecMap("insert into user (`name`, title, age, alias, nick_name,created) "+
   565  		"values (?name,?title,?age,?alias,?nick_name,?created)",
   566  		&mp)
   567  	if err != nil {
   568  		t.Error(err)
   569  	}
   570  
   571  	rows, err := db.Query("select * from user")
   572  	if err != nil {
   573  		t.Error(err)
   574  	}
   575  
   576  	for rows.Next() {
   577  		var user User
   578  		err = rows.ScanStructByName(&user)
   579  		if err != nil {
   580  			t.Error(err)
   581  		}
   582  		fmt.Println("--", user)
   583  	}
   584  }
   585  
   586  func TestExecStruct(t *testing.T) {
   587  	db, err := testOpen()
   588  	if err != nil {
   589  		t.Error(err)
   590  	}
   591  	defer db.Close()
   592  
   593  	_, err = db.Exec(createTableSql)
   594  	if err != nil {
   595  		t.Error(err)
   596  	}
   597  
   598  	user := User{Name: "xlw",
   599  		Title:    "tester",
   600  		Age:      1.2,
   601  		Alias:    "lunny",
   602  		NickName: "lunny xiao",
   603  		Created:  NullTime(time.Now()),
   604  	}
   605  
   606  	_, err = db.ExecStruct("insert into user (`name`, title, age, alias, nick_name,created) "+
   607  		"values (?Name,?Title,?Age,?Alias,?NickName,?Created)",
   608  		&user)
   609  	if err != nil {
   610  		t.Error(err)
   611  	}
   612  
   613  	rows, err := db.QueryStruct("select * from user where `name` = ?Name", &user)
   614  	if err != nil {
   615  		t.Error(err)
   616  	}
   617  
   618  	for rows.Next() {
   619  		var user User
   620  		err = rows.ScanStructByName(&user)
   621  		if err != nil {
   622  			t.Error(err)
   623  		}
   624  		fmt.Println("1--", user)
   625  	}
   626  }
   627  
   628  func BenchmarkExecStruct(b *testing.B) {
   629  	b.StopTimer()
   630  	db, err := testOpen()
   631  	if err != nil {
   632  		b.Error(err)
   633  	}
   634  	defer db.Close()
   635  
   636  	_, err = db.Exec(createTableSql)
   637  	if err != nil {
   638  		b.Error(err)
   639  	}
   640  
   641  	b.StartTimer()
   642  
   643  	user := User{Name: "xlw",
   644  		Title:    "tester",
   645  		Age:      1.2,
   646  		Alias:    "lunny",
   647  		NickName: "lunny xiao",
   648  		Created:  NullTime(time.Now()),
   649  	}
   650  
   651  	for i := 0; i < b.N; i++ {
   652  		_, err = db.ExecStruct("insert into user (`name`, title, age, alias, nick_name,created) "+
   653  			"values (?Name,?Title,?Age,?Alias,?NickName,?Created)",
   654  			&user)
   655  		if err != nil {
   656  			b.Error(err)
   657  		}
   658  	}
   659  }