maldisoft/maldisql.py

import MySQLdb

class maldisql:
	def __init__(self, host="localhost", db="maldisoft", user="maldiuser"):
		passwd = ""
		self.db = MySQLdb.connect(host=host, db=db, user=user, passwd=passwd)
		self.db.autocommit(True)
		self.c = self.db.cursor(MySQLdb.cursors.DictCursor)
	
	# New user functions
	def AddUser(self, email, passwd, name, last):
		try:
			self.c.execute("""INSERT into USER (email, passwd, name, last, lastaccess) values 
			(%s, %s, %s, %s, CURRENT_TIMESTAMP)""", 
			(email, passwd, name, last))

		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)
		
		except MySQLdb.Error, e: 
			return e
		
		return self.c.lastrowid

	def GetUser(self, uid):
		try:
			self.c.execute("""select email, name, last, lastaccess from USER where uid=%s""", uid)
		except MySQLdb.Error, e:
			return e

		return self.c.fetchone()

	def UpdatePassword(self, pwd, uid):
		try:
			self.c.execute("""update USER set passwd=%s where uid=%s""", (pwd, uid))

		except MySQLdb.Error, e:
			return e

		return None

	def UpdateUser(self, uid, email, name, last):
		try:
			self.c.execute("""update USER set email=%s, name=%s, last=%s where uid=%s""", (email, name, last, uid))
		except MySQLdb.Error, e:
			return e

		return None

	def ChangePassword(self, passwd):
		try:
			self.c.execute("""update USER set passwd=%s""", (passwd,))
		except MySQLdb.Error, e:
			return e
		return None

	def ChangeEmail(self, email, uid):
		try:
			self.c.execute("""update USER set email=%s where uid=%s""", (email,uid))
		except MySQLdb.Error, e:
			return e

		return None

	def Login(self, email, passwd):
		try:
			self.c.execute("""select uid, passwd from USER where email=%s""", email)
		except MySQLdb.Error, e:
			return None

		user=self.c.fetchone()
		if user and user["passwd"] == passwd:
			try:
				self.c.execute("""update USER set lastaccess=CURRENT_TIMESTAMP where uid=%s""", user["uid"])
			except:
				return None

			return user["uid"]
		else:
			return None

	# Create experiment functions

	def CreateExperiment(self, name, description, uid):
		try:
			self.c.execute("""INSERT into EXPERIMENT (uid, name, description) values 
				(%s, %s, %s)""",
				(uid, name, description))
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)

		except MySQLdb.Error, e:
			return e

		return int(self.c.lastrowid)


	def UpdateExperiment(self, name, description, eid):
		try:
			self.c.execute("""update EXPERIMENT set name=%s, description=%s where eid=%s""",
				(name, description, eid))
		except MySQLdb.Error, e:
			return e

		return None

	def DeleteExperiment(self, eid):
		try:
			self.c.execute("""delete from EXPERIMENT where eid=%s""", eid)
		except:
			return False

		return True

	def GetExperiments(self, uid):
		try:
			self.c.execute("""select eid, name, description, date from EXPERIMENT where uid=%s""", uid)
	
		except MySQLdb.Error, e:
			return e

		return self.c.fetchall()

	def CheckEIDOwner(self, eid, uid):
		try: 
			self.c.execute("""select eid from EXPERIMENT where eid=%s and uid=%s""", (eid, uid))
		except MySQLdb.Error, e:
			return False
		return True

	def GetColEID(self, eid, uid):
		try:
			self.c.execute("""select eid from USER_EXPERIMENT where eid=%s and uid=%s""", (eid, uid))
		except MySQLdb.Error, e:
			return e
		return self.c.fetchone()

	def GetExperiment(self, eid):
		try:
			self.c.execute("""select uid, name, description, date from EXPERIMENT where eid=%s""", eid)
	
		except MySQLdb.Error, e:
			return e

		return self.c.fetchone()		

	def ChangePassword(self, passwd):
		try:
			self.c.execute("""update USER set passwd=%s""", (passwd,))
		except MySQLdb.Error, e:
			return e
	
	def AddCollaborator(self, uid, eid):
		try:
			self.c.execute("""INSERT into USER_EXPERIMENT (uid, eid) values (%s, %s)""", (uid, eid))      
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)

		except MySQLdb.Error, e:
			return e

		return self.c.lastrowid

	def AddExperimentCollaborator(self, eid, email):

		try: 
			self.c.execute("""select uid from USER where email=%s""", email)
			user = self.c.fetchone()
		except:
			return "Fail! Some error with the collaborator email."

		if not user:
			return "No such collaborator in the system"

		return self.AddCollaborator(user["uid"], eid)

	def GetExperimentCollaborators(self, eid):
		try:
			self.c.execute("""select USER.name as name, USER.last as last, email, ueid from  USER, USER_EXPERIMENT where USER.uid = USER_EXPERIMENT.uid and USER_EXPERIMENT.eid=%s""", eid)
		except MySQLdb.Error, e:
			return e

		return self.c.fetchall()

	def RemoveExperimentCollaborator(self, ueid):
		try:
			self.c.execute("""delete from USER_EXPERIMENT where ueid=%s""", ueid)
		except:
			return False

		return True


	def GetCollaborations(self, uid):
		try:
			self.c.execute("""select USER_EXPERIMENT.eid as eid, EXPERIMENT.name as name, description, date from EXPERIMENT, USER_EXPERIMENT where EXPERIMENT.eid = USER_EXPERIMENT.eid and USER_EXPERIMENT.uid=%s""", uid)
		except MySQLdb.Error, e:
			return e

		return self.c.fetchall()
	
	def AddSample(self, eid, uuid, name):
		try:
			self.c.execute("""INSERT into SAMPLE (eid, uuid, name) values (%s, %s, %s)""", 
				(eid, uuid, name))      
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)
	
		except MySQLdb.Error, e:
			return e
		return self.c.lastrowid


	def GetSamples(self, eid):
		try:
			self.c.execute("""select sid, name, uuid, lastmodified from SAMPLE where eid=%s""", eid)
		except MySQLdb.Error, e:
			return e
		return self.c.fetchall()

	def GetSample(self, sid):
		try:
			self.c.execute("""select eid, name, uuid, lastmodified from SAMPLE where sid=%s""", sid)
		except MySQLdb.Error, e:
			return e

		return self.c.fetchone()


	def AddPeak(self, sid, peak):
		try:
			self.c.execute("""INSERT into PEAK (sid, peak) values (%s, %s)""", 
				(sid, peak))
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)
		except MySQLdb.Error, e:
			return e

		return self.c.lastrowid

	def AddExperimentPeak(self, eid, peak):
		try:
			self.c.execute("""INSERT into EXPERIMENT_PEAK (eid, peak) values (%s, %s)""",
				(eid, peak))
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)

		except MySQLdb.Error, e:
			return e

		return self.c.lastrowid    	

	def GetExperimentPeaks(self, eid):
		try:
			self.c.execute("""select * from EXPERIMENT_PEAK where eid=%s""", eid)
		except MySQLdb.Error, e:
			return e

		return self.c.fetchall()

	def RemovePeak(self, epid):
		try:
			self.c.execute("""delete from EXPERIMENT_PEAK where ep_id=%s""", epid)
		except:
			return False

		return True

	def GetPeaks(self, sid):
		try:
			self.c.execute("""select pid, peak from PEAK where sid=%s""", sid)
		except MySQLdb.Error, e:
			return e

		peaks = self.c.fetchall()
		for peak in peaks:
			try:
				self.c.execute("""select intensity_id, peak, intensity from INTENSITY where pid=%s""", peak["pid"]) 
				peak["intensities"] = self.c.fetchall()
			except MySQLdb.Error, e:
				return e

		return peaks
			
	def AddIntensity(self, pid, peak, intensity):
		try:
			self.c.execute("""INSERT into INTENSITY (pid, peak, intensity) values (%s, %s, %s)""",
				(pid, peak, intensity))
		except MySQLdb.IntegrityError, e:
			return "%s|%s" % ("Duplicate",  e)
		except MySQLdb.Error, e:
			return e

		return self.c.lastrowid
	

	def close(self):
		self.db.close()


def main():
	check1 = 1

	msql = maldisql()
	if check1:

		uid1 =  msql.AddUser("cheo@gmail.com","tuxi", "Jose", "Ortiz")
		uid2 =  msql.AddUser("kary@gmail.com","tuxi", "Kariluz", "Davila")
		print uid1
		print msql.ChangePassword("sleepy")
		print msql.ChangeEmail("cheojr@gmail.com", uid1)
		eid = msql.CreateExperiment("EXp1", "Esto es la descr", uid1)
		print "Experiment", eid
		print eid, uid2
		print msql.AddCollaborator(uid2, eid)
		sid = msql.AddSample(eid, "hola", "asi se llama el sample")
		print sid
		pid = msql.AddPeak(sid, 1.3)
		print pid
		print msql.AddIntensity(pid, 100.3)	

	print msql.GetUser(uid1)
	print msql.GetExperiments(uid1)
	print msql.GetCollaborations(uid2)
	print msql.GetSamples(eid)
	print msql.GetSample(sid)
	print msql.GetPeaks(sid)
	msql.close()

if __name__ == "__main__":
	main()