· 9 years ago · Jan 24, 2017, 01:28 AM
1package com.example.artur.timeger.helper;
2
3import android.content.ContentValues;
4import android.content.Context;
5import android.database.Cursor;
6import android.database.sqlite.SQLiteDatabase;
7import android.database.sqlite.SQLiteOpenHelper;
8import android.util.Log;
9
10import com.example.artur.timeger.model.Quest;
11import com.example.artur.timeger.model.Tag;
12import com.example.artur.timeger.model.User;
13
14import java.text.SimpleDateFormat;
15import java.util.ArrayList;
16import java.util.Date;
17import java.util.List;
18import java.util.Locale;
19
20/**
21 * Created by bionanek on 26-11-2016.
22 */
23
24// everything made by Kuba
25
26public class DatabaseHelper extends SQLiteOpenHelper
27{
28 // Logcat tag
29 private static final String LOG = "DatabaseHelper";
30
31 // Database Version
32 private static final int DATABASE_VERSION = 1;
33
34 // Database Name
35 private static final String DATABASE_NAME = "Timeger";
36
37 // Table Names
38 private static final String TABLE_QUEST = "quest";
39 private static final String TABLE_USER = "user";
40 private static final String TABLE_TAG = "tag";
41 private static final String TABLE_QUEST_TAG = "quest_tag";
42
43 // Common column names
44 private static final String KEY_ID = "_id";
45 private static final String KEY_CREATED_AT = "created_at";
46
47 // QUEST Table - column names
48 private static final String KEY_DESCRIPTION = "description";
49 private static final String KEY_STATUS = "status";
50 private static final String KEY_PRIORITY = "priority";
51 private static final String KEY_QUEST_DATE = "quest_date";
52 private static final String KEY_ALARM_DATE = "alarm_date";
53 private static final String KEY_QUEST_PLACE = "quest_place";
54 private static final String KEY_COMMENTS = "comments";
55 private static final String KEY_TYPE = "type";
56 private static final String KEY_ORGANISATOR = "organisator";
57
58 //USER Table - column names
59 private static final String KEY_LOGIN = "login";
60 private static final String KEY_PASSWORD = "password";
61 private static final String KEY_NAME = "name";
62 private static final String KEY_SURNAME = "surname";
63 private static final String KEY_EMAIL = "email";
64
65 // TAGS Table - column names
66 private static final String KEY_TAG_NAME = "tag_name";
67
68 // NOTE_TAGS Table - column names
69 private static final String KEY_QUEST_ID = "quest_id";
70 private static final String KEY_TAG_ID = "tag_id";
71
72 //Table create statements
73 //Quest table create
74 private static String CREATE_TABLE_QUEST =
75 "CREATE TABLE IF NOT EXISTS "+TABLE_QUEST+"(" +
76 KEY_ID + " INTEGER primary key autoincrement," +
77 KEY_DESCRIPTION + " TEXT not null," +
78 KEY_STATUS + " INTEGER," +
79 KEY_PRIORITY + " TEXT," +
80 KEY_QUEST_DATE + " DATETIME," +
81 KEY_ALARM_DATE + " DATETIME," +
82 KEY_QUEST_PLACE + " TEXT," +
83 KEY_TYPE + " TEXT," +
84 KEY_ORGANISATOR + " TEXT," +
85 KEY_COMMENTS + " TEXT," +
86 KEY_CREATED_AT + " DATETIME);";
87
88 //User table create
89 private static String CREATE_TABLE_USER =
90 "CREATE TABLE IF NOT EXISTS "+TABLE_USER+"(" +
91 KEY_ID + " INTEGER primary key autoincrement," +
92 KEY_LOGIN + " TEXT," +
93 KEY_PASSWORD + " TEXT," +
94 KEY_NAME + " TEXT," +
95 KEY_SURNAME + " TEXT," +
96 KEY_EMAIL + " TEXT," +
97 KEY_CREATED_AT + "DATETIME);";
98
99 //Tag table create
100 private static String CREATE_TABLE_TAG =
101 "CREATE TABLE IF NOT EXISTS "+TABLE_TAG+"(" +
102 KEY_ID + " INTEGER primary key autoincrement," +
103 KEY_TAG_NAME + " TEXT," +
104 KEY_CREATED_AT + "DATETIME);";
105
106 //Quest_Tag table create
107 private static String CREATE_TABLE_QUEST_TAG =
108 "CREATE TABLE IF NOT EXISTS "+TABLE_TAG+"(" +
109 KEY_ID + " INTEGER primary key autoincrement," +
110 KEY_QUEST_ID + " INTEGER," +
111 KEY_TAG_ID + " INTEGER);";
112
113 public DatabaseHelper(Context con)
114 {
115 super(con, DATABASE_NAME, null, DATABASE_VERSION);
116 }
117
118 @Override
119 public void onCreate(SQLiteDatabase db)
120 {
121 //Creating tables
122 try
123 {
124 db.execSQL(CREATE_TABLE_QUEST);
125 db.execSQL(CREATE_TABLE_TAG);
126 db.execSQL(CREATE_TABLE_USER);
127 db.execSQL(CREATE_TABLE_QUEST_TAG);
128 }
129 catch (Exception ex)
130 {
131 Log.e("Error creating tables", ex.toString());
132 }
133 }
134
135 @Override
136 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
137 {
138 try
139 {
140 db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUEST);
141 db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
142 db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG);
143 db.execSQL("DROP TABLE IF EXISTS " + TABLE_QUEST_TAG);
144
145 this.onCreate(db);
146 }
147 catch (Exception ex)
148 {
149 Log.e("Error upgrading db", ex.toString());
150 }
151 }
152
153 //QUEST METHODS
154
155 public long createQuest(Quest quest, long[] tag_ids)
156 {
157 SQLiteDatabase db = this.getWritableDatabase();
158
159 ContentValues values = this.setQuestObjectValues(quest);
160
161 // insert row
162 long quest_id = db.insert(TABLE_QUEST, null, values);
163
164 // assigning tags to quest
165 for (long tag_id : tag_ids)
166 {
167 createQuestTag(quest_id, tag_id);
168 }
169
170 return quest_id;
171 }
172
173 public Quest getQuestById(long quest_id)
174 {
175 SQLiteDatabase db = this.getWritableDatabase();
176
177 String selectQuery = "SELECT * FROM " + TABLE_QUEST +
178 " WHERE " + KEY_ID + " = " + quest_id;
179
180 Log.e(LOG, selectQuery);
181
182 Cursor c = db.rawQuery(selectQuery, null);
183
184 if(c != null)
185 c.moveToFirst();
186
187 Quest quest = new Quest();
188 quest.setId(c.getInt(c.getColumnIndex(KEY_ID)));
189 quest.setTitle(c.getString(c.getColumnIndex(KEY_DESCRIPTION)));
190 quest.setStatus(c.getString(c.getColumnIndex(KEY_STATUS)));
191 quest.setQuestDate((c.getString(c.getColumnIndex(KEY_QUEST_DATE))));
192 quest.setAlarm((c.getString(c.getColumnIndex(KEY_ALARM_DATE))));
193 quest.setPlace((c.getString(c.getColumnIndex(KEY_QUEST_PLACE))));
194 quest.setType((c.getString(c.getColumnIndex(KEY_TYPE))));
195 quest.setDescription((c.getString(c.getColumnIndex(KEY_COMMENTS))));
196 quest.setCreatedAt((c.getString(c.getColumnIndex(KEY_CREATED_AT))));
197
198 c.close();
199
200 return quest;
201 }
202
203 public long createQuestTag(long quest_id, long tag_id)
204 {
205 SQLiteDatabase db = this.getWritableDatabase();
206
207 ContentValues values = new ContentValues();
208 values.put(KEY_QUEST_ID, quest_id);
209 values.put(KEY_TAG_ID, tag_id);
210 values.put(KEY_CREATED_AT, getDateTime());
211
212 long id = db.insert(TABLE_QUEST_TAG, null, values);
213
214 return id;
215 }
216
217 public List<Quest> getAllQuests()
218 {
219 List<Quest> quests = new ArrayList<>();
220 String selectQuery = "SELECT * FROM " + TABLE_QUEST;
221
222 Log.e(LOG, selectQuery);
223
224 SQLiteDatabase db = this.getReadableDatabase();
225 Cursor cursor = db.rawQuery(selectQuery, null);
226 quests = this.getListOfQuests(cursor);
227 cursor.close();
228
229 return quests;
230 }
231
232 public List<Quest> getAllQuestsByTag(String tagName)
233 {
234 String selectQuery = "SELECT * FROM " + TABLE_QUEST + " qst, "
235 + TABLE_TAG + " tg, " + TABLE_QUEST_TAG + " qt WHERE tg."
236 + KEY_TAG_NAME + " = '" + tagName + "'" + " AND tg." + KEY_ID
237 + " = " + "qt." + KEY_TAG_ID + " AND qst." + KEY_ID + " = "
238 + "qt." + KEY_QUEST_ID;
239
240 Log.e(LOG, selectQuery);
241
242 SQLiteDatabase db = this.getReadableDatabase();
243 Cursor cursor = db.rawQuery(selectQuery, null);
244 List<Quest> quests = this.getListOfQuests(cursor);
245 cursor.close();
246
247 return quests;
248 }
249
250 public int updateQuest(Quest quest)
251 {
252 SQLiteDatabase db = this.getWritableDatabase();
253 ContentValues values = this.setQuestObjectValues(quest);
254
255 return db.update(TABLE_QUEST, values, KEY_ID + " = ?",
256 new String[] {String.valueOf(quest.getId())} );
257
258 }
259
260 public void deleteQuest(int questId)
261 {
262 SQLiteDatabase db = this.getWritableDatabase();
263 db.delete(TABLE_QUEST, KEY_ID + " = ?",
264 new String[] {String.valueOf(questId)} );
265 }
266
267 // TAG METHODS
268 public long createTag(Tag tag)
269 {
270 SQLiteDatabase db = getWritableDatabase();
271 ContentValues values = new ContentValues();
272 values.put(KEY_TAG_NAME, tag.getTagName());
273 values.put(KEY_CREATED_AT, getDateTime());
274
275 //insert row and return id;
276 return db.insert(TABLE_TAG, null, values);
277 }
278
279 public List<Tag> getAllTags()
280 {
281 List<Tag> tags = new ArrayList<>();
282 String selectQuery = "SELECT * FROM " + TABLE_TAG;
283
284 Log.e(LOG, selectQuery);
285
286 SQLiteDatabase db = this.getReadableDatabase();
287 Cursor c = db.rawQuery(selectQuery, null);
288
289 if(c.moveToFirst())
290 {
291 do
292 {
293 Tag t = new Tag();
294 t.setId(c.getInt(c.getColumnIndex(KEY_ID)));
295 t.setTagName(c.getString(c.getColumnIndex(KEY_TAG_NAME)));
296
297 tags.add(t);
298 }while(c.moveToNext());
299 }
300 c.close();
301 return tags;
302 }
303
304 public int updateTag(Tag tag)
305 {
306 SQLiteDatabase db = this.getWritableDatabase();
307
308 ContentValues values = new ContentValues();
309 values.put(KEY_TAG_NAME, tag.getTagName());
310
311 return db.update(TABLE_TAG, values, KEY_ID + " = ?",
312 new String[] { String.valueOf(tag.getId()) });
313 }
314
315 public void deleteTag(Tag tag, boolean deleteAllTagQuests)
316 {
317 SQLiteDatabase db = getWritableDatabase();
318
319 //check if quests udner this tag should be deleted too
320 if(deleteAllTagQuests)
321 {
322 List<Quest> allTagQuests = getAllQuestsByTag(tag.getTagName());
323
324 // delete all quests
325 for(Quest quest : allTagQuests)
326 {
327 deleteQuest(quest.getId());
328 }
329 }
330 //delete the tag
331 db.delete(TABLE_TAG, KEY_ID + " = ?",
332 new String[] { String.valueOf(tag.getId()) });
333 }
334
335 // QUEST_TAG TABLE METHODS
336 public long createQuestTag(int questId, int tagId)
337 {
338 SQLiteDatabase db = this.getWritableDatabase();
339
340 ContentValues values = new ContentValues();
341 values.put(KEY_QUEST_ID, questId);
342 values.put(KEY_TAG_ID, tagId);
343 values.put(KEY_CREATED_AT, getDateTime());
344
345 long id = db.insert(TABLE_QUEST_TAG, null, values);
346
347 return id;
348 }
349
350 // following method will remove the tag assigned to a quest
351 public void deleteToDoTag(long id)
352 {
353 SQLiteDatabase db = this.getWritableDatabase();
354
355 db.delete(TABLE_QUEST, KEY_ID + " = ?",
356 new String[] { String.valueOf(id) });
357 }
358
359 public int updateQuestTag(long questId, long tagId)
360 {
361 SQLiteDatabase db = this.getWritableDatabase();
362
363 ContentValues values = new ContentValues();
364 values.put(KEY_TAG_ID, tagId);
365
366 return db.update(TABLE_QUEST, values, KEY_ID + " = ?",
367 new String[] { String.valueOf(questId) });
368 }
369
370 //USER TABLE METHODS
371 public long createUser(User user)
372 {
373 SQLiteDatabase db = this.getWritableDatabase();
374
375 ContentValues values = this.setUserObjectValues(user);
376
377 Log.e("USER", "USER" + user.getLogin() + " CREATED");
378
379 return db.insert(TABLE_USER, null, values);
380 }
381
382 public List<User> getAllUsers()
383 {
384 List<User> users = new ArrayList<>();
385 String selectQuery = "SELECT * FROM " + TABLE_USER;
386
387 Log.e(LOG, selectQuery);
388
389 SQLiteDatabase db = this.getReadableDatabase();
390 Cursor c = db.rawQuery(selectQuery, null);
391
392 if(c.moveToFirst())
393 {
394 do
395 {
396 User u = new User();
397 u.setId(c.getInt(c.getColumnIndex(KEY_ID)));
398 u.setLogin(c.getString(c.getColumnIndex(KEY_LOGIN)));
399 u.setName(c.getString(c.getColumnIndex(KEY_NAME)));
400 u.setEmail(c.getString(c.getColumnIndex(KEY_EMAIL)));
401 u.setPass(c.getString(c.getColumnIndex(KEY_PASSWORD)));
402 u.setSurname(c.getString(c.getColumnIndex(KEY_SURNAME)));
403 users.add(u);
404 }while(c.moveToNext());
405 }
406 c.close();
407 return users;
408 }
409
410 public void deleteUser(User user)
411 {
412 SQLiteDatabase db = this.getWritableDatabase();
413 db.delete(TABLE_USER, KEY_ID + " = ?",
414 new String[] { String.valueOf(user.getId()) });
415 }
416
417 public long updateUser(User user)
418 {
419 SQLiteDatabase db = this.getWritableDatabase();
420
421 ContentValues values = this.setUserObjectValues(user);
422
423 return db.update(TABLE_TAG, values, KEY_ID + " = ?",
424 new String[] { String.valueOf(user.getId()) });
425 }
426
427 public User getUserById(long userID)
428 {
429 SQLiteDatabase db = this.getWritableDatabase();
430
431 String selectQuery = "SELECT * FROM " + TABLE_USER +
432 " WHERE " + KEY_ID + " = " + userID;
433
434 Log.e(LOG, selectQuery);
435
436 Cursor c = db.rawQuery(selectQuery, null);
437
438 if(c != null)
439 c.moveToFirst();
440
441 User user = new User();
442
443 user.setId(c.getInt(c.getColumnIndex(KEY_ID)));
444 user.setLogin(c.getString(c.getColumnIndex(KEY_LOGIN)));
445 user.setPass(c.getString(c.getColumnIndex(KEY_PASSWORD)));
446 user.setName(c.getString(c.getColumnIndex(KEY_NAME)));
447 user.setSurname(c.getString(c.getColumnIndex(KEY_SURNAME)));
448 user.setEmail(c.getString(c.getColumnIndex(KEY_EMAIL)));
449
450 c.close();
451
452 return user;
453 }
454
455
456 // CLOSING DATABASE CONNECTION
457 public void closeDB()
458 {
459 SQLiteDatabase db = this.getReadableDatabase();
460 if(db != null && db.isOpen())
461 db.close();
462 }
463
464 // helpers classes
465 private ContentValues setUserObjectValues(User user)
466 {
467 ContentValues values = new ContentValues();
468 values.put(KEY_LOGIN, user.getLogin());
469 values.put(KEY_PASSWORD, user.getPass());
470 values.put(KEY_NAME, user.getName());
471 values.put(KEY_SURNAME, user.getSurname());
472 values.put(KEY_EMAIL, user.getEmail());
473 return values;
474 }
475
476 private String getDateTime()
477 {
478 SimpleDateFormat dateFormat =
479 new SimpleDateFormat("yyy-MM-dd HH:mm:ss", Locale.getDefault());
480 Date date = new Date();
481
482 return dateFormat.format(date);
483 }
484
485 private List<Quest> getListOfQuests(Cursor c)
486 {
487 List<Quest> quests = new ArrayList<>();
488 if(c.moveToFirst())
489 {
490 do
491 {
492 Quest quest = new Quest();
493 quest.setId(c.getInt(c.getColumnIndex(KEY_ID)));
494 quest.setTitle(c.getString(c.getColumnIndex(KEY_DESCRIPTION)));
495 quest.setStatus(c.getString(c.getColumnIndex(KEY_STATUS)));
496 quest.setQuestDate((c.getString(c.getColumnIndex(KEY_QUEST_DATE))));
497 quest.setAlarm((c.getString(c.getColumnIndex(KEY_ALARM_DATE))));
498 quest.setPlace((c.getString(c.getColumnIndex(KEY_QUEST_PLACE))));
499 quest.setType((c.getString(c.getColumnIndex(KEY_TYPE))));
500 quest.setDescription((c.getString(c.getColumnIndex(KEY_COMMENTS))));
501 quest.setCreatedAt((c.getString(c.getColumnIndex(KEY_CREATED_AT))));
502
503 //adds this row to list
504 quests.add(quest);
505
506 }while(c.moveToNext());
507 }
508 c.close();
509 return quests;
510 }
511
512 private ContentValues setQuestObjectValues(Quest quest)
513 {
514 ContentValues values = new ContentValues();
515 values.put(KEY_DESCRIPTION, quest.getTitle());
516 values.put(KEY_STATUS, quest.getStatus());
517 values.put(KEY_QUEST_DATE, quest.getQuestDate());
518 values.put(KEY_ALARM_DATE, quest.getAlarmDate());
519 values.put(KEY_QUEST_PLACE, quest.getPlace());
520 values.put(KEY_TYPE, quest.getType());
521 values.put(KEY_COMMENTS, quest.getDescription());
522 values.put(KEY_CREATED_AT, getDateTime());
523
524 return values;
525 }
526}