From 8554cd89897f9cbd1705592cf0318b3ef4e42665 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Thu, 25 Apr 2013 07:35:45 +0200 Subject: Add support for schema version table --- odb/option-functions.cxx | 5 ++ odb/option-types.cxx | 42 +++++++++++++++ odb/option-types.hxx | 72 +++++++++++++++++++++++++ odb/options.cli | 113 +++++++++++++++++++++++++-------------- odb/relational/mssql/schema.cxx | 61 +++++++++++++++++++++ odb/relational/mysql/schema.cxx | 47 +++++++++++++--- odb/relational/oracle/schema.cxx | 50 +++++++++++++++++ odb/relational/pgsql/schema.cxx | 103 +++++++++++++++++++++++++++++++++++ odb/relational/schema-source.cxx | 42 +++++++++++++-- odb/relational/schema.cxx | 31 +++++++++++ odb/relational/schema.hxx | 81 ++++++++++++++++++++++++++++ odb/relational/sqlite/schema.cxx | 35 ++++++++++++ 12 files changed, 632 insertions(+), 50 deletions(-) (limited to 'odb') diff --git a/odb/option-functions.cxx b/odb/option-functions.cxx index 61db175..6c693c1 100644 --- a/odb/option-functions.cxx +++ b/odb/option-functions.cxx @@ -48,6 +48,11 @@ process_options (options& o) } } + // Set default --schema-version-table value. + // + if (o.schema_version_table ().count (db) == 0) + o.schema_version_table ()[db] = "schema_version"; + // Set default --schema-name value. // if (o.schema_name ().count (db) == 0) diff --git a/odb/option-types.cxx b/odb/option-types.cxx index 841c513..405a512 100644 --- a/odb/option-types.cxx +++ b/odb/option-types.cxx @@ -218,6 +218,48 @@ operator>> (istream& is, name_case& v) } // +// pgsql_version +// + +istream& +operator>> (istream& is, pgsql_version& v) +{ + unsigned short major, minor; + + // Extract the major version. + // + is >> major; + + if (!is.fail ()) + { + // Extract the decimal point. + // + char p; + is >> p; + + if (!is.fail () && p == '.') + { + // Extract the minor version. + // + is >> minor; + + if (!is.fail ()) + v = pgsql_version (major, minor); + } + else + is.setstate (istream::failbit); + } + + return is; +} + +ostream& +operator<< (ostream& os, pgsql_version v) +{ + return os << v.ver_major () << '.' << v.ver_minor (); +} + +// // oracle_version // diff --git a/odb/option-types.hxx b/odb/option-types.hxx index 2f8825d..f078d3c 100644 --- a/odb/option-types.hxx +++ b/odb/option-types.hxx @@ -173,6 +173,78 @@ operator>> (std::istream&, name_case&); // // +struct pgsql_version +{ + pgsql_version (unsigned short major, unsigned short minor) + : major_ (major), minor_ (minor) + { + } + + unsigned short + ver_major () const + { + return major_; + } + + unsigned short + ver_minor () const + { + return minor_; + } + +private: + unsigned short major_; + unsigned short minor_; +}; + +inline bool +operator== (const pgsql_version& x, const pgsql_version& y) +{ + return x.ver_major () == y.ver_major (); +} + +inline bool +operator!= (const pgsql_version& x, const pgsql_version& y) +{ + return !(x == y); +} + +inline bool +operator< (const pgsql_version& x, const pgsql_version& y) +{ + return x.ver_major () < y.ver_major () || + (x.ver_major () == y.ver_major () && + x.ver_minor () < y.ver_minor ()); +} + +inline bool +operator> (const pgsql_version& x, const pgsql_version& y) +{ + return x.ver_major () > y.ver_major () || + (x.ver_major () == y.ver_major () && + x.ver_minor () > y.ver_minor ()); +} + +inline bool +operator<= (const pgsql_version& x, const pgsql_version& y) +{ + return !(x > y); +} + +inline bool +operator>= (const pgsql_version& x, const pgsql_version& y) +{ + return !(x < y); +} + +std::istream& +operator>> (std::istream&, pgsql_version&); + +std::ostream& +operator<< (std::ostream&, pgsql_version); + +// +// struct oracle_version { oracle_version (unsigned short major, unsigned short minor) diff --git a/odb/options.cli b/odb/options.cli index 569566d..c3bb908 100644 --- a/odb/options.cli +++ b/odb/options.cli @@ -134,6 +134,24 @@ class options "Suppress the generation of database schema migration statements." }; + bool --suppress-schema-version + { + "Suppress the generation of schema version table. If you specify this + option then you are also expected to manually specify the database + schema version and migration state at runtime using the + \cb{odb::database::schema_version()} function." + }; + + database_map --schema-version-table + { + "", + "Specify the alternative schema version table name instead of the default + \cb{schema_version}. If you specify this option then you are also + expected to manually specify the schema version table name at runtime + using the \cb{odb::database::schema_version_table()} function. The table + name can be qualified." + }; + database_map > --schema-format { "", @@ -926,51 +944,45 @@ class options bool --trace {"Trace the compilation process."}; // - // SQL Server-specific options. + // MySQL-specific options. // - ::mssql_version --mssql-server-version (10, 0) + std::string --mysql-engine = "InnoDB" { - "", - "Specify the minimum SQL Server server version with which the generated - C++ code will be used. This information is used to enable - version-specific optimizations and workarounds in the generated C++ - code. The version must be in the \c{\i{major}\b{.}\i{minor}} form, for - example, \cb{9.0} (SQL Server 2005), \cb{10.5} (2008R2), or \cb{11.0} - (2012). If this option is not specified, then \cb{10.0} (SQL Server 2008) - or later is assumed." + "", + "Use instead of the default \cb{InnoDB} in the generated + database schema file. For more information on the storage engine + options see the MySQL documentation. If you would like to use the + database-default engine, pass \cb{default} as the value for this + option." }; - unsigned int --mssql-short-limit = 1024 + // + // SQLite-specific options. + // + + bool --sqlite-lax-auto-id { - "", - "Specify the short data size limit. If a character, national character, or - binary data type has a maximum length (in bytes) less than or equal to - this limit, then it is treated as \i{short data}, otherwise it is \i{long - data}. For short data ODB pre-allocates an intermediate buffer of - the maximum size and binds it directly to a parameter or result - column. This way the underlying API (ODBC) can read/write directly - from/to this buffer. In the case of long data, the data is read/written - in chunks using the \cb{SQLGetData()}/\cb{SQLPutData()} ODBC functions. - While the long data approach reduces the amount of memory used by the - application, it may require greater CPU resources. The default short - data limit is 1024 bytes. When setting a custom short data limit, make - sure that it is sufficiently large so that no object id in the - application is treated as long data." + "Do not force monotonically increasing automatically-assigned + object ids. In this mode the generated database schema omits the + \cb{AUTOINCREMENT} keyword which results in faster object persistence + but may lead to automatically-assigned ids not being in a strictly + ascending order. Refer to the SQLite documentation for details." }; // - // MySQL-specific options. + // PostgreSQL-specific options. // - std::string --mysql-engine = "InnoDB" + ::pgsql_version --pgsql-server-version (7, 4) { - "", - "Use instead of the default \cb{InnoDB} in the generated - database schema file. For more information on the storage engine - options see the MySQL documentation. If you would like to use the - database-default engine, pass \cb{default} as the value for this - option." + "", + "Specify the minimum PostgreSQL server version with which the generated + C++ code and schema will be used. This information is used to enable + version-specific optimizations and workarounds in the generated C++ + code and schema. The version must be in the \c{\i{major}\b{.}\i{minor}} + form, for example, \cb{9.1}. If this option is not specified, then + \cb{7.4} or later is assumed." }; // @@ -989,15 +1001,36 @@ class options }; // - // SQLite-specific options. + // SQL Server-specific options. // - bool --sqlite-lax-auto-id + ::mssql_version --mssql-server-version (10, 0) { - "Do not force monotonically increasing automatically-assigned - object ids. In this mode the generated database schema omits the - \cb{AUTOINCREMENT} keyword which results in faster object persistence - but may lead to automatically-assigned ids not being in a strictly - ascending order. Refer to the SQLite documentation for details." + "", + "Specify the minimum SQL Server server version with which the generated + C++ code and schema will be used. This information is used to enable + version-specific optimizations and workarounds in the generated C++ + code and schema. The version must be in the \c{\i{major}\b{.}\i{minor}} + form, for example, \cb{9.0} (SQL Server 2005), \cb{10.5} (2008R2), or + \cb{11.0} (2012). If this option is not specified, then \cb{10.0} (SQL + Server 2008) or later is assumed." + }; + + unsigned int --mssql-short-limit = 1024 + { + "", + "Specify the short data size limit. If a character, national character, or + binary data type has a maximum length (in bytes) less than or equal to + this limit, then it is treated as \i{short data}, otherwise it is \i{long + data}. For short data ODB pre-allocates an intermediate buffer of + the maximum size and binds it directly to a parameter or result + column. This way the underlying API (ODBC) can read/write directly + from/to this buffer. In the case of long data, the data is read/written + in chunks using the \cb{SQLGetData()}/\cb{SQLPutData()} ODBC functions. + While the long data approach reduces the amount of memory used by the + application, it may require greater CPU resources. The default short + data limit is 1024 bytes. When setting a custom short data limit, make + sure that it is sufficiently large so that no object id in the + application is treated as long data." }; }; 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_; // + // 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_; + + // // Drop. // @@ -565,6 +587,45 @@ namespace relational } }; entry 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_; } } } 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_; @@ -442,6 +438,45 @@ namespace relational } }; entry 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_; } } } 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_; + + // + // 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_; } } } 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_; + + // + // 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_; } } } 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 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 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 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 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 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 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 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 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 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_; + + // + // 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_; } } } -- cgit v1.1