Support:Queries: Difference between revisions
Jump to navigation
Jump to search
Content deleted Content added
imported>Kymara activity |
imported>Hendrik Brummermann |
||
| (27 intermediate revisions by 2 users not shown) | |||
| Line 17: | Line 17: | ||
</source> |
</source> |
||
=== Merge |
=== Merge all characters into an account === |
||
| ⚫ | |||
1. Learn the names of the original account and new account: |
|||
-- get ids as we will need them later |
|||
select id as account_id from account where username = '[account]'; |
|||
<pre> |
|||
select id as character_account_id from account where username = '[character]'; |
|||
SELECT account.username, account.status |
|||
update account set status = 'merged' where username ='[character]'; |
|||
FROM characters, account |
|||
update characters set player_id = '[account_id]' where charname = '[character]'; |
|||
WHERE account.id=player_id AND charname='[charname]'; |
|||
insert into gameEvents (source, event, param1, param2) |
|||
</pre> |
|||
values ('[character]','accountmerge','[character_account_id]', '[character]-->[account]'); |
|||
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> |
</source> |
||
| Line 90: | Line 96: | ||
SELECT left(timedate, 10) As day, source, param1, count(*) As cnt |
SELECT left(timedate, 10) As day, source, param1, count(*) As cnt |
||
FROM gameEvents |
FROM gameEvents |
||
WHERE event='use' AND timedate>date_sub(CURDATE(), INTERVAL |
WHERE event='use' AND timedate>date_sub(CURDATE(), INTERVAL 30 day) |
||
GROUP BY day, param1, source ORDER BY cnt DESC |
GROUP BY day, param1, source ORDER BY cnt DESC |
||
LIMIT 50; |
LIMIT 50; |
||
| Line 144: | Line 150: | ||
| 93715333 | logout | | 1 | |
| 93715333 | logout | | 1 | |
||
</pre> |
</pre> |
||
A variation with one session per line, identifying only the sessions which seem suspicious: |
|||
| ⚫ | |||
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> |
|||