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.DateList;
10 import net.fortuna.ical4j.model.DateTime;
11 import net.fortuna.ical4j.model.Period;
12 import net.fortuna.ical4j.model.Recur;
13 import net.fortuna.ical4j.model.parameter.Value;
14 import net.fortuna.ical4j.model.property.RRule;
16 import java.lang.reflect.Array;
17 import java.text.ParseException;
18 import java.util.ArrayList;
19 import java.util.Date;
20 import java.util.ListIterator;
22 import de.dhbwloe.campusapp.CampusAppContext;
23 import de.dhbwloe.campusapp.mensaplan.MensaTagesplan;
24 import de.dhbwloe.campusapp.news.NewsItem;
25 import de.dhbwloe.campusapp.search.SearchIndices;
26 import de.dhbwloe.campusapp.vorlesungen.CourseEvent;
27 import de.dhbwloe.campusapp.vorlesungen.CourseGroup;
30 * Created by pk910 on 19.01.2016.
32 public class DatabaseManager {
33 private static final String DATABASE_NAME = "DHBWLoe.CampusApp.DEV";
34 private static final int DATABASE_VERSION = 1;
35 private CampusAppContext AppContext;
36 private SQLiteDatabase database;
37 private NewsDatabaseHelper newsDBHelper;
38 private MensaplanDatabaseHelper mensaplanDBHelper;
39 private VorlesungsplanDatabaseHelper vorlesungsplanDBHelper;
42 public DatabaseManager(CampusAppContext context) {
46 public void initializeDatabase() {
47 database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
48 database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
50 Cursor resultSet = database.rawQuery("Select * from Version", null);
52 if(resultSet.moveToFirst()) {
53 version = resultSet.getInt(0);
56 database.execSQL("INSERT INTO Version (Version) VALUES (0);");
60 if(version < DATABASE_VERSION)
61 upgradeTables(version, DATABASE_VERSION);
66 private void upgradeTables(int oldVersion, int newVersion) {
67 if(oldVersion == 0 && newVersion > 0) {
68 database.execSQL("CREATE TABLE IF NOT EXISTS RuntimeCache " +
73 "PRIMARY KEY (Reference)" +
75 database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
80 "Description TEXT, " +
84 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
86 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
93 "EventLocation TEXT, " +
96 "ExcludeDates TEXT," +
97 "CourseGroupId INT," +
98 "UNIQUE (CourseName, UniqueId)" +
100 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
105 "PRIMARY KEY (EventId, EventFrom, EventTo)" +
107 database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
108 database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
112 "CardBalance INT, " +
113 "CardLastTransaction INT, " +
114 "PRIMARY KEY (CardId, UpdateTime)" +
116 database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
118 "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
122 "UNIQUE (GroupName)" +
124 database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
125 database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
132 "Additional TEXT, " +
134 "PriceStudents INT, " +
135 "PriceEmployees INT, " +
136 "PriceGuests INT, " +
137 "PriceSchool INT, " +
138 "PRIMARY KEY (PlanDate, MenuName)" +
140 database.execSQL("CREATE TABLE IF NOT EXISTS News " +
142 "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
151 "Categories TEXT, " +
152 "UNIQUE(Source, UniqueId) " +
154 database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
156 if(oldVersion < 2 && newVersion >= 2) {
161 database.execSQL("UPDATE Version SET Version = "+Integer.toString(newVersion));
164 public void addSearchIndices(SearchIndices[] indices) {
165 for(int i = 0; i < indices.length; i++) {
166 String[] whereArgs = new String[] {
167 indices[i].getKeyName()
169 Cursor resultSet = database.rawQuery("SELECT UpdateTime FROM SearchIndex WHERE KeyName = ?", whereArgs);
170 if(resultSet.moveToFirst()) {
171 long updateTime = resultSet.getLong(0);
172 if (updateTime < indices[i].getUpdateTime()) {
174 database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
181 ContentValues indexValues = new ContentValues();
182 indexValues.put("KeyName", indices[i].getKeyName());
183 indexValues.put("SearchText", indices[i].getKeyWords());
184 indexValues.put("SearchTitle", indices[i].getTitle());
185 indexValues.put("Description", indices[i].getDescription());
186 indexValues.put("StaticEntry", indices[i].getIsStatic() ? 1 : 0);
187 indexValues.put("UpdateTime", indices[i].getUpdateTime());
188 indexValues.put("TargetPage", indices[i].getTarget());
190 database.insertOrThrow("SearchIndex", null, indexValues);
191 } catch(Exception e) {
197 public SearchIndices[] performSearchRequest(String query, int maxResults) {
198 String[] whereArgs = new String[] {
201 Cursor resultSet = database.rawQuery("SELECT KeyName, SearchTitle, Description, StaticEntry, UpdateTime, TargetPage, SearchText FROM SearchIndex WHERE SearchText LIKE ? ORDER BY StaticEntry DESC, UpdateTime DESC", whereArgs);
202 ArrayList<SearchIndices> indices = new ArrayList<SearchIndices>();
203 if(resultSet.moveToFirst()) {
204 int[] columnIndexes = {
205 resultSet.getColumnIndex("KeyName"),
206 resultSet.getColumnIndex("SearchTitle"),
207 resultSet.getColumnIndex("Description"),
208 resultSet.getColumnIndex("StaticEntry"),
209 resultSet.getColumnIndex("UpdateTime"),
210 resultSet.getColumnIndex("TargetPage"),
211 resultSet.getColumnIndex("SearchText")
214 SearchIndices cIndices = new SearchIndices(resultSet.getString(columnIndexes[0]), (resultSet.getInt(columnIndexes[3]) == 1));
215 cIndices.setUpdateTime(resultSet.getLong(columnIndexes[4]));
216 cIndices.setTarget(resultSet.getString(columnIndexes[5]));
217 cIndices.addKeyWord(resultSet.getString(columnIndexes[6]));
218 cIndices.setTitle(resultSet.getString(columnIndexes[1]));
219 cIndices.setDescription(resultSet.getString(columnIndexes[2]));
220 indices.add(cIndices);
221 } while (resultSet.moveToNext() && indices.size() < maxResults);
225 SearchIndices[] indicesArr = new SearchIndices[indices.size()];
226 indicesArr = indices.toArray(indicesArr);
230 public void setRuntimeCache(String name, String value) {
231 long now = (new Date()).getTime() / 1000;
232 String[] whereArgs = new String[] {
235 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
236 if(resultSet.moveToFirst()) {
237 if(resultSet.getString(0).equalsIgnoreCase(value))
240 ContentValues updateValues = new ContentValues();
241 updateValues.put("Value", value);
242 updateValues.put("LastUpdate", now);
244 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
245 } catch(Exception e) {
250 ContentValues indexValues = new ContentValues();
251 indexValues.put("Reference", name);
252 indexValues.put("Value", value);
253 indexValues.put("LastUpdate", now);
255 database.insertOrThrow("RuntimeCache", null, indexValues);
256 } catch(Exception e) {
263 public String getRuntimeCache(String name) {
265 String[] whereArgs = new String[] {
268 Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
269 if(resultSet.moveToFirst()) {
270 value = resultSet.getString(0);
276 public void addNfcCardData(NfcCardData nfcCardData) {
277 String[] whereArgs = new String[] {
278 Integer.toString(nfcCardData.getUniqueId()),
279 Long.toString(nfcCardData.getLastUpdate())
281 Cursor resultSet = database.rawQuery("SELECT CardBalance FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
282 if(resultSet.moveToFirst()) {
284 ContentValues updateValues = new ContentValues();
285 updateValues.put("CardBalance", nfcCardData.getBalance());
286 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
288 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
289 } catch(Exception e) {
294 ContentValues indexValues = new ContentValues();
295 indexValues.put("CardId", nfcCardData.getUniqueId());
296 indexValues.put("UpdateTime", nfcCardData.getLastUpdate());
297 indexValues.put("CardBalance", nfcCardData.getBalance());
298 indexValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
300 database.insertOrThrow("NfcCardStore", null, indexValues);
301 } catch(Exception e) {
308 public NfcCardData[] getNfcCardData(int lastLimit) {
312 String[] whereArgs = {
313 Integer.toString(lastLimit)
315 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
317 resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC", null);
318 ArrayList<NfcCardData> nfcCardDatas = new ArrayList<NfcCardData>();
319 if(resultSet.moveToFirst()) {
321 NfcCardData nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getInt(2), resultSet.getInt(3));
322 nfcCardDatas.add(nfcCardData);
323 } while (resultSet.moveToNext());
326 NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
327 resultsArr = nfcCardDatas.toArray(resultsArr);
331 public void updateCourseCalendar(CourseEvent event) {
332 if(vorlesungsplanDBHelper == null)
333 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
334 vorlesungsplanDBHelper.updateCourseCalendar(event);
337 public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
338 if(vorlesungsplanDBHelper == null)
339 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
340 return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
343 public CourseGroup getCourseGroup(int courseGroupId) {
344 if(vorlesungsplanDBHelper == null)
345 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
346 return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
349 public CourseGroup getCourseGroup(String coursename, String groupname) {
350 if(vorlesungsplanDBHelper == null)
351 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
352 return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
355 public CourseGroup addCourseGroup(String coursename, String groupname) {
356 if(vorlesungsplanDBHelper == null)
357 vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
358 return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
361 public void updateMensaTagesplan(MensaTagesplan plan) {
362 if(mensaplanDBHelper == null)
363 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
364 mensaplanDBHelper.updateMensaTagesplan(plan);
367 public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
368 if(mensaplanDBHelper == null)
369 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
370 return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
373 public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
374 if(mensaplanDBHelper == null)
375 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
376 return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
379 public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
380 if(mensaplanDBHelper == null)
381 mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
382 return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
385 public void updateNewsItem(NewsItem news) {
386 if(newsDBHelper == null)
387 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
388 newsDBHelper.updateNewsItem(news);
391 public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
392 if(newsDBHelper == null)
393 newsDBHelper = new NewsDatabaseHelper(AppContext, database);
394 return newsDBHelper.getNewsItems(source, timeFrom, timeTo);