xonotic/xonstatdb.git
3 weeks agoMerge branch 'add-ok-weapons' into 'master' master
Ant Zucaro [Sun, 25 Aug 2019 16:57:56 +0000 (16:57 +0000)]
Merge branch 'add-ok-weapons' into 'master'

Add Overkill weapons

See merge request xonotic/xonstatdb!1

3 weeks agoUpdate the RPC with what it stands for. add-ok-weapons 1/head
Ant Zucaro [Sat, 24 Aug 2019 11:53:14 +0000 (07:53 -0400)]
Update the RPC with what it stands for.

3 weeks agoAdd weapon metadata for Overkill.
Ant Zucaro [Sat, 24 Aug 2019 11:50:06 +0000 (07:50 -0400)]
Add weapon metadata for Overkill.

21 months agoMerge branch 'add-game-id-indexes'
Ant Zucaro [Sat, 25 Nov 2017 22:10:40 +0000 (17:10 -0500)]
Merge branch 'add-game-id-indexes'

22 months agoAdd indexes on the game_id field. add-game-id-indexes
Ant Zucaro [Sat, 18 Nov 2017 14:06:52 +0000 (09:06 -0500)]
Add indexes on the game_id field.

22 months agoUpdate the refresh script for active servers. top-servers
Ant Zucaro [Wed, 8 Nov 2017 02:19:21 +0000 (21:19 -0500)]
Update the refresh script for active servers.

22 months agoModify the sum type of the active servers MV.
Ant Zucaro [Wed, 8 Nov 2017 02:19:04 +0000 (21:19 -0500)]
Modify the sum type of the active servers MV.

23 months agoMerge branch 'frag-matrix'
Ant Zucaro [Tue, 17 Oct 2017 00:02:58 +0000 (20:02 -0400)]
Merge branch 'frag-matrix'

23 months agoUse the plain JSON type, not JSONB due to PG9.3.
Ant Zucaro [Tue, 17 Oct 2017 00:02:45 +0000 (20:02 -0400)]
Use the plain JSON type, not JSONB due to PG9.3.

23 months agoMerge pull request #3 from antzucaro/frag-matrix
Ant Zucaro [Mon, 16 Oct 2017 23:48:26 +0000 (19:48 -0400)]
Merge pull request #3 from antzucaro/frag-matrix

Add a frag matrix table.

23 months agoPartition the frag matrix table. frag-matrix
Ant Zucaro [Wed, 4 Oct 2017 00:16:41 +0000 (20:16 -0400)]
Partition the frag matrix table.

23 months agoAdd the table to the build.
Ant Zucaro [Sat, 30 Sep 2017 14:23:24 +0000 (10:23 -0400)]
Add the table to the build.

23 months agoInitial version of the player_game_frag_matrix table.
Ant Zucaro [Sat, 30 Sep 2017 14:21:08 +0000 (10:21 -0400)]
Initial version of the player_game_frag_matrix table.

2 years agoMake mention of needing superuser for drop & load, note cron scripts and SQLs.
Ant Zucaro [Sat, 26 Aug 2017 15:03:11 +0000 (11:03 -0400)]
Make mention of needing superuser for drop & load, note cron scripts and SQLs.

2 years agoAdd a pl/pgsql block to update the player_agg_stats_mv table.
Ant Zucaro [Sat, 13 May 2017 12:46:42 +0000 (08:46 -0400)]
Add a pl/pgsql block to update the player_agg_stats_mv table.

2 years agoRemove the trailing comma.
Ant Zucaro [Sat, 13 May 2017 12:20:53 +0000 (08:20 -0400)]
Remove the trailing comma.

2 years agoAdd the aggregate table to the build.
Ant Zucaro [Fri, 12 May 2017 23:31:16 +0000 (19:31 -0400)]
Add the aggregate table to the build.

2 years agoAdd a new 'mv' table for player aggregate stats.
Ant Zucaro [Fri, 12 May 2017 23:29:07 +0000 (19:29 -0400)]
Add a new 'mv' table for player aggregate stats.

2 years agoAdd category to player_ranks and its history.
Ant Zucaro [Sun, 30 Apr 2017 12:37:34 +0000 (08:37 -0400)]
Add category to player_ranks and its history.

2 years agoAdd elo categories to several tables.
Ant Zucaro [Sun, 30 Apr 2017 12:37:09 +0000 (08:37 -0400)]
Add elo categories to several tables.

2 years agoAdd a SQL script to clean the DB.
Ant Zucaro [Sat, 22 Apr 2017 20:44:25 +0000 (16:44 -0400)]
Add a SQL script to clean the DB.

2 years agoAdd some additional IP address padding.
Ant Zucaro [Mon, 3 Apr 2017 00:23:33 +0000 (20:23 -0400)]
Add some additional IP address padding.

2 years agoAdd a script to create a data snapshot of the DB.
Ant Zucaro [Thu, 19 Jan 2017 21:51:07 +0000 (16:51 -0500)]
Add a script to create a data snapshot of the DB.

2 years agoInsert a logging record to the merged_server table after the merge is done.
Ant Zucaro [Fri, 25 Nov 2016 21:17:49 +0000 (16:17 -0500)]
Insert a logging record to the merged_server table after the merge is done.

2 years agoAdd the new table to the build script.
Ant Zucaro [Fri, 25 Nov 2016 21:16:37 +0000 (16:16 -0500)]
Add the new table to the build script.

2 years agoAdd a log table for merged servers.
Ant Zucaro [Fri, 25 Nov 2016 21:14:09 +0000 (16:14 -0500)]
Add a log table for merged servers.

2 years agoOn second thought, let XonStat handle the attribute updates.
Ant Zucaro [Fri, 25 Nov 2016 15:04:41 +0000 (10:04 -0500)]
On second thought, let XonStat handle the attribute updates.

2 years agoAdd a merge_servers function.
Ant Zucaro [Fri, 25 Nov 2016 14:15:18 +0000 (09:15 -0500)]
Add a merge_servers function.

2 years agoAdd an Elo indicator for turning off processing at the server scope.
Ant Zucaro [Fri, 11 Nov 2016 01:45:42 +0000 (20:45 -0500)]
Add an Elo indicator for turning off processing at the server scope.

3 years agoAdd player_medals to the build.
Ant Zucaro [Tue, 29 Mar 2016 01:09:24 +0000 (21:09 -0400)]
Add player_medals to the build.

3 years agoAdd player_medals table.
Ant Zucaro [Tue, 29 Mar 2016 01:09:03 +0000 (21:09 -0400)]
Add player_medals table.

3 years agoFix typos.
Ant Zucaro [Thu, 17 Mar 2016 00:23:18 +0000 (20:23 -0400)]
Fix typos.

3 years agoGive the active MVs primary keys.
Ant Zucaro [Thu, 4 Feb 2016 23:03:39 +0000 (18:03 -0500)]
Give the active MVs primary keys.

3 years agoAdd active maps MV to the build.
Ant Zucaro [Tue, 2 Feb 2016 01:51:04 +0000 (20:51 -0500)]
Add active maps MV to the build.

3 years agoAdd a script to refresh active maps.
Ant Zucaro [Tue, 2 Feb 2016 01:50:22 +0000 (20:50 -0500)]
Add a script to refresh active maps.

3 years agoAdd an MV for active maps.
Ant Zucaro [Tue, 2 Feb 2016 01:49:27 +0000 (20:49 -0500)]
Add an MV for active maps.

3 years agoAdd active servers MV to the build.
Ant Zucaro [Sat, 30 Jan 2016 19:54:27 +0000 (14:54 -0500)]
Add active servers MV to the build.

3 years agoAdd a script to refresh active servers.
Ant Zucaro [Sat, 30 Jan 2016 19:53:53 +0000 (14:53 -0500)]
Add a script to refresh active servers.

3 years agoAdd an MV for active servers.
Ant Zucaro [Sat, 30 Jan 2016 19:52:50 +0000 (14:52 -0500)]
Add an MV for active servers.

3 years agoUnify permissions on all the table scripts.
Ant Zucaro [Sat, 30 Jan 2016 19:50:05 +0000 (14:50 -0500)]
Unify permissions on all the table scripts.

3 years agoAdd active players MV to the build.
Ant Zucaro [Sat, 30 Jan 2016 19:49:14 +0000 (14:49 -0500)]
Add active players MV to the build.

3 years agoAdd script to refresh the active players MV.
Ant Zucaro [Sat, 30 Jan 2016 19:48:17 +0000 (14:48 -0500)]
Add script to refresh the active players MV.

3 years agoAdd an MV for active players.
Ant Zucaro [Sat, 30 Jan 2016 19:47:30 +0000 (14:47 -0500)]
Add an MV for active players.

3 years agoAdd script to refresh summary stats.
Ant Zucaro [Fri, 29 Jan 2016 03:14:05 +0000 (22:14 -0500)]
Add script to refresh summary stats.

3 years agoAdd a create_dt to summary stats.
Ant Zucaro [Fri, 29 Jan 2016 03:13:04 +0000 (22:13 -0500)]
Add a create_dt to summary stats.

3 years agoAdd a summary stats "materialized view".
Ant Zucaro [Fri, 29 Jan 2016 03:11:18 +0000 (22:11 -0500)]
Add a summary stats "materialized view".

3 years agoConsolidate indexes for the games table.
Ant Zucaro [Tue, 26 Jan 2016 14:42:25 +0000 (09:42 -0500)]
Consolidate indexes for the games table.

3 years agoExclude rankings for those at the Elo floor.
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.

4 years agoUpdate the unique key on the player captimes table.
Ant Zucaro [Sat, 13 Jun 2015 13:18:47 +0000 (09:18 -0400)]
Update the unique key on the player captimes table.

4 years agoThe mod is 64 characters, not 30.
Ant Zucaro [Sat, 13 Jun 2015 12:39:58 +0000 (08:39 -0400)]
The mod is 64 characters, not 30.

4 years agoAdd the mod name to the captimes table to somewhat distinguish different methods...
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.

4 years agoAdd a field to track the absolute number of impure cvar changes.
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.

4 years agoAdd the Arc to the weapons table.
Ant Zucaro [Wed, 22 Apr 2015 00:54:15 +0000 (20:54 -0400)]
Add the Arc to the weapons table.

4 years agoAdd primary keys using our naming scheme.
Ant Zucaro [Sun, 11 Jan 2015 17:30:05 +0000 (12:30 -0500)]
Add primary keys using our naming scheme.

4 years agoCreate a game_id index, and put the gin(players) index into gen_partitions.shl too...
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.

4 years agoThe games table gets a gin index on players.
Ant Zucaro [Wed, 24 Dec 2014 18:28:59 +0000 (13:28 -0500)]
The games table gets a gin index on players.

4 years agoStore a list of player_ids in the games table.
Ant Zucaro [Tue, 16 Dec 2014 02:31:00 +0000 (21:31 -0500)]
Store a list of player_ids in the games table.

4 years agoAdd new weapons to the initial data set.
Ant Zucaro [Sat, 13 Dec 2014 03:57:22 +0000 (22:57 -0500)]
Add new weapons to the initial data set.

4 years agoPartition tables out to 2020, remove old ones.
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.

4 years agoAdd team_game_stats to the mix.
Ant Zucaro [Sun, 30 Nov 2014 13:22:47 +0000 (08:22 -0500)]
Add team_game_stats to the mix.

4 years agoAdd indexes. Rename to reflect its purpose.
Ant Zucaro [Wed, 19 Nov 2014 02:17:01 +0000 (21:17 -0500)]
Add indexes. Rename to reflect its purpose.

4 years agoAdd table definitions.
Ant Zucaro [Tue, 18 Nov 2014 03:16:40 +0000 (22:16 -0500)]
Add table definitions.

4 years agoComplete trigger definition.
Ant Zucaro [Tue, 18 Nov 2014 02:39:48 +0000 (21:39 -0500)]
Complete trigger definition.

4 years agoRule generator for partitions. Incomplete.
Ant Zucaro [Tue, 18 Nov 2014 02:21:03 +0000 (21:21 -0500)]
Rule generator for partitions. Incomplete.

5 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Sat, 12 Jul 2014 21:11:07 +0000 (17:11 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

5 years agoTake the greatest Elo per gametype in merges.
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.

5 years agoAdd groups table for admin functionality.
Ant Zucaro [Mon, 16 Jun 2014 01:05:07 +0000 (21:05 -0400)]
Add groups table for admin functionality.

5 years agoAdd email address to the players table (for admins only).
Ant Zucaro [Fri, 13 Jun 2014 12:24:13 +0000 (08:24 -0400)]
Add email address to the players table (for admins only).

5 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Wed, 28 May 2014 00:36:41 +0000 (20:36 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

5 years agoAdd script to purge anticheat logs.
Ant Zucaro [Wed, 28 May 2014 00:36:27 +0000 (20:36 -0400)]
Add script to purge anticheat logs.

5 years agoAdd an anticheat log table.
Ant Zucaro [Wed, 28 May 2014 00:32:16 +0000 (20:32 -0400)]
Add an anticheat log table.

5 years agoPlaying one Elo game type in the past 30 days will prevent rot.
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.

5 years agoFix date range for q4 partition.
Ant Zucaro [Wed, 2 Oct 2013 00:44:38 +0000 (20:44 -0400)]
Fix date range for q4 partition.

6 years agoDamn off-by-one errors. *shakes fist*
antzucaro [Sat, 17 Aug 2013 04:34:33 +0000 (00:34 -0400)]
Damn off-by-one errors. *shakes fist*

6 years agoAdd Elo rot analysis query.
antzucaro [Sat, 17 Aug 2013 04:12:14 +0000 (00:12 -0400)]
Add Elo rot analysis query.

6 years agoUse a new Elo rot scheme.
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.

6 years agoAdd drop and load convenience script.
Ant Zucaro [Sun, 11 Aug 2013 14:01:11 +0000 (10:01 -0400)]
Add drop and load convenience script.

6 years agoVarious typos on Q4.
Ant Zucaro [Thu, 23 May 2013 02:05:53 +0000 (22:05 -0400)]
Various typos on Q4.

6 years agoPartition player weapon stats through 2014.
Ant Zucaro [Thu, 23 May 2013 02:05:06 +0000 (22:05 -0400)]
Partition player weapon stats through 2014.

6 years agoPartition player game stats through 2014.
Ant Zucaro [Thu, 23 May 2013 01:42:56 +0000 (21:42 -0400)]
Partition player game stats through 2014.

6 years agoPartition games through 2014.
Ant Zucaro [Thu, 23 May 2013 01:24:36 +0000 (21:24 -0400)]
Partition games through 2014.

6 years agoYou know what? We don't need tables for the past!
Ant Zucaro [Thu, 23 May 2013 01:11:39 +0000 (21:11 -0400)]
You know what? We don't need tables for the past!

6 years agoConfigure partitions through 2014.
Ant Zucaro [Thu, 23 May 2013 01:05:02 +0000 (21:05 -0400)]
Configure partitions through 2014.

6 years agoAdd rounds, caps fields.
Ant Zucaro [Thu, 23 May 2013 00:58:09 +0000 (20:58 -0400)]
Add rounds, caps fields.

6 years agoAdd team scores table. Partition till 2015.
Ant Zucaro [Sun, 21 Apr 2013 20:36:53 +0000 (16:36 -0400)]
Add team scores table. Partition till 2015.

6 years agoAdd port to servers table
Ant Zucaro [Tue, 5 Feb 2013 08:26:43 +0000 (03:26 -0500)]
Add port to servers table

6 years agoAdd mod string to games table
Ant Zucaro [Tue, 5 Feb 2013 08:25:09 +0000 (03:25 -0500)]
Add mod string to games table

6 years agoIdentify games with bad ranks.
Ant Zucaro [Fri, 1 Feb 2013 21:21:26 +0000 (16:21 -0500)]
Identify games with bad ranks.

6 years agoModify column structure for better reuse.
Ant Zucaro [Fri, 25 Jan 2013 03:28:22 +0000 (22:28 -0500)]
Modify column structure for better reuse.

6 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Wed, 16 Jan 2013 02:28:35 +0000 (21:28 -0500)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

6 years agoAdd teamrank and scoreboardpos fields.
Ant Zucaro [Wed, 16 Jan 2013 02:28:12 +0000 (21:28 -0500)]
Add teamrank and scoreboardpos fields.

6 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
antzucaro [Wed, 2 Jan 2013 16:47:59 +0000 (11:47 -0500)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

6 years agoUse greatest instead of least.
antzucaro [Wed, 2 Jan 2013 16:47:42 +0000 (11:47 -0500)]
Use greatest instead of least.

6 years agoAdd win ratio query
Ant Zucaro [Mon, 31 Dec 2012 03:45:33 +0000 (22:45 -0500)]
Add win ratio query

6 years agoAdd player retention query
Ant Zucaro [Mon, 31 Dec 2012 03:41:11 +0000 (22:41 -0500)]
Add player retention query

6 years agoAdd primary key to summary stats. SQLAlchemy requires it.
Ant Zucaro [Sat, 15 Dec 2012 15:17:44 +0000 (10:17 -0500)]
Add primary key to summary stats. SQLAlchemy requires it.

6 years agoRemove the commit. It is redundant.
Ant Zucaro [Sat, 15 Dec 2012 15:07:08 +0000 (10:07 -0500)]
Remove the commit. It is redundant.

6 years agoAdd a small script to update the summary stats table.
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.

6 years agoAdd the summary stats table to the build.
Ant Zucaro [Sat, 15 Dec 2012 14:48:26 +0000 (09:48 -0500)]
Add the summary stats table to the build.

6 years agoAdd support for summary statistics.
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.