archivebot.py
def handle_query(event, cursor, say):
    """
    Handles a DM to the bot that is requesting a search of the archives.

    Usage:

        <query> from:<user> in:<channel> sort:asc|desc limit:<number>

        query: The text to search for.
        user: If you want to limit the search to one user, the username.
        channel: If you want to limit the search to one channel, the channel name.
        sort: Either asc if you want to search starting with the oldest messages,
            or desc if you want to start from the newest. Default asc.
        limit: The number of responses to return. Default 10.
    """
    try:
        text = []
        user_name = None
        channel_name = None
        sort = None
        limit = 10

        params = event["text"].lower().split()
        for p in params:
            # Handle emoji
            # usual format is " :smiley_face: "
            if len(p) > 2 and p[0] == ":" and p[-1] == ":":
                text.append(p)
                continue

            p = p.split(":")

            if len(p) == 1:
                text.append(p[0])
            if len(p) == 2:
                if p[0] == "from":
                    user_name = p[1]
                if p[0] == "in":
                    channel_name = p[1].replace("#", "").strip()
                if p[0] == "sort":
                    if p[1] in ["asc", "desc"]:
                        sort = p[1]
                    else:
                        raise ValueError("Invalid sort order %s" % p[1])
                if p[0] == "limit":
                    try:
                        limit = int(p[1])
                    except:
                        raise ValueError("%s not a valid number" % p[1])

        query = f"""
            SELECT DISTINCT
                messages.message, messages.user, messages.timestamp, messages.channel
            FROM messages
            INNER JOIN users ON messages.user = users.id
            -- Only query channel that archive bot is a part of
            INNER JOIN (
                SELECT * FROM channels
                INNER JOIN members ON
                    channels.id = members.channel AND
                    members.user = (?)
            ) as channels ON messages.channel = channels.id
            INNER JOIN members ON channels.id = members.channel
            WHERE
                -- Only return messages that are in public channels or the user is a member of
                (channels.is_private <> 1 OR members.user = (?)) AND
                messages.message LIKE (?)
        """
        query_args = [app._bot_user_id, event["user"], "%" + " ".join(text) + "%"]

        if user_name:
            query += " AND users.name = (?)"
            query_args.append(user_name)
        if channel_name:
            query += " AND channels.name = (?)"
            query_args.append(channel_name)
        if sort:
            query += " ORDER BY messages.timestamp %s" % sort

        logger.debug(query)
        logger.debug(query_args)

        cursor.execute(query, query_args)

        res = cursor.fetchmany(limit)
        res_message = None
        if res:
            logger.debug(res)
            res_message = "\n".join(
                [
                    "*<@%s>* _<!date^%s^{date_pretty} {time}|A while ago>_ _<#%s>_\n%s\n\n"
                    % (i[1], int(float(i[2])), i[3], i[0])
                    for i in res
                ]
            )
        if res_message:
            say(res_message)
        else:
            say("No results found")
    except ValueError as e:
        logger.error(traceback.format_exc())
        say(str(e))

    Handles a DM to the bot that is requesting a search of the archives.

    Usage:

        <query> from:<user> in:<channel> sort:asc|desc limit:<number>

        query: The text to search for.
        user: If you want to limit the search to one user, the username.
        channel: If you want to limit the search to one channel, the channel name.
        sort: Either asc if you want to search starting with the oldest messages,
            or desc if you want to start from the newest. Default asc.
        limit: The number of responses to return. Default 10.
    
Handles a DM to the bot that is requesting a search of the archives.
Usage.

 from: in: sort:asc|desc limit:

The text to search for.
user: If you want to limit the search to one user, the username.
channel: If you want to limit the search to one channel, the channel name.
sort: Either asc if you want to search starting with the oldest messages,
or desc if you want to start from the newest. Default asc.
limit: The number of responses to return. Default 10.
Handles a DM to the bot that is requesting a search of the archives . Usage . from : in : sort : asc|desc limit : The text to search for . user : If you want to limit the search to one user the username . channel : If you want to limit the search to one channel the channel name . sort : Either asc if you want to search starting with the oldest messages or desc if you want to start from the newest . Default asc . limit : The number of responses to return . Default 10 .
def handle_query(event, cursor, say):
    try:
        text = []
        user_name = None
        channel_name = None
        sort = None
        limit = 10
        params = event["text"].lower().split()
        for p in params:
            if len(p) > 2 and p[0] == ":" and p[-1] == ":":
                text.append(p)
                continue
            p = p.split(":")
            if len(p) == 1:
                text.append(p[0])
            if len(p) == 2:
                if p[0] == "from":
                    user_name = p[1]
                if p[0] == "in":
                    channel_name = p[1].replace("#", "").strip()
                if p[0] == "sort":
                    if p[1] in ["asc", "desc"]:
                        sort = p[1]
                    else:
                        raise ValueError("Invalid sort order %s" % p[1])
                if p[0] == "limit":
                    try:
                        limit = int(p[1])
                    except:
                        raise ValueError("%s not a valid number" % p[1])
        query = f"""
            SELECT DISTINCT
                messages.message, messages.user, messages.timestamp, messages.channel
            FROM messages
            INNER JOIN users ON messages.user = users.id
            -- Only query channel that archive bot is a part of
            INNER JOIN (
                SELECT * FROM channels
                INNER JOIN members ON
                    channels.id = members.channel AND
                    members.user = (?)
            ) as channels ON messages.channel = channels.id
            INNER JOIN members ON channels.id = members.channel
            WHERE
                -- Only return messages that are in public channels or the user is a member of
                (channels.is_private <> 1 OR members.user = (?)) AND
                messages.message LIKE (?)
        """
        query_args = [app._bot_user_id, event["user"], "%" + " ".join(text) + "%"]
        if user_name:
            query += " AND users.name = (?)"
            query_args.append(user_name)
        if channel_name:
            query += " AND channels.name = (?)"
            query_args.append(channel_name)
        if sort:
            query += " ORDER BY messages.timestamp %s" % sort
        logger.debug(query)
        logger.debug(query_args)
        cursor.execute(query, query_args)
        res = cursor.fetchmany(limit)
        res_message = None
        if res:
            logger.debug(res)
            res_message = "\n".join(
                [
                    "*<@%s>* _<!date^%s^{date_pretty} {time}|A while ago>_ _<#%s>_\n%s\n\n"
                    % (i[1], int(float(i[2])), i[3], i[0])
                    for i in res
                ]
            )
        if res_message:
            say(res_message)
        else:
            say("No results found")
    except ValueError as e:
        logger.error(traceback.format_exc())
        say(str(e))
def handle_query ( event , cursor , say ) : try : text = [ ] user_name = None channel_name = None sort = None limit = 10 params = event [ "text" ] . lower ( ) . split ( ) for p in params : if len ( p ) > 2 and p [ 0 ] == ":" and p [ - 1 ] == ":" : text . append ( p ) continue p = p . split ( ":" ) if len ( p ) == 1 : text . append ( p [ 0 ] ) if len ( p ) == 2 : if p [ 0 ] == "from" : user_name = p [ 1 ] if p [ 0 ] == "in" : channel_name = p [ 1 ] . replace ( "#" , "" ) . strip ( ) if p [ 0 ] == "sort" : if p [ 1 ] in [ "asc" , "desc" ] : sort = p [ 1 ] else : raise ValueError ( "Invalid sort order %s" % p [ 1 ] ) if p [ 0 ] == "limit" : try : limit = int ( p [ 1 ] ) except : raise ValueError ( "%s not a valid number" % p [ 1 ] ) query = f"""
            SELECT DISTINCT
                messages.message, messages.user, messages.timestamp, messages.channel
            FROM messages
            INNER JOIN users ON messages.user = users.id
            -- Only query channel that archive bot is a part of
            INNER JOIN (
                SELECT * FROM channels
                INNER JOIN members ON
                    channels.id = members.channel AND
                    members.user = (?)
            ) as channels ON messages.channel = channels.id
            INNER JOIN members ON channels.id = members.channel
            WHERE
                -- Only return messages that are in public channels or the user is a member of
                (channels.is_private <> 1 OR members.user = (?)) AND
                messages.message LIKE (?)
        """ query_args = [ app . _bot_user_id , event [ "user" ] , "%" + " " . join ( text ) + "%" ] if user_name : query += " AND users.name = (?)" query_args . append ( user_name ) if channel_name : query += " AND channels.name = (?)" query_args . append ( channel_name ) if sort : query += " ORDER BY messages.timestamp %s" % sort logger . debug ( query ) logger . debug ( query_args ) cursor . execute ( query , query_args ) res = cursor . fetchmany ( limit ) res_message = None if res : logger . debug ( res ) res_message = "\n" . join ( [ "*<@%s>* _<!date^%s^{date_pretty} {time}|A while ago>_ _<#%s>_\n%s\n\n" % ( i [ 1 ] , int ( float ( i [ 2 ] ) ) , i [ 3 ] , i [ 0 ] ) for i in res ] ) if res_message : say ( res_message ) else : say ( "No results found" ) except ValueError as e : logger . error ( traceback . format_exc ( ) ) say ( str ( e ) )
Handles a DM to the bot that is requesting a search of the archives.
Handles a DM to the bot that is requesting a search of the archives .
"""
    Handles a DM to the bot that is requesting a search of the archives.

    Usage:

        <query> from:<user> in:<channel> sort:asc|desc limit:<number>

        query: The text to search for.
        user: If you want to limit the search to one user, the username.
        channel: If you want to limit the search to one channel, the channel name.
        sort: Either asc if you want to search starting with the oldest messages,
            or desc if you want to start from the newest. Default asc.
        limit: The number of responses to return. Default 10.
    """ # Handle emoji # usual format is " :smiley_face: "
event
cursor
say