From 74c1e947ba979ebce33efffa5b6389354282881a Mon Sep 17 00:00:00 2001 From: Anton Tananaev Date: Sat, 19 Dec 2015 09:05:07 +1300 Subject: Normalize database foreign key names --- database/db.changelog-3.2.xml | 16 ------ database/db.changelog-3.3.xml | 18 ------ database/db.changelog-master.xml | 1 + database/db.changelog-normalize.xml | 112 ++++++++++++++++++++++++++++++++++++ 4 files changed, 113 insertions(+), 34 deletions(-) create mode 100644 database/db.changelog-normalize.xml diff --git a/database/db.changelog-3.2.xml b/database/db.changelog-3.2.xml index 8c7a0cc8a..bcf20c860 100644 --- a/database/db.changelog-3.2.xml +++ b/database/db.changelog-3.2.xml @@ -5,22 +5,6 @@ xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"> - - - - - - - - - - - - - - - - diff --git a/database/db.changelog-3.3.xml b/database/db.changelog-3.3.xml index 4c5c216db..cb9823ef8 100644 --- a/database/db.changelog-3.3.xml +++ b/database/db.changelog-3.3.xml @@ -5,24 +5,6 @@ xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"> - - - - - - - - - - - - - - - - - - diff --git a/database/db.changelog-master.xml b/database/db.changelog-master.xml index c0dd947b1..06fc7a695 100644 --- a/database/db.changelog-master.xml +++ b/database/db.changelog-master.xml @@ -5,6 +5,7 @@ xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"> + diff --git a/database/db.changelog-normalize.xml b/database/db.changelog-normalize.xml new file mode 100644 index 000000000..21a2c45d7 --- /dev/null +++ b/database/db.changelog-normalize.xml @@ -0,0 +1,112 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + CREATE ALIAS IF NOT EXISTS EXECUTE AS $$ void executeSql(Connection conn, String sql) throws SQLException { try { conn.createStatement().executeUpdate(sql); } catch (Exception e) {} } $$; + + CALL EXECUTE('ALTER TABLE POSITION DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'POSITION' AND COLUMN_LIST = 'DEVICEID')); + CALL EXECUTE('ALTER TABLE DEVICE DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'DEVICE' AND COLUMN_LIST = 'POSITIONID')); + CALL EXECUTE('ALTER TABLE DEVICE DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'DEVICE' AND COLUMN_LIST = 'DATAID')); + CALL EXECUTE('ALTER TABLE USER_DEVICE DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'USER_DEVICE' AND COLUMN_LIST = 'USERID')); + CALL EXECUTE('ALTER TABLE USER_DEVICE DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'USER_DEVICE' AND COLUMN_LIST = 'DEVICEID')); + CALL EXECUTE('ALTER TABLE DATA DROP CONSTRAINT ' || (SELECT DISTINCT UNIQUE_INDEX_NAME FROM INFORMATION_SCHEMA.CONSTRAINTS WHERE CONSTRAINT_TYPE = 'REFERENTIAL' AND TABLE_NAME = 'DATA' AND COLUMN_LIST = 'DEVICEID')); + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + -- cgit v1.2.3