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

   1 package de.dhbwloe.campusapp.database;
   2
   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;
   8
   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;
  16
  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;
  22
  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;
  29
  30 /**
  31  * Created by pk910 on 19.01.2016.
  32  */
  33 public class DatabaseManager {
  34     private static final String DATABASE_NAME = "DHBWLoe.CampusApp.db";
  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;
  41
  42
  43     public DatabaseManager(CampusAppContext context) {
  44         AppContext = context;
  45     }
  46
  47     private void openDatabase() {
  48         if(database != null)
  49             return;
  50
  51         database = AppContext.getMainActivity().openOrCreateDatabase(DATABASE_NAME, Activity.MODE_PRIVATE, null);
  52     }
  53
  54     public void initializeDatabase() {
  55         openDatabase();
  56         database.execSQL("CREATE TABLE IF NOT EXISTS Version(Version INT);");
  57
  58         Cursor resultSet = database.rawQuery("Select * from Version", null);
  59         int version;
  60         if(resultSet.moveToFirst()) {
  61             version = resultSet.getInt(0);
  62         } else {
  63             version = 0;
  64             database.execSQL("INSERT INTO Version (Version) VALUES (0);");
  65         }
  66
  67         resultSet.close();
  68         if(version < DATABASE_VERSION)
  69             upgradeTables(version, DATABASE_VERSION);
  70
  71         //database.execSQL("DELETE FROM CourseCalendar");
  72         //database.execSQL("DELETE FROM CourseCalendarEvent");
  73     }
  74
  75     private void upgradeTables(int oldVersion, int newVersion) {
  76         if(oldVersion == 0 && newVersion > 0) {
  77             database.execSQL("CREATE TABLE IF NOT EXISTS RuntimeCache " +
  78                     "(" +
  79                     "Reference TEXT, " +
  80                     "Value TEXT, " +
  81                     "LastUpdate INT, " +
  82                     "PRIMARY KEY (Reference)" +
  83                     ");");
  84             database.execSQL("CREATE TABLE IF NOT EXISTS SearchIndex " +
  85                     "(" +
  86                     "KeyName TEXT, " +
  87                     "SearchText TEXT, " +
  88                     "SearchTitle TEXT," +
  89                     "Description TEXT, " +
  90                     "StaticEntry INT, " +
  91                     "UpdateTime INT, " +
  92                     "TargetPage TEXT);");
  93             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendar " +
  94                     "(" +
  95                     "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
  96                     "CourseName TEXT, " +
  97                     "UniqueId TEXT, " +
  98                     "SequenceId INT, " +
  99                     "EventFrom INT, " +
 100                     "EventTo INT, " +
 101                     "EventTitle TEXT, " +
 102                     "EventLocation TEXT, " +
 103                     "EventStatus TEXT," +
 104                     "RecurRule TEXT," +
 105                     "ExcludeDates TEXT," +
 106                     "CourseGroupId INT," +
 107                     "UNIQUE (CourseName, UniqueId)" +
 108                     ");");
 109             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarEvent " +
 110                     "(" +
 111                     "EventId INT, " +
 112                     "EventFrom INT, " +
 113                     "EventTo INT, " +
 114                     "PRIMARY KEY (EventId, EventFrom, EventTo)" +
 115                     ");");
 116             database.execSQL("CREATE INDEX CourseCalendarEventIdx ON CourseCalendarEvent (EventFrom, EventTo);");
 117             database.execSQL("CREATE TABLE IF NOT EXISTS NfcCardStore " +
 118                     "(" +
 119                     "CardId INT, " +
 120                     "UpdateTime INT," +
 121                     "CardBalance INT, " +
 122                     "CardLastTransaction INT, " +
 123                     "PRIMARY KEY (CardId, UpdateTime)" +
 124                     ");");
 125             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
 126                     "(" +
 127                     "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 128                     "CourseName INT," +
 129                     "GroupName TEXT, " +
 130                     "LastUpdate INT, " +
 131                     "UNIQUE (GroupName)" +
 132                     ");");
 133             database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
 134             database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
 135                     "(" +
 136                     "PlanDate INT, " +
 137                     "MenuName TEXT, " +
 138                     "ChkSum INT, " +
 139                     "Name TEXT, " +
 140                     "NameHtml TEXT, " +
 141                     "Additional TEXT, " +
 142                     "Notes TEXT, " +
 143                     "PriceStudents INT, " +
 144                     "PriceEmployees INT, " +
 145                     "PriceGuests INT, " +
 146                     "PriceSchool INT, " +
 147                     "PRIMARY KEY (PlanDate, MenuName)" +
 148                     ");");
 149             database.execSQL("CREATE TABLE IF NOT EXISTS News " +
 150                     "(" +
 151                     "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 152                     "Source TEXT, " +
 153                     "Time INT, " +
 154                     "UniqueId TEXT, " +
 155                     "ChkSum INT, " +
 156                     "Title TEXT, " +
 157                     "Summary TEXT, " +
 158                     "Content TEXT, " +
 159                     "Link TEXT, " +
 160                     "Categories TEXT, " +
 161                     "UNIQUE(Source, UniqueId) " +
 162                     ");");
 163             database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
 164         }
 165         if(oldVersion < 2 && newVersion >= 2) {
 166             // Version 2
 167             database.execSQL("ALTER TABLE CourseCalendarEvent ADD EventType INT;");
 168         }
 169         if(oldVersion < 3 && newVersion >= 3) {
 170             // Version 3
 171
 172         }
 173
 174         database.execSQL("UPDATE Version SET Version = " + Integer.toString(newVersion));
 175     }
 176
 177     public void addSearchIndices(SearchIndices[] indices) {
 178         openDatabase();
 179         for(int i = 0; i < indices.length; i++) {
 180             String[] whereArgs = new String[] {
 181                     indices[i].getKeyName()
 182             };
 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()) {
 187                     // remove
 188                     database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
 189                 } else
 190                     continue;
 191             }
 192             resultSet.close();
 193             // add new
 194             try {
 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());
 203
 204                 database.insertOrThrow("SearchIndex", null, indexValues);
 205             } catch(Exception e) {
 206                 e.printStackTrace();
 207             }
 208         }
 209     }
 210
 211     public SearchIndices[] performSearchRequest(String query, int maxResults) {
 212         openDatabase();
 213         String[] whereArgs = new String[] {
 214                 "%" + query + "%"
 215         };
 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")
 227             };
 228             do {
 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);
 237         }
 238         resultSet.close();
 239
 240         SearchIndices[] indicesArr = new SearchIndices[indices.size()];
 241         indicesArr = indices.toArray(indicesArr);
 242         return indicesArr;
 243     }
 244
 245     public void setRuntimeCache(String name, String value) {
 246         openDatabase();
 247         long now = (new Date()).getTime() / 1000;
 248         String[] whereArgs = new String[] {
 249                 name
 250         };
 251         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 252         if(resultSet.moveToFirst()) {
 253             if(resultSet.getString(0).equalsIgnoreCase(value))
 254                 return;
 255             try {
 256                 ContentValues updateValues = new ContentValues();
 257                 updateValues.put("Value", value);
 258                 updateValues.put("LastUpdate", now);
 259
 260                 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
 261             } catch(Exception e) {
 262                 e.printStackTrace();
 263             }
 264         } else {
 265             try {
 266                 ContentValues indexValues = new ContentValues();
 267                 indexValues.put("Reference", name);
 268                 indexValues.put("Value", value);
 269                 indexValues.put("LastUpdate", now);
 270
 271                 database.insertOrThrow("RuntimeCache", null, indexValues);
 272             } catch(Exception e) {
 273                 e.printStackTrace();
 274             }
 275         }
 276         resultSet.close();
 277     }
 278
 279     public String getRuntimeCache(String name) {
 280         openDatabase();
 281         String value = null;
 282         String[] whereArgs = new String[] {
 283                 name
 284         };
 285         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 286         if(resultSet.moveToFirst()) {
 287             value = resultSet.getString(0);
 288         }
 289         resultSet.close();
 290         return value;
 291     }
 292
 293     public void addNfcCardData(NfcCardData nfcCardData) {
 294         openDatabase();
 295         String[] whereArgs = new String[] {
 296                 Integer.toString(nfcCardData.getUniqueId()),
 297                 Long.toString(nfcCardData.getLastUpdate())
 298         };
 299         Cursor resultSet = database.rawQuery("SELECT CardBalance FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
 300         if(resultSet.moveToFirst()) {
 301             try {
 302                 ContentValues updateValues = new ContentValues();
 303                 updateValues.put("CardBalance", nfcCardData.getBalance());
 304                 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
 305
 306                 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
 307             } catch(Exception e) {
 308                 e.printStackTrace();
 309             }
 310         } else {
 311             try {
 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());
 317
 318                 database.insertOrThrow("NfcCardStore", null, indexValues);
 319             } catch(Exception e) {
 320                 e.printStackTrace();
 321             }
 322         }
 323         resultSet.close();
 324     }
 325
 326     public NfcCardData[] getNfcCardData(int lastLimit) {
 327         openDatabase();
 328         String value = null;
 329         Cursor resultSet;
 330         if(lastLimit > 0) {
 331             String[] whereArgs = {
 332                     Integer.toString(lastLimit)
 333             };
 334             resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
 335         } else
 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()) {
 339             do {
 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());
 343         }
 344         resultSet.close();
 345         NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
 346         resultsArr = nfcCardDatas.toArray(resultsArr);
 347         return resultsArr;
 348     }
 349
 350     public NfcCardData getLatestNfcCardData(int cardId) {
 351         openDatabase();
 352         String value = null;
 353         String[] whereArgs = {
 354                 Integer.toString(cardId)
 355         };
 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));
 360         }
 361         resultSet.close();
 362         return nfcCardData;
 363     }
 364
 365     public void updateCourseCalendar(CourseEvent event, Component cevent) {
 366         openDatabase();
 367         if(vorlesungsplanDBHelper == null)
 368             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 369         vorlesungsplanDBHelper.updateCourseCalendar(event, cevent);
 370     }
 371
 372     public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
 373         openDatabase();
 374         if(vorlesungsplanDBHelper == null)
 375             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 376         return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
 377     }
 378
 379     public CourseEvent[] getCourseEventsByGroup(CourseGroup group) {
 380         openDatabase();
 381         if(vorlesungsplanDBHelper == null)
 382             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 383         return vorlesungsplanDBHelper.getCourseEventsByGroup(group);
 384     }
 385
 386     public CourseEvent[] getCourseCalendarTimetable(String coursename, long timeFrom, int days) {
 387         openDatabase();
 388         if(vorlesungsplanDBHelper == null)
 389             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 390         return vorlesungsplanDBHelper.getCourseCalendarTimetable(coursename, timeFrom, days);
 391     }
 392
 393     public CourseGroup getCourseGroup(int courseGroupId) {
 394         openDatabase();
 395         if(vorlesungsplanDBHelper == null)
 396             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 397         return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
 398     }
 399
 400     public CourseGroup getCourseGroup(String coursename, String groupname) {
 401         openDatabase();
 402         if(vorlesungsplanDBHelper == null)
 403             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 404         return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
 405     }
 406
 407     public CourseGroup addCourseGroup(String coursename, String groupname) {
 408         openDatabase();
 409         if(vorlesungsplanDBHelper == null)
 410             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 411         return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
 412     }
 413
 414     public CourseGroup[] getCourseGroups(String coursename, Date notBefore) {
 415         openDatabase();
 416         if(vorlesungsplanDBHelper == null)
 417             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 418         return vorlesungsplanDBHelper.getCourseGroups(coursename, notBefore);
 419     }
 420
 421     public CourseEvent[] getCourseExamEvents(String coursename, long timeFrom, long timeTo) {
 422         openDatabase();
 423         if(vorlesungsplanDBHelper == null)
 424             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 425         return vorlesungsplanDBHelper.getCourseExamEvents(coursename, timeFrom, timeTo);
 426     }
 427
 428     public void updateMensaTagesplan(MensaTagesplan plan) {
 429         openDatabase();
 430         if(mensaplanDBHelper == null)
 431             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 432         mensaplanDBHelper.updateMensaTagesplan(plan);
 433     }
 434
 435     public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
 436         openDatabase();
 437         if(mensaplanDBHelper == null)
 438             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 439         return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
 440     }
 441
 442     public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
 443         openDatabase();
 444         if(mensaplanDBHelper == null)
 445             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 446         return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
 447     }
 448
 449     public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
 450         openDatabase();
 451         if(mensaplanDBHelper == null)
 452             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 453         return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
 454     }
 455
 456     public void updateNewsItem(NewsItem news) {
 457         openDatabase();
 458         if(newsDBHelper == null)
 459             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 460         newsDBHelper.updateNewsItem(news);
 461     }
 462
 463     public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
 464         openDatabase();
 465         if(newsDBHelper == null)
 466             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 467         return newsDBHelper.getNewsItems(source, timeFrom, timeTo);
 468     }
 469
 470 }