Tag Archives: sqlite

.net, database, microsoft, professional, software development, tech

SQLite with .NET: excellent but some oddities

I have been porting a C# application which uses an MDB database (the old Access/JET format) to one that uses SQLite. The process has been relatively smooth, but I encountered a few oddities.

One is puzzling and is described by another user here. If you have a column that normally stores string values, but insert a string that happens to be numeric such as “12345”, then you get an invalid cast exception from the GetString method of the SQLite DataReader. The odd thing is that the GetFieldType method correctly returns String. You can overcome this by using GetValue and casting the result to a string, or calling GetString() on the result as in dr.GetValue().ToString().

Another strange one is date comparisons. In my case the application only stores dates, not times; but SQLite using the .NET provider stores the values as DateTime strings. The SQLite query engine returns false if you test whether “yyyy-mm-dd 00:00:00” is equal to “yyy-mm-dd”. The solution is to use the date function: date(datefield) = date(datevalue) works as you would expect. Alternatively you can test for a value between two dates, such as more than yesterday and less than tomorrow.

Performance is excellent, with SQLite . Unit tests of various parts of the application that make use of the database showed speed-ups of between 2 and 3 times faster then JET on average; one was 8 times faster. Note though that you must use transactions with SQLite (or disable synchronous operation) for bulk updates, otherwise database writes are very slow. The reason is that SQLite wraps every INSERT or UPDATE in a transaction by default. So you get the effect described here:

Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive. A transaction normally requires two complete rotations of the disk platter, which on a 7200RPM disk drive limits you to about 60 transactions per second.

Without a transaction, a unit test that does a bulk insert, for example, took 3 minutes, versus 6 seconds for JET. Refactoring into several transactions reduced the SQLite time to 3 seconds, while JET went down to 5 seconds.

database, software development, tech

SQLite adds support for .NET Core 2.0 and .NET Standard 2.0

image

The open source SQLite database engine goes from strength to strength, largely by not changing that much: it remains small, fast, reliable, cross-platform, and completely free. The engine is written in C but there are many wrappers for different languages, a recent addition being .NET Core 2.0 and .NET Standard 2.0:

1.0.109.0: Add preliminary support for .NET Core 2.0 and the .NET Standard 2.0. Pursuant to [5c89cecd1b].

.NET developers using SQLite are fortunate in that System.Data.SQLite, the .NET provider, is supported by the SQLite team and has its own sub-site on sqlite.org. “The SQLite team is committed to supporting System.Data.SQLite long-term,” states the home page.

The addition of .NET Core 2.0 support is valuable, in part because .NET Core is where Microsoft’s energy is now focused, and will make it easier to write cross-platform code. There is a snag though: there is no official cross-platform GUI for .NET Core, which would be useful for SQLite given that it is a local database engine. However, Microsoft’s Xamarin framework, which is cross-platform, does support .NET Standard 2.0 so this should work though I have not tried it.

The truth is that almost any framework can be made to work with SQLite. I did some work myself on a wrapper for Delphi (Object Pascal) which still has some users today.

Back in 2007 I interviewed SQLite’s creator, Dr Richard Hipp, for Guardian Technology. Worth a read if you are wondering why SQLite, unlike most open source projects, has no licence: it is simply public domain:

“I looked at all of the licences,” Hipp says, “and I thought, why not just put it in the public domain? Why have these restrictions on it? I never expected to make one penny. I just wanted to make it available to other people to solve their problem.”

development, open source, professional, software, software development

Updating the world’s most widely deployed SQL database engine: welcome to SQLite 4

A new version of SQLite is in preparation. If you are not a developer, you might not have heard of SQLite, but you have almost certainly used it. It is built into Mac OS X and numerous web browsers, used by many applications which run on Adobe’s Flash runtime, and is the obvious choice if you want a small, fast and reliable database engine to embed into an application. It is open source and as free as you can get:

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

SQLite3 is the current version; but now there is an update to version 4:

SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired.

The reason for the new version is that some issues in version 3 could not be fixed without breaking compatibility.

So what is new? On a quick read, these seem to be the highlights:

  • A global configuration object (sqlite4_env) which eliminates all use of global and static variables.
  • A new key/value storage engine which has a “greatly simplified” interface and which is pluggable, so you can use a different one if required. The default storage engine is described as a “log-structured merge database”. A B-Tree engine may also be offered later.
  • Primary keys are now real primary keys, as opposed to unique constraints. This speeds up primary key searches.
  • Decimal maths. “All numeric values are represented internally as an 18-digit decimal number with a 3-digit base-10 exponent.” This is advantageous for currency calculations and for cross-platform consistency.
  • Foreign key constraints and recursive triggers on by default
  • Covering support in indexes (when required), to increase the number of queries that can be resolved by querying the indexes alone, at the expense of greater duplication of data

When will SQLite 4 be ready? Code is available but Author D Richard Hipp says:

Everything is still pretty makefile-touchy. Remember, this is like pre-alpha code. It works, but just barely. And things are changing rapidly.

Porting an application from SQLite3 to SQLite4 should be straightforward, according to the author. “An hour or two search-and-replace.”

image

database, delphi, embarcadero, software development

Using SQLite 3 with Unicode in Delphi

2 Comments

I’ve updated my SQLite3 Delphi wrapper for Unicode in Delphi 2009 and higher. Previous versions of the wrapper ducked the issue by using Ansi strings throughout.

image

I actually used Embarcadero Delphi XE for the development, but I would expect it to work in Delphi 2009 and higher, since it was in that version that Delphi first properly supported Unicode. Converting older Delphi projects is meant to be seamless, except in cases where you are using pointers or doing interop with native DLLs; of course this wrapper does both.

The SQLite 3 API expects either UTF8 or Unicode strings. To be more precise, some functions have Unicode versions indicated with a “16” suffix, and some do not, in which case they expect UTF8 if they accept string values. Although UTF8 strings support Unicode characters, most characters generally occupy a single byte just as in Ansi strings, so one of the things I discovered was that I could not simply rely on PChar, Delphi’s null-terminated string type, which from Delphi 2009 is a Unicode type with double-byte characters. Instead, for cases where the SQLite 3 API expects a UTF8 string, I have used PAnsiChar as before.

It is all somewhat confusing, and there are a few cases where Delphi does not do quite what you would expect. I recommend Marco Cantu’s paper Delphi and Unicode [pdf], one of the best resources I found. This article by Nick Hodges on Unicodifying your code is handy too.

Finally, in the example I keep an object in memory and it is easy to end up with code paths that do not free it. I love this feature of Delphi (since at least 2007) which informs you of your mistake when the application closes:

image

I have uploaded the code and you can find it linked here.

Update: My assumption that Delphi 2010 would work the same way as Delphi XE was incorrect. I have some code that reads a blob field containing a UTF8 string and returns it as a string. I read the stream into a byte array and then cast it to a UTF8String:

str := UTF8String(bytes);

where str is a UTF8string variable. This worked in Delphi XE, but in Delphi 2010 I got garbage. I have modified the code to use a TStringStream and now it works in both.

delphi, embarcadero, open source, professional, software, software development

Updated SQLite wrapper for Embarcadero Delphi (and Free Pascal)

6 Comments

A while back I worked on a Delphi wrapper for SQLite 3, which I published on this site as an open source project. Others amended it to support Free Pascal and Lazarus, so you can use it on Linux and on the Mac. I’ve not touched it for a couple of years; but recently received an email requesting support for the SQLite 3 backup API. There are only a few functions involved so I added them to the wrapper, and also updated the Sqlite DLL to version 3.7.5.

Although I used Delphi XE to work on the wrapper, I did the build for the repository with Delphi 7, running in a virtual machine, because I know this version still has plenty of use. The problem though is that Delphi 2009 introduced full Unicode support, causing compatibility issues. My wrapper is compromised because it uses the old AnsiString, so that it works with all Delphi versions. I have in mind to fix this so you get full Unicode support when I have time to do so.

There is no support for Delphi’s data binding, and the wrapper appeals to developers happy to code their own SQL. Of course it works like lightning.

apple, google, mobile, professional, software development, web authoring

Back to BASIC with NS App Studio for mobile

8 Comments

I was intrigued to discover NS Basic/App Studio, which offers a simple Windows IDE targeting iPhone and Android mobile devices.

image

It is all a bit retro, especially when you discover that the company (NS Basic Corporation) has the leading Basic on the … Apple Newton.

Still, something like Visual Basic for iPhone and Android sounds interesting. Does this thing deliver?

I tried the demo. What NS App Studio actually does is to translate Basic code to JavaScript, so the end result is a web application targeting mobile browsers, rather than a mobile app. There is a bit more to it though. Apps have access to local storage including SQLite databases, since this is available to the WebKit-based browsers on iPhone and Android. You can create a shortcut to a web app and even run it offline, making it behave somewhat like a locally installed app. Further, the FAQ notes that you can wrap your web app with PhoneGap to create an app that you can distribute through the App Store or Android Market; and this or similar capability may eventually be included in the IDE.

The question though: why would you choose to use Basic rather than just learning JavaScript? I can make sense of the Google Web Toolkit, which compiles Java to JavaScript, but Google’s effort is more sophisticated. You are not expected to puzzle out the generated JavaScript, but just work in Java. By contrast, with NS App Studio you code in Basic but debug in JavaScript, with all sorts of potential for confusion.

I got the impression that the product is not yet mature. I changed the name of the form in my Hello World project, for example, but found generated code that still referred to the old name, causing a JavaScript error. I found it confusing that the property listed as “text” in the visual grid was “textContent” in code. The IDE is very simple, but also very lacking in features. Most developers would find a modern JavaScript IDE more productive.

Nevertheless it is interesting as a proof of concept, and shows the capability of these mobile browsers as a pre-installed application runtime.