class Entity: ## CREATE TABLE uuidtable(uuid int NOT NULL PRIMARY KEY ,name varchar( 3000 )); ## mysqlimport --ignore-lines=1 --fields-terminated-by=, --columns='uuid,name' --local -u root -p flasktemp uuidtable.csv ## MOST IMP THING while importing: keep the csvname same as tablename ## keeping int now ## match (n:entity) return n.uuid as uuid, n.name as name def __init__(self, name): self.uuid = None self.name = name ##TODO: but what if the primary name changes? #self.uri = uri #TODO: will decide afterwards from app.constants import META_TABLE_UUID self.tablename = META_TABLE_UUID self.dbwrap = MetaSQLDB() def create(self): query = 'insert into uuidtable(name) values("%s");' query = query %(self.name) cursor = self.dbwrap.connectAndCursor() numrows = cursor.execute(query) self.uuid = cursor.lastrowid self.dbwrap.commitAndClose() ##what if something breaks? TODO! return numrows
class RELIDTable: ##chossing this name instead of a relation ## startnode and ennode can be foregin keys here constraints ## finally the table query! ## create table relidtable(relid int not null primary key, reltype varchar(1000), startuuid int, enduuid int, foreign key (startuuid) references uuidtable(uuid) on delete cascade on update cascade, foreign key (enduuid) references uuidtable(uuid) on delete cascade on update cascade); ## mysqlimport --ignore-lines=1 --fields-terminated-by=, --columns='relid,reltype,startuuid,enduuid' --local -u root -p flasktemp relidtable.csv ## match (start:entity)-[r]->(end:entity) return r.relid as relid, type(r) as reltype, start.uuid as startuuid, end.uuid as enduuid def __init__(self, relid, reltype, startuuid, enduuid): self.relid = relid ##this helps in tracking bugs really! self.reltype = reltype ##RULE: once a type given to a relation, it's always given self.startuuid = startuuid self.enduuid = enduuid from app.constants import META_TABLE_RELID self.tablename = META_TABLE_RELID self.dbwrap = MetaSQLDB() def create(self): query = "insert into relidtable(relid, reltype, startuuid, enduuid ) values(%s,'%s', %s, %s);" ##TODO use constant name of table? here? query = query %(self.relid, self.reltype, self.startuuid, self.enduuid) cursor = self.dbwrap.connectAndCursor() numrows = cursor.execute(query) self.dbwrap.commitAndClose() ##TODO: what about databse errors? how to catch them print numrows
class HyperEdgeNode: # CREATE TABLE henidtable( # henid bigint( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , # labels text # ) def __init__(self, labels): ##for the sake of completion for now just writing the labels for now for this self.henid = None self.labels = labels ##TODO: Comma separated list from app.constants import META_TABLE_HENID self.tablename = META_TABLE_HENID self.dbwrap = MetaSQLDB() def create(self): query = "insert into henidtable(labels) values('%s');" ##TODO use constant name of table? here? query = query %(self.labels) cursor = self.dbwrap.connectAndCursor() numrows = cursor.execute(query) self.henid = cursor.lastrowid self.dbwrap.commitAndClose() ##what if something breaks? TODO! print numrows
class UuidTable: def __init__(self, uuid=None, name=''): self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_UUID self.uuid = uuid self.name = name return def create(self): ##TODO: can put a check here that some strings cannot be empty ##or in __init__ self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidTable object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (uuid, name)\ VALUES(%d, '%s')" % (self.uuid, self.name) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[UuidTable.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass ##Update reltable., Not present ##For the RelLabels and RelProps class def update(self, column='all'): ##update self object into db attr_list = ['all', 'name', 'uuid'] assert(column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidTable] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE uuid= "+str(self.uuid) if column == "all": body_query = "uuid=%d, name='%s'" % (self.uuid, self.name) else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" body_query = (column+"="+typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message self.dbwrap.commitAndClose() exit(3) else: self.dbwrap.commitAndClose() return numrows def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidTable object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT uuid, name FROM " + self.tablename +\ " where uuid=" + str(self.uuid) print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.uuid = r[0] self.name = r[1] self.dbwrap.commitAndClose() return self @classmethod def getUuid(cls, uuid): uuid = UuidTable(uuid) return uuid.getSelfFromDB() def __str__(self): s = '[ UuidTable: uuid: ' + str(self.uuid)+' name: '\ + str(self.name) + ']' return s
class UuidProps: ##TODO: see how if ' in string how to handle that! ##MVP '[u'naveen jindal']' will have to be handled separately ##IDEA: disable aliases completely in api calls? def __init__(self, changeid=None, uuid=None, propname='', ##makes sense to change propname to None, this way nothing will be inserted, error! oldpropvalue='', newpropvalue='', changetype=''): import MySQLdb from app.utils.commonutils import Utils self.changeid = changeid self.uuid = uuid self.propname = propname self.oldpropvalue = MySQLdb.escape_string(oldpropvalue) self.newpropvalue = MySQLdb.escape_string(newpropvalue) ##TODO: add constarint in programming or db, if both none, non need of anything here self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_UUIDPROPS def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidProps object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() ##TODO: will insert empty strings for oldpropvalue! query = "INSERT INTO " + self.tablename + " (changeid, uuid, propname,\ oldpropvalue, newpropvalue, changetype) VALUES(%d, %d, '%s',\ '%s', '%s', %d)" % (int(self.changeid), int(self.uuid), self.propname, self.oldpropvalue, self.newpropvalue, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[UuidProps.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): assert (by in ['changeid', 'uuid']) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[UuidProps object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == "changeid": bystr = 'where changeid= ' + str(self.changeid) else: bystr = ' where uuid= ' + str(self.uuid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, uuid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) results_list = [] uprop = UuidProps() for r in rows: uprop.changeid = r[0] uprop.uuid = r[1] uprop.propname = r[2] uprop.oldpropvalue = r[3] uprop.newpropvalue = r[4] uprop.changetype = r[5] results_list.append(uprop.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['tablename'] del r['dbwrap'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert(by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidProps object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [' ' + str(by) + '=' + self.__dict__[by] for by in by_list] #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, uuid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] uprop = UuidProps() for r in rows: uprop.changeid = r[0] uprop.relid = r[1] uprop.propname = r[2] uprop.oldpropvalue = r[3] uprop.newpropvalue = r[4] uprop.changetype = r[5] results_list.append(uprop.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list for r in results_list: del r['tablename'] del r['dbwrap'] return results_list def __str__(self): s = '[ UuidProps -- uuid: %s propname: %s changeid: %s changetype: %s oldpropvalue: %s newpropvalue: %s]' s = s %(self.uuid, self.propname, self.changeid, self.changetype, self.oldpropvalue, self.newpropvalue) return s @classmethod def getUuidPropsUUId(cls, uuid): up = UuidProps(uuid=uuid) return up.getListFromDB(by='uuid') @classmethod def getUuidPropsChangeId(cls, changeid): up = UuidProps(changeid = changeid) return up.getListFromDB(by='changeid') @classmethod def getUuidPropsBothIds(cls, changeid, uuid): u = UuidProps(changeid=changeid, uuid=uuid) return u.getListFromDBMultiple(['changeid', 'uuid']) @classmethod def getUuidByPropUuid(cls, propname, propvalue, uuid): u = UuidProps(uuid=uuid) u.propname = propname u.newpropvalue = propvalue return u.getListFromDBMultiple(['uuid', 'propname', 'newpropvalue'])
class UuidLabels: def __init__(self, changeid=None, uuid=None, label='', changetype=''): self.changeid = changeid self.uuid = uuid self.label = label self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_UUIDLAB return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidLabels object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (changeid, uuid,\ label, changetype) VALUES(%d, %d, '%s', %d)" %\ (int(self.changeid), int(self.uuid), self.label, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[UuidLabels.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'changeid': bystr = " where changeid=" + str(self.changeid) else: bystr = " where uuid=" + str(self.uuid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, uuid, label, changetype FROM "\ + self.tablename + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] ulabel = UuidLabels() for r in rows: ulabel.changeid = r[0] ulabel.uuid = r[1] ulabel.label = r[2] ulabel.changetype = r[3] results_list.append(ulabel.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['tablename'] del r['dbwrap'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert(by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[UuidLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [' ' + str(by) + '=' + self.__dict__[by] for by in by_list] #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, uuid, label, changetype FROM "\ + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] rlabel = UuidLabels() for r in rows: rlabel.changeid = r[0] rlabel.uuid = r[1] rlabel.label = r[2] rlabel.changetype = r[3] results_list.append(rlabel.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list return results_list def __str__(self): s = '[ UuidLabels -- uuid: %s label: %s changeid: %s changetype: %s]' %(self.uuid, self.label, self.changeid, self.changetype) return s @classmethod def getUuidLabelsUUId(cls, uuid): u = UuidLabels(uuid=uuid) return u.getListFromDB(by='uuid') @classmethod def getUuidLabelsChangeId(cls, changeid): u = UuidLabels(changeid=changeid) return u.getListFromDB(by='changeid') @classmethod def getUuidLabelsBothIds(cls, changeid, uuid): u = UuidLabels(changeid=changeid, uuid=uuid) return u.getListFromDBMultiple(['changeid', 'uuid']) #TODO - get records by uuid, label @classmethod def getUuidByLabelUuid(cls, label, uuid): u = UuidLabels(uuid=uuid) u.label = label return u.getListFromDBMultiple(['label', 'uuid'])
class User: def __init__(self, userid, password="", role=1, keyEnabled=0, name='', lastlogin='', lastpwdchange=''): ##password by default empty, must be checked from higher function ##for a non empty password self.userid = userid self.role = role self.password = nt.getMD5(password) self.apikey = nt.generateApiKey(self.userid) self.keyEnabled = keyEnabled self.name = name # lastlogin and lastpwdchange are normally inserted # during last login last pwd change only self.lastlogin = lastlogin self.lastpwdchange = lastpwdchange ##other meta self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_USER ##all column names too? - Other names are included. will see, will be micro managing def insert(self): self.dbwrap.connect() ##ignore self.userid here ##user self.role and self.password ##insert self object into db try: cursor = self.dbwrap.cursor() except: print "[User object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() #print type(self.keyEnabled) query = "INSERT INTO " + self.tablename + " (`userid`, `password`, `role`, `apikey`,\ `keyenabled`, `name`, `lastlogin`, `lastpwdchange`) VALUES('%s', '%s', %d, '%s', %d, '%s', '%s', '%s')"\ % (self.userid, self.password, self.role, self.apikey, self.keyEnabled, self.name, self.lastlogin, self.lastpwdchange) print query print self.dbwrap.dbhost numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def update(self, column='all'): ##update self object into db attr_list = ['all', 'password', 'role', 'keyEnabled' 'name', 'lastpwdchange', 'lastlogin'] assert(column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE userid= '"+str(self.userid) + "'" if column == "all": body_query = "password='******', role=%d, apikey='%s', keyEnabled=%d" % \ (self.password, self.role, self.apikey, self.keyEnabled) query = base_query + body_query + rest_query print "UPDATE query" print query else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" if column == 'password': val = nt.getMD5(val) body_query = (column+"="+typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def setKeyEnabled(self, keyval): self.keyEnabled = keyval self.update(self, 'keyEnabled') return def validateUser(self, password): #self.getSelfFromDB() password = nt.getMD5(password) if self.password == password: return True return False @classmethod def validateToken(cls, userid, token): try: usr = cls.getUser(userid=userid) return usr.apikey==token and usr.keyEnabled==1 except Exception as e: return False pass def __str__(self): print '[User: userid: '+str(self.userid)+' role: '+str(self.role)+']' return def getSelfFromDB(self): ''' Gets an user obj by userid field''' self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT userid, password, role, apikey, keyEnabled \ , name, lastlogin, lastpwdchange FROM " + self.tablename\ + " where userid='" + str(self.userid) + "'" cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.userid = r[0] self.password = r[1] self.role = r[2] self.apikey = r[3] self.keyEnabled = r[4] self.name = r[5] self.lastlogin = r[6] self.lastpwdchange = r[7] self.dbwrap.commitAndClose() return self def setLastLogin(self): from datetime import datetime from app.constants import META_TABLE_DATEFMT self.lastlogin = datetime.now().strftime(META_TABLE_DATEFMT) self.update('lastlogin') return @classmethod def getUser(cls, userid): ##get User object using the userid ##has to be classmethod try: usr = User(userid) return usr.getSelfFromDB() except: return None
class RelProps: def __init__(self, changeid='', relid='', propname='', oldpropvalue='', newpropvalue='', changetype=''): import MySQLdb ##makes sense to change propname to None, this way nothing will be inserted, error! self.changeid = changeid self.relid = relid self.propname = propname ##TODO: add constarint in programming or db, if both none, non need of anything here self.oldpropvalue = MySQLdb.escape_string(oldpropvalue) self.newpropvalue = MySQLdb.escape_string(newpropvalue) self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELIDPROPS def create(self): ##TODO: can put a check here that some strings cannot be empty ##or in __init__ ##TODO: check numrows on insert! self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelProps object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() ##TODO: will insert empty strings for oldpropvalue! query = "INSERT INTO " + self.tablename + " (changeid, relid, propname,\ oldpropvalue, newpropvalue, changetype) VALUES(%d, %d, '%s',\ '%s', '%s', %d)" % (int(self.changeid), int( self.relid), self.propname, self.oldpropvalue, self.newpropvalue, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelProps.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): assert (by in ['relid', 'changeid']) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[RelProps object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'changeid': bystr = " where changeid=" + str(self.changeid) else: bystr = " where relid=" + str(self.relid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert (len(rows) >= 1) results_list = [] rprops = RelProps() for r in rows: rprops.changeid = r[0] rprops.relid = r[1] rprops.propname = r[2] rprops.oldpropvalue = r[3] rprops.newpropvalue = r[4] rprops.changetype = r[5] results_list.append(rprops.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['dbwrap'] del r['tablename'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert (by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelProps object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [] for by in by_list: paramstr = ' ' + str(by) + '=' if (type(self.__dict__[by]) == int): fieldstr = "%d" else: fieldstr = "'%s'" fields = fieldstr % self.__dict__[by] bystr_list.append(paramstr + fields) #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert (len(rows) >= 1) print len(rows) results_list = [] rprop = RelProps() for r in rows: rprop.changeid = r[0] rprop.relid = r[1] rprop.propname = r[2] rprop.oldpropvalue = r[3] rprop.newpropvalue = r[4] rprop.changetype = r[5] results_list.append(rprop.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list return results_list def __str__(self): s = '[ RelIDprops -- relid: %s propname: %s changeid: %s changetype: %s oldpropvalue: %s newpropvalue: %s]' s = s % (str(self.relid), self.propname, str(self.changeid), str(self.changetype), self.oldpropvalue, self.newpropvalue) return s @classmethod def getRelPropsChangeId(cls, changeid): rp = RelProps(changeid=changeid) return rp.getListFromDB(by='changeid') @classmethod def getRelPropsRelId(cls, relid): rp = RelProps(relid=relid) return rp.getListFromDB(by='relid') @classmethod def getRelPropsBothIds(cls, changeid, relid): rp = RelProps(changeid=changeid, relid=relid) return rp.getListFromDBMultiple(['changeid', 'relid']) @classmethod def getRelByPropRelId(cls, propname, relid): u = RelProps(relid=relid) u.propname = propname return u.getListFromDBMultiple(['relid', 'propname'])
class RelIdTable: def __init__(self, relid, reltype='', startuuid='', enduuid=''): self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELID self.relid = relid self.reltype = reltype self.startuuid = startuuid self.enduuid = enduuid return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelIdTable object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (relid, reltype, startuuid,\ enduuid) VALUES(%d, '%s', %d, %d)" % (self.relid, self.reltype, self.startuuid, self.enduuid) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelIdTable.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass ##Update reltable., Not present ##For the RelLabels and RelProps class def update(self, column='all'): ##update self object into db attr_list = ['all', 'reltype', 'startuuid', 'enduuid'] assert(column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelIdTable] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE relid= "+str(self.relid) if column == "all": body_query = "reltype='%s', startuuid=%d, enduuid = %d" % \ (self.reltype, self.startuuid, self.enduuid) else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" body_query = (column+"="+typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message self.dbwrap.commitAndClose() exit(3) else: self.dbwrap.commitAndClose() return numrows def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT relid, reltype, startuuid, enduuid \ FROM " + self.tablename + " where relid=" + str(self.relid) cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.relid = r[0] self.reltype = r[1] self.startuuid = r[2] self.enduuid = r[3] self.dbwrap.commitAndClose() return self @classmethod def getRel(cls, relid): rel = RelIdTable(relid) return rel.getSelfFromDB() def __str__(self): print '[ Relation: relid: '+str(self.relid)+' startuuid: '\ + str(self.startuuid)+' enduuid: '+str(self.enduuid)+' ]' return
class Tasks: def __init__(self, ownerid='', name='', description='description', iscrawled=0): self.ownerid = ownerid self.name = name self.taskid = None self.description = description self.iscrawled = iscrawled self.createdate = datetime.now().strftime('%Y-%m-%d %H:%M:%S') self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKS def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasks object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (ownerid, name, description,\ createdate, iscrawled) VALUES('%s', '%s', '%s', '%s', %d)" % ( self.ownerid, self.name, self.description, self.createdate, self.iscrawled) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() else: self.taskid = cursor.lastrowid self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def update(self, column='all'): ##update self object into db attr_list = ['all', 'name', 'description'] assert (column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasks object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE taskid= " + str(self.taskid) if column == "all": body_query = "name='%s', description='%s'" % \ (self.name, self.description) query = base_query + body_query + rest_query print "UPDATE query" print query else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" body_query = (column + "=" + typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = 0 numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def getSelfFromDB(self): ##TODO: use the where clause! self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT ownerid, taskid, name, description, iscrawled, createdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.ownerid = r[0] self.taskid = r[1] self.name = r[2] self.description = r[3] self.iscrawled = r[4] self.createdate = r[5] self.dbwrap.commitAndClose() return self def getSelfFromDBWhere(self, where): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT ownerid, taskid, name, description, iscrawled, createdate \ FROM " + self.tablename + " where " + where cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.ownerid = r[0] self.taskid = r[1] self.name = r[2] self.description = r[3] self.iscrawled = r[4] self.createdate = r[5] self.dbwrap.commitAndClose() return self @classmethod def getTask(cls, taskid): tsk = Tasks() tsk.taskid = taskid return tsk.getSelfFromDB() @classmethod def getWikiTaskByUser(cls, ownerid): task = Tasks() task.ownerid = ownerid where = "ownerid='%s' and iscrawled=0 limit 1" % (ownerid) return task.getSelfFromDBWhere(where) def __str__(self): print '[ Task: taskid: '+str(self.taskid)+' name: '\ + str(self.name)+' descr: '+str(self.description)+' ]' return
class ChangeItem: def __init__(self, taskid='', pushedby='', sourceurl='', verifiedby='', fetchdate='', pushdate='', verifydate=''): ##password by default empty, must be checked from higher function ##for a non empty password self.changeid = None self.taskid = taskid self.pushedby = pushedby self.verifiedby = verifiedby self.verifydate = verifydate ##adding this patch for neo4j, since this ts will be gen from neo4j if verifydate == '': self.verifydate = datetime.now().strftime(META_TABLE_DATEFMT) self.fetchdate = fetchdate self.pushdate = pushdate self.sourceurl = sourceurl ##other meta self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_CHANGE ##all column names too? - Other names are included. will see, will be micro managing def insert(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[ChangeID object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() #print type(self.keyEnabled) query = "INSERT INTO " + self.tablename + " (taskid, pushedby, verifiedby,\ verifydate, fetchdate, pushdate, sourceurl) VALUES(%d, '%s', '%s', '%s', '%s','%s', '%s')"\ % (int(self.taskid), self.pushedby, self.verifiedby, self.verifydate, self.fetchdate, self.pushdate, self.sourceurl) print query numrows = cursor.execute(query) self.changeid = cursor.lastrowid self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def __str__(self): s = '[Change: changeid: ' + str(self.changeid) + ' pushedby: ' + str( self.pushedby) + ']' print s return s def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, taskid, pushedby, sourceurl, verifiedby, verifydate, \ pushdate, fetchdate FROM " + self.tablename + " where changeid=" +\ str(self.changeid) + rest_str cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.changeid = r[0] self.taskid = r[1] self.pushedby = r[2] self.sourceurl = r[3] self.verifiedby = r[4] self.verifydate = r[5] self.pushdate = r[6] self.fetchdate = r[7] return self def getListFromDB(self, by): assert (by in ['pushedby', 'changeid', 'taskid', 'userid']) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[ChangeId object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() by_str = ' ' + by + '=' if self.__dict__[by] == int: field_str = "%d" else: field_str = "'%s'" field = field_str % self.__dict__[by] rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, taskid, pushedby, verifiedby, verifydate, \ pushdate, fetchdate, sourceurl FROM " + self.tablename + " WHERE "\ + by_str + field + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert (len(rows) >= 1) print rows results = [] chg = ChangeItem() for r in rows: chg.changeid = r[0] chg.taskid = r[1] chg.pushedby = r[2] chg.verifiedby = r[3] chg.verifydate = r[4] chg.pushdate = r[5] chg.fetchdate = r[6] chg.sourceurl = r[7] results.append(chg.__dict__.copy()) self.dbwrap.commitAndClose() for r in results: del r['tablename'] del r['dbwrap'] return results @classmethod def getChangeItem(cls, changeid): chg = ChangeItem() chg.changeid = changeid return chg.getSelfFromDB() @classmethod def getChangesUserId(cls, userid): r = ChangeItem(pushedby=userid) return r.getListFromDB(by='pushedby')
class Tasklog: def __init__(self, taskid=None, userid='', description='', jsondump=''): self.taskid = taskid self.userid = userid self.description = description self.jsondump = jsondump self.dumpdate = datetime.now().strftime('%Y-%m-%d %H:%M:%S') self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKLOG return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (taskid, userid,\ description, jsondump, dumpdate) VALUES(%d, '%s', '%s', '%s', '%s')" % \ (self.taskid, self.userid, self.description, self.jsondump, self.dumpdate) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, taskid): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In getListFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT userid, taskid, description, jsondump, dumpdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) results = [] try: cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() exit(3) else: rows = cursor.fetchall() for r in rows: userid, taskid, description, jsondump, dumpdate =\ r[0], r[1], r[2], r[3], r[4] tmpdict = { "userid": userid, "taskid": taskid, "desc": description, "ddate": dumpdate } results.append(tmpdict.copy()) self.dbwrap.commitAndClose() return results def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In getSelfFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT userid, taskid, description, jsondump, dumpdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) + \ " AND userid='" + self.userid + "'" cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.userid = r[0] self.taskid = r[1] self.description = r[2] self.jsondump = r[3] self.dumpdate = r[4] self.dbwrap.commitAndClose() return self @classmethod def getTasklog(cls, userid, taskid): tl = Tasklog(taskid, userid) return tl.getSelfFromDB()
class Taskusers: def __init__(self, taskid='', userid=''): self.taskid = taskid self.userid = userid self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKUSERS return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (taskid, userid)\ VALUES(%d, '%s')" % (self.taskid, self.userid) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelIdTable.create: query execution error]" print "query execution error" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'userid': bystr = "userid='" + self.userid + "'" else: bystr = "taskid=" + str(self.taskid) query = "SELECT userid, taskid FROM " + self.tablename + \ " WHERE " + bystr cursor.execute(query) rows = cursor.fetchall() #assert(len(rows) == 1) result_list = [] for r in rows: self.userid = r[0] self.taskid = r[1] if by == 'userid': result_list.append(self.taskid) else: result_list.append(self.userid) self.dbwrap.commitAndClose() return result_list def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In getSelfFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT taskid, userid \ FROM " + self.tablename + " where taskid=" + str(self.taskid) + \ " AND userid='" + self.userid + "'" cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.userid = r[0] self.taskid = r[1] self.dbwrap.commitAndClose() return self @classmethod def validateTaskAndUser(cls, taskid, userid): try: tu = Taskusers() tu.userid = userid tu.taskid = taskid tu = tu.getSelfFromDB() return True except Exception as e: return False
class ChangeItem: def __init__(self, taskid='', pushedby='', sourceurl='', verifiedby='', fetchdate='', pushdate='', verifydate=''): ##password by default empty, must be checked from higher function ##for a non empty password self.changeid = None self.taskid = taskid self.pushedby = pushedby self.verifiedby = verifiedby self.verifydate = verifydate ##adding this patch for neo4j, since this ts will be gen from neo4j if verifydate == '': self.verifydate = datetime.now().strftime(META_TABLE_DATEFMT) self.fetchdate = fetchdate self.pushdate = pushdate self.sourceurl = sourceurl ##other meta self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_CHANGE ##all column names too? - Other names are included. will see, will be micro managing def insert(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[ChangeID object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() #print type(self.keyEnabled) query = "INSERT INTO " + self.tablename + " (taskid, pushedby, verifiedby,\ verifydate, fetchdate, pushdate, sourceurl) VALUES(%d, '%s', '%s', '%s', '%s','%s', '%s')"\ % (int(self.taskid), self.pushedby, self.verifiedby, self.verifydate, self.fetchdate, self.pushdate, self.sourceurl) print query numrows = cursor.execute(query) self.changeid = cursor.lastrowid self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def __str__(self): s = '[Change: changeid: '+str(self.changeid)+' pushedby: '+str(self.pushedby)+']' print s return s def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, taskid, pushedby, sourceurl, verifiedby, verifydate, \ pushdate, fetchdate FROM " + self.tablename + " where changeid=" +\ str(self.changeid) + rest_str cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.changeid = r[0] self.taskid = r[1] self.pushedby = r[2] self.sourceurl = r[3] self.verifiedby = r[4] self.verifydate = r[5] self.pushdate = r[6] self.fetchdate = r[7] return self def getListFromDB(self, by): assert(by in ['pushedby', 'changeid', 'taskid', 'userid']) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[ChangeId object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() by_str = ' ' + by + '=' if self.__dict__[by] == int: field_str = "%d" else: field_str = "'%s'" field = field_str % self.__dict__[by] rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, taskid, pushedby, verifiedby, verifydate, \ pushdate, fetchdate, sourceurl FROM " + self.tablename + " WHERE "\ + by_str + field + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print rows results = [] chg = ChangeItem() for r in rows: chg.changeid = r[0] chg.taskid = r[1] chg.pushedby = r[2] chg.verifiedby = r[3] chg.verifydate = r[4] chg.pushdate = r[5] chg.fetchdate = r[6] chg.sourceurl = r[7] results.append(chg.__dict__.copy()) self.dbwrap.commitAndClose() for r in results: del r['tablename'] del r['dbwrap'] return results @classmethod def getChangeItem(cls, changeid): chg = ChangeItem() chg.changeid = changeid return chg.getSelfFromDB() @classmethod def getChangesUserId(cls, userid): r = ChangeItem(pushedby=userid) return r.getListFromDB(by='pushedby')
class RelLabels: def __init__(self, changeid='', relid='', label='', changetype=''): self.changeid = changeid self.relid = relid self.label = label self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELIDLAB return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[Taskusers object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (changeid, relid,\ label, changetype) VALUES(%d, %d, '%s', %d)" %\ (int(self.changeid), int(self.relid), self.label, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelLabels.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): assert(by in ['relid', 'changeid']) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'changeid': bystr = " where changeid=" + str(self.changeid) else: bystr = " where relid=" + str(self.relid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, label, changetype FROM "\ + self.tablename + bystr + rest_str cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] rlabel = RelLabels() for r in rows: rlabel.changeid = r[0] rlabel.relid = r[1] rlabel.label = r[2] rlabel.changetype = r[3] results_list.append(rlabel.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['dbwrap'] del r['tablename'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert(by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [] for by in by_list: paramstr = ' ' + str(by) + '=' if (type(self.__dict__[by]) == int): fieldstr = "%d" else: fieldstr = "'%s'" fields = fieldstr % self.__dict__[by] bystr_list.append(paramstr + fields) #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, label, changetype FROM "\ + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] rlabel = RelLabels() for r in rows: rlabel.changeid = r[0] rlabel.relid = r[1] rlabel.label = r[2] rlabel.changetype = r[3] results_list.append(rlabel.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list return results_list def __str__(self): s = '[ RelIDLabels -- relid: %s label: %s changeid: %s changetype: %s]' %(str(self.relid), self.label, str(self.changeid), str(self.changetype)) return s @classmethod def getRelLabelsRelId(cls, relid): r = RelLabels(relid=relid) return r.getListFromDB(by='relid') @classmethod def getRelLabelsChangeId(cls, changeid): r = RelLabels(changeid=changeid) return r.getListFromDB(by='changeid') @classmethod def getRelLabelsBothIds(cls, changeid, relid): rp = RelLabels(changeid=changeid, relid=relid) return rp.getListFromDBMultiple(['changeid', 'relid']) @classmethod def getRelByLabelRelId(cls, label, relid): u = RelLabels(relid=relid) u.label = label return u.getListFromDBMultiple(['label', 'relid'])
class Taskusers: def __init__(self, taskid='', userid=''): self.taskid = taskid self.userid = userid self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKUSERS return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (taskid, userid)\ VALUES(%d, '%s')" % (self.taskid, self.userid) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelIdTable.create: query execution error]" print "query execution error" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'userid': bystr = "userid='" + self.userid + "'" else: bystr = "taskid=" + str(self.taskid) query = "SELECT userid, taskid FROM " + self.tablename + \ " WHERE " + bystr cursor.execute(query) rows = cursor.fetchall() #assert(len(rows) == 1) result_list = [] for r in rows: self.userid = r[0] self.taskid = r[1] if by == 'userid': result_list.append(self.taskid) else: result_list.append(self.userid) self.dbwrap.commitAndClose() return result_list def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Taskusers object] In getSelfFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT taskid, userid \ FROM " + self.tablename + " where taskid=" + str(self.taskid) + \ " AND userid='" + self.userid + "'" cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.userid = r[0] self.taskid = r[1] self.dbwrap.commitAndClose() return self @classmethod def validateTaskAndUser(cls, taskid, userid): try: tu = Taskusers() tu.userid = userid tu.taskid = taskid tu = tu.getSelfFromDB() return True except Exception as e: return False
class RelProps: def __init__(self, changeid='', relid='', propname='', oldpropvalue='', newpropvalue='', changetype=''): import MySQLdb ##makes sense to change propname to None, this way nothing will be inserted, error! self.changeid = changeid self.relid = relid self.propname = propname ##TODO: add constarint in programming or db, if both none, non need of anything here self.oldpropvalue = MySQLdb.escape_string(oldpropvalue) self.newpropvalue = MySQLdb.escape_string(newpropvalue) self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELIDPROPS def create(self): ##TODO: can put a check here that some strings cannot be empty ##or in __init__ ##TODO: check numrows on insert! self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelProps object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() ##TODO: will insert empty strings for oldpropvalue! query = "INSERT INTO " + self.tablename + " (changeid, relid, propname,\ oldpropvalue, newpropvalue, changetype) VALUES(%d, %d, '%s',\ '%s', '%s', %d)" % (int(self.changeid), int(self.relid), self.propname, self.oldpropvalue, self.newpropvalue, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelProps.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): assert(by in ['relid', 'changeid']) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[RelProps object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'changeid': bystr = " where changeid=" + str(self.changeid) else: bystr = " where relid=" + str(self.relid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) results_list = [] rprops = RelProps() for r in rows: rprops.changeid = r[0] rprops.relid = r[1] rprops.propname = r[2] rprops.oldpropvalue = r[3] rprops.newpropvalue = r[4] rprops.changetype = r[5] results_list.append(rprops.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['dbwrap'] del r['tablename'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert(by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelProps object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [] for by in by_list: paramstr = ' ' + str(by) + '=' if (type(self.__dict__[by]) == int): fieldstr = "%d" else: fieldstr = "'%s'" fields = fieldstr % self.__dict__[by] bystr_list.append(paramstr + fields) #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, propname, oldpropvalue, newpropvalue,\ changetype FROM " + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert(len(rows) >= 1) print len(rows) results_list = [] rprop = RelProps() for r in rows: rprop.changeid = r[0] rprop.relid = r[1] rprop.propname = r[2] rprop.oldpropvalue = r[3] rprop.newpropvalue = r[4] rprop.changetype = r[5] results_list.append(rprop.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list return results_list def __str__(self): s = '[ RelIDprops -- relid: %s propname: %s changeid: %s changetype: %s oldpropvalue: %s newpropvalue: %s]' s = s %(str(self.relid), self.propname, str(self.changeid), str(self.changetype), self.oldpropvalue, self.newpropvalue) return s @classmethod def getRelPropsChangeId(cls, changeid): rp = RelProps(changeid=changeid) return rp.getListFromDB(by='changeid') @classmethod def getRelPropsRelId(cls, relid): rp = RelProps(relid=relid) return rp.getListFromDB(by='relid') @classmethod def getRelPropsBothIds(cls, changeid, relid): rp = RelProps(changeid=changeid, relid=relid) return rp.getListFromDBMultiple(['changeid', 'relid']) @classmethod def getRelByPropRelId(cls, propname, relid): u = RelProps(relid=relid) u.propname = propname return u.getListFromDBMultiple(['relid', 'propname'])
class Tasklog: def __init__(self, taskid=None, userid='', description='', jsondump=''): self.taskid = taskid self.userid = userid self.description = description self.jsondump = jsondump self.dumpdate = datetime.now().strftime('%Y-%m-%d %H:%M:%S') self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKLOG return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (taskid, userid,\ description, jsondump, dumpdate) VALUES(%d, '%s', '%s', '%s', '%s')" % \ (self.taskid, self.userid, self.description, self.jsondump, self.dumpdate) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, taskid): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In getListFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT userid, taskid, description, jsondump, dumpdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) results = [] try: cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() exit(3) else: rows = cursor.fetchall() for r in rows: userid, taskid, description, jsondump, dumpdate =\ r[0], r[1], r[2], r[3], r[4] tmpdict = {"userid": userid, "taskid": taskid, "desc": description, "ddate": dumpdate} results.append(tmpdict.copy()) self.dbwrap.commitAndClose() return results def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasklog object] In getSelfFromDB" print "Cannot get cursor" self.dbwrap.commitAndClose() exit(3) query = "SELECT userid, taskid, description, jsondump, dumpdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) + \ " AND userid='" + self.userid + "'" cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.userid = r[0] self.taskid = r[1] self.description = r[2] self.jsondump = r[3] self.dumpdate = r[4] self.dbwrap.commitAndClose() return self @classmethod def getTasklog(cls, userid, taskid): tl = Tasklog(taskid, userid) return tl.getSelfFromDB()
class RelLabels: def __init__(self, changeid='', relid='', label='', changetype=''): self.changeid = changeid self.relid = relid self.label = label self.changetype = changetype self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELIDLAB return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[Taskusers object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (changeid, relid,\ label, changetype) VALUES(%d, %d, '%s', %d)" %\ (int(self.changeid), int(self.relid), self.label, int(self.changetype)) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelLabels.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def getListFromDB(self, by): assert (by in ['relid', 'changeid']) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() if by == 'changeid': bystr = " where changeid=" + str(self.changeid) else: bystr = " where relid=" + str(self.relid) rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, label, changetype FROM "\ + self.tablename + bystr + rest_str cursor.execute(query) rows = cursor.fetchall() assert (len(rows) >= 1) print len(rows) results_list = [] rlabel = RelLabels() for r in rows: rlabel.changeid = r[0] rlabel.relid = r[1] rlabel.label = r[2] rlabel.changetype = r[3] results_list.append(rlabel.__dict__.copy()) self.dbwrap.commitAndClose() for r in results_list: del r['dbwrap'] del r['tablename'] return results_list def getListFromDBMultiple(self, by_list=['']): #TODO - verify the by_list for by in by_list: assert (by in self.__dict__.keys()) #TODO - connect to db self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelLabels object] In SELECT" print "Cannot get cursor" self.dbwrap.commitAndClose() #TODO - create a list of all strings bystr_list = [] for by in by_list: paramstr = ' ' + str(by) + '=' if (type(self.__dict__[by]) == int): fieldstr = "%d" else: fieldstr = "'%s'" fields = fieldstr % self.__dict__[by] bystr_list.append(paramstr + fields) #TODO - join all sources by ' AND ' bystr = ' AND '.join(bystr_list) #TODO - append to select query and run it as usual rest_str = ' ORDER by changeid DESC' query = "SELECT changeid, relid, label, changetype FROM "\ + self.tablename + " WHERE " + bystr + rest_str print query cursor.execute(query) rows = cursor.fetchall() assert (len(rows) >= 1) print len(rows) results_list = [] rlabel = RelLabels() for r in rows: rlabel.changeid = r[0] rlabel.relid = r[1] rlabel.label = r[2] rlabel.changetype = r[3] results_list.append(rlabel.__dict__.copy()) self.dbwrap.commitAndClose() #TODO - return the list return results_list def __str__(self): s = '[ RelIDLabels -- relid: %s label: %s changeid: %s changetype: %s]' % ( str(self.relid), self.label, str( self.changeid), str(self.changetype)) return s @classmethod def getRelLabelsRelId(cls, relid): r = RelLabels(relid=relid) return r.getListFromDB(by='relid') @classmethod def getRelLabelsChangeId(cls, changeid): r = RelLabels(changeid=changeid) return r.getListFromDB(by='changeid') @classmethod def getRelLabelsBothIds(cls, changeid, relid): rp = RelLabels(changeid=changeid, relid=relid) return rp.getListFromDBMultiple(['changeid', 'relid']) @classmethod def getRelByLabelRelId(cls, label, relid): u = RelLabels(relid=relid) u.label = label return u.getListFromDBMultiple(['label', 'relid'])
class Tasks: def __init__(self, ownerid = '', name='', description='description', iscrawled=0): self.ownerid = ownerid self.name = name self.taskid = None self.description = description self.iscrawled = iscrawled self.createdate = datetime.now().strftime('%Y-%m-%d %H:%M:%S') self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_TASKS def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasks object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (ownerid, name, description,\ createdate, iscrawled) VALUES('%s', '%s', '%s', '%s', %d)" % (self.ownerid, self.name, self.description, self.createdate, self.iscrawled) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message print "query execution error" self.dbwrap.commitAndClose() else: self.taskid = cursor.lastrowid self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def update(self, column='all'): ##update self object into db attr_list = ['all', 'name', 'description'] assert(column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[Tasks object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE taskid= "+str(self.taskid) if column == "all": body_query = "name='%s', description='%s'" % \ (self.name, self.description) query = base_query + body_query + rest_query print "UPDATE query" print query else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" body_query = (column+"="+typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = 0 numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def getSelfFromDB(self): ##TODO: use the where clause! self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT ownerid, taskid, name, description, iscrawled, createdate \ FROM " + self.tablename + " where taskid=" + str(self.taskid) cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.ownerid = r[0] self.taskid = r[1] self.name = r[2] self.description = r[3] self.iscrawled = r[4] self.createdate = r[5] self.dbwrap.commitAndClose() return self def getSelfFromDBWhere(self, where): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT ownerid, taskid, name, description, iscrawled, createdate \ FROM " + self.tablename + " where "+where cursor.execute(query) rows = cursor.fetchall() assert(len(rows) == 1) for r in rows: self.ownerid = r[0] self.taskid = r[1] self.name = r[2] self.description = r[3] self.iscrawled = r[4] self.createdate = r[5] self.dbwrap.commitAndClose() return self @classmethod def getTask(cls, taskid): tsk = Tasks() tsk.taskid = taskid return tsk.getSelfFromDB() @classmethod def getWikiTaskByUser(cls, ownerid): task = Tasks() task.ownerid = ownerid where = "ownerid='%s' and iscrawled=0 limit 1" %(ownerid) return task.getSelfFromDBWhere(where) def __str__(self): print '[ Task: taskid: '+str(self.taskid)+' name: '\ + str(self.name)+' descr: '+str(self.description)+' ]' return
class RelIdTable: def __init__(self, relid, reltype='', startuuid='', enduuid=''): self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_RELID self.relid = relid self.reltype = reltype self.startuuid = startuuid self.enduuid = enduuid return def create(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelIdTable object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "INSERT INTO " + self.tablename + " (relid, reltype, startuuid,\ enduuid) VALUES(%d, '%s', %d, %d)" % ( self.relid, self.reltype, self.startuuid, self.enduuid) print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: import traceback traceback.print_exc() traceback.print_stack() print "[relid.RelIdTable.create: query execution error]" self.dbwrap.commitAndClose() else: self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass ##Update reltable., Not present ##For the RelLabels and RelProps class def update(self, column='all'): ##update self object into db attr_list = ['all', 'reltype', 'startuuid', 'enduuid'] assert (column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[RelIdTable] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE relid= " + str(self.relid) if column == "all": body_query = "reltype='%s', startuuid=%d, enduuid = %d" % \ (self.reltype, self.startuuid, self.enduuid) else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" body_query = (column + "=" + typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = 0 try: numrows = cursor.execute(query) except Exception as e: print e.message self.dbwrap.commitAndClose() exit(3) else: self.dbwrap.commitAndClose() return numrows def getSelfFromDB(self): self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except Exception as e: print e.message print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT relid, reltype, startuuid, enduuid \ FROM " + self.tablename + " where relid=" + str(self.relid) cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.relid = r[0] self.reltype = r[1] self.startuuid = r[2] self.enduuid = r[3] self.dbwrap.commitAndClose() return self @classmethod def getRel(cls, relid): rel = RelIdTable(relid) return rel.getSelfFromDB() def __str__(self): print '[ Relation: relid: '+str(self.relid)+' startuuid: '\ + str(self.startuuid)+' enduuid: '+str(self.enduuid)+' ]' return
class User: def __init__(self, userid, password="", role=1, keyEnabled=0, name='', lastlogin='', lastpwdchange=''): ##password by default empty, must be checked from higher function ##for a non empty password self.userid = userid self.role = role self.password = nt.getMD5(password) self.apikey = nt.generateApiKey(self.userid) self.keyEnabled = keyEnabled self.name = name # lastlogin and lastpwdchange are normally inserted # during last login last pwd change only self.lastlogin = lastlogin self.lastpwdchange = lastpwdchange ##other meta self.dbwrap = MetaSQLDB() self.tablename = META_TABLE_USER ##all column names too? - Other names are included. will see, will be micro managing def insert(self): self.dbwrap.connect() ##ignore self.userid here ##user self.role and self.password ##insert self object into db try: cursor = self.dbwrap.cursor() except: print "[User object] In create" print "Cannot get cursor" self.dbwrap.commitAndClose() #print type(self.keyEnabled) query = "INSERT INTO " + self.tablename + " (`userid`, `password`, `role`, `apikey`,\ `keyenabled`, `name`, `lastlogin`, `lastpwdchange`) VALUES('%s', '%s', %d, '%s', %d, '%s', '%s', '%s')"\ % (self.userid, self.password, self.role, self.apikey, self.keyEnabled, self.name, self.lastlogin, self.lastpwdchange) print query print self.dbwrap.dbhost numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def delete(self): ##delete self object into db ##TODO - later pass def update(self, column='all'): ##update self object into db attr_list = [ 'all', 'password', 'role', 'keyEnabled' 'name', 'lastpwdchange', 'lastlogin' ] assert (column in attr_list) self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() base_query = "UPDATE " + self.tablename + " SET " rest_query = " WHERE userid= '" + str(self.userid) + "'" if column == "all": body_query = "password='******', role=%d, apikey='%s', keyEnabled=%d" % \ (self.password, self.role, self.apikey, self.keyEnabled) query = base_query + body_query + rest_query print "UPDATE query" print query else: t = type(column) val = getattr(self, column) if t == int: typestr = "%d" else: typestr = "'%s'" if column == 'password': val = nt.getMD5(val) body_query = (column + "=" + typestr) % (val) query = base_query + body_query + rest_query print "UPDATE query" print query numrows = cursor.execute(query) self.dbwrap.commitAndClose() return numrows def setKeyEnabled(self, keyval): self.keyEnabled = keyval self.update(self, 'keyEnabled') return def validateUser(self, password): #self.getSelfFromDB() password = nt.getMD5(password) if self.password == password: return True return False @classmethod def validateToken(cls, userid, token): try: usr = cls.getUser(userid=userid) return usr.apikey == token and usr.keyEnabled == 1 except Exception as e: return False pass def __str__(self): print '[User: userid: ' + str(self.userid) + ' role: ' + str( self.role) + ']' return def getSelfFromDB(self): ''' Gets an user obj by userid field''' self.dbwrap.connect() try: cursor = self.dbwrap.cursor() except: print "[User object] In update" print "Cannot get cursor" self.dbwrap.commitAndClose() query = "SELECT userid, password, role, apikey, keyEnabled \ , name, lastlogin, lastpwdchange FROM " + self.tablename\ + " where userid='" + str(self.userid) + "'" cursor.execute(query) rows = cursor.fetchall() assert (len(rows) == 1) for r in rows: self.userid = r[0] self.password = r[1] self.role = r[2] self.apikey = r[3] self.keyEnabled = r[4] self.name = r[5] self.lastlogin = r[6] self.lastpwdchange = r[7] self.dbwrap.commitAndClose() return self def setLastLogin(self): from datetime import datetime from app.constants import META_TABLE_DATEFMT self.lastlogin = datetime.now().strftime(META_TABLE_DATEFMT) self.update('lastlogin') return @classmethod def getUser(cls, userid): ##get User object using the userid ##has to be classmethod try: usr = User(userid) return usr.getSelfFromDB() except: return None