def rebuild_schema(schema, schema_owner, save_users, 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 --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() app.logger.info('Loading default data into database') load_data('ooiui_schema_data.sql') 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: new_user = User() new_user.id = sresult.id new_user.user_id = sresult.user_id new_user.pass_hash = sresult.pass_hash new_user.email = sresult.email new_user.user_name = sresult.user_name new_user.active = sresult.active new_user.confirmed_at = sresult.confirmed_at new_user.first_name = sresult.first_name new_user.last_name = sresult.last_name new_user.phone_primary = sresult.phone_primary new_user.phone_alternate = sresult.phone_alternate new_user.role = sresult.role new_user.organization_id = sresult.organization_id db.session.add(new_user) db.engine.execute("SELECT nextval('ooiui.users_id_seq')") db.session.commit() 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: 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() # db.engine.execute('INSERT INTO {0}.users SELECT * FROM {0}_{1}.users'.format(schema, timestamp)) # db.engine.execute('INSERT INTO {0}.user_scope_link SELECT * FROM {0}_{1}.user_scope_link'.format(schema, timestamp)) else: app.logger.info('Adding the default admin user') if admin_username is None: app.logger.info('Admin username set to: admin') admin_username = '******' if admin_password is None: app.logger.info('Admin password set to: password') admin_password = '******' if first_name is None: app.logger.info('Admin first_name set to: Default') first_name = 'Default' if last_name is None: app.logger.info('Admin last_name set to: Admin') last_name = 'Admin' if email is None: app.logger.info('Admin email set to: [email protected]') email = '*****@*****.**' if org_name is None: app.logger.info('Admin org_name set to: Rutgers') org_name = 'Rutgers' add_admin_user(username=admin_username, password=admin_password, first_name=first_name, last_name=last_name, email=email, org_name=org_name) load_data(sql_file='ooiui_schema_data_notifications.sql') app.logger.info('Database reloaded successfully')
def rebuild_schema(schema, schema_owner, save_users, 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 --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() app.logger.info('Loading default data into database') load_data('ooiui_schema_data.sql') 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: new_user = User() new_user.id = sresult.id new_user.user_id = sresult.user_id new_user.pass_hash = sresult.pass_hash new_user.email = sresult.email new_user.user_name = sresult.user_name new_user.active = sresult.active new_user.confirmed_at = sresult.confirmed_at new_user.first_name = sresult.first_name new_user.last_name = sresult.last_name new_user.phone_primary = sresult.phone_primary new_user.phone_alternate = sresult.phone_alternate new_user.role = sresult.role new_user.organization_id = sresult.organization_id db.session.add(new_user) db.engine.execute("SELECT nextval('ooiui.users_id_seq')") db.session.commit() 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: 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() # db.engine.execute('INSERT INTO {0}.users SELECT * FROM {0}_{1}.users'.format(schema, timestamp)) # db.engine.execute('INSERT INTO {0}.user_scope_link SELECT * FROM {0}_{1}.user_scope_link'.format(schema, timestamp)) else: app.logger.info('Adding the default admin user') if admin_username is None: app.logger.info('Admin username set to: admin') admin_username = '******' if admin_password is None: app.logger.info('Admin password set to: password') admin_password = '******' if first_name is None: app.logger.info('Admin first_name set to: Default') first_name = 'Default' if last_name is None: app.logger.info('Admin last_name set to: Admin') last_name = 'Admin' if email is None: app.logger.info('Admin email set to: [email protected]') email = '*****@*****.**' if org_name is None: app.logger.info('Admin org_name set to: Rutgers') org_name = 'Rutgers' add_admin_user(username=admin_username, password=admin_password, first_name=first_name, last_name=last_name, email=email, org_name=org_name) load_data(sql_file='ooiui_schema_data_notifications.sql') app.logger.info('Database reloaded successfully')
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))