class TestFileHandler(unittest.TestCase):
    def setUp(self):
        self.fh = FileHandler("..")

    def test_save_and_load_json(self):
        sample_json = {}
        sample_json["testbool"] = True
        sample_json["teststring"] = "hello world"

        self.fh.save_json(sample_json, "resources/sample.json")
        data = self.fh.load_json("resources/sample.json")
        self.assertEqual(data["testbool"], True)
        self.assertEqual(data["teststring"], "hello world")

    def test_save_and_load_file(self):
        data = "text message"
        self.fh.save_file(data=data, path="resources/sample.txt")
        result = self.fh.load_file("resources/sample.txt")
        self.assertEqual(result, "text message")

    def test_file_exists(self):
        data = "text message"
        self.fh.save_file(data=data, path="resources/sample.txt")
        self.assertTrue(self.fh.file_exists("resources/sample.txt"))

    def tearDown(self):
        base_path = os.path.abspath(os.path.dirname(__file__))
        filepath_json = os.path.join(base_path, '..', "resources/sample.json")
        filepath_text = os.path.join(base_path, '..', "resources/sample.txt")
        if os.path.exists(filepath_json):
            os.remove(filepath_json)
        if os.path.exists(filepath_text):
            os.remove(filepath_text)
class DatabaseHandler(object):
    def __init__(self, database_path):

        self.database_path = database_path
        self.filehandler = FileHandler(relative_root_path="..")

        if not self.filehandler.file_exists(self.database_path):
            sql_command = self.filehandler.load_file("resources/setup.sql")

            conn = sqlite3.connect(self.database_path)
            cursor = conn.cursor()
            cursor.executescript(sql_command)
            conn.commit()
            conn.close()

    def add_user(self, telegram_id, username, firstname, lastname,
                 language_code, is_bot, is_active):
        """Adds a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (str): The username of a user.
            param3 (str): The firstname of a user.
            param4 (str): The lastname of a user.
            param5 (str): The language_code of a user.
            param6 (str): The is_bot flag of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
                       (telegram_id, username, firstname, lastname,
                        language_code, is_bot, is_active))

        conn.commit()
        conn.close()

    def remove_user(self, telegram_id):
        """Removes a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("DELETE FROM user WHERE telegram_id=" +
                       str(telegram_id))

        conn.commit()
        conn.close()

    def update_user(self, telegram_id, **kwargs):
        """Updates a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (kwargs): The attributes to be updated of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE user SET "
        for key in kwargs:
            sql_command = sql_command + \
                str(key) + "='" + str(kwargs[key]) + "', "
        sql_command = sql_command[:-2] + \
            " WHERE telegram_id=" + str(telegram_id)

        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def get_user(self, telegram_id):
        """Returns a user by its id

        Args:
            param1 (int): The telegram_id of a user.

        Returns:
            list: The return value. A list containing all attributes of a user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM user WHERE telegram_id = " +
                       str(telegram_id))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def add_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT OR IGNORE INTO web (url, last_updated) VALUES (?,?)",
            (url, dh.get_datetime_now()))

        conn.commit()
        conn.close()

    def remove_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "DELETE FROM web_user WHERE url='" + str(url) + "';"
        cursor.execute(sql_command)

        sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def update_url(self, url, **kwargs):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE web SET "
        for key in kwargs:
            sql_command = sql_command + \
                str(key) + "='" + str(kwargs[key]) + "', "
        if len(kwargs) == 0:
            sql_command = sql_command + " WHERE url='" + str(url) + "';"
        else:
            sql_command = sql_command[:-2] + " WHERE url='" + str(url) + "';"

        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def get_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web WHERE url='" + str(url) + "';"

        cursor.execute(sql_command)
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_all_urls(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web;"

        cursor.execute(sql_command)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def add_user_bookmark(self, telegram_id, url, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        self.add_url(url)  # add if not exists
        cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
                       (url, telegram_id, alias))

        conn.commit()
        conn.close()

    def remove_user_bookmark(self, telegram_id, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
            (telegram_id, url))
        cursor.execute(
            "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        )

        conn.commit()
        conn.close()

    def update_user_bookmark(self, telegram_id, url, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
            (alias, telegram_id, url))

        conn.commit()
        conn.close()

    def get_user_bookmark(self, telegram_id, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
            + str(telegram_id) + " AND web_user.alias ='" + str(alias) + "';")

        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_urls_for_user(self, telegram_id):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
            + str(telegram_id) + ";")

        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_users_for_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id AND web_user.url ='"
            + str(url) + "';")
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_url_for_user_from_alias(self, telegram_id, url_alias):
        '''
        :return: The url identified by url_alias for the given user or None if it does not exist
        '''
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        query = "SELECT url FROM web_user WHERE telegram_id = %d AND alias = '%s'" % (
            telegram_id, url_alias)
        cursor.execute(query)
        url = cursor.fetchone()
        conn.commit()
        conn.close()

        if url is None:
            return None
        else:
            return url[0]

    def add_filter(self, user, filter_alias, filter_regex, url_alias):
        url = self.get_url_for_user_from_alias(user.id, url_alias)
        if not url:
            raise Exception("no url for filter and alias")

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT INTO filter VALUES (?,?,?, ?)",
                       (filter_regex, filter_alias, url, user.id))
        conn.commit()
        conn.close()

    def get_filters(self, telegram_id, url):
        '''
        :return: All filters for the specified url of the given user or None if there are none
        '''
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()
        query = "SELECT regexp FROM filter WHERE filter.url = '%s' AND filter.telegram_id = %d;" % (
            url, telegram_id)
        cursor.execute(query)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        if len(result) > 0:
            #unpack from list of tuples of strings into list of strings
            return list(chain.from_iterable(result))
        else:
            return []

    def get_filter(self, user, filter_alias, url_alias):
        url = self.get_url_for_user_from_alias(user.id, url_alias)
        if not url:
            raise Exception("no url for filter and alias")

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        query = "SELECT filter.* FROM filter WHERE filter.alias = '%s' AND filter.url = '%s' AND filter.telegram_id = %d;" % (
            filter_alias, url, user.id)
        cursor.execute(query)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result
Exemple #3
0
class DatabaseHandler(object):
    def __init__(self, database_path):

        self.database_path = database_path
        self.filehandler = FileHandler(relative_root_path="..")

        if not self.filehandler.file_exists(self.database_path):
            sql_command = self.filehandler.load_file("resources/setup.sql")

            conn = sqlite3.connect(self.database_path)
            cursor = conn.cursor()
            cursor.executescript(sql_command)
            conn.commit()
            conn.close()

    def add_user(self, telegram_id, username, firstname, lastname,
                 language_code, is_bot, is_active):
        """Adds a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (str): The username of a user.
            param3 (str): The firstname of a user.
            param4 (str): The lastname of a user.
            param5 (str): The language_code of a user.
            param6 (str): The is_bot flag of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
                       (telegram_id, username, firstname, lastname,
                        language_code, is_bot, is_active))

        conn.commit()
        conn.close()

    def remove_user(self, telegram_id):
        """Removes a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("DELETE FROM user WHERE telegram_id=" +
                       str(telegram_id))

        conn.commit()
        conn.close()

    def update_user(self, telegram_id, **kwargs):
        """Updates a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (kwargs): The attributes to be updated of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE user SET "
        for key in kwargs:
            sql_command = sql_command + \
                str(key) + "='" + str(kwargs[key]) + "', "
        sql_command = sql_command[:-2] + \
            " WHERE telegram_id=" + str(telegram_id)

        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def get_user(self, telegram_id):
        """Returns a user by its id

        Args:
            param1 (int): The telegram_id of a user.

        Returns:
            list: The return value. A list containing all attributes of a user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM user WHERE telegram_id = " +
                       str(telegram_id))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_all_users(self):
        """Returns a user by its id

        Args:
            param1 (int): The telegram_id of a user.

        Returns:
            list: The return value. A list containing all attributes of a user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        result = cursor.execute("SELECT telegram_id FROM user;")
        #result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def add_url(self, url, url_content):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()
        print("web ok")
        #timestamp = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')
        timestamp = datetime.datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')

        print("web ok, timestamp: " + timestamp)
        cursor.execute(
            "INSERT OR IGNORE INTO web (url, last_updated, url_content) VALUES (?,?,?)",
            (url, timestamp, url_content))

        conn.commit()
        conn.close()

    def remove_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "DELETE FROM web_user WHERE url='" + str(url) + "';"
        cursor.execute(sql_command)

        sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def update_url(self, url, last_updated, url_content):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE web SET last_updated = ?, url_content = ? where url = ?"
        cursor.execute(sql_command, (last_updated, url_content, url))

        conn.commit()
        conn.close()

    # def update_url(self, url, **kwargs):
    #     conn = sqlite3.connect(self.database_path)
    #     cursor = conn.cursor()

    #     sql_command = "UPDATE web SET "
    #     for key in kwargs:
    #         sql_command = sql_command + \
    #             str(key) + "='" + str(kwargs[key]) + "', "
    #     if len(kwargs) == 0:
    #         sql_command = sql_command + " WHERE url='" + str(url) + "';"
    #     else:
    #         sql_command = sql_command[:-2] + " WHERE url='" + str(url) + "';"

    #     cursor.execute(sql_command)

    #     conn.commit()
    #     conn.close()

    def get_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web WHERE url='" + str(url) + "';"
        sql_command = "SELECT * FROM web WHERE url=?;"

        cursor.execute(sql_command, (url))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_all_urls(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web;"

        cursor.execute(sql_command)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def add_user_bookmark(self, telegram_id, url, alias, url_content):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()
        print("adding bookmark")
        self.add_url(url, url_content)  # add if not exists
        print("web completed")
        cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
                       (url, telegram_id, alias))
        conn.commit()
        conn.close()

    def remove_user_bookmark(self, telegram_id, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
            (telegram_id, url))
        cursor.execute(
            "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        )

        conn.commit()
        conn.close()

    def update_user_bookmark(self, telegram_id, url, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
            (alias, telegram_id, url))

        conn.commit()
        conn.close()

    def get_user_bookmark(self, telegram_id, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
            + str(telegram_id) + " AND web_user.alias ='" + str(alias) + "';")

        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_urls_for_user(self, telegram_id):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated FROM web, web_user WHERE web_user.url = web.url AND web_user.telegram_id ="
            + str(telegram_id) + ";")

        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_users_for_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id AND web_user.url ='"
            + str(url) + "';")
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result
class DatabaseHandler(object):
    def __init__(self, database_path):

        self.database_path = database_path
        self.filehandler = FileHandler(relative_root_path="..")

        if not self.filehandler.file_exists(self.database_path):
            sql_command = self.filehandler.load_file("resources/setup.sql")

            conn = sqlite3.connect(self.database_path)
            cursor = conn.cursor()
            cursor.executescript(sql_command)
            conn.commit()
            conn.close()

    def add_user(self, telegram_id, username, firstname, lastname,
                 language_code, is_bot, is_active):
        """Adds a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (str): The username of a user.
            param3 (str): The firstname of a user.
            param4 (str): The lastname of a user.
            param5 (str): The language_code of a user.
            param6 (str): The is_bot flag of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
                       (telegram_id, username, firstname, lastname,
                        language_code, is_bot, is_active))

        conn.commit()
        conn.close()

    def remove_user(self, telegram_id):
        """Removes a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("DELETE FROM user WHERE telegram_id=?", (telegram_id, ))

        conn.commit()
        conn.close()

    def update_user(self, telegram_id, **kwargs):
        """Updates a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (kwargs): The attributes to be updated of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE user SET "
        for key in kwargs:
            sql_command += f"{key} ='{kwargs[key]}', "
        sql_command = sql_command[:-2] + f" WHERE telegram_id={telegram_id}"

        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def get_user(self, telegram_id):
        """Returns a user by its id

        Args:
            param1 (int): The telegram_id of a user.

        Returns:
            list: The return value. A list containing all attributes of a user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM user WHERE telegram_id = ?",
                       (telegram_id, ))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def add_url(self, url, items):
        """Add URL to database

        Args:
            url (string): URL to add
            items (dict): A dictionary containing the items from the given feed.
                          Dictionary in the form: { 'Item_hash': {'active': True/False, 'last_date': Str}, ...}
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT OR IGNORE INTO web (url, last_updated, items) VALUES (?,?,?)",
            (url, dh.get_datetime_now(), json.dumps(items)))

        conn.commit()
        conn.close()

    def remove_url(self, url):
        """Remove URL to database

        Args:
            url (string): URL to be removed
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "DELETE FROM web_user WHERE url=?;"
        cursor.execute(sql_command, (url, ))

        sql_command = "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def get_all_urls(self):
        """Return all URLs

        Args:
            None

        Returns:
            list: A list containing every URL
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT url FROM web;"

        cursor.execute(sql_command)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_url_items(self, url):
        """Return saved items from a feed identified by URL

        Args:
            url (string): URL of the feed

        Returns:
            Dict: A dictionary containing the saved items from the given feed or empty dict if empty.
                  Dictionary in the form: { 'Item1_hash': {'active': True/False, 'last_date': Str}, ...}
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT items FROM web WHERE url=?;", (url, ))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        if result:
            return json.loads(result[0])
        else:
            return dict()

    def update_url_items(self, url, items):
        """Update the saved items from a feed identified by URL

        Args:
            url (string): URL of the feed
            items (dict): A dictionary containing the saved items from the given feed or empty dict if empty.
                          Dictionary in the form: { 'Item1_hash': {'active': True/False, 'last_date': Str}, ...}

        Returns:
            None
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("UPDATE web SET items=? WHERE url=?;",
                       (json.dumps(items), url))

        conn.commit()
        conn.close()

    def add_user_bookmark(self, telegram_id, url, alias):
        """Add a user bookmark

        Args:
            telegram_id (int): Telegram ID of the user
            url (string): URL of the feed to add (URL must be already saved in web -table)
            alias (string): Name/Alias of the feed for this user

        Returns:
            None
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        #self.add_url(url)  # add if not exists
        cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
                       (url, telegram_id, alias))

        conn.commit()
        conn.close()

    def remove_user_bookmark(self, telegram_id, url):
        """Remove a user bookmark. Remove also from the URL table if there is no more bookmarks with this URL

        Args:
            telegram_id (int): Telegram ID of the user
            url (string): URL of the bookmark to be removed from this user

        Returns:
            None
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("DELETE FROM web_user WHERE telegram_id=? AND url = ?",
                       (telegram_id, url))
        cursor.execute(
            "DELETE FROM web WHERE web.url NOT IN (SELECT web_user.url from web_user)"
        )

        conn.commit()
        conn.close()

    def update_user_bookmark(self, telegram_id, url, alias):
        """Update a user bookmark.

        Args:
            telegram_id (int): Telegram ID of the user
            url (string): URL of the bookmark to be updated from this user
            alias (string): New name/alias of the feed for this user

        Returns:
            None
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE web_user SET alias='?' WHERE telegram_id=? AND url=?",
            (alias, telegram_id, url))

        conn.commit()
        conn.close()

    def get_user_bookmark(self, telegram_id, alias):
        """Get a user bookmark from the alias

        Args:
            telegram_id (int): Telegram ID of the user
            alias (string): Name/alias of the feed to get for this user

        Returns:
            URL (String): URL of the feed identified with alias for this user
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT url FROM web_user WHERE telegram_id =? AND alias =?;",
            (telegram_id, alias))

        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_urls_for_user(self, telegram_id):
        """Get a user's URLs

        Args:
            telegram_id (int): Telegram ID of the user

        Returns:
            List: List of [url, alias] of this user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT url, alias FROM web_user WHERE telegram_id =?;",
                       (telegram_id, ))

        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_users_for_url(self, url):
        """Get users and user's data for a given URL

        Args:
            URL (String): URL to search for

        Returns:
            List: 
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT user.*, web_user.alias FROM user, web_user WHERE web_user.telegram_id = user.telegram_id " + \
                      "AND web_user.url =? AND user.is_active = 1;"

        cursor.execute(sql_command, (url, ))
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result
Exemple #5
0
class DatabaseHandler(object):
    def __init__(self, database_path):

        self.database_path = database_path
        self.filehandler = FileHandler(relative_root_path="..")

        if not self.filehandler.file_exists(self.database_path):
            sql_command = self.filehandler.load_file("resources/setup.sql")

            conn = sqlite3.connect(self.database_path)
            cursor = conn.cursor()
            cursor.executescript(sql_command)
            conn.commit()
            conn.close()

    def add_user(self, telegram_id, username, firstname, lastname,
                 language_code, is_bot, is_active):
        """Adds a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (str): The username of a user.
            param3 (str): The firstname of a user.
            param4 (str): The lastname of a user.
            param5 (str): The language_code of a user.
            param6 (str): The is_bot flag of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT OR IGNORE INTO user VALUES (?,?,?,?,?,?,?)",
                       (telegram_id, username, firstname, lastname,
                        language_code, is_bot, is_active))

        conn.commit()
        conn.close()

    def remove_user(self, telegram_id):
        """Removes a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("DELETE FROM user WHERE telegram_id=?", (telegram_id, ))

        conn.commit()
        conn.close()

    def update_user(self, telegram_id, **kwargs):
        """Updates a user to sqlite database

        Args:
            param1 (int): The telegram_id of a user.
            param2 (kwargs): The attributes to be updated of a user.
        """

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE user SET "
        for key in kwargs:
            sql_command += str(key) + '=:' + str(key) + ','

        sql_command = sql_command[:-1] + \
            " WHERE telegram_id=:telegram_id"
        kwargs['telegram_id'] = telegram_id

        cursor.execute(sql_command, kwargs)

        conn.commit()
        conn.close()

    def get_user(self, telegram_id):
        """Returns a user by its id

        Args:
            param1 (int): The telegram_id of a user.

        Returns:
            list: The return value. A list containing all attributes of a user.
        """
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT * FROM user WHERE telegram_id=?",
                       (telegram_id, ))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def add_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT OR IGNORE INTO web (url, last_updated) VALUES (?,?)",
            (url, dh.get_datetime_now()))

        conn.commit()
        conn.close()

    def remove_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "DELETE FROM web_user WHERE url=?"
        cursor.execute(sql_command, url)

        sql_command = "DELETE FROM web " \
                      "WHERE web.url NOT IN (SELECT url from web_user) " \
                      "AND web.url NOT IN (SELECT url from web_channel) "
        cursor.execute(sql_command)

        conn.commit()
        conn.close()

    def update_url(self, url, **kwargs):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "UPDATE web SET "
        for key in kwargs:
            sql_command += str(key) + '=:' + str(key) + ','

        sql_command = sql_command[:-1] + \
            " WHERE url=:url"
        kwargs['url'] = url

        cursor.execute(sql_command, kwargs)

        conn.commit()
        conn.close()

    def get_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web WHERE url=?"

        cursor.execute(sql_command, (url, ))
        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_all_urls(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        sql_command = "SELECT * FROM web;"

        cursor.execute(sql_command)
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def add_user_bookmark(self, telegram_id, url, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        self.add_url(url)  # add if not exists
        cursor.execute("INSERT OR IGNORE INTO web_user VALUES (?,?,?)",
                       (url, telegram_id, alias))

        conn.commit()
        conn.close()

    def remove_user_bookmark(self, telegram_id, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "DELETE FROM web_user WHERE telegram_id=(?) AND url = (?)",
            (telegram_id, url))
        cursor.execute("DELETE FROM web "
                       "WHERE web.url NOT IN (SELECT url from web_user) "
                       "AND web.url NOT IN (SELECT url from web_channel)")

        conn.commit()
        conn.close()

    def update_user_bookmark(self, telegram_id, url, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "UPDATE web_user SET alias=(?) WHERE telegram_id=(?) AND url=(?)",
            (alias, telegram_id, url))

        conn.commit()
        conn.close()

    def get_user_bookmark(self, telegram_id, alias):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated "
            "FROM web, web_user "
            "WHERE web_user.url = web.url "
            "AND web_user.telegram_id=? "
            "AND web_user.alias=?", (telegram_id, alias))

        result = cursor.fetchone()

        conn.commit()
        conn.close()

        return result

    def get_urls_for_user(self, telegram_id):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT web.url, web_user.alias, web.last_updated "
            "FROM web, web_user "
            "WHERE web_user.url = web.url "
            "AND web_user.telegram_id=?", (telegram_id, ))

        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_users_for_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT user.*, web_user.alias "
            "FROM user, web_user "
            "WHERE web_user.telegram_id = user.telegram_id "
            "AND web_user.url=?", (url, ))
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def add_channel(self, channel_name, url):
        self.add_url(url)

        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "INSERT INTO web_channel (url, channel_name, alias) VALUES (?,?,?)",
            (url, channel_name, ''))
        conn.commit()
        conn.close()

    def get_channels(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT channel_name, url FROM web_channel")
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result

    def get_channels_for_url(self, url):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute(
            "SELECT channel_name "
            "FROM web_channel "
            "WHERE url=? ", (url, ))
        result = cursor.fetchall()

        conn.commit()
        conn.close()

        return result