#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import os
from   os.path import join, getsize, getmtime
from   time    import strftime, localtime, sleep
from   sqlite3 import dbapi2 as sqlite

SELECTALL = "SELECT dirname, filename, filesize, filedate, id_file  "
SELECTCOUNT = "SELECT Count(*) "



class QueryMask(object):
    def __init__(self, dirname = '%', filename = '%', compare = '>=',
                 filesize = '0', fileunit = 'K', filedate = '%',
                 ignorecase = True, orderby = 'filename', limit = '250000'):
        self.dirname    = dirname
        self.filename   = filename
        self.compare    = compare
        self.filesize   = filesize
        self.fileunit   = fileunit
        self.filedate   = filedate
        self.ignorecase = ignorecase
        self.orderby    = orderby
        self.displimit  = limit


class StoreManDb(object):
    TABDIR  = 'nasexptbdir'
    TABFILE = 'nasexptbfile'
    TABSCAN = 'nasexptbscan'

    def __init__(self, filename = 'storeman.db3', updateRecords = None,
                 updateMatches = None):
        self.filename = filename
        if updateRecords == None:
            self.updateRecords = self.dummyUpdateRecords
        else:
            self.updateRecords = updateRecords
        if updateMatches == None:
            self.updateMatches = self.dummyUpdateMatches
        else:
            self.updateMatches = updateMatches
        self.priority = False
        self.connectOrCreate()
        self.lastdir = ''
        self.lastdirid = 0
        self.updatemodulus = 512
    #-------------------------------------------------------------------------------------------

    def connectOrCreate(self):        
        self.connection = sqlite.connect(self.filename, isolation_level = None)
        self.connection.text_factory = lambda x: unicode(x, "utf-8", "replace")

        self.cursor = self.connection.cursor()
        self.dircursor = self.connection.cursor()
        self.transaction = False
        ismuma, tabcount = self.checkDatabase()
        if tabcount < 2:                
            try:
                self.cursor.execute('CREATE TABLE ' + self.TABDIR +
                                    ''' (
                                         id_dir     integer primary key autoincrement,
                                         dirname    varchar(255)
                                        )''')
                self.cursor.execute('CREATE TABLE ' + self.TABFILE +
                                    ''' (
                                         id_file    integer primary key autoincrement,
                                         dir_id     integer key,
                                         filename   varchar(255),
                                         filesize   integer,
                                         filedate   datetime
                                        )''')
                self.cursor.execute('CREATE INDEX FileName ON ' + self.TABFILE + '(filename)') 
                self.cursor.execute('CREATE TABLE ' + self.TABSCAN +
                                    ''' (
                                         id_scan    integer primary key autoincrement,
                                         scandate   datetime,
                                         scandir    varchar(255)
                                        )''')
            except:
                pass
            
        self.recordcount = self.getRecordCount()
        self._updateRecords()
    #-------------------------------------------------------------------------------------------

    def dummyUpdateMatches(self, p1 = None):
        return
    #-------------------------------------------------------------------------------------------

    def dummyUpdateRecords(self, p1 = None):
        return
    #-------------------------------------------------------------------------------------------

    def setPriority(self, priority):
        self.priority = priority
    #-------------------------------------------------------------------------------------------
    
    def _updateRecords(self):
        self.updateRecords(self.recordcount)
        if self.priority: sleep(0.01)
    #-------------------------------------------------------------------------------------------

    def checkDatabase(self):
        self.commitTransaction()
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        record = self.cursor.fetchall()
        tabnames = []
        for item in record:
            tabnames.append(item[0])
        return self.TABDIR in tabnames and self.TABFILE in tabnames and \
               self.TABSCAN in tabnames, len(tabnames)
    #-------------------------------------------------------------------------------------------

    def deleteRecordById(self, recordid):
        self.commitTransaction()
        self.cursor.execute("DELETE FROM %s WHERE id_file == %s" % (self.TABFILE, recordid))
    #-------------------------------------------------------------------------------------------

    def getScanDates(self):
        self.commitTransaction()
        self.cursor.execute("SELECT scandate, scandir FROM %s" % (self.TABSCAN))
        records = self.cursor.fetchall()
        return records
    #-------------------------------------------------------------------------------------------

    def getDirCount(self):
        self.commitTransaction()
        try:
            self.cursor.execute("SELECT seq FROM SQLITE_SEQUENCE WHERE name = '%s'" %
                                                                       (self.TABDIR))
            record = self.cursor.fetchone()
            return record[0]
        except:
            return 0
    #-------------------------------------------------------------------------------------------

    def getRecordCount(self):
        self.commitTransaction()
        try:
            self.cursor.execute("SELECT seq FROM SQLITE_SEQUENCE WHERE name = '%s'" %
                                                                       (self.TABFILE))
            record = self.cursor.fetchone()
            return record[0]
        except:
            return 0
    #-------------------------------------------------------------------------------------------

    def getMatches(self, querymask):
        """   """
        filesize = querymask.filesize
        if querymask.fileunit != 'B': filesize *= 1024
        if querymask.fileunit == 'MB': filesize *= 1024
        query = "FROM %s, %s WHERE id_dir = dir_id AND dirname LIKE '%s' AND filename \
                 LIKE '%s' AND filesize %s '%s' AND filedate LIKE '%s'" \
                % (self.TABDIR, self.TABFILE, querymask.dirname, querymask.filename,
                   querymask.compare, str(filesize), querymask.filedate)

        self.cursor.execute(SELECTCOUNT + query)
        record = self.cursor.fetchone()
        if record:
            matchcount = record[0]
            self.updateMatches(matchcount)
            self.commitTransaction()
            if querymask.ignorecase and querymask.orderby != 'filesize':
                self.cursor.execute("%s %s ORDER BY upper(%s),upper(filename) LIMIT %s" %
                                   (SELECTALL, query, querymask.orderby, querymask.displimit))
            else:
                self.cursor.execute("%s %s ORDER BY %s,filename LIMIT %s" %
                                   (SELECTALL, query, querymask.orderby, querymask.displimit))

            return self.cursor.fetchall(), matchcount
        else:
            return None, 0            
    #-------------------------------------------------------------------------------------------

    def putRecord(self, record):
        if not self.transaction:
            self.cursor.execute('BEGIN TRANSACTION')
            self.transaction = True
        # find dir in tab
        if self.lastdir != record[0]:
            # new dir to tab
            self.lastdir = record[0]
            query = "INSERT INTO %s VALUES (NULL, '%s')" % (self.TABDIR, self.lastdir)
            self.cursor.execute(query)
            # get id of new dir
            query = "SELECT id_dir FROM %s WHERE dirname = '%s'" % (self.TABDIR, self.lastdir)
            self.cursor.execute(query)
            self.lastdirid = self.cursor.fetchone()[0]

        query = "INSERT INTO %s VALUES (NULL, '%s', '%s', '%s', '%s')" % (self.TABFILE, \
                                        self.lastdirid, record[1], record[2], record[3])
        try:
            self.cursor.execute(query)
            self.recordcount += 1
        except:
            pass
        
        if self.recordcount % self.updatemodulus == 0:
            self._updateRecords()
            if self.recordcount == 16384:
                self.updatemodulus = 1024
    #-------------------------------------------------------------------------------------------

    def putScanDate(self, rootdir = ''):
        if not self.transaction:
            self.cursor.execute('BEGIN TRANSACTION')
            self.transaction = True
        datetime = strftime('%Y-%m-%d %H:%M')
        query = "INSERT INTO %s VALUES (NULL, '%s', '%s')" % (self.TABSCAN, datetime, rootdir)
        self.cursor.execute(query)       
    #-------------------------------------------------------------------------------------------

    def commitTransaction(self):
        if self.transaction:
            self.cursor.execute('COMMIT TRANSACTION')
            self.transaction = False
    #-------------------------------------------------------------------------------------------

    def closeAll(self):
        self.commitTransaction()
        self.cursor.close()
        self.connection.close()
    #-------------------------------------------------------------------------------------------

    def deleteDb(self):
        self.closeAll()
        os.remove(self.filename)
        self.connectOrCreate()
    #-------------------------------------------------------------------------------------------


class ScanDir(object):

    def __init__(self, getAbortAction, rootdir = '', db = None, setStatusText = None, priority = False):
        self.getAbortAction = getAbortAction 
        self.rootdir = rootdir
        self.db = db
        if setStatusText == None:
            self.setStatusText = self.dummySetStatusText()
        else:
            self.setStatusText = setStatusText
        db.setPriority(priority)    # True ::= reduce priority
    #-------------------------------------------------------------------------------------------

    def dummySetStatusText(self, p1 = None, p2 = None):
        pass
    #-------------------------------------------------------------------------------------------

    def scanDirs(self):
        if self.rootdir:
            self.db.putScanDate(self.rootdir)
            try:
                for root, dirs, files in os.walk(self.rootdir, onerror = None):
                    if self.getAbortAction(): break
                    self.setStatusText(root, 3)
                    for name in files:
                        if self.getAbortAction(): break
                        path = join(root, name)
                        try:
                            record = (root, name, getsize(path),
                                      strftime('%Y-%m-%d %H:%M', localtime(getmtime(path))))
                            self.db.putRecord(record)
                        except:
                            pass
                raise
            except:
                self.db.commitTransaction()                    
    #-------------------------------------------------------------------------------------------


## test main
#if (__name__ == '__main__'):
#    stomadb = StoreManDb()
#
#    record = ('/home/mersmann', 'freenas.png', '12300', '09.07.2008 19:23:55')
#    for i in range(5):
#        stomadb.putRecord(record)
#    record = ('/home/mersmann', 'bild1.png', '12300', '09.07.2008 19:23:55')
#    for i in range(5):
#        stomadb.putRecord(record)
#    stomadb.closeAll()
#
#    querymask = QueryMask(filesize = '135%')
#    stomadb = StoreManDb()
#    entrycount = stomadb.getRecords(querymask)
#    print entrycount
#    while True:
#        record = stomadb.fetchOne()
#        if not record: break
#        #print record
#        print record[0], record[1], record[2], record[3], record[4]
#    stomadb.closeAll()
#
#    scan = RescanNAS('/home/mersmann', stomadb)
#    scan.scanDirs()
#    stomadb.closeAll()