Support:Queries

Characters and accounts

Account belonging to a character

SELECT account.* 
FROM account, characters
WHERE characters.player_id=account.id AND charname='[character]';

Characters belonging to an account

SELECT characters.*
FROM account, characters
WHERE characters.player_id=account.id AND username='[account]'

Merge character into an account

-- get ids as we will need them later
select id as account_id from account where username = '[account]';
select id as character_account_id from account where username = '[character]';
update account set status = 'merged' where username ='[character]';
update characters set player_id = '[account_id]' where charname = '[character]';
insert into gameEvents (source, event, param1, param2) 
values ('dankass','accountmerge','[character_account_id]', 'dankass-->[account]');

Items

Item history of all items touched by suspicious characters

SELECT * 
FROM itemlog, 
  (SELECT DISTINCT itemid 
   FROM itemlog
   WHERE source IN ('[charname1]', '[charname2]')
  ) As x
WHERE x.itemid=itemlog.itemid
ORDER BY itemlog.itemid, itemlog.id

Item history of all items touched by suspicious characters and someone else

SELECT * 
FROM itemlog, 
  (SELECT DISTINCT i1.itemid 
   FROM itemlog i1, itemlog i2
   WHERE i1.source IN ('[charname1]', '[charname2]')
   AND i1.itemid=i2.itemid
   AND i2.source NOT IN ('null', '[charname1]', '[charname2]')
  ) As x
WHERE x.itemid=itemlog.itemid
ORDER BY itemlog.itemid, itemlog.id

The string 'null' is required in the NOT IN clause because of register events

Bots

Finding Bots

TODO: {{{1}}}

Checking if someone is a bot

The following queries lists the number of "use"-actions per player and hour. If there are high number of uses for several hours in a row, it is likely a bot.

SELECT source, left(timedate, 13) As day, count(*) As cnt
FROM gameEvents
WHERE (source='[character1]' OR source='[character2]') AND event='use' GROUP BY source, event, day ORDER BY day;