github.com/kotovmak/go-admin@v1.1.1/modules/db/types_test.go (about)

     1  package db
     2  
     3  import (
     4  	"fmt"
     5  	"os"
     6  	"regexp"
     7  	"strings"
     8  	"testing"
     9  	"time"
    10  
    11  	"github.com/kotovmak/go-admin/modules/config"
    12  	_ "github.com/kotovmak/go-admin/modules/db/drivers/mysql"
    13  	"github.com/magiconair/properties/assert"
    14  )
    15  
    16  const (
    17  	typeTestdbName            = "go-admin-type-test"
    18  	typeTesttableName         = "all_types"
    19  	typeTestpostgresCreateSql = `CREATE TABLE public.%s
    20  (
    21      id integer NOT NULL,
    22      type_1 smallint,
    23      type_2 bigint,
    24      type_3 numeric,
    25      type_4 real,
    26      type_5 double precision,
    27      type_6 smallint NOT NULL DEFAULT nextval('all_types_type_6_seq'::regclass),
    28      type_7 integer NOT NULL DEFAULT nextval('all_types_type_7_seq'::regclass),
    29      type_8 bigint NOT NULL DEFAULT nextval('all_types_type_8_seq'::regclass),
    30      type_9 money,
    31      type_10 character varying COLLATE pg_catalog."default",
    32      type_11 character(1) COLLATE pg_catalog."default",
    33      type_12 text COLLATE pg_catalog."default",
    34      type_13 timestamp with time zone,
    35      type_14 time with time zone,
    36      type_15 date,
    37      type_16 timestamp without time zone,
    38      type_17 interval,
    39      type_18 point,
    40      type_19 line,
    41      type_20 lseg,
    42      type_21 box,
    43      type_22 path,
    44      type_23 polygon,
    45      type_24 circle,
    46      type_25 cidr,
    47      type_26 inet,
    48      type_27 macaddr,
    49  	type_28 boolean,
    50      CONSTRAINT all_types_pkey PRIMARY KEY (id)
    51  )
    52  WITH (
    53      OIDS = FALSE
    54  )
    55  TABLESPACE pg_default;
    56  
    57  ALTER TABLE public.all_types
    58      OWNER to postgres;`
    59  )
    60  
    61  func TestMysqlGetTypeFromString(t *testing.T) {
    62  
    63  	conn := testConnDSN(DriverMysql, fmt.Sprintf("root:root@tcp(127.0.0.1:3306)/%s", typeTestdbName))
    64  	_, err := conn.Exec(fmt.Sprintf("create database if not exists `%s`", typeTestdbName))
    65  	assert.Equal(t, err, nil)
    66  	_, err = conn.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`;", typeTesttableName))
    67  	assert.Equal(t, err, nil)
    68  	_, err = conn.Exec(fmt.Sprintf(`CREATE TABLE `+"`"+`%s`+"`"+` (
    69    id int(11) unsigned NOT NULL AUTO_INCREMENT,
    70    `+testDelimiter("type_1")+` tinyint(11) DEFAULT NULL,
    71    `+testDelimiter("type_2")+` smallint(11) DEFAULT NULL,
    72    `+testDelimiter("type_3")+` mediumint(11) DEFAULT NULL,
    73    `+testDelimiter("type_4")+` bigint(11) DEFAULT NULL,
    74    `+testDelimiter("type_5")+` float DEFAULT NULL,
    75    `+testDelimiter("type_6")+` double(5,3) DEFAULT NULL,
    76    `+testDelimiter("type_7")+` double DEFAULT NULL,
    77    `+testDelimiter("type_8")+` double(5,3) DEFAULT NULL,
    78    `+testDelimiter("type_9")+` decimal(11,0) DEFAULT NULL,
    79    `+testDelimiter("type_10")+` bit(11) DEFAULT NULL,
    80    `+testDelimiter("type_11")+` tinyint(1) DEFAULT NULL,
    81    `+testDelimiter("type_12")+` tinyint(1) DEFAULT NULL,
    82    `+testDelimiter("type_13")+` decimal(10,5) DEFAULT NULL,
    83    `+testDelimiter("type_14")+` decimal(10,0) DEFAULT NULL,
    84    `+testDelimiter("type_15")+` decimal(10,0) DEFAULT NULL,
    85    `+testDelimiter("type_16")+` char(11) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    86    `+testDelimiter("type_17")+` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    87    `+testDelimiter("type_18")+` tinytext COLLATE utf8mb4_unicode_ci,
    88    `+testDelimiter("type_19")+` text COLLATE utf8mb4_unicode_ci,
    89    `+testDelimiter("type_20")+` mediumtext COLLATE utf8mb4_unicode_ci,
    90    `+testDelimiter("type_21")+` longtext COLLATE utf8mb4_unicode_ci,
    91    `+testDelimiter("type_22")+` tinyblob,
    92    `+testDelimiter("type_23")+` mediumblob,
    93    `+testDelimiter("type_24")+` blob,
    94    `+testDelimiter("type_25")+` longblob,
    95    `+testDelimiter("type_26")+` binary(1) DEFAULT NULL,
    96    `+testDelimiter("type_27")+` varbinary(1) DEFAULT NULL,
    97    `+testDelimiter("type_28")+` enum('RED','GREEN','BLUE') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    98    `+testDelimiter("type_29")+` set('RED','GREEN','BLUE') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    99    `+testDelimiter("type_30")+` date DEFAULT NULL,
   100    `+testDelimiter("type_31")+` datetime DEFAULT NULL,
   101    `+testDelimiter("type_32")+` timestamp NULL DEFAULT NULL,
   102    `+testDelimiter("type_33")+` time DEFAULT NULL,
   103    `+testDelimiter("type_34")+` year(4) DEFAULT NULL,
   104    `+testDelimiter("type_35")+` geometry DEFAULT NULL,
   105    `+testDelimiter("type_36")+` point DEFAULT NULL,
   106    `+testDelimiter("type_39")+` multilinestring DEFAULT NULL,
   107    `+testDelimiter("type_41")+` multipolygon DEFAULT NULL,
   108    `+testDelimiter("type_37")+` linestring DEFAULT NULL,
   109    `+testDelimiter("type_38")+` polygon DEFAULT NULL,
   110    `+testDelimiter("type_40")+` multipoint DEFAULT NULL,
   111    `+testDelimiter("type_42")+` geometrycollection DEFAULT NULL,
   112    `+testDelimiter("type_50")+` double(5,2) DEFAULT NULL,
   113    `+testDelimiter("type_51")+` json DEFAULT NULL,
   114    PRIMARY KEY (`+"`"+`id`+"`"+`)
   115  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`, typeTesttableName))
   116  
   117  	assert.Equal(t, err, nil)
   118  
   119  	_, err = conn.Exec(`INSERT INTO ` + testDelimiter(typeTesttableName) + ` (id, type_1, type_2, type_3, type_4, type_5, type_6, type_7, type_8, type_9, type_10, type_11, type_12, type_13, type_14, type_15, type_16, type_17, type_18, type_19, type_20, type_21, type_22, type_23, type_24, type_25, type_26, type_27, type_28, type_29, type_30, type_31, type_32, type_33, type_34, type_35, type_36, type_39, type_41, type_37, type_38, type_40, type_42, type_50, type_51)
   120  VALUES
   121  	(1, 1, 1, 1, 1, 1, 1.000, 1, 1.000, 1, 0, 1, 1, 1.00000, 1, 1, '1', '1', '1', '1', '1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2001', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);`)
   122  
   123  	assert.Equal(t, err, nil)
   124  
   125  	typeField := "Type"
   126  	fieldField := "Field"
   127  
   128  	conn = testConnDSN(DriverMysql, fmt.Sprintf("root:root@tcp(127.0.0.1:3306)/%s?charset=utf8mb4", typeTestdbName))
   129  
   130  	config.Initialize(&config.Config{
   131  		SqlLog: true,
   132  	})
   133  
   134  	columnsModel, _ := WithDriver(conn).Table(typeTesttableName).ShowColumns()
   135  	item, err := WithDriver(conn).Table(typeTesttableName).First()
   136  
   137  	for _, model := range columnsModel {
   138  		fieldTypeName := strings.ToUpper(testGetType(model[typeField].(string)))
   139  		GetDTAndCheck(fieldTypeName)
   140  		GetValueFromSQLOfDatabaseType(DatabaseType(fieldTypeName), item[model[fieldField].(string)])
   141  	}
   142  	assert.Equal(t, err, nil)
   143  }
   144  
   145  func TestPostgresqlGetTypeFromString(t *testing.T) {
   146  
   147  	// pg 11
   148  	testPG(t, "5433")
   149  	// pg 12
   150  	//testPG(t, "5434")
   151  }
   152  
   153  func testPG(t *testing.T, port string) {
   154  	connStatement := "host=127.0.0.1 port=" + port + " user=postgres password=root dbname=%s sslmode=disable"
   155  
   156  	conn := testConnDSN(DriverPostgresql, fmt.Sprintf(connStatement, typeTestdbName))
   157  	fmt.Println("creating database")
   158  	_, err := conn.Exec(fmt.Sprintf(`SELECT 'CREATE DATABASE %s' WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '%s')`, typeTestdbName, typeTestdbName))
   159  	assert.Equal(t, err, nil)
   160  	fmt.Println("drop table")
   161  	_, err = conn.Exec(fmt.Sprintf("DROP TABLE IF EXISTS `%s`;", typeTesttableName))
   162  	assert.Equal(t, err, nil)
   163  	fmt.Println("create sequence all_types_type_6_seq")
   164  	_, err = conn.Exec(`CREATE SEQUENCE IF NOT EXISTS public.all_types_type_6_seq START 1;`)
   165  	assert.Equal(t, err, nil)
   166  	fmt.Println("create sequence all_types_type_7_seq")
   167  	_, err = conn.Exec(`CREATE SEQUENCE IF NOT EXISTS public.all_types_type_7_seq START 1;`)
   168  	assert.Equal(t, err, nil)
   169  	fmt.Println("create sequence all_types_type_8_seq")
   170  	_, err = conn.Exec(`CREATE SEQUENCE IF NOT EXISTS public.all_types_type_8_seq START 1;`)
   171  	assert.Equal(t, err, nil)
   172  	fmt.Println("create table")
   173  	_, err = conn.Exec(fmt.Sprintf(typeTestpostgresCreateSql, typeTesttableName))
   174  
   175  	assert.Equal(t, err, nil)
   176  
   177  	fmt.Println("insert data")
   178  	_, err = conn.Exec(`INSERT INTO public.` + typeTesttableName + `(
   179  	id, type_1, type_2, type_3, type_4, type_5, type_6, type_7, type_8, type_9, type_10, type_11, type_12, type_13, type_14, type_15, type_16, type_17, type_18, type_19, type_20, type_21, type_22, type_23, type_24, type_25, type_26, type_27, type_28)
   180  	VALUES (1, 1, 1, 0.3, 1, 1, 1, 1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'n');`)
   181  
   182  	assert.Equal(t, err, nil)
   183  
   184  	typeField := "udt_name"
   185  	fieldField := "column_name"
   186  
   187  	conn = testConnDSN(DriverPostgresql, fmt.Sprintf(connStatement, typeTestdbName))
   188  
   189  	config.Initialize(&config.Config{
   190  		SqlLog: true,
   191  	})
   192  
   193  	columnsModel, _ := WithDriver(conn).Table(typeTesttableName).ShowColumns()
   194  	item, err := WithDriver(conn).Table(typeTesttableName).First()
   195  
   196  	for _, model := range columnsModel {
   197  		fieldTypeName := strings.ToUpper(testGetType(model[typeField].(string)))
   198  		fmt.Println("fieldTypeName", fieldTypeName)
   199  		GetDTAndCheck(fieldTypeName)
   200  		fmt.Println(model[fieldField].(string), GetValueFromSQLOfDatabaseType(DatabaseType(fieldTypeName), item[model[fieldField].(string)]))
   201  	}
   202  
   203  	assert.Equal(t, err, nil)
   204  }
   205  
   206  // *******************************
   207  // test helper methods
   208  // *******************************
   209  
   210  func testGetType(typeName string) string {
   211  	r, _ := regexp.Compile(`\((.*?)\)`)
   212  	typeName = r.ReplaceAllString(typeName, "")
   213  	return strings.ToLower(strings.ReplaceAll(typeName, " unsigned", ""))
   214  }
   215  
   216  func testConnDSN(driver, dsn string) Connection {
   217  	return GetConnectionByDriver(driver).InitDB(map[string]config.Database{
   218  		"default": {Dsn: dsn},
   219  	})
   220  }
   221  
   222  func testConn(driver string, cfg config.Database) Connection {
   223  	cfg.Driver = driver
   224  	cfg.MaxIdleConns = 10
   225  	cfg.MaxOpenConns = 80
   226  	cfg.ConnMaxLifetime = time.Hour
   227  	cfg.ConnMaxIdleTime = 0
   228  	return GetConnectionByDriver(driver).InitDB(map[string]config.Database{
   229  		"default": cfg,
   230  	})
   231  }
   232  
   233  func testDelimiter(s string) string {
   234  	return "`" + s + "`"
   235  }
   236  
   237  func testCurrentPath() string {
   238  	dir, err := os.Getwd()
   239  	if err != nil {
   240  		panic(err)
   241  	}
   242  	return dir
   243  }