Subscribe to
Posts
Comments
NSLog(); Header Image

SQLite

I'm doing some work with SQLDatabase from Blackhole Media, which interfaces with the lovely SQLite embeddable database. Everything so far is working quite well - it's been compiled with UTF-8 support, and so on.

However, I'm continually frustrated with the CLI tool. It is the most rudimentary thing I've seen, and it's quite annoying. This is a bit of a rant, but I'll try to be constructive.

<rant type="numbered">

1. Arrow keys don't work. Do you you know how frustrating it is to see '^[[D^' in your terminal just for hitting the left arrow key?

2. emacs key bindings don't work. Do you you know how frustrating it is to see '^A' in your terminal just for hitting ctrl-A? For whatever reason (probably my own lack of knowledge), ctrl-w clears the line.

3. Column titles are not given in output. In other words, if you "select * from people;" your result table could be fourteen columns wide, but you'll have to remember what the column names are, because the output doesn't tell you.

sqlite> select * from directories; 
1||0|0|Test
2|||1|Something Else
3|2|||Wheeee
4|1|2||Subfolder

4. There's no indication of rows changed or time. In MySQL you're given a bit of information after doing a query:

mysql> UPDATE mt_log SET log_ip='11.222.3.444' WHERE log_ip='64.226.43.134';
Query OK, 43 rows affected (0.22 sec)
Rows matched: 43  Changed: 43  Warnings: 0

In SQLite:

sqlite> UPDATE mt_log SET log_ip='11.222.3.444' WHERE log_ip='64.226.43.134';
sqlite>

I just spent half an hour troubleshooting something that shouldn't have happened. Why? Because I had a typo in my "WHERE" clause that resulted in 0 rows changed. Had it told me 0 rows changed, I'd have noticed the typo and fixed it.

5. SQLite uses non-standard commands. You don't quit by typing "quit" or "exit." Instead, you type ".quit."

6. You can't dump to a file, and the dump is badly ordered. I'm used to having all the "DROP TABLE IF EXIST" and "CREATE TABLE" stuff at the top of a dump with all of the INSERTs at the end. SQLite's ".dump" tool mixes them together. Frustrating.

7. There is no man file. So if I'm missing something, somewhere, anything, my first method of discovery is MIA itself.

</rant>

5 Responses to "SQLite"

  1. Probably isn't a help.. but did you try the VI bindings? (hjkl) ?

    Pretty damn ugly system if you ask me.. and you have to use it? Ugg. DB2 (in all of its unusable glory is better then that) 🙂

  2. I haven't tried it yet, but I think if you install readline the editing keys will work.

    You can dump to a file using .output followed by .dump.

    BTW, if you are truly using UTF8 you should modify SQLDatabase to not use the cString methods.

  3. I had the same problems with sqlite - it's kind of nasty without readline support. I managed to figure out how to get it to build with readline and fink installed. If you like email me and I'll send you the changed make file.

  4. > 1. Arrow keys don't work.

    They work for me... In my xterm window.

    > 3. Column titles are not given in output.

    You can switch ".headers ON" so you can see the headers.

    > 5. SQLite uses non-standard commands.

    You can use the standard Ctrl-D to leave sqlite command line just like in MySQL.

    > 6. You can't dump to a file, and the dump is badly ordered.

    You can dump to a file, just type

    "sqlite x.db .dump > /tmp/dump.sql" command or

    "echo '.dump' | sqlite x.db > /tmp/dump.sql"

    It will work, just try it.

    > 7. There is no man file.

    The manual is at http://www.sqlite.org/lang.html

  5. Thanks for the tips, gyulus. Some of those I've since learned, but some will come in handy.