Export Database

This document explains how to export the SAWP database from phpMyAdmin so that it can be published and checked in.

when shipping the project, make sure that the content of /web/includes/config/installed.php is <?php return false;
also /web/index-test.php has to be moved to /tests/
and /web/includes/controllers/TestingController.php has to be moved to /tests/

Export from phpMyAdmin (Version 5.6.16)

  1. Cleaning the database

    The following steps can be performed automated with the given sql at the end of this document
    1. Clear all entrys in the tables:
      • auth_assignment
      • team
      • team_user
      • screenplay
      • screenplay_comment
      • screenplay_revision
      • screenplay_text_revision
      • screenplay_tree_revision
      • user
      • user_mail_token
    2. Add a tuple ('admin', 1, NULL) to auth_assignment. This will become the first administrator account later. To be able to insert this tuple you must disable foreign key checking as the user will be created later.
    3. Reset the autoincrement values to 1 for tables (see Operations) team screenplay screenplay_comment screenplay_revision screenplay_text_revision screenplay_tree_revision user

  2. Exporting the database

    1. Select the database and select the Export tab
    2. Select the option "Custom - display all possible options" and make the following configurations (leave the defaults for all other options)

      Format-specific options:
      ENABLE "Disable foreign key checks"

      Object creation options:
      ENABLE "Add DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT statement"

    3. Click "GO" and store the export as "sawp.sql" in web/includes/sawp.sql

SQL CODE for step 1


SET foreign_key_checks = 0;

/* STEP 1.a) */
TRUNCATE `auth_assignment`;
TRUNCATE `team`;
TRUNCATE `team_user`;
TRUNCATE `screenplay`;
TRUNCATE `screenplay_comment`;
TRUNCATE `screenplay_revision`;
TRUNCATE `screenplay_text_revision`;
TRUNCATE `screenplay_tree_revision`;
TRUNCATE `user`;
TRUNCATE `user_mail_token`;

/* STEP 1.b) */
INSERT INTO `sawp`.`auth_assignment` (`item_name`, `user_id`, `created_at`) VALUES ('admin', '1', NULL);

/* STEP 1.c) */
ALTER TABLE team AUTO_INCREMENT = 1;
ALTER TABLE screenplay AUTO_INCREMENT = 1;
ALTER TABLE screenplay_comment AUTO_INCREMENT = 1;
ALTER TABLE screenplay_revision AUTO_INCREMENT = 1;
ALTER TABLE screenplay_text_revision AUTO_INCREMENT = 1;
ALTER TABLE screenplay_tree_revision AUTO_INCREMENT = 1;
ALTER TABLE user AUTO_INCREMENT = 1;

SET foreign_key_checks = 1;