def cropsExist(self):
# check if the database is already up to date: i.e. there is "summary" in standardofliving
# checks in assets
config = self.config
dbinfo = config.dbinfo().copy()
db = Connect(**dbinfo)
cursor = db.cursor()
query = "SHOW COLUMNS FROM standardofliving"
## will change to:
# query = "SELECT * FROM setup_foods_crops"
## if it returns some rows then returns otherwise returns false
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
db.close()
upToDate = False
for row in rows:
for field in row:
if field == "null":
upToDate = True
return upToDate
def initialiseDB(self):
config = self.config
dbinfo = config.dbinfo().copy()
# assume that mysql server is running and database is installed already
mysqlstarted = True
dbinstalled = False
# try connecting to database
try:
db = Connect(**dbinfo)
db.close()
dbinstalled = True
# interfaceError occurs if MySQL server is not running
except errors.InterfaceError:
mysqlstarted = False
# OperationalError or ProgrammingError happen if the db does not exist
except (errors.OperationalError, errors.ProgrammingError) as e:
dbinstalled = self.createDatabase()
self.setupStartupCrops() # initialise setup_foods_crops table
dbuptodate = False
if mysqlstarted == True and dbinstalled == True:
dbuptodate = self.updateDatabase()
# self.insertStartupCrops()
dbstatus = dict()
dbstatus["mysqlstarted"] = mysqlstarted
dbstatus["dbinstalled"] = dbinstalled
dbstatus["dbuptodate"] = dbuptodate
return dbstatus
def insertStartupCrops(self):
# if database is already up to date return
if self.cropsExist():
return True
# else update the database
else:
sqlfile = file("data/scripts/openihmdb_mysql_fix59.sql", "r")
commands = sqlfile.read()
commandlist = commands.split(";")
sqlfile.close()
try:
dbinfo = self.config.superuser_dbinfo().copy()
db = Connect(**dbinfo)
cursor = db.cursor()
for command in commandlist:
if not command.isspace():
cursor.execute(command)
cursor.close()
db.close()
return True
except errors.InterfaceError, e:
print e
return False
except (errors.OperationalError, errors.ProgrammingError) as e:
print e
return False
def execute_select(self, query, data=None):
"""
Yet another wrapper around queries
"""
db = Connect(**self.config.superuser_dbinfo().copy())
cursor = db.cursor()
cursor.execute(query, data)
yield cursor.fetchall()
db.close()
def createDatabase(self):
sqlfile = file("data/scripts/openihmdb_mysql.sql", "r")
commands = sqlfile.read()
commandlist = commands.split(";")
sqlfile.close()
try:
dbinfo = self.config.superuser_dbinfo().copy()
del dbinfo["database"]
db = Connect(**dbinfo)
cursor = db.cursor()
for command in commandlist:
if not command.isspace():
cursor.execute(command)
updatestr = "latest update on %s" % (date.today().isoformat())
query = "INSERT INTO dbupdate VALUES('%s')" % updatestr
cursor.execute(query)
db.commit()
cursor.close()
db.close()
return True
except errors.InterfaceError, e:
return False
def _ddl_command(self, query, params=None):
config = self.config.superuser_dbinfo().copy()
config['database'] = 'mysql'
db = Connect(**config)
cursor = db.cursor()
cursor.execute(query, params)
db.commit()
db.close()
def setupStartupCrops(self):
""" Initialise table setup_foods_crops at database creation time"""
sqlfile = file("data/scripts/openihmdb_mysql_fix59.sql", "r")
commands = sqlfile.read()
commandlist = commands.split(";")
sqlfile.close()
try:
dbinfo = self.config.superuser_dbinfo().copy()
db = Connect(**dbinfo)
cursor = db.cursor()
for command in commandlist:
if not command.isspace():
cursor.execute(command)
cursor.close()
db.close()
return True
except errors.InterfaceError, e:
print e
return False
def execute_instruction(self, query, data=None):
"""
Yet another wrapper around execute
"""
db = Connect(**self.config.superuser_dbinfo().copy())
cursor = db.cursor()
cursor.execute(query, data)
db.commit()
db.close()
return cursor
def updateDatabase(self):
# if database is already up to date return
if self.databaseUpToDate():
return True
# else update the database
else:
sqlfile = file("data/scripts/openihmdb_mysql_update.sql", "r")
commands = sqlfile.read()
commandlist = commands.split(";")
sqlfile.close()
try:
dbinfo = self.config.superuser_dbinfo().copy()
db = Connect(**dbinfo)
cursor = db.cursor()
for command in commandlist:
if not command.isspace():
cursor.execute(command)
updatestr = "latest update on %s" % (date.today().isoformat())
query = "UPDATE dbupdate SET lastupdate='%s'" % updatestr
cursor.execute(query)
db.commit()
cursor.close()
db.close()
return True
except errors.InterfaceError, e:
print e
return False
except (errors.OperationalError, errors.ProgrammingError) as e:
print e
return False
def databaseUpToDate(self):
# check if the database is already up to date: i.e. there is "summary" in standardofliving
# checks in assets
config = self.config
dbinfo = config.dbinfo().copy()
db = Connect(**dbinfo)
cursor = db.cursor()
query = "SHOW TABLES"
cursor.execute(query)
rows = cursor.fetchall()
upToDate = False
for row in rows:
if row[0] == "dbupdate":
query = "SELECT lastupdate FROM dbupdate"
cursor.execute(query)
rows = cursor.fetchall()
if len(rows) != 0:
for row in rows:
if row[0] > self.latestupdatestring:
upToDate = True
else:
updatestr = "latest update on %s" % (date.today().isoformat())
query = "INSERT INTO dbupdate VALUES('%s')" % updatestr
cursor.execute(query)
db.commit()
upToDate = True
cursor.close()
db.close()
return upToDate
def createDatabase(self):
sqlfile = file('data/scripts/openihmdb_mysql.sql', 'r')
commands = sqlfile.read()
commandlist = commands.split(';')
sqlfile.close()
try:
dbinfo = self.config.superuser_dbinfo().copy()
database = dbinfo['database']
del dbinfo['database']
db = Connect(**dbinfo)
cursor = db.cursor()
# FIXME: long term we should look at changing the character set.
cursor.execute("CREATE SCHEMA IF NOT EXISTS `%s` DEFAULT CHARACTER SET latin1 ;" % database)
cursor.execute("USE `%s`;" % database)
for command in commandlist:
if ( not command.isspace() ):
cursor.execute(command)
try:
if self.config.user != self.config.superuser:
cursor.execute("GRANT ALL ON %s.* TO %s@localhost IDENTIFIED BY '%s';" %
(database, self.config.user, self.config.password))
except ( errors.OperationalError, errors.ProgrammingError) as e:
print e.msg
updatestr = "latest update on %s" % (date.today().isoformat())
query = "INSERT INTO dbupdate VALUES('%s')" % updatestr
cursor.execute(query)
db.commit()
cursor.close()
db.close()
return True
except errors.InterfaceError, e:
return False
