Home  Beta programs 
  Welcome to Mobipocket Developer Center
powered by FreeFind

Creating Content

Getting Started
What is the Mobipocket file formatHow do I create a Mobipocket eBookStandard eBooksAdvanced eBooks
eBook features
Cross-platform feature supportImage supportTable supportCover PageParagraph rendering and hyphenationHyperlinksGuide itemsFramesIndexes and DictionariesSectionsAuthoring tips
Extended eBook features
DatabasesSQL queriesJavascriptHTML forms
Reference
Open-eBook HTML tagsSupported HTML entitiesHTML form tagsMobipocket custom tagsOPF x-metadata tagsMobipocket URLsMobipocket Document Object Model (DOM)Mobipocket Active Server Pages (ASP)Mobipocket Active Data Objects (ADO)Mobipocket Javascript Objects
Medical
Drug interaction module
Advanced topics
Setting margins
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.

[Object Database ]

function

return value

notes

db.OpenTable('tablename') a RecordSet object (may be undefined) opens a table in the database
db.CreateTable('tablename') an error message string or an empty string if successful creates a new table in the database
db.DropTable('tablename') an error message string or an empty string if successful deletes a whole table from the database
db.AddField('tablename', 'fieldname', FIELDTYPE) an error message string or an empty string if successful adds a column to a table
db.RemoveField('tablename', 'fieldname') an error message string or an empty string if successful removes a whole column from a table
db.Save() an error message string or an empty string if successful commits the changes to the database - nothing is stored to permanent storage before you call this function

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.

[Object RecordSet ]
function return value RW database RO database notes
rs.MoveFirst() none yes yes moves to the beginning of the RecordSet
rs.MoveLast() none yes not implemented moves to the end of the RecordSet
rs.MoveNext() none yes yes moves to the next line (next SQL result if the RecordSet has been obtained from an SQL request)
rs.MovePrevious() none yes yes moves to the previous line (previous SQL result if the RecordSet has been obtained from an SQL request)
rs.MoveRandom() none yes yes move to a random line (random SQL result if the RecordSet has been obtained from an SQL request)
rs.FindFirst('column', data) an error message string or an empty string if successful yes yes (but implemented only on 'orth' column) move to the first line where the contents of the field match 'data'. 'data' can be a string or an integer depending on the type of the column.
rs.FindNext() none yes yes move to the next line in the search started by FindFirst
rs.AddNew() none yes no add a new line to the current table
rs.Delete() none yes no delete the current line from the current table
rs.Fields('column') RecordSetField object (can be undefined) no yes access to multi-valued fields in RO databases. Data accessible here have been defined using the <idx:string> tag or the <idx:key> tag (provided the attribute "scriptable" has been set to "yes" ) in the source HTML. requires Reader 5 to access <idx:key> fields
rs.Infl('inflname') the inflected string no yes access to inflection data in RO databases. Inflection data are defined using the <idx:infl> tag in the source HTML. Warning: using the "infl" attribute only builds a dis-inflection database, not an inflections database accessible through the Infl() function.

The RecordSet object also has a few useful properties:

[Object RecordSet ]
property type RW database RO database notes
rs.BOF boolean yes yes true if the RecordSet is positioned before the first line
rs.EOF boolean yes yes true if the RecordSet is positioned after the last line
rs.language string no yes the language of the current table a RO database
rs.orth string no yes access to data indexed using the <idx:orth> tag. Only the first <idx:orth> in an <idx:entry> is accessible with this syntax for the time being.
rs.url string no yes access to a position defined using the <idx:entry> tag. The url returned includes the identification of the book. Use it with the window.open() function to jump to location that could be in a different book.
rs.anchor string no yes access to a position defined using the <idx:entry> tag. The url returned only includes the anchor part, not the identification of the book. Use it with the window.open() function to jump to a location inside of the same book.
rs.gramgrp string no not implemented access to data indexed using the <idx:gramgrp> tag. Not yet implemented.

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:

title author keyword
Red Mars Kim Stanley Robinson science-fiction Mars
Double homicide Jonathan Kellerman Faye Kellerman crime homicide murder
...    

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:

[Object RecordSetField]
property type RW database RO database notes
rsf.Count integer no yes the number of values in this field
rsf[n]   no yes returns a RecordSetProperty object.
If it is a string or an integer value, it can directly be converted to its actual content.
If it is a position, use .url or .anchor to retrieve a string suitable for the window.open function.
rsf.Value   no yes a shortcut for rsf[0]

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.

  Headwords Forms rules raw compression
(rules/forms*100)
English 66 093 91 185 465 0.51%
Spanish 42 143 379 200 2871 0.76%
French 33 587 283 743 1260 0.44%
German 55 785 203 278 1260 0.62%

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.

 

Copyright 2000-2007 Mobipocket.com