gistfile1.sql

DELIMITER $$

DROP PROCEDURE IF EXISTS addLogPlayerID $$
CREATE PROCEDURE addLogPlayerID()
BEGIN

-- add logPlayerID column safely
IF NOT EXISTS( (SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE()
        AND COLUMN_NAME='logPlayerID' AND TABLE_NAME='tbl_chatlog') ) THEN
    ALTER TABLE `tbl_chatlog` ADD COLUMN `logPlayerID` INT(10) UNSIGNED DEFAULT NULL;
    ALTER TABLE `tbl_chatlog` ADD INDEX (`logPlayerID`);
    ALTER TABLE `tbl_chatlog` ADD CONSTRAINT `tbl_chatlog_ibfk_player_id` FOREIGN KEY (`logPlayerID`) REFERENCES `tbl_playerdata` (`PlayerID`) ON DELETE CASCADE ON UPDATE CASCADE;
    UPDATE 
        `tbl_chatlog`
    INNER JOIN 
        `tbl_playerdata`
    ON 
        `tbl_chatlog`.`logSoldierName` = `tbl_playerdata`.`SoldierName` 
    SET 
        `tbl_chatlog`.`logPlayerID` = `tbl_playerdata`.`PlayerID`
    WHERE 
        `tbl_playerdata`.`SoldierName` <> 'AutoAdmin' 
    AND 
        `tbl_playerdata`.`SoldierName` <> 'AdKats' 
    AND 
        `tbl_playerdata`.`SoldierName` <> 'Server' 
    AND 
        `tbl_playerdata`.`SoldierName` <> 'BanEnforcer'
    AND 
        `tbl_chatlog`.`logPlayerID` IS NULL;
END IF;

END $$

CALL addLogPlayerID() $$

DROP TRIGGER IF EXISTS `tbl_chatlog_player_id_insert`$$
CREATE TRIGGER `tbl_chatlog_player_id_insert` BEFORE INSERT ON `tbl_chatlog`
 FOR EACH ROW BEGIN 
        SET NEW.logPlayerID = (SELECT `tbl_playerdata`.`PlayerID` FROM `tbl_server`
                            INNER JOIN `tbl_games` ON `tbl_server`.`GameID` = `tbl_games`.`GameID`
                            INNER JOIN `tbl_playerdata` ON `tbl_games`.`GameID` = `tbl_playerdata`.`GameID`
                            WHERE `tbl_playerdata`.`SoldierName` = NEW.logSoldierName AND `tbl_server`.`ServerID` = NEW.ServerID LIMIT 1);
    END
$$

DROP TRIGGER IF EXISTS `Player_Update_BlankDataFix`$$
CREATE TRIGGER
    Player_Update_BlankDataFix
BEFORE UPDATE ON
    tbl_playerdata
FOR EACH ROW
BEGIN
    IF (NEW.SoldierName IS NULL OR CHAR_LENGTH(NEW.SoldierName) = 0) AND OLD.SoldierName IS NOT NULL
        THEN SET NEW.SoldierName = OLD.SoldierName;
    END IF;
    IF (NEW.EAGUID IS NULL OR CHAR_LENGTH(NEW.EAGUID) = 0) AND OLD.EAGUID IS NOT NULL
        THEN SET NEW.EAGUID = OLD.EAGUID;
    END IF;
    IF (NEW.PBGUID IS NULL OR CHAR_LENGTH(NEW.PBGUID) = 0) AND OLD.PBGUID IS NOT NULL
        THEN SET NEW.PBGUID = OLD.PBGUID;
    END IF;
    IF (NEW.IP_Address IS NULL OR CHAR_LENGTH(NEW.IP_Address) = 0) AND OLD.IP_Address IS NOT NULL
        THEN SET NEW.IP_Address = OLD.IP_Address;
    END IF;
END;
$$
DELIMITER ;