pgsql_tests.sh.in 33.6 KB
Newer Older
1
2
#!/bin/sh

3
# Copyright (C) 2015-2019 Internet Systems Consortium, Inc. ("ISC")
4
#
5
6
7
# This Source Code Form is subject to the terms of the Mozilla Public
# License, v. 2.0. If a copy of the MPL was not distributed with this
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
8
9
10
11

# Include common test library.
. @abs_top_builddir@/src/lib/testutils/dhcp_test_lib.sh

12
13
# Include admin utilities
. @abs_top_srcdir@/src/bin/admin/admin-utils.sh
14

15
16
# Set path to the production schema scripts
db_scripts_dir=@abs_top_srcdir@/src/share/database/scripts
17
18
19
20

db_user="keatest"
db_password="keatest"
db_name="keatest"
21
db_host="localhost"
22
23
24
25

# Set location of the kea-admin.
keaadmin=@abs_top_builddir@/src/bin/admin/kea-admin

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
# Convenience function for running an SQL statement
# param hdr - text message to prepend to any error
# param qry - SQL statement to run
# param exp_valu - optional expected value.  This can be used IF the SQL statement
# generates a single value, such as a SELECT which returns one column for one row.
# Examples:
#
#   qry="insert into lease6 (address, lease_type, subnet_id, state) values ($addr,$ltype,1,0);"
#   run_statement "#2" "$qry"
#
#   qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0";
#   run_statement "#3" "$qry" 1
run_statement() {
    hdr="$1";shift;
    qry="$1";shift;
    exp_value="$1";

    # Execute the statment
    value=`pgsql_execute "${qry}"`
    ERRCODE=$?

    # Execution should succeed
    assert_eq 0 $ERRCODE "$hdr: SQL=[$qry] failed: (expected status code %d, returned %d)"

    # If there's an expected value, test it
    if [ "x$exp_value" != "x" ]
    then
        assert_str_eq "$exp_value" "$value" "$hdr: SQL=[$qry] wrong: (expected value %s, returned %s)"
    fi
}

57
58
59
60
61
# Wipe all tables from the DB:
pgsql_wipe() {
    printf "Wiping whole database %s\n" $db_name
    export PGPASSWORD=$db_password

Tomek Mrugalski's avatar
Tomek Mrugalski committed
62
    cat $db_scripts_dir/pgsql/dhcpdb_drop.pgsql | psql --set ON_ERROR_STOP=1 -A -t -h localhost -q -U keatest -d keatest >/dev/null 2>&1
63
    assert_eq 0 $?  "pgsql_wipe drop failed, expected exit code: %d, actual: %d"
64
65
}

66
67
pgsql_db_init_test() {
    test_start "pgsql.db-init"
68
69
70
71
72

    # Let's wipe the whole database
    pgsql_wipe

    # Create the database
73
74
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 0 $? "kea-admin db-init pgsql failed, expected exit code: %d, actual: %d"
75
76
77
78

    # Verify that all the expected tables exist

    # Check schema_version table
79
    RESULT=`pgsql_execute "SELECT version, minor FROM schema_version;"`
80
    assert_eq 0 $? "schema_version table check failed, expected exit code: %d, actual: %d"
81
82

    # Check lease4 table
83
    RESULT=`pgsql_execute "SELECT address, hwaddr, client_id, valid_lifetime, expire, subnet_id, fqdn_fwd, fqdn_rev, hostname, state, user_context FROM lease4;"`
84
    assert_eq 0 $? "lease4 table check failed, expected exit code: %d, actual: %d"
85
86

    # Check lease6 table
87
    RESULT=`pgsql_execute "SELECT address, duid, valid_lifetime, expire, subnet_id, pref_lifetime, lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, state, user_context FROM lease6;"`
88
    assert_eq 0 $? "lease6 table check failed, expected exit code: %d, actual: %d"
89
90

    # Check lease6_types table
91
    RESULT=`pgsql_execute "SELECT lease_type, name FROM lease6_types;"`
92
    assert_eq 0 $? "lease6_types table check failed, expected exit code: %d, actual: %d"
93

94
95
96
97
    # Check lease_state table
    RESULT=`pgsql_execute "SELECT state, name FROM lease_state;"`
    assert_eq 0 $? "lease_state table check failed, expected exit code: %d, actual: %d"

98
99
    # Trying to create it again should fail.  This verifies the db present
    # check
100
101
    echo ""
    echo "DB created successfully, make sure we aren't allowed to try it again:"
102
103
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 2 $? "kea-admin failed to deny db-init, expected exit code: %d, actual: %d"
104
105
106
107
108
109
110

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

111
112
pgsql_db_version_test() {
    test_start "pgsql.db-version"
113
114
115
116
117

    # Wipe the whole database
    pgsql_wipe

    # Create the database
118
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
119
    assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d"
120

121
122
    # Verify that kea-admin db-version returns the correct version
    version=$(${keaadmin} db-version pgsql -u $db_user -p $db_password -n $db_name)
123
    assert_str_eq "5.1" ${version} "Expected kea-admin to return %s, returned value was %s"
124
125
126
127
128
129
130

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

131
132
133
pgsql_upgrade_1_0_to_2_0() {
    # Added state column to lease4
    output=`pgsql_execute "select state from lease4;"`
134
    ERRCODE=$?
135
    assert_eq 0 $ERRCODE "lease4 is missing state column. (expected status code %d, returned %d)"
136

137
138
    # Added state column to lease6
    output=`pgsql_execute "select state from lease6;"`
139
    ERRCODE=$?
140
    assert_eq 0 $ERRCODE "lease6 is missing state column. (expected status code %d, returned %d)"
141

142
    # Added stored procedures for lease dumps
143
    output=`pgsql_execute "select lease4DumpHeader from lease4DumpHeader();"`
144
    assert_eq 0 $ERRCODE "function lease4DumpHeader() broken or missing. (expected status code %d, returned %d)"
145
146

    output=`pgsql_execute "select address from lease4DumpData();"`
147
    assert_eq 0 $ERRCODE "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"
148
149

    output=`pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"`
150
    assert_eq 0 $ERRCODE "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"
151
152

    output=`pgsql_execute "select address from lease6DumpData();"`
153
    assert_eq 0 $ERRCODE "function lease6DumpData() broken or missing. (expected status code %d, returned %d)"
154
155
156
157
158
159
}

pgsql_upgrade_2_0_to_3_0() {
    # Added hwaddr, hwtype, and hwaddr_source columns to lease6 table
    output=`pgsql_execute "select hwaddr, hwtype, hwaddr_source from lease6;"`
    ERRCODE=$?
160
    assert_eq 0 $ERRCODE "lease6 table not upgraded to 3.0 (expected status code %d, returned %d)"
161
162
163
164

    # Added lease_hwaddr_source table
    output=`pgsql_execute "select hwaddr_source, name from lease_hwaddr_source;"`
    ERRCODE=$?
165
    assert_eq 0 $ERRCODE "lease_hwaddr_source table is missing or broken. (expected status code %d, returned %d)"
166
167

    # Added hosts table
168
    output=`pgsql_execute "select host_id, dhcp_identifier, dhcp_identifier_type, dhcp4_subnet_id, dhcp6_subnet_id, ipv4_address, hostname, dhcp4_client_classes, dhcp6_client_classes, dhcp4_next_server, dhcp4_server_hostname, dhcp4_boot_file_name, auth_key from hosts;"`
169
    ERRCODE=$?
170
    assert_eq 0 $ERRCODE "hosts table is missing or broken. (expected status code %d, returned %d)"
171
172
173
174

    # Added ipv6_reservations table
    output=`pgsql_execute "select reservation_id, address, prefix_len, type, dhcp6_iaid, host_id from ipv6_reservations;"`
    ERRCODE=$?
175
    assert_eq 0 $ERRCODE "ipv6_reservations table is missing or broken. (expected status code %d, returned %d)"
176
177
178
179

    # Added dhcp4_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp4_subnet_id, host_id, scope_id from dhcp4_options;"`
    ERRCODE=$?
180
    assert_eq 0 $ERRCODE "dhcp4_options table is missing or broken. (expected status code %d, returned %d)"
181
182
183
184

    # Added dhcp6_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"`
    ERRCODE=$?
185
    assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"
186
187
188
189

    # Added  host_identifier_type table
    output=`pgsql_execute "select type, name from host_identifier_type;"`
    ERRCODE=$?
190
    assert_eq 0 $ERRCODE "host_identifier_type table is missing or broken. (expected status code %d, returned %d)"
191
192
193
194

    # Added dhcp_option_scope table
    output=`pgsql_execute "select scope_id, scope_name from dhcp_option_scope;"`
    ERRCODE=$?
195
    assert_eq 0 $ERRCODE "dhcp_option_scope table is missing or broken. (expected status code %d, returned %d)"
196
197
198
199

    # Added dhcp6_options table
    output=`pgsql_execute "select option_id, code, value, formatted_value, space, persistent, dhcp_client_class, dhcp6_subnet_id, host_id,scope_id from dhcp6_options;"`
    ERRCODE=$?
200
    assert_eq 0 $ERRCODE "dhcp6_options table is missing or broken. (expected status code %d, returned %d)"
201
202
203

    # Added order by clause to lease4DumpData
    output=`pgsql_execute "select address from lease4DumpData();"`
204
    assert_eq 0 $ERRCODE "function lease4DumpData() broken or missing. (expected status code %d, returned %d)"
205
    output=`pgsql_execute "\sf lease4DumpData"`
206
    assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (expected status code %d, returned %d)"
207
208
209
210
211
    count=`echo $output | grep -ic "order by l\.address"`
    assert_eq 1 $count "lease4DumpData is missing order by clause"

    # Added hwaddr columns to lease6DumpHeader
    output=`pgsql_execute "select lease6DumpHeader from lease6DumpHeader();"`
212
    assert_eq 0 $ERRCODE "function lease6DumpHeader() broken or missing. (expected status code %d, returned %d)"
213
214
215
216
    count=`echo $output | grep -ic "hwaddr,hwtype,hwaddr_source"`
    assert_eq 1 $count "lease6DumpHeader is missing hwaddr columns"

    # Added hwaddr columns to lease6DumpData
Josh Soref's avatar
Josh Soref committed
217
    output=`pgsql_execute "select hwaddr,hwtype,hwaddr_source from lease6DumpData();"`
218
    assert_eq 0 $ERRCODE "function lease6DumpData() broken or missing. (expected status code %d, returned %d)"
219
220
221

    # Added order by clause to lease6DumpData
    output=`pgsql_execute "\sf lease4DumpData"`
222
    assert_eq 0 $ERRCODE "\sf of lease4DumpData failed. (expected status code %d, returned %d)"
223
224
225
    count=`echo $output | grep -ic "order by l\.address"`
    assert_eq 1 $count "lease4DumpData is missing order by clause"

226
    # lease_hardware_source should have row for source = 0
227
228
    output=`pgsql_execute "select count(hwaddr_source) from lease_hwaddr_source where hwaddr_source = 0 and name='HWADDR_SOURCE_UNKNOWN';"`
    ERRCODE=$?
229
    assert_eq 0 $ERRCODE "select from lease_hwaddr_source failed. (expected status code %d, returned %d)"
Josh Soref's avatar
Josh Soref committed
230
    assert_eq 1 "$output" "lease_hwaddr_source does not contain entry for HWADDR_SOURCE_UNKNOWN. (record count %d, expected %d)"
231
}
232

233
pgsql_upgrade_3_0_to_5_1() {
234
    # Verify upgraded schema reports version 5.1.
235
    version=$(${keaadmin} db-version pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir)
236
    assert_str_eq "5.1" ${version} "Expected kea-admin to return %s, returned value was %s"
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251

    # Added user_context to lease4
    output=`pgsql_execute "select user_context from lease4;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease4 is missing user_context column. (expected status code %d, returned %d)"

    # Added user_context to lease6
    output=`pgsql_execute "select user_context from lease6;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "lease6 is missing user_context column. (expected status code %d, returned %d)"

    # Added logs table
    output=`pgsql_execute "select timestamp, address, log from logs;"`
    ERRCODE=$?
    assert_eq 0 $ERRCODE "logs table is missing or broken. (expected status code %d, returned %d)"
252
253
254
255
256
257
258
259
260
261
262
263
}

pgsql_upgrade_test() {
    test_start "pgsql.upgrade-test"

    # Wipe the whole database
    pgsql_wipe

    # Initialize database to schema 1.0.
    pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql
    assert_eq 0 $? "cannot initialize the database, expected exit code: %d, actual: %d"

264
265
    ${keaadmin} db-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
    assert_eq 0 $? "db-upgrade failed, expected exit code: %d, actual: %d"
266
267
268
269
270
271

    # Check 1.0 to 2.0 upgrade
    pgsql_upgrade_1_0_to_2_0

    # Check 2.0 to 3.0 upgrade
    pgsql_upgrade_2_0_to_3_0
272

273
274
    # Check 3.0 to 5.1 upgrade
    pgsql_upgrade_3_0_to_5_1
275

276
277
    # Let's wipe the whole database
    pgsql_wipe
278
279
280
281

    test_finish 0
}

282
283
284
285
286
287
288
# Given a valid timestamp string, returns a timestamp with timezone string
# for the give time localized by the PostgreSQL server.
get_local_time() {
    timestamp="$1"

    # Expiration field is a "timestamp with timezone" so we need a reference
    # time for the machine/DB this test is running upon.
289
    ref_timestamp=`pgsql_execute "select timestamptz '$1';"`
290
291
292
293
294
295
    ERRCODE=$?
    assert_eq 0 $ERRCODE "reference time query failed for [$timestamp], expected exit code %d, actual %d"
    echo $ref_timestamp
}


296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
# Test verifies the ability to dump lease4 data to CSV file
# The dump output file is compared against a reference file.
# If the dump is successful, the file contents will be the
# same.  Note that the expire field in the lease4 table
# is of data type "timestamp with timezone". This means that
# the dumped file content is dependent upon the timezone
# setting the PostgreSQL server is using.  To account for
# this the reference data contains a tag, "<timestamp>"
# where the expire column's data would normally be. This
# tag is replaced during text execution with a value
# determined by querying the PostgreSQL server.  This
# updated reference data is captured in a temporary file
# which is used for the actual comparison.
pgsql_lease4_dump_test() {
    test_start "pgsql.lease4_dump_test"

    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
313
    output_dir="@abs_top_builddir@/src/bin/admin/tests"
314
    script_dir="@abs_top_srcdir@/src/bin/admin/scripts"
315
316
317

    output_file="$output_dir/data/pgsql.lease4_dump_test.output.csv"

318
    ref_file="$test_dir/data/pgsql.lease4_dump_test.reference.csv"
319
    ref_file_tmp="$output_dir/data/pgsql.lease4_dump_test.reference.csv.tmp"
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334

    # wipe out any residuals from prior failed runs
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $ref_file_tmp ]
    then
        rm $ref_file_tmp
    fi

    # Let's wipe the whole database
    pgsql_wipe

Andrei Pavel's avatar
Andrei Pavel committed
335
    # Ok, now let's initialize the database
336
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
337
338
339
    ERRCODE=$?
    assert_eq 0 $ERRCODE "could not create database, expected exit code %d, actual %d"

340
341
342
343
    timestamp1="2015-01-01 01:15:30"
    timestamp2="2015-02-02 02:30:45"
    timestamp3="2015-03-03 11:01:07"

344
345
    # Insert the reference records
    insert_sql="\
346
347
348
insert into lease4 values(10,E'\\x20',E'\\x30',40,'$timestamp1',50,'t','t','one.example.com', 0, '');\
insert into lease4 values(11,'',E'\\x0123',40,'$timestamp2',50,'t','t','', 1, '');\
insert into lease4 values(12,E'\\x22','',40,'$timestamp3',50,'t','t','three.example.com', 2, '');"
349
350
351
352
353
354

    pgsql_execute "$insert_sql"
    ERRCODE=$?
    assert_eq 0 $ERRCODE "insert into lease4 failed, expected exit code %d, actual %d"

    # Dump lease4 to output_file
355
    ${keaadmin} lease-dump pgsql -4 -u $db_user -p $db_password -n $db_name -d $db_scripts_dir -o $output_file
356
357
358
    ERRCODE=$?
    assert_eq 0 $ERRCODE "kea-admin lease-dump -4 failed,  status code %d"

359
360
361
362
363
    # Expiration field is a "timestamp with timezone" so we need a localized reference
    # times for the machine/DB this test is running upon.
    local_timestamp1=`get_local_time "$timestamp1"`
    local_timestamp2=`get_local_time "$timestamp2"`
    local_timestamp3=`get_local_time "$timestamp3"`
364
365
366

    # Create the comparison file by replacing the <timestamp> tags
    # with the local reference timestamp
367
368
369
370
371
372
    sedstr="\
sed 's/<timestamp1>/$local_timestamp1/g' $ref_file | \
sed 's/<timestamp2>/$local_timestamp2/g' | \
sed 's/<timestamp3>/$local_timestamp3/g' "

    eval $sedstr >$ref_file_tmp
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
    ERRCODE=$?
    assert_eq 0 $ERRCODE "timestamp replacement failed, expected exit code %d, actual %d"

    # Compare the dump output to reference file, they should be identical
    cmp -s $output_file  $ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dump file does not match reference file, expected exit code %d, actual %d"

    # Remove the output file and temporary reference file
    rm $output_file
    rm $ref_file_tmp

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

# Test verifies the ability to dump lease6 data to CSV file
# The dump output file is compared against a reference file.
# If the dump is successful, the file contents will be the
# same.  Note that the expire field in the lease6 table
# is of data type "timestamp with timezone". This means that
# the dumped file content is dependent upon the timezone
# setting the PostgreSQL server is using.  To account for
# this the reference data contains a tag, "<timestamp>"
# where the expire column's data would normally be. This
# tag is replaced during text execution with a value
# determined by querying the PostgreSQL server.  This
# updated reference data is captured in a temporary file
# which is used for the actual comparison.
pgsql_lease6_dump_test() {
    test_start "pgsql.lease6_dump_test"

    test_dir="@abs_top_srcdir@/src/bin/admin/tests"
408
    output_dir="@abs_top_builddir@/src/bin/admin/tests"
409
    script_dir="@abs_top_srcdir@/src/bin/admin/scripts"
410
411
412

    output_file="$output_dir/data/pgsql.lease6_dump_test.output.csv"

413
    ref_file="$test_dir/data/pgsql.lease6_dump_test.reference.csv"
414
    ref_file_tmp="$output_dir/data/pgsql.lease6_dump_test.reference.csv.tmp"
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429

    # wipe out any residuals from prior failed runs
    if [ -e $output_file ]
    then
        rm $output_file
    fi

    if [ -e $ref_file_tmp ]
    then
        rm $ref_file_tmp
    fi

    # Let's wipe the whole database
    pgsql_wipe

Andrei Pavel's avatar
Andrei Pavel committed
430
    # Ok, now let's initialize the database
431
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
432
433
434
    ERRCODE=$?
    assert_eq 0 $ERRCODE "could not create database,  status code %d"

435
436
437
438
    timestamp1="2015-04-04 01:15:30"
    timestamp2="2015-02-02 02:30:45"
    timestamp3="2015-06-06 11:01:07"

439
440
    # Insert the reference records
    insert_sql="\
441
442
443
insert into lease6 values(10,E'\\x20',30,'$timestamp1',40,50,1,60,70,'t','t','one.example.com', 0,decode('80','hex'),90,16,'');\
insert into lease6 values(11,'',30,'$timestamp2',40,50,1,60,70,'t','t','', 1,decode('80','hex'),90,1,'');\
insert into lease6 values(12,E'\\x21',30,'$timestamp3',40,50,1,60,70,'t','t','three.example.com', 2,decode('80','hex'),90,4,'');"
444
445
446
447
448
449

    pgsql_execute "$insert_sql"
    ERRCODE=$?
    assert_eq 0 $ERRCODE "insert into lease6 failed, status code %d"

    # Dump lease6 to output_file
450
    ${keaadmin} lease-dump pgsql -6 -u $db_user -p $db_password -n $db_name -d $db_scripts_dir -o $output_file
451
452
453
    ERRCODE=$?
    assert_eq 0 $ERRCODE "kea-admin lease-dump -6 failed, status code %d"

454
455
456
457
458
    # Expiration field is a "timestamp with timezone" so we need a localized reference
    # times for the machine/DB this test is running upon.
    local_timestamp1=`get_local_time "$timestamp1"`
    local_timestamp2=`get_local_time "$timestamp2"`
    local_timestamp3=`get_local_time "$timestamp3"`
459
460
461

    # Create the comparison file by replacing the <timestamp> tags
    # with the local reference timestamp
462
463
464
465
466
467
    sedstr="\
sed 's/<timestamp1>/$local_timestamp1/g' $ref_file | \
sed 's/<timestamp2>/$local_timestamp2/g' | \
sed 's/<timestamp3>/$local_timestamp3/g' "

    eval $sedstr >$ref_file_tmp
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
    ERRCODE=$?
    assert_eq 0 $ERRCODE "timestamp replacement failed, expected exit code %d, actual %d"

    # Compare the dump output to reference file, they should be identical
    cmp -s $output_file  $ref_file_tmp
    ERRCODE=$?
    assert_eq 0 $ERRCODE "dump file does not match reference file"

    # Remove the output file and temporary reference file
    rm $output_file
    rm $ref_file_tmp

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
# Upgrades an existing schema to a target newer version
# param target_version - desired schema version as "major.minor"
pgsql_upgrade_schema_to_version() {
    target_version=$1

    # Check if the scripts directory exists at all.
    if [ ! -d ${db_scripts_dir}/pgsql ]; then
        log_error "Invalid scripts directory: ${db_scripts_dir}/pgsql"
        exit 1
    fi

    # Check if there are any files in it
    num_files=$(find ${db_scripts_dir}/pgsql/upgrade*.sh -type f | wc -l)
    if [ $num_files -eq 0 ]; then
        log_error "No scripts in ${db_scripts_dir}/pgsql?"
        exit 1
    fi

    # Postgres psql does not accept pw on command line, but can do it
    # thru an env
    export PGPASSWORD=$db_password

    for script in ${db_scripts_dir}/pgsql/upgrade*.sh
    do
        if [ $version = "$target_version" ]
        then
            break;
        fi

        echo "Processing $script file..."
        sh ${script} -U ${db_user} -h ${db_host} -d ${db_name}

        version=`pgsql_version`
    done

    echo "Schema upgraded to $version"
}

# Verifies lease4_stat trigger operations on
# an new, empty database.  It inserts, updates, and
# deletes various leases, checking lease4_stat
# values along the way.
pgsql_lease4_stat_test() {
    test_start "pgsql.lease4_stat_test"

    # Let's wipe the whole database
    pgsql_wipe

    # Ok, now let's initialize the database
535
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
536
    ERRCODE=$?
537
    assert_eq 0 $ERRCODE "kea-admin db-init pgsql returned non-zero status code %d, expected %d"
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626

    # Verify lease4 stat table is present
    qry="select count(subnet_id) from lease4_stat"
    run_statement "#1" "$qry" 0

    # Insert lease4
    qry="insert into lease4 (address, subnet_id, state) values (111,1,0)"
    run_statement "#2" "$qry"

    # Assigned state count should be 1
    qry="select leases from lease4_stat where subnet_id = 1 and state = 0"
    run_statement "#3" "$qry" 1

    # Set lease state to declined
    qry="update lease4 set state = 1 where address = 111"
    run_statement "#4" "$qry"

    # Leases state count for assigned should be 0
    qry="select leases from lease4_stat where subnet_id = 1 and state = 0"
    run_statement "#5" "$qry" 0

    # Leases state count for declined should be 1
    qry="select leases from lease4_stat where subnet_id = 1 and state = 1"
    run_statement "#6" "$qry" 1

    # Delete the lease
    qry="delete from lease4 where address = 111"
    run_statement "#7" "$qry"

    # Leases state count for declined should be 0
    qry="select leases from lease4_stat where subnet_id = 1 and state = 1"
    run_statement "#8" "$qry" 0

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

# Verifies that lease6_stat triggers operate correctly
# for using a given address and lease_type.  It will
# insert a lease, update it, and delete checking the
# lease stat counts along the way.  It assumes the
# database has been created but is empty.
# param addr - address to use to add to subnet 1
# param ltype - type of lease to create
pgsql_lease6_stat_per_type() {
    addr=$1;shift;
    ltype=$1;

    # insert a lease6 for addr and ltype, state assigned
    qry="insert into lease6 (address, lease_type, subnet_id, state) values ('$addr',$ltype,1,0);"
    run_statement "#2" "$qry"

    # assigned stat should be 1
    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and state = 0";
    run_statement "#3" "$qry" 1

    # update the lease, changing state to declined
    qry="update lease6 set state = 1 where address = '$addr'"
    run_statement "#4" "$qry"

    # leases stat for assigned state should be 0
    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 0";
    run_statement "#5" "$qry" 0

    # leases count for declined state should be 1
    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 1";
    run_statement "#6" "$qry" 1

    # delete the lease
    qry="delete from lease6 where address = '$addr';"
    run_statement "#7" "$qry"

    # leases count for declined state should be 0
    qry="select leases from lease6_stat where subnet_id = 1 and lease_type = $ltype and  state = 0";
    run_statement "#6" "$qry" 0
}

# Verifies that lease6_stat triggers operation correctly
# for both NA and PD lease types, pgsql_lease6_stat_per_type()
pgsql_lease6_stat_test() {

    test_start "pgsql.lease6_stat_test"

    # Let's wipe the whole database
    pgsql_wipe

    # Ok, now let's initialize the database
627
    ${keaadmin} db-init pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
628
629
    ERRCODE=$?

630
    assert_eq 0 $ERRCODE "kea-admin db-init pgsql returned non-zero status code %d, expected %d"
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685

    # verify lease6 stat table is present
    qry="select count(subnet_id) from lease6_stat"
    run_statement "#1" "$qry"

    # Test for address 111, NA lease type
    pgsql_lease6_stat_per_type "111" "0"

    # Test for address 222, PD lease type
    pgsql_lease6_stat_per_type "222" "1"

    # Let's wipe the whole database
    pgsql_wipe

    test_finish 0
}

# Verifies that you can upgrade from earlier version and
# lease<4/6>_stat tables will be populated based on existing
# leases and that the stat triggers work properly.
pgsql_lease_stat_upgrade_test() {
    test_start "pgsql.lease_stat_upgrade_test"

    # Let's wipe the whole database
    pgsql_wipe

    # We need to create an older database with lease data so we can
    # verify the upgrade mechanisms which prepopulate the lease stat
    # tables.
    #
    # Initialize database to scheme 1.0.
    pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql
    assert_eq 0 $? "cannot initialize 1.0 database, expected exit code: %d, actual: %d"

    # Now upgrade to schema 2.0, this has lease_state in it
    pgsql_upgrade_schema_to_version 2.0

    # Now we need insert some leases to "migrate" for both v4 and v6
    qry=\
"insert into lease4 (address, subnet_id, state) values (111,10,0);\
 insert into lease4 (address, subnet_id, state) values (222,10,0);\
 insert into lease4 (address, subnet_id, state) values (333,10,1);\
 insert into lease4 (address, subnet_id, state) values (444,10,2);\
 insert into lease4 (address, subnet_id, state) values (555,77,0);"
    run_statement "insert v4 leases" "$qry"

    qry=\
"insert into lease6 (address, lease_type, subnet_id, state) values (111,0,40,0);\
 insert into lease6 (address, lease_type, subnet_id, state) values (222,0,40,1);\
 insert into lease6 (address, lease_type, subnet_id, state) values (333,1,40,0);\
 insert into lease6 (address, lease_type, subnet_id, state) values (444,1,50,0);\
 insert into lease6 (address, lease_type, subnet_id, state) values (555,1,50,0);\
 insert into lease6 (address, lease_type, subnet_id, state) values (666,1,40,2);"
    run_statement "insert v6 leases" "$qry"

686
    # Ok, we have a 2.0 database with leases. Let's upgrade it to 4.0
687
    ${keaadmin} db-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
    ERRCODE=$?

    #
    # First we'll verify lease4_stats are correct after migration.
    #

    # Assigned leases for subnet 10 should be 2
    qry="select leases from lease4_stat where subnet_id = 10 and state = 0"
    run_statement "#4.1" "$qry" 2

    # Assigned leases for subnet 77 should be 1
    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
    run_statement "#4.2" "$qry" 1

    # Should be no records for EXPIRED
    qry="select count(subnet_id) from lease4_stat where state = 2"
    run_statement "#4.3" "$qry" 0

    #
    # Now we'll verify v4 trigger operation for insert,update, and delete
    #

    # Insert a new lease subnet 77
    qry="insert into lease4 (address, subnet_id, state) values (777,77,0);"
    run_statement "#4.4" "$qry"

    # Assigned count for subnet 77 should be 2
    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
    run_statement "#4.5" "$qry" 2

    # Update the state of the new lease to declined
    qry="update lease4 set state = 1 where address = 777;"
    run_statement "#4.6" "$qry"

    # Assigned count for subnet 77 should be 1 again
    qry="select leases from lease4_stat where subnet_id = 77 and state = 0"
    run_statement "#4.7" "$qry" 1

    # Declined count for subnet 77 should be 1
    qry="select leases from lease4_stat where subnet_id = 77 and state = 1"
    run_statement "#4.8" "$qry" 1

    # Delete the lease.
    qry="delete from lease4 where address = 777;"
    run_statement "#4.9" "$qry"

    # Declined count for subnet 77 should be 0
    qry="select leases from lease4_stat where subnet_id = 77 and state = 1"
    run_statement "#4.10" "$qry" 0

    #
    # Next we'll verify lease6_stats are correct after migration.
    #

    # Assigned leases for subnet 40 should be 1
    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 0"
    run_statement "#6.1" "$qry" 1

    # Assigned (PD) leases for subnet 40 should be 1
    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 1 and state = 0"
    run_statement "#6.2" "$qry" 1

    # Declined leases for subnet 40 should be 1
    qry="select leases from lease6_stat where subnet_id = 40 and lease_type = 0 and state = 1"
    run_statement "#6.3" "$qry" 1

    # Assigned (PD) leases for subnet 50 should be 2
    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
    run_statement "#6.4" "$qry" 2

    # Should be no records for EXPIRED
    qry="select count(subnet_id) from lease4_stat where state = 2"
    run_statement "#6.5" "$qry" 0

    #
    # Finally we'll verify v6 trigger operation for insert,update, and delete
    #

    # Insert a new lease subnet 50
    qry="insert into lease6 (address, subnet_id, lease_type, state) values ('777',50,1,0)"
    run_statement "#6.5" "$qry"

    # Assigned count for subnet 50 should be 3
    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
    run_statement "#6.6" "$qry" 3

    # Update the state of the new lease to expired
    qry="update lease6 set state = 2 where address = '777';"
    run_statement "#6.7" "$qry"

    # Assigned count for subnet 50 should be 2 again
    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
    run_statement "#6.8" "$qry" 2

    # Delete another PD lease.
    qry="delete from lease6 where address = '555'"
    run_statement "#6.9" "$qry"

    # Assigned leases for subnet 50 should be 1
    qry="select leases from lease6_stat where subnet_id = 50 and lease_type = 1 and state = 0"
    run_statement "#6.10" "$qry" 1

    # Let's wipe the whole database
    pgsql_wipe
}

794
795
# Verifies that you can upgrade from earlier version and
# that unused subnet ID values in hosts and options tables are
796
# converted to NULL.
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
pgsql_unused_subnet_id_test() {
    test_start "pgsql.unused_subnet_id_test"

    # Let's wipe the whole database
    pgsql_wipe

    # We need to create an older database with lease data so we can
    # verify the upgrade mechanisms which prepopulate the lease stat
    # tables.
    #
    # Initialize database to scheme 1.0.
    pgsql_execute_script @abs_top_srcdir@/src/bin/admin/tests/dhcpdb_create_1.0.pgsql
    assert_eq 0 $? "cannot initialize 1.0 database, expected exit code: %d, actual: %d"

    # Now upgrade to schema 4.0
    pgsql_upgrade_schema_to_version 4.0

    # Now we need insert some hosts to "migrate" for both v4 and v6
    qry=\
"insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
   values (0, '0123456', 0, 0, 'both'); \
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
819
   values (0, '1123456', 4, 0, 'v4only');
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname)\
   values (0, '2123456', 0, 6, 'v6only');\
 insert into hosts (dhcp_identifier_type, dhcp_identifier, dhcp4_subnet_id, dhcp6_subnet_id, hostname) \
   values (0, '3123456', 4, 6, 'neither');"

    run_statement "insert hosts" "$qry"

    # Now we need insert some options to "migrate" for both v4 and v6
    qry=\
"insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (1, 4, 0);\
 insert into dhcp4_options (code, dhcp4_subnet_id, scope_id) values (2, 0, 0);\
 insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (1, 6, 0);\
 insert into dhcp6_options (code, dhcp6_subnet_id, scope_id) values (2, 0, 0);"

    run_statement "insert options" "$qry"

    # Ok, we have a 4.0 database with hosts and options. Let's upgrade it to 5.0
837
    ${keaadmin} db-upgrade pgsql -u $db_user -p $db_password -n $db_name -d $db_scripts_dir
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
    ERRCODE=$?

    # Upgrade should succeed
    assert_eq 0 $ERRCODE "upgrade failed"

    # Two hosts should have null v4 subnet ids
    qry="select count(host_id) from hosts where dhcp4_subnet_id is null;"
    run_statement "#hosts.1" "$qry" 2

    # Two hosts should have v4 subnet ids = 4
    qry="select count(host_id) from hosts where dhcp4_subnet_id = 4;"
    run_statement "#hosts.2" "$qry" 2

    # Two hosts should have null v6 subnet ids
    qry="select count(host_id) from hosts where dhcp6_subnet_id is null;"
    run_statement "#hosts.3" "$qry" 2

    # Two hosts should should have v6 subnet ids = 6
    qry="select count(host_id) from hosts where dhcp6_subnet_id = 6;"
    run_statement "#hosts.4" "$qry" 2

    # One option should have null v4 subnet id
    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id is null;"
    run_statement "#options.1" "$qry" 1

    # One option should have v4 subnet id = 4
    qry="select count(option_id) from dhcp4_options where dhcp4_subnet_id = 4;"
    run_statement "#options.2" "$qry" 1

    # One option should have null v6 subnet id
    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id is null;"
    run_statement "#options.3" "$qry" 1

    # One option should have v4 subnet id = 6
    qry="select count(option_id) from dhcp6_options where dhcp6_subnet_id = 6;"
    run_statement "#options.4" "$qry" 1

    # Let's wipe the whole database
    pgsql_wipe
}


880
881
pgsql_db_init_test
pgsql_db_version_test
882
pgsql_upgrade_test
883
884
pgsql_lease4_dump_test
pgsql_lease6_dump_test
885
886
887
pgsql_lease4_stat_test
pgsql_lease6_stat_test
pgsql_lease_stat_upgrade_test
888
pgsql_unused_subnet_id_test