def test_returnOnePlSQLAndOneSQL(self):
"""
One sql statement and one pl/sql statement yields two separate strings
"""
sql = dedent('''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM
INNER JOIN BioEntity
ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN);''')
plsql = dedent('''BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;''')
s2 = "BEGIN\nFOR i IN 1..10 LOOP\nIF MOD(i,2) = 0 THEN\nINSERT INTO temp VALUES (i, x, 'i is even');ELSE\nINSERT INTO temp VALUES (i, x, 'i is odd');END IF;x := x + 100;END LOOP;COMMIT;END;"
result = splitSQLString(sql + plsql)
r1 = result.next()
self.assertEquals(r1, sql.rstrip(";"))
r2 = result.next()
self.assertEquals(r2, s2)
self.assertRaises(StopIteration, result.next)
def test_returnOneComplexStatement(self):
"""
One complex sql statement yields a single string
"""
bigSQL = dedent('''SELECT
CL.CODE,
CL.CATEGORY,
FROM
CLIENTS_SUPPLIERS CL
INVOICES I
WHERE
CL.CODE = I.CODE AND
CL.CATEGORY = I.CATEGORY AND
CL.UP_DATE =
(SELECT
MAX(CL2.UP_DATE)
FROM
CLIENTS_SUPPLIERS CL2
WHERE
CL2.CODE = I.CODE AND
CL2.CATEGORY = I.CATEGORY AND
CL2.UP_DATE <= I.EMISSION
) AND
I.EMISSION BETWEEN DATE1 AND DATE2;''')
result = splitSQLString(bigSQL)
r1 = result.next()
self.assertEquals(r1, bigSQL.rstrip(";"))
self.assertRaises(StopIteration, result.next)
def test_returnOnePlSQLAndOneSQL(self):
"""
One sql statement and one pl/sql statement yields two separate strings
"""
sql = dedent(
'''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM
INNER JOIN BioEntity
ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN);''')
plsql = dedent(
'''BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;''')
s2 = "BEGIN\nFOR i IN 1..10 LOOP\nIF MOD(i,2) = 0 THEN\nINSERT INTO temp VALUES (i, x, 'i is even');ELSE\nINSERT INTO temp VALUES (i, x, 'i is odd');END IF;x := x + 100;END LOOP;COMMIT;END;"
result = splitSQLString(sql+plsql)
r1 = result.next()
self.assertEquals(r1, sql.rstrip(";"))
r2 = result.next()
self.assertEquals(r2, s2)
self.assertRaises(StopIteration, result.next)
def test_returnOneComplexStatement(self):
"""
One complex sql statement yields a single string
"""
bigSQL = dedent(
'''SELECT
CL.CODE,
CL.CATEGORY,
FROM
CLIENTS_SUPPLIERS CL
INVOICES I
WHERE
CL.CODE = I.CODE AND
CL.CATEGORY = I.CATEGORY AND
CL.UP_DATE =
(SELECT
MAX(CL2.UP_DATE)
FROM
CLIENTS_SUPPLIERS CL2
WHERE
CL2.CODE = I.CODE AND
CL2.CATEGORY = I.CATEGORY AND
CL2.UP_DATE <= I.EMISSION
) AND
I.EMISSION BETWEEN DATE1 AND DATE2;''')
result = splitSQLString(bigSQL)
r1 = result.next()
self.assertEquals(r1, bigSQL.rstrip(";"))
self.assertRaises(StopIteration, result.next)
def test_dontSplitOneStatement(self):
"""
A single sql statement yields a single string
"""
result = splitSQLString("select * from foo;")
r1 = result.next()
self.assertEquals(r1, "select * from foo")
self.assertRaises(StopIteration, result.next)
def test_dontSplitOneStatement(self):
"""
A single sql statement yields a single string
"""
result = splitSQLString("select * from foo;")
r1 = result.next()
self.assertEquals(r1, "select * from foo")
self.assertRaises(StopIteration, result.next)
def test_returnTwoSimpleStatements(self):
"""
Two simple sql statements yield two separate strings
"""
result = splitSQLString("select count(*) from baz; select bang from boop;")
r1 = result.next()
self.assertEquals(r1, "select count(*) from baz")
r2 = result.next()
self.assertEquals(r2, "select bang from boop")
self.assertRaises(StopIteration, result.next)
def test_returnTwoSimpleStatements(self):
"""
Two simple sql statements yield two separate strings
"""
result = splitSQLString(
"select count(*) from baz; select bang from boop;")
r1 = result.next()
self.assertEquals(r1, "select count(*) from baz")
r2 = result.next()
self.assertEquals(r2, "select bang from boop")
self.assertRaises(StopIteration, result.next)
def test_returnOnePlSQL(self):
"""
One pl/sql block yields a single string
"""
plsql = dedent('''BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;''')
s1 = 'BEGIN\nLOOP\nINSERT INTO T1 VALUES(i,i);i := i+1;EXIT WHEN i>100;END LOOP;END;'
result = splitSQLString(plsql)
r1 = result.next()
self.assertEquals(r1, s1)
self.assertRaises(StopIteration, result.next)
def test_returnOnePlSQL(self):
"""
One pl/sql block yields a single string
"""
plsql = dedent(
'''BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;''')
s1 = 'BEGIN\nLOOP\nINSERT INTO T1 VALUES(i,i);i := i+1;EXIT WHEN i>100;END LOOP;END;'
result = splitSQLString(plsql)
r1 = result.next()
self.assertEquals(r1, s1)
self.assertRaises(StopIteration, result.next)
def test_returnOnePlSQLAndOneSQLAndOneFunction(self):
"""
One sql statement and one pl/sql statement yields two separate strings
"""
sql = dedent(
'''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM
INNER JOIN BioEntity
ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN);''')
sqlfn = dedent(
'''CREATE or REPLACE FUNCTION foobar() RETURNS integer as $$
DECLARE
result integer;
BEGIN
SELECT ID into result from JOB;
RETURN result;
END
$$ LANGUAGE plpgsql;''')
plsql = dedent(
'''BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;''')
s3 = "BEGIN\nFOR i IN 1..10 LOOP\nIF MOD(i,2) = 0 THEN\nINSERT INTO temp VALUES (i, x, 'i is even');ELSE\nINSERT INTO temp VALUES (i, x, 'i is odd');END IF;x := x + 100;END LOOP;COMMIT;END;"
result = splitSQLString(sql + sqlfn + plsql)
r1 = result.next()
self.assertEquals(r1, sql.rstrip(";"))
r2 = result.next()
self.assertEquals(r2, sqlfn.rstrip(";"))
r3 = result.next()
self.assertEquals(r3, s3)
self.assertRaises(StopIteration, result.next)
def test_returnOnePlSQLAndOneSQLAndOneFunction(self):
"""
One sql statement and one pl/sql statement yields two separate strings
"""
sql = dedent('''SELECT EGM.Name, BioEntity.BioEntityId INTO AUX
FROM EGM
INNER JOIN BioEntity
ON EGM.name LIKE BioEntity.Name AND EGM.TypeId = BioEntity.TypeId
OPTION (MERGE JOIN);''')
sqlfn = dedent(
'''CREATE or REPLACE FUNCTION foobar() RETURNS integer as $$
DECLARE
result integer;
BEGIN
SELECT ID into result from JOB;
RETURN result;
END
$$ LANGUAGE plpgsql;''')
plsql = dedent('''BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,2) = 0 THEN
INSERT INTO temp VALUES (i, x, 'i is even');
ELSE
INSERT INTO temp VALUES (i, x, 'i is odd');
END IF;
x := x + 100;
END LOOP;
COMMIT;
END;''')
s3 = "BEGIN\nFOR i IN 1..10 LOOP\nIF MOD(i,2) = 0 THEN\nINSERT INTO temp VALUES (i, x, 'i is even');ELSE\nINSERT INTO temp VALUES (i, x, 'i is odd');END IF;x := x + 100;END LOOP;COMMIT;END;"
result = splitSQLString(sql + sqlfn + plsql)
r1 = result.next()
self.assertEquals(r1, sql.rstrip(";"))
r2 = result.next()
self.assertEquals(r2, sqlfn.rstrip(";"))
r3 = result.next()
self.assertEquals(r3, s3)
self.assertRaises(StopIteration, result.next)
def test_actualSchemaUpgrade(self):
"""
A real-world schema upgrade is split into the expected number of statements,
ignoring comments
"""
realsql = dedent('''
----
-- Copyright (c) 2011-2014 Apple Inc. All rights reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
----
---------------------------------------------------
-- Upgrade database schema from VERSION 16 to 17 --
---------------------------------------------------
------------------------------
-- CALENDAR_OBJECT clean-up --
------------------------------
begin
for i in (select constraint_name from user_cons_columns where column_name = 'ORGANIZER_OBJECT')
loop
execute immediate 'alter table calendar_object drop constraint ' || i.constraint_name;
end loop;
end;
alter table CALENDAR_OBJECT
drop (ORGANIZER_OBJECT);
create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT (
ICALENDAR_UID
);
-- Now update the version
update CALENDARSERVER set VALUE = '17' where NAME = 'VERSION';
''')
s1 = "begin\nfor i in (select constraint_name from user_cons_columns where column_name = 'ORGANIZER_OBJECT')\nloop\nexecute immediate 'alter table calendar_object drop constraint ' || i.constraint_name;end loop;end;"
s2 = 'alter table CALENDAR_OBJECT\n drop (ORGANIZER_OBJECT)'
s3 = 'create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT (\n ICALENDAR_UID\n)'
s4 = "update CALENDARSERVER set VALUE = '17' where NAME = 'VERSION'"
result = splitSQLString(realsql)
r1 = result.next()
self.assertEquals(r1, s1)
r2 = result.next()
self.assertEquals(r2, s2)
r3 = result.next()
self.assertEquals(r3, s3)
r4 = result.next()
self.assertEquals(r4, s4)
self.assertRaises(StopIteration, result.next)
def test_actualSchemaUpgrade(self):
"""
A real-world schema upgrade is split into the expected number of statements,
ignoring comments
"""
realsql = dedent(
'''
----
-- Copyright (c) 2011-2013 Apple Inc. All rights reserved.
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
----
---------------------------------------------------
-- Upgrade database schema from VERSION 16 to 17 --
---------------------------------------------------
------------------------------
-- CALENDAR_OBJECT clean-up --
------------------------------
begin
for i in (select constraint_name from user_cons_columns where column_name = 'ORGANIZER_OBJECT')
loop
execute immediate 'alter table calendar_object drop constraint ' || i.constraint_name;
end loop;
end;
alter table CALENDAR_OBJECT
drop (ORGANIZER_OBJECT);
create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT (
ICALENDAR_UID
);
-- Now update the version
update CALENDARSERVER set VALUE = '17' where NAME = 'VERSION';
''')
s1 = "begin\nfor i in (select constraint_name from user_cons_columns where column_name = 'ORGANIZER_OBJECT')\nloop\nexecute immediate 'alter table calendar_object drop constraint ' || i.constraint_name;end loop;end;"
s2 = 'alter table CALENDAR_OBJECT\n drop (ORGANIZER_OBJECT)'
s3 = 'create index CALENDAR_OBJECT_ICALE_82e731d5 on CALENDAR_OBJECT (\n ICALENDAR_UID\n)'
s4 = "update CALENDARSERVER set VALUE = '17' where NAME = 'VERSION'"
result = splitSQLString(realsql)
r1 = result.next()
self.assertEquals(r1, s1)
r2 = result.next()
self.assertEquals(r2, s2)
r3 = result.next()
self.assertEquals(r3, s3)
r4 = result.next()
self.assertEquals(r4, s4)
self.assertRaises(StopIteration, result.next)
