- -

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 3^rd 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.

- -