走向云原生数据库 - 使用 Babelfish 加速迁移 SQL Server 的代码实践

create table dept(    deptno int NOT NULL PRIMARY KEY,    dname varchar(14),    loc varchar(13)) 
create table employees (    empno int NOT NULL PRIMARY KEY,    ename varchar(10),      job varchar(9),      mgr int,      hiredate datetime,      sal money,      comm money,      deptno int)

ALTER TABLE [dbo].[employees]  WITH CHECK ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])REFERENCES [dbo].[dept] ([deptno])GOALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_DEPT]GO

ALTER TABLE [dbo].[employees] ADD CONSTRAINT [FK_DEPT] FOREIGN KEY([deptno])REFERENCES [dbo].[dept] ([deptno])GO

ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]GO

/* ALTER ROLE [???] ADD MEMBER [NT AUTHORITY\SYSTEM]GO */

ALTER DATABASE [???] SET RECOVERY FULL GO

/* ALTER DATABASE [???] SET RECOVERY FULL GO */

ALTER AUTHORIZATION ON [dbo].[employees] TO  SCHEMA OWNER GO

/* ALTER AUTHORIZATION ON [dbo].[employees] TO  SCHEMA OWNER GO */

EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'dept', @level2type=N'COLUMN',@level2name=N'deptno'GO

select name from sysobjects where objectproperty(id, N'IsTable') = 1 and name not like N'#%%' order by nameselect * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND objectproperty(id, N'IsUserTable') = 1

select name from sysobjects where xtype in ('U','IT','S') and name not like N'#%%' order by nameselect * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND xtype='U'

CREATE PROCEDURE [dbo].[P_Rowcount]@id intasset nocount onset rowcount @id beginselect * from employees order by empnoendGO

CREATE PROCEDURE [dbo].[P_Rowcount]@id intasset nocount onbeginselect top (@id) * from employees order by empnoendGO

CREATE PROCEDURE [dbo].[P_Rowcount]@id intasset nocount onbeginselect * from employees order by empno offset 0 rows fetch first @id rows only;endGO

CREATE PROCEDURE [dbo].[P_CurrentOf] ASBEGIN  DECLARE @empno int  DECLARE NoResponce CURSOR FOR    SELECT empno FROM employees;  OPEN NoResponce;  FETCH NEXT FROM NoResponce INTO @empno;  DELETE FROM employees WHERE CURRENT OF NoResponce;ENDGO

CREATE PROCEDURE [dbo].[P_CurrentOf] ASBEGIN  DECLARE @empno int  DECLARE NoResponce CURSOR FOR    SELECT empno FROM employees;  OPEN NoResponce;  FETCH NEXT FROM NoResponce INTO @empno;  DELETE FROM employees WHERE empno = @empno;ENDGO

SELECT IDENTITY(INT,1,1) AS rowid,* INTO #tmpFROM employeesORDER BY empno

SELECT row_number() over () as rowid, * INTO #tmpFROM employeesORDER BY empno

create table source(   id      int not null primary key ,   country varchar(20) null,   city    varchar(20));
insert into source  (id, country, city) VALUES  (1, 'RUSSIA',  'MOSCOW'),  (2, 'FRANCE',  'PARIS'),  (3, 'ENGLAND', 'LONDON'),  (4, 'USA',     'NEW YORK'),  (5, 'GERMANY', 'BERLIN'),  (6, 'BRAZIL',  'BRASILIA');
create table target(   id      int not null primary key ,   country varchar(20) null,   city    varchar(20));
insert into target  (id, country, city) VALUES  (1, 'JAPAN',   'TOKYO'),  (4, 'USA',     'DENVER'),  (7, 'CHINA',   'BEI JING');

MERGE INTO target AS C2USING source AS C1 ON C2.id = C1.idWHEN MATCHED   THEN UPDATE       SET         C2.country = C1.country,         C2.city = c1.cityWHEN NOT MATCHED   THEN INSERT (id, country, city)            VALUES (C1.id, C1.country, C1.city);

beginupdate target set country = C1.country, city = C1.city from (select id, country, city from source) C1 where target.id = C1.id;insert into target (id, country, city) select * from source as C1 where not exists (select id from target where id = C1.id);endgo

with upsert as(update target c2 set country=c1.country, city=c1.city  from source c1 where c1.id=c2.id  RETURNING c2.*)insert into target select a.id, a.country, a.city from source a where a.id not in (select b.id from upsert b);

insert into target (id,country,city) select id,country,city from sourceon conflict (id)do update set country=excluded.country,city=excluded.city;

CREATE、ALTER、DROP FULLTEXT CATALOGCREATE、ALTER、DROP FULLTEXT INDEXCREATE、ALTER、DROP FULLTEXT STOPLISTexec sp_fulltext_database 'enable';

set search_path=dbo;create extension pg_bigm;
CREATE TABLE fulltext_doc (doc text);INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 成本优化');INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 性能优化');INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 提升使用体验');INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 2-gram 全文搜索功能的工具');INSERT INTO fulltext_doc VALUES('Babelfish助力SQL迁移 中提供 3-gram 全文搜索功能的工具');
CREATE INDEX fulltext_doc_idx ON fulltext_doc USING gin (doc gin_bigm_ops);alter table fulltext_doc owner to dbo;

SELECT CAST(SWITCHOFFSET(TODATETIMEOFFSET(SYSUTCDATETIME(),'+00:00'),'+08:00') AS DATETIME)

CREATE OR REPLACE FUNCTION dbo.f_get_cst()RETURNS sys.datetime AS $$   BEGIN      RETURN cast(timezone('Asia/Shanghai',now()) as sys.datetime);END;$$ LANGUAGE plpgsql;

create table t_xml_test (  id int,  country  nvarchar(max),  industry nvarchar(max)); 
insert t_xml_test values(1, 'China', 'Manufacturing and foreign trade business');insert t_xml_test values(2, 'USA', 'Financial and Bioindustry');insert t_xml_test values(3, 'Russia', 'Resource export');

create procedure p_xml_test @xml xmlasbegin       set nocount on   select * from t_xml_test   where id in (select imgXML.Item.value('id[1]','int') from @xml.nodes('/root/country') as imgXML(Item));    set nocount offendgo

CREATE OR REPLACE FUNCTION xmlQueryID(in_xml xml) RETURNS TABLE (id text) AS $$DECLAREBEGIN  RETURN QUERY    select * from (      WITH xmldata(data) AS (VALUES (in_xml::xml))      SELECT xmltable.*      FROM XMLTABLE('/root/country' PASSING (SELECT data FROM xmldata) COLUMNS id text)) as foo;END;$$ LANGUAGE plpgsql;

create procedure p_xml_test @xml xmlasbegin       set nocount on       select * from t_xml_test   where id in (select * from xmlQueryID(@xml)) ;   set nocount offendgo