def runTest(self):
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
from sqlalchemy import create_engine
engine = create_engine('mysql://root@localhost/test')
self.assertEqual(engine.dialect.name, 'mysql')
self.assertEqual(engine.driver, 'pymysql')
def generate_getconn(engine, user, password, host, port, dbname, dirname=None):
kwargs = {}
if engine == 'mysql':
# If using mysql, choose among MySQLdb or pymysql,
# Trying to load MySQLdb, and if it fails, trying
# to load and register pymysql
try:
import MySQLdb
assert MySQLdb is not None # It can never be: just avoid pyflakes warnings
except ImportError:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
# In the case of MySQL, we need to activate this flag
kwargs['client_flag'] = 2
elif engine == 'sqlite':
# By default, sqlite uses a timeout of 5 seconds. Given the
# concurrency levels that WebLab-Deusto might achieve with
# multiple users in a queue, this might not be enough. We
# increase it to a minute and a half to avoid problems with
# multiple concurrent users
kwargs['timeout'] = 90
if dbname == ':memory:':
kwargs['check_same_thread'] = False
# Then load the sqlalchemy dialect. In order to do the
# equivalent to:
#
# from sqlalchemy.dialects.mysql import base
# dbi = base.dialect.dbapi()
#
# We import the module itself (sqlalchemy.dialects.mysql)
import sqlalchemy.dialects as dialects
__import__('sqlalchemy.dialects.%s' % engine)
# And once imported, we take the base.dialect.dbapi
dbi = getattr(dialects, engine).base.dialect.dbapi()
if engine == 'sqlite':
def getconn_sqlite():
return dbi.connect(database=get_sqlite_dbname(dbname, dirname),
**kwargs)
getconn = getconn_sqlite
else:
def getconn_else():
kwargs.update(
dict(user=user, passwd=password, host=host, db=dbname))
if port is not None:
kwargs['port'] = port
return dbi.connect(**kwargs)
getconn = getconn_else
return getconn
def generate_getconn(engine, user, password, host, port, dbname, dirname = None):
kwargs = {}
if engine == 'mysql':
# If using mysql, choose among MySQLdb or pymysql,
# Trying to load MySQLdb, and if it fails, trying
# to load and register pymysql
try:
import MySQLdb
assert MySQLdb is not None # It can never be: just avoid pyflakes warnings
except ImportError:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
# In the case of MySQL, we need to activate this flag
kwargs['client_flag'] = 2
elif engine == 'sqlite':
# By default, sqlite uses a timeout of 5 seconds. Given the
# concurrency levels that WebLab-Deusto might achieve with
# multiple users in a queue, this might not be enough. We
# increase it to a minute and a half to avoid problems with
# multiple concurrent users
kwargs['timeout'] = 90
if dbname == ':memory:':
kwargs['check_same_thread'] = False
# Then load the sqlalchemy dialect. In order to do the
# equivalent to:
#
# from sqlalchemy.dialects.mysql import base
# dbi = base.dialect.dbapi()
#
# We import the module itself (sqlalchemy.dialects.mysql)
import sqlalchemy.dialects as dialects
__import__('sqlalchemy.dialects.%s' % engine)
# And once imported, we take the base.dialect.dbapi
dbi = getattr(dialects, engine).base.dialect.dbapi()
if engine == 'sqlite':
def getconn_sqlite():
return dbi.connect(database = get_sqlite_dbname(dbname, dirname), **kwargs)
getconn = getconn_sqlite
else:
def getconn_else():
kwargs.update(dict(user = user, passwd = password, host = host, db = dbname))
if port is not None:
kwargs['port'] = port
return dbi.connect(**kwargs)
getconn = getconn_else
return getconn
def __init__(self, url):
if url.startswith('mysql://'):
try:
import MySQLdb
assert MySQLdb is not None # avoid warnings
except ImportError:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
self.url = url
self.config = Config(os.path.join(self.alembic_path, "alembic.ini"))
self.config.set_main_option("script_location", self.alembic_path)
self.config.set_main_option("url", self.url)
self.config.set_main_option("sqlalchemy.url", self.url)
def __init__(self, url):
if url.startswith('mysql://'):
try:
import MySQLdb
assert MySQLdb is not None # avoid warnings
except ImportError:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
self.url = url
self.config = Config(os.path.join(self.alembic_path, "alembic.ini"))
self.config.set_main_option("script_location", self.alembic_path)
self.config.set_main_option("url", self.url)
self.config.set_main_option("sqlalchemy.url", self.url)
def deploy_testdb(options):
from weblab.admin.deploy import insert_required_initial_data, populate_weblab_tests, generate_create_database
import weblab.db.model as Model
import weblab.core.coordinator.sql.model as CoordinatorModel
import voodoo.sessions.db_lock_data as DbLockData
import voodoo.sessions.sqlalchemy_data as SessionSqlalchemyData
from sqlalchemy import create_engine
try:
import MySQLdb
dbi = MySQLdb
except ImportError:
try:
import pymysql_sa
except ImportError:
raise Exception("Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'")
pymysql_sa.make_default_mysql_dialect()
t_initial = time.time()
db_dir = 'db'
if not os.path.exists(db_dir):
os.mkdir(db_dir)
db_engine = options.testdb_engine
weblab_db_username = options.testdb_user
weblab_db_password = options.testdb_passwd
weblab_admin_db_username = options.testdb_admin_user
weblab_admin_db_password = options.testdb_admin_passwd
if db_engine == 'mysql':
weblab_test_db_str = 'mysql://%s:%s@localhost/WebLabTests%s' % (weblab_db_username, weblab_db_password,'%s')
weblab_coord_db_str = 'mysql://%s:%s@localhost/WebLabCoordination%s' % (weblab_db_username, weblab_db_password, '%s')
weblab_sessions_db_str = 'mysql://%s:%s@localhost/WebLabSessions' % (weblab_db_username, weblab_db_password)
elif db_engine == 'sqlite':
weblab_test_db_str = 'sqlite:///db/WebLabTests%s.db'
weblab_coord_db_str = 'sqlite:///db/WebLabCoordination%s.db'
weblab_sessions_db_str = 'sqlite:///db/WebLabSessions.db'
else:
raise Exception("db engine %s not supported" % db_engine)
if options.testdb_create_db:
create_database = generate_create_database(db_engine)
if create_database is None:
raise Exception("db engine %s not supported for creating database" % db_engine)
t = time.time()
error_message = 'Could not create database. This may happen if the admin db credentials are wrong.'
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLab", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests2", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabTests3", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination2", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabCoordination3", weblab_db_username, weblab_db_password, db_dir = db_dir)
create_database(error_message, weblab_admin_db_username, weblab_admin_db_password, "WebLabSessions", weblab_db_username, weblab_db_password, db_dir = db_dir)
print "Databases created.\t\t\t\t[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating main database
#
for tests in ('','2','3'):
print "Populating 'WebLabTests%s' database... \t\t" % tests,
t = time.time()
engine = create_engine(weblab_test_db_str % tests, echo = False)
metadata = Model.Base.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
insert_required_initial_data(engine)
populate_weblab_tests(engine, tests)
print "[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating Coordination database
#
for coord in ('','2','3'):
print "Populating 'WebLabCoordination%s' database...\t" % coord,
t = time.time()
engine = create_engine(weblab_coord_db_str % coord, echo = False)
CoordinatorModel.load()
metadata = CoordinatorModel.Base.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
print "[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating Sessions database
#
print "Populating 'WebLabSessions' database...\t\t",
t = time.time()
engine = create_engine(weblab_sessions_db_str, echo = False)
metadata = DbLockData.SessionLockBase.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
metadata = SessionSqlalchemyData.SessionBase.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
print "[done] [%1.2fs]" % (time.time() - t)
print "Total database deployment: \t\t\t[done] [%1.2fs]" % (time.time() - t_initial)
def generate_create_database(engine_str):
"Generate a create_database function that creates the database"
if engine_str == 'sqlite':
import sqlite3
dbi = sqlite3
def create_database_sqlite(admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'):
fname = os.path.join(db_dir, '%s.db' % database_name)
if os.path.exists(fname):
os.remove(fname)
sqlite3.connect(database = fname).close()
return create_database_sqlite
elif engine_str == 'mysql':
try:
import MySQLdb
dbi = MySQLdb
except ImportError:
try:
import pymysql_sa
except ImportError:
raise Exception("Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'")
pymysql_sa.make_default_mysql_dialect()
import pymysql
dbi = pymysql
def create_database_mysql(error_message, admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'):
args = {
'DATABASE_NAME' : database_name,
'USER' : new_user,
'PASSWORD' : new_password,
}
sentence1 = "DROP DATABASE IF EXISTS %(DATABASE_NAME)s;" % args
sentence2 = "CREATE DATABASE %(DATABASE_NAME)s;" % args
sentence3 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'%%' IDENTIFIED BY '%(PASSWORD)s';" % args
sentence4 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'localhost' IDENTIFIED BY '%(PASSWORD)s';" % args
sentence5 = "FLUSH PRIVILEGES;" % args
try:
kwargs = dict(db=database_name, user = admin_username, passwd = admin_password, host = host)
if port is not None:
kwargs['port'] = port
dbi.connect(**kwargs).close()
except Exception, e:
if e[1].startswith("Unknown database"):
sentence1 = "SELECT 1"
for sentence in (sentence1, sentence2, sentence3, sentence4, sentence5):
try:
kwargs = dict(user = admin_username, passwd = admin_password, host = host)
if port is not None:
kwargs['port'] = port
connection = dbi.connect(**kwargs)
except dbi.OperationalError:
traceback.print_exc()
print("", file=sys.stderr)
print(" %s" % error_message, file=sys.stderr)
print("", file=sys.stderr)
sys.exit(-1)
else:
cursor = connection.cursor()
cursor.execute(sentence)
connection.commit()
connection.close()
return create_database_mysql
def generate_create_database(engine_str):
"Generate a create_database function that creates the database"
if engine_str == 'sqlite':
import sqlite3
dbi = sqlite3
def create_database_sqlite(admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'):
fname = os.path.join(db_dir, '%s.db' % database_name)
if os.path.exists(fname):
os.remove(fname)
sqlite3.connect(database = fname).close()
return create_database_sqlite
elif engine_str == 'mysql':
try:
import MySQLdb
dbi = MySQLdb
except ImportError:
try:
import pymysql_sa
except ImportError:
raise Exception("Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'")
pymysql_sa.make_default_mysql_dialect()
import pymysql
dbi = pymysql
def create_database_mysql(error_message, admin_username, admin_password, database_name, new_user, new_password, host = "localhost", port = None, db_dir = '.'):
args = {
'DATABASE_NAME' : database_name,
'USER' : new_user,
'PASSWORD' : new_password,
}
sentence1 = "DROP DATABASE IF EXISTS %(DATABASE_NAME)s;" % args
sentence2 = "CREATE DATABASE %(DATABASE_NAME)s;" % args
sentence3 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'%%' IDENTIFIED BY '%(PASSWORD)s';" % args
sentence4 = "GRANT ALL ON %(DATABASE_NAME)s.* TO '%(USER)s'@'localhost' IDENTIFIED BY '%(PASSWORD)s';" % args
sentence5 = "FLUSH PRIVILEGES;" % args
try:
kwargs = dict(db=database_name, user = admin_username, passwd = admin_password, host = host)
if port is not None:
kwargs['port'] = port
dbi.connect(**kwargs).close()
except Exception, e:
if e[1].startswith("Unknown database"):
sentence1 = "SELECT 1"
for sentence in (sentence1, sentence2, sentence3, sentence4, sentence5):
try:
kwargs = dict(user = admin_username, passwd = admin_password, host = host)
if port is not None:
kwargs['port'] = port
connection = dbi.connect(**kwargs)
except dbi.OperationalError:
traceback.print_exc()
print("", file=sys.stderr)
print(" %s" % error_message, file=sys.stderr)
print("", file=sys.stderr)
sys.exit(-1)
else:
cursor = connection.cursor()
cursor.execute(sentence)
connection.commit()
connection.close()
return create_database_mysql
"""
import json
import hashlib
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from labmanager import app
SQLALCHEMY_ENGINE_STR = app.config['SQLALCHEMY_ENGINE_STR']
USE_PYMYSQL = app.config.get('USE_PYMYSQL', False)
if USE_PYMYSQL:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
engine = create_engine(SQLALCHEMY_ENGINE_STR,
convert_unicode=True,
pool_recycle=3600)
db_session = scoped_session(
sessionmaker(autocommit=False, autoflush=False, bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
def init_db(drop=False):
# import all modules here that might define models so that
# they will be registered properly on the metadata. Otherwise
# you will have to import them first before calling init_db()
"""
import json
import hashlib
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from labmanager import app
SQLALCHEMY_ENGINE_STR = app.config['SQLALCHEMY_ENGINE_STR']
USE_PYMYSQL = app.config.get('USE_PYMYSQL', False)
if USE_PYMYSQL:
import pymysql_sa
pymysql_sa.make_default_mysql_dialect()
engine = create_engine(SQLALCHEMY_ENGINE_STR, convert_unicode=True, pool_recycle=3600)
db_session = scoped_session(sessionmaker(autocommit=False,
autoflush=False,
bind=engine))
Base = declarative_base()
Base.query = db_session.query_property()
def init_db(drop = False):
# import all modules here that might define models so that
# they will be registered properly on the metadata. Otherwise
# you will have to import them first before calling init_db()
import labmanager.models
assert labmanager.models != None # pyflakes ignore
def deploy_testdb(options):
from weblab.admin.deploy import insert_required_initial_data, populate_weblab_tests, generate_create_database
import weblab.db.model as Model
import weblab.core.coordinator.sql.model as CoordinatorModel
import voodoo.sessions.db_lock_data as DbLockData
import voodoo.sessions.sqlalchemy_data as SessionSqlalchemyData
from sqlalchemy import create_engine
try:
import MySQLdb
dbi = MySQLdb
except ImportError:
try:
import pymysql_sa
except ImportError:
raise Exception(
"Neither MySQLdb nor pymysql have been installed. First install them by running 'pip install pymysql' or 'pip install python-mysql'"
)
pymysql_sa.make_default_mysql_dialect()
t_initial = time.time()
db_dir = 'db'
if not os.path.exists(db_dir):
os.mkdir(db_dir)
db_engine = options.testdb_engine
weblab_db_username = options.testdb_user
weblab_db_password = options.testdb_passwd
weblab_admin_db_username = options.testdb_admin_user
weblab_admin_db_password = options.testdb_admin_passwd
if db_engine == 'mysql':
weblab_test_db_str = 'mysql://%s:%s@localhost/WebLabTests%s' % (
weblab_db_username, weblab_db_password, '%s')
weblab_coord_db_str = 'mysql://%s:%s@localhost/WebLabCoordination%s' % (
weblab_db_username, weblab_db_password, '%s')
weblab_sessions_db_str = 'mysql://%s:%s@localhost/WebLabSessions' % (
weblab_db_username, weblab_db_password)
elif db_engine == 'sqlite':
weblab_test_db_str = 'sqlite:///db/WebLabTests%s.db'
weblab_coord_db_str = 'sqlite:///db/WebLabCoordination%s.db'
weblab_sessions_db_str = 'sqlite:///db/WebLabSessions.db'
else:
raise Exception("db engine %s not supported" % db_engine)
if options.testdb_create_db:
create_database = generate_create_database(db_engine)
if create_database is None:
raise Exception(
"db engine %s not supported for creating database" % db_engine)
t = time.time()
error_message = 'Could not create database. This may happen if the admin db credentials are wrong.'
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLab",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabTests",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabTests2",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabTests3",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabCoordination",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabCoordination2",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabCoordination3",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
create_database(error_message,
weblab_admin_db_username,
weblab_admin_db_password,
"WebLabSessions",
weblab_db_username,
weblab_db_password,
db_dir=db_dir)
print "Databases created.\t\t\t\t[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating main database
#
for tests in ('', '2', '3'):
print "Populating 'WebLabTests%s' database... \t\t" % tests,
t = time.time()
engine = create_engine(weblab_test_db_str % tests, echo=False)
metadata = Model.Base.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
insert_required_initial_data(engine)
populate_weblab_tests(engine, tests)
print "[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating Coordination database
#
for coord in ('', '2', '3'):
print "Populating 'WebLabCoordination%s' database...\t" % coord,
t = time.time()
engine = create_engine(weblab_coord_db_str % coord, echo=False)
CoordinatorModel.load()
metadata = CoordinatorModel.Base.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
print "[done] [%1.2fs]" % (time.time() - t)
#####################################################################
#
# Populating Sessions database
#
print "Populating 'WebLabSessions' database...\t\t",
t = time.time()
engine = create_engine(weblab_sessions_db_str, echo=False)
metadata = DbLockData.SessionLockBase.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
metadata = SessionSqlalchemyData.SessionBase.metadata
metadata.drop_all(engine)
metadata.create_all(engine)
print "[done] [%1.2fs]" % (time.time() - t)
print "Total database deployment: \t\t\t[done] [%1.2fs]" % (time.time() -
t_initial)
