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
"""
c = db.cursor()
c.execute(query, (keywordGroup, keyword))
n = c.rowcount
if (n != 1):
c.close()
self.clear()
raise RecordNotFoundError, (keywordGroup + ':' + keyword, n)
else:
r = DBInterface.fetch_named(c)
self._load_row(r)
c.close()
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
(EpisodeNum, ClipNum, SnapshotNum, KeywordGroup, Keyword, Example)
VALUES
(%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.episodeNum, 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):
"""Lock a record. If the lock is unable to be obtained, a
RecordLockedError exception is raised with the username of the lock
holder passed."""
if self.number == 0: # no record or new record not yet in database loaded
return
tablename = self._table()
numname = self._num()
db = DBInterface.get_db()
c = db.cursor()
lq = self._get_db_fields(('RecordLock', 'LockTime'), c)
if (lq[1] == None) or (lq[0] == "") or ((DBInterface.ServerDateTime() - lq[1]).days > 1):
# Lock the record
self._set_db_fields( ('RecordLock', 'LockTime'),
(DBInterface.get_username(),
str(DBInterface.ServerDateTime())[:-3]), c)
c.close()
# Indicate that the object was successfully locked
self._isLocked = True
if DEBUG:
print "DataObject.lock_record(): Record '%s' locked by '%s'" % (self.number, DBInterface.get_username())
else:
# We just raise an exception here since GUI code isn't appropriate.
c.close()
if DEBUG:
print "DataObject.lock_record(): Record %s locked by %s raises exception" % (self.id, lq[0])
raise RecordLockedError, lq[0] # Pass name of person
def get_shop_nearby(longitude, latitude):
'''get all shop nearby
Args:
longitude:
latitude:
Returns:
result: [[shopId, shopName, ownerName, address, shopPhone, longitude,
latitude, starRating, description, account, password],
[shopId, shopName, ownerName, address, shopPhone, longitude,
latitude, starRating, description, account, password],
...]
'''
search_range = 0.03
sql = "SELECT * FROM shop WHERE longitude < %s AND longitude > %s AND latitude < %s AND latitude > %s"
location = (float(longitude)+search_range, float(longitude)-search_range,
float(latitude)+search_range, float(latitude)-search_range)
result = DBInterface.query_for_all(sql, location)
while len(result) < 5:
search_range = float(search_range + 0.03)
location = (float(longitude)+search_range, float(longitude)-search_range,
float(latitude)+search_range, float(latitude)-search_range)
result = DBInterface.query_for_all(sql, location)
return result
def _db_start_save(self):
"""Return 0 if creating new record, 1 if updating an existing one."""
tname = type(self).__name__
if (self.keywordGroup == ""):
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
prompt = unicode(_("Cannot save a %s with a blank Keyword Group."), 'utf8') % tname
else:
prompt = _("Cannot save a %s with a blank Keyword Group.") % tname
raise SaveError, prompt
elif (self.keyword == ""):
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
prompt = unicode(_("Cannot save a %s with a blank Keyword."), 'utf8') % tname
else:
prompt = _("Cannot save a %s with a blank Keyword.") % tname
raise SaveError, prompt
else:
# Verify record lock is still good
db = DBInterface.get_db()
if ((self.originalKeywordGroup == None) and \
(self.originalKeyword == None)) or \
((self.record_lock == DBInterface.get_username()) and
((DBInterface.ServerDateTime() - self.lock_time).days <= 1)):
c = db.cursor()
# If record num is 0, this is a NEW record and needs to be
# INSERTed. Otherwise, it is an existing record to be UPDATEd.
if (self.originalKeywordGroup == None) or \
(self.originalKeyword == None): # no record loaded?
return 0
else:
return 1
else:
raise SaveError, _("Record lock no longer valid.")
def lock_record(self):
"""Lock a record. If the lock is unable to be obtained, a
RecordLockedError exception is raised with the username of the lock
holder passed."""
if (self.originalKeywordGroup == None) or \
(self.originalKeyword == None): # no record loaded?
return
tablename = self._table()
db = DBInterface.get_db()
c = db.cursor()
lq = self._get_db_fields(('RecordLock', 'LockTime'), c)
if (lq[1] == None) or (lq[0] == "") or ((DBInterface.ServerDateTime() - lq[1]).days > 1):
(EpisodeClipLockCount, LockName) = self.checkEpisodesClipsSnapshotsForLocks()
if EpisodeClipLockCount == 0:
# Lock the record
self._set_db_fields( ('RecordLock', 'LockTime'),
(DBInterface.get_username(),
str(DBInterface.ServerDateTime())[:-3]), c)
c.close()
else:
c.close()
raise RecordLockedError, LockName
else:
# We just raise an exception here since GUI code isn't appropriate.
c.close()
raise RecordLockedError, lq[0] # Pass name of person
def _load_row(self, r):
self.number = r['NoteNum']
self.id = r['NoteID']
self.comment = ''
self.series_num = r['SeriesNum']
self.episode_num = r['EpisodeNum']
self.collection_num = r['CollectNum']
self.clip_num = r['ClipNum']
self.transcript_num = r['TranscriptNum']
self.snapshot_num = r['SnapshotNum']
self.document_num = r['DocumentNum']
self.quote_num = r['QuoteNum']
self.author = r['NoteTaker']
# self.text = r['NoteText']
# Okay, this isn't so straight-forward any more.
# With MySQL for Python 0.9.x, r['NoteText'] is of type str.
# With MySQL for Python 1.2.0, r['NoteText'] is of type array. It could then either be a
# character string (typecode == 'c') or a unicode string (typecode == 'u'), which then
# need to be interpreted differently.
if type(r['NoteText']).__name__ == 'array':
if r['NoteText'].typecode == 'u':
self.text = r['NoteText'].tounicode()
else:
self.text = r['NoteText'].tostring()
else:
self.text = r['NoteText']
# If we're in Unicode mode, we need to encode the data from the database appropriately.
# (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.)
if 'unicode' in wx.PlatformInfo:
self.id = DBInterface.ProcessDBDataForUTF8Encoding(self.id)
self.comment = DBInterface.ProcessDBDataForUTF8Encoding(
self.comment)
self.author = DBInterface.ProcessDBDataForUTF8Encoding(self.author)
self.text = DBInterface.ProcessDBDataForUTF8Encoding(self.text)
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 document 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 Documents2
WHERE DocumentNum = %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 lock_record(self):
"""Lock a record. If the lock is unable to be obtained, a
RecordLockedError exception is raised with the username of the lock
holder passed."""
if (self.originalKeywordGroup == None) or \
(self.originalKeyword == None): # no record loaded?
return
tablename = self._table()
db = DBInterface.get_db()
c = db.cursor()
lq = self._get_db_fields(('RecordLock', 'LockTime'), c)
if (lq[1] == None) or (lq[0] == "") or (
(DBInterface.ServerDateTime() - lq[1]).days > 1):
(EpisodeClipLockCount,
LockName) = self.checkEpisodesClipsSnapshotsForLocks()
if EpisodeClipLockCount == 0:
# Lock the record
self._set_db_fields(('RecordLock', 'LockTime'),
(DBInterface.get_username(),
str(DBInterface.ServerDateTime())[:-3]),
c)
c.close()
else:
c.close()
raise RecordLockedError, LockName
else:
# We just raise an exception here since GUI code isn't appropriate.
c.close()
raise RecordLockedError, lq[0] # Pass name of person
def add_shop(shopName, ownerName, address, shopPhone, longitude,
latitude, description, account, password):
'''add shop when shop register
Args:
shopName:
ownerName:
address:
shopPhone:
longtitude:
latitude:
description:
account:
password:
Returns:
shopId:
'''
# m = hashlib.md5()
# m.update(password)
# password = m.digest()
# print m.digest()
sql = "INSERT INTO shop (shopName, ownerName, address, shopPhone, longitude, latitude, description, account, password) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
param = (shopName, ownerName, address, shopPhone, longitude,
latitude, description, account, password)
DBInterface.execute_sql(sql, param)
shopId = get_shopId_by_account(account)
return shopId
def db_load_by_name(self, series, 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:
series = series.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, series))
# 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 set_shopPhone(shopId, shopPhone):
'''set shop phone
Args:
shopPhone:
'''
sql = "UPDATE shop SET shopPhone=%s WHERE shopId=%s"
param = (shopPhone, shopId)
DBInterface.execute_sql(sql, param)
def db_delete(self, use_transactions=1):
"""Delete this object record from the database. Raises
RecordLockedError exception if record is locked and unable to be
deleted."""
# Assume success
result = 1
try:
# Initialize delete operation, begin transaction if necessary.
(db, c) = self._db_start_delete(use_transactions)
if (db == None):
return # Abort delete without even trying
# Delete all Series-based Filter Configurations
# Delete Series Keyword Sequence Map records
DBInterface.delete_filter_records(5, self.number)
# Delete Series Keyword Bar Graph records
DBInterface.delete_filter_records(6, self.number)
# Delete Series Keyword Percentage Map records
DBInterface.delete_filter_records(7, self.number)
# Delete Series Report records
DBInterface.delete_filter_records(10, self.number)
# Delete Series Clip Data Export records
DBInterface.delete_filter_records(14, self.number)
# Detect, Load, and Delete all Series 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
# Deletes Episodes, which in turn will delete Episode Transcripts,
# Episode Notes, and Episode Keywords
episodes = self.get_episode_nums()
for episode_num in episodes:
episode = Episode.Episode(episode_num)
# Store the result so we can rollback the transaction on failure
result = result and episode.db_delete(0)
del episode
del episodes
# 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 Series 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 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 set_shopPhone(shopId, shopPhone):
'''set shop phone
Args:
shopPhone:
'''
sql = "UPDATE shop SET shopPhone=%s WHERE shopId=%s"
param = (shopPhone, shopId)
DBInterface.execute_sql(sql, param)
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 set_description(shopId, description):
"""set shop description
Args:
shopId:
description:
"""
sql = "UPDATE shop SET description=%s WHERE shopId=%s"
param = (description, shopId)
DBInterface.execute_sql(sql, param)
def set_starRating(shopId, starRating):
"""set shop starRating
Args:
shopId:
description:
"""
sql = "UPDATE shop SET starRating=%s WHERE shopId=%s"
param = (float(starRating), int(shopId))
DBInterface.execute_sql(sql, param)
def set_password(shopId, password):
"""set shop password
Args:
shopId:
password:
"""
sql = "UPDATE shop SET password=%s WHERE shopId=%s"
param = (password, shopId)
DBInterface.execute_sql(sql, param)
def getTablesListToRun_CM(**kwargs):
logger = kwargs["logging"]
logger.info("In func : getTablesListToRun")
input_module = kwargs["module"]
input_table_list = kwargs["input_table_list"]
for table in input_table_list:
logger.info("Checking table : {} for run".format(table))
kwargs["table_name"] = table
DBInterface.checkPreviousRunForTable_DB(**kwargs)
return input_module
def set_starRating(shopId, starRating):
"""set shop starRating
Args:
shopId:
description:
"""
sql = "UPDATE shop SET starRating=%s WHERE shopId=%s"
param = (float(starRating), int(shopId))
DBInterface.execute_sql(sql, param)
def set_description(shopId, description):
"""set shop description
Args:
shopId:
description:
"""
sql = "UPDATE shop SET description=%s WHERE shopId=%s"
param = (description, shopId)
DBInterface.execute_sql(sql, param)
def set_password(shopId, password):
"""set shop password
Args:
shopId:
password:
"""
sql = "UPDATE shop SET password=%s WHERE shopId=%s"
param = (password, shopId)
DBInterface.execute_sql(sql, param)
def checkEpisodesAndClipsForLocks(self):
""" Checks Episodes and Clips 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 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()
c.execute(query, values)
result = c.fetchall()
RecordCount = len(result)
c.close()
# If no Episodes 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()
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_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_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 set_comment(recordId, starRating, contents, commentTime):
"""set record's comment.
Args:
recordId:
starRating:
contents：
commentTime:
"""
sql = "INSERT INTO comment VALUES (%s, %s, %s, %s)"
param = (recordId, starRating, contents, commentTime)
DBInterface.execute_sql(sql, param)
def _load_row(self, r):
self.number = r['SeriesNum']
self.id = r['SeriesID']
self.comment = r['SeriesComment']
self.owner = r['SeriesOwner']
self.keyword_group = r['DefaultKeywordGroup']
# If we're in Unicode mode, we need to encode the data from the database appropriately.
# (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.)
if 'unicode' in wx.PlatformInfo:
self.id = DBInterface.ProcessDBDataForUTF8Encoding(self.id)
self.comment = DBInterface.ProcessDBDataForUTF8Encoding(self.comment)
self.owner = DBInterface.ProcessDBDataForUTF8Encoding(self.owner)
self.keyword_group = DBInterface.ProcessDBDataForUTF8Encoding(self.keyword_group)
def add_favor(customerId, shopId):
"""add favor shop of customer
Args:
customerId:
shopId:
Returns:
"""
sql = "INSERT INTO favor (customerId, shopId) VALUES (%s, %s)"
param = (customerId, shopId)
DBInterface.execute_sql(sql, param)
return
def set_comment(recordId, starRating, contents, commentTime):
"""set record's comment.
Args:
recordId:
starRating:
contents：
commentTime:
"""
sql = "INSERT INTO comment VALUES (%s, %s, %s, %s)"
param = (recordId, starRating, contents, commentTime)
DBInterface.execute_sql(sql, param)
def add_record(customerId, shopId, recordTime, journeyTime):
"""add record
Args:
customerId:
shopId:
recordTime:
journeyTime:
"""
sql = "INSERT INTO record (customerId, shopId, recordTime, journeyTime) VALUES (%s, %s, %s, %s)"
param = (customerId, shopId, recordTime, journeyTime)
DBInterface.execute_sql(sql, param)
return
def add_record(customerId, shopId, recordTime, journeyTime):
"""add record
Args:
customerId:
shopId:
recordTime:
journeyTime:
"""
sql = "INSERT INTO record (customerId, shopId, recordTime, journeyTime) VALUES (%s, %s, %s, %s)"
param = (customerId, shopId, recordTime, journeyTime)
DBInterface.execute_sql(sql, param)
return
def delete_favor(customerId, shopId):
"""add favor shop of customer
Args:
customerId:
shopId:
Returns:
"""
sql = "DELETE FROM favor WHERE customerId=%s AND shopId=%s"
param = (customerId, shopId)
DBInterface.execute_sql(sql, param)
return
def OnDelKW(self, evt):
"""Invoked when the 'Delete Keyword from list' button is activated."""
kw_group = self.kw_group.GetStringSelection()
kw_name = self.kw_lb.GetStringSelection()
if kw_name == "":
return
sel = self.kw_lb.GetSelection()
# If we found the keyword we're supposed to delete ...
if TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Detect', evt, kw_group, kw_name):
# ... present an error message to the user
msg = _("Keyword %s : %s is contained in a Snapshot you are currently editing.\nYou cannot delete it at this time.")
msg = unicode(msg, 'utf8')
dlg = Dialogs.ErrorDialog(self, msg % (kw_group, kw_name))
dlg.ShowModal()
dlg.Destroy()
# Signal that we do NOT want to delete the Keyword!
result = wx.ID_NO
else:
msg = _('Are you sure you want to delete Keyword "%s" and all instances of it from Quotes, Clips, and Snapshots?')
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
msg = unicode(msg, 'utf8')
dlg = Dialogs.QuestionDialog(self, msg % kw_name)
result = dlg.LocalShowModal()
dlg.Destroy()
if result == wx.ID_YES:
try:
DBInterface.delete_keyword(self.kw_group.GetStringSelection(), kw_name)
self.kw_lb.Delete(sel)
self.definition.SetValue('')
# We need to update all open Snapshot Windows based on the change in this Keyword
TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Update', evt, kw_group, kw_name)
if not TransanaConstants.singleUserVersion:
if TransanaGlobal.chatWindow != None:
# We need the UNTRANSLATED Root Node here
msgData = "%s >|< %s >|< %s >|< %s" % ('KeywordNode', 'Keywords', self.kw_group.GetStringSelection(), kw_name)
TransanaGlobal.chatWindow.SendMessage("DN %s" % msgData)
# We need to update the Keyword Visualization no matter what here, when deleting a keyword
TransanaGlobal.chatWindow.SendMessage("UKV %s %s %s" % ('None', 0, 0))
except GeneralError, e:
# Display the Exception Message, allow "continue" flag to remain true
prompt = "%s"
data = e.explanation
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
prompt = unicode(prompt, 'utf8')
errordlg = Dialogs.ErrorDialog(None, prompt % data)
errordlg.ShowModal()
errordlg.Destroy()
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 OnDelKW(self, evt):
"""Invoked when the 'Delete Keyword from list' button is activated."""
kw_group = self.kw_group.GetStringSelection()
kw_name = self.kw_lb.GetStringSelection()
if kw_name == "":
return
sel = self.kw_lb.GetSelection()
# If we found the keyword we're supposed to delete ...
if TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Detect', evt, kw_group, kw_name):
# ... present an error message to the user
msg = _("Keyword %s : %s is contained in a Snapshot you are currently editing.\nYou cannot delete it at this time.")
msg = unicode(msg, 'utf8')
dlg = Dialogs.ErrorDialog(self, msg % (kw_group, kw_name))
dlg.ShowModal()
dlg.Destroy()
# Signal that we do NOT want to delete the Keyword!
result = wx.ID_NO
else:
msg = _('Are you sure you want to delete Keyword "%s" and all instances of it from Quotes, Clips, and Snapshots?')
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
msg = unicode(msg, 'utf8')
dlg = Dialogs.QuestionDialog(self, msg % kw_name)
result = dlg.LocalShowModal()
dlg.Destroy()
if result == wx.ID_YES:
try:
DBInterface.delete_keyword(self.kw_group.GetStringSelection(), kw_name)
self.kw_lb.Delete(sel)
self.definition.SetValue('')
# We need to update all open Snapshot Windows based on the change in this Keyword
TransanaGlobal.menuWindow.ControlObject.UpdateSnapshotWindows('Update', evt, kw_group, kw_name)
if not TransanaConstants.singleUserVersion:
if TransanaGlobal.chatWindow != None:
# We need the UNTRANSLATED Root Node here
msgData = "%s >|< %s >|< %s >|< %s" % ('KeywordNode', 'Keywords', self.kw_group.GetStringSelection(), kw_name)
TransanaGlobal.chatWindow.SendMessage("DN %s" % msgData)
# We need to update the Keyword Visualization no matter what here, when deleting a keyword
TransanaGlobal.chatWindow.SendMessage("UKV %s %s %s" % ('None', 0, 0))
except GeneralError, e:
# Display the Exception Message, allow "continue" flag to remain true
prompt = "%s"
data = e.explanation
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
prompt = unicode(prompt, 'utf8')
errordlg = Dialogs.ErrorDialog(None, prompt % data)
errordlg.ShowModal()
errordlg.Destroy()
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 checkEpisodesAndClipsForLocks(self):
""" Checks Episodes and Clips 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 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()
c.execute(query, values)
result = c.fetchall()
RecordCount = len(result)
c.close()
# If no Episodes 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()
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, 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 fsinit(self):
try:
global GlobalConfig
self.config = ConfigFactory.GetConfigByTable(
GetDBTable(self.dbname))
self.config.setDB(self.dbname)
GlobalConfig = self.config
if (self.dbname is None):
self.db = DBInterface(self.config)
else:
self.db = DBInterface(self.config, self.dbname)
except Exception, e:
print "Caught exception in FSINIT:", e
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 _load_row(self, r):
self.keywordGroup = r['KeywordGroup']
self.keyword = r['Keyword']
self.definition = r['Definition']
if self.definition == None:
self.definition = ''
# If we're in Unicode mode, we need to encode the data from the database appropriately.
# (unicode(var, TransanaGlobal.encoding) doesn't work, as the strings are already unicode, yet aren't decoded.)
if 'unicode' in wx.PlatformInfo:
self.keywordGroup = DBInterface.ProcessDBDataForUTF8Encoding(
self.keywordGroup)
self.keyword = DBInterface.ProcessDBDataForUTF8Encoding(
self.keyword)
self.definition = DBInterface.ProcessDBDataForUTF8Encoding(
self.definition)
def refresh_keyword_groups(self):
"""Refresh the keyword groups listbox."""
self.kw_groups = DBInterface.list_of_keyword_groups()
self.kw_group_lb.Clear()
self.kw_group_lb.InsertItems(self.kw_groups, 0)
if len(self.kw_groups) > 0:
self.kw_group_lb.SetSelection(0)
def register_email(self):
self.password_info = self.password.get()
self.password_entry.delete(0, END)
self.email_info = self.email_address.get()
self.email_address_entry.delete(0, END)
if (isBlank(self.email_info) or isBlank(self.password_info)):
self.empty_filler()
self.info_regis.configure(text="Failed",
justify="center",
bg="white",
fg="green",
font=("calibri", 11))
else:
db = DBInterface.DBInterface()
log = db.login(self.username_info2, self.password_info)
data2 = json.loads(log)
if (data2["status"] == True):
db.update_email(self.ID_info2, self.email_info2,
self.email_info, self.password_info)
pub_cmd.publish(client, "update email")
self.info_regis.configure(text="Success Changing Email",
justify="center",
bg="white",
fg="green",
font=("calibri", 11))
else:
self.info_regis.configure(text="Wrong Current Password",
justify="center",
bg="white",
fg="green",
font=("calibri", 11))
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 refresh_keyword_groups(self):
"""Refresh the keyword groups listbox."""
self.kw_groups = DBInterface.list_of_keyword_groups()
self.kw_group_lb.Clear()
self.kw_group_lb.InsertItems(self.kw_groups, 0)
if len(self.kw_groups) > 0:
self.kw_group_lb.SetSelection(0)
def _db_start_save(self):
"""Return 0 if creating new record, 1 if updating an existing one."""
tname = type(self).__name__
# You can save a Clip Transcript with a blank Transcript ID!
if (self.id == "") and (tname != 'Transcript'):
if 'unicode' in wx.PlatformInfo:
# Encode with UTF-8 rather than TransanaGlobal.encoding because this is a prompt, not DB Data.
prompt = unicode(_("Cannot save a %s with a blank %s ID"), 'utf8')
else:
prompt = _("Cannot save a %s with a blank %s ID")
raise SaveError, prompt % (tname, tname)
else:
# Verify record lock is still good
if (self.number == 0) or \
((self.record_lock == DBInterface.get_username()) and
((self.lock_time == None) or
((DBInterface.ServerDateTime() - self.lock_time).days <= 1))):
# If record num is 0, this is a NEW record and needs to be
# INSERTed. Otherwise, it is an existing record to be UPDATEd.
if (self.number == 0):
return 0
else:
return 1
else:
raise SaveError, _("Record lock no longer valid.")
def __init__(self, parent, numRecords = 0):
""" Create a Dialog Box to process the Database Conversion.
Parameters: parent Parent Window
numRecords The number of records that will be updated """
# Remember the total number of records passed in, or obtain that number if needed
if numRecords == 0:
self.numRecords = DBInterface.CountItemsWithoutPlainText()
else:
self.numRecords = numRecords
# Create a Dialog Box
wx.Dialog.__init__(self, parent, -1, _("Plain Text Conversion"), size=(600, 350))
# Add the Main Sizer
mainSizer = wx.BoxSizer(wx.VERTICAL)
# Add a gauge based on the number of records to be handled
self.gauge = wx.Gauge(self, -1, numRecords)
mainSizer.Add(self.gauge, 0, wx.EXPAND | wx.LEFT | wx.TOP | wx.RIGHT, 5)
# Add a TextCtrl to provide user information
self.txtCtrl = wx.TextCtrl(self, -1, "", style=wx.TE_LEFT | wx.TE_MULTILINE)
mainSizer.Add(self.txtCtrl, 1, wx.EXPAND | wx.ALL, 5)
# Add a hidden RichTextCrtl used for the conversion process
self.richTextCtrl = TranscriptEditor_RTC.TranscriptEditor(self)
self.richTextCtrl.SetReadOnly(True)
self.richTextCtrl.Enable(False)
# The user doesn't need to see this.
self.richTextCtrl.Show(False)
# Even though we add it to the sizer, it won't show up because of the Show(False).
mainSizer.Add(self.richTextCtrl, 1, wx.EXPAND | wx.ALL, 5)
# Finalize the Dialog layout
self.SetSizer(mainSizer)
self.SetAutoLayout(True)
self.Layout()
# Center the Dialog on the Screen
TransanaGlobal.CenterOnPrimary(self)
def refresh_keywords(self):
"""Clear the keyword list and refresh it from the database."""
self._kwlist = []
kwpairs = DBInterface.list_of_keywords(Quote=self.number)
for data in kwpairs:
tempClipKeyword = ClipKeywordObject.ClipKeyword(data[0], data[1], quoteNum=self.number, example=data[2])
self._kwlist.append(tempClipKeyword)
def refresh_keywords(self):
"""Clear the keyword list and refresh it from the database."""
self._kwlist = []
kwpairs = DBInterface.list_of_keywords(Episode=self.number)
for data in kwpairs:
tempClipKeyword = ClipKeywordObject.ClipKeyword(data[0], data[1], episodeNum=self.number, example=data[2])
self._kwlist.append(tempClipKeyword)
def update_customer_info(open_id, customer_name, gender, customer_phone, car_type, plate_number):
'''update customer information
Args:
open_id: customerId
customer_name:
gender:
customer_phone:
car_type:
plate_number:
'''
sql = "UPDATE customer\
SET customerName=%s, gender=%s, customerPhone=%s, carModelId=%s, plateNumber=%s\
WHERE customerId=%s"
param = (customer_name, gender, customer_phone, car_type, plate_number, open_id)
DBInterface.execute_sql(sql, param)
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 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 _get_lt(self):
# Get the Record Lock Time from the Database
lt = self._get_db_fields(('LockTime', ))
# If a Lock Time has been specified ...
if (len(lt) > 0) and (lt[0] is not None):
# ... If we're using sqlite, we get a string and need to convert it to a datetime object
if TransanaConstants.DBInstalled in ['sqlite3']:
import datetime
# Start catching exceptions
try:
# This conversion *almost* always works
tempDate = datetime.datetime.strptime(
lt[0], '%Y-%m-%d %H:%M:%S.%f')
except ValueError:
# Every once in a while, we get an lt[0] value with no SECONDS, requiring this conversion. I don't know why.
tempDate = datetime.datetime.strptime(
lt[0], '%Y-%m-%d %H:%M')
return tempDate
# ... If we're using MySQL, we get a MySQL DateTime value
else:
return lt[0]
# If we don't get a Lock Time ...
else:
# ... return the current Server Time
return DBInterface.ServerDateTime()
def update_comment_starRating(shopId):
sql = "SELECT AVG(Com.starRating)\
FROM record AS Rec, comment AS Com\
WHERE Rec.shopId=%s AND Com.recordId=Rec.recordId"
starRating = DBInterface.query_for_one(sql, shopId)[0]
set_starRating(shopId, starRating)
return