PHP 针对人大金仓KingbaseES自动生成数据字典

置顶树下水月

已于 2023-11-28 14:35:21 修改

阅读量889

点赞数 9

文章标签： php 开发语言

于 2023-11-26 14:13:59 首次发布

本文为博主原创文章，未经博主允许不得转载。

本文链接：https://blog.csdn.net/u013416034/article/details/134627294

版权

针对国产数据库人大金仓KingbaseES 其实php 连接采用pdo方式

必须：需要去人大数据金仓官方网站下载对应版本的pdo_kdb 扩展驱动

其连接方法与pgsql 数据库连接方法大致相同不解释直接上代码：

<?php
/**
 * 生成人大金仓数据字典
 */
header("Content-type:text/html;charset=utf-8");

// 配置数据库
$database = array();
$database['DB_HOST'] = '127.0.0.1';
$database['DB_NAME'] = 'demo';
$database['DB_USER'] = 'demo';
$database['DB_PWD'] = 'demo';
$database['PORT'] = 54321;
try {
    $conn = new PDO("kdb:host={$database['DB_HOST']};dbname={$database['DB_NAME']};port={$database['PORT']}", $database['DB_USER'], $database['DB_PWD']);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("连接失败: " . $e->getMessage());
}

$result = $conn->query("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'");
$tables = $result->fetchAll(PDO::FETCH_ASSOC);

$html = '';

$html .= '<table border="1" cellspacing="0" cellpadding="0" align="center">';
$html .= '<tbody><tr><th>序号</th><th>表名</th><th>功能说明</th></tr>';

foreach ($tables as $k => $v) {
    $sort = $k + 1;
    $tableName = $v['table_name'];

    // Get table comment
    $tableComment = $conn->query("SELECT obj_description('public.{$tableName}'::regclass, 'pg_class') AS table_comment")->fetchColumn();

    $html .= '<tr>';
    $html .= '<td class="c1">' . $sort . '</td>';
    $html .= '<td class="c2">' . $tableName . '</td>';
    $html .= '<td class="c3">' . $tableComment . '</td>';
    $html .= '</tr>';
}
$html .= '</tbody></table></p>';

$html .= "<br /><br /><br />";

foreach ($tables as $v) {
    $tableName = $v['table_name'];

    $fields = $conn->query("
        SELECT 
            column_name,
            data_type,
            column_default,
            is_nullable,
            character_maximum_length, -- Add this line if you want to get the character maximum length
            numeric_precision, -- Add this line if you want to get the numeric precision
            numeric_scale, -- Add this line if you want to get the numeric scale
            (SELECT description FROM pg_description 
            WHERE objoid = (SELECT oid FROM pg_class 
                            WHERE relname = '{$tableName}' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'))
            AND objsubid = (SELECT ordinal_position FROM information_schema.columns 
                            WHERE table_name = '{$tableName}' AND table_schema = 'public' AND column_name = c.column_name)
            ) AS column_comment
        FROM 
            information_schema.columns c
        WHERE 
            table_name = '{$tableName}' 
            AND table_schema = 'public'
    ")->fetchAll(PDO::FETCH_ASSOC);

    $html .= '<table border="1" cellspacing="0" cellpadding="0" align="center">';
    $html .= '<caption>表名：' . $tableName . ' ' . '</caption>';
    $html .= '<tbody><tr><th>字段名</th><th>数据类型</th><th>默认值</th><th>允许非空</th><th>备注</th></tr>';

    foreach ($fields as $f) {
        $html .= '<tr>';
        $html .= '<td class="c1">' . $f['column_name'] . '</td>';
        $html .= '<td class="c2">' . $f['data_type'] . '</td>';
        $html .= '<td class="c3">' . $f['column_default'] . '</td>';
        $html .= '<td class="c4">' . ($f['is_nullable'] == 'YES' ? '是' : '否') . '</td>';
        $html .= '<td class="c5">' . $f['column_comment'] . '</td>';
        $html .= '</tr>';
    }

    $html .= '</tbody></table></p>';
}

echo '<html>
  <meta charset="utf-8">
  <title>自动生成数据字典</title>
  <style>
    body,td,th {font-family:"宋体"; font-size:12px;}
    table,h1,p{width:960px;margin:0px auto;}
    table{border-collapse:collapse;border:1px solid #CCC;background:#efefef;}
    table caption{text-align:left; background-color:#fff; line-height:2em; font-size:14px; font-weight:bold; }
    table th{text-align:left; font-weight:bold;height:26px; line-height:26px; font-size:12px; border:1px solid #CCC;padding-left:5px;}
    table td{height:20px; font-size:12px; border:1px solid #CCC;background-color:#fff;padding-left:5px;}
    .c1{ width: 150px;}
    .c2{ width: 150px;}
    .c3{ width: 80px;}
    .c4{ width: 100px;}
    .c5{ width: 300px;}
  </style>
  <body>';
echo '<h1 style="text-align:center;">' . $database['DB_NAME'] . '数据字典</h1>';
echo '<p style="text-align:center;margin:20px auto;">生成时间：' . date('Y-m-d H:i:s', time()) . '</p>';
echo $html;
echo '<p style="text-align:left;margin:20px auto;">总共：' . count($tables) . '个数据表</p>';
echo '</body></html>';
?>