Three SQLite Gotchas

If you are using SQLite coming from say a Microsoft SQL Server background, here are a few differences that might throw you.

1. There is no TOP as in "select top 50 from....".  
   Instead you use LIMIT, and you put it at the end of your query like "Select blah from blah order by blah limit 50".
2. Paging because of the above is actually made easy.  You can use OFFSET with LIMIT for easy paging like so. "Select blah from blah order by and wheres blah limit 20 offset 50..." if you pass in a paging variable like say @page you can times your offset to get you what page you want, like "offset * @page".   There is even a shorthand for it, but with a gotcha...the offset and limit are swapped.  Like "select .... order by blah limit 40,10" actually means offset 40 limit 10, which is counter intuitive but there for backwards compatibility.
3. CASE.  Case is kind of a bummer for comparisons in SQLite.  you can set in on your indexes or on your connection strings to ignore case on your where @mystring = 'blah' type queries like so
   
   - programa collate nocase in your connections string
   - collate nocase on the end of your queries
   - or add collate nocase on your index when creating a column
   
   But alas, not that simple.  this only works for UTF-8, if you are using UTF-16 you have to use more exotic solutions with collate binary (I'll leave a link).
   
   Out of the box you can fake insensitivity using the like operator,  select blah where x like @xxx +'%'... though note here you are better off adding that % operator on your sql param then having it in your query string, for some reason the later doesn't work for me but maybe I'm missing something.
   
   Also instr, the equivalent to charindex in SQL, insn't case insensitive.  Bummer.

Some useful links-

>> Stack Overflow How To Set SQLite Case Insensitive (binary collate in link towards end)
>> Stack Overflow Limit and Offset in SQLite

Importing Access Tables into Sqlite

There are numerous ways to do this, this is how I did it.

1. Download LibreOffice.
2. Use Base to open your access .mdb database
3. Right click on the table, click copy.
4. Open Calc, create a new spread sheet, control V to paste in data that is in memory from when you right clicked copy on the table in Base.
5. Export your new spread sheet in CVS format (I left all the default options).
6. Open your SQLite DB (I use the SQLite Manager Plugin for Firefox to Manage SQLite DBs).
7. Import the CSV file (again I left all the defaults except I did click use the first row as a header).
8. Once imported, you can tweak the headers.  If the datatypes are slightly off, you might have to create a new table how you like it and then select into that table from the newly created imported table.

This was good for a simple table with about 30000 records.  Your mileage may very.  Happy coding.

SQLite Windows and Mono Compatible

I haven't tried this yet, but if you have a .net project that needs to use SQLite on both Mono and Window's platforms, I found this comment interesting.

Taken from here http://www.codeproject.com/Articles/821149/Universal-System-Data-Sqlite-binary-for-NET-and-Mo

You could just use Mono.Data.SQLite  William Ivanski 22-Sep-14 4:16  2

I copied the file "Mono.Data.SQLite.dll" from Mono to my application bin folder. I also copied "sqlite3.dll" from SQLite website. It works flawlessly under both .NET/Windows and Mono/Linux.

A Few SQLite Tips

 

 SQLite http://sqlite.org/ is an open source database that is written in C.  It is similar to say an access .mdb file or a Microsoft SQL Server CE file only databases in that it is a file and runs in the same process (well, some caviots here with newer mods to SQLite but I'm not familiar enough there to comment) as your deployed program.  It is used by Firefox, Chrome, a lot of phone app developers, and now by me.  SQL Server is overkill for many of the little web applications I write and although I like SQL Server Compact Edition it looks like it is dead in the water [LINK].

1) Once you have created a table in SQLite, you cannot add a new column to that table with the alter command that has a default value [LINK].

2) It seems like time for SQLite is defaulted to GMT, which in my opinion is a good thing.  For default dates in tables, use CURRENT_TIMESTAMP.

3) SQLite-Net looks really cool, but I haven't been able to hack it to work for web development.  If you are using Windows 8 and developing APPS or Desktop applications, take a look at it. [LINK].

4) A really great and free GUI manager for SQLite is a plugin for Firefox called SQLite Manager. Look for it in the Firefox Plugins.  More info [LINK].

5) It looks like the main .Net SQLite Data Connection enabler is System.Data.Sqlite.  The full Nuget package contains Linq and Entity Framework support.  More info [LINK].

6) There is a version of SQLite written in managed .net code, but it looks like it is no longer being revised.  Maybe Microsoft will pick it up.  Info [LINK].  You might be able to hack SQLite-net to use this version, as it looks like it was originally intended to use it.  I tried for a few hours but gave up.  Maybe I'll take another stab at it.

7) There are no stored procs with SQLite (although I believe there is a fork in development that supports that, but I'm not sure how stable it is).  You can however create user defined functions.

8) When to use SQLite [LINK].

9) Interestingly, if you try to connect to a SQLite DB that doesn't exist the SQLite Connection object will create a new blank DB file for you with the name that you were looking for.  This actually caused me some confusion as has an extra character in my connection string and suddenly I couldn't query any of my tables...duh, they didn't exist because I was querying a new database.

10) If you are familiar with Transact SQL and Microsoft SQL Server (or MySQL, Postgress, Oracle) I don't think you will have any problems using SQLite.

11) Data types on columns are more recommendations than set and fast.  I believe if you try you can save strings in int fields and mix and match.  This might have changed with the 3.x version but from what I have been reading this at lest used to be very true.  Who knows in some cases this could be a good thing, but in most if you aren't careful this might cause havoc.

12) For bulk inserts you want to use SQLite transactions else SQLite will be very slow [LINK]

13) SQLite select queries are CASE SENSITIVE by default.  You can get around this by using COLLATE NOCASE after your where condition like select * from mytable where colum1 = @somevalue COLLATE NOCASE.  A post explaining the particulars can be found [HERE].

14) There is no "Select top * or Select top 10" in SQLite.  Instead use limit. Example: "Select blah from mytable where blah order by blah limit 10"

I got my start in web development using classic ASP and Access MDB databases.  I could crank stuff out FAST.  I think now for some of my projects using MVC (thought without most of the MVC, just using Razor pages almost like development with WebMatrix) and SQLite might be a sweet spot for me as far as making development fun and productive.  I plan to update this page as I find more little quirks using SQLite with ASP.Net.  I also will be posting some source code examples.