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