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 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 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 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 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 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 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 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 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 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