]>
git.rm.cloudns.org Git - xonotic/xonstatdb.git/log
Ant Zucaro [Sat, 30 Jan 2016 19:54:27 +0000 (14:54 -0500)]
Add active servers MV to the build.
Ant Zucaro [Sat, 30 Jan 2016 19:53:53 +0000 (14:53 -0500)]
Add a script to refresh active servers.
Ant Zucaro [Sat, 30 Jan 2016 19:52:50 +0000 (14:52 -0500)]
Add an MV for active servers.
Ant Zucaro [Sat, 30 Jan 2016 19:50:05 +0000 (14:50 -0500)]
Unify permissions on all the table scripts.
Ant Zucaro [Sat, 30 Jan 2016 19:49:14 +0000 (14:49 -0500)]
Add active players MV to the build.
Ant Zucaro [Sat, 30 Jan 2016 19:48:17 +0000 (14:48 -0500)]
Add script to refresh the active players MV.
Ant Zucaro [Sat, 30 Jan 2016 19:47:30 +0000 (14:47 -0500)]
Add an MV for active players.
Ant Zucaro [Fri, 29 Jan 2016 03:14:05 +0000 (22:14 -0500)]
Add script to refresh summary stats.
Ant Zucaro [Fri, 29 Jan 2016 03:13:04 +0000 (22:13 -0500)]
Add a create_dt to summary stats.
Ant Zucaro [Fri, 29 Jan 2016 03:11:18 +0000 (22:11 -0500)]
Add a summary stats "materialized view".
Ant Zucaro [Tue, 26 Jan 2016 14:42:25 +0000 (09:42 -0500)]
Consolidate indexes for the games table.
Ant Zucaro [Thu, 21 Jan 2016 23:46:15 +0000 (18:46 -0500)]
Exclude rankings for those at the Elo floor.
Those with an Elo value of 100 (the floor) will no longer be ranked.
Additionally, there will no longer be shared ranks. If two players happen to
have the exact same Elo value (unlikely), the player whose Elo record was
created first will be given the higher rank. This rewards players who have
stuck around for a long time, should they tie with someone else who hasn't been
around as long.
Ant Zucaro [Sat, 13 Jun 2015 13:18:47 +0000 (09:18 -0400)]
Update the unique key on the player captimes table.
Ant Zucaro [Sat, 13 Jun 2015 12:39:58 +0000 (08:39 -0400)]
The mod is 64 characters, not 30.
Ant Zucaro [Tue, 9 Jun 2015 21:42:37 +0000 (17:42 -0400)]
Add the mod name to the captimes table to somewhat distinguish different methods of capping.
Ant Zucaro [Tue, 9 Jun 2015 21:38:11 +0000 (17:38 -0400)]
Add a field to track the absolute number of impure cvar changes.
Ant Zucaro [Wed, 22 Apr 2015 00:54:15 +0000 (20:54 -0400)]
Add the Arc to the weapons table.
Ant Zucaro [Sun, 11 Jan 2015 17:30:05 +0000 (12:30 -0500)]
Add primary keys using our naming scheme.
Rudolf Polzer [Sun, 11 Jan 2015 16:04:00 +0000 (16:04 +0000)]
Create a game_id index, and put the gin(players) index into gen_partitions.shl too for easier maintenance.
Ant Zucaro [Wed, 24 Dec 2014 18:28:59 +0000 (13:28 -0500)]
The games table gets a gin index on players.
Ant Zucaro [Tue, 16 Dec 2014 02:31:00 +0000 (21:31 -0500)]
Store a list of player_ids in the games table.
Ant Zucaro [Sat, 13 Dec 2014 03:57:22 +0000 (22:57 -0500)]
Add new weapons to the initial data set.
Ant Zucaro [Tue, 2 Dec 2014 02:18:57 +0000 (21:18 -0500)]
Partition tables out to 2020, remove old ones.
Since these scripts are used to create a new xonstatdb, references to
partitions using past dates will just mean empty tables. I'll cut off this
pruning at each prior year.
Ant Zucaro [Sun, 30 Nov 2014 13:22:47 +0000 (08:22 -0500)]
Add team_game_stats to the mix.
Ant Zucaro [Wed, 19 Nov 2014 02:17:01 +0000 (21:17 -0500)]
Add indexes. Rename to reflect its purpose.
Ant Zucaro [Tue, 18 Nov 2014 03:16:40 +0000 (22:16 -0500)]
Add table definitions.
Ant Zucaro [Tue, 18 Nov 2014 02:39:48 +0000 (21:39 -0500)]
Complete trigger definition.
Ant Zucaro [Tue, 18 Nov 2014 02:21:03 +0000 (21:21 -0500)]
Rule generator for partitions. Incomplete.
Ant Zucaro [Sat, 12 Jul 2014 21:11:07 +0000 (17:11 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Sat, 12 Jul 2014 21:09:45 +0000 (17:09 -0400)]
Take the greatest Elo per gametype in merges.
With Elo rot in place, doing a weighted average is no longer
appropriate. We will still sum the game count between the two
records, but the Elo will get set to the maximum score instead
of the weighted average.
Ant Zucaro [Mon, 16 Jun 2014 01:05:07 +0000 (21:05 -0400)]
Add groups table for admin functionality.
Ant Zucaro [Fri, 13 Jun 2014 12:24:13 +0000 (08:24 -0400)]
Add email address to the players table (for admins only).
Ant Zucaro [Wed, 28 May 2014 00:36:41 +0000 (20:36 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Wed, 28 May 2014 00:36:27 +0000 (20:36 -0400)]
Add script to purge anticheat logs.
Ant Zucaro [Wed, 28 May 2014 00:32:16 +0000 (20:32 -0400)]
Add an anticheat log table.
Ant Zucaro [Mon, 10 Feb 2014 23:32:54 +0000 (18:32 -0500)]
Playing one Elo game type in the past 30 days will prevent rot.
Ant Zucaro [Wed, 2 Oct 2013 00:44:38 +0000 (20:44 -0400)]
Fix date range for q4 partition.
antzucaro [Sat, 17 Aug 2013 04:34:33 +0000 (00:34 -0400)]
Damn off-by-one errors. *shakes fist*
antzucaro [Sat, 17 Aug 2013 04:12:14 +0000 (00:12 -0400)]
Add Elo rot analysis query.
antzucaro [Sat, 17 Aug 2013 03:51:00 +0000 (23:51 -0400)]
Use a new Elo rot scheme.
The old rot was to decay 1 Elo point per day of inactivity beyond
30 days of inactivity. This was extremely conservative and leads
to people staying on the leaderboards for an extraordinary amount
of time after they have ceased playing (or ceased recording).
The new system uses a weeks-based rot Starting on your 31st day
of inactivity (as defined by no recorded games in the particular
game type), you will lose 1 point per week of inactivty *each day*.
This looks like so:
Days 1-30: nothing happens - no penalty
Days 31-37: 1 point docked per day
Days 38-44: 2 points docked per day
... and so on.
It is my hope that this system will favor active, skilled players
while at the same time penalizing inactive players.
Ant Zucaro [Sun, 11 Aug 2013 14:01:11 +0000 (10:01 -0400)]
Add drop and load convenience script.
Ant Zucaro [Thu, 23 May 2013 02:05:53 +0000 (22:05 -0400)]
Various typos on Q4.
Ant Zucaro [Thu, 23 May 2013 02:05:06 +0000 (22:05 -0400)]
Partition player weapon stats through 2014.
Ant Zucaro [Thu, 23 May 2013 01:42:56 +0000 (21:42 -0400)]
Partition player game stats through 2014.
Ant Zucaro [Thu, 23 May 2013 01:24:36 +0000 (21:24 -0400)]
Partition games through 2014.
Ant Zucaro [Thu, 23 May 2013 01:11:39 +0000 (21:11 -0400)]
You know what? We don't need tables for the past!
Ant Zucaro [Thu, 23 May 2013 01:05:02 +0000 (21:05 -0400)]
Configure partitions through 2014.
Ant Zucaro [Thu, 23 May 2013 00:58:09 +0000 (20:58 -0400)]
Add rounds, caps fields.
Ant Zucaro [Sun, 21 Apr 2013 20:36:53 +0000 (16:36 -0400)]
Add team scores table. Partition till 2015.
Ant Zucaro [Tue, 5 Feb 2013 08:26:43 +0000 (03:26 -0500)]
Add port to servers table
Ant Zucaro [Tue, 5 Feb 2013 08:25:09 +0000 (03:25 -0500)]
Add mod string to games table
Ant Zucaro [Fri, 1 Feb 2013 21:21:26 +0000 (16:21 -0500)]
Identify games with bad ranks.
Ant Zucaro [Fri, 25 Jan 2013 03:28:22 +0000 (22:28 -0500)]
Modify column structure for better reuse.
Ant Zucaro [Wed, 16 Jan 2013 02:28:35 +0000 (21:28 -0500)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Wed, 16 Jan 2013 02:28:12 +0000 (21:28 -0500)]
Add teamrank and scoreboardpos fields.
antzucaro [Wed, 2 Jan 2013 16:47:59 +0000 (11:47 -0500)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
antzucaro [Wed, 2 Jan 2013 16:47:42 +0000 (11:47 -0500)]
Use greatest instead of least.
Ant Zucaro [Mon, 31 Dec 2012 03:45:33 +0000 (22:45 -0500)]
Add win ratio query
Ant Zucaro [Mon, 31 Dec 2012 03:41:11 +0000 (22:41 -0500)]
Add player retention query
Ant Zucaro [Sat, 15 Dec 2012 15:17:44 +0000 (10:17 -0500)]
Add primary key to summary stats. SQLAlchemy requires it.
Ant Zucaro [Sat, 15 Dec 2012 15:07:08 +0000 (10:07 -0500)]
Remove the commit. It is redundant.
Ant Zucaro [Sat, 15 Dec 2012 14:58:10 +0000 (09:58 -0500)]
Add a small script to update the summary stats table.
This script is intended to be run from cron on a semi-frequent
basis, like hourly or daily. One would adjust that depending on
the volume of new games/players/servers.
Ant Zucaro [Sat, 15 Dec 2012 14:48:26 +0000 (09:48 -0500)]
Add the summary stats table to the build.
Ant Zucaro [Sat, 15 Dec 2012 14:44:06 +0000 (09:44 -0500)]
Add support for summary statistics.
The summary statistics table is very small, and is intended
to be populated via cron to avoid any lag in the actual webapp
when scanning all of the rows in players, games, and servers.
Even caching such a query would yield bad performance during
invalidation, so I've opted to make this table be populated with
an offline script similar to the one for ranks.
Ant Zucaro [Sun, 2 Dec 2012 19:43:36 +0000 (14:43 -0500)]
Put a blurb about the misc functions into the README.
Ant Zucaro [Sun, 2 Dec 2012 19:30:49 +0000 (14:30 -0500)]
Add elo rot script.
Ant Zucaro [Sun, 2 Dec 2012 13:03:23 +0000 (08:03 -0500)]
Add an update_dt to the player_elos table.
Ant Zucaro [Mon, 29 Oct 2012 16:03:08 +0000 (12:03 -0400)]
Fix merge.
Ant Zucaro [Mon, 29 Oct 2012 15:55:11 +0000 (11:55 -0400)]
Add captimes table to the build.
Ant Zucaro [Mon, 29 Oct 2012 15:53:47 +0000 (11:53 -0400)]
Add indexes to the game and weapon stats tables. Store captimes.
Ant Zucaro [Fri, 24 Aug 2012 13:59:56 +0000 (09:59 -0400)]
Merge branch 'master' of ssh://push.git.xonotic.org/xonstatdb
Ant Zucaro [Fri, 24 Aug 2012 13:55:35 +0000 (09:55 -0400)]
Index game_id and player_id for performance (and badges).
Ant Zucaro [Tue, 21 Aug 2012 21:40:16 +0000 (17:40 -0400)]
Add players to ladder associative table, with ranks.
Ant Zucaro [Tue, 21 Aug 2012 21:30:23 +0000 (17:30 -0400)]
Change title to name.
Ant Zucaro [Tue, 21 Aug 2012 01:28:58 +0000 (21:28 -0400)]
Add the ladder code table.
Ant Zucaro [Mon, 11 Jun 2012 11:03:42 +0000 (07:03 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Mon, 11 Jun 2012 11:01:05 +0000 (07:01 -0400)]
Add an active indicator to player_elo.
This will allow me to disable the ranking of players without
disabling the entire player record. This is to be applied
in cases where players have cheated more than once but don't
warrant a full kickban. Instead I can now make their record
essentially harmless with respect to everyone else.
antzucaro [Fri, 27 Apr 2012 13:52:13 +0000 (06:52 -0700)]
Merge pull request #1 from nyov/master
updated README
nyov [Fri, 27 Apr 2012 02:05:55 +0000 (04:05 +0200)]
Modified Readme
Ant Zucaro [Sat, 21 Apr 2012 20:30:37 +0000 (16:30 -0400)]
Default games to 1 in another place.
Ant Zucaro [Sat, 21 Apr 2012 20:25:40 +0000 (16:25 -0400)]
Default total games to 1 and also print who we are merging.
Ant Zucaro [Sat, 21 Apr 2012 20:00:27 +0000 (16:00 -0400)]
Do a weighted average instead of picking the max.
Ant Zucaro [Tue, 20 Mar 2012 12:53:04 +0000 (08:53 -0400)]
Damn you, pgadmin!
Ant Zucaro [Tue, 20 Mar 2012 12:44:18 +0000 (08:44 -0400)]
Adding yo dawg, just for the hell of it.
Ant Zucaro [Tue, 20 Mar 2012 12:43:12 +0000 (08:43 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Tue, 20 Mar 2012 12:41:40 +0000 (08:41 -0400)]
A simple dashboard report for providing high level stats...about stats!
Ant Zucaro [Thu, 1 Mar 2012 04:08:20 +0000 (23:08 -0500)]
Get rid of bad header chars in the file. All else is the same.
Ant Zucaro [Wed, 29 Feb 2012 17:26:07 +0000 (12:26 -0500)]
Add advanced elo checks in the merge. We want an optimistic view of the player, so we will go with the best possible elo.
Ant Zucaro [Wed, 29 Feb 2012 12:05:05 +0000 (07:05 -0500)]
Function to merge two players.
Ant Zucaro [Mon, 20 Feb 2012 16:42:21 +0000 (11:42 -0500)]
Support deactivating players.
Ant Zucaro [Mon, 20 Feb 2012 15:25:35 +0000 (10:25 -0500)]
Change game type codes to match new gametypes abbreviations.
Ant Zucaro [Sun, 22 Jan 2012 17:36:34 +0000 (12:36 -0500)]
Add rank history to the build.
Ant Zucaro [Sun, 22 Jan 2012 17:36:21 +0000 (12:36 -0500)]
Whoops, add the 32 game req.
Ant Zucaro [Sun, 22 Jan 2012 17:35:41 +0000 (12:35 -0500)]
Save rank history and also do not generate ranks if < 32 games played for the game type.
Ant Zucaro [Sun, 22 Jan 2012 17:31:50 +0000 (12:31 -0500)]
Add rank history table.
Ant Zucaro [Sun, 22 Jan 2012 04:08:33 +0000 (23:08 -0500)]
Add elos and ranks to the build file.
Ant Zucaro [Sun, 22 Jan 2012 04:07:24 +0000 (23:07 -0500)]
Fix player_ranks.
Ant Zucaro [Sun, 22 Jan 2012 03:54:41 +0000 (22:54 -0500)]
Small transaction to regenerate ranks.
Ant Zucaro [Sun, 22 Jan 2012 03:49:43 +0000 (22:49 -0500)]
Add ranks table for batch purposes.
Ant Zucaro [Sat, 31 Dec 2011 22:46:11 +0000 (17:46 -0500)]
Add player Elo support.