PHP - Manual: SQLite (PDO)

2024-04-23

SQLite Functions (PDO_SQLITE)

简介

PDO_SQLITE is a driver that implements the PHP Data Objects (PDO) interface to enable access to SQLite 3 databases.

注意:
PDO_SQLITE allows using strings apart from streams together with PDO::PARAM_LOB.

安装

The PDO_SQLITE PDO driver is enabled by default. To disable, --without-pdo-sqlite[=DIR] may be used, where the optional [=DIR] is the sqlite base install directory. As of PHP 7.4.0 » libsqlite ≥ 3.5.0 is required. Formerly, the bundled libsqlite could have been used instead, and was the default, if [=DIR] has been omitted.

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

PDO_SQLITE DSN — Connecting to SQLite databases
PDO::sqliteCreateAggregate — Registers an aggregating User Defined Function for use in SQL statements
PDO::sqliteCreateCollation — Registers a User Defined Function for use as a collating function in SQL statements
PDO::sqliteCreateFunction — Registers a User Defined Function for use in SQL statements

add a note

User Contributed Notes 8 notes

down

ohcc at 163 dot com ¶

1 year ago


With PDO SQLite driver, calculation within an SQL with multiple ? may not get results as you expect.

<?php
// ....
$stmt = $PDO->prepare('SELECT * FROM `X` WHERE `TimeUpdated`+?>?');
$stmt->execute([3600, time()]);
$data = $stmt->fetchAll();
print_r($data);
?>

To get the right results, you have more than 3 solutions.

1. Change 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?' to 'SELECT * FROM `X` WHERE `TimeUpdated`>?' and do the math using Php (ie: $stmt->execute([time()-3600]); ).

2. Use PdoStatement::bindParam or PdoStatement::bindValue, and set the parameter type to PDO::PARAM_INT.

3. Change 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?' to 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?+0', here '?+0' may be replaced by another math function or another calculation, such as 'abs(?)', you can even wrap both ? with a math calculation.

down

aidan at php dot net ¶

16 years ago


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.

down

nospam8715 at dririan dot com ¶

9 years ago


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.

down

Duffalo ¶

15 years ago


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.

down

-3

chrislangton84 at gmail dot com ¶

6 years ago


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

down

-5

gopal at gopalarathnam dot com ¶

15 years ago


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
    // ...

down

-15

tris dot php at tfconsulting dot com dot au ¶

8 years ago


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;
    }
}
?>

down

-16

gmac63 at charter dot net ¶

15 years ago


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!

add a note

官方地址：https://www.php.net/manual/en/ref.pdo-sqlite.php

有任何技术问题请点击这里网站运营推广招聘

IT PHP 编程语言 Linux 科技开发编程 Elasticsearch HTML/CSS/XML 网络 JAVA NoSQL C/C++ 面试 Golang 数据库 Git 算法正则表达式操作系统 Redis 互联网 MySql JavaScript 运维软件架构设计 Mac OS TCP/IP 国际 Excel Vim Socket Windows Oracle VR MongoDB 运营 Python MemCache 商业硬件电子设计 nginx 娱乐摄影游戏 WordPress HTTP 团建数码电器

php7 安装fileinfo扩展 preg_split — 通过一个正则表达式分隔字符串 [PHP] inet_pton/inet_ntop IP地址转换函数 php位值,解决 PHP 中 usort 在值相同时改变原始位置的问题 PHPMailer设置utf8 PHPMailer字符集CharSet 如何在Composer中为GitLab实例设置访问令牌 array_multisort() 多字段排序 laravel框架查看sql语句方式 [原创]诡异的php-fpm超时问题 PHP获取指定函数定义在哪个文件中及行号利用php soap实现web service PHP5 扩展SOAP 调用 webservice php设置cookie为httponly防止xss攻击 PHP-FPM进程数的设定纯php实现DES以及TripleDES加密算法利用PHP SOAP实现WEB SERVICE PHP7添加redis扩展 PHP注释规范 PHPstorm 里面Terminal 不能使用 esc键吗退出编辑模式吗 Composer的Packagist资源

略微加速

PHP官方手册 - 互联网笔记

SQLite Functions (PDO_SQLITE)

简介

安装

目录

User Contributed Notes 8 notes