https://wiki.mozilla.org/api.php?action=feedcontributions&user=PeterMortensen&feedformat=atomMozillaWiki - User contributions [en]2024-03-29T06:24:01ZUser contributionsMediaWiki 1.27.4https://wiki.mozilla.org/index.php?title=Places/Places_SQL_queries_best_practices&diff=1243429Places/Places SQL queries best practices2022-07-20T21:24:27Z<p>PeterMortensen: /* Perf hints */ Copy edited (e.g. ref. <en.wiktionary.org/wiki/existence#Noun> and <en.wikipedia.org/wiki/Sentence_clause_structure#Run-on_sentences>). Expanded.</p>
<hr />
<div>= Places SQL queries best practices =<br />
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].<br />
<br />
For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]<br />
<br />
== Table aliases ==<br />
When querying Places tables and in need of aliasing a table for joins or complex queries, it is always better to use the same aliases in all queries.<br />
<br />
This is needed not only for our internal code style, but also to avoid common performance killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s. Indeed referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.<br />
For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.<br />
<br />
<center><br />
{| border="1" cellpadding="2"<br />
|+Commonly used table aliases<br />
! Table || Alias || On-conflict alias<br />
|-<br />
| moz_anno_attributes || n || <br />
|-<br />
| moz_annos || a ||<br />
|-<br />
| moz_bookmarks || b ||<br />
|- <br />
| moz_bookmarks_roots || r || <br />
|-<br />
| moz_favicons || f || <br />
|-<br />
| moz_historyvisits || v || <br />
|-<br />
| moz_historyvisits_temp || v || v_t <br />
|-<br />
| moz_inputhistory || i || <br />
|-<br />
| moz_items_annos || a || t<br />
|-<br />
| moz_keywords || k ||<br />
|-<br />
| moz_places || h || p<br />
|-<br />
| moz_places_temp || h || h_t<br />
|}<br />
</center><br />
<br />
When joining a table with itself, it could make sense to define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":<br />
<pre><br />
SELECT b.id FROM moz_bookmarks b<br />
JOIN moz_bookmarks parents ON parents.id = b.parent<br />
WHERE parents.title = 'folder'<br />
</pre><br />
<br />
The code style for using aliases is:<br />
* if the alias is in a real (temporary) table from places.sqlite, use <i>TABLE_NAME ALIAS</i><br />
* if the alias is in a subquery, use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i><br />
* when referring to a column of a table having an alias, always specify the alias, even if there isn't any risk of column names conflicts.<br />
<br />
== Querying the database in the temp tables era ==<br />
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}}).<br />
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.<br />
<br />
NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.<br />
<br />
<b>WARNING: don't use any Places service on quit-application or later, since on quit-application we will close the database connection and data could be lost. Use quit-application-granted instead.</b><br />
<br />
The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.<br />
Since SQLite as of now (3.6.10) does not support indices on views, we have to follow two different paths based on the type of query we are executing:<br />
<br />
* <i>INSERT</i>s/<i>UPDATE</i>s/<i>DELETE</i>s must always act on the views<br />
** Real tables have triggers attached to them to allow for syncing so in no case an implementer should try to execute one of these operations on the temp tables.<br />
** Views have triggers attached to them that ensure data are written/moved to memory tables when needed.<br />
* <i>SELECT</i> must always act on real tables, since due to the lack of indices querying the view would be slow. We can <i>SELECT</i> from the views in tests though, when we are sure perf is not an issue.<br />
<br />
So this is correct:<br />
<pre><br />
DELETE FROM moz_places_view<br />
WHERE id = 1337<br />
</pre><br />
While this is completely <strong>WRONG</strong>:<br />
<pre><br />
DELETE FROM moz_places_temp<br />
WHERE id = 1337<br />
</pre><br />
<br />
When using <i>SELECT</i> we must always be sure to query both tables, new changed data will be in the temp table, while the disk table will be synced in background or when a new bookmarks is added (to prevent bookmarks corruption). Still some place/visit won't never be synced to disk, for example visits with a <i>visit_type = TRANSITION_EMBED</i>.<br />
<br />
To query both tables we can use a special SQL construct:<br />
* UNION merges the resultsets of 2 queries discarding duplicates<br />
* UNION ALL merges the resultsets of 2 queries<br />
<br />
We use one of these based on the query, usually <i>UNION</i> is slower than <i>UNION ALL</i>, so the actual way we query both tables for a place with a certain url may be:<br />
<pre><br />
SELECT id FROM moz_places_temp<br />
WHERE url = 'http://www.mozilla.org'<br />
UNION ALL<br />
SELECT id FROM moz_places<br />
WHERE url = 'http://www.mozilla.org'<br />
AND id NOT IN(SELECT id FROM moz_places_temp)<br />
</pre><br />
<br />
Due to the fact tables are partitioned, we cannot get the last inserted id from one of those, instead we should get the max between both tables, to do that we may, for example, execute:<br />
<pre><br />
SELECT MAX(<br />
(SELECT MAX(id) FROM moz_places_temp),<br />
(SELECT MAX(id) FROM moz_places)<br />
)<br />
</pre><br />
<br />
A similar trick can be applied to <i>IFNULL</i>, this, in many complex queries, can help removing an expensive JOIN, to only get a single value:<br />
<pre><br />
SELECT IFNULL(<br />
(SELECT id FROM moz_places_temp WHERE url = 'http://www.mozilla.org'),<br />
(SELECT id FROM moz_places WHERE url = 'http://www.mozilla.org')<br />
) AS id<br />
</pre><br />
<br />
Notice SQLite does support only one definition of <i>GROUP BY</i>, <i>ORDER BY</i>, <i>LIMIT</i> for <i>UNION</i>s, but sometimes we don't need to <i>UNION</i> all results from both queries. In this case we can use a small trick:<br />
<pre><br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits_temp<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
UNION ALL<br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
ORDER BY 2 DESC<br />
LIMIT 10<br />
</pre><br />
<br />
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).<br />
<br />
== Querying multiple tables ==<br />
When querying multiple tables involving foreign keys, please always avoid Cartesian products like:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h, moz_bookmarks b<br />
WHERE h.id = b.fk<br />
</pre><br />
Instead use a <i>JOIN</i>:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h<br />
JOIN moz_bookmarks b ON h.id = b.fk<br />
</pre><br />
That ensures the query is easier to expand, is less error-prone for future developers and more performance friendly.<br />
We actually use two types of <i>JOIN</i>s:<br />
* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.<br />
* <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.<br />
<br />
== Querying Places database examples ==<br />
This section contains an example of how to create a statement to query the Places database. This is for quick reference, and further information is available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation].<br />
<br />
C++ code:<br />
<pre><br />
nsCOMPtr<mozIStorageStatement> statement;<br />
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(<br />
"INSERT INTO moz_bookmarks "<br />
"(type, parent, position, dateAdded) VALUES (?1, ?2, ?3, ?4)"),<br />
getter_AddRefs(statement));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->BindInt64Parameter(0, TYPE_SEPARATOR);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(1, aParent);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt32Parameter(2, index);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(3, PR_Now()); <br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->Execute();<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
JavaScript code:<br />
<pre><br />
var stmt = createStatement(<br />
"INSERT INTO moz_bookmarks " +<br />
"(id, fk) " +<br />
"VALUES (NULL, :place_id)");<br />
stmt.params.place_id = 1337.<br />
try {<br />
stmt.execute();<br />
} catch (e) {<br />
dump("Statement error: " + e.message);<br />
} finally {<br />
stmt.finalize();<br />
}<br />
</pre><br />
<br />
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 choose 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.<br />
<br />
When querying with a precompiled statement it is important to use a mozStorageStatementScoper. It will ensure the statement is correctly reset on scope exiting:<br />
<pre><br />
{ // init of scope<br />
mozStorageStatementScoper scoper(mDBGetRedirectDestinations);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(0, aCurrentSource);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(1, aMinTime);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
PRBool hasMore;<br />
while (NS_SUCCEEDED(mDBGetRedirectDestinations->ExecuteStep(&hasMore)) &&<br />
hasMore) {<br />
// do something<br />
}<br />
} // end of scope<br />
// precompiled statement gets reset here<br />
</pre><br />
<br />
<br />
In JavaScript instead, remember to use statement.reset() when you need to reuse a statement changing parameters, and statement.finalize() when you won't need it anymore.<br />
<pre><br />
var stmt = createStatement(<br />
"SELECT b.id FROM moz_bookmarks b " +<br />
"JOIN moz_places h ON h.id = b.fk " +<br />
"WHERE h.url = :url");<br />
stmt.params.url = "http://www.mozilla.org/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.reset();<br />
stmt.params.url = "http://www.mozilla.com/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.finalize();<br />
</pre><br />
<br />
== Asynchronous statements ==<br />
Asynchronous statements are executed by mozStorage in a separate thread, and they are managed as a FIFO queue. Thanks to the separate threads using such a statement is a good idea, since it won't lock the UI. But at the same time we can't rely on immediate database values, since we have to wait for it to be executed (see [https://developer.mozilla.org/En/MozIStorageStatementCallback mozIStorageStatementCallback])<br />
<br />
To execute a single async statement in Cpp:<br />
<pre><br />
nsCOMPtr<YourStatementCallback> callback =<br />
new YourStatementCallback();<br />
// this is needed to cancel a pending statement, we don't use it actually<br />
nsCOMPtr<mozIStoragePendingStatement> canceler;<br />
rv = stmt->ExecuteAsync(callback, getter_AddRefs(canceler));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
while in JS:<br />
<pre><br />
var stmt = dbConn.createStatement("UPDATE ...")<br />
dbConn.executeAsync(stmt, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
It is also possible to execute multiple async statements at once using database connection executeAsync:<br />
<pre><br />
let statements = [];<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
dbConn.executeAsync(statements, statements.length, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
== Performance hints ==<br />
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.<br />
* 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.<br />
* Test queries against a big database. That can make a lot of difference.<br />
* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.<br />
* Use subqueries when you don't really need a complete JOIN, but only check for existence of a record, or get at least one record of a certain type.<br />
* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).<br />
* Virtual machines have slower disk access, so take take into account when testing write performance in a VM.<br />
* Use transactions every time you need to do multiple writes.<br />
* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example, the following query will use the 'url' index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre></div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Places/Places_SQL_queries_best_practices&diff=1243428Places/Places SQL queries best practices2022-07-20T21:17:55Z<p>PeterMortensen: /* Querying Places database examples */ Copy edited (e.g. ref. <en.wikipedia.org/wiki/C%2B%2B> and <en.wikipedia.org/wiki/JavaScript>). "information" in an uncountable noun in this context. Expanded.</p>
<hr />
<div>= Places SQL queries best practices =<br />
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].<br />
<br />
For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]<br />
<br />
== Table aliases ==<br />
When querying Places tables and in need of aliasing a table for joins or complex queries, it is always better to use the same aliases in all queries.<br />
<br />
This is needed not only for our internal code style, but also to avoid common performance killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s. Indeed referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.<br />
For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.<br />
<br />
<center><br />
{| border="1" cellpadding="2"<br />
|+Commonly used table aliases<br />
! Table || Alias || On-conflict alias<br />
|-<br />
| moz_anno_attributes || n || <br />
|-<br />
| moz_annos || a ||<br />
|-<br />
| moz_bookmarks || b ||<br />
|- <br />
| moz_bookmarks_roots || r || <br />
|-<br />
| moz_favicons || f || <br />
|-<br />
| moz_historyvisits || v || <br />
|-<br />
| moz_historyvisits_temp || v || v_t <br />
|-<br />
| moz_inputhistory || i || <br />
|-<br />
| moz_items_annos || a || t<br />
|-<br />
| moz_keywords || k ||<br />
|-<br />
| moz_places || h || p<br />
|-<br />
| moz_places_temp || h || h_t<br />
|}<br />
</center><br />
<br />
When joining a table with itself, it could make sense to define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":<br />
<pre><br />
SELECT b.id FROM moz_bookmarks b<br />
JOIN moz_bookmarks parents ON parents.id = b.parent<br />
WHERE parents.title = 'folder'<br />
</pre><br />
<br />
The code style for using aliases is:<br />
* if the alias is in a real (temporary) table from places.sqlite, use <i>TABLE_NAME ALIAS</i><br />
* if the alias is in a subquery, use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i><br />
* when referring to a column of a table having an alias, always specify the alias, even if there isn't any risk of column names conflicts.<br />
<br />
== Querying the database in the temp tables era ==<br />
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}}).<br />
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.<br />
<br />
NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.<br />
<br />
<b>WARNING: don't use any Places service on quit-application or later, since on quit-application we will close the database connection and data could be lost. Use quit-application-granted instead.</b><br />
<br />
The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.<br />
Since SQLite as of now (3.6.10) does not support indices on views, we have to follow two different paths based on the type of query we are executing:<br />
<br />
* <i>INSERT</i>s/<i>UPDATE</i>s/<i>DELETE</i>s must always act on the views<br />
** Real tables have triggers attached to them to allow for syncing so in no case an implementer should try to execute one of these operations on the temp tables.<br />
** Views have triggers attached to them that ensure data are written/moved to memory tables when needed.<br />
* <i>SELECT</i> must always act on real tables, since due to the lack of indices querying the view would be slow. We can <i>SELECT</i> from the views in tests though, when we are sure perf is not an issue.<br />
<br />
So this is correct:<br />
<pre><br />
DELETE FROM moz_places_view<br />
WHERE id = 1337<br />
</pre><br />
While this is completely <strong>WRONG</strong>:<br />
<pre><br />
DELETE FROM moz_places_temp<br />
WHERE id = 1337<br />
</pre><br />
<br />
When using <i>SELECT</i> we must always be sure to query both tables, new changed data will be in the temp table, while the disk table will be synced in background or when a new bookmarks is added (to prevent bookmarks corruption). Still some place/visit won't never be synced to disk, for example visits with a <i>visit_type = TRANSITION_EMBED</i>.<br />
<br />
To query both tables we can use a special SQL construct:<br />
* UNION merges the resultsets of 2 queries discarding duplicates<br />
* UNION ALL merges the resultsets of 2 queries<br />
<br />
We use one of these based on the query, usually <i>UNION</i> is slower than <i>UNION ALL</i>, so the actual way we query both tables for a place with a certain url may be:<br />
<pre><br />
SELECT id FROM moz_places_temp<br />
WHERE url = 'http://www.mozilla.org'<br />
UNION ALL<br />
SELECT id FROM moz_places<br />
WHERE url = 'http://www.mozilla.org'<br />
AND id NOT IN(SELECT id FROM moz_places_temp)<br />
</pre><br />
<br />
Due to the fact tables are partitioned, we cannot get the last inserted id from one of those, instead we should get the max between both tables, to do that we may, for example, execute:<br />
<pre><br />
SELECT MAX(<br />
(SELECT MAX(id) FROM moz_places_temp),<br />
(SELECT MAX(id) FROM moz_places)<br />
)<br />
</pre><br />
<br />
A similar trick can be applied to <i>IFNULL</i>, this, in many complex queries, can help removing an expensive JOIN, to only get a single value:<br />
<pre><br />
SELECT IFNULL(<br />
(SELECT id FROM moz_places_temp WHERE url = 'http://www.mozilla.org'),<br />
(SELECT id FROM moz_places WHERE url = 'http://www.mozilla.org')<br />
) AS id<br />
</pre><br />
<br />
Notice SQLite does support only one definition of <i>GROUP BY</i>, <i>ORDER BY</i>, <i>LIMIT</i> for <i>UNION</i>s, but sometimes we don't need to <i>UNION</i> all results from both queries. In this case we can use a small trick:<br />
<pre><br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits_temp<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
UNION ALL<br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
ORDER BY 2 DESC<br />
LIMIT 10<br />
</pre><br />
<br />
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).<br />
<br />
== Querying multiple tables ==<br />
When querying multiple tables involving foreign keys, please always avoid Cartesian products like:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h, moz_bookmarks b<br />
WHERE h.id = b.fk<br />
</pre><br />
Instead use a <i>JOIN</i>:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h<br />
JOIN moz_bookmarks b ON h.id = b.fk<br />
</pre><br />
That ensures the query is easier to expand, is less error-prone for future developers and more performance friendly.<br />
We actually use two types of <i>JOIN</i>s:<br />
* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.<br />
* <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.<br />
<br />
== Querying Places database examples ==<br />
This section contains an example of how to create a statement to query the Places database. This is for quick reference, and further information is available in the [https://developer.mozilla.org/en/Storage mozStorage developers documentation].<br />
<br />
C++ code:<br />
<pre><br />
nsCOMPtr<mozIStorageStatement> statement;<br />
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(<br />
"INSERT INTO moz_bookmarks "<br />
"(type, parent, position, dateAdded) VALUES (?1, ?2, ?3, ?4)"),<br />
getter_AddRefs(statement));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->BindInt64Parameter(0, TYPE_SEPARATOR);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(1, aParent);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt32Parameter(2, index);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(3, PR_Now()); <br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->Execute();<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
JavaScript code:<br />
<pre><br />
var stmt = createStatement(<br />
"INSERT INTO moz_bookmarks " +<br />
"(id, fk) " +<br />
"VALUES (NULL, :place_id)");<br />
stmt.params.place_id = 1337.<br />
try {<br />
stmt.execute();<br />
} catch (e) {<br />
dump("Statement error: " + e.message);<br />
} finally {<br />
stmt.finalize();<br />
}<br />
</pre><br />
<br />
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 choose 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.<br />
<br />
When querying with a precompiled statement it is important to use a mozStorageStatementScoper. It will ensure the statement is correctly reset on scope exiting:<br />
<pre><br />
{ // init of scope<br />
mozStorageStatementScoper scoper(mDBGetRedirectDestinations);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(0, aCurrentSource);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(1, aMinTime);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
PRBool hasMore;<br />
while (NS_SUCCEEDED(mDBGetRedirectDestinations->ExecuteStep(&hasMore)) &&<br />
hasMore) {<br />
// do something<br />
}<br />
} // end of scope<br />
// precompiled statement gets reset here<br />
</pre><br />
<br />
<br />
In JavaScript instead, remember to use statement.reset() when you need to reuse a statement changing parameters, and statement.finalize() when you won't need it anymore.<br />
<pre><br />
var stmt = createStatement(<br />
"SELECT b.id FROM moz_bookmarks b " +<br />
"JOIN moz_places h ON h.id = b.fk " +<br />
"WHERE h.url = :url");<br />
stmt.params.url = "http://www.mozilla.org/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.reset();<br />
stmt.params.url = "http://www.mozilla.com/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.finalize();<br />
</pre><br />
<br />
== Asynchronous statements ==<br />
Asynchronous statements are executed by mozStorage in a separate thread, and they are managed as a FIFO queue. Thanks to the separate threads using such a statement is a good idea, since it won't lock the UI. But at the same time we can't rely on immediate database values, since we have to wait for it to be executed (see [https://developer.mozilla.org/En/MozIStorageStatementCallback mozIStorageStatementCallback])<br />
<br />
To execute a single async statement in Cpp:<br />
<pre><br />
nsCOMPtr<YourStatementCallback> callback =<br />
new YourStatementCallback();<br />
// this is needed to cancel a pending statement, we don't use it actually<br />
nsCOMPtr<mozIStoragePendingStatement> canceler;<br />
rv = stmt->ExecuteAsync(callback, getter_AddRefs(canceler));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
while in JS:<br />
<pre><br />
var stmt = dbConn.createStatement("UPDATE ...")<br />
dbConn.executeAsync(stmt, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
It is also possible to execute multiple async statements at once using database connection executeAsync:<br />
<pre><br />
let statements = [];<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
dbConn.executeAsync(statements, statements.length, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
== Perf hints ==<br />
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.<br />
* 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.<br />
* Test queries against a big database, that can make a lot of difference.<br />
* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.<br />
* Use subqueries when you don't really need a complete JOIN but only check for existance of a record, or get at least one record of a certain type.<br />
* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).<br />
* Virtual machines have slower disk access, so take that in count when testing write performances in a vm.<br />
* Use transactions every time you need to do multiple writes.<br />
* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example the following query will use the url index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre></div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Places/Places_SQL_queries_best_practices&diff=1243427Places/Places SQL queries best practices2022-07-20T21:10:03Z<p>PeterMortensen: /* Querying multiple tables */ Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/Cartesian_product#Noun>). Expanded.</p>
<hr />
<div>= Places SQL queries best practices =<br />
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].<br />
<br />
For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]<br />
<br />
== Table aliases ==<br />
When querying Places tables and in need of aliasing a table for joins or complex queries, it is always better to use the same aliases in all queries.<br />
<br />
This is needed not only for our internal code style, but also to avoid common performance killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s. Indeed referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.<br />
For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.<br />
<br />
<center><br />
{| border="1" cellpadding="2"<br />
|+Commonly used table aliases<br />
! Table || Alias || On-conflict alias<br />
|-<br />
| moz_anno_attributes || n || <br />
|-<br />
| moz_annos || a ||<br />
|-<br />
| moz_bookmarks || b ||<br />
|- <br />
| moz_bookmarks_roots || r || <br />
|-<br />
| moz_favicons || f || <br />
|-<br />
| moz_historyvisits || v || <br />
|-<br />
| moz_historyvisits_temp || v || v_t <br />
|-<br />
| moz_inputhistory || i || <br />
|-<br />
| moz_items_annos || a || t<br />
|-<br />
| moz_keywords || k ||<br />
|-<br />
| moz_places || h || p<br />
|-<br />
| moz_places_temp || h || h_t<br />
|}<br />
</center><br />
<br />
When joining a table with itself, it could make sense to define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":<br />
<pre><br />
SELECT b.id FROM moz_bookmarks b<br />
JOIN moz_bookmarks parents ON parents.id = b.parent<br />
WHERE parents.title = 'folder'<br />
</pre><br />
<br />
The code style for using aliases is:<br />
* if the alias is in a real (temporary) table from places.sqlite, use <i>TABLE_NAME ALIAS</i><br />
* if the alias is in a subquery, use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i><br />
* when referring to a column of a table having an alias, always specify the alias, even if there isn't any risk of column names conflicts.<br />
<br />
== Querying the database in the temp tables era ==<br />
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}}).<br />
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.<br />
<br />
NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.<br />
<br />
<b>WARNING: don't use any Places service on quit-application or later, since on quit-application we will close the database connection and data could be lost. Use quit-application-granted instead.</b><br />
<br />
The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.<br />
Since SQLite as of now (3.6.10) does not support indices on views, we have to follow two different paths based on the type of query we are executing:<br />
<br />
* <i>INSERT</i>s/<i>UPDATE</i>s/<i>DELETE</i>s must always act on the views<br />
** Real tables have triggers attached to them to allow for syncing so in no case an implementer should try to execute one of these operations on the temp tables.<br />
** Views have triggers attached to them that ensure data are written/moved to memory tables when needed.<br />
* <i>SELECT</i> must always act on real tables, since due to the lack of indices querying the view would be slow. We can <i>SELECT</i> from the views in tests though, when we are sure perf is not an issue.<br />
<br />
So this is correct:<br />
<pre><br />
DELETE FROM moz_places_view<br />
WHERE id = 1337<br />
</pre><br />
While this is completely <strong>WRONG</strong>:<br />
<pre><br />
DELETE FROM moz_places_temp<br />
WHERE id = 1337<br />
</pre><br />
<br />
When using <i>SELECT</i> we must always be sure to query both tables, new changed data will be in the temp table, while the disk table will be synced in background or when a new bookmarks is added (to prevent bookmarks corruption). Still some place/visit won't never be synced to disk, for example visits with a <i>visit_type = TRANSITION_EMBED</i>.<br />
<br />
To query both tables we can use a special SQL construct:<br />
* UNION merges the resultsets of 2 queries discarding duplicates<br />
* UNION ALL merges the resultsets of 2 queries<br />
<br />
We use one of these based on the query, usually <i>UNION</i> is slower than <i>UNION ALL</i>, so the actual way we query both tables for a place with a certain url may be:<br />
<pre><br />
SELECT id FROM moz_places_temp<br />
WHERE url = 'http://www.mozilla.org'<br />
UNION ALL<br />
SELECT id FROM moz_places<br />
WHERE url = 'http://www.mozilla.org'<br />
AND id NOT IN(SELECT id FROM moz_places_temp)<br />
</pre><br />
<br />
Due to the fact tables are partitioned, we cannot get the last inserted id from one of those, instead we should get the max between both tables, to do that we may, for example, execute:<br />
<pre><br />
SELECT MAX(<br />
(SELECT MAX(id) FROM moz_places_temp),<br />
(SELECT MAX(id) FROM moz_places)<br />
)<br />
</pre><br />
<br />
A similar trick can be applied to <i>IFNULL</i>, this, in many complex queries, can help removing an expensive JOIN, to only get a single value:<br />
<pre><br />
SELECT IFNULL(<br />
(SELECT id FROM moz_places_temp WHERE url = 'http://www.mozilla.org'),<br />
(SELECT id FROM moz_places WHERE url = 'http://www.mozilla.org')<br />
) AS id<br />
</pre><br />
<br />
Notice SQLite does support only one definition of <i>GROUP BY</i>, <i>ORDER BY</i>, <i>LIMIT</i> for <i>UNION</i>s, but sometimes we don't need to <i>UNION</i> all results from both queries. In this case we can use a small trick:<br />
<pre><br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits_temp<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
UNION ALL<br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
ORDER BY 2 DESC<br />
LIMIT 10<br />
</pre><br />
<br />
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).<br />
<br />
== Querying multiple tables ==<br />
When querying multiple tables involving foreign keys, please always avoid Cartesian products like:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h, moz_bookmarks b<br />
WHERE h.id = b.fk<br />
</pre><br />
Instead use a <i>JOIN</i>:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h<br />
JOIN moz_bookmarks b ON h.id = b.fk<br />
</pre><br />
That ensures the query is easier to expand, is less error-prone for future developers and more performance friendly.<br />
We actually use two types of <i>JOIN</i>s:<br />
* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.<br />
* <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.<br />
<br />
== Querying Places database examples ==<br />
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]<br />
<br />
Cpp code:<br />
<pre><br />
nsCOMPtr<mozIStorageStatement> statement;<br />
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(<br />
"INSERT INTO moz_bookmarks "<br />
"(type, parent, position, dateAdded) VALUES (?1, ?2, ?3, ?4)"),<br />
getter_AddRefs(statement));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->BindInt64Parameter(0, TYPE_SEPARATOR);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(1, aParent);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt32Parameter(2, index);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(3, PR_Now()); <br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->Execute();<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
JS code:<br />
<pre><br />
var stmt = createStatement(<br />
"INSERT INTO moz_bookmarks " +<br />
"(id, fk) " +<br />
"VALUES (NULL, :place_id)");<br />
stmt.params.place_id = 1337.<br />
try {<br />
stmt.execute();<br />
} catch (e) {<br />
dump("Statement error: " + e.message);<br />
} finally {<br />
stmt.finalize();<br />
}<br />
</pre><br />
<br />
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.<br />
<br />
When querying with a precompiled statement it is important to use a mozStorageStatementScoper, it will ensure the statement is correctly reset on scope exiting:<br />
<pre><br />
{ // init of scope<br />
mozStorageStatementScoper scoper(mDBGetRedirectDestinations);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(0, aCurrentSource);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(1, aMinTime);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
PRBool hasMore;<br />
while (NS_SUCCEEDED(mDBGetRedirectDestinations->ExecuteStep(&hasMore)) &&<br />
hasMore) {<br />
// do something<br />
}<br />
} // end of scope<br />
// precompiled statement gets resetted here<br />
</pre><br />
<br />
<br />
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.<br />
<pre><br />
var stmt = createStatement(<br />
"SELECT b.id FROM moz_bookmarks b " +<br />
"JOIN moz_places h ON h.id = b.fk " +<br />
"WHERE h.url = :url");<br />
stmt.params.url = "http://www.mozilla.org/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.reset();<br />
stmt.params.url = "http://www.mozilla.com/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.finalize();<br />
</pre><br />
<br />
== Asynchronous statements ==<br />
Asynchronous statements are executed by mozStorage in a separate thread, and they are managed as a FIFO queue. Thanks to the separate threads using such a statement is a good idea, since it won't lock the UI. But at the same time we can't rely on immediate database values, since we have to wait for it to be executed (see [https://developer.mozilla.org/En/MozIStorageStatementCallback mozIStorageStatementCallback])<br />
<br />
To execute a single async statement in Cpp:<br />
<pre><br />
nsCOMPtr<YourStatementCallback> callback =<br />
new YourStatementCallback();<br />
// this is needed to cancel a pending statement, we don't use it actually<br />
nsCOMPtr<mozIStoragePendingStatement> canceler;<br />
rv = stmt->ExecuteAsync(callback, getter_AddRefs(canceler));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
while in JS:<br />
<pre><br />
var stmt = dbConn.createStatement("UPDATE ...")<br />
dbConn.executeAsync(stmt, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
It is also possible to execute multiple async statements at once using database connection executeAsync:<br />
<pre><br />
let statements = [];<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
dbConn.executeAsync(statements, statements.length, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
== Perf hints ==<br />
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.<br />
* 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.<br />
* Test queries against a big database, that can make a lot of difference.<br />
* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.<br />
* Use subqueries when you don't really need a complete JOIN but only check for existance of a record, or get at least one record of a certain type.<br />
* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).<br />
* Virtual machines have slower disk access, so take that in count when testing write performances in a vm.<br />
* Use transactions every time you need to do multiple writes.<br />
* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example the following query will use the url index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre></div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Places/Places_SQL_queries_best_practices&diff=1243426Places/Places SQL queries best practices2022-07-20T21:07:35Z<p>PeterMortensen: /* Table aliases */ Copy edited (e.g. ref. <https://en.wiktionary.org/wiki/complex#Adjective> and <https://en.wiktionary.org/wiki/conflict#Noun>). Added the missing words, e.g., subjects. Expanded.</p>
<hr />
<div>= Places SQL queries best practices =<br />
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].<br />
<br />
For a better comprehension please look at the [https://wiki.mozilla.org/Places#Quick_Links Database schema ERD]<br />
<br />
== Table aliases ==<br />
When querying Places tables and in need of aliasing a table for joins or complex queries, it is always better to use the same aliases in all queries.<br />
<br />
This is needed not only for our internal code style, but also to avoid common performance killer paths in complex <i>DELETE</i>s or <i>UPDATE</i>s. Indeed referring to <i>table_name.column_name</i> in such a query could be different than referring to h.id, causing a full lookup of the table.<br />
For the above reasons, we always try to avoid the <i>table_name.column_name</i> path.<br />
<br />
<center><br />
{| border="1" cellpadding="2"<br />
|+Commonly used table aliases<br />
! Table || Alias || On-conflict alias<br />
|-<br />
| moz_anno_attributes || n || <br />
|-<br />
| moz_annos || a ||<br />
|-<br />
| moz_bookmarks || b ||<br />
|- <br />
| moz_bookmarks_roots || r || <br />
|-<br />
| moz_favicons || f || <br />
|-<br />
| moz_historyvisits || v || <br />
|-<br />
| moz_historyvisits_temp || v || v_t <br />
|-<br />
| moz_inputhistory || i || <br />
|-<br />
| moz_items_annos || a || t<br />
|-<br />
| moz_keywords || k ||<br />
|-<br />
| moz_places || h || p<br />
|-<br />
| moz_places_temp || h || h_t<br />
|}<br />
</center><br />
<br />
When joining a table with itself, it could make sense to define an alias based on the <i>JOIN</i> meaning, for example selecting all bookmarks with a parent called "folder":<br />
<pre><br />
SELECT b.id FROM moz_bookmarks b<br />
JOIN moz_bookmarks parents ON parents.id = b.parent<br />
WHERE parents.title = 'folder'<br />
</pre><br />
<br />
The code style for using aliases is:<br />
* if the alias is in a real (temporary) table from places.sqlite, use <i>TABLE_NAME ALIAS</i><br />
* if the alias is in a subquery, use <i>AS</i> construct, like <i>SUBQUERY AS ALIAS</i><br />
* when referring to a column of a table having an alias, always specify the alias, even if there isn't any risk of column names conflicts.<br />
<br />
== Querying the database in the temp tables era ==<br />
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}}).<br />
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.<br />
<br />
NOTE: while for disk tables you can query <i>sqlite_master</i> table to get existant tables, indexes and triggers, for temporary tables you should instead use <i>sqlite_temp_master</i>. Notice mozStorage functions indexExists and tableExists are not correct atm ({{bug|472963}}), so if you are looking for existance of moz_places_temp, they will return false even if the table exists.<br />
<br />
<b>WARNING: don't use any Places service on quit-application or later, since on quit-application we will close the database connection and data could be lost. Use quit-application-granted instead.</b><br />
<br />
The tables are abstracted through a view, actual views are <i>moz_places_view</i> and <i>moz_historyvists_view</i>.<br />
Since SQLite as of now (3.6.10) does not support indices on views, we have to follow two different paths based on the type of query we are executing:<br />
<br />
* <i>INSERT</i>s/<i>UPDATE</i>s/<i>DELETE</i>s must always act on the views<br />
** Real tables have triggers attached to them to allow for syncing so in no case an implementer should try to execute one of these operations on the temp tables.<br />
** Views have triggers attached to them that ensure data are written/moved to memory tables when needed.<br />
* <i>SELECT</i> must always act on real tables, since due to the lack of indices querying the view would be slow. We can <i>SELECT</i> from the views in tests though, when we are sure perf is not an issue.<br />
<br />
So this is correct:<br />
<pre><br />
DELETE FROM moz_places_view<br />
WHERE id = 1337<br />
</pre><br />
While this is completely <strong>WRONG</strong>:<br />
<pre><br />
DELETE FROM moz_places_temp<br />
WHERE id = 1337<br />
</pre><br />
<br />
When using <i>SELECT</i> we must always be sure to query both tables, new changed data will be in the temp table, while the disk table will be synced in background or when a new bookmarks is added (to prevent bookmarks corruption). Still some place/visit won't never be synced to disk, for example visits with a <i>visit_type = TRANSITION_EMBED</i>.<br />
<br />
To query both tables we can use a special SQL construct:<br />
* UNION merges the resultsets of 2 queries discarding duplicates<br />
* UNION ALL merges the resultsets of 2 queries<br />
<br />
We use one of these based on the query, usually <i>UNION</i> is slower than <i>UNION ALL</i>, so the actual way we query both tables for a place with a certain url may be:<br />
<pre><br />
SELECT id FROM moz_places_temp<br />
WHERE url = 'http://www.mozilla.org'<br />
UNION ALL<br />
SELECT id FROM moz_places<br />
WHERE url = 'http://www.mozilla.org'<br />
AND id NOT IN(SELECT id FROM moz_places_temp)<br />
</pre><br />
<br />
Due to the fact tables are partitioned, we cannot get the last inserted id from one of those, instead we should get the max between both tables, to do that we may, for example, execute:<br />
<pre><br />
SELECT MAX(<br />
(SELECT MAX(id) FROM moz_places_temp),<br />
(SELECT MAX(id) FROM moz_places)<br />
)<br />
</pre><br />
<br />
A similar trick can be applied to <i>IFNULL</i>, this, in many complex queries, can help removing an expensive JOIN, to only get a single value:<br />
<pre><br />
SELECT IFNULL(<br />
(SELECT id FROM moz_places_temp WHERE url = 'http://www.mozilla.org'),<br />
(SELECT id FROM moz_places WHERE url = 'http://www.mozilla.org')<br />
) AS id<br />
</pre><br />
<br />
Notice SQLite does support only one definition of <i>GROUP BY</i>, <i>ORDER BY</i>, <i>LIMIT</i> for <i>UNION</i>s, but sometimes we don't need to <i>UNION</i> all results from both queries. In this case we can use a small trick:<br />
<pre><br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits_temp<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
UNION ALL<br />
SELECT * FROM (<br />
SELECT id, visit_date FROM moz_historyvisits<br />
WHERE visit_type = 1<br />
ORDER BY visit_date DESC<br />
LIMIT 10<br />
)<br />
ORDER BY 2 DESC<br />
LIMIT 10<br />
</pre><br />
<br />
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).<br />
<br />
== Querying multiple tables ==<br />
When querying multiple tables involving foreign keys, please always avoid cartesian products like:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h, moz_bookmarks b<br />
WHERE h.id = b.fk<br />
</pre><br />
Instead use a <i>JOIN</i>:<br />
<pre><br />
SELECT b.id, h.url<br />
FROM moz_places h<br />
JOIN moz_bookmarks b ON h.id = b.fk<br />
</pre><br />
That ensures the query is easier to expand, is less error-prone for future developers and more perf-friendly.<br />
We actually use 2 types of <i>JOIN</i>s:<br />
* <i>LEFT JOIN</i> is used when the right table values could not exist, but we still want a record containing the left table values.<br />
* <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.<br />
<br />
== Querying Places database examples ==<br />
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]<br />
<br />
Cpp code:<br />
<pre><br />
nsCOMPtr<mozIStorageStatement> statement;<br />
rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(<br />
"INSERT INTO moz_bookmarks "<br />
"(type, parent, position, dateAdded) VALUES (?1, ?2, ?3, ?4)"),<br />
getter_AddRefs(statement));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->BindInt64Parameter(0, TYPE_SEPARATOR);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(1, aParent);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt32Parameter(2, index);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = statement->BindInt64Parameter(3, PR_Now()); <br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
rv = statement->Execute();<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
JS code:<br />
<pre><br />
var stmt = createStatement(<br />
"INSERT INTO moz_bookmarks " +<br />
"(id, fk) " +<br />
"VALUES (NULL, :place_id)");<br />
stmt.params.place_id = 1337.<br />
try {<br />
stmt.execute();<br />
} catch (e) {<br />
dump("Statement error: " + e.message);<br />
} finally {<br />
stmt.finalize();<br />
}<br />
</pre><br />
<br />
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.<br />
<br />
When querying with a precompiled statement it is important to use a mozStorageStatementScoper, it will ensure the statement is correctly reset on scope exiting:<br />
<pre><br />
{ // init of scope<br />
mozStorageStatementScoper scoper(mDBGetRedirectDestinations);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(0, aCurrentSource);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
rv = mDBGetRedirectDestinations->BindInt64Parameter(1, aMinTime);<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
<br />
PRBool hasMore;<br />
while (NS_SUCCEEDED(mDBGetRedirectDestinations->ExecuteStep(&hasMore)) &&<br />
hasMore) {<br />
// do something<br />
}<br />
} // end of scope<br />
// precompiled statement gets resetted here<br />
</pre><br />
<br />
<br />
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.<br />
<pre><br />
var stmt = createStatement(<br />
"SELECT b.id FROM moz_bookmarks b " +<br />
"JOIN moz_places h ON h.id = b.fk " +<br />
"WHERE h.url = :url");<br />
stmt.params.url = "http://www.mozilla.org/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.reset();<br />
stmt.params.url = "http://www.mozilla.com/";<br />
while(stmt.step()) {<br />
dump(stmt.row.id);<br />
}<br />
stmt.finalize();<br />
</pre><br />
<br />
== Asynchronous statements ==<br />
Asynchronous statements are executed by mozStorage in a separate thread, and they are managed as a FIFO queue. Thanks to the separate threads using such a statement is a good idea, since it won't lock the UI. But at the same time we can't rely on immediate database values, since we have to wait for it to be executed (see [https://developer.mozilla.org/En/MozIStorageStatementCallback mozIStorageStatementCallback])<br />
<br />
To execute a single async statement in Cpp:<br />
<pre><br />
nsCOMPtr<YourStatementCallback> callback =<br />
new YourStatementCallback();<br />
// this is needed to cancel a pending statement, we don't use it actually<br />
nsCOMPtr<mozIStoragePendingStatement> canceler;<br />
rv = stmt->ExecuteAsync(callback, getter_AddRefs(canceler));<br />
NS_ENSURE_SUCCESS(rv, rv);<br />
</pre><br />
<br />
while in JS:<br />
<pre><br />
var stmt = dbConn.createStatement("UPDATE ...")<br />
dbConn.executeAsync(stmt, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
It is also possible to execute multiple async statements at once using database connection executeAsync:<br />
<pre><br />
let statements = [];<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
statememnts.push(dbConn.createStatement("UPDATE ..."));<br />
dbConn.executeAsync(statements, statements.length, mozIStorageStatementCallback_obj);<br />
</pre><br />
<br />
== Perf hints ==<br />
* Use SQLite command <i>EXPLAIN QUERY PLAN statement</i> to check what tables and indices you're using, and try to optimize it.<br />
* 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.<br />
* Test queries against a big database, that can make a lot of difference.<br />
* Use <i>LIMIT</i> every time is possible, especially with partitioned tables, since when working without indices, having small intermediate tables is really important.<br />
* Use subqueries when you don't really need a complete JOIN but only check for existance of a record, or get at least one record of a certain type.<br />
* When a query is too slow, try rebuilding it to do the opposite and exclude results (<i>IN</i>, <i>NOT IN</i> constructs can be useful).<br />
* Virtual machines have slower disk access, so take that in count when testing write performances in a vm.<br />
* Use transactions every time you need to do multiple writes.<br />
* Since each query can only use an index, the optimizer has to make a choice when the same query can be executed using two different indices. You can avoid using a certain index, and force the optimizer to use the another one, prepending the column name with a +. For example the following query will use the url index.<pre>SELECT id, url FROM moz_places WHERE +id = 5 AND url = 'http://www.mozilla.org/'</pre></div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places/Places_SQL_queries_best_practices&diff=1243425Talk:Places/Places SQL queries best practices2022-07-20T20:59:08Z<p>PeterMortensen: /* Broken link */ new section</p>
<hr />
<div>== Broken link ==<br />
<br />
The link ''"Database schema ERD"'' is (effectively) broken. The direct link isn't broken, but the link of the same name at the destination page '''''is''''' broken.<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:59, 20 July 2022 (UTC)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places&diff=1243424Talk:Places2022-07-20T20:53:47Z<p>PeterMortensen: /* More broken links */ Added another one.</p>
<hr />
<div><br />
== Breaking bookmarks ==<br />
<br />
Best not break my bookmarks boy.<br />
<br />
<br />
== Store & synch bookmarks remotely ==<br />
<br />
This may be more of an extension feature than a core feature, but there isn't an extension out there that does it as well as it could be done.<br />
<br />
It would be fantastic to have a feature that allows you to store & synch your bookmarks at a remote location in a format that can be read in a web browser. I regularly use 3 different computer for work and pop into Internet cafes while traveling, and never seem to have the bookmark I need in the right place at the right time.<br />
<br />
The one extension I've found that comes close to this is Bookmarks Synchronizer that stores your bookmarks in an XML format. But it has a critical flaw that can cause you to lose your existing bookmarks and appears to have been abandonded after version 1.0.1.<br />
<br />
: Two points to make here.<br />
:<br />
: First, I agree with the post above. I sometimes work from home and if I find something worth bookmarking, I usually have to send myself the link at work (or viceversa). Having an online repository would be great.<br />
:<br />
: I'm afraid it may be late for the second point as you guys already are in the middle of implementation. However, I read an article by UI guru Jakob Nielsen years ago that was focused on Bookmarks, History. What I found is not what I had in mind but it mentions the same concepts. http://www.useit.com/alertbox/9711a.html<br />
<br />
== Integrate Firefox with the Windows "Favorites" system ==<br />
<br />
I'm going to propose something very radical, but please give it serious thought. Consider natively integrating Firefox with the Windows 98/Windows 2000/Windows XP "Favorites" system. I realise most people consider this to be an "evil IE feature", but all versions of Windows in the past 6+ years have included Favorites as a native, OS-level feature. Favorites are included in everything from the start menu to the "main menu" of explorer folder windows. Yes, it's part of the "Integrate IE with the OS curse", but I believe it falls within the Firefox philosophy - simple, standardize, OS-friendly user interfaces.<br />
<br />
My proposal: If a user loads Firefox on a Windows platform that supports favorites natively at the OS level, Firefox uses those favorites - either directly, or through some sort of "transparent caching". If the favorites are cached so that Firefox can optimize them, the user should not be aware of that - they should look and feel the same as Favorites, for the most part (ie/ no additional IE, no syncronization dialogs). <br />
<br />
An option/preference would allow the user to select which bookmark system to use (ie/ where new bookmarks go). "meta data" about the "favorite" (.url) bookmarks could be cached in firefox's bookmark file or another file somehow. <br />
<br />
I realize this is likely to be stomped out very quickly - but consider that "Favorites" are an operating system feature - supporting favorites is no different than supporting the native Windows "File Save Dialog" or rendering scrollbars with Windows XP Themes. In addition, it provides a very easy, gradual upgrade path for Internet Explorer users. They can easily "dabble" in Firefox until they are ready to take the full plunge. I can honestly say that even as a technical user, I found the lack of automatic/transparent bookmark syncronization a severe frustration when I first started using Firefox. <br />
<br />
I'm not suggesting the Firefox team sign a deal with the devil - but perhaps it would allow users to switch to Firefox with much less pain. :-) Nothing says that the favorites and the firefox bookmark file cannot be "merged" at display time.<br />
<br />
== Replacing local bookmarks with remote ==<br />
<br />
I'd find it really useful if you could actually provide a way for extensions to override native bookmarking functionality and use an online provider -- like del.icio.us, for example. I stopped using local bookmarks a long time ago for anything I wanted to remember in the future, so "Places" functionality will be completely lost on me unless there's a way to get external services in there somewhere.<br />
<br />
== Local/Absolute dual bookmarks ==<br />
<br />
I've long wanted a way to bookmark news articles that I have stored on my hard drive along with the URL. I frequently cite URLs as sources and it'd be nice to be able to quickly check the link, then source the file from my hard drive if the URL is dead. The first implementation that comes to mind is an icon to the right of the link text that summons the local file.<br />
<br />
Other suggestions:<br />
When using the navigation toolbar, checking a bookmark's properties or sorting a folder by name (inter alia) causes the tree to close/collapse/whatever. This is annoying.<br />
<br />
It would be great if we could shift-click and control-click to select bookmarks/folders for export from the bookmark manager.<br />
<br />
It would be great if we could export bookmarks and optionally have the result look and behave as the links in the navbar and/or bookmark manager do (i.e., preserve file structure).<br />
<br />
When copying/pasting bookmarks, it would be great if we had an option to allow the title(s) AND the URL(s) to be copied/pasted.<br />
<br />
It'd be great if we could automate a periodic check for dead URLs, with a nice detailed report, but I'm guessing this is the sort of thing tons of people have suggested.<br />
<br />
When saving pages maybe a way to make an article's publication date the file's creation date? I know this is outside the scope here...<br />
<br />
I'm sure I could come up with more given time. I'm very glad to hear that bookmarks are going to finally get some attention.<br />
<br />
== Tagging: different than bookmarking ==<br />
<br />
Recent thinking on the UI (as discussed in the newsgroups) has led us to shift from our initial thinking that tagging should be seen as an alternate way to create bookmarks, with tags simply acting as surrogates for bookmark folders. After looking at some hard questions about what that implied for nested folders, preserving existing bookmark heirarchy structures, and adjusting users' mental models, we began to see things differently.<br />
<br />
Most recently, we've started to see tagging as the ability to do something else: specifically, to be able to mark an entry in your web browsing history as "interesting."<br />
<br />
Here's the rationale: Bookmarks were originally intended to be shortcuts to commonly visited sites. But then the web got bigger, and people started using them as ways to remember interesting places. But then the web got bigger still, and all of a sudden the bookmarking system couldn't cope. Initial thoughts about tagging were: well, let's use tags and search to make it easier to handle large bookmark sets. But that led to some very good questions: search isn't as quick as click-click-click through a drop-down menu or click on a personal toolbar. The key was realizing that the use of bookmarks shifted and merged: a single system was being used to store shortcuts AND sites that were just interesting and that the user thought they might want to get back to. The goal of keeping all of these interesting sites is, well, to keep track of one's personal world wide web. To tag those pages that they thing are interesting and then be able to quickly search not the entire web, but across their already-experienced subset to get back to where they were. And that's why we think it's different.)<br />
<br />
[[User:Beltzner|Beltzner]] 19:35, 25 Feb 2006 (PST)<br />
<br />
Excellent point! I think any redesign of a bookmarking interface in current browsers needs to address exactly this problem. But, may be there is a simple solution: two different purposes require two different interfaces that serve each purpose best. Have a look at this little essay I wrote recently: <br />
http://pubnotes.wordpress.com/2007/10/14/rethinking-bookmarks-ui/<br />
<br />
-- 24 March 2006 --<br />
<br />
This is exactly what I think I would use. Give me a button I can hit when I find a site that is either interesting, or that I know I'll need to get back to soon. No popups. No "Enter tags" or "Rename" dialog or nothing. Just a button that does everything for me, and lites up to let me know its done. Then put a list of 'em (with readable names) in the sidebar or bookmarks menu or history menu or as a dropdown outta the URL bar. I don't really care where it shows up. I just don't wanna have to open a new window for it. That's pretty much all I use bookmarks for. And most of them I'm more than happy to be done with after 2 days usage.<br />
<br />
-- 19:43, 8 March 2006 --<br />
<br />
== Cheaper on bandwidth ==<br />
<br />
I'm glad to see firefox will be heading in this direction. Let me make a suggestion: if need be to implement server side functionality in order to keep cached bookmarks current, just remember that server hosting would benefit from this feature -and most likely jump to the aid of Firefox- since it would reduce the ammount of bandwidth they would waste unnecessarily.<br />
<br />
== Puts Places in a Tab ==<br />
<br />
Does the Places section have to be a window? These clutter the browsing space, and I think it would be much better to have the Places section in a tab itself. Opera does this at the moment with Bookmarks and History (separately). [[User:Sgd|Sgd]] 15:52, 20 Mar 2006 (PST)<br />
<br />
Places used to be in a tab but there were a lot of bugs related to having XUL in the content area of the window. I liked it better in a tab as well, and I hope we can go back to a tab in future releases when the bugs are fixed. ~BrettWilson<br />
<br />
== Sidebar for Bookmarks ==<br />
<br />
Having bookmarks in a sidebar is the fastest access (one click) and can be used as a permanent visual reminder for bookmarks, bookmarklets, and keywords to activate bookmarks (e.g. "dt _ del.icio.us tag search" with the %s hack and "dt" as keyword that I write as a visual reminder at the beginning of the line followed by an underscore to separate it from the text). The menu for bookmarks requires at lest two clicks and is a "dynamic" visual reminder. The sidebar and the menu for bookmarks are perfect interfaces for a small number of frequently visited websites and as toolbox for bookmarklets. So, don't drop it with the new Places Interface. --[[User:Tdot|Tdot]] 20:35, 23 March 2006 (PST)<br />
<br />
== Use Cases ==<br />
<br />
Someone really needs to fill in the use case section. It seems like it would be important when designing a UI. Somebody in the groups suggested this case: "One example is the deletion of a place. ... That used to delete just one instance in one folder for decades, and now you change it, including dataloss." Things that delete data should be done with a dialogue. Something that asks if you want to entirely remove the bookmark(s), or just remove it(them) from that category, and maybe an "Always perform this action" checkbox at the bottom.<br />
<br />
I looked through the UI and didn't see any UI on deleting bookmarks at all, but I apologize if this is a repeat of something. --[[User:Wjjohnst|wjjohnst]] April 13, 2006 3:20(CST)<br />
<br />
== Browse Bookmarks, then Save! ==<br />
<br />
A feature of Konqueror I always loved was that you could drop down any menu from your Bookmarks and click "Add Bookmark Here".<br />
<br />
I use my Bookmarks Toolbar and rarely the Bookmarks menu. It would be great to just click that category I want from the menu, navigate to one of my sub-menus and then click "Add Bookmark Here". I find this method much more speedy than using the "Bookmark This Page" function.<br />
<br />
If I want to organise my bookmarks the Bookmarks Manager will still be there, but when I want to quickly mark a page, I want to quickly mark it.<br />
<br />
Also, there are often situations when I might want to add several pages from a site or sites all in reasonably quick succession. Using the "Add Bookmark Here" function is the slow way of doing things.<br />
<br />
[[User:Bugman|Bugman]] 23:52, 15 May 2006 (ACST)<br />
<br />
== index ALL and pinpoint SOME ==<br />
<br />
Hi all,<br />
<br />
I am extremely surprised that even in Firefox 2 we still have no more than this completely obsolete and unpractical bookmark functionality to go back where we once were (apart from history of course).<br />
<br />
The solution is so simple! Of course the hierarchical organization of <br />
bookmarks is a nonsense and we'd be better off with a keyword/tag approach. <br />
Yet the solution I would retain is even simpler to implement and use: <br />
just put ALL the visited URLs back to a user chosen period of time in a local or remote htdig/glimpse style database, storing the last visits date as well. Also allow the user to add an extra pinpoint to URLs of particular interest to him/her and you're done!<br />
<br />
Indexing everything is important so that the user does not have to worry about not forgetting to bookmark, especially when there are several pages of interest within the same website.<br />
<br />
Well now OK the index might get big if an extensive surfer wants several years of storage. But this should not normally grow beyond the capacity of present hard disks. And remote storage is of course an option.<br />
<br />
Why looking further for awkward and complicated schemes most users will <br />
neglect to learn ? <br />
<br />
A functionally equivalent approach would be to ask Google to add your name, the date and your eventual pinpoint in their database when you visit a site ;-)<br />
<br />
--[[User:Alexandre Oberlin|Alexandre Oberlin]] 12:42, 31 December 2006 (PST)<br />
<br />
== Broken link ==<br />
<br />
The link ''"Database schema ERD"'' in sub section [[Places#Quick Links|Quick Links]] is broken (''"404 Not Found"'').<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:38, 20 July 2022 (UTC)<br />
<br />
== More broken links ==<br />
<br />
Some broken links:<br />
<br />
#''http://developer.mozilla.org/en/docs/Places''. Link text '''''"Places developer wiki"'''''. Two instances.<br />
#''http://www.foxcloud.com/Places/''. Link text '''''"ERD (possibly obsolete)"'''''. Message: ''"Not Found. The requested URL was not found on this server. Additionally, a 404 Not Found error was encountered while trying to use an ErrorDocument to handle the request."''.<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:46, 20 July 2022 (UTC)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places&diff=1243423Talk:Places2022-07-20T20:46:18Z<p>PeterMortensen: /* More broken links */ new section</p>
<hr />
<div><br />
== Breaking bookmarks ==<br />
<br />
Best not break my bookmarks boy.<br />
<br />
<br />
== Store & synch bookmarks remotely ==<br />
<br />
This may be more of an extension feature than a core feature, but there isn't an extension out there that does it as well as it could be done.<br />
<br />
It would be fantastic to have a feature that allows you to store & synch your bookmarks at a remote location in a format that can be read in a web browser. I regularly use 3 different computer for work and pop into Internet cafes while traveling, and never seem to have the bookmark I need in the right place at the right time.<br />
<br />
The one extension I've found that comes close to this is Bookmarks Synchronizer that stores your bookmarks in an XML format. But it has a critical flaw that can cause you to lose your existing bookmarks and appears to have been abandonded after version 1.0.1.<br />
<br />
: Two points to make here.<br />
:<br />
: First, I agree with the post above. I sometimes work from home and if I find something worth bookmarking, I usually have to send myself the link at work (or viceversa). Having an online repository would be great.<br />
:<br />
: I'm afraid it may be late for the second point as you guys already are in the middle of implementation. However, I read an article by UI guru Jakob Nielsen years ago that was focused on Bookmarks, History. What I found is not what I had in mind but it mentions the same concepts. http://www.useit.com/alertbox/9711a.html<br />
<br />
== Integrate Firefox with the Windows "Favorites" system ==<br />
<br />
I'm going to propose something very radical, but please give it serious thought. Consider natively integrating Firefox with the Windows 98/Windows 2000/Windows XP "Favorites" system. I realise most people consider this to be an "evil IE feature", but all versions of Windows in the past 6+ years have included Favorites as a native, OS-level feature. Favorites are included in everything from the start menu to the "main menu" of explorer folder windows. Yes, it's part of the "Integrate IE with the OS curse", but I believe it falls within the Firefox philosophy - simple, standardize, OS-friendly user interfaces.<br />
<br />
My proposal: If a user loads Firefox on a Windows platform that supports favorites natively at the OS level, Firefox uses those favorites - either directly, or through some sort of "transparent caching". If the favorites are cached so that Firefox can optimize them, the user should not be aware of that - they should look and feel the same as Favorites, for the most part (ie/ no additional IE, no syncronization dialogs). <br />
<br />
An option/preference would allow the user to select which bookmark system to use (ie/ where new bookmarks go). "meta data" about the "favorite" (.url) bookmarks could be cached in firefox's bookmark file or another file somehow. <br />
<br />
I realize this is likely to be stomped out very quickly - but consider that "Favorites" are an operating system feature - supporting favorites is no different than supporting the native Windows "File Save Dialog" or rendering scrollbars with Windows XP Themes. In addition, it provides a very easy, gradual upgrade path for Internet Explorer users. They can easily "dabble" in Firefox until they are ready to take the full plunge. I can honestly say that even as a technical user, I found the lack of automatic/transparent bookmark syncronization a severe frustration when I first started using Firefox. <br />
<br />
I'm not suggesting the Firefox team sign a deal with the devil - but perhaps it would allow users to switch to Firefox with much less pain. :-) Nothing says that the favorites and the firefox bookmark file cannot be "merged" at display time.<br />
<br />
== Replacing local bookmarks with remote ==<br />
<br />
I'd find it really useful if you could actually provide a way for extensions to override native bookmarking functionality and use an online provider -- like del.icio.us, for example. I stopped using local bookmarks a long time ago for anything I wanted to remember in the future, so "Places" functionality will be completely lost on me unless there's a way to get external services in there somewhere.<br />
<br />
== Local/Absolute dual bookmarks ==<br />
<br />
I've long wanted a way to bookmark news articles that I have stored on my hard drive along with the URL. I frequently cite URLs as sources and it'd be nice to be able to quickly check the link, then source the file from my hard drive if the URL is dead. The first implementation that comes to mind is an icon to the right of the link text that summons the local file.<br />
<br />
Other suggestions:<br />
When using the navigation toolbar, checking a bookmark's properties or sorting a folder by name (inter alia) causes the tree to close/collapse/whatever. This is annoying.<br />
<br />
It would be great if we could shift-click and control-click to select bookmarks/folders for export from the bookmark manager.<br />
<br />
It would be great if we could export bookmarks and optionally have the result look and behave as the links in the navbar and/or bookmark manager do (i.e., preserve file structure).<br />
<br />
When copying/pasting bookmarks, it would be great if we had an option to allow the title(s) AND the URL(s) to be copied/pasted.<br />
<br />
It'd be great if we could automate a periodic check for dead URLs, with a nice detailed report, but I'm guessing this is the sort of thing tons of people have suggested.<br />
<br />
When saving pages maybe a way to make an article's publication date the file's creation date? I know this is outside the scope here...<br />
<br />
I'm sure I could come up with more given time. I'm very glad to hear that bookmarks are going to finally get some attention.<br />
<br />
== Tagging: different than bookmarking ==<br />
<br />
Recent thinking on the UI (as discussed in the newsgroups) has led us to shift from our initial thinking that tagging should be seen as an alternate way to create bookmarks, with tags simply acting as surrogates for bookmark folders. After looking at some hard questions about what that implied for nested folders, preserving existing bookmark heirarchy structures, and adjusting users' mental models, we began to see things differently.<br />
<br />
Most recently, we've started to see tagging as the ability to do something else: specifically, to be able to mark an entry in your web browsing history as "interesting."<br />
<br />
Here's the rationale: Bookmarks were originally intended to be shortcuts to commonly visited sites. But then the web got bigger, and people started using them as ways to remember interesting places. But then the web got bigger still, and all of a sudden the bookmarking system couldn't cope. Initial thoughts about tagging were: well, let's use tags and search to make it easier to handle large bookmark sets. But that led to some very good questions: search isn't as quick as click-click-click through a drop-down menu or click on a personal toolbar. The key was realizing that the use of bookmarks shifted and merged: a single system was being used to store shortcuts AND sites that were just interesting and that the user thought they might want to get back to. The goal of keeping all of these interesting sites is, well, to keep track of one's personal world wide web. To tag those pages that they thing are interesting and then be able to quickly search not the entire web, but across their already-experienced subset to get back to where they were. And that's why we think it's different.)<br />
<br />
[[User:Beltzner|Beltzner]] 19:35, 25 Feb 2006 (PST)<br />
<br />
Excellent point! I think any redesign of a bookmarking interface in current browsers needs to address exactly this problem. But, may be there is a simple solution: two different purposes require two different interfaces that serve each purpose best. Have a look at this little essay I wrote recently: <br />
http://pubnotes.wordpress.com/2007/10/14/rethinking-bookmarks-ui/<br />
<br />
-- 24 March 2006 --<br />
<br />
This is exactly what I think I would use. Give me a button I can hit when I find a site that is either interesting, or that I know I'll need to get back to soon. No popups. No "Enter tags" or "Rename" dialog or nothing. Just a button that does everything for me, and lites up to let me know its done. Then put a list of 'em (with readable names) in the sidebar or bookmarks menu or history menu or as a dropdown outta the URL bar. I don't really care where it shows up. I just don't wanna have to open a new window for it. That's pretty much all I use bookmarks for. And most of them I'm more than happy to be done with after 2 days usage.<br />
<br />
-- 19:43, 8 March 2006 --<br />
<br />
== Cheaper on bandwidth ==<br />
<br />
I'm glad to see firefox will be heading in this direction. Let me make a suggestion: if need be to implement server side functionality in order to keep cached bookmarks current, just remember that server hosting would benefit from this feature -and most likely jump to the aid of Firefox- since it would reduce the ammount of bandwidth they would waste unnecessarily.<br />
<br />
== Puts Places in a Tab ==<br />
<br />
Does the Places section have to be a window? These clutter the browsing space, and I think it would be much better to have the Places section in a tab itself. Opera does this at the moment with Bookmarks and History (separately). [[User:Sgd|Sgd]] 15:52, 20 Mar 2006 (PST)<br />
<br />
Places used to be in a tab but there were a lot of bugs related to having XUL in the content area of the window. I liked it better in a tab as well, and I hope we can go back to a tab in future releases when the bugs are fixed. ~BrettWilson<br />
<br />
== Sidebar for Bookmarks ==<br />
<br />
Having bookmarks in a sidebar is the fastest access (one click) and can be used as a permanent visual reminder for bookmarks, bookmarklets, and keywords to activate bookmarks (e.g. "dt _ del.icio.us tag search" with the %s hack and "dt" as keyword that I write as a visual reminder at the beginning of the line followed by an underscore to separate it from the text). The menu for bookmarks requires at lest two clicks and is a "dynamic" visual reminder. The sidebar and the menu for bookmarks are perfect interfaces for a small number of frequently visited websites and as toolbox for bookmarklets. So, don't drop it with the new Places Interface. --[[User:Tdot|Tdot]] 20:35, 23 March 2006 (PST)<br />
<br />
== Use Cases ==<br />
<br />
Someone really needs to fill in the use case section. It seems like it would be important when designing a UI. Somebody in the groups suggested this case: "One example is the deletion of a place. ... That used to delete just one instance in one folder for decades, and now you change it, including dataloss." Things that delete data should be done with a dialogue. Something that asks if you want to entirely remove the bookmark(s), or just remove it(them) from that category, and maybe an "Always perform this action" checkbox at the bottom.<br />
<br />
I looked through the UI and didn't see any UI on deleting bookmarks at all, but I apologize if this is a repeat of something. --[[User:Wjjohnst|wjjohnst]] April 13, 2006 3:20(CST)<br />
<br />
== Browse Bookmarks, then Save! ==<br />
<br />
A feature of Konqueror I always loved was that you could drop down any menu from your Bookmarks and click "Add Bookmark Here".<br />
<br />
I use my Bookmarks Toolbar and rarely the Bookmarks menu. It would be great to just click that category I want from the menu, navigate to one of my sub-menus and then click "Add Bookmark Here". I find this method much more speedy than using the "Bookmark This Page" function.<br />
<br />
If I want to organise my bookmarks the Bookmarks Manager will still be there, but when I want to quickly mark a page, I want to quickly mark it.<br />
<br />
Also, there are often situations when I might want to add several pages from a site or sites all in reasonably quick succession. Using the "Add Bookmark Here" function is the slow way of doing things.<br />
<br />
[[User:Bugman|Bugman]] 23:52, 15 May 2006 (ACST)<br />
<br />
== index ALL and pinpoint SOME ==<br />
<br />
Hi all,<br />
<br />
I am extremely surprised that even in Firefox 2 we still have no more than this completely obsolete and unpractical bookmark functionality to go back where we once were (apart from history of course).<br />
<br />
The solution is so simple! Of course the hierarchical organization of <br />
bookmarks is a nonsense and we'd be better off with a keyword/tag approach. <br />
Yet the solution I would retain is even simpler to implement and use: <br />
just put ALL the visited URLs back to a user chosen period of time in a local or remote htdig/glimpse style database, storing the last visits date as well. Also allow the user to add an extra pinpoint to URLs of particular interest to him/her and you're done!<br />
<br />
Indexing everything is important so that the user does not have to worry about not forgetting to bookmark, especially when there are several pages of interest within the same website.<br />
<br />
Well now OK the index might get big if an extensive surfer wants several years of storage. But this should not normally grow beyond the capacity of present hard disks. And remote storage is of course an option.<br />
<br />
Why looking further for awkward and complicated schemes most users will <br />
neglect to learn ? <br />
<br />
A functionally equivalent approach would be to ask Google to add your name, the date and your eventual pinpoint in their database when you visit a site ;-)<br />
<br />
--[[User:Alexandre Oberlin|Alexandre Oberlin]] 12:42, 31 December 2006 (PST)<br />
<br />
== Broken link ==<br />
<br />
The link ''"Database schema ERD"'' in sub section [[Places#Quick Links|Quick Links]] is broken (''"404 Not Found"'').<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:38, 20 July 2022 (UTC)<br />
<br />
== More broken links ==<br />
<br />
The link ''http://developer.mozilla.org/en/docs/Places'' (link text "Places developer wiki") is broken (two instances).<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:46, 20 July 2022 (UTC)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places&diff=1243422Talk:Places2022-07-20T20:40:43Z<p>PeterMortensen: More cleanup: Correct header levels</p>
<hr />
<div><br />
== Breaking bookmarks ==<br />
<br />
Best not break my bookmarks boy.<br />
<br />
<br />
== Store & synch bookmarks remotely ==<br />
<br />
This may be more of an extension feature than a core feature, but there isn't an extension out there that does it as well as it could be done.<br />
<br />
It would be fantastic to have a feature that allows you to store & synch your bookmarks at a remote location in a format that can be read in a web browser. I regularly use 3 different computer for work and pop into Internet cafes while traveling, and never seem to have the bookmark I need in the right place at the right time.<br />
<br />
The one extension I've found that comes close to this is Bookmarks Synchronizer that stores your bookmarks in an XML format. But it has a critical flaw that can cause you to lose your existing bookmarks and appears to have been abandonded after version 1.0.1.<br />
<br />
: Two points to make here.<br />
:<br />
: First, I agree with the post above. I sometimes work from home and if I find something worth bookmarking, I usually have to send myself the link at work (or viceversa). Having an online repository would be great.<br />
:<br />
: I'm afraid it may be late for the second point as you guys already are in the middle of implementation. However, I read an article by UI guru Jakob Nielsen years ago that was focused on Bookmarks, History. What I found is not what I had in mind but it mentions the same concepts. http://www.useit.com/alertbox/9711a.html<br />
<br />
== Integrate Firefox with the Windows "Favorites" system ==<br />
<br />
I'm going to propose something very radical, but please give it serious thought. Consider natively integrating Firefox with the Windows 98/Windows 2000/Windows XP "Favorites" system. I realise most people consider this to be an "evil IE feature", but all versions of Windows in the past 6+ years have included Favorites as a native, OS-level feature. Favorites are included in everything from the start menu to the "main menu" of explorer folder windows. Yes, it's part of the "Integrate IE with the OS curse", but I believe it falls within the Firefox philosophy - simple, standardize, OS-friendly user interfaces.<br />
<br />
My proposal: If a user loads Firefox on a Windows platform that supports favorites natively at the OS level, Firefox uses those favorites - either directly, or through some sort of "transparent caching". If the favorites are cached so that Firefox can optimize them, the user should not be aware of that - they should look and feel the same as Favorites, for the most part (ie/ no additional IE, no syncronization dialogs). <br />
<br />
An option/preference would allow the user to select which bookmark system to use (ie/ where new bookmarks go). "meta data" about the "favorite" (.url) bookmarks could be cached in firefox's bookmark file or another file somehow. <br />
<br />
I realize this is likely to be stomped out very quickly - but consider that "Favorites" are an operating system feature - supporting favorites is no different than supporting the native Windows "File Save Dialog" or rendering scrollbars with Windows XP Themes. In addition, it provides a very easy, gradual upgrade path for Internet Explorer users. They can easily "dabble" in Firefox until they are ready to take the full plunge. I can honestly say that even as a technical user, I found the lack of automatic/transparent bookmark syncronization a severe frustration when I first started using Firefox. <br />
<br />
I'm not suggesting the Firefox team sign a deal with the devil - but perhaps it would allow users to switch to Firefox with much less pain. :-) Nothing says that the favorites and the firefox bookmark file cannot be "merged" at display time.<br />
<br />
== Replacing local bookmarks with remote ==<br />
<br />
I'd find it really useful if you could actually provide a way for extensions to override native bookmarking functionality and use an online provider -- like del.icio.us, for example. I stopped using local bookmarks a long time ago for anything I wanted to remember in the future, so "Places" functionality will be completely lost on me unless there's a way to get external services in there somewhere.<br />
<br />
== Local/Absolute dual bookmarks ==<br />
<br />
I've long wanted a way to bookmark news articles that I have stored on my hard drive along with the URL. I frequently cite URLs as sources and it'd be nice to be able to quickly check the link, then source the file from my hard drive if the URL is dead. The first implementation that comes to mind is an icon to the right of the link text that summons the local file.<br />
<br />
Other suggestions:<br />
When using the navigation toolbar, checking a bookmark's properties or sorting a folder by name (inter alia) causes the tree to close/collapse/whatever. This is annoying.<br />
<br />
It would be great if we could shift-click and control-click to select bookmarks/folders for export from the bookmark manager.<br />
<br />
It would be great if we could export bookmarks and optionally have the result look and behave as the links in the navbar and/or bookmark manager do (i.e., preserve file structure).<br />
<br />
When copying/pasting bookmarks, it would be great if we had an option to allow the title(s) AND the URL(s) to be copied/pasted.<br />
<br />
It'd be great if we could automate a periodic check for dead URLs, with a nice detailed report, but I'm guessing this is the sort of thing tons of people have suggested.<br />
<br />
When saving pages maybe a way to make an article's publication date the file's creation date? I know this is outside the scope here...<br />
<br />
I'm sure I could come up with more given time. I'm very glad to hear that bookmarks are going to finally get some attention.<br />
<br />
== Tagging: different than bookmarking ==<br />
<br />
Recent thinking on the UI (as discussed in the newsgroups) has led us to shift from our initial thinking that tagging should be seen as an alternate way to create bookmarks, with tags simply acting as surrogates for bookmark folders. After looking at some hard questions about what that implied for nested folders, preserving existing bookmark heirarchy structures, and adjusting users' mental models, we began to see things differently.<br />
<br />
Most recently, we've started to see tagging as the ability to do something else: specifically, to be able to mark an entry in your web browsing history as "interesting."<br />
<br />
Here's the rationale: Bookmarks were originally intended to be shortcuts to commonly visited sites. But then the web got bigger, and people started using them as ways to remember interesting places. But then the web got bigger still, and all of a sudden the bookmarking system couldn't cope. Initial thoughts about tagging were: well, let's use tags and search to make it easier to handle large bookmark sets. But that led to some very good questions: search isn't as quick as click-click-click through a drop-down menu or click on a personal toolbar. The key was realizing that the use of bookmarks shifted and merged: a single system was being used to store shortcuts AND sites that were just interesting and that the user thought they might want to get back to. The goal of keeping all of these interesting sites is, well, to keep track of one's personal world wide web. To tag those pages that they thing are interesting and then be able to quickly search not the entire web, but across their already-experienced subset to get back to where they were. And that's why we think it's different.)<br />
<br />
[[User:Beltzner|Beltzner]] 19:35, 25 Feb 2006 (PST)<br />
<br />
Excellent point! I think any redesign of a bookmarking interface in current browsers needs to address exactly this problem. But, may be there is a simple solution: two different purposes require two different interfaces that serve each purpose best. Have a look at this little essay I wrote recently: <br />
http://pubnotes.wordpress.com/2007/10/14/rethinking-bookmarks-ui/<br />
<br />
-- 24 March 2006 --<br />
<br />
This is exactly what I think I would use. Give me a button I can hit when I find a site that is either interesting, or that I know I'll need to get back to soon. No popups. No "Enter tags" or "Rename" dialog or nothing. Just a button that does everything for me, and lites up to let me know its done. Then put a list of 'em (with readable names) in the sidebar or bookmarks menu or history menu or as a dropdown outta the URL bar. I don't really care where it shows up. I just don't wanna have to open a new window for it. That's pretty much all I use bookmarks for. And most of them I'm more than happy to be done with after 2 days usage.<br />
<br />
-- 19:43, 8 March 2006 --<br />
<br />
== Cheaper on bandwidth ==<br />
<br />
I'm glad to see firefox will be heading in this direction. Let me make a suggestion: if need be to implement server side functionality in order to keep cached bookmarks current, just remember that server hosting would benefit from this feature -and most likely jump to the aid of Firefox- since it would reduce the ammount of bandwidth they would waste unnecessarily.<br />
<br />
== Puts Places in a Tab ==<br />
<br />
Does the Places section have to be a window? These clutter the browsing space, and I think it would be much better to have the Places section in a tab itself. Opera does this at the moment with Bookmarks and History (separately). [[User:Sgd|Sgd]] 15:52, 20 Mar 2006 (PST)<br />
<br />
Places used to be in a tab but there were a lot of bugs related to having XUL in the content area of the window. I liked it better in a tab as well, and I hope we can go back to a tab in future releases when the bugs are fixed. ~BrettWilson<br />
<br />
== Sidebar for Bookmarks ==<br />
<br />
Having bookmarks in a sidebar is the fastest access (one click) and can be used as a permanent visual reminder for bookmarks, bookmarklets, and keywords to activate bookmarks (e.g. "dt _ del.icio.us tag search" with the %s hack and "dt" as keyword that I write as a visual reminder at the beginning of the line followed by an underscore to separate it from the text). The menu for bookmarks requires at lest two clicks and is a "dynamic" visual reminder. The sidebar and the menu for bookmarks are perfect interfaces for a small number of frequently visited websites and as toolbox for bookmarklets. So, don't drop it with the new Places Interface. --[[User:Tdot|Tdot]] 20:35, 23 March 2006 (PST)<br />
<br />
== Use Cases ==<br />
<br />
Someone really needs to fill in the use case section. It seems like it would be important when designing a UI. Somebody in the groups suggested this case: "One example is the deletion of a place. ... That used to delete just one instance in one folder for decades, and now you change it, including dataloss." Things that delete data should be done with a dialogue. Something that asks if you want to entirely remove the bookmark(s), or just remove it(them) from that category, and maybe an "Always perform this action" checkbox at the bottom.<br />
<br />
I looked through the UI and didn't see any UI on deleting bookmarks at all, but I apologize if this is a repeat of something. --[[User:Wjjohnst|wjjohnst]] April 13, 2006 3:20(CST)<br />
<br />
== Browse Bookmarks, then Save! ==<br />
<br />
A feature of Konqueror I always loved was that you could drop down any menu from your Bookmarks and click "Add Bookmark Here".<br />
<br />
I use my Bookmarks Toolbar and rarely the Bookmarks menu. It would be great to just click that category I want from the menu, navigate to one of my sub-menus and then click "Add Bookmark Here". I find this method much more speedy than using the "Bookmark This Page" function.<br />
<br />
If I want to organise my bookmarks the Bookmarks Manager will still be there, but when I want to quickly mark a page, I want to quickly mark it.<br />
<br />
Also, there are often situations when I might want to add several pages from a site or sites all in reasonably quick succession. Using the "Add Bookmark Here" function is the slow way of doing things.<br />
<br />
[[User:Bugman|Bugman]] 23:52, 15 May 2006 (ACST)<br />
<br />
== index ALL and pinpoint SOME ==<br />
<br />
Hi all,<br />
<br />
I am extremely surprised that even in Firefox 2 we still have no more than this completely obsolete and unpractical bookmark functionality to go back where we once were (apart from history of course).<br />
<br />
The solution is so simple! Of course the hierarchical organization of <br />
bookmarks is a nonsense and we'd be better off with a keyword/tag approach. <br />
Yet the solution I would retain is even simpler to implement and use: <br />
just put ALL the visited URLs back to a user chosen period of time in a local or remote htdig/glimpse style database, storing the last visits date as well. Also allow the user to add an extra pinpoint to URLs of particular interest to him/her and you're done!<br />
<br />
Indexing everything is important so that the user does not have to worry about not forgetting to bookmark, especially when there are several pages of interest within the same website.<br />
<br />
Well now OK the index might get big if an extensive surfer wants several years of storage. But this should not normally grow beyond the capacity of present hard disks. And remote storage is of course an option.<br />
<br />
Why looking further for awkward and complicated schemes most users will <br />
neglect to learn ? <br />
<br />
A functionally equivalent approach would be to ask Google to add your name, the date and your eventual pinpoint in their database when you visit a site ;-)<br />
<br />
--[[User:Alexandre Oberlin|Alexandre Oberlin]] 12:42, 31 December 2006 (PST)<br />
<br />
== Broken link ==<br />
<br />
The link ''"Database schema ERD"'' in sub section [[Places#Quick Links|Quick Links]] is broken (''"404 Not Found"'').<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:38, 20 July 2022 (UTC)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places&diff=1243421Talk:Places2022-07-20T20:38:21Z<p>PeterMortensen: /* Broken link */ new section</p>
<hr />
<div><br />
== Breaking bookmarks ==<br />
<br />
Best not break my bookmarks boy.<br />
<br />
<br />
== Store & synch bookmarks remotely ==<br />
<br />
This may be more of an extension feature than a core feature, but there isn't an extension out there that does it as well as it could be done.<br />
<br />
It would be fantastic to have a feature that allows you to store & synch your bookmarks at a remote location in a format that can be read in a web browser. I regularly use 3 different computer for work and pop into Internet cafes while traveling, and never seem to have the bookmark I need in the right place at the right time.<br />
<br />
The one extension I've found that comes close to this is Bookmarks Synchronizer that stores your bookmarks in an XML format. But it has a critical flaw that can cause you to lose your existing bookmarks and appears to have been abandonded after version 1.0.1.<br />
<br />
: Two points to make here.<br />
:<br />
: First, I agree with the post above. I sometimes work from home and if I find something worth bookmarking, I usually have to send myself the link at work (or viceversa). Having an online repository would be great.<br />
:<br />
: I'm afraid it may be late for the second point as you guys already are in the middle of implementation. However, I read an article by UI guru Jakob Nielsen years ago that was focused on Bookmarks, History. What I found is not what I had in mind but it mentions the same concepts. http://www.useit.com/alertbox/9711a.html<br />
<br />
== Integrate Firefox with the Windows "Favorites" system ==<br />
<br />
I'm going to propose something very radical, but please give it serious thought. Consider natively integrating Firefox with the Windows 98/Windows 2000/Windows XP "Favorites" system. I realise most people consider this to be an "evil IE feature", but all versions of Windows in the past 6+ years have included Favorites as a native, OS-level feature. Favorites are included in everything from the start menu to the "main menu" of explorer folder windows. Yes, it's part of the "Integrate IE with the OS curse", but I believe it falls within the Firefox philosophy - simple, standardize, OS-friendly user interfaces.<br />
<br />
My proposal: If a user loads Firefox on a Windows platform that supports favorites natively at the OS level, Firefox uses those favorites - either directly, or through some sort of "transparent caching". If the favorites are cached so that Firefox can optimize them, the user should not be aware of that - they should look and feel the same as Favorites, for the most part (ie/ no additional IE, no syncronization dialogs). <br />
<br />
An option/preference would allow the user to select which bookmark system to use (ie/ where new bookmarks go). "meta data" about the "favorite" (.url) bookmarks could be cached in firefox's bookmark file or another file somehow. <br />
<br />
I realize this is likely to be stomped out very quickly - but consider that "Favorites" are an operating system feature - supporting favorites is no different than supporting the native Windows "File Save Dialog" or rendering scrollbars with Windows XP Themes. In addition, it provides a very easy, gradual upgrade path for Internet Explorer users. They can easily "dabble" in Firefox until they are ready to take the full plunge. I can honestly say that even as a technical user, I found the lack of automatic/transparent bookmark syncronization a severe frustration when I first started using Firefox. <br />
<br />
I'm not suggesting the Firefox team sign a deal with the devil - but perhaps it would allow users to switch to Firefox with much less pain. :-) Nothing says that the favorites and the firefox bookmark file cannot be "merged" at display time.<br />
<br />
== Replacing local bookmarks with remote ==<br />
<br />
I'd find it really useful if you could actually provide a way for extensions to override native bookmarking functionality and use an online provider -- like del.icio.us, for example. I stopped using local bookmarks a long time ago for anything I wanted to remember in the future, so "Places" functionality will be completely lost on me unless there's a way to get external services in there somewhere.<br />
<br />
== Local/Absolute dual bookmarks ==<br />
<br />
I've long wanted a way to bookmark news articles that I have stored on my hard drive along with the URL. I frequently cite URLs as sources and it'd be nice to be able to quickly check the link, then source the file from my hard drive if the URL is dead. The first implementation that comes to mind is an icon to the right of the link text that summons the local file.<br />
<br />
Other suggestions:<br />
When using the navigation toolbar, checking a bookmark's properties or sorting a folder by name (inter alia) causes the tree to close/collapse/whatever. This is annoying.<br />
<br />
It would be great if we could shift-click and control-click to select bookmarks/folders for export from the bookmark manager.<br />
<br />
It would be great if we could export bookmarks and optionally have the result look and behave as the links in the navbar and/or bookmark manager do (i.e., preserve file structure).<br />
<br />
When copying/pasting bookmarks, it would be great if we had an option to allow the title(s) AND the URL(s) to be copied/pasted.<br />
<br />
It'd be great if we could automate a periodic check for dead URLs, with a nice detailed report, but I'm guessing this is the sort of thing tons of people have suggested.<br />
<br />
When saving pages maybe a way to make an article's publication date the file's creation date? I know this is outside the scope here...<br />
<br />
I'm sure I could come up with more given time. I'm very glad to hear that bookmarks are going to finally get some attention.<br />
<br />
----<br />
= Tagging: different than bookmarking =<br />
<br />
Recent thinking on the UI (as discussed in the newsgroups) has led us to shift from our initial thinking that tagging should be seen as an alternate way to create bookmarks, with tags simply acting as surrogates for bookmark folders. After looking at some hard questions about what that implied for nested folders, preserving existing bookmark heirarchy structures, and adjusting users' mental models, we began to see things differently.<br />
<br />
Most recently, we've started to see tagging as the ability to do something else: specifically, to be able to mark an entry in your web browsing history as "interesting."<br />
<br />
Here's the rationale: Bookmarks were originally intended to be shortcuts to commonly visited sites. But then the web got bigger, and people started using them as ways to remember interesting places. But then the web got bigger still, and all of a sudden the bookmarking system couldn't cope. Initial thoughts about tagging were: well, let's use tags and search to make it easier to handle large bookmark sets. But that led to some very good questions: search isn't as quick as click-click-click through a drop-down menu or click on a personal toolbar. The key was realizing that the use of bookmarks shifted and merged: a single system was being used to store shortcuts AND sites that were just interesting and that the user thought they might want to get back to. The goal of keeping all of these interesting sites is, well, to keep track of one's personal world wide web. To tag those pages that they thing are interesting and then be able to quickly search not the entire web, but across their already-experienced subset to get back to where they were. And that's why we think it's different.)<br />
<br />
[[User:Beltzner|Beltzner]] 19:35, 25 Feb 2006 (PST)<br />
<br />
Excellent point! I think any redesign of a bookmarking interface in current browsers needs to address exactly this problem. But, may be there is a simple solution: two different purposes require two different interfaces that serve each purpose best. Have a look at this little essay I wrote recently: <br />
http://pubnotes.wordpress.com/2007/10/14/rethinking-bookmarks-ui/<br />
<br />
-- 24 March 2006 --<br />
<br />
This is exactly what I think I would use. Give me a button I can hit when I find a site that is either interesting, or that I know I'll need to get back to soon. No popups. No "Enter tags" or "Rename" dialog or nothing. Just a button that does everything for me, and lites up to let me know its done. Then put a list of 'em (with readable names) in the sidebar or bookmarks menu or history menu or as a dropdown outta the URL bar. I don't really care where it shows up. I just don't wanna have to open a new window for it. That's pretty much all I use bookmarks for. And most of them I'm more than happy to be done with after 2 days usage.<br />
<br />
-- 19:43, 8 March 2006 --<br />
<br />
== Cheaper on bandwidth ==<br />
<br />
I'm glad to see firefox will be heading in this direction. Let me make a suggestion: if need be to implement server side functionality in order to keep cached bookmarks current, just remember that server hosting would benefit from this feature -and most likely jump to the aid of Firefox- since it would reduce the ammount of bandwidth they would waste unnecessarily.<br />
<br />
== Puts Places in a Tab ==<br />
<br />
Does the Places section have to be a window? These clutter the browsing space, and I think it would be much better to have the Places section in a tab itself. Opera does this at the moment with Bookmarks and History (separately). [[User:Sgd|Sgd]] 15:52, 20 Mar 2006 (PST)<br />
<br />
Places used to be in a tab but there were a lot of bugs related to having XUL in the content area of the window. I liked it better in a tab as well, and I hope we can go back to a tab in future releases when the bugs are fixed. ~BrettWilson<br />
<br />
= Sidebar for Bookmarks =<br />
<br />
Having bookmarks in a sidebar is the fastest access (one click) and can be used as a permanent visual reminder for bookmarks, bookmarklets, and keywords to activate bookmarks (e.g. "dt _ del.icio.us tag search" with the %s hack and "dt" as keyword that I write as a visual reminder at the beginning of the line followed by an underscore to separate it from the text). The menu for bookmarks requires at lest two clicks and is a "dynamic" visual reminder. The sidebar and the menu for bookmarks are perfect interfaces for a small number of frequently visited websites and as toolbox for bookmarklets. So, don't drop it with the new Places Interface. --[[User:Tdot|Tdot]] 20:35, 23 March 2006 (PST)<br />
<br />
= Use Cases =<br />
<br />
Someone really needs to fill in the use case section. It seems like it would be important when designing a UI. Somebody in the groups suggested this case: "One example is the deletion of a place. ... That used to delete just one instance in one folder for decades, and now you change it, including dataloss." Things that delete data should be done with a dialogue. Something that asks if you want to entirely remove the bookmark(s), or just remove it(them) from that category, and maybe an "Always perform this action" checkbox at the bottom.<br />
<br />
I looked through the UI and didn't see any UI on deleting bookmarks at all, but I apologize if this is a repeat of something. --[[User:Wjjohnst|wjjohnst]] April 13, 2006 3:20(CST)<br />
<br />
= Browse Bookmarks, then Save! =<br />
<br />
A feature of Konqueror I always loved was that you could drop down any menu from your Bookmarks and click "Add Bookmark Here".<br />
<br />
I use my Bookmarks Toolbar and rarely the Bookmarks menu. It would be great to just click that category I want from the menu, navigate to one of my sub-menus and then click "Add Bookmark Here". I find this method much more speedy than using the "Bookmark This Page" function.<br />
<br />
If I want to organise my bookmarks the Bookmarks Manager will still be there, but when I want to quickly mark a page, I want to quickly mark it.<br />
<br />
Also, there are often situations when I might want to add several pages from a site or sites all in reasonably quick succession. Using the "Add Bookmark Here" function is the slow way of doing things.<br />
<br />
[[User:Bugman|Bugman]] 23:52, 15 May 2006 (ACST)<br />
<br />
== index ALL and pinpoint SOME ==<br />
<br />
Hi all,<br />
<br />
I am extremely surprised that even in Firefox 2 we still have no more than this completely obsolete and unpractical bookmark functionality to go back where we once were (apart from history of course).<br />
<br />
The solution is so simple! Of course the hierarchical organization of <br />
bookmarks is a nonsense and we'd be better off with a keyword/tag approach. <br />
Yet the solution I would retain is even simpler to implement and use: <br />
just put ALL the visited URLs back to a user chosen period of time in a local or remote htdig/glimpse style database, storing the last visits date as well. Also allow the user to add an extra pinpoint to URLs of particular interest to him/her and you're done!<br />
<br />
Indexing everything is important so that the user does not have to worry about not forgetting to bookmark, especially when there are several pages of interest within the same website.<br />
<br />
Well now OK the index might get big if an extensive surfer wants several years of storage. But this should not normally grow beyond the capacity of present hard disks. And remote storage is of course an option.<br />
<br />
Why looking further for awkward and complicated schemes most users will <br />
neglect to learn ? <br />
<br />
A functionally equivalent approach would be to ask Google to add your name, the date and your eventual pinpoint in their database when you visit a site ;-)<br />
<br />
--[[User:Alexandre Oberlin|Alexandre Oberlin]] 12:42, 31 December 2006 (PST)<br />
<br />
== Broken link ==<br />
<br />
The link ''"Database schema ERD"'' in sub section [[Places#Quick Links|Quick Links]] is broken (''"404 Not Found"'').<br />
<br />
--[[User:PeterMortensen|PeterMortensen]] ([[User talk:PeterMortensen|talk]]) 20:38, 20 July 2022 (UTC)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Talk:Places&diff=1243420Talk:Places2022-07-20T20:35:28Z<p>PeterMortensen: Cleaned up: Added the missing sub section headers and missing threading.</p>
<hr />
<div><br />
== Breaking bookmarks ==<br />
<br />
Best not break my bookmarks boy.<br />
<br />
<br />
== Store & synch bookmarks remotely ==<br />
<br />
This may be more of an extension feature than a core feature, but there isn't an extension out there that does it as well as it could be done.<br />
<br />
It would be fantastic to have a feature that allows you to store & synch your bookmarks at a remote location in a format that can be read in a web browser. I regularly use 3 different computer for work and pop into Internet cafes while traveling, and never seem to have the bookmark I need in the right place at the right time.<br />
<br />
The one extension I've found that comes close to this is Bookmarks Synchronizer that stores your bookmarks in an XML format. But it has a critical flaw that can cause you to lose your existing bookmarks and appears to have been abandonded after version 1.0.1.<br />
<br />
: Two points to make here.<br />
:<br />
: First, I agree with the post above. I sometimes work from home and if I find something worth bookmarking, I usually have to send myself the link at work (or viceversa). Having an online repository would be great.<br />
:<br />
: I'm afraid it may be late for the second point as you guys already are in the middle of implementation. However, I read an article by UI guru Jakob Nielsen years ago that was focused on Bookmarks, History. What I found is not what I had in mind but it mentions the same concepts. http://www.useit.com/alertbox/9711a.html<br />
<br />
== Integrate Firefox with the Windows "Favorites" system ==<br />
<br />
I'm going to propose something very radical, but please give it serious thought. Consider natively integrating Firefox with the Windows 98/Windows 2000/Windows XP "Favorites" system. I realise most people consider this to be an "evil IE feature", but all versions of Windows in the past 6+ years have included Favorites as a native, OS-level feature. Favorites are included in everything from the start menu to the "main menu" of explorer folder windows. Yes, it's part of the "Integrate IE with the OS curse", but I believe it falls within the Firefox philosophy - simple, standardize, OS-friendly user interfaces.<br />
<br />
My proposal: If a user loads Firefox on a Windows platform that supports favorites natively at the OS level, Firefox uses those favorites - either directly, or through some sort of "transparent caching". If the favorites are cached so that Firefox can optimize them, the user should not be aware of that - they should look and feel the same as Favorites, for the most part (ie/ no additional IE, no syncronization dialogs). <br />
<br />
An option/preference would allow the user to select which bookmark system to use (ie/ where new bookmarks go). "meta data" about the "favorite" (.url) bookmarks could be cached in firefox's bookmark file or another file somehow. <br />
<br />
I realize this is likely to be stomped out very quickly - but consider that "Favorites" are an operating system feature - supporting favorites is no different than supporting the native Windows "File Save Dialog" or rendering scrollbars with Windows XP Themes. In addition, it provides a very easy, gradual upgrade path for Internet Explorer users. They can easily "dabble" in Firefox until they are ready to take the full plunge. I can honestly say that even as a technical user, I found the lack of automatic/transparent bookmark syncronization a severe frustration when I first started using Firefox. <br />
<br />
I'm not suggesting the Firefox team sign a deal with the devil - but perhaps it would allow users to switch to Firefox with much less pain. :-) Nothing says that the favorites and the firefox bookmark file cannot be "merged" at display time.<br />
<br />
== Replacing local bookmarks with remote ==<br />
<br />
I'd find it really useful if you could actually provide a way for extensions to override native bookmarking functionality and use an online provider -- like del.icio.us, for example. I stopped using local bookmarks a long time ago for anything I wanted to remember in the future, so "Places" functionality will be completely lost on me unless there's a way to get external services in there somewhere.<br />
<br />
== Local/Absolute dual bookmarks ==<br />
<br />
I've long wanted a way to bookmark news articles that I have stored on my hard drive along with the URL. I frequently cite URLs as sources and it'd be nice to be able to quickly check the link, then source the file from my hard drive if the URL is dead. The first implementation that comes to mind is an icon to the right of the link text that summons the local file.<br />
<br />
Other suggestions:<br />
When using the navigation toolbar, checking a bookmark's properties or sorting a folder by name (inter alia) causes the tree to close/collapse/whatever. This is annoying.<br />
<br />
It would be great if we could shift-click and control-click to select bookmarks/folders for export from the bookmark manager.<br />
<br />
It would be great if we could export bookmarks and optionally have the result look and behave as the links in the navbar and/or bookmark manager do (i.e., preserve file structure).<br />
<br />
When copying/pasting bookmarks, it would be great if we had an option to allow the title(s) AND the URL(s) to be copied/pasted.<br />
<br />
It'd be great if we could automate a periodic check for dead URLs, with a nice detailed report, but I'm guessing this is the sort of thing tons of people have suggested.<br />
<br />
When saving pages maybe a way to make an article's publication date the file's creation date? I know this is outside the scope here...<br />
<br />
I'm sure I could come up with more given time. I'm very glad to hear that bookmarks are going to finally get some attention.<br />
<br />
----<br />
= Tagging: different than bookmarking =<br />
<br />
Recent thinking on the UI (as discussed in the newsgroups) has led us to shift from our initial thinking that tagging should be seen as an alternate way to create bookmarks, with tags simply acting as surrogates for bookmark folders. After looking at some hard questions about what that implied for nested folders, preserving existing bookmark heirarchy structures, and adjusting users' mental models, we began to see things differently.<br />
<br />
Most recently, we've started to see tagging as the ability to do something else: specifically, to be able to mark an entry in your web browsing history as "interesting."<br />
<br />
Here's the rationale: Bookmarks were originally intended to be shortcuts to commonly visited sites. But then the web got bigger, and people started using them as ways to remember interesting places. But then the web got bigger still, and all of a sudden the bookmarking system couldn't cope. Initial thoughts about tagging were: well, let's use tags and search to make it easier to handle large bookmark sets. But that led to some very good questions: search isn't as quick as click-click-click through a drop-down menu or click on a personal toolbar. The key was realizing that the use of bookmarks shifted and merged: a single system was being used to store shortcuts AND sites that were just interesting and that the user thought they might want to get back to. The goal of keeping all of these interesting sites is, well, to keep track of one's personal world wide web. To tag those pages that they thing are interesting and then be able to quickly search not the entire web, but across their already-experienced subset to get back to where they were. And that's why we think it's different.)<br />
<br />
[[User:Beltzner|Beltzner]] 19:35, 25 Feb 2006 (PST)<br />
<br />
Excellent point! I think any redesign of a bookmarking interface in current browsers needs to address exactly this problem. But, may be there is a simple solution: two different purposes require two different interfaces that serve each purpose best. Have a look at this little essay I wrote recently: <br />
http://pubnotes.wordpress.com/2007/10/14/rethinking-bookmarks-ui/<br />
<br />
-- 24 March 2006 --<br />
<br />
This is exactly what I think I would use. Give me a button I can hit when I find a site that is either interesting, or that I know I'll need to get back to soon. No popups. No "Enter tags" or "Rename" dialog or nothing. Just a button that does everything for me, and lites up to let me know its done. Then put a list of 'em (with readable names) in the sidebar or bookmarks menu or history menu or as a dropdown outta the URL bar. I don't really care where it shows up. I just don't wanna have to open a new window for it. That's pretty much all I use bookmarks for. And most of them I'm more than happy to be done with after 2 days usage.<br />
<br />
-- 19:43, 8 March 2006 --<br />
<br />
== Cheaper on bandwidth ==<br />
<br />
I'm glad to see firefox will be heading in this direction. Let me make a suggestion: if need be to implement server side functionality in order to keep cached bookmarks current, just remember that server hosting would benefit from this feature -and most likely jump to the aid of Firefox- since it would reduce the ammount of bandwidth they would waste unnecessarily.<br />
<br />
== Puts Places in a Tab ==<br />
<br />
Does the Places section have to be a window? These clutter the browsing space, and I think it would be much better to have the Places section in a tab itself. Opera does this at the moment with Bookmarks and History (separately). [[User:Sgd|Sgd]] 15:52, 20 Mar 2006 (PST)<br />
<br />
Places used to be in a tab but there were a lot of bugs related to having XUL in the content area of the window. I liked it better in a tab as well, and I hope we can go back to a tab in future releases when the bugs are fixed. ~BrettWilson<br />
<br />
= Sidebar for Bookmarks =<br />
<br />
Having bookmarks in a sidebar is the fastest access (one click) and can be used as a permanent visual reminder for bookmarks, bookmarklets, and keywords to activate bookmarks (e.g. "dt _ del.icio.us tag search" with the %s hack and "dt" as keyword that I write as a visual reminder at the beginning of the line followed by an underscore to separate it from the text). The menu for bookmarks requires at lest two clicks and is a "dynamic" visual reminder. The sidebar and the menu for bookmarks are perfect interfaces for a small number of frequently visited websites and as toolbox for bookmarklets. So, don't drop it with the new Places Interface. --[[User:Tdot|Tdot]] 20:35, 23 March 2006 (PST)<br />
<br />
= Use Cases =<br />
<br />
Someone really needs to fill in the use case section. It seems like it would be important when designing a UI. Somebody in the groups suggested this case: "One example is the deletion of a place. ... That used to delete just one instance in one folder for decades, and now you change it, including dataloss." Things that delete data should be done with a dialogue. Something that asks if you want to entirely remove the bookmark(s), or just remove it(them) from that category, and maybe an "Always perform this action" checkbox at the bottom.<br />
<br />
I looked through the UI and didn't see any UI on deleting bookmarks at all, but I apologize if this is a repeat of something. --[[User:Wjjohnst|wjjohnst]] April 13, 2006 3:20(CST)<br />
<br />
= Browse Bookmarks, then Save! =<br />
<br />
A feature of Konqueror I always loved was that you could drop down any menu from your Bookmarks and click "Add Bookmark Here".<br />
<br />
I use my Bookmarks Toolbar and rarely the Bookmarks menu. It would be great to just click that category I want from the menu, navigate to one of my sub-menus and then click "Add Bookmark Here". I find this method much more speedy than using the "Bookmark This Page" function.<br />
<br />
If I want to organise my bookmarks the Bookmarks Manager will still be there, but when I want to quickly mark a page, I want to quickly mark it.<br />
<br />
Also, there are often situations when I might want to add several pages from a site or sites all in reasonably quick succession. Using the "Add Bookmark Here" function is the slow way of doing things.<br />
<br />
[[User:Bugman|Bugman]] 23:52, 15 May 2006 (ACST)<br />
<br />
== index ALL and pinpoint SOME ==<br />
<br />
Hi all,<br />
<br />
I am extremely surprised that even in Firefox 2 we still have no more than this completely obsolete and unpractical bookmark functionality to go back where we once were (apart from history of course).<br />
<br />
The solution is so simple! Of course the hierarchical organization of <br />
bookmarks is a nonsense and we'd be better off with a keyword/tag approach. <br />
Yet the solution I would retain is even simpler to implement and use: <br />
just put ALL the visited URLs back to a user chosen period of time in a local or remote htdig/glimpse style database, storing the last visits date as well. Also allow the user to add an extra pinpoint to URLs of particular interest to him/her and you're done!<br />
<br />
Indexing everything is important so that the user does not have to worry about not forgetting to bookmark, especially when there are several pages of interest within the same website.<br />
<br />
Well now OK the index might get big if an extensive surfer wants several years of storage. But this should not normally grow beyond the capacity of present hard disks. And remote storage is of course an option.<br />
<br />
Why looking further for awkward and complicated schemes most users will <br />
neglect to learn ? <br />
<br />
A functionally equivalent approach would be to ask Google to add your name, the date and your eventual pinpoint in their database when you visit a site ;-)<br />
<br />
--[[User:Alexandre Oberlin|Alexandre Oberlin]] 12:42, 31 December 2006 (PST)</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=User:PeterMortensen&diff=1161759User:PeterMortensen2017-02-02T21:05:45Z<p>PeterMortensen: More regular page, at least for now.</p>
<hr />
<div>==Profile==<br />
<br />
Profile: see [http://en.wikipedia.org/wiki/User:Mortense my Wikipedia user page].<br />
<br />
Credentials: more than 5000 edits on the English Wikipedia.<br />
<br />
==Shortcuts==<br />
<br />
* [[Special:Random|Random page]]</div>PeterMortensenhttps://wiki.mozilla.org/index.php?title=Performance/Avoid_SQLite_In_Your_Next_Firefox_Feature&diff=1161758Performance/Avoid SQLite In Your Next Firefox Feature2017-02-02T21:02:08Z<p>PeterMortensen: /* Introduction */ Some copy editing.</p>
<hr />
<div>== Introduction ==<br />
Many Firefox developers see SQLite as a default choice for storing any non-trivial amount of data, and this wiki explains why that view is incorrect.<br />
<br />
SQLite may seem like a very appealing option when first designing a feature. It offers the familiar and powerful SQL language, our codebase has nice C++ and JavaScript APIs for it, and it's already used in many places in the codebase. However, when choosing storage formats, we have to keep in mind that SQLite is a powerful and reliable database and that it comes with a lot of '''hidden complexity'''. It's very tempting to take the abstractions it offers at face value and to ignore the complexity happening under the hood. As a result, we have repeatedly seen SQLite become a source of performance problems. This isn't an indictment of SQLite itself -- any other relational embedded DB would pose the same challenges.<br />
<br />
SQLite DBs are simply too complex to be used for relatively simple data storage needs. The list below outlines some of the ways SQLite can backfire on developers.<br />
<br />
== SQLite Pitfalls ==<br />
<br />
=== Storage Footprint ===<br />
<br />
* Features that regularly add data to the DB (e.g. visited URIs), but have no expiration policy for the data, can easily cause the DB to balloon into tens or hundreds of MBs. This is particularly undesirable on mobile devices<br />
* WAL journals have been known to grow very large, e.g. {{bug|609122}}, {{bug|608422}}, their size must be limited at a performance cost<br />
* Large transactions cause large journals, so transactions should be kept as small as possible<br />
* Every index contains a complete copy of the indexed data. Creating indexes on lengthy text fields (like URIs) will cause very large indexes to be stored on disk <br />
* Using the default microsecond precision for timestamps causes unnecessary bloat<br />
<br />
=== I/O Patterns ===<br />
<br />
* Your database file can suffer from logical/internal fragmentation due to common SQL patterns <br />
** Eg appending data to columns with indexes causes interleaved index and table data. This means both table and index scans are non-sequential until a VACUUM is performed. <br />
** Appending data to multiple tables interleaves tables too.<br />
** Deleting data does not shrink the database, just marks pages as unused.<br />
* Physical/external fragmentation can also happen due to SQLite using inappropriate defaults<br />
** SQLite does not preallocate files by default. Appending data to an SQLite in multiple sessions often means the OS has to start a new data block that's not adjacent to the previous one. <br />
** By default sqlite will grow/shrink the database file by |page_size|. This behavior causes new data blocks to be allocated too. This is especially problematic on OSX, Linux XFS.<br />
** SQLite [https://www.sqlite.org/c3ref/c_fcntl_chunk_size.html#sqlitefcntlchunksize CHUNK_SIZE] feature is a way to minimize this problem<br />
Factory-default SQLite [http://www.sqlite.org/pragma.html#pragma_page_size page size] is 1024 bytes. When the Mozilla default page size was changed to 32KB in {{bug|416330}}, there was a [http://taras.glek.net/blog/2013/06/28/new-performance-people/ 4x reduction in SQLite IO waits] according to Telemetry. This is likely due to a reduction in syscalls and OS readahead.<br />
* Performance can [http://sqlite.1065341.n5.nabble.com/Coping-with-database-growth-fragmentation-td44781.html#a44782 significantly degenerate over time]; scheduling periodic [https://sqlite.org/lang_vacuum.html rebuilds of the DB] is necessary. <br />
** Note that this "vacuuming" only deals with internal fragmentation, not the external [filesystem] fragmentation<br />
** VACUUM empties the free pages space of the database file, that means after a vacuum, until there's new free space, inserts will be a little bit more expensive cause they will have to grow the file<br />
* SQLite uses fsync's to guarantee transaction durability and enable recovery from crashes. fsyncs can be very expensive, and happen relatively frequently in the default rollback-journal mode. This performance/reliability trade-off might not be necessary for simple storage needs, especially if the important data can be rebuilt at any time.<br />
* JSON files or log files will show better I/O patterns almost every time, especially if they're compressed and read/written in entirety each time<br />
<br />
=== Memory Usage ===<br />
<br />
* By default, our SQLite wrapper uses a [http://hg.mozilla.org/mozilla-central/file/444714c3820a/storage/src/mozStorageConnection.cpp#l48 max of 2MB memory cache per connection].<br />
** This cache size may be too large for Fennec and B2G, especially if there are multiple connections. The cache size should be adjusted with [[#Important Pragmas|PRAGMAs]]<br />
** Queries unable to use an index will use SCAN TABLE to go through all of the rows, this will quickly grow the page memory cache to the maximum value, this space is never called back until PRAGMA shrink_memory is used, or the connection is closed.<br />
<br />
=== CPU Usage ===<br />
<br />
* Users have found certain Firefox features using 15% of their CPU time because SQL statements were constantly being executed in the background<br />
* Lock contention can occur when using the DB from multiple threads<br />
** e.g. VACCUM-ing the DB on one thread while executing SQL queries on another<br />
<br />
=== Battery Life ===<br />
<br />
* Same as CPU, important on mobile<br />
<br />
=== Unintended Main-Thread I/O ===<br />
<br />
Main-thread SQL is a known evil, and luckily there are only a few major sources of it left in the codebase, but the [http://telemetry.mozilla.org/slowsql/ Slow SQL dashboard] shows that developers are still getting bitten by main thread I/O from unexpected sources:<br />
<br />
* PRAGMA statements to set the cache size are done on the main thread, but if this is the first transaction of the session, it can trigger SQLite crash recovery operations. This is particularly bad if using the default WAL journal size. Also note that "crashes" are particularly common on mobile where we never have clean exits.<br />
* Some addons access Firefox DBs directly using main thread SQL<br />
<br />
=== Contending for Access to Storage with the Main Thread ===<br />
<br />
Bad SQLite performance off the main thread also degrades Firefox responsiveness because it can contend with the main thread for access to storage. Sadly our code still does main-thread I/O, and additionally, there will always be main-thread IO from add-ons and swapping.<br />
<br />
* Even seemingly simple SQL queries can take multiple seconds to execute<br />
** e.g. in {{bug|966469}} a user reports the following statement taking 22 seconds to execute: <code>DELETE FROM moz_pages WHERE id NOT IN (SELECT DISTINCT(pid) FROM moz_subresources);</code><br />
<br />
=== UX Impact of Slow Queries ===<br />
<br />
Slow DB operations hurt UX. For example, you might have noticed that the AwesomeBar sometimes takes a very long time to fetch any auto-complete suggestions. <br />
<br />
* The schemas have to be designed carefully and the DB has to be maintained periodically<br />
* Fragmentation causes extra seeks, and the problem is made worse by magnetic hard disks and cheap or old SSDs. Vacuuming DBs helps, but vacuums are resource-intensive and scheduling them can be tricky. See the [[#More Tips|Tips]] section<br />
<br />
== How to Store Your Data ==<br />
<br />
* If you need to store a small amount of data (less than 1MB), you should use JSON files, and do all your I/O off the main thread. Simple solutions with linear worst-case performance are ideal.<br />
** If you're working with larger amounts of data (roughly on the order of 1MB), you should compress the JSON data with Snappy or lz4 before writing it to disk<br />
** You can use OS.File's [https://developer.mozilla.org/en-US/docs/JavaScript_OS.File/OS.File_for_the_main_thread#OS.File.writeAtomic%28%29 writeAtomic()] method. More information on writeAtomic [http://dutherenverseauborddelatable.wordpress.com/2014/02/05/is-my-data-on-the-disk-safety-properties-of-os-file-writeatomic/ in this blog post]<br />
** If your workload involves a lot of strings, don't use SQLite. Store your data in external files.<br />
** If your workload involves a lot of blobs, don't use SQLite. Store your data in external files.<br />
* If you have a large dataset, but you don't need to run complex queries over it, evaluate a JSON files structure on disk<br />
* If you must read and write most of your data every time, evaluate using a JSON file<br />
* For larger datasets or when SQL is absolutely necessary, use SQLite.<br />
** Make sure you understand [https://sqlite.org/docs.html how SQLite works], carefully design your schemas and then profile your implementation.<br />
** IndexedDB is implemented on top of SQLite and has additional issues<br />
<br />
NOTE: ''We're currently working on an intermediate solution based on log-storage which will reduce the amount of data written for small modifications to data already on disk.''<br />
<br />
== How to Best Use SQLite If You Really Need To ==<br />
<br />
=== Important Pragmas ===<br />
See [http://www.sqlite.org/pragma.html the list of SQLite pragmas], the defaults are probably not OK. We already over-ride some defaults in the [http://mxr.mozilla.org/mozilla-central/source/db/sqlite3/src/moz.build moz.build file] and [http://mxr.mozilla.org/mozilla-central/source/storage/src/mozStorageConnection.cpp#622 mozStorageConnection.cpp].<br />
<br />
* Use a [https://www.sqlite.org/wal.html write-ahead log] for fewer fsyncs<br />
** <code>PRAGMA journal_mode = WAL;</code><br />
** Though limit the size of the journal, by using a small autocheckpoint and a journal_size_limit about thrice that size<br />
*** <code>PRAGMA wal_autocheckpoint = 16; /* number of 32KiB pages in a 512KiB journal */</code><br />
*** <code>PRAGMA journal_size_limit = 1536; /* 512KiB * 3 */</code><br />
** If it's not possible to use WAL journaling, consider using a TRUNCATE journal<br />
* Set the DB cache size based on the device specs, and consider adjusting it dynamically based on memory pressure on mobile devices<br />
** <code>PRAGMA cache_size = X;</code><br />
* For large DBs prone to fragmentation, pre-allocate a large empty DB initially by increasing the DB's growth increment<br />
** This can be done by calling Connection::SetGrowthIncrement() which changes the value of SQLite's <code>SQLITE_FCNTL_CHUNK_SIZE</code>. See {{bug|581606}} for reference.<br />
* Use a memory temp store if the amount of data in memory entities is acceptable, otherwise temp data will be written to disk, causing unexpected I/O<br />
** <code>PRAGMA temp_store = MEMORY</code><br />
<br />
=== More Tips ===<br />
<br />
* It should go without saying that you should '''never execute SQL on the main thread'''<br />
** Use the [https://developer.mozilla.org/en-US/docs/Mozilla/JavaScript_code_modules/Sqlite.jsm SQLite.jsm wrapper] for DB operations from JavaScript, it's fully asynchronous and off-main-thread<br />
** For native code, use the [https://mxr.mozilla.org/mozilla-central/source/storage/public/mozIStorageAsyncConnection.idl async methods]<br />
* Model how large your DB will grow on desktop/mobile. Report the DB size & memory use to [https://developer.mozilla.org/en-US/docs/Performance/Adding_a_new_Telemetry_probe Telemetry] to confirm your estimates were accurate. '''Implement an expiration policy!'''<br />
* Profile your implementation and measure the number of SQL operations performed over the course of a typical session if it’s an “always-on” feature and you suspect it might cause performance issues<br />
* Don’t index on text fields, index on hashes of text fields. Indexes can become very large.<br />
* Don't use the default precision for timestamps (microseconds). Desktop apps are unlikely to ever require this level of precision<br />
* Maintain your DB with [https://sqlite.org/lang_vacuum.html VACUUMs] during Firefox version upgrades or during idle times. Run [http://www.sqlite.org/lang_analyze.html ANALYZE] periodically if necessary<br />
** You should have a vacuum plan from the get-go. '''This is a requirement for all new code'''.<br />
* If working on the same data from multiple connections, consider using an unshared [http://www.sqlite.org/sharedcache.html memory cache], to avoid memory contention. This will improve concurrency.<br />
** Though, it will also multiply the memory cache per the number of connection, so it's a memory/performance choice you should do carefully</div>PeterMortensen