Support:Queries: Difference between revisions
Content deleted Content added
imported>Kymara activity |
imported>Hendrik Brummermann |
||
| (31 intermediate revisions by 2 users not shown) | |||
Line 17:
</source>
=== Merge
<source lang="SQL">▼
1. Learn the names of the original account and new account:
<pre>
SELECT account.username, account.status
FROM characters, account
WHERE account.id=player_id AND charname='[charname]';
</pre>
2. As user marauroa execute the following script. <nowiki>[oldaccount]</nowiki> is the account name, from which the characters are taken away.
<source lang="bash">
cd /var/www/stendhal/scripts/cmd
php merge.php [oldaccount] [newaccount]
</source>
Line 90 ⟶ 96:
SELECT left(timedate, 10) As day, source, param1, count(*) As cnt
FROM gameEvents
WHERE event='use' AND timedate>date_sub(CURDATE(), INTERVAL
GROUP BY day, param1, source ORDER BY cnt DESC
LIMIT 50;
Line 144 ⟶ 150:
| 93715333 | logout | | 1 |
</pre>
A variation with one session per line, identifying only the sessions which seem suspicious:
▲<source lang="SQL">
SELECT login_id, min(timedate) as login_time, group_concat(distinct event) events, count(*) num_events, timediff(max(timedate),min(timedate)) time_online
FROM gameEvents m,
(SELECT source, id AS login_id, (SELECT id FROM gameEvents g2 WHERE g2.source = '[username]' AND g2.id > g1.id AND event = 'logout' ORDER BY id LIMIT 1) logout_id
FROM gameEvents g1
-- for query optimisation, specify username by hand in each subquery
WHERE source = '[username]'
AND event = 'login'
AND timedate > '[date]') temp
-- exclude the login and logout events from count
WHERE m.id BETWEEN login_id+1 AND logout_id-1
AND m.source = '[username]'
-- id check covers this off already but this optimises the query
AND timedate > '[date]'
GROUP BY login_id
-- 2 or less events (i.e. only attack and use say)
having count(distinct event) < 3
-- but more than 5 events in total
and count(*) > 5;
</source>
| |||