Learn DB Transaction with Perl

README.md

Install Perl MySQL Dependencies

Debian:

apt-get install libdbd-mysql-perl

RHEL:

yum install perl-DBD-mysql

Install Required Perl Modules Using CPAN

cpan 
> install DBI

Setup Sample Databases

Use MySQLSampleData.txt or customers.sql to create sample databases.

Command Line Arguments for the Perl Script

The Perl script requires 5 command line arguments:

1. Customer ID from the table "cus_main"
2. Phone number of the aforementioned customer
3. Product name to insert into the table "product_main"
4. Available stock for the product insertion
5. Product ID to delete from the table "product_info"

Example:

./bdt.pl 1 0211542 AK-47 500 1

db_dump.sql

-- MySQL dump 10.13  Distrib 5.7.22, for Linux (x86_64)
--
-- Host: localhost    Database: customers
-- ------------------------------------------------------
-- Server version	5.7.22-0ubuntu0.16.04.1

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `customers`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `customers` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `customers`;

--
-- Table structure for table `cus_info`
--

DROP TABLE IF EXISTS `cus_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cus_info` (
  `cus_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `cus_address` char(30) NOT NULL,
  `cus_tp` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`cus_id`),
  CONSTRAINT `cus_info_ibfk_1` FOREIGN KEY (`cus_id`) REFERENCES `cus_main` (`cus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cus_info`
--

LOCK TABLES `cus_info` WRITE;
/*!40000 ALTER TABLE `cus_info` DISABLE KEYS */;
INSERT INTO `cus_info` VALUES (1,'The Stormlands','711115481'),(2,'The Crownlands','991115481'),(3,'The Riverlands','336115695'),(4,'The North','436155698'),(5,'The Westerlands','881115261');
/*!40000 ALTER TABLE `cus_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `cus_main`
--

DROP TABLE IF EXISTS `cus_main`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `cus_main` (
  `cus_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `cus_name` char(30) NOT NULL,
  PRIMARY KEY (`cus_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `cus_main`
--

LOCK TABLES `cus_main` WRITE;
/*!40000 ALTER TABLE `cus_main` DISABLE KEYS */;
INSERT INTO `cus_main` VALUES (1,'Robert Baratheon'),(2,'Daenerys Targaryen'),(3,'Catelyn Stark'),(4,'Jorah Mormont'),(5,'Tyrion Lannister');
/*!40000 ALTER TABLE `cus_main` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product_info`
--

DROP TABLE IF EXISTS `product_info`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product_info` (
  `prod_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `prod_dealer` char(30) NOT NULL,
  PRIMARY KEY (`prod_id`),
  CONSTRAINT `product_info_ibfk_1` FOREIGN KEY (`prod_id`) REFERENCES `product_main` (`prod_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product_info`
--

LOCK TABLES `product_info` WRITE;
/*!40000 ALTER TABLE `product_info` DISABLE KEYS */;
INSERT INTO `product_info` VALUES (1,'Stannis Baratheon'),(2,'Theon Greyjoy'),(3,'Theon Greyjoy'),(4,'Khal Drogo'),(5,'Bronn');
/*!40000 ALTER TABLE `product_info` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `product_main`
--

DROP TABLE IF EXISTS `product_main`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `product_main` (
  `prod_id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `prod_name` char(30) NOT NULL,
  `prod_stock` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`prod_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `product_main`
--

LOCK TABLES `product_main` WRITE;
/*!40000 ALTER TABLE `product_main` DISABLE KEYS */;
INSERT INTO `product_main` VALUES (1,'Arrows',1504),(2,'Arming sword',500),(3,'Battle axe',200),(4,'Club',624),(5,'Wildfire',477);
/*!40000 ALTER TABLE `product_main` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-05-08 20:30:08

dbt.perl

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
	
open( LOGS, ">>db_perl.log") || die ("Couldn't Open the Log File to write\n");

my $CUS_ID = $ARGV[0];		#Customer ID for Update quarries
my $CUS_TP = $ARGV[1];		#Customer TP No. For Update quarry 
my $PRODUCT_NAME = $ARGV[2];	#Product Name for Insert quarry
my $PRODUCT_STOCK = $ARGV[3];	#Product Stock for Insert quarry
my $PRODUCT_ID = $ARGV[4];	#Product ID for Delete quarry


my %MYSQL = (
        	hostname        =>      "localhost",
        	username        =>      "root",
        	password        =>      "password",
        	database        =>      "customers"
);

my %ATTRIB = (RaiseError=>1,
	 AutoCommit=>0);

my $DB_CON = DBI->connect("dbi:mysql:$MYSQL{database}:$MYSQL{hostname}","$MYSQL{username}","$MYSQL{password}",\%ATTRIB)
 || die("Couldn't connect to the Database!\n"); 


eval {

#Update quarry	
	$SQL_STR = "UPDATE cus_info SET cus_tp='$CUS_TP' WHERE cus_id=$CUS_ID";
    $SQL_EXEC = $DB_CON->prepare($SQL_STR);
	$SQL_EXEC->execute();
#	print "Update Quarry Executed\n";
	print LOGS localtime , ":Update Quarry Executed\n";

#Insert quarry	
	$SQL_STR = "INSERT INTO product_main(prod_name,prod_stock) VALUES ('$PRODUCT_NAME','$PRODUCT_STOCK')";
        $SQL_EXEC = $DB_CON->prepare($SQL_STR);
        $SQL_EXEC->execute();
#       print "Insert Quarry Executed\n";
	print LOGS localtime , ":Insert Quarry Executed\n";

#Delete quarry
	$SQL_STR = "DELETE FROM product_info WHERE prod_id='$PRODUCT_ID'";
        $SQL_EXEC = $DB_CON->prepare($SQL_STR);
        $SQL_EXEC->execute();
#       print "Delete Quarry Executed\n";
	print LOGS localtime , ":Delete Quarry Executed\n";
	
#Commit If everything went well
	$DB_CON->commit();
	print LOGS localtime , ":Changes were written to the DB\n";

sample_data.sql

CREATE DATABASE customers;

DROP TABLE cus_info;
DROP TABLE cus_main;
DROP TABLE product_info;
DROP TABLE product_main;


CREATE TABLE cus_main (
     cus_id MEDIUMINT NOT NULL AUTO_INCREMENT,
     cus_name CHAR(30) NOT NULL,
     PRIMARY KEY (cus_id)
);
CREATE TABLE cus_info (
     cus_id MEDIUMINT NOT NULL AUTO_INCREMENT,
     cus_address CHAR(30) NOT NULL,
	 cus_tp VARCHAR(15),
     PRIMARY KEY (cus_id),
	 FOREIGN KEY (cus_id) REFERENCES cus_main(cus_id)
);


CREATE TABLE product_main (
     prod_id MEDIUMINT NOT NULL AUTO_INCREMENT,
     prod_name CHAR(30) NOT NULL,
	 prod_stock 	MEDIUMINT,
     PRIMARY KEY (prod_id)
);
CREATE TABLE product_info (
     prod_id MEDIUMINT NOT NULL AUTO_INCREMENT,
	 prod_dealer CHAR(30) NOT NULL,
     PRIMARY KEY (prod_id),
	 FOREIGN KEY (prod_id) REFERENCES product_main(prod_id)
);

INSERT INTO cus_main(cus_name) VALUES ('Robert Baratheon');
INSERT INTO cus_main(cus_name) VALUES ('Daenerys Targaryen');
INSERT INTO cus_main(cus_name) VALUES ('Catelyn Stark');
INSERT INTO cus_main(cus_name) VALUES ('Jorah Mormont');
INSERT INTO cus_main(cus_name) VALUES ('Tyrion Lannister');

INSERT INTO cus_info(cus_address,cus_tp) VALUES ('The Stormlands','711115481');
INSERT INTO cus_info(cus_address,cus_tp) VALUES ('The Crownlands','991115481');
INSERT INTO cus_info(cus_address,cus_tp) VALUES ('The Riverlands','336115695');
INSERT INTO cus_info(cus_address,cus_tp) VALUES ('The North','436155698');
INSERT INTO cus_info(cus_address,cus_tp) VALUES ('The Westerlands','881115261');

INSERT INTO product_main(prod_name,prod_stock) VALUES ('Arrows','1504');
INSERT INTO product_main(prod_name,prod_stock) VALUES ('Arming sword','500');
INSERT INTO product_main(prod_name,prod_stock) VALUES ('Battle axe','200');
INSERT INTO product_main(prod_name,prod_stock) VALUES ('Club','624');
INSERT INTO product_main(prod_name,prod_stock) VALUES ('Wildfire','477');


INSERT INTO product_info(prod_dealer) VALUES ('Stannis Baratheon');
INSERT INTO product_info(prod_dealer) VALUES ('Theon Greyjoy');
INSERT INTO product_info(prod_dealer) VALUES ('Theon Greyjoy');
INSERT INTO product_info(prod_dealer) VALUES ('Khal Drogo');
INSERT INTO product_info(prod_dealer) VALUES ('Bronn');