EPOC   SDK Home Glossary Indexes Previous Next Up

About DBMS in EPOC Release 5


Contents


Sharing databases

Section Contents


Client-server DBMS

EPOC Release 5 introduces a client-server interface to DBMS.

A session with the DBMS server provides the basis for sharing databases with other threads; the DBMS server allows shared read/write access to databases by multiple clients.

A session with the DBMS server is set up through an instance of the new RDbs class. The behaviour of this class is similar to other session classes, for example, the file server session class, RFs.

A client opens a connection with the DBMS server using the Connect() member function of RDbs and closes the connection by calling Close() on RDbs.

Some useful points to note:

Shared databases support all DBMS API functionality except database creation and changing the security key:

The client server interface offers utility functions which allow the client to check the number of allocated DBMS objects.

The interface also offers heap debugging support for the server's heap.


Named databases

In ER5, in order to support a client-server interface, there is a new mechanism for creating a database object.

A new class, RDbNamedDatabase provides the interface for creating and opening databases identified by name.

The new mechanism is generic; the database is identified by its name and the database ‘format’. The format is a name which identifies the database implementation used for creating or opening that database. The default format, which can also be named as “epoc”, is a StoreDatabase in the root stream of the named file, but other formats can be provided in extension DBMS driver libraries, if required (these do not have to be file-based). Additional DBMS driver libraries are dynamically bound to the API at run-time, if one can be found to support the format requested.

Formats which are UidTyped files (such as the default) can allow the client to specify a 3rd Uid by extending the format name with the Uid name (as generated by the Name() member function of TUid, an 8 digit hexadecimal number enclosed in square brackets).

If specified, this is also checked when a database is opened. e.g. creating a FileStore database “C:\My Database” with a 3rd Uid of 0x10001234 would look something like

    _LIT(KMydatabase,"C:\\My Database");
    _LIT(KDemoUid,"[10001234]");
    RDbNamedDatabase database;
    TInt r=database.Create(fs,KMydatabase,KDemoUid);

For comparison, the equivalent code required to create an RDbStoreDatabase in the root of a CPermanentFileStore would have been:

    _LIT(KMydatabase,"C:\\My Database");
    CFileStore* fstore=CPermanentFileStore::ReplaceLC(fs,KMydatabase,EFileWrite);
    fstore->SetTypeL(TUidType(fstore->Layout(),KDatabaseUid,TUid::Uid(0x10001234)));
    RDbStoreDatabase database;
    fstore->SetRootL(database.CreateL(fstore));
    fstore->CommitL();

A named database may be encrypted; the default is an un-encrypted database.


Access modes

A named database supports three access modes:

The first two access modes are implemented through a file server session which provides the necessary file access modes while shared read/write access must be done through the DBMS server.

Creating a database through the Create() and Replace() member functions of RDbNamedDatabase, initiates client-side access to the database and does not require a connection to the DBMS server.

RDbNamedDatabase offers two Open() functions, one for client-side access and the other for client-server access. Client-side access is preferred over client-server access as data retrieval is faster than indirecting via the DBMS server.


Change notification

In ER5, clients can be notified of changes to a database through an RDbNotifier object. This class is new in ER5 and is particularly useful for shared databases.

A client makes an asynchronous request for notification and is signalled when:

Any number of notifiers can be opened by a client on the same database and they all act identically.

The lifetime of a notifier object is independent of the database that it is observing. For example, a client can close a database before closing a notifier which is observing that database. In this case, the notifier continues to report all activity on the database caused by other clients. Once the last client has closed the database, the notifier reports the status RDbNotifier::EClose; any further requests for change notification complete immediately with the same status, i.e. RDbNotifier::EClose.

Notifiers cannot be opened on client-side databases.

Only one request can be outstanding on a single notifier at any one time. If further database events occur while a client is handling the completion of a previous database event, the notifier remembers the most significant event; this is determined as:

Read-locks removed events (RDbNotifier::EUnlock) are not remembered in this way.


Transactions and locks

Transactions in exclusive client-side access behave exactly as they did before ER5; see transactions before ER5.

When multiple clients can access the same database, transactions ensure that only one client can change data at a time.

DBMS transactions do not provide any form of isolation between clients; while one client is updating a table within a transaction, other clients can see the changes as they are made. For example, if a client retrieves two separate rows from a database, there is no automatic guarantee that the data being retrieved has not been changed between the reads; this can lead to an ‘inconsistent read’. A client can prevent an update while retrieving related rows by enclosing the individual reads within a transaction. Such a transaction does not modify the database and only operates as a read-lock; the Commit() or Rollback() member functions of the abstract base class RDbDatabase releases such a lock and does not affect the database in any way.

In practice:

Sharing read-locks enables greater concurrency while providing some safe guard against inconsistent reads. However, there is the possibility of a deadlock occurring. If two clients want to update a database and both Begin() a transaction before either of them starts an update, then one client's read-lock will prevent the other from upgrading to a write lock and vice versa. The only way out of this is to code the clients in such a way as to back out of such a deadlock situation, rather than retry forever without releasing the locks.

Prior to ER5, RDbStoreDatabase prevented a single transaction from containing both Data modification and Data definition statements; in ER5, this constraint has been removed by ensuring that all affected cursors are invalidated when the definition of their underlying table is changed. Such a cursor reports a KErrDisconnected error in this state and can only be closed and the rowset re-generated once the DDL (SQL schema update) statement has completed. Calling Reset() on such a cursor has no effect. It is still impossible to update rows while changing the schema.

A client can change the database schema while other clients are using that database provided that they have no locks on it. However, those other clients may find that their rowsets are invalidated asynchronously.


Database compaction

When a File-store database is constructed using the new RDbNamedDatabase interface, there is no access to the underlying store in order to compact it and recover any free space for re-use.

In ER5 the RDbDatabase abstract base class supports a compaction interface, the Compact() member function. The class also offers a new complementary interface, the UpdateStats() and Size() member functions, which calculate and report size and usage information.

Incremental forms of these functions are also offered by the RDbIncremental class in ER5.


Enhanced SQL

Section Contents

In ER5, the SQL grammar understood by DBMS has been extended to support various DDL (Schema update) statements and DML (Data update) statements.

The SQL engine has also undergone major enhancements, and is now capable of evaluating queries in various ways, providing significant performance improvements for SQL query parsing and evaluation.

The requirement that SQL keywords do not appear as table, index or column names in SQL statements and queries has been removed.


Data definition SQL (DDL)

The DDL statements are those which modify the database definition, i.e. the CREATE, ALTER and DROP statements. These are equivalent to using the functional interface provided by the RDbDatabase and RDbIncremental classes for the corresponding operation; however, the SQL statements may not provide the full flexibility of the functional API. For example, creating a table using the functional API might look like (no error handling shown):

    _LIT(KId,"Id");
    _LIT(KSupplierName,"SupplierName");
    _LIT(KSuppliers,"Suppliers");
    ...
    CDbColSet* colset=CDbColSet::NewL();
    TDbCol id(KId,EDbColInt32);
    id.iAttributes=id.EAutoIncrement;
    colset->AddL(id);
    colset->AddL(TDbCol(KSupplierName,EDbColText,30));
    database.CreateTable(KSuppliers,*colset);
    delete colset;

This can be written as an SQL statement like this:

    _LIT(KSQLText,"CREATE TABLE Suppliers (Id COUNTER, SupplierName CHAR(30)");
    ...
    database.Execute(KText);

Data modification SQL (DML)

The DML statements are those which modify the data content of the database, i.e. the INSERT, UPDATE and DELETE statements. These update SQL statements are effectively run within a transaction and are, therefore, atomic; either they complete fully or no changes are made to the database. There is no immediate functional equivalent provided by DBMS, though these operations can also be performed using an RDbView object with a suitable query and update code. Some examples (no error handling shown):

Inserting a row using the functional API:
    _LIT(KSelect,"SELECT SupplierName from Suppliers");
    _LIT(KSymLim,"Symbian Limited");
    ...
    RDbView view;
    view.Prepare(database,KSelect,view.EInsertOnly);
    view.InsertL();
    view.SetColL(1,KSymLim);
    view.PutL();
    view.Close();

The same update using SQL:

    _LIT(KSQLText,"INSERT INTO Suppliers (SupplierName) VALUES (‘Symbian Limited’)");
    ...
    database.Execute(KSQLText);
Deleting rows using the functional API:
    _LIT(KSelect,"SELECT SupplierName from Suppliers WHERE SupplierName=‘Symbian Limited’");
    ...
    database.Begin();
    RDbView view;
    view.Prepare(database,KSelect);
    view.EvaluateAll();
    while (view.NextL())
          view.DeleteL();
    view.Close(); 
    database.Commit();

The same using SQL:

    _LIT(KSQLText,"DELETE FROM Suppliers WHERE SupplierName=‘Symbian Limited’");
    ...
    database.Execute(KSQLText);

For UPDATE and DELETE statements where many rows are affected, the SQL forms can be more efficient than the equivalent C++ functional form; however, INSERT statements are significantly less efficient than the equivalent C++ code.


Executing SQL statements

In ER5, the RDbDatabase class provides a new member function, Execute(), which directly executes both DDL and DML statements on the database.

As most of these statements can take an extended time to complete, there are also new interfaces to execute these statements incrementally.

A different interface is needed for DML statements because it is not always possible to know how many steps a DML operation is going to need.

The following code fragment illustrates the incremental execution of a DDL statement. In practice, this is no better than direct execution but shows the 'end conditions' for running RDbIncremental.

    _LIT(KTxt,"DROP TABLE Suppliers");
    ...
    RDbIncremental op;
    TInt step;
    TInt r=op.Execute(database,KTxt,step);
    while (step>0 && r==KErrNone)
          r=op.Next(step);
    op.Close();
    ...
    // r has the error code from the operation

The following code fragment is an example of driving the incremental execution of a DML statement to completion.

    _LIT(KTxt,"DELETE FROM Suppliers");
    ...
    RDbUpdate update;
    TInt r=update.Execute(database,KTxt);
    while (r>0)
          r=op.Next();
    op.Close();
    ...
    // r has the error code from the operation
    // update.RowCount() returns the number of rows deleted, if successful

Query evaluation

There are two changes to the query evaluator which may allow queries to be optimised and will have a significant effect on database design and use:

Both evaluation schemes require the full pre-evaluation of the query and, thus, a full pre-evaluation window. The optimiser provides such a view even when a client requests the default “no window”. This is a change in behaviour. Prior to ER5, the window parameter request was followed precisely; in ER5, this parameter is considered to be a minimum specification so that if a window is requested, it is definitely provided.

To take advantage of these changes, a different way of designing a database is needed - specifically in the indexes that are created. Prior to ER5, indexes were needed to support ordering and, unless explicit use was made of RDbTable::SeekL(), they were of no use for look-up purposes.

In ER5, ordering behaviour is often more efficiently provided without using an index (both in terms of memory and performance); however, look-up can be extremely fast with an appropriate index.

For example, in the following SQL query:

    SELECT * FROM Parts WHERE SupplierId=1234 ORDER BY Name

Given a Parts table containing 1000 rows, a result set of 100 rows evaluates 7 times faster in ER5 compared to ER4; a result set of only 10 rows evaluates 60 times faster in ER5 compared to ER4. Removing the text column index also saves some 40K of disk space.

EPOC       SDK Home Glossary Indexes Previous Next Up