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, 216 insertions, 243 deletions

diff --git a/odb/relational/pgsql/context.cxx b/odb/relational/pgsql/context.cxx
index 28a6cbd..c17b41f 100644
--- a/odb/relational/pgsql/context.cxx
+++ b/odb/relational/pgsql/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_ = true;
-      data_->need_alias_as_ = true;
+      generate_grow = true;
+      need_alias_as = true;
       data_->bind_vector_ = "pgsql::bind*";
       data_->truncated_vector_ = "bool*";
 
@@ -232,9 +235,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)
     {
@@ -243,18 +243,30 @@ namespace relational
                   : "pgsql-" + 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:
@@ -368,11 +380,8 @@ namespace relational
                 }
                 else if (id == "TIMETZ")
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: PostgreSQL time zones are not currently "
-                       << "supported" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "PostgreSQL time zones are not currently supported");
                 }
                 else if (id == "TIMESTAMP")
                 {
@@ -380,11 +389,8 @@ namespace relational
                 }
                 else if (id == "TIMESTAMPTZ")
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: PostgreSQL time zones are not currently "
-                       << "supported" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "PostgreSQL time zones are not currently supported");
                 }
                 //
                 // String and binary types.
@@ -450,16 +456,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 PostgreSQL type '" <<
-                    t.identifier () << "'" << endl;
+                {
+                  throw invalid_sql_type (
+                    "unknown PostgreSQL type '" + t.identifier () + "'");
+                }
                 else
-                  cerr << " error: expected PostgreSQL type name" << endl;
-
-                throw operation_failed ();
+                  throw invalid_sql_type ("expected PostgreSQL type name");
               }
 
               // Fall through.
@@ -474,11 +477,8 @@ namespace relational
 
                 if (t.type () != sql_token::t_int_lit)
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: integer range expected in PostgreSQL type "
-                       << "declaration" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "integer range expected in PostgreSQL type declaration");
                 }
 
                 unsigned int v;
@@ -486,11 +486,9 @@ namespace relational
 
                 if (!(is >> v && is.eof ()))
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: invalid range value '" << t.literal ()
-                       << "'in PostgreSQL type declaration" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "invalid range value '" + t.literal () + "' in PostgreSQL "
+                    "type declaration");
                 }
 
                 r.range = true;
@@ -508,11 +506,8 @@ namespace relational
 
                 if (t.punctuation () != sql_token::p_rparen)
                 {
-                  cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                       << ": error: expected ')' in PostgreSQL type "
-                       << "declaration" << endl;
-
-                  throw operation_failed ();
+                  throw invalid_sql_type (
+                    "expected ')' in PostgreSQL type declaration");
                 }
 
                 s = parse_suffix;
@@ -549,11 +544,9 @@ namespace relational
 
                         if (id3 == "ZONE")
                         {
-                          cerr << m.file () << ":" << m.line () << ":"
-                               << m.column ()<< ": error: PostgreSQL time "
-                               << "zones are not currently supported" << endl;
-
-                          throw operation_failed ();
+                          throw invalid_sql_type (
+                            "PostgreSQL time zones are not currently "
+                            "supported");
                         }
                       }
                     }
@@ -596,10 +589,7 @@ namespace relational
 
         if (r.type == sql_type::invalid)
         {
-          cerr << m.file () << ":" << m.line () << ":" << m.column ()
-               << ": error: incomplete PostgreSQL type declaration" << endl;
-
-          throw operation_failed ();
+          throw invalid_sql_type ("incomplete PostgreSQL type declaration");
         }
 
         // If range is omitted for CHAR or BIT types, it defaults to 1.
@@ -614,11 +604,8 @@ namespace relational
       }
       catch (sql_lexer::invalid_input const& e)
       {
-        cerr << m.file () << ":" << m.line () << ":" << m.column ()
-             << ": error: invalid PostgreSQL type declaration: " << e.message
-             << endl;
-
-        throw operation_failed ();
+        throw invalid_sql_type (
+          "invalid PostgreSQL type declaration: " + e.message);
       }
     }
   }
diff --git a/odb/relational/pgsql/context.hxx b/odb/relational/pgsql/context.hxx
index a4fbc00..cab7095 100644
--- a/odb/relational/pgsql/context.hxx
+++ b/odb/relational/pgsql/context.hxx
@@ -73,6 +73,21 @@ 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&);
+
     protected:
       virtual bool
       grow_impl (semantics::class_&);
@@ -92,7 +107,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/pgsql/model.cxx b/odb/relational/pgsql/model.cxx
new file mode 100644
index 0000000..83ab289
--- /dev/null
+++ b/odb/relational/pgsql/model.cxx
@@ -0,0 +1,71 @@
+// file      : odb/relational/pgsql/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/pgsql/common.hxx>
+#include <odb/relational/pgsql/context.hxx>
+
+using namespace std;
+
+namespace relational
+{
+  namespace pgsql
+  {
+    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)
+        {
+          return v ? "TRUE" : "FALSE";
+        }
+
+        virtual string
+        default_enum (semantics::data_member& m, tree en, string const&)
+        {
+          // Make sure the column is mapped to an integer type.
+          //
+          switch (column_sql_type (m).type)
+          {
+          case sql_type::SMALLINT:
+          case sql_type::INTEGER:
+          case sql_type::BIGINT:
+            break;
+          default:
+            {
+              cerr << m.file () << ":" << m.line () << ":" << m.column ()
+                   << ": error: column with default value specified as C++ "
+                   << "enumerator must map to PostgreSQL integer type" << 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 ();
+        }
+      };
+      entry<object_columns> object_columns_;
+    }
+  }
+}
diff --git a/odb/relational/pgsql/schema.cxx b/odb/relational/pgsql/schema.cxx
index 450d056..c723818 100644
--- a/odb/relational/pgsql/schema.cxx
+++ b/odb/relational/pgsql/schema.cxx
@@ -3,11 +3,15 @@
 // copyright : Copyright (c) 2009-2011 Code Synthesis Tools CC
 // license   : GNU GPL v3; see accompanying LICENSE file
 
+#include <set>
+
 #include <odb/relational/schema.hxx>
 
 #include <odb/relational/pgsql/common.hxx>
 #include <odb/relational/pgsql/context.hxx>
 
+using namespace std;
+
 namespace relational
 {
   namespace pgsql
@@ -20,42 +24,50 @@ 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)
         {
-          os << "DROP TABLE IF EXISTS " << quote_id (table) << " CASCADE"
-             << endl;
+          os << "DROP TABLE IF EXISTS " << quote_id (table) <<
+            " CASCADE" << 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) {}
+
+        void
+        traverse (sema_rel::table&);
+
+      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
-        type (semantics::data_member& m)
+        type (sema_rel::column& c, bool auto_)
         {
-          if (m.count ("auto"))
+          if (auto_)
           {
-            sql_type const& t (column_sql_type (m));
+            // This should never fail since we have already parsed this.
+            //
+            sql_type const& t (parse_sql_type (c.type ()));
 
             if (t.type == sql_type::INTEGER)
               os << "SERIAL";
@@ -63,7 +75,9 @@ namespace relational
               os << "BIGSERIAL";
             else
             {
-              cerr << m.file () << ":" << m.line () << ":" << m.column ()
+              semantics::node& n (*c.get<semantics::node*> ("cxx-node"));
+
+              cerr << n.file () << ":" << n.line () << ":" << n.column ()
                    << ": error: automatically assigned object id must map "
                    << "to PostgreSQL INTEGER or BIGINT" << endl;
 
@@ -71,204 +85,87 @@ namespace relational
             }
           }
           else
-          {
-            base::type (m);
-          }
+            base::type (c, auto_);
         }
+      };
+      entry<create_column> create_column_;
 
-        virtual void
-        default_bool (semantics::data_member&, bool v)
+      struct create_foreign_key: relational::create_foreign_key, context
+      {
+        create_foreign_key (schema_format f, relational::create_table& ct)
+            : base (f, ct)
         {
-          os << " DEFAULT " << (v ? "TRUE" : "FALSE");
         }
 
+        create_foreign_key (base const& x): base (x) {}
+
         virtual void
-        default_enum (semantics::data_member& m, tree en, string const&)
+        traverse (sema_rel::foreign_key& fk)
         {
-          // Make sure the column is mapped to an integer type.
+          // 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).
           //
-          switch (column_sql_type (m).type)
-          {
-          case sql_type::SMALLINT:
-          case sql_type::INTEGER:
-          case sql_type::BIGINT:
-            break;
-          default:
-            {
-              cerr << m.file () << ":" << m.line () << ":" << m.column ()
-                   << ": error: column with default value specified as C++ "
-                   << "enumerator must map to PostgreSQL integer type" << endl;
+          create_table& ct (static_cast<create_table&> (create_table_));
 
-              throw operation_failed ();
-            }
+          if (ct.tables_.find (fk.referenced_table ()) != ct.tables_.end ())
+          {
+            base::traverse (fk);
+            fk.set ("pgsql-fk-defined", true); // Mark it as defined.
           }
-
-          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&)
+        deferred ()
         {
+          os << endl
+             << "    INITIALLY DEFERRED";
         }
       };
-      entry<object_columns> object_columns_;
+      entry<create_foreign_key> create_foreign_key_;
 
-      struct object_columns_references:
-        object_columns_base, relational::common, context
+      struct add_foreign_key: create_foreign_key, relational::common
       {
-        object_columns_references (emitter& e,
-                                   ostream& os,
-                                   string const& table,
-                                   string const& prefix = string ())
-            : relational::common (e, os),
-              table_ (table),
-              prefix_ (prefix)
+        add_foreign_key (schema_format f, relational::create_table& ct)
+            : create_foreign_key (f, ct), common (ct.emitter (), ct.stream ())
         {
         }
 
-        virtual bool
-        traverse_column (semantics::data_member& m, string const& name, bool)
+        virtual void
+        traverse (sema_rel::foreign_key& fk)
         {
-          if (inverse (m))
-            return false;
-
-          if (semantics::class_* c =
-              object_pointer (member_utype (m, prefix_)))
-          {
-            pre_statement ();
-
-            os << "ALTER TABLE " << quote_id (table_) << endl
-               << "  ADD FOREIGN KEY (" << quote_id (name) << ")" << endl
-               << "  REFERENCES " << table_qname (*c) << endl
-               << "  INITIALLY DEFERRED" << endl;
-
-            post_statement ();
-          }
-          else if (prefix_ == "id")
+          if (!fk.count ("pgsql-fk-defined"))
           {
-            semantics::class_& c (*context::top_object);
+            sema_rel::table& t (dynamic_cast<sema_rel::table&> (fk.scope ()));
 
             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;
+            os << "ALTER TABLE " << quote_id (t.name ()) << " ADD" << endl;
+            base::create (fk);
+            os << endl;
 
             post_statement ();
           }
-
-          return true;
         }
-
-      private:
-        string table_;
-        string prefix_;
       };
 
-      struct member_create: object_members_base, context
+      void create_table::
+      traverse (sema_rel::table& t)
       {
-        member_create (emitter& e, ostream& os, relational::tables& tables)
-            : object_members_base (false, true, false),
-              e_ (e),
-              os_ (os),
-              tables_ (tables)
+        if (pass_ == 1)
         {
+          tables_.insert (t.name ()); // Add it before to cover self-refs.
+          base::traverse (t);
+          return;
         }
 
-        virtual void
-        traverse_container (semantics::data_member& m, semantics::type& t)
-        {
-          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;
-
-          type& vt (container_vt (t));
-
-          // 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);
-          }
-
-          // 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);
-          }
-
-          tables_.insert (name);
-        }
-
-      private:
-        emitter& e_;
-        ostream& os_;
-        relational::tables& tables_;
-      };
-
-      struct class_create: relational::class_create
-      {
-        class_create (base const& x): base (x) {}
-
-        virtual void
-        traverse (type& c)
-        {
-          if (pass_ != 2)
-          {
-            base::traverse (c);
-            return;
-          }
-
-          if (c.file () != unit.file ())
-            return;
-
-          if (!object (c) || abstract (c))
-            return;
-
-          string const& name (table_name (c));
-
-          if (tables_[pass_].count (name))
-            return;
-
-          object_columns_references ocr (e_, os_, name);
-          ocr.traverse (c);
-
-          tables_[pass_].insert (name);
-
-          member_create mc (e_, os_, tables_[pass_]);
-          mc.traverse (c);
-        }
-      };
-      entry<class_create> class_create_;
+        // Add foreign keys.
+        //
+        instance<add_foreign_key> fk (format_, *this);
+        trav_rel::names n (*fk);
+        names (t, n);
+      }
     }
   }
 }