[#2244] option scopes and createOptionAuditDHCP6

src/share/database/scripts/pgsql/dhcpdb_create.pgsql
    Added missing option scope values
    Replaced procedure createOptionAuditDHCP6()

src/share/database/scripts/pgsql/dhcpdb_drop.pgsql
    added drop

1 files changed, 124 insertions, 2 deletions

diff --git a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
index ad417486d7..d3bd9e111b 100644
--- a/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
+++ b/src/share/database/scripts/pgsql/dhcpdb_create.pgsql
@@ -3706,7 +3706,6 @@ CREATE TRIGGER dhcp6_server_AUPD
     AFTER UPDATE ON dhcp6_server
         FOR EACH ROW EXECUTE PROCEDURE func_dhcp6_server_AUPD();
 
-
 -- Trigger function for dhcp6_server_ADEL called AFTER DELETE on dhcp6_server
 CREATE OR REPLACE FUNCTION func_dhcp6_server_ADEL() RETURNS TRIGGER AS $dhcp6_server_ADEL$
 BEGIN
@@ -3824,13 +3823,136 @@ ALTER TABLE dhcp6_option_def
     ON UPDATE CASCADE;
 
 -- -----------------------------------------------------------------------
--- Add missing preferred_lifetime columns to dhcp6_client_class table.
+-- Add preferred_lifetime columns to dhcp6_client_class table.
 -- -----------------------------------------------------------------------
 ALTER TABLE dhcp6_client_class
     ADD COLUMN preferred_lifetime BIGINT DEFAULT NULL,
     ADD COLUMN min_preferred_lifetime BIGINT DEFAULT NULL,
     ADD COLUMN max_preferred_lifetime BIGINT DEFAULT NULL;
 
+-- -----------------------------------------------------------------------
+-- Add option scopes
+-- -----------------------------------------------------------------------
+-- Add scope for shared network specific options.
+INSERT INTO dhcp_option_scope (scope_id, scope_name)
+    VALUES(4, 'shared-network');
+
+-- Add scope for pool specific options.
+INSERT INTO dhcp_option_scope (scope_id, scope_name)
+    VALUES(5, 'pool');
+
+-- Add scope for PD pool specific options.
+INSERT INTO dhcp_option_scope (scope_id, scope_name)
+    VALUES(6, 'pd-pool');
+
+DROP FUNCTION IF EXISTS createOptionAuditDHCP6(modification_type VARCHAR(32),
+                                               scope_id SMALLINT, option_id INT, subnet_id BIGINT,
+                                               host_id INT, network_name VARCHAR(128),
+                                               pool_id BIGINT, pd_pool_id BIGINT,
+                                               modification_ts TIMESTAMP WITH TIME ZONE);
+
+-- -----------------------------------------------------
+--
+-- New version of the createOptionAuditDHCP4 stored
+-- procedure which updates modification timestamp of
+-- a parent object when an option is modified.
+--
+-- The following parameters are passed to the procedure:
+-- - modification_type: "create", "update" or "delete"
+-- - scope_id: identifier of the option scope, e.g.
+--   global, subnet specific etc. See dhcp_option_scope
+--   for specific values.
+-- - option_id: identifier of the option.
+-- - subnet_id: identifier of the subnet if the option
+--   belongs to the subnet.
+-- - host_id: identifier of the host if the option
+-- - belongs to the host.
+-- - network_name: shared network name if the option
+--   belongs to the shared network.
+-- - pool_id: identifier of the pool if the option
+--   belongs to the pool.
+-- - pd_pool_id: identifier of the pool if the option
+--   belongs to the pd pool.
+-- - modification_ts: modification timestamp of the
+--   option.
+--   Some arguments are prefixed with "p_" to avoid ambiguity
+--   with column names in SQL statements. PostgreSQL does not
+--   allow table aliases to be used with column names in update
+--   set expressions.
+-- -----------------------------------------------------
+CREATE OR REPLACE FUNCTION createOptionAuditDHCP6(modification_type VARCHAR(32),
+                                                  scope_id SMALLINT,
+                                                  option_id INT,
+                                                  p_subnet_id BIGINT,
+                                                  host_id INT,
+                                                  network_name VARCHAR(128),
+                                                  pool_id BIGINT,
+                                                  pd_pool_id BIGINT,
+                                                  p_modification_ts TIMESTAMP WITH TIME ZONE)
+RETURNS VOID
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    -- These variables will hold shared network id and subnet id that
+    -- we will select.
+    snid VARCHAR(128);
+    sid BIGINT;
+    cascade_transaction BOOLEAN := false;
+
+BEGIN
+    -- Cascade transaction flag is set to true to prevent creation of
+    -- the audit entries for the options when the options are
+    -- created as part of the parent object creation or update.
+    -- For example: when the option is added as part of the subnet
+    -- addition, the cascade transaction flag is equal to true. If
+    -- the option is added into the existing subnet the cascade
+    -- transaction is equal to false. Note that depending on the option
+    -- scope the audit entry will contain the object_type value
+    -- of the parent object to cause the server to replace the
+    -- entire subnet. The only case when the object_type will be
+    -- set to 'dhcp6_options' is when a global option is added.
+    -- Global options do not have the owner.
+    cascade_transaction := get_session_boolean('kea.cascade_transaction');
+    IF cascade_transaction = false THEN
+        -- todo: host manager hasn't been updated to use audit
+        -- mechanisms so ignore host specific options for now.
+        IF scope_id = 0 THEN
+            -- If a global option is added or modified, create audit
+            -- entry for the 'dhcp6_options' table.
+            PERFORM createAuditEntryDHCP6('dhcp6_options', option_id, modification_type);
+        ELSEIF scope_id = 1 THEN
+            -- If subnet specific option is added or modified, update
+            -- the modification timestamp of this subnet to allow the
+            -- servers to refresh the subnet information. This will
+            -- also result in creating an audit entry for this subnet.
+            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
+                WHERE subnet_id = p_subnet_id;
+        ELSEIF scope_id = 4 THEN
+            -- If shared network specific option is added or modified,
+            -- update the modification timestamp of this shared network
+            -- to allow the servers to refresh the shared network
+            -- information. This will also result in creating an
+            -- audit entry for this shared network.
+           SELECT id INTO snid FROM dhcp6_shared_network WHERE name = network_name LIMIT 1;
+           UPDATE dhcp6_shared_network SET modification_ts = p_modification_ts
+                WHERE id = snid;
+        ELSEIF scope_id = 5 THEN
+            -- If pool specific option is added or modified, update
+            -- the modification timestamp of the owning subnet.
+            SELECT dhcp6_pool.subnet_id INTO sid FROM dhcp6_pool WHERE id = pool_id;
+            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
+                WHERE subnet_id = sid;
+        ELSEIF scope_id = 6 THEN
+            -- If pd pool specific option is added or modified, create
+            -- audit entry for the subnet which this pool belongs to.
+            SELECT dhcp6_pd_pool.subnet_id INTO sid FROM dhcp6_pd_pool WHERE id = pool_id;
+            UPDATE dhcp6_subnet SET modification_ts = p_modification_ts
+                WHERE subnet_id = sid;
+        END IF;
+    END IF;
+    RETURN;
+END;$$;
+
 -- Update the schema version number
 UPDATE schema_version
     SET version = '8', minor = '0';