In (early) praise of SQLite and EF4
Folks, just in case anyone was wondering ... my experiments so far indicate that SQLite works nicely with Entity Framework 4. I expected horrors, but I guess the authors of the SQLite ADO provider have obeyed the rules. A couple of little irritations have surfaced. The INTEGER type in SQLite which is used for primary keys is an Int64 whereas under SQL Server and SQL CE it was an Int32. I had to adjust my code to prevent cast errors on ExecScalar calls. Bulk inserts of a thousand rows or so into SQLite tables seems excruciatingly slow. Perhaps there is some bulk insert trick or transaction gotcha affecting this that I'm not aware of yet. I have UNIQUEIDENTIFIER columns in the SQL Server tables I'm migrating to SQLite, but my reading so far is confusing about what underlying string value is expected to be stored by the caller in such a column in SQLite. I guess it's any string value which can be TypeConveter'd to and from a GUID. That's fine, but I can't find a NEWID() equivalent and some people seem to have invented their own ones by a messy combination of functions. I gave up and used a BIGINT SQLite column instead and filled with DEFAULT (ABS(RANDOM()). This seems like a good replacement for a GUID. I'm still unsure about the corresponding CLR type widths for INT, INTEGER, BIGINT, etc. More reading will sort this out I guess. I haven't pushed EF4 really hard yet other than proving it works, but it looks promising for more intense work. This is great, as I'm planning to re-jig one of my apps so that it uses SQLite and has absolutely no install footprint like it used to have with SQL CE. Greg
Re: In praise of SQLite
Greg, Now that you are a convert just take go one step further and get LightSpeed from our friends over the Tasman: http://www.mindscape.co.nz/products/lightspeed/ It works superbly with SQLIte, has a great designer and you'll feel like you never want to use any other DB. It has some small limitations (take care of group by/sum/count if there is no data) but except that it is fantastic. Corneliu. On Sun, Dec 5, 2010 at 11:13 AM, Greg Keogh g...@mira.net wrote: A couple of years ago I asked in here what people recommended for an “RDB lite” with little to no installation footprint. Some people recommended SQLite, but for some reasons I can’t remember I rejected it as unsuitable. I’ve been running with SQL CE since then simply because it was from Microsoft, it was free, well documented and it had a familiar style. CE does have an installation footprint, which is small, but still a nuisance sometimes. I just revisited SQLite http://www.sqlite.org/index.html and ran a few tests with a VS2010 solution and the ADO.NET providerhttp://sqlite.phxsoftware.com/. Hey, I’m impressed ... it just works. The ADO .NET code follows the familiar coding style (connection, command, adapter, etc). The raw SQLite uses a plain C API which is indigestible for .NET developers, so the provider is the miraculous part that makes things easy for us. Best of all, there is zero installation footprint, you just reference the DLL. The SQLite VS2010 designer is working but incomplete, so I look forward to seeing it expanded. I’m pretty sure I’m going to abandon CE in future projects and use SQLite instead. Cheers, Greg
In praise of SQLite
A couple of years ago I asked in here what people recommended for an RDB lite with little to no installation footprint. Some people recommended SQLite, but for some reasons I can't remember I rejected it as unsuitable. I've been running with SQL CE since then simply because it was from Microsoft, it was free, well documented and it had a familiar style. CE does have an installation footprint, which is small, but still a nuisance sometimes. I just revisited SQLite http://www.sqlite.org/index.html and ran a few tests with a VS2010 solution and the ADO.NET provider http://sqlite.phxsoftware.com/ . Hey, I'm impressed ... it just works. The ADO .NET code follows the familiar coding style (connection, command, adapter, etc). The raw SQLite uses a plain C API which is indigestible for .NET developers, so the provider is the miraculous part that makes things easy for us. Best of all, there is zero installation footprint, you just reference the DLL. The SQLite VS2010 designer is working but incomplete, so I look forward to seeing it expanded. I'm pretty sure I'm going to abandon CE in future projects and use SQLite instead. Cheers, Greg
RE: In praise of SQLite
Hi Greg, You may be aware of this, but there is a simple GUI tool at http://sqlitebrowser.sourceforge.net/ Also SQLite is the default db on the Android platform... Regards, Nic From: g...@mira.net To: ozdotnet@ozdotnet.com Subject: In praise of SQLite Date: Sun, 5 Dec 2010 11:13:01 +1100 A couple of years ago I asked in here what people recommended for an “RDB lite” with little to no installation footprint. Some people recommended SQLite, but for some reasons I can’t remember I rejected it as unsuitable. I’ve been running with SQL CE since then simply because it was from Microsoft, it was free, well documented and it had a familiar style. CE does have an installation footprint, which is small, but still a nuisance sometimes. I just revisited SQLite and ran a few tests with a VS2010 solution and the ADO.NET provider. Hey, I’m impressed ... it just works. The ADO .NET code follows the familiar coding style (connection, command, adapter, etc). The raw SQLite uses a plain C API which is indigestible for .NET developers, so the provider is the miraculous part that makes things easy for us. Best of all, there is zero installation footprint, you just reference the DLL. The SQLite VS2010 designer is working but incomplete, so I look forward to seeing it expanded. I’m pretty sure I’m going to abandon CE in future projects and use SQLite instead. Cheers, Greg
Re: In praise of SQLite
sqlite is amazingly ubiquitous these days. Most mozilla (or mozilla based) projects use sqlite for any relational storage (including html5 databases) - firefox, songbird, thunderbird, etc. - as did google gears. Other miscellaneous applications seem to favour it these days - calibre ebook manager, delicious monster etc. It is really easy to get started with, has plenty of tools available and works on all platforms. On Sun, Dec 5, 2010 at 12:52 PM, Nic Roche nicro...@hotmail.com wrote: Hi Greg, You may be aware of this, but there is a simple GUI tool at http://sqlitebrowser.sourceforge.net/ Also SQLite is the default db on the Android platform... Regards, Nic -- From: g...@mira.net To: ozdotnet@ozdotnet.com Subject: In praise of SQLite Date: Sun, 5 Dec 2010 11:13:01 +1100 A couple of years ago I asked in here what people recommended for an “RDB lite” with little to no installation footprint. Some people recommended SQLite, but for some reasons I can’t remember I rejected it as unsuitable. I’ve been running with SQL CE since then simply because it was from Microsoft, it was free, well documented and it had a familiar style. CE does have an installation footprint, which is small, but still a nuisance sometimes. I just revisited SQLite http://www.sqlite.org/index.html and ran a few tests with a VS2010 solution and the ADO.NET providerhttp://sqlite.phxsoftware.com/. Hey, I’m impressed ... it just works. The ADO .NET code follows the familiar coding style (connection, command, adapter, etc). The raw SQLite uses a plain C API which is indigestible for .NET developers, so the provider is the miraculous part that makes things easy for us. Best of all, there is zero installation footprint, you just reference the DLL. The SQLite VS2010 designer is working but incomplete, so I look forward to seeing it expanded. I’m pretty sure I’m going to abandon CE in future projects and use SQLite instead. Cheers, Greg
Re: In praise of SQLite
SQLite is the 900lb gorilla in the room of in-process databases. It is in every mozilla install, android device, iOS device. It's the default database for Adobe AIR apps. Skype. Symbian. McAffee. It is widely used by pretty much everyone except Microsoft. It's fast (in my tests it was about 2x faster than SQL CE 4 straight out of the box, although I recently made some perf changes to my code in the way it calls SQLite that make it about 10x faster than SQL CE 4, but I don't know much about tuning SQL CE perf). From my tests ESE from Microsoft is the only in-process database technology of comparable speed. But ESE doesn't have a SQL interface, full text search etc like SQLite does. SQLite has some weird notions about types, but it still manages to be very fast. Their approach to software reliability is quite interesting 600x more test code and scripts than actual SQLite code. Joseph On Sun, Dec 5, 2010 at 1:05 PM, Mark Ryall mark.ry...@gmail.com wrote: sqlite is amazingly ubiquitous these days. Most mozilla (or mozilla based) projects use sqlite for any relational storage (including html5 databases) - firefox, songbird, thunderbird, etc. - as did google gears. Other miscellaneous applications seem to favour it these days - calibre ebook manager, delicious monster etc. It is really easy to get started with, has plenty of tools available and works on all platforms. On Sun, Dec 5, 2010 at 12:52 PM, Nic Roche nicro...@hotmail.com wrote: Hi Greg, You may be aware of this, but there is a simple GUI tool at http://sqlitebrowser.sourceforge.net/ Also SQLite is the default db on the Android platform... Regards, Nic -- From: g...@mira.net To: ozdotnet@ozdotnet.com Subject: In praise of SQLite Date: Sun, 5 Dec 2010 11:13:01 +1100 A couple of years ago I asked in here what people recommended for an “RDB lite” with little to no installation footprint. Some people recommended SQLite, but for some reasons I can’t remember I rejected it as unsuitable. I’ve been running with SQL CE since then simply because it was from Microsoft, it was free, well documented and it had a familiar style. CE does have an installation footprint, which is small, but still a nuisance sometimes. I just revisited SQLite http://www.sqlite.org/index.html and ran a few tests with a VS2010 solution and the ADO.NET providerhttp://sqlite.phxsoftware.com/. Hey, I’m impressed ... it just works. The ADO .NET code follows the familiar coding style (connection, command, adapter, etc). The raw SQLite uses a plain C API which is indigestible for .NET developers, so the provider is the miraculous part that makes things easy for us. Best of all, there is zero installation footprint, you just reference the DLL. The SQLite VS2010 designer is working but incomplete, so I look forward to seeing it expanded. I’m pretty sure I’m going to abandon CE in future projects and use SQLite instead. Cheers, Greg -- w: http://jcooney.net t: @josephcooney
Re: In praise of SQLite
The most significant issue at the moment is an out of storage space error that starts occuring after a while of heavy use. Queries will no longer work. Even a simple select for a single value of a single column and no transactions to be found. The sqlce engine is somehow getting in to a state where it's not freeing up it's resources. I have thoroughly checked to be sure I'm disposing when I should. I suspect we are pushing (and hitting) the limits of the engine with our recent projects. Another really annoying problem is the way transactions are handled. ie, when you commit, you would (or at least I did) expect the data to be safely on the disk. In fact, its still in ram, waiting to be flushed. The default is a 10 second timeout and I think the best you can do is set it to 1 second. Combine this with the out of storage issue and the very real possibility the mobile device suddenly gets a flat battery and you get some major problems that are difficult to solve. My solution is an entire rework of the DAL to reduce the load on sqlce. This has to be ready for tomorrow :( Of course, there are some old favourites like never insert images into a sqlce database and never use any MS tools to create/edit the database because they can't handle foreign keys. David If we can hit that bullseye, the rest of the dominoes will fall like a house of cards... checkmate! -Zapp Brannigan, Futurama On Sun, Dec 5, 2010 at 15:08, Greg Keogh g...@mira.net wrote: However, I'm having so much trouble now with SQL CE (I've been working all this weekend because of it) that I've recently been considering looking at it again. Good grief! I considered SQL CE so pleasantly vanilla that I find it hard to believe you can get into such trouble. Do you have warnings for us?! Any opinions on relative performance, reliability and stability compared to SQL CE? I haven't pushed it hard enough yet to report anything. I'll report back after I give it a heavier bash, maybe in several weeks. There are CE vs SQLite issues I'm worrying about in the back of my mind such as locking, threading, synch with SQL Server, etc. We'll see... Greg
