JRG Stats v1.6b (fast php killog parser / stats program)

[jrgp]

JRG Stats is a Soldat kill log parser.

It pretty much does the same thing as Zitrostats and U13SSS, only it is a little faster (in a few areas) and much more customizable and lightweight.

Download latest Release (v1.6) (~60k)

See in action

View the license (GPL)

What I really need are code and feature suggestions and bug notifications.


here's my server with this stats app installed
soldat://68.205.60.83:23073


Version History:

v1.6 (10/01/2007) -
 - Fixed SQL Structure bug where kills with Stationary Gun get screwed
 - Enabled support for stationary gun kills

v1.5 (9/23/2007) -
 - Update script extremely optimised
 - Ability to get logs via ftp
 - First non beta release

v1.4b (9/19/2007) -
 - Much better update script
 - Optimised SQL queries
 - Signature Image Support
 - Random bug fixes
 - Various Optimisations
 - Pagination added to the Players List page

v1.3b (9/11/2007) -
 - Support logs with the old filenames
 - Completely reworked database structure
 - Much faster
 - Tidier and more organized code

v1.2b (9/10/2007) -
 - Support for Soldat's Kill log bugs
 - More Weapon Stats
 - Tidier code in certain areas
 - Various Optimizations

v1.1b (9/9/2007) -
 - Weapon Stats
 - Weapon Stats for each player
 - Weapon Images
 - Tidier code
 - Various Optimizations

[fireblade212]

no1s going to say anything?
well if i must...

GENERAL WART.



,, well very nice, i like it alot

[DorkeyDear]

Very nice! I encourage adding more to it, although all the basic wants/needs are already there.

[jrgp]

Very nice! I encourage adding more to it, although all the basic wants/needs are already there.

Thanks

It's got a very lot of features that are about to be added (very specific weapon stats, ranking, maybe clan tracking) the only reason I haven't added them already is because my time is extremely limited.

[FliesLikeABrick]

A few comments on your database structure:

• Any stats program that has a table which stores ALL kills will get VERY slow relatively quickly.
• Assigning a unique ID to every kill entry gains you no performance, and could quite possibly cause you to lose performance.  Do you even ever make any queries that use the ID field?  Adding a key without using it for anything is not useful.
• If you do intend to store kills, you should normalize player names to numeric IDs.  This makes your database much smaller (because any repeated data is much smaller.  Representing a player as the number 423313 (if you were to ever have that many nicknames in your database) takes up 4 bytes in storage, whereas "FliesLikeABrick" takes up 15. 
• Even with this normalization, it is very hard or impossible to make that table fast to access
• If you intend to make the table searchable, you should add additional indices on the weapon, killer, and victim fields.  If you want to add a primary key to the table, you should make it (killer,victim,date).  Yes this could be a problem if someone kills the same person twice in one second, but this will almost never happen except in very weird maps and very strange gamemodes, but even then it should almost never happen.
• Adding the player name normalization and the primary key I just mentioned are the best ways to add performance to this table

These are the reasons why U13SSS doesn't work this way.  I've tried various ways of optimizing the full-killlog way, and have yet to find one that works well.


I'll let you know about anything else I find.

edit: your program doesn't work with the old log style.  It would probably be a good idea to make it do so.  If you do, I'll test it out for you and probably be able to give you a lot more feedback.

[Leo]

It didn't work when I tried it. Didn't parse any log files.

[jrgp]

Thank you Flies for your advice. I will be completely changing the mysql part of it when I get the chance.

edit: your program doesn't work with the old log style.  It would probably be a good idea to make it do so.  If you do, I'll test it out for you and probably be able to give you a lot more feedback.

I'll take a look at the old log style. Which version of the dedicated server are you referring to? 2.4.9? 2.5.0/1?

It didn't work when I tried it. Didn't parse any log files.

You need to configure config.php then run install.php before running update.php.

Also, it can only read logs generated by the latest dedicated server. (2.6.2)

[FliesLikeABrick]

uh, first of all the latest dedicated server is 2.6.2

Second of all, the newest servers have an option to use the old log format (the only difference is the file names).  You should make it compatible with those log names.

[jrgp]

uh, first of all the latest dedicated server is 2.6.2

Right, I wasn't thinking clearly.

Second of all, the newest servers have an option to use the old log format (the only difference is the file names).  You should make it compatible with those log names.

I will definitely do that then.

Right now, I am redoing the db structure (adding indexes to the right places, using id's to represent player names and weapons, etc.) and looking into the old log format so I can modify my log parser to work with it as well as the new format.

Also, is it better / faster to use mysql's date format for storing dates or just using UNIX timestamps and storing them as INT(11) ?

Date Posted: September 12, 2007, 03:22:03 PMv1.3b Released! Many, many new improvements.

[.::Blaze::.]

its got a password...so i can't play there am i missing something?

[ElephantHunter]

Also, is it better / faster to use mysql's date format for storing dates or just using UNIX timestamps and storing them as INT(11) ?

You should use INT(11). This is smaller and faster than MySQL's date format.

[Clawbug]

Source code looks neat and is very well commented, big plus for that. I am really looking forward to this.

[ElephantHunter]

This is a great program. You comment very well. Here are a few suggestions...

• Alias the tables
• Make your joins from the WHERE clause obvious
• Convert subqueries to joins
• Capitalize SQL keywords

Original Code (taken from the kill history page)

Code: [Select]

<?php

// Original code
$query = mysql_query("
select
`{$db['prefix']}kills`.`killer` as kid,
`{$db['prefix']}kills`.`victim` as vid,
`{$db['prefix']}kills`.`weapon` as wid,
`{$db['prefix']}kills`.`date` as d,
`{$db['prefix']}kills`.`suicide` as s,
`{$db['prefix']}players`.`name` as kn,
`{$db['prefix']}weapons`.`name` as wn,
(select `name` from `{$db['prefix']}players` where `id` = vid) as vn
from
`{$db['prefix']}kills`,
`{$db['prefix']}players`,
`{$db['prefix']}weapons`
where
`{$db['prefix']}kills`.`killer` = `{$db['prefix']}players`.`id`
AND
`{$db['prefix']}kills`.`weapon` = `{$db['prefix']}weapons`.`id`
order
by d desc limit ".$limit.','.$perpage);

?>

Code with modifications

Code: [Select]

<?php

$query = mysql_query("
SELECT
`kills`.`killer` AS kid,
`kills`.`victim` AS vid,
`kills`.`weapon` AS wid,
`kills`.`date` AS d,
`kills`.`suicide` AS s,
`killers`.`name` AS kn,
`weapons`.`name` AS wn,
`victims`.`name` AS vn
FROM
`{$db['prefix']}kills` AS `kills`
JOIN `{$db['prefix']}players` AS `killers` ON `killers`.`id` = `kills`.`killer`
JOIN `{$db['prefix']}players` AS `victims` ON `victims`.`id` = `kills`.`victim`
JOIN `{$db['prefix']}weapons` AS `weapons` ON `weapons`.`id` = `kills`.`weapon`
ORDER BY
d DESC LIMIT ".$limit.','.$perpage);

?>


[bja888]

There where so many inner joins in the code I had to maintain for my last job. It drove me crazy. The whole app was so poorly put together and they would join 3 tables for 3 sql query's on one page.

f***ing hated it. Not using joins is cleaner code in my opinion.

[ElephantHunter]

There where so many inner joins in the code I had to maintain for my last job. It drove me crazy. The whole app was so poorly put together and they would join 3 tables for 3 sql query's on one page.

f***ing hated it. Not using joins is cleaner code in my opinion.

I'm sorry about your poor experience. Sounds like your business was using joins incorrectly.

If used correctly, joins can increase performance. They also make it very easy to spot table relationships.

[ramirez]

Just a note about your query ElephantHunter. If you use comma to seperate the tables, you don't need the JOIN keyword. A comma equals to INNER JOIN. In matter of fact, I don't even think the syntax you're using is valid according to SQL standard (the tables should be comma seperated _or_ seperated with join, not both). That being said, the original code was INNER JOIN'ing those 3 tables, but that one subquery is unnecessary. Just join the players table twice, with different aliases, and different clauses just like EH showed. And as a side note, using ON or WHERE clause is the very same thing for INNER JOINs.

[ElephantHunter]

You're right about the commas. I forgot to take them out. Fixed.

[jrgp]

This is a great program. You comment very well. Here are a few suggestions...

• Alias the tables
• Make your joins from the WHERE clause obvious
• Convert subqueries to joins
• Capitalize SQL keywords

Original Code (taken from the kill history page)

Code: [Select]

<?php

// Original code
$query = mysql_query("
select
`{$db['prefix']}kills`.`killer` as kid,
`{$db['prefix']}kills`.`victim` as vid,
`{$db['prefix']}kills`.`weapon` as wid,
`{$db['prefix']}kills`.`date` as d,
`{$db['prefix']}kills`.`suicide` as s,
`{$db['prefix']}players`.`name` as kn,
`{$db['prefix']}weapons`.`name` as wn,
(select `name` from `{$db['prefix']}players` where `id` = vid) as vn
from
`{$db['prefix']}kills`,
`{$db['prefix']}players`,
`{$db['prefix']}weapons`
where
`{$db['prefix']}kills`.`killer` = `{$db['prefix']}players`.`id`
AND
`{$db['prefix']}kills`.`weapon` = `{$db['prefix']}weapons`.`id`
order
by d desc limit ".$limit.','.$perpage);

?>

Code with modifications

Code: [Select]

<?php

$query = mysql_query("
SELECT
`kills`.`killer` AS kid,
`kills`.`victim` AS vid,
`kills`.`weapon` AS wid,
`kills`.`date` AS d,
`kills`.`suicide` AS s,
`killers`.`name` AS kn,
`weapons`.`name` AS wn,
`victims`.`name` AS vn
FROM
`{$db['prefix']}kills` AS `kills`
JOIN `{$db['prefix']}players` AS `killers` ON `killers`.`id` = `kills`.`killer`
JOIN `{$db['prefix']}players` AS `victims` ON `victims`.`id` = `kills`.`victim`
JOIN `{$db['prefix']}weapons` AS `weapons` ON `weapons`.`id` = `kills`.`weapon`
ORDER BY
d DESC LIMIT ".$limit.','.$perpage);

?>


Thanks for tips, I will be implementing throughout the entire script. The next version will be released either late Sunday or late Monday.

By the way, does capitalizing SQL keywords make any difference?

its got a password...so i can't play there am i missing something?

Shouldn't be passworded now.

[ramirez]

Makes no difference. My personal preference is to capitalize keywords, makes it cleaner and easier to follow.

[Leo]

When I tested it with a lot of log files I got this error:

Code: [Select]

Parsing logs to get kills......
<br />
<b>Fatal error</b>:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 549 bytes) in <b>/var/www/web0/html/files/update.php</b> on line <b>84</b><br />