contents/articles/quick-overview-of-sqlite.html
1 2 3 4 5 6 7 |
----- title: "A Quick Overview of SQLite" content-type: article timestamp: 1132937558 tags: "review|databases" ----- A few months ago, my old hosting company started having problems with their servers. The servers would go down unexpectedly for 5-10 minutes on a relatively frequent basis, but for some weird reason... the MySQL databases were unusable for a couple of hours afterwards every time. "We had problems with MySQL, BUT the server was up, so we're still within the 99% uptime guarantee"... At the time I was thinking: "If only MySQL databases behaved like plain files..." <br /><br /><strong>What is SQLite?</strong><br /><br />When PHP5 was first released, I discovered SQLite: <em>"...a small C library that implements a self-contained, embeddable, zero-configuration SQL database engine"</em> (as quoted from the <a href="http://www.sqlite.org">official site</a>). PHP5 offers native support to this little wonder, whose development actually started long before PHP5 was released, and can be used with many, many other programming languages.<br /> <br />SQLite organizes each database in a .db file, and implements most of the SQL 92 standards, to access the databases with no need of a server process running at the same time. Access is accomplished through standard reading/writing file operations.<br /><br />Let's examine the pros and cons of using SQLite in your web applications.<br /><br /><strong>Features</strong><br /><br />- SQLite is FREE "for any purpose", <a href="http://www.sqlite.org/copyright.html">they say</a>.<br />- It doesn't rely on a server process to run<br />- You don't need to spend time configuring your installation, because there's nothing to configure!<br />- As there's no client-server negotiation, accesses to the database are much faster (2-3 times faster than a MySQL database)<br /><br />As a consequence of all this, there's actually no concept of "users" allowed to access the database; as I said, the actual data of each database is stored into a single file, and as such, it has permissions which regulate access. If a script has read or write access to the file, a read/write sql instruction can be executed on the database. You can therefore simply protect your databases as you would protect any other file on your server.<br /><br />- SQLite is small: the library is just 250KB, and takes care of everything, you don't need any other library or program to use it.<br />- SQLite can handle files up to 2 terabytes in size.<br />- SQLite implements most of the SQL 92 standard. This means you can usually use standard and well known queries to access it (with some exceptions, discussed in the next section).<br />- SQLite does not enforce datatype constraints. Is this a feature or a bug? Well, they call it a feature, but others may not agree. As a matter of fact, you can put a string into a field marked "integer" and vice versa, and furthermore, the string can be as big as you like! There's one exception to this rule, though. Columns marked as PRIMARY KEY must be of integer type. <br /><br /><br /><strong>Limitations</strong><br />Now that you have read all of the preceding material, and know that PHP5 supports SQLite natively, you might be thinking about putting MySQL in the bin and using SQLite for everything instead: it's smaller, faster, portable, simpler, and headache-free... it's love at first sight. Right?<br /><br />Well, the developers themselves decided to devote <a href="http://www.sqlite.org/whentouse.html">a page</a> to discuss when you should use SQLite and where you'd be better off sticking with your "old" database engine. Furthermore, being such a small and powerful piece of code, SQLite comes with some limitations which should be considered before starting to use it in a project:<br /><br />- Not all SQL queries and syntax are supported. For a full list, have a look <a href="http://www.sqlite.org/omitted.html">here</a>. The most notable things you'll miss in SQLite are: the inability (for now) to "ALTER TABLE" (you do this, they say, by creating a new modified table and deleting the old one), no VIEW, and no CHECK or FOREIGN KEY constraints (they are "parsed but not enforced").<br /><br />- Syntax can be different sometimes. I noticed that, for example, in a JOIN between two or more tables, when accessing columns you ALWAYS have to specify <table>.<column>, whereas MySQL doesn't complain if there's ambiguity.<br /><br />- SQLite is not suitable for projects which requires a lot of semi-simultaneous writing operations. SQLite uses reader/writer locks: if there's someone reading from the database, writing to it is not allowed. This basically mean that multiple simultaneous read operations (SELECT x FROM ...) have higher priority than write operations (INSERT, UPDATE, ...), which are therefore delayed. <br /><br />- Do not use SQLite for big databases. Even though I said that (theoretically) databases up to 2 terabytes are supported, when your database is more than 1 GB, SQLite requires too much memory to run (256 bytes of RAM for each MB of database space, they say).<br /><br />- Generally, if your website gets lots of traffic, SQLite shouldn't be your primary database engine, for the issues mentioned above. php.net uses SQLite for its site, but only on certain parts of it. If you get fewer than 100,000 hits/day, SQLite should work fine - they say. So basically I can use for any site I make...<br /><br /><br /><strong>Conclusions</strong><br />Considering all features and limitations, SQLite is an excellent solution for small or medium websites, embedded applications, programs which only need a small database to function and shouldn't be bound to a server, temporary databases, testing, and the like. Always keep in mind that you're working with files, so keep them protected wherever you put them in your server (a connection is established simply by specifying the path to the file).<br /><br />Last but not least, if you're used to phpMyAdmin for administering your MySQL databases, there are similar tools for SQLite, such as <a href="http://www.sqlitemanager.org">sqlitemanager</a>.<br /> |