From 1e12757c6dbbb7a48923705437426fe24f86e10a Mon Sep 17 00:00:00 2001
From: Abyss777 <abyss@fox5.ru>
Date: Wed, 8 Feb 2017 17:48:28 +0500
Subject: - Replace fk_user_user_manageduserid in MS SQL with trigger

- Drop defaultValue before add
---
 schema/changelog-3.10.xml | 28 +++++++++++++++++++++++++++-
 1 file changed, 27 insertions(+), 1 deletion(-)

(limited to 'schema')

diff --git a/schema/changelog-3.10.xml b/schema/changelog-3.10.xml
index c4d9b7b18..33f02880d 100644
--- a/schema/changelog-3.10.xml
+++ b/schema/changelog-3.10.xml
@@ -62,12 +62,12 @@
     </createTable>
 
     <addForeignKeyConstraint baseTableName="user_user" baseColumnNames="userid" constraintName="fk_user_user_userid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" />
-    <addForeignKeyConstraint baseTableName="user_user" baseColumnNames="manageduserid" constraintName="fk_user_user_manageduserid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" />
 
     <update tableName="users">
         <column name="devicelimit" valueNumeric="-1" />
         <where>devicelimit = 0</where>
     </update>
+    <dropDefaultValue tableName="users" columnName="devicelimit" />
     <addDefaultValue tableName="users" columnName="devicelimit" defaultValueNumeric="-1" />
 
     <addColumn tableName="users">
@@ -75,4 +75,30 @@
     </addColumn>
 
   </changeSet>
+  <changeSet author="author" id="changelog-3.10-notmssql">
+
+    <preConditions onFail="MARK_RAN">
+      <not>
+        <dbms type="mssql" />
+      </not>
+    </preConditions>
+    <addForeignKeyConstraint baseTableName="user_user" baseColumnNames="manageduserid" constraintName="fk_user_user_manageduserid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" />
+
+  </changeSet>
+
+  <changeSet author="author" id="changelog-3.10-mssql">
+
+    <preConditions onFail="MARK_RAN">
+      <dbms type="mssql" />
+    </preConditions>
+    <sql>
+      CREATE TRIGGER tg_users_delete
+      ON users FOR DELETE
+      AS BEGIN
+        DELETE FROM user_user WHERE managedUserId IN (SELECT deleted.id FROM deleted)
+      END
+    </sql>
+
+  </changeSet>
+
 </databaseChangeLog>
-- 
cgit v1.2.3


From c8da62d78ea19dbdaa15697e7f31dbd02474864d Mon Sep 17 00:00:00 2001
From: Abyss777 <abyss@fox5.ru>
Date: Thu, 9 Feb 2017 09:39:54 +0500
Subject: Add trigger on group deleting

---
 schema/changelog-3.10.xml | 14 +++++++++++---
 1 file changed, 11 insertions(+), 3 deletions(-)

(limited to 'schema')

diff --git a/schema/changelog-3.10.xml b/schema/changelog-3.10.xml
index 33f02880d..ad8c34668 100644
--- a/schema/changelog-3.10.xml
+++ b/schema/changelog-3.10.xml
@@ -34,11 +34,11 @@
 
     <addForeignKeyConstraint baseTableName="user_calendar" baseColumnNames="userid" constraintName="fk_user_calendar_userid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" />
     <addForeignKeyConstraint baseTableName="user_calendar" baseColumnNames="calendarid" constraintName="fk_user_calendar_geofenceid" referencedTableName="calendars" referencedColumnNames="id" onDelete="CASCADE" />
-    
+
     <addColumn tableName="geofences">
       <column name="calendarid" type="INT" />
     </addColumn>
-    
+
     <addForeignKeyConstraint baseColumnNames="calendarid" baseTableName="geofences" constraintName="fk_geofence_calendar_calendarid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="calendars"/>
 
     <addColumn tableName="positions">
@@ -75,6 +75,7 @@
     </addColumn>
 
   </changeSet>
+
   <changeSet author="author" id="changelog-3.10-notmssql">
 
     <preConditions onFail="MARK_RAN">
@@ -95,7 +96,14 @@
       CREATE TRIGGER tg_users_delete
       ON users FOR DELETE
       AS BEGIN
-        DELETE FROM user_user WHERE managedUserId IN (SELECT deleted.id FROM deleted)
+        DELETE FROM user_user WHERE manageduserid IN (SELECT deleted.id FROM deleted)
+      END
+    </sql>
+    <sql>
+      CREATE TRIGGER tg_groups_delete
+      ON groups FOR DELETE
+      AS BEGIN
+        UPDATE groups SET groupid = NULL WHERE groupid IN (SELECT deleted.id FROM deleted)
       END
     </sql>
 
-- 
cgit v1.2.3


From 0d789419b2707be5160a71d2073d48c15f3daf50 Mon Sep 17 00:00:00 2001
From: Abyss777 <abyss@fox5.ru>
Date: Thu, 9 Feb 2017 10:29:06 +0500
Subject: Split trigger to 3.7-mssql changeset

---
 schema/changelog-3.10.xml | 8 ++++++++
 1 file changed, 8 insertions(+)

(limited to 'schema')

diff --git a/schema/changelog-3.10.xml b/schema/changelog-3.10.xml
index ad8c34668..4c64373e9 100644
--- a/schema/changelog-3.10.xml
+++ b/schema/changelog-3.10.xml
@@ -99,6 +99,14 @@
         DELETE FROM user_user WHERE manageduserid IN (SELECT deleted.id FROM deleted)
       END
     </sql>
+
+  </changeSet>
+
+  <changeSet author="author" id="changelog-3.7-mssql">
+
+    <preConditions onFail="MARK_RAN">
+      <dbms type="mssql" />
+    </preConditions>
     <sql>
       CREATE TRIGGER tg_groups_delete
       ON groups FOR DELETE
-- 
cgit v1.2.3