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