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 totrue
(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¶ms[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
-
noice!
i will definetly convert shadowmod to this when i update that plugin to sockbot2.0
/me bookmarks this thread for laters.
-
explain - set to true (exact) to receive an execution plan in the output.
Exact? As in, case sensitive? Any reason for that?
params - A form-encoded hash of the parameterized values needed by the query. (e.g. a=b¶ms[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.
-
Exact? As in, case sensitive? Any reason for that?
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.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.
-
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?
-
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 ...
-
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.
-
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.
-
/me hides the source code for @SockBot behind her back
So you're pulling it from github?
-
no, nut poi9nting out that a bot could do that if someone wrote the module for it.
-
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.
-
No, a bot could try.
and succeed! (just jeff in small batches problem solved?)
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!There is no reason to expect that a bot would be any more successful than I was.
other than science!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 ;-)
-
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.
yes! lets do that again!
No.
other than science!
I have it on good authority that i was, in fact, an accident
I did not need (or want) to know that.
-
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.
-