def DropOldTables():
tables = [
"Music_Table",
"Pictures_Table",
"SortData",
"Video_Table",
"_Backup_Files_CarlosMac",
"_Backup_Files_Code",
"_Backup_Files_GigaByteMotherBoard",
"_Backup_Files_SaoriMac",
"_Media",
"_Pictures",
"_Temp_FolderComp",
"_Temp_FolderComp2",
"_music",
"_videos",
"c_Temp_FolderComp",
"c_Temp_FolderComp2",
"d_Pictures",
"d_music",
"d_videos",
"g_Backup_Files",
"g_Backup_Files_CarlosMac",
"g_Backup_Files_Code",
"g_Backup_Files_GigaByteMotherBoard",
"g_Backup_Files_Samsung_GS2",
"g_Backup_Files_SaoriMac",
"g_Backup_Files__CCC_Archives",
"sort_data",
]
for table in tables:
sql.drop_table(table.strip())
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 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 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 ScanFolders(self):
Trace(self.Folder)
folderRows = []
folderName = os.path.basename(self.Folder)
root = os.path.dirname(self.Folder)
if not os.path.exists(self.Folder):
Error('Missing [Folder]')
stats = os.stat(self.Folder)
folderRows.append([folderName, self.Folder, root, stats.st_mtime, stats.st_ctime, 0])
count = 0
for folder in FindFolders(self.Folder):
folderName = os.path.basename(folder)
root = os.path.dirname(folder)
stats = os.stat(folder)
count += 1
dbg_print('%d %s' % (count, folder))
folderRows.append([folderName, folder, root, stats.st_mtime, stats.st_ctime, 0])
print('')
Log(len(folderRows), r'files in [Folder]')
sql.write_to_table(self.Table, folderRows, FolderComp.Columns, UseExistingTable=True, SkipInsert=['idx'], Verbose=self.Verbose)
sql.write_to_table(FolderComp.FolderTable, folderRows, FolderComp.Columns, UseExistingTable=True, SkipInsert=['idx'], Verbose=self.Verbose)
Verbose('Inserted %d rows' % (len(folderRows)))
def ScanFiles(self, Types='[AllMediaTypes]'):
TraceVerbose(Types, self.Folder)
Types = to_list(Expand(Types))
fileRows = []
extensions = flatten([ Globals.Media[mediaType].Extensions for mediaType in Types ])
self.Register()
count = 0
for root, dirnames, filenames in os.walk(self.Folder):
for filename in filenames:
basename, ext = os.path.splitext(filename)
ext = ternary(ext.startswith('.'), ext[1:], ext)
if ext.lower() not in extensions:
continue
filepath = os.path.join(root, filename)
count += 1
dbg_print(count, filepath)
try:
stats = os.stat(filepath)
mediaType = Home.MediaTypeFromExtension(ext)
fileRows.append([filename, filepath, root, stats.st_size, stats.st_mtime, stats.st_ctime, 0, mediaType])
except (KeyboardInterrupt, SystemExit):
raise
except:
LogPlainError('failed to process %s' % filepath)
pass
print()
Log(len(fileRows), r'files in [Folder]')
sql.write_to_table(self.Table, fileRows, FindDups.Columns, UseExistingTable=True, SkipInsert=['idx'], Verbose=self.Verbose)
Verbose('Inserted %d rows' % (len(fileRows)))
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 Register(self):
row = None
if sql.tables(FolderComp.RegistryTable):
row = sql.select(FolderComp.RegistryTable, WhereClause=Expand(r"WHERE folder=?"), Verbose=self.Verbose, Data=[self.Folder])
now = time.time()
if not row:
row = [self.Folder, now, now]
else:
row = row[0]
row[2] = now
sql.write_to_table(FolderComp.RegistryTable, [row], FolderComp.RegistryColumns, UseExistingTable=True, IdentityIndex=True, Verbose=self.Verbose)
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 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 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 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 AddNewFile(self, FilePath, DestFolder, KeepOriginals=False):
if not sql.tables(self.Table):
return False
destFile = self.MoveFile(-1, FilePath, DestFolder, False, KeepOriginals=KeepOriginals)
if not destFile:
return False
# add to Database
filename = os.path.basename(destFile)
folder = os.path.dirname(destFile)
stats = os.stat(destFile)
basename, ext = os.path.splitext(filename)
rows = []
rows.append([filename, destFile, folder, stats.st_size, stats.st_mtime, stats.st_ctime, 0, Home.MediaTypeFromExtension(ext)])
sql.write_to_table(self.Table, rows, FindDups.Columns, UseExistingTable=True, SkipInsert=['idx'], Verbose=self.Verbose)
return destFile
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 select_folder_rows(self, FolderName='', Where='', **kwargs):
Data = None
if FolderName:
Data = [ FolderName ]
if not Where:
Where = "Where foldername=?" % (Data[0])
else:
Where += " and foldername=?" % (Data[0])
return sql.select(FolderComp.FolderTable, WhereClause=Where, Data=Data, Verbose=self.Verbose, **kwargs)
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 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 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 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 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 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 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 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]])