Sahina Bose has uploaded a new change for review.
Change subject: engine: Modified fk_validator to handle composite foreign keys
......................................................................
engine: Modified fk_validator to handle composite foreign keys
In case of composite foreign keys, the table_col and fk_col in
the select references cursor was empty, which causes the validation
to error out.
for instance, if fk is on a composite primary key like this
"(sched_name, trigger_name, trigger_group)"
the regular expression should consider "," and space.
Also,in the query to fetch violations, the inner select
clause for composite keys should return multiple columns as
opposed to single column - hence trimming "()"
Change-Id: Ie4210537c6ef7ce7f89bd73be9e44178b6b8d43a
Signed-off-by: Sahina Bose <sab...@redhat.com>
---
M packaging/setup/dbutils/fkvalidator_sp.sql
1 file changed, 4 insertions(+), 4 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/03/36603/1
diff --git a/packaging/setup/dbutils/fkvalidator_sp.sql
b/packaging/setup/dbutils/fkvalidator_sp.sql
index 2168cbe..d258ae4 100644
--- a/packaging/setup/dbutils/fkvalidator_sp.sql
+++ b/packaging/setup/dbutils/fkvalidator_sp.sql
@@ -16,9 +16,9 @@
v_cur CURSOR FOR
SELECT
c.relname as table_name,
- substring(substring ((select pg_get_constraintdef(r.oid)) from
'[a-zA-Z0-9_\-][(][a-zA-Z0-9_\-]+[)]') from 2) as table_col,
+ substring(substring ((select pg_get_constraintdef(r.oid)) from
'[a-zA-Z0-9_, \-][(][a-zA-Z0-9_, \-]+[)]') from 2) as table_col,
c2.relname AS fk_table_name,
- substring ((select pg_get_constraintdef(r.oid)) from '
[(][a-zA-Z0-9_\-]+[)] ') as fk_col
+ substring ((select pg_get_constraintdef(r.oid)) from ' [(][a-zA-Z0-9_,
\-]+[)] ') as fk_col
FROM pg_class c, pg_class c2, pg_constraint r
WHERE c.relname in (select table_name from information_schema.tables
where table_schema not in ('pg_catalog','information_schema') and
table_type = 'BASE TABLE') AND
@@ -38,12 +38,12 @@
IF (v_fix_it) THEN
v_sql := 'delete from ' || v_record.fk_table_name ||
' where ' || v_record.fk_col || 'IS NOT NULL and ' ||
v_record.fk_col || ' not in (select ' ||
- v_record.table_col || ' from ' || v_record.table_name ||
');';
+ trim(both '()' from v_record.table_col) || ' from ' ||
v_record.table_name || ');';
v_msg := 'Fixing violation/s found in ' || v_record.fk_table_name
;
ELSE
v_sql := 'select ' || v_record.fk_col || ' from ' ||
v_record.fk_table_name ||
' where ' || v_record.fk_col || 'IS NOT NULL and ' ||
v_record.fk_col || ' not in (select ' ||
- v_record.table_col || ' from ' || v_record.table_name ||
');';
+ trim(both '()' from v_record.table_col) || ' from ' ||
v_record.table_name || ');';
v_msg := 'Constraint violation found in ' ||
v_record.fk_table_name || v_record.fk_col;
END IF;
EXECUTE v_sql;
--
To view, visit http://gerrit.ovirt.org/36603
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Ie4210537c6ef7ce7f89bd73be9e44178b6b8d43a
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: master
Gerrit-Owner: Sahina Bose <sab...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
