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