on Sep 11th, 2006PHP 5, Sqlite, and Windows
My first task at my new job was a simple enough one. All our customers have a review site that has a list of contacts. The file was contacts.html and it was just a static html file. The managers had to edit the html directly to add, update, or delete any contacts. Since editing HTML directly is so difficult, they all wanted Dreamweaver on their systems, which would get pretty expensive!
Since there are already 100s of sites using the static html, I wanted to avoid any kind of complex migration or external database. I decided to give SQLite a try since the db files are so portable. Each site would have it’s own database with a single contacts table. Simple right? Let me go over all the problems I ran into.
SQLite 3 does not work with PHP
There is very little documentation out there about using SQLite 3 with PHP. After a lot of testing and pulling my hair out, I came to the conclusion that SQLite 3 simply does not work with php. The error you will most like get is
“PHP Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database”
I later found php-sqlite3 on sourceforge which promises the add support, but has no public release as of yet.
Have I mentioned that SQLite 3 was released over 2 years ago? I find it very surprising that PHP, the most popular web scripting/cgi language, still does not support it.
PHP is not very friendly with SQLite 2
After giving up on SQLite 3, I tried out version 2 and it still wouldn’t work. I got an error message when I attemped to use sqlite_popen on the test database I created from the SQLite command line tool. After much googling and research, it turns out that PHP can’t open SQLite databases that are created with the command line tool. You must create the database with PHP to be able to use it! How silly. So I added code that would create the database if it did not already exist to get around that problem.
SQLite is very stupid about directory permissions
SQLite needs read/write permissions for every single parent folder in the path. What I mean by this is if your database is located at D:\websites\site_1234\db.sqlite, the user account that PHP is running as will need read and write permissions for D:\websites and D:\websites\site_1234\. This is a huge security issue if you have 500 other sites hosted on the machine located in D:\websites. To solve this problem I put all my database files in D:\databases and gave the account that php runs as full permissions to that directory, which is outside of the webroot.
Conclusion
Using SQLite with PHP is a pain in the ass and if I knew it had so many problems I would have just used postgresql. It would have been overkill, but It would not have taken as long.
Turn of the Crank: PHP 5, Sqlite, and Windows
Well the first problem you had with sqlite 3 with the “file encrypted” error relates to the need to use pdo with php 5. But you are right that documentation and examples are not very good - I had major problems too, gave up on it, and went back to postgresql even for a small app.
Too many PHP’ers are hung up on MySQL which is overkill for probably 80% of the apps. But I still hope php sqlite support can get straightened out - sqlite offers great promise for most simple db apps.
How did you create the database, when there isn’t a CREATE DATABASE command?
$db = new SQLiteDatabase(”db.sqlite”); will create a database, then you just need to create the tables, etc.
quote from : http://au.php.net/manual/en/ref.sqlite.php
“SQLite 3 is supported through PDO SQLite. ”
http://au.php.net/manual/en/ref.pdo-sqlite.php
works fine for me. use the normal sqlite extension to open sqlite2 db’s and the pdo_sqlite extension to open sqlite3 db’s. i’ve had no problems using sqlite2 for the last couple years. but recently i’ve been using sqlite3 through pdo_sqlite.
quote from : http://www.php.net/README_UPGRADE_51.php#db3
”
11c. Changes in SQLite support
In PHP 5.0, SQLite 2 support was provided by the built-in sqlite extension, which was also available as a PECL extension in PHP 4.3 and PHP 4.4. With the introduction of PDO, the sqlite extension doubles up to act as a ’sqlite2′ driver for PDO; it is due to this that the sqlite extension in PHP 5.1 has a dependency upon the PDO extension.
PHP 5.1 ships with a number of alternative interfaces to sqlite:
The sqlite extension provides the “classic” sqlite procedural/OO API that you may have used in prior versions of PHP. It also provides the PDO ’sqlite2′ driver, which allows you to access legacy SQLite 2 databases using the PDO API.
PDO_SQLITE provides the ’sqlite’ version 3 driver. SQLite version 3 is vastly superior to SQLite version 2, but the file formats of the two versions are not compatible.
If your SQLite-based project is already written and working against earlier PHP versions, then you can continue to use ext/sqlite without problems, but will need to explicitly enable both PDO and sqlite. New projects should use PDO and the ’sqlite’ (version 3) driver, as this is faster than SQLite 2, has improved locking concurrency, and supports both prepared statements and binary columns natively.
You must enable PDO to use the SQLite extension. If you want to build the PDO extension as a shared extension, then the SQLite extension must also be built shared. The same holds true for any extension that provides a PDO driver
”
sorry to chock up your comments but this explains why they’ve kept the sqlite2 stuff in there. because the two versions of sqlite are not compatible, if they move wholesale to sqlite3 it would break a lot of scripts.
I just spend only 20 minutes to have PHP5 + SQLite 3 working smooth together. You must use PDO functions. The documentation is very clear on that.
What you DON’T have is an sqlite3_* functions. You must to connect via PDO.