text: -- phpMyAdmin SQL Dump
  -- version 5.1.1
  -- https://www.phpmyadmin.net/
  --
  -- Host: 127.0.0.1
  -- Generation Time: Feb 02, 2022 at 06:52 AM
  -- Server version: 10.4.22-MariaDB
  -- PHP Version: 8.1.1
  
  SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
  START TRANSACTION;
  SET time_zone = "+00:00";
  
  
  /*!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 utf8mb4 */;
  
  --
  -- Database: `jobportal`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `employer`
  --
  
  CREATE TABLE `employer` (
    `id` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `email` varchar(200) NOT NULL,
    `password` varchar(200) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `employer`
  --
  
  INSERT INTO `employer` (`id`, `name`, `email`, `password`) VALUES
  (4, 'Wipro Technologies ', 'admin@wipro.com', '112233'),
  (10, 'Mahindra', 'admin@mahindra.com', '112233'),
  (11, 'Ins It Services', 'admin@ins.com', '112233'),
  (14, 'infosys', 'admin@infosys.com', '112233'),
  (15, 'Paladion Networks', 'admin@paladion.com', '112233'),
  (16, 'Accenture', 'admin@accenture.com', '112233'),
  (26, 'Microsoft', 'admin@microsoft.com', '112233'),
  (27, 'Spark Foundation', 'admin@sf.com', '112233'),
  (28, 'Facebook', 'admin@facebook.com', '112233'),
  (29, 'Intel', 'admin@intel.com', '112233');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `jobsapplied`
  --
  
  CREATE TABLE `jobsapplied` (
    `id` int(11) NOT NULL,
    `date` date NOT NULL,
    `pid` int(11) NOT NULL,
    `sid` int(11) NOT NULL,
    `status` varchar(500) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `jobsapplied`
  --
  
  INSERT INTO `jobsapplied` (`id`, `date`, `pid`, `sid`, `status`) VALUES
  (26, '2022-01-26', 5, 35, 'sent'),
  (27, '2022-01-26', 4, 35, 'Accepted'),
  (28, '2022-01-26', 8, 35, 'sent'),
  (29, '2022-01-27', 39, 34, 'sent'),
  (35, '2022-01-31', 4, 34, 'sent');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `logpost`
  --
  
  CREATE TABLE `logpost` (
    `lpid` int(11) NOT NULL,
    `pid` int(11) NOT NULL,
    `eid` int(11) NOT NULL,
    `name` varchar(500) NOT NULL,
    `category` varchar(500) NOT NULL,
    `industry` varchar(500) NOT NULL,
    `role` varchar(100) NOT NULL,
    `employmentType` varchar(500) NOT NULL,
    `status` varchar(500) NOT NULL,
    `action` varchar(500) NOT NULL,
    `cdate` datetime NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  
  --
  -- Dumping data for table `logpost`
  --
  
  INSERT INTO `logpost` (`lpid`, `pid`, `eid`, `name`, `category`, `industry`, `role`, `employmentType`, `status`, `action`, `cdate`) VALUES
  (7, 40, 4, 'Product Manager', 'Business Intelligence Jobs', 'IT-Software , Software Services', 'Lead', 'Permanent', 'Open', 'INSERTED', '2022-02-02 10:46:59'),
  (8, 41, 29, 'Graphic Designer', 'Graphic Designer Jobs', 'Animation , Gaming', 'Intern', 'Permanent', 'Open', 'INSERTED', '2022-02-02 10:53:40'),
  (9, 42, 28, 'Python Developer', 'IT Jobs', 'IT-Software , Software Services', 'Asst. Python Developer ', 'Permanent', 'Open', 'INSERTED', '2022-02-02 11:03:24'),
  (10, 6, 14, 'Team Lead (Technical)', 'IT Jobs', 'IT-Software , Software Services', 'Team Lead', 'Permanent', 'open', 'UPDATED', '2022-02-02 11:07:48'),
  (11, 8, 16, 'Accounts Manager', 'Accounting Jobs', 'Accounting , Finance', 'Accounts Manager', 'Permanent', 'open', 'UPDATED', '2022-02-02 11:09:58');
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `post`
  --
  
  CREATE TABLE `post` (
    `id` int(11) NOT NULL,
    `date` timestamp NOT NULL DEFAULT current_timestamp(),
    `eid` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `category` varchar(500) NOT NULL,
    `minexp` varchar(100) NOT NULL,
    `desc` varchar(5000) NOT NULL,
    `salary` varchar(200) NOT NULL,
    `industry` varchar(500) NOT NULL,
    `role` varchar(500) NOT NULL,
    `employmentType` varchar(500) NOT NULL,
    `status` varchar(100) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `post`
  --
  
  INSERT INTO `post` (`id`, `date`, `eid`, `name`, `category`, `minexp`, `desc`, `salary`, `industry`, `role`, `employmentType`, `status`) VALUES
  (6, '2018-10-04 18:30:00', 14, 'Team Lead (Technical)', 'IT Jobs', '8', 'Aid a group of programmers.', '100000-150000', 'IT-Software , Software Services', 'Team Lead', 'Permanent', 'open'),
  (8, '2018-10-04 18:30:00', 16, 'Accounts Manager', 'Accounting Jobs', '6', 'Experience with accounting software. General Math skills.', '70000-80000', 'Accounting , Finance', 'Accounts Manager', 'Permanent', 'open'),
  (40, '2022-02-01 18:30:00', 4, 'Product Manager', 'Business Intelligence Jobs', '3', 'Communication Skills, Technical Knowledge', '40000-60000', 'IT-Software , Software Services', 'Lead', 'Permanent', 'Open'),
  (41, '2022-02-01 18:30:00', 29, 'Graphic Designer', 'Graphic Designer Jobs', '3', '3D Animation, Adobe products.', '30000-50000', 'Animation , Gaming', 'Intern', 'Permanent', 'Open'),
  (42, '2022-02-01 18:30:00', 28, 'Python Developer', 'IT Jobs', '2', 'Proficiency in Python, Test software components', '40000-60000', 'IT-Software , Software Services', 'Asst. Python Developer ', 'Permanent', 'Open');
  
  --
  -- Triggers `post`
  --
  DELIMITER $$
  CREATE TRIGGER `Existing Row Deleted` AFTER DELETE ON `post` FOR EACH ROW INSERT INTO logpost VALUES(null, OLD.id, OLD.eid, OLD.name, OLD.category, OLD.industry, OLD.role, OLD.employmentType, OLD.status, 'DELETED', NOW())
  $$
  DELIMITER ;
  DELIMITER $$
  CREATE TRIGGER `Existing Row Updated` AFTER UPDATE ON `post` FOR EACH ROW INSERT INTO logpost VALUES(null, NEW.id, NEW.eid, NEW.name, NEW.category, NEW.industry, NEW.role, NEW.employmentType, NEW.status, 'UPDATED', NOW())
  $$
  DELIMITER ;
  DELIMITER $$
  CREATE TRIGGER `New Row Inserted` AFTER INSERT ON `post` FOR EACH ROW INSERT INTO logpost VALUES(null, NEW.id, NEW.eid, NEW.name, NEW.category, NEW.industry, NEW.role, NEW.employmentType, NEW.status, 'INSERTED', NOW())
  $$
  DELIMITER ;
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `seeker`
  --
  
  CREATE TABLE `seeker` (
    `id` int(11) NOT NULL,
    `name` varchar(200) NOT NULL,
    `email` varchar(200) NOT NULL,
    `password` varchar(200) NOT NULL,
    `qualification` varchar(500) NOT NULL,
    `dob` date NOT NULL,
    `skills` varchar(2000) NOT NULL,
    `resume` varchar(500) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  --
  -- Dumping data for table `seeker`
  --
  
  INSERT INTO `seeker` (`id`, `name`, `email`, `password`, `qualification`, `dob`, `skills`, `resume`) VALUES
  (34, 'Rakshith Acharya', 'rakshith.19cs121@sode-edu.in', '112233', 'BE', '2001-09-06', 'HTML, CSS, PHP, Web Development', ''),
  (35, 'Raksha B Kottari', 'raksha.19cs073@sode-edu.in', '112233', 'BE', '2001-10-17', 'Database Designer, Back-end, Marketing, Accounting', ''),
  (36, 'Shreya', 'shreya@gmail.com', '112233', 'BE', '2001-06-21', 'C++', ''),
  (37, 'Sumanth Shettigar', 'sumanth@gmail.com', '112233', 'BE', '2001-07-04', 'html', ''),
  (38, 'Sudarshan', 'sudarshan@gmail.com', '112233', 'MBBS', '2001-09-05', 'Bone specialist', ''),
  (39, 'Rithesh Kumar', 'rithesh@gmail.com', '112233', 'BE', '2001-06-02', 'Circuit Design', ''),
  (40, 'Ramesha', 'ramesha@gmail.com', '112233', 'BA', '1995-07-19', 'history, sociology', ''),
  (41, 'Venkat', 'venkat@gmail.com', '112233', 'B.Sc.', '1995-11-23', 'Physiotherapy', ''),
  (42, 'Paul Walker', 'paul@gmail.com', '112233', 'BBA', '1991-08-12', 'International Business', ''),
  (43, 'Dom', 'dom@gmail.com', '112233', 'Bachelor of Engg./Tech', '1998-03-07', 'Computer Applications', '');
  
  -- --------------------------------------------------------
  
  --
  -- Stand-in structure for view `totalactiveusers`
  -- (See below for the actual view)
  --
  CREATE TABLE `totalactiveusers` (
  `TotalActiveUsers` decimal(42,0)
  );
  
  -- --------------------------------------------------------
  
  --
  -- Stand-in structure for view `totalposts`
  -- (See below for the actual view)
  --
  CREATE TABLE `totalposts` (
  `AllPosts` bigint(21)
  );
  
  -- --------------------------------------------------------
  
  --
  -- Stand-in structure for view `users`
  -- (See below for the actual view)
  --
  CREATE TABLE `users` (
  `SeekersAndEmployers` bigint(21)
  );
  
  -- --------------------------------------------------------
  
  --
  -- Structure for view `totalactiveusers`
  --
  DROP TABLE IF EXISTS `totalactiveusers`;
  
  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `totalactiveusers`  AS SELECT sum(`users`.`SeekersAndEmployers`) AS `TotalActiveUsers` FROM `users` ;
  
  -- --------------------------------------------------------
  
  --
  -- Structure for view `totalposts`
  --
  DROP TABLE IF EXISTS `totalposts`;
  
  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `totalposts`  AS SELECT count(`post`.`id`) AS `AllPosts` FROM `post` ;
  
  -- --------------------------------------------------------
  
  --
  -- Structure for view `users`
  --
  DROP TABLE IF EXISTS `users`;
  
  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `users`  AS SELECT count(`seeker`.`id`) AS `SeekersAndEmployers` FROM `seeker` ;
  
  --
  -- Indexes for dumped tables
  --
  
  --
  -- Indexes for table `employer`
  --
  ALTER TABLE `employer`
    ADD PRIMARY KEY (`id`);
  
  --
  -- Indexes for table `jobsapplied`
  --
  ALTER TABLE `jobsapplied`
    ADD PRIMARY KEY (`id`),
    ADD KEY `jobapplied_seekerFK` (`sid`),
    ADD KEY `pid` (`pid`);
  
  --
  -- Indexes for table `logpost`
  --
  ALTER TABLE `logpost`
    ADD PRIMARY KEY (`lpid`);
  
  --
  -- Indexes for table `post`
  --
  ALTER TABLE `post`
    ADD PRIMARY KEY (`id`),
    ADD UNIQUE KEY `id` (`id`),
    ADD UNIQUE KEY `id_2` (`id`),
    ADD KEY `employer_postFK` (`eid`);
  
  --
  -- Indexes for table `seeker`
  --
  ALTER TABLE `seeker`
    ADD PRIMARY KEY (`id`);
  
  --
  -- AUTO_INCREMENT for dumped tables
  --
  
  --
  -- AUTO_INCREMENT for table `employer`
  --
  ALTER TABLE `employer`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;
  
  --
  -- AUTO_INCREMENT for table `jobsapplied`
  --
  ALTER TABLE `jobsapplied`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;
  
  --
  -- AUTO_INCREMENT for table `logpost`
  --
  ALTER TABLE `logpost`
    MODIFY `lpid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
  
  --
  -- AUTO_INCREMENT for table `post`
  --
  ALTER TABLE `post`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;
  
  --
  -- AUTO_INCREMENT for table `seeker`
  --
  ALTER TABLE `seeker`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;
  
  --
  -- Constraints for dumped tables
  --
  
  --
  -- Constraints for table `jobsapplied`
  --
  ALTER TABLE `jobsapplied`
    ADD CONSTRAINT `jobapplied_seekerFK` FOREIGN KEY (`sid`) REFERENCES `seeker` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  
  --
  -- Constraints for table `post`
  --
  ALTER TABLE `post`
    ADD CONSTRAINT `employer_postFK` FOREIGN KEY (`eid`) REFERENCES `employer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
  COMMIT;
  
  /*!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 */;
  