Templates may use a datasource that is retrieved from an Sqlite database, such as those used by the mozStorage API. This allows information from the database to be used to generate XUL content. To do this, set the querytype attribute on the root node of the template to the value storage. This will cause the datasource to be treated as an Sqlite database.
The datasources attribute may be set to one of two possible types of values. First, it may a file URL pointing to a file on disk. In this case, you will often be setting the datasources attribute dynamically after determining the file path. (If you intend to set it by script, you must set datasources in the XUL to rdf:null). The second method involves using a special url form:
profile:filename.sqlite
This form with the 'profile' prefix is used to refer to files in the profile directory. This is useful for user data, and is suitable as you do not need to calculate where this directory is located. The file within this directory is specified after the colon, in this case 'filename.sqlite'. Sqlite files typically have the 'sqlite' extension.
As with XML datasources, the ref attribute isn't currently used for Sqlite sources, so you should just set the ref attribute to a dummy value; '*' is typically used.
The query for an Sqlite datasource is just an SQL select statement, as text inside the query element. This simple example shows how to display a listbox containing the values in one column in a database table.
<listbox datasources="profile:userdata.sqlite" ref="*" querytype="storage">
<template>
<query>
select name from myfriends
</query>
<action>
<listitem uri="?" label="?name"/>
</action>
</template>
</listbox>
The query returns the list of values from the 'name' column in the table 'myfriends'. The action body, starting at the node with uri="?" is repeated for each result returned from the query. If the query returns six results, six listitems will be generated. The labels have the special value '?name'. The syntax with the question mark is similar as with the other query types. It means, substitute the attribute value with the value of a column from the result. In this case, '?name' means replace this with the value of the name column. The effect is a listbox containing the names from the database.
You can return multiple columns or filtered data by using a more complex query.
<vbox datasources="profile:userdata.sqlite" ref="*" querytype="storage">
<template>
<query>
select name, email from myfriends where age >= 30
</query>
<action>
<hbox uri="?">
<label value="?name"/>
<label value="?email"/>
</hbox>
</action>
</template>
</vbox>
This template displays the results of two columns. Any occurance of a question mark in the action body is replaced with the value from the query results. Here, this is used two times, once to display the name and the second to display the email. The uri attribute on the hbox element is required on one element within the action body. For Sqlite queries it will almost always be on the outermost node.
Note also that the query statement has a where clause which restricts the rows to those with an age greater or equal to 30.
Query Parameters
Sometimes, you will want to adjust the query depending on certain criteria. One possibility is to simply use methods to adjust the text within the query element and rebuild the template. However, this can become unweidly for more complex queries, and would be more error prone if values were not escaped properly to ensure that extraneous quotes, semicolons and other characters didn't appear in the values. This is especially important when you are taking values that were entered by the user.
The solution is to use query parameters, using the param element.
<query> select name, email from myfriends where gender = :wantedgender <param name="wantedgender">male</param> </query>
In this example, a parameter 'wantedgender' is used. This is a param element which should be used as a direct child of the query element. The name attribute is used to specify the parameter name, and the contents of the param element specify the value to use. When the name appears in the query, preceded by a colon, as ':wantedgender' does in the example above, the value of that parameter will be filled in to the query. The resulting query that gets used will be:
select name, email from myfriends where gender = 'male'
This technique is used as quotes or other special characters are handled automatically. If a quote appears in the value, it will escaped as needed. Now, to adjust the query to something else, say to look up females, all you need to do is get a reference to the param element and adjust the contents. You would likely want to add an id to the param element so that the getElementById method may be used to refer to it.
You can add as many parameters as necessary for the query. The example above used a string parameter. If you want a numeric parameter, you should also set the type attribute.
<listbox id="friends" datasources="profile:userdata.sqlite" ref="*" querytype="storage">
<template>
<query>
select name, from myfriends where age >= :minage && age <= :maxage
<param id="minage" name="minage" type="integer">30</param>
<param id="maxage" name="maxage" type="integer">40</param>
</query>
<action>
<listitem uri="?" label="?name"/>
</action>
</template>
</listbox>
Here, the age is an integer number, so the type attribute is set to integer. Integers are 32-bit values, but you can use the value int64 for 64-bit integers. You can also use the value double for double precision floating point values. If no type is specified, a string is assumed.
We could later change the age to use in the query with a short script:
function adjustAge(min, max)
{
document.getElementById("minage").textContent = min;
document.getElementById("maxage").textContent = max;
document.getElementById("friends").builder.rebuild();
}
This function takes two arguments, the minimum and maximum values to use. We retrieve the param elements and set their textContent properties to the desired value. Finally, we rebuild the entire template.