Archive for the ‘SQLite’ Category

« Older Entries

SQLite updated webpage… now has search! Happy New Year!

Friday, January 8th, 2010

It seems the SQLite folks have added a significant, needed and often requested, feature to the SQLite site.  No the change we are talking about has nothing to do with the SQLite engine, but yes I’m talking about the web site. In the top right side of the website there is a new shiny unobtrusive “Search SQLite Docs…” search box.

So for all us who have spent time searching, or had pages pointed out to us :-) , for options  this is an excellent new years present!

Many thanks to the SQLite guys for both their product, and their handy dandy search feature!

Posted in General, SQLite | No Comments »

SQLite for C# – Part 8 – Loading CSV/Pipe into SQLite via command line

Saturday, September 19th, 2009

Ever wondered how hard it would be to load a CSV file into a SQLite database. I know how I would do it in code, no rocket science needed there! However in this case I wanted to really know the speed of doing this natively and really didn’t want to code anything!

So looking at what SQLite3.exe has too offer it pretty much supports it out of the box. Very nice :-)

Requirements:

  • Loading speed
  • Making the data to consuming applications available asap

While I love C# and frankly its hard to go back to C or C++, sometimes performance trumps the creature comforts we have become accustomed to.

Note: I did this without circling back to a C# implementation as I know the data and performance requirements  are tight and in this case I wanted max performance with no code! The biggest factor to a successful implementation is to ensure you use the tools best for the job, not just the ones you favor in that specific year.

So first things first – create a table to take the input

DROP TABLE IF EXISTS BookSales;
CREATE TABLE IF NOT EXISTS BookSales
(
   Store    int
  ,Date     varchar
  ,OrderReference varchar
  ,Line     int
  ,BookISBN varchar(14)
  ,Quantity int
  ,Price    int
, Primary Key (OrderReference,Line)
);

Next is the magic. We need to load the CSV into the table:

.separator "|"
.import BookSales.txt BookSales

Wow that was easy :-) . You can see we set the separator to be a pipe rather than comma in this case, then the import.

.IMPORT [FileName] [Table]

Now the database is ready to be queried! But if we want to take it just one stage further:

.output SummaryBookSales.csv
SELECT Store, Date, BookISBN, SUM(Quantity), SUM(Price)
FROM BookSales
GROUP BY Store, Date, BookISBN;

Now we output the results of our simple aggregation into a pipe separated output file.

Tying this all together in a single configuration file, which we will call “BookAnalysisLoader.sql”, gives us:

DROP TABLE IF EXISTS BookSales;
CREATE TABLE IF NOT EXISTS BookSales
(
   Store    int
  ,Date     varchar
  ,OrderReference varchar
  ,Line     int
  ,BookISBN varchar(14)
  ,Quantity int
  ,Price    int
, Primary Key (OrderReference,Line)
);

.separator "|"
.import BookSales.txt BookSales

.output SummaryBookSales.csv
SELECT Store, Date, BookISBN, SUM(Quantity), SUM(Price)
FROM BookSales
GROUP BY Store, Date, BookISBN;
.exit

The last piece of the puzzle is the final execution:

sqlite3.exe BookSalesAnalysis.db3 < BookAnalysisLoader.sql

Now we have a newly created database with our analysis data in it, and we have a summary CSV file generated from the output. So we can load the CSV into Excel or another DB, or directly interrogate the DB for more analytical information – and all without coding!

Related Links:

Posted in CodeProject, SQLite | 6 Comments »

SQLite 3.6.18 has been offically released!

Friday, September 11th, 2009

There are a number of good changes here!

  • Improved query planner:
    • Through better use of statistics
    • Compile time option enables Analyze to better handle the index histograms
    • Additionally it was just plain improved as well!
  • Recursive triggers
  • Delete triggers fire during a REPLACE/MERGE
  • More precise use of caching approaches with the
    • Shared Cache – basically cache the results within the application rather can on each thread. Now configurable on a per-thread basis rather than global.
    • Private – The old way

Well done to the SQLite team! Good stuff

More details can be found [SQLite Release 3.6.18 On 2009 Sep 11 (3.6.18)]

Posted in SQLite | No Comments »

SQLite for C# – Part 7 – Building SQLite.Net from source (and netmodules)

Sunday, July 5th, 2009

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:

netmodule

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


&lt;OutputType&gt;Module&lt;/OutputType&gt;

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:


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

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:

Posted in Development, SQLite | 2 Comments »

« Older Entries