def __del__( self ): global gInstancesCount try: while 1 and self.__initialized: self.__connectionSemaphore.release() try: connection = self.__connectionQueue.get_nowait() connection.close() except Queue.Empty: self.logger.debug( 'No more connection in Queue' ) break if gInstancesCount == 1: # only when the last instance of a MySQL object is deleted, the server # can be ended MySQLdb.server_end() gInstancesCount -= 1 except Exception: pass
def __init__(self, parent, ID, title): pw = raw_input('Enter Password:'******'Transana', 'Verception') hostName = config.Read('/2.0/hostMU', '') # '128.104.150.81' dbName = config.Read('/2.0/databaseMU', '') # 'Transana_250a2' print "Unit Test: OrphanCheck.py" print print "Database Server:", hostName print "Database:", dbName print print "Checking for Orphaned Database Records:" print # Get all Series Records # Create a connection to the database DBConn = MySQLdb.Connection(host=hostName, user='******', passwd=pw, port=3306) # db=dbName, # Create a cursor and execute the appropriate query self.DBCursor = DBConn.cursor() self.DBCursor.execute('SET CHARACTER SET utf8') self.DBCursor.execute('SET character_set_connection = utf8') self.DBCursor.execute('SET character_set_client = utf8') self.DBCursor.execute('SET character_set_server = utf8') self.DBCursor.execute('SET character_set_database = utf8') self.DBCursor.execute('SET character_set_results = utf8') self.DBCursor.execute('USE %s' % dbName.encode('utf8')) print "Get Database Version" query = "SELECT Value FROM ConfigInfo WHERE KeyVal = 'DBVersion'" self.DBCursor.execute(query) Records = self.DBCursor.fetchone() DBVersion = int(Records[0]) print "Database Version:", DBVersion print print print "Check for Series Notes without Series:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum FROM Notes2 WHERE (SeriesNum <> 0) AND (SeriesNum not in (SELECT SeriesNum FROM Series2))' self.ExecQuery(query) print "Check for Episodes without Series:" query = 'SELECT EpisodeNum, EpisodeID, SeriesNum FROM Episodes2 WHERE (SeriesNum not in (SELECT SeriesNum FROM Series2))' self.ExecQuery(query) print "Check for Episode Transcripts without Episodes:" query = 'SELECT TranscriptNum, EpisodeNum, SourceTranscriptNum, ClipNum FROM Transcripts2 WHERE (ClipNum = 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' self.ExecQuery(query) print "Check for Episode Additional Video Files without Episodes:" query = 'SELECT AddVidNum, EpisodeNum, ClipNum FROM AdditionalVids2 WHERE (ClipNum = 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' self.ExecQuery(query) print "Check for Episode Keywords without Episodes:" if DBVersion > 250: query = 'SELECT EpisodeNum, ClipNum, SnapshotNum, KeywordGroup, Keyword FROM ClipKeywords2 WHERE (ClipNum = 0) AND (SnapshotNum = 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' else: query = 'SELECT EpisodeNum, ClipNum, KeywordGroup, Keyword FROM ClipKeywords2 WHERE (ClipNum = 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' self.ExecQuery(query) print "Check for Episode Notes without Episodes:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum FROM Notes2 WHERE (EpisodeNum <> 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' self.ExecQuery(query) print "Check for Transcript Notes without Transcripts:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum FROM Notes2 WHERE (TranscriptNum <> 0) AND (TranscriptNum not in (SELECT TranscriptNum FROM Transcripts2))' self.ExecQuery(query) print "Check for Nested Collections without Parents:" query = 'SELECT CollectNum, CollectID, ParentCollectNum FROM Collections2 WHERE (ParentCollectNum <> 0) AND (ParentCollectNum not in (SELECT CollectNum FROM Collections2))' self.ExecQuery(query) print "Check for Collection Notes without Collections:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum FROM Notes2 WHERE (CollectNum <> 0) AND (CollectNum not in (SELECT CollectNum FROM Collections2))' self.ExecQuery(query) print "Check for Clips without Collections:" query = 'SELECT ClipNum, ClipID, CollectNum FROM Clips2 WHERE (CollectNum not in (SELECT CollectNum FROM Collections2))' self.ExecQuery(query) print "Check for Clip Transcripts without Clips:" query = 'SELECT TranscriptNum, EpisodeNum, SourceTranscriptNum, ClipNum FROM Transcripts2 WHERE (ClipNum <> 0) AND (ClipNum not in (SELECT ClipNum FROM Clips2))' self.ExecQuery(query) print "Check for Clip Transcripts without Source Transcripts:" query = 'SELECT TranscriptNum, EpisodeNum, SourceTranscriptNum, ClipNum FROM Transcripts2 WHERE (SourceTranscriptNum <> 0) AND (SourceTranscriptNum not in (SELECT TranscriptNum FROM Transcripts2))' self.ExecQuery(query) # Known Orphans print "Known orphans: (SourceTranscriptNum = 0)", query = 'SELECT TranscriptNum, EpisodeNum, SourceTranscriptNum, ClipNum FROM Transcripts2 WHERE (ClipNum > 0) AND (SourceTranscriptNum = 0)' self.DBCursor.execute(query) print self.DBCursor.rowcount print if self.DBCursor.rowcount > 0: for Record in self.DBCursor.fetchall(): print Record print print "Check for Clips with a length of 0.0" query = 'SELECT ClipNum, ClipID, CollectNum FROM Clips2 WHERE ClipStart = ClipStop' self.ExecQuery(query) print "Check for Clip Additional Video Files without Clips:" query = 'SELECT AddVidNum, EpisodeNum, ClipNum FROM AdditionalVids2 WHERE (ClipNum <> 0) AND (ClipNum not in (SELECT ClipNum FROM Clips2))' self.ExecQuery(query) print "Check for Clip Keywords without Clips:" query = 'SELECT EpisodeNum, ClipNum, KeywordGroup, Keyword FROM ClipKeywords2 WHERE (ClipNum <> 0) AND (ClipNum not in (SELECT ClipNum FROM Clips2))' self.ExecQuery(query) print "Check for Clip Notes without Clips:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum FROM Notes2 WHERE (ClipNum <> 0) AND (ClipNum not in (SELECT ClipNum FROM Clips2))' self.ExecQuery(query) if DBVersion >= 260: print "Check for Snapshots without Episode:" query = 'SELECT SnapshotNum, SnapshotID, EpisodeNum FROM Snapshots2 WHERE (EpisodeNum > 0) AND (EpisodeNum not in (SELECT EpisodeNum FROM Episodes2))' self.ExecQuery(query) print "Check for Snapshots without Transcripts:" query = 'SELECT SnapshotNum, SnapshotID, TranscriptNum FROM Snapshots2 WHERE (TranscriptNum > 0) AND (TranscriptNum not in (SELECT TranscriptNum FROM Transcripts2))' self.ExecQuery(query) print "Check for Snapshots without Collections:" query = 'SELECT SnapshotNum, SnapshotID, CollectNum FROM Snapshots2 WHERE (CollectNum not in (SELECT CollectNum FROM Collections2))' self.ExecQuery(query) print "Check for Whole Snapshot Keywords without Snapshots:" query = 'SELECT EpisodeNum, ClipNum, SnapshotNum, KeywordGroup, Keyword FROM ClipKeywords2 WHERE (SnapshotNum <> 0) AND (SnapshotNum not in (SELECT SnapshotNum FROM Snapshots2))' self.ExecQuery(query) print "Check for Snapshot Coding Keywords without Snapshots:" query = 'SELECT SnapshotNum, KeywordGroup, Keyword FROM SnapshotKeywords2 WHERE (SnapshotNum not in (SELECT SnapshotNum FROM Snapshots2))' self.ExecQuery(query) print "Check for Snapshot Keyword Styles without Snapshots:" query = 'SELECT SnapshotNum, KeywordGroup, Keyword FROM SnapshotKeywordStyles2 WHERE (SnapshotNum not in (SELECT SnapshotNum FROM Snapshots2))' self.ExecQuery(query) print "Check for Snapshot Notes without Snapshots:" query = 'SELECT NoteNum, NoteID, SeriesNum, EpisodeNum, TranscriptNum, CollectNum, ClipNum, SnapshotNum FROM Notes2 WHERE (SnapshotNum <> 0) AND (SnapshotNum not in (SELECT SnapshotNum FROM Snapshots2))' self.ExecQuery(query) keywordList = [] print "Loading Keywords..." query = 'SELECT KeywordGroup, Keyword from Keywords2 ORDER BY KeywordGroup, Keyword' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() for Record in Records: keywordList.append((Record[0], Record[1])) # print "Adding", Record print "Keywords loaded" print testPassed = True self.testsTried += 1 print "Check for ClipKeywords without Keywords..." query = 'SELECT KeywordGroup, Keyword from ClipKeywords2' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() for Record in Records: # Stripping required for Arabic "Transana Users" group!! if not((Record[0].strip(), Record[1].strip()) in keywordList): testPassed = False break if testPassed: self.testsPassed += 1 print "PASSED" print else: for Record in Records: if not((Record[0].strip(), Record[1].strip()) in keywordList): print Record, "not found." print if DBVersion >= 260: snapshotKeywordsList = [] testPassed = True self.testsTried += 1 print "Check for SnapshotKeywords without Keywords..." query = 'SELECT KeywordGroup, Keyword, SnapshotNum from SnapshotKeywords2' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() for Record in Records: if not((Record[0], Record[1]) in keywordList): testPassed = False snapshotKeywordsList.append((Record[2], Record[0], Record[1])) if testPassed: self.testsPassed += 1 print "PASSED" print else: for Record in Records: if not((Record[0], Record[1]) in keywordList): print Record, "not found." print testPassed = True self.testsTried += 1 print "Check for SnapshotKeywordStyles without Keywords..." query = 'SELECT KeywordGroup, Keyword from SnapshotKeywordStyles2' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() for Record in Records: if not((Record[0], Record[1]) in keywordList): testPassed = False break if testPassed: self.testsPassed += 1 print "PASSED" print else: for Record in Records: if not((Record[0], Record[1]) in keywordList): print Record, "not found." print testPassed = True self.testsTried += 1 print "Check for SnapshotKeywordStyles without Coding Objects..." query = 'SELECT SnapshotNum, KeywordGroup, Keyword from SnapshotKeywordStyles2' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount # print # for rec in self.DBCursor.description: # print rec[0], '\t\t', # print if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() for Record in Records: if not ((Record[0], Record[1], Record[2]) in snapshotKeywordsList): testPassed = False break if testPassed: self.testsPassed += 1 print "PASSED" print else: for Record in Records: if not ((Record[0], Record[1], Record[2]) in snapshotKeywordsList): print Record, "not found." print testPassed = True self.testsTried += 1 print "Check for Sort Order issues in Collections ..." query = 'SELECT CollectNum, CollectID FROM Collections2' try: self.DBCursor.execute(query) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print "Count =", self.DBCursor.rowcount # print # for rec in self.DBCursor.description: # print rec[0], '\t\t', # print if self.DBCursor.rowcount > 0: Records = self.DBCursor.fetchall() DBCursor2 = DBConn.cursor() for Record in Records: SortOrder = {} query2 = 'SELECT ClipNum, ClipID, SortOrder FROM Clips2 WHERE CollectNum = %s' % Record[0] try: DBCursor2.execute(query2) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print " Count =", DBCursor2.rowcount # print Records2 = DBCursor2.fetchall() for Record2 in Records2: # print " Clip:", Record2[0], Record2[1], Record2[2] if SortOrder.has_key(Record2[2]): testPassed = False # print ' Collection ', Records[0], Records[1] print ' Clip %d - "%s" has the same Sort Order as %s "%s" in Collection "%s"' % \ (Record2[0], Record2[1], SortOrder[Record2[2]][0], SortOrder[Record2[2]][1], Record[1]) print else: SortOrder[Record2[2]] = ('Clip', Record2[1]) if DBVersion >= 260: query2 = 'SELECT SnapshotNum, SnapshotID, SortOrder FROM Snapshots2 WHERE CollectNum = %s' % Record[0] try: DBCursor2.execute(query2) except: print "Exception!", sys.exc_info()[0], sys.exc_info()[1] # print " Count =", DBCursor2.rowcount # print Records2 = DBCursor2.fetchall() for Record2 in Records2: # print " Snapshot:", Record2[0], Record2[1], Record2[2] if SortOrder.has_key(Record2[2]): testPassed = False print ' Snapshot "%s" has the same Sort Order as %s "%s" in Collection "%s"' % \ (Record2[1], SortOrder[Record2[2]][0], SortOrder[Record2[2]][1], Record[1]) print else: SortOrder[Record2[2]] = ('Snapshot', Record2[1]) if testPassed: self.testsPassed += 1 print "PASSED" print self.DBCursor.close() DBConn.close() MySQLdb.server_end() print "SUMMARY: Tests conducted: %d Tests passed: %d Tests failed: %d" % (self.testsTried, self.testsPassed, self.testsTried - self.testsPassed)