From 28debfa919d5a32cebbecd0834f12a26f310a2f4 Mon Sep 17 00:00:00 2001 From: jesopo Date: Sun, 5 Aug 2018 12:52:29 +0100 Subject: Add "user_channel_settings" table in Database.py, for settings per user per channel --- Database.py | 84 ++++++++++++++++++++++++++++++++++++++++++++++------------- IRCChannel.py | 16 ++++++++++++ 2 files changed, 82 insertions(+), 18 deletions(-) diff --git a/Database.py b/Database.py index c6efba1c..3e0a11e7 100644 --- a/Database.py +++ b/Database.py @@ -17,6 +17,7 @@ class Database(object): self.make_server_settings_table() self.make_channel_settings_table() self.make_user_settings_table() + self.make_user_channel_settings_table() def cursor(self): if self._cursor == None: @@ -91,6 +92,35 @@ class Database(object): PRIMARY KEY (server_id, nickname, setting))""") except sqlite3.Error as e: pass + def make_user_channel_settings_table(self): + try: + self.execute("""CREATE TABLE user_channel_settings + (server_id INTEGER, channel TEXT, nickname TEXT, + setting TEXT, value TEXT, FOREIGN KEY (server_id) + REFERENCES servers(server_id) ON DELETE CASCADE, + PRIMARY KEY (server_id, nickname, channel, setting))""") + except sqlite3.Error as e: + pass + + def add_server(self, hostname, port, password, ipv4, tls, nickname, + username=None, realname=None): + username = username or nickname + realname = realname or nickname + self.execute( + """INSERT INTO servers (hostname, port, password, ipv4, + tls, nickname, username, realname) VALUES ( + ?, ?, ?, ?, ?, ?, ?, ?)""", + [hostname, port, password, ipv4, tls, nickname, username, realname]) + def get_servers(self): + return self.execute_fetchall( + """SELECT server_id, hostname, port, password, ipv4, + tls, nickname, username, realname FROM servers""") + def get_server(self, id): + return self.execute_fetchone( + """SELECT server_id, hostname, port, password, ipv4, + tls, nickname, username, realname FROM servers WHERE + server_id=?""", + [id]) def set_bot_setting(self, setting, value): self.execute( @@ -222,22 +252,40 @@ class Database(object): server_id=? AND nickname=? AND setting=?""", [server_id, nickname.lower(), setting.lower()]) - def add_server(self, hostname, port, password, ipv4, tls, nickname, - username=None, realname=None): - username = username or nickname - realname = realname or nickname + def set_user_channel_setting(self, server_id, channel, nickname, + setting, value): self.execute( - """INSERT INTO servers (hostname, port, password, ipv4, - tls, nickname, username, realname) VALUES ( - ?, ?, ?, ?, ?, ?, ?, ?)""", - [hostname, port, password, ipv4, tls, nickname, username, realname]) - def get_servers(self): - return self.execute_fetchall( - """SELECT server_id, hostname, port, password, ipv4, - tls, nickname, username, realname FROM servers""") - def get_server(self, id): - return self.execute_fetchone( - """SELECT server_id, hostname, port, password, ipv4, - tls, nickname, username, realname FROM servers WHERE - server_id=?""", - [id]) + """INSERT OR REPLACE INTO user_channel_settings VALUES + (?, ?, ?, ?, ?)""", + [server_id, channel.lower(), nickname.lower(), setting.lower(), + json.dumps(value)]) + def get_user_channel_setting(self, server_id, channel, nickname, + setting, default=None): + value = self.execute_fetchone( + """SELECT value FROM user_channel_settings WHERE + server_id=? AND channel=? AND nickname=? and setting=?""", + [server_id, channel.lower(), nickname.lower(), setting.lower()]) + if value: + return json.loads(value[0]) + return default + def find_user_channel_settings(self, server_id, channel, nickname, + pattern, default=[]): + values = self.cursor().execute( + """SELECT setting, value FROM user_channel_settings WHERE + server_id=? AND channel=? AND nickname=? AND setting LIKE '?'""", + [server_id, channel.lower(), nickname.lower(), pattern.lower()]) + if values: + for i, value in enumerate(values): + values[i] = value[0], json.loads(value[1]) + return values + return default + def find_user_channel_settings_prefix(self, server_id, channel, nickname, + prefix, default=[]): + return self.find_user_settings(server_id, nickname, "%s%" % prefix, + default) + def del_user_channel_setting(self, server_id, channel, nickname, setting): + self.execute( + """DELETE FROM user_channel_settings WHERE + server_id=? AND channel=? AND nickname=? AND setting=?""", + [server_id, channel.lower(), nickname.lower(), setting.lower()]) + diff --git a/IRCChannel.py b/IRCChannel.py index 07208725..6e785f10 100644 --- a/IRCChannel.py +++ b/IRCChannel.py @@ -82,6 +82,22 @@ class Channel(object): self.bot.database.del_channel_setting(self.server.id, self.name, setting) + def set_user_setting(self, nickname, setting, value): + self.bot.database.set_user_channel_setting(self.server.id, + self.name, nickname, setting, value) + def get_user_setting(self, nickname, setting, default=None): + return self.bot.database.get_user_channel_setting( + self.server.id, self.name, nickname, setting, default) + def find_user_settings(self, nickname, pattern, default=[]): + return self.bot.database.find_user_channel_settings( + self.server.id, self.name, nickname, pattern, default) + def find_user_settings_prefix(self, nickname, prefix, default=[]): + return self.bot.database.find_user_channel_settings_prefix( + self.server.id, self.name, nickname, prefix, default) + def del_user_setting(self, nickname, setting): + self.bot.database.del_user_channel_setting(self.server.id, + self.name, nickname, setting) + def send_message(self, text, prefix=None): self.server.send_message(self.name, text, prefix=prefix) def send_mode(self, mode=None, target=None): -- cgit v1.3.1-10-gc9f91