JRT抛弃数据库类型Datetime和流类型后只支持常规类型,同时没开放SQL语句api为多库支持打好基础。框架上支持多库只是一方面,让开发人员怎么从开发库方便的得到其他数据库的库也是一个重要问题。不可能我每个库的基础数据都要重新做,建表也要每周库都同步做,那样是不现实的,也是对开发不友好的,实际上是把架构不行的锅扔给了开发人员。
JRT基于IRIS当主开发库,就是看中了M的程序执行能力和M的类表改表能力,以及操作global的能力。因为开发阶段表结构改动频繁,有的改动需要清空数据,global带来最直接的方案。借助M执行能力可以用M就给各种库生成建库脚本。所以可以达到IRIS有了就各种数据库都有了的效果。
实现原理就是用M读取IRIS的表信息构造其他库的建库SQL,和插入数据SQL,由于RowID主键是自增的,所以把数据导入其他库的时候外键参照的数据就存在怎样保留参照的问题。因为RowID不受控制。
为了由开发库得到各种数据库的支持库,需要解决外键数据同步问题。为此提出了RowID对齐理论,把构造库分为几个步骤:
1.创建表,表说明,列说明
2.生成插入数据SQL,为了保持外键关系,用RowID对齐理论把不可控的RowID实现可控效果,就是把开发库数据空的RowID生成对应数量的Insert和delete语句,来强行使新库数据的RowID和开发库数据对齐。每个表的RowID都对齐了的话,那么外键关系也是可靠的(这就是我为多库支持提出的RowID对齐理论)。
3.所有表建完和数据插入完毕之后再生产外键参照SQL构建外键关系。
基于上面3步,不管库有多大,数据参照多复杂都能快速平移开发库得到JRT支持的各种库。借助此理论,JRT往PostGreSql迁移的数据越来越多,表越来越多,一百个多级参照关系和上十万数据的开发库都能直接平移,所以别看我平时老是拿PostGreSql库演示,其实开发我根本不用他,只是定期做个平移库测试的,到PostGreSql库上面一个表我都没建过。
总体就是:一库既多库、脚本定乾坤
构造PostGreSql和人大金仓库的脚本
Class Jbase.ImportTableToPostGresql Extends %RegisteredObject
{
/// 生成创建PostgreSql的SQL语句脚本
/// w ##class(Jbase.ImportTableToPostGresql).MakePGScript("/mktablesql.sql","")
/// w ##class(Jbase.ImportTableToPostGresql).MakePGScript("D:\mktablesql.sql","")
ClassMethod MakePGScript(path, onlyInsert)
{
s path=$g(path)
s onlyInsert=$g(onlyInsert)
s file=##class(%File).%New(path)
//存在就追加
i ##class(%File).Exists(path) d
.d ##class(%File).Delete(path)
.d file.Open("WSN:/SHARED:/IOTABLE=""UTF8""")
//不存在就新建
e d
.d file.Open("WSN:/SHARED:/IOTABLE=""UTF8""")
//创建隐士转换
//d ..WriteLineWithCode(file,"CREATE CAST (INTEGER AS VARCHAR) WITH INOUT AS IMPLICIT;")
//d ..WriteLineWithCode(file,"CREATE CAST (VARCHAR AS INTEGER) WITH INOUT AS IMPLICIT;")
//d ..WriteLineWithCode(file,"CREATE CAST (BIGINT AS VARCHAR) WITH INOUT AS IMPLICIT;")
//d ..WriteLineWithCode(file,"CREATE CAST (VARCHAR AS BIGINT) WITH INOUT AS IMPLICIT;")
//d ..WriteLineWithCode(file,"CREATE CAST (TEXT AS INTEGER) WITH INOUT AS IMPLICIT;")
s rset1 = ##class(%ResultSet).%New()
d rset1.Prepare("select TABLE_NAME,DESCRIPTION FROM information_schema.Tables where TABLE_SCHEMA='dbo'")
s exeret1=rset1.Execute()
s colCount1=rset1.GetColumnCount()
s dealNum1=0
s ViewNameMap=""
While(rset1.Next())
{
s colField=rset1.GetColumnName(1)
s ColValue=rset1.GetDataByName(colField)
i $e(ColValue,1,2)="V_" d continue
.s ViewNameMap(ColValue)=""
s colField2=rset1.GetColumnName(2)
s ColValue2=rset1.GetDataByName(colField2)
s ColValue2=$tr(ColValue2,$c(10))
s ColValue2=$tr(ColValue2,$c(13))
//i $e(ColValue,1,3)'="SYS" continue
i onlyInsert'="1" d
.//创建表的sql,先设置主键不自增
.s oneCreateSql=..MakeOneCreateTableSql(ColValue)
.d ..WriteLineWithCode(file,oneCreateSql_";")
.s tableName="dbo."_ColValue
.//添加表说明sql
.s tableRemarkSql="comment on table "_tableName_" is '"_$tr(ColValue,"_","")_":"_ColValue2_"'"
.d ..WriteLineWithCode(file,tableRemarkSql_";")
.d ..WriteLineWithCode(file,"")
.//添加列说明sql
.s ColRemarkList=..MakeOneColRemarkSql(ColValue)
.i $ll(ColRemarkList) d
..f ci=1:1:$ll(ColRemarkList) d
...d ..WriteLineWithCode(file,$lg(ColRemarkList,ci)_";")
.d ..WriteLineWithCode(file,"")
.d ..WriteLineWithCode(file,"")
//构造insert语句
d ..MakeOneInsertSql(file,ColValue)
//构造索引语句
d ..MakeOneIndexSql(file,ColValue)
d ..WriteLineWithCode(file,"")
d ..WriteLineWithCode(file,"")
d ..WriteLineWithCode(file,"")
}
//构造外键参照
s rset2 = ##class(%ResultSet).%New()
d rset2.Prepare("select TABLE_NAME,DESCRIPTION FROM information_schema.Tables where TABLE_SCHEMA='dbo'")
s exeret2=rset2.Execute()
s colCount2=rset2.GetColumnCount()
s dealNum2=0
While(rset2.Next())
{
s colField=rset2.GetColumnName(1)
s ColValue=rset2.GetDataByName(colField)
i $e(ColValue,1,2)="V_" continue
s colField2=rset2.GetColumnName(2)
s ColValue2=rset2.GetDataByName(colField2)
//i $e(ColValue,1,3)'="SYS" continue
//构造外键约束语句
d ..MakeOneFKSql(file,ColValue)
}
//构造创建视图语句
s ViewName="" f s ViewName=$o(ViewNameMap(ViewName)) q:ViewName="" d
.s ClassName="dbo."_$tr(ViewName,"_")
.s pathTmp=path_".xml"
.s ret=$system.OBJ.Export(ClassName_".cls",pathTmp)
.s file1=##class(%File).%New(pathTmp)
.Do file1.Open("R:/SHARED")
.s AllStr=""
.f i=1:1:300 d
..s Str=file1.Read(32000,.sc)
..s AllStr=AllStr_Str_" "
.d file1.Close()
.s ResponseXml= ##class(LIS.Util.COM.XML).FromXML(AllStr)
.s SqlTableName=ResponseXml.Class.SqlTableName
.s Sql=ResponseXml.Class.ViewQuery
.s SqlTmp=$replace(Sql,"dbo.",$c(0))
.s SqlTmp=$replace(SqlTmp,"AS ",$c(1)_"""")
.s SqlTmp=$replace(SqlTmp,".",".""")
.s SqlTmp1=""
.s FlagNum=0
.f j=1:1:$l(SqlTmp) d
..s OneChar=$e(SqlTmp,j,j)
..i OneChar="""" s FlagNum=FlagNum+1
..i (OneChar=" ")||(OneChar=",") d
...i FlagNum>0 d
....s FlagNum=FlagNum-1
....s SqlTmp1=SqlTmp1_""""
..s SqlTmp1=SqlTmp1_OneChar
.i FlagNum>0 s SqlTmp1=SqlTmp1_""""
.s SqlTmp=$replace(SqlTmp1,$c(0),"dbo.")
.s SqlTmp=$replace(SqlTmp,$c(1),"AS ")
.s ViewSql="create view dbo."_SqlTableName_" as "_SqlTmp_";"
.d ..WriteLineWithCode(file,ViewSql)
q "完成"
}
/// 生成一个表的创建表语句
/// w ##class(Jbase.ImportTableToPostGresql).MakeOneCreateTableSql("SYS_User")
ClassMethod MakeOneCreateTableSql(TableName)
{
s TableName=$g(TableName)
s rset = ##class(%ResultSet).%New()
d rset.Prepare("select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,DESCRIPTION,IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='"_TableName_"'")
s exeret=rset.Execute()
s colCount=rset.GetColumnCount()
s retSql="create table dbo."_TableName
s retSql=retSql_"("
s retSql=retSql_"""RowID"" serial primary key"
While(rset.Next())
{
s outStr=""
s colField=rset.GetColumnName(1)
s colName=rset.GetDataByName(colField)
s colField1=rset.GetColumnName(2)
s colType=rset.GetDataByName(colField1)
s colField2=rset.GetColumnName(3)
s colLen=rset.GetDataByName(colField2)
i colLen>10485760 s colLen=10485760
s colField3=rset.GetColumnName(4)
s colDesc=rset.GetDataByName(colField3)
s colField4=rset.GetColumnName(5)
s NULLABLE=rset.GetDataByName(colField4)
i '$l(colDesc) s colDesc=colName
s NullStr=""
i NULLABLE="NO" d
.s NullStr=" not null"
i colName="RowID" continue
s type=colType
s lenStr=""
i colType="integer" d
.s type="integer"
.s colLen=10
i colType="bigint" d
.s type="integer"
.s colLen=10
i colType="smallint" d
.s type="integer"
.s colLen=10
i colType="tinyint" d
.s type="integer"
.s colLen=10
e i colType="varchar" d
.s type="varchar"
.s lenStr="("_colLen_")"
e i colType="bit" d
.s type="boolean"
.s colLen=1
e i type="longvarbinary"
.s type="varchar"
.s colLen=10485760
e i colType="double" d
.s type="double precision"
.s colLen=10
e i colType="numeric" d
.s type="numeric"
.s colLen=10
s retSql=retSql_","""_colName_""" "_type_lenStr_NullStr
}
s retSql=retSql_")"
q retSql
}
/// 生成一个表的列说明
/// w ##class(Jbase.ImportTableToPostGresql).MakeOneColRemarkSql("SYS_User")
ClassMethod MakeOneColRemarkSql(TableName)
{
s TableName=$g(TableName)
s rset = ##class(%ResultSet).%New()
d rset.Prepare("select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,DESCRIPTION,IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='"_TableName_"'")
s exeret=rset.Execute()
s colCount=rset.GetColumnCount()
s tableName="dbo."_TableName
s retList=""
While(rset.Next())
{
s outStr=""
s colField=rset.GetColumnName(1)
s colName=rset.GetDataByName(colField)
s colField1=rset.GetColumnName(2)
s colType=rset.GetDataByName(colField1)
s colField2=rset.GetColumnName(3)
s colLen=rset.GetDataByName(colField2)
i colLen>10485760 s colLen=10485760
s colField3=rset.GetColumnName(4)
s colDesc=rset.GetDataByName(colField3)
s colField4=rset.GetColumnName(5)
s NULLABLE=rset.GetDataByName(colField4)
s colDesc=$tr(colDesc,$c(10))
s colDesc=$tr(colDesc,$c(13))
i '$l(colDesc) s colDesc=colName
s sql="comment on column "_tableName_"."""_colName_""" is '"_colDesc_"'"
s retList=retList_$lb(sql)
}
q retList
}
/// 构造一个表的insert语句
/// w ##class(Jbase.ImportTableToPostGresql).MakeOneInsertSql("","SYS_User")
ClassMethod MakeOneInsertSql(file, TableName)
{
s TableName=$g(TableName)
//替换下划线
s tableName=$tr(TableName,"_")
s MaxRowID=0
i $d(@("^dbo."_tableName_"D")) d
.s MaxRowID=""
.&sql(SELECT count(*) INTO MaxRowID FROM dbo."_TableName_")
//大于10000的不认为是基础数据
//i ($e(tableName,1,3)'="SYS")&&(MaxRowID>10000) q ""
//i ($e(tableName,1,3)'="SYS") q ""
i MaxRowID>10000 q ""
i ($e(tableName,$l(tableName)-2,$l(tableName))="Log") q ""
s rset = ##class(%ResultSet).%New()
d rset.Prepare("select COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,DESCRIPTION,IS_NULLABLE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='"_TableName_"'")
s exeret=rset.Execute()
s colCount=rset.GetColumnCount()
s tableName="dbo."_TableName
s retList=""
s insertHead="insert into "_tableName_"("
s index=0
s typeStr=""
While(rset.Next())
{
s outStr=""
s colField=rset.GetColumnName(1)
s colName=rset.GetDataByName(colField)
s colField1=rset.GetColumnName(2)
s colType=rset.GetDataByName(colField1)
s colField2=rset.GetColumnName(3)
s colLen=rset.GetDataByName(colField2)
i colLen>10485760 s colLen=10485760
s colField3=rset.GetColumnName(4)
s colDesc=rset.GetDataByName(colField3)
s colField4=rset.GetColumnName(5)
s NULLABLE=rset.GetDataByName(colField4)
i '$l(colDesc) s colDesc=colName
i colName="RowID" continue
s type=colType
i colType="integer" d
.s type="integer"
i colType="bigint" d
.s type="integer"
i colType="smallint" d
.s type="integer"
i colType="tinyint" d
.s type="integer"
e i colType="varchar" d
.s type="varchar"
.s lenStr="("_colLen_")"
e i colType="bit" d
.s type="bit"
.s lenStr="("_colLen_")"
e i type="longvarbinary"
.s type="varchar"
e i colType="double" d
.s type="numeric"
e i colType="numeric" d
.s type="numeric"
s typeStr=typeStr_type_","
s index=index+1
i index>1 d
.s insertHead=insertHead_","""_colName_""""
e d
.s insertHead=insertHead_""""_colName_""""
}
s insertHead=insertHead_") values("
s rset1 = ##class(%ResultSet).%New()
d rset1.Prepare("select * FROM dbo."_TableName_" order by RowID asc")
s exeret=rset1.Execute()
s colCount=rset1.GetColumnCount()
s rowNum=0
s newRowID=0
While(rset1.Next())
{
s index=0
s valSql=""
s rowNum=rowNum+1
s rowID=0
f i=1:1:colCount d
.s colField=rset1.GetColumnName(i)
.s colVal=rset1.GetDataByName(colField)
.s colVal=..DealNotSeeChar(colVal)
.i i=1 s rowID=colVal q
.s type=$p(typeStr,",",i-1)
.s OutChar="'"
.i (type="integer")||(type="numeric")||(type="bit") d
..s OutChar=""
..i '$l(colVal) s colVal="null"
.i type="bit" d
..i colVal="1" s colVal="true"
..i colVal="0" s colVal="false"
.//帮助里面有特殊字符
.i colField="FormHelp" s colVal=""
.s index=index+1
.i index>1 d
..s valSql=valSql_","_OutChar_colVal_OutChar
.e d
..s valSql=valSql_""_OutChar_colVal_OutChar
i rowNum<rowID d
.//比实际RowID少就删除让RowID增长
.f j=1:1:(rowID-rowNum) d
..i $l(file) d
...d ..WriteLineWithCode(file,insertHead_valSql_");")
...s newRowID=newRowID+1
...//对齐RowID
...d ..WriteLineWithCode(file,"delete from dbo."_TableName_" where ""RowID""="_newRowID_"")
i $l(file) d
.d ..WriteLineWithCode(file,insertHead_valSql_");")
.s newRowID=newRowID+1
s rowNum=newRowID
}
i $l(file) d ..WriteLineWithCode(file,"")
q ""
}
/// 构造生成索引语句
ClassMethod MakeOneIndexSql(file, TableName)
{
s TableName=$g(TableName)
//替换下划线
s tableName=$tr(TableName,"_")
s rset = ##class(%ResultSet).%New()
d rset.Prepare("SELECT INDEX_NAME,COLUMN_NAME,NON_UNIQUE FROM information_schema.INDEXES WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='"_TableName_"' order by ORDINAL_POSITION asc")
s exeret=rset.Execute()
s colCount=rset.GetColumnCount()
s tableName="dbo."_TableName
s MapObj=""
While(rset.Next())
{
s outStr=""
s colField=rset.GetColumnName(1)
s IndexName=rset.GetDataByName(colField)
s colField1=rset.GetColumnName(2)
s ColName=rset.GetDataByName(colField1)
s colField2=rset.GetColumnName(3)
s NoUnique=rset.GetDataByName(colField2)
s IndexName=$tr(IndexName,".")
i '$d(MapObj(NoUnique,IndexName)) d
.s MapObj(NoUnique,IndexName)=$lb(ColName)
e d
.s MapObj(NoUnique,IndexName)=MapObj(NoUnique,IndexName)_$lb(ColName)
}
s NoUnique="" f s NoUnique=$o(MapObj(NoUnique)) q:NoUnique="" d
.s IndexName="" f s IndexName=$o(MapObj(NoUnique,IndexName)) q:IndexName="" d
..s NoUniqueStr=""
..i NoUnique="0" s NoUniqueStr="unique"
..s sql="create "_NoUniqueStr_" index "_TableName_"_"_IndexName_" on dbo."_TableName_" ("
..s ListCol=$g(MapObj(NoUnique,IndexName))
..f j=1:1:$ll(ListCol) d
...s ColName=$lg(ListCol,j)
...i j=1 s sql=sql_""""_ColName_""""
...e s sql=sql_","""_ColName_""""
..s sql=sql_");"
..i $l(file) d ..WriteLineWithCode(file,sql)
i $l(file) d ..WriteLineWithCode(file,"")
}
/// 构造生成索引语句
ClassMethod MakeOneFKSql(file, TableName)
{
s TableName=$g(TableName)
//替换下划线
s tableName=$tr(TableName,"_")
s rset = ##class(%ResultSet).%New()
d rset.Prepare("SELECT constraint_name,column_name,referenced_table_name,referenced_column_name FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA='dbo' and TABLE_NAME='"_TableName_"'")
s exeret=rset.Execute()
s colCount=rset.GetColumnCount()
s tableName="dbo."_TableName
While(rset.Next())
{
s outStr=""
s colField=rset.GetColumnName(1)
s fkName=rset.GetDataByName(colField)
s colField1=rset.GetColumnName(2)
s ColName=rset.GetDataByName(colField1)
s colField2=rset.GetColumnName(3)
s refTableName=rset.GetDataByName(colField2)
i '$l(refTableName) continue
s colField3=rset.GetColumnName(4)
s refColName=rset.GetDataByName(colField3)
i '$l(refColName) continue
s sql="alter table dbo."_TableName_" add constraint "_TableName_"_"_fkName_" foreign key ("""_ColName_""") references dbo."_refTableName_" ("""_refColName_""");"
i $l(file) d ..WriteLineWithCode(file,sql)
}
i $l(file) d ..WriteLineWithCode(file,"")
}
/// 带编码转换写字符
ClassMethod WriteLineWithCode(file, str)
{
d file.WriteLine(str)
//d file.WriteLine($zcvt(str,"O","UTF8"))
}
/// 去除不可见字符
ClassMethod DealNotSeeChar(Data As %String) As %String
{
s Data=$g(Data)
i $l(Data) d
.f i=0:1:31 d
..s Data=$tr(Data,$c(i))
.s Data=$tr(Data,$c(127))
q Data
}
}