Checksum Verification

-- MySQL Administrator dump 1.4
--
-- ------------------------------------------------------
-- Server version	5.5.15


/*!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 */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;


--
-- Create schema drmc
--

CREATE DATABASE IF NOT EXISTS drmc;
USE drmc;

--
-- Temporary table structure for view `vw_changedfiles`
--
DROP TABLE IF EXISTS `vw_changedfiles`;
DROP VIEW IF EXISTS `vw_changedfiles`;
CREATE TABLE `vw_changedfiles` (
  `FILEPATH` varchar(255),
  `SHA256` varchar(64),
  `COUNT(ID)` bigint(21)
);

--
-- Definition of table `checksum`
--

DROP TABLE IF EXISTS `checksum`;
CREATE TABLE `checksum` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FILEPATH` varchar(255) NOT NULL,
  `FILENAME` varchar(255) DEFAULT NULL,
  `SHA256` varchar(64) NOT NULL,
  `MOD_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ID`),
  KEY `IDX_SHA256` (`SHA256`),
  KEY `IDX_FILEPATH` (`FILEPATH`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=635 DEFAULT CHARSET=utf8;

--
-- Dumping data for table `checksum`
--

/*!40000 ALTER TABLE `checksum` DISABLE KEYS */;
/*!40000 ALTER TABLE `checksum` ENABLE KEYS */;


--
-- Definition of procedure `proc_changedfiles`
--

DROP PROCEDURE IF EXISTS `proc_changedfiles`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_changedfiles`(OUT M_CHANGECOUNT INT)
BEGIN

SET @SEL = "
SELECT FILEPATH,SHA256,MOD_DATE
FROM drmc.checksum
WHERE FILEPATH IN(
SELECT FILEPATH
FROM drmc.vw_changedfiles
GROUP BY FILEPATH
HAVING COUNT(SHA256) > 1)
GROUP BY FILEPATH,SHA256;";

    PREPARE STMT FROM @SEL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;

SELECT FILEPATH FROM drmc.vw_changedfiles GROUP BY FILEPATH HAVING COUNT(SHA256) > 1;

SELECT FOUND_ROWS() INTO M_CHANGECOUNT;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `proc_checksum`
--

DROP PROCEDURE IF EXISTS `proc_checksum`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_checksum`(IN M_FILEPATH VARCHAR(255), IN M_FILENAME VARCHAR(255), IN M_SHA256 VARCHAR(64))
BEGIN

     SET @INS = CONCAT("INSERT INTO drmc.checksum (FILEPATH,FILENAME,SHA256) VALUES ('", M_FILEPATH, "','", M_FILENAME, "','", M_SHA256, "');");
     PREPARE STMT FROM @INS;
     EXECUTE STMT;
     DEALLOCATE PREPARE STMT;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `proc_newfiles`
--

DROP PROCEDURE IF EXISTS `proc_newfiles`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_newfiles`(OUT M_NEWCOUNT INT)
BEGIN

    SET @SEL = "SELECT FILEPATH,SHA256,MOD_DATE FROM drmc.checksum
    WHERE ID IN(
      SELECT ID
      FROM drmc.checksum
      GROUP BY FILEPATH
      HAVING COUNT(ID) = 1);";

    PREPARE STMT FROM @SEL;
    EXECUTE STMT;
    DEALLOCATE PREPARE STMT;

SELECT FOUND_ROWS() INTO M_NEWCOUNT;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `proc_samefiles`
--

DROP PROCEDURE IF EXISTS `proc_samefiles`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_samefiles`(OUT M_SAMECOUNT INT)
BEGIN

SELECT COUNT(ID) FROM drmc.checksum GROUP BY FILEPATH,SHA256 HAVING COUNT(ID) > 1;

SELECT FOUND_ROWS() INTO M_SAMECOUNT;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of procedure `proc_totalfiles`
--

DROP PROCEDURE IF EXISTS `proc_totalfiles`;

DELIMITER $$

/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_totalfiles`(OUT M_TOTALCOUNT INT)
BEGIN

SELECT COUNT(ID) FROM drmc.checksum GROUP BY FILEPATH,SHA256;

SELECT FOUND_ROWS() INTO M_TOTALCOUNT;

END $$
/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */  $$

DELIMITER ;

--
-- Definition of view `vw_changedfiles`
--

DROP TABLE IF EXISTS `vw_changedfiles`;
DROP VIEW IF EXISTS `vw_changedfiles`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_changedfiles` AS select `checksum`.`FILEPATH` AS `FILEPATH`,`checksum`.`SHA256` AS `SHA256`,count(`checksum`.`ID`) AS `COUNT(ID)` from `checksum` group by `checksum`.`FILEPATH`,`checksum`.`SHA256`;



/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;