1 /* DatabaseManager.java
3 * This program is free software: you can redistribute it and/or modify
4 * it under the terms of the GNU General Public License as published by
5 * the Free Software Foundation, either version 3 of the License, or
6 * (at your option) any later version.
8 * This program is distributed in the hope that it will be useful,
9 * but WITHOUT ANY WARRANTY; without even the implied warranty of
10 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
11 * GNU General Public License for more details.
13 * You should have received a copy of the GNU General Public License
14 * along with this program. If not, see <http://www.gnu.org/licenses/>.
16 package de.dhbwloe.campusapp.database;
17 import android.app.Activity;
18 import android.content.ContentValues;
19 import android.database.Cursor;
20 import android.database.sqlite.SQLiteDatabase;
21 import android.util.Log;
23 import net.fortuna.ical4j.model.Component;
24 import net.fortuna.ical4j.model.DateList;
25 import net.fortuna.ical4j.model.DateTime;
26 import net.fortuna.ical4j.model.Period;
27 import net.fortuna.ical4j.model.Recur;
28 import net.fortuna.ical4j.model.parameter.Value;
29 import net.fortuna.ical4j.model.property.RRule;
31 import java.lang.reflect.Array;
32 import java.text.ParseException;
33 import java.util.ArrayList;
34 import java.util.Date;
35 import java.util.ListIterator;
37 import de.dhbwloe.campusapp.CampusAppContext;
38 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
39 import de.dhbwloe.campusapp.news.NewsItem;
40 import de.dhbwloe.campusapp.search.SearchIndices;
41 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
42 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
45 * Created by pk910 on 19.01.2016.
47 public class DatabaseManager {
48 private static final String DATABASE_NAME = "DHBWLoe.CampusApp.db";
49 private static final int DATABASE_VERSION = 2; // Datenbank Version - muss bei strukturellen Anpassungen erhöht werden
51 private CampusAppContext AppContext;
52 private SQLiteDatabase database;
53 private NewsDatabaseHelper newsDBHelper;
54 private MensaplanDatabaseHelper mensaplanDBHelper;
55 private VorlesungsplanDatabaseHelper vorlesungsplanDBHelper;
58 public DatabaseManager(CampusAppContext context) {
62 private void openDatabase() {
66 database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
69 public void initializeDatabase() {
71 database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
73 // Abfragen der Datenbank Version
74 Cursor resultSet = database.rawQuery("Select * from Version", null);
76 if(resultSet.moveToFirst()) {
77 version = resultSet.getInt(0);
80 database.execSQL("INSERT INTO Version (Version) VALUES (0);");
84 if(version < DATABASE_VERSION) // Upgrade der Datenbank Struktur (oder auch Initialisierung)
85 upgradeTables(version, DATABASE_VERSION);
88 private void upgradeTables(int oldVersion, int newVersion) {
89 if(oldVersion == 0 && newVersion > 0) { // Initialisierung (noch keine Datenbank)
90 database.execSQL("CREATE TABLE IF NOT EXISTS RuntimeCache " +
95 "PRIMARY KEY (Reference)" +
97 database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
100 "SearchText TEXT, " +
101 "SearchTitle TEXT," +
102 "Description TEXT, " +
103 "StaticEntry INT, " +
105 "TargetPage TEXT);");
108 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
110 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
111 "CourseName TEXT, " +
116 "EventTitle TEXT, " +
117 "EventLocation TEXT, " +
118 "EventStatus TEXT," +
120 "ExcludeDates TEXT," +
121 "CourseGroupId INT," +
122 "UNIQUE (CourseName, UniqueId)" +
124 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
129 "PRIMARY KEY (EventId, EventFrom, EventTo)" +
131 database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
132 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
134 "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
138 "UNIQUE (GroupName)" +
140 database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
142 // Mensa Karten History is this actually used?)
143 database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
147 "CardBalance INT, " +
148 "CardLastTransaction INT, " +
149 "PRIMARY KEY (CardId, UpdateTime)" +
153 database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
160 "Additional TEXT, " +
162 "PriceStudents INT, " +
163 "PriceEmployees INT, " +
164 "PriceGuests INT, " +
165 "PriceSchool INT, " +
166 "PRIMARY KEY (PlanDate, MenuName)" +
170 database.execSQL("CREATE TABLE IF NOT EXISTS News " +
172 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
181 "Categories TEXT, " +
182 "UNIQUE(Source, UniqueId) " +
184 database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
186 if(oldVersion < 2 && newVersion >= 2) {
188 database.execSQL("ALTER TABLE CourseCalendarEvent ADD EventType INT;");
190 if(oldVersion < 3 && newVersion >= 3) {
195 database.execSQL("UPDATE Version SET Version = " + Integer.toString(newVersion));
198 public void addSearchIndices(SearchIndices[] indices) {
199 // Hinzufügen oder Updaten mehrerer SearchIndice Objekte
201 for(int i = 0; i < indices.length; i++) {
202 String[] whereArgs = new String[] {
203 indices[i].getKeyName()
205 Cursor resultSet = database.rawQuery("SELECT UpdateTime FROM SearchIndex WHERE KeyName = ?", whereArgs);
206 if(resultSet.moveToFirst()) {
207 long updateTime = resultSet.getLong(0);
208 if (updateTime < indices[i].getUpdateTime()) {
210 database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
217 ContentValues indexValues = new ContentValues();
218 indexValues.put("KeyName", indices[i].getKeyName());
219 indexValues.put("SearchText", indices[i].getKeyWords());
220 indexValues.put("SearchTitle", indices[i].getTitle());
221 indexValues.put("Description", indices[i].getDescription());
222 indexValues.put("StaticEntry", indices[i].getIsStatic() ? 1 : 0);
223 indexValues.put("UpdateTime", indices[i].getUpdateTime());
224 indexValues.put("TargetPage", indices[i].getTarget());
226 database.insertOrThrow("SearchIndex", null, indexValues);
227 } catch(Exception e) {
233 public SearchIndices[] performSearchRequest(String query, int maxResults) {
236 String[] whereArgs = new String[] {
239 Cursor resultSet = database.rawQuery("SELECT KeyName, SearchTitle, Description, StaticEntry, UpdateTime, TargetPage, SearchText FROM SearchIndex WHERE SearchText LIKE ? ORDER BY StaticEntry DESC, UpdateTime DESC", whereArgs);
240 ArrayList<SearchIndices> indices = new ArrayList<SearchIndices>();
241 if(resultSet.moveToFirst()) {
242 int[] columnIndexes = {
243 resultSet.getColumnIndex("KeyName"),
244 resultSet.getColumnIndex("SearchTitle"),
245 resultSet.getColumnIndex("Description"),
246 resultSet.getColumnIndex("StaticEntry"),
247 resultSet.getColumnIndex("UpdateTime"),
248 resultSet.getColumnIndex("TargetPage"),
249 resultSet.getColumnIndex("SearchText")
252 SearchIndices cIndices = new SearchIndices(resultSet.getString(columnIndexes[0]), (resultSet.getInt(columnIndexes[3]) == 1));
253 cIndices.setUpdateTime(resultSet.getLong(columnIndexes[4]));
254 cIndices.setTarget(resultSet.getString(columnIndexes[5]));
255 cIndices.addKeyWord(resultSet.getString(columnIndexes[6]));
256 cIndices.setTitle(resultSet.getString(columnIndexes[1]));
257 cIndices.setDescription(resultSet.getString(columnIndexes[2]));
258 indices.add(cIndices);
259 } while (resultSet.moveToNext() && indices.size() < maxResults);
263 SearchIndices[] indicesArr = new SearchIndices[indices.size()];
264 indicesArr = indices.toArray(indicesArr);
268 public void setRuntimeCache(String name, String value) {
269 // Simple cache for runtime options
271 long now = (new Date()).getTime() / 1000;
272 String[] whereArgs = new String[] {
275 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
276 if(resultSet.moveToFirst()) {
277 if(resultSet.getString(0).equalsIgnoreCase(value))
280 ContentValues updateValues = new ContentValues();
281 updateValues.put("Value", value);
282 updateValues.put("LastUpdate", now);
284 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
285 } catch(Exception e) {
290 ContentValues indexValues = new ContentValues();
291 indexValues.put("Reference", name);
292 indexValues.put("Value", value);
293 indexValues.put("LastUpdate", now);
295 database.insertOrThrow("RuntimeCache", null, indexValues);
296 } catch(Exception e) {
303 public String getRuntimeCache(String name) {
306 String[] whereArgs = new String[] {
309 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
310 if(resultSet.moveToFirst()) {
311 value = resultSet.getString(0);
317 public void addNfcCardData(NfcCardData nfcCardData) {
319 String[] whereArgs = new String[] {
320 Integer.toString(nfcCardData.getUniqueId()),
321 Long.toString(nfcCardData.getLastUpdate())
323 Cursor resultSet = database.rawQuery("SELECT CardBalance FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
324 if(resultSet.moveToFirst()) {
326 ContentValues updateValues = new ContentValues();
327 updateValues.put("CardBalance", nfcCardData.getBalance());
328 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
330 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
331 } catch(Exception e) {
336 ContentValues indexValues = new ContentValues();
337 indexValues.put("CardId", nfcCardData.getUniqueId());
338 indexValues.put("UpdateTime", nfcCardData.getLastUpdate());
339 indexValues.put("CardBalance", nfcCardData.getBalance());
340 indexValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
342 database.insertOrThrow("NfcCardStore", null, indexValues);
343 } catch(Exception e) {
350 public NfcCardData[] getNfcCardData(int lastLimit) {
355 String[] whereArgs = {
356 Integer.toString(lastLimit)
358 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
360 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC", null);
361 ArrayList<NfcCardData> nfcCardDatas = new ArrayList<NfcCardData>();
362 if(resultSet.moveToFirst()) {
364 NfcCardData nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getInt(2), resultSet.getInt(3));
365 nfcCardDatas.add(nfcCardData);
366 } while (resultSet.moveToNext());
369 NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
370 resultsArr = nfcCardDatas.toArray(resultsArr);
374 public NfcCardData getLatestNfcCardData(int cardId) {
377 String[] whereArgs = {
378 Integer.toString(cardId)
380 Cursor resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore WHERE CardId = ? ORDER BY UpdateTime DESC LIMIT 1", whereArgs);
381 NfcCardData nfcCardData = null;
382 if(resultSet.moveToFirst()) {
383 nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getInt(2), resultSet.getInt(3));
389 public void updateCourseCalendar(CourseEvent event, Component cevent) {
391 if(vorlesungsplanDBHelper == null)
392 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
393 vorlesungsplanDBHelper.updateCourseCalendar(event, cevent);
396 public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
398 if(vorlesungsplanDBHelper == null)
399 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
400 return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
403 public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
405 if(vorlesungsplanDBHelper == null)
406 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
407 return vorlesungsplanDBHelper.getCourseEventsByGroup(group);
410 public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
412 if(vorlesungsplanDBHelper == null)
413 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
414 return vorlesungsplanDBHelper.getCourseCalendarTimetable(coursename, timeFrom, days);
417 public CourseGroup getCourseGroup(int courseGroupId) {
419 if(vorlesungsplanDBHelper == null)
420 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
421 return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
424 public CourseGroup getCourseGroup(String coursename, String groupname) {
426 if(vorlesungsplanDBHelper == null)
427 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
428 return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
431 public CourseGroup addCourseGroup(String coursename, String groupname) {
433 if(vorlesungsplanDBHelper == null)
434 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
435 return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
438 public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
440 if(vorlesungsplanDBHelper == null)
441 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
442 return vorlesungsplanDBHelper.getCourseGroups(coursename, notBefore);
445 public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
447 if(vorlesungsplanDBHelper == null)
448 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
449 return vorlesungsplanDBHelper.getCourseExamEvents(coursename, timeFrom, timeTo);
452 public void updateMensaTagesplan(MensaTagesplan plan) {
454 if(mensaplanDBHelper == null)
455 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
456 mensaplanDBHelper.updateMensaTagesplan(plan);
459 public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
461 if(mensaplanDBHelper == null)
462 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
463 return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
466 public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
468 if(mensaplanDBHelper == null)
469 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
470 return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
473 public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
475 if(mensaplanDBHelper == null)
476 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
477 return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
480 public void updateNewsItem(NewsItem news) {
482 if(newsDBHelper == null)
483 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
484 newsDBHelper.updateNewsItem(news);
487 public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
489 if(newsDBHelper == null)
490 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
491 return newsDBHelper.getNewsItems(source, timeFrom, timeTo);