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 downgrade(pyramid_env):
with context.begin_transaction():
if not using_virtuoso():
op.alter_column('user', 'preferred_email', type_=sa.String(50))
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')
