OPL's Database Management System

Summary: OPL is a language that has always placed great importance on its database functionality. OPL uses Symbian OS DBMS; a relational database management system which supports SQL (the Standard Query Language).

Contents

• 1. Compatibility with SIBO OPL
• 2. The database model
• 3. Views onto the database
• 4. Indexing the database
• 5. Database Keyword Changes
• 6. Acknowledgements

1. Compatibility with SIBO OPL

OPL provides more database functionality while maintaining compatibility with the SIBO (as used on the Psion Series 3, 3a, 3c, 3mx, Siena and WorkAbout) OPL methods of database programming. Existing code will not need to change except for a couple of minor details:

• SIBO OPL's RECSIZE and COMPRESS keywords have been dropped
• It is necessary to use transactions to obtain maximum update speeds
• Databases no longer compress automatically on closing (although this may be enabled with SETFLAGS)

Reason for these changes are explained Keyword Changes.

2. The database model

To explain the features of OPL's DBMS clearly, it is necessary to expand upon the terminology that is currently used in the SIBO OPL manual. What were known as data files are now known as databases. A database may contain one or more 'tables'. These tables, like data files in SIBO OPL, contain records which are made up of fields. Field names, as well as the table names, are now stored away.

Taking a look at a typical example of the SIBO OPL CREATE statement:

CREATE "Datafile",A,f1%,f2&

OPL would create a database called "datafile" and a table with the default name "table1" would be added to it. The field names are derived from f1% and f2& which we now call 'field handles'. SQL does not permit $, %, & in field names so $ is replaced by S, % by I and & by A (N.B. avoid calling a field I$ as this will become "IS", a word reserved by SQL which could cause you problems later). The type of the field, as with SIBO OPL, is got from these field handles.

In the following SIBO OPL OPEN statement:

OPEN "Datafile",A,f1%,f2&

OPL would open the default table, "table1", and provide access to as many fields as there are handles supplied. The names of the field handles is not important when OPENing, all that is required is that the types specified by these handles match the types of the fields.

With OPL it is possible to do something like this.

CREATE "Books FIELDS Author(40),Title,Price TO Fiction",G,a$,b$,d&

This will create a database called 'BOOKS' (or open it if it already exists) and insert a table called 'FICTION' (a trappable error would occur if such a table in ‘BOOKS’ already existed). The table will have fields called 'AUTHOR', 'TITLE', and 'PRICE', whose respective types are, as always, specified by the field handles ($ = string, &=long etc.). Author has a string length of 40 bytes and title has the default length of 255 bytes. Notice that the logical filename range has been expanded from A-D to A-Z and is more sensibly known (as we shall see) as the logical view name.

Here is an example of such a table:

Author	Title	Price
Dickens Charles	Oliver Twist	1799
Homer	Odyssey	999
Orwell George	Nineteen Eighty Four	899
Dickens Charles	Bleak House	1499
Conrad Joseph	Amy Foster	1299
Orwell George	Animal Farm	1149
Dickens Charles	Homage To Catalonia	1599

3. Views onto the database

It is possible to open multiple 'views' on a table and to specify which fields are to be available in a view.

OPEN "Books SELECT Title FROM Fiction",G,f1$

This statement would open a 'view' that would give you access to just the 'title' field from the 'fiction' table.

The view (blue text shows the information that is available)

The string from 'SELECT' onwards in the OPEN statement forms an SQL query which is passed straight on to the underlying Symbian OS DBMS. Other example SQL queries follow.

SELECT * FROM Fiction

The * is shorthand for "all the fields".

SELECT Title, Author FROM Fiction WHERE Title < 'B'

This is an example of an SQL WHERE clause, it contains a search condition to specify which records are wanted in the view.

The view: (blue text shows the information that is available)

SELECT * FROM Fiction ORDER BY Author ASC, Price DESC

This is a more advanced view, ordered by an index. The view would present the 'Author' fields in ascending alphabetical order (A through to Z) and, then, if any author fields matched the Price field (descending numerically) this field would be used to order these records. For this view to work, it would first be necessary to create a corresponding index.

The ordered view:

Author	Title	Price
Conrad Joseph	Amy Foster	1299
Dickens Charles	Oliver Twist	1799
Dickens Charles	Homage To Catalonia	1599
Dickens Charles	Bleak House	1499
Homer	Odyssey	999
Orwell George	Animal Farm	1149
Orwell George	Nineteen Eighty Four	899

SELECT * FROM Fiction WHERE Author < 'B' ORDER BY Author ASC, Title ASC

This example combines some of the above.

4. Indexing the database

The commands for creating indexes have bean supplied as language extensions via the OPX mechanism and can be found in "Dbase.oxh" as follows.

DECLARE OPX DBASE,KUidOpxDBase&,KOpxDBaseVersion%
     DbAddField:(keyPtr&,fieldName$,order&) : 1
     DbAddFieldTrunc:(keyPtr&,fieldName$,order&,trunc&) : 2
     DbCreateIndex:(index$,keyPtr&,dbase$,table$) : 3
     DbDeleteKey:(keyPtr&) : 4
     DbDropIndex:(index$,dbase$,table$) : 5
     DbIsUnique&:(keyPtr&) : 10
     DbMakeUnique:(keyPtr&) : 11
     DbNewKey&: : 12
     DbSetComparison:(KeyPtr&,comp&) : 14
END DECLARE

To use any of these procedures you would need the following statement at the very top of every OPL module that requires them:

INCLUDE "Dbase.oxh"

All the procedures in this OPX begin with "dB". This is for neatness, identity and to reduce the likelihood of the names clashing with other procedures. Although OPX procedures look like ordinary user-defined procedures they are as fast to call as the language keywords.

DBCREATEINDEX takes a name for the index so that it may one day be dropped from the database. It also takes the name of the database and table on which the index is to be be built and a key which specifies how the index is to be ordered. This key will first need to be built.

DBEWKEY returns a pointer to a key object. The procedures that require a key, like DBCREATEINDEX, will check to make sure that the pointer is valid and prevent any invalid accessing of memory.

DbADDFIELD:(key&, field$, order&)
This procedure adds a field-name to the key, key& (the pointer returned by DBNEWKEY). The first field added to a key is the most significant in the ordering of the records. If any of these fields then match the second field added to the key, this key would be used and so on. Order& specifies whether the field should be ordered alphanumerically forwards (order&=1) or reverse (order&=0).

DbADDFIELDTRUNC:(key&, field$, order&,trunc&)
This procedure is the same as DbADDFIELD except it also takes a truncation value trunc&. This is used to truncate string fields during the ordering process. i.e. if this value was "1" then only the first letter of the field would be used to order the records. N.B. This procedure must be used when ordering string fields that were originally created with the default length of 255 bytes, i.e. the string length was not specified in the CREATE statement. The maximum length that can be used is 240. If a string field is not truncated the DBMS will attempt to use its full length, giving an error for non truncated fields of length in excess of 240 bytes.

DbMAKEUNIQE:(key&)
Using this procedure on a key will cause any subsequent index created with that key to be unique. A view, ordered by a unique index won't allow any changes to the database which would result in exactly matching records when ordered by that index. This is useful for preventing any unwanted repetition of records.

5. Database Keyword Changes

COMPACT replaces COMPRESS
This is because in maintaining compatibility COMPRESS would need to copy the DATABASE to another file and if the target file already existed then the records would be appended. However, OPL can have multiple tables in a database file so it would be ambiguous to emulate this behavior. OPL's new COMPACT keyword can compress a database in place without needing to copy it elsewhere.

RECSIZE has been dropped because OPL places no restriction on the size of a record, making this keyword redundant

INSERT/ MODIFY, PUT / CANCEL
These new keywords allow a database to be updated without having to move records to the end of the table. An update is commenced with modify or INSERT and ended with PUT or CANCEL.

DELETE dbase$, table$
Deletes the table,table$, from the database, dbase$.

BOOKMARK, GOTOMARK, KILLMARK
Bookmarks provide a method for fast record access and should also be used instead of POSITION when using multiple views. BOOKMARK returns a bookmark for the current record, GOTOMARK takes a bookmark and makes the record that corresponds to that bookmark the current record. KILLMARK takes a bookmark and removes that bookmark from the database.

BEGINTRANS, COMMITTRANS, ROLLBACK, INTRANS
Transactions allow changes to a database to be committed in stages.

BEGINTRANS begins a transaction, COMMITTRANS commits the transaction, ROLLBACK cancels the transaction and INTRANS tells you whether or not the current logical view is in a transaction. Transactions should be used in OPL to obtain optimum update speeds.

In the following example the 100 appends are done in a single transaction.

LOCAL i%
CREATE"Database",Z,f1&
BEGINTRANS
DO
    z.f1&=i%
    APPEND
    i%=i%+1
UNTIL i%=99
COMMITTRANS

Another advantage of transactions is atomicity. You can ensure that changes are only made to a database if all the changes that you desire can be made. In the following example view G and view H are only updated if both views can be updated.

OPEN"Staff SELECT * FROM Id",G,Name$,Id&
OPEN"Staff SELECT * FROM Salary",H,Id&,wage&
USE H
BEGINTRANS
USE G
BEGINTRANS
INSERT
INPUT G.Nam$
INPUT G.Id&
PUT
USE H
INSERT
INPUT H.Id&
INPUT H.wage&
PUT
USE H
COMMITTRANS
USE G
COMMITTRANS

6. Acknowledgements

Symbian is a trademark of Symbian Ltd.

The Symbian Developer Network logo is a trademark of Symbian Ltd.

This paper refers to trademarks of third parties and Symbian recognizes their rights.