summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--configure.ac2
-rw-r--r--src/bin/admin/tests/pgsql_tests.sh.in57
-rw-r--r--src/lib/dhcpsrv/lease_mgr.h15
-rw-r--r--src/lib/dhcpsrv/pgsql_host_data_source.cc30
-rw-r--r--src/lib/dhcpsrv/pgsql_lease_mgr.cc179
-rw-r--r--src/lib/dhcpsrv/pgsql_lease_mgr.h12
-rw-r--r--src/lib/pgsql/pgsql_connection.h2
-rw-r--r--src/share/database/scripts/pgsql/.gitignore1
-rw-r--r--src/share/database/scripts/pgsql/Makefile.am1
-rw-r--r--src/share/database/scripts/pgsql/dhcpdb_create.pgsql128
-rw-r--r--src/share/database/scripts/pgsql/upgrade_017_to_018.sh.in169
11 files changed, 391 insertions, 205 deletions
diff --git a/configure.ac b/configure.ac
index 577ca3e6b5..55bba32618 100644
--- a/configure.ac
+++ b/configure.ac
@@ -1749,6 +1749,8 @@ AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_015_to_016.sh],
[chmod +x src/share/database/scripts/pgsql/upgrade_015_to_016.sh])
AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_016_to_017.sh],
[chmod +x src/share/database/scripts/pgsql/upgrade_016_to_017.sh])
+AC_CONFIG_FILES([src/share/database/scripts/pgsql/upgrade_017_to_018.sh],
+ [chmod +x src/share/database/scripts/pgsql/upgrade_017_to_018.sh])
AC_CONFIG_FILES([src/share/database/scripts/pgsql/wipe_data.sh],
[chmod +x src/share/database/scripts/pgsql/wipe_data.sh])
AC_CONFIG_FILES([src/share/yang/Makefile])
diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in
index dde10aaa1a..a6b1e6d3de 100644
--- a/src/bin/admin/tests/pgsql_tests.sh.in
+++ b/src/bin/admin/tests/pgsql_tests.sh.in
@@ -143,7 +143,7 @@ pgsql_db_version_test() {
run_command \
"${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}"
version="${OUTPUT}"
- assert_str_eq "17.0" "${version}" "Expected kea-admin to return %s, returned value was %s"
+ assert_str_eq "18.0" "${version}" "Expected kea-admin to return %s, returned value was %s"
# Let's wipe the whole database
pgsql_wipe
@@ -387,7 +387,7 @@ pgsql_upgrade_7_0_to_8_0_test() {
# Check lease6Dump*().
run_command \
- pgsql_execute "INSERT INTO lease6 VALUES('::10',E'\\\\x3230',30,TO_TIMESTAMP(1678900000),40,50,1,60,70,'t','t','one,example,com',0,E'\\\\x3830',16,0,'{ \"a\": 1, \"b\": 2 }',0,NULL)"
+ pgsql_execute "INSERT INTO lease6 VALUES('::10',E'\\\\x3230',30,TO_TIMESTAMP(1678900000),40,50,1,60,70,'t','t','one,example,com',0,E'\\\\x3830',16,0,'{ \"a\": 1, \"b\": 2 }',0)"
assert_eq 0 "${EXIT_CODE}" 'INSERT INTO lease6 failed, expected exit code %d, actual %d'
assert_str_eq '' "${OUTPUT}"
@@ -512,21 +512,21 @@ pgsql_upgrade_12_to_13_test() {
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
INSERT INTO lease4 (address, subnet_id, state, user_context) VALUES (105,1,1,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (100,0,1,0,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::10' as inet),0,1,0,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (101,0,1,1,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::11' as inet),0,1,1,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (102,0,1,0,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::12' as inet),0,1,0,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (103,0,1,1,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::13' as inet),0,1,1,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (104,2,1,0,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::14' as inet),2,1,0,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (105,2,1,1,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::15' as inet),2,1,1,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (106,2,1,0,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::16' as inet),2,1,0,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
- INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (107,2,1,1,
+ INSERT INTO lease6 (address, lease_type, subnet_id, state, user_context) VALUES (cast('::17' as inet),2,1,1,
'{\"ISC\": {\"client-classes\": [\"ALL\", \"KNOWN\", \"bar\", \"foo\"] } }');
"
assert_eq 0 "${EXIT_CODE}" 'INSERT INTO leases when upgrading from 11 to 12 failed. expected %d, returned %d'
@@ -839,11 +839,6 @@ pgsql_upgrade_16_to_17_test() {
pgsql_execute "SELECT subnet_id, pool_id, lease_type, state, leases FROM lease6_pool_stat"
assert_eq 0 "${EXIT_CODE}" "lease6_pool_stat table is missing or broken. (expected status code %d, returned %d)"
- # Added binaddr column to lease6
- run_command \
- pgsql_execute "select binaddr from lease6"
- assert_eq 0 "${EXIT_CODE}" "lease6 is missing binaddr column. (expected status code %d, returned %d)"
-
# Added lease6_relay_id table
run_command \
pgsql_execute "select extended_info_id, relay_id, lease_addr from lease6_relay_id"
@@ -855,6 +850,24 @@ pgsql_upgrade_16_to_17_test() {
assert_eq 0 "${EXIT_CODE}" "lease6_remote_id table is missing or broken. (expected status code %d, returned %d)"
}
+pgsql_upgrade_17_to_18_test() {
+ # Verify that lease6 address is binary.
+ qry="insert into lease6 (address,duid,prefix_len,lease_type,subnet_id) values(cast('3001::99' as inet),'18219',128,1,0);"
+ run_statement "lease6_insert" "$qry"
+
+ qry="select host(address) from lease6 where duid = '18219';"
+ run_statement "lease6_insert" "$qry" "3001::99"
+
+ # Verify that ipv6_reservations address is binary.
+ qry="\
+ insert into hosts(host_id, dhcp_identifier, dhcp_identifier_type) values (18219, '18219', 1); \
+ insert into ipv6_reservations (address, prefix_len, type, dhcp6_iaid, host_id) \
+ values (cast('3001::99' as inet), 128, 1, 123, 18219); \
+ select host(address) from ipv6_reservations where host_id = 18219;"
+
+ run_statement "ipv6_reservations_insert" "$qry" "3001::99"
+}
+
pgsql_upgrade_test() {
test_start "pgsql.upgrade"
@@ -873,7 +886,7 @@ pgsql_upgrade_test() {
# Verify upgraded schema reports the latest version.
version=$("${kea_admin}" db-version pgsql -u "${db_user}" -p "${db_password}" -n "${db_name}" -d "${db_scripts_dir}")
- assert_str_eq "17.0" "${version}" 'Expected kea-admin to return %s, returned value was %s'
+ assert_str_eq "18.0" "${version}" 'Expected kea-admin to return %s, returned value was %s'
# Check 1.0 to 2.0 upgrade
pgsql_upgrade_1_0_to_2_0_test
@@ -920,6 +933,9 @@ pgsql_upgrade_test() {
# Check 16 to 17 upgrade
pgsql_upgrade_16_to_17_test
+ # Check 17 to 18 upgrade
+ pgsql_upgrade_17_to_18_test
+
# Let's wipe the whole database
pgsql_wipe
@@ -1046,9 +1062,9 @@ pgsql_lease6_dump_test() {
# Because shell evaluates the double quoted string one more time, they need to be doubled.
# Otherwise, the value is interpreted as ASCII instead of raw bytes.
insert_sql="\
-insert into lease6 values('::10',E'\\\\x323033',30,TO_TIMESTAMP(1642000000),40,50,1,60,70,'t','t','one.example.com',0,decode(encode('80','hex'),'hex'),90,16,'',0,NULL); \
-insert into lease6 values('::11',E'\\\\x323133',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{ }',0,NULL); \
-insert into lease6 values('::12',E'\\\\x323233',30,TO_TIMESTAMP(1643212345),40,50,1,60,70,'t','t','three,example,com',2,decode(encode('80','hex'),'hex'),90,4,'{ \"a\": 1, \"b\": \"c\" }',0,NULL)"
+insert into lease6 values(cast('::10' as inet),E'\\\\x323033',30,TO_TIMESTAMP(1642000000),40,50,1,60,70,'t','t','one.example.com',0,decode(encode('80','hex'),'hex'),90,16,'',0); \
+insert into lease6 values(cast('::11' as inet),E'\\\\x323133',30,TO_TIMESTAMP(1643210000),40,50,1,60,70,'t','t','',1,decode(encode('80','hex'),'hex'),90,1,'{ }',0); \
+insert into lease6 values(cast('::12' as inet),E'\\\\x323233',30,TO_TIMESTAMP(1643212345),40,50,1,60,70,'t','t','three,example,com',2,decode(encode('80','hex'),'hex'),90,4,'{ \"a\": 1, \"b\": \"c\" }',0)"
run_command \
pgsql_execute "$insert_sql"
@@ -1981,6 +1997,9 @@ pgsql_update_empty_duid_test() {
}
# Run tests.
+pgsql_lease4_upload_test
+exit 0
+
pgsql_db_init_test
pgsql_db_version_test
pgsql_upgrade_test
diff --git a/src/lib/dhcpsrv/lease_mgr.h b/src/lib/dhcpsrv/lease_mgr.h
index c4d286996e..4b84ae3cbc 100644
--- a/src/lib/dhcpsrv/lease_mgr.h
+++ b/src/lib/dhcpsrv/lease_mgr.h
@@ -1032,21 +1032,6 @@ public:
return (extended_info_tables_enabled_);
}
- /// @brief Upgrade binary address (v6).
- ///
- /// On SQL backends for all leases with null binary address set this
- /// new column. Memfile uses IOAddress objects so does not need it.
- /// This function implements the new BLQ hook command named
- /// "binary-address6-upgrade".
- ///
- /// @param page_size The page size used for retrieval.
- /// @return The number of updates in the database.
- /// @todo This will be obsolete once #2909 is completed for all backends
- /// and lease-query hook lib
- virtual size_t upgradeBinaryAddress6(const LeasePageSize& /* page_size */) {
- return (0);
- }
-
/// @brief Build extended info v6 tables.
///
/// @param update Update extended info in database.
diff --git a/src/lib/dhcpsrv/pgsql_host_data_source.cc b/src/lib/dhcpsrv/pgsql_host_data_source.cc
index 71e245017c..2222672a65 100644
--- a/src/lib/dhcpsrv/pgsql_host_data_source.cc
+++ b/src/lib/dhcpsrv/pgsql_host_data_source.cc
@@ -1660,7 +1660,7 @@ TaggedStatementArray tagged_statements = { {
" o4.persistent, o4.cancelled, o4.user_context, "
" o6.option_id, o6.code, o6.value, o6.formatted_value, o6.space, "
" o6.persistent, o6.cancelled, o6.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp4_options AS o4 ON h.host_id = o4.host_id "
"LEFT JOIN dhcp6_options AS o6 ON h.host_id = o6.host_id "
@@ -1725,7 +1725,7 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp6_options AS o ON h.host_id = o.host_id "
"LEFT JOIN ipv6_reservations AS r ON h.host_id = r.host_id "
@@ -1773,14 +1773,14 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, "
" r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp6_options AS o ON h.host_id = o.host_id "
"LEFT JOIN ipv6_reservations AS r ON h.host_id = r.host_id "
"WHERE h.host_id = "
" (SELECT host_id FROM ipv6_reservations "
- " WHERE address = $1 AND prefix_len = $2) "
+ " WHERE address = cast($1 as inet) AND prefix_len = $2) "
"ORDER BY h.host_id, o.option_id, r.reservation_id"
},
@@ -1802,14 +1802,14 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, "
" r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp6_options AS o ON h.host_id = o.host_id "
"LEFT JOIN ipv6_reservations AS r ON h.host_id = r.host_id "
"WHERE h.dhcp6_subnet_id = $1 AND h.host_id IN "
" (SELECT host_id FROM ipv6_reservations "
- " WHERE address = $2) "
+ " WHERE address = cast($2 as inet)) "
"ORDER BY h.host_id, o.option_id, r.reservation_id"
},
@@ -1856,7 +1856,7 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp6_options AS o ON h.host_id = o.host_id "
"LEFT JOIN ipv6_reservations AS r ON h.host_id = r.host_id "
@@ -1883,7 +1883,7 @@ TaggedStatementArray tagged_statements = { {
" o4.persistent, o4.cancelled, o4.user_context, "
" o6.option_id, o6.code, o6.value, o6.formatted_value, o6.space, "
" o6.persistent, o6.cancelled, o6.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp4_options AS o4 ON h.host_id = o4.host_id "
"LEFT JOIN dhcp6_options AS o6 ON h.host_id = o6.host_id "
@@ -1931,7 +1931,7 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM hosts AS h "
"LEFT JOIN dhcp6_options AS o ON h.host_id = o.host_id "
"LEFT JOIN ipv6_reservations AS r ON h.host_id = r.host_id "
@@ -1980,7 +1980,7 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM ( SELECT * FROM hosts AS h "
" WHERE h.dhcp6_subnet_id = $1 AND h.host_id > $2 "
" ORDER BY h.host_id "
@@ -2031,7 +2031,7 @@ TaggedStatementArray tagged_statements = { {
" h.dhcp4_boot_file_name, h.auth_key, "
" o.option_id, o.code, o.value, o.formatted_value, o.space, "
" o.persistent, o.cancelled, o.user_context, "
- " r.reservation_id, r.address, r.prefix_len, r.type, r.dhcp6_iaid "
+ " r.reservation_id, host(r.address), r.prefix_len, r.type, r.dhcp6_iaid "
"FROM ( SELECT * FROM hosts AS h "
" WHERE h.host_id > $1 "
" ORDER BY h.host_id "
@@ -2096,7 +2096,7 @@ TaggedStatementArray tagged_statements = { {
"insert_v6_resrv_non_unique",
"INSERT INTO ipv6_reservations(address, prefix_len, type, "
" dhcp6_iaid, host_id) "
- "VALUES ($1, $2, $3, $4, $5)"
+ "VALUES (cast($1 as inet), $2, $3, $4, $5)"
},
// PgSqlHostDataSourceImpl::INSERT_V6_RESRV_UNIQUE
@@ -2107,10 +2107,10 @@ TaggedStatementArray tagged_statements = { {
"insert_v6_resrv_unique",
"INSERT INTO ipv6_reservations(address, prefix_len, type, "
" dhcp6_iaid, host_id) "
- "SELECT $1, $2, $3, $4, $5 "
+ "SELECT cast($1 as inet), $2, $3, $4, $5 "
" WHERE NOT EXISTS ("
" SELECT 1 FROM ipv6_reservations"
- " WHERE address = $6 AND prefix_len = $7"
+ " WHERE address = cast($6 as inet) AND prefix_len = $7"
" LIMIT 1"
" )"
},
@@ -2153,7 +2153,7 @@ TaggedStatementArray tagged_statements = { {
{ OID_INT8, OID_VARCHAR },
"del_host_addr6",
"DELETE FROM hosts USING ipv6_reservations "
- " WHERE dhcp6_subnet_id = $1 AND ipv6_reservations.address = $2"
+ " WHERE dhcp6_subnet_id = $1 AND ipv6_reservations.address = cast($2 as inet)"
},
// PgSqlHostDataSourceImpl::DEL_HOST_SUBID4_ID
diff --git a/src/lib/dhcpsrv/pgsql_lease_mgr.cc b/src/lib/dhcpsrv/pgsql_lease_mgr.cc
index 1dc27b5f72..20963e3b40 100644
--- a/src/lib/dhcpsrv/pgsql_lease_mgr.cc
+++ b/src/lib/dhcpsrv/pgsql_lease_mgr.cc
@@ -55,7 +55,7 @@ PgSqlTaggedStatement tagged_statements[] = {
// DELETE_LEASE6
{ 2, { OID_VARCHAR, OID_TIMESTAMP },
"delete_lease6",
- "DELETE FROM lease6 WHERE address = $1 AND expire = $2" },
+ "DELETE FROM lease6 WHERE address = cast($1 as inet) AND expire = $2"},
// DELETE_LEASE6_STATE_EXPIRED
{ 2, { OID_INT8, OID_TIMESTAMP },
@@ -303,13 +303,13 @@ PgSqlTaggedStatement tagged_statements[] = {
// GET_LEASE6_ADDR
{ 2, { OID_VARCHAR, OID_INT2 },
"get_lease6_addr",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
"state, user_context, pool_id "
"FROM lease6 "
- "WHERE address = $1 AND lease_type = $2" },
+ "WHERE address = cast($1 as inet) AND lease_type = $2"},
// GET_LEASE6_DUID_IAID
{ 3, { OID_BYTEA, OID_INT8, OID_INT2 },
@@ -337,46 +337,33 @@ PgSqlTaggedStatement tagged_statements[] = {
// GET_LEASE6_PAGE
{ 2, { OID_VARCHAR, OID_INT8 },
"get_lease6_page",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
"state, user_context, pool_id "
"FROM lease6 "
- "WHERE address > $1 "
+ "WHERE address > cast($1 as inet) "
"ORDER BY address "
- "LIMIT $2" },
+ "LIMIT $2"},
// GET_LEASE6_UCTX_PAGE
{ 2, { OID_VARCHAR, OID_INT8 },
"get_lease6_uctx_page",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
"state, user_context, pool_id "
"FROM lease6 "
- "WHERE address > $1 AND user_context IS NOT NULL "
- "ORDER BY address "
- "LIMIT $2" },
-
- // GET_LEASE6_BINADDR_PAGE
- { 2, { OID_VARCHAR, OID_INT8 },
- "get_lease6_binaddr_page",
- "SELECT address, duid, valid_lifetime, "
- "extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
- "lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
- "hwaddr, hwtype, hwaddr_source, "
- "state, user_context, pool_id "
- "FROM lease6 "
- "WHERE address > $1 AND binaddr IS NULL "
+ "WHERE address > cast($1 as inet) AND user_context IS NOT NULL "
"ORDER BY address "
"LIMIT $2" },
// GET_LEASE6_SUBID
{ 1, { OID_INT8 },
"get_lease6_subid",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
@@ -387,7 +374,7 @@ PgSqlTaggedStatement tagged_statements[] = {
// GET_LEASE6_DUID
{ 1, { OID_BYTEA },
"get_lease6_duid",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
@@ -398,7 +385,7 @@ PgSqlTaggedStatement tagged_statements[] = {
// GET_LEASE6_HOSTNAME
{ 1, { OID_VARCHAR },
"get_lease6_hostname",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
@@ -409,7 +396,7 @@ PgSqlTaggedStatement tagged_statements[] = {
// GET_LEASE6_EXPIRE
{ 3, { OID_INT8, OID_TIMESTAMP, OID_INT8 },
"get_lease6_expire",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, "
"fqdn_fwd, fqdn_rev, hostname, "
@@ -421,17 +408,16 @@ PgSqlTaggedStatement tagged_statements[] = {
"LIMIT $3" },
// GET_LEASE6_LINK
- { 3, { OID_BYTEA, OID_BYTEA, OID_INT8 },
+ { 3, { OID_VARCHAR, OID_VARCHAR, OID_INT8 },
"get_lease6_link",
- "SELECT address, duid, valid_lifetime, "
+ "SELECT host(address), duid, valid_lifetime, "
"extract(epoch from expire)::bigint, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
"state, user_context, pool_id "
"FROM lease6 "
- "WHERE binaddr IS NOT NULL "
- "AND binaddr BETWEEN $1 and $2 "
- "ORDER BY binaddr "
+ "WHERE address BETWEEN cast($1 as inet) and cast($2 as inet) "
+ "ORDER BY address "
"LIMIT $3" },
// INSERT_LEASE4
@@ -445,17 +431,17 @@ PgSqlTaggedStatement tagged_statements[] = {
"VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)" },
// INSERT_LEASE6
- { 19, { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
+ { 18, { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
OID_INT8, OID_INT2, OID_INT8, OID_INT2, OID_BOOL, OID_BOOL,
OID_VARCHAR, OID_BYTEA, OID_INT2, OID_INT2, OID_INT8, OID_TEXT,
- OID_INT8, OID_BYTEA },
+ OID_INT8},
"insert_lease6",
"INSERT INTO lease6(address, duid, valid_lifetime, "
"expire, subnet_id, pref_lifetime, "
"lease_type, iaid, prefix_len, fqdn_fwd, fqdn_rev, hostname, "
"hwaddr, hwtype, hwaddr_source, "
- "state, user_context, pool_id, binaddr) "
- "VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19)" },
+ "state, user_context, pool_id) "
+ "VALUES (cast($1 as inet), $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)" },
// UPDATE_LEASE4
{ 16, { OID_INT8, OID_BYTEA, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8,
@@ -469,18 +455,18 @@ PgSqlTaggedStatement tagged_statements[] = {
"WHERE address = $15 AND expire = $16" },
// UPDATE_LEASE6
- { 21, { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8, OID_INT8,
+ { 20, { OID_VARCHAR, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8, OID_INT8,
OID_INT2, OID_INT8, OID_INT2, OID_BOOL, OID_BOOL, OID_VARCHAR,
OID_BYTEA, OID_INT2, OID_INT2,
- OID_INT8, OID_TEXT, OID_INT8, OID_BYTEA, OID_VARCHAR, OID_TIMESTAMP },
+ OID_INT8, OID_TEXT, OID_INT8, OID_VARCHAR, OID_TIMESTAMP },
"update_lease6",
- "UPDATE lease6 SET address = $1, duid = $2, "
+ "UPDATE lease6 SET address = cast($1 as inet), duid = $2, "
"valid_lifetime = $3, expire = $4, subnet_id = $5, "
"pref_lifetime = $6, lease_type = $7, iaid = $8, "
"prefix_len = $9, fqdn_fwd = $10, fqdn_rev = $11, hostname = $12, "
"hwaddr = $13, hwtype = $14, hwaddr_source = $15, "
- "state = $16, user_context = $17, pool_id = $18, binaddr = $19 "
- "WHERE address = $20 AND expire = $21" },
+ "state = $16, user_context = $17, pool_id = $18 "
+ "WHERE address = cast($19 as inet) AND expire = $20" },
// ALL_LEASE4_STATS
{ 0, { OID_NONE },
@@ -919,10 +905,9 @@ private:
static const size_t STATE_COL = 15;
static const size_t USER_CONTEXT_COL = 16;
static const size_t POOL_ID_COL = 17;
- static const size_t BINADDR_COL = 18;
//@}
/// @brief Number of columns in the table holding DHCPv6 leases.
- static const size_t LEASE_COLUMNS = 19;
+ static const size_t LEASE_COLUMNS = 18;
public:
@@ -957,7 +942,7 @@ public:
preferred_lifetime_str_(""), hwtype_(0), hwtype_str_(""),
hwaddr_source_(0), hwaddr_source_str_("") {
- BOOST_STATIC_ASSERT(18 < LEASE_COLUMNS);
+ BOOST_STATIC_ASSERT(17 < LEASE_COLUMNS);
memset(duid_buffer_, 0, sizeof(duid_buffer_));
@@ -980,10 +965,6 @@ public:
columns_.push_back("state");
columns_.push_back("user_context");
columns_.push_back("pool_id");
- // all columns that are used in insert/update queries but are not also
- // used in select queries must be added last - the next column is the
- // first of this kind
- columns_.push_back("binaddr");
}
/// @brief Creates the bind array for sending Lease6 data to the database.
@@ -1101,9 +1082,6 @@ public:
pool_id_str_ = boost::lexical_cast<std::string>(lease->pool_id_);
bind_array.add(pool_id_str_);
-
- addr_bin_ = lease_->addr_.toBytes();
- bind_array.add(addr_bin_);
} catch (const std::exception& ex) {
isc_throw(DbOperationError,
"Could not create bind array from Lease6: "
@@ -2313,15 +2291,8 @@ PgSqlLeaseMgr::getLeases6(const IOAddress& lower_bound_address,
// Prepare WHERE clause
PsqlBindArray bind_array;
- // In IPv6 we compare addresses represented as strings. The IPv6 zero address
- // is ::, so it is greater than any other address. In this special case, we
- // just use 0 for comparison which should be lower than any real IPv6 address.
- std::string lb_address_data = "0";
- if (!lower_bound_address.isV6Zero()) {
- lb_address_data = lower_bound_address.toText();
- }
-
// Bind lower bound address
+ std::string lb_address_data = lower_bound_address.toText();
bind_array.add(lb_address_data);
// Bind page size value
@@ -3264,10 +3235,12 @@ PgSqlLeaseMgr::getLeases6ByLink(const IOAddress& link_addr,
"retrieving leases from the lease database, got "
<< link_addr);
}
+
if ((link_len == 0) || (link_len > 128)) {
isc_throw(OutOfRange, "invalid IPv6 prefix length "
<< static_cast<unsigned>(link_len));
}
+
if (!lower_bound_address.isV6()) {
isc_throw(InvalidAddressFamily, "expected IPv6 address while "
"retrieving leases from the lease database, got "
@@ -3292,22 +3265,16 @@ PgSqlLeaseMgr::getLeases6ByLink(const IOAddress& link_addr,
PsqlBindArray bind_array;
// Bind start address
- std::vector<uint8_t> start_addr_data = start_addr.toBytes();
- if (start_addr_data.size() != 16) {
- isc_throw(DbOperationError, "start address is not 16 bytes long");
- }
- bind_array.add(start_addr_data);
+ std::string start_addr_str = start_addr.toText();
+ bind_array.add(start_addr_str);
// Bind last address
- std::vector<uint8_t> last_addr_data = last_addr.toBytes();
- if (last_addr_data.size() != 16) {
- isc_throw(DbOperationError, "last address is not 16 bytes long");
- }
- bind_array.add(last_addr_data);
+ std::string last_addr_str = last_addr.toText();
+ bind_array.add(last_addr_str);
// Bind page size value
- std::string page_size_data =
- boost::lexical_cast<std::string>(page_size.page_size_);
+ std::string page_size_data =
+ boost::lexical_cast<std::string>(page_size.page_size_);
bind_array.add(page_size_data);
// Get a context
@@ -3321,80 +3288,6 @@ PgSqlLeaseMgr::getLeases6ByLink(const IOAddress& link_addr,
}
size_t
-PgSqlLeaseMgr::upgradeBinaryAddress6(const LeasePageSize& page_size) {
- auto check = CfgMgr::instance().getCurrentCfg()->
- getConsistency()->getExtendedInfoSanityCheck();
-
- size_t pages = 0;
- size_t updated = 0;
- IOAddress start_addr = IOAddress::IPV6_ZERO_ADDRESS();
- for (;;) {
- LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL,
- DHCPSRV_PGSQL_UPGRADE_BINARY_ADDRESS6_PAGE)
- .arg(pages)
- .arg(start_addr.toText())
- .arg(updated);
-
- // Prepare WHERE clause.
- PsqlBindArray bind_array;
-
- // Bind start address.
- std::string start_addr_data = "0";
- if (!start_addr.isV6Zero()) {
- start_addr_data = start_addr.toText();
- }
- bind_array.add(start_addr_data);
-
- // Bind page size value.
- std::string page_size_data =
- boost::lexical_cast<std::string>(page_size.page_size_);
- bind_array.add(page_size_data);
-
- Lease6Collection leases;
-
- // Get a context.
- {
- PgSqlLeaseContextAlloc get_context(*this);
- PgSqlLeaseContextPtr ctx = get_context.ctx_;
-
- getLeaseCollection(ctx, GET_LEASE6_BINADDR_PAGE, bind_array, leases);
- }
-
- if (leases.empty()) {
- // Done.
- break;
- }
-
- ++pages;
- start_addr = leases.back()->addr_;
- for (auto lease : leases) {
- try {
- // Update to the same lease will fill the new column i.e.
- // refresh does the job...
- updateLease6(lease);
- ++updated;
- } catch (const NoSuchLease&) {
- // The lease was modified in parallel:
- // as its extended info was processed just ignore.
- continue;
- } catch (const std::exception& ex) {
- // Something when wrong, for instance extract failed.
- LOG_ERROR(dhcpsrv_logger,
- DHCPSRV_PGSQL_UPGRADE_BINARY_ADDRESS6_ERROR)
- .arg(lease->addr_.toText())
- .arg(ex.what());
- }
- }
- }
-
- LOG_INFO(dhcpsrv_logger, DHCPSRV_PGSQL_UPGRADE_BINARY_ADDRESS6)
- .arg(pages)
- .arg(updated);
-
- return (updated);
-}
-
-size_t
PgSqlLeaseMgr::buildExtendedInfoTables6(bool /* update */, bool /* current */) {
isc_throw(isc::NotImplemented,
"PgSqlLeaseMgr::buildExtendedInfoTables6 not implemented");
diff --git a/src/lib/dhcpsrv/pgsql_lease_mgr.h b/src/lib/dhcpsrv/pgsql_lease_mgr.h
index 12435dd0a7..8b60e4231f 100644
--- a/src/lib/dhcpsrv/pgsql_lease_mgr.h
+++ b/src/lib/dhcpsrv/pgsql_lease_mgr.h
@@ -722,7 +722,6 @@ public:
GET_LEASE6_DUID_IAID_SUBID, // Get lease6 by DUID, IAID and subnet ID
GET_LEASE6_PAGE, // Get page of leases beginning with an address
GET_LEASE6_UCTX_PAGE, // Get page of leases with user context
- GET_LEASE6_BINADDR_PAGE, // Get page of leases with null binary address
GET_LEASE6_SUBID, // Get IPv6 leases by subnet ID
GET_LEASE6_DUID, // Get IPv6 leases by DUID
GET_LEASE6_HOSTNAME, // Get IPv6 leases by hostname
@@ -1118,17 +1117,6 @@ private:
/// @return The number of updates in the database.
virtual size_t upgradeExtendedInfo4(const LeasePageSize& page_size) override;
- /// @brief Upgrade binary address (v6).
- ///
- /// On SQL backends for all leases with null binary address set this
- /// new column. Memfile uses IOAddress objects so does not need it.
- /// This function implements the new BLQ hook command named
- /// "binary-address6-upgrade".
- ///
- /// @param page_size The page size used for retrieval.
- /// @return The number of updates in the database.
- virtual size_t upgradeBinaryAddress6(const LeasePageSize& page_size) override;
-
/// @brief Build extended info v6 tables.
///
/// @param update Update extended info in database.
diff --git a/src/lib/pgsql/pgsql_connection.h b/src/lib/pgsql/pgsql_connection.h
index 7d1e5c4505..1446222945 100644
--- a/src/lib/pgsql/pgsql_connection.h
+++ b/src/lib/pgsql/pgsql_connection.h
@@ -18,7 +18,7 @@ namespace isc {
namespace db {
/// @brief Define the PostgreSQL backend version.
-const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 17;
+const uint32_t PGSQL_SCHEMA_VERSION_MAJOR = 18;
const uint32_t PGSQL_SCHEMA_VERSION_MINOR = 0;
// Maximum number of parameters that can be used a statement
diff --git a/src/share/database/scripts/pgsql/.gitignore b/src/share/database/scripts/pgsql/.gitignore
index b5dbcaf549..36bcce1548 100644
--- a/src/share/database/scripts/pgsql/.gitignore
+++ b/src/share/database/scripts/pgsql/.gitignore
@@ -20,4 +20,5 @@
/upgrade_014_to_015.sh
/upgrade_015_to_016.sh
/upgrade_016_to_017.sh
+/upgrade_017_to_018.sh
/wipe_data.sh
diff --git a/src/share/database/scripts/pgsql/Makefile.am b/src/share/database/scripts/pgsql/Makefile.am
index b616cf62b2..b38c38071b 100644
--- a/src/share/database/scripts/pgsql/Makefile.am
+++ b/src/share/database/scripts/pgsql/Makefile.am
@@ -31,6 +31,7 @@ pgsql_SCRIPTS += upgrade_013_to_014.sh
pgsql_SCRIPTS += upgrade_014_to_015.sh
pgsql_SCRIPTS += upgrade_015_to_016.sh
pgsql_SCRIPTS += upgrade_016_to_017.sh
+pgsql_SCRIPTS += upgrade_017_to_018.sh
pgsql_SCRIPTS += wipe_data.sh
DISTCLEANFILES = ${pgsql_SCRIPTS}
diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
index d474ae4d90..93708a145b 100644
--- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
+++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
@@ -6136,6 +6136,134 @@ UPDATE schema_version
-- This line concludes the schema upgrade to version 17.0.
+-- This line starts the schema upgrade to version 18.0.
+
+-- Drop binaddr index and column from lease6.
+DROP INDEX lease6_by_binaddr;
+ALTER TABLE lease6
+ DROP COLUMN binaddr;
+
+-- Change lease6:address to INET.
+ALTER TABLE lease6 ALTER COLUMN address TYPE INET USING address::INET;
+
+-- Change ipv6_reservations:address to INET.
+ALTER TABLE ipv6_reservations ALTER COLUMN address TYPE INET USING address::INET;
+
+-- Invoke HOST() on address now that address type is inet
+CREATE OR REPLACE FUNCTION lease6DumpData()
+RETURNS TABLE (
+ address VARCHAR,
+ duid VARCHAR,
+ valid_lifetime BIGINT,
+ expire BIGINT,
+ subnet_id BIGINT,
+ pref_lifetime BIGINT,
+ lease_type SMALLINT,
+ iaid INT,
+ prefix_len SMALLINT,
+ fqdn_fwd INT,
+ fqdn_rev INT,
+ hostname VARCHAR,
+ hwaddr VARCHAR,
+ state INT8,
+ user_context VARCHAR,
+ hwtype SMALLINT,
+ hwaddr_source SMALLINT,
+ pool_id BIGINT
+) AS $$
+ SELECT
+ HOST(address),
+ colonSeparatedHex(encode(duid, 'hex')),
+ valid_lifetime,
+ extract(epoch from expire)::bigint,
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd::int,
+ fqdn_rev::int,
+ replace(hostname, ',', '&#x2c'),
+ colonSeparatedHex(encode(hwaddr, 'hex')),
+ state,
+ replace(user_context, ',', '&#x2c'),
+ hwtype,
+ hwaddr_source,
+ pool_id
+ FROM lease6
+ ORDER BY address;
+$$ LANGUAGE SQL;
+
+-- Invoke HOST() on address now that address type is inet
+CREATE OR REPLACE FUNCTION lease6Upload(
+ IN address VARCHAR,
+ IN duid VARCHAR,
+ IN valid_lifetime BIGINT,
+ IN expire BIGINT,
+ IN subnet_id BIGINT,
+ IN pref_lifetime BIGINT,
+ IN lease_type INT,
+ IN iaid INT,
+ IN prefix_len INT,
+ IN fqdn_fwd INT,
+ IN fqdn_rev INT,
+ IN hostname VARCHAR,
+ IN hwaddr VARCHAR,
+ IN state INT8,
+ IN user_context VARCHAR,
+ IN hwtype INT,
+ IN hwaddr_source INT,
+ IN pool_id BIGINT
+) RETURNS VOID AS $$
+BEGIN
+ INSERT INTO lease6 (
+ address,
+ duid,
+ valid_lifetime,
+ expire,
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd,
+ fqdn_rev,
+ hostname,
+ hwaddr,
+ state,
+ user_context,
+ hwtype,
+ hwaddr_source,
+ pool_id
+ ) VALUES (
+ cast(address as inet),
+ decode(replace(duid, ':', ''), 'hex'),
+ valid_lifetime,
+ to_timestamp(expire),
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd::int::boolean,
+ fqdn_rev::int::boolean,
+ replace(hostname, '&#x2c', ','),
+ decode(replace(hwaddr, ':', ''), 'hex'),
+ state,
+ replace(user_context, '&#x2c', ','),
+ hwtype,
+ hwaddr_source,
+ pool_id
+ );
+END
+$$ LANGUAGE plpgsql;
+
+-- Update the schema version number.
+UPDATE schema_version
+ SET version = '18', minor = '0';
+
+-- This line concludes the schema upgrade to version 18.0.
+
-- Commit the script transaction.
COMMIT;
diff --git a/src/share/database/scripts/pgsql/upgrade_017_to_018.sh.in b/src/share/database/scripts/pgsql/upgrade_017_to_018.sh.in
new file mode 100644
index 0000000000..6b7b9769d0
--- /dev/null
+++ b/src/share/database/scripts/pgsql/upgrade_017_to_018.sh.in
@@ -0,0 +1,169 @@
+#!/bin/sh
+
+# Copyright (C) 2023 Internet Systems Consortium, Inc. ("ISC")
+#
+# 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/.
+
+# shellcheck disable=SC1091
+# SC1091: Not following: ... was not specified as input (see shellcheck -x).
+
+# Exit with error if commands exit with non-zero and if undefined variables are
+# used.
+set -eu
+
+# shellcheck disable=SC2034
+# SC2034: ... appears unused. Verify use (or export if used externally).
+prefix="@prefix@"
+
+# Include utilities. Use installed version if available and
+# use build version if it isn't.
+if test -f "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh"; then
+ . "@datarootdir@/@PACKAGE_NAME@/scripts/admin-utils.sh"
+else
+ . "@abs_top_builddir@/src/bin/admin/admin-utils.sh"
+fi
+
+VERSION=$(pgsql_version "$@")
+
+if [ "$VERSION" != "17.0" ]; then
+ printf 'This script upgrades 17.0 to 18.0. '
+ printf 'Reported version is %s. Skipping upgrade.\n' "${VERSION}"
+ exit 0
+fi
+
+psql "$@" >/dev/null <<EOF
+
+-- This line starts the schema upgrade to version 18.0.
+
+-- Drop binaddr index and column from lease6.
+DROP INDEX lease6_by_binaddr;
+ALTER TABLE lease6
+ DROP COLUMN binaddr;
+
+-- Change lease6:address to INET.
+ALTER TABLE lease6 ALTER COLUMN address TYPE INET USING address::INET;
+
+-- Change ipv6_reservations:address to INET.
+ALTER TABLE ipv6_reservations ALTER COLUMN address TYPE INET USING address::INET;
+
+-- Invoke HOST() on address now that address type is inet
+CREATE OR REPLACE FUNCTION lease6DumpData()
+RETURNS TABLE (
+ address VARCHAR,
+ duid VARCHAR,
+ valid_lifetime BIGINT,
+ expire BIGINT,
+ subnet_id BIGINT,
+ pref_lifetime BIGINT,
+ lease_type SMALLINT,
+ iaid INT,
+ prefix_len SMALLINT,
+ fqdn_fwd INT,
+ fqdn_rev INT,
+ hostname VARCHAR,
+ hwaddr VARCHAR,
+ state INT8,
+ user_context VARCHAR,
+ hwtype SMALLINT,
+ hwaddr_source SMALLINT,
+ pool_id BIGINT
+) AS \$\$
+ SELECT
+ HOST(address),
+ colonSeparatedHex(encode(duid, 'hex')),
+ valid_lifetime,
+ extract(epoch from expire)::bigint,
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd::int,
+ fqdn_rev::int,
+ replace(hostname, ',', '&#x2c'),
+ colonSeparatedHex(encode(hwaddr, 'hex')),
+ state,
+ replace(user_context, ',', '&#x2c'),
+ hwtype,
+ hwaddr_source,
+ pool_id
+ FROM lease6
+ ORDER BY address;
+\$\$ LANGUAGE SQL;
+
+-- Invoke HOST() on address now that address type is inet
+CREATE OR REPLACE FUNCTION lease6Upload(
+ IN address VARCHAR,
+ IN duid VARCHAR,
+ IN valid_lifetime BIGINT,
+ IN expire BIGINT,
+ IN subnet_id BIGINT,
+ IN pref_lifetime BIGINT,
+ IN lease_type INT,
+ IN iaid INT,
+ IN prefix_len INT,
+ IN fqdn_fwd INT,
+ IN fqdn_rev INT,
+ IN hostname VARCHAR,
+ IN hwaddr VARCHAR,
+ IN state INT8,
+ IN user_context VARCHAR,
+ IN hwtype INT,
+ IN hwaddr_source INT,
+ IN pool_id BIGINT
+) RETURNS VOID AS \$\$
+BEGIN
+ INSERT INTO lease6 (
+ address,
+ duid,
+ valid_lifetime,
+ expire,
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd,
+ fqdn_rev,
+ hostname,
+ hwaddr,
+ state,
+ user_context,
+ hwtype,
+ hwaddr_source,
+ pool_id
+ ) VALUES (
+ cast(address as inet),
+ decode(replace(duid, ':', ''), 'hex'),
+ valid_lifetime,
+ to_timestamp(expire),
+ subnet_id,
+ pref_lifetime,
+ lease_type,
+ iaid,
+ prefix_len,
+ fqdn_fwd::int::boolean,
+ fqdn_rev::int::boolean,
+ replace(hostname, '&#x2c', ','),
+ decode(replace(hwaddr, ':', ''), 'hex'),
+ state,
+ replace(user_context, '&#x2c', ','),
+ hwtype,
+ hwaddr_source,
+ pool_id
+ );
+END
+\$\$ LANGUAGE plpgsql;
+
+-- Update the schema version number.
+UPDATE schema_version
+ SET version = '18', minor = '0';
+
+-- This line concludes the schema upgrade to version 18.0.
+
+-- Commit the script transaction.
+COMMIT;
+
+EOF
generated by cgit v1.2.3 (git 2.39.1) at 2024-12-02 00:39:28 +0100