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

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

# Release DGS 1.0.15 at 10-Jun-2012 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 !!
#   Comments ending with '-> CLEANUP' can be postponed for later; the others,
#   even if looking like postponable needs to be executed (those are marked
#   by '[mandatory']').
# * enable mysql-client warnings with: \W
#
# * Recommendation: Import it section by section manually taking comments into account !
#


###  NOTE: these are mandatory changes before release 1.0.15 can be done !!
###
###  ------ Synchronize table-structure of DGS-servers -----------------------------------
###         (live / SourceForget / local / test-remote)

-- [field-order] sync Players-table
ALTER TABLE Players
   MODIFY MayPostOnForum enum('N','Y','M') NOT NULL default 'Y' AFTER AdminNote,
   MODIFY Rating2 double default NULL AFTER Rating ;

-- [field-order] sync Waitingroom-table
ALTER TABLE Waitingroom
   MODIFY Handicap int(11) NOT NULL default '0' AFTER Komi ;


###  ------ Optimize table-indexes of DGS-servers -----------------------------------

-- [index] IDX Bio.uid (uid) : -> (uid,SortOrder) -- to accelerate bio-show and sort
ALTER TABLE Bio
   DROP INDEX uid ;
ALTER TABLE Bio
   ADD INDEX uid (uid,SortOrder) ;

-- [index] add Errorlog-indexes to be able to search for errors
-- [index] IDX Errorlog.Date
ALTER TABLE Errorlog
   ADD INDEX Date (Date) ;
-- [data] cleanup Errorlog (delete old portion of data, e.g. keep 6 months)
DELETE FROM Errorlog WHERE Date < NOW() - INTERVAL 180 DAY ;
-- [field-size] COL Errorlog.Message : text -> varchar(32)
ALTER TABLE Errorlog
   MODIFY Message varchar(32) NOT NULL ;
-- [index] IDX Errorlog.Message -> prefix-index Message(8)
ALTER TABLE Errorlog
   ADD INDEX Message (Message(8)) ;

-- [index] IDX Forumlog.Action -> prefix-index Action(4) for searches
ALTER TABLE Forumlog
   ADD INDEX Action (Action(4)) ;

-- [index] remove unused IDX Games.Maintime (Maintime)
-- [index] remove unused IDX Games.Byotime (Byotime)
ALTER TABLE Games
   DROP INDEX Maintime,
   DROP INDEX Byotime ;

-- [index] optimize MessageCorrespondents-index -> remove double-index 'Folder_nr(Folder_nr,uid)' (part 1)
ALTER TABLE MessageCorrespondents
   DROP INDEX Folder_nr ;
-- [field-size] COL Folders.Folder_nr : int -> tinyint + foreign-keys
-- [no-def] Folders.uid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Folders.Name varchar(40) NOT NULL default '' -> no default [ok]
ALTER TABLE Folders
   MODIFY Folder_nr tinyint NOT NULL default '0',
   MODIFY uid int NOT NULL,
   MODIFY Name varchar(40) NOT NULL ;
-- [field-size] COL MessageCorrespondents.Folder_nr : int -> tinyint (foreign-key of Folders.Folder_nr)
-- [no-def] MessageCorrespondents.Folder_nr default NULL -> no default [ok]
ALTER TABLE MessageCorrespondents
   MODIFY Folder_nr tinyint ;
-- [index] optimize MessageCorrespondents-index -> remove double-index 'Folder_nr(Folder_nr,uid)' (part 2)
ALTER TABLE MessageCorrespondents
   ADD INDEX Folder_nr (Folder_nr) ;


-- refactor Moves-table without ALTER-table in multiple steps, because table is so large:
-- [index] fix wrong Moves-index 'gid(gid,ID)' -> 'gid(gid,MoveNr)' (part 1)
-- [field-size] COL Moves.Stone : smallint -> tinyint unsigned
-- [field-size] COL Moves.PosX : smallint -> tinyint signed
-- [field-size] COL Moves.PosY : smallint -> tinyint signed
-- [not-null] Moves.MoveNr smallint(5) unsigned default NULL -> no default -> NOT NULL [ok]
-- [not-null] Moves.PosX smallint(6) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Moves.PosY smallint(6) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Moves.Hours smallint(5) unsigned default NULL -> default 0 -> NOT NULL [ok]
-- [no-def] Moves.gid int(11) NOT NULL default '0' -> no default [ok]

-- Create new Moves-table (part 1/5)
CREATE TABLE MovesNew (
   ID int NOT NULL auto_increment,
   gid int NOT NULL,
   MoveNr smallint unsigned NOT NULL,
   Stone tinyint unsigned NOT NULL default '0',
   PosX tinyint NOT NULL,
   PosY tinyint NOT NULL,
   Hours smallint unsigned NOT NULL,
   PRIMARY KEY (ID)
) ENGINE=MyISAM ;

-- Copy all data into new structure of Moves-table (part 2/5)
INSERT INTO MovesNew (ID,gid,MoveNr,Stone,PosX,PosY,Hours)
    SELECT ID,gid,MoveNr,Stone,PosX,PosY,Hours FROM Moves ;

-- Create indexes for new Moves-table (part 3/5)
ALTER TABLE MovesNew
   ADD INDEX gid (gid,MoveNr) ;

-- Replace old with new Moves-table (part 4/5)
RENAME TABLE Moves TO MovesOld ;
RENAME TABLE MovesNew TO Moves ;

-- Delete old Moves-table (part 5/5) - after size checks
-- SHOW TABLE STATUS LIKE 'Moves%' ;
-- DROP TABLE MovesOld ;

-- [index] IDX Ratinglog.gid -> replace index 'gid(gid,uid)' with gid only
ALTER TABLE Ratinglog
   DROP INDEX gid ;
ALTER TABLE Ratinglog
   ADD INDEX gid (gid);


###  ------ Cleanup unused tables/fields of DGS-servers -----------------------------

-- [unused] cleanup Messages-table (fields not longer used and not referenced) -> CLEANUP
-- [unused] remove unused COL Messages.To_ID
-- [unused] remove unused COL Messages.From_ID
ALTER TABLE Messages
   DROP COLUMN From_ID,
   DROP COLUMN To_ID ;

-- [unused] cleanup (only on) live-server DB (removing old games and forum tables), saving 370 MB -> CLEANUP
-- done in live-server-db [12-Dec-2010/JUG]
-- DROP TABLE Games2               --   0.2 MB
-- DROP TABLE dragondisc           --  88.2 MB
-- DROP TABLE dragondisc_bodies    -- 206.7 MB
-- DROP TABLE faqdisc              --   4.0 MB
-- DROP TABLE faqdisc_bodies       --  10.6 MB
-- DROP TABLE forums               --   0.1 MB
-- DROP TABLE godisc               --   7.0 MB
-- DROP TABLE godisc_bodies        --  17.3 MB
-- DROP TABLE news                 --   0.8 MB
-- DROP TABLE news_bodies          --   1.8 MB
-- DROP TABLE opponents            --   2.7 MB
-- DROP TABLE opponents_bodies     --   5.3 MB
-- DROP TABLE support              --   5.0 MB
-- DROP TABLE support_bodies       --  10.8 MB
-- DROP TABLE transl               --   2.9 MB
-- DROP TABLE transl_bodies        --   6.2 MB

-- [unused] cleanup unused RatingChange-table (maybe later)
-- [unused] note: referenced in 'include/rating.php#update_rating()', though not used any more (therefore removed since rev 1.101)
-- DROP TABLE RatingChange         --   0.7 MB


###  ------ Cleanup tables of DGS-servers [part 1] ----------------------------------
###         (Data types, NOT NULL, Defaults, Enums)

-- [no-def] Bio.uid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Bio.Category varchar(40) NOT NULL default '' -> no default [ok]
-- [field-size] COL Bio.SortOrder : int -> smallint unsigned
ALTER TABLE Bio
   MODIFY uid int NOT NULL,
   MODIFY Category varchar(40) NOT NULL,
   MODIFY SortOrder smallint unsigned NOT NULL default '0' ;

-- [field-size] COL Clock.ID : int -> smallint + foreign-keys
-- [no-def] Clock.ID int(11) NOT NULL default '0' -> no default [ok]
-- [not-null] Clock.Ticks int(11) default '0' -> NOT NULL [ok]
ALTER TABLE Clock
   MODIFY ID smallint NOT NULL,
   MODIFY Ticks int NOT NULL default '0' ;
-- [field-size] COL Games.ClockUsed : int -> smallint (foreign-key of Clock.ID)
ALTER TABLE Games
   MODIFY ClockUsed smallint NOT NULL default '0' ;
-- [field-size] COL Players.Nightstart : int -> smallint (foreign-key of Clock.ID)
-- [field-size] COL Players.ClockUsed : int -> smallint (foreign-key of Clock.ID)
ALTER TABLE Players
   MODIFY Nightstart smallint NOT NULL default '22',
   MODIFY ClockUsed smallint NOT NULL default '22' ;

-- [no-def] Contacts.uid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Contacts.cid int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE Contacts
   MODIFY uid int NOT NULL,
   MODIFY cid int NOT NULL ;

-- [field-size] COL FAQ.Level : int -> tinyint unsigned
-- [field-size] COL FAQ.SortOrder : int -> smallint unsigned
ALTER TABLE FAQ
   MODIFY Level tinyint unsigned NOT NULL default '0',
   MODIFY SortOrder smallint NOT NULL default '0' ;

-- [not-null] FAQlog.Question text -> NOT NULL [ok]
-- [not-null] FAQlog.Answer text -> NOT NULL [ok]
ALTER TABLE FAQlog
   MODIFY Question text NOT NULL,
   MODIFY Answer text NOT NULL ;

-- [no-def] Forumlog.User_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Forumlog.Thread_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Forumlog.Post_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Forumlog.Action varchar(40) NOT NULL default '' -> no default [ok]
ALTER TABLE Forumlog
   MODIFY User_ID int NOT NULL,
   MODIFY Thread_ID int NOT NULL,
   MODIFY Post_ID int NOT NULL,
   MODIFY Action varchar(40) NOT NULL ;

-- [field-size] COL GoDiagrams.Size : int -> tinyint unsigned
-- [field-size] COL GoDiagrams.View_Left : int -> tinyint
-- [field-size] COL GoDiagrams.View_Right : int -> tinyint
-- [field-size] COL GoDiagrams.View_Up : int -> tinyint
-- [field-size] COL GoDiagrams.View_Down : int -> tinyint
-- [not-null] GoDiagrams.Size int(11) default NULL -> NOT NULL [ok]
-- [not-null] GoDiagrams.View_Left int(11) default NULL -> default 0 -> NOT NULL [ok]
-- [not-null] GoDiagrams.View_Right int(11) default NULL -> default 0 -> NOT NULL [ok]
-- [not-null] GoDiagrams.View_Up int(11) default NULL -> default 0 -> NOT NULL [ok]
-- [not-null] GoDiagrams.View_Down int(11) default NULL -> default 0 -> NOT NULL [ok]
ALTER TABLE GoDiagrams
   MODIFY Size tinyint unsigned NOT NULL,
   MODIFY View_Left tinyint NOT NULL default '0',
   MODIFY View_Right tinyint NOT NULL default '0',
   MODIFY View_Up tinyint NOT NULL default '0',
   MODIFY View_Down tinyint NOT NULL default '0' ;

-- [unused] MoveMessages-table (remove game-id auto-increment)
-- [not-null] MoveMessages.Text text -> NOT NULL [ok]
ALTER TABLE MoveMessages
   MODIFY gid int(11) NOT NULL,
   MODIFY Text text NOT NULL ;

-- [not-null] Observers.uid int(11) default NULL -> NOT NULL -> no default [ok]
-- [not-null] Observers.gid int(11) default NULL -> NOT NULL -> no default [ok]
ALTER TABLE Observers
   MODIFY uid int NOT NULL,
   MODIFY gid int NOT NULL ;

-- [field-size] COL Players.Adminlevel : int -> smallint unsigned
-- [field-size] COL Players.AdminOptions : int -> smallint unsigned
-- [field-size] COL Players.VacationDays : double -> float
-- [field-size] COL Players.OnVacation : double -> float
-- [field-size] COL Players.Woodcolor : int -> tinyint unsigned
-- [field-size] COL Players.Boardcoords : int -> smallint unsigned
-- [field-size] COL Players.Button : int -> tinyint unsigned
-- [field-size] COL Players.Running : int -> smallint unsigned
-- [field-size] COL Players.Finished : int -> mediumint unsigned
-- [field-size] COL Players.RatedGames : int -> mediumint unsigned
-- [field-size] COL Players.Won : int -> mediumint unsigned
-- [field-size] COL Players.Lost : int -> mediumint unsigned
-- [no-def] Players.Handle varchar(16) NOT NULL default '' -> no default [ok]
-- [no-def] Players.Password varchar(41) NOT NULL default '' -> no default [ok]
-- [no-def] Players.Registerdate date default NULL -> no default [ok]
-- [not-null] Players.Moves int(11) default '0' -> NOT NULL [ok]
-- [not-null] Players.Email varchar(80) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Players.Rank varchar(40) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Players.Open varchar(40) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Players.VacationDays double default '14' -> NOT NULL
-- [not-null] Players.OnVacation double default '0' -> NOT NULL
-- [not-null] Players.Browser varchar(100) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Players.Country char(2) default NULL -> default '' -> NOT NULL [ok]
-- [unused] Players.NotesSmallMode -> fix enum (removed unused 'OFF'-value)
-- [unused] Players.NotesLargeMode -> fix enum (removed unused 'OFF'-value)
ALTER TABLE Players
   MODIFY Adminlevel smallint unsigned NOT NULL default '0',
   MODIFY AdminOptions smallint unsigned NOT NULL default '0',
   MODIFY VacationDays float NOT NULL default '14',
   MODIFY OnVacation float NOT NULL default '0',
   MODIFY Woodcolor tinyint unsigned NOT NULL default '1',
   MODIFY Boardcoords smallint unsigned NOT NULL default '31',
   MODIFY Button tinyint unsigned NOT NULL default '0',
   MODIFY Running smallint unsigned NOT NULL default '0',
   MODIFY Finished mediumint unsigned NOT NULL default '0',
   MODIFY RatedGames mediumint unsigned NOT NULL default '0',
   MODIFY Won mediumint unsigned NOT NULL default '0',
   MODIFY Lost mediumint unsigned NOT NULL default '0',
   MODIFY Handle varchar(16) NOT NULL,
   MODIFY Password varchar(41) NOT NULL,
   MODIFY Registerdate date,
   MODIFY Moves int NOT NULL default '0',
   MODIFY Email varchar(80) NOT NULL default '',
   MODIFY Rank varchar(40) NOT NULL default '',
   MODIFY Open varchar(40) NOT NULL default '',
   MODIFY Browser varchar(100) NOT NULL default '',
   MODIFY Country char(2) NOT NULL default '',
   MODIFY NotesSmallMode enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') NOT NULL default 'RIGHT',
   MODIFY NotesLargeMode enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') NOT NULL default 'RIGHT' ;

-- [field-size] COL Posts.AnswerNr : int -> mediumint unsigned
-- [field-size] COL Posts.Depth : int -> tinyint unsigned
-- [field-size] COL Posts.PostsInThread : int -> mediumint unsigned
-- [no-def] Posts.Forum_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Posts.User_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE Posts
   MODIFY AnswerNr mediumint unsigned NOT NULL default '0',
   MODIFY Depth tinyint unsigned NOT NULL default '0',
   MODIFY PostsInThread mediumint unsigned NOT NULL default '0',
   MODIFY Forum_ID int NOT NULL,
   MODIFY User_ID int NOT NULL ;

-- [no-def] RatingChange.uid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] RatingChange.gid int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE RatingChange
   MODIFY uid int NOT NULL,
   MODIFY gid int NOT NULL ;

-- [field-size] COL Ratinglog.RatingDiff : double -> float
-- [no-def] Ratinglog.uid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Ratinglog.gid int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE Ratinglog
   MODIFY RatingDiff float default NULL,
   MODIFY uid int NOT NULL,
   MODIFY gid int NOT NULL ;

-- [not-null] Statistics.Time datetime default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.Hits int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.Users int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.Moves int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.MovesFinished int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.MovesRunning int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.Games int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.GamesFinished int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.GamesRunning int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Statistics.Activity int(11) default NULL -> no default -> NOT NULL [ok]
ALTER TABLE Statistics
   MODIFY Time datetime NOT NULL,
   MODIFY Hits int NOT NULL,
   MODIFY Users int NOT NULL,
   MODIFY Moves int NOT NULL,
   MODIFY MovesFinished int NOT NULL,
   MODIFY MovesRunning int NOT NULL,
   MODIFY Games int NOT NULL,
   MODIFY GamesFinished int NOT NULL,
   MODIFY GamesRunning int NOT NULL,
   MODIFY Activity int NOT NULL ;

-- [no-def] TranslationFoundInGroup.Text_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] TranslationFoundInGroup.Group_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE TranslationFoundInGroup
   MODIFY Text_ID int NOT NULL,
   MODIFY Group_ID int NOT NULL ;

-- [no-def] TranslationGroups.Groupname varchar(32) NOT NULL default '' -> no default [ok]
ALTER TABLE TranslationGroups
   MODIFY Groupname varchar(32) NOT NULL ;

-- [not-null] TranslationLanguages.Language varchar(32) default NULL -> no default -> NOT NULL [ok]
-- [not-null] TranslationLanguages.Name varchar(32) default NULL -> no default -> NOT NULL [ok]
ALTER TABLE TranslationLanguages
   MODIFY Language varchar(32) NOT NULL,
   MODIFY Name varchar(32) NOT NULL ;

-- [not-null] TranslationPages.Page varchar(64) default NULL -> no default -> NOT NULL [ok]
-- [not-null] TranslationPages.Group_ID int(11) default NULL -> no default -> NOT NULL [ok]
ALTER TABLE TranslationPages
   MODIFY Page varchar(64) NOT NULL,
   MODIFY Group_ID int NOT NULL ;

-- [not-null] Translationlog.Player_ID int(11) default NULL -> no default -> NOT NULL [ok]
-- [not-null] Translationlog.Language_ID int(11) default NULL -> no default -> NOT NULL [ok]
ALTER TABLE Translationlog
   MODIFY Player_ID int NOT NULL,
   MODIFY Language_ID int NOT NULL ;

-- [no-def] Translations.Original_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Translations.Language_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE Translations
   MODIFY Original_ID int NOT NULL,
   MODIFY Language_ID int NOT NULL ;

-- [field-size] COL Waitingroom.nrGames : int -> tinyint unsigned
-- [field-size] COL Waitingroom.Size : int -> tinyint unsigned
-- [field-size] COL Waitingroom.Handicap : int -> tinyint unsigned
-- [field-size] COL Waitingroom.Maintime : int -> smallint
-- [field-size] COL Waitingroom.Byotime : int -> smallint
-- [field-size] COL Waitingroom.Byoperiods : int -> tinyint
-- [field-size] COL Waitingroom.Ratingmin : double -> float
-- [field-size] COL Waitingroom.Ratingmax : double -> float
-- [no-def] Waitingroom.uid int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE Waitingroom
   MODIFY nrGames tinyint unsigned NOT NULL default '1',
   MODIFY Size tinyint unsigned NOT NULL default '19',
   MODIFY Handicap tinyint unsigned NOT NULL default '0',
   MODIFY Maintime smallint NOT NULL default '0',
   MODIFY Byotime smallint NOT NULL default '0',
   MODIFY Byoperiods tinyint NOT NULL default '0',
   MODIFY Ratingmin float NOT NULL default '-9999',
   MODIFY Ratingmax float NOT NULL default '-9999',
   MODIFY uid int NOT NULL ;




###  ------ Changes for Release 1.0.15 ----------------------------------------------

-- change Players table-columns-handling
ALTER TABLE Players
   MODIFY UsersColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY GamesColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY RunningGamesColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY FinishedGamesColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY ObservedGamesColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY WaitingroomColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY TournamentsColumns INT( 11 ) DEFAULT '-1' NOT NULL ,
   MODIFY ContactColumns INT( 11 ) DEFAULT '-1' NOT NULL ;


-- change Players.Activity: float -> integer
UPDATE Players
   SET Activity=FLOOR(1000*Activity) WHERE Activity>0;
ALTER TABLE Players
   MODIFY Activity INT(11) NOT NULL DEFAULT '15000';


-- enable JavaScript usage for old players (as actually):
-- perform this only, if your DGS-server wants to support JavaScript
UPDATE Players
   SET Boardcoords=Boardcoords|0x100;


-- add feature-voting
CREATE TABLE FeatureList (
   ID int(11) NOT NULL auto_increment,
   Status enum('NEW','NACK','ACK','WORK','DONE','LIVE','ARCH') NOT NULL default 'NEW',
   Subject varchar(120) NOT NULL,
   Description text NOT NULL,
   Editor_ID int(11) NOT NULL default '0',
   Created datetime NOT NULL default '0000-00-00 00:00:00',
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (ID),
   KEY Status (Status),
   KEY Editor_ID (Editor_ID)
) ENGINE=MyISAM;

CREATE TABLE FeatureVote (
   fid int(11) NOT NULL,
   Voter_ID int(11) NOT NULL default '0',
   Points int(11) NOT NULL default '0',
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (fid,Voter_ID),
   KEY Voter_ID (Voter_ID)
) ENGINE=MyISAM;


-- add Forums.ThreadsInForum & seed it
ALTER TABLE `Forums`
   ADD `ThreadsInForum` int(11) NOT NULL default '0' AFTER `LastPost` ;
UPDATE Forums,
   (SELECT Forum_ID, COUNT(*) AS X_Count
       FROM Posts WHERE Approved='Y' AND Thread_ID>0 AND Parent_ID=0
       GROUP BY Forum_ID) as TMPF
   SET Forums.ThreadsInForum=TMPF.X_Count WHERE Forums.ID=TMPF.Forum_ID ;

-- add Posts.Hits
ALTER TABLE `Posts`
   ADD `Hits` int(11) NOT NULL default '0' AFTER `PostsInThread` ;
UPDATE Posts,
   (SELECT ID, COUNT(*) AS X_Count
       FROM Posts WHERE Thread_ID>0 AND PosIndex>''
       GROUP BY Thread_ID HAVING X_Count>0) as TMP
   SET Posts.Hits=TMP.X_Count WHERE Posts.ID=TMP.ID ;


-- add Posts.Updated for NEW-handling
ALTER TABLE `Posts`
   ADD `Updated` datetime NOT NULL default '0000-00-00 00:00:00' AFTER `Lastedited` ;
UPDATE Posts
   SET Updated=Lastchanged WHERE Parent_ID=0 ;

-- add Forums.Updated for NEW-handling
ALTER TABLE `Forums`
   ADD `Updated` datetime NOT NULL default '0000-00-00 00:00:00' AFTER `LastPost` ;
UPDATE Forums, Posts
   SET Forums.Updated=Posts.Time WHERE Forums.LastPost>0 AND Posts.ID=Forums.LastPost ;

DELETE FROM Forumreads
   WHERE Thread_ID=0 ;


-- configure forum-GUI for user, manage hidden forum-config for user
ALTER TABLE `Players`
   ADD `ForumFlags` tinyint unsigned NOT NULL default '8' AFTER `MayPostOnForum` ;


-- replace Posts.PendingApproval -> Approved (enum 'P')
ALTER TABLE `Posts`
   MODIFY Approved enum('Y','N','P') NOT NULL DEFAULT 'Y';
UPDATE Posts
   SET Approved='P' WHERE PendingApproval='Y';

-- after update above, the "old" column can be dropped -> CLEANUP
ALTER TABLE `Posts`
   DROP COLUMN PendingApproval ;


-- add Table Profiles for search- and form-profiles
CREATE TABLE Profiles (
   ID int(11) NOT NULL auto_increment,
   User_ID int(11) NOT NULL default '0',
   Type smallint(5) NOT NULL default '0',
   SortOrder tinyint(3) NOT NULL default '1',
   Active enum('Y','N') NOT NULL default 'N',
   Name varchar(40) NOT NULL default '',
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   Text blob NOT NULL,
   PRIMARY KEY (ID),
   KEY UserType (User_ID,Type)
) ENGINE=MyISAM ;


-- game-handicap adjustment & min/max-limits
ALTER TABLE `Waitingroom`
   ADD `AdjHandicap` tinyint signed NOT NULL default '0' AFTER `Handicaptype`,
   ADD `MinHandicap` tinyint unsigned NOT NULL default '0' AFTER `AdjHandicap`,
   ADD `MaxHandicap` tinyint unsigned NOT NULL default '127' AFTER `MinHandicap` ;


-- user-type characteristics: bot, teacher, pro, etc.
ALTER TABLE `Players`
   ADD `Type` smallint(5) unsigned NOT NULL default '0' AFTER `ID` ;
ALTER TABLE `Players`
   ADD INDEX `Type` (`Type`) ;


-- Feature-voting: Voter-IP
ALTER TABLE `FeatureVote`
   ADD `IP` varchar(16) NOT NULL default '' ;


-- Feature-voting: refactoring
ALTER TABLE `FeatureList`
   MODIFY Status enum('NEW','WORK','DONE','LIVE','NACK') NOT NULL DEFAULT 'NEW' ;
ALTER TABLE `FeatureList`
   DROP INDEX Editor_ID ;


-- removed ADMIN_TIME-role (has been replaced with admin-option)
UPDATE Players
   SET Adminlevel=Adminlevel & ~0x10 WHERE Adminlevel > 0 ;


-- global user profile flags (in preparation to split Players-table)
ALTER TABLE `Players`
   ADD `UserFlags` int(11) NOT NULL default '0' ;

-- move JavaScript-flags from Boardcoords into UserFlags
UPDATE Players
   SET UserFlags=IF(Boardcoords & 0x100,1,0) ;
UPDATE Players
   SET Boardcoords=Boardcoords & ~0x100 ;


-- group (global) user-profile fields in Players-table
ALTER TABLE `Players`
   MODIFY Button tinyint unsigned NOT NULL default '0' AFTER UserFlags,
   MODIFY TableMaxRows smallint(5) unsigned NOT NULL default '20'  AFTER UserFlags,
   MODIFY MenuDirection enum('VERTICAL','HORIZONTAL') NOT NULL default 'VERTICAL' AFTER UserFlags,
   MODIFY SkinName varchar(32) NOT NULL default '' AFTER UserFlags ;


-- split Players-table into GUI-related config-tables (board-related)
CREATE TABLE ConfigBoard (
   User_ID int(11) NOT NULL DEFAULT '0',
   Stonesize tinyint(3) unsigned NOT NULL default '25',
   Woodcolor int(11) NOT NULL default '1',
   Boardcoords int(11) NOT NULL default '31',
   MoveNumbers smallint(5) unsigned NOT NULL default '0',
   MoveModulo smallint(5) unsigned NOT NULL default '0',
   NotesSmallHeight tinyint(3) unsigned NOT NULL default '25',
   NotesSmallWidth tinyint(3) unsigned NOT NULL default '30',
   NotesSmallMode enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') NOT NULL default 'RIGHT',
   NotesLargeHeight tinyint(3) unsigned NOT NULL default '25',
   NotesLargeWidth tinyint(3) unsigned NOT NULL default '30',
   NotesLargeMode enum('RIGHT','BELOW','RIGHTOFF','BELOWOFF') NOT NULL default 'RIGHT',
   NotesCutoff tinyint(3) unsigned NOT NULL default '13',
   PRIMARY KEY (User_ID)
) ENGINE=MyISAM;

-- create ConfigBoard-entries for all players and drop copied columns
INSERT INTO ConfigBoard
   (User_ID,Stonesize,Woodcolor,Boardcoords,MoveNumbers,MoveModulo,
   NotesSmallHeight,NotesSmallWidth,NotesSmallMode,NotesLargeHeight,
   NotesLargeWidth,NotesLargeMode,NotesCutoff)
   SELECT ID,Stonesize,Woodcolor,Boardcoords,MoveNumbers,MoveModulo,
      NotesSmallHeight,NotesSmallWidth,NotesSmallMode,NotesLargeHeight,
      NotesLargeWidth,NotesLargeMode,NotesCutoff
   FROM Players ;

-- [mandatory] drop copied columns (ConfigBoard)
ALTER TABLE Players
   DROP COLUMN Stonesize,
   DROP COLUMN Woodcolor,
   DROP COLUMN Boardcoords,
   DROP COLUMN MoveNumbers,
   DROP COLUMN MoveModulo,
   DROP COLUMN NotesSmallHeight,
   DROP COLUMN NotesSmallWidth,
   DROP COLUMN NotesSmallMode,
   DROP COLUMN NotesLargeHeight,
   DROP COLUMN NotesLargeWidth,
   DROP COLUMN NotesLargeMode,
   DROP COLUMN NotesCutoff ;

-- split Players-table into GUI-related config-tables (related to other pages)
CREATE TABLE ConfigPages (
   User_ID int(11) NOT NULL DEFAULT '0',
   StatusFolders varchar(40) NOT NULL default '',
   ForumFlags tinyint(3) unsigned NOT NULL default '8',
   ColumnsStatusGames int(11) NOT NULL default '-1',
   ColumnsWaitingroom int(11) NOT NULL default '-1',
   ColumnsUsers int(11) NOT NULL default '-1',
   ColumnsOpponents int(11) NOT NULL default '-1',
   ColumnsContacts int(11) NOT NULL default '-1',
   ColumnsGamesRunningAll int(11) NOT NULL default '-1',
   ColumnsGamesRunningAll2 int(11) NOT NULL default '-1',
   ColumnsGamesRunningUser int(11) NOT NULL default '-1',
   ColumnsGamesRunningUser2 int(11) NOT NULL default '-1',
   ColumnsGamesFinishedAll int(11) NOT NULL default '-1',
   ColumnsGamesFinishedAll2 int(11) NOT NULL default '-1',
   ColumnsGamesFinishedUser int(11) NOT NULL default '-1',
   ColumnsGamesFinishedUser2 int(11) NOT NULL default '-1',
   ColumnsGamesObserved int(11) NOT NULL default '-1',
   ColumnsGamesObserved2 int(11) NOT NULL default '-1',
   ColumnsTournaments int(11) NOT NULL default '-1',
   PRIMARY KEY (User_ID)
) ENGINE=MyISAM;

-- create ConfigPages-entries for all players and drop copied columns (migration-split)
INSERT INTO ConfigPages
   (User_ID,StatusFolders,ForumFlags,
   ColumnsStatusGames,ColumnsWaitingroom,
   ColumnsUsers,ColumnsOpponents,ColumnsContacts,
   ColumnsGamesRunningAll,
   ColumnsGamesRunningAll2,
   ColumnsGamesRunningUser,
   ColumnsGamesRunningUser2,
   ColumnsGamesFinishedAll,
   ColumnsGamesFinishedAll2,
   ColumnsGamesFinishedUser,
   ColumnsGamesFinishedUser2,
   ColumnsGamesObserved,
   ColumnsGamesObserved2,
   ColumnsTournaments)
   SELECT
      ID,StatusFolders,ForumFlags,
      GamesColumns,
      WaitingroomColumns,
      UsersColumns,
      UsersColumns,
      ContactColumns,
      0x3fffffff & RunningGamesColumns,
      0x3ffffffc | ((RunningGamesColumns & 0x3fffffff) >> 30),
      0x3fffffff & RunningGamesColumns,
      0x3ffffffc | ((RunningGamesColumns & 0x3fffffff) >> 30),
      0x3fffffff & FinishedGamesColumns,
      0x3ffffffc | ((FinishedGamesColumns & 0x3fffffff) >> 30),
      0x3fffffff & FinishedGamesColumns,
      0x3ffffffc | ((FinishedGamesColumns & 0x3fffffff) >> 30),
      0x3fffffff & ObservedGamesColumns,
      0x3ffffffc | ((ObservedGamesColumns& 0x3fffffff) >> 30),
      TournamentsColumns
   FROM Players ;

-- [mandatory] drop copied columns (ConfigPages)
ALTER TABLE Players
   DROP COLUMN StatusFolders,
   DROP COLUMN ForumFlags,
   DROP COLUMN GamesColumns,
   DROP COLUMN WaitingroomColumns,
   DROP COLUMN UsersColumns,
   DROP COLUMN ContactColumns,
   DROP COLUMN RunningGamesColumns,
   DROP COLUMN FinishedGamesColumns,
   DROP COLUMN ObservedGamesColumns,
   DROP COLUMN TournamentsColumns ;


-- added games-list mode for [all observed games]
ALTER TABLE `ConfigPages`
   ADD ColumnsGamesObservedAll2 int(11) NOT NULL default '-1' AFTER `ColumnsGamesObserved2`,
   ADD ColumnsGamesObservedAll int(11) NOT NULL default '-1' AFTER `ColumnsGamesObserved2` ;


-- manage Tournaments
CREATE TABLE Tournament (
   ID int(11) NOT NULL auto_increment,
   Scope enum('DRAGON','PUBLIC','PRIVATE') NOT NULL default 'PUBLIC',
   Type enum('ROUNDROBIN') NOT NULL default 'ROUNDROBIN',
   Title varchar(255) NOT NULL default '',
   Description text NOT NULL,
   Owner_ID int(11) NOT NULL default '0',
   Status enum('ADM','NEW','REG','PAIR','PLAY','CLOSED') NOT NULL default 'NEW',
   Created datetime NOT NULL default '0000-00-00 00:00:00',
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   StartTime datetime NOT NULL default '0000-00-00 00:00:00',
   EndTime datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (ID),
   KEY Status (Status),
   KEY StartTime (StartTime)
) ENGINE=MyISAM;

-- table-column-set for tournament-list
ALTER TABLE ConfigPages
   CHANGE ColumnsTournaments ColumnsTournamentList int(11) NOT NULL default '-1' ;
UPDATE ConfigPages
   SET ColumnsTournamentList=-1 ;

-- manage Tournament-Directors
CREATE TABLE TournamentDirector (
   tid int(11) NOT NULL,
   uid int(11) NOT NULL,
   Comment varchar(255) NOT NULL default '',
   PRIMARY KEY (tid,uid)
) ENGINE=MyISAM;


-- undo rename of table-column-set for tournament-list
ALTER TABLE ConfigPages
   CHANGE ColumnsTournamentList ColumnsTournaments int(11) NOT NULL default '-1' ;

-- table-column-set for tournament-participant-list
ALTER TABLE ConfigPages
   ADD ColumnsTournamentParticipants int(11) NOT NULL default '-1' ;


-- removed ADMIN_ADD_ADMIN-role (has been merged with ADMIN_SUPERADMIN-role)
UPDATE Players
   SET Adminlevel=Adminlevel & ~0x20 WHERE Adminlevel > 0 ;


-- manage Tournament-Participants
CREATE TABLE TournamentParticipant (
   ID int(11) NOT NULL auto_increment,
   tid int(11) NOT NULL,
   uid int(11) NOT NULL,
   Status enum('APPLY','REGISTER','INVITE') NOT NULL default 'APPLY',
   Flags smallint(5) unsigned NOT NULL default '0',
   Rating double NOT NULL default '-9999',
   StartRound tinyint(3) unsigned NOT NULL default '1',
   AuthToken varchar(32) NOT NULL default '',
   Created datetime NOT NULL default '0000-00-00 00:00:00',
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   Comment varchar(60) NOT NULL default '',
   Notes text NOT NULL,
   PRIMARY KEY (ID),
   KEY tid (tid),
   KEY uid (uid)
) ENGINE=MyISAM;


-- added message-texts for talk between TD and participant-user
ALTER TABLE TournamentParticipant
   ADD UserMessage text NOT NULL,
   ADD AdminMessage text NOT NULL ;

-- table-column-set for status-tournaments-list
ALTER TABLE ConfigPages
   ADD ColumnsStatusTournaments int(11) NOT NULL default '-1' AFTER `ColumnsStatusGames`;

-- table-column-set for tournament-participant-list for TD
ALTER TABLE ConfigPages
   ADD ColumnsTDTournamentParticipants int(11) NOT NULL default '-1' ;


-- add required number of finished rated games
ALTER TABLE `Waitingroom`
   ADD `MinRatedGames` smallint NOT NULL default '0' AFTER `Ratingmax` ;


-- manage Tournament-Properties with restrictions for register-phase
CREATE TABLE TournamentProperties (
   tid int(11) NOT NULL,
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   MinParticipants smallint NOT NULL default '2',
   MaxParticipants smallint NOT NULL default '0',
   RatingUseMode enum('COPY_CUSTOM','CURR_FIX','COPY_FIX','ENTER_FIX') NOT NULL default 'COPY_CUSTOM',
   RegisterEndTime datetime NOT NULL default '0000-00-00 00:00:00',
   UserMinRating double NOT NULL default '-9999',
   UserMaxRating double NOT NULL default '-9999',
   UserRated enum('N','Y') NOT NULL default 'N',
   UserMinGamesFinished smallint NOT NULL default '0',
   UserMinGamesRated smallint NOT NULL default '0',
   Notes text NOT NULL,
   PRIMARY KEY (tid)
) ENGINE=MyISAM;


-- added tournament-rounds
ALTER TABLE Tournament
   ADD Rounds int(11) NOT NULL default '1',
   ADD CurrentRound int(11) NOT NULL default '1' ;


-- added tournament-rules with game-settings
CREATE TABLE TournamentRules (
   ID int(11) NOT NULL auto_increment,
   tid int(11) NOT NULL,
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   Flags smallint(5) unsigned NOT NULL default '0',
   Size int(11) NOT NULL default '19',
   Handicaptype enum('CONV','PROPER','NIGIRI','DOUBLE') NOT NULL default 'CONV',
   Handicap int(11) NOT NULL default '0',
   Komi decimal(6,1) NOT NULL default '6.5',
   AdjHandicap tinyint signed NOT NULL default '0',
   MinHandicap tinyint signed NOT NULL default '0',
   MaxHandicap tinyint signed NOT NULL default '127',
   StdHandicap enum('N','Y') NOT NULL default 'N',
   Maintime int(11) NOT NULL default '0',
   Byotype enum('JAP','CAN','FIS') NOT NULL default 'JAP',
   Byotime int(11) NOT NULL default '0',
   Byoperiods int(11) NOT NULL default '0',
   WeekendClock enum('N','Y') NOT NULL default 'Y',
   Rated enum('N','Y') NOT NULL default 'N',
   Notes text NOT NULL,
   PRIMARY KEY (ID),
   KEY tid (tid)
) ENGINE=MyISAM;


-- added user-picture
ALTER TABLE Players
   ADD UserPicture varchar(48) NOT NULL default '' ;


-- allow longer Feature-subject
ALTER TABLE FeatureList
   MODIFY Subject varchar(255) NOT NULL ;


-- added table for points-management and quota-like fields
CREATE TABLE UserQuota (
   uid int(11) NOT NULL,
   FeaturePoints smallint(5) NOT NULL default '25',
   FeaturePointsUpdated datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (uid),
   KEY FeaturePointsUpdated (FeaturePointsUpdated)
) ENGINE=MyISAM;

-- create UserQuota for all players
INSERT UserQuota (uid,FeaturePointsUpdated)
   SELECT ID,NOW() FROM Players ;


-- need restriction on voted points
ALTER TABLE FeatureVote
   ADD INDEX Points (Points) ;


-- table-column-set for feature-list
ALTER TABLE ConfigPages
   ADD ColumnsFeatureList int(11) NOT NULL default '-1' AFTER ColumnsGamesObservedAll2 ;


-- game-komi adjustment & jigo mode (for Waiting room)
ALTER TABLE `Waitingroom`
   ADD `AdjKomi` decimal(6,1) signed NOT NULL default '0.0' AFTER `Handicaptype`,
   ADD `JigoMode` enum('KEEP_KOMI','ALLOW_JIGO','NO_JIGO') NOT NULL default 'KEEP_KOMI' AFTER `AdjKomi` ;

-- game-komi adjustment & jigo mode (for Tournament rules)
ALTER TABLE `TournamentRules`
   ADD `AdjKomi` decimal(6,1) signed NOT NULL default '0.0' AFTER `Handicaptype`,
   ADD `JigoMode` enum('KEEP_KOMI','ALLOW_JIGO','NO_JIGO') NOT NULL default 'KEEP_KOMI' AFTER `AdjKomi` ;


-- accept same opponent
ALTER TABLE Waitingroom
   ADD SameOpponent tinyint signed NOT NULL default '0' AFTER MinRatedGames ;

-- added table to keep track of joined waiting-room games with opponent
CREATE TABLE WaitingroomJoined (
   opp_id int(11) NOT NULL,
   wroom_id int(11) NOT NULL,
   JoinedCount tinyint signed NOT NULL default '0',
   ExpireDate datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (wroom_id,opp_id)
) ENGINE=MyISAM;

-- cleanup table Waitingroom removing (unused) enum-value 'Done'
ALTER TABLE Waitingroom
   MODIFY Rated enum('N','Y') NOT NULL default 'N',
   MODIFY MustBeRated enum('N','Y') NOT NULL default 'N' ;


-- added manual coloring black/white
ALTER TABLE Waitingroom
   MODIFY Handicaptype enum('conv','proper','nigiri','double','black','white') NOT NULL default 'conv' ;
ALTER TABLE Waitingroom
   ADD INDEX Handicaptype (Handicaptype);


-- show Settings-column in waiting-room
UPDATE ConfigPages
   SET ColumnsWaitingroom=ColumnsWaitingroom | 0x20000 ;


-- added double-game reference
ALTER TABLE Games
   ADD DoubleGame_ID int(11) NOT NULL DEFAULT '0' AFTER mid ;


-- add message-thread (without the need for recursion), (~90s + ~90s)
ALTER TABLE Messages
   ADD Level smallint NOT NULL DEFAULT '0' AFTER Type,
   ADD Thread int(11) NOT NULL DEFAULT '0' AFTER Type ;
ALTER TABLE Messages
   ADD INDEX Thread (Thread) ;

-- fill Messages.Thread with some database-queries and fix-script
-- First do preparations for fix-scripts ...
-- * step 1, init (~25s): all starting messages start a thread
UPDATE Messages SET Thread=ID WHERE ReplyTo=0 ;
-- * step 2, init (~20s): revert Thread-field for system-message as they should have no replies and no threads
UPDATE Messages AS M INNER JOIN MessageCorrespondents AS MC ON MC.mid=M.ID SET M.Thread=0 WHERE M.Thread>0 AND MC.Sender='S' ;
-- * step 3, init: shouldn't happen, but did (system-message replied) -> add thread for those
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.ReplyTo=0 SET M2.Thread=M2.ID WHERE M.ReplyTo>0 AND M.Thread=0 AND M2.Thread=0 ;
-- * step 4, check: message-count which need message-thread assigning ... (~530.000 rows)
SELECT COUNT(*) FROM Messages WHERE ReplyTo>0 AND Thread=0 ;
-- * step 5, pre-run (~20s): set message of reply-level #1
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.ReplyTo=0 SET M.Thread=M.ReplyTo, M.Level=1 WHERE M.ReplyTo>0 ;
-- * step 6, pre-run (~4-20s each run): set message of reply-level #2 (execute 10 times) !!
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
UPDATE Messages AS M INNER JOIN Messages AS M2 ON M.ReplyTo=M2.ID AND M2.Thread>0 SET M.Thread=M2.Thread, M.Level=M2.Level+1 WHERE M.ReplyTo>0 and M.Thread=0 ;
-- * step 7, check: remaining message-count that need message-thread assigning ... (~37.000 rows left)
SELECT COUNT(*) FROM Messages WHERE ReplyTo>0 AND Thread=0 ;
-- * step 8, repeat for all thread-levels until no more rows affected any more using optimized script:
--           execute (with do_it=1):   'scripts/updates/fix_message_thread-1_0_15.php'
-- execute 'scripts/message_consistency.php'




###  ------ Cleanup tables of DGS-servers [part 2] ----------------------------------
###         (Data types, NOT NULL, Defaults, Enums)

-- [field-size] COL ConfigBoard.Woodcolor : int -> tinyint unsigned
-- [field-size] COL ConfigBoard.Boardcoords : int -> smallint unsigned
-- [no-def] ConfigBoard.User_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE ConfigBoard
   MODIFY Woodcolor tinyint unsigned NOT NULL default '1',
   MODIFY Boardcoords smallint unsigned NOT NULL default '31',
   MODIFY User_ID int NOT NULL ;

-- [no-def] ConfigPages.User_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE ConfigPages
   MODIFY User_ID int NOT NULL ;

-- [no-def] FeatureList.Editor_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] FeatureList.Created datetime NOT NULL default '0000-00-00 00:00:00' -> no default [ok]
ALTER TABLE FeatureList
   MODIFY Editor_ID int NOT NULL,
   MODIFY Created datetime NOT NULL ;

-- [field-size] COL FeatureVote.Points : int -> tinyint
-- [no-def] FeatureVote.Voter_ID int(11) NOT NULL default '0' -> no default [ok]
ALTER TABLE FeatureVote
   MODIFY Points tinyint NOT NULL default '0',
   MODIFY Voter_ID int NOT NULL ;

-- [field-size] COL Forums.ID : int -> smallint + foreign-keys
-- [field-size] COL Forums.SortOrder : int -> smallint unsigned
-- [field-size] COL Forums.PostsInForum : int -> mediumint unsigned
-- [field-size] COL Forums.ThreadsInForum : int -> mediumint unsigned
-- [not-null] Forums.Name varchar(40) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Forums.Description varchar(255) default NULL -> default '' -> NOT NULL [ok]
-- [not-null] Forums.LastPost int(11) default NULL default '0' -> NOT NULL [ok]
ALTER TABLE Forums
   MODIFY ID smallint NOT NULL auto_increment,
   MODIFY SortOrder smallint unsigned NOT NULL default '0',
   MODIFY PostsInForum mediumint unsigned NOT NULL default '0',
   MODIFY ThreadsInForum mediumint unsigned NOT NULL default '0',
   MODIFY Name varchar(40) NOT NULL default '',
   MODIFY Description varchar(255) NOT NULL default '',
   MODIFY LastPost int NOT NULL default '0' ;
-- [field-size] COL Posts.Forum_ID : int -> smallint (foreign-key of Forums.ID)
ALTER TABLE Posts
   MODIFY Forum_ID smallint NOT NULL default '0' ;

-- [field-size] COL Games.Size : int -> tinyint unsigned
-- [field-size] COL Games.Handicap : int -> tinyint unsigned
-- [field-size] COL Games.Moves : int -> smallint unsigned
-- [field-size] COL Games.Black_Prisoners : int -> smallint unsigned
-- [field-size] COL Games.White_Prisoners : int -> smallint unsigned
-- [field-size] COL Games.Last_X : int -> tinyint
-- [field-size] COL Games.Last_Y : int -> tinyint
-- [field-size] COL Games.Maintime : int -> smallint
-- [field-size] COL Games.Black_Maintime : int -> smallint
-- [field-size] COL Games.White_Maintime : int -> smallint
-- [field-size] COL Games.Byotime : int -> smallint
-- [field-size] COL Games.Black_Byotime : int -> smallint
-- [field-size] COL Games.White_Byotime : int -> smallint
-- [field-size] COL Games.Byoperiods : int -> tinyint
-- [field-size] COL Games.Black_Byoperiods : int -> tinyint
-- [field-size] COL Games.White_Byoperiods : int -> tinyint
-- [no-def] Games.Black_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Games.White_ID int(11) NOT NULL default '0' -> no default [ok]
-- NOTE: ca. 80 warnings of data-loss, because of exorbitant large main/byo-time/byo-periods,
--       will be limited to data-type specific max-value
ALTER TABLE Games
   MODIFY Size tinyint unsigned NOT NULL default '19',
   MODIFY Handicap tinyint unsigned NOT NULL default '0',
   MODIFY Moves smallint unsigned NOT NULL default '0',
   MODIFY Black_Prisoners smallint unsigned NOT NULL default '0',
   MODIFY White_Prisoners smallint unsigned NOT NULL default '0',
   MODIFY Last_X tinyint NOT NULL default '-1',
   MODIFY Last_Y tinyint NOT NULL default '-1',
   MODIFY Maintime smallint NOT NULL default '0',
   MODIFY Black_Maintime smallint NOT NULL default '0',
   MODIFY White_Maintime smallint NOT NULL default '0',
   MODIFY Byotime smallint NOT NULL default '0',
   MODIFY Black_Byotime smallint NOT NULL default '0',
   MODIFY White_Byotime smallint NOT NULL default '0',
   MODIFY Byoperiods tinyint NOT NULL default '0',
   MODIFY Black_Byoperiods tinyint NOT NULL default '-1',
   MODIFY White_Byoperiods tinyint NOT NULL default '-1',
   MODIFY Black_ID int NOT NULL,
   MODIFY White_ID int NOT NULL ;

-- [no-def] GamesNotes.gid int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] GamesNotes.player enum('B','W') NOT NULL default 'B' -> no default [ok]
ALTER TABLE GamesNotes
   MODIFY gid int NOT NULL,
   MODIFY player enum('B','W') NOT NULL ;

-- [index] removed index for removed field IDX Posts.PendingApproval
ALTER TABLE Posts
   DROP INDEX PendingApproval ;
-- [index] added index IDX Posts.Approved (replace Posts.PendingApproval (double))
ALTER TABLE Posts
   ADD INDEX Approved (Approved) ;

-- [field-size] COL Profiles.SortOrder : tinyint -> smallint
-- [no-def] Profiles.User_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Profiles.Type smallint(5) NOT NULL default '0' -> no default [ok]
ALTER TABLE Profiles
   MODIFY SortOrder smallint NOT NULL default '1',
   MODIFY User_ID int NOT NULL,
   MODIFY Type smallint NOT NULL ;

-- [field-size] COL Tournament.Rounds : int -> tinyint unsigned
-- [field-size] COL Tournament.CurrentRound : int -> tinyint unsigned
-- [no-def] Tournament.Type enum('ROUNDROBIN') NOT NULL default 'ROUNDROBIN' -> no default [ok]
-- [no-def] Tournament.Title varchar(255) NOT NULL default '' -> no default [ok]
-- [no-def] Tournament.Owner_ID int(11) NOT NULL default '0' -> no default [ok]
-- [no-def] Tournament.Created datetime NOT NULL default '0000-00-00 00:00:00' -> no default [ok]
ALTER TABLE Tournament
   MODIFY Rounds tinyint unsigned NOT NULL default '1',
   MODIFY CurrentRound tinyint unsigned NOT NULL default '1',
   MODIFY Type enum('ROUNDROBIN') NOT NULL,
   MODIFY Title varchar(255) NOT NULL,
   MODIFY Owner_ID int NOT NULL,
   MODIFY Created datetime NOT NULL ;

-- [field-size] COL TournamentProperties.UserMinRating : double -> float
-- [field-size] COL TournamentProperties.UserMaxRating : double -> float
ALTER TABLE TournamentProperties
   MODIFY UserMinRating float NOT NULL default '-9999',
   MODIFY UserMaxRating float NOT NULL default '-9999' ;

-- [field-size] COL TournamentRules.Size : int -> tinyint unsigned
-- [field-size] COL TournamentRules.Handicap : int -> tinyint unsigned
-- [field-size] COL TournamentRules.MinHandicap : tinyint -> tinyint unsigned
-- [field-size] COL TournamentRules.MaxHandicap : tinyint -> tinyint unsigned
-- [field-size] COL TournamentRules.Maintime : int -> smallint
-- [field-size] COL TournamentRules.Byotime : int -> smallint
-- [field-size] COL TournamentRules.Byoperiods : int -> tinyint
ALTER TABLE TournamentRules
   MODIFY Size tinyint unsigned NOT NULL default '19',
   MODIFY Handicap tinyint unsigned NOT NULL default '0',
   MODIFY MinHandicap tinyint unsigned NOT NULL default '0',
   MODIFY MaxHandicap tinyint unsigned NOT NULL default '127',
   MODIFY Maintime smallint NOT NULL default '0',
   MODIFY Byotime smallint NOT NULL default '0',
   MODIFY Byoperiods tinyint NOT NULL default '0' ;

-- [index] analyze data distribution for query-optimizer
ANALYZE TABLE Bio ;
ANALYZE TABLE Contacts ;
ANALYZE TABLE Errorlog ;
ANALYZE TABLE Folders ;
ANALYZE TABLE Forumreads ;
ANALYZE TABLE Forumlog ;
ANALYZE TABLE Forums ;
ANALYZE TABLE Games ;
ANALYZE TABLE GamesNotes ;
ANALYZE TABLE MessageCorrespondents ;
ANALYZE TABLE Messages ;
ANALYZE TABLE MoveMessages ;
ANALYZE TABLE Moves ;
ANALYZE TABLE Observers ;
ANALYZE TABLE Players ;
ANALYZE TABLE Posts ;
ANALYZE TABLE Ratinglog ;
ANALYZE TABLE TranslationFoundInGroup ;
ANALYZE TABLE Translations ;
ANALYZE TABLE UserQuota ;
ANALYZE TABLE Waitingroom ;

###  ------ end of cleanup ----------------------------------------------------------



-- add index on Messages.Time to support searches on date (~ 2mins)
ALTER TABLE Messages
   ADD INDEX Time (Time) ;


-- enlarge field-size of Players.Open 40 -> 60
ALTER TABLE Players
   MODIFY Open varchar(60) NOT NULL default '' ;


-- change GamesNotes.player-enum to user-id (e.g. for later Rengo has more than one B and W player)
ALTER TABLE GamesNotes
   ADD uid int NOT NULL AFTER gid;

-- migrate GamesNotes.player -> uid
UPDATE GamesNotes AS GN, Games AS G
   SET GN.uid=IF(GN.player='B',G.Black_ID,IF(G.White_ID<>G.Black_ID,G.White_ID,-1)) WHERE GN.gid=G.ID ;

-- check if migration successful (following query-result must be empty)
-- NOTE: this can happen if Games are "broken", e.g. with same user as B & W
--       -> resolve by deleting GamesNotes or correcting Games-table
SELECT gid FROM GamesNotes WHERE uid <= 0 ;

-- switch primary-key (gid,uid) must be unique (that's the case if above conflicts have been resolved)
ALTER TABLE GamesNotes
   DROP PRIMARY KEY,
   ADD PRIMARY KEY (gid,uid) ;

-- remove unused field GamesNotes.ID, .player -> CLEANUP
ALTER TABLE GamesNotes
   DROP COLUMN ID,
   DROP COLUMN player ;


-- [not-null] MessageCorrespondents.Folder_nr tinyint default NULL -> no default -> NOT NULL [sources adjusted]
-- the following query-result should be empty before going on [update ca. 10s, alter-table ca. 25s]
SELECT COUNT(*) FROM MessageCorrespondents WHERE Folder_nr < 0 ;
UPDATE MessageCorrespondents
   SET Folder_nr=-4 WHERE Folder_nr IS NULL ;
ALTER TABLE MessageCorrespondents
   MODIFY Folder_nr tinyint NOT NULL ;


-- [not-null] Players.Registerdate -> NOT NULL -> no default [mandatory field]
-- the following query-result should be empty
SELECT COUNT(*) FROM Players WHERE Registerdate IS NULL ;
ALTER TABLE Players
   MODIFY Registerdate date NOT NULL ;

-- [not-null] Players.Sessionexpire -> NOT NULL -> default '0' [ok]
UPDATE Players
   SET Sessionexpire=0 WHERE Sessionexpire IS NULL ;
ALTER TABLE Players
   MODIFY Sessionexpire datetime NOT NULL default 0 ;

-- [not-null] Players.LastMove -> NOT NULL -> default '0' [ok]
UPDATE Players
   SET LastMove=0 WHERE LastMove IS NULL ;
ALTER TABLE Players
   MODIFY LastMove datetime NOT NULL default 0 ;

-- [not-null] Players.Lastaccess -> NOT NULL -> default '0' [ok]
UPDATE Players
   SET Lastaccess=0 WHERE Lastaccess IS NULL ;
ALTER TABLE Players
   MODIFY Lastaccess datetime NOT NULL default 0 ;


-- [unused] cleanup Translationlog-table (fields not used and not referenced) -> CLEANUP
-- fields have been replaced with Player_ID/Language_ID, check with select
SELECT DISTINCT Handle, Language FROM Translationlog ;
ALTER TABLE Translationlog
   DROP COLUMN Handle,
   DROP COLUMN Language ;


-- [not-null] Translationlog.Original_ID -> NOT NULL -> default '0' [ok]
UPDATE Translationlog
   SET Original_ID=0 WHERE Original_ID IS NULL ;
ALTER TABLE Translationlog
   MODIFY Original_ID int(11) NOT NULL default 0 ;


-- [not-null] TranslationTexts.Ref_ID -> NOT NULL -> default '0' [ok]
UPDATE TranslationTexts
   SET Ref_ID=0 WHERE Ref_ID IS NULL ;
ALTER TABLE TranslationTexts
   MODIFY Ref_ID int(11) NOT NULL default 0 ;


-- [not-null] Players.RatingStatus -> NOT NULL -> default 'NONE' (enhance enum) [ok]
ALTER TABLE Players
   MODIFY RatingStatus enum('NONE','INIT','RATED') default 'NONE' ;
UPDATE Players
   SET RatingStatus='NONE' WHERE RatingStatus IS NULL OR RatingStatus='' ;
ALTER TABLE Players
   MODIFY RatingStatus enum('NONE','INIT','RATED') NOT NULL default 'NONE' ;


-- added index to accelerate building of rating-graph (for ORDER BY Time)
ALTER TABLE Ratinglog
   DROP INDEX uid ;
ALTER TABLE Ratinglog
   ADD INDEX UserTime (uid,Time) ;


-- [not-null] Players.InitialRating -> NOT NULL -> default -9999 [ok]
UPDATE Players
   SET InitialRating='-9999' WHERE InitialRating IS NULL ;
ALTER TABLE Players
   MODIFY InitialRating double NOT NULL default '-9999' ;

-- [not-null] Ratinglog.Rating/RatingMin/RatingMax/RatingDiff -> NOT NULL -> no default [mandatory fields]
-- following query-result should be 0
SELECT COUNT(*) FROM Ratinglog
   WHERE Rating IS NULL OR RatingMin IS NULL OR RatingMax IS NULL OR RatingDiff IS NULL ;
ALTER TABLE Ratinglog
   MODIFY Rating double NOT NULL,
   MODIFY RatingMin double NOT NULL,
   MODIFY RatingMax double NOT NULL,
   MODIFY RatingDiff float NOT NULL ;


-- [unused] remove unused enum-values of Messages.Type (ACCEPTED,DECLINED,DELETED), cleanup wrong table-values
-- check (wrong) empty 'Type'-values (2489 empties)
SELECT COUNT(*),Type FROM Messages GROUP BY Type ;
-- check all for normal type (game invitations accepted or declined)
SELECT COUNT(*) FROM Messages WHERE Type='' AND Subject LIKE 'Game invitation%' ;
-- check for newer such entries (all should be <2009)
SELECT MAX(Time) FROM Messages WHERE Type='' ;
-- fix table data + check empty types again (should be 0)
UPDATE Messages
   SET Type='NORMAL' WHERE Type='' AND Subject LIKE 'Game invitation%' LIMIT 2489 ;
SELECT COUNT(*),Type FROM Messages GROUP BY Type ;

-- cleanup Messages.Type enum (ca. 2min)
ALTER TABLE Messages
   MODIFY Type enum('NORMAL','INVITATION','DISPUTED','RESULT') NOT NULL default 'NORMAL' ;


-- deactivate game-notes in table-columns as default (to reduce server-load) for games-list (status, my running/finished games)
UPDATE ConfigPages
   SET ColumnsStatusGames=ColumnsStatusGames & ~0x800,
   ColumnsGamesRunningUser2=ColumnsGamesRunningUser2 & ~0x4,
   ColumnsGamesFinishedUser2=ColumnsGamesFinishedUser2 & ~0x4 ;


-- added ruleset selection to waiting room
ALTER TABLE `Waitingroom`
   ADD COLUMN `Ruleset` enum('area', 'territory') NOT NULL DEFAULT 'territory' AFTER `Time` ;


-- fix country-code for East Timor -> Timor Leste: TP -> TL
UPDATE Players SET Country='tl' WHERE Country='tp' ;

-- fix country-code for Montenegro: MJ -> ME
UPDATE Players SET Country='me' WHERE Country='mj' ;

-- fix country-code for Earth: __ -> XE
UPDATE Players SET Country='xe' WHERE Country='__' ;

-- fix country-code for (language) Interlingua: IA -> XI
UPDATE Players SET Country='xi' WHERE Country='ia' ;

-- fix country-code for (language) Esperanto: EO -> XO
UPDATE Players SET Country='xo' WHERE Country='eo' ;

-- obsoleted country-code for Yugoslavia: YU -> RS (Serbia), but could be ME (Montenegro)
UPDATE Players SET Country='rs' WHERE Country='yu' ;

-- check for more unknown country-codes with Statistics page (evtl. fix more):
-- unknown flags are shown without flag-image but shows "[country-code]"
statistics.php?stats=1


-- GUI-flags for status page
ALTER TABLE ConfigPages
   ADD StatusFlags smallint NOT NULL default '3' AFTER User_ID ;

-- quick-read-count for NEW-messages of user
ALTER TABLE Players
   ADD CountMsgNew mediumint NOT NULL default '-1' AFTER Notify ;


-- quick-read-count for NEW-features for user
ALTER TABLE Players
   ADD CountFeatNew smallint NOT NULL default '-1' AFTER CountMsgNew ;
ALTER TABLE Players
   ADD INDEX CountFeatNew (CountFeatNew) ;


-- quick-read-count for NEW-forum for user
ALTER TABLE Players
   ADD CountForumNew mediumint NOT NULL default '-1' AFTER CountFeatNew ;


-- add indicator for hidden-comments within move-messages
ALTER TABLE Games
   MODIFY Flags set('Ko','HiddenMsg') NOT NULL default '' ;
-- execute fix-script (with do_it=1 right away), because db-statement takes quite some time:
--    'scripts/updates/fix_game_comments-1_0_15.php?do_it=1'


-- fix date-field Contacts.Created
UPDATE Contacts SET Created=Lastchanged WHERE YEAR(Created)=1970 ;


-- added index to search for games with comments
ALTER TABLE Games
   ADD INDEX Flags (Flags) ;


-- grant access-right to create temporary tables to DGS db-user
-- Lookup your respective configuration in 'include/config-local.php'
GRANT CREATE TEMPORARY TABLES ON DB_NAME.* TO MYSQLUSER@MYSQLHOST ;


-- [data] cleanup Adminlog (delete uninteresting messages, not longer logged)
DELETE FROM Adminlog WHERE Message = 'logged_in' ;


-- [data] cleanup unused Clocks
DELETE FROM Clock WHERE NOT ((ID BETWEEN 0 AND 23) OR (ID BETWEEN 100 AND 123) OR ID IN (201,202,203) ) ;


-- status-games ordering coupled with "next game"
ALTER TABLE Players
   ADD NextGameOrder enum('LASTMOVED','MOVES') NOT NULL DEFAULT 'LASTMOVED' AFTER UserPicture ;


-- status-games ordering by priority
CREATE TABLE GamesPriority (
   gid int(11) NOT NULL,
   uid int(11) NOT NULL,
   Priority smallint signed NOT NULL default '0',
   PRIMARY KEY (gid,uid)
) ENGINE=MyISAM;

ALTER TABLE Players
   MODIFY NextGameOrder enum('LASTMOVED','MOVES','PRIO') NOT NULL DEFAULT 'LASTMOVED' ;

-- deactivate status-game priority column by default
UPDATE ConfigPages SET ColumnsStatusGames=ColumnsStatusGames & ~0x10000 ;


-- status-games ordering by remaining-time
ALTER TABLE Games
   ADD TimeOutDate int NOT NULL DEFAULT '0' AFTER ClockUsed ;
ALTER TABLE Players
   MODIFY NextGameOrder enum('LASTMOVED','MOVES','PRIO','TIMELEFT') NOT NULL DEFAULT 'LASTMOVED' ;

-- Clock for remaining-time ordering
INSERT INTO Clock SET ID=204,Lastchanged=0 ;
-- execute fix-script
--    'scripts/fix_games_timeleft.php'


-- add index for vacation-cron job to increase/decrase vacation-days
ALTER TABLE Players
   ADD INDEX OnVacation (OnVacation),
   ADD INDEX VacationDays (VacationDays) ;


-- check Forumreads-table with thread=0 (must be 0 entries)
SELECT * FROM Forumreads WHERE Thread_ID=0 ;

-- changed forum to backport to enhanced Forumreads-table, optimizing index
ALTER TABLE Forumreads
   DROP PRIMARY KEY ;
ALTER TABLE Forumreads
   ADD Forum_ID smallint NOT NULL DEFAULT '0' AFTER User_ID ;
ALTER TABLE Forumreads
   ADD PRIMARY KEY (Thread_ID,User_ID,Forum_ID) ;
UPDATE Forumreads AS FR INNER JOIN Posts AS P ON P.ID=FR.Thread_ID
   SET FR.Forum_ID=P.Forum_ID WHERE FR.Thread_ID>0 AND FR.Forum_ID=0 ;
ANALYZE TABLE Forumreads ;

-- check Forumreads-table with forum=0 (must be 0 entries)
SELECT * FROM Forumreads WHERE Forum_ID=0 ;

-- [not-null] cleanup Forumreads.Time datetime default NULL -> no default -> NOT NULL
ALTER TABLE Forumreads
   MODIFY Time datetime NOT NULL ;

-- NEW-flag for forum-list
ALTER TABLE Forumreads
   ADD HasNew tinyint NOT NULL DEFAULT '0' ;

-- removed unused field for (first-trial) aggregated global-forum NEW-flag
ALTER TABLE Players
   DROP COLUMN CountForumNew ;


-- increase Browser-field length: 100->150
ALTER TABLE Players
   MODIFY Browser varchar(150) NOT NULL DEFAULT '';


-- bugfix: leading and trailing spaces are not allowed within T_(..) translation-texts
UPDATE TranslationTexts
   SET Text=TRIM(Text) WHERE LENGTH(Text) != LENGTH(TRIM(Text)) ;
-- run translation-scripts as described in 'scripts/README.translations'


-- [unused] remove unused field
ALTER TABLE Posts
   DROP COLUMN Updated ;


-- enhanced Errorlog with uid and page-request for causing error
ALTER TABLE Errorlog
   ADD uid int(11) NOT NULL DEFAULT '0' AFTER ID,
   ADD Request varchar(128) NOT NULL DEFAULT '' AFTER Message ;


-- create official "DGS-ladder" for tournament-wizard
ALTER TABLE Tournament
   ADD WizardType tinyint NOT NULL AFTER Type,
   MODIFY Type enum('LADDER','ROUNDROBIN') NOT NULL ;


-- added tournament delete-status
ALTER TABLE Tournament
   MODIFY Status enum('ADM','NEW','REG','PAIR','PLAY','CLOSED','DEL') NOT NULL default 'NEW' ;


-- removed RatingUseMode 'ENTER_FIX'
ALTER TABLE TournamentProperties
   MODIFY RatingUseMode enum('COPY_CUSTOM','CURR_FIX','COPY_FIX') NOT NULL default 'COPY_CUSTOM' ;


-- added changed-by field
ALTER TABLE Tournament
   ADD ChangedBy varchar(54) NOT NULL DEFAULT '' AFTER Lastchanged ;
ALTER TABLE TournamentProperties
   ADD ChangedBy varchar(54) NOT NULL DEFAULT '' AFTER Lastchanged ;
ALTER TABLE TournamentRules
   ADD ChangedBy varchar(54) NOT NULL DEFAULT '' AFTER Lastchanged ;
ALTER TABLE TournamentParticipant
   ADD ChangedBy varchar(54) NOT NULL DEFAULT '' AFTER Lastchanged ;


-- added index for TP-status
ALTER TABLE TournamentParticipant
   DROP INDEX tid ;
ALTER TABLE TournamentParticipant
   ADD INDEX tid_status (tid,Status) ;


-- added table to keep track of Ladder-type tournament
CREATE TABLE TournamentLadder (
   tid int(11) NOT NULL,
   rid int(11) NOT NULL,
   uid int(11) NOT NULL,
   Created datetime NOT NULL default '0000-00-00 00:00:00',
   RankChanged datetime NOT NULL default '0000-00-00 00:00:00',
   Rank smallint unsigned NOT NULL DEFAULT '0',
   BestRank smallint unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (tid,rid),
   KEY uid (uid),
   KEY Rank (tid,Rank)
) ENGINE=MyISAM;


-- table-column-set for tournament-ladder-view
ALTER TABLE ConfigPages
   ADD ColumnsTournamentLadderView int NOT NULL DEFAULT -1 ;


-- grant access-right to lock tables to DGS db-user
-- Lookup your respective configuration in 'include/config-local.php'
GRANT LOCK TABLES ON DB_NAME.* TO MYSQLUSER@MYSQLHOST ;


-- added table for ladder tournament properties
CREATE TABLE TournamentLadderProps (
   tid int(11) NOT NULL,
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   ChangedBy varchar(54) NOT NULL default '',
   ChallengeRangeAbsolute smallint NOT NULL DEFAULT '0',
   PRIMARY KEY (tid)
) ENGINE=MyISAM;


-- [field-size] COL Games.Score : dec(7,1) -> dec(5,1)
-- [cleanup] COL Games.Score : migrate MySQL4 -> MySQL5 interal storage
-- following must be 0
SELECT COUNT(*) FROM Games WHERE Score NOT BETWEEN -2000 AND 2000 ;
-- compare Data_length for table before & after field-migration
SHOW TABLE STATUS LIKE 'Games' ;
-- migrate MySQL4 (decimal string-type) -> MySQL5 (decimal byte-type)
ALTER TABLE Games
   ADD MIGR_Score decimal(5,1) NOT NULL DEFAULT '0.0' AFTER Score ;
UPDATE Games
   SET MIGR_Score = Score ;
ALTER TABLE Games
   DROP COLUMN Score ;
ALTER TABLE Games
   CHANGE MIGR_Score Score decimal(5,1) NOT NULL DEFAULT '0.0' ;
ALTER TABLE Games
  ADD KEY Score (Score) ;
SHOW TABLE STATUS LIKE 'Games' ;

-- [field-size] COL Games.Komi : dec(6,1) -> dec(4,1)
-- [cleanup] COL Games.Komi : migrate MySQL4 -> MySQL5 interal storage
-- following must be 0
SELECT COUNT(*) FROM Games WHERE Komi NOT BETWEEN -200 AND 200 ;
-- compare Data_length for table before & after field-migration
SHOW TABLE STATUS LIKE 'Games' ;
-- migrate MySQL4 (decimal string-type) -> MySQL5 (decimal byte-type)
ALTER TABLE Games
   ADD MIGR_Komi decimal(4,1) NOT NULL DEFAULT '6.5' AFTER Komi ;
UPDATE Games
   SET MIGR_Komi = Komi ;
ALTER TABLE Games
   DROP COLUMN Komi ;
ALTER TABLE Games
   CHANGE MIGR_Komi Komi decimal(4,1) NOT NULL DEFAULT '6.5' ;
SHOW TABLE STATUS LIKE 'Games' ;

-- [field-size] COL Waitingroom.Komi : dec(6,1) -> dec(4,1)
-- [cleanup] COL Waitingroom.Komi : migrate MySQL4 -> MySQL5 interal storage
-- following must be 0
SELECT COUNT(*) FROM Waitingroom WHERE Komi NOT BETWEEN -200 AND 200 ;
-- compare Data_length for table before & after field-migration
SHOW TABLE STATUS LIKE 'Waitingroom' ;
-- migrate MySQL4 (decimal string-type) -> MySQL5 (decimal byte-type)
ALTER TABLE Waitingroom
   ADD MIGR_Komi decimal(4,1) NOT NULL DEFAULT '6.5' AFTER Komi ;
UPDATE Waitingroom
   SET MIGR_Komi = Komi ;
ALTER TABLE Waitingroom
   DROP COLUMN Komi ;
ALTER TABLE Waitingroom
   CHANGE MIGR_Komi Komi decimal(4,1) NOT NULL DEFAULT '6.5' ;
SHOW TABLE STATUS LIKE 'Waitingroom' ;

-- [field-size] COL Waitingroom.AdjKomi : dec(6,1) -> dec(4,1)
ALTER TABLE Waitingroom
   MODIFY AdjKomi decimal(4,1) NOT NULL default '0.0' ;

-- [field-size] COL TournamentRules.Komi : dec(6,1) -> dec(4,1)
-- [field-size] COL TournamentRules.AdjKomi : dec(6,1) -> dec(4,1)
ALTER TABLE TournamentRules
   MODIFY Komi decimal(4,1) NOT NULL default '6.5',
   MODIFY AdjKomi decimal(4,1) NOT NULL default '0.0' ;


-- added table for tournament games
CREATE TABLE TournamentGames (
   ID int NOT NULL auto_increment,
   tid int NOT NULL,
   gid int NOT NULL DEFAULT '0',
   Status enum('INIT','PLAY','DONE') NOT NULL DEFAULT 'INIT',
   Flags smallint unsigned NOT NULL DEFAULT '0',
   Lastchanged datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   ChangedBy varchar(54) NOT NULL DEFAULT '',
   Challenger_uid int NOT NULL,
   Challenger_rid int NOT NULL,
   Defender_uid int NOT NULL,
   Defender_rid int NOT NULL,
   StartTime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   EndTime datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   Score decimal(5,1) NOT NULL DEFAULT '0.0',
   PRIMARY KEY (ID),
   KEY tid (tid)
) ENGINE=MyISAM;


-- [unused] removed unused field
ALTER TABLE TournamentParticipant
   DROP COLUMN AuthToken ;


-- add tournament-reference in Games-table to tournament
ALTER TABLE Games
   ADD tid int NOT NULL DEFAULT '0' AFTER ID ;
ALTER TABLE Games
   ADD KEY tid (tid) ;


-- adjust key: combine index on tid with Status
ALTER TABLE TournamentGames
   DROP KEY tid ;
ALTER TABLE TournamentGames
   ADD KEY tid_Status (tid,Status) ;


-- [index] added index on player uids
ALTER TABLE TournamentGames
   ADD KEY Challenger_uid (Challenger_uid),
   ADD KEY Defender_uid (Defender_uid ) ;


-- added max. number of incoming ladder-challenges
ALTER TABLE TournamentLadderProps
   ADD MaxDefenses tinyint unsigned NOT NULL,
   ADD MaxDefenses1 tinyint unsigned NOT NULL DEFAULT '0',
   ADD MaxDefenses2 tinyint unsigned NOT NULL DEFAULT '0',
   ADD MaxDefensesStart1 tinyint unsigned NOT NULL DEFAULT '0',
   ADD MaxDefensesStart2 tinyint unsigned NOT NULL DEFAULT '0' ;

ALTER TABLE TournamentLadder
   ADD ChallengesIn tinyint unsigned NOT NULL DEFAULT '0' ;


-- added properties for handling tournament-game-end
ALTER TABLE TournamentLadderProps
   ADD GameEndNormal enum('CH_ABOVE','CH_BELOW','SWITCH','DF_BELOW','DF_LAST') NOT NULL DEFAULT 'CH_ABOVE',
   ADD GameEndJigo enum('NO_CHANGE','CH_ABOVE','CH_BELOW') NOT NULL DEFAULT 'CH_BELOW',
   ADD GameEndTimeout enum('NO_CHANGE','CH_ABOVE','CH_BELOW','SWITCH','DF_BELOW','DF_LAST','DF_DEL') NOT NULL DEFAULT 'DF_BELOW' ;


-- added SCORE-status for signaling game-end
ALTER TABLE TournamentGames
   MODIFY Status enum('INIT','PLAY','SCORE','DONE') NOT NULL DEFAULT 'INIT',
   ADD INDEX gid (gid) ;

-- Clock for tournament-cron
INSERT INTO Clock SET ID=205,Lastchanged=0 ;


-- handle tournament-game-ending for tournament-cron
ALTER TABLE TournamentGames
   DROP INDEX tid_Status,
   ADD INDEX tid (tid),
   ADD INDEX Status (Status) ;


-- added property for handling tournament-game-end challenger-timeout
ALTER TABLE TournamentLadderProps
   CHANGE GameEndTimeout GameEndTimeoutWin enum('NO_CHANGE','CH_ABOVE','CH_BELOW','SWITCH','DF_BELOW','DF_LAST','DF_DEL') NOT NULL DEFAULT 'DF_BELOW',
   ADD GameEndTimeoutLoss enum('NO_CHANGE','CH_LAST','CH_DEL') NOT NULL DEFAULT 'CH_LAST' ;


-- added property to control waiting-time to rematch user
ALTER TABLE TournamentLadderProps
   ADD ChallengeRematchWait smallint unsigned NOT NULL DEFAULT '0' AFTER ChallengeRangeAbsolute ;

ALTER TABLE TournamentGames
   MODIFY Status enum('INIT','PLAY','SCORE','WAIT','DONE') NOT NULL default 'INIT',
   ADD TicksDue int NOT NULL DEFAULT '0' AFTER Status,
   DROP INDEX Status,
   ADD INDEX Status_Ticks (Status,TicksDue) ;


-- added max. number of outgoing ladder-challenges
ALTER TABLE TournamentLadderProps
   ADD MaxChallenges tinyint unsigned NOT NULL DEFAULT '0' AFTER MaxDefensesStart2 ;

ALTER TABLE TournamentLadder
   ADD ChallengesOut tinyint unsigned NOT NULL DEFAULT '0' ;


-- added optional percentage of ladder-users above current pos that can be challenged
ALTER TABLE TournamentLadderProps
   ADD ChallengeRangeRelative tinyint unsigned NOT NULL DEFAULT '0' AFTER ChallengeRangeAbsolute ;


-- added option for challenge-range for theoretical pos in ladder by rating-order
ALTER TABLE TournamentLadderProps
   ADD ChallengeRangeRating smallint NOT NULL DEFAULT '-32768' AFTER ChallengeRangeRelative ;


-- added manual handicap-types BLACK/WHITE for tournaments
ALTER TABLE TournamentRules
   MODIFY Handicaptype enum('CONV','PROPER','NIGIRI','DOUBLE','BLACK','WHITE') NOT NULL default 'CONV' ;


-- added flags to handle admin-rights for TD
ALTER TABLE TournamentDirector
   ADD Flags smallint unsigned NOT NULL DEFAULT '0' AFTER uid ;


-- added tournament-flags for lock-status
ALTER TABLE Tournament
   ADD Flags smallint unsigned NOT NULL DEFAULT '0' AFTER Status ;


-- handling long-user-absence for tournaments
ALTER TABLE TournamentLadderProps
   ADD UserAbsenceDays tinyint unsigned NOT NULL DEFAULT '0',
   ADD UserAbsenceAction enum('LAST','DEL') NOT NULL DEFAULT 'DEL' ;
ALTER TABLE TournamentLadderProps
   ADD INDEX UserAbsenceDays (UserAbsenceDays) ;

ALTER TABLE Players
   ADD UseVacation tinyint unsigned NOT NULL DEFAULT '0' AFTER OnVacation ;
UPDATE Players
   SET UseVacation=ROUND(OnVacation)+1 WHERE OnVacation > 0 AND UseVacation = 0 ;

-- Clock for (daily) tournament-cron
INSERT INTO Clock SET ID=206, Lastchanged=0 ;


-- reduce long-user-absence handling to one option, so remove field with action-choice
ALTER TABLE TournamentLadderProps
   DROP COLUMN UserAbsenceAction ;


-- added tournament-game stats for tournament-participant
ALTER TABLE TournamentParticipant
   ADD Finished mediumint unsigned NOT NULL default '0',
   ADD Won mediumint unsigned NOT NULL default '0',
   ADD Lost mediumint unsigned NOT NULL default '0' ;


-- added history for tournament-ladder-ranks
ALTER TABLE TournamentLadder
   ADD HistoryRank smallint unsigned NOT NULL default '0' AFTER BestRank,
   ADD PeriodRank smallint unsigned NOT NULL default '0' AFTER BestRank,
   ADD StartRank smallint unsigned NOT NULL default '0' AFTER BestRank ;

-- added table for tournament-runtime-properties
CREATE TABLE TournamentExtension (
   tid int NOT NULL,
   Property smallint unsigned NOT NULL,
   IntValue int NOT NULL DEFAULT '0',
   DateValue datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   Lastchanged datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   ChangedBy varchar(54) NOT NULL DEFAULT '',
   PRIMARY KEY (tid,Property),
   KEY DateValue (DateValue)
) ENGINE=MyISAM;

-- added length of rank-period in months
ALTER TABLE TournamentLadderProps
   ADD RankPeriodLength tinyint unsigned NOT NULL DEFAULT '1' ;


-- added note with reason for tournament-locking
ALTER TABLE Tournament
   ADD LockNote varchar(255) NOT NULL default '' ;


-- store global forum-read date and NEW-flag in Players-table
ALTER TABLE Players
   ADD ForumReadNew tinyint NOT NULL default '0' AFTER BlockReason,
   ADD ForumReadTime datetime NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER BlockReason ;

-- migration of global forum-read data
UPDATE Players AS P INNER JOIN Forumreads AS FR ON FR.User_ID=P.ID
   SET P.ForumReadTime=FR.Time, P.ForumReadNew=FR.HasNew WHERE FR.Forum_ID=0 AND FR.Thread_ID=0 ;

-- cleanup, later eventually
DELETE FROM Forumreads
   WHERE Forum_ID=0 AND Thread_ID=0 ;


-- changed Ruleset from scoring-method to real ruleset for new game
ALTER TABLE Waitingroom
   DROP COLUMN Ruleset ;
ALTER TABLE Waitingroom
   ADD Ruleset enum('JAPANESE','CHINESE') NOT NULL DEFAULT 'JAPANESE' AFTER Time ;

-- added Ruleset for new game
ALTER TABLE Games
   ADD Ruleset enum('JAPANESE','CHINESE') NOT NULL DEFAULT 'JAPANESE' AFTER ToMove_ID ;

-- added Ruleset for tournament-rules
ALTER TABLE TournamentRules
   ADD Ruleset enum('JAPANESE','CHINESE') NOT NULL DEFAULT 'JAPANESE' AFTER Flags ;


-- redefine tournament-round for round-robin tournaments: only one set of rules, shorter status-values
DROP TABLE IF EXISTS TournamentRound ;

CREATE TABLE TournamentRound (
   ID int NOT NULL auto_increment,
   tid int NOT NULL,
   Round tinyint unsigned NOT NULL DEFAULT '1',
   Status enum('INIT','POOL','PAIR','GAME','DONE') NOT NULL DEFAULT 'INIT',
   MinPoolSize smallint unsigned NOT NULL DEFAULT '0',
   MaxPoolSize smallint unsigned NOT NULL DEFAULT '0',
   PoolCount smallint unsigned NOT NULL DEFAULT '0',
   Lastchanged datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   ChangedBy varchar(54) NOT NULL DEFAULT '',
   PRIMARY KEY (ID),
   UNIQUE KEY tidRound (tid,Round)
) ENGINE=MyISAM ;


-- [cleanup] renamed/resized fields
ALTER TABLE TournamentRound
   MODIFY MinPoolSize tinyint unsigned NOT NULL DEFAULT '0',
   MODIFY MaxPoolSize tinyint unsigned NOT NULL DEFAULT '0',
   CHANGE PoolCount MaxPoolCount smallint unsigned NOT NULL DEFAULT '0' ;

-- add PLAY-status
ALTER TABLE TournamentRound
   MODIFY Status enum('INIT','POOL','PAIR','GAME','PLAY','DONE') NOT NULL DEFAULT 'INIT' ;


-- remove GAME-status
ALTER TABLE TournamentRound
   MODIFY Status enum('INIT','POOL','PAIR','PLAY','DONE') NOT NULL DEFAULT 'INIT' ;


-- table to assign users to pools
CREATE TABLE TournamentPool (
   ID int NOT NULL auto_increment,
   tid int NOT NULL,
   Round tinyint unsigned NOT NULL,
   Pool smallint unsigned NOT NULL,
   uid int NOT NULL DEFAULT '0',
   GamesRun smallint unsigned NOT NULL DEFAULT '0',
   PRIMARY KEY (ID),
   KEY tidRoundPool (tid,Round,Pool),
   KEY uid (uid)
) ENGINE=MyISAM ;


-- define actual pool-size and pool-count to be used for pooling tournament round
ALTER TABLE TournamentRound
   ADD PoolSize tinyint unsigned NOT NULL DEFAULT '0' AFTER MaxPoolCount,
   ADD Pools smallint unsigned NOT NULL DEFAULT '0' AFTER MaxPoolCount ;


-- [field-size] reducing max. pool-size (100->25), TournamentPool.GamesRun: smallint -> tinyint
ALTER TABLE TournamentPool
   MODIFY GamesRun tinyint unsigned NOT NULL DEFAULT '0' ;


-- [table-size] removed ChangedBy-field, saving size for expected-big-table
ALTER TABLE TournamentGames
   DROP COLUMN ChangedBy ;


-- table-column-set for pool-view for round-robin-tournament
ALTER TABLE ConfigPages
   ADD ColumnsTournamentPoolView int NOT NULL DEFAULT -1 ;


-- added TournamentRound.ID for round-robin tournaments
ALTER TABLE TournamentGames
   ADD Round_ID int NOT NULL default '0' AFTER tid,
   DROP INDEX tid,
   ADD INDEX tidRound (tid,Round_ID) ;

-- remove calculated field
ALTER TABLE TournamentPool
   DROP COLUMN GamesRun ;


-- added size-field and filter on tournament-list
ALTER TABLE TournamentRules
   ADD INDEX Size (Size);


-- added Rank to store users place within tournament-pool
ALTER TABLE TournamentPool
   ADD Rank tinyint NOT NULL DEFAULT '-100',
   ADD INDEX Rank (Rank) ;


-- added TournamentGames.Pool for pool-resulting
ALTER TABLE TournamentGames
   ADD Pool smallint unsigned NOT NULL DEFAULT '0' AFTER Round_ID,
   DROP INDEX tidRound,
   ADD INDEX tidRoundPool (tid,Round_ID,Pool) ;


-- added field for start/next-round handling for tournament-participant
ALTER TABLE TournamentParticipant
   ADD NextRound tinyint unsigned NOT NULL DEFAULT '0' AFTER StartRound ;


-- added Players.LastQuickAccess for quick-suite usage
ALTER TABLE Players
   ADD LastQuickAccess datetime NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER Lastaccess ;


-- [field-size] COL TournamentProperties.UserMinRating : double -> float -> smallint
-- [field-size] COL TournamentProperties.UserMaxRating : double -> float -> smallint
ALTER TABLE TournamentProperties
   MODIFY `UserMinRating` smallint NOT NULL default '-9999',
   MODIFY `UserMaxRating` smallint NOT NULL default '-9999' ;

-- [field-size] COL Waitingroom.Ratingmin : double -> float -> smallint
-- [field-size] COL Waitingroom.Ratingmax : double -> float -> smallint
ALTER TABLE Waitingroom
   MODIFY `Ratingmin` smallint NOT NULL default '-9999',
   MODIFY `Ratingmax` smallint NOT NULL default '-9999' ;


-- removed admin-option show-time (always showed now)
UPDATE Players
   SET AdminOptions=AdminOptions & ~0x40 WHERE AdminOptions > 0 ;


-- replaced Players.MayPostOnForum with user admin-options
UPDATE Players
   SET AdminOptions = AdminOptions | 0x4000 WHERE MayPostOnForum='M' ;
UPDATE Players
   SET AdminOptions = AdminOptions | 0x2000 WHERE MayPostOnForum='N' ;
ALTER TABLE Players
   DROP COLUMN MayPostOnForum ;


-- added multi-player-game: Team-Go, Zen-Go
ALTER TABLE Waitingroom
   ADD GameType enum('GO','TEAM_GO','ZEN_GO') NOT NULL default 'GO' AFTER Time ;
ALTER TABLE Waitingroom
   ADD GamePlayers char(5) NOT NULL default '' AFTER GameType ;
ALTER TABLE Waitingroom
   ADD gid int NOT NULL default 0 AFTER uid ;

-- added multi-player-game: Team-Go, Zen-Go
ALTER TABLE Games
   ADD GameType enum('GO','TEAM_GO','ZEN_GO') NOT NULL default 'GO' AFTER ToMove_ID ;
ALTER TABLE Games
   ADD GamePlayers char(5) NOT NULL default '' AFTER GameType ;
ALTER TABLE Games
   MODIFY Status enum('SETUP','INVITED','PLAY','PASS','SCORE','SCORE2','FINISHED') NOT NULL default 'INVITED' ;

-- added multi-player-game: Team-Go, Zen-Go
CREATE TABLE GamePlayers (
   ID int NOT NULL auto_increment,
   gid int NOT NULL,
   GroupColor enum('B','W','G1','G2','BW') NOT NULL default 'BW',
   GroupOrder tinyint signed NOT NULL default 0,
   uid int NOT NULL default 0,
   Flags smallint unsigned NOT NULL default 0,
   PRIMARY KEY (ID),
   KEY gidGroup (gid,GroupColor,GroupOrder),
   KEY uid (uid)
) ENGINE=MyISAM ;


-- fix wrong accepted/declined invitation-messages adding Game_ID of parent message (invitation/dispute)
-- * 1. count (ca. 270.000 rows - 15s)
SELECT COUNT(*) FROM Messages AS M WHERE M.Subject like 'Game invitation%' AND M.ReplyTo>0 AND M.Game_ID=0 ;
# not all of these are invitation-msgs, some are also replies to invitations
-- * 2. fix (repeat till change-count is 0)
UPDATE Messages AS M, Messages AS MSRC
   SET M.Game_ID=MSRC.Game_ID
   WHERE M.ReplyTo=MSRC.ID AND M.Subject LIKE 'Game invitation%' AND M.ReplyTo>0 AND M.Game_ID=0 AND MSRC.Game_ID>0 ;
-- * 3. control-count, repeat step (2) till count is 0 (ca. 4min)
SELECT COUNT(*) FROM Messages AS M INNER JOIN Messages AS MSRC ON MSRC.ID=M.ReplyTo WHERE M.Subject like 'Game invitation%' AND M.ReplyTo>0 AND M.Game_ID=0 AND MSRC.Game_ID>0 ;

-- added message-flags to mark as bulk with multiple receivers
ALTER TABLE Messages
   ADD Flags tinyint unsigned NOT NULL default '0' AFTER Type ;


-- number of setup MP-games to optimize status-loading
ALTER TABLE Players
   ADD GamesMPG smallint unsigned NOT NULL default '0' AFTER Lost ;


-- add indicator for game-result set by admin
ALTER TABLE Games
   MODIFY Flags set('Ko','HiddenMsg','AdmResult') NOT NULL default '' ;


-- added table with rating-changes by admin
CREATE TABLE RatingChangeAdmin (
   ID int NOT NULL auto_increment,
   uid int NOT NULL,
   Created datetime NOT NULL default '0000-00-00 00:00:00',
   Changes tinyint NOT NULL default 0,
   Rating double NOT NULL default '-9999',
   PRIMARY KEY (ID),
   KEY uid (uid),
   KEY Created (Created)
) ENGINE=MyISAM ;


-- added aggregate of tournaments registered participants in Tournament-table
ALTER TABLE Tournament
   ADD RegisteredTP smallint NOT NULL default '0' AFTER CurrentRound ;


-- drop unused column TranslationPages.ID
ALTER TABLE TranslationPages
   DROP COLUMN ID,
   ADD PRIMARY KEY (Page) ;


-- added table for tournament-results
CREATE TABLE TournamentResult (
   ID int NOT NULL auto_increment,
   tid int NOT NULL,
   uid int NOT NULL,
   rid int NOT NULL default 0,
   Rating double NOT NULL default '-9999',
   Type tinyint unsigned NOT NULL default 0,
   StartTime datetime NOT NULL default '0000-00-00 00:00:00',
   EndTime datetime NOT NULL default '0000-00-00 00:00:00',
   Round tinyint unsigned NOT NULL default '1',
   Rank smallint unsigned NOT NULL default '0',
   RankKept smallint unsigned NOT NULL default '0',
   PRIMARY KEY (ID),
   KEY tidRank (tid,Rank),
   KEY uid (uid)
) ENGINE=MyISAM ;

-- added config for crowning of ladder-king
ALTER TABLE TournamentLadderProps
   ADD CrownKingHours smallint unsigned NOT NULL default 0 ;

-- Clock for (hourly) tournament-cron
INSERT INTO Clock SET ID=207, Lastchanged=0 ;

-- table-column-set for tournament-results
ALTER TABLE ConfigPages
   ADD ColumnsTournamentResults int NOT NULL DEFAULT -1 AFTER ColumnsTDTournamentParticipants ;


-- added Reference-field to allow edit links with FAQ-editor
ALTER TABLE FAQ
   ADD Reference varchar(255) NOT NULL default '' ;
ALTER TABLE FAQlog
   ADD Reference varchar(255) NOT NULL default '' AFTER Answer ;

-- added table to store links with URL, text and extra-description
CREATE TABLE Links (
  ID int NOT NULL auto_increment,
  Parent int NOT NULL default '0',
  Level tinyint unsigned NOT NULL default '0',
  SortOrder smallint NOT NULL default '0',
  Question int NOT NULL default '0',
  Answer int NOT NULL default '0',
  Hidden enum('N','Y') NOT NULL default 'N',
  Reference varchar(255) NOT NULL default '',
  PRIMARY KEY (ID)
) ENGINE=MyISAM ;


-- added table to store introduction-entries
CREATE TABLE Intro (
  ID int NOT NULL auto_increment,
  Parent int NOT NULL default '0',
  Level tinyint unsigned NOT NULL default '0',
  SortOrder smallint NOT NULL default '0',
  Question int NOT NULL default '0',
  Answer int NOT NULL default '0',
  Hidden enum('N','Y') NOT NULL default 'N',
  Reference varchar(255) NOT NULL default '',
  PRIMARY KEY (ID)
) ENGINE=MyISAM ;


-- added table to store tournament news
CREATE TABLE TournamentNews (
  ID int NOT NULL auto_increment,
  tid int NOT NULL,
  uid int NOT NULL,
  Status enum('NEW','SHOW','ARCHIVE') NOT NULL default 'NEW',
  Flags tinyint unsigned NOT NULL default 0,
  Published datetime NOT NULL default '0000-00-00 00:00:00',
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  ChangedBy varchar(54) NOT NULL default '',
  Subject varchar(255) NOT NULL,
  Text text NOT NULL,
  PRIMARY KEY  (ID),
  KEY tidPublished (tid,Published),
  KEY Status (Status)
) ENGINE=MyISAM ;


-- added DELETE-status to delete tournament-news
ALTER TABLE TournamentNews
   MODIFY Status enum('NEW','SHOW','ARCHIVE','DELETE') NOT NULL default 'NEW' ;


-- added date to first check for crowning of ladder-king
ALTER TABLE TournamentLadderProps
   ADD CrownKingStart datetime NOT NULL default '0000-00-00 00:00:00' ;
UPDATE TournamentLadderProps
   SET CrownKingStart=NOW() WHERE CrownKingHours > 0 ;


-- added Bulletin-table to store bulletins
CREATE TABLE Bulletin (
  ID int NOT NULL auto_increment,
  uid int NOT NULL,
  Category enum('MAINT','ADM_MSG','TOURNEY','TNEWS','PRIV_MSG','AD') NOT NULL default 'PRIV_MSG',
  Status enum('NEW','PENDING','HIDDEN','SHOW','ARCHIVE','DELETE') NOT NULL default 'NEW',
  TargetType enum('ALL','TD','TP','UL') NOT NULL,
  PublishTime datetime NOT NULL default '0000-00-00 00:00:00',
  ExpireTime datetime NOT NULL default '0000-00-00 00:00:00',
  tid int NOT NULL default 0,
  AdminNote varchar(255) NOT NULL default '',
  Subject varchar(255) NOT NULL,
  Text text NOT NULL,
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (ID),
  KEY Status (Status)
) ENGINE=MyISAM ;


-- quick-read-count for NEW-bulletins for user
ALTER TABLE Players
   ADD CountBulletinNew smallint NOT NULL default '-1' AFTER CountFeatNew ;


-- added BulletinRead-table to store if user has read bulletins
CREATE TABLE BulletinRead (
  bid int NOT NULL,
  uid int NOT NULL,
  PRIMARY KEY (bid,uid)
) ENGINE=MyISAM ;


-- add read-counter for Bulletins
ALTER TABLE Bulletin
   ADD CountReads mediumint unsigned NOT NULL default '0' AFTER tid ;


-- added BulletinTarget-table to store user-list to show specific bulletin to
CREATE TABLE BulletinTarget (
  bid int NOT NULL,
  uid int NOT NULL,
  PRIMARY KEY (bid,uid)
) ENGINE=MyISAM ;


-- table-column-set for bulletin-list
ALTER TABLE ConfigPages
   ADD ColumnsBulletinList int NOT NULL default -1 AFTER ColumnsGamesObservedAll2 ;


-- added user-config to skip bulletin-categories
ALTER TABLE Players
   ADD SkipBulletin tinyint unsigned NOT NULL default 0x4 ;


-- added target-type for bulletin with multi-player-game
ALTER TABLE Bulletin
   MODIFY TargetType enum('ALL','TD','TP','UL','MPG') NOT NULL,
   ADD gid int NOT NULL default 0 AFTER tid ;


-- added bulletin admin-flags
ALTER TABLE Bulletin
   ADD Flags tinyint unsigned NOT NULL default 0 AFTER TargetType ;


-- added LockVersion-field for optimistic locking
ALTER TABLE Bulletin
   ADD LockVersion tinyint unsigned NOT NULL default 0 AFTER uid ;


-- renamed Bulletin.Status: HIDDEN -> REJECTED
ALTER TABLE Bulletin
   MODIFY Status enum('NEW','PENDING','REJECTED','SHOW','ARCHIVE','DELETE') NOT NULL default 'NEW' ;


-- added bulletin-category for Feature-news
ALTER TABLE Bulletin
   MODIFY `Category` enum('MAINT','ADM_MSG','TOURNEY','TNEWS','FEATURE','PRIV_MSG','AD') NOT NULL default 'PRIV_MSG' ;


-- feature-cleanup: table-name, feature-status NEW/VOTE, drop IP for votes
RENAME TABLE FeatureList TO Feature;

ALTER TABLE Feature
   MODIFY Status enum('NEW','VOTE','WORK','DONE','LIVE','NACK') NOT NULL default 'NEW',
   ADD Size enum('?','EPIC','XXL','XL','L','M','S') NOT NULL default '?' AFTER Status ;
UPDATE Feature SET Status='VOTE' WHERE Status='NEW' ;

ALTER TABLE FeatureVote
   DROP COLUMN IP;


-- added Survery-tables to store survey, survey-option, survey-vote
CREATE TABLE Survey (
  ID int NOT NULL auto_increment,
  uid int NOT NULL,
  SurveyType enum('POINTS','SUM','SINGLE','MULTI') NOT NULL default 'POINTS',
  Status enum('NEW','ACTIVE','CLOSED','DELETE') NOT NULL default 'NEW',
  Flags tinyint unsigned NOT NULL default 0,
  MinPoints tinyint NOT NULL default 0,
  MaxPoints tinyint NOT NULL default 0,
  Created datetime NOT NULL default '0000-00-00 00:00:00',
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  Title varchar(255) NOT NULL,
  PRIMARY KEY (ID),
  KEY uid (uid),
  KEY Status (Status)
) ENGINE=MyISAM ;

CREATE TABLE SurveyOption (
  ID int NOT NULL auto_increment,
  sid int NOT NULL,
  Tag tinyint unsigned NOT NULL default 0,
  SortOrder tinyint unsigned NOT NULL default 0,
  MinPoints tinyint NOT NULL default 0,
  UserCount mediumint unsigned NOT NULL default 0,
  Score int NOT NULL default 0,
  Title varchar(255) NOT NULL,
  Text text NOT NULL,
  PRIMARY KEY (ID),
  UNIQUE KEY sidTag (sid,Tag)
) ENGINE=MyISAM ;

CREATE TABLE SurveyVote (
  sid int NOT NULL,
  uid int NOT NULL default 0,
  Tag tinyint unsigned NOT NULL default 0,
  Points tinyint NOT NULL default 0,
  PRIMARY KEY (sid,uid,Tag)
) ENGINE=MyISAM ;


-- renamed field Survey.SurveyType -> Type
ALTER TABLE Survey
   CHANGE SurveyType Type enum('POINTS','SUM','SINGLE','MULTI') NOT NULL default 'POINTS' ;


-- added defaults to avoid warning on INSERT .. ON DUPLICATE KEY ...
ALTER TABLE SurveyOption
   MODIFY sid int NOT NULL default 0,
   MODIFY Title varchar(255) NOT NULL default '' ;

-- refactor SurveyOption.UserCount into Survey-table
ALTER TABLE SurveyOption
   DROP COLUMN UserCount ;
ALTER TABLE Survey
   ADD UserCount mediumint unsigned not null default '0' AFTER MaxPoints ;


-- normalize FK SurveyVote.sid/Tag to use SurveyVote.soid = SurveyOption.ID
RENAME TABLE SurveyVote TO SurveyVote_old ;

CREATE TABLE SurveyVote (
  soid int NOT NULL,
  uid int NOT NULL default 0,
  Points tinyint NOT NULL default 0,
  PRIMARY KEY (soid,uid)
) ENGINE=MyISAM ;

-- migrate data into new structure
INSERT INTO SurveyVote (soid,uid,Points)
   SELECT SOPT.ID, SV.uid, SV.Points
   FROM SurveyVote_old AS SV INNER JOIN SurveyOption AS SOPT ON SOPT.sid=SV.sid AND SOPT.Tag=SV.Tag ;

DROP TABLE SurveyVote_old ;


-- added survey header-text
ALTER TABLE Survey
   ADD Header text NOT NULL ;


-- added SurveyUser-table to store user-list to restrict voting for survey
CREATE TABLE SurveyUser (
  sid int NOT NULL,
  uid int NOT NULL,
  PRIMARY KEY (sid,uid)
) ENGINE=MyISAM ;


-- allow pure-text ID-button
ALTER TABLE Players
   MODIFY Button tinyint signed NOT NULL default '0' ;


-- added field with snapshot of current game-position for showing thumbnail
ALTER TABLE Games
   ADD Snapshot varchar(216) NOT NULL default '' ;

-- execute fix-script to add Games.Snapshot for ALL games:
-- (may need to run it several times with smaller limit and start-game-id to skip faulty games):
--    'scripts/fix_game_snapshot.php?limit=999999&sleep=1'


-- added Shape-Games
CREATE TABLE Shape (
  ID int NOT NULL auto_increment,
  uid int NOT NULL,
  Name varchar(40) NOT NULL default '',
  Size tinyint unsigned NOT NULL,
  Flags tinyint unsigned NOT NULL default 0,
  Snapshot varchar(216) NOT NULL default '',
  Notes text NOT NULL,
  Created datetime NOT NULL default '0000-00-00 00:00:00',
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY (ID),
  KEY uid (uid),
  KEY Name (Name)
) ENGINE=MyISAM ;


-- make Shape.Name unique
ALTER TABLE Shape
   DROP KEY Name,
   ADD UNIQUE KEY Name (Name) ;


-- added fields to handle shape-games
ALTER TABLE Games
   ADD ShapeID int unsigned NOT NULL default '0' AFTER tid,
   ADD ShapeSnapshot varchar(255) NOT NULL default '' ;


-- added fields to handle shape-games
ALTER TABLE Waitingroom
   ADD ShapeID int unsigned NOT NULL default '0' AFTER gid,
   ADD ShapeSnapshot varchar(255) NOT NULL default '' AFTER SameOpponent ;


-- added fields to handle shape-games
ALTER TABLE TournamentRules
   ADD ShapeID int unsigned NOT NULL default '0' AFTER tid,
   ADD ShapeSnapshot varchar(255) NOT NULL default '' AFTER Rated ;


-- added handicap-type for auction-komi
ALTER TABLE Waitingroom
   MODIFY Handicaptype enum('conv','proper','nigiri','double','black','white','auko') NOT NULL default 'conv' ;


-- added handicap-types for auction-komi, divide & choose
DELETE FROM Waitingroom
   WHERE Handicaptype='auko' ;
ALTER TABLE Waitingroom
   MODIFY Handicaptype enum('conv','proper','nigiri','double','black','white','auko_sec','auko_opn','div_ykic','div_ikyc') NOT NULL default 'conv' ;

-- added new game-status KOMI to negotiate fair-komi
ALTER TABLE Games
   MODIFY Status enum('KOMI','SETUP','INVITED','PLAY','PASS','SCORE','SCORE2','FINISHED') NOT NULL default 'INVITED' ;

-- store game-setings for rematch/komi-negotiation/invitations
ALTER TABLE Games
   ADD GameSetup varchar(255) NOT NULL default '' ;

-- db-cleanup for consistency: renamed fields Waitingroom.Ratingmin/max -> RatingMin/Max
ALTER TABLE Waitingroom
   CHANGE Ratingmin RatingMin smallint(6) NOT NULL default '-9999',
   CHANGE Ratingmax RatingMax smallint(6) NOT NULL default '-9999' ;


-- extended Profiles.Name
ALTER TABLE Profiles
   MODIFY Name varchar(60) NOT NULL default '' ;


-- added board-flags for marking last-capture
ALTER TABLE ConfigBoard
   ADD BoardFlags tinyint unsigned NOT NULL default 0 AFTER Woodcolor ;


-- added field to control if win-by-timeout should be rejected for winner
ALTER TABLE Players
   ADD RejectTimeoutWin tinyint signed NOT NULL default -1 ;


-- remove old unused table for rating-changes
DROP TABLE IF EXISTS RatingChange ;


-- speed-up translators-query on people-page
ALTER TABLE Translationlog
   ADD KEY PlayerLang (Player_ID,Language_ID) ;


-- added table for used IP and user-id to track too-much-requests
CREATE TABLE IpStats (
   uid int NOT NULL default 0,
   Page char(4) NOT NULL default '',
   IP varchar(16) NOT NULL,
   Counter int unsigned NOT NULL default 0,
   Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
   PRIMARY KEY (uid,Page,IP)
) ENGINE=MyISAM ;


-- create day/week-profile for moving
CREATE TABLE MoveStats (
   uid int NOT NULL default 0,
   SlotTime smallint unsigned NOT NULL default 0,
   SlotWDay tinyint NOT NULL default 0,
   SlotWeek tinyint NOT NULL default 0,
   Counter mediumint unsigned NOT NULL default 0,
   PRIMARY KEY (uid,SlotTime,SlotWDay,SlotWeek)
) ENGINE=MyISAM ;


-- use alias for removed tzdata of 'China/Beijing'-timezone
UPDATE Players
   SET Timezone='Asia/Shanghai' WHERE Timezone ='China/Beijing' ;


-- delete invitations older than 6 months
SELECT COUNT(*) FROM Games
   WHERE Status='INVITED' AND Lastchanged <= NOW() - INTERVAL 6 MONTH ;
DELETE FROM Games
   WHERE Status='INVITED' AND Lastchanged <= NOW() - INTERVAL 6 MONTH ;


-- fix Players.LastQuickAccess if LastMove > Lastaccess
SELECT ID, Handle, LastQuickAccess, Lastaccess, LastMove, TIMEDIFF(Lastaccess,LastMove) AS TDiff, TIME_TO_SEC(timediff(Lastaccess,LastMove)) AS TSec
   FROM Players WHERE LastMove >0 AND LastQuickAccess < LastMove HAVING TSec < 0 ;
UPDATE Players
   SET LastQuickAccess = LastMove WHERE LastMove >0 AND LastQuickAccess < LastMove AND TIME_TO_SEC(TIMEDIFF(Lastaccess,LastMove)) < 0 ;


-- rename users with spaces in Handle: 1. find them, 2. contact them, 3. rename Handle via user-admin-page
SELECT ID, Handle, Email, Lastaccess, Running, LastMove
   FROM Players WHERE Handle LIKE '% %' ORDER BY Lastaccess ;


-- add creation-date for cleanup
ALTER TABLE IpStats
   ADD Created datetime NOT NULL default '0000-00-00 00:00:00' AFTER Counter,
   ADD KEY Created (Created) ;


-- fix Starttime for invitations
UPDATE Games
   SET Starttime=Lastchanged WHERE Status='INVITED' AND Starttime=0 ;


-- speed up sending mail-notifications in halfhourly-cron-script
ALTER TABLE Players
   ADD KEY Notify (Notify) ;


-- speed up forum-search on specific user
ALTER TABLE Posts
   ADD KEY User_ID (User_ID);


-- cleanup invalid emails (no '@' found or containing space)
SELECT Handle, Lastaccess, Email
   FROM Players WHERE Email>'' AND (Email LIKE '% %' OR NOT Email LIKE '%@%') ;
UPDATE Players
   SET Email='' WHERE Email>'' AND (Email LIKE '% %' OR NOT Email LIKE '%@%') ;


-- mark notify-types for mail-notifications
ALTER TABLE Players
   ADD NotifyFlags tinyint unsigned NOT NULL default '0' AFTER Notify ;
UPDATE Players
   SET NotifyFlags=1 WHERE Notify IN ('NOW','NEXT') ;


-- speed-up cleanup of MoveStats
ALTER TABLE MoveStats
   ADD KEY SlotWeek (SlotWeek) ;


-- store end-time of cron-run
ALTER TABLE Clock
   ADD Finished datetime NOT NULL default '0000-00-00 00:00:00' ;


-- added last-change and status for translations for easier cleanup
ALTER TABLE TranslationTexts
   ADD Status enum('USED','CHECK','ORPHAN') NOT NULL default 'USED',
   ADD Updated datetime NOT NULL default '0000-00-00 00:00:00' ;
ALTER TABLE Translations
   ADD Updated datetime NOT NULL default '0000-00-00 00:00:00' ;


-- add Type to check for double-text of same type
ALTER TABLE TranslationTexts
   ADD Type enum('NONE','FAQ','LINKS','INTRO','SRC') NOT NULL default 'NONE' AFTER Text ;
ALTER TABLE FAQlog
   ADD Type enum('FAQ','Links','Intro') NOT NULL default 'FAQ' AFTER FAQID ;
ALTER TABLE FAQlog
   CHANGE FAQID Ref_ID int(11) NOT NULL default '0' ;

-- add index for faster consistency-checks, add Type to check for double-text of same type
ALTER TABLE TranslationTexts
   ADD KEY Text (Text(4)) ;
ALTER TABLE Translations
   ADD KEY Original_ID (Original_ID) ;
ANALYZE TABLE TranslationTexts;
ANALYZE TABLE Translations;

-- drop unused columns
ALTER TABLE Translationlog
   DROP COLUMN CString ;
ALTER TABLE TranslationTexts
   DROP COLUMN Ref_ID ;


-- fix translation-adjustments in code
-- 1. following selects must results in not more than one entry per select
SELECT * FROM TranslationTexts WHERE Text='Send Message';
SELECT * FROM TranslationTexts WHERE Text='prev page';
SELECT * FROM TranslationTexts WHERE Text='next page';
SELECT * FROM TranslationTexts WHERE Text='Tournament Status';

-- 2. only if criteria in (1) is met, update the following:
UPDATE TranslationTexts SET Text='Send message' WHERE Text='Send Message' LIMIT 1;
UPDATE TranslationTexts SET Text='Prev Page' WHERE Text='prev page' LIMIT 1;
UPDATE TranslationTexts SET Text='Next Page' WHERE Text='next page' LIMIT 1;
UPDATE TranslationTexts SET Text='Tournament Status#tourney' WHERE Text='Tournament Status' LIMIT 1;


-- use alias for removed tzdata of 'China/Shanghai'-timezone
UPDATE Players
   SET Timezone='Asia/Shanghai' WHERE Timezone ='China/Shanghai' ;


-- replace varchars to get fixed-size tables
ALTER TABLE ConfigPages
   MODIFY StatusFolders char(40) NOT NULL default '' ;
ALTER TABLE Folders
   MODIFY Name char(40) NOT NULL,
   MODIFY BGColor char(8) NOT NULL default 'f7f5e3FF',
   MODIFY FGColor char(6) NOT NULL default '000000' ;
ALTER TABLE Forums
   MODIFY Name char(40) NOT NULL default '',
   MODIFY Description char(128) NOT NULL default '',
   MODIFY SortOrder tinyint unsigned NOT NULL default '0' ;
ALTER TABLE IpStats
   MODIFY IP char(16) NOT NULL ;
ALTER TABLE TranslationGroups
   MODIFY Groupname char(32) NOT NULL ;
ALTER TABLE TranslationLanguages
   MODIFY Language char(32) NOT NULL,
   MODIFY Name char(32) NOT NULL ;
ALTER TABLE TranslationPages
   MODIFY Page char(64) NOT NULL ;
ALTER TABLE TournamentExtension
   MODIFY ChangedBy char(54) NOT NULL default '' ;
ALTER TABLE TournamentLadderProps
   MODIFY ChangedBy char(54) NOT NULL default '' ;
ALTER TABLE TournamentProperties
   MODIFY ChangedBy char(54) NOT NULL default '' ;
ALTER TABLE TournamentRound
   MODIFY ChangedBy char(54) NOT NULL default '' ;


-- added some keys for FAQ/Intro/Links for editing
ALTER TABLE FAQ
   ADD KEY Parent (Parent),
   ADD KEY Level (Level) ;
ALTER TABLE Links
   ADD KEY Parent (Parent),
   ADD KEY Level (Level) ;
ALTER TABLE Intro
   ADD KEY Parent (Parent),
   ADD KEY Level (Level) ;


-- cleanup: removed not needed field for tournament-ladder user-absence-tracking
ALTER TABLE Players
   DROP COLUMN UseVacation ;


-- added flag to store detached-tournament-game
ALTER TABLE Games
   MODIFY Flags SET('Ko','HiddenMsg','AdmResult','TGDetached') NOT NULL default '' ;


-- added tournament-log to log actions on tournaments
CREATE TABLE Tournamentlog (
  ID int NOT NULL auto_increment,
  tid int NOT NULL,
  uid int NOT NULL,
  Date datetime NOT NULL default '0000-00-00 00:00:00',
  Type char(2) NOT NULL,
  Object varchar(16) NOT NULL default 'T',
  Action varchar(16) NOT NULL,
  actuid int NOT NULL default '0',
  Message text NOT NULL,
  PRIMARY KEY (ID),
  KEY tid (tid)
) ENGINE=MyISAM ;


-- determine ladder-position for new user
ALTER TABLE TournamentLadderProps
   ADD UserJoinOrder enum('REGTIME','RATING','RANDOM') NOT NULL default 'REGTIME' AFTER GameEndTimeoutLoss ;


-- IMPORTANT NOTE: run this FIX ONLY if you have used the features-feature before on your server!!
-- [FIX] fix negative voted points on UserQuota
CREATE TEMPORARY TABLE fix_uq (
   uid int NOT NULL,
   sumP smallint NOT NULL
   ) SELECT Voter_ID AS uid, SUM(ABS(Points)) AS sumP FROM FeatureVote GROUP BY Voter_ID ;
SELECT * FROM fix_uq AS FIX WHERE FIX.sumP > 0 ;
SELECT UQ.uid, UQ.FeaturePoints FROM UserQuota AS UQ INNER JOIN fix_uq AS FIX ON FIX.uid=UQ.uid WHERE FIX.sumP > 0 ;

-- [FIX] NOTE: 29 is the initial start quota of 25 + 98 (=4*20 +x) days since live-server upgrade on 10-Jun-2012
UPDATE UserQuota AS UQ INNER JOIN fix_uq AS FIX ON FIX.uid=UQ.uid
   SET UQ.FeaturePoints= 29 - FIX.sumP WHERE FIX.sumP > 0;


-- added hourly-cron to cleanup expired cache-entries
INSERT INTO Clock SET ID=208,Lastchanged=0 ;


-- add read-only flag for threads
ALTER TABLE Posts
   ADD Flags tinyint unsigned NOT NULL default '0' AFTER Thread_ID ;


-- fix Posts.Lastchanged for single-post threads that once were hidden due to rejecting or re-editing
UPDATE Posts
   SET Lastchanged=Time WHERE ID=Thread_ID AND PostsInThread=1 AND PosIndex >'' AND Time <> Lastchanged ;

-- trigger recalc of NEW forum-entries
UPDATE Players
   SET ForumReadNew=-1 WHERE ForumReadNew > 0 ;


-- disable added GamesPriority for my running games per default
UPDATE ConfigPages
   SET ColumnsGamesRunningUser2 = ColumnsGamesRunningUser2 & ~0x8000 ;


-- change existing translation-text for waiting-room without losing translations
UPDATE TranslationTexts
   SET Text='All waiting games', Translatable='Y', Updated=NOW()
   WHERE Text='Show all waiting games' LIMIT 1 ;
UPDATE TranslationTexts
   SET Text='Suitable waiting games', Translatable='Y', Updated=NOW()
   WHERE Text='Show suitable games only' LIMIT 1 ;


-- added table to store attached SGFs for games
CREATE TABLE GameSgf (
  gid int NOT NULL,
  uid int NOT NULL,
  Lastchanged datetime NOT NULL default '0000-00-00 00:00:00',
  SgfData blob NOT NULL,
  PRIMARY KEY (gid,uid)
) ENGINE=MyISAM ;


-- added 'AttachedSgf'-flag as indicator for the presence of SGFs for game
ALTER TABLE Games
   MODIFY Flags set('Ko','HiddenMsg','AdmResult','TGDetached','AttachedSgf') NOT NULL default '' ;


-- fix rematch-wait due-time for ladder-tournament-games using 5min-ticks TIMELEFT-clock
-- reduced by 5min-ticks already passed since game ended
-- IMPORTANT NOTE: only run this once!
UPDATE TournamentGames AS TG
   INNER JOIN TournamentLadderProps AS TLP ON TLP.tid=TG.tid
   INNER JOIN Clock
   SET TG.TicksDue=Clock.Ticks + TLP.ChallengeRematchWait*12 - FLOOR((UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(TG.EndTime))/300)
   WHERE TG.Status='WAIT' AND TLP.ChallengeRematchWait > 0 AND TG.TicksDue>0 AND Clock.ID=204 ;

-- fix potential tournament-games with negative due-time
UPDATE TournamentGames
   SET TicksDue=0 WHERE TicksDue < 0 ;