diff options
Diffstat (limited to 'schema')
| -rw-r--r-- | schema/changelog-3.10.xml | 24 | ||||
| -rw-r--r-- | schema/changelog-3.11.xml | 6 | ||||
| -rw-r--r-- | schema/changelog-3.12.xml | 20 | ||||
| -rw-r--r-- | schema/changelog-3.14.xml | 7 | ||||
| -rw-r--r-- | schema/changelog-3.15.xml | 16 | ||||
| -rw-r--r-- | schema/changelog-3.16.xml | 37 | ||||
| -rw-r--r-- | schema/changelog-3.17.xml | 95 | ||||
| -rw-r--r-- | schema/changelog-3.3.xml | 7 | ||||
| -rw-r--r-- | schema/changelog-3.5.xml | 3 | ||||
| -rw-r--r-- | schema/changelog-3.6.xml | 3 | ||||
| -rw-r--r-- | schema/changelog-3.7.xml | 19 | ||||
| -rw-r--r-- | schema/changelog-3.8.xml | 17 | ||||
| -rw-r--r-- | schema/changelog-3.9.xml | 21 | ||||
| -rw-r--r-- | schema/changelog-4.0-clean.xml | 649 | ||||
| -rw-r--r-- | schema/changelog-4.0.xml | 192 | ||||
| -rw-r--r-- | schema/changelog-4.1.xml | 47 | ||||
| -rw-r--r-- | schema/changelog-4.7.xml | 28 | ||||
| -rw-r--r-- | schema/changelog-master.xml | 9 |
18 files changed, 1166 insertions, 34 deletions
diff --git a/schema/changelog-3.10.xml b/schema/changelog-3.10.xml index 4c64373e9..5495819bd 100644 --- a/schema/changelog-3.10.xml +++ b/schema/changelog-3.10.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.10"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <createTable tableName="calendars"> <column name="id" type="INT" autoIncrement="true"> <constraints primaryKey="true" /> @@ -39,7 +45,7 @@ <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"/> + <addForeignKeyConstraint baseColumnNames="calendarid" baseTableName="geofences" constraintName="fk_geofence_calendar_calendarid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="calendars" /> <addColumn tableName="positions"> <column name="accuracy" type="DOUBLE" defaultValueNumeric="0"> @@ -64,8 +70,8 @@ <addForeignKeyConstraint baseTableName="user_user" baseColumnNames="userid" constraintName="fk_user_user_userid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" /> <update tableName="users"> - <column name="devicelimit" valueNumeric="-1" /> - <where>devicelimit = 0</where> + <column name="devicelimit" valueNumeric="-1" /> + <where>devicelimit = 0</where> </update> <dropDefaultValue tableName="users" columnName="devicelimit" /> <addDefaultValue tableName="users" columnName="devicelimit" defaultValueNumeric="-1" /> @@ -80,9 +86,13 @@ <preConditions onFail="MARK_RAN"> <not> + <tableExists tableName="tc_servers" /> + </not> + <not> <dbms type="mssql" /> </not> </preConditions> + <addForeignKeyConstraint baseTableName="user_user" baseColumnNames="manageduserid" constraintName="fk_user_user_manageduserid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" /> </changeSet> @@ -90,8 +100,12 @@ <changeSet author="author" id="changelog-3.10-mssql"> <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> <dbms type="mssql" /> </preConditions> + <sql> CREATE TRIGGER tg_users_delete ON users FOR DELETE @@ -105,8 +119,12 @@ <changeSet author="author" id="changelog-3.7-mssql"> <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> <dbms type="mssql" /> </preConditions> + <sql> CREATE TRIGGER tg_groups_delete ON groups FOR DELETE diff --git a/schema/changelog-3.11.xml b/schema/changelog-3.11.xml index 1fdaa3f17..31893c0df 100644 --- a/schema/changelog-3.11.xml +++ b/schema/changelog-3.11.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.11"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <addColumn tableName="users"> <column name="phone" type="VARCHAR(128)" /> </addColumn> diff --git a/schema/changelog-3.12.xml b/schema/changelog-3.12.xml index 19c67cb36..0d8c8550f 100644 --- a/schema/changelog-3.12.xml +++ b/schema/changelog-3.12.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.12"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <addColumn tableName="statistics"> <column name="mailsent" type="INT" defaultValueNumeric="0"> <constraints nullable="false" /> @@ -77,12 +83,6 @@ <addForeignKeyConstraint baseTableName="device_attribute" baseColumnNames="deviceid" constraintName="fk_device_attribute_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" /> <addForeignKeyConstraint baseTableName="device_attribute" baseColumnNames="attributeid" constraintName="fk_device_attribute_attributeid" referencedTableName="attributes" referencedColumnNames="id" onDelete="CASCADE" /> - <!--<dropForeignKeyConstraint baseTableName="positions" constraintName="fk_position_deviceid" /> - <addForeignKeyConstraint baseTableName="positions" baseColumnNames="deviceid" constraintName="fk_positions_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" /> - - <dropForeignKeyConstraint baseTableName="events" constraintName="fk_event_deviceid" /> - <addForeignKeyConstraint baseTableName="events" baseColumnNames="deviceid" constraintName="fk_events_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" />--> - <dropForeignKeyConstraint baseTableName="device_geofence" constraintName="fk_user_device_geofence_deviceid" /> <addForeignKeyConstraint baseTableName="device_geofence" baseColumnNames="deviceid" constraintName="fk_device_geofence_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" /> <dropForeignKeyConstraint baseTableName="device_geofence" constraintName="fk_user_device_geofence_geofenceid" /> @@ -100,9 +100,13 @@ <preConditions onFail="MARK_RAN"> <not> + <tableExists tableName="tc_servers" /> + </not> + <not> <dbms type="mssql" /> </not> </preConditions> + <dropForeignKeyConstraint baseTableName="groups" constraintName="fk_group_group_groupid" /> <addForeignKeyConstraint baseTableName="groups" baseColumnNames="groupid" constraintName="fk_groups_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups" /> @@ -111,9 +115,13 @@ <changeSet author="author" id="changelog-3.12-pgsql"> <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> <dbms type="postgresql" /> <sqlCheck expectedResult="oid">SELECT data_type FROM information_schema.columns WHERE table_name = 'calendars' AND column_name = 'data';</sqlCheck> </preConditions> + <dropColumn tableName="calendars" columnName="data" /> <addColumn tableName="calendars"> <column name="data" type="bytea"> diff --git a/schema/changelog-3.14.xml b/schema/changelog-3.14.xml index f6cda4c1f..063adeed7 100644 --- a/schema/changelog-3.14.xml +++ b/schema/changelog-3.14.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.14"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <createTable tableName="drivers"> <column name="id" type="INT" autoIncrement="true"> <constraints primaryKey="true" /> @@ -64,4 +70,5 @@ <renameTable oldTableName="server" newTableName="servers" /> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.15.xml b/schema/changelog-3.15.xml index 9756fe696..a9bea1ca1 100644 --- a/schema/changelog-3.15.xml +++ b/schema/changelog-3.15.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.15"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <dropForeignKeyConstraint baseTableName="attribute_aliases" constraintName="fk_attribute_aliases_deviceid" /> <dropUniqueConstraint tableName="attribute_aliases" constraintName="uk_deviceid_attribute" /> @@ -104,7 +110,7 @@ INSERT INTO user_notification (notificationid, userid) SELECT id AS notificationid, userid FROM notifications; </sql> - <dropForeignKeyConstraint baseTableName="notifications" constraintName="fk_notifications_userid"/> + <dropForeignKeyConstraint baseTableName="notifications" constraintName="fk_notifications_userid" /> <dropColumn tableName="notifications" columnName="userid" /> <addForeignKeyConstraint baseTableName="user_notification" baseColumnNames="notificationid" constraintName="fk_user_notification_notificationid" referencedTableName="notifications" referencedColumnNames="id" onDelete="CASCADE" /> @@ -133,5 +139,13 @@ <addForeignKeyConstraint baseTableName="device_notification" baseColumnNames="deviceid" constraintName="fk_device_notification_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" /> <addForeignKeyConstraint baseTableName="device_notification" baseColumnNames="notificationid" constraintName="fk_device_notification_notificationid" referencedTableName="notifications" referencedColumnNames="id" onDelete="CASCADE" /> + <dropNotNullConstraint tableName="users" columnName="hashedpassword" columnDataType="VARCHAR(128)" /> + <dropNotNullConstraint tableName="users" columnName="salt" columnDataType="VARCHAR(128)" /> + + <addColumn tableName="users"> + <column name="login" type="VARCHAR(128)" /> + </addColumn> + </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.16.xml b/schema/changelog-3.16.xml new file mode 100644 index 000000000..b5af517da --- /dev/null +++ b/schema/changelog-3.16.xml @@ -0,0 +1,37 @@ +<?xml version="1.0" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd" + logicalFilePath="changelog-3.16"> + + <changeSet author="author" id="changelog-3.16"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + + <addColumn tableName="devices"> + <column name="disabled" type="BOOLEAN" defaultValueBoolean="false" /> + </addColumn> + + <addColumn tableName="users"> + <column name="poilayer" type="VARCHAR(512)" /> + </addColumn> + + <addColumn tableName="servers"> + <column name="poilayer" type="VARCHAR(512)" /> + </addColumn> + + <addColumn tableName="notifications"> + <column name="calendarid" type="INT" /> + </addColumn> + + <addForeignKeyConstraint baseColumnNames="calendarid" baseTableName="notifications" constraintName="fk_notification_calendar_calendarid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="calendars" /> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-3.17.xml b/schema/changelog-3.17.xml new file mode 100644 index 000000000..dd4e18b2a --- /dev/null +++ b/schema/changelog-3.17.xml @@ -0,0 +1,95 @@ +<?xml version="1.0" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd" + logicalFilePath="changelog-3.17"> + + <changeSet author="author" id="changelog-3.3-admin"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + <not> + <columnExists tableName="users" columnName="administrator" /> + </not> + </preConditions> + + <renameColumn tableName="users" columnDataType="BOOLEAN" oldColumnName="admin" newColumnName="administrator" /> + + </changeSet> + + <changeSet author="author" id="changelog-3.17"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + + <addColumn tableName="events"> + <column name="maintenanceid" type="INT" /> + </addColumn> + + <createTable tableName="maintenances"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="start" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="period" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="user_maintenance"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <addForeignKeyConstraint baseTableName="user_maintenance" baseColumnNames="userid" constraintName="fk_user_maintenance_userid" referencedTableName="users" referencedColumnNames="id" onDelete="CASCADE" /> + <addForeignKeyConstraint baseTableName="user_maintenance" baseColumnNames="maintenanceid" constraintName="fk_user_maintenance_maintenanceid" referencedTableName="maintenances" referencedColumnNames="id" onDelete="CASCADE" /> + + <createTable tableName="group_maintenance"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <addForeignKeyConstraint baseTableName="group_maintenance" baseColumnNames="groupid" constraintName="fk_group_maintenance_groupid" referencedTableName="groups" referencedColumnNames="id" onDelete="CASCADE" /> + <addForeignKeyConstraint baseTableName="group_maintenance" baseColumnNames="maintenanceid" constraintName="fk_group_maintenance_maintenanceid" referencedTableName="maintenances" referencedColumnNames="id" onDelete="CASCADE" /> + + <createTable tableName="device_maintenance"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <addForeignKeyConstraint baseTableName="device_maintenance" baseColumnNames="deviceid" constraintName="fk_device_maintenance_deviceid" referencedTableName="devices" referencedColumnNames="id" onDelete="CASCADE" /> + <addForeignKeyConstraint baseTableName="device_maintenance" baseColumnNames="maintenanceid" constraintName="fk_device_maintenance_maintenanceid" referencedTableName="maintenances" referencedColumnNames="id" onDelete="CASCADE" /> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-3.3.xml b/schema/changelog-3.3.xml index 6ed8cecaa..4a34c6626 100644 --- a/schema/changelog-3.3.xml +++ b/schema/changelog-3.3.xml @@ -10,7 +10,7 @@ <preConditions onFail="MARK_RAN"> <not> - <tableExists tableName="server" /> + <tableExists tableName="tc_servers" /> </not> </preConditions> @@ -33,7 +33,7 @@ <column name="readonly" type="BOOLEAN" defaultValueBoolean="false"> <constraints nullable="false" /> </column> - <column name="admin" type="BOOLEAN" defaultValueBoolean="false"> + <column name="administrator" type="BOOLEAN" defaultValueBoolean="false"> <constraints nullable="false" /> </column> <column name="map" type="VARCHAR(128)" defaultValue="osm"> @@ -181,8 +181,9 @@ <column name="email" value="admin" /> <column name="hashedpassword" value="D33DCA55ABD4CC5BC76F2BC0B4E603FE2C6F61F4C1EF2D47" /> <column name="salt" value="000000000000000000000000000000000000000000000000" /> - <column name="admin" valueBoolean="true" /> + <column name="administrator" valueBoolean="true" /> </insert> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.5.xml b/schema/changelog-3.5.xml index 934f389b2..7adfc9f2f 100644 --- a/schema/changelog-3.5.xml +++ b/schema/changelog-3.5.xml @@ -10,7 +10,7 @@ <preConditions onFail="MARK_RAN"> <not> - <tableExists tableName="groups" /> + <tableExists tableName="tc_servers" /> </not> </preConditions> @@ -65,4 +65,5 @@ </addColumn> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.6.xml b/schema/changelog-3.6.xml index 2e7cd61c8..27701640f 100644 --- a/schema/changelog-3.6.xml +++ b/schema/changelog-3.6.xml @@ -10,7 +10,7 @@ <preConditions onFail="MARK_RAN"> <not> - <tableExists tableName="events" /> + <tableExists tableName="tc_servers" /> </not> </preConditions> @@ -112,4 +112,5 @@ </addColumn> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.7.xml b/schema/changelog-3.7.xml index 1bae2aaff..427497f43 100644 --- a/schema/changelog-3.7.xml +++ b/schema/changelog-3.7.xml @@ -8,15 +8,21 @@ <changeSet author="author" id="changelog-3.7"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <update tableName="devices"> - <column name="groupid"/> + <column name="groupid" /> <where>groupid NOT IN (SELECT id FROM groups)</where> </update> - <addForeignKeyConstraint baseColumnNames="groupid" baseTableName="devices" constraintName="fk_device_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups"/> + <addForeignKeyConstraint baseColumnNames="groupid" baseTableName="devices" constraintName="fk_device_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups" /> <update tableName="groups"> - <column name="groupid"/> + <column name="groupid" /> <where>groupid NOT IN (SELECT id FROM (SELECT DISTINCT id FROM groups) AS groups_ids)</where> </update> @@ -42,10 +48,15 @@ <preConditions onFail="MARK_RAN"> <not> + <tableExists tableName="tc_servers" /> + </not> + <not> <dbms type="mssql" /> </not> </preConditions> - <addForeignKeyConstraint baseColumnNames="groupid" baseTableName="groups" constraintName="fk_group_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups"/> + + <addForeignKeyConstraint baseColumnNames="groupid" baseTableName="groups" constraintName="fk_group_group_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="groups" /> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-3.8.xml b/schema/changelog-3.8.xml index 120ddf5cc..aeed32888 100644 --- a/schema/changelog-3.8.xml +++ b/schema/changelog-3.8.xml @@ -8,6 +8,12 @@ <changeSet author="author" id="changelog-3.8"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <createTable tableName="attribute_aliases"> <column name="id" type="INT" autoIncrement="true"> <constraints primaryKey="true" /> @@ -27,15 +33,15 @@ <addUniqueConstraint tableName="attribute_aliases" columnNames="deviceid, attribute" constraintName="uk_deviceid_attribute" /> <update tableName="users"> - <column name="map" type="VARCHAR(128)"/> + <column name="map" type="VARCHAR(128)" /> <where>map = 'osm'</where> </update> <update tableName="users"> - <column name="distanceunit" type="VARCHAR(128)"/> + <column name="distanceunit" type="VARCHAR(128)" /> <where>distanceunit = 'km'</where> </update> <update tableName="users"> - <column name="speedunit" type="VARCHAR(128)"/> + <column name="speedunit" type="VARCHAR(128)" /> <where>speedunit = 'kmh'</where> </update> @@ -122,9 +128,4 @@ </changeSet> - <changeSet author="author" id="changelog-3.8-dropuniquetoken" failOnError="false"> - - <dropUniqueConstraint tableName="users" uniqueColumns="token" constraintName="uk_user_token" /> - - </changeSet> </databaseChangeLog> diff --git a/schema/changelog-3.9.xml b/schema/changelog-3.9.xml index 93fa6123b..9a797ff7d 100644 --- a/schema/changelog-3.9.xml +++ b/schema/changelog-3.9.xml @@ -8,30 +8,37 @@ <changeSet author="author" id="changelog-3.9"> + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + <addColumn tableName="notifications"> <column name="web" type="BOOLEAN" defaultValueBoolean="false" /> <column name="mail" type="BOOLEAN" defaultValueBoolean="false" /> </addColumn> <update tableName="notifications"> - <column name="web" valueBoolean="true" /> - <where>attributes = '{"web":"true"}'</where> + <column name="web" valueBoolean="true" /> + <where>attributes = '{"web":"true"}'</where> </update> <update tableName="notifications"> - <column name="mail" valueBoolean="true" /> + <column name="mail" valueBoolean="true" /> <where>attributes = '{"mail":"true"}'</where> </update> <update tableName="notifications"> - <column name="web" valueBoolean="true" /> - <column name="mail" valueBoolean="true" /> - <where>attributes = '{"web":"true","mail":"true"}'</where> + <column name="web" valueBoolean="true" /> + <column name="mail" valueBoolean="true" /> + <where>attributes = '{"web":"true","mail":"true"}'</where> </update> <update tableName="notifications"> - <column name="attributes" value="{}" /> + <column name="attributes" value="{}" /> </update> </changeSet> + </databaseChangeLog> diff --git a/schema/changelog-4.0-clean.xml b/schema/changelog-4.0-clean.xml new file mode 100644 index 000000000..f3f814eba --- /dev/null +++ b/schema/changelog-4.0-clean.xml @@ -0,0 +1,649 @@ +<?xml version="1.1" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd" + logicalFilePath="changelog-4.0-clean"> + + <changeSet author="author" id="changelog-4.0-clean"> + + <preConditions onFail="MARK_RAN"> + <not> + <changeSetExecuted changeLogFile="changelog-3.3" id="changelog-3.3" author="author" /> + </not> + </preConditions> + + <createTable tableName="tc_attributes"> + <column autoIncrement="true" name="id" type="INT"> + <constraints primaryKey="true" /> + </column> + <column name="description" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="attribute" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="expression" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_calendars"> + <column autoIncrement="true" name="id" type="INT"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="data" type="MEDIUMBLOB"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_commands"> + <column autoIncrement="true" name="id" type="INT"> + <constraints primaryKey="true" /> + </column> + <column name="description" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="textchannel" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_attribute"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="attributeid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_command"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="commandid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_driver"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="driverid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_geofence"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="geofenceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_maintenance"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_device_notification"> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="notificationid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_devices"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="uniqueid" type="VARCHAR(128)"> + <constraints nullable="false" unique="true" /> + </column> + <column name="lastupdate" type="timestamp" /> + <column name="positionid" type="INT" /> + <column name="groupid" type="INT" /> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="phone" type="VARCHAR(128)" /> + <column name="model" type="VARCHAR(128)" /> + <column name="contact" type="VARCHAR(512)" /> + <column name="category" type="VARCHAR(128)" /> + <column name="disabled" type="BOOLEAN" defaultValueBoolean="false" /> + </createTable> + + <createTable tableName="tc_drivers"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="uniqueid" type="VARCHAR(128)"> + <constraints nullable="false" unique="true" /> + </column> + <column name="attributes" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_events"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="servertime" type="TIMESTAMP"> + <constraints nullable="false" /> + </column> + <column name="deviceid" type="INT" /> + <column name="positionid" type="INT" /> + <column name="geofenceid" type="INT" /> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="maintenanceid" type="INT" /> + </createTable> + + <createTable tableName="tc_geofences"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="description" type="VARCHAR(128)" /> + <column name="area" type="VARCHAR(4096)"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="calendarid" type="INT" /> + </createTable> + + <createTable tableName="tc_group_attribute"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="attributeid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_group_command"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="commandid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_group_driver"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="driverid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_group_geofence"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="geofenceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_group_maintenance"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_group_notification"> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="notificationid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_groups"> + <column autoIncrement="true" name="id" type="INT"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="groupid" type="INT" /> + <column name="attributes" type="VARCHAR(4000)" /> + </createTable> + + <createTable tableName="tc_maintenances"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="start" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="period" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_notifications"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="type" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="always" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="calendarid" type="INT" /> + <column name="notificators" type="VARCHAR(128)" /> + </createTable> + + <createTable tableName="tc_positions"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="protocol" type="VARCHAR(128)" /> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="servertime" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP"> + <constraints nullable="false" /> + </column> + <column name="devicetime" type="TIMESTAMP"> + <constraints nullable="false" /> + </column> + <column name="fixtime" type="TIMESTAMP"> + <constraints nullable="false" /> + </column> + <column name="valid" type="BOOLEAN"> + <constraints nullable="false" /> + </column> + <column name="latitude" type="DOUBLE"> + <constraints nullable="false" /> + </column> + <column name="longitude" type="DOUBLE"> + <constraints nullable="false" /> + </column> + <column name="altitude" type="FLOAT"> + <constraints nullable="false" /> + </column> + <column name="speed" type="FLOAT"> + <constraints nullable="false" /> + </column> + <column name="course" type="FLOAT"> + <constraints nullable="false" /> + </column> + <column name="address" type="VARCHAR(512)" /> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="accuracy" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="network" type="VARCHAR(4000)" /> + </createTable> + + <createTable tableName="tc_servers"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="registration" type="BOOLEAN" defaultValueBoolean="true"> + <constraints nullable="false" /> + </column> + <column name="latitude" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="longitude" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="zoom" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="map" type="VARCHAR(128)" /> + <column name="bingkey" type="VARCHAR(128)" /> + <column name="mapurl" type="VARCHAR(512)" /> + <column name="readonly" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="twelvehourformat" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="forcesettings" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="coordinateformat" type="VARCHAR(128)" /> + <column name="devicereadonly" type="BOOLEAN" defaultValueBoolean="false" /> + <column name="limitcommands" type="BOOLEAN" defaultValueBoolean="false" /> + <column name="poilayer" type="VARCHAR(512)" /> + </createTable> + + <createTable tableName="tc_statistics"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="capturetime" type="TIMESTAMP"> + <constraints nullable="false" /> + </column> + <column name="activeusers" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="activedevices" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="requests" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="messagesreceived" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="messagesstored" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4096)"> + <constraints nullable="false" /> + </column> + <column name="mailsent" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="smssent" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="geocoderrequests" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="geolocationrequests" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_attribute"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="attributeid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_calendar"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="calendarid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_command"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="commandid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_device"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="deviceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_driver"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="driverid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_geofence"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="geofenceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_group"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="groupid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_maintenance"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="maintenanceid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_notification"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="notificationid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_user_user"> + <column name="userid" type="INT"> + <constraints nullable="false" /> + </column> + <column name="manageduserid" type="INT"> + <constraints nullable="false" /> + </column> + </createTable> + + <createTable tableName="tc_users"> + <column name="id" type="INT" autoIncrement="true"> + <constraints primaryKey="true" /> + </column> + <column name="name" type="VARCHAR(128)"> + <constraints nullable="false" /> + </column> + <column name="email" type="VARCHAR(128)"> + <constraints nullable="false" unique="true" /> + </column> + <column name="hashedpassword" type="VARCHAR(128)" /> + <column name="salt" type="VARCHAR(128)" /> + <column name="readonly" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="administrator" type="BOOLEAN" /> + <column name="map" type="VARCHAR(128)" /> + <column name="latitude" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="longitude" type="DOUBLE" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="zoom" type="INT" defaultValueNumeric="0"> + <constraints nullable="false" /> + </column> + <column name="twelvehourformat" type="BOOLEAN" defaultValueBoolean="false"> + <constraints nullable="false" /> + </column> + <column name="attributes" type="VARCHAR(4000)" /> + <column name="coordinateformat" type="VARCHAR(128)" /> + <column name="disabled" type="BOOLEAN" defaultValueBoolean="false" /> + <column name="expirationtime" type="timestamp" /> + <column name="devicelimit" type="INT" defaultValueNumeric="-1" /> + <column name="token" type="VARCHAR(128)" /> + <column name="userlimit" type="INT" defaultValueNumeric="0" /> + <column name="devicereadonly" type="BOOLEAN" defaultValueBoolean="false" /> + <column name="phone" type="VARCHAR(128)" /> + <column name="limitcommands" type="BOOLEAN" defaultValueBoolean="false" /> + <column name="login" type="VARCHAR(128)" /> + <column name="poilayer" type="VARCHAR(512)" /> + </createTable> + + <addForeignKeyConstraint baseTableName="tc_device_command" baseColumnNames="commandid" constraintName="fk_device_command_commandid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_commands" /> + <addForeignKeyConstraint baseTableName="tc_device_command" baseColumnNames="deviceid" constraintName="fk_device_command_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + + <addForeignKeyConstraint baseTableName="tc_device_driver" baseColumnNames="deviceid" constraintName="fk_device_driver_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + <addForeignKeyConstraint baseTableName="tc_device_driver" baseColumnNames="driverid" constraintName="fk_device_driver_driverid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_drivers" /> + + <addForeignKeyConstraint baseTableName="tc_device_geofence" baseColumnNames="deviceid" constraintName="fk_device_geofence_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + <addForeignKeyConstraint baseTableName="tc_device_geofence" baseColumnNames="geofenceid" constraintName="fk_device_geofence_geofenceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_geofences" /> + + <addForeignKeyConstraint baseTableName="tc_device_maintenance" baseColumnNames="deviceid" constraintName="fk_device_maintenance_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + <addForeignKeyConstraint baseTableName="tc_device_maintenance" baseColumnNames="maintenanceid" constraintName="fk_device_maintenance_maintenanceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_maintenances" /> + + <addForeignKeyConstraint baseTableName="tc_device_notification" baseColumnNames="deviceid" constraintName="fk_device_notification_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + <addForeignKeyConstraint baseTableName="tc_device_notification" baseColumnNames="notificationid" constraintName="fk_device_notification_notificationid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_notifications" /> + + <addForeignKeyConstraint baseTableName="tc_devices" baseColumnNames="groupid" constraintName="fk_devices_groupid" onDelete="SET NULL" referencedColumnNames="id" referencedTableName="tc_groups" /> + + <addForeignKeyConstraint baseTableName="tc_events" baseColumnNames="deviceid" constraintName="fk_events_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + + <addForeignKeyConstraint baseTableName="tc_geofences" baseColumnNames="calendarid" constraintName="fk_geofence_calendar_calendarid" onDelete="SET NULL" referencedColumnNames="id" referencedTableName="tc_calendars" /> + + <addForeignKeyConstraint baseTableName="tc_group_attribute" baseColumnNames="attributeid" constraintName="fk_group_attribute_attributeid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_attributes" /> + <addForeignKeyConstraint baseTableName="tc_group_attribute" baseColumnNames="groupid" constraintName="fk_group_attribute_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + + <addForeignKeyConstraint baseTableName="tc_group_command" baseColumnNames="commandid" constraintName="fk_group_command_commandid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_commands" /> + <addForeignKeyConstraint baseTableName="tc_group_command" baseColumnNames="groupid" constraintName="fk_group_command_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + + <addForeignKeyConstraint baseTableName="tc_group_driver" baseColumnNames="driverid" constraintName="fk_group_driver_driverid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_drivers" /> + <addForeignKeyConstraint baseTableName="tc_group_driver" baseColumnNames="groupid" constraintName="fk_group_driver_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + + <addForeignKeyConstraint baseTableName="tc_group_geofence" baseColumnNames="geofenceid" constraintName="fk_group_geofence_geofenceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_geofences" /> + <addForeignKeyConstraint baseTableName="tc_group_geofence" baseColumnNames="groupid" constraintName="fk_group_geofence_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + + <addForeignKeyConstraint baseTableName="tc_group_maintenance" baseColumnNames="groupid" constraintName="fk_group_maintenance_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + <addForeignKeyConstraint baseTableName="tc_group_maintenance" baseColumnNames="maintenanceid" constraintName="fk_group_maintenance_maintenanceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_maintenances" /> + + <addForeignKeyConstraint baseTableName="tc_group_notification" baseColumnNames="groupid" constraintName="fk_group_notification_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + <addForeignKeyConstraint baseTableName="tc_group_notification" baseColumnNames="notificationid" constraintName="fk_group_notification_notificationid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_notifications" /> + + <addForeignKeyConstraint baseTableName="tc_notifications" baseColumnNames="calendarid" constraintName="fk_notification_calendar_calendarid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="tc_calendars" /> + + <addForeignKeyConstraint baseTableName="tc_positions" baseColumnNames="deviceid" constraintName="fk_positions_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + + <addForeignKeyConstraint baseTableName="tc_user_attribute" baseColumnNames="attributeid" constraintName="fk_user_attribute_attributeid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_attributes" /> + <addForeignKeyConstraint baseTableName="tc_user_attribute" baseColumnNames="userid" constraintName="fk_user_attribute_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_calendar" baseColumnNames="calendarid" constraintName="fk_user_calendar_calendarid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_calendars" /> + <addForeignKeyConstraint baseTableName="tc_user_calendar" baseColumnNames="userid" constraintName="fk_user_calendar_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_command" baseColumnNames="commandid" constraintName="fk_user_command_commandid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_commands" /> + <addForeignKeyConstraint baseTableName="tc_user_command" baseColumnNames="userid" constraintName="fk_user_command_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_device_attribute" baseColumnNames="attributeid" constraintName="fk_user_device_attribute_attributeid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_attributes" /> + <addForeignKeyConstraint baseTableName="tc_device_attribute" baseColumnNames="deviceid" constraintName="fk_user_device_attribute_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + + <addForeignKeyConstraint baseTableName="tc_user_device" baseColumnNames="deviceid" constraintName="fk_user_device_deviceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_devices" /> + <addForeignKeyConstraint baseTableName="tc_user_device" baseColumnNames="userid" constraintName="fk_user_device_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_driver" baseColumnNames="driverid" constraintName="fk_user_driver_driverid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_drivers" /> + <addForeignKeyConstraint baseTableName="tc_user_driver" baseColumnNames="userid" constraintName="fk_user_driver_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_geofence" baseColumnNames="geofenceid" constraintName="fk_user_geofence_geofenceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_geofences" /> + <addForeignKeyConstraint baseTableName="tc_user_geofence" baseColumnNames="userid" constraintName="fk_user_geofence_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_group" baseColumnNames="groupid" constraintName="fk_user_group_groupid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_groups" /> + <addForeignKeyConstraint baseTableName="tc_user_group" baseColumnNames="userid" constraintName="fk_user_group_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_maintenance" baseColumnNames="maintenanceid" constraintName="fk_user_maintenance_maintenanceid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_maintenances" /> + <addForeignKeyConstraint baseTableName="tc_user_maintenance" baseColumnNames="userid" constraintName="fk_user_maintenance_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_notification" baseColumnNames="notificationid" constraintName="fk_user_notification_notificationid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_notifications" /> + <addForeignKeyConstraint baseTableName="tc_user_notification" baseColumnNames="userid" constraintName="fk_user_notification_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <addForeignKeyConstraint baseTableName="tc_user_user" baseColumnNames="userid" constraintName="fk_user_user_userid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + <insert tableName="tc_servers"> + <column name="registration" valueBoolean="true" /> + <column name="latitude" valueNumeric="0" /> + <column name="longitude" valueNumeric="0" /> + <column name="zoom" valueNumeric="0" /> + </insert> + + <insert tableName="tc_users"> + <column name="name" value="admin" /> + <column name="email" value="admin" /> + <column name="hashedpassword" value="D33DCA55ABD4CC5BC76F2BC0B4E603FE2C6F61F4C1EF2D47" /> + <column name="salt" value="000000000000000000000000000000000000000000000000" /> + <column name="administrator" valueBoolean="true" /> + </insert> + + </changeSet> + + <changeSet author="author" id="changelog-4.0-clean-common"> + + <preConditions onFail="MARK_RAN"> + <not> + <changeSetExecuted changeLogFile="changelog-3.3" id="changelog-3.3" author="author" /> + </not> + <not> + <dbms type="mssql" /> + </not> + </preConditions> + + <addForeignKeyConstraint baseTableName="tc_groups" baseColumnNames="groupid" constraintName="fk_groups_groupid" onDelete="SET NULL" onUpdate="RESTRICT" referencedColumnNames="id" referencedTableName="tc_groups" /> + <addForeignKeyConstraint baseTableName="tc_user_user" baseColumnNames="manageduserid" constraintName="fk_user_user_manageduserid" onDelete="CASCADE" referencedColumnNames="id" referencedTableName="tc_users" /> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-4.0.xml b/schema/changelog-4.0.xml new file mode 100644 index 000000000..8d0da6284 --- /dev/null +++ b/schema/changelog-4.0.xml @@ -0,0 +1,192 @@ +<?xml version="1.0" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd" + logicalFilePath="changelog-4.0"> + + <changeSet author="author" id="changelog-4.0-pre"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + <not> + <columnExists tableName="notifications" columnName="notificators" /> + </not> + </preConditions> + + <addColumn tableName="notifications"> + <column name="notificators" type="VARCHAR(128)" /> + </addColumn> + + </changeSet> + + <changeSet author="author" id="changelog-4.0-common"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + <not> + <dbms type="postgresql" /> + </not> + <columnExists tableName="notifications" columnName="web" /> + <columnExists tableName="notifications" columnName="mail" /> + <columnExists tableName="notifications" columnName="sms" /> + </preConditions> + + <update tableName="notifications"> + <column name="notificators" value="web,mail,sms" /> + <where>web = 1 AND mail = 1 AND sms = 1</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web,mail" /> + <where>web = 1 AND mail = 1 AND sms = 0</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web" /> + <where>web = 1 AND mail = 0 AND sms = 0</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web,sms" /> + <where>web = 1 AND mail = 0 AND sms = 1</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="mail,sms" /> + <where>web = 0 AND mail = 1 AND sms = 1</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="mail" /> + <where>web = 0 AND mail = 1 AND sms = 0</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="sms" /> + <where>web = 0 AND mail = 0 AND sms = 1</where> + </update> + + </changeSet> + + <changeSet author="author" id="changelog-4.0-pg"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + <dbms type="postgresql" /> + <columnExists tableName="tc_notifications" columnName="web" /> + <columnExists tableName="tc_notifications" columnName="mail" /> + <columnExists tableName="tc_notifications" columnName="sms" /> + </preConditions> + + <update tableName="notifications"> + <column name="notificators" value="web,mail,sms" /> + <where>web = TRUE AND mail = TRUE AND sms = TRUE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web,mail" /> + <where>web = TRUE AND mail = TRUE AND sms = FALSE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web" /> + <where>web = TRUE AND mail = FALSE AND sms = FALSE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="web,sms" /> + <where>web = TRUE AND mail = FALSE AND sms = TRUE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="mail,sms" /> + <where>web = FALSE AND mail = TRUE AND sms = TRUE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="mail" /> + <where>web = FALSE AND mail = TRUE AND sms = FALSE</where> + </update> + + <update tableName="notifications"> + <column name="notificators" value="sms" /> + <where>web = FALSE AND mail = FALSE AND sms = TRUE</where> + </update> + + </changeSet> + + <changeSet author="author" id="changelog-4.0"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + + <dropDefaultValue tableName="notifications" columnName="web" /> + <dropColumn tableName="notifications" columnName="web" /> + + <dropDefaultValue tableName="notifications" columnName="mail" /> + <dropColumn tableName="notifications" columnName="mail" /> + + <dropDefaultValue tableName="notifications" columnName="sms" /> + <dropColumn tableName="notifications" columnName="sms" /> + + </changeSet> + + <changeSet author="author" id="changelog-4.0-renaming"> + + <preConditions onFail="MARK_RAN"> + <not> + <tableExists tableName="tc_servers" /> + </not> + </preConditions> + + <renameTable oldTableName="attributes" newTableName="tc_attributes" /> + <renameTable oldTableName="calendars" newTableName="tc_calendars" /> + <renameTable oldTableName="commands" newTableName="tc_commands" /> + <renameTable oldTableName="device_attribute" newTableName="tc_device_attribute" /> + <renameTable oldTableName="device_command" newTableName="tc_device_command" /> + <renameTable oldTableName="device_driver" newTableName="tc_device_driver" /> + <renameTable oldTableName="device_geofence" newTableName="tc_device_geofence" /> + <renameTable oldTableName="device_maintenance" newTableName="tc_device_maintenance" /> + <renameTable oldTableName="device_notification" newTableName="tc_device_notification" /> + <renameTable oldTableName="devices" newTableName="tc_devices" /> + <renameTable oldTableName="drivers" newTableName="tc_drivers" /> + <renameTable oldTableName="events" newTableName="tc_events" /> + <renameTable oldTableName="geofences" newTableName="tc_geofences" /> + <renameTable oldTableName="group_attribute" newTableName="tc_group_attribute" /> + <renameTable oldTableName="group_command" newTableName="tc_group_command" /> + <renameTable oldTableName="group_driver" newTableName="tc_group_driver" /> + <renameTable oldTableName="group_geofence" newTableName="tc_group_geofence" /> + <renameTable oldTableName="group_maintenance" newTableName="tc_group_maintenance" /> + <renameTable oldTableName="group_notification" newTableName="tc_group_notification" /> + <renameTable oldTableName="groups" newTableName="tc_groups" /> + <renameTable oldTableName="maintenances" newTableName="tc_maintenances" /> + <renameTable oldTableName="notifications" newTableName="tc_notifications" /> + <renameTable oldTableName="positions" newTableName="tc_positions" /> + <renameTable oldTableName="servers" newTableName="tc_servers" /> + <renameTable oldTableName="statistics" newTableName="tc_statistics" /> + <renameTable oldTableName="user_attribute" newTableName="tc_user_attribute" /> + <renameTable oldTableName="user_calendar" newTableName="tc_user_calendar" /> + <renameTable oldTableName="user_command" newTableName="tc_user_command" /> + <renameTable oldTableName="user_device" newTableName="tc_user_device" /> + <renameTable oldTableName="user_driver" newTableName="tc_user_driver" /> + <renameTable oldTableName="user_geofence" newTableName="tc_user_geofence" /> + <renameTable oldTableName="user_group" newTableName="tc_user_group" /> + <renameTable oldTableName="user_maintenance" newTableName="tc_user_maintenance" /> + <renameTable oldTableName="user_notification" newTableName="tc_user_notification" /> + <renameTable oldTableName="user_user" newTableName="tc_user_user" /> + <renameTable oldTableName="users" newTableName="tc_users" /> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-4.1.xml b/schema/changelog-4.1.xml new file mode 100644 index 000000000..3446fb9f1 --- /dev/null +++ b/schema/changelog-4.1.xml @@ -0,0 +1,47 @@ +<?xml version="1.0" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd" + logicalFilePath="changelog-4.1"> + + <changeSet author="author" id="changelog-4.1-mssql"> + + <preConditions onFail="MARK_RAN"> + <dbms type="mssql" /> + </preConditions> + + <sql> + IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tg_groups_delete') + BEGIN + DROP TRIGGER tg_groups_delete + END; + </sql> + + <sql> + CREATE TRIGGER tg_groups_delete + ON tc_groups FOR DELETE + AS BEGIN + UPDATE tc_groups SET groupid = NULL WHERE groupid IN (SELECT deleted.id FROM deleted) + END + </sql> + + <sql> + IF EXISTS (SELECT * FROM sysobjects WHERE type = 'TR' AND name = 'tg_users_delete') + BEGIN + DROP TRIGGER tg_users_delete + END; + </sql> + + <sql> + CREATE TRIGGER tg_users_delete + ON tc_users FOR DELETE + AS BEGIN + DELETE FROM tc_user_user WHERE manageduserid IN (SELECT deleted.id FROM deleted) + END + </sql> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-4.7.xml b/schema/changelog-4.7.xml new file mode 100644 index 000000000..ca42aa6f9 --- /dev/null +++ b/schema/changelog-4.7.xml @@ -0,0 +1,28 @@ +<?xml version="1.0" encoding="UTF-8"?> +<databaseChangeLog + xmlns="http://www.liquibase.org/xml/ns/dbchangelog" + xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" + xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog + http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd" + logicalFilePath="changelog-4.7"> + + <changeSet author="author" id="changelog-4.7"> + + <preConditions onFail="MARK_RAN"> + <not> + <indexExists indexName="user_device_user_id" /> + </not> + </preConditions> + + <createIndex tableName="tc_user_device" indexName="user_device_user_id"> + <column name="userid" /> + </createIndex> + + <createIndex tableName="tc_positions" indexName="position_deviceid_fixtime"> + <column name="deviceid" /> + <column name="fixtime" /> + </createIndex> + + </changeSet> + +</databaseChangeLog> diff --git a/schema/changelog-master.xml b/schema/changelog-master.xml index 58b2a8307..5d770c4aa 100644 --- a/schema/changelog-master.xml +++ b/schema/changelog-master.xml @@ -5,6 +5,8 @@ xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"> + <include file="changelog-4.0-clean.xml" relativeToChangelogFile="true" /> + <include file="changelog-3.3.xml" relativeToChangelogFile="true" /> <include file="changelog-3.5.xml" relativeToChangelogFile="true" /> <include file="changelog-3.6.xml" relativeToChangelogFile="true" /> @@ -16,4 +18,11 @@ <include file="changelog-3.12.xml" relativeToChangelogFile="true" /> <include file="changelog-3.14.xml" relativeToChangelogFile="true" /> <include file="changelog-3.15.xml" relativeToChangelogFile="true" /> + <include file="changelog-3.16.xml" relativeToChangelogFile="true" /> + <include file="changelog-3.17.xml" relativeToChangelogFile="true" /> + <include file="changelog-4.0.xml" relativeToChangelogFile="true" /> + + <include file="changelog-4.1.xml" relativeToChangelogFile="true" /> + <include file="changelog-4.7.xml" relativeToChangelogFile="true" /> + </databaseChangeLog> |