4:07 p.m.
Author: justi9
Date: 2008-11-25 10:07:55 -0500 (Tue, 25 Nov 2008)
New Revision: 2877
Added:
mgmt/trunk/mint/python/mint/sql.py
Log:
Forgot to add this (important\!) file
Added: mgmt/trunk/mint/python/mint/sql.py
===================================================================
--- mgmt/trunk/mint/python/mint/sql.py (rev 0)
+++ mgmt/trunk/mint/python/mint/sql.py 2008-11-25 15:07:55 UTC (rev 2877)
@@ -0,0 +1,220 @@
+import logging, mint
+from time import clock
+from sqlobject import MixedCaseUnderscoreStyle
+
+log = logging.getLogger("mint.sql")
+
+dbStyle = MixedCaseUnderscoreStyle()
+profile = None
+
+def transformTable(table):
+ try:
+ table = mint.schema.schemaReservedWordsMap[table]
+ except KeyError:
+ pass
+
+ table = table[0] + table[1:] # XXX why is this necessary?
+ table = dbStyle.pythonClassToDBTable(table)
+
+ return table
+
+def transformColumn(column):
+ return dbStyle.pythonAttrToDBColumn(column)
+
+class SqlOperation(object):
+ def __init__(self, name):
+ self.name = name
+
+ self.time = None
+ self.text = None
+
+ if profile:
+ profile.ops.append(self)
+
+ def key(self):
+ if hasattr(self, "cls"):
+ return "%s(%s)" % (self.name, getattr(self, "cls").__name__)
+ else:
+ return self.name
+
+ def generate(self):
+ pass
+
+ def execute(self, cursor, values=None):
+ self.text = self.generate()
+
+ try:
+ if profile:
+ start = clock()
+ cursor.execute(self.text, values)
+ self.time = clock() - start
+ else:
+ cursor.execute(self.text, values)
+ except:
+ log.warn("Text: %s", self.text)
+
+ if values:
+ for item in values.items():
+ log.warn(" %-20s %r", *item)
+
+ raise
+
+class SqlGetId(SqlOperation):
+ def __init__(self, cls):
+ super(SqlGetId, self).__init__("get_id")
+
+ self.cls = cls
+
+ def generate(self):
+ table = self.cls.sqlmeta.table
+
+ return """
+ select id from %s
+ where source_scope_id = %%(sourceScopeId)s
+ and source_object_id = %%(sourceObjectId)s
+ """ % table
+
+class SqlSetStatsRefs(SqlOperation):
+ def __init__(self, cls):
+ super(SqlSetStatsRefs, self).__init__("set_stats_refs")
+
+ self.cls = cls
+
+ def generate(self):
+ table = self.cls.sqlmeta.table
+
+ return """
+ update %s
+ set stats_curr_id = %%(statsId)s, stats_prev_id = stats_curr_id
+ where id = %%(id)s
+ """ % table
+
+class SqlInsert(SqlOperation):
+ def __init__(self, cls, attrs):
+ super(SqlInsert, self).__init__("insert")
+
+ self.cls = cls
+ self.attrs = attrs
+
+ def generate(self):
+ table = self.cls.sqlmeta.table
+
+ cols = list()
+ vals = list()
+
+ for name in self.attrs:
+ cols.append(transformColumn(name))
+ vals.append("%%(%s)s" % name)
+
+ colsSql = ", ".join(cols)
+ valsSql = ", ".join(vals)
+
+ insert = "insert into %s (%s) values (%s)" % (table, colsSql, valsSql)
+ select = "select currval('%s_id_seq')" % table
+
+ sql = "%s; %s" % (insert, select)
+
+ return sql
+
+class SqlUpdate(SqlOperation):
+ def __init__(self, cls, attrs):
+ super(SqlUpdate, self).__init__("update")
+
+ self.cls = cls
+ self.attrs = attrs
+
+ def generate(self):
+ table = self.cls.sqlmeta.table
+
+ elems = list()
+
+ for name in self.attrs:
+ elems.append("%s = %%(%s)s" % (transformColumn(name), name))
+
+ elemsSql = ", ".join(elems)
+
+ sql = "update %s set %s where id = %%(id)s" % (table, elemsSql)
+
+ return sql
+
+class SqlGetBrokerRegistration(SqlOperation):
+ def __init__(self):
+ super(SqlGetBrokerRegistration, self).__init__("get_broker_reg")
+
+ def generate(self):
+ return """
+ select id
+ from broker_registration
+ where url = %(url)s
+ """
+
+class SqlAttachBroker(SqlOperation):
+ def __init__(self):
+ super(SqlAttachBroker, self).__init__("attach_broker")
+
+ def generate(self):
+ return """
+ update broker_registration
+ set broker_id = %(id)s
+ where id = %(registrationId)s;
+ update broker
+ set registration_id = %(registrationId)s
+ where id = %(id)s
+ """
+
+class SqlProfile(object):
+ def __init__(self):
+ self.ops = list()
+ self.commitTime = 0.0
+
+ def report(self):
+ timesByKey = dict()
+
+ executeTime = 0.0
+
+ for op in self.ops:
+ if op.time is not None:
+ executeTime += op.time
+
+ try:
+ times = timesByKey[op.key()]
+
+ if op.time is not None:
+ times.append(op.time)
+ except KeyError:
+ if op.time is not None:
+ timesByKey[op.key()] = list((op.time,))
+
+ fmt = "%-40s %9.2f %9.2f %6i"
+ records = list()
+
+ for key, values in timesByKey.items():
+ count = len(values)
+ ttime = sum(values) * 1000
+ atime = ttime / float(count)
+
+ records.append((key, ttime, atime, count))
+
+ print
+
+ srecords = sorted(records, key=lambda x: x[1], reverse=True)
+
+ for i, rec in enumerate(srecords):
+ print fmt % rec
+
+ if i >= 10:
+ break
+
+ print
+
+ srecords = sorted(records, key=lambda x: x[2], reverse=True)
+
+ for i, rec in enumerate(srecords):
+ print fmt % rec
+
+ if i >= 10:
+ break
+
+ print
+ print "Total statement execute time: %9.3f seconds" % executeTime
+ print "Total commit time: %9.3f seconds" % self.commitTime