This project demonstrates using GNU make to automatically order table creation in MySQL according to foreign key constraints. For a complex or frequently-changing schema, this can save a lot of manual tedium. As of today, the project I am currently working on has a schema of 145 tables, 74 of which have foreign key references. We also expect correct, automated database builds as often as daily. As it is under heavy development, the schema changes frequently. The method described here saves substantial manual effort to arrive at a correct ordering. How to use ---------- See the sample Makefile. Each table must be defined in an individual file in the sql/ directory (often a good practice for other reasons). A script (split.pl) is provided to break up a long SQL file into table files. To create the schema and its tables: $ make create all-tables How it works ------------ The fk.pl script extracts dependencies by simplistically parsing for foreign key REFERENCES. These are added as rules to a temporary makefile. The 'all-tables' rule has every table as a pre-requisite. A 'table pre-requisite' is represented by an imaginary table_name.t target. In order to build all-tables, make generates rules in an order which satisfies the defined dependencies, as it is designed to do. (It is not necessary to physically create the targets as files; instead, the 'side-effect' of executing the rule's command is to create the corresponding table.) Example ------- Applied to a small schema found in the wild. Notice the 3 FK references are respected by the order of table creation. $ curl -s -k https://svn.tigase.org/reps/tigase-server/trunk/database/mysql-schema.sql | ./split.pl short_news xmpp_stanza tig_users tig_nodes tig_pairs $ make create all-tables perl fk.pl sql/short_news.sql sql/tig_nodes.sql sql/tig_pairs.sql sql/tig_users.sql sql/xmpp_stanza.sql > tables.mk mysqladmin -u root create fk_test mysql -B -u root fk_test < sql/short_news.sql mysql -B -u root fk_test < sql/tig_users.sql mysql -B -u root fk_test < sql/tig_nodes.sql mysql -B -u root fk_test < sql/tig_pairs.sql mysql -B -u root fk_test < sql/xmpp_stanza.sql $ grep : tables.mk short_news.t : short_news.sql tig_nodes.t : tig_nodes.sql tig_users.t tig_pairs.t : tig_pairs.sql tig_users.t tig_nodes.t tig_users.t : tig_users.sql xmpp_stanza.t : xmpp_stanza.sql .PHONY : all-tables all-tables : short_news.t tig_nodes.t tig_pairs.t tig_users.t xmpp_stanza.t Notes ----- This will not work if there are circular references, involving more than one table, in your schema. For example, MySQL Sakila sample has such a reference: # split into one file for each table actor address category city country customer film film_actor film_category film_text inventory language payment rental staff store $ make all-tables perl fk.pl sql/_empty.sql sql/actor.sql sql/address.sql sql/category.sql sql/city.sql sql/country.sql sql/customer.sql sql/film.sql sql/film_actor.sql sql/film_category.sql sql/film_text.sql sql/inventory.sql sql/language.sql sql/payment.sql sql/rental.sql sql/staff.sql sql/store.sql > tables.mk mysql -B -u root fk_test < sql/actor.sql mysql -B -u root fk_test < sql/country.sql mysql -B -u root fk_test < sql/city.sql mysql -B -u root fk_test < sql/address.sql mysql -B -u root fk_test < sql/category.sql make: Circular staff.t <- store.t dependency dropped. mysql -B -u root fk_test < sql/staff.sql ERROR 1005 (HY000) at line 1: Can't create table './fk_test/staff.frm' (errno: 150) make: *** [staff.t] Error 1 Bigger Example -------------- This is a larger schema found on the net. http://openqrm.cvs.sourceforge.net/viewvc/openqrm/base/openqrm-3.5.2/src/base/include/db/mysql/create.sql?revision=1.1&view=markup $ ./split.pl openqrm.sql qrm_schema qrm_roles qrm_users qrm_sessions qrm_users_roles qrm_sessions_roles qrm_events qrm_events_entities qrm_events_entities_relations qrm_event_listeners qrm_properties qrm_operation_systems qrm_cpu_architectures qrm_operation_system_architectures qrm_cpu_models qrm_tags qrm_storage_types qrm_storage_servers qrm_kernel_images qrm_images qrm_deployment_configs qrm_pool_config qrm_pool_states qrm_virtual_environment_statistics qrm_virtual_environments qrm_deployment_instances qrm_resource_statistics qrm_ve_filesystem_images qrm_resources qrm_resource_tags qrm_resource_controls qrm_filters qrm_persisted_objects qrm_physical_interfaces qrm_resource_interfaces qrm_favorites qrm_metrics_definitions qrm_ve_metrics_values qrm_resource_metrics_values qrm_ve_users qrm_ve_sessions qrm_action_roles qrm_ve_preferred_resources qrm_audit qrm_partitions_data $ make all-tables perl fk.pl sql/_empty.sql sql/qrm_action_roles.sql sql/qrm_audit.sql sql/qrm_cpu_architectures.sql sql/qrm_cpu_models.sql sql/qrm_deployment_configs.sql sql/qrm_deployment_instances.sql sql/qrm_event_listeners.sql sql/qrm_events.sql sql/qrm_events_entities.sql sql/qrm_events_entities_relations.sql sql/qrm_favorites.sql sql/qrm_filters.sql sql/qrm_images.sql sql/qrm_kernel_images.sql sql/qrm_metrics_definitions.sql sql/qrm_operation_system_architectures.sql sql/qrm_operation_systems.sql sql/qrm_partitions_data.sql sql/qrm_persisted_objects.sql sql/qrm_physical_interfaces.sql sql/qrm_pool_config.sql sql/qrm_pool_states.sql sql/qrm_properties.sql sql/qrm_resource_controls.sql sql/qrm_resource_interfaces.sql sql/qrm_resource_metrics_values.sql sql/qrm_resource_statistics.sql sql/qrm_resource_tags.sql sql/qrm_resources.sql sql/qrm_roles.sql sql/qrm_schema.sql sql/qrm_sessions.sql sql/qrm_sessions_roles.sql sql/qrm_storage_servers.sql sql/qrm_storage_types.sql sql/qrm_tags.sql sql/qrm_users.sql sql/qrm_users_roles.sql sql/qrm_ve_filesystem_images.sql sql/qrm_ve_metrics_values.sql sql/qrm_ve_preferred_resources.sql sql/qrm_ve_sessions.sql sql/qrm_ve_users.sql sql/qrm_virtual_environment_statistics.sql sql/qrm_virtual_environments.sql > tables.mk mysql -B -u root fk_test < sql/qrm_action_roles.sql mysql -B -u root fk_test < sql/qrm_audit.sql mysql -B -u root fk_test < sql/qrm_cpu_architectures.sql mysql -B -u root fk_test < sql/qrm_cpu_models.sql mysql -B -u root fk_test < sql/qrm_storage_types.sql mysql -B -u root fk_test < sql/qrm_storage_servers.sql make: Circular qrm_images.t <- qrm_images.t dependency dropped. mysql -B -u root fk_test < sql/qrm_operation_systems.sql mysql -B -u root fk_test < sql/qrm_images.sql mysql -B -u root fk_test < sql/qrm_deployment_configs.sql mysql -B -u root fk_test < sql/qrm_pool_config.sql mysql -B -u root fk_test < sql/qrm_pool_states.sql mysql -B -u root fk_test < sql/qrm_virtual_environment_statistics.sql mysql -B -u root fk_test < sql/qrm_virtual_environments.sql mysql -B -u root fk_test < sql/qrm_deployment_instances.sql mysql -B -u root fk_test < sql/qrm_event_listeners.sql make: Circular qrm_events.t <- qrm_events.t dependency dropped. make: Circular qrm_events.t <- qrm_events.t dependency dropped. mysql -B -u root fk_test < sql/qrm_events.sql make: Circular qrm_events_entities.t <- qrm_events_entities.t dependency dropped. mysql -B -u root fk_test < sql/qrm_events_entities.sql mysql -B -u root fk_test < sql/qrm_events_entities_relations.sql mysql -B -u root fk_test < sql/qrm_users.sql mysql -B -u root fk_test < sql/qrm_favorites.sql mysql -B -u root fk_test < sql/qrm_filters.sql mysql -B -u root fk_test < sql/qrm_kernel_images.sql mysql -B -u root fk_test < sql/qrm_metrics_definitions.sql mysql -B -u root fk_test < sql/qrm_operation_system_architectures.sql make: Circular qrm_resources.t <- qrm_resources.t dependency dropped. make: Circular qrm_resources.t <- qrm_resources.t dependency dropped. mysql -B -u root fk_test < sql/qrm_resources.sql mysql -B -u root fk_test < sql/qrm_partitions_data.sql mysql -B -u root fk_test < sql/qrm_persisted_objects.sql mysql -B -u root fk_test < sql/qrm_physical_interfaces.sql mysql -B -u root fk_test < sql/qrm_properties.sql mysql -B -u root fk_test < sql/qrm_resource_controls.sql mysql -B -u root fk_test < sql/qrm_resource_interfaces.sql mysql -B -u root fk_test < sql/qrm_resource_metrics_values.sql mysql -B -u root fk_test < sql/qrm_resource_statistics.sql mysql -B -u root fk_test < sql/qrm_tags.sql mysql -B -u root fk_test < sql/qrm_resource_tags.sql mysql -B -u root fk_test < sql/qrm_roles.sql mysql -B -u root fk_test < sql/qrm_schema.sql mysql -B -u root fk_test < sql/qrm_sessions.sql mysql -B -u root fk_test < sql/qrm_sessions_roles.sql mysql -B -u root fk_test < sql/qrm_users_roles.sql mysql -B -u root fk_test < sql/qrm_ve_filesystem_images.sql mysql -B -u root fk_test < sql/qrm_ve_metrics_values.sql mysql -B -u root fk_test < sql/qrm_ve_preferred_resources.sql mysql -B -u root fk_test < sql/qrm_ve_sessions.sql mysql -B -u root fk_test < sql/qrm_ve_users.sql $ This file is part of fk, a method for using UNIX 'make' to help create schema which have foreign key constraints. Copyright (C) 2009 Toby Thain, toby@telegraphics.com.au This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 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 General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA