class UpdateServer:
def __init__(self, tls_context, db_operator: DBOperator):
self._db_operator = db_operator
self._rm = ResourceManager()
self._host = "0.0.0.0"
self._port = 5000 # initiate port no above 1024
self._secure_port = 5000
self._stop = False
self._socket = None
self._secure_socket = None
self._tls_context = tls_context
self._logger = LoggingServer.getInstance("overseer")
self._latest_states = {}
self._strategies = {
ServerState.ACCEPT: self._accept_connection,
ServerState.DISPATCH: self._dispatch_connection
}
self._state = ServerState.ACCEPT
self._connection_to_dispatch = None
self._heartbeat_message_interval = 10
self._heartbeat_interval_counters = {}
def launch(self):
self._stop = False
self._secure_socket = socket.socket()
self._secure_socket.bind((self._host, self._secure_port))
self._secure_socket.listen()
self._logger.info("UpdateServer: secure listening on %s" % str(
(self._host, self._port)))
connection_dispatcher = Thread(target=self._act)
connection_dispatcher.setDaemon(True)
connection_dispatcher.start()
def stop(self):
self._stop = True
def _act(self):
try:
while not self._stop:
self._strategies[self._state]()
except Exception as e:
self._logger.warn("UpdateServer is going down: " + str(e) +
repr(e) + str(type(e)))
self._stop = True
finally:
self._secure_socket.close()
def _accept_connection(self):
try:
conn, address = self._secure_socket.accept(
) # accept new connection
self._logger.info("Connection from: " + str(address))
connstream = self._tls_context.wrap_socket(conn, server_side=True)
except (ssl.SSLError, ConnectionError, socket.error) as e:
self._logger.warn("UpdateServer: " + str(e))
return
self._connection_to_dispatch = (connstream, address)
self._state = ServerState.DISPATCH
def _dispatch_connection(self):
communicator = Thread(target=self._communicate,
args=self._connection_to_dispatch)
communicator.setDaemon(True)
communicator.start()
self._connection_to_dispatch = None
self._state = ServerState.ACCEPT
def _authenticate_slave(self, nickname, password):
slave = self._db_operator.get_slave(nickname)
md5_pass = md5(password.encode()).hexdigest()
if slave.slave_password == md5_pass and password != "":
return True
return False
def _communicate(self, connection: socket.socket, address):
try:
slave_nickname, slave_password = connection.recv(
1024).decode().split("\r\n")
if not self._authenticate_slave(slave_nickname, slave_password):
raise ValueError("Wrong username/password!")
except ValueError as e:
self._logger.warn("Authentication failed, %s" % str(e))
connection.send(("Authentication failed: " + e.args[0]).encode())
return
connection.send(slave_nickname.encode())
self._logger.debug("Successful handshake with %s" %
str(slave_nickname))
self._heartbeat_interval_counters[slave_nickname] = 0
for data in self._update_generator(connection, slave_nickname):
self._latest_states[slave_nickname] = SlaveState(
slave_nickname, data)
self._log_heartbeat(slave_nickname, address, len(data))
connection.close()
def _update_generator(self, connection, slave_nickname):
data = connection.recv(1024).decode()
while data != "" and not self._stop:
yield data
data = connection.recv(1024).decode()
if data == "":
self._logger.debug("Emtpy data from %s, closing" %
str(slave_nickname))
def _log_heartbeat(self, slave_nickname, address, length):
if self._heartbeat_interval_counters[slave_nickname] %\
self._heartbeat_message_interval == 0:
self._logger.debug("State update from %s (%s) of length %d" %
(slave_nickname, address[0], length))
self._heartbeat_interval_counters[slave_nickname] = 0
self._heartbeat_interval_counters[slave_nickname] += 1
def get_latest_state(self, slave_nickname):
try:
return self._latest_states[slave_nickname]
except KeyError:
return SlaveState(
slave_nickname,
self._rm.get_string("slave_not_connected") % slave_nickname)
class DBOperatorTest(unittest.TestCase):
def setUp(self):
self._conn = psycopg2.connect(
"dbname=%s user=%s password=%s" %
("overseer_test", "inlatexbot", "inlatexbot"))
self._sut = DBOperator("overseer_test",
"inlatexbot",
"inlatexbot",
drop_key="r4jYi1@")
self._rm = ResourceManager()
def testDbIndependentConnections(self):
db_operator = DBOperator("overseer_test", "inlatexbot", "inlatexbot")
# db_operator = self._sut
try:
db_operator._c.execute("INVALID_TRANSACTION")
except ProgrammingError:
pass
user = UserMock()
self._sut.add_user(user)
users = self._sut.get_users()
self.assertIn((user.id, user.full_name, user.name), users)
def testAddUser(self):
user = UserMock()
self._sut.add_user(user)
users = self._sut.get_users()
self.assertIn((user.id, user.full_name, user.name), users)
user1 = UserMock(135)
# testing update
with self._conn:
c = self._conn.cursor()
c.execute("INSERT INTO users (telegram_id) VALUES (%s)",
[user1.id])
self._sut.add_user(user1)
users = self._sut.get_users()
self.assertIn((user1.id, user1.full_name, user1.name), users)
def testAddUserTwoTimes(self):
user = UserMock()
self._sut.add_user(user)
self._sut.add_user(user)
users = self._sut.get_users()
self.assertEqual(len(users), 1)
self.assertIn((user.id, user.full_name, user.name), users)
def testAddSlave(self):
slave1 = SlaveMock()
slave2 = SlaveMock("slave2", password="******", owner=45678)
self._sut.add_slave(slave1)
self._sut.add_slave(slave2)
slaves = self._sut.get_slaves()
slave1.password = md5(slave1.password.encode()).hexdigest()
slave2.password = md5(slave2.password.encode()).hexdigest()
self.assertListEqual([slave1.to_tuple(), slave2.to_tuple()], slaves)
slave3 = SlaveMock("a" * 60) # long name
try:
self._sut.add_slave(slave3)
except ValueError as e:
self.assertEqual(e.args[0],
self._rm.get_string("slave_name_too_long"))
else:
assert False
def testAddSlaveTwoTimes(self):
slave = SlaveMock()
self._sut.add_slave(slave)
try:
self._sut.add_slave(slave)
except ValueError as e:
self.assertEqual(e.args[0],
"Slave already exists") # TODO:replace with rm
else:
assert False
slaves = self._sut.get_slaves()
slave.password = md5(slave.password.encode()).hexdigest()
self.assertListEqual([slave.to_tuple()], slaves)
def testUpdateSlave(self):
slave = SlaveMock()
self._sut.add_slave(slave)
slave.password = "******"
self._sut.update_slave(slave)
slaves = self._sut.get_slaves()
slave.password = md5(slave.password.encode()).hexdigest()
self.assertListEqual([slave.to_tuple()], slaves)
def testGetSlave(self):
slave = SlaveMock()
self._sut.add_slave(slave)
slave_from_db = self._sut.get_slave(slave.nickname)
slave.password = md5(slave.password.encode()).hexdigest()
self.assertEqual(slave.to_tuple(), slave_from_db)
try:
self._sut.get_slave("missing_slave")
except ValueError:
return
else:
assert False
def testSubscribe(self):
telegram_id = 123459
self._sut.add_user(UserMock(telegram_id=telegram_id))
self._sut.add_slave(SlaveMock())
self._sut.add_slave(SlaveMock("slave2"))
self._sut.subscribe(telegram_id, "slave1", 11)
self._sut.subscribe(telegram_id, "slave2", 12)
subs = self._sut.get_subscriptions(telegram_id)
self.assertListEqual(subs, [("slave1", 11), ("slave2", 12)])
def testIncorrectSubscribe(self):
telegram_id = 123459
user = UserMock(telegram_id=telegram_id)
try:
self._sut.subscribe(telegram_id, "slave1", 11)
except ValueError as e:
self.assertEqual(e.args[0], "User %d not found" % telegram_id)
self._sut.add_user(user)
try:
self._sut.subscribe(telegram_id, "slave1", 11)
except ValueError as e:
self.assertEqual(e.args[0], "Slave %s not found" % "slave1")
def testDoubleSubscribe(self):
# double subscribe
telegram_id = 123459
self._sut.add_user(UserMock(telegram_id=telegram_id))
self._sut.add_slave(SlaveMock())
self._sut.subscribe(telegram_id, "slave1", 11)
subs = self._sut.get_subscriptions(telegram_id)
self.assertListEqual(subs, [("slave1", 11)])
self._sut.subscribe(telegram_id, "slave1", 12)
subs = self._sut.get_subscriptions(telegram_id)
self.assertListEqual(subs, [("slave1", 12)])
def testUnsubscribe(self):
telegram_id = 123459
user = UserMock(telegram_id=telegram_id)
self._sut.add_user(user)
self._sut.add_slave(SlaveMock())
self._sut.add_slave(SlaveMock("slave2"))
self._sut.subscribe(telegram_id, "slave1", 11)
self._sut.subscribe(telegram_id, "slave2", 12)
subs = self._sut.get_subscriptions(telegram_id)
self.assertListEqual(subs, [("slave1", 11), ("slave2", 12)])
self._sut.unsubscribe(telegram_id, "slave1")
self._sut.unsubscribe(telegram_id, "slave2")
subs = self._sut.get_subscriptions(telegram_id)
self.assertListEqual(subs, [])
with self.assertRaises(ValueError):
self._sut.unsubscribe(telegram_id, "slave2123123") # no such slave
with self.assertRaises(ValueError):
self._sut.unsubscribe(11, "slave2") # no such user
def testGetSubscribers(self):
user1 = UserMock()
user2 = UserMock(123460, full_name="Joe Marti", nickname="@pidr")
slave1 = SlaveMock()
slave2 = SlaveMock("slave2")
self._sut.add_user(user1)
self._sut.add_user(user2)
self._sut.add_slave(slave1)
self._sut.add_slave(slave2)
def info_message_ids():
for i in range(4):
yield i + 1
gen = info_message_ids()
for user in self._sut.get_users():
for slave in self._sut.get_slaves():
uid, snick, m = user.telegram_id, slave.slave_nickname, next(
gen)
self._sut.subscribe(uid, snick, m)
self.assertListEqual(self._sut.get_subscriptions(user1.id),
[(slave1.nickname, 1), (slave2.nickname, 2)])
self.assertListEqual(self._sut.get_subscriptions(user2.id),
[(slave1.nickname, 3), (slave2.nickname, 4)])
def testAddMessage(self):
telegram_id = 123456
message = MessageMock(telegram_id)
self._sut.add_message(message)
messages = self._sut.get_messages(telegram_id)
self.assertEqual(len(messages), 1)
message_repr = messages[0]
self.assertEqual(message_repr[0], telegram_id)
self.assertEqual(message_repr[1], 1)
self.assertEqual(message_repr[2], "Korenkov Alex")
self.assertEqual(message_repr[3], "@lox")
self.assertEqual(message_repr[4], message.date)
self.assertEqual(message_repr[5],
md5("I am a lox".encode()).hexdigest())
class Overseer:
def __init__(self, broadcaster, db_operator: DBOperator):
"""
:type broadcaster: src.Broadcaster.Broadcaster
"""
self._broadcaster = broadcaster
self._db_operator = db_operator
self._updater = broadcaster.get_telegram_updater()
self._resource_manager = ResourceManager()
self._logger = LoggingServer.getInstance("overseer")
self._updater.dispatcher.add_handler(CommandHandler('start', self.on_start))
self._updater.dispatcher.add_handler(CommandHandler('help', self.on_help))
self._updater.dispatcher.add_handler(CommandHandler('scheme', self.on_scheme))
self._updater.dispatcher.add_handler(CommandHandler('checkout', self.on_checkout))
self._updater.dispatcher.add_handler(CommandHandler('subscribe', self.on_subscribe))
self._updater.dispatcher.add_handler(CommandHandler('unsubscribe', self.on_unsubscribe))
self._updater.dispatcher.add_handler(CommandHandler('register_slave', self.on_register_slave))
self._updater.dispatcher.add_handler(CommandHandler('abort', self.on_abort))
self._updater.dispatcher.add_handler(MessageHandler(Filters.text, callback=self.on_message))
self._updater.dispatcher.add_handler(CallbackQueryHandler(self.on_callback))
self._message_filters = [self._filter_slave_registration]
self._slave_registration_conversations = {}
self._slave_registration_data = {}
self._slave_registration_functions = {SlaveRegistrationStages.SLAVE_NAME: self._register_slave_name,
SlaveRegistrationStages.SLAVE_PASS: self._register_slave_password}
def launch(self):
self._updater.start_polling()
self._broadcaster.launch()
def stop(self):
self._broadcaster.stop()
self._updater.stop()
def stop_broadcaster(self):
self._broadcaster.stop()
@record_message
def on_start(self, bot, update):
self._log_user_action("/start", update.message.from_user)
update.message.reply_text(self._resource_manager.get_string("greeting"))
@record_message
def on_help(self, bot, update):
self._log_user_action("/help", update.message.from_user)
with open("resources/command_summary.html", "r") as f:
update.message.reply_text(f.read(), parse_mode="HTML")
@record_message
def on_scheme(self, bot, update):
reply_markup = InlineKeyboardMarkup([[InlineKeyboardButton("OK", callback_data="OK")]])
with open("scheme.PNG", 'rb') as f:
self._updater.bot.send_photo(update.message.chat_id, f, reply_markup=reply_markup)
@record_message
def on_subscribe(self, bot, update):
self._log_user_action("/subscribe", update.message.from_user)
slave_nickname = update.message.text[11:]
if slave_nickname == "":
update.message.reply_text(self._resource_manager.get_string("no_slave_nickname"))
return
user_telegram_id = update.message.chat_id
try:
self._db_operator.get_slave(slave_nickname)
except ValueError:
update.message.reply_text(self._resource_manager.get_string("no_slave"))
return
self._logger.info("Subscribing user %d to slave %s" % (user_telegram_id, slave_nickname))
self._db_operator.add_user(update.message.from_user)
# Slave = namedtuple("Slave", "nickname ip owner password")
# self._db_operator.add_slave(Slave(slave_nickname, None, user_telegram_id, ))
info_message_id = update.message.reply_text(self._resource_manager
.get_string("fetching_updates") % slave_nickname).message_id
self._db_operator.subscribe(user_telegram_id, slave_nickname, info_message_id)
@record_message
def on_checkout(self, bot, update):
self._log_user_action("/checkout", update.message.from_user)
slave_nickname = update.message.text[10:]
if slave_nickname == "":
update.message.reply_text(self._resource_manager.get_string("no_slave_nickname"))
return
reply_markup = InlineKeyboardMarkup([[InlineKeyboardButton("OK", callback_data="OK")]])
update.message.reply_text(self._broadcaster.get_update_server().get_latest_state(slave_nickname),
parse_mode=ParseMode.MARKDOWN, reply_markup=reply_markup)
@record_message
def on_unsubscribe(self, bot, update):
self._log_user_action("/unsubscribe", update.message.from_user)
user_telegram_id = update.message.chat_id
slave_nickname = update.message.text[13:]
if slave_nickname == "":
update.message.reply_text(self._resource_manager.get_string("no_slave_nickname"))
return
self._logger.info("Unsubscribing user %d from slave %s" % (user_telegram_id, slave_nickname))
self._db_operator.unsubscribe(user_telegram_id, slave_nickname)
update.message.reply_text(self._resource_manager.get_string("unsubscribed") % slave_nickname)
def on_callback(self, bot, update):
if update.callback_query.data == "OK":
self._updater.bot.deleteMessage(update.callback_query.message.chat_id,
update.callback_query.message.message_id)
@record_message
def on_register_slave(self, bot, update):
self._log_user_action("/register_slave", update.message.from_user)
self._slave_registration_conversations[update.message.from_user.id] = SlaveRegistrationStages.SLAVE_NAME
update.message.reply_text(self._resource_manager.get_string("slave_registration_started"))
def on_abort(self, bot, update):
user_id = update.message.from_user.id
if user_id in self._slave_registration_conversations.keys():
self._slave_registration_conversations.pop(user_id)
try:
self._slave_registration_data.pop(user_id)
except KeyError:
pass # no data were here yet
update.message.reply_text(self._resource_manager.get_string("conversation_aborted"))
else:
update.message.reply_text(self._resource_manager.get_string("nothing_to_abort"))
def on_message(self, bot, update):
self._log_user_action("A message was received", update.message.from_user)
for message_filter in self._message_filters:
update = message_filter(update)
if update is None:
return # update consumed
def _filter_slave_registration(self, update):
conversations = self._slave_registration_conversations
user_id = update.message.from_user.id
if user_id in conversations.keys():
conversations[user_id] = self._slave_registration_functions[conversations[user_id]](update)
if conversations[user_id] is None:
self._log_user_action("New slave %s registered" % self._slave_registration_data[user_id],
update.message.from_user)
del self._slave_registration_data[user_id]
del self._slave_registration_conversations[user_id]
update.message.reply_text(self._resource_manager.get_string("slave_registered"))
else:
return update
def _register_slave_name(self, update):
slave_name = update.message.text
self._slave_registration_data[update.message.from_user.id] = slave_name
self._log_user_action("Slave name %s received for registration" % slave_name, update.message.from_user)
try:
Slave = namedtuple("Slave", "nickname ip owner password")
self._db_operator.add_slave(Slave(slave_name, "0.0.0.0", update.message.from_user.id, ""))
update.message.reply_text(self._resource_manager.get_string("slave_registration_password"))
except ValueError as e:
self._logger.debug("Slave name invalid: %s" % e.args[0])
update.message.reply_text(e.args[0])
return SlaveRegistrationStages.SLAVE_NAME
else:
return SlaveRegistrationStages.SLAVE_PASS
def _register_slave_password(self, update):
user_id = update.message.from_user.id
slave_pass = update.message.text
slave_name = self._slave_registration_data[user_id]
self._log_user_action("Slave password received for registration", update.message.from_user)
try:
Slave = namedtuple("Slave", "nickname ip owner password")
self._db_operator.update_slave(Slave(slave_name, "0.0.0.0",
user_id, slave_pass))
except ValueError as e:
update.message.reply_text(e.args[0])
return SlaveRegistrationStages.SLAVE_PASS
def _log_user_action(self, msg, user):
self._logger.debug("%s, user: %s, %d" % (msg, user.full_name, user.id))
class DBOperator:
TABLES = ["users", "slaves", "subscriptions", "messages"]
def __init__(self, dbname, user, password, drop_key=""):
self._dbname = dbname
self._user = user
self._password = password
self._conn = psycopg2.connect("dbname=%s user=%s password=%s" %
(dbname, user, password))
self._c = self._conn.cursor()
if drop_key == "r4jYi1@" and dbname == "overseer_test":
for table in self.TABLES:
try:
with self._conn:
self._c.execute("DROP TABLE %s CASCADE;" % table)
except ProgrammingError as e:
print(e)
if len(self.get_tables()) == 0:
self.create_tables()
self._rm = ResourceManager()
def get_tables(self):
with self._conn:
query = """SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_schema,table_name;"""
self._c.execute(query)
return self._c.fetchall()
def create_tables(self):
with self._conn:
query = """
CREATE TABLE users (
user_id serial PRIMARY KEY,
telegram_id integer UNIQUE NOT NULL,
full_name VARCHAR(50),
nickname VARCHAR(50)
);
"""
self._c.execute(query)
query = """
CREATE TABLE slaves (
slave_id serial PRIMARY KEY,
slave_nickname VARCHAR(50) UNIQUE NOT NULL,
slave_ip inet,
slave_owner integer,
slave_password VARCHAR(60)
);
"""
self._c.execute(query)
query = """
CREATE TABLE subscriptions (
user_id integer NOT NULL,
slave_id integer NOT NULL,
sub_date timestamp without time zone,
info_message_id integer,
PRIMARY KEY (user_id, slave_id),
CONSTRAINT subscriptions_role_id_fkey FOREIGN KEY (user_id)
REFERENCES users (user_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT subscriptions_user_id_fkey FOREIGN KEY (slave_id)
REFERENCES slaves (slave_id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
);
"""
self._c.execute(query)
query = """
CREATE TABLE messages (
user_telegram_id integer NOT NULL,
message_id integer NOT NULL,
user_full_name VARCHAR(250) NOT NULL,
user_telegram_nick VARCHAR(250) NOT NULL,
date timestamp without time zone,
text VARCHAR(6000)
);
CREATE INDEX messages_idx ON messages (user_telegram_id, message_id);
"""
self._c.execute(query)
def add_user(self, user):
telegram_id = user.id
full_name = user.full_name
nickname = user.name
try:
query = "INSERT INTO users (telegram_id, full_name, nickname) VALUES (%s, %s, %s);"
self._c.execute(query, [telegram_id, full_name, nickname])
self._conn.commit()
except IntegrityError:
self._conn.rollback() # user is already known
query = "UPDATE users SET (full_name, nickname) = (%s, %s) WHERE telegram_id = %s;"
self._c.execute(query, [full_name, nickname, telegram_id])
self._conn.commit()
def add_slave(self, slave):
slave_nickname = slave.nickname
slave_ip = slave.ip
slave_password = md5(slave.password.encode()).hexdigest()
slave_owner = slave.owner
try:
query = "INSERT INTO slaves (slave_nickname, slave_ip, slave_owner, slave_password) " \
"VALUES (%s, %s, %s, %s);"
self._c.execute(
query,
[slave_nickname,
Inet(slave_ip), slave_owner, slave_password])
self._conn.commit()
except IntegrityError:
self._conn.rollback()
raise ValueError("Slave already exists")
except DataError as e:
self._conn.rollback()
if len(slave_nickname) > 50:
raise ValueError(self._rm.get_string("slave_name_too_long"))
else:
raise e
def update_slave(self, slave):
slave_nickname = slave.nickname
slave_ip = slave.ip
slave_password = md5(slave.password.encode()).hexdigest()
slave_owner = slave.owner
try:
query = "UPDATE slaves SET (slave_ip, slave_owner, slave_password) = (%s, %s, %s) " \
"WHERE slave_nickname = %s;"
self._c.execute(
query,
[Inet(slave_ip), slave_owner, slave_password, slave_nickname])
self._conn.commit()
# except IntegrityError:
# self._conn.rollback()
# raise ValueError("Slave already exists")
except DataError as e:
self._conn.rollback()
if len(slave_nickname) > 50:
raise ValueError(self._rm.get_string("slave_name_too_long"))
else:
raise e
def get_users(self):
fields = "telegram_id", "full_name", "nickname"
with self._conn:
self._c.execute("SELECT %s FROM users" % ", ".join(fields))
raw_users = self._c.fetchall()
users = []
for raw_user in raw_users:
User = namedtuple("User", fields)
users.append(User(*raw_user))
return users
def get_slaves(self):
with self._conn:
fields = "slave_nickname", "slave_ip", "slave_owner", "slave_password"
self._c.execute("SELECT %s FROM slaves" % ", ".join(fields))
raw_slaves = self._c.fetchall()
slaves = []
for raw_slave in raw_slaves:
Slave = namedtuple("Slave", fields)
slaves.append(Slave(*raw_slave))
return slaves
def get_slave(self, nickname):
with self._conn:
fields = "slave_nickname", "slave_ip", "slave_owner", "slave_password"
self._c.execute(
"SELECT %s FROM slaves WHERE slave_nickname = '%s';" %
(", ".join(fields), nickname))
try:
raw_slave = self._c.fetchall()[0]
Slave = namedtuple("Slave", fields)
return Slave(*raw_slave)
except IndexError:
raise ValueError("Slave %s not found" % nickname)
def get_subscriptions(self, telegram_id):
with self._conn:
query = """SELECT slave_nickname, info_message_id
FROM slaves
LEFT OUTER JOIN subscriptions
ON slaves.slave_id = subscriptions.slave_id
LEFT OUTER JOIN users
ON users.user_id = subscriptions.user_id
where telegram_id = %s;
"""
self._c.execute(query, [telegram_id])
return self._c.fetchall()
def subscribe(self, telegram_id, slave_nickname, info_message_id):
user_id = self._get_user_id(telegram_id)
slave_id = self._get_slave_id(slave_nickname)
sub_date = datetime.now()
try:
query = "INSERT INTO subscriptions (user_id, slave_id, sub_date, info_message_id) VALUES (%s, %s, %s, %s);"
self._c.execute(query,
[user_id, slave_id, sub_date, info_message_id])
self._conn.commit()
except IntegrityError: # user is already subscribed to slave
self._conn.rollback()
with self._conn:
query = "UPDATE subscriptions SET info_message_id = %s, sub_date = %s WHERE user_id = %s AND slave_id = %s"
self._c.execute(query,
[info_message_id, sub_date, user_id, slave_id])
def unsubscribe(self, telegram_id, slave_nickname):
user_id = self._get_user_id(telegram_id)
slave_id = self._get_slave_id(slave_nickname)
try:
query = "DELETE FROM subscriptions WHERE user_id=%s AND slave_id=%s;"
self._c.execute(query, [user_id, slave_id])
self._conn.commit()
except ProgrammingError as e:
self._conn.rollback()
raise ValueError("Delete error: %s" % str(e))
def add_message(self, message: Message):
user_telegram_id = message.from_user.id
message_id = message.message_id
user_full_name = message.from_user.full_name
user_telegram_nick = message.from_user.name
date = message.date
text = md5(message.text.encode()).hexdigest()
fields = [
user_telegram_id, message_id, user_full_name, user_telegram_nick,
date, text
]
with self._conn:
query = "INSERT INTO messages (user_telegram_id, message_id, user_full_name, " \
" user_telegram_nick, date, text) " \
"VALUES (%s, %s, %s, %s, %s, %s);"
self._c.execute(query, fields)
def get_messages(self, telegram_id):
with self._conn:
query = "SELECT * from messages WHERE user_telegram_id = %s"
self._c.execute(query, [telegram_id])
return self._c.fetchall()
def _get_user_id(self, telegram_id):
try:
self._c.execute(
"select users.user_id from users where users.telegram_id = %s",
[telegram_id])
return self._c.fetchall()[0][0]
except IndexError:
self._conn.rollback()
raise ValueError("User %d not found" % telegram_id)
def _get_slave_id(self, slave_nickname):
try:
self._c.execute(
"select slaves.slave_id from slaves where slaves.slave_nickname=%s",
[slave_nickname])
return self._c.fetchall()[0][0]
except IndexError:
self._conn.rollback()
raise ValueError("Slave %s not found" % slave_nickname)