Author: justi9
Date: 2010-02-05 10:53:42 -0500 (Fri, 05 Feb 2010)
New Revision: 3842
Added:
mgmt/trunk/rosemary/python/rosemary/sqloperation.py
mgmt/trunk/rosemary/python/rosemary/types.py
Removed:
mgmt/trunk/rosemary/python/rosemary/sql.py
Modified:
mgmt/trunk/rosemary/bin/rosemary-test
mgmt/trunk/rosemary/python/rosemary/model.py
mgmt/trunk/rosemary/python/rosemary/sqlmodel.py
mgmt/trunk/rosemary/python/rosemary/util.py
Log:
* Flesh out the sql modeling of qmf classes, adding constraints and
indexes and correct types
* Remove old sql operations in favor of the new ones that use the sql
metadata
* Improve the test tool
* Consolidate util code
Modified: mgmt/trunk/rosemary/bin/rosemary-test
===================================================================
--- mgmt/trunk/rosemary/bin/rosemary-test 2010-02-05 11:17:08 UTC (rev 3841)
+++ mgmt/trunk/rosemary/bin/rosemary-test 2010-02-05 15:53:42 UTC (rev 3842)
@@ -1,6 +1,5 @@
#!/usr/bin/python
-import sys, os, logging
from rosemary.model import *
from rosemary.sql import *
@@ -11,12 +10,6 @@
for cls in package.classes:
print " class %s" % cls.name
- select = SelectObject(cls)
- #print select.emit()
-
- update = UpdateObject(cls)
- #print update.emit()
-
for prop in cls.properties:
print " prop %s %s" % (prop.name, prop.title or
"")
@@ -29,12 +22,37 @@
for arg in meth.arguments:
print " arg %s" % arg.name
-def do_schema(args):
- model.sql_model.write_ddl(sys.stdout)
+def do_ddl(args):
+ model.sql_model.write_drop_ddl(sys.stdout)
+ model.sql_model.write_create_ddl(sys.stdout)
+def do_dml(args):
+ for schema in model.sql_model.schemas:
+ for table in schema.tables:
+ select = SqlSelectItem(table)
+ insert = SqlInsertItem(table)
+ update = SqlUpdateItem(table)
+ delete = SqlDeleteItem(table)
+
+ print "---", table.name, "---"
+ print
+ print insert.emit(table.columns)
+ print
+ print select.emit(table.columns)
+ print
+ print update.emit(table.columns)
+ print
+ print delete.emit(table.columns)
+ print
+
if __name__ == "__main__":
model = RosemaryModel()
+
model.load_qmf_dir(os.path.join(os.environ["ROSEMARY_HOME"],
"xml"))
+ model.init()
- #do_model(sys.argv)
- do_schema(sys.argv)
+ if len(sys.argv) == 1:
+ print "model, ddl, dml"
+ sys.exit(1)
+
+ globals()["do_%s" % sys.argv[1]](sys.argv[2:])
Modified: mgmt/trunk/rosemary/python/rosemary/model.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/model.py 2010-02-05 11:17:08 UTC (rev 3841)
+++ mgmt/trunk/rosemary/python/rosemary/model.py 2010-02-05 15:53:42 UTC (rev 3842)
@@ -1,16 +1,10 @@
-import sys
-import os
-
-from string import Template
-
-try:
- from xml.etree.ElementTree import *
-except ImportError:
- from elementtree.ElementTree import *
-
from sql import *
from sqlmodel import *
+from types import *
+from util import *
+log = logging.getLogger("rosemary.model")
+
class RosemaryModel(object):
def __init__(self):
self.packages = list()
@@ -46,6 +40,10 @@
pkg = self.packages_by_name[child.get("name")]
pkg.extend(child)
+ def init(self):
+ for pkg in self.packages:
+ pkg.init()
+
class RosemaryPackage(object):
def __init__(self, model, name):
self.model = model
@@ -70,7 +68,8 @@
cls.extend(child)
def init(self):
- pass
+ for cls in self.classes:
+ cls.init()
class RosemaryClass(object):
def __init__(self, package, name):
@@ -89,11 +88,56 @@
self.methods = list()
self.methods_by_name = dict()
- stats_name = "%s_stats" % self.name
-
self.sql_table = SqlTable(self.package.sql_schema, self.name)
- self.sql_stats_table = SqlTable(self.package.sql_schema, stats_name)
+ self.sql_select = SqlSelectItem(self.sql_table)
+ self.sql_insert = SqlInsertItem(self.sql_table)
+ self.sql_update = SqlUpdateItem(self.sql_table)
+
+ name = "%sStats" % self.name
+
+ self.sql_stats_table = SqlTable(self.package.sql_schema, name)
+
+ self.add_columns()
+
+ def add_columns(self):
+ id_col = SqlColumn(self.sql_table, "id", "serial")
+
+ name = "%s_pk" % self.name
+
+ SqlPrimaryKeyConstraint(self.sql_table, name, (id_col,))
+
+ agent_col = SqlColumn(self.sql_table, "qmf_agent_id",
"text")
+ object_col = SqlColumn(self.sql_table, "qmf_object_id",
"text")
+
+ name = "%s_qmf_ids_uq" % self.name
+
+ SqlUniqueConstraint(self.sql_table, name, (agent_col, object_col))
+
+ SqlColumn(self.sql_table, "qmf_session_id", "text")
+ SqlColumn(self.sql_table, "qmf_update_time", "timestamp")
+ SqlColumn(self.sql_table, "qmf_create_time", "timestamp")
+
+ col = SqlColumn(self.sql_table, "qmf_delete_time",
"timestamp")
+ col.nullable = True
+
+ stats_id_col = SqlColumn(self.sql_stats_table, "id",
"serial")
+
+ name = "%s_pk" % self.sql_stats_table.name
+
+ SqlPrimaryKeyConstraint(self.sql_stats_table, name, (stats_id_col,))
+
+ parent_col = SqlColumn(self.sql_stats_table, "parent_id",
"int4")
+ parent_col.foreign_key_column = id_col
+
+ update_col = SqlColumn \
+ (self.sql_stats_table, "qmf_update_time", "timestamp")
+
+ name = "%s_%s_uq" % (self.sql_stats_table.name, update_col.name)
+
+ SqlUniqueConstraint \
+ (self.sql_stats_table, name, (parent_col, update_col))
+
def load(self, elem):
for child in elem.findall("property"):
prop = RosemaryProperty(self, child.get("name"))
@@ -120,6 +164,36 @@
meth = self.methods_by_name[child.get("name")]
meth.extend(child)
+ def init(self):
+ for prop in self.properties:
+ prop.init()
+
+ for stat in self.statistics:
+ stat.init()
+
+ for meth in self.methods:
+ meth.init()
+
+ def load_object(self, cursor, id):
+ self.sql_select.execute(cursor, self.sql_table.columns, {"id": id})
+
+ return object # XXX
+
+ def save_object(self, cursor, object):
+ assert isinstance(object, RosemaryObject)
+
+ values = object.__dict__
+
+ try:
+ self.sql_update.execute(cursor, self.sql_table.columns, values)
+ except: # XXX need better exception
+ self.sql_insert.execute(cursor, self.sql_table.columns, values)
+
+ def delete_object(self, cursor, object):
+ assert isinstance(object, RosemaryObject)
+
+ self.sql_delete.execute(cursor, (), {"id": object.id})
+
class RosemaryProperty(object):
def __init__(self, cls, name):
self.cls = cls
@@ -137,7 +211,7 @@
self.is_optional = None
self.description = None
- self.sql_column = SqlColumn(self.cls.sql_table, self.name)
+ self.sql_column = None
def load(self, elem):
self.type = elem.get("type")
@@ -152,8 +226,22 @@
self.title = elem.findtext("title")
def init(self):
- pass
+ type = sql_types_by_qmf_type[self.type]
+ self.sql_column = SqlColumn(self.cls.sql_table, self.name, type)
+ self.sql_column.nullable = self.is_optional
+
+ if self.references:
+ try:
+ cls = self.cls.package.classes_by_name[self.references]
+ except KeyError:
+ log.warn("Reference to '%s' invalid", self.references)
+ return
+
+ col = cls.sql_table.columns_by_name["id"]
+
+ self.sql_column.foreign_key_column = col
+
class RosemaryStatistic(object):
def __init__(self, cls, name):
self.cls = cls
@@ -166,7 +254,7 @@
self.cls.statistics.append(self)
self.cls.statistics_by_name[self.name] = self
- self.sql_column = SqlColumn(self.cls.sql_stats_table, self.name)
+ self.sql_column = None
def load(self, elem):
self.type = elem.get("type")
@@ -177,21 +265,23 @@
pass
def init(self):
- self.column = SqlColumn(self.cls.stats_table, self.name)
+ type = sql_types_by_qmf_type[self.type]
+ self.sql_column = SqlColumn(self.cls.sql_stats_table, self.name, type)
+
class RosemaryMethod(object):
def __init__(self, cls, name):
self.cls = cls
+ self.name = name
- self.name = name
+ self.cls.methods.append(self)
+ self.cls.methods_by_name[self.name] = self
+
self.description = None
self.arguments = list()
self.arguments_by_name = dict()
- self.cls.methods.append(self)
- self.cls.methods_by_name[self.name] = self
-
def load(self, elem):
self.description = elem.get("desc")
@@ -202,6 +292,10 @@
def extend(self, elem):
pass
+ def init(self):
+ for arg in self.arguments:
+ arg.init()
+
def call(self, console, object, callback, **kwargs):
pass
@@ -225,23 +319,24 @@
def extend(self, elem):
pass
+ def init(self):
+ pass
+
class RosemaryObject(object):
def __init__(self, cls):
self.cls = cls
self.id = None
- self.qmf_broker_id = None
+ self.qmf_agent_id = None
self.qmf_object_id = None
for name in self.cls.properties:
setattr(name, None)
- self.__select = SelectObject(self.cls)
- self.__update = UpdateObject(self.cls)
- self.__insert = InsertObject(self.cls)
+ self.sql_insert = SqlInsert(self.cls.sql_table)
def load(self, cursor, id):
- self.__select.execute(cursor, {"id": id})
+ pass # XXX self.__select.execute(cursor, {"id": id})
def save(self, cursor):
assert self.id is not None
Deleted: mgmt/trunk/rosemary/python/rosemary/sql.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/sql.py 2010-02-05 11:17:08 UTC (rev 3841)
+++ mgmt/trunk/rosemary/python/rosemary/sql.py 2010-02-05 15:53:42 UTC (rev 3842)
@@ -1,50 +0,0 @@
-from model import *
-from util import *
-
-class Operation(object):
- def __init__(self, cls):
- self.cls = cls
-
- def execute(self, cursor, args):
- text = self.emit()
- results = cursor.execute(text, args)
- return results
-
-class SelectOperation(Operation):
- def emit_columns(self):
- names = [x.name for x in self.cls.properties]
- cols = map(translate_column, names)
- return ", ".join(cols)
-
-class SelectObject(SelectOperation):
- def emit(self):
- cols = self.emit_columns()
- table = translate_table(self.cls.name)
- return "select %s from %s where id = %%(id)" % (cols, table)
-
-class UpdateOperation(Operation):
- def emit_exprs(self):
- names = [x.name for x in self.cls.properties]
- exprs = list()
-
- for name in names:
- exprs.append("%s = %%(%s)" % (translate_column(name), name))
-
- return ", ".join(exprs)
-
-class UpdateObject(UpdateOperation):
- def emit(self):
- exprs = self.emit_exprs()
- table = translate_table(self.cls.name)
- return "update %s set %s where id = %%(id)" % (table, exprs)
-
-def translate_column(name):
- name = unstudly(name)
-
- if name.endswith("_ref"):
- name = "%s_id" % name[:-4]
-
- return name
-
-def translate_table(name):
- return unstudly(name)
Modified: mgmt/trunk/rosemary/python/rosemary/sqlmodel.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/sqlmodel.py 2010-02-05 11:17:08 UTC (rev 3841)
+++ mgmt/trunk/rosemary/python/rosemary/sqlmodel.py 2010-02-05 15:53:42 UTC (rev 3842)
@@ -8,10 +8,14 @@
self.relations = list()
self.relations_by_name = dict()
- def write_ddl(self, out):
+ def write_create_ddl(self, out):
for schema in self.schemas:
- schema.write_ddl(out)
+ schema.write_create_ddl(out)
+ def write_drop_ddl(self, out):
+ for schema in self.schemas:
+ schema.write_drop_ddl(out)
+
class SqlSchema(object):
def __init__(self, model, name):
self.model = model
@@ -23,15 +27,23 @@
self.tables = list()
self.tables_by_name = dict()
- def write_ddl(self, out):
+ self.indexes = list()
+ self.indexes_by_name = dict()
+
+ def write_create_ddl(self, out):
out.write("create schema \"%s\"\n" % self.name)
for table in self.tables:
- if table.columns:
- table.write_ddl(out)
+ table.write_create_ddl(out)
+ for index in self.indexes:
+ index.write_create_ddl(out)
+
out.write(" ;\n")
+ def write_drop_ddl(self, out):
+ out.write("drop schema \"%s\" cascade;\n" % self.name)
+
class SqlTable(object):
def __init__(self, schema, name):
self.schema = schema
@@ -43,26 +55,94 @@
self.columns = list()
self.columns_by_name = dict()
- def write_ddl(self, out):
- out.write(" create table \"%s\" (\n" % self.name)
+ self.constraints = list()
+ self.constraints_by_name = dict()
+ def write_create_ddl(self, out):
+ out.write(" create table \"%s\" (" % self.name)
+
exprs = list()
- for column in self.columns:
- exprs.append("\"%s\" text" % column.name)
+ for col in self.columns:
+ exprs.append(col.get_ddl())
- out.write(" ")
- out.write(",\n ".join(exprs))
+ for constraint in self.constraints:
+ exprs.append(constraint.get_ddl())
+
+ exprs = ["\n %s" % x.strip() for x in exprs]
+
+ out.write(",".join(exprs))
+
out.write("\n )\n")
class SqlColumn(object):
- def __init__(self, table, name):
+ def __init__(self, table, name, type):
self.table = table
self.name = name
+ self.type = type
self.table.columns.append(self)
self.table.columns_by_name[self.name] = self
+ self.nullable = False
+ self.foreign_key_column = None
+
+ def get_ddl(self):
+ tokens = list()
+
+ tokens.append("\"%s\"" % self.name)
+ tokens.append(self.type)
+
+ if not self.nullable:
+ tokens.append("not null")
+
+ if self.foreign_key_column:
+ table = self.foreign_key_column.table.name
+ col = self.foreign_key_column.name
+ expr = "references \"%s\"(\"%s\")"
+
+ tokens.append(expr % (table, col))
+
+ return " ".join(tokens)
+
+class SqlTableConstraint(object):
+ def __init__(self, table, name, columns):
+ self.table = table
+ self.name = name
+ self.columns = columns
+
+ self.table.constraints.append(self)
+ self.table.constraints_by_name[self.name] = name
+
+class SqlPrimaryKeyConstraint(SqlTableConstraint):
+ def get_ddl(self):
+ cols = "\"%s\"" % "\", \"".join([x.name
for x in self.columns])
+
+ return "constraint \"%s\" primary key (%s)" % (self.name,
cols)
+
+class SqlUniqueConstraint(SqlTableConstraint):
+ def get_ddl(self):
+ cols = "\"%s\"" % "\", \"".join([x.name
for x in self.columns])
+
+ return "constraint \"%s\" unique (%s)" % (self.name, cols)
+
+class SqlIndex(object):
+ def __init__(self, schema, name, columns):
+ assert len(set([x.table for x in columns])) == 1
+
+ self.schema = schema
+ self.name = name
+ self.columns = columns
+
+ self.schema.indexes.append(self)
+ self.schema.indexes_by_name[self.name] = self
+
+ def write_create_ddl(self, out):
+ cols = "\"%s\"" % "\", \"".join([x.name
for x in self.columns])
+ args = (self.name, self.columns[0].table.name, cols)
+
+ out.write(" create index \"%s\" on \"%s\"(%s)\n"
% args)
+
class SqlRelation(object):
def __init__(self, model, name, columns):
self.name = name
Added: mgmt/trunk/rosemary/python/rosemary/sqloperation.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/sqloperation.py (rev 0)
+++ mgmt/trunk/rosemary/python/rosemary/sqloperation.py 2010-02-05 15:53:42 UTC (rev
3842)
@@ -0,0 +1,41 @@
+from sqlmodel import *
+from util import *
+
+class SqlOperation(object):
+ def __init__(self, table):
+ self.table = table
+
+ def execute(self, cursor, columns, values):
+ text = self.emit(names)
+ results = cursor.execute(text, values)
+ return results
+
+class SqlSelectItem(SqlOperation):
+ def emit(self, columns):
+ cols = ", ".join(["\"%s\"" % x.name for x in
columns])
+ args = (cols, self.table.schema.name, self.table.name)
+
+ return "select %s from \"%s\".\"%s\" where id =
%%(id)" % args
+
+class SqlInsertItem(SqlOperation):
+ def emit(self, columns):
+ names = [x.name for x in columns]
+ cols = ", ".join(["\"%s\"" % x for x in names])
+ vals = ", ".join(["%%(%s)" % x for x in names])
+ args = (self.table.schema.name, self.table.name, cols, vals)
+
+ return "insert into \"%s\".\"%s\" (%s) values (%s)"
% args
+
+class SqlUpdateItem(SqlOperation):
+ def emit(self, columns):
+ exprs = ["\"%s\" = %%(%s)" % (x.name, x.name) for x in
columns]
+ exprs = ", ".join(exprs)
+ args = (self.table.schema.name, self.table.name, exprs)
+
+ return "update \"%s\".\"%s\" set %s where id =
%%(id)" % args
+
+class SqlDeleteItem(SqlOperation):
+ def emit(self, columns):
+ args = (self.table.schema.name, self.table.name)
+
+ return "delete from \"%s\".\"%s\" where id =
%%(id)" % args
Added: mgmt/trunk/rosemary/python/rosemary/types.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/types.py (rev 0)
+++ mgmt/trunk/rosemary/python/rosemary/types.py 2010-02-05 15:53:42 UTC (rev 3842)
@@ -0,0 +1,33 @@
+from util import *
+
+sql_types_by_qmf_type = {
+ "int8": "int2",
+ "int16": "int2",
+ "int32": "int4",
+ "int64": "int8",
+ "uint8": "int2",
+ "uint16": "int4",
+ "uint32": "int8",
+ "uint64": "numeric(19)",
+ "count8": "int2",
+ "count16": "int4",
+ "count32": "int8",
+ "count64": "numeric(19)",
+ "hilo8": "int2",
+ "hilo16": "int4",
+ "hilo32": "int8",
+ "hilo64": "numeric(19)",
+ "mma32": "int8",
+ "mma64": "numeric(19)",
+ "float": "float4",
+ "double": "float8",
+ "bool": "bool",
+ "sstr": "text",
+ "lstr": "text",
+ "absTime": "timestamp",
+ "deltaTime": "numeric(19)",
+ "mmaTime": "numeric(19)",
+ "map": "text",
+ "objId": "text",
+ "uuid": "text",
+ }
Modified: mgmt/trunk/rosemary/python/rosemary/util.py
===================================================================
--- mgmt/trunk/rosemary/python/rosemary/util.py 2010-02-05 11:17:08 UTC (rev 3841)
+++ mgmt/trunk/rosemary/python/rosemary/util.py 2010-02-05 15:53:42 UTC (rev 3842)
@@ -1,3 +1,12 @@
+import logging
+import os
+import sys
+
+try:
+ from xml.etree.ElementTree import *
+except ImportError:
+ from elementtree.ElementTree import *
+
def unstudly(name):
chars = list()