SQLite-icide
I bought a virtual server on VPS.Net to test PHP 7.0. A virtual server is a blank canvas that allows me to set up my web site any old way I please.
I am not used to this power. I am used to situations where I am handed a working server and making do with whatever is there without a complaint. I am in somewhat of a minamilist mood at the moment. Since I have a blank slate and get to choose my DB, I am seriously thinking of choosing the DB with the smallest footprint and that happens to be SQLite.
SQLite has such a small footprint that it is actually a completely different world than enterprise level databases like Postgres.
MySQL, Postgres and Oracle are built on the client/server model with the database and client sitting on physically different hardware. Implementing the client/server model in this project would imply buying and setting up two servers and I don't have the resources for that.
Installing Postgres or MySQL on the same server as the account consumes asubstantial portion of the resources of the server. If I can't afford to have two servers, then I want to avoid databases that consume a lot of resources.
SQLite is an embedded database. The program was designed for applications where the data is embedded on the same device as the main program.
SQLite is not suitable for enterprise applications. By enterprise, I mean situations where in which multiple people and applications routinely need direct access to the data.
SQLite is prone to problems with scalability. One can scale a web site using the client/server model by adding new web servers. This is not possible with SQLlite. If I try to replicate servers, I will end up with two different databases.
The SQLite web site itself warns against using SQLite on web sites that get more than 100,000 hits per day. The vast majority of web sites could be handled by SQLite sites.
It seems to me that judicious use of SQLite could help scale an enterprise application. One can put static data, such as translation tables, in a SQLite database on the web servers. This frees up the main database for the dynamic information needed to run the company.
Database Filing System
Traditional database management systems (RDMS) hide the actually database files from developers. SQLite uses small files sitting out in the open in the directory tree.
Since the databases are stored in separate database files. One can do some cool things with passing databases around. So, I am imagining a content management system in which one computer maintains data and simply pushes the databases to remoter devices. This ability to publish databases allows SQLite to claim tha it has the largest install base.
File Locking
What worries me most about SQLite is the statement from the SQLite FAQ: "When any process wants to write, it must lock the entire database file for the duration of its update." In contrast, client/server databases have dedicated processes to monitor concurrency allowing for row level locking.
I take the above to mean that if I have a read only database, it will be screaming fast, but if I have a have a large number of writes, I run the risk of turning a database into a bottleneck.
If SQLite locks files, then I want to put the tables I write to regularly in separate files. This means I have to have to open up multiple PDO objects in a web script.
SQLite and PDO
SQLite stores its data in regular files in the file directory. Opening the database is the simple matter of telling the SQLite driver the location of the file. The files do not require a password and the serverr-to-server authentication required of a enterprise level application.
The sqlite driver for the PDO object has a very low overhead. The overhead is not much more than an fopen() statement. Switching from a client-server database to an embedded database appears to weaken my argument that the database connection should be seen as a global resource, but what is okay for a small embedded app is not necessarily okay for an enterprise level application.
PDO the location of the file. The requests do not require a password and server-to-server authentication as an enterprise level application would require.NOTE In enterprise level applications, the database connection is often the most resource intensive part of transaction requiring resources of both the production database and web servers. This is why I resist opening more than one connection per web page.
A SQLite connection is not much more resource intensive than opening a file. There is no problem opening and closing a dozen connections in a session...that is until you've discovered your database is popular and are scrabbling to port the application on your crashed server to a client server set up and discover that your application is not enterprise ready.
Contemplating SQLite-icide
My web sites are small, about 200M SQL statements a day; So, I am seriously thinking of using SQLite as the primary database and seeing what happens. I can imagine the site stovepiping as I scramble to port to a real database. But that would mean that the site was successful, and I prefer problems caused by success to those caused by failure.
The biggest problem for me, here, is that using SQLite for this site dampens my argument that web sites connecting to an enterprise application need to limit the number of connections to the server.
I make no pretense that my personal sites are really enterprise level sites. The one thing that might bring this project down is that I like to update hit counters to monitor performance.
*The stats on the MySQL server for Community Color are 7,181K selects per hour. 483K Inserts per hour, 413 updates per hour. The database receives 1.4MB per hour and sends 7.7 GB of data per hour. Yeah, I think SQLite could handle a lite load like that with no problem.