def _tagids(self, tags): ids = [] cursor = self._get_cur() for t in tags: pickled = cPickle.dumps(t) try: ids.append(self._tagidcache[pickled]) continue except KeyError, e: pass # not in cache sql = "SELECT ID FROM %sTAGS WHERE DATA = %%(tag)s" % ( self._prefix) if not isinstance(t, Tag): raise DatabaseException( "Tried to insert foreign object into database [%s]", t) data = {"tag": pickled} try: cursor.execute(sql, data) row = cursor.fetchone() if (row != None): ids.append(row[0]) self._tagidcache[pickled] = row[0] else: sql = "INSERT INTO %sTAGS(DATA) VALUES(%%(tag)s)" % ( self._prefix) cursor.execute(sql, data) id = self._lastrowid("TAGS") ids.append(id) self._tagidcache[pickled] = id except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def editnode(self, id, node): if not isinstance(node, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]" % node) try: sql = "UPDATE NODES SET DATA = ? WHERE ID = ?" self._cur.execute(sql, [cPickle.dumps(node), id]) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e))
def editnode(self, id, node): if not isinstance(node, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]" % node) try: cursor = self._get_cur() sql = "UPDATE %sNODES SET DATA = %%s WHERE ID = %%s" % (self._prefix) cursor.execute(sql, (cPickle.dumps(node), id)) except MySQL.DatabaseError, e: raise DatabaseException("MySQL: %s" % (e))
def removenodes(self, nodes): cursor = self._get_cur() for n in nodes: if not isinstance(n, Node): raise DatabaseException( "Tried to delete foreign object from database [%s]", n) try: sql = "DELETE FROM %sNODES WHERE ID = %%s" % (self._prefix) cursor.execute(sql, [n.get_id()]) except MySQLdb.DatabaseError, e: raise DatabaseException("MySQL: %s" % (e)) self._deletenodetags(n)
def createdb(dburi, version): dburi = urlparse(dburi) dbtype = dburi.scheme try: cls = getattr(drivers, create_db_map[dbtype][0]) return cls(create_db_map[dbtype][1](dburi)) except AttributeError: raise DatabaseException( '%s not installed? ' % class_db_map[dbtype][-1]) except KeyError: raise DatabaseException('Unknown database [%s] given ...' % (dbtype))
def editnode(self, id, node): if not isinstance(node, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]" % node) try: cursor = self._get_cur() sql = "UPDATE %sNODES SET DATA = %%(data)s WHERE ID = %%(id)d" % ( self._prefix) cursor.execute(sql, {"data": cPickle.dumps(node), "id": id}) except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def removenodes(self, nodes): for n in nodes: if not isinstance(n, Node): raise DatabaseException( "Tried to delete foreign object from database [%s]", n) try: sql = "DELETE FROM NODES WHERE ID = ?" self._cur.execute(sql, [n.get_id()]) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e)) self._deletenodetags(n)
def listnodes(self): sql = '' params = [] if len(self._filtertags) == 0: sql = "SELECT ID FROM NODES ORDER BY ID ASC" else: first = True for t in self._filtertags: if not first: sql += " INTERSECT " else: first = False sql += ( "SELECT NODE FROM LOOKUP OUTER JOIN TAGS ON TAG = TAGS.ID" + " WHERE TAGS.DATA = ? ") params.append(cPickle.dumps(t)) try: self._cur.execute(sql, params) ids = [] row = self._cur.fetchone() while (row != None): ids.append(row[0]) row = self._cur.fetchone() return ids except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e))
def addnodes(self, nodes): for n in nodes: sql = "INSERT INTO NODES(DATA) VALUES(?)" if not isinstance(n, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]", n) value = cPickle.dumps(n) try: self._cur.execute(sql, [value]) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e)) id = self._cur.lastrowid n.set_id(id) self._setnodetags(n) self._commit()
def _open(self): try: self._con = sqlite.connect(self._filename) self._cur = self._con.cursor() self._checktables() except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (s))
def addnodes(self, nodes): cursor = self._get_cur() for n in nodes: sql = "INSERT INTO %sNODES(DATA) VALUES(%%s)" % (self._prefix) if not isinstance(n, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]", n) values = [cPickle.dumps(n)] try: cursor.execute(sql, values) except MySQLdb.DatabaseError, e: raise DatabaseException("MySQL: %s" % (e)) id = cursor.lastrowid print "id: %d" % (id) n.set_id(id) self._setnodetags(n)
def _deletenodetags(self, node): try: sql = "DELETE FROM LOOKUP WHERE NODE = ?" self._cur.execute(sql, [node.get_id()]) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e))
def _commit(self): try: self._con.commit() except pgdb.DatabaseError, e: self._con.rollback() raise DatabaseException( "Postgresql: Error commiting data to db [%s]" % (e))
def listnodes(self): sql = '' params = {} i = 0 cursor = self._get_cur() if len(self._filtertags) == 0: sql = "SELECT ID FROM %sNODES ORDER BY ID ASC" % (self._prefix) else: first = True for t in self._filtertags: if not first: sql += " INTERSECT " else: first = False i += 1 paramname = "tag%d" % (i) sql += (( "SELECT NODE FROM %sLOOKUP LEFT JOIN %sTAGS ON TAG = %sTAGS.ID" + " WHERE %sTAGS.DATA = %%(%s)s ") % (self._prefix, self._prefix, self._prefix, self._prefix, paramname)) params[paramname] = cPickle.dumps(t) try: cursor.execute(sql, params) ids = [] row = cursor.fetchone() while (row != None): ids.append(row[0]) row = cursor.fetchone() return ids except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def getnodes(self, ids): nodes = [] idstr = "" first = True if len(ids) == 0: idstr = "-1" for i in ids: if first: idstr += "%d" % (i) first = False else: idstr += ", %d" % (i) sql = "SELECT ID,DATA FROM %sNODES WHERE ID IN (%s)" % ( self._prefix, pgdb.escape_string(idstr)) try: cursor = self._get_cur() cursor.execute(sql) row = cursor.fetchone() while row != None: node = cPickle.loads(str(row[1])) node.set_id(row[0]) nodes.append(node) row = cursor.fetchone() except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def _commit(self): try: self._con.commit() except sqlite.DatabaseError, e: self._con.rollback() raise DatabaseException("SQLite: Error commiting data to db [%s]" % (e))
def _commit(self): try: self._con.commit() except MySQLdb.DatabaseError, e: self._con.rollback() raise DatabaseException( "MySQL: Error commiting data to db [%s]" % (e))
def __init__(self): """Initialise SQLitePwmanDatabase instance.""" Database.__init__(self) try: self._filename = config.get_value('Database', 'filename') except KeyError, e: raise DatabaseException("SQLite: missing parameter [%s]" % (e))
def _deletenodetags(self, node): try: cursor = self._get_cur() sql = "DELETE FROM %sLOOKUP WHERE NODE = %%s" % (self._prefix) cursor.execute(sql, [node.get_id()]) except MySQLdb.DatabaseError, e: raise DatabaseException("MySQLdb: %s" % (e))
def addnodes(self, nodes): cursor = self._get_cur() for n in nodes: sql = "INSERT INTO %sNODES(DATA) VALUES(%%(data)s)" % ( self._prefix) if not isinstance(n, Node): raise DatabaseException( "Tried to insert foreign object into database [%s]", n) values = {"data": cPickle.dumps(n)} try: cursor.execute(sql, values) except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e)) id = self._lastrowid("NODES") n.set_id(id) self._setnodetags(n)
def _deletenodetags(self, node): try: cursor = self._get_cur() sql = "DELETE FROM %sLOOKUP WHERE NODE = %%(node)d" % ( self._prefix) cursor.execute(sql, {"node": node.get_id()}) except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def savekey(self, key): sql = "UPDATE KEY SET THEKEY = ?" values = [key] self._cur.execute(sql, values) try: self._con.commit() except sqlite.DatabaseError, e: self._con.rollback() raise DatabaseException("SQLite: Error saving key [%s]" % (e))
def _checktags(self): self._tagidcache.clear() try: cursor = self._get_cur() sql = ("DELETE FROM %sTAGS WHERE ID NOT IN " + "(SELECT TAG FROM %sLOOKUP GROUP BY TAG)") % (self._prefix, self._prefix) cursor.execute(sql) except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def savekey(self, key): sql = "UPDATE %sKEY SET THEKEY = %%(key)s" % (self._prefix) values = {"key": key} cursor = self._get_cur() cursor.execute(sql, values) try: self._con.commit() except pgdb.DatabaseError, e: self._con.rollback() raise DatabaseException("Postgresql: Error saving key [%s]" % (e))
def check_db_version(dburi): dburi = urlparse(dburi) dbtype = dburi.scheme try: cls = getattr(drivers, class_db_map[dbtype][0]) ver = cls.check_db_version(class_db_map[dbtype][1](dburi)) return ver except AttributeError: raise DatabaseException('%s not installed? ' % class_db_map[dbtype][-1])
def savekey(self, key): sql = "UPDATE %sKEY SET THEKEY = %%s" % (self._prefix) values = ( key ) cursor = self._get_cur() cursor.execute(sql, values) try: self._con.commit() except MySQLdb.DatabaseError, e: self._con.rollback() raise DatabaseException( "MySQL: Error saving key [%s]" % (e))
def _tagids(self, tags): ids = [] for t in tags: sql = "SELECT ID FROM TAGS WHERE DATA = ?" if not isinstance(t, Tag): raise DatabaseException( "Tried to insert foreign object into database [%s]", t) data = cPickle.dumps(t) try: self._cur.execute(sql, [data]) row = self._cur.fetchone() if (row != None): ids.append(row[0]) else: sql = "INSERT INTO TAGS(DATA) VALUES(?)" self._cur.execute(sql, [data]) ids.append(self._cur.lastrowid) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e))
def _open(self): try: self._con = None # server = "%s:%s" % (self._server, self._port) # self._con = pgdb.connect(host = server, # database = self._database, # user = self._user, # password = self._password) # self._cur = self._con.cursor() self._checktables() except pgdb.DatabaseError, e: raise DatabaseException("Postgresql: %s" % (e))
def _setnodetags(self, node): self._deletenodetags(node) ids = self._tagids(node.get_tags()) for i in ids: sql = "INSERT OR REPLACE INTO LOOKUP VALUES(?, ?)" params = [node.get_id(), i] try: self._cur.execute(sql, params) except sqlite.DatabaseError, e: raise DatabaseException("SQLite: %s" % (e))
def _setnodetags(self, node): self._deletenodetags(node) ids = self._tagids(node.get_tags()) for i in ids: sql = "INSERT INTO %sLOOKUP VALUES(%%s, %%s)" % (self._prefix) params = [ node.get_id(), i ] try: cursor = self._get_cur() cursor.execute(sql, params) except MySQLdb.DatabaseError, e: raise DatabaseException("MySQLdb: %s" % (e))