[CalendarServer-changes] [11059] CalendarServer/trunk/txdav/common/datastore
source_changes at macosforge.org
source_changes at macosforge.org
Thu Apr 18 14:30:17 PDT 2013
Revision: 11059
http://trac.calendarserver.org//changeset/11059
Author: dre at apple.com
Date: 2013-04-18 14:30:17 -0700 (Thu, 18 Apr 2013)
Log Message:
-----------
Add support for executing pl/sql in the context of Oracle schema upgrades.
Modified Paths:
--------------
CalendarServer/trunk/txdav/common/datastore/sql.py
CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_16_to_17.sql
CalendarServer/trunk/txdav/common/datastore/sql_tables.py
CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
Modified: CalendarServer/trunk/txdav/common/datastore/sql.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql.py 2013-04-18 20:58:44 UTC (rev 11058)
+++ CalendarServer/trunk/txdav/common/datastore/sql.py 2013-04-18 21:30:17 UTC (rev 11059)
@@ -57,7 +57,7 @@
from txdav.carddav.iaddressbookstore import IAddressBookTransaction
from txdav.common.datastore.common import HomeChildBase
-from txdav.common.datastore.sql_tables import schema
+from txdav.common.datastore.sql_tables import schema, splitSQLString
from txdav.common.datastore.sql_tables import _BIND_MODE_OWN, \
_BIND_STATUS_ACCEPTED, _BIND_STATUS_DECLINED, \
NOTIFICATION_OBJECT_REVISIONS_TABLE
@@ -71,7 +71,6 @@
from twext.python.clsprop import classproperty
from twext.enterprise.ienterprise import AlreadyFinishedError
-from twext.enterprise.dal.parseschema import significant
from twext.enterprise.dal.syntax import \
Delete, utcNowSQL, Union, Insert, Len, Max, Parameter, SavepointAction, \
@@ -94,7 +93,6 @@
from pycalendar.datetime import PyCalendarDateTime
from cStringIO import StringIO
-from sqlparse import parse
import time
current_sql_schema = getModule(__name__).filePath.sibling("sql_schema").child("current.sql").getContent()
@@ -1004,19 +1002,11 @@
@inlineCallbacks
def execSQLBlock(self, sql):
"""
- Execute a block of SQL by parsing it out into individual statements and execute
- each of those.
-
+ Execute SQL statements parsed by splitSQLString.
FIXME: temporary measure for handling large schema upgrades. This should NOT be used
for regular SQL operations - only upgrades.
"""
- parsed = parse(sql)
- for stmt in parsed:
- while stmt.tokens and not significant(stmt.tokens[0]):
- stmt.tokens.pop(0)
- if not stmt.tokens:
- continue
- stmt = str(stmt).rstrip(";")
+ for stmt in splitSQLString(sql):
yield self.execSQL(stmt)
Modified: CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_16_to_17.sql
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_16_to_17.sql 2013-04-18 20:58:44 UTC (rev 11058)
+++ CalendarServer/trunk/txdav/common/datastore/sql_schema/upgrades/oracle-dialect/upgrade_from_16_to_17.sql 2013-04-18 21:30:17 UTC (rev 11059)
@@ -23,6 +23,13 @@
-- 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);
Modified: CalendarServer/trunk/txdav/common/datastore/sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2013-04-18 20:58:44 UTC (rev 11058)
+++ CalendarServer/trunk/txdav/common/datastore/sql_tables.py 2013-04-18 21:30:17 UTC (rev 11059)
@@ -27,8 +27,9 @@
from twext.enterprise.ienterprise import ORACLE_DIALECT, POSTGRES_DIALECT
from twext.enterprise.dal.syntax import Insert
from twext.enterprise.ienterprise import ORACLE_TABLE_NAME_MAX
-from twext.enterprise.dal.parseschema import schemaFromPath
-
+from twext.enterprise.dal.parseschema import schemaFromPath, significant
+from sqlparse import parse
+from re import compile
import hashlib
@@ -384,6 +385,43 @@
out.write('\n);\n\n')
+def splitSQLString(sqlString):
+ """
+ Strings which mix zero or more sql statements with zero or more pl/sql
+ statements need to be split into individual sql statements for execution.
+ This function was written to allow execution of pl/sql during Oracle schema
+ upgrades.
+ """
+ aggregated = ''
+ inPlSQL = None
+ parsed = parse(sqlString)
+ for stmt in parsed:
+ while stmt.tokens and not significant(stmt.tokens[0]):
+ stmt.tokens.pop(0)
+ if not stmt.tokens:
+ continue
+ if inPlSQL is not None:
+ agg = str(stmt).strip()
+ if "end;".lower() in agg.lower():
+ inPlSQL = None
+ aggregated += agg
+ rex = compile("\n +")
+ aggregated = rex.sub('\n', aggregated)
+ yield aggregated.strip()
+ continue
+ aggregated += agg
+ continue
+ if inPlSQL is None:
+ #if 'begin'.lower() in str(stmt).split()[0].lower():
+ if 'begin'.lower() in str(stmt).lower():
+ inPlSQL = True
+ aggregated += str(stmt)
+ continue
+ else:
+ continue
+ yield str(stmt).rstrip().rstrip(";")
+
+
if __name__ == '__main__':
import sys
if len(sys.argv) == 2:
Modified: CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py
===================================================================
--- CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2013-04-18 20:58:44 UTC (rev 11058)
+++ CalendarServer/trunk/txdav/common/datastore/test/test_sql_tables.py 2013-04-18 21:30:17 UTC (rev 11059)
@@ -31,10 +31,12 @@
from twext.enterprise.dal.syntax import SchemaSyntax
from txdav.common.datastore.sql_tables import schema, _translateSchema
-from txdav.common.datastore.sql_tables import SchemaBroken
+from txdav.common.datastore.sql_tables import SchemaBroken, splitSQLString
from twext.enterprise.dal.test.test_parseschema import SchemaTestHelper
+from textwrap import dedent
+
class SampleSomeColumns(TestCase, SchemaTestHelper):
"""
Sample some columns from the tables defined by L{schema} and verify that
@@ -268,3 +270,175 @@
+class SQLSplitterTests(TestCase):
+ """
+ Test that strings which mix zero or more sql statements with zero or more
+ pl/sql statements are split into individual statements.
+ """
+
+ 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_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_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_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_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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.macosforge.org/pipermail/calendarserver-changes/attachments/20130418/13932a6b/attachment-0001.html>
More information about the calendarserver-changes
mailing list