app/src/main/java/de/dhbwloe/campusapp/database/DatabaseManager.java

   1 /* DatabaseManager.java
   2  *
   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.
   7  *
   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.
  12  *
  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/>.
  15  */
  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;
  22
  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;
  30
  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;
  36
  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;
  43
  44 /**
  45  * Created by pk910 on 19.01.2016.
  46  */
  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
  50
  51     private CampusAppContext AppContext;
  52     private SQLiteDatabase database;
  53     private NewsDatabaseHelper newsDBHelper;
  54     private MensaplanDatabaseHelper mensaplanDBHelper;
  55     private VorlesungsplanDatabaseHelper vorlesungsplanDBHelper;
  56
  57
  58     public DatabaseManager(CampusAppContext context) {
  59         AppContext = context;
  60     }
  61
  62     private void openDatabase() {
  63         if(database != null)
  64             return;
  65
  66         database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
  67     }
  68
  69     public void initializeDatabase() {
  70         openDatabase();
  71         database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
  72
  73         // Abfragen der Datenbank Version
  74         Cursor resultSet = database.rawQuery("Select * from Version", null);
  75         int version;
  76         if(resultSet.moveToFirst()) {
  77             version = resultSet.getInt(0);
  78         } else {
  79             version = 0;
  80             database.execSQL("INSERT INTO Version (Version) VALUES (0);");
  81         }
  82
  83         resultSet.close();
  84         if(version < DATABASE_VERSION) // Upgrade der Datenbank Struktur (oder auch Initialisierung)
  85             upgradeTables(version, DATABASE_VERSION);
  86     }
  87
  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 " +
  91                     "(" +
  92                     "Reference TEXT, " +
  93                     "Value TEXT, " +
  94                     "LastUpdate INT, " +
  95                     "PRIMARY KEY (Reference)" +
  96                     ");");
  97             database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
  98                     "(" +
  99                     "KeyName TEXT, " +
 100                     "SearchText TEXT, " +
 101                     "SearchTitle TEXT," +
 102                     "Description TEXT, " +
 103                     "StaticEntry INT, " +
 104                     "UpdateTime INT, " +
 105                     "TargetPage TEXT);");
 106
 107             // Vorlesungsplan
 108             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
 109                     "(" +
 110                     "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 111                     "CourseName TEXT, " +
 112                     "UniqueId TEXT, " +
 113                     "SequenceId INT, " +
 114                     "EventFrom INT, " +
 115                     "EventTo INT, " +
 116                     "EventTitle TEXT, " +
 117                     "EventLocation TEXT, " +
 118                     "EventStatus TEXT," +
 119                     "RecurRule TEXT," +
 120                     "ExcludeDates TEXT," +
 121                     "CourseGroupId INT," +
 122                     "UNIQUE (CourseName, UniqueId)" +
 123                     ");");
 124             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
 125                     "(" +
 126                     "EventId INT, " +
 127                     "EventFrom INT, " +
 128                     "EventTo INT, " +
 129                     "PRIMARY KEY (EventId, EventFrom, EventTo)" +
 130                     ");");
 131             database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
 132             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
 133                     "(" +
 134                     "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 135                     "CourseName INT," +
 136                     "GroupName TEXT, " +
 137                     "LastUpdate INT, " +
 138                     "UNIQUE (GroupName)" +
 139                     ");");
 140             database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
 141
 142             // Mensa Karten History is this actually used?)
 143             database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
 144                     "(" +
 145                     "CardId INT, " +
 146                     "UpdateTime INT," +
 147                     "CardBalance INT, " +
 148                     "CardLastTransaction INT, " +
 149                     "PRIMARY KEY (CardId, UpdateTime)" +
 150                     ");");
 151
 152             // Mensaplan
 153             database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
 154                     "(" +
 155                     "PlanDate INT, " +
 156                     "MenuName TEXT, " +
 157                     "ChkSum INT, " +
 158                     "Name TEXT, " +
 159                     "NameHtml TEXT, " +
 160                     "Additional TEXT, " +
 161                     "Notes TEXT, " +
 162                     "PriceStudents INT, " +
 163                     "PriceEmployees INT, " +
 164                     "PriceGuests INT, " +
 165                     "PriceSchool INT, " +
 166                     "PRIMARY KEY (PlanDate, MenuName)" +
 167                     ");");
 168
 169             // News
 170             database.execSQL("CREATE TABLE IF NOT EXISTS News " +
 171                     "(" +
 172                     "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 173                     "Source TEXT, " +
 174                     "Time INT, " +
 175                     "UniqueId TEXT, " +
 176                     "ChkSum INT, " +
 177                     "Title TEXT, " +
 178                     "Summary TEXT, " +
 179                     "Content TEXT, " +
 180                     "Link TEXT, " +
 181                     "Categories TEXT, " +
 182                     "UNIQUE(Source, UniqueId) " +
 183                     ");");
 184             database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
 185         }
 186         if(oldVersion < 2 && newVersion >= 2) {
 187             // Version 2
 188             database.execSQL("ALTER TABLE CourseCalendarEvent ADD EventType INT;");
 189         }
 190         if(oldVersion < 3 && newVersion >= 3) {
 191             // Version 3
 192
 193         }
 194
 195         database.execSQL("UPDATE Version SET Version = " + Integer.toString(newVersion));
 196     }
 197
 198     public void addSearchIndices(SearchIndices[] indices) {
 199         // Hinzufügen oder Updaten mehrerer SearchIndice Objekte
 200         openDatabase();
 201         for(int i = 0; i < indices.length; i++) {
 202             String[] whereArgs = new String[] {
 203                     indices[i].getKeyName()
 204             };
 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()) {
 209                     // remove
 210                     database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
 211                 } else
 212                     continue;
 213             }
 214             resultSet.close();
 215             // add new
 216             try {
 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());
 225
 226                 database.insertOrThrow("SearchIndex", null, indexValues);
 227             } catch(Exception e) {
 228                 e.printStackTrace();
 229             }
 230         }
 231     }
 232
 233     public SearchIndices[] performSearchRequest(String query, int maxResults) {
 234         // App Suche
 235         openDatabase();
 236         String[] whereArgs = new String[] {
 237                 "%" + query + "%"
 238         };
 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")
 250             };
 251             do {
 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);
 260         }
 261         resultSet.close();
 262
 263         SearchIndices[] indicesArr = new SearchIndices[indices.size()];
 264         indicesArr = indices.toArray(indicesArr);
 265         return indicesArr;
 266     }
 267
 268     public void setRuntimeCache(String name, String value) {
 269         // Simple cache for runtime options
 270         openDatabase();
 271         long now = (new Date()).getTime() / 1000;
 272         String[] whereArgs = new String[] {
 273                 name
 274         };
 275         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 276         if(resultSet.moveToFirst()) {
 277             if(resultSet.getString(0).equalsIgnoreCase(value))
 278                 return;
 279             try {
 280                 ContentValues updateValues = new ContentValues();
 281                 updateValues.put("Value", value);
 282                 updateValues.put("LastUpdate", now);
 283
 284                 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
 285             } catch(Exception e) {
 286                 e.printStackTrace();
 287             }
 288         } else {
 289             try {
 290                 ContentValues indexValues = new ContentValues();
 291                 indexValues.put("Reference", name);
 292                 indexValues.put("Value", value);
 293                 indexValues.put("LastUpdate", now);
 294
 295                 database.insertOrThrow("RuntimeCache", null, indexValues);
 296             } catch(Exception e) {
 297                 e.printStackTrace();
 298             }
 299         }
 300         resultSet.close();
 301     }
 302
 303     public String getRuntimeCache(String name) {
 304         openDatabase();
 305         String value = null;
 306         String[] whereArgs = new String[] {
 307                 name
 308         };
 309         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 310         if(resultSet.moveToFirst()) {
 311             value = resultSet.getString(0);
 312         }
 313         resultSet.close();
 314         return value;
 315     }
 316
 317     public void addNfcCardData(NfcCardData nfcCardData) {
 318         openDatabase();
 319         String[] whereArgs = new String[] {
 320                 Integer.toString(nfcCardData.getUniqueId()),
 321                 Long.toString(nfcCardData.getLastUpdate())
 322         };
 323         Cursor resultSet = database.rawQuery("SELECT CardBalance FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
 324         if(resultSet.moveToFirst()) {
 325             try {
 326                 ContentValues updateValues = new ContentValues();
 327                 updateValues.put("CardBalance", nfcCardData.getBalance());
 328                 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
 329
 330                 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
 331             } catch(Exception e) {
 332                 e.printStackTrace();
 333             }
 334         } else {
 335             try {
 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());
 341
 342                 database.insertOrThrow("NfcCardStore", null, indexValues);
 343             } catch(Exception e) {
 344                 e.printStackTrace();
 345             }
 346         }
 347         resultSet.close();
 348     }
 349
 350     public NfcCardData[] getNfcCardData(int lastLimit) {
 351         openDatabase();
 352         String value = null;
 353         Cursor resultSet;
 354         if(lastLimit > 0) {
 355             String[] whereArgs = {
 356                     Integer.toString(lastLimit)
 357             };
 358             resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
 359         } else
 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()) {
 363             do {
 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());
 367         }
 368         resultSet.close();
 369         NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
 370         resultsArr = nfcCardDatas.toArray(resultsArr);
 371         return resultsArr;
 372     }
 373
 374     public NfcCardData getLatestNfcCardData(int cardId) {
 375         openDatabase();
 376         String value = null;
 377         String[] whereArgs = {
 378                 Integer.toString(cardId)
 379         };
 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));
 384         }
 385         resultSet.close();
 386         return nfcCardData;
 387     }
 388
 389     public void updateCourseCalendar(CourseEvent event, Component cevent) {
 390         openDatabase();
 391         if(vorlesungsplanDBHelper == null)
 392             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 393         vorlesungsplanDBHelper.updateCourseCalendar(event, cevent);
 394     }
 395
 396     public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
 397         openDatabase();
 398         if(vorlesungsplanDBHelper == null)
 399             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 400         return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
 401     }
 402
 403     public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
 404         openDatabase();
 405         if(vorlesungsplanDBHelper == null)
 406             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 407         return vorlesungsplanDBHelper.getCourseEventsByGroup(group);
 408     }
 409
 410     public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
 411         openDatabase();
 412         if(vorlesungsplanDBHelper == null)
 413             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 414         return vorlesungsplanDBHelper.getCourseCalendarTimetable(coursename, timeFrom, days);
 415     }
 416
 417     public CourseGroup getCourseGroup(int courseGroupId) {
 418         openDatabase();
 419         if(vorlesungsplanDBHelper == null)
 420             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 421         return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
 422     }
 423
 424     public CourseGroup getCourseGroup(String coursename, String groupname) {
 425         openDatabase();
 426         if(vorlesungsplanDBHelper == null)
 427             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 428         return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
 429     }
 430
 431     public CourseGroup addCourseGroup(String coursename, String groupname) {
 432         openDatabase();
 433         if(vorlesungsplanDBHelper == null)
 434             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 435         return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
 436     }
 437
 438     public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
 439         openDatabase();
 440         if(vorlesungsplanDBHelper == null)
 441             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 442         return vorlesungsplanDBHelper.getCourseGroups(coursename, notBefore);
 443     }
 444
 445     public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
 446         openDatabase();
 447         if(vorlesungsplanDBHelper == null)
 448             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 449         return vorlesungsplanDBHelper.getCourseExamEvents(coursename, timeFrom, timeTo);
 450     }
 451
 452     public void updateMensaTagesplan(MensaTagesplan plan) {
 453         openDatabase();
 454         if(mensaplanDBHelper == null)
 455             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 456         mensaplanDBHelper.updateMensaTagesplan(plan);
 457     }
 458
 459     public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
 460         openDatabase();
 461         if(mensaplanDBHelper == null)
 462             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 463         return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
 464     }
 465
 466     public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
 467         openDatabase();
 468         if(mensaplanDBHelper == null)
 469             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 470         return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
 471     }
 472
 473     public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
 474         openDatabase();
 475         if(mensaplanDBHelper == null)
 476             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 477         return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
 478     }
 479
 480     public void updateNewsItem(NewsItem news) {
 481         openDatabase();
 482         if(newsDBHelper == null)
 483             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 484         newsDBHelper.updateNewsItem(news);
 485     }
 486
 487     public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
 488         openDatabase();
 489         if(newsDBHelper == null)
 490             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 491         return newsDBHelper.getNewsItems(source, timeFrom, timeTo);
 492     }
 493
 494 }