a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT kicks.kicker, kicks.kickerid, ukicker.seen, ukicker.curnick, kicks.victim, kicks.victimid, uvictim.seen, uvictim.curnick, kicks.timestamp, kicks.reason "
"FROM ? "
"LEFT JOIN ? ON channels.id = kicks.channelid "
- "LEFT JOIN ? AS ukicker ON (ukicker.channelid = channels.id AND (kicks.kickerid != 0 AND kicks.kickerid = ukicker.accountid OR kicks.kickerid = 0 AND kicks.kicker = ukicker.account)) "
- "LEFT JOIN ? AS uvictim ON (uvictim.channelid = channels.id AND (kicks.victimid != 0 AND kicks.victimid = uvictim.accountid OR kicks.victimid = 0 AND kicks.victim = uvictim.account)) "
+ "LEFT JOIN ? AS ukicker ON (ukicker.channelid = channels.id AND kicks.kickerid = ukicker.accountid AND kicks.kicker = ukicker.account) "
+ "LEFT JOIN ? AS uvictim ON (uvictim.channelid = channels.id AND kicks.victimid = uvictim.accountid AND kicks.victim = uvictim.account) "
"WHERE channels.name = ? ORDER BY kicks.timestamp DESC LIMIT 10", "TTTTs", "kicks", "channels", "users", "users", argv[0]);
return 0;
a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT topics.topic, topics.timestamp, topics.setby, topics.setbyid, users.seen, users.curnick "
"FROM ? "
"LEFT JOIN ? ON channels.id = topics.channelid "
- "LEFT JOIN ? ON (users.channelid = channels.id AND (topics.setbyid != 0 AND topics.setbyid = users.accountid OR topics.setbyid = 0 AND topics.setby = users.account)) "
+ "LEFT JOIN ? ON (users.channelid = channels.id AND topics.setbyid = users.accountid AND topics.setby = users.account) "
"WHERE channels.name = ? ORDER BY topics.timestamp DESC LIMIT 10", "TTTs", "topics", "channels", "users", argv[0]);
return 0;
}
static int handle_getuser(struct rline *ri, int argc, char **argv) {
-#define USER_QUERY(a, b) a "users.account, users.accountid, users.seen, users.rating, users.lines, users.chars, users.words, " \
- "users.h0, users.h1, users.h2, users.h3, users.h4, users.h5, users.h6, users.h7, users.h8, users.h9, " \
- "users.h10, users.h11, users.h12, users.h13, users.h14, users.h15, users.h16, users.h17, users.h18, " \
- "users.h19, users.h20, users.h21, users.h22, users.h23, users.last, users.quote, users.quotereset, " \
- "users.mood_happy, users.mood_sad, users.questions, users.yelling, users.caps, users.slaps, users.slapped, " \
- "users.highlights, users.kicks, users.kicked, users.ops, users.deops, users.actions, users.skitzo, " \
- "users.foul, users.firstseen, users.curnick" b
+#define USER_QUERY(b) "SELECT users.account, users.accountid, users.seen, users.rating, users.lines, users.chars, users.words, " \
+ "users.h0, users.h1, users.h2, users.h3, users.h4, users.h5, users.h6, users.h7, users.h8, users.h9, " \
+ "users.h10, users.h11, users.h12, users.h13, users.h14, users.h15, users.h16, users.h17, users.h18, " \
+ "users.h19, users.h20, users.h21, users.h22, users.h23, users.last, users.quote, users.quotereset, " \
+ "users.mood_happy, users.mood_sad, users.questions, users.yelling, users.caps, users.slaps, users.slapped, " \
+ "users.highlights, users.kicks, users.kicked, users.ops, users.deops, users.actions, users.skitzo, " \
+ "users.foul, users.firstseen, users.curnick FROM ? LEFT JOIN ? ON channels.id = users.channelid WHERE channels.name = ? AND " b
/*
Possible cases:
*/
if (argv[1][0] == '#') {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("SELECT ", " FROM ? LEFT JOIN ? ON channels.id = users.channelid WHERE channels.name = ? AND (users.accountid = 0 AND users.account = ?)"),
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("(users.accountid = 0 AND users.account = ?)"),
"TTss", "users", "channels", argv[0], &(argv[1][1]));
} else if (atoi(argv[2]) == 0) {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("SELECT ", " FROM ? LEFT JOIN ? ON channels.id = users.channelid WHERE channels.name = ? AND (users.accountid != 0 AND users.account = ?) ORDER BY users.accountid DESC LIMIT 1"),
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("(users.accountid != 0 AND users.account = ?) ORDER BY users.accountid DESC LIMIT 1"),
"TTss", "users", "channels", argv[0], argv[1]);
} else {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("SELECT ", " FROM ? LEFT JOIN ? ON channels.id = users.channelid WHERE channels.name = ? AND users.accountid = ?"),
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USER_QUERY("(users.accountid = ?)"),
"TTss", "users", "channels", argv[0], argv[2]);
}
a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT relations.first, relations.firstid, ufirst.curnick, ufirst.seen, relations.second, relations.secondid, usecond.curnick, usecond.seen, relations.seen, relations.score "
"FROM ? "
"LEFT JOIN ? ON relations.channelid = channels.id "
- "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND (relations.firstid != 0 AND relations.firstid = ufirst.accountid OR relations.firstid = 0 AND relations.first = ufirst.account)) "
- "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND (relations.secondid != 0 AND relations.secondid = usecond.accountid OR relations.secondid = 0 AND relations.second = usecond.account)) "
- "WHERE channels.name = ? ORDER BY score DESC LIMIT 25",
+ "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND relations.firstid = ufirst.accountid AND relations.first = ufirst.account) "
+ "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND relations.secondid = usecond.accountid AND relations.second = usecond.account) "
+ "WHERE channels.name = ? ORDER BY score DESC LIMIT 50",
"TTTTs", "relations", "channels", "users", "users", argv[0]);
return 0;
}
static int handle_getuserrelations(struct rline *ri, int argc, char **argv) {
+#define USERREALTIONS_QUERY(b) "SELECT relations.first, relations.firstid, ufirst.curnick, ufirst.seen, relations.second, relations.secondid, usecond.curnick, usecond.seen, relations.seen, relations.score " \
+ "FROM ? " \
+ "LEFT JOIN ? ON relations.channelid = channels.id " \
+ "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND relations.firstid = ufirst.accountid AND relations.first = ufirst.account) " \
+ "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND relations.secondid = usecond.accountid AND relations.second = usecond.account) " \
+ "WHERE channels.name = ? AND " b " ORDER BY score DESC LIMIT 25"
/*
Possible cases:
accountid = 0, account = "username" -> new-style account, look up latest account for user
*/
if (argv[1][0] == '#') {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT relations.first, relations.firstid, ufirst.curnick, ufirst.seen, relations.second, relations.secondid, usecond.curnick, usecond.seen, relations.seen, relations.score "
- "FROM ? "
- "LEFT JOIN ? ON relations.channelid = channels.id "
- "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND (relations.firstid != 0 AND relations.firstid = ufirst.accountid OR relations.firstid = 0 AND relations.first = ufirst.account)) "
- "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND (relations.secondid != 0 AND relations.secondid = usecond.accountid OR relations.secondid = 0 AND relations.second = usecond.account)) "
- "WHERE channels.name = ? AND (relations.first = ? AND relations.firstid = 0 OR relations.second = ? AND relations.secondid = 0) ORDER BY score DESC LIMIT 10",
- "TTTTsss", "relations", "channels", "users", "users", argv[0], argv[1], argv[1]);
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USERREALTIONS_QUERY("(relations.first = ? AND relations.firstid = 0 OR relations.second = ? AND relations.secondid = 0)"),
+ "TTTTsss", "relations", "channels", "users", "users", argv[0], &(argv[1][1]), &(argv[1][1]));
} else if (atoi(argv[2]) == 0) {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT relations.first, relations.firstid, ufirst.curnick, ufirst.seen, relations.second, relations.secondid, usecond.curnick, usecond.seen, relations.seen, relations.score "
- "FROM ? "
- "LEFT JOIN ? ON relations.channelid = channels.id "
- "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND (relations.firstid != 0 AND relations.firstid = ufirst.accountid OR relations.firstid = 0 AND relations.first = ufirst.account)) "
- "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND (relations.secondid != 0 AND relations.secondid = usecond.accountid OR relations.secondid = 0 AND relations.second = usecond.account)) "
- "WHERE channels.name = ? AND (ROW(relations.first, relations.firstid) = "
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USERREALTIONS_QUERY("(ROW(relations.first, relations.firstid) = "
"(SELECT account, accountid FROM ? WHERE accountid != 0 AND account = ? ORDER BY accountid DESC LIMIT 1) OR "
- "ROW(relations.second, relations.secondid) = (SELECT account, accountid FROM ? WHERE accountid != 0 AND account = ? ORDER BY accountid DESC LIMIT 1)) "
- "ORDER BY score DESC LIMIT 10",
+ "ROW(relations.second, relations.secondid) = (SELECT account, accountid FROM ? WHERE accountid != 0 AND account = ? ORDER BY accountid DESC LIMIT 1))"),
"TTTTsTsTs", "relations", "channels", "users", "users", argv[0], "users", argv[1], "users", argv[1]);
} else {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT relations.first, relations.firstid, ufirst.curnick, ufirst.seen, relations.second, relations.secondid, usecond.curnick, usecond.seen, relations.seen, relations.score "
- "FROM ? "
- "LEFT JOIN ? ON relations.channelid = channels.id "
- "LEFT JOIN ? AS ufirst ON (ufirst.channelid = channels.id AND (relations.firstid != 0 AND relations.firstid = ufirst.accountid OR relations.firstid = 0 AND relations.first = ufirst.account)) "
- "LEFT JOIN ? AS usecond ON (usecond.channelid = channels.id AND (relations.secondid != 0 AND relations.secondid = usecond.accountid OR relations.secondid = 0 AND relations.second = usecond.account)) "
- "WHERE channels.name = ? AND (relations.firstid = ? OR relations.secondid = ?) ORDER BY score DESC LIMIT 10",
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USERREALTIONS_QUERY("(relations.firstid = ? OR relations.secondid = ?)"),
"TTTTsssss", "relations", "channels", "users", "users", argv[0], argv[1], argv[2], argv[1], argv[2]);
}
}
static int handle_finduser(struct rline *ri, int argc, char **argv) {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT users.account, users.accountid, users.seen, users.curnick, channels.name, channels.privacy "
+ a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT users.account, users.accountid, users.seen, users.curnick, channels.privacy "
"FROM ? "
"LEFT JOIN ? ON channels.id = users.channelid "
- "WHERE LOWER(curnick) LIKE ? OR LOWER(account) LIKE ? ORDER BY seen DESC LIMIT 150",
- "TTss", "users", "channels", argv[0], argv[0]);
-
- return 0;
-}
-
-static int handle_findchan(struct rline *ri, int argc, char **argv) {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT name, privacy FROM ? WHERE active = 1 AND LOWER(name) LIKE ? LIMIT 25",
- "Ts", "channels", argv[0]);
-
- return 0;
-}
-
-static int handle_getuserchans(struct rline *ri, int argc, char **argv) {
-#define USERCHANS_QUERY(b) "SELECT channels.name, channels.privacy FROM ? JOIN ? ON channels.id = users.channelid WHERE channels.active = 1 AND " b
- /*
- Possible cases:
- accountid = 0, account = "username" -> new-style account, look up latest account for user
- accountid = 0, account = "#username" -> legacy account or user not authed, look up using "username" (remove #)
- accountid = <some value>, account = <unused> -> new-style account, look up by account id
- */
-
- if (argv[0][0] == '#') {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USERCHANS_QUERY("(users.accountid = 0 AND users.account = ?)"),
- "TTs", "users", "channels", &(argv[0][1]));
- } else if (atoi(argv[1]) == 0) {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, "SELECT channels.name, channels.privacy "
- "FROM ? JOIN ? ON channels.id = users.channelid "
- "WHERE channels.active = 1 AND ROW(users.account, users.accountid) = "
- "(SELECT account, accountid FROM ? WHERE users.accountid != 0 AND users.account = ? ORDER BY users.accountid DESC LIMIT 1)",
- "TTTs", "users", "channels", "users", argv[0]);
- } else {
- a4statsdb->query(a4statsdb, a4stats_nt_query_cb, ri, USERCHANS_QUERY("users.accountid = ?"),
- "TTs", "users", "channels", argv[1]);
- }
+ "WHERE channels.name = ? AND (lines > 5 OR accountid != 0) AND (LOWER(curnick) LIKE ? OR LOWER(account) LIKE ?) ORDER BY seen DESC LIMIT 150",
+ "TTsss", "users", "channels", argv[0], argv[1], argv[1]);
return 0;
}
register_handler(a4stats_node, "getrelations", 1, handle_getrelations);
register_handler(a4stats_node, "getuserrelations", 3, handle_getuserrelations);
register_handler(a4stats_node, "setprivacy", 2, handle_setprivacy);
- register_handler(a4stats_node, "finduser", 1, handle_finduser);
- register_handler(a4stats_node, "findchan", 1, handle_findchan);
- register_handler(a4stats_node, "getuserchans", 2, handle_getuserchans);
+ register_handler(a4stats_node, "finduser", 2, handle_finduser);
}
void _fini(void) {
projects / irc / quakenet / newserv.git / blobdiff