您现在的位置：程式碼之家 >> 數據庫知識 > >> DB2 資料庫 >> DB2 教學

基於 DB2 pureXML 與 PHP 構建智能知識庫系統 (上)：架構設計與核心類實現

編輯：DB2 教學

本教程面向 Web 應用開發者，逐步指導如何利用 IBM DB2 pureXML 特性與 PHP 構建 Support Knowledge Base 系統。系統融合關係表結構與 XML 原生存儲，通過 SQL/XML 將 XML 數據映射為關係視圖，實現靈活高效的數據訪問。

系統架構與目錄結構

運行環境：Windows XP + DB2 Express-C 9.5 + Apache HTTP Server 2.2 + PHP 5.2

目錄佈局 (Apache htdocs/kbase/)：

classes/：PHP 核心類 (數據庫連接、業務實體)
css/：樣式表
includes/：頁面公共片段 (Header、導航、Footer)
sql/：數據庫建表腳本

頁面分層：

管理視圖：類別/文章/評論 CRUD
客戶端視圖：首頁、搜索、類別瀏覽、文章詳情

數據庫設計：混合關係/XML 模型

四張核心表，均含 id (主鍵) + data XML (業務載體) + 元數據列：

CREATE DATABASE kbase USING CODESET UTF-8 TERRITORY us;
CONNECT TO kbase;

CREATE TABLE category (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  data XML NOT NULL, PRIMARY KEY(id)
);

CREATE TABLE article (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  date_created TIMESTAMP NOT NULL,
  date_modified TIMESTAMP NOT NULL,
  view_count INT,
  category_id INT NOT NULL,
  data XML NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(category_id) REFERENCES category(id) ON DELETE CASCADE
);

CREATE TABLE comment (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  date_left TIMESTAMP NOT NULL,
  approved SMALLINT NOT NULL,
  article_id INT NOT NULL,
  data XML NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE
);

CREATE TABLE rating (
  id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  date_rated TIMESTAMP NOT NULL,
  article_id INT NOT NULL,
  data XML NOT NULL,
  PRIMARY KEY(id),
  FOREIGN KEY(article_id) REFERENCES article(id) ON DELETE CASCADE
);

圖 1. DB2 Command Editor

圖 2. 執行 database.sql 的結果

XML 文檔結構約定

<!-- category.data -->
<category><name>Category Name</name></category>

<!-- article.data -->
<article><title>...</title><content>...</content></article>

<!-- comment.data -->
<comment><name>...</name><ip_address>...</ip_address><content>...</content></comment>

外層根節點為 DB2 pureXML 要求，便於未來擴展字段。

核心 PHP 類實現

1. DB 類：數據庫連接封裝

<?php
class DB {
  private $conn;
  function __construct() {
    $db_connect_string = "DRIVER={IBM DB2 ODBC DRIVER};DATABASE=kbase;HOSTNAME=localhost;PORT=50000;PROTOCOL=TCPIP;UID=username;PWD=password;";
    $this->conn = db2_connect($db_connect_string, '', '');
    if(!$this->conn) die(db2_conn_errormsg($this->conn));
  }
  function safe_no_Html($string, $include_quotes=true) {
    htmlentities($string, ENT_QUOTES, 'utf-8');
    return $include_quotes ? "'".db2_escape_string($string)."'" : db2_escape_string($string);
  }
  function query($sql) {
    $result = db2_exec($this->conn, $sql);
    if(!$result) die(db2_stmt_errormsg());
    return $result;
  }
  function get_row($result) { return db2_fetch_array($result); }
}
?>

構造函數集中管理連接參數，便於維護
safe_no_Html：防 SQL 注入 + 防 XSS 雙重防護
query/get_row：簡化執行與獲取流程
生產環境建議改用預備語句，錯誤信息不直接輸出用戶端

2. Category 類：類別實體與 XML 映射

class Category {
  private $db, $id, $name;
  function __construct() { $this->db = new DB(); }
  // getter/setter 略...
  function save() {
    $sql = $this->id ? "UPDATE category SET data = XMLPARSE(DOCUMENT '<category><name>".$this->db->safe_no_Html($this->name,false)."</name></category>') WHERE id = $this->id"
                    : "INSERT INTO category(data) VALUES(XMLPARSE(DOCUMENT '<category><name>".$this->db->safe_no_Html($this->name,false)."</name></category>'))";
    return $this->db->query($sql) ? true : false;
  }
  function delete() { /* DELETE by id */ }
  function getAllCategories() {
    $sql = 'SELECT c.id, x.name FROM category c,
      XMLTABLE(\'$d/category\' PASSING c.data AS "d" COLUMNS name VARCHAR(200) PATH \'name\') AS x
      ORDER BY x.name';
    $result = $this->db->query($sql);
    while($row = $this->db->get_row($result)) $rows[] = array($row[0], $row[1]);
    return $rows;
  }
}

3. SQL/XML 核心函數解析

XMLPARSE：將字符串解析為 XML 類型存入 data 列
XMLTABLE：將 XML 節點映射為關係列
```
XMLTABLE('$d/category' PASSING c.data AS "d" COLUMNS name VARCHAR(200) PATH 'name') AS x
```
使 x.name 可像普通列一樣參與 SELECT/WHERE/ORDER BY

4. Article 類：豐富業務邏輯與高級 XML 構建

class Article {
  // 屬性: id, title, content, category_id, view_count, date_created, date_modified, db
  function save() {
    $xml = 'XMLDOCUMENT(XMLELEMENT(NAME "article", XMLCONCAT(
      XMLELEMENT(NAME "title", '.$this->db->safe_no_Html($this->title).'),
      XMLELEMENT(NAME "content", '.$this->db->safe_no_Html($this->content).')
    )))';
    $sql = $this->id
      ? "UPDATE article SET data = $xml, category_id = $this->category_id, date_modified = CURRENT TIMESTAMP WHERE id = $this->id"
      : "INSERT INTO article(date_created, date_modified, view_count, category_id, data) VALUES (CURRENT TIMESTAMP, CURRENT TIMESTAMP, 0, $this->category_id, $xml)";
    return $this->db->query($sql) ? true : false;
  }
  function getArticlesBySearchTerm($search_term) {
    $search_term = strtolower(trim($search_term));
    $sql = 'SELECT a.id, x.title, a.date_created, a.view_count FROM article a,
      XMLTABLE(\'$d/article\' PASSING a.data AS "d" COLUMNS title VARCHAR(200) PATH \'title\', content VARCHAR(4000) PATH \'content\') AS x
      WHERE LOWER(x.title) LIKE \'%'.$this->db->safe_no_Html($search_term,false).'%\'
         OR LOWER(x.content) LIKE \'%'.$this->db->safe_no_Html($search_term,false).'%\'
      ORDER BY x.title';
    // 返回結果集...
  }
  function addComment($name, $comments, $ip_address) {
    $sql = "INSERT INTO comment(date_left, article_id, approved, data) VALUES (CURRENT TIMESTAMP, {$this->id}, 0,
      XMLDOCUMENT(XMLELEMENT(NAME \"comment\", XMLCONCAT(
        XMLELEMENT(NAME \"name\", '".$this->db->safe_no_Html($name,false)."'),
        XMLELEMENT(NAME \"ip_address\", '$ip_address'),
        XMLELEMENT(NAME \"content\", '".$this->db->safe_no_Html($comments,false)."')
      ))))";
    return $this->db->query($sql) ? true : false;
  }
}