seek files

#!/bin/pyton

import os
import sys
import string
import re
import time
import stat
import zlib
import zipfile
import sqlite3

def walk( base, parentId=0 ):
    global cur

    mainId = 0
    if base != '.':
        dt = getDadosArquivo( base, parentId )
        if dt == None:
            mainId = makeArquivoReg( base, parentId, os.path.realpath(base) )
        else:
            mainId = dt[0]  #id do arquivo

    for f in os.listdir( base ):
        pathname = os.path.join(base, f)
        if not match(pathname):
            isDir = os.path.isdir( pathname )
            #print "%s - %s" % (base, pathname)
            if isDir:
                #dt = getDadosArquivo( pathname, mainId )
                # se nao existe cria
                #if dt == None:
                #    makeArquivoReg
                walk( pathname, mainId )
            else:
                #zip.write( pathname )
                dt = getDadosArquivo( pathname, mainId )
                if dt == None:
                    makeArquivoReg( pathname, mainId, os.path.realpath(pathname) )
                else:
                    #time_format = "%m/%d/%Y %I:%M:%S %p"
                    file_stats = os.stat(pathname)
                    modification_time = file_stats[stat.ST_MTIME] #time.strftime(time_format,time.localtime(file_stats[stat.ST_MTIME]))
                    creation_time = file_stats[stat.ST_CTIME] #time.strftime(time_format,time.localtime(file_stats[stat.ST_CTIME]))
                    if ("%s" % modification_time) != ("%s" %dt[2]):
                        #print "%s %s %s" % (pathname, file_stats[stat.ST_MTIME], dt[2] )
                        makeArquivoReg( '%s' % pathname, mainId, os.path.realpath(pathname), dt[0] )


def checkDeletes( ):
    global cur
    global commitId

    cur.execute("SELECT indexpath,idarquivos FROM arquivos WHERE isAtive=1")
    for row in cur.fetchall():
        if not os.path.exists( "./%s" % row[0] ):
            deleteTrunk( row[1] )

def deleteTrunk( idArquivo ):
    global commitId

    #hide
    cur.execute("UPDATE arquivos SET isAtive=0 WHERE idarquivos=?", (idArquivo,) )
    # history
    cur.execute("""INSERT INTO mt_arquivos_history
                (arquivo_idarquivos, operacao, commits_idcommits)
                VALUES (?, 2,?)
                """, (idArquivo, commitId) )
    # filhos?
    cur.execute("SELECT isFolder FROM arquivos WHERE idarquivos=?", (idArquivo,) )
    df = cur.fetchone()
    if ("%s" % df[0] ) == "1":
        cur.execute("SELECT idarquivos FROM arquivos WHERE parent=? AND isAtive=1", (idArquivo,) )
        for row in cur.fetchall():
            deleteTrunk( row[0] )

def getDadosArquivo( path, pai ):
    global cur
    #print "SELECT idarquivos,data_insercao,data_atualizacao FROM arquivos WHERE parent=%s AND nome='%s'" % (pai, path[2:len(path)])
    cur.execute("SELECT idarquivos,data_insercao,data_atualizacao FROM arquivos WHERE parent=? AND indexpath=? AND isAtive=1", (pai, path[2:len(path)],) )
    dt = cur.fetchone()
    return dt

def makeArquivoReg( base, parentId, path, toUpdate = -1 ):
    global con
    global cur
    global commitId

    fpts = base.split( os.sep )
    fNome = fpts[ len(fpts)-1 ]
    basepath = ""
    for  xp in fpts:
        if xp != ".":
            basepath += "%s%s" % (xp, os.sep )
    basepath = basepath[0:len(basepath)-1]

    paiStr = "'%s'" % path
    #base = base[2:len(base)]

    file_stats = os.stat(path)
    modification_time = '%s' % file_stats[stat.ST_MTIME]
    creation_time = '%s' %  file_stats[stat.ST_CTIME]
    isFolder = 0
    if os.path.isdir(path):
        isFolder = 1

    binary = ""
    print "Obtendo conteudo de: %s" % path
    #print "Comprimir %s" % compress(fNome)
    f = open(path, "r")
    binary = f.read()
    f.close()
    if compress(fNome):
        tBin = zlib.compress(binary,9)
        binary = tBin
    #binary = ''

    retId = toUpdate
    workId = 0
    operationStr = 'INSERT'

    if toUpdate == -1:
        print "Add: %s" % path
        cur.execute("""INSERT INTO arquivos
        (nome,realpath,parent,data_atualizacao,indexpath,
        dono,isbinary,
        isfolder,
        isagregate,
        conteudo)
        VALUES
        ( ?,?,?,?,?,0,0,?,0,? )""", (fNome, paiStr, parentId, modification_time, basepath, isFolder, sqlite3.Binary(binary),)) #buffer(binary)), ) )
        retId = cur.lastrowid
        workId = retId
        operationStr = 'INSERT'
    else:
        print "Atualizando: %s" % path
        #cur.execute("""UPDATE arquivos
        #SET  data_atualizacao=?,conteudo=?
        #WHERE idarquivos=?;""", ( modification_time,sqlite3.Binary(binary),toUpdate,))
        cur.execute("SELECT idarquivos,revisao FROM arquivos WHERE indexpath=? AND isAtive=1", (basepath,))
        dtt = cur.fetchone()
        print dtt is None
        parentVersionId = dtt[0]
        revisao = int(dtt[1]) + 1
        cur.execute("""INSERT INTO arquivos
        (nome,realpath,parent,data_atualizacao,indexpath,parent_version_id,revisao,
        dono,isbinary,
        isfolder,
        isagregate,
        conteudo)
        VALUES
        ( ?,?,?,?,?,?,?,0,0,?,0,? )""", (fNome, paiStr, parentId, modification_time, basepath, parentVersionId, revisao, isFolder, sqlite3.Binary(binary),)) #buffer(binary)), ) )
        cur.execute("UPDATE arquivos SET isAtive=0 WHERE idarquivos=?",(parentVersionId,))
        workId = toUpdate
        operationStr = 'UPDATE'

    # history
    cur.execute("""INSERT INTO mt_arquivos_history
                (arquivo_idarquivos, operacao, commits_idcommits)
                VALUES (?, (SELECT indice FROM mt_execucao_types WHERE descricao=?),?)
                """, (workId, operationStr, commitId) )

    #con.commit()
    return retId

def match( str ):
    global matches
    for rg in matches:
        if rg.search(str) != None:
            return True
    return False

def compress( str ):
    global noCompress
    for rg in noCompress:
        if rg.search(str) != None:
            return False
    return True

def getId( path ):
    global cur
    cur.execute("SELECT idarquivos FROM arquivos WHERE isAtive=1 AND indexpath=?", (path,))
    dt = cur.fetchone()
    return dt[0]

#matches
matches = []
with open('%s%s%s' %( os.curdir, os.sep, 'ignore.txt')) as f:
    for line in f:
        matches.append( re.compile( line[0:len(line)-1] ) )
#nao comprimir
noCompress = []
with open('%s%s%s' %( os.curdir, os.sep, 'nocompress.txt')) as f:
    for line in f:
        noCompress.append( re.compile( line[0:len(line)-1] ) )

def ldz(str):
    if (len('%s' % str)<2):
        str = '0%s' % str
    return str

#zip = zipfile.ZipFile('strut.zip','w')
#walk( os.curdir )
#zip.close()

con = sqlite3.connect('store.db')
con.isolation_level = None
cur = con.cursor()

# commit
t = time.localtime()
commitStr = "<update>"
if len(sys.argv)>1:
    commitStr = sys.argv[1]
commitSQL = "[%s:%s:%s %s-%s-%s]) %s" % (ldz(t.tm_hour),ldz(t.tm_min),ldz(t.tm_sec), ldz(t.tm_year),ldz(t.tm_mon),ldz(t.tm_mday), commitStr)
cur.execute("INSERT INTO commits (descricao,data_execucao) VALUES(?,?)", (commitSQL, int(time.mktime(t)),) )
con.commit()
commitId = cur.lastrowid

walk( os.curdir )
checkDeletes()

#dados =  sqlite3.Binary( "mandioca frita e lazanha amarela" )
f = open("7.png","rb")
dados = f.read()
f.close()

print len(dados)

compressed = zlib.compress(dados,9)

print len(compressed)

f = open("compressed.png","wb")
f.write(zlib.decompress(compressed))
f.flush()
f.close()
b = sqlite3.Binary(compressed)
print b
cur.execute("INSERT INTO tmp (dados) VALUES (?);", (b,) )


#pf = ".\\fdf\\fdd\\ffd"
#print os.sep
#print pf.split( os.sep )
con.commit()
con.close()


database= """
DROP TABLE IF EXISTS "mt_execucao_types";
DROP TRIGGER IF EXISTS "commits_insert";
DROP TABLE IF EXISTS "commits";
DROP TRIGGER IF EXISTS "arquivos_insert";
DROP INDEX IF EXISTS "arquivos_indexpath";
DROP INDEX IF EXISTS "arquivos_parent";
DROP INDEX IF EXISTS "arquivos_done";
DROP TABLE IF EXISTS "arquivos";
DROP TRIGGER IF EXISTS "mt_arquivos_history";
DROP INDEX IF EXISTS "mt_arquivos_history_idcommit";
DROP TABLE IF EXISTS "mt_arquivos_history";
DROP VIEW IF EXISTS "arquivos_from_commit";


CREATE TABLE IF NOT EXISTS "mt_execucao_types" (
indice INTEGER,
descricao VARCHAR(32)
);
INSERT INTO mt_execucao_types VALUES( 0, "INSERT" );
INSERT INTO mt_execucao_types VALUES( 1, "UPDATE" );
INSERT INTO mt_execucao_types VALUES( 2, "DELETE" );
INSERT INTO mt_execucao_types VALUES( 3, "MOVE" );
INSERT INTO mt_execucao_types VALUES( 4, "RESTORE" );
INSERT INTO mt_execucao_types VALUES( 5, "DISPOSE FROM DB" );
INSERT INTO mt_execucao_types VALUES( 6, "COMPRESS DB" );
INSERT INTO mt_execucao_types VALUES( 7, "CLEANUP DB" );

CREATE TABLE "commits" (
idcommits INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
data_execucao VARCHAR(12) DEFAULT "0" NOT NULL,
descricao TEXT
);

CREATE TRIGGER IF NOT EXISTS commits_insert
AFTER INSERT
ON commits
BEGIN
UPDATE commits SET `data_execucao`=strftime('%s','NOW') WHERE `idcommits`=new.rowid;
END

CREATE TABLE IF NOT EXISTS "arquivos" (
idarquivos INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
nome VARCHAR(255) DEFAULT "erro" NOT NULL,
indexpath VARCHAR(255),
realpath VARCHAR(255),
parent INTEGER,
revisao INTEGER DEFAULT 1 NOT NULL,
parent_version_id INTEGER,
dono INTEGER DEFAULT 0 NOT NULL,
data_insercao VARCHAR(12) DEFAULT "0" NOT NULL,
data_atualizacao VARCHAR(12) DEFAULT "0" NOT NULL,
isBinary INTEGER DEFAULT 0 NOT NULL,
isFolder INTEGER NOT NULL DEFAULT 0,
isAgregate INTEGER NOT NULL DEFAULT 0,
isLink INTEGER NOT NULL DEFAULT 0,
isAtive INTEGER NOT NULL DEFAULT 1,
md5checksum VARCHAR(32),
conteudo BLOB);

CREATE INDEX arquivos_indexpath ON arquivos (indexpath);
CREATE INDEX arquivos_parent ON arquivos (parent);
CREATE INDEX arquivos_done ON arquivos (dono);

CREATE TRIGGER IF NOT EXISTS arquivos_insert
AFTER INSERT
ON arquivos
BEGIN
UPDATE arquivos SET `data_insercao`=strftime('%s','NOW') WHERE `idarquivos`=new.rowid;
END



CREATE TABLE IF NOT EXISTS "mt_arquivos_history" (
idmt_arquivos_history INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
arquivo_idarquivos INTEGER DEFAULT 0 NOT NULL,
operacao INTEGER DEFAULT 1 NOT NULL,
data_execucao VARCHAR(12) DEFAULT "0" NOT NULL,
commits_idcommits INTEGER
);

CREATE INDEX mt_arquivos_history_idcommit ON mt_arquivos_history (commits_idcommits);

CREATE TRIGGER IF NOT EXISTS mt_arquivos_history_insert
AFTER INSERT
ON mt_arquivos_history
BEGIN
UPDATE mt_arquivos_history SET `data_execucao`=strftime('%s','NOW') WHERE `idmt_arquivos_history`=new.rowid;
END

CREATE VIEW IF NOT EXISTS arquivos_from_commit AS
SELECT arquivos.nome as nome, mt_arquivos_history.commits_idcommits as idcommits FROM arquivos INNER JOIN mt_arquivos_history ON  mt_arquivos_history.arquivo_idarquivos=arquivos.idarquivos;

###############################3

INSERT INTO mt_arquivos_history
(arquivo_idarquivos, executado, data_execucao,commits_idcommits)
VALUES (1, (SELECT indice FROM mt_execucao_types WHERE descricao='INSERT'),strftime('%s','NOW'),1)

INSERT INTO mt_arquivos_history
(arquivo_idarquivos, executado, commits_idcommits)
VALUES (1, (SELECT indice FROM mt_execucao_types WHERE descricao='INSERT'),1)


SELECT idcommits FROM mt_commits ORDER BY idcommits DESC LIMIT 1;

SELECT arquivo_idarquivos FROM mt_arquivos_history WHERE commits_idcommits=4;

SELECT nome FROM arquivos INNER JOIN mt_arquivos_history ON  mt_arquivos_history.arquivo_idarquivos=arquivos.idarquivos  WHERE commits_idcommits=4;
"""