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                     "CardData TEXT, " +
 113                     "CardBalance INT, " +
 114                     "CardLastTransaction INT, " +
 115                     "PRIMARY KEY (CardId, UpdateTime)" +
 116                     ");");
 117             database.execSQL("CREATE TABLE IF NOT EXISTS CourseCalendarGroup " +
 118                     "(" +
 119                     "GroupId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 120                     "CourseName INT," +
 121                     "GroupName TEXT, " +
 122                     "LastUpdate INT, " +
 123                     "UNIQUE (GroupName)" +
 124                     ");");
 125             database.execSQL("CREATE INDEX CourseCalendarGroupIdx ON CourseCalendarGroup (CourseName, GroupName);");
 126             database.execSQL("CREATE TABLE IF NOT EXISTS MensaPlan " +
 127                     "(" +
 128                     "PlanDate INT, " +
 129                     "MenuName TEXT, " +
 130                     "ChkSum INT, " +
 131                     "Name TEXT, " +
 132                     "NameHtml TEXT, " +
 133                     "Additional TEXT, " +
 134                     "Notes TEXT, " +
 135                     "PriceStudents INT, " +
 136                     "PriceEmployees INT, " +
 137                     "PriceGuests INT, " +
 138                     "PriceSchool INT, " +
 139                     "PRIMARY KEY (PlanDate, MenuName)" +
 140                     ");");
 141             database.execSQL("CREATE TABLE IF NOT EXISTS News " +
 142                     "(" +
 143                     "Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " +
 144                     "Source TEXT, " +
 145                     "Time INT, " +
 146                     "UniqueId TEXT, " +
 147                     "ChkSum INT, " +
 148                     "Title TEXT, " +
 149                     "Summary TEXT, " +
 150                     "Content TEXT, " +
 151                     "Link TEXT, " +
 152                     "Categories TEXT, " +
 153                     "UNIQUE(Source, UniqueId) " +
 154                     ");");
 155             database.execSQL("CREATE INDEX NewsIdx ON News (Source, Time);");
 156         }
 157         if(oldVersion < 2 && newVersion >= 2) {
 158             // Version 2
 159
 160         }
 161
 162         database.execSQL("UPDATE Version SET Version = "+Integer.toString(newVersion));
 163     }
 164
 165     public void addSearchIndices(SearchIndices[] indices) {
 166         for(int i = 0; i < indices.length; i++) {
 167             String[] whereArgs = new String[] {
 168                     indices[i].getKeyName()
 169             };
 170             Cursor resultSet = database.rawQuery("SELECT UpdateTime FROM SearchIndex WHERE KeyName = ?", whereArgs);
 171             if(resultSet.moveToFirst()) {
 172                 long updateTime = resultSet.getLong(0);
 173                 if (updateTime < indices[i].getUpdateTime()) {
 174                     // remove
 175                     database.rawQuery("DELETE FROM SearchIndex WHERE KeyName = ?", whereArgs);
 176                 } else
 177                     continue;
 178             }
 179             resultSet.close();
 180             // add new
 181             try {
 182                 ContentValues indexValues = new ContentValues();
 183                 indexValues.put("KeyName", indices[i].getKeyName());
 184                 indexValues.put("SearchText", indices[i].getKeyWords());
 185                 indexValues.put("SearchTitle", indices[i].getTitle());
 186                 indexValues.put("Description", indices[i].getDescription());
 187                 indexValues.put("StaticEntry", indices[i].getIsStatic() ? 1 : 0);
 188                 indexValues.put("UpdateTime", indices[i].getUpdateTime());
 189                 indexValues.put("TargetPage", indices[i].getTarget());
 190
 191                 database.insertOrThrow("SearchIndex", null, indexValues);
 192             } catch(Exception e) {
 193                 e.printStackTrace();
 194             }
 195         }
 196     }
 197
 198     public SearchIndices[] performSearchRequest(String query, int maxResults) {
 199         String[] whereArgs = new String[] {
 200                 "%" + query + "%"
 201         };
 202         Cursor resultSet = database.rawQuery("SELECT KeyName, SearchTitle, Description, StaticEntry, UpdateTime, TargetPage, SearchText FROM SearchIndex WHERE SearchText LIKE ? ORDER BY StaticEntry DESC, UpdateTime DESC", whereArgs);
 203         ArrayList<SearchIndices> indices = new ArrayList<SearchIndices>();
 204         if(resultSet.moveToFirst()) {
 205             int[] columnIndexes = {
 206                     resultSet.getColumnIndex("KeyName"),
 207                     resultSet.getColumnIndex("SearchTitle"),
 208                     resultSet.getColumnIndex("Description"),
 209                     resultSet.getColumnIndex("StaticEntry"),
 210                     resultSet.getColumnIndex("UpdateTime"),
 211                     resultSet.getColumnIndex("TargetPage"),
 212                     resultSet.getColumnIndex("SearchText")
 213             };
 214             do {
 215                 SearchIndices cIndices = new SearchIndices(resultSet.getString(columnIndexes[0]), (resultSet.getInt(columnIndexes[3]) == 1));
 216                 cIndices.setUpdateTime(resultSet.getLong(columnIndexes[4]));
 217                 cIndices.setTarget(resultSet.getString(columnIndexes[5]));
 218                 cIndices.addKeyWord(resultSet.getString(columnIndexes[6]));
 219                 cIndices.setTitle(resultSet.getString(columnIndexes[1]));
 220                 cIndices.setDescription(resultSet.getString(columnIndexes[2]));
 221                 indices.add(cIndices);
 222             } while (resultSet.moveToNext() && indices.size() < maxResults);
 223         }
 224         resultSet.close();
 225
 226         SearchIndices[] indicesArr = new SearchIndices[indices.size()];
 227         indicesArr = indices.toArray(indicesArr);
 228         return indicesArr;
 229     }
 230
 231     public void setRuntimeCache(String name, String value) {
 232         long now = (new Date()).getTime() / 1000;
 233         String[] whereArgs = new String[] {
 234                 name
 235         };
 236         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 237         if(resultSet.moveToFirst()) {
 238             if(resultSet.getString(0).equalsIgnoreCase(value))
 239                 return;
 240             try {
 241                 ContentValues updateValues = new ContentValues();
 242                 updateValues.put("Value", value);
 243                 updateValues.put("LastUpdate", now);
 244
 245                 database.update("RuntimeCache", updateValues, "Reference = ?", whereArgs);
 246             } catch(Exception e) {
 247                 e.printStackTrace();
 248             }
 249         } else {
 250             try {
 251                 ContentValues indexValues = new ContentValues();
 252                 indexValues.put("Reference", name);
 253                 indexValues.put("Value", value);
 254                 indexValues.put("LastUpdate", now);
 255
 256                 database.insertOrThrow("RuntimeCache", null, indexValues);
 257             } catch(Exception e) {
 258                 e.printStackTrace();
 259             }
 260         }
 261         resultSet.close();
 262     }
 263
 264     public String getRuntimeCache(String name) {
 265         String value = null;
 266         String[] whereArgs = new String[] {
 267                 name
 268         };
 269         Cursor resultSet = database.rawQuery("SELECT Value FROM RuntimeCache WHERE Reference = ?", whereArgs);
 270         if(resultSet.moveToFirst()) {
 271             value = resultSet.getString(0);
 272         }
 273         resultSet.close();
 274         return value;
 275     }
 276
 277     public void addNfcCardData(NfcCardData nfcCardData) {
 278         String[] whereArgs = new String[] {
 279                 Integer.toString(nfcCardData.getUniqueId()),
 280                 Long.toString(nfcCardData.getLastUpdate())
 281         };
 282         Cursor resultSet = database.rawQuery("SELECT CardData FROM NfcCardStore WHERE CardId = ? AND UpdateTime = ?", whereArgs);
 283         if(resultSet.moveToFirst()) {
 284             if(resultSet.getString(0).equalsIgnoreCase(nfcCardData.getCardData()))
 285                 return;
 286             try {
 287                 ContentValues updateValues = new ContentValues();
 288                 updateValues.put("CardData", nfcCardData.getCardData());
 289                 updateValues.put("CardBalance", nfcCardData.getBalance());
 290                 updateValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
 291
 292                 database.update("NfcCardStore", updateValues, "CardId = ? AND UpdateTime = ?", whereArgs);
 293             } catch(Exception e) {
 294                 e.printStackTrace();
 295             }
 296         } else {
 297             try {
 298                 ContentValues indexValues = new ContentValues();
 299                 indexValues.put("CardId", nfcCardData.getUniqueId());
 300                 indexValues.put("UpdateTime", nfcCardData.getLastUpdate());
 301                 indexValues.put("CardData", nfcCardData.getCardData());
 302                 indexValues.put("CardBalance", nfcCardData.getBalance());
 303                 indexValues.put("CardLastTransaction", nfcCardData.getLastTransaction());
 304
 305                 database.insertOrThrow("NfcCardStore", null, indexValues);
 306             } catch(Exception e) {
 307                 e.printStackTrace();
 308             }
 309         }
 310         resultSet.close();
 311     }
 312
 313     public NfcCardData[] getNfcCardData(int lastLimit) {
 314         String value = null;
 315         Cursor resultSet;
 316         if(lastLimit > 0) {
 317             String[] whereArgs = {
 318                     Integer.toString(lastLimit)
 319             };
 320             resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardData,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC LIMIT ?", whereArgs);
 321         } else
 322             resultSet = database.rawQuery("SELECT CardId,UpdateTime,CardData,CardBalance,CardLastTransaction FROM NfcCardStore ORDER BY UpdateTime DESC", null);
 323         ArrayList<NfcCardData> nfcCardDatas = new ArrayList<NfcCardData>();
 324         if(resultSet.moveToFirst()) {
 325             do {
 326                 NfcCardData nfcCardData = new NfcCardData(resultSet.getInt(0), resultSet.getLong(1), resultSet.getString(2), resultSet.getInt(3), resultSet.getInt(4));
 327                 nfcCardDatas.add(nfcCardData);
 328             } while (resultSet.moveToNext());
 329         }
 330         resultSet.close();
 331         NfcCardData[] resultsArr = new NfcCardData[nfcCardDatas.size()];
 332         resultsArr = nfcCardDatas.toArray(resultsArr);
 333         return resultsArr;
 334     }
 335
 336     public void updateCourseCalendar(CourseEvent event) {
 337         if(vorlesungsplanDBHelper == null)
 338             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 339         vorlesungsplanDBHelper.updateCourseCalendar(event);
 340     }
 341
 342     public CourseEvent[] getCourseCalendarEvents(String coursename, long timeFrom, long timeTo) {
 343         if(vorlesungsplanDBHelper == null)
 344             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 345         return vorlesungsplanDBHelper.getCourseCalendarEvents(coursename, timeFrom, timeTo);
 346     }
 347
 348     public CourseGroup getCourseGroup(int courseGroupId) {
 349         if(vorlesungsplanDBHelper == null)
 350             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 351         return vorlesungsplanDBHelper.getCourseGroup(courseGroupId);
 352     }
 353
 354     public CourseGroup getCourseGroup(String coursename, String groupname) {
 355         if(vorlesungsplanDBHelper == null)
 356             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 357         return vorlesungsplanDBHelper.getCourseGroup(coursename, groupname);
 358     }
 359
 360     public CourseGroup addCourseGroup(String coursename, String groupname) {
 361         if(vorlesungsplanDBHelper == null)
 362             vorlesungsplanDBHelper = new VorlesungsplanDatabaseHelper(AppContext, database);
 363         return vorlesungsplanDBHelper.addCourseGroup(coursename, groupname);
 364     }
 365
 366     public void updateMensaTagesplan(MensaTagesplan plan) {
 367         if(mensaplanDBHelper == null)
 368             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 369         mensaplanDBHelper.updateMensaTagesplan(plan);
 370     }
 371
 372     public MensaTagesplan[] getMensaTagesplan(long timeFrom, long timeTo) {
 373         if(mensaplanDBHelper == null)
 374             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 375         return mensaplanDBHelper.getMensaTagesplan(timeFrom, timeTo);
 376     }
 377
 378     public long[] getDaysWithPlanData(long timeFrom, long timeTo) {
 379         if(mensaplanDBHelper == null)
 380             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 381         return mensaplanDBHelper.getDaysWithPlanData(timeFrom, timeTo);
 382     }
 383
 384     public long[] getWeeksWithPlanData(long timeFrom, long timeTo) {
 385         if(mensaplanDBHelper == null)
 386             mensaplanDBHelper = new MensaplanDatabaseHelper(AppContext, database);
 387         return mensaplanDBHelper.getWeeksWithPlanData(timeFrom, timeTo);
 388     }
 389
 390     public void updateNewsItem(NewsItem news) {
 391         if(newsDBHelper == null)
 392             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 393         newsDBHelper.updateNewsItem(news);
 394     }
 395
 396     public NewsItem[] getNewsItems(String source, long timeFrom, long timeTo) {
 397         if(newsDBHelper == null)
 398             newsDBHelper = new NewsDatabaseHelper(AppContext, database);
 399         return newsDBHelper.getNewsItems(source, timeFrom, timeTo);
 400     }
 401
 402 }