FilesExpand file tree

master

/

PostgresDialectTest.php

Copy path

Blame

More file actions

Blame

More file actions

Latest commit

 

History

History

History

191 lines (159 loc) · 7.83 KB

master

/

PostgresDialectTest.php

Copy path

Top

File metadata and controls

• Code
• Blame

191 lines (159 loc) · 7.83 KB

Raw

Copy raw file

Download raw file

Open symbols panel

Edit and raw actions

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

<?php

namespace DBDiff\Tests\Unit;

use PHPUnit\Framework\TestCase;

use DBDiff\SQLGen\Dialect\PostgresDialect;

class PostgresDialectTest extends TestCase

{

private PostgresDialect $dialect;

protected function setUp(): void

{

$this->dialect = new PostgresDialect();

}

// ── Quoting ───────────────────────────────────────────────────────────

public function testQuoteSimpleName(): void

{

$this->assertSame('"users"', $this->dialect->quote('users'));

}

public function testQuoteHyphenatedName(): void

{

$this->assertSame('"my-table"', $this->dialect->quote('my-table'));

}

public function testQuoteNameWithDoubleQuote(): void

{

$this->assertSame('"name""with""quotes"', $this->dialect->quote('name"with"quotes'));

}

public function testQuoteNameWithSpaces(): void

{

$this->assertSame('"table name"', $this->dialect->quote('table name'));

}

// ── Driver ────────────────────────────────────────────────────────────

public function testGetDriver(): void

{

$this->assertSame('pgsql', $this->dialect->getDriver());

}

public function testIsNotMySQLOnly(): void

{

$this->assertFalse($this->dialect->isMySQLOnly());

}

// ── DROP TRIGGER requires ON table ────────────────────────────────────

public function testDropTriggerIncludesOnTable(): void

{

$sql = $this->dialect->dropTrigger('trg_audit', 'orders');

$this->assertSame('DROP TRIGGER IF EXISTS "trg_audit" ON "orders";', $sql);

}

public function testDropTriggerQuotesSpecialNames(): void

{

$sql = $this->dialect->dropTrigger('my-trigger', 'my-table');

$this->assertSame('DROP TRIGGER IF EXISTS "my-trigger" ON "my-table";', $sql);

}

// ── DROP INDEX (schema-level, no ALTER TABLE) ─────────────────────────

public function testDropIndexNoAlterTable(): void

{

$sql = $this->dialect->dropIndex('orders', 'idx_orders_user');

$this->assertSame('DROP INDEX "idx_orders_user";', $sql);

}

// ── ADD/DROP COLUMN ───────────────────────────────────────────────────

public function testAddColumn(): void

{

$sql = $this->dialect->addColumn('users', '"phone" varchar(20) DEFAULT NULL');

$this->assertSame('ALTER TABLE "users" ADD COLUMN "phone" varchar(20) DEFAULT NULL;', $sql);

}

public function testDropColumn(): void

{

$sql = $this->dialect->dropColumn('users', 'phone');

$this->assertSame('ALTER TABLE "users" DROP COLUMN "phone" CASCADE;', $sql);

}

// ── Column changes with an ALTER of their own ─────────────────────────

public function testAnIdentityKindAndOptionsChangeInPlace(): void

{

$this->assertSame(

"ALTER TABLE \"t\" ALTER COLUMN \"id\" SET GENERATED BY DEFAULT;\n"

. 'ALTER TABLE "t" ALTER COLUMN "id" SET INCREMENT BY 10;',

$this->dialect->changeColumn(

't', 'id',

'"id" integer GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 10)',

'"id" integer GENERATED ALWAYS AS IDENTITY'

)

);

}

public function testAnIdentityRetypedKeepsTheIdentity(): void

{

$this->assertSame(

'ALTER TABLE "t" ALTER COLUMN "id" TYPE bigint;',

$this->dialect->changeColumn('t', 'id', '"id" bigint GENERATED ALWAYS AS IDENTITY', '"id" integer GENERATED ALWAYS AS IDENTITY')

);

}

public function testAPlainColumnBecomingAnIdentityStartsPastItsValues(): void

{

$sql = $this->dialect->changeColumn('t', 'id', '"id" integer NOT NULL GENERATED ALWAYS AS IDENTITY', '"id" integer NOT NULL');

$this->assertStringContainsString('ALTER COLUMN "id" ADD GENERATED ALWAYS AS IDENTITY;', $sql);

$this->assertStringContainsString("SELECT setval(pg_get_serial_sequence('\"t\"', 'id'), COALESCE(MAX(\"id\"), 0) + 1, false) FROM \"t\";", $sql);

$this->assertStringNotContainsString('DROP IDENTITY', $sql);

}

public function testASerialColumnBecomingAnIdentityLosesItsSequenceFirst(): void

{

$this->assertSame(

"ALTER TABLE \"t\" ALTER COLUMN \"id\" DROP DEFAULT;\n"

. "DROP SEQUENCE IF EXISTS public.t_id_seq;\n"

. "ALTER TABLE \"t\" ALTER COLUMN \"id\" ADD GENERATED BY DEFAULT AS IDENTITY;\n"

. "SELECT setval(pg_get_serial_sequence('\"t\"', 'id'), COALESCE(MAX(\"id\"), 0) + 1, false) FROM \"t\";",

$this->dialect->withSerialSequence('public.t_id_seq')

->changeColumn('t', 'id', '"id" integer NOT NULL GENERATED BY DEFAULT AS IDENTITY', '"id" serial')

);

}

public function testAnIdentityBecomingSerialGetsAnOwnedSequence(): void

{

$this->assertSame(

"ALTER TABLE \"t\" ALTER COLUMN \"id\" DROP IDENTITY;\n"

. "CREATE SEQUENCE IF NOT EXISTS public.t_id_seq AS bigint;\n"

. "ALTER SEQUENCE public.t_id_seq OWNED BY \"t\".\"id\";\n"

. "ALTER TABLE \"t\" ALTER COLUMN \"id\" SET DEFAULT nextval('public.t_id_seq'::regclass);\n"

. "SELECT setval('public.t_id_seq', COALESCE(MAX(\"id\"), 0) + 1, false) FROM \"t\";",

$this->dialect->withSerialSequence('public.t_id_seq')

->changeColumn('t', 'id', '"id" bigserial', '"id" bigint NOT NULL GENERATED ALWAYS AS IDENTITY')

);

}

public function testARetypedSerialTakesItsSequenceWithIt(): void

{

$this->assertSame(

"ALTER TABLE \"t\" ALTER COLUMN \"id\" TYPE bigint;\n"

. 'ALTER SEQUENCE public.t_id_seq AS bigint;',

$this->dialect->withSerialSequence('public.t_id_seq', 'bigint')->changeColumn('t', 'id', '"id" bigserial', '"id" serial')

);

}

public function testARetypedSerialLeavesASequenceAlreadyOfTheRightType(): void

{

// ALTER COLUMN TYPE does not retype the sequence, so the source's

// may well still be bigint: nothing to do then.

$this->assertSame(

'ALTER TABLE "t" ALTER COLUMN "id" TYPE integer;',

$this->dialect->withSerialSequence('public.t_id_seq')->changeColumn('t', 'id', '"id" serial', '"id" bigserial')

);

}

public function testASerialSequenceNameIsGuessedWhenNotRead(): void

{

$this->assertStringContainsString(

'DROP SEQUENCE IF EXISTS "t_id_seq";',

$this->dialect->changeColumn('t', 'id', '"id" integer NOT NULL', '"id" serial')

);

}

public function testCompressionIsSetAgainAfterATypeChange(): void

{

$this->assertSame(

"ALTER TABLE \"t\" ALTER COLUMN \"b\" TYPE character varying(20);\n"

. 'ALTER TABLE "t" ALTER COLUMN "b" SET COMPRESSION lz4;',

$this->dialect->changeColumn('t', 'b', '"b" character varying(20) COMPRESSION lz4', '"b" text COMPRESSION lz4')

);

$this->assertSame(

'ALTER TABLE "t" ALTER COLUMN "b" SET COMPRESSION default;',

$this->dialect->changeColumn('t', 'b', '"b" text', '"b" text COMPRESSION lz4')

);

}

public function testAGeneratedColumnBecomingPlainKeepsItsValues(): void

{

$this->assertSame(

'ALTER TABLE "t" ALTER COLUMN "g" DROP EXPRESSION;',

$this->dialect->changeColumn('t', 'g', '"g" integer', '"g" integer GENERATED ALWAYS AS ((a * 2)) STORED')

);

}

}