def __verify_foreign_key_indexes(self): self.__notice('Verifying foreign key indexes...') for data in self.__unindexed_foreign_keys: table_name = data[0] parent_table_name = data[1] cols = data[2] parent_cols = data[3] parts = table_name.split('_') try: tinyAPI.dsh().create( 'rdbms_builder.dirty_module', {'name': parts[0]}) tinyAPI.dsh().commit() except DataStoreDuplicateKeyException: pass self.__notice('(!) unindexed foreign key', 1) self.__notice('table: ' + table_name + ' -> parent: ' + parent_table_name, 2) self.__notice(repr(cols) + ' -> ' + repr(parent_cols), 2) self.__notice('--------------------------------------------------' + '------------', 2) if len(self.__unindexed_foreign_keys) > 0: raise RDBMSBuilderException('unindexed foreign keys (see above)')
def __drop_foreign_key_constraints(self): self.__notice('Dropping relevant foreign key constraints...') if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() constraints = tinyAPI.dsh().query( '''select constraint_schema, table_name, constraint_name from referential_constraints where constraint_schema in (''' + self.__managed_schemas + ')') for constraint in constraints: parts = constraint['constraint_name'].split('_') if parts[0] in self.__modules_to_build_prefix: self.__notice('(-) ' + constraint['constraint_name'], 1) tinyAPI.dsh().query( 'alter table ' + constraint['constraint_schema'] + '.' + constraint['table_name'] + ' drop foreign key ' + constraint['constraint_name'])
def test_getting_nth_record_from_table(self): if self.__execute_tests is True: for i in range(0, 5): tinyAPI.dsh().create('unit_test_table', {'value': i}, True) results = tinyAPI.dsh().nth(3, 'select value from unit_test_table') self.assertEqual(3, results['value'])
def test_ping(self): self.assertEqual( 1, tinyAPI.dsh().count("""select 1 from dual""")) tinyAPI.dsh().ping() self.assertEqual( 2, tinyAPI.dsh().count("""select 2 from dual"""))
def test_adding_records_to_table(self): if self.__execute_tests is True: for i in range(0, 5): tinyAPI.dsh().create('unit_test_table', {'value': i}, True) for i in range(0, 5): self.assertEqual( 1, tinyAPI.dsh().count( '''select count(*) from unit_test_table where value = %s''', [i]))
def test_last_row_id_with_insert(self): tinyAPI.dsh().query( '''insert into unit_test_table( value) values( %s)''', [99881122]) self.assertIsNotNone(tinyAPI.dsh().get_last_row_id()) record = tinyAPI.dsh().one( """select value from unit_test_table where id = %s""", [tinyAPI.dsh().get_last_row_id()]) self.assertEqual(99881122, record['value'])
def main(cli): '''Executes an example of a RDBMS duplicate key exception.''' cli.header('Duplicate Key - RDBMS') tinyAPI.dsh.select_db('local', 'tinyAPI') cli.notice('Creating table called "a_table"...') tinyAPI.dsh().query( ('create table if not exists a_table(' + 'id integer not null auto_increment primary key,' + 'value integer not null)') ) cli.notice('Removing any records in a_table...') tinyAPI.dsh().query('delete from a_table') cli.notice('Added record to a_table...') tinyAPI.dsh().create( 'a_table', {'id': 1, 'value': 1}) cli.notice('The next record is a duplicate...') tinyAPI.dsh().create( 'a_table', {'id': 1, 'value': 1})
def test_auto_reconnect(self): connection_id = tinyAPI.dsh().connection_id() self.assertIsNotNone(connection_id) dsh_1 = \ provider.autonomous_tx_start( ConfigManager().value('default unit test connection'), 'tinyAPI' ) dsh_1.query('kill {}'.format(connection_id)) provider.autonomous_tx_stop_commit(dsh_1) records = tinyAPI.dsh().query('select 1 from dual') self.assertEqual(1, len(records))
def test_create_with_binary(self): id = tinyAPI.dsh().create('unit_test_table', { 'value': 123, '_binary message': 'abc def ghi' }) self.assertIsNotNone(id) record = tinyAPI.dsh().one( """select value, message from unit_test_table where id = %s""", [id]) self.assertIsNotNone(record) self.assertEqual(123, record['value']) self.assertEqual('abc def ghi', record['message'].decode())
def test_native_conversion_of_time_columns(self): tinyAPI.dsh().query( '''insert into unit_test_table( value, ti) values( %s, %s)''', [12345, '08:30:00']) record = tinyAPI.dsh().one( """select ti from unit_test_table where value = %s""", [12345]) self.assertIsNotNone(record) self.assertIsInstance(record['ti'], datetime.time) self.assertEqual('08:30:00', str(record['ti']))
def setUp(self): self.__execute_tests = False if ConfigManager().value('data store') == 'mysql': self.__execute_tests = True tinyAPI.dsh.select_db( ConfigManager().value('default unit test connection'), 'tinyAPI') tinyAPI.dsh().query('''create table if not exists unit_test_table ( id integer not null auto_increment primary key, value integer not null, ti time null, message blob null )''')
def __set_table_names(self): if self.__inactive_table_name is not None and \ self.__active_table_name is not None: return record = tinyAPI.dsh().one( """select view_definition from information_schema.views where table_name = %s""", [self.view_name]) if not record: raise RuntimeError( 'could not retrieve view definition for "' + self.view_name + '"') matches = \ re.match( 'select `(.*?)`.`(.*?)`\.', record['view_definition']) if not matches: raise RuntimeError( 'cannot determine active table from view defintion for "' + select.view_name + '"') self.__schema_name = matches.group(1) if matches.group(2) == self.view_name + '_1': self.__active_table_name = self.view_name + '_1' self.__inactive_table_name = self.view_name + '_2' else: self.__active_table_name = self.view_name + '_2' self.__inactive_table_name = self.view_name + '_1'
def execute(self): self.__set_table_names() record = tinyAPI.dsh().one( """select table_collation from information_schema.tables where table_schema = %s and table_name = %s""", [self.__schema_name, self.__inactive_table_name]) if record is None or record['table_collation'] is None: raise RuntimeError( 'could not determine table collation for "{}.{}"' .format(self.__schema_name, self.__inactive_table_name)) tinyAPI.dsh().query( "set collation_connection = '{}'" .format(record['table_collation'])) tinyAPI.dsh().query( "create or replace view {}.{} as select * from {}.{}" .format(self.__schema_name, self.view_name, self.__schema_name, self.__inactive_table_name)) tinyAPI.dsh().commit()
def __compile_dirty_module_list(self): self.__notice('Determining if there are dirty modules...') records = tinyAPI.dsh().query('''select name from rdbms_builder.dirty_module''') for record in records: if record['name'] not in self.__modules: # The dirty module is no longer part of the application, so # we should stop tracking it here. self.__notice('(-) ' + record['name'], 1) tinyAPI.dsh().query( '''delete from rdbms_builder.dirty_module where name = %s''', [record['name']]) tinyAPI.dsh().commit() else: self.__notice('(+) ' + record['name'], 1) self.__compile_build_list_for_module(record['name'])
def __compile_dirty_module_list(self): self.__notice('Determining if there are dirty modules...') records = tinyAPI.dsh().query( '''select name from rdbms_builder.dirty_module''') for record in records: if record['name'] not in self.__modules: # The dirty module is no longer part of the application, so # we should stop tracking it here. self.__notice('(-) ' + record['name'], 1) tinyAPI.dsh().query( '''delete from rdbms_builder.dirty_module where name = %s''', [record['name']]) tinyAPI.dsh().commit() else: self.__notice('(+) ' + record['name'], 1) self.__compile_build_list_for_module(record['name'])
def cli_main(function, args=None, stop_on_signal=True): '''Executes the "main" CLI function passing in the configured arguments.''' if stop_on_signal and os.path.isfile(CLI_STOP_SIGNAL_FILE): raise CLIException('CLI execution has been stopped') Context().set_cli() cli = CLI(args) if not stop_on_signal: cli.dont_stop_on_signal() try: function(cli) except Exception as e: _handle_cli_exception_logging(e) cli.set_status_error() tinyAPI.dsh().rollback(True) tinyAPI.dsh().close() raise
def __drop_objects(self): self.__notice('Dropping objects that will be rebuilt...') if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() tables = tinyAPI.dsh().query('''select table_schema, table_name from tables where table_schema in (''' + self.__managed_schemas + ')') for table in tables: parts = table['table_name'].split('_') if parts[0] in self.__modules_to_build_prefix: self.__notice('(-) table ' + table['table_name'], 1) tinyAPI.dsh().query('drop table ' + table['table_schema'] + '.' + table['table_name']) routines = tinyAPI.dsh().query('''select routine_schema, routine_type, routine_name from routines where routine_schema in (''' + self.__managed_schemas + ')') for routine in routines: parts = routine['routine_name'].split('_') if parts[0] in self.__modules_to_build_prefix: self.__notice('(-) ' + routine['routine_type'].lower() + ' ' + routine['routine_name']) tinyAPI.dsh().query('drop type ' + routine['routine_schema'] + '.' + routine['routine_name'])
def test_cached_data(self): patcher_1 = mock.patch('tinyAPI.base.data_store.memcache.pylibmc') patcher_2 = mock.patch('tinyAPI.base.data_store.provider.Context') memcache = patcher_1.start() context = patcher_2.start() client = mock.Mock() memcache.Client.return_value = client context.env_unit_test.return_value = False for i in range(2): tinyAPI.dsh() \ .memcache( 'test_memcache_cache', 180) \ .query( """select 1 from dual""") self.assertEqual(1, client.get.call_count) tinyAPI.dsh().close() tinyAPI.dsh() \ .memcache( 'test_memcache_cache', 180) \ .query( """select 1 from dual""") self.assertEqual(2, client.get.call_count) patcher_1.stop() patcher_2.stop()
def __track_module_info(self, module, file): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() with open(file, 'rb') as f: sha1 = hashlib.sha1(f.read()).hexdigest(); tinyAPI.dsh().query( '''insert into rdbms_builder.module_info ( file, sha1 ) values ( %s, %s ) on duplicate key update sha1 = %s''', [file, sha1, sha1]) tinyAPI.dsh().query( '''delete from rdbms_builder.dirty_module where name = %s''', [module.get_name()]) tinyAPI.dsh().commit()
def __file_has_been_modified(self, file): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() with open(file, 'rb') as f: sha1 = hashlib.sha1(f.read()).hexdigest() records = tinyAPI.dsh().query( '''select sha1 from rdbms_builder.module_info where file = %s''', [file]) return len(records) == 0 or records[0]['sha1'] != sha1
def __file_has_been_modified(self, file): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() with open(file, 'rb') as f: sha1 = hashlib.sha1(f.read()).hexdigest(); records = tinyAPI.dsh().query( '''select sha1 from rdbms_builder.module_info where file = %s''', [file]) return len(records) == 0 or records[0]['sha1'] != sha1
def __find_potentially_incorrect_default_values(self): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() self.__notice('Finding potentially incorrect default values...') records = tinyAPI.dsh().query( """select table_name, column_name from information_schema.columns where table_schema in (""" + self.__managed_schemas + """) and column_default = %s""", ['0000-00-00 00:00:00']) for record in records: self.__notice( '(!) {}.{}'.format(record['table_name'], record['column_name']), 1) self.__notice('has default of "0000-00-00 00:00:00"', 3)
def main(cli): '''Executes an example of a RDBMS duplicate key exception.''' cli.header('Duplicate Key - RDBMS') tinyAPI.dsh.select_db('local', 'tinyAPI') cli.notice('Creating table called "a_table"...') tinyAPI.dsh().query(('create table if not exists a_table(' + 'id integer not null auto_increment primary key,' + 'value integer not null)')) cli.notice('Removing any records in a_table...') tinyAPI.dsh().query('delete from a_table') cli.notice('Added record to a_table...') tinyAPI.dsh().create('a_table', {'id': 1, 'value': 1}) cli.notice('The next record is a duplicate...') tinyAPI.dsh().create('a_table', {'id': 1, 'value': 1})
def __build_dml(self, module): for file in module.get_dml_files(): with open(file, 'rb') as f: self.__execute_statement(f.read().decode()) self.__track_module_info(module, file) routines = tinyAPI.dsh().query("""select routine_type, routine_name from information_schema.routines where routine_name like '""" + module.get_prefix() + "\_%%'") for routine in routines: self.__notice( '(+) ' + routine['routine_type'].lower() + ' ' + routine['routine_name'] + '()', 2) self.__num_rdbms_routines += 1
def test_deleting_from_table(self): if self.__execute_tests is True: for i in range(0, 5): tinyAPI.dsh().create('unit_test_table', {'value': i}, True) self.assertEqual( 5, tinyAPI.dsh().count('select count(*) from unit_test_table')) tinyAPI.dsh().delete('unit_test_table', {'value': 3}) self.assertEqual( 4, tinyAPI.dsh().count('select count(*) from unit_test_table'))
def __find_potentially_incorrect_default_values(self): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() self.__notice('Finding potentially incorrect default values...') records = tinyAPI.dsh().query( """select table_name, column_name from information_schema.columns where table_schema in (""" + self.__managed_schemas + """) and column_default = %s""", ['0000-00-00 00:00:00'] ) for record in records: self.__notice( '(!) {}.{}' .format(record['table_name'], record['column_name']), 1 ) self.__notice('has default of "0000-00-00 00:00:00"', 3)
def __drop_objects(self): self.__notice('Dropping objects that will be rebuilt...') if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() tables = tinyAPI.dsh().query( '''select table_schema, table_name from tables where table_schema in (''' + self.__managed_schemas + ')') for table in tables: parts = table['table_name'].split('_') if parts[0] in self.__modules_to_build_prefix: self.__notice('(-) table ' + table['table_name'], 1) tinyAPI.dsh().query( 'drop table ' + table['table_schema' ] + '.' + table['table_name']) routines = tinyAPI.dsh().query( '''select routine_schema, routine_type, routine_name from routines where routine_schema in (''' + self.__managed_schemas + ')') for routine in routines: parts = routine['routine_name'].split('_') if parts[0] in self.__modules_to_build_prefix: self.__notice('(-) ' + routine['routine_type'].lower() + ' ' + routine['routine_name']) tinyAPI.dsh().query( 'drop type ' + routine['routine_schema'] + '.' + routine['routine_name'])
def __verify_rdbms_builder_objects(self): if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() tinyAPI.dsh.select_db(self.__cli.args.connection_name, 'information_schema') databases = tinyAPI.dsh().query('show databases') for database in databases: if database['Database'] == 'rdbms_builder': return build_instructions = ''' create database rdbms_builder; create table rdbms_builder.module_info ( file varchar(100) not null primary key, sha1 char(40) not null ); create table rdbms_builder.dirty_module ( name varchar(100) not null primary key ); grant all privileges on rdbms_builder.* to ''@'localhost' identified by ''; flush privileges;''' raise RDBMSBuilderException( 'RDBMS Builder database and objects do not exist; create them as ' + 'root using:\n' + build_instructions)
def __build_dml(self, module): for file in module.get_dml_files(): with open(file, 'rb') as f: self.__execute_statement(f.read().decode()) self.__track_module_info(module, file) routines = tinyAPI.dsh().query( """select routine_type, routine_name from information_schema.routines where routine_name like '""" + module.get_prefix() + "\_%%'") for routine in routines: self.__notice('(+) ' + routine['routine_type'].lower() + ' ' + routine['routine_name'] + '()', 2) self.__num_rdbms_routines += 1
def tearDown(self): self.tear_down() tinyAPI.dsh().rollback(True)
def __compile_reference_definitions(self): '''Compile the reference tables created with RefTable() into variables so that no database interactions are required to interact with them.''' if ConfigManager.value('reference definition file') is None: return self.__notice('Compiling reference definitions...') if ConfigManager.value('data store') != 'mysql': self.__data_store_not_supported() reference_tables = tinyAPI.dsh().query( """select table_schema, table_name from tables where table_schema in (""" + self.__managed_schemas + """) and table_name like '%%\_ref\_%%' order by table_name asc""") content = """ # +---------------------------------------------------------------------------+ # | WARNING - MACHINE GENERATED FILE | # +---------------------------------------------------------------------------+ ## # Any changes that you make directly to this file WILL BE LOST! It was auto- # generated by the RDBMS Builder. ## # ----- Imports --------------------------------------------------------------- import builtins import gettext # ----- Instructions ---------------------------------------------------------- # Language translation support builtins._ = gettext.gettext # TABLE tinyAPI_ref_unit_test builtins.TINYAPI_UNIT_TEST_ONE = 1 builtins.TINYAPI_UNIT_TEST_TWO = 2 builtins.TINYAPI_UNIT_TEST_THREE = 3 def _tinyapi_ref_unit_test(): return { 1: "one", 2: "two", 3: "three" } builtins._tinyapi_ref_unit_test = _tinyapi_ref_unit_test """ index = 0 for reference_table in reference_tables: data = tinyAPI.dsh().query( """select value, id from """ + reference_table['table_schema'] + '.' + reference_table['table_name'] + """ order by id asc""") if index > 0: content += "\n" index += 1 content += "# TABLE " + reference_table['table_name'] + "\n" values = [] for item in data: value = re.sub('[^A-Za-z0-9_]', '_', reference_table['table_name'] + '_' + item['value']) value = re.sub('_ref_', '_', value) value = re.sub('[_]+', '_', value) value = re.sub('[_]+$', '', value) content += ('builtins.' + value.upper() + ' = ' + str(item['id']) + "\n") values.append(' ' + str(item['id']) + ': "' + item['value'] + '"') content += ("def _" + reference_table['table_name'] + '():\n' + ' return {\n' + ",\n".join(values) + '\n }\n' + 'builtins._' + reference_table['table_name' ] + ' = _' + reference_table[ 'table_name' ] + "\n") f = open(ConfigManager.value('reference definition file'), 'w') f.write(content.lstrip()) f.close()
def main(cli): '''Executes examples of all the ways in which you can use the data store sub-system for interacting with an RDBMS.''' cli.header('Data Store - RDBMS') '''Select the named database you wish to connect to. In this case, "local" is one of the connection names defined in tinyAPI_config.py. "tinyAPI" is the name of the schema you wish to connect to.''' tinyAPI.dsh.select_db('local', 'tinyAPI') cli.notice('Creating table called "a_table"...') tinyAPI.dsh().query( ('create table if not exists a_table(' + 'id integer not null auto_increment primary key,' + 'value integer not null)') ) cli.notice('Removing any records in a_table...') tinyAPI.dsh().query('delete from a_table') cli.notice('Added record to a_table...') for i in range(1, 5): id = tinyAPI.dsh().create( 'a_table', {'value': i}, True) cli.notice('created ID #' + str(id), 1) tinyAPI.dsh().commit() cli.notice('How many records did we just create?') cli.notice(str(tinyAPI.dsh().count('select count(*) from a_table')), 1) cli.notice('What is the ID for 3rd value?') record = tinyAPI.dsh().nth(2, 'select id from a_table'); cli.notice(str(record['id']), 1) cli.notice('Update all records by multiplying value by 2.') tinyAPI.dsh().query( ('update a_table' + ' set value = value * 2')) tinyAPI.dsh().commit() cli.notice('Delete one of the records...') tinyAPI.dsh().delete( 'a_table', {'value': 2}) tinyAPI.dsh().commit() cli.notice('Get all of the remaining records...') records = tinyAPI.dsh().query( '''select id, value from a_table order by id asc''') for record in records: cli.notice(str(record['id']) + ' = ' + str(record['value']), 1) cli.notice('Delete all of the records from a_table...') tinyAPI.dsh().delete('a_table') tinyAPI.dsh().commit() cli.notice('Drop a_table...') tinyAPI.dsh().query('drop table a_table')
def test_row_count(self): tinyAPI.dsh().query('''insert into unit_test_table( id, value) values( 1000, 123)''') self.assertEqual(1, tinyAPI.dsh().get_row_count()) tinyAPI.dsh().query('''update unit_test_table set value = 456 where id = 1''') self.assertEqual(0, tinyAPI.dsh().get_row_count()) tinyAPI.dsh().query('''update unit_test_table set value = 456 where id = 1000''') self.assertEqual(1, tinyAPI.dsh().get_row_count()) tinyAPI.dsh().delete('unit_test_table', {'id': 1000}) self.assertEqual(1, tinyAPI.dsh().get_row_count())
def tearDown(self): if self.__execute_tests is True: tinyAPI.dsh().query('drop table unit_test_table')
def test_last_row_id_with_select(self): tinyAPI.dsh().query('''select 1 from dual''') self.assertIsNone(tinyAPI.dsh().get_last_row_id())