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_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_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_returnTwoPlSQLCreateOrReplace(self):
"""
One pl/sql block yields a single string
"""
plsql = """create or replace function next_job_all(now timestamp)
return integer is
cursor c1 is
select JOB_ID from JOB
where ASSIGNED is NULL and PAUSE = 0 and NOT_BEFORE <= now
order by PRIORITY desc
for update skip locked;
result integer;
begin
open c1;
fetch c1 into result;
close c1;
return result;
end;
/
create or replace function next_job_medium_high(now timestamp)
return integer is
cursor c1 is
select JOB_ID from JOB
where PRIORITY != 0 and ASSIGNED is NULL and PAUSE = 0 and NOT_BEFORE <= now
order by PRIORITY desc
for update skip locked;
result integer;
begin
open c1;
fetch c1 into result;
close c1;
return result;
end;
/
"""
s1 = """create or replace function next_job_all(now timestamp)
return integer is
cursor c1 is
select JOB_ID from JOB
where ASSIGNED is NULL and PAUSE = 0 and NOT_BEFORE <= now
order by PRIORITY desc
for update skip locked;result integer;begin
open c1;fetch c1 into result;close c1;return result;end;"""
s2 = """create or replace function next_job_medium_high(now timestamp)
return integer is
cursor c1 is
select JOB_ID from JOB
where PRIORITY != 0 and ASSIGNED is NULL and PAUSE = 0 and NOT_BEFORE <= now
order by PRIORITY desc
for update skip locked;result integer;begin
open c1;fetch c1 into result;close c1;return result;end;"""
result = splitSQLString(plsql)
r1 = result.next()
self.assertEquals(r1, s1)
r2 = result.next()
self.assertEquals(r2, s2)
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 ready(self, createDatabaseConn, createDatabaseCursor):
"""
Subprocess is ready. Time to initialize the subservice.
If the database has not been created and there is a dump file,
then the dump file is imported.
"""
if self.resetSchema:
try:
createDatabaseCursor.execute(
"drop database {}".format(self.databaseName)
)
except postgres.DatabaseError:
pass
try:
createDatabaseCursor.execute(
"create database {} with encoding 'UTF8'"
.format(self.databaseName)
)
except:
# database already exists
sqlToExecute = None
else:
# database does not yet exist; if dump file exists, execute it,
# otherwise execute schema
sqlToExecute = self.schema
createDatabaseCursor.close()
createDatabaseConn.close()
if sqlToExecute is not None:
connection = self.produceConnection()
cursor = connection.cursor()
for statement in splitSQLString(sqlToExecute):
cursor.execute(statement)
connection.commit()
connection.close()
if self.shutdownDeferred is None:
# Only continue startup if we've not begun shutdown
self.subServiceFactory(
self.produceConnection, self
).setServiceParent(self)
def ready(self, createDatabaseConn, createDatabaseCursor):
"""
Subprocess is ready. Time to initialize the subservice.
If the database has not been created and there is a dump file,
then the dump file is imported.
"""
if self.resetSchema:
try:
createDatabaseCursor.execute("drop database {}".format(
self.databaseName))
except postgres.DatabaseError:
pass
try:
createDatabaseCursor.execute(
"create database {} with encoding 'UTF8'".format(
self.databaseName))
except:
# database already exists
sqlToExecute = None
else:
# database does not yet exist; if dump file exists, execute it,
# otherwise execute schema
sqlToExecute = self.schema
createDatabaseCursor.close()
createDatabaseConn.close()
if sqlToExecute is not None:
connection = self.produceConnection()
cursor = connection.cursor()
for statement in splitSQLString(sqlToExecute):
cursor.execute(statement)
connection.commit()
connection.close()
if self.shutdownDeferred is None:
# Only continue startup if we've not begun shutdown
self.subServiceFactory(self.produceConnection,
self).setServiceParent(self)
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-2017 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-2015 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 doit(txn):
for statement in splitSQLString(nodeSchema + schemaText):
yield txn.execSQL(statement)