|Home Beta programs|
|Welcome to Mobipocket Developer Center|
SQL user's guide
The main purpose of SQL queries in the mobipocket Reader is to display interactive lists of elements chosen using complex criteria. The display is similar to the index view used for indexes but search expressions can be much more complex. For example, you can request all books written by a given author which are not about religion. Moreover, SQL expressions can also be used to visually mark queried elements if they match an additional query. In the previous example, you could this time display all the books from that author but mark those about religion with a bullet. User interaction is supported as well. Queries can be constructed in such a way as to prompt the user for one or more search keys and then display the results.
A sample screenshot (link to full sample at the top of this page):
Data can be defined in the html content by using the indexing tags <idx:entry>, <idx:orth>, <idx:key>. For a full refence of indexing tags, see the indexing article or the reference documentation. Starting from reader 4.8, additionnal columns can also be deined using the <idx:string>, <idx:subentry> and <idx:ext-subentry> tags. However, Mobipocket SQL can only test for the presence or absence of a value in these fields using the the IS NULL operator.
Important: when using SQL, all entries and keys must be named using the "name" attribute on the <idx:entry> and <idx:key> tags.
The table structure accessible to SQL queries is as follows:
The first column (defined by an idx:orth) is then regarded as the primary key, columns defined by idx:key as foreign keys and all other columns as regular data. This means that only data from idx:orth or idx:key columns are indexed and can therefore be used to retrieve table lines in an efficient way. Trying to match a string on a non indexed column (for example an <idx:string> column) would be so slow that it is not allowed in Mobipocket Reader.
Let's try to picture the table structure using an example:
The following content excerpt:
A request will always start with "SELECT * FROM tablename". However, because of the user interface design on PDAs, only the first column (the primary key) of the table will be displayed in the index view of the Mobipocket Reader. Please notice that all tables used in the request have to be declared in the FROM statement (separated by commas).
Then follows an expression introduced by the WHERE keyword. The expression can use AND, OR, NOT and IS NULL (from Reader 4.8) operators. Comparisons use the = (equal) and <> (diffent) operators. String constants are written using single quotes. Operator precedence can be specified using parentheses.
The ? (question) operator can be used in place of a litteral constant to request user input (using the index view of the specified column).
Examples (see sample data table above):
SELECT * FROM books WHERE Category='Religion' Result: Smalcald
SELECT * FROM books WHERE price IS NOT NULL Result: The Grin Bin, The Grand Alliance requires Reader 4.8
SELECT * FROM books WHERE orth ANAGRAM_OF 'The Nigri Ben%1' Result: The Grin Bin (on character has been discarded, the 'e' in 'Ben') requires Reader 4.8
In short, supported satandard SQL keywords and operators are:
Non-standatrd operators and keywords are:
Supported comparison operations are:
and their opposite operations either using the oposite operator (<> instead of =) or using the NOT logical operator.
Unsupported comparison operations are:
The functions allow you to specify a compound caption string for all screens the query will display to the user. Strings to be displayed in the different screens are separated by %%% (three consecutive percent characters).
For example, the string "caption 1 %%% caption 2 %%% caption 3" will display "caption 1" on top of the first screen (corresponding to the first ? (question) in the query, "caption 2" on top of the second screen (second ? in the query) and "caption 3" on top of the last screen containig the final results of the request. You should always have as many caption strings as ? operators in the request plus one for the final results.
It can be useful to remind the user of the items he/she clicked on in previous screens. For this purpose, you can use the %key string to insert into the caption the item name selected by the user in the previous screen. You can also use %1, %2, %3,... and so on to insert the item name chosen by the user in any previous screen (numbered from 1).
The sql_bullet_search function is used to display al list of query results where items matching a complementary request are marked by a bullet. Four kinds of bullets are available. They can be used using the following identifiers:
Examples (see sample data table above):sql_search('SELECT * FROM Books WHERE Category=?', 'Please chose a category:%%%Books in category %key are:')
This will display in the first screen:
Assuming the user clicks on 'Cartoon', the second screen will look like this:
This will display:
If the second argument is changed to "true", only buletted entries are displayed. You can leave this setting to the user by passing the value of a checkbox as the second parameter.sql_bullet_search(BUL_BLUE, true, 'SELECT * FROM Books, 'Category<>\'Religion\'')
The following example retrieves all the books from the table and displays their titles in an alert box:var records = sql_execute('SELECT * FROM books');
var results = ' - ';
results = results + records.orth + ' - ';
The resulting display will be:
Example:sql_search('SELECT * FROM books', 'my caption string', 'my frameset', 'f_my_callback');
The callback could be (this example jumps to the entry following the one selected bu the user):function f_my_callback(results)
It is possible to fill a list box or a combo box in an HTML form from a database table. Use the <SELECT src="sql: your query here"> syntax.
Example:<SELECT name="all" src="select * from books" size="5"/>
The output in a Mobipocket ebook will be visually similar to what you get in a web browser:
However, in Mobipocket, the widget will work even if the list contains tens of thousands of items. The user interface is also optimized to handle very large collections. For example, when you scroll the list, the scrolling speed accelerates so that scrolling through any number of items will never take more than a few seconds.
Tables from external document can also be accessed from SQL queries. They are accessed by their URL. However, they have to be renamed in the FROM part of the request in order to be used.
Example:SELECT*FROMbooks,'oeb:redirect?title=Exlusion_List$table=banned_books' AS banned
WHERE books <> banned
In this example, the URL of the external table (in single quotes) is renamed as "banned" which is then used in the query. The resulting list of books will not contain all the books contained int the "banned_books" table in the ebook identified by "Exclusion_List".
Here is the explanation of all the parts of the URL used in the example above:
This finds in the books visible from the library of the Mobipcket Reader the book identified by "Exclusion_List". Book identifiers are derived from the book title and are given by prcgen (the book compiler) at the end of each build.
The $ (dollar) sign is used to access objects embedded within a document. Here, the type of the object is "table" and its name is "banned_books"
Important: For technical reasons too complex to be explained here, database columns declared with <idx:key> must also be defined by their URL and renamed in the FROM statement of the SQL query if they are in a different ebook. This shortcomming will be fixed in future a version of Mobipocket reader. Here is the syntax for performing a filtered search from a book that does not conatain the database:SELECT * FROM 'oeb:redirect?title=my_external_data$table=books' AS books, 'oeb:redirect?title=my_external_data$table=author' AS author WHERE author = 'Mark Lynch'
5 LIKE and ANAGRAM_OF operators require Reader 4.8
LIKE operator: use wild="yes" in your data
In order to use the LIKE operator in SQL queries, the data table must be optimized. Since these optimisations have an impact on data size, they are not performed by default. You have to ask for them specifcally when defining index entries with the wild="yes" attribute (wild stands for wildcard). If you enable spell correction using the spell="yes" attribute, LIKE optimizations will be turned on automatically because the spell correction algorithm needs them. Here is an example index entry definition with LIKE optimisations:
LIKE operator syntax
The LIKE operator allows you to use wildcards in string comparisons:
Example (see data above):SELECT * FROM books WHERE orth LIKE 'The%'
will return 'The Grin Bin' and 'The Grand Alliance'
ANAGRAM_OF operator syntax
The ANAGRAM_OF operator finds anagrams in the database. You specify letters to use to form the anagram and a the maximal number of those letters the algorithm can discard. Both parameters form a single string and are separated by a % sign.
Example (see data above):
SELECT * FROM books WHERE orth ANAGRAM_OF 'The Nigri Ben%1'
returns 'The Grin Bin'. One character has been discarded, the 'e' in 'Ben'
© Copyright 2000-2007 Mobipocket.com