Test MySQL sub-second precision support

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<database> db (create_database (argc, argv));
 
+    mysql_version v;
+    {
+      transaction t (db->begin ());
+      db->query<mysql_version> ().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<boost::posix_time::time_duration> 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<database> db (create_specific_database<database> (argc, argv));
 
+    mysql_version v;
+    {
+      transaction t (db->begin ());
+      db->query<mysql_version> ().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<unsigned int> (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<database> db (create_database (argc, argv));
 
+    mysql_version v;
+    {
+      transaction t (db->begin ());
+      db->query<mysql_version> ().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