1 package de.dhbwloe.campusapp.database;
3 import android.app.Activity;
4 import android.content.ContentValues;
5 import android.database.Cursor;
6 import android.database.sqlite.SQLiteDatabase;
7 import android.util.Log;
9 import net.fortuna.ical4j.model.Component;
10 import net.fortuna.ical4j.model.DateList;
11 import net.fortuna.ical4j.model.DateTime;
12 import net.fortuna.ical4j.model.Period;
13 import net.fortuna.ical4j.model.Recur;
14 import net.fortuna.ical4j.model.parameter.Value;
15 import net.fortuna.ical4j.model.property.RRule;
17 import java.lang.reflect.Array;
18 import java.text.ParseException;
19 import java.util.ArrayList;
20 import java.util.Date;
21 import java.util.ListIterator;
23 import de.dhbwloe.campusapp.CampusAppContext;
24 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
25 import de.dhbwloe.campusapp.news.NewsItem;
26 import de.dhbwloe.campusapp.search.SearchIndices;
27 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
28 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
31 * Created by pk910 on 19.01.2016.
33 public class DatabaseManager {
34 private static final String DATABASE_NAME = "DHBWLoe.CampusApp.DEV";
35 private static final int DATABASE_VERSION = 2;
36 private CampusAppContext AppContext;
37 private SQLiteDatabase database;
38 private NewsDatabaseHelper newsDBHelper;
39 private MensaplanDatabaseHelper mensaplanDBHelper;
40 private VorlesungsplanDatabaseHelper vorlesungsplanDBHelper;
43 public DatabaseManager(CampusAppContext context) {
47 private void openDatabase() {
51 database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
54 public void initializeDatabase() {
56 database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
58 Cursor resultSet = database.rawQuery("Select * from Version", null);
60 if(resultSet.moveToFirst()) {
61 version = resultSet.getInt(0);
64 database.execSQL("INSERT INTO Version (Version) VALUES (0);");
68 if(version < DATABASE_VERSION)
69 upgradeTables(version, DATABASE_VERSION);
71 //database.execSQL("DELETE FROM CourseCalendar");
72 //database.execSQL("DELETE FROM CourseCalendarEvent");
75 private void upgradeTables(int oldVersion, int newVersion) {
76 if(oldVersion == 0 && newVersion > 0) {
77 database.execSQL("CREATE TABLE IF NOT EXISTS RuntimeCache " +
82 "PRIMARY KEY (Reference)" +
84 database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
89 "Description TEXT, " +
93 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
95 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
101 "EventTitle TEXT, " +
102 "EventLocation TEXT, " +
103 "EventStatus TEXT," +
105 "ExcludeDates TEXT," +
106 "CourseGroupId INT," +
107 "UNIQUE (CourseName, UniqueId)" +
109 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
114 "PRIMARY KEY (EventId, EventFrom, EventTo)" +
116 database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
117 database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
121 "CardBalance INT, " +
122 "CardLastTransaction INT, " +
123 "PRIMARY KEY (CardId, UpdateTime)" +
125 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
127 "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
131 "UNIQUE (GroupName)" +
133 database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
134 database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
141 "Additional TEXT, " +
143 "PriceStudents INT, " +
144 "PriceEmployees INT, " +
145 "PriceGuests INT, " +
146 "PriceSchool INT, " +
147 "PRIMARY KEY (PlanDate, MenuName)" +
149 database.execSQL("CREATE TABLE IF NOT EXISTS News " +
151 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
160 "Categories TEXT, " +
161 "UNIQUE(Source, UniqueId) " +
163 database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
165 if(oldVersion < 2 && newVersion >= 2) {
167 database.execSQL("ALTER TABLE CourseCalendarEvent ADD EventType INT;");
169 if(oldVersion < 3 && newVersion >= 3) {
174 database.execSQL("UPDATE Version SET Version = " + Integer.toString(newVersion));
177 public void addSearchIndices(SearchIndices[] indices) {
179 for(int i = 0; i < indices.length; i++) {
180 String[] whereArgs = new String[] {
181 indices[i].getKeyName()
183 Cursor resultSet = database.rawQuery("SELECT UpdateTime FROM SearchIndex WHERE KeyName = ?", whereArgs);
184 if(resultSet.moveToFirst()) {
185 long updateTime = resultSet.getLong(0);
186 if (updateTime < indices[i].getUpdateTime()) {
188 database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
195 ContentValues indexValues = new ContentValues();
196 indexValues.put("KeyName", indices[i].getKeyName());
197 indexValues.put("SearchText", indices[i].getKeyWords());
198 indexValues.put("SearchTitle", indices[i].getTitle());
199 indexValues.put("Description", indices[i].getDescription());
200 indexValues.put("StaticEntry", indices[i].getIsStatic() ? 1 : 0);
201 indexValues.put("UpdateTime", indices[i].getUpdateTime());
202 indexValues.put("TargetPage", indices[i].getTarget());
204 database.insertOrThrow("SearchIndex", null, indexValues);
205 } catch(Exception e) {
211 public SearchIndices[] performSearchRequest(String query, int maxResults) {
213 String[] whereArgs = new String[] {
216 Cursor resultSet = database.rawQuery("SELECT KeyName, SearchTitle, Description, StaticEntry, UpdateTime, TargetPage, SearchText FROM SearchIndex WHERE SearchText LIKE ? ORDER BY StaticEntry DESC, UpdateTime DESC", whereArgs);
217 ArrayList<SearchIndices> indices = new ArrayList<SearchIndices>();
218 if(resultSet.moveToFirst()) {
219 int[] columnIndexes = {
220 resultSet.getColumnIndex("KeyName"),
221 resultSet.getColumnIndex("SearchTitle"),
222 resultSet.getColumnIndex("Description"),
223 resultSet.getColumnIndex("StaticEntry"),
224 resultSet.getColumnIndex("UpdateTime"),
225 resultSet.getColumnIndex("TargetPage"),
226 resultSet.getColumnIndex("SearchText")
229 SearchIndices cIndices = new SearchIndices(resultSet.getString(columnIndexes[0]), (resultSet.getInt(columnIndexes[3]) == 1));
230 cIndices.setUpdateTime(resultSet.getLong(columnIndexes[4]));
231 cIndices.setTarget(resultSet.getString(columnIndexes[5]));
232 cIndices.addKeyWord(resultSet.getString(columnIndexes[6]));
233 cIndices.setTitle(resultSet.getString(columnIndexes[1]));
234 cIndices.setDescription(resultSet.getString(columnIndexes[2]));
235 indices.add(cIndices);
236 } while (resultSet.moveToNext() && indices.size() < maxResults);
240 SearchIndices[] indicesArr = new SearchIndices[indices.size()];
241 indicesArr = indices.toArray(indicesArr);
245 public void setRuntimeCache(String name, String value) {
247 long now = (new Date()).getTime() / 1000;
248 String[] whereArgs = new String[] {
251 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
252 if(resultSet.moveToFirst()) {
253 if(resultSet.getString(0).equalsIgnoreCase(value))
256 ContentValues updateValues = new ContentValues();
257 updateValues.put("Value", value);
258 updateValues.put("LastUpdate", now);
260 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
261 } catch(Exception e) {
266 ContentValues indexValues = new ContentValues();
267 indexValues.put("Reference", name);
268 indexValues.put("Value", value);
269 indexValues.put("LastUpdate", now);
271 database.insertOrThrow("RuntimeCache", null, indexValues);
272 } catch(Exception e) {
279 public String getRuntimeCache(String name) {
282 String[] whereArgs = new String[] {
285 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
286 if(resultSet.moveToFirst()) {
287 value = resultSet.getString(0);
293 public void addNfcCardData(NfcCardData nfcCardData) {
295 String[] whereArgs = new String[] {
296 Integer.toString(nfcCardData.getUniqueId()),
297 Long.toString(nfcCardData.getLastUpdate())
299 Cursor resultSet = database.rawQuery("SELECT CardBalance FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
300 if(resultSet.moveToFirst()) {
302 ContentValues updateValues = new ContentValues();
303 updateValues.put("CardBalance", nfcCardData.getBalance());
304 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
306 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
307 } catch(Exception e) {
312 ContentValues indexValues = new ContentValues();
313 indexValues.put("CardId", nfcCardData.getUniqueId());
314 indexValues.put("UpdateTime", nfcCardData.getLastUpdate());
315 indexValues.put("CardBalance", nfcCardData.getBalance());
316 indexValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
318 database.insertOrThrow("NfcCardStore", null, indexValues);
319 } catch(Exception e) {
326 public NfcCardData[] getNfcCardData(int lastLimit) {
331 String[] whereArgs = {
332 Integer.toString(lastLimit)
334 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
336 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC", null);
337 ArrayList<NfcCardData> nfcCardDatas = new ArrayList<NfcCardData>();
338 if(resultSet.moveToFirst()) {
340 NfcCardData nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getInt(2), resultSet.getInt(3));
341 nfcCardDatas.add(nfcCardData);
342 } while (resultSet.moveToNext());
345 NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
346 resultsArr = nfcCardDatas.toArray(resultsArr);
350 public NfcCardData getLatestNfcCardData(int cardId) {
353 String[] whereArgs = {
354 Integer.toString(cardId)
356 Cursor resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore WHERE CardId = ? ORDER BY UpdateTime DESC LIMIT 1", whereArgs);
357 NfcCardData nfcCardData = null;
358 if(resultSet.moveToFirst()) {
359 nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getInt(2), resultSet.getInt(3));
365 public void updateCourseCalendar(CourseEvent event, Component cevent) {
367 if(vorlesungsplanDBHelper == null)
368 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
369 vorlesungsplanDBHelper.updateCourseCalendar(event, cevent);
372 public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
374 if(vorlesungsplanDBHelper == null)
375 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
376 return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
379 public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
381 if(vorlesungsplanDBHelper == null)
382 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
383 return vorlesungsplanDBHelper.getCourseEventsByGroup(group);
386 public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
388 if(vorlesungsplanDBHelper == null)
389 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
390 return vorlesungsplanDBHelper.getCourseCalendarTimetable(coursename, timeFrom, days);
393 public CourseGroup getCourseGroup(int courseGroupId) {
395 if(vorlesungsplanDBHelper == null)
396 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
397 return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
400 public CourseGroup getCourseGroup(String coursename, String groupname) {
402 if(vorlesungsplanDBHelper == null)
403 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
404 return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
407 public CourseGroup addCourseGroup(String coursename, String groupname) {
409 if(vorlesungsplanDBHelper == null)
410 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
411 return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
414 public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
416 if(vorlesungsplanDBHelper == null)
417 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
418 return vorlesungsplanDBHelper.getCourseGroups(coursename, notBefore);
421 public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
423 if(vorlesungsplanDBHelper == null)
424 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
425 return vorlesungsplanDBHelper.getCourseExamEvents(coursename, timeFrom, timeTo);
428 public void updateMensaTagesplan(MensaTagesplan plan) {
430 if(mensaplanDBHelper == null)
431 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
432 mensaplanDBHelper.updateMensaTagesplan(plan);
435 public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
437 if(mensaplanDBHelper == null)
438 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
439 return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
442 public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
444 if(mensaplanDBHelper == null)
445 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
446 return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
449 public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
451 if(mensaplanDBHelper == null)
452 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
453 return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
456 public void updateNewsItem(NewsItem news) {
458 if(newsDBHelper == null)
459 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
460 newsDBHelper.updateNewsItem(news);
463 public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
465 if(newsDBHelper == null)
466 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
467 return newsDBHelper.getNewsItems(source, timeFrom, timeTo);