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)