mssql.php

来自「开源邮件管理系统」· PHP 代码 · 共 653 行 · 第 1/2 页

        $result = $db->query(sprintf($query, $constraint_name));
        if (PEAR::isError($result)) {
            return $result;
        }

        $definition = array(
            'fields' => array()
        );
        while (is_array($row = $result->fetchRow(MDB2_FETCHMODE_ASSOC))) {
            $row = array_change_key_case($row, CASE_LOWER);
            $column_name = $row['field_name'];
            if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
                if ($db->options['field_case'] == CASE_LOWER) {
                    $column_name = strtolower($column_name);
                } else {
                    $column_name = strtoupper($column_name);
                }
            }
            $definition['fields'][$column_name] = array(
                'position' => (int)$row['field_position']
            );
            if ($row['foreign']) {
                $ref_column_name = $row['references_field'];
                if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
                    if ($db->options['field_case'] == CASE_LOWER) {
                        $ref_column_name = strtolower($ref_column_name);
                    } else {
                        $ref_column_name = strtoupper($ref_column_name);
                    }
                }
                $definition['references']['table'] = $row['references_table'];
                $definition['references']['fields'][$ref_column_name] = array(
                    'position' => (int)$row['field_position']
                );
            }
            //collation?!?
            /*
            if (!empty($row['collation'])) {
                $definition['fields'][$column_name]['sorting'] = ($row['collation'] == 'ASC'
                    ? 'ascending' : 'descending');
            }
            */
            $lastrow = $row;
            // otherwise $row is no longer usable on exit from loop
        }
        $result->free();
        if (empty($definition['fields'])) {
            return $db->raiseError(MDB2_ERROR_NOT_FOUND, null, null,
                $constraint_name . ' is not an existing table constraint', __FUNCTION__);
        }

        $definition['primary'] = (boolean)$lastrow['primary'];
        $definition['unique']  = (boolean)$lastrow['unique'];
        $definition['foreign'] = (boolean)$lastrow['foreign'];
        $definition['check']   = (boolean)$lastrow['check'];
        $definition['deferrable'] = (boolean)$lastrow['deferrable'];
        $definition['initiallydeferred'] = (boolean)$lastrow['initiallydeferred'];
        $definition['onupdate'] = $lastrow['onupdate'];
        $definition['ondelete'] = $lastrow['ondelete'];
        $definition['match']    = $lastrow['match'];

        return $definition;
    }

    // }}}
    // {{{ getTriggerDefinition()

    /**
     * Get the structure of a trigger into an array
     *
     * EXPERIMENTAL
     *
     * WARNING: this function is experimental and may change the returned value
     * at any time until labelled as non-experimental
     *
     * @param string    $trigger    name of trigger that should be used in method
     * @return mixed data array on success, a MDB2 error on failure
     * @access public
     */
    function getTriggerDefinition($trigger)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        $query = "SELECT sys1.name trigger_name,
                         sys2.name table_name,
                         c.text trigger_body,
                         c.encrypted is_encripted,
                         CASE
                           WHEN OBJECTPROPERTY(sys1.id, 'ExecIsTriggerDisabled') = 1
                           THEN 0 ELSE 1
                         END trigger_enabled,
                         CASE
                           WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsertTrigger') = 1
                           THEN 'INSERT'
                           WHEN OBJECTPROPERTY(sys1.id, 'ExecIsUpdateTrigger') = 1
                           THEN 'UPDATE'
                           WHEN OBJECTPROPERTY(sys1.id, 'ExecIsDeleteTrigger') = 1
                           THEN 'DELETE'
                         END trigger_event,
                         CASE WHEN OBJECTPROPERTY(sys1.id, 'ExecIsInsteadOfTrigger') = 1
                           THEN 'INSTEAD OF' ELSE 'AFTER'
                         END trigger_type,
                         '' trigger_comment
                    FROM sysobjects sys1
                    JOIN sysobjects sys2 ON sys1.parent_obj = sys2.id
                    JOIN syscomments c ON sys1.id = c.id
                   WHERE sys1.xtype = 'TR'
                     AND sys1.name = ". $db->quote($trigger, 'text');

        $types = array(
            'trigger_name'    => 'text',
            'table_name'      => 'text',
            'trigger_body'    => 'text',
            'trigger_type'    => 'text',
            'trigger_event'   => 'text',
            'trigger_comment' => 'text',
            'trigger_enabled' => 'boolean',
            'is_encripted'    => 'boolean',
        );

        $def = $db->queryRow($query, $types, MDB2_FETCHMODE_ASSOC);
        if (PEAR::isError($def)) {
            return $def;
        }
        $trg_body = $db->queryCol('EXEC sp_helptext '. $db->quote($trigger, 'text'), 'text');
        if (!PEAR::isError($trg_body)) {
            $def['trigger_body'] = implode(' ', $trg_body);
        }
        return $def;
    }

    // }}}
    // {{{ tableInfo()

    /**
     * Returns information about a table or a result set
     *
     * NOTE: only supports 'table' and 'flags' if <var>$result</var>
     * is a table name.
     *
     * @param object|string  $result  MDB2_result object from a query or a
     *                                 string containing the name of a table.
     *                                 While this also accepts a query result
     *                                 resource identifier, this behavior is
     *                                 deprecated.
     * @param int            $mode    a valid tableInfo mode
     *
     * @return array  an associative array with the information requested.
     *                 A MDB2_Error object on failure.
     *
     * @see MDB2_Driver_Common::tableInfo()
     */
    function tableInfo($result, $mode = null)
    {
        if (is_string($result)) {
           return parent::tableInfo($result, $mode);
        }

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

        $resource = MDB2::isResultCommon($result) ? $result->getResource() : $result;
        if (!is_resource($resource)) {
            return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA, null, null,
                'Could not generate result resource', __FUNCTION__);
        }

        if ($db->options['portability'] & MDB2_PORTABILITY_FIX_CASE) {
            if ($db->options['field_case'] == CASE_LOWER) {
                $case_func = 'strtolower';
            } else {
                $case_func = 'strtoupper';
            }
        } else {
            $case_func = 'strval';
        }

        $count = @mssql_num_fields($resource);
        $res   = array();

        if ($mode) {
            $res['num_fields'] = $count;
        }

        $db->loadModule('Datatype', null, true);
        for ($i = 0; $i < $count; $i++) {
            $res[$i] = array(
                'table' => '',
                'name'  => $case_func(@mssql_field_name($resource, $i)),
                'type'  => @mssql_field_type($resource, $i),
                'length'   => @mssql_field_length($resource, $i),
                'flags' => '',
            );
            $mdb2type_info = $db->datatype->mapNativeDatatype($res[$i]);
            if (PEAR::isError($mdb2type_info)) {
               return $mdb2type_info;
            }
            $res[$i]['mdb2type'] = $mdb2type_info[0][0];
            if ($mode & MDB2_TABLEINFO_ORDER) {
                $res['order'][$res[$i]['name']] = $i;
            }
            if ($mode & MDB2_TABLEINFO_ORDERTABLE) {
                $res['ordertable'][$res[$i]['table']][$res[$i]['name']] = $i;
            }
        }

        return $res;
    }

    // }}}
    // {{{ _mssql_field_flags()

    /**
     * Get a column's flags
     *
     * Supports "not_null", "primary_key",
     * "auto_increment" (mssql identity), "timestamp" (mssql timestamp),
     * "unique_key" (mssql unique index, unique check or primary_key) and
     * "multiple_key" (multikey index)
     *
     * mssql timestamp is NOT similar to the mysql timestamp so this is maybe
     * not useful at all - is the behaviour of mysql_field_flags that primary
     * keys are alway unique? is the interpretation of multiple_key correct?
     *
     * @param string $table   the table name
     * @param string $column  the field name
     *
     * @return string  the flags
     *
     * @access protected
     * @author Joern Barthel <[e-mail]>
     */
    function _mssql_field_flags($table, $column)
    {
        $db =& $this->getDBInstance();
        if (PEAR::isError($db)) {
            return $db;
        }

        static $tableName = null;
        static $flags = array();

        if ($table != $tableName) {

            $flags = array();
            $tableName = $table;

            // get unique and primary keys
            $res = $db->queryAll("EXEC SP_HELPINDEX[$table]", null, MDB2_FETCHMODE_ASSOC);

            foreach ($res as $val) {
                $val = array_change_key_case($val, CASE_LOWER);
                $keys = explode(', ', $val['index_keys']);

                if (sizeof($keys) > 1) {
                    foreach ($keys as $key) {
                        $this->_add_flag($flags[$key], 'multiple_key');
                    }
                }

                if (strpos($val['index_description'], 'primary key')) {
                    foreach ($keys as $key) {
                        $this->_add_flag($flags[$key], 'primary_key');
                    }
                } elseif (strpos($val['index_description'], 'unique')) {
                    foreach ($keys as $key) {
                        $this->_add_flag($flags[$key], 'unique_key');
                    }
                }
            }

            // get auto_increment, not_null and timestamp
            $res = $db->queryAll("EXEC SP_COLUMNS[$table]", null, MDB2_FETCHMODE_ASSOC);

            foreach ($res as $val) {
                $val = array_change_key_case($val, CASE_LOWER);
                if ($val['nullable'] == '0') {
                    $this->_add_flag($flags[$val['column_name']], 'not_null');
                }
                if (strpos($val['type_name'], 'identity')) {
                    $this->_add_flag($flags[$val['column_name']], 'auto_increment');
                }
                if (strpos($val['type_name'], 'timestamp')) {
                    $this->_add_flag($flags[$val['column_name']], 'timestamp');
                }
            }
        }

        if (!empty($flags[$column])) {
            return(implode(' ', $flags[$column]));
        }
        return '';
    }

    // }}}
    // {{{ _add_flag()

    /**
     * Adds a string to the flags array if the flag is not yet in there
     * - if there is no flag present the array is created
     *
     * @param array  &$array  the reference to the flag-array
     * @param string $value   the flag value
     *
     * @return void
     *
     * @access protected
     * @author Joern Barthel <[e-mail]>
     */
    function _add_flag(&$array, $value)
    {
        if (!is_array($array)) {
            $array = array($value);
        } elseif (!in_array($value, $array)) {
            array_push($array, $value);
        }
    }

    // }}}
}
?>