好风凭借力 – 使用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])
GO
ALTER 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

COMMENT ON COLUMN dept.deptno IS '编号';

select name from sysobjects where objectproperty(id, N'IsTable') = 1 and name not like N'#%%' order by name
select * 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 name
select * from sysobjects where id = object_id(N'temp_tableSpaceInfo') AND xtype='U'

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
set rowcount @id 
begin
select * from employees order by empno
end
GO

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select top (@id) * from employees order by empno
end
GO

CREATE PROCEDURE [dbo].[P_Rowcount]
@id int
as
set nocount on
begin
select * from employees order by empno offset 0 rows fetch first @id rows only;
end
GO

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
  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;
END
GO

CREATE PROCEDURE [dbo].[P_CurrentOf] AS
BEGIN
  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;
END
GO

SELECT IDENTITY(INT,1,1) AS rowid,* INTO #tmp
FROM employees
ORDER BY empno

SELECT row_number() over () as rowid, * INTO #tmp
FROM employees
ORDER 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 C2
USING source AS C1 
ON C2.id = C1.id
WHEN MATCHED
   THEN UPDATE 
      SET
         C2.country = C1.country,
         C2.city = c1.city
WHEN NOT MATCHED
   THEN INSERT (id, country, city)
            VALUES (C1.id, C1.country, C1.city);

begin
update 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);
end
go

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 source
on conflict (id)
do update set country=excluded.country,city=excluded.city;

CREATE、ALTER、DROP FULLTEXT CATALOG
CREATE、ALTER、DROP FULLTEXT INDEX
CREATE、ALTER、DROP FULLTEXT STOPLIST
exec 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 xml
as
begin	
   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 off
end
go

CREATE OR REPLACE FUNCTION xmlQueryID(in_xml xml) 
RETURNS TABLE (id text) 
AS $$
DECLARE
BEGIN
  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 xml
as
begin	
   set nocount on	
   select * from t_xml_test
   where id in (select * from xmlQueryID(@xml)) ;
   set nocount off
end
go