/
extensions.pg
180 lines (151 loc) · 6.57 KB
/
extensions.pg
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
SET search_path = public,contrib,tap;
BEGIN;
SELECT plan(99);
--SELECT * FROM no_plan();
SELECT has_table('public', 'extensions', 'Should have table public.extensions');
SELECT columns_are('public', 'extensions', ARRAY[
'name',
'owner',
'created_at',
'updated_at'
]);
SELECT has_table( 'extensions' );
SELECT has_pk( 'extensions' );
SELECT has_fk( 'extensions' );
SELECT has_column( 'extensions', 'name' );
SELECT col_type_is( 'extensions', 'name', 'citext' );
SELECT col_hasnt_default( 'extensions', 'name' );
SELECT col_is_pk( 'extensions', 'name' );
SELECT has_column( 'extensions', 'owner' );
SELECT col_type_is( 'extensions', 'owner', 'label' );
SELECT col_not_null( 'extensions', 'owner' );
SELECT col_hasnt_default( 'extensions', 'owner' );
SELECT col_is_fk( 'extensions', 'owner' );
SELECT fk_ok( 'extensions', 'owner', 'users', 'nickname');
SELECT has_column( 'extensions', 'created_at' );
SELECT col_type_is( 'extensions', 'created_at', 'timestamp with time zone' );
SELECT col_not_null( 'extensions', 'created_at' );
SELECT col_has_default( 'extensions', 'created_at' );
SELECT col_default_is( 'extensions', 'created_at', 'now()' );
SELECT has_column( 'extensions', 'updated_at' );
SELECT col_type_is( 'extensions', 'updated_at', 'timestamp with time zone' );
SELECT col_not_null( 'extensions', 'updated_at' );
SELECT col_has_default( 'extensions', 'updated_at' );
SELECT col_default_is( 'extensions', 'updated_at', 'now()' );
-- Check privileges.
SELECT ok(
has_table_privilege('pgxn', 'extensions', 'SELECT'),
'User "pgxn" should have SELECT priv on extensions table'
);
SELECT ok(
NOT has_table_privilege('pgxn', 'extensions', priv),
'User "pgxn" should not have ' || priv || ' priv on extensions table'
) FROM unnest(ARRAY[
'UPDATE',
'INSERT',
'DELETE',
'TRUNCATE',
'REFERENCES',
'TRIGGER'
]) AS priv;
/****************************************************************************/
-- Test coowners.
SELECT has_table('public', 'coowners', 'Should have table public.coowners');
SELECT columns_are('public', 'coowners', ARRAY[
'extension',
'nickname',
'created_at'
]);
SELECT has_table( 'coowners' );
SELECT has_pk( 'coowners' );
SELECT has_fk( 'coowners' );
SELECT has_column( 'coowners', 'extension' );
SELECT col_type_is( 'coowners', 'extension', 'citext' );
SELECT col_not_null( 'coowners', 'extension' );
SELECT col_hasnt_default( 'coowners', 'extension' );
SELECT col_is_fk( 'coowners', 'extension' );
SELECT fk_ok( 'coowners', 'extension', 'extensions', 'name' );
SELECT has_column( 'coowners', 'nickname' );
SELECT col_type_is( 'coowners', 'nickname', 'label' );
SELECT col_not_null( 'coowners', 'nickname' );
SELECT col_hasnt_default( 'coowners', 'nickname' );
SELECT col_is_fk( 'coowners', 'nickname' );
SELECT fk_ok( 'coowners', 'nickname', 'users', 'nickname');
SELECT col_is_pk( 'coowners', ARRAY['extension', 'nickname'] );
SELECT has_column( 'coowners', 'created_at' );
SELECT col_type_is( 'coowners', 'created_at', 'timestamp with time zone' );
SELECT col_not_null( 'coowners', 'created_at' );
SELECT col_has_default( 'coowners', 'created_at' );
SELECT col_default_is( 'coowners', 'created_at', 'now()' );
-- Check privileges.
SELECT ok(
NOT has_table_privilege('pgxn', 'coowners', priv),
'User "pgxn" should not have ' || priv || ' priv on coowners table'
) FROM unnest(ARRAY[
'SELECT',
'UPDATE',
'INSERT',
'DELETE',
'TRUNCATE',
'REFERENCES',
'TRIGGER'
]) AS priv;
/****************************************************************************/
-- Test distribution_extensions.
SELECT has_table('public', 'distribution_extensions', 'Should have table public.distribution_extensions');
SELECT columns_are('public', 'distribution_extensions', ARRAY[
'extension',
'ext_version',
'abstract',
'distribution',
'dist_version'
]);
SELECT has_table( 'distribution_extensions' );
SELECT has_pk( 'distribution_extensions' );
SELECT has_fk( 'distribution_extensions' );
SELECT has_column( 'distribution_extensions', 'extension' );
SELECT col_type_is( 'distribution_extensions', 'extension', 'citext' );
SELECT col_not_null( 'distribution_extensions', 'extension' );
SELECT col_hasnt_default( 'distribution_extensions', 'extension' );
SELECT col_is_fk( 'distribution_extensions', 'extension' );
SELECT fk_ok( 'distribution_extensions', 'extension', 'extensions', 'name' );
SELECT has_column( 'distribution_extensions', 'ext_version' );
SELECT col_type_is( 'distribution_extensions', 'ext_version', 'semver' );
SELECT col_not_null( 'distribution_extensions', 'ext_version' );
SELECT col_hasnt_default( 'distribution_extensions', 'ext_version' );
SELECT col_is_pk( 'distribution_extensions', ARRAY['extension', 'ext_version', 'distribution', 'dist_version'] );
SELECT has_column( 'distribution_extensions', 'abstract' );
SELECT col_type_is( 'distribution_extensions', 'abstract', 'text' );
SELECT col_not_null( 'distribution_extensions', 'abstract' );
SELECT col_hasnt_default( 'distribution_extensions', 'abstract' );
SELECT has_column( 'distribution_extensions', 'distribution' );
SELECT col_type_is( 'distribution_extensions', 'distribution', 'citext' );
SELECT col_not_null( 'distribution_extensions', 'distribution' );
SELECT col_hasnt_default( 'distribution_extensions', 'distribution' );
SELECT has_column( 'distribution_extensions', 'dist_version' );
SELECT col_type_is( 'distribution_extensions', 'dist_version', 'semver' );
SELECT col_not_null( 'distribution_extensions', 'dist_version' );
SELECT col_hasnt_default( 'distribution_extensions', 'dist_version' );
SELECT col_is_fk('distribution_extensions', ARRAY['distribution', 'dist_version'] );
SELECT fk_ok(
'distribution_extensions', ARRAY['distribution', 'dist_version'],
'distributions', ARRAY['name', 'version']
);
-- Check privileges.
SELECT ok(
has_table_privilege('pgxn', 'distribution_extensions', 'SELECT'),
'User "pgxn" should have SELECT priv on distribution_extensions table'
);
SELECT ok(
NOT has_table_privilege('pgxn', 'distribution_extensions', priv),
'User "pgxn" should not have ' || priv || ' priv on distribution_extensions table'
) FROM unnest(ARRAY[
'UPDATE',
'INSERT',
'DELETE',
'TRUNCATE',
'REFERENCES',
'TRIGGER'
]) AS priv;
SELECT * FROM finish();
ROLLBACK;