pragmas.py

pragmas.py

# coding: utf-8


# *** DISCLAIMER PLEASE READ ***
# please understand, i am a newbie. being a newbie,
# my code may be crap or worse buggy
# i still like to share. it could be useful code.
# or parts of could be useful
# but i like the feedback

# 
'''
	class SqlLitePragmas attempts to do -
	1. given a valid Sqlite database filespec
		a. return a OrderedDict of all the PRAGMA settings for the database, with the following execptions
		
		The PRAGM's listed below, only return a message. The message is being generated by me, not Sqlite. I have tried to get i right. But please dont use blindly. As i say above, i am a newbie. i still try to be correct.
		
			a. If query on the PRAGMA, will result in code being executed on the database
		
			b. If the PRAGMA requires a parameter to execute. This is suppose to be a generic query and easy to use. 
		
			c. If the PRAGMA is deprecated. i have included all deprecated PRAGM's in the definitions for completeness. Better to have them there. if you had a old style database that use these  deprecated PRAGMA's you could modify _pragmas for the deprecated PRAGM's you support to be a 0 instead of a 3.
			
			d. The wal_checkpoint, can execute code on the database depending on some journal settings. This PRAGMA is not queried
	
	2.	given a valid Sqlite database filespec
			a. print the instantiated object x = SqlLitePragmas(valud_sqlite3_d ). __str__ is overriden to privide the functionality. if you need another layout, its easy to change
			
	3. given an instantiated class of SqlLitePragmas(), 
			a.you can print a list of all the PRAGMA names as links to the console. console.write_link() is used. The links are links into each PRAGMA directive at https://www.sqlite.org
'''


'''
	KNOWN BUGS
	1. print_pragma_help_links - method
		returns 1 unexpected result at the end of the list. None. I really have no idea why this is happening, is so strange. maybe a side effect of overriding __str__. i will track i down.	
'''

'''
	TO DO
	1. 
		Add a set PRAGMA method. I have the code to do it now. its not difficult. but more testing required. i got some strange results i didnt expect. not many , but reading is one thing. writing is a lot more dangerous. So more time required...
	2.
		Add code to confirm the provided filespec is a sqlite database file. Also honour application_id setting. I have this code, but i need to make sure i get the TODO 1. set PRAGMA completley nailed before adding it. 
'''

'''
	Notes
	1. I really question my use of the iterxxx calls on the dict. in this case, i assume its just crazy overhead and time wasting. large datasets, yes. but a single dict, its probably really stupid. i am sure just better to use like dict.keys() and dict.items() etc... rather than the iter versions. No need for generators here. 
	intrested if anone has ideas on this... 
	
	*** UPDATE ***
	I replaced all the iter calls 
'''
from collections import OrderedDict
import sqlite3
import console
import sys

_web_help_link =  '''https://www.sqlite.org/pragma.html#pragma_{0}'''

'''
	_pragmas is list of PRAGMS I copied from sqlite.org
	for my definitions:
	
	
	The attr values I assign to each PRAGMA:
		
	0 = Information Prama (Possiblly can be set also)
	1 = If queried, this PRAGMA will execute code.
		  This PRAGMA is not queried, so not to execute code
		  eg. 'integrity_check'
	2 = PRAGMA requires a specfic parameter, this PRAGMA is ignored
		  as this is just a generic information reporting tool
		  
	3 = PRAGMA is deprecated, included for completness only
	
	Some PRAGMA's will come back with a message 'PRAGMA Not Supported'. this is not a message from sqlite, its a msg i generate. Some PRAGMA's are only valid under certian conditions. When in debug mode for example. That msg is returned if the PRAGMA is queried and results in an error. ( i am assume that this is the right thing to do)
		  
'''
_pragmas = OrderedDict((
        ('application_id', 0),
        ('auto_vacuum',0),
        ('automatic_index', 0),
        ('busy_timeout', 0),
        ('cache_size', 0),
        ('cache_spill', 0),
        ('case_sensitive_like', 2),
        ('cell_size_check', 0),
        ('checkpoint_fullfsync', 0),
        ('collation_list' , 0),
        ('compile_options' , 0),
        ('count_changes', 3),
        ('data_version', 0),
        ('database_list', 0),
        ('data_store_directory', 3),
        ('default_cache_size', 3),
        ('defer_foreign_keys', 0),
        ('empty_result_callbacks', 3),
        ('encoding', 0 ),
        ('foreign_key_check', 1),
        ('foreign_key_list', 2),
        ('foreign_keys', 0),
        ('freelist_count', 0),
        ('full_column_names', 3),
        ('fullfsync', 0),
        ('ignore_check_constraints', 0),
        ('incremental_vacuum', 2),
        ('index_info', 2),
        ('index_list', 2),
        ('index_xinfo', 2),
        ('integrity_check', 1),
        ('journal_mode', 0),
        ('journal_size_limit', 0),
        ('legacy_file_format', 0),
        ('locking_mode', 0),
        ('max_page_count', 0),
        ('mmap_size', 0),
        ('page_count', 0),
        ('parser_trace', 2),
        ('query_only', 0),
        ('quick_check', 1),
        ('read_uncommitted', 0),
        ('recursive_triggers', 0),
        ('reverse_unordered_selects', 0),
        ('schema_version', 0),
        ('secure_delete', 0),
        ('short_column_names', 3),
        ('shrink_memory', 1),
        ('soft_heap_limit', 0),
        ('stats', 0),
        ('synchronous', 0),
        ('table_info', 2),
        ('temp_store', 0),
        ('temp_store_directory', 3),
        ('threads', 0),
        ('user_version', 0),
        ('vdbe_addoptrace', 0),
        ('vdbe_debug', 0),
        ('vdbe_listing' , 0),
        ('vdbe_trace', 0),
        ('wal_autocheckpoint', 0),
        ('wal_checkpoint', 4),
        ('writable_schema', 0),
        ))

pragma_messages = [
			'''
					just a list of messages. the are in ordinal seq to the 
					attrs used in the _pragmas above
			'''
			'NO_OP',
			'Will execute code',
			'Parameter required',
			'Deprecated',
			'Could execute code',
			'PRAGMA Not Supported',
]




_web_help_link_adjustments = {
		''' 
		there is one PRAGMA, that does not conform the normal rules for the web help link on sqlite.org :( so i do,a subsutition at runtime. its expensive to do it, but realitive speaking its not. if we were creating 100,000's of records, i would do it differently.
 
		Normal convention is:
		https://www.sqlite.org/pragma.html#pragma_ + the PRAGMA name 
		The PRAGMA user_version breaks the convention. I implemented like this in the event that some future PRAGMA's break. i know highly unlikely. but what to do.
		'''
		
		'user_version' :'schema_version',
		
}

class SqlLitePragmas (object):
	'''
		Args:
			dbfile, a valid sqlite3 database file... the database is not tested for existence of created when the object is instantiated. also the the file is not checked to confirm its a valid sqlite3 database file. there are notes in the todo about this.
		
		Purpose:
			To query all the PRAGMA's in sqlite3 against the supplied database name.
			No queries are executed with PRAGMS that will execute code, or require parameters or that are deprecated. Instead these PRAGM's return a text msg in their attr.
	'''
	
	def __init__(self, dbfile):
		self.dbfile = dbfile
		
		
	def get_pragmas(self):
		'''
			returns a tuple (dict, sqlite3.Error)
			if there is no error, dict is valid and err is None. 
			if there is an error, dict is None abd error is a sqlite3.Error object
		'''
		try:
			conn = sqlite3.connect(self.dbfile)
		except sqlite3.Error as err:
			# i need to understand error handling better than this
			return (None, err)
		
		# we have a valid connection to a database	
		with conn:
			
			rec = _pragmas.copy()
			
			for k, v in rec.items():
				
				if v:
					# if non zero, means we dont want to query
					# this PRAGMA. we insert a message into the 
					# field insead. its explained above...
					rec[k] = pragma_messages[v]
				else:
					try:
						rec[k] = conn.execute('''PRAGMA {0}'''.format(k)).fetchone()[0]
					except :
						# assume if the try: fails, then the PRAGMA
						# IS NOT SUPPORTED!! i hope i am correct
						
						# last msg in the list :(
						rec[k] = pragma_messages[len(pragma_messages) - 1]
						
		return (rec, None)
	
	def __str__(self):
		# string representation of the PRAGMA's, 
		# good for console use...
		# returns PRAGMA : Value \n ...
		d =  self.get_pragmas()[0]

		return 'PRAGMA LISTING FOR DATABASE - {0}\n'.format(self.dbfile) + '\n'.join('{0} : {1}'.format(k,v) for k,v in d.items())
		
	def print_pragma_help_links(self):
		rec = _pragmas.copy()
		for k , v in rec.items():
			link = k
			if k in _web_help_link_adjustments.keys():
				link = _web_help_link_adjustments[k]
				
			console.write_link(k, _web_help_link.format(link))
			if v:
				print '  -({0})'.format(pragma_messages[v])
			print '\n'

if __name__ == '__main__':
	sqllite_pragmas = SqlLitePragmas('pragma_test_db.db')
	
	# returns a dict with PRAGMA infomation,
	# or a sqlite3.Error object
	obj = sqllite_pragmas.get_pragmas() 
	

	if obj[1]: # then we have a error object
		print obj.message()
		print 'program can not continue...'
		sys.exit()
	
	print '{pad} Orderdict PRAGMA settings {pad}'.format(pad = '#' * 5)
	print obj[0]
	print '\n' * 2
	
	print	 '{pad} __str__ Version {pad}'.format(pad = '#' * 5)	
	print sqllite_pragmas
	print '\n' * 2
	
	print	 '{pad} PRAGMA HELP LINKS {pad}'.format(pad = '#' * 5)	
	print '\n'
	print sqllite_pragmas.print_pragma_help_links()