SQLite implementation for Symbian OS with an ADO.NET Provider for Red Five Labs’ .NET Compact Framework 1.0

Converting SQLite to S60

Red Five Labs’ Net60, a .NET Compact Framework Version 1.0, introduces .NET programming to the great family of Symbian S60 smartphones. Now it is possible to run a managed .NET application, written for Windows Mobile, on S60 smartphones. Unfortunately, however, the .NET Compact Framework 1.0 does not provide any form of database support. This is first supported in the .NET Compact Framework 2.0 together with the SQL Server Compact Edition. This SQL Server only runs on Windows Mobile devices which means another database system is needed to provide database functionality to Symbian devices. Enter SQLite for S60.

SQLite is a popular open source, stand-alone database system that was written in C and is available for Windows, Windows Mobile, OS2 and Unix operating systems. It is ACID compliant and supports the SQL-92 standard. The Symbian OS at present does not provide a database system, however in the future Symbian OS Version 9.4 will support a SQLite implementation.

PIPS for S60 made porting SQLite to Symbian OS possible

At the beginning of 2007 Symbian published the P.I.P.S. SDK (http://developer.symbian.com/wiki/display/oe/P.I.P.S.+Home ) for the S60 and UIQ platforms. P.I.P.S. is the implementation of the POSIX Interface for Symbian OS that provides C Libraries like STDIO or STDLIB. With the Open C SDK it is possible to port the SQLite library written in ANSI C to Symbian OS. To do such a conversion of an existing ANSI C project knowledge of programming for Symbian OS is needed. A good description of the conversion of SQLite to S60 is the article on Dr.Dobbs (http://www.ddj.com/mobile/198702204?pgno=2) and describes how such a port of SQLite could be done. By following these instructions, it was easy to create a project that could compile the C files of the SQLite project.

Note, the PIPS SDK for S60 must be installed to the PC to compile the SqliteS60 project whilst the PIPS_s60_1_2_SS .SIS must be installed on the device.

Converting SQLite .C files to .CPP files

Before the SQLite source could be completely compiled, the file IO operations had to be rewritten. This part of SQLite is not platform independent and uses the native OS operations to open, read and write files. The first step was to rewrite the file operations with the STDIO implementation. For example the OpenDatabase method was modified to use the fopen method of POSIX. Unfortunately this did not result in the outcome that was expected. On the test device this implementation does not run. After analyzing this, it seems to be that the ANSI C file IO methods cause the error. The Symbian API is an object oriented programming interface, therefore to use the native file IO classes, the SQLite source must be converted from .C files to .CPP files. Renaming the files was easy, but after renaming the source files over 500 compiler errors were output. Most of these were language differences between C and C++ and could be solved quickly. After resolving these problems, programming the OS routines with the Symbian file IO classes could begin.

For this part a new file was created and added to the SQLiteS60 project (os_symbian.cpp). This file includes all methods that SQLite expects for file IO. The conversion of the existing C files of the SQLite project to CPP files makes the SQLiteS60 project very different from the main source. As a result of this, the implementations for the other OS operations, i.e. for OS2, Windows and Mac, were deleted from this project. SQLite uses a structure to reference the correct OS operations and is implemented in every OS implementation. Due to this practice, only a compiler switch is needed to generate a run-able SQLite library for the specific operating system. As this structure is not used in the S60 conversion project, it has also been removed from the code and the Symbian IO methods are directly connected to the OS methods in the file os.cpp.

A simple example to open or create a file with native Symbian methods is shown in the following code snippet.

    RFs fileSession;

    RFile file;

    fileSession.Connect();

    TInt err=file.Open(fsSession,fileName,shareMode);

    if (err==KErrNotFound) // file does not exist - create it

    {

        err=file.Create(fsSession,fileName,shareMode);

    }

    // do read and write operations

    file.Close();

    fileSession.Close();

The RFs class defines the file server session. Any file operation in controlled by an instance of this class. After this instance is created, the file can be opened over the RFile class. The RFile class contains all file IO operations. By using these two classes to extend the sqlite3_file structure, the SQLite file IO operations can use the class instances of RFs and RFile as shown in the struct below.

    struct symbianFile {

      int isOpen;

      unsigned char locktype; /* Type of lock currently held on this file */

      short sharedLockByte;   /* Randomly chosen byte used as a shared lock */

      char fileName[512];

      RFs session;

      RFile file;

    };

Finally all the methods that SQLite needs to handle file operations must be rewritten by using this structure and the RFile methods. The following example shows how to close an open file and the file server session.

    int Close(sqlite3_file *id){

      int rc, cnt = 0;

      symbianFile *pFile = (symbianFile*)id;

      pFile->file.Close();

      pFile->session.Close();

      return SQLITE_OK;

    }

The other file IO operations are similar to the close method. The open method of RFile expects a Unicode string that contains the name of the database file. A conversion of the ASCII format to Unicode must be done before calling the open method. For such a conversion the CCnvCharacterSetConverter class can be used. This class needs the file server session handle and the string to convert to Unicode. The following example shows the converter method used in SQLite.

    void ConvertToUnicode(RFs session, TDes16& aUnicode, const char *str)

    {

      CCnvCharacterSetConverter *converter = CCnvCharacterSetConverter::NewL();

      converter->PrepareToConvertToOrFromL(KCharacterSetIdentifierUtf8, session);

      TPtrC8 ptr((const unsigned char*)str);

      int state = CCnvCharacterSetConverter::KStateDefault;

      converter->ConvertToUnicode(aUnicode, ptr, state);

    }

After rewriting all operations used by SQLite to use the RFile class, in the os_symbian.cpp file , the project can be compiled by the Symbian C compiler (which has been included in the build chain by installing the Carbide plug in for Visual Studio) and a Symbian Library is created that can be used on a S60 device as a database. This library takes the form of a native Symbian DLL called sqlite.dll.

The database file that the sqlite.dll generates can be used by any other implementation of SQLite. So it will be possible to transfer all stored data from a smart device to a database server in a company network.

Compiling sqlite.dll from the SQLiteS60 project

It is possible to compile the sqlite.dll using Visual Studio with the Carbide plugin.

Another easier possibility is to type the following command lines in the ‘group’ directory of the SqliteS60 folder.

To compile for the S60 device (GCCE compiler) and as a release version:

    > Bldmake bldfiles [enter]

    > abld build gcce urel

For the emulator with debug information:

    > Bldmake bldfiles [enter]

    > abld build winscw udeb [enter]

Note: No spaces must be present in the directory path where the SQliteS60 project is located. Also, the command line console must be run with “Admin” privileges.

Writing an ADO.NET Provider using Net60 Compact Framework 1.0

After the successful conversion of the SQLite code to the S60 platform, the development of an ADO.NET provider for Net60 that uses the SQLite library for Symbian OS can be started. As there are some existing implementations of an ADO.NET provider for the .NET Framework, this development did not have to start at the grass roots level. Mono, for example, contains such an implementation ( www.mono-project.com ). This could be used as a base for the ADO.NET provider for Net60.

The .NET Compact Framework 1.0 only provides the basic interfaces like IDbConnection, IDbCommand, IDbParameter and so on. Other functionality such as the base classes in the System.Data.Common namespace are not provided. Also the DbDataAdapter base class is not implemented. So this implementation cannot support DataAdapters, however with these basic interfaces it is possible to develop an ADO.NET Provider that supports all basic features of the .NET Framework 1.1 for the desktop.

The SQLiteS60 project results in a native Symbian DLL, sqlite.dll, which must be used from within managed .NET code. Net60, and the .NET Compact Framework, supports interoperability between managed and unmanaged code through a mechanism called P/Invoke.

The diagram below shows the interaction between the various components of the solution:

The native SQLite methods, such as libversion, open etc., are called using the P/Invoke mechanism in the .NET code. To do this, the entry point of the corresponding method in the SQLite library must be known. Symbian has a specific entry point model. All methods are exported from a library by ordinal numbers instead of method names. The relation of method name and export number can be found in the generated .DEF file of the SQLite.dll or by using a dump tool.

The following example shows how a native SQLite method is defined in .NET. The DLLImort attribute defines the library name and the entry points were the method is located in the library. The CallingConvention defines the type of data exchange between the unmanaged SQLite and the managed .NET code.

    [DllImport("sqlite.dll", EntryPoint = "#86", CallingConvention = _Convention)]

    public static extern SQLiteCode sqlite3_open(IntPtr filename, out IntPtr db);

Based on the existing implementations of SQLite for .NET it was easy to develop an ADO.NET Provider that runs on the Compact Framework. To test the behavior of a .NET application, this implementation provides a P/Invoke call for each sqlite3_ method call to the Symbian version contained in the sqlite.dll. These P/Invokes are performed in the SymbianMethods.cs file in the managed System.Data.SQLiteClient.dll extension class.

Interestingly, all applications are also executable on the smartphone device emulator. To change between the emulator and device, only the emulator flag in the connection string must be set to true or false. For the emulator the Windows version of SQLite is required ( www.sqlite.org ), the emulator flag links the P/Invoke call to that library.

Deploying SQLiteS60 to the device

In order to deploy the SQLite database to device, the sqlite.dll must be deployed to the \sys\bin directory and the System.Data.SQLiteClient.dll to the Red Five Labs GAC repository on the device which is \Resource\RedFiveLabs\Gac. The sqlite.pkg is used together with the Symbian makesis.exe to create an installable SQLite.SIS file which deploys these two libraries to their respective destinations. If the sqlite.dll and System.Data.SQLiteClient.dll are placed in the same directory as the sqlite.pcg file, an example command line to generate the Sqlite.sis is

    C:\temp\sis>makesis sqlite.pkg

Using SQLite

The implementation for Compact Framework 1.0 contains the SQLiteConnection class that handles the connection to the sqlite database and creates new SQLiteCommand instances. With these instances database queries can be sent to and the results received from the database. As a result a SQLiteDataReader set instance can be returned. This class provides the functionality to step through the rows of the result sets. Transactional processing can be performed by using the SQLiteTransaction class. An instance for that class can be created over the SQLiteConnection instance. Any SQLiteCommand can contain one or more SQL statements. These statements are handled in the SQLiteStatement class. Each statement is represented by an instance of that class. This class calls the native SQLite methods to perform the database operations.

The following example shows how to open a database by using the SQLiteConnection class.

    SQLiteConnection _Connection = new SQLiteConnection("Data Source=test1.db;NewDatabase=True;

                                                      Synchronous=Off;Encoding=UTF8;Emulator=true");

    _Connection.Open();

    SQLiteCommand cmd = _Connection.CreateCommand();

    cmd.CommandText = "select id, orderid, article from orderdetails where OrderId = @OrderId";

    cmd.Parameters.Add("@OrderId", DbType.Int32).Value = orderId;

    DataTable table = new DataTable();

    table.Columns.Add("Id");

    table.Columns.Add("OrderId");

    table.Columns.Add("Article");

    SQLiteDataReader reader = cmd.ExecuteReader();

    while (reader.Read())

    {

        DataRow row = table.NewRow();

        row["Id"] = reader.GetInt32(0);

        row["OrderId"] = reader.GetInt32(1);

        row["Article"] = reader.GetString(2);

        table.Rows.Add(row);

    }

    dataGridView.DataSource = table;

The SQLiteCommand instance contains a select statement that loads the orders with the specified OrderId. The result set of this query is then filled in a data table that is databinded to an dataGridView that displays the result. An example application is provided in the form of SqlMobileTest project.

Conclusion

With this conversion of SQLite, it is now possible to create .NET applications with database support for the great family of Symbian S60 smartphones. With the upcoming release of version 9.4 the Symbian OS provides an implementation of SQLite but this version is not released yet and only new devices will get this database support. With this conversion all S60 3rd Edition devices will be able to use SQLite. Together with the Red Five Labs Net60 implementation of the .NET Compact Framework it is possible to run .NET applications on Symbian OS. Together with the ADO.NET provider for this SQLite implementation these applications can use database functionality like any other application on a personal computer.

Tools Used for this project

Microsoft Visual Studio 2005 together with the Carbide.vs Plugin (http://www.forum.nokia.com/main/resources/tools_and_sdks/carbide/index.html ) were used in this project.

Additionally the S60 SDK and the Symbian PIPS SDK are required to compile the project. With the Carbide plug in it is possible to develop native Symbian applications in the Visual Studio IDE and to debug on the S60 emulator. To convert a S60 project (f.e. a BLD or MMP file) to a Visual Studio project, the Import Wizard of the carbide plugin could be used. It is located under the File menu. The user only has to select the s60 project and to assign an S60 SDK (f.e. maintenance release or FP1/FP2). The rest of the transformation does the wizard. So it is easy to convert existing projects to use the carbide plugin.