prm2.py

prm2.py

# coding: utf-8


import dbdef
from dbdef import get_SQL

# only testing modes
from faker import Faker
fake = Faker()

from random import randint
# todo : remove, testing modules

import sqlite3
from collections import namedtuple

import os, shutil

# maybe this is not necessary
try:
	import cPickle
	print 'using cPickle'
except:
	import pickle
	print 'using pickle'

from os.path import isfile, getsize

# some field defs
_resid_unique = True



res_types = ['RES', 'STR', 'TEXT', 'PICT', 'LIST', 'COLOURS', 'TUPLE', 'ROOM', 'IVAN', 'PYUI']


def db_backup(db_file_name):

	result = False
	base = db_file_name.split('.')[0]
	new_file_name = '{0}_{1}.db'.format(base,'backup')
	try:
		shutil.copyfile(db_file_name, new_file_name)
		result = True
	except:
		raise IOError
	finally:
		print 'database backup = ', result
		return result





def isSQLite3(filename):
	'''
	try and determine if the filename is a valid sqlite3 database. if the filename does not exist, still returns False.

	copied this code from stackflow
	'''


	if not isfile(filename):
		return False
	if getsize(filename) < 100:
		# SQLite database file header is 100 bytes
		return False

	with open(filename, 'rb') as fd:
		header = fd.read(100)

	# i dont think this is future proof, have to think about this
	# todo
	return header[:16] == 'SQLite format 3\x00'


class ErrorHandler(Exception):
	pass

class PersonalResourceManager (object):
	'''

	'''
	def __init__(self, db_def, db_filename):
		self.db_def = db_def
		self.dbfile = db_filename

		self.success = False
		self.connection = None

		self.progress_handler = None
		self.progress_intervals = 0

		# always commit
		self.db_commit_flag = True
		self.db_outstanding_commits = 0

		# if we can determine we have a valid sqlite3 db
		# aviod opening it to create it, etc...
		if not isSQLite3(self.dbfile):
			conn = self.__dbconn()
			conn.close()

		self.success = True
		# fix this shit. Modify isSQLite3 to only determine if valid
		# sqllite3 db...
		self.connection = self.__dbconn()


	@property
	def auto_commit(self):
		return self.auto_commit_flag

	@auto_commit.setter
	def auto_commit(self, value):
		self.auto_commit_flag = value

	def __dbconn(self):
		'''
		The only method used to create a database connection.
		need to beef up the error checking/handling here
		'''
		self.success = False

		try:
			conn = sqlite3.connect(self.dbfile)
			conn.set_progress_handler(self.progress_handler, self.progress_intervals)

		except Exception as ex:
			print ex
			raise 'Connection to database Error'
		self.success = True

		return conn

	def __connect(self):
		'''
		always get a connnection object via this method. want to always use the context manager. this is not suppose to be a high speed database. is suppose to convient and safe to use.
		'''
		if not self.connection:
			self.connection = self.__dbconn()

		'''
		not sure setting the Row factory to None each time a connection is request is smart or not. For the moment i do.
		'''
		self.connection.row_factory = None
		return(self.connection)

	def db_progress_handler(self, callback, n = 10):
		self.progress_handler = callback
		self.progress_intervals = n

	def table_info(self, type):
		with self.__connect() as conn:
			return conn.execute(_table_info_sql.format(type)).fetchone()[0]


	def table_list(self):
		'''
		return a list of tables from the sqlite_master table, only types that == 'name'
		#555
		'''
		conn = self.__connect()
		return [ str(tbl[0]) for tbl in  conn.execute(_table_list_sql)]


	def table_create(self, tbl_name):
		self.__connect().execute(get_SQL('table_create').format(tbl_name))

	def table_drop(self, tbl_name):
		# remove a table
		conn = self.__connect().execute(get_SQL('table_drop').format(tbl_name))

	def table_exists(self, type):

		conn = self.__connect()
		return True if conn.execute(_table_exists_sql.format(type)).fetchone() else False


	def add_entry(self, type, resid , key, ord, value, value1, data, pickled = False):


		pickle_code = None

		sql = _insert_sql.format(type)
		if pickled:
			data = pickle.dumps(data)
			pickle_code =  _pickle_code

		# if resid == 0, select the max + 1 for the ID
		if resid == 0:
			resid = self.get_max(type, 'id') + 1

		if ord == 0:
			ord = self.get_max(type, 'ord') + 1

		conn = self.__connect()
		conn.execute(sql, (None,resid, key, ord, value, value1,data,pickle_code ))

		if self.auto_commit_flag:
			conn.commit()

	def add_resource(self, type, res, commit = False):

		pickle_code = None

		# if we dont provide a resid, get the max num
		# and add 1

		resid = res.resid if res.resid <> 0 else self.get_max(type, 'resid') + 1
		data = res.data if not res.pickled else pickle.dumps(res.data)

		ord = res.ord
		'''
		if ord == 0:
		ord = self.get_max(type, 'ord') + 1
		'''
		sql = _insert_sql.format(type)

		conn = self.__connect()
		conn.execute(sql, (None, resid, res.key, ord, res.value, res.value1,data, res.pickled ))

		if commit:
			conn.commit()

	def add_record(self, type, res):
		print _insert_sql.format(type), [v for v in res]
		try:
			db.execute(_insert_sql.format(type), [v for v in res])
		except:
			raise ValueError


	def get_entry_ID(self, type, resid):
		conn = self.__connect()
		conn.row_factory = namedtuple_factory
		return conn.execute(_select_sql.format(type), (resid,)).fetchone()

	def get_max(self, type, fld):
		try:
			print 'fuck', table_select_max_sql.format(fld, type)
			return self.__connect().execute(table_select_max_sql.format(fld, type)).fetchone()[0] or 0
		except Exception as e:
			return 0
			#print repr(e)


	def get_table_entries(self, type):
		conn = self.__connect()
		conn.row_factory = namedtuple_factory
		sql = ''' Select * from {0} order by resid'''.format(type)
		for row in conn.execute(sql):
			yield row

	def get_table_entries_list(self, type):
		conn = self.__connect()
		conn.row_factory = namedtuple_factory
		sql = ''' Select data, pickled from {0} order by resid'''.format(type)
		cur = conn.execute(sql)
		return [d for d in cur ]

	def shrink_database(self):
		conn = self.__connect()
		conn.execute('VACUUM')

	def db_backup(self):
		had_connection = False

		# if the database is open, commit and close
		# before trying to backup
		if self.connection:
			self.close()

		result = db_backup(self.dbfile)

		# if we had a connection before the backup started
		# reopen it.
		if had_connection:
			self.__connect()


	def fix_database_lock(self):
		conn = self.__connect()
		conn.commit()
		conn.close()

	def table_count(self):
		'''
		return the number of user tables from sqlite_master table
		if we fail for whatever reason, we return 0
		'''
		try:
			return self.__connect().execute(_table_count).fetchone()[0]
		except:
			return 0

	def table_rec_count(self, type):
		'''
		return the number of records for the given table. if there is a problem, eg. the table does not exist we return 0
		'''
		try:
			return self.__connect().execute(get_SQL('table_rec_count').format(type)).fetchone()[0]

		except:
			return 0

	def record_new(self, **kwargs):

		'''
		*** not sure if i can do this better or not ***

		create a empty record with all fields set to None
		returns a ordereddict of flds as defined in db_def
		All the flds are set to None. kwargs, populate the
		the flds
		'''

		rec = self.db_def['REC'].copy()
		for k,v in kwargs.iteritems():
			if rec.has_key(k):
				rec[k] = v

		return rec

	def record_add(self, tbl_name, record):
		print 'in record add', record
		print 'record add', get_SQL('table_insert').format(tbl_name),record.values()
		try:
			conn = self.__connect()
			conn.execute(get_SQL('table_insert').format(tbl_name),
			record.values())
			self.db_commit(self.db_commit_flag)
		except ValueError as err:
			print(err.args)



	def db_commit(self, commit):
		'''
		maybe stupid, but keeping a count of un commited operations.
		i am not sure how the sqlite journal file works yet. but if it does not provide adequate protection, it might be smarter to write the sql statement to a plain text file for recover...
		Then again, not writing an Enterprise Class system here. i suspect, the sqlite journal is pretty good. just have to read up on it.
		'''
		if self.connection:
			if commit:
				self.connection.commit()
				self.db_outstanding_commits = 0
			else:
				self.outstanding_commits += 1

	def db_close(self):
		'''
		if there is a connection , do a commit, then close the connection
		'''
		if self.connection:
			self.db_commit(True)
			self.connection = None

	# context manager methods...
	def __enter__(self):
		self.__connect()
		print 'Opened Database....'


	def __exit__(self, exc_type, exc_value, traceback ):
		self.connection.commit()
		self.connection.close()
		self.connection = None
		print 'Database Closing...'
		
	def __del__(self):
		self.db_close()

def create_random_str_recs(prm, tbl_name, recs):
	print 'starting to add {0} records'.format(recs)
	xresid = randint(1, 50000)

	for i in range(1, recs):
		rec = prm.record_new(resid = 1000, key = fake.name())
		print 'new record', rec
		prm.record_add(tbl_name, rec)

	print '{0} records added'.format(recs)
	print 'commiting {0} records'.format(recs)
	prm.db_commit(True)
	print '{0} records committed'.format(recs)

def db_progress(self):
	print 'in database progress'

import timeit

if __name__ == '__main__':
	
	test_tbl_name = '666'
	db_file_name = 'junk.db'
	db_def = dbdef.db_def.copy()

	recs_to_add = 1

	print 'is sqlite3 Db', isSQLite3(db_file_name)
	prm = PersonalResourceManager(db_def, 'junk.db')
	prm.table_create(test_tbl_name)
	prm.table_drop('JAN')
	create_random_str_recs(prm, test_tbl_name,  recs_to_add)
	print '{0} has {1} records'.format(test_tbl_name, prm.table_rec_count(test_tbl_name))
	
	#prm.db_progress_handler(db_progress)
	#prm.fix_database_lock()
	'''
	print 'db success = ' , prm.success
	prm.add_table(res_types)


	#timeit.timeit('create_random_str_recs(prm, 100)')
	create_random_str_recs(prm, recs_to_add)


	#print prm.get_last_ID('STR')
	'''
	'''
	for x in range(1,5):
	print 'adding res'
	res = new_resource('STR', 0, 'LANG', fake.name())
	prm.add_resource('STR', res, True)
	res = key_pair_resource('LIST', 101, fake.address())
	prm.add_resource('LIST', res)
	'''
	'''
	id = prm.get_max('STR', 'id')

	prm.add_table(['IVAN', 'JOHNNY','JAN', 'Bjarne'])

	#prm.drop_table('IVAN')

	mytbl = 'LIST'
	print 'table exists:', mytbl,  '=', prm.table_exists(mytbl)
	print 'table info', prm.table_info('TEXT')

	print 'get max', prm.get_max('STR', 'ord')

	'''
	'''
	print 'shrinking Database'
	prm.shrink_database()
	print 'Finished m shrinking Database...'
	'''


	#db_backup(db_file_name)
	#prm.drop_table('ROOM')
	#prm.add_table('5#>#')
	'''
	with prm:
	for row in prm.get_table_entries('LIST'):
	print row

	with prm:
	print prm.get_table_entries_list('TUPLE')

	x = Resource(0, 'shit', 1,2,(1,2,3), 1)
	print 'named tuple', x
	print x._fields

	print 'table definition \n'
	for i, item in enumerate(tb_def):
	print tb_def._fields[i], '=',  item
	'''
	'''
	print 'number of tables', prm.table_count()
	print 'number recs', prm.record_count('IVAN')

	prm.add_record('TEXT', MY_REC(resid = randint(1,5000), value1 = 'shit'))

	print 'table list', prm.table_list()
	'''