def test_current_oracle(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
currentSchema = sqlSchema.child("current.sql")
current_version = self.versionFromSchema(currentSchema)
currentOracleSchema = sqlSchema.child("current-oracle-dialect.sql")
current_oracle_version = self.versionFromSchema(currentOracleSchema)
self.assertEqual(current_version, current_oracle_version)
schema_current = schemaFromPath(currentSchema)
schema_oracle = schemaFromPath(currentOracleSchema)
# Remove any not null constraints in the postgres schema for text columns as in
# Oracle nclob or nvarchar never uses not null
for table in schema_current.tables:
for constraint in tuple(table.constraints):
if constraint.type == Constraint.NOT_NULL and len(constraint.affectsColumns) == 1:
if constraint.affectsColumns[0].type.name in ("text", "char", "varchar"):
table.constraints.remove(constraint)
# Remove stored procedures which we only use on Oracle
schema_oracle.functions = []
mismatched = schema_current.compare(schema_oracle)
self.assertEqual(len(mismatched), 0, msg=", ".join(mismatched))
def test_current_oracle(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
currentSchema = sqlSchema.child("current.sql")
current_version = self.versionFromSchema(currentSchema)
currentOracleSchema = sqlSchema.child("current-oracle-dialect.sql")
current_oracle_version = self.versionFromSchema(currentOracleSchema)
self.assertEqual(current_version, current_oracle_version)
schema_current = schemaFromPath(currentSchema)
schema_oracle = schemaFromPath(currentOracleSchema)
# Remove any not null constraints in the postgres schema for text columns as in
# Oracle nclob or nvarchar never uses not null
for table in schema_current.tables:
for constraint in tuple(table.constraints):
if constraint.type == Constraint.NOT_NULL and len(constraint.affectsColumns) == 1:
if constraint.affectsColumns[0].type.name in ("text", "char", "varchar"):
table.constraints.remove(constraint)
mismatched = schema_current.compare(schema_oracle)
self.assertEqual(len(mismatched), 0, msg=", ".join(mismatched))
def databaseUpgrade(self):
"""
Do a database schema upgrade.
"""
self.log.warn("Beginning database {vers} check.",
vers=self.versionDescriptor)
# Retrieve information from schema and database
dialect, required_version, actual_version = yield self.getVersions()
if required_version == actual_version:
self.log.warn("{vers} version check complete: no upgrade needed.",
vers=self.versionDescriptor.capitalize())
if self.checkExistingSchema:
if dialect == "postgres-dialect":
expected_schema = self.schemaLocation.child("current.sql")
schema_name = "public"
else:
expected_schema = self.schemaLocation.child(
"current-oracle-dialect.sql")
schema_name = config.DatabaseConnection.user
yield self.sqlStore.checkSchema(
schemaFromPath(expected_schema), schema_name)
elif required_version < actual_version:
msg = "Actual %s version %s is more recent than the expected version %s. The service cannot be started" % (
self.versionDescriptor,
actual_version,
required_version,
)
self.log.error(msg)
raise RuntimeError(msg)
elif self.failIfUpgradeNeeded:
if self.checkExistingSchema:
expected_schema = self.schemaLocation.child("old").child(
dialect).child("v{}.sql".format(actual_version))
if dialect == "postgres-dialect":
schema_name = "public"
else:
schema_name = config.DatabaseConnection.user
yield self.sqlStore.checkSchema(
schemaFromPath(expected_schema), schema_name)
raise NotAllowedToUpgrade()
else:
self.sqlStore.setUpgrading(True)
yield self.upgradeVersion(actual_version, required_version,
dialect)
self.sqlStore.setUpgrading(False)
self.log.warn("Database {vers} check complete.",
vers=self.versionDescriptor)
returnValue(None)
def test_schema_compare(self):
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
# Test with same schema
currentSchema = schemaFromPath(sqlSchema.child("current.sql"))
duplicateSchema = schemaFromPath(sqlSchema.child("current.sql"))
mismatched = currentSchema.compare(duplicateSchema)
self.assertEqual(len(mismatched), 0)
# Test with same schema
v6Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v6.sql"))
v5Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v5.sql"))
mismatched = v6Schema.compare(v5Schema)
self.assertEqual(len(mismatched), 3, msg="\n".join(mismatched))
def test_schema_compare(self):
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
# Test with same schema
currentSchema = schemaFromPath(sqlSchema.child("current.sql"))
duplicateSchema = schemaFromPath(sqlSchema.child("current.sql"))
mismatched = currentSchema.compare(duplicateSchema)
self.assertEqual(len(mismatched), 0)
# Test with same schema
v6Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v6.sql"))
v5Schema = schemaFromPath(sqlSchema.child("old").child("postgres-dialect").child("v5.sql"))
mismatched = v6Schema.compare(v5Schema)
self.assertEqual(len(mismatched), 5, msg="\n".join(mismatched))
def test_references_index(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
schema = schemaFromPath(
getModule(__name__).filePath.parent().sibling("sql_schema").child(
"current.sql"))
# Get index details
indexed_columns = set()
for index in schema.pseudoIndexes():
indexed_columns.add("%s.%s" % (
index.table.name,
index.columns[0].name,
))
# print indexed_columns
# Look at each table
failures = []
for table in schema.tables:
for column in table.columns:
if column.references is not None:
id = "%s.%s" % (
table.name,
column.name,
)
if id not in indexed_columns:
failures.append(id)
self.assertEqual(len(failures),
0,
msg="Missing index for references columns: %s" %
(", ".join(sorted(failures))))
def test_references_index(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
schema = schemaFromPath(getModule(__name__).filePath.parent().sibling("sql_schema").child("current.sql"))
# Get index details
indexed_columns = set()
for index in schema.pseudoIndexes():
indexed_columns.add("%s.%s" % (index.table.name, index.columns[0].name))
# print indexed_columns
# Look at each table
failures = []
for table in schema.tables:
for column in table.columns:
if column.references is not None:
id = "%s.%s" % (table.name, column.name)
if id not in indexed_columns:
failures.append(id)
self.assertEqual(
len(failures), 0, msg="Missing index for references columns: %s" % (", ".join(sorted(failures)))
)
def test_primary_keys(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
schema = schemaFromPath(
getModule(__name__).filePath.parent().sibling("sql_schema").child(
"current.sql"))
# Set of tables for which missing primary key is allowed
table_exceptions = (
"ADDRESSBOOK_OBJECT_REVISIONS",
"CALENDAR_OBJECT_REVISIONS",
"NOTIFICATION_OBJECT_REVISIONS",
"PERUSER",
)
# Look at each table
failures = []
for table in schema.tables:
if table.primaryKey is None and table.name not in table_exceptions:
failures.append(table.name)
self.assertEqual(len(failures),
0,
msg="Missing primary key for tables: %s" %
(", ".join(sorted(failures))))
def test_current_oracle(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
currentSchema = sqlSchema.child("current.sql")
current_version = self.versionFromSchema(currentSchema)
currentOracleSchema = sqlSchema.child("current-oracle-dialect.sql")
current_oracle_version = self.versionFromSchema(currentOracleSchema)
self.assertEqual(current_version, current_oracle_version)
mismatched = schemaFromPath(currentSchema).compare(schemaFromPath(currentOracleSchema))
self.assertEqual(len(mismatched), 0, msg=", ".join(mismatched))
def _populateSchema(pathObj=None):
"""
Generate the global L{SchemaSyntax}.
"""
if pathObj is None:
pathObj = _schemaFiles()[0]
return SchemaSyntax(schemaFromPath(pathObj))
def test_current_oracle(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
sqlSchema = getModule(__name__).filePath.parent().sibling("sql_schema")
currentSchema = sqlSchema.child("current.sql")
current_version = self.versionFromSchema(currentSchema)
currentOracleSchema = sqlSchema.child("current-oracle-dialect.sql")
current_oracle_version = self.versionFromSchema(currentOracleSchema)
self.assertEqual(current_version, current_oracle_version)
mismatched = schemaFromPath(currentSchema).compare(schemaFromPath(currentOracleSchema))
self.assertEqual(len(mismatched), 0, msg=", ".join(mismatched))
def _populateSchema(pathObj=None):
"""
Generate the global L{SchemaSyntax}.
"""
if pathObj is None:
pathObj = _schemaFiles()[0]
return SchemaSyntax(schemaFromPath(pathObj))
def _populateSchema(version=None):
"""
Generate the global L{SchemaSyntax}.
"""
if version is None:
pathObj = getModule(__name__).filePath.sibling("sql_schema").child("current.sql")
else:
pathObj = getModule(__name__).filePath.sibling("sql_schema").child("old").child(POSTGRES_DIALECT).child("%s.sql" % (version,))
return SchemaSyntax(schemaFromPath(pathObj))
def checkSchema(dbversion, verbose=False):
"""
Compare schema in the database with the expected schema file.
"""
dbschema = dumpCurrentSchema(verbose)
# Find current schema
fp = FilePath(SCHEMADIR)
fpschema = fp.child("old").child("postgres-dialect").child("v{}.sql".format(dbversion))
if not fpschema.exists():
fpschema = fp.child("current.sql")
expectedSchema = schemaFromPath(fpschema)
mismatched = dbschema.compare(expectedSchema)
if mismatched:
print("\nCurrent schema in database is mismatched:\n\n" + "\n".join(mismatched))
else:
print("\nCurrent schema in database is a match to the expected server version")
def checkSchema(dbversion, verbose=False):
"""
Compare schema in the database with the expected schema file.
"""
dbschema = dumpCurrentSchema(verbose)
# Find current schema
fp = FilePath(SCHEMADIR)
fpschema = fp.child("old").child("postgres-dialect").child("v{}.sql".format(dbversion))
if not fpschema.exists():
fpschema = fp.child("current.sql")
expectedSchema = schemaFromPath(fpschema)
mismatched = dbschema.compare(expectedSchema)
if mismatched:
print("\nCurrent schema in database is mismatched:\n\n" + "\n".join(mismatched))
else:
print("\nCurrent schema in database is a match to the expected server version")
def test_primary_keys(self):
"""
Make sure current-oracle-dialect.sql matches current.sql
"""
schema = schemaFromPath(getModule(__name__).filePath.parent().sibling("sql_schema").child("current.sql"))
# Set of tables for which missing primary key is allowed
table_exceptions = (
"ADDRESSBOOK_OBJECT_REVISIONS",
"CALENDAR_OBJECT_REVISIONS",
"NOTIFICATION_OBJECT_REVISIONS",
"PERUSER",
)
# Look at each table
failures = []
for table in schema.tables:
if table.primaryKey is None and table.name not in table_exceptions:
failures.append(table.name)
self.assertEqual(len(failures), 0, msg="Missing primary key for tables: %s" % (", ".join(sorted(failures))))
def _dbSchemaUpgrades(self, child):
"""
This does a full DB test of all possible upgrade paths. For each old schema, it loads it into the DB
then runs the upgrade service. This ensures all the upgrade.sql files work correctly - at least for
postgres.
"""
store = yield theStoreBuilder.buildStore(
self, {"push": StubNotifierFactory()}, enableJobProcessing=False)
@inlineCallbacks
def _loadOldSchema(path):
"""
Use the postgres schema mechanism to do tests under a separate "namespace"
in postgres that we can quickly wipe clean afterwards.
"""
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("create schema test_dbUpgrades;")
yield startTxn.execSQL("set search_path to test_dbUpgrades;")
yield startTxn.execSQL(path.getContent())
yield startTxn.commit()
@inlineCallbacks
def _loadVersion():
startTxn = store.newTransaction("test_dbUpgrades")
new_version = yield startTxn.execSQL(
"select value from calendarserver where name = 'VERSION';")
yield startTxn.commit()
returnValue(int(new_version[0][0]))
@inlineCallbacks
def _loadSchemaFromDatabase():
startTxn = store.newTransaction("test_dbUpgrades")
schema = yield dumpSchema(
startTxn, "Upgraded from %s" % (child.basename(), ),
"test_dbUpgrades")
yield startTxn.commit()
returnValue(schema)
@inlineCallbacks
def _unloadOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("set search_path to public;")
yield startTxn.execSQL("drop schema test_dbUpgrades cascade;")
yield startTxn.commit()
@inlineCallbacks
def _cleanupOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("set search_path to public;")
yield startTxn.execSQL(
"drop schema if exists test_dbUpgrades cascade;")
yield startTxn.commit()
self.addCleanup(_cleanupOldSchema)
test_upgrader = UpgradeDatabaseSchemaStep(None)
expected_version = self._getSchemaVersion(
test_upgrader.schemaLocation.child("current.sql"), "VERSION")
# Upgrade allowed
upgrader = UpgradeDatabaseSchemaStep(store)
yield _loadOldSchema(child)
yield upgrader.databaseUpgrade()
new_version = yield _loadVersion()
# Compare the upgraded schema with the expected current schema
new_schema = yield _loadSchemaFromDatabase()
currentSchema = schemaFromPath(
test_upgrader.schemaLocation.child("current.sql"))
mismatched = currentSchema.compare(new_schema)
# These are special case exceptions
for i in (
"Table: CALENDAR_HOME, column name DATAVERSION default mismatch",
"Table: ADDRESSBOOK_HOME, column name DATAVERSION default mismatch",
"Table: PUSH_NOTIFICATION_WORK, column name PUSH_PRIORITY default mismatch",
):
try:
mismatched.remove(i)
except ValueError:
pass
self.assertEqual(len(mismatched), 0,
"Schema mismatch:\n" + "\n".join(mismatched))
yield _unloadOldSchema()
self.assertEqual(new_version, expected_version)
# Upgrade disallowed
upgrader = UpgradeDatabaseSchemaStep(store, failIfUpgradeNeeded=True)
yield _loadOldSchema(child)
old_version = yield _loadVersion()
try:
yield upgrader.databaseUpgrade()
except NotAllowedToUpgrade:
pass
except Exception:
self.fail("NotAllowedToUpgrade not raised")
else:
self.fail("NotAllowedToUpgrade not raised")
new_version = yield _loadVersion()
yield _unloadOldSchema()
self.assertEqual(old_version, new_version)
def _dbSchemaUpgrades(self, child):
"""
This does a full DB test of all possible upgrade paths. For each old schema, it loads it into the DB
then runs the upgrade service. This ensures all the upgrade.sql files work correctly - at least for
postgres.
"""
store = yield theStoreBuilder.buildStore(
self, {"push": StubNotifierFactory()}
)
@inlineCallbacks
def _loadOldSchema(path):
"""
Use the postgres schema mechanism to do tests under a separate "namespace"
in postgres that we can quickly wipe clean afterwards.
"""
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("create schema test_dbUpgrades;")
yield startTxn.execSQL("set search_path to test_dbUpgrades;")
yield startTxn.execSQL(path.getContent())
yield startTxn.commit()
@inlineCallbacks
def _loadVersion():
startTxn = store.newTransaction("test_dbUpgrades")
new_version = yield startTxn.execSQL("select value from calendarserver where name = 'VERSION';")
yield startTxn.commit()
returnValue(int(new_version[0][0]))
@inlineCallbacks
def _loadSchemaFromDatabase():
startTxn = store.newTransaction("test_dbUpgrades")
schema = yield dumpSchema(startTxn, "Upgraded from %s" % (child.basename(),), "test_dbUpgrades")
yield startTxn.commit()
returnValue(schema)
@inlineCallbacks
def _unloadOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("set search_path to public;")
yield startTxn.execSQL("drop schema test_dbUpgrades cascade;")
yield startTxn.commit()
@inlineCallbacks
def _cleanupOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
yield startTxn.execSQL("set search_path to public;")
yield startTxn.execSQL("drop schema if exists test_dbUpgrades cascade;")
yield startTxn.commit()
self.addCleanup(_cleanupOldSchema)
test_upgrader = UpgradeDatabaseSchemaStep(None)
expected_version = self._getSchemaVersion(test_upgrader.schemaLocation.child("current.sql"), "VERSION")
# Upgrade allowed
upgrader = UpgradeDatabaseSchemaStep(store)
yield _loadOldSchema(child)
yield upgrader.databaseUpgrade()
new_version = yield _loadVersion()
# Compare the upgraded schema with the expected current schema
new_schema = yield _loadSchemaFromDatabase()
currentSchema = schemaFromPath(test_upgrader.schemaLocation.child("current.sql"))
mismatched = currentSchema.compare(new_schema)
self.assertEqual(len(mismatched), 0, "Schema mismatch:\n" + "\n".join(mismatched))
yield _unloadOldSchema()
self.assertEqual(new_version, expected_version)
# Upgrade disallowed
upgrader = UpgradeDatabaseSchemaStep(store, failIfUpgradeNeeded=True)
yield _loadOldSchema(child)
old_version = yield _loadVersion()
try:
yield upgrader.databaseUpgrade()
except RuntimeError:
pass
except Exception:
self.fail("RuntimeError not raised")
else:
self.fail("RuntimeError not raised")
new_version = yield _loadVersion()
yield _unloadOldSchema()
self.assertEqual(old_version, new_version)
def _dbSchemaUpgrades(self, child):
"""
This does a full DB test of all possible upgrade paths. For each old schema, it loads it into the DB
then runs the upgrade service. This ensures all the upgrade.sql files work correctly - at least for
postgres.
"""
store = yield self.testStoreBuilder.buildStore(
self, {"push": StubNotifierFactory()}, enableJobProcessing=False
)
@inlineCallbacks
def _loadOldSchema(path):
"""
Use the postgres schema mechanism to do tests under a separate "namespace"
in postgres that we can quickly wipe clean afterwards.
"""
startTxn = store.newTransaction("test_dbUpgrades")
if startTxn.dialect == POSTGRES_DIALECT:
yield startTxn.execSQL("create schema test_dbUpgrades")
yield startTxn.execSQL("set search_path to test_dbUpgrades")
yield startTxn.execSQLBlock(path.getContent())
yield startTxn.commit()
@inlineCallbacks
def _loadVersion():
startTxn = store.newTransaction("test_dbUpgrades")
new_version = yield startTxn.execSQL("select value from calendarserver where name = 'VERSION'")
yield startTxn.commit()
returnValue(int(new_version[0][0]))
@inlineCallbacks
def _loadSchemaFromDatabase():
startTxn = store.newTransaction("test_dbUpgrades")
schema = yield dumpSchema(startTxn, "Upgraded from %s" % (child.basename(),), "test_dbUpgrades")
yield startTxn.commit()
returnValue(schema)
@inlineCallbacks
def _unloadOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
if startTxn.dialect == POSTGRES_DIALECT:
yield startTxn.execSQL("set search_path to public")
yield startTxn.execSQL("drop schema test_dbUpgrades cascade")
elif startTxn.dialect == ORACLE_DIALECT:
yield cleanDatabase(startTxn)
yield startTxn.commit()
@inlineCallbacks
def _cleanupOldSchema():
startTxn = store.newTransaction("test_dbUpgrades")
if startTxn.dialect == POSTGRES_DIALECT:
yield startTxn.execSQL("set search_path to public")
yield startTxn.execSQL("drop schema if exists test_dbUpgrades cascade")
elif startTxn.dialect == ORACLE_DIALECT:
yield cleanDatabase(startTxn)
yield startTxn.commit()
self.addCleanup(_cleanupOldSchema)
test_upgrader = UpgradeDatabaseSchemaStep(None)
expected_version = self._getSchemaVersion(test_upgrader.schemaLocation.child(DB_TYPE[2]), "VERSION")
# Upgrade allowed
upgrader = UpgradeDatabaseSchemaStep(store)
yield _loadOldSchema(child)
yield upgrader.databaseUpgrade()
new_version = yield _loadVersion()
# Compare the upgraded schema with the expected current schema
new_schema = yield _loadSchemaFromDatabase()
currentSchema = schemaFromPath(test_upgrader.schemaLocation.child(DB_TYPE[2]))
mismatched = currentSchema.compare(new_schema)
# These are special case exceptions
for i in (
"Table: CALENDAR_HOME, column name DATAVERSION default mismatch",
"Table: CALENDAR_HOME, mismatched constraints: set([<Constraint: (NOT NULL ('DATAVERSION',) None)>])",
"Table: ADDRESSBOOK_HOME, column name DATAVERSION default mismatch",
"Table: ADDRESSBOOK_HOME, mismatched constraints: set([<Constraint: (NOT NULL ('DATAVERSION',) None)>])",
"Table: PUSH_NOTIFICATION_WORK, column name PUSH_PRIORITY default mismatch",
):
try:
mismatched.remove(i)
except ValueError:
pass
self.assertEqual(len(mismatched), 0, "Schema mismatch:\n" + "\n".join(mismatched))
yield _unloadOldSchema()
self.assertEqual(new_version, expected_version)
# Upgrade disallowed
upgrader = UpgradeDatabaseSchemaStep(store, failIfUpgradeNeeded=True)
yield _loadOldSchema(child)
old_version = yield _loadVersion()
try:
yield upgrader.databaseUpgrade()
except NotAllowedToUpgrade:
pass
except Exception:
self.fail("NotAllowedToUpgrade not raised")
else:
self.fail("NotAllowedToUpgrade not raised")
new_version = yield _loadVersion()
yield _unloadOldSchema()
self.assertEqual(old_version, new_version)