def rebuild_schema(schema, schema_owner, save_users, save_disabled_streams, admin_username, admin_password, first_name, last_name, email, org_name):
"""
Creates the OOI UI Services schema based on models.py
:usage: python manage.py rebuild_schema --schema ooiui --schema_owner postgres --save_users False --save_disabled_streams True --admin_username admin --admin_password password --first_name Default --last_name Admin --email [email protected] --org_name Rutgers
:param schema:
:param schema_owner:
:return:
"""
# Check if schema exists
timestamp = int((datetime.utcnow() - datetime(1970, 1, 1)).total_seconds())
sql = "SELECT schema_name FROM information_schema.schemata WHERE schema_name = '{0}'".format(schema)
sql_result = db.engine.execute(sql).first()
if sql_result != None:
# Move current schema to _timestamp
app.logger.info('Backing up schema container {0} to {0}_{1}'.format(schema, timestamp))
db.engine.execute('ALTER SCHEMA {0} RENAME TO {0}_{1}'.format(schema, timestamp))
app.logger.info('Creating schema container: {0}'.format(schema))
db.engine.execute('CREATE SCHEMA IF NOT EXISTS {0} AUTHORIZATION {1}'.format(schema, schema_owner))
app.logger.info('Building schema objects')
db.create_all()
app.logger.info('Adding base user_scopes')
UserScope.insert_scopes()
db.session.commit()
app.logger.info('Loading default data into database')
load_data('ooiui_schema_data.sql')
db.session.commit()
app.logger.info('Loading params data into database')
load_data(sql_file='ooiui_params_streams_data.sql')
db.session.commit()
# app.logger.info('Loading new vocab data into database')
# load_data(sql_file='ooiui_vocab.sql')
db.session.commit()
if save_disabled_streams == 'True':
app.logger.info('Re-populating disabledstreams table from backup schema')
ds_sql = 'SELECT * FROM {0}_{1}.disabledstreams'.format(schema, timestamp)
sql_result = db.engine.execute(ds_sql)
fa = sql_result.fetchall()
for sresult in fa:
ds_record = DisabledStreams()
ds_record.id = sresult.id
ds_record.ref_des = getattr(sresult, 'ref_des', '')
ds_record.stream_name = getattr(sresult, 'stream_name', '')
ds_record.disabled_by = getattr(sresult, 'disabled_by', '')
ds_record.timestamp = getattr(sresult, 'timestamp', '')
db.session.add(ds_record)
db.engine.execute("SELECT nextval('ooiui.disabledstreams_id_seq')")
db.session.commit()
if save_users == 'True':
app.logger.info('Re-populating users from backup schema')
users_sql = 'SELECT * FROM {0}_{1}.users'.format(schema, timestamp)
sql_result = db.engine.execute(users_sql)
fa = sql_result.fetchall()
for sresult in fa:
try:
new_user = User()
new_user.id = sresult.id
new_user.user_id = getattr(sresult, 'user_id', '')
if hasattr(sresult, 'pass_hash'):
new_user._password = getattr(sresult, 'pass_hash', '')
else:
new_user._password = getattr(sresult, '_password', '')
new_user.email = getattr(sresult, 'email', '')
new_user.user_name = getattr(sresult, 'user_name', '')
new_user.active = getattr(sresult, 'active', '')
new_user.confirmed_at = getattr(sresult, 'confirmed_at', '')
new_user.first_name = getattr(sresult, 'first_name', '')
new_user.last_name = getattr(sresult, 'last_name', '')
new_user.phone_primary = getattr(sresult, 'phone_primary', '')
new_user.phone_alternate = getattr(sresult, 'phone_alternate', '')
new_user.role = getattr(sresult, 'role', '')
new_user.email_opt_in = getattr(sresult, 'email_opt_in', '')
new_user.organization_id = getattr(sresult, 'organization_id', '')
new_user.other_organization = getattr(sresult, 'other_organization', '')
new_user.vocation = getattr(sresult, 'vocation', '')
new_user.country = getattr(sresult, 'country', '')
new_user.state = getattr(sresult, 'state', '')
db.session.add(new_user)
db.engine.execute("SELECT nextval('ooiui.users_id_seq')")
db.session.commit()
except sqlalchemy.exc.IntegrityError, exc:
app.logger.info('Failure: rebuild_schema failed: ')
reason = exc.message
app.logger.info('Cause: ' + reason)
app.logger.info('Restoring to previous version')
app.logger.info('Restoring schema container {0}_{1} to {0}'.format(schema, timestamp))
db.engine.execute('ALTER SCHEMA {0} RENAME TO {0}_{1}_failed'.format(schema, timestamp))
db.engine.execute('ALTER SCHEMA {0}_{1} RENAME TO {0}'.format(schema, timestamp))
user_scope_link_sql = 'SELECT * FROM {0}_{1}.user_scope_link'.format(schema, timestamp)
sql_resultc = db.engine.execute(user_scope_link_sql)
fac = sql_resultc.fetchall()
for scresult in fac:
try:
new_user_scope_link = UserScopeLink()
new_user_scope_link.id = scresult.id
new_user_scope_link.user_id = scresult.user_id
new_user_scope_link.scope_id = scresult.scope_id
db.session.add(new_user_scope_link)
db.engine.execute("SELECT nextval('ooiui.user_scope_link_id_seq')")
db.session.commit()
except sqlalchemy.exc.IntegrityError, exc:
app.logger.info('Failure: rebuild_schema failed: ')
reason = exc.message
app.logger.info('Cause: ' + reason)
app.logger.info('Restoring to previous version')
app.logger.info('Restoring schema container {0}_{1} to {0}'.format(schema, timestamp))
db.engine.execute('ALTER SCHEMA {0} RENAME TO {0}_{1}_failed'.format(schema, timestamp))
db.engine.execute('ALTER SCHEMA {0}_{1} RENAME TO {0}'.format(schema, timestamp))
