From a1697ce64b60f1ce1e8bf5ee5fb6e228c69b785c Mon Sep 17 00:00:00 2001 From: Marcin Siodelski Date: Tue, 9 Jan 2018 15:47:10 +0100 Subject: [PATCH] [5468] Added lease indexing by subnet identifiers and lease type. --- src/share/database/scripts/mysql/dhcpdb_create.mysql | 8 +++++++- src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in | 6 ++++++ src/share/database/scripts/pgsql/dhcpdb_create.pgsql | 8 +++++++- src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in | 6 ++++++ 4 files changed, 26 insertions(+), 2 deletions(-) diff --git a/src/share/database/scripts/mysql/dhcpdb_create.mysql b/src/share/database/scripts/mysql/dhcpdb_create.mysql index 1f333b96b9..b1cdd1d058 100644 --- a/src/share/database/scripts/mysql/dhcpdb_create.mysql +++ b/src/share/database/scripts/mysql/dhcpdb_create.mysql @@ -1,4 +1,4 @@ -# Copyright (C) 2012-2017 Internet Systems Consortium, Inc. ("ISC") +# Copyright (C) 2012-2018 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 @@ -510,6 +510,12 @@ ALTER TABLE hosts ADD COLUMN user_context TEXT NULL; ALTER TABLE dhcp4_options ADD COLUMN user_context TEXT NULL; ALTER TABLE dhcp6_options ADD COLUMN user_context TEXT NULL; +# Create index for searching leases by subnet identifier. +CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); + +# Create for searching leases by subnet identifier and lease type. +CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); + # Update the schema version number UPDATE schema_version SET version = '6', minor = '0'; diff --git a/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in b/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in index c367165e42..c4e7809128 100644 --- a/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in +++ b/src/share/database/scripts/mysql/upgrade_5.1_to_6.0.sh.in @@ -24,6 +24,12 @@ ALTER TABLE hosts ADD COLUMN user_context TEXT NULL; ALTER TABLE dhcp4_options ADD COLUMN user_context TEXT NULL; ALTER TABLE dhcp6_options ADD COLUMN user_context TEXT NULL; +# Create index for searching leases by subnet identifier. +CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); + +# Create for searching leases by subnet identifier and lease type. +CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); + # Update the schema version number UPDATE schema_version SET version = '6', minor = '0'; diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql index 0dedc8a6c3..90d1aed7da 100644 --- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql +++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql @@ -1,4 +1,4 @@ --- Copyright (C) 2012-2017 Internet Systems Consortium, Inc. ("ISC") +-- Copyright (C) 2012-2018 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 @@ -537,6 +537,12 @@ ALTER TABLE hosts ADD COLUMN user_context TEXT; ALTER TABLE dhcp4_options ADD COLUMN user_context TEXT; ALTER TABLE dhcp6_options ADD COLUMN user_context TEXT; +-- Create index for searching leases by subnet identifier. +CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); + +-- Create for searching leases by subnet identifier and lease type. +CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); + -- Set 4.0 schema version. UPDATE schema_version SET version = '4', minor = '0'; diff --git a/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in b/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in index c19bccbef4..c20e114f2a 100644 --- a/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in +++ b/src/share/database/scripts/pgsql/upgrade_3.2_to_4.0.sh.in @@ -26,6 +26,12 @@ ALTER TABLE hosts ADD COLUMN user_context TEXT; ALTER TABLE dhcp4_options ADD COLUMN user_context TEXT; ALTER TABLE dhcp6_options ADD COLUMN user_context TEXT; +-- Create index for searching leases by subnet identifier. +CREATE INDEX lease4_by_subnet_id ON lease4 (subnet_id); + +-- Create for searching leases by subnet identifier and lease type. +CREATE INDEX lease6_by_subnet_id_lease_type ON lease6 (subnet_id, lease_type); + -- Set 4.0 schema version. UPDATE schema_version SET version = '4', minor = '0'; -- 2.47.2