Storage

Storage is a SQLite database API. It is available to trusted callers, meaning extensions and Firefox components only.

The API is currently "unfrozen", which means it is subject to change at any time; in fact, it has changed somewhat with each release of Firefox since it was introduced, and will likely continue to do so for a while.

Getting started

This document covers the Storage API and some peculiarities of SQLite. It does not cover SQL or "regular" SQLite. You can find some very useful links in the See also section however. For Storage API help, you can post to mozilla.dev.apps.platform on the news server news.mozilla.org. To report bugs, use Bugzilla.

The overall procedure for use is:

1. Get the Storage service - mozIStorageService.
2. Open a connection to the database of your choice - mozIStorageConnection.
3. Create statements to execute on the connection - mozIStorageStatement.
4. Bind parameters to a statement as necessary.
5. Execute the statement.
6. Check for errors.
7. Reset the statement.

Opening a connection

JavaScript example of opening my_db_file_name.sqlite in the profile directory:

Components.utils.import("resource://gre/modules/Services.jsm");
Components.utils.import("resource://gre/modules/FileUtils.jsm");
let file = FileUtils.getFile("ProfD", ["my_db_file_name.sqlite"]);
let dbConn = Services.storage.openDatabase(file); // Will also create the file if it does not exist

Likewise, the C++ would look like this:

nsCOMPtr<nsIFile> dbFile;
rv = NS_GetSpecialDirectory(NS_APP_USER_PROFILE_50_DIR,
                            getter_AddRefs(dbFile));
NS_ENSURE_SUCCESS(rv, rv);
rv = dbFile->Append(NS_LITERAL_STRING("my_db_file_name.sqlite"));
NS_ENSURE_SUCCESS(rv, rv);
nsCOMPtr<mozIStorageService> dbService =
  do_GetService(MOZ_STORAGE_SERVICE_CONTRACTID, &rv);
NS_ENSURE_SUCCESS(rv, rv);
nsCOMPtr<mozIStorageConnection> dbConn;
rv = dbService->OpenDatabase(dbFile, getter_AddRefs(dbConn));
NS_ENSURE_SUCCESS(rv, rv);

Closing a connection

To close a connection on which only synchronous transactions were performed, use the mozIStorageConnection.close() method. If you performed any asynchronous transactions, you should instead use the mozIStorageConnection.asyncClose() method. The latter will allow all ongoing transactions to complete before closing the connection, and will optionally notify you via callback when the connection is closed.

Statements

This section demonstrates how you can execute SQL statements on your database. For a complete reference see mozIStorageStatement.

Creating a Statement

There are actually two ways to execute a statement. You should choose the right one based on your needs.

No Results to be Returned

If you do not need to get any results back, you can use mozIStorageConnection.executeSimpleSQL() API like this in JavaScript:

dbConn.executeSimpleSQL("CREATE TEMP TABLE table_name (column_name INTEGER)");

Similarly, the C++ looks like this:

rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TEMP TABLE table_name (column_name INTEGER)"));
NS_ENSURE_SUCCESS(rv, rv);

Results to be Returned

However, if you need to get results back, you should create the statement with the mozIStorageConnection.createStatement() API like this in JavaScript:

var statement = dbConn.createStatement("SELECT * FROM table_name WHERE column_name = :parameter");

This example uses a named placeholder called "parameter" to be bound later (described in Binding Parameters). Similarly, the C++ looks like this:

nsCOMPtr<mozIStorageStatement> statement;
rv = dbConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE column_name = ?1"),
                             getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

This example uses the numbered placeholder indexed by zero for a parameter to be bound later (described in Binding Parameters).

Binding Parameters

In order to effectively use the statements that you create, you have to bind values to the parameters you placed in the statement. A given placeholder can appear multiple times in the same statement, and all instances of it will be replaced with the bound value. If you neglect to bind a value to a parameter, it will be interpreted as NULL.

Binding One Set of Parameters

If you only have one row to insert, or are using the synchronous API you'll need to use this method. In JavaScript, there is a useful helper object (mozIStorageStatementParams) available () that makes binding parameters much easier:

var statement = dbConn.createStatement("SELECT * FROM table_name WHERE id = :row_id");
statement.params.row_id = 1234;

Use :boundParameterName the same way for numeric and non-numeric columns. Do not put the :boundParameterName within apostrophes, because that the binding mechanism does that for you. You can still use this helper object by manually creating the statement wrapper, mozIStorageStatementWrapper, which is provided in Gecko 1.9.1 and later.

Using named parameters in C++ is a lot more difficult, so it's generally accepted to use numerical placeholders instead. The example below uses mozIStorageStatement.bindInt32Parameter(). The full list of binding functions can be found with the mozIStorageStatement documentation.

C++ example:

nsCOMPtr<mozIStorageStatement> statement;
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM table_name WHERE id = ?1"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindInt32Parameter(0, 1234);
NS_ENSURE_SUCCESS(rv, rv);

Binding Multiple Sets of Parameters

Starting in Gecko 1.9.2 (Firefox 3.6), there's a new, more convenient way to bind multiple sets of parameters at once prior to executing your statement asynchronously. This API is only available for asynchronous execution.

let stmt = dbConn.createStatement("INSERT INTO table_name (value) VALUES(:value)");
let params = stmt.newBindingParamsArray();
for (let i = 0; i < 10; i++) {
  let bp = params.newBindingParams();
  bp.bindByName("value", i);
  params.addParams(bp);
}
stmt.bindParameters(params);

You can attach multiple sets of bindings to a statement by adding multiple mozIStorageBindingParams objects to the array of parameter lists, adding each one through calls to the mozIStorageBindingParamsArray.addParams(). Once all the parameters are set up, a single call to mozIStorageStatement.bindParameters() will ensure that the parameters are bound before execution. You can then execute the statement asynchronously, and the statement will get each set of bindings bound to it before execution asynchronously.

Executing a Statement

You may execute statements either synchronously (which is supported in Firefox Gecko 1.8 and 1.9) or asynchronously (starting in Gecko 1.9.1). If your code needs to work with applications based on Gecko 1.8 or 1.9, you should the technique covered in the section Synchronously below. Otherwise, it's strongly recommended that you use asynchronous execution, for performance reasons.

Asynchronously

Gecko 1.9.1 introduced support for asynchronous execution of a statement by calling mozIStorageStatement.executeAsync() on the statement. Multiple statements can be executed in a transaction by calling mozIStorageConnection.executeAsync() on the connection and passing in an array of statements. Both of these methods have similar signatures that accept an object as input that receives notifications the execution of the statement(s). A C++ example is omitted here because it would be verbose, but real-world code can be found in the Mozilla source tree (MXR ID Search for mozIStorageStatementCallback).

After you create and bind a statement, your JavaScript should look something like this to execute a statement asynchronously:

statement.executeAsync({
  handleResult: function(aResultSet) {
    for (let row = aResultSet.getNextRow();
         row;
         row = aResultSet.getNextRow()) {
      let value = row.getResultByName("column_name");
    }
  },
  handleError: function(aError) {
    print("Error: " + aError.message);
  },
  handleCompletion: function(aReason) {
    if (aReason != Components.interfaces.mozIStorageStatementCallback.REASON_FINISHED)
      print("Query canceled or aborted!");
  }
});

The call to executeAsync takes an object that implements mozIStorageStatementCallback. See its documentation for more details on each method. The callback is optional, however, so if you do not want to receive feedback, you can pass nothing.

Synchronously

If you are OK with the possibility of locking up your user interface, or if you are running on a background thread, you can use mozIStorageStatement.executeStep(). This function allows you to enumerate all the results produced by the statement.

As you step through each row, you can obtain each parameter by name through a helper object (mozIStorageStatementRow) in JavaScript ( ) like so:

while (statement.executeStep()) {
  let value = statement.row.column_name;
}

You can create this helper object yourself if it's not available in your version of Gecko. See mozIStorageStatementWrapper for details.

In C++, the code would look something like this:

bool hasMoreData;
while (NS_SUCCEEDED(statement->ExecuteStep(&hasMoreData)) && hasMoreData) {
  PRInt32 value;
  rv = statement->GetInt32(0, &value);
  NS_ENSURE_SUCCESS(rv, rv);
}

You can obtain other types of data by using the various methods available on mozIStorageValueArray.

Alternatively, if you do not expect any results but still need to execute a bound statement, you can simply call mozIStorageStatement.execute(). This is equivalent to calling mozIStorageStatement.executeStep() and then mozIStorageStatement.reset().

Resetting a Statement

When you execute a statement synchronously, it is important to make sure you reset your statement. You can accomplish this by calling mozIStorageStatement.reset() on the statement. If you end up finalizing the statement (see mozIStorageStatement.finalize()) you do not need to worry about calling mozIStorageStatement.reset(). You should do this before you reuse the statement.

In JavaScript, the language makes it pretty easy to ensure that you always reset a statement. Be aware that you should always reset even if an exception is thrown, so your code should look something like this:

var statement = dbConn.createStatement("SELECT * FROM table_name");
try {
  while (statement.step()) {
    // Use the results...
  }
}
finally {
  statement.reset();
}

In C++, Storage provides a helper object in storage/public/mozStorageHelper.h, mozStorageStatementScoper, which ensures that the statement object is reset when the object falls out of scope. Of course, if your statement is local only to the function, you do not have to worry about calling reset since the object will be destroyed.

nsresult
myClass::myFunction()
{
  // mSpecialStatement is a member variable of the class that contains a statement.
  mozStorageStatementScoper scoper(mSpecialStatement);
  // You can use mSpecialStatement without concern now.
  nsCOMPtr<mozIStorageStatement> statement;
  // mDBConn is a database connection that is stored a member variable of the class.
  nsresult rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("DELETE FROM table_name"),
                                         getter_AddRefs(statement));
  NS_ENSURE_SUCCESS(rv, rv);
  return statement->Execute();
  // Once this function returns, mSpecialStatement will be reset, and statement will
  // be destroyed.
}

Transactions

Transactions can be used to either improve performance, or group statements together as an atomic operation. In both cases, you execute more than one statement inside of a transaction.

In JavaScript, managing transactions can be difficult when you are using the same connection on different threads, or are using a combination of asynchronous and synchronous statement execution. The best way to deal with this is to only execute your statements asynchronously using mozIStorageConnection.executeAsync(). This method will manage the transactions for you, so you don't have to worry about them.

Transactions can be started with mozIStorageConnection.beginTransaction() or mozIStorageConnection.beginTransactionAs(). The latter takes one of three constants to describe the type of transaction:

• mozIStorageConnection.TRANSACTION_DEFERRED
• mozIStorageConnection.TRANSACTION_IMMEDIATE
• mozIStorageConnection.TRANSACTION_EXCLUSIVE

mozIStorageConnection.beginTransaction() is equivalent to calling mozIStorageConnection.beginTransactionAs() and passing mozIStorageConnection.TRANSACTION_DEFERRED. In general, this is the method you want to use.

Once you start a transaction, you can either commit the changes by calling mozIStorageConnection.commitTransaction(), or rollback the changes by calling mozIStorageConnection.rollbackTransaction().

In C++ code, there is a helper class defined in storage/public/mozStorageHelper.h, mozStorageTransaction, that will attempt to get a transaction for you, and handle it appropriately when it falls out of scope. If a transaction is already in progress, no transaction is obtained. If your function returns without calling Commit on the helper object, the transaction will be rolled back.

nsresult
myClass::myFunction()
{
  // mDBConn is a member variable of our mozIStorageConnection.
  mozStorageTransaction transaction(mDBConn);
  // Execute some statements.  If we encounter an error, the transaction will
  // be rolled back.
  return transaction.Commit();
}

Collation (sorting)

SQLite provides several collation methods (BINARY, NOCASE, and RTRIM), but these are all very simple and have no support for various text encodings or the user's locale.

Gecko 1.9.2 added support for several new collation methods:

locale

Case- and accent-insensitive collation.

locale_case_sensitive

Case-sensitive, accent-insensitive collation.

locale_accent_sensitive

Case-insensitive, accent-sensitive collation.

locale_case_accent_sensitive

Case- and accent-sensitive collation.

You can use them quite simply in your SELECT queries, like this:

var stmt = aConn.createStatement("SELECT * FROM foo ORDER BY name COLLATE locale ASC");
var results = [];
while (stmt.executeStep()) {
  results.push(stmt.row.t);
}
stmt.finalize();

How to Corrupt a Database

SQLite is very good about maintaining database integrity, but there are a few things you can do that can lead to database corruption. You can find out more by reading SQLite's documentation on this. These are a few simple things you can avoid to help make sure this doesn't happen:

• Open more than one connection to the same file with names that aren't exactly the same as determined by strcmp. This includes "my.db" and "../dir/my.db" or, on Windows (case-insensitive) "my.db" and "My.db". Sqlite tries to handle many of these cases, but you shouldn't count on it.

• Access a database from a symbolic or hard link.

• Access a statement from more than one thread (discussed in Thread safety).

• Call mozIStorageService.backupDatabaseFile() on a locked database, assuming this will leave your database locked. Due to bug 626193, locked databases get unlocked when you call this.

Thread Safety

mozIStorageService and mozIStorageConnection are thread safe. However, no other interface or method is, so do not use them on different threads at the same time!

If you want to use concurrency to work on your database, you should use the asynchronous APIs provided by Storage.

See also

• mozIStorageConnection Database connection to a specific file or in-memory data storage
• mozIStorageStatement Create and execute SQL statements on a SQLite database.
• mozIStorageValueArray Wraps an array of SQL values, such as a result row.
• mozIStorageFunction Create a new SQLite function.
• mozIStorageAggregateFunction Create a new SQLite aggregate function.
• mozIStorageProgressHandler Monitor progress during the execution of a statement.
• mozIStorageStatementWrapper Storage statement wrapper
• mozIStorageService Storage Service

• Storage:Performance How to get your database connection performing well.
• Storage Inspector Extension Makes it easy to view any sqlite database files in the current profile.
• SQLite Syntax Query language understood by SQLite
• SQLite Database Browser is a capable free tool available for many platforms. It can be handy for examining existing databases and testing SQL statements.
• SQLite Manager Extension helps manage sqlite database files on your computer.