Home | Hide TOC | Download Sample | Add to Favorites

updated: 2005-01-21

Databases, Recordsets and Fields
in the Mobipocket Reader

There are two kinds of databases:

• Read-only (RO) databases compiled from tagged elements in the input HTML.
• Read-write (RW) databases that can be created using JavaScript in the Reader.

---

Content

---

How to access a database
How to modify the structure of a RW Database
Manipulating data: the RecordSet object
What are multi-valued fields
What is an inflections database

---

Objects

---

[Object Database ]
[Object RecordSet ]
[Object RecordSetField]

---

1 How to access a database

• OpenDatabase('name') opens or creates a named RW database.
  The return value is a Database object (may be undefined).
• sql_execute('sql') accesses the RO database embedded in a book.
  The return value is a RecordSet object.

example:

var db = OpenDatabase('flowers');
if (typeof db == 'undefined')
   -- handle error case --
else
   -- proceed --

There is no "CloseDatabase" function. A database is closed automatically when no Database object points to it anymore.

2 How to modify the structure of a RW Database

This is obviously possible for RW databases only. The Database object has several functions for creating and deleting tables and columns.

possible FIELDTYPE values:

• TYPE_STRING: a string
• TYPE_POOLED_STRING: a string which will be stored into a string pool. Use this if you are reusing a small quantity of different strings multiple times.
• TYPE_UBYTE: unsigned byte
• TYPE_UINT: unsigned 32-bit integer

Warning: modifying the structure of a table in a RW database is only possible if the table is not locked by a RecordSet pointing to it. The OpenTable function locks a table. There is no "CloseTable" function however. The table is unlocked automatically when no more RecordSets pointing to it exist.

example:

var db = OpenDtabase('flowers');
// to lock a table
var rs = db.OpenTable('myfavoriteflowers');
// to unlock it
rs = null;

3 Manipulating data: the RecordSet object

The RecordSet object is used in both RW and RO databases to iterate through the lines and columns of a table in a database. On RW databases, it can be obtained from the Database object by calling the OpenTable function. You use the sql_execute('sql') global function to get a RecordSet on a table embedded in a book.

It is also possible to get a RecordSet from the RO database of the current book by calling the current_index_entry('tablename') global function. It returns a RecordSet pointing to the specified table and positioned on the line matching the portion of HTML currently displayed. Data in the source HTML must be tagged using the <idx:entry scriptable="yes" name="tablename"> or <idx:ext-subentry name="fieldname" extends="id" scriptable="yes"> tags. The scriptable attribute is very important because it indicates to the compiler to generate the reverse access tables used for retrieving  the current entry from the current position in the HTML.

Example:
The Sample 'Multi Level Nav with Index' gives an example of navigation in a frameset with three levels of drop-down lists, and how to use the current_index_entry function to set the selected item in the list when coming from an index lookup.

Several functions are provided for manipulating RecordSets. Most of them work on RecordSets coming from RW and from RO databases although there are exceptions.

The RecordSet object also has a few useful properties:

To access positions defined by <idx:subentry> and <idx:ext-subentry> tags in a RO database, the Fields function must be used.

4 What are multi-valued fields (in a RO database)

4.1 the theory

Multi-valued fields are a natural extension to traditional database constructs which becomes obvious when you look at how XML data are structured. Let's take sample data organised in a XML structure (warning: the syntax used in this example is NOT the syntax understood by the Mobipocket book compiler):

<book-database>
   <book>
      <title>Red Mars</title>
      <author>Kim Stanley Robinson</author>
      <keyword>science-fiction</keyword>
      <keyword>Mars</keyword>
   </book>
   <book>
      <title>Double homicide</title>
      <author>Jonathan Kellerman</author>
      <author>Faye Kellerman</author>
      <keyword>crime</keyword>
      <keyword>homicide</keyword>
      <keyword>murder</keyword>
   </book>
</book-database>

Now the table structure that simply and naturally describes these data is the following one:

And to retrieve all the books about crime, you want to write this simple request:

SELECT * FROM 'book-database' WHERE keyword='crime'

In a usual database, the example above cannot be implemented using a single table. At least three tables are necessary with relations between them: a table of books, one of authors and one of keywords. In Mobipocket though, the above construct is possible thanks to the support of multi-valued fields.

4.2 access to multi-valued fields in Javascript

Imagine we have a RecordSet in variable rs pointing to a RO database table. You usually access field values in the current line as properties of the RecordSet object.

example:

var rs = sql_execute('--some valid sql --');
// access to a standard mobipocket field of type string
var headword = rs.orth;
// access to the position of an entry and a jump to it
var url = rs.url;
window.open(url);
// access to a user-defined value in column 'mycolumn'
var value = rs.mycolumn;

If you want to retrieve values from multi-valued fields, you have to use the Fields('mycolumn') function. It returns an object of type RecordSetField which is a table and has the following properties:

example:

var rs = sql_execute('--some valid sql --');
// access to a custom multivalued string field defined using <idx:string name="mycolumn">
var rsf = rs.Fields('mycolumn');
var message;
for (i=0; i<rsf.Count; i++)
    message = message + rsf[i];
// access to a position defined using an <idx:subentry name="mysubentry">
rsf = rs.Fields('mysubentry');
var anchor = rsf.Value.anchor;
// which is equivalent to
anchor = rsf[0].anchor;
// note that rsf.anchor is not a valid statement
// jump to the anchor
window.open(anchor);

Please notice that sequential access to multi-valued fields is optimised. Therefore, it is a good idea to take a reference on a RecordSetField before iterating on it.

example:

// the following iteration is of complexity n
var rsf = rs.Fields('mycolumn');
for (i=0; i<rsf.Count; i++)
    message = message + rsf[i];
// but this one is of complexity n**2 because the RecordSetField is recreated
// on each call of the Fields function and has to iterate through all its
// values starting from 0 before reaching the requested one.
for (i=0; i<rs.Fields('mycolumn').Count; i++)
    message = message + rs.Fields('mycolumn')[i];

5 What is an inflections database (in a RO database)

Inflections are stored using a special linguistic compression algorithm based on the Levenshtein's edit distance algorithm described here: http://www.merriampark.com/ld.htm. For a set of root forms and their corresponding inflected forms, the algorithm constructs a nearly-minimal set of rules representing the transformations needed to inflect a root form or disinflect an inflected form.

For example example: the german root form "bleiben" and the inflected form "geblieben" generate the following rule: "*ge3+ei-ie" which can be applied to "bleiben" to retrieve "geblieben" or to "geblieben" to retrieve "bleiben". The explanation of the syntax of the rule is beyond the scope of this document.

The algorithm used is fairly efficient as can be seen in the following table. It typically generates a rule for every 200 inflected forms.

Inflections are specified in the source HTML using the <idx:infl> and <idx:iform> tags. In Mobipocket syntax, you can also specify inflections using the "infl" attribute but then, only a dis-inflection database is build using a different algorithm. Inflections defined in that way cannot be retrieved with the Infl() function.