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
michael@0:  * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
michael@0: 
michael@0: "use strict";
michael@0: 
michael@0: const Cc = Components.classes;
michael@0: const Ci = Components.interfaces;
michael@0: const Cu = Components.utils;
michael@0: 
michael@0: Cu.import("resource://gre/modules/Services.jsm");
michael@0: Cu.import("resource://gre/modules/AddonManager.jsm");
michael@0: Cu.import("resource://gre/modules/FileUtils.jsm");
michael@0: 
michael@0: const KEY_PROFILEDIR  = "ProfD";
michael@0: const FILE_DATABASE   = "addons.sqlite";
michael@0: const LAST_DB_SCHEMA   = 4;
michael@0: 
michael@0: // Add-on properties present in the columns of the database
michael@0: const PROP_SINGLE = ["id", "type", "name", "version", "creator", "description",
michael@0:                      "fullDescription", "developerComments", "eula",
michael@0:                      "homepageURL", "supportURL", "contributionURL",
michael@0:                      "contributionAmount", "averageRating", "reviewCount",
michael@0:                      "reviewURL", "totalDownloads", "weeklyDownloads",
michael@0:                      "dailyUsers", "sourceURI", "repositoryStatus", "size",
michael@0:                      "updateDate"];
michael@0: 
michael@0: Cu.import("resource://gre/modules/Log.jsm");
michael@0: const LOGGER_ID = "addons.repository.sqlmigrator";
michael@0: 
michael@0: // Create a new logger for use by the Addons Repository SQL Migrator
michael@0: // (Requires AddonManager.jsm)
michael@0: let logger = Log.repository.getLogger(LOGGER_ID);
michael@0: 
michael@0: this.EXPORTED_SYMBOLS = ["AddonRepository_SQLiteMigrator"];
michael@0: 
michael@0: 
michael@0: this.AddonRepository_SQLiteMigrator = {
michael@0: 
michael@0:   /**
michael@0:    * Migrates data from a previous SQLite version of the
michael@0:    * database to the JSON version.
michael@0:    *
michael@0:    * @param structFunctions an object that contains functions
michael@0:    *                        to create the various objects used
michael@0:    *                        in the new JSON format
michael@0:    * @param aCallback       A callback to be called when migration
michael@0:    *                        finishes, with the results in an array
michael@0:    * @returns bool          True if a migration will happen (DB was
michael@0:    *                        found and succesfully opened)
michael@0:    */
michael@0:   migrate: function(aCallback) {
michael@0:     if (!this._openConnection()) {
michael@0:       this._closeConnection();
michael@0:       aCallback([]);
michael@0:       return false;
michael@0:     }
michael@0: 
michael@0:     logger.debug("Importing addon repository from previous " + FILE_DATABASE + " storage.");
michael@0: 
michael@0:     this._retrieveStoredData((results) => {
michael@0:       this._closeConnection();
michael@0:       let resultArray = [addon for ([,addon] of Iterator(results))];
michael@0:       logger.debug(resultArray.length + " addons imported.")
michael@0:       aCallback(resultArray);
michael@0:     });
michael@0: 
michael@0:     return true;
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Synchronously opens a new connection to the database file.
michael@0:    *
michael@0:    * @return bool           Whether the DB was opened successfully.
michael@0:    */
michael@0:   _openConnection: function AD_openConnection() {
michael@0:     delete this.connection;
michael@0: 
michael@0:     let dbfile = FileUtils.getFile(KEY_PROFILEDIR, [FILE_DATABASE], true);
michael@0:     if (!dbfile.exists())
michael@0:       return false;
michael@0: 
michael@0:     try {
michael@0:       this.connection = Services.storage.openUnsharedDatabase(dbfile);
michael@0:     } catch (e) {
michael@0:       return false;
michael@0:     }
michael@0: 
michael@0:     this.connection.executeSimpleSQL("PRAGMA locking_mode = EXCLUSIVE");
michael@0: 
michael@0:     // Any errors in here should rollback
michael@0:     try {
michael@0:       this.connection.beginTransaction();
michael@0: 
michael@0:       switch (this.connection.schemaVersion) {
michael@0:         case 0:
michael@0:           return false;
michael@0: 
michael@0:         case 1:
michael@0:           logger.debug("Upgrading database schema to version 2");
michael@0:           this.connection.executeSimpleSQL("ALTER TABLE screenshot ADD COLUMN width INTEGER");
michael@0:           this.connection.executeSimpleSQL("ALTER TABLE screenshot ADD COLUMN height INTEGER");
michael@0:           this.connection.executeSimpleSQL("ALTER TABLE screenshot ADD COLUMN thumbnailWidth INTEGER");
michael@0:           this.connection.executeSimpleSQL("ALTER TABLE screenshot ADD COLUMN thumbnailHeight INTEGER");
michael@0:         case 2:
michael@0:           logger.debug("Upgrading database schema to version 3");
michael@0:           this.connection.createTable("compatibility_override",
michael@0:                                       "addon_internal_id INTEGER, " +
michael@0:                                       "num INTEGER, " +
michael@0:                                       "type TEXT, " +
michael@0:                                       "minVersion TEXT, " +
michael@0:                                       "maxVersion TEXT, " +
michael@0:                                       "appID TEXT, " +
michael@0:                                       "appMinVersion TEXT, " +
michael@0:                                       "appMaxVersion TEXT, " +
michael@0:                                       "PRIMARY KEY (addon_internal_id, num)");
michael@0:         case 3:
michael@0:           logger.debug("Upgrading database schema to version 4");
michael@0:           this.connection.createTable("icon",
michael@0:                                       "addon_internal_id INTEGER, " +
michael@0:                                       "size INTEGER, " +
michael@0:                                       "url TEXT, " +
michael@0:                                       "PRIMARY KEY (addon_internal_id, size)");
michael@0:           this._createIndices();
michael@0:           this._createTriggers();
michael@0:           this.connection.schemaVersion = LAST_DB_SCHEMA;
michael@0:         case LAST_DB_SCHEMA:
michael@0:           break;
michael@0:         default:
michael@0:           return false;
michael@0:       }
michael@0:       this.connection.commitTransaction();
michael@0:     } catch (e) {
michael@0:       logger.error("Failed to open " + FILE_DATABASE + ". Data import will not happen.", e);
michael@0:       this.logSQLError(this.connection.lastError, this.connection.lastErrorString);
michael@0:       this.connection.rollbackTransaction();
michael@0:       return false;
michael@0:     }
michael@0: 
michael@0:     return true;
michael@0:   },
michael@0: 
michael@0:   _closeConnection: function() {
michael@0:     for each (let stmt in this.asyncStatementsCache)
michael@0:       stmt.finalize();
michael@0:     this.asyncStatementsCache = {};
michael@0: 
michael@0:     if (this.connection)
michael@0:       this.connection.asyncClose();
michael@0: 
michael@0:     delete this.connection;
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Asynchronously retrieve all add-ons from the database, and pass it
michael@0:    * to the specified callback
michael@0:    *
michael@0:    * @param  aCallback
michael@0:    *         The callback to pass the add-ons back to
michael@0:    */
michael@0:   _retrieveStoredData: function AD_retrieveStoredData(aCallback) {
michael@0:     let self = this;
michael@0:     let addons = {};
michael@0: 
michael@0:     // Retrieve all data from the addon table
michael@0:     function getAllAddons() {
michael@0:       self.getAsyncStatement("getAllAddons").executeAsync({
michael@0:         handleResult: function getAllAddons_handleResult(aResults) {
michael@0:           let row = null;
michael@0:           while ((row = aResults.getNextRow())) {
michael@0:             let internal_id = row.getResultByName("internal_id");
michael@0:             addons[internal_id] = self._makeAddonFromAsyncRow(row);
michael@0:           }
michael@0:         },
michael@0: 
michael@0:         handleError: self.asyncErrorLogger,
michael@0: 
michael@0:         handleCompletion: function getAllAddons_handleCompletion(aReason) {
michael@0:           if (aReason != Ci.mozIStorageStatementCallback.REASON_FINISHED) {
michael@0:             logger.error("Error retrieving add-ons from database. Returning empty results");
michael@0:             aCallback({});
michael@0:             return;
michael@0:           }
michael@0: 
michael@0:           getAllDevelopers();
michael@0:         }
michael@0:       });
michael@0:     }
michael@0: 
michael@0:     // Retrieve all data from the developer table
michael@0:     function getAllDevelopers() {
michael@0:       self.getAsyncStatement("getAllDevelopers").executeAsync({
michael@0:         handleResult: function getAllDevelopers_handleResult(aResults) {
michael@0:           let row = null;
michael@0:           while ((row = aResults.getNextRow())) {
michael@0:             let addon_internal_id = row.getResultByName("addon_internal_id");
michael@0:             if (!(addon_internal_id in addons)) {
michael@0:               logger.warn("Found a developer not linked to an add-on in database");
michael@0:               continue;
michael@0:             }
michael@0: 
michael@0:             let addon = addons[addon_internal_id];
michael@0:             if (!addon.developers)
michael@0:               addon.developers = [];
michael@0: 
michael@0:             addon.developers.push(self._makeDeveloperFromAsyncRow(row));
michael@0:           }
michael@0:         },
michael@0: 
michael@0:         handleError: self.asyncErrorLogger,
michael@0: 
michael@0:         handleCompletion: function getAllDevelopers_handleCompletion(aReason) {
michael@0:           if (aReason != Ci.mozIStorageStatementCallback.REASON_FINISHED) {
michael@0:             logger.error("Error retrieving developers from database. Returning empty results");
michael@0:             aCallback({});
michael@0:             return;
michael@0:           }
michael@0: 
michael@0:           getAllScreenshots();
michael@0:         }
michael@0:       });
michael@0:     }
michael@0: 
michael@0:     // Retrieve all data from the screenshot table
michael@0:     function getAllScreenshots() {
michael@0:       self.getAsyncStatement("getAllScreenshots").executeAsync({
michael@0:         handleResult: function getAllScreenshots_handleResult(aResults) {
michael@0:           let row = null;
michael@0:           while ((row = aResults.getNextRow())) {
michael@0:             let addon_internal_id = row.getResultByName("addon_internal_id");
michael@0:             if (!(addon_internal_id in addons)) {
michael@0:               logger.warn("Found a screenshot not linked to an add-on in database");
michael@0:               continue;
michael@0:             }
michael@0: 
michael@0:             let addon = addons[addon_internal_id];
michael@0:             if (!addon.screenshots)
michael@0:               addon.screenshots = [];
michael@0:             addon.screenshots.push(self._makeScreenshotFromAsyncRow(row));
michael@0:           }
michael@0:         },
michael@0: 
michael@0:         handleError: self.asyncErrorLogger,
michael@0: 
michael@0:         handleCompletion: function getAllScreenshots_handleCompletion(aReason) {
michael@0:           if (aReason != Ci.mozIStorageStatementCallback.REASON_FINISHED) {
michael@0:             logger.error("Error retrieving screenshots from database. Returning empty results");
michael@0:             aCallback({});
michael@0:             return;
michael@0:           }
michael@0: 
michael@0:           getAllCompatOverrides();
michael@0:         }
michael@0:       });
michael@0:     }
michael@0: 
michael@0:     function getAllCompatOverrides() {
michael@0:       self.getAsyncStatement("getAllCompatOverrides").executeAsync({
michael@0:         handleResult: function getAllCompatOverrides_handleResult(aResults) {
michael@0:           let row = null;
michael@0:           while ((row = aResults.getNextRow())) {
michael@0:             let addon_internal_id = row.getResultByName("addon_internal_id");
michael@0:             if (!(addon_internal_id in addons)) {
michael@0:               logger.warn("Found a compatibility override not linked to an add-on in database");
michael@0:               continue;
michael@0:             }
michael@0: 
michael@0:             let addon = addons[addon_internal_id];
michael@0:             if (!addon.compatibilityOverrides)
michael@0:               addon.compatibilityOverrides = [];
michael@0:             addon.compatibilityOverrides.push(self._makeCompatOverrideFromAsyncRow(row));
michael@0:           }
michael@0:         },
michael@0: 
michael@0:         handleError: self.asyncErrorLogger,
michael@0: 
michael@0:         handleCompletion: function getAllCompatOverrides_handleCompletion(aReason) {
michael@0:           if (aReason != Ci.mozIStorageStatementCallback.REASON_FINISHED) {
michael@0:             logger.error("Error retrieving compatibility overrides from database. Returning empty results");
michael@0:             aCallback({});
michael@0:             return;
michael@0:           }
michael@0: 
michael@0:           getAllIcons();
michael@0:         }
michael@0:       });
michael@0:     }
michael@0: 
michael@0:     function getAllIcons() {
michael@0:       self.getAsyncStatement("getAllIcons").executeAsync({
michael@0:         handleResult: function getAllIcons_handleResult(aResults) {
michael@0:           let row = null;
michael@0:           while ((row = aResults.getNextRow())) {
michael@0:             let addon_internal_id = row.getResultByName("addon_internal_id");
michael@0:             if (!(addon_internal_id in addons)) {
michael@0:               logger.warn("Found an icon not linked to an add-on in database");
michael@0:               continue;
michael@0:             }
michael@0: 
michael@0:             let addon = addons[addon_internal_id];
michael@0:             let { size, url } = self._makeIconFromAsyncRow(row);
michael@0:             addon.icons[size] = url;
michael@0:             if (size == 32)
michael@0:               addon.iconURL = url;
michael@0:           }
michael@0:         },
michael@0: 
michael@0:         handleError: self.asyncErrorLogger,
michael@0: 
michael@0:         handleCompletion: function getAllIcons_handleCompletion(aReason) {
michael@0:           if (aReason != Ci.mozIStorageStatementCallback.REASON_FINISHED) {
michael@0:             logger.error("Error retrieving icons from database. Returning empty results");
michael@0:             aCallback({});
michael@0:             return;
michael@0:           }
michael@0: 
michael@0:           let returnedAddons = {};
michael@0:           for each (let addon in addons)
michael@0:             returnedAddons[addon.id] = addon;
michael@0:           aCallback(returnedAddons);
michael@0:         }
michael@0:       });
michael@0:     }
michael@0: 
michael@0:     // Begin asynchronous process
michael@0:     getAllAddons();
michael@0:   },
michael@0: 
michael@0:   // A cache of statements that are used and need to be finalized on shutdown
michael@0:   asyncStatementsCache: {},
michael@0: 
michael@0:   /**
michael@0:    * Gets a cached async statement or creates a new statement if it doesn't
michael@0:    * already exist.
michael@0:    *
michael@0:    * @param  aKey
michael@0:    *         A unique key to reference the statement
michael@0:    * @return a mozIStorageAsyncStatement for the SQL corresponding to the
michael@0:    *         unique key
michael@0:    */
michael@0:   getAsyncStatement: function AD_getAsyncStatement(aKey) {
michael@0:     if (aKey in this.asyncStatementsCache)
michael@0:       return this.asyncStatementsCache[aKey];
michael@0: 
michael@0:     let sql = this.queries[aKey];
michael@0:     try {
michael@0:       return this.asyncStatementsCache[aKey] = this.connection.createAsyncStatement(sql);
michael@0:     } catch (e) {
michael@0:       logger.error("Error creating statement " + aKey + " (" + sql + ")");
michael@0:       throw Components.Exception("Error creating statement " + aKey + " (" + sql + "): " + e,
michael@0:                                  e.result);
michael@0:     }
michael@0:   },
michael@0: 
michael@0:   // The queries used by the database
michael@0:   queries: {
michael@0:     getAllAddons: "SELECT internal_id, id, type, name, version, " +
michael@0:                   "creator, creatorURL, description, fullDescription, " +
michael@0:                   "developerComments, eula, homepageURL, supportURL, " +
michael@0:                   "contributionURL, contributionAmount, averageRating, " +
michael@0:                   "reviewCount, reviewURL, totalDownloads, weeklyDownloads, " +
michael@0:                   "dailyUsers, sourceURI, repositoryStatus, size, updateDate " +
michael@0:                   "FROM addon",
michael@0: 
michael@0:     getAllDevelopers: "SELECT addon_internal_id, name, url FROM developer " +
michael@0:                       "ORDER BY addon_internal_id, num",
michael@0: 
michael@0:     getAllScreenshots: "SELECT addon_internal_id, url, width, height, " +
michael@0:                        "thumbnailURL, thumbnailWidth, thumbnailHeight, caption " +
michael@0:                        "FROM screenshot ORDER BY addon_internal_id, num",
michael@0: 
michael@0:     getAllCompatOverrides: "SELECT addon_internal_id, type, minVersion, " +
michael@0:                            "maxVersion, appID, appMinVersion, appMaxVersion " +
michael@0:                            "FROM compatibility_override " +
michael@0:                            "ORDER BY addon_internal_id, num",
michael@0: 
michael@0:     getAllIcons: "SELECT addon_internal_id, size, url FROM icon " +
michael@0:                  "ORDER BY addon_internal_id, size",
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Make add-on structure from an asynchronous row.
michael@0:    *
michael@0:    * @param  aRow
michael@0:    *         The asynchronous row to use
michael@0:    * @return The created add-on
michael@0:    */
michael@0:   _makeAddonFromAsyncRow: function AD__makeAddonFromAsyncRow(aRow) {
michael@0:     // This is intentionally not an AddonSearchResult object in order
michael@0:     // to allow AddonDatabase._parseAddon to parse it, same as if it
michael@0:     // was read from the JSON database.
michael@0: 
michael@0:     let addon = { icons: {} };
michael@0: 
michael@0:     for (let prop of PROP_SINGLE) {
michael@0:       addon[prop] = aRow.getResultByName(prop)
michael@0:     };
michael@0: 
michael@0:     return addon;
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Make a developer from an asynchronous row
michael@0:    *
michael@0:    * @param  aRow
michael@0:    *         The asynchronous row to use
michael@0:    * @return The created developer
michael@0:    */
michael@0:   _makeDeveloperFromAsyncRow: function AD__makeDeveloperFromAsyncRow(aRow) {
michael@0:     let name = aRow.getResultByName("name");
michael@0:     let url = aRow.getResultByName("url")
michael@0:     return new AddonManagerPrivate.AddonAuthor(name, url);
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Make a screenshot from an asynchronous row
michael@0:    *
michael@0:    * @param  aRow
michael@0:    *         The asynchronous row to use
michael@0:    * @return The created screenshot
michael@0:    */
michael@0:   _makeScreenshotFromAsyncRow: function AD__makeScreenshotFromAsyncRow(aRow) {
michael@0:     let url = aRow.getResultByName("url");
michael@0:     let width = aRow.getResultByName("width");
michael@0:     let height = aRow.getResultByName("height");
michael@0:     let thumbnailURL = aRow.getResultByName("thumbnailURL");
michael@0:     let thumbnailWidth = aRow.getResultByName("thumbnailWidth");
michael@0:     let thumbnailHeight = aRow.getResultByName("thumbnailHeight");
michael@0:     let caption = aRow.getResultByName("caption");
michael@0:     return new AddonManagerPrivate.AddonScreenshot(url, width, height, thumbnailURL,
michael@0:                                                    thumbnailWidth, thumbnailHeight, caption);
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Make a CompatibilityOverride from an asynchronous row
michael@0:    *
michael@0:    * @param  aRow
michael@0:    *         The asynchronous row to use
michael@0:    * @return The created CompatibilityOverride
michael@0:    */
michael@0:   _makeCompatOverrideFromAsyncRow: function AD_makeCompatOverrideFromAsyncRow(aRow) {
michael@0:     let type = aRow.getResultByName("type");
michael@0:     let minVersion = aRow.getResultByName("minVersion");
michael@0:     let maxVersion = aRow.getResultByName("maxVersion");
michael@0:     let appID = aRow.getResultByName("appID");
michael@0:     let appMinVersion = aRow.getResultByName("appMinVersion");
michael@0:     let appMaxVersion = aRow.getResultByName("appMaxVersion");
michael@0:     return new AddonManagerPrivate.AddonCompatibilityOverride(type,
michael@0:                                                               minVersion,
michael@0:                                                               maxVersion,
michael@0:                                                               appID,
michael@0:                                                               appMinVersion,
michael@0:                                                               appMaxVersion);
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Make an icon from an asynchronous row
michael@0:    *
michael@0:    * @param  aRow
michael@0:    *         The asynchronous row to use
michael@0:    * @return An object containing the size and URL of the icon
michael@0:    */
michael@0:   _makeIconFromAsyncRow: function AD_makeIconFromAsyncRow(aRow) {
michael@0:     let size = aRow.getResultByName("size");
michael@0:     let url = aRow.getResultByName("url");
michael@0:     return { size: size, url: url };
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * A helper function to log an SQL error.
michael@0:    *
michael@0:    * @param  aError
michael@0:    *         The storage error code associated with the error
michael@0:    * @param  aErrorString
michael@0:    *         An error message
michael@0:    */
michael@0:   logSQLError: function AD_logSQLError(aError, aErrorString) {
michael@0:     logger.error("SQL error " + aError + ": " + aErrorString);
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * A helper function to log any errors that occur during async statements.
michael@0:    *
michael@0:    * @param  aError
michael@0:    *         A mozIStorageError to log
michael@0:    */
michael@0:   asyncErrorLogger: function AD_asyncErrorLogger(aError) {
michael@0:     logger.error("Async SQL error " + aError.result + ": " + aError.message);
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Synchronously creates the triggers in the database.
michael@0:    */
michael@0:   _createTriggers: function AD__createTriggers() {
michael@0:     this.connection.executeSimpleSQL("DROP TRIGGER IF EXISTS delete_addon");
michael@0:     this.connection.executeSimpleSQL("CREATE TRIGGER delete_addon AFTER DELETE " +
michael@0:       "ON addon BEGIN " +
michael@0:       "DELETE FROM developer WHERE addon_internal_id=old.internal_id; " +
michael@0:       "DELETE FROM screenshot WHERE addon_internal_id=old.internal_id; " +
michael@0:       "DELETE FROM compatibility_override WHERE addon_internal_id=old.internal_id; " +
michael@0:       "DELETE FROM icon WHERE addon_internal_id=old.internal_id; " +
michael@0:       "END");
michael@0:   },
michael@0: 
michael@0:   /**
michael@0:    * Synchronously creates the indices in the database.
michael@0:    */
michael@0:   _createIndices: function AD__createIndices() {
michael@0:     this.connection.executeSimpleSQL("CREATE INDEX IF NOT EXISTS developer_idx " +
michael@0:                                      "ON developer (addon_internal_id)");
michael@0:     this.connection.executeSimpleSQL("CREATE INDEX IF NOT EXISTS screenshot_idx " +
michael@0:                                      "ON screenshot (addon_internal_id)");
michael@0:     this.connection.executeSimpleSQL("CREATE INDEX IF NOT EXISTS compatibility_override_idx " +
michael@0:                                      "ON compatibility_override (addon_internal_id)");
michael@0:     this.connection.executeSimpleSQL("CREATE INDEX IF NOT EXISTS icon_idx " +
michael@0:                                      "ON icon (addon_internal_id)");
michael@0:   }
michael@0: }