def main(): parser = argparse.ArgumentParser( description="Manage database bootstrap, backup and update.") parser.add_argument("configuration", help="configuration file") parser.add_argument("command", help="command", choices=['bootstrap', 'backup', 'alembic']) parser.add_argument('alembic_args', nargs='*') args = parser.parse_args() settings = get_appsettings(args.configuration, 'assembl') set_config(settings) configure_zmq(settings['changes.socket'], False) engine = configure_engine(settings, True) admin_engine = engine from assembl.lib.sqla import using_virtuoso if using_virtuoso(): admin_engine = create_engine_sqla('virtuoso://*****:*****@VOSU') else: admin_engine = engine if args.command == "bootstrap": SessionMaker = sessionmaker(admin_engine) session = SessionMaker() if using_virtuoso() and not session.execute( "select count(*) from db..sys_users" " where u_name = '%(db_user)s'" % settings).scalar(): for i in init_instructions: session.execute(i % settings) session.commit() db = bootstrap_db(args.configuration) bootstrap_db_data(db) mark_changed() transaction.commit() elif args.command == "backup": admin_engine.execute("backup_context_clear()") filename_prefix = 'assembl-virtuoso-backup' # % time.strftime('%Y%m%d%H%M%S') #virtuoso will add this suffix to the filename no matter what we do virtuoso_suffix = "1.bp" # Unfortunately adding , 3600, vector('"+os.getcwd()+"') typically # doesn't work as forbidden by default virtuoso configuration. admin_engine.execute("backup_online('" + filename_prefix + "', 524288)") sys.stdout.write(filename_prefix + virtuoso_suffix + '\n') elif args.command == "alembic": context = MigrationContext.configure(engine.connect()) db_version = context.get_current_revision() if not db_version: sys.stderr.write('Database not initialized.\n' 'Try this: "assembl-db-manage %s bootstrap"\n' % args.configuration) sys.exit(2) cmd = ['alembic', '-c', args.configuration] + args.alembic_args print(subprocess.check_output(cmd))
def main(): parser = argparse.ArgumentParser(description="Manage database bootstrap, backup and update.") parser.add_argument("configuration", help="configuration file") parser.add_argument("command", help="command", choices=['bootstrap', 'backup', 'alembic']) parser.add_argument('alembic_args', nargs='*') args = parser.parse_args() settings = get_appsettings(args.configuration, 'assembl') set_config(settings) configure_zmq(settings['changes.socket'], False) engine = configure_engine(settings, True) admin_engine = engine from assembl.lib.sqla import using_virtuoso if using_virtuoso(): admin_engine = create_engine_sqla('virtuoso://*****:*****@VOSU') else: admin_engine = engine if args.command == "bootstrap": SessionMaker = sessionmaker(admin_engine) session = SessionMaker() if using_virtuoso() and not session.execute( "select count(*) from db..sys_users" " where u_name = '%(db_user)s'" % settings).scalar(): for i in init_instructions: session.execute(i % settings) session.commit() db = bootstrap_db(args.configuration) bootstrap_db_data(db) mark_changed() transaction.commit() elif args.command == "backup": admin_engine.execute("backup_context_clear()") filename_prefix = 'assembl-virtuoso-backup' # % time.strftime('%Y%m%d%H%M%S') #virtuoso will add this suffix to the filename no matter what we do virtuoso_suffix = "1.bp" # Unfortunately adding , 3600, vector('"+os.getcwd()+"') typically # doesn't work as forbidden by default virtuoso configuration. admin_engine.execute("backup_online('"+filename_prefix+"', 524288)") sys.stdout.write(filename_prefix+virtuoso_suffix+'\n') elif args.command == "alembic": context = MigrationContext.configure(engine.connect()) db_version = context.get_current_revision() if not db_version: sys.stderr.write('Database not initialized.\n' 'Try this: "assembl-db-manage %s bootstrap"\n' % args.configuration) sys.exit(2) cmd = ['alembic', '-c', args.configuration] + args.alembic_args print(subprocess.check_output(cmd))
def reset_semantic_mapping(): from assembl.lib.sqla import using_virtuoso if using_virtuoso(): from .virtuoso_mapping import AssemblQuadStorageManager aqsm = AssemblQuadStorageManager() aqsm.drop_all() aqsm.update_all_storages()
def downgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): op.create_table('social_auth_account_temp', sa.Column('id', sa.Integer, primary_key=True), sa.Column('username', sa.String(200))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute("""INSERT INTO social_auth_account_temp SELECT id, username FROM social_auth_account WHERE username IS NOT NULL""") mark_changed() with context.begin_transaction(): op.drop_column('social_auth_account', 'username') op.add_column( 'social_auth_account', sa.Column('username', sa.String(200))) with transaction.manager: db.execute("""UPDATE social_auth_account SET username = ( SELECT username FROM social_auth_account_temp WHERE social_auth_account_temp.id = social_auth_account.id)""") mark_changed() with context.begin_transaction(): op.drop_table('social_auth_account_temp') else: with context.begin_transaction(): op.alter_column('social_auth_account', 'username', type_=sa.Unicode(200))
def downgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): op.create_table('social_auth_account_temp', sa.Column('id', sa.Integer, primary_key=True), sa.Column('username', sa.String(200))) # Do stuff with the app's models here. from assembl import models as m db = m.get_session_maker()() with transaction.manager: db.execute("""INSERT INTO social_auth_account_temp SELECT id, username FROM social_auth_account WHERE username IS NOT NULL""") mark_changed() with context.begin_transaction(): op.drop_column('social_auth_account', 'username') op.add_column('social_auth_account', sa.Column('username', sa.String(200))) with transaction.manager: db.execute("""UPDATE social_auth_account SET username = ( SELECT username FROM social_auth_account_temp WHERE social_auth_account_temp.id = social_auth_account.id)""" ) mark_changed() with context.begin_transaction(): op.drop_table('social_auth_account_temp') else: with context.begin_transaction(): op.alter_column('social_auth_account', 'username', type_=sa.Unicode(200))
def downgrade(pyramid_env): if not using_virtuoso(): with context.begin_transaction(): op.drop_index( 'ix_public_abstract_agent_account_email_ci', 'abstract_agent_account') op.create_index( 'ix_public_abstract_agent_account_email', 'abstract_agent_account', ['email'], unique=False)
def downgrade(pyramid_env): if using_virtuoso(): # Virtuoso implicitly indexes foreign keys return schema = config.get("db_schema") with context.begin_transaction(): for index in foreign_keys: table, column = index.split(".") op.drop_index(index_name(schema, table, column), table)
def downgrade(pyramid_env): if not using_virtuoso(): return full_schema = '.'.join((config.get('db_schema'), config.get('db_user'))) with context.begin_transaction(): for seqname in history_sequences: op.execute( "SELECT sequence_set('{0}', sequence_set('{1}.{0}', 0, 1), 0)" .format(seqname, full_schema))
def downgrade(pyramid_env): if using_virtuoso(): # Virtuoso implicitly indexes foreign keys return schema = config.get("db_schema") with context.begin_transaction(): for index in foreign_keys: table, column = index.split(".") op.drop_index( index_name(schema, table, column), table)
def drop_tables(app_settings, session): log.info('Dropping all tables.') if not using_virtuoso(): # postgres. Thank you to # http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it session.close() session.execute("""SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid()""" % (app_settings.get("db_database"))) try: for row in get_all_tables(app_settings, session): log.debug("Dropping table: %s" % row) session.execute("drop table \"%s\"" % row) mark_changed() except: raise Exception('Error dropping tables: %s' % (sys.exc_info()[1]))
def drop_tables(app_settings, session): log.info('Dropping all tables.') if not using_virtuoso(): # postgres. Thank you to # http://stackoverflow.com/questions/5408156/how-to-drop-a-postgresql-database-if-there-are-active-connections-to-it session.close() session.execute( """SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = '%s' AND pid <> pg_backend_pid()""" % ( app_settings.get("db_database"))) try: for row in get_all_tables(app_settings, session): log.debug("Dropping table: %s" % row) session.execute("drop table \"%s\"" % row) mark_changed() except: raise Exception('Error dropping tables: %s' % ( sys.exc_info()[1]))
def downgrade(pyramid_env): with context.begin_transaction(): if not using_virtuoso(): op.alter_column('user', 'preferred_email', type_=sa.String(50))
def schema_prefix(): if using_virtuoso(): return "_".join([ config.get('db_schema'), config.get('db_user')]) else: return config.get('db_schema')
def upgrade_semantic_mapping(): from assembl.lib.sqla import using_virtuoso if using_virtuoso(): from .virtuoso_mapping import AppQuadStorageManager aqsm = AppQuadStorageManager() aqsm.update_all_storages()
def upgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): # Add the type column to Document op.create_table( 'tmp_document', sa.Column('id', sa.Integer, primary_key=True), sa.Column('uri_id', URLString(1024)), sa.Column('creation_date', sa.DateTime, nullable=False, default=datetime.utcnow), sa.Column( 'discussion_id', sa.Integer, sa.ForeignKey('discussion.id', ondelete="CASCADE", onupdate="CASCADE"), nullable=False, index=False, ), sa.Column('oembed_type', sa.String(1024), server_default=""), sa.Column('mime_type', sa.String(1024), server_default=""), sa.Column('title', sa.Unicode(), server_default=""), sa.Column('description', sa.UnicodeText), sa.Column('author_name', sa.Unicode()), sa.Column('author_url', URLString()), sa.Column('thumbnail_url', URLString()), sa.Column('site_name', sa.Unicode()), sa.Column('type', sa.String(100))) op.execute("""INSERT INTO tmp_document (id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type") SELECT id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, 'document' from document""" ) op.drop_constraint("attachment_document_document_id_id", "attachment") with context.begin_transaction(): op.execute("DELETE from document") with context.begin_transaction(): op.add_column('document', sa.Column('type', sa.String(60), nullable=False)) with context.begin_transaction(): op.execute("""INSERT INTO document (id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type") SELECT id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type" FROM tmp_document""") with context.begin_transaction(): op.drop_table('tmp_document') op.execute("""ALTER TABLE "{schema}"."{user}"."attachment" ADD CONSTRAINT "attachment_document_document_id_id" FOREIGN KEY ("document_id") REFERENCES "{schema}"."{user}"."document" ("id") ON UPDATE CASCADE ON DELETE CASCADE""".format( schema=config.get('db_schema'), user=config.get('db_user'))) else: with context.begin_transaction(): op.add_column("document", sa.Column('type', sa.String(60)), config.get("db_schema")) op.execute("""UPDATE document SET "type"='document'""") op.alter_column("document", "type", nullable=False) with context.begin_transaction(): op.create_table( 'file', sa.Column('id', sa.Integer, sa.ForeignKey('document.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('data', sa.LargeBinary, nullable=False))
def upgrade(pyramid_env): if using_virtuoso(): with context.begin_transaction(): # Add the type column to Document op.create_table( 'tmp_document', sa.Column('id', sa.Integer, primary_key=True), sa.Column('uri_id', URLString(1024)), sa.Column('creation_date', sa.DateTime, nullable=False, default=datetime.utcnow), sa.Column('discussion_id', sa.Integer, sa.ForeignKey( 'discussion.id', ondelete="CASCADE", onupdate="CASCADE"), nullable=False, index=False,), sa.Column('oembed_type', sa.String(1024), server_default=""), sa.Column('mime_type', sa.String(1024), server_default=""), sa.Column('title', sa.Unicode(), server_default=""), sa.Column('description', sa.UnicodeText), sa.Column('author_name', sa.Unicode()), sa.Column('author_url', URLString()), sa.Column('thumbnail_url', URLString()), sa.Column('site_name', sa.Unicode()), sa.Column('type', sa.String(100)) ) op.execute("""INSERT INTO tmp_document (id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type") SELECT id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, 'document' from document""") op.drop_constraint("attachment_document_document_id_id", "attachment") with context.begin_transaction(): op.execute("DELETE from document") with context.begin_transaction(): op.add_column('document', sa.Column('type', sa.String(60), nullable=False)) with context.begin_transaction(): op.execute("""INSERT INTO document (id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type") SELECT id, uri_id, creation_date, discussion_id, oembed_type, mime_type, title, description, author_name, author_url, thumbnail_url, site_name, "type" FROM tmp_document""") with context.begin_transaction(): op.drop_table('tmp_document') op.execute( """ALTER TABLE "{schema}"."{user}"."attachment" ADD CONSTRAINT "attachment_document_document_id_id" FOREIGN KEY ("document_id") REFERENCES "{schema}"."{user}"."document" ("id") ON UPDATE CASCADE ON DELETE CASCADE""".format( schema=config.get('db_schema'), user=config.get('db_user'))) else: with context.begin_transaction(): op.add_column( "document", sa.Column('type', sa.String(60)), config.get("db_schema")) op.execute("""UPDATE document SET "type"='document'""") op.alter_column("document", "type", nullable=False) with context.begin_transaction(): op.create_table( 'file', sa.Column('id', sa.Integer, sa.ForeignKey( 'document.id', onupdate='CASCADE', ondelete='CASCADE'), primary_key=True), sa.Column('data', sa.LargeBinary, nullable=False) )
def schema_prefix(): if using_virtuoso(): return "_".join([config.get('db_schema'), config.get('db_user')]) else: return config.get('db_schema')