def add_link_to_db(self, url_hash, link):
"""
Takes the given hash and link and put it into database.
| **param** url_hash (str)
| **param** link (str)
| **return** short_hash (str)
"""
for i in range(self._min_url_length, len(url_hash)):
short = url_hash[:i]
try:
cursor = self._get_cursor()
cursor.execute("""INSERT INTO `link`
(`link_shorthash`,`link_hash`,`link_link`)
VALUES (%s, %s, %s)""",
(short, url_hash, link))
self.commit()
cursor.close()
return short
except DatabaseError, e:
if e.args and e.args[0] == DUP_ENTRY:
if e[1].endswith("key 2"):
return self.get_short_for_hash_from_db(url_hash)
if e[1].endswith("key 1"):
break
else:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def is_hash_blocked(self, shorthash):
"""
Checks whether given (short) hash is marked as blocked and is returning
some data about. If its not blocked, its just returning none.
| **param** shorthash (str)
| **return** list with link_id, shorthash, entry_date and comment
"""
if not shorthash:
return None
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `block`.`link_id`,
`link`.`link_shorthash`,
`block`.`entry_date`, `comment`
FROM `link`, `block`
WHERE `link`.`link_shorthash` = %s
AND `link`.`link_id` = `block`.`link_id`; ''', (shorthash))
result = cursor.fetchone()
cursor.close()
if result:
return result
else:
return None
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def mark_link_as_deleted(self, shorthash):
"""
Marks a link as deleted inside database
| **param** shothash (str)
"""
try:
cursor = self._get_cursor()
cursor.execute("""UPDATE `link`
SET `link`.`deleted` ='Y', `link`.`del_time` = now()
WHERE `link`.`link_shorthash` = %s """ % (shorthash,))
self.commit()
cursor.close()
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def add_logentry_to_database(self, shorthash):
"""
Creates a log entry inside DB for a given hash.
| **param** hash (str)
"""
try:
cursor = self._get_cursor()
cursor.execute("""INSERT into `access_log` (link_id)
SELECT link_id
FROM link
WHERE link_shorthash = (%s)""", (shorthash,))
self.commit()
cursor.close()
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def get_link_creation_timestamp(self, shorthash):
"""
Return the creation timestamp of the link based on its shorthash
| **param** shorthash (str)
| **return** timestamp (datetime)
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`entry_date`
FROM `link`
WHERE `link`.`link_shorthash` = %s''', (shorthash))
result = cursor.fetchone()
cursor.close()
return result
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def get_link_from_db(self, url_hash):
"""
Fetches the link from database based on given url_hash
| **param** url_hash (str)
| **return** url (str)
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`link_link`
FROM `link`
WHERE `link`.`link_shorthash` = %s LIMIT 1 ''', (url_hash))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def is_shorthash_in_db(self, shorthash):
"""
Checks whether a shorthash is stored in the database. If so,
it returns the link ID of the database entry.
| **param** shorthash (str)
| **return** link_id (int)
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`link_id`
FROM `link`
WHERE `link`.`link_shorthash` = %s''', (shorthash))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def is_hash_in_db(self, url_hash):
"""
Returns the link ID for a hash in case of it's available in the
database.
| **param** url_hash (str)
| **return** link_id (int)
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`link_id`
FROM `link`
WHERE `link`.`link_hash` = %s''', (url_hash))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def add_blockentry(self, shorthash, comment):
"""
Mark a link as blocked.
| **param** shorthash (str) -- short hash of link
| **comment** comment (str) -- Reason why link has been blocked
"""
try:
cursor = self._get_cursor()
cursor.execute("""INSERT INTO block( `link_id` , `comment` )
VALUES (
(
SELECT `link`.`link_id`
FROM `link`
WHERE `link`.`link_shorthash` = %s
),%s);""" % (shorthash, comment))
self.commit()
cursor.close()
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def get_link_details(self, shorthash):
"""
Returns a list with complete details of given link.
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`link_id`,
`link`.`link_shorthash`,
`link`.`link_hash`,
`link`.`link_link`,
`link`.`link_comment`,
`link`.`entry_date`
FROM `link`
WHERE `link`.`link_shorthash` = %s LIMIT 1 ''', (shorthash))
result = cursor.fetchone()
cursor.close()
if result:
return result
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def get_short_for_hash_from_db(self, url_hash):
"""
Checks, whether a short hash is already stored in the
database. If it is stored, the function will return the hash for
this shorthash, otherwise None
| **param** url_hash (str)
| **return** short_hash (str)
"""
try:
cursor = self._get_cursor()
cursor.execute('''SELECT `link`.`link_shorthash`
FROM `link`
WHERE `link`.`link_hash` = %s LIMIT 1''', (url_hash))
result = cursor.fetchone()
cursor.close()
if result:
return result[0]
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))
def get_statistics_for_hash(self, shorthash):
"""
Returns the number of calls for a particular hash
| **param** hash (str)
| **return** number of usages (int)
"""
try:
cursor = self._get_cursor()
# Is this real a nice way in terms of memory usage at DB?
cursor.execute("""SELECT count(access_time)
FROM access_log left join link on (access_log.link_id = link.link_id)
WHERE link.link_shorthash = (%s);""", (shorthash,))
# Is SELECT count(access_time)
# FROM access_log, link
# WHERE access_log.link_id = link.link_id
# AND link.link_shorthash = (%s);
# maybe better?
result = cursor.fetchone()
cursor.close()
return result[0]
except DatabaseError, e:
self.logger.error('Database error: %s' % e, exc_info=True)
raise YuDatabaseError(str(e))