|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2009-01-19 09:46 UTC] pcdinh at gmail dot com
Description:
------------
I try to use prepared statement to insert a batch of rows into a SQLite3 database. Also, I have created a constraint to make sure that
I do not insert duplicate rows. When the constraint is violated, SQLite3 class does not allow me to close the database.
The scenario is as follows:
1. I write a script to iterate through a directory and add found files into a SQLite3 empty database. The round 1 is OK because there is no violated constraint.
2. I run it again with the same directory. Now the database is not empty anymore. Lot of warnings are generated as below:
Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275
Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275
Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db
Reproduce code:
---------------
protected function _buildDatabase($path)
{
$files = new DirectoryIterator($path);
// By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE.
$db = new SQLite3($this->_databasePath);
$sql = 'CREATE TABLE IF NOT EXISTS pone_plugins (
plugin_id INTEGER PRIMARY KEY AUTOINCREMENT,
plugin_class TEXT,
plugin_version INTEGER,
plugin_title TEXT,
plugin_desc TEXT,
plugin_author TEXT,
plugin_hooks TEXT,
plugin_url TEXT,
plugin_deps TEXT,
plugin_status INTEGER
)';
$db->exec($sql);
$sql = 'CREATE UNIQUE INDEX IF NOT EXISTS pone_plugin_idx ON pone_plugins (plugin_class)';
$db->exec($sql);
$sql = 'INSERT INTO pone_plugins (
plugin_class, plugin_version, plugin_title, plugin_desc,
plugin_author, plugin_hooks, plugin_url, plugin_deps, plugin_status
) VALUES (
:class, :version, :title, :desc,
:author, :hooks, :url, :deps, :status
)';
$stmt = $db->prepare($sql);
foreach ($files as $file)
{
if (!$file->isDot() && !$file->isDir())
{
/* @var $file DirectoryIterator */
$path = $file->getPathname();
$dotPos = strpos($path, '.php');
if (false === $dotPos)
{
continue;
}
include_once $path;
$class = str_replace('.php', '', $file->getFilename());
$plugin = new $class();
/* @var $plugin Pone_Pluggable */
$info = $plugin->getInfo();
/* @var $plugin Pone_PluginInfo */
/* @var $stmt SQLite3Stmt */
$stmt->bindValue(':class', $class, SQLITE3_TEXT);
$stmt->bindValue(':version', (int) $info->version, SQLITE3_INTEGER);
$stmt->bindValue(':title', $info->title, SQLITE3_TEXT);
$stmt->bindValue(':desc', $info->desc, SQLITE3_TEXT);
$stmt->bindValue(':author', $info->author, SQLITE3_TEXT);
$stmt->bindValue(':hooks', serialize($info->hooks), SQLITE3_TEXT);
$stmt->bindValue(':url', $info->url, SQLITE3_TEXT);
$stmt->bindValue(':deps', serialize($info->depends), SQLITE3_TEXT);
$stmt->bindValue(':status', 3, SQLITE3_INTEGER);
$rs = $stmt->execute();
if (false != $rs)
{
/* @var $rs SQLite3Result */
$rs->finalize();
}
else
{
echo 'Error: '. $db->lastErrorMsg();
}
// Resets the prepared statement to its state prior to execution.
// All bindings remain intact after reset.
$stmt->reset();
$stmt->clear();
}
}
$stmt->close();
$db->close();
}
Expected result:
----------------
I believe that the error happens to a single query (prepared statement) will be cleared out with
$stmt->reset();
$stmt->clear();
Therefore, there will be no problem with
$stmt->close();
$db->close();
No warning should be emited and database should be closed as normal.
Actual result:
--------------
Lot of warnings are generated as below:
Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275
Warning: SQLite3Stmt::execute() [sqlite3stmt.execute]: Unable to execute statement: constraint failed in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 261
Error: constraint failed
Warning: SQLite3Stmt::reset() [sqlite3stmt.reset]: Unable to reset statement: column plugin_class is not unique in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 275
Warning: SQLite3::close() [sqlite3.close]: Unable to close database: 5, Unable to close due to unfinalised statements in D:\wvbsrc\repos\pone\trunk\incubator\Pone\Pone_PluginManager.php on line 281
D:\wvbsrc\repos\pone\trunk\tests\Plugin/plugins/plugins.db
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Wed Oct 29 00:00:01 2025 UTC |
Hi Scott, Here is the test script: <?php // By default, open uses SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE. $db = new SQLite3('./test.db'); $sql = 'CREATE TABLE IF NOT EXISTS pone_plugins ( plugin_id INTEGER PRIMARY KEY AUTOINCREMENT, plugin_class TEXT, plugin_version INTEGER, plugin_title TEXT, plugin_desc TEXT, plugin_author TEXT, plugin_hooks TEXT, plugin_url TEXT, plugin_deps TEXT, plugin_status INTEGER )'; $db->exec($sql); $sql = 'CREATE UNIQUE INDEX IF NOT EXISTS pone_plugin_idx ON pone_plugins (plugin_class)'; $db->exec($sql); $sql = 'INSERT INTO pone_plugins ( plugin_class, plugin_version, plugin_title, plugin_desc, plugin_author, plugin_hooks, plugin_url, plugin_deps, plugin_status ) VALUES ( :class, :version, :title, :desc, :author, :hooks, :url, :deps, :status )'; $stmt = $db->prepare($sql); $files = array( array( 'class' => 'class1', 'version' => 1, 'title' => 'Sample 1', 'desc' => 'Sample description 1', 'url' => 'Sample url 1', ), array( 'class' => 'class2', 'version' => 2, 'title' => 'Sample 2', 'desc' => 'Sample description 2', 'url' => 'Sample url 2', ), ); foreach ($files as $file) { /* @var $stmt SQLite3Stmt */ $stmt->bindValue(':class', $file['class'], SQLITE3_TEXT); /* @var $plugin Pone_PluginInfo */ $stmt->bindValue(':version', (int) $file['version'], SQLITE3_INTEGER); $stmt->bindValue(':title', $file['title'], SQLITE3_TEXT); $stmt->bindValue(':desc', $file['desc'], SQLITE3_TEXT); $stmt->bindValue(':author', $file['class'], SQLITE3_TEXT); $stmt->bindValue(':hooks', $file['class'], SQLITE3_TEXT); $stmt->bindValue(':url', $file['url'], SQLITE3_TEXT); $stmt->bindValue(':deps', $file['class'], SQLITE3_TEXT); $stmt->bindValue(':status', 3, SQLITE3_INTEGER); $rs = $stmt->execute(); if (false !== $rs) { /* @var $rs SQLite3Result */ // Closes the result set $rs->finalize(); } else { echo 'Error: '. $db->lastErrorMsg(); } // Resets the prepared statement to its state prior to execution. // All bindings remain intact after reset. // $stmt->reset(); $stmt->clear(); } // Close statement $stmt->close(); // Close database connection. // Error: Unable to close due to unfinalised statements $db->close(); ?>