More on SQLite

Had a rough day due to a permissions problem, which I must speak about in another post.

Thought I’d post a few useful links regarding SQLite as they are bound to be useful for others.

1) Software -
I mentioned in the last post the wonderful HeidiSQL, which is probably the best remote database tool I’ve used.  Our office purchased both EMS MySQL manager and Heidi (while it was still proprietary).  Since than, HeidiSQL got into a fight with the guys at MySQL that resulted in them open sourcing the project, and I daresay that it has surpassed the much more sophisticated EMS in a number of ways.  [Though even EMS's free version has many more options than Heidi, and is an excellent program.]

It turns out there are a number of initiatives to write software for SQLite, including the following open source tools:

• SQLiteAdmin – Windows tool, good reviews.
  
• SQLite manager – A Firefox Plugin.
• SQLiteDM – Web based, simple, works well.
• SQLiteManager – Web based. Looks solid, similar to phpMyAdmin, but hasn’t had updates in a few years.
• phpSQLiteAdmin – Young, but alive.  Haven’t looked much at it.

2) Tutorials:
SQLite can be accessed via bash over Telnet (using Putty, or even WinSCP), or using PHP.  Beware the permissions issue! Some of the better tutorials I’ve come across, for absolute beginners:

• http://www.switchonthecode.com/tutorials/php-tutorial-creating-and-modifying-sqlite-databases
• http://devzone.zend.com/node/view/id/644
• http://www.litewebsite.com/?c=49

And the official docs, at sqlite.org and PHP:

• http://www.sqlite.org/docs.html
• http://www.php.net/manual/en/book.sqlite3.php

3)
For the record, it is not at all clear how SQLite stacks up to MySQL.  Many good comparisons such as this would have you believe that no serious site would be SQLite.  Others would say that SQLIte is better in one way or another, but less efficient [eg. more expensive if you are on NFS which charges according to use],  The official SQLite site claims that it trounces MySQL in every way including speed, and only gives the full databases the advantage of arcane query support.
I doubt that either is correct.  On most hosts, even VPSs, the databses are stored on separate servers which can be more or less responsive that using the local db files generated by SQLite.  Dreamhost suffers from a terrible reputation when it comes to database response time [though overalll they sound fine], whereas NFS has a blazing fast cluster  [they should, considering that most of their clients pay 3x as much for the database as they do for the hosting].

3) Flat files
In case any of you has a need to use flat files, the best beginners tutorials I have seen are here and here.  This article at AlbinoBlackSheep is also good, spending more time on the presentation than the code.  It is curious to me that only this About.com article refers to the feof() and other basic functions.  Good luck.