Restored faith in SQLite.
I wasn't always a Microsoft fan. It was actually Silverlight and WPF that turned me back on to Microsoft. Since I got heavily into those 2 languages I haven't thought twice about wanting to switch to Linux full time, which was something I tossed around as little as 2 years ago. Each time I was only stopped by a lack of support for applications I use daily. Heck, I had secretly even considered a mac as my next computer. I wasn't a fan really of any of the three options, but I was in downward spiral away from MS.
For those who are developers and have never used WPF or Silverlight. You are missing out. Honestly. The XAML and code-behind combined with the data binding done right makes this, in my opinion the greatest all around programming language I've seen. I'm not saying it does anything the best (except maybe the data binding bit), but I am saying that most other languages excel in a very small area. I may prefer a purely functional language if I'm developing a computationally intensive application. I may prefer Java if I want something cross platform. PERL is great for data mining and the list goes on. All of these languages DO other things, but the point is that they do only one or a few things either very well or easily.
Basically where I'm going with this is, I tend to write applications. Not libraries, or functions, or other small components. And I don't tend to write "apps" (by which I mean dinky one offs you might find on a phone or tablet). My applications tend to have a bit of everything. I typically have a proper database of some sort, a full out UI, an underlying collection of libraries full of a variety of functions, frequently a server and client side component, etc.... And while most languages and suites can do a lot, most cannot do all of that elegantly.
The .Net suite was the exception to all of that. While, like I said, it maybe isn't the best at all things, it is actually very good at just about everything. And Visual Studio is a great IDE to complement it.
But there was always one bit I couldn't find a satisfactory solution for from Microsoft. The actual database itself. For enterprise solutions, I fully agree with SQL Server. But for smaller businesses, even SQL Server Express is overkill. During my searches I found SQLite. It is supposedly faster than SQL CE and supported most of the SQL syntax making a perfect match for my skills, but most importantly, it was easy as sin to configure and use.
Of course, I settled on Nhiberate, and eventually moved to Fluent NHibernate, and have ensured at every step that my applications could be configured on the fly to a use different DB provider and client, which actually means my applications aren't a derivative work or dependent upon the DB software so I don't need to release them under the same license. But for development I still didn't want the hassle of a full blown server based database. And my first "customer" was my fiance running a small alterations business who didn't need the overhead of a SQL Server install either.
Anyway, the first version of her application was a .Net 2.0 Windows Form application (so still not a huge fan of Microsoft, and Windows Forms did nothing to make me like them any more). And SQLite had fantastic drivers and even an ADO .Net provider for .Net 2.0 that worked great most of the time (sometimes it was fickle about your target build architecture), but nothing drastic.
Then, I found Silverlight and WPF. And .Net 4. And Windows Phone. And I was in love with MS products again. But getting a project using SQLite and NHibernate to compile in VS 2010 under .Net 4 was nightmare. I actually haven't upgraded my fiances project in about a year and a half (which says some great things about my V1 design) simply because I couldn't do it the way I wanted to easily.
So I installed Windows 8 on my dev machine. OH GOOD HEAVENS does this OS hate SQLite. I had this combination working before with the application I wrote for my brother in Windows 7, and it was painful, but it DID work. Anyway, a large part of it was that the SQLite project wasn't native .Net code and the project was much less active than it once was. True, I checked and they have released updated binaries which they work with .Net 4.0, they even mixed-mode binaries and x86 and x64 binaries. But you think any of them will work in my environment? Nope!
So, what do I do? I start looking at the list of drivers NHibernate does support. Originally I was thinking that maybe there was a new driver I needed when I saw one called CShardSQLiteDriver. Hmmm? What could this be? Turns out there was a port of SQLite to C#. So I pulled down the source, compiled under .Net 4 and VOILA! I have a client... and guess what it just works! No having to change target architecture, no funky 3rd party providers. And I can load the source into the same project so that I can actually debug through the client as it runs!!!!!!
And this brings me back to one of the things I discovered and ignored a while ago about the old SQLite client. Standard configuration... not a huge fan GUID's. Not knowing any better, since HEY all of my legacy apps actually work, all of surrogate keys which were GUID's were being stored as blobs of binary data. Some time after I wrote Veronica's app and she had been using it for a while I downloaded SQLite Pro 2009 which is something like SSMS for SQL. And I start writing queries, and HEY I can't join on tables or query the way I want against the GUID's. By this time, there are thousands of records and this is a production application, so I wasn't going to fix it for no reason.
The only 'gripe' I have with this CSharp port, is that it doesn't treat Guid's the same way as the original SQLite implementation. I say gripe in quotes, because I didn't really like the way it was done originally, so I technically find I agree with what is done in the CSharp port. So, while the application technically worked, it was unable to locate objects by Id, which also meant that ALL of the relationships were broken. But an interesting turn of fate... once the objects were loaded, NHibernate was able to dump the Guid into the ID column. So today I wrote a conversion utility, takes all of the entities, writes them into a new database with the ID's set as assigned and since the new DB was generated using the CSharp port, the CSharp client can correctly store and reference the Id's in the new database.
So as an added bonus, I can now view and query my GUID Id's properly (the reason I had wanted this in the first place was that sometimes Veronica wants reporting data that I don't have reports for in the application yet, the idea WAS to use something like SQLite Pro 2009 to generate a query to show her the data she wanted while I had time to write a proper report in the application). It also means I can continue developing these applications against a SQLite database without the headaches of the past.
Basically, this post is a round about way of saying... if you're a .Net developer and you like SQLite... use the CSharp port and not the original project. This isn't to trash the original SQLite project, this port likely wouldn't exist without it. And I think SQLite is fantastic, but as .Net 4 developer it has become a freaking nightmare. They also have pre-built clients for Silverlight, Windows Phone, Windows Mobile and of course .Net. It will compile and run like a dream in .Net 4, works great with the latest versions of NHibernate and Fluent NHibernate; my original application wasn't using Fluent, so it is running against straight NHibernate 3.2 but the conversion tool was using Fluent 1.2 which it built against NHibernate 3.1, and both apps work like a charm.
For those who are developers and have never used WPF or Silverlight. You are missing out. Honestly. The XAML and code-behind combined with the data binding done right makes this, in my opinion the greatest all around programming language I've seen. I'm not saying it does anything the best (except maybe the data binding bit), but I am saying that most other languages excel in a very small area. I may prefer a purely functional language if I'm developing a computationally intensive application. I may prefer Java if I want something cross platform. PERL is great for data mining and the list goes on. All of these languages DO other things, but the point is that they do only one or a few things either very well or easily.
Basically where I'm going with this is, I tend to write applications. Not libraries, or functions, or other small components. And I don't tend to write "apps" (by which I mean dinky one offs you might find on a phone or tablet). My applications tend to have a bit of everything. I typically have a proper database of some sort, a full out UI, an underlying collection of libraries full of a variety of functions, frequently a server and client side component, etc.... And while most languages and suites can do a lot, most cannot do all of that elegantly.
The .Net suite was the exception to all of that. While, like I said, it maybe isn't the best at all things, it is actually very good at just about everything. And Visual Studio is a great IDE to complement it.
But there was always one bit I couldn't find a satisfactory solution for from Microsoft. The actual database itself. For enterprise solutions, I fully agree with SQL Server. But for smaller businesses, even SQL Server Express is overkill. During my searches I found SQLite. It is supposedly faster than SQL CE and supported most of the SQL syntax making a perfect match for my skills, but most importantly, it was easy as sin to configure and use.
Of course, I settled on Nhiberate, and eventually moved to Fluent NHibernate, and have ensured at every step that my applications could be configured on the fly to a use different DB provider and client, which actually means my applications aren't a derivative work or dependent upon the DB software so I don't need to release them under the same license. But for development I still didn't want the hassle of a full blown server based database. And my first "customer" was my fiance running a small alterations business who didn't need the overhead of a SQL Server install either.
Anyway, the first version of her application was a .Net 2.0 Windows Form application (so still not a huge fan of Microsoft, and Windows Forms did nothing to make me like them any more). And SQLite had fantastic drivers and even an ADO .Net provider for .Net 2.0 that worked great most of the time (sometimes it was fickle about your target build architecture), but nothing drastic.
Then, I found Silverlight and WPF. And .Net 4. And Windows Phone. And I was in love with MS products again. But getting a project using SQLite and NHibernate to compile in VS 2010 under .Net 4 was nightmare. I actually haven't upgraded my fiances project in about a year and a half (which says some great things about my V1 design) simply because I couldn't do it the way I wanted to easily.
So I installed Windows 8 on my dev machine. OH GOOD HEAVENS does this OS hate SQLite. I had this combination working before with the application I wrote for my brother in Windows 7, and it was painful, but it DID work. Anyway, a large part of it was that the SQLite project wasn't native .Net code and the project was much less active than it once was. True, I checked and they have released updated binaries which they work with .Net 4.0, they even mixed-mode binaries and x86 and x64 binaries. But you think any of them will work in my environment? Nope!
So, what do I do? I start looking at the list of drivers NHibernate does support. Originally I was thinking that maybe there was a new driver I needed when I saw one called CShardSQLiteDriver. Hmmm? What could this be? Turns out there was a port of SQLite to C#. So I pulled down the source, compiled under .Net 4 and VOILA! I have a client... and guess what it just works! No having to change target architecture, no funky 3rd party providers. And I can load the source into the same project so that I can actually debug through the client as it runs!!!!!!
And this brings me back to one of the things I discovered and ignored a while ago about the old SQLite client. Standard configuration... not a huge fan GUID's. Not knowing any better, since HEY all of my legacy apps actually work, all of surrogate keys which were GUID's were being stored as blobs of binary data. Some time after I wrote Veronica's app and she had been using it for a while I downloaded SQLite Pro 2009 which is something like SSMS for SQL. And I start writing queries, and HEY I can't join on tables or query the way I want against the GUID's. By this time, there are thousands of records and this is a production application, so I wasn't going to fix it for no reason.
The only 'gripe' I have with this CSharp port, is that it doesn't treat Guid's the same way as the original SQLite implementation. I say gripe in quotes, because I didn't really like the way it was done originally, so I technically find I agree with what is done in the CSharp port. So, while the application technically worked, it was unable to locate objects by Id, which also meant that ALL of the relationships were broken. But an interesting turn of fate... once the objects were loaded, NHibernate was able to dump the Guid into the ID column. So today I wrote a conversion utility, takes all of the entities, writes them into a new database with the ID's set as assigned and since the new DB was generated using the CSharp port, the CSharp client can correctly store and reference the Id's in the new database.
So as an added bonus, I can now view and query my GUID Id's properly (the reason I had wanted this in the first place was that sometimes Veronica wants reporting data that I don't have reports for in the application yet, the idea WAS to use something like SQLite Pro 2009 to generate a query to show her the data she wanted while I had time to write a proper report in the application). It also means I can continue developing these applications against a SQLite database without the headaches of the past.
Basically, this post is a round about way of saying... if you're a .Net developer and you like SQLite... use the CSharp port and not the original project. This isn't to trash the original SQLite project, this port likely wouldn't exist without it. And I think SQLite is fantastic, but as .Net 4 developer it has become a freaking nightmare. They also have pre-built clients for Silverlight, Windows Phone, Windows Mobile and of course .Net. It will compile and run like a dream in .Net 4, works great with the latest versions of NHibernate and Fluent NHibernate; my original application wasn't using Fluent, so it is running against straight NHibernate 3.2 but the conversion tool was using Fluent 1.2 which it built against NHibernate 3.1, and both apps work like a charm.
Comments
Post a Comment