Searching a database solution for Windows Phone 7-8 and for Windows 8

I’m preparing for one of my new app-ideas. This app-idea requires a local storage of data, initially I want to create this app for Windows Phone, but I would like to have as much code-reuse as possible. So it would be very nice to have the same database solution for all platforms.

I’ve been using Sterling in the past for Windows Phone. This was before SQL CE was available for Windows Phone. Looking at releases, the last release of Sterling is from June 2011, more than a year and half ago. When I’m looking at the Source Code I’m seeing an resume in commits in last December. So the project is not dead, at least it doesn’t look dead to me. There’s also a discussion on the Future of Sterling which might interest you. There seems to be an issue with Windows Phone 8 which you can easily patch yourself. Okay, interesting, but there is no solution for Windows 8 apps yet.

Let’s explore the other options. SQL CE was added to Windows Phone 7 during one of the updates. However it’s not added to Windows 8 and will probably not be added to Windows 8 because there has been a lot of talking about SQLite in the area of Windows 8. But for Windows Phone 8 SQL CE seems to be lacking and there’s an option to use SQLite for Windows Phone 8 as well. So definitely no SQL CE for me, I want to have a solution that works at least for the whole Phone platform and not just the Windows Phone 7 platform.

So there’s a lot of talking about SQLite? It does support Windows 8 and Windows Phone 8 apps. After some searching there isn’t any official support for Windows Phone 7. I found an article written in 2011 that might help get SQLite on Windows Phone 7. This is an almost solution, maybe I just need to give SQLite a try.

While browsing the Sterling discussions I came around a comment about Lex.DB. This might be an interesting alternative, but it doesn’t support Windows Phone 7 yet. I’ll keep an eye on this project, but it’s very new in this space.

Update 26-01-2013:

In the comments Marcio already says that SQL CE works fine on Windows Phone 8. So even while there isn’t much to be found on that subject it should work. I had to try this myself, so I did. And the end-result? Windows Phone 8 supports SQL CE very well, nothing special that I’ve found in there so far.

Book review: NHibernate in Action now in print

imageIt took the authors of NHibernate in Action a very long time to complete this book. It was already in the summer of 2006 that I got involved in the NHibernate in Action book. First steps were basically reviewing the first chapters manuscript. Later on I also reviewed other chapters. About half a year ago, or maybe it was even three quarter of a year ago (time goes fast these days), Manning asked me to do the Technical Proofreading of NHïbernate in Action. In het end the book finally got to print. And yes I really like it. Although I would have been very happy to have this book in the year 2006, I’m glad to have it now. Besides the fact that this is a NHibernate book, it also covers ORM in general. And a lot patterns and concepts discussed in this book apply to other ORMs as well, like Linq2SQL and the Entity Framework.

Upgrade failure from WordPress 2.2.1 to 2.6

I thought why not upgrade the WordPress since I was a lot of versions behind. I first looked at the different steps that are required to upgrade to the new version. Just four steps:
  1. Delete your old WP files, saving ones you've modified.
  2. Upload the new files.
  3. Point your browser to /wp-admin/upgrade.php.
  4. You wanted more, perhaps? That's it!
Yes just four steps. You didn't even need to make a backup first it seams. I'm glad I did make a backup, specially of the database. So which are the troubles I had to overcome. The white page I just went to the blog's homepage, but saw nothing, yes just nothing. The management interface did work though. So when I went to the Design section I found out the theme doesn't work anymore. Not only this, it was also removed from the blog. Very strange, so I selected the WordPress default theme to have at least something online. The categories I though a not supported theme, that can happen. But when looking at the default page I saw a list of empty categories, yes empty categories only the amount of posts in each category was shown. This did make me feel a little bit angry, where are categories gone to? After some Googling I found out people who have the same people including this by hand solution. Beside the missing of the category names also the hierarchy is lost. I didn't fix the hierarchy because my new theme doesn't support hierarchical categories. The theme transition Because the failure of my old theme I got looking into a new one. So goodbye Connections Reloaded. Connections Reloaded Theme Welcome Dreamplace. Dreamplace Theme

Practicing more on the database - Rowversion / Timestamp data type

The Transact-SQL timestamp data type is not what it looks. It's not some sort of datetime data type, it's a rowversion. Each database has a counter that is incremented for each insert or update operation on a table that has a rowversion / timestamp column. Rowversion / timestamp is generally used as a mechanism for version-stamping table rows. The rowversion is unique for one entire database.

What about rowversion or timestamp?
Timestamp is a somewhat confusing name to use. Because in SQL-2003 the timestamp data type is exactly what we know as the datetime data type in T-SQL. So I prefer to use the synonym rowversion.

What about journaling these values?
In the past, I talked about triggers for journaling your data operations. In the specific example of journaling, you have to handle a rowversion column different. Because of the characteristics of a rowversion column you cannot use this datatype to journal the values deleted and inserted into a table. However the nonnullable rowversion column is semantically equivalent to a binary(8) column (a nullable rowversion is equivalent to a varbinary(8)), so you can choose to copy the inserted and deleted data from a rowversion column into a binary(8) column.

Versioning an entire entity
As mentioned a rowversion column can be used for versioning a row in a table. But you can look further. It can also be used for versioning an entire entity. The only thing you need to check is the version column in any of the entity's rows. If one is changed act like the entire entity is changed.

I hope you have learned something about this SQL Server feature that was unknown to me for a long time. At least I've learned something new on SQL Server.

Practicing more on the database - Triggers for journaling

I'm .NET developer not a database developer. But the customer I'm with since June this year wants to have as many things on the database as possible. Yes, as many THINGS as possible. First I thought why would you want to, but now I start getting it. The customer has a long history of Oracle application and as I understood everything is done in the database in case of an Oracle application.

Most of the time I would use a database as a data-store. Using it for creating, reading, updating and deleting data. If the client requires the usage of Stored Procedures, I basically create an Insert, Update and Delete Stored Procedure for every table, nothing special. For the selection of data I create a select by id, and selects that are required by the functionality for the specific table.

But of course the database has a lot of more power. The customer wanted to have a journal of all data operations. It is possible to add this functionality through application code that registers the calls in separate tables. It is also possible to add this functionality through more intelligent Stored Procedures. But the way the DBA wants it to have is the best solution I think. It is through triggers. As my database knowledge is pretty basic, I had to learn how to tackle this functionality using triggers. Now I've set up this functionality I must admit this solution is the only appropriate solution. The solution is tested, and if I'm adding a new stored procedure change my application or whatever change, I don't have to change my journal triggers (if I'm not changing the table structure of course).

The journal table must consist of the following structure: operation type, operation date, user and for every column a new_ and a old_ column.

It's important to understand that triggers are set-based in SQL Server 2000. At first people start testing using a single row, but when you for example update every record from a specific table only one trigger gets fired. Inside the trigger you can get the data using the virtual tables INSERTED and DELETED. For an Insert only the INSERTED columns are provided. When updating a set of data both the INSERTED and DELETED tables are filled. And on deletion only the DELETED table is filled. You can relate the INSERTED and DELETED data by joining them on the primary-key.

This solution makes sure that every data operation get's journaled, not only when calling Stored Procedures or when accessing the application.

Triggers are one of the things I've learned to appreciate of the non-basic dbms functionality. While I'm still no database guy, more on databases will follow in the near future.