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