def DegradeOriginalRows(self, ExcludeFolder=[]):
Trace(self.Folder)
if not sql.tables(self.Table):
return
def is_excluded(folder):
for cur in ExcludeFolder:
if cur in folder:
return True
if os.path.abspath(folder) == os.path.abspath(self.Folder):
return True
return False
rows = sql.execute('select distinct original from %s where original <> 0' % (self.Table), Flatten=True)
for count, original in enumerate(rows):
dbg_print(original, count)
rows = sql.execute('select idx, modified_date, path from %s where idx=%d or original=%d order by modified_date ASC' % (self.Table, original, original))
for idx, row in enumerate(rows):
filePath = row[2]
folder = os.path.dirname(filePath)
if folder in ExcludeFolder or is_excluded(folder):
continue
basename, ext = os.path.splitext(os.path.basename(filePath))
suffix = os.path.splitext(basename)[1].lstrip('.')
#print(ext, suffix, basename)
if is_number(suffix):
continue
if idx != 0:
print()
self.ChangeOriginal(original, row[0])
break
def Test4():
max = 150456
query = "select * from _Pictures where filename='sound_activation_level_dialog_basic7.png'"
# query = r'select path from _Pictures where idx > 150456 order by idx'
rows = sql.execute(query, Flatten=True)
print(rows)
# fp = open(r'c:\temp\_Pictures.log', 'w')
# for path in rows:
# fp.write(path + '\n')
# fp.close()
print(len(rows))
query = r"select max(idx) from _Pictures order by idx"
rows = sql.execute(query)
print(rows)
query = r"select idx from d_Pictures order by idx"
rows = sql.execute(query)
print(len(rows))
query = r"select max(idx) from d_Pictures order by idx"
rows = sql.execute(query)
print(rows)
def update_original(left, right):
table = FolderComp.FolderTable
query = r"select idx from %s Where folder=?" % (table)
left_id = sql.execute(query, Flatten=True, Data=[left])
left_id = left_id[0]
query = r"UPDATE %s set original=%d WHERE folder=?" % (table, left_id)
sql.execute(query, Data=[right])
def select_all_dups_by_folder():
Trace()
table = FolderComp.FolderTable
query = Expand(r'select distinct original from [table] Where original <> 0')
originals = sql.execute(query, Flatten=True)
for original in originals:
folder = sql.execute(r'select folder from %s Where idx=%d' % (table, original), Flatten=True)
folder = folder[0]
rows = sql.execute(r'select folder from %s Where original=%d' % (table, original), Flatten=True)
dups = dictn()
dups.folder = folder
dups.dups = rows
yield dups
def set_new_path(self, artist, album, where, where_data):
rows = sql.execute('select idx,path,title from %s where %s' % (self.Table, where), Data=where_data)
for row in rows:
ext = os.path.splitext(row[1])[1]
title = row[2]
newPath = r'%s%s%s%s%s%s%s%s' % (self.Folder, os.sep, artist, os.sep, album, os.sep, title, ext)
print(newPath)
def dump(self):
Trace()
rows = sql.execute('select new_path from %s' % (self.Table), Flatten=True)
for idx, row in enumerate(rows):
dest_path = row
dest_path = GenUniqueFileNamePlain(dest_path, os.path.dirname(dest_path), '')
LogPlain(dest_path)
def DeleteDups(self, DeleteFiles=False, Types='[AllMediaTypes]'):
Trace(self.Folder)
if not sql.tables(self.Table):
return
Globals.IgnoreExpandErrors = True
query = 'SELECT idx, path from [Table] where original <> 0'
dups = sql.execute(Expand(query), Verbose=self.Verbose)
idxList = []
for dup in dups:
idx, path = dup
try:
destPath = os.path.dirname(path)
destPath = destPath.replace(':\\', ':\\ServerName\dups\\')
if not DeleteFiles:
self.MoveFile(idx, path, destPath, Update=False)
else:
Log(r'Delete %s' % path)
DeleteFile(path)
idxList.append([str(idx)])
except (KeyboardInterrupt, SystemExit):
raise
except:
LogPlainError('Delete failed for %s' % (path))
query = r'DELETE FROM [Table] WHERE idx=?'
sql.execute_many(Expand(query), idxList)
def fix_album_names2(self):
Trace()
albums = sql.execute('select distinct album from %s' % (self.Table), Flatten=True)
for idx, album in enumerate(albums):
rows = sql.execute('select distinct old_folder from %s where album=?' % (self.Table), Data=[album], Flatten=True)
if len(rows) > 1:
PrettyPrint(rows, '(%s)' % (album))
dst_folder = rows[0]
move_rows = sql.execute('select idx,path,title from %s where album=? and folder <> ?' % (self.Table), Data=[album, dst_folder])
for row in move_rows:
ext = os.path.splitext(row[1])[1]
title = row[2]
fileName = '%s%s' % (title, ext)
newPath = GenUniqueFileNamePlain(fileName, dst_folder, '')
#print(newPath)
sql.execute("update %s set new_path=? , new_folder=? where idx=?" % (self.Table), Data=[newPath, os.path.dirname(newPath), row[0]])
def DegradeOriginalShowFolders(self, ExcludeFolder=[]):
Trace(self.Folder)
if not sql.tables(self.Table):
return
def is_excluded(folder):
for cur in ExcludeFolder:
if cur in folder:
return True
if os.path.abspath(folder) == os.path.abspath(self.Folder):
return True
return False
count = 0
rows = sql.execute('select distinct original from %s where original <> 0' % (self.Table), Flatten=True)
data = []
for original in rows:
count += 1
dbg_print(original, count)
rows = sql.execute('select idx, modified_date, path from %s where idx=%d or original=%d order by modified_date ASC' % (self.Table, original, original))
included = []
for row in rows:
filePath = row[2]
folder = os.path.dirname(filePath)
basename, ext = os.path.splitext(os.path.basename(filePath))
suffix = os.path.splitext(basename)[1].lstrip('.')
#print(ext, suffix, basename)
if is_number(suffix):
continue
elif folder in ExcludeFolder or is_excluded(folder):
continue
if folder not in included:
included.append(folder)
if len(included) == 0:
folder = os.path.dirname(rows[0][2])
included.append(folder)
for folder in included:
if folder not in data:
data.append(folder)
data.sort()
PrettyPrint(data, 'Included Folders')
Exit()
def Query(Query=None, Verbose=False):
Trace()
if not Query:
Log('Missing query')
return
rows = sql.execute(Query)
PrettyPrintList(rows, UseExpand=False)
def DeleteFolderDups(Delete=False, FindDups=False):
if FindDups:
FolderComp.FolderComp.find_all_dups()
dups = FolderComp.FolderComp.select_all_dups()
for folder in dups:
if Delete:
if os.path.exists(folder):
shutil.rmtree(folder, True)
dbg_print(folder)
print()
if Delete:
FolderComp.FolderComp.remove_all_dups()
dups = FolderComp.FolderComp.select_all_dups()
PrettyPrint(dups, 'Remaining Dups')
date = Globals.Date.replace('-', '_')
sql.execute('ALTER TABLE %s RENAME TO DupFolders_%s' % (FolderComp.FolderComp.FolderTable, date))
def Test6(ImportLogFile="[ImportLog]"):
ImportLogFile = ExpandPath(ImportLogFile)
moved = []
fp = open(ImportLogFile, "r", encoding="utf-8")
for line in fp:
values = line.split(",")
if len(values) == 2:
_, dst = line.split(",")
else:
idx = int(len(values) / 2)
dst = ",".join(values[idx:])
moved.append(dst.lower().strip())
fp.close()
for table in ["FindDups_d_Pictures", "FindDups_d_Music", "FindDups_d_Videos"]:
print(table)
query = r"select distinct original from %s where original <> 0" % (table)
originals = sql.execute(query, Flatten=True)
count = 0
rows = []
for oid in originals:
dbg_print(count)
query = r"select path from %s where idx=%d" % (table, oid)
path = sql.execute(query, Flatten=True)
for row in path:
row = row.lower().strip()
if row in moved:
count += 1
DeleteFile(row)
print()
query = r"select path from %s where original <> 0" % (table)
rows = sql.execute(query, Flatten=True)
count = 0
for row in rows:
dbg_print(count)
if row.lower() in moved:
count += 1
print()
def ConsolidateMusicDupsOrig(self):
Trace(self.Folder)
Globals.IgnoreExpandErrors = True
self.Verbose = True
query = r'SELECT DISTINCT folder FROM [Table] WHERE original <> 0'
dup_folders = sql.execute(Expand(query), Verbose=self.Verbose, Flatten=True)
for folder in dup_folders:
try:
LogPlain(r'processing folder %s' % str(folder))
except:
LogPlain('cant process folder - check unicode')
continue
query = "SELECT DISTINCT original from [Table] where folder='%s' and original <> 0" % (folder)
orig_idx = sql.execute(Expand(query), Verbose=self.Verbose, Flatten=True)
if len(orig_idx) == 0:
continue
orig_idx_str = ','.join(map(str, orig_idx))
print(orig_idx_str)
query = 'SELECT DISTINCT folder from [Table] where idx in (?)'
orig_folders = sql.execute(Expand(query), Verbose=self.Verbose, Data=[orig_idx_str])
orig_dest = orig_folders[0]
for orig_folder in orig_folders:
if orig_folder == orig_dest:
# Log('Skipping [orig_folder] == [orig_dest]')
continue
query = 'SELECT path from [Table] where folder=?'
files = sql.execute(Expand(query), Verbose=self.Verbose, Data=[orig_folder])
for file in files:
query = r'SELECT idx from [Table] where path=?'
idx = sql.execute(Expand(query), Verbose=self.Verbose, Data=[file])
idx = idx[0]
print('MoveFile', idx, file, orig_dest)
Exit()
self.MoveFile(idx, file, orig_dest)
def MoveFile(self, Idx, SourceFile, DestFolder, Update=True, KeepOriginals=False):
if not sql.tables(self.Table):
return False
# Trace(r'[Idx] [SourceFile] [DestFolder]')
try:
uniqFile = ''
if not KeepOriginals:
uniqFile = MoveToUniqueFile(SourceFile, DestFolder, '')
else:
uniqFile = CopyToUniqueFile(SourceFile, DestFolder, '')
ImportLog.log(SourceFile, uniqFile)
if Update:
query = r'UPDATE [Table] set path=?, folder=? WHERE idx=?'
#print([uniqFile, DestFolder, Idx])
sql.execute(Expand(query), Verbose=self.Verbose, Data=[uniqFile, DestFolder, Idx])
except (KeyboardInterrupt, SystemExit):
raise
except:
LogPlainError('move failed for [Idx]')
ReportException()
def fix_various_artists(self):
folders = sql.execute('select distinct folder from %s' % (self.Table), Flatten=True)
for folder in folders:
albums = sql.execute('select distinct album from %s where folder=?' % (self.Table), Data=[folder], Flatten=True)
for album in albums:
rows = sql.execute('select distinct artist from %s where folder=? and album=?' % (self.Table), Data=[folder, album], Flatten=True)
if len(rows) > 1:
artist = 'Various Artists'
for row in rows:
if 'various' not in row.lower():
artist = artist + os.sep + row
break
lowered = list(map(str.lower, rows))
lowered = [ re.sub(r'\W+', '', row) for row in lowered ]
lowered = list(set(lowered))
artist = ternary(len(lowered) > 1, artist, rows[0])
PrettyPrint(rows, 'Multiple artists for (%s) (%s) %s' % (artist, album, folder))
rows = sql.execute('select idx,path,title from %s where folder=? and album=?' % (self.Table), Data=[folder, album])
for row in rows:
ext = os.path.splitext(row[1])[1]
title = row[2]
newPath = r'%s%s%s%s%s%s%s%s' % (self.Folder, os.sep, artist, os.sep, album, os.sep, title, ext)
sql.execute("update %s set new_path=? , new_folder=? where idx=?" % (self.Table), Data=[newPath, os.path.dirname(newPath), row[0]])
def ConsolidateMusicDups(self):
Trace(self.Folder)
rows = sql.execute('select distinct original from %s where original <> 0' % (self.Table), Flatten=True)
for count, original in enumerate(rows):
#dbg_print(original, count)
original_path = sql.execute('select path from %s where idx=%d' % (self.Table, original), Flatten=True)
orig_dest = os.path.dirname(original_path[0])
rows = sql.execute('select path from %s where original=%d' % (self.Table, original), Flatten=True)
for idx, filePath in enumerate(rows):
folder = os.path.dirname(filePath)
query = 'SELECT path from [Table] where folder=?'
files = sql.execute(Expand(query), Verbose=self.Verbose, Data=[folder], Flatten=True)
for file in files:
query = r'SELECT idx from [Table] where path=?'
idx = sql.execute(Expand(query), Verbose=self.Verbose, Data=[file], Flatten=True)
idx = idx[0]
#print('MoveFile', idx, file, orig_dest)
self.MoveFile(idx, file, orig_dest)
def find_all_dups():
Trace()
dups = []
table = FolderComp.FolderTable
query = Expand(r'select distinct foldername from [table] Where original=0')
folderNames = sql.execute(query, Flatten=True)
for folderName in folderNames:
dbg_print(len(dups), folderName)
query = Expand(r"select * from [table] Where foldername=?")
rows = sql.execute(query, Data=[folderName])
if len(rows) > 1:
original_row = rows[0]
oid = original_row[0]
for dup_row in rows[1:]:
result = Compare(original_row[2], dup_row[2])
if result:
dups.append([oid, dup_row[0]])
print('')
if len(dups):
count = sql.update(table, dups, ['original=?'], "WHERE idx=?", Verbose=True)
Log('Updated [count] rows')
return len(dups)
def MoveDups(self, Types='[AllMediaTypes]'):
Trace(self.Folder)
if not sql.tables(self.Table):
return
Globals.IgnoreExpandErrors = True
dups = self.select_rows('original!=0', Types, Columns=['idx', 'path', 'folder', 'original'])
for dup in dups:
idx, path, folder, original = dup
query = 'SELECT idx, folder from [Table] where idx=[original]'
orig = sql.execute(Expand(query), Verbose=self.Verbose)
orig_idx, orig_folder = orig
if folder != orig_folder:
self.MoveFile(idx, path, orig_folder)
def PrintDups(self, Limit=None, Types='[AllMediaTypes]'):
Trace(self.Folder)
if not sql.tables(self.Table):
return
rows = sql.execute('select A.path, B.path from %s as A, %s as B where A.original <> 0 and B.idx = A.original' % (self.Table, self.Table))
dups = []
for row in rows:
if os.path.exists(row[0]) and os.path.exists(row[1]):
dups.append(row)
rows = dups
Log(r'Found %d dups' % (len(rows)))
if len(rows) > 100:
PrettyPrintList(rows, FilePath=ExpandPath(r'[Temp]\Dups.log'))
Log(r'Generated [Temp]\Dups.log')
else:
PrettyPrintList(rows, UseExpand=False)
def move_to_new_paths(self):
rows = sql.execute('select path,new_path from %s where new_folder != old_folder' % (self.Table))
for idx, row in enumerate(rows):
#print(row)
orig_path = row[0]
dest_path = row[1]
dest_path = GenUniqueFileNamePlain(dest_path, os.path.dirname(dest_path), '')
#if idx == 100:
# Exit()
try:
os.chmod(orig_path, stat.S_IRWXU)
shutil.move(orig_path, dest_path)
print('Moved to ', dest_path)
except:
ReportException()
pass
def GetFileDups(self, FilePath):
# Trace(FilePath)
if not sql.tables(self.Table):
return []
stats = os.stat(FilePath)
size = stats.st_size
query = 'SELECT path from [Table] WHERE size=[size]'
rows = sql.execute(Expand(query), Verbose=self.Verbose)
dups = []
for row in rows:
path = row
if not os.path.exists(path):
Log(r'Error missing file: [path]')
continue
if filecmp.cmp(FilePath, path, False):
dups.append(path)
return dups
def IsDupFile(self, FilePath):
#Trace(FilePath)
if not sql.tables(self.Table):
return False
stats = os.stat(FilePath)
size = stats.st_size
query = 'SELECT path from [Table] WHERE size=[size]'
rows = flatten(sql.execute(Expand(query), Verbose=self.Verbose))
dups = []
for row in rows:
path = row
if not os.path.exists(path):
LogPlainError(r'Error missing file: %s' % (path))
continue
if filecmp.cmp(FilePath, path, False):
return True
return False
def SetOldestOriginal(self):
Trace(self.Folder)
if not sql.tables(self.Table):
return
count = 0
rows = sql.execute('select distinct original from %s where original <> 0' % (self.Table), Flatten=True)
for original in rows:
count += 1
dbg_print(original, count)
rows = sql.execute('select idx, modified_date, path from %s where idx=%d or original=%d order by modified_date ASC' % (self.Table, original, original))
data = []
for row in rows:
filePath = row[2]
if os.path.exists(filePath):
oldest = row
newOriginal = oldest[0]
sql.execute('update %s set original=%s where idx=%s or original=%s' % (self.Table, newOriginal, original, original))
break
sql.execute('update %s set original=0 where idx=original' % (self.Table))
def fix_album_names(self):
Trace()
artists = sql.execute('select distinct artist from %s' % (self.Table), Flatten=True)
for idx, artist in enumerate(artists):
rows = sql.execute('select distinct album from %s where artist=?' % (self.Table), Data=[artist], Flatten=True)
if len(rows) > 1:
lowered = list(map(str.lower, rows))
lowered = [ re.sub(r'\W+', '', row) for row in lowered ]
if len(list(set(lowered))) == len(rows):
continue
values = list(range(len(lowered)))
values.reverse()
for index in values:
try:
found = lowered[0 : index].index(lowered[index])
except:
continue
src_album = rows[index]
dst_album = rows[found]
#print('Move (%s) (%s)' % (src_album, dst_album))
dst_folders = sql.execute('select distinct old_folder from %s where artist=? and album=?' % (self.Table), Data=[artist, dst_album], Flatten=True)
if len(dst_folders) > 1:
Error('multiple dest folders', dst_folders)
Exit()
dst_folder = dst_folders[0]
move_rows = sql.execute('select idx,path,title from %s where artist=? and album=?' % (self.Table), Data=[artist, src_album])
for row in move_rows:
ext = os.path.splitext(row[1])[1]
title = row[2]
fileName = '%s%s' % (title, ext)
newPath = GenUniqueFileNamePlain(fileName, dst_folder, '')
#print(newPath)
sql.execute("update %s set new_path=? , new_folder=? where idx=?" % (self.Table), Data=[newPath, os.path.dirname(newPath), row[0]])
def ChangeOriginal(self, old, new):
if not sql.tables(self.Table):
return
sql.execute('update %s set original=%s where idx=%s or original=%s' % (self.Table, new, old, old), Verbose=True)
sql.execute('update %s set original=0 where idx=%s' % (self.Table, new), Verbose=True)
def UnRegister(self):
sql.drop_table(self.Table)
if sql.tables(FolderComp.RegistryTable):
sql.execute(Expand("delete from {0} where folder=?".format(FolderComp.RegistryTable)), Verbose=self.Verbose, Data=[self.Folder])
def remove_all_dups():
Trace()
table = FolderComp.FolderTable
query = Expand(r'delete from [table] Where original <> 0')
return sql.execute(query, Flatten=True)
def GetOriginals(self, Limit=None, Types='[AllMediaTypes]'):
Trace(self.Folder)
if not sql.tables(self.Table):
return []
return sql.execute('select DISTINCT B.idx, B.path from %s as A, %s as B where A.original <> 0 and B.idx = A.original' % (self.Table, self.Table))
def GetDuplicates(self, Original):
#Trace(self.Folder)
if not sql.tables(self.Table):
return []
return sql.execute('select idx, path from %s where original = %d' % (self.Table, Original))
def UnRegister(self):
sql.drop_table(self.Table)
if sql.tables(FindDups.RegistryTable):
sql.execute(Expand("delete from {0} where folder='[Folder]'".format(FindDups.RegistryTable)), Verbose=self.Verbose)