Example #1
0
 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
Example #2
0
    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)