Use proper string time format for SQLite

6 files changed, 81 insertions, 10 deletions

diff --git a/include/urweb.h b/include/urweb.h
index 9314d089..4c86e7ec 100644
--- a/include/urweb.h
+++ b/include/urweb.h
@@ -295,4 +295,8 @@ extern int uw_time_max, uw_supports_direct_status, uw_min_heap;
 
 failure_kind uw_runCallback(uw_context, void (*callback)(uw_context));
 
+uw_Basis_string uw_Basis_timeToStringf(uw_context, const char *fmt, uw_Basis_time);
+uw_Basis_time uw_Basis_stringToTimef(uw_context, const char *fmt, uw_Basis_string);
+uw_Basis_time uw_Basis_stringToTimef_error(uw_context, const char *fmt, uw_Basis_string);
+
 #endif
diff --git a/src/c/urweb.c b/src/c/urweb.c
index 0356e0fa..5b109ec2 100644
--- a/src/c/urweb.c
+++ b/src/c/urweb.c
@@ -2055,6 +2055,7 @@ uw_unit uw_Basis_htmlifyBool_w(uw_context ctx, uw_Basis_bool b) {
 
 #define TIME_FMT "%x %X"
 #define TIME_FMT_PG "%Y-%m-%d %T"
+#define TIME_FMT_PG "%Y-%m-%d %T"
 
 uw_Basis_string uw_Basis_htmlifyTime(uw_context ctx, uw_Basis_time t) {
   size_t len;
@@ -2560,6 +2561,21 @@ uw_Basis_string uw_Basis_timeToString(uw_context ctx, uw_Basis_time t) {
     return "<Invalid time>";
 }
 
+uw_Basis_string uw_Basis_timeToStringf(uw_context ctx, const char *fmt, uw_Basis_time t) {
+  size_t len;
+  char *r;
+  struct tm stm;
+
+  if (localtime_r(&t, &stm)) {
+    uw_check_heap(ctx, TIMES_MAX);
+    r = ctx->heap.front;
+    len = strftime(r, TIMES_MAX, fmt, &stm);
+    ctx->heap.front += len+1;
+    return r;
+  } else
+    return "<Invalid time>";
+}
+
 uw_Basis_int *uw_Basis_stringToInt(uw_context ctx, uw_Basis_string s) {
   char *endptr;
   uw_Basis_int n = strtoll(s, &endptr, 10);
@@ -2643,6 +2659,19 @@ uw_Basis_time *uw_Basis_stringToTime(uw_context ctx, uw_Basis_string s) {
   }
 }
 
+uw_Basis_time *uw_Basis_stringToTimef(uw_context ctx, const char *fmt, uw_Basis_string s) {
+  char *end = strchr(s, 0);
+  struct tm stm;
+
+  if (strptime(s, fmt, &stm) == end) {
+    uw_Basis_time *r = uw_malloc(ctx, sizeof(uw_Basis_time));
+    *r = mktime(&stm);
+    return r;
+  }
+  else
+    return NULL;
+}
+
 uw_Basis_int uw_Basis_stringToInt_error(uw_context ctx, uw_Basis_string s) {
   char *endptr;
   uw_Basis_int n = strtoll(s, &endptr, 10);
@@ -2704,7 +2733,7 @@ uw_Basis_bool uw_Basis_stringToBool_error(uw_context ctx, uw_Basis_string s) {
     uw_error(ctx, FATAL, "Can't parse bool: %s", s);
 }
 
-uw_Basis_time uw_Basis_stringToTime_error(uw_context ctx, uw_Basis_string s) {
+uw_Basis_time uw_Basis_unsqlTime(uw_context ctx, uw_Basis_string s) {
   char *dot = strchr(s, '.'), *end = strchr(s, 0);
   struct tm stm = {};
 
@@ -2720,16 +2749,16 @@ uw_Basis_time uw_Basis_stringToTime_error(uw_context ctx, uw_Basis_string s) {
     }
   }
   else {
-    if (strptime(s, TIME_FMT_PG, &stm) == end)
+    if (strptime(s, TIME_FMT_PG, &stm) == end) {
       return mktime(&stm);
-    else if (strptime(s, TIME_FMT, &stm) == end)
+    } else if (strptime(s, TIME_FMT, &stm) == end) {
       return mktime(&stm);
-    else
+    } else
       uw_error(ctx, FATAL, "Can't parse time: %s", s);
   }
 }
 
-uw_Basis_time uw_Basis_unsqlTime(uw_context ctx, uw_Basis_string s) {
+uw_Basis_time uw_Basis_stringToTime_error(uw_context ctx, uw_Basis_string s) {
   char *dot = strchr(s, '.'), *end = strchr(s, 0);
   struct tm stm = {};
 
@@ -2745,15 +2774,25 @@ uw_Basis_time uw_Basis_unsqlTime(uw_context ctx, uw_Basis_string s) {
     }
   }
   else {
-    if (strptime(s, TIME_FMT_PG, &stm) == end) {
+    if (strptime(s, TIME_FMT_PG, &stm) == end)
       return mktime(&stm);
-    } else if (strptime(s, TIME_FMT, &stm) == end) {
+    else if (strptime(s, TIME_FMT, &stm) == end)
       return mktime(&stm);
-    } else
+    else
       uw_error(ctx, FATAL, "Can't parse time: %s", s);
   }
 }
 
+uw_Basis_time uw_Basis_stringToTimef_error(uw_context ctx, const char *fmt, uw_Basis_string s) {
+  char *end = strchr(s, 0);
+  struct tm stm = {};
+
+  if (strptime(s, fmt, &stm) == end)
+    return mktime(&stm);
+  else
+    uw_error(ctx, FATAL, "Can't parse time: %s", s);
+}
+
 uw_Basis_blob uw_Basis_stringToBlob_error(uw_context ctx, uw_Basis_string s, size_t len) {
   char *r = ctx->heap.front;
   uw_Basis_blob b = {len, r};
diff --git a/src/sqlite.sml b/src/sqlite.sml
index c87bd509..e668f53a 100644
--- a/src/sqlite.sml
+++ b/src/sqlite.sml
@@ -402,6 +402,8 @@ fun init {dbstring, prepared = ss, tables, views, sequences} =
              newline]
     end
 
+val fmt = "\"%Y-%m-%d %H:%M:%S\""
+
 fun p_getcol {loc, wontLeakStrings, col = i, typ = t} =
     let
         fun p_unsql t =
@@ -415,7 +417,11 @@ fun p_getcol {loc, wontLeakStrings, col = i, typ = t} =
                     box [string "uw_strdup(ctx, (uw_Basis_string)sqlite3_column_text(stmt, ", string (Int.toString i), string "))"]
               | Char => box [string "sqlite3_column_text(stmt, ", string (Int.toString i), string ")[0]"]
               | Bool => box [string "(uw_Basis_bool)sqlite3_column_int(stmt, ", string (Int.toString i), string ")"]
-              | Time => box [string "uw_Basis_stringToTime_error(ctx, (uw_Basis_string)sqlite3_column_text(stmt, ", string (Int.toString i), string "))"]
+              | Time => box [string "uw_Basis_stringToTimef_error(ctx, ",
+                             string fmt,
+                             string ", (uw_Basis_string)sqlite3_column_text(stmt, ",
+                             string (Int.toString i),
+                             string "))"]
               | Blob => box [string "({",
                              newline,
                              string "char *data = (char *)sqlite3_column_blob(stmt, ",
@@ -591,7 +597,9 @@ fun p_inputs loc =
                                                      string ")"]
                                       | Time => box [string "sqlite3_bind_text(stmt, ",
                                                      string (Int.toString (i + 1)),
-                                                     string ", uw_Basis_attrifyTime(ctx, ",
+                                                     string ", uw_Basis_timeToStringf(ctx, ",
+                                                     string fmt,
+                                                     string ", ",
                                                      arg,
                                                      string "), -1, SQLITE_TRANSIENT)"]
                                       | Blob => box [string "sqlite3_bind_blob(stmt, ",
diff --git a/tests/sqliteTime.ur b/tests/sqliteTime.ur
new file mode 100644
index 00000000..427b5e0f
--- /dev/null
+++ b/tests/sqliteTime.ur
@@ -0,0 +1,14 @@
+table dates : { Date : time }
+
+fun aform () =
+    let
+        val a : time = readError "01/02/03 04:06:07"
+    in
+        dml(INSERT INTO dates (Date) VALUES ({[a]}));
+        ds <- queryX (SELECT * FROM dates)
+                     (fn r => <xml>{[r.Dates.Date]}<br/></xml>);
+        return <xml><body>{ds}</body></xml>
+    end
+
+fun main () =
+  return <xml><body><form><submit action={aform}/></form></body></xml>
diff --git a/tests/sqliteTime.urp b/tests/sqliteTime.urp
new file mode 100644
index 00000000..9275bf48
--- /dev/null
+++ b/tests/sqliteTime.urp
@@ -0,0 +1,5 @@
+dbms sqlite
+database /tmp/test
+sql sqliteTime.sql
+
+sqliteTime
diff --git a/tests/sqliteTime.urs b/tests/sqliteTime.urs
new file mode 100644
index 00000000..6ac44e0b
--- /dev/null
+++ b/tests/sqliteTime.urs
@@ -0,0 +1 @@
+val main : unit -> transaction page