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