def Query(self, sql, params=None, commit=True): """Query the database via our connection.""" set_request_lock = None set_single_threaded_lock = None try: (set_request_lock, set_single_threaded_lock) = self.__QueryLock() # If request tracing is enabled, keep track of the queries if self.request.trace: self.request.sql_queries.append((sql, params)) done = False retry = 0 # Loop through errors and stuff, if they are recoverable (connection failures->reconnect) while not done: try: if not params: Log('Query: %s' % sql) else: Log('Query: %s -- %s' % (sql, params)) result = Query(self.connection, self.cursor, sql, params=params, commit=commit) done = True # Handle DB connection problems except pymysql.OperationalError, e: print 'MySQL: OperationError: %s' % e retry += 1 if retry >= 3: self.__QueryUnlock(set_request_lock, set_single_threaded_lock) raise Exception('Failed %s times: %s' % (retry - 1, e)) # Else, reconnect, something went wrong, this is the best way to fix it else: self.__QueryUnlock(set_request_lock, set_single_threaded_lock) self.Connect() # If we are single threaded, release the lock finally: # If we got through the initial lock (it will be a bool, not None) self.__QueryUnlock(set_request_lock, set_single_threaded_lock) return result
def __init__(self, connection_data, server_id, request, is_single_threaded=SINGLE_THREADED_DEFAULT): Log('Creating new connection: MySQL: %s' % connection_data['datasource']['database']) # We need these to actually connect self.connection_data = connection_data self.server_id = server_id self.is_single_threaded = is_single_threaded # This tells us who is connection. Release() to make this connection available for other requests. self.request = request self.request_lock = threading.Lock() # Generate the server key, since this specifies which CONNECTION_POOL_POOL we are in self.server_key = GetServerKey(request) self.connection = None self.cursor = None # Connect self.Connect()
def Connect(self): """Connect to the database""" server = self.GetServerData() Log('Connecting to MySQL server: %s: %s' % (server['host'], server['database'])) # Read the password from the first line of the password file try: password = open(server['password_path']).read().split('\n', 1)[0] Log('Loaded password: %s' % server['password_path']) except Exception, e: Log( 'ERROR: Failed to read from password file: %s' % server['password_path'], logging.ERROR) password = None
def Release(self): """Release this connection.""" Log('Releasing connection: MySQL: %s: %s' % (self.server_key, self.request.username)) if self.request_lock.locked and self.request == None: print '\n\nERROR: Request Connection was not locked, but had a request: %s' % self.request self.request = None self.request_lock.release()
def Close(self): """Close the cursor and connection, if they are open, and set them to None""" Log('Closing connection: MySQL: %s: %s' % (self.connection_data['datasource']['database'], self.server_key)) if self.cursor: try: self.cursor.close() finally: self.cursor = None if self.connection: try: self.connection.close() finally: self.connection = None
def Acquire(self, request): """Acquire this Connection for this Request.""" if self.request_lock.locked(): raise Exception( 'Attempting to Acquire a Connection when it is already locked: %s' % request) # Get the lock self.request_lock.acquire() Log('Acquiring connection: MySQL: %s: %s (auto_commit=%s)' % (self.server_key, request.username, request.auto_commit)) self.request = request try: # If any transactions werent committed, we obviously dont want them to be, or whatever, theyre gone! self.connection.rollback() except pymysql.OperationalError, e: self.Connect()
def ReleaseLock(request, lock): """Releases a lock. Args: request: Request Object, the connection spec data and user and auth info, etc lock: string, lock name. Will be unique Returns: boolean, did we release the lock? True = yes. False = no. If false, the lock was not set (which can indicate a problem) """ Log('Release Lock: %s' % lock) lock_list = Filter(request, 'schema_lock', {'name': lock}) if not lock_list: return False lock_record = lock_list[0] Delete(request, 'schema_lock', lock_record['id']) return True
def AcquireLock(request, lock, timeout=None, sleep_interval=0.1): """Spin loops until we can get this lock. Args: request: Request Object, the connection spec data and user and auth info, etc lock: string, lock name. Will be unique timeout: float (optional), time in seconds before timeout sleep_interval: float, time to sleep between checking on the lock Returns: boolean, did we get the lock? True = yes. False = no. This only matters if timeout is set, otherwise we will wait forever """ Log('Acquire Lock: %s' % lock) done = False started = time.time() while not done: duration = time.time() - started # try: if 1: Set(request, 'schema_lock', {'name': lock}) # It worked, we are done done = True # #TODO(g): Get the correct exception type here, so we only catch insertion failures # except Exception, e: # print 'AcquireLock: Failed: %s: %s: %s' % (lock, duration, e) # # if timeout and duration > timeout: # return False # # # Sleep for the specified time # time.sleep(sleep_interval) return True
def SetVersion(request, table, data, commit_version=False, version_number=None, version_data=None, commit=True): """Put (insert/update) data into this datasource's Version Management tables (working, unless version_number is specified). This is the same as Set() except version_managament=True, which is more explicit. This should be easier to read and type, and it clearly does something different. By default Set() will Args: request: Request Object, the connection spec data and user and auth info, etc table: string, name of table to operate on data: dict, record to set into table commit_version: boolean (default False), if True, this will attempt to Commit the Version data after it has been stored in version_change as a single record update, without any additional VMCM actions version_number: int (default None), if an int, this is the version number in the version_change table to write to, if None this will create a new version_working entry Returns: int or None, if creating a new record this returns the newly created record primary key (ex: `id`), otherwise None """ Log('Set Version: %s: %s' % (table, data)) (schema, schema_table) = GetInfoSchemaAndTable(request, table) user = GetUser(request) if version_number: version_table = 'version_pending' else: version_table = 'version_working' # Expand version data if version_data is not None: _, change, delete_change = version_data else: # If version_working wasn't passed in, lets get it if version_number: version_working_list = Get(request, 'version_pending', version_number) else: version_working_list = Filter(request, 'version_working', {'user_id': user['id']}) if version_working_list: version_working = version_working_list[0] else: if version_number: raise Exception('Unable to get version data for version=%s' % version_number) if version_working: change = utility.path.LoadYamlFromString( version_working['data_yaml']) delete_change = utility.path.LoadYamlFromString( version_working['delete_data_yaml']) if not change: change = {} if not delete_change: delete_change = {} else: version_working = {'user_id': request.user['id'], 'data_yaml': {}} # Format record key #TODO(g): Do this properly with the above dynamic PKEY info data_key = data['id'] # Add this set data to the version change record, if it doesnt exist if schema['id'] not in change: change[schema['id']] = {} # Add this table to the change record, if it doesnt exist if schema_table['id'] not in change[schema['id']]: change[schema['id']][schema_table['id']] = {} # Readability variable change_table = change[schema['id']][schema_table['id']] print '\n\nSetting data in table: %s: %s: %s\nChange Table: %s\nData: %s\n\n' % ( schema['id'], schema_table['id'], data_key, change_table, data) # Add this specific record, or update it if it already exists if data_key in change_table: change_table[data_key].update(data) else: change_table[data_key] = data print '\nAfter Setting Data: %s\n\n' % change_table # If we had an entry in the delete_change list for this record, remove that. Any position change wipes out a delete, for obvious reasons. if schema['id'] in delete_change: if schema_table['id'] in delete_change[schema['id']]: # If we have this record's ID in our delete list, remove it if data['id'] in delete_change[schema['id']][schema_table['id']]: delete_change[schema['id']][schema_table['id']].remove( data['id']) # Get the Real record (if it exists), so we only store fields that are different. If all fields are the same, we store nothing real_record = Get(request, table, data['id'], use_working_version=False) # If we have a Real record, then remove any matching fields if real_record: for (real_key, real_value) in real_record.items(): # If our change data has this key if real_key in change_table[data_key]: #NOTE(t) converting everything to a string because mysql stores it all as strings anyways-- and people might be inserting an int into # a varchar field (for example) -- and we don't want it to show as a diff # If the key is the same value as the Real record value, then we dont need it versioned, because it hasnt changed, and it isnt the 'id' key # Also, delete if the real value is NULL, and we have an empty string (no change) if str(real_value) == str( change_table[data_key][real_key]) or ( real_value == None and change_table[data_key][real_key] == ''): del change_table[data_key][real_key] # Clean up any unused data structures, so we dont have a bunch of junk hanging around CleanEmptyVersionData(change) CleanEmptyVersionData(delete_change) # Save the change version_working if commit: # Put this change record back into the version_change table, so it's saved version_working['data_yaml'] = utility.path.DumpYamlAsString(change) version_working['delete_data_yaml'] = utility.path.DumpYamlAsString( delete_change) result_record = SetDirect(request, version_table, version_working) return result_record else: return
def DeleteVersion(request, table, record_id, version_number=None, version_data=None, commit=True): """Delete a single record from Working Version or a Pending Commit. Args: request: Request Object, the connection spec data and user and auth info, etc table: string, name of table to operate on record_id: int, primary key (ex: `id`) of the record in this table. Use Filter() to use other field values version_number: int, this is the version number in the version_changelist.id Returns: None """ if version_number != None: raise Exception( 'TBD: Version Number specific Deletes has not yet been implemented.' ) Log('Delete Version: %s: %s' % (table, record_id)) (schema, schema_table) = GetInfoSchemaAndTable(request, table) # Expand version data if version_data is not None: _, update_data, delete_data = version_data else: user = GetUser(request) version_working_list = Filter(request, 'version_working', {'user_id': user['id']}) if version_working_list: version_working = version_working_list[0] # No version working, we are done else: Log('Delete Version: %s: %s -- No version_working available for this user' % (table, record_id)) return # If we dont have a working version, make new dicts to store data in update_data = {} delete_data = {} # If, we have a working version, so get the data if version_working: update_data = utility.path.LoadYamlFromString( version_working['data_yaml']) delete_data = utility.path.LoadYamlFromString( version_working['delete_data_yaml']) if not update_data: update_data = {} if not delete_data: delete_data = {} # Check to see if this a Real record real_record = Get(request, table, record_id) # If we have a Real record, make an entry in the Delete Data, because we really want to delete this if real_record: # If we dont have the schema in our delete_data, add it if schema['id'] not in delete_data: delete_data[schema['id']] = {} # If we dont have the schema_table in our delete_data, add it if schema_table['id'] not in delete_data[schema['id']]: delete_data[schema['id']][schema_table['id']] = [] # If we dont have this record in the proper place already (other records of that table to-delete), and this isnt a negative number, append it if record_id not in delete_data[schema['id']][ schema_table['id']] and record_id >= 0: delete_data[schema['id']][schema_table['id']].append(record_id) # If we have an entry of this record in update_data, then remove that, because Delete always means to clear version data if schema['id'] in update_data: if schema_table['id'] in update_data[schema['id']]: if record_id in update_data[schema['id']][schema_table['id']]: # Delete the record from this update_data table, we are nulling that potential change del update_data[schema['id']][schema_table['id']][record_id] # Clean up the data, so we dont leave empty cruft around CleanEmptyVersionData(update_data) CleanEmptyVersionData(delete_data) if commit: # Add this to the working version record version_working['data_yaml'] = utility.path.DumpYamlAsString( update_data) version_working['delete_data_yaml'] = utility.path.DumpYamlAsString( delete_data) # Save the working version record Set(request, 'version_working', version_working)
def GetConnection(request, server_id=None): """Returns a connection to the specified database server_id, based on the request number (may already have a connection for that request).""" global CONNECTION_POOL_POOL # If we didnt have a server_id specified, use the master_server_id if server_id != None: server_id = server_id elif request.server_id == None: server_id = request.connection_data['datasource']['master_server_id'] else: server_id = request.server_id # Find the master host, which we will assume we are connecting to for now found_server = None for server_data in request.connection_data['datasource']['servers']: if server_data['id'] == server_id: found_server = server_data break # Generate the server key, since this specifies which CONNECTION_POOL_POOL we are in server_key = GetServerKey(request) # Look through the current connection pool, to see if we already have a connection for this request_number if server_key in CONNECTION_POOL_POOL: for connection in CONNECTION_POOL_POOL[server_key]: # If this connection is for the same request, use it if connection.IsUsedByRequest(request): return connection # Look through current connection pool, to see if we have any available connections in this server, that we can use if server_key in CONNECTION_POOL_POOL: for connection in CONNECTION_POOL_POOL[server_key]: # If this connection is available (not being used in a request) if connection.IsAvailable(): # This request has now acquired this connection connection.Acquire(request) return connection # Create the connection connection = Connection(request.connection_data, server_id, request) connection.Acquire(request) # Ensure we have a pool for this server in our connection pool pools if connection.server_key not in CONNECTION_POOL_POOL: try: CONNECTION_POOL_POOL_LOCK.acquire() Log('Creating new MySQL connection pool: %s' % connection.server_key) CONNECTION_POOL_POOL[connection.server_key] = [connection] finally: CONNECTION_POOL_POOL_LOCK.release() # Else, add it to the existing connection pool pool else: try: CONNECTION_POOL_POOL_LOCK.acquire() Log('Appending to existing MySQL connection pool: %s (Count: %s)' % (connection.server_key, len(CONNECTION_POOL_POOL[connection.server_key]))) CONNECTION_POOL_POOL[connection.server_key].append(connection) finally: CONNECTION_POOL_POOL_LOCK.release() return connection
def Action(connection_data, action_input_args): """Perform action: Test VMCM (Version and Change Managament)""" print 'Test VMCM (Version and Change Managament)' # Create working Request object request = datasource.Request(connection_data, 'ghowland', 'testauth') # Determine table to operate on table = 'owner_group' record_id = 1 # Get what versions are already available versions_available = datasource.RecordVersionsAvailable( request, table, record_id) # Get original data record = datasource.Get(request, table, record_id) # Make a change record['name'] = '%s!' % record['name'] # Save the change un-commited (as a version) Log('Set initial changed record in version_working\n\n') datasource.SetVersion(request, table, record) # Get data again (with VM changes applied) record_again = datasource.Get(request, table, record_id) ReadLine('1: Pause for initial set version, type enter to continue: ') # Get HEAD data (without VM changed applied) record_head = datasource.Get(request, table, record_id, use_working_version=False) # Abandon working versions of data Log('Abandon Working version\n\n') was_abandoned = datasource.AbandonWorkingVersion(request, table, record_id) ReadLine( '2: Pause for initial set version is abandonded, type enter to continue: ' ) # Get data again (with VM changed applied, but no change) record_again_again = datasource.Get(request, table, record_id) # We already made this change once, but if you forgot, this was it: # record['name'] = '%s!' % record['name'] # Save the change un-commited (as a version) Log('Set second changed record in version_working\n\n') datasource.SetVersion(request, table, record) ReadLine('3: Pause for second set version, type enter to continue: ') # Get again, see change record_again_again_again = datasource.Get(request, table, record_id) # Commit change Log('Commit working version\n\n') datasource.CommitWorkingVersion(request) # Get HEAD data, see change record_head_again = datasource.Get(request, table, record_id, use_working_version=False) # List versions and see where our new version made that change versions_available_again = datasource.RecordVersionsAvailable( request, table, record_id)
def ProcessAction(action, action_args, command_options): """Process the specified action, by it's action arguments. Using command options.""" #NOTE(g): This cannot be imported at the top, because it isnt ready yet, and it's a parent. Python modules are terrible at pathing issues. import schemaman.action as action_module # If Action is info if action == 'info': if len(action_args) == 0: Usage( '"init" action requires 1 argument: path schema definition YAML' ) elif not os.path.isfile(action_args[0]): Usage('"init" action requires arguments: %s: Is not a file' % action_args[0]) schema_path = action_args[0] connection_data = datasource.LoadConnectionSpec(schema_path) print '\nConnection Specification:\n\n%s\n' % pprint.pformat( connection_data) print '\nTesting Connection:\n' request = datasource.Request(connection_data, 'testuser', 'testauth') # Attempt to connect to the DB to test it result = datasource.TestConnection(request) if result: print '\nConnection test: SUCCESS' else: print '\nConnection test: FAILURE' # If Action is action: This is where we dump all kinds of functions, that dont need top-level access. The long-tail of features. elif action == 'action': if len(action_args) < 3: Usage( 'All actions requires at least 3 arguments: <path schema definition YAML> <category> <action> ...' ) elif not os.path.isfile(action_args[0]): Usage('All actions requires arguments: %s: Is not a file' % action_args[0]) schema_path = action_args[0] connection_data = datasource.LoadConnectionSpec(schema_path) # Get all the args after the initial 3 args and use them as input for our Action function action_input_args = action_args[3:] #TODO(g): Turn this into YAML so that we can add into it. Make sure it's multiple YAML files or something, so people can add their own without impacting the standard ones pass # -- Category -- # Configure if action_args[1] == 'config': # Action if action_args[2] == 'version_change_management': if len(action_input_args) != 1: Error( 'action: populate: schema_into_db: Takes 1 argument: <path to target schema defintion YAML>' ) result = action_module.config.version_change_management.Action( connection_data, action_input_args) print result else: Usage('Unknown Action in Category: %s: %s' % (action_args[1], action_args[2])) # Populate elif action_args[1] == 'populate': # Action if action_args[2] == 'schema_into_db': if len(action_input_args) != 1: Error( 'action: populate: schema_into_db: Takes 1 argument: <path to target schema defintion YAML>' ) result = action_module.populate.schema_into_db.Action( connection_data, action_input_args) print result else: Usage('Unknown Action in Category: %s: %s' % (action_args[1], action_args[2])) # Test elif action_args[1] == 'test': # Action if action_args[2] == 'vmcm': # if len(action_input_args) != 1: # Error('action: test: vmcm: Takes 1 argument: <path to target schema defintion YAML>') result = action_module.test.test_vmcm.Action( connection_data, action_input_args) print result else: Usage('Unknown Action in Category: %s: %s' % (action_args[1], action_args[2])) else: Usage('Unknown Category: %s' % action_args[1]) # Else, Initialize a directory to be a SchemaMan location elif action == 'init': if len(action_args) == 0: Usage( '"init" action requires 1 argument: directory to store schema definition inside of' ) elif not os.path.isdir(action_args[0]): Usage('"init" action requires arguments: %s: Is not a directory' % action_args[0]) schema_dir = action_args[0] Log('Initializing Schema Definition Directory: %s' % schema_dir) # Collect the initialization data from the user data = utility.interactive_input.CollectInitializationDataFromInput() schema_path = '%s/%s.yaml' % (schema_dir, data['alias']) # Check to see if we havent already created this schema definition. We don't allow init twice, let them clean it up. if os.path.exists(schema_path): Error( 'The file you requested to initialize already exists, choose another Schema Alias: %s' % schema_path) # Create the location for our schema record paths. This is the specific data source record schema, whereas the schema_path is the definition for the data set schema_record_path = '%s/schema/%s.yaml' % (schema_dir, data['alias']) # We need to create this directory, if it doesnt exist schema_record_path_dir = os.path.dirname(schema_record_path) if not os.path.isdir(schema_record_path_dir): os.makedirs(schema_record_path_dir, mode=MODE_DIRECTORY) # If we dont have this file yet, create it, so we can write into it if not os.path.isfile(schema_record_path): with open(schema_record_path, 'w') as fp: # Write an empty dictionary for YAML fp.write('{}\n') # Format the data into the expandable schema format (we accept lots more data, but on init we just want 1 set of data) schema_data = { 'alias': data['alias'], 'name': data['name'], 'owner_user': data['owner_user'], 'owner_group': data['owner_group'], 'datasource': { 'database': data['database_name'], 'user': data['database_user'], 'password_path': data['database_password_path'], 'master_server_id': 1, 'servers': [ { 'id': 1, 'host': data['database_host'], 'port': data['database_port'], 'type': data['database_type'], }, ], }, 'schema_paths': [schema_record_path], 'value_type_path': 'data/schema/value_types/standard.yaml', 'actions': {}, 'version': CONNECTION_SPEC_VERSION, } SaveYaml(schema_path, schema_data) Log('Initialized new schema path: %s' % schema_path) # Else, if Action prefix is Schema elif action == 'schema': # Ensure there are sub-actions, as they are always required if len(action_args) == 0: Usage( '"schema" action requires arguments: create, export, extract, migrate, update' ) elif action_args[0] == 'create': connection_data = datasource.LoadConnectionSpec(action_args[1]) result = datasource.CreateSchema(connection_data) # Export the current DB schema to a specified data source elif action_args[0] == 'export': if len(action_args) == 3: Usage( '"schema export" action requires arguments: <path to connection spec> <path to export data to>' ) connection_data = datasource.LoadConnectionSpec(action_args[1]) target_path = action_args[2] if not os.path.isdir(os.path.dirname(target_path)): Usage('Path specified does not have a valid directory: %s' % target_path) result = datasource.ExportSchema(connection_data, target_path) # Extract is the opposite of "update", and will get our DB schema and put it into our files where we "update" from elif action_args[0] == 'extract': if len(action_args) == 1: Usage( '"schema extract" action requires arguments: <path to connection spec>' ) connection_data = datasource.LoadConnectionSpec(action_args[1]) result = datasource.ExtractSchema(connection_data) print 'Extract Schema:' pprint.pprint(result) # Save the extracted schema to the last file in the connection data (which updates over the rest) #TODO(g): We should load all the previous files, and then only update things that are already in the current files, and save them, and then add any new things to the last file. output_path = connection_data['schema_paths'][-1] SaveYaml(output_path, result) elif action_args[0] == 'migrate': # Export from one, and import to another, in one step source_result = datasource.ExportSchema() target_result = datasource.UpdateSchema(source_result) # Update the database based on our schema spec files elif action_args[0] == 'update': connection_data = datasource.LoadConnectionSpec(action_args[1]) result = datasource.UpdateSchema(connection_data) # ERROR else: Usage('Unknown Schema action: %s' % action) # Else, if Action prefix is Data elif action == 'data': if len(action_args) == 0: Usage('"data" action requires arguments: export, import') elif action_args[0] == 'export': result = datasource.ExportData() elif action_args[0] == 'import': result = datasource.ImportData() # ERROR else: Usage('Unknown Data action: %s' % action) # Put elif action == 'put': result = datasource.Put() # Get elif action == 'get': result = datasource.Get() # Filter elif action == 'filter': result = datasource.Filter() # Delete elif action == 'delete': result = datasource.Delete() # ERROR else: Usage('Unknown action: %s' % action)