Révision 116226 sur Storage

  • Raccourci de la révision : Storage
  • Titre de la révision : Storage
  • ID de la révision : 116226
  • Créé :
  • Créateur : Chbok
  • Version actuelle ? Non
  • Commentaire /* Getting started */ traduction

Contenu de la révision

{{template.Traduction_en_cours("Storage")}}

Storage est une API de base de données dans Firefox 2 et suivante pilotée par sqlite. Elle est disponible aux appels avec permission, tels que du code chrome et des extensions, mais pas aux pages Web. Son statut actuel est "en développement", ce qui signifie que les API peuvent être modifiées à n'importe quel moment. Il se peut en effet que l'API soit légèrement modifiée entre Firefox 2 alpha 2 et Firefox 2, et également entre Firefox 2 et Firefox 3.

Storage peut parfois être confondu avec la fonctionnalité WHATWG DOM storage de Firefox 2 qui permet à des pages Web d'enregistrer des données permanentes. L'API Storage s'adresse uniquement aux auteurs d'extensions et aux composants de Firefox.

Ce document traite l'API mozStorage et quelques particularités de sqlite. Il ne traite pas du SQL ou de l'utilisation de sqlite. Pour ces autres informations, vous devrez consulter vos références favorites sur SQL. Vous pouvez également consulter la documentation sur sqlite et particulièrement celle sur la compréhension du langage de requêtes sur sqlite. Pour obtenir plus d'aide sur l'API mozStorage, vous pouvez poster sur le serveur de news mozilla.dev.apps.firefox sur news.mozilla.org. Pour signaler des bogues, utilisez Bugzilla (product "Toolkit", component "Storage").

Consultez Storage:Performance pour optimiser les performances de connexion de votre base de données.

SQLite Database Browser est un outil libre, disponible sur plusieurs plateformes, permettant l'examen de bases de données existantes et le test des états SQL.

Préambule

mozStorage se présente comme n'importe quelle autres systèmes de bases de données. La procédure complète d'utilisation est la suivante :

  • Ouverture d'une connexion vers la base de données de votre choix.
  • Création d'une variable d'exécution de la connexion.
  • Liaison de paramètres à la variable si nécessaire.
  • Exécution de la requête.
  • Réinitialisation de la variable.

Opening a connection

The storage service's first initialization must be from the main thread. You will get an error if you initialize it the first time from another thread. Therefore, if you want to use the service from a thread, be sure to call getService from the main thread to ensure the service has been created.

C++ example of opening a connection to "asdf.sqlite" in the user's profile directory:

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("asdf.sqlite"));
NS_ENSURE_SUCCESS(rv, rv);

mDBService = do_GetService(MOZ_STORAGE_SERVICE_CONTRACTID, &rv);
NS_ENSURE_SUCCESS(rv, rv);
rv = mDBService->OpenDatabase(dbFile, getter_AddRefs(mDBConn));
NS_ENSURE_SUCCESS(rv, rv);

MOZ_STORAGE_SERVICE_CONTRACTID is defined in {{template.Source("storage/build/mozStorageCID.h")}}. It's value is "@mozilla.org/storage/service;1"

JavaScript example:

var file = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
file.append("asdf.sqlite");

var storageService = Components.classes["@mozilla.org/storage/service;1"]
                        .getService(Components.interfaces.mozIStorageService);
var mDBConn = storageService.openDatabase(file);
Note: The OpenDatabase function is subject to change. It will likely be enhanced/simplified to make it more difficult to get into trouble.

It may be tempting to give your database a name ending in ".sdb" for sqlite database, but this is not recommended. This extension is treated specially by Windows as a known extension for an "Application Compatability Database" and changes are backed up by the system automatically as part of system restore functioality. This can result in much higher overhead file operations.

Creating a statement

There are two ways to create a statement. If you have no parameters and the statement doesn't return any data, use mozIStorageConnection.executeSimpleSQL.

C++:
rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE foo (a INTEGER)"));

JS:
mDBConn.executeSimpleSQL("CREATE TABLE foo (a INTEGER)");

Otherwise, you should prepare a statement using mozIStorageConnection.createStatement:

C++:
nsCOMPtr<mozIStorageStatement> statement;
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM foo WHERE a = ?1"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

JS:
var statement = mDBConn.createStatement("SELECT * FROM foo WHERE a = ?1");

This example uses the placeholder "?1" for a parameter to be bound later (see next section).

Once you have a prepared statement, you can bind parameters to it, execute it, and reset it over and over. If you are doing a statement many times, using a precompiled statement will give you a noticable performance improvement because the SQL query doesn't need to be parsed each time.

If you are familiar with sqlite, you may know that prepared statements are invalidated when the schema changes of the database. Fortunately, mozIStorageStatement detects the error and will recompile the statement as needed. Therefore, once you create a statement, you don't need to worry when changing the schema; all statements will continue to transparently work.

Binding parameters

It is generally best to bind all parameters separately rather than try to construct SQL strings on the fly containing the parameters. Among other things, this prevents SQL injection attacks, since a bound parameter can never be executed as SQL.

You bind parameters to a statement that has placeholders. The placeholders are addressed by index, starting from "?1", then "?2"... You use the statement functions BindXXXParameter(0) BindXXXParameter(1)... to bind to these placeholders.

Watch out: The indices in the placeholders count from 1. The integers passed to the binding functions count from 0. This means "?1" corresponds to parameter 0, "?2" corresponds to parameters 1, etc.

A placeholder can appear multiple times in the SQL string and all instances will be replaced with the bound value. Unbound parameters will be interpreted as NULL.

Binding functions available in mozIStorageStatement (see {{template.Source("storage/public/mozIStorageStatement.idl")}}) are:

  • bindUTF8StringParameter(in unsigned long aParamIndex, in AUTF8String aValue)
  • bindStringParameter(in unsigned long aParamIndex, in AString aValue)
  • bindDoubleParameter(in unsigned long aParamIndex, in double aValue)
  • bindInt32Parameter(in unsigned long aParamIndex, in long aValue)
  • bindInt64Parameter(in unsigned long aParamIndex, in long long aValue)
  • bindNullParameter(in unsigned long aParamIndex)
  • bindBlobParameter(in unsigned long aParamIndex, {{mediawiki.external('array,const,size_is(aValueSize)')}} in octet aValue, in unsigned long ValueSize) (for binary data)

C++ example:

nsCOMPtr<mozIStorageStatement> statement;
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM foo WHERE a = ?1 AND b > ?2"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindUTF8StringParameter(0, "hello"); // "hello" will be substituted for "?1"
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindInt32Parameter(1, 1234); // 1234 will be substituted for "?2"
NS_ENSURE_SUCCESS(rv, rv);

Javascript example:

var statement = mDBConn.createStatement("SELECT * FROM foo WHERE a = ?1 AND b > ?2");
statement.bindUTF8StringParameter(0, "hello");
statement.bindInt32Parameter(1, 1234);

Executing a statement

The main way to execute a statement is with mozIStorageStatement.executeStep. This function allows you to enumerate all the result rows your statement produces, and will notify you when there are no more results.

After a call to executeStep, you can use the getter functions in mozIStorageValueArray (see {{template.Source("storage/public/mozIStorageValueArray.idl")}}. mozIStorageStatement implements mozIStorageValueArray. These functions are:

  • long getInt32(in unsigned long aIndex);
  • long long getInt64(in unsigned long aIndex);
  • double getDouble(in unsigned long aIndex);
  • AUTF8String getUTF8String(in unsigned long aIndex);
  • AString getString(in unsigned long aIndex);
  • void getBlob(in unsigned long aIndex, out unsigned long aDataSize, {{mediawiki.external('array,size_is(aDataSize)')}} out octet aData); warning: data will be NULL if dataSize is 0.
  • boolean getIsNull(in unsigned long aIndex); Returns true if the cell is NULL (different from an empty string)

You can get the type of a value from mozIStorageValueArray.getTypeOfIndex, which returns the type of the specified column. Be careful: sqlite is not a typed database. Any type can be put into any cell, regardless of the type declared for the column. If you request a different type, sqlite will do its best to convert them, and will do some default value if it is impossible. Therefore, it is impossible to get type errors, but you may get weird data out.

C++ code can also use AsInt32, AsDouble, etc. functions which return the value as a more convenient C++ return value. Watch out, though, because you won't get any errors if your index is invalid. Other errors are impossible, because sqlite will always convert types, even if they don't make sense.

C++ example:

PRBool hasMoreData;
while (NS_SUCCEEDED(statement->ExecuteStep(&hasMoreData)) && hasMoreData) {
  PRInt32 value = statement->AsInt32(0);
  // use the value...
}

Javascript example:

while (statement.executeStep()) {
  var value = statement.getInt32(0);
  // use the value...
}

mozIStorageStatement.execute() is a convenience function for when you are getting no data out of the statement. It steps the statement once and resets it. This can be useful for insert statements because it really cleans up the code:

var statement = mDBConn.createStatement("INSERT INTO my_table VALUES (?1)");
statement.bindInt32Parameter(52);
statement.execute();

Resetting a statement

It is important to reset statements that are no longer being used. Un-reset write statements will keep a lock on the tables and will prevent other statements from accessing it. Un-reset read statements will prevent writes.

When the statement object is freed, it's corresponding database statement is closed. If you are using C++ and you know that all references will be destroyed, you don't have to explicitly reset the statement. Also, if you use mozIStorageStatement.execute(), you don't need to explicitly reset the statement; this function will reset it for you. Otherwise, call mozIStorageStatement.reset().

JavaScript callers should ensure that statements are reset. Be particularly careful about exceptions. You will want to make sure to reset your statements even if an exception is fired, or subsequent access to the database may not be possible. Resetting a statement is relatively lightweight, and nothing bad happens if it's already reset, so don't worry about unnecessary resets.

var statement = connection.createStatement(...);
try {
  // use the statement...
} finally {
  statement.reset();
}

C++ callers must do the same. There is a scoped object in {{template.Source("storage/public/mozStorageHelper.h")}} called mozStorageStatementScoper which will ensure that a given statement is reset when the enclosing scope is exited. It is hightly recommended that you use this object if possible.

void someClass::someFunction()
{
  mozStorageStatementScoper scoper(mStatement)
  // use the statement
}

Transactions

mozIStorageConnection has functions for beginning and ending transactions. If you do not explicitly use transactions, an implicit transaction will be created for you for each statement. This has major performance implications. There is overhead for each transaction, especially for commits. You will therefore see a large performance win when you are doing multiple statements in a row if you put them in a transaction. See Storage:Performance for more performance information.

The major difference between other database systems is that sqlite does not support nested transactions. This means that once a transaction is open, you can not open another transaction. You can check mozIStorageConnection.transactionInProgress to see if a transaction is currently in progress.

You can also just execute "BEGIN TRANSACTION" and "END TRANSACTION" directly as SQL statements (this is what the connection does when you call the functions). However, use of mozIStorageConnection.beginTransaction and related functions are strongly recommended because it stores transaction state in the connection. Otherwise, the attribute transactionInProgress will have the wrong value.

sqlite has several types of transactions:

  • mozIStorageConnection.TRANSACTION_DEFERRED: The default. The database lock is acquired when needed (usually the first time you execute a statement in the transaction).
  • mozIStorageConnection.TRANSACTION_IMMEDIATE: Get a read lock on the database immediately.
  • mozIStorageConnection.TRANSACTION_EXCLUSIVE: Get a write lock on the database immediately.

You can pass this type of transaction to mozIStorageConnection.beginTransactionAs to determine what kind of transaction you need. Keep in mind that if another transaction has already started, this operation will not succeed. Generally, the default TRANSACTION_DEFERRED type is sufficient and you shouldn't use the other types unless you really know why you need them. For more information, see the sqlite documentation about BEGIN TRANSACTION and locking.

var ourTransaction = false;
if (mDBConn.transactionInProgress) {
  ourTransaction = true;
  mDBConn.beginTransactionAs(mDBConn.TRANSACTION_DEFERRED);
}

// ... use the connection ...

if (ourTransaction)
  mDBConn.commitTransaction();

From C++ code, you can use the mozStorageTransaction helper class defined in {{template.Source("storage/public/mozStorageHelper.h")}}. This class will begin a transaction of the specified type on the specified connection when it comes into scope, and will either commit or rollback the transaction when it goes out of scope. If a transaction is already in progress, the transaction helper class will not do anything.

It also has functions for explicitly committing. The typical use is that you create the class defaulting to rollback, and then explicitly commit the transaction when processing has succeeded:

nsresult someFunction()
{
  // deferred transaction (the default) with rollback on failure
  mozStorageTransaction transaction(mDBConn, PR_FALSE);

  // ... use the connection ...

  // everything succeeded, now explicitly commit
  return transaction.Commit();
}

How to corrupt your database

  • 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.
  • Open connections to the same database from more than one thread (see "Thread safety" below).
  • Access a connection or statement from more than one thread (see "Thread safety" below).
  • Open the database from an external program while it is open in Mozilla. Our caching breaks the normal file-locking in sqlite that allows this to be done safely.

Thread safety

The mozStorage service and sqlite are threadsafe. However, no other mozStorage or sqlite objects or operations are threadsafe.

  • The storage service must be created on the main thread. If you want to access the service from another thread, you should be sure that you call getService from the main thread ahead of time.
  • You can not access a connection or statement from multiple threads. These storage objects are not threadsafe, and the sqlite representations of them are not threadsafe either. Even if you do locking and ensure that only one thread is doing something at once, there may be problems. This case hasn't been tested, and there may be some internal per-thread state in sqlite. It is strongly advised that you don't do this.
  • You can not access a single database from multiple connections from different threads. Normally, sqlite allows this. However, we do sqlite3_enable_shared_cache(1); (see sqlite shared-cache mode) which makes multiple connections share the same cache. This is important for performance. However, there is no lock for cache access, meaning it will break if you use if from more than one thread.

SQLite Locking

SQLite locks the entire database; that is, any active readers will cause an attempt to write to return SQLITE_BUSY, and an active writer will cause any attempt to read to return SQLITE_BUSY. A statement is considered active from the first step() until reset() is called. execute() calls step() and reset() in one go. A common problem is forgetting to reset() a statement after you've finished step()'ing through.

While a given SQLite connection is capable of having multiple statements open, its locking model limits what these statements can do concurrently (reading or writing). It is in fact possible for multiple statements to be actively reading at one time. It is not possible, however, for multiple statements to be reading and writing at one time on the same table -- even if they are derived from the same connection.

SQLite has a two-tiered locking model: connection level and table level. Most people are familiar with the connection (database) level locking: multiple readers but only one writer. The table-level (B-Tree) locks are what can sometimes be confusing. (Internally, each table in the database has its own B-Tree, so "table" and "B-Tree" are technically synonymous).

Table-level locks

You would think that if you have only one connection, and it locks the database for writing, you could use multiple statements to do whatever you want. Not entirely. You must be aware of table-level (B-Tree) locks, which are maintined by statement handles traversing the database (i.e. open SELECT statements).

The general rule is this: a statement handle may not modify a table (B-Tree) which other statement handles are reading (have open cursors on) -- even if that statement handle shares the same connection (transaction context, database lock, etc.) with the other statement handles. Attempts to do so will still block (or return SQLITE_BUSY).

This problem often crops up when you attempt to iterate over a table with one statement and modify records within it using another statement. This will not work (or carries a high probability of not working, depending on the optimizer's involvement (see below)). The modifying statement will block because the reading statement has an open cursor on the table.

Working around locking problems

The solution is to follow (1) as described above. Theoretically, (2) actually shouldn't work with SQLite 3.x. In this scenario, database locks come into play (with multiple connections) in addition to table locks. Connection 2 (modifying connection) will not be able to modify (write to) the database while the Connection 1 (reading connection) is reading it. Connection 2 will require an exclusive lock to execute a modifying SQL command, which it cannot get as long as Connection 1 has active statements reading the database (Connection 1 has a shared read lock during this time which prohibits any other connections from getting an exclusive lock).

Another option is to use a temporary table. Create a temporary table containing the results of the table of interest, iterate over it (putting the reading statement's table lock on the temp table) and then the modifing statement can make changes to the real table without any problem. This can be done with statements derived from a single connection (transaction context). This scenario sometimes happens behind the scenes anyway as ORDER BY can produce temporary tables internally. However, it is not safe to assume that the optimizer will do this in all cases. Explicitly creating a temporary table is only safe way to do perform this latter option.

{{ wiki.languages( { "en": "en/Storage", "es": "es/Almacenamiento", "pl": "pl/Storage" } ) }}

Source de la révision

<p>{{template.Traduction_en_cours("Storage")}}

</p><p>Storage est une API de base de données dans <a href="fr/Firefox_2">Firefox 2</a> et suivante pilotée par <a class="external" href="http://www.sqlite.org/">sqlite</a>. Elle est disponible aux appels avec permission, tels que du code <a href="fr/Chrome">chrome</a> et des <a href="fr/Extensions">extensions</a>, mais <i>pas</i> aux pages Web. Son statut actuel est "en développement", ce qui signifie que les API peuvent être modifiées à n'importe quel moment. Il se peut en effet que l'API soit légèrement modifiée entre Firefox 2 alpha 2 et Firefox 2, et également entre Firefox 2 et Firefox 3.
</p><p>Storage peut parfois être confondu avec la fonctionnalité <a class="external" href="http://www.whatwg.org/specs/web-apps/current-work/#scs-client-side">WHATWG DOM storage</a> de Firefox 2 qui permet à des pages Web d'enregistrer des données permanentes. L'API Storage s'adresse uniquement aux auteurs d'extensions et aux composants de Firefox.
</p><p>Ce document traite l'API mozStorage et quelques particularités de sqlite. Il <i>ne</i> traite <i>pas</i> du SQL ou de l'utilisation de sqlite. Pour ces autres informations, vous devrez consulter vos références favorites sur SQL. Vous pouvez également consulter <a class="external" href="http://www.sqlite.org/docs.html">la documentation sur sqlite</a> et particulièrement celle sur <a class="external" href="http://www.sqlite.org/lang.html">la compréhension du langage de requêtes sur sqlite</a>. Pour obtenir plus d'aide sur l'API mozStorage, vous pouvez poster sur le serveur de news mozilla.dev.apps.firefox sur news.mozilla.org. Pour signaler des bogues, utilisez <a class="external" href="https://bugzilla.mozilla.org/enter_bug.cgi?product=Toolkit&amp;component=Storage">Bugzilla</a> (product "Toolkit", component "Storage").
</p><p>Consultez <a href="fr/Storage/Performance">Storage:Performance</a> pour optimiser les performances de connexion de votre base de données.
</p><p><a class="external" href="http://sqlitebrowser.sourceforge.net/">SQLite Database Browser</a> est un outil libre, disponible sur plusieurs plateformes, permettant l'examen de bases de données existantes et le test des états SQL.
</p>
<h4 name="Pr.C3.A9ambule"> Préambule </h4>
<p>mozStorage se présente comme n'importe quelle autres systèmes de bases de données. La procédure complète d'utilisation est la suivante :
</p>
<ul><li> Ouverture d'une connexion vers la base de données de votre choix.
</li><li> Création d'une variable d'exécution de la connexion.
</li><li> Liaison de paramètres à la variable si nécessaire.
</li><li> Exécution de la requête.
</li><li> Réinitialisation de la variable.
</li></ul>
<h4 name="Opening_a_connection"> Opening a connection </h4>
<p>The storage service's first initialization must be from the main thread. You will get an error if you initialize it the first time from another thread. Therefore, if you want to use the service from a thread, be sure to call getService from the main thread to ensure the service has been created.
</p><p>C++ example of opening a connection to "asdf.sqlite" in the user's profile directory:
</p>
<pre>nsCOMPtr&lt;nsIFile&gt; dbFile;
rv = NS_GetSpecialDirectory(NS_APP_USER_PROFILE_50_DIR,
                            getter_AddRefs(dbFile));
NS_ENSURE_SUCCESS(rv, rv);
rv = dbFile-&gt;Append(NS_LITERAL_STRING("asdf.sqlite"));
NS_ENSURE_SUCCESS(rv, rv);

mDBService = do_GetService(MOZ_STORAGE_SERVICE_CONTRACTID, &amp;rv);
NS_ENSURE_SUCCESS(rv, rv);
rv = mDBService-&gt;OpenDatabase(dbFile, getter_AddRefs(mDBConn));
NS_ENSURE_SUCCESS(rv, rv);
</pre>
<p><code>MOZ_STORAGE_SERVICE_CONTRACTID</code> is defined in {{template.Source("storage/build/mozStorageCID.h")}}. It's value is <code>"@mozilla.org/storage/service;1"</code>
</p><p>JavaScript example:
</p>
<pre>var file = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
file.append("asdf.sqlite");

var storageService = Components.classes["@mozilla.org/storage/service;1"]
                        .getService(Components.interfaces.mozIStorageService);
var mDBConn = storageService.openDatabase(file);
</pre>
<dl><dd><div class="note">Note: The OpenDatabase function is subject to change. It will likely be enhanced/simplified to make it more difficult to get into trouble.</div>
</dd></dl>
<p>It may be tempting to give your database a name ending in ".sdb" for <b>s</b>qlite <b>d</b>ata<b>b</b>ase, but this is <i>not recommended.</i> This extension is treated specially by Windows as a known extension for an "Application Compatability Database" and changes are backed up by the system automatically as part of system restore functioality. This can result in much higher overhead file operations.
</p>
<h4 name="Creating_a_statement"> Creating a statement </h4>
<p>There are two ways to create a statement. If you have no parameters and the statement doesn't return any data, use <code>mozIStorageConnection.executeSimpleSQL</code>.
</p>
<pre>C++:
rv = mDBConn-&gt;ExecuteSimpleSQL(NS_LITERAL_CSTRING("CREATE TABLE foo (a INTEGER)"));

JS:
mDBConn.executeSimpleSQL("CREATE TABLE foo (a INTEGER)");
</pre>
<p>Otherwise, you should prepare a statement using <code>mozIStorageConnection.createStatement</code>:
</p>
<pre>C++:
nsCOMPtr&lt;mozIStorageStatement&gt; statement;
rv = mDBConn-&gt;CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM foo WHERE a = ?1"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);

JS:
var statement = mDBConn.createStatement("SELECT * FROM foo WHERE a = ?1");
</pre>
<p>This example uses the placeholder "?1" for a parameter to be bound later (see next section).
</p><p>Once you have a prepared statement, you can bind parameters to it, execute it, and reset it over and over. If you are doing a statement many times, using a precompiled statement will give you a noticable performance improvement because the SQL query doesn't need to be parsed each time.
</p><p>If you are familiar with sqlite, you may know that prepared statements are invalidated when the schema changes of the database. Fortunately, mozIStorageStatement detects the error and will recompile the statement as needed. Therefore, once you create a statement, you don't need to worry when changing the schema; all statements will continue to transparently work.
</p>
<h4 name="Binding_parameters"> Binding parameters </h4>
<p>It is generally best to bind all parameters separately rather than try to construct SQL strings on the fly containing the parameters. Among other things, this prevents SQL injection attacks, since a bound parameter can never be executed as SQL.
</p><p>You bind parameters to a statement that has placeholders. The placeholders are addressed by index, starting from "?1", then "?2"... You use the statement functions BindXXXParameter(0) BindXXXParameter(1)... to bind to these placeholders.
</p>
<dl><dd><div class="note">Watch out: The indices in the placeholders count from 1. The integers passed to the binding functions count from 0. This means "?1" corresponds to parameter 0, "?2" corresponds to parameters 1, etc.</div>
</dd></dl>
<p>A placeholder can appear multiple times in the SQL string and all instances will be replaced with the bound value. Unbound parameters will be interpreted as NULL.
</p><p>Binding functions available in <code>mozIStorageStatement</code> (see {{template.Source("storage/public/mozIStorageStatement.idl")}}) are:
</p>
<ul><li> <code>bindUTF8StringParameter(in unsigned long aParamIndex, in AUTF8String aValue)</code>
</li><li> <code>bindStringParameter(in unsigned long aParamIndex, in AString aValue)</code>
</li><li> <code>bindDoubleParameter(in unsigned long aParamIndex, in double aValue)</code>
</li><li> <code>bindInt32Parameter(in unsigned long aParamIndex, in long aValue)</code>
</li><li> <code>bindInt64Parameter(in unsigned long aParamIndex, in long long aValue)</code>
</li><li> <code>bindNullParameter(in unsigned long aParamIndex)</code>
</li><li> <code>bindBlobParameter(in unsigned long aParamIndex, {{mediawiki.external('array,const,size_is(aValueSize)')}} in octet aValue, in unsigned long ValueSize)</code> (for binary data)
</li></ul>
<p>C++ example:
</p>
<pre>nsCOMPtr&lt;mozIStorageStatement&gt; statement;
rv = mDBConn-&gt;CreateStatement(NS_LITERAL_CSTRING("SELECT * FROM foo WHERE a = ?1 AND b &gt; ?2"),
                              getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);
rv = statement-&gt;BindUTF8StringParameter(0, "hello"); // "hello" will be substituted for "?1"
NS_ENSURE_SUCCESS(rv, rv);
rv = statement-&gt;BindInt32Parameter(1, 1234); // 1234 will be substituted for "?2"
NS_ENSURE_SUCCESS(rv, rv);
</pre>
<p>Javascript example:
</p>
<pre>var statement = mDBConn.createStatement("SELECT * FROM foo WHERE a = ?1 AND b &gt; ?2");
statement.bindUTF8StringParameter(0, "hello");
statement.bindInt32Parameter(1, 1234);
</pre>
<h4 name="Executing_a_statement"> Executing a statement </h4>
<p>The main way to execute a statement is with <code>mozIStorageStatement.executeStep</code>. This function allows you to enumerate all the result rows your statement produces, and will notify you when there are no more results.
</p><p>After a call to <code>executeStep</code>, you can use the getter functions in mozIStorageValueArray (see {{template.Source("storage/public/mozIStorageValueArray.idl")}}. mozIStorageStatement implements mozIStorageValueArray. These functions are:
</p>
<ul><li> <code>long getInt32(in unsigned long aIndex);</code>
</li><li> <code>long long getInt64(in unsigned long aIndex);</code>
</li><li> <code>double getDouble(in unsigned long aIndex);</code>
</li><li> <code>AUTF8String getUTF8String(in unsigned long aIndex);</code>
</li><li> <code>AString getString(in unsigned long aIndex);</code>
</li><li> <code>void getBlob(in unsigned long aIndex, out unsigned long aDataSize, {{mediawiki.external('array,size_is(aDataSize)')}} out octet aData);</code> warning: data will be NULL if dataSize is 0.
</li><li> <code>boolean getIsNull(in unsigned long aIndex);</code> Returns true if the cell is NULL (different from an empty string)
</li></ul>
<p>You can get the type of a value from <code>mozIStorageValueArray.getTypeOfIndex</code>, which returns the type of the specified column. Be careful: sqlite is not a typed database. Any type can be put into any cell, regardless of the type declared for the column. If you request a different type, sqlite will do its best to convert them, and will do some default value if it is impossible. Therefore, it is impossible to get type errors, but you may get weird data out.
</p><p>C++ code can also use <code>AsInt32</code>, <code>AsDouble</code>, etc. functions which return the value as a more convenient C++ return value. Watch out, though, because you won't get any errors if your index is invalid. Other errors are impossible, because sqlite will always convert types, even if they don't make sense.
</p><p>C++ example:
</p>
<pre>PRBool hasMoreData;
while (NS_SUCCEEDED(statement-&gt;ExecuteStep(&amp;hasMoreData)) &amp;&amp; hasMoreData) {
  PRInt32 value = statement-&gt;AsInt32(0);
  // use the value...
}
</pre>
<p>Javascript example:
</p>
<pre>while (statement.executeStep()) {
  var value = statement.getInt32(0);
  // use the value...
}
</pre>
<p><code>mozIStorageStatement.execute()</code> is a convenience function for when you are getting no data out of the statement. It steps the statement once and resets it. This can be useful for insert statements because it really cleans up the code:
</p>
<pre>var statement = mDBConn.createStatement("INSERT INTO my_table VALUES (?1)");
statement.bindInt32Parameter(52);
statement.execute();
</pre>
<h4 name="Resetting_a_statement"> Resetting a statement </h4>
<p>It is important to reset statements that are no longer being used. Un-reset write statements will keep a lock on the tables and will prevent other statements from accessing it. Un-reset read statements will prevent writes.
</p><p>When the statement object is freed, it's corresponding database statement is closed. If you are using C++ and you know that all references will be destroyed, you don't have to explicitly reset the statement. Also, if you use <code>mozIStorageStatement.execute()</code>, you don't need to explicitly reset the statement; this function will reset it for you. Otherwise, call <code>mozIStorageStatement.reset()</code>.
</p><p>JavaScript callers should ensure that statements are reset. Be particularly careful about exceptions. You will want to make sure to reset your statements even if an exception is fired, or subsequent access to the database may not be possible. Resetting a statement is relatively lightweight, and nothing bad happens if it's already reset, so don't worry about unnecessary resets.
</p>
<pre>var statement = connection.createStatement(...);
try {
  // use the statement...
} finally {
  statement.reset();
}
</pre>
<p>C++ callers must do the same. There is a scoped object in {{template.Source("storage/public/mozStorageHelper.h")}} called mozStorageStatementScoper which will ensure that a given statement is reset when the enclosing scope is exited. It is hightly recommended that you use this object if possible.
</p>
<pre>void someClass::someFunction()
{
  mozStorageStatementScoper scoper(mStatement)
  // use the statement
}
</pre>
<h4 name="Transactions"> Transactions </h4>
<p>mozIStorageConnection has functions for beginning and ending transactions. If you do not explicitly use transactions, an implicit transaction will be created for you for each statement. This has major performance implications. There is overhead for each transaction, especially for commits. You will therefore see a large performance win when you are doing multiple statements in a row if you put them in a transaction. See <a href="fr/Storage/Performance">Storage:Performance</a> for more performance information.
</p><p>The major difference between other database systems is that sqlite does not support nested transactions. This means that once a transaction is open, you can not open another transaction. You can check <code>mozIStorageConnection.transactionInProgress</code> to see if a transaction is currently in progress.
</p><p>You can also just execute "BEGIN TRANSACTION" and "END TRANSACTION" directly as SQL statements (this is what the connection does when you call the functions). However, use of <code>mozIStorageConnection.beginTransaction</code> and related functions are <i>strongly</i> recommended because it stores transaction state in the connection. Otherwise, the attribute <code>transactionInProgress</code> will have the wrong value.
</p><p>sqlite has several types of transactions:
</p>
<ul><li> mozIStorageConnection.TRANSACTION_DEFERRED: The default. The database lock is acquired when needed (usually the first time you execute a statement in the transaction).
</li></ul>
<ul><li> mozIStorageConnection.TRANSACTION_IMMEDIATE: Get a read lock on the database immediately.
</li></ul>
<ul><li> mozIStorageConnection.TRANSACTION_EXCLUSIVE: Get a write lock on the database immediately.
</li></ul>
<p>You can pass this type of transaction to <code>mozIStorageConnection.beginTransactionAs</code> to determine what kind of transaction you need. Keep in mind that if another transaction has already started, this operation will not succeed. Generally, the default TRANSACTION_DEFERRED type is sufficient and you shouldn't use the other types unless you really know why you need them. For more information, see the sqlite documentation about <a class="external" href="http://www.sqlite.org/lang_transaction.html">BEGIN TRANSACTION</a> and <a class="external" href="http://www.sqlite.org/lockingv3.html">locking</a>.
</p>
<pre>var ourTransaction = false;
if (mDBConn.transactionInProgress) {
  ourTransaction = true;
  mDBConn.beginTransactionAs(mDBConn.TRANSACTION_DEFERRED);
}

// ... use the connection ...

if (ourTransaction)
  mDBConn.commitTransaction();
</pre>
<p>From C++ code, you can use the mozStorageTransaction helper class defined in {{template.Source("storage/public/mozStorageHelper.h")}}. This class will begin a transaction of the specified type on the specified connection when it comes into scope, and will either commit or rollback the transaction when it goes out of scope. If a transaction is already in progress, the transaction helper class will not do anything.
</p><p>It also has functions for explicitly committing. The typical use is that you create the class defaulting to rollback, and then explicitly commit the transaction when processing has succeeded:
</p>
<pre>nsresult someFunction()
{
  // deferred transaction (the default) with rollback on failure
  mozStorageTransaction transaction(mDBConn, PR_FALSE);

  // ... use the connection ...

  // everything succeeded, now explicitly commit
  return transaction.Commit();
}
</pre>
<h4 name="How_to_corrupt_your_database"> How to corrupt your database </h4>
<ul><li> Read this document: <a class="external" href="http://www.sqlite.org/lockingv3.html">File locking and concurrency in sqlite version 3</a>, especially the section on corruption.
</li></ul>
<ul><li> Open more than one connection to the same file with names that aren't exactly the same as determined by <code>strcmp</code>. 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.
</li></ul>
<ul><li> Access a database from a symbolic or hard link.
</li></ul>
<ul><li> Open connections to the same database from more than one thread (see "Thread safety" below).
</li></ul>
<ul><li> Access a connection or statement from more than one thread (see "Thread safety" below).
</li></ul>
<ul><li> Open the database from an external program while it is open in Mozilla. Our caching breaks the normal file-locking in sqlite that allows this to be done safely.
</li></ul>
<h4 name="Thread_safety"> Thread safety </h4>
<p>The mozStorage service and sqlite are threadsafe. However, no other mozStorage or sqlite objects or operations are threadsafe.
</p>
<ul><li> The storage service must be created on the main thread. If you want to access the service from another thread, you should be sure that you call getService from the main thread ahead of time.
</li></ul>
<ul><li> You can not access a connection or statement from multiple threads. These storage objects are not threadsafe, and the sqlite representations of them are not threadsafe either. Even if you do locking and ensure that only one thread is doing something at once, there may be problems. This case hasn't been tested, and there may be some internal per-thread state in sqlite. It is strongly advised that you don't do this.
</li></ul>
<ul><li> You can not access a single database from multiple connections from different threads. Normally, sqlite allows this. However, we do <code>sqlite3_enable_shared_cache(1);</code> (see <a class="external" href="http://www.sqlite.org/sharedcache.html">sqlite shared-cache mode</a>) which makes multiple connections share the same cache. This is important for performance. However, there is no lock for cache access, meaning it will break if you use if from more than one thread.
</li></ul>
<h4 name="SQLite_Locking"> SQLite Locking </h4>
<p>SQLite locks the entire database; that is, any active readers will cause an attempt to write to return SQLITE_BUSY, and an active writer will cause any attempt to read to return SQLITE_BUSY.  A statement is considered active from the first step() until reset() is called.  execute() calls step() and reset() in one go.  A common problem is forgetting to reset() a statement after you've finished step()'ing through.
</p><p>While a given SQLite connection is capable of having multiple statements open, its locking model limits what these statements can do concurrently (reading or writing). It is in fact possible for multiple statements to be actively reading at one time. It is not possible, however, for multiple statements to be reading and writing at one time <i>on the same table</i> -- even if they are derived from the same connection.
</p><p>SQLite has a two-tiered locking model: connection level and table level. Most people are familiar with the connection (database) level locking: multiple readers but only one writer. The table-level (B-Tree) locks are what can sometimes be confusing. (Internally, each table in the database has its own B-Tree, so "table" and "B-Tree" are technically synonymous).
</p>
<h5 name="Table-level_locks"> Table-level locks </h5>
<p>You would think that if you have only one connection, and it locks the database for writing, you could use multiple statements to do whatever you want. Not entirely. You must be aware of table-level (B-Tree) locks, which are maintined by statement handles traversing the database (i.e. open SELECT statements).
</p><p>The general rule is this: a statement handle may <b>not</b> modify a table (B-Tree) which other statement handles are reading (have open cursors on) -- even if that statement handle shares the same connection (transaction context, database lock, etc.) with the other statement handles. <i>Attempts to do so will still block (or return SQLITE_BUSY)</i>.
</p><p>This problem often crops up when you attempt to iterate over a table with one statement and modify records within it using another statement. This will not work (or carries a high probability of not working, depending on the optimizer's involvement (see below)). The modifying statement will block because the reading statement has an open cursor on the table.
</p>
<h5 name="Working_around_locking_problems"> Working around locking problems </h5>
<p>The solution is to follow (1) as described above. Theoretically, (2) actually shouldn't work with SQLite 3.x. In this scenario, database locks come into play (with multiple connections) in addition to table locks. Connection 2 (modifying connection) will not be able to modify (write to) the database while the Connection 1 (reading connection) is reading it. Connection 2 will require an exclusive lock to execute a modifying SQL command, which it cannot get as long as Connection 1 has active statements reading the database (Connection 1 has a shared read lock during this time which prohibits any other connections from getting an exclusive lock).
</p><p>Another option is to use a temporary table. Create a temporary table containing the results of the table of interest, iterate over it (putting the reading statement's table lock on the temp table) and then the modifing statement can make changes to the real table without any problem. This can be done with statements derived from a single connection (transaction context). This scenario sometimes happens behind the scenes anyway as ORDER BY can produce temporary tables internally. However, it is not safe to assume that the optimizer will do this in all cases. Explicitly creating a temporary table is only safe way to do perform this latter option.
</p>{{ wiki.languages( { "en": "en/Storage", "es": "es/Almacenamiento", "pl": "pl/Storage" } ) }}
Revenir à cette révision