Clipboard Manager AutoHotkey

/**
 * clipboard manager
 * by Blauhirn eochgls@web.de
 * 20160301_07.54
 * .___.
 * {o,o}
 * /)___)
 * --"-"-
 */
#NoEnv
#Persistent
SetWorkingDir %A_ScriptDir%
#include SQLiteDB.ahk
db_name := "schinken_sqlite_data.db"
init()
return


;****************************************
;********** CUSTOM HOTKEYS **************



    ; OPEN CLIPBOARD CHOICE DIALOG
^+c::
    show_and_choose_clipboards()
return


    ; SAVE COMPLETE CLIPBPOARD HISTORY INTO FILE as csv
^+x::
    exportClipboards("clipboards.csv")
return




;****************************************
;****************************************




init() {
    global db, db_name
    db := new SQLiteDB
    db_existed := true
    ifnotexist,%db_name%
        db_existed := false
    if(! db.openDB(db_name) ) {
        err(db.errormsg " Could not create Database. Program will exit")
        exitapp
    }
    onExit, onExittt
    if(!db_existed) {
        goSub, setupClipboardsTable
        msgbox, Database was set up
    }
}
onExittt:
    db.closeDB()
exitapp

setupClipboardsTable:
    /*
    0   ID
    1   time
    2   processExe
    3   clp
    */
    result := ""
    if(db.getTable("SELECT * FROM clipboards LIMIT 1 `;", result) ) {
        if(result["rows"][1]!="") {
            msgbox, 4,, table clipboards already exists!!! DELETE and OVERWRITE?
            ifmsgbox, no
                return
            if(! db.exec("DROP TABLE clipboards`;") ) {
                err(db.errormsg " Could not delete clipboards table. Program will exit")
                exitapp
            }
        }
    }
    db.exec("CREATE TABLE clipboards (  ID Integer PRIMARY KEY AUTOINCREMENT NOT NULL, time Integer NOT NULL DEFAULT (strftime('%s','now')), processExe Text, clp Text NOT NULL )`;")

    db.exec("CREATE INDEX time_ind ON clipboards (time)`; CREATE INDEX clp_ind ON clipboards (clp)`; CREATE INDEX processExe_ind ON clipboards (processExe)`;")

    loop, 10
        db.exec("INSERT INTO clipboards (ID, processExe, clp) VALUES (" a_index ", 'x', 'empty')`;")
return

assignClps_current() {
    global db, clps_current
    clps_current := []
    result := ""
    if(! db.getTable("SELECT * FROM clipboards WHERE ID BETWEEN 1 AND 10 ORDER BY ID ASC`;", result) ) {
        err(db.errormsg)
        exit
    }
    for i,row in result["rows"]
        clps_current.push( [ row[2], row[3], row[4] ] )
}

exportClipboards(filename) {
    global db

    recordSet := ""
    if(! db.query("SELECT * FROM clipboards`;", recordSet) ) {
        err(db.errormsg)
        return
    }
    file := fileOpen(filename, "w")
    if(!isobject(file)) {
        msgbox, err("invalid file name")
        return
    }
    tooltip, WRITING TO CLIPBOAORDS.TXT...................,0,0
    header := ""
    for i,column in recordSet.columnNames
        header .= column ", "
    file.writeLine(header)
    file.writeLine("")
    file.writeLine("")
    while(recordSet.next(row) == true) {
        line := ""
        for k,v in row {
            line .= v ", "
        }
        file.writeLine(line)
    }
    file.close()
    run %filename%
    tooltip
}

~^x::
~^c::
newClipboard_()
return
newClipboard_() {
    global db, clps_current

    sleep, 100
    if(clipboard=="") ; picture, file, ..
        return

    if(clps_current=="")
        assignClps_current()

    winget, processExe, processname, A
    clp_new_short := substr(clipboard,1,2000)

    newElem := [0, processExe, clp_new_short] ; newElem[0] is not set

    ; already in top 10?
    for i,clp_current in clps_current {
        if(newElem[3]==clp_current[3]) {
            clps_current.removeAt(i)
            clps_current.insertAt(1, newElem) ; shifts
            return
        }
    }
    ; new clipboard value

    proc := db.EscapeStr(newElem[2])
    clip := db.EscapeStr(newElem[3])
    ; append to table
    if(! db.exec("INSERT INTO clipboards (processExe, clp) VALUES (" proc ", " clip ")`;") ) {
        err(db.errormsg)
        return
    }
    result := ""
    db.getTable("SELECT time FROM clipboards WHERE ID = (SELECT MAX(ID) FROM clipboards)`;", result)
    ; set time from db for new entry
    newElem[1] := result["rows"][1][1]

    ; put new entry in top 10
    clps_current.insertAt(1, newElem)
    clps_current.pop() ; 11th

    ; update top 10 in table
    for i, clp_current in clps_current {
        proc := db.EscapeStr(clp_current[2])
        clip := db.EscapeStr(clp_current[3])
        db.exec("UPDATE clipboards SET time = " clp_current[1] ", processExe = " proc ", clp = " clip " WHERE ID = " i "`;")
    }
}

show_and_choose_clipboards() {
    global db, clps_current

    if(clps_current=="")
        assignClps_current()

    winget, activeWindow, ID, A

    bla := ""
    for i, clp_current in clps_current {
        clp_preview := regexreplace(substr(clp_current[3], 1, 130), "[`r`n`t]", " ")
        time_formatted := time_unix2human(clp_current[1])
        formattime, time_formatted, %time_formatted%, hh:mm
        bla .= "`r`n((" i ")) " time_formatted "`t" clp_preview ; clp_current[2] clp_current[3]
    }
    inputbox, clpbNbr, CHOOSE YOUR CLIPBOARD, %bla%,,1100,400
    winActivate ahk_id %activeWindow%
    if errorlevel = 1
        return
    if(clpbNbr < 1) {
        run clipboards.ini
        return
    }
    chosenElem := clps_current[clpbNbr]

    clps_current.removeAt(clpbNbr)
    clps_current.insertAt(1, chosenElem)

    clipboard := chosenElem[3]

    winActivate, ahk_id %activeWindow%
}

err(s) {
    out := "STACK TRACE:"
    for i, info in Traceback()
        out .= "`r`nCalled by: " info.caller ", Line: " info.line
    msgbox % s "`r`n`r`n" out
}
Traceback(actual:=false)
{
    r := [], i := 0, n := actual ? 0 : A_AhkVersion<"2" ? 1 : 2
    Loop {
        e := Exception(".", offset := -(A_Index + n))
        if (e.What == offset)
            break
        r[++i] := { "file": e.file, "line": e.Line, "caller": e.What, "offset": offset + n }
    }
    return r
}
Time_unix2human(time)

{

    human=19700101000000

    time-=((A_NowUTC-A_Now)//10000)*3600    ;时差/Time lag

    human+=%time%,Seconds

    return human

    }

Time_human2unix(time)

{

    time-=19700101000000,Seconds

    time+=((A_NowUTC-A_Now)//10000)*3600    ;时差/Time lag

    return time

    }