Sqlite

[aspnetguy]

I am currently using Sqlite (comes bundled with PHP5) on my current project.I personally like the new OO interface for DB management.Has anyone else used Sqlite? Any pros or cons about it versus MySQL?

[justsomeguy]

It seems pretty cool.

Unlike client-server database management systems, the SQLite engine is not a standalone process with which the program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the program. The program uses SQLite's functionality through simple function calls. This reduces latency in database access because function calls are more efficient than inter-process communication. The entire database (definitions, tables, indices, and the data itself) is stored as a single cross-platform file on a host machine. This simple design is achieved by locking the entire database file at the beginning of a transaction.

I'm a fan of small and lightweight.

[jesh]

This simple design is achieved by locking the entire database file at the beginning of a transaction.

Does this mean that there will be a higher rate of performance decrease as the number of visitors goes up compared to sites which use a separate DB management system? If there are concurrent visitors, isn't there only one connection to the DB at any time if the file is locked for each transaction? And wouldn't that mean that all the other users have to wait for the file to be unlocked before they can access the data?I've never used SQLite, I'm just curious about the above.

[justsomeguy]

Yeah, I was thinking about that too. It would seem like there would be a lot of waiting associated with many (>1000) connections at a time. If there is no DBMS to control access and it was first-come, then it would seem that some processes may be waiting a while to get access to the database.

[aspnetguy]

Yeah, I was thinking about that too. It would seem like there would be a lot of waiting associated with many (>1000) connections at a time. If there is no DBMS to control access and it was first-come, then it would seem that some processes may be waiting a while to get access to the database.

hmm...interesting point I'll have to look into that. I'm looking at a small number of users for this site so it shouldn't be an issue.

[aspnetguy]

Like every tool, SQLite has its strengths and weaknesses. While being an ideal solution for small and/or mostly-read applications, it is not well suited for large-scale applications performing frequent writes. This limitation is due to SQLite’s single file based architecture, which doesn’t allow multiplexing across servers, or the usage of database-wide locks on writes.

There is the question that. It doesn't do well for large or write heavy applications. Perhaps I'd better re-evaluate my application.