def build_app(service):
"""Builds and returns a web app that exposes DDL and ERD.
Arguments:
service Base URL of the data models service to use.
""" # noqa
app.config['service'] = service
app.config['service_version'] = get_service_version(service)
app.config['models'] = get_template_models(service)
app.config['dialects'] = get_template_dialects()
return app
def create_erd_route(model, version):
ext = request.args.get('format') or 'png'
filename = '{0}_{1}_dms_{2}_dmsa_{3}.{4}'.format(
model, version, get_service_version(app.config['service']),
__version__, ext)
filepath = '/'.join([app.instance_path, filename])
try:
os.makedirs(app.instance_path)
except OSError:
pass
try:
erd.write(model, version, filepath, app.config['service'])
except ImportError:
return render_template('erd_500.html'), 500
return redirect(
url_for('erd_route', model=model, version=version, filename=filename))
def generate(model, model_version, dialect, tables=True, constraints=True,
indexes=True, drop=False, delete_data=False, nologging=False,
logging=False,
service='https://data-models-service.research.chop.edu/'):
"""Generate data definition language for the data model specified in the
given DBMS dialect.
Arguments:
model Model to generate DDL for.
model_version Model version to generate DDL for.
dialect DBMS dialect to generate DDL in.
tables Include tables when generating DDL.
constraints Include constraints when generating DDL.
indexes Include indexes when generating DDL.
drop Generate DDL to drop, instead of create, objects.
delete_data Generate DML to delete data from the model.
nologging Generate Oracle DDL to make objects "nologging".
logging Generate Oracle DDL to make objects "logging".
service Base URL of the data models service to use.
""" # noqa
metadata = MetaData()
model_json = get_model_json(model, model_version, service)
make_model(model_json, metadata)
service_version = get_service_version(service)
engine = create_engine(dialect + '://')
output = []
INSERT = ("INSERT INTO version_history (operation, model, model_version, "
"dms_version, dmsa_version) VALUES ('{operation}', '" +
model + "', '" + model_version + "', '" +
service_version + "', '" + __version__ + "');\n\n")
if dialect.startswith('oracle'):
INSERT = INSERT + "COMMIT;\n\n"
version_history = Table(
'version_history', MetaData(),
Column('datetime', DateTime(), primary_key=True,
server_default=text('CURRENT_TIMESTAMP')),
Column('operation', String(100)),
Column('model', String(16)),
Column('model_version', String(50)),
Column('dms_version', String(50)),
Column('dmsa_version', String(50))
)
version_tbl_ddl = str(CreateTable(version_history).
compile(dialect=engine.dialect)).strip()
if dialect.startswith('mssql'):
version_tbl_ddl = ("IF OBJECT_ID ('version_history', 'U') IS NULL " +
version_tbl_ddl + ";")
elif dialect.startswith('oracle'):
version_tbl_ddl = ("BEGIN\n" +
"EXECUTE IMMEDIATE '" + version_tbl_ddl + "';\n" +
"EXCEPTION\n" +
"WHEN OTHERS THEN\n" +
"IF SQLCODE = -955 THEN NULL;\n" +
"ELSE RAISE;\n" +
"END IF;\nEND;\n/")
else:
version_tbl_ddl = version_tbl_ddl.replace('CREATE TABLE',
'CREATE TABLE IF NOT EXISTS')
version_tbl_ddl = version_tbl_ddl + ";"
if delete_data:
output.append(INSERT.format(operation='delete data'))
tables = reversed(metadata.sorted_tables)
output.extend(delete_ddl(tables, engine))
output.insert(0, version_tbl_ddl + '\n\n')
output = ''.join(output)
return output
LOGGING = 'ALTER {type} {name} LOGGING;\n'
NOLOGGING = 'ALTER {type} {name} NOLOGGING;\n'
if tables:
if drop:
tables = reversed(metadata.sorted_tables)
output.extend(table_ddl(tables, engine, True))
output.insert(0, INSERT.format(operation='drop tables'))
elif logging and dialect.startswith('oracle'):
output.append(INSERT.format(operation='table logging'))
for table in metadata.sorted_tables:
output.append(LOGGING.format(type='TABLE', name=table.name))
output.append('\n')
elif nologging and dialect.startswith('oracle'):
output.append(INSERT.format(operation='table nologging'))
for table in metadata.sorted_tables:
output.append(NOLOGGING.format(type='TABLE', name=table.name))
output.append('\n')
else:
output.append(INSERT.format(operation='create tables'))
tables = metadata.sorted_tables
output.extend(table_ddl(tables, engine, False))
if constraints:
if drop and not dialect.startswith('sqlite'):
tables = reversed(metadata.sorted_tables)
output.insert(0, '\n')
output[0:0] = constraint_ddl(tables, engine, True)
output.insert(0, INSERT.format(operation='drop constraints'))
elif logging:
pass
elif nologging:
pass
elif not dialect.startswith('sqlite'):
output.append('\n')
output.append(INSERT.format(operation='create constraints'))
tables = metadata.sorted_tables
output.extend(constraint_ddl(tables, engine, False))
if indexes:
if drop:
tables = reversed(metadata.sorted_tables)
output.insert(0, '\n')
output[0:0] = index_ddl(tables, engine, True)
output.insert(0, INSERT.format(operation='drop indexes'))
elif logging and dialect.startswith('oracle'):
output.append(INSERT.format(operation='index logging'))
for table in metadata.sorted_tables:
output.append(LOGGING.format(type='INDEX', name=table.name))
output.append('\n')
elif nologging and dialect.startswith('oracle'):
output.append(INSERT.format(operation='index nologging'))
for table in metadata.sorted_tables:
output.append(NOLOGGING.format(type='INDEX', name=table.name))
output.append('\n')
else:
output.append('\n')
output.append(INSERT.format(operation='create indexes'))
tables = metadata.sorted_tables
output.extend(index_ddl(tables, engine, False))
output.insert(0, version_tbl_ddl + '\n\n')
output = ''.join(output)
return output
