欢迎大家讨论：通用SQL定义语言

[xjlh]

我们打算研究的一个小课题，请大家多提宝贵意见

通用SQL定义语言

问题提出
１． 数据库版本很多，升级困难。如果对每个版本都写一个升级脚本，则开发和测试的工作量很大（每一种版本的升级情况都要编写脚本并测试）；
２． SQL代码和文档不一致。SQL代码和文档是分别编写的，如果其中之一发生改动之后，另外一份也应该作相应的改动，但在实际开发中很难做到这一点，这就造成了SQL 代码和文档的不一致，影响后续开发和维护工作。
解决方法－General SQL（通用SQL语言，简称GSQL）
定义一种通用的数据定义语言（与具体的数据库无关），用此语言定义数据库的表结构，同时这个语言也可以直接生成数据结构文档。
当升级时，通过一个通用的升级工具读取最新的数据定义（采用GSQL编写），然后与待升级的数据库对照，生成差异脚本后执行，就可以完成升级过程。
文档可以通过一个文档生成工具，从数据定义（采用GSQL编写）直接生成文档。
注意：GSQL只是数据定义语言，不涉及数据操作（如：SELECT、INSERT、UPDATE、DELETE等）。
为什么采用GSQL
1. 标准SQL比较复杂，升级工具的自动分析比较困难；
2. 标准SQL存在很多方言，不适合学习和使用；
3. GSQL语法简单，易于程序自动分析，并自动生成升级脚本和文档；
4. 由于GSQL语法简单，也容易学习和推广；

研究计划
时间：一个人月
1. 定义出GSQL的语法（如：表定义、字段定义、索引、数据类型等）；
2. 文档生成工具，用于分析GSQL语法并生成文档；
3. 通用升级工具，用于分析GSQL语法，对比目标数据库，并生成差异升级脚本。
附录１－GSQL数据结构描述
table ::= tablename + remark + column* +index*
column ::= name+remark+datatype+length+dicimals+ isnotnull+defaultvalue
index ::= indexname+remark+indextype+column*
indextype ::= {unique | primarykey | ordinary} + remark
datatype ::= SCounter | SInt | SNumric｜SDatetime| SString | SBlob |SBool
附录2－GSQL的物理实现
XML风格
<table name="$TableName" tablecode="$TableCode">
<column name="$ColumnName" columncode="$columncode" remark="$Remark" datatype="$Datatype" length="$length" dicimals="$decimals" isnotnull="$isnotnull" defaultvalue="$defaultvalue"/>
<index name="$IndexName" remark="$Remark" indextype="$Indextype" >
<column name="$ColumnName">
</index>
</table>
文档风格（通过tab键作为column描述的定界符号）
#-------------------------
table:$TableName $TableCode
column:$ColumnName
columnode=$ColumnCode
datatype=$Datatype
length=$length
dicimals=$decimals
isnotnull=$isnotnull
defaultvalue=$defaultvalue
index :$IndexName $ColumnName,$ColumnName,$ColumnName,...
#-------------------------

中文文档风格
注意：（通过tab键作为column扩展描述的定界符号） "#-"#10#13 作为表的定界符号
#-------------------------
表:$TableName
表描述：$remark
列:$ColumnName
编码=$columncode
数据类型=$Datatype
长度=$length
小数位=$decimals
是否空=$isnotnull
缺省值=$defaultvalue
列描述=$remark
索引 :$IndexName $IndexType $ColumnName,$ColumnName,$ColumnName,...
#-------------------------
lisp风格
$remark 需要有定界符号
($TableName $TableCode
(column $ColumnName $ColumnCode $Datatype $length $decimals $isnotnull $defaultvalue $Remark)
(index $IndexName $Remark $Indextype
($ColumnName $ColumnName $ColumnName ...)
)
)
中文文档风格例子
Create table base (
f1 integer default 1 not null primary key,
f2 string (3)
)
对应：
#-------------------------
表:基本表 base
表描述：基本表格，可以书写描述并能够换行
列:f1的名字
编码=f1
数据类型=SInt
是否空=否
缺省值=1
列描述=备注
列:f2的名字
编码=f2
数据类型=String
长度=3
是否空=是
缺省值=1
列描述=备注
索引 :pk_name 主键 f1
#-------------------------

[txlicenhe]

时间：一个人月


等楼主的好消息。

[shuiniu]

up!

[zjcxc]

对数据升级,我个人持这样的看法.

根据最新的数据结构脚本生成新的数据库,将旧的数据库中的数据复制到新的数据库中.
从而完成升级.

[zjcxc]

--对于这方面,我写了两个通用的存储过程.供大家参考:

/*--比较两个数据库的表结构差异

可以比较
--邹建 2003.9--*/
/*--调用示例

exec p_comparestructure 'xzkh_model','xzkh_new'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_comparestructure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_comparestructure]
GO

create proc p_comparestructure
@dbname1 varchar(250), --要比较的数据库名1
@dbname2 varchar(250) --要比较的数据库名2
as
create table #tb1(表名1 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))

create table #tb2(表名2 varchar(250),字段名 varchar(250),序号 int,标识 bit,主键 bit,类型 varchar(250),
占用字节数 int,长度 int,小数位数 int,允许空 bit,默认值 varchar(500),字段说明 varchar(500))

--得到数据库1的结构
exec('insert into #tb1 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname1+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname1+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname1+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname1+'..syscolumns a
left join '+@dbname1+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname1+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join '+@dbname1+'..syscomments e on a.cdefault=e.id
left join '+@dbname1+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')

--得到数据库2的结构
exec('insert into #tb2 SELECT
表名=d.name,字段名=a.name,序号=a.colid,
标识=case when a.status=0x80 then 1 else 0 end,
主键=case when exists(SELECT 1 FROM '+@dbname2+'..sysobjects where xtype=''PK'' and name in (
SELECT name FROM '+@dbname2+'..sysindexes WHERE indid in(
SELECT indid FROM '+@dbname2+'..sysindexkeys WHERE id = a.id AND colid=a.colid
))) then 1 else 0 end,
类型=b.name, 占用字节数=a.length,长度=a.prec,小数位数=a.scale, 允许空=a.isnullable,
默认值=isnull(e.text,''''''),字段说明=isnull(g.[value],'''''')
FROM '+@dbname2+'..syscolumns a
left join '+@dbname2+'..systypes b on a.xtype=b.xusertype
inner join '+@dbname2+'..sysobjects d on a.id=d.id and d.xtype=''U'' and d.name<>''dtproperties''
left join '+@dbname2+'..syscomments e on a.cdefault=e.id
left join '+@dbname2+'..sysproperties g on a.id=g.id and a.colid=g.smallid
order by a.id,a.colorder')
--and not exists(select 1 from #tb2 where 表名2=a.表名1)
select 比较结果=case when a.表名1 is null and b.序号=1 then '库1缺少表：'+b.表名2
when b.表名2 is null and a.序号=1 then '库2缺少表:'+a.表名1
when a.字段名 is null and exists(select 1 from #tb1 where 表名1=b.表名2) then '库1 ['+b.表名2+'] 缺少字段：'+b.字段名
when b.字段名 is null and exists(select 1 from #tb2 where 表名2=a.表名1) then '库2 ['+a.表名1+'] 缺少字段：'+a.字段名
when a.标识<>b.标识 then '标识不同'
when a.主键<>b.主键 then '主键设置不同'
when a.类型<>b.类型 then '字段类型不同'
when a.占用字节数<>b.占用字节数 then '占用字节数'
when a.长度<>b.长度 then '长度不同'
when a.小数位数<>b.小数位数 then '小数位数不同'
when a.允许空<>b.允许空 then '是否允许空不同'
when a.默认值<>b.默认值 then '默认值不同'
when a.字段说明<>b.字段说明 then '字段说明不同'
else '' end,
*
from #tb1 a
full join #tb2 b on a.表名1=b.表名2 and a.字段名=b.字段名
where a.表名1 is null or a.字段名 is null or b.表名2 is null or b.字段名 is null
or a.标识<>b.标识 or a.主键<>b.主键 or a.类型<>b.类型
or a.占用字节数<>b.占用字节数 or a.长度<>b.长度 or a.小数位数<>b.小数位数
or a.允许空<>b.允许空 or a.默认值<>b.默认值 or a.字段说明<>b.字段说明
order by isnull(a.表名1,b.表名2),isnull(a.序号,b.序号)--isnull(a.字段名,b.字段名)
go

[zjcxc]

/*--数据库数据复制

将一个数据库中的数据复制到另一个数据库
如果某列在目标数据库中为标识列,将不会被复制

适用范围:数据库结构发生了变化,想将旧数据库进行升级
这样就可以根据新的数据库结构创建一个空库,然后
将旧数据库的所有数据复制到新库中
--邹建 203.10--*/

/*--调用示例

exec p_copydb '源数据库','目标数据库'
exec p_copydb 'acc_五医','acc_演示数据8'
--*/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_copydb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_copydb]
GO

create proc p_copydb
@o_dbname sysname, --要复制数据的数据库--源数据库
@n_dbname sysname, --接收数据的数据库--目标数据库
@cleardb bit=0 --清空目标数据库
as
declare @sql nvarchar(4000)

--禁用约束,防止复制时的数据冲突
set @sql='declare #tbc cursor for select name,tbname=object_name(parent_obj)
from '+@n_dbname+'..sysobjects where xtype in(''C'',''F'')'
exec(@sql)
declare @name sysname,@tbname sysname
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] NOCHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc

--复制数据
declare @sql1 varchar(8000)
set @sql='declare #tb cursor for select a.name from '
+@o_dbname+'..sysobjects a inner join '
+@n_dbname+'..sysobjects b on a.name=b.name
where a.xtype=''U'' and b.xtype=''U'''
exec(@sql)
open #tb
fetch next from #tb into @tbname
while @@fetch_status=0
begin
select @sql1=''
,@sql='select @sql1=@sql1+'',[''+a.name+'']'' from(
select name from '+@o_dbname+'..syscolumns where id in
(select id from '+@o_dbname+'..sysobjects where name='''+@tbname+''')
) a inner join (
select name from '+@n_dbname+'..syscolumns where status<>0x80 and id in
(select id from '+@n_dbname+'..sysobjects where name='''+@tbname+''')
) b on a.name=b.name'
exec sp_executesql @sql,N'@sql1 nvarchar(4000) out',@sql1 out

select @sql1=substring(@sql1,2,8000)
exec('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
if @@error<>0
print('insert into '+@n_dbname+'..['+@tbname+']('+@sql1
+') select '+@sql1+' from '+@o_dbname+'..['+@tbname+']')
fetch next from #tb into @tbname
end
close #tb
deallocate #tb

--数据复制完成后启用约束
open #tbc
fetch next from #tbc into @name,@tbname
while @@fetch_status=0
begin
set @sql='alter table '+@n_dbname+'..['+@tbname+'] CHECK CONSTRAINT ['+@name+']'
exec(@sql)
fetch next from #tbc into @name,@tbname
end
close #tbc
deallocate #tbc
go