[rhmessaging-commits] rhmessaging commits: r2912 - in mgmt/trunk/mint: python/mint and 1 other directories.

Author: nunofsantos Date: 2008-12-02 19:10:28 -0500 (Tue, 02 Dec 2008) New Revision: 2912 Added: mgmt/trunk/mint/sql/triggers.sql Modified: mgmt/trunk/mint/Makefile mgmt/trunk/mint/python/mint/Makefile mgmt/trunk/mint/python/mint/__init__.py mgmt/trunk/mint/python/mint/schemaparser.py mgmt/trunk/mint/sql/Makefile Log: automatically generate SQL triggers to update foreign keys to statistics tables on parent tables, upon insertion of statistics records Modified: mgmt/trunk/mint/Makefile =================================================================== --- mgmt/trunk/mint/Makefile 2008-12-02 22:58:08 UTC (rev 2911) +++ mgmt/trunk/mint/Makefile 2008-12-03 00:10:28 UTC (rev 2912) @@ -2,8 +2,6 @@ include ../etc/Makefile.common -dsn := "postgresql://localhost/" - name := mint lib := ${PYTHON_LIB_DIR}/${name} Modified: mgmt/trunk/mint/python/mint/Makefile =================================================================== --- mgmt/trunk/mint/python/mint/Makefile 2008-12-02 22:58:08 UTC (rev 2911) +++ mgmt/trunk/mint/python/mint/Makefile 2008-12-03 00:10:28 UTC (rev 2912) @@ -1,10 +1,9 @@ .PHONY: schema clean -dsn := "postgresql://localhost/" - schema: schema.py schema.py: schemaparser.py ../../xml/*.xml - python schemaparser.py schema.py ${dsn} ../../xml/*.xml + python schemaparser.py schema.py ../../sql/triggers.sql ../../xml/*.xml clean: + rm -f schema.py ../../sql/triggers.sql Modified: mgmt/trunk/mint/python/mint/__init__.py =================================================================== --- mgmt/trunk/mint/python/mint/__init__.py 2008-12-02 22:58:08 UTC (rev 2911) +++ mgmt/trunk/mint/python/mint/__init__.py 2008-12-03 00:10:28 UTC (rev 2912) @@ -94,6 +94,7 @@ pass for path, text in scripts: + # TODO: fix splitting of sql statements by ';' before enabling triggers stmts = text.split(";") count = 0 Modified: mgmt/trunk/mint/python/mint/schemaparser.py =================================================================== --- mgmt/trunk/mint/python/mint/schemaparser.py 2008-12-02 22:58:08 UTC (rev 2911) +++ mgmt/trunk/mint/python/mint/schemaparser.py 2008-12-03 00:10:28 UTC (rev 2912) @@ -4,15 +4,16 @@ class SchemaParser: """parses broker XML schema""" - def __init__(self, pythonFilePath, dsn, xmlFilePaths): + def __init__(self, pythonFilePath, sqlTriggersFilePath, xmlFilePaths): self.pythonFilePath = pythonFilePath - self.dsn = dsn + self.sqlTriggersFilePath = sqlTriggersFilePath self.xmlFilePaths = xmlFilePaths self.style = MixedCaseUnderscoreStyle() self.additionalPythonOutput = "" self.currentClass = "" self.pythonOutput = "" self.finalPythonOutput = "" + self.sqlTriggersOutput = "" self.entityClasses = [] self.statsClasses = [] self.groups = dict() @@ -134,6 +135,9 @@ pythonName = self.style.dbTableToPythonClass(schemaName + "_stats") colPythonName = self.style.dbColumnToPythonAttr(schemaName) keyPythonName = self.style.dbTableToPythonClass(schemaName) + self.sqlTriggersOutput += "DROP TRIGGER update_stats ON %s; \n" % (self.style.pythonClassToDBTable(pythonName)) + self.sqlTriggersOutput += "CREATE TRIGGER update_stats AFTER INSERT ON %s \n" % (self.style.pythonClassToDBTable(pythonName)) + self.sqlTriggersOutput += " FOR EACH ROW EXECUTE PROCEDURE update_stats(); \n\n" else: pythonName = self.style.dbTableToPythonClass(schemaName) statsPythonName = self.style.dbTableToPythonClass(schemaName + "_stats") @@ -208,7 +212,27 @@ self.finalPythonOutput += ' "connectionRef": "clientConnectionRef", \n' self.finalPythonOutput += ' "user": "gridUser", "User": "GridUser", \n' self.finalPythonOutput += ' "registeredTo": "broker"} \n\n' + + # TODO: optimize getting the id to the parent table from new.parent_table_id + self.sqlTriggersOutput += """ +CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' +DECLARE + parent_table varchar; + update_parent varchar; +BEGIN + -- remove "_stats" suffix from table name + parent_table := substr(tg_table_name, 0, char_length(tg_table_name) - 5); + update_parent := ''UPDATE '' || parent_table || + '' SET stats_prev_id = stats_curr_id, stats_curr_id = '' || new.id || + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_table_name || '' WHERE id = '' || new.id || '')''; + EXECUTE update_parent; + RETURN new; +END +' LANGUAGE plpgsql; + +""" outputFile = open(self.pythonFilePath, "w") + sqlTriggersFile = open(self.sqlTriggersFilePath, "w") for xmlFile in self.xmlFilePaths: schema = mllib.xml_parse(xmlFile) # parse groups and store their structure as is @@ -239,12 +263,14 @@ self.finalPythonOutput += "\nstatsClasses = %s\n" % (self.statsClasses) outputFile.write(self.pythonOutput + self.finalPythonOutput) outputFile.close() + sqlTriggersFile.write(self.sqlTriggersOutput) + sqlTriggersFile.close() if __name__ == "__main__": import sys if len(sys.argv) < 3: - print "Usage: schemaparser.py OUTPUT-PYTHON-FILE DSN INPUT-XML-SCHEMA [INPUT-XML-SCHEMA]*" + print "Usage: schemaparser.py OUTPUT-PYTHON-FILE OUTPUT-SQL-TRIGGERS-FILE INPUT-XML-SCHEMA [INPUT-XML-SCHEMA]*" sys.exit(1) else: parser = SchemaParser(sys.argv[1], sys.argv[2], sys.argv[3:]) Modified: mgmt/trunk/mint/sql/Makefile =================================================================== --- mgmt/trunk/mint/sql/Makefile 2008-12-02 22:58:08 UTC (rev 2911) +++ mgmt/trunk/mint/sql/Makefile 2008-12-03 00:10:28 UTC (rev 2912) @@ -8,3 +8,4 @@ sqlobject-admin sql -m mint -m mint.schema -c ${dsn} | sed -e '1,2d' > schema.sql clean: + rm -f schema.sql Added: mgmt/trunk/mint/sql/triggers.sql =================================================================== --- mgmt/trunk/mint/sql/triggers.sql (rev 0) +++ mgmt/trunk/mint/sql/triggers.sql 2008-12-03 00:10:28 UTC (rev 2912) @@ -0,0 +1,108 @@ + +CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' +DECLARE + parent_table varchar; + update_parent varchar; +BEGIN + -- remove "_stats" suffix from table name + parent_table := substr(tg_table_name, 0, char_length(tg_table_name) - 5); + update_parent := ''UPDATE '' || parent_table || + '' SET stats_prev_id = stats_curr_id, stats_curr_id = '' || new.id || + '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_table_name || '' WHERE id = '' || new.id || '')''; + EXECUTE update_parent; + RETURN new; +END +' LANGUAGE plpgsql; + +DROP TRIGGER update_stats ON slot_stats; +CREATE TRIGGER update_stats AFTER INSERT ON slot_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON job_stats; +CREATE TRIGGER update_stats AFTER INSERT ON job_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON scheduler_stats; +CREATE TRIGGER update_stats AFTER INSERT ON scheduler_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON submitter_stats; +CREATE TRIGGER update_stats AFTER INSERT ON submitter_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON negotiator_stats; +CREATE TRIGGER update_stats AFTER INSERT ON negotiator_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON collector_stats; +CREATE TRIGGER update_stats AFTER INSERT ON collector_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON master_stats; +CREATE TRIGGER update_stats AFTER INSERT ON master_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON acl_stats; +CREATE TRIGGER update_stats AFTER INSERT ON acl_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON cluster_stats; +CREATE TRIGGER update_stats AFTER INSERT ON cluster_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON store_stats; +CREATE TRIGGER update_stats AFTER INSERT ON store_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON journal_stats; +CREATE TRIGGER update_stats AFTER INSERT ON journal_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON system_stats; +CREATE TRIGGER update_stats AFTER INSERT ON system_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON broker_stats; +CREATE TRIGGER update_stats AFTER INSERT ON broker_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON agent_stats; +CREATE TRIGGER update_stats AFTER INSERT ON agent_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON vhost_stats; +CREATE TRIGGER update_stats AFTER INSERT ON vhost_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON queue_stats; +CREATE TRIGGER update_stats AFTER INSERT ON queue_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON exchange_stats; +CREATE TRIGGER update_stats AFTER INSERT ON exchange_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON binding_stats; +CREATE TRIGGER update_stats AFTER INSERT ON binding_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON client_connection_stats; +CREATE TRIGGER update_stats AFTER INSERT ON client_connection_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON link_stats; +CREATE TRIGGER update_stats AFTER INSERT ON link_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON bridge_stats; +CREATE TRIGGER update_stats AFTER INSERT ON bridge_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON session_stats; +CREATE TRIGGER update_stats AFTER INSERT ON session_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); + +DROP TRIGGER update_stats ON sysimage_stats; +CREATE TRIGGER update_stats AFTER INSERT ON sysimage_stats + FOR EACH ROW EXECUTE PROCEDURE update_stats(); +