Add support for schema version table

8 files changed, 440 insertions, 10 deletions

diff --git a/odb/relational/mssql/schema.cxx b/odb/relational/mssql/schema.cxx
index 3528de9..c61bbc2 100644
--- a/odb/relational/mssql/schema.cxx
+++ b/odb/relational/mssql/schema.cxx
@@ -36,6 +36,28 @@ namespace relational
       entry<sql_emitter> sql_emitter_;
 
       //
+      // File.
+      //
+
+      struct sql_file: relational::sql_file, context
+      {
+        sql_file (const base& x): base (x) {}
+
+        virtual void
+        prologue ()
+        {
+          // Suppress the (x rows affected) messages from sqlcmd for DML
+          // statements. We only use DML for schema version management.
+          //
+          if ((model == 0 || model->version () != 0) &&
+              !options.suppress_schema_version ())
+            os << "SET NOCOUNT ON;" << endl
+               << endl;
+        }
+      };
+      entry<sql_file> sql_file_;
+
+      //
       // Drop.
       //
 
@@ -565,6 +587,45 @@ namespace relational
         }
       };
       entry<alter_table_post> alter_table_post_;
+
+      //
+      // Schema version table.
+      //
+
+      struct version_table: relational::version_table, context
+      {
+        version_table (base const& x): base (x) {}
+
+        virtual void
+        create_table ()
+        {
+          pre_statement ();
+
+          os << "IF OBJECT_ID(" << quote_string (table_.string ()) <<
+            ", " << quote_string ("U") << ") IS NULL" << endl
+             << "  CREATE TABLE " << qt_ << " (" << endl
+             << "    " << qn_ << " VARCHAR(256) NOT NULL PRIMARY KEY," << endl
+             << "    " << qv_ << " BIGINT NOT NULL," << endl
+             << "    " << qm_ << " BIT NOT NULL)" << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        create (sema_rel::version v)
+        {
+          pre_statement ();
+
+          os << "IF NOT EXISTS (SELECT 1 FROM " << qt_ << " WHERE " << qn_ <<
+            " = " << qs_ << ")" << endl
+             << "  INSERT INTO " << qt_ << " (" << endl
+             << "    " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+             << "    VALUES (" << qs_ << ", " << v << ", 0)" << endl;
+
+          post_statement ();
+        }
+      };
+      entry<version_table> version_table_;
     }
   }
 }
diff --git a/odb/relational/mysql/schema.cxx b/odb/relational/mysql/schema.cxx
index 8e71f92..c44b796 100644
--- a/odb/relational/mysql/schema.cxx
+++ b/odb/relational/mysql/schema.cxx
@@ -301,15 +301,11 @@ namespace relational
         virtual void
         create_post ()
         {
-          os << ")";
+          os << ")" << endl;
 
           string const& engine (options.mysql_engine ());
-
           if (engine != "default")
-            os << endl
-               << " ENGINE=" << engine;
-
-          os << endl;
+            os << " ENGINE=" << engine << endl;
         }
       };
       entry<create_table> create_table_;
@@ -442,6 +438,45 @@ namespace relational
         }
       };
       entry<alter_table_post> alter_table_post_;
+
+      //
+      // Schema version table.
+      //
+
+      struct version_table: relational::version_table, context
+      {
+        version_table (base const& x): base (x) {}
+
+        virtual void
+        create_table ()
+        {
+          pre_statement ();
+
+          os << "CREATE TABLE IF NOT EXISTS " << qt_ << " (" << endl
+             << "  " << qn_ << " VARCHAR(255) NOT NULL PRIMARY KEY," << endl
+             << "  " << qv_ << " BIGINT UNSIGNED NOT NULL," << endl
+             << "  " << qm_ << " TINYINT(1) NOT NULL)" << endl;
+
+          string const& engine (options.mysql_engine ());
+          if (engine != "default")
+            os << " ENGINE=" << engine << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        create (sema_rel::version v)
+        {
+          pre_statement ();
+
+          os << "INSERT IGNORE INTO " << qt_ << " (" << endl
+             << "  " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+             << "  VALUES (" << qs_ << ", " << v << ", 0)" << endl;
+
+          post_statement ();
+        }
+      };
+      entry<version_table> version_table_;
     }
   }
 }
diff --git a/odb/relational/oracle/schema.cxx b/odb/relational/oracle/schema.cxx
index a49ace0..03da322 100644
--- a/odb/relational/oracle/schema.cxx
+++ b/odb/relational/oracle/schema.cxx
@@ -460,6 +460,56 @@ namespace relational
         }
       };
       entry<alter_table_post> alter_table_post_;
+
+      //
+      // Schema version table.
+      //
+
+      struct version_table: relational::version_table, context
+      {
+        version_table (base const& x)
+            : base (x)
+        {
+          // If the schema name is empty, replace it with a single space
+          // to workaround the VARCHAR2 empty/NULL issue.
+          //
+          if (qs_ == "''")
+            qs_ = "' '";
+        }
+
+        virtual void
+        create_table ()
+        {
+          pre_statement ();
+
+          os << "BEGIN" << endl
+             << "  EXECUTE IMMEDIATE 'CREATE TABLE " << qt_ << " (" << endl
+             << "    " << qn_ << " VARCHAR2(512) NOT NULL PRIMARY KEY," << endl
+             << "    " << qv_ << " NUMBER(20) NOT NULL," << endl
+             << "    " << qm_ << " NUMBER(1) NOT NULL)';" << endl
+             << "EXCEPTION" << endl
+             << "  WHEN OTHERS THEN" << endl
+             << "    IF SQLCODE != -955 THEN RAISE; END IF;" << endl
+             << "END;" << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        create (sema_rel::version v)
+        {
+          pre_statement ();
+
+          os << "MERGE INTO " << qt_ << " USING DUAL ON (" << qn_ << " = " <<
+            qs_ << ")" << endl
+             << "  WHEN NOT MATCHED THEN INSERT (" << endl
+             << "    " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+             << "    VALUES (" << qs_ << ", " << v << ", 0)" << endl;
+
+          post_statement ();
+        }
+      };
+      entry<version_table> version_table_;
     }
   }
 }
diff --git a/odb/relational/pgsql/schema.cxx b/odb/relational/pgsql/schema.cxx
index 77216af..f127b95 100644
--- a/odb/relational/pgsql/schema.cxx
+++ b/odb/relational/pgsql/schema.cxx
@@ -150,6 +150,109 @@ namespace relational
         }
       };
       entry<alter_column> alter_column_;
+
+      //
+      // Schema version table.
+      //
+
+      struct version_table: relational::version_table, context
+      {
+        version_table (base const& x): base (x) {}
+
+        // PostgreSQL prior to 9.1 doesn't support IF NOT EXISTS in
+        // CREATE TABLE. We also cannot use IF-ELSE construct in plain
+        // SQL. To make it at least work for a single schema, we are
+        // going to drop the schema version table after the DROP
+        // statements and then unconditionally create it after CREATE.
+        //
+        virtual void
+        create_table ()
+        {
+          if (options.pgsql_server_version () >= pgsql_version (9, 1))
+          {
+            pre_statement ();
+
+            os << "CREATE TABLE IF NOT EXISTS " << qt_ << " (" << endl
+               << "  " << qn_ << " TEXT NOT NULL PRIMARY KEY," << endl
+               << "  " << qv_ << " BIGINT NOT NULL," << endl
+               << "  " << qm_ << " BOOLEAN NOT NULL)" << endl;
+
+            post_statement ();
+          }
+        }
+
+        virtual void
+        drop ()
+        {
+          pre_statement ();
+
+          if (options.pgsql_server_version () >= pgsql_version (9, 1))
+            os << "DELETE FROM " << qt_ << endl
+               << "  WHERE " << qn_ << " = " << qs_ << endl;
+          else
+            os << "DROP TABLE IF EXISTS " << qt_ << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        create (sema_rel::version v)
+        {
+          pre_statement ();
+
+          if (options.pgsql_server_version () >= pgsql_version (9, 1))
+          {
+            os << "INSERT INTO " << qt_ << " (" << endl
+               << "  " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+               << "  SELECT " << qs_ << ", " << v << ", FALSE" << endl
+               << "  WHERE NOT EXISTS (" << endl
+               << "    SELECT 1 FROM " << qt_ << " WHERE " << qn_ << " = " <<
+              qs_ << ")" << endl;
+          }
+          else
+          {
+            os << "CREATE TABLE " << qt_ << " (" << endl
+               << "  " << qn_ << " TEXT NOT NULL PRIMARY KEY," << endl
+               << "  " << qv_ << " BIGINT NOT NULL," << endl
+               << "  " << qm_ << " BOOLEAN NOT NULL)" << endl;
+
+            post_statement ();
+            pre_statement ();
+
+            os << "INSERT INTO " << qt_ << " (" << endl
+               << "  " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+               << "  VALUES (" << qs_ << ", " << v << ", FALSE)" << endl;
+          }
+
+          post_statement ();
+        }
+
+        virtual void
+        migrate_pre (sema_rel::version v)
+        {
+          pre_statement ();
+
+          os << "UPDATE " << qt_ << endl
+             << "  SET " << qv_ << " = " << v << ", " << qm_ << " = TRUE" << endl
+             << "  WHERE " << qn_ << " = " << qs_ << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        migrate_post ()
+        {
+          pre_statement ();
+
+          os << "UPDATE " << qt_ << endl
+             << "  SET " << qm_ << " = FALSE" << endl
+             << "  WHERE " << qn_ << " = " << qs_ << endl;
+
+          post_statement ();
+        }
+
+      };
+      entry<version_table> version_table_;
     }
   }
 }
diff --git a/odb/relational/schema-source.cxx b/odb/relational/schema-source.cxx
index d54ae02..54d5076 100644
--- a/odb/relational/schema-source.cxx
+++ b/odb/relational/schema-source.cxx
@@ -21,20 +21,24 @@ namespace relational
       sema_rel::model& model (*ctx.model);
       string const& schema_name (ops.schema_name ()[db]);
 
-      if (log != 0 && log->contains_changeset_empty ())
+      if (log != 0 &&
+          (log->contains_changeset_empty () || ops.suppress_migration ()))
         log = 0;
 
+      bool schema_version (
+        model.version () != 0 && !ctx.options.suppress_schema_version ());
+
       instance<cxx_emitter> emitter;
       emitter_ostream emitter_os (*emitter);
       schema_format format (schema_format::embedded);
 
-      if (!model.names_empty () || log != 0)
+      if (!model.names_empty () || log != 0 || schema_version)
         os << "namespace odb"
            << "{";
 
       // Schema.
       //
-      if (!model.names_empty ())
+      if (!model.names_empty () || schema_version)
       {
         os << "static bool" << endl
            << "create_schema (database& db, unsigned short pass, bool drop)"
@@ -69,6 +73,14 @@ namespace relational
             close = close || !emitter->empty ();
           }
 
+          if (schema_version)
+          {
+            instance<version_table> vt (*emitter, emitter_os, format);
+            vt->create_table ();
+            vt->drop ();
+            close = true;
+          }
+
           if (close) // Close the last case and the switch block.
             os << "return false;"
                << "}"  // case
@@ -106,6 +118,14 @@ namespace relational
             close = close || !emitter->empty ();
           }
 
+          if (schema_version)
+          {
+            instance<version_table> vt (*emitter, emitter_os, format);
+            vt->create_table ();
+            vt->create (model.version ());
+            close = true;
+          }
+
           if (close) // Close the last case and the switch block.
             os << "return false;"
                << "}"  // case
@@ -172,6 +192,13 @@ namespace relational
               close = close || !emitter->empty ();
             }
 
+            if (!ctx.options.suppress_schema_version ())
+            {
+              instance<version_table> vt (*emitter, emitter_os, format);
+              vt->migrate_pre (cs.version ());
+              close = true;
+            }
+
             if (close) // Close the last case and the switch block.
               os << "return false;"
                  << "}"  // case
@@ -208,6 +235,13 @@ namespace relational
               close = close || !emitter->empty ();
             }
 
+            if (!ctx.options.suppress_schema_version ())
+            {
+              instance<version_table> vt (*emitter, emitter_os, format);
+              vt->migrate_post ();
+              close = true;
+            }
+
             if (close) // Close the last case and the switch block.
               os << "return false;"
                  << "}"  // case
@@ -229,7 +263,7 @@ namespace relational
         }
       }
 
-      if (!model.names_empty () || log != 0)
+      if (!model.names_empty () || log != 0 || schema_version)
         os << "}"; // namespace odb
     }
   }
diff --git a/odb/relational/schema.cxx b/odb/relational/schema.cxx
index 2cf1a4c..b7498f6 100644
--- a/odb/relational/schema.cxx
+++ b/odb/relational/schema.cxx
@@ -53,6 +53,14 @@ namespace relational
 
         model->traverse (*ctx.model);
       }
+
+      if (ctx.model->version () != 0 &&
+          !ctx.options.suppress_schema_version ())
+      {
+        instance<version_table> vt (*em, emos, f);
+        vt->create_table ();
+        vt->drop ();
+      }
     }
 
     void
@@ -79,6 +87,17 @@ namespace relational
 
         model->traverse (*ctx.model);
       }
+
+      if (ctx.model->version () != 0 &&
+          !ctx.options.suppress_schema_version ())
+      {
+        instance<version_table> vt (*em, emos, f);
+
+        if (ctx.options.omit_drop ())
+          vt->create_table ();
+
+        vt->create (ctx.model->version ());
+      }
     }
 
     void
@@ -109,6 +128,12 @@ namespace relational
 
         changeset->traverse (cs);
       }
+
+      if (!ctx.options.suppress_schema_version ())
+      {
+        instance<version_table> vt (*em, emos, f);
+        vt->migrate_pre (cs.version ());
+      }
     }
 
     void
@@ -139,6 +164,12 @@ namespace relational
 
         changeset->traverse (cs);
       }
+
+      if (!ctx.options.suppress_schema_version ())
+      {
+        instance<version_table> vt (*em, emos, f);
+        vt->migrate_post ();
+      }
     }
   }
 }
diff --git a/odb/relational/schema.hxx b/odb/relational/schema.hxx
index 1152175..4e0febf 100644
--- a/odb/relational/schema.hxx
+++ b/odb/relational/schema.hxx
@@ -1438,6 +1438,87 @@ namespace relational
     };
 
     //
+    // Schema version table.
+    //
+
+    struct version_table: common
+    {
+      typedef version_table base;
+
+      version_table (emitter_type& e, ostream& os, schema_format f)
+          : common (e, os, f),
+            table_ (options.schema_version_table ()[db]),
+            qt_ (quote_id (table_)),
+            qs_ (quote_string (options.schema_name ()[db])),
+            qn_ (quote_id ("name")),
+            qv_ (quote_id ("version")),
+            qm_ (quote_id ("migration"))
+      {
+      }
+
+      // Create the version table if it doesn't exist.
+      //
+      virtual void
+      create_table () {}
+
+      // Remove the version entry. Called after the DROP statements.
+      //
+      virtual void
+      drop ()
+      {
+        pre_statement ();
+
+        os << "DELETE FROM " << qt_ << endl
+           << "  WHERE " << qn_ << " = " << qs_ << endl;
+
+        post_statement ();
+      }
+
+      // Set the version. Called after the CREATE statements.
+      //
+      virtual void
+      create (sema_rel::version) {}
+
+      // Set the version and migration state to true. Called after
+      // the pre migration statements.
+      //
+      virtual void
+      migrate_pre (sema_rel::version v)
+      {
+        pre_statement ();
+
+        os << "UPDATE " << qt_ << endl
+           << "  SET " << qv_ << " = " << v << ", " << qm_ << " = 1" << endl
+           << "  WHERE " << qn_ << " = " << qs_ << endl;
+
+        post_statement ();
+      }
+
+      // Set migration state to false. Called after the post migration
+      // statements.
+      //
+      virtual void
+      migrate_post ()
+      {
+        pre_statement ();
+
+        os << "UPDATE " << qt_ << endl
+           << "  SET " << qm_ << " = 0" << endl
+           << "  WHERE " << qn_ << " = " << qs_ << endl;
+
+        post_statement ();
+      }
+
+    protected:
+      sema_rel::qname table_;
+      string qt_; // Quoted table.
+      string qs_; // Quoted schema name string.
+      string qn_; // Quoted name column.
+      string qv_; // Quoted version column.
+      string qm_; // Quoted migration column.
+    };
+
+    //
     // SQL output.
     //
 
diff --git a/odb/relational/sqlite/schema.cxx b/odb/relational/sqlite/schema.cxx
index 6c5b525..4a2eef2 100644
--- a/odb/relational/sqlite/schema.cxx
+++ b/odb/relational/sqlite/schema.cxx
@@ -279,6 +279,41 @@ namespace relational
         }
       };
       entry<alter_table_post> alter_table_post_;
+
+      //
+      // Schema version table.
+      //
+
+      struct version_table: relational::version_table, context
+      {
+        version_table (base const& x): base (x) {}
+
+        virtual void
+        create_table ()
+        {
+          pre_statement ();
+
+          os << "CREATE TABLE IF NOT EXISTS " << qt_ << " (" << endl
+             << "  " << qn_ << " TEXT NOT NULL PRIMARY KEY," << endl
+             << "  " << qv_ << " INTEGER NOT NULL," << endl
+             << "  " << qm_ << " INTEGER NOT NULL)" << endl;
+
+          post_statement ();
+        }
+
+        virtual void
+        create (sema_rel::version v)
+        {
+          pre_statement ();
+
+          os << "INSERT OR IGNORE INTO " << qt_ << " (" << endl
+             << "  " << qn_ << ", " << qv_ << ", " << qm_ << ")" << endl
+             << "  VALUES (" << qs_ << ", " << v << ", 0)" << endl;
+
+          post_statement ();
+        }
+      };
+      entry<version_table> version_table_;
     }
   }
 }