SQL Virgin Again

I've been in the coding dungeon lately, which explains why I haven't posted for a while.  One of the things I'm working on is moving some of FeedDemon's data out of XML and into SQLite, which I didn't think would be a big deal since I spent a few years working with SQL before I entered the world of indie development.  I wouldn't claim that I was ever a SQL guru, but I used to be pretty good at it.

It turns out, though, that I've forgotten SQL since then.  And I don't mean that I'm just rusty at it: I mean it's like a foreign language to me now.  Sure, I can throw together a simple SELECT statement, but I've completely forgotten the syntax for GROUP BY, HAVING, and pretty much everything else.  So rather than tackling the move to SQLite with ease, instead I've been stumbling through it like a drunk bishop trying to remember where he left his hat.

Anyway...

So far I'm liking SQLite a lot.  It's fast, compact, simple and - most importantly - reliable.  For now I'm only converting a few features to use SQLite for storage (specifically, the "Popular Topics" and "Feed History" reports), since that way I can get an idea of the potential pitfalls before trusting it with more valuable data (such as the customer's subscriptions).  The end goal is to enable easily querying downloaded content without the substantial performance hit and overhead of an XML parser.

BTW, if you consider yourself a SQLite wizard, please feel free to share any tips here.  I'm sure I could use them at this point!

Comments

[Ben Combee]

Run, don't walk, to the book store and get a copy of "The Definitive Guide to SQLite" from APress books. Not only is it a great guide to both what SQLite does and how it works, it's a really great overview of SQL. I came to SQLite when I worked on Palm's Foleo device without any background in SQL, and by the time I was through with the book, I felt I had a great grasp on things.

With SQLite: make sure you use transactions (BEGIN TRANSACTION, END TRANSACTION), as they're the main way to keep performance high and the database consistent. DB writes are only committed on the END, so this avoids lots of churn in the DB when you're doing something like updating a big bunch of records or adding a lot of data.

Also, be careful with indices. They can really speed up queries, but they also can slow down modifications. A common trick is to drop the indices, do a batch of modifications, then recreate them.

Finally, be sure to use column affinity properly. If you don't, you can get into odd situations where you get the wrong data type out of a query. In particular, VARCHAR and STRING don't mean anything, you've got to use TEXT. I found that one in some code I was auditing that was acting odd when a user entered a number with leading zeroes as their ID code :)

[Mike Howard]

What controls are you using to access SQLite?

[Nick Bradbury]

@Ben: Thanks for the tip - I just ordered that book. Column affinity is one of the first stumbling points I hit, primarily because I didn't understand how to query based on a date range. I think I've got that straightened out now, though.

@Mike: I'm using DISQLite3 for Delphi.

[Randy Peterman]

The snag we hit with one project was the mis-use of transactions. Make sure that you maximize their efficiency. Once you've knocked that out you're in pretty good shape as its performance is pretty reasonable. We've moved some software over to Firebird, but I think that SQLite has a lot of merit. Can't wait to test out the updated code.

[Warren]

Um - for anything SQL (and specific syntax) - I tend to hit www.sqlzoo.net - and from memory they also have a tab for SQLLite now as well.

[Jackson Miller]

@Warreen thanks for the pointer to SQLZoo.com. I use Oracle, DB2, SQL Server, and MySQL on a weekly basis and always find myself trying to remember syntax differences and having to look them up. That site could turn into my goto source.

[Javier Estrada]

Technically, it should be celibate and not virgin ;-)

I'm in the same boat. In the early 90's I sold a few systems that I developed using R-Base, MS Access. Today I can only read the SELECT statements and hope for the best.

Regards.

[Nick Bradbury]

@Ben: thanks again for the book recommendation - it arrived today, and as you said, it's excellent.

[javadog]

I just started playing around with DISQLite myself a week ago. One of my products runs on Jet/ADO, and I swore I would never go that route again. Not only that, but SQLite is more "portable" to other platforms.

[Gavin Carothers]

Have you considered NOT storing the XML feeds in SQL?

http://www.oracle.com/technology/products/berkeley-db/xml/index.html

Very good performance, leaves the data in XML (including for example extension elements) lets you run XQuery and XPath to query the data. The database is under the ASL 1.1 and BSD licenses.

[Nick Bradbury]

@Gavin: thanks for the tip about Berkeley DB XML. Any idea how embedding it in your app increases the EXE size?