From 79968b447098a4892108da97f92f470820aa3c0f Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Thu, 7 Feb 2013 17:52:49 +0200 Subject: Use multi-pass table creation in MySQL This deals with table creation order and circular dependencies. Unfortunately, there doesn't seem to be a way in MySQL to drop a foreign key only if it exists without resorting to stored procedures. --- odb/relational/mysql/schema.cxx | 229 +++++++++++++++++++++++++++++++++++----- 1 file changed, 204 insertions(+), 25 deletions(-) diff --git a/odb/relational/mysql/schema.cxx b/odb/relational/mysql/schema.cxx index 5b34cfa..d74ee9c 100644 --- a/odb/relational/mysql/schema.cxx +++ b/odb/relational/mysql/schema.cxx @@ -2,11 +2,15 @@ // copyright : Copyright (c) 2009-2012 Code Synthesis Tools CC // license : GNU GPL v3; see accompanying LICENSE file +#include + #include #include #include +using namespace std; + namespace relational { namespace mysql @@ -16,6 +20,94 @@ namespace relational namespace relational = relational::schema; // + // Drop. + // + /* + struct drop_table: relational::drop_table, context + { + drop_table (base const& x): base (x) {} + + virtual void + traverse (sema_rel::table&); + + private: + friend class drop_foreign_key; + set tables_; // Set of tables we would have already dropped. + }; + entry drop_table_; + + struct drop_foreign_key: trav_rel::foreign_key, relational::common + { + drop_foreign_key (drop_table& dt) + : common (dt.emitter (), dt.stream ()), dt_ (dt) + { + } + + virtual void + traverse (sema_rel::foreign_key& fk) + { + // Deferred constraints are not supported by MySQL. + // + if (fk.deferred ()) + return; + + // If the table which we reference is droped before us, then + // we need to drop the constraint first. Similarly, if the + // referenced table is not part if this model, then assume + // it is dropped before us. + // + sema_rel::qname const& rt (fk.referenced_table ()); + sema_rel::table& t (dynamic_cast (fk.scope ())); + sema_rel::model& m (dynamic_cast (t.scope ())); + + if (dt_.tables_.find (rt) != dt_.tables_.end () || + m.find (rt) == m.names_end ()) + { + pre_statement (); + + // @@ This does not work: in MySQL control statements can only + // be used in stored procedures. It seems the only way to + // implement this is to define, execute, and drop a stored + // procedure, which is just too ugly. + // + os << "IF EXISTS (SELECT NULL FROM information_schema.TABLE_CONSTRAINTS" << endl + << " WHERE CONSTRAINT_TYPE = " << quote_string ("FOREIGN KEY") << "AND" << endl + << " CONSTRAINT_SCHEMA = DATABASE() AND" << endl + << " CONSTRAINT_NAME = " << quote_string (fk.name ()) << ") THEN" << endl + << " ALTER TABLE " << quote_id (t.name ()) << " DROP FOREIGN KEY " << quote_id (fk.name ()) << ";" << endl + << "END IF;" << endl; + post_statement (); + } + } + + private: + drop_table& dt_; + }; + + void drop_table:: + traverse (sema_rel::table& t) + { + qname const& table (t.name ()); + + if (pass_ == 1) + { + // Drop constraints. + // + tables_.insert (table); // Add it before to cover self-refs. + drop_foreign_key fk (*this); + trav_rel::unames n (fk); + names (t, n); + } + else if (pass_ == 2) + { + pre_statement (); + os << "DROP TABLE IF EXISTS " << quote_id (table) << endl; + post_statement (); + } + } + */ + + // // Create. // @@ -54,36 +146,82 @@ namespace relational }; entry create_column_; + struct create_foreign_key; + + struct create_table: relational::create_table, context + { + create_table (base const& x): base (x) {} + + void + traverse (sema_rel::table&); + + virtual void + create_post () + { + os << ")"; + + string const& engine (options.mysql_engine ()); + + if (engine != "default") + os << endl + << " ENGINE=" << engine; + + os << endl; + } + + private: + friend class create_foreign_key; + set tables_; // Set of tables we have already defined. + }; + entry create_table_; + struct create_foreign_key: relational::create_foreign_key, context { + create_foreign_key (schema_format f, relational::create_table& ct) + : base (f, ct) + { + } + create_foreign_key (base const& x): base (x) {} virtual void traverse (sema_rel::foreign_key& fk) { - // MySQL does not support deferred constraint checking. Output - // such foreign keys as comments, for documentation, unless we - // are generating embedded schema. + // If the referenced table has already been defined, do the + // foreign key definition in the table definition. Otherwise + // postpone it until pass 2 where we do it via ALTER TABLE + // (see add_foreign_key below). // - if (fk.deferred ()) + create_table& ct (static_cast (create_table_)); + + if (ct.tables_.find (fk.referenced_table ()) != ct.tables_.end ()) { - // Don't bloat C++ code with comment strings if we are - // generating embedded schema. + // MySQL does not support deferred constraint checking. Output + // such foreign keys as comments, for documentation, unless we + // are generating embedded schema. // - if (format_ != schema_format::embedded) + if (fk.deferred ()) { - os << endl - << endl - << " /*" << endl; + // Don't bloat C++ code with comment strings if we are + // generating embedded schema. + // + if (format_ != schema_format::embedded) + { + os << endl + << endl + << " /*" << endl; - base::create (fk); + base::create (fk); - os << endl - << " */"; + os << endl + << " */"; + } } + else + base::traverse (fk); + + fk.set ("mysql-fk-defined", true); // Mark it as defined. } - else - base::traverse (fk); } virtual void @@ -94,25 +232,66 @@ namespace relational }; entry create_foreign_key_; - struct create_table: relational::create_table, context + struct add_foreign_key: create_foreign_key, relational::common { - create_table (base const& x): base (x) {} + add_foreign_key (schema_format f, relational::create_table& ct) + : create_foreign_key (f, ct), common (ct.emitter (), ct.stream ()) + { + } virtual void - create_post () + traverse (sema_rel::foreign_key& fk) { - os << ")"; + if (!fk.count ("mysql-fk-defined")) + { + sema_rel::table& t (dynamic_cast (fk.scope ())); - string const& engine (options.mysql_engine ()); + // MySQL has no deferred constraints. + // + if (fk.deferred ()) + { + if (format_ != schema_format::embedded) + { + os << "/*" << endl; - if (engine != "default") - os << endl - << " ENGINE=" << engine; + os << "ALTER TABLE " << quote_id (t.name ()) << " ADD" << endl; + base::create (fk); - os << endl; + os << endl + << "*/" << endl + << endl; + } + } + else + { + pre_statement (); + + os << "ALTER TABLE " << quote_id (t.name ()) << " ADD" << endl; + base::create (fk); + os << endl; + + post_statement (); + } + } } }; - entry create_table_; + + void create_table:: + traverse (sema_rel::table& t) + { + if (pass_ == 1) + { + tables_.insert (t.name ()); // Add it before to cover self-refs. + base::traverse (t); + return; + } + + // Add foreign keys. + // + add_foreign_key fk (format_, *this); + trav_rel::unames n (fk); + names (t, n); + } struct create_index: relational::create_index, context { -- cgit v1.1