📄 sqlite.php
字号:
<?php
// +----------------------------------------------------------------------+
// | PHP versions 4 and 5 |
// +----------------------------------------------------------------------+
// | Copyright (c) 1998-2008 Manuel Lemos, Tomas V.V.Cox, |
// | Stig. S. Bakken, Lukas Smith, Lorenzo Alberton |
// | All rights reserved. |
// +----------------------------------------------------------------------+
// | MDB2 is a merge of PEAR DB and Metabases that provides a unified DB |
// | API as well as database abstraction for PHP applications. |
// | This LICENSE is in the BSD license style. |
// | |
// | Redistribution and use in source and binary forms, with or without |
// | modification, are permitted provided that the following conditions |
// | are met: |
// | |
// | Redistributions of source code must retain the above copyright |
// | notice, this list of conditions and the following disclaimer. |
// | |
// | Redistributions in binary form must reproduce the above copyright |
// | notice, this list of conditions and the following disclaimer in the |
// | documentation and/or other materials provided with the distribution. |
// | |
// | Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, |
// | Lukas Smith nor the names of his contributors may be used to endorse |
// | or promote products derived from this software without specific prior|
// | written permission. |
// | |
// | THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
// | "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
// | LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS |
// | FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE |
// | REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, |
// | INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, |
// | BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS|
// | OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED |
// | AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT |
// | LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY|
// | WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE |
// | POSSIBILITY OF SUCH DAMAGE. |
// +----------------------------------------------------------------------+
// | Authors: Lukas Smith <smith@pooteeweet.org> |
// | Lorenzo Alberton <l.alberton@quipo.it> |
// +----------------------------------------------------------------------+
//
// $Id: sqlite.php,v 1.74 2008/03/05 11:08:53 quipo Exp $
//
require_once 'MDB2/Driver/Manager/Common.php';
/**
* MDB2 SQLite driver for the management modules
*
* @package MDB2
* @category Database
* @author Lukas Smith <smith@pooteeweet.org>
* @author Lorenzo Alberton <l.alberton@quipo.it>
*/
class MDB2_Driver_Manager_sqlite extends MDB2_Driver_Manager_Common
{
// {{{ createDatabase()
/**
* create a new database
*
* @param string $name name of the database that should be created
* @param array $options array with charset info
*
* @return mixed MDB2_OK on success, a MDB2 error on failure
* @access public
*/
function createDatabase($name, $options = array())
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
$database_file = $db->_getDatabaseFile($name);
if (file_exists($database_file)) {
return $db->raiseError(MDB2_ERROR_ALREADY_EXISTS, null, null,
'database already exists', __FUNCTION__);
}
$php_errormsg = '';
$handle = @sqlite_open($database_file, $db->dsn['mode'], $php_errormsg);
if (!$handle) {
return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null,
(isset($php_errormsg) ? $php_errormsg : 'could not create the database file'), __FUNCTION__);
}
if (!empty($options['charset'])) {
$query = 'PRAGMA encoding = ' . $db->quote($options['charset'], 'text');
@sqlite_query($query, $handle);
}
@sqlite_close($handle);
return MDB2_OK;
}
// }}}
// {{{ dropDatabase()
/**
* drop an existing database
*
* @param string $name name of the database that should be dropped
* @return mixed MDB2_OK on success, a MDB2 error on failure
* @access public
*/
function dropDatabase($name)
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
$database_file = $db->_getDatabaseFile($name);
if (!@file_exists($database_file)) {
return $db->raiseError(MDB2_ERROR_CANNOT_DROP, null, null,
'database does not exist', __FUNCTION__);
}
$result = @unlink($database_file);
if (!$result) {
return $db->raiseError(MDB2_ERROR_CANNOT_DROP, null, null,
(isset($php_errormsg) ? $php_errormsg : 'could not remove the database file'), __FUNCTION__);
}
return MDB2_OK;
}
// }}}
// {{{ _getAdvancedFKOptions()
/**
* Return the FOREIGN KEY query section dealing with non-standard options
* as MATCH, INITIALLY DEFERRED, ON UPDATE, ...
*
* @param array $definition
* @return string
* @access protected
*/
function _getAdvancedFKOptions($definition)
{
$query = '';
if (!empty($definition['match'])) {
$query .= ' MATCH '.$definition['match'];
}
if (!empty($definition['onupdate']) && (strtoupper($definition['onupdate']) != 'NO ACTION')) {
$query .= ' ON UPDATE '.$definition['onupdate'];
}
if (!empty($definition['ondelete']) && (strtoupper($definition['ondelete']) != 'NO ACTION')) {
$query .= ' ON DELETE '.$definition['ondelete'];
}
if (!empty($definition['deferrable'])) {
$query .= ' DEFERRABLE';
} else {
$query .= ' NOT DEFERRABLE';
}
if (!empty($definition['initiallydeferred'])) {
$query .= ' INITIALLY DEFERRED';
} else {
$query .= ' INITIALLY IMMEDIATE';
}
return $query;
}
// }}}
// {{{ _getCreateTableQuery()
/**
* Create a basic SQL query for a new table creation
* @param string $name Name of the database that should be created
* @param array $fields Associative array that contains the definition of each field of the new table
* @param array $options An associative array of table options
* @return mixed string (the SQL query) on success, a MDB2 error on failure
* @see createTable()
*/
function _getCreateTableQuery($name, $fields, $options = array())
{
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
if (!$name) {
return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null,
'no valid table name specified', __FUNCTION__);
}
if (empty($fields)) {
return $db->raiseError(MDB2_ERROR_CANNOT_CREATE, null, null,
'no fields specified for table "'.$name.'"', __FUNCTION__);
}
$query_fields = $this->getFieldDeclarationList($fields);
if (PEAR::isError($query_fields)) {
return $query_fields;
}
if (!empty($options['primary'])) {
$query_fields.= ', PRIMARY KEY ('.implode(', ', array_keys($options['primary'])).')';
}
if (!empty($options['foreign_keys'])) {
foreach ($options['foreign_keys'] as $fkname => $fkdef) {
if (empty($fkdef)) {
continue;
}
$query_fields.= ', CONSTRAINT '.$fkname.' FOREIGN KEY ('.implode(', ', array_keys($fkdef['fields'])).')';
$query_fields.= ' REFERENCES '.$fkdef['references']['table'].' ('.implode(', ', array_keys($fkdef['references']['fields'])).')';
$query_fields.= $this->_getAdvancedFKOptions($fkdef);
}
}
$name = $db->quoteIdentifier($name, true);
$result = 'CREATE ';
if (!empty($options['temporary'])) {
$result .= $this->_getTemporaryTableQuery();
}
$result .= " TABLE $name ($query_fields)";
return $result;
}
// }}}
// {{{ createTable()
/**
* create a new table
*
* @param string $name Name of the database that should be created
* @param array $fields Associative array that contains the definition of each field of the new table
* @param array $options An associative array of table options
* @return mixed MDB2_OK on success, a MDB2 error on failure
* @access public
*/
function createTable($name, $fields, $options = array())
{
$result = parent::createTable($name, $fields, $options);
if (PEAR::isError($result)) {
return $result;
}
// create triggers to enforce FOREIGN KEY constraints
if (!empty($options['foreign_keys'])) {
$db =& $this->getDBInstance();
if (PEAR::isError($db)) {
return $db;
}
foreach ($options['foreign_keys'] as $fkname => $fkdef) {
if (empty($fkdef)) {
continue;
}
//set actions to 'RESTRICT' if not set
$fkdef['onupdate'] = empty($fkdef['onupdate']) ? 'RESTRICT' : strtoupper($fkdef['onupdate']);
$fkdef['ondelete'] = empty($fkdef['ondelete']) ? 'RESTRICT' : strtoupper($fkdef['ondelete']);
$trigger_names = array(
'insert' => $fkname.'_insert_trg',
'update' => $fkname.'_update_trg',
'pk_update' => $fkname.'_pk_update_trg',
'pk_delete' => $fkname.'_pk_delete_trg',
);
//create the [insert|update] triggers on the FK table
$table_fields = array_keys($fkdef['fields']);
$referenced_fields = array_keys($fkdef['references']['fields']);
$query = 'CREATE TRIGGER %s BEFORE %s ON '.$name
.' FOR EACH ROW BEGIN'
.' SELECT RAISE(ROLLBACK, \'%s on table "'.$name.'" violates FOREIGN KEY constraint "'.$fkname.'"\')'
.' WHERE (SELECT ';
$aliased_fields = array();
foreach ($referenced_fields as $field) {
$aliased_fields[] = $fkdef['references']['table'] .'.'.$field .' AS '.$field;
}
$query .= implode(',', $aliased_fields)
.' FROM '.$fkdef['references']['table']
.' WHERE ';
$conditions = array();
for ($i=0; $i<count($table_fields); $i++) {
$conditions[] = $referenced_fields[$i] .' = NEW.'.$table_fields[$i];
}
$query .= implode(' AND ', $conditions).') IS NULL; END;';
$result = $db->exec(sprintf($query, $trigger_names['insert'], 'INSERT', 'insert'));
if (PEAR::isError($result)) {
return $result;
}
$result = $db->exec(sprintf($query, $trigger_names['update'], 'UPDATE', 'update'));
if (PEAR::isError($result)) {
return $result;
}
//create the ON [UPDATE|DELETE] triggers on the primary table
$restrict_action = 'SELECT RAISE(ROLLBACK, \'%s on table "'.$name.'" violates FOREIGN KEY constraint "'.$fkname.'"\')'
.' WHERE (SELECT ';
$aliased_fields = array();
foreach ($table_fields as $field) {
$aliased_fields[] = $name .'.'.$field .' AS '.$field;
}
$restrict_action .= implode(',', $aliased_fields)
.' FROM '.$name
.' WHERE ';
$conditions = array();
$new_values = array();
$null_values = array();
for ($i=0; $i<count($table_fields); $i++) {
$conditions[] = $table_fields[$i] .' = OLD.'.$referenced_fields[$i];
$new_values[] = $table_fields[$i] .' = NEW.'.$referenced_fields[$i];
$null_values[] = $table_fields[$i] .' = NULL';
}
$restrict_action .= implode(' AND ', $conditions).') IS NOT NULL';
$cascade_action_update = 'UPDATE '.$name.' SET '.implode(', ', $new_values) .' WHERE '.implode(' AND ', $conditions);
$cascade_action_delete = 'DELETE FROM '.$name.' WHERE '.implode(' AND ', $conditions);
$setnull_action = 'UPDATE '.$name.' SET '.implode(', ', $null_values).' WHERE '.implode(' AND ', $conditions);
if ('SET DEFAULT' == $fkdef['onupdate'] || 'SET DEFAULT' == $fkdef['ondelete']) {
$db->loadModule('Reverse', null, true);
$default_values = array();
foreach ($table_fields as $table_field) {
$field_definition = $db->reverse->getTableFieldDefinition($name, $field);
if (PEAR::isError($field_definition)) {
return $field_definition;
}
$default_values[] = $table_field .' = '. $field_definition[0]['default'];
}
$setdefault_action = 'UPDATE '.$name.' SET '.implode(', ', $default_values).' WHERE '.implode(' AND ', $conditions);
}
$query = 'CREATE TRIGGER %s'
.' %s ON '.$fkdef['references']['table']
.' FOR EACH ROW BEGIN ';
if ('CASCADE' == $fkdef['onupdate']) {
$sql_update = sprintf($query, $trigger_names['pk_update'], 'AFTER UPDATE', 'update') . $cascade_action_update. '; END;';
} elseif ('SET NULL' == $fkdef['onupdate']) {
$sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setnull_action. '; END;';
} elseif ('SET DEFAULT' == $fkdef['onupdate']) {
$sql_update = sprintf($query, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . $setdefault_action. '; END;';
} elseif ('NO ACTION' == $fkdef['onupdate']) {
$sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'AFTER UPDATE', 'update') . '; END;';
} else {
//'RESTRICT'
$sql_update = sprintf($query.$restrict_action, $trigger_names['pk_update'], 'BEFORE UPDATE', 'update') . '; END;';
}
if ('CASCADE' == $fkdef['ondelete']) {
$sql_delete = sprintf($query, $trigger_names['pk_delete'], 'AFTER DELETE', 'delete') . $cascade_action_delete. '; END;';
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -