Click here to Skip to main content
12,698,179 members (21,943 online)
Click here to Skip to main content

Stats

25K views
1.1K downloads
15 bookmarked
Posted

Checksum Verification

, 22 Sep 2011 CPOL
Loop through all files in a folder/subfolders and run a checksum that is stored in a database. E-mail descrepancies and results.
-- 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 */;

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

smoore4
Database Developer
United States United States
I am an MBA with a bunch of MS certifications. Technically, I am a DBA, but I do a good deal of sys admin work and web development using .NET. I like to focus on business intelligence, database design, messaging architectures, and web services.

You may also be interested in...

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 23 Sep 2011
Article Copyright 2011 by smoore4
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid