Generate database schema from database model instead of C++ model

We now first create the so-called database model from C++ model and
then use that to generate the database schema. The new approach also
adds more general support for primary/foreign keys, including multi-
column keys. Finally, for MySQL we now generate out-of-line foreign
key definitions. Because MySQL does not support deferred constraints
checking, deferred foreign keys are written commented out, for
documentation.

4 files changed, 272 insertions, 263 deletions

diff --git a/odb/relational/oracle/context.cxx b/odb/relational/oracle/context.cxx
index 236c725..3fe3e6d 100644
--- a/odb/relational/oracle/context.cxx
+++ b/odb/relational/oracle/context.cxx
@@ -67,16 +67,19 @@ namespace relational
     }
 
     context::
-    context (ostream& os, semantics::unit& u, options_type const& ops)
+    context (ostream& os,
+             semantics::unit& u,
+             options_type const& ops,
+             sema_rel::model* m)
         : root_context (os, u, ops, data_ptr (new (shared) data (os))),
-          base_context (static_cast<data*> (root_context::data_.get ())),
+          base_context (static_cast<data*> (root_context::data_.get ()), m),
           data_ (static_cast<data*> (base_context::data_))
     {
       assert (current_ == 0);
       current_ = this;
 
-      data_->generate_grow_ = false;
-      data_->need_alias_as_ = false;
+      generate_grow = false;
+      need_alias_as = false;
       data_->bind_vector_ = "oracle::bind*";
 
       // Populate the C++ type to DB type map.
@@ -143,9 +146,6 @@ namespace relational
     // SQL type parsing.
     //
 
-    static sql_type
-    parse_sql_type (semantics::data_member& m, std::string const& sql);
-
     sql_type const& context::
     column_sql_type (semantics::data_member& m, string const& kp)
     {
@@ -154,18 +154,30 @@ namespace relational
                   : "oracle-" + kp + "-column-sql-type");
 
       if (!m.count (key))
-        m.set (key, parse_sql_type (m, column_type (m, kp)));
+      {
+        try
+        {
+          m.set (key, parse_sql_type (column_type (m, kp)));
+        }
+        catch (invalid_sql_type const& e)
+        {
+          cerr << m.file () << ":" << m.line () << ":" << m.column ()
+               << ": error: " << e.message () << endl;
+
+          throw operation_failed ();
+        }
+      }
 
       return m.get<sql_type> (key);
     }
 
-    static sql_type
-    parse_sql_type (semantics::data_member& m, string const& sql)
+    sql_type context::
+    parse_sql_type (string const& sqlt)
     {
       try
       {
         sql_type r;
-        sql_lexer l (sql);
+        sql_lexer l (sqlt);
 
         // While most type names use single identifier, there are
         // a couple of exceptions to this rule:
@@ -304,11 +316,9 @@ namespace relational
                          (prefix == "TIMESTAMP WITH LOCAL TIME" ||
                           prefix == "TIMESTAMP WITH TIME"))
                 {
-                  cerr << m.file () << ":" << m.line () << ":"
-                       << m.column ()<< ": error: Oracle timestamps with time "
-                       << "zones are not currently supported" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "Oracle timestamps with time zones are not currently "
+                    "supported");
                 }
                 //
                 // String and binary types.
@@ -387,24 +397,18 @@ namespace relational
                 //
                 else if (id == "LONG")
                 {
-                  cerr << m.file () << ":" << m.line () << ":"
-                       << m.column () << ": error: LONG types are not "
-                       << " supported" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "Oracle LONG types are not supported");
                 }
                 else
                 {
-                  cerr << m.file () << ":" << m.line () << ":" <<
-                    m.column () << ":";
-
                   if (tt == sql_token::t_identifier)
-                    cerr << " error: unknown Oracle type '" <<
-                      t.identifier () << "'" << endl;
+                  {
+                    throw invalid_sql_type (
+                      "unknown Oracle type '" + t.identifier () + "'");
+                  }
                   else
-                    cerr << " error: expected Oracle type name" << endl;
-
-                  throw operation_failed ();
+                    throw invalid_sql_type ("expected Oracle type name");
                 }
               }
 
@@ -440,16 +444,13 @@ namespace relational
 
                 if (r.type == sql_type::invalid)
                 {
-                  cerr << m.file () << ":" << m.line () << ":" <<
-                    m.column () << ":";
-
                   if (tt == sql_token::t_identifier)
-                    cerr << " error: unknown Oracle type '" <<
-                      prefix + t.identifier () << "'" << endl;
+                  {
+                    throw invalid_sql_type (
+                      "unknown Oracle type '" + t.identifier () + "'");
+                  }
                   else
-                    cerr << " error: expected Oracle type name" << endl;
-
-                  throw operation_failed ();
+                    throw invalid_sql_type ("expected Oracle type name");
                 }
               }
 
@@ -465,11 +466,8 @@ namespace relational
 
                 if (t.type () != sql_token::t_int_lit)
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: integer range expected in Oracle type "
-                       << "declaration" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "integer range expected in Oracle type declaration");
                 }
 
                 // Parse the range.
@@ -480,12 +478,9 @@ namespace relational
 
                   if (!(is >> v && is.eof ()))
                   {
-                    cerr << m.file () << ":" << m.line () << ":"
-                         << m.column ()
-                         << ": error: invalid range value '" << t.literal ()
-                         << "'in Oracle type declaration" << endl;
-
-                    throw operation_failed ();
+                    throw invalid_sql_type (
+                      "invalid range value '" + t.literal () + "' in Oracle "
+                      "type declaration");
                   }
 
                   r.range = true;
@@ -502,11 +497,8 @@ namespace relational
 
                   if (t.type () != sql_token::t_int_lit)
                   {
-                    cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                         << ": error: integer scale expected in Oracle type "
-                         << "declaration" << endl;
-
-                    throw operation_failed ();
+                    throw invalid_sql_type (
+                      "integer scale expected in Oracle type declaration");
                   }
 
                   short v;
@@ -514,12 +506,9 @@ namespace relational
 
                   if (!(is >> v && is.eof ()))
                   {
-                    cerr << m.file () << ":" << m.line () << ":"
-                         << m.column ()
-                         << ": error: invalid scale value '" << t.literal ()
-                         << "'in Oracle type declaration" << endl;
-
-                    throw operation_failed ();
+                    throw invalid_sql_type (
+                      "invalid scale value '" + t.literal () + "' in Oracle "
+                      "type declaration");
                   }
 
                   r.scale = true;
@@ -535,20 +524,16 @@ namespace relational
                     r.byte_semantics = false;
                   else if (id != "BYTE")
                   {
-                    cerr << m.file () << ":" << m.line () << ":"
-                         << m.column ()
-                         << ": error: invalid keyword '" << t.literal ()
-                         << "'in Oracle type declaration" << endl;
+                    throw invalid_sql_type (
+                      "invalid keyword '" + t.literal () + "' in Oracle "
+                      "type declaration");
                   }
                 }
 
                 if (t.punctuation () != sql_token::p_rparen)
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: expected ')' in Oracle type declaration"
-                       << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "expected ')' in Oracle type declaration");
                 }
               }
 
@@ -594,21 +579,16 @@ namespace relational
 
         if (r.type == sql_type::invalid)
         {
-          cerr << "error: incomplete Oracle type declaration: " << prefix
-               << endl;
-
-          throw operation_failed ();
+          throw invalid_sql_type (
+            "incomplete Oracle type declaration: '" + prefix + "'");
         }
 
         return r;
       }
       catch (sql_lexer::invalid_input const& e)
       {
-        cerr << m.file () << ":" << m.line () << ":" << m.column ()
-             << ": error: invalid Oracle type declaration: " << e.message
-             << endl;
-
-        throw operation_failed ();
+        throw invalid_sql_type (
+          "invalid Oracle type declaration: " + e.message);
       }
     }
   }
diff --git a/odb/relational/oracle/context.hxx b/odb/relational/oracle/context.hxx
index d334f56..e99b273 100644
--- a/odb/relational/oracle/context.hxx
+++ b/odb/relational/oracle/context.hxx
@@ -74,6 +74,22 @@ namespace relational
       column_sql_type (semantics::data_member&,
                        string const& key_prefix = string ());
 
+    public:
+      struct invalid_sql_type
+      {
+        invalid_sql_type (string const& message): message_ (message) {}
+
+        string const&
+        message () const {return message_;}
+
+      private:
+        string message_;
+      };
+
+      static sql_type
+      parse_sql_type (string const&);
+
+    public:
       static bool
       unsigned_integer (semantics::type&);
 
@@ -90,7 +106,10 @@ namespace relational
       ~context ();
 
       context ();
-      context (std::ostream&, semantics::unit&, options_type const&);
+      context (std::ostream&,
+               semantics::unit&,
+               options_type const&,
+               sema_rel::model*);
 
       static context&
       current ()
diff --git a/odb/relational/oracle/model.cxx b/odb/relational/oracle/model.cxx
new file mode 100644
index 0000000..a71d351
--- /dev/null
+++ b/odb/relational/oracle/model.cxx
@@ -0,0 +1,74 @@
+// file      : odb/relational/oracle/model.cxx
+// author    : Constantin Michael <constantin@codesynthesis.com>
+// copyright : Copyright (c) 2009-2011 Code Synthesis Tools CC
+// license   : GNU GPL v3; see accompanying LICENSE file
+
+#include <sstream>
+
+#include <odb/relational/model.hxx>
+
+#include <odb/relational/oracle/common.hxx>
+#include <odb/relational/oracle/context.hxx>
+
+using namespace std;
+
+namespace relational
+{
+  namespace oracle
+  {
+    namespace model
+    {
+      namespace relational = relational::model;
+
+      struct object_columns: relational::object_columns, context
+      {
+        object_columns (base const& x): base (x) {}
+
+        virtual string
+        default_bool (semantics::data_member&, bool v)
+        {
+          // Oracle seems to have the TRUE and FALSE literals, though no
+          // boolean type.
+          //
+          return v ? "TRUE" : "FALSE";
+        }
+
+        virtual string
+        default_enum (semantics::data_member& m, tree en, string const&)
+        {
+          // Make sure the column is mapped to Oracle NUMBER.
+          //
+          sql_type t (column_sql_type (m));
+
+          if (t.type != sql_type::NUMBER)
+          {
+            cerr << m.file () << ":" << m.line () << ":" << m.column ()
+                 << ": error: column with default value specified as C++ "
+                 << "enumerator must map to Oracle NUMBER" << endl;
+
+            throw operation_failed ();
+          }
+
+          using semantics::enumerator;
+
+          enumerator& e (dynamic_cast<enumerator&> (*unit.find (en)));
+
+          ostringstream ostr;
+
+          if (e.enum_ ().unsigned_ ())
+            ostr << e.value ();
+          else
+            ostr << static_cast<long long> (e.value ());
+
+          return ostr.str ();
+        }
+
+        virtual void
+        reference (semantics::data_member&)
+        {
+        }
+      };
+      entry<object_columns> object_columns_;
+    }
+  }
+}
diff --git a/odb/relational/oracle/schema.cxx b/odb/relational/oracle/schema.cxx
index 8469761..a371235 100644
--- a/odb/relational/oracle/schema.cxx
+++ b/odb/relational/oracle/schema.cxx
@@ -3,7 +3,7 @@
 // copyright : Copyright (c) 2009-2011 Code Synthesis Tools CC
 // license   : GNU GPL v3; see accompanying LICENSE file
 
-#include <string>
+#include <set>
 
 #include <odb/relational/schema.hxx>
 
@@ -27,8 +27,13 @@ namespace relational
         virtual void
         line (const std::string& l)
         {
-          base::line (l);
-          last_ = l;
+          // SQLPlus doesn't like empty line in the middle of a statement.
+          //
+          if (!l.empty ())
+          {
+            base::line (l);
+            last_ = l;
+          }
         }
 
         virtual void
@@ -61,8 +66,11 @@ namespace relational
         schema_file (const base& x): base (x) {}
 
         virtual void
-        pre ()
+        prologue ()
         {
+          // Quiet down SQLPlus and make sure it exits with an error
+          // code if there is an error.
+          //
           os << "SET FEEDBACK OFF;" << endl
              << "WHENEVER SQLERROR EXIT FAILURE;" << endl
              << "WHENEVER OSERROR EXIT FAILURE;" << endl
@@ -70,7 +78,7 @@ namespace relational
         }
 
         virtual void
-        post ()
+        epilogue ()
         {
           os << "EXIT;" << endl;
         }
@@ -81,10 +89,12 @@ namespace relational
       // Drop.
       //
 
-      struct drop_common: virtual relational::drop_common
+      struct drop_table: relational::drop_table, context
       {
+        drop_table (base const& x): base (x) {}
+
         virtual void
-        drop_table (string const& table)
+        drop (string const& table)
         {
           // Oracle has no IF EXISTS conditional for dropping objects. The
           // PL/SQL approach below seems to be the least error-prone and the
@@ -107,7 +117,7 @@ namespace relational
              << "  END;" << endl
              << "  BEGIN" << endl
              << "    EXECUTE IMMEDIATE 'DROP TRIGGER " <<
-            quote_id (table + "_trig") << "';" << endl
+            quote_id (table + "_trg") << "';" << endl
              << "  EXCEPTION" << endl
              << "    WHEN OTHERS THEN" << endl
              << "      IF SQLCODE != -4080 THEN RAISE; END IF;" << endl
@@ -115,250 +125,176 @@ namespace relational
              << "END;" << endl;
         }
       };
-
-      struct member_drop: relational::member_drop, drop_common
-      {
-        member_drop (base const& x): base (x) {}
-      };
-      entry<member_drop> member_drop_;
-
-      struct class_drop: relational::class_drop, drop_common
-      {
-        class_drop (base const& x): base (x) {}
-      };
-      entry<class_drop> class_drop_;
+      entry<drop_table> drop_table_;
 
       //
       // Create.
       //
 
-      struct object_columns: relational::object_columns, context
+      struct create_foreign_key;
+
+      struct create_table: relational::create_table, context
       {
-        object_columns (base const& x): base (x) {}
+        create_table (base const& x): base (x) {}
 
-        virtual void
-        null (semantics::data_member& m)
-        {
-          sql_type::core_type t (column_sql_type (m, prefix_).type);
+        void
+        traverse (sema_rel::table&);
 
-          // Oracle interprets empty VARCHAR2 and NVARCHAR2 strings as NULL. As
-          // an empty string is always valid within the C++ context, VARCHAR2
-          // and NVARCHAR2 columns are always specified as nullable.
-          //
-          if (t != sql_type::VARCHAR2 && t != sql_type::NVARCHAR2)
-            base::null (m);
-        }
+      private:
+        friend class create_foreign_key;
+        set<string> tables_; // Set of tables we have already defined.
+      };
+      entry<create_table> create_table_;
+
+      struct create_column: relational::create_column, context
+      {
+        create_column (base const& x): base (x) {}
 
         virtual void
-        default_enum (semantics::data_member& m, tree en, string const&)
+        null (sema_rel::column& c)
         {
-          // Make sure the column is mapped to Oracle NUMBER.
+          // Oracle interprets empty VARCHAR2 and NVARCHAR2 strings as
+          // NULL. As an empty string is always valid within the C++
+          // context, VARCHAR2 and NVARCHAR2 columns are always
+          // specified as nullable.
           //
-          sql_type t (column_sql_type (m));
-
-          if (t.type != sql_type::NUMBER)
+          if (!c.null ())
           {
-            cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                 << ": error: column with default value specified as C++ "
-                 << "enumerator must map to Oracle NUMBER" << endl;
+            // This should never fail since we have already parsed this.
+            //
+            sql_type const& t (parse_sql_type (c.type ()));
 
-            throw operation_failed ();
+            if (t.type == sql_type::VARCHAR2 || t.type == sql_type::NVARCHAR2)
+              return;
           }
 
-          using semantics::enumerator;
-
-          enumerator& e (dynamic_cast<enumerator&> (*unit.find (en)));
-
-          if (e.enum_ ().unsigned_ ())
-            os << " DEFAULT " << e.value ();
-          else
-            os << " DEFAULT " << static_cast<long long> (e.value ());
-        }
-
-        virtual void
-        reference (semantics::data_member&)
-        {
+          base::null (c);
         }
       };
-      entry<object_columns> object_columns_;
+      entry<create_column> create_column_;
 
-      struct object_columns_references:
-        object_columns_base, relational::common, context
+      struct create_foreign_key: relational::create_foreign_key, context
       {
-        object_columns_references (emitter& e,
-                                   ostream& os,
-                                   string const& table,
-                                   string const& prefix = string ())
-            : relational::common (e, os),
-              table_ (table),
-              prefix_ (prefix)
+        create_foreign_key (schema_format f, relational::create_table& ct)
+            : base (f, ct)
         {
         }
 
-        virtual bool
-        traverse_column (semantics::data_member& m, string const& name, bool)
-        {
-          if (inverse (m))
-            return false;
-
-          if (semantics::class_* c =
-              object_pointer (member_utype (m, prefix_)))
-          {
-            pre_statement ();
+        create_foreign_key (base const& x): base (x) {}
 
-            os << "ALTER TABLE " << quote_id (table_) << endl
-               << "  ADD FOREIGN KEY (" << quote_id (name) << ")" << endl
-               << "  REFERENCES " << table_qname (*c) << endl
-               << "  DEFERRABLE INITIALLY DEFERRED" << endl;
+        virtual void
+        traverse (sema_rel::foreign_key& fk)
+        {
+          // 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).
+          //
+          create_table& ct (static_cast<create_table&> (create_table_));
 
-            post_statement ();
-          }
-          else if (prefix_ == "id")
+          if (ct.tables_.find (fk.referenced_table ()) != ct.tables_.end ())
           {
-            semantics::class_& c (*context::top_object);
-
-            pre_statement ();
-
-            // We don't need INITIALLY DEFERRED here since the object row
-            // must exist before any container row.
-            //
-            os << "ALTER TABLE " << quote_id (table_) << endl
-               << "  ADD FOREIGN KEY (" << quote_id (name) << ")" << endl
-               << "  REFERENCES " << table_qname (c) << endl
-               << "  ON DELETE CASCADE" << endl;
-
-            post_statement ();
+            base::traverse (fk);
+            fk.set ("oracle-fk-defined", true); // Mark it as defined.
           }
-
-          return true;
         }
 
-      private:
-        string table_;
-        string prefix_;
+        virtual string
+        name (sema_rel::foreign_key& fk)
+        {
+          // In Oracle, foreign key names are schema-global. Make them
+          // unique by prefixing the key name with table name.
+          //
+          return static_cast<sema_rel::table&> (fk.scope ()).name () +
+            '_' + fk.name ();
+        }
       };
+      entry<create_foreign_key> create_foreign_key_;
 
-      struct member_create: object_members_base, context
+      struct add_foreign_key: create_foreign_key, relational::common
       {
-        member_create (emitter& e, ostream& os, relational::tables& tables)
-            : object_members_base (false, true, false),
-              e_ (e),
-              os_ (os),
-              tables_ (tables)
+        add_foreign_key (schema_format f, relational::create_table& ct)
+            : create_foreign_key (f, ct), common (ct.emitter (), ct.stream ())
         {
         }
 
         virtual void
-        traverse_container (semantics::data_member& m, semantics::type& t)
+        traverse (sema_rel::foreign_key& fk)
         {
-          using semantics::type;
-          using semantics::data_member;
-
-          // Ignore inverse containers of object pointers.
-          //
-          if (inverse (m, "value"))
-            return;
-
-          string const& name (table_name (m, table_prefix_));
-
-          if (tables_.count (name))
-            return;
+          if (!fk.count ("oracle-fk-defined"))
+          {
+            sema_rel::table& t (dynamic_cast<sema_rel::table&> (fk.scope ()));
 
-          type& vt (container_vt (t));
+            pre_statement ();
 
-          // object_id
-          //
-          {
-            object_columns_references ocr (e_, os_, name, "id");
-            string id_name (column_name (m, "id", "object_id"));
-            ocr.traverse_column (m, id_name, true);
-          }
+            os << "ALTER TABLE " << quote_id (t.name ()) << " ADD" << endl;
+            base::create (fk);
+            os << endl;
 
-          // value
-          //
-          if (semantics::class_* cvt = composite_wrapper (vt))
-          {
-            object_columns_references ocr (e_, os_, name);
-            ocr.traverse (m, *cvt, "value", "value");
-          }
-          else
-          {
-            object_columns_references ocr (e_, os_, name, "value");
-            string const& value_name (column_name (m, "value", "value"));
-            ocr.traverse_column (m, value_name, true);
+            post_statement ();
           }
-
-          tables_.insert (name);
         }
-
-      private:
-        emitter& e_;
-        ostream& os_;
-        relational::tables& tables_;
       };
 
-      struct class_create: relational::class_create, context
+      void create_table::
+      traverse (sema_rel::table& t)
       {
-        class_create (base const& x): base (x) {}
-
-        virtual void
-        traverse (type& c)
+        if (pass_ == 1)
         {
-          if (pass_ != 2)
-          {
-            base::traverse (c);
-            return;
-          }
+          tables_.insert (t.name ()); // Add it before to cover self-refs.
+          base::traverse (t);
 
-          if (c.file () != unit.file ())
-            return;
-
-          if (!object (c) || abstract (c))
-            return;
-
-          string const& name (table_name (c));
+          // Create the sequence and trigger if we have auto primary key.
+          //
+          using sema_rel::primary_key;
 
-          if (tables_[pass_].count (name))
-            return;
+          sema_rel::table::names_iterator i (t.find ("")); // Special name.
 
-          semantics::data_member* id (id_member (c));
+          primary_key* pk (i != t.names_end ()
+                           ? &dynamic_cast<primary_key&> (i->nameable ())
+                           : 0);
 
-          if (id->count ("auto"))
+          if (pk != 0 && pk->auto_ ())
           {
-            string seq_name (quote_id (name + "_seq"));
+            string const& tname (t.name ());
+            string const& cname (pk->contains_begin ()->column ().name ());
 
+            string seq_name (tname + "_seq");
+            string trg_name (tname + "_trg");
+
+            // Sequence.
+            //
             pre_statement ();
 
-            os_ << "CREATE SEQUENCE " << seq_name << endl
-                << "  START WITH 1 INCREMENT BY 1" << endl
-                << endl;
+            os_ << "CREATE SEQUENCE " << quote_id (seq_name) << endl
+                << "  START WITH 1 INCREMENT BY 1" << endl;
 
             post_statement ();
 
+            // Trigger.
+            //
             pre_statement ();
 
-            os_ << "CREATE TRIGGER " <<
-              quote_id (name + "_trig") << endl
-                << "  BEFORE INSERT ON " << quote_id (name) << endl
+            os_ << "CREATE TRIGGER " << quote_id (trg_name) << endl
+                << "  BEFORE INSERT ON " << quote_id (tname) << endl
                 << "  FOR EACH ROW" << endl
                 << "BEGIN" << endl
-                << "  SELECT " << seq_name << ".nextval INTO :new." <<
-              column_qname (*id) << " FROM DUAL;" << endl
+                << "  SELECT " << quote_id (seq_name) << ".nextval " <<
+              "INTO :new." << quote_id (cname) << " FROM DUAL;" << endl
                 << "END;" << endl;
 
             post_statement ();
           }
 
-          object_columns_references ocr (e_, os_, name);
-          ocr.traverse (c);
-
-          tables_[pass_].insert (name);
-
-          member_create mc (e_, os_, tables_[pass_]);
-          mc.traverse (c);
+          return;
         }
-      };
-      entry<class_create> class_create_;
+
+        // Add foreign keys.
+        //
+        instance<add_foreign_key> fk (format_, *this);
+        trav_rel::names n (*fk);
+        names (t, n);
+      }
     }
   }
 }