sqlite.php

开源邮件管理系统

        if (PEAR::isError($db)) {
            return $db;
        }

        $table = $db->quote($table, 'text');
        $query = "SELECT sql FROM sqlite_master WHERE type='index' AND ";
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $query.= 'LOWER(tbl_name)='.strtolower($table);
        } else {
            $query.= "tbl_name=$table";
        }
        $query.= " AND sql NOT NULL ORDER BY name";
        $indexes = $db->queryCol($query, 'text');
        if (PEAR::isError($indexes)) {
            return $indexes;
        }

        $result = array();
        foreach ($indexes as $sql) {
            if (preg_match("/^create index ([^ ]+) on /i", $sql, $tmp)) {
                $index = $this->_fixIndexName($tmp[1]);
                if (!empty($index)) {
                    $result[$index] = true;
                }
            }
        }

        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }
        return array_keys($result);
    }

    // }}}
    // {{{ createConstraint()

    /**
     * create a constraint on a table
     *
     * @param string $table      name of the table on which the constraint is to be created
     * @param string $name       name of the constraint to be created
     * @param array  $definition associative array that defines properties of the constraint to be created.
     *                           Currently, only one property named FIELDS is supported. This property
     *                           is also an associative with the names of the constraint fields as array
     *                           constraints. Each entry of this array is set to another type of associative
     *                           array that specifies properties of the constraint that are specific to
     *                           each field.
     *
     *                           Example
     *                              array(
     *                                  'fields' => array(
     *                                      'user_name' => array(),
     *                                      'last_login' => array()
     *                                  )
     *                              )
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function createConstraint($table, $name, $definition)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        if (!empty($definition['primary'])) {
            return $db->manager->alterTable($table, array(), false, array('primary' => $definition['fields']));
        }
        
        if (!empty($definition['foreign'])) {
            return $db->manager->alterTable($table, array(), false, array('foreign_keys' => array($name => $definition)));
        }

        $table = $db->quoteIdentifier($table, true);
        $name  = $db->getIndexName($name);
        $query = "CREATE UNIQUE INDEX $name ON $table";
        $fields = array();
        foreach ($definition['fields'] as $field_name => $field) {
            $field_string = $field_name;
            if (!empty($field['sorting'])) {
                switch ($field['sorting']) {
                case 'ascending':
                    $field_string.= ' ASC';
                    break;
                case 'descending':
                    $field_string.= ' DESC';
                    break;
                }
            }
            $fields[] = $field_string;
        }
        $query .= ' ('.implode(', ', $fields) . ')';
        return $db->exec($query);
    }

    // }}}
    // {{{ dropConstraint()

    /**
     * drop existing constraint
     *
     * @param string    $table        name of table that should be used in method
     * @param string    $name         name of the constraint to be dropped
     * @param string    $primary      hint if the constraint is primary
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function dropConstraint($table, $name, $primary = false)
    {
        if ($primary || $name == 'PRIMARY') {
            return $this->alterTable($table, array(), false, array('primary' => null));
        }

        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        //is it a FK constraint? If so, also delete the associated triggers
        $db->loadModule('Reverse', null, true);
        $definition = $db->reverse->getTableConstraintDefinition($table, $name);
        if (!PEAR::isError($definition) && !empty($definition['foreign'])) {
            //first drop the FK enforcing triggers
            $result = $this->_dropFKTriggers($table, $name, $definition['references']['table']);
            if (PEAR::isError($result)) {
                return $result;
            }
            //then drop the constraint itself
            return $this->alterTable($table, array(), false, array('foreign_keys' => array($name => null)));
        }

        $name = $db->getIndexName($name);
        return $db->exec("DROP INDEX $name");
    }

    // }}}
    // {{{ _dropFKTriggers()
    
    /**
     * Drop the triggers created to enforce the FOREIGN KEY constraint on the table
     *
     * @param string $table  table name
     * @param string $fkname FOREIGN KEY constraint name
     * @param string $referenced_table  referenced table name
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access private
     */
    function _dropFKTriggers($table, $fkname, $referenced_table)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $triggers  = $this->listTableTriggers($table);
        $triggers2 = $this->listTableTriggers($referenced_table);
        if (!PEAR::isError($triggers2) && !PEAR::isError($triggers)) {
            $triggers = array_merge($triggers, $triggers2);
            $pattern = '/^'.$fkname.'(_pk)?_(insert|update|delete)_trg$/i';
            foreach ($triggers as $trigger) {
                if (preg_match($pattern, $trigger)) {
                    $result = $db->exec('DROP TRIGGER '.$trigger);
                    if (PEAR::isError($result)) {
                        return $result;
                    }
                }
            }
        }
        return MDB2_OK;
    }

    // }]]
    // {{{ listTableConstraints()

    /**
     * list all constraints in a table
     *
     * @param string $table name of table that should be used in method
     * @return mixed array of constraint names on success, a MDB2 error on failure
     * @access public
     */
    function listTableConstraints($table)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $table = $db->quote($table, 'text');
        $query = "SELECT sql FROM sqlite_master WHERE type='index' AND ";
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $query.= 'LOWER(tbl_name)='.strtolower($table);
        } else {
            $query.= "tbl_name=$table";
        }
        $query.= " AND sql NOT NULL ORDER BY name";
        $indexes = $db->queryCol($query, 'text');
        if (PEAR::isError($indexes)) {
            return $indexes;
        }

        $result = array();
        foreach ($indexes as $sql) {
            if (preg_match("/^create unique index ([^ ]+) on /i", $sql, $tmp)) {
                $index = $this->_fixIndexName($tmp[1]);
                if (!empty($index)) {
                    $result[$index] = true;
                }
            }
        }
        
        // also search in table definition for PRIMARY KEYs...
        $query = "SELECT sql FROM sqlite_master WHERE type='table' AND ";
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $query.= 'LOWER(name)='.strtolower($table);
        } else {
            $query.= "name=$table";
        }
        $query.= " AND sql NOT NULL ORDER BY name";
        $table_def = $db->queryOne($query, 'text');
        if (PEAR::isError($table_def)) {
            return $table_def;
        }
        if (preg_match("/\bPRIMARY\s+KEY\b/i", $table_def, $tmp)) {
            $result['primary'] = true;
        }

        // ...and for FOREIGN KEYs
        if (preg_match_all("/\bCONSTRAINT\b\s+([^\s]+)\s+\bFOREIGN\s+KEY/imsx", $table_def, $tmp)) {
            foreach ($tmp[1] as $fk) {
                $result[$fk] = true;
            }
        }

        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_change_key_case($result, $db->options['field_case']);
        }
        return array_keys($result);
    }

    // }}}
    // {{{ createSequence()

    /**
     * create sequence
     *
     * @param string    $seq_name     name of the sequence to be created
     * @param string    $start         start value of the sequence; default is 1
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function createSequence($seq_name, $start = 1)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        $seqcol_name = $db->quoteIdentifier($db->options['seqcol_name'], true);
        $query = "CREATE TABLE $sequence_name ($seqcol_name INTEGER PRIMARY KEY DEFAULT 0 NOT NULL)";
        $res = $db->exec($query);
        if (PEAR::isError($res)) {
            return $res;
        }
        if ($start == 1) {
            return MDB2_OK;
        }
        $res = $db->exec("INSERT INTO $sequence_name ($seqcol_name) VALUES (".($start-1).')');
        if (!PEAR::isError($res)) {
            return MDB2_OK;
        }
        // Handle error
        $result = $db->exec("DROP TABLE $sequence_name");
        if (PEAR::isError($result)) {
            return $db->raiseError($result, null, null,
                'could not drop inconsistent sequence table', __FUNCTION__);
        }
        return $db->raiseError($res, null, null,
            'could not create sequence table', __FUNCTION__);
    }

    // }}}
    // {{{ dropSequence()

    /**
     * drop existing sequence
     *
     * @param string    $seq_name     name of the sequence to be dropped
     * @return mixed MDB2_OK on success, a MDB2 error on failure
     * @access public
     */
    function dropSequence($seq_name)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $sequence_name = $db->quoteIdentifier($db->getSequenceName($seq_name), true);
        return $db->exec("DROP TABLE $sequence_name");
    }

    // }}}
    // {{{ listSequences()

    /**
     * list all sequences in the current database
     *
     * @return mixed array of sequence names on success, a MDB2 error on failure
     * @access public
     */
    function listSequences()
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $query = "SELECT name FROM sqlite_master WHERE type='table' AND sql NOT NULL ORDER BY name";
        $table_names = $db->queryCol($query);
        if (PEAR::isError($table_names)) {
            return $table_names;
        }
        $result = array();
        foreach ($table_names as $table_name) {
            if ($sqn = $this->_fixSequenceName($table_name, true)) {
                $result[] = $sqn;
            }
        }
        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            $result = array_map(($db->options['field_case'] == CASE_LOWER ? 'strtolower' : 'strtoupper'), $result);
        }
        return $result;
    }

    // }}}
}
?>