User talk:Kymara: Difference between revisions

== loots for achievements ==

<source lang = "sql">

create table looted_all_items

select

i1.id,

i1.itemid,

i1.param1,

i1.timedate,

cast('' as char(32)) as name,

cast(0 as unsigned) as nextid

from itemlog i1

where event = 'register'

and (param1 like 'golden %' or param1 like 'black %' or param1 like 'chaos %' or param1 like 'shadow %');

delete from looted_all_items

where param1 IN ('golden arrow', 'golden chainmail', 'golden mace', 'golden hammer', 'black apple', 'black pearl', 'black book');

alter table looted_all_items

add primary key (id),

add index Index_looted_itemid(itemid);

-- actually for all but one item, nextid = id+1 - but I didn't want to assume this!

update looted_all_items l

set nextid = (select i1.id from itemlog i1

where i1.itemid=l.itemid and i1.id>l.id

order by i1.id

limit 1);

update looted_all_items

join itemlog on nextid = itemlog.id

set name = source

where event = 'slot-to-slot'

and itemlog.param2 = 'content'

and source = param3;

</source>