From 5d974ea790397d0646f013e91c8d3b0829ffbd54 Mon Sep 17 00:00:00 2001
From: Anton Tananaev <anton.tananaev@gmail.com>
Date: Fri, 18 Dec 2015 16:32:46 +1300
Subject: Finish Liquibase migration implementation

---
 database/db.changelog-3.0.xml    |  25 ++++++----
 database/db.changelog-3.1.xml    | 102 ++++++++++++++++++++++++++++++++++-----
 database/db.changelog-3.2.xml    |  50 ++++++++++++++-----
 database/db.changelog-3.3.xml    |  30 ++++++++++++
 database/db.changelog-master.xml |   7 +--
 5 files changed, 180 insertions(+), 34 deletions(-)
 create mode 100644 database/db.changelog-3.3.xml

(limited to 'database')

diff --git a/database/db.changelog-3.0.xml b/database/db.changelog-3.0.xml
index bbc52dfeb..4f94494b4 100644
--- a/database/db.changelog-3.0.xml
+++ b/database/db.changelog-3.0.xml
@@ -1,9 +1,9 @@
-<?xml version="1.0" encoding="UTF-8"?> 
-<databaseChangeLog 
-  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" 
-  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
-  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
-                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd"> 
+<?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">
 
   <changeSet author="author" id="changelog-3.0">
 
@@ -44,6 +44,9 @@
       <column name="distanceUnit" type="VARCHAR(1024)" defaultValue="km">
         <constraints nullable="false" />
       </column>
+      <column name="speedUnit" type="VARCHAR(1024)" defaultValue="kmh">
+        <constraints nullable="false" />
+      </column>
       <column name="latitude" type="DOUBLE" defaultValueNumeric="0">
         <constraints nullable="false" />
       </column>
@@ -156,9 +159,6 @@
 
     <addForeignKeyConstraint baseTableName="data" baseColumnNames="deviceId" constraintName="fk_data_deviceId" referencedTableName="device" referencedColumnNames="id" onDelete="CASCADE" />
 
-    <addForeignKeyConstraint baseTableName="device" baseColumnNames="positionId" constraintName="fk_device_positionId" referencedTableName="position" referencedColumnNames="id" />
-    <addForeignKeyConstraint baseTableName="device" baseColumnNames="dataId" constraintName="fk_device_dataId" referencedTableName="data" referencedColumnNames="id" />
-
     <createTable tableName="server">
       <column name="id" type="INT" autoIncrement="true">
         <constraints primaryKey="true" />
@@ -183,5 +183,12 @@
       </column>
     </createTable>
 
+    <insert tableName="server">
+      <column name="registration" valueBoolean="true" />
+      <column name="latitude" valueNumeric="0" />
+      <column name="longitude" valueNumeric="0" />
+      <column name="zoom" valueNumeric="0" />
+    </insert>
+
   </changeSet>
 </databaseChangeLog>
diff --git a/database/db.changelog-3.1.xml b/database/db.changelog-3.1.xml
index b8c706806..589356863 100644
--- a/database/db.changelog-3.1.xml
+++ b/database/db.changelog-3.1.xml
@@ -1,17 +1,97 @@
-<?xml version="1.0" encoding="UTF-8"?> 
-<databaseChangeLog 
-  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" 
-  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
-  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
-                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
+<?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">
 
   <changeSet author="author" id="changelog-3.1">
 
-    <createTable tableName="TablesAndTables">
-      <column name="COLUMN1" type="TEXT">
-        <constraints nullable="true" primaryKey="false" unique="false"/>
-      </column>
-    </createTable>
+    <preConditions onFail="MARK_RAN">
+      <not>
+        <columnExists tableName="traccar" columnName="version" />
+      </not>
+    </preConditions>
+
+    <modifyDataType tableName="user" columnName="name" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="email" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="hashedPassword" newDataType="VARCHAR(128)" />
+    <dropDefaultValue tableName="user" columnName="salt" />
+    <modifyDataType tableName="user" columnName="salt" newDataType="VARCHAR(128)" />
+    <addDefaultValue tableName="user" columnName="salt" defaultValue="" />
+    <dropDefaultValue tableName="user" columnName="map" />
+    <dropNotNullConstraint tableName="user" columnName="map" columnDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="map" newDataType="VARCHAR(128)" />
+    <dropDefaultValue tableName="user" columnName="language" />
+    <dropNotNullConstraint tableName="user" columnName="language" columnDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="language" newDataType="VARCHAR(128)" />
+    <dropDefaultValue tableName="user" columnName="distanceUnit" />
+    <dropNotNullConstraint tableName="user" columnName="distanceUnit" columnDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="distanceUnit" newDataType="VARCHAR(128)" />
+    <dropDefaultValue tableName="user" columnName="speedUnit" />
+    <dropNotNullConstraint tableName="user" columnName="speedUnit" columnDataType="VARCHAR(128)" />
+    <modifyDataType tableName="user" columnName="speedUnit" newDataType="VARCHAR(128)" />
+    <dropDefaultValue tableName="user" columnName="latitude" />
+    <modifyDataType tableName="user" columnName="latitude" newDataType="FLOAT" />
+    <addDefaultValue tableName="user" columnName="latitude" defaultValueNumeric="0" />
+    <dropDefaultValue tableName="user" columnName="longitude" />
+    <modifyDataType tableName="user" columnName="longitude" newDataType="FLOAT" />
+    <addDefaultValue tableName="user" columnName="longitude" defaultValueNumeric="0" />
+
+    <modifyDataType tableName="device" columnName="name" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="device" columnName="uniqueId" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="device" columnName="status" newDataType="VARCHAR(128)" />
+      
+    <dropIndex tableName="user_device" indexName="user_device_user_id" />
+    <createIndex tableName="user_device" indexName="user_device_userId">
+      <column name="userId" />
+    </createIndex>
+
+    <modifyDataType tableName="position" columnName="protocol" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="position" columnName="latitude" newDataType="FLOAT" />
+    <modifyDataType tableName="position" columnName="longitude" newDataType="FLOAT" />
+    <modifyDataType tableName="position" columnName="altitude" newDataType="FLOAT" />
+    <modifyDataType tableName="position" columnName="speed" newDataType="FLOAT" />
+    <modifyDataType tableName="position" columnName="course" newDataType="FLOAT" />
+    <modifyDataType tableName="position" columnName="address" newDataType="VARCHAR(512)" />
+    <modifyDataType tableName="position" columnName="other" newDataType="VARCHAR(4096)" />
+
+    <createIndex tableName="position" indexName="position_deviceId_fixTime">
+      <column name="deviceId" />
+      <column name="fixTime" />
+    </createIndex>
+
+    <modifyDataType tableName="data" columnName="protocol" newDataType="VARCHAR(128)" />
+    <modifyDataType tableName="data" columnName="other" newDataType="VARCHAR(4096)" />
+
+    <addColumn tableName="server">
+      <column name="map" type="VARCHAR(128)" />
+    </addColumn>
+    <addColumn tableName="server">
+      <column name="language" type="VARCHAR(128)" />
+    </addColumn>
+    <addColumn tableName="server">
+      <column name="distanceUnit" type="VARCHAR(128)" />
+    </addColumn>
+    <addColumn tableName="server">
+      <column name="speedUnit" type="VARCHAR(128)" />
+    </addColumn>
+    <modifyDataType tableName="server" columnName="latitude" newDataType="FLOAT" />
+    <addDefaultValue tableName="server" columnName="latitude" defaultValueNumeric="0" />
+    <modifyDataType tableName="server" columnName="longitude" newDataType="FLOAT" />
+    <addDefaultValue tableName="server" columnName="longitude" defaultValueNumeric="0" />
+
+    <dropPrimaryKey tableName="traccar" />
+    <addColumn tableName="traccar">
+        <column name="version" type="INT" defaultValueNumeric="0">
+          <constraints nullable="false" />
+        </column>
+    </addColumn>
+    <dropColumn tableName="traccar" columnName="id" />
+
+    <insert tableName="traccar">
+      <column name="version" valueNumeric="301" />
+    </insert>
 
   </changeSet>
 </databaseChangeLog>
diff --git a/database/db.changelog-3.2.xml b/database/db.changelog-3.2.xml
index a072c0703..576713dc7 100644
--- a/database/db.changelog-3.2.xml
+++ b/database/db.changelog-3.2.xml
@@ -1,17 +1,45 @@
-<?xml version="1.0" encoding="UTF-8"?> 
-<databaseChangeLog 
-  xmlns="http://www.liquibase.org/xml/ns/dbchangelog/1.9" 
-  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
-  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9
-                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">
+<?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">
 
   <changeSet author="author" id="changelog-3.2">
 
-    <createTable tableName="TablesAndTables">
-      <column name="COLUMN1" type="TEXT">
-        <constraints nullable="true" primaryKey="false" unique="false"/>
-      </column>
-    </createTable>
+    <preConditions onFail="MARK_RAN">
+      <not>
+        <columnExists tableName="position" columnName="attributes" />
+      </not>
+    </preConditions>
+
+    <dropDefaultValue tableName="user" columnName="latitude" />
+    <modifyDataType tableName="user" columnName="latitude" newDataType="DOUBLE" />
+    <addDefaultValue tableName="user" columnName="latitude" defaultValueNumeric="0" />
+    <dropDefaultValue tableName="user" columnName="longitude" />
+    <modifyDataType tableName="user" columnName="longitude" newDataType="DOUBLE" />
+    <addDefaultValue tableName="user" columnName="longitude" defaultValueNumeric="0" />
+
+    <dropColumn tableName="device" columnName="dataId" />
+
+    <modifyDataType tableName="position" columnName="latitude" newDataType="DOUBLE" />
+    <modifyDataType tableName="position" columnName="longitude" newDataType="DOUBLE" />
+    <renameColumn tableName="position" oldColumnName="other" newColumnName="attributes" />
+
+    <dropTable tableName="data" />
+
+    <addColumn tableName="server">
+      <column name="bingKey" type="VARCHAR(128)" />
+    </addColumn>
+    <addColumn tableName="server">
+      <column name="mapUrl" type="VARCHAR(128)" />
+    </addColumn>
+    <modifyDataType tableName="server" columnName="latitude" newDataType="DOUBLE" />
+    <modifyDataType tableName="server" columnName="longitude" newDataType="DOUBLE" />
+
+    <update tableName="traccar">
+      <column name="version" valueNumeric="302" />
+    </update>
 
   </changeSet>
 </databaseChangeLog>
diff --git a/database/db.changelog-3.3.xml b/database/db.changelog-3.3.xml
new file mode 100644
index 000000000..cb9823ef8
--- /dev/null
+++ b/database/db.changelog-3.3.xml
@@ -0,0 +1,30 @@
+<?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">
+
+  <changeSet author="author" id="changelog-3.3">
+
+    <dropDefaultValue tableName="user" columnName="salt" />
+
+    <addDefaultValue tableName="server" columnName="registration" defaultValueBoolean="true" />
+
+    <addColumn tableName="server">
+      <column name="readonly" type="BOOLEAN" defaultValueBoolean="false">
+        <constraints nullable="false" />
+      </column>
+    </addColumn>
+
+    <dropTable tableName="traccar" />
+
+    <renameTable oldTableName="user" newTableName="users" />
+    <renameTable oldTableName="device" newTableName="devices" />
+    <renameTable oldTableName="position" newTableName="positions" />
+
+    <dropColumn tableName="user_device" columnName="read" />
+    <dropColumn tableName="user_device" columnName="write" />
+
+  </changeSet>
+</databaseChangeLog>
diff --git a/database/db.changelog-master.xml b/database/db.changelog-master.xml
index 151cb5d19..c0dd947b1 100644
--- a/database/db.changelog-master.xml
+++ b/database/db.changelog-master.xml
@@ -3,9 +3,10 @@
   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.1.xsd">
+                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd">
 
   <include file="db.changelog-3.0.xml" relativeToChangelogFile="true" />
-  <!--<include file="db.changelog-3.1.xml" relativeToChangelogFile="true" />
-  <include file="db.changelog-3.2.xml" relativeToChangelogFile="true" />-->
+  <include file="db.changelog-3.1.xml" relativeToChangelogFile="true" />
+  <include file="db.changelog-3.2.xml" relativeToChangelogFile="true" />
+  <include file="db.changelog-3.3.xml" relativeToChangelogFile="true" />
 </databaseChangeLog>
-- 
cgit v1.2.3