· 6 years ago · Jun 17, 2019, 03:28 PM
1package com.robustastudio.plugs.room_migrations;
2
3import android.arch.persistence.db.SupportSQLiteDatabase;
4import android.arch.persistence.room.migration.Migration;
5import android.database.Cursor;
6import android.support.annotation.NonNull;
7import android.util.Log;
8
9import com.robustastudio.plugs.data.model.Model;
10import com.robustastudio.plugs.utils.CameraModelUtil;
11
12import java.util.List;
13
14/**
15 * Created by hanaa on 28/02/18.
16 */
17
18public class MigrationToVersion13 extends Migration {
19
20 private String OLD_CAMERA_TABLE_NAME = "CameraTable";
21 private String CAMERA_TABLE_NAME = "camera";
22
23 private String OLD_CAMERA_GROUP_TABLE_NAME = "cameraGroups";
24 private String CAMERA_GROUP_TABLE_NAME = "CamerasGroup";
25
26
27 private final static String MODEL_TABLE_NAME = "cameraModels";
28
29
30 /**
31 * Creates a new migration between {@code startVersion} and {@code endVersion}.
32 *
33 * @param startVersion The start version of the database.
34 * @param endVersion The end version of the database after this migration is applied.
35 */
36 public MigrationToVersion13(int startVersion, int endVersion) {
37 super(startVersion, endVersion);
38 }
39
40
41 @Override
42 public void migrate(@NonNull SupportSQLiteDatabase database) {
43
44 //CHECK WHETHER CAMERA MODEL TABLE EXIST OR NOT
45 if (isTableExists(MODEL_TABLE_NAME, database)) {
46 database.execSQL("DROP TABLE IF EXISTS " + MODEL_TABLE_NAME);
47 createCameraModelsTable(database);
48 } else createCameraModelsTable(database);
49
50
51 if (isTableExists(OLD_CAMERA_TABLE_NAME, database)) {
52 Cursor dbCursor = database.query("select * from " + OLD_CAMERA_TABLE_NAME);
53 int videoColIndex = dbCursor.getColumnIndex("videoExtension");
54 if (videoColIndex == -1) {
55 Log.d("col", "videoExtension not found");
56 cloneCameraTable(database, false);
57// database.delete(CAMERA_TABLE_NAME, null, null);
58// createCameraModelsTable(database);
59 database.execSQL("ALTER TABLE " + CAMERA_TABLE_NAME + " ADD COLUMN videoExtension TEXT");
60 String updateQuery = "UPDATE " + CAMERA_TABLE_NAME
61 + " SET videoExtension = (SELECT videoUrl FROM " + MODEL_TABLE_NAME +
62 " WHERE name = CameraTable.modelName)";
63 database.execSQL(updateQuery);
64
65 } else {
66 //IF THE TABLE EXIST BUT WITH THE OLD DATA STRUCTURE RE CREATE IT AND DROP THE OLD ONE
67 cloneCameraTable(database, true);
68 }
69 } else {
70 createCameraTable(database, true);
71 }
72
73
74 //CHECK WHETHER CAMERA GROUP TABLE EXIST OR NOT
75 if (isTableExists(OLD_CAMERA_GROUP_TABLE_NAME, database)) {
76 cloneCameraGroupTable(database);
77 } else createCameraGroupTable(database);
78 }
79
80
81 private static boolean isTableExists(String tableName, SupportSQLiteDatabase db) {
82 boolean isExists = false;
83 Cursor cursor = db.query("select DISTINCT tbl_name from sqlite_master where tbl_name = '"
84 + tableName + "'", null);
85 if (cursor != null) {
86 if (cursor.getCount() > 0) {
87 isExists = true;
88 }
89 cursor.close();
90 }
91 return isExists;
92 }
93
94 private static void createCameraModelsTable(SupportSQLiteDatabase db) {
95 //CREATE CAMERA MODELS TABLE (Id, name, url)
96 db.execSQL("CREATE TABLE IF NOT EXISTS " + MODEL_TABLE_NAME
97 + " (id INTEGER PRIMARY KEY autoincrement, name TEXT, imageUrl TEXT" +
98 ",videoUrl TEXT" +
99 ")");
100
101 List<Model> modelList = CameraModelUtil.getStaticModels();
102
103 for (Model model : modelList) {
104 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(" + model.getId() +
105 ", '" + model.getName() + "'," +
106 " '" + model.getImageUrl() + "'," +
107 "'" + model.getVideoUrl() + "')");
108 }
109 /* db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(1, 'Cisco WVC 201', '/img/'" +
110 ",'/img/video.mjpeg'" +
111 ")");
112 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(2, 'DCS 930', '/image/jpeg.cgi'" +
113 ",'/video/mjpg.cgi'" +
114 ")");
115 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(3, 'DCS 2103/2130', '/image/jpeg.cgi'" +
116 ",'/video2.mjpg'" +
117 ")");
118 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(4, 'DCS 2121', '/image/jpeg.cgi'" +
119 ",'/video/mjpg.cgi'" +
120 ")");
121 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(5, 'DCS 5020', '/image/jpeg.cgi'" +
122 ",'/video/mjpg.cgi'" +
123 ")");
124 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(6, 'DCS 5211/5222L', '/image/jpeg.cgi'" +
125 ",'/video/mjpg.cgi'" +
126 ")");
127 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(7, 'DCS 6010', '/image/jpeg.cgi'" +
128 ",'/video/mjpg.cgi'" +
129 ")");
130 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(8, 'DCS 6113', '/image/jpeg.cgi'" +
131 ",'/video/mjpg.cgi'" +
132 ")");
133 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(9, 'DCS 7010', '/image/jpeg.cgi'" +
134 ",'/video/mjpg.cgi'" +
135 ")");
136 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(10, 'DCS 7110', '/image/jpeg.cgi'" +
137 ",'/video/mjpg.cgi'" +
138 ")");
139 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(11, 'DCS 4703', '/image/jpeg.cgi'" +
140 ",'/video/mjpg.cgi'" +
141 ")");
142 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(12, 'DCS 4602', '/image/jpeg.cgi'" +
143 ",'/video/mjpg.cgi'" +
144 ")");
145 db.execSQL("INSERT OR IGNORE INTO " + MODEL_TABLE_NAME + " values(13, 'DCS 936', '/image/jpeg.cgi'" +
146 ",'/video/mjpg.cgi'" +
147 ")");
148
149*/
150 }
151
152
153 /**
154 * A method to migrate from sqlite to room since sqlite doesn't support altering any column's type or name
155 * and room doesn't support varchar types so we need a new camera table with the new structure then clone
156 * the old data into the new one.
157 *
158 * @param db database object
159 */
160 private void cloneCameraTable(SupportSQLiteDatabase db, boolean isVideoExtensionExist) {
161 // CREATE A NEW TABLE WITH NEW STRUCTURE REPLACE EACH VARCHAR WITH TEXT
162 createCameraTable(db, isVideoExtensionExist);
163
164
165 db.execSQL("INSERT INTO " + CAMERA_TABLE_NAME + " SELECT * FROM " + OLD_CAMERA_TABLE_NAME + ";");
166
167
168 //DROP THE OLD ONE
169 db.execSQL("DROP TABLE IF EXISTS " + OLD_CAMERA_TABLE_NAME);
170
171
172 }
173
174 /**
175 * A method to migrate from sqlite to room since sqlite doesn't support altering any column's type or name
176 * and room doesn't support varchar types so we need a new camera table with the new structure then clone
177 * the old data into the new one.
178 *
179 * @param db database object
180 */
181 private void cloneCameraGroupTable(SupportSQLiteDatabase db) {
182 // CREATE A NEW TABLE WITH NEW STRUCTURE REPLACE EACH VARCHAR WITH TEXT
183 createCameraGroupTable(db);
184
185 //CLONE OLD CAMERA TABLE INTO THE NEW CAMERA TABLE
186 db.execSQL("INSERT INTO " + CAMERA_GROUP_TABLE_NAME + " SELECT * FROM " + OLD_CAMERA_GROUP_TABLE_NAME + ";");
187
188 //DROP THE OLD ONE
189 db.execSQL("DROP TABLE IF EXISTS " + OLD_CAMERA_GROUP_TABLE_NAME);
190
191
192 }
193
194 private void createCameraTable(SupportSQLiteDatabase database, boolean addVideoExtensionColumn) {
195 String cloningSql = "CREATE TABLE IF NOT EXISTS camera " +
196 "(Id INTEGER PRIMARY KEY autoincrement, Alias TEXT, IP TEXT, Port TEXT, _Username TEXT"
197 + ", _Password TEXT, imageExtension TEXT," +
198 " modelName TEXT,camera_group_id INTEGER," +
199 (addVideoExtensionColumn ? "videoExtension TEXT" : "") +
200 " REFERENCES " + CAMERA_GROUP_TABLE_NAME + "(Id) ON DELETE SET NULL)";
201 database.execSQL(cloningSql);
202
203 }
204
205 private void createCameraGroupTable(SupportSQLiteDatabase database) {
206 database.execSQL("CREATE TABLE IF NOT EXISTS " + CAMERA_GROUP_TABLE_NAME
207 + " (Id INTEGER PRIMARY KEY autoincrement, name TEXT)");
208 }
209
210}