DIR : /home/kozerus/public_html/go/dgs0/scripts/updates/database_changes_1_0_16_to_1_0_17.mysql

/home/kozerus/public_html/go/dgs0/scripts/updates

# Release DGS 1.0.17 at ??-???-2014 from MAIN-branch:
#
# IMPORTANT NOTE:
# * Do a full backup of all data in the database before performing (these) changes !!
#   Note current space (phpmyadmin or 'show table status') for later comparison.
# * Do NOT simply execute these SQL-statements as there are DROP-tables,
#   some to-be-replaced templates and non-SQL-statements in it !!
# * enable mysql-client warnings with: \W
#
# * Recommendation: Import it section by section manually taking comments into account !
#

-- added index for loading next-round tournament-participants
ALTER TABLE TournamentParticipant
   ADD KEY tidNextRound (tid,NextRound) ;


-- deactivate challenges-out column in ladder-view as default (to reduce server-load)
UPDATE ConfigPages
   SET ColumnsTournamentLadderView = ColumnsTournamentLadderView & ~0x8000 ;


-- added table to store point-configuration for round-robin-tournaments
CREATE TABLE TournamentPoints (
  tid int NOT NULL,
  PointsType enum('SIMPLE','HAHN') NOT NULL default 'SIMPLE',
  Flags tinyint unsigned NOT NULL default '1',
  PointsWon tinyint NOT NULL default '2',
  PointsLost tinyint NOT NULL default '0',
  PointsDraw tinyint NOT NULL default '1',
  PointsBye smallint NOT NULL default '2',
  PointsNoResult smallint NOT NULL default '1',
  ScoreBlock tinyint unsigned NOT NULL default '10',
  MaxPoints smallint unsigned NOT NULL default '10',
  PointsResignation smallint NOT NULL default '10',
  PointsTimeout smallint NOT NULL default '10',
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  ChangedBy varchar(54) NOT NULL default '',
  PRIMARY KEY (tid)
) ENGINE=MyISAM ;


-- renamed TournamentPoints.PointsBye to PointsForfeit
ALTER TABLE TournamentPoints
   CHANGE PointsBye PointsForfeit smallint NOT NULL default '2' ;


-- check if user needs re-do search-profiles for finished-games-lists, because score-filter changed:
--    if the query-result is not empty AND parameter-value X of 'fsf10...=X' is >= 7,
--    then user needs to re-do the corresponding search-profile
SELECT * FROM Profiles WHERE Type BETWEEN 11 AND 13 AND Text LIKE '%fsf10%' ;


-- change Games.Flags from SET() into integer-flags
ALTER TABLE Games
   DROP KEY Flags,
   CHANGE Flags OldFlags set('Ko','HiddenMsg','AdmResult','TGDetached','AttachedSgf') NOT NULL default '',
   ADD Flags smallint unsigned NOT NULL DEFAULT '0' AFTER OldFlags ;

UPDATE Games
   SET Flags=OldFlags ;

ALTER TABLE Games
   ADD KEY Flags (Flags),
   DROP COLUMN OldFlags ;


-- added tracking for number of consecutive-wins in ladder-tournament
ALTER TABLE TournamentLadder
   ADD SeqWins smallint unsigned NOT NULL DEFAULT '0',
   ADD SeqWinsBest smallint unsigned NOT NULL DEFAULT '0' ;

-- execute fix-script for all ladder-tournaments (use the following query to identify these)
-- to fix current state of consecutive-wins:
--    'scripts/fix_ladder_seq_wins.php'
SELECT ID AS tid FROM Tournament WHERE Type='LADDER' ;


-- extended tournament-result
ALTER TABLE TournamentResult
   MODIFY Round tinyint unsigned NOT NULL default '1' AFTER Type,
   ADD Result mediumint NOT NULL default '0' AFTER EndTime,
   ADD Comment varchar(128) NOT NULL default '',
   ADD Note varchar(255) NOT NULL default '' ;


-- cleanup: remove db-field calculated from other fields
ALTER TABLE TournamentResult
   DROP COLUMN RankKept ;


-- cleanup: remove unnecessary db-index
ALTER TABLE TournamentResult
   DROP KEY uid ;


-- execute tournament-consistency-script to fix TournamentParticipant.Finished for annulled tournament-games
--    'scripts/tournament_consistency.php'


-- added restrictions to allow user to start in higher rounds
ALTER TABLE TournamentProperties
   ADD MinRatingStartRound smallint NOT NULL default '-9999' AFTER MaxParticipants,
   ADD MaxStartRound tinyint unsigned NOT NULL default '1' AFTER MaxParticipants ;


-- added min-threshold to cross by ladder-users best-consecutive-wins to enter tournament-result
ALTER TABLE TournamentLadderProps
   ADD SeqWinsThreshold tinyint unsigned NOT NULL default '0' ;

-- added index for update on consecutive-wins in tournament-results
ALTER TABLE TournamentResult
   ADD KEY rid (rid) ;