Mercurial > lbo > hg > goe_bot
changeset 22:9e7757101e75
Add scratchpad for SQL queries
| author | Lewin Bormann <lbo@spheniscida.de> |
|---|---|
| date | Sat, 10 Dec 2016 09:37:47 +0100 |
| parents | 08faa7039be7 |
| children | ee729eaf611c |
| files | queries.sql |
| diffstat | 1 files changed, 21 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/queries.sql Sat Dec 10 09:37:47 2016 +0100 @@ -0,0 +1,21 @@ +-- Queries to be used by the bot. + +-- Select a random fortune and update the 'used' column +-- +-- Select a random entry from the least-used fortunes. +SELECT id, fortune FROM fortunes WHERE used = (SELECT min(used) FROM fortunes) ORDER BY random() LIMIT 1; +UPDATE fortunes SET used = used + 1 WHERE id = $0; -- $0 comes from previous query + +-- Insert a quote (from forwarded message) +INSERT INTO quotes (ts, quote, owner, chat_id) VALUES (now(), $0, $1, $2); + +-- Select a random quote and update the 'quoted' column +SELECT id, ts, quote, owner FROM quotes + WHERE quoted = (SELECT min(quoted) FROM quotes) + AND chat_id = $0 -- constrain on quotes from the same chat, if needed + ORDER BY random() + LIMIT 1; +UPDATE quotes SET quoted = quoted + 1 WHERE id = $0; -- $0 comes from previous query + +-- Select open todo items +SELECT id, text, owner FROM todo WHERE NOT done AND chat_id = $0 AND list = '' ORDER BY ts;