Tag Archives: sqlite

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

Using SQLite 3 with Unicode in Delphi

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.

Updated SQLite wrapper for Embarcadero Delphi (and Free Pascal)

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.

Back to BASIC with NS App Studio for mobile

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.