sqlite

clear

import os
clear = lambda: os.system('clear') #on Linux System
clear()

import os
clear = lambda: os.system('cls') #on Windows System
clear()

tableOrganization.py

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)