SQLite(PDO)

简介

PDO_SQLITE是一个驱动程序，该驱动程序实现PHP数据对象（PDO）接口以启用对SQLite 3数据库的访问。

在PHP 5.1中，SQLite扩展还提供了SQLite 2数据库的驱动程序。尽管从技术上讲，它不是PDO_SQLITE驱动程序的一部分，但其行为类似，因此在它的旁边进行了说明。提供PDO的SQLite 2驱动程序主要是为了使其更容易将旧版SQLite 2数据库文件导入使用更快，更高效的SQLite 3驱动程序的应用程序中。结果，SQLite 2驱动程序的功能不如SQLite 3驱动程序丰富。

注意事项：

PDO_SQLITE允许将流之外的字符串与一起使用PDO::PARAM_LOB。

安装

默认情况下启用PDO_SQLITE PDO驱动程序。要禁用，可以使用--without-pdo-sqlite[= DIR]，其中可选的[= DIR]是sqlite基本安装目录。从PHP 7.4.0起»需要libsqlite≥3.5.0。以前，如果省略[= DIR]，则可以使用捆绑的libsqlite，它是默认值。

注意：Windows自PHP 7.4.0起的其他设置

为了使此扩展生效，DLL文件必须能在 Windows 系统的PATH指示的路径下找到。如何操作的信息，请参见题为“如何在 Windows 中将 PHP目录加到 PATH 中”的FAQ。虽然将DLL 文件从 PHP 文件夹复制到 Windows 系统目录也行，但不建议这样做。此扩展需要下列文件在PATH路径中：libsqlite3.dll.

Instead of compiling an old version of SQLite to create a database using an older database format that the version of SQLite bundled with PDO can handle, you can (much more easily) just run the query "PRAGMA legacy_file_format = TRUE;" BEFORE creating the database (if you have an existing database, run ".dump" from the sqlite shell on your database, run the sqlite shell on a new database, run the PRAGMA, then paste the contents of the .dump). That will ensure SQLite creates a database readable by SQLite 3.0 and later.

If you receive an error while trying to write to a sqlite database (update, delete, drop):
Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 1 unable to open database
The folder that houses the database file must be writeable.

This page has been out of date for some time - Installation specifically.
As of PHP 5.4 sqlite is no longer part of PHP and in only available through PECL

Note that as of the date of this post, PDO_SQLITE will not interact with database files created with the current version of the SQLite console application, sqlite-3.3.6.
It is currently necessary to obtain version 3.2.8, available from http://www.sqlite.org/ but only by entering the URI manually, as there is no link. Go to http://www.sqlite.org/download.html and find the URI of the version you're looking for, then make the appropriate version number substitution.

If you get an error reporting "invalid resource" when trying to query the database table and looping through it, the version of the SQLite extension compiled in to PHP might be incompatible with the version that had created the database (like SQLite 2.x vs 3.x).
The database open itself might be successful, failing only when querying.
$dbh = new PDO('sqlite:/tmp/foo.db'); // success
foreach ($dbh->query('SELECT * FROM bar') as $row) // prints invalid resource
  // ...

After wrestling with "General error: 5 database is locked" errors for a highly concurrent project I finally wrapped the PDO transaction code with a semaphore. No errors since...
Obviously only works if all processes use the subclass and wrap database modifying statements in beginTransaction() .. commit(). The same could be achieved with flock() if semaphore is not available on your system but will be slower.
<?php
class SQLitePDO extends PDO {
  function __construct($filename) {
    $filename = realpath($filename);
    parent::__construct('sqlite:' . $filename);
    $key = ftok($filename, 'a');
    $this->sem = sem_get($key);
  }
  function beginTransaction() {
    sem_acquire($this->sem);
    return parent::beginTransaction();
  }
  function commit() {
    $success = parent::commit();
    sem_release($this->sem);
    return $success;
  }
  function rollBack() {
    $success = parent::rollBack();
    sem_release($this->sem);
    return $success;
  }
}
?>

Issue:
Error: SQLSTATE[HY000]: General error: 1 unsupported file format 
Resolution:
To solve this (and/or many issues) involving this type of error, I assumed the error to be generated from php. Well, it was to an extent. The sqlite pdo code offered the solution:
I researched the error by grep'ing the php source code and found the error string to come from php-5.1.4/ext/pdo_sqlite/sqlite/src/prepare.c, lines 265:278 :
/*
 ** file_format==1  Version 3.0.0.
 ** file_format==2  Version 3.1.3.
 ** file_format==3  Version 3.1.4.
 **
 ** Version 3.0 can only use files with file_format==1. Version 3.1.3
 ** can read and write files with file_format==1 or file_format==2.
 ** Version 3.1.4 can read and write file formats 1, 2 and 3.
 */
 if( meta[1]>3 ){
  sqlite3BtreeCloseCursor(curMain);
  sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0);
  return SQLITE_ERROR;
 }
This is interesting as I am running SQLite version 3.3.5 which the databases were created in. I see that the SQLite PDO source in the php source is :
   # cat ext/pdo_sqlite/sqlite/VERSION
    3.2.8
My solution was then to find a version of sqlite that was =< 3.1.4. I found source for 3.1.3, compiled, recreated my database using that version (NOTE: the databases are unsupported between 3.1.x and 3.2.x versions of SQLite). Once I did this it worked.
Also as a side note, to get SQLite compiled as a PDO, I had to:
1) configure with
...
--enable-pdo=shared \
--with-sqlite=shared \
--with-pdo-sqlite=shared
--with-zlib
... \
'make && make install' if configure is successful.
2) Make sure the pdo libs were copied/installed to the correct directory. On my installation it was /usr/local/include/php/ext/pdo/
3) Make these changes in my php.ini:
 - change ' extension_dir = "./" ' to ' extension_dir="/usr/local/include/php/ext/pdo/" '
 - add/edit in this order:
   extension=pdo.so
   extension=pdo_sqlite.so
   extension=sqlite.so
4) test php with : 'php -m' at the command line and solve any issues from there. Mostly php.ini config issues. Also restart the http service!