Anyone using SQLite?

I've been thinking/trying to move my forum over from MySQL to SQLite because a) my host's MySQL is grossly outdated (3.something - though I suppose it doesn't really make much of a difference) and b) I enjoy the convenience of simply downloading the database to back it up (though I suppose neither is really important seeing as no one - including me - posts to my forums anymore).

I also use SQLite when playing around with things locally simply because it's so easy to set up.

In other words, SQLite support is neat to play with but not wholly necessary, for me at least.

I have just started to use sqlite because of hosting restrictions, though how far I get with it is another matter.

Just to update things, as I have spent most of the day importing data from Yabb into punBB the forum is now available via: http://www.by-users.co.uk/forum/

Not that much done theme wise, though things do change according to the season (header, logo and background only at this point).

Now to see if the server can cope with it!

I assure you, it would not. The example you posted is valid, but only for a more or less static website. A forum is anything but static. SQLite would probably handle this forum, but as soon as the number of concurrent users increases, you will hit a wall with sqlite. It's very fast at doing your every day run-of-the-mill queries, but as soon as you write something to the database, every other thread that wants access to any table in the database will have to wait until that write is completed. That's the problem with what is essentially database level locking.

It has no choice. The whole database is stored in one file

I couldn't say. I really don't know of any big forums that use sqlite.

Rich: It's more dependent on the number of concurrent users. I'd be interested in seeing an SQLite forum with an average of maybe 50 users online.

and then more problems.

SQLite is fine, but the server I am hosting on isn't and scripts are failing, first it was edit, and now show all your posts *sigh*. I can't even perform a back up, FTP speeds are so slow the database, which is currently 60+Mb, would take all day to download *grrrrr*.

I'm now recommending we ditch our free hosting and go elsewhere, then I'll have MySQL *yay*

<-- Running lighttpd+sqlite3 (I coded the db layer to support sqlite3 myself, can't garantee it's 1.2.14 compatible, but it should work afaik)

Been a while since I last posted here... Anywhos, somewhere on these forums you'll probably run into "pdosqlite3 dblayer" or something along them lines. Download the tarball and follow the directions to add support for sqlite3 databases (which add a few improvements to sqlite 2 databases, the default with punbb 1.x). Converting from sqlite to sqlite3 is easy from a shell:

sqlite forum.db2 ".dump" | sqlite3 forum.db3

I've had a few problems with my computer and my time the past few months, and I am making the move to mysql (only because my e-mail server requires it; and because it's just as easy to backup the database iff you have shell access; and because of async requests - if sqlite3 supported async read/writing i'd probably stick with sqlite though ). So if anyone wants to take over the sqlite3 db layer "mod" your welcome to it.

EDIT --

Found it, here's a link to the topic:
http://punbb.org/forums/viewtopic.php?id=13123

And here's one to the tarball:
https://robshouse.no-ip.info/pdosqlite.tar.bz2

If the link doesn't work, I've probably buggered something up in my firewall config, so just post and tell me. I don't really have a computer outside the firewall to test with ;.;

EDIT --

Ohh, just to say, the reason I started to use punbb was because it supported SQLite. I started with lighttpd, then moved on to choosing a database, and finally choose a forum that supported my database (I think punbb is the only open source forum project that does, or at least the only one that will work ^.~).

SQLite(3, and 2 afaik) can do async read requests. It get's into trouble with major traffic if there is a write request. For the duration of that single write request, each read and/or write request will probably fail; From what I remeber both my version and the original version of the sqlite db layer set-up read/write access on every php page, and the translates into each single request being required to close before the next request for a page is allowed to be opened (or times out). Although, if you were crazy and felt like a quick hack, you ~could~ re-implement all the sqlite dblayer functions to retry on fail every 1 seconds for 15 seconds, and only open the db as read until a write statement is found. Since both 2 and 3 support the BEGIN TRANSACTION, COMMIT and ROLLBACK SQL statements this would be close to trivial, except for the parsing of each statement (which could be a SELECT or could be a UPDATE or any number of commands that may or may not require write access).

I personally feel that a more abstract DB layer should be made on top of the current layer. This would mean more CPU usage server side, but could facilitate database support for modules. http://punbb.org/forums/viewtopic.php?id=13125 for a fuller view of what I'm trying to say in to many mumbled words, and a few reasons why not to do it too ~.^ Also with this type of db layer databases could be opened and closed depending on which sql statement is about to be executed. Allowing a "only reading from db" php page to only open for reading. I think index.php, search.php and viewforum.php are the only pages that are read-only, but they are also probably the most used pages on any forum.

lol, sorry for the triple edit... seems my short post turned into a long one, but I have a few months of silence to make up for

I am having some performance issues and i suspect is the database taking to long to answer...
but i am not having that many concurrent users, usually there is between 6 and 10 users online, sometimes 12.

My database is 20 Mbyte big
3000 users
7000 posts
Could it be the database that is slowing things down?