def _removeContents(self, parent, positions):
with db.transaction():
db.multiQuery("DELETE FROM {p}contents WHERE container_id=? AND position=?",
[(parent.id, pos) for pos in positions])
db.updateElementsCounter((parent.id,))
super()._removeContents(parent, positions)
def _changeTags(self, changes, dbOnly=False):
"""Change tags without undo/redo support.
:param bool dbOnly: If *True*, only change tags in database, not in the actual file.
"""
if not dbOnly:
changeTags(changes) # might raise TagWriteError
dbChanges = {el: diffs for el, diffs in changes.items() if el.isInDb()}
if len(dbChanges) > 0:
with db.transaction():
dbRemovals = [(el.id, tag.id, db.tags.id(tag, value))
for el, diff in dbChanges.items()
for tag, value in diff.getRemovals() if tag.isInDb()]
if len(dbRemovals):
db.multiQuery('DELETE FROM {p}tags WHERE element_id=? AND tag_id=? AND value_id=?',
dbRemovals)
dbAdditions = [(el.id, tag.id, db.tags.id(tag, value, insert=True))
for el, diff in dbChanges.items()
for tag, value in diff.getAdditions() if tag.isInDb()]
if len(dbAdditions):
db.multiQuery('INSERT INTO {p}tags (element_id, tag_id, value_id) VALUES (?,?,?)',
dbAdditions)
files = [ (elem.id, time.time()) for elem in dbChanges if elem.isFile() ]
if len(files) > 0:
db.multiQuery('UPDATE {p}files SET verified=? WHERE element_id=?', files)
super()._changeTags(changes)
def _removeContents(self, parent, positions):
with db.transaction():
db.multiQuery(
"DELETE FROM {p}contents WHERE container_id=? AND position=?",
[(parent.id, pos) for pos in positions])
db.updateElementsCounter((parent.id, ))
super()._removeContents(parent, positions)
def _insertContents(self, parent, insertions):
if not all(element.isInDb() for _, element in insertions):
raise levels.ConsistencyError("Elements must be in the DB before being added to a container.")
with db.transaction():
db.multiQuery("INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
[(parent.id, pos, child.id) for pos, child in insertions])
db.updateElementsCounter((parent.id,))
super()._insertContents(parent, insertions)
def _insertContents(self, parent, insertions):
if not all(element.isInDb() for _, element in insertions):
raise levels.ConsistencyError(
"Elements must be in the DB before being added to a container."
)
with db.transaction():
db.multiQuery(
"INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
[(parent.id, pos, child.id) for pos, child in insertions])
db.updateElementsCounter((parent.id, ))
super()._insertContents(parent, insertions)
def _changeFlags(self, changes):
if not all(element.isInDb() for element in changes.keys()):
raise levels.ConsistencyError("Elements on real must be added to the DB before adding tags.")
with db.transaction():
dbRemovals = [(el.id, flag.id) for el, diff in changes.items() for flag in diff.getRemovals()]
if len(dbRemovals):
db.multiQuery("DELETE FROM {p}flags WHERE element_id = ? AND flag_id = ?",
dbRemovals)
dbAdditions = [(el.id, flag.id) for el, diff in changes.items() for flag in diff.getAdditions()]
if len(dbAdditions):
db.multiQuery("INSERT INTO {p}flags (element_id, flag_id) VALUES(?,?)",
dbAdditions)
super()._changeFlags(changes)
def _setStickers(self, type, elementToStickers):
if not all(element.isInDb() for element in elementToStickers.keys()):
raise levels.ConsistencyError("Elements on real must be added to the DB before adding stickers.")
values = []
for element, stickers in elementToStickers.items():
if stickers is not None:
values.extend((element.id, type, i, s) for i, s in enumerate(stickers))
with db.transaction():
db.query("DELETE FROM {}stickers WHERE type = ? AND element_id IN ({})"
.format(db.prefix, db.csIdList(elementToStickers.keys())), type)
if len(values) > 0:
db.multiQuery("INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
values)
super()._setStickers(type, elementToStickers)
def _changeStickers(self, changes):
if not all(element.isInDb() for element in changes.keys()):
raise levels.ConsistencyError("Elements on real must be added to the DB before adding stickers.")
with db.transaction():
for element, diff in changes.items():
for type, (a, b) in diff.diffs.items():
if a is not None:
db.query("DELETE FROM {p}stickers WHERE type=? AND element_id=?",
type, element.id)
if b is not None:
db.multiQuery(
"INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
[(element.id, type, i, val) for i, val in enumerate(b)])
super()._changeStickers(changes)
def _setContents(self, parent, contents):
with db.transaction():
db.query("DELETE FROM {p}contents WHERE container_id = ?", parent.id)
#Note: This checks skips elements which are not loaded on real. This should rarely happen and
# due to foreign key constraints...
if not all(self[childId].isInDb() for childId in contents if childId in self):
raise levels.ConsistencyError("Elements must be in the DB before being added to a container.")
if len(contents) > 0:
# ...the following query will fail anyway (but with a DBException)
# if some contents are not in the database yet.
db.multiQuery("INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
[(parent.id, pos, childId) for pos, childId in contents.items()])
db.updateElementsCounter((parent.id,))
super()._setContents(parent, contents)
def _changeStickers(self, changes):
if not all(element.isInDb() for element in changes.keys()):
raise levels.ConsistencyError(
"Elements on real must be added to the DB before adding stickers."
)
with db.transaction():
for element, diff in changes.items():
for type, (a, b) in diff.diffs.items():
if a is not None:
db.query(
"DELETE FROM {p}stickers WHERE type=? AND element_id=?",
type, element.id)
if b is not None:
db.multiQuery(
"INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
[(element.id, type, i, val)
for i, val in enumerate(b)])
super()._changeStickers(changes)
def _changeFlags(self, changes):
if not all(element.isInDb() for element in changes.keys()):
raise levels.ConsistencyError(
"Elements on real must be added to the DB before adding tags.")
with db.transaction():
dbRemovals = [(el.id, flag.id) for el, diff in changes.items()
for flag in diff.getRemovals()]
if len(dbRemovals):
db.multiQuery(
"DELETE FROM {p}flags WHERE element_id = ? AND flag_id = ?",
dbRemovals)
dbAdditions = [(el.id, flag.id) for el, diff in changes.items()
for flag in diff.getAdditions()]
if len(dbAdditions):
db.multiQuery(
"INSERT INTO {p}flags (element_id, flag_id) VALUES(?,?)",
dbAdditions)
super()._changeFlags(changes)
def _setStickers(self, type, elementToStickers):
if not all(element.isInDb() for element in elementToStickers.keys()):
raise levels.ConsistencyError(
"Elements on real must be added to the DB before adding stickers."
)
values = []
for element, stickers in elementToStickers.items():
if stickers is not None:
values.extend(
(element.id, type, i, s) for i, s in enumerate(stickers))
with db.transaction():
db.query(
"DELETE FROM {}stickers WHERE type = ? AND element_id IN ({})".
format(db.prefix, db.csIdList(elementToStickers.keys())), type)
if len(values) > 0:
db.multiQuery(
"INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
values)
super()._setStickers(type, elementToStickers)
def _setContents(self, parent, contents):
with db.transaction():
db.query("DELETE FROM {p}contents WHERE container_id = ?",
parent.id)
#Note: This checks skips elements which are not loaded on real. This should rarely happen and
# due to foreign key constraints...
if not all(self[childId].isInDb()
for childId in contents if childId in self):
raise levels.ConsistencyError(
"Elements must be in the DB before being added to a container."
)
if len(contents) > 0:
# ...the following query will fail anyway (but with a DBException)
# if some contents are not in the database yet.
db.multiQuery(
"INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
[(parent.id, pos, childId)
for pos, childId in contents.items()])
db.updateElementsCounter((parent.id, ))
super()._setContents(parent, contents)
def _changeTags(self, changes, dbOnly=False):
"""Change tags without undo/redo support.
:param bool dbOnly: If *True*, only change tags in database, not in the actual file.
"""
if not dbOnly:
changeTags(changes) # might raise TagWriteError
dbChanges = {el: diffs for el, diffs in changes.items() if el.isInDb()}
if len(dbChanges) > 0:
with db.transaction():
dbRemovals = [(el.id, tag.id, db.tags.id(tag, value))
for el, diff in dbChanges.items()
for tag, value in diff.getRemovals()
if tag.isInDb()]
if len(dbRemovals):
db.multiQuery(
'DELETE FROM {p}tags WHERE element_id=? AND tag_id=? AND value_id=?',
dbRemovals)
dbAdditions = [(el.id, tag.id,
db.tags.id(tag, value, insert=True))
for el, diff in dbChanges.items()
for tag, value in diff.getAdditions()
if tag.isInDb()]
if len(dbAdditions):
db.multiQuery(
'INSERT INTO {p}tags (element_id, tag_id, value_id) VALUES (?,?,?)',
dbAdditions)
files = [(elem.id, time.time()) for elem in dbChanges
if elem.isFile()]
if len(files) > 0:
db.multiQuery(
'UPDATE {p}files SET verified=? WHERE element_id=?',
files)
super()._changeTags(changes)
def _addToDb(self, elements):
"""Like addToDb but not undoable."""
if len(elements) == 0:
return # multiquery will fail otherwise
for element in elements:
assert not element.isInDb()
assert element.level is self
if element.id not in self:
assert element.isContainer()
self.elements[element.id] = element
else:
assert element.isFile()
with db.transaction():
data = [(element.domain.id, element.id, element.isFile(),
element.type.value if element.isContainer() else 0,
len(element.contents) if element.isContainer() else 0)
for element in elements]
db.multiQuery(
"INSERT INTO {p}elements (domain, id, file, type, elements) VALUES (?,?,?,?,?)",
data)
# Do this early, otherwise e.g. setFlags might raise a ConsistencyError)
_dbIds.update(element.id for element in elements)
for element in elements:
# Set tags
db.query("DELETE FROM {p}tags WHERE element_id = ?",
element.id)
for tag in element.tags:
db.multiQuery(
"INSERT INTO {p}tags (element_id,tag_id,value_id) VALUES (?,?,?)",
[(element.id, tag.id,
db.tags.id(tag, value, insert=True))
for value in element.tags[tag]])
# Set flags
db.query("DELETE FROM {p}flags WHERE element_id = ?",
element.id)
if len(element.flags) > 0:
db.multiQuery(
"INSERT INTO {p}flags (element_id, flag_id) VALUES (?,?)",
[(element.id, flag.id) for flag in element.flags])
# Set stickers
db.query("DELETE FROM {p}stickers WHERE element_id = ?",
element.id)
for stickerType, values in element.stickers.items():
db.multiQuery(
"INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
[(element.id, stickerType, i, val)
for i, val in enumerate(values)])
newFiles = [element for element in elements if element.isFile()]
if len(newFiles) > 0:
from .. import filesystem
db.multiQuery(
'INSERT INTO {p}files (element_id, url, hash, verified, length)'
'VALUES (?, ?, ?, ?, ?)', [(element.id, str(
element.url), filesystem.getNewfileHash(
element.url), time.time(), element.length)
for element in newFiles])
self.emitFilesystemEvent(
added=[f for f in newFiles if f.url.scheme == 'file'])
contentData = []
for element in elements:
if element.isContainer():
contentData.extend((element.id, item[0], item[1])
for item in element.contents.items())
for childId in element.contents:
if element.id not in self[childId].parents:
self[childId].parents.append(element.id)
if len(contentData) > 0:
db.multiQuery(
"INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
contentData)
self.emit(
levels.LevelChangeEvent(dbAddedIds=[el.id for el in elements]))
def _addToDb(self, elements):
"""Like addToDb but not undoable."""
if len(elements) == 0:
return # multiquery will fail otherwise
for element in elements:
assert not element.isInDb()
assert element.level is self
if element.id not in self:
assert element.isContainer()
self.elements[element.id] = element
else: assert element.isFile()
with db.transaction():
data = [(element.domain.id,
element.id,
element.isFile(),
element.type.value if element.isContainer() else 0,
len(element.contents) if element.isContainer() else 0)
for element in elements]
db.multiQuery("INSERT INTO {p}elements (domain, id, file, type, elements) VALUES (?,?,?,?,?)",
data)
# Do this early, otherwise e.g. setFlags might raise a ConsistencyError)
_dbIds.update(element.id for element in elements)
for element in elements:
# Set tags
db.query("DELETE FROM {p}tags WHERE element_id = ?", element.id)
for tag in element.tags:
db.multiQuery("INSERT INTO {p}tags (element_id,tag_id,value_id) VALUES (?,?,?)",
[(element.id, tag.id, db.tags.id(tag, value, insert=True))
for value in element.tags[tag]])
# Set flags
db.query("DELETE FROM {p}flags WHERE element_id = ?", element.id)
if len(element.flags) > 0:
db.multiQuery("INSERT INTO {p}flags (element_id, flag_id) VALUES (?,?)",
[(element.id, flag.id) for flag in element.flags])
# Set stickers
db.query("DELETE FROM {p}stickers WHERE element_id = ?", element.id)
for stickerType, values in element.stickers.items():
db.multiQuery("INSERT INTO {p}stickers (element_id, type, sort, data) VALUES (?,?,?,?)",
[(element.id, stickerType, i, val) for i, val in enumerate(values)])
newFiles = [element for element in elements if element.isFile()]
if len(newFiles) > 0:
from .. import filesystem
db.multiQuery('INSERT INTO {p}files (element_id, url, hash, verified, length)'
'VALUES (?, ?, ?, ?, ?)',
[(element.id, str(element.url), filesystem.getNewfileHash(element.url),
time.time(), element.length) for element in newFiles])
self.emitFilesystemEvent(added=[f for f in newFiles if f.url.scheme == 'file'])
contentData = []
for element in elements:
if element.isContainer():
contentData.extend((element.id, item[0], item[1]) for item in element.contents.items())
for childId in element.contents:
if element.id not in self[childId].parents:
self[childId].parents.append(element.id)
if len(contentData) > 0:
db.multiQuery("INSERT INTO {p}contents (container_id, position, element_id) VALUES (?,?,?)",
contentData)
self.emit(levels.LevelChangeEvent(dbAddedIds=[el.id for el in elements]))
def runTest(self):
# Create the table
if self.type == 'mysql':
db.query("""
CREATE TEMPORARY TABLE {}{} (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
age INT NOT NULL,
size DOUBLE NOT NULL,
male BOOLEAN NOT NULL,
death INT NULL DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE InnoDB, CHARACTER SET 'utf8';
""".format(db.prefix, testTable))
else:
db.query("""
CREATE TABLE {}{} (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(30) NOT NULL,
age INT NOT NULL,
size DOUBLE NOT NULL,
male BOOLEAN NOT NULL,
death INT NULL DEFAULT NULL
)
""".format(db.prefix, testTable))
# Fill it with data
result = db.query(
"INSERT INTO {}{} (name,age,size,male,death) VALUES (?,?,?,?,?)".
format(db.prefix, testTable), *data[0]) # without death column
self.assertEqual(result.insertId(), 1)
result = db.multiQuery(
"INSERT INTO {}{} (name,age,size,male,death) VALUES (?,?,?,?,?)".
format(db.prefix, testTable), data[1:])
# Neither affectedRows nor insertId are equal for different drivers after a multiQuery
self.assertEqual(
db.query("SELECT COUNT(*) FROM {}{}".format(
db.prefix, testTable)).getSingle(), 4)
# And retrieve it again
result = db.query(
"SELECT id,name,age,size,male,death FROM {}{} ORDER BY id".format(
db.prefix, testTable))
self.assertEqual(len(result), 4)
for i, row in enumerate(result):
self.assertEqual(i + 1, row[0]) # id
for j in range(5):
self.assertEqual(
data[i][j],
row[j +
1] if j + 1 < 5 else utils.FlexiDate.fromSql(row[j +
1]))
# Check getSingle* methods
result = db.query(
"SELECT id FROM {}{} WHERE age = ?".format(db.prefix, testTable),
24)
self.assertEqual(result.getSingle(), 1)
result = db.query("SELECT id FROM {}{} ORDER BY id".format(
db.prefix, testTable))
for i, v in enumerate(result.getSingleColumn()):
self.assertEqual(i + 1, v)
result = db.query(
"SELECT id,age FROM {}{} WHERE id = ?".format(
db.prefix, testTable), 2)
row = result.getSingleRow()
self.assertEqual(row[0], 2)
self.assertEqual(row[1], data[1][1])
# Start modifying the data
result = db.query("DELETE FROM {}{} WHERE death IS NOT NULL".format(
db.prefix, testTable))
self.assertEqual(result.affectedRows(), 1)
# Test transactions
db.transaction()
for i in range(1, 4):
db.query(
"UPDATE {}{} SET age=age+1 WHERE id = ?".format(
db.prefix, testTable), i)
db.commit()
result = db.query("SELECT age FROM {}{} ORDER BY id".format(
db.prefix, testTable))
self.assertListEqual(list(result.getSingleColumn()), [25, 23, 22])
db.transaction()
for i in range(1, 4):
db.query("UPDATE {}{} SET death = ?".format(db.prefix, testTable),
utils.FlexiDate(2000))
db.rollback()
result = db.query("SELECT death FROM {}{}".format(
db.prefix, testTable))
self.assertListEqual(
list(
utils.FlexiDate.fromSql(value)
for value in result.getSingleColumn()), 3 * [None])
# Check exceptions
self.assertRaises(db.sql.DBException, lambda: db.query("STUPID QUERY"))
result = db.query("SELECT * FROM {}{} WHERE death IS NOT NULL".format(
db.prefix, testTable))
self.assertRaises(db.sql.EmptyResultException, result.getSingle)
self.assertRaises(db.sql.EmptyResultException, result.getSingleRow)
def runTest(self):
# Create the table
if self.type == 'mysql':
db.query("""
CREATE TEMPORARY TABLE {}{} (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
age INT NOT NULL,
size DOUBLE NOT NULL,
male BOOLEAN NOT NULL,
death INT NULL DEFAULT NULL,
PRIMARY KEY(id)
) ENGINE InnoDB, CHARACTER SET 'utf8';
""".format(db.prefix,testTable)
)
else:
db.query("""
CREATE TABLE {}{} (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(30) NOT NULL,
age INT NOT NULL,
size DOUBLE NOT NULL,
male BOOLEAN NOT NULL,
death INT NULL DEFAULT NULL
)
""".format(db.prefix,testTable)
)
# Fill it with data
result = db.query("INSERT INTO {}{} (name,age,size,male,death) VALUES (?,?,?,?,?)"
.format(db.prefix,testTable),*data[0]) # without death column
self.assertEqual(result.insertId(),1)
result = db.multiQuery("INSERT INTO {}{} (name,age,size,male,death) VALUES (?,?,?,?,?)"
.format(db.prefix,testTable),data[1:])
# Neither affectedRows nor insertId are equal for different drivers after a multiQuery
self.assertEqual(db.query("SELECT COUNT(*) FROM {}{}".format(db.prefix,testTable)).getSingle(),4)
# And retrieve it again
result = db.query("SELECT id,name,age,size,male,death FROM {}{} ORDER BY id".format(db.prefix,testTable))
self.assertEqual(len(result),4)
for i,row in enumerate(result):
self.assertEqual(i+1,row[0]) # id
for j in range(5):
self.assertEqual(data[i][j],row[j+1] if j+1<5 else utils.FlexiDate.fromSql(row[j+1]))
# Check getSingle* methods
result = db.query("SELECT id FROM {}{} WHERE age = ?".format(db.prefix,testTable),24)
self.assertEqual(result.getSingle(),1)
result = db.query("SELECT id FROM {}{} ORDER BY id".format(db.prefix,testTable))
for i,v in enumerate(result.getSingleColumn()):
self.assertEqual(i+1,v)
result = db.query("SELECT id,age FROM {}{} WHERE id = ?".format(db.prefix,testTable),2)
row = result.getSingleRow()
self.assertEqual(row[0],2)
self.assertEqual(row[1],data[1][1])
# Start modifying the data
result = db.query("DELETE FROM {}{} WHERE death IS NOT NULL".format(db.prefix,testTable))
self.assertEqual(result.affectedRows(),1)
# Test transactions
db.transaction()
for i in range(1,4):
db.query("UPDATE {}{} SET age=age+1 WHERE id = ?".format(db.prefix,testTable),i)
db.commit()
result = db.query("SELECT age FROM {}{} ORDER BY id".format(db.prefix,testTable))
self.assertListEqual(list(result.getSingleColumn()),[25,23,22])
db.transaction()
for i in range(1,4):
db.query("UPDATE {}{} SET death = ?".format(db.prefix,testTable),utils.FlexiDate(2000))
db.rollback()
result = db.query("SELECT death FROM {}{}".format(db.prefix,testTable))
self.assertListEqual(list(utils.FlexiDate.fromSql(value) for value in result.getSingleColumn()),
3*[None])
# Check exceptions
self.assertRaises(db.sql.DBException,lambda: db.query("STUPID QUERY"))
result = db.query("SELECT * FROM {}{} WHERE death IS NOT NULL".format(db.prefix,testTable))
self.assertRaises(db.sql.EmptyResultException,result.getSingle)
self.assertRaises(db.sql.EmptyResultException,result.getSingleRow)