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 urelFor 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.pkgUsing 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.