EHN: Add new base schema (db_5_0.sql) for installation

Summary

#32 (closed)

The Schema had once (until 2016 A.D.) a table named "groups" which is a keyword since MySQL 8. It is not part of the currenty schema (v5.0.0) anymore, but it has been created (as part of schema (v.2.0.1) and then been removed again in a patch from 2016-04. This is why the installation fails on MySQL 8 (and will likely fail on future MariaDB versions).

This MR adds a new schema installer (on the basis of our schema v5.0.0, without the groups table) and removes tables and functions which turned out to be unused during the review of the new db_5_0.sql file.

Focus

Obviously, there is not much really changing here. The new db_5_0.sql which replaces db_2_0.sql is just a dump of fresh installation with the old installer. A few tweaks had to be made (order of table creation and some tweaks to set some INT UNSINGED AUTOINCREMENT values to zero).

Just note that

• the function retrieveGroup was referencing the (deleted) groups table.
• the table logging is an unused remnant of a never-finished logging persistence functionality (We use a proper logging framework now, which has its own persistence functionality).

Test Environment

No manual testing required. Just make sure that the complete pipeline succeeds (mysqlbackend, deploy, pyinttests)

Check List for the Author

Please, prepare your MR for a review. Be sure to write a summary and a focus and create gitlab comments for the reviewer. They should guide the reviewer through the changes, explain your changes and also point out open questions. For further good practices have a look at our review guidelines

• All automated tests pass
• Reference related Issues
• Up-to-date CHANGELOG.md
• Annotations in code (Gitlab comments)
  • Intent of new code
  • Problems with old code
  • Why this implementation?

Check List for the Reviewer

• I understand the intent of this MR
• All automated tests pass
• Up-to-date CHANGELOG.md
• The test environment setup works and the intended behavior is reproducible in the test environment
• In-code documentation and comments are up-to-date.
• Check: Are there specifications? Are they satisfied?

For further good practices have a look at our review guidelines.

procedures/retrieveGroup.sql

-/*
- * ** header v3.0
- * This file is a part of the CaosDB Project.
- *
- * Copyright (C) 2018 Research Group Biomedical Physics,
- * Max-Planck-Institute for Dynamics and Self-Organization Göttingen
- *
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU Affero General Public License as
- * published by the Free Software Foundation, either version 3 of the
- * License, or (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
- * GNU Affero General Public License for more details.
- *
- * You should have received a copy of the GNU Affero General Public License
- * along with this program. If not, see <https://www.gnu.org/licenses/>.
- *
- * ** end header
- */
-
-Drop Procedure if exists db_5_0.retrieveGroup;
-Delimiter //
-Create Procedure db_5_0.retrieveGroup(in USERID INT UNSIGNED, in GROUPID INT UNSIGNED)
-
-
-BEGIN 
-IF USERID IS NOT NULL THEN 
-  Select user_id, group_id from groups where user_id = USERID;
-ELSEIF GROUPID IS NOT NULL THEN
-  Select user_id, group_id from groups where group_id = GROUPID;
-END IF;
-
-END;
-//
-delimiter ;