prm2.py

prm2.py

# coding: utf-8

'''
	the rules are not so clear to me about making your own application id.
	will look into it more...
'''


import sqlite3

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 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
APP_ID = 1234

# only test purposes, can create tables from a list
# def table_create_from_list(self, tbl_name_list):
_tbl_list = ['RES', 'STR', 'TEXT', 'PICT', 'LIST', 'COLOURS', 'TUPLE', 'ROOM', 'IVAN', 'PYUI']



''' 
	sqlite row factory's
'''
def dict_factory_master(cursor, row):
	rec = dbdef.db_def['MASTER_REC'].copy()
	for idx, col in enumerate(cursor.description):
		rec[col[0]] = row[idx]
	return rec
'''
	end factory's
'''

class query (object):
	def __init__(self, sql, **params):
		pass
		
class __table (object):
	def init(self, prm, tb_name):
		pass


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)

	return header[:16] == 'SQLite format 3\x00'


class ErrorHandler(Exception):
	pass

class PersonalResourceManager (object):
	'''

	'''
	def __init__(self, db_def, db_file = None):
		
		self.db_def = db_def
		self.dbfile = db_file or db_def['db_name']

		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
		
		# the file does not exist...
		# try to create the file
		if not isfile(self.dbfile):
			self.__connect()
			self.pragma_set('application_id', APP_ID)
			self.db_close()
			
		if isSQLite3(self.dbfile):
			if not self.pragma_get('application_id') ==  APP_ID:
				raise ValueError('this is  a valid sqlite db file, but not created by this application')
				return
		
		self.__connect()
		

		self.success = True


	@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 ValueError as err:
			print err(args)
			return
			
		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, type = 'table'):
		'''
		return a list of tables from the sqlite_master table, only types that == 'name'
		'''
		conn = self.__connect()
		conn.row_factory = dict_factory_master
		return conn.execute(get_SQL('table_list'), (type,)).fetchall()
	
	def table_list_all(self):
		'''
		return a list of tables from the sqlite_master table, only types that == 'name'
		'''
		conn = self.__connect()
		conn.row_factory = dict_factory_master
		return conn.execute(get_SQL('table_list_all')).fetchall()
	

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

	def table_create_from_list(self, tbl_name_list):
		for tbl_name in tbl_name_list:
			self.table_create(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, tbl_name):

		return True if self.__connect().execute(get_SQL('table_exists').format(tbl_name)).fetchone() else False

	def table_info(self, tbl_name):
		try:
			return self.__connect().execute(get_SQL('pragma_table_info').format(tbl_name)).fetchone()
		except ValueError as err:
			print(err.args)

	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 db_shrink_database(self):
		'''
		The VACUUM command rebuilds the entire database...

		The VACUUM command may change the ROWIDs of entries in any tables that do not have an explicit INTEGER PRIMARY KEY.

		read more...
		https://sqlite.org/lang_vacuum.html
		'''
		self.__connect().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 (not indexes etc..)
		from sqlite_master table
		if we fail for whatever reason, we return 0
		'''
		try:
			return self.__connect().execute(get_SQL('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):
		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:
			conn.rollback()
			self.db_close()
			print(err.args)


	def record_delete_id(self, tbl_name,  rec_id):
		try:
			self.__connect().execute(get_SQL('record_delete').format(tbl_name), (rec_id,))
			self.db_commit(True)
		except ValueError as err:
			print(err.args)
			
	def view_create(self, view_name, sql):
		conn = self.__connect()
		
		try:
			conn.execute('CREATE VIEW IF NOT EXISTS {0} AS {1}'.format(view_name, sql))
		except ValueError as err:
			print err(args)
		
	def db_commit(self, commit = True):
		'''
		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

	def db_file_size(self):
		return getsize(self.dbfile) /1024

	def db_exec_sql(self, sql):
		try:
			self.__connect().execute(sql)
			self.db_commit()
		except ValueError as err:
			print err(args)
	
	def pragma_get(self, pragma_id):
		return self.__connect().execute(''' PRAGMA {0} '''.format(pragma_id)).fetchone()[0]
		
	def pragma_set(self, pragma_id, value):
		print ''' PRAGMA {0} = ({1})'''.format(pragma_id, (value,))
		self.__connect().execute(''' PRAGMA {0} = ? '''.format(pragma_id), (value,))
	
	# context manager methods...
	def __enter__(self):
		self.__connect()
		print 'Opened Database....'

	def db_get_conn(self):
		return self.__connect()
		
	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()
		print '__del__ being called'

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


	for i in range(0, recs):
		rec = prm.record_new(resid = randint(2000, 500000), key = fake.name(), data = fake.address())
		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()
	db_def['db_name'] = 'really.db'
	recs_to_add = 2

	print 'is sqlite3 Db', isSQLite3(db_file_name)
	prm = PersonalResourceManager(db_def, 'test.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.table_create_from_list(_tbl_list)

	print 'database file size is {0}kb'.format(prm.db_file_size())

	print 'the fuck', prm.table_list('view')
	print '\n' * 3
	
	for d in prm.table_list_all() :
		print d , '\n'
		
	print 'num tables =', len(prm.table_list_all() )
	tbl_name_test_exist = 'STR'
	print 'table {0} exists = {1}'.format(tbl_name_test_exist, prm.table_exists(tbl_name_test_exist))
	print 'The number of tables in the db = {0}'.format(prm.table_count())


	prm.record_delete_id(test_tbl_name, 6)
	#prm.db_progress_handler(db_progress)
	#prm.fix_database_lock()

	#print 'table info', prm.table_info(test_tbl_name)
	#prm.db_exec_sql('''create index 'shit' on '{0}' (resid) '''.format(test_tbl_name))


	conn = prm.db_get_conn()
	print 'application_id =' ,  conn.execute('PRAGMA integrity_check').fetchone()[0], '\n' * 3
	#conn.execute('CREATE VIEW TESTVIEW AS SELECT * FROM sqlite_master')
	#conn.commit()
	#print 'from view', conn.execute(''' SELECT * FROM TESTVIEW WHERE type = 'index' ''').fetchall()
	
	'''
	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()
	'''