数据库生成器(1)--实现方案

决定采用xml作为数据库设计的描述语言，xsl+html作为数据库设计的表现方式，使用vb程序解析xml，转换为相应的sql语句，通过执行生成的sql保持数据库架构和数据库设计一致。

使用xml+xsl就能快速浏览数据库的全貌和细节，并且查找关键字和定位也很容易。将xml转换为sql避免了人为干预数据库，使设计和实现保持一致成为可能。程序在将xml抓换成sql语句过程中可以进行强制检查，有利于防止数据库随意设计。

为了实现数据库对象命名强制检查，对数据库对象命名进行了规定，要求如下：

数据库对象类型 前缀
TABLE t_
VIEW v_
FUNCTION f_
PROCEDURE p_
PARAMETER a_
PRIMARY KEY pk_
INDEX ix_
TRIGGER tri_
DEFAULT def_

同时对字段和参数的命名也进行了规范，要求字段名能反映字段的用途和数据类型，要求如下：

数据类型 后缀 数据用途
INT _id 序号
TINYINT _flag 标志位(最常见0/1)
NUMERIC(18, 0) _unit 数量(各类Count值)
NUMERIC(18, 2) _numb 数量(各类低精度数，比如金额)
NUMERIC(18, 4) _rate 数量(各类高精度数，比如汇率)
VARCHAR(20) _no 编号(No.,不含中文)
VARCHAR(20) _code 编码(不含中文)
VARCHAR(20) _type 类别(不含中文)
NVARCHAR(200) _sign 标记(比如银行账号)
NVARCHAR(200) _name 名字
NVARCHAR(500) _text 说明(不超过500个字的备注，比如地址)
VARCHAR(2000) _list _no/_code/_type字段的列表(列表元素不超过100个)
NVARCHAR(2000) _file 文件名(含路径)
NVARCHAR(4000) _note 描述(不超过4000个字的备注，比如规格说明)
NVARCHAR(MAX) _memo 备注(超过4000个字的备注)
VARCHAR(10) _date 日期(格式为yyy-mm-dd)
VARCHAR(10) _time 时间(格式为hh:mm:ss)
UNIQUEIDENTIFIER _guid GUID值
VARBINARY(MAX) _bin 二进制序列(比如image)
XML _xml XML文档

最终的xml文档内容如下：

  1 <?xml version="1.0" encoding="utf-8"?>
  2 <database name="TEST_DB" version="1.0" description="这是一个测试数据库，文档中的所有节点都有description属性，用于数据库设计者对该处设计的说明">
  3     <tables description="所有的表设计放这里">
  4         <common description="关于表的通用设计，包括默认值，每个表都强制要有的字段放这里">
  5             <defaults description="数据库中所有的默认值都放这里，避免随意给字段设默认值。">
  6                 <default name="def_Null" value="NULL" description="NULL值，保留。name是默认值名，value是默认值，description说明该默认值用于哪些类型的字段"/>
  7                 <default name="def_Empty" value="''" description="用于_no, _code, _date, _time, _list类型字段"/>
  8                 <default name="def_NEmpty" value="N''" description="用于_sign, _name, _text, _file, _note, _memo类型字段"/>
  9                 <default name="def_Unknown" value="''" description="用于_type类型字段"/>
 10                 <default name="def_Void" value="N''" description="用于_xml类型字段"/>
 11                 <default name="def_False" value="0" description="用于_flag类型字段"/>
 12                 <default name="def_True" value="1" description="用于_flag类型字段"/>
 13                 <default name="def_Zero" value="0" description="用于_id, _unit, _numb, _rate类型字段"/>
 14                 <default name="def_One" value="1" description="保留"/>
 15                 <default name="def_Nil" value="0x0" description="用于_bin, _guid类型字段"/>
 16                 <default name="def_Today" value="dbo.f_GetToday()" description="用于_date类型字段"/>
 17                 <default name="def_Now" value="dbo.f_GetNow()" description="用于_time类型字段"/>
 18                 <default name="def_CurOperatorCode" value="dbo.f_GetCurOperatorCode()" description="用于Modifier_code字段"/>
 19                 <default name="def_CurOperatorName" value="dbo.f_GetCurOperatorName()" description="用于Modifier_name字段"/>
 20             </defaults>
 21             <fields description="数据库中任何一个表都要求包含以下字段">
 22                 <field name="Row_id" caption="Row Id" identity="true" description="标识列，自增。name是字段名，caption是字段标签，有identity属性，并且为true说明字段是标识列"/>
 23                 <field name="Insert_date" caption="Create" default="def_Today" description="记录创建日期，有default属性说明该字段不使用默认的默认值，而是使用指定的默认值"/>
 24                 <field name="Insert_time" caption="Create" default="def_Now" description="记录创建时间"/>
 25                 <field name="Modify_date" caption="Last Change" default="def_Today" description="记录最后修改日期"/>
 26                 <field name="Modify_time" caption="Last Change" default="def_Now" description="记录最后修改时间"/>
 27                 <field name="Modifier_code" caption="Last Changer" default="def_CurOperatorCode" description="记录最后的修改者code"/>
 28                 <field name="Modifier_name" caption="Last Changer" default="def_CurOperatorName" description="记录最后的修改者name"/>
 29             </fields>
 30         </common>
 31         <table name="t_EventLog" pkeys="0" description="事件日志表。pkeys标明该表的主键数。0表示主键是标识列，即Row_id字段；没有该属性或该属性值为1表示除通用字段外第一个字段为主键字段。大于1表示除通用字段外前n个字段为主键字段">
 32             <fields>
 33                 <field name="Event_type" caption="Source" description="事件类型"/>
 34                 <field name="Event_file" caption="Place" description="事件发生的文件或表名"/>
 35                 <field name="Event_memo" caption="Content" description="日志内容"/>
 36             </fields>
 37             <indexes description="该表的附加索引。所有附加索引都是非聚集索引。">
 38                 <index name="IX_EventLog_Event_file" for="Event_file" description="name为索引名，for为要索引的字段，如果包含多个字段用','分隔。"/>
 39             </indexes>
 40         </table>
 41         <table name="t_TestLog" pkeys="2" description="测试日志表。该表主键由Test_file和Test_id 2个字段组成">
 42             <fields>
 43                 <field name="Test_file" caption="File" description="被测试文件名"/>
 44                 <field name="Test_id" caption="Id" description="该文件被测试序号"/>
 45                 <field name="Test_sign" caption="Row Key" value="Test_file + ':' + CONVERT(VARCHAR(20), Test_id)" description="包含value属性的字段为计算列。value属性值即计算公式"/>
 46                 <field name="Content_memo" caption="Content" description="测试内容"/>
 47                 <field name="Result_flag" caption="Result" description="测试结果。0为失败，1为成功"/>
 48             </fields>
 49             <triggers description="该表的附加触发器">
 50                 <trigger name="tri_TestLog_DeleteLog" for="D" description="name为触发器名，for属性指定触发事件I为Insert，U为Update，D为Delete">
 51                     <content><![CDATA[
 52 SET NOCOUNT ON;
 53 
 54 INSERT INTO dbo.t_EventLog(Event_type, Event_file, Event_memo)
 55 SELECT 'DELETE', 'dbo.t_TestLog', Test_sign
 56 FROM [DELETED];
 57 ]]></content>
 58                 </trigger>
 59                 <trigger name="tri_TestLog_UpdateLog" for="U">
 60                     <content><![CDATA[
 61 SET NOCOUNT ON;
 62 
 63 UPDATE t
 64 SET Modify_date = dbo.f_GetToday()
 65 , Modify_time = dbo.f_GetNow()
 66 , Modifier_code = dbo.f_GetCurOperatorCode()
 67 , Modifier_name = dbo.f_GetCurOperatorName()
 68 FROM dbo.t_TestLog AS t
 69 INNER JOIN [INSERTED] AS i 
 70 ON t.Row_id = i.Row_id;
 71 
 72 INSERT INTO dbo.t_EventLog(Event_type, Event_file, Event_memo)
 73 SELECT 'UPDATE', 'dbo.t_TestLog', Test_sign
 74 FROM [INSERTED];
 75 ]]></content>
 76                 </trigger>
 77             </triggers>
 78         </table>
 79         <table name="t_Variable" description="用于存放系统配置参数的表。因为没有pkeys属性，所以主键为Variable_name字段">
 80             <fields>
 81                 <field name="Variable_name" caption="Name" description="参数名"/>
 82                 <field name="Variable_text" caption="Value" description="参数值"/>
 83             </fields>
 84             <indexes>
 85                 <index name="IX_Variable_Variable_name" for="Variable_name" unique="true" include="Variable_text" description="unique属性为true表示创建唯一索引，该属性不存在或不为true则创建普通索引。include属性指定索引包含的其他字段，如果包含多个字段用','分隔。"/>
 86             </indexes>
 87         </table>
 88     </tables>
 89     <viewes description="所有的视图设计放这里">
 90         <view name="v_TestEventLog" description="包含TestLog的日志">
 91             <content><![CDATA[
 92 SELECT *
 93 FROM dbo.t_EventLog
 94 WHERE Event_file = N'dbo.t_TestLog'
 95 ]]></content>
 96         </view>
 97     </viewes>
 98     <functions description="所有的函数设计放这里">
 99         <function name="f_GetToday" return="CHAR(10)" description="name为函数名，return属性为函数返回的数据类型">
100             <content><![CDATA[
101 RETURN CONVERT(CHAR(10), GETDATE(), 120)
102 ]]></content>
103         </function>
104         <function name="f_GetNow" return="CHAR(8)" description="get current time, the format is hh:mm:ss">
105             <content><![CDATA[
106 RETURN CONVERT(CHAR(8), GETDATE(), 108)
107 ]]></content>
108         </function>
109         <function name="f_GetCurOperatorCode" return="VARCHAR(20)" description="get current operator code">
110             <content><![CDATA[
111 RETURN HOST_NAME() --The connection string property is "Workstation ID"
112 ]]></content>
113         </function>
114         <function name="f_GetCurOperatorName" return="NVARCHAR(200)" description="get current operator name">
115             <content><![CDATA[
116 RETURN N''
117 ]]></content>
118         </function>
119         <function name="f_GetLastTestLogId" return="INT" description="返回指定Test_file的最大的Test_id">
120             <parameters>
121                 <parameter name="a_Test_file" description="被求Test_id的Test_file"/>
122             </parameters>
123             <content><![CDATA[
124 DECLARE @Last_id INT
125 
126 SELECT @Last_id = MAX(Test_id)
127 FROM dbo.t_TestLog
128 WHERE Test_file = @a_Test_file
129 
130 RETURN @Last_id
131 ]]></content>
132         </function>
133     </functions>
134     <procedures description="所有的存储过程设计放这里">
135         <procedure name="p_AppendTestLog" description="新增TestLog记录，成功返回Test_id">
136             <parameters>
137                 <parameter name="a_Test_file" description="被测试文件名"/>
138                 <parameter name="a_Content_memo" description="测试内容"/>
139                 <parameter name="a_Result_flag" description="测试结果"/>
140                 <parameter name="a_Test_id" output="true" description="返回的Test_id。output参数为true表示该参数是一个输出参数"/>
141             </parameters>
142             <content><![CDATA[
143 DECLARE @Last_id INT
144 
145 SET @Last_id = dbo.f_GetLastTestLogId(@a_Test_file)
146 SET @Last_id = ISNULL(@Last_id, 0) + 1
147 
148 INSERT INTO dbo.t_TestLog(Test_file, Test_id, Content_memo, Result_flag)
149 VALUES(@a_Test_file, @Last_id, @a_Content_memo, @a_Result_flag)
150 
151 SET @a_Test_id = @Last_id
152 ]]></content>
153         </procedure>
154     </procedures>
155 </database>

 负责解析的xsl文件内容如下：

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:html="http://www.w3.org/1999/xhtml">
<xsl:output method="html"/>
<xsl:param name="object"/>
<xsl:param name="name"/>

<xsl:template match="/">
 <xsl:variable name="back_object">
  <xsl:choose>
   <xsl:when test="$object = 'database' or $object = 'tables' or $object = 'viewes' or $object = 'functions' or $object = 'procedures'">
    <xsl:value-of select="'database'"/>
   </xsl:when>
   <xsl:when test="$object = 'common-defaults' or $object = 'common-fields' or $object = 'table'">
    <xsl:value-of select="'tables'"/>
   </xsl:when>
   <xsl:when test="$object = 'index'">
    <xsl:value-of select="'table'"/>
   </xsl:when>
   <xsl:when test="$object = 'trigger'">
    <xsl:value-of select="'table'"/>
   </xsl:when>
   <xsl:when test="$object = 'view'">
    <xsl:value-of select="'viewes'"/>
   </xsl:when>
   <xsl:when test="$object = 'function'">
    <xsl:value-of select="'functions'"/>
   </xsl:when>
   <xsl:when test="$object = 'procedure'">
    <xsl:value-of select="'procedures'"/>
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="'database'"/>
   </xsl:otherwise>
  </xsl:choose>
 </xsl:variable>
 <xsl:variable name="back_name">
  <xsl:choose>
   <xsl:when test="$object = 'index'">
    <xsl:value-of select="/database/tables/table/indexes/index[@name = $name]/http://www.cnblogs.com/@name"/>
   </xsl:when>
   <xsl:when test="$object = 'trigger'">
    <xsl:value-of select="/database/tables/table/triggers/trigger[@name = $name]/http://www.cnblogs.com/@name"/>
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="''"/>
   </xsl:otherwise>
  </xsl:choose>
 </xsl:variable>
 <table class="head">
  <tr>
   <td class="index">
    <div class="index">
     <a href="?object=database">INDEX</a>
     <a href="?object={$back_object}&amp;name={$back_name}">BACK</a>
    </div>
   </td>
  </tr>
  <tr>
   <td>
    <span style="text-transform:capitalize;">
     <xsl:choose>
      <xsl:when test="$object = ''">
       database
        : <xsl:value-of select="/database/@name"/>
      </xsl:when>
      <xsl:otherwise>
       <xsl:value-of select="$object"/>
       <xsl:if test="$object = 'database'">
        : <xsl:value-of select="/database/@name"/>
       </xsl:if>
      </xsl:otherwise>
     </xsl:choose>
    </span>
    <xsl:if test="$name != ''">
     : <xsl:value-of select="$name"/>
    </xsl:if>
   </td>
  </tr>
 </table>
 <table class="body">
  <tr>
   <td><br/></td>
  </tr>
  <tr>
   <td>
    <xsl:choose>
     <xsl:when test="$object = 'database' or $object = ''">
      <xsl:apply-templates select="database"/>
     </xsl:when>
     <xsl:when test="$object = 'tables'">
      <xsl:apply-templates select="/database/tables"/>
     </xsl:when>
     <xsl:when test="$object = 'viewes'">
      <xsl:apply-templates select="/database/viewes"/>
     </xsl:when>
     <xsl:when test="$object = 'functions'">
      <xsl:apply-templates select="/database/functions"/>
     </xsl:when>
     <xsl:when test="$object = 'procedures'">
      <xsl:apply-templates select="/database/procedures"/>
     </xsl:when>
     <xsl:when test="$object = 'common-defaults'">
      <xsl:apply-templates select="/database/tables/common/defaults"/>
     </xsl:when>
     <xsl:when test="$object = 'common-fields'">
      <xsl:apply-templates select="/database/tables/common/fields"/>
     </xsl:when>
     <xsl:when test="$object = 'table'">
      <xsl:apply-templates select="/database/tables/table[@name = $name]" mode="table"/>
     </xsl:when>
     <xsl:when test="$object = 'index'">
      <xsl:apply-templates select="/database/tables/table/indexes/index[@name = $name]">
       <xsl:with-param name="index_tablename" select="''"/>
      </xsl:apply-templates>
     </xsl:when>
     <xsl:when test="$object = 'trigger'">
      <xsl:apply-templates select="/database/tables/table/triggers/trigger[@name = $name]">
       <xsl:with-param name="trigger_tablename" select="''"/>
      </xsl:apply-templates>
     </xsl:when>
     <xsl:when test="$object = 'view'">
      <xsl:apply-templates select="/database/viewes/view[@name = $name]"/>
     </xsl:when>
     <xsl:when test="$object = 'function'">
      <xsl:apply-templates select="/database/functions/function[@name = $name]"/>
     </xsl:when>
     <xsl:when test="$object = 'procedure'">
      <xsl:apply-templates select="/database/procedures/procedure[@name = $name]"/>
     </xsl:when>
     <xsl:otherwise>
      Unknown Object
     </xsl:otherwise>
    </xsl:choose>
   </td>
  </tr>
  <tr>
   <td><br/></td>
  </tr>
 </table>
 <hr style="border:none; height:1px; background-color:#999999;"/>
 <div class="index">
  <a href="?object=database">INDEX</a>
  <a href="?object={$back_object}&amp;name={$back_name}">BACK</a>
 </div>
</xsl:template>

<xsl:template match="database">
 <div class="caption">version</div>
 <div class="message"><xsl:value-of select="./@version"/></div>
 <br/>
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <div class="caption">objects</div>
 <table class="list">
  <tr class="head">
   <td>object</td>
   <td>count</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="*">
   <xsl:variable name="database_child">
    <xsl:value-of select="local-name()"/>
   </xsl:variable>
   <tr>
    <td nowrap="nowrap"><a href="?object={$database_child}"><xsl:value-of select="$database_child"/></a></td>
    <td nowrap="nowrap">
     <xsl:choose>
      <xsl:when test="local-name()='tables'">
       <xsl:value-of select="count(./*)-count(./common)"/>
      </xsl:when>
      <xsl:otherwise>
       <xsl:value-of select="count(./*)"/>
      </xsl:otherwise>
     </xsl:choose>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="tables">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <xsl:if test="count(./common) != 0">
  <div class="caption">common</div>
  <xsl:apply-templates select="./common"/>
  <br/>
 </xsl:if>
 <div class="caption">table</div>
 <table class="list">
  <tr class="head">
   <td>name</td>
   <td>PK</td>
   <td>FK</td>
   <td>fields</td>
   <td>indexes</td>
   <td>triggers</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="./table">
   <tr>
    <td nowrap="nowrap">
     <a href="?object=table&amp;name={./@name}"><xsl:value-of select="./@name"/></a>
    </td>
    <td nowrap="nowrap">
     <xsl:choose>
      <xsl:when test="./@pkeys = '0'">Row_id</xsl:when>
      <xsl:when test="count(./@pkeys) = 0 or ./@pkeys = '1'">
       <xsl:value-of select="./fields/field[position()=1]/@name"/>
      </xsl:when>
      <xsl:otherwise>
       <xsl:variable name="table_pkeys" select="number(./@pkeys)"/>
       <xsl:for-each select="./fields/field[position() &lt;= $table_pkeys]">
        <xsl:if test="position() > 1"><br/></xsl:if>
        <xsl:value-of select="./@name"/>
       </xsl:for-each>
      </xsl:otherwise>
     </xsl:choose>
    </td>
    <td nowrap="nowrap"><!--none FK--></td>
    <td nowrap="nowrap"><xsl:value-of select="count(./fields/field) + count(../common/fields/field)"/></td>
    <td nowrap="nowrap">
     <xsl:apply-templates select="." mode="index">
      <xsl:with-param name="table_name" select="./@name"/>
     </xsl:apply-templates>
    </td>
    <td nowrap="nowrap">
     <xsl:apply-templates select="." mode="trigger">
      <xsl:with-param name="table_name" select="./@name"/>
     </xsl:apply-templates>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="viewes">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <table class="list">
  <tr class="head">
   <td>name</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="./view">
   <tr>
    <td nowrap="nowrap">
     <a href="?object=view&amp;name={./@name}"><xsl:value-of select="./@name"/></a>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="functions|procedures">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <table class="list">
  <tr class="head">
   <td>name</td>
   <td>parameters</td>
   <td>return</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="./function|./procedure">
   <tr>
    <td nowrap="nowrap">
     <a href="?object={local-name(.)}&amp;name={./@name}"><xsl:value-of select="./@name"/></a>
    </td>
    <td nowrap="nowrap">
     <xsl:for-each select="./parameters/parameter">
      <xsl:if test="position() > 1"><br/></xsl:if>
      <xsl:value-of select="concat(./@name, ' ')"/>
      <xsl:if test="./@output = 'true'">OUTPUT</xsl:if>
     </xsl:for-each>
    </td>
    <td><xsl:value-of select="./@return"/></td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="common">
 <table class="list">
 <tr class="head">
  <td>object</td>
  <td>count</td>
  <td>description</td>
 </tr>
 <xsl:for-each select="*">
  <xsl:variable name="common_child">
   <xsl:value-of select="local-name()"/>
  </xsl:variable>
  <tr>
   <td nowrap="nowrap"><a href="?object=common-{$common_child}"><xsl:value-of select="$common_child"/></a></td>
   <td nowrap="nowrap"><xsl:value-of select="count(./*)"/></td>
   <td><xsl:value-of select="./@description"/></td>
  </tr>
 </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="defaults">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <table class="list">
  <tr class="head">
   <td>name</td>
   <td>expression</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="./default">
   <tr>
    <td nowrap="nowrap"><xsl:value-of select="./@name"/></td>
    <td nowrap="nowrap">
     <xsl:if test="starts-with(./@value, 'dbo.f_')">
      <xsl:variable name="defaults_functionname">
       <xsl:value-of select="substring-after(substring-before(./@value, '('), 'dbo.')"/>
      </xsl:variable>
      <a href="?object=function&amp;name={$defaults_functionname}"><xsl:value-of select="./@value"/></a>
     </xsl:if>
     <xsl:if test="not(starts-with(./@value, 'dbo.f_'))">
      <xsl:value-of select="./@value"/>
     </xsl:if>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="fields">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <table class="list">
  <tr class="head">
   <td>Name</td>
   <td>Caption</td>
   <td>Datatype</td>
   <td>Default</td>
   <td>Nullable</td>
   <td>Description</td>
  </tr>
  <xsl:for-each select="./field">
   <tr>
    <td nowrap="nowrap"><xsl:value-of select="./@name"/></td>
    <td nowrap="nowrap"><xsl:value-of select="./@caption"/></td>
    <td nowrap="nowrap">
     <xsl:apply-templates select="." mode="datatype">
      <xsl:with-param name="field_name" select="./@name"/>
      <xsl:with-param name="field_datatype" select="./@datatype"/>
     </xsl:apply-templates>
    </td>
    <td nowrap="nowrap">
     <xsl:if test="count(./@value) > 0">(<xsl:value-of select="./@value"/>)</xsl:if>
     <xsl:if test="count(./@value) = 0">
      <xsl:apply-templates select="." mode="default">
       <xsl:with-param name="field_name" select="./@name"/>
       <xsl:with-param name="field_identity" select="./@identity"/>
       <xsl:with-param name="field_default" select="./@default"/>
      </xsl:apply-templates>
     </xsl:if>
    </td>
    <td nowrap="nowrap">
     <xsl:choose>
      <xsl:when test="count(./@value) > 0">&#8730;</xsl:when>
      <xsl:when test="./@nullable = 'true'">&#8730;</xsl:when>
      <xsl:otherwise></xsl:otherwise>
     </xsl:choose>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="table" mode="table">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <xsl:variable name="table_name" select="./@name"/>
 <xsl:variable name="table_pkeys" select="./@pkeys"/>
 <xsl:variable name="table_pkeysnumber" select="number($table_pkeys)"/>
 <div class="caption">fields</div>
 <xsl:if test="./fields/@description != ''">
  <div class="message"><xsl:value-of select="./fields/@description"/></div>
 </xsl:if>
 <table class="list">
  <tr class="head">
   <td>PK</td>
   <td>FK</td>
   <td>Name</td>
   <td>Caption</td>
   <td>Datatype</td>
   <td>Default</td>
   <td>Nullable</td>
   <td>Description</td>
  </tr>
  <xsl:for-each select="../common/fields/field|./fields/field">
   <xsl:variable name="table_pkflag">
    <xsl:choose>
     <xsl:when test="$table_pkeys = '0'">
      <xsl:if test="./@name = 'Row_id'">
       <xsl:value-of select="'Yes'"/>
      </xsl:if>
     </xsl:when>
     <xsl:when test="count($table_pkeys) = 0 or $table_pkeys = '1'">
      <xsl:if test="http://www.cnblogs.com/@name = $table_name">
       <xsl:if test="position() = 1 + count(http://www.cnblogs.com/../common/fields/field)">
        <xsl:value-of select="'Yes'"/>
       </xsl:if>
      </xsl:if>
     </xsl:when>
     <xsl:otherwise>
      <xsl:if test="http://www.cnblogs.com/@name = $table_name">
       <xsl:if test="position() &lt;= $table_pkeysnumber + count(http://www.cnblogs.com/../common/fields/field)">
        <xsl:value-of select="'Yes'"/>
       </xsl:if>
      </xsl:if>
     </xsl:otherwise>
    </xsl:choose>
   </xsl:variable>
   <tr>
    <td nowrap="nowrap">
     <xsl:if test="$table_pkflag = 'Yes'">&#8730;</xsl:if>
    </td>
    <td nowrap="nowrap"><!--none FK--></td>
    <td nowrap="nowrap"><xsl:value-of select="./@name"/></td>
    <td nowrap="nowrap"><xsl:value-of select="./@caption"/></td>
    <td nowrap="nowrap">
     <xsl:apply-templates select="." mode="datatype">
      <xsl:with-param name="field_name" select="./@name"/>
      <xsl:with-param name="field_datatype" select="./@datatype"/>
     </xsl:apply-templates>
    </td>
    <td nowrap="nowrap">
     <xsl:if test="count(./@value) > 0">(<xsl:value-of select="./@value"/>)</xsl:if>
     <xsl:if test="count(./@value) = 0">
      <xsl:apply-templates select="." mode="default">
       <xsl:with-param name="field_name" select="./@name"/>
       <xsl:with-param name="field_identity" select="./@identity"/>
       <xsl:with-param name="field_default" select="./@default"/>
      </xsl:apply-templates>
     </xsl:if>
    </td>
    <td nowrap="nowrap">
     <xsl:choose>
      <xsl:when test="count(./@value) > 0">&#8730;</xsl:when>
      <xsl:when test="./@nullable = 'true'">&#8730;</xsl:when>
      <xsl:otherwise></xsl:otherwise>
     </xsl:choose>
    </td>
    <td><xsl:value-of select="./@description"/></td>
   </tr>
  </xsl:for-each>
 </table>
 <br/>
 <div class="caption">Indexes</div>
 <xsl:if test="./indexes/@description != ''">
  <div class="message"><xsl:value-of select="./indexes/@description"/></div>
 </xsl:if>
 <xsl:choose>
  <xsl:when test="count(./indexes/index) = 0">
   <div class="message">(None index.)</div>
  </xsl:when>
  <xsl:otherwise>
   <xsl:apply-templates select="./indexes"/>
  </xsl:otherwise>
 </xsl:choose>
 <br/>
 <div class="caption">Triggers</div>
 <xsl:if test="./triggers/@description != ''">
  <div class="message"><xsl:value-of select="./triggers/@description"/></div>
 </xsl:if>
 <xsl:choose>
  <xsl:when test="count(./triggers/trigger) = 0">
   <div class="message">(None trigger.)</div>
  </xsl:when>
  <xsl:otherwise>
   <xsl:apply-templates select="./triggers"/>
  </xsl:otherwise>
 </xsl:choose>
</xsl:template>

<xsl:template match="indexes">
 <table class="list">
 <tr class="head">
  <td>name</td>
  <td>for</td>
  <td>unique</td>
  <td>include</td>
  <td>description</td>
 </tr>
 <xsl:for-each select="./index">
 <tr>
  <td nowrap="nowrap"><a href="?object=index&amp;name={./@name}"><xsl:value-of select="./@name"/></a></td>
  <td nowrap="nowrap"><xsl:value-of select="./@for"/></td>
  <td nowrap="nowrap"><xsl:if test="./@unique = 'true'">&#8730;</xsl:if></td>
  <td nowrap="nowrap"><xsl:value-of select="./@include"/></td>
  <td><xsl:value-of select="./@description"/></td>
 </tr>
 </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="triggers">
 <xsl:param name="triggers_tablename"/>

 <table class="list">
  <tr class="head">
   <td>name</td>
   <td>for</td>
   <td>order</td>
   <td>description</td>
  </tr>
  <xsl:for-each select="./trigger">
  <tr>
   <td nowrap="nowrap"><a href="?object=trigger&amp;name={./@name}"><xsl:value-of select="./@name"/></a></td>
   <td nowrap="nowrap">AFTER
    <xsl:if test="count(./@for) = 0 or ./@for = ''">INSERT UPDATE DELETE</xsl:if>
    <xsl:if test="contains(./@for, 'I')">INSERT </xsl:if>
    <xsl:if test="contains(./@for, 'U')">UPDATE </xsl:if>
    <xsl:if test="contains(./@for, 'D')">DELETE </xsl:if>
   </td>
   <td><xsl:value-of select="./@order"/></td>
   <td><xsl:value-of select="./@description"/></td>
  </tr>
  </xsl:for-each>
 </table>
</xsl:template>

<xsl:template match="index">
 <xsl:param name="index_tablename"/>
 
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <div class="caption">for</div>
 <div class="message"><xsl:value-of select="./@for"/></div>
 <xsl:if test="./@unique = 'true'">
  <div class="caption">unique</div>
  <div class="message">Yes</div>
  <br/>
 </xsl:if>
 <xsl:if test="./@include != ''">
  <div class="caption">include</div>
  <div class="message"><xsl:value-of select="./@include"/></div>
  <br/>
 </xsl:if>
</xsl:template>

<xsl:template match="trigger">
 <xsl:param name="trigger_tablename"/>

 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <div class="caption">type</div>
 <div class="message">AFTER
  <xsl:if test="count(./@for) = 0 or ./@for = ''">INSERT UPDATE DELETE</xsl:if>
  <xsl:if test="contains(./@for, 'I')">INSERT </xsl:if>
  <xsl:if test="contains(./@for, 'U')">UPDATE </xsl:if>
  <xsl:if test="contains(./@for, 'D')">DELETE </xsl:if>
 </div>
 <br/>
 <div class="caption">definition</div>
 <div class="message">
  <pre><xsl:value-of select="./content/text()"/></pre>
 </div>
</xsl:template>

<xsl:template match="view">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <div class="caption">definition</div>
 <div class="message">
  <pre><xsl:value-of select="./content/text()"/></pre>
 </div>
</xsl:template>

<xsl:template match="function|procedure">
 <xsl:if test="./@description != ''">
  <div class="caption">description</div>
  <div class="message"><xsl:value-of select="./@description"/></div>
  <br/>
 </xsl:if>
 <div class="caption">parameters</div>
  <xsl:if test="count(./parameters/parameter) = 0">
   <div class="message">(None parameter.)</div>
  </xsl:if>
  <xsl:if test="count(./parameters/parameter) > 0">
   <table class="list">
    <tr class="head">
     <td>name</td>
     <td>datatype</td>
     <td>output</td>
     <td>default</td>
     <td>description</td>
    </tr>
    <xsl:for-each select="./parameters/parameter">
     <tr>
      <td nowrap="nowrap"><xsl:value-of select="./@name"/></td>
      <td nowrap="nowrap">
       <xsl:apply-templates select="." mode="datatype">
        <xsl:with-param name="field_name" select="./@name"/>
        <xsl:with-param name="field_datatype" select="./@datatype"/>
       </xsl:apply-templates>
      </td>
      <td nowrap="nowrap">
       <xsl:if test="./@output = 'true'">&#8730;</xsl:if>
      </td>
      <td nowrap="nowrap">
       <xsl:apply-templates select="." mode="default">
        <xsl:with-param name="field_name" select="./@name"/>
        <xsl:with-param name="field_identity" select="./@identity"/>
        <xsl:with-param name="field_default" select="./@default"/>
       </xsl:apply-templates>
      </td>
      <td><xsl:value-of select="./@description"/></td>
     </tr>
    </xsl:for-each>
   </table>
  </xsl:if>
 <br/>
 <div class="caption">return</div>
 <div class="message">
  <xsl:choose>
   <xsl:when test="count(./@return) = 0 or ./@return = ''">
    (None return value.)
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="./@return"/>
   </xsl:otherwise>
  </xsl:choose>
 </div>
 <br/>
 <div class="caption">definition</div>
 <div class="message">
  <pre><xsl:value-of select="./content/text()"/></pre>
 </div>
</xsl:template>

<xsl:template match="table" mode="index">
 <xsl:param name="table_name"/>

 <xsl:for-each select="./indexes/index">
  <a href="?object=index&amp;name={./@name}"><xsl:value-of select="./@name"/></a>
  <br/>
 </xsl:for-each>
</xsl:template>

<xsl:template match="table" mode="trigger">
 <xsl:param name="table_name"/>

 <xsl:for-each select="./triggers/trigger">
  <a href="?object=trigger&amp;name={./@name}"><xsl:value-of select="./@name"/></a>
  <br/>
 </xsl:for-each>
</xsl:template>

<xsl:template match="field|parameter" mode="default">
 <xsl:param name="field_name"/>
 <xsl:param name="field_identity"/>
 <xsl:param name="field_default"/>
 
 <xsl:if test="count($field_default) = 0 or $field_default = ''">
  <xsl:choose>
   <xsl:when test="substring($field_name, string-length($field_name) - 2) = '_no'
    or substring($field_name, string-length($field_name) - 4) = '_code'
    or substring($field_name, string-length($field_name) - 4) = '_type'
    or substring($field_name, string-length($field_name) - 4) = '_date'
    or substring($field_name, string-length($field_name) - 4) = '_time'
    or substring($field_name, string-length($field_name) - 4) = '_list'">
     <a href="?object=common-defaults">def_Empty</a>
   </xsl:when>
   <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_sign'
    or substring($field_name, string-length($field_name) - 4) = '_name'
    or substring($field_name, string-length($field_name) - 4) = '_text'
     or substring($field_name, string-length($field_name) - 4) = '_file'
    or substring($field_name, string-length($field_name) - 4) = '_note'
    or substring($field_name, string-length($field_name) - 4) = '_memo'">
     <a href="?object=common-defaults">def_NEmpty</a>
   </xsl:when>
   <xsl:when test="substring($field_name, string-length($field_name) - 2) = '_id'
    or substring($field_name, string-length($field_name) - 4) = '_unit'
    or substring($field_name, string-length($field_name) - 4) = '_numb'
    or substring($field_name, string-length($field_name) - 4) = '_rate'">
    <xsl:if test="$field_identity = 'true'">
     IDENTITY (1, 1)
    </xsl:if>
    <xsl:if test="count($field_identity) = 0 or $field_identity != 'true'">
     <a href="?object=common-defaults">def_Zero</a>
    </xsl:if>
   </xsl:when>
   <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_flag'">
    <a href="?object=common-defaults">def_False</a>
   </xsl:when>
   <xsl:when test="substring($field_name, string-length($field_name) - 3) = '_xml'">
    <a href="?object=common-defaults">def_Void</a>
   </xsl:when>
   <xsl:when test="substring($field_name, string-length($field_name) - 3) = '_bin'
    or substring($field_name, string-length($field_name) - 4) = '_guid'">
    <a href="?object=common-defaults">def_Nil</a>
   </xsl:when>
  </xsl:choose>
 </xsl:if>
 <xsl:if test="$field_default != ''">
  <xsl:choose>
   <xsl:when test="count(/database/tables/common/defaults/default[@name = $field_default]) > 0">
    <a href="?object=common-defaults"><xsl:value-of select="$field_default"/></a>
   </xsl:when>
   <xsl:otherwise>
    <xsl:value-of select="$field_default"/>
   </xsl:otherwise>
  </xsl:choose>
 </xsl:if>
</xsl:template>

<xsl:template match="field|parameter" mode="datatype">
 <xsl:param name="field_name"/>
 <xsl:param name="field_datatype"/>
 
 <xsl:choose>
  <xsl:when test="count($field_datatype) > 0 and $field_datatype != ''"><xsl:value-of select="$field_datatype"/></xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 2) = '_id'">INT</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_flag'">TINYINT</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_unit'">NUMERIC(18, 0)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_numb'">NUMERIC(18, 2)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_rate'">NUMERIC(18, 4)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 2) = '_no'">VARCHAR(20)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_code'">VARCHAR(20)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_type'">VARCHAR(20)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_date'">VARCHAR(10)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_time'">VARCHAR(10)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_list'">VARCHAR(2000)</xsl:when>  
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_sign'">NVARCHAR(200)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_name'">NVARCHAR(200)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_text'">NVARCHAR(500)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_file'">NVARCHAR(2000)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_note'">NVARCHAR(2000)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_memo'">NVARCHAR(MAX)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 3) = '_xml'">XML</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 3) = '_bin'">VARBINARY(MAX)</xsl:when>
  <xsl:when test="substring($field_name, string-length($field_name) - 4) = '_guid'">UNIQUEIDENTIFIER</xsl:when>
 </xsl:choose>
</xsl:template>

</xsl:stylesheet>

最后还有一个负责将二者关联起来的html文件，内容如下：

  1 <html>
  2 <head>
  3 <title>Database Schema</title>
  4 <meta http-equiv="content-type" content="text/html; charset=utf-8">
  5 <script language="javascript">
  6 function getHTML(theObject, theName){
  7 var XMLFile = "test.xml"; //xml文件
  8 var XSLFile = "test.xsl"; //xsl文件
  9 var outerHTML = "";
 10     
 11 if (window.ActiveXObject){ //for IE
 12 var oXMLDoc = new ActiveXObject("MSXML2.DOMDocument");
 13         oXMLDoc.async = false;
 14         oXMLDoc.load(XMLFile);
 15 var oXSLDoc = new ActiveXObject("MSXML2.FreeThreadedDOMDocument");
 16         oXSLDoc.async = false;
 17         oXSLDoc.load(XSLFile);
 18         
 19 var oXSLTemp = new ActiveXObject("MSXML2.XSLTemplate");
 20         oXSLTemp.stylesheet = oXSLDoc;
 21 var oXSLProc = oXSLTemp.createProcessor();
 22         oXSLProc.input = oXMLDoc;
 23         oXSLProc.addParameter("object", theObject);
 24         oXSLProc.addParameter("name", theName);
 25         oXSLProc.transform();
 26         
 27         outerHTML = oXSLProc.output;
 28     }else{ //for FF
 29 var oXMLDoc = loadXML(XMLFile);
 30 var oXSLDoc = loadXML(XSLFile);
 31         
 32 var oXSLProc = new XSLTProcessor();
 33         oXSLProc.importStylesheet(oXSLDoc);
 34         oXSLProc.setParameter(null, "object", theObject);
 35         oXSLProc.setParameter(null, "name", theName);
 36 var tmpResult = oXSLProc.transformToDocument(oXMLDoc);
 37         outerHTML = (new XMLSerializer()).serializeToString(tmpResult);
 38     }
 39     
 40 return(outerHTML);
 41 }
 42 function loadXML(XMLFile){
 43 var oXMLDoc = document.implementation.createDocument("", "", null);
 44     oXMLDoc.async = false;
 45 try{ //for FF
 46         oXMLDoc.load(XMLFile);
 47     }catch(e){ //for Safari
 48 var oXMLHttp = new XMLHttpRequest();
 49         oXMLHttp.open("GET", XMLFile, false);
 50         oXMLHttp.send(null);
 51         oXMLDoc = oXMLHttp.responseXML;
 52     }
 53 return (oXMLDoc);
 54 }
 55 function setContent(){
 56 var qString = window.location.search.substr(1);
 57 var qArray = qString.split("&");
 58 var tmpObject = "";
 59 var tmpName = "";
 60 for (var qId=0; qId<qArray.length; qId++){
 61 var qItem = qArray[qId].split("=");
 62 switch (qItem[0]){
 63 case "object":
 64                 tmpObject = qItem[1];
 65 break;
 66 case "name":
 67                 tmpName = qItem[1];
 68 break;
 69         }
 70     }
 71     document.getElementById("content_div").innerHTML = getHTML(tmpObject, tmpName);
 72 }
 73 </script>
 74 <style type="text/css">
 75 BODY {
 76     margin:0px;
 77     background-color:#FFFFFF;
 78     font-family:Verdana, Arial, Helvetica, sans-serif;
 79     font-size:12px;
 80     font-weight:normal;
 81     text-align:left;
 82 }
 83 DIV.index {
 84     text-align:right;
 85     font-size:9px;
 86     font-weight:bolder;
 87     padding:1px;
 88     padding-right:6px;
 89     text-transform:none;
 90 }
 91 DIV.index A {
 92     margin-right:12px;
 93 }
 94 DIV.caption {
 95     font-size:12px;
 96     font-weight:bolder;
 97     font-style:italic;
 98     line-height:18px;
 99     text-transform:capitalize;
100 }
101 DIV.message {
102     font-size:12px;
103     font-weight:normal;
104     font-style:normal;
105     padding-left:12px;
106     text-transform:none;
107 }
108 TABLE.head {
109     border:none;
110     border-bottom:solid 1px;
111     border-collapse:collapse;
112     border-color:#999999;
113     background-color:#C0FFC0;
114     width:100%;
115 }
116 TABLE.head TD.index {
117     padding:0px;
118 }
119 TABLE.head TD {
120     font-size:15px;
121     font-weight:bolder;
122     padding-left:12px;
123     padding-top:12px;
124     padding-bottom:6px;
125     text-transform:none;
126 }
127 
128 TABLE.body {
129     border:none;
130     width:100%;
131     font-size:12px;
132     text-transform:none;
133 }
134 TABLE.body TD {
135     line-height:16px;
136     padding-left:24px;
137 }
138 
139 TABLE.list {
140     text-align:left;
141     vertical-align:top;
142     border-style:solid;
143     border-width:1px;
144     border-color:#999999;
145     border-collapse:collapse;
146     table-layout:auto;
147     font-size:12px;
148 }
149 /*
150 TABLE.list TR {
151     background-color:expression((this.sectionRowIndex%2==0)?"#FFFFFF":"#F0F0F0");
152 }
153 */
154 TABLE.list TR:hover {
155     background-color:#F0F0F0;
156 }
157 TABLE.list TD {
158     border-style:solid;
159     border-width:1px;
160     border-color:#999999; 
161     text-align:left; 
162     padding:3px 6px;
163     text-transform:none;
164 }
165 TABLE.list TR.head TD{
166     border-style:solid; 
167     border-width:1px; 
168     border-color:#999999; 
169     background-color:#DDDDDD;
170     font-weight:bolder;
171     white-space:nowrap;
172     font-size:12px;
173     text-transform:capitalize;
174 }
175 </style>
176 </head>
177 <body onLoad="setContent();">
178 <div id="content_div">
179 </div>
180 </body>
181 </html>

各文件的说明见下篇。

posted on 2012-01-09 13:56  dk163  阅读(240)  评论(0)  编辑  收藏  举报

导航