# ************************************************************
# Sequel Pro SQL dump
# Version 4096
#
# http://www.sequelpro.com/
# http://code.google.com/p/sequel-pro/
#
# Host: mysql01.online.production.bownty.net (MySQL 10.0.15-MariaDB-1~wheezy-log)
# Database: crowd
# Generation Time: 2015-01-08 11:26:05 +0000
# ************************************************************


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_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 */;


# Dump of table cwd_app_dir_group_mapping
# ------------------------------------------------------------

CREATE TABLE `cwd_app_dir_group_mapping` (
  `id` bigint(20) NOT NULL,
  `app_dir_mapping_id` bigint(20) NOT NULL,
  `application_id` bigint(20) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `app_dir_mapping_id` (`app_dir_mapping_id`,`group_name`),
  UNIQUE KEY `uk_app_dir_group` (`app_dir_mapping_id`,`group_name`),
  KEY `idx_app_dir_group_group_dir` (`directory_id`,`group_name`),
  KEY `fk_app_dir_group_dir` (`directory_id`),
  KEY `fk_app_dir_group_app` (`application_id`),
  KEY `fk_app_dir_group_mapping` (`app_dir_mapping_id`),
  CONSTRAINT `fk_app_dir_group_app` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`),
  CONSTRAINT `fk_app_dir_group_dir` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`),
  CONSTRAINT `fk_app_dir_group_mapping` FOREIGN KEY (`app_dir_mapping_id`) REFERENCES `cwd_app_dir_mapping` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_app_dir_mapping
# ------------------------------------------------------------

CREATE TABLE `cwd_app_dir_mapping` (
  `id` bigint(20) NOT NULL,
  `application_id` bigint(20) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  `allow_all` char(1) NOT NULL,
  `list_index` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `application_id` (`application_id`,`directory_id`),
  UNIQUE KEY `uk_app_dir` (`application_id`,`directory_id`),
  KEY `fk_app_dir_dir` (`directory_id`),
  KEY `fk_app_dir_app` (`application_id`),
  CONSTRAINT `fk_app_dir_app` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`),
  CONSTRAINT `fk_app_dir_dir` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_app_dir_operation
# ------------------------------------------------------------

CREATE TABLE `cwd_app_dir_operation` (
  `app_dir_mapping_id` bigint(20) NOT NULL,
  `operation_type` varchar(32) NOT NULL,
  PRIMARY KEY (`app_dir_mapping_id`,`operation_type`),
  KEY `fk_app_dir_mapping` (`app_dir_mapping_id`),
  CONSTRAINT `fk_app_dir_mapping` FOREIGN KEY (`app_dir_mapping_id`) REFERENCES `cwd_app_dir_mapping` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_application
# ------------------------------------------------------------

CREATE TABLE `cwd_application` (
  `id` bigint(20) NOT NULL,
  `application_name` varchar(255) NOT NULL,
  `lower_application_name` varchar(255) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `active` char(1) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `application_type` varchar(32) NOT NULL,
  `credential` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lower_application_name` (`lower_application_name`),
  UNIQUE KEY `uk_app_l_name` (`lower_application_name`),
  KEY `idx_app_active` (`active`),
  KEY `idx_app_type` (`application_type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_application_address
# ------------------------------------------------------------

CREATE TABLE `cwd_application_address` (
  `application_id` bigint(20) NOT NULL,
  `remote_address` varchar(255) NOT NULL,
  PRIMARY KEY (`application_id`,`remote_address`),
  KEY `fk_application_address` (`application_id`),
  CONSTRAINT `fk_application_address` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_application_alias
# ------------------------------------------------------------

CREATE TABLE `cwd_application_alias` (
  `id` bigint(20) NOT NULL,
  `application_id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `lower_user_name` varchar(255) NOT NULL,
  `alias_name` varchar(255) NOT NULL,
  `lower_alias_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `application_id` (`application_id`,`lower_user_name`),
  UNIQUE KEY `application_id_2` (`application_id`,`lower_alias_name`),
  UNIQUE KEY `uk_alias_app_l_username` (`application_id`,`lower_user_name`),
  UNIQUE KEY `uk_alias_app_l_alias` (`application_id`,`lower_alias_name`),
  KEY `fk_alias_app_id` (`application_id`),
  CONSTRAINT `fk_alias_app_id` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_application_attribute
# ------------------------------------------------------------

CREATE TABLE `cwd_application_attribute` (
  `application_id` bigint(20) NOT NULL,
  `attribute_value` varchar(4000) DEFAULT NULL,
  `attribute_name` varchar(255) NOT NULL,
  PRIMARY KEY (`application_id`,`attribute_name`),
  KEY `fk_application_attribute` (`application_id`),
  CONSTRAINT `fk_application_attribute` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_directory
# ------------------------------------------------------------

CREATE TABLE `cwd_directory` (
  `id` bigint(20) NOT NULL,
  `directory_name` varchar(255) NOT NULL,
  `lower_directory_name` varchar(255) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `active` char(1) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `impl_class` varchar(255) NOT NULL,
  `lower_impl_class` varchar(255) NOT NULL,
  `directory_type` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lower_directory_name` (`lower_directory_name`),
  UNIQUE KEY `uk_dir_l_name` (`lower_directory_name`),
  KEY `idx_dir_type` (`directory_type`),
  KEY `idx_dir_active` (`active`),
  KEY `idx_dir_l_impl_class` (`lower_impl_class`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_directory_attribute
# ------------------------------------------------------------

CREATE TABLE `cwd_directory_attribute` (
  `directory_id` bigint(20) NOT NULL,
  `attribute_value` varchar(4000) DEFAULT NULL,
  `attribute_name` varchar(255) NOT NULL,
  PRIMARY KEY (`directory_id`,`attribute_name`),
  KEY `fk_directory_attribute` (`directory_id`),
  CONSTRAINT `fk_directory_attribute` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_directory_operation
# ------------------------------------------------------------

CREATE TABLE `cwd_directory_operation` (
  `directory_id` bigint(20) NOT NULL,
  `operation_type` varchar(32) NOT NULL,
  PRIMARY KEY (`directory_id`,`operation_type`),
  KEY `fk_directory_operation` (`directory_id`),
  CONSTRAINT `fk_directory_operation` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_group
# ------------------------------------------------------------

CREATE TABLE `cwd_group` (
  `id` bigint(20) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `lower_group_name` varchar(255) NOT NULL,
  `active` char(1) NOT NULL,
  `is_local` char(1) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `group_type` varchar(32) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lower_group_name` (`lower_group_name`,`directory_id`),
  UNIQUE KEY `uk_group_name_dir_id` (`lower_group_name`,`directory_id`),
  KEY `idx_group_active` (`active`,`directory_id`),
  KEY `idx_group_dir_id` (`directory_id`),
  KEY `fk_directory_id` (`directory_id`),
  CONSTRAINT `fk_directory_id` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_group_attribute
# ------------------------------------------------------------

CREATE TABLE `cwd_group_attribute` (
  `id` bigint(20) NOT NULL,
  `group_id` bigint(20) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  `attribute_name` varchar(255) NOT NULL,
  `attribute_value` varchar(255) DEFAULT NULL,
  `attribute_lower_value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `group_id` (`group_id`,`attribute_name`,`attribute_lower_value`),
  UNIQUE KEY `uk_group_name_attr_lval` (`group_id`,`attribute_name`,`attribute_lower_value`),
  KEY `idx_group_attr_dir_name_lval` (`directory_id`,`attribute_name`,`attribute_lower_value`),
  KEY `idx_group_attr_group_id` (`group_id`),
  KEY `fk_group_attr_dir_id` (`directory_id`),
  KEY `fk_group_attr_id_group_id` (`group_id`),
  CONSTRAINT `fk_group_attr_dir_id` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`),
  CONSTRAINT `fk_group_attr_id_group_id` FOREIGN KEY (`group_id`) REFERENCES `cwd_group` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_membership
# ------------------------------------------------------------

CREATE TABLE `cwd_membership` (
  `id` bigint(20) NOT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `child_id` bigint(20) DEFAULT NULL,
  `membership_type` varchar(32) DEFAULT NULL,
  `group_type` varchar(32) NOT NULL,
  `parent_name` varchar(255) NOT NULL,
  `lower_parent_name` varchar(255) NOT NULL,
  `child_name` varchar(255) NOT NULL,
  `lower_child_name` varchar(255) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `parent_id` (`parent_id`,`child_id`,`membership_type`),
  UNIQUE KEY `uk_mem_parent_child_type` (`parent_id`,`child_id`,`membership_type`),
  KEY `idx_mem_dir_child` (`membership_type`,`lower_child_name`,`directory_id`),
  KEY `idx_mem_dir_parent` (`membership_type`,`lower_parent_name`,`directory_id`),
  KEY `idx_mem_dir_parent_child` (`membership_type`,`lower_parent_name`,`lower_child_name`,`directory_id`),
  KEY `fk_membership_dir` (`directory_id`),
  CONSTRAINT `fk_membership_dir` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_property
# ------------------------------------------------------------

CREATE TABLE `cwd_property` (
  `property_key` varchar(255) NOT NULL,
  `property_name` varchar(255) NOT NULL,
  `property_value` varchar(4000) DEFAULT NULL,
  PRIMARY KEY (`property_key`,`property_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_token
# ------------------------------------------------------------

CREATE TABLE `cwd_token` (
  `id` bigint(20) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  `entity_name` varchar(255) NOT NULL,
  `random_number` bigint(20) NOT NULL,
  `identifier_hash` varchar(255) NOT NULL,
  `random_hash` varchar(255) NOT NULL,
  `created_date` datetime NOT NULL,
  `last_accessed_date` datetime NOT NULL,
  `last_accessed_time` bigint(20) NOT NULL DEFAULT '0',
  `duration` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `identifier_hash` (`identifier_hash`),
  UNIQUE KEY `uk_token_id_hash` (`identifier_hash`),
  KEY `idx_token_key` (`random_hash`),
  KEY `idx_token_dir_id` (`directory_id`),
  KEY `idx_token_name_dir_id` (`directory_id`,`entity_name`),
  KEY `idx_token_last_access` (`last_accessed_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_user
# ------------------------------------------------------------

CREATE TABLE `cwd_user` (
  `id` bigint(20) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `lower_user_name` varchar(255) NOT NULL,
  `active` char(1) NOT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `lower_first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  `lower_last_name` varchar(255) DEFAULT NULL,
  `display_name` varchar(255) DEFAULT NULL,
  `lower_display_name` varchar(255) DEFAULT NULL,
  `email_address` varchar(255) DEFAULT NULL,
  `lower_email_address` varchar(255) DEFAULT NULL,
  `directory_id` bigint(20) NOT NULL,
  `credential` varchar(255) DEFAULT NULL,
  `external_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lower_user_name` (`lower_user_name`,`directory_id`),
  UNIQUE KEY `uk_user_name_dir_id` (`lower_user_name`,`directory_id`),
  KEY `idx_user_lower_display_name` (`lower_display_name`,`directory_id`),
  KEY `idx_user_lower_last_name` (`lower_last_name`,`directory_id`),
  KEY `idx_user_active` (`active`,`directory_id`),
  KEY `idx_user_name_dir_id` (`directory_id`),
  KEY `idx_user_lower_first_name` (`lower_first_name`,`directory_id`),
  KEY `idx_user_lower_email_address` (`lower_email_address`,`directory_id`),
  KEY `fk_user_dir_id` (`directory_id`),
  KEY `idx_external_id` (`external_id`),
  CONSTRAINT `fk_user_dir_id` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_user_attribute
# ------------------------------------------------------------

CREATE TABLE `cwd_user_attribute` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `directory_id` bigint(20) NOT NULL,
  `attribute_name` varchar(255) NOT NULL,
  `attribute_value` varchar(255) DEFAULT NULL,
  `attribute_lower_value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`attribute_name`,`attribute_lower_value`),
  UNIQUE KEY `uk_user_attr_name_lval` (`user_id`,`attribute_name`,`attribute_lower_value`),
  KEY `idx_user_attr_dir_name_lval` (`directory_id`,`attribute_name`,`attribute_lower_value`),
  KEY `idx_user_attr_user_id` (`user_id`),
  KEY `fk_user_attr_dir_id` (`directory_id`),
  KEY `fk_user_attribute_id_user_id` (`user_id`),
  CONSTRAINT `fk_user_attr_dir_id` FOREIGN KEY (`directory_id`) REFERENCES `cwd_directory` (`id`),
  CONSTRAINT `fk_user_attribute_id_user_id` FOREIGN KEY (`user_id`) REFERENCES `cwd_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_user_credential_record
# ------------------------------------------------------------

CREATE TABLE `cwd_user_credential_record` (
  `id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `list_index` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_user_cred_user` (`user_id`),
  CONSTRAINT `fk_user_cred_user` FOREIGN KEY (`user_id`) REFERENCES `cwd_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



# Dump of table cwd_webhook
# ------------------------------------------------------------

CREATE TABLE `cwd_webhook` (
  `id` bigint(20) NOT NULL,
  `endpoint_url` varchar(255) NOT NULL,
  `application_id` bigint(20) NOT NULL,
  `token` varchar(255) DEFAULT NULL,
  `oldest_failure_date` datetime DEFAULT NULL,
  `failures_since_last_success` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_webhook_url_app` (`endpoint_url`,`application_id`),
  KEY `idx_webhook_url_app` (`endpoint_url`,`application_id`),
  KEY `fk_webhook_app` (`application_id`),
  CONSTRAINT `fk_webhook_app` FOREIGN KEY (`application_id`) REFERENCES `cwd_application` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table hibernate_unique_key
# ------------------------------------------------------------

CREATE TABLE `hibernate_unique_key` (
  `next_hi` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_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 */;
