def init_data():
from imports import (
Widget,Article,Page,
User,Setting,Type,
Template,Tag,Role,
Category,Block,Profile,
ContactMessage)
"""Fish data for project"""
if prompt_bool('Do you want to kill your db?'):
if squ.database_exists(db.engine.url):
squ.drop_database(db.engine.url)
try:
db.drop_all()
except:
pass
try:
squ.create_database(db.engine.url)
db.create_all()
except:
pass
user = User.query.filter(User.email=='*****@*****.**').first()
if user is None:
user = User(username='******', email='*****@*****.**', password='******')
user.save()
def app(request):
"""The Flask API (scope = Session)."""
config.DB_NAME = DB_NAME
DATABASE_URI = config.DATABASE_URI.format(**config.__dict__)
if not database_exists(DATABASE_URI):
create_database(DATABASE_URI)
print "Test Database: %s" % DATABASE_URI
# Config the app
_app.config["SQLALCHEMY_DATABASE_URI"] = DATABASE_URI
_app.config["SQLALCHEMY_ECHO"] = True # Toggle SQL Alchemy output
_app.config["DEBUG"] = True
_app.config["TESTING"] = True
# Establish an application context before running the tests.
ctx = _app.app_context()
ctx.push()
# Initialize a null cache
cache.config = {}
cache.init_app(_app)
def teardown():
ctx.pop()
request.addfinalizer(teardown)
return _app
def create_ctfd(ctf_name="CTFd", name="admin", email="*****@*****.**", password="******", setup=True):
app = create_app('CTFd.config.TestingConfig')
url = make_url(app.config['SQLALCHEMY_DATABASE_URI'])
if url.drivername == 'postgres':
url.drivername = 'postgresql'
if database_exists(url):
drop_database(url)
create_database(url)
with app.app_context():
app.db.create_all()
if setup:
with app.app_context():
with app.test_client() as client:
data = {}
r = client.get('/setup') # Populate session with nonce
with client.session_transaction() as sess:
data = {
"ctf_name": ctf_name,
"name": name,
"email": email,
"password": password,
"nonce": sess.get('nonce')
}
client.post('/setup', data=data)
return app
def makeDbEngine(self):
'''
function to establish engine with PostgreSQl database
so that additional tables can be made
'''
try:
## connect to Postgres
dbname = self.getDbName()
username = self.getUserName()
print dbname
print username
## create and set
engine = create_engine('postgres://%s@localhost/%s'%(username, dbname))
self.setDbEngine(engine)
## test if it exists
db_exist = database_exists(engine.url)
if not db_exist:
create_database(engine.url)
db_exist = database_exists(engine.url)
self.setDbExist(db_exist)
return 0
except:
return 1
def add_db(): # pragma: no cover
db_url = config['service']['db_uri']
global engine
engine = create_engine(db_url)
if database_exists(engine.url):
print('!!! DATABASE ALREADY EXISTS !!!')
return False
print()
print('!!! DATABASE NOT DETECTED !!!')
print()
try:
confirm = input('Create database designated in the config file? [Y/n]') or 'Y'
except KeyboardInterrupt:
confirm = ''
print()
if confirm.strip() != 'Y':
print('Not createing DB. Exiting.')
return False
create_database(engine.url)
return True
def init_db(self):
"""
Initializes the database connection based on the configuration parameters
"""
db_type = self.config['db_type']
db_name = self.config['db_name']
if db_type == 'sqlite':
# we can ignore host, username, password, etc
sql_lite_db_path = os.path.join(os.path.split(CONFIG)[0], db_name)
self.db_connection_string = 'sqlite:///{}'.format(sql_lite_db_path)
else:
username = self.config['username']
password = self.config['password']
host_string = self.config['host_string']
self.db_connection_string = '{}://{}:{}@{}/{}'.format(db_type, username, password, host_string, db_name)
self.db_engine = create_engine(self.db_connection_string)
# If db not present AND type is not SQLite, create the DB
if not self.config['db_type'] == 'sqlite':
if not database_exists(self.db_engine.url):
create_database(self.db_engine.url)
Base.metadata.bind = self.db_engine
Base.metadata.create_all()
# Bind the global Session to our DB engine
global Session
Session.configure(bind=self.db_engine)
def create_postgres_db():
dbname = 'beer_db_2'
username = '******'
mypassword = '******'
## Here, we're using postgres, but sqlalchemy can connect to other things too.
engine = create_engine('postgres://%s:%s@localhost/%s'%(username,mypassword,dbname))
print "Connecting to",engine.url
if not database_exists(engine.url):
create_database(engine.url)
print "Does database exist?",(database_exists(engine.url))
# load a database from CSV
brewery_data = pd.DataFrame.from_csv('clean_data_csv/brewery_information_rescrape.csv')
## insert data into database from Python (proof of concept - this won't be useful for big data, of course)
## df is any pandas dataframe
brewery_data.to_sql('breweries', engine, if_exists='replace')
#dbname = 'beer_review_db'
# load a database from CSV
beer_data = pd.DataFrame.from_csv('clean_data_csv/beer_review_information_rescrape.csv')
#engine_2 = create_engine('postgres://%s:%s@localhost/%s'%(username,mypassword,dbname))
#print "connecting to",engine.url
#if not database_exists(engine_2.url):
# create_database(engine_2.url)
#print "Does database exist?",(database_exists(engine_2.url))
beer_data.to_sql('reviews',engine,if_exists='replace')
print "database",dbname,"has been created"
return
def setup():
print(app.config['SQLALCHEMY_DATABASE_URI'])
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'])
if database_exists(engine.url):
drop_database(engine.url)
create_database(engine.url)
engine.execute('create extension if not exists fuzzystrmatch')
def create_db(app):
from project.core.db import db
from sqlalchemy_utils import database_exists, create_database
if not database_exists(db.engine.url):
print '====> Create database'
create_database(db.engine.url)
else:
print '====> database exist'
def create_db_test():
create_database(url_db)
session = sessionmaker()
engine = create_engine(url_db)
session.configure(bind=engine)
Base.metadata.create_all(engine)
return engine
def create_findmyride_database(database_name):
engine = create_engine('postgresql://%s:%s@localhost/%s'%('dianeivy', password, database_name))
print(engine.url)
if not database_exists(engine.url):
create_database(engine.url)
print(database_exists(engine.url))
return engine
def start_fixture(self):
"""Create necessary temp files and do the config dance."""
global CONF
data_tmp_dir = tempfile.mkdtemp(prefix='gnocchi')
coordination_dir = os.path.join(data_tmp_dir, 'tooz')
os.mkdir(coordination_dir)
coordination_url = 'file://%s' % coordination_dir
conf = service.prepare_service([])
CONF = self.conf = conf
self.tmp_dir = data_tmp_dir
# Use the indexer set in the conf, unless we have set an
# override via the environment.
if 'GNOCCHI_TEST_INDEXER_URL' in os.environ:
conf.set_override('url',
os.environ.get("GNOCCHI_TEST_INDEXER_URL"),
'indexer')
# TODO(jd) It would be cool if Gabbi was able to use the null://
# indexer, but this makes the API returns a lot of 501 error, which
# Gabbi does not want to see, so let's just disable it.
if conf.indexer.url is None or conf.indexer.url == "null://":
raise case.SkipTest("No indexer configured")
# Use the presence of DEVSTACK_GATE_TEMPEST as a semaphore
# to signal we are not in a gate driven functional test
# and thus should override conf settings.
if 'DEVSTACK_GATE_TEMPEST' not in os.environ:
conf.set_override('driver', 'file', 'storage')
conf.set_override('coordination_url', coordination_url, 'storage')
conf.set_override('policy_file',
os.path.abspath('etc/gnocchi/policy.json'),
group="oslo_policy")
conf.set_override('file_basepath', data_tmp_dir, 'storage')
# NOTE(jd) All of that is still very SQL centric but we only support
# SQL for now so let's say it's good enough.
url = sqlalchemy_url.make_url(conf.indexer.url)
url.database = url.database + str(uuid.uuid4()).replace('-', '')
db_url = str(url)
conf.set_override('url', db_url, 'indexer')
sqlalchemy_utils.create_database(db_url)
index = indexer.get_driver(conf)
index.connect()
index.upgrade()
conf.set_override('pecan_debug', False, 'api')
# Turn off any middleware.
conf.set_override('middlewares', [], 'api')
self.index = index
def createdb():
print "Connecting to %s" % settings.SQLALCHEMY_DATABASE_URI
engine = create_engine(settings.SQLALCHEMY_DATABASE_URI)
if settings.DROP_DB_ON_RESTART and database_exists(engine.url):
print "Dropping old database... (because DROP_DB_ON_RESTART=True)"
drop_database(engine.url)
if not database_exists(engine.url):
print "Creating databases..."
create_database(engine.url)
def _create_new_database(cls, url):
"""Used by testing to create a new database."""
purl = sqlalchemy_url.make_url(
cls.dress_url(
url))
purl.database = purl.database + str(uuid.uuid4()).replace('-', '')
new_url = str(purl)
sqlalchemy_utils.create_database(new_url)
return new_url
def start_fixture(self):
"""Set up config."""
self.conf = None
self.conn = None
# Determine the database connection.
db_url = os.environ.get(
'AODH_TEST_STORAGE_URL', "").replace(
"mysql://", "mysql+pymysql://")
if not db_url:
self.fail('No database connection configured')
conf = service.prepare_service([], config_files=[])
# NOTE(jd): prepare_service() is called twice: first by load_app() for
# Pecan, then Pecan calls pastedeploy, which starts the app, which has
# no way to pass the conf object so that Paste apps calls again
# prepare_service. In real life, that's not a problem, but here we want
# to be sure that the second time the same conf object is returned
# since we tweaked it. To that, once we called prepare_service() we
# mock it so it returns the same conf object.
self.prepare_service = service.prepare_service
service.prepare_service = mock.Mock()
service.prepare_service.return_value = conf
conf = fixture_config.Config(conf).conf
self.conf = conf
opts.set_defaults(self.conf)
conf.set_override('policy_file',
os.path.abspath(
'aodh/tests/open-policy.json'),
group='oslo_policy',
enforce_type=True)
conf.set_override(
'paste_config',
os.path.abspath('aodh/tests/functional/gabbi/gabbi_paste.ini'),
group='api',
)
conf.set_override('pecan_debug', True, group='api',
enforce_type=True)
parsed_url = urlparse.urlparse(db_url)
if parsed_url.scheme != 'sqlite':
parsed_url = list(parsed_url)
parsed_url[2] += '-%s' % str(uuid.uuid4()).replace('-', '')
db_url = urlparse.urlunparse(parsed_url)
conf.set_override('connection', db_url, group='database',
enforce_type=True)
if (parsed_url[0].startswith("mysql")
or parsed_url[0].startswith("postgresql")):
sqlalchemy_utils.create_database(conf.database.connection)
self.conn = storage.get_connection_from_config(self.conf)
self.conn.upgrade()
def createDatabase(dbName):
"""Create specified database if it doesn't exist."""
config = CONFIG_DB
connectString = "postgresql://{}:{}@{}:{}/{}".format(
config["username"], config["password"], config["host"], config["port"], dbName
)
if not sqlalchemy_utils.database_exists(connectString):
sqlalchemy_utils.create_database(connectString)
def create_tables(db_url=None):
if db_url is None:
db_url = generate_url()
print("Connecting to database at %s" % db_url)
engine = create_engine(db_url)
if not database_exists(engine.url):
print("Creating database %s" % DB_NAME)
create_database(engine.url)
Base.metadata.create_all(create_engine(db_url))
print("Created tables! Open up mysql to see.")
def check_schema(schema):
url = db.engine.url
if url.database != schema:
copy_url = copy.copy(url)
copy_url.database = schema
url = copy_url
if not database_exists(url):
create_database(url)
def app():
"""
Create test database and application
"""
app = create_app(testing=True)
sqlalchemy_url = app.config['SQLALCHEMY_DATABASE_URI']
if database_exists(sqlalchemy_url):
drop_database(sqlalchemy_url)
create_database(sqlalchemy_url)
yield app
def initialize(re_createTable= False):
if re_createTable :
if not database_exists(engine.url):
create_database(DATABASE.url)
print(database_exists(engine.url))
Base.metadata.drop_all(DATABASE, checkfirst = True)
Base.metadata.create_all(DATABASE, checkfirst = True)
def setUp(self):
self.app = self.create_app()
self.db = DB(engine,session,meta)
import sqlalchemy_utils as squ
if squ.database_exists(self.db.engine.url):
squ.drop_database(self.db.engine.url)
squ.create_database(self.db.engine.url)
meta.bind = self.db.engine
meta.create_all()
def create_database(dbname):
#create a database with name "dbname" using lordluen ad username.
#dbname = 'legislatr'
username = '******'
engine = create_engine('postgres://%s@localhost/%s'%(username,dbname))
print(engine.url)
if not database_exists(engine.url):
create_database(engine.url)
print(database_exists(engine.url))
return
def create_sweography_db():
engine = create_engine(SQLALCHEMY_DATABASE_URI)
if database_exists(engine.url):
drop_database(engine.url)
create_database(engine.url)
##creates all tables in database
Base.metadata.create_all(engine)
def setUpClass(cls):
cls.engine = create_engine('postgresql+psycopg2://monitor@localhost/monitor_test')
if not database_exists(cls.engine.url):
create_database(cls.engine.url, template='template_postgis')
model.create_database(cls.engine, drop=True)
cls.monitor = StatusMonitor(cls.engine)
cls.monitor.read_expected_csv(os.path.join(test_dir, 'data', 'expected-rates.csv'))
def create():
"""
Create database and all tables
"""
if not database_exists(db.engine.url):
app.logger.debug('Creating a fresh database to work with')
create_database(db.engine.url)
alembic_stamp()
db.create_all()
app.logger.debug('Database already exists, please drop the database and try again!')
def db_init(ctx):
"""Create db."""
url = config.DATABASE_URL
if database_exists(url):
click.secho('Database %s already exists...' % url, fg='yellow')
ctx.abort()
click.secho('Creating %s...' % url, fg='blue')
create_database(url)
click.secho('Creating tables...', fg='blue')
Base.metadata.create_all(engine)
click.secho('Database initialized!', fg='green')
def setUp(self):
self.dao_url = make_url('sqlite:///community_app.db')
if not database_exists(self.dao_url):
create_database(self.dao_url)
self.dao = DataAccessObject(self.dao_url)
self.photos = [
Photo(owner_id=-129836227, object_id=431928280, album_id=-7, album='wall',
date_time=datetime.datetime(2016, 9, 30, 23, 55, 7), user_id=100,
text=None,
link='http://cs638122.vk.me/v638122248/1c41/SnfoaFP-Hfk.jpg')
]
def test_client(request):
db_uri = dci_app.config['DCI_DATABASE_URI']
sqlalchemy_utils.create_database(db_uri)
mydci.server.engine = sqlalchemy.create_engine(db_uri, echo=False)
models.metadata.create_all(mydci.server.engine)
def drop_db():
sqlalchemy_utils.drop_database(db_uri)
request.addfinalizer(drop_db)
dci_app.config['TESTING'] = True
return dci_app.test_client()
def create_if_not_exists(force_creation=False):
connect_db()
db_exists = database_exists(_engine.url)
if not db_exists or force_creation:
get_module_logger().info("Creating database '{}'. ({}, {})".format(
_engine.url, not db_exists, force_creation))
create_database(_engine.url)
Base.metadata.create_all(bind=_engine)
_session.close()
def setUp(self):
self.engine = sqlalchemy.create_engine(
CONFIG.get('database', 'test_conn_string'),
isolation_level="AUTOCOMMIT"
)
if not database_exists(self.engine.url):
create_database(self.engine.url)
self.maxDiff = None
self.connection = self.engine.connect()
Base.metadata.create_all(self.engine, checkfirst=True)
self.conn = AlchemyConnection(self.engine.url)
def postgresql(host, rows, queries, include_hits):
"""Insert rows and run search queries in postgresql.
:param host: PostgreSQL server location
:type host: str
:param rows: Rows to insert in the logs table
:type count: list(dict(str))
:param queries: Queries to execute
:type queries: list(str)
:param include_hits: Include hits in results
:type include_hits: bool
:returns: Insert and query timers
:rtype: dict(str, contexttimer.Timer)
"""
table_name = 'logs'
url = 'postgres://postgres@{}/database'.format(host)
logging.debug('Connecting to postgresql in: %r...', url)
engine = create_engine(url)
if not database_exists(url):
create_database(url)
metadata = MetaData()
metadata.bind = engine
table = Table(
table_name,
metadata,
Column('timestamp', TIMESTAMP, nullable=False),
Column('message', TEXT, nullable=False),
Column('vector', TSVECTOR, nullable=False),
)
Index(
'vector_index',
table.c.vector,
postgresql_using='gin',
)
metadata.drop_all()
metadata.create_all()
connection = engine.connect()
logging.debug('Inserting %d rows...', len(rows))
insert_query = text(
"INSERT INTO logs (timestamp, message, vector) "
"VALUES (:timestamp, :message, to_tsvector('english', :message))")
with Timer() as insert_timer:
connection.execute(insert_query, rows)
logging.debug('Inserting took %f seconds', insert_timer.elapsed)
logging.debug('Running random search queries...')
if include_hits:
select_query = text(
"WITH matches AS ("
"SELECT message, query "
"FROM logs, to_tsquery('english', :plain_query) AS query "
"WHERE vector @@ query "
"ORDER BY ts_rank(vector, query) DESC "
")"
"SELECT "
"(SELECT COUNT(*) FROM matches) AS count, "
"ts_headline('english', message, query) "
"FROM matches "
"LIMIT 1")
else:
select_query = text(
"SELECT ts_headline('english', message, query) "
"FROM ("
"SELECT message, query "
"FROM logs, to_tsquery('english', :plain_query) AS query "
"WHERE vector @@ query "
"ORDER BY ts_rank(vector, query) DESC "
"LIMIT 1"
") AS subquery")
with Timer() as query_timer:
for words in queries:
plain_query = ' | '.join(words.split())
result = connection.execute(
select_query,
{
'plain_query': plain_query
},
).fetchone()
if include_hits:
if result:
total, highlight = result
else:
total, highlight = 0, None
logging.debug('%r -> %d hits', plain_query, total)
else:
if result:
highlight = result[0]
logging.debug('%r', plain_query)
if highlight is not None:
logging.debug(pformat(highlight))
logging.debug('Querying took %f seconds', query_timer.elapsed)
return {'insert': insert_timer, 'query': query_timer}
def generate_database(engine_url, data_path, engine):
create_database(engine_url)
# ---------------------
# Species_table generation
# ----------------------
# extract Species information from the list
species_list_df = pd.read_csv(data_path + "vog.species.list",
sep='\t',
header=0,
names=['SpeciesName', 'TaxonID', 'Phage', 'Source', 'Version']) \
.assign(Phage=lambda p: p.Phage == 'phage')
# create a species table in the database
species_list_df.to_sql(name='Species_profile',
con=engine,
if_exists='replace',
index=False,
chunksize=1000)
with engine.connect() as con:
con.execute(
'ALTER TABLE `Species_profile` ADD PRIMARY KEY (`TaxonID`);')
con.execute(
'ALTER TABLE Species_profile MODIFY SpeciesName char(100) NOT NULL; '
)
con.execute(
'ALTER TABLE Species_profile MODIFY TaxonID int(255) NOT NULL; ')
con.execute(
'ALTER TABLE Species_profile MODIFY Phage bool NOT NULL; ')
con.execute(
'ALTER TABLE Species_profile MODIFY Source char(100) NOT NULL; ')
con.execute(
'ALTER TABLE Species_profile MODIFY Version int(255) NOT NULL; ')
# ToDo add foreign key to connect tax_id in protein_profile and species_profile? create index?
print('Species_profile table successfully created!')
# ---------------------
# VOG_table generation
# ----------------------
# read in the data files
members = pd.read_csv(os.path.join(data_path, 'vog.members.tsv.gz'),
compression='gzip',
sep='\t',
header=0,
names=[
'VOG_ID', 'ProteinCount', 'SpeciesCount',
'FunctionalCategory', 'Proteins'
],
usecols=[
'VOG_ID', 'ProteinCount', 'SpeciesCount',
'FunctionalCategory', 'Proteins'
],
index_col='VOG_ID')
annotations = pd.read_csv(os.path.join(data_path,
'vog.annotations.tsv.gz'),
compression='gzip',
sep='\t',
header=0,
names=[
'VOG_ID', 'ProteinCount', 'SpeciesCount',
'FunctionalCategory',
'Consensus_func_description'
],
usecols=['VOG_ID', 'Consensus_func_description'],
index_col='VOG_ID')
lca = pd.read_csv(
os.path.join(data_path, 'vog.lca.tsv.gz'),
compression='gzip',
sep='\t',
header=0,
names=['VOG_ID', 'GenomesInGroup', 'GenomesTotal', 'Ancestors'],
index_col='VOG_ID')
virusonly = pd.read_csv(os.path.join(data_path, 'vog.virusonly.tsv.gz'),
compression='gzip',
sep='\t',
header=0,
names=[
'VOG_ID', 'StringencyHigh', 'StringencyMedium',
'StringencyLow'
],
dtype={
'StringencyHigh': bool,
'StringencyMedium': bool,
'StringencyLow': bool
},
index_col='VOG_ID')
dfr = members.join(annotations).join(lca).join(virusonly)
dfr['VirusSpecific'] = np.where((dfr['StringencyHigh']
| dfr['StringencyMedium']
| dfr['StringencyLow']), True, False)
#create number of phages and non-phages for VOG. also "phages_only" "np_only" or "mixed"
dfr['NumPhages'] = 0
dfr['NumNonPhages'] = 0
dfr['PhageNonphage'] = ''
species_list_df.set_index("TaxonID", inplace=True)
for index, row in dfr.iterrows():
num_nonphage = 0
num_phage = 0
p = row['Proteins'].split(",")
for protein in p:
species_id = protein.split('.')[0]
species_id = int(species_id)
if (species_list_df.loc[species_id])["Phage"]:
num_phage = num_phage + 1
else:
num_nonphage = num_nonphage + 1
dfr.at[index, 'NumPhages'] = num_phage
dfr.at[index, 'NumNonPhages'] = num_nonphage
if (num_phage > 0) and (num_nonphage > 0):
dfr.at[index, 'PhageNonphage'] = "mixed"
elif num_phage > 0:
dfr.at[index, 'PhageNonphage'] = "phages_only"
else:
dfr.at[index, 'PhageNonphage'] = "np_only"
# create a table in the database
dfr.to_sql(name='VOG_profile',
con=engine,
if_exists='replace',
index=True,
dtype={
'VOG_ID':
VARCHAR(
dfr.index.get_level_values('VOG_ID').str.len().max()),
'Proteins':
LONGTEXT
},
chunksize=1000)
with engine.connect() as con:
con.execute('ALTER TABLE VOG_profile ADD PRIMARY KEY (`VOG_ID`(8)); '
) # add primary key
con.execute(
'ALTER TABLE VOG_profile MODIFY VOG_ID char(30) NOT NULL; '
) # convert text to char
con.execute(
'ALTER TABLE VOG_profile MODIFY FunctionalCategory char(30) NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY Consensus_func_description char(100) NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY ProteinCount int(255) NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY SpeciesCount int(255) NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY GenomesInGroup int(255) NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY GenomesTotal int(255) NOT NULL; '
)
con.execute('ALTER TABLE VOG_profile MODIFY Ancestors TEXT; ')
con.execute(
'ALTER TABLE VOG_profile MODIFY StringencyHigh bool NOT NULL; ')
con.execute(
'ALTER TABLE VOG_profile MODIFY StringencyMedium bool NOT NULL; '
)
con.execute(
'ALTER TABLE VOG_profile MODIFY StringencyLow bool NOT NULL; ')
con.execute(
'ALTER TABLE VOG_profile MODIFY VirusSpecific bool NOT NULL; ')
con.execute(
'ALTER TABLE VOG_profile MODIFY NumPhages int(255) NOT NULL; ')
con.execute(
'ALTER TABLE VOG_profile MODIFY NumNonPhages int(255) NOT NULL; '
)
con.execute('ALTER TABLE VOG_profile MODIFY PhageNonphage TEXT; ')
con.execute('ALTER TABLE VOG_profile MODIFY Proteins LONGTEXT; ')
con.execute(
'CREATE UNIQUE INDEX VOG_profile_index ON VOG_profile (VOG_ID, FunctionalCategory);'
) # create index
# con.execute('CREATE INDEX VOG_profile_index2 ON VOG_profile (Consensus_func_description);') # create index
#con.execute('ALTER TABLE VOG_profile ADD FOREIGN KEY (TaxonID) REFERENCES Species_profile(TaxonID); ')
# ToDo: add foreign keys to link to proteins, and species lists.
print('VOG_table successfully created!')
#---------------------
# Protein_table generation
#----------------------
# extract proteins for each VOG
protein_list_df = pd.read_csv(data_path + "vog.members.tsv.gz",
compression='gzip',
sep='\t').iloc[:, [0, 4]]
protein_list_df = protein_list_df.rename(columns={
"#GroupName": "VOG_ID",
"ProteinIDs": "ProteinID"
})
# assign each protein a vog
protein_list_df = (protein_list_df["ProteinID"].str.split(",").apply(
lambda x: pd.Series(x)).stack().reset_index(
level=1,
drop=True).to_frame("ProteinID").join(protein_list_df[["VOG_ID"]],
how="left"))
protein_list_df.set_index("ProteinID")
# save the taxon ID in a separate column
protein_list_df["TaxonID"] = protein_list_df["ProteinID"].str.split(
".").str[0]
# create a protein table in the database
protein_list_df.to_sql(name='Protein_profile',
con=engine,
if_exists='replace',
index=False,
chunksize=1000)
with engine.connect() as con:
con.execute(
'ALTER TABLE Protein_profile MODIFY ProteinID char(30) NOT NULL; '
)
con.execute(
'ALTER TABLE Protein_profile MODIFY TaxonID int(30) NOT NULL; ')
con.execute(
'ALTER TABLE Protein_profile MODIFY VOG_ID char(30) NOT NULL; ')
con.execute(
'CREATE INDEX VOG_profile_index_by_protein ON Protein_profile (ProteinID);'
)
# add foreign key
con.execute(
'ALTER TABLE Protein_profile ADD FOREIGN KEY (TaxonID) REFERENCES Species_profile(TaxonID); '
)
con.execute(
'ALTER TABLE Protein_profile ADD FOREIGN KEY (VOG_ID) REFERENCES VOG_profile(VOG_ID); '
)
print('Protein_profile table successfully created!')
#---------------------
# Amino Acid and Nucleotide Sequence Table Generation
#----------------------
proteinfile = data_path + "vog.proteins.all.fa"
genefile = data_path + "vog.genes.all.fa"
prot = []
for seq_record in SeqIO.parse(proteinfile, "fasta"):
prot.append([seq_record.id, str(seq_record.seq)])
df = pd.DataFrame(prot, columns=['ID', 'AAseq'])
df.set_index("ID")
genes = []
for seq_record in SeqIO.parse(genefile, "fasta"):
genes.append([seq_record.id, str(seq_record.seq)])
dfg = pd.DataFrame(genes, columns=['ID', 'NTseq'])
dfg.set_index('ID')
# convert dataframes to DB Tables:
df.to_sql(name='AA_seq',
con=engine,
if_exists='replace',
index=False,
chunksize=1000)
dfg.to_sql(name='NT_seq',
con=engine,
if_exists='replace',
index=False,
chunksize=1000)
with engine.connect() as con:
con.execute('ALTER TABLE AA_seq MODIFY ID char(30) NOT NULL; ')
con.execute('ALTER TABLE AA_seq MODIFY AASeq LONGTEXT; ')
con.execute('CREATE INDEX ID ON AA_seq (ID);')
con.execute('ALTER TABLE NT_seq MODIFY ID char(30) NOT NULL; ')
con.execute('ALTER TABLE NT_seq MODIFY NTSeq LONGTEXT; ')
con.execute('CREATE INDEX ID ON NT_seq (ID);')
def create_db(username, password, host, port, db_name):
engine = create_engine(f'postgresql://{username}:{password}@{host}:{port}/{db_name}')
if not database_exists(engine.url):
create_database(engine.url)
def db_init():
from models.db_model.db import engine, Db
from models.db_model.model import Base, SysUser, SysMenu, SysRole, SysRoleMenu, SysUserRole
from sqlalchemy_utils import database_exists, create_database
if not database_exists(engine.url):
create_database(engine.url)
Base.metadata.create_all(engine)
if not database_exists(engine.url):
session = Db.get_session()
try:
admin_user = SysUser(
name='admin',
password=
'******',
cname='管理员',
)
session.add(admin_user)
workspace_menu = SysMenu(name='我的工作台',
menu_path='/workspace',
menu_reg='^\/workspace\/?$',
icon='workspace')
session.add(workspace_menu)
project_menu = SysMenu(name='项目',
menu_path='/project',
menu_reg='^\/project\/?',
icon='project')
session.add(project_menu)
tool_menu = SysMenu(name='工具',
menu_path='/tool',
menu_reg='^\/tool\/?',
icon='tool')
session.add(tool_menu)
manage_menu = SysMenu(name='管理',
menu_path='/manage',
menu_reg='^\/manage\/?$',
icon='manage')
session.add(manage_menu)
session.commit()
user_manage_menu = SysMenu(name='用户管理',
menu_path='/manage/user',
menu_reg='^\/manage\/user\/?$',
parent_id=manage_menu.id)
session.add(user_manage_menu)
role_manage_menu = SysMenu(name='角色管理',
menu_path='/manage/role',
menu_reg='^\/manage\/role\/?$',
parent_id=manage_menu.id)
session.add(role_manage_menu)
admin_role = SysRole(role_name='管理员', )
session.add(admin_role)
session.commit()
session.add(
SysUserRole(user_id=admin_user.id, role_id=admin_role.id))
session.add(
SysRoleMenu(role_id=admin_role.id, menu_id=workspace_menu.id))
session.add(
SysRoleMenu(role_id=admin_role.id, menu_id=project_menu.id))
session.add(
SysRoleMenu(role_id=admin_role.id, menu_id=tool_menu.id))
session.add(
SysRoleMenu(role_id=admin_role.id, menu_id=manage_menu.id))
session.add(
SysRoleMenu(role_id=admin_role.id,
menu_id=user_manage_menu.id))
session.add(
SysRoleMenu(role_id=admin_role.id,
menu_id=role_manage_menu.id))
session.commit()
except Exception as e:
session.rollback()
logging.error(traceback.format_exc())
def test_create_and_drop(self):
assert not database_exists(self.url)
create_database(self.url)
assert database_exists(self.url)
drop_database(self.url)
assert not database_exists(self.url)
def create_app(test_config=None):
"""
The flask application factory. To run the app somewhere else you can:
```
from api import create_app
app = create_app()
if __main__ == "__name__":
app.run()
"""
app = Flask(__name__)
CORS(app) # add CORS
JWTManager(app)
# check environment variables to see which config to load
env = os.environ.get("FLASK_ENV", "dev")
# for configuration options, look at api/config.py
if test_config:
# purposely done so we can inject test configurations
# this may be used as well if you'd like to pass
# in a separate configuration although I would recommend
# adding/changing it in api/config.py instead
# ignore environment variable config if config was given
app.config.from_mapping(**test_config)
else:
app.config.from_object(
config[env]) # config dict is from api/config.py
# logging
formatter = RequestFormatter(
"%(asctime)s %(remote_addr)s: requested %(url)s: %(levelname)s in [%(module)s: %(lineno)d]: %(message)s"
)
if app.config.get("LOG_FILE"):
fh = logging.FileHandler(app.config.get("LOG_FILE"))
fh.setLevel(logging.DEBUG)
fh.setFormatter(formatter)
app.logger.addHandler(fh)
strm = logging.StreamHandler()
strm.setLevel(logging.DEBUG)
strm.setFormatter(formatter)
app.logger.addHandler(strm)
app.logger.setLevel(logging.DEBUG)
root = logging.getLogger("core")
root.addHandler(strm)
# decide whether to create database
if env != "prod":
db_url = app.config["SQLALCHEMY_DATABASE_URI"]
if not database_exists(db_url):
create_database(db_url)
# register sqlalchemy to this app
from api.db import db
db.init_app(app) # initialize Flask SQLALchemy with this flask app
Migrate(app, db)
init_jtw_security(app)
# register error Handler
app.register_error_handler(Exception, all_exception_handler)
# import and register blueprints
from .blueprints import register_blueprints
# why blueprints http://flask.pocoo.org/docs/1.0/blueprints/
register_blueprints(app)
return app
from flask import Flask
from acceptme import Bot
from flask_login import LoginManager
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy_utils import create_database, database_exists
bot = Bot
app = Flask(__name__)
app.config['SECRET_KEY'] = 'supersecretkey'
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql://*****:*****@localhost/instamax'
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
db = SQLAlchemy(app)
if not database_exists(app.config['SQLALCHEMY_DATABASE_URI']):
create_database(app.config['SQLALCHEMY_DATABASE_URI'])
login_manager = LoginManager()
login_manager.init_app(app)
login_manager.blueprint_login_views = {
'core': 'core.login',
'admin': 'admin.login'
}
from project.core.views import core_blueprint
from project.users.views import users_blueprint
from project.admin.views import admins_blueprint
from project.error_pages.handler import error_pages
app.register_blueprint(error_pages)
def _DB_conn(user, passwd, db):
conn = create_engine('mysql+mysqldb://{}:{}@localhost/{}'.format(
user, passwd, db))
if not database_exists(conn.url):
create_database(conn.url)
return conn
def init_database(request):
DB_URL = TestingConfig.SQLALCHEMY_DATABASE_URI
if not database_exists(DB_URL):
print('Creating database.')
create_database(DB_URL)
db.create_all()
# Fill database with data for testing
author_1 = Author(name="Niel Gaiman",
date_of_birth=datetime.strptime('1976-12-29',
'%Y-%m-%d'),
country="UK")
author_2 = Author(name="Terry Pratchet",
date_of_birth=datetime.strptime('1950-02-15',
'%Y-%m-%d'),
country="UK")
author_3 = Author(name="George Martin",
date_of_birth=datetime.strptime('1960-06-09',
'%Y-%m-%d'),
country="USA")
author_4 = Author(name="Stephen King",
date_of_birth=datetime.strptime('1970-07-21',
'%Y-%m-%d'),
country="USA")
author_5 = Author(name="Niel Stephenson",
date_of_birth=datetime.strptime('1974-05-29',
'%Y-%m-%d'),
country="USA")
author_6 = Author(name="Paolo Bachigalupi",
date_of_birth=datetime.strptime('1976-12-13',
'%Y-%m-%d'),
country="USA")
author_7 = Author(name="Patrick Rothfuss",
date_of_birth=datetime.strptime('1970-12-13',
'%Y-%m-%d'),
country="USA")
author_8 = Author(name="Isaak Asimov",
date_of_birth=datetime.strptime('1940-12-13',
'%Y-%m-%d'),
country="USA")
author_9 = Author(name="Miguel de Servantes",
date_of_birth=datetime.strptime('1630-02-13',
'%Y-%m-%d'),
country="Spain")
book_1 = Book(title="Good omens",
publish_date=datetime.strptime('2008-12-29', '%Y-%m-%d'),
author_ids=[author_1, author_2],
avg_rating=1,
num_rating=10)
book_2 = Book(title="Storm of swords",
publish_date=datetime.strptime('2005-11-29', '%Y-%m-%d'),
author_ids=[author_3],
avg_rating=5,
num_rating=10)
book_3 = Book(title="Wind-up girl",
publish_date=datetime.strptime('2009-07-12', '%Y-%m-%d'),
author_ids=[author_6, author_7],
avg_rating=5,
num_rating=10)
book_4 = Book(title="Great omens",
publish_date=datetime.strptime('2002-12-29', '%Y-%m-%d'),
author_ids=[author_1, author_3],
avg_rating=3,
num_rating=10)
book_5 = Book(title="Game of thrones",
publish_date=datetime.strptime('2003-12-29', '%Y-%m-%d'),
author_ids=[author_1, author_3, author_5],
avg_rating=4,
num_rating=10)
book_6 = Book(title="Feast for crows",
publish_date=datetime.strptime('2015-12-29', '%Y-%m-%d'),
author_ids=[author_3],
avg_rating=2,
num_rating=10)
book_7 = Book(title="Fever dreams",
publish_date=datetime.strptime('2002-12-29', '%Y-%m-%d'),
author_ids=[author_3],
avg_rating=2,
num_rating=10)
book_8 = Book(title="Song of Lia",
publish_date=datetime.strptime('2018-01-29', '%Y-%m-%d'),
author_ids=[author_3],
avg_rating=1,
num_rating=10)
book_9 = Book(title="Wind-up Girl 2",
publish_date=datetime.strptime('2011-03-29', '%Y-%m-%d'),
author_ids=[author_7, author_5],
avg_rating=3.4,
num_rating=10)
book_10 = Book(title="Name of the wind",
publish_date=datetime.strptime('2011-12-11', '%Y-%m-%d'),
author_ids=[author_7, author_3],
avg_rating=1.3,
num_rating=10)
book_11 = Book(title="Wise man fear",
publish_date=datetime.strptime('2018-12-22', '%Y-%m-%d'),
author_ids=[author_1, author_7],
avg_rating=5,
num_rating=10)
book_12 = Book(title="Don Quihote",
publish_date=datetime.strptime('1670-12-02', '%Y-%m-%d'),
author_ids=[author_9],
avg_rating=1,
num_rating=9)
book_13 = Book(title="Foundation and Empire",
publish_date=datetime.strptime('2018-12-29', '%Y-%m-%d'),
author_ids=[author_8],
avg_rating=1,
num_rating=9)
db.session.add_all([
author_1, author_2, author_3, author_4, author_5, author_6, author_7,
author_8, author_9, book_1, book_2, book_3, book_4, book_5, book_6,
book_7, book_8, book_9, book_10, book_11, book_12, book_13
])
db.session.commit()
def teardown():
db.drop_all()
request.addfinalizer(teardown)
return db
def _create_test_db():
db_url = os.environ['DATABASE_URL']
if not sqlalchemy_utils.database_exists(db_url):
sqlalchemy_utils.create_database(db_url)
def connect(self): # noqa
if not database_exists(self.engine.url):
create_database(self.engine.url)
self.session = sessionmaker(bind=self.engine)()
Result.metadata.create_all(self.engine)
def auto_migrate(engine: sqlalchemy.engine.Engine, models: [sqlalchemy.sql.schema.MetaData]):
"""
Compares a database with a list of defined orm models and applies the diff. Prints executed SQL statements to stdout.
Based on `alembic automigrations`_, but doesn't require intermediate migration files.
Use with care, does not work in many cases.
Args:
engine: the database to use
models: A list of orm models
Returns:
True in case of no failures
.. _alembic automigrations:
http://alembic.zzzcomputing.com/en/latest/autogenerate.html
"""
import alembic.runtime.migration
import alembic.autogenerate
import sqlalchemy_utils
try:
# create database if it does not exist
if not sqlalchemy_utils.database_exists(engine.url):
sqlalchemy_utils.create_database(engine.url)
print(f'Created database "{engine.url!r}"\n')
except Exception as e:
print(f'Could not access or create database "{engine.url!r}":\n{e}', file=sys.stderr)
return False
# merge all models into a single metadata object
combined_meta_data = MetaData()
for model in models:
model.metadata.tables[model.__tablename__].tometadata(combined_meta_data)
# create diff between models and current db and translate to ddl
ddl = []
with engine.connect() as connection:
output = io.StringIO()
diff_context = alembic.runtime.migration.MigrationContext(connection.dialect, connection, opts={})
autogen_context = alembic.autogenerate.api.AutogenContext(diff_context,
opts={'sqlalchemy_module_prefix': 'sqlalchemy.',
'alembic_module_prefix': 'executor.'})
execution_context = alembic.runtime.migration.MigrationContext(connection.dialect, connection,
opts={'output_buffer': output, 'as_sql': True})
# needed for the eval below
executor = alembic.operations.Operations(execution_context)
# Step 1: create a diff between the meta data and the data base
# operations is a list of MigrateOperation instances, e.g. a DropTableOp
operations = alembic.autogenerate.produce_migrations(diff_context, combined_meta_data).upgrade_ops.ops
for operation in operations:
# Step 2: autogenerate a python statement from the operation, e.g. "executor.drop_table('bar')"
renderer = alembic.autogenerate.renderers.dispatch(operation)
statements = renderer(autogen_context, operation)
if not isinstance(statements, list):
statements = [statements]
for statement in statements:
# Step 3: "execute" python statement and get sql from buffer, e.g. "DROP TABLE bar;"
try:
eval(statement)
except Exception as e:
print('statement: ' + statement)
raise (e)
ddl.append(output.getvalue())
output.truncate(0)
output.seek(0)
with engine.begin() as connection:
for statement in ddl:
sys.stdout.write('\033[1;32m' + statement + '\033[0;0m')
connection.execute(statement)
return True
from core import db
from core.manager import MinitManager
from flask_script import Manager
from flask_migrate import Migrate, MigrateCommand
from sqlalchemy.engine.url import make_url
from sqlalchemy_utils import database_exists, create_database
migrate = Migrate(app, db)
manager = Manager(app)
manager.add_command('db', MigrateCommand)
manager.add_command('minit', MinitManager)
url = make_url(app.config['SQLALCHEMY_DATABASE_URI'])
if not database_exists(url):
create_database(url, encoding='utf8mb4')
from modules.models import *
@manager.command
def filldb():
user0 = User(username="******",
password="******",
role=roles["customer"])
user1 = User(username="******", password="******", role=roles["customer"])
user2 = User(username="******", password="******", role=roles["sales"])
user3 = User(username="******", password="******", role=roles["engineering"])
user4 = User(username="******", password="******", role=roles["production"])
user5 = User(username="******", password="******", role=roles["finance"])
user6 = User(username="******", password="******", role=roles["test"])
app.register_blueprint(screamscape, url_prefix='/api/v1/screamscape')
app.register_blueprint(coastergoals, url_prefix='/api/v1/coastergoals')
app.register_blueprint(articles, url_prefix='/api/v1/coasters/articles')
app.register_blueprint(tripjournals, url_prefix='/api/v1/coasters/journals')
app.register_blueprint(parks, url_prefix='/api/v1/coasters/parks')
app.register_blueprint(jobs, url_prefix='/api/v1/jobs')
migrate = Migrate(app, db)
with app.app_context():
db.init_app(app)
if not os.path.isfile(
os.path.dirname(os.path.realpath(__file__)) +
"/data/homeserver.db"):
print("No database found. Creating one")
create_database(app.config['SQLALCHEMY_DATABASE_URI'],
encoding='utf8mb4')
db.create_all()
db.session.commit()
@app.teardown_appcontext
def shutdown_session(exception=None):
db.session.remove()
@app.route("/", defaults={'u_path': ''})
@app.route('/<path:u_path>')
def hello(u_path):
return render_template('index.html')
#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
__author__ = 'RemiZOffAlex'
__copyright__ = '(c) RemiZOffAlex'
__license__ = 'MIT'
__email__ = '*****@*****.**'
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy_utils import database_exists, create_database
from .. import app
engine = create_engine(app.config['SQLALCHEMY_DATABASE_URI'], echo=True)
if not database_exists(engine.url):
create_database(engine.url)
db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
from .users import User
Base.metadata.create_all(engine)
__all__ = ['db_session', 'User']
from utils.database import get_all_user_db_urls
from views import blueprints
from views.auth import get_user_by_id
JINJA_ENVIRONMENT = jinja2.Environment(loader=jinja2.FileSystemLoader(
os.path.join(os.path.dirname(__file__), 'build')),
extensions=['jinja2.ext.autoescape'],
autoescape=True)
set_logging(app)
set_config(app)
db.init_app(app)
if not database_exists(app.config['SQLALCHEMY_DATABASE_URI']):
create_database(app.config['SQLALCHEMY_DATABASE_URI'], encoding='utf8mb4')
logging.error(f'The application database has just been created, the tables'
f' still need to be created (run reate_tables_new_db.sh) '
f'for the app to work.')
if not database_exists(app.config['SQLALCHEMY_EXAMPLE_CUSTOMER_DB_URI']):
create_database(app.config['SQLALCHEMY_EXAMPLE_CUSTOMER_DB_URI'],
encoding='utf8mb4')
logging.error(f'The example database has just been created, the tables '
f'still need to be created (run reate_tables_new_db.sh) '
f'for the app to work.')
with app.app_context():
try:
load_customer_db_config(app)
except ProgrammingError:
def create_connector(json_conf, logger=None):
"""Creator function for the database connection. It necessitates the following information from
the json_conf dictionary:
- dbtype (one of sqlite, mysql, postgresql)
- db (name of the database file, for sqlite, otherwise name of the database)
If the database is MySQL/PostGreSQL, the method also requires:
- dbuser
- dbhost
- dbpasswd
- dbport
These are controlled and added automatically by the json_utils functions.
:param json_conf: configuration dictionary
:type json_conf: dict
:param logger: a logger instance
:type logger: logging.Logger
:rtype : MySQLdb.connect | sqlite3.connect | psycopg2.connect
"""
if logger is None:
# Create a default null handler
logger = logging.Logger("null")
logger.addHandler(logging.NullHandler())
db_settings = json_conf["db_settings"]
func = None
if db_settings["dbtype"] == "sqlite":
if not database_exists("sqlite:///{}".format(db_settings["db"])):
logger.warning("No database found, creating a mock one!")
create_database("sqlite:///{}".format(db_settings["db"]))
if json_conf["pick"]["run_options"]['shm'] is False:
logger.debug("Connecting to %s", db_settings["db"])
func = sqlite3.connect(database=db_settings["db"],
check_same_thread=False)
else:
logger.debug("Connecting to %s",
json_conf["pick"]["run_options"]["shm_db"])
func = sqlite3.connect(
database=json_conf["pick"]["run_options"]["shm_db"],
check_same_thread=False,
isolation_level=None)
elif db_settings["dbtype"] in ("mysql", "postgresql"):
if db_settings["dbpasswd"] != '':
passwd = ":{0}".format(db_settings["dbpasswd"])
else:
passwd = ''
url = "{dialect}://{user}{passwd}@{host}:{port}/{db}".format(
dialect=db_settings["dbtype"],
host=db_settings["dbhost"],
user=db_settings["dbuser"],
passwd=passwd,
db=db_settings["db"],
port=db_settings["dbport"])
if database_exists(url) is False:
create_database(url)
if db_settings["dbtype"] == "mysql":
import MySQLdb
logger.debug("Connecting to MySQL %s", db_settings["db"])
func = MySQLdb.connect(host=db_settings["dbhost"],
user=db_settings["dbuser"],
passwd=db_settings["dbpasswd"],
db=db_settings["db"],
port=db_settings["dbport"])
elif db_settings["dbtype"] == "postgresql":
import psycopg2
logger.debug("Connecting to PSQL %s", db_settings["db"])
func = psycopg2.connect(host=db_settings["dbhost"],
user=db_settings["dbuser"],
password=db_settings["dbpasswd"],
database=db_settings["db"],
port=db_settings["dbport"])
else:
raise ValueError("DB type not supported! {0}".format(
db_settings["dbtype"]))
return func
def __init__(self, config, db_type=TYPE_MYSQL):
self.conf = config
self.user = self.conf["sqlUser"]
self.passwd = self.conf["sqlPass"]
self.host = "127.0.0.1"
self.database = "MoltenIron"
self.db_type = db_type
engine = None
try:
# Does the database exist?
engine = self.create_engine()
c = engine.connect()
c.close()
except (OperationalError, InternalError) as e:
if DEBUG:
print("Database:__init__: Caught %s" % (e, ))
# engine.connect will throw sqlalchemy.exc exception
if isinstance(e, InternalError):
(num, msg) = e.orig.args
if DEBUG:
print("Database:__init__: (%d,%s)" % (
num,
msg,
))
if num != 1049 or msg != "Unknown database 'MoltenIron'":
# If it is not the above then reraise it!
raise
# It does not! Create it.
# CREATE DATABASE MoltenIron;
sqlalchemy_utils.create_database(engine.url)
engine = self.create_engine()
c = engine.connect()
c.close()
self.engine = engine
self.create_metadata()
self.blob_status = {
"element_info": [
# The following are returned from the query call
# field_name length special_fmt skip
("id", 4, int, False),
("name", 6, str, False),
("ipmi_ip", 16, str, False),
("blob", 40, str, False),
("status", 8, str, False),
("provisioned", 40, str, False),
# We add timeString
("time", 14, float, False),
("node_pool", 19, str, False),
]
}
self.baremetal_status = {
"element_info": [
# The following are returned from the query call
# field_name length special_fmt skip
("id", 4, int, False),
("name", 6, str, False),
("ipmi_ip", 9, str, False),
("ipmi_user", 11, str, False),
("ipmi_password", 15, str, False),
("port_hwaddr", 19, str, False),
("cpu_arch", 10, str, False),
("cpus", 6, int, False),
("ram_mb", 8, int, False),
("disk_gb", 9, int, False),
("status", 8, str, False),
("provisioned", 40, str, False),
# We add timeString
("time", 14, float, False),
("node_pool", 19, str, False),
]
}
# Pass map by reference but update our copy with the new information
self.baremetal_status = self.setup_status(**self.baremetal_status)
self.blob_status = self.setup_status(**self.blob_status)
from sqlalchemy_utils import database_exists, create_database
from flask import Flask
from flask_cors import CORS
from common.config import UPLOAD_FOLDER
from common.config import META_DATABASE_ENDPOINT
from common.config import DEFAULT_RUNTIME
from operators.runtime import runtime_client_getter
app = Flask(__name__)
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
app.config['SQLALCHEMY_DATABASE_URI'] = META_DATABASE_ENDPOINT
for i in range(5):
try:
if not database_exists(META_DATABASE_ENDPOINT):
create_database(META_DATABASE_ENDPOINT)
break
except Exception as e:
print(e)
print(
f"count {i+1}; retry to connect to database {META_DATABASE_ENDPOINT}"
)
time.sleep(3)
db = SQLAlchemy(app)
db.init_app(app)
#db.create_all()
CORS(app)
runtime_client = runtime_client_getter(DEFAULT_RUNTIME)
def _test_db_fixture_implementation(test_db_uri: str):
engine = create_engine(test_db_uri)
create_database(engine.url)
yield
drop_database(engine.url)
def create_or_verify_database(url,
galaxy_config_file,
engine_options={},
app=None,
map_install_models=False):
"""
Check that the database is use-able, possibly creating it if empty (this is
the only time we automatically create tables, otherwise we force the
user to do it using the management script so they can create backups).
1) Empty database --> initialize with latest version and return
2) Database older than migration support --> fail and require manual update
3) Database at state where migrate support introduced --> add version control information but make no changes (might still require manual update)
4) Database versioned but out of date --> fail with informative message, user must run "sh manage_db.sh upgrade"
"""
# Create the base database if it doesn't yet exist.
new_database = not database_exists(url)
if new_database:
template = app and getattr(app.config, "database_template", None)
encoding = app and getattr(app.config, "database_encoding", None)
create_kwds = {}
message = "Creating database for URI [%s]" % url
if template:
message += " from template [%s]" % template
create_kwds["template"] = template
if encoding:
message += " with encoding [%s]" % encoding
create_kwds["encoding"] = encoding
log.info(message)
create_database(url, **create_kwds)
# Create engine and metadata
engine = create_engine(url, **engine_options)
def migrate():
try:
# Declare the database to be under a repository's version control
db_schema = schema.ControlledSchema.create(engine,
migrate_repository)
except Exception:
# The database is already under version control
db_schema = schema.ControlledSchema(engine, migrate_repository)
# Apply all scripts to get to current version
migrate_to_current_version(engine, db_schema)
def migrate_from_scratch():
log.info("Creating new database from scratch, skipping migrations")
current_version = migrate_repository.version().version
mapping.init(file_path='/tmp',
url=url,
map_install_models=map_install_models,
create_tables=True)
schema.ControlledSchema.create(engine,
migrate_repository,
version=current_version)
db_schema = schema.ControlledSchema(engine, migrate_repository)
assert db_schema.version == current_version
migrate()
if app:
# skips the tool migration process.
app.new_installation = True
meta = MetaData(bind=engine)
if new_database:
migrate_from_scratch()
return
elif app and getattr(app.config, 'database_auto_migrate', False):
migrate()
return
# Try to load dataset table
try:
Table("dataset", meta, autoload=True)
except NoSuchTableError:
# No 'dataset' table means a completely uninitialized database.
log.info("No database, initializing")
migrate_from_scratch()
return
try:
hda_table = Table("history_dataset_association", meta, autoload=True)
except NoSuchTableError:
raise Exception(
"Your database is older than hg revision 1464:c7acaa1bb88f and will need to be updated manually"
)
# There is a 'history_dataset_association' table, so we (hopefully) have
# version 1 of the database, but without the migrate_version table. This
# happens if the user has a build from right before migration was added.
# Verify that this is true, if it is any older they'll have to update
# manually
if 'copied_from_history_dataset_association_id' not in hda_table.c:
# The 'copied_from_history_dataset_association_id' column was added in
# rev 1464:c7acaa1bb88f. This is the oldest revision we currently do
# automated versioning for, so stop here
raise Exception(
"Your database is older than hg revision 1464:c7acaa1bb88f and will need to be updated manually"
)
# At revision 1464:c7acaa1bb88f or greater (database version 1), make sure
# that the db has version information. This is the trickiest case -- we
# have a database but no version control, and are assuming it is a certain
# version. If the user has postion version 1 changes this could cause
# problems
try:
Table("migrate_version", meta, autoload=True)
except NoSuchTableError:
# The database exists but is not yet under migrate version control, so init with version 1
log.info("Adding version control to existing database")
try:
Table("metadata_file", meta, autoload=True)
schema.ControlledSchema.create(engine,
migrate_repository,
version=2)
except NoSuchTableError:
schema.ControlledSchema.create(engine,
migrate_repository,
version=1)
# Verify that the code and the DB are in sync
db_schema = schema.ControlledSchema(engine, migrate_repository)
if migrate_repository.versions.latest != db_schema.version:
config_arg = ''
if galaxy_config_file and os.path.abspath(
os.path.join(os.getcwd(), 'config',
'galaxy.ini')) != galaxy_config_file:
config_arg = ' -c %s' % galaxy_config_file.replace(
os.path.abspath(os.getcwd()), '.')
expect_msg = "Your database has version '%d' but this code expects version '%d'" % (
db_schema.version, migrate_repository.versions.latest)
instructions = ""
if db_schema.version > migrate_repository.versions.latest:
instructions = "To downgrade the database schema you have to checkout the Galaxy version that you were running previously. "
cmd_msg = "sh manage_db.sh%s downgrade %d" % (
config_arg, migrate_repository.versions.latest)
else:
cmd_msg = "sh manage_db.sh%s upgrade" % config_arg
backup_msg = "Please backup your database and then migrate the database schema by running '%s'." % cmd_msg
raise Exception("%s. %s%s" % (expect_msg, instructions, backup_msg))
else:
log.info("At database version %d" % db_schema.version)
from flask import Flask
from flask_bcrypt import Bcrypt
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy_utils import database_exists, create_database
from kazoo.client import KazooClient
from kazoo.exceptions import NodeExistsError, ConnectionLossException
import json
db_engine = create_engine(constant.DATABASE_BASE_URL + constant.DATABASE_NAME)
if not database_exists(db_engine.url):
create_database(db_engine.url)
def registerUserManagementService(host, port):
try:
zk = KazooClient(hosts='zookeeper', read_only=True)
zk.start()
path = '/user_management'
data = json.dumps({'host': host, 'port': port}).encode('utf-8')
zk.create(path, value=data, ephemeral=True, makepath=True)
print('user_management service is running on ' + path + ':' +
str(port))
except NodeExistsError:
print('Node already exists in zookeeper')
except ConnectionLossException:
def createdb():
if not database_exists(URI):
create_database(URI)
print("資料庫已經存在")
db.create_all()
return redirect(url_for("main.index"))
app.static_path = path.join(path.abspath(__file__), 'static')
app.config['SQLALCHEMY_DATABASE_URI'] = DB.SQLALCHEMY_DATABASE_URI
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = DB.SQLALCHEMY_TRACK_MODIFICATIONS
app.config['SECRET_KEY'] = DB.SECRET_KEY
Bootstrap(app)
db.init_app(app)
make_searchable(db.metadata)
with app.test_request_context():
if not database_exists(DB.SQLALCHEMY_DATABASE_URI):
create_database(DB.SQLALCHEMY_DATABASE_URI)
from auth.models import User, WorkingTimeRecord, LeaveApplication
from invoices.models import Products, Customers, Invoices, Basket, Quantities, Suppliers, Orders
sa.orm.configure_mappers()
db.create_all()
db.session.commit()
if not User.query.filter_by(username='******').first():
init_admin()
from home.views import home_blueprint
from auth.views import auth_blueprint
from invoices.views import invoices_blueprint
from stock.views import stock_blueprint
def create_database(self):
"""
create database method
"""
create_database(self.engine.url)
logging.debug("Database created!")
# Initialize Flask app
app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = DB_URL
with app.app_context():
db.init_app(app)
if reset_db:
if database_exists(DB_URL):
print('Deleting database.')
drop_database(DB_URL)
if not database_exists(DB_URL):
print('Creating database.')
create_database(DB_URL)
db.create_all()
db.session.commit()
else:
db.create_all()
db.session.commit()
user_data = [('*****@*****.**', 'Jack Smith', sha256_crypt.hash('password1')), ('*****@*****.**', 'Jane Doe', sha256_crypt.hash('password2')), \
('*****@*****.**', 'Bubble Owner', sha256_crypt.hash('bubbles_pass')), ('*****@*****.**', 'Ron LaFlamme', sha256_crypt.hash('moneymoneymoney')), \
('*****@*****.**', 'Sarah Example', sha256_crypt.hash('imastudent')), ('*****@*****.**', 'Coney Owner', sha256_crypt.hash('coneyconey1234'))]
def create(uri):
log.debug(f'Creating database at {uri}')
create_database(uri)
def recreate_pg(self):
if database_exists(self.conn):
drop_database(self.conn)
create_database(self.conn)
def db_engine(base_app):
test_db_engine = create_engine(base_app.config['SQLALCHEMY_DATABASE_URI'])
if not database_exists(test_db_engine.url):
create_database(test_db_engine.url)
yield test_db_engine
drop_database(test_db_engine.url)
def start_session(self):
create_if_missing = self._source.create_if_missing
is_database_missing = lambda: not database_exists(self._source.url)
if create_if_missing and is_database_missing():
create_database(self._source.url)
self._session = self._SessionClass()
