Fetching contributors…
Cannot retrieve contributors at this time
261 lines (185 sloc) 7.9 KB
- Window functions
part(max(sc) by char)
All second wins:
!lg * win partition(rownum(), name, o=end)=2 s=name x=min(char)
- Subquery syntax:
$ <query-type> [: <alias> ] [ ... ]
In a subquery, and only in a subquery j= foo
- Allow !lg to join on lm with lm:
Games with no Lair:
!lg * !exist($lm[br.enter=Lair])
No grouping or explicit join, therefore implicit join by game_key
Auto-join on game_key
Most common first win
!lg * s=char $lg[s=name win j=name,min(end)]
Most common second win
!lg * s=char $lg[win partition(row(), name, o=end)=2 j=game_key]
Explicit join clause:
$lg[outer:name=name outer:end=min(end)]
Game with most Boris kills
Simple JOIN subquery. The subquery is run and then joined on based
on the provided fields.
!lg * $lm[uniq=Boris s=game_id]
No explicit join or x=foo, but grouping: therefore, join on
grouped field.
finds the game with the most Boris kills.
If no x=field is provided, join happens on the grouped field
selected by s=field
Expression subquery:
A subquery may be used as an expression:
!lg * count($lm[])=0
(Shows games with no milestones; expensive)
No explicit join, x=foo or s=foo, so join by game_key.
- exists/not exists subquery:
!lg * win !exist($lm[Lair])
(finds Lairless wins)
Accept exist/exists as synonyms for exists queries.
- Make Henzell/Sequell more modular:
- Config should be hot-reloadable.
- Any modified .pm files should be reloadable.
- Convert all Henzell do 'foo.pl' to reloadable modules.
- Henzell should need restart only if the core changes.
- Join resolution:
- Non-expression queries are always joins.
- Expression queries are joins only with explicit j=
- If main query is !lg and join is !lm, default join condition is
game_key, and vice versa of !lm/!lg.
- If $[] has s=foo, join condition is foo
- If $[] has j=x,y,z join condition is all of those
- If $[] has x=q:x y=q:y, join condition is on x, y
- A join without a join condition is an error.
- Query aliasing:
- The primary query always has the alias lg/lm based on which command.
- The subquery gets no explicit alias unless requested
- Subqueries:
- Queries involved in expressions are always (possibly correlated) subqueries.
- Subqueries use the same mechanisms of join resolution -- perhaps provide
a way to get rid of implicit join conditions?
- Query construction:
- Top-level query may be:
- Standard game query, in which case select all game fields
(effectively SELECT *)
Eg: !lg * DEFE killer=hobgoblin
- s=foo,bar,baz, in which case use GROUP BY on the nominated fields and
SELECT foo, bar, baz, COUNT(*) AS c
Eg: !lg * DEFE s=ckiller,sk
- Non-summary query using x=aggregate(foo),...:
SELECT aggregate(foo)
Eg: !lg * Ke x=avg(hp), max(hp), min(hp)
Any of these queries for !lm may be joined to the listgame table,
in which case the join is by name + rstart, and all selected fields
are explicitly aliased to the relevant table: unqualified fields will
reference milestones table if present, or listgame table if not.
Qualified fields will reference the qualified table.
- Subquery may be:
- New grammar -- error checking needed:
Many errors cannot be caught by the parser. These include:
- Too many summarize clauses
- Too many order clauses
- Too many result indexes
- Ratio queries in subqueries.
- Type mismatches in expressions.
The query compiler must check explicitly for these conditions after
parsing.
- Allow query expressions:
-int!=${dex} (expressions enclosed in ${})
${expr1}!=${expr2}
${int+dex}<${str}
${int + $[[* x=count ]]}
expressions may be:
- simple field names
- full SQL arithmetic, boolean expressions: x+b*c, etc using nested
expressions.
- subqueries.
Extended Henzell queries (joins, yay):
- Fork query as a separate process, keep track of PID.
- When child exits, display its output (presumably dumped to a file)
- Set alarm to kill child after 30s. If the alarm fires, get MySQL's
process list, find any currently executing query and nuke it with
KILL CONNECTION.
- Allow !cancelquery to cancel any running query immediately using
the same process kill + connection kill.
Join clause:
Examples: j=a,b (join on a and b, simple fields or expressions.)
j=a,fn(b) (group by a, then join by a and fn(b), where fn
is an aggregate function)
Join steps:
1. Identify subqueries
2. Identify join parameters. Subquery must have x=<field> to specify joins,
OR must have s=<field> to specify grouping, in which case the same <field>
is assumed as the join condition.
3. Create query with joins. Easy!
Conditions:
1. Robust parser.
2. Must handle top-level ratio queries.
3. Must handle milestone + logfile joins as subqueries.
Parser special cases:
- Games by players who have more than X wins:
SELECT * FROM logrecord WHERE pname IN (
SELECT pname FROM logrecord WHERE ktyp='winning' GROUP BY pname
HAVING COUNT(*) > 3 );
SELECT * FROM
logrecord lg,
(SELECT pname FROM logrecord WHERE ktyp='winning' GROUP BY pname
HAVING count(*) > 3) lg2
WHERE lg.pname = lg2.pname
!lg * [[ * win s=name ?: N > 3 ]]
^^ x=field is implied if no x=<foo> is specified and there's an s=field
- Games that are first wins:
explain extended
SELECT charabbrev, count(*) AS c FROM logrecord a,
(SELECT pname, MIN(tstart) AS tstart FROM logrecord
WHERE ktyp = 'winning' GROUP BY pname) b
WHERE a.pname = b.pname AND a.tstart = b.tstart
group by charabbrev
order by c DESC;
!lg * [[ * win s=name j=name,min(start) ]] s=char
Use: j= in subqueries to select join fields.
- Player who has the most combo highscores:
Combo highscores: char, MAX(score) FROM logrecord GROUP BY char
Players with combo highscores:
SELECT l.pname, COUNT(*) c FROM logrecord l,
(SELECT charabbrev, MAX(sc) sc FROM logrecord GROUP BY charabbrev) ch
WHERE l.charabbrev = ch.charabbrev AND l.sc = ch.sc
GROUP BY l.pname ORDER BY c DESC
!lg * [[* s=char,max(sc)]] s=name
- Game with most Boris kills:
!lg * [[ !lm * uniq=Boris s=gid ?: limit=1 ]]
(The implicit order by COUNT(*) is used here, limited to 1 result to join
the right game).
- Games with most milestones in Shoals:
!lg * [[!lm * Shoals s=game_id]]
- Lairless wins:
!lg * win $[[!lm * br.enter=Lair j=start,name x=count]]=0
OR
!lg * win $[[!lm * br.enter=Lair start=q:start name=q:name x=count]]=0
Prefixes:
where qp: implies the parent query, q: the top-level query. Other queries
may use aliases specified in the subquery.
Different kinds of subqueries:
- General JOIN query -- just use x=fields to join
- Correlated subquery: use j=field1,field2 to correlate (join) on those field
values.
- Correlated subquery as expression: $[[ ]] <op> <val>
- EXISTS subquery: $exists[[ ]] (may also be correlated)
- NOT EXISTS subquery: !$exists[[ ]] (may also be correlated)
Real query parser.
---------------------------------------------------
- Save query:
!alias blarg !lg <xyz>
!blarg <stuff> => !lg <xyz> +++ <stuff>
!blarg is checked only if none of the existing commands match.
Would be nice if !alias <keywords> could work.
+++ operator to combine multiple arglists.
---------------------------------------------------
!won * t pa <- fix
!won * sprint ?
---------------------------------------------------
Allow multiple s= conditions:
s=char,god => group by char + god: 10x DEFE (3x Okawaru, 2x Xom, ...), 5x MuPr
Allow !ttyrec to use ruby dir lister.
---------------------------------------------------
!lg general syntax:
[(mode-select)] (query-form) [s=grouping] [x=show-fields] [o=ordering]
[ / (query-form) [?: extended filters]]
where query-form = <nick-selector> [keywords...] [key-op-val arguments]