Author: justi9 Date: 2009-02-06 11:28:59 -0500 (Fri, 06 Feb 2009) New Revision: 3110 Modified: mgmt/trunk/mint/python/mint/schema.py mgmt/trunk/mint/python/mint/schemaparser.py mgmt/trunk/mint/sql/triggers.sql Log: Nuno's patch to simplify triggers, and a new schema Modified: mgmt/trunk/mint/python/mint/schema.py =================================================================== --- mgmt/trunk/mint/python/mint/schema.py 2009-02-05 18:53:07 UTC (rev 3109) +++ mgmt/trunk/mint/python/mint/schema.py 2009-02-06 16:28:59 UTC (rev 3110) @@ -1353,7 +1353,8 @@ "connection": "clientConnection", "Connection": "ClientConnection", "connectionRef": "clientConnectionRef", "user": "gridUser", "User": "GridUser", - "registeredTo": "broker"} + "registeredTo": "broker", + "sync": "syncRsv"} classToSchemaNameMap['Slot'] = 'Slot' schemaNameToClassMap['Slot'] = Slot Modified: mgmt/trunk/mint/python/mint/schemaparser.py =================================================================== --- mgmt/trunk/mint/python/mint/schemaparser.py 2009-02-05 18:53:07 UTC (rev 3109) +++ mgmt/trunk/mint/python/mint/schemaparser.py 2009-02-06 16:28:59 UTC (rev 3110) @@ -40,7 +40,8 @@ "connection": "clientConnection", "Connection": "ClientConnection", "connectionRef": "clientConnectionRef", "user": "gridUser", "User": "GridUser", - "registeredTo": "broker"} + "registeredTo": "broker", + "sync": "syncRsv"} def renameReservedWord(self, name): if (name in self.reservedWords.keys()): @@ -141,8 +142,11 @@ colPythonName = self.style.dbColumnToPythonAttr(schemaName) keyPythonName = self.style.dbTableToPythonClass(schemaName) sqlTable = self.style.pythonClassToDBTable(pythonName) - self.sqlTriggersOutput += "CREATE TRIGGER update_stats AFTER INSERT ON %s \n" % (sqlTable) - self.sqlTriggersOutput += " FOR EACH ROW EXECUTE PROCEDURE update_stats(); \n\n" + sqlParentTable = self.style.pythonClassToDBTable(self.style.dbTableToPythonClass(schemaName)) + self.sqlTriggersOutput += self.sqlTriggerFunction % (sqlParentTable, sqlParentTable, sqlParentTable) + self.sqlTriggersOutput += "\n" + self.sqlTriggersOutput += "CREATE TRIGGER update_%s_stats AFTER INSERT ON %s \n" % (sqlParentTable, sqlTable) + self.sqlTriggersOutput += " FOR EACH ROW EXECUTE PROCEDURE update_%s_stats(); \n\n" % (sqlParentTable) self.sqlTriggersOutput += "CREATE INDEX %s_update_time ON %s (qmf_update_time);\n\n" % (sqlTable, sqlTable) else: pythonName = self.style.dbTableToPythonClass(schemaName) @@ -218,7 +222,8 @@ self.finalPythonOutput += ' "connection": "clientConnection", "Connection": "ClientConnection", \n' self.finalPythonOutput += ' "connectionRef": "clientConnectionRef", \n' self.finalPythonOutput += ' "user": "gridUser", "User": "GridUser", \n' - self.finalPythonOutput += ' "registeredTo": "broker"} \n\n' + self.finalPythonOutput += ' "registeredTo": "broker",\n' + self.finalPythonOutput += ' "sync": "syncRsv"} \n\n' # TODO: optimize getting the id to the parent table from new.parent_table_id self.sqlTriggersOutput += """ @@ -239,30 +244,16 @@ DROP FUNCTION create_plpgsql(); -CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' -DECLARE - parent_table varchar; - select_parent varchar; - update_parent varchar; -BEGIN - -- remove "_stats" suffix from table name - parent_table := substr(tg_relname, 0, char_length(tg_relname) - 5); - - select_parent := ''SELECT id FROM '' || parent_table || - '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || - new.id || '') FOR UPDATE''; - - 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_relname || '' WHERE id = '' || - new.id || '')''; - EXECUTE select_parent; - EXECUTE update_parent; - RETURN new; -END -' LANGUAGE plpgsql; - """ + self.sqlTriggerFunction = """ +CREATE OR REPLACE FUNCTION update_%s_stats() RETURNS trigger AS ' +BEGIN + UPDATE %s SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.%s_id; + RETURN new; +END +' LANGUAGE plpgsql; +""" + outputFile = open(self.pythonFilePath, "w") sqlTriggersFile = open(self.sqlTriggersFilePath, "w") for xmlFile in self.xmlFilePaths: Modified: mgmt/trunk/mint/sql/triggers.sql =================================================================== --- mgmt/trunk/mint/sql/triggers.sql 2009-02-05 18:53:07 UTC (rev 3109) +++ mgmt/trunk/mint/sql/triggers.sql 2009-02-06 16:28:59 UTC (rev 3110) @@ -16,141 +16,302 @@ DROP FUNCTION create_plpgsql(); -CREATE OR REPLACE FUNCTION update_stats() RETURNS trigger AS ' -DECLARE - parent_table varchar; - select_parent varchar; - update_parent varchar; -BEGIN - -- remove "_stats" suffix from table name - parent_table := substr(tg_relname, 0, char_length(tg_relname) - 5); - select_parent := ''SELECT id FROM '' || parent_table || - '' WHERE id = (SELECT '' || parent_table || ''_id FROM '' || tg_relname || '' WHERE id = '' || - new.id || '') FOR UPDATE''; +CREATE OR REPLACE FUNCTION update_slot_stats() RETURNS trigger AS ' +BEGIN + UPDATE slot SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.slot_id; + RETURN new; +END +' LANGUAGE plpgsql; - 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_relname || '' WHERE id = '' || - new.id || '')''; - EXECUTE select_parent; - EXECUTE update_parent; - RETURN new; -END -' LANGUAGE plpgsql; +CREATE TRIGGER update_slot_stats AFTER INSERT ON slot_stats + FOR EACH ROW EXECUTE PROCEDURE update_slot_stats(); -CREATE TRIGGER update_stats AFTER INSERT ON slot_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); - CREATE INDEX slot_stats_update_time ON slot_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON job_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_job_stats() RETURNS trigger AS ' +BEGIN + UPDATE job SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.job_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_job_stats AFTER INSERT ON job_stats + FOR EACH ROW EXECUTE PROCEDURE update_job_stats(); + CREATE INDEX job_stats_update_time ON job_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON scheduler_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_scheduler_stats() RETURNS trigger AS ' +BEGIN + UPDATE scheduler SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.scheduler_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_scheduler_stats AFTER INSERT ON scheduler_stats + FOR EACH ROW EXECUTE PROCEDURE update_scheduler_stats(); + CREATE INDEX scheduler_stats_update_time ON scheduler_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON submitter_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_submitter_stats() RETURNS trigger AS ' +BEGIN + UPDATE submitter SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.submitter_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_submitter_stats AFTER INSERT ON submitter_stats + FOR EACH ROW EXECUTE PROCEDURE update_submitter_stats(); + CREATE INDEX submitter_stats_update_time ON submitter_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON negotiator_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_negotiator_stats() RETURNS trigger AS ' +BEGIN + UPDATE negotiator SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.negotiator_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_negotiator_stats AFTER INSERT ON negotiator_stats + FOR EACH ROW EXECUTE PROCEDURE update_negotiator_stats(); + CREATE INDEX negotiator_stats_update_time ON negotiator_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON collector_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_collector_stats() RETURNS trigger AS ' +BEGIN + UPDATE collector SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.collector_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_collector_stats AFTER INSERT ON collector_stats + FOR EACH ROW EXECUTE PROCEDURE update_collector_stats(); + CREATE INDEX collector_stats_update_time ON collector_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON master_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_master_stats() RETURNS trigger AS ' +BEGIN + UPDATE master SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.master_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_master_stats AFTER INSERT ON master_stats + FOR EACH ROW EXECUTE PROCEDURE update_master_stats(); + CREATE INDEX master_stats_update_time ON master_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON acl_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_acl_stats() RETURNS trigger AS ' +BEGIN + UPDATE acl SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.acl_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_acl_stats AFTER INSERT ON acl_stats + FOR EACH ROW EXECUTE PROCEDURE update_acl_stats(); + CREATE INDEX acl_stats_update_time ON acl_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON cluster_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_cluster_stats() RETURNS trigger AS ' +BEGIN + UPDATE cluster SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.cluster_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_cluster_stats AFTER INSERT ON cluster_stats + FOR EACH ROW EXECUTE PROCEDURE update_cluster_stats(); + CREATE INDEX cluster_stats_update_time ON cluster_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON store_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_store_stats() RETURNS trigger AS ' +BEGIN + UPDATE store SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.store_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_store_stats AFTER INSERT ON store_stats + FOR EACH ROW EXECUTE PROCEDURE update_store_stats(); + CREATE INDEX store_stats_update_time ON store_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON journal_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_journal_stats() RETURNS trigger AS ' +BEGIN + UPDATE journal SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.journal_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_journal_stats AFTER INSERT ON journal_stats + FOR EACH ROW EXECUTE PROCEDURE update_journal_stats(); + CREATE INDEX journal_stats_update_time ON journal_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON system_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_system_stats() RETURNS trigger AS ' +BEGIN + UPDATE system SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.system_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_system_stats AFTER INSERT ON system_stats + FOR EACH ROW EXECUTE PROCEDURE update_system_stats(); + CREATE INDEX system_stats_update_time ON system_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON broker_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_broker_stats() RETURNS trigger AS ' +BEGIN + UPDATE broker SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.broker_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_broker_stats AFTER INSERT ON broker_stats + FOR EACH ROW EXECUTE PROCEDURE update_broker_stats(); + CREATE INDEX broker_stats_update_time ON broker_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON agent_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_agent_stats() RETURNS trigger AS ' +BEGIN + UPDATE agent SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.agent_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_agent_stats AFTER INSERT ON agent_stats + FOR EACH ROW EXECUTE PROCEDURE update_agent_stats(); + CREATE INDEX agent_stats_update_time ON agent_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON vhost_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_vhost_stats() RETURNS trigger AS ' +BEGIN + UPDATE vhost SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.vhost_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_vhost_stats AFTER INSERT ON vhost_stats + FOR EACH ROW EXECUTE PROCEDURE update_vhost_stats(); + CREATE INDEX vhost_stats_update_time ON vhost_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON queue_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_queue_stats() RETURNS trigger AS ' +BEGIN + UPDATE queue SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.queue_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_queue_stats AFTER INSERT ON queue_stats + FOR EACH ROW EXECUTE PROCEDURE update_queue_stats(); + CREATE INDEX queue_stats_update_time ON queue_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON exchange_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_exchange_stats() RETURNS trigger AS ' +BEGIN + UPDATE exchange SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.exchange_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_exchange_stats AFTER INSERT ON exchange_stats + FOR EACH ROW EXECUTE PROCEDURE update_exchange_stats(); + CREATE INDEX exchange_stats_update_time ON exchange_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON binding_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_binding_stats() RETURNS trigger AS ' +BEGIN + UPDATE binding SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.binding_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_binding_stats AFTER INSERT ON binding_stats + FOR EACH ROW EXECUTE PROCEDURE update_binding_stats(); + CREATE INDEX binding_stats_update_time ON binding_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON client_connection_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_client_connection_stats() RETURNS trigger AS ' +BEGIN + UPDATE client_connection SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.client_connection_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_client_connection_stats AFTER INSERT ON client_connection_stats + FOR EACH ROW EXECUTE PROCEDURE update_client_connection_stats(); + CREATE INDEX client_connection_stats_update_time ON client_connection_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON link_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_link_stats() RETURNS trigger AS ' +BEGIN + UPDATE link SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.link_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_link_stats AFTER INSERT ON link_stats + FOR EACH ROW EXECUTE PROCEDURE update_link_stats(); + CREATE INDEX link_stats_update_time ON link_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON bridge_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_bridge_stats() RETURNS trigger AS ' +BEGIN + UPDATE bridge SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.bridge_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_bridge_stats AFTER INSERT ON bridge_stats + FOR EACH ROW EXECUTE PROCEDURE update_bridge_stats(); + CREATE INDEX bridge_stats_update_time ON bridge_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON session_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_session_stats() RETURNS trigger AS ' +BEGIN + UPDATE session SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.session_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_session_stats AFTER INSERT ON session_stats + FOR EACH ROW EXECUTE PROCEDURE update_session_stats(); + CREATE INDEX session_stats_update_time ON session_stats (qmf_update_time); -CREATE TRIGGER update_stats AFTER INSERT ON sysimage_stats - FOR EACH ROW EXECUTE PROCEDURE update_stats(); +CREATE OR REPLACE FUNCTION update_sysimage_stats() RETURNS trigger AS ' +BEGIN + UPDATE sysimage SET stats_prev_id = stats_curr_id, stats_curr_id = new.id WHERE id = new.sysimage_id; + RETURN new; +END +' LANGUAGE plpgsql; + +CREATE TRIGGER update_sysimage_stats AFTER INSERT ON sysimage_stats + FOR EACH ROW EXECUTE PROCEDURE update_sysimage_stats(); + CREATE INDEX sysimage_stats_update_time ON sysimage_stats (qmf_update_time);