github.com/kunlun-qilian/sqlx/v3@v3.0.0/connectors/postgresql/postgresql_connector_test.go (about)

     1  package postgresql
     2  
     3  import (
     4  	"context"
     5  	"database/sql/driver"
     6  	"fmt"
     7  	"testing"
     8  
     9  	"github.com/kunlun-qilian/sqlx/v3/builder"
    10  	"github.com/kunlun-qilian/sqlx/v3/builder/buidertestingutils"
    11  	"github.com/onsi/gomega"
    12  )
    13  
    14  func TestPostgreSQLConnector(t *testing.T) {
    15  	c := &PostgreSQLConnector{}
    16  
    17  	table := builder.T("t",
    18  		builder.Col("F_id").Type(uint64(0), ",autoincrement"),
    19  		builder.Col("f_old_name").Type("", ",deprecated=f_name"),
    20  		builder.Col("f_name").Type("", ",size=128,default=''"),
    21  		builder.Col("F_geo").Type(&Point{}, ""),
    22  		builder.Col("F_created_at").Type(int64(0), ",default='0'"),
    23  		builder.Col("F_updated_at").Type(int64(0), ",default='0'"),
    24  		builder.PrimaryKey(builder.Cols("F_id")),
    25  		builder.UniqueIndex("I_name", builder.Cols("F_id", "F_name")).Using("BTREE"),
    26  		builder.Index("I_created_at", builder.Cols("F_created_at")).Using("BTREE"),
    27  		builder.Index("I_geo", builder.Cols("F_geo")).Using("SPATIAL"),
    28  	)
    29  
    30  	cases := map[string]struct {
    31  		expr   builder.SqlExpr
    32  		expect builder.SqlExpr
    33  	}{
    34  		"CreateDatabase": {
    35  			c.CreateDatabase("db"),
    36  			builder.Expr( /* language=PostgreSQL */ `CREATE DATABASE db;`),
    37  		},
    38  		"DropDatabase": {
    39  			c.DropDatabase("db"),
    40  			builder.Expr( /* language=PostgreSQL */ `DROP DATABASE IF EXISTS db;`),
    41  		},
    42  		"AddIndex": {
    43  			c.AddIndex(table.Key("I_name")),
    44  			builder.Expr( /* language=PostgreSQL */ "CREATE UNIQUE INDEX t_i_name ON t USING BTREE (f_id, f_name);"),
    45  		},
    46  		"AddPrimaryKey": {
    47  			c.AddIndex(table.Key("PRIMARY")),
    48  			builder.Expr( /* language=PostgreSQL */ "ALTER TABLE t ADD PRIMARY KEY (f_id);"),
    49  		},
    50  		"AddSpatialIndex": {
    51  			c.AddIndex(table.Key("I_geo")),
    52  			builder.Expr( /* language=PostgreSQL */ "CREATE INDEX t_i_geo ON t USING GIST (f_geo);"),
    53  		},
    54  		"DropIndex": {
    55  			c.DropIndex(table.Key("I_name")),
    56  			builder.Expr( /* language=PostgreSQL */ "DROP INDEX IF EXISTS t_i_name"),
    57  		},
    58  		"DropPrimaryKey": {
    59  			c.DropIndex(table.Key("PRIMARY")),
    60  			builder.Expr( /* language=PostgreSQL */ "ALTER TABLE t DROP CONSTRAINT t_pkey;"),
    61  		},
    62  		"CreateTableIsNotExists": {
    63  			c.CreateTableIsNotExists(table)[0],
    64  			builder.Expr( /* language=PostgreSQL */ `CREATE TABLE IF NOT EXISTS t (
    65  	f_id bigserial NOT NULL,
    66  	f_name varchar(128) NOT NULL DEFAULT '',
    67  	f_geo POINT NOT NULL,
    68  	f_created_at bigint NOT NULL DEFAULT '0',
    69  	f_updated_at bigint NOT NULL DEFAULT '0',
    70  	PRIMARY KEY (f_id)
    71  );`),
    72  		},
    73  		"DropTable": {
    74  			c.DropTable(table),
    75  			builder.Expr( /* language=PostgreSQL */ "DROP TABLE IF EXISTS t;"),
    76  		},
    77  		"TruncateTable": {
    78  			c.TruncateTable(table),
    79  			builder.Expr( /* language=PostgreSQL */ "TRUNCATE TABLE t;"),
    80  		},
    81  		"AddColumn": {
    82  			c.AddColumn(table.Col("F_name")),
    83  			builder.Expr( /* language=PostgreSQL */ "ALTER TABLE t ADD COLUMN f_name varchar(128) NOT NULL DEFAULT '';"),
    84  		},
    85  		"DropColumn": {
    86  			c.DropColumn(table.Col("F_name")),
    87  			builder.Expr( /* language=PostgreSQL */ "ALTER TABLE t DROP COLUMN f_name;"),
    88  		},
    89  	}
    90  
    91  	for name, c := range cases {
    92  		t.Run(name, func(t *testing.T) {
    93  			gomega.NewWithT(t).Expect(c.expr).To(buidertestingutils.BeExpr(c.expr.Ex(context.Background()).Query()))
    94  		})
    95  	}
    96  }
    97  
    98  type Point struct {
    99  	X float64
   100  	Y float64
   101  }
   102  
   103  func (Point) DataType(engine string) string {
   104  	return "POINT"
   105  }
   106  
   107  func (Point) ValueEx() string {
   108  	return `ST_GeomFromText(?)`
   109  }
   110  
   111  func (p Point) Value() (driver.Value, error) {
   112  	return fmt.Sprintf("POINT(%v %v)", p.X, p.Y), nil
   113  }