Update ORM and improve SQL

12 files changed, 73 insertions, 29 deletions

diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/DatabaseConnectionOrm.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/DatabaseConnectionOrm.java
index 4da3fcc1..330830dd 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/DatabaseConnectionOrm.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/DatabaseConnectionOrm.java
@@ -64,8 +64,20 @@ public class DatabaseConnectionOrm {
         daoSession = DatabaseHelperOrm.getDaoSession(context);
     }
 
+    /*
     public void insertNewFolder (Folder folder) {
         daoSession.getFolderDao().insertOrReplace(folder);
+    }*/
+
+    public void deleteOldAndInsertNewFolders (final Folder... folder) {
+        daoSession.runInTx(new Runnable() {
+            @Override
+            public void run() {
+                daoSession.getFolderDao().deleteAll();
+                daoSession.getFolderDao().insertInTx(folder);
+            }
+        });
+
     }
 
     public void insertNewFeed (Feed feed) {
@@ -359,10 +371,7 @@ public class DatabaseConnectionOrm {
 
         String buildSQL =  "SELECT " + RssItemDao.Properties.Id.columnName +
                 " FROM " + RssItemDao.TABLENAME +
-                " WHERE " + RssItemDao.Properties.FeedId.columnName + " IN " +
-                "(SELECT " + FeedDao.Properties.Id.columnName +
-                " FROM " + FeedDao.TABLENAME +
-                " WHERE " + FeedDao.Properties.Id.columnName  + " = " + idFeed + ")";
+                " WHERE " + RssItemDao.Properties.FeedId.columnName + " = " + idFeed;
 
         if(onlyUnread && !onlyStarredItems)
             buildSQL += " AND " + RssItemDao.Properties.Read_temp.columnName + " != 1";
@@ -426,8 +435,16 @@ public class DatabaseConnectionOrm {
     public void insertIntoRssCurrentViewTable(String SQL_SELECT) {
         SQL_SELECT = "INSERT INTO " + CurrentRssItemViewDao.TABLENAME +
                 " (" + CurrentRssItemViewDao.Properties.RssItemId.columnName + ") " + SQL_SELECT;
-        daoSession.getCurrentRssItemViewDao().deleteAll();
-        daoSession.getDatabase().execSQL(SQL_SELECT);
+
+        final String SQL_INSERT_STATEMENT = SQL_SELECT;
+
+        daoSession.runInTx(new Runnable() {
+            @Override
+            public void run() {
+                daoSession.getCurrentRssItemViewDao().deleteAll();
+                daoSession.getDatabase().execSQL(SQL_INSERT_STATEMENT);
+            }
+        });
     }
 
     public SparseArray<String> getUnreadItemCountForFolder() {
@@ -448,7 +465,7 @@ public class DatabaseConnectionOrm {
     }
 
     public String getUnreadItemsCountForSpecificFolder(SPECIAL_FOLDERS specialFolder) {
-        String buildSQL = "SELECT COUNT(rss." + RssItemDao.Properties.Id.columnName + ")" +
+        String buildSQL = "SELECT COUNT(1)" +
                 " FROM " + RssItemDao.TABLENAME + " rss ";
 
         if(specialFolder != null && specialFolder.equals(SPECIAL_FOLDERS.ALL_STARRED_ITEMS)) {
@@ -462,7 +479,7 @@ public class DatabaseConnectionOrm {
     }
 
     public SparseArray<String> getUnreadItemCountForFeed() {
-        String buildSQL = "SELECT " + RssItemDao.Properties.FeedId.columnName + ", COUNT(" + RssItemDao.Properties.Id.columnName + ")" + // rowid as _id,
+        String buildSQL = "SELECT " + RssItemDao.Properties.FeedId.columnName + ", COUNT(1)" + // rowid as _id,
                 " FROM " + RssItemDao.TABLENAME +
                 " WHERE " + RssItemDao.Properties.Read_temp.columnName + " != 1 " +
                 " GROUP BY " + RssItemDao.Properties.FeedId.columnName;
@@ -471,7 +488,7 @@ public class DatabaseConnectionOrm {
     }
 
     public SparseArray<String> getStarredItemCountForFeed() {
-        String buildSQL = "SELECT " + RssItemDao.Properties.FeedId.columnName + ", COUNT(" + RssItemDao.Properties.Id.columnName + ")" + // rowid as _id,
+        String buildSQL = "SELECT " + RssItemDao.Properties.FeedId.columnName + ", COUNT(1)" + // rowid as _id,
                 " FROM " + RssItemDao.TABLENAME +
                 " WHERE " + RssItemDao.Properties.Starred_temp.columnName + " = 1 " +
                 " GROUP BY " + RssItemDao.Properties.FeedId.columnName;
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/DatabaseOrmGenerator.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/DatabaseOrmGenerator.java
index 685b9477..89637016 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/DatabaseOrmGenerator.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/DatabaseOrmGenerator.java
@@ -29,7 +29,7 @@ public class DatabaseOrmGenerator {
     public static void main(String[] args) throws IOException, Exception {
         List<SchemaVersion> versions = new ArrayList<SchemaVersion>();
 
-        versions.add(new Version3(true));
+        versions.add(new Version4(true));
 
         validateSchemas(versions);
 
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/Version3.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/Version4.java
index dedc535b..9c067d3e 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/Version3.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/generator/Version4.java
@@ -13,14 +13,14 @@ import de.greenrobot.daogenerator.Schema;
  *
  * @author Jeremy
  */
-public class Version3 extends SchemaVersion {
+public class Version4 extends SchemaVersion {
 
     /**
      * Constructor
      *
      * @param current
      */
-    public Version3(boolean current) {
+    public Version4(boolean current) {
         super(current);
 
         Schema schema = getSchema();
@@ -32,7 +32,7 @@ public class Version3 extends SchemaVersion {
      */
     @Override
     public int getVersionNumber() {
-        return 3;
+        return 4;
     }
 
     private static void addEntitysToSchema(Schema schema) {
@@ -45,7 +45,7 @@ public class Version3 extends SchemaVersion {
         /* Feed */
         Entity feed = schema.addEntity("Feed");
         Property feedId = feed.addIdProperty().notNull().getProperty();
-        Property folderIdProperty = feed.addLongProperty("folderId").getProperty();
+        Property folderIdProperty = feed.addLongProperty("folderId").index().getProperty();
 
         feed.addStringProperty("feedTitle").notNull();
         feed.addStringProperty("faviconUrl");
@@ -57,7 +57,7 @@ public class Version3 extends SchemaVersion {
         /* RSS Item */
         Entity rssItem = schema.addEntity("RssItem");
         Property rssItemId = rssItem.addIdProperty().notNull().getProperty();
-        Property rssItemFeedId = rssItem.addLongProperty("feedId").notNull().getProperty();
+        Property rssItemFeedId = rssItem.addLongProperty("feedId").notNull().index().getProperty();
 
         rssItem.addStringProperty("link");
         rssItem.addStringProperty("title");
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/CurrentRssItemViewDao.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/CurrentRssItemViewDao.java
index dc2a8680..49e764a2 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/CurrentRssItemViewDao.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/CurrentRssItemViewDao.java
@@ -8,6 +8,8 @@ import de.greenrobot.dao.AbstractDao;
 import de.greenrobot.dao.Property;
 import de.greenrobot.dao.internal.DaoConfig;
 
+import de.luhmer.owncloudnewsreader.database.model.CurrentRssItemView;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 /** 
  * DAO for table CURRENT_RSS_ITEM_VIEW.
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoMaster.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoMaster.java
index af494042..6cd97e0f 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoMaster.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoMaster.java
@@ -5,16 +5,20 @@ import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteDatabase.CursorFactory;
 import android.database.sqlite.SQLiteOpenHelper;
 import android.util.Log;
-
 import de.greenrobot.dao.AbstractDaoMaster;
 import de.greenrobot.dao.identityscope.IdentityScopeType;
 
+import de.luhmer.owncloudnewsreader.database.model.FolderDao;
+import de.luhmer.owncloudnewsreader.database.model.FeedDao;
+import de.luhmer.owncloudnewsreader.database.model.RssItemDao;
+import de.luhmer.owncloudnewsreader.database.model.CurrentRssItemViewDao;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 /** 
- * Master of DAO (schema version 3): knows all DAOs.
+ * Master of DAO (schema version 4): knows all DAOs.
 */
 public class DaoMaster extends AbstractDaoMaster {
-    public static final int SCHEMA_VERSION = 3;
+    public static final int SCHEMA_VERSION = 4;
 
     /** Creates underlying database table using DAOs. */
     public static void createAllTables(SQLiteDatabase db, boolean ifNotExists) {
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoSession.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoSession.java
index 12933605..0cc251e9 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoSession.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/DaoSession.java
@@ -9,6 +9,16 @@ import de.greenrobot.dao.AbstractDaoSession;
 import de.greenrobot.dao.identityscope.IdentityScopeType;
 import de.greenrobot.dao.internal.DaoConfig;
 
+import de.luhmer.owncloudnewsreader.database.model.Folder;
+import de.luhmer.owncloudnewsreader.database.model.Feed;
+import de.luhmer.owncloudnewsreader.database.model.RssItem;
+import de.luhmer.owncloudnewsreader.database.model.CurrentRssItemView;
+
+import de.luhmer.owncloudnewsreader.database.model.FolderDao;
+import de.luhmer.owncloudnewsreader.database.model.FeedDao;
+import de.luhmer.owncloudnewsreader.database.model.RssItemDao;
+import de.luhmer.owncloudnewsreader.database.model.CurrentRssItemViewDao;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 
 /**
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Feed.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Feed.java
index ac60553e..a82cecfa 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Feed.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Feed.java
@@ -1,7 +1,7 @@
 package de.luhmer.owncloudnewsreader.database.model;
 
 import java.util.List;
-
+import de.luhmer.owncloudnewsreader.database.model.DaoSession;
 import de.greenrobot.dao.DaoException;
 
 // THIS CODE IS GENERATED BY greenDAO, EDIT ONLY INSIDE THE "KEEP"-SECTIONS
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FeedDao.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FeedDao.java
index 5b011d19..67354e9b 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FeedDao.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FeedDao.java
@@ -1,19 +1,20 @@
 package de.luhmer.owncloudnewsreader.database.model;
 
+import java.util.List;
+import java.util.ArrayList;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteStatement;
 
-import java.util.ArrayList;
-import java.util.List;
-
 import de.greenrobot.dao.AbstractDao;
 import de.greenrobot.dao.Property;
-import de.greenrobot.dao.internal.DaoConfig;
 import de.greenrobot.dao.internal.SqlUtils;
+import de.greenrobot.dao.internal.DaoConfig;
 import de.greenrobot.dao.query.Query;
 import de.greenrobot.dao.query.QueryBuilder;
 
+import de.luhmer.owncloudnewsreader.database.model.Feed;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 /** 
  * DAO for table FEED.
@@ -58,6 +59,9 @@ public class FeedDao extends AbstractDao<Feed, Long> {
                 "'FAVICON_URL' TEXT," + // 3: faviconUrl
                 "'LINK' TEXT," + // 4: link
                 "'AVG_COLOUR' TEXT);"); // 5: avgColour
+        // Add Indexes
+        db.execSQL("CREATE INDEX " + constraint + "IDX_FEED_FOLDER_ID ON FEED" +
+                " (FOLDER_ID);");
     }
 
     /** Drops the underlying database table. */
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Folder.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Folder.java
index ea5cf36d..fde89c00 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Folder.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/Folder.java
@@ -1,7 +1,7 @@
 package de.luhmer.owncloudnewsreader.database.model;
 
 import java.util.List;
-
+import de.luhmer.owncloudnewsreader.database.model.DaoSession;
 import de.greenrobot.dao.DaoException;
 
 // THIS CODE IS GENERATED BY greenDAO, EDIT ONLY INSIDE THE "KEEP"-SECTIONS
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FolderDao.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FolderDao.java
index f94c8ce0..f98b9245 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FolderDao.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/FolderDao.java
@@ -8,6 +8,8 @@ import de.greenrobot.dao.AbstractDao;
 import de.greenrobot.dao.Property;
 import de.greenrobot.dao.internal.DaoConfig;
 
+import de.luhmer.owncloudnewsreader.database.model.Folder;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 /** 
  * DAO for table FOLDER.
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItem.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItem.java
index 9bfb859b..4bc72709 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItem.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItem.java
@@ -1,7 +1,8 @@
 package de.luhmer.owncloudnewsreader.database.model;
 
-import de.greenrobot.dao.DaoException;
 import de.luhmer.owncloudnewsreader.adapter.HasId;
+import de.luhmer.owncloudnewsreader.database.model.DaoSession;
+import de.greenrobot.dao.DaoException;
 
 // THIS CODE IS GENERATED BY greenDAO, EDIT ONLY INSIDE THE "KEEP"-SECTIONS
 
diff --git a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItemDao.java b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItemDao.java
index 4739d5f9..f5613e05 100644
--- a/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItemDao.java
+++ b/News-Android-App/src/main/java/de/luhmer/owncloudnewsreader/database/model/RssItemDao.java
@@ -1,19 +1,20 @@
 package de.luhmer.owncloudnewsreader.database.model;
 
+import java.util.List;
+import java.util.ArrayList;
 import android.database.Cursor;
 import android.database.sqlite.SQLiteDatabase;
 import android.database.sqlite.SQLiteStatement;
 
-import java.util.ArrayList;
-import java.util.List;
-
 import de.greenrobot.dao.AbstractDao;
 import de.greenrobot.dao.Property;
-import de.greenrobot.dao.internal.DaoConfig;
 import de.greenrobot.dao.internal.SqlUtils;
+import de.greenrobot.dao.internal.DaoConfig;
 import de.greenrobot.dao.query.Query;
 import de.greenrobot.dao.query.QueryBuilder;
 
+import de.luhmer.owncloudnewsreader.database.model.RssItem;
+
 // THIS CODE IS GENERATED BY greenDAO, DO NOT EDIT.
 /** 
  * DAO for table RSS_ITEM.
@@ -78,6 +79,9 @@ public class RssItemDao extends AbstractDao<RssItem, Long> {
                 "'PUB_DATE' INTEGER," + // 13: pubDate
                 "'ENCLOSURE_LINK' TEXT," + // 14: enclosureLink
                 "'ENCLOSURE_MIME' TEXT);"); // 15: enclosureMime
+        // Add Indexes
+        db.execSQL("CREATE INDEX " + constraint + "IDX_RSS_ITEM_FEED_ID ON RSS_ITEM" +
+                " (FEED_ID);");
     }
 
     /** Drops the underlying database table. */