Eli Mesika has uploaded a new change for review. Change subject: [WIP] : [db] create application objects within own schema ......................................................................
[WIP] : [db] create application objects within own schema creating a separate ovirt_engine schema for all engine db objects. Handling clean install and upgrades Change-Id: Ic1801ce852e7637523314d57b34cdfaa58f425b1 Bug-Url: https://bugzilla.redhat.com/show_bug.cgi?id=951923 Signed-off-by: Eli Mesika <[email protected]> --- M backend/manager/dbscripts/common_sp.sql M backend/manager/dbscripts/create_functions.sql M backend/manager/dbscripts/create_schema.sh M backend/manager/dbscripts/dbcustomfunctions.sh M backend/manager/dbscripts/exportDbSchema.sh A backend/manager/dbscripts/upgrade/03_03_0160_set_ovirt_schema.sh M backend/manager/dbscripts/upgrade/post_upgrade/0010_add_object_column_white_list_table.sql A backend/manager/dbscripts/upgrade/pre_upgrade/0001_set_schema.sql 8 files changed, 52 insertions(+), 12 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/78/14978/1 diff --git a/backend/manager/dbscripts/common_sp.sql b/backend/manager/dbscripts/common_sp.sql index 91d99aa..c5cc487 100644 --- a/backend/manager/dbscripts/common_sp.sql +++ b/backend/manager/dbscripts/common_sp.sql @@ -272,14 +272,14 @@ Create or replace FUNCTION generate_drop_all_functions_syntax() RETURNS SETOF text AS $procedure$ BEGIN -RETURN QUERY select 'drop function if exists ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ') cascade;' from pg_proc inner join pg_namespace ns on (pg_proc.pronamespace=ns.oid) where ns.nspname = 'public' and proname not ilike 'uuid%' order by proname; +RETURN QUERY select 'drop function if exists ' || ns.nspname || '.' || proname || '(' || oidvectortypes(proargtypes) || ') cascade;' from pg_proc inner join pg_namespace ns on (pg_proc.pronamespace=ns.oid) where ns.nspname = 'ovirt_engine' and proname not ilike 'uuid%' order by proname; END; $procedure$ LANGUAGE plpgsql; Create or replace FUNCTION generate_drop_all_views_syntax() RETURNS SETOF text AS $procedure$ BEGIN -RETURN QUERY select 'DROP VIEW if exists ' || table_name || ' CASCADE;' from information_schema.views where table_schema = 'public' order by table_name; +RETURN QUERY select 'DROP VIEW if exists ' || table_name || ' CASCADE;' from information_schema.views where table_schema = 'ovirt_engine' order by table_name; END; $procedure$ LANGUAGE plpgsql; @@ -292,7 +292,7 @@ retvalue := character_maximum_length from information_schema.columns where table_name ilike v_table and column_name ilike v_column and - table_schema = 'public' and udt_name in ('char','varchar'); + table_schema = 'ovirt_engine' and udt_name in ('char','varchar'); return retvalue; END; $procedure$ LANGUAGE plpgsql; diff --git a/backend/manager/dbscripts/create_functions.sql b/backend/manager/dbscripts/create_functions.sql index 06c74d5..a97d828 100644 --- a/backend/manager/dbscripts/create_functions.sql +++ b/backend/manager/dbscripts/create_functions.sql @@ -31,7 +31,7 @@ END; $function$ LANGUAGE plpgsql IMMUTABLE; -CREATE OR REPLACE FUNCTION public.fnSplitter(ids TEXT) RETURNS SETOF idTextType AS +CREATE OR REPLACE FUNCTION fnSplitter(ids TEXT) RETURNS SETOF idTextType AS $function$ BEGIN RETURN QUERY @@ -56,7 +56,7 @@ --All permissions of current user (include groups) DROP TYPE IF EXISTS user_permissions CASCADE; CREATE TYPE user_permissions AS(permission_id UUID, role_id UUID, user_id UUID); -CREATE OR REPLACE FUNCTION public.fn_user_permissions(v_userId IN uuid) RETURNS SETOF user_permissions AS +CREATE OR REPLACE FUNCTION fn_user_permissions(v_userId IN uuid) RETURNS SETOF user_permissions AS $function$ DECLARE @@ -90,7 +90,7 @@ LANGUAGE 'plpgsql'; -CREATE OR REPLACE FUNCTION public.fn_get_entity_parents(v_entity_id IN uuid, v_object_type IN int4) RETURNS SETOF idUuidType AS +CREATE OR REPLACE FUNCTION fn_get_entity_parents(v_entity_id IN uuid, v_object_type IN int4) RETURNS SETOF idUuidType AS $function$ /* Gets a list of all parent GUID to the system root (including) @@ -303,7 +303,7 @@ -CREATE OR REPLACE FUNCTION public.fn_get_disk_commited_value_by_storage(v_storage_domain_id IN uuid) RETURNS integer AS +CREATE OR REPLACE FUNCTION fn_get_disk_commited_value_by_storage(v_storage_domain_id IN uuid) RETURNS integer AS $function$ DECLARE result integer; @@ -394,7 +394,7 @@ END; $function$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION public.fn_get_ad_element_name(v_ad_element_id IN uuid) RETURNS text AS +CREATE OR REPLACE FUNCTION fn_get_ad_element_name(v_ad_element_id IN uuid) RETURNS text AS $function$ DECLARE result text; @@ -412,7 +412,7 @@ END; $function$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION public.fn_get_entity_name(v_entity_id IN uuid, v_object_type IN int4) RETURNS text AS +CREATE OR REPLACE FUNCTION fn_get_entity_name(v_entity_id IN uuid, v_object_type IN int4) RETURNS text AS $function$ /* Gets object name by its id and type diff --git a/backend/manager/dbscripts/create_schema.sh b/backend/manager/dbscripts/create_schema.sh index 567167a..1faf036 100755 --- a/backend/manager/dbscripts/create_schema.sh +++ b/backend/manager/dbscripts/create_schema.sh @@ -52,6 +52,9 @@ echo user name is: ${USERNAME} +CMD="create schema ovirt_engine;ALTER DATABASE ${DATABASE} SET search_path=ovirt_engine,public;" +execute_command "${CMD}" ${DATABASE} ${SERVERNAME} ${PORT}> /dev/null + printf "Creating tables...\n" execute_file "create_tables.sql" ${DATABASE} ${SERVERNAME} ${PORT} > /dev/null diff --git a/backend/manager/dbscripts/dbcustomfunctions.sh b/backend/manager/dbscripts/dbcustomfunctions.sh index f92830d..ba3035b 100755 --- a/backend/manager/dbscripts/dbcustomfunctions.sh +++ b/backend/manager/dbscripts/dbcustomfunctions.sh @@ -43,7 +43,7 @@ cmd="select c.relname \ from pg_class c join pg_roles r on r.oid = c.relowner join pg_namespace n on n.oid = c.relnamespace \ where c.relkind in ('r','v','S') \ - and n.nspname = 'public' and r.rolname != '${DBOBJECT_OWNER}';" + and n.nspname = 'ovirt_engine' and r.rolname != '${DBOBJECT_OWNER}';" res=$(execute_command "${cmd}" engine ${SERVERNAME} ${PORT}) if [ -n "${res}" ]; then cmd="" diff --git a/backend/manager/dbscripts/exportDbSchema.sh b/backend/manager/dbscripts/exportDbSchema.sh index a17e69b..a5ab726 100755 --- a/backend/manager/dbscripts/exportDbSchema.sh +++ b/backend/manager/dbscripts/exportDbSchema.sh @@ -39,7 +39,7 @@ esac done -pg_dump -f .${DATABASE}.schema -F p -n public -s -U ${USERNAME} ${DATABASE} -h ${SERVERNAME} -p ${PORT} >& /dev/null +pg_dump -f .${DATABASE}.schema -F p -n "ovirt_engine" -s -U ${USERNAME} ${DATABASE} -h ${SERVERNAME} -p ${PORT} >& /dev/null printf "Done.\n" printf "Exported file is .${DATABASE}.schema.\n" diff --git a/backend/manager/dbscripts/upgrade/03_03_0160_set_ovirt_schema.sh b/backend/manager/dbscripts/upgrade/03_03_0160_set_ovirt_schema.sh new file mode 100755 index 0000000..acc6412 --- /dev/null +++ b/backend/manager/dbscripts/upgrade/03_03_0160_set_ovirt_schema.sh @@ -0,0 +1,23 @@ +#!/bin/bash + +#include db general functions +source ./dbfunctions.sh + + +# set db search to look first in the ovirt-engine schema +CMD="ALTER DATABASE ${DATABASE} SET search_path=ovirt_engine,public;" +execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} > /dev/null + + +file=$(mktemp) +# modify all tables and sequences to be under the ovirt-engine schema +CMD="select 'alter table ' || tablename || ' set schema ovirt_engine;' from pg_tables where schemaname != 'ovirt_engine'and substring(tablename from 1 for 3) not in ('pg_','sql');" +execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} > $file + +CMD="select 'alter sequence ' || relname || ' set schema ovirt_engine;' from pg_statio_user_sequences where schemaname != 'ovirt_engine';" +execute_command "${CMD}" "${DATABASE}" ${SERVERNAME} ${PORT} >> $file + +psql -U ${USERNAME} -h ${SERVERNAME} -p ${PORT} -f $file ${DATABASE} > /dev/null + + + diff --git a/backend/manager/dbscripts/upgrade/post_upgrade/0010_add_object_column_white_list_table.sql b/backend/manager/dbscripts/upgrade/post_upgrade/0010_add_object_column_white_list_table.sql index 6f50f84..b3283ee 100644 --- a/backend/manager/dbscripts/upgrade/post_upgrade/0010_add_object_column_white_list_table.sql +++ b/backend/manager/dbscripts/upgrade/post_upgrade/0010_add_object_column_white_list_table.sql @@ -34,7 +34,7 @@ ----------------------------------- -- A new added column will not be displayed for the user unless added specifically. insert into object_column_white_list(object_name,column_name) - (select 'vds', column_name + (select distinct 'vds', column_name from information_schema.columns where table_name = 'vds' and column_name in ( diff --git a/backend/manager/dbscripts/upgrade/pre_upgrade/0001_set_schema.sql b/backend/manager/dbscripts/upgrade/pre_upgrade/0001_set_schema.sql new file mode 100644 index 0000000..4a7fbf8 --- /dev/null +++ b/backend/manager/dbscripts/upgrade/pre_upgrade/0001_set_schema.sql @@ -0,0 +1,14 @@ +create or replace function __temp_set_schema() +RETURNS void +AS $procedure$ +BEGIN + IF NOT EXISTS (select 1 from pg_catalog.pg_namespace where nspname = 'ovirt_engine') THEN + CREATE SCHEMA ovirt_engine; + END IF; +END; $procedure$ +LANGUAGE plpgsql; +SELECT __temp_set_schema(); +DROP FUNCTION __temp_set_schema(); + + + -- To view, visit http://gerrit.ovirt.org/14978 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: Ic1801ce852e7637523314d57b34cdfaa58f425b1 Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Eli Mesika <[email protected]> _______________________________________________ Engine-patches mailing list [email protected] http://lists.ovirt.org/mailman/listinfo/engine-patches
