One of the key differentiators that SQLite for .Net has is that you have full access to the source code and can compile it. Its interesting in the fact most commercial developers like this option, but rarely use it. Kind of a ‘checkbox’ – “if I need to can I?”. This article covers how to get the source code from the archive and compile it up. I find this predominately useful if you are isolating a crash, or trying to determine if there is an option you can ‘flip’ to get an application to behave the way you want to.

The first thing to do is go and grab the latest source! The source code is currently hosted on SourceForge at: http://sourceforge.net/projects/sqlite-dotnet2/. If you go to this page you there are links (or there were at the time of writing!!) to “Download Now!” or “View all files“. If you are looking for the source you need to chose “View all Files”. From here you should choose the latest source package (currently “SQLite-1.0.63.0-source.zip”) and download and unzip to your drive. You should now have the latest and greatest ‘stable’ source code, if you need to pick up an in flight fix or need to most up to date you may have to use CVS to download the package. Unfortunately (albeit fortunate for this article) “SQLite-1.0.63.0-source.zip” contains an issue where the build didnt completely work straight out of the box for Win32 builds. I contacted the team through the developer forum (http://sqlite.phxsoftware.com/forums/) and the problem was resolved in the latest CVS code patch (Patchset 415). So given this we need to pull it from CVS rather than the prepackaged code. NOTE: Normally speaking you should only need (and I would recommend only) the packaged code rather than CVS. Only if you want to live on the bleeding edge, or have a real need to get the latest release should you really use the CVS version. If fact the team doesnt even recommend using the CVS version (http://sqlite.phxsoftware.com/forums/t/1814.aspx).

However to complete the full circle and want to go down the CVS route and you are not familiar with CVS you can find out more information here. Raw CVS executables for Windows can be found here, but I would recommend if you are going to make a habit out of it you get and use TortoiseCVS instead.

cvs -d:pserver:anonymous@sqlite-dotnet2.cvs.sourceforge.net:/cvsroot/sqlite-dotnet2 login
cvs -z3 -d:pserver:anonymous@sqlite-dotnet2.cvs.sourceforge.net:/cvsroot/sqlite-dotnet2 co -P SQLite.NET

[Note if the first line fails create an empty ".cvspass" file and retry it]

Now you should have the latest code straight from the CVS repository, or if you are more sensible the packaged ZIP file covered at the beginning of the article.

Now on to the building! In the top level folder there are 2 interesting/key files:

• readme.htm – This details the latest release notes associated with the provider code, and covers the differences between the various historical releases.
• SQLite.NET.sln – This is our well loved and trusted VS2008 solution file!

Starting the solution file the solution loads up and with the latest version I’m using8 projects are loaded:

• SQLite.Interop
• System.Data.SQLite – Compact
• System.Data.SQLite – Managed Only
• System.Data.SQLite – Netmodule
• System.Data.SQLite.Linq
• test
• testce
• testlinq

Out of the box you should be able to build the release and debug files. You will also notice slightly different icons associated with the projects “System.Data.SQLite – Compact” & “testce”. This is because these builds are targeting a mobile or Compact framework device.  They dont hurt to be there so feel free to ignore them as they wont be used unless you a compact/mobile build type.

Congratulations you should have successfully built the components and can verify your build by running the “test” application.

Now on to the more technical deep dive of how it works

Firstly lets be clear – this is certainly a non-trivial build process! This is merging C code & .Net code into the same binary DLL using netmodules. More on netmodules can be found at “How to: Build a Multifile Assembly“. Frankly I’m going to just dive into a little more detail here as this concept is key to the build.

Purely looking at the project in Visual Studio is a little misleading:

Hmmm – a “Windows Application” – really…. However looking at the “System.Data.SQLite – netmodule.csproj” in a text viewer you see something very different:

<OutputType>Module</OutputType>

Obviously this is very different than “<OutputType>Library</OutputType>”, “<OutputType>WinExe</OutputType>”, “<OutputType>Exe</OutputType>”. This is the netmodule magic we covered above – all this is largely hidden from the user if they just look at the VS interface. This magic is then referenced through the SQLite.Interop.vcproj though:

<Tool
Name="VCLinkerTool"
AdditionalDependencies="..\System.Data.SQLite\bin\System.Data.SQLite.netmodule"
OutputFile="../bin/System.Data.SQLite.DLL"
ModuleDefinitionFile="src\sqlite3.def"
EmbedManagedResourceFile=""
DelayLoadDLLs="advapi32.dll"
RandomizedBaseAddress="1"
DataExecutionPrevention="0"
ImportLibrary=""
TargetMachine="5"
KeyFile="..\System.Data.SQLite\System.Data.SQLite.snk"
CLRUnmanagedCodeCheck="true"
/>

The good news about this is that this is just a ‘dependency’ and is fully supported in the C/C++ interface in VS2008. A little more can be read about here “.netmodule Files as Linker Input”

Alright so we now have the background knowledge to understand how this build is actually being used. So now lets look at the what the build sets up for us (the below is really the Win32/x64 build):

• Debug
  • SQLite.Interop
  • System.Data.SQLite – Managed Only
  • System.Data.SQLite.Linq
  • test
  • testlinq
• Debug – Stock
  • System.Data.SQLite – Managed Only
  • System.Data.SQLite.Linq
  • test
• Release
  • SQLite.Interop
  • System.Data.SQLite – Netmodule
  • System.Data.SQLite.Linq
  • test
• Release – Stock
  • System.Data.SQLite – Managed Only
  • System.Data.SQLite.Linq
  • test

The project dependencies are:

• SQLite.InterOp -> System.Data.SQLite – netmodule
• System.Data.SQLite.Linq -> SQLite.InterOp
• Test -> SQLite.InterOp

You can see that only the “Release” build doesn’t include the “System.Data.SQLite – Managed Only” version. Any build other than this one generates a build that requires both “System.Data.SQLite” and “SQLite.Interop.DLL”. The “Managed Only” isn’t a pure managed only version of SQLite, it still uses the native SQL C runtime code – but it makes the call out to the Interop dll. The ‘netmodule’ version is used to embed in the ADO.NET C# classes into the ‘System.Data.SQLite’ DLL, all other builds of ‘System.Data.SQLite’ just reference the interop DLL.

Release Build

As mentioned before, in the release build SQLite.Interop is where all the magic happens in this build! This is the one that outputs bin\System.Data.SQLite.DLL (note in all other builds this generates ‘SQLite.Interop.dll’ or in the stock case ‘sqlite3.dll’). However since this is really compiling the standard C SQLite code there is a little more magic going on here. This project has the linker dependency on “..\System.Data.SQLite\bin\System.Data.SQLite.netmodule”. This is how we get the C# ADO.NET provider injected into the System.Data.SQLite.DLL through the netmodules. However this process has the side effect of not being able to debug into the provider . So for source code debugging use the debug build that references “Managed Only” rather than the netmodule.

Debug Build

From now on it is fairly standard, although I have seen an odd quirk is that if you attempt to debug into the regular “Debug Build” an error is through regarding the “‘OutputType’ failed”, if you use the “Debug – Stock” build this goes away as the ‘netmodule’ is not included in the build.

Happy compiling and debugging!

Related Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

Alright – so we are now ready to get started in earnest. The first thing we need to do when connecting to a database is setup the connection string. This article covers the various connection string parameters available for the .Net version for SQLite.

Most Simple – Data Source
Format: “Data Source=[DBFileName]”
Example: @”Data Source=C:\CSharpHackerDemo.db3″

This connection string will create the database if it doesnt already exist without notifying you. For some utility functions this may be exactly what is desired, in some cases you want to know your DB doesnt exist!

If you want to programatically create the file intentionally, presumably if  “!File.Exists()” you can do:

SQLiteConnection.CreateFile(@"C:\CSharpHackerDemo.db3");

Only Open if it exists – FailIfMissing
Format: “Data Source=[DBFileName];FailIfMissing=True;”
Example: @”Data Source=C:\CSharpHackerDemo.db3;FailIfMissing=True;”

With this connection string if the DB file is not present the open will fail.

Secure/Encrypt the DB – Password
Format: “Data Source=[DBFileName];Password=[123456789];”
Example: @”Data Source=C:\CSharpHackerDemo.db3;Password=[123456789];”

With this connection string if the DB file will be created or opened using the password as the encryption key.

Other options:

• ReadOnly – When enabled, the database will be opened for read-only access and writing will be disabled. Default False.
• CacheSize – Sets the cache size for the connection. Default value is 2000 bytes. Under the covers this calls “PRAGMA cache_size={0}”.
• PageSize – Sets the database page size. Under the covers this calls “PRAGMA page_size={0}”. Default is 1024 bytes.
• MaxPageCount – Sets the maximum number of pages the database may hold. Default is 0 which is unrestricted. Under the covers this calls “PRAGMA max_page_count={0}”.
• DateTimeFormat - Sets the datetime format for the connection. Default is ISO8601
  • Ticks - Using ticks is not recommended and is not well supported with LINQ.
  • ISO8601 - Default format for this provider.
  • JulianDay - JulianDay format, which is what SQLite uses internally
• JournalMode - Determines how SQLite handles the transaction journal file.  Under the covers this calls “PRAGMA journal_mode={0}”. Default “Delete”
  • Delete - default mode, this causes SQLite to create and destroy the journal file as-needed.
  • Persist - When this is set, SQLite will keep the journal file even after a transaction has completed.  It’s contents will be erased, and the journal re-used as often as needed.  If it is deleted, it will be recreated the next time it is needed.
  • Off - This option disables the rollback journal entirely.  Interrupted transactions or a program crash can cause database corruption in this mode!
• DefaultIsolationLevel - Sets the default isolation level for transactions on the connection. Default “Serializable”. Note this is a standard .Net “System.Data.IsolationLevel” type. Do not even try to change this . Only “Serializable” and “ReadCommitted” can be used, all others will throw a “NotSupportedException”. However for completeness the full list and meanings are outlined below:
  • Serializable - A range lock is placed on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.
  • ReadUncommitted – A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.
  • ReadCommitted - Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.
  • RepeatableRead - Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.
  • Snapshot - Reduces blocking by storing a version of data that one application can read while another is modifying the same data. Indicates that from one transaction you cannot see changes made in other transactions, even if you requery.
  • Chaos - The pending changes from more highly isolated transactions cannot be overwritten.
  • Unspecified - A different isolation level than the one specified is being used, but the level cannot be determined.
• Version – Sets the default version of the SQLite engine to instantiate.  Currently the only valid value is 3, indicating version 3 of the SQLite library.
• Synchronous – Sets the synchronization mode (file flushing) of the connection string.  Default is “Normal”.  Under the covers this calls “PRAGMA synchronous={0}”. Supported values are:
  • Normal – Normal file flushing at critical sections of the code
  • Full – Full file flushing after every write operation
  • Off – Use the default operating system’s file flushing, SQLite does not explicitly flush the file buffers after writing
• UseUTF16Encoding – Boolean indicator setting the encoding for the connection.  The default is “False” which indicates UTF-8 encoding.
• Pooling – Sets whether or not to use connection pooling.  The default is “False”
• BinaryGUID - Sets to or not to store GUID’s in binary format.  The default is True which saves space in the database.
• Deprecated Uri – depreciated call to the data source property.
• DefaultTimeout – sets the default command timeout for newly-created commands.  This is especially useful for commands used internally such as inside a SQLiteTransaction, where setting the timeout is not possible. Time is in seconds, the default is 30 seconds.
• Enlist – Determines whether or not the connection will automatically participate in the current distributed transaction (if one exists). Default is True.
• LegacyFormat – If enabled, uses the legacy 3.xx format for maximum compatibility, but results in larger database sizes. Default is False. Under the covers this calls “PRAGMA legacy_file_format={0}”.

So this is the connection string options available on the latest release. The interesting part is how I found this – in the source code (so at least now you dont need to do that part). However the next blog will be how to build the provider to help you diagnose and understand any issues you come across.

Series Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

 SQLiteConnection.CreateFile("c:\\mydatabasefile.db3"); SQLiteConnection.CreateFile("c:\\mydatabasefile.db3");

So before I start I dont want to get into any religious debates on if this is a feature,  a quirk or something else. The goal here is to identify areas where SQLite behaves differently than either other DB systems or just in an unexpected/unanticipated way.

So starting with the biggies:

1) Limited Database enforced Type Safety:

CREATE TABLE  IF NOT EXISTS Book
(
BookISBN     varchar(14) primary key
,Name         varchar(200)
,PublisherId    int
);

INSERT INTO BOOK(BookISBN,Name)
VALUES(
     "ReallyLongValueThatShouldCauseAProblem"
    , "CSharpHacker SQLite Manual");

Most DBs honor the restrictions of the character lengths. SQLite will record the information you submit to it – even it is technically breaks your DDL definitions. More about this can be read about it Datatypes In SQLite Version 3. What should specifically be called out is:

Any column in a version 3 database, except an INTEGER PRIMARY KEY column, may be used to store any type of value.

So a more outlandish example would be:

DROP TABLE IF EXISTS Issue;
CREATE TABLE  IF NOT EXISTS Issue
(
 IssueId 	int
,Description	varchar(200)
);
INSERT INTO Issue(IssueId,Description)
VALUES(
     "ReallyLongTextValueThatShouldCauseAProblem"
     , "CSharpHacker SQLite Manual");
SELECT * from Issue;

If you run the above you get “ReallyLongTextValueThatShouldCauseAProblem|CSharpHacker SQLite Manual”, with no errors!
Is that the sound of DBA’s running away screaming in horror…? Believe it or not this was intentional, and ‘sometimes’ sensible . I’ll cover a little more on that later, but lets go full circle on this.

Underneath the covers there is a 64 bit row identifier attached to every row, but if you want to use your own you can override that to use your own values. If you do this then this column categorically has to be an INTEGER value. So looking at the SQL below you can see we have now changed the type from ‘int’ to ‘INTEGER PRIMARY KEY’. This now enforces you have a value that is an integer for this one column, and the below fails with a “SQL error near line xx: datatype mismatch”.

DROP TABLE IF EXISTS Issue2;
CREATE TABLE  IF NOT EXISTS Issue2
(
 IssueId 	INTEGER Primary key
,Description	varchar(200)
);
INSERT INTO Issue2(IssueId,Description)
VALUES(
     "ReallyLongTextValueThatShouldCauseAProblem"
    , "CSharpHacker SQLite Manual");
SELECT * from Issue2;

So one final warning “INTEGER PRIMARY KEY” is the same as “integer primary key”, but not “int primary key”. If you are wanting to override the internal 64 bit row identifier you have to use “integer” and not “int”, “bigint”, “short integer”. Thats an easy one to miss! Final quirk is that the “INTEGER PRIMARY KEY” needs to be exactly that or “INTEGER PRIMARY KEY ASC” (case insensitive), but “INTEGER PRIMARY KEY DESC” will not use the internal ID.

2. Only one process can write to the DB at the same time

The locking in SQLite is not fine grained! If anyone is writing to the DB it locks everyone and everything else out of the DB. All readers and writers are blocked until the pending write is done. So if you are expecting a lot of concurrent writes to the database that may need to be interleaved with some reads from other threads/processes you may experience some blocking. In reality it shouldnt matter too much as the writes are normally speedy. It should also be noted that in SQLite v2 there was a possibility of writer starvation where so many reads are going on the writer was never able to lock the file to perform the write – this has been addressed in SQLite V3.

3. Limited support for ALTER TABLE

Now I bet this has driven off the DBA’s now! Because of its heritage SQLite team hasn’t ever needed to support the more complex data migrations supported by other database products. You can use to add a column to the end of a table or to change the name of a table. However if you want to make more complex changes in the structure of a table, you have to recreate a new table and then copy the contents of your old table into the new one.

4. Foreign Keys are ‘recorded’ but not enforced

Technically the same thing can be achieved using the ‘Triggers’, but you need to be aware that these are ‘soft RI’ keys and are not enforced by the database engine.

5. Support for UPSERT via “REPLACE” or “INSERT OR REPLACE”

For MySQL users they are familiar with REPLACE, SQLite has aliased REPLACE to its native “INSERT OR REPLACE’.

So if you are still reading that is good! This is definitely the scary page that drives a few DBA’s into fits, but its sensible to know what you are using and its behaviors before it surprises you.

Related Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

Well I would actually advocate the question/title of this topic is misplaced. This is really the question I would have asked probably 4 months ago, and in all likelihood dismissed SQLite after hearing the answers! So bear with me, its worth the read! The key question that really should be asked is not how it stacks up, but what are its strengths and in what circumstances should I use it. In essence what problems is SQLite designed to solve rather than academic bullet point comparisons.

However back to the question – How does it stack up?

As you can see SQL Server definitely has the most features, in fact as far as features go its a slam dunk. However often feature count just isn’t everything. The major drawback for SQL Server is that it requires a Server process to run, and that then means maintenance, probably a DBA and all of a sudden things have got complicated (and expensive)! So we come back to the question “What are we looking to do with the database?”. If the answer is heavy duty processing that requires stored procedures, cubes and large amount of concurrent remote access by users – SQL Server is definitely your answer. However I have to think that you are reading this because SQL Server is too heavy weight for your needs. So on to the other candidates.

SQL CE is a Microsoft database that started in the mobile space and moved up to the PC platform. Overall is has reasonable features but is known to be slower and heavier than SQLite. It definitely has some strengths in the SQL Server ==> SQL CE replication, but outside of that its largely even or less featured that SQLite. If you are looking for replication definitely checkout SQL CE, but I would also say the Microsoft Synch framework is evolving and we may see a native SQLite provider (or determine that the ADO.NET is sufficient) in this area.

SQLite is a lightweight add-on to C# (System.Data.SQLite.DLL ~ 850Kb). You may note this is heavier than the often referenced SQLite runtimes take “200Kb”, but in this 850Kb (457Kb Zipped) is the SQL DLL, ADO.NET SQLite provider and the magic glue to bring these two worlds together. If you are using .Net you can assume you have to assume your package size will go up by 457Kb, and the runtime size on disk will be ~850Kb. Performance of SQLite ranks it above SQL CE for insert tests, and it has a lower file size – and the database can be assessed on a massive number of operating systems!

Before you jump whole heartedly on the SQLite bandwagon, it is only fair to also point out SQLite has some quirks (SQLite folks call these ‘features’ – I’m not going to argue, but its approach is different in a couple of cases). I’m going to cover these in the next blog entry “Part 5″. These are things that you definitely need to be aware of rather than discover!

The key question that the article should be asking is “What type of application features benefits from SQLite?”. The best way I can describe SQLite is that is is an “Application Data Store” that you then have ANSI-92 SQL access to. This removes the needs of rolling your own indexing into files, creating convoluted file system ‘databases’ etc. If you can wrap your head around how you can use SQLite to your advantage I think you will be pleasantly surprised.

Related Links:

• SQLite for C# – Part 1 – Am I allowed to use it?
• SQLite for C# – Part 2 – How do I setup a SQLite DB (without coding)
• SQLite for C# – Part 3 – My first C# app using SQLite aka Hello World
• SQLite for C# – Part 4 – So how does SQLite stack up against other DB’s?
• SQLite for C# – Part 5 – SQLite ‘features’, or ‘quirks’
• SQLite for C# – Part 6 – SQLite Connection String Definitions
• SQLite for C# – Part 7 – Building SQLite.Net from source
• SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line