def getReadyPostgres(connstr): pool = ConnectionPool('psycopg2', connstr) def i1(c): try: c.execute('''create table sticky ( id serial primary key, board_id text, updated timestamp default current_timestamp, note text, x integer, y integer)''') except Exception as e: log.err(e) def i2(c): try: c.execute('''create table image ( id serial primary key, board_id text, updated timestamp default current_timestamp, data bytea )''') c.execute('''create unique index image_board_id on image(board_id)''') except Exception as e: log.err(e) d = pool.runInteraction(i1) d.addCallback(lambda x: pool.runInteraction(i2)) return d.addCallbacks((lambda x:pool), log.err)
class ChannelLogger(object): implements(IDBLogger) def __init__(self, dbfile, **kw): # XXX Ignore thread warnings from sqlite3. Should be OK. # http://twistedmatrix.com/trac/ticket/3629 kw.setdefault("check_same_thread", False) from twisted.enterprise.adbapi import ConnectionPool type = 'sqlite3' self.dbfile = dbfile self.dbconn = ConnectionPool(type, dbfile, **kw) self.table = 'channels' self.initialize_db() def initialize_db(self): return self.dbconn.runInteraction(self._initialize_db, self.table) @staticmethod def _initialize_db(tx, table): tx.execute('CREATE TABLE IF NOT EXISTS {0} (' 'id INTEGER PRIMARY KEY AUTOINCREMENT,' 'timestamp INTEGER,' 'channel TEXT,' 'nick TEXT,' 'msg TEXT )'.format(table)) def log(self, who, chan, msg): return self.dbconn.runInteraction(self._log, who, chan, msg, self.table) @staticmethod def _log(tx, who, chan, msg, table): now = int(time.time()) stmt = 'INSERT INTO {0}(timestamp,nick,channel,msg) VALUES(?,?,?,?)' tx.execute(stmt.format(table), (now, who, chan, msg) )
def getReadySqlite(connstr): pool = ConnectionPool('pysqlite2.dbapi2', connstr, cp_min=1, cp_max=1) def interaction(c): try: c.execute('''create table sticky ( id integer primary key, board_id text, updated timestamp default current_timestamp, note text, x integer, y integer)''') except Exception as e: log.err(e) try: c.execute('''create table image ( id integer primary key, board_id text, updated timestamp default current_timestamp, data blob )''') c.execute('''create unique index image_board_id on image(board_id)''') except Exception as e: log.err(e) return pool.runInteraction(interaction).addCallbacks((lambda x:pool), log.err)
class ConnectionPool(object): """ Wrapper for twisted.enterprise.adbapi.ConnectionPool to use with tornado. """ def __init__(self, *args, **kwargs): self._pool = TxConnectionPool(*args, **kwargs) def run_query(self, *args, **kwargs): return self._defer_to_future(self._pool.runQuery(*args, **kwargs)) def run_operation(self, *args, **kwargs): return self._defer_to_future(self._pool.runOperation(*args, **kwargs)) def run_interaction(self, *args, **kwargs): return self._defer_to_future(self._pool.runInteraction(*args, **kwargs)) def close(self): self._pool.close() @staticmethod def _defer_to_future(defer): future = TracebackFuture() defer.addCallbacks( future.set_result, lambda failure: future.set_exc_info( (failure.type, failure.value, failure.tb))) return future
class ChannelLogger(object): implements(IDBLogger) def __init__(self, dbfile, **kw): # XXX Ignore thread warnings from sqlite3. Should be OK. # http://twistedmatrix.com/trac/ticket/3629 kw.setdefault("check_same_thread", False) from twisted.enterprise.adbapi import ConnectionPool type = 'sqlite3' self.dbfile = dbfile self.dbconn = ConnectionPool(type, dbfile, **kw) self.table = 'channels' self.initialize_db() def initialize_db(self): return self.dbconn.runInteraction(self._initialize_db, self.table) @staticmethod def _initialize_db(tx, table): tx.execute('CREATE TABLE IF NOT EXISTS {0} (' 'id INTEGER PRIMARY KEY AUTOINCREMENT,' 'timestamp INTEGER,' 'channel TEXT,' 'nick TEXT,' 'msg TEXT )'.format(table)) def log(self, who, chan, msg): return self.dbconn.runInteraction(self._log, who, chan, msg, self.table) @staticmethod def _log(tx, who, chan, msg, table): now = int(time.time()) stmt = 'INSERT INTO {0}(timestamp,nick,channel,msg) VALUES(?,?,?,?)' tx.execute(stmt.format(table), (now, who, chan, msg))
class MySQLPipeline(object): @classmethod def from_crawler(cls, crawler): return cls(crawler.stats) def __init__(self, stats): #Instantiate DB self.dbpool = ConnectionPool('MySQLdb', host=SETTINGS['DB_HOST'], user=SETTINGS['DB_USER'], passwd=SETTINGS['DB_PASSWD'], port=SETTINGS['DB_PORT'], db=SETTINGS['DB_DB'], charset='utf8', use_unicode=True, cursorclass=MySQLdb.cursors.DictCursor) self.stats = stats dispatcher.connect(self.spider_closed, signals.spider_closed) def spider_closed(self, spider): """ Cleanup function, called after crawing has finished to close open objects. Close ConnectionPool. """ self.dbpool.close() def process_item(self, item, spider): query = self.dbpool.runInteraction(self._insert_record, item) query.addErrback(self._handle_error) return item def _insert_record(self, tx, item): result = tx.execute( """INSERT INTO agregator_results (task_id, direct_link, source_link, rank, site, `date`) VALUES (%s, %s, %s, %s, %s, %s)""", ( item["django_task_id"], item["direct_link"], item["source_link"], item["rank"], item["site"], item["date"], )) if result > 0: self.stats.inc_value('database/items_added') def _handle_error(self, e): log.err(e)
class Database(): """ HouseAgent database interaction. """ def __init__(self, log, db_location): self.log = log type = "sqlite" self.coordinator = None # Note: cp_max=1 is required otherwise undefined behaviour could occur when using yield icw subsequent # runQuery or runOperation statements if type == "sqlite": self.dbpool = ConnectionPool("sqlite3", db_location, check_same_thread=False, cp_max=1) # Check database schema version and upgrade when required self.updatedb('0.1') def updatedb(self, dbversion): ''' Perform a database schema update when required. ''' # Note: runInteraction runs all queries defined within the specified function as part of a transaction. return self.dbpool.runInteraction(self._updatedb, dbversion) def _updatedb(self, txn, dbversion): ''' Check whether a database schema update is required and act accordingly. ''' # Note: Although all queries are run as part of a transaction, a create or drop table statement result in an implicit commit # Query the version of the current schema try: result = txn.execute("SELECT parm_value FROM common WHERE parm = 'schema_version'").fetchall() except: result = None if result: version = result[0][0] else: version = '0.0' if float(version) > float(dbversion): self.log.error("ERROR: The current database schema (%s) is not supported by this version of HouseAgent" % version) # Exit HouseAgent sys.exit(1) elif float(version) == float(dbversion): self.log.debug("Database schema is up to date") return else: self.log.info("Database schema will be updated from %s to %s:" % (version, dbversion)) # Before we start manipulating the database schema, first make a backup copy of the database try: shutil.copy(db_location, db_location + datetime.datetime.strftime(datetime.datetime.now(), ".%y%m%d-%H%M%S")) except: self.log.error("Cannot make a backup copy of the database (%s)", sys.exc_info()[1]) return if version == '0.0': try: # Create common table txn.execute("CREATE TABLE IF NOT EXISTS common (parm VARCHAR(16) PRIMARY KEY, parm_value VARCHAR(24) NOT NULL)") # Add schema version to database txn.execute("INSERT INTO common (parm, parm_value) VALUES ('schema_version', ?)", [dbversion]) # Set primary key of the devices table on address + plugin_id to prevent adding duplicate devices txn.execute("CREATE TEMPORARY TABLE devices_backup(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER NOT NULL, location_id INTEGER)") txn.execute("INSERT INTO devices_backup SELECT id, name, address, plugin_id, location_id FROM devices") txn.execute("DROP TABLE devices") txn.execute("CREATE TABLE devices(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER, location_id INTEGER)") txn.execute("CREATE UNIQUE INDEX device_address ON devices (address, plugin_id)") txn.execute("INSERT INTO devices SELECT id, name, address, plugin_id, location_id FROM devices_backup") txn.execute("DROP TABLE devices_backup") self.log.info("Successfully upgraded database schema") except: self.log.error("Database schema upgrade failed (%s)" % sys.exc_info()[1]) def query_plugin_auth(self, authcode): return self.dbpool.runQuery("SELECT authcode, id from plugins WHERE authcode = '%s'" % authcode) def check_plugin_auth(self, result): if len(result) >= 1: return {'registered': True} else: return {'registered': False} def insert_result(self, result): return {'received': True} def add_event(self, name, enabled, triggers): """ This function adds an event to the database. """ d = self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", (name, enabled) ) def event_added(result): print "added event" return self.dbpool.runQuery("select id from events order by id desc limit 1") d.addCallback(event_added) def got_id(result): event_id = result[0][0] print "got event_id", result[0][0] print "triggers=",triggers # Add triggers deferredlist = [] for trigger in triggers: trigger_type_id = trigger["trigger_type"] print "trigger", trigger def got_triggerid(result): trigger_id = result[0][0] print "parameters", trigger["parameters"] for name, value in trigger["parameters"].iteritems(): print name, value deferredlist.append(self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, " + "triggers_id) VALUES (?, ?, ?)", (name, value, trigger_id))) def trigger_added(result): self.dbpool.runQuery("select id from triggers order by id desc limit 1").addCallback(got_triggerid) # Triggers deferredlist.append(self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id)" + " VALUES (?, ?)", (trigger_type_id, event_id)).addCallback(trigger_added) ) d = defer.gatherResults(deferredlist) return d d.addCallback(got_id) def added_triggers(result): print "triggers added" d.addCallback(added_triggers) return d def add_location(self, name, parent): if parent: return self.dbpool.runQuery("INSERT INTO locations (name, parent) VALUES (?, ?)", [name, parent]) else: return self.dbpool.runQuery("INSERT INTO locations (name) VALUES (?)", [name]) @inlineCallbacks def add_event2(self, name, enabled, conditions, actions, trigger): ''' This adds an event to the database. ''' # Add event, and get event id yield self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", [name, enabled]) eventid = yield self.dbpool.runQuery("select id from events order by id desc limit 1") eventid = eventid[0][0] # Add conditions for condition in conditions: condition_type_id = condition["condition_type"] yield self.dbpool.runQuery("INSERT INTO conditions (condition_types_id, events_id)" + " VALUES (?, ?)", [condition_type_id, eventid]) condition_id = yield self.dbpool.runQuery("select id from conditions order by id desc limit 1") condition_id = condition_id[0][0] for name, value in condition["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO condition_parameters (name, value, " + "conditions_id) VALUES (?, ?, ?)", [name, value, condition_id]) # Add actions for action in actions: action_type_id = action["action_type"] yield self.dbpool.runQuery("INSERT INTO actions (action_types_id, events_id)" + " VALUES (?, ?)", [action_type_id, eventid]) action_id = yield self.dbpool.runQuery("select id from actions order by id desc limit 1") action_id = action_id[0][0] for name, value in action["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO action_parameters (name, value, " + "actions_id) VALUES (?, ?, ?)", [name, value, action_id]) # Insert trigger yield self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id, conditions)" + " VALUES (?,?,?)", [trigger["trigger_type"], eventid, trigger["conditions"]]) trigger_id = yield self.dbpool.runQuery("select id from triggers order by id desc limit 1") trigger_id = trigger_id[0][0] for name, value in trigger["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, " + "triggers_id) VALUES (?, ?, ?)", [name, value, trigger_id]) def add_trigger(self, trigger_type_id, event_id, value_id, parameters): print "INSERT INTO triggers (trigger_types_id, events_id, current_values_id) VALUES (%d, %d, %d)" % (int(trigger_type_id), int(event_id), int(value_id)) d = self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id" + ", current_values_id) VALUES (%s, %s, %s)", (int(trigger_type_id), int(event_id), int(value_id)) ) for name, value in parameters.iteritems(): self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, triggers_id) VALUES (%s, %s, last_insert_id())", (name, value) ) return d #def add_action(self, action_type_id, event_id): def query_latest_device_id(self): ''' This function queries the latest device id. ''' return self.dbpool.runQuery('select id from devices LIMIT 1') def query_triggers(self): return self.dbpool.runQuery("SELECT triggers.id, trigger_types.name, triggers.events_id, triggers.conditions " + "FROM triggers INNER JOIN trigger_types ON (triggers.trigger_types_id = trigger_types.id)") def query_trigger(self, event_id): return self.dbpool.runQuery("SELECT triggers.id, trigger_types.name, triggers.events_id, triggers.conditions " + "FROM triggers INNER JOIN trigger_types ON (triggers.trigger_types_id = trigger_types.id) " + "WHERE triggers.events_id = ? LIMIT 1", [event_id]) def query_conditions(self): return self.dbpool.runQuery("SELECT conditions.id, condition_types.name, conditions.events_id " + "FROM conditions INNER JOIN condition_types ON (conditions.condition_types_id = condition_types.id)") def query_actions(self): return self.dbpool.runQuery("SELECT actions.id, action_types.name, actions.events_id " + "FROM actions INNER JOIN action_types ON (actions.action_types_id = action_types.id)") def query_trigger_parameters(self, trigger_id): return self.dbpool.runQuery("SELECT name, value from trigger_parameters WHERE triggers_id = ?", [trigger_id]) def query_condition_parameters(self, condition_id): return self.dbpool.runQuery("SELECT name, value from condition_parameters WHERE conditions_id = ?", [condition_id]) def query_action_parameters(self, action_id): return self.dbpool.runQuery("SELECT name, value from action_parameters WHERE actions_id = ?", [action_id]) def query_device_routing_by_id(self, device_id): return self.dbpool.runQuery("SELECT devices.address, plugins.authcode FROM devices " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " "WHERE devices.id = ?", [device_id]) def query_value_properties(self, value_id): return self.dbpool.runQuery("SELECT current_values.name, devices.address, devices.plugin_id from current_values " + "INNER JOIN devices ON (current_values.device_id = devices.id) " + "WHERE current_values.id = ?", [value_id]) def query_plugin_devices(self, plugin_id): return self.dbpool.runQuery("SELECT devices.id, devices.name, devices.address, locations.name from devices " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id) " + "WHERE plugin_id=? ", [plugin_id]) @inlineCallbacks def update_or_add_value(self, name, value, pluginid, address, time=None): ''' This function updates or adds values to the HouseAgent database. @param name: the name of the value @param value: the actual value of the value @param pluginid: the plugin which holds the device information @param address: the address of the device being handled @param time: the time at which the update has been received, this defaults to now() ''' if not time: updatetime = datetime.datetime.now().isoformat(' ').split('.')[0] else: updatetime = datetime.datetime.fromtimestamp(time).isoformat(' ').split('.')[0] # Query device first device_id = yield self.dbpool.runQuery('select id from devices WHERE plugin_id = ? and address = ? LIMIT 1', (pluginid, address) ) try: device_id = device_id[0][0] except: returnValue('') # device does not exist current_value = yield self.dbpool.runQuery("select id, name, history from current_values where name=? AND device_id=? LIMIT 1", (name, device_id)) try: value_id = current_value[0][0] except: value_id = None if value_id: value_id = current_value[0][0] if current_value[0][2] not in (0, None): DataHistory("data", current_value[0][0], value, "GAUGE", 60, int(time)) yield self.dbpool.runQuery("UPDATE current_values SET value=?, lastupdate=? WHERE id=?", (value, updatetime, value_id)) else: yield self.dbpool.runQuery("INSERT INTO current_values (name, value, device_id, lastupdate) VALUES (?, ?, (select id from devices where address=? AND plugin_id=?), ?)", (name, value, address, pluginid, updatetime)) current_value = yield self.dbpool.runQuery("select id from current_values where name=? AND device_id=?", (name, device_id)) value_id = current_value[0][0] returnValue(value_id) def register_plugin(self, name, uuid, location): return self.dbpool.runQuery("INSERT INTO plugins (name, authcode, location_id) VALUES (?, ?, ?)", [str(name), str(uuid), location]) def query_plugins(self): return self.dbpool.runQuery("SELECT plugins.name, plugins.authcode, plugins.id, locations.name, plugins.location_id from plugins " + "LEFT OUTER JOIN locations ON (plugins.location_id = locations.id)") def query_plugin_by_type_name(self, type_name): return self.dbpool.runQuery("SELECT plugins.id, plugins.authcode from plugins " + "INNER JOIN plugin_types ON (plugins.plugin_type_id = plugin_types.id)" + "WHERE plugin_types.name = ? LIMIT 1", [type_name]) def query_device_classes(self): return self.dbpool.runQuery("SELECT * from device_class order by name ASC") def query_device_types(self): return self.dbpool.runQuery("SELECT * from device_types order by name ASC") @inlineCallbacks def cb_device_crud(self, result, action, id=None, plugin=None, address=None, name=None, location=None): ''' Callback function that get's called when a device has been created, updated or deleted in, to or from the database. @param result: the result of the action @param action: the action initiating the callback being create, update or delete @param plugin: the uuid of the plugin owning the device @param address: the address of the device @param name: the name of the device @param location: the name of the location associated with the device ''' if action == "create": parms = yield self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM devices, plugins, locations WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id ORDER BY devices.id DESC LIMIT 1") if action == "update": parms = yield self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM devices, plugins, locations WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id AND devices.id=?", [id]) if action != "delete": plugin = parms[0][0] address = parms[0][1] name = parms[0][2] location = parms[0][3] parameters = {"plugin": plugin, "address": address, "name": name, "location": location} if self.coordinator: self.coordinator.send_crud_update("device", action, parameters) def save_device(self, name, address, plugin_id, location_id, id=None): ''' This functions saves a device in the HouseAgent database. @param name: the name of the device @param address: the address of the device @param plugin_id: the plugin_id of the associated plugin @param location_id: the location_id of the associated location @param id: the id of the device (in case this is an update) ''' if not id: return self.dbpool.runQuery("INSERT INTO devices (name, address, plugin_id, location_id) VALUES (?, ?, ?, ?)", \ (name, address, plugin_id, location_id)).addCallback(self.cb_device_crud, "create") else: return self.dbpool.runQuery("UPDATE devices SET name=?, address=?, plugin_id=?, location_id=? WHERE id=?", \ (name, address, plugin_id, location_id, id)).addCallback(self.cb_device_crud, "update", id) def del_device(self, id): def delete(result, id): self.dbpool.runQuery("DELETE FROM devices WHERE id=?", [id]).addCallback(self.cb_device_crud, "delete", id, result[0][0], result[0][1], result[0][2], result[0][3]) return self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM plugins, devices, locations " + "WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id AND devices.id=?", [id]).addCallback(delete, id) def del_location(self, id): return self.dbpool.runQuery("DELETE FROM locations WHERE id=?", [id]) @inlineCallbacks def del_event(self, id): # Delete all parameters for this event id yield self.dbpool.runQuery("DELETE FROM trigger_parameters where triggers_id=" + " (select id from triggers where events_id=?)", [id]) yield self.dbpool.runQuery("DELETE FROM condition_parameters where conditions_id=" + " (select id from conditions where events_id=?)" , [id]) yield self.dbpool.runQuery("DELETE FROM action_parameters where actions_id=" + " (select id from actions where events_id=?)", [id]) yield self.dbpool.runQuery("DELETE FROM triggers where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM actions where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM conditions where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM events where id=?", [id]) def del_plugin(self, id): return self.dbpool.runQuery("DELETE FROM plugins WHERE id=?", [id]) def query_locations(self): return self.dbpool.runQuery("select locations.id, locations.name, l2.name from locations " + "left join locations as l2 on locations.parent=l2.id") def query_values(self): return self.dbpool.runQuery("SELECT current_values.name, current_values.value, devices.name, " + "current_values.lastupdate, plugins.name, devices.address, locations.name, current_values.id" + ", control_types.name, control_types.id, history FROM current_values INNER " + "JOIN devices ON (current_values.device_id = devices.id) INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id) " + "LEFT OUTER JOIN control_types ON (current_values.control_type_id = control_types.id)") def query_devices(self): return self.dbpool.runQuery("SELECT devices.id, devices.name, devices.address, plugins.name, locations.name from devices " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id)") def query_location(self, id): return self.dbpool.runQuery("SELECT id, name, parent FROM locations WHERE id=?", [id]) def query_plugin(self, id): return self.dbpool.runQuery("SELECT id, name, location_id FROM plugins WHERE id=?", [id]) def query_device(self, id): return self.dbpool.runQuery("SELECT id, name, address, plugin_id, location_id FROM devices WHERE id=?", [id]) def query_triggertypes(self): return self.dbpool.runQuery("SELECT id, name from trigger_types") def query_actiontypes(self): return self.dbpool.runQuery("SELECT id, name from action_types") def query_conditiontypes(self): return self.dbpool.runQuery("SELECT id, name from condition_types") def query_controltypes(self): return self.dbpool.runQuery("SELECT id, name from control_types") def query_controltypename(self, current_value_id): return self.dbpool.runQuery("select control_types.name from current_values " + "INNER JOIN controL_types ON (control_types.id = current_values.control_type_id) " + "where current_values.id=?", [current_value_id]) def query_devices_simple(self): return self.dbpool.runQuery("SELECT id, name from devices") def query_plugintypes(self): return self.dbpool.runQuery("SELECT id, name from plugin_types") def query_historic_values(self): return self.dbpool.runQuery("select current_values.id, current_values.name, devices.name, current_values.history from current_values, devices where current_values.device_id = devices.id and history = 1;") def query_controllable_devices(self): return self.dbpool.runQuery("SELECT devices.name, devices.address, plugins.name, plugins.authcode, current_values.value, devices.id, control_types.name FROM current_values " + "INNER JOIN devices ON (current_values.device_id = devices.id) " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.control_type_id != ''") def query_action_types_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT current_values.id, current_values.name, control_types.name FROM current_values " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.device_id = ?", [device_id]) def query_action_type_by_value_id(self, value_id): return self.dbpool.runQuery("SELECT control_types.name FROM current_values " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.id = ? LIMIT 1", [value_id]) def query_values_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT id, name from current_values WHERE device_id = '%s'" % device_id) def query_device_type_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT device_types.name FROM devices " + "INNER JOIN device_types ON (device_types.id = devices.device_type_id) " + "WHERE devices.id = ? LIMIT 1", [device_id]) def query_value_by_valueid(self, value_id): return self.dbpool.runQuery("SELECT value,name from current_values WHERE id = ? LIMIT 1", [value_id]) def query_extra_valueinfo(self, value_id): return self.dbpool.runQuery("select devices.name, current_values.name from current_values " + "inner join devices on (current_values.device_id = devices.id) " + "where current_values.id = ?", [value_id]) def set_history(self, id, history): return self.dbpool.runQuery("UPDATE current_values SET history=? WHERE id=?", [history, id]) def set_controltype(self, id, control_type): return self.dbpool.runQuery("UPDATE current_values SET control_type_id=? WHERE id=?", [control_type, id]) def update_location(self, id, name, parent): return self.dbpool.runQuery("UPDATE locations SET name=?, parent=? WHERE id=?", [name, parent, id]) def update_plugin(self, id, name, location): return self.dbpool.runQuery("UPDATE plugins SET name=?, location_id=? WHERE id=?", [name, location, id]) def query_events(self): return self.dbpool.runQuery("SELECT id, name, enabled from events")
class Database(): """ HouseAgent database interaction. """ def __init__(self, log, db_location): self.log = log type = "sqlite" self.coordinator = None self.histcollector = None self._db_location = db_location # Note: cp_max=1 is required otherwise undefined behaviour could occur when using yield icw subsequent # runQuery or runOperation statements if type == "sqlite": self.dbpool = ConnectionPool("sqlite3", db_location, check_same_thread=False, cp_max=1) # Check database schema version and upgrade when required self.updatedb('0.2') def updatedb(self, dbversion): ''' Perform a database schema update when required. ''' # Note: runInteraction runs all queries defined within the specified function as part of a transaction. return self.dbpool.runInteraction(self._updatedb, dbversion) def _updatedb(self, txn, dbversion): ''' Check whether a database schema update is required and act accordingly. ''' # Note: Although all queries are run as part of a transaction, a create or drop table statement result in an implicit commit # Query the version of the current schema try: result = txn.execute("SELECT parm_value FROM common WHERE parm = 'schema_version'").fetchall() except: result = None if result: version = result[0][0] else: version = '0.0' if float(version) > float(dbversion): self.log.error("ERROR: The current database schema (%s) is not supported by this version of HouseAgent" % version) # Exit HouseAgent sys.exit(1) elif float(version) == float(dbversion): self.log.debug("Database schema is up to date") return else: self.log.info("Database schema will be updated from %s to %s:" % (version, dbversion)) # Before we start manipulating the database schema, first make a backup copy of the database try: shutil.copy(self._db_location, self._db_location + datetime.datetime.strftime(datetime.datetime.now(), ".%y%m%d-%H%M%S")) except: self.log.error("Cannot make a backup copy of the database (%s)", sys.exc_info()[1]) return if version == '0.0': try: # Create common table txn.execute("CREATE TABLE IF NOT EXISTS common (parm VARCHAR(16) PRIMARY KEY, parm_value VARCHAR(24) NOT NULL)") # Add schema version to database txn.execute("INSERT INTO common (parm, parm_value) VALUES ('schema_version', ?)", [dbversion]) # Set primary key of the devices table on address + plugin_id to prevent adding duplicate devices txn.execute("CREATE TEMPORARY TABLE devices_backup(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER NOT NULL, location_id INTEGER)") txn.execute("INSERT INTO devices_backup SELECT id, name, address, plugin_id, location_id FROM devices") txn.execute("DROP TABLE devices") txn.execute("CREATE TABLE devices(id INTEGER PRIMARY KEY, name VARCHAR(45), address VARCHAR(45) NOT NULL, plugin_id INTEGER, location_id INTEGER)") txn.execute("CREATE UNIQUE INDEX device_address ON devices (address, plugin_id)") txn.execute("INSERT INTO devices SELECT id, name, address, plugin_id, location_id FROM devices_backup") txn.execute("DROP TABLE devices_backup") self.log.info("Successfully upgraded database schema") except: self.log.error("Database schema upgrade failed (%s)" % sys.exc_info()[1]) elif version == '0.1': # update DB schema version to '0.2' try: # update common table txn.execute("UPDATE common SET parm_value=0.2 WHERE parm='schema_version';") # history_periods table txn.execute("CREATE TABLE history_periods(id integer PRIMARY KEY AUTOINCREMENT NOT NULL,\ name varchar(20), secs integer NOT NULL, sysflag CHAR(1) NOT NULL DEFAULT '0');") # default values for history_periods table txn.execute("INSERT INTO history_periods VALUES(1,'Disabled',0,'1');") txn.execute("INSERT INTO history_periods VALUES(2,'5 min',300,'1');") txn.execute("INSERT INTO history_periods VALUES(3,'15 min',900,'1');") txn.execute("INSERT INTO history_periods VALUES(4,'30 min',1800,'1');") txn.execute("INSERT INTO history_periods VALUES(5,'1 hour',3600,'1');") txn.execute("INSERT INTO history_periods VALUES(6,'2 hours',7200,'1');") txn.execute("INSERT INTO history_periods VALUES(7,'8 hours',28800,'1');") txn.execute("INSERT INTO history_periods VALUES(8,'12 hours',43200,'1');") txn.execute("INSERT INTO history_periods VALUES(9,'1 day',86400,'1');") # history_types table txn.execute("CREATE TABLE history_types (id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \ name varchar(50));") # default values for history_types table txn.execute("INSERT INTO history_types VALUES (NULL, 'GAUGE');") txn.execute("INSERT INTO history_types VALUES (NULL, 'COUNTER');") txn.execute("CREATE TEMPORARY TABLE current_values_tmp( \ id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \ name varchar(45), value varchar(45), device_id integer NOT NULL, \ lastupdate datetime, history bool DEFAULT 0, \ history_type_id integer, control_type_id integer DEFAULT 0);") txn.execute("INSERT INTO current_values_tmp \ SELECT id, name, value, device_id, lastupdate, history, \ history_type_id, control_type_id FROM current_values;") # create new current_values scheme (old data are purged) txn.execute("DROP TABLE current_values;") txn.execute("CREATE TABLE current_values(id integer PRIMARY KEY AUTOINCREMENT NOT NULL, \ name varchar(45), value varchar(45), device_id integer NOT NULL, \ lastupdate datetime, history_period_id int DEFAULT 1, \ history_type_id int DEFAULT 1, control_type_id integer DEFAULT 0, \ FOREIGN KEY (history_period_id) REFERENCES history_periods(id), \ FOREIGN KEY (history_type_id) REFERENCES history_types(id), \ FOREIGN KEY (device_id) REFERENCES devices(id));") # current_values indexes txn.execute("CREATE INDEX 'current_values.fk_current_values_control_types1' \ ON current_values (control_type_id);") txn.execute("CREATE INDEX 'current_values.fk_current_values_history_periods1' \ ON current_values (history_period_id);") txn.execute("CREATE INDEX 'current_values.fk_current_values_history_types1' \ ON current_values (history_type_id);") txn.execute("CREATE INDEX 'current_values.fk_values_devices1' \ ON current_values (device_id);") # fill new current_values table txn.execute("INSERT INTO current_values \ SELECT id, name, value, device_id, lastupdate, 1, 1, control_type_id \ FROM current_values_tmp;") txn.execute("DROP TABLE current_values_tmp;") # history_values table txn.execute("CREATE TABLE history_values (value_id integer,\ value real, created_at datetime, \ FOREIGN KEY (value_id) REFERENCES current_values(id));") txn.execute("CREATE INDEX 'history_values.idx_history_values_created_at1' \ ON history_values (created_at);") txn.execute("CREATE INDEX 'history_values.idx_history_values_value_id1' \ ON history_values (value_id);") # Control types fix txn.execute("INSERT into control_types VALUES(0, 'Not controllable');") txn.execute("UPDATE control_types SET name='Switch (On/off)' WHERE id=1;") txn.execute("UPDATE control_types SET name='Thermostat (Setpoint)' WHERE id=2;") self.log.info("Successfully upgraded database schema") except: self.log.error("Database schema upgrade failed (%s)" % sys.exc_info()[1]) def query_plugin_auth(self, authcode): return self.dbpool.runQuery("SELECT authcode, id from plugins WHERE authcode = '%s'" % authcode) def check_plugin_auth(self, result): if len(result) >= 1: return {'registered': True} else: return {'registered': False} def insert_result(self, result): return {'received': True} def add_event(self, name, enabled, triggers): """ This function adds an event to the database. """ d = self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", (name, enabled) ) def event_added(result): print "added event" return self.dbpool.runQuery("select id from events order by id desc limit 1") d.addCallback(event_added) def got_id(result): event_id = result[0][0] print "got event_id", result[0][0] print "triggers=",triggers # Add triggers deferredlist = [] for trigger in triggers: trigger_type_id = trigger["trigger_type"] print "trigger", trigger def got_triggerid(result): trigger_id = result[0][0] print "parameters", trigger["parameters"] for name, value in trigger["parameters"].iteritems(): print name, value deferredlist.append(self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, " + "triggers_id) VALUES (?, ?, ?)", (name, value, trigger_id))) def trigger_added(result): self.dbpool.runQuery("select id from triggers order by id desc limit 1").addCallback(got_triggerid) # Triggers deferredlist.append(self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id)" + " VALUES (?, ?)", (trigger_type_id, event_id)).addCallback(trigger_added) ) d = defer.gatherResults(deferredlist) return d d.addCallback(got_id) def added_triggers(result): print "triggers added" d.addCallback(added_triggers) return d def add_location(self, name, parent): if parent: return self.dbpool.runQuery("INSERT INTO locations (name, parent) VALUES (?, ?)", [name, parent]) else: return self.dbpool.runQuery("INSERT INTO locations (name) VALUES (?)", [name]) @inlineCallbacks def add_event2(self, name, enabled, conditions, actions, trigger): ''' This adds an event to the database. ''' # Add event, and get event id yield self.dbpool.runQuery("INSERT INTO events (name, enabled) VALUES (?, ?)", [name, enabled]) eventid = yield self.dbpool.runQuery("select id from events order by id desc limit 1") eventid = eventid[0][0] # Add conditions for condition in conditions: condition_type_id = condition["condition_type"] yield self.dbpool.runQuery("INSERT INTO conditions (condition_types_id, events_id)" + " VALUES (?, ?)", [condition_type_id, eventid]) condition_id = yield self.dbpool.runQuery("select id from conditions order by id desc limit 1") condition_id = condition_id[0][0] for name, value in condition["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO condition_parameters (name, value, " + "conditions_id) VALUES (?, ?, ?)", [name, value, condition_id]) # Add actions for action in actions: action_type_id = action["action_type"] yield self.dbpool.runQuery("INSERT INTO actions (action_types_id, events_id)" + " VALUES (?, ?)", [action_type_id, eventid]) action_id = yield self.dbpool.runQuery("select id from actions order by id desc limit 1") action_id = action_id[0][0] for name, value in action["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO action_parameters (name, value, " + "actions_id) VALUES (?, ?, ?)", [name, value, action_id]) # Insert trigger yield self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id, conditions)" + " VALUES (?,?,?)", [trigger["trigger_type"], eventid, trigger["conditions"]]) trigger_id = yield self.dbpool.runQuery("select id from triggers order by id desc limit 1") trigger_id = trigger_id[0][0] for name, value in trigger["parameters"].iteritems(): yield self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, " + "triggers_id) VALUES (?, ?, ?)", [name, value, trigger_id]) def add_trigger(self, trigger_type_id, event_id, value_id, parameters): print "INSERT INTO triggers (trigger_types_id, events_id, current_values_id) VALUES (%d, %d, %d)" % (int(trigger_type_id), int(event_id), int(value_id)) d = self.dbpool.runQuery("INSERT INTO triggers (trigger_types_id, events_id" + ", current_values_id) VALUES (%s, %s, %s)", (int(trigger_type_id), int(event_id), int(value_id)) ) for name, value in parameters.iteritems(): self.dbpool.runQuery("INSERT INTO trigger_parameters (name, value, triggers_id) VALUES (%s, %s, last_insert_id())", (name, value) ) return d #def add_action(self, action_type_id, event_id): def query_latest_device_id(self): ''' This function queries the latest device id. ''' return self.dbpool.runQuery('select id from devices LIMIT 1') def query_triggers(self): return self.dbpool.runQuery("SELECT triggers.id, trigger_types.name, triggers.events_id, triggers.conditions " + "FROM triggers INNER JOIN trigger_types ON (triggers.trigger_types_id = trigger_types.id)") def query_trigger(self, event_id): return self.dbpool.runQuery("SELECT triggers.id, trigger_types.name, triggers.events_id, triggers.conditions " + "FROM triggers INNER JOIN trigger_types ON (triggers.trigger_types_id = trigger_types.id) " + "WHERE triggers.events_id = ? LIMIT 1", [event_id]) def query_conditions(self): return self.dbpool.runQuery("SELECT conditions.id, condition_types.name, conditions.events_id " + "FROM conditions INNER JOIN condition_types ON (conditions.condition_types_id = condition_types.id)") def query_actions(self): return self.dbpool.runQuery("SELECT actions.id, action_types.name, actions.events_id " + "FROM actions INNER JOIN action_types ON (actions.action_types_id = action_types.id)") def query_trigger_parameters(self, trigger_id): return self.dbpool.runQuery("SELECT name, value from trigger_parameters WHERE triggers_id = ?", [trigger_id]) def query_condition_parameters(self, condition_id): return self.dbpool.runQuery("SELECT name, value from condition_parameters WHERE conditions_id = ?", [condition_id]) def query_action_parameters(self, action_id): return self.dbpool.runQuery("SELECT name, value from action_parameters WHERE actions_id = ?", [action_id]) def query_device_routing_by_id(self, device_id): return self.dbpool.runQuery("SELECT devices.address, plugins.authcode FROM devices " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " "WHERE devices.id = ?", [device_id]) def query_value_properties(self, value_id): return self.dbpool.runQuery("SELECT current_values.name, devices.address, devices.plugin_id from current_values " + "INNER JOIN devices ON (current_values.device_id = devices.id) " + "WHERE current_values.id = ?", [value_id]) def query_plugin_devices(self, plugin_id): return self.dbpool.runQuery("SELECT devices.id, devices.name, devices.address, locations.name from devices " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id) " + "WHERE plugin_id=? ", [plugin_id]) @inlineCallbacks def update_or_add_value(self, name, value, pluginid, address, time=None): ''' This function updates or adds values to the HouseAgent database. @param name: the name of the value @param value: the actual value of the value @param pluginid: the plugin which holds the device information @param address: the address of the device being handled @param time: the time at which the update has been received, this defaults to now() ''' if not time: updatetime = datetime.datetime.now().isoformat(' ').split('.')[0] else: updatetime = datetime.datetime.fromtimestamp(time).isoformat(' ').split('.')[0] # Query device first device_id = yield self.dbpool.runQuery('select id from devices WHERE plugin_id = ? and address = ? LIMIT 1', (pluginid, address) ) try: device_id = device_id[0][0] except: returnValue('') # device does not exist current_value = yield self.dbpool.runQuery("SELECT id, name, history_type_id, history_period_id FROM current_values WHERE name=? AND device_id=? LIMIT 1", (name, device_id)) try: value_id = current_value[0][0] except: value_id = None if value_id: value_id = current_value[0][0] history_type = current_value[0][2] history_period = current_value[0][3] yield self.dbpool.runQuery("UPDATE current_values SET value=?, lastupdate=? WHERE id=?", (value, updatetime, value_id)) else: yield self.dbpool.runQuery("INSERT INTO current_values (name, value, device_id, lastupdate) VALUES (?, ?, (SELECT id FROM devices WHERE address=? AND plugin_id=?), ?)", (name, value, address, pluginid, updatetime)) current_value = yield self.dbpool.runQuery("SELECT id FROM current_values WHERE name=? AND device_id=?", (name, device_id)) value_id = current_value[0][0] returnValue(value_id) def register_plugin(self, name, uuid, location): return self.dbpool.runQuery("INSERT INTO plugins (name, authcode, location_id) VALUES (?, ?, ?)", [str(name), str(uuid), location]) def query_plugins(self): return self.dbpool.runQuery("SELECT plugins.name, plugins.authcode, plugins.id, locations.name, plugins.location_id from plugins " + "LEFT OUTER JOIN locations ON (plugins.location_id = locations.id)") def query_plugin_by_type_name(self, type_name): return self.dbpool.runQuery("SELECT plugins.id, plugins.authcode from plugins " + "INNER JOIN plugin_types ON (plugins.plugin_type_id = plugin_types.id)" + "WHERE plugin_types.name = ? LIMIT 1", [type_name]) def query_device_classes(self): return self.dbpool.runQuery("SELECT * from device_class order by name ASC") def query_device_types(self): return self.dbpool.runQuery("SELECT * from device_types order by name ASC") @inlineCallbacks def cb_device_crud(self, result, action, id=None, plugin=None, address=None, name=None, location=None): ''' Callback function that get's called when a device has been created, updated or deleted in, to or from the database. @param result: the result of the action @param action: the action initiating the callback being create, update or delete @param plugin: the uuid of the plugin owning the device @param address: the address of the device @param name: the name of the device @param location: the name of the location associated with the device ''' if action == "create": parms = yield self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM devices, plugins, locations WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id ORDER BY devices.id DESC LIMIT 1") if action == "update": parms = yield self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM devices, plugins, locations WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id AND devices.id=?", [id]) if action != "delete": plugin = parms[0][0] address = parms[0][1] name = parms[0][2] location = parms[0][3] parameters = {"plugin": plugin, "address": address, "name": name, "location": location} if self.coordinator: self.coordinator.send_crud_update("device", action, parameters) def save_device(self, name, address, plugin_id, location_id, id=None): ''' This functions saves a device in the HouseAgent database. @param name: the name of the device @param address: the address of the device @param plugin_id: the plugin_id of the associated plugin @param location_id: the location_id of the associated location @param id: the id of the device (in case this is an update) ''' if not id: return self.dbpool.runQuery("INSERT INTO devices (name, address, plugin_id, location_id) VALUES (?, ?, ?, ?)", \ (name, address, plugin_id, location_id)).addCallback(self.cb_device_crud, "create") else: return self.dbpool.runQuery("UPDATE devices SET name=?, address=?, plugin_id=?, location_id=? WHERE id=?", \ (name, address, plugin_id, location_id, id)).addCallback(self.cb_device_crud, "update", id) def del_device(self, id): def delete(result, id): self.dbpool.runQuery("DELETE FROM devices WHERE id=?", [id]).addCallback(self.cb_device_crud, "delete", id, result[0][0], result[0][1], result[0][2], result[0][3]) return self.dbpool.runQuery("SELECT plugins.authcode, devices.address, devices.name, locations.name FROM plugins, devices, locations " + "WHERE devices.plugin_id = plugins.id AND devices.location_id = locations.id AND devices.id=?", [id]).addCallback(delete, id) def del_location(self, id): return self.dbpool.runQuery("DELETE FROM locations WHERE id=?", [id]) @inlineCallbacks def del_event(self, id): # Delete all parameters for this event id yield self.dbpool.runQuery("DELETE FROM trigger_parameters where triggers_id=" + " (select id from triggers where events_id=?)", [id]) yield self.dbpool.runQuery("DELETE FROM condition_parameters where conditions_id=" + " (select id from conditions where events_id=?)" , [id]) yield self.dbpool.runQuery("DELETE FROM action_parameters where actions_id=" + " (select id from actions where events_id=?)", [id]) yield self.dbpool.runQuery("DELETE FROM triggers where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM actions where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM conditions where events_id=?", [id]) yield self.dbpool.runQuery("DELETE FROM events where id=?", [id]) def del_plugin(self, id): return self.dbpool.runQuery("DELETE FROM plugins WHERE id=?", [id]) def query_locations(self): return self.dbpool.runQuery("select locations.id, locations.name, l2.name from locations " + "left join locations as l2 on locations.parent=l2.id") def query_values(self): return self.dbpool.runQuery("SELECT current_values.name, current_values.value, devices.name, " + "current_values.lastupdate, plugins.name, devices.address, locations.name, current_values.id" + ", control_types.name, control_types.id, history_types.name, history_periods.name, plugins.id FROM current_values INNER " + "JOIN devices ON (current_values.device_id = devices.id) INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id) " + "LEFT OUTER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "LEFT OUTER JOIN history_types ON (current_values.history_type_id = history_types.id) " + "LEFT OUTER JOIN history_periods ON (current_values.history_period_id = history_periods.id)") def query_values_light(self): return self.dbpool.runQuery("SELECT id, name, history_period_id, history_type_id FROM current_values;") def query_devices(self): return self.dbpool.runQuery("SELECT devices.id, devices.name, devices.address, plugins.name, locations.name from devices " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "LEFT OUTER JOIN locations ON (devices.location_id = locations.id)") def query_location(self, id): return self.dbpool.runQuery("SELECT id, name, parent FROM locations WHERE id=?", [id]) def query_plugin(self, id): return self.dbpool.runQuery("SELECT id, name, location_id FROM plugins WHERE id=?", [id]) def query_device(self, id): return self.dbpool.runQuery("SELECT id, name, address, plugin_id, location_id FROM devices WHERE id=?", [id]) def query_triggertypes(self): return self.dbpool.runQuery("SELECT id, name from trigger_types") def query_actiontypes(self): return self.dbpool.runQuery("SELECT id, name from action_types") def query_conditiontypes(self): return self.dbpool.runQuery("SELECT id, name from condition_types") def query_controltypes(self): return self.dbpool.runQuery("SELECT id, name from control_types") def query_controltypename(self, current_value_id): return self.dbpool.runQuery("select control_types.name from current_values " + "INNER JOIN controL_types ON (control_types.id = current_values.control_type_id) " + "where current_values.id=?", [current_value_id]) def query_devices_simple(self): return self.dbpool.runQuery("SELECT id, name from devices") def query_plugintypes(self): return self.dbpool.runQuery("SELECT id, name from plugin_types") # history collector stuff def query_history_types(self): return self.dbpool.runQuery("SELECT id, name FROM history_types;") def query_history_schedules(self): return self.dbpool.runQuery("SELECT id, name, history_period_id, history_type_id FROM current_values;") def query_history_periods(self): return self.dbpool.runQuery("SELECT id, name, secs, sysflag FROM history_periods;") def query_history_values(self, date_from, date_to): return self.dbpool.runQuery("SELECT value, created_at FROM history_values WHERE created_at >= '%s' AND created_at < '%s';" % (date_from, date_to)) def cleanup_history_values(self): """keep 7 days history of history_values table""" return self.dbpool.runQuery("DELETE FROM history_values WHERE created_at < DATETIME(DATETIME(), 'localtime', '-7 day');") def collect_history_values(self, value_id): return self.dbpool.runQuery("INSERT INTO history_values SELECT id, value, DATETIME(DATETIME(), 'localtime') FROM current_values WHERE id=?;", [value_id]) # /history collector stuff def query_controllable_devices(self): return self.dbpool.runQuery("SELECT devices.name, devices.address, plugins.name, plugins.authcode, current_values.value, devices.id, control_types.name, current_values.id FROM current_values " + "INNER JOIN devices ON (current_values.device_id = devices.id) " + "INNER JOIN plugins ON (devices.plugin_id = plugins.id) " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.control_type_id != 0") def query_action_types_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT current_values.id, current_values.name, control_types.name FROM current_values " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.device_id = ?", [device_id]) def query_action_type_by_value_id(self, value_id): return self.dbpool.runQuery("SELECT control_types.name FROM current_values " + "INNER JOIN control_types ON (current_values.control_type_id = control_types.id) " + "WHERE current_values.id = ? LIMIT 1", [value_id]) def query_values_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT id, name from current_values WHERE device_id = '%s'" % device_id) def query_device_type_by_device_id(self, device_id): return self.dbpool.runQuery("SELECT device_types.name FROM devices " + "INNER JOIN device_types ON (device_types.id = devices.device_type_id) " + "WHERE devices.id = ? LIMIT 1", [device_id]) def query_value_by_valueid(self, value_id): return self.dbpool.runQuery("SELECT value,name from current_values WHERE id = ? LIMIT 1", [value_id]) def query_extra_valueinfo(self, value_id): return self.dbpool.runQuery("select devices.name, current_values.name from current_values " + "inner join devices on (current_values.device_id = devices.id) " + "where current_values.id = ?", [value_id]) def set_history(self, id, history_period, history_type): # histcollector needs a fresh data -> defer the UPDATE d = self.dbpool.runQuery("UPDATE current_values SET history_period_id=?, history_type_id=? WHERE id=?", [history_period, history_type, id]) # helper fn def histcollector_refresh(result, id, history_period): self.histcollector.cb_unregister_schedule(int(id)) self.histcollector.cb_register_schedule(int(id), history_period) d.addCallback(histcollector_refresh, id, history_period) return d def set_controltype(self, id, control_type): return self.dbpool.runQuery("UPDATE current_values SET control_type_id=? WHERE id=?", [control_type, id]) def update_location(self, id, name, parent): return self.dbpool.runQuery("UPDATE locations SET name=?, parent=? WHERE id=?", [name, parent, id]) def update_plugin(self, id, name, location): return self.dbpool.runQuery("UPDATE plugins SET name=?, location_id=? WHERE id=?", [name, location, id]) def query_events(self): return self.dbpool.runQuery("SELECT id, name, enabled from events")
class DatabaseQuery: def __init__(self): # Import postgres & twisted credentials from environment. Throws if does not exist. self.postgres_database = os.environ['POSTGRES_DATABASE'] self.postgres_user = os.environ['POSTGRES_USER'] self.postgres_password = os.environ['POSTGRES_PASS'] self.postgres_host = os.environ['POSTGRES_HOST'] def connect(self): """ Setup our database connection. Throws if cannot connect. """ print("[DatabaseQuery] Connecting to database" "\n -> database:'%s' user:'******' host:'%s'" % (self.postgres_database, self.postgres_user, self.postgres_host)) # get a connection, if a connect cannot be made an exception will be raised here self.dbConnection = ConnectionPool('psycopg2', database=self.postgres_database, user=self.postgres_user, host=self.postgres_host, password=self.postgres_password) print("[DatabaseQuery] Database connection sucsessful") def get_connection(self): """ Returns the database connection so we can access it from an AMP responder :return: """ if 'dbConnection' in dir(self): return self.dbConnection else: raise ConnectionError def insert_into_register_token_blind_token_hash_user_id_ballot_id( self, blind_token_hash, user_id, ballot_id): """ Register a token signiture request in the database. :return: """ def onSuccess(result): print("[DatabaseQuery - register_token_request] - Insert sucsess:") return {'ok': True} def onError(failure): print("[DatabaseQuery - register_token_request] - Insert error:") pprint.pprint(failure.value) raise failure.raiseException() def _insert(cursor, user_id, ballot_id): statement = "INSERT INTO token_request (blind_token_hash, user_id, ballot_id) VALUES (%s, %s, %s);" cursor.execute(statement, (blind_token_hash, user_id, ballot_id)) deferred = self.dbConnection.runInteraction(_insert, user_id, ballot_id) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def insert_into_register_vote_signed_token_hash_voter_address_ballot_id( self, signed_token_hash, voter_address, ballot_id): """ Register a token signiture request in the database. :return: """ def onSuccess(result): print("[DatabaseQuery - register_vote_request] - Insert sucsess:") return {'ok': True} def onError(failure): print("[DatabaseQuery - register_vote_request] - Insert error:") pprint.pprint(failure.value) raise failure.raiseException() def _insert(cursor, signed_token, voter_address, ballot_id): statement = "INSERT INTO register_vote (signed_token_hash, voter_address, ballot_id) VALUES (%s, %s, %s);" cursor.execute(statement, (signed_token_hash, voter_address, ballot_id)) deferred = self.dbConnection.runInteraction(_insert, signed_token_hash, voter_address, ballot_id) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def search_register_vote_for_voter_address(self, voter_address): """ Requests all rows ascociated with a voter_address from the token_request table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :param user_id: :return: """ def onSuccess(results): print("[DatabaseQuery - retrieve_request_sign] - Query sucsess:") # pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print("[DatabaseQuery - retrieve_request_sign] - Query error:") pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT * FROM register_vote WHERE voter_address='%s';" % voter_address deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def search_token_request_for_user_id(self, user_id): """ Requests all rows ascociated with a user_id from the token_request table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :param user_id: :return: """ def onSuccess(results): print("[DatabaseQuery - retrieve_request_sign] - Query sucsess:") pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print("[DatabaseQuery - retrieve_request_sign] - Query error:") pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT * FROM token_request WHERE user_id=%s;" % user_id deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def search_ballot_register_for_ballot_id(self, ballot_id, voter_address): """ Requests all rows ascociated with a ballot_id from the ballot_register table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :param ballot_id: :param voter_address: :return: """ def onSuccess(results): print( "[DatabaseQuery - retrieve_request_register] - Query sucsess:") pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print("[DatabaseQuery - retrieve_request_register] - Query error:") pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT * FROM register_vote WHERE ballot_id=%s AND voter_address='%s';" % ( ballot_id, voter_address) deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred
class SQLMagicPipeline(object): def __init__(self, settings, **kwargs): """Connect to database in the pool.""" if not isinstance(settings, dict): raise NotConfigured('No database connection settings found.') self.settings = settings self.stats = kwargs.get('stats') self.debug = kwargs.get('debug', False) self.paramstyle = ':' self.identifier = '"' # default to ANSI quoting self.queries = { 'select': "SELECT $fields FROM $table:esc WHERE $indices:and", # select on UniqueFields 'selectall': "SELECT $fields FROM $table:esc", 'selectone': "SELECT $fields FROM $table:esc WHERE $indices:and LIMIT 1", # if backend supports LIMIT # 'delete' : "DELETE FROM $table:esc WHERE $indices:and", # match on UniqueFields 'deleteme': "DELETE FROM $table:esc WHERE $fields_values:and", # exact item match } self.dbapi = None if self.settings.get('drivername') == 'sqlite': self.dbapi = __import__('sqlite3', fromlist=['']) self.__dbpool = ConnectionPool('sqlite3', self.settings.get('database', ':memory:'), # apparently the connection pool / thread pool does not do the teardown in the same thread # https://twistedmatrix.com/trac/ticket/3629 # therefore throwing errors on finalClose at reactor shutdown # TODO: should be able to work around that? check_same_thread=False, # SQLite must be compiled threadsafe to use this # limit connection pool to one thread to avoid "database is locked" errors #cp_max=1, # - or raise the database timeout sufficiently timeout=300, ) # alternative escaping parameter #self.paramstyle = '?' #self.paramstyle = ':' #self.paramstyle = '$' # default statements for sqlite self.queries.update({ 'insert': "INSERT INTO $table:esc SET $fields_values", 'upsert': "INSERT OR REPLACE INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'pgsql': self.dbapi = __import__('psycopg2', fromlist=['']) #from psycopg2.extras import DictCursor self.__dbpool = ConnectionPool('psycopg2', database=self.settings.get('database'), user = self.settings.get('username'), password = self.settings.get('password', None), host = self.settings.get('host', None), # default to unix socket port = self.settings.get('port', '5432'), # cursor_factory = DictCursor, ) self.paramstyle = '%s' # default statements for postgres self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'mysql': self.dbapi = __import__('MySQLdb', fromlist=['']) from MySQLdb import cursors self.__dbpool = ReconnectingConnectionPool('MySQLdb', db=self.settings.get('database'), user = self.settings.get('username'), passwd = self.settings.get('password', None), host = self.settings.get('host', 'localhost'), # should default to unix socket port = self.settings.get('port', 3306), cursorclass = cursors.DictCursor, charset = 'utf8', use_unicode = True, # connpool settings cp_reconnect = True, #cp_noisy = True, #cp_min = 1, #cp_max = 1, ) self.paramstyle = '%s' self.identifier = '`' # MySQL quoting # default statements for mysql self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", # 'upsert': "REPLACE INTO $table ($fields) VALUES ($values)", 'upsert': "INSERT INTO $table:esc SET $fields_values ON DUPLICATE KEY UPDATE $fields_values", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'firebird': # untested self.dbapi = __import__('fdb', fromlist=['']) self.__dbpool = ConnectionPool('fdb', database=self.settings.get('database'), user = self.settings.get('username'), password = self.settings.get('password', None), host = self.settings.get('host', None), # default to unix socket port = self.settings.get('port', 3050), #dialect = 1, # necessary for all dialect 1 databases charset = 'UTF8',# specify a character set for the connection ) self.paramstyle = '?' self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) self.queries.update(kwargs.get('queries', {})) @classmethod def from_crawler(cls, crawler): if not crawler.settings.get('SQLMAGIC_DATABASE'): raise NotConfigured('No database connection settings found.') o = cls( settings=crawler.settings.get('SQLMAGIC_DATABASE'), stats=crawler.stats, queries=crawler.settings.get('SQLMAGIC_QUERIES', {}), debug=crawler.settings.getbool('SQLMAGIC_DEBUG') ) return o def open_spider(self, spider): self.on_connect() def on_connect(self): ## override this to run some queries after connecting # e.g. create tables for an in-memory SQLite database pass def close_spider(self, spider): self.shutdown() def shutdown(self): """Shutdown connection pool, kill associated threads""" self.__dbpool.close() def process_item(self, item, spider): """Process the item.""" # Only handle items inheriting SQLItem if not isinstance(item, SQLItem): return item self.stats.inc_value('sqlmagic/total_items_caught') # always return original item deferred = self.operation(item, spider) deferred.addBoth(lambda _: item) return deferred def operation(self, item, spider): def on_insert(result, query, params): self.stats.inc_value('sqlmagic/sqlop_success_insert') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG, spider=spider) return result def on_update(result, query, params): self.stats.inc_value('sqlmagic/sqlop_success_update') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG, spider=spider) return result def on_integrityerror(error, query, params): error.trap(self.dbapi.IntegrityError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_integrity') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.INFO, spider=spider) # error.raiseException() # keep bubbling def on_operationalerror(error, query, params): error.trap(self.dbapi.OperationalError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_operational') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider) # error.raiseException() # keep bubbling def on_seriouserror(error, query, params): error.trap(self.dbapi.ProgrammingError, self.dbapi.InterfaceError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_connection') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider) error.raiseException() # keep bubbling return error def update(error, query, params): error.trap(self.dbapi.IntegrityError) if error.value[0] != 1062: # Duplicate key error.raiseException() # keep bubbling #e = error.getErrorMessage() #if self.debug: # qlog = self._log_preparedsql(query, params) # log.msg('%s got error %s - trying update' % (self.__class__.__name__, e), level=log.DEBUG, spider=spider) self.stats.inc_value('sqlmagic/sqlop_update_after_insert_tries') d = self.__dbpool.runInteraction(self.transaction, query, params, item, spider) d.addCallback(on_update, query, params) return d # try insert query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier) #query, params = _sql_format(self.queries['upsert'], item, paramstyle=self.paramstyle, identifier=self.identifier) deferred = self.__dbpool.runInteraction(self.transaction, query, params, item, spider) deferred.addCallback(on_insert, query, params) deferred.addErrback(on_seriouserror, query, params) deferred.addErrback(on_operationalerror, query, params) #deferred.addErrback(on_integrityerror, query, params) # ignore failing inserts before update # on failure, update query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier) deferred.addErrback(update, query, params) deferred.addErrback(on_seriouserror, query, params) deferred.addErrback(on_operationalerror, query, params) deferred.addErrback(on_integrityerror, query, params) deferred.addErrback(self._database_error, item, spider) # deferred = self.insert_or_update((query,params), (update, uparams), item, spider) self.stats.inc_value('sqlmagic/total_items_returned') return deferred def transaction(self, txn, query, params, item, spider): self.stats.inc_value('sqlmagic/sqlop_transact_%s' % query[:6].lower()) txn.execute(query, params) """ def xtransaction(self, txn, query, params, item, spider): # primary key check query, params = _sql_format(self.queries['select'], item, paramstyle=self.paramstyle, identifier=self.identifier) txn.execute(query, params) result = txn.fetchone() if result: log.msg("Item already in db: (id) %s item:\n%r" % (result['id'], item), level=log.WARNING) query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier) # transaction in thread qlog = self._log_preparedsql(query, params) try: txn.execute(query, params) except self.dbapi.IntegrityError as e: #spider.log('%s FAILED executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier) qlog = self._log_preparedsql(query, params) try: #spider.log('%s executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) txn.execute(query, params) except self.dbapi.OperationalError as e: # retrying in new transaction # spider.log('%s errored. Retrying.\nError: %s\nQuery: %s' % (self.__class__.__name__, e, qlog), level=log.WARNING) # self._spool.append((query, params, item)) #except Exception as e: if self.debug: spider.log('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING) raise finally: if self.debug: spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) except self.dbapi.OperationalError as e: # also try again if self.debug: spider.log('%s failed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) raise finally: if self.debug: spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) """ def _log_preparedsql(self, query, params): """Simulate escaped query for log""" for p in params: query = re.sub('(\\'+self.paramstyle+r'\d?)', '"%s"' % p, query, count=1) return query def _database_error(self, e, item, spider=None): """Log exceptions.""" if spider: log.err(e, spider=spider) else: log.err(e) def query(self, sql): # run a query in the connection pool # parameters for prepared statements must be passed as 'sql=(query, params)' # (possible use-case from inside spider code) '''Spider Example: build start requests from database results from scrapy.exceptions import CloseSpider, NotConfigured from ..pipelines.sqlmagic import SQLMagicPipeline class MySpider(Spider): def spider_opened(self, spider): try: self.db = SQLMagicPipeline(self.settings.get('SQLMAGIC_DATABASE')) except NotConfigured: raise CloseSpider('Could not get database settings.') @defer.inlineCallbacks def db_queries(self, response): query = """CALL procedure ()""" result = yield self.db.query(query) # build requests requests = [] for value in result: r = yield self.build_request_fromdb(response, value) requests.append(r) # queue them defer.returnValue(requests) def start_requests(self): yield Request(self.start_urls[0], callback=self.database_queries) def build_request_fromdb(self, response, db): # custom logic to convert db result into a request r = Request(response.url) r.callback = self.parse return r ''' if query[:6].lower() in ('select',): deferred = self.__dbpool.runQuery(sql) if query[:4].lower() in ('call',): # potential fail: procedure must run a SELECT for this, # otherwise it should do runOperation deferred = self.__dbpool.runQuery(sql) else: deferred = self.__dbpool.runOperation(sql) return deferred
class DatabaseQuery: def __init__(self): # Import postgres & twisted credentials from environment. Throws if does not exist. self.postgres_database = os.environ['POSTGRES_DATABASE'] self.postgres_user = os.environ['POSTGRES_USER'] self.postgres_password = os.environ['POSTGRES_PASS'] self.postgres_host = os.environ['POSTGRES_HOST'] def connect(self): """ Setup our database connection. Throws if cannot connect. """ print("[DatabaseQuery] Connecting to database" "\n -> database:'%s' user:'******' host:'%s'" % (self.postgres_database, self.postgres_user, self.postgres_host)) # get a connection, if a connect cannot be made an exception will be raised here self.dbConnection = ConnectionPool('psycopg2', database=self.postgres_database, user=self.postgres_user, host=self.postgres_host, password=self.postgres_password) print("[DatabaseQuery] Database connection sucsessful") def get_connection(self): """ Returns the database connection so we can access it from an AMP responder :return: """ if 'dbConnection' in dir(self): return self.dbConnection else: raise ConnectionError def search_ballot_register_for_user_id(self, user_id): """ Requests all rows ascociated with a user_id from the ballot_register table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :param user_id: :return: """ def onSuccess(results): print("[DatabaseQuery - retrieve_ballots] - Query sucsess:") # pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print("[DatabaseQuery - retrieve_ballots] - Query error:") pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT " \ "ballot_register.user_id," \ "ballot_register.ballot_id," \ "ballot_register.created_on," \ "available_ballots.ballot_name," \ "available_ballots.ballot_address " \ "FROM " \ "available_ballots, " \ "ballot_register " \ "WHERE " \ "ballot_register.user_id=%s AND " \ "ballot_register.ballot_id=available_ballots.ballot_id;" % user_id deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def search_ballots_available_for_all_ballots(self): """ Requests all rows from the ballot_register table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :return: """ def onSuccess(results): print( "[DatabaseQuery - search_ballots_available_for_all_ballots] - Query sucsess:" ) # pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print( "[DatabaseQuery - search_ballots_available_for_all_ballots] - Query error:" ) pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT * FROM available_ballots;" deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def search_ballots_available_for_ballot_id(self, ballot_id): """ Requests all rows from the ballot_register table. Will return either a dictionary (onSucsess) or raise an exception (onError) to be passed back to the client. :return: """ def onSuccess(results): print( "[DatabaseQuery - search_ballots_available_for_ballot_id] - Query sucsess:" ) # pprint.pprint(results, indent=4) # Convert list of results to bytes for transport encoded_results = pickle.dumps(results) return {'ok': encoded_results} def onError(failure): print( "[DatabaseQuery - search_ballots_available_for_all_ballots] - Query error:" ) pprint.pprint(failure.value) raise failure.raiseException() query = "SELECT * FROM available_ballots WHERE ballot_id=%s;" % ballot_id deferred = self.dbConnection.runQuery(query) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def insert_into_ballot_register_user_id_ballot_id(self, user_id, ballot_id): """ Request to register a user for a ballot in the ballot_register table. Will return True on sucsess or raise an exception on failure which will be passed back to the client. :param user_id: :return: """ def onSuccess(result): print( "[DatabaseQuery - insert_into_ballot_register_user_id_ballot_id] - Insert sucsess:" ) return {'ok': True} def onError(failure): print( "[DatabaseQuery - insert_into_ballot_register_user_id_ballot_id] - Insert error:" ) pprint.pprint(failure.value) raise failure.raiseException() def _insert(cursor, user_id, ballot_id): statement = "INSERT INTO ballot_register (user_id, ballot_id) VALUES (%s, %s);" % ( user_id, ballot_id) cursor.execute(statement) deferred = self.dbConnection.runInteraction(_insert, user_id, ballot_id) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred def insert_into_ballots_available(self, ballot_name, ballot_address, ballot_interface, ballot_end_date): """ Request to register a new ballot in the ballots_available table. Will return True on sucsess or raise an exception on failure which will be passed back to the client. :param user_id: :return: """ def onSuccess(result): print( "[DatabaseQuery - insert_into_ballots_available] - Insert sucsess:" ) return {'ballot_address': ballot_address} def onError(failure): print( "[DatabaseQuery - insert_into_ballots_available] - Insert error:" ) pprint.pprint(failure.value) raise failure.raiseException() def _insert(cursor, ballot_name, ballot_address, ballot_interface, ballot_end_date): statement = "INSERT INTO available_ballots (ballot_name, ballot_address, ballot_interface, ballot_end_date) VALUES (%s, %s, %s, %s);" % ( ballot_name, ballot_address, ballot_interface, ballot_end_date) print(statement) cursor.execute(statement) print("\nballot_name\n", ballot_name, "\nballot_address\n", ballot_address, "\nballot_interface\n", ballot_interface, "\nballot_end_date\n", ballot_end_date) deferred = self.dbConnection.runInteraction(_insert, ballot_name, ballot_address, ballot_interface, ballot_end_date) deferred.addCallback(onSuccess) deferred.addErrback(onError) return deferred
class SQLMagicPipeline(object): def __init__(self, settings, **kwargs): """Connect to database in the pool.""" if not isinstance(settings, dict): raise NotConfigured('No database connection settings found.') self.settings = settings self.stats = kwargs.get('stats') self.debug = kwargs.get('debug', False) self.paramstyle = ':' self.identifier = '"' # default to ANSI quoting self.queries = { 'select': "SELECT $fields FROM $table:esc WHERE $indices:and", # select on UniqueFields 'selectall': "SELECT $fields FROM $table:esc", 'selectone': "SELECT $fields FROM $table:esc WHERE $indices:and LIMIT 1", # if backend supports LIMIT # 'delete': "DELETE FROM $table:esc WHERE $indices:and", # match on UniqueFields 'deleteme': "DELETE FROM $table:esc WHERE $fields_values:and", # exact item match } self.dbapi = None if self.settings.get('drivername') == 'sqlite': self.dbapi = __import__('sqlite3', fromlist=['']) self.__dbpool = ConnectionPool( 'sqlite3', self.settings.get('database', ':memory:'), # apparently the connection pool / thread pool does not do the teardown in the same thread # https://twistedmatrix.com/trac/ticket/3629 # therefore throwing errors on finalClose at reactor shutdown # TODO: should be able to work around that? check_same_thread= False, # SQLite must be compiled threadsafe to use this # limit connection pool to one thread to avoid "database is locked" errors #cp_max=1, # - or raise the database timeout sufficiently timeout=300, ) # alternative escaping parameter #self.paramstyle = '?' #self.paramstyle = ':' #self.paramstyle = '$' # default statements for sqlite self.queries.update({ 'insert': "INSERT INTO $table:esc SET $fields_values", 'upsert': "INSERT OR REPLACE INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'pgsql': self.dbapi = __import__('psycopg2', fromlist=['']) #from psycopg2.extras import DictCursor self.__dbpool = ConnectionPool( 'psycopg2', database=self.settings.get('database'), user=self.settings.get('username'), password=self.settings.get('password', None), host=self.settings.get('host', None), # default to unix socket port=self.settings.get('port', '5432'), # cursor_factory = DictCursor, ) self.paramstyle = '%s' # default statements for postgres self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'mysql': self.dbapi = __import__('MySQLdb', fromlist=['']) from MySQLdb import cursors self.__dbpool = ReconnectingConnectionPool( 'MySQLdb', db=self.settings.get('database'), user=self.settings.get('username'), passwd=self.settings.get('password', None), host=self.settings.get( 'host', 'localhost'), # should default to unix socket port=self.settings.get('port', 3306), cursorclass=cursors.DictCursor, charset='utf8', use_unicode=True, # connpool settings cp_reconnect=True, #cp_noisy = True, #cp_min = 1, #cp_max = 1, ) self.paramstyle = '%s' self.identifier = '`' # MySQL quoting # default statements for mysql self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", # 'upsert': "REPLACE INTO $table ($fields) VALUES ($values)", 'upsert': "INSERT INTO $table:esc SET $fields_values ON DUPLICATE KEY UPDATE $fields_values", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) elif self.settings.get('drivername') == 'firebird': # untested self.dbapi = __import__('fdb', fromlist=['']) self.__dbpool = ConnectionPool( 'fdb', database=self.settings.get('database'), user=self.settings.get('username'), password=self.settings.get('password', None), host=self.settings.get('host', None), # default to unix socket port=self.settings.get('port', 3050), #dialect = 1, # necessary for all dialect 1 databases charset='UTF8', # specify a character set for the connection ) self.paramstyle = '?' self.queries.update({ 'insert': "INSERT INTO $table:esc ($fields) VALUES ($values)", 'update': "UPDATE $table:esc SET $fields_values WHERE $indices:and", }) self.queries.update(kwargs.get('queries', {})) @classmethod def from_crawler(cls, crawler): if not crawler.settings.get('SQLMAGIC_DATABASE'): raise NotConfigured('No database connection settings found.') o = cls(settings=crawler.settings.get('SQLMAGIC_DATABASE'), stats=crawler.stats, queries=crawler.settings.get('SQLMAGIC_QUERIES', {}), debug=crawler.settings.getbool('SQLMAGIC_DEBUG')) return o def open_spider(self, spider): self.on_connect() def on_connect(self): ## override this to run some queries after connecting # e.g. create tables for an in-memory SQLite database pass def close_spider(self, spider): self.shutdown() def shutdown(self): """Shutdown connection pool, kill associated threads""" self.__dbpool.close() def process_item(self, item, spider): """Process the item.""" # Only handle items inheriting SQLItem if not isinstance(item, SQLItem): return item self.stats.inc_value('sqlmagic/total_items_caught') # always return original item deferred = self.operation(item, spider) deferred.addBoth(lambda _: item) return deferred def operation(self, item, spider): def on_insert(result, query, params): self.stats.inc_value('sqlmagic/sqlop_success_insert') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG, spider=spider) return result def on_update(result, query, params): self.stats.inc_value('sqlmagic/sqlop_success_update') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG, spider=spider) return result def on_integrityerror(error, query, params): error.trap(self.dbapi.IntegrityError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_integrity') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.INFO, spider=spider) # error.raiseException() # keep bubbling def on_operationalerror(error, query, params): error.trap(self.dbapi.OperationalError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_operational') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s failed executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider) # error.raiseException() # keep bubbling def on_seriouserror(error, query, params): error.trap(self.dbapi.ProgrammingError, self.dbapi.InterfaceError) e = error.getErrorMessage() self.stats.inc_value('sqlmagic/error_connection') if self.debug: qlog = self._log_preparedsql(query, params) log.msg('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING, spider=spider) error.raiseException() # keep bubbling return error def update(error, query, params): error.trap(self.dbapi.IntegrityError) if error.value[0] != 1062: # Duplicate key error.raiseException() # keep bubbling #e = error.getErrorMessage() #if self.debug: # qlog = self._log_preparedsql(query, params) # log.msg('%s got error %s - trying update' % (self.__class__.__name__, e), level=log.DEBUG, spider=spider) self.stats.inc_value('sqlmagic/sqlop_update_after_insert_tries') d = self.__dbpool.runInteraction(self.transaction, query, params, item, spider) d.addCallback(on_update, query, params) return d # try insert query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier) #query, params = _sql_format(self.queries['upsert'], item, paramstyle=self.paramstyle, identifier=self.identifier) deferred = self.__dbpool.runInteraction(self.transaction, query, params, item, spider) deferred.addCallback(on_insert, query, params) deferred.addErrback(on_seriouserror, query, params) deferred.addErrback(on_operationalerror, query, params) #deferred.addErrback(on_integrityerror, query, params) # ignore failing inserts before update # on failure, update query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier) deferred.addErrback(update, query, params) deferred.addErrback(on_seriouserror, query, params) deferred.addErrback(on_operationalerror, query, params) deferred.addErrback(on_integrityerror, query, params) deferred.addErrback(self._database_error, item, spider) # deferred = self.insert_or_update((query,params), (update, uparams), item, spider) self.stats.inc_value('sqlmagic/total_items_returned') return deferred def transaction(self, txn, query, params, item, spider): self.stats.inc_value('sqlmagic/sqlop_transact_%s' % query[:6].lower()) txn.execute(query, params) """ def xtransaction(self, txn, query, params, item, spider): # primary key check query, params = _sql_format(self.queries['select'], item, paramstyle=self.paramstyle, identifier=self.identifier) txn.execute(query, params) result = txn.fetchone() if result: log.msg("Item already in db: (id) %s item:\n%r" % (result['id'], item), level=log.WARNING) query, params = _sql_format(self.queries['insert'], item, paramstyle=self.paramstyle, identifier=self.identifier) # transaction in thread qlog = self._log_preparedsql(query, params) try: txn.execute(query, params) except self.dbapi.IntegrityError as e: #spider.log('%s FAILED executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) query, params = _sql_format(self.queries['update'], item, paramstyle=self.paramstyle, identifier=self.identifier) qlog = self._log_preparedsql(query, params) try: #spider.log('%s executing: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) txn.execute(query, params) except self.dbapi.OperationalError as e: # retrying in new transaction # spider.log('%s errored. Retrying.\nError: %s\nQuery: %s' % (self.__class__.__name__, e, qlog), level=log.WARNING) # self._spool.append((query, params, item)) #except Exception as e: if self.debug: spider.log('%s FAILED executing: %s\nError: %s' % (self.__class__.__name__, qlog, e), level=log.WARNING) raise finally: if self.debug: spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) except self.dbapi.OperationalError as e: # also try again if self.debug: spider.log('%s failed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) raise finally: if self.debug: spider.log('%s executed: %s' % (self.__class__.__name__, qlog), level=log.DEBUG) """ def _log_preparedsql(self, query, params): """Simulate escaped query for log""" for p in params: query = re.sub('(\\' + self.paramstyle + r'\d?)', '"%s"' % p, query, count=1) return query def _database_error(self, e, item, spider=None): """Log exceptions.""" if spider: log.err(e, spider=spider) else: log.err(e) def query(self, sql): # run a query in the connection pool # parameters for prepared statements must be passed as 'sql=(query, params)' # (possible use-case from inside spider code) '''Spider Example: build start requests from database results from scrapy.exceptions import CloseSpider, NotConfigured from ..pipelines.sqlmagic import SQLMagicPipeline class MySpider(Spider): def spider_opened(self, spider): try: self.db = SQLMagicPipeline(self.settings.get('SQLMAGIC_DATABASE')) except NotConfigured: raise CloseSpider('Could not get database settings.') @defer.inlineCallbacks def db_queries(self, response): query = """CALL procedure ()""" result = yield self.db.query(query) # build requests requests = [] for value in result: r = yield self.build_request_fromdb(response, value) requests.append(r) # queue them defer.returnValue(requests) def start_requests(self): yield Request(self.start_urls[0], callback=self.database_queries) def build_request_fromdb(self, response, db): # custom logic to convert db result into a request r = Request(response.url) r.callback = self.parse return r ''' if query[:6].lower() in ('select', ): deferred = self.__dbpool.runQuery(sql) if query[:4].lower() in ('call', ): # potential fail: procedure must run a SELECT for this, # otherwise it should do runOperation deferred = self.__dbpool.runQuery(sql) else: deferred = self.__dbpool.runOperation(sql) return deferred