def test_migrate(calculate, monkeypatch):
runner = CliRunner()
with runner.isolated_filesystem():
fake_mig = Path.cwd() / '0001.sql'
with fake_mig.open('w') as f:
f.write('INSERT INTO test (id) VALUES (29)')
monkeypatch.setattr(
'chitanda.database.DATABASE_PATH', Path.cwd() / 'db.sqlite3'
)
with database() as (conn, cursor):
cursor.execute('CREATE TABLE test (id INTEGER PRIMARY KEY)')
cursor.execute(
"""
CREATE TABLE versions (
source TEXT,
version INTEGER,
PRIMARY KEY (source, version)
);
"""
)
conn.commit()
calculate.return_value = [
Migration(path=fake_mig, version=9, source='hi')
]
runner.invoke(migrate)
with database() as (conn, cursor):
cursor.execute('SELECT version FROM versions WHERE source = "hi"')
assert 9 == cursor.fetchone()[0]
cursor.execute('SELECT id FROM test')
assert 29 == cursor.fetchone()[0]
async def test_unset(test_db):
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO lastfm (user, listener, lastfm)
VALUES ('azuline', 'DiscordListener', 'azulfm')
""")
conn.commit()
await unset.call(
Message(
bot=None,
listener=Mock(
is_authed=AsyncMock(return_value="azuline"),
spec=DiscordListener,
__str__=lambda *a: "DiscordListener",
),
target="#chan",
author="azul",
contents="",
private=True,
))
with database() as (conn, cursor):
cursor.execute("""
SELECT lastfm FROM lastfm
WHERE user = '******' and listener = 'DiscordListener'
""")
assert not cursor.fetchone()
def test_delete_tell(test_db):
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO tells (
id, channel, listener, message, recipient, sender
) VALUES
(1, "#chan", "DiscordListener", "hi", "azul", "newuser"),
(2, "#notchan", "DiscordListener", "hi", "azul", "newuser")
""")
conn.commit()
_delete_tell(1)
with database() as (conn, cursor):
cursor.execute('SELECT COUNT(1) FROM tells')
assert 1 == cursor.fetchone()[0]
async def call(message):
"""Find a quote by its content."""
with database() as (conn, cursor):
cursor.execute(
"""
SELECT
id,
quote,
time,
adder
FROM quotes
WHERE
channel = ?
AND listener = ?
AND quote LIKE ?
ORDER BY random()
LIMIT 3
""",
(message.target, str(message.listener), f"%{message.args[0]}%"),
)
quotes = cursor.fetchall()
if quotes:
for quote in quotes:
yield f'#{quote["id"]} by {quote["adder"]}: {quote["quote"]}'
else:
yield "No quotes found."
async def call(*, bot, listener, target, author, args, private):
"""Find a quote by its content."""
with database() as (conn, cursor):
cursor.execute(
"""
SELECT
id,
quote,
time,
adder
FROM quotes
WHERE
channel = ?
AND listener = ?
AND quote LIKE ?
ORDER BY random()
LIMIT 3
""",
(target, str(listener), f'%{args[0]}%'),
)
quotes = cursor.fetchall()
if quotes:
for quote in quotes:
yield f'#{quote["id"]} by {quote["adder"]}: {quote["quote"]}'
else:
yield 'No quotes found.'
def test_create_nonexistent_database(monkeypatch):
with CliRunner().isolated_filesystem():
monkeypatch.setattr('chitanda.database.DATABASE_PATH',
Path.cwd() / 'db.sqlite3')
create_database_if_nonexistent()
with database() as (conn, cursor):
cursor.execute('SELECT version FROM versions')
async def test_delete_quote(test_db):
with patch("chitanda.modules.quotes.delete.fetch.fetch_quotes") as f:
f.return_value = []
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO quotes (id, channel, listener, quote, adder)
VALUES
(1, '#chan', 'DiscordListener', 'hi', 'azul'),
(2, '#chan', 'DiscordListener', 'hi again', 'azul'),
(1, '#notchan', 'DiscordListener', 'bye', 'azul'),
(3, '#chan', 'IRCListener', 'bye again', 'azul')
""")
conn.commit()
async for r in delete.call(
Message(
bot=None,
listener=Mock(
is_admin=AsyncMock(return_value=True),
spec=DiscordListener,
__str__=lambda *a: "DiscordListener",
),
target="#chan",
author="azul",
contents="1 2 3",
private=False,
)):
pass
cursor.execute("SELECT COUNT(1) FROM quotes")
assert 2 == cursor.fetchone()[0]
def test_get_quote_id(test_db):
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO quotes (id, channel, listener, quote, adder)
VALUES (20, 'a', 'b', 'c', 'd'), (59, 'a', 'b', 'c', 'd')
""")
conn.commit()
assert 60 == add._get_quote_id(cursor, "b", "a")
def test_get_channels_to_rejoin(test_db):
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO irc_channels (name, server) VALUES
("#1", "1"), ("#2", "2")
""")
conn.commit()
assert _get_channels_to_rejoin() == {"1": ["#1"], "2": ["#2"]}
async def call(*, bot, listener, target, author, message, private):
"""Fetch quotes by ID or one random quote from the channel."""
with database() as (conn, cursor):
if not message:
yield _fetch_random_quote(cursor, target, listener)
else:
quote_ids = _parse_quote_ids(message)
for quote in fetch_quotes(cursor, target, listener, quote_ids):
yield quote
def test_database_contextmanager(monkeypatch):
with CliRunner().isolated_filesystem():
monkeypatch.setattr('chitanda.database.DATABASE_PATH',
Path.cwd() / 'db.sqlite3')
with database() as (conn, cursor):
cursor.execute('CREATE TABLE ham(id INTEGER PRIMARY KEY)')
cursor.execute('INSERT INTO ham (id) VALUES (1)')
conn.commit()
cursor.execute('SELECT id FROM ham')
assert cursor.fetchone()[0] == 1
async def call(message):
"""Fetch quotes by ID or one random quote from the channel."""
with database() as (conn, cursor):
if not message.contents:
yield _fetch_random_quote(cursor, message.target, message.listener)
else:
quote_ids = _parse_quote_ids(message.contents)
for quote in fetch_quotes(cursor, message.target, message.listener,
quote_ids):
yield quote
async def test_on_join(test_db):
listener = IRCListener(None, "chitanda", "irc.freenode.fake")
listener.nickname = "chitanda"
listener.on_join = AsyncMock(return_value=True)
await on_join(listener, "#channel", "chitanda")
with database() as (conn, cursor):
cursor.execute("""
SELECT 1 FROM irc_channels WHERE name = "#channel"
AND server = "irc.freenode.fake" AND active = 1
""")
assert cursor.fetchone()
def test_get_version(test_db):
with database() as (conn, cursor):
cursor.execute('DELETE FROM versions')
cursor.execute(
'INSERT INTO versions (version, source) VALUES (83, "a")')
cursor.execute(
'INSERT INTO versions (version, source) VALUES (28, "b")')
cursor.execute(
'INSERT INTO versions (version, source) VALUES (18, "b")')
conn.commit()
assert {'a': 83, 'b': 28} == _get_versions()
async def on_part(self, channel, user, reason):
if self.is_same_nick(self.nickname, user):
with database() as (conn, cursor):
cursor.execute(
"""
UPDATE irc_channels SET active = 0
WHERE name = ? AND server = ?
""",
(channel, self.hostname),
)
conn.commit()
await super(IRCListener, self).on_part(channel, user, reason)
async def test_on_join_other_user(test_db):
listener = IRCListener(None, 'chitanda', 'irc.freenode.fake')
listener.nickname = 'chitanda'
listener.on_join = coroutine(lambda *args: True)
await on_join(listener, '#channel', 'azul')
with database() as (conn, cursor):
cursor.execute(
"""
SELECT 1 FROM irc_channels WHERE name = "#channel"
AND server = "irc.freenode.fake" AND active = 1
"""
)
assert not cursor.fetchone()
async def call(bot, listener, target, author, args, private):
"""Save a message for a user the next time they are seen."""
with database() as (conn, cursor):
cursor.execute(
"""
INSERT INTO tells (
channel, listener, message, recipient, sender
) VALUES (?, ?, ?, ?, ?)
""",
(target, str(listener), args[1], args[0], author),
)
conn.commit()
logger.info(f'Added a tell for {args[0]} in {target} on {listener}')
return f'{args[0]} will be told when next seen.'
async def call(*, bot, listener, target, author, args, private, username):
"""Add a quote to the database."""
with database() as (conn, cursor):
new_quote_id = _get_quote_id(cursor, listener, target)
cursor.execute(
"""
INSERT INTO quotes (
id, channel, listener, quote, adder
) VALUES (?, ?, ?, ?, ?)
""",
(new_quote_id, target, str(listener), args[0], username),
)
conn.commit()
return f'Added quote with ID {new_quote_id}.'
def _get_lastfm_nick(username, listener):
with database() as (conn, cursor):
cursor.execute(
"""
SELECT lastfm
FROM lastfm
WHERE user = ? AND listener = ?
""",
(username, str(listener)),
)
row = cursor.fetchone()
if not row:
raise BotError("No Last.FM name set.")
return row["lastfm"]
def _get_channels_to_rejoin():
# Map server to a list of channels
channels = defaultdict(list)
with database() as (conn, cursor):
cursor.execute("""
SELECT name, server
FROM irc_channels
WHERE active = 1
""")
for row in cursor.fetchall():
channels[row['server']].append(row['name'])
return channels
def test_fetch_tells(test_db):
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO tells
(channel, listener, message, recipient, sender)
VALUES
("#chan", "DiscordListener", "hi", "azul", "newuser"),
("#notchan", "DiscordListener", "hi", "azul", "newuser"),
("#chan", "*****@*****.**", "hi", "azul",
"newuser"),
("#chan", "DiscordListener", "hi", "notazul", "newuser")
""")
conn.commit()
assert len(_fetch_tells('#chan', 'DiscordListener', 'azul')) == 1
def test_get_version_empty_table(monkeypatch):
with CliRunner().isolated_filesystem():
monkeypatch.setattr('chitanda.database.DATABASE_PATH',
Path.cwd() / 'db.sqlite3')
with database() as (conn, cursor):
cursor.execute("""
CREATE TABLE versions (
source TEXT,
version INTEGER,
PRIMARY KEY (source, version)
);
""")
conn.commit()
assert {} == _get_versions()
async def test_on_part_other_user(test_db):
listener = IRCListener(None, "chitanda", "irc.freenode.fake")
listener.nickname = "chitanda"
listener.on_part = AsyncMock(return_value=True)
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO irc_channels (name, server)
VALUES ("#channel", "irc.freenode.fake")
""")
conn.commit()
await on_part(listener, "#channel", "azul", None)
cursor.execute("""
SELECT 1 FROM irc_channels WHERE name = "#channel"
AND server = "irc.freenode.fake" AND active = 1
""")
assert cursor.fetchone()
async def call(message):
"""Set a Last.FM name for the nowplaying command."""
lastfm = message.args[0]
with database() as (conn, cursor):
cursor.execute(
"""
INSERT OR IGNORE INTO lastfm (
user, listener, lastfm
) VALUES (?, ?, ?)
""",
(message.username, str(message.listener), lastfm),
)
cursor.execute(
'UPDATE lastfm SET lastfm = ? WHERE user = ? AND listener = ?',
(lastfm, message.username, str(message.listener)),
)
conn.commit()
return f'Set Last.FM username to {lastfm}.'
async def call(*, bot, listener, target, author, args, private, username):
"""Set a Last.FM name for the nowplaying command."""
lastfm = args[0]
with database() as (conn, cursor):
cursor.execute(
"""
INSERT OR IGNORE INTO lastfm (
user, listener, lastfm
) VALUES (?, ?, ?)
""",
(username, str(listener), lastfm),
)
cursor.execute(
'UPDATE lastfm SET lastfm = ? WHERE user = ? AND listener = ?',
(lastfm, username, str(listener)),
)
conn.commit()
return f'Set Last.FM username to {lastfm}.'
async def test_lastfm(test_db, monkeypatch):
requests = Mock()
requests.get.side_effect = [
Mock(json=lambda d=d: d) for d in DEMO_RESPONSES
]
monkeypatch.setattr('chitanda.modules.lastfm.lastfm.requests', requests)
monkeypatch.setattr(
'chitanda.modules.lastfm.lastfm.config',
{
'user_agent': 'chitanda',
'lastfm': {
'api_key': 'abc'
}
},
)
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO lastfm (user, listener, lastfm)
VALUES ('azuline', 'DiscordListener', 'azulfm')
""")
conn.commit()
response = await lastfm.call(
Message(
bot=None,
listener=Mock(
is_authed=coroutine(lambda *a: 'azuline'),
spec=DiscordListener,
__str__=lambda *a: 'DiscordListener',
),
target='#chan',
author='azul',
contents='',
private=False,
))
assert response == (
'azul is now playing Forgotten Love (Claptone Remix) by Aurora '
'from Forgotten Love (Claptone Remix) '
'[tags: trance / Melodic Death Metal / dance]' # lol nice tag
)
async def test_lastfm(test_db, monkeypatch):
requests = Mock()
requests.get.side_effect = [
Mock(json=lambda d=d: d) for d in DEMO_RESPONSES
]
monkeypatch.setattr("chitanda.modules.lastfm.lastfm.requests", requests)
monkeypatch.setattr(
"chitanda.modules.lastfm.lastfm.config",
{
"user_agent": "chitanda",
"lastfm": {
"api_key": "abc"
}
},
)
with database() as (conn, cursor):
cursor.execute("""
INSERT INTO lastfm (user, listener, lastfm)
VALUES ('azuline', 'DiscordListener', 'azulfm')
""")
conn.commit()
response = await lastfm.call(
Message(
bot=None,
listener=Mock(
is_authed=AsyncMock(return_value="azuline"),
spec=DiscordListener,
__str__=lambda *a: "DiscordListener",
),
target="#chan",
author="123",
contents="",
private=False,
))
assert response == (
"<@123> is now playing Forgotten Love (Claptone Remix) by Aurora "
"from Forgotten Love (Claptone Remix) "
"[tags: trance / Melodic Death Metal / dance]" # lol nice tag
)
def _fetch_tells(target, listener, author):
with database() as (conn, cursor):
cursor.execute(
"""
SELECT
id,
message,
time,
sender
FROM tells
WHERE
channel = ?
AND listener = ?
AND recipient = ?
ORDER BY id ASC
""",
(target, str(listener), author),
)
return cursor.fetchall()
def migrate():
"""Upgrade the database to the latest migration."""
migrations_needed = calculate_migrations_needed()
if not migrations_needed:
click.echo("Database is up to date.")
sys.exit(1)
logger.info("Pending database migrations found.")
with database() as (conn, cursor):
for mig in migrations_needed:
logger.info(f"Executing migration at {mig.path} .")
with mig.path.open() as sql:
cursor.executescript(sql.read())
cursor.execute(
"INSERT INTO versions (source, version) VALUES (?, ?)",
(mig.source, mig.version),
)
conn.commit()
async def on_join(self, channel, user):
if self.is_same_nick(self.nickname, user):
with database() as (conn, cursor):
cursor.execute(
"""
INSERT OR IGNORE INTO irc_channels (name, server)
VALUES (?, ?)
""",
(channel, self.hostname),
)
cursor.execute(
"""
UPDATE irc_channels SET active = 1
WHERE name = ? AND server = ?
""",
(channel, self.hostname),
)
conn.commit()
await super(IRCListener, self).on_join(channel, user)
