xonotic/xonstatdb.git
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.

6 years agoPut a blurb about the misc functions into the README.
Ant Zucaro [Sun, 2 Dec 2012 19:43:36 +0000 (14:43 -0500)]
Put a blurb about the misc functions into the README.

6 years agoAdd elo rot script.
Ant Zucaro [Sun, 2 Dec 2012 19:30:49 +0000 (14:30 -0500)]
Add elo rot script.

6 years agoAdd an update_dt to the player_elos table.
Ant Zucaro [Sun, 2 Dec 2012 13:03:23 +0000 (08:03 -0500)]
Add an update_dt to the player_elos table.

6 years agoFix merge.
Ant Zucaro [Mon, 29 Oct 2012 16:03:08 +0000 (12:03 -0400)]
Fix merge.

6 years agoAdd captimes table to the build.
Ant Zucaro [Mon, 29 Oct 2012 15:55:11 +0000 (11:55 -0400)]
Add captimes table to the build.

6 years agoAdd indexes to the game and weapon stats tables. Store captimes.
Ant Zucaro [Mon, 29 Oct 2012 15:53:47 +0000 (11:53 -0400)]
Add indexes to the game and weapon stats tables. Store captimes.

7 years agoMerge branch 'master' of ssh://push.git.xonotic.org/xonstatdb
Ant Zucaro [Fri, 24 Aug 2012 13:59:56 +0000 (09:59 -0400)]
Merge branch 'master' of ssh://push.git.xonotic.org/xonstatdb

7 years agoIndex game_id and player_id for performance (and badges).
Ant Zucaro [Fri, 24 Aug 2012 13:55:35 +0000 (09:55 -0400)]
Index game_id and player_id for performance (and badges).

7 years agoAdd players to ladder associative table, with ranks.
Ant Zucaro [Tue, 21 Aug 2012 21:40:16 +0000 (17:40 -0400)]
Add players to ladder associative table, with ranks.

7 years agoChange title to name.
Ant Zucaro [Tue, 21 Aug 2012 21:30:23 +0000 (17:30 -0400)]
Change title to name.

7 years agoAdd the ladder code table.
Ant Zucaro [Tue, 21 Aug 2012 01:28:58 +0000 (21:28 -0400)]
Add the ladder code table.

7 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Mon, 11 Jun 2012 11:03:42 +0000 (07:03 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

7 years agoAdd an active indicator to player_elo.
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.

7 years agoMerge pull request #1 from nyov/master
antzucaro [Fri, 27 Apr 2012 13:52:13 +0000 (06:52 -0700)]
Merge pull request #1 from nyov/master

updated README

7 years agoModified Readme
nyov [Fri, 27 Apr 2012 02:05:55 +0000 (04:05 +0200)]
Modified Readme

7 years agoDefault games to 1 in another place.
Ant Zucaro [Sat, 21 Apr 2012 20:30:37 +0000 (16:30 -0400)]
Default games to 1 in another place.

7 years agoDefault total games to 1 and also print who we are merging.
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.

7 years agoDo a weighted average instead of picking the max.
Ant Zucaro [Sat, 21 Apr 2012 20:00:27 +0000 (16:00 -0400)]
Do a weighted average instead of picking the max.

7 years agoDamn you, pgadmin!
Ant Zucaro [Tue, 20 Mar 2012 12:53:04 +0000 (08:53 -0400)]
Damn you, pgadmin!

7 years agoAdding yo dawg, just for the hell of it.
Ant Zucaro [Tue, 20 Mar 2012 12:44:18 +0000 (08:44 -0400)]
Adding yo dawg, just for the hell of it.

7 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Tue, 20 Mar 2012 12:43:12 +0000 (08:43 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

7 years agoA simple dashboard report for providing high level stats...about stats!
Ant Zucaro [Tue, 20 Mar 2012 12:41:40 +0000 (08:41 -0400)]
A simple dashboard report for providing high level stats...about stats!

7 years agoGet rid of bad header chars in the file. All else is the same.
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.

7 years agoAdd advanced elo checks in the merge. We want an optimistic view of the player, so...
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.

7 years agoFunction to merge two players.
Ant Zucaro [Wed, 29 Feb 2012 12:05:05 +0000 (07:05 -0500)]
Function to merge two players.

7 years agoSupport deactivating players.
Ant Zucaro [Mon, 20 Feb 2012 16:42:21 +0000 (11:42 -0500)]
Support deactivating players.

7 years agoChange game type codes to match new gametypes abbreviations.
Ant Zucaro [Mon, 20 Feb 2012 15:25:35 +0000 (10:25 -0500)]
Change game type codes to match new gametypes abbreviations.

7 years agoAdd rank history to the build.
Ant Zucaro [Sun, 22 Jan 2012 17:36:34 +0000 (12:36 -0500)]
Add rank history to the build.

7 years agoWhoops, add the 32 game req.
Ant Zucaro [Sun, 22 Jan 2012 17:36:21 +0000 (12:36 -0500)]
Whoops, add the 32 game req.

7 years agoSave rank history and also do not generate ranks if < 32 games played for the game...
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.

7 years agoAdd rank history table.
Ant Zucaro [Sun, 22 Jan 2012 17:31:50 +0000 (12:31 -0500)]
Add rank history table.

7 years agoAdd elos and ranks to the build file.
Ant Zucaro [Sun, 22 Jan 2012 04:08:33 +0000 (23:08 -0500)]
Add elos and ranks to the build file.

7 years agoFix player_ranks.
Ant Zucaro [Sun, 22 Jan 2012 04:07:24 +0000 (23:07 -0500)]
Fix player_ranks.

7 years agoSmall transaction to regenerate ranks.
Ant Zucaro [Sun, 22 Jan 2012 03:54:41 +0000 (22:54 -0500)]
Small transaction to regenerate ranks.

7 years agoAdd ranks table for batch purposes.
Ant Zucaro [Sun, 22 Jan 2012 03:49:43 +0000 (22:49 -0500)]
Add ranks table for batch purposes.

7 years agoAdd player Elo support.
Ant Zucaro [Sat, 31 Dec 2011 22:46:11 +0000 (17:46 -0500)]
Add player Elo support.

7 years agoFix unique key definition on games.
Ant Zucaro [Thu, 15 Dec 2011 02:55:25 +0000 (21:55 -0500)]
Fix unique key definition on games.

7 years agoAdd the duel game type.
Ant Zucaro [Thu, 15 Dec 2011 02:41:23 +0000 (21:41 -0500)]
Add the duel game type.

7 years agoThe match_id is now a varchar.
Ant Zucaro [Tue, 13 Dec 2011 00:45:20 +0000 (19:45 -0500)]
The match_id is now a varchar.

7 years agoAdd a unique constraint on server_id/match_id.
Ant Zucaro [Mon, 5 Dec 2011 17:15:31 +0000 (12:15 -0500)]
Add a unique constraint on server_id/match_id.

7 years agoAdd a match_id to each games row.
Ant Zucaro [Thu, 1 Dec 2011 17:00:34 +0000 (12:00 -0500)]
Add a match_id to each games row.

7 years agoRevert the functional index on players table. It won't be used with LIKE query anyway.
Ant Zucaro [Sun, 20 Nov 2011 21:59:06 +0000 (16:59 -0500)]
Revert the functional index on players table. It won't be used with LIKE query anyway.

7 years agoRevert the functional index on stripped_nick. Searches won't benefit from it anyway.
Ant Zucaro [Sun, 20 Nov 2011 21:58:16 +0000 (16:58 -0500)]
Revert the functional index on stripped_nick. Searches won't benefit from it anyway.

7 years agoAdd stripped_nick and indexes for it.
Ant Zucaro [Sun, 20 Nov 2011 02:54:54 +0000 (21:54 -0500)]
Add stripped_nick and indexes for it.

7 years agoAdd default to the create_dt of player game stats.
Ant Zucaro [Thu, 17 Nov 2011 15:15:40 +0000 (10:15 -0500)]
Add default to the create_dt of player game stats.

7 years agoUse UTC timestamps instead of local timestamps.
Ant Zucaro [Wed, 16 Nov 2011 20:22:49 +0000 (15:22 -0500)]
Use UTC timestamps instead of local timestamps.

7 years agoChange to unix encoding.
antzucaro [Wed, 16 Nov 2011 01:01:26 +0000 (20:01 -0500)]
Change to unix encoding.

7 years agoAdd stripped nick to the players table to support future search implementation.
antzucaro [Wed, 16 Nov 2011 01:00:05 +0000 (20:00 -0500)]
Add stripped nick to the players table to support future search implementation.

7 years agoFix the triggers for partitioning.
Ant Zucaro [Wed, 9 Nov 2011 23:15:55 +0000 (18:15 -0500)]
Fix the triggers for partitioning.

7 years agoGet rid of the hashkeys table. It is not needed.
Ant Zucaro [Mon, 31 Oct 2011 01:05:01 +0000 (21:05 -0400)]
Get rid of the hashkeys table. It is not needed.

7 years agoMake the server hashkey an attribute instead of its own table.
Ant Zucaro [Mon, 31 Oct 2011 00:44:34 +0000 (20:44 -0400)]
Make the server hashkey an attribute instead of its own table.

7 years agoEnsure that the hashkeys for player and server are unique, as they are used for lookup.
Ant Zucaro [Fri, 28 Oct 2011 00:33:43 +0000 (20:33 -0400)]
Ensure that the hashkeys for player and server are unique, as they are used for lookup.

7 years agoAdd server hashkey table
Ant Zucaro [Tue, 25 Oct 2011 10:40:54 +0000 (06:40 -0400)]
Add server hashkey table

7 years agoClean up cd_mutator stuff.
Ant Zucaro [Tue, 25 Oct 2011 10:40:36 +0000 (06:40 -0400)]
Clean up cd_mutator stuff.

7 years agoWe no longer need the mutator table or the game types each map supports. Commenting...
Ant Zucaro [Fri, 21 Oct 2011 02:57:24 +0000 (22:57 -0400)]
We no longer need the mutator table or the game types each map supports. Commenting out those definitions

8 years agoAllow up to 128 characters in the player nick
Ant Zucaro [Mon, 17 Oct 2011 15:25:04 +0000 (11:25 -0400)]
Allow up to 128 characters in the player nick

8 years agoAdd nick history table.
antzucaro [Mon, 10 Oct 2011 19:13:35 +0000 (15:13 -0400)]
Add nick history table.

8 years agoRemove unique constraint on pgstats that prevents multiple bot stats.
antzucaro [Mon, 10 Oct 2011 19:06:42 +0000 (15:06 -0400)]
Remove unique constraint on pgstats that prevents multiple bot stats.

8 years agoMake directory location more clear.
antzucaro [Mon, 10 Oct 2011 17:23:54 +0000 (13:23 -0400)]
Make directory location more clear.

8 years agoFix typo on language command.
antzucaro [Mon, 10 Oct 2011 17:22:10 +0000 (13:22 -0400)]
Fix typo on language command.

8 years agoMake schema creation clear. Create pgplsql language.
Ant Zucaro [Thu, 6 Oct 2011 17:06:14 +0000 (13:06 -0400)]
Make schema creation clear. Create pgplsql language.

8 years agoAdd triggers for partitioning functionality.
Ant Zucaro [Thu, 7 Jul 2011 20:04:29 +0000 (16:04 -0400)]
Add triggers for partitioning functionality.

8 years agoRemove stat type column, as we only care about scoreboard stats.
Ant Zucaro [Thu, 7 Jul 2011 19:44:36 +0000 (15:44 -0400)]
Remove stat type column, as we only care about scoreboard stats.

8 years agoFix camping rifle short name
Ant Zucaro [Thu, 30 Jun 2011 02:56:49 +0000 (22:56 -0400)]
Fix camping rifle short name

8 years agoOnce again use the correct weapon names.
Ant Zucaro [Sat, 21 May 2011 02:45:26 +0000 (22:45 -0400)]
Once again use the correct weapon names.

8 years agoMerge branch 'master' of github.com:antzucaro/xonstatdb
Ant Zucaro [Fri, 20 May 2011 02:03:12 +0000 (22:03 -0400)]
Merge branch 'master' of github.com:antzucaro/xonstatdb

8 years agoRemove nick from weapon stats and replace it w/ pgstat id. Remove corresponding const...
Ant Zucaro [Fri, 20 May 2011 02:02:48 +0000 (22:02 -0400)]
Remove nick from weapon stats and replace it w/ pgstat id. Remove corresponding constraint and replace it with an FK to player_game_stats.

8 years agoMake weapon codes match w/ QCSRC weapon codes.
Ant Zucaro [Wed, 18 May 2011 16:24:00 +0000 (12:24 -0400)]
Make weapon codes match w/ QCSRC weapon codes.

8 years agoAdd nick to player_weapon_stats to give anonymous players uniqueness (they always...
Ant Zucaro [Mon, 16 May 2011 03:10:45 +0000 (23:10 -0400)]
Add nick to player_weapon_stats to give anonymous players uniqueness (they always have player_id of 2).

8 years agoAdd alivetime and rank to player_game_stats table.
azucaro [Thu, 28 Apr 2011 12:35:34 +0000 (08:35 -0400)]
Add alivetime and rank to player_game_stats table.

8 years agoMisc fixes.
Ant Zucaro [Thu, 28 Apr 2011 02:43:39 +0000 (22:43 -0400)]
Misc fixes.

8 years agoFix weapon_cd in child table.
Ant Zucaro [Thu, 28 Apr 2011 01:58:31 +0000 (21:58 -0400)]
Fix weapon_cd in child table.

8 years agoAdd public key to servers.
Ant Zucaro [Thu, 28 Apr 2011 00:18:20 +0000 (20:18 -0400)]
Add public key to servers.

8 years agoAdd hit and fired to player_weapon_stats.
Ant Zucaro [Wed, 27 Apr 2011 22:40:42 +0000 (18:40 -0400)]
Add hit and fired to player_weapon_stats.

8 years agoAdd revision to servers table.
Ant Zucaro [Wed, 27 Apr 2011 01:31:40 +0000 (21:31 -0400)]
Add revision to servers table.

8 years agoAlign weapon codes with what is emitted in the stats code.
Ant Zucaro [Wed, 27 Apr 2011 01:25:30 +0000 (21:25 -0400)]
Align weapon codes with what is emitted in the stats code.

8 years agoAdd plural to alter for map_game_types.
Ant Zucaro [Sat, 23 Apr 2011 16:01:10 +0000 (12:01 -0400)]
Add plural to alter for map_game_types.

8 years agoAdd default to servers table.
Ant Zucaro [Wed, 9 Mar 2011 22:25:58 +0000 (17:25 -0500)]
Add default to servers table.

8 years agoUpdate README
Ant Zucaro [Sun, 6 Mar 2011 21:21:01 +0000 (16:21 -0500)]
Update README

8 years agoGet rid of time zones. We do not need them.
Ant Zucaro [Sun, 6 Mar 2011 21:09:39 +0000 (16:09 -0500)]
Get rid of time zones. We do not need them.

8 years agoChange from integer to serial where I forgot on servers.
Ant Zucaro [Sun, 6 Mar 2011 21:05:35 +0000 (16:05 -0500)]
Change from integer to serial where I forgot on servers.

8 years agoChange datatypes from integer to serial where I forgot.
Ant Zucaro [Sun, 6 Mar 2011 21:04:32 +0000 (16:04 -0500)]
Change datatypes from integer to serial where I forgot.

8 years agoAdd ignore file.
Ant Zucaro [Sun, 6 Mar 2011 21:02:15 +0000 (16:02 -0500)]
Add ignore file.

8 years agoChange player_*_stats to bigserial - there is going to be a lot of them.
Ant Zucaro [Sun, 6 Mar 2011 21:01:03 +0000 (16:01 -0500)]
Change player_*_stats to bigserial - there is going to be a lot of them.

8 years agoChange foreign keys from serial to integer.
Ant Zucaro [Sun, 6 Mar 2011 21:00:14 +0000 (16:00 -0500)]
Change foreign keys from serial to integer.

8 years agoInitial commit - migration of xonstatdb from MySQL to PostgreSQL.
Ant Zucaro [Sun, 6 Mar 2011 20:58:09 +0000 (15:58 -0500)]
Initial commit - migration of xonstatdb from MySQL to PostgreSQL.