michael@0: /* -*- Mode: Java; c-basic-offset: 4; tab-width: 20; indent-tabs-mode: nil; -*- */ michael@0: /* This Source Code Form is subject to the terms of the Mozilla Public michael@0: * License, v. 2.0. If a copy of the MPL was not distributed with this file, michael@0: * You can obtain one at http://mozilla.org/MPL/2.0/. */ michael@0: michael@0: package org.mozilla.gecko.db; michael@0: michael@0: import java.io.ByteArrayOutputStream; michael@0: import java.io.File; michael@0: import java.lang.reflect.Field; michael@0: import java.util.ArrayList; michael@0: import java.util.List; michael@0: import java.util.Locale; michael@0: import java.util.regex.Matcher; michael@0: import java.util.regex.Pattern; michael@0: michael@0: import org.json.JSONArray; michael@0: import org.json.JSONException; michael@0: import org.json.JSONObject; michael@0: import org.mozilla.gecko.AppConstants; michael@0: import org.mozilla.gecko.Distribution; michael@0: import org.mozilla.gecko.R; michael@0: import org.mozilla.gecko.db.BrowserContract.Bookmarks; michael@0: import org.mozilla.gecko.db.BrowserContract.Combined; michael@0: import org.mozilla.gecko.db.BrowserContract.FaviconColumns; michael@0: import org.mozilla.gecko.db.BrowserContract.Favicons; michael@0: import org.mozilla.gecko.db.BrowserContract.History; michael@0: import org.mozilla.gecko.db.BrowserContract.Obsolete; michael@0: import org.mozilla.gecko.db.BrowserContract.ReadingListItems; michael@0: import org.mozilla.gecko.db.BrowserContract.Thumbnails; michael@0: import org.mozilla.gecko.gfx.BitmapUtils; michael@0: import org.mozilla.gecko.sync.Utils; michael@0: import org.mozilla.gecko.util.GeckoJarReader; michael@0: import org.mozilla.gecko.util.ThreadUtils; michael@0: michael@0: import android.content.ContentValues; michael@0: import android.content.Context; michael@0: import android.database.Cursor; michael@0: import android.database.DatabaseUtils; michael@0: import android.database.SQLException; michael@0: import android.database.sqlite.SQLiteDatabase; michael@0: import android.database.sqlite.SQLiteOpenHelper; michael@0: import android.graphics.Bitmap; michael@0: import android.net.Uri; michael@0: import android.os.Build; michael@0: import android.text.TextUtils; michael@0: import android.util.Log; michael@0: michael@0: michael@0: final class BrowserDatabaseHelper extends SQLiteOpenHelper { michael@0: michael@0: private static final String LOGTAG = "GeckoBrowserDBHelper"; michael@0: public static final int DATABASE_VERSION = 18; michael@0: public static final String DATABASE_NAME = "browser.db"; michael@0: michael@0: final protected Context mContext; michael@0: michael@0: static final String TABLE_BOOKMARKS = Bookmarks.TABLE_NAME; michael@0: static final String TABLE_HISTORY = History.TABLE_NAME; michael@0: static final String TABLE_FAVICONS = Favicons.TABLE_NAME; michael@0: static final String TABLE_THUMBNAILS = Thumbnails.TABLE_NAME; michael@0: static final String TABLE_READING_LIST = ReadingListItems.TABLE_NAME; michael@0: michael@0: static final String VIEW_COMBINED = Combined.VIEW_NAME; michael@0: static final String VIEW_BOOKMARKS_WITH_FAVICONS = Bookmarks.VIEW_WITH_FAVICONS; michael@0: static final String VIEW_HISTORY_WITH_FAVICONS = History.VIEW_WITH_FAVICONS; michael@0: static final String VIEW_COMBINED_WITH_FAVICONS = Combined.VIEW_WITH_FAVICONS; michael@0: michael@0: static final String TABLE_BOOKMARKS_JOIN_FAVICONS = TABLE_BOOKMARKS + " LEFT OUTER JOIN " + michael@0: TABLE_FAVICONS + " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " = " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons._ID); michael@0: michael@0: static final String TABLE_HISTORY_JOIN_FAVICONS = TABLE_HISTORY + " LEFT OUTER JOIN " + michael@0: TABLE_FAVICONS + " ON " + qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " = " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons._ID); michael@0: michael@0: static final String TABLE_BOOKMARKS_TMP = TABLE_BOOKMARKS + "_tmp"; michael@0: static final String TABLE_HISTORY_TMP = TABLE_HISTORY + "_tmp"; michael@0: static final String TABLE_IMAGES_TMP = Obsolete.TABLE_IMAGES + "_tmp"; michael@0: michael@0: private static final String[] mobileIdColumns = new String[] { Bookmarks._ID }; michael@0: private static final String[] mobileIdSelectionArgs = new String[] { Bookmarks.MOBILE_FOLDER_GUID }; michael@0: michael@0: public BrowserDatabaseHelper(Context context, String databasePath) { michael@0: super(context, databasePath, null, DATABASE_VERSION); michael@0: mContext = context; michael@0: } michael@0: michael@0: private void createBookmarksTable(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_BOOKMARKS + " table"); michael@0: michael@0: // Android versions older than Froyo ship with an sqlite michael@0: // that doesn't support foreign keys. michael@0: String foreignKeyOnParent = null; michael@0: if (Build.VERSION.SDK_INT >= 8) { michael@0: foreignKeyOnParent = ", FOREIGN KEY (" + Bookmarks.PARENT + michael@0: ") REFERENCES " + TABLE_BOOKMARKS + "(" + Bookmarks._ID + ")"; michael@0: } michael@0: michael@0: db.execSQL("CREATE TABLE " + TABLE_BOOKMARKS + "(" + michael@0: Bookmarks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: Bookmarks.TITLE + " TEXT," + michael@0: Bookmarks.URL + " TEXT," + michael@0: Bookmarks.TYPE + " INTEGER NOT NULL DEFAULT " + Bookmarks.TYPE_BOOKMARK + "," + michael@0: Bookmarks.PARENT + " INTEGER," + michael@0: Bookmarks.POSITION + " INTEGER NOT NULL," + michael@0: Bookmarks.KEYWORD + " TEXT," + michael@0: Bookmarks.DESCRIPTION + " TEXT," + michael@0: Bookmarks.TAGS + " TEXT," + michael@0: Bookmarks.DATE_CREATED + " INTEGER," + michael@0: Bookmarks.DATE_MODIFIED + " INTEGER," + michael@0: Bookmarks.GUID + " TEXT NOT NULL," + michael@0: Bookmarks.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" + michael@0: (foreignKeyOnParent != null ? foreignKeyOnParent : "") + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX bookmarks_url_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.URL + ")"); michael@0: db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX bookmarks_guid_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.GUID + ")"); michael@0: db.execSQL("CREATE INDEX bookmarks_modified_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.DATE_MODIFIED + ")"); michael@0: } michael@0: michael@0: private void createBookmarksTableOn13(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_BOOKMARKS + " table"); michael@0: michael@0: // Android versions older than Froyo ship with an sqlite michael@0: // that doesn't support foreign keys. michael@0: String foreignKeyOnParent = null; michael@0: if (Build.VERSION.SDK_INT >= 8) { michael@0: foreignKeyOnParent = ", FOREIGN KEY (" + Bookmarks.PARENT + michael@0: ") REFERENCES " + TABLE_BOOKMARKS + "(" + Bookmarks._ID + ")"; michael@0: } michael@0: michael@0: db.execSQL("CREATE TABLE " + TABLE_BOOKMARKS + "(" + michael@0: Bookmarks._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: Bookmarks.TITLE + " TEXT," + michael@0: Bookmarks.URL + " TEXT," + michael@0: Bookmarks.TYPE + " INTEGER NOT NULL DEFAULT " + Bookmarks.TYPE_BOOKMARK + "," + michael@0: Bookmarks.PARENT + " INTEGER," + michael@0: Bookmarks.POSITION + " INTEGER NOT NULL," + michael@0: Bookmarks.KEYWORD + " TEXT," + michael@0: Bookmarks.DESCRIPTION + " TEXT," + michael@0: Bookmarks.TAGS + " TEXT," + michael@0: Bookmarks.FAVICON_ID + " INTEGER," + michael@0: Bookmarks.DATE_CREATED + " INTEGER," + michael@0: Bookmarks.DATE_MODIFIED + " INTEGER," + michael@0: Bookmarks.GUID + " TEXT NOT NULL," + michael@0: Bookmarks.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" + michael@0: (foreignKeyOnParent != null ? foreignKeyOnParent : "") + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX bookmarks_url_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.URL + ")"); michael@0: db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX bookmarks_guid_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.GUID + ")"); michael@0: db.execSQL("CREATE INDEX bookmarks_modified_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.DATE_MODIFIED + ")"); michael@0: } michael@0: michael@0: private void createHistoryTable(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_HISTORY + " table"); michael@0: db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" + michael@0: History._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: History.TITLE + " TEXT," + michael@0: History.URL + " TEXT NOT NULL," + michael@0: History.VISITS + " INTEGER NOT NULL DEFAULT 0," + michael@0: History.DATE_LAST_VISITED + " INTEGER," + michael@0: History.DATE_CREATED + " INTEGER," + michael@0: History.DATE_MODIFIED + " INTEGER," + michael@0: History.GUID + " TEXT NOT NULL," + michael@0: History.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX history_url_index ON " + TABLE_HISTORY + "(" michael@0: + History.URL + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX history_guid_index ON " + TABLE_HISTORY + "(" michael@0: + History.GUID + ")"); michael@0: db.execSQL("CREATE INDEX history_modified_index ON " + TABLE_HISTORY + "(" michael@0: + History.DATE_MODIFIED + ")"); michael@0: db.execSQL("CREATE INDEX history_visited_index ON " + TABLE_HISTORY + "(" michael@0: + History.DATE_LAST_VISITED + ")"); michael@0: } michael@0: michael@0: private void createHistoryTableOn13(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_HISTORY + " table"); michael@0: db.execSQL("CREATE TABLE " + TABLE_HISTORY + "(" + michael@0: History._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: History.TITLE + " TEXT," + michael@0: History.URL + " TEXT NOT NULL," + michael@0: History.VISITS + " INTEGER NOT NULL DEFAULT 0," + michael@0: History.FAVICON_ID + " INTEGER," + michael@0: History.DATE_LAST_VISITED + " INTEGER," + michael@0: History.DATE_CREATED + " INTEGER," + michael@0: History.DATE_MODIFIED + " INTEGER," + michael@0: History.GUID + " TEXT NOT NULL," + michael@0: History.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX history_url_index ON " + TABLE_HISTORY + "(" michael@0: + History.URL + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX history_guid_index ON " + TABLE_HISTORY + "(" michael@0: + History.GUID + ")"); michael@0: db.execSQL("CREATE INDEX history_modified_index ON " + TABLE_HISTORY + "(" michael@0: + History.DATE_MODIFIED + ")"); michael@0: db.execSQL("CREATE INDEX history_visited_index ON " + TABLE_HISTORY + "(" michael@0: + History.DATE_LAST_VISITED + ")"); michael@0: } michael@0: michael@0: private void createImagesTable(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.TABLE_IMAGES + " table"); michael@0: db.execSQL("CREATE TABLE " + Obsolete.TABLE_IMAGES + " (" + michael@0: Obsolete.Images._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: Obsolete.Images.URL + " TEXT UNIQUE NOT NULL," + michael@0: Obsolete.Images.FAVICON + " BLOB," + michael@0: Obsolete.Images.FAVICON_URL + " TEXT," + michael@0: Obsolete.Images.THUMBNAIL + " BLOB," + michael@0: Obsolete.Images.DATE_CREATED + " INTEGER," + michael@0: Obsolete.Images.DATE_MODIFIED + " INTEGER," + michael@0: Obsolete.Images.GUID + " TEXT NOT NULL," + michael@0: Obsolete.Images.IS_DELETED + " INTEGER NOT NULL DEFAULT 0" + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX images_url_index ON " + Obsolete.TABLE_IMAGES + "(" michael@0: + Obsolete.Images.URL + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX images_guid_index ON " + Obsolete.TABLE_IMAGES + "(" michael@0: + Obsolete.Images.GUID + ")"); michael@0: db.execSQL("CREATE INDEX images_modified_index ON " + Obsolete.TABLE_IMAGES + "(" michael@0: + Obsolete.Images.DATE_MODIFIED + ")"); michael@0: } michael@0: michael@0: private void createFaviconsTable(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_FAVICONS + " table"); michael@0: db.execSQL("CREATE TABLE " + TABLE_FAVICONS + " (" + michael@0: Favicons._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: Favicons.URL + " TEXT UNIQUE," + michael@0: Favicons.DATA + " BLOB," + michael@0: Favicons.DATE_CREATED + " INTEGER," + michael@0: Favicons.DATE_MODIFIED + " INTEGER" + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX favicons_url_index ON " + TABLE_FAVICONS + "(" michael@0: + Favicons.URL + ")"); michael@0: db.execSQL("CREATE INDEX favicons_modified_index ON " + TABLE_FAVICONS + "(" michael@0: + Favicons.DATE_MODIFIED + ")"); michael@0: } michael@0: michael@0: private void createThumbnailsTable(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_THUMBNAILS + " table"); michael@0: db.execSQL("CREATE TABLE " + TABLE_THUMBNAILS + " (" + michael@0: Thumbnails._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + michael@0: Thumbnails.URL + " TEXT UNIQUE," + michael@0: Thumbnails.DATA + " BLOB" + michael@0: ");"); michael@0: michael@0: db.execSQL("CREATE INDEX thumbnails_url_index ON " + TABLE_THUMBNAILS + "(" michael@0: + Thumbnails.URL + ")"); michael@0: } michael@0: michael@0: private void createBookmarksWithImagesView(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES + " AS " + michael@0: "SELECT " + qualifyColumn(TABLE_BOOKMARKS, "*") + michael@0: ", " + Obsolete.Images.FAVICON + ", " + Obsolete.Images.THUMBNAIL + " FROM " + michael@0: Obsolete.TABLE_BOOKMARKS_JOIN_IMAGES); michael@0: } michael@0: michael@0: private void createBookmarksWithFaviconsView(SQLiteDatabase db) { michael@0: debug("Creating " + VIEW_BOOKMARKS_WITH_FAVICONS + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_BOOKMARKS_WITH_FAVICONS + " AS " + michael@0: "SELECT " + qualifyColumn(TABLE_BOOKMARKS, "*") + michael@0: ", " + qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Bookmarks.FAVICON + michael@0: ", " + qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Bookmarks.FAVICON_URL + michael@0: " FROM " + TABLE_BOOKMARKS_JOIN_FAVICONS); michael@0: } michael@0: michael@0: private void createHistoryWithImagesView(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_HISTORY_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES + " AS " + michael@0: "SELECT " + qualifyColumn(TABLE_HISTORY, "*") + michael@0: ", " + Obsolete.Images.FAVICON + ", " + Obsolete.Images.THUMBNAIL + " FROM " + michael@0: Obsolete.TABLE_HISTORY_JOIN_IMAGES); michael@0: } michael@0: michael@0: private void createHistoryWithFaviconsView(SQLiteDatabase db) { michael@0: debug("Creating " + VIEW_HISTORY_WITH_FAVICONS + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_HISTORY_WITH_FAVICONS + " AS " + michael@0: "SELECT " + qualifyColumn(TABLE_HISTORY, "*") + michael@0: ", " + qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + History.FAVICON + michael@0: ", " + qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + History.FAVICON_URL + michael@0: " FROM " + TABLE_HISTORY_JOIN_FAVICONS); michael@0: } michael@0: michael@0: private void createCombinedWithImagesView(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritze bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" + michael@0: ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES + michael@0: " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL)); michael@0: } michael@0: michael@0: private void createCombinedWithImagesViewOn9(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritze bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" + michael@0: ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES + michael@0: " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL)); michael@0: } michael@0: michael@0: private void createCombinedWithImagesViewOn10(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritze bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ")" + michael@0: ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES + michael@0: " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL)); michael@0: } michael@0: michael@0: private void createCombinedWithImagesViewOn11(SQLiteDatabase db) { michael@0: debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritze bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: // Only use DISPLAY_READER if the matching bookmark entry inside reading michael@0: // list folder is not marked as deleted. michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID + michael@0: " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " + michael@0: ") LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES + michael@0: " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL)); michael@0: } michael@0: michael@0: private void createCombinedViewOn12(SQLiteDatabase db) { michael@0: debug("Creating " + VIEW_COMBINED + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritze bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: // Only use DISPLAY_READER if the matching bookmark entry inside reading michael@0: // list folder is not marked as deleted. michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID + michael@0: " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " + michael@0: ")"); michael@0: michael@0: debug("Creating " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES + " AS" + michael@0: " SELECT *, " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.FAVICON) + " AS " + Combined.FAVICON + ", " + michael@0: qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.THUMBNAIL) + " AS " + Obsolete.Combined.THUMBNAIL + michael@0: " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + Obsolete.TABLE_IMAGES + michael@0: " ON " + Combined.URL + " = " + qualifyColumn(Obsolete.TABLE_IMAGES, Obsolete.Images.URL)); michael@0: } michael@0: michael@0: private void createCombinedViewOn13(SQLiteDatabase db) { michael@0: debug("Creating " + VIEW_COMBINED + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: Combined.FAVICON_ID + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " AS " + Combined.FAVICON_ID + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritize bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: // Only use DISPLAY_READER if the matching bookmark entry inside reading michael@0: // list folder is not marked as deleted. michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID + michael@0: " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " AS " + Combined.FAVICON_ID + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " + michael@0: ")"); michael@0: michael@0: debug("Creating " + VIEW_COMBINED_WITH_FAVICONS + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED_WITH_FAVICONS + " AS" + michael@0: " SELECT " + qualifyColumn(VIEW_COMBINED, "*") + ", " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Combined.FAVICON_URL + ", " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Combined.FAVICON + michael@0: " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + TABLE_FAVICONS + michael@0: " ON " + Combined.FAVICON_ID + " = " + qualifyColumn(TABLE_FAVICONS, Favicons._ID)); michael@0: } michael@0: michael@0: private void createCombinedViewOn16(SQLiteDatabase db) { michael@0: debug("Creating " + VIEW_COMBINED + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED + " AS" + michael@0: " SELECT " + Combined.BOOKMARK_ID + ", " + michael@0: Combined.HISTORY_ID + ", " + michael@0: // We need to return an _id column because CursorAdapter requires it for its michael@0: // default implementation for the getItemId() method. However, since michael@0: // we're not using this feature in the parts of the UI using this view, michael@0: // we can just use 0 for all rows. michael@0: "0 AS " + Combined._ID + ", " + michael@0: Combined.URL + ", " + michael@0: Combined.TITLE + ", " + michael@0: Combined.VISITS + ", " + michael@0: Combined.DISPLAY + ", " + michael@0: Combined.DATE_LAST_VISITED + ", " + michael@0: Combined.FAVICON_ID + michael@0: " FROM (" + michael@0: // Bookmarks without history. michael@0: " SELECT " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " AS " + Combined.URL + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + " AS " + Combined.TITLE + ", " + michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_READING_LIST_ID + " THEN " + Combined.DISPLAY_READER + " ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: "-1 AS " + Combined.HISTORY_ID + ", " + michael@0: "-1 AS " + Combined.VISITS + ", " + michael@0: "-1 AS " + Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.FAVICON_ID) + " AS " + Combined.FAVICON_ID + michael@0: " FROM " + TABLE_BOOKMARKS + michael@0: " WHERE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + " AND " + michael@0: // Ignore pinned bookmarks. michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " <> " + Bookmarks.FIXED_PINNED_LIST_ID + " AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " = 0 AND " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + michael@0: " NOT IN (SELECT " + History.URL + " FROM " + TABLE_HISTORY + ")" + michael@0: " UNION ALL" + michael@0: // History with and without bookmark. michael@0: " SELECT " + "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN " + michael@0: // Give pinned bookmarks a NULL ID so that they're not treated as bookmarks. We can't michael@0: // completely ignore them here because they're joined with history entries we care about. michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + michael@0: Bookmarks.FIXED_PINNED_LIST_ID + " THEN NULL ELSE " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks._ID) + " END " + michael@0: "ELSE NULL END AS " + Combined.BOOKMARK_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL) + " AS " + Combined.URL + ", " + michael@0: // Prioritize bookmark titles over history titles, since the user may have michael@0: // customized the title for a bookmark. michael@0: "COALESCE(" + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TITLE) + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.TITLE) +")" + " AS " + Combined.TITLE + ", " + michael@0: // Only use DISPLAY_READER if the matching bookmark entry inside reading michael@0: // list folder is not marked as deleted. michael@0: "CASE " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.IS_DELETED) + " WHEN 0 THEN CASE " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.PARENT) + " WHEN " + Bookmarks.FIXED_READING_LIST_ID + michael@0: " THEN " + Combined.DISPLAY_READER + " ELSE " + Combined.DISPLAY_NORMAL + " END ELSE " + michael@0: Combined.DISPLAY_NORMAL + " END AS " + Combined.DISPLAY + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History._ID) + " AS " + Combined.HISTORY_ID + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.VISITS) + " AS " + Combined.VISITS + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.DATE_LAST_VISITED) + " AS " + Combined.DATE_LAST_VISITED + ", " + michael@0: qualifyColumn(TABLE_HISTORY, History.FAVICON_ID) + " AS " + Combined.FAVICON_ID + michael@0: " FROM " + TABLE_HISTORY + " LEFT OUTER JOIN " + TABLE_BOOKMARKS + michael@0: " ON " + qualifyColumn(TABLE_BOOKMARKS, Bookmarks.URL) + " = " + qualifyColumn(TABLE_HISTORY, History.URL) + michael@0: " WHERE " + qualifyColumn(TABLE_HISTORY, History.URL) + " IS NOT NULL AND " + michael@0: qualifyColumn(TABLE_HISTORY, History.IS_DELETED) + " = 0 AND (" + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " IS NULL OR " + michael@0: qualifyColumn(TABLE_BOOKMARKS, Bookmarks.TYPE) + " = " + Bookmarks.TYPE_BOOKMARK + ") " + michael@0: ")"); michael@0: michael@0: debug("Creating " + VIEW_COMBINED_WITH_FAVICONS + " view"); michael@0: michael@0: db.execSQL("CREATE VIEW IF NOT EXISTS " + VIEW_COMBINED_WITH_FAVICONS + " AS" + michael@0: " SELECT " + qualifyColumn(VIEW_COMBINED, "*") + ", " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons.URL) + " AS " + Combined.FAVICON_URL + ", " + michael@0: qualifyColumn(TABLE_FAVICONS, Favicons.DATA) + " AS " + Combined.FAVICON + michael@0: " FROM " + VIEW_COMBINED + " LEFT OUTER JOIN " + TABLE_FAVICONS + michael@0: " ON " + Combined.FAVICON_ID + " = " + qualifyColumn(TABLE_FAVICONS, Favicons._ID)); michael@0: } michael@0: michael@0: @Override michael@0: public void onCreate(SQLiteDatabase db) { michael@0: debug("Creating browser.db: " + db.getPath()); michael@0: michael@0: createBookmarksTableOn13(db); michael@0: createHistoryTableOn13(db); michael@0: createFaviconsTable(db); michael@0: createThumbnailsTable(db); michael@0: michael@0: createBookmarksWithFaviconsView(db); michael@0: createHistoryWithFaviconsView(db); michael@0: createCombinedViewOn16(db); michael@0: michael@0: createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID, michael@0: R.string.bookmarks_folder_places, 0); michael@0: michael@0: createOrUpdateAllSpecialFolders(db); michael@0: michael@0: // Create distribution bookmarks before our own default bookmarks michael@0: int pos = createDistributionBookmarks(db); michael@0: createDefaultBookmarks(db, pos); michael@0: michael@0: createReadingListTable(db); michael@0: } michael@0: michael@0: private String getLocalizedProperty(JSONObject bookmark, String property, Locale locale) throws JSONException { michael@0: // Try the full locale michael@0: String fullLocale = property + "." + locale.toString(); michael@0: if (bookmark.has(fullLocale)) { michael@0: return bookmark.getString(fullLocale); michael@0: } michael@0: // Try without a variant michael@0: if (!TextUtils.isEmpty(locale.getVariant())) { michael@0: String noVariant = fullLocale.substring(0, fullLocale.lastIndexOf("_")); michael@0: if (bookmark.has(noVariant)) { michael@0: return bookmark.getString(noVariant); michael@0: } michael@0: } michael@0: // Try just the language michael@0: String lang = property + "." + locale.getLanguage(); michael@0: if (bookmark.has(lang)) { michael@0: return bookmark.getString(lang); michael@0: } michael@0: // Default to the non-localized property name michael@0: return bookmark.getString(property); michael@0: } michael@0: michael@0: // Returns the number of bookmarks inserted in the db michael@0: private int createDistributionBookmarks(SQLiteDatabase db) { michael@0: JSONArray bookmarks = Distribution.getBookmarks(mContext); michael@0: if (bookmarks == null) { michael@0: return 0; michael@0: } michael@0: michael@0: Locale locale = Locale.getDefault(); michael@0: int pos = 0; michael@0: Integer mobileFolderId = getMobileFolderId(db); michael@0: if (mobileFolderId == null) { michael@0: Log.e(LOGTAG, "Error creating distribution bookmarks: mobileFolderId is null"); michael@0: return 0; michael@0: } michael@0: michael@0: for (int i = 0; i < bookmarks.length(); i++) { michael@0: try { michael@0: final JSONObject bookmark = bookmarks.getJSONObject(i); michael@0: michael@0: String title = getLocalizedProperty(bookmark, "title", locale); michael@0: final String url = getLocalizedProperty(bookmark, "url", locale); michael@0: createBookmark(db, title, url, pos, mobileFolderId); michael@0: michael@0: if (bookmark.has("pinned")) { michael@0: try { michael@0: // Create a fake bookmark in the hidden pinned folder to pin bookmark michael@0: // to about:home top sites. Pass pos as the pinned position to pin michael@0: // sites in the order that bookmarks are specified in bookmarks.json. michael@0: if (bookmark.getBoolean("pinned")) { michael@0: createBookmark(db, title, url, pos, Bookmarks.FIXED_PINNED_LIST_ID); michael@0: } michael@0: } catch (JSONException e) { michael@0: Log.e(LOGTAG, "Error pinning bookmark to top sites", e); michael@0: } michael@0: } michael@0: michael@0: pos++; michael@0: michael@0: // return early if there is no icon for this bookmark michael@0: if (!bookmark.has("icon")) { michael@0: continue; michael@0: } michael@0: michael@0: // create icons in a separate thread to avoid blocking about:home on startup michael@0: ThreadUtils.postToBackgroundThread(new Runnable() { michael@0: @Override michael@0: public void run() { michael@0: SQLiteDatabase db = getWritableDatabase(); michael@0: try { michael@0: String iconData = bookmark.getString("icon"); michael@0: Bitmap icon = BitmapUtils.getBitmapFromDataURI(iconData); michael@0: if (icon != null) { michael@0: createFavicon(db, url, icon); michael@0: } michael@0: } catch (JSONException e) { michael@0: Log.e(LOGTAG, "Error creating distribution bookmark icon", e); michael@0: } michael@0: } michael@0: }); michael@0: } catch (JSONException e) { michael@0: Log.e(LOGTAG, "Error creating distribution bookmark", e); michael@0: } michael@0: } michael@0: return pos; michael@0: } michael@0: michael@0: private void createReadingListTable(SQLiteDatabase db) { michael@0: debug("Creating " + TABLE_READING_LIST + " table"); michael@0: michael@0: db.execSQL("CREATE TABLE " + TABLE_READING_LIST + "(" + michael@0: ReadingListItems._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + michael@0: ReadingListItems.URL + " TEXT NOT NULL, " + michael@0: ReadingListItems.TITLE + " TEXT, " + michael@0: ReadingListItems.EXCERPT + " TEXT, " + michael@0: ReadingListItems.READ + " TINYINT DEFAULT 0, " + michael@0: ReadingListItems.IS_DELETED + " TINYINT DEFAULT 0, " + michael@0: ReadingListItems.GUID + " TEXT UNIQUE NOT NULL, " + michael@0: ReadingListItems.DATE_MODIFIED + " INTEGER NOT NULL, " + michael@0: ReadingListItems.DATE_CREATED + " INTEGER NOT NULL, " + michael@0: ReadingListItems.LENGTH + " INTEGER DEFAULT 0 ); "); michael@0: michael@0: db.execSQL("CREATE INDEX reading_list_url ON " + TABLE_READING_LIST + "(" michael@0: + ReadingListItems.URL + ")"); michael@0: db.execSQL("CREATE UNIQUE INDEX reading_list_guid ON " + TABLE_READING_LIST + "(" michael@0: + ReadingListItems.GUID + ")"); michael@0: } michael@0: michael@0: // Inserts default bookmarks, starting at a specified position michael@0: private void createDefaultBookmarks(SQLiteDatabase db, int pos) { michael@0: Class stringsClass = R.string.class; michael@0: Field[] fields = stringsClass.getFields(); michael@0: Pattern p = Pattern.compile("^bookmarkdefaults_title_"); michael@0: michael@0: Integer mobileFolderId = getMobileFolderId(db); michael@0: if (mobileFolderId == null) { michael@0: Log.e(LOGTAG, "Error creating default bookmarks: mobileFolderId is null"); michael@0: return; michael@0: } michael@0: michael@0: for (int i = 0; i < fields.length; i++) { michael@0: final String name = fields[i].getName(); michael@0: Matcher m = p.matcher(name); michael@0: if (!m.find()) { michael@0: continue; michael@0: } michael@0: try { michael@0: int titleid = fields[i].getInt(null); michael@0: String title = mContext.getString(titleid); michael@0: michael@0: Field urlField = stringsClass.getField(name.replace("_title_", "_url_")); michael@0: int urlId = urlField.getInt(null); michael@0: final String url = mContext.getString(urlId); michael@0: createBookmark(db, title, url, pos, mobileFolderId); michael@0: michael@0: // create icons in a separate thread to avoid blocking about:home on startup michael@0: ThreadUtils.postToBackgroundThread(new Runnable() { michael@0: @Override michael@0: public void run() { michael@0: SQLiteDatabase db = getWritableDatabase(); michael@0: Bitmap icon = getDefaultFaviconFromPath(name); michael@0: if (icon == null) { michael@0: icon = getDefaultFaviconFromDrawable(name); michael@0: } michael@0: if (icon != null) { michael@0: createFavicon(db, url, icon); michael@0: } michael@0: } michael@0: }); michael@0: pos++; michael@0: } catch (java.lang.IllegalAccessException ex) { michael@0: Log.e(LOGTAG, "Can't create bookmark " + name, ex); michael@0: } catch (java.lang.NoSuchFieldException ex) { michael@0: Log.e(LOGTAG, "Can't create bookmark " + name, ex); michael@0: } michael@0: } michael@0: } michael@0: michael@0: private void createBookmark(SQLiteDatabase db, String title, String url, int pos, int parent) { michael@0: ContentValues bookmarkValues = new ContentValues(); michael@0: bookmarkValues.put(Bookmarks.PARENT, parent); michael@0: michael@0: long now = System.currentTimeMillis(); michael@0: bookmarkValues.put(Bookmarks.DATE_CREATED, now); michael@0: bookmarkValues.put(Bookmarks.DATE_MODIFIED, now); michael@0: michael@0: bookmarkValues.put(Bookmarks.TITLE, title); michael@0: bookmarkValues.put(Bookmarks.URL, url); michael@0: bookmarkValues.put(Bookmarks.GUID, Utils.generateGuid()); michael@0: bookmarkValues.put(Bookmarks.POSITION, pos); michael@0: db.insertOrThrow(TABLE_BOOKMARKS, Bookmarks.TITLE, bookmarkValues); michael@0: } michael@0: michael@0: private void createFavicon(SQLiteDatabase db, String url, Bitmap icon) { michael@0: ByteArrayOutputStream stream = new ByteArrayOutputStream(); michael@0: michael@0: ContentValues iconValues = new ContentValues(); michael@0: iconValues.put(Favicons.PAGE_URL, url); michael@0: michael@0: byte[] data = null; michael@0: if (icon.compress(Bitmap.CompressFormat.PNG, 100, stream)) { michael@0: data = stream.toByteArray(); michael@0: } else { michael@0: Log.w(LOGTAG, "Favicon compression failed."); michael@0: } michael@0: iconValues.put(Favicons.DATA, data); michael@0: michael@0: insertFavicon(db, iconValues); michael@0: } michael@0: michael@0: private Bitmap getDefaultFaviconFromPath(String name) { michael@0: Class stringClass = R.string.class; michael@0: try { michael@0: // Look for a drawable with the id R.drawable.bookmarkdefaults_favicon_* michael@0: Field faviconField = stringClass.getField(name.replace("_title_", "_favicon_")); michael@0: if (faviconField == null) { michael@0: return null; michael@0: } michael@0: int faviconId = faviconField.getInt(null); michael@0: String path = mContext.getString(faviconId); michael@0: michael@0: String apkPath = mContext.getPackageResourcePath(); michael@0: File apkFile = new File(apkPath); michael@0: String bitmapPath = "jar:jar:" + apkFile.toURI() + "!/" + AppConstants.OMNIJAR_NAME + "!/" + path; michael@0: return GeckoJarReader.getBitmap(mContext.getResources(), bitmapPath); michael@0: } catch (java.lang.IllegalAccessException ex) { michael@0: Log.e(LOGTAG, "[Path] Can't create favicon " + name, ex); michael@0: } catch (java.lang.NoSuchFieldException ex) { michael@0: // If the field does not exist, that means we intend to load via a drawable michael@0: } michael@0: return null; michael@0: } michael@0: michael@0: private Bitmap getDefaultFaviconFromDrawable(String name) { michael@0: Class drawablesClass = R.drawable.class; michael@0: try { michael@0: // Look for a drawable with the id R.drawable.bookmarkdefaults_favicon_* michael@0: Field faviconField = drawablesClass.getField(name.replace("_title_", "_favicon_")); michael@0: if (faviconField == null) { michael@0: return null; michael@0: } michael@0: int faviconId = faviconField.getInt(null); michael@0: return BitmapUtils.decodeResource(mContext, faviconId); michael@0: } catch (java.lang.IllegalAccessException ex) { michael@0: Log.e(LOGTAG, "[Drawable] Can't create favicon " + name, ex); michael@0: } catch (java.lang.NoSuchFieldException ex) { michael@0: // If the field does not exist, that means we intend to load via a file path michael@0: } michael@0: return null; michael@0: } michael@0: michael@0: private void createOrUpdateAllSpecialFolders(SQLiteDatabase db) { michael@0: createOrUpdateSpecialFolder(db, Bookmarks.MOBILE_FOLDER_GUID, michael@0: R.string.bookmarks_folder_mobile, 0); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.TOOLBAR_FOLDER_GUID, michael@0: R.string.bookmarks_folder_toolbar, 1); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.MENU_FOLDER_GUID, michael@0: R.string.bookmarks_folder_menu, 2); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.TAGS_FOLDER_GUID, michael@0: R.string.bookmarks_folder_tags, 3); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.UNFILED_FOLDER_GUID, michael@0: R.string.bookmarks_folder_unfiled, 4); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.READING_LIST_FOLDER_GUID, michael@0: R.string.bookmarks_folder_reading_list, 5); michael@0: createOrUpdateSpecialFolder(db, Bookmarks.PINNED_FOLDER_GUID, michael@0: R.string.bookmarks_folder_pinned, 6); michael@0: } michael@0: michael@0: private void createOrUpdateSpecialFolder(SQLiteDatabase db, michael@0: String guid, int titleId, int position) { michael@0: ContentValues values = new ContentValues(); michael@0: values.put(Bookmarks.GUID, guid); michael@0: values.put(Bookmarks.TYPE, Bookmarks.TYPE_FOLDER); michael@0: values.put(Bookmarks.POSITION, position); michael@0: michael@0: if (guid.equals(Bookmarks.PLACES_FOLDER_GUID)) michael@0: values.put(Bookmarks._ID, Bookmarks.FIXED_ROOT_ID); michael@0: else if (guid.equals(Bookmarks.READING_LIST_FOLDER_GUID)) michael@0: values.put(Bookmarks._ID, Bookmarks.FIXED_READING_LIST_ID); michael@0: else if (guid.equals(Bookmarks.PINNED_FOLDER_GUID)) michael@0: values.put(Bookmarks._ID, Bookmarks.FIXED_PINNED_LIST_ID); michael@0: michael@0: // Set the parent to 0, which sync assumes is the root michael@0: values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID); michael@0: michael@0: String title = mContext.getResources().getString(titleId); michael@0: values.put(Bookmarks.TITLE, title); michael@0: michael@0: long now = System.currentTimeMillis(); michael@0: values.put(Bookmarks.DATE_CREATED, now); michael@0: values.put(Bookmarks.DATE_MODIFIED, now); michael@0: michael@0: int updated = db.update(TABLE_BOOKMARKS, values, michael@0: Bookmarks.GUID + " = ?", michael@0: new String[] { guid }); michael@0: michael@0: if (updated == 0) { michael@0: db.insert(TABLE_BOOKMARKS, Bookmarks.GUID, values); michael@0: debug("Inserted special folder: " + guid); michael@0: } else { michael@0: debug("Updated special folder: " + guid); michael@0: } michael@0: } michael@0: michael@0: private boolean isSpecialFolder(ContentValues values) { michael@0: String guid = values.getAsString(Bookmarks.GUID); michael@0: if (guid == null) michael@0: return false; michael@0: michael@0: return guid.equals(Bookmarks.MOBILE_FOLDER_GUID) || michael@0: guid.equals(Bookmarks.MENU_FOLDER_GUID) || michael@0: guid.equals(Bookmarks.TOOLBAR_FOLDER_GUID) || michael@0: guid.equals(Bookmarks.UNFILED_FOLDER_GUID) || michael@0: guid.equals(Bookmarks.TAGS_FOLDER_GUID); michael@0: } michael@0: michael@0: private void migrateBookmarkFolder(SQLiteDatabase db, int folderId, michael@0: BookmarkMigrator migrator) { michael@0: Cursor c = null; michael@0: michael@0: debug("Migrating bookmark folder with id = " + folderId); michael@0: michael@0: String selection = Bookmarks.PARENT + " = " + folderId; michael@0: String[] selectionArgs = null; michael@0: michael@0: boolean isRootFolder = (folderId == Bookmarks.FIXED_ROOT_ID); michael@0: michael@0: // If we're loading the root folder, we have to account for michael@0: // any previously created special folder that was created without michael@0: // setting a parent id (e.g. mobile folder) and making sure we're michael@0: // not adding any infinite recursion as root's parent is root itself. michael@0: if (isRootFolder) { michael@0: selection = Bookmarks.GUID + " != ?" + " AND (" + michael@0: selection + " OR " + Bookmarks.PARENT + " = NULL)"; michael@0: selectionArgs = new String[] { Bookmarks.PLACES_FOLDER_GUID }; michael@0: } michael@0: michael@0: List subFolders = new ArrayList(); michael@0: List invalidSpecialEntries = new ArrayList(); michael@0: michael@0: try { michael@0: c = db.query(TABLE_BOOKMARKS_TMP, michael@0: null, michael@0: selection, michael@0: selectionArgs, michael@0: null, null, null); michael@0: michael@0: // The key point here is that bookmarks should be added in michael@0: // parent order to avoid any problems with the foreign key michael@0: // in Bookmarks.PARENT. michael@0: while (c.moveToNext()) { michael@0: ContentValues values = new ContentValues(); michael@0: michael@0: // We're using a null projection in the query which michael@0: // means we're getting all columns from the table. michael@0: // It's safe to simply transform the row into the michael@0: // values to be inserted on the new table. michael@0: DatabaseUtils.cursorRowToContentValues(c, values); michael@0: michael@0: boolean isSpecialFolder = isSpecialFolder(values); michael@0: michael@0: // The mobile folder used to be created with PARENT = NULL. michael@0: // We want fix that here. michael@0: if (values.getAsLong(Bookmarks.PARENT) == null && isSpecialFolder) michael@0: values.put(Bookmarks.PARENT, Bookmarks.FIXED_ROOT_ID); michael@0: michael@0: if (isRootFolder && !isSpecialFolder) { michael@0: invalidSpecialEntries.add(values); michael@0: continue; michael@0: } michael@0: michael@0: if (migrator != null) michael@0: migrator.updateForNewTable(values); michael@0: michael@0: debug("Migrating bookmark: " + values.getAsString(Bookmarks.TITLE)); michael@0: db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values); michael@0: michael@0: Integer type = values.getAsInteger(Bookmarks.TYPE); michael@0: if (type != null && type == Bookmarks.TYPE_FOLDER) michael@0: subFolders.add(values.getAsInteger(Bookmarks._ID)); michael@0: } michael@0: } finally { michael@0: if (c != null) michael@0: c.close(); michael@0: } michael@0: michael@0: // At this point is safe to assume that the mobile folder is michael@0: // in the new table given that we've always created it on michael@0: // database creation time. michael@0: final int nInvalidSpecialEntries = invalidSpecialEntries.size(); michael@0: if (nInvalidSpecialEntries > 0) { michael@0: Integer mobileFolderId = getMobileFolderId(db); michael@0: if (mobileFolderId == null) { michael@0: Log.e(LOGTAG, "Error migrating invalid special folder entries: mobile folder id is null"); michael@0: return; michael@0: } michael@0: michael@0: debug("Found " + nInvalidSpecialEntries + " invalid special folder entries"); michael@0: for (int i = 0; i < nInvalidSpecialEntries; i++) { michael@0: ContentValues values = invalidSpecialEntries.get(i); michael@0: values.put(Bookmarks.PARENT, mobileFolderId); michael@0: michael@0: db.insert(TABLE_BOOKMARKS, Bookmarks.URL, values); michael@0: } michael@0: } michael@0: michael@0: final int nSubFolders = subFolders.size(); michael@0: for (int i = 0; i < nSubFolders; i++) { michael@0: int subFolderId = subFolders.get(i); michael@0: migrateBookmarkFolder(db, subFolderId, migrator); michael@0: } michael@0: } michael@0: michael@0: private void migrateBookmarksTable(SQLiteDatabase db) { michael@0: migrateBookmarksTable(db, null); michael@0: } michael@0: michael@0: private void migrateBookmarksTable(SQLiteDatabase db, BookmarkMigrator migrator) { michael@0: debug("Renaming bookmarks table to " + TABLE_BOOKMARKS_TMP); michael@0: db.execSQL("ALTER TABLE " + TABLE_BOOKMARKS + michael@0: " RENAME TO " + TABLE_BOOKMARKS_TMP); michael@0: michael@0: debug("Dropping views and indexes related to " + TABLE_BOOKMARKS); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES); michael@0: michael@0: db.execSQL("DROP INDEX IF EXISTS bookmarks_url_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS bookmarks_type_deleted_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS bookmarks_guid_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS bookmarks_modified_index"); michael@0: michael@0: createBookmarksTable(db); michael@0: createBookmarksWithImagesView(db); michael@0: michael@0: createOrUpdateSpecialFolder(db, Bookmarks.PLACES_FOLDER_GUID, michael@0: R.string.bookmarks_folder_places, 0); michael@0: michael@0: migrateBookmarkFolder(db, Bookmarks.FIXED_ROOT_ID, migrator); michael@0: michael@0: // Ensure all special folders exist and have the michael@0: // right folder hierarchy. michael@0: createOrUpdateAllSpecialFolders(db); michael@0: michael@0: debug("Dropping bookmarks temporary table"); michael@0: db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOKMARKS_TMP); michael@0: } michael@0: michael@0: michael@0: private void migrateHistoryTable(SQLiteDatabase db) { michael@0: debug("Renaming history table to " + TABLE_HISTORY_TMP); michael@0: db.execSQL("ALTER TABLE " + TABLE_HISTORY + michael@0: " RENAME TO " + TABLE_HISTORY_TMP); michael@0: michael@0: debug("Dropping views and indexes related to " + TABLE_HISTORY); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: db.execSQL("DROP INDEX IF EXISTS history_url_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS history_guid_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS history_modified_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS history_visited_index"); michael@0: michael@0: createHistoryTable(db); michael@0: createHistoryWithImagesView(db); michael@0: createCombinedWithImagesView(db); michael@0: michael@0: db.execSQL("INSERT INTO " + TABLE_HISTORY + " SELECT * FROM " + TABLE_HISTORY_TMP); michael@0: michael@0: debug("Dropping history temporary table"); michael@0: db.execSQL("DROP TABLE IF EXISTS " + TABLE_HISTORY_TMP); michael@0: } michael@0: michael@0: private void migrateImagesTable(SQLiteDatabase db) { michael@0: debug("Renaming images table to " + TABLE_IMAGES_TMP); michael@0: db.execSQL("ALTER TABLE " + Obsolete.TABLE_IMAGES + michael@0: " RENAME TO " + TABLE_IMAGES_TMP); michael@0: michael@0: debug("Dropping views and indexes related to " + Obsolete.TABLE_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: db.execSQL("DROP INDEX IF EXISTS images_url_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS images_guid_index"); michael@0: db.execSQL("DROP INDEX IF EXISTS images_modified_index"); michael@0: michael@0: createImagesTable(db); michael@0: createHistoryWithImagesView(db); michael@0: createCombinedWithImagesView(db); michael@0: michael@0: db.execSQL("INSERT INTO " + Obsolete.TABLE_IMAGES + " SELECT * FROM " + TABLE_IMAGES_TMP); michael@0: michael@0: debug("Dropping images temporary table"); michael@0: db.execSQL("DROP TABLE IF EXISTS " + TABLE_IMAGES_TMP); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom1to2(SQLiteDatabase db) { michael@0: migrateBookmarksTable(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom2to3(SQLiteDatabase db) { michael@0: debug("Dropping view: " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES); michael@0: michael@0: createBookmarksWithImagesView(db); michael@0: michael@0: debug("Dropping view: " + Obsolete.VIEW_HISTORY_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES); michael@0: michael@0: createHistoryWithImagesView(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom3to4(SQLiteDatabase db) { michael@0: migrateBookmarksTable(db, new BookmarkMigrator3to4()); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom4to5(SQLiteDatabase db) { michael@0: createCombinedWithImagesView(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom5to6(SQLiteDatabase db) { michael@0: debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: createCombinedWithImagesView(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom6to7(SQLiteDatabase db) { michael@0: debug("Removing history visits with NULL GUIDs"); michael@0: db.execSQL("DELETE FROM " + TABLE_HISTORY + " WHERE " + History.GUID + " IS NULL"); michael@0: michael@0: debug("Update images with NULL GUIDs"); michael@0: String[] columns = new String[] { Obsolete.Images._ID }; michael@0: Cursor cursor = null; michael@0: try { michael@0: cursor = db.query(Obsolete.TABLE_IMAGES, columns, Obsolete.Images.GUID + " IS NULL", null, null ,null, null, null); michael@0: ContentValues values = new ContentValues(); michael@0: if (cursor.moveToFirst()) { michael@0: do { michael@0: values.put(Obsolete.Images.GUID, Utils.generateGuid()); michael@0: db.update(Obsolete.TABLE_IMAGES, values, Obsolete.Images._ID + " = ?", new String[] { michael@0: cursor.getString(cursor.getColumnIndexOrThrow(Obsolete.Images._ID)) michael@0: }); michael@0: } while (cursor.moveToNext()); michael@0: } michael@0: } finally { michael@0: if (cursor != null) michael@0: cursor.close(); michael@0: } michael@0: michael@0: migrateBookmarksTable(db); michael@0: migrateHistoryTable(db); michael@0: migrateImagesTable(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom7to8(SQLiteDatabase db) { michael@0: debug("Combining history entries with the same URL"); michael@0: michael@0: final String TABLE_DUPES = "duped_urls"; michael@0: final String TOTAL = "total"; michael@0: final String LATEST = "latest"; michael@0: final String WINNER = "winner"; michael@0: michael@0: db.execSQL("CREATE TEMP TABLE " + TABLE_DUPES + " AS" + michael@0: " SELECT " + History.URL + ", " + michael@0: "SUM(" + History.VISITS + ") AS " + TOTAL + ", " + michael@0: "MAX(" + History.DATE_MODIFIED + ") AS " + LATEST + ", " + michael@0: "MAX(" + History._ID + ") AS " + WINNER + michael@0: " FROM " + TABLE_HISTORY + michael@0: " GROUP BY " + History.URL + michael@0: " HAVING count(" + History.URL + ") > 1"); michael@0: michael@0: db.execSQL("CREATE UNIQUE INDEX " + TABLE_DUPES + "_url_index ON " + michael@0: TABLE_DUPES + " (" + History.URL + ")"); michael@0: michael@0: final String fromClause = " FROM " + TABLE_DUPES + " WHERE " + michael@0: qualifyColumn(TABLE_DUPES, History.URL) + " = " + michael@0: qualifyColumn(TABLE_HISTORY, History.URL); michael@0: michael@0: db.execSQL("UPDATE " + TABLE_HISTORY + michael@0: " SET " + History.VISITS + " = (SELECT " + TOTAL + fromClause + "), " + michael@0: History.DATE_MODIFIED + " = (SELECT " + LATEST + fromClause + "), " + michael@0: History.IS_DELETED + " = " + michael@0: "(" + History._ID + " <> (SELECT " + WINNER + fromClause + "))" + michael@0: " WHERE " + History.URL + " IN (SELECT " + History.URL + " FROM " + TABLE_DUPES + ")"); michael@0: michael@0: db.execSQL("DROP TABLE " + TABLE_DUPES); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom8to9(SQLiteDatabase db) { michael@0: createOrUpdateSpecialFolder(db, Bookmarks.READING_LIST_FOLDER_GUID, michael@0: R.string.bookmarks_folder_reading_list, 5); michael@0: michael@0: debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: createCombinedWithImagesViewOn9(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom9to10(SQLiteDatabase db) { michael@0: debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: createCombinedWithImagesViewOn10(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom10to11(SQLiteDatabase db) { michael@0: debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: db.execSQL("CREATE INDEX bookmarks_type_deleted_index ON " + TABLE_BOOKMARKS + "(" michael@0: + Bookmarks.TYPE + ", " + Bookmarks.IS_DELETED + ")"); michael@0: michael@0: createCombinedWithImagesViewOn11(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom11to12(SQLiteDatabase db) { michael@0: debug("Dropping view: " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: michael@0: createCombinedViewOn12(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom12to13(SQLiteDatabase db) { michael@0: // Update images table with favicon URLs michael@0: SQLiteDatabase faviconsDb = null; michael@0: Cursor c = null; michael@0: try { michael@0: final String FAVICON_TABLE = "favicon_urls"; michael@0: final String FAVICON_URL = "favicon_url"; michael@0: final String FAVICON_PAGE = "page_url"; michael@0: michael@0: String dbPath = mContext.getDatabasePath(Obsolete.FAVICON_DB).getPath(); michael@0: faviconsDb = SQLiteDatabase.openDatabase(dbPath, null, SQLiteDatabase.OPEN_READONLY); michael@0: String[] columns = new String[] { FAVICON_URL, FAVICON_PAGE }; michael@0: c = faviconsDb.query(FAVICON_TABLE, columns, null, null, null, null, null, null); michael@0: int faviconIndex = c.getColumnIndexOrThrow(FAVICON_URL); michael@0: int pageIndex = c.getColumnIndexOrThrow(FAVICON_PAGE); michael@0: while (c.moveToNext()) { michael@0: ContentValues values = new ContentValues(1); michael@0: String faviconUrl = c.getString(faviconIndex); michael@0: String pageUrl = c.getString(pageIndex); michael@0: values.put(FAVICON_URL, faviconUrl); michael@0: db.update(Obsolete.TABLE_IMAGES, values, Obsolete.Images.URL + " = ?", new String[] { pageUrl }); michael@0: } michael@0: } catch (SQLException e) { michael@0: // If we can't read from the database for some reason, we won't michael@0: // be able to import the favicon URLs. This isn't a fatal michael@0: // error, so continue the upgrade. michael@0: Log.e(LOGTAG, "Exception importing from " + Obsolete.FAVICON_DB, e); michael@0: } finally { michael@0: if (c != null) michael@0: c.close(); michael@0: if (faviconsDb != null) michael@0: faviconsDb.close(); michael@0: } michael@0: michael@0: createFaviconsTable(db); michael@0: michael@0: // Import favicons into the favicons table michael@0: db.execSQL("ALTER TABLE " + TABLE_HISTORY michael@0: + " ADD COLUMN " + History.FAVICON_ID + " INTEGER"); michael@0: db.execSQL("ALTER TABLE " + TABLE_BOOKMARKS michael@0: + " ADD COLUMN " + Bookmarks.FAVICON_ID + " INTEGER"); michael@0: michael@0: try { michael@0: c = db.query(Obsolete.TABLE_IMAGES, michael@0: new String[] { michael@0: Obsolete.Images.URL, michael@0: Obsolete.Images.FAVICON_URL, michael@0: Obsolete.Images.FAVICON, michael@0: Obsolete.Images.DATE_MODIFIED, michael@0: Obsolete.Images.DATE_CREATED michael@0: }, michael@0: Obsolete.Images.FAVICON + " IS NOT NULL", michael@0: null, null, null, null); michael@0: michael@0: while (c.moveToNext()) { michael@0: long faviconId = -1; michael@0: int faviconUrlIndex = c.getColumnIndexOrThrow(Obsolete.Images.FAVICON_URL); michael@0: String faviconUrl = null; michael@0: if (!c.isNull(faviconUrlIndex)) { michael@0: faviconUrl = c.getString(faviconUrlIndex); michael@0: Cursor c2 = null; michael@0: try { michael@0: c2 = db.query(TABLE_FAVICONS, michael@0: new String[] { Favicons._ID }, michael@0: Favicons.URL + " = ?", michael@0: new String[] { faviconUrl }, michael@0: null, null, null); michael@0: if (c2.moveToFirst()) { michael@0: faviconId = c2.getLong(c2.getColumnIndexOrThrow(Favicons._ID)); michael@0: } michael@0: } finally { michael@0: if (c2 != null) michael@0: c2.close(); michael@0: } michael@0: } michael@0: michael@0: if (faviconId == -1) { michael@0: ContentValues values = new ContentValues(4); michael@0: values.put(Favicons.URL, faviconUrl); michael@0: values.put(Favicons.DATA, c.getBlob(c.getColumnIndexOrThrow(Obsolete.Images.FAVICON))); michael@0: values.put(Favicons.DATE_MODIFIED, c.getLong(c.getColumnIndexOrThrow(Obsolete.Images.DATE_MODIFIED))); michael@0: values.put(Favicons.DATE_CREATED, c.getLong(c.getColumnIndexOrThrow(Obsolete.Images.DATE_CREATED))); michael@0: faviconId = db.insert(TABLE_FAVICONS, null, values); michael@0: } michael@0: michael@0: ContentValues values = new ContentValues(1); michael@0: values.put(FaviconColumns.FAVICON_ID, faviconId); michael@0: db.update(TABLE_HISTORY, values, History.URL + " = ?", michael@0: new String[] { c.getString(c.getColumnIndexOrThrow(Obsolete.Images.URL)) }); michael@0: db.update(TABLE_BOOKMARKS, values, Bookmarks.URL + " = ?", michael@0: new String[] { c.getString(c.getColumnIndexOrThrow(Obsolete.Images.URL)) }); michael@0: } michael@0: } finally { michael@0: if (c != null) michael@0: c.close(); michael@0: } michael@0: michael@0: createThumbnailsTable(db); michael@0: michael@0: // Import thumbnails into the thumbnails table michael@0: db.execSQL("INSERT INTO " + TABLE_THUMBNAILS + " (" michael@0: + Thumbnails.URL + ", " michael@0: + Thumbnails.DATA + ") " michael@0: + "SELECT " + Obsolete.Images.URL + ", " + Obsolete.Images.THUMBNAIL michael@0: + " FROM " + Obsolete.TABLE_IMAGES michael@0: + " WHERE " + Obsolete.Images.THUMBNAIL + " IS NOT NULL"); michael@0: michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_BOOKMARKS_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_HISTORY_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + Obsolete.VIEW_COMBINED_WITH_IMAGES); michael@0: db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED); michael@0: michael@0: createBookmarksWithFaviconsView(db); michael@0: createHistoryWithFaviconsView(db); michael@0: createCombinedViewOn13(db); michael@0: michael@0: db.execSQL("DROP TABLE IF EXISTS " + Obsolete.TABLE_IMAGES); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom13to14(SQLiteDatabase db) { michael@0: createOrUpdateSpecialFolder(db, Bookmarks.PINNED_FOLDER_GUID, michael@0: R.string.bookmarks_folder_pinned, 6); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom14to15(SQLiteDatabase db) { michael@0: Cursor c = null; michael@0: try { michael@0: // Get all the pinned bookmarks michael@0: c = db.query(TABLE_BOOKMARKS, michael@0: new String[] { Bookmarks._ID, Bookmarks.URL }, michael@0: Bookmarks.PARENT + " = ?", michael@0: new String[] { Integer.toString(Bookmarks.FIXED_PINNED_LIST_ID) }, michael@0: null, null, null); michael@0: michael@0: while (c.moveToNext()) { michael@0: // Check if this URL can be parsed as a URI with a valid scheme. michael@0: String url = c.getString(c.getColumnIndexOrThrow(Bookmarks.URL)); michael@0: if (Uri.parse(url).getScheme() != null) { michael@0: continue; michael@0: } michael@0: michael@0: // If it can't, update the URL to be an encoded "user-entered" value. michael@0: ContentValues values = new ContentValues(1); michael@0: String newUrl = Uri.fromParts("user-entered", url, null).toString(); michael@0: values.put(Bookmarks.URL, newUrl); michael@0: db.update(TABLE_BOOKMARKS, values, Bookmarks._ID + " = ?", michael@0: new String[] { Integer.toString(c.getInt(c.getColumnIndexOrThrow(Bookmarks._ID))) }); michael@0: } michael@0: } finally { michael@0: if (c != null) { michael@0: c.close(); michael@0: } michael@0: } michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom15to16(SQLiteDatabase db) { michael@0: db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED); michael@0: db.execSQL("DROP VIEW IF EXISTS " + VIEW_COMBINED_WITH_FAVICONS); michael@0: michael@0: createCombinedViewOn16(db); michael@0: } michael@0: michael@0: private void upgradeDatabaseFrom16to17(SQLiteDatabase db) { michael@0: // Purge any 0-byte favicons/thumbnails michael@0: try { michael@0: db.execSQL("DELETE FROM " + TABLE_FAVICONS + michael@0: " WHERE length(" + Favicons.DATA + ") = 0"); michael@0: db.execSQL("DELETE FROM " + TABLE_THUMBNAILS + michael@0: " WHERE length(" + Thumbnails.DATA + ") = 0"); michael@0: } catch (SQLException e) { michael@0: Log.e(LOGTAG, "Error purging invalid favicons or thumbnails", e); michael@0: } michael@0: } michael@0: michael@0: /* michael@0: * Moves reading list items from 'bookmarks' table to 'reading_list' table. Uses the michael@0: * same item GUID. michael@0: */ michael@0: private void upgradeDatabaseFrom17to18(SQLiteDatabase db) { michael@0: debug("Moving reading list items from 'bookmarks' table to 'reading_list' table"); michael@0: michael@0: final String selection = Bookmarks.PARENT + " = ? AND " + Bookmarks.IS_DELETED + " = ? "; michael@0: final String[] selectionArgs = { String.valueOf(Bookmarks.FIXED_READING_LIST_ID), "0" }; michael@0: final String[] projection = { Bookmarks._ID, michael@0: Bookmarks.GUID, michael@0: Bookmarks.URL, michael@0: Bookmarks.DATE_MODIFIED, michael@0: Bookmarks.DATE_CREATED, michael@0: Bookmarks.TITLE }; michael@0: Cursor cursor = null; michael@0: try { michael@0: // Start transaction michael@0: db.beginTransaction(); michael@0: michael@0: // Create 'reading_list' table michael@0: createReadingListTable(db); michael@0: michael@0: // Get all the reading list items from bookmarks table michael@0: cursor = db.query(TABLE_BOOKMARKS, projection, selection, selectionArgs, michael@0: null, null, null); michael@0: michael@0: // Insert reading list items into reading_list table michael@0: while (cursor.moveToNext()) { michael@0: debug(DatabaseUtils.dumpCurrentRowToString(cursor)); michael@0: ContentValues values = new ContentValues(); michael@0: DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.URL, values, ReadingListItems.URL); michael@0: DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.GUID, values, ReadingListItems.GUID); michael@0: DatabaseUtils.cursorStringToContentValues(cursor, Bookmarks.TITLE, values, ReadingListItems.TITLE); michael@0: DatabaseUtils.cursorLongToContentValues(cursor, Bookmarks.DATE_CREATED, values, ReadingListItems.DATE_CREATED); michael@0: DatabaseUtils.cursorLongToContentValues(cursor, Bookmarks.DATE_MODIFIED, values, ReadingListItems.DATE_MODIFIED); michael@0: michael@0: db.insertOrThrow(TABLE_READING_LIST, null, values); michael@0: } michael@0: michael@0: // Delete reading list items from bookmarks table michael@0: db.delete(TABLE_BOOKMARKS, michael@0: Bookmarks.PARENT + " = ? ", michael@0: new String[] { String.valueOf(Bookmarks.FIXED_READING_LIST_ID) }); michael@0: michael@0: // Delete reading list special folder michael@0: db.delete(TABLE_BOOKMARKS, michael@0: Bookmarks._ID + " = ? ", michael@0: new String[] { String.valueOf(Bookmarks.FIXED_READING_LIST_ID) }); michael@0: // Done michael@0: db.setTransactionSuccessful(); michael@0: michael@0: } catch (SQLException e) { michael@0: Log.e(LOGTAG, "Error migrating reading list items", e); michael@0: } finally { michael@0: if (cursor != null) { michael@0: cursor.close(); michael@0: } michael@0: db.endTransaction(); michael@0: } michael@0: } michael@0: michael@0: @Override michael@0: public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { michael@0: debug("Upgrading browser.db: " + db.getPath() + " from " + michael@0: oldVersion + " to " + newVersion); michael@0: michael@0: // We have to do incremental upgrades until we reach the current michael@0: // database schema version. michael@0: for (int v = oldVersion + 1; v <= newVersion; v++) { michael@0: switch(v) { michael@0: case 2: michael@0: upgradeDatabaseFrom1to2(db); michael@0: break; michael@0: michael@0: case 3: michael@0: upgradeDatabaseFrom2to3(db); michael@0: break; michael@0: michael@0: case 4: michael@0: upgradeDatabaseFrom3to4(db); michael@0: break; michael@0: michael@0: case 5: michael@0: upgradeDatabaseFrom4to5(db); michael@0: break; michael@0: michael@0: case 6: michael@0: upgradeDatabaseFrom5to6(db); michael@0: break; michael@0: michael@0: case 7: michael@0: upgradeDatabaseFrom6to7(db); michael@0: break; michael@0: michael@0: case 8: michael@0: upgradeDatabaseFrom7to8(db); michael@0: break; michael@0: michael@0: case 9: michael@0: upgradeDatabaseFrom8to9(db); michael@0: break; michael@0: michael@0: case 10: michael@0: upgradeDatabaseFrom9to10(db); michael@0: break; michael@0: michael@0: case 11: michael@0: upgradeDatabaseFrom10to11(db); michael@0: break; michael@0: michael@0: case 12: michael@0: upgradeDatabaseFrom11to12(db); michael@0: break; michael@0: michael@0: case 13: michael@0: upgradeDatabaseFrom12to13(db); michael@0: break; michael@0: michael@0: case 14: michael@0: upgradeDatabaseFrom13to14(db); michael@0: break; michael@0: michael@0: case 15: michael@0: upgradeDatabaseFrom14to15(db); michael@0: break; michael@0: michael@0: case 16: michael@0: upgradeDatabaseFrom15to16(db); michael@0: break; michael@0: michael@0: case 17: michael@0: upgradeDatabaseFrom16to17(db); michael@0: break; michael@0: michael@0: case 18: michael@0: upgradeDatabaseFrom17to18(db); michael@0: break; michael@0: } michael@0: } michael@0: michael@0: // If an upgrade after 12->13 fails, the entire upgrade is rolled michael@0: // back, but we can't undo the deletion of favicon_urls.db if we michael@0: // delete this in step 13; therefore, we wait until all steps are michael@0: // complete before removing it. michael@0: if (oldVersion < 13 && newVersion >= 13 michael@0: && mContext.getDatabasePath(Obsolete.FAVICON_DB).exists() michael@0: && !mContext.deleteDatabase(Obsolete.FAVICON_DB)) { michael@0: throw new SQLException("Could not delete " + Obsolete.FAVICON_DB); michael@0: } michael@0: } michael@0: michael@0: @Override michael@0: public void onOpen(SQLiteDatabase db) { michael@0: debug("Opening browser.db: " + db.getPath()); michael@0: michael@0: Cursor cursor = null; michael@0: try { michael@0: cursor = db.rawQuery("PRAGMA foreign_keys=ON", null); michael@0: } finally { michael@0: if (cursor != null) michael@0: cursor.close(); michael@0: } michael@0: cursor = null; michael@0: try { michael@0: cursor = db.rawQuery("PRAGMA synchronous=NORMAL", null); michael@0: } finally { michael@0: if (cursor != null) michael@0: cursor.close(); michael@0: } michael@0: michael@0: // From Honeycomb on, it's possible to run several db michael@0: // commands in parallel using multiple connections. michael@0: if (Build.VERSION.SDK_INT >= 11) { michael@0: db.enableWriteAheadLogging(); michael@0: db.setLockingEnabled(false); michael@0: } else { michael@0: // Pre-Honeycomb, we can do some lesser optimizations. michael@0: cursor = null; michael@0: try { michael@0: cursor = db.rawQuery("PRAGMA journal_mode=PERSIST", null); michael@0: } finally { michael@0: if (cursor != null) michael@0: cursor.close(); michael@0: } michael@0: } michael@0: } michael@0: michael@0: static final String qualifyColumn(String table, String column) { michael@0: return DBUtils.qualifyColumn(table, column); michael@0: } michael@0: michael@0: // Calculate these once, at initialization. isLoggable is too expensive to michael@0: // have in-line in each log call. michael@0: private static boolean logDebug = Log.isLoggable(LOGTAG, Log.DEBUG); michael@0: private static boolean logVerbose = Log.isLoggable(LOGTAG, Log.VERBOSE); michael@0: protected static void trace(String message) { michael@0: if (logVerbose) { michael@0: Log.v(LOGTAG, message); michael@0: } michael@0: } michael@0: michael@0: protected static void debug(String message) { michael@0: if (logDebug) { michael@0: Log.d(LOGTAG, message); michael@0: } michael@0: } michael@0: michael@0: private Integer getMobileFolderId(SQLiteDatabase db) { michael@0: Cursor c = null; michael@0: michael@0: try { michael@0: c = db.query(TABLE_BOOKMARKS, michael@0: mobileIdColumns, michael@0: Bookmarks.GUID + " = ?", michael@0: mobileIdSelectionArgs, michael@0: null, null, null); michael@0: michael@0: if (c == null || !c.moveToFirst()) michael@0: return null; michael@0: michael@0: return c.getInt(c.getColumnIndex(Bookmarks._ID)); michael@0: } finally { michael@0: if (c != null) michael@0: c.close(); michael@0: } michael@0: } michael@0: michael@0: private long insertFavicon(SQLiteDatabase db, ContentValues values) { michael@0: // This method is a dupicate of BrowserProvider.insertFavicon. michael@0: // If changes are needed, please update both michael@0: String faviconUrl = values.getAsString(Favicons.URL); michael@0: String pageUrl = null; michael@0: long faviconId; michael@0: michael@0: trace("Inserting favicon for URL: " + faviconUrl); michael@0: michael@0: DBUtils.stripEmptyByteArray(values, Favicons.DATA); michael@0: michael@0: // Extract the page URL from the ContentValues michael@0: if (values.containsKey(Favicons.PAGE_URL)) { michael@0: pageUrl = values.getAsString(Favicons.PAGE_URL); michael@0: values.remove(Favicons.PAGE_URL); michael@0: } michael@0: michael@0: // If no URL is provided, insert using the default one. michael@0: if (TextUtils.isEmpty(faviconUrl) && !TextUtils.isEmpty(pageUrl)) { michael@0: values.put(Favicons.URL, org.mozilla.gecko.favicons.Favicons.guessDefaultFaviconURL(pageUrl)); michael@0: } michael@0: michael@0: long now = System.currentTimeMillis(); michael@0: values.put(Favicons.DATE_CREATED, now); michael@0: values.put(Favicons.DATE_MODIFIED, now); michael@0: faviconId = db.insertOrThrow(TABLE_FAVICONS, null, values); michael@0: michael@0: if (pageUrl != null) { michael@0: ContentValues updateValues = new ContentValues(1); michael@0: updateValues.put(FaviconColumns.FAVICON_ID, faviconId); michael@0: db.update(TABLE_HISTORY, michael@0: updateValues, michael@0: History.URL + " = ?", michael@0: new String[] { pageUrl }); michael@0: db.update(TABLE_BOOKMARKS, michael@0: updateValues, michael@0: Bookmarks.URL + " = ?", michael@0: new String[] { pageUrl }); michael@0: } michael@0: michael@0: return faviconId; michael@0: } michael@0: michael@0: private interface BookmarkMigrator { michael@0: public void updateForNewTable(ContentValues bookmark); michael@0: } michael@0: michael@0: private class BookmarkMigrator3to4 implements BookmarkMigrator { michael@0: @Override michael@0: public void updateForNewTable(ContentValues bookmark) { michael@0: Integer isFolder = bookmark.getAsInteger("folder"); michael@0: if (isFolder == null || isFolder != 1) { michael@0: bookmark.put(Bookmarks.TYPE, Bookmarks.TYPE_BOOKMARK); michael@0: } else { michael@0: bookmark.put(Bookmarks.TYPE, Bookmarks.TYPE_FOLDER); michael@0: } michael@0: michael@0: bookmark.remove("folder"); michael@0: } michael@0: } michael@0: }