· 8 years ago · May 07, 2018, 07:26 PM
1
2package cz.eman.android.hosl.db;
3
4import cz.eman.android.hosl.R;
5
6import android.content.ContentValues;
7import android.content.Context;
8import android.database.Cursor;
9import android.database.DatabaseUtils;
10import android.database.SQLException;
11import android.database.sqlite.SQLiteDatabase;
12import android.database.sqlite.SQLiteDatabaseCorruptException;
13import android.database.sqlite.SQLiteOpenHelper;
14import android.database.sqlite.SQLiteStatement;
15import android.preference.PreferenceManager;
16import android.util.Log;
17
18import java.io.BufferedReader;
19import java.io.IOException;
20import java.io.InputStreamReader;
21import java.util.ArrayList;
22import java.util.Iterator;
23import java.util.Map;
24import java.util.Set;
25
26public class Database {
27
28 public static String TAG = "hosl";
29
30 private static final int DATABASE_VERSION = 11;
31 private DatabaseHelper mDbHelper;
32 private SQLiteDatabase mDb;
33 private final Context mCtx;
34
35 private static final String DATABASE_NAME = "database.db";
36
37 public static final String TABLE_REGIONS = "regions";
38 public static final String TABLE_RESORTS = "resorts";
39 public static final String TABLE_FAVORITES = "favorites";
40 public static final String TABLE_STATIONS = "stations";
41
42 public static final String REGIONS_ID = "_id";
43 public static final String REGIONS_NAME = "name";
44 public static final String REGIONS_ALIAS = "alias";
45 public static final String REGIONS_CALLING_NUMBER = "calling_number";
46 public static final String REGIONS_SMS_NUMBERS = "sms_numbers";
47
48 public static final String RESORTS_ID = "_id";
49 public static final String RESORTS_REGION_ID = "region_id";
50 public static final String RESORTS_NAME = "name";
51 public static final String RESORTS_ALIAS = "alias";
52 public static final String RESORTS_LASTUPDATE = "lastupdate";
53 public static final String RESORTS_LATITUDE = "latitude";
54 public static final String RESORTS_LONGITUDE = "longitude";
55 public static final String RESORTS_WEATHER = "weather";
56 public static final String RESORTS_SNOW = "snow";
57 public static final String RESORTS_NEWSNOW = "newsnow";
58 public static final String RESORTS_SNOWTYPE = "snowtype";
59
60 public static final String FAVORITES_ALIAS = "alias";
61 public static final String FAVORITES_FLAG = "flag";
62
63 public static final String STATIONS_ID = "_id";
64 public static final String STATIONS_REGION_ALIAS = "region_alias";
65 public static final String STATIONS_RESORT_ALIAS = "resort_alias";
66 public static final String STATIONS_NAME = "name";
67 public static final String STATIONS_ALIAS = "alias";
68 public static final String STATIONS_LASTUPDATE = "lastupdate";
69 public static final String STATIONS_LATITUDE = "latitude";
70 public static final String STATIONS_LONGITUDE = "longitude";
71 public static final String STATIONS_STREET = "street";
72 public static final String STATIONS_HOUSENUMBER = "housenumber";
73 public static final String STATIONS_ZIP = "zip";
74 public static final String STATIONS_PHONE = "phone";
75 public static final String STATIONS_PHONE2 = "phone2";
76 public static final String STATIONS_CELL = "cell";
77
78 public static final String STATIONS_CITY = "city";
79 public static final String STATIONS_IMAGE = "img";
80 public static final String STATIONS_WEEKENDS_ONLY = "weekends";
81
82 private class DatabaseHelper extends SQLiteOpenHelper {
83
84 Context ctx;
85
86 DatabaseHelper(Context context) {
87 super(context, DATABASE_NAME, null, DATABASE_VERSION);
88 this.ctx = context;
89 }
90
91 private void dropTables(SQLiteDatabase db) {
92 db.execSQL("DROP TABLE IF EXISTS " + TABLE_REGIONS);
93 db.execSQL("DROP TABLE IF EXISTS " + TABLE_RESORTS);
94 db.execSQL("DROP TABLE IF EXISTS " + TABLE_STATIONS);
95 db.execSQL("DROP TABLE IF EXISTS " + TABLE_FAVORITES);
96 PreferenceManager.getDefaultSharedPreferences(mCtx).edit().clear()
97 .commit();
98 }
99
100 @Override
101 public void onCreate(SQLiteDatabase db) {
102 mDb = db;
103 Log.i(TAG, "oncreateDB");
104
105 String stations = "CREATE TABLE IF NOT EXISTS " + TABLE_STATIONS
106 + " ( "
107
108 + STATIONS_ID + " INTEGER primary key autoincrement, "
109
110 + STATIONS_REGION_ALIAS + " VARCHAR(100),"
111
112 + STATIONS_RESORT_ALIAS + " VARCHAR(100),"
113
114 + STATIONS_NAME + " VARCHAR(100),"
115
116 + STATIONS_ALIAS + " VARCHAR(100) UNIQUE, "
117
118 + STATIONS_LASTUPDATE + " INTEGER, "
119
120 + STATIONS_LATITUDE + " REAL, "
121
122 + STATIONS_LONGITUDE + " REAL, "
123
124 + STATIONS_STREET + " VARCHAR(200), "
125
126 + STATIONS_HOUSENUMBER + " VARCHAR(10), "
127
128 + STATIONS_ZIP + " VARCHAR(200), "
129
130 + STATIONS_PHONE + " VARCHAR(200), "
131
132 + STATIONS_PHONE2 + " VARCHAR(200), "
133
134 + STATIONS_CELL + " VARCHAR(200), "
135
136 + STATIONS_CITY + " VARCHAR(200), "
137
138 + STATIONS_IMAGE + " VARCHAR(200), "
139
140 + STATIONS_WEEKENDS_ONLY + " INTEGER "
141
142 + ");";
143
144 db.execSQL(stations);
145
146 String regions = "CREATE TABLE IF NOT EXISTS " + TABLE_REGIONS
147 + " ( "
148
149 + REGIONS_ID + " INTEGER primary key autoincrement, "
150
151 + REGIONS_NAME + " VARCHAR(100), "
152
153 + REGIONS_CALLING_NUMBER + " VARCHAR(100), "
154
155 + REGIONS_SMS_NUMBERS + " VARCHAR(255), "
156
157 + REGIONS_ALIAS + " VARCHAR(100));";
158 db.execSQL(regions);
159
160 String resorts = "CREATE TABLE IF NOT EXISTS " + TABLE_RESORTS
161 + " ( "
162
163 + RESORTS_ID + " INTEGER primary key autoincrement, "
164
165 + RESORTS_REGION_ID + " INTEGER, "
166
167 + RESORTS_NAME + " VARCHAR(100), "
168
169 + RESORTS_ALIAS + " VARCHAR(100) UNIQUE, "
170
171 + RESORTS_LASTUPDATE + " INTEGER, "
172
173 + RESORTS_LATITUDE + " REAL, "
174
175 + RESORTS_LONGITUDE + " REAL, "
176
177 + RESORTS_WEATHER + " INTEGER, "
178
179 + RESORTS_SNOW + " INTEGER, "
180
181 + RESORTS_NEWSNOW + " INTEGER, "
182
183 + RESORTS_SNOWTYPE + " INTEGER "
184
185 + ");";
186
187 db.execSQL(resorts);
188
189 String favorits = "CREATE TABLE IF NOT EXISTS " + TABLE_FAVORITES
190 + " ( "
191
192 + FAVORITES_ALIAS + " VARCHAR(100) primary key,"
193
194 + FAVORITES_FLAG + " INTEGER "
195
196 + ");";
197
198 db.execSQL(favorits);
199
200 BufferedReader ir = new BufferedReader(new InputStreamReader(ctx
201 .getResources()
202 .openRawResource(
203 R.raw.init_data)));
204 ArrayList<String> sqlStatements = new ArrayList<String>();
205
206 String line = null;
207 try {
208 while ((line = ir.readLine()) != null) {
209 sqlStatements.add(line);
210 }
211 } catch (IOException e) {
212 Log.e(TAG,
213 "Error opening offline data file: " + e.getMessage());
214 }
215
216 for (String sql : sqlStatements) {
217 db.execSQL(sql);
218 }
219
220 }
221
222 @Override
223 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
224 Log.w(TAG, "Upgrading database from version " + oldVersion
225 + " to " + newVersion
226 + ", which will destroy all old data");
227 dropTables(db);
228 onCreate(db);
229 }
230 }
231
232 public Database(Context ctx) {
233 this.mCtx = ctx;
234 }
235
236 public synchronized Database open() throws SQLException {
237 mDbHelper = new DatabaseHelper(mCtx);
238 mDb = mDbHelper.getWritableDatabase();
239 mDb.setLockingEnabled(false);
240 return this;
241 }
242
243 public synchronized Database openReadable() throws SQLException {
244 mDbHelper = new DatabaseHelper(mCtx);
245 mDb = mDbHelper.getReadableDatabase();
246 mDb.setLockingEnabled(false);
247 return this;
248 }
249
250 public synchronized void close() {
251 mDbHelper.close();
252 mDbHelper = null;
253 }
254
255 public synchronized long addRegion(int id, String name, String alias) {
256 ContentValues initialValues = new ContentValues();
257 initialValues.put(REGIONS_ID, id);
258 initialValues.put(REGIONS_NAME, name);
259 initialValues.put(REGIONS_ALIAS, alias);
260 return mDb.insert(TABLE_REGIONS, null, initialValues);
261 }
262
263 public synchronized void addOrReplaceRegion(int id, String name,
264 String alias) {
265 ContentValues initialValues = new ContentValues();
266 initialValues.put(REGIONS_ID, id);
267 initialValues.put(REGIONS_NAME, name);
268 initialValues.put(REGIONS_ALIAS, alias);
269 insertOrReplace(TABLE_REGIONS, null, initialValues);
270 }
271
272 public synchronized void addOrReplaceResort(int id, int regionId,
273 String name, String alias, long lastUpdate, double latitude,
274 double longitude, int weather, int snow, int newSnow, int snowType) {
275 ContentValues initialValues = new ContentValues();
276 initialValues.put(RESORTS_ID, id);
277 initialValues.put(RESORTS_REGION_ID, regionId);
278 initialValues.put(RESORTS_NAME, name);
279 initialValues.put(RESORTS_ALIAS, alias);
280 initialValues.put(RESORTS_LASTUPDATE, lastUpdate);
281 initialValues.put(RESORTS_LATITUDE, latitude);
282 initialValues.put(RESORTS_LONGITUDE, longitude);
283 initialValues.put(RESORTS_WEATHER, weather);
284 initialValues.put(RESORTS_SNOW, snow);
285 initialValues.put(RESORTS_SNOWTYPE, snowType);
286 insertOrReplace(TABLE_RESORTS, null, initialValues);
287
288 }
289
290 public synchronized void setRegionNumbers(String regionAlias,
291 String callingNumber, String smsNumbers) {
292 ContentValues args = new ContentValues();
293 args.put(REGIONS_CALLING_NUMBER, callingNumber);
294 args.put(REGIONS_SMS_NUMBERS, smsNumbers);
295 Log.d(TAG, "Updating region: " + regionAlias + " with number:"
296 + callingNumber);
297 mDb.update(TABLE_REGIONS, args, REGIONS_ALIAS + "='" + regionAlias
298 + "'", null);
299 }
300
301 public synchronized void addOrReplaceStation(String name, String alias,
302 String region, double latitude, double longitude, String street,
303 String city, String zipCode, String phone, String mobile,
304 String photoUrl, boolean onlyWeekends) {
305 Log.d("db", "inserting station " + name);
306 ContentValues initialValues = new ContentValues();
307 initialValues.put(STATIONS_NAME, name);
308 initialValues.put(STATIONS_ALIAS, alias);
309 initialValues.put(STATIONS_REGION_ALIAS, region);
310 initialValues.put(STATIONS_LATITUDE, latitude);
311 initialValues.put(STATIONS_LONGITUDE, longitude);
312 initialValues.put(STATIONS_STREET, street);
313 initialValues.put(STATIONS_CITY, city);
314 initialValues.put(STATIONS_ZIP, zipCode);
315 initialValues.put(STATIONS_PHONE, phone);
316 initialValues.put(STATIONS_CELL, mobile);
317 initialValues.put(STATIONS_IMAGE, photoUrl);
318 if (onlyWeekends) {
319 initialValues.put(STATIONS_WEEKENDS_ONLY, 1);
320 } else {
321 initialValues.put(STATIONS_WEEKENDS_ONLY, 0);
322 }
323 insertOrReplace(TABLE_STATIONS, null, initialValues);
324
325 }
326
327 public synchronized void insertOrReplace(String table,
328 String nullColumnHack, ContentValues initialValues) {
329 StringBuilder sql = new StringBuilder(152);
330 sql.append("INSERT");
331 sql.append(" OR REPLACE");
332 sql.append(" INTO ");
333 sql.append(table);
334 // Measurements show most values lengths < 40
335 StringBuilder values = new StringBuilder(40);
336
337 Set<Map.Entry<String, Object>> entrySet = null;
338 if (initialValues != null && initialValues.size() > 0) {
339 entrySet = initialValues.valueSet();
340 Iterator<Map.Entry<String, Object>> entriesIter = entrySet
341 .iterator();
342 sql.append('(');
343
344 boolean needSeparator = false;
345 while (entriesIter.hasNext()) {
346 if (needSeparator) {
347 sql.append(", ");
348 values.append(", ");
349 }
350 needSeparator = true;
351 Map.Entry<String, Object> entry = entriesIter.next();
352 sql.append(entry.getKey());
353 values.append('?');
354 }
355
356 sql.append(')');
357 } else {
358 sql.append("(" + nullColumnHack + ") ");
359 values.append("NULL");
360 }
361
362 sql.append(" VALUES(");
363 sql.append(values);
364 sql.append(");");
365 SQLiteStatement statement = null;
366 try {
367 statement = mDb.compileStatement(sql.toString());
368
369 // Bind the values
370 if (entrySet != null) {
371 int size = entrySet.size();
372 Iterator<Map.Entry<String, Object>> entriesIter = entrySet
373 .iterator();
374 for (int i = 0; i < size; i++) {
375 Map.Entry<String, Object> entry = entriesIter.next();
376 DatabaseUtils.bindObjectToProgram(statement, i + 1,
377 entry.getValue());
378 }
379 }
380
381 // Run the program and then cleanup
382 statement.execute();
383 } catch (SQLiteDatabaseCorruptException e) {
384 throw e;
385 } finally {
386 if (statement != null) {
387 statement.close();
388 }
389 }
390 }
391
392 public synchronized long addResort(int id, int regionId, String name,
393 String alias, long lastUpdate, double latitude, double longitude,
394 int weather, int snow, int newSnow, int snowType) {
395 ContentValues initialValues = new ContentValues();
396 initialValues.put(RESORTS_ID, id);
397 initialValues.put(RESORTS_REGION_ID, regionId);
398 initialValues.put(RESORTS_NAME, name);
399 initialValues.put(RESORTS_ALIAS, alias);
400 initialValues.put(RESORTS_LASTUPDATE, lastUpdate);
401 initialValues.put(RESORTS_LATITUDE, latitude);
402 initialValues.put(RESORTS_LONGITUDE, longitude);
403 initialValues.put(RESORTS_WEATHER, weather);
404 initialValues.put(RESORTS_SNOW, snow);
405 initialValues.put(RESORTS_SNOWTYPE, snowType);
406 return mDb.insert(TABLE_RESORTS, null, initialValues);
407 }
408
409 public synchronized long addFavorite(String resortAlias) {
410 ContentValues initialValues = new ContentValues();
411 initialValues.put(FAVORITES_ALIAS, resortAlias);
412 initialValues.put(FAVORITES_FLAG, 1);
413 return mDb.insert(TABLE_FAVORITES, null, initialValues);
414 }
415
416 public synchronized int removeFavorite(String resortAlias) {
417 return mDb.delete(TABLE_FAVORITES, FAVORITES_ALIAS + "='"
418 + resortAlias + "'", null);
419 }
420
421 public synchronized Cursor getFavorites() {
422 Cursor c = mDb.query(TABLE_FAVORITES, null, null, null, null, null,
423 null);
424 return prepareCursor(c);
425 }
426
427 public synchronized Cursor getFavorite(String resortAlias) {
428 Cursor c = mDb.query(TABLE_FAVORITES, null, FAVORITES_ALIAS + "='"
429 + resortAlias + "'", null, null, null, null);
430 return prepareCursor(c);
431 }
432
433 public synchronized Cursor getFavoriteResorts() {
434 Cursor c = mDb.rawQuery("SELECT "
435 + TABLE_RESORTS
436 + ".* FROM "
437 + TABLE_RESORTS
438 + " JOIN "
439 + TABLE_FAVORITES
440 + " ON "
441 + TABLE_RESORTS
442 + "."
443 + RESORTS_ALIAS
444 + "="
445 + TABLE_FAVORITES
446 + "."
447 + FAVORITES_ALIAS
448 + ";", null);
449 return prepareCursor(c);
450 }
451
452 public synchronized void deleteAll() {
453 mDb.delete(TABLE_REGIONS, null, null);
454 mDb.delete(TABLE_RESORTS, null, null);
455 mDb.delete(TABLE_STATIONS, null, null);
456 }
457
458 private Cursor prepareCursor(Cursor c) {
459 if (c != null && c.moveToFirst())
460 return c;
461 return null;
462 }
463
464 public synchronized boolean isTableEmpty(String table) {
465 Cursor cur = mDb.query(table, null, null, null, null, null, null);
466 return (cur == null || cur.getCount() == 0);
467 }
468
469 // ----------------------------------------
470 public synchronized Cursor getRegions() {
471 Cursor c = mDb.rawQuery("SELECT * FROM " + TABLE_REGIONS
472 + " ORDER BY " + REGIONS_NAME + " COLLATE LOCALIZED", null);
473
474 return prepareCursor(c);
475 }
476
477 public synchronized Cursor getRegionsWithStations() {
478 Cursor c = mDb.rawQuery("SELECT "
479 + TABLE_REGIONS
480 + ".* FROM "
481 + TABLE_REGIONS
482 + " JOIN "
483 + TABLE_STATIONS
484 + " ON "
485 + TABLE_REGIONS
486 + "."
487 + REGIONS_ALIAS
488 + "="
489 + TABLE_STATIONS
490 + "."
491 + STATIONS_REGION_ALIAS
492 + " ORDER BY "
493 + TABLE_REGIONS
494 + "."
495 + REGIONS_NAME
496 + " COLLATE LOCALIZED"
497 + ";", null);
498 // Cursor c = mDb.rawQuery("SELECT * FROM " + TABLE_REGIONS +
499 // " ORDER BY " + REGIONS_NAME + " COLLATE LOCALIZED", null);
500
501 return prepareCursor(c);
502 }
503
504 public synchronized Cursor getRegion(String alias) {
505 Cursor c = mDb.query(TABLE_REGIONS, null, REGIONS_ALIAS + "='" + alias
506 + "'", null, null, null, null);
507 return prepareCursor(c);
508 }
509
510 public synchronized Cursor getRegion(int regionID) {
511 Cursor c = mDb.query(TABLE_REGIONS, null, REGIONS_ID + "='" + regionID
512 + "'", null, null, null, null);
513 return prepareCursor(c);
514 }
515
516 // ----------------------------------------
517 public synchronized Cursor getResorts() {
518 Cursor c = mDb.rawQuery("SELECT * FROM " + TABLE_RESORTS
519 + " ORDER BY " + RESORTS_NAME + " COLLATE LOCALIZED", null);
520 return prepareCursor(c);
521 }
522
523 public synchronized Cursor getResorts(int regionID) {
524 Cursor c = mDb.query(TABLE_RESORTS, null, RESORTS_REGION_ID + "='"
525 + regionID + "'", null, null, null, RESORTS_NAME);
526 return prepareCursor(c);
527 }
528
529 public synchronized Cursor getResort(int resortID) {
530 Cursor c = mDb.query(TABLE_RESORTS, null, RESORTS_ID + "='" + resortID
531 + "'", null, null, null, null);
532 return prepareCursor(c);
533 }
534
535 // ----------------------------------------
536 public synchronized Cursor getStations() {
537 Cursor c = mDb.rawQuery("SELECT * FROM " + TABLE_STATIONS
538 + " ORDER BY " + STATIONS_NAME + " COLLATE LOCALIZED", null);
539 return prepareCursor(c);
540 }
541
542 public synchronized Cursor getStation(int stationID) {
543 Cursor c = mDb.query(TABLE_STATIONS, null, STATIONS_ID + "='"
544 + stationID + "'", null, null, null, null);
545 return prepareCursor(c);
546 }
547
548 public synchronized Cursor getStations(String region) {
549 Cursor c = mDb.rawQuery("SELECT * FROM " + TABLE_STATIONS + " WHERE "
550 + STATIONS_REGION_ALIAS + "='" + region + "' ORDER BY "
551 + STATIONS_NAME + " COLLATE LOCALIZED", null);
552 return prepareCursor(c);
553 }
554
555}