1 files changed, 354 insertions, 0 deletions
diff --git a/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java b/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java
new file mode 100644
index 000000000..84ac02dba
--- /dev/null
+++ b/WordPress/src/main/java/org/wordpress/android/datasets/PeopleTable.java
@@ -0,0 +1,354 @@
+package org.wordpress.android.datasets;
+
+import android.content.ContentValues;
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.database.sqlite.SQLiteQueryBuilder;
+import android.support.annotation.Nullable;
+
+import org.wordpress.android.WordPress;
+import org.wordpress.android.models.Person;
+import org.wordpress.android.models.Role;
+import org.wordpress.android.ui.people.utils.PeopleUtils;
+import org.wordpress.android.util.AppLog;
+import org.wordpress.android.util.SqlUtils;
+
+import java.util.ArrayList;
+import java.util.List;
+
+public class PeopleTable {
+    private static final String TEAM_TABLE = "people_team";
+    private static final String FOLLOWERS_TABLE = "people_followers";
+    private static final String EMAIL_FOLLOWERS_TABLE = "people_email_followers";
+    private static final String VIEWERS_TABLE = "people_viewers";
+
+    private static SQLiteDatabase getReadableDb() {
+        return WordPress.wpDB.getDatabase();
+    }
+    private static SQLiteDatabase getWritableDb() {
+        return WordPress.wpDB.getDatabase();
+    }
+
+    public static void createTables(SQLiteDatabase db) {
+        db.execSQL("CREATE TABLE " + TEAM_TABLE + " ("
+                + "person_id               INTEGER DEFAULT 0,"
+                + "local_blog_id           INTEGER DEFAULT 0,"
+                + "user_name               TEXT,"
+                + "display_name            TEXT,"
+                + "avatar_url              TEXT,"
+                + "role                    TEXT,"
+                + "PRIMARY KEY (person_id, local_blog_id)"
+                + ");");
+
+        db.execSQL("CREATE TABLE " + FOLLOWERS_TABLE + " ("
+                + "person_id               INTEGER DEFAULT 0,"
+                + "local_blog_id           INTEGER DEFAULT 0,"
+                + "user_name               TEXT,"
+                + "display_name            TEXT,"
+                + "avatar_url              TEXT,"
+                + "subscribed              TEXT,"
+                + "PRIMARY KEY (person_id, local_blog_id)"
+                + ");");
+
+        db.execSQL("CREATE TABLE " + EMAIL_FOLLOWERS_TABLE + " ("
+                + "person_id               INTEGER DEFAULT 0,"
+                + "local_blog_id           INTEGER DEFAULT 0,"
+                + "display_name            TEXT,"
+                + "avatar_url              TEXT,"
+                + "subscribed              TEXT,"
+                + "PRIMARY KEY (person_id, local_blog_id)"
+                + ");");
+    }
+
+    public static void createViewersTable(SQLiteDatabase db) {
+        db.execSQL("CREATE TABLE " + VIEWERS_TABLE + " ("
+                + "person_id               INTEGER DEFAULT 0,"
+                + "local_blog_id           INTEGER DEFAULT 0,"
+                + "user_name               TEXT,"
+                + "display_name            TEXT,"
+                + "avatar_url              TEXT,"
+                + "PRIMARY KEY (person_id, local_blog_id)"
+                + ");");
+    }
+
+    private static void dropTables(SQLiteDatabase db) {
+        // People table is not used anymore, each filter now has it's own table
+        db.execSQL("DROP TABLE IF EXISTS people");
+
+        db.execSQL("DROP TABLE IF EXISTS " + TEAM_TABLE);
+        db.execSQL("DROP TABLE IF EXISTS " + FOLLOWERS_TABLE);
+        db.execSQL("DROP TABLE IF EXISTS " + EMAIL_FOLLOWERS_TABLE);
+        db.execSQL("DROP TABLE IF EXISTS " + VIEWERS_TABLE);
+    }
+
+    public static void reset(SQLiteDatabase db) {
+        AppLog.i(AppLog.T.PEOPLE, "resetting people table");
+        dropTables(db);
+        createTables(db);
+    }
+
+    public static void saveUser(Person person) {
+        save(TEAM_TABLE, person, getWritableDb());
+    }
+
+    private static void save(String table, Person person, SQLiteDatabase database) {
+        ContentValues values = new ContentValues();
+        values.put("person_id", person.getPersonID());
+        values.put("local_blog_id", person.getLocalTableBlogId());
+        values.put("display_name", person.getDisplayName());
+        values.put("avatar_url", person.getAvatarUrl());
+
+        switch (table) {
+            case TEAM_TABLE:
+                values.put("user_name", person.getUsername());
+                if (person.getRole() != null) {
+                    values.put("role", person.getRole().toString());
+                }
+                break;
+            case FOLLOWERS_TABLE:
+                values.put("user_name", person.getUsername());
+                values.put("subscribed", person.getSubscribed());
+                break;
+            case EMAIL_FOLLOWERS_TABLE:
+                values.put("subscribed", person.getSubscribed());
+                break;
+            case VIEWERS_TABLE:
+                values.put("user_name", person.getUsername());
+                break;
+        }
+
+        database.insertWithOnConflict(table, null, values, SQLiteDatabase.CONFLICT_REPLACE);
+    }
+
+    public static void saveUsers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) {
+        savePeople(TEAM_TABLE, peopleList, localTableBlogId, isFreshList);
+    }
+
+    public static void saveFollowers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) {
+        savePeople(FOLLOWERS_TABLE, peopleList, localTableBlogId, isFreshList);
+    }
+
+    public static void saveEmailFollowers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) {
+        savePeople(EMAIL_FOLLOWERS_TABLE, peopleList, localTableBlogId, isFreshList);
+    }
+
+    public static void saveViewers(List<Person> peopleList, int localTableBlogId, boolean isFreshList) {
+        savePeople(VIEWERS_TABLE, peopleList, localTableBlogId, isFreshList);
+    }
+
+    private static void savePeople(String table, List<Person> peopleList, int localTableBlogId, boolean isFreshList) {
+        getWritableDb().beginTransaction();
+        try {
+            // We have a fresh list, remove the previous list of people in case it was deleted on remote
+            if (isFreshList) {
+                PeopleTable.deletePeople(table, localTableBlogId);
+            }
+
+            for (Person person : peopleList) {
+                PeopleTable.save(table, person, getWritableDb());
+            }
+            getWritableDb().setTransactionSuccessful();
+        } finally {
+            getWritableDb().endTransaction();
+        }
+    }
+
+    public static void deletePeopleForLocalBlogId(int localTableBlogId) {
+        deletePeople(TEAM_TABLE, localTableBlogId);
+        deletePeople(FOLLOWERS_TABLE, localTableBlogId);
+        deletePeople(EMAIL_FOLLOWERS_TABLE, localTableBlogId);
+        deletePeople(VIEWERS_TABLE, localTableBlogId);
+    }
+
+    private static void deletePeople(String table, int localTableBlogId) {
+        String[] args = new String[]{Integer.toString(localTableBlogId)};
+        getWritableDb().delete(table, "local_blog_id=?1", args);
+    }
+
+    /**
+     * In order to avoid syncing issues, this method will be called when People page is created. We only keep
+     * the first page of users, so we don't show an empty screen. When fresh data is received, it'll replace
+     * the existing page.
+     * @param localTableBlogId - the local blog id people will be deleted from
+     */
+    public static void deletePeopleExceptForFirstPage(int localTableBlogId) {
+        int fetchLimit = PeopleUtils.FETCH_LIMIT;
+        String[] tables = {TEAM_TABLE, FOLLOWERS_TABLE, EMAIL_FOLLOWERS_TABLE, VIEWERS_TABLE};
+
+        getWritableDb().beginTransaction();
+        try {
+            for (String table : tables) {
+                int size = getPeopleCountForLocalBlogId(table, localTableBlogId);
+                if (size > fetchLimit) {
+                    String where = "local_blog_id=" + localTableBlogId;
+                    String[] columns = {"person_id"};
+                    String limit = Integer.toString(size - fetchLimit);
+                    String orderBy;
+                    if (shouldOrderAlphabetically(table)) {
+                        orderBy = "lower(display_name) DESC, lower(user_name) DESC";
+                    } else {
+                        orderBy = "ROWID DESC";
+                    }
+                    String inQuery = SQLiteQueryBuilder.buildQueryString(false, table, columns, where, null, null,
+                            orderBy, limit);
+
+                    String[] args = new String[] {Integer.toString(localTableBlogId)};
+                    getWritableDb().delete(table, "local_blog_id=?1 AND person_id IN (" + inQuery + ")", args);
+                }
+            }
+            getWritableDb().setTransactionSuccessful();
+        } finally {
+            getWritableDb().endTransaction();
+        }
+    }
+
+    public static int getUsersCountForLocalBlogId(int localTableBlogId) {
+        return getPeopleCountForLocalBlogId(TEAM_TABLE, localTableBlogId);
+    }
+
+    public static int getViewersCountForLocalBlogId(int localTableBlogId) {
+        return getPeopleCountForLocalBlogId(VIEWERS_TABLE, localTableBlogId);
+    }
+
+    private static int getPeopleCountForLocalBlogId(String table, int localTableBlogId) {
+        String[] args = new String[]{Integer.toString(localTableBlogId)};
+        String sql = "SELECT COUNT(*) FROM " + table + " WHERE local_blog_id=?";
+        return SqlUtils.intForQuery(getReadableDb(), sql, args);
+    }
+
+    public static void deletePerson(long personID, int localTableBlogId, Person.PersonType personType) {
+        String table = getTableForPersonType(personType);
+        if (table != null) {
+            deletePerson(table, personID, localTableBlogId);
+        }
+    }
+
+    private static void deletePerson(String table, long personID, int localTableBlogId) {
+        String[] args = new String[]{Long.toString(personID), Integer.toString(localTableBlogId)};
+        getWritableDb().delete(table, "person_id=? AND local_blog_id=?", args);
+    }
+
+    public static List<Person> getUsers(int localTableBlogId) {
+        return PeopleTable.getPeople(TEAM_TABLE, localTableBlogId);
+    }
+
+    public static List<Person> getFollowers(int localTableBlogId) {
+        return PeopleTable.getPeople(FOLLOWERS_TABLE, localTableBlogId);
+    }
+
+    public static List<Person> getEmailFollowers(int localTableBlogId) {
+        return PeopleTable.getPeople(EMAIL_FOLLOWERS_TABLE, localTableBlogId);
+    }
+
+    public static List<Person> getViewers(int localTableBlogId) {
+        return PeopleTable.getPeople(VIEWERS_TABLE, localTableBlogId);
+    }
+
+    private static List<Person> getPeople(String table, int localTableBlogId) {
+        String[] args = {Integer.toString(localTableBlogId)};
+        String orderBy;
+        if (shouldOrderAlphabetically(table)) {
+            orderBy = " ORDER BY lower(display_name), lower(user_name)";
+        } else {
+            // we want the server-side order for followers & viewers
+            orderBy = " ORDER BY ROWID";
+        }
+        Cursor c = getReadableDb().rawQuery("SELECT * FROM " + table + " WHERE local_blog_id=?" + orderBy, args);
+
+        List<Person> people = new ArrayList<>();
+        try {
+            while (c.moveToNext()) {
+                Person person = getPersonFromCursor(c, table, localTableBlogId);
+                people.add(person);
+            }
+        } finally {
+            SqlUtils.closeCursor(c);
+        }
+        return people;
+    }
+
+    @Nullable
+    public static Person getPerson(long personId, int localTableBlogId, Person.PersonType personType) {
+        String table = getTableForPersonType(personType);
+        if (table != null) {
+            return getPerson(table, personId, localTableBlogId);
+        }
+        return null;
+    }
+
+    public static Person getUser(long personId, int localTableBlogId) {
+        return getPerson(TEAM_TABLE, personId, localTableBlogId);
+    }
+
+    /**
+     * retrieve a person
+     * @param table - sql table the person record is in
+     * @param personId - id of a person in a particular blog
+     * @param localTableBlogId - the local blog id the user belongs to
+     * @return Person if found, null otherwise
+     */
+    private static Person getPerson(String table, long personId, int localTableBlogId) {
+        String[] args = { Long.toString(personId), Integer.toString(localTableBlogId)};
+        Cursor c = getReadableDb().rawQuery("SELECT * FROM " + table +
+                " WHERE person_id=? AND local_blog_id=?", args);
+        try {
+            if (!c.moveToFirst()) {
+                return null;
+            }
+            return getPersonFromCursor(c, table, localTableBlogId);
+        } finally {
+            SqlUtils.closeCursor(c);
+        }
+    }
+
+    private static Person getPersonFromCursor(Cursor c, String table, int localTableBlogId) {
+        long personId = c.getInt(c.getColumnIndex("person_id"));
+
+        Person person = new Person(personId, localTableBlogId);
+        person.setDisplayName(c.getString(c.getColumnIndex("display_name")));
+        person.setAvatarUrl(c.getString(c.getColumnIndex("avatar_url")));
+        switch (table) {
+            case TEAM_TABLE:
+                person.setUsername(c.getString(c.getColumnIndex("user_name")));
+                String role = c.getString(c.getColumnIndex("role"));
+                person.setRole(Role.fromString(role));
+                person.setPersonType(Person.PersonType.USER);
+                break;
+            case FOLLOWERS_TABLE:
+                person.setUsername(c.getString(c.getColumnIndex("user_name")));
+                person.setSubscribed(c.getString(c.getColumnIndex("subscribed")));
+                person.setPersonType(Person.PersonType.FOLLOWER);
+                break;
+            case EMAIL_FOLLOWERS_TABLE:
+                person.setSubscribed(c.getString(c.getColumnIndex("subscribed")));
+                person.setPersonType(Person.PersonType.EMAIL_FOLLOWER);
+                break;
+            case VIEWERS_TABLE:
+                person.setUsername(c.getString(c.getColumnIndex("user_name")));
+                person.setPersonType(Person.PersonType.VIEWER);
+                break;
+        }
+
+        return person;
+    }
+
+    // order is disabled for followers & viewers for now since the API is not supporting it
+    private static boolean shouldOrderAlphabetically(String table) {
+       return table.equals(TEAM_TABLE);
+    }
+
+    @Nullable
+    private static String getTableForPersonType(Person.PersonType personType) {
+        switch (personType) {
+            case USER:
+                return TEAM_TABLE;
+            case FOLLOWER:
+                return FOLLOWERS_TABLE;
+            case EMAIL_FOLLOWER:
+                return EMAIL_FOLLOWERS_TABLE;
+            case VIEWER:
+                return VIEWERS_TABLE;
+        }
+        return null;
+    }
+}