diff options
| -rw-r--r-- | ChangeLog | 1 | ||||
| -rw-r--r-- | Makefile | 13 | ||||
| -rw-r--r-- | app/Schema/Sql/mysql.sql | 524 | ||||
| -rw-r--r-- | app/Schema/Sql/postgres.sql | 1843 | ||||
| -rw-r--r-- | doc/mysql-configuration.markdown | 13 | ||||
| -rw-r--r-- | doc/postgresql-configuration.markdown | 12 |
6 files changed, 2406 insertions, 0 deletions
@@ -21,6 +21,7 @@ Core functionalities moved to plugins: Improvements: +* Offer alternative method to create Mysql and Postgres databases (import sql dump) * Make sure there is always a trailing slash for application_url * Do not show the checkbox "Show default swimlane" when there is no active swimlanes * Append filters instead of replacing value for users and categories dropdowns @@ -88,4 +88,17 @@ test-postgres: unittest: test-sqlite test-mysql test-postgres +sql: + @ pg_dump --schema-only --no-owner --file app/Schema/Sql/postgres.sql kanboard + @ mysqldump -uroot --quote-names --no-create-db --skip-comments --no-data --single-transaction kanboard | sed 's/ AUTO_INCREMENT=[0-9]*//g' > app/Schema/Sql/mysql.sql + + @ php -r "echo 'INSERT INTO users (username, password, is_admin) VALUES (\'admin\', \''.password_hash('admin', PASSWORD_DEFAULT).'\', \'1\');';" | \ + tee -a app/Schema/Sql/postgres.sql app/Schema/Sql/mysql.sql >/dev/null + + @ let mysql_version=`echo 'select version from schema_version;' | mysql -N -uroot kanboard` ;\ + echo "INSERT INTO schema_version VALUES ('$$mysql_version');" >> app/Schema/Sql/mysql.sql + + @ let pg_version=`psql -U postgres -A -c 'copy(select version from schema_version) to stdout;' kanboard` ;\ + echo "INSERT INTO schema_version VALUES ('$$pg_version');" >> app/Schema/Sql/postgres.sql + .PHONY: all diff --git a/app/Schema/Sql/mysql.sql b/app/Schema/Sql/mysql.sql new file mode 100644 index 00000000..a4e0a7ff --- /dev/null +++ b/app/Schema/Sql/mysql.sql @@ -0,0 +1,524 @@ + +/*!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 */; +DROP TABLE IF EXISTS `action_has_params`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `action_has_params` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `action_id` int(11) NOT NULL, + `name` varchar(50) NOT NULL, + `value` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `action_id` (`action_id`), + CONSTRAINT `action_has_params_ibfk_1` FOREIGN KEY (`action_id`) REFERENCES `actions` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `actions`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `actions` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `project_id` int(11) NOT NULL, + `event_name` varchar(50) NOT NULL, + `action_name` varchar(50) NOT NULL, + PRIMARY KEY (`id`), + KEY `project_id` (`project_id`), + CONSTRAINT `actions_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `columns`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `columns` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(255) NOT NULL, + `position` int(11) NOT NULL, + `project_id` int(11) NOT NULL, + `task_limit` int(11) DEFAULT '0', + `description` text, + PRIMARY KEY (`id`), + UNIQUE KEY `idx_title_project` (`title`,`project_id`), + KEY `columns_project_idx` (`project_id`), + CONSTRAINT `columns_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `comments`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `comments` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `task_id` int(11) NOT NULL, + `user_id` int(11) DEFAULT '0', + `date_creation` bigint(20) DEFAULT NULL, + `comment` text, + `reference` varchar(50) DEFAULT '', + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + KEY `comments_reference_idx` (`reference`), + KEY `comments_task_idx` (`task_id`), + CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `currencies`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `currencies` ( + `currency` char(3) NOT NULL, + `rate` float DEFAULT '0', + UNIQUE KEY `currency` (`currency`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `custom_filters`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `custom_filters` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `filter` varchar(100) NOT NULL, + `project_id` int(11) NOT NULL, + `user_id` int(11) NOT NULL, + `name` varchar(100) NOT NULL, + `is_shared` tinyint(1) DEFAULT '0', + PRIMARY KEY (`id`), + KEY `project_id` (`project_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `custom_filters_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, + CONSTRAINT `custom_filters_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `files`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `files` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `path` varchar(255) DEFAULT NULL, + `is_image` tinyint(1) DEFAULT '0', + `task_id` int(11) NOT NULL, + `date` bigint(20) DEFAULT NULL, + `user_id` int(11) NOT NULL DEFAULT '0', + `size` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + KEY `files_task_idx` (`task_id`), + CONSTRAINT `files_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `last_logins`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `last_logins` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `auth_type` varchar(25) DEFAULT NULL, + `user_id` int(11) DEFAULT NULL, + `ip` varchar(40) DEFAULT NULL, + `user_agent` varchar(255) DEFAULT NULL, + `date_creation` bigint(20) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + CONSTRAINT `last_logins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `links`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `links` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `label` varchar(255) NOT NULL, + `opposite_id` int(11) DEFAULT '0', + PRIMARY KEY (`id`), + UNIQUE KEY `label` (`label`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `plugin_schema_versions`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `plugin_schema_versions` ( + `plugin` varchar(80) NOT NULL, + `version` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`plugin`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_activities`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_activities` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `date_creation` bigint(20) DEFAULT NULL, + `event_name` varchar(50) NOT NULL, + `creator_id` int(11) DEFAULT NULL, + `project_id` int(11) DEFAULT NULL, + `task_id` int(11) DEFAULT NULL, + `data` text, + PRIMARY KEY (`id`), + KEY `creator_id` (`creator_id`), + KEY `project_id` (`project_id`), + KEY `task_id` (`task_id`), + CONSTRAINT `project_activities_ibfk_1` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, + CONSTRAINT `project_activities_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, + CONSTRAINT `project_activities_ibfk_3` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_daily_column_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_daily_column_stats` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `day` char(10) NOT NULL, + `project_id` int(11) NOT NULL, + `column_id` int(11) NOT NULL, + `total` int(11) NOT NULL DEFAULT '0', + `score` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + UNIQUE KEY `project_daily_column_stats_idx` (`day`,`project_id`,`column_id`), + KEY `column_id` (`column_id`), + KEY `project_id` (`project_id`), + CONSTRAINT `project_daily_column_stats_ibfk_1` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, + CONSTRAINT `project_daily_column_stats_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_daily_stats`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_daily_stats` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `day` char(10) NOT NULL, + `project_id` int(11) NOT NULL, + `avg_lead_time` int(11) NOT NULL DEFAULT '0', + `avg_cycle_time` int(11) NOT NULL DEFAULT '0', + PRIMARY KEY (`id`), + UNIQUE KEY `project_daily_stats_idx` (`day`,`project_id`), + KEY `project_id` (`project_id`), + CONSTRAINT `project_daily_stats_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_has_categories`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_has_categories` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `project_id` int(11) NOT NULL, + `description` text, + PRIMARY KEY (`id`), + UNIQUE KEY `idx_project_category` (`project_id`,`name`), + KEY `categories_project_idx` (`project_id`), + CONSTRAINT `project_has_categories_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_has_users`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_has_users` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `project_id` int(11) NOT NULL, + `user_id` int(11) NOT NULL, + `is_owner` tinyint(1) DEFAULT '0', + PRIMARY KEY (`id`), + UNIQUE KEY `idx_project_user` (`project_id`,`user_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `project_has_users_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, + CONSTRAINT `project_has_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `project_integrations`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_integrations` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `project_id` int(11) NOT NULL, + `hipchat` tinyint(1) DEFAULT '0', + `hipchat_api_url` varchar(255) DEFAULT 'https://api.hipchat.com', + `hipchat_room_id` varchar(255) DEFAULT NULL, + `hipchat_room_token` varchar(255) DEFAULT NULL, + `slack` tinyint(1) DEFAULT '0', + `slack_webhook_url` varchar(255) DEFAULT NULL, + `jabber` int(11) DEFAULT '0', + `jabber_server` varchar(255) DEFAULT '', + `jabber_domain` varchar(255) DEFAULT '', + `jabber_username` varchar(255) DEFAULT '', + `jabber_password` varchar(255) DEFAULT '', + `jabber_nickname` varchar(255) DEFAULT 'kanboard', + `jabber_room` varchar(255) DEFAULT '', + `slack_webhook_channel` varchar(255) DEFAULT '', + PRIMARY KEY (`id`), + UNIQUE KEY `project_id` (`project_id`), + CONSTRAINT `project_integrations_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `projects`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `projects` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(255) NOT NULL, + `is_active` tinyint(4) DEFAULT '1', + `token` varchar(255) DEFAULT NULL, + `last_modified` bigint(20) DEFAULT NULL, + `is_public` tinyint(1) DEFAULT '0', + `is_private` tinyint(1) DEFAULT '0', + `is_everybody_allowed` tinyint(1) DEFAULT '0', + `default_swimlane` varchar(200) DEFAULT 'Default swimlane', + `show_default_swimlane` int(11) DEFAULT '1', + `description` text, + `identifier` varchar(50) DEFAULT '', + `start_date` varchar(10) DEFAULT '', + `end_date` varchar(10) DEFAULT '', + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`), + UNIQUE KEY `name_2` (`name`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `remember_me`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `remember_me` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) DEFAULT NULL, + `ip` varchar(40) DEFAULT NULL, + `user_agent` varchar(255) DEFAULT NULL, + `token` varchar(255) DEFAULT NULL, + `sequence` varchar(255) DEFAULT NULL, + `expiration` int(11) DEFAULT NULL, + `date_creation` bigint(20) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + CONSTRAINT `remember_me_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `schema_version`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `schema_version` ( + `version` int(11) DEFAULT '0' +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `settings`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `settings` ( + `option` varchar(100) NOT NULL, + `value` varchar(255) DEFAULT '', + PRIMARY KEY (`option`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `subtask_time_tracking`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `subtask_time_tracking` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `subtask_id` int(11) NOT NULL, + `start` bigint(20) DEFAULT NULL, + `end` bigint(20) DEFAULT NULL, + `time_spent` float DEFAULT '0', + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + KEY `subtask_id` (`subtask_id`), + CONSTRAINT `subtask_time_tracking_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, + CONSTRAINT `subtask_time_tracking_ibfk_2` FOREIGN KEY (`subtask_id`) REFERENCES `subtasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `subtasks`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `subtasks` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(255) NOT NULL, + `status` int(11) DEFAULT '0', + `time_estimated` float DEFAULT NULL, + `time_spent` float DEFAULT NULL, + `task_id` int(11) NOT NULL, + `user_id` int(11) DEFAULT NULL, + `position` int(11) DEFAULT '1', + PRIMARY KEY (`id`), + KEY `subtasks_task_idx` (`task_id`), + CONSTRAINT `subtasks_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `swimlanes`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `swimlanes` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `name` varchar(200) NOT NULL, + `position` int(11) DEFAULT '1', + `is_active` int(11) DEFAULT '1', + `project_id` int(11) DEFAULT NULL, + `description` text, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`,`project_id`), + KEY `swimlanes_project_idx` (`project_id`), + CONSTRAINT `swimlanes_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `task_has_links`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `task_has_links` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `link_id` int(11) NOT NULL, + `task_id` int(11) NOT NULL, + `opposite_task_id` int(11) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `task_has_links_unique` (`link_id`,`task_id`,`opposite_task_id`), + KEY `opposite_task_id` (`opposite_task_id`), + KEY `task_has_links_task_index` (`task_id`), + CONSTRAINT `task_has_links_ibfk_1` FOREIGN KEY (`link_id`) REFERENCES `links` (`id`) ON DELETE CASCADE, + CONSTRAINT `task_has_links_ibfk_2` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE, + CONSTRAINT `task_has_links_ibfk_3` FOREIGN KEY (`opposite_task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `tasks`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `tasks` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `title` varchar(255) NOT NULL, + `description` text, + `date_creation` bigint(20) DEFAULT NULL, + `date_completed` bigint(20) DEFAULT NULL, + `date_due` bigint(20) DEFAULT NULL, + `color_id` varchar(50) DEFAULT NULL, + `project_id` int(11) NOT NULL, + `column_id` int(11) NOT NULL, + `owner_id` int(11) DEFAULT '0', + `position` int(11) DEFAULT NULL, + `score` int(11) DEFAULT NULL, + `is_active` tinyint(4) DEFAULT '1', + `category_id` int(11) DEFAULT '0', + `creator_id` int(11) DEFAULT '0', + `date_modification` int(11) DEFAULT '0', + `reference` varchar(50) DEFAULT '', + `date_started` bigint(20) DEFAULT NULL, + `time_spent` float DEFAULT '0', + `time_estimated` float DEFAULT '0', + `swimlane_id` int(11) DEFAULT '0', + `date_moved` bigint(20) DEFAULT NULL, + `recurrence_status` int(11) NOT NULL DEFAULT '0', + `recurrence_trigger` int(11) NOT NULL DEFAULT '0', + `recurrence_factor` int(11) NOT NULL DEFAULT '0', + `recurrence_timeframe` int(11) NOT NULL DEFAULT '0', + `recurrence_basedate` int(11) NOT NULL DEFAULT '0', + `recurrence_parent` int(11) DEFAULT NULL, + `recurrence_child` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `idx_task_active` (`is_active`), + KEY `column_id` (`column_id`), + KEY `tasks_reference_idx` (`reference`), + KEY `tasks_project_idx` (`project_id`), + CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, + CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `transitions`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `transitions` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `project_id` int(11) NOT NULL, + `task_id` int(11) NOT NULL, + `src_column_id` int(11) NOT NULL, + `dst_column_id` int(11) NOT NULL, + `date` bigint(20) DEFAULT NULL, + `time_spent` int(11) DEFAULT '0', + PRIMARY KEY (`id`), + KEY `src_column_id` (`src_column_id`), + KEY `dst_column_id` (`dst_column_id`), + KEY `transitions_task_index` (`task_id`), + KEY `transitions_project_index` (`project_id`), + KEY `transitions_user_index` (`user_id`), + CONSTRAINT `transitions_ibfk_1` FOREIGN KEY (`src_column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, + CONSTRAINT `transitions_ibfk_2` FOREIGN KEY (`dst_column_id`) REFERENCES `columns` (`id`) ON DELETE CASCADE, + CONSTRAINT `transitions_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, + CONSTRAINT `transitions_ibfk_4` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE, + CONSTRAINT `transitions_ibfk_5` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `user_has_notification_types`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `user_has_notification_types` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `notification_type` varchar(50) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `user_has_notification_types_user_idx` (`user_id`,`notification_type`), + CONSTRAINT `user_has_notification_types_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `user_has_notifications`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `user_has_notifications` ( + `user_id` int(11) NOT NULL, + `project_id` int(11) NOT NULL, + UNIQUE KEY `project_id` (`project_id`,`user_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `user_has_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, + CONSTRAINT `user_has_notifications_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `user_has_unread_notifications`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `user_has_unread_notifications` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `user_id` int(11) NOT NULL, + `date_creation` bigint(20) NOT NULL, + `event_name` varchar(50) NOT NULL, + `event_data` text NOT NULL, + PRIMARY KEY (`id`), + KEY `user_id` (`user_id`), + CONSTRAINT `user_has_unread_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +DROP TABLE IF EXISTS `users`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `users` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `username` varchar(50) NOT NULL, + `password` varchar(255) DEFAULT NULL, + `is_admin` tinyint(4) DEFAULT '0', + `is_ldap_user` tinyint(1) DEFAULT '0', + `name` varchar(255) DEFAULT NULL, + `email` varchar(255) DEFAULT NULL, + `google_id` varchar(30) DEFAULT NULL, + `github_id` varchar(30) DEFAULT NULL, + `notifications_enabled` tinyint(1) DEFAULT '0', + `timezone` varchar(50) DEFAULT NULL, + `language` char(5) DEFAULT NULL, + `disable_login_form` tinyint(1) DEFAULT '0', + `twofactor_activated` tinyint(1) DEFAULT '0', + `twofactor_secret` char(16) DEFAULT NULL, + `token` varchar(255) DEFAULT '', + `notifications_filter` int(11) DEFAULT '4', + `nb_failed_login` int(11) DEFAULT '0', + `lock_expiration_date` bigint(20) DEFAULT NULL, + `is_project_admin` int(11) DEFAULT '0', + `gitlab_id` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `users_username_idx` (`username`), + KEY `users_admin_idx` (`is_admin`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +/*!40101 SET character_set_client = @saved_cs_client */; +/*!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 */; + +INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$BBxt.HuQCFur7qhICY22i.A6OKyce0KGqmMEWq3JdGT4WcEKCBWP.', '1');INSERT INTO schema_version VALUES ('90'); diff --git a/app/Schema/Sql/postgres.sql b/app/Schema/Sql/postgres.sql new file mode 100644 index 00000000..e686e8d2 --- /dev/null +++ b/app/Schema/Sql/postgres.sql @@ -0,0 +1,1843 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET lock_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: - +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: - +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- Name: action_has_params; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE action_has_params ( + id integer NOT NULL, + action_id integer NOT NULL, + name character varying(50) NOT NULL, + value character varying(50) NOT NULL +); + + +-- +-- Name: action_has_params_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE action_has_params_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: action_has_params_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE action_has_params_id_seq OWNED BY action_has_params.id; + + +-- +-- Name: actions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE actions ( + id integer NOT NULL, + project_id integer NOT NULL, + event_name character varying(50) NOT NULL, + action_name character varying(50) NOT NULL +); + + +-- +-- Name: actions_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE actions_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: actions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE actions_id_seq OWNED BY actions.id; + + +-- +-- Name: columns; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE columns ( + id integer NOT NULL, + title character varying(255) NOT NULL, + "position" integer, + project_id integer NOT NULL, + task_limit integer DEFAULT 0, + description text +); + + +-- +-- Name: columns_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE columns_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: columns_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE columns_id_seq OWNED BY columns.id; + + +-- +-- Name: comments; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE comments ( + id integer NOT NULL, + task_id integer NOT NULL, + user_id integer DEFAULT 0, + date_creation bigint NOT NULL, + comment text, + reference character varying(50) DEFAULT ''::character varying +); + + +-- +-- Name: comments_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE comments_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: comments_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE comments_id_seq OWNED BY comments.id; + + +-- +-- Name: currencies; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE currencies ( + currency character(3) NOT NULL, + rate real DEFAULT 0 +); + + +-- +-- Name: custom_filters; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE custom_filters ( + id integer NOT NULL, + filter character varying(100) NOT NULL, + project_id integer NOT NULL, + user_id integer NOT NULL, + name character varying(100) NOT NULL, + is_shared boolean DEFAULT false +); + + +-- +-- Name: custom_filters_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE custom_filters_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: custom_filters_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE custom_filters_id_seq OWNED BY custom_filters.id; + + +-- +-- Name: files; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE files ( + id integer NOT NULL, + name character varying(255) NOT NULL, + path character varying(255), + is_image boolean DEFAULT false, + task_id integer NOT NULL, + date bigint DEFAULT 0 NOT NULL, + user_id integer DEFAULT 0 NOT NULL, + size integer DEFAULT 0 NOT NULL +); + + +-- +-- Name: last_logins; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE last_logins ( + id integer NOT NULL, + auth_type character varying(25), + user_id integer, + ip character varying(40), + user_agent character varying(255), + date_creation bigint +); + + +-- +-- Name: last_logins_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE last_logins_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: last_logins_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE last_logins_id_seq OWNED BY last_logins.id; + + +-- +-- Name: links; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE links ( + id integer NOT NULL, + label character varying(255) NOT NULL, + opposite_id integer DEFAULT 0 +); + + +-- +-- Name: links_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE links_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: links_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE links_id_seq OWNED BY links.id; + + +-- +-- Name: plugin_schema_versions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE plugin_schema_versions ( + plugin character varying(80) NOT NULL, + version integer DEFAULT 0 NOT NULL +); + + +-- +-- Name: project_activities; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_activities ( + id integer NOT NULL, + date_creation bigint NOT NULL, + event_name character varying(50) NOT NULL, + creator_id integer, + project_id integer, + task_id integer, + data text +); + + +-- +-- Name: project_activities_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_activities_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_activities_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_activities_id_seq OWNED BY project_activities.id; + + +-- +-- Name: project_daily_column_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_daily_column_stats ( + id integer NOT NULL, + day character(10) NOT NULL, + project_id integer NOT NULL, + column_id integer NOT NULL, + total integer DEFAULT 0 NOT NULL, + score integer DEFAULT 0 NOT NULL +); + + +-- +-- Name: project_daily_stats; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_daily_stats ( + id integer NOT NULL, + day character(10) NOT NULL, + project_id integer NOT NULL, + avg_lead_time integer DEFAULT 0 NOT NULL, + avg_cycle_time integer DEFAULT 0 NOT NULL +); + + +-- +-- Name: project_daily_stats_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_daily_stats_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_daily_stats_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_daily_stats_id_seq OWNED BY project_daily_stats.id; + + +-- +-- Name: project_daily_summaries_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_daily_summaries_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_daily_summaries_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_daily_summaries_id_seq OWNED BY project_daily_column_stats.id; + + +-- +-- Name: project_has_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_has_categories ( + id integer NOT NULL, + name character varying(255) NOT NULL, + project_id integer NOT NULL, + description text +); + + +-- +-- Name: project_has_categories_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_has_categories_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_has_categories_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_has_categories_id_seq OWNED BY project_has_categories.id; + + +-- +-- Name: project_has_users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_has_users ( + id integer NOT NULL, + project_id integer NOT NULL, + user_id integer NOT NULL, + is_owner boolean DEFAULT false +); + + +-- +-- Name: project_has_users_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_has_users_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_has_users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_has_users_id_seq OWNED BY project_has_users.id; + + +-- +-- Name: project_integrations; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_integrations ( + id integer NOT NULL, + project_id integer NOT NULL, + hipchat boolean DEFAULT false, + hipchat_api_url character varying(255) DEFAULT 'https://api.hipchat.com'::character varying, + hipchat_room_id character varying(255), + hipchat_room_token character varying(255), + slack boolean DEFAULT false, + slack_webhook_url character varying(255), + jabber integer DEFAULT 0, + jabber_server character varying(255) DEFAULT ''::character varying, + jabber_domain character varying(255) DEFAULT ''::character varying, + jabber_username character varying(255) DEFAULT ''::character varying, + jabber_password character varying(255) DEFAULT ''::character varying, + jabber_nickname character varying(255) DEFAULT 'kanboard'::character varying, + jabber_room character varying(255) DEFAULT ''::character varying, + slack_webhook_channel character varying(255) DEFAULT ''::character varying +); + + +-- +-- Name: project_integrations_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE project_integrations_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: project_integrations_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE project_integrations_id_seq OWNED BY project_integrations.id; + + +-- +-- Name: projects; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE projects ( + id integer NOT NULL, + name character varying(255) NOT NULL, + is_active boolean DEFAULT true, + token character varying(255), + last_modified bigint DEFAULT 0, + is_public boolean DEFAULT false, + is_private boolean DEFAULT false, + is_everybody_allowed boolean DEFAULT false, + default_swimlane character varying(200) DEFAULT 'Default swimlane'::character varying, + show_default_swimlane boolean DEFAULT true, + description text, + identifier character varying(50) DEFAULT ''::character varying, + start_date character varying(10) DEFAULT ''::character varying, + end_date character varying(10) DEFAULT ''::character varying +); + + +-- +-- Name: projects_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE projects_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: projects_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE projects_id_seq OWNED BY projects.id; + + +-- +-- Name: remember_me; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE remember_me ( + id integer NOT NULL, + user_id integer, + ip character varying(40), + user_agent character varying(255), + token character varying(255), + sequence character varying(255), + expiration integer, + date_creation bigint +); + + +-- +-- Name: remember_me_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE remember_me_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: remember_me_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE remember_me_id_seq OWNED BY remember_me.id; + + +-- +-- Name: schema_version; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE schema_version ( + version integer DEFAULT 0 +); + + +-- +-- Name: settings; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE settings ( + option character varying(100) NOT NULL, + value character varying(255) DEFAULT ''::character varying +); + + +-- +-- Name: subtask_time_tracking; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE subtask_time_tracking ( + id integer NOT NULL, + user_id integer NOT NULL, + subtask_id integer NOT NULL, + start bigint DEFAULT 0, + "end" bigint DEFAULT 0, + time_spent real DEFAULT 0 +); + + +-- +-- Name: subtask_time_tracking_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE subtask_time_tracking_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: subtask_time_tracking_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE subtask_time_tracking_id_seq OWNED BY subtask_time_tracking.id; + + +-- +-- Name: subtasks; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE subtasks ( + id integer NOT NULL, + title character varying(255) NOT NULL, + status smallint DEFAULT 0, + time_estimated double precision DEFAULT 0, + time_spent double precision DEFAULT 0, + task_id integer NOT NULL, + user_id integer, + "position" integer DEFAULT 1 +); + + +-- +-- Name: swimlanes; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE swimlanes ( + id integer NOT NULL, + name character varying(200) NOT NULL, + "position" integer DEFAULT 1, + is_active boolean DEFAULT true, + project_id integer, + description text +); + + +-- +-- Name: swimlanes_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE swimlanes_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: swimlanes_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE swimlanes_id_seq OWNED BY swimlanes.id; + + +-- +-- Name: task_has_files_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE task_has_files_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: task_has_files_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE task_has_files_id_seq OWNED BY files.id; + + +-- +-- Name: task_has_links; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE task_has_links ( + id integer NOT NULL, + link_id integer NOT NULL, + task_id integer NOT NULL, + opposite_task_id integer NOT NULL +); + + +-- +-- Name: task_has_links_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE task_has_links_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: task_has_links_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE task_has_links_id_seq OWNED BY task_has_links.id; + + +-- +-- Name: task_has_subtasks_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE task_has_subtasks_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: task_has_subtasks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE task_has_subtasks_id_seq OWNED BY subtasks.id; + + +-- +-- Name: tasks; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE tasks ( + id integer NOT NULL, + title character varying(255) NOT NULL, + description text, + date_creation bigint, + color_id character varying(255), + project_id integer NOT NULL, + column_id integer NOT NULL, + owner_id integer DEFAULT 0, + "position" integer, + is_active boolean DEFAULT true, + date_completed bigint, + score integer, + date_due bigint, + category_id integer DEFAULT 0, + creator_id integer DEFAULT 0, + date_modification integer DEFAULT 0, + reference character varying(50) DEFAULT ''::character varying, + date_started bigint, + time_spent double precision DEFAULT 0, + time_estimated double precision DEFAULT 0, + swimlane_id integer DEFAULT 0, + date_moved bigint DEFAULT 0, + recurrence_status integer DEFAULT 0 NOT NULL, + recurrence_trigger integer DEFAULT 0 NOT NULL, + recurrence_factor integer DEFAULT 0 NOT NULL, + recurrence_timeframe integer DEFAULT 0 NOT NULL, + recurrence_basedate integer DEFAULT 0 NOT NULL, + recurrence_parent integer, + recurrence_child integer +); + + +-- +-- Name: tasks_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE tasks_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: tasks_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE tasks_id_seq OWNED BY tasks.id; + + +-- +-- Name: transitions; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE transitions ( + id integer NOT NULL, + user_id integer NOT NULL, + project_id integer NOT NULL, + task_id integer NOT NULL, + src_column_id integer NOT NULL, + dst_column_id integer NOT NULL, + date bigint NOT NULL, + time_spent integer DEFAULT 0 +); + + +-- +-- Name: transitions_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE transitions_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: transitions_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE transitions_id_seq OWNED BY transitions.id; + + +-- +-- Name: user_has_notification_types; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE user_has_notification_types ( + id integer NOT NULL, + user_id integer NOT NULL, + notification_type character varying(50) +); + + +-- +-- Name: user_has_notification_types_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE user_has_notification_types_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: user_has_notification_types_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE user_has_notification_types_id_seq OWNED BY user_has_notification_types.id; + + +-- +-- Name: user_has_notifications; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE user_has_notifications ( + user_id integer NOT NULL, + project_id integer +); + + +-- +-- Name: user_has_unread_notifications; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE user_has_unread_notifications ( + id integer NOT NULL, + user_id integer NOT NULL, + date_creation bigint NOT NULL, + event_name character varying(50) NOT NULL, + event_data text NOT NULL +); + + +-- +-- Name: user_has_unread_notifications_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE user_has_unread_notifications_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: user_has_unread_notifications_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE user_has_unread_notifications_id_seq OWNED BY user_has_unread_notifications.id; + + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE users ( + id integer NOT NULL, + username character varying(50) NOT NULL, + password character varying(255), + is_admin boolean DEFAULT false, + is_ldap_user boolean DEFAULT false, + name character varying(255), + email character varying(255), + google_id character varying(255), + github_id character varying(30), + notifications_enabled boolean DEFAULT false, + timezone character varying(50), + language character(5), + disable_login_form boolean DEFAULT false, + twofactor_activated boolean DEFAULT false, + twofactor_secret character(16), + token character varying(255) DEFAULT ''::character varying, + notifications_filter integer DEFAULT 4, + nb_failed_login integer DEFAULT 0, + lock_expiration_date bigint DEFAULT 0, + is_project_admin boolean DEFAULT false, + gitlab_id integer +); + + +-- +-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE users_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE users_id_seq OWNED BY users.id; + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY action_has_params ALTER COLUMN id SET DEFAULT nextval('action_has_params_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY actions ALTER COLUMN id SET DEFAULT nextval('actions_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY columns ALTER COLUMN id SET DEFAULT nextval('columns_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY comments ALTER COLUMN id SET DEFAULT nextval('comments_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY custom_filters ALTER COLUMN id SET DEFAULT nextval('custom_filters_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY files ALTER COLUMN id SET DEFAULT nextval('task_has_files_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY last_logins ALTER COLUMN id SET DEFAULT nextval('last_logins_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY links ALTER COLUMN id SET DEFAULT nextval('links_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_activities ALTER COLUMN id SET DEFAULT nextval('project_activities_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_daily_column_stats ALTER COLUMN id SET DEFAULT nextval('project_daily_summaries_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_daily_stats ALTER COLUMN id SET DEFAULT nextval('project_daily_stats_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_categories ALTER COLUMN id SET DEFAULT nextval('project_has_categories_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_users ALTER COLUMN id SET DEFAULT nextval('project_has_users_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_integrations ALTER COLUMN id SET DEFAULT nextval('project_integrations_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY projects ALTER COLUMN id SET DEFAULT nextval('projects_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY remember_me ALTER COLUMN id SET DEFAULT nextval('remember_me_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY subtask_time_tracking ALTER COLUMN id SET DEFAULT nextval('subtask_time_tracking_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY subtasks ALTER COLUMN id SET DEFAULT nextval('task_has_subtasks_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY swimlanes ALTER COLUMN id SET DEFAULT nextval('swimlanes_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY task_has_links ALTER COLUMN id SET DEFAULT nextval('task_has_links_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY tasks ALTER COLUMN id SET DEFAULT nextval('tasks_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions ALTER COLUMN id SET DEFAULT nextval('transitions_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_notification_types ALTER COLUMN id SET DEFAULT nextval('user_has_notification_types_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_unread_notifications ALTER COLUMN id SET DEFAULT nextval('user_has_unread_notifications_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY users ALTER COLUMN id SET DEFAULT nextval('users_id_seq'::regclass); + + +-- +-- Name: action_has_params_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY action_has_params + ADD CONSTRAINT action_has_params_pkey PRIMARY KEY (id); + + +-- +-- Name: actions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY actions + ADD CONSTRAINT actions_pkey PRIMARY KEY (id); + + +-- +-- Name: columns_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY columns + ADD CONSTRAINT columns_pkey PRIMARY KEY (id); + + +-- +-- Name: columns_title_project_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY columns + ADD CONSTRAINT columns_title_project_id_key UNIQUE (title, project_id); + + +-- +-- Name: comments_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY comments + ADD CONSTRAINT comments_pkey PRIMARY KEY (id); + + +-- +-- Name: currencies_currency_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY currencies + ADD CONSTRAINT currencies_currency_key UNIQUE (currency); + + +-- +-- Name: custom_filters_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY custom_filters + ADD CONSTRAINT custom_filters_pkey PRIMARY KEY (id); + + +-- +-- Name: last_logins_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY last_logins + ADD CONSTRAINT last_logins_pkey PRIMARY KEY (id); + + +-- +-- Name: links_label_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY links + ADD CONSTRAINT links_label_key UNIQUE (label); + + +-- +-- Name: links_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY links + ADD CONSTRAINT links_pkey PRIMARY KEY (id); + + +-- +-- Name: plugin_schema_versions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY plugin_schema_versions + ADD CONSTRAINT plugin_schema_versions_pkey PRIMARY KEY (plugin); + + +-- +-- Name: project_activities_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_activities + ADD CONSTRAINT project_activities_pkey PRIMARY KEY (id); + + +-- +-- Name: project_daily_stats_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_daily_stats + ADD CONSTRAINT project_daily_stats_pkey PRIMARY KEY (id); + + +-- +-- Name: project_daily_summaries_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_daily_column_stats + ADD CONSTRAINT project_daily_summaries_pkey PRIMARY KEY (id); + + +-- +-- Name: project_has_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_has_categories + ADD CONSTRAINT project_has_categories_pkey PRIMARY KEY (id); + + +-- +-- Name: project_has_categories_project_id_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_has_categories + ADD CONSTRAINT project_has_categories_project_id_name_key UNIQUE (project_id, name); + + +-- +-- Name: project_has_users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_has_users + ADD CONSTRAINT project_has_users_pkey PRIMARY KEY (id); + + +-- +-- Name: project_has_users_project_id_user_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_has_users + ADD CONSTRAINT project_has_users_project_id_user_id_key UNIQUE (project_id, user_id); + + +-- +-- Name: project_integrations_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_integrations + ADD CONSTRAINT project_integrations_pkey PRIMARY KEY (id); + + +-- +-- Name: project_integrations_project_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_integrations + ADD CONSTRAINT project_integrations_project_id_key UNIQUE (project_id); + + +-- +-- Name: projects_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY projects + ADD CONSTRAINT projects_name_key UNIQUE (name); + + +-- +-- Name: projects_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY projects + ADD CONSTRAINT projects_pkey PRIMARY KEY (id); + + +-- +-- Name: remember_me_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY remember_me + ADD CONSTRAINT remember_me_pkey PRIMARY KEY (id); + + +-- +-- Name: settings_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY settings + ADD CONSTRAINT settings_pkey PRIMARY KEY (option); + + +-- +-- Name: subtask_time_tracking_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY subtask_time_tracking + ADD CONSTRAINT subtask_time_tracking_pkey PRIMARY KEY (id); + + +-- +-- Name: swimlanes_name_project_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY swimlanes + ADD CONSTRAINT swimlanes_name_project_id_key UNIQUE (name, project_id); + + +-- +-- Name: swimlanes_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY swimlanes + ADD CONSTRAINT swimlanes_pkey PRIMARY KEY (id); + + +-- +-- Name: task_has_files_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY files + ADD CONSTRAINT task_has_files_pkey PRIMARY KEY (id); + + +-- +-- Name: task_has_links_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY task_has_links + ADD CONSTRAINT task_has_links_pkey PRIMARY KEY (id); + + +-- +-- Name: task_has_subtasks_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY subtasks + ADD CONSTRAINT task_has_subtasks_pkey PRIMARY KEY (id); + + +-- +-- Name: tasks_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY tasks + ADD CONSTRAINT tasks_pkey PRIMARY KEY (id); + + +-- +-- Name: transitions_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_pkey PRIMARY KEY (id); + + +-- +-- Name: user_has_notification_types_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY user_has_notification_types + ADD CONSTRAINT user_has_notification_types_pkey PRIMARY KEY (id); + + +-- +-- Name: user_has_notifications_project_id_user_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY user_has_notifications + ADD CONSTRAINT user_has_notifications_project_id_user_id_key UNIQUE (project_id, user_id); + + +-- +-- Name: user_has_unread_notifications_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY user_has_unread_notifications + ADD CONSTRAINT user_has_unread_notifications_pkey PRIMARY KEY (id); + + +-- +-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT users_pkey PRIMARY KEY (id); + + +-- +-- Name: categories_project_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX categories_project_idx ON project_has_categories USING btree (project_id); + + +-- +-- Name: columns_project_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX columns_project_idx ON columns USING btree (project_id); + + +-- +-- Name: comments_reference_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX comments_reference_idx ON comments USING btree (reference); + + +-- +-- Name: comments_task_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX comments_task_idx ON comments USING btree (task_id); + + +-- +-- Name: files_task_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX files_task_idx ON files USING btree (task_id); + + +-- +-- Name: project_daily_column_stats_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX project_daily_column_stats_idx ON project_daily_column_stats USING btree (day, project_id, column_id); + + +-- +-- Name: project_daily_stats_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX project_daily_stats_idx ON project_daily_stats USING btree (day, project_id); + + +-- +-- Name: subtasks_task_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX subtasks_task_idx ON subtasks USING btree (task_id); + + +-- +-- Name: swimlanes_project_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX swimlanes_project_idx ON swimlanes USING btree (project_id); + + +-- +-- Name: task_has_links_task_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX task_has_links_task_index ON task_has_links USING btree (task_id); + + +-- +-- Name: task_has_links_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX task_has_links_unique ON task_has_links USING btree (link_id, task_id, opposite_task_id); + + +-- +-- Name: tasks_project_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX tasks_project_idx ON tasks USING btree (project_id); + + +-- +-- Name: tasks_reference_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX tasks_reference_idx ON tasks USING btree (reference); + + +-- +-- Name: transitions_project_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX transitions_project_index ON transitions USING btree (project_id); + + +-- +-- Name: transitions_task_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX transitions_task_index ON transitions USING btree (task_id); + + +-- +-- Name: transitions_user_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX transitions_user_index ON transitions USING btree (user_id); + + +-- +-- Name: user_has_notification_types_user_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX user_has_notification_types_user_idx ON user_has_notification_types USING btree (user_id, notification_type); + + +-- +-- Name: users_admin_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX users_admin_idx ON users USING btree (is_admin); + + +-- +-- Name: users_username_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE UNIQUE INDEX users_username_idx ON users USING btree (username); + + +-- +-- Name: action_has_params_action_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY action_has_params + ADD CONSTRAINT action_has_params_action_id_fkey FOREIGN KEY (action_id) REFERENCES actions(id) ON DELETE CASCADE; + + +-- +-- Name: actions_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY actions + ADD CONSTRAINT actions_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: columns_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY columns + ADD CONSTRAINT columns_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: comments_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY comments + ADD CONSTRAINT comments_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: last_logins_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY last_logins + ADD CONSTRAINT last_logins_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: project_activities_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_activities + ADD CONSTRAINT project_activities_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: project_activities_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_activities + ADD CONSTRAINT project_activities_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: project_activities_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_activities + ADD CONSTRAINT project_activities_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: project_daily_stats_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_daily_stats + ADD CONSTRAINT project_daily_stats_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: project_daily_summaries_column_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_daily_column_stats + ADD CONSTRAINT project_daily_summaries_column_id_fkey FOREIGN KEY (column_id) REFERENCES columns(id) ON DELETE CASCADE; + + +-- +-- Name: project_daily_summaries_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_daily_column_stats + ADD CONSTRAINT project_daily_summaries_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: project_has_categories_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_categories + ADD CONSTRAINT project_has_categories_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: project_has_users_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_users + ADD CONSTRAINT project_has_users_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: project_has_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_users + ADD CONSTRAINT project_has_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: project_integrations_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_integrations + ADD CONSTRAINT project_integrations_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: remember_me_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY remember_me + ADD CONSTRAINT remember_me_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: subtask_time_tracking_subtask_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY subtask_time_tracking + ADD CONSTRAINT subtask_time_tracking_subtask_id_fkey FOREIGN KEY (subtask_id) REFERENCES subtasks(id) ON DELETE CASCADE; + + +-- +-- Name: subtask_time_tracking_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY subtask_time_tracking + ADD CONSTRAINT subtask_time_tracking_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: swimlanes_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY swimlanes + ADD CONSTRAINT swimlanes_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: task_has_files_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY files + ADD CONSTRAINT task_has_files_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: task_has_links_link_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY task_has_links + ADD CONSTRAINT task_has_links_link_id_fkey FOREIGN KEY (link_id) REFERENCES links(id) ON DELETE CASCADE; + + +-- +-- Name: task_has_links_opposite_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY task_has_links + ADD CONSTRAINT task_has_links_opposite_task_id_fkey FOREIGN KEY (opposite_task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: task_has_links_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY task_has_links + ADD CONSTRAINT task_has_links_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: task_has_subtasks_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY subtasks + ADD CONSTRAINT task_has_subtasks_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: tasks_column_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY tasks + ADD CONSTRAINT tasks_column_id_fkey FOREIGN KEY (column_id) REFERENCES columns(id) ON DELETE CASCADE; + + +-- +-- Name: tasks_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY tasks + ADD CONSTRAINT tasks_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: transitions_dst_column_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_dst_column_id_fkey FOREIGN KEY (dst_column_id) REFERENCES columns(id) ON DELETE CASCADE; + + +-- +-- Name: transitions_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: transitions_src_column_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_src_column_id_fkey FOREIGN KEY (src_column_id) REFERENCES columns(id) ON DELETE CASCADE; + + +-- +-- Name: transitions_task_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_task_id_fkey FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE; + + +-- +-- Name: transitions_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY transitions + ADD CONSTRAINT transitions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: user_has_notification_types_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_notification_types + ADD CONSTRAINT user_has_notification_types_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: user_has_notifications_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_notifications + ADD CONSTRAINT user_has_notifications_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- +-- Name: user_has_notifications_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_notifications + ADD CONSTRAINT user_has_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: user_has_unread_notifications_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY user_has_unread_notifications + ADD CONSTRAINT user_has_unread_notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: - +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM f; +GRANT ALL ON SCHEMA public TO f; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- PostgreSQL database dump complete +-- + +INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$BBxt.HuQCFur7qhICY22i.A6OKyce0KGqmMEWq3JdGT4WcEKCBWP.', '1');INSERT INTO schema_version VALUES ('70'); diff --git a/doc/mysql-configuration.markdown b/doc/mysql-configuration.markdown index eef1e125..554bec3b 100644 --- a/doc/mysql-configuration.markdown +++ b/doc/mysql-configuration.markdown @@ -42,3 +42,16 @@ define('DB_NAME', 'kanboard'); ``` Note: You can also rename the template file `config.default.php` to `config.php`. + +### Importing SQL dump (alternative method) + +The first time, Kanboard will run one by one each database migration and this process can take some time according to your configuration. + +To avoid any issues or potential timeouts you can initialize the database directly by importing the SQL schema: + +```bash +mysql -u root -p my_database < app/Schema/Sql/mysql.sql +``` + +The file `app/Schema/Sql/mysql.sql` is a sql dump that represent the last version of the database. + diff --git a/doc/postgresql-configuration.markdown b/doc/postgresql-configuration.markdown index 3c07ff16..904dfc0f 100644 --- a/doc/postgresql-configuration.markdown +++ b/doc/postgresql-configuration.markdown @@ -38,3 +38,15 @@ define('DB_NAME', 'kanboard'); ``` Note: You can also rename the template file `config.default.php` to `config.php`. + +### Importing SQL dump (alternative method) + +The first time, Kanboard will run one by one each database migration and this process can take some time according to your configuration. + +To avoid any issues or potential timeouts you can initialize the database directly by importing the SQL schema: + +```bash +psql -U postgres my_database < app/Schema/Sql/postgres.sql +``` + +The file `app/Schema/Sql/postgres.sql` is a sql dump that represent the last version of the database. |