def runTest(self):
if not self.real:
self.assertEqual(self.level.elements, {}) # this is important on undo
self.f1 = self.level.collect(TestUrl('test://band 1 - song'))
# due to the fixed url->id mapping, these id is the same on real and editor
self.assertEqual(self.f1.id, 1)
self.assertIn(self.f1.id, self.level)
self.f2 = self.level.collect(TestUrl('test://band 2 - a song'))
self.f3 = self.level.collect(TestUrl('test://band 3 - another song'))
self.f4 = self.level.collect(TestUrl('test://band 4 - no song'))
containerTags = tags.Storage({tags.TITLE: ['Weird album']})
if self.real: # On real level createContainer does not work until we added the contents to the db
self.assertEqual(0, db.query("SELECT COUNT(*) FROM {}elements".format(db.prefix)).getSingle())
from maestro.core import reallevel
self.assertEqual(reallevel._dbIds,set())
self.level.addToDb([self.f1, self.f2, self.f3, self.f4])
self.assertEqual(4, db.query("SELECT COUNT(*) FROM {}elements".format(db.prefix)).getSingle())
self.assertEqual(reallevel._dbIds,set([1,2,3,4]))
# note that this id will be different when this test is run for editor and real level
predictedId = db._nextId
self.assertNotIn(predictedId, self.level)
self.assertEqual(self.f1.parents, [])
self.c = self.level.createContainer(tags=containerTags, contents=[self.f1, self.f2, self.f3])
self.assertEqual(self.c.id, predictedId)
self.assertIn(predictedId, self.level)
self.assertEqual(self.c.contents,
elements.ContentList.fromList([self.f1.id, self.f2.id, self.f3.id]))
self.assertEqual(self.f1.parents, [self.c.id])
self.checkUndo()
self.checkRedo()
def setUp(self):
super().setUp()
self.level.elements = {}
if self.real:
db.query("DELETE FROM {}elements".format(db.prefix))
from maestro.core import reallevel
reallevel._dbIds = set()
def accept(self):
from maestro.plugins.coverdesk.plugin import StackItem, CoverItem
stack = StackItem(self.titleEdit.text())
if self.allButton.isChecked():
toplevel = list(db.query("""
SELECT id
FROM {p}elements
WHERE domain=? AND id NOT IN (SELECT element_id FROM {p}contents)
ORDER BY id
""", self.scene.domain.id).getSingleColumn())
elements = levels.real.collect(toplevel)
stack.items = [CoverItem(self.scene, el) for el in elements]
elif self.searchButton.isChecked():
criterion = self.searchBox.criterion
if criterion is None:
return # do not accept
search.search(criterion, domain=self.scene.domain)
elids = criterion.result
toplevel = set(elids)
toplevel.difference_update(db.query(
"SELECT element_id FROM {p}contents WHERE container_id IN ({elids})",
elids=db.csList(elids)).getSingleColumn())
toplevel = sorted(elids)
elements = levels.real.collect(toplevel)
stack.items = [CoverItem(self.scene, el) for el in elements]
stack.setPos(self.scene.makePosition(self.position))
self.scene.addItem(stack)
super().accept()
def accept(self):
from maestro.plugins.coverdesk.plugin import StackItem, CoverItem
stack = StackItem(self.titleEdit.text())
if self.allButton.isChecked():
toplevel = list(
db.query(
"""
SELECT id
FROM {p}elements
WHERE domain=? AND id NOT IN (SELECT element_id FROM {p}contents)
ORDER BY id
""", self.scene.domain.id).getSingleColumn())
elements = levels.real.collect(toplevel)
stack.items = [CoverItem(self.scene, el) for el in elements]
elif self.searchButton.isChecked():
criterion = self.searchBox.criterion
if criterion is None:
return # do not accept
search.search(criterion, domain=self.scene.domain)
elids = criterion.result
toplevel = set(elids)
toplevel.difference_update(
db.query(
"SELECT element_id FROM {p}contents WHERE container_id IN ({elids})",
elids=db.csList(elids)).getSingleColumn())
toplevel = sorted(elids)
elements = levels.real.collect(toplevel)
stack.items = [CoverItem(self.scene, el) for el in elements]
stack.setPos(self.scene.makePosition(self.position))
self.scene.addItem(stack)
super().accept()
def setUp(self):
super().setUp()
self.level.elements = {}
if self.real:
db.query("DELETE FROM {}elements".format(db.prefix))
from maestro.core import reallevel
reallevel._dbIds = set()
def _shouldSkip(self, state):
"""Some states should be skipped, when the database already contains the corresponding values."""
if state == 'domains':
return db.query("SELECT COUNT(*) FROM {p}domains").getSingle() > 0
elif state == 'tags':
return db.query("SELECT COUNT(*) FROM {p}tagids").getSingle() > 0
else: return False
def _removeFromDb(self, elements):
"""Like removeFromDb but not undoable."""
for element in elements:
assert element.isInDb()
if element.isContainer():
del self.elements[element.id]
for childId in element.contents:
self[childId].parents.remove(element.id)
_dbIds.difference_update(element.id for element in elements)
# Rely on foreign keys to delete all tags, flags etc. from the database
ids = itertools.chain.from_iterable(element.contents
for element in elements
if element.isContainer())
db.query("DELETE FROM {}elements WHERE id IN ({})".format(
db.prefix, db.csList(element.id for element in elements)))
removedFiles = [
element.url for element in elements
if element.isFile() and element.url.scheme == "file"
]
if len(removedFiles) > 0:
self.emitFilesystemEvent(removed=removedFiles)
self.checkGlobalSelection(elements)
self.emit(
levels.LevelChangeEvent(dbRemovedIds=[el.id for el in elements]))
def handleMissingFiles(self):
"""Called after all missing hashes have been computed and all modified files have been examined for
tag changes.
"""
self.handleTagAndHashChanges()
if len(self.missingDB) > 0: # some files have been (re)moved outside Maestro
missingHashes = {} # hashes of missing files mapped to Track objects
for file in self.missingDB:
if file.hash is not None:
missingHashes[file.hash] = file
if len(missingHashes) > 0:
# search newfiles for the missing hashes in order to detect moves
detectedMoves = []
for file in self.files.values():
if file.id is None and file.hash in missingHashes:
oldFile = missingHashes[file.hash]
detectedMoves.append((oldFile, file.url))
self.missingDB.remove(oldFile)
del missingHashes[file.hash]
for file, newURL in detectedMoves:
db.query('UPDATE {p}files SET url=? WHERE element_id=?', str(newURL), file.id)
logging.info(__name__, 'renamed outside maestro: {}->{}'.format(file.url, newURL))
self.moveFile(file, newURL)
if len(self.missingDB) > 0:
# --> some files are lost. Show a dialog and let the user fix this
from maestro.filesystem import dialogs
dialog = dialogs.MissingFilesDialog([file.id for file in self.missingDB])
dialog.exec_()
stack.clear()
self.scanState = ScanState.notScanning
self.scanTimer.stop()
logging.debug(__name__, 'scan of source {} finished'.format(self.name))
def runTest(self):
if self.real:
self.assertEqual(0, db.query("SELECT COUNT(*) FROM {}elements".format(db.prefix)).getSingle())
self.assertEqual(set(self.level.elements.keys()), set([el.id for el in self.fs]))
else:
self.assertEqual(self.level.elements, {})
print("Start commit")
self.subLevel.commit()
if self.real:
self.assertEqual(5, db.query("SELECT COUNT(*) FROM {}elements".format(db.prefix)).getSingle())
self.assertEqual(set(self.level.elements.keys()),
set([element.id for element in [self.f1, self.f2, self.f3, self.f4, self.c]]))
self.assertEqual(self.level[self.c.id].contents, self.contentList)
self.assertIsNot(self.level[self.c.id].contents, self.contentList)
self.assertEqual(self.level[self.f3.id].parents, [])
self.assertEqual(self.level[self.c.id].tags, self.containerTags)
self.assertIsNot(self.level[self.c.id].tags, self.containerTags)
# Now change stuff on the sub level and commit again
#===================================================
self.f5 = self.subLevel.collect(TestUrl('test://band 5 - new song'))
contentList = elements.ContentList.fromPairs([(10,self.f5), (12,self.f2)])
self.subLevel.changeContents({self.c: contentList})
self.subLevel.removeElements([self.f1])
self.assertEqual(self.subLevel.elements.keys(),
set([element.id for element in [self.f2, self.f3, self.f4, self.f5, self.c]]))
self.subLevel.commit()
self.assertEqual(set(self.level.elements.keys()),
set([element.id for element in [self.f1, self.f2, self.f3, self.f4, self.f5, self.c]]))
self.assertEqual(self.level[self.c.id].contents, contentList)
self.checkUndo()
self.checkRedo()
def query(resource, mbid, includes=[]):
"""Queries MusicBrainz' web service for *resource* with *mbid* and the given list of includes.
Returns an LXML ElementTree root node. All namespaces are removed from the result.
"""
url = '{}/{}/{}'.format(wsURL, resource, mbid)
if queryCallback:
queryCallback(url)
if len(includes) > 0:
url += '?inc={}'.format('+'.join(includes))
logging.debug(__name__, 'querying {}'.format(url))
ans = db.query("SELECT xml FROM {}musicbrainzqueries WHERE url=?".format(db.prefix), url)
try:
data = ans.getSingle()
except db.EmptyResultException:
try:
request = urllib.request.Request(url)
request.add_header('User-Agent',
'Maestro/0.4.0 (https://github.com/maestromusic/maestro)')
with urllib.request.urlopen(request) as response:
data = response.read()
except urllib.error.HTTPError as e:
if e.code == 404:
raise e
else:
raise ConnectionError(e.msg)
db.query("INSERT INTO {}musicbrainzqueries (url, xml) VALUES (?,?)"
.format(db.prefix), url, data)
root = etree.fromstring(data)
# remove namespace tags
for node in root.iter():
if node.tag.startswith('{'):
node.tag = node.tag.rsplit('}', 1)[-1]
return root
def _shouldSkip(self, state):
"""Some states should be skipped, when the database already contains the corresponding values."""
if state == 'domains':
return db.query("SELECT COUNT(*) FROM {p}domains").getSingle() > 0
elif state == 'tags':
return db.query("SELECT COUNT(*) FROM {p}tagids").getSingle() > 0
else:
return False
def getStatistics(self):
"""Gather and return the data for the statistics table."""
length = db.query("SELECT SUM(length) FROM {}files".format(db.prefix)).getSingle()
# SQL's SUM returns NULL if files is empty
if length is None:
length = 0
return [
(self.tr("Elements"), db.query("SELECT COUNT(*) FROM {}elements".format(db.prefix)).getSingle()),
(self.tr("Files"), db.query("SELECT COUNT(*) FROM {}files".format(db.prefix)).getSingle()),
(self.tr("Total Length"), utils.strings.formatLength(length)),
(self.tr("Containers"),db.query(
"SELECT COUNT(*) FROM {}elements WHERE file = 0"
.format(db.prefix)).getSingle()),
(self.tr("Toplevel elements"),db.query("""
SELECT COUNT(*)
FROM {0}elements AS el LEFT JOIN {0}contents AS c ON el.id = c.element_id
WHERE c.element_id IS NULL
""".format(db.prefix)).getSingle()),
(self.tr("Toplevel files"),db.query("""
SELECT COUNT(*)
FROM {0}elements AS el LEFT JOIN {0}contents AS c ON el.id = c.element_id
WHERE el.file = 1 AND c.element_id IS NULL
""".format(db.prefix)).getSingle()),
(self.tr("Content relations"),db.query(
"SELECT COUNT(*) FROM {}contents"
.format(db.prefix)).getSingle()),
(self.tr("Tag relations"),db.query(
"SELECT COUNT(*) FROM {}tags"
.format(db.prefix)).getSingle()),
(self.tr("Tracked new files"),db.query(
"SELECT COUNT(*) FROM {}newfiles"
.format(db.prefix)).getSingle()),
]
def undo(self):
if not exists(dirname(self.tmpPath)):
os.makedirs(dirname(self.tmpPath))
shutil.move(self.element.url.path, self.tmpPath)
tmpUrl = urls.URL.fileURL(self.tmpPath)
db.query('UPDATE {p}files SET url=? WHERE element_id=?', str(tmpUrl), self.element.id)
for level in levels.allLevels:
if self.element.id in level:
levelElem = level[self.element.id]
levelElem.url = tmpUrl
level.emitEvent(dataIds=[self.element.id])
levels.real.emitFilesystemEvent(deleted=[self.element])
def runTest(self):
if self.real:
self.assertEqual(
0,
db.query("SELECT COUNT(*) FROM {}elements".format(
db.prefix)).getSingle())
self.assertEqual(set(self.level.elements.keys()),
set([el.id for el in self.fs]))
else:
self.assertEqual(self.level.elements, {})
print("Start commit")
self.subLevel.commit()
if self.real:
self.assertEqual(
5,
db.query("SELECT COUNT(*) FROM {}elements".format(
db.prefix)).getSingle())
self.assertEqual(
set(self.level.elements.keys()),
set([
element.id
for element in [self.f1, self.f2, self.f3, self.f4, self.c]
]))
self.assertEqual(self.level[self.c.id].contents, self.contentList)
self.assertIsNot(self.level[self.c.id].contents, self.contentList)
self.assertEqual(self.level[self.f3.id].parents, [])
self.assertEqual(self.level[self.c.id].tags, self.containerTags)
self.assertIsNot(self.level[self.c.id].tags, self.containerTags)
# Now change stuff on the sub level and commit again
#===================================================
self.f5 = self.subLevel.collect(TestUrl('test://band 5 - new song'))
contentList = elements.ContentList.fromPairs([(10, self.f5),
(12, self.f2)])
self.subLevel.changeContents({self.c: contentList})
self.subLevel.removeElements([self.f1])
self.assertEqual(
self.subLevel.elements.keys(),
set([
element.id
for element in [self.f2, self.f3, self.f4, self.f5, self.c]
]))
self.subLevel.commit()
self.assertEqual(
set(self.level.elements.keys()),
set([
element.id for element in
[self.f1, self.f2, self.f3, self.f4, self.f5, self.c]
]))
self.assertEqual(self.level[self.c.id].contents, contentList)
self.checkUndo()
self.checkRedo()
def doAction(self):
elem = next(self.parent().selection.fileWrappers()).element
path = QtWidgets.QFileDialog.getOpenFileName(
application.mainWindow,
self.tr('Select new location of the missing file'),
os.path.dirname(elem.url.path))[0]
if path != '':
newUrl = urls.URL.fileURL(path)
from maestro.filesystem import getNewfileHash
db.query('UPDATE {p}files SET url=?,hash=? WHERE element_id=?',
str(newUrl), getNewfileHash(newUrl), elem.id)
elem.url = newUrl
levels.real.emitEvent(dataIds=(elem.id,))
def undo(self):
if not exists(dirname(self.tmpPath)):
os.makedirs(dirname(self.tmpPath))
shutil.move(self.element.url.path, self.tmpPath)
tmpUrl = urls.URL.fileURL(self.tmpPath)
db.query('UPDATE {p}files SET url=? WHERE element_id=?', str(tmpUrl),
self.element.id)
for level in levels.allLevels:
if self.element.id in level:
levelElem = level[self.element.id]
levelElem.url = tmpUrl
level.emitEvent(dataIds=[self.element.id])
levels.real.emitFilesystemEvent(deleted=[self.element])
def enable():
"""Initialize filesystem module. Creates :class:`Source` instances for all configured sources.
"""
global allSources
from maestro.filesystem.sources import Source
allSources = [Source(**data) for data in config.storage.filesystem.sources]
# delete files not in any source
if len(allSources) > 0:
db.query('DELETE FROM {p}newfiles WHERE ' + ' AND '.join('url NOT LIKE "file://{}%"'.format(
source.path.replace('"', '\\"')) for source in allSources))
allSources.sort(key=lambda s: s.name)
urls.fileBackends.append(RealFile)
parseAutoReplace()
def getTags(self):
"""Gather and return the data for the tags table."""
tags = []
result = db.query("SELECT id, tagname, tagtype, private FROM {}tagids ORDER BY id".format(db.prefix))
for id,name,type,private in result:
tuple = (id,name,type,private,
db.query("SELECT COUNT(*) FROM {}values_{} WHERE tag_id={}"
.format(db.prefix,type,id)).getSingle(),
db.query("SELECT COUNT(*) FROM {}tags WHERE tag_id={}"
.format(db.prefix,id)).getSingle()
)
tags.append(tuple)
return(tags)
def doAction(self):
elem = next(self.parent().selection.fileWrappers()).element
path = QtWidgets.QFileDialog.getOpenFileName(
application.mainWindow,
self.tr('Select new location of the missing file'),
os.path.dirname(elem.url.path))[0]
if path != '':
newUrl = urls.URL.fileURL(path)
from maestro.filesystem import getNewfileHash
db.query('UPDATE {p}files SET url=?,hash=? WHERE element_id=?',
str(newUrl), getNewfileHash(newUrl), elem.id)
elem.url = newUrl
levels.real.emitEvent(dataIds=(elem.id, ))
def enable():
"""Initialize filesystem module. Creates :class:`Source` instances for all configured sources.
"""
global allSources
from maestro.filesystem.sources import Source
allSources = [Source(**data) for data in config.storage.filesystem.sources]
# delete files not in any source
if len(allSources) > 0:
db.query('DELETE FROM {p}newfiles WHERE ' + ' AND '.join(
'url NOT LIKE "file://{}%"'.format(source.path.replace('"', '\\"'))
for source in allSources))
allSources.sort(key=lambda s: s.name)
urls.fileBackends.append(RealFile)
parseAutoReplace()
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 getTags(self):
"""Gather and return the data for the tags table."""
tags = []
result = db.query(
"SELECT id, tagname, tagtype, private FROM {}tagids ORDER BY id".
format(db.prefix))
for id, name, type, private in result:
tuple = (
id, name, type, private,
db.query(
"SELECT COUNT(*) FROM {}values_{} WHERE tag_id={}".format(
db.prefix, type, id)).getSingle(),
db.query("SELECT COUNT(*) FROM {}tags WHERE tag_id={}".format(
db.prefix, id)).getSingle())
tags.append(tuple)
return (tags)
def updateTable(self):
"""Update the result table (in the GUI) with data from the result table (in the database)."""
self.table.clear()
rowCount = 0 if self.criterion is None else len(self.criterion.result)
self.table.setRowCount(rowCount)
if rowCount == 0:
return
# Add the titles. If there are more than one title, concatenate them.
result = db.query("""
SELECT el.id AS id, GROUP_CONCAT(v.value {separator} ', ') AS value
FROM {0}elements AS el
LEFT JOIN {0}tags AS t ON el.id = t.element_id AND t.tag_id = {titleTag}
LEFT JOIN {0}values_varchar AS v ON t.tag_id = v.tag_id AND t.value_id = v.id
WHERE el.id IN ({ids})
GROUP BY el.id
""".format(db.prefix,
separator='SEPARATOR' if db.type == 'mysql' else ',',
titleTag=tags.TITLE.id,
ids=','.join(str(id) for id in self.criterion.result)))
for i, row in enumerate(result):
if i == 0:
self.table.setColumnCount(len(row))
for j, data in enumerate(row):
item = QtWidgets.QTableWidgetItem(str(data))
item.setFlags(Qt.ItemIsEnabled)
self.table.setItem(i, j, item)
self.table.resizeColumnsToContents()
self.table.setEnabled(True)
def getDates(self):
"""Return heights and labels of the bars in the date chart."""
result = db.query("""
SELECT v.value {1} 10000 AS date, COUNT(*) AS count
FROM {0}tags AS t JOIN {0}values_date AS v ON t.tag_id = v.tag_id AND t.value_id = v.id
WHERE t.tag_id = 8
GROUP BY date
""".format(db.prefix, '/' if db.type == 'sqlite' else
'DIV')) # SQLite uses integer division
counters = collections.defaultdict(int)
for date, count in result:
if date < 1700:
date = (date // 100) * 100
elif date < 1950:
date = (date // 50) * 50
else:
date = (date // 10) * 10
counters[date] += count
def dateToStr(date):
if date < 1700:
return "{}-{}".format(date, date + 99)
elif date < 1950:
return "{}-{}".format(date, date + 49)
elif date < 2000: #70s etc.
return "{}s".format(date % 100)
else:
return "{}-{}".format(
date, min(date + 9,
datetime.date.today().year))
return [(counters[date], dateToStr(date))
for date in sorted(counters.keys())]
def _loaded(self, task):
"""Load covers after search for elements has finished. If no search was necessary, *task* is None.
"""
if task is not None:
if not isinstance(task, search.SearchTask): # subclasses might submit over tasks
return
elids = task.criterion.result
if len(elids):
filterClause = " AND el.id IN ({})".format(db.csList(elids))
else:
self.display().setCovers([], {})
return
else:
filterClause = " AND el.domain={}".format(self.domain.id)
result = db.query("""
SELECT el.id, st.data
FROM {p}elements AS el
JOIN {p}stickers AS st ON el.id = st.element_id
WHERE st.type = 'COVER' {filter}
""", filter=filterClause)
coverPaths = {id: path for id, path in result}
ids = list(coverPaths.keys())
levels.real.collect(ids)
if tags.isInDb("artist") and tags.isInDb("date"):
sortValues = {}
artistTag = tags.get("artist")
dateTag = tags.get("date")
for id in ids:
el = levels.real[id]
sortValues[id] = (el.tags[artistTag][0] if artistTag in el.tags else utils.PointAtInfinity(),
el.tags[dateTag][0] if dateTag in el.tags else utils.PointAtInfinity())
ids.sort(key=sortValues.get)
self.display().setCovers(ids, coverPaths)
def updateTable(self):
"""Update the result table (in the GUI) with data from the result table (in the database)."""
self.table.clear()
rowCount = 0 if self.criterion is None else len(self.criterion.result)
self.table.setRowCount(rowCount)
if rowCount == 0:
return
# Add the titles. If there are more than one title, concatenate them.
result = db.query("""
SELECT el.id AS id, GROUP_CONCAT(v.value {separator} ', ') AS value
FROM {0}elements AS el
LEFT JOIN {0}tags AS t ON el.id = t.element_id AND t.tag_id = {titleTag}
LEFT JOIN {0}values_varchar AS v ON t.tag_id = v.tag_id AND t.value_id = v.id
WHERE el.id IN ({ids})
GROUP BY el.id
""".format(
db.prefix,
separator='SEPARATOR' if db.type == 'mysql' else ',',
titleTag=tags.TITLE.id,
ids=','.join(str(id) for id in self.criterion.result)))
for i, row in enumerate(result):
if i == 0:
self.table.setColumnCount(len(row))
for j, data in enumerate(row):
item = QtWidgets.QTableWidgetItem(str(data))
item.setFlags(Qt.ItemIsEnabled)
self.table.setItem(i, j, item)
self.table.resizeColumnsToContents()
self.table.setEnabled(True)
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 _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 deleteSuperfluousValues():
"""Remove unused entries from the values_* tables."""
tables = set(valueType.table for valueType in tagsModule.TYPES)
for table in tables:
# This is complicated because we need different queries for MySQL and SQLite.
# Neither query works in both.
mainPart = """ FROM {1} LEFT JOIN {0}tags ON {1}.tag_id = {0}tags.tag_id
AND {1}.id = {0}tags.value_id
WHERE element_id IS NULL
""".format(db.prefix, table)
if db.type == 'mysql':
# Cannot use DELETE together with JOIN in SQLite
db.query("DELETE {} {}".format(table, mainPart))
else:
# Cannot delete from a table used in a subquery in MySQL
db.query("DELETE FROM {0} WHERE id IN (SELECT {0}.id {1})".format(
table, mainPart))
def getStorage(elid):
"""Return a tags.Storage object filled with the tags of the element with the given id."""
result = db.query(
"SELECT tag_id, value_id FROM {p}tags WHERE element_id = ?", elid)
storage = tagsModule.Storage()
for tagId, valueId in result:
tag = tagsModule.get(tagId)
storage.add(tag, value(tag, valueId))
return storage
def getIdsAndValues(tagSpec, whereClause='1', *args, **kwargs):
tag = tagsModule.get(tagSpec)
result = db.query(
"SELECT id, value FROM {} WHERE tag_id = ? AND {}".format(
tag.type.table, whereClause), tag.id, *args, **kwargs)
if tag.type != tagsModule.TYPE_DATE:
return (tuple(row) for row in result)
else:
return ((id, utils.FlexiDate.fromSql(date)) for id, date in result)
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 getValues(tagSpec, whereClause='1', *args, **kwargs):
tag = tagsModule.get(tagSpec)
result = db.query(
"SELECT value FROM {} WHERE tag_id = ? AND {}".format(
tag.type.table, whereClause), tag.id, *args, **kwargs)
if tag.type != tagsModule.TYPE_DATE:
return result.getSingleColumn()
else:
return (utils.FlexiDate.fromSql(date)
for date in result.getSingleColumn())
def runTest(self):
if not self.real:
self.assertEqual(self.level.elements,
{}) # this is important on undo
self.f1 = self.level.collect(TestUrl('test://band 1 - song'))
# due to the fixed url->id mapping, these id is the same on real and editor
self.assertEqual(self.f1.id, 1)
self.assertIn(self.f1.id, self.level)
self.f2 = self.level.collect(TestUrl('test://band 2 - a song'))
self.f3 = self.level.collect(TestUrl('test://band 3 - another song'))
self.f4 = self.level.collect(TestUrl('test://band 4 - no song'))
containerTags = tags.Storage({tags.TITLE: ['Weird album']})
if self.real: # On real level createContainer does not work until we added the contents to the db
self.assertEqual(
0,
db.query("SELECT COUNT(*) FROM {}elements".format(
db.prefix)).getSingle())
from maestro.core import reallevel
self.assertEqual(reallevel._dbIds, set())
self.level.addToDb([self.f1, self.f2, self.f3, self.f4])
self.assertEqual(
4,
db.query("SELECT COUNT(*) FROM {}elements".format(
db.prefix)).getSingle())
self.assertEqual(reallevel._dbIds, set([1, 2, 3, 4]))
# note that this id will be different when this test is run for editor and real level
predictedId = db._nextId
self.assertNotIn(predictedId, self.level)
self.assertEqual(self.f1.parents, [])
self.c = self.level.createContainer(
tags=containerTags, contents=[self.f1, self.f2, self.f3])
self.assertEqual(self.c.id, predictedId)
self.assertIn(predictedId, self.level)
self.assertEqual(
self.c.contents,
elements.ContentList.fromList([self.f1.id, self.f2.id,
self.f3.id]))
self.assertEqual(self.f1.parents, [self.c.id])
self.checkUndo()
self.checkRedo()
def moveFile(self, file: File, newUrl: urls.URL):
"""Internally move *file* to *newUrl* by updating the folders and their states.
This does not alter the filesystem and normally also not the database. The exception is
the target URL already exist in self.files; in that case it is removed from newfiles.
"""
newDir = self.getFolder(newUrl.directory)
oldDir = file.folder
oldDir.files.remove(file)
if newUrl.path in self.files:
newDir.files.remove(self.files[newUrl.path])
db.query('DELETE FROM {p}newfiles WHERE url=?', str(newUrl))
newDir.add(file)
del self.files[file.url.path]
file.url = newUrl
self.files[newUrl.path] = file
newDir.updateState(True, emit=self.folderStateChanged)
if oldDir != newDir:
oldDir.updateState(True, emit=self.folderStateChanged)
self.fileStateChanged.emit(newUrl.path)
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 _removeFromDb(self, elements):
"""Like removeFromDb but not undoable."""
for element in elements:
assert element.isInDb()
if element.isContainer():
del self.elements[element.id]
for childId in element.contents:
self[childId].parents.remove(element.id)
_dbIds.difference_update(element.id for element in elements)
# Rely on foreign keys to delete all tags, flags etc. from the database
ids = itertools.chain.from_iterable(element.contents for element in elements
if element.isContainer())
db.query("DELETE FROM {}elements WHERE id IN ({})"
.format(db.prefix, db.csList(element.id for element in elements)))
removedFiles = [element.url for element in elements if element.isFile()
and element.url.scheme == "file"]
if len(removedFiles) > 0:
self.emitFilesystemEvent(removed=removedFiles)
self.checkGlobalSelection(elements)
self.emit(levels.LevelChangeEvent(dbRemovedIds=[el.id for el in elements]))
def getContainerTypes(self):
"""Return sizes and labels for each wedge in the container types chart. The percentage of a wedge
is its size divided by the sum of all sizes times 100."""
result = db.query("""
SELECT COUNT(*) AS count, type
FROM {p}elements
WHERE file = 0
GROUP BY type
ORDER BY count DESC
""")
return [(row[0], elements.ContainerType(row[1]).title())
for row in result]
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 replaceTrack(self, wavName, tracknumber):
os.remove(join(self.tmpdir, wavName))
flacPath = join(self.tmpdir, wavName[:-3] + 'flac')
try:
ans = db.query("SELECT element_id FROM {}files WHERE url LIKE 'audiocd://{}.{}/%'"
.format(db.prefix, self.discid, tracknumber)).getSingle()
elem = levels.real.collect(ans)
levels.real.stack.push(InsertRippedFileCommand(elem, flacPath))
except db.EmptyResultException:
finishedTracks.append((self.discid, tracknumber, flacPath))
if tracknumber == self.toTrack:
mainwindow.mainWindow.statusBar().removeWidget(self.statusWidget)
self.encodingProcess = self.ripProcess = None
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 redo(self):
target = self.element.url.path
if not exists(dirname(target)):
os.makedirs(dirname(target))
shutil.move(self.tmpPath, target)
newUrl = urls.URL.fileURL(target)
tmpFile = filesystem.RealFile(newUrl)
tmpFile.readTags()
length = tmpFile.length
tmpFile.tags = self.element.tags.withoutPrivateTags(copy=True)
tracknr = parseNetloc(self.element.url)[1]
tmpFile.specialTags = dict(tracknumber=str(tracknr))
tmpFile.saveTags()
db.query('UPDATE {p}files SET url=?, length=? WHERE element_id=?',
str(newUrl), length, self.element.id)
for level in levels.allLevels:
if self.element.id in level:
levelElem = level[self.element.id]
levelElem.length = length
levelElem.url = newUrl
level.emitEvent(dataIds=[self.element.id])
levels.real.emitFilesystemEvent(added=[self.element])
def redo(self):
target = self.element.url.path
if not exists(dirname(target)):
os.makedirs(dirname(target))
shutil.move(self.tmpPath, target)
newUrl = urls.URL.fileURL(target)
tmpFile = filesystem.RealFile(newUrl)
tmpFile.readTags()
length = tmpFile.length
tmpFile.tags = self.element.tags.withoutPrivateTags(copy=True)
tracknr = parseNetloc(self.element.url)[1]
tmpFile.specialTags = dict(tracknumber=str(tracknr))
tmpFile.saveTags()
db.query('UPDATE {p}files SET url=?, length=? WHERE element_id=?',
str(newUrl), length, self.element.id)
for level in levels.allLevels:
if self.element.id in level:
levelElem = level[self.element.id]
levelElem.length = length
levelElem.url = newUrl
level.emitEvent(dataIds=[self.element.id])
levels.real.emitFilesystemEvent(added=[self.element])
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 query(resource, mbid, includes=[]):
"""Queries MusicBrainz' web service for *resource* with *mbid* and the given list of includes.
Returns an LXML ElementTree root node. All namespaces are removed from the result.
"""
url = '{}/{}/{}'.format(wsURL, resource, mbid)
if queryCallback:
queryCallback(url)
if len(includes) > 0:
url += '?inc={}'.format('+'.join(includes))
logging.debug(__name__, 'querying {}'.format(url))
ans = db.query(
"SELECT xml FROM {}musicbrainzqueries WHERE url=?".format(db.prefix),
url)
try:
data = ans.getSingle()
except db.EmptyResultException:
try:
request = urllib.request.Request(url)
request.add_header(
'User-Agent',
'Maestro/0.4.0 (https://github.com/maestromusic/maestro)')
with urllib.request.urlopen(request) as response:
data = response.read()
except urllib.error.HTTPError as e:
if e.code == 404:
raise e
else:
raise ConnectionError(e.msg)
db.query(
"INSERT INTO {}musicbrainzqueries (url, xml) VALUES (?,?)".format(
db.prefix), url, data)
root = etree.fromstring(data)
# remove namespace tags
for node in root.iter():
if node.tag.startswith('{'):
node.tag = node.tag.rsplit('}', 1)[-1]
return root
def __init__(self, parent):
super().__init__(parent=parent)
self.addField('icon',
self.tr("Icon"),
'image',
folders=[':maestro/flags', ':maestro/tags'])
self.addField('name', self.tr("Name"), 'string')
self.addField('number', self.tr("# of elements"), 'fixed')
self.items = list(flags.allFlags())
# Cache the element counts (we assume that they never change while this model is used)
self.elementCounts = dict(
db.query(
"SELECT flag_id, COUNT(*) FROM {p}flags GROUP BY flag_id"))
application.dispatcher.connect(self._handleDispatcher)
def replaceTrack(self, wavName, tracknumber):
os.remove(join(self.tmpdir, wavName))
flacPath = join(self.tmpdir, wavName[:-3] + 'flac')
try:
ans = db.query(
"SELECT element_id FROM {}files WHERE url LIKE 'audiocd://{}.{}/%'"
.format(db.prefix, self.discid, tracknumber)).getSingle()
elem = levels.real.collect(ans)
levels.real.stack.push(InsertRippedFileCommand(elem, flacPath))
except db.EmptyResultException:
finishedTracks.append((self.discid, tracknumber, flacPath))
if tracknumber == self.toTrack:
mainwindow.mainWindow.statusBar().removeWidget(self.statusWidget)
self.encodingProcess = self.ripProcess = None
def sortValue(tagSpec, valueId, valueIfNone=False):
"""Returns the sort value for the given tag value, or None if it is not set.
If *valueIfNone=True*, the value itself is returned if no sort value is set."""
tag = tagsModule.get(tagSpec)
value, sortValue = db.query(
"SELECT value, sort_value FROM {} WHERE tag_id = ? AND id = ?".format(
tag.type.table), tag.id, valueId).getSingleRow()
if sortValue is not None:
return sortValue
elif valueIfNone:
return value
else:
return None
def check(self,name,iconPath,redo=True):
result = db.query("SELECT name,icon FROM {}flag_names WHERE name='testflag' OR name='testflag2'"
.format(db.prefix))
if name is None: # no flag should exist
self.assertRaises(db.sql.EmptyResultException,result.getSingle)
else:
for dbName,dbIconPath in result:
if db.isNull(dbIconPath):
dbIconPath = None
flag = flags.get(name)
self.assertEqual(flag.name,name)
self.assertEqual(dbName,name)
self.assertEqual(flag.iconPath,iconPath)
self.assertEqual(dbIconPath,iconPath)
break;
else: self.fail() # two flags found by the db query
def check(self,values,redo=True):
result = db.query("SELECT tagtype,title,icon,private,sort FROM {}tagids WHERE tagname='testtag'"
.format(db.prefix))
if values is not None:
dbValues = [v if not db.isNull(v) else None for v in result.getSingleRow()]
dbValues[3] = bool(dbValues[3]) # private
dbValues = tuple(dbValues)
self.assertEqual(dbValues,values)
tag = tags.get("testtag")
self.assertEqual(tag.type.name,values[0])
self.assertEqual(tag.rawTitle,values[1])
self.assertEqual(tag.iconPath,values[2])
self.assertEqual(tag.private,values[3])
self.assertEqual(tags.tagList.index(tag),values[4])
else:
self.assertRaises(db.sql.EmptyResultException,result.getSingleRow)
def doAction(self):
treeView = self.parent()
# Collect tags
tags = set()
from maestro.widgets.browser.model import TagLayer
for layer in treeView.model().layers:
if isinstance(layer, TagLayer):
tags.update(layer.tagList)
# Collect values
valuesToHide = {tag: [] for tag in tags}
for wrapper in treeView.selection.wrappers():
if not wrapper.isContainer():
continue
# Collect all children ids and tag values
wrapper.loadContents(recursive=True)
ids = []
allValues = {tag: set() for tag in tags}
for child in wrapper.getAllNodes(skipSelf=True):
element = child.element
ids.append(element.id)
for tag in tags:
if tag in element.tags:
allValues[tag].update(db.tags.id(tag, value) for value in element.tags[tag])
# Check which tag values appear outside the children ids (thus possibly in wrapper itself)
for tag, vids in allValues.items():
if len(vids) > 0:
result = db.query("""
SELECT value_id
FROM {p}tags
WHERE tag_id=? AND element_id NOT IN ({elids}) AND value_id IN ({vids})
GROUP BY value_id
""", tag.id, elids=db.csList(ids), vids=db.csList(vids))
# We will not hide the values returned from the query because they appear elsewhere
vids.difference_update(result.getSingleColumn())
# these lists should be disjoint for different wrappers
# (unless one is an ancestor of the other one)
valuesToHide[tag].extend(vids)
dialog = HideTagValuesDialog(treeView, valuesToHide)
dialog.exec_()
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 build(self, layerIndex, domain, elids, matchingTags):
# 1. Get toplevel nodes.
if elids is None:
toplevel = set(db.query("""
SELECT id
FROM {p}elements
WHERE domain=? AND id NOT IN (SELECT element_id FROM {p}contents)
""", domain.id).getSingleColumn())
if len(toplevel) == 0:
return []
elif len(elids) > 0:
toplevel = set(elids)
toplevel.difference_update(db.query(
"SELECT element_id FROM {p}contents WHERE container_id IN ({elids})",
elids=db.csList(elids)).getSingleColumn())
else:
return []
# Shortcut: For very small result sets simply use a container tree
if len(toplevel) <= 5:
return _buildContainerTree(domain, elids)
# 2. Check whether a VariousNode is necessary.
# (that is, some toplevel nodes don't have a tag from self.tagList)
# We do this so early because 'toplevel' will be enlarged in the next step.
tagFilter = db.csIdList(self.tagList)
idFilter = db.csList(toplevel)
variousNodeElements = list(db.query("""
SELECT el.id
FROM {p}elements AS el LEFT JOIN {p}tags AS t
ON el.id = t.element_id AND t.tag_id IN ({tagFilter})
WHERE domain={domain} AND el.id IN ({idFilter}) AND t.value_id IS NULL
LIMIT 1
""", tagFilter=tagFilter, idFilter=idFilter, domain=domain.id).getSingleColumn())
# 3. Add contents of permeable nodes to 'toplevel', as long as they are in the search result.
# Tag values in these nodes should get a TagNode even if
# they don't appear in an actual toplevel node.
new = toplevel
while len(new):
new = set(db.query("""
SELECT c.element_id
FROM {p}contents AS c JOIN {p}elements AS el ON c.container_id = el.id
WHERE el.type IN ({collection},{container}) AND el.id IN ({parents})""",
collection=elements.ContainerType.Collection.value,
container=elements.ContainerType.Container.value,
parents=db.csList(new)).getSingleColumn())
# Restrict to search result. If the node's value only appears in contents of a permeable
# node in the search result and these contents are not in the result themselves,
# we would create an empty TagNode.
if elids is not None:
new.intersection_update(elids)
toplevel.update(new)
# 4. Create a TagNode for each tag value that appears in 'toplevel'
# Make sure to use as single TagNode for equal values in different tags
nodes = collections.defaultdict(functools.partial(bnodes.TagNode, layerIndex))
idFilter = db.csList(toplevel)
result = db.query("""
SELECT DISTINCT t.tag_id, v.id, v.value, v.hide, v.sort_value
FROM {p}tags AS t JOIN {p}values_varchar AS v ON t.tag_id = v.tag_id AND t.value_id = v.id
WHERE t.tag_id IN ({tagFilter}) AND t.element_id IN ({idFilter})
""", tagFilter=tagFilter, idFilter=idFilter)
for tagId, valueId, value, hide, sortValue in result:
matching = (tagId, valueId) in matchingTags
nodes[value].addTagValue(tagId, valueId, value, hide, sortValue, matching)
# 5. Optimize TagNodes (if there are only few of them)
if len(nodes) <= 20:
# Above we had to use values as keys, here ids are more useful
nodes = {tagTuple: node for node in nodes.values() for tagTuple in node.tagIds}
# The first task is to find all contents of each TagNode. Note that the last query (to find
# TagNodes) only considered toplevel elements.
for node in nodes.values():
node.elids = set()
if elids is not None:
idFilter = "t.element_id IN ({})".format(db.csList(elids))
else:
idFilter = '1'
result = db.query("""
SELECT DISTINCT tag_id, value_id, element_id
FROM {p}tags AS t
WHERE t.tag_id IN ({tagFilter}) AND {idFilter}
""", tagFilter=tagFilter, idFilter=idFilter)
withinMatchingTags = set()
for tagId, valueId, elementId in result:
if (tagId, valueId) in nodes:
node = nodes[(tagId, valueId)]
else:
continue # this means that this value does not appear in a 'toplevel' node
node.elids.add(elementId)
if (tagId, valueId) in matchingTags:
withinMatchingTags.add(elementId)
def checkSuperNode(node, superNode):
"""Given two nodes where the second contains all contents of the first, return whether
the first node may be deleted. As a sideeffect this method may merge *node* into *superNode*.
"""
# If *node* is completely contained in superNode, delete it.
if len(node.elids) < len(superNode.elids):
# However, matching nodes must not be deleted in favor of not matching ones.
# and visible nodes must not be deleted in favor of a hidden superNode.
return not ((node.matching and not superNode.matching)
or (not node.hide and superNode.hide))
else:
if node.hide == superNode.hide:
superNode.merge(node)
return True
else:
return node.hide
for k in list(nodes.keys()):
node = nodes[k]
# Delete nodes whose contents are covered by nodes matching the search query.
if not node.matching and node.elids <= withinMatchingTags:
del nodes[k]
continue
# Try to delete (or merge) TagNodes whose contents are contained in (or equal to) another
# TagNode
for node2 in nodes.values():
if node2 is not node and node.elids <= node2.elids: # node2 is a superNode:
if checkSuperNode(node, node2):
del nodes[k]
break
# 6. Create final list of nodes
visibleNodes = [node for node in nodes.values() if not node.hide]
hiddenNodes = [node for node in nodes.values() if node.hide]
visibleNodes.sort(key=lambda node: locale.strxfrm(node.sortValues[0][0]))
hiddenNodes.sort(key=lambda node: locale.strxfrm(node.sortValues[0][0]))
if len(variousNodeElements) > 0:
node = bnodes.VariousNode(layerIndex, self.tagList)
visibleNodes.append(node)
if len(hiddenNodes) > 0:
# If hidden nodes are present this layer needs two actual levels in the tree structure
# Since this interferes with the algorithm to determine the layer of a node, we have to store
# that layer index. See BrowserModel._getLayerIndex
for node in hiddenNodes:
node.layer = self
visibleNodes.append(bnodes.HiddenValuesNode(hiddenNodes))
return visibleNodes
def _buildContainerTree(domain, elids):
"""Create a wrapper tree including all elements from *elids* (or all elements with the given domain,
if *elids* is None). The tree will organize wrappers according to the natural tree structure.
"""
if elids is None:
toplevel = list(db.query("""
SELECT id
FROM {p}elements
WHERE domain=? AND id NOT IN (SELECT element_id FROM {p}contents)
""", domain.id).getSingleColumn())
elif len(elids) > 0:
toplevel = set(elids)
toplevel.difference_update(db.query(
"SELECT element_id FROM {}contents WHERE container_id IN ({})"
.format(db.prefix, db.csList(elids))).getSingleColumn())
else:
return []
# Load all toplevel elements and all of their ancestors
newIds = toplevel
while len(newIds) > 0:
levels.real.collect(newIds)
nextIds = []
for id in newIds:
nextIds.extend(levels.real[id].parents)
newIds = nextIds
# Collect all parents in cDict (mapping parent id -> list of children ids)
# Parents contained as key in this dict, will only contain part of their element's contants in
# the browser. The part is given by the corresponding value (a list) in this dict.
# The dict must not contain direct search results as keys, as they should always show all their
# contents.
cDict = collections.defaultdict(list)
def processNode(id):
"""Check whether the element with the given id has major parents that need to be added to the
browser's tree. If such a parent is found, update cDict and toplevel and return True.
"""
result = False
for pid in levels.real[id].parents:
if pid in cDict: # We've already added this parent
result = True
cDict[pid].append(id)
toplevel.discard(id)
elif elids is None or pid in elids: # This parent belongs to the direct search result
result = True
toplevel.discard(id)
elif processNode(pid): # We must add this parent, because it has a major ancestor.
result = True
cDict[pid].append(id)
toplevel.discard(id)
elif levels.real[pid].type.major: # This is a major parent. Add it to toplevel
result = True
cDict[pid].append(id)
toplevel.discard(id)
toplevel.add(pid)
return result
for id in list(toplevel): # copy!
processNode(id)
def createWrapper(id):
"""Create a wrapper to be inserted in the browser. If the wrapper should contain all of its
element's contents, create a BrowserWrapper, that will load the contents """
element = levels.real[id]
if id in cDict: # wrapper should contain only a part of its element's contents
wrapper = Wrapper(element)
wrapper.setContents([createWrapper(cid) for cid in cDict[id]])
return wrapper
elif element.isFile() or len(element.contents) == 0:
return Wrapper(element)
else:
return bnodes.BrowserWrapper(element) # a wrapper that will load all its contents when needed
contents = [createWrapper(id) for id in toplevel]
def sortFunction(wrapper):
"""Intelligent sort: sort albums by their date, everything else by name."""
element = wrapper.element
date = 0
if element.isContainer() and element.type == elements.ContainerType.Album:
dateTag = tags.get("date")
if dateTag.type == tags.TYPE_DATE and dateTag in element.tags:
date = -element.tags[dateTag][0].toSql() # minus leads to descending sort
return (date, element.getTitle(neverShowIds=True))
contents.sort(key=sortFunction)
return contents
def loadFromDb(self, idList, level=None):
"""Load elements specified by *idList* from the database into *level* which defaults to the
real level."""
if level is None:
level = self
if len(idList) == 0: # queries will fail otherwise
return []
csIdList = db.csList(idList)
# bare elements
result = db.query("""
SELECT el.domain, el.id, el.file, el.type, f.url, f.length
FROM {0}elements AS el LEFT JOIN {0}files AS f ON el.id = f.element_id
WHERE el.id IN ({1})
""".format(db.prefix, csIdList))
for domainId, id, file, elementType, url, length in result:
_dbIds.add(id)
if file:
level.elements[id] = elements.File(domains.domainById(domainId), level, id,
url=URL(url), length=length,
type=elements.ContainerType(elementType))
else:
level.elements[id] = elements.Container(domains.domainById(domainId), level, id,
type=elements.ContainerType(elementType))
# contents
result = db.query("""
SELECT el.id, c.position, c.element_id
FROM {0}elements AS el JOIN {0}contents AS c ON el.id = c.container_id
WHERE el.id IN ({1})
ORDER BY position
""".format(db.prefix, csIdList))
for id, pos, contentId in result:
level.elements[id].contents.insert(pos, contentId)
# parents
result = db.query("""
SELECT el.id, c.container_id
FROM {0}elements AS el JOIN {0}contents AS c ON el.id = c.element_id
WHERE el.id IN ({1})
""".format(db.prefix, csIdList))
for id, contentId in result:
level.elements[id].parents.append(contentId)
# tags
result = db.query("""
SELECT el.id, t.tag_id, t.value_id
FROM {0}elements AS el JOIN {0}tags AS t ON el.id = t.element_id
WHERE el.id IN ({1})
""".format(db.prefix, csIdList))
for id, tagId, valueId in result:
tag = tags.get(tagId)
level.elements[id].tags.add(tag, db.tags.value(tag, valueId))
# flags
result = db.query("""
SELECT el.id, f.flag_id
FROM {0}elements AS el JOIN {0}flags AS f ON el.id = f.element_id
WHERE el.id IN ({1})
""".format(db.prefix, csIdList))
for id, flagId in result:
level.elements[id].flags.append(flags.get(flagId))
# stickers
result = db.query("""
SELECT element_id, type, data
FROM {}stickers
WHERE element_id IN ({})
ORDER BY element_id, type, sort
""".format(db.prefix, csIdList))
# This is a bit complicated because the stickers should be stored in tuples, not lists
# Changing the lists would break undo/redo
#TODO: is this really necessary?
current = None
buffer = []
for (id, type, sticker) in result:
if current is None:
current = (id, type)
elif current != (id, type):
level.elements[current[0]].stickers[current[1]] = tuple(buffer)
current = (id, type)
buffer = []
element = level.elements[id]
if element.stickers is None:
element.stickers = {}
buffer.append(sticker)
if current is not None:
level.elements[current[0]].stickers[current[1]] = tuple(buffer)
try:
return [self.elements[id] for id in idList]
except KeyError: # probably some ids were not contained in the database
raise levels.ElementGetError(self, [id for id in idList if id not in self])
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]))
