github.com/octohelm/storage@v0.0.0-20240516030302-1ac2cc1ea347/internal/sql/adapter/postgres/dialect_test.go (about)

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