Sqlite for disk backed associative array?

Question

I want to use SQLite as an associative array that is saved to disk.

Is this a good idea? I am worried about having to parse SQL every time I do something like:

database["someindex"] which will have to be translated to something like

select value from db where index = 'someindex' which in turn will have to be translated to the SQL internal language.

Answer 1

If you are worried about SQL overhead and only need a simple associative array, maybe a dbm relative like GDBM or Berkeley DB would be a good choice?

Answer 2

Check out sqlite parameters for an easy way to go variable <-> sql

Answer 3

It really depends on your actual problem. Your problem statement is very generic and highly dependent on the size of your hashtable.

For small hashtables you're only intending to read and write once you might actually prefer a text-file (handy for debugging).

If your hashtable is, say, smaller than 25meg SQLite will probably work well for you

Answer 4

SQLite should be pretty fast as a disk based associative array. Remember to use prepared statements, which parse and compile your SQL once to be invoked many times; they're also safer against SQL injection attacks. If you do that, you should get pretty good performance out of SQLite.

Another option, for a simple disk-based associative array, is, well, the filesystem; that's a pretty popular disk-based associative array. Create a directory on the filesystem, use one key per entry. If you are going to need more than a couple hundred, then create one directory per two-character prefix of the key, to keep the number of files per directory reasonably small. If your keys aren't safe as filenames, then hash them using SHA-1 or SHA-256 or something.