Recently the updating program had an issue with disk space and indexing, but I think I fixed that Paul... if something else is acting funny you should tell me right away
Updaters tell the database to update the AF table by clicking a button (it's a static table, which means it doesn't change on its own unless we tell it to).
When you said the table was out of whack, I went and pressed the button again and the table recreated itself (even if no new times were updated). When a new player is added, it shouldn't be a problem since their AF is correctly calculated immediately. This is the code that recreates the table:
Code:INSERT INTO aftable
SELECT p.pid, p.name, p.country, s.avg as val, s.count, (SELECT prev FROM temp_af WHERE pid=p.pid), hz
FROM player p NATURAL JOIN (SELECT pid, AVG(count)::FLOAT4, COUNT(*)::INT2
FROM af_raw
GROUP BY pid) s
ORDER BY s.avg;
The ORDER BY at the bottom should sort all the scores, so Stephen's 34 should appear in the correct place. Why it (only sometimes, not always) leaves players who don't have a previous score (ie. new guys) at the top or the bottom of the entire list.. I don't know. "temp_af" is a temporary listing of (pids) -> (old AF score) in order to do the "change" column, and "raw_af" is a dynamic view that maps (pid,cid) -> position.
Unless I'm really blind and can't see the problem, there shouldn't be a reason that this creates a non-sorted table. Maybe it's just a software glitch.
I can fix/hack it by forcing PHP to sort the results before it displays them on the screen, but that should be unnecessary since they should already be sorted by the s.avg (the AF score) by the database!
Pages: 1
