def get_data_from_db(db_url):
db = DataBase(db_url)
if db.connect():
if not db.table_exists("inf_list"):
db.execute("CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, date TIMESTAMP, data LONGBLOB)")
db.close()
return {}
else:
inf_list = {}
res = db.select("select * from inf_list order by id desc")
if len(res) > 0:
for elem in res:
#inf_id = elem[0]
#date = elem[1]
try:
inf = pickle.loads(elem[2])
if not inf.deleted:
inf_list[inf.id] = inf
except:
InfrastructureManager.logger.exception("ERROR reading infrastructure %d from database, ignoring it!." % inf.id)
else:
InfrastructureManager.logger.error("ERROR getting inf_list from database!.")
db.close()
return inf_list
else:
InfrastructureManager.logger.error("ERROR connecting with the database!.")
return {}
def _reinit():
"""Restart the class attributes to initial values."""
InfrastructureList.infrastructure_list = {}
InfrastructureList._lock = threading.Lock()
db = DataBase(Config.DATA_DB)
if db.connect():
db.execute("delete from inf_list")
db.close()
def delete_data_from_db(db_url, date):
db = DataBase(db_url)
if db.connect():
db.execute("DELETE FROM inf_list WHERE deleted = 1 and date < '%s';" % date)
db.close()
else:
sys.stderr.write("ERROR connecting with the database!.")
sys.exit(-1)
def test_sqlite_db(self):
filename = "/tmp/inf.dat"
if os.path.exists(filename):
os.unlink(filename)
db_url = "sqlite://" + filename
db = DataBase(db_url)
self.assertTrue(db.connect())
if not db.table_exists("test"):
db.execute("CREATE TABLE test(id int PRIMARY KEY, date TIMESTAMP, data LONGBLOB)")
self.assertTrue(db.table_exists("test"))
db.execute("insert into test (id, data, date) values (%s, %s, now())", (1, "Data"))
res = db.select("select data from test where id = %s", (1,))
self.assertEqual(res, [("Data",)])
db.close()
def init_table():
""" Creates de database """
db = DataBase(Config.DATA_DB)
if db.connect():
if not db.table_exists("inf_list"):
db.execute(
"CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, deleted INTEGER,"
" date TIMESTAMP, data LONGBLOB)")
db.close()
return True
else:
InfrastructureList.logger.error(
"ERROR connecting with the database!.")
return False
def _save_data_to_db(db_url, inf_list, inf_id=None):
db = DataBase(db_url)
if db.connect():
infs_to_save = inf_list
if inf_id:
infs_to_save = {inf_id: inf_list[inf_id]}
for inf in infs_to_save.values():
data = inf.serialize()
if db.db_type == DataBase.MONGO:
res = db.replace("inf_list", {"id": inf.id}, {
"id": inf.id,
"deleted": int(inf.deleted),
"data": data,
"date": time.time()
})
else:
res = db.execute(
"replace into inf_list (id, deleted, data, date) values (%s, %s, %s, now())",
(inf.id, int(inf.deleted), data))
db.close()
return res
else:
InfrastructureList.logger.error(
"ERROR connecting with the database!.")
return None
def init_table():
""" Creates de database """
db = DataBase(Config.DATA_DB)
if db.connect():
if not db.table_exists("inf_list"):
InfrastructureList.logger.debug("Creating the IM database!.")
if db.db_type == DataBase.MYSQL:
db.execute("CREATE TABLE inf_list(rowid INTEGER NOT NULL AUTO_INCREMENT UNIQUE,"
" id VARCHAR(255) PRIMARY KEY, deleted INTEGER, date TIMESTAMP, data LONGBLOB)")
elif db.db_type == DataBase.SQLITE:
db.execute("CREATE TABLE inf_list(id VARCHAR(255) PRIMARY KEY, deleted INTEGER,"
" date TIMESTAMP, data LONGBLOB)")
db.close()
return True
else:
InfrastructureList.logger.error("ERROR connecting with the database!.")
return False
def test_mysql_db(self, mdb_conn):
connection = MagicMock()
mdb_conn.return_value = connection
db_url = "mysql://*****:*****@server/db_name"
db = DataBase(db_url)
self.assertTrue(db.connect())
if not db.table_exists("test"):
db.execute("CREATE TABLE test(id int PRIMARY KEY, date TIMESTAMP, data LONGBLOB)")
db.execute("insert into test (id, data, date) values (%s, %s, now())", (1, "Data"))
cursor = MagicMock()
cursor.fetchall.return_value = [("Data",)]
connection.cursor.return_value = cursor
res = db.select("select data from test where id = %s", (1,))
self.assertEqual(res, [("Data",)])
db.close()
def rename_old_data():
db = DataBase(Config.DATA_DB)
if db.connect():
if db.table_exists("inf_list"):
now = str(int(time.time() * 100))
if db.db_type == DataBase.SQLITE:
db.execute('ALTER TABLE inf_list RENAME TO inf_list_%s;' % now)
db.close()
elif db.db_type == DataBase.MYSQL:
db.execute('RENAME TABLE inf_list TO inf_list_%s;' % now)
db.close()
else:
db.close()
sys.stderr.write("ERROR connecting with the database!.")
sys.exit(-1)
else:
db.close()
else:
sys.stderr.write("ERROR connecting with the database!.")
sys.exit(-1)
def save_data_to_db(db_url, inf_list, inf_id = None):
db = DataBase(db_url)
if db.connect():
infs_to_save = inf_list
if inf_id:
infs_to_save = {inf_id: inf_list[inf_id]}
for inf in infs_to_save.values():
res = db.execute("replace into inf_list set id = %s, data = %s, date = now()", (inf.id, pickle.dumps(inf)))
db.close()
return res
else:
InfrastructureManager.logger.error("ERROR connecting with the database!.")
return None
def _save_data_to_db(db_url, inf_list, inf_id=None):
db = DataBase(db_url)
if db.connect():
infs_to_save = inf_list
if inf_id:
infs_to_save = {inf_id: inf_list[inf_id]}
for inf in infs_to_save.values():
res = db.execute("replace into inf_list (id, deleted, data, date) values (%s, %s, %s, now())",
(inf.id, int(inf.deleted), inf.serialize()))
db.close()
return res
else:
InfrastructureList.logger.error("ERROR connecting with the database!.")
return None
def insertRecipe(name,
version,
module,
recipe,
desc,
requirements,
galaxy_module=None,
isapp=0):
""" Static method to insert a recipe in the DB """
if not DataBase.db_available:
return False
else:
try:
db = DataBase(Config.RECIPES_DB_FILE)
db.connect()
res = db.execute(
'''insert into recipes values ("%s", "%s", "%s", "%s", %d, %d, "%s", "%s")'''
% (name, version, module, recipe, isapp, galaxy_module,
desc, requirements))
return res
except Exception:
return False
sys.path.append("..")
sys.path.append(".")
from IM.config import Config
from IM.db import DataBase
if __name__ == "__main__":
if not Config.DATA_DB:
sys.stderr.write("No DATA_DB defined in the im.cfg file!!")
sys.exit(-1)
db = DataBase(Config.DATA_DB)
if db.connect():
if db.table_exists("inf_list"):
if db.db_type == DataBase.MYSQL:
sys.stdout.write("Updating DB: %s.\n" % Config.DATA_DB)
db.execute(
"ALTER TABLE `inf_list` ADD COLUMN `rowid` INT AUTO_INCREMENT UNIQUE FIRST;"
)
else:
sys.stdout.write("SQLite DB does not need to be updated.")
db.close()
else:
sys.stdout.write(
"There are no inf_list table. Do not need to update.")
else:
sys.stderr.write("Error connecting with DB: %s\n" % Config.DATA_DB)
sys.exit(-1)
sys.exit(0)
