def updateMySQLBackend(backendConfigFile=u'/etc/opsi/backends/mysql.conf',
additionalBackendConfiguration={}):
"""
Applies migrations to the MySQL backend.
:param backendConfigFile: Path to the file where the backend \
configuration is read from.
:type backendConfigFile: str
:param additionalBackendConfiguration: Additional / different \
settings for the backend that will extend / override the configuration \
read from `backendConfigFile`.
:type additionalBackendConfiguration: dict
"""
config = getBackendConfiguration(backendConfigFile)
config.update(additionalBackendConfiguration)
LOGGER.info(u"Current mysql backend config: %s" % config)
LOGGER.notice(u"Connection to database '%s' on '%s' as user '%s'" %
(config['database'], config['address'], config['username']))
mysql = MySQL(**config)
schemaVersion = readSchemaVersion(mysql)
LOGGER.debug("Found database schema version {0}", schemaVersion)
if schemaVersion is None:
LOGGER.notice("Missing information about database schema. Creating...")
createSchemaVersionTable(mysql)
with updateSchemaVersion(mysql, version=0):
_processOpsi40migrations(mysql)
schemaVersion = readSchemaVersion(mysql)
# The migrations that follow are each a function that will take the
# established database connection as first parameter.
# Do not change the order of the migrations once released, because
# this may lead to hard-to-debug inconsistent version numbers.
migrations = [
_dropTableBootconfiguration,
_addIndexOnProductPropertyValues,
_addWorkbenchAttributesToHosts,
_adjustLengthOfGroupId,
_increaseInventoryNumberLength,
]
for newSchemaVersion, migration in enumerate(migrations, start=1):
if schemaVersion < newSchemaVersion:
with updateSchemaVersion(mysql, version=newSchemaVersion):
migration(mysql)
LOGGER.debug("Expected database schema version: {0}",
DATABASE_SCHEMA_VERSION)
if not readSchemaVersion(mysql) == DATABASE_SCHEMA_VERSION:
raise BackendUpdateError("Not all migrations have been run!")
with MySQLBackend(**config) as mysqlBackend:
# We do this to make sure all tables that are currently
# non-existing will be created. That creation will give them
# the currently wanted schema.
mysqlBackend.backend_createBase()
def testDropTableBootConfiguration(mysqlBackendConfig, mySQLBackendConfigFile):
"""
Test if the BOOT_CONFIGURATION table gets dropped with an update.
"""
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
assert 'BOOT_CONFIGURATION' not in getTableNames(db)
def testAddingIndexToProductPropertyValues(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
# Just making sure nothing breaks because checking if the right
# index exists in mysql comes near totally senseless torture.
# Calling the update procedure a second time must not fail.
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
def testCorrectingObjectToGroupGroupIdFieldLength(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
for column in getTableColumns(db, 'OBJECT_TO_GROUP'):
if column.name == 'groupId':
assert column.type.lower().startswith('varchar(')
assert getColumnLength(column.type) == 255
break
def testUpdatingSchemaVersion(mysqlBackendConfig, mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createSchemaVersionTable(db)
version = readSchemaVersion(db)
assert version is None
with updateSchemaVersion(db, version=2):
pass # NOOP
version = readSchemaVersion(db)
assert version == 2
def testReadingSchemaVersionFailsOnUnfinishedUpdate(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createSchemaVersionTable(db)
try:
with updateSchemaVersion(db, version=1):
raise RuntimeError("For testing.")
except RuntimeError:
pass
with pytest.raises(DatabaseMigrationUnfinishedError):
readSchemaVersion(db)
def cleanUpMySQL(backendConfigFile=u'/etc/opsi/backends/mysql.conf'):
"""
Clean up an MySQL backend.
**This does not work with any backend other than MySQL.**
:param backendConfigFile: The configuration file of the currently \
used MySQL backend.
:type backendConfigFile: str
"""
config = backendUtil.getBackendConfiguration(backendConfigFile)
LOGGER.info(u"Current mysql backend config: %s" % config)
LOGGER.notice(
u"Connection to database '{database}' on '{address}' as user "
u"'{username}'".format(**config))
mysql = MySQL(**config)
LOGGER.notice(u"Cleaning up defaultValues in productProperties")
deleteIds = []
found = []
for res in mysql.getSet(
"SELECT * FROM PRODUCT_PROPERTY_VALUE WHERE isDefault like '1'"):
ident = ';'.join([
res['propertyId'], res['productId'], res['productVersion'],
res['productVersion'], res['value']
])
if ident not in found:
found.append(ident)
else:
if res['value'] in (
'0', '1') and res['product_property_id'] not in deleteIds:
deleteIds.append(res['product_property_id'])
for ID in deleteIds:
LOGGER.notice(u"Deleting PropertyValue id: {0}".format(ID))
mysql.execute("DELETE FROM `PRODUCT_PROPERTY_VALUE` where "
"`product_property_id` = '{0}'".format(ID))
def testIncreasingInventoryNumberFieldLength(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
for column in getTableColumns(db, 'HOST'):
if column.name == 'inventoryNumber':
assert column.type.lower().startswith('varchar(')
assert getColumnLength(column.type) == 64
break
else:
raise RuntimeError("Expected to find matching column.")
def testUpdatingCurrentBackendDoesBreakNothing(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)):
with MySQLBackend(**mysqlBackendConfig) as freshBackend:
freshBackend.backend_createBase()
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
# Updating again. Should break nothing.
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
with MySQLBackend(**mysqlBackendConfig) as anotherBackend:
# We want to have the latest schema version
assert DATABASE_SCHEMA_VERSION == readSchemaVersion(
anotherBackend._sql)
def testCorrectingProductIdLength(mysqlBackendConfig, mySQLBackendConfigFile):
"""
Test if the product id length is correctly set.
"""
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
for tableName in ('PRODUCT_PROPERTY', ):
print("Checking {0}...".format(tableName))
assert tableName in getTableNames(db)
assertColumnIsVarchar(db, tableName, 'productId', 255)
def testInsertingSchemaNumber(mysqlBackendConfig, mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
assert 'OPSI_SCHEMA' in getTableNames(db)
for column in getTableColumns(db, 'OPSI_SCHEMA'):
name = column.name
if name == 'version':
assert column.type.lower().startswith('int')
elif name == 'updateStarted':
assert column.type.lower().startswith('timestamp')
elif name == 'updateEnded':
assert column.type.lower().startswith('timestamp')
else:
raise Exception("Unexpected column!")
def testReadingSchemaVersionOnlyReturnsNewestValue(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createSchemaVersionTable(db)
with updateSchemaVersion(db, version=1):
pass
with updateSchemaVersion(db, version=15):
pass
for number in range(1, 4):
with updateSchemaVersion(db, version=number * 2):
pass
with updateSchemaVersion(db, version=3):
pass
assert readSchemaVersion(db) == 15
def testCorrectingLicenseOnClientLicenseKeyLength(mysqlBackendConfig,
mySQLBackendConfigFile):
"""
Test if the license key length is correctly set.
An backend updated from an older version has the field 'licenseKey'
on the LICENSE_ON_CLIENT table as VARCHAR(100).
A fresh backend has the length of 1024.
The size should be the same.
"""
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
for tableName in ('LICENSE_ON_CLIENT', 'SOFTWARE_CONFIG',
'SOFTWARE_LICENSE_TO_LICENSE_POOL'):
print("Checking {0}...".format(tableName))
assert tableName in getTableNames(db)
assertColumnIsVarchar(db, tableName, 'licenseKey', 1024)
def testAddingWorkbenchAttributesToHost(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createRequiredTables(db)
updateMySQLBackend(backendConfigFile=mySQLBackendConfigFile)
changesFound = 0
for column in getTableColumns(db, 'HOST'):
if column.name == 'workbenchLocalUrl':
assert column.type.lower().startswith('varchar(')
assert getColumnLength(column.type) == 128
changesFound += 1
elif column.name == 'workbenchRemoteUrl':
assert column.type.lower().startswith('varchar(')
assert getColumnLength(column.type) == 255
changesFound += 1
if changesFound == 2:
break
assert changesFound == 2
def testReadingSchemaVersionIfTableIsMissing(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
assert readSchemaVersion(db) is None
def testCreatingBackendSetsTheLatestSchemaVersion(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
with MySQLBackend(**mysqlBackendConfig) as freshBackend:
freshBackend.backend_createBase()
assert readSchemaVersion(db) == DATABASE_SCHEMA_VERSION
def testReadingSchemaVersionFromEmptyTable(mysqlBackendConfig,
mySQLBackendConfigFile):
with cleanDatabase(MySQL(**mysqlBackendConfig)) as db:
createSchemaVersionTable(db)
assert readSchemaVersion(db) is None