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
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()
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 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
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
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
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 __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 __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 __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 __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 __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 __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 __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 __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 __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 __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 __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 __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 __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 __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
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
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'])
def __init__(self, taskid='', userid=''):
self.taskid = taskid
self.userid = userid
self.dbwrap = MetaSQLDB()
self.tablename = META_TABLE_TASKUSERS
return
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'])
def __init__(self, uuid=None, name=''):
self.dbwrap = MetaSQLDB()
self.tablename = META_TABLE_UUID
self.uuid = uuid
self.name = name
return
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 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 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 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 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
def __init__(self, taskid='', userid=''):
self.taskid = taskid
self.userid = userid
self.dbwrap = MetaSQLDB()
self.tablename = META_TABLE_TASKUSERS
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 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 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
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 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 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 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 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
