def execute():
print 'Connecting to MySQL...'
db = DB('mymusic32')
print 'Loading data...'
paths = db.perform_sql('select distinct(strPath) from songview;')
print 'Scanning dirs...'
dirs = []
for path in paths:
for key, value in REPLACEMENTS.iteritems():
local_path = path.replace(key, value)
if not any(directory in path for directory in EXCLUDE):
try:
dirs.append({'time': os.path.getctime(local_path),
'orig_path': path})
except Exception:
pass
dirs = sorted(dirs, key=lambda k: k['time'])
i = 0
start_id = int(db.perform_sql(
'SELECT idAlbum from songview ORDER BY idAlbum DESC LIMIT 1;')[0])
print 'Beginning to re-order album IDs, beginning with idAlbum={0}'.format(start_id + 1)
for directory in dirs:
i += 1
new_id = start_id + i
strPath = db.escape_string(directory['orig_path'])
sql = 'select idAlbum from songview where strPath="{0}";'
strPath_album_ids = db.perform_sql(sql.format(strPath))
try:
assert(all(strPath_album_ids[0] == album_id for album_id in strPath_album_ids))
except AssertionError:
print 'Multiple album IDs for media in {0}'.format(strPath)
continue
for table in ['album', 'albuminfo', 'album_artist', 'album_genre', 'song']:
sql = 'update {table} set idAlbum={new} where idAlbum={old};'
db.perform_sql(sql.format(table=table, new=new_id,
old=strPath_album_ids[0]))
sql = 'update {table} set media_id={new} where media_id={old};'
db.perform_sql(sql.format(table='art', new=new_id,
old=strPath_album_ids[0]))
if i % 100 == 0:
print 'Processed {0} of {1} albums'.format(i, len(dirs))
db.perform_sql('alter table album auto_increment={0};'.format(i+start_id+1))
print 'Done!'
update_data = {}
if rating > 0:
update_data['rating'] = int(rating)
if play_count > 0:
update_data['play_count'] = play_count
if last_played != 'N/A':
update_data['last_played'] = last_played
if update_data and update_data != last_sync.get(path, {}):
path_split = path.split('/')
del path_split[0]
strPath = u'smb://192.168.92.20/' + u'/'.join(path_split[:-1]).replace('"', '\\"') + '/'
strFileName = path_split[-1].replace('"', '\\"')
sqlstrings = []
if update_data.get('rating'):
sqlstrings.append(u'rating = {0}'.format(update_data['rating']))
if update_data.get('play_count'):
plays_since_last_sync = update_data['play_count'] - last_sync.get(path, {}).get('play_count', 0)
if plays_since_last_sync:
sqlstrings.append(u'iTimesPlayed = IF(iTimesPlayed IS NULL, {plays}, iTimesPlayed + {plays})'.format(plays=plays_since_last_sync))
if update_data.get('last_played'):
sqlstrings.append(u'lastplayed = IF(lastplayed IS NULL OR lastplayed < "{lp}", "{lp}", lastplayed)'.format(lp=update_data['last_played']))
if sqlstrings:
sql = u'UPDATE songview SET {0} WHERE strPath = "{1}" and strFileName = "{2}";'
sql = sql.format(', '.join(sqlstrings), strPath, strFileName).encode('utf-8')
db.perform_sql(sql)
results[path] = update_data
print 'Dumping sync to last_sync.json'
with open('last_sync.json', 'w') as f:
json.dump(results, f, indent=4)
sql += '{0} from {1};'.format(column, table)
rows = db.perform_sql(sql)
sql = "update {table} set `{column}`='{new}' where `{column}`='{old}';"
for row in rows:
old_value = row.replace('\\', '\\\\').replace("'", "\\'")
db.perform_sql(sql.format(
table=table, column=column,
new=old_value.replace('\\\\\\\\', 'smb://').replace('\\\\', '/'),
old=old_value))
if __name__ == '__main__':
VIDEOS_FIXES = [
# List of (table, column) tuples to be fixed
('movieview', 'strPath'),
('episodeview', 'strPath'),
('movieview', 'c22'),
('episodeview', 'strShowPath'),
('episode', 'c18'),
('path', 'strPath'),
('tvshow', 'c16'),
('tvshowview', 'strPath')
]
for entry in VIDEOS_FIXES:
perform_fix(videos_db, entry[0], entry[1])
rows = videos_db.perform_sql('select strFileName from movieview where strFileName like "stack://\\\\\\\\%";')
perform_fix(videos_db, 'movieview', 'strFileName', rows=rows)
perform_fix(music_db, 'songview', 'strPath', distinct=True)
perform_fix(music_db, 'path', 'strPath', distinct=True)
