Author: justi9 Date: 2010-02-05 15:08:12 -0500 (Fri, 05 Feb 2010) New Revision: 3844 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/sqloperation.py Log: * Separate modeling of references * Name internal and qmf metadata columns in the data to avoid collisions with primary qmf data * Reorganize how columns are added * Store a sql identifier literal with sql objects * Add the first part of a flexible query operation Modified: mgmt/trunk/rosemary/bin/rosemary-test =================================================================== --- mgmt/trunk/rosemary/bin/rosemary-test 2010-02-05 16:05:34 UTC (rev 3843) +++ mgmt/trunk/rosemary/bin/rosemary-test 2010-02-05 20:08:12 UTC (rev 3844) @@ -45,6 +45,29 @@ print delete.emit(table.columns) print +def do_query(args): + schema = model.sql_model.schemas_by_name["org.apache.qpid.broker"] + table = schema.tables_by_name["Queue"] + stats_table = schema.tables_by_name["QueueStats"] + vhost_table = schema.tables_by_name["Vhost"] + broker_table = schema.tables_by_name["Broker"] + + query = SqlQuery(table) + + stats_col = stats_table.columns_by_name["parent_id"] + + SqlOuterJoin(query, table.id_column, stats_col) + SqlInnerJoin(query, table.id_column, vhost_table.id_column) + + broker_col = vhost_table.columns_by_name["brokerRef"] + + SqlInnerJoin(query, broker_col, broker_table.id_column) + + cols = table.columns + vhost_table.columns + broker_table.columns + \ + stats_table.columns + + print query.emit(cols) + if __name__ == "__main__": model = RosemaryModel() Modified: mgmt/trunk/rosemary/python/rosemary/model.py =================================================================== --- mgmt/trunk/rosemary/python/rosemary/model.py 2010-02-05 16:05:34 UTC (rev 3843) +++ mgmt/trunk/rosemary/python/rosemary/model.py 2010-02-05 20:08:12 UTC (rev 3844) @@ -79,6 +79,9 @@ self.package.classes.append(self) self.package.classes_by_name[self.name] = self + self.references = list() + self.references_by_name = dict() + self.properties = list() self.properties_by_name = dict() @@ -98,51 +101,17 @@ self.sql_stats_table = SqlTable(self.package.sql_schema, name) - self.add_columns() + def load(self, elem): + for child in elem.findall("property"): + name = child.get("name") - def add_columns(self): - id_col = SqlColumn(self.sql_table, "id", "serial") + if child.get("references"): + attr = RosemaryReference(self, name) + else: + attr = RosemaryProperty(self, name) - name = "%s_pk" % self.name + attr.load(child) - 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")) - prop.load(child) - for child in elem.findall("statistic"): stat = RosemaryStatistic(self, child.get("name")) stat.load(child) @@ -165,6 +134,13 @@ meth.extend(child) def init(self): + self.add_id_columns() + + for ref in self.references: + ref.init() + + self.add_qmf_columns() + for prop in self.properties: prop.init() @@ -174,6 +150,40 @@ for meth in self.methods: meth.init() + def add_id_columns(self): + id_col = SqlColumn(self.sql_table, "_id", "serial") + self.sql_table.key_column = id_col + + name = "%s_pk" % self.name + SqlPrimaryKeyConstraint(self.sql_table, name, (id_col,)) + + stats_id_col = SqlColumn(self.sql_stats_table, "_id", "serial") + self.sql_stats_table.key_column = stats_id_col + + 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 + + def add_qmf_columns(self): + 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 + + update_col = SqlColumn \ + (self.sql_stats_table, "_qmf_update_time", "timestamp") + def load_object(self, cursor, id): self.sql_select.execute(cursor, self.sql_table.columns, {"id": id}) @@ -194,21 +204,19 @@ self.sql_delete.execute(cursor, (), {"id": object.id}) -class RosemaryProperty(object): +class RosemaryAttribute(object): def __init__(self, cls, name): self.cls = cls self.name = name - self.cls.properties.append(self) - self.cls.properties_by_name[self.name] = self - - self.title = None self.type = None self.references = None self.access = None self.unit = None self.is_index = None self.is_optional = None + + self.title = None self.description = None self.sql_column = None @@ -225,46 +233,54 @@ def extend(self, elem): self.title = elem.findtext("title") +class RosemaryReference(RosemaryAttribute): + def __init__(self, cls, name): + super(RosemaryReference, self).__init__(cls, name) + + self.cls.references.append(self) + self.cls.references_by_name[self.name] = self + def init(self): - type = sql_types_by_qmf_type[self.type] + assert self.references - self.sql_column = SqlColumn(self.cls.sql_table, self.name, type) + try: + that_cls = self.cls.package.classes_by_name[self.references] + except KeyError: + log.warn("Reference to '%s' invalid", self.references) + + return + + name = "_%s_id" % self.name + + self.sql_column = SqlColumn(self.cls.sql_table, name, "int4") + self.sql_column.foreign_key_column = that_cls.sql_table.key_column self.sql_column.nullable = self.is_optional + +class RosemaryProperty(RosemaryAttribute): + def __init__(self, cls, name): + super(RosemaryProperty, self).__init__(cls, name) - if self.references: - try: - cls = self.cls.package.classes_by_name[self.references] - except KeyError: - log.warn("Reference to '%s' invalid", self.references) - return + self.cls.properties.append(self) + self.cls.properties_by_name[self.name] = self - col = cls.sql_table.columns_by_name["id"] + def init(self): + assert not self.references - self.sql_column.foreign_key_column = col + type = sql_types_by_qmf_type[self.type] -class RosemaryStatistic(object): + self.sql_column = SqlColumn(self.cls.sql_table, self.name, type) + self.sql_column.nullable = self.is_optional + +class RosemaryStatistic(RosemaryAttribute): def __init__(self, cls, name): - self.cls = cls + super(RosemaryStatistic, self).__init__(cls, name) - self.name = name - self.type = None - self.unit = None - self.description = None - self.cls.statistics.append(self) self.cls.statistics_by_name[self.name] = self - self.sql_column = None + def init(self): + assert not self.references - def load(self, elem): - self.type = elem.get("type") - self.unit = elem.get("unit") - self.description = elem.get("desc") - - def extend(self, elem): - pass - - def init(self): type = sql_types_by_qmf_type[self.type] self.sql_column = SqlColumn(self.cls.sql_stats_table, self.name, type) Modified: mgmt/trunk/rosemary/python/rosemary/sqlmodel.py =================================================================== --- mgmt/trunk/rosemary/python/rosemary/sqlmodel.py 2010-02-05 16:05:34 UTC (rev 3843) +++ mgmt/trunk/rosemary/python/rosemary/sqlmodel.py 2010-02-05 20:08:12 UTC (rev 3844) @@ -24,6 +24,8 @@ self.model.schemas.append(self) self.model.schemas_by_name[self.name] = self + self.identifier = "\"%s\"" % self.name + self.tables = list() self.tables_by_name = dict() @@ -31,7 +33,7 @@ self.indexes_by_name = dict() def write_create_ddl(self, out): - out.write("create schema \"%s\"\n" % self.name) + out.write("create schema %s\n" % self.identifier) for table in self.tables: table.write_create_ddl(out) @@ -42,7 +44,7 @@ out.write(" ;\n") def write_drop_ddl(self, out): - out.write("drop schema \"%s\" cascade;\n" % self.name) + out.write("drop schema %s cascade;\n" % self.identifier) class SqlTable(object): def __init__(self, schema, name): @@ -52,6 +54,9 @@ self.schema.tables.append(self) self.schema.tables_by_name[self.name] = self + self.identifier = "%s.\"%s\"" % (self.schema.identifier, self.name) + self.key_column = None + self.columns = list() self.columns_by_name = dict() @@ -84,6 +89,8 @@ self.table.columns.append(self) self.table.columns_by_name[self.name] = self + self.identifier = "\"%s\".\"%s\"" % (self.table.name, self.name) + self.nullable = False self.foreign_key_column = None Modified: mgmt/trunk/rosemary/python/rosemary/sqloperation.py =================================================================== --- mgmt/trunk/rosemary/python/rosemary/sqloperation.py 2010-02-05 16:05:34 UTC (rev 3843) +++ mgmt/trunk/rosemary/python/rosemary/sqloperation.py 2010-02-05 20:08:12 UTC (rev 3844) @@ -12,30 +12,75 @@ 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) + cols = ", ".join([x.identifier for x in columns]) + args = (cols, self.table.identifier) - return "select %s from \"%s\".\"%s\" where id = %%(id)" % args + return "select %s from %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) + args = (self.table.identifier, cols, vals) - return "insert into \"%s\".\"%s\" (%s) values (%s)" % args + return "insert into %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) + args = (self.table.identifier, exprs) - return "update \"%s\".\"%s\" set %s where id = %%(id)" % args + return "update %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 where id = %%(id)" % self.table.identifier + +class SqlQuery(SqlOperation): + def __init__(self, table): + super(SqlQuery, self).__init__(table) + + self.joins = list() + + def emit(self, columns, parameters=None): + tokens = list() + + cols = ",".join(["\n %s" % x.identifier for x in columns]) + tokens.append("select %s" % cols) + + tokens.append("from %s" % self.table.identifier) + + for join in self.joins: + tokens.append(join.emit()) + + return "%s\n" % "\n".join(tokens) - return "delete from \"%s\".\"%s\" where id = %%(id)" % args +class SqlQueryJoin(object): + def __init__(self, query, this_column, that_column): + self.query = query + self.this_column = this_column + self.that_column = that_column + + assert self not in self.query.joins + + self.query.joins.append(self) + +class SqlInnerJoin(SqlQueryJoin): + def emit(self): + table = self.that_column.table.identifier + this = self.this_column.identifier + that = self.that_column.identifier + args = (table, this, that) + + return "inner join %s on %s = %s" % args + +class SqlOuterJoin(SqlQueryJoin): + def emit(self): + table = self.that_column.table.identifier + this = self.this_column.identifier + that = self.that_column.identifier + args = (table, this, that) + + return "left outer join %s on %s = %s" % args