diff options
Diffstat (limited to 'app/Schema/Sql')
| -rw-r--r-- | app/Schema/Sql/mysql.sql | 50 | ||||
| -rw-r--r-- | app/Schema/Sql/postgres.sql | 177 |
2 files changed, 169 insertions, 58 deletions
diff --git a/app/Schema/Sql/mysql.sql b/app/Schema/Sql/mysql.sql index eb59cca1..2c5ec989 100644 --- a/app/Schema/Sql/mysql.sql +++ b/app/Schema/Sql/mysql.sql @@ -112,6 +112,29 @@ CREATE TABLE `files` ( 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 `group_has_users`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `group_has_users` ( + `group_id` int(11) NOT NULL, + `user_id` int(11) NOT NULL, + UNIQUE KEY `group_id` (`group_id`,`user_id`), + KEY `user_id` (`user_id`), + CONSTRAINT `group_has_users_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE, + CONSTRAINT `group_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 `groups`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `groups` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `external_id` varchar(255) DEFAULT '', + `name` varchar(100) NOT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `name` (`name`) +) 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 */; @@ -214,6 +237,19 @@ CREATE TABLE `project_has_categories` ( 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_groups`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `project_has_groups` ( + `group_id` int(11) NOT NULL, + `project_id` int(11) NOT NULL, + `role` varchar(25) NOT NULL, + UNIQUE KEY `group_id` (`group_id`,`project_id`), + KEY `project_id` (`project_id`), + CONSTRAINT `project_has_groups_ibfk_1` FOREIGN KEY (`group_id`) REFERENCES `groups` (`id`) ON DELETE CASCADE, + CONSTRAINT `project_has_groups_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_has_metadata`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; @@ -241,11 +277,9 @@ 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`), + `role` varchar(25) NOT NULL DEFAULT 'project-viewer', 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, @@ -507,7 +541,6 @@ 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, @@ -523,11 +556,10 @@ CREATE TABLE `users` ( `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, + `role` varchar(25) NOT NULL DEFAULT 'app-user', PRIMARY KEY (`id`), - UNIQUE KEY `users_username_idx` (`username`), - KEY `users_admin_idx` (`is_admin`) + UNIQUE KEY `users_username_idx` (`username`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -549,7 +581,7 @@ CREATE TABLE `users` ( LOCK TABLES `settings` WRITE; /*!40000 ALTER TABLE `settings` DISABLE KEYS */; -INSERT INTO `settings` VALUES ('api_token','3783f814662e83f1ebe9ada40314f93a6e75688fe7e04a3820e4078966f0'),('application_currency','USD'),('application_date_format','m/d/Y'),('application_language','en_US'),('application_stylesheet',''),('application_timezone','UTC'),('application_url',''),('board_columns',''),('board_highlight_period','172800'),('board_private_refresh_interval','10'),('board_public_refresh_interval','60'),('calendar_project_tasks','date_started'),('calendar_user_subtasks_time_tracking','0'),('calendar_user_tasks','date_started'),('cfd_include_closed_tasks','1'),('default_color','yellow'),('integration_gravatar','0'),('project_categories',''),('subtask_restriction','0'),('subtask_time_tracking','1'),('webhook_token','c95cf0a67507ca68cc93f717bb78ac5dfaf0c73d38ab159fc73038aa19d9'),('webhook_url',''); +INSERT INTO `settings` VALUES ('api_token','af33e30de765b4eb6ac9eea6243e8edac9acfe7afb9df3c3d7e6eff696f9'),('application_currency','USD'),('application_date_format','m/d/Y'),('application_language','en_US'),('application_stylesheet',''),('application_timezone','UTC'),('application_url',''),('board_columns',''),('board_highlight_period','172800'),('board_private_refresh_interval','10'),('board_public_refresh_interval','60'),('calendar_project_tasks','date_started'),('calendar_user_subtasks_time_tracking','0'),('calendar_user_tasks','date_started'),('cfd_include_closed_tasks','1'),('default_color','yellow'),('integration_gravatar','0'),('project_categories',''),('subtask_restriction','0'),('subtask_time_tracking','1'),('webhook_token','280c9d5ea16323d0edb1bbf9409ffbc7c23bfd65ae0a1a00fe7236d11536'),('webhook_url',''); /*!40000 ALTER TABLE `settings` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; @@ -578,4 +610,4 @@ UNLOCK TABLES; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$4/2e1E1VIeZVc5PhRHQJmuOBI/UV7H73hRyH60IvpTpY05G9tD49W', '1');INSERT INTO schema_version VALUES ('94'); +INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$fe3.kkCOOiOxf21QW0tEUOCo5hsL19ct4YUumL.xxTnKKMbJCUb/y', '1');INSERT INTO schema_version VALUES ('97'); diff --git a/app/Schema/Sql/postgres.sql b/app/Schema/Sql/postgres.sql index f13863c8..63148955 100644 --- a/app/Schema/Sql/postgres.sql +++ b/app/Schema/Sql/postgres.sql @@ -218,6 +218,46 @@ CREATE TABLE files ( -- +-- Name: group_has_users; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE group_has_users ( + group_id integer NOT NULL, + user_id integer NOT NULL +); + + +-- +-- Name: groups; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE groups ( + id integer NOT NULL, + external_id character varying(255) DEFAULT ''::character varying, + name character varying(100) NOT NULL +); + + +-- +-- Name: groups_id_seq; Type: SEQUENCE; Schema: public; Owner: - +-- + +CREATE SEQUENCE groups_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +-- +-- Name: groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - +-- + +ALTER SEQUENCE groups_id_seq OWNED BY groups.id; + + +-- -- Name: last_logins; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -421,6 +461,17 @@ ALTER SEQUENCE project_has_categories_id_seq OWNED BY project_has_categories.id; -- +-- Name: project_has_groups; Type: TABLE; Schema: public; Owner: -; Tablespace: +-- + +CREATE TABLE project_has_groups ( + group_id integer NOT NULL, + project_id integer NOT NULL, + role character varying(25) NOT NULL +); + + +-- -- Name: project_has_metadata; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -466,33 +517,13 @@ ALTER SEQUENCE project_has_notification_types_id_seq OWNED BY project_has_notifi -- CREATE TABLE project_has_users ( - id integer NOT NULL, project_id integer NOT NULL, user_id integer NOT NULL, - is_owner boolean DEFAULT false + role character varying(25) DEFAULT 'project-viewer'::character varying NOT NULL ); -- --- 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: projects; Type: TABLE; Schema: public; Owner: -; Tablespace: -- @@ -931,7 +962,6 @@ 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), @@ -947,8 +977,8 @@ CREATE TABLE users ( 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 + gitlab_id integer, + role character varying(25) DEFAULT 'app-user'::character varying NOT NULL ); @@ -1017,6 +1047,13 @@ ALTER TABLE ONLY files ALTER COLUMN id SET DEFAULT nextval('task_has_files_id_se -- Name: id; Type: DEFAULT; Schema: public; Owner: - -- +ALTER TABLE ONLY groups ALTER COLUMN id SET DEFAULT nextval('groups_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); @@ -1066,13 +1103,6 @@ ALTER TABLE ONLY project_has_notification_types ALTER COLUMN id SET DEFAULT next -- 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 projects ALTER COLUMN id SET DEFAULT nextval('projects_id_seq'::regclass); @@ -1203,6 +1233,30 @@ ALTER TABLE ONLY custom_filters -- +-- Name: group_has_users_group_id_user_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY group_has_users + ADD CONSTRAINT group_has_users_group_id_user_id_key UNIQUE (group_id, user_id); + + +-- +-- Name: groups_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_name_key UNIQUE (name); + + +-- +-- Name: groups_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY groups + ADD CONSTRAINT groups_pkey PRIMARY KEY (id); + + +-- -- Name: last_logins_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- @@ -1275,6 +1329,14 @@ ALTER TABLE ONLY project_has_categories -- +-- Name: project_has_groups_group_id_project_id_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: +-- + +ALTER TABLE ONLY project_has_groups + ADD CONSTRAINT project_has_groups_group_id_project_id_key UNIQUE (group_id, project_id); + + +-- -- Name: project_has_metadata_project_id_name_key; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: -- @@ -1299,14 +1361,6 @@ ALTER TABLE ONLY project_has_notification_types -- --- 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: -- @@ -1570,13 +1624,6 @@ CREATE UNIQUE INDEX user_has_notification_types_user_idx ON user_has_notificatio -- --- 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: -- @@ -1616,6 +1663,22 @@ ALTER TABLE ONLY comments -- +-- Name: group_has_users_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY group_has_users + ADD CONSTRAINT group_has_users_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE; + + +-- +-- Name: group_has_users_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY group_has_users + ADD CONSTRAINT group_has_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; + + +-- -- Name: last_logins_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- @@ -1680,6 +1743,22 @@ ALTER TABLE ONLY project_has_categories -- +-- Name: project_has_groups_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_groups + ADD CONSTRAINT project_has_groups_group_id_fkey FOREIGN KEY (group_id) REFERENCES groups(id) ON DELETE CASCADE; + + +-- +-- Name: project_has_groups_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - +-- + +ALTER TABLE ONLY project_has_groups + ADD CONSTRAINT project_has_groups_project_id_fkey FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE; + + +-- -- Name: project_has_metadata_project_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - -- @@ -1922,8 +2001,7 @@ INSERT INTO settings (option, value) VALUES ('board_highlight_period', '172800') INSERT INTO settings (option, value) VALUES ('board_public_refresh_interval', '60'); INSERT INTO settings (option, value) VALUES ('board_private_refresh_interval', '10'); INSERT INTO settings (option, value) VALUES ('board_columns', ''); -INSERT INTO settings (option, value) VALUES ('webhook_token', 'ca57fbbc9e17d00a1ca8c2e45d8dc5d1b54ede740f72709bae2e8de26fbd'); -INSERT INTO settings (option, value) VALUES ('api_token', 'bc20677f12faa32d9426af9a31041b8576c13f4ab54b641bae955112ae79'); +INSERT INTO settings (option, value) VALUES ('webhook_token', '2e3e9c804be85c221394727a142e6e45f1f3bfaa3ab85576d5d555ea661a'); INSERT INTO settings (option, value) VALUES ('application_language', 'en_US'); INSERT INTO settings (option, value) VALUES ('application_timezone', 'UTC'); INSERT INTO settings (option, value) VALUES ('application_url', ''); @@ -1940,6 +2018,7 @@ INSERT INTO settings (option, value) VALUES ('webhook_url', ''); INSERT INTO settings (option, value) VALUES ('default_color', 'yellow'); INSERT INTO settings (option, value) VALUES ('subtask_time_tracking', '1'); INSERT INTO settings (option, value) VALUES ('cfd_include_closed_tasks', '1'); +INSERT INTO settings (option, value) VALUES ('api_token', '19ffd9709d03ce50675c3a43d1c49c1ac207f4bc45f06c5b2701fbdf8929'); -- @@ -1987,4 +2066,4 @@ SELECT pg_catalog.setval('links_id_seq', 11, true); -- PostgreSQL database dump complete -- -INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$4/2e1E1VIeZVc5PhRHQJmuOBI/UV7H73hRyH60IvpTpY05G9tD49W', '1');INSERT INTO schema_version VALUES ('74'); +INSERT INTO users (username, password, is_admin) VALUES ('admin', '$2y$10$fe3.kkCOOiOxf21QW0tEUOCo5hsL19ct4YUumL.xxTnKKMbJCUb/y', '1');INSERT INTO schema_version VALUES ('77'); |