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
|
# Each test runs on a clean database.
# Apply schema "1.hcl" on fresh database.
apply 1.hcl
cmpshow users posts 1.sql
apply 2.hcl
cmpshow users posts 2.sql
# Below files represent HCL and SQL. File names defined their index in
# execution order. 1.hcl is executed first, 2.hcl executed second, etc.
-- 1.hcl --
schema "$db" {
charset = "$charset"
collate = "$collate"
}
table "users" {
schema = schema.$db
column "id" {
type = int
}
primary_key {
columns = [table.users.column.id]
}
}
table "posts" {
schema = schema.$db
column "id" {
type = int
}
column "author_id" {
type = int
null = true
}
primary_key {
columns = [table.posts.column.id]
}
foreign_key "owner_id" {
columns = [table.posts.column.author_id]
ref_columns = [table.users.column.id]
on_update = SET_NULL
}
}
-- 1.sql --
CREATE TABLE `users` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `posts` (
`id` int(11) NOT NULL,
`author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `owner_id` (`author_id`),
CONSTRAINT `owner_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE SET NULL
)
-- mysql8/1.sql --
CREATE TABLE `users` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `posts` (
`id` int NOT NULL,
`author_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `owner_id` (`author_id`),
CONSTRAINT `owner_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON UPDATE SET NULL
)
-- 2.hcl --
schema "$db" {
charset = "$charset"
collate = "$collate"
}
table "users" {
schema = schema.$db
column "id" {
type = int
}
primary_key {
columns = [table.users.column.id]
}
}
table "posts" {
schema = schema.$db
column "id" {
type = int
}
column "author_id" {
type = int
null = true
}
primary_key {
columns = [table.posts.column.id]
}
foreign_key "owner_id" {
columns = [table.posts.column.author_id]
ref_columns = [table.users.column.id]
on_update = NO_ACTION
on_delete = CASCADE
}
}
-- 2.sql --
CREATE TABLE `users` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `posts` (
`id` int(11) NOT NULL,
`author_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `owner_id` (`author_id`),
CONSTRAINT `owner_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
)
-- mysql8/2.sql --
CREATE TABLE `users` (
`id` int NOT NULL,
PRIMARY KEY (`id`)
)
CREATE TABLE `posts` (
`id` int NOT NULL,
`author_id` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `owner_id` (`author_id`),
CONSTRAINT `owner_id` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
)
|
