Changeset 3582

Show
Ignore:
Timestamp:
05/01/08 04:40:48 (3 months ago)
Author:
bselby
Message:

#333 - The dbBackend.py file has now been updated to get rid of dbEscape and use the same sql formatting as used elsewhere. Currently left the old SQL there but commented
out, as not had enough time to fully test all queries run correctly. Please checkout this file and let me know. If needed uncomment old query and comment out the new one.

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved

  • peerreviewplugin/branches/2.1-toddler/codereview/dbBackend.py

    r717 r3582  
    3030            if len(newStr) != 0: 
    3131                newStr = newStr + "OR " 
    32             newStr = newStr + colName + " LIKE '%" + str + "%' " 
     32            newStr = newStr + colName + " LIKE '%%s%' " % str 
    3333        return newStr 
    3434 
    3535    #Returns an array of all the code reviews whose author is the given user 
    3636    def getMyCodeReviews(self, user): 
    37         query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Author= '" + dbEscape(user) + "' ORDER BY DateCreate" 
     37        query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Author = '%s' ORDER BY DateCreate" % user 
     38        #query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Author= '" + dbEscape(user) + "' ORDER BY DateCreate" 
    3839        return self.execCodeReviewQuery(query, False) 
    3940 
    4041    #Returns an array of all the code reviews who have the given user assigned to them as a reviewer 
    4142    def getCodeReviews(self, user): 
    42         query = "SELECT CodeReviews.IDReview, CodeReviews.Author, CodeReviews.Status, CodeReviews.DateCreate, CodeReviews.Name, CodeReviews.Notes FROM CodeReviews, Reviewers WHERE Reviewers.IDReview = CodeReviews.IDReview AND Reviewer = '" + dbEscape(user) + "' ORDER BY CodeReviews.DateCreate" 
     43        query = "SELECT cr.IDReview, cr.Author, cr.Status, cr.DateCreate, cr.Name, cr.Notes FROM CodeReviews cr, Reviewers r WHERE r.IDReview = cr.IDReview AND r.Reviewer = '%s' ORDER BY cr.DateCreate" % user 
     44        #query = "SELECT CodeReviews.IDReview, CodeReviews.Author, CodeReviews.Status, CodeReviews.DateCreate, CodeReviews.Name, CodeReviews.Notes FROM CodeReviews, Reviewers WHERE Reviewers.IDReview = CodeReviews.IDReview AND Reviewer = '" + dbEscape(user) + "' ORDER BY CodeReviews.DateCreate" 
    4345        return self.execCodeReviewQuery(query, False) 
    4446 
    4547    #Returns an array of all the code reviews with the given status 
    4648    def getCodeReviewsByStatus(self, status): 
    47         query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Status= '" + dbEscape(status) + "'ORDER BY DateCreate" 
     49        query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Status = '%s' ORDER BY DateCreate" % status 
     50        #query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE Status= '" + dbEscape(status) + "'ORDER BY DateCreate" 
    4851        return self.execCodeReviewQuery(query, False) 
    4952 
    5053    #Returns the number of votes of type 'type' for the given code review 
    5154    def getVotesByID(self, type, id): 
    52         query = "SELECT Count(Reviewer) FROM Reviewers WHERE IDReview = '" + dbEscape(id) +"' AND Vote = '" + dbEscape(type) + "'" 
     55        query = "SELECT Count(Reviewer) FROM Reviewers WHERE IDReview = '%s' AND Vote = '%s'" % id, type 
     56        #query = "SELECT Count(Reviewer) FROM Reviewers WHERE IDReview = '" + dbEscape(id) +"' AND Vote = '" + dbEscape(type) + "'" 
    5357        cursor = self.db.cursor() 
    5458        cursor.execute(query) 
    …… 
    6064    #Returns the code review requested by ID 
    6165    def getCodeReviewsByID(self, id): 
    62         query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE IDReview= '" + dbEscape(id) + "'" 
     66        query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE IDReview = '%s'" % id 
     67        #query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE IDReview= '" + dbEscape(id) + "'" 
    6368        return self.execCodeReviewQuery(query, True) 
    6469 
    …… 
    6671    #names given in the 'name' string 
    6772    def searchCodeReviewsByName(self, name): 
    68         query = self.createORLoop(dbEscape(name), "Name") 
     73        queryPart = self.createORLoop(dbEscape(name), "Name") 
    6974        if len(queryPart) == 0: 
    7075            query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews" 
    7176        else: 
    72             query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE " + dbEscape(query) 
     77            query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE %s" % queryPart 
     78            #query = "SELECT IDReview, Author, Status, DateCreate, Name, Notes FROM CodeReviews WHERE " + dbEscape(query) 
    7379        return self.execCodeReviewQuery(query, True) 
    7480 
    …… 
    7985        queryPart = self.createORLoop(dbEscape(crStruct.Name), "Name") 
    8086        if len(queryPart) != 0: 
    81             query = query + "(" + queryPart + ") AND " 
    82         query = query + "Author LIKE '%" + dbEscape(crStruct.Author) + "%' AND Status LIKE '%" + dbEscape(crStruct.Status) + "%' AND DateCreate >= '" + crStruct.DateCreate + "'" 
     87            query = query + "(%s) AND " % queryPart 
     88        query = query + "Author LIKE '%%s%' AND Status LIKE '%%s%' AND DateCreate >= '%s'" % crStruct.Author, crStruct.Status, crStruct.DateCreate 
    8389        return self.execCodeReviewQuery(query, False) 
    8490 
    8591    #Returns an array of all the reviewers for a code review 
    8692    def getReviewers(self, id): 
    87         query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = '" + dbEscape(id) + "'" 
     93        query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = %s" % id 
     94        #query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = '" + dbEscape(id) + "'" 
    8895        return self.execReviewerQuery(query, False) 
    8996 
    9097    #Returns a specific reviewer entry for the given code review and name 
    9198    def getReviewerEntry(self, id, name): 
    92         query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = '" + dbEscape(id) + "' AND Reviewer = '" + dbEscape(name) + "'" 
     99        query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = %s AND Reviewer = %s" % id, name 
     100        #query = "SELECT IDReview, Reviewer, Status, Vote FROM Reviewers WHERE IDReview = '" + dbEscape(id) + "' AND Reviewer = '" + dbEscape(name) + "'" 
    93101        return self.execReviewerQuery(query, True) 
    94102 
    95103    #Returns an array of the files associated with the given review id 
    96104    def getReviewFiles(self, id): 
    97         query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDReview = '" + dbEscape(id) + "'" 
     105        query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDReview = %s" % id 
     106        #query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDReview = '" + dbEscape(id) + "'" 
    98107        return self.execReviewFileQuery(query, False) 
    99108 
    100109    #Returns the requested review file 
    101110    def getReviewFile(self, id): 
    102         query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDFile = '" + dbEscape(id) + "'" 
    103         return self.execReviewFileQuery(query, True) 
     111        query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDFile = %s" % id 
     112        #query = "SELECT IDFile, IDReview, Path, LineStart, LineEnd, Version FROM ReviewFiles WHERE IDFile = '" + dbEscape(id) + "'" 
     113        return self.execReviewFileQuery(query, True) 
    104114 
    105115    #Returns the requested comment 
    106116    def getCommentByID(self, id): 
    107         query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDComment = '" + dbEscape(id) + "'" 
     117        query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDComment = %s" % id 
     118        #query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDComment = '" + dbEscape(id) + "'" 
    108119        return self.execReviewCommentQuery(query, True) 
    109120 
    110121    #Returns an array of comments for the given file 
    111122    def getCommentsByFileID(self, id): 
    112         query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' ORDER BY DateCreate" 
     123        query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = %s ORDER BY DateCreate" % id 
     124        #query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' ORDER BY DateCreate" 
    113125        return self.execReviewCommentQuery(query, False) 
    114126 
    115127    #Returns all the comments for the given file on the given line 
    116128    def getCommentsByFileIDAndLine(self, id, line): 
    117         query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' AND LineNum = '" + dbEscape(line) + "' ORDER BY DateCreate" 
     129        query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = %s AND LineNum = %s ORDER BY DateCreate" % id, line 
     130        #query = "SELECT IDComment, IDFile, IDParent, LineNum, Author, Text, AttachmentPath, DateCreate FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' AND LineNum = '" + dbEscape(line) + "' ORDER BY DateCreate" 
    118131        return self.execReviewCommentQuery(query, False) 
    119132 
    …… 
    143156    #for the given file id. 
    144157    def getCommentDictForFile(self, id): 
    145         query = "SELECT LineNum, Count(IDComment) FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' GROUP BY LineNum" 
     158        #query = "SELECT LineNum, Count(IDComment) FROM ReviewComments WHERE IDFile = '" + dbEscape(id) + "' GROUP BY LineNum" 
     159        query = "SELECT LineNum, Count(IDComment) FROM ReviewComments WHERE IDFile = '%s' GROUP BY LineNum" % id 
    146160        cursor = self.db.cursor() 
    147161        cursor.execute(query)