This is basically just a method for any SQL mod to let you see what it is doing by storing everything in a new table. Also, this is an example, you can extend it as you see fit.
Setup the table.
- Code: Select all
delimiter $$
CREATE TABLE `mod_log` (
`ID` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`ActionTime` DATETIME NULL DEFAULT NULL,
`ModName` VARCHAR(50) NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
`ModAction` VARCHAR(1024) NULL DEFAULT '0' COLLATE 'utf8_unicode_ci',
PRIMARY KEY (`ID`)
)
COMMENT='Table for logging things done by SQL mods.'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;
$$
Now set up the stored procedure to add logs to it. The first param when calling it is the name your give your mod (in case you have multiple doing different things) and the second is the message you want to send to the log.
- Code: Select all
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_ModLog`(IN `in_ModName` VARCHAR(50), IN `in_ModAction` VARCHAR(1024))
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY DEFINER
COMMENT ''
BEGIN
INSERT INTO Mod_Log(`ActionTime`,`ModName`,`ModAction`)
VALUES(NOW(),in_ModName,in_ModAction);
END
$$
Now that those are setup, inside any custom queries you make you can call that procedure to add logging information to it.
Example: Say you have another procedure that turns trees into pastries and it has some checks and balances to make sure that birch trees come out as bearclaws and willows come out as a danish. Obviously, this is fake, don't try to turn your forests into a Krispy Kreme.
- Code: Select all
Create Procedure p_KrispyForest()
CALL p_ModLog('KrispyForestUpdate','Starting KrispyUpdate');
Declare something
Declare something_else
set @FillingType = select Filling from FruitTable where sweet = 1;
set @TreeType = Select TreeType from Trees;
CALL p_ModLog('KrispyForestUpdate',CONCAT('Setting Filling to ',@FillingType,' and TreeType to ',@TreeType));
IF @TreeType = Birch Then
CALL p_ModLog('KrispyForestUpdate',CONCAT('Setting FillingType to ',@FillingType,' for birch trees'));
else IF @TreeType = Willow Then
CALL p_ModLog('KrispyForestUpdate',CONCAT('Setting FillingType to ',@FillingType,' for willow trees'));
else
CALL p_ModLog('KrispyForestUpdate',CONCAT('No Trees found for treetype ',@TreeType));
end if;
etc...
Then select * from Mod_Log to see all logs written.
Just a handy tool for going back and seeing what your mods changed and when.