gitea_admin/hades-chart
1
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
hades-chart/hades/sql/002_dump.sql
Sagar Patil 077835d68e fix: add DROP TRIGGER IF EXISTS before CREATE TRIGGER for idempotent re-runs 
- MySQL 5.7 does not support CREATE TRIGGER IF NOT EXISTS
- Add DROP TRIGGER IF EXISTS before each CREATE TRIGGER in 004_hades.sql (69 triggers) and 002_dump.sql (90 triggers)
- DROP TRIGGER only removes trigger definition, does not affect table data
- Fixes ERROR 1359 (Trigger already exists) when dbinit Job runs multiple times

Co-Authored-By: Claude Opus 4.6 <noreply@anthropic.com>
2026-03-12 12:44:51 +05:30

3000 lines
138 KiB
SQL
Raw Permalink Blame History

-- MySQL dump 10.13 Distrib 5.7.36, for Linux (x86_64)
--
-- Host: localhost Database: hades_db
-- ------------------------------------------------------
-- Server version 5.7.36-0ubuntu0.18.04.1-log
/*!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 */;
--
-- Table structure for table `access_permissions`
--
use hades_db;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `access_permissions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_permissions_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_permissions_insert_trigger BEFORE INSERT ON access_permissions
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_permissions_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_permissions_update_trigger BEFORE UPDATE ON access_permissions
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `access_role_permission_mappings`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `access_role_permission_mappings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`access_role_id` int(11) NOT NULL,
`permission_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `access_role_id` (`access_role_id`,`permission_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_role_permission_mappings_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_role_permission_mappings_insert_trigger BEFORE INSERT ON access_role_permission_mappings
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_role_permission_mappings_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_role_permission_mappings_update_trigger BEFORE UPDATE ON access_role_permission_mappings
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `access_role_tag_mappings`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `access_role_tag_mappings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`access_role_id` int(11) NOT NULL,
`tag` varchar(250) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `access_role_id` (`access_role_id`,`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_role_tag_mappings_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_role_tag_mappings_insert_trigger BEFORE INSERT ON access_role_tag_mappings
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_role_tag_mappings_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_role_tag_mappings_update_trigger BEFORE UPDATE ON access_role_tag_mappings
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `access_roles`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `access_roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_id` int(11) NOT NULL DEFAULT '-1',
`alias` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`description` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`user_id` int(11) NOT NULL DEFAULT '-1',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
KEY `ar_index_agency` (`agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_roles_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_roles_insert_trigger BEFORE INSERT ON hades_db.access_roles
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_access_roles_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_access_roles_update_trigger BEFORE UPDATE ON hades_db.access_roles
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `agencies`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `agencies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`parent_agency_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`marketplace_client_id` int(11) DEFAULT NULL,
`agency_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_insert_trigger BEFORE INSERT ON hades_db.agencies
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_after_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_after_insert_trigger AFTER INSERT ON hades_db.agencies
FOR EACH ROW
BEGIN
call create_single_agency_hierarchy(NEW.id, NEW.name, NEW.status_type_id);
insert into user_agency_mappings (user_id, agency_id, access_role_id, status_type_id, end_user_id) values (4661, new.id, 3, 1, 4661);
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_update_trigger BEFORE UPDATE ON hades_db.agencies
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_update_trigger2;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_update_trigger2 AFTER UPDATE ON hades_db.agencies
FOR EACH ROW
BEGIN
IF NEW.status_type_id <> OLD.status_type_id OR NEW.name <> OLD.name THEN
UPDATE agencies_flattened
SET status_type_id= NEW.status_type_id, name= NEW.name
WHERE agency_id = NEW.id;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `agencies_flattened`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `agencies_flattened` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`agency_id` int(11) NOT NULL,
`name` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`ancestor_agency_id` int(11) NOT NULL,
`level` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `agency_id` (`agency_id`,`ancestor_agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_flattened_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_flattened_insert_trigger BEFORE INSERT ON hades_db.agencies_flattened
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_flattened_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_flattened_update_trigger BEFORE UPDATE ON hades_db.agencies_flattened
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `agencies_id_generator`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `agencies_id_generator` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_id_generator_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_id_generator_insert_trigger BEFORE INSERT ON agencies_id_generator
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_agencies_id_generator_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_agencies_id_generator_update_trigger BEFORE UPDATE ON agencies_id_generator
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `app_ips_accesses`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `app_ips_accesses` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` int(11) NOT NULL,
`ipaddress` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `app_id` (`app_id`,`ipaddress`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_app_ips_accesses_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_app_ips_accesses_insert_trigger BEFORE INSERT ON hades_db.app_ips_accesses
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_app_ips_accesses_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_app_ips_accesses_update_trigger BEFORE UPDATE ON hades_db.app_ips_accesses
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `apps`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `apps` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`token` char(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`team_id` int(11) NOT NULL DEFAULT '18',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`),
UNIQUE KEY `token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_apps_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_apps_insert_trigger BEFORE INSERT ON hades_db.apps
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_apps_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_apps_update_trigger BEFORE UPDATE ON hades_db.apps
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `auth_channel_keys`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `auth_channel_keys` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`auth_channel_id` int(11) NOT NULL,
`key` char(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_channel_id` (`auth_channel_id`,`key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_channel_keys_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_channel_keys_insert_trigger BEFORE INSERT ON hades_db.auth_channel_keys
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_channel_keys_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_channel_keys_update_trigger BEFORE UPDATE ON hades_db.auth_channel_keys
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `auth_channels`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `auth_channels` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_channels_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_channels_insert_trigger BEFORE INSERT ON hades_db.auth_channels
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_channels_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_channels_update_trigger BEFORE UPDATE ON hades_db.auth_channels
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `auth_provider_metadata`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `auth_provider_metadata` (
`user_id` int(11) NOT NULL,
`auth_provider_type_id` int(3) NOT NULL,
`auth_provider_value` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`activation_code` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_verified` tinyint(1) NOT NULL DEFAULT '0',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` char(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`auth_provider_type_id`,`auth_provider_value`),
KEY `index_user_id_on_auth_provider_metadata` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_metadata_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_metadata_insert_trigger BEFORE INSERT ON hades_db.auth_provider_metadata
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_metadata_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_metadata_update_trigger BEFORE UPDATE ON hades_db.auth_provider_metadata
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `auth_provider_metadata_v2`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `auth_provider_metadata_v2` (
`user_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL DEFAULT '-1',
`auth_provider_type_id` int(3) NOT NULL,
`auth_provider_value` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`activation_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_verified` tinyint(1) NOT NULL DEFAULT '0',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`auth_provider_type_id`,`auth_provider_value`,`agency_id`),
KEY `index_user_id_on_auth_provider_metadata_v2` (`user_id`,`agency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_metadata_v2_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_metadata_v2_insert_trigger BEFORE INSERT ON hades_db.auth_provider_metadata_v2
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_apm_v2_insert_apm_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_apm_v2_insert_apm_trigger AFTER INSERT ON hades_db.auth_provider_metadata_v2
FOR EACH ROW
BEGIN
INSERT IGNORE INTO auth_provider_metadata (user_id, auth_provider_type_id, auth_provider_value, activation_code, is_verified, is_deleted, creation_date, last_update, last_updated_by) VALUES (NEW.user_id, NEW.auth_provider_type_id, NEW.auth_provider_value, NEW.activation_code, NEW.is_verified, NEW.is_deleted, NEW.creation_date, NEW.last_update, NEW.last_updated_by);
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_metadata_v2_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_metadata_v2_update_trigger BEFORE UPDATE ON hades_db.auth_provider_metadata_v2
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_apm_v2_update_apm_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_apm_v2_update_apm_trigger AFTER UPDATE ON hades_db.auth_provider_metadata_v2
FOR EACH ROW
BEGIN
UPDATE auth_provider_metadata SET is_deleted = NEW.is_deleted,
is_verified = NEW.is_verified,
activation_code = NEW.activation_code
WHERE auth_provider_type_id = NEW.auth_provider_type_id AND auth_provider_value = NEW.auth_provider_value AND user_id = NEW.user_id;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `auth_provider_types`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `auth_provider_types` (
`id` int(3) NOT NULL AUTO_INCREMENT,
`name` char(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` char(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_types_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_types_insert_trigger BEFORE INSERT ON hades_db.auth_provider_types
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_auth_provider_types_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_auth_provider_types_update_trigger BEFORE UPDATE ON hades_db.auth_provider_types
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `clients`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `clients` (
`client_id` int(11) NOT NULL,
`name` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`alias` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_id` int(11) NOT NULL,
`store_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'custom',
`merchant_type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'client',
`url` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`status_type_id` int(11) NOT NULL,
`contact_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`feed_source` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`installation_type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`currency_code` char(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`timezone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_clients_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_clients_insert_trigger BEFORE INSERT ON hades_db.clients
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_clients_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_clients_update_trigger BEFORE UPDATE ON hades_db.clients
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `clients_backup`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `clients_backup` (
`client_id` int(11) NOT NULL,
`name` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `clients_id_generator`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `clients_id_generator` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_clients_id_generator_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_clients_id_generator_insert_trigger BEFORE INSERT ON clients_id_generator
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_clients_id_generator_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_clients_id_generator_update_trigger BEFORE UPDATE ON clients_id_generator
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `email_id_metadata`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `email_id_metadata` (
`user_id` int(11) NOT NULL,
`email` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`activation_code` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_verified` tinyint(1) NOT NULL DEFAULT '0',
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` char(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db.email_id_metadata_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db.email_id_metadata_insert_trigger BEFORE INSERT ON hades_db.email_id_metadata
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db.email_id_metadata_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db.email_id_metadata_update_trigger BEFORE UPDATE ON hades_db.email_id_metadata
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `encrypted_app_contexts`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `encrypted_app_contexts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` int(11) NOT NULL,
`app_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`context` varchar(8192) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `app_id` (`app_id`,`app_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_app_contexts_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_app_contexts_insert_trigger BEFORE INSERT ON hades_db.encrypted_app_contexts
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_app_contexts_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_app_contexts_update_trigger BEFORE UPDATE ON hades_db.encrypted_app_contexts
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `encrypted_app_contexts_bk`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `encrypted_app_contexts_bk` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` int(11) NOT NULL,
`app_key` varchar(100) NOT NULL,
`context` varchar(8192) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `app_id` (`app_id`,`app_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_app_contexts_v2_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_app_contexts_v2_insert_trigger BEFORE INSERT ON `encrypted_app_contexts_bk` FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_app_contexts_v2_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_app_contexts_v2_update_trigger BEFORE UPDATE ON `encrypted_app_contexts_bk` FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `encrypted_tokens`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `encrypted_tokens` (
`token` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`auth_channel_id` int(11) DEFAULT '1',
`issue_datetime` datetime NOT NULL,
`expiration_datetime` datetime NOT NULL,
`ubid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`token`),
KEY `tokens_idx1` (`ubid`,`expiration_datetime`),
KEY `tokens_idx2` (`token`,`expiration_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_tokens_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_tokens_insert_trigger BEFORE INSERT ON hades_db.encrypted_tokens
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_encrypted_tokens_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_encrypted_tokens_update_trigger BEFORE UPDATE ON hades_db.encrypted_tokens
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `entity`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `entity` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entity_id` int(11) NOT NULL,
`entity_type_id` int(11) NOT NULL,
`entity_name` varchar(250) NOT NULL,
`entity_alias` varchar(250) NOT NULL,
`status_type_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `entity_type_id` (`entity_type_id`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_insert_trigger BEFORE INSERT ON entity
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_update_trigger BEFORE UPDATE ON entity
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_update_agencies_clients_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_update_agencies_clients_trigger AFTER UPDATE ON hades_db.entity
FOR EACH ROW
BEGIN
IF NEW.entity_type_id = 1 THEN
IF NEW.entity_alias <> OLD.entity_alias OR NEW.status_type_id <> OLD.status_type_id OR NEW.is_deleted <> OLD.is_deleted THEN
UPDATE clients SET alias = NEW.entity_alias, status_type_id = NEW.status_type_id, is_deleted = NEW.is_deleted
WHERE client_id = NEW.entity_id;
END IF;
ELSEIF NEW.entity_type_id = 2 THEN
IF NEW.status_type_id <> OLD.status_type_id OR NEW.is_deleted <> OLD.is_deleted THEN
UPDATE agencies SET status_type_id = NEW.status_type_id, is_deleted = NEW.is_deleted
WHERE id = NEW.entity_id;
END IF;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `entity_maps`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `entity_maps` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`entity_id` int(11) NOT NULL,
`entity_type_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`parent_id` int(11) DEFAULT NULL,
`parent_entity_type_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL,
`parent_depth` int(11) NOT NULL,
`absolute_depth` int(11) NOT NULL,
`root_mcc_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `entity_type_id` (`entity_type_id`,`entity_id`,`parent_entity_type_id`,`parent_id`,`root_mcc_id`),
KEY `em_index_parent_entity` (`parent_entity_type_id`,`parent_id`,`absolute_depth`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_maps_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_maps_insert_trigger BEFORE INSERT ON entity_maps
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_maps_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_maps_update_trigger BEFORE UPDATE ON entity_maps
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `entity_metadata`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `entity_metadata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`agency_id` int(11) NOT NULL,
`entity_id` int(11) NOT NULL,
`entity_type_id` int(11) NOT NULL,
`marketplace_client_id` int(11) DEFAULT NULL,
`business_definition` varchar(64) DEFAULT NULL,
`url` varchar(2048) DEFAULT NULL,
`merchant_id` varchar(256) DEFAULT NULL,
`merchant_type` varchar(64) DEFAULT NULL,
`city` varchar(128) DEFAULT NULL,
`state` varchar(128) DEFAULT NULL,
`country` varchar(128) DEFAULT NULL,
`currency` varchar(4) DEFAULT NULL,
`timezone` varchar(32) DEFAULT NULL,
`approval_status` varchar(64) DEFAULT NULL,
`business_status` varchar(64) DEFAULT NULL,
`geolocation` varchar(32) DEFAULT NULL,
`business_legal_info` varchar(1024) DEFAULT NULL,
`custom_label_1` varchar(512) DEFAULT NULL,
`custom_label_2` varchar(512) DEFAULT NULL,
`custom_label_3` varchar(512) DEFAULT NULL,
`custom_label_4` varchar(512) DEFAULT NULL,
`custom_label_5` varchar(512) DEFAULT NULL,
`custom_label_6` varchar(512) DEFAULT NULL,
`custom_label_7` varchar(512) DEFAULT NULL,
`custom_label_8` varchar(512) DEFAULT NULL,
`custom_label_9` varchar(512) DEFAULT NULL,
`custom_label_10` varchar(512) DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
`store_type` varchar(50) DEFAULT 'custom',
`created_by_user_id` int(11) NOT NULL,
`updated_by_user_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `entity_type_id` (`entity_type_id`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_metadata_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_metadata_insert_trigger BEFORE INSERT ON entity_metadata
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_metadata_insert_agencies_clients_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_metadata_insert_agencies_clients_trigger AFTER INSERT ON hades_db.entity_metadata
FOR EACH ROW
BEGIN
IF NEW.entity_type_id = 1 THEN
INSERT IGNORE INTO clients(client_id, name, alias, agency_id, store_type, merchant_type, url,
status_type_id, contact_number, feed_source, installation_type, currency_code,
timezone, creation_date, last_update, last_updated_by, is_deleted)
SELECT entity_id, entity_name, entity_alias, agency_id, IFNULL(NEW.store_type, 'custom'), IFNULL(NEW.merchant_type, 'client'), IFNULL(NEW.url, 'http://www.onlinesales.ai'),
status_type_id, NULL, NULL, NULL, NEW.currency, NEW.timezone, creation_date, last_update, last_updated_by, is_deleted
FROM entity where entity_id = NEW.entity_id and entity_type_id = NEW.entity_type_id;
ELSEIF NEW.entity_type_id = 2 THEN
INSERT IGNORE INTO agencies(id, name, parent_agency_id, status_type_id, marketplace_client_id,
agency_type, creation_date, last_update, last_updated_by, is_deleted)
SELECT entity_id, entity_name, -1, status_type_id, NEW.marketplace_client_id,
NEW.custom_label_1, creation_date, last_update, last_updated_by, is_deleted
FROM entity where entity_id = NEW.entity_id and entity_type_id = NEW.entity_type_id;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_metadata_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_metadata_update_trigger BEFORE UPDATE ON entity_metadata
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_metadata_update_entity_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_metadata_update_entity_update_trigger AFTER UPDATE ON entity_metadata
FOR EACH ROW
BEGIN
UPDATE entity SET last_updated_by=USER(), last_update = NOW() WHERE entity_id = NEW.entity_id AND entity_type_id = NEW.entity_type_id AND agency_id = NEW.agency_id;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_metadata_update_agencies_clients_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_metadata_update_agencies_clients_trigger AFTER UPDATE ON hades_db.entity_metadata
FOR EACH ROW
BEGIN
IF NEW.entity_type_id = 1 THEN
IF NEW.merchant_type <> OLD.merchant_type OR NEW.url <> OLD.url OR NEW.currency <> OLD.currency OR NEW.timezone <> OLD.timezone THEN
UPDATE clients SET merchant_type = IFNULL(NEW.merchant_type, merchant_type),
url = IFNULL(NEW.url, url),
currency_code = NEW.currency, timezone = NEW.timezone
WHERE client_id = NEW.entity_id;
END IF;
ELSEIF NEW.entity_type_id = 2 THEN
UPDATE agencies SET marketplace_client_id = NEW.marketplace_client_id,
agency_type = NEW.custom_label_1
WHERE id = NEW.entity_id;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `entity_types`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `entity_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_types_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_types_insert_trigger BEFORE INSERT ON entity_types
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_entity_types_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_entity_types_update_trigger BEFORE UPDATE ON entity_types
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `mccs_id_generator`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `mccs_id_generator` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_mccs_id_generator_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_mccs_id_generator_insert_trigger BEFORE INSERT ON mccs_id_generator
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_mccs_id_generator_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_mccs_id_generator_update_trigger BEFORE UPDATE ON mccs_id_generator
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `old_app_contexts`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `old_app_contexts` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`app_id` int(11) NOT NULL,
`app_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`context` varchar(1000) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `app_id` (`app_id`,`app_key`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_app_contexts_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_app_contexts_insert_trigger BEFORE INSERT ON `old_app_contexts` FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_app_contexts_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_app_contexts_update_trigger BEFORE UPDATE ON `old_app_contexts` FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `organization_types`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `organization_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_organization_types_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_organization_types_insert_trigger BEFORE INSERT ON hades_db.organization_types
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_organization_types_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_organization_types_update_trigger BEFORE UPDATE ON hades_db.organization_types
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `otp`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `otp` (
`expiration_datetime` datetime NOT NULL,
`user_id` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_otp` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`issue_datetime` datetime NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uc_otp` (`expiration_datetime`,`user_id`,`user_otp`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_otp_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_otp_insert_trigger BEFORE INSERT ON hades_db.otp
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_otp_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_otp_update_trigger BEFORE UPDATE ON hades_db.otp
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `schema_info`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `schema_info` (
`db_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`version` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`filename` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`run_date` datetime DEFAULT NULL,
`status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`db_name`,`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sms_log`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `sms_log` (
`sms_datetime` datetime NOT NULL,
`user_id` int(11) NOT NULL,
`id` int(11) NOT NULL AUTO_INCREMENT,
`otp` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
`log` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uc_otp` (`sms_datetime`,`user_id`,`otp`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_sms_log_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_sms_log_insert_trigger BEFORE INSERT ON hades_db.sms_log
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_sms_log_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_sms_log_update_trigger BEFORE UPDATE ON hades_db.sms_log
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `status_types`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `status_types` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(25) COLLATE utf8mb4_unicode_ci NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_status_types_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_status_types_insert_trigger BEFORE INSERT ON hades_db.status_types
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_status_types_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_status_types_update_trigger BEFORE UPDATE ON hades_db.status_types
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `teams`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `teams` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` char(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`status_type_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` char(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_teams_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_teams_insert_trigger BEFORE INSERT ON hades_db.teams
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_teams_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_teams_update_trigger BEFORE UPDATE ON hades_db.teams
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `tmp_user_client_mappings_del`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `tmp_user_client_mappings_del` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`client_id` int(11) NOT NULL,
`access_role_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`end_user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tmp_users`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `tmp_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` char(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` char(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`contact_no` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`organization_id` int(11) NOT NULL,
`organization_type_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`activation_code` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`activation_code_expiration_time` datetime DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`salt` char(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tokens`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `tokens` (
`token` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
`auth_channel_id` int(11) DEFAULT '1',
`issue_datetime` datetime NOT NULL,
`expiration_datetime` datetime NOT NULL,
`ubid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`token`),
KEY `tokens_idx1` (`ubid`,`expiration_datetime`),
KEY `tokens_idx2` (`token`,`expiration_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `tokens_old`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `tokens_old` (
`token` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`issue_datetime` datetime NOT NULL,
`expiration_datetime` datetime NOT NULL,
`ubid` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`token`),
KEY `tokens_idx1` (`ubid`,`expiration_datetime`),
KEY `tokens_idx2` (`token`,`expiration_datetime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_tokens_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_tokens_insert_trigger BEFORE INSERT ON `tokens_old` FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_tokens_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_tokens_update_trigger BEFORE UPDATE ON `tokens_old` FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `user_agency_clients_delta`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `user_agency_clients_delta` (
`dml_type` int(11) DEFAULT NULL,
`uow_id` bigint(20) DEFAULT NULL,
`fv$gsn` bigint(20) DEFAULT NULL,
`user_id` int(11) NOT NULL DEFAULT '0',
`username` char(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` char(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_id` int(11) NOT NULL DEFAULT '0',
`agency_name` char(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`client_id` int(11) NOT NULL,
`client_name` char(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_access_role_id` int(11) NOT NULL,
`mview$hash` int(10) unsigned DEFAULT NULL,
KEY `uow_id` (`uow_id`),
KEY `fv$gsn` (`fv$gsn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `user_agency_mappings`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `user_agency_mappings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL,
`access_role_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`end_user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_agency_mappings_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_agency_mappings_insert_trigger BEFORE INSERT ON hades_db.user_agency_mappings
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_agency_mappings_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_agency_mappings_update_trigger BEFORE UPDATE ON hades_db.user_agency_mappings
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `user_client_mappings`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `user_client_mappings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`client_id` int(11) NOT NULL,
`access_role_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`end_user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`client_id`),
KEY `ucm_client_id_index` (`client_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_client_mappings_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_client_mappings_insert_trigger BEFORE INSERT ON hades_db.user_client_mappings
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_client_mappings_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_client_mappings_update_trigger BEFORE UPDATE ON hades_db.user_client_mappings
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `user_details`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `user_details` (
`user_id` int(11) NOT NULL,
`key_id` int(11) NOT NULL,
`value` varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`user_id`,`key_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_details_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_details_insert_trigger BEFORE INSERT ON hades_db.user_details
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_details_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_details_update_trigger BEFORE UPDATE ON hades_db.user_details
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `user_entity_mappings`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `user_entity_mappings` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`entity_type_id` int(11) NOT NULL,
`entity_id` int(11) NOT NULL,
`agency_id` int(11) NOT NULL,
`access_role_id` int(11) NOT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`end_user_id` int(11) NOT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `user_id` (`user_id`,`entity_type_id`,`entity_id`,`access_role_id`),
KEY `uem_index_entity` (`entity_type_id`,`entity_id`),
KEY `uem_index_user_agency` (`user_id`,`agency_id`,`is_deleted`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_entity_mappings_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_entity_mappings_insert_trigger BEFORE INSERT ON user_entity_mappings
FOR EACH ROW BEGIN
SET NEW.creation_date=NOW();
SET NEW.last_updated_by=USER();
SET NEW.last_update=NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_entity_mappings_insert_ucm_uam_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_entity_mappings_insert_ucm_uam_trigger AFTER INSERT ON hades_db.user_entity_mappings
FOR EACH ROW
BEGIN
IF NEW.entity_type_id = 1 THEN
INSERT IGNORE INTO user_client_mappings(user_id, client_id, access_role_id, status_type_id,
end_user_id, creation_date, last_update, last_updated_by, is_deleted)
VALUES(NEW.user_id, NEW.entity_id, NEW.access_role_id, 1,
NEW.end_user_id, NEW.creation_date, NEW.last_update, NEW.last_updated_by, NEW.is_deleted);
ELSEIF NEW.entity_type_id = 2 THEN
INSERT IGNORE INTO user_agency_mappings(user_id, agency_id, access_role_id, status_type_id,
end_user_id, creation_date, last_update, last_updated_by, is_deleted)
VALUES(NEW.user_id, NEW.entity_id, NEW.access_role_id, 1,
NEW.end_user_id, NEW.creation_date, NEW.last_update, NEW.last_updated_by, NEW.is_deleted);
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_entity_mappings_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_entity_mappings_update_trigger BEFORE UPDATE ON user_entity_mappings
FOR EACH ROW
BEGIN
SET NEW.last_updated_by=USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_user_entity_mappings_update_ucm_uam_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_user_entity_mappings_update_ucm_uam_trigger AFTER UPDATE ON hades_db.user_entity_mappings
FOR EACH ROW
BEGIN
IF NEW.entity_type_id = 1 THEN
UPDATE user_client_mappings SET is_deleted = NEW.is_deleted
WHERE client_id = NEW.entity_id AND user_id = NEW.user_id AND access_role_id = NEW.access_role_id;
ELSEIF NEW.entity_type_id = 2 THEN
UPDATE user_agency_mappings SET is_deleted = NEW.is_deleted
WHERE agency_id = NEW.entity_id AND user_id = NEW.user_id AND access_role_id = NEW.access_role_id;
END IF;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `users`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` char(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` char(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`email` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`contact_no` char(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`organization_id` int(11) NOT NULL,
`organization_type_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`activation_code` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`activation_code_expiration_time` datetime DEFAULT NULL,
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`salt` char(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_insert_trigger BEFORE INSERT ON hades_db.users
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_update_trigger BEFORE UPDATE ON hades_db.users
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `users_v2`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `users_v2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` char(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` char(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`auth_provider_type_id` int(3) NOT NULL,
`auth_provider_value` char(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`organization_id` int(11) NOT NULL,
`organization_type_id` int(11) NOT NULL,
`status_type_id` int(11) NOT NULL,
`activation_code` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`activation_code_expiration_time` datetime DEFAULT NULL,
`salt` char(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` char(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_provider_type_id` (`auth_provider_type_id`,`auth_provider_value`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v2_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v2_insert_trigger BEFORE INSERT ON hades_db.users_v2
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v2_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v2_update_trigger BEFORE UPDATE ON hades_db.users_v2
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Table structure for table `users_v3`
--
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE IF NOT EXISTS `users_v3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`password` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`auth_provider_type_id` int(3) NOT NULL,
`auth_provider_value` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
`agency_id` int(11) NOT NULL DEFAULT '-1',
`status_type_id` int(11) NOT NULL,
`activation_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`activation_code_expiration_time` datetime DEFAULT NULL,
`salt` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`is_deleted` tinyint(1) NOT NULL DEFAULT '0',
`old_user_id` int(11) DEFAULT '-1',
`creation_date` datetime DEFAULT NULL,
`last_update` datetime DEFAULT NULL,
`last_updated_by` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `auth_provider_type_id` (`auth_provider_type_id`,`auth_provider_value`,`agency_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v3_insert_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v3_insert_trigger BEFORE INSERT ON hades_db.users_v3
FOR EACH ROW BEGIN
SET NEW.creation_date = NOW();
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v3_insert_users_v2_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v3_insert_users_v2_trigger AFTER INSERT ON hades_db.users_v3
FOR EACH ROW
BEGIN
INSERT IGNORE INTO users_v2 (id, user, password, auth_provider_type_id, auth_provider_value, organization_id, organization_type_id, status_type_id, activation_code, activation_code_expiration_time, salt, is_deleted, creation_date, last_update, last_updated_by) VALUES (NEW.id, NEW.user, NEW.password, NEW.auth_provider_type_id, NEW.auth_provider_value, NEW.agency_id, 2, NEW.status_type_id, NEW.activation_code, NEW.activation_code_expiration_time, NEW.salt, NEW.is_deleted, NEW.creation_date, NEW.last_update, NEW.last_updated_by);
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v3_update_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v3_update_trigger BEFORE UPDATE ON hades_db.users_v3
FOR EACH ROW
BEGIN
SET NEW.last_updated_by = USER();
SET NEW.last_update = NOW();
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
DROP TRIGGER IF EXISTS hades_db_users_v3_update_users_v2_trigger;
/*!50003 CREATE*/ /*!50003 TRIGGER hades_db_users_v3_update_users_v2_trigger AFTER UPDATE ON hades_db.users_v3
FOR EACH ROW
BEGIN
UPDATE users_v2 set status_type_id = NEW.status_type_id, is_deleted = NEW.is_deleted,
auth_provider_type_id = NEW.auth_provider_type_id, auth_provider_value = NEW.auth_provider_value,
password = NEW.password, salt = NEW.salt, activation_code = NEW.activation_code,
activation_code_expiration_time = NEW.activation_code_expiration_time
WHERE id = NEW.id;
END */;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
--
-- Dumping routines for database 'hades_db'
--
/*!50003 DROP PROCEDURE IF EXISTS `create_single_agency_hierarchy` */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
DELIMITER ;;
CREATE PROCEDURE `create_single_agency_hierarchy`(IN child_id int(11), IN agency_name char(250), IN status_type int(11))
BEGIN
DECLARE parent_id int(11) DEFAULT -1;
DECLARE level_count int(11) DEFAULT 0;
SELECT parent_agency_id INTO parent_id FROM agencies WHERE id = child_id;
IF EXISTS(SELECT id from agencies where id = parent_id) THEN
WHILE parent_id != -1 DO
SET level_count = level_count + 1;
INSERT IGNORE INTO
agencies_flattened(agency_id, name, ancestor_agency_id, level, status_type_id)
VALUES(child_id, agency_name, parent_id, level_count, status_type);
SELECT parent_agency_id INTO parent_id FROM agencies WHERE id = parent_id;
IF NOT EXISTS(SELECT id from agencies where id = parent_id) THEN
SET parent_id = NULL;
END IF;
END WHILE;
ELSE
INSERT IGNORE INTO
agencies_flattened(agency_id, name, level, status_type_id)
VALUES(child_id, agency_name, 0, status_type);
END IF;
END ;;
DELIMITER ;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;
/*!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 2023-06-01 5:22:53
Reference in New Issue View Git Blame Copy Permalink