We have a large database with client data, with many entities, but we dont allow connections to the production db (mysql) due to security. Only option remaining is to move some data to development db (mysql) so we can do local testing before release. Now, the thing is, we have many tables with many relationships between them ie., schools, courses, tests, students etc,. To effectively test data, we'll need to take a full school level dump with all related entities and their relationships being maintained and be mapped to respective tables (ie., all tables must have values). The data dump can then be loaded in development db and used for testing.
I know dumps can be made with a -- "where school_id = 'abc' " condition but can we automate this process to go to each table and apply the same query or a derived query specific to that table's primary key?
For example: Say there are 5 tables:
schools- school_id, school_name, etc, departments- department_id, department_name, etc, school_departments- school_id, department_id, etc, department_courses- department_id, course_id, etc, course_tests- course_id, test_id, test_details,
I want the tool to take only the input of top level school_id filter and apply it systematically to all tables to extract data from that school alone:
schools- apply given filter -- "where school_id = 'sch001' " departments- apply derived filter -- "where department_id in (select department_id from school_departments where school_id = 'sch001') " school_departments- "where school_id = 'sch001' " department_courses- -- "where department_id in (select department_id from school_departments where school_id = 'sch001') " course_tests-- "where course_id in (select course_id from department_courses where department_id in (select department_id from school_departments where school_id = 'sch001')) "