#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3
import Crypto.Cipher.AES as AES
from os import urandom,remove


def encrypt(s,cypher):
	return cypher.encrypt(s)
def decrypt(s,cypher):
	try:
		print 'The type of the data to decrypt is ' + str(type(s))
		print 'decrypting {0}'.format(s) 
		return cypher.decrypt(s)
	except Exception as E:
		print 'error : ' + str(E)
		return 'error'

filename='test_sqlite.db'
try:
	remove(filename)
except:
	pass
	
# first of all the database is created
print "=== creating the database file " + filename
con = sqlite3.connect(filename)
con.text_factory=str
query = 'CREATE TABLE my_table(title TEXT)'
con.execute(query);
con.close()
print "********** DATABASE CREATED  ****\n\n"

# now adding an encrypted string, which is an str
S='hello everybody!'
psw='123abc456defaabb'
iv=urandom(16)
cypher = AES.new(psw,IV=iv)
eS = cypher.encrypt(S)
print "the type of the encrypted data is : " + str(type(eS))
con = sqlite3.connect('test.db')
con.text_factory=str
query = 'INSERT INTO my_table (title) VALUES (?)'
con.execute(query,(eS,));
con.commit()
con.close()
print "********** DATA WRITTEN TO DISK  ****\n\n"

# now retrieving the data directly
con = sqlite3.connect('test.db')
con.text_factory=str
query = 'SELECT * FROM my_table'
cur=con.execute(query);
L=cur.fetchone()
L=L[0]
print 'no decrypt: ' + str(L)
print "decrypted=" + decrypt(L,cypher)
con.close()
print "********** DATA READ OK  ****\n\n"


# now retrieving the data ut with a nested decrypt function 
con = sqlite3.connect('test.db')
con.text_factory=str
con.create_function("decrypt", 1, lambda s:decrypt(s,cypher))
query = 'SELECT decrypt(title) FROM my_table'
cur=con.execute(query);
L=cur.fetchone()
print 'decrypt: ' + str(L)
con.close()
print "********** DATA READ+DECRYPTION FAILED  ****"