SQL解析引擎已经可以生成完整的异构SQLAST,除有少数小bug之外已经基本达到了预期的目标。另外在SQL解析引擎基础上开发的CowNewSQL翻译器也可以实现了基本的功能。可以翻译大部分主流的sql语句,比如:
(1)可以将符合CowNewSQL语法的select now()翻译成对应如下的特定平台sql:
mssql:Select GETDATE()
mysql:Select NOW()
oracle8i:Select SYSDATE FROM DUAL
oracle9i:Select SYSDATE FROM DUAL
(2)可以将符合CowNewSQL语法的
update T2 set fid=t1.fid from (select * from t2
left join t3 on t3.fid=t2.fid union select * from t5)
翻译成对应如下的特定平台sql:
mssql:UPDATE T2 SET fid = t1.fid
FROM(
Select *
From t2 left join t3 on t3.fid = t2.fid
union Select *
From t5
)
mysql:UPDATE T2 SET fid = t1.fid
FROM(
Select *
From t2 left join t3 on t3.fid = t2.fid
union Select *
From t5
)
oracle8i:UPDATE T2 SET fid = t1.fid
FROM(
Select *
From t2,t3 WHERE t3.fid = t2.fid(+)
union Select *
From t5
)
oracle9i:UPDATE T2 SET fid = t1.fid
FROM(
Select *
From t2 left join t3 on t3.fid = t2.fid
union Select *
From t5
)
select top 20 ABS(-1),lcase(trim(name)),tochar(now()), ADD_SECONDS(now(),22),t2.name,DateAdd(Year,now(),3) from T_Table
,(select * from T_MD where FID=2222222222) as tmd
left join T_T4 on tmd.id>T_Table.id
left join T_T2 as t2 on T_T2.id=T_Table.id
right join T_T3 as t3 on t2.id=t3.id
翻译成对应如下的特定平台sql:
mssql:Select top 20
ABS(-1) , LOWER(LTRIM(RTRIM(name)) ,CONVERT(VARCHAR, GETDATE()),DATEADD(second,GETDATE(),22),t2.name,DATEADD(Year, GETDATE(), 3)
From T_Table,
Select *
From T_MD
Where FID = 2222222222 as tmd
left join T_T4 on tmd.id > T_Table.id left join T_T2 as t2 on T_T2.id = T_Table.id right join T_T3 as t3 on t2.id = t3.id
mysql:Select ABS(-1) , LOWER(LTRIM(RTRIM(name)) ,CONCAT('', NOW()),DATE_ADD(NOW(), INTERVAL 22 SECOND),t2.name,DATE_ADD(3, INTERVAL NOW() YEAR)
From T_Table,
Select *
From T_MD
Where FID = 2222222222 as tmd
left join T_T4 on tmd.id > T_Table.id left join T_T2 as t2 on T_T2.id = T_Table.id right join T_T3 as t3 on t2.id = t3.id limit 0, 20
From T_Table,
Select *
From T_MD
Where FID = 2222222222 as tmd
,T_T4,T_T2 as t2,T_T3 as t3 WHERE ROWNUM <= 20
AND tmd.id > T_Table.id(+) AND T_T2.id = T_Table.id(+) AND t2.id(+) = t3.id
oracle9i:Select ABS(-1) , LOWER(LTRIM(RTRIM(name)) , TO_CHAR(SYSDATE) ,(SYSDATE + 22/86400),t2.name,add_months(3, TRUNC(SYSDATE) * 12)
From T_Table,
Select *
From T_MD
Where FID = 2222222222 as tmd
left join T_T4 on tmd.id > T_Table.id left join T_T2 as t2 on T_T2.id = T_Table.id right join T_T3 as t3 on t2.id = t3.id WHERE ROWNUM <= 20
目前仅支持MSSQLSERVER,MYSQL,Oracle8i,Oracle9i,我们计划进一步支持DB2UDB,FireBird、ACCESS等数据库。而且SQL解析引擎是用JDK5开发的,我们将是用Retrotranslator将其转换个以保证可以在JDK1.4上运行。
目前我们的项目还是很缺乏高手,比如CowNewSQL这块就缺乏对各种数据库比较熟悉的朋友,还有能对CowNewSQL进行全面测试的朋友,希望对这方面开发比较感兴趣的能加入到我们的开发中来,共同打造优秀的开源产品。可以直接给我发送邮件about521 at 163.com