Pointers on best use of sqlite 'in memory' databases?

[Cups]

Can anyone share the cases of when they have used sqlite :memory: databases?

I was able to use APC in the past which was fantastically fast, but I have never thought of using sqlite::memory: before.

I realise that if the server loses power then everything is lost, but I have a few thousand records I'd like to use - a bit like a readonly 'in memory array' so data loss is not an issue for me.

Once loaded in memory is this database then really available to all connections?

How do I make sure the database is loaded in memory whenever the service is restarted?

Any experience good or bad would be welcomed.

Thanks

[crmalibu]

You don't use an sqlite memory database for this. The memory database only exists for the current connection/process. Unless you plan to have a program which runs as a service/daemon which holds the connection which owns the memory db, and other processes(scripts) talk to this daemon to get thier data, it just won't work.

[Cups]

The memory database only exists for the current connection/process.

Just had that sinking feeling as I discovered this fact.

Thanks for confirming it.

So the main value of a sqlite :memory: database is if you are expecting to do a load of, possibly recursive, lookups and/or data manipulations while a single (script) instance runs, yes?

[crmalibu]

In a typical php envornment, yeah that's a situation for them.

I've used them before. I had to pull data from the tables on disk and do processing and manipulation on the data, and the logic had to be done in steps. I switched to a memory database to where I copied data and then manipulated and processed it, and it was much faster than when I tried with a temp table, although a temp table can end up being pure memory too given the right conditons. Anyway, I could have easily just done the processing in php using loops and stuff(a php array is like a memory db if you think about it), but sql syntax was much easier for the tasks I had to do.

Keep in mind sqlite is used extremely heavily in enviornments like cell phones and all sorts of devices. Here, you don't often have the constant rapid creation and destruction of connections and processes. If memory permits, it makes a lot more sense here.

[Cups]

Yeah. I was reading some time ago about 'views' and was thinking how unwieldy that must get.

What you describe is very much how I understand Google Gears works. Dump some data offload it to the client, they manipulate it, and then send it back to the server.

Thanks for you replies.