def _config_init(script_path: str, dsn: str) -> Config:
log.info("Alembic config initialization", script_location=script_path)
alembic_cfg = Config()
alembic_cfg.set_main_option("script_location", script_path)
alembic_cfg.set_main_option("sqlalchemy.url", dsn)
filename_format = (
"%%(year)d_%%(month).2d_%%(day).2d_"
"%%(hour).2d%%(minute).2d%%(second).2d_%%(slug)s"
)
alembic_cfg.set_main_option("file_template", filename_format)
alembic_cfg.set_section_option("post_write_hooks", "hooks", "black isort")
alembic_cfg.set_section_option(
"post_write_hooks", "black.type", "console_scripts"
)
alembic_cfg.set_section_option(
"post_write_hooks", "black.entrypoint", "black"
)
alembic_cfg.set_section_option(
"post_write_hooks", "isort.type", "console_scripts"
)
alembic_cfg.set_section_option(
"post_write_hooks", "isort.entrypoint", "isort"
)
return alembic_cfg
def db(request, monkeypatch):
"""Session-wide test database."""
if os.path.exists(TESTDB_PATH):
os.unlink(TESTDB_PATH)
from wordbook.infra import config
from wordbook.infra import db
monkeypatch.setattr(config, 'SQLALCHEMY_ENGINE', 'sqlite:///' + TESTDB_PATH)
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
_engine = create_engine(
'sqlite:///' + TESTDB_PATH,
echo=config.SQLALCHEMY_ECHO,
echo_pool=config.SQLALCHEMY_ECHO_POOL)
db_session = sessionmaker(bind=_engine)
def get_session():
return db_session()
monkeypatch.setattr(db, 'get_session', get_session)
alembic_config = Config('alembic.ini')
alembic_config.set_section_option('alembic', 'sqlalchemy.url', 'sqlite:///' + TESTDB_PATH)
def teardown():
downgrade(alembic_config, '18554c40c9e')
os.unlink(TESTDB_PATH)
upgrade(alembic_config, 'head')
request.addfinalizer(teardown)
return
def alembic_config(self):
alembic_cfg = Config()
alembic_cfg.set_section_option(
'alembic', 'script_location', 'alembic')
alembic_cfg.set_section_option(
'alembic', 'sqlalchemy.url', str(self.db.engine.url))
return alembic_cfg
def run_alembic_migration(self):
""" Migrate to latest Alembic revision if not up-to-date. """
def migrate_if_required(rev, context):
rev = script.get_revision(rev)
if not (rev and rev.is_head):
migration_required = True
return []
migration_required = False
config = Config(os.path.join(os.path.dirname(__file__), 'alembic.ini'))
config.set_section_option('alembic', 'script_location',
os.path.join(os.path.dirname(__file__), 'migrations'))
config.set_main_option('sqlalchemy.url',
'sqlite:///' + self.filepath)
script = ScriptDirectory.from_config(config)
with EnvironmentContext(config, script, fn=migrate_if_required):
script.run_env()
if migration_required:
logging.info('Migrating SQLite database to latest revision')
alembic.command.upgrade(config, 'head')
else:
logging.info('SQLite database is on the latest revision')
def _get_alembic_config(self):
config = Config(os.path.join(os.path.dirname(__file__), 'alembic.ini'))
config.set_section_option(
'alembic', 'script_location',
os.path.join(os.path.dirname(__file__), 'migrations'))
config.set_main_option('sqlalchemy.url', self.connect_string)
return config
def run_alembic_migration(self):
""" Migrate to latest Alembic revision if not up-to-date. """
def migrate_if_required(rev, context):
rev = script.get_revision(rev)
if not (rev and rev.is_head):
migration_required = True
return []
migration_required = False
config = Config(os.path.join(os.path.dirname(__file__), 'alembic.ini'))
config.set_section_option(
'alembic', 'script_location',
os.path.join(os.path.dirname(__file__), 'migrations'))
config.set_main_option('sqlalchemy.url', 'sqlite:///' + self.filepath)
script = ScriptDirectory.from_config(config)
with EnvironmentContext(config, script, fn=migrate_if_required):
script.run_env()
if migration_required:
logging.info('Migrating SQLite database to latest revision')
alembic.command.upgrade(config, 'head')
else:
logging.info('SQLite database is on the latest revision')
def _get_alembic_config():
from alembic.config import Config
from gam.settings import DATABASE_URL
config_file = path.join(path.dirname(path.abspath(__file__)),
'alembic.ini')
config = Config(config_file)
config.set_section_option('alembic', 'sqlalchemy.url', DATABASE_URL)
return config
def setup(ctx, force):
# TODO: change this
p = Path(bcpc_build.__file__)
pkg_dir = p.parent.parent
config_file = pkg_dir.joinpath('alembic.ini').as_posix()
# TODO(kamidzi): check for file
alembic_cfg = Config(config_file)
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url', config.db.url)
command.upgrade(alembic_cfg, 'head')
def clean_db(reset_db, ckan_config):
reset_db()
alembic_ini = _resolve_alembic_config("spc")
alembic_cfg = Config(alembic_ini)
alembic_cfg.set_section_option("alembic", "sqlalchemy.url",
ckan_config.get("sqlalchemy.url"))
command.upgrade(alembic_cfg, "head")
def setup_tables(cls, engine):
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option("alembic", "script_location", "alembic")
alembic_cfg.set_section_option("alembic", "sqlalchemy.url", str(engine.url))
command.stamp(alembic_cfg, "head")
trans.commit()
def setup_tables(cls, engine):
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option('alembic', 'script_location',
'alembic')
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
str(engine.url))
command.stamp(alembic_cfg, "head")
trans.commit()
def create_session(database_uri: str, is_test=False) -> Session:
if database_uri.startswith("sqlite"):
engine = create_engine(database_uri,
echo=False,
connect_args={"check_same_thread": False})
else:
# Force mysql driver to be utf8mb4
if database_uri.startswith(
"mysql") and "charset=utf8mb4" not in database_uri:
database_uri += "?charset=utf8mb4"
engine = create_engine(
database_uri,
echo=False,
pool_pre_ping=True,
pool_recycle=600,
pool_size=10,
pool_use_lifo=True,
poolclass=QueuePool,
)
alembic_cfg = Config(
str((Path(__file__).parent.parent.parent / "alembic.ini").absolute()))
# Hot-patch migration location if in test environment
if is_test:
alembic_cfg.set_section_option(
"alembic", "script_location",
str(Path(__file__).parent / "migrations"))
# Create tables if accounts does not exist
if not engine.dialect.has_table(engine, "accounts"):
models.Base.metadata.create_all(engine)
command.stamp(alembic_cfg, "head")
if not is_test:
# Check if there are any pending migrations
with engine.begin() as con:
script_dir = script.ScriptDirectory.from_config(alembic_cfg)
ctx = migration.MigrationContext.configure(con)
assert set(ctx.get_current_heads()) == set(
script_dir.get_heads()
), 'There are pending migrations, run them via "alembic upgrade heads"'
# Bind engine and create session
models.Base.metadata.bind = engine
session: Session = sessionmaker(bind=engine)()
return session
def get_alembic_settings(config_uri):
""" Get alembic settings from the config file.
:param config_uri: an .ini config file
:return: an ``alembic.config.Config`` object
"""
# get setting from the pyramid config file
settings = get_appsettings(config_uri)
# set alembic settings
alembic_cfg = Config(config_uri)
alembic_cfg.set_section_option(
'alembic',
'sqlalchemy.url',
settings['sqlalchemy.url'])
return alembic_cfg
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
options = parse_vars(argv[2:])
setup_logging(config_uri)
settings = load_full_settings(config_uri, options=options)
engine = engine_from_config(settings, 'sqlalchemy.')
with engine.begin() as connection:
initdb(connection, drop_all='--force' in options)
# generate the Alembic version table and stamp it with the latest revision
alembic_cfg = Config('alembic.ini')
alembic_cfg.set_section_option(
'alembic', 'sqlalchemy.url', engine.url.__str__())
command.stamp(alembic_cfg, 'head')
def ensure_database(dsn=TEST_DB_DSN):
"""Ensure that a clean database is available for tests."""
engine = sqlalchemy.create_engine(dsn)
path_to_alembic = os.path.abspath(
os.path.join(os.getcwd(), 'templates/alembic.ini'))
output_buffer = io.BytesIO()
alembic_cfg = Config(path_to_alembic, output_buffer=output_buffer)
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url', dsn)
alembic_cfg.set_section_option('loggers', 'keys', '')
with engine.begin() as connection:
alembic_cfg.attributes['connection'] = connection
logging.disable(logging.ERROR)
command.upgrade(alembic_cfg, 'head')
logging.disable(logging.NOTSET)
for table in reversed(METADATA.sorted_tables):
connection.execute(table.delete())
engine.dispose()
def main(argv=sys.argv):
if len(argv) < 2:
usage(argv)
config_uri = argv[1]
options = parse_vars(argv[2:])
setup_logging(config_uri)
settings = load_full_settings(config_uri, options=options)
engine = engine_from_config(settings, 'sqlalchemy.')
with engine.begin() as connection:
initdb(connection, drop_all='--force' in options)
# generate the Alembic version table and stamp it with the latest revision
alembic_cfg = Config('alembic.ini')
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
engine.url.__str__())
command.stamp(alembic_cfg, 'head')
def create_config(app):
"""Programmatically create Alembic config"""
cfg = Config(stdout=StringIO())
cfg.get_template_directory = get_template_directory
migrations = app.get("migrations_dir") or os.path.join(
app["cwd"], "migrations")
cfg.set_main_option("script_location", migrations)
cfg.config_file_name = os.path.join(migrations, "alembic.ini")
db = app["db"]
cfg.set_section_option("default", "sqlalchemy.url",
str(db.sync_engine.url))
# put database in main options
cfg.set_main_option("databases", "default")
# create empty logging section to avoid raising errors in env.py
cfg.set_section_option("logging", "path", "")
cfg.metadata = dict(default=db.metadata)
return cfg
def setup_engine(cls):
cls.db_master = _make_db()
cls.db_slave = _make_db()
engine = cls.db_master.engine
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option('alembic',
'script_location',
'alembic')
alembic_cfg.set_section_option('alembic',
'sqlalchemy.url',
str(engine.url))
command.stamp(alembic_cfg, "head")
trans.commit()
def create_db_with_alembic(app, request):
# bind the app the database instance
_db.app = app
alembic_config = AlembicConfig(_project_root / 'alembic.ini')
# override settings & run migrations
alembic_config.set_section_option('alembic', 'sqlalchemy.url',
app.config['SQLALCHEMY_DATABASE_URI'])
alembic.command.upgrade(alembic_config, 'head')
def teardown():
_db.session.remove()
_db.drop_all()
# drop alembic's revisions table, so next test can re-run these
# migrations
_db.session.execute('drop table if exists alembic_version')
request.addfinalizer(teardown)
return _db
def setup_tables(cls, engine):
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option(
'alembic', 'script_location', 'alembic')
alembic_cfg.set_section_option(
'alembic', 'sqlalchemy.url', str(engine.url))
alembic_cfg.set_section_option(
'alembic', 'sourceless', 'true')
command.stamp(alembic_cfg, 'head')
# always add a test API key
conn.execute(ApiKey.__table__.delete())
key1 = ApiKey.__table__.insert().values(
valid_key='test', allow_fallback=False, allow_locate=True)
conn.execute(key1)
key2 = ApiKey.__table__.insert().values(
valid_key='export', allow_fallback=False, allow_locate=False)
conn.execute(key2)
trans.commit()
def setup_tables(cls, engine):
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option('alembic', 'script_location',
'alembic')
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
str(engine.url))
alembic_cfg.set_section_option('alembic', 'sourceless', 'true')
command.stamp(alembic_cfg, 'head')
# always add a test API key
conn.execute(ApiKey.__table__.delete())
key1 = ApiKey.__table__.insert().values(valid_key='test',
shortname='test',
allow_fallback=False,
allow_locate=True,
log_locate=True,
log_region=True,
log_submit=True)
conn.execute(key1)
key2 = ApiKey.__table__.insert().values(valid_key='export',
shortname='export',
allow_fallback=False,
allow_locate=False,
log_locate=False,
log_region=False,
log_submit=False)
conn.execute(key2)
trans.commit()
def setup_tables(cls, engine):
with engine.connect() as conn:
trans = conn.begin()
_Model.metadata.create_all(engine)
# Now stamp the latest alembic version
alembic_cfg = Config()
alembic_cfg.set_section_option('alembic', 'script_location',
'alembic')
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
str(engine.url))
alembic_cfg.set_section_option('alembic', 'sourceless', 'true')
command.stamp(alembic_cfg, 'head')
# always add a test API key
conn.execute(ApiKey.__table__.delete())
key1 = ApiKey.__table__.insert().values(
valid_key='test',
allow_fallback=False,
allow_locate=True,
fallback_name='fall',
fallback_url='http://127.0.0.1:9/?api',
fallback_ratelimit=10,
fallback_ratelimit_interval=60,
fallback_cache_expire=60,
)
conn.execute(key1)
key2 = ApiKey.__table__.insert().values(valid_key='export',
allow_fallback=False,
allow_locate=False)
conn.execute(key2)
trans.commit()
def alembic_config(self):
config = join(self.alembic_path, 'alembic.ini')
versions = join(self.alembic_path, 'versions')
script_location = self.alembic_path
alembic_cfg = Config(config)
alembic_cfg.set_section_option('alembic', 'version_locations',
versions)
alembic_cfg.set_section_option('alembic', 'script_location',
script_location)
alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
str(self.engine.url))
return alembic_cfg
LOCAL_DATABASE = "test"
app.secret_key = "ME@deepgully"
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///%s.db" % LOCAL_DATABASE
# app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://*****:*****@127.0.0.1:3306/%s'%LOCAL_DATABASE
elif RUNTIME_ENV in ("gae", "gae_dev"):
app.secret_key = "ME@deepgully+GAE"
if RUNTIME_ENV in ("bae", "local"):
from alembic.config import Config
MIGRATE_CFG = Config("alembic.ini")
MIGRATE_CFG.set_section_option("alembic", "sqlalchemy.url", app.config["SQLALCHEMY_DATABASE_URI"])
app.config["MIGRATE_CFG"] = MIGRATE_CFG
app.config["SiteTitle"] = "boolog"
app.config["SiteSubTitle"] = ""
app.config["OwnerEmail"] = "*****@*****.**"
app.config["DefaultPassword"] = "******"
######################################
## User
######################################
from flask.ext.login import LoginManager
login_manager = LoginManager()
def get_config(self):
'''
Programmatically create Alembic config. To determine databases,
DATASTORE from project's config file is used. To customize Alembic
use MIGRATIONS in you config file.
Example:
MIGRATIONS = {
'alembic': {
'script_location': '<path>',
'databases': '<db_name1>,<db_name2>',
},
'<db_name1>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'<bd_name2>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'logging': {
'path': '<path_to_logging_config>',
}
}
For more information about possible options, please visit Alembic
documentation:
https://alembic.readthedocs.org/en/latest/index.html
'''
from alembic.config import Config
# Because we are using custom template, we need to change default
# implementation of get_template_directory() to make it pointing
# to template stored in lux, we need to do this hack
Config.get_template_directory = self.get_lux_template_directory
# put migrations in main project dir
migration_dir = os.path.join(self.app.meta.path, 'migrations')
# set default settings in case where MIGRATIONS is not set
alembic_cfg = Config()
# where to place alembic env
alembic_cfg.set_main_option('script_location', migration_dir)
# get database(s) name(s) and location(s)
odm = self.app.odm()
databases = []
# set section for each found database
for name, engine in odm.keys_engines():
if not name:
name = 'default'
databases.append(name)
# url = str(engine.url).replace('+green', '')
url = str(engine.url)
alembic_cfg.set_section_option(name, 'sqlalchemy.url', url)
# put databases in main options
alembic_cfg.set_main_option("databases", ','.join(databases))
# create empty logging section to avoid raising errors in env.py
alembic_cfg.set_section_option('logging', 'path', '')
# obtain the metadata required for `auto` command
self.get_metadata(alembic_cfg)
# get rest of settings from project config. This may overwrite
# already existing options (especially if different migration dir
# is provided)
cfg = self.app.config.get('MIGRATIONS')
if cfg:
for section in cfg.keys():
for key, value in cfg[section].items():
if section == 'alembic':
alembic_cfg.set_main_option(key, value)
else:
alembic_cfg.set_section_option(section, key, value)
return alembic_cfg
DB_DDL_URI = os.environ.get('DB_DDL_URI')
if not DB_RO_URI:
DB_RO_URI = ('mysql+%s://%s:%s@%s:%s/%s' % (
DB_LIBRARY, DB_RO_USER, DB_RO_PWD, DB_RO_HOST, DB_PORT, DB_NAME))
if not DB_RW_URI:
DB_RW_URI = ('mysql+%s://%s:%s@%s:%s/%s' % (
DB_LIBRARY, DB_RW_USER, DB_RW_PWD, DB_RW_HOST, DB_PORT, DB_NAME))
if not DB_DDL_URI:
DB_DDL_URI = ('mysql+%s://%s:%s@%s:%s/%s' % (
DB_LIBRARY, DB_DDL_USER, DB_DDL_PWD, DB_RW_HOST, DB_PORT, DB_NAME))
ALEMBIC_CFG = AlembicConfig()
ALEMBIC_CFG.set_section_option(
'alembic', 'script_location', os.path.join(HERE, 'alembic'))
ALEMBIC_CFG.set_section_option(
'alembic', 'sqlalchemy.url', DB_DDL_URI)
GEOIP_PATH = os.environ.get('GEOIP_PATH')
if not GEOIP_PATH:
GEOIP_PATH = os.path.join(HERE, 'tests/data/GeoIP2-City-Test.mmdb')
MAP_TOKEN = os.environ.get('MAP_TOKEN')
REDIS_HOST = os.environ.get('REDIS_HOST')
REDIS_PORT = os.environ.get('REDIS_PORT', '6379')
REDIS_DB = '1' if TESTING else '0'
REDIS_URI = os.environ.get('REDIS_URI')
if REDIS_HOST and not REDIS_URI:
REDIS_URI = 'redis://%s:%s/%s' % (REDIS_HOST, REDIS_PORT, REDIS_DB)
else:
print 'alembic_version drop successfully.'
# delete `migration` dir if exists.
root_path = get_root_path()
migration_dirname = os.path.join(root_path, 'migration')
try:
shutil.rmtree(migration_dirname)
except Exception:
print 'migration dir drop failed.'
else:
print 'migration dir drop successfully.'
# config
alembic_config = Config(os.path.join(root_path, 'alembic.ini'))
alembic_config.set_section_option('alembic', 'sqlalchemy.url',
app_config.SQL_SERVER_URL)
# init env
command.init(alembic_config, migration_dirname)
# copy migration_env to replace migration/env.py Good.
migration_env_abspath = os.path.abspath(
os.path.join(root_path, 'lib/serve/migration_env.py'))
to_env_abspath = os.path.abspath(os.path.join(root_path, 'migration/env.py'))
print migration_env_abspath, to_env_abspath
shutil.copyfile(migration_env_abspath, to_env_abspath)
#
command.revision(alembic_config, autogenerate=True)
command.upgrade(alembic_config, "head")
print '[Migrate] finish!\n'
class SPCCommand(CkanCommand):
"""
ckanext-spc management commands.
Usage::
paster spc [command]
Commands::
db-upgrade Upgrade database to the state of the latest migration
fix-missed-licenses
drop-mendeley-publications
broken_links_report
spc_find_duplicate
...
"""
summary = __doc__.split('\n')[0]
usage = __doc__
parser = paste.script.command.Command.standard_parser(verbose=True)
parser.add_option(
'-c',
'--config',
dest='config',
default='../spc.ini',
help='Config file to use.'
)
parser.add_option(
'-d',
'--delay',
type=int,
default=1,
help='Delay between pushes to datastore.'
)
parser.add_option(
'-f',
'--formats',
type=str,
default='xls,csv,xlsx',
help='Delay between pushes to datastore.'
)
def command(self):
self._load_config()
model.Session.commit()
alembic_ini = os.path.normpath(
os.path.join(__file__, '../../../alembic.ini')
)
self.alembic_cfg = Config(alembic_ini)
self.alembic_cfg.set_section_option(
'alembic', 'sqlalchemy.url', config.get('sqlalchemy.url')
)
cmd_name = (self.args[0] if self.args else '').replace('-', '_')
cmd = getattr(self, cmd_name, None)
if cmd is None:
return self.usage
return cmd()
def db_upgrade(self):
command.upgrade(self.alembic_cfg, 'head')
return 'Success'
def db_downgrade(self):
command.downgrade(self.alembic_cfg, 'base')
return 'Success'
def create_datastore(self):
resources = model.Session.query(model.Resource)
step = 20
user = tk.get_action('get_site_user')({'ignore_auth': True})
for offset in range(0, resources.count(), step):
for res in resources.offset(offset).limit(step):
if res.extras.get('datastore_active'):
continue
if not res.format or res.format.lower() not in self.options.formats.split(','):
continue
print('Pushing <{}> into datastore'.format(res.id))
tk.get_action('datastore_create')(
{'ignore_auth': True, 'user': user['name']},
{'resource_id': res.id, 'force': True}
)
tk.get_action('datapusher_submit')(
{'ignore_auth': True, 'user': user['name']},
{'resource_id': res.id, 'force': True}
)
sleep(self.options.delay)
return 'Success'
def fix_missed_licenses(self):
q = model.Session.query(model.Package).filter(
model.Package.license_id.is_(None)
| (model.Package.license_id == '')
)
ids = [pkg.id for pkg in q]
if not ids:
print('There are no packages with missed license_id')
return
broken_count = q.update({
'license_id': 'notspecified'
},
synchronize_session=False)
model.Session.commit()
print('{} packages were updated:'.format(broken_count))
for id in ids:
search.rebuild(id)
print('\t' + id)
def update_topic_names(self):
pairs = (
('"Social"', '"Gender and Youth"'),
('"Statistics"', '"Official Statistics"'),
)
model.repo.new_revision()
for old_name, new_name in pairs:
items = model.Session.query(
model.PackageExtra
).filter_by(key='thematic_area_string').filter(
model.PackageExtra.value != '[]',
~model.PackageExtra.value.is_(None),
model.PackageExtra.value.contains(old_name)
)
for item in items:
item.value = item.value.replace(old_name, new_name)
model.repo.commit_and_remove()
def create_country_orgs(self):
site_user = tk.get_action('get_site_user')({'ignore_auth': True}, {})
for name, title in country_orgs.items():
if model.Session.query(model.Group).filter_by(name=name + '-data'
).count():
continue
tk.get_action('organization_create')({
'ignore_auth': True,
'user': site_user['name']
}, {
'name': name + '-data',
'title': title
})
def drop_mendeley_publications(self):
while True:
results = tk.get_action('package_search')(
None, {
'q': 'harvest_source:MENDELEY',
'rows': 100
}
)
if not results['count']:
break
print('{} packages left'.format(results['count']))
for pkg in results['results']:
package = model.Package.get(pkg['id'])
if package:
package.purge()
print('\tPurged package <{}>'.format(pkg['id']))
model.Session.commit()
print('Done')
def broken_links_report(self):
jobs.enqueue(broken_links_report, timeout=7200)
def fix_harvester_duplications(self):
# paster spc fix_harvester_duplications 'SOURCE_TYPE_TO_DROP' -c /etc/ckan/default/production.ini
# paster spc fix_harvester_duplications 'SPREP' -c /etc/ckan/default/production.ini
# Prepare HarvestObject to have munged ids for search
formatted_harvest_objects = model.Session.query(
harvest_model.HarvestObject,
_func.replace(_func.replace(harvest_model.HarvestObject.guid, ':', '-'), '.', '-').label('possible_id')
).subquery()
# Find packages with duplications
subquery_packages = model.Session.query(
model.Package.id.label('pkg_id'),
harvest_model.HarvestSource.id.label('hs_id'),
harvest_model.HarvestSource.type.label('hs_type'),
)\
.distinct()\
.join(
formatted_harvest_objects,
_or_(
formatted_harvest_objects.c.guid == model.Package.id,
formatted_harvest_objects.c.possible_id == model.Package.id,
formatted_harvest_objects.c.package_id == model.Package.id
)
).join(
harvest_model.HarvestSource,
formatted_harvest_objects.c.harvest_source_id == harvest_model.HarvestSource.id
).group_by(
model.Package.id,
formatted_harvest_objects.c.id,
harvest_model.HarvestSource.id
).subquery()
subquery_count = model.Session.query(
subquery_packages.c.pkg_id.label('pkg_id'),
_func.count(subquery_packages.c.pkg_id).label('hs_count'),
_func.array_agg(subquery_packages.c.hs_id).label('hs_ids'),
_func.array_agg(subquery_packages.c.hs_type).label('hs_types')
).group_by(subquery_packages.c.pkg_id).subquery()
q = model.Session.query(
subquery_count.c.pkg_id,
subquery_count.c.hs_ids,
subquery_count.c.hs_types)\
.filter(subquery_count.c.hs_count > 1)
res = q.all()
package_ids = []
harvest_sources_types = []
harvest_sources_ids = []
for pkg_id, hs_ids, hs_types in res:
package_ids.append(pkg_id)
harvest_sources_types.extend(x for x in hs_types if x not in harvest_sources_types)
harvest_sources_ids.extend(x for x in hs_ids if x not in harvest_sources_ids)
try:
source_type_to_drop = self.args[1]
if len(res) == 0 or len(harvest_sources_types) < 2:
raise ValueError
except IndexError:
print('Source type to drop is not defined')
print('paster spc fix_harvester_duplications \'SOURCE_TYPE_TO_DROP\' -c /etc/ckan/default/production.ini')
return
except ValueError:
print('No duplications found')
return
print('{} duplications found'.format(len(res)))
print('Duplications found for source types: {}'.format(', '.join(harvest_sources_types)))
print('Harvest Sources IDs: {}'.format(', '.join(harvest_sources_ids)))
# Filter by Source
harvest_objects_ids = model.Session.query(formatted_harvest_objects.c.id)\
.join(
harvest_model.HarvestSource,
harvest_model.HarvestSource.id == formatted_harvest_objects.c.harvest_source_id
).filter(harvest_model.HarvestSource.type == source_type_to_drop)\
.join(
model.Package,
_or_(
model.Package.id == formatted_harvest_objects.c.guid,
model.Package.id == formatted_harvest_objects.c.possible_id,
model.Package.id == formatted_harvest_objects.c.package_id
)
).filter(model.Package.id.in_(package_ids)).all()
# Delete Harvest Object Errors
if harvest_objects_ids:
model.Session.query(harvest_model.HarvestObjectError)\
.filter(harvest_model.HarvestObjectError.harvest_object_id.in_(harvest_objects_ids))\
.delete(synchronize_session='fetch')
# Delete Harvest Objects
model.Session.query(harvest_model.HarvestObject)\
.filter(harvest_model.HarvestObject.id.in_(harvest_objects_ids))\
.delete(synchronize_session='fetch')
model.Session.commit()
print 'Done'
def update_dataset_coordinates(self):
# EXAMPLE: paster spc update_dataset_coordinates 'COORDINATES_NEW' 'FIND_WITH_CURRENT' -c /etc/ckan/default/production.ini
if self.args[1] and self.args[2]:
new_coordinates = self.args[1]
find_with_current = self.args[2]
q = model.Session.query(model.PackageExtra)\
.filter(model.PackageExtra.key == 'spatial')
updated_items = []
ds_list = [ds_extra for ds_extra in q.all() if find_with_current in ds_extra.value]
if len(ds_list):
print('There are items that match, will start to update')
for ds in ds_list:
q = model.Session.query(model.PackageExtra)\
.filter(model.PackageExtra.id == ds.id)
if q:
q.update({
'value': new_coordinates
})
updated_items.append(ds.package_id)
model.Session.commit()
print('{0} items been updated. Here is the list of IDs:{1}'.format(
len(updated_items), updated_items))
else:
print('No items match found.')
else:
print('Please provide two arguments.')
def spc_user_deletion(self):
if self.args and len(self.args) == 2:
file = self.args[1]
site_user = logic.get_action(u'get_site_user')({u'ignore_auth': True}, {})
context = {u'user': site_user[u'name']}
with open(file) as csvfile:
read = csv.reader(csvfile)
for row in read:
usr_id = row[0]
user = model.User.get(usr_id)
if user:
print("Removing {0} user".format(user.name))
tk.get_action(u'user_delete')(context, {u'id': usr_id})
else:
print('User with ID "{0}" no exstis on the portal. Skipping...'.format(usr_id))
print('User deletion finished.')
else:
print('Please provide path to the CSV file.')
def spc_find_detached_datasets(self):
"""
You must provide the creator_user_id
Use a comma as a separator to provide multiple ids
"""
if self.args and len(self.args) == 2:
creator_user_ids = self.args[1].split(',')
else:
error('Please, provide only the creator_user_id(s)')
# check if the file is already exist
# ask for rewrite confirmation
if os.path.isfile('detached_datasets.csv'):
print('File detached_datasets.csv is already exist.')
confirm = query_yes_no('Do you want to rewrite it?:', default='no')
if confirm == 'no':
error('Command aborted by user')
return
obj = harvest_model.HarvestObject
pkg = model.Package
# getting all packages that have no related harvest_object
subquery = model.Session.query(obj.package_id).distinct().subquery()
detached_pkgs = model.Session.query(pkg) \
.outerjoin(subquery, pkg.id == subquery.c.package_id) \
.filter(pkg.creator_user_id.in_(creator_user_ids),
subquery.c.package_id.is_(None)) \
.filter(pkg.type != 'harvest')
pkg_list = detached_pkgs.all()
if not pkg_list:
print('There is no detached datasets at all')
return
print('{} detached datasets found'.format(len(pkg_list)))
print('Creating report .csv file')
# generating report with detached datasets
try:
self._generate_detached_report(pkg_list)
except csv.Error as e:
error('Failed. An error occured during writing report: {}'.format(e))
print('DONE')
def _generate_detached_report(self, pkg_list):
filename = 'detached_datasets.csv'
fieldnames = (
'id',
'title',
'creator_username',
'org_name',
'metadata_created',
'metadata_modified'
)
with open(filename, 'w') as file:
writer = csv.DictWriter(file, fieldnames=fieldnames, delimiter=';')
writer.writeheader()
for pkg in pkg_list:
creator = model.Session.query(model.User).get(pkg.creator_user_id)
org = model.Session.query(model.Group).get(pkg.owner_org)
writer.writerow(
{
'id': pkg.id,
'title': pkg.title.strip().encode('utf8'),
'creator_username': creator.name,
'org_name': org.name,
'metadata_created': pkg.metadata_created,
'metadata_modified': pkg.metadata_modified
}
)
print('{} file created'.format(filename))
def spc_del_datasets_from_list(self):
"""
Purge all datasets from provided list of IDs
"""
if self.args and len(self.args) == 2:
file = self.args[1]
site_user = logic.get_action(u'get_site_user')(
{u'ignore_auth': True}, {})
else:
error('Please, provide only a file path to CSV file with package IDs.')
# read all package ids from file
with open(file) as file:
file = csv.reader(file, quoting=csv.QUOTE_NONE)
next(file) # skip headers
pkg_ids = {pkg[0].split(';')[0] for pkg in file}
print('Are you sure you want to purge {} packages?'.format(len(pkg_ids)))
print('This action is irreversible!')
confirm = query_yes_no('Do you want to proceed?:', default='no')
if confirm == 'no':
error('Command aborted by user')
return
for idx, pkg_id in enumerate(pkg_ids, start=1):
print('Purging packages in progress: {}/{}'.format(idx, len(pkg_ids)))
try:
context = {u'user': site_user[u'name']}
tk.get_action('dataset_purge')(context, {'id': pkg_id})
print('Package {} was purged'.format(pkg_id))
except logic.NotFound:
print('Dataset with id {} wasn\'t found. Skipping...'.format(pkg_id))
print('DONE')
elif RUNTIME_ENV in ("local", ):
LOCAL_DATABASE = "chenyangblog"
app.secret_key = "xxxxxx"
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///%s.db' % LOCAL_DATABASE
#app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://*****:*****@127.0.0.1:3306/%s' % LOCAL_DATABASE
elif RUNTIME_ENV in ("gae", "gae_dev"):
app.secret_key = "xxxxxx+GAE"
if RUNTIME_ENV in ("bae", "sae", "local"):
from alembic.config import Config
MIGRATE_CFG = Config(os.path.join(BASE_DIR, "alembic.ini"))
MIGRATE_CFG.set_section_option("alembic", "sqlalchemy.url",
app.config['SQLALCHEMY_DATABASE_URI'])
app.config['MIGRATE_CFG'] = MIGRATE_CFG
######################################
# User
######################################
from flask.ext.login import LoginManager
login_manager = LoginManager()
login_manager.init_app(app)
#####################################
# Mail
#####################################
if RUNTIME_ENV in ("bae", "sae"):
pass
class SPCCommand(CkanCommand):
"""
ckanext-spc management commands.
Usage::
paster spc [command]
Commands::
db-upgrade Upgrade database to the state of the latest migration
fix-missed-licenses
drop-mendeley-publications
...
"""
summary = __doc__.split('\n')[0]
usage = __doc__
parser = paste.script.command.Command.standard_parser(verbose=True)
parser.add_option('-c',
'--config',
dest='config',
default='../spc.ini',
help='Config file to use.')
def command(self):
self._load_config()
model.Session.commit()
alembic_ini = os.path.normpath(
os.path.join(__file__, '../../../alembic.ini'))
self.alembic_cfg = Config(alembic_ini)
self.alembic_cfg.set_section_option('alembic', 'sqlalchemy.url',
config.get('sqlalchemy.url'))
cmd_name = (self.args[0] if self.args else '').replace('-', '_')
cmd = getattr(self, cmd_name, None)
if cmd is None:
return self.usage
return cmd()
def db_upgrade(self):
command.upgrade(self.alembic_cfg, 'head')
return 'Success'
def db_downgrade(self):
command.downgrade(self.alembic_cfg, 'base')
return 'Success'
def fix_missed_licenses(self):
q = model.Session.query(model.Package).filter(
model.Package.license_id.is_(None)
| (model.Package.license_id == ''))
ids = [pkg.id for pkg in q]
if not ids:
print('There are no packages with missed license_id')
return
broken_count = q.update({'license_id': 'notspecified'},
synchronize_session=False)
model.Session.commit()
print('{} packages were updated:'.format(broken_count))
for id in ids:
search.rebuild(id)
print('\t' + id)
def create_country_orgs(self):
site_user = tk.get_action('get_site_user')({'ignore_auth': True}, {})
for name, title in country_orgs.items():
if model.Session.query(model.Group).filter_by(name=name +
'-data').count():
continue
tk.get_action('organization_create')({
'ignore_auth': True,
'user': site_user['name']
}, {
'name': name + '-data',
'title': title
})
def drop_mendeley_publications(self):
while True:
results = tk.get_action('package_search')(None, {
'q': 'harvest_source:MENDELEY',
'rows': 100
})
if not results['count']:
break
print('{} packages left'.format(results['count']))
for pkg in results['results']:
package = model.Package.get(pkg['id'])
if package:
package.purge()
print('\tPurged package <{}>'.format(pkg['id']))
model.Session.commit()
print('Done')
},
'Bhutan': {
'city': False,
'region_code': 'BT',
'region_name': 'Bhutan',
'ip': '67.43.156.1',
'latitude': 27.5,
'longitude': 90.5,
'radius': GEOCODER.region_max_radius('BT'),
'region_radius': GEOCODER.region_max_radius('BT'),
'score': 0.9,
},
}
ALEMBIC_CFG = AlembicConfig()
ALEMBIC_CFG.set_section_option(
'alembic', 'script_location', 'alembic')
ALEMBIC_CFG.set_section_option(
'alembic', 'sqlalchemy.url', SQLURI)
ALEMBIC_CFG.set_section_option(
'alembic', 'sourceless', 'true')
@pytest.yield_fixture(scope='session', autouse=True)
def package():
# We do this here as early as possible in tests.
# We only do it in tests, as the real celery processes should
# run without the monkey patches applied. The gunicorn arbiter
# patches its worker processes itself.
from gevent import monkey
monkey.patch_all()
from alembic.config import Config
from flask.ext.assets import ManageAssets
from call_server.app import create_app
from call_server.extensions import assets, db, cache
from call_server import political_data
from call_server.user import User, USER_ADMIN, USER_ACTIVE
app = create_app()
app.db = db
manager = Manager(app)
alembic_config = Config(
os.path.realpath(os.path.dirname(__name__)) + "/alembic.ini")
# let the config override the default db location in production
alembic_config.set_section_option('alembic', 'sqlalchemy.url',
app.config.get('SQLALCHEMY_DATABASE_URI'))
manager.add_command("assets", ManageAssets())
def reset_assets():
"""Reset assets named bundles to {} before running command.
This command should really be run with TestingConfig context"""
print "resetting assets"
assets._named_bundles = {}
@manager.command
def runserver(external=None):
"""Run web server for local development and debugging
pass --external for external routing"""
def create_db(params):
params = process_connection_values(params)
db_name = params["database"]
username = params["username"]
params["username"] = MASTER_USER
params["password"] = MASTER_PASSWORD
master_params = params.copy()
master_params["database"] = MASTER_DB
engine = connect(master_params)
engine.execute('COMMIT')
sql = 'CREATE DATABASE "{}";'.format(db_name)
try:
engine.execute(sql)
except Exception as e:
print sql, e
# TODO: This will only run for the first time and fail in all other cases.
# Maybe test before instead?
sql = 'CREATE ROLE {user} LOGIN PASSWORD "{user}" VALID UNTIL "infinity";'.format(
user=username)
try:
engine.execute(sql)
except Exception as e:
pass
engine = connect(params)
# TODO: Alembic (and sqlalchemy for that matter) don't like schemas. We should
# figure out a way to add these later
config = load_flask_config()
models = config.get("models", [])
if not models:
raise Exception("This app has no models defined in config")
for model_module_name in models:
log.info("Building models from %s", model_module_name)
models = importlib.import_module(model_module_name)
models.ModelBase.metadata.create_all(engine)
# stamp the db with the latest alembic upgrade version
from drift.flaskfactory import _find_app_root
approot = _find_app_root()
ini_path = os.path.join(approot, "alembic.ini")
alembic_cfg = Config(ini_path)
script_path = os.path.join(
os.path.split(os.path.abspath(ini_path))[0], "alembic")
alembic_cfg.set_main_option("script_location", script_path)
db_names = alembic_cfg.get_main_option('databases')
connection_string = format_connection_string(params)
alembic_cfg.set_section_option(db_names, "sqlalchemy.url",
connection_string)
command.stamp(alembic_cfg, "head")
for schema in SCHEMAS:
# Note that this does not automatically grant on tables added later
sql = '''
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "{schema}" TO {user};
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA "{schema}" TO {user};
GRANT ALL ON SCHEMA "{schema}" TO {user};'''.format(
schema=schema, user=username)
try:
engine.execute(sql)
except Exception as e:
print sql, e
return db_name
def _get_alembic_config(self):
config = Config(os.path.join(os.path.dirname(__file__), 'alembic.ini'))
config.set_section_option('alembic', 'script_location',
os.path.join(os.path.dirname(__file__), 'migrations'))
config.set_main_option('sqlalchemy.url', self.connect_string)
return config
def alembic_setup(logger):
logger.info("")
config.init_modules()
usable_modules = config.config_data['papercup']['modules_by_name']
alembic_modules = ['papercup']
alembic_versions = [
os.path.join(os.path.dirname(__file__), 'migrations', 'versions')
]
for m in config.config_data['papercup']['loaded_modules']:
if hasattr(usable_modules[m], 'alembic'):
alembic_modules.append(m)
alembic_versions.append(usable_modules[m].alembic['versions'])
from alembic.config import Config
alembic_config = Config()
alembic_config.set_main_option(
'script_location', os.path.join(os.path.dirname(__file__),
'migrations'))
alembic_config.set_main_option('version_locations',
' '.join(alembic_versions))
alembic_config.set_section_option('loggers', 'keys',
'root,sqlalchemy,alembic')
alembic_config.set_section_option('handlers', 'keys', 'console')
alembic_config.set_section_option('formatters', 'keys', 'generic')
alembic_config.set_section_option('logger_root', 'level', 'WARN')
alembic_config.set_section_option('logger_root', 'handlers', 'console')
alembic_config.set_section_option('logger_root', 'qualname', '')
alembic_config.set_section_option('logger_sqlalchemy', 'level', 'WARN')
alembic_config.set_section_option('logger_sqlalchemy', 'handlers', '')
alembic_config.set_section_option('logger_sqlalchemy', 'qualname',
'sqlalchemy.engine')
alembic_config.set_section_option('logger_alembic', 'level', 'INFO')
alembic_config.set_section_option('logger_alembic', 'handlers', '')
alembic_config.set_section_option('logger_alembic', 'qualname', 'alembic')
alembic_config.set_section_option('handler_console', 'class',
'StreamHandler')
alembic_config.set_section_option('handler_console', 'args',
'(sys.stderr,)')
alembic_config.set_section_option('handler_console', 'level', 'NOTSET')
alembic_config.set_section_option('handler_console', 'formatter',
'generic')
alembic_config.set_section_option(
'formatter_generic', 'format',
'%%(levelname)-5.5s [%%(name)s] %%(message)s')
alembic_config.set_section_option('formatter_generic', 'datefmt',
'%%H:%%M:%%S')
#alembic_cfg.attributes['connection'] = connection
return (usable_modules, alembic_modules, alembic_config)
def main():
setup_logging('development.ini')
settings = get_appsettings('development.ini')
load_local_settings(settings)
engine = engine_from_config(settings, 'sqlalchemy.')
DBSession.configure(bind=engine)
translation_manager.options.update({
'locales': settings['available_languages'].split(),
'get_locale_fallback': True
})
configure_mappers()
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
# then, load the Alembic configuration and generate the
# version table, "stamping" it with the most recent rev:
from alembic.config import Config
from alembic import command
alembic_cfg = Config("alembic.ini")
url = settings['sqlalchemy.url']
alembic_cfg.set_section_option("alembic", "sqlalchemy.url", url)
command.stamp(alembic_cfg, "head")
with transaction.manager:
geometry = '{"type":"Polygon","coordinates":[[[85.31038284301758,27.70731518595052],[85.31089782714842,27.698120147680104],[85.3242015838623,27.69842412827061],[85.323429107666,27.70731518595052],[85.31038284301758,27.70731518595052]]]}' # noqa
geometry = geojson.loads(geometry,
object_hook=geojson.GeoJSON.to_instance)
geometry = shapely.geometry.asShape(geometry)
geometry = shape.from_shape(geometry, 4326)
area = Area(
geometry
)
DBSession.add(area)
license1 = License()
license1.name = 'NextView'
license1.description = "This data is licensed for use by the US Government (USG) under the NextView (NV) license and copyrighted by Digital Globe or GeoEye. The NV license allows the USG to share the imagery and Literal Imagery Derived Products (LIDP) with entities outside the USG when that entity is working directly with the USG, for the USG, or in a manner that is directly beneficial to the USG. The party receiving the data can only use the imagery or LIDP for the original purpose or only as otherwise agreed to by the USG. The party receiving the data cannot share the imagery or LIDP with a third party without express permission from the USG. At no time should this imagery or LIDP be used for other than USG-related purposes and must not be used for commercial gain. The copyright information should be maintained at all times. Your acceptance of these license terms is implied by your use." # noqa
license1.plain_text = "In other words, you may only use NextView imagery linked from this site for digitizing OpenStreetMap data for humanitarian purposes." # noqa
DBSession.add(license1)
license2 = License()
license2.name = 'Astrium/UNOSAT'
license2.description = "UNOSAT allow any INTERNET USER to use the IMAGE to develop DERIVATIVE WORKS provided that the INTERNET USER includes the DERIVATIVE WORKS he/she created in the OpenStreetMap database under CC-BY-SA licence (http://creativecommons.org/licenses/by-sa/2.0/) and/or Open Database licence (ODbL: http://www.opendatacommons.org/licenses/odbl/), with the credit of the corresponding PRODUCT conspicuously displayed and written in full, in order to allow any OpenStreetMap database user to have access to and to use the DERIVATIVE WORKS. Except for the foregoing, the END USER and/or the INTERNET USER shall not be entitled to sell, distribute, assign, dispose of, lease, sublicense or transfer, directly or indirectly, any DERIVATIVE WORKS to any third party." # noqa
license2.plain_text = "Astrium GEO-Information Services and UNOSAT are allowing access to this imagery for creating information in OpenStreetMap. Other uses are not allowed." # noqa
DBSession.add(license2)
project = Project(
'Kathmandu - Map all primary roads and buildings'
)
project.area = area
project.short_description = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua." # noqa
project.description = "Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur. Excepteur sint occaecat cupidatat non proident, sunt in culpa qui officia deserunt mollit anim id est laborum." # noqa
project.instructions = "**The detailed instructions**\n\nLorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat." # noqa
project.entities_to_map = "primary roads, buildings"
project.imagery = "tms[22]:http://hiu-maps.net/hot/1.0.0/kathmandu_flipped/{zoom}/{x}/{y}.png" # noqa
project.license = license1
DBSession.add(project)
with project.force_locale('fr'):
project.name = "Kathmandu - Cartographier les routes et les bâtiments" # noqa
project.auto_fill(17)
def upgrade_databases(local_conf, global_conf):
"""
the database migration is managed by using alembic
see the alembic.ini file for configuration options
remark the database urls in the alembic.ini will be
replaced with the ones of your linotp.ini
if the writeback of the alembic.ini should not be done,
this could be specified in the linotp.ini by the option
alembic.writeback = False
:param local_conf: the linotp section of the linotp configuration
:param global_conf: the whole linotp configuration
:return: -nothing-
"""
preface = """# This config file is adjusted wrt. the sqlalchemy.urls
# by the websetup.py during
#
# paster setup-app linotp.ini
#
# Before running alembic manualy, make sure, that the sqlalchemy.url's
# are correct !!
#
# alembic supports you to run the run the database migration by the commands
#
# alembic upgrade head
# or
# alembic downgrade -1
"""
config = local_conf
here = global_conf.get('here', '')
alembic_ini = config.get('alembic.ini', "%s/alembic.ini" % here)
if not os.path.isfile(alembic_ini):
log.error('No Database migration done as no alembic configuration'
' [alembic.ini] could be found!')
return
databases = {}
linotp_url = config.get('sqlalchemy.url', '')
if linotp_url:
databases['linotp'] = linotp_url
audit_url = config.get('linotpAudit.sql.url', '')
if audit_url:
table_prefix = config.get("linotpAudit.sql.table_prefix", "")
databases['audit'] = audit_url
openid_url = config.get('linotpOpenID.sql.url', '')
if openid_url:
databases['openid'] = openid_url
# load the alembic configuration
alembic_cfg = Config(alembic_ini)
for database in databases:
if database == 'audit':
alembic_cfg.set_section_option(database, 'table_prefix', table_prefix)
alembic_cfg.set_section_option(database, 'sqlalchemy.url', databases.get(database))
alembic_cfg.set_section_option('alembic', 'databases', ','.join(databases.keys()))
if config.get('alembic.writeback', 'false').lower() == 'true':
fileConfig = alembic_cfg.file_config
with open(alembic_ini, 'w') as cfgfile:
cfgfile.write(preface)
fileConfig.write(cfgfile)
try:
if config.get('alembic.auto_update', 'false').lower() == 'true':
command.upgrade(alembic_cfg, "head")
except Exception as exx:
log.exception('error during upgrade %r' % exx)
return
from alembic import command
from alembic.config import Config
from flask.ext.assets import ManageAssets
from call_server.app import create_app
from call_server.extensions import assets, db, cache
from call_server import political_data
from call_server.user import User, USER_ADMIN, USER_ACTIVE
app = create_app()
app.db = db
manager = Manager(app)
alembic_config = Config(os.path.realpath(os.path.dirname(__name__)) + "/alembic.ini")
# let the config override the default db location in production
alembic_config.set_section_option('alembic', 'sqlalchemy.url',
app.config.get('SQLALCHEMY_DATABASE_URI'))
manager.add_command("assets", ManageAssets())
def reset_assets():
"""Reset assets named bundles to {} before running command.
This command should really be run with TestingConfig context"""
print "resetting assets"
assets._named_bundles = {}
@manager.command
def runserver(external=None):
"""Run web server for local development and debugging
pass --external for external routing"""
def upgrade_databases(local_conf, global_conf):
"""
the database migration is managed by using alembic
see the alembic.ini file for configuration options
remark the database urls in the alembic.ini will be
replaced with the ones of your linotp.ini
if the writeback of the alembic.ini should not be done,
this could be specified in the linotp.ini by the option
alembic.writeback = False
:param local_conf: the linotp section of the linotp configuration
:param global_conf: the whole linotp configuration
:return: -nothing-
"""
preface = """# This config file is adjusted wrt. the sqlalchemy.urls
# by the websetup.py during
#
# paster setup-app linotp.ini
#
# Before running alembic manualy, make sure, that the sqlalchemy.url's
# are correct !!
#
# alembic supports you to run the run the database migration by the commands
#
# alembic upgrade head
# or
# alembic downgrade -1
"""
config = local_conf
here = global_conf.get('here', '')
alembic_ini = config.get('alembic.ini', "%s/alembic.ini" % here)
if not os.path.isfile(alembic_ini):
log.error('No Database migration done as no alembic configuration'
' [alembic.ini] could be found!')
return
databases = {}
linotp_url = config.get('sqlalchemy.url', '')
if linotp_url:
databases['linotp'] = linotp_url
audit_url = config.get('linotpAudit.sql.url', '')
if audit_url:
table_prefix = config.get("linotpAudit.sql.table_prefix", "")
databases['audit'] = audit_url
openid_url = config.get('linotpOpenID.sql.url', '')
if openid_url:
databases['openid'] = openid_url
# load the alembic configuration
alembic_cfg = Config(alembic_ini)
for database in databases:
if database == 'audit':
alembic_cfg.set_section_option(database, 'table_prefix',
table_prefix)
alembic_cfg.set_section_option(database, 'sqlalchemy.url',
databases.get(database))
alembic_cfg.set_section_option('alembic', 'databases',
','.join(databases.keys()))
if config.get('alembic.writeback', 'false').lower() == 'true':
fileConfig = alembic_cfg.file_config
with open(alembic_ini, 'w') as cfgfile:
cfgfile.write(preface)
fileConfig.write(cfgfile)
try:
if config.get('alembic.auto_update', 'false').lower() == 'true':
command.upgrade(alembic_cfg, "head")
except Exception as exx:
log.exception('error during upgrade %r' % exx)
return
def create_db(tenant, db_host=None, tier_name=None):
config = load_config()
service = config['name']
db_name = construct_db_name(tenant, service, tier_name)
username = get_db_info()['user']
engine = connect(MASTER_DB, db_host)
engine.execute('COMMIT')
sql = 'CREATE DATABASE "{}";'.format(db_name)
try:
engine.execute(sql)
except Exception as e:
print sql, e
# TODO: This will only run for the first time and fail in all other cases.
# Maybe test before instead?
sql = 'CREATE ROLE {user} LOGIN PASSWORD "{user}" VALID UNTIL "infinity";'.format(
user=username)
try:
engine.execute(sql)
except Exception as e:
pass
engine = connect(db_name, db_host)
# TODO: Alembic (and sqlalchemy for that matter) don't like schemas. We should
# figure out a way to add these later
models = config.get("models", [])
if not models:
raise Exception("This app has no models defined in config")
for model_module_name in models:
log.info("Building models from %s", model_module_name)
models = importlib.import_module(model_module_name)
models.ModelBase.metadata.create_all(engine)
engine = connect(db_name, db_host)
for schema in schemas:
# Note that this does not automatically grant on tables added later
sql = '''
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "{schema}" TO {user};
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA "{schema}" TO {user};
GRANT ALL ON SCHEMA "{schema}" TO {user};'''.format(
schema=schema, user=username)
try:
engine.execute(sql)
except Exception as e:
print sql, e
# stamp the db with the latest alembic upgrade version
ini_path = os.path.join(
os.path.split(os.environ["drift_CONFIG"])[0], "..", "alembic.ini")
alembic_cfg = Config(ini_path)
db_names = alembic_cfg.get_main_option('databases')
connection_string = 'postgresql://%s:%s@%s/%s' % (username, username,
db_host, db_name)
alembic_cfg.set_section_option(db_names, "sqlalchemy.url",
connection_string)
command.stamp(alembic_cfg, "head")
sql = '''
ALTER TABLE alembic_version
OWNER TO postgres;
GRANT ALL ON TABLE alembic_version TO postgres;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE alembic_version TO zzp_user;
'''
engine.execute(sql)
return db_name
def alembic_config(self):
alembic_cfg = Config()
alembic_cfg.set_section_option("alembic", "script_location", "alembic")
alembic_cfg.set_section_option("alembic", "sqlalchemy.url", str(self.db.engine.url))
alembic_cfg.set_section_option("alembic", "sourceless", "true")
return alembic_cfg
def alembic_cfg():
from app.environment import env
alembic_cfg = AlembicConfig("alembic.ini")
alembic_cfg.set_section_option("alembic", "sqlalchemy.url", env['DB_URL'])
return alembic_cfg
def _create_config(self):
"""Programmatically create Alembic config. To determine databases,
DATASTORE from project's config file is used. To customize Alembic
use MIGRATIONS in you config file.
Example::
MIGRATIONS = {
'alembic': {
'script_location': '<path>',
'databases': '<db_name1>,<db_name2>',
},
'<db_name1>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'<bd_name2>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'logging': {
'path': '<path_to_logging_config>',
}
}
For more information about possible options, please visit Alembic
documentation:
https://alembic.readthedocs.org/en/latest/index.html
"""
app = self.app
cfg = Config()
cfg.get_template_directory = lux_template_directory
migrations = os.path.join(app.meta.path, 'migrations')
cfg.set_main_option('script_location', migrations)
cfg.config_file_name = os.path.join(migrations, 'alembic.ini')
odm = app.odm()
databases = []
# set section for each found database
for name, engine in odm.keys_engines():
if not name:
name = 'default'
databases.append(name)
# url = str(engine.url).replace('+green', '')
url = str(engine.url)
cfg.set_section_option(name, 'sqlalchemy.url', url)
# put databases in main options
cfg.set_main_option("databases", ','.join(databases))
# create empty logging section to avoid raising errors in env.py
cfg.set_section_option('logging', 'path', '')
# obtain the metadata required for `auto` command
metadata = {}
for key, db_engine in odm.keys_engines():
if not key:
key = 'default'
metadata[key] = meta = MetaData()
for table, engine in odm.binds.items():
if engine == db_engine:
table.tometadata(meta)
cfg.metadata = metadata
config = app.config.get('MIGRATIONS')
if config:
for section in config.keys():
for key, value in config[section].items():
if section == 'alembic':
cfg.set_main_option(key, value)
else:
cfg.set_section_option(section, key, value)
return cfg
DB_RO_URI = (
'mysql+%s://%s:%s@%s:%s/%s' %
(DB_LIBRARY, DB_RO_USER, DB_RO_PWD, DB_RO_HOST, DB_PORT, DB_NAME))
if not DB_RW_URI:
DB_RW_URI = (
'mysql+%s://%s:%s@%s:%s/%s' %
(DB_LIBRARY, DB_RW_USER, DB_RW_PWD, DB_RW_HOST, DB_PORT, DB_NAME))
if not DB_DDL_URI:
DB_DDL_URI = (
'mysql+%s://%s:%s@%s:%s/%s' %
(DB_LIBRARY, DB_DDL_USER, DB_DDL_PWD, DB_RW_HOST, DB_PORT, DB_NAME))
ALEMBIC_CFG = AlembicConfig()
ALEMBIC_CFG.set_section_option('alembic', 'script_location',
os.path.join(HERE, 'alembic'))
ALEMBIC_CFG.set_section_option('alembic', 'sqlalchemy.url', DB_DDL_URI)
GEOIP_PATH = os.environ.get('GEOIP_PATH')
if not GEOIP_PATH:
GEOIP_PATH = os.path.join(HERE, 'tests/data/GeoIP2-City-Test.mmdb')
MAP_TOKEN = os.environ.get('MAP_TOKEN')
REDIS_HOST = os.environ.get('REDIS_HOST')
REDIS_PORT = os.environ.get('REDIS_PORT', '6379')
REDIS_DB = '1' if TESTING else '0'
REDIS_URI = os.environ.get('REDIS_URI')
if REDIS_HOST and not REDIS_URI:
REDIS_URI = 'redis://%s:%s/%s' % (REDIS_HOST, REDIS_PORT, REDIS_DB)
def get_config(self):
'''
Programmatically create Alembic config. To determine databases,
DATASTORE from project's config file is used. To customize Alembic
use MIGRATIONS in you config file.
Example:
MIGRATIONS = {
'alembic': {
'script_location': '<path>',
'databases': '<db_name1>,<db_name2>',
},
'<db_name1>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'<bd_name2>': {
'sqlalchemy.url': 'driver://*****:*****@localhost/dbname',
},
'logging': {
'path': '<path_to_logging_config>',
}
}
For more information about possible options, please visit Alembic
documentation:
https://alembic.readthedocs.org/en/latest/index.html
'''
from alembic.config import Config
# Because we are using custom template, we need to change default
# implementation of get_template_directory() to make it pointing
# to template stored in lux, we need to do this hack
Config.get_template_directory = self.get_lux_template_directory
# put migrations in main project dir
migration_dir = os.path.join(os.getcwd(), 'migrations')
# set default settings in case where MIGRATIONS is not set
alembic_cfg = Config()
# where to place alembic env
alembic_cfg.set_main_option('script_location', migration_dir)
# get database(s) name(s) and location(s)
odm = self.app.odm()
databases = []
# set section for each found database
for name, engine in odm.keys_engines():
if not name:
name = 'default'
databases.append(name)
alembic_cfg.set_section_option(name, 'sqlalchemy.url',
str(engine.url))
# put databases in main options
alembic_cfg.set_main_option("databases", ','.join(databases))
# create empty logging section to avoid raising errors in env.py
alembic_cfg.set_section_option('logging', 'path', '')
# obtain the metadata required for `auto` command
self.get_metadata(alembic_cfg)
# get rest of settings from project config. This may overwrite
# already existing options (especially if different migration dir
# is provided)
cfg = self.app.config.get('MIGRATIONS')
if cfg:
for section in cfg.keys():
for key, value in cfg[section].items():
if section == 'alembic':
alembic_cfg.set_main_option(key, value)
else:
alembic_cfg.set_section_option(section, key, value)
return alembic_cfg