Revision fd2c83de
Added by dsorber almost 12 years ago
| software/bookmark_library/lib/bookmark.py | ||
|---|---|---|
|
SQL_SELECT_BOOKMARK = "SELECT * FROM bookmarks WHERE id=?"
|
||
|
SQL_INSERT_BOOKMARK = "INSERT INTO bookmarks VALUES (?,?,?,?,?,?,?,?)"
|
||
|
SQL_UPDATE_BOOKMARK = "UPDATE bookmarks SET {:s} WHERE id=?"
|
||
|
SQL_SELECT_CAT_TAGS = "SELECT tag_id FROM category_tags where bookmark_id=?"
|
||
|
SQL_SELECT_DESC_TAGS = "SELECT tag_id FROM description_tags where bookmark_id=?"
|
||
|
SQL_SELECT_CAT_TAGS = "SELECT tag_id FROM category_tags WHERE bookmark_id=?"
|
||
|
SQL_SELECT_DESC_TAGS = "SELECT tag_id FROM description_tags WHERE bookmark_id=?"
|
||
|
|
||
|
SQL_DELETE_CAT_TAGS = "DELETE FROM category_tags WHERE bookmark_id=?"
|
||
|
SQL_DELETE_DESC_TAGS = "DELETE FROM description_tags WHERE bookmark_id=?"
|
||
|
|
||
|
SQL_INSERT_CAT_TAGS = "INSERT INTO category_tags VALUES (?, ?)"
|
||
|
SQL_INSERT_DESC_TAGS = "INSERT INTO description_tags VALUES (?, ?)"
|
||
|
|
||
|
HTTP_OKAY = 200
|
||
|
|
||
| ... | ... | |
|
self.description_tags = set()
|
||
|
|
||
|
# Retrieve an existing bookmark if an ID is passed in
|
||
|
if self.id:
|
||
|
if self.id is not None:
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(SQL_SELECT_BOOKMARK, (self.id,))
|
||
|
row = db.fetchone()
|
||
| ... | ... | |
|
|
||
|
# Grab category tags
|
||
|
for row in db.execute(SQL_SELECT_CAT_TAGS, (self.id,)):
|
||
|
self.category_tags.add(Tag(int(row[0])))
|
||
|
|
||
|
# Grab description tags
|
||
|
self.category_tags.add(Tag(int(row[0])))
|
||
|
|
||
|
# Grab description tags
|
||
|
for row in db.execute(SQL_SELECT_DESC_TAGS, (self.id,)):
|
||
|
self.description_tags.add(Tag(int(row[0])))
|
||
|
self.description_tags.add(Tag(int(row[0])))
|
||
|
|
||
|
# Store hash of set for dirty comparison
|
||
|
self._cat_tags_hash = hash(frozenset(self.category_tags))
|
||
|
self._desc_tags_hash = hash(frozenset(self.description_tags))
|
||
|
|
||
|
# This dict MUST appear *after* the data attributes, it is used to
|
||
|
# record which data attributes are dirty
|
||
| ... | ... | |
|
def _db_insert(self):
|
||
|
""" Insert a new bookmark record into the database. """
|
||
|
db = self._db_conn.cursor()
|
||
|
values = (0, self.url, self.title, self.description,
|
||
|
values = (None, self.url, self.title, self.description,
|
||
|
self.times_visited, self.last_visited, self.last_reachable,
|
||
|
self.deleted and '1' or '0')
|
||
|
db.execute(SQL_INSERT_BOOKMARK, values)
|
||
|
|
||
|
# Set bookmark ID after insert
|
||
|
self.id = db.lastrowid
|
||
|
|
||
|
# Insert any category tags
|
||
|
for tag in self.category_tags:
|
||
|
tag.save()
|
||
|
db.execute(SQL_INSERT_CAT_TAGS, (tag.id, self.id))
|
||
|
|
||
|
# Insert any description tags
|
||
|
for tag in self.description_tags:
|
||
|
tag.save()
|
||
|
db.execute(SQL_INSERT_DESC_TAGS, (tag.id, self.id))
|
||
|
|
||
|
self._db_conn.commit()
|
||
|
|
||
|
# Reset dirty flags
|
||
| ... | ... | |
|
sets.append('{:s}=?'.format(key))
|
||
|
values.append(getattr(self, key))
|
||
|
|
||
|
if not values or not sets:
|
||
|
return
|
||
|
|
||
|
# Add the record id as the last value
|
||
|
values.append(self.id)
|
||
|
if values and sets:
|
||
|
# Add the record id as the last value
|
||
|
values.append(self.id)
|
||
|
|
||
|
# Build full SQL statement then execute it
|
||
|
sql = SQL_UPDATE_BOOKMARK.format(','.join(sets))
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(sql, values)
|
||
|
self._db_conn.commit()
|
||
|
|
||
|
# Reset dirty flags
|
||
|
for key in self._dirty.keys():
|
||
|
self._dirty[key] = False
|
||
|
|
||
|
# Build full SQL statement then execute it
|
||
|
sql = SQL_UPDATE_BOOKMARK.format(','.join(sets))
|
||
|
db = self._db_conn.cursor()
|
||
|
db.execute(sql, values)
|
||
|
self._db_conn.commit()
|
||
|
# Inner function FTW
|
||
|
def update_tags_lists(delete_sql, insert_sql, tags):
|
||
|
db.execute(delete_sql, (self.id,))
|
||
|
tag_updates = []
|
||
|
for tag in tags:
|
||
|
tag.save()
|
||
|
tag_updates.append((tag.id, self.id))
|
||
|
db.executemany(insert_sql, tag_updates)
|
||
|
|
||
|
# Update tag lists if they are dirty
|
||
|
if self.category_tags_dirty:
|
||
|
update_tag_lists(SQL_DELETE_CAT_TAGS, SQL_INSERT_CAT_TAGS,
|
||
|
self.category_tags)
|
||
|
if self.description_tags_dirty:
|
||
|
update_tag_lists(SQL_DELETE_DESC_TAGS, SQL_INSERT_DESC_TAGS,
|
||
|
self.description_tags)
|
||
|
|
||
|
@property
|
||
|
def category_tags_dirty(self):
|
||
|
return self._cat_tags_hash != hash(frozenset(self.category_tags))
|
||
|
|
||
|
# Reset dirty flags
|
||
|
for key in self._dirty.keys():
|
||
|
self._dirty[key] = False
|
||
|
@property
|
||
|
def description_tags_dirty(self):
|
||
|
return self._desc_tags_hash != hash(frozenset(self.description_tags))
|
||
|
|
||
|
@property
|
||
|
def record_dirty(self):
|
||
|
""" Check if any data attributes are dirty, which indicates the record
|
||
|
is dirty.
|
||
|
"""
|
||
|
return any([val for key, val in self._dirty.items()])
|
||
|
dirty = [val for key, val in self._dirty.items()]
|
||
|
dirty.append(self.category_tags_dirty)
|
||
|
dirty.append(self.description_tags_dirty)
|
||
|
return any(dirty)
|
||
|
|
||
|
def _get_content(self):
|
||
|
""" Read the page and get its raw page HTML content. """
|
||
Adding modifications to the Bookmark and Tag classes to support the category and description tag lists. I also learned about the very awesome "lastrowid" feature.