SQL Compact Insert Performance

I've just posted the SQL Compact Insert Performance demo on my managed code blog. It compares various approaches for inserting large volumes of data in a SQL Compact database and, although written for a managed code audience, is also relevant for native code and the OLE DB stack (the basic reality is the same, only slightly faster...).

TouchBrowser - II

Here are a few more tweaks to the touch browser sample I published in my last post. One of the issues that nagged me was the item's height - too small for finger use. This new sample uses the large system image list which makes for bigger items that can be more easily selected with a finger.

As you can see from the image, directories and regular files are rendered differently. File items will also have the file size and last change date printed in the list for the next release.

In this version I also fixed the bub in the EnsureVisible function. When you use the direction keys to navigate the list the items are now correctly shown and the list scrolls with a small animation.

My major concern with this code is finger-friendliness. There are still a lot of rough edges and it works best with a stylus - not my intended purpose. I'm still wondering how to solve this issue and make the code consistently recognize a drag gesture. Finally, I will also have to change the way the code accepts an action on an item. One of the ideas I'm exploring for this is to define a "hot" area in the item that will be used to take the action (in this case to open a file or a directory). Each item will know where its hot area is and will report it back to the container in a HitTest - type of function.

Sample: TouchBrowser2.zip

TouchBrowser - I

My planning for the first half of 2009 includes writing a SQL Compact explorer, something like the old isqlw mobile application. One of the components this application needs is a friendly way to select the database files - something along the lines of the open file dialog, only a bit better.

Towards this end, I picked up the "touch" window code used in the property list and adapted it to a very simple file browser. The sample application that I'm publishing includes a revised version of the atltouch.h file and implements a very simple-minded "file explorer" window.

You navigate either by scrolling the list or by using the arrow keys. To list a sub folder, just click it or select it and press the enter key. To back up to the previous level, use the main menu "Up" command.

There are still some issues to be sorted out in this code, like the apparent sensitivity when you start dragging a directory entry up or down - you may end up expanding that directory instead of dragging the list. I have struggled with this issue ever since I started to write this code and I'm starting to believe that a different approach may be needed, like the addition of an action button to the right of each file: only by pressing it will the user get the associated action (opening a sub-folder, for instance). This solution implies having larger list items so that a finger can be effectively used (but this is also an issue with WM devices that are expecting a stylus, not a bulky finger). I will be investigating these issues and posting the code in the next few posts.

Sample code: TouchBrowser.zip (27 KB)

Minor bug correction

There is a bug in the last version of atltouch.h that causes a debug assertion when the user clicks the empty area of the list. To solve it open the atltouch.h file and look for the ActivateItem method on line 910. Before the last call to NotifyItemChange, insert the following line:

if(IsValidItem(iItem))

Thanks, Vincent!

Why does CeRapiInitEx return E_INVALIDARG?

This was a very hard debugging session. Suddenly I found that while using exactly the same DLLs, Wizard was failing to open a RAPI connection while Console was working without any issues. Yes, the code and the binaries were the same. I tried everything, from file and directory rights to the .NET trust, not forgetting the application threading model. Nothing worked: Wizard consistently reported an E_INVALIDARG error when calling CeRapiInitEx (both static and dynamically linked). What to do?

Out of desperation I called the RAPI initialization code on the main form constructor. Surprise: it works! If I move the code to the button event handler it failed. Then I tried the code on the form load event, only to find that the code also failed. What could be happening between the main form constructor and the form event handling code?

Wait, let's try the code at the end of the main form constructor. Voilá, it failed. At the start of the constructor I get an S_OK and at the end I get an E_INVALIDARG. Why? Some time ago I tried to be really smart (lesson: don't try to be smart) and wrote some code that would test the presence of a particular database engine OLE DB CLSID so that the user would see a list of the available engines on her PC. This actually worked badly and instead of dynamically building the UI labels, I went back to static text but I forgot to remove the calls. They look like this:

DPTRACK_API int IsLocalEngineInstalled(GUID guid)
{
   CComPtr<IDBInitialize>    spInitialize;
   HRESULT                   hr;

   CoInitializeEx(NULL, COINIT_MULTITHREADED);
   hr = CoCreateInstance(guid, NULL, CLSCTX_INPROC_SERVER,
                         IID_IDBInitialize,
                         (void**)&spInitialize);
   CoUninitialize();

   return SUCCEEDED(hr);
}

After removing the calls to this toxic code, Wizard works again! Why is CeRapiInitEx sensitive to COM initialization?

Property List Notifications

In my last post I presented a custom control based on the previously published CTouchWindow WTL class. In this post I'm updating the code to support individual item enabling and disabling and also to illustrate how the client code can manage item notifications.

Each CTouchListItem (the base class for CPropertyListItem) now supports two new functions to control and report the enabled state: SetEnabled and IsEnabled. After changing the item state your code will have to update the item in order to reflect its updated status (this is very likely to change in the near future).

The new sample code now implements a very simple notification handling mechanism that tests the check state of the check box. This is done in the dialog's OnItemActivated method, called after a particular item is activated or deactivated. In this case, I test if the activated item is the check box item and enable or disable the second group (collapsing it when disabling).

As always, comments are welcome!

Sample: PropList2.zip (46 KB)

The Property List

Before I can go any further in the development of the SQL Explorer device application, I must make sure that I have some components ready for use. These will include (but will not be limited to):

• A tree component to display the database schema;
• A property list control to edit/display object properties such as connection, table and column properties;
• A grid or table-like display for table and query results;
• A splitter control for compound views;
• A decent file open dialog box.

It surely would be easy to just use the controls that Windows Mobile has in store for us: the tree view and the list view. These are undoubtedly the most used controls but are somewhat déja-vu and are arguably hard to use (especially the list view). I decided to (guess what) write these controls from scratch using something the WTL CTouchWindow class that I published here a some months ago.

This class is a nice candidate to be a base class for the new tree, grid and property list controls because it enables a touch-like interface. My first approach was to implement the property list control taking some of my previous work as a model: A list-based form for Windows Mobile. There are two nagging issues with this code: it uses MFC and subclasses the list view control. As I've learned by implementing the WTL version I'm presenting here, subclassing and custom-painting the list view is more work than you need to implement the control.

The property list is shown here contains a list of editable items and groups. Groups are shown as collapsed (the + sign indicates that the item can be expanded) and, like the original MFC implementation, each group can contain only editable items, not other goups (I may change this later on).

A group should be used as a means to group related data items and to ease the list navigation. When clicked the group expands to display the contained editable items. By clicking again, the group collapses and hides the contained items. This is the same principle that you can see at work with a tree, so extending this concept to a "touch tree" should not be very difficult.

Each editable item may be in either of two states: focused or activated. An item gets the focus when it is clicked and when either the up or down keys are used to change the selection. A focused item shows the focus rectangle around it. When the enter key is pressed (or when the user clicks the item) it is activated (note that activating implies setting the focus). The notable exception to this rule is the text editor item that activates itself when it receives the focus.

When an item is activated, it generally creates a Windows control to perform the editing. The exceptions to this rule are the check box (changes its state) and the group item (expands / collapses the contained items).

As of this implementation, the supported data item types are:

• Text editor
• Date time editor
• Check box
• Combo box

Here is the expanding property list:



The scroll bar to the right is custom-painted and is not yet the final version (I'm still thinking about allowing the use of old style scroll bars). The advantage of this type of scroll bar is its transparency - you can actually read what's beneath it so you do get a few more pixels of screen real estate.

I will make lots of changes and additions to this code but, in the meantime, please do take a look at it and be so kind as to use it and criticize it.

Sample code: PropList.zip (45 KB)

Enumerating Unique Constraints

There's no big deal about getting the list of UNIQUE constraints - they get listed as indexes by the CIndexesRowset class. When using this schema rowset you get everything that is implemented as an index by the database engine:

• Indexes
• Primary Key constraints
• Unique constraints
  

While the schema rowset can distinguish between a regular index and a PRIMARY KEY, there's no telling if the listed index is a UNIQUE constraint or not. To correct this, you must first list all the UNIQUE constraints using the new CTableDefinition::FillUniqueArray method (see the updated sample below). As you can see, a unique index is also represented by a CIndex object, but stored in a different collection in the CTableSchema class. When the indexes are finally enumerated, the list is filtered for known UNIQUE constraints.

Another addition to the sample code is the ability to open any SQL Compact database on your device by using the Menu / Open sequence. The code that opens the database uses the installed SQL Compact OLE DB engines to "sense" the correct database format (see the updated CSchemaTreeFrame::OpenDataSource method).

On the next post I will start building the SQL Compact Explorer application by using this sample as a starting point. I will also use this application to bring back some of the code that I have been working on, namely the touch list and all the derivatives that have been developed but left unpublished.

Sample code: SchemaTree4.zip (155 KB)

Windows Mobile VM Article

A very interesting article about Windows Mobile Virtual Memory has just turned up on Code Project:

Visualizing the Windows Mobile Virtual Memory Monster

Not only this is a worthwhile read, you will also get a couple of links into the Windows Mobile RSS (Reed and Steve Stuff) Feed blog where the Windows Mobile virtual memory manager is discussed.

On my next post I will publish the revised version of the SchemaTree sample with a few additions, like the ability to open any SQL Compact database on the device. From then on, the project will be renamed and I will start building an on-device SQL Compact explorer application. In native code, of course!

Enumerating Foreign Key Constraints

After enumerating columns and indexes, now it's time to work on the first of the three types of constraints that are supported by SQL Compact: foreign key constraints. Constraint definitions are also retrieved by calling ITableCreation:.GetTableDefinition. These are returned in an array of DBCONSTRAINTDESC structures that contain all the supported constraints. Each record contains one constraint whose type is stored on the ConstraintType member. The SQL Compact header file defines the following values:

• DBCONSTRAINTTYPE_UNIQUE - A unique constraint.
  
• DBCONSTRAINTTYPE_FOREIGNKEY - A foreign key constraint.
  
• DBCONSTRAINTTYPE_PRIMARYKEY - A primary key constraint.
  
• DBCONSTRAINTTYPE_CHECK - A check constraint (not supported by the current SQL Compact versions)
  
• DBCONSTRAINTTYPE_SSCE_DEFAULT - Apparently this is a default value constraint. I have found no documentation about this constant and will investigate it later.
  

For now, we concentrate on the foreign key constraints and how to show them on the SchemaTree sample aplpication. In this version of tha sample, foreign keys will be displayed in a folder of their own, but for future versions this folder will be renamed "Constraints" and will contain all table contraints, not just foreign keys.

A foreign key definition is stored in the new CForeignKey class. You can see these classes being built from the array of DBCONSTRAINTDESC structures in the new CTableDefinition::FillForeignKeyArray function, where each array item is tested for the correct type and then fed to the CForeignKey class constructor, where all thework takes place. Each foreign key contains a list of matched columns from the reference table and the base table (each pair is stored in an instance of the CForeignKeyPair class).

Displaying these in the schema tree is quite straightforward and follows the same rules I have used for columns and indexes. Please note that there are new accessors for foreign keys on the CTableSchema class.

On my next post I will also enumerate primary keys and unique constraints, put them all under the same folder and start moving towards an on-device SQL Compact editor. I already got a name suggestion from Alberto Silva: SQL Explorer. More names, anyone?

Sample code: SchemaTree3.zip (1.2 MB)

Native Pointers

No, I'm not talking about memory addresses, just interesting bits of information for the Native Mobile developer.

MSDN forums have recently moved to a new platform and got new URLs. Here are my favorites:
Visual Studio Smart Device Development - Native C++ Project
SQL Server Compact

On another note, Christopher Fairbairn just wrote another great post in his blog where he discusses some very cool techniques for Native Mobile developers, such as playing sound, using COM and displaying PNG images. A must read!

Code changes

After a second look at the array CAtlArray implementation, I'm planning to change all the containers from value to pointer (instead of CAtlArray, I will change this to CAtlArray). The reason is quite simple: when growing the buffer through Add, the existing items are moved to the new buffer with a simple Checked::memmove_s call. The contained class copy constructor is not called which may lead to memory leaks (depending on the contained class implementation). If the array merely contains pointers, everything works as expected and all memory relocations will be much faster (only the pointers are copied, not the objects themselves). The downsidr to this approach is that I will have to manually implement the code to release all the objects pointed to by the array.

Finally, don't miss this great post on the Windows Mobile Team Blog: Uninstalling Applications Programmatically in Windows Mobile.

The perfect time-waster

Reinventing the wheel. That's what I have been doing for the last few days implementing object arrays in C++... Why? Apparently some template classes are not very easily exportable on a DLL so you cannot use useful stuff like CString or CAtlArray as exported class members. After having implemented a very simple string class and while working on object array allocation I decided it was enough: from now on the OLE DB Client library will not live in a DLL but either on a LIB or embedded in the client project. Enough is enough - I want to move ahead and implement the cool stuff, not arrays and strings so that the damned thing can be neatly exported as a DLL.

The code I'm presenting today has some additions to the OLE DB Client library, namely some new schema-related classes:

• CSchema - Contains an array of table CTableSchema objects and a reference to a CSession.
  
• CTableSchema - Contains a table schema information. This class is prepared to load this information on demand in order to avoid a performance penalty when enumerating the database schema (all tables are loaded and each table schema is loaded on demand).
  
• CTableDefinition - Helper class that loads the table definition in a single OLE DB call. This populates the columns and constraints collections. Indexes are loaded separately through a specialized schema rowset (see the LoadIndexes method).
  
• CColumn - Contains column schema information.
  
• CIndex - Contains index schema information and a list of index columns.
  
• CIndexColumn - An individual index column.
  

Please note that some of these classes will change in content and placement (I mean the .cpp file).

The sample project is the same - it enumerates in a tree the schema of the sample database placed on the device root. As you can see from the code, the tree lazily loads the table schema information (when the user expands either the "Columns" or the "Indexes" folder).

After loking a bit at this sample and to how the OLE DB Client has evolved, I started wondering about writing a "Query Analyzer" type of application. This would mimick most of the "old" SQL Compact Query Analyzer application and would add a few more features. Writing this application would require development in other areas such as the user interface, but I think that it will be a very interesting challenge.

What features would you like to see in the open source QA? What name would you give such app?

Sample: SchemaTree2.zip (1.21 MB)

Enumerating Columns with IDBSchemaRowset

While it's freezing out there (for Lisbon standards, of course) I have been busy working on the schema enumeration code for the OLE DB Client library. My first approach is to enumerate database tables and columns using the IDBSchemaRowset interface. Access to this interface is encapsulated in the CRowsetInterface class that is derived in CTablesRowset and CColumnsRowset classes to enumerate tables and columns respectively. The IDBSchemaRowset interface works by accepting a set of "restrictions" and generates a schema rowset containing the requested data. Schema rowsets are formally defined on the Appendix B of the OLE DB Programmer's Guide, where you can see the returned columns and the accepted "restrictions". SQL Compact implements a subset of these, as described in the BOL.

Using the CTablesRowset is quite easy (and you have seen it at work in a previous sample where it was used to test for the presence of a given table):

CTablesRowset tables(m_session);
CRowset rowset;
HRESULT hr;
hr = tables.Open(NULL, NULL, NULL, NULL, rowset);

The first four NULL parameters mean that you don't want to filter on any of the supported restrictions, so you will get a list of all the tables in the database. SQL Compact does not support table catalogs nor table schemas so the first two parameters are always NULL for this provider. The third restriction is the table name and the fourth is the table type (see the possible values for this in the TABLES schema rowset reference).

When scrolling through the tables rowset, you retrieve the current table name like this:

CString strTable;
rowset.GetValue(3, strTable);

Now that we have the table name, we can enumerate its columns by using the CColumnsRowset and imposing a restriction on the table name (see the COLUMNS schema rowset reference):

CColumnsRowset columns(m_session);
CRowset rsColumns;

hr = columns.Open(NULL, NULL, strTable, NULL, rsColumns);

This retrieves all columns from the given table and you can get the column name from ordinal 4. As you can see from the COLUMNS schema rowset, there are lots of additional schema information about a column, like its OLE DB type, "nullability", size and more.

Although this is a quite convenient way to retrieve a table schema, we can use another OLE DB interface that in a single call returns column and constraint information. On the next post, I will look at ITableCreation.

Sample code:
SchemaTree.zip
OleDbClientLib2.zip

Connection Manager Article

The latest issue of MSDN Magazine is out and it features a great article from Marcus Perryman: Going Places: How Connection Manager Connects. The reason why I'm also referring to this article here is that its code samples are in C, a rarity these days.

SQL Compact Schemas

Now that I have looked into the most basic principles of using a SQL Compact database from native code (base table cursors and command execution), I will now look at the available tools to enumerate and manipulate the database schema. There are essentially two ways to go about this:

1. SQL commands
2. OLE DB interfaces

Using SQL commands
This may be the easiest approach and is also language-independent - you can use these techniques from both native and managed code. To determine what's in your database you can execute SELECT commands against the INFORMATION_SCHEMA system views. These work like regular SELECT commands and return specific rowsets:

• COLUMNS
• INDEXES
• KEY_COLUMN_USAGE
• PROVIDER_TYPES
• TABLES
• TABLE_CONSTRAINTS
• REFERENTIAL_CONSTRAINTS (not documented for 2.0)
  

To retrieve information from one of these views, you just execute a simple query like:

SELECT * FROM INFORMATION_SCHEMA.TABLES

This will return all tables in your database. The advantage of using a SELECT command is that you can add a WHERE clause and filter the result. If you need to make changes to your database schema you can use all the supported DDL commands. Since version 3.0 came out, Microsoft even provided us with a stored procedure to rename tables: sp_rename. Unfortunately you cannot rename columns which sometimes is needed.

Using OLE DB interfaces
All of this (and a bit more) can also be done through some of the exposed OLE DB interfaces, namely the following Session interfaces:

• IDBSchemaRowset - Generates rowsets that are similar to the ones returned by the INFORMATION_SCHEMA views.
  
• IIndexDefinition - Creates and drops indexes.
  
• ITableCreation - Retrieves the table definition (full schema).
  
• ITableDefinition - Creates drops tables, adds and drops columns.
  
• ITableDefinitionWithConstraints - Same as above, but also manages table constraints.
  
• IAlterIndex - Only renames indexes in SQL Compact.
  
• IAlterTable - Allows changing table properties like the table name, column defaults and identity properties. You can also use this interface to rename columns (not available in managed code).
  

Note that when using these interfaces, you are accessing the storage engine directly, circumventing the Query Processor.

On my next post I'm planning to publish a sample that illustrates how to use these interfaces.

Logo Certification Article

Here's a very nice article for native mobile developers that addresses the required coding for some of the strict Windows Mobile Logo Certification. A worthwhile read.

Implementing SQL Compact Command Parameter BLOBs

After much ramblings, tests and misconceptions, I finally closed the chapter on SQL Compact command parameters. The sample code (see below) illustrates how to use a BLOB command parameter using the "variable length" binding buffer. Whenever the total size of the columns exceeds the allocated binding buffer size, the binding process is repeated increasing the binding buffer size. Although this is not an optimal solution (being able to use storage objects for this purpose would be much better), it frees you from having to specify the maximum size for BLOB parameters - the code calculates this for you.

Before you can execute a command with parameters, you must prepare it by calling the Prepare method (failing to do so will cause a run-time error). Here the code determines the number of existing parameters and allocates memory for a "user buffer". This user buffer is an array of CDbValue objects, a VARIANT-like class whose sole purpose is to store parameter values. When the user sets a parameter it is first written to one of these objects and upon execution it is copied to the data buffer. Meanwhile, the code determines if the new set of parameters on the CDbValue array requires rebinding, does so if needed and finally copies the values to the data buffer. This way you just have to set the parameters and execute the command (see the sample code).

As a side-effect of all these travails, I ended up creating a new class - CDbValueRef - from which CDbValue derives that I use as the "engine" that sets and gets data from various types, performing the necessary data type conversions. As you can now see, the column data access on the CRowset class is performed through an array of such objects that directly reference the bound data buffer, and they know how to handle storage object BLOBs. Now you can access your CRowset data using a type-safe interface.

Sample: InsertBlob.zip (152 KB)

I was right...

... for the wrong reasons. In fact, you cannot use storage objects to set BLOB command parameters. I found that the usual mechanism does not work because:

1. When retrieving command parameter information through GetParameterInfo, you don't get the DBPARAMFLAGS_ISLONG bit set for BLOBs;
2. Binding a BLOB parameter through a storage object causes the Execute method to fail reporting a DBSTATUS_E_CANTCONVERTVALUE on the BLOB column.

So I had to revert to the old code that buffers the parameter values in a buffer and forces a rebind when the new set of parameters is larger than the old parameter buffer (used by the accessor). Now that I got this straight, it's time to finish the new demo that inserts BLOBs using a parametrized INSERT command. Please stay tuned for the next post.

I was wrong

Tomorrow I will deliver (using the English language) my second presentation in an international Microsoft conference. My first time ever was in 2005 in Nice for the now defunct MEDC (Mobile and Embedded Developers Conference). I have been splitting my time in attending interesting presentations, preparing my own presentation and completing the OLE DB Client library code. It was while working some final details of the presentation that I had an epiphany: I was wrong about BLOB command parameters!

The fact that the OLE DB specification limits us to using just one parameter accessor handle to set all the parameters does not mean that we are unable to use storage objects. Why? When I discussed the limitation of the SQL Compact engine that prevents it to create more than one storage object at the same time, it actually applies to when the data flows from the provider to the consumer, like when you are reading data from a base table cursor or from a query cursor. It does not apply to when data flows from the consumer to the provider. In fact, we experienced no such limitation when inserting or updating data through a base table cursor. The consumer application can create as many storage object as it pleases to, and this is the exact same situation with command parameters: the consumer application can only write to them because SQL Compact does not support output parameters.

Right now this is still a theory that seems to make a lot of sense to me and I will focus now on proving it right. Meanwhile, I developed a new set of classes to manage column values in such a way that they would do double duty when used on a rowset and on command parameters. For the latter, these classes would work as intermediate data buffers that would be used as temporary storage for application-provided parameter data. When needed, the CCommand code would bind these to a real data buffer, recreating the accessor handle when needed. In fact, I have just tested the code and it works... But this can't be right. If I can bind the parameters before setting them (just like in the CRowset class) then I will have the best solution.

Back to the drawing board. (If you want to see the code as it is, please drop me a line.)