Skip to main content

Attaching multiple sqlite databases in WP7

Following on from my previous post Rich & I are developing an app which has 2 versions - one has only a single sqlite database but the other could use multiple sqlite databases to store the data - in effect the data is partitioned (sharded) alphabetically for the second version.

Our existing code used for the single database version uses a Simple ORM to do our data access. The primary reason being the code is 2 lines and importantly the performance cost of using the Simple ORM is not an issue at the moment. If it ever becomes an issue we'll switch it out.

What follows is how we approached attaching multiple databases together. Sqlite allows multiple databases to be used under the same connection. The databases don't have to share the same schema at all, all that is required to use the attached databases is to use the syntax database-name.table-name. More info can be found at the sqlite.org website here.

The first attempt was to do this from Vici CoolStorage, I didn't expect this to work because it only has the notion of setting the database you are about to access, I couldn't find anyway to attach a second database. The code is shown below and the runtime exception afterwards, as I said this was hopeful and failed.

    private void databaseAttach_Click(object sender, RoutedEventArgs e)
    {
        CSConfig.SetDB("database1.sql", SqliteOption.None);
        CSDatabase.ExecuteNonQuery("ATTACH 'database2.sql' AS db2;");

        var results = CSDatabase.RunQuery<search_db_column_names>("SELECT db2.search_db_column_names.pk FROM db2.search_db_column_names");

        Debug.WriteLine(results.ToString());
     }

Blew up with the following exception:

The second attempt was to use C# Sqlite For WP7 of codeplex. Vici CoolStorage is written on top of this so my thought were it would be less of an abstraction and therefore more likely to succeed. This time the code has more traditional DAL feel about - the use of connection, command & reader objects.

Success this worked!

The code is shown below and it's pretty much the same as for a single database call apart from the execute no  query to attach the second database and the modified SQL statement.

As stated earlier the statement is now explicit about which columns and tables are being referenced:

"SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes"

In this case all I'm doing is the union between 2 tables as they have the same structure and none over lapping data.

        private IList<PostCode> ExecuteAllDatabase1()
        {
            var postcodes = new List<PostCode>();
            using (var conn = new SqliteConnection("Version=3,uri=file:" + Database1))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "ATTACH '" + Database2 + "' AS db2;";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes";

                    using (var reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
                    {
                        while (reader.Read())
                        {
                            var postCode = new PostCode
                            {
                                Value = reader.GetString(1),
                                District = reader.GetString(2),
                                Latitude = reader.GetDouble(3),
                                Longitude = reader.GetDouble(4),
                            };

                            postcodes.Add(postCode);
                        }
                    }

                    conn.Close();
                }
            }
            
            return postcodes;
        }

Okay got it working but what about if I got as close to the metal as possible - C# Sqlite For WP7 is written using the csharp-sqlite project up on google code. Shown below is the same functionality written using the C style sqlite.org API.

        private IList<PostCode> ExecuteAllDatabase2()
        {
            var postcodes = new List<PostCode>();
            var database1 = new Sqlite3.sqlite3();

            if (Sqlite3.sqlite3_open(Database1, ref database1) == Sqlite3.SQLITE_OK)
            {
                var attachSql = string.Format("ATTACH DATABASE '{0}' AS db2", Database2);
                var errorMessage = string.Empty;

                if (Sqlite3.sqlite3_exec(database1, attachSql, null, null, ref errorMessage) == Sqlite3.SQLITE_OK)
                {
                    var selectStmt = new Sqlite3.Vdbe();
                    var selectSql = @"SELECT * FROM main.PostCodes UNION SELECT * FROM db2.PostCodes";
                    var stringTail = string.Empty;

                    if (Sqlite3.sqlite3_prepare_v2(database1, selectSql, -1, ref selectStmt, ref stringTail) == Sqlite3.SQLITE_OK)
                    {
                        var n = 0;
                        while (Sqlite3.sqlite3_step(selectStmt) == Sqlite3.SQLITE_ROW)
                        {
                            var col1 = Sqlite3.sqlite3_column_int(selectStmt, 0);
                            var postCode = Sqlite3.sqlite3_column_text(selectStmt, 1);
                            var district = Sqlite3.sqlite3_column_text(selectStmt, 2);
                            var latitude = Sqlite3.sqlite3_column_double(selectStmt, 3);
                            var longitude = Sqlite3.sqlite3_column_double(selectStmt, 4);

                            postcodes.Add(new PostCode { District = district, Value = postCode, Latitude = latitude, Longitude = longitude});
                        }
                    }
                }
                else {}
            }
            else {}

            Sqlite3.sqlite3_close(database1);

            return postcodes;
        }

The only thing left to do was compare the performance of the 2 working solutions. Interestingly it appears the C# Sqlite For WP7 gives better performance than the sqlite API.

C# Sqlite For WP7 average = 85 ms
sqlite API average = 95 ms


I suggest this is probably due to the fact I haven't tweaked the sqlite API code to be as per-formant as possible. Again I have ignored the first result for both implementations.



Comments

Popular posts from this blog

Showing a message box from a ViewModel in MVVM

I was doing a code review with a client last week for a WPF app using MVVM and they asked ' How can I show a message from the ViewModel? '. What follows is how I would (and have) solved the problem in the past. When I hear the words ' show a message... ' I instantly think you mean show a transient modal message box that requires the user input before continuing ' with something else ' - once the user has interacted with the message box it will disappear. The following solution only applies to this scenario. The first solution is the easiest but is very wrong from a separation perspective. It violates the ideas behind the Model-View-Controller pattern because it places View concerns inside the ViewModel - the ViewModel now knows about the type of the View and specifically it knows how to show a message box window: The second approach addresses this concern by introducing the idea of messaging\events between the ViewModel and the View. In the example below

Implementing a busy indicator using a visual overlay in MVVM

This is a technique we use at work to lock the UI whilst some long running process is happening - preventing the user clicking on stuff whilst it's retrieving or rendering data. Now we could have done this by launching a child dialog window but that feels rather out of date and clumsy, we wanted a more modern pattern similar to the way <div> overlays are done on the web. Imagine we have the following simple WPF app and when 'Click' is pressed a busy waiting overlay is shown for the duration entered into the text box. What I'm interested in here is not the actual UI element of the busy indicator but how I go about getting this to show & hide from when using MVVM. The actual UI elements are the standard Busy Indicator coming from the WPF Toolkit : The XAML behind this window is very simple, the important part is the ViewHost. As you can see the ViewHost uses a ContentPresenter element which is bound to the view model, IMainViewModel, it contains 3 child v

Custom AuthorizationHandler for SignalR Hubs

How to implement IAuthorizationRequirement for SignalR in Asp.Net Core v5.0 Been battling this for a couple of days, and eventually ended up raising an issue on Asp.Net Core gitHub  to find the answer. Wanting to do some custom authorization on a SignalR Hub when the client makes a connection (Hub is created) and when an endpoint (Hub method) is called:  I was assuming I could use the same Policy for both class & method attributes, but it ain't so - not because you can't, because you need the signatures to be different. Method implementation has a resource type of HubInnovationContext: I assumed class implementation would have a resource type of HubConnectionContext - client connects etc... This isn't the case, it's infact of type DefaultHttpContext . For me I don't even need that, it can be removed completely  from the inheritence signature and override implementation. Only other thing to note, and this could be a biggy, is the ordering of the statements in th