数据库生成器(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}&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}&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&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() <= $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&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(.)}&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&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">√</xsl:when>
<xsl:when test="./@nullable = 'true'">√</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() <= $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'">√</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">√</xsl:when>
<xsl:when test="./@nullable = 'true'">√</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&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'">√</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&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'">√</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&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&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>
各文件的说明见下篇。