From e4df42531d45f32b0cee584ab9a15e769f0ec50f Mon Sep 17 00:00:00 2001 From: Razvan Becheriu Date: Fri, 26 May 2023 11:01:34 +0300 Subject: [PATCH] [#145] addressed review comments --- src/bin/admin/tests/mysql_tests.sh.in | 4 +- src/bin/admin/tests/pgsql_tests.sh.in | 4 +- src/lib/dhcpsrv/lease.cc | 16 +- src/lib/dhcpsrv/mysql_lease_mgr.cc | 146 ++++++++++++------ src/lib/dhcpsrv/pgsql_lease_mgr.cc | 77 ++++----- .../scripts/mysql/dhcpdb_create.mysql | 126 +-------------- .../scripts/mysql/upgrade_017_to_018.sh.in | 126 +-------------- .../scripts/pgsql/dhcpdb_create.pgsql | 10 +- .../scripts/pgsql/upgrade_016_to_017.sh.in | 10 +- 9 files changed, 173 insertions(+), 346 deletions(-) diff --git a/src/bin/admin/tests/mysql_tests.sh.in b/src/bin/admin/tests/mysql_tests.sh.in index 8a83a68298..882b8a7ed8 100644 --- a/src/bin/admin/tests/mysql_tests.sh.in +++ b/src/bin/admin/tests/mysql_tests.sh.in @@ -419,14 +419,14 @@ mysql_upgrade_13_to_14_test() { # -- lease counting tests -- # Check that @json_supported is NULL by default. - query='SELECT @json_supported' + query="SELECT @json_supported" run_command \ mysql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" assert_str_eq "NULL" "${OUTPUT}" "${query}: expected output %s, returned %s" # Clean up. - query='DELETE FROM lease4; DELETE FROM lease6' + query="DELETE FROM lease4; DELETE FROM lease6" run_command \ mysql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" diff --git a/src/bin/admin/tests/pgsql_tests.sh.in b/src/bin/admin/tests/pgsql_tests.sh.in index 9de49a7a23..dd9ca64369 100644 --- a/src/bin/admin/tests/pgsql_tests.sh.in +++ b/src/bin/admin/tests/pgsql_tests.sh.in @@ -490,7 +490,7 @@ pgsql_upgrade_12_to_13_test() { # -- lease counting tests -- # Clean up. - query='DELETE FROM lease4; DELETE FROM lease6' + query="DELETE FROM lease4; DELETE FROM lease6" run_command \ pgsql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" @@ -533,7 +533,7 @@ pgsql_upgrade_12_to_13_test() { assert_str_eq '' "${OUTPUT}" "INSERT INTO leases when upgrading from 11 to 12 failed. expected output %s, returned %s" # Check that @json_supported is NULL by default. - query='SELECT isJsonSupported()' + query="SELECT isJsonSupported()" run_command \ pgsql_execute "${query}" assert_eq 0 "${EXIT_CODE}" "${query}: expected %d, returned %d" diff --git a/src/lib/dhcpsrv/lease.cc b/src/lib/dhcpsrv/lease.cc index d750c14faf..58cc495207 100644 --- a/src/lib/dhcpsrv/lease.cc +++ b/src/lib/dhcpsrv/lease.cc @@ -166,7 +166,7 @@ Lease::fromElementCommon(const LeasePtr& lease, const data::ConstElementPtr& ele ConstElementPtr subnet_id = element->get("subnet-id"); if (!subnet_id || (subnet_id->getType() != Element::integer)) { isc_throw(BadValue, "subnet-id not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if (subnet_id->intValue() <= 0) { @@ -183,7 +183,7 @@ Lease::fromElementCommon(const LeasePtr& lease, const data::ConstElementPtr& ele ConstElementPtr pool_id = element->get("pool-id"); if (pool_id) { if (pool_id->getType() != Element::integer) { - isc_throw(BadValue, "pool-id is not a number"); + isc_throw(BadValue, "pool-id is not an integer"); } if (pool_id->intValue() < 0) { @@ -219,7 +219,7 @@ Lease::fromElementCommon(const LeasePtr& lease, const data::ConstElementPtr& ele ConstElementPtr cltt = element->get("cltt"); if (!cltt || (cltt->getType() != Element::integer)) { isc_throw(BadValue, "cltt is not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if (cltt->intValue() <= 0) { @@ -233,7 +233,7 @@ Lease::fromElementCommon(const LeasePtr& lease, const data::ConstElementPtr& ele ConstElementPtr valid_lifetime = element->get("valid-lft"); if (!valid_lifetime || (valid_lifetime->getType() != Element::integer)) { isc_throw(BadValue, "valid-lft is not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if (valid_lifetime->intValue() < 0) { @@ -275,7 +275,7 @@ Lease::fromElementCommon(const LeasePtr& lease, const data::ConstElementPtr& ele ConstElementPtr state = element->get("state"); if (!state || (state->getType() != Element::integer)) { isc_throw(BadValue, "state is not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if ((state->intValue() < 0) || (state->intValue() > Lease::STATE_EXPIRED_RECLAIMED)) { @@ -664,7 +664,7 @@ Lease6::fromElement(const data::ConstElementPtr& element) { ConstElementPtr prefix_len = element->get("prefix-len"); if (!prefix_len || (prefix_len->getType() != Element::integer)) { isc_throw(BadValue, "prefix-len is not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if ((prefix_len->intValue() < 1) || (prefix_len->intValue() > 128)) { @@ -679,7 +679,7 @@ Lease6::fromElement(const data::ConstElementPtr& element) { ConstElementPtr iaid = element->get("iaid"); if (!iaid || (iaid->getType() != Element::integer)) { isc_throw(BadValue, "iaid is not present in the parsed lease" - " or it is not a number"); + " or it is not an integer"); } if (iaid->intValue() < 0) { @@ -708,7 +708,7 @@ Lease6::fromElement(const data::ConstElementPtr& element) { ConstElementPtr preferred_lft = element->get("preferred-lft"); if (!preferred_lft || (preferred_lft->getType() != Element::integer)) { isc_throw(BadValue, "preferred-lft is not present in the parsed lease" - " or is not a number"); + " or is not an integer"); } if (preferred_lft->intValue() < 0) { diff --git a/src/lib/dhcpsrv/mysql_lease_mgr.cc b/src/lib/dhcpsrv/mysql_lease_mgr.cc index e24c15725f..bd128cd9f9 100644 --- a/src/lib/dhcpsrv/mysql_lease_mgr.cc +++ b/src/lib/dhcpsrv/mysql_lease_mgr.cc @@ -356,7 +356,7 @@ tagged_statements = { { "lease_type, iaid, prefix_len, " "fqdn_fwd, fqdn_rev, hostname, " "hwaddr, hwtype, hwaddr_source, " - "state, user_context " + "state, user_context, pool_id " "FROM lease6 " "WHERE address > ? AND binaddr IS NULL " "ORDER BY address " @@ -407,7 +407,7 @@ tagged_statements = { { "lease_type, iaid, prefix_len, " "fqdn_fwd, fqdn_rev, hostname, " "hwaddr, hwtype, hwaddr_source, " - "state, user_context " + "state, user_context, pool_id " "FROM lease6 " "WHERE binaddr IS NOT NULL " "AND binaddr BETWEEN ? AND ? " @@ -425,7 +425,7 @@ tagged_statements = { { "lease_type, iaid, prefix_len, " "fqdn_fwd, fqdn_rev, hostname, " "hwaddr, hwtype, hwaddr_source, " - "state, user_context, binaddr, pool_id) " + "state, user_context, pool_id, binaddr) " "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"}, {MySqlLeaseMgr::UPDATE_LEASE4, "UPDATE lease4 SET address = ?, hwaddr = ?, " @@ -441,7 +441,7 @@ tagged_statements = { { "pref_lifetime = ?, lease_type = ?, iaid = ?, " "prefix_len = ?, fqdn_fwd = ?, fqdn_rev = ?, " "hostname = ?, hwaddr = ?, hwtype = ?, hwaddr_source = ?, " - "state = ?, user_context = ?, binaddr = ?, pool_id = ? " + "state = ?, user_context = ?, pool_id = ?, binaddr = ? " "WHERE address = ? AND expire = ?"}, {MySqlLeaseMgr::ALL_LEASE4_STATS, "SELECT subnet_id, state, leases as state_count " @@ -574,7 +574,26 @@ public: /// in all MySqlLeaseMgr::xxx4() calls where it is used. class MySqlLease4Exchange : public MySqlLeaseExchange { - /// @brief Set number of database columns for this lease structure + /// These are used for both retrieving data and for looking up + /// column labels for logging. Note that their numeric order + /// MUST match that of the column order in the Lease4 table. + //@{ + static const size_t ADDRESS_COL = 0; + static const size_t HWADDR_COL = 1; + static const size_t CLIENT_ID_COL = 2; + static const size_t VALID_LIFETIME_COL = 3; + static const size_t EXPIRE_COL = 4; + static const size_t SUBNET_ID_COL = 5; + static const size_t FQDN_FWD_COL = 6; + static const size_t FQDN_REV_COL = 7; + static const size_t HOSTNAME_COL = 8; + static const size_t STATE_COL = 9; + static const size_t USER_CONTEXT_COL = 10; + static const size_t RELAY_ID_COL = 11; + static const size_t REMOTE_ID_COL = 12; + static const size_t POOL_ID_COL = 13; + //@} + /// @brief Number of columns in the table holding DHCPv4 leases. static const size_t LEASE_COLUMNS = 14; public: @@ -600,20 +619,20 @@ public: std::fill(&error_[0], &error_[LEASE_COLUMNS], MLM_FALSE); // Set the column names (for error messages) - columns_[0] = "address"; - columns_[1] = "hwaddr"; - columns_[2] = "client_id"; - columns_[3] = "valid_lifetime"; - columns_[4] = "expire"; - columns_[5] = "subnet_id"; - columns_[6] = "fqdn_fwd"; - columns_[7] = "fqdn_rev"; - columns_[8] = "hostname"; - columns_[9] = "state"; - columns_[10] = "user_context"; - columns_[11] = "relay_id"; - columns_[12] = "remote_id"; - columns_[13] = "pool_id"; + columns_[ADDRESS_COL] = "address"; + columns_[HWADDR_COL] = "hwaddr"; + columns_[CLIENT_ID_COL] = "client_id"; + columns_[VALID_LIFETIME_COL] = "valid_lifetime"; + columns_[EXPIRE_COL] = "expire"; + columns_[SUBNET_ID_COL] = "subnet_id"; + columns_[FQDN_FWD_COL] = "fqdn_fwd"; + columns_[FQDN_REV_COL] = "fqdn_rev"; + columns_[HOSTNAME_COL] = "hostname"; + columns_[STATE_COL] = "state"; + columns_[USER_CONTEXT_COL] = "user_context"; + columns_[RELAY_ID_COL] = "relay_id"; + columns_[REMOTE_ID_COL] = "remote_id"; + columns_[POOL_ID_COL] = "pool_id"; BOOST_STATIC_ASSERT(13 < LEASE_COLUMNS); } @@ -1140,7 +1159,32 @@ private: /// in all MySqlLeaseMgr::xxx6() calls where it is used. class MySqlLease6Exchange : public MySqlLeaseExchange { - /// @brief Set number of database columns for this lease structure + /// @brief Column numbers for each column in the Lease6 table. + /// These are used for both retrieving data and for looking up + /// column labels for logging. Note that their numeric order + /// MUST match that of the column order in the Lease6 table. + //@{ + static const size_t ADDRESS_COL = 0; + static const size_t DUID_COL = 1; + static const size_t VALID_LIFETIME_COL = 2; + static const size_t EXPIRE_COL = 3; + static const size_t SUBNET_ID_COL = 4; + static const size_t PREF_LIFETIME_COL = 5; + static const size_t LEASE_TYPE_COL = 6; + static const size_t IAID_COL = 7; + static const size_t PREFIX_LEN_COL = 8; + static const size_t FQDN_FWD_COL = 9; + static const size_t FQDN_REV_COL = 10; + static const size_t HOSTNAME_COL = 11; + static const size_t HWADDR_COL = 12; + static const size_t HWTYPE_COL = 13; + static const size_t HWADDR_SOURCE_COL = 14; + 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; public: @@ -1165,25 +1209,25 @@ public: std::fill(&error_[0], &error_[LEASE_COLUMNS], MLM_FALSE); // Set the column names (for error messages) - columns_[0] = "address"; - columns_[1] = "duid"; - columns_[2] = "valid_lifetime"; - columns_[3] = "expire"; - columns_[4] = "subnet_id"; - columns_[5] = "pref_lifetime"; - columns_[6] = "lease_type"; - columns_[7] = "iaid"; - columns_[8] = "prefix_len"; - columns_[9] = "fqdn_fwd"; - columns_[10] = "fqdn_rev"; - columns_[11] = "hostname"; - columns_[12] = "hwaddr"; - columns_[13] = "hwtype"; - columns_[14] = "hwaddr_source"; - columns_[15] = "state"; - columns_[16] = "user_context"; - columns_[17] = "binaddr"; - columns_[18] = "pool_id"; + columns_[ADDRESS_COL] = "address"; + columns_[DUID_COL] = "duid"; + columns_[VALID_LIFETIME_COL] = "valid_lifetime"; + columns_[EXPIRE_COL] = "expire"; + columns_[SUBNET_ID_COL] = "subnet_id"; + columns_[PREF_LIFETIME_COL] = "pref_lifetime"; + columns_[LEASE_TYPE_COL] = "lease_type"; + columns_[IAID_COL] = "iaid"; + columns_[PREFIX_LEN_COL] = "prefix_len"; + columns_[FQDN_FWD_COL] = "fqdn_fwd"; + columns_[FQDN_REV_COL] = "fqdn_rev"; + columns_[HOSTNAME_COL] = "hostname"; + columns_[HWADDR_COL] = "hwaddr"; + columns_[HWTYPE_COL] = "hwtype"; + columns_[HWADDR_SOURCE_COL] = "hwaddr_source"; + columns_[STATE_COL] = "state"; + columns_[USER_CONTEXT_COL] = "user_context"; + columns_[POOL_ID_COL] = "pool_id"; + columns_[BINADDR_COL] = "binaddr"; BOOST_STATIC_ASSERT(18 < LEASE_COLUMNS); } @@ -1427,6 +1471,14 @@ public: bind_[16].buffer_type = MYSQL_TYPE_NULL; } + // pool_id: unsigned int + // Can use lease_->pool_id_ directly as it is of type uint32_t. + bind_[17].buffer_type = MYSQL_TYPE_LONG; + bind_[17].buffer = reinterpret_cast(&lease_->pool_id_); + bind_[17].is_unsigned = MLM_TRUE; + // bind_[17].is_null = &MLM_FALSE; // commented out for performance + // reasons, see memset() above + // binaddr: binary(16) binaddr_ = lease->addr_.toBytes(); if (binaddr_.size() != 16) { @@ -1434,18 +1486,10 @@ public: } binaddr_length_ = 16; - bind_[17].buffer_type = MYSQL_TYPE_BLOB; - bind_[17].buffer = reinterpret_cast(&binaddr_[0]); - bind_[17].buffer_length = 16; - bind_[17].length = &binaddr_length_; - // bind_[17].is_null = &MLM_FALSE; // commented out for performance - // reasons, see memset() above - - // pool_id: unsigned int - // Can use lease_->pool_id_ directly as it is of type uint32_t. - bind_[18].buffer_type = MYSQL_TYPE_LONG; - bind_[18].buffer = reinterpret_cast(&lease_->pool_id_); - bind_[18].is_unsigned = MLM_TRUE; + bind_[18].buffer_type = MYSQL_TYPE_BLOB; + bind_[18].buffer = reinterpret_cast(&binaddr_[0]); + bind_[18].buffer_length = 16; + bind_[18].length = &binaddr_length_; // bind_[18].is_null = &MLM_FALSE; // commented out for performance // reasons, see memset() above diff --git a/src/lib/dhcpsrv/pgsql_lease_mgr.cc b/src/lib/dhcpsrv/pgsql_lease_mgr.cc index 8630564dd7..1dc27b5f72 100644 --- a/src/lib/dhcpsrv/pgsql_lease_mgr.cc +++ b/src/lib/dhcpsrv/pgsql_lease_mgr.cc @@ -144,7 +144,7 @@ PgSqlTaggedStatement tagged_statements[] = { "FROM lease4 " "WHERE address > $1 AND user_context IS NOT NULL " "ORDER BY address " - "LIMIT $2"}, + "LIMIT $2" }, // GET_LEASE4_SUBID { 1, { OID_INT8 }, @@ -358,7 +358,7 @@ PgSqlTaggedStatement tagged_statements[] = { "FROM lease6 " "WHERE address > $1 AND user_context IS NOT NULL " "ORDER BY address " - "LIMIT $2"}, + "LIMIT $2" }, // GET_LEASE6_BINADDR_PAGE { 2, { OID_VARCHAR, OID_INT8 }, @@ -367,11 +367,11 @@ PgSqlTaggedStatement tagged_statements[] = { "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 " + "state, user_context, pool_id " "FROM lease6 " "WHERE address > $1 AND binaddr IS NULL " "ORDER BY address " - "LIMIT $2"}, + "LIMIT $2" }, // GET_LEASE6_SUBID { 1, { OID_INT8 }, @@ -427,12 +427,12 @@ PgSqlTaggedStatement tagged_statements[] = { "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 " + "state, user_context, pool_id " "FROM lease6 " "WHERE binaddr IS NOT NULL " "AND binaddr BETWEEN $1 and $2 " "ORDER BY binaddr " - "LIMIT $3"}, + "LIMIT $3" }, // INSERT_LEASE4 { 14, { OID_INT8, OID_BYTEA, OID_BYTEA, OID_INT8, OID_TIMESTAMP, OID_INT8, @@ -448,13 +448,13 @@ PgSqlTaggedStatement tagged_statements[] = { { 19, { 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_BYTEA, OID_INT8}, + OID_INT8, OID_BYTEA }, "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, binaddr, pool_id) " + "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)" }, // UPDATE_LEASE4 @@ -472,14 +472,14 @@ PgSqlTaggedStatement tagged_statements[] = { { 21, { 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_BYTEA, OID_INT8, OID_VARCHAR, OID_TIMESTAMP }, + OID_INT8, OID_TEXT, OID_INT8, OID_BYTEA, OID_VARCHAR, OID_TIMESTAMP }, "update_lease6", "UPDATE lease6 SET address = $1, 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, binaddr = $18, pool_id = $19 " + "state = $16, user_context = $17, pool_id = $18, binaddr = $19 " "WHERE address = $20 AND expire = $21" }, // ALL_LEASE4_STATS @@ -568,7 +568,7 @@ PgSqlTaggedStatement tagged_statements[] = { "WHERE client_class = $1 AND lease_type = $2" }, // End of list sentinel - { 0, { 0 }, NULL, NULL} + { 0, { 0 }, NULL, NULL } }; } // namespace @@ -592,7 +592,7 @@ public: hostname_(""), state_str_(""), user_context_(""), addr_bin_(16) { } - virtual ~PgSqlLeaseExchange(){} + virtual ~PgSqlLeaseExchange() {} protected: @@ -628,6 +628,7 @@ private: /// These are used for both retrieving data and for looking up /// column labels for logging. Note that their numeric order /// MUST match that of the column order in the Lease4 table. + //@{ static const size_t ADDRESS_COL = 0; static const size_t HWADDR_COL = 1; static const size_t CLIENT_ID_COL = 2; @@ -642,6 +643,7 @@ private: static const size_t RELAY_ID_COL = 11; static const size_t REMOTE_ID_COL = 12; static const size_t POOL_ID_COL = 13; + //@} /// @brief Number of columns in the table holding DHCPv4 leases. static const size_t LEASE_COLUMNS = 14; @@ -673,6 +675,7 @@ public: columns_.push_back("user_context"); columns_.push_back("relay_id"); columns_.push_back("remote_id"); + columns_.push_back("pool_id"); } /// @brief Creates the bind array for sending Lease4 data to the database. @@ -800,7 +803,7 @@ public: expire_ = convertFromDatabaseTime(getRawColumnValue(r, row, EXPIRE_COL)); - getColumnValue(r, row , SUBNET_ID_COL, subnet_id_); + getColumnValue(r, row, SUBNET_ID_COL, subnet_id_); // Recover from overflow (see createBindForSend) if (valid_lifetime_ == Lease::INFINITY_LFT) { @@ -816,7 +819,7 @@ public: hostname_ = getRawColumnValue(r, row, HOSTNAME_COL); uint32_t state; - getColumnValue(r, row , STATE_COL, state); + getColumnValue(r, row, STATE_COL, state); HWAddrPtr hwaddr(new HWAddr(hwaddr_buffer_, hwaddr_length_, HTYPE_ETHER)); @@ -837,7 +840,7 @@ public: convertFromBytea(r, row, REMOTE_ID_COL, remote_id_buffer_, sizeof(remote_id_buffer_), remote_id_length_); - getColumnValue(r, row , POOL_ID_COL, pool_id_); + getColumnValue(r, row, POOL_ID_COL, pool_id_); Lease4Ptr result(boost::make_shared(addr4_, hwaddr, client_id_buffer_, @@ -915,8 +918,8 @@ private: static const size_t HWADDR_SOURCE_COL = 14; static const size_t STATE_COL = 15; static const size_t USER_CONTEXT_COL = 16; - static const size_t BINADDR_COL = 17; - static const size_t POOL_ID_COL = 18; + 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; @@ -932,11 +935,11 @@ public: union Uiaid { /// @brief Constructor /// @param val unsigned 32 bit value for the IAID. - Uiaid(uint32_t val) : uval_(val){}; + Uiaid(uint32_t val) : uval_(val) {}; /// @brief Constructor /// @param val signed 32 bit value for the IAID. - Uiaid(int32_t val) : ival_(val){}; + Uiaid(int32_t val) : ival_(val) {}; /// @brief Return a string representing the signed 32-bit value. std::string dbInputString() { @@ -976,6 +979,10 @@ public: columns_.push_back("hwaddr_source"); 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"); } @@ -1092,11 +1099,11 @@ public: } bind_array.add(user_context_); - addr_bin_ = lease_->addr_.toBytes(); - bind_array.add(addr_bin_); - pool_id_str_ = boost::lexical_cast(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: " @@ -1140,15 +1147,15 @@ public: cltt_ = expire_ - valid_lifetime_; } - getColumnValue(r, row , SUBNET_ID_COL, subnet_id_); + getColumnValue(r, row, SUBNET_ID_COL, subnet_id_); - getColumnValue(r, row , PREF_LIFETIME_COL, pref_lifetime_); + getColumnValue(r, row, PREF_LIFETIME_COL, pref_lifetime_); getLeaseTypeColumnValue(r, row, LEASE_TYPE_COL, lease_type_); - getColumnValue(r, row , IAID_COL, iaid_u_.ival_); + getColumnValue(r, row, IAID_COL, iaid_u_.ival_); - getColumnValue(r, row , PREFIX_LEN_COL, prefix_len_); + getColumnValue(r, row, PREFIX_LEN_COL, prefix_len_); getColumnValue(r, row, FQDN_FWD_COL, fqdn_fwd_); @@ -1159,9 +1166,9 @@ public: convertFromBytea(r, row, HWADDR_COL, hwaddr_buffer_, sizeof(hwaddr_buffer_), hwaddr_length_); - getColumnValue(r, row , HWTYPE_COL, hwtype_); + getColumnValue(r, row, HWTYPE_COL, hwtype_); - getColumnValue(r, row , HWADDR_SOURCE_COL, hwaddr_source_); + getColumnValue(r, row, HWADDR_SOURCE_COL, hwaddr_source_); HWAddrPtr hwaddr; @@ -1173,7 +1180,7 @@ public: } uint32_t state; - getColumnValue(r, row , STATE_COL, state); + getColumnValue(r, row, STATE_COL, state); user_context_ = getRawColumnValue(r, row, USER_CONTEXT_COL); ConstElementPtr ctx; @@ -1185,7 +1192,7 @@ public: } } - getColumnValue(r, row , POOL_ID_COL, pool_id_); + getColumnValue(r, row, POOL_ID_COL, pool_id_); Lease6Ptr result(boost::make_shared(lease_type_, addr, duid_ptr, @@ -1230,7 +1237,7 @@ public: void getLeaseTypeColumnValue(const PgSqlResult& r, const int row, const size_t col, Lease6::Type& value) const { uint32_t raw_value = 0; - getColumnValue(r, row , col, raw_value); + getColumnValue(r, row, col, raw_value); switch (raw_value) { case Lease6::TYPE_NA: case Lease6::TYPE_TA: @@ -1393,7 +1400,7 @@ public: // Fetch the pool id if we were told to do so. if (fetch_pool_) { - PgSqlExchange::getColumnValue(*result_set_, next_row_ , col, + PgSqlExchange::getColumnValue(*result_set_, next_row_, col, row.pool_id_); ++col; } @@ -1401,7 +1408,7 @@ public: // Fetch the lease type if we were told to do so. if (fetch_type_) { uint32_t lease_type; - PgSqlExchange::getColumnValue(*result_set_, next_row_ , col, + PgSqlExchange::getColumnValue(*result_set_, next_row_, col, lease_type); row.lease_type_ = static_cast(lease_type); ++col; @@ -1410,7 +1417,7 @@ public: } // Fetch the lease state. - PgSqlExchange::getColumnValue(*result_set_, next_row_ , col, + PgSqlExchange::getColumnValue(*result_set_, next_row_, col, row.lease_state_); ++col; @@ -1816,7 +1823,7 @@ PgSqlLeaseMgr::getLeaseCollection(PgSqlLeaseContextPtr& ctx, << tagged_statements[stindex].name); } - for(int i = 0; i < rows; ++ i) { + for(int i = 0; i < rows; ++i) { result.push_back(exchange->convertFromDatabase(r, i)); } } diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql index 666e61ba82..2f07a01bf0 100644 --- a/src/share/database/scripts/mysql/dhcpdb_create.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql @@ -5188,118 +5188,6 @@ ALTER TABLE lease6 UPDATE lease6 SET duid = UNHEX('000000') WHERE duid = UNHEX('00'); --- Drop and create lease4Upload stored procedure with 255 bytes long client_id. -DROP PROCEDURE IF EXISTS lease4Upload; - --- Create a procedure that inserts a v4 lease from memfile data. -DELIMITER $$ -CREATE PROCEDURE lease4Upload( - IN address VARCHAR(15), - IN hwaddr VARCHAR(20), - IN client_id VARCHAR(255), - IN valid_lifetime INT UNSIGNED, - IN expire BIGINT UNSIGNED, - IN subnet_id INT UNSIGNED, - IN fqdn_fwd TINYINT, - IN fqdn_rev TINYINT, - IN hostname VARCHAR(255), - IN state INT UNSIGNED, - IN user_context TEXT -) -BEGIN - INSERT INTO lease4 ( - address, - hwaddr, - client_id, - valid_lifetime, - expire, - subnet_id, - fqdn_fwd, - fqdn_rev, - hostname, - state, - user_context - ) VALUES ( - INET_ATON(address), - UNHEX(REPLACE(hwaddr, ':', '')), - UNHEX(REPLACE(client_id, ':', '')), - valid_lifetime, - FROM_UNIXTIME(expire), - subnet_id, - fqdn_fwd, - fqdn_rev, - REPLACE(hostname, ',', ','), - state, - REPLACE(user_context, ',', ',') - ); -END $$ -DELIMITER ; - --- Drop and create lease6Upload stored procedure with 130 bytes long duid. -DROP PROCEDURE IF EXISTS lease6Upload; - --- Create a procedure that inserts a v6 lease from memfile data. -DELIMITER $$ -CREATE PROCEDURE lease6Upload( - IN address VARCHAR(39), - IN duid VARCHAR(130), - IN valid_lifetime INT UNSIGNED, - IN expire BIGINT UNSIGNED, - IN subnet_id INT UNSIGNED, - IN pref_lifetime INT UNSIGNED, - IN lease_type TINYINT, - IN iaid INT UNSIGNED, - IN prefix_len TINYINT UNSIGNED, - IN fqdn_fwd TINYINT, - IN fqdn_rev TINYINT, - IN hostname VARCHAR(255), - IN hwaddr VARCHAR(64), - IN state INT UNSIGNED, - IN user_context TEXT, - IN hwtype SMALLINT, - IN hwaddr_source INT UNSIGNED -) -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 - ) VALUES ( - address, - UNHEX(REPLACE(duid, ':', '')), - valid_lifetime, - FROM_UNIXTIME(expire), - subnet_id, - pref_lifetime, - lease_type, - iaid, - prefix_len, - fqdn_fwd, - fqdn_rev, - REPLACE(hostname, ',', ','), - UNHEX(REPLACE(hwaddr, ':', '')), - state, - REPLACE(user_context, ',', ','), - hwtype, - hwaddr_source - ); -END $$ -DELIMITER ; - -- Add the binary version of the IPv6 address for v6 BLQ prefix filter. ALTER TABLE lease6 ADD COLUMN binaddr BINARY(16) DEFAULT NULL; @@ -5420,7 +5308,7 @@ BEGIN UPDATE lease4_pool_stat SET leases = IF(leases > 0, leases - 1, 0) WHERE subnet_id = old_subnet_id AND pool_id = old_pool_id - AND old_state = state; + AND state = old_state; END IF; END $$ DELIMITER ; @@ -5601,7 +5489,7 @@ SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_r END $$ DELIMITER ; --- Modify the procedure to output a memfile-ready CSV file. +-- Adding support for pool ID in procedure to output a memfile-ready CSV file. DROP PROCEDURE IF EXISTS lease4DumpData; DELIMITER $$ CREATE PROCEDURE lease4DumpData() @@ -5632,7 +5520,7 @@ BEGIN END $$ DELIMITER ; --- Modify the procedure to output a memfile-ready CSV file. +-- Adding support for pool ID in procedure to output a memfile-ready CSV file. DROP PROCEDURE IF EXISTS lease6DumpData; DELIMITER $$ CREATE PROCEDURE lease6DumpData() @@ -5661,13 +5549,13 @@ BEGIN END $$ DELIMITER ; --- Create a procedure that inserts a v4 lease from memfile data. +-- Drop and create lease4Upload stored procedure with 255 bytes long client_id and support for pool_id. DROP PROCEDURE IF EXISTS lease4Upload; DELIMITER $$ CREATE PROCEDURE lease4Upload( IN address VARCHAR(15), IN hwaddr VARCHAR(20), - IN client_id VARCHAR(128), + IN client_id VARCHAR(255), IN valid_lifetime INT UNSIGNED, IN expire BIGINT UNSIGNED, IN subnet_id INT UNSIGNED, @@ -5709,12 +5597,12 @@ BEGIN END $$ DELIMITER ; --- Create a procedure that inserts a v6 lease from memfile data. +-- Drop and create lease6Upload stored procedure with 130 bytes long duid and support for pool_id. DROP PROCEDURE IF EXISTS lease6Upload; DELIMITER $$ CREATE PROCEDURE lease6Upload( IN address VARCHAR(39), - IN duid VARCHAR(128), + IN duid VARCHAR(130), IN valid_lifetime INT UNSIGNED, IN expire BIGINT UNSIGNED, IN subnet_id INT UNSIGNED, diff --git a/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in index e5f8a31cad..8af3720ad3 100644 --- a/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in +++ b/src/share/database/scripts/mysql/upgrade_017_to_018.sh.in @@ -76,118 +76,6 @@ ALTER TABLE lease6 UPDATE lease6 SET duid = UNHEX('000000') WHERE duid = UNHEX('00'); --- Drop and create lease4Upload stored procedure with 255 bytes long client_id. -DROP PROCEDURE IF EXISTS lease4Upload; - --- Create a procedure that inserts a v4 lease from memfile data. -DELIMITER $$ -CREATE PROCEDURE lease4Upload( - IN address VARCHAR(15), - IN hwaddr VARCHAR(20), - IN client_id VARCHAR(255), - IN valid_lifetime INT UNSIGNED, - IN expire BIGINT UNSIGNED, - IN subnet_id INT UNSIGNED, - IN fqdn_fwd TINYINT, - IN fqdn_rev TINYINT, - IN hostname VARCHAR(255), - IN state INT UNSIGNED, - IN user_context TEXT -) -BEGIN - INSERT INTO lease4 ( - address, - hwaddr, - client_id, - valid_lifetime, - expire, - subnet_id, - fqdn_fwd, - fqdn_rev, - hostname, - state, - user_context - ) VALUES ( - INET_ATON(address), - UNHEX(REPLACE(hwaddr, ':', '')), - UNHEX(REPLACE(client_id, ':', '')), - valid_lifetime, - FROM_UNIXTIME(expire), - subnet_id, - fqdn_fwd, - fqdn_rev, - REPLACE(hostname, ',', ','), - state, - REPLACE(user_context, ',', ',') - ); -END $$ -DELIMITER ; - --- Drop and create lease6Upload stored procedure with 130 bytes long duid. -DROP PROCEDURE IF EXISTS lease6Upload; - --- Create a procedure that inserts a v6 lease from memfile data. -DELIMITER $$ -CREATE PROCEDURE lease6Upload( - IN address VARCHAR(39), - IN duid VARCHAR(130), - IN valid_lifetime INT UNSIGNED, - IN expire BIGINT UNSIGNED, - IN subnet_id INT UNSIGNED, - IN pref_lifetime INT UNSIGNED, - IN lease_type TINYINT, - IN iaid INT UNSIGNED, - IN prefix_len TINYINT UNSIGNED, - IN fqdn_fwd TINYINT, - IN fqdn_rev TINYINT, - IN hostname VARCHAR(255), - IN hwaddr VARCHAR(64), - IN state INT UNSIGNED, - IN user_context TEXT, - IN hwtype SMALLINT, - IN hwaddr_source INT UNSIGNED -) -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 - ) VALUES ( - address, - UNHEX(REPLACE(duid, ':', '')), - valid_lifetime, - FROM_UNIXTIME(expire), - subnet_id, - pref_lifetime, - lease_type, - iaid, - prefix_len, - fqdn_fwd, - fqdn_rev, - REPLACE(hostname, ',', ','), - UNHEX(REPLACE(hwaddr, ':', '')), - state, - REPLACE(user_context, ',', ','), - hwtype, - hwaddr_source - ); -END $$ -DELIMITER ; - -- Add the binary version of the IPv6 address for v6 BLQ prefix filter. ALTER TABLE lease6 ADD COLUMN binaddr BINARY(16) DEFAULT NULL; @@ -308,7 +196,7 @@ BEGIN UPDATE lease4_pool_stat SET leases = IF(leases > 0, leases - 1, 0) WHERE subnet_id = old_subnet_id AND pool_id = old_pool_id - AND old_state = state; + AND state = old_state; END IF; END $$ DELIMITER ; @@ -489,7 +377,7 @@ SELECT 'address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_r END $$ DELIMITER ; --- Modify the procedure to output a memfile-ready CSV file. +-- Adding support for pool ID in procedure to output a memfile-ready CSV file. DROP PROCEDURE IF EXISTS lease4DumpData; DELIMITER $$ CREATE PROCEDURE lease4DumpData() @@ -520,7 +408,7 @@ BEGIN END $$ DELIMITER ; --- Modify the procedure to output a memfile-ready CSV file. +-- Adding support for pool ID in procedure to output a memfile-ready CSV file. DROP PROCEDURE IF EXISTS lease6DumpData; DELIMITER $$ CREATE PROCEDURE lease6DumpData() @@ -549,13 +437,13 @@ BEGIN END $$ DELIMITER ; --- Create a procedure that inserts a v4 lease from memfile data. +-- Drop and create lease4Upload stored procedure with 255 bytes long client_id and support for pool_id. DROP PROCEDURE IF EXISTS lease4Upload; DELIMITER $$ CREATE PROCEDURE lease4Upload( IN address VARCHAR(15), IN hwaddr VARCHAR(20), - IN client_id VARCHAR(128), + IN client_id VARCHAR(255), IN valid_lifetime INT UNSIGNED, IN expire BIGINT UNSIGNED, IN subnet_id INT UNSIGNED, @@ -597,12 +485,12 @@ BEGIN END $$ DELIMITER ; --- Create a procedure that inserts a v6 lease from memfile data. +-- Drop and create lease6Upload stored procedure with 130 bytes long duid and support for pool_id. DROP PROCEDURE IF EXISTS lease6Upload; DELIMITER $$ CREATE PROCEDURE lease6Upload( IN address VARCHAR(39), - IN duid VARCHAR(128), + IN duid VARCHAR(130), IN valid_lifetime INT UNSIGNED, IN expire BIGINT UNSIGNED, IN subnet_id INT UNSIGNED, diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index a65b772865..f68651d4ae 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -5753,7 +5753,7 @@ BEGIN UPDATE lease4_pool_stat SET leases = GREATEST(leases - 1, 0) WHERE subnet_id = old_subnet_id AND pool_id = old_pool_id - AND old_state = state; + AND state = old_state; END IF; END; $$ LANGUAGE plpgsql; @@ -5914,7 +5914,7 @@ RETURNS text AS $$ select cast('address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state,user_context,pool_id' as text) as result; $$ LANGUAGE SQL; --- Modify the function to output a memfile-ready CSV file. +-- Adding support for pool ID in function to output a memfile-ready CSV file. -- Some columns that are SMALLINT in the lease4 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -5957,7 +5957,7 @@ RETURNS TEXT AS $$ SELECT CAST('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' AS TEXT) AS result; $$ LANGUAGE SQL; --- Modify the function to output a memfile-ready CSV file. +-- Adding support for pool ID in function to output a memfile-ready CSV file. -- Some columns that are SMALLINT in the lease6 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -6006,7 +6006,7 @@ RETURNS TABLE ( ORDER BY address; $$ LANGUAGE SQL; --- Create a procedure that inserts a v4 lease from memfile data. +-- Adding support for pool id in function that inserts a v4 lease from memfile data. -- Some columns that are SMALLINT in the lease4 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -6055,7 +6055,7 @@ BEGIN END $$ LANGUAGE plpgsql; --- Create a procedure that inserts a v6 lease from memfile data. +-- Adding support for pool id in function that inserts a v6 lease from memfile data. -- Some columns that are SMALLINT in the lease6 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. diff --git a/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in index 3009959398..a78cdf1795 100644 --- a/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in +++ b/src/share/database/scripts/pgsql/upgrade_016_to_017.sh.in @@ -156,7 +156,7 @@ BEGIN UPDATE lease4_pool_stat SET leases = GREATEST(leases - 1, 0) WHERE subnet_id = old_subnet_id AND pool_id = old_pool_id - AND old_state = state; + AND state = old_state; END IF; END; \$\$ LANGUAGE plpgsql; @@ -317,7 +317,7 @@ RETURNS text AS \$\$ select cast('address,hwaddr,client_id,valid_lifetime,expire,subnet_id,fqdn_fwd,fqdn_rev,hostname,state,user_context,pool_id' as text) as result; \$\$ LANGUAGE SQL; --- Modify the function to output a memfile-ready CSV file. +-- Adding support for pool ID in function to output a memfile-ready CSV file. -- Some columns that are SMALLINT in the lease4 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -360,7 +360,7 @@ RETURNS TEXT AS \$\$ SELECT CAST('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' AS TEXT) AS result; \$\$ LANGUAGE SQL; --- Modify the function to output a memfile-ready CSV file. +-- Adding support for pool ID in function to output a memfile-ready CSV file. -- Some columns that are SMALLINT in the lease6 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -409,7 +409,7 @@ RETURNS TABLE ( ORDER BY address; \$\$ LANGUAGE SQL; --- Create a procedure that inserts a v4 lease from memfile data. +-- Adding support for pool id in function that inserts a v4 lease from memfile data. -- Some columns that are SMALLINT in the lease4 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. @@ -458,7 +458,7 @@ BEGIN END \$\$ LANGUAGE plpgsql; --- Create a procedure that inserts a v6 lease from memfile data. +-- Adding support for pool id in function that inserts a v6 lease from memfile data. -- Some columns that are SMALLINT in the lease6 table have their type promoted -- to INT in the declaration of this function for backwards compatibility with -- PostgreSQL versions. -- 2.47.2