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))
