Sqlite.jsm 编辑

The Sqlite.jsmJavaScript code module is a promise-based wrapper around the Storage/SQLite interface. Sqlite.jsm offers some compelling advantages over the low-level Storage XPCOM interfaces:

• Automatic statement management. Sqlite.jsm will create, manage, and destroy statement instances for you. You don't need to worry about caching created statement instances, destroying them when you are done, etc. This translates to fewer lines of code to talk to SQLite.
• All operations are asynchronous. Use of synchronous Storage APIs is discouraged because they block the main thread. All the functionality in Sqlite.jsm is asynchronous.
• Easier memory management. Since Sqlite.jsm manages statements for you, it can perform intelligent actions like purging all cached statements not in use, freeing memory in the process. There is even a shrinkMemoryAPI that will minimize memory usage of the connection automatically.
• Simpler transactions. Sqlite.jsm exposes a transaction API built on top of Task.jsm that allows transactions to be written as procedural JavaScript functions (as opposed to a series of callback driven operations). If the function throws, the transaction is automatically rolled back. This makes code easy to write and read.
• JavaScript-y API. Sqlite.jsm feels like a pure JavaScript module. The complexities of XPCOM are mostly hidden from view. JavaScript programmers should feel right at home using Sqlite.jsm.

Note: The Sqlite.jsm JavaScript code module can only be used from chrome -- that is, from within the application itself or an add-on.

Before you can use this module, you need to import it into your scope:

Components.utils.import("resource://gre/modules/Sqlite.jsm")

Obtaining a Connection

Sqlite.jsm exports the Sqlite symbol. This symbol is an object with a single function: openConnection. This function takes an object defining connection options:

path

(required) The database file to open. This can be an absolute or relative path. If a relative path is given, it is interpreted as relative to the current profile's directory. If the path does not exist, a new SQLite database will be created. The value typically ends with .sqlite.

sharedMemoryCache

(optional) Boolean indicating whether multiple connections to the database share the same memory cache. Sharing the memory cache likely results in less memory utilization. However, sharing also requires connections to obtain a lock, possibly making database access slower. Defaults to true.

shrinkMemoryOnConnectionIdleMS

(optional) If defined, the connection will attempt to minimize its memory usage after this many milliseconds of connection idle. The connection is idle when no statements are executing. There is no default value which means no automatic memory minimization will occur. Please note that this is not a timer on the idle service and this could fire while the application is active.

openConnection(options) returns a promise that resolves to an opened connection instance or is rejected if an error occurred while opening the database.

Here is an example:

Components.utils.import("resource://gre/modules/Sqlite.jsm");

try {
  const conn = await Sqlite.openConnection({ path: "myDatabase.sqlite", sharedMemoryCache: false });
  // connection is the opened SQLite connection (see below for API).
} catch (error) {
  // The connection could not be opened. error is an Error describing what went wrong.
}

Working with Opened Connections

Opened connections are what you will interface most with in Sqlite.jsm. The following sections detail the API of an opened connection instance.

Connection Management

These APIs are used to manage and inspect the state of the connection.

close()

Close this database connection. This must be called on every opened connection or else application shutdown will fail due to waiting on the opened connection to close (Gecko doesn't force close connections because it doesn't know that you are really done with them).

This function returns a promise that will be resolved when the database has closed.

If a transaction is in progress at the time this function is called, the transaction will be forcibly rolled back.

If statements are in progress at the time this function is called, they will be cancelled.

Callers should not attempt to use the connection after calling this method as the connection will be unusable.

It is possible to automatically close the connection when the browser is closed with the shutdown hook:

const conn = await Sqlite.openConnection({ path: "myDatabase.sqlite", sharedMemoryCache: false });

try {
  Sqlite.shutdown.addBlocker("My connection closing",
                             async () => await conn.close());
} catch (e) {
  // It's too late to block shutdown, just close the connection.
  await conn.close();
  throw e;
}

clone(readOnly)

This function returns a clone of the current connection-promise.

These functions receive the following arguments:

readOnly

(optional) If true the clone will be read-only, default is false.  If the original connection is already read-only, the clone will be, regardless of this option.  If the original connection is using the shared cache, this parameter will be ignored and the clone will be as privileged as the original connection.

transactionInProgress

This boolean property indicates whether a transaction is in progress. This is rarely needed by external callers.

shrinkMemory()

This function can be called to shrink the memory usage of the connection. This is a glorified wrapper around the PRAGMA shrink_memory statement, which tells SQLite to shrink its memory usage (by clearing caches, etc).

While calling this has the benefit of shrinking memory, it can make your database slower, especially if you will be interacting with it shortly after calling this function. This is because SQLite will need to page the database back into memory from disk. Therefore, caution should be exercised before calling this function.

This returns a promise that is resolved when the operation completes.

discardCachedStatements()

This function is used to discard cached statement instances, freeing memory in the process. Active cached statements will not be discarded. Therefore, it is safe to call this any time.

This returns an integer count of the number of cached statements that were discarded.

Table and Schema Management

These APIs deal with management of tables and database schema.

getSchemaVersion()

The user-set version associated with the schema for the current database. If no schema version has been set, this will return the string "0".

setSchemaVersion(value)

Sets value as the new version associated with the schema for the current database. This is a wrapper around the PRAGMA user_version statement.

tableExists(name)

This function determines whether a table exists in the current database. It returns a promise that is resolved with a boolean indicating whether the table exists.

indexExists(name)

This functions determines whether a named index exists in the current database. It returns a promise that is resolved with a boolean indicating whether the index exists.

Examples

Statement Execution

These APIs facilitate execution of statements on the connection. These are arguably the most important APIs in this type.

executeCached(sql, params, onRow)

execute(sql, params, onRow)

These similar functions are used to execute a single SQL statement on the connection. As you might have guessed by the name, there are 2 flavors: cached and non-cached. Other than that, they behave identically.

These functions receive the following arguments:

sql

(required) String SQL statement to execute. The trailing semicolon is not required.

params

(optional) Parameters to bind to this statement. This can be an array or an object. See notes below.

onRow

(optional) Function that is called when a row has been received.

The return value is a promise that is resolved when the statement has finished execution.

When a statement is executed via executeCached(), the prepared statement object is cached inside the opened connection. The next time this same SQL statement is executed (the sqlargument is identical to one passed in before), the old statement object is reused. This saves time associated with parsing the SQL statement and creating a new statement object. The downside is the cached statement object lingers in the opened connection, taking up memory.

When a statement is executed via execute(), the underlying statement object is thrown away as soon as the statement finishes execution.

executeCached() is recommended for statements that will be executed many times. execute() is recommended for statements that will be executed seldomly or once.

Please note that callers don't need to prepare statements manually before execution. Just call executeCached() and the statement will be prepared for you automatically.

Parameters can be bound to the statement by defining the paramsargument. This argument can be an array of positional parameters or an object of named parameters. If the statement does not contain any bound parameters, this argument can be omitted or specified as null.

Note: Callers are strongly encouraged to use bound parameters instead of dynamically creating SQL statements for security reasons. Bound parameters aren't prone to SQL injection like manual SQL statement construction (e.g., concatenating a raw value into the executed SQL statement). Note: Callers are encouraged to pass objects rather than Arrays for bound parameters because they prevent foot guns. With positional arguments, it is simple to modify the parameter count or positions without fixing all users of the statement. Objects/named parameters are a little safer because changes in the order of parameters in the statement won't change how values are bound to those parameters.   When onRowis not defined, the full results of the operation are buffered before the caller is notified of statement completion. For INSERT, UPDATE, and DELETE statements, this is not relevant. However, it can have drastic implications for SELECTstatements. If your SELECTstatement could return lots of data, this buffering of returned rows could result in excessive memory usage. Therefore, it's recommended to use onRowwith SELECTstatements.   Along with a result row, onRow is passed a cancel function as its second argument. Consumers of this API can call the cancel function should they want to immediately abort the execution of the statement. When cancelled, subsequent rows will not be processed and no more onRowinvocations will be made. If an exception is thrown by the onRowhandler, the exception is logged and processing of subsequent rows occurs as if nothing happened. The promise is still resolved (not rejected).

The return promise will be rejected with an Errorinstance if the statement did not finish execution fully. The Errormay have an errors property. If defined, it will be an Array of objects describing individual errors. Each object has the properties result and message. result is a numeric error code and messageis a string description of the problem.

If onRowis specified, the returned promise will be resolved with a boolean indicating whether the onRow handler was called. Else, the resolved value will be an array of mozIStorageRow.

executeTransaction(func, type)

This function is used to execute a database transaction. A transaction is a series of related statements treated as one functional unit. If the transaction succeeds, all the statements contained within it are committed as one unit. If the transaction fails, the database is rolled back to its state before the transaction started.

This function receives the following arguments:

func

The function defining the transaction body.

type

The type of transaction to perform. This must be one of the TRANSACTION_* constants on the opened connection instance. Valid values are TRANSACTION_DEFERRED, TRANSACTION_IMMEDIATE, TRANSACTION_EXCLUSIVE. See the SQLite documentation for their meaning. The default is TRANSACTION_DEFERRED.

The passed function is a Task.jsm compatible generator function. When called, the function receives as its argument the current connection instance. This generator function is expected to yield promises, likely those returned by calling executeCached() and execute().

If we reach the end of the generator function without error, the transaction is committed. If an error occurs, the transaction is rolled up.

The returned value from this function is a promise that is resolved when the transaction has been committed or is rejected if the transaction was rolled back.

Open, Execute, Close

In this example, we open a connection, execute a simple statement, then close the connection.

const conn = await Sqlite.openConnection({path: "MyDB.sqlite"});
const result = await conn.execute("SELECT 1");
await conn.close();
alert("We are done!");

This isn't a terrific example because it doesn't include error handling.

Bound Parameters

Here are some examples demonstrating bound parameters. Assume we open an opened connection in the connvariable.

let dataToInsert = [
  ["foo", "bar"],
  ["biz", "baz"],
  {"yo", "ho"],
];

async function doInsert() {
  for (let data of dataToInsert) {
    await conn.executeCached("INSERT INTO myTable VALUES (?, ?)", data);
  }
});

And the same thing with named parameters.

let dataToInsert = [
  {paramA: "foo", paramB: "bar"},
  {paramA: "biz", paramB: "baz"},
  {paramA: "yo", paramB: "ho"},
];

async function doInsert() {
  for (let data of dataToInsert) {
    await conn.executeCached("INSERT INTO myTable VALUES (:paramA, :paramB)", data);
  }
});

Transactions

These examples demonstrate how transactions work.

conn.executeTransaction(async function simpleTransaction() {
  await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["biz", "baz"]);
});

The above will result in 2 INSERT statements being committed in a deferred transaction (assuming the inserts proceed without error, of course).

Let's do an example where we want to force a transaction rollback.

conn.executeTransaction(async function complexTransaction() {
  await conn.execute("INSERT INTO myTable VALUES (?, ?)", ["foo", "bar"]);
  let data = await conn.execute("SELECT * FROM myTable");
  if (data.length < 5) {
    throw new Error("We don't have the expected 5 rows to perform the next operation!");
  }

  // ...
});

Selecting and Returning Data

These examples demonstrate how to access the data that is returned.

This example shows multiple rows of a table being returned using the onRow function parameter.

let accounts = [];
let accountId, userName;

let statement = "SELECT account_id, username FROM accounts ORDER BY username ASC";

try {
  const result = await conn.executeCached(statement, null, function(row, cancel) {
    accountId = row.getResultByName("account_id");
    userName = row.getResultByName("username");
    accounts.push({ accountId: accountId, userName: userName });
    // If the cancel function is called here, the statement being executed will
    // be cancelled, and no more rows will be processed.
  });
} catch (err) {
  // An error occurred.
  console.log(err);
  // Error, Oh noes!
  if (callback) {
    callback(err);
  }
}

// All accounts returned successfully, so do something with them.
console.log(result); // It worked!
if (callback) {
  callback(null, accounts);
}

Note: the then parameters can be anonymous functions (i.e. function() ), and only are labeled as onStatementComplete and onError for readability.

This example demonstrates retrieving a row without using the onRow function parameter, and instead, using the conn.execute result. This example also demonstrates retrieving the primary key row id of the last inserted row.

async function () {
  try {
    conn = await Sqlite.openConnection({ path: dbFile.path });

    let statement = "INSERT INTO accounts (username, details) VALUES (:username, :details)"
    let params = { username:"LordBusiness", details: "All I'm asking for is total perfection." };

    await conn.execute(statement,params);

    // Get accountId of the INSERT.
    statement = "SELECT last_insert_rowid() AS lastInsertRowID";
    result = await conn.execute(statement);

    // Only one row is returned.
    let row = result[0];
    let accountId = row.getResultByName("lastInsertRowID");

    if (callback) {
      callback(null, accountId);
    }
  } catch (err) {
    if (callback) {
      callback(err);
    }
  } finally {
    conn.close();
  }
});

Note: The value returned by the last_insert_rowid() is per connection, so you may need to open separate connections when doing multiple INSERTs from different locations, to be sure the row id that is being returned is from the correct corresponding INSERT.

See also

• Sqlite.jsm source on DXR