def db_save(self): """ Saves ClipKeyword record to Database """ # NOTE: This routine, at present, is ONLY used by the Database Import routine. # Therefore, it does no checking for duplicate records. If you want to # use it for other purposes, you probably have to make it smarter! # If we're using Unicode ... if 'unicode' in wx.PlatformInfo: # ... encode the text fields for this object keywordGroup = self.keywordGroup.encode(TransanaGlobal.encoding) keyword = self.keyword.encode(TransanaGlobal.encoding) # If we're not using Unicode ... else: # ... no encoding is needed keywordGroup = self.keywordGroup keyword = self.keyword # Get a Database Cursor dbCursor = DBInterface.get_db().cursor() # Create the Insert Query SQLText = """ INSERT INTO ClipKeywords2 (DocumentNum, EpisodeNum, QuoteNum, ClipNum, SnapshotNum, KeywordGroup, Keyword, Example) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """ # Adjust the query for sqlite if needed SQLText = DBInterface.FixQuery(SQLText) # Prepare the Data Values for the query values = (self.documentNum, self.episodeNum, self.quoteNum, self.clipNum, self.snapshotNum, keywordGroup, keyword, self.example) # Execute the Query dbCursor.execute(SQLText, values) # Close the Database Cursor dbCursor.close()
def lock_record(self): """ Override the DataObject Lock Method """ # If we're using the single-user version of Transana, we just need to ... if not TransanaConstants.singleUserVersion: # ... confirm that the quote has not been altered by another user since it was loaded. # To do this, first pull the LastSaveTime for this record from the database. query = """ SELECT LastSaveTime FROM Quotes2 WHERE QuoteNum = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor tempDBCursor = DBInterface.get_db().cursor() # Execute the Lock query tempDBCursor.execute(query, (self.number, )) # Get the query results rows = tempDBCursor.fetchall() # If we get exactly one row (and we should) ... if len(rows) == 1: # ... get the LastSaveTime newLastSaveTime = rows[0] # Otherwise ... else: # ... raise an exception raise RecordNotFoundError, (self.id, len(rows)) # Close the database cursor tempDBCursor.close() # if the LastSaveTime has changed, some other user has altered the record since we loaded it. if newLastSaveTime != self.lastsavetime: # ... so we need to re-load it! self.db_load_by_num(self.number) # ... lock the Transcript Record DataObject.DataObject.lock_record(self)
def get_episode_nums(self): """Get a list of Episode numbers that belong to this Series.""" notelist = [] t = self._prefix() table = self._table() query = """ SELECT EpisodeNum FROM Episodes2 a, %s b WHERE a.%sNum = b.%sNum and %sID = %%s ORDER BY EpisodeID """ % (table, t, t, t) if type(self.id).__name__ == 'unicode': id = self.id.encode(TransanaGlobal.encoding) else: id = self.id # Adjust the query for sqlite if needed c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, (id, )) r = c.fetchall() # return array of tuples with results for tup in r: notelist.append(tup[0]) c.close() return notelist
def db_load_by_name(self, collectionID, quoteID, collectionParent): """Load a record by ID / Name.""" # If we're in Unicode mode, we need to encode the parameters so that the query will work right. if 'unicode' in wx.PlatformInfo: collectionID = collectionID.encode(TransanaGlobal.encoding) quoteID = quoteID.encode(TransanaGlobal.encoding) # Get a database connection db = DBInterface.get_db() # Craft a query to get Quote data query = """SELECT * FROM Quotes2 a, Collections2 b, QuotePositions2 c WHERE QuoteID = %s AND a.CollectNum = b.CollectNum AND b.CollectID = %s AND b.ParentCollectNum = %s AND c.QuoteNum = a.QuoteNum """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (quoteID, collectionID, collectionParent)) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record returned and check later n = 1 # If not sqlite ... else: # ... we can use rowcount to know how much data was returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Document object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (quoteID, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the cursor ... c.close() # ... clear the Document object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (quoteID, 0) # Load the data into the Document object self._load_row(r) # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def db_load_by_name(self, library, episode): """Load a record by ID / Name.""" # If we're in Unicode mode, we need to encode the parameters so that the query will work right. if 'unicode' in wx.PlatformInfo: library = library.encode(TransanaGlobal.encoding) episode = episode.encode(TransanaGlobal.encoding) # Get a database connection db = DBInterface.get_db() # Craft a query to get Episode data query = """SELECT * FROM Episodes2 a, Series2 b WHERE EpisodeID = %s AND a.SeriesNum = b.SeriesNum AND b.SeriesID = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (episode, library)) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record returned and check later n = 1 # If not sqlite ... else: # ... we can use rowcount to know how much data was returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Episode object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (episode, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the cursor ... c.close() # ... clear the Episode object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (episode, 0) # Load the data into the Episode object self._load_row(r) # Load Additional Media Files, which aren't handled in the "old" code self.load_additional_vids() # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def db_load_by_num(self, num): """Load a record by record number.""" # Get a database connection db = DBInterface.get_db() # Craft a query to get Episode data query = """SELECT * FROM Episodes2 a, Series2 b WHERE EpisodeNum = %s AND a.SeriesNum = b.SeriesNum """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (num, )) # Get the number of rows returned # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # so assume one record for now n = 1 # If not sqlite ... else: # ... we can use rowcount to know the number of records returned n = c.rowcount # If we don't get exactly one result ... if (n != 1): # Close the cursor c.close() # Clear the current Episode object self.clear() # Raise an exception saying the data is not found raise RecordNotFoundError, (num, n) # If we get exactly one result ... else: # Get the data from the cursor r = DBInterface.fetch_named(c) # Load the data into the Episode object self._load_row(r) # If sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # .. close the cursor ... c.close() # ... clear the Episode object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Load Additional Media Files, which aren't handled in the "old" code self.load_additional_vids() # Refresh the Keywords self.refresh_keywords() # Close the Database cursor c.close()
def db_load_by_name(self, name): """Load a record by ID / Name. Raise a RecordNotFound exception if record is not found in database.""" # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: name = name.encode(TransanaGlobal.encoding) # Get the database connection db = DBInterface.get_db() # Define the load query query = """ SELECT * FROM Series2 WHERE SeriesID = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (name, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one row return for now n = 1 # If not sqlite ... else: # ... we can use rowcount n = c.rowcount # If not one row ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current series ... self.clear() # ... and raise an exception raise RecordNotFoundError, (name, n) # If exactly one row, or sqlite ... else: # ... get the query results ... r = DBInterface.fetch_named(c) # If sqlite and no results ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current series ... self.clear() # ... and raise an exception raise RecordNotFoundError, (name, 0) # Load the database results into the Series object self._load_row(r) # Close the database cursor ... c.close()
def db_load_by_num(self, num): """Load a record by record number.""" # Get the database connection db = DBInterface.get_db() # If we're skipping the RTF Text ... if self.skipText: # Define the query to load a Clip Transcript without text query = """SELECT TranscriptNum, TranscriptID, EpisodeNum, SourceTranscriptNum, ClipNum, SortOrder, Transcriber, ClipStart, ClipStop, Comment, MinTranscriptWidth, RecordLock, LockTime, LastSaveTime FROM Transcripts2 WHERE TranscriptNum = %s """ # If we're NOT skipping the RTF Text ... else: # Define the query to load a Clip Transcript with everything query = """SELECT * FROM Transcripts2 WHERE TranscriptNum = %s""" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the Load query c.execute(query, (num, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': n = 1 else: n = c.rowcount # If we don't have exactly ONE record ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Transcript Object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, n) # If we DO have exactly one record (or use sqlite) ... else: # ... Fetch the query results ... r = DBInterface.fetch_named(c) # If sqlite and not results are found ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Transcript object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Load the data into the Transcript Object self._load_row(r) # Close the database cursor c.close()
def _get_db_fields(self, fieldlist, c=None): """Get the values of fields from the database for the currently loaded record. Use existing cursor if it exists, otherwise create a new one. Return a tuple containing the values obtained.""" # If the object number is 0, there's no record in the database ... if self.number == 0: # ... so just return an empty tuple return () # Get the table name and the name of the Number property tablename = self._table() numname = self._num() # Create a flag tht indicates whether the cursor was passed in close_c = False # If no cursor was passed in ... if (c == None): # ... update the flag ... close_c = True # ... get a database reference ... db = DBInterface.get_db() # ... and create a database cursor c = db.cursor() # Determine the field values needed for the query fields = "" for field in fieldlist: fields = fields + field + ", " fields = fields[:-2] # Formulate the query based on the fields query = "SELECT " + fields + " FROM " + tablename + \ " WHERE " + numname + " = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) # Get query row results qr = c.fetchone() if DEBUG: print "DataObject._get_db_fields():\n", query, qr print # If we created the cursor locally (as flagged) ... if (close_c): # ... close the database cursor c.close() # Return the Query Results return qr
def db_load_by_num(self, num): """Load a record by record number.""" # Get the database connection db = DBInterface.get_db() # Define the load query query = """ SELECT * FROM Series2 WHERE SeriesNum = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (num, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one result for now n = 1 # If not sqlite ... else: # ... we can use rowcount n = c.rowcount # If something other than one record is returned ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Library object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, n) # If exactly one row is returned, or we're using sqlite ... else: # ... load the query results ... r = DBInterface.fetch_named(c) # If sqlite and no data is loaded ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Library object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Place the loaded data in the object self._load_row(r) # Close the database cursor ... c.close()
def db_load(self, name): """Load a record by Name.""" # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: name = name.encode(TransanaGlobal.encoding) # Define the SQL that loads a Core Data record query = """ SELECT * FROM CoreData2 WHERE Identifier = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a Database Cursor dbCursor = DBInterface.get_db().cursor() # Execute the SQL Statement using the Database Cursor dbCursor.execute(query, (name, )) # Check the Query Results and load the data # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so just assume we get one record for now rowCount = 1 # If MySQL, use rowcount else: rowCount = dbCursor.rowcount # If anything other that 1 record is returned, we have a problem if (rowCount != 1): # Close the Database Cursor dbCursor.close() # Raise an Exception raise RecordNotFoundError, (name, rowCount) # If exactly 1 record is returned, load the data into the Core Data Object else: # Get the Raw Data and prepare it for loading into the Object data = DBInterface.fetch_named(dbCursor) # If sqlite and no data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (data == {}): # ... close the database cursor ... dbCursor.close() # ... clear the current CoreData object ... self.clear() # and raise an exception raise RecordNotFoundError, (name, 0) # Load the prepared Raw Data into the Object self._load_row(data) # Close the Database Cursor dbCursor.close()
def db_load_by_num(self, num): """Load a record by record number.""" # Get the database connection db = DBInterface.get_db() # Define the query for loading the requested Note query = """SELECT * FROM Notes2 WHERE NoteNum = %s""" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get the Database Cursor c = db.cursor() # Execute the query c.execute(query, (num, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # ... so assume one record is returned, for now n = 1 # If not sqlite ... else: # ... we can use rowcount n = c.rowcount # If something other than one record is returned ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Note object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, n) # If one record is returned (or sqlite) ... else: # ... get the values from the query ... r = DBInterface.fetch_named(c) # ... if sqlite and no data ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Note object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # ... load the data into the Note object self._load_row(r) # Close the database cursor c.close()
def db_load_by_num(self, num): """Load a record by record number. Raise a RecordNotFound exception if record is not found in database.""" # Get a reference to the database db = DBInterface.get_db() # Define the "Load" query query = """ SELECT * FROM Collections2 WHERE CollectNum = %s """ # Adjust query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the query c.execute(query, (num, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': n = 1 else: n = c.rowcount # if we don't get exactly one result ... if (n != 1): # close the cursor c.close() # clear the current Collection self.clear() # Raise an exception saying the record is not found raise RecordNotFoundError, (num, n) # If we get exactly one result ... else: # get the data from the cursor r = DBInterface.fetch_named(c) # if sqlite and no results ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current object ... self.clear() # Raise an exception saying the record is not found raise RecordNotFoundError, (num, 0) # Load the data into the Collection object self._load_row(r) # Close the Database Cursor c.close()
def db_delete(self, use_transactions=1): """Delete this object record from the database.""" result = 1 try: # Initialize delete operation, begin transaction if necessary (db, c) = self._db_start_delete(use_transactions) # Delete the QuotePosition data, if it exists query = "DELETE FROM QuotePositions2 WHERE QuoteNum = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) notes = [] # Detect, Load, and Delete all Quote Notes. notes = self.get_note_nums() for note_num in notes: note = Note.Note(note_num) result = result and note.db_delete(0) del note del notes # Delete all related references in the ClipKeywords table if result: DBInterface.delete_all_keywords_for_a_group( 0, 0, 0, self.number, 0) # Delete the actual record. self._db_do_delete(use_transactions, c, result) # Cleanup c.close() self.clear() except RecordLockedError, e: # if a sub-record is locked, we may need to unlock the Transcript record (after rolling back the Transaction) if self.isLocked: # c (the database cursor) only exists if the record lock was obtained! # We must roll back the transaction before we unlock the record. c.execute("ROLLBACK") c.close() self.unlock_record() raise e
def refresh_quotes(self): # Clear the Quote List self.clear_quotes() # Get the database connection db = DBInterface.get_db() # Get a database cursor c = db.cursor() # Define the Query. No need for an ORDER BY clause! query = "SELECT QuoteNum, StartChar, EndChar FROM QuotePositions2 WHERE DocumentNum = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the Query c.execute(query, (self.number, )) # Get the query results results = c.fetchall() # For each found Quote Position record ... for rec in results: # ... add the Quote to the Quote List self.add_quote(rec[0], rec[1], rec[2])
def _set_db_fields(self, fields, values, c=None): """Set the values of fields in the database for the currently loaded record. Use existing cursor if it exists, otherwise create a new one.""" # If the object number is 0, there's no record in the database ... if self.number == 0: # ... so just return return # Get the table name and the name of the Number property tablename = self._table() numname = self._num() # Create a flag tht indicates whether the cursor was passed in close_c = False # If no cursor was passed in ... if (c == None): # ... update the flag ... close_c = True # ... get a database reference ... db = DBInterface.get_db() # ... and create a database cursor c = db.cursor() # Determine the field values needed for the query fv = "" for f, v in map(None, fields, values): fv = fv + f + " = " + "%s, " fv = fv[:-2] # Formulate the query based on the fields query = "UPDATE " + tablename + " SET " + fv + " WHERE " + numname + " = %s" # Modify the values by adding the object number on the end values = values + (self.number, ) # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, values) # If we created the cursor locally (as flagged) ... if (close_c): # ... close the database cursor c.close()
def load_additional_vids(self): """Load additional media file names from the database.""" # Get a database connection db = DBInterface.get_db() # Create a database cursor c = db.cursor() # Define the DB Query query = "SELECT MediaFile, VidLength, Offset, Audio FROM AdditionalVids2 WHERE EpisodeNum = %s ORDER BY AddVidNum" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) # For each video in the query results ... for (vidFilename, vidLength, vidOffset, audio) in c.fetchall(): # Detection of the use of the Video Root Path is platform-dependent and must be done for EACH filename! if wx.Platform == "__WXMSW__": # On Windows, check for a colon in the position, which signals the presence or absence of a drive letter useVideoRoot = (vidFilename[1] != ':') and (vidFilename[:2] != '//') else: # On Mac OS-X and *nix, check for a slash in the first position for the root folder designation useVideoRoot = (vidFilename[0] != '/') # If we are using the Video Root Path, add it to the Filename if useVideoRoot: video = TransanaGlobal.configData.videoPath.replace( '\\', '/') + DBInterface.ProcessDBDataForUTF8Encoding( vidFilename) else: video = DBInterface.ProcessDBDataForUTF8Encoding(vidFilename) # Add the video to the additional media files list self.additional_media_files = { 'filename': video, 'length': vidLength, 'offset': vidOffset, 'audio': audio } # If the video offset is less than 0 and is smaller than the current smallest (most negative) offset ... if (vidOffset < 0) and (vidOffset < -self.offset): # ... then use this video offset as the global offset self.offset = abs(vidOffset) # Close the database cursor c.close()
def _get_db_fields(self, fieldlist, c=None): """Get the values of fields from the database for the currently loaded record. Use existing cursor if it exists, otherwise create a new one. Return a tuple containing the values obtained.""" if (self.originalKeywordGroup == None) or \ (self.originalKeyword == None): # no record loaded? return () # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword tablename = self._table() close_c = 0 if (c == None): close_c = 1 db = DBInterface.get_db() c = db.cursor() fields = "" for field in fieldlist: fields = fields + field + ", " fields = fields[:-2] query = "SELECT " + fields + " FROM " + tablename + "\n" + \ " WHERE KeywordGroup = %s AND\n" + \ " Keyword = %s\n" query = DBInterface.FixQuery(query) c.execute(query, (originalKeywordGroup, originalKeyword)) qr = c.fetchone() # get query row results if (close_c): c.close() return qr
def db_load_by_clipnum(self, clip): """ Load a Transcript Record based on Clip Number """ # Get the database connection db = DBInterface.get_db() # Define the query to load a Clip Transcript query = """SELECT * FROM Transcripts2 a WHERE ClipNum = %s """ # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Get a database cursor c = db.cursor() # Execute the Load query c.execute(query, (clip, )) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': n = 1 else: n = c.rowcount # If we don't have exactly ONE record ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Transcript Object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (clip, n) # If we DO have exactly one record (or use sqlite) ... else: # ... Fetch the query results ... r = DBInterface.fetch_named(c) # If sqlite and not results are found ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Transcript object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (num, 0) # Load the data into the Transcript Object self._load_row(r) # Close the database cursor c.close()
def _set_db_fields(self, fields, values, c=None): """Set the values of fields in the database for the currently loaded record. Use existing cursor if it exists, otherwise create a new one.""" if (self.originalKeywordGroup == None) or \ (self.originalKeyword == None): # no record loaded? return # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword tablename = self._table() close_c = 0 if (c == None): close_c = 1 db = DBInterface.get_db() c = db.cursor() fv = "" for f, v in map(None, fields, values): fv = fv + f + " = " + "%s,\n\t\t" fv = fv[:-4] query = "UPDATE " + tablename + "\n" + \ " SET " + fv + "\n" + \ " WHERE KeywordGroup = %s AND\n" + \ " Keyword = %s\n" values = values + (originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) if (close_c): c.close()
def get_note_nums(self): """Get a list of Note numbers that belong to this Object.""" notelist = [] t = self._prefix() query = """ SELECT NoteNum FROM Notes2 WHERE %sNum = %%s ORDER BY NoteID """ % (t, ) # Adjust the query for sqlite if needed db = DBInterface.get_db() c = db.cursor() query = DBInterface.FixQuery(query) c.execute(query, (self.number, )) r = c.fetchall() # return array of tuples with results for tup in r: notelist.append(tup[0]) c.close() return notelist
def db_load_by_name(self, keywordGroup, keyword): """Load a record. Raise a RecordNotFound exception if record is not found in database.""" # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: keywordGroup = keywordGroup.encode(TransanaGlobal.encoding) keyword = keyword.encode(TransanaGlobal.encoding) db = DBInterface.get_db() query = """ SELECT * FROM Keywords2 WHERE KeywordGroup = %s AND Keyword = %s """ query = DBInterface.FixQuery(query) c = db.cursor() c.execute(query, (keywordGroup, keyword)) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': n = 1 else: n = c.rowcount if (n != 1): c.close() self.clear() raise RecordNotFoundError, (keywordGroup + ':' + keyword, n) else: r = DBInterface.fetch_named(c) if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): c.close() self.clear() raise RecordNotFoundError, (keywordGroup + ':' + keyword, 0) self._load_row(r) c.close()
def _db_do_delete(self, use_transactions, c, result): """Do the actual record delete and handle the transaction as needed. This is a helper method intended for sub-class db_delete() methods.""" tablename = self._table() numname = self._num() # Define the Delete query query = "DELETE FROM " + tablename + \ " WHERE " + numname + " = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) # If we're using Transactions ... if (use_transactions): # ... and the result exists ... if (result): # Commit the transaction c.execute("COMMIT") if DEBUG: print "Transaction committed" # ... and the result does NOT exist (failed) .... else: # Rollback transaction because some part failed c.execute("ROLLBACK") if DEBUG: print "Transaction rolled back" # if the object has a number (and therefore existed before) ... if (self.number != 0): # ... release the record lock when the delete fails self.unlock_record() if DEBUG: print "Record '%s' unlocked" % self.id
def db_save(self, use_transactions=True): """Save the record to the database using Insert or Update as appropriate.""" # Define and implement Demo Version limits if TransanaConstants.demoVersion and (self._db_start_save() == 0): # Get a DB Cursor c = DBInterface.get_db().cursor() # Find out how many records exist c.execute('SELECT COUNT(Keyword) from Keywords2') res = c.fetchone() c.close() # Define the maximum number of records allowed maxKeywords = TransanaConstants.maxKeywords # Compare if res[0] >= maxKeywords: # If the limit is exceeded, create and display the error using a SaveError exception prompt = _( 'The Transana Demonstration limits you to %d Keyword records.\nPlease cancel the "Add Keyword" dialog to continue.' ) if 'unicode' in wx.PlatformInfo: prompt = unicode(prompt, 'utf8') raise SaveError, prompt % maxKeywords # Validity Checks if (self.keywordGroup == ''): raise SaveError, _('Keyword Group is required.') elif (self.keyword == ''): raise SaveError, _('Keyword is required.') # If we're in Unicode mode, ... if 'unicode' in wx.PlatformInfo: # Encode strings to UTF8 before saving them. The easiest way to handle this is to create local # variables for the data. We don't want to change the underlying object values. Also, this way, # we can continue to use the Unicode objects where we need the non-encoded version. (error messages.) keywordGroup = self.keywordGroup.encode(TransanaGlobal.encoding) keyword = self.keyword.encode(TransanaGlobal.encoding) if self.originalKeywordGroup != None: originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) else: originalKeywordGroup = None if self.originalKeyword != None: originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) else: originalKeyword = None definition = self.definition.encode(TransanaGlobal.encoding) lineColorName = self.lineColorName.encode(TransanaGlobal.encoding) else: # If we don't need to encode the string values, we still need to copy them to our local variables. keywordGroup = self.keywordGroup keyword = self.keyword if self.originalKeywordGroup != None: originalKeywordGroup = self.originalKeywordGroup else: originalKeywordGroup = None if self.originalKeyword != None: originalKeyword = self.originalKeyword else: originalKeyword = None definition = self.definition lineColorName = self.lineColorName values = (keywordGroup, keyword, definition, lineColorName, self.lineColorDef, self.drawMode, self.lineWidth, self.lineStyle) if (self._db_start_save() == 0): # duplicate Keywords are not allowed if DBInterface.record_match_count("Keywords2", \ ("KeywordGroup", "Keyword"), \ (keywordGroup, keyword) ) > 0: if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = _('A Keyword named "%s : %s" already exists.') if type(prompt) == str: prompt = unicode(prompt, 'utf8') prompt = prompt % (self.keywordGroup, self.keyword) else: prompt = _('A Keyword named "%s : %s" already exists.') % ( self.keywordGroup, self.keyword) raise SaveError, prompt # insert the new Keyword query = """ INSERT INTO Keywords2 (KeywordGroup, Keyword, Definition, LineColorName, LineColorDef, DrawMode, LineWidth, LineStyle) VALUES (%s, %s, %s, %s, %s, %s, %s, %s) """ c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) # if TransanaConstants.DBInstalled in ['sqlite3']: # DBInterface.get_db().commit() c.close() # When inserting, we're not merging keywords! mergeKeywords = False else: # check for dupes, which are not allowed if either the Keyword Group or Keyword have been changed. if (DBInterface.record_match_count("Keywords2", \ ("KeywordGroup", "Keyword"), \ (keywordGroup, keyword) ) > 0) and \ ((originalKeywordGroup != keywordGroup) or \ (originalKeyword.lower() != keyword.lower())): # If duplication is found, ask the user if we should MERGE the keywords. if 'unicode' in wx.PlatformInfo: oKG = unicode(originalKeywordGroup, 'utf8') oKW = unicode(originalKeyword, 'utf8') # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode( _('A Keyword named "%s : %s" already exists. Do you want to merge\n"%s : %s" with "%s : %s"?' ), 'utf8') % (self.keywordGroup, self.keyword, oKG, oKW, self.keywordGroup, self.keyword) else: prompt = _( 'A Keyword named "%s : %s" already exists. Do you want to merge\n"%s : %s" with "%s : %s"?' ) % (self.keywordGroup, self.keyword, originalKeywordGroup, originalKeyword, self.keywordGroup, self.keyword) dlg = Dialogs.QuestionDialog(None, prompt) result = dlg.LocalShowModal() dlg.Destroy() # If the user wants to merge ... if result == wx.ID_YES: # .. then signal the user's desire to merge. mergeKeywords = True # If the user does NOT want to merge keywords ... else: # ... then signal the user's desire NOT to merge (though this no longer matters!) mergeKeywords = False # ... and raise the duplicate keyword error exception if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode( _('A Keyword named "%s : %s" already exists.'), 'utf8') % (self.keywordGroup, self.keyword) else: prompt = _('A Keyword named "%s : %s" already exists.' ) % (self.keywordGroup, self.keyword) raise SaveError, prompt # If there are NO duplicate keywords ... else: # ... then signal that there is NO need to merge! mergeKeywords = False # NOTE: This is a special instance. Keywords behave differently than other DataObjects here! # Before we can save, we have to check to see if someone locked an Episode or a Clip that # uses our Keyword since we obtained the lock on the Keyword. It doesn't make sense to me # to block editing Episode or Clips properties because some else is editing a Keyword it contains. # Mostly, editing Keywords will have to do with changing their definition field, not their keyword # group or keyword fields. Because of that, we have to block the save of an altered keyword # because the locked Episode or Clip would retain the obsolete keyword and would lose the # new keyword record, so it would not appear in all Search results that it should. I expect # this to be extraordinarily rare. (EpisodeClipLockCount, LockName) = self.checkEpisodesClipsSnapshotsForLocks() if EpisodeClipLockCount != 0 and \ ((originalKeywordGroup != keywordGroup) or \ (originalKeyword != keyword)): tempstr = _( """You cannot proceed because another user has recently started editing a Document, Episode, Quote, Clip, or Snapshot that uses Keyword "%s:%s". If you change the Keyword now, that would corrupt the record that is currently locked by %s. Please try again later.""" ) if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. tempstr = unicode(tempstr, 'utf8') raise SaveError(tempstr % (self.originalKeywordGroup, self.originalKeyword, LockName)) else: c = DBInterface.get_db().cursor() # If we're merging keywords ... if mergeKeywords: # We'd better do this as a Transaction! query = 'BEGIN' c.execute(query) # ... then we remove duplicate keywords and we DON'T rename the keyword self.removeDuplicatesForMerge() # If we're NOT merging keywords ... else: # update the record record with new values query = """ UPDATE Keywords2 SET KeywordGroup = %s, Keyword = %s, Definition = %s, LineColorName = %s, LineColorDef = %s, DrawMode = %s, LineWidth = %s, LineStyle = %s WHERE KeywordGroup = %s AND Keyword = %s """ values = values + (originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # If the Keyword Group or Keyword has changed, we need to update all ClipKeyword records too. if ((originalKeywordGroup != keywordGroup) or \ (originalKeyword != keyword)): query = """ UPDATE ClipKeywords2 SET KeywordGroup = %s, Keyword = %s WHERE KeywordGroup = %s AND Keyword = %s """ values = (keywordGroup, keyword, originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # If the Keyword Group or Keyword has changed, we need to update all Snapshot Keyword records too. if ((originalKeywordGroup != keywordGroup) or \ (originalKeyword != keyword)): query = """ UPDATE SnapshotKeywords2 SET KeywordGroup = %s, Keyword = %s WHERE KeywordGroup = %s AND Keyword = %s """ values = (keywordGroup, keyword, originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # If the Keyword Group or Keyword has changed, we need to update all Snapshot Keyword Style records too. if ((originalKeywordGroup != keywordGroup) or \ (originalKeyword != keyword)): query = """ UPDATE SnapshotKeywordStyles2 SET KeywordGroup = %s, Keyword = %s WHERE KeywordGroup = %s AND Keyword = %s """ values = (keywordGroup, keyword, originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # If we're merging Keywords, we need to DELETE the original keyword and end the transaction if mergeKeywords: # Since we've already taken care of the Clip Keywords, we can just delete the keyword! query = """ DELETE FROM Keywords2 WHERE KeywordGroup = %s AND Keyword = %s""" values = (originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # If we make it this far, we can commit the transaction, 'cause we're done. query = 'COMMIT' c.execute(query) ## if TransanaConstants.DBInstalled in ['sqlite3']: ## c.commit() c.close() # If the save is successful, we need to update the "original" values to reflect the new record key. # Otherwise, we can't unlock the proper record, among other things. self.originalKeywordGroup = self.keywordGroup self.originalKeyword = self.keyword # We need to signal if the we need to update (or delete) the keyword listing in the database tree. return not mergeKeywords
def removeDuplicatesForMerge(self): """ When merging keywords, we need to remove instances of the OLD keyword that already exist in Documents, Episodes, Quotes, Clips, or Snapshots that also contain the NEW keyword. (Doing it this way reduces overhead.) """ # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: # Encode strings to UTF8 before saving them. The easiest way to handle this is to create local # variables for the data. We don't want to change the underlying object values. Also, this way, # we can continue to use the Unicode objects where we need the non-encoded version. (error messages.) originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) keywordGroup = self.keywordGroup.encode(TransanaGlobal.encoding) keyword = self.keyword.encode(TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword keywordGroup = self.keywordGroup keyword = self.keyword # Look for Episodes, Clips, and Whole-Snapshots that have BOTH the original and the merge keywords query = """SELECT a.EpisodeNum, a.DocumentNum, a.ClipNum, a.QuoteNum, a.SnapshotNum, a.KeywordGroup, a.Keyword FROM ClipKeywords2 a, ClipKeywords2 b WHERE a.KeywordGroup = %s AND a.Keyword = %s AND b.KeywordGroup = %s AND b.Keyword = %s AND a.EpisodeNum = b.EpisodeNum AND a.DocumentNum = b.DocumentNum AND a.ClipNum = b.ClipNum AND a.QuoteNum = b.QuoteNum AND a.SnapshotNum = b.SnapshotNum""" values = (originalKeywordGroup, originalKeyword, keywordGroup, keyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) # Remember the list of what would become duplicate entries result = c.fetchall() # Prepare a query for deleting the duplicate query = """ DELETE FROM ClipKeywords2 WHERE EpisodeNum = %s AND DocumentNum = %s AND ClipNum = %s AND QuoteNum = %s AND SnapshotNum = %s AND KeywordGroup = %s AND Keyword = %s """ query = DBInterface.FixQuery(query) # Go through the list of duplicates ... for line in result: # ... and delete the original keyword listing, leaving the other (merge) record untouched. values = (line[0], line[1], line[2], line[3], line[4], line[5], line[6]) c.execute(query, values) # For Snapshot Coding, we don't want to LOSE any of the drawn shapes, so we rename the OLD # Keyword Records to match the new Keyword Records query = """UPDATE SnapshotKeywords2 SET KeywordGroup = %s, Keyword = %s WHERE KeywordGroup = %s AND Keyword = %s """ values = (keywordGroup, keyword, originalKeywordGroup, originalKeyword) query = DBInterface.FixQuery(query) c.execute(query, values) # Look for Snapshots that have STYLES for BOTH the original and the merge keywords query = """SELECT * FROM SnapshotKeywordStyles2 a, SnapshotKeywordStyles2 b WHERE a.KeywordGroup = %s AND a.Keyword = %s AND b.KeywordGroup = %s AND b.Keyword = %s AND a.SnapshotNum = b.SnapshotNum """ values = (originalKeywordGroup, originalKeyword, keywordGroup, keyword) query = DBInterface.FixQuery(query) c.execute(query, values) # Remember the list of what would become duplicate entries result = c.fetchall() # Prepare a query for deleting the duplicate query = """ DELETE FROM SnapshotKeywordStyles2 WHERE SnapshotNum = %s AND KeywordGroup = %s AND Keyword = %s """ query = DBInterface.FixQuery(query) # Go through the list of duplicates ... for line in result: # ... and delete the original keyword listing, leaving the other (merge) record untouched. values = (line[0], line[1], line[2]) c.execute(query, values) c.close()
def __init__(self, dbTree, searchCount, kwg=None, kw=None, searchName=None, searchTerms=None): """ Initialize the ProcessSearch class. The dbTree parameter accepts a wxTreeCtrl as the Database Tree where Search Results should be displayed. The searchCount parameter accepts the number that should be included in the Default Search Title. Optional kwg (Keyword Group) and kw (Keyword) parameters implement Quick Search for the keyword specified. """ # Note the Database Tree that accepts Search Results self.dbTree = dbTree self.collectionList = [] # If kwg and kw are None, we are doing a regular (full) search. if ((kwg == None) or (kw == None)) and (searchTerms == None): # Create the Search Dialog Box dlg = SearchDialog.SearchDialog(_("Search") + " %s" % searchCount) # Display the Search Dialog Box and record the Result result = dlg.ShowModal() # If the user selects OK ... if result == wx.ID_OK: # ... get the search name from the dialog searchName = dlg.searchName.GetValue().strip() # Search Name is required. If it was eliminated, put it back! if searchName == '': searchName = _("Search") + " %s" % searchCount # Get the Collections Tree from the Search Form collTree = dlg.ctcCollections # Get the Collections Tree's Root Node collNode = collTree.GetRootItem() # Get a list of all the Checked Collections in the Collections Tree self.collectionList = dlg.GetCollectionList( collTree, collNode, True) # ... and get the search terms from the dialog searchTerms = dlg.searchQuery.GetValue().split('\n') # Get the includeDocuments info includeDocuments = dlg.includeDocuments.IsChecked() # Get the includeEpisodes info includeEpisodes = dlg.includeEpisodes.IsChecked() # Get the includeQuotes info includeQuotes = dlg.includeQuotes.IsChecked() # Get the includeClips info includeClips = dlg.includeClips.IsChecked() # Get the includeSnapshots info includeSnapshots = dlg.includeSnapshots.IsChecked() # Destroy the Search Dialog Box dlg.Destroy() elif (searchTerms != None): # There's no dialog. Just say the user said OK. result = wx.ID_OK # Include Clips. Do not include Documents or Episodes includeDocuments = False includeEpisodes = False includeClips = True # If Pro, Lab, or MU, include Quotes and Snapshots. if TransanaConstants.proVersion: includeQuotes = True includeSnapshots = True else: includeQuotes = False includeSnapshots = False # if kwg and kw are passed in, we're doing a Quick Search else: # There's no dialog. Just say the user said OK. result = wx.ID_OK # The Search Name is built from the kwg : kw combination searchName = "%s : %s" % (kwg, kw) # The Search Terms are just the keyword group and keyword passed in searchTerms = ["%s:%s" % (kwg, kw)] # Include Clips. Do not include Documents or Episodes includeDocuments = False includeEpisodes = False includeClips = True # If Pro, Lab, or MU, include Quotes and Snapshots. if TransanaConstants.proVersion: includeQuotes = True includeSnapshots = True else: includeQuotes = False includeSnapshots = False # If OK is pressed (or Quick Search), process the requested Search if result == wx.ID_OK: # Increment the Search Counter self.searchCount = searchCount + 1 # The "Search" node itself is always item 0 in the node list searchNode = self.dbTree.select_Node((_("Search"), ), 'SearchRootNode') # We need to collect a list of the named searches already done. namedSearches = [] # Get the first child node from the Search root node (childNode, cookieVal) = self.dbTree.GetFirstChild(searchNode) # As long as there are child nodes ... while childNode.IsOk(): # Add the node name to the named searches list ... namedSearches.append(self.dbTree.GetItemText(childNode)) # ... and get the next child node (childNode, cookieVal) = self.dbTree.GetNextChild(childNode, cookieVal) # We need to give each search result a unique name. So note the search count number nameIncrementValue = searchCount # As long as there's already a named search with the name we want to use ... while (searchName in namedSearches): # ... if this is our FIRST attempt ... if nameIncrementValue == searchCount: # ... append the appropriate number on the end of the search name searchName += unicode(_(' - Search %d'), 'utf8') % nameIncrementValue # ... if this is NOT our first attempt ... else: # ... remove the previous number and add the appropriate next number to try searchName = searchName[:searchName.rfind( ' ')] + ' %d' % nameIncrementValue # Increment our counter by one. We'll keep trying new numbers until we find one that works. nameIncrementValue += 1 # As long as there's a search name (and there's no longer a way to eliminate it! if searchName != '': # Add a Search Results Node to the Database Tree nodeListBase = [_("Search"), searchName] self.dbTree.add_Node('SearchResultsNode', nodeListBase, 0, 0, expandNode=True) # Build the appropriate Queries based on the Search Query specified in the Search Dialog. # (This method parses the Natural Language Search Terms into queries for Episode Search # Terms, for Clip Search Terms, and for Snapshot Search Terms, and includes the appropriate # Parameters to be used with the queries. Parameters are not integrated into the queries # in order to allow for automatic processing of apostrophes and other text that could # otherwise interfere with the SQL execution.) (documentQuery, episodeQuery, quoteQuery, clipQuery, wholeSnapshotQuery, snapshotCodingQuery, params) = \ self.BuildQueries(searchTerms) # Get a Database Cursor dbCursor = DBInterface.get_db().cursor() if includeEpisodes: # Adjust query for sqlite, if needed episodeQuery = DBInterface.FixQuery(episodeQuery) # Execute the Library/Episode query dbCursor.execute(episodeQuery, tuple(params)) # Process the results of the Library/Episode query for line in DBInterface.fetchall_named(dbCursor): # Add the new Transcript(s) to the Database Tree Tab. # To add a Transcript, we need to build the node list for the tree's add_Node method to climb. # We need to add the Library, Episode, and Transcripts to our Node List, so we'll start by loading # the current Library and Episode tempLibrary = Library.Library(line['SeriesNum']) tempEpisode = Episode.Episode(line['EpisodeNum']) # Add the Search Root Node, the Search Name, and the current Library and Episode Names. nodeList = (_('Search'), searchName, tempLibrary.id, tempEpisode.id) # Find out what Transcripts exist for each Episode transcriptList = DBInterface.list_transcripts( tempLibrary.id, tempEpisode.id) # If the Episode HAS defined transcripts ... if len(transcriptList) > 0: # Add each Transcript to the Database Tree for (transcriptNum, transcriptID, episodeNum) in transcriptList: # Add the Transcript Node to the Tree. self.dbTree.add_Node( 'SearchTranscriptNode', nodeList + (transcriptID, ), transcriptNum, episodeNum) # If the Episode has no transcripts, it still has the keywords and SHOULD be displayed! else: # Add the Transcript-less Episode Node to the Tree. self.dbTree.add_Node('SearchEpisodeNode', nodeList, tempEpisode.number, tempLibrary.number) if includeDocuments: # Adjust query for sqlite, if needed documentQuery = DBInterface.FixQuery(documentQuery) # Execute the Library/Document query dbCursor.execute(documentQuery, tuple(params)) # Process the results of the Library/Document query for line in DBInterface.fetchall_named(dbCursor): # Add the new Document(s) to the Database Tree Tab. # To add a Document, we need to build the node list for the tree's add_Node method to climb. # We need to add the Library and Documents to our Node List, so we'll start by loading # the current Library tempLibraryName = DBInterface.ProcessDBDataForUTF8Encoding( line['SeriesID']) tempDocument = Document.Document(line['DocumentNum']) # Add the Search Root Node, the Search Name, and the current Library Name. nodeList = (_('Search'), searchName, tempLibraryName) # Add the Document Node to the Tree. self.dbTree.add_Node('SearchDocumentNode', nodeList + (tempDocument.id, ), tempDocument.number, tempDocument.library_num) if includeQuotes: # Adjust query for sqlite, if needed quoteQuery = DBInterface.FixQuery(quoteQuery) # Execute the Collection/Quote query dbCursor.execute(quoteQuery, params) # Process all results of the Collection/Quote query for line in DBInterface.fetchall_named(dbCursor): # Add the new Quote to the Database Tree Tab. # To add a Quote, we need to build the node list for the tree's add_Node method to climb. # We need to add all of the Collection Parents to our Node List, so we'll start by loading # the current Collection tempCollection = Collection.Collection( line['CollectNum']) # Add the current Collection Node Data nodeList = tempCollection.GetNodeData() # Get the DB Values tempID = line['QuoteID'] # If we're in Unicode mode, format the strings appropriately if 'unicode' in wx.PlatformInfo: tempID = DBInterface.ProcessDBDataForUTF8Encoding( tempID) # Now add the Search Root Node and the Search Name to the front of the Node List and the # Quote Name to the back of the Node List nodeList = (_('Search'), searchName) + nodeList + (tempID, ) # Add the Node to the Tree self.dbTree.add_Node('SearchQuoteNode', nodeList, line['QuoteNum'], line['CollectNum'], sortOrder=line['SortOrder']) if includeClips: # Adjust query for sqlite, if needed clipQuery = DBInterface.FixQuery(clipQuery) # Execute the Collection/Clip query dbCursor.execute(clipQuery, params) # Process all results of the Collection/Clip query for line in DBInterface.fetchall_named(dbCursor): # Add the new Clip to the Database Tree Tab. # To add a Clip, we need to build the node list for the tree's add_Node method to climb. # We need to add all of the Collection Parents to our Node List, so we'll start by loading # the current Collection tempCollection = Collection.Collection( line['CollectNum']) # Add the current Collection Node Data nodeList = tempCollection.GetNodeData() # Get the DB Values tempID = line['ClipID'] # If we're in Unicode mode, format the strings appropriately if 'unicode' in wx.PlatformInfo: tempID = DBInterface.ProcessDBDataForUTF8Encoding( tempID) # Now add the Search Root Node and the Search Name to the front of the Node List and the # Clip Name to the back of the Node List nodeList = (_('Search'), searchName) + nodeList + (tempID, ) # Add the Node to the Tree self.dbTree.add_Node('SearchClipNode', nodeList, line['ClipNum'], line['CollectNum'], sortOrder=line['SortOrder']) if includeSnapshots: # Adjust query for sqlite, if needed wholeSnapshotQuery = DBInterface.FixQuery( wholeSnapshotQuery) # Execute the Whole Snapshot query dbCursor.execute(wholeSnapshotQuery, params) # Since we have two sources of Snapshots that get included, we need to track what we've already # added so we don't add the same Snapshot twice addedSnapshots = [] # Process all results of the Whole Snapshot query for line in DBInterface.fetchall_named(dbCursor): # Add the new Snapshot to the Database Tree Tab. # To add a Snapshot, we need to build the node list for the tree's add_Node method to climb. # We need to add all of the Collection Parents to our Node List, so we'll start by loading # the current Collection tempCollection = Collection.Collection( line['CollectNum']) # Add the current Collection Node Data nodeList = tempCollection.GetNodeData() # Get the DB Values tempID = line['SnapshotID'] # If we're in Unicode mode, format the strings appropriately if 'unicode' in wx.PlatformInfo: tempID = DBInterface.ProcessDBDataForUTF8Encoding( tempID) # Now add the Search Root Node and the Search Name to the front of the Node List and the # Clip Name to the back of the Node List nodeList = (_('Search'), searchName) + nodeList + (tempID, ) # Add the Node to the Tree self.dbTree.add_Node('SearchSnapshotNode', nodeList, line['SnapshotNum'], line['CollectNum'], sortOrder=line['SortOrder']) # Add the Snapshot to the list of Snapshots added to the Search Result addedSnapshots.append(line['SnapshotNum']) tmpNode = self.dbTree.select_Node( nodeList[:-1], 'SearchCollectionNode', ensureVisible=False) self.dbTree.SortChildren(tmpNode) # Adjust query for sqlite if needed snapshotCodingQuery = DBInterface.FixQuery( snapshotCodingQuery) # Execute the Snapshot Coding query dbCursor.execute(snapshotCodingQuery, params) # Process all results of the Snapshot Coding query for line in DBInterface.fetchall_named(dbCursor): # If the Snapshot is NOT already in the Search Results ... if not (line['SnapshotNum'] in addedSnapshots): # Add the new Snapshot to the Database Tree Tab. # To add a Snapshot, we need to build the node list for the tree's add_Node method to climb. # We need to add all of the Collection Parents to our Node List, so we'll start by loading # the current Collection tempCollection = Collection.Collection( line['CollectNum']) # Add the current Collection Node Data nodeList = tempCollection.GetNodeData() # Get the DB Values tempID = line['SnapshotID'] # If we're in Unicode mode, format the strings appropriately if 'unicode' in wx.PlatformInfo: tempID = DBInterface.ProcessDBDataForUTF8Encoding( tempID) # Now add the Search Root Node and the Search Name to the front of the Node List and the # Clip Name to the back of the Node List nodeList = (_('Search'), searchName) + nodeList + (tempID, ) # Add the Node to the Tree self.dbTree.add_Node('SearchSnapshotNode', nodeList, line['SnapshotNum'], line['CollectNum'], sortOrder=line['SortOrder']) # Add the Snapshot to the list of Snapshots added to the Search Result addedSnapshots.append(line['SnapshotNum']) tmpNode = self.dbTree.select_Node( nodeList[:-1], 'SearchCollectionNode', ensureVisible=False) self.dbTree.SortChildren(tmpNode) else: self.searchCount = searchCount # If the Search Dialog is cancelled, do NOT increment the Search Number else: self.searchCount = searchCount
def db_save(self, use_transactions=True): """ Save the record to the database using Insert or Update as appropriate. """ # Sanity Checks if ((self.series_num == 0) or (self.series_num == None)) and \ ((self.episode_num == 0) or (self.episode_num == None)) and \ ((self.collection_num == 0) or (self.collection_num == None)) and \ ((self.clip_num == 0) or (self.clip_num == None)) and \ ((self.transcript_num == 0) or (self.transcript_num == None)) and \ ((self.snapshot_num == 0) or (self.snapshot_num == None)) and \ ((self.document_num == 0) or (self.document_num == None)) and \ ((self.quote_num == 0) or (self.quote_num == None)): if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode(_("Note %s is not assigned to any object."), 'utf8') else: prompt = _("Note %s is not assigned to any object.") raise SaveError, prompt % self.id # If we're in Unicode mode, ... if 'unicode' in wx.PlatformInfo: # Encode strings to UTF8 before saving them. The easiest way to handle this is to create local # variables for the data. We don't want to change the underlying object values. Also, this way, # we can continue to use the Unicode objects where we need the non-encoded version. (error messages.) id = self.id.encode(TransanaGlobal.encoding) author = self.author.encode(TransanaGlobal.encoding) text = self.text.encode(TransanaGlobal.encoding) else: # If we don't need to encode the string values, we still need to copy them to our local variables. id = self.id author = self.author text = self.text values = (id, self.series_num, self.episode_num, self.collection_num, self.clip_num, self.transcript_num, \ self.snapshot_num, self.document_num, self.quote_num, author, text) # Determine if we are creating a new record or saving an existing one if (self._db_start_save() == 0): # Creating new record # Check to see that no identical record exists if DBInterface.record_match_count('Notes2', \ ("NoteID", "SeriesNum", "EpisodeNum", "CollectNum", "ClipNum", "TranscriptNum", \ "SnapshotNum", "DocumentNum", "QuoteNum"), \ (id, self.series_num, self.episode_num, self.collection_num, self.clip_num, self.transcript_num, \ self.snapshot_num, self.document_num, self.quote_num) ) > 0: targetObject = _('object') if (self.series_num != 0) and (self.series_num != None): targetObject = _('Libraries') elif (self.episode_num != 0) and (self.episode_num != None): targetObject = _('Episode') elif (self.transcript_num != 0) and (self.transcript_num != None): targetObject = _('Transcript') elif (self.collection_num != 0) and (self.collection_num != None): targetObject = _('Collection') elif (self.clip_num != 0) and (self.clip_num != None): targetObject = _('Clip') elif (self.snapshot_num != 0) and (self.snapshot_num != None): targetObject = _('Snapshot') elif (self.document_num != 0) and (self.document_num != None): targetObject = _('Document') elif (self.quote_num != 0) and (self.quote_num != None): targetObject = _('Quote') if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode( _('A Note named "%s" already exists for this %s.'), 'utf8') targetObject = unicode(targetObject, 'utf8') else: prompt = _('A Note named "%s" already exists for this %s.') raise SaveError, prompt % (self.id, targetObject) # insert a new record query = """ INSERT INTO Notes2 (NoteID, SeriesNum, EpisodeNum, CollectNum, ClipNum, TranscriptNum, SnapshotNum, DocumentNum, QuoteNum, NoteTaker, NoteText) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ else: # Saving an existing record # Check to see that no identical record with a different number exists (!NoteNum specifies "Not same note number") if DBInterface.record_match_count('Notes2', \ ("NoteID", "SeriesNum", "EpisodeNum", "CollectNum", "ClipNum", "TranscriptNum", \ "SnapshotNum", "DocumentNum", "QuoteNum", "!NoteNum"), \ (id, self.series_num, self.episode_num, self.collection_num, self.clip_num, self.transcript_num, \ self.snapshot_num, self.document_num, self.quote_num, self.number) ) > 0: targetObject = _('object') if (self.series_num != 0) and (self.series_num != None): targetObject = _('Libraries') elif (self.episode_num != 0) and (self.episode_num != None): targetObject = _('Episode') elif (self.transcript_num != 0) and (self.transcript_num != None): targetObject = _('Transcript') elif (self.collection_num != 0) and (self.collection_num != None): targetObject = _('Collection') elif (self.clip_num != 0) and (self.clip_num != None): targetObject = _('Clip') elif (self.snapshot_num != 0) and (self.snapshot_num != None): targetObject = _('Snapshot') elif (self.document_num != 0) and (self.document_num != None): targetObject = _('Document') elif (self.quote_num != 0) and (self.quote_num != None): targetObject = _('Quote') if 'unicode' in wx.PlatformInfo: # Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data. prompt = unicode( _('A Note named "%s" already exists for this %s.'), 'utf8') targetObject = unicode(targetObject, 'utf8') else: prompt = _('A Note named "%s" already exists for this %s.') raise SaveError, prompt % (self.id, targetObject) # Update the existing record query = """ UPDATE Notes2 SET NoteID = %s, SeriesNum = %s, EpisodeNum = %s, CollectNum = %s, ClipNum = %s, TranscriptNum = %s, SnapshotNum = %s, DocumentNum = %s, QuoteNum = %s, NoteTaker = %s, NoteText = %s WHERE NoteNum = %s """ values = values + (self.number, ) # Get a database cursor c = DBInterface.get_db().cursor() # Adjust query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, values) # If this not doesn't have a number, it is a new note. if self.number == 0: # If we are dealing with a brand new Note, it does not yet know its # record number. It HAS a record number, but it is not known yet. # The following query should produce the correct record number. query = """ SELECT NoteNum FROM Notes2 WHERE NoteID = %s AND SeriesNum = %s AND EpisodeNum = %s AND CollectNum = %s AND ClipNum = %s AND TranscriptNum = %s AND SnapshotNum = %s AND DocumentNum = %s AND QuoteNum = %s """ # Adjust query for sqlite if needed query = DBInterface.FixQuery(query) # Get a temporary database cursor tempDBCursor = DBInterface.get_db().cursor() # Execute the query tempDBCursor.execute( query, (id, self.series_num, self.episode_num, self.collection_num, self.clip_num, self.transcript_num, self.snapshot_num, self.document_num, self.quote_num)) # Get the query results data = tempDBCursor.fetchall() # If there is one record ... if len(data) == 1: # ... get the note number self.number = data[0][0] # ... otherwise ... else: # ... raise an exception raise RecordNotFoundError, (self.id, len(data)) # Close the temporary database cursor tempDBCursor.close() # Close the main database cursor c.close()
def db_delete(self, use_transactions=1): """Delete this object record from the database.""" result = 1 try: # Initialize delete operation, begin transaction if necessary (db, c) = self._db_start_delete(use_transactions) # Delete all Episode-based Filter Configurations # Delete Episode Keyword Map records DBInterface.delete_filter_records(1, self.number) # Delete Episode Keyword Visualization records DBInterface.delete_filter_records(2, self.number) # Delete Episode Clip Data Export records DBInterface.delete_filter_records(3, self.number) # Delete Episode Clip Data Coder Reliability Export records (Kathleen Liston's code) DBInterface.delete_filter_records(8, self.number) # Delete Episode Report records DBInterface.delete_filter_records(11, self.number) # Detect, Load, and Delete all Clip Notes. notes = self.get_note_nums() for note_num in notes: note = Note.Note(note_num) result = result and note.db_delete(0) del note del notes for (transcriptNo, transcriptID, transcriptEpisodeNo) in DBInterface.list_transcripts( self.series_id, self.id): # To save time here, we can skip loading the actual transcript text, which can take time once we start dealing with images! trans = Transcript.Transcript(transcriptNo, skipText=True) # if transcript delete fails, rollback clip delete result = result and trans.db_delete(0) del trans # Delete all related references in the ClipKeywords table if result: DBInterface.delete_all_keywords_for_a_group( self.number, 0, 0, 0, 0) if result: # Craft a query to remove all existing Additonal Videos query = "DELETE FROM AdditionalVids2 WHERE EpisodeNum = %s" # Adjust the query for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (self.number, )) # We need a user confirmation when Snapshot will be orphaned. # Snapshots with this Episode number need to be cleared. # This must be done LAST, after everything else. if result: DBInterface.ClearSourceEpisodeRecords(self.number) # Delete the actual record. self._db_do_delete(use_transactions, c, result) # Cleanup c.close() self.clear() except RecordLockedError, e: # if a sub-record is locked, we may need to unlock the Episode record (after rolling back the Transaction) if self.isLocked: # c (the database cursor) only exists if the record lock was obtained! # We must roll back the transaction before we unlock the record. c.execute("ROLLBACK") c.close() self.unlock_record() raise e
def checkEpisodesClipsSnapshotsForLocks(self): """ Checks Episodes, Clips, and Snapshots to see if a Keyword record is free of related locks """ # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: originalKeywordGroup = self.originalKeywordGroup.encode( TransanaGlobal.encoding) originalKeyword = self.originalKeyword.encode( TransanaGlobal.encoding) else: originalKeywordGroup = self.originalKeywordGroup originalKeyword = self.originalKeyword # query the lock status for Documents that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Documents2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.DocumentNum <> 0 AND b.DocumentNum = c.DocumentNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Document that contain the record are locked, check the lock status of Episodes that contain the Keyword if RecordCount == 0: # query the lock status for Episodes that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Episodes2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.EpisodeNum <> 0 AND b.EpisodeNum = c.EpisodeNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Documents or Episodes that contain the record are locked, check the lock status of Quotes that # contain the Keyword if RecordCount == 0: # query the lock status for Quotes that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Quotes2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.QuoteNum <> 0 AND b.QuoteNum = c.QuoteNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Documents, Episodes, or Quotes that contain the record are locked, check the lock status of # Clips that contain the Keyword if RecordCount == 0: # query the lock status for Clips that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Clips2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.ClipNum <> 0 AND b.ClipNum = c.ClipNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Documents, Episodes, Quotes, or Clips that contain the record are locked, check the lock status # of Snapshots that contain the Keyword for Whole Snapshot coding if RecordCount == 0: # query the lock status for Snapshots that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, ClipKeywords2 b, Snapshots2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.SnapshotNum <> 0 AND b.SnapshotNum = c.SnapshotNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() # If no Documents, Episodes, Quotes, Clips, or whole Snapshots that contain the record are locked, # check the lock status of Snapshots that contain the Keyword for Snapshot Coding if RecordCount == 0: # query the lock status for Snapshots that contain the Keyword query = """SELECT c.RecordLock FROM Keywords2 a, SnapshotKeywords2 b, Snapshots2 c WHERE a.KeywordGroup = %s AND a.Keyword = %s AND a.KeywordGroup = b.KeywordGroup AND a.Keyword = b.Keyword AND b.SnapshotNum <> 0 AND b.SnapshotNum = c.SnapshotNum AND (c.RecordLock <> '' AND c.RecordLock IS NOT NULL)""" values = (originalKeywordGroup, originalKeyword) c = DBInterface.get_db().cursor() query = DBInterface.FixQuery(query) c.execute(query, values) result = c.fetchall() RecordCount = len(result) c.close() if RecordCount != 0: LockName = result[0][0] return (RecordCount, LockName) else: return (RecordCount, None)
def db_load_by_name(self, note_id, **kwargs): """Load a Note by Note ID and the Series, Episode, Collection, Clip, or Transcript number the Note belongs to. Record numbers are passed for one of the parameters after Note ID. Example: db_load_by_name("My note", Collection=1) """ # If we're in Unicode mode, we need to encode the parameter so that the query will work right. if 'unicode' in wx.PlatformInfo: note_id = note_id.encode(TransanaGlobal.encoding) # Get the database connection db = DBInterface.get_db() # Determine the TYPE of note that has been requested if kwargs.has_key("Library"): q = "SeriesNum" elif kwargs.has_key("Episode"): q = "EpisodeNum" elif kwargs.has_key("Collection"): q = "CollectNum" elif kwargs.has_key("Clip"): q = "ClipNum" elif kwargs.has_key("Transcript"): q = "TranscriptNum" elif kwargs.has_key("Snapshot"): q = "SnapshotNum" elif kwargs.has_key("Document"): q = "DocumentNum" elif kwargs.has_key("Quote"): q = "QuoteNum" # Determine the NUMBER of the record type, i.e. which Series, Episode, etc. the note is attached to. num = kwargs.values()[0] # Ensure that the parameter IS a number! if type(num) != int and type(num) != long: raise ProgrammingError, _("Integer record number required.") # Define the query query = """SELECT * FROM Notes2 WHERE NoteID = %%s AND %s = %%s""" % q # Get a database cursor c = db.cursor() # Adjust the cursor for sqlite if needed query = DBInterface.FixQuery(query) # Execute the query c.execute(query, (note_id, num)) # rowcount doesn't work for sqlite! if TransanaConstants.DBInstalled == 'sqlite3': # if sqlite, assume one row for now n = 1 # If not sqlite ... else: # use rowcount n = c.rowcount # If we don't have one row ... if (n != 1): # ... close the database cursor ... c.close() # ... clear the current Note object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (note_id, n) else: # Get the object data r = DBInterface.fetch_named(c) # If sqlite and not data is returned ... if (TransanaConstants.DBInstalled == 'sqlite3') and (r == {}): # ... close the database cursor ... c.close() # ... clear the current Note object ... self.clear() # ... and raise an exception raise RecordNotFoundError, (note_id, n) # Load the data into the object self._load_row(r) # Close the database cursor c.close()