Once I have a list of the hostprobe row IDs, I need to find out the sensor name from the sensor table. This entry is referenced from the probe table, which contains parameters specific to this particular sensor. The probe is directly referenced from the host probe entry. XML-RPC server information is in the host table, which is also referenced directly from the host probe table. I now need to combine all that data into a single SQL statement. The simplest way is to use implicit join notation, which lists all fields that need to match. Because I'm using primary key fields when referencing the rows, it is a matter of comparing them in the select statement.
In Listing 9-11 you see the part of the ticket dispatcher code that uses the previously generated list of pending tickets. The for loop will iterate through all ticket ID - hostprobe ID pairs and get the information required to make a sensor check call. Once the call is made, the corresponding ticket is marked as dispatched, so it will not show up in the subsequent queries.
Listing 9-11. Retrieving information from multiple tables for (ticket_id, hostprobe_id) in pending_tickets:
res = [r for r in self.con.execute( SELECT host.address, host.port, sensor.name, probe.parameter FROM hostprobe, host, probe, sensor WHERE hostprobe.id=?
AND hostprobe.host_id = host.id AND hostprobe.probe_id = probe.id
AND probe.sensor_id = sensor.id ,
(hostprobe_id,) )] self._send_request(ticket_id, res, res, res, res)
self.con.execute("UPDATE ticketqueue SET dispatched=1 WHERE id=?", (ticket_id,)) self.con.commit()
The comparison operations in the WHERE clause effectively performs a join on the table data, so that only matching records are returned as a result. Obviously, the starting point is the host probe row ID. You may also notice that I call another function, self._send_request, which performs the XML-RPC call to the remote system. The code (Listing 9-12) is pretty self-explanatory; there is just one thing to note: the parameter string is a comma-separated list when stored in the database, and it needs to be converted into an array before it is sent tothe remote client.
Listing 9-12. The function that sends an XML-RPC call to the client nodes def _send_request(self, ticket, address, port, sensor, parameter_string=None): url = "http://%s:%s/xmlrpc/" % (address, port) proxy = xmlrpclib.ServerProxy(url, allow_none=True) if parameter_string:
parameter = parameter_string.split(',') else:
parameter = None print ticket print sensor print parameter res = proxy.cmd_submit_reading(ticket, sensor, parameter) return
Was this article helpful?