Use proper DB schema with InnoDB as option

[J0s3f]

Please add the option for those of us who want to use a real DB (as opposed to MyISAM) to use InnoDB with a proper DB schema (using foreign keys). Here is the DB I use atm. The only thing that has to be changed in BigBrother is the creation of the database and BB has to add new actions into the bbactions db. (This is not really strictly necessary, but I like to have all usefull information inside the database, not some "magic numbers" you have to lookup somewhere else. Also it makes my big-brother view pretty) The actions which are in the Wiki are in the SQLdump here too:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT=0;
START TRANSACTION;

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

CREATE TABLE IF NOT EXISTS `bbactions` (
  `id` tinyint(4) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `bbdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` int(10) unsigned NOT NULL DEFAULT '0',
  `player` int(10) unsigned NOT NULL DEFAULT '0',
  `action` tinyint(4) NOT NULL DEFAULT '0',
  `world` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `x` int(11) NOT NULL DEFAULT '0',
  `y` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `z` int(11) NOT NULL DEFAULT '0',
  `type` smallint(6) NOT NULL DEFAULT '0',
  `data` varchar(500) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `rbacked` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `world` (`world`),
  KEY `x` (`x`,`y`,`z`),
  KEY `player` (`player`),
  KEY `action` (`action`),
  KEY `date` (`date`),
  KEY `type` (`type`),
  KEY `rbacked` (`rbacked`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `bbusers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Player',
  `flags` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `bbworlds` (
  `id` tinyint(3) unsigned NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'world',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DROP TABLE IF EXISTS `big-brother`;
CREATE VIEW `big-brother` AS 
select `bbdata`.`id` AS `id`,
from_unixtime(`bbdata`.`date`) AS `date`,
`bbusers`.`name` AS `player`,
`bbactions`.`name` AS `action`,
`bbworlds`.`name` AS `world`,
`bbdata`.`x` AS `x`,
`bbdata`.`y` AS `y`,
`bbdata`.`z` AS `z`,
`bbdata`.`type` AS `type`,
`bbdata`.`data` AS `data`,
`bbdata`.`rbacked` AS `rbacked`
 from (
 (
 (`bbdata` join `bbusers` on
 (`bbdata`.`player` = `bbusers`.`id`)
 ) join `bbworlds` on
 (`bbdata`.`world` = `bbworlds`.`id`)
 ) join `bbactions` on
 (`bbdata`.`action` = `bbactions`.`id`) );

INSERT INTO `bbactions` (`id`, `name`, `description`) VALUES
(0, 'BLOCK_BROKEN ', 'A block that is destroyed (includes liquid picked up by a bucket)'),
(1, 'BLOCK_PLACED', 'A block that is placed (includes liquid placed by a bucket)'),
(2, 'DESTROY_SIGN_TEXT', 'A sign that was destroyed and had text'),
(3, 'TELEPORT', 'When a user teleports'),
(4, 'DELTA_CHEST', 'The chest that got its contents modified (currently only fires when destroying a chest)'),
(5, 'COMMAND', 'When the user uses a command'),
(6, 'CHAT', 'When a user chats. How pleasant'),
(7, 'DISCONNECT', 'When a user disconnects.'),
(8, 'LOGIN', 'When a user logs in'),
(9, 'DOOR_OPEN', 'Doors that were opened'),
(10, 'BUTTON_PRESS', 'The button that got pressed'),
(11, 'LEVER_SWITCH', 'The lever that got switched'),
(12, 'CREATE_SIGN_TEXT', ' A Sign that has new text (Not yet implemented)'),
(13, 'LEAF_DECAY', 'A leaf that decayed (Currently logged as player "Environment")'),
(14, 'FLINT_AND_STEEL', 'Blocks that a player used flint and steel against'),
(15, 'TNT_EXPLOSION', 'Blocks destroyed by TNT going off (Currently logged as player "Environment")'),
(16, 'CREEPER_EXPLOSION', 'Blocks destroyed by Creepers blowing up (Logged as "Environment")'),
(17, 'MISC_EXPLOSION', 'Miscellaneous explosions... Who knows?'),
(18, 'OPEN_CHEST', ' A chest that was opened by a player'),
(19, 'BLOCK_BURN', 'A block that was burnt and destroyed by fire (Current logged as "Environment")'),
(20, 'LAVA_FLOW', 'Lava flowed from one block to another (Currently logged as "Environment")');

ALTER TABLE `bbdata`
  ADD CONSTRAINT `bbdata_ibfk_3` FOREIGN KEY (`action`) REFERENCES `bbactions` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `bbdata_ibfk_1` FOREIGN KEY (`player`) REFERENCES `bbusers` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `bbdata_ibfk_2` FOREIGN KEY (`world`) REFERENCES `bbworlds` (`id`) ON UPDATE CASCADE;
COMMIT;

[benjajaja]

Hey J0s3f, What would you say about using enums instead of magic numbers?

[J0s3f]

Can you change ENUMs in MySQL to add more options later without a huge database reorganization (slow)? If this is possible, I think this would be the best option.

edit: From what I found it should work good if only new ENUM options are added at the end. We don't want to have to go through all data on adding a new logable event...

[benjajaja]

Yes, ENUM column sets can be added-to easily. It think it is even possible to rename an element of the set (add new element, update all rows, remove old element). Also, quick stupid question from me: what good do FKs do if they are all on serial ids which shouldn't change?

[N3X15]

A lot of the features I see (FK restraints etc) are not easily compatible with other database systems (Postgres, H2), and older versions of MySQL. If there are other changes, please let me know.

[J0s3f]

Well, there are at this time intentionally no other changes so it still works with older Versions. Adding ENUMs like Benja suggested would be a good option IMO. PostgreSQL and H2 support FKs and views, the syntax is just a little different. For older MySQL versions the DB could be created without this options. (This is why I didn't suggest ENUMs in the first place.)

[benjajaja]

MySQL supports enums since version 3 or so. I will try to implement it, but make it optional, depending on the DBMS or configuration settings. Edit: However, I will test how long it takes to update all "data" rows with a large DB. Edit2: will now use your temporary repo :)

[J0s3f]

The update is instant if only new values are added at the end. ENUMs are stored as integers internally, and if one of them changes all rows have to be updated. I just added a new value at the end and it was instant on a ~1000000row table.

[benjajaja]

Ok, i just tried ALTER TABLE table CHANGE COLUMN column column ENUM('A','B','C','D') where column was previously an INT, and it seems like the rows are set to the Nth enum element, where N is the previous INT value, so looking good.