From d31e96535e3f41c36646f375680d7a4efc5772b2 Mon Sep 17 00:00:00 2001 From: Boris Kolpackov Date: Wed, 3 Jul 2013 11:59:08 +0200 Subject: Test MySQL sub-second precision support --- boost/mysql/date-time/driver.cxx | 47 +++++++++++++++++++++++++++++++++++----- boost/mysql/date-time/makefile | 4 ++-- boost/mysql/date-time/test.hxx | 17 +++++++++++++++ mysql/types/driver.cxx | 29 +++++++++++++++++++++++++ mysql/types/test.hxx | 27 ++++++++++++++++++++--- mysql/types/traits.hxx | 3 ++- qt/mysql/date-time/driver.cxx | 34 ++++++++++++++++++++++++----- qt/mysql/date-time/makefile | 4 ++-- qt/mysql/date-time/test.hxx | 17 +++++++++++++++ 9 files changed, 164 insertions(+), 18 deletions(-) diff --git a/boost/mysql/date-time/driver.cxx b/boost/mysql/date-time/driver.cxx index 49d94b7..6ffe051 100644 --- a/boost/mysql/date-time/driver.cxx +++ b/boost/mysql/date-time/driver.cxx @@ -37,6 +37,35 @@ main (int argc, char* argv[]) { auto_ptr db (create_database (argc, argv)); + mysql_version v; + { + transaction t (db->begin ()); + db->query ().begin ().load (v); + t.commit (); + } + + // If we are running against MySQL 5.6.4 or later alter the tables + // to allow sub-second precision. + // + bool fs (v.major > 5 || + (v.major == 5 && (v.minor > 6 || + (v.minor == 6 && v.release >= 4)))); + if (fs) + { + transaction t (db->begin ()); + + db->execute ("ALTER TABLE `boost_mysql_dt_object_durations`" \ + " MODIFY COLUMN `value` TIME(6)"); + + db->execute ("ALTER TABLE `boost_mysql_dt_object_times`" \ + " MODIFY COLUMN `value` DATETIME(6)"); + + db->execute ("ALTER TABLE `boost_mysql_dt_object_timestamps`" \ + " MODIFY COLUMN `value` TIMESTAMP(6) NULL"); + + t.commit (); + } + object o; // Test all valid date-time mappings. @@ -46,13 +75,16 @@ main (int argc, char* argv[]) o.dates.push_back (date (max_date_time)); o.dates.push_back (date (min_date_time)); - o.times.push_back (second_clock::local_time ()); + if (fs) + o.times.push_back (microsec_clock::local_time ()); + else + o.times.push_back (second_clock::local_time ()); o.times.push_back (not_a_date_time); o.times.push_back (min_date_time); - // MySQL time interface does not support fraction seconds. Construct - // with zero fractional seconds so that comparison test does not - // fail for invalid reasons. + // MySQL prior to 5.6.4 does not support fraction seconds. Construct + // with zero fractional seconds so that comparison test does not fail + // for invalid reasons. // o.times.push_back ( ptime ( @@ -62,10 +94,15 @@ main (int argc, char* argv[]) ptime (max_date_time).time_of_day ().minutes (), ptime (max_date_time).time_of_day ().seconds ()))); - o.timestamps.push_back (second_clock::local_time ()); + if (fs) + o.timestamps.push_back (microsec_clock::local_time ()); + else + o.timestamps.push_back (second_clock::local_time ()); o.timestamps.push_back (not_a_date_time); o.durations.push_back (time_duration (1, 2, 3)); + if (fs) + o.durations.back () += time_duration (microseconds (123456)); o.durations.push_back (time_duration (-1, 2, 3)); o.durations.push_back (not_a_date_time); diff --git a/boost/mysql/date-time/makefile b/boost/mysql/date-time/makefile index aaff422..c6c60fd 100644 --- a/boost/mysql/date-time/makefile +++ b/boost/mysql/date-time/makefile @@ -41,8 +41,8 @@ $(boost.l.cpp-options) $(gen): $(odb) $(gen): odb := $(odb) -$(gen) $(dist): export odb_options += --database mysql --profile boost/date-time \ ---generate-schema --table-prefix boost_mysql_dt_ +$(gen) $(dist): export odb_options += --database mysql --generate-schema \ +--generate-query --profile boost/date-time --table-prefix boost_mysql_dt_ $(gen): cpp_options := -I$(src_base) $(gen): $(common.l.cpp-options) $(odb_boost.l.cpp-options) \ $(boost.l.cpp-options) diff --git a/boost/mysql/date-time/test.hxx b/boost/mysql/date-time/test.hxx index 6244177..253ebb1 100644 --- a/boost/mysql/date-time/test.hxx +++ b/boost/mysql/date-time/test.hxx @@ -46,4 +46,21 @@ struct object std::vector durations; }; +// MySQL server version view. +// +#pragma db view query( \ + "SELECT " \ + "CAST(SUBSTRING_INDEX(@@version, '.', 1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '.', 2), '.', -1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '-', 1), '.', -1) AS UNSIGNED)," \ + "@@protocol_version") +struct mysql_version +{ + unsigned int major; + unsigned int minor; + unsigned int release; + + unsigned int protocol; +}; + #endif // TEST_HXX diff --git a/mysql/types/driver.cxx b/mysql/types/driver.cxx index 07487a2..12610d8 100644 --- a/mysql/types/driver.cxx +++ b/mysql/types/driver.cxx @@ -28,6 +28,16 @@ main (int argc, char* argv[]) { auto_ptr db (create_specific_database (argc, argv)); + mysql_version v; + { + transaction t (db->begin ()); + db->query ().begin ().load (v); + t.commit (); + } + + //cerr << "MySQL " << v.major << '.' << v.minor << '.' << v.release + // << " protocol " << v.protocol << endl; + object o (1); o.bool_ = true; @@ -53,6 +63,25 @@ main (int argc, char* argv[]) o.timestamp_ = date_time (false, 2010, 8, 29, 12, 26, 59); o.year_ = 2010; + // If we are running against MySQL 5.6.4 or later, add fractional + // seconds and also alter the table to allow sub-second precision. + // + if (v.major > 5 || + (v.major == 5 && (v.minor > 6 || + (v.minor == 6 && v.release >= 4)))) + { + o.time_.microseconds = 123456; + o.date_time_.microseconds = 234567; + o.timestamp_.microseconds = 345678; + + transaction t (db->begin ()); + db->execute ("ALTER TABLE `mysql_types_object`" \ + " MODIFY COLUMN `time` TIME(6)," \ + " MODIFY COLUMN `date_time` DATETIME(6)," \ + " MODIFY COLUMN `timestamp` TIMESTAMP(6)"); + t.commit (); + } + string short_str (128, 's'); string medium_str (250, 'm'); string long_str (2040, 'l'); diff --git a/mysql/types/test.hxx b/mysql/types/test.hxx index 73160c2..c46175d 100644 --- a/mysql/types/test.hxx +++ b/mysql/types/test.hxx @@ -27,14 +27,16 @@ struct date_time unsigned int d, unsigned int h, unsigned int min, - unsigned int sec) + unsigned int sec, + unsigned int msec = 0) : negative (n), year (y), month (m), day (d), hour (h), minute (min), - second (sec) + second (sec), + microseconds (msec) { } @@ -48,7 +50,8 @@ struct date_time day == y.day && hour == y.hour && minute == y.minute && - second == y.second; + second == y.second && + microseconds == y.microseconds; } bool negative; @@ -58,6 +61,7 @@ struct date_time unsigned int hour; unsigned int minute; unsigned int second; + unsigned int microseconds; }; struct bitfield @@ -305,4 +309,21 @@ struct char_array } }; +// MySQL server version view. +// +#pragma db view query( \ + "SELECT " \ + "CAST(SUBSTRING_INDEX(@@version, '.', 1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '.', 2), '.', -1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '-', 1), '.', -1) AS UNSIGNED)," \ + "@@protocol_version") +struct mysql_version +{ + unsigned int major; + unsigned int minor; + unsigned int release; + + unsigned int protocol; +}; + #endif // TEST_HXX diff --git a/mysql/types/traits.hxx b/mysql/types/traits.hxx index f1d98b7..53a130c 100644 --- a/mysql/types/traits.hxx +++ b/mysql/types/traits.hxx @@ -35,6 +35,7 @@ namespace odb v.hour = i.hour; v.minute = i.minute; v.second = i.second; + v.microseconds = static_cast (i.second_part); } else v = date_time (); @@ -51,7 +52,7 @@ namespace odb i.hour = v.hour; i.minute = v.minute; i.second = v.second; - i.second_part = 0; + i.second_part = v.microseconds; } }; diff --git a/qt/mysql/date-time/driver.cxx b/qt/mysql/date-time/driver.cxx index 57eab2c..f26879c 100644 --- a/qt/mysql/date-time/driver.cxx +++ b/qt/mysql/date-time/driver.cxx @@ -35,6 +35,29 @@ main (int argc, char* argv[]) { auto_ptr db (create_database (argc, argv)); + mysql_version v; + { + transaction t (db->begin ()); + db->query ().begin ().load (v); + t.commit (); + } + + // If we are running against MySQL 5.6.4 or later alter the tables + // to allow sub-second precision. + // + bool fs (v.major > 5 || + (v.major == 5 && (v.minor > 6 || + (v.minor == 6 && v.release >= 4)))); + if (fs) + { + transaction t (db->begin ()); + db->execute ("ALTER TABLE `qt_mysql_dt_object`" \ + " MODIFY COLUMN `date_time` DATETIME(3)," \ + " MODIFY COLUMN `timestamp` TIMESTAMP(3) NULL," \ + " MODIFY COLUMN `time` TIME(3)"); + t.commit (); + } + // // Check valid dates and times. // @@ -61,14 +84,15 @@ main (int argc, char* argv[]) // // Create a QDateTime containing the current date and time - // but with the milliseconds zeroed. MySQL does not currently - // support millisecond times. + // but with the milliseconds zeroed. MySQL prior to 5.6.4 + // does not support sub-second prevision. // QDateTime t (QDateTime::currentDateTime ()); - t.setTime (QTime (t.time ().hour (), - t.time ().minute (), - t.time ().second ())); + if (!fs) + t.setTime (QTime (t.time ().hour (), + t.time ().minute (), + t.time ().second ())); o.date = t.date (); o.date_time = t; diff --git a/qt/mysql/date-time/makefile b/qt/mysql/date-time/makefile index 039b952..ded9a6c 100644 --- a/qt/mysql/date-time/makefile +++ b/qt/mysql/date-time/makefile @@ -37,8 +37,8 @@ $(qt_core.l.cpp-options) $(gen): $(odb) $(gen): odb := $(odb) -$(gen) $(dist): export odb_options += --database mysql --profile qt/date-time \ ---generate-schema --table-prefix qt_mysql_dt_ +$(gen) $(dist): export odb_options += --database mysql --generate-schema \ +--generate-query --profile qt/date-time --table-prefix qt_mysql_dt_ $(gen): cpp_options := -I$(src_base) $(gen): $(common.l.cpp-options) $(odb_qt.l.cpp-options) \ $(qt_core.l.cpp-options) diff --git a/qt/mysql/date-time/test.hxx b/qt/mysql/date-time/test.hxx index 7e58376..2570285 100644 --- a/qt/mysql/date-time/test.hxx +++ b/qt/mysql/date-time/test.hxx @@ -51,4 +51,21 @@ struct object QTime time; }; +// MySQL server version view. +// +#pragma db view query( \ + "SELECT " \ + "CAST(SUBSTRING_INDEX(@@version, '.', 1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '.', 2), '.', -1) AS UNSIGNED)," \ + "CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(@@version, '-', 1), '.', -1) AS UNSIGNED)," \ + "@@protocol_version") +struct mysql_version +{ + unsigned int major; + unsigned int minor; + unsigned int release; + + unsigned int protocol; +}; + #endif // TEST_HXX -- cgit v1.1