github.com/tuhaihe/gpbackup@v1.0.3/integration/predata_types_queries_test.go (about) 1 package integration 2 3 import ( 4 "github.com/tuhaihe/gp-common-go-libs/structmatcher" 5 "github.com/tuhaihe/gp-common-go-libs/testhelper" 6 "github.com/tuhaihe/gpbackup/backup" 7 "github.com/tuhaihe/gpbackup/options" 8 "github.com/tuhaihe/gpbackup/testutils" 9 10 . "github.com/onsi/ginkgo/v2" 11 . "github.com/onsi/gomega" 12 ) 13 14 var _ = Describe("backup integration tests", func() { 15 Describe("Get[type]Types functions", func() { 16 var ( 17 shellType backup.ShellType 18 baseTypeDefault backup.BaseType 19 baseTypeCustom backup.BaseType 20 compositeType backup.CompositeType 21 enumType backup.EnumType 22 enumType2 backup.EnumType 23 ) 24 BeforeEach(func() { 25 shellType = backup.ShellType{Schema: "public", Name: "shell_type"} 26 baseTypeDefault = backup.BaseType{ 27 Oid: 1, Schema: "public", Name: "base_type", Input: "public.base_fn_in", Output: "public.base_fn_out", Receive: "", 28 Send: "", ModIn: "", ModOut: "", InternalLength: -1, IsPassedByValue: false, Alignment: "i", Storage: "p", 29 DefaultVal: "", Element: "", Delimiter: ",", Category: "U", 30 } 31 baseTypeCustom = backup.BaseType{ 32 Oid: 1, Schema: "public", Name: "base_type", Input: "public.base_fn_in", Output: "public.base_fn_out", Receive: "", 33 Send: "", ModIn: "", ModOut: "", InternalLength: 8, IsPassedByValue: true, Alignment: "d", Storage: "p", 34 DefaultVal: "0", Element: "integer", Delimiter: ";", Category: "U", StorageOptions: "compresstype=zlib, compresslevel=1, blocksize=32768", 35 } 36 compositeType = backup.CompositeType{ 37 Oid: 1, Schema: "public", Name: "composite_type", 38 Attributes: []backup.Attribute{ 39 {Name: "name", Type: "integer"}, 40 {Name: "name2", Type: "numeric(8,2)"}, 41 {Name: "name1", Type: "character(8)"}, 42 }, 43 } 44 enumType = backup.EnumType{ 45 Oid: 1, Schema: "public", Name: "enum_type", EnumLabels: "'label1',\n\t'label2',\n\t'label3'", 46 } 47 enumType2 = backup.EnumType{ 48 Oid: 1, Schema: "public", Name: "enum_type2", EnumLabels: "'label3',\n\t'label2',\n\t'label1'", 49 } 50 }) 51 It("returns a slice for a shell type", func() { 52 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.shell_type") 53 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.shell_type") 54 55 results := backup.GetShellTypes(connectionPool) 56 57 Expect(results).To(HaveLen(1)) 58 structmatcher.ExpectStructsToMatchIncluding(&shellType, &results[0], "Schema", "Name", "Type") 59 }) 60 It("returns a slice of composite types", func() { 61 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.composite_type AS (name int4, name2 numeric(8,2), name1 character(8));") 62 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.composite_type") 63 64 results := backup.GetCompositeTypes(connectionPool) 65 66 Expect(results).To(HaveLen(1)) 67 structmatcher.ExpectStructsToMatchExcluding(&compositeType, &results[0], "Oid", "Attributes.CompositeTypeOid") 68 }) 69 It("returns a slice of composite types with attribute comments", func() { 70 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.composite_type AS (name int4, name2 numeric(8,2), name1 character(8));") 71 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.composite_type") 72 testhelper.AssertQueryRuns(connectionPool, "COMMENT ON COLUMN public.composite_type.name IS 'name comment';") 73 74 results := backup.GetCompositeTypes(connectionPool) 75 76 Expect(results).To(HaveLen(1)) 77 compositeType.Attributes[0].Comment = "'name comment'" 78 structmatcher.ExpectStructsToMatchExcluding(&compositeType, &results[0], "Oid", "Attributes.CompositeTypeOid") 79 }) 80 It("returns a slice of composite types with collations", func() { 81 testutils.SkipIfBefore6(connectionPool) 82 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX');`) 83 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 84 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.composite_type AS (name int4, name2 numeric(8,2), name1 character(8) COLLATE public.some_coll);") 85 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.composite_type") 86 87 results := backup.GetCompositeTypes(connectionPool) 88 89 Expect(results).To(HaveLen(1)) 90 compositeType.Attributes = []backup.Attribute{ 91 {Name: "name", Type: "integer"}, 92 {Name: "name2", Type: "numeric(8,2)"}, 93 {Name: "name1", Type: "character(8)", Collation: "public.some_coll"}, 94 } 95 structmatcher.ExpectStructsToMatchExcluding(&compositeType, &results[0], "Oid", "Attributes.CompositeTypeOid") 96 }) 97 It("returns a slice for a base type with default values", func() { 98 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_type") 99 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.base_type CASCADE") 100 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_fn_in(cstring) RETURNS public.base_type AS 'boolin' LANGUAGE internal") 101 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_fn_out(public.base_type) RETURNS cstring AS 'boolout' LANGUAGE internal") 102 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_type(INPUT=public.base_fn_in, OUTPUT=public.base_fn_out)") 103 104 results := backup.GetBaseTypes(connectionPool) 105 106 Expect(results).To(HaveLen(1)) 107 if false { 108 structmatcher.ExpectStructsToMatchExcluding(&baseTypeDefault, &results[0], "Oid", "ModIn", "ModOut") 109 } else { 110 structmatcher.ExpectStructsToMatchExcluding(&baseTypeDefault, &results[0], "Oid") 111 } 112 }) 113 It("returns a slice for a base type with custom configuration", func() { 114 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_type") 115 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.base_type CASCADE") 116 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_fn_in(cstring) RETURNS public.base_type AS 'boolin' LANGUAGE internal") 117 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_fn_out(public.base_type) RETURNS cstring AS 'boolout' LANGUAGE internal") 118 if false { 119 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_type(INPUT=public.base_fn_in, OUTPUT=public.base_fn_out, INTERNALLENGTH=8, PASSEDBYVALUE, ALIGNMENT=double, STORAGE=plain, DEFAULT=0, ELEMENT=integer, DELIMITER=';')") 120 } else { 121 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_type(INPUT=public.base_fn_in, OUTPUT=public.base_fn_out, INTERNALLENGTH=8, PASSEDBYVALUE, ALIGNMENT=double, STORAGE=plain, DEFAULT=0, ELEMENT=integer, DELIMITER=';', CATEGORY='N', PREFERRED=true, COLLATABLE=true)") 122 } 123 testhelper.AssertQueryRuns(connectionPool, "ALTER TYPE public.base_type SET DEFAULT ENCODING (compresstype=zlib)") 124 125 results := backup.GetBaseTypes(connectionPool) 126 127 Expect(results).To(HaveLen(1)) 128 baseTypeCustom.Category = "N" 129 baseTypeCustom.Preferred = true 130 baseTypeCustom.Collatable = true 131 structmatcher.ExpectStructsToMatchExcluding(&baseTypeCustom, &results[0], "Oid") 132 }) 133 It("returns a slice for a user-created array type", func() { 134 arrayType := backup.BaseType{ 135 Oid: 1, Schema: "public", Name: "base_array_type", Input: "public.base_array_fn_in", Output: "public.base_array_fn_out", Receive: "", 136 Send: "", ModIn: "", ModOut: "", InternalLength: -1, IsPassedByValue: false, Alignment: "i", Storage: "p", 137 DefaultVal: "", Element: "text", Delimiter: ",", Category: "U", 138 } 139 140 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_array_type") 141 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.base_array_type CASCADE") 142 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_array_fn_in(cstring) RETURNS public.base_array_type AS 'boolin' LANGUAGE internal") 143 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION public.base_array_fn_out(public.base_array_type) RETURNS cstring AS 'boolout' LANGUAGE internal") 144 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.base_array_type(INPUT=public.base_array_fn_in, OUTPUT=public.base_array_fn_out, ELEMENT=text)") 145 146 results := backup.GetBaseTypes(connectionPool) 147 148 Expect(results).To(HaveLen(1)) 149 structmatcher.ExpectStructsToMatchExcluding(&arrayType, &results[0], "Oid") 150 }) 151 It("returns a slice for an enum type", func() { 152 testutils.SkipIfBefore5(connectionPool) 153 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.enum_type AS ENUM ('label1','label2','label3')") 154 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.enum_type") 155 156 results := backup.GetEnumTypes(connectionPool) 157 158 Expect(results).To(HaveLen(1)) 159 structmatcher.ExpectStructsToMatchExcluding(&enumType, &results[0], "Oid") 160 }) 161 It("returns a slice for enum types with labels in the correct order", func() { 162 testutils.SkipIfBefore5(connectionPool) 163 164 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.enum_type AS ENUM ('label1','label2','label3')") 165 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.enum_type") 166 if false { 167 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.enum_type2 AS ENUM ('label3','label2','label1')") 168 } else { 169 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.enum_type2 AS ENUM ('label3', 'label1')") 170 testhelper.AssertQueryRuns(connectionPool, "ALTER TYPE public.enum_type2 ADD VALUE 'label2' BEFORE 'label1'") 171 } 172 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.enum_type2") 173 174 results := backup.GetEnumTypes(connectionPool) 175 176 Expect(results).To(HaveLen(2)) 177 structmatcher.ExpectStructsToMatchExcluding(&enumType, &results[0], "Oid") 178 structmatcher.ExpectStructsToMatchExcluding(&enumType2, &results[1], "Oid") 179 }) 180 It("does not return types for sequences or views", func() { 181 testhelper.AssertQueryRuns(connectionPool, "CREATE SEQUENCE public.my_sequence START 10") 182 defer testhelper.AssertQueryRuns(connectionPool, "DROP SEQUENCE public.my_sequence") 183 testhelper.AssertQueryRuns(connectionPool, "CREATE VIEW public.simpleview AS SELECT rolname FROM pg_roles") 184 defer testhelper.AssertQueryRuns(connectionPool, "DROP VIEW public.simpleview") 185 186 bases := backup.GetBaseTypes(connectionPool) 187 composites := backup.GetCompositeTypes(connectionPool) 188 189 Expect(bases).To(BeEmpty()) 190 Expect(composites).To(BeEmpty()) 191 }) 192 It("does not return types for foreign tables", func() { 193 testutils.SkipIfBefore6(connectionPool) 194 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN DATA WRAPPER foreignwrapper") 195 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN DATA WRAPPER foreignwrapper") 196 testhelper.AssertQueryRuns(connectionPool, "CREATE SERVER foreignserver FOREIGN DATA WRAPPER foreignwrapper") 197 defer testhelper.AssertQueryRuns(connectionPool, "DROP SERVER foreignserver") 198 testhelper.AssertQueryRuns(connectionPool, "CREATE FOREIGN TABLE public.ft1 (c1 integer) SERVER foreignserver;") 199 defer testhelper.AssertQueryRuns(connectionPool, "DROP FOREIGN TABLE public.ft1") 200 201 results := backup.GetCompositeTypes(connectionPool) 202 203 Expect(results).To(BeEmpty()) 204 }) 205 It("does not return implicit base or composite types for tables with length > NAMEDATALEN", func() { 206 testhelper.AssertQueryRuns(connectionPool, "CREATE TABLE public.looooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooong(i int)") 207 // The table's name will be truncated to 63 characters upon creation, as will the names of its implicit types 208 defer testhelper.AssertQueryRuns(connectionPool, "DROP TABLE public.loooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo;") 209 210 bases := backup.GetBaseTypes(connectionPool) 211 composites := backup.GetCompositeTypes(connectionPool) 212 213 Expect(bases).To(BeEmpty()) 214 Expect(composites).To(BeEmpty()) 215 }) 216 It("returns a slice for a domain type", func() { 217 domainType := backup.Domain{ 218 Oid: 1, Schema: "public", Name: "domain1", DefaultVal: "'abc'::bpchar", BaseType: "character(8)", NotNull: false, Collation: "", 219 } 220 testhelper.AssertQueryRuns(connectionPool, "CREATE DOMAIN public.domain1 AS character(8) DEFAULT 'abc'") 221 defer testhelper.AssertQueryRuns(connectionPool, "DROP DOMAIN public.domain1") 222 223 results := backup.GetDomainTypes(connectionPool) 224 225 Expect(results).To(HaveLen(1)) 226 structmatcher.ExpectStructsToMatchExcluding(&domainType, &results[0], "Oid") 227 }) 228 It("returns a slice for a domain type with a collation", func() { 229 testutils.SkipIfBefore6(connectionPool) 230 domainType := backup.Domain{ 231 Oid: 1, Schema: "public", Name: "domain1", DefaultVal: "'abc'::bpchar", BaseType: "character(8)", NotNull: false, Collation: "public.some_coll", 232 } 233 testhelper.AssertQueryRuns(connectionPool, "CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX')") 234 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 235 testhelper.AssertQueryRuns(connectionPool, "CREATE DOMAIN public.domain1 AS character(8) DEFAULT 'abc' COLLATE public.some_coll") 236 defer testhelper.AssertQueryRuns(connectionPool, "DROP DOMAIN public.domain1") 237 238 results := backup.GetDomainTypes(connectionPool) 239 240 Expect(results).To(HaveLen(1)) 241 structmatcher.ExpectStructsToMatchExcluding(&domainType, &results[0], "Oid") 242 }) 243 It("returns a slice for a type in a specific schema", func() { 244 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE public.shell_type") 245 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.shell_type") 246 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 247 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 248 testhelper.AssertQueryRuns(connectionPool, "CREATE TYPE testschema.shell_type") 249 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE testschema.shell_type") 250 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 251 252 results := backup.GetShellTypes(connectionPool) 253 shellTypeOtherSchema := backup.ShellType{Schema: "testschema", Name: "shell_type"} 254 255 Expect(results).To(HaveLen(1)) 256 structmatcher.ExpectStructsToMatchExcluding(&shellTypeOtherSchema, &results[0], "Oid") 257 }) 258 }) 259 Describe("GetRangeTypes", func() { 260 It("returns a slice of a range type with a collation", func() { 261 testutils.SkipIfBefore6(connectionPool) 262 testhelper.AssertQueryRuns(connectionPool, "CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX');") 263 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 264 testhelper.AssertQueryRuns(connectionPool, `CREATE TYPE public.textrange AS RANGE ( 265 SUBTYPE = pg_catalog.text, 266 COLLATION = public.some_coll 267 );`) 268 defer testhelper.AssertQueryRuns(connectionPool, "DROP TYPE public.textrange") 269 results := backup.GetRangeTypes(connectionPool) 270 271 Expect(len(results)).To(Equal(1)) 272 273 expectedRangeType := backup.RangeType{ 274 Oid: 0, 275 Schema: "public", 276 Name: "textrange", 277 SubType: "text", 278 Collation: "public.some_coll", 279 SubTypeOpClass: "pg_catalog.text_ops", 280 } 281 structmatcher.ExpectStructsToMatchExcluding(&expectedRangeType, &results[0], "Oid") 282 }) 283 It("returns a slice of a range type in a specific schema with a subtype diff function", func() { 284 testutils.SkipIfBefore6(connectionPool) 285 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema;") 286 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema CASCADE;") 287 testhelper.AssertQueryRuns(connectionPool, "CREATE FUNCTION testschema.time_subtype_diff(x time, y time) RETURNS float8 AS 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;") 288 testhelper.AssertQueryRuns(connectionPool, `CREATE TYPE testschema.timerange AS RANGE ( 289 SUBTYPE = pg_catalog.time, 290 SUBTYPE_DIFF = testschema.time_subtype_diff 291 );`) 292 293 results := backup.GetRangeTypes(connectionPool) 294 295 Expect(len(results)).To(Equal(1)) 296 297 expectedRangeType := backup.RangeType{ 298 Oid: 0, 299 Schema: "testschema", 300 Name: "timerange", 301 SubType: "time without time zone", 302 SubTypeOpClass: "pg_catalog.time_ops", 303 SubTypeDiff: "testschema.time_subtype_diff", 304 } 305 structmatcher.ExpectStructsToMatchExcluding(&expectedRangeType, &results[0], "Oid") 306 }) 307 }) 308 Describe("GetCollations", func() { 309 It("returns a slice of collations", func() { 310 testutils.SkipIfBefore6(connectionPool) 311 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX');`) 312 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 313 314 results := backup.GetCollations(connectionPool) 315 316 Expect(results).To(HaveLen(1)) 317 318 collationDef := backup.Collation{Oid: 0, Schema: "public", Name: "some_coll", Collate: "POSIX", Ctype: "POSIX"} 319 if true { 320 collationDef.IsDeterministic = "true" 321 collationDef.Provider = "c" 322 } 323 structmatcher.ExpectStructsToMatchExcluding(&collationDef, &results[0], "Oid") 324 325 }) 326 It("returns a slice of collations in a specific schema", func() { 327 testutils.SkipIfBefore6(connectionPool) 328 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION public.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX');`) 329 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 330 testhelper.AssertQueryRuns(connectionPool, "CREATE SCHEMA testschema") 331 defer testhelper.AssertQueryRuns(connectionPool, "DROP SCHEMA testschema") 332 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION testschema.some_coll (lc_collate = 'POSIX', lc_ctype = 'POSIX');`) 333 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION testschema.some_coll") 334 _ = backupCmdFlags.Set(options.INCLUDE_SCHEMA, "testschema") 335 336 results := backup.GetCollations(connectionPool) 337 338 Expect(results).To(HaveLen(1)) 339 340 collationDef := backup.Collation{Oid: 0, Schema: "testschema", Name: "some_coll", Collate: "POSIX", Ctype: "POSIX"} 341 if true { 342 collationDef.IsDeterministic = "true" 343 collationDef.Provider = "c" 344 } 345 structmatcher.ExpectStructsToMatchExcluding(&collationDef, &results[0], "Oid") 346 347 }) 348 It("returns a slice of with specific collation", func() { 349 testutils.SkipIfBefore7(connectionPool) 350 testhelper.AssertQueryRuns(connectionPool, `CREATE COLLATION public.some_coll (provider = 'libc', locale = 'de_DE');`) 351 defer testhelper.AssertQueryRuns(connectionPool, "DROP COLLATION public.some_coll") 352 353 results := backup.GetCollations(connectionPool) 354 355 Expect(results).To(HaveLen(1)) 356 357 collationDef := backup.Collation{Oid: 0, Schema: "public", Name: "some_coll", Collate: "de_DE", Ctype: "de_DE", Provider: "c", IsDeterministic: "true"} 358 structmatcher.ExpectStructsToMatchExcluding(&collationDef, &results[0], "Oid") 359 360 }) 361 }) 362 })