FlintSable
6/9/2017 - 9:57 PM
sqlite
sqlite
import os
import sys
import sqlite3
__version__ = 'this is version 1.0.2'
USAGE = 'To reorganize photos in Photo Shop Elements Database. Must enter in the location of the Photos and the location of the Database'
DEBUG = False
NEW_RES = 2048
def zero(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 46, "")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def one(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 51, "")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def two(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_decimal_table VALUES(?,53,-181)', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def three(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_decimal_table VALUES(?,52,-91)', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def four(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_decimal_table VALUES(?,54,0)', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def five(meta_id, tag_id, conn):
t = (tag_id, meta_id)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def six(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1214, "")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def seven(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1216, "deny_download deny_order_prints")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def eight(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1218, "")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def nine(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1220, "")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def ten(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1222, "gtlmemories")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def eleven(meta_id, tag_id, conn):
m = (meta_id,)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1224, "gtlmemories")', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
def twelve(meta_id, tag_id, conn):
tag_id_xml = "%d.xml" % tag_id
s = (meta_id,)
m = (meta_id, tag_id_xml)
t = (tag_id, meta_id)
conn.execute('INSERT INTO metadata_string_table VALUES(?, 1226, ?)', m)
conn.execute('INSERT INTO tag_to_metadata_table VALUES(?,?)', t)
conn.execute('UPDATE _dbl_auto_id_table SET last_assigned_id = ?', s)
options = {0 : zero, 1 : one, 2 : two, 3 : three, 4 : four, 5 : five, 6 : six, 7 : seven, 8 : eight, 9 : nine, 10 : ten, 11 : eleven, 12 : twelve,}
def dbconnect(db):
conn = sqlite3.connect(db)
c = conn.cursor()
def createalbums(fold, db):
#try to create pse albums based on the directory entered in the command.
conn = sqlite3.connect(db)
parent_id = conn.execute('SELECT id FROM tag_table WHERE name="collection_ns"').fetchone()[0]
foldlower = fold.split(':')[1].replace('\\','/').lower()
tagname = os.path.basename(fold)
print(fold)
print(tagname)
c = conn.cursor()
t = (tagname, parent_id)
c.execute('SELECT id FROM tag_table WHERE name = ? AND parent_id = ?', t)
tag_data = c.fetchone()
if tag_data == None :
print('%s does not exist...Adding Folder' % tagname)
tag_id = conn.execute("SELECT last_assigned_id FROM _dbl_auto_id_table").fetchone()[0] + 1
next_assigned_id = tag_id + 1
#adds a new row to the tag_table using the
t = (tag_id,tagname,parent_id)
c.execute('INSERT INTO tag_table VALUES(?,102,?,?,100,"collection",1,1,0,0,0)', t)
count = 0
while count < 13:
print('Inserting metadata %d' % count)
options[count](tag_id + count + 1, tag_id, conn)
count = count + 1
conn.commit()
def albumorder(db):
conn = sqlite3.connect(db)
c = conn.cursor()
parent_id = c.execute('SELECT id FROM tag_table WHERE name="collection_ns"').fetchone()[0]
albumoriginal = []
neworder = []
t = (parent_id,)
c.execute('SELECT id,name FROM tag_table WHERE parent_id=? ORDER BY name COLLATE NOCASE ASC', t)
for row in c:
albumoriginal.append(row)
neworder.append(row[0])
neworder.sort()
count = 0
for album in albumoriginal:
albumoriginal[count] = albumoriginal[count] + (neworder[count],)
count = count + 1
count = 0
proper_data = []
for album in albumoriginal:
if albumoriginal[count][0] != albumoriginal[count][2]:
t = (albumoriginal[count][0],)
c.execute('SELECT media_id,media_index FROM tag_to_media_table WHERE tag_id =?', t)
for row in c:
row = row + (albumoriginal[count][2],albumoriginal[count][0])
proper_data.append(row)
t = (albumoriginal[count][1],count,albumoriginal[count][2],)
c.execute('UPDATE tag_table SET name=?,sibling_index=? WHERE id=?', t)
print('Updating tag table')
count = count + 1
conn.commit()
x = 0
if proper_data != []:
for prop in proper_data:
t = (proper_data[x][2],proper_data[x][0],proper_data[x][3],)
c.execute('UPDATE tag_to_media_table SET tag_id=? WHERE media_id=? AND tag_id=?', t )
x = x + 1
print('Updating tag_to_media_table')
conn.commit()
conn.close()
def pseorder(fold,db,count = 0):
foldlower = fold.split(':')[1].replace('\\','/').lower() + "/"
tagname = os.path.basename(fold)
print(tagname)
conn = sqlite3.connect(db)
c = conn.cursor()
t = (tagname,)
c.execute('SELECT id FROM tag_table WHERE name = ?', t)
tag_id = c.fetchone()
tag_id = tag_id[0]
t = (foldlower,)
c.execute('SELECT id,filename_search_index FROM media_table WHERE filepath_search_index = ? ORDER BY filename_search_index COLLATE NOCASE ASC', t)
for row in c:
media_id = row[0]
l = conn.cursor()
t = (media_id, tag_id,)
l.execute('SELECT media_index FROM tag_to_media_table WHERE media_id = ? AND tag_id = ?', t)
data = l.fetchone()
if data == None:
print('Does Not Exist...Adding')
t = (media_id, tag_id, count,)
l.execute('REPLACE INTO tag_to_media_table(media_id,tag_id,media_index) VALUES(?,?,?)', t)
else:
if data[0] != count:
print('Not in order...Changing')
t = (media_id, tag_id, count,)
l.execute('REPLACE INTO tag_to_media_table(media_id,tag_id,media_index) VALUES(?,?,?)', t)
count = count + 1
conn.commit()
conn.close()
def main(fdir):
for root, dires, files in os.walk(fdir):
if root != fdir:
if root.count(os.path.sep) == 4: # 4 For Foga, 2 For MLGA
print(root.count(os.path.sep))
print(fdir.count(os.path.sep))
pseorder(root, DB_PATH)
# you are here
def mainer(fdir):
for root, dires, files in os.walk(fdir):
if root != fdir:
if root.count(os.path.sep) == 4: # this one too
print(root.count(os.path.sep))
print(fdir.count(os.path.sep))
createalbums(root, DB_PATH)
if '--version' in sys.argv:
print(__version__)
else:
if __name__ == '__main__':
if '--help' in sys.argv:
print(USAGE)
elif '--organize' in sys.argv:
albumorder(sys.argv[2])
print(sys.argv[2])
elif '--createalbums' in sys.argv:
print('creating albums')
ROOT_PATH = sys.argv[1]
DB_PATH = sys.argv[2]
mainer(ROOT_PATH)
else:
print(sys.argv[2])
ROOT_PATH = sys.argv[1]
DB_PATH = sys.argv[2]
mainer(ROOT_PATH)
albumorder(DB_PATH)
main(ROOT_PATH)
import os
clear = lambda: os.system('clear') #on Linux System
clear()
import os
clear = lambda: os.system('cls') #on Windows System
clear()