def create_attachment_table(dbcnx: sqlite3.Connection):
try:
c = dbcnx.cursor()
c.execute("DROP TABLE IF EXISTS Attachment")
sql = '''CREATE TABLE IF NOT EXISTS Attachment (
attachmentid INTEGER PRIMARY KEY AUTOINCREMENT,
messageid INTEGER NOT NULL,
filepath TEXT NOT NULL,
FOREIGN KEY (messageid) REFERENCES Message(messageid)
)'''
c.execute(sql)
dbcnx.commit()
print("Success: Attachment table initialised.")
except sqlite3.Error as e:
dbcnx.rollback()
print("ERROR: Unable to create Attachment table. Details:", e)
raise e
def update_transactions(transactiondb: sqlite3.Connection, documentid: str,
sql: str) -> int:
# SQL Server only wants to see single quotes around literals.
# SQL Lite wants to see doubled singled quote around literals when we're searching for it, as the SQL has
# single quote in it already.
# Therefore, I need to ensure that any single quotes in the SQL are doubled up in order to not throw exceptions
# within SQL server...
sql = sql.replace("'", "''")
try:
transactioncursor = transactiondb.cursor()
if TransactionsTestModeEnabled:
logging.info("SQLite Testing enabled - we will commit nothing.")
else:
transactioncursor.execute(
"update transactions set is_processed = 1 where docid='{0}' AND update_sql='{1}'"
.format(documentid, sql))
transactiondb.commit()
except Exception as e:
logging.error(
"Transaction database update failed with {0}, sql = {1}".format(
e, sql))
transactiondb.rollback()
return -1
transactioncursor.close()
return 0
def create_user_table(dbcnx: sqlite3.Connection):
try:
# GET DATABASE CURSOR OBJECT
c = dbcnx.cursor()
# REMOVE EXISTING USER TABLE
c.execute("DROP TABLE IF EXISTS User")
# CREATE USER TABLE
sql = '''CREATE TABLE IF NOT EXISTS User (
userid INTEGER PRIMARY KEY AUTOINCREMENT,
username TEXT UNIQUE NOT NULL,
password TEXT NOT NULL,
last_login_ts NUMERIC,
admin INTEGER DEFAULT 0,
active INTEGER DEFAULT 1)
'''
c.execute(sql)
# Add 'deleteduser' entry to assign messages to when a user is deleted later
c.execute("INSERT INTO User VALUES (0,'DeletedUser','',0,0,0)")
dbcnx.commit()
print("Success: User table initialised.")
except sqlite3.Error as e:
dbcnx.rollback()
print("ERROR: Unable to create User table. Details:", e)
raise e
def selectByDateRange(prec_no, block_no, fromDate, toDate):
__mylogger.info('selectByDateRange start!')
try:
#dbのオープン
db = Connection(dbFilePath)
#データ取得
statement = "SELECT * FROM WeatherInfo WHERE"
statement = statement + " date BETWEEN '{fromDate}' and '{toDate}' ".format(fromDate=fromDate, toDate=toDate)
result = np.array([])
for item in db.cursor().execute(statement):
if len(result) > 0:
result = np.vstack([result, item])
else:
result = np.array(item)
#result.append(item)
return result
except Exception as ex:
__mylogger.error('error has occured.')
__mylogger.error(ex)
if db != None:
db.rollback()
finally:
if db != None:
db.close()
__mylogger.info('selectByDateRange ended')
def add(name, descrption, db: sqlite3.Connection):
try:
c = db.cursor()
Chatroom.__check_name_is_unique(name, db)
while True:
try:
new_join_code = Chatroom.__get_new_joincode()
Chatroom.__check_join_code_is_unique(new_join_code, db)
break
except ChatroomActionError:
print("WARNING: new_join_code in use. Generating another.")
c.execute(
"INSERT INTO Chatroom (name, description, joincode) VALUES (?, ?, ?)",
[name, descrption,
Chatroom.__get_new_joincode()])
db.commit()
return Chatroom(c.lastrowid, db)
except sqlite3.Error as e:
db.rollback()
print(
f"ERROR: Unable to add chatroom with name {name}.Details:\n{e}"
)
def add(username, password, db: sqlite3.Connection):
# TODO: Check for unique username
try:
c = db.cursor()
# Check username is unique
existing_username = c.execute(
"SELECT userid FROM User WHERE username=?",
[username]).fetchone()
if existing_username:
raise UserActionError(
f"User already exists with username '{username}'.")
# Insert new user
c.execute(
"INSERT INTO User (username, password, last_login_ts) VALUES (?, ?, ?)",
(username, password, 0))
new_user_id = c.lastrowid
db.commit()
return User(new_user_id, db)
except sqlite3.Error as e:
db.rollback()
print(f"ERROR: Exception raised when adding user {username}.\n"
f"Database rolled back to last commit. Details:\n{e}")
def atomic_transaction(conn: sqlite3.Connection, sql: str,
*args: Any) -> sqlite3.Cursor:
"""Perform an **atomic** transaction.
The transaction is committed if there are no exceptions else the
transaction is rolled back.
Args:
conn: database connection
sql: formatted string
*args: arguments to use for parameter substitution
Returns:
sqlite cursor
"""
try:
c = transaction(conn, sql, *args)
except Exception as e:
logging.exception("Could not execute transaction, rolling back")
conn.rollback()
raise e
conn.commit()
return c
def create_message_table(dbcnx: sqlite3.Connection):
try:
c = dbcnx.cursor()
c.execute("DROP TABLE IF EXISTS Message")
sql = '''CREATE TABLE IF NOT EXISTS Message (
messageid INTEGER PRIMARY KEY AUTOINCREMENT,
content TEXT NOT NULL,
chatroomid INTEGER NOT NULL,
senderid INTEGER NOT NULL,
timestamp NUMERIC,
FOREIGN KEY (chatroomid) REFERENCES Chatroom(chatroomid),
FOREIGN KEY (senderid) REFERENCES User(userid)
)'''
c.execute(sql)
dbcnx.commit()
print("Success: Message table initialised.")
except sqlite3.Error as e:
dbcnx.rollback()
print("ERROR: Unable to create Message table. Details:", e)
raise e
def insertIntoMoveTable(self, conn: sqlite3.Connection, moves: list):
cursor = conn.cursor()
insertQuery = self._buildInsertQuery()
try:
cursor.executemany(insertQuery, moves)
except sqlite3.Error as sqlErr:
conn.rollback()
conn.commit()
def importCsv(csvName):
__mylogger.info('importing start!')
try:
#csvファイル読み込み
df = pd.read_csv(csvName, encoding='utf-8', parse_dates=True, header=0)
#INTEGERの精度変更 objectにする
#なぜかint64のままだとsqliteにBLOBとして登録される。int8やint32に変更すると文字化けしてしまう。
#objectにすると自動判定でintと判断されるようである。
df['県番号'] = df['県番号'].astype('object')
df['地区番号'] = df['地区番号'].astype('object')
df['時刻'] = df['時刻'].astype('object')
df['湿度(%)'] = df['湿度(%)'].astype('object')
'''
#欠損値の補完
def __conv00(val):
if val == '--' or val == None:
return 0.0
else:
return val
df['降水量'] = df['降水量'].apply(__conv00)
df['日照時間(h)'] = df['日照時間(h)'].fillna(0.0)
df['全天日射量(MJ/m2)'] = df['全天日射量(MJ/m2)'].fillna(0.0)
df['降雪(cm)'] = df['降雪(cm)'].fillna(0.0)
df['降雪(cm)'] = df['降雪(cm)'].apply(__conv00)
df['積雪(cm)'] = df['積雪(cm)'].fillna(0.0)
df['積雪(cm)'] = df['積雪(cm)'].apply(__conv00)
df['天気'] = df['天気'].fillna('')
df['雨雲'] = df['雨雲'].fillna('')
df['視程(km)'] = df['視程(km)'].fillna(0.0)
'''
#dbのオープン
db = Connection(dbFilePath)
#データ一括登録
for idx in range(0, len(df)):
__mylogger.debug("\n" + str(df.iloc[idx]))
statement = "INSERT INTO WeatherInfo VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "
try:
db.cursor().execute(statement, df.iloc[idx])
except DatabaseError as insertEx:
__mylogger.error('error has occured.')
__mylogger.error(insertEx)
db.commit()
except Exception as ex:
__mylogger.error('error has occured.')
__mylogger.error(ex)
if db != None:
db.rollback()
finally:
if db != None:
db.close()
__mylogger.info('importing ended')
def txn(conn: Connection) -> Iterator[Cursor]:
cur = conn.cursor()
try:
yield cur
except Exception:
conn.rollback()
raise
else:
conn.commit()
def load_max_id(db: sqlite3.Connection):
cur = db.cursor()
cur.execute('select min(id) from fav_tweets')
r = cur.fetchone()
db.rollback()
cur.close()
if r:
return r[0]
pass
def sqlite_transaction(conn: Connection) -> Generator:
# We must issue a "BEGIN" explicitly when running in auto-commit mode.
conn.execute('BEGIN')
try:
# Yield control back to the caller.
yield
except Exception:
conn.rollback() # Roll back all changes if an exception occurs.
raise
else:
conn.commit()
def transaction(conn: sqlite3.Connection):
# We must issue a "BEGIN" explicitly when running in auto-commit mode.
conn.execute("BEGIN")
try:
# Yield control back to the caller.
yield conn
except:
conn.rollback() # Roll back all changes if an exception occurs.
raise
else:
conn.commit()
def safe_execute(conn: sqlite3.Connection, statement: str) -> bool:
# conn.execute(statement)
# conn.commit()
try:
conn.execute(statement)
conn.commit()
return True
except:
# print("Wrong Execute.")
conn.rollback()
return False
def sqlite_session(
engine: sqlite3.Connection, ) -> ContextManager[sqlite3.Cursor]:
session = engine.cursor()
try:
yield session
session.close()
except Exception as e:
session.close()
engine.rollback()
raise e
finally:
session.close()
engine.commit()
def _add_underlying_tokens(
connection: sqlite3.Connection,
parent_token_address: ChecksumEthAddress,
underlying_tokens: List[UnderlyingToken],
) -> None:
"""Add the underlying tokens for the parent token
Passing in the connection so it can be rolled back in case of error
"""
cursor = GlobalDBHandler()._conn.cursor()
for underlying_token in underlying_tokens:
# make sure underlying token address is tracked if not already there
asset_id = GlobalDBHandler.get_ethereum_token_identifier(
underlying_token.address) # noqa: E501
if asset_id is None:
try: # underlying token does not exist. Track it
cursor.execute(
'INSERT INTO ethereum_tokens(address) VALUES(?)',
(underlying_token.address, ),
)
asset_id = ethaddress_to_identifier(
underlying_token.address)
cursor.execute(
"""INSERT INTO assets(identifier, type, name, symbol,
started, swapped_for, coingecko, cryptocompare, details_reference)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)""",
(asset_id, 'C', None, None, None, None, None, None,
underlying_token.address),
)
except sqlite3.IntegrityError as e:
connection.rollback()
raise InputError(
f'Failed to add underlying tokens for {parent_token_address} '
f'due to {str(e)}', ) from e
try:
cursor.execute(
'INSERT INTO underlying_tokens_list(address, weight, parent_token_entry) '
'VALUES(?, ?, ?)',
(
underlying_token.address,
str(underlying_token.weight),
parent_token_address,
),
)
except sqlite3.IntegrityError as e:
connection.rollback()
raise InputError(
f'Failed to add underlying tokens for {parent_token_address} due to {str(e)}',
) from e
def migrate(connection: sqlite3.Connection, thread: gui.threads.WorkerThread):
connection.executescript(f"""
BEGIN;
CREATE TABLE version (
db_version INTEGER PRIMARY KEY,
app_version TEXT
);
ALTER TABLE images ADD COLUMN hash BLOB; -- Cannot use INTEGER as hashes are 64-bit *unsigned* integers
CREATE INDEX idx_images_hash ON images (hash); -- Speed up hash querying
ALTER TABLE tags ADD COLUMN definition TEXT;
INSERT INTO version (db_version, app_version) VALUES (1, "{constants.VERSION}");
""")
cursor = connection.execute('SELECT id, path FROM images')
rows = cursor.fetchall()
total_rows = len(rows)
for i, (ident, path) in enumerate(rows):
if thread.cancelled:
cursor.close()
connection.rollback()
break
thread.progress_signal.emit(
i / total_rows,
_t(f'popup.database_update.migration_0000.hashing_image_text',
image=path,
index=i + 1,
total=total_rows), thread.STATUS_UNKNOWN)
image_hash = utils.image.get_hash(path)
try:
connection.execute('UPDATE images SET hash = ? WHERE id = ?',
(data_access.ImageDao.encode_hash(image_hash)
if image_hash is not None else None, ident))
except sqlite3.Error as e:
cursor.close()
thread.error = str(e)
thread.cancel()
else:
thread.progress_signal.emit(
(i + 1) / total_rows,
_t(f'popup.database_update.migration_0000.hashing_image_text',
image=path,
index=i + 1,
total=total_rows), thread.STATUS_SUCCESS)
else:
cursor.close()
connection.commit()
def atomic(conn: sqlite3.Connection):
"""
Guard a series of transactions as atomic.
If one fails the transaction is rolled back and no more transactions
are performed.
Args:
- conn: connection to guard
"""
try:
yield
except Exception as e:
conn.rollback()
log.exception("Rolling back due to unhandled exception")
raise RuntimeError("Rolling back due to unhandled exception") from e
else:
conn.commit()
def drop_tables(connector: sql.Connection):
"""Удаление всех таблиц в базе данных"""
try:
connector.executescript("""
pragma foreign_keys=off;
begin transaction;
drop table if exists webpages;
drop table if exists tags;
drop table if exists webpagetags;
drop table if exists search_table;
drop table if exists html_contents;
commit;
pragma foreign_keys=on;
""")
except sql.Error:
logger.exception('Exception in drop tables')
connector.rollback()
close_connection(connector)
def add(messageid, filepath, db: sqlite3.Connection):
# TODO: Add ability to receive any file, copy it to the correct path and set the correct path location for this
# Attachment object
try:
c = db.cursor()
c.execute(
"INSERT INTO Attachment (messageid, filepath) VALUES (?, ?)",
[messageid, filepath])
new_attachmentid = c.lastrowid
db.commit()
return Attachment(new_attachmentid, db)
except sqlite3.Error as e:
db.rollback()
print(
f"ERROR: Exception raised when inserting a new attachment. Details:\n{e}"
)
raise e
def transaction(conn: sqlite3.Connection) -> Iterator[None]:
# We must issue a "BEGIN" explicitly when running in auto-commit mode.
conn.execute('BEGIN')
try:
# Yield control back to the caller.
yield
except Exception:
conn.rollback() # Roll back all changes if an exception occurs.
raise
else:
for n in range(10):
try:
conn.commit()
return
except sqlite3.OperationalError:
time.sleep((2**n) + (random.randint(0, 1000) / 1000))
if n == 9:
raise
def add(content, chatroomid, senderid, db: sqlite3.Connection):
try:
c = db.cursor()
ts = int(round(datetime.datetime.now().timestamp(), 0))
# (content, chatroomid, senderid, int(round(datetime.datetime.now().timestamp(), 0))))
c.execute(
"INSERT INTO Message (content, chatroomid, senderid, timestamp) VALUES (?, ?, ?, ?)",
(content, chatroomid, senderid, ts))
new_messageid = c.lastrowid
db.commit()
return Message(new_messageid, db)
except sqlite3.Error as e:
db.rollback()
print(f"ERROR: Exception raised when adding message.\n"
f"Database rolled back to last commit. Details:\n{e}")
raise e
def create_indexes(connector: sql.Connection):
"""Создает индексы в базе данных"""
try:
with connector:
connector.executescript("""
begin transaction;
CREATE UNIQUE INDEX IF NOT EXISTS html_contents_id_page_idx ON html_contents (id_page);
CREATE INDEX IF NOT EXISTS webpages_title_idx ON webpages (title COLLATE nocase);
CREATE INDEX IF NOT EXISTS webpages_time_saved_idx ON webpages (time_saved);
CREATE UNIQUE INDEX IF NOT EXISTS webpages_hash_idx ON webpages (hash);
CREATE INDEX IF NOT EXISTS tags_tag_idx ON tags (tag COLLATE nocase);
CREATE INDEX IF NOT EXISTS webpagetags_id_page_idx ON webpagetags (id_page);
CREATE INDEX IF NOT EXISTS webpagetags_id_tag_idx ON webpagetags (id_tag);
commit;
""")
except sql.Error as e:
logger.exception('Ошибка создания индексов')
connector.rollback()
raise e
def delete_indexes(connector: sql.Connection):
"""Удаляет индексы в базе данных"""
try:
with connector:
connector.executescript("""
begin transaction;
drop index if exists html_contents_id_page_idx;
drop index if exists webpages_title_idx;
drop index if exists webpages_time_saved_idx;
drop index if exists webpages_hash_idx;
drop index if exists tags_tag_idx;
drop index if exists webpagetags_id_page_idxl;
drop index if exists webpagetags_id_tag_idx;
commit;
""")
except sql.Error as e:
logger.exception('Ошибка удаления индексов')
connector.rollback()
raise e
def add_to_db(item: dict, conn: sqlite3.Connection, cursor: sqlite3.Cursor):
"""
Add photo detains to db.
"""
query = "INSERT INTO images VALUES (?,?,?,?,?,?)"
try:
cursor.execute(
query,
(
item["id"],
item.get("author"),
item.get("camera"),
item.get("tags"),
item.get("cropped_picture"),
item.get("full_picture"),
),
)
conn.commit()
except sqlite3.IntegrityError:
conn.rollback()
def execute(cursor: Cursor,
conn: Connection,
sql: str,
args: Optional[Tuple[Optional[str], ...]] = None) -> Execute:
"""Is used to execute an sql query on the database."""
try:
result: Execute
if args is not None:
result = cursor.execute(sql, args)
conn.commit()
return result
result = cursor.execute(sql)
conn.commit()
return result
except Exception as e:
if type(e) == OperationalError:
if 'duplicate' not in e.args[0]:
print(f'Error in db.execute executing command: {e}.')
if 'database is locked' in str(e):
conn.rollback()
return e
def create_chatroommember_table(dbcnx: sqlite3.Connection):
try:
c = dbcnx.cursor()
c.execute("DROP TABLE IF EXISTS ChatroomMember")
sql = '''CREATE TABLE IF NOT EXISTS ChatroomMember(
chatroomid INTEGER NOT NULL,
userid INTEGER NOT NULL,
owner INTEGER DEFAULT 0,
PRIMARY KEY (chatroomid, userid),
FOREIGN KEY (userid) references User(userid)
)'''
c.execute(sql)
dbcnx.commit()
print("Success: ChatroomMember table initialised.")
except sqlite3.Error as e:
dbcnx.rollback()
print("ERROR: Unable to create ChatroomMember table. Details:", e)
raise e
def create_chatroom_table(dbcnx: sqlite3.Connection):
try:
c = dbcnx.cursor()
# REMOVE EXISTING CHATROOM TABLE
c.execute("DROP TABLE IF EXISTS Chatroom")
# CREATE NEW CHATROOM TABLE
sql = '''CREATE TABLE IF NOT EXISTS Chatroom (
chatroomid INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT UNIQUE NOT NULL,
description TEXT NOT NULL,
joincode TEXT UNIQUE NOT NULL
)
'''
c.execute(sql)
dbcnx.commit()
print("Success: Chatroom table initialised.")
except sqlite3.Error as e:
dbcnx.rollback()
print("ERROR: Unable to create Chatroom table. Details:", e)
raise e
def save_to_db(self, database: sqlite3.Connection):
c = database.cursor()
logger.debug("%s: Checking if Comic '%s' already exists in database." %
(self.comic_site.name, self.title))
if self.get_comic_id(database) is None:
# Comic not found in DB, saving it...
logger.info(
"%s: Comic not found in database. Saving Comic '%s' ..." %
(self.comic_site.name, self.title))
c.execute(
'''
INSERT or IGNORE into comics(title, source, comic_site_id)
VALUES (?, ?, ?)
''', (self.title, self.source, self.comic_site.comic_site_id))
try:
resp = telegram.send_photo(
bot_id=getenv('BOT_ID'),
bot_token=getenv('BOT_TOKEN'),
chat_id=getenv('CHAT_ID'),
photo=self.source,
caption="*Title:* %s\n*Site:* %s" %
(escape_special_chars(self.title),
escape_special_chars(self.comic_site.name)))
if resp.status_code == 200:
logger.info(
"Successfully sent telegram notification for Comic '%s: %s'!"
% (self.comic_site.name, self.title))
c.execute(
'''
INSERT into telegram_notifications(sent_bool, comic_id)
VALUES (?, ?)
''', (1, self.get_comic_id(database)))
database.commit()
elif resp.status_code == 429:
# Too many requests, need to wait. Comic get's send out in next batch.
wait_time = resp.json()['parameters']['retry_after']
logger.info(
"Error 'Too many requests': Need to wait %s seconds..."
% wait_time)
sleep(wait_time)
database.rollback()
else:
logger.error(
"Error while sending telegram notification for Comic '%s: %s': %s"
% (self.comic_site.name, self.title, resp.content))
database.rollback()
except Exception as e:
logger.error(
"Error while sending telegram notification for Comic '%s: %s': %s"
% (self.comic_site.name, self.title, e))
database.rollback()
else:
logger.debug("%s: Comic '%s' already in database. Skipping..." %
(self.comic_site.name, self.title))
class SimpleBot(Client):
def on_connected(self):
self.init_db()
self.authorized_users = list()
self.command_prefix = "*"
for channel in self.config['channels']:
self.join(channel)
self.aliases = {
"telka": ["телка", "телочка"]
}
def on_disconnected(self):
if self.db:
self.db.close()
def init_db(self):
self.db = Connection(self.config['database'])
self.db.execute("""
CREATE TABLE IF NOT EXISTS
message_log (
id INTEGER PRIMARY KEY ASC,
channel TEXT,
nick TEXT,
ident TEXT,
host TEXT,
message TEXT,
date INTEGER
)
""")
self.db.execute("""
CREATE TABLE IF NOT EXISTS
social_telki (
id INTEGER PRIMARY KEY ASC,
rating INTEGER,
displayed_times INNTEGER,
url TEXT,
who_added TEXT,
date_added INTEGER
)
""")
def dict_factory(cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
self.db.row_factory = dict_factory
def get_unix_timestamp(self):
return int(time.mktime(datetime.datetime.now().timetuple()))
def log_message(self, nick_name, ident, host_name, message, channel):
self.db.execute("""
INSERT INTO message_log (channel, nick, ident, host, message, date)
VALUES (?,?,?,?,?,?)
""", (channel, nick_name, ident, host_name, message, self.get_unix_timestamp()))
self.db.commit()
def on_private(self, nick, ident, host, message):
print u"pm from:%s: %s" % (nick, message)
def on_channel(self, nick, ident, host, message, channel):
print u"on %s from %s: %s" % (channel, nick, message)
def on_privmsg(self, nick, ident, host, params, trailing):
channel = None
message = trailing
if params == self.config['nick']:
self.on_private(nick, ident, host, message)
else:
channel = params.decode('utf-8')
self.on_channel(nick, ident, host, message, channel)
self.log_message(nick.decode('utf-8'), ident.decode('utf-8'), host.decode('utf-8'), message.decode('utf-8'), channel)
if message.startswith(self.command_prefix):
self.handle_command(nick, ident, host, message[len(self.command_prefix):], channel)
def on_nick(self, old_nick, ident, host, params, new_nick):
if old_nick == self.nick_name:
self.print_debug("Yay! My name changed to: %s" % new_nick)
self.nick_name = new_nick
def is_authorized(self, nick, ident, host):
for login_data in self.authorized_users:
if login_data[0] == nick and login_data[1] == ident and login_data[2] == host:
return True
return False
def authorize(self, nick, ident, host, password):
for bot_oper in self.config['allowed_ops']:
if bot_oper['nick'] == nick and bot_oper['password'] == password:
self.authorized_users.append((nick, ident, host))
return True
def handle_command(self, nick, ident, host, command, channel):
args = None
if " " in command:
command, args = command.split(" ", 1)
command_name = "cmd_%s" % command.lower()
try:
meth = getattr(self, command_name, None)
if meth is None:
for cmdname, aliases in self.aliases.iteritems():
for alias in aliases:
if command == alias:
command_name = "cmd_%s" % cmdname.lower()
meth = getattr(self, command_name)
break
if meth:
meth(nick, ident, host, channel, args)
ameth = getattr(self, "a%s" % command_name, None)
if ameth is not None:
if self.is_authorized(nick, ident, host):
ameth(nick, ident, host, channel, args)
return
else:
self.say_to(nick, "Nope!")
except Exception as e:
print(e.message)
self.say_to(nick, "Some error occurred while your command being executed :[")
def cmd_auth(self, nick, ident, host, channel, args):
if channel:
self.say_to(nick, 'Noob :D', channel)
if not self.is_authorized(nick, ident, host):
if self.authorize(nick, ident, host, args):
self.say_to(nick, "Auth successed!")
else:
self.say_to(nick, "Auth failed!")
else:
self.say_to(nick, "You're already authorized!")
def acmd_die(self, nick, ident, host, channel, args):
self.say_to(nick, "Quitting....")
self._shutdown_pending = True
def acmd_join(self, nick, ident, host, channel, args):
self.join(args)
def acmd_part(self, nick, ident, host, channel, args):
self.part(args)
def cmd_lastlog(self, nick, ident, host, channel, args):
known_args = ["nick", "channel", "message", "date", "limit"]
if not args:
self.say_to(nick, "Known args are: %s" % ", ".join(known_args), channel)
return
arg_dict = dict()
if args:
arg_pairs = args.split(" ")
wrong_attrs = list()
for p in arg_pairs:
if "=" in p:
k,v = p.split("=")
if k in known_args:
arg_dict[k] = v
else:
wrong_attrs.append(k)
else:
wrong_attrs.append(p)
if wrong_attrs:
self.say_to(nick, "Wrong or unknown attributes: %s" % ",".join(wrong_attrs), channel)
if not arg_dict:
return
has_limit = 30
if "limit" in arg_dict.keys():
has_limit = arg_dict.pop("limit")
query = [
"SELECT * FROM ("
"SELECT id, channel, nick, ident, host, message, date FROM message_log"
]
if arg_dict:
query.append("WHERE")
query_conditions = list()
placeholders = list()
parsed = parse_data(arg_dict)
#print parsed
for attr, value in parsed:
query_conditions.append("%s %s"% (attr, value[0]))
placeholders.append(value[1])
query.append(" AND ".join(query_conditions))
query.append("LIMIT ?")
placeholders.append(has_limit)
query.append(") ORDER BY id ASC")
self.print_debug("querying: %s" % " ".join(query))
cur = self.db.execute(" ".join(query), placeholders)
message = ["chan\tnick\tmessage"]
for row in cur.fetchall():
datime = datetime.datetime.fromtimestamp(row['date'])
row['date'] = datime.strftime("%Y.%m.%d %H:%M:%S")
message.append("%(date)s\t%(channel)s\t%(nick)s\t%(message)s" % row)
if not message:
self.say_to(nick, "Nothing found :[")
return
resp = requests.post("https://clbin.com", dict(clbin="\n".join(message)))
if resp.status_code == 200:
self.say_to(nick, resp.text, channel)
else:
self.say_to(nick, "Post failed. Code %d" % resp.status_code,channel)
def acmd_nick(self, nick, ident, host, channel, args):
self.sendMessage("NICK :%s" % args)
def acmd_telki_loadf(self, nick, ident, host, channel, args):
self.print_debug("Loading telki from plain file %s" % args)
duplicated_rows = 0
added_rows = 0
processed_rows = 0
incorrect_urls = 0
if os.path.exists(args):
args = open(args)
elif "," in args:
args = args.split(",")
elif args.startswith("http"):
args = [args]
for line in args:
clean_url = line.strip()
if not clean_url.startswith("http"):
incorrect_urls +=1
continue
cur = self.db.execute("SELECT COUNT(*) count FROM social_telki WHERE URL=?", (clean_url, ))
count = cur.fetchone()
if int(count['count']) <=0:
self.db.execute("""INSERT INTO social_telki (
rating,
displayed_times,
url,
who_added,
date_added
)
VALUES
(?,?,?,?,?)""", (0, 0, clean_url, nick, self.get_unix_timestamp()))
added_rows +=1
else:
duplicated_rows += 1
processed_rows+=1
self.db.commit()
self.say_to(nick, "File loaded; total rows: %d; added: %s; doubles ignored: %s; incorrect urls: %d" % (processed_rows, added_rows, duplicated_rows, incorrect_urls), channel)
def cmd_telka(self, nick, ident, host, channel, args):
cur = self.db.execute("""
SELECT id, url FROM social_telki
WHERE displayed_times<= (SELECT MIN(displayed_times) FROM social_telki)
ORDER BY RANDOM()
LIMIT 1
""")
row = cur.fetchone()
if row:
cur.execute("UPDATE social_telki SET displayed_times=displayed_times+1 WHERE id=?", (row['id'], ))
url = row['url']
resp = requests.get(url)
if resp.status_code == 200:
self.say_to(args or nick, row['url'], channel)
self.db.commit()
else:
self.say_to(nick, "Еще разок попробуй, ссыль битая :(", channel)
self.db.rollback()
return
self.say_to(nick, "Nothing found O_o", channel)
def _perform_update(
self,
connection: sqlite3.Connection,
conflicts: Optional[Dict[Asset, Literal['remote', 'local']]],
local_schema_version: int,
infojson: Dict[str, Any],
up_to_version: Optional[int],
) -> None:
version = self.local_assets_version + 1
target_version = min(
up_to_version, self.last_remote_checked_version
) if up_to_version else self.last_remote_checked_version # type: ignore # noqa: E501
# type ignore since due to check_for_updates we know last_remote_checked_version exists
cursor = connection.cursor()
while version <= target_version:
try:
min_schema_version = infojson['updates'][str(
version)]['min_schema_version']
max_schema_version = infojson['updates'][str(
version)]['max_schema_version']
if local_schema_version < min_schema_version:
self.msg_aggregator.add_warning(
f'Skipping assets update {version} since it requires a min schema of '
f'{min_schema_version}. Please upgrade rotki to get this assets update',
)
break # get out of the loop
if local_schema_version > max_schema_version:
self.msg_aggregator.add_warning(
f'Skipping assets update {version} since it requires a min '
f'schema of {min_schema_version} and max schema of {max_schema_version} '
f'while the local DB schema version is {local_schema_version}. '
f'You will have to follow an alternative method to '
f'obtain the assets of this update. Easiest would be to reset global DB.',
)
cursor.execute(
'INSERT OR REPLACE INTO settings(name, value) VALUES(?, ?)',
(ASSETS_VERSION_KEY, str(version)),
)
version += 1
continue
except KeyError as e:
log.error(
f'Remote info.json for version {version} did not contain '
f'key "{str(e)}". Skipping update.', )
version += 1
continue
try:
url = f'https://raw.githubusercontent.com/rotki/assets/{self.branch}/updates/{version}/updates.sql' # noqa: E501
response = requests.get(url=url, timeout=DEFAULT_TIMEOUT_TUPLE)
except requests.exceptions.RequestException as e:
connection.rollback()
raise RemoteError(
f'Failed to query Github for {url} during assets update: {str(e)}'
) from e # noqa: E501
if response.status_code != 200:
connection.rollback()
raise RemoteError(
f'Github query for {url} failed with status code '
f'{response.status_code} and text: {response.text}', )
self._apply_single_version_update(
cursor=cursor,
version=version,
text=response.text,
conflicts=conflicts,
)
version += 1
if self.conflicts == []:
connection.commit()
return
# In this case we have conflicts. Everything should also be rolled back
connection.rollback()
