コード例 #1
0
ファイル: relid.py プロジェクト: amartyaamp/power_networks
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'])
コード例 #2
0
ファイル: relid.py プロジェクト: amartyaamp/power_networks
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'])
コード例 #3
0
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'])
コード例 #4
0
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
コード例 #5
0
ファイル: user.py プロジェクト: amartyaamp/power_networks
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
コード例 #6
0
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'])
コード例 #7
0
ファイル: tasks.py プロジェクト: amartyaamp/power_networks
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
コード例 #8
0
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'])
コード例 #9
0
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
コード例 #10
0
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')
コード例 #11
0
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()
コード例 #12
0
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
コード例 #13
0
ファイル: change.py プロジェクト: amartyaamp/power_networks
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')
コード例 #14
0
ファイル: relid.py プロジェクト: amartyaamp/power_networks
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
コード例 #15
0
ファイル: tasks.py プロジェクト: amartyaamp/power_networks
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()
コード例 #16
0
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'])
コード例 #17
0
ファイル: tasks.py プロジェクト: amartyaamp/power_networks
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
コード例 #18
0
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
コード例 #19
0
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