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 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 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 SQL_UnitTest(GenerateTestData=False):
Trace()
data = [
[ 55, 54, 53, 52, 51],
[ 55, 44, 43, 42, 41],
[ 35, 34, 33, 32, 31],
[ 35, 34, 33, 22, 21],
[ 15, 14, 13, 12, 11],
[ 5, 4, 3, 2, 1],
]
columns = [
[ 'col0', 'int' ],
[ 'col1', 'int' ],
[ 'col2', 'int' ],
[ 'col3', 'int' ],
[ 'col4', 'int' ],
]
primaryKeys = ['col0', 'col1']
sorted = sql.sort_data(data, [0, 1, 2, 3], columns)
PrettyPrintList(sorted)
for idx in range(0, len(data[0]), 1):
UnitTest.Step('Check column [idx]')
col = [row[idx] for row in data]
sorted = [row[idx] for row in data]
sorted.sort()
sorted.reverse()
UnitTest.Verify(col == sorted, 'Column [idx] is sorted correctly')
Log('col: %s' % col)
Log('sorted: %s' % sorted)
table = 'SQL_UnitTest'
if GenerateTestData:
jsonData = dictn()
jsonData.expectedResultsJson = data
UnitTest.SaveExpectedResults(jsonData)
UnitTest.Step('Write & Read from table')
sql.write_to_table(table, data, columns, Verbose=True)
read = sql.select(table)
UnitTest.Verify(data == read)
UnitTest.Step('Update Tests')
row = [
[ 55, 54, 1, 1, 1]
]
count = sql.update(table, row, WhereClause=r'Where col0=55 and col1=54', Verbose=True)
Log('count=[count]')
UnitTest.Verify(count == 1)
updated = sql.select(table, WhereClause=r'Where col0=55 and col1=54', Verbose=True)
UnitTest.VerifyMatch(updated, row)
UnitTest.Step('Write & Read unique data')
unique_data = []
unique_data.append(list(range(100, 200, 20)))
unique_data.append(list(range(200, 300, 20)))
unique_data.append(list(range(300, 400, 20)))
unique_data.append(list(range(400, 500, 20)))
unique_data.append(list(range(500, 600, 20)))
PrettyPrint(unique_data)
sql.write_to_table(table, unique_data, columns, PrimaryKey=primaryKeys, UseExistingTable=False, IdentityIndex=False, Verbose=True)
read = sql.select(table)
PrettyPrint(read)
UnitTest.Verify(unique_data == read)
UnitTest.Step('Write & Read indexed unique data')
sql.write_to_table(table, unique_data, columns, PrimaryKey=None, UseExistingTable=False, IdentityIndex=True, Verbose=True)
read = sql.select(table)
PrettyPrint(read)
[row.insert(0, idx) for idx, row in enumerate(unique_data, 1)]
UnitTest.Verify(unique_data == read)
def add_files(self, Debug=False):
Trace(self.Folder)
#loweredFolder = Folder.lower()
data = []
debugData = []
count = 0
for file in FindFiles(self.Folder):
count += 1
if not Debug:
dbg_print(count)
ext = os.path.splitext(file)[1]
if 'Music' != Home.MediaTypeFromExtension(ext):
#print('skipping', file)
continue
try:
tag = stagger.read_tag(file)
except:
#print('failed', file)
self.errors.append(file)
continue
if Debug:
dump_mp3_tags(tag)
artist = ''
if not artist:
artist = tag.album_artist
if not artist:
artist = tag.artist
if not artist:
artist = tag.composer
artist = normalize_name(artist)
folder = os.path.dirname(file)
album = tag.album
album = normalize_name(album)
title = tag.title
title = normalize_name(title)
if album == '' or title == '':
continue
newPath = r'%s%s%s%s%s%s%s%s' % (self.Folder, os.sep, artist, os.sep, album, os.sep, title, ext)
oldPath = file
if Debug:
if os.path.dirname(newPath).lower() != os.path.dirname(oldPath).lower():
debugData.append([newPath, oldPath])
data.append([file, folder, artist, album, title, newPath, os.path.dirname(newPath), os.path.dirname(oldPath)])
#if len(data) == 20:
# break
print()
#PrettyPrint(data)
PrettyPrint(debugData)
sql.write_to_table(self.Table, data, MP3Rename.Columns, UseExistingTable=self.UseExistingTable, SkipInsert=['idx'], Verbose=self.Verbose)
Log('Inserted %d rows' % (len(data)))