コード例 #1
0
function get_domain_definitions($olddomains)
{
    global $dbhandle, $s_charsets;
    $sql = 'SELECT  F.RDB$FIELD_NAME AS DNAME,' . ' F.RDB$FIELD_TYPE AS FTYPE,' . ' F.RDB$FIELD_SUB_TYPE AS STYPE,' . ' F.RDB$FIELD_LENGTH AS FLEN,' . ' F.RDB$FIELD_PRECISION AS FPREC,' . ' F.RDB$FIELD_SCALE AS FSCALE,' . ' F.RDB$SEGMENT_LENGTH AS SEGLEN,' . ' F.RDB$CHARACTER_SET_ID AS CHARID,' . ' F.RDB$COLLATION_ID AS COLLID,' . ' F.RDB$NULL_FLAG AS NFLAG,' . ' F.RDB$DEFAULT_SOURCE AS DSOURCE,' . ' F.RDB$VALIDATION_SOURCE AS VSOURCE' . ' FROM  RDB$FIELDS F ' . ' WHERE  (RDB$SYSTEM_FLAG=0 OR RDB$SYSTEM_FLAG IS NULL)' . " AND  RDB\$FIELD_NAME NOT STARTING WITH 'RDB\$'" . ' ORDER  BY F.RDB$FIELD_NAME';
    $res = fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    $domains = array();
    while ($obj = fbird_fetch_object($res)) {
        $dname = trim($obj->DNAME);
        $stype = isset($obj->STYPE) ? $obj->STYPE : 0;
        $domains[$dname]['type'] = get_datatype($obj->FTYPE, $stype);
        if ($stype != 0) {
            $domains[$dname]['stype'] = $stype;
        }
        if ($domains[$dname]['type'] == 'VARCHAR' || $domains[$dname]['type'] == 'CHARACTER') {
            $domains[$dname]['size'] = $obj->FLEN;
        }
        if (isset($obj->CHARID)) {
            $domains[$dname]['charset'] = $s_charsets[$obj->CHARID]['name'];
        }
        $domains[$dname]['collate'] = isset($obj->COLLID) && $obj->COLLID != 0 ? $s_charsets[$obj->CHARID]['collations'][$obj->COLLID] : NULL;
        if ($domains[$dname]['type'] == 'DECIMAL' || $domains[$dname]['type'] == 'NUMERIC') {
            $domains[$dname]['prec'] = $obj->FPREC;
            $domains[$dname]['scale'] = -$obj->FSCALE;
        }
        if ($domains[$dname]['type'] == 'BLOB') {
            $domains[$dname]['segsize'] = $obj->SEGLEN;
        }
        $domains[$dname]['notnull'] = isset($obj->NFLAG) && !empty($obj->NFLAG) ? TRUE : FALSE;
        $domains[$dname]['default'] = isset($obj->DSOURCE) && !empty($obj->DSOURCE) ? get_domain_default($dname) : '';
        $domains[$dname]['check'] = isset($obj->VSOURCE) && !empty($obj->VSOURCE) ? get_domain_check($dname) : '';
        $domains[$dname]['status'] = isset($olddomains[$dname]) ? $olddomains[$dname]['status'] : 'close';
    }
    fbird_free_result($res);
    return $domains;
}
コード例 #2
0
function get_systable($s_systable)
{
    global $dbhandle;
    // get the field names and types
    $sql = 'SELECT RDB$RELATION_FIELDS.RDB$FIELD_NAME AS FNAME,' . ' RDB$RELATION_FIELDS.RDB$FIELD_POSITION,' . ' RDB$FIELD_TYPE AS FTYPE,' . ' RDB$FIELD_SUB_TYPE AS STYPE' . ' FROM RDB$RELATION_FIELDS, RDB$FIELDS' . ' WHERE RDB$RELATION_NAME=\'' . $s_systable['table'] . '\'' . ' AND RDB$FIELD_SOURCE=RDB$FIELDS.RDB$FIELD_NAME' . ' ORDER BY RDB$FIELD_POSITION';
    $res = fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    $table = array();
    while ($row = fbird_fetch_object($res)) {
        $type = isset($row->FTYPE) ? $row->FTYPE : NULL;
        $stype = isset($row->STYPE) ? $row->STYPE : NULL;
        $table[trim($row->FNAME)]['type'] = get_datatype($type, $stype);
    }
    fbird_free_result($res);
    // get the table content
    $sql = 'SELECT *' . ' FROM ' . $s_systable['table'];
    if ($s_systable['sysdata'] == FALSE) {
        $fields = array_keys($table);
        $sql .= ' WHERE ' . pos($fields) . " NOT LIKE 'RDB\$%'" . ' AND ' . pos($fields) . " NOT LIKE 'TMP\$%'";
    }
    // handle the filter
    if (!empty($s_systable['ffield']) && in_array($s_systable['ffield'], array_keys($table))) {
        $sql .= $s_systable['sysdata'] == TRUE ? ' WHERE ' : ' AND ';
        switch ($s_systable['fvalue']) {
            case '':
                $sql .= $s_systable['ffield'] . ' IS NULL';
                break;
            case 'BLOB':
                if ($table[$s_systable['ffield']]['type'] == 'BLOB') {
                    $sql .= $s_systable['ffield'] . " IS NOT NULL";
                    break;
                }
            default:
                $sql .= $s_systable['ffield'] . "='" . $s_systable['fvalue'] . "'";
        }
    }
    if (!empty($s_systable['order'])) {
        $sql .= ' ORDER BY ' . $s_systable['order'] . ' ' . $s_systable['dir'];
    }
    $res = fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    while ($row = fbird_fetch_object($res)) {
        foreach (array_keys($table) as $fname) {
            if ($row->{$fname} === 0) {
                $table[$fname]['col'][] = '0';
            } elseif (!isset($row->{$fname}) || empty($row->{$fname})) {
                $table[$fname]['col'][] = ' ';
            } elseif ($table[$fname]['type'] == 'BLOB') {
                $table[$fname]['col'][] = '<i>BLOB</i>';
            } else {
                $table[$fname]['col'][] = trim($row->{$fname});
            }
        }
    }
    fbird_free_result($res);
    return $table;
}
コード例 #3
0
function get_udfs($order = 1, $dir = 'ASC')
{
    global $dbhandle;
    $sql = 'SELECT F.RDB$FUNCTION_NAME AS FNAME,' . ' F.RDB$MODULE_NAME AS MODULE,' . ' F.RDB$ENTRYPOINT AS EPOINT,' . ' F.RDB$RETURN_ARGUMENT AS RPOS,' . ' A.RDB$ARGUMENT_POSITION AS APOS,' . ' A.RDB$FIELD_TYPE AS FTYPE,' . ' A.RDB$FIELD_SUB_TYPE AS STYPE,' . ' A.RDB$FIELD_SCALE AS SCALE,' . ' A.RDB$FIELD_LENGTH AS FLENGTH,' . ' A.RDB$FIELD_PRECISION AS PREC' . ' FROM RDB$FUNCTIONS F' . ' INNER JOIN RDB$FUNCTION_ARGUMENTS A' . ' ON F.RDB$FUNCTION_NAME=A.RDB$FUNCTION_NAME' . ' ORDER BY ' . $order . ' ' . $dir;
    $res = fbird_query($dbhandle, $sql) or ib_error($sql);
    $udfs = array();
    while ($obj = fbird_fetch_object($res)) {
        $fname = trim($obj->FNAME);
        $udfs[$fname]['module'] = trim($obj->MODULE);
        $udfs[$fname]['entrypoint'] = trim($obj->EPOINT);
        if ($obj->APOS == $obj->RPOS) {
            $udfs[$fname]['returns'] = get_datatype($obj->FTYPE, $obj->STYPE) . get_datatye_size_string($obj->FTYPE, $obj->FLENGTH, $obj->PREC, $obj->SCALE);
        } else {
            $udfs[$fname]['params'][$obj->APOS] = get_datatype($obj->FTYPE, $obj->STYPE) . get_datatye_size_string($obj->FTYPE, $obj->FLENGTH, $obj->PREC, $obj->SCALE);
        }
    }
    return $udfs;
}
コード例 #4
0
function get_procedure_parameters($name)
{
    global $dbhandle, $s_charsets;
    $sql = 'SELECT P.RDB$PARAMETER_NAME PNAME,' . ' P.RDB$PARAMETER_TYPE PTYPE,' . ' F.RDB$FIELD_NAME AS DNAME,' . ' F.RDB$FIELD_TYPE AS FTYPE,' . ' F.RDB$FIELD_SUB_TYPE AS STYPE,' . ' F.RDB$FIELD_LENGTH AS FLEN,' . ' F.RDB$FIELD_PRECISION AS FPREC,' . ' F.RDB$FIELD_SCALE AS FSCALE,' . ' F.RDB$SEGMENT_LENGTH AS SEGLEN,' . ' F.RDB$CHARACTER_SET_ID AS CHARID,' . ' F.RDB$COLLATION_ID AS COLLID' . ' FROM RDB$PROCEDURE_PARAMETERS P' . ' INNER JOIN RDB$FIELDS F ON P.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME' . " WHERE P.RDB\$PROCEDURE_NAME='" . $name . "'";
    $res = fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    $in = $out = array();
    while ($obj = fbird_fetch_object($res)) {
        $ptype = $obj->PTYPE == 0 ? 'in' : 'out';
        $stype = isset($obj->STYPE) ? $obj->STYPE : NULL;
        $type = get_datatype($obj->FTYPE, $stype);
        if (in_array($type, array('DECIMAL', 'NUMERIC'))) {
            $prec = $obj->FPREC;
            $scale = -$obj->FSCALE;
            $stype = NULL;
        } else {
            $prec = $scale = NULL;
        }
        ${$ptype}[] = array('name' => trim($obj->PNAME), 'type' => $type, 'stype' => $stype, 'size' => in_array($type, array('VARCHAR', 'CHARACTER')) ? $obj->FLEN : NULL, 'charset' => isset($obj->CHARID) ? $s_charsets[$obj->CHARID]['name'] : NULL, 'collate' => isset($obj->COLLID) && $obj->COLLID != 0 ? $s_charsets[$obj->CHARID]['collations'][$obj->COLLID] : NULL, 'prec' => $prec, 'scale' => $scale, 'segsize' => $type == 'BLOB' ? $obj->SEGLEN : NULL);
    }
    return array($in, $out);
}
コード例 #5
0
function get_tables()
{
    global $dbhandle, $ib_error, $s_tables, $s_fields, $s_foreigns, $s_primaries, $s_uniques, $s_login;
    global $s_charsets, $s_tables_counts, $s_views_counts, $s_tables_def, $s_tables_comp;
    $previous = $s_tables;
    $s_tables = array();
    $s_fields = array();
    // get the tablenames, owner and view flag
    $sql = 'SELECT RDB$RELATION_NAME AS RNAME,' . ' RDB$VIEW_BLR AS VBLR,' . ' RDB$OWNER_NAME AS OWNER' . ' FROM RDB$RELATIONS' . ' WHERE RDB$SYSTEM_FLAG=0' . ' ORDER BY RDB$RELATION_NAME';
    $res = @fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    if (!is_resource($res)) {
        return FALSE;
    }
    // initialize $s_tables[]
    while ($row = fbird_fetch_object($res)) {
        $tablename = trim($row->RNAME);
        $s_tables[$tablename]['status'] = isset($previous[$tablename]) ? $previous[$tablename]['status'] : 'close';
        $s_tables[$tablename]['is_view'] = isset($row->VBLR) && $row->VBLR !== NULL ? TRUE : FALSE;
        $s_tables[$tablename]['owner'] = trim($row->OWNER);
        $s_tables[$tablename]['privileges'] = array();
    }
    fbird_free_result($res);
    unset($previous);
    // get privileges on tables for the current user and for the role used at login
    $sql = 'SELECT R.RDB$RELATION_NAME AS RNAME,' . ' P1.RDB$PRIVILEGE AS PRIV' . ' FROM RDB$RELATIONS R' . ' INNER JOIN RDB$USER_PRIVILEGES P1' . ' ON R.RDB$RELATION_NAME=P1.RDB$RELATION_NAME' . ' WHERE R.RDB$SYSTEM_FLAG=0' . " AND (P1.RDB\$USER='******'user'] . "' OR P1.RDB\$USER='******')";
    if (!empty($s_login['role'])) {
        $sql .= ' UNION' . ' SELECT R.RDB$RELATION_NAME AS RNAME,' . ' P2.RDB$PRIVILEGE AS PRIV' . ' FROM RDB$USER_PRIVILEGES P1' . ' INNER JOIN RDB$USER_PRIVILEGES P2 ON P1.RDB$RELATION_NAME=P2.RDB$USER' . ' INNER JOIN RDB$RELATIONS R ON R.RDB$RELATION_NAME=P2.RDB$RELATION_NAME' . " WHERE P1.RDB\$PRIVILEGE='M'" . ' AND R.RDB$SYSTEM_FLAG=0' . " AND P1.RDB\$RELATION_NAME='" . $s_login['role'] . "'" . " AND (P1.RDB\$USER='******'user'] . "' OR P1.RDB\$USER='******')";
    }
    $res = @fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    while ($row = fbird_fetch_object($res)) {
        $s_tables[trim($row->RNAME)]['privileges'][] = trim($row->PRIV);
    }
    fbird_free_result($res);
    // find the check, not null, unique, pk and fk and  constraints
    $sql = 'SELECT RC.RDB$RELATION_NAME TNAME,' . ' RC.RDB$CONSTRAINT_TYPE RTYPE,' . ' RC.RDB$CONSTRAINT_NAME CNAME,' . ' RC.RDB$INDEX_NAME INAME,' . ' CC.RDB$TRIGGER_NAME TRIGNAME,' . ' SE.RDB$FIELD_NAME SENAME,' . ' SE.RDB$FIELD_POSITION POS,' . ' DP.RDB$FIELD_NAME DPNAME' . ' FROM RDB$RELATION_CONSTRAINTS RC' . ' LEFT JOIN RDB$CHECK_CONSTRAINTS CC' . ' ON RC.RDB$CONSTRAINT_NAME=CC.RDB$CONSTRAINT_NAME' . " AND RC.RDB\$CONSTRAINT_TYPE='CHECK'" . ' LEFT JOIN RDB$INDEX_SEGMENTS SE' . ' ON RC.RDB$INDEX_NAME=SE.RDB$INDEX_NAME' . ' LEFT JOIN RDB$DEPENDENCIES DP' . ' ON CC.RDB$TRIGGER_NAME=DP.RDB$DEPENDENT_NAME' . ' ORDER BY RC.RDB$RELATION_NAME';
    $res = @fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    // reset the index infos
    $s_foreigns = array();
    $s_primaries = array();
    $s_uniques = array();
    $constraints = array();
    while ($row = fbird_fetch_object($res)) {
        $cname = trim($row->CNAME);
        switch (trim($row->RTYPE)) {
            case 'CHECK':
                $constraints[trim($row->TNAME)][trim($row->DPNAME)]['check'] = $cname;
                break;
            case 'UNIQUE':
                $constraints[trim($row->TNAME)][trim($row->SENAME)]['unique'] = $cname;
                $s_uniques[$cname]['index'] = trim($row->INAME);
                $s_uniques[$cname]['cols'] = isset($s_uniques[$cname]['cols']) ? $s_uniques[$cname]['cols']++ : 1;
                break;
            case 'FOREIGN KEY':
                $constraints[trim($row->TNAME)][trim($row->SENAME)]['foreign'] = $cname;
                $s_foreigns[$cname]['index'] = trim($row->INAME);
                $s_foreigns[$cname]['cols'] = isset($s_foreigns[$cname]['cols']) ? $s_foreigns[$cname]['cols']++ : 1;
                break;
            case 'PRIMARY KEY':
                $constraints[trim($row->TNAME)][trim($row->SENAME)]['primary'] = $cname;
                $s_primaries[$cname]['index'] = trim($row->INAME);
                $s_primaries[$cname]['cols'] = isset($s_primaries[$cname]['cols']) ? $s_primaries[$cname]['cols']++ : 1;
                break;
        }
    }
    fbird_free_result($res);
    //     debug_var($sql);
    //     debug_var($constraints);
    //     debug_var($s_foreigns);
    //     debug_var($s_primaries);
    // find the field properties for all non-system tables
    $sql = 'SELECT DISTINCT R.RDB$FIELD_NAME AS FNAME,' . ' R.RDB$NULL_FLAG AS NFLAG,' . ' R.RDB$DEFAULT_SOURCE AS DSOURCE,' . ' R.RDB$FIELD_POSITION,' . ' R.RDB$RELATION_NAME AS TNAME,' . ' R.RDB$COLLATION_ID AS COLLID,' . ' F.RDB$FIELD_NAME AS DNAME,' . ' F.RDB$FIELD_TYPE AS FTYPE,' . ' F.RDB$FIELD_SUB_TYPE AS STYPE,' . ' F.RDB$FIELD_LENGTH AS FLEN,' . ' F.RDB$COMPUTED_SOURCE AS CSOURCE,' . ' F.RDB$FIELD_PRECISION AS FPREC,' . ' F.RDB$FIELD_SCALE AS FSCALE,' . ' F.RDB$SEGMENT_LENGTH AS SEGLEN,' . ' F.RDB$CHARACTER_SET_ID AS CHARID,' . ' D.RDB$LOWER_BOUND AS LBOUND,' . ' D.RDB$UPPER_BOUND AS UBOUND' . ' FROM RDB$RELATION_FIELDS R ' . ' JOIN RDB$FIELDS F ON R.RDB$FIELD_SOURCE=F.RDB$FIELD_NAME' . ' LEFT JOIN RDB$FIELD_DIMENSIONS D ON R.RDB$FIELD_SOURCE=D.RDB$FIELD_NAME' . ' WHERE F.RDB$SYSTEM_FLAG=0' . ' ORDER BY R.RDB$FIELD_POSITION';
    $res = @fbird_query($dbhandle, $sql) or ib_error(__FILE__, __LINE__, $sql);
    //initialize $s_fields[]
    $idx = 0;
    while ($row = fbird_fetch_object($res)) {
        $tname = trim($row->TNAME);
        $field = $s_fields[$tname][$idx]['name'] = trim($row->FNAME);
        if (strpos($row->DNAME, 'RDB$') !== 0) {
            $s_fields[$tname][$idx]['domain'] = 'Yes';
            $s_fields[$tname][$idx]['type'] = trim($row->DNAME);
        } else {
            $s_fields[$tname][$idx]['stype'] = isset($row->STYPE) ? $row->STYPE : NULL;
            $s_fields[$tname][$idx]['type'] = get_datatype($row->FTYPE, $s_fields[$tname][$idx]['stype']);
        }
        if ($s_fields[$tname][$idx]['type'] == 'VARCHAR' || $s_fields[$tname][$idx]['type'] == 'CHARACTER') {
            $s_fields[$tname][$idx]['size'] = $row->FLEN;
        }
        // field is defined as NOT NULL
        if (!empty($row->NFLAG)) {
            $s_fields[$tname][$idx]['notnull'] = 'Yes';
        }
        // this field is computed
        if (isset($row->CSOURCE)) {
            $s_fields[$tname][$idx]['comp'] = 'Yes';
            $s_fields[$tname][$idx]['csource'] = FALSE;
        }
        // this field has a default value
        if (isset($row->DSOURCE)) {
            $s_fields[$tname][$idx]['default'] = 'Yes';
            $s_fields[$tname][$idx]['dsource'] = FALSE;
        }
        if ($s_fields[$tname][$idx]['type'] == 'DECIMAL' or $s_fields[$tname][$idx]['type'] == 'NUMERIC') {
            $s_fields[$tname][$idx]['prec'] = $row->FPREC;
            $s_fields[$tname][$idx]['scale'] = -$row->FSCALE;
        }
        if ($s_fields[$tname][$idx]['type'] == 'BLOB') {
            $s_fields[$tname][$idx]['segsize'] = $row->SEGLEN;
        }
        $s_fields[$tname][$idx]['charset'] = isset($row->CHARID) ? $s_charsets[$row->CHARID]['name'] : NULL;
        $s_fields[$tname][$idx]['collate'] = isset($row->COLLID) && $row->COLLID != 0 && isset($s_charsets[$row->CHARID]['collations'][$row->COLLID]) ? $s_charsets[$row->CHARID]['collations'][$row->COLLID] : NULL;
        // optional array dimensions
        if (isset($row->LBOUND)) {
            $s_fields[$tname][$idx]['lower_bound'] = $row->LBOUND;
            $s_fields[$tname][$idx]['upper_bound'] = $row->UBOUND;
        }
        // column constraints
        foreach (array('check', 'unique', 'foreign', 'primary') as $ctype) {
            if (isset($constraints[$tname][$field][$ctype])) {
                $s_fields[$tname][$idx][$ctype] = $constraints[$tname][$field][$ctype];
            }
        }
        $idx++;
    }
    //     debug_var($s_fields);
    $quote = identifier_quote($s_login['dialect']);
    foreach ($s_tables as $name => $properties) {
        if ($s_tables_def == TRUE) {
            $s_fields = get_table_defaults_sources($name, $s_fields);
        }
        if ($s_tables_comp == TRUE) {
            $s_fields = get_table_computed_sources($name, $s_fields);
        }
        if (!in_array('S', $properties['privileges'])) {
            continue;
        }
        if ($properties['is_view'] == FALSE && $s_tables_counts == TRUE || $properties['is_view'] == TRUE && $s_views_counts == TRUE) {
            $sql = 'SELECT COUNT(*) AS CNT FROM ' . $quote . $name . $quote;
            $res = fbird_query($dbhandle, $sql) or $ib_error .= fbird_errmsg() . "<br>\n";
            if (is_resource($res)) {
                $row = fbird_fetch_object($res);
                $s_tables[$name]['count'] = $row->CNT;
                fbird_free_result($res);
            }
        }
    }
    return TRUE;
}