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()
