def finish(self):
items = [item for item in self.domainManager.model.items
if item[0]] # enabled items
if len(items) == 0:
QtWidgets.QMessageBox.warning(
self, self.tr('No domain'),
self.tr('Please create and activate at least one domain.'))
return False
if any(
len(set(item[i] for item in items)) < len(items)
for i in (1, 2)):
QtWidgets.QMessageBox.warning(
self, self.tr('Names not unique'),
self.tr('Please give each domain a unique name and path.'))
return False
db.multiQuery('INSERT INTO {p}domains (name) VALUES (?)',
[(item[1], ) for item in items])
# Create one source for each domain
config.storage.filesystem.sources = [{
'name': item[1],
'path': item[2],
'domain': item[0],
'enabled': True
} for item in items]
return True
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 finish(self):
"""Read tag information from table, check it (invalid or duplicate tag names?) and write it to the
tagids table."""
# Read tags
tags = collections.OrderedDict()
for row in range(self.tableWidget.rowCount()):
column = self._getColumnIndex('name')
if self.tableWidget.item(row, column).checkState() != Qt.Checked:
continue
name = self.tableWidget.item(row, column).text()
# Check invalid tag names
if not isValidTagName(name):
QtWidgets.QMessageBox.warning(
self, self.tr("Invalid tagname"),
self.tr("'{}' is not a valid tagname.").format(name))
return False
# Check duplicate tag names
if name in tags:
QtWidgets.QMessageBox.warning(
self, self.tr("Some tags have the same name"),
self.tr("There is more than one tag with name '{}'.").
format(name))
return False
column = self._getColumnIndex('type')
if row <= 1:
valueType = self.tableWidget.item(row, column).text()
else:
valueType = self.tableWidget.indexWidget(
self.tableWidget.model().index(row, column)).currentText()
column = self._getColumnIndex('title')
title = self.tableWidget.item(row, column).text()
if title == '':
title = None
column = self._getColumnIndex('icon')
iconLabel = self.tableWidget.indexWidget(
self.tableWidget.model().index(row, column))
icon = iconLabel.path
column = self._getColumnIndex('private')
private = self.tableWidget.item(row,
column).checkState() == Qt.Checked
tags[name] = (name, valueType, title, icon, 1 if private else 0,
row + 1)
# Write tags to database
assert db.query("SELECT COUNT(*) FROM {}tagids".format(
db.prefix)).getSingle() == 0
db.multiQuery(
"INSERT INTO {}tagids (tagname, tagtype, title, icon, private, sort)"
" VALUES (?,?,?,?,?,?)".format(db.prefix), tags.values())
# The first two tags are used as title and album. popitem returns a (key, value) tuple.
config.options.tags.title_tag = tags.popitem(last=False)[0]
config.options.tags.album_tag = tags.popitem(last=False)[0]
return True
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 updateHashesAndVerified(self, files):
"""Updates hash and verification timestamp of given *files* in the database to the values stored in
the according File objects.
"""
dbFiles = [(f.hash, f.verified, f.id) for f in files if f.id]
newFiles = [(f.hash, f.verified, str(f.url)) for f in files if not f.id]
if len(dbFiles):
db.multiQuery('UPDATE {p}files SET hash=?, verified=? WHERE element_id=?', dbFiles)
if len(newFiles):
db.multiQuery('UPDATE {p}newfiles SET hash=?, verified=? WHERE url=?', newFiles)
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 _changePositions(self, parent, changes):
super()._changePositions(parent, changes)
changes = list(changes.items())
changesOne = [ (newPos, parent.id, oldPos)
for (oldPos, newPos) in sorted(changes, key=lambda cng: cng[1], reverse=True)
if newPos > oldPos ]
changesTwo = [ (newPos, parent.id, oldPos)
for (oldPos, newPos) in sorted(changes, key=lambda chng: chng[1])
if newPos < oldPos ]
for data in changesOne, changesTwo:
if len(data):
db.multiQuery("UPDATE {p}contents SET position=? WHERE container_id=? AND position=?", data)
def finish(self):
"""Read tag information from table, check it (invalid or duplicate tag names?) and write it to the
tagids table."""
# Read tags
tags = collections.OrderedDict()
for row in range(self.tableWidget.rowCount()):
column = self._getColumnIndex('name')
if self.tableWidget.item(row, column).checkState() != Qt.Checked:
continue
name = self.tableWidget.item(row, column).text()
# Check invalid tag names
if not isValidTagName(name):
QtWidgets.QMessageBox.warning(self, self.tr("Invalid tagname"),
self.tr("'{}' is not a valid tagname.").format(name))
return False
# Check duplicate tag names
if name in tags:
QtWidgets.QMessageBox.warning(self, self.tr("Some tags have the same name"),
self.tr("There is more than one tag with name '{}'.").format(name))
return False
column = self._getColumnIndex('type')
if row <= 1:
valueType = self.tableWidget.item(row, column).text()
else: valueType = self.tableWidget.indexWidget(
self.tableWidget.model().index(row, column)).currentText()
column = self._getColumnIndex('title')
title = self.tableWidget.item(row, column).text()
if title == '':
title = None
column = self._getColumnIndex('icon')
iconLabel = self.tableWidget.indexWidget(self.tableWidget.model().index(row, column))
icon = iconLabel.path
column = self._getColumnIndex('private')
private = self.tableWidget.item(row, column).checkState() == Qt.Checked
tags[name] = (name, valueType, title, icon, 1 if private else 0, row+1)
# Write tags to database
assert db.query("SELECT COUNT(*) FROM {}tagids".format(db.prefix)).getSingle() == 0
db.multiQuery("INSERT INTO {}tagids (tagname, tagtype, title, icon, private, sort)"
" VALUES (?,?,?,?,?,?)"
.format(db.prefix), tags.values())
# The first two tags are used as title and album. popitem returns a (key, value) tuple.
config.options.tags.title_tag = tags.popitem(last=False)[0]
config.options.tags.album_tag = tags.popitem(last=False)[0]
return True
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 _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 _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 _changePositions(self, parent, changes):
super()._changePositions(parent, changes)
changes = list(changes.items())
changesOne = [(newPos, parent.id, oldPos) for (
oldPos,
newPos) in sorted(changes, key=lambda cng: cng[1], reverse=True)
if newPos > oldPos]
changesTwo = [(newPos, parent.id, oldPos)
for (oldPos,
newPos) in sorted(changes, key=lambda chng: chng[1])
if newPos < oldPos]
for data in changesOne, changesTwo:
if len(data):
db.multiQuery(
"UPDATE {p}contents SET position=? WHERE container_id=? AND position=?",
data)
def _renameFiles(self, renamings):
"""On the real level, files are renamed both on disk and in DB."""
doneFiles = []
try:
for elem, (oldUrl, newUrl) in renamings.items():
oldUrl.backendFile().rename(newUrl)
doneFiles.append(elem)
except OSError as e:
# rollback changes and throw error
for elem in doneFiles:
oldUrl, newUrl = renamings[elem]
newUrl.backendFile().rename(oldUrl)
raise levels.RenameFilesError(oldUrl, newUrl, str(e))
db.multiQuery("UPDATE {p}files SET url=? WHERE element_id=?",
[ (str(newUrl), element.id) for element, (_, newUrl) in renamings.items() ])
super()._renameFiles(renamings)
def _renameFiles(self, renamings):
"""On the real level, files are renamed both on disk and in DB."""
doneFiles = []
try:
for elem, (oldUrl, newUrl) in renamings.items():
oldUrl.backendFile().rename(newUrl)
doneFiles.append(elem)
except OSError as e:
# rollback changes and throw error
for elem in doneFiles:
oldUrl, newUrl = renamings[elem]
newUrl.backendFile().rename(oldUrl)
raise levels.RenameFilesError(oldUrl, newUrl, str(e))
db.multiQuery("UPDATE {p}files SET url=? WHERE element_id=?",
[(str(newUrl), element.id)
for element, (_, newUrl) in renamings.items()])
super()._renameFiles(renamings)
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 _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 _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 finish(self):
items = [item for item in self.domainManager.model.items if item[0]] # enabled items
if len(items) == 0:
QtWidgets.QMessageBox.warning(self, self.tr('No domain'),
self.tr('Please create and activate at least one domain.'))
return False
if any(len(set(item[i] for item in items)) < len(items) for i in (1,2)):
QtWidgets.QMessageBox.warning(self, self.tr('Names not unique'),
self.tr('Please give each domain a unique name and path.'))
return False
db.multiQuery('INSERT INTO {p}domains (name) VALUES (?)', [(item[1],) for item in items])
# Create one source for each domain
config.storage.filesystem.sources = [
{'name': item[1], 'path': item[2], 'domain': item[0], 'enabled': True}
for item in items
]
return True
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 load(self):
"""Load files and newfiles tables, creating the internal structure of File and Folder objects."""
for elid, urlstring, elhash, verified in db.query(
'SELECT element_id, url, hash, verified FROM {p}files WHERE url LIKE ' +
"'{}%'".format('file://' + self.path.replace("'", "\\'"))):
url = urls.URL(urlstring)
if url.extension in self.extensions:
self.addFile(url, id=elid, verified=verified, hash=elhash, store=False)
toDelete = []
for urlstring, elhash, verified in db.query("SELECT url, hash, verified FROM {p}newfiles "
+ "WHERE url LIKE '{}%'".format('file://' + self.path.replace("'", "\\'"))):
url = urls.URL(urlstring)
if url.extension in self.extensions:
if url.path in self.files:
toDelete.append((urlstring,))
continue
self.addFile(url, hash=elhash, verified=verified, store=False)
else:
toDelete.append((urlstring,))
if len(toDelete):
db.multiQuery('DELETE FROM {p}newfiles WHERE url=?', toDelete)
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 removeFiles(self, files):
"""Removes given files from structure and database.
:type files: list of File
"""
if len(files) == 0:
return
urlstrings = []
for file in files:
folder = file.folder
folder.files.remove(file)
while folder.empty():
# recursively delete empty parent folders
folder.updateState(False, emit=self.folderStateChanged)
del self.folders[folder.path]
if folder.parent:
folder.parent.subdirs.remove(folder)
folder = folder.parent
else:
break
urlstrings.append((str(file.url),))
del self.files[file.url.path]
if len(urlstrings):
db.multiQuery("DELETE FROM {p}newfiles WHERE url=?", urlstrings)
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 storeNewFiles(self, newfiles):
"""Inserts the given list of :class:`File` objects into the newfiles table."""
if len(newfiles):
db.multiQuery('INSERT INTO {p}newfiles (url, hash, verified) VALUES (?,?,?)',
[(str(file.url), file.hash, file.verified) for file in newfiles])
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 _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 _setTypes(self, containerTypes):
if len(containerTypes) > 0:
db.multiQuery("UPDATE {p}elements SET type = ? WHERE id = ?",
((type.value, c.id)
for c, type in containerTypes.items()))
super()._setTypes(containerTypes)
def _setTypes(self, containerTypes):
if len(containerTypes) > 0:
db.multiQuery("UPDATE {p}elements SET type = ? WHERE id = ?",
((type.value, c.id) for c, type in containerTypes.items()))
super()._setTypes(containerTypes)
def handleRealFileEvent(self, event):
"""Handle an event issued by levels.real if something has affected the filesystem.
Updates the internal directory tree structure, and recomputes hashes if necessary.
"""
if self.scanState not in (ScanState.notScanning, ScanState.realHashOnly):
self.scanInterrupted = True
updateHash = set() # paths for which new hashes need to be computed
for oldURL, newURL in event.renamed:
if oldURL.path in self.files:
if self.files[oldURL.path].id is None:
db.query('DELETE FROM {p}newfiles WHERE url=?', str(oldURL))
if self.contains(newURL.path):
self.moveFile(self.files[oldURL.path], newURL)
else:
self.removeFiles([self.files[oldURL.path]])
elif self.contains(newURL.path):
elem = levels.real.fetch(newURL)
self.addFile(url=newURL, id=elem.id)
updateHash.add(newURL.path)
for url in event.modified:
if self.contains(url.path):
updateHash.add(url.path) # recompute hash if file was modified
if len(event.added) > 0:
db.multiQuery('DELETE FROM {p}newfiles WHERE url=?', [(str(elem.url),) for elem in event.added])
for elem in event.added:
if self.contains(elem.url.path):
url = elem.url
if url.path not in self.files:
file = self.addFile(url, id=elem.id)
else:
file = self.files[url.path]
if file.hash is None:
updateHash.add(url.path)
file.id = elem.id
file.folder.updateState(True, emit=self.folderStateChanged)
self.fileStateChanged.emit(url.path)
if len(updateHash) > 0:
self.hashThread.lastJobDone.clear()
for path in updateHash:
self.hashThread.jobQueue.put(HashRequest(priority=-1, path=path))
if self.scanState == ScanState.notScanning:
self.scanState = ScanState.realHashOnly
self.scanTimer.start(5000)
if len(event.removed) > 0: # removed means deleted from DB, but not filesystem
newFiles = []
for url in event.removed:
if url.path not in self.files:
continue
file = self.files[url.path]
newFiles.append(file)
file.id = None
self.fileStateChanged.emit(url.path)
file.folder.updateState(True, emit=self.folderStateChanged)
self.storeNewFiles(newFiles)
if len(event.deleted) > 0:
self.removeFiles([self.files[url.path] for url in event.deleted if url.path in self.files])
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)
