#!/usr/bin/python
# -*- coding: utf-8 -*-
import sqlite3 as lite
import sys
import pdb
import tkMessageBox
import Tkinter as tk
import ttk
import tkFileDialog
from Tkinter import Tk, Text, Entry, OptionMenu, BOTH, W, N, E, S, StringVar
from ttk import Frame, Button, Label, Style
print 'sqlite version: %s' % lite.version
def readFile(filePath):
    try:
        fin = open(filePath, "rb")
        return fin.read()
    except IOError, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
    finally:
        if fin:
            fin.close()
class SqliteUtil:
    
    def __init__(self, sqliteFile):
        self.conn = lite.connect(sqliteFile)
        self.cur = self.conn.cursor()
                
    def fetchall(self, sqlStmt):
        self.cur.execute(sqlStmt)
        return self.cur.fetchall()
                
    def fetchone(self, sqlStmt):
        self.cur.execute(sqlStmt)
        return self.cur.fetchone()
        
    def setBinData(self, tableName, binColumnName, filePath, whereClause):
        try:
            data = readFile(filePath)
            binData = lite.Binary(data)
            sql = 'UPDATE %s SET %s=? WHERE %s' % (tableName, binColumnName, whereClause)
            print sql
            self.cur.execute(sql, (binData,) )
            print 'Affected rows using sqlite.Binary(): %d' % self.cur.rowcount
            self.cur.execute(sql, [buffer(data)])
            print 'Affected rows using buffer(): %d' % self.cur.rowcount
            self.conn.commit()
        except lite.Error, e:
            if self.conn:
                self.conn.rollback()
            print "Error %s:" % e.args[0]
            sys.exit(1)
        #finally:
            #if conn:
                #conn.close()
class WordFrame(Frame):
    
    def __init__(self, parent):
        Frame.__init__(self, parent)
        self.parent = parent
        self.initUI()
        self.sqliteUtil = None
        
    @staticmethod
    def refreshOptionMenu(om, oldVarChoice, newChoices):
        # Reset var and delete all old options
        oldVarChoice.set('')
        #oldVarChoice.set(newChoices[0])
        om['menu'].delete(0, 'end')
        # Insert list of new options (tk._setit hooks them up to var)
        for choice in newChoices:
            om['menu'].add_command(label=choice, command=tk._setit(oldVarChoice, choice))
        
    def browseSqlite(self):
        ftypes = [('Sqlite files', '*.sqlite'), ('All files', '*')]
        dlg = tkFileDialog.Open(self, filetypes=ftypes)
        sqliteFile = dlg.show()
        if sqliteFile != '':
            self.varFilename.set(sqliteFile)
            
            self.sqliteUtil = SqliteUtil(sqliteFile)
            tables = self.sqliteUtil.fetchall("SELECT name FROM sqlite_master WHERE type='table'")
            #self.varTable.set(tuple(map(lambda x: x[0], tables)))
            self.refreshOptionMenu(self.omTables, self.varTable, map(lambda x: x[0], tables))
            
            # fetch the cols
            cols = self.sqliteUtil.fetchall('PRAGMA table_info(%s)' % (tables[0]))
            self.refreshOptionMenu(self.omCols, self.varCol, map(lambda x: x[1], cols))
            # pdb.set_trace()
        
    def initUI(self):
        self.parent.title("导入音频数据")
        self.style = ttk.Style()
        self.style.theme_use("default")
        self.pack(fill=BOTH, expand=1)
        self.centerWindow()
        # self.columnconfigure(1, weight=1)
        # row 0: file selection
        panelFileSelection = Frame(self)
        Label(panelFileSelection, text="sqlite文件:").pack(side="left", fill=None, expand=False)
        self.varFilename=StringVar()
        self.txtFileName = Entry(panelFileSelection, textvariable=self.varFilename, width=60)
        self.txtFileName.pack(side="left", fill=None, expand=True)
        btnBrowse = Button(panelFileSelection, text="...", command=self.browseSqlite)
        #btnBrowser.grid(row=0, column=4, pady=4)
        btnBrowse.pack(side="right", fill=None, expand=False)
        
        panelFileSelection.grid(row=0, column=0, columnspan=4, sticky="we")
        # Row 1: word table/audio column name
        self.varTable = StringVar(self)
        Label(self, text="表名:").grid(row=1, sticky=W, padx=5, pady=4)
        self.omTables = OptionMenu(self, self.varTable, '')
        self.omTables.grid(row=1, column=1, sticky=E+W)
        self.omTables['menu'].bind("<Unmap>", self.onSelectTable)
        
        self.varCol = StringVar(self)
        Label(self, text="列名:").grid(row=1, column=2, sticky=E, padx=5, pady=4)
        self.omCols = OptionMenu(self, self.varCol, '')
        self.omCols.grid(row=1, column=3, sticky=E+W)
        # Row 3: close/import buttons
        btnImport = Button(self, text="导入", command=self.importAudios)
        btnImport.grid(row=2, column=2, padx=5, pady=5)
        
        btnClose = Button(self, text="关闭", command=self.quit)
        btnClose.grid(row=2, column=1, padx=5, pady=5)
        
    def quit(self):
        print 'in quit()'
        print self.sqliteUtil
        if not (self.sqliteUtil is None) and not (self.sqliteUtil.conn is None):
            print 'to close conn'
            self.sqliteUtil.conn.close()
        print 'to exit()'
        sys.exit(0)
        
    def importAudios(self):
        # tkMessageBox.showinfo("col: ", self.varCol.get())
        
	# use loop here
        audioFile = 'C:/Users/fouding/Desktop/fetion_received_files/a bas.mp3'
        self.sqliteUtil.setBinData(self.varTable.get(), self.varCol.get(), audioFile, "word='think'")
    def onSelectTable(self, val):
        print 'onSelectTable'
        sender = val.widget
        tblName = sender.get(sender.curselection())
        
        # fetch the cols
        cols = self.sqliteUtil.fetchall('PRAGMA table_info(%s)' % (tblName))
        self.refreshOptionMenu(self.omCols, self.varCol, map(lambda x: x[1], cols))
    def centerWindow(self):
        w = 500
        h = 200
        
        sw = self.parent.winfo_screenwidth()
        sh = self.parent.winfo_screenheight()
        x = (sw - w) / 2
        y = (sh - h) / 2
        self.parent.geometry('%dx%d+%d+%d' % (w, h, x, y))
def main():
    root = tk.Tk()
    app = WordFrame(root)
    root.mainloop()
if __name__ == '__main__':
    main()