Places/Places SQL queries best practices: Difference between revisions

Jump to navigation Jump to search

Places/Places SQL queries best practices (view source)

Revision as of 22:58, 26 January 2009

2,560 bytes added ,  26 January 2009
no edit summary
(New page: == Places SQL queries best practices == This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style. Add-o...)
 
No edit summary
Line 1: Line 1:
== Places SQL queries best practices ==
= Places SQL queries best practices =
This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style.  Add-on developers are strongly recommended to use the Places API to query the database, since direct queries could end up corrupting the database when schema changes [https://developer.mozilla.org/Querying_Places Querying Places].
This is a collection of suggestions and hints to help internal developers hack into the Places database correctly and with common code style.  Add-on developers are strongly recommended to use the Places API to query the database, since direct queries could end up corrupting the database when schema changes [https://developer.mozilla.org/Querying_Places Querying Places].


For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]
For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]


=== Table aliases ===
== Table aliases ==
When querying Places tables and in need of aliasing a table for joins or complext queries, is always better to use the same aliases in all queries.
When querying Places tables and in need of aliasing a table for joins or complext queries, is always better to use the same aliases in all queries.


Line 53: Line 53:
* when referring to a column of a table having an alias, always specify the alias, even if there's no risk of column names conficts.
* when referring to a column of a table having an alias, always specify the alias, even if there's no risk of column names conficts.


=== Querying the database in the temp tables era ===
== Querying the database in the temp tables era ==
Starting from Firefox 3.1 <i>moz_places</i> and <i>moz_historyvisits</i> tables are partitioned between disk and memory, to avoid common locking issues with OS's fsyncs calls ({{Bug|442967}}).
Starting from Firefox 3.1 <i>moz_places</i> and <i>moz_historyvisits</i> tables are partitioned between disk and memory, to avoid common locking issues with OS's fsyncs calls ({{Bug|442967}}).
New memory tables are called <i>moz_places_temp</i> and <i>moz_historyvisits_temp</i>, with the same columns and indexes as the disk tables.
New memory tables are called <i>moz_places_temp</i> and <i>moz_historyvisits_temp</i>, with the same columns and indexes as the disk tables.
Line 131: Line 131:
This query gets the 10 most recent visit ids, this involves some code duplication, but is really faster than simply <i>UNION</i> all results when there are a lot of records.  Notice however in the external query we can't use column names, instead we have to use the column indices (2: eg visit_date).
This query gets the 10 most recent visit ids, this involves some code duplication, but is really faster than simply <i>UNION</i> all results when there are a lot of records.  Notice however in the external query we can't use column names, instead we have to use the column indices (2: eg visit_date).


=== Querying multiple tables ===
== Querying multiple tables ==
When querying multiple tables involving foreign keys, please always avoid cartesian products like:
When querying multiple tables involving foreign keys, please always avoid cartesian products like:
<pre>
<pre>
Line 149: Line 149:
* <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned.
* <i>JOIN</i> is used when the right table must obey the <i>JOIN</i> condition, so records without a match in the right table won't be returned.


=== Places database entry points ===
== Querying Places database examples ==
This section contains example queries for common tasks, notice most of this can already be done through APIs, but in some case toolkit code could directly query to avoid a perf hit.
This section contains an example of how to create a statement to query the Places database. This is for quick reference, further informations are available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation]


<i>Coming soon...</i>
Cpp code:
<pre>
nsCOMPtr<mozIStorageStatement> statement;
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(
    "INSERT INTO moz_bookmarks "
      "(type, parent, position, dateAdded) VALUES (?1, ?2, ?3, ?4)"),
  getter_AddRefs(statement));
NS_ENSURE_SUCCESS(rv, rv);
 
rv = statement->BindInt64Parameter(0, TYPE_SEPARATOR);
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindInt64Parameter(1, aParent);
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindInt32Parameter(2, index);
NS_ENSURE_SUCCESS(rv, rv);
rv = statement->BindInt64Parameter(3, PR_Now());
NS_ENSURE_SUCCESS(rv, rv);
 
rv = statement->Execute();
NS_ENSURE_SUCCESS(rv, rv);
</pre>
 
JS code:
<pre>
var stmt = createStatement(
  "INSERT INTO moz_bookmarks " +
  "(id, fk) " +
  "VALUES (NULL, :place_id)");
stmt.params.place_id = 1337.
try {
  stmt.execute();
} catch (e) {
  dump("Statement error: " + e.message);
} finally {
  stmt.finalize();
}
</pre>
 
Places is using some precompiled statement to speed up the most used queries creation.  Those statements are created at Places init, so it's important to choice which queries need a precompiled statement, since every new addition can hit Ts.  Precompiled statements can cause leaks if not correctly destroyed, so when creating a new one, remember to add it to the array in ::FinalizeStatements() method.
 
When querying with a precompiled statement it is important to use a  mozStorageStatementScoper, it will ensure the statement is correctly reset on scope exiting:
<pre>
{ // init of scope
  mozStorageStatementScoper scoper(mDBGetRedirectDestinations);
  rv = mDBGetRedirectDestinations->BindInt64Parameter(0, aCurrentSource);
  NS_ENSURE_SUCCESS(rv, rv);
  rv = mDBGetRedirectDestinations->BindInt64Parameter(1, aMinTime);
  NS_ENSURE_SUCCESS(rv, rv);
 
  PRBool hasMore;
  while (NS_SUCCEEDED(mDBGetRedirectDestinations->ExecuteStep(&hasMore)) &&
        hasMore) {
    // do something
  }
} // end of scope
// precompiled statement gets resetted here
</pre>
 
 
in Javascript instead, remember to use statement.reset() when you need to reuse a stetement changing params, and statement.finalize() when you won't need it anymore.
<pre>
var stmt = createStatement(
  "SELECT b.id FROM moz_bookmarks b " +
  "JOIN moz_places h ON h.id = b.fk " +
  "WHERE h.url = :url");
stmt.params.url = "http://www.mozilla.org/";
while(stmt.step()) {
  dump(stmt.row.id);
}
stmt.reset();
stmt.params.url = "http://www.mozilla.com/";
while(stmt.step()) {
  dump(stmt.row.id);
}
stmt.finalize();
</pre>
 
== Asynchronous statements ==


=== Perf hints ===
== Perf hints ==
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.
* When possible don't rely on SQLite internal optimizer, it can do a very good job, but we can do also better, so always try to optimize your queries.
* When possible don't rely on SQLite internal optimizer, it can do a very good job, but we can do also better, so always try to optimize your queries.
Mak77
Confirmed users
595

edits

Retrieved from "https://wiki.allizom.org/Special:MobileDiff/125594"

Navigation menu