User talk:Kymara

converting xml path nodes to java

sed 's|</parameter>|));|' node > node2
sed 's|<parameter name="node[0-9]*">|nodes.add(new Node(|' node2 > node3

cleanup postman table

<source lang="sql"> CREATE TABLE IF NOT EXISTS temp_postman ( source VARCHAR(64),

target     VARCHAR(64),
message    TEXT);

LOAD DATA LOCAL INFILE '/home/katie/workspace/stendhal/postmantable.csv' INTO TABLE temp_postman FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

insert into postman (source, target, message, timedate, messagetype, delivered) select source, target, message, '2010-07-20 00:00:00', 'P', 0 from temp_postman;

-- yes thats a lie about the message type, some came from npcs update postman set messagetype = 'N' where source in ('MrTaxman','Dagobert','Harold','Wilfred');

-- add indices on target and delivered (combined?)

-- remove messages where the target does not correspond to an existing character name delete postman from postman left join characters on characters.charname = postman.target where characters.charname is null;

-- remove messages where the target account.username is permanently banned (why do this first? it might get more?) delete postman from postman join account on account.username = postman.target where account.status='banned';

-- remove messages where the target character has an account which is is permanently banned delete postman from postman join characters on characters.charname = postman.target join account on account.id = characters.player_id where account.status='banned';

-- remove any uncaught spam? delete from postman where source = 'Harold' and length(message)>1000;

-- postman used to be case sensitive on target and so there were some really ancient messages in there delete postman from postman join characters ON characters.charname = postman.target where characters.charname not like binary postman.target;

-- empty messages (1 was delivered was from new version) delete from postman where message = and delivered =0;

</source>

Stuff to do, over time

• Correct some of the warnings in findbugs:
  • Blackjack
  • Help tomi string append
  • Portal cast
• Review golden orc sword quest that's in feature requests
• Create quests pointing to dungeons
• Postman on website
• one player area access portals in banks
• equipment refactoring (right click equip, swapping items over existing items in slots)
• Stendhal Quest Histories - can we get some smart default methods in? or use existing actions?

stats

File:Bug age 20101127.ods or picture:

https://sourceforge.net/tracker/reporting/index.php?atid=101111&what=aging&span=30&period=month&group_id=1111

installing egit plugin

   * Open Help->Install New Software. 
   * Are there any sites already in a drop list list to work with? If not then click Add...
   * Add the Helios update site: Name: helios Location: http://download.eclipse.org/releases/helios/
   * type egit into the filter text box
   * Eclipse Egit should come up, mark that by clicking the small box next to it
   * Click Next > and follow through clicking Next > until finished. 
   * Restart Eclipse when prompted

getting marauroa source from git

Configuration

• First you need to tell Git about yourself, click Preferences > Team > Git > Configuration
• Click New Entry and enter

Key: user.name
value: your sourceforge username, e.g. kymara

• Click New Entry again and enter

Key: user.email
value: your sourceforge email address, e.g. kymara@users.sourceforge.net

• This information is stored in ~/.gitconfig and will be used by Git to identify who did change the history of the repository whenever you are the user logged on to your computer.

• Now you can add the rest of the file as in the example. You'll see Configuration and the file location, click open and edit it directly in Eclipse.

Get code

• File> Import ... Git> Projects from Git
• Clone ... and add :

URI ssh://USERNAME@arianne.git.sourceforge.net/gitroot/arianne/marauroa.git
changing your username to your own

• If you have trouble at this stage try restarting eclipse and try again
• Select all branches (fast anyway)
• initial branch is master with remote name origin
• let it import everything
• select the repository you just cloned .. Next>
• Method for project creation: use the new projects wizard. Don't know about the team sharing option I did the default.
• Finish this part
• new Project Wizard will open
• Select Java Project, Net>
• give it a name (marauroa will do)
• I think the other defaults are okay
• Finish

elfy

<source lang = "sql"> create table temp_elf_princess select timedate, source from gameEvents where event='quest' and param1= 'elf_princess' and param2 = 'flower_brought' and id < 58379295; Query OK, 562 rows affected (6.22 sec) Records: 562 Duplicates: 0 Warnings: 0

-- yeah I could have done a union but I wanted to know counts and have more control insert into temp_elf_princess select timedate, source from gameEvents_2009_08_17 where event='quest' and param1= 'elf_princess' and param2 = 'flower_brought'; Query OK, 346 rows affected (5.67 sec) Records: 346 Duplicates: 0 Warnings: 0

insert into temp_elf_princess select timedate, source from gameEvents_2009_02_19 where event='quest' and param1= 'elf_princess' and param2 = 'flower_brought'; Query OK, 393 rows affected (6.40 sec) Records: 393 Duplicates: 0 Warnings: 0

insert into temp_elf_princess select timedate, source from gameEvents_2008_08_21 where event='quest' and param1= 'elf_princess' and param2 = 'flower_brought'; Query OK, 131 rows affected (49.79 sec) Records: 131 Duplicates: 0 Warnings: 0

select count(*) from temp_elf_princess; +----------+ | count(*) | +----------+ | 1432 | +----------+ </source>

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>

flour

<source lang = "sql"> create table temp_milled_flour select source, substring_index(param2,';',1) as count, id as gameEvents_id, timedate from gameEvents where event ='quest' and param1 = 'jenny_mill_flour' and param2 <> 'done' and timedate < '2010-11-24';

insert into temp_milled_flour SELECT source, substring_index(param2,';',1) AS count, id AS gameEvents_id, timedate FROM gameEvents_2009_08_17 WHERE event ='quest' AND param1 = 'jenny_mill_flour' AND param2 <> 'done' ;

insert into temp_milled_flour SELECT source, substring_index(param2,';',1) AS count, id AS gameEvents_id, timedate FROM gameEvents_2009_02_19 WHERE event ='quest' AND param1 = 'jenny_mill_flour' AND param2 <> 'done' ;

insert into temp_milled_flour SELECT source, substring_index(param2,';',1) AS count, id AS gameEvents_id, timedate FROM gameEvents_2008_08_21 WHERE event ='quest' AND param1 = 'jenny_mill_flour' AND param2 <> 'done' ;

-- yes I know about unions I just like to see row counts.

alter table temp_milled_flour add index Index_tempflour_source(source);

alter table temp_milled_flour add column last boolean default 0;

update temp_milled_flour t join (select source, max(timedate) as maxdate from temp_milled_flour group by source) t2 on t.source = t2.source set last = 1 where maxdate = timedate;

-- oh yes i remember now why i thought this was going to get hard - i can't use id like i planned. select * from temp_milled_flour where last = 1 and timedate < '2009_08_17';

-- if they didn't pick it up then delete it - as when they pick it up later they will get the points -- oh and they might pick it up between now and the next release too :/ -- anyway it's only worth to check when it's the last mill they did

/* delete FROM stendhal.temp_milled_flour where last = 1 and (select id from gameEvents where event ='quest' and param1 = 'jenny_mill_flour' and param2 = 'done' and temp_milled_flour.source=gameEvents.source and gameEvents.id > gameEvents_id limit 1) is null; */

</source>

tortoise git

Downloaded http://code.google.com/p/tortoisegit/downloads/detail?name=TortoiseGit-1.6.3.0-32bit.msi&can=2&q=

Accepted all defaults, including using the putty based ssh client (said it was better with windows) rather than openssh. Note: puttygen was therefore included.

Opened new folder, right click, Git clone .. error that something hasn't installed properly, asked me if i want to open settings dialog to fix it. (something about msysgit and a path) don't know what to do next

Okay, looks like I should have done this first:

Please install msysgit 1.6.1 or above before install tortoisegit http://code.google.com/p/msysgit

For building achievements to list at Stendhal Achievements

They still need a bit of categorisation for the page though: <source lang = "sql"> select distinct concat('{{',

  concat_ws('|',
     'Achievement',
      concat('title=',title),
      concat('description=',description),
      concat('difficulty=',(case base_score when 10 then 'easy' when 50 then 'medium' when 1000 then 'hard' else 'todo' end))),
   '}}') 

from testserver.achievement order by category, identifier; </source>

Pending achievements

<source lang = "sql"> insert into pending_achievement(charname, achievement_id, param, cnt) select source, 17, "", count(*) from temp_elf_princess group by source;

stendhal> insert into pending_achievement(charname, achievement_id, param, cnt) select name, 26, param1, 1 from looted_all_items where timedate<'2010-11-24' and name <> and param1 IN ('chaos armor', 'chaos boots', 'chaos cloak', 'chaos helmet', 'chaos legs', 'chaos shield'); Query OK, 20004 rows affected (0.46 sec) Records: 20004 Duplicates: 0 Warnings: 0

stendhal> insert into pending_achievement(charname, achievement_id, param, cnt) select name, 34, param1, 1 from looted_all_items where timedate<'2010-11-24' and name <> and param1 IN ('shadow armor', 'shadow boots', 'shadow cloak', 'shadow helmet', 'shadow legs', 'shadow shield'); Query OK, 13100 rows affected (0.20 sec) Records: 13100 Duplicates: 0 Warnings: 0

stendhal> insert into pending_achievement(charname, achievement_id, param, cnt) select name, 40, param1, 1 from looted_all_items where timedate<'2010-11-24' and name <> and param1 IN ('black armor', 'black boots', 'black cloak', 'black helmet', 'black legs', 'black shield'); Query OK, 1632 rows affected (0.17 sec) Records: 1632 Duplicates: 0 Warnings: 0

stendhal> insert into pending_achievement(charname, achievement_id, param, cnt) select name, 61, param1, 1 from looted_all_items where timedate<'2010-11-24' and name <> and param1 IN ('golden armor', 'golden boots', 'golden cloak', 'golden helmet', 'golden legs', 'golden shield'); Query OK, 9562 rows affected (0.26 sec) Records: 9562 Duplicates: 0 Warnings: 0

-- after 0.92 if we want a kill enemy army achievement, can do -- highest number so far is about 40 and there's nothing in older gameEvents tables insert into pending_achievement(charname, achievement_id, param, cnt) SELECT source, XX, "", count(*) FROM gameEvents WHERE event='quest' AND param1= 'kill_enemy_army' AND param2 like 'done%' group by source;

-- 0.94 insert into pending_achievement(charname, achievement_id, param, cnt) select name, 95, param1, 1 from looted_red_items where timedate<'2010-11-24' and name <>;

</source>

turn overflows table

<source lang="bash"> grep "Turn " log/server.log* > turn.txt sed 's/^[^:]*://' < turn.txt | sed 's/ WARN.*: //' > turn2.txt </source>

<source lang = "sql"> CREATE TABLE overflow ( odate DATE, otime TIME, o1 INTEGER, o2 INTEGER, o3 INTEGER, o4 INTEGER, o5 INTEGER, o6 INTEGER, o7 INTEGER, o8 INTEGER, o9 INTEGER, o10 INTEGER, o11 INTEGER, o12 INTEGER);

LOAD DATA LOCAL INFILE '/tmp/turn2.txt' INTO TABLE overflow FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n';

alter table overflow add column timedate datetime first;

update overflow set timedate = concat_ws(' ',odate, otime);

alter table overflow drop column odate;

alter table overflow drop column otime;

rename table overflow to overflow_sum

create table overflow (timedate timestamp, lck integer, snxt integer, visit integer, end integer, trans integer, percept integer, save integer, wnxt integer, begin integer, ulck integer, stats integer, kickdb integer, sum integer);

insert into overflow select timedate, o1, o2-o1, o3-o2, o4-o3, o5-o4, o6-o5, o7-o6, o8-o7, o9-o8, o10-o9,o11-o10,o12-o11, o12 from overflow_sum order by timedate;

</source>

Translation

2010
\#arianne.01-04.log:23:42
\#arianne.08-07.log:12:35

2011
\#arianne.01-04.log:23:42
\#arianne.02-04.log:10:15

Maze Challenge

<source lang = "sql">

-- score board for Maze Challenge, to run on 15th October select

charname, 
points, 
@old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old, 
points - @old as difference 

from halloffame_archive_recent r2 where fametype = 'M' and day = date(now()) having difference >0 order by difference desc;

-- experimental procedure to get max maze score for a given player (i.e. the ones in that list) in a certain time period DROP FUNCTION IF EXISTS getMaxMazeScore // CREATE FUNCTION getMaxMazeScore(

  name VARCHAR(32),
  enddate DATE,
  ndays SMALLINT

) RETURNS INTEGER COMMENT 'Get max maze score for player in certain time period' BEGIN

  DECLARE maxscore INT DEFAULT 0;

  SET maxscore = 0;
  WHILE ndays >= 0 DO
    select points, 
           @old := (select points from halloffame_archive_recent r1 where r1.charname = name and fametype = 'M' and day <  date_sub(enddate, interval ndays day) order by day desc limit 1) old, 
           points - @old as difference 
          from halloffame_archive_recent r2 
          where fametype = 'M' 
          and day = date_sub(enddate, interval ndays day)  
          and r2.charname = name INTO @p, @o, @d;
    IF @d > maxscore THEN
       SET maxscore = @d;
    END IF;
    SET ndays = ndays-1;

  END WHILE;

  RETURN maxscore;
    

END //

-- now put it all together in one super amazing query that runs in about 35 seconds!! wow super amazing hey!! -- (it would be way slower if the function call was not done 'outside' the scores table as the scores table uses a 'having' so is only small after it's completed. incase you wondered why I did that) select charname, difference as totalscore, getMaxMazeScore(charname, date(now()), 14) as maxscore from

   (select 
       charname, 
       points, 
       @old := (select points from halloffame_archive_recent r1 where r1.charname = r2.charname and fametype = 'M' and day < '2011-10-01' order by day desc limit 1) old, 
       points - @old as difference 
    from halloffame_archive_recent r2 where fametype = 'M' and day = date(now()) 
    having difference >0 
    order by difference desc) 

scores;

-- output is name, total score, max score, for all participants </source>

making a video

sudo apt-get install gtk-recordmydesktop

Run it. Select area to record, press Record.

Stop recording by pressing the square on the top right of your menu bar where the clock is normally.

Encoding takes a while.

Saves as out.ogv in your home folder by default.

Sound etc was okay on my netbook though it seemed to pick up the mic and you can hear typing.

Couldn't get sound to capture at all on my desktop.

Youtube didn't like the .ogv file. Said it was too long, it was 12 mins, 15 is max? maybe the problem is the format.

Convert to AVI:

sudo apt-get install mencoder

Use command as per [1]

mencoder -idx out.ogv -ovc lavc -oac mp3lame -o intro_stendhal_201201.avi

Weirdly the avi was 36 minutes but the original was 12, nothing happens after 12 minutes, weird? Trim to only keep the first 00:12:12

mencoder -endpos 00:12:12 -ovc copy -oac copy intro_stendhal_201201.avi -o intro_stendhal_201201_1.avi

following advice at [2]

Then google accepted it

Benfords Law fun

For a blog post or something, is interesting stats :)

-- xp of characters
mysql> select left(xp,1), count(*) from character_stats where xp > 0 group by left(xp,1);
+------------+----------+
| left(xp,1) | count(*) |
+------------+----------+
| 1          |     5555 |
| 2          |     3122 |
| 3          |     2380 |
| 4          |     2344 |
| 5          |     2854 |
| 6          |      996 |
| 7          |      788 |
| 8          |      667 |
| 9          |      633 |
+------------+----------+

-- age of characters
-- max is 2940058
mysql> select left(age,1), count(*) from character_stats where age > 0 group by left(age,1);
+-------------+----------+
| left(age,1) | count(*) |
+-------------+----------+
| 1           |     8810 |
| 2           |     4962 |
| 3           |     3456 |
| 4           |     2417 |
| 5           |     2854 |
| 6           |     1545 |
| 7           |     1402 |
| 8           |     1250 |
| 9           |      971 |
+-------------+----------+
9 rows in set (0.02 sec)

-- number of kills per day
-- 2160 is max
mysql> select left(cnt,1), count(*) from kills group by left(cnt,1);
+-------------+----------+
| left(cnt,1) | count(*) |
+-------------+----------+
| 1           |   527945 |
| 2           |   272803 |
| 3           |   164413 |
| 4           |   116935 |
| 5           |    81453 |
| 6           |    64920 |
| 7           |    49086 |
| 8           |    40612 |
| 9           |    32843 |
+-------------+----------+
9 rows in set (0.61 sec)

-- size of stacks of items added to other items
mysql> select left(param2,1), count(*) from itemlog where event = 'merge in' group by left(param2,1);
+----------------+----------+
| left(param2,1) | count(*) |
+----------------+----------+
| 1              | 10636326 |
| 2              |  3470611 |
| 3              |  2667505 |
| 4              |  1920918 |
| 5              |  1397535 |
| 6              |   972027 |
| 7              |   757029 |
| 8              |   683354 |
| 9              |   667485 |
+----------------+----------+
9 rows in set (5 min 38.02 sec)

paperchase check

<source lang="sql"> select source, param2 from gameEvents where event = 'quest' and param1 = 'paper_chase_2013' and param2 like 'done%' order by round(substring_index(substring_index(param2,';',2),';',-1)); </source>

sokoban check

<source lang = "sql"> select `char`, `round`, score as timetaken, (1000000*round - score) as points from (SELECT

  source AS `char`,    
  least(max(round(substring_index(substring_index(param2,';',2),';',-1))),59) AS `round`,
   max(round(substring_index(substring_index(param2,';',3),';',-1))) AS `score` 

FROM gameEvents WHERE event = 'quest' AND param1 = 'sokoban' AND param2 LIKE 'done%' GROUP BY source ) t order by points desc limit 10; </source>