-- adding a table named object_column_white_list that defined displayed columns per object (table or view)
CREATE OR REPLACE FUNCTION __temp_add_object_column_white_list_table()
RETURNS void
AS $function$
BEGIN

   -- This table holds the column white list per object
   IF NOT EXISTS (SELECT * FROM pg_tables WHERE tablename ILIKE 'object_column_white_list') THEN
      CREATE TABLE object_column_white_list
      (
         object_name varchar(128) NOT NULL,
         column_name varchar(128) NOT NULL,
         CONSTRAINT pk_object_column_white_list PRIMARY KEY(object_name,column_name)
      ) WITH OIDS;
   END IF;

   -- this table holds generated sql per object and is truncated upon upgrade to reflect schema changes
   IF EXISTS (SELECT * FROM pg_tables WHERE tablename ILIKE 'object_column_white_list_sql') THEN
       truncate table object_column_white_list_sql;
   ELSE
      CREATE TABLE object_column_white_list_sql
      (
         object_name varchar(128) NOT NULL,
         sql text NOT NULL,
         CONSTRAINT pk_object_column_white_list_sql PRIMARY KEY(object_name)
      ) WITH OIDS;

   END IF;
   -----------------------------------
   -- Initial white list settings  ---
   -----------------------------------
   --            vds view
   -----------------------------------
   --  A new added column will not be displayed for the user unless added specifically.
   if not exists (select 1 from object_column_white_list where object_name = 'vds') then
      insert into object_column_white_list(object_name,column_name)
      (select 'vds', column_name
       from information_schema.columns
       where table_name = 'vds' and
       column_name in (
          'vds_group_id', 'vds_group_name', 'vds_group_description', 'selection_algorithm',
          'vds_id', 'vds_name', 'ip', 'vds_unique_id', 'host_name', 'port', 'vds_strength',
          'server_ssl_enabled', 'vds_type', 'pm_type', 'pm_user', 'pm_password', 'pm_port',
          'pm_options', 'pm_enabled', 'vds_spm_priority', 'hooks', 'status', 'cpu_cores',
          'cpu_model', 'cpu_speed_mh', 'if_total_speed', 'kvm_enabled', 'physical_mem_mb',
          'pending_vcpus_count', 'pending_vmem_size', 'mem_commited', 'vm_active', 'vm_count',
          'vm_migrating', 'vms_cores_count', 'cpu_over_commit_time_stamp', 'hypervisor_type',
          'net_config_dirty', 'high_utilization', 'low_utilization', 'max_vds_memory_over_commit',
          'cpu_over_commit_duration_minutes', 'storage_pool_id', 'storage_pool_name', 'reserved_mem',
          'guest_overhead', 'software_version', 'version_name', 'build_name', 'previous_status',
          'cpu_idle', 'cpu_load', 'cpu_sys', 'cpu_user', 'usage_mem_percent', 'usage_cpu_percent',
          'usage_network_percent', 'mem_available', 'mem_shared', 'swap_free', 'swap_total', 'ksm_cpu_percent',
          'ksm_pages', 'ksm_state', 'cpu_flags', 'vds_group_cpu_name', 'cpu_sockets', 'vds_spm_id',
          'otp_validity', 'spm_status', 'supported_cluster_levels', 'supported_engines',
          'vds_group_compatibility_version', 'host_os', 'kvm_version', 'spice_version', 'kernel_version',
          'iscsi_initiator_name', 'transparent_hugepages_state', 'anonymous_hugepages',
          'non_operational_reason', 'recoverable'));
-- pm_options are missing
   end if;
END; $function$
LANGUAGE plpgsql;
SELECT * FROM __temp_add_object_column_white_list_table();
DROP FUNCTION __temp_add_object_column_white_list_table();

-----------------------------------------------------
-- Object white list modification section
-- use fn_db_add_column_to_object_white_list(obj,col)
-- to add new object columns to the white list
-- since this script must remain reentrant
-----------------------------------------------------


