For shadowmod - live query API



  • @shadowmod will need to make the following request to query the live database:

    POST /admin/plugins/explorer/queries/12345/run
    

    Where 12345 is the query ID. Queries can only be set up and run by admins.

    With a form-encoded body with the following parameters: (these are all optional!)

    • explain - set to true (exact) to receive an execution plan in the output.
    • limit - row limit (1000 if not specified)
    • params - A form-encoded hash of the parameterized values needed by the query. (e.g. a=b&params[username]=riking). If the parameter is all digits it will be treated as an integer; otherwise it will be a string. If you need to check if a value is in a list of possible values, use = ANY ( :param::int[] ).

    It will get back the following response:

    {

    • success - true/false. if false, inspect the errors value and do not access any other values.
    • errors - array of strings.
    • duration - float. Time to execute the query, in milliseconds to 1 decimal.
    • columns - array of strings. Titles of the returned columns, in order. Note - a lot of characters are valid here, including ?column? and $.
    • explain - string | null. Postgres query plan, UNIX newlines.
    • rows - array of array of strings. Results of the query. In the same order as columns.

    }

    SUBJECT TO CHANGE. especially the 'all parameters are strings' However, most of the output should be stable.

    ping @accalia


  • FoxDev

    noice!

    i will definetly convert shadowmod to this when i update that plugin to sockbot2.0

    /me bookmarks this thread for laters.


  • BINNED

    @riking said:

    explain - set to true (exact) to receive an execution plan in the output.

    Exact? As in, case sensitive? Any reason for that?

    @riking said:

    params - A form-encoded hash of the parameterized values needed by the query. (e.g. a=b&params[username]=riking). If the parameter is all digits it will be treated as an integer; otherwise it will be a string. If you need to check if a value is in a list of possible values, use = ANY ( :param::int[] ).

    WHY THE FUCK WOULD IT CAST TO INTE...

    *looks at JS with contempt*

    ... carry on ...



  • Any reason it doesn't come with even the most simplistic interface? Looks cool though.



  • @Onyx said:

    Exact? As in, case sensitive? Any reason for that?

    @Onyx said:

    WHY THE FUCK WOULD IT CAST TO INTE...

    looks at JS with contempt

    ... carry on ...

    You switched it up. The first one is due to JS, the second one is due to ActiveRecord/Rails. The params are always strings (or arrays of strings or hashes of strings), so when they get passed into the parameterized SQL it sees a string and puts quotes around it. Turning the values into integers first, they don't get quotes.

    @Maciejasjmj said:

    Any reason it doesn't come with even the most simplistic interface? Looks cool though.

    Well, all the request parameters are optional. For queries with default params set, or no parameters (same SQL every time), you don't need any form values at all.

    I considered changing this to the more complicated system where you set the types in advance, and it supports several different types for the parameters, but then I decided CBA, you can just cast from string.



  • Yeah, but something like a menu option for people to run the queries without actually having to involve a bot - you know, a form, a simple table. I s'pose it wouldn't be too hard to hard-fill the user ID so that you can't peek on others' stats and lock some queries to TLs.


    Filed under: inb4 pull requests accepted



  • That was in the old version, but that (any user running the query) is more useful to TDWTF that it is to any of the customers, so we settled on admin-only.



  • @riking said:

    but that (any user running the query) is more useful to TDWTF that it is to any of the customers

    Wait, this is actually useful to customers? I suppose you mean the ones on hosted instances without access to the DB?


    Filed under: guess that's why it's a plugin on your personal github



  • Don't you know? Analytics are essential for the enterprise.



  • couldn't this be abused to cause discodoom?


  • BINNED

    If it is Discodoom you are after just perform some admin actions on a large topic ... let's say Jefffing the status thread to /t/1000 ...



  • @Luhmann said:

    If it is Discodoom you are after just perform some admin actions on a large topic ... let's say Jefffing the status thread to /t/1000 ...

    Considering how difficult it is to even jeff 100 posts at a time, that would be a herculean task.


  • FoxDev

    @abarker said:

    Considering how difficult it is to even jeff 100 posts at a time, that would be a herculean task.

    /me hides the source code for @SockBot behind her back



  • Yes mistress Sultanatrix of Swypos; @RaceProUK's queen, I shall appear as summoned.



  • @accalia said:

    /me hides the source code for @SockBot behind her back

    So you're pulling it from github?


  • FoxDev

    no, nut poi9nting out that a bot could do that if someone wrote the module for it.



  • @accalia said:

    no, nut poi9nting out that a bot could do that if someone wrote the module for it.

    No, a bot could try. Remember the multiple notifications and such that came from when the Discopædia was split to create the Meta-Discopædia topic? That was partly because of discourse failing to jeff groups of 75-100 posts, which meant that I had to perform the operation multiple times. This being duckwhores, this unsurprisingly led to the creation of notifications for the failed jeffings and the successful jeffings. There is no reason to expect that a bot would be any more successful than I was.

    Also, I get the feeling that some of those @accalias were intentional.


  • FoxDev

    @abarker said:

    No, a bot could try.

    and succeed! (just jeff in small batches problem solved?)

    @abarker said:

    Remember the multiple notifications and such that came from when the Discopædia was split to create the Meta-Discopædia topic?
    yes! lets do that again!

    @abarker said:

    There is no reason to expect that a bot would be any more successful than I was.
    other than science!

    @abarker said:

    Also, I get the feeling that some of those @accalias were intentional.
    I have it on good authority that i was, in fact, an accident ;-)



  • @accalia said:

    and succeed! (just jeff in small batches problem solved?)

    Ok, that would probably work, assuming the bot waited long enough to avoid any potential race conditions.

    @accalia said:

    yes! lets do that again!

    No.

    @accalia said:

    other than science!

    :facepalm:

    @accalia said:

    I have it on good authority that i was, in fact, an accident

    I did not need (or want) to know that.


  • Discourse touched me in a no-no place

    @abarker said:

    There is no reason to expect that a bot would be any more successful than I was.

    A bot could do it in smaller groups faster. Whether that would create enough notifications to be annoying is a separate issue.


  • FoxDev

    @abarker said:

    I did not need (or want) to know that.


Log in to reply