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.