def __init__(self):
Database.__init__(self, 'db')
self.schemaCore = Schema(self, 'core')
self.schemaGeology = Schema(self, 'geology')
self.schemaAudit = Schema(self, 'audit')
self.schemaLogic = Schema(self, 'logic')
self.createCore()
self.createGeology()
self.setupTests()
class GLTModel(Database):
def __init__(self):
Database.__init__(self, 'db')
self.schemaCore = Schema(self, 'core')
self.schemaGeology = Schema(self, 'geology')
self.schemaAudit = Schema(self, 'audit')
self.schemaLogic = Schema(self, 'logic')
self.createCore()
self.createGeology()
self.setupTests()
def createCore(self):
self.tTags = self.createTags()
self.tSiPrefixes = self.createSiPrefixes()
self.tUnits = self.createUnits()
self.tConstants = self.createConstants()
self.tSVGElements = self.createSVGElements()
self.tProjectElementTypes = self.createProjectElementTypes()
self.tProjectElements = self.createProjectElements()
def createGeology(self):
self.tRockClasses = self.createRockClasses()
self.tRocks = self.createRocks()
self.tSedimentFeatureTypes = self.createSedimentFeatureTypes()
self.tSedimentFeatures = self.createSedimentFeatures()
self.tGeologicUnitTypes = self.createGeologicUnitTypes()
self.tAgeModelElementTypes = self.createAgeModelElementTypes()
self.tAgeModelElements = self.createAgeModelElements()
self.tGrainSizeTypes = self.createGrainSizeTypes()
self.tGrainSizes = self.createGrainSizes()
self.tOutcrops = self.createOutcrops()
self.tProfileTypes = self.createProfileTypes()
self.tProfiles = self.createProfiles()
self.tProfileColumns = self.createProfileColumns()
self.tAssignedProfileColumns = self.createAssignedProfileColumns()
def createAssignedProfileColumns(self):
t = self.schemaGeology.createTable('profiles_profile_columns')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_profiles_profile_columns'))
profile_id = t.createColumn('profile_id', self.tInt, nullable=False, referencedColumn=self.tProfiles.primaryKey.firstColumn())
profile_column_id = t.createColumn('profile_column_id', self.tInt, nullable=False, referencedColumn=self.tProfileColumns.primaryKey.firstColumn())
position = t.createColumn('position', self.tInt, nullable=False, defaultValue=1)
t.createPrimaryKey([id])
t.createUniqueConstraint([profile_id, profile_column_id])
t.createUniqueConstraint([profile_id, position])
t.createCreateProcedure(self.schemaLogic, 'assign_column_to_profile', [profile_column_id, profile_id, position])
t.createUpdateProcedure(self.schemaLogic, 'update_column_in_profile', [id, profile_column_id, profile_id, position])
t.createGetAllProcedure(self.schemaLogic, 'get_all_columns_in_all_profiles', [OrderStatement(profile_id, True),
OrderStatement(profile_column_id, True),
OrderStatement(position, True)])
return t
def createProfileColumns(self):
return self.createStandardTable(self.schemaGeology, 'create_profile_columns',
createProcedureName='create_profile_column',
updateProcedureName='update_profile_column',
deleteProcedureName='delete_profile_column',
getAllProcedureName='get_all_profile_columns')
def createProfileTypes(self):
return self.createStandardTable(self.schemaGeology, 'profile_types',
createProcedureName='create_profile_type',
updateProcedureName='update_profile_type',
deleteProcedureName='delete_profile_type',
getAllProcedureName='get_all_profile_types')
def createProfiles(self):
t = self.schemaGeology.createTable('profiles')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_profiles'))
type_id = t.createColumn('profile_type_id', self.tInt, nullable=False, referencedColumn=self.tProfileTypes.primaryKey.firstColumn())
project_element_id = t.createColumn('project_element_id', self.tInt, nullable=False, referencedColumn=self.tProjectElements.primaryKey.firstColumn())
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Profile')
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([project_element_id, name])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_profile', [name, code, type_id, project_element_id, description])
t.createUpdateProcedure(self.schemaLogic, 'update_profile', [id, name, code, type_id, project_element_id, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_profile', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_profiles', [OrderStatement(name, True)])
return t
def createOutcrops(self):
t = self.schemaGeology.createTable('outcrops')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_outcrops'))
lat = t.createColumn('lat', self.tNumeric, nullable=True)
lon = t.createColumn('lon', self.tNumeric, nullable=True)
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Outcrop')
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_outcrop', [name, code, lat, lon, description])
t.createUpdateProcedure(self.schemaLogic, 'update_outcrop', [id, name, code, lat, lon, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_outcrop', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_outcrops', [OrderStatement(lat, True),
OrderStatement(lon, True)])
return t
def createProjectElements(self):
t = self.schemaCore.createTable('project_elements')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaCore.createSequence('seq_project_elements'))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True, referencedColumn=id)
type_id = t.createColumn('project_element_type_id', self.tInt, nullable=False, referencedColumn=self.tProjectElementTypes.primaryKey.firstColumn())
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Project Element')
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([parent_id, name])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_project_element', [name, type_id, parent_id, description])
t.createUpdateProcedure(self.schemaLogic, 'update_project_element', [id, name, type_id, parent_id, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_project_element', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_project_elements', [OrderStatement(name, True)])
return t
def createProjectElementTypes(self):
return self.createStandardTable(self.schemaCore, 'project_element_types',
createProcedureName='create_project_element_type',
updateProcedureName='update_project_element_type',
deleteProcedureName='delete_project_element_type',
getAllProcedureName='get_all_project_element_types')
def createGrainSizes(self):
t = self.schemaGeology.createTable('grain_sizes')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_grain_sizes'))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True, referencedColumn=id)
type_id = t.createColumn('grain_size_type_id', self.tInt, nullable=False)
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Grain Size')
code = t.createColumn('code', self.tText, nullable=True)
symbol = t.createColumn('symbol', self.tText, nullable=False)
min_size_value = t.createColumn('min_size', self.tNumeric, nullable=False, defaultValue=0.0)
min_size_unit_id = t.createColumn('min_size_unit_id', self.tInt, nullable=False, referencedColumn=self.tUnits.primaryKey.firstColumn())
max_size_value = t.createColumn('max_size', self.tNumeric, nullable=False, defaultValue=0.0)
max_size_unit_id = t.createColumn('max_size_unit_id', self.tInt, nullable=False, referencedColumn=self.tUnits.primaryKey.firstColumn())
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([type_id, name, parent_id])
t.createUniqueConstraint([parent_id, type_id])
t.createUniqueConstraint([parent_id, symbol])
t.createAuditTable(self.schemaAudit)
t.createUniqueConstraint([min_size_value, min_size_unit_id, max_size_value, max_size_unit_id, parent_id])
t.createCreateProcedure(self.schemaLogic, 'create_grain_size',
[name, code, symbol,
type_id, parent_id,
min_size_value, min_size_unit_id,
max_size_value, max_size_unit_id,
description])
t.createUpdateProcedure(self.schemaLogic, 'update_grain_size',
[id, name, code, symbol,
type_id, parent_id,
min_size_value, min_size_unit_id,
max_size_value, max_size_unit_id,
description])
t.createDeleteProcedure(self.schemaLogic, 'delete_grain_size', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_grain_sizes', [OrderStatement(name, True),
OrderStatement(min_size_value, True),
OrderStatement(min_size_unit_id, True)])
return t
def createAgeModelElements(self):
t = self.schemaGeology.createTable('age_model_elements')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_age_model_elements'))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True, referencedColumn=id)
type_id = t.createColumn('age_model_element_type_id', self.tInt, nullable=False, referencedColumn=self.tAgeModelElementTypes.primaryKey.firstColumn())
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Age Model Element')
code = t.createColumn('code', self.tText, nullable=True)
symbol = t.createColumn('symbol', self.tText, nullable=False, preventEmptyText=False, defaultText='')
begin_value = t.createColumn('begin_value', self.tNumeric, nullable=True)
end_value = t.createColumn('end_value', self.tNumeric, nullable=True)
begin_unit_id = t.createColumn('begin_unit_id', self.tInt, nullable=True, referencedColumn=self.tUnits.primaryKey.firstColumn())
end_unit_id = t.createColumn('end_unit_id', self.tInt, nullable=True, referencedColumn=self.tUnits.primaryKey.firstColumn())
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name, parent_id])
t.createUniqueConstraint([code])
t.createUniqueConstraint([symbol])
t.createUniqueConstraint([begin_value, begin_unit_id, end_value, end_unit_id])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_age_model_element',
[name, code, symbol,
type_id, parent_id,
begin_value, begin_unit_id,
end_value, end_unit_id,
description])
t.createUpdateProcedure(self.schemaLogic, 'update_age_model_element',
[id, name, code, symbol,
type_id, parent_id,
begin_value, begin_unit_id,
end_value, end_unit_id,
description])
t.createDeleteProcedure(self.schemaLogic, 'delete_age_model_element', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_age_model_elements', [OrderStatement(name, True)])
return t
def createSedimentFeatures(self):
t = self.schemaGeology.createTable('sediment_features')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_sediment_features'))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True, referencedColumn=id)
type_id = t.createColumn('sediment_feature_type_id', self.tInt, nullable=False, referencedColumn=self.tSedimentFeatureTypes.primaryKey.firstColumn())
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Name')
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name, parent_id])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_sediment_feature', [name, code, type_id, parent_id, description])
t.createUpdateProcedure(self.schemaLogic, 'update_sediment_feature', [id, name, code, type_id, parent_id, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_sediment_feature', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_sediment_features', [OrderStatement(name, True)])
return t
def createRocks(self):
t = self.schemaGeology.createTable('rocks')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaGeology.createSequence('seq_rocks'))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True)
rock_class_id = t.createColumn('rock_class_id', self.tInt, nullable=False, referencedColumn=self.tRockClasses.primaryKey.firstColumn())
name = t.createColumn('name', self.tText, nullable=False, defaultText='New Rock', preventEmptyText=True)
code = t.createColumn('code', self.tText, nullable=True)
svg_element_id = t.createColumn('svg_element_id', self.tInt, nullable=True, referencedColumn=self.tSVGElements.primaryKey.firstColumn())
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([parent_id, name])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_rock', [name, code, rock_class_id, svg_element_id, parent_id, description])
t.createUpdateProcedure(self.schemaLogic, 'update_rock', [id, name, code, rock_class_id, svg_element_id, parent_id, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_rock', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_rocks', [OrderStatement(name, True)])
def createRockClasses(self):
return self.createStandardHierarchicalTable(self.schemaGeology, 'rock_classes',
createProcedureName='create_rock_class',
updateProcedureName='update_rock_class',
deleteProcedureName='delete_rock_class',
getAllProcedureName='get_all_rock_classes')
def createSedimentFeatureTypes(self):
return self.createStandardHierarchicalTable(self.schemaGeology, 'sediment_feature_types',
createProcedureName='create_sediment_feature_type',
updateProcedureName='update_sediment_feature_type',
deleteProcedureName='delete_sediment_feature_type',
getAllProcedureName='get_all_sediment_feature_types')
def createGeologicUnitTypes(self):
return self.createStandardHierarchicalTable(self.schemaGeology, 'geologic_unit_types',
createProcedureName='create_geologic_unit_type',
updateProcedureName='update_geologic_unit_type',
deleteProcedureName='delete_geologic_unit_type',
getAllProcedureName='get_all_geologic_unit_types')
def createGrainSizeTypes(self):
return self.createStandardHierarchicalTable(self.schemaGeology, 'grain_size_types',
createProcedureName='create_grain_size_type',
updateProcedureName='update_grain_size_type',
deleteProcedureName='delete_grain_size_type',
getAllProcedureName='get_all_grain_size_types')
def createAgeModelElementTypes(self):
return self.createStandardTable(self.schemaGeology, 'age_model_element_types',
createProcedureName='create_age_model_element_type',
updateProcedureName='update_age_model_element_type',
deleteProcedureName='delete_age_model_element_type',
getAllProcedureName='get_all_age_model_element_types')
def createStandardHierarchicalTable(self, schema, name,
createProcedureName,
updateProcedureName,
deleteProcedureName,
getAllProcedureName):
t = schema.createTable(name)
id = t.createColumn('id', self.tInt, nullable=False, sequence=schema.createSequence('seq_{}'.format(name)))
parent_id = t.createColumn('parent_id', self.tInt, nullable=True, referencedColumn=id)
name = t.createColumn('name', self.tText, nullable=False, defaultText='New Entry', preventEmptyText=True)
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name, parent_id])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, createProcedureName, [name, code, parent_id, description])
t.createUpdateProcedure(self.schemaLogic, updateProcedureName, [id, name, code, parent_id, description])
t.createDeleteProcedure(self.schemaLogic, deleteProcedureName, id)
t.createGetAllProcedure(self.schemaLogic, getAllProcedureName, [OrderStatement(name, True)])
return t
def createStandardTable(self, schema, name,
createProcedureName,
updateProcedureName,
deleteProcedureName,
getAllProcedureName):
t = schema.createTable(name)
id = t.createColumn('id', self.tInt, nullable=False, sequence=schema.createSequence('seq_{}'.format(name)))
name = t.createColumn('name', self.tText, nullable=False, defaultText='New Entry', preventEmptyText=True)
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, createProcedureName, [name, code, description])
t.createUpdateProcedure(self.schemaLogic, updateProcedureName, [id, name, code, description])
t.createDeleteProcedure(self.schemaLogic, deleteProcedureName, id)
t.createGetAllProcedure(self.schemaLogic, getAllProcedureName, [OrderStatement(name, True)])
return t
def createSVGElements(self):
t = self.schemaCore.createTable('svg_elements')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaCore.createSequence('seq_svg_elements'))
name = t.createColumn('name', self.tText, nullable=False, defaultText='New SVG Element', preventEmptyText=True)
svg = t.createColumn('svg', self.tText, nullable=True)
path = t.createColumn('path', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_svg_element', [name, svg, path, description])
t.createUpdateProcedure(self.schemaLogic, 'update_svg_element', [id, name, svg, path, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_svg_element', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_svg_elements', [OrderStatement(name, True)])
return t
def createTags(self):
return self.createStandardTable(self.schemaCore, 'tags',
createProcedureName='create_tag',
updateProcedureName='update_tag',
deleteProcedureName='delete_tag',
getAllProcedureName='get_all_tags')
def createSiPrefixes(self):
t = self.schemaCore.createTable('si_prefixes')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaCore.createSequence('seq_si_prefixes'))
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New SI Prefix')
symbol = t.createColumn('symbol', self.tText, nullable=False, preventEmptyText=True, defaultText='')
code = t.createColumn('code', self.tText, nullable=True)
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
factor = t.createColumn('factor', self.tNumeric, nullable=False, preventValue=0.0)
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createUniqueConstraint([symbol])
t.createUniqueConstraint([code])
t.createUniqueConstraint([factor])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_si_prefix', [name, code, symbol, factor, description])
t.createUpdateProcedure(self.schemaLogic, 'update_si_prefix', [id, name, code, symbol, factor, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_si_prefix', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_si_prefixes', [OrderStatement(name, True)])
return t
def createUnits(self):
t = self.schemaCore.createTable('units')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaCore.createSequence('seq_units'))
base_unit_id = t.createColumn('base_unit_id', self.tInt, nullable=True, referencedColumn=id)
name = t.createColumn('name', self.tText, nullable=False, preventEmptyText=True, defaultText='New Unit')
code = t.createColumn('code', self.tText, nullable=True)
symbol = t.createColumn('symbol', self.tText, nullable=False, defaultText='Symbol')
prefix_id = t.createColumn('si_prefix_id', self.tInt, nullable=True, referencedColumn=self.tSiPrefixes.primaryKey.firstColumn())
description = t.createColumn('description', self.tText, nullable=False, defaultText='')
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createUniqueConstraint([symbol])
t.createUniqueConstraint([code])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_unit', [name, code, symbol, base_unit_id, prefix_id, description])
t.createUpdateProcedure(self.schemaLogic, 'update_unit', [id, name, code, symbol, base_unit_id, prefix_id, description])
t.createDeleteProcedure(self.schemaLogic, 'delete_unit', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_si_prefixes', [OrderStatement(name, True)])
return t
def createConstants(self):
t = self.schemaCore.createTable('constants')
id = t.createColumn('id', self.tInt, nullable=False, sequence=self.schemaCore.createSequence('seq_constants'))
name = t.createColumn('name', self.tText, nullable=False, defaultText='New Constant', preventEmptyText=True)
code = t.createColumn('code', self.tText, nullable=True)
symbol = t.createColumn('symbol', self.tText, nullable=True)
unit_id = t.createColumn('unit_id', self.tInt, nullable=True, referencedColumn=self.tUnits.primaryKey.firstColumn())
value = t.createColumn('numeric_value', self.tNumeric, nullable=False, preventValue=0)
t.createPrimaryKey([id])
t.createUniqueConstraint([name])
t.createUniqueConstraint([code])
t.createUniqueConstraint([symbol])
t.createAuditTable(self.schemaAudit)
t.createCreateProcedure(self.schemaLogic, 'create_constant', [name, code, symbol, value, unit_id])
t.createUpdateProcedure(self.schemaLogic, 'update_constant', [id, name, code, symbol, value, unit_id])
t.createDeleteProcedure(self.schemaLogic, 'delete_constant', id)
t.createGetAllProcedure(self.schemaLogic, 'get_all_constants', [OrderStatement(name, True)])
return t
def setupTests(self):
self.addTest("SELECT * FROM LOGIC.CREATE_TAG('foo', NULL, 'bar');")
self.addTest("SELECT * FROM LOGIC.UPDATE_TAG(1, 'foobar', NULL, 'barfo');")
self.addTest("SELECT * FROM LOGIC.DELETE_TAG(1);")
self.addTest("SELECT LOGIC.GET_ALL_TAGS('all_tags');")
self.addTest("FETCH ALL IN ALL_TAGS;")
self.addTest("CLOSE ALL_TAGS;")
