From b0391e168b489811708ca7ba5f71a0eb67b46ffe Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Wed, 3 Apr 2013 11:22:42 +0200 Subject: Generate add/drop table migration statements --- odb/relational/changelog.cxx | 35 ++++++--- odb/relational/generate.hxx | 12 ++- odb/relational/mssql/schema.cxx | 63 ++++++++++------ odb/relational/mysql/schema.cxx | 88 +++++++++++++++------- odb/relational/oracle/schema.cxx | 82 +++++++++++++------- odb/relational/pgsql/schema.cxx | 11 ++- odb/relational/schema.cxx | 52 +++++++++++++ odb/relational/schema.hxx | 159 ++++++++++++++++++++++++++------------- 8 files changed, 356 insertions(+), 146 deletions(-) (limited to 'odb/relational') diff --git a/odb/relational/changelog.cxx b/odb/relational/changelog.cxx index 1d536fc..f576c79 100644 --- a/odb/relational/changelog.cxx +++ b/odb/relational/changelog.cxx @@ -654,7 +654,8 @@ namespace relational model_version const& mv, changelog* old, std::string const& in_name, - std::string const& out_name) + std::string const& out_name, + bool force_init) { cutl::shared_ptr cl (new (shared) changelog); graph& g (*cl); @@ -672,8 +673,9 @@ namespace relational throw operation_failed (); } - cerr << out_name << ": info: initializing changelog with base " << - "version " << mv.base << endl; + if (!force_init) + cerr << out_name << ": info: initializing changelog with base " << + "version " << mv.base << endl; if (mv.base == mv.current) g.new_edge (*cl, g.new_node (m, g)); @@ -687,7 +689,10 @@ namespace relational changeset& c (diff (nm, m, *cl)); if (!c.names_empty ()) + { + g.new_edge (c, nm); g.new_edge (*cl, c); + } } return cl; @@ -725,12 +730,11 @@ namespace relational // changeset. // // - model* base (bver == mv.base ? &g.new_node (oldm, g) : 0); + model* last (&g.new_node (oldm, g)); + model* base (bver == mv.base ? last : 0); if (base != 0) g.new_edge (*cl, *base); - model* last (&oldm); - for (changelog::contains_changeset_iterator i ( old->contains_changeset_begin ()); i != old->contains_changeset_end (); ++i) @@ -743,7 +747,8 @@ namespace relational if (cs.version () == mv.current) break; - last = &patch (*last, cs, g); + model& prev (*last); + last = &patch (prev, cs, g); if (base == 0) { @@ -762,7 +767,10 @@ namespace relational changeset& c (diff (*base, *last, *cl)); if (!c.names_empty ()) + { + g.new_edge (c, *base); g.new_edge (*cl, c); + } continue; } @@ -780,6 +788,8 @@ namespace relational ? static_cast (*base) // Cannot be NULL. : cl->contains_changeset_back ().changeset (), g)); + + g.new_edge (c, prev); g.new_edge (*cl, c); } @@ -794,7 +804,7 @@ namespace relational { // Fast-forward the latest model to the new base. // - base = last != &oldm ? last : &g.new_node (oldm, g); + base = last; base->version (mv.base); } @@ -835,10 +845,13 @@ namespace relational // if (mv.base != mv.current) { - changeset& cs (diff (*last, m, *cl)); + changeset& c (diff (*last, m, *cl)); - if (!cs.names_empty ()) - g.new_edge (*cl, cs); + if (!c.names_empty ()) + { + g.new_edge (c, *last); + g.new_edge (*cl, c); + } } return cl; diff --git a/odb/relational/generate.hxx b/odb/relational/generate.hxx index a50b999..5bd795e 100644 --- a/odb/relational/generate.hxx +++ b/odb/relational/generate.hxx @@ -10,6 +10,7 @@ #include #include +#include #include namespace relational @@ -53,7 +54,8 @@ namespace relational model_version const&, semantics::relational::changelog* old, // Can be NULL. std::string const& in_name, - std::string const& out_name); + std::string const& out_name, + bool force_init); } namespace schema @@ -62,13 +64,19 @@ namespace relational generate_prologue (); void + generate_epilogue (); + + void generate_drop (); void generate_create (); void - generate_epilogue (); + generate_migrate_pre (semantics::relational::changeset&); + + void + generate_migrate_post (semantics::relational::changeset&); } } diff --git a/odb/relational/mssql/schema.cxx b/odb/relational/mssql/schema.cxx index ca89d68..11467b3 100644 --- a/odb/relational/mssql/schema.cxx +++ b/odb/relational/mssql/schema.cxx @@ -45,7 +45,7 @@ namespace relational drop_table (base const& x): base (x) {} virtual void - traverse (sema_rel::table&); + traverse (sema_rel::table&, bool); private: friend class drop_foreign_key; @@ -55,8 +55,8 @@ namespace relational struct drop_foreign_key: trav_rel::foreign_key, relational::common { - drop_foreign_key (drop_table& dt) - : common (dt.emitter (), dt.stream ()), dt_ (dt) + drop_foreign_key (drop_table& dt, bool m) + : common (dt.emitter (), dt.stream ()), dt_ (dt), migration_ (m) { } @@ -71,39 +71,52 @@ namespace relational // 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. + // it is dropped before us. In migration we always do this + // first. // - 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 ()) + if (!migration_) { - pre_statement (); + sema_rel::qname const& rt (fk.referenced_table ()); + sema_rel::model& m (dynamic_cast (t.scope ())); + + if (dt_.tables_.find (rt) == dt_.tables_.end () && + m.find (rt) != m.names_end ()) + return; + } + + pre_statement (); + + if (!migration_) os << "IF OBJECT_ID(" << quote_string (fk.name ()) << ", " << quote_string ("F") << ") IS NOT NULL" << endl - << " ALTER TABLE " << quote_id (t.name ()) << " DROP" << endl - << " CONSTRAINT " << quote_id (fk.name ()) << endl; - post_statement (); - } + << " "; + + os << "ALTER TABLE " << quote_id (t.name ()) << " DROP" << endl + << (!migration_ ? " " : "") << " CONSTRAINT " << + quote_id (fk.name ()) << endl; + + post_statement (); } private: drop_table& dt_; + bool migration_; }; void drop_table:: - traverse (sema_rel::table& t) + traverse (sema_rel::table& t, bool migration) { qname const& table (t.name ()); if (pass_ == 1) { - // Drop constraints. + // Drop constraints. In migration this is always done on pass 1. // - tables_.insert (table); // Add it before to cover self-refs. - drop_foreign_key fk (*this); + if (!migration) + tables_.insert (table); // Add it before to cover self-refs. + drop_foreign_key fk (*this, migration); trav_rel::unames n (fk); names (t, n); } @@ -114,9 +127,14 @@ namespace relational // drop a table if it exists. // pre_statement (); - os << "IF OBJECT_ID(" << quote_string (table.string ()) << - ", " << quote_string ("U") << ") IS NOT NULL" << endl - << " DROP TABLE " << quote_id (table) << endl; + + if (!migration) + os << "IF OBJECT_ID(" << quote_string (table.string ()) << + ", " << quote_string ("U") << ") IS NOT NULL" << endl + << " "; + + os << "DROP TABLE " << quote_id (table) << endl; + post_statement (); } } @@ -258,7 +276,10 @@ namespace relational { if (pass_ == 1) { - tables_.insert (t.name ()); // Add it before to cover self-refs. + // In migration we always add foreign keys on pass 2. + // + if (!t.is_a ()) + tables_.insert (t.name ()); // Add it before to cover self-refs. base::traverse (t); return; } diff --git a/odb/relational/mysql/schema.cxx b/odb/relational/mysql/schema.cxx index 3784c31..e216918 100644 --- a/odb/relational/mysql/schema.cxx +++ b/odb/relational/mysql/schema.cxx @@ -22,13 +22,12 @@ namespace relational // // Drop. // - /* struct drop_table: relational::drop_table, context { drop_table (base const& x): base (x) {} virtual void - traverse (sema_rel::table&); + traverse (sema_rel::table&, bool migration); private: friend class drop_foreign_key; @@ -38,8 +37,8 @@ namespace relational struct drop_foreign_key: trav_rel::foreign_key, relational::common { - drop_foreign_key (drop_table& dt) - : common (dt.emitter (), dt.stream ()), dt_ (dt) + drop_foreign_key (drop_table& dt, bool m) + : common (dt.emitter (), dt.stream ()), dt_ (dt), migration_ (m) { } @@ -54,58 +53,86 @@ namespace relational // 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. + // it is dropped before us. In migration we always do this + // first. // - 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 ()) + if (!migration_) { - pre_statement (); + sema_rel::qname const& rt (fk.referenced_table ()); + sema_rel::model& m (dynamic_cast (t.scope ())); - // @@ 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 (); + if (dt_.tables_.find (rt) == dt_.tables_.end () && + m.find (rt) != m.names_end ()) + return; } + + 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. + // + // Another option would be to use CREATE TABLE IF NOT EXISTS + // to create a dummy table with a dummy constraint that makes + // the following DROP succeed. Note, however, that MySQL issues + // a notice if the table already exist so would need to suppress + // that as well. Still not sure that the utility of this support + // justifies this kind of a hack. + // + 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; + */ + + os << "ALTER TABLE " << quote_id (t.name ()) << " DROP FOREIGN " << + "KEY " << quote_id (fk.name ()) << endl; + + post_statement (); } private: drop_table& dt_; + bool migration_; }; void drop_table:: - traverse (sema_rel::table& t) + traverse (sema_rel::table& t, bool migration) { + // Only enabled for migration support for now (see above). + // + if (!migration) + { + base::traverse (t, migration); + return; + } + qname const& table (t.name ()); if (pass_ == 1) { - // Drop constraints. + // Drop constraints. In migration this is always done on pass 1. // - tables_.insert (table); // Add it before to cover self-refs. - drop_foreign_key fk (*this); + if (!migration) + tables_.insert (table); // Add it before to cover self-refs. + drop_foreign_key fk (*this, migration); trav_rel::unames n (fk); names (t, n); } else if (pass_ == 2) { pre_statement (); - os << "DROP TABLE IF EXISTS " << quote_id (table) << endl; + os << "DROP TABLE " << (migration ? "" : "IF EXISTS ") << + quote_id (table) << endl; post_statement (); } } - */ // // Create. @@ -281,7 +308,10 @@ namespace relational { if (pass_ == 1) { - tables_.insert (t.name ()); // Add it before to cover self-refs. + // In migration we always add foreign keys on pass 2. + // + if (!t.is_a ()) + tables_.insert (t.name ()); // Add it before to cover self-refs. base::traverse (t); return; } diff --git a/odb/relational/oracle/schema.cxx b/odb/relational/oracle/schema.cxx index 28fd4fd..bd0acda 100644 --- a/odb/relational/oracle/schema.cxx +++ b/odb/relational/oracle/schema.cxx @@ -93,49 +93,72 @@ namespace relational drop_table (base const& x): base (x) {} virtual void - traverse (sema_rel::table& t) + traverse (sema_rel::table& t, bool migration) { if (pass_ != 1) return; - qname const& table (t.name ()); - - // Oracle has no IF EXISTS conditional for dropping objects. The - // PL/SQL approach below seems to be the least error-prone and the - // most widely used of the alternatives. - // - pre_statement (); - os << "BEGIN" << endl - << " BEGIN" << endl - << " EXECUTE IMMEDIATE 'DROP TABLE " << quote_id (table) << - " CASCADE CONSTRAINTS';" << endl - << " EXCEPTION" << endl - << " WHEN OTHERS THEN" << endl - << " IF SQLCODE != -942 THEN RAISE; END IF;" << endl - << " END;" << endl; - - // Drop the sequence if we have auto primary key. - // using sema_rel::primary_key; + qname const& table (t.name ()); + sema_rel::table::names_iterator i (t.find ("")); // Special name. primary_key* pk (i != t.names_end () ? &dynamic_cast (i->nameable ()) : 0); - if (pk != 0 && pk->auto_ ()) + string qt (quote_id (table)); + string qs (pk != 0 && pk->auto_ () + ? quote_id (sequence_name (table)) + : ""); + + if (migration) + { + pre_statement (); + os << "DROP TABLE " << qt << " CASCADE CONSTRAINTS" << endl; + post_statement (); + + // Drop the sequence if we have auto primary key. + // + if (!qs.empty ()) + { + pre_statement (); + os << "DROP SEQUENCE " << qs << endl; + post_statement (); + } + } + else { - os << " BEGIN" << endl - << " EXECUTE IMMEDIATE 'DROP SEQUENCE " << - quote_id (sequence_name (table)) << "';" << endl + // Oracle has no IF EXISTS conditional for dropping objects. The + // PL/SQL approach below seems to be the least error-prone and the + // most widely used of the alternatives. + // + pre_statement (); + os << "BEGIN" << endl + << " BEGIN" << endl + << " EXECUTE IMMEDIATE 'DROP TABLE " << qt << " CASCADE " << + "CONSTRAINTS';" << endl << " EXCEPTION" << endl << " WHEN OTHERS THEN" << endl - << " IF SQLCODE != -2289 THEN RAISE; END IF;" << endl + << " IF SQLCODE != -942 THEN RAISE; END IF;" << endl << " END;" << endl; - } - os << "END;" << endl; - post_statement (); + // Drop the sequence if we have auto primary key. + // + if (!qs.empty ()) + { + os << " BEGIN" << endl + << " EXECUTE IMMEDIATE 'DROP SEQUENCE " << qs << + "';" << endl + << " EXCEPTION" << endl + << " WHEN OTHERS THEN" << endl + << " IF SQLCODE != -2289 THEN RAISE; END IF;" << endl + << " END;" << endl; + } + + os << "END;" << endl; + post_statement (); + } } }; entry drop_table_; @@ -217,7 +240,10 @@ namespace relational { if (pass_ == 1) { - tables_.insert (t.name ()); // Add it before to cover self-refs. + // In migration we always add foreign keys on pass 2. + // + if (!t.is_a ()) + tables_.insert (t.name ()); // Add it before to cover self-refs. base::traverse (t); // Create the sequence if we have auto primary key. diff --git a/odb/relational/pgsql/schema.cxx b/odb/relational/pgsql/schema.cxx index 0a2b82d..efe9b40 100644 --- a/odb/relational/pgsql/schema.cxx +++ b/odb/relational/pgsql/schema.cxx @@ -28,10 +28,10 @@ namespace relational drop_table (base const& x): base (x) {} virtual void - drop (sema_rel::qname const& table) + drop (sema_rel::qname const& table, bool migration) { - os << "DROP TABLE IF EXISTS " << quote_id (table) << - " CASCADE" << endl; + os << "DROP TABLE " << (migration ? "" : "IF EXISTS ") << + quote_id (table) << " CASCADE" << endl; } }; entry drop_table_; @@ -146,7 +146,10 @@ namespace relational { if (pass_ == 1) { - tables_.insert (t.name ()); // Add it before to cover self-refs. + // In migration we always add foreign keys on pass 2. + // + if (!t.is_a ()) + tables_.insert (t.name ()); // Add it before to cover self-refs. base::traverse (t); return; } diff --git a/odb/relational/schema.cxx b/odb/relational/schema.cxx index 492c3b7..e764790 100644 --- a/odb/relational/schema.cxx +++ b/odb/relational/schema.cxx @@ -86,5 +86,57 @@ namespace relational model->traverse (*ctx.model); } } + + void + generate_migrate_pre (sema_rel::changeset& cs) + { + context ctx; + instance em; + emitter_ostream emos (*em); + + schema_format f (schema_format::sql); + + instance changeset (*em, emos, f); + instance ctable (*em, emos, f); + trav_rel::qnames names; + + changeset >> names >> ctable; + + // Pass 1 and 2. + // + for (unsigned short pass (1); pass < 3; ++pass) + { + changeset->pass (pass); + ctable->pass (pass); + + changeset->traverse (cs); + } + } + + void + generate_migrate_post (sema_rel::changeset& cs) + { + context ctx; + instance em; + emitter_ostream emos (*em); + + schema_format f (schema_format::sql); + + instance changeset (*em, emos, f); + instance dtable (*em, emos, f); + trav_rel::qnames names; + + changeset >> names >> dtable; + + // Pass 1 and 2. + // + for (unsigned short pass (1); pass < 3; ++pass) + { + changeset->pass (pass); + dtable->pass (pass); + + changeset->traverse (cs); + } + } } } diff --git a/odb/relational/schema.hxx b/odb/relational/schema.hxx index 86b7419..8ce9f59 100644 --- a/odb/relational/schema.hxx +++ b/odb/relational/schema.hxx @@ -58,84 +58,69 @@ namespace relational // Drop. // - struct drop_index: trav_rel::index, common + struct drop_table: trav_rel::table, + trav_rel::drop_table, + trav_rel::add_table, // override + trav_rel::alter_table, // override + common { - typedef drop_index base; + typedef drop_table base; - drop_index (emitter_type& e, ostream& os, schema_format f) + drop_table (emitter_type& e, ostream& os, schema_format f) : common (e, os), format_ (f) { } - virtual string - name (sema_rel::index& in) - { - return quote_id (in.name ()); - } - - virtual string - table_name (sema_rel::index& in) + virtual void + drop (sema_rel::qname const& table, bool migration) { - return quote_id (static_cast (in.scope ()).name ()); + os << "DROP TABLE " << (migration ? "" : "IF EXISTS ") << + quote_id (table) << endl; } virtual void - drop (string const& /*name*/, string const& /*table*/) + traverse (sema_rel::table& t, bool migration) { - // Most database systems drop indexes together with the table. + // By default we do everything in a single pass. But some + // databases may require the second pass. // - // os << "DROP INDEX IF EXISTS " << quote_id (name) << " ON " << - // table << endl; - } + if (pass_ > 1) + return; - virtual void - traverse (sema_rel::index& in) - { pre_statement (); - drop (name (in), table_name (in)); + drop (t.name (), migration); post_statement (); } - protected: - schema_format format_; - }; - - struct drop_table: trav_rel::table, common - { - typedef drop_table base; - - drop_table (emitter_type& e, ostream& os, schema_format f) - : common (e, os), format_ (f) + virtual void + traverse (sema_rel::table& t) { + traverse (t, false); } virtual void - drop (sema_rel::qname const& table) + traverse (sema_rel::drop_table& dt) { - os << "DROP TABLE IF EXISTS " << quote_id (table) << endl; + using sema_rel::model; + using sema_rel::changeset; + using sema_rel::table; + + // Find the table we are dropping in the base model. + // + changeset& cs (dynamic_cast (dt.scope ())); + model& bm (cs.base_model ()); + table* t (bm.find (dt.name ())); + assert (t != 0); + traverse (*t, true); } virtual void - traverse (sema_rel::table& t) - { - // By default we do everything in a single pass. But some - // databases may require the second pass. - // - if (pass_ > 1) - return; + traverse (sema_rel::add_table&) {} - // Drop indexes. - // - { - instance in (emitter (), stream (), format_); - trav_rel::unames n (*in); - names (t, n); - } + virtual void + traverse (sema_rel::alter_table&) {} - pre_statement (); - drop (t.name ()); - post_statement (); - } + using table::names; void pass (unsigned short p) @@ -646,6 +631,78 @@ namespace relational unsigned short pass_; }; + struct migrate_pre_changeset: trav_rel::changeset, common + { + typedef migrate_pre_changeset base; + + migrate_pre_changeset (emitter_type& e, ostream& os, schema_format f) + : common (e, os), format_ (f) + { + } + + // This version is only called for file migration. + // + virtual void + traverse (sema_rel::changeset& m) + { + traverse (m.names_begin (), m.names_end ()); + } + + virtual void + traverse (sema_rel::changeset::names_iterator begin, + sema_rel::changeset::names_iterator end) + { + for (; begin != end; ++begin) + dispatch (*begin); + } + + void + pass (unsigned short p) + { + pass_ = p; + } + + protected: + schema_format format_; + unsigned short pass_; + }; + + struct migrate_post_changeset: trav_rel::changeset, common + { + typedef migrate_post_changeset base; + + migrate_post_changeset (emitter_type& e, ostream& os, schema_format f) + : common (e, os), format_ (f) + { + } + + // This version is only called for file migration. + // + virtual void + traverse (sema_rel::changeset& m) + { + traverse (m.names_begin (), m.names_end ()); + } + + virtual void + traverse (sema_rel::changeset::names_iterator begin, + sema_rel::changeset::names_iterator end) + { + for (; begin != end; ++begin) + dispatch (*begin); + } + + void + pass (unsigned short p) + { + pass_ = p; + } + + protected: + schema_format format_; + unsigned short pass_; + }; + // // SQL output. // -- cgit v1.1