-
Notifications
You must be signed in to change notification settings - Fork 3
/
create_sql.sh
executable file
·314 lines (270 loc) · 13.1 KB
/
create_sql.sh
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
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
#!/bin/bash
# The script creates the SQL script to create the files needed for PGXN
# Accept an argument whether the zip file for pgxn.org should be overridden
if [ -z "$1" ]; then
PGXN='N'
else
PGXN=$1
fi # [ -z "$1" ]
# Copy the build.cfg.example to build.cfg and edit the configuration to match your nees
# Include the local configuration
source ./build.cfg
# Starts with the script to create all objects
# Define output file
EXTENSION=$(grep -m 1 '"name":' META.json | \
sed -e 's/[[:space:]]*"name":[[:space:]]*"\([^"]*\)",/\1/')
EXTVERSION=$(grep -m 1 '"version":' META.json | \
sed -e 's/[[:space:]]*"version":[[:space:]]*"\([^"]*\)",/\1/')
# Folders
UNINSTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql/out/uninstall"
VERSIONDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql/out/versions"
TESTDIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/test/sql/out"
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql"
# Uninstall file with drop statements
FILENAME="$UNINSTDIR/$EXTENSION""_uninstall--$EXTVERSION.sql"
# Always start with an empty file
truncate -s 0 $FILENAME
echo '/*** uninstall file to drop all objects created by the extension pgsql_tweaks ***/' >> $FILENAME
echo '' >> $FILENAME
echo 'BEGIN;' >> $FILENAME
echo '' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_active_locks;' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_empty(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_empty_b(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a BIGINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a INTEGER[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a SMALLINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a REAL[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a DOUBLE PRECISION[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_sum(a NUMERIC[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a BIGINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a INTEGER[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a SMALLINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a REAL[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a DOUBLE PRECISION[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_avg(a NUMERIC[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a TEXT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a BIGINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a INTEGER[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a SMALLINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a REAL[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a DOUBLE PRECISION[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_min(a NUMERIC[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a TEXT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a BIGINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a INTEGER[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a NUMERIC[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a REAL[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a DOUBLE PRECISION[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_max(a SMALLINT[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a text[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a SMALLINT[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a INTEGER[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a BIGINT[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a NUMERIC[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a REAL[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a DOUBLE PRECISION[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a DATE[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a TIMESTAMP[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS array_trim(a TIMESTAMP WITH TIME ZONE[], rd BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS to_unix_timestamp(ts timestamp with time zone);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS to_unix_timestamp(ts timestamp);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS datetime_de(t TIMESTAMP WITH TIME ZONE, with_tz BOOLEAN);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS date_de(d DATE);' >> $FILENAME
echo 'DROP AGGREGATE IF EXISTS gap_fill(anyelement);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS gap_fill_internal(s anyelement, v anyelement);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_latin1(s text, s_search text[], s_replace text[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_latin1(s text, replacement text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_latin1(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_encoding(s text, s_search text[], s_replace text[]);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_encoding(s text, e text, replacement text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS replace_encoding(s text, e text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS return_not_part_of_encoding(s text, e text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS return_not_part_of_latin1(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_latin1(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_encoding(s text, enc text, enc_from text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_encoding(s text, enc text);' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_functions;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_foreign_keys;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_db_views;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_table_matview_infos;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_object_ownership;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_bloat_info;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_unused_indexes;' >> $FILENAME
echo 'DROP VIEW IF EXISTS pg_partitioned_tables_infos;' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS pg_schema_size(text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS sha256(bytea);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_bigint(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_integer(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_smallint(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_numeric(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_real(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_double_precision(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_boolean(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_json(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_timestamp(s text, f text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_timestamp(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_time(s text, f text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_time(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_date(s text, f text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_json(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_jsonb(s text);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_hex(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_uuid(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS hex2bigint(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_bigint_array(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_integer_array(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_smallint_array(s TEXT);' >> $FILENAME
echo 'DROP FUNCTION IF EXISTS is_text_array(s TEXT);' >> $FILENAME
echo '' >> $FILENAME
echo 'END;' >> $FILENAME
DROPFILE=$FILENAME
# Installation file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/sql"
FILENAME="$VERSIONDIR/$EXTENSION--$EXTVERSION.sql"
# Array with all file names
declare -a SQLFILES=(
"function_is_date"
"function_is_time"
"function_is_timestamp"
"function_is_numeric"
"function_is_bigint"
"function_is_integer"
"function_is_smallint"
"function_is_real"
"function_is_double_precision"
"function_is_boolean"
"function_is_json"
"function_is_jsonb"
"function_is_uuid"
"function_sha256"
"function_pg_schema_size"
"view_pg_db_views"
"view_pg_foreign_keys"
"view_pg_functions"
"view_pg_table_matview_infos"
"view_pg_partitioned_tables_infos"
"function_is_encoding"
"function_is_latin1"
"function_return_not_part_of_latin1"
"function_replace_encoding"
"function_replace_latin1"
"function_return_not_part_of_encoding"
"aggregate_function_gap_fill"
"function_date_de"
"function_datetime_de"
"function_to_unix_timestamp"
"function_is_empty"
"function_array_max"
"function_array_min"
"function_array_avg"
"function_array_sum"
"function_array_trim"
"view_pg_active_locks"
"view_pg_object_ownership"
"view_pg_bloat_info"
"view_pg_unused_indexes"
"function_hex2bigint"
"function_is_hex"
"function_is_bigint_array"
"function_is_integer_array"
"function_is_smallint_array"
"function_is_text_array"
)
arraylength=${#SQLFILES[@]}
# Always start with an empty file
truncate -s 0 $FILENAME
# Add licencse information
echo '/**' >> $FILENAME
echo ' * PostgreSQL pgsql_tweaks extension' >> $FILENAME
echo ' * Licence: PostgreSQL Licence, see https://raw.githubusercontent.com/sjstoelting/pgsql-tweaks/master/LICENSE.md' >> $FILENAME
echo ' * Author: Stefanie Janine Stölting <[email protected]>' >> $FILENAME
echo ' * Repository: http://github.com/sjstoelting/pgsql_tweaks/' >> $FILENAME
echo ' * Version: '$EXTVERSION >> $FILENAME
echo ' */' >> $FILENAME
# Add initial statements
echo '' >> $FILENAME
echo '/*** initial statements ***/' >> $FILENAME
echo 'SET client_min_messages TO warning;' >> $FILENAME
echo 'SET log_min_messages TO warning;' >> $FILENAME
echo '' >> $FILENAME
# It is not allowed to drop functions in the script to create the extension
# cat $DROPFILE >> $FILENAME
echo '' >> $FILENAME
echo '/*** files with creation statements ***/' >> $FILENAME
echo '' >> $FILENAME
for (( i=1; i<${arraylength}+1; i++ ));
do
cat $DIR/${SQLFILES[$i-1]}".sql" >> $FILENAME
echo '' >> $FILENAME
done # (( i=1; i<${arraylength}+1; i++ ))
# Now the test script has to be generated
# Define output file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )/test/sql"
FILENAME="$TESTDIR/$EXTENSION""_test--$EXTVERSION.sql"
# Always start with an empty file
truncate -s 0 $FILENAME
# Add initial statements
# Timing is only on when not creating versions
if [ "$PGXN" != "y" ]; then
echo '\timing' >> $FILENAME
fi # [ "$PGXN" != "y" ]
echo 'SET client_min_messages TO warning;' >> $FILENAME
echo 'SET log_min_messages TO warning;' >> $FILENAME
echo '' >> $FILENAME
echo '/*** files with test statements ***/' >> $FILENAME
echo '' >> $FILENAME
for (( i=1; i<${arraylength}+1; i++ ));
do
echo "SELECT 'Test starting: ${SQLFILES[$i-1]}' AS next_test;" >> $FILENAME
cat $DIR/${SQLFILES[$i-1]}".sql" >> $FILENAME
echo '' >> $FILENAME
done # (( i=1; i<${arraylength}+1; i++ ))
# Create control file
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
FILENAME="$DIR/pgsql_tweaks.control"
# Always start with an empty file
truncate -s 0 $FILENAME
# Control data
echo "# pgsql_tweaks extension" >> $FILENAME
echo "comment = 'Some functions and views for daily usage'" >> $FILENAME
echo "default_version = '$EXTVERSION'" >> $FILENAME
echo "module_pathname = '\$libdir/pgsql_tweaks'" >> $FILENAME
echo "relocatable = true" >> $FILENAME
# Create the test data
DIR="$( cd "$( dirname "${BASH_SOURCE[0]}" )" && pwd )"
psql -h $DBHOST -p $DBPORT -X -q -b postgres -c "CREATE DATABASE $DBNAME;"
psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -c "SELECT version ();"
psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/sql/out/versions/pgsql_tweaks--$EXTVERSION.sql"
if [ "$PGXN" = "y" ]; then
# The result messages and captions are exported in English UTF8 en_EN
LC_MESSAGES=en_EN psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.sql" > "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.out"
else
# During development the messages are kept in the local installed language
psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.sql" > "$DIR/test/sql/out/pgsql_tweaks_test--$EXTVERSION.out"
fi # [ "$PGXN" = "y" ]
# Check the statements used in the README file
psql -h $DBHOST -p $DBPORT -X -q -b -v ON_ERROR_STOP=1 $DBNAME -f "$DIR/test/sql/examples.sql" > "/dev/null"
psql -h $DBHOST -p $DBPORT -X -q -b postgres -c "DROP DATABASE $DBNAME;"
# Create the PGXN package, output path is users tmp
if [ "$PGXN" = "y" ]; then
# Create a documentation for PGXN, the link differ from GitHun to PGXN
./create_pgxn_doc.sh
# Create a documentation in HTML
./create_html_doc.sh
echo "Creating pgxn zip file"
# Check if the tmp directory exists, if not, create it
if [ ! -d "$HOME/tmp" ]; then
echo "Directory $HOME/tmp does not exist. Creating it."
mkdir -p "$HOME/tmp"
fi # [ ! -d "$HOME/tmp" ]
rm -f "$HOME/tmp/pgsql-tweaks-$EXTVERSION.zip"
git archive --format zip --prefix=pgsql-tweaks-$EXTVERSION/ --output "$HOME/tmp/pgsql-tweaks-$EXTVERSION.zip" main
else
echo "No pgxn zip file has been created"
fi # [ "$PGXN" = "y" ]
# Unset variables
unset DIR
unset FILENAME
unset i
unset arraylength