CREATE TABLE IF NOT EXISTS `ajxp_changes` (
  `seq` int(20) NOT NULL AUTO_INCREMENT,
  `repository_identifier` TEXT NOT NULL,
  `node_id` bigint(20) NOT NULL,
  `type` enum('create','delete','path','content') NOT NULL,
  `source` text NOT NULL,
  `target` text NOT NULL,
  PRIMARY KEY (`seq`),
  KEY `node_id` (`node_id`,`type`)
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `ajxp_index` (
  `node_id` int(20) NOT NULL AUTO_INCREMENT,
  `node_path` text NOT NULL,
  `bytesize` bigint(20) NOT NULL,
  `md5` varchar(32) NOT NULL,
  `mtime` int(11) NOT NULL,
  `repository_identifier` text NOT NULL,
  PRIMARY KEY (`node_id`)
) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

DROP TRIGGER IF EXISTS `LOG_DELETE`;

CREATE TRIGGER `LOG_DELETE` AFTER DELETE ON `ajxp_index`
FOR EACH ROW INSERT INTO ajxp_changes (repository_identifier, node_id,source,target,type)
  VALUES (old.repository_identifier, old.node_id, old.node_path, 'NULL', 'delete');

DROP TRIGGER IF EXISTS `LOG_INSERT`;

CREATE TRIGGER `LOG_INSERT` AFTER INSERT ON `ajxp_index`
FOR EACH ROW INSERT INTO ajxp_changes (repository_identifier, node_id,source,target,type)
  VALUES (new.repository_identifier, new.node_id, 'NULL', new.node_path, 'create');

DROP TRIGGER IF EXISTS `LOG_UPDATE`;

CREATE TRIGGER `LOG_UPDATE` AFTER UPDATE ON `ajxp_index`
FOR EACH ROW INSERT INTO ajxp_changes (repository_identifier, node_id,source,target,type)
  VALUES (new.repository_identifier, new.node_id, old.node_path, new.node_path, CASE LENGTH(new.node_path) = LENGTH(old.node_path) AND old.node_path COLLATE utf8_bin = new.node_path COLLATE utf8_bin WHEN true THEN 'content' ELSE 'path' END);