[libgda] Correctly handle timezone in time and timestamp values
- From: Vivien Malerba <vivien src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [libgda] Correctly handle timezone in time and timestamp values
- Date: Wed, 28 Aug 2013 20:15:59 +0000 (UTC)
commit 587152acfdaa20db8abbb9c36448fe1c0f0691ca
Author: Vivien Malerba <malerba gnome-db org>
Date: Wed Aug 28 22:04:00 2013 +0200
Correctly handle timezone in time and timestamp values
most of the databases store time and timestamp in the GMT timezone,
so the time and timestamp in other timezones need to be converted
to GMT before being used
doc/C/libgda-sections.txt | 2 +
doc/C/limitations.xml | 34 +++
libgda/gda-statement.c | 37 +++
libgda/gda-statement.h | 4 +-
libgda/gda-value.c | 150 +++++++++-
libgda/gda-value.h | 39 ++--
libgda/handlers/gda-handler-time.c | 40 ++-
libgda/libgda.symbols | 2 +
libgda/sqlite/gda-sqlite-provider.c | 77 ++++--
libgda/sqlite/gda-sqlite-recordset.c | 10 +-
providers/mysql/gda-mysql-provider.c | 32 ++-
providers/mysql/gda-mysql-recordset.c | 4 +-
providers/postgres/gda-postgres-provider.c | 61 ++++-
providers/postgres/gda-postgres-recordset.c | 10 +-
.../skel-implementation/capi/gda-capi-provider.c | 5 +
tests/.gitignore | 1 +
tests/Makefile.am | 13 +-
tests/data-models/check_pmodel.c | 8 +-
tests/data-models/check_vcnc.c | 4 +-
tests/providers/check_mysql.c | 3 +-
tests/providers/check_postgres.c | 3 +-
tests/providers/check_sqlite.c | 3 +-
tests/providers/prov-test-common.c | 131 +++++++++-
tests/providers/prov-test-common.h | 15 +-
tests/providers/prov_dbstruct.xml | 5 +
tests/test-sql-renderer.c | 291 ++++++++++++++++++++
26 files changed, 876 insertions(+), 108 deletions(-)
---
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index 1d6acbe..f08cfc4 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -936,6 +936,7 @@ GdaTime
gda_time_copy
gda_time_free
gda_time_valid
+gda_time_change_timezone
gda_value_get_time
gda_value_set_time
<SUBSECTION>
@@ -943,6 +944,7 @@ GdaTimestamp
gda_timestamp_copy
gda_timestamp_free
gda_timestamp_valid
+gda_timestamp_change_timezone
gda_value_get_timestamp
gda_value_set_timestamp
gda_value_new_timestamp_from_timet
diff --git a/doc/C/limitations.xml b/doc/C/limitations.xml
index e755576..76270f0 100644
--- a/doc/C/limitations.xml
+++ b/doc/C/limitations.xml
@@ -54,6 +54,17 @@
</para>
</sect2>
+ <sect2>
+ <title>Timezone information</title>
+ <para>
+ Timezone information associated with time and timestamp data types is not stored by MySQL, so when
executing
+ statements all the variables containing a time or timestamp information are converted to GMT
(timezone 0) before
+ the execution happens. The consequence is that for example if a variable holds the "11:23:55+2" time
(11 hours,
+ 23 minutes and 55 seconds, at GMT + 2), then the actual time stored in the database will be
"09:23:55", the same
+ time but GMT.
+ </para>
+ </sect2>
+
</sect1>
<sect1 id="limitations_oracle"><title>For Oracle</title>
@@ -113,6 +124,18 @@
</itemizedlist>
</para>
</sect2>
+
+ <sect2>
+ <title>Timezone information</title>
+ <para>
+ Timezone information associated with time and timestamp data types is not stored by PostgreSQL, so
when executing
+ statements all the variables containing a time or timestamp information are converted to GMT
(timezone 0) before
+ the execution happens. The consequence is that for example if a variable holds the "11:23:55+2" time
(11 hours,
+ 23 minutes and 55 seconds, at GMT + 2), then the actual time stored in the database will be
"09:23:55", the same
+ time but GMT.
+ </para>
+ </sect2>
+
</sect1>
<sect1 id="limitations_sqlite"><title>For SQLite</title>
@@ -133,6 +156,17 @@
</para>
</sect2>
+ <sect2>
+ <title>Timezone information</title>
+ <para>
+ Timezone information associated with time and timestamp data types is not stored by PostgreSQL, so
when executing
+ statements all the variables containing a time or timestamp information are converted to GMT
(timezone 0) before
+ the execution happens. The consequence is that for example if a variable holds the "11:23:55+2" time
(11 hours,
+ 23 minutes and 55 seconds, at GMT + 2), then the actual time stored in the database will be
"09:23:55", the same
+ time but GMT.
+ </para>
+ </sect2>
+
<sect2><title>Multi threaded environment</title>
<para>
No limitation if sqlite has been compiled with the SQLITE_THREADSAFE=1 flag (which is the case for the
diff --git a/libgda/gda-statement.c b/libgda/gda-statement.c
index c80fe08..08bea9b 100644
--- a/libgda/gda-statement.c
+++ b/libgda/gda-statement.c
@@ -796,6 +796,43 @@ default_render_value (const GValue *value, GdaSqlRenderingContext *context, GErr
return NULL;
}
}
+ if (context->flags & GDA_STATEMENT_SQL_TIMEZONE_TO_GMT) {
+ if (G_VALUE_TYPE (value) == GDA_TYPE_TIME) {
+ GdaTime *nts;
+ nts = (GdaTime*) gda_value_get_time (value);
+ if (nts && (nts->timezone != GDA_TIMEZONE_INVALID)) {
+ nts = gda_time_copy (nts);
+ gda_time_change_timezone (nts, 0);
+ nts->timezone = GDA_TIMEZONE_INVALID;
+ GValue v = {0};
+ g_value_init (&v, GDA_TYPE_TIME);
+ gda_value_set_time (&v, nts);
+ gda_time_free (nts);
+ gchar *tmp;
+ tmp = gda_data_handler_get_sql_from_value (dh, &v);
+ g_value_reset (&v);
+ return tmp;
+ }
+ }
+ else if (G_VALUE_TYPE (value) == GDA_TYPE_TIMESTAMP) {
+ GdaTimestamp *nts;
+ nts = (GdaTimestamp*) gda_value_get_timestamp (value);
+ if (nts && (nts->timezone != GDA_TIMEZONE_INVALID)) {
+ nts = gda_timestamp_copy (nts);
+ gda_timestamp_change_timezone (nts, 0);
+ nts->timezone = GDA_TIMEZONE_INVALID;
+ GValue v = {0};
+ g_value_init (&v, GDA_TYPE_TIMESTAMP);
+ gda_value_set_timestamp (&v, nts);
+ gda_timestamp_free (nts);
+ gchar *tmp;
+ tmp = gda_data_handler_get_sql_from_value (dh, &v);
+ g_value_reset (&v);
+ return tmp;
+ }
+ }
+ }
+
return gda_data_handler_get_sql_from_value (dh, value);
}
else
diff --git a/libgda/gda-statement.h b/libgda/gda-statement.h
index 6979592..9521177 100644
--- a/libgda/gda-statement.h
+++ b/libgda/gda-statement.h
@@ -86,6 +86,7 @@ typedef enum {
* @GDA_STATEMENT_SQL_PARAMS_AS_DOLLAR: parameters will be rendered using the "$<param_number>" syntax
where parameters are numbered starting from 1
* @GDA_STATEMENT_SQL_PARAMS_AS_QMARK: parameters will be rendered using the "?<param_number>" syntax
where parameters are numbered starting from 1
* @GDA_STATEMENT_SQL_PARAMS_AS_UQMARK: parameters will be rendered using the "?" syntax
+ * @GDA_STATEMENT_SQL_TIMEZONE_TO_GMT: time and timestamp with a timezone information are converted to GMT
before rendering, and rendering does not show the timezone information
*
* Specifies rendering options
*/
@@ -97,7 +98,8 @@ typedef enum {
GDA_STATEMENT_SQL_PARAMS_AS_COLON = 1 << 3,
GDA_STATEMENT_SQL_PARAMS_AS_DOLLAR = 1 << 4,
GDA_STATEMENT_SQL_PARAMS_AS_QMARK = 1 << 5,
- GDA_STATEMENT_SQL_PARAMS_AS_UQMARK = 1 << 6
+ GDA_STATEMENT_SQL_PARAMS_AS_UQMARK = 1 << 6,
+ GDA_STATEMENT_SQL_TIMEZONE_TO_GMT = 1 << 7
} GdaStatementSqlFlag;
/* struct for the object's data */
diff --git a/libgda/gda-value.c b/libgda/gda-value.c
index 658c9fc..93d47e2 100644
--- a/libgda/gda-value.c
+++ b/libgda/gda-value.c
@@ -1240,14 +1240,65 @@ gda_time_valid (const GdaTime *time)
{
g_return_val_if_fail (time, FALSE);
- if (time->hour > 24 ||
- time->minute > 59 ||
- time->second > 59)
+ if ((time->hour > 23) ||
+ (time->minute > 59) ||
+ (time->second > 59))
return FALSE;
- else
- return TRUE;
+ if ((time->fraction >= 1000000) ||
+ (time->timezone <= -12 * 3600) ||
+ (time->timezone >= 12 * 3600))
+ return FALSE;
+ return TRUE;
}
+/**
+ * gda_time_change_timezone:
+ * @time: a valid #GdaTime
+ * @ntz: a new timezone to use, in seconds added to GMT
+ *
+ * Changes @time's timezone (for example to convert from GMT to another time zone).
+ * If @time's current timezone is unset (i.e. equal to %GDA_TIMEZONE_INVALID), then this function simply sets
+ * @time's timezone attribute; Otherwise, it adds or removes hours, minutes or seconds to reflect the time
in the new timezone.
+ *
+ * Note: the resulting will always be a valid time.
+ *
+ * Since: 5.2
+ */
+void
+gda_time_change_timezone (GdaTime *time, glong ntz)
+{
+ g_return_if_fail (time);
+ g_return_if_fail (gda_time_valid (time));
+ g_return_if_fail ((ntz > - 12 * 3600) && (ntz < 12 * 3600));
+
+ if (time->timezone == ntz)
+ return;
+
+ if (time->timezone != GDA_TIMEZONE_INVALID) {
+ glong nsec;
+ nsec = time->hour * 3600 + time->minute * 60 + time->second - time->timezone + ntz;
+ if (nsec < 0)
+ nsec += 86400;
+ else if (nsec >= 86400)
+ nsec -= 86400;
+
+ /* hours */
+ gint n;
+ n = nsec / 3600;
+ time->hour = (gushort) n;
+
+ /* minutes */
+ nsec -= n * 3600;
+ n = nsec / 60;
+ time->minute = (gushort) n;
+
+ /* seconds */
+ nsec -= n * 60;
+ time->second = (gushort) nsec;
+ }
+
+ time->timezone = ntz;
+}
/*
* Register the GdaTimestamp type in the GType system
@@ -1366,12 +1417,79 @@ gda_timestamp_valid (const GdaTimestamp *timestamp)
return FALSE;
/* check the time part */
- if (timestamp->hour > 24 ||
- timestamp->minute > 59 ||
- timestamp->second > 59)
+ if ((timestamp->hour > 23) ||
+ (timestamp->minute > 59) ||
+ (timestamp->second > 59))
return FALSE;
- else
- return TRUE;
+ if ((timestamp->fraction >= 1000000) ||
+ (timestamp->timezone <= -12 * 3600) ||
+ (timestamp->timezone >= 12 * 3600))
+ return FALSE;
+
+ return TRUE;
+}
+
+/**
+ * gda_timestamp_change_timezone:
+ * @time: a valid #GdaTimestamp
+ * @ntz: a new timezone to use, in seconds added to GMT
+ *
+ * This function is similar to gda_time_change_timezone() but operates on time stamps.
+ *
+ * Note: the resulting will always be a valid time.
+ *
+ * Since: 5.2
+ */
+void
+gda_timestamp_change_timezone (GdaTimestamp *ts, glong ntz)
+{
+ g_return_if_fail (ts);
+ g_return_if_fail (gda_timestamp_valid (ts));
+ g_return_if_fail ((ntz > - 12 * 3600) && (ntz < 12 * 3600));
+
+ if (ts->timezone == ntz)
+ return;
+
+ if (ts->timezone != GDA_TIMEZONE_INVALID) {
+ glong nsec;
+ nsec = ts->hour * 3600 + ts->minute * 60 + ts->second - ts->timezone + ntz;
+ if (nsec < 0) {
+ GDate *date;
+ date = g_date_new_dmy ((GDateDay) ts->day, (GDateMonth) ts->month, (GDateYear)
ts->year);
+ g_date_subtract_days (date, 1);
+ ts->year = g_date_get_year (date);
+ ts->month = g_date_get_month (date);
+ ts->day = g_date_get_day (date);
+ g_date_free (date);
+ nsec += 86400;
+ }
+ else if (nsec >= 86400) {
+ GDate *date;
+ date = g_date_new_dmy ((GDateDay) ts->day, (GDateMonth) ts->month, (GDateYear)
ts->year);
+ g_date_add_days (date, 1);
+ ts->year = g_date_get_year (date);
+ ts->month = g_date_get_month (date);
+ ts->day = g_date_get_day (date);
+ g_date_free (date);
+ nsec -= 86400;
+ }
+
+ /* hours */
+ gint n;
+ n = nsec / 3600;
+ ts->hour = (gushort) n;
+
+ /* minutes */
+ nsec -= n * 3600;
+ n = nsec / 60;
+ ts->minute = (gushort) n;
+
+ /* seconds */
+ nsec -= n * 60;
+ ts->second = (gushort) nsec;
+ }
+
+ ts->timezone = ntz;
}
/**
@@ -1521,7 +1639,14 @@ gda_value_new_blob_from_file (const gchar *filename)
* @val: value to set for the new #GValue.
*
* Makes a new #GValue of type #GDA_TYPE_TIMESTAMP with value @val
- * (of type time_t).
+ * (of type time_t). The returned timestamp has a timezone initialized with the
+ * current timezone, taking into account the daylight savings.
+ *
+ * For example, to get a time stamp representing the current date and time, use:
+ *
+ * <code>
+ * ts = gda_value_new_timestamp_from_timet (time (NULL));
+ * </code>
*
* Returns: (transfer full): the newly created #GValue.
*
@@ -1537,6 +1662,7 @@ gda_value_new_timestamp_from_timet (time_t val)
value = g_new0 (GValue, 1);
#ifdef HAVE_LOCALTIME_R
struct tm tmpstm;
+ tzset ();
ltm = localtime_r ((const time_t *) &val, &tmpstm);
#elif HAVE_LOCALTIME_S
struct tm tmpstm;
@@ -1557,7 +1683,7 @@ gda_value_new_timestamp_from_timet (time_t val)
tstamp.minute = ltm->tm_min;
tstamp.second = ltm->tm_sec;
tstamp.fraction = 0;
- tstamp.timezone = GDA_TIMEZONE_INVALID;
+ tstamp.timezone = - timezone + daylight * 3600;
gda_value_set_timestamp (value, (const GdaTimestamp *) &tstamp);
}
diff --git a/libgda/gda-value.h b/libgda/gda-value.h
index 2d280fa..d84ed82 100644
--- a/libgda/gda-value.h
+++ b/libgda/gda-value.h
@@ -1,7 +1,7 @@
/*
* Copyright (C) 2001 - 2003 Rodrigo Moya <rodrigo gnome-db org>
* Copyright (C) 2002 - 2003 Gonzalo Paniagua Javier <gonzalo gnome-db org>
- * Copyright (C) 2002 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2002 - 2013 Vivien Malerba <malerba gnome-db org>
* Copyright (C) 2003 Akira TAGOH <tagoh gnome-db org>
* Copyright (C) 2003 Danilo Schoeneberg <dschoene src gnome org>
* Copyright (C) 2003 Laurent Sansonetti <laurent datarescue be>
@@ -91,30 +91,34 @@ struct _GdaNumeric {
/**
* GdaTime:
- * @hour:
- * @minute:
- * @second:
- * @fraction:
- * @timezone:
+ * @hour: hour representation of the time, as a number between 0 and 23
+ * @minute: minute representation of the time, as a number between 0 and 59
+ * @second: second representation of the time, as a number between 0 and 59
+ * @fraction: fractionnal part of the seconds, in millionth' of second
+ * @timezone: number of seconds added to the GMT timezone
+ *
+ * Represents a time information.
*/
typedef struct {
gushort hour;
gushort minute;
gushort second;
gulong fraction;
- glong timezone; /* # of seconds to the east UTC */
+ glong timezone;
} GdaTime;
/**
* GdaTimestamp:
- * @year: representation of the date
- * @month: month representation of the date, as a number between 1 and 12
- * @day: day representation of the date, as a number between 1 and 31
- * @hour:
- * @minute:
- * @second:
- * @fraction:
- * @timezone:
+ * @year: year representation of the time stamp
+ * @month: month representation of the time stamp, as a number between 1 and 12
+ * @day: day representation of the time stamp, as a number between 1 and 31
+ * @hour: hour representation of the time stamp, as a number between 0 and 23
+ * @minute: minute representation of the time stamp, as a number between 0 and 59
+ * @second: second representation of the time stamp, as a number between 0 and 59
+ * @fraction: fractionnal part of the seconds, in millionth' of second
+ * @timezone: number of seconds added to the GMT timezone
+ *
+ * Represents an instant (a time stamp)
*/
typedef struct {
gshort year;
@@ -124,7 +128,7 @@ typedef struct {
gushort minute;
gushort second;
gulong fraction;
- glong timezone; /* # of seconds to the east UTC */
+ glong timezone;
} GdaTimestamp;
/**
@@ -271,12 +275,13 @@ GType gda_time_get_type (void) G_GNUC_CONST;
gpointer gda_time_copy (gpointer boxed);
void gda_time_free (gpointer boxed);
gboolean gda_time_valid (const GdaTime *time);
-
+void gda_time_change_timezone (GdaTime *time, glong ntz);
GType gda_timestamp_get_type (void) G_GNUC_CONST;
gpointer gda_timestamp_copy (gpointer boxed);
void gda_timestamp_free (gpointer boxed);
gboolean gda_timestamp_valid (const GdaTimestamp *timestamp);
+void gda_timestamp_change_timezone (GdaTimestamp *ts, glong ntz);
GType gda_geometricpoint_get_type (void) G_GNUC_CONST;
diff --git a/libgda/handlers/gda-handler-time.c b/libgda/handlers/gda-handler-time.c
index 085f397..ef4235e 100644
--- a/libgda/handlers/gda-handler-time.c
+++ b/libgda/handlers/gda-handler-time.c
@@ -453,12 +453,19 @@ gda_handler_time_get_no_locale_str_from_value (GdaHandlerTime *dh, const GValue
}
else if (type == GDA_TYPE_TIME) {
const GdaTime *tim;
-
+ GString *string;
+ string = g_string_new ("");
+ g_string_append_c (string, '\'');
tim = gda_value_get_time ((GValue *) value);
- retval = g_strdup_printf ("'%02d:%02d:%02d'",
- tim->hour,
- tim->minute,
- tim->second);
+ g_string_append_printf (string, "%02d:%02d:%02d",
+ tim->hour,
+ tim->minute,
+ tim->second);
+ if (tim->timezone != GDA_TIMEZONE_INVALID)
+ g_string_append_printf (string, "%+02d",
+ (int) tim->timezone / 3600);
+ g_string_append_c (string, '\'');
+ retval = g_string_free (string, FALSE);
}
else if (type == GDA_TYPE_TIMESTAMP) {
const GdaTimestamp *gdats;
@@ -480,7 +487,7 @@ gda_handler_time_get_no_locale_str_from_value (GdaHandlerTime *dh, const GValue
g_string_append_printf (string, ".%lu", gdats->fraction);
if (gdats->timezone != GDA_TIMEZONE_INVALID)
- g_string_append_printf (string, "%+02d",
+ g_string_append_printf (string, "%+02d",
(int) gdats->timezone / 3600);
retval = g_strdup_printf ("%s %s", str, string->str);
@@ -621,12 +628,19 @@ gda_handler_time_get_sql_from_value (GdaDataHandler *iface, const GValue *value)
}
else if (type == GDA_TYPE_TIME) {
const GdaTime *tim;
-
+ GString *string;
+ string = g_string_new ("");
tim = gda_value_get_time ((GValue *) value);
- retval = g_strdup_printf ("'%02d:%02d:%02d'",
- tim->hour,
- tim->minute,
- tim->second);
+ g_string_append_c (string, '\'');
+ g_string_append_printf (string, "%02d:%02d:%02d",
+ tim->hour,
+ tim->minute,
+ tim->second);
+ if (tim->timezone != GDA_TIMEZONE_INVALID)
+ g_string_append_printf (string, "%+02d",
+ (int) tim->timezone / 3600);
+ g_string_append_c (string, '\'');
+ retval = g_string_free (string, FALSE);
}
else if (type == GDA_TYPE_TIMESTAMP) {
const GdaTimestamp *gdats;
@@ -648,7 +662,7 @@ gda_handler_time_get_sql_from_value (GdaDataHandler *iface, const GValue *value)
g_string_append_printf (string, ".%lu", gdats->fraction);
if (gdats->timezone != GDA_TIMEZONE_INVALID)
- g_string_append_printf (string, "%+02d",
+ g_string_append_printf (string, "%+02d",
(int) gdats->timezone / 3600);
retval = g_strdup_printf ("'%s %s'", str, string->str);
@@ -752,7 +766,7 @@ gda_handler_time_get_str_from_value (GdaDataHandler *iface, const GValue *value)
g_string_append_printf (string, ".%lu", gdats->fraction);
if (gdats->timezone != GDA_TIMEZONE_INVALID)
- g_string_append_printf (string, "%+02d",
+ g_string_append_printf (string, "%+02d",
(int) gdats->timezone / 3600);
retval = g_strdup_printf ("%s %s", str, string->str);
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index 1e476e9..e6bd926 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -886,10 +886,12 @@
gda_thread_wrapper_steal_signal
gda_thread_wrapper_unset_io_channel
gda_time_copy
+ gda_time_change_timezone
gda_time_free
gda_time_get_type
gda_time_valid
gda_timestamp_copy
+ gda_timestamp_change_timezone
gda_timestamp_free
gda_timestamp_get_type
gda_timestamp_valid
diff --git a/libgda/sqlite/gda-sqlite-provider.c b/libgda/sqlite/gda-sqlite-provider.c
index 3ea3d8f..01a8627 100644
--- a/libgda/sqlite/gda-sqlite-provider.c
+++ b/libgda/sqlite/gda-sqlite-provider.c
@@ -2972,13 +2972,14 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
ps = (GdaSqlitePStmt *) gda_connection_get_prepared_statement (cnc, stmt);
if (!ps) {
if (!gda_sqlite_provider_statement_prepare (provider, cnc, stmt, NULL)) {
- /* try to use the SQL when parameters are rendered with their values */
+ /* try to use the SQL when parameters are rendered with their values, using GMT for
timezones */
gchar *sql;
int status;
sqlite3_stmt *sqlite_stmt;
char *left;
- sql = gda_sqlite_provider_statement_to_sql (provider, cnc, stmt, params, 0, NULL,
error);
+ sql = gda_sqlite_provider_statement_to_sql (provider, cnc, stmt, params,
+ GDA_STATEMENT_SQL_TIMEZONE_TO_GMT, NULL,
error);
if (!sql)
return NULL;
@@ -3288,17 +3289,31 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
bin->data, bin->binary_length, SQLITE_TRANSIENT);
}
else if (G_VALUE_TYPE (value) == GDA_TYPE_TIME) {
- gchar *str;
- const GdaTime *ts;
+ GString *string;
+ GdaTime *gtime;
+ gboolean tofree = FALSE;
- ts = gda_value_get_time (value);
- if (ts->fraction != 0)
- str = g_strdup_printf ("%02d:%02d:%02d.%ld",
- ts->hour, ts->minute, ts->second, ts->fraction);
- else
- str = g_strdup_printf ("%02d:%02d:%02d",
- ts->hour, ts->minute, ts->second);
- SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, str, -1, g_free);
+ gtime = (GdaTime *) gda_value_get_time (value);
+
+ string = g_string_new ("");
+ if (gtime->timezone != GDA_TIMEZONE_INVALID) {
+ /* SQLite cant' store timezone information, so if timezone information is
+ * provided, we do our best and convert it to GMT */
+ gtime = gda_time_copy (gtime);
+ tofree = TRUE;
+ gda_time_change_timezone (gtime, 0);
+ }
+
+ g_string_append_printf (string, "%02u:%02u:%02u",
+ gtime->hour,
+ gtime->minute,
+ gtime->second);
+ if (gtime->fraction > 0)
+ g_string_append_printf (string, ".%lu", gtime->fraction);
+
+ if (tofree)
+ gda_time_free (gtime);
+ SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, g_string_free (string, FALSE),
-1, g_free);
}
else if (G_VALUE_TYPE (value) == G_TYPE_DATE) {
gchar *str;
@@ -3310,17 +3325,35 @@ gda_sqlite_provider_statement_execute (GdaServerProvider *provider, GdaConnectio
SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, str, -1, g_free);
}
else if (G_VALUE_TYPE (value) == GDA_TYPE_TIMESTAMP) {
- gchar *str;
- const GdaTimestamp *ts;
+ GString *string;
+ GdaTimestamp *timestamp;
+ gboolean tofree = FALSE;
- ts = gda_value_get_timestamp (value);
- if (ts->fraction != 0)
- str = g_strdup_printf ("%4d-%02d-%02d %02d:%02d:%02d.%ld", ts->year,
ts->month, ts->day,
- ts->hour, ts->minute, ts->second, ts->fraction);
- else
- str = g_strdup_printf ("%4d-%02d-%02d %02d:%02d:%02d", ts->year, ts->month,
ts->day,
- ts->hour, ts->minute, ts->second);
- SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, str, -1, g_free);
+ timestamp = (GdaTimestamp *) gda_value_get_timestamp (value);
+
+ string = g_string_new ("");
+ if (timestamp->timezone != GDA_TIMEZONE_INVALID) {
+ /* SQLite cant' store timezone information, so if timezone information is
+ * provided, we do our best and convert it to GMT */
+ timestamp = gda_timestamp_copy (timestamp);
+ tofree = TRUE;
+ gda_timestamp_change_timezone (timestamp, 0);
+ }
+
+ g_string_append_printf (string, "%04u-%02u-%02u %02u:%02u:%02u",
+ timestamp->year,
+ timestamp->month,
+ timestamp->day,
+ timestamp->hour,
+ timestamp->minute,
+ timestamp->second);
+ if (timestamp->fraction > 0)
+ g_string_append_printf (string, ".%lu", timestamp->fraction);
+
+ if (tofree)
+ gda_timestamp_free (timestamp);
+
+ SQLITE3_CALL (sqlite3_bind_text) (ps->sqlite_stmt, i, g_string_free (string, FALSE),
-1, g_free);
}
else if (G_VALUE_TYPE (value) == GDA_TYPE_NUMERIC) {
const GdaNumeric *gdan;
diff --git a/libgda/sqlite/gda-sqlite-recordset.c b/libgda/sqlite/gda-sqlite-recordset.c
index 6ef4914..e1c0376 100644
--- a/libgda/sqlite/gda-sqlite-recordset.c
+++ b/libgda/sqlite/gda-sqlite-recordset.c
@@ -550,8 +550,11 @@ fetch_next_sqlite_row (GdaSqliteRecordset *model, gboolean do_store, GError **er
(gchar *) SQLITE3_CALL (sqlite3_column_text)
(ps->sqlite_stmt, real_col));
gda_row_invalidate_value_e (prow, value, lerror);
}
- else
+ else {
+ if (timegda.timezone == GDA_TIMEZONE_INVALID)
+ timegda.timezone = 0; /* set to GMT */
gda_value_set_time (value, &timegda);
+ }
}
else if (type == GDA_TYPE_TIMESTAMP) {
GdaTimestamp timestamp;
@@ -565,8 +568,11 @@ fetch_next_sqlite_row (GdaSqliteRecordset *model, gboolean do_store, GError **er
(gchar *) SQLITE3_CALL (sqlite3_column_text)
(ps->sqlite_stmt, real_col));
gda_row_invalidate_value_e (prow, value, lerror);
}
- else
+ else {
+ if (timestamp.timezone == GDA_TIMEZONE_INVALID)
+ timestamp.timezone = 0; /* set to GMT */
gda_value_set_timestamp (value, ×tamp);
+ }
}
else if (type == G_TYPE_CHAR) {
gint64 i;
diff --git a/providers/mysql/gda-mysql-provider.c b/providers/mysql/gda-mysql-provider.c
index 2aa3e74..4f8b856 100644
--- a/providers/mysql/gda-mysql-provider.c
+++ b/providers/mysql/gda-mysql-provider.c
@@ -2210,7 +2210,7 @@ gda_mysql_provider_statement_execute (GdaServerProvider *provider,
* and use that SQL instead of @stmt to create another GdaMysqlPStmt object.
*/
gchar *sql = gda_mysql_provider_statement_to_sql (provider, cnc, stmt,
- params, 0, NULL, error);
+ params,
GDA_STATEMENT_SQL_TIMEZONE_TO_GMT, NULL, error);
gboolean proto_error;
if (!sql)
return NULL;
@@ -2454,14 +2454,23 @@ gda_mysql_provider_statement_execute (GdaServerProvider *provider,
}
}
else if (G_VALUE_TYPE (value) == GDA_TYPE_TIMESTAMP) {
- const GdaTimestamp *ts;
+ GdaTimestamp *ts;
- ts = gda_value_get_timestamp (value);
+ ts = (GdaTimestamp*) gda_value_get_timestamp (value);
if (!ts) {
mysql_bind_param[i].buffer_type = MYSQL_TYPE_NULL;
mysql_bind_param[i].is_null = (my_bool*)1;
}
else {
+ gboolean tofree = FALSE;
+ if (ts->timezone != GDA_TIMEZONE_INVALID) {
+ /* MySQL does not store timezone information, so if timezone
information is
+ * provided, we do our best and convert it to GMT */
+ ts = gda_timestamp_copy (ts);
+ tofree = TRUE;
+ gda_timestamp_change_timezone (ts, 0);
+ }
+
MYSQL_TIME *mtime;
mtime = g_new0 (MYSQL_TIME, 1);
mem_to_free = g_slist_prepend (mem_to_free, mtime);
@@ -2472,6 +2481,8 @@ gda_mysql_provider_statement_execute (GdaServerProvider *provider,
mtime->minute = ts->minute;
mtime->second = ts->second;
mtime->second_part = ts->fraction;
+ if (tofree)
+ gda_timestamp_free (ts);
mysql_bind_param[i].buffer_type= MYSQL_TYPE_TIMESTAMP;
mysql_bind_param[i].buffer= (char *)mtime;
@@ -2479,14 +2490,23 @@ gda_mysql_provider_statement_execute (GdaServerProvider *provider,
}
}
else if (G_VALUE_TYPE (value) == GDA_TYPE_TIME) {
- const GdaTime *ts;
+ GdaTime *ts;
- ts = gda_value_get_time (value);
+ ts = (GdaTime*) gda_value_get_time (value);
if (!ts) {
mysql_bind_param[i].buffer_type = MYSQL_TYPE_NULL;
mysql_bind_param[i].is_null = (my_bool*)1;
}
else {
+ gboolean tofree = FALSE;
+ if (ts->timezone != GDA_TIMEZONE_INVALID) {
+ /* MySQL does not store timezone information, so if timezone
information is
+ * provided, we do our best and convert it to GMT */
+ ts = gda_time_copy (ts);
+ tofree = TRUE;
+ gda_time_change_timezone (ts, 0);
+ }
+
MYSQL_TIME *mtime;
mtime = g_new0 (MYSQL_TIME, 1);
mem_to_free = g_slist_prepend (mem_to_free, mtime);
@@ -2494,6 +2514,8 @@ gda_mysql_provider_statement_execute (GdaServerProvider *provider,
mtime->minute = ts->minute;
mtime->second = ts->second;
mtime->second_part = ts->fraction;
+ if (tofree)
+ gda_time_free (ts);
mysql_bind_param[i].buffer_type= MYSQL_TYPE_TIME;
mysql_bind_param[i].buffer= (char *)mtime;
diff --git a/providers/mysql/gda-mysql-recordset.c b/providers/mysql/gda-mysql-recordset.c
index d2d7f3a..e534cad 100644
--- a/providers/mysql/gda-mysql-recordset.c
+++ b/providers/mysql/gda-mysql-recordset.c
@@ -7,7 +7,7 @@
* Copyright (C) 2003 Chris Silles <csilles src gnome org>
* Copyright (C) 2003 Laurent Sansonetti <lrz gnome org>
* Copyright (C) 2003 Paisa Seeluangsawat <paisa users sf net>
- * Copyright (C) 2004 - 2012 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2004 - 2013 Vivien Malerba <malerba gnome-db org>
* Copyright (C) 2005 Alan Knowles <alan akbkhome com>
* Copyright (C) 2005 - 2009 Bas Driessen <bas driessen xobas com>
* Copyright (C) 2005 Mike Fisk <mfisk woozle org>
@@ -864,6 +864,7 @@ new_row_from_mysql_stmt (GdaMysqlRecordset *imodel, G_GNUC_UNUSED gint rownum, G
.minute = bvalue.minute,
.second = bvalue.second,
.fraction = bvalue.second_part,
+ .timezone = 0 /* GMT */
};
gda_value_set_time (value, &time);
}
@@ -883,6 +884,7 @@ new_row_from_mysql_stmt (GdaMysqlRecordset *imodel, G_GNUC_UNUSED gint rownum, G
.minute = bvalue.minute,
.second = bvalue.second,
.fraction = bvalue.second_part,
+ .timezone = 0 /* GMT */
};
gda_value_set_timestamp (value, ×tamp);
}
diff --git a/providers/postgres/gda-postgres-provider.c b/providers/postgres/gda-postgres-provider.c
index e2ce2a7..d9bccb4 100644
--- a/providers/postgres/gda-postgres-provider.c
+++ b/providers/postgres/gda-postgres-provider.c
@@ -1844,8 +1844,9 @@ gda_postgres_provider_statement_execute (GdaServerProvider *provider, GdaConnect
GdaDataModel *recset;
GdaConnectionEvent *event;
- /* convert to SQL to remove any reference to a variable */
- sql = gda_postgres_provider_statement_to_sql (provider, cnc, stmt, params, 0, NULL, error);
+ /* convert to SQL to remove any reference to a variable, using GMT for timezones */
+ sql = gda_postgres_provider_statement_to_sql (provider, cnc, stmt, params,
+ GDA_STATEMENT_SQL_TIMEZONE_TO_GMT, NULL, error);
if (!sql)
return NULL;
@@ -1891,7 +1892,8 @@ gda_postgres_provider_statement_execute (GdaServerProvider *provider, GdaConnect
*/
gchar *sql;
- sql = gda_postgres_provider_statement_to_sql (provider, cnc, stmt, params, 0, NULL,
error);
+ sql = gda_postgres_provider_statement_to_sql (provider, cnc, stmt, params,
+ GDA_STATEMENT_SQL_TIMEZONE_TO_GMT,
NULL, error);
if (!sql)
return NULL;
ps = prepare_stmt_simple (cdata, sql, error); // FIXME: this @ps is leaked!
@@ -2108,16 +2110,59 @@ gda_postgres_provider_statement_execute (GdaServerProvider *provider, GdaConnect
param_formats [i] = 1; /* binary format */
param_mem [i] = TRUE; /* don't free later */
}
- else if ((G_VALUE_TYPE (value) == G_TYPE_DATE) ||
- (G_VALUE_TYPE (value) == GDA_TYPE_TIMESTAMP) ||
- (G_VALUE_TYPE (value) == GDA_TYPE_TIME)) {
+ else if (G_VALUE_TYPE (value) == G_TYPE_DATE) {
GdaHandlerTime *timdh;
-
timdh = GDA_HANDLER_TIME (gda_server_provider_get_data_handler_g_type (provider, cnc,
- G_VALUE_TYPE
(value)));
+ G_VALUE_TYPE
(value)));
g_assert (timdh);
param_values [i] = gda_handler_time_get_no_locale_str_from_value (timdh, value);
}
+ else if (G_VALUE_TYPE (value) == GDA_TYPE_TIMESTAMP) {
+ GdaHandlerTime *timdh;
+ timdh = GDA_HANDLER_TIME (gda_server_provider_get_data_handler_g_type (provider, cnc,
+ G_VALUE_TYPE
(value)));
+ g_assert (timdh);
+
+ GdaTimestamp *timestamp;
+ timestamp = (GdaTimestamp *) gda_value_get_timestamp (value);
+ if (timestamp->timezone != GDA_TIMEZONE_INVALID) {
+ /* PostgreSQL does not store timezone information, so if timezone information
is
+ * provided, we do our best and convert it to GMT */
+ timestamp = gda_timestamp_copy (timestamp);
+ gda_timestamp_change_timezone (timestamp, 0);
+ GValue *rv;
+ rv = gda_value_new (GDA_TYPE_TIMESTAMP);
+ gda_value_set_timestamp (rv, timestamp);
+ gda_timestamp_free (timestamp);
+ param_values [i] = gda_handler_time_get_no_locale_str_from_value (timdh, rv);
+ gda_value_free (rv);
+ }
+ else
+ param_values [i] = gda_handler_time_get_no_locale_str_from_value (timdh,
value);
+ }
+ else if (G_VALUE_TYPE (value) == GDA_TYPE_TIME) {
+ GdaHandlerTime *timdh;
+ timdh = GDA_HANDLER_TIME (gda_server_provider_get_data_handler_g_type (provider, cnc,
+ G_VALUE_TYPE
(value)));
+ g_assert (timdh);
+
+ GdaTime *gdatime;
+ gdatime = (GdaTime*) gda_value_get_time (value);
+ if (gdatime->timezone != GDA_TIMEZONE_INVALID) {
+ /* PostgreSQL does not store timezone information, so if timezone information
is
+ * provided, we do our best and convert it to GMT */
+ gdatime = gda_time_copy (gdatime);
+ gda_time_change_timezone (gdatime, 0);
+ GValue *rv;
+ rv = gda_value_new (GDA_TYPE_TIME);
+ gda_value_set_time (rv, gdatime);
+ gda_time_free (gdatime);
+ param_values [i] = gda_handler_time_get_no_locale_str_from_value (timdh, rv);
+ gda_value_free (rv);
+ }
+ else
+ param_values [i] = gda_handler_time_get_no_locale_str_from_value (timdh,
value);
+ }
else {
GdaDataHandler *dh;
diff --git a/providers/postgres/gda-postgres-recordset.c b/providers/postgres/gda-postgres-recordset.c
index 8096f4a..60b1170 100644
--- a/providers/postgres/gda-postgres-recordset.c
+++ b/providers/postgres/gda-postgres-recordset.c
@@ -628,8 +628,11 @@ set_value (GdaConnection *cnc, GdaRow *row, GValue *value, GType type, const gch
GDA_SERVER_PROVIDER_DATA_ERROR,
_("Invalid time '%s' (time format should be HH:MM:SS[.ms])"), thevalue);
}
- else
+ else {
+ if (timegda.timezone == GDA_TIMEZONE_INVALID)
+ timegda.timezone = 0; /* set to GMT */
gda_value_set_time (value, &timegda);
+ }
}
else if (type == G_TYPE_INT64)
g_value_set_int64 (value, atoll (thevalue));
@@ -671,8 +674,11 @@ set_value (GdaConnection *cnc, GdaRow *row, GValue *value, GType type, const gch
_("Invalid timestamp '%s' (format should be YYYY-MM-DD HH:MM:SS[.ms])"),
thevalue);
}
- else
+ else {
+ if (timestamp.timezone == GDA_TIMEZONE_INVALID)
+ timestamp.timezone = 0; /* set to GMT */
gda_value_set_timestamp (value, ×tamp);
+ }
}
else if (type == GDA_TYPE_BINARY) {
/*
diff --git a/providers/skel-implementation/capi/gda-capi-provider.c
b/providers/skel-implementation/capi/gda-capi-provider.c
index 5d59710..2742b64 100644
--- a/providers/skel-implementation/capi/gda-capi-provider.c
+++ b/providers/skel-implementation/capi/gda-capi-provider.c
@@ -1239,6 +1239,11 @@ gda_capi_provider_statement_execute (GdaServerProvider *provider, GdaConnection
}
}
else {
+ /* usually the way to bind parameters is different depending on the type of @value.
+ * Also, if the database engine does not support storing timezone information for
time and
+ * timestamp values, then before binding, the value must be converted to GMT using
+ * gda_time_change_timezone (xxx, 0) or gda_timestamp_change_timezone (xxx, 0)
+ */
TO_IMPLEMENT;
}
}
diff --git a/tests/.gitignore b/tests/.gitignore
index 0429d8a..9bc2f53 100644
--- a/tests/.gitignore
+++ b/tests/.gitignore
@@ -6,5 +6,6 @@ test-sql-builder
test-connection-string-split
test-input-parsers
test-quark-list
+test-sql-renderer
*.log
*.trs
\ No newline at end of file
diff --git a/tests/Makefile.am b/tests/Makefile.am
index f23467f..ac3b8ce 100644
--- a/tests/Makefile.am
+++ b/tests/Makefile.am
@@ -1,8 +1,8 @@
noinst_LTLIBRARIES = libgda-test-5.0.la
TESTS_ENVIRONMENT = GDA_TOP_SRC_DIR="$(abs_top_srcdir)" GDA_TOP_BUILD_DIR="$(abs_top_builddir)"
-TESTS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes test-sql-builder
test-connection-string-split test-input-parsers test-quark-list
-check_PROGRAMS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes
test-sql-builder test-connection-string-split test-input-parsers test-quark-list
+TESTS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes test-sql-builder
test-connection-string-split test-input-parsers test-quark-list test-sql-renderer
+check_PROGRAMS = test-ddl-creator test-bin-converter test-sql-identifier test-identifiers-quotes
test-sql-builder test-connection-string-split test-input-parsers test-quark-list test-sql-renderer
if ENABLE_VALA_EXTENSIONS
VALA_EXTENSIONS= vala
@@ -98,4 +98,13 @@ test_quark_list_LDADD = \
$(top_builddir)/libgda/libgda-5.0.la \
$(COREDEPS_LIBS)
+test_sql_renderer_SOURCES = \
+ test-sql-renderer.c
+
+test_sql_renderer_LDADD = \
+ $(top_builddir)/libgda/libgda-5.0.la \
+ libgda-test-5.0.la \
+ $(COREDEPS_LIBS)
+
+
EXTRA_DIST = dbstruct.xml
diff --git a/tests/data-models/check_pmodel.c b/tests/data-models/check_pmodel.c
index 326cdb4..a78224e 100644
--- a/tests/data-models/check_pmodel.c
+++ b/tests/data-models/check_pmodel.c
@@ -451,7 +451,7 @@ test3 (GdaConnection *cnc)
/****/
gda_value_set_from_string ((value = gda_value_new (GDA_TYPE_TIMESTAMP)),
- "2009-11-30 11:22:33", GDA_TYPE_TIMESTAMP);
+ "2009-11-30 11:22:33+0", GDA_TYPE_TIMESTAMP);
if (! check_set_value_at (model, 2, 1, value, cnc, stmt, NULL)) {
nfailed ++;
goto out;
@@ -879,7 +879,7 @@ test7 (GdaConnection *cnc)
}
gda_value_set_from_string ((value = gda_value_new (GDA_TYPE_TIMESTAMP)),
- "2004-03-22 11:22:44", GDA_TYPE_TIMESTAMP);
+ "2004-03-22 11:22:44+0", GDA_TYPE_TIMESTAMP);
if (! check_set_value_at (model, 2, 1, value, cnc, stmt, params)) {
nfailed ++;
goto out;
@@ -1453,7 +1453,7 @@ test13 (GdaConnection *cnc)
/****/
gda_value_set_from_string ((value = gda_value_new (GDA_TYPE_TIMESTAMP)),
- "2009-11-30 11:22:33", GDA_TYPE_TIMESTAMP);
+ "2009-11-30 11:22:33+0", GDA_TYPE_TIMESTAMP);
if (! check_set_value_at (model, 1, 1, value, cnc, stmt, NULL)) {
nfailed ++;
goto out;
@@ -1548,7 +1548,7 @@ test14 (GdaConnection *cnc)
/****/
gda_value_set_from_string ((value = gda_value_new (GDA_TYPE_TIMESTAMP)),
- "2019-11-30 11:22:33", GDA_TYPE_TIMESTAMP);
+ "2019-11-30 11:22:33+0", GDA_TYPE_TIMESTAMP);
if (! check_set_value_at (model, 1, 1, value, cnc, stmt, NULL)) {
nfailed ++;
goto out;
diff --git a/tests/data-models/check_vcnc.c b/tests/data-models/check_vcnc.c
index 617088e..9b8908b 100644
--- a/tests/data-models/check_vcnc.c
+++ b/tests/data-models/check_vcnc.c
@@ -568,8 +568,8 @@ check_date (GdaConnection *virtual)
{
g_print ("*** insert dates into 'misc' table...\n");
GdaSet *set;
- GdaTimestamp ts = {2011, 01, 31, 12, 34, 56, 0, GDA_TIMEZONE_INVALID};
- GdaTime atime = {13, 45, 59, 0, GDA_TIMEZONE_INVALID};
+ GdaTimestamp ts = {2011, 01, 31, 12, 34, 56, 0, 0};
+ GdaTime atime = {13, 45, 59, 0, 0};
GDate *adate;
GdaDataModel *model;
GError *error = NULL;
diff --git a/tests/providers/check_mysql.c b/tests/providers/check_mysql.c
index 3247eb2..d14890d 100644
--- a/tests/providers/check_mysql.c
+++ b/tests/providers/check_mysql.c
@@ -1,5 +1,5 @@
/*
- * Copyright (C) 2007 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2007 - 2013 Vivien Malerba <malerba gnome-db org>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
@@ -42,6 +42,7 @@ main (int argc, char **argv)
number_failed = prov_test_common_setup ();
if (cnc) {
+ number_failed += prov_test_common_check_timestamp ();
number_failed += prov_test_common_check_meta ();
number_failed += prov_test_common_clean ();
}
diff --git a/tests/providers/check_postgres.c b/tests/providers/check_postgres.c
index 9dfef83..e244cab 100644
--- a/tests/providers/check_postgres.c
+++ b/tests/providers/check_postgres.c
@@ -1,5 +1,5 @@
/*
- * Copyright (C) 2007 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2007 - 2013 Vivien Malerba <malerba gnome-db org>
* Copyright (C) 2012 Daniel Espinosa <despinosa src gnome org>
*
* This program is free software; you can redistribute it and/or
@@ -45,6 +45,7 @@ main (int argc, char **argv)
number_failed = prov_test_common_setup ();
if (cnc) {
+ number_failed += prov_test_common_check_timestamp ();
number_failed += prov_test_common_check_meta ();
number_failed += prov_test_common_check_meta_identifiers (TRUE, TRUE);
number_failed += prov_test_common_check_meta_identifiers (TRUE, FALSE);
diff --git a/tests/providers/check_sqlite.c b/tests/providers/check_sqlite.c
index 11fac25..8db20e3 100644
--- a/tests/providers/check_sqlite.c
+++ b/tests/providers/check_sqlite.c
@@ -1,5 +1,5 @@
/*
- * Copyright (C) 2007 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2007 - 2013 Vivien Malerba <malerba gnome-db org>
*
* This program is free software; you can redistribute it and/or
* modify it under the terms of the GNU General Public License
@@ -44,6 +44,7 @@ main (int argc, char **argv)
number_failed = prov_test_common_setup ();
if (cnc) {
+ number_failed += prov_test_common_check_timestamp ();
number_failed += prov_test_common_check_meta ();
number_failed += prov_test_common_check_meta_identifiers (TRUE, TRUE);
number_failed += prov_test_common_check_meta_identifiers (TRUE, FALSE);
diff --git a/tests/providers/prov-test-common.c b/tests/providers/prov-test-common.c
index b8a135e..ce91185 100644
--- a/tests/providers/prov-test-common.c
+++ b/tests/providers/prov-test-common.c
@@ -1,5 +1,5 @@
/*
- * Copyright (C) 2007 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2007 - 2013 Vivien Malerba <malerba gnome-db org>
* Copyright (C) 2011 Murray Cumming <murrayc murrayc com>
* Copyright (C) 2012 Daniel Espinosa <despinosa src gnome org>
*
@@ -42,7 +42,7 @@ gboolean fork_tests = TRUE;
*/
int
-prov_test_common_setup ()
+prov_test_common_setup (void)
{
int number_failed = 0;
GError *error = NULL;
@@ -90,7 +90,7 @@ prov_test_common_setup ()
*
*/
int
-prov_test_common_clean ()
+prov_test_common_clean (void)
{
int number_failed = 0;
@@ -111,7 +111,7 @@ prov_test_common_clean ()
*/
int
-prov_test_common_check_meta ()
+prov_test_common_check_meta (void)
{
int number_failed = 0;
GSList *tables = NULL, *list;
@@ -529,7 +529,7 @@ prov_test_common_check_meta_identifiers (gboolean case_sensitive, gboolean updat
*
*/
int
-prov_test_common_load_data ()
+prov_test_common_load_data (void)
{
int number_failed = 0;
@@ -555,7 +555,7 @@ prov_test_common_load_data ()
*
*/
int
-prov_test_common_check_cursor_models ()
+prov_test_common_check_cursor_models (void)
{
int number_failed = 0;
@@ -584,7 +584,7 @@ prov_test_common_check_cursor_models ()
*
*/
int
-prov_test_common_check_data_select ()
+prov_test_common_check_data_select (void)
{
GdaSqlParser *parser = NULL;
GdaStatement *stmt = NULL;
@@ -696,3 +696,120 @@ prov_test_common_check_data_select ()
return number_failed;
}
+
+
+/*
+ * Check that timezones are handled correctly when storing and retreiving timestamps
+ */
+static gboolean
+gda_timestamp_equal (const GValue *cv1, const GValue *cv2)
+{
+ g_assert (G_VALUE_TYPE (cv1) == GDA_TYPE_TIMESTAMP);
+ g_assert (G_VALUE_TYPE (cv2) == GDA_TYPE_TIMESTAMP);
+ const GdaTimestamp *ts1, *ts2;
+ ts1 = gda_value_get_timestamp (cv1);
+ ts2 = gda_value_get_timestamp (cv2);
+ if (ts1->timezone == ts2->timezone)
+ return gda_value_differ (cv1, cv2) ? FALSE : TRUE;
+
+ GdaTimestamp *ts;
+ ts = gda_timestamp_copy ((GdaTimestamp*) ts1);
+ gda_timestamp_change_timezone (ts, ts2->timezone);
+ gboolean res;
+ res = memcmp (ts2, ts, sizeof (GdaTimestamp)) ? FALSE : TRUE;
+ gda_timestamp_free (ts);
+ return res;
+}
+
+int
+prov_test_common_check_timestamp (void)
+{
+ GdaSqlParser *parser = NULL;
+ GdaStatement *stmt = NULL;
+ GdaSet *params = NULL;
+ GError *error = NULL;
+ int number_failed = 0;
+
+#ifdef CHECK_EXTRA_INFO
+ g_print ("\n============= %s() =============\n", __FUNCTION__);
+#endif
+
+ parser = gda_connection_create_parser (cnc);
+ if (!parser)
+ parser = gda_sql_parser_new ();
+
+ GValue *tso;
+ tso = gda_value_new_timestamp_from_timet (time (NULL));
+
+ /* insert timestamp */
+ stmt = gda_sql_parser_parse_string (parser, "INSERT INTO tstest (ts) VALUES (##ts::timestamp)",
+ NULL, &error);
+ if (!stmt ||
+ ! gda_statement_get_parameters (stmt, ¶ms, &error) ||
+ ! gda_set_set_holder_value (params, &error, "ts", gda_value_get_timestamp (tso)) ||
+ (gda_connection_statement_execute_non_select (cnc, stmt, params, NULL, &error) == -1)) {
+ number_failed ++;
+ goto out;
+ }
+
+ g_print ("Inserted TS %s\n", gda_value_stringify (tso));
+
+ /* retreive timestamp */
+ stmt = gda_sql_parser_parse_string (parser, "SELECT ts FROM tstest",
+ NULL, &error);
+ if (!stmt) {
+ number_failed ++;
+ goto out;
+ }
+
+ GdaDataModel *model;
+ model = gda_connection_statement_execute_select (cnc, stmt, NULL, &error);
+ if (!model) {
+ number_failed ++;
+ goto out;
+ }
+ gda_data_model_dump (model, NULL);
+ if (gda_data_model_get_n_rows (model) != 1) {
+ g_set_error (&error, TEST_ERROR, TEST_ERROR_GENERIC,
+ "Data model should have exactly 1 row");
+ number_failed ++;
+ goto out;
+ }
+
+ const GValue *cvalue;
+ cvalue = gda_data_model_get_typed_value_at (model, 0, 0, GDA_TYPE_TIMESTAMP, FALSE, &error);
+ if (!cvalue) {
+ number_failed ++;
+ goto out;
+ }
+ if (! gda_timestamp_equal (tso, cvalue)) {
+ gchar *tmpg, *tmpe;
+ tmpg = gda_value_stringify (cvalue);
+ tmpe = gda_value_stringify (tso);
+ g_set_error (&error, TEST_ERROR, TEST_ERROR_GENERIC,
+ "Retreived time stamp differs from expected: got '%s' and expected '%s'", tmpg,
tmpe);
+ g_free (tmpg);
+ g_free (tmpe);
+ number_failed ++;
+ goto out;
+ }
+
+out:
+ if (stmt)
+ g_object_unref (stmt);
+ if (params)
+ g_object_unref (params);
+ if (model)
+ g_object_unref (model);
+ g_object_unref (parser);
+
+#ifdef CHECK_EXTRA_INFO
+ g_print ("Timestamp test resulted in %d error(s)\n", number_failed);
+ if (number_failed != 0)
+ g_print ("error: %s\n", error && error->message ? error->message : "No detail");
+ if (error)
+ g_error_free (error);
+#endif
+
+ return number_failed;
+}
diff --git a/tests/providers/prov-test-common.h b/tests/providers/prov-test-common.h
index 521eda5..0479e29 100644
--- a/tests/providers/prov-test-common.h
+++ b/tests/providers/prov-test-common.h
@@ -1,5 +1,5 @@
/*
- * Copyright (C) 2007 - 2011 Vivien Malerba <malerba gnome-db org>
+ * Copyright (C) 2007 - 2013 Vivien Malerba <malerba gnome-db org>
* Copyright (C) 2011 Murray Cumming <murrayc murrayc com>
*
* This program is free software; you can redistribute it and/or
@@ -24,12 +24,13 @@
#include <libgda/libgda.h>
#include "prov-test-util.h"
-int prov_test_common_setup ();
-int prov_test_common_load_data ();
-int prov_test_common_check_meta ();
+int prov_test_common_setup (void);
+int prov_test_common_load_data (void);
+int prov_test_common_check_meta (void);
int prov_test_common_check_meta_identifiers (gboolean case_sensitive, gboolean update_all);
-int prov_test_common_check_cursor_models ();
-int prov_test_common_check_data_select ();
-int prov_test_common_clean ();
+int prov_test_common_check_cursor_models (void);
+int prov_test_common_check_data_select (void);
+int prov_test_common_check_timestamp (void);
+int prov_test_common_clean (void);
#endif
diff --git a/tests/providers/prov_dbstruct.xml b/tests/providers/prov_dbstruct.xml
index 46e2fa6..6ee5ace 100644
--- a/tests/providers/prov_dbstruct.xml
+++ b/tests/providers/prov_dbstruct.xml
@@ -117,4 +117,9 @@
<definition>SELECT * FROM films ORDER BY last_update</definition>
</view>
+ <!-- testing timestamps -->
+ <table name="tstest">
+ <column name="ts" type="timestamp"/>
+ </table>
+
</schema>
diff --git a/tests/test-sql-renderer.c b/tests/test-sql-renderer.c
new file mode 100644
index 0000000..11b6b45
--- /dev/null
+++ b/tests/test-sql-renderer.c
@@ -0,0 +1,291 @@
+/*
+ * Copyright (C) 2013 Vivien Malerba <malerba gnome-db org>
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU General Public License
+ * as published by the Free Software Foundation; either version 2
+ * of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ *
+ * You should have received a copy of the GNU General Public License
+ * along with this program; if not, write to the Free Software
+ * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
+ */
+
+#include <libgda/libgda.h>
+#include <sql-parser/gda-sql-parser.h>
+#include <string.h>
+
+#define PROV_CLASS(provider) (GDA_SERVER_PROVIDER_CLASS (G_OBJECT_GET_CLASS (provider)))
+
+static gboolean
+string_equal_to_template (const gchar *str, const gchar *tmpl)
+{
+ const gchar *ptrs, *ptrt;
+ for (ptrs = str; *ptrs != '('; ptrs++);
+
+ for (ptrt = tmpl;
+ *ptrs && *ptrt;
+ ptrs++, ptrt++) {
+ if (*ptrt == '@')
+ continue;
+ if (*ptrs != *ptrt)
+ break;
+ }
+ if (*ptrs || *ptrt)
+ return FALSE;
+ else
+ return TRUE;
+}
+
+GdaTimestamp ts = {
+ .year = 2013,
+ .month = 8,
+ .day = 28,
+ .hour = 17,
+ .minute = 10,
+ .second = 23,
+ .timezone = 3600 * 2
+};
+GdaTime gt = {
+ .hour = 16,
+ .minute = 9,
+ .second = 22,
+ .timezone = - 3600 * 3
+};
+
+
+static guint
+do_a_test (GdaServerProvider *prov, GdaSqlParser *parser)
+{
+ guint nfailed = 0;
+ GdaStatement *stmt;
+ GdaSet *params;
+ gchar *sql;
+ GError *error = NULL;
+
+ /* SQL parsed as an INSERT statement */
+ sql = "INSERT INTO tstest VALUES (##ts::timestamp, ##time::time)";
+ stmt = gda_sql_parser_parse_string (parser, sql, NULL, &error);
+ if (!stmt) {
+ g_print ("Failed to parse [%s]: %s\n", sql, error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ nfailed ++;
+ goto endtest;
+ }
+
+ g_assert (gda_statement_get_statement_type (stmt) == GDA_SQL_STATEMENT_INSERT);
+ if (! gda_statement_get_parameters (stmt, ¶ms, &error)) {
+ g_print ("Failed to obtain parameters: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ nfailed ++;
+ goto endtest;
+ }
+
+ if (! gda_set_set_holder_value (params, &error, "ts", &ts)) {
+ g_print ("Failed to bind 'ts' parameter: %s\n", error && error->message ? error->message :
"No detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+
+ if (! gda_set_set_holder_value (params, &error, "time", >)) {
+ g_print ("Failed to bind 'time' parameter: %s\n", error && error->message ? error->message :
"No detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+
+ gchar *expected;
+ expected = "('@@@@@@@@@@ 17:10:23+2', '16:09:22-3')";
+ if (prov && PROV_CLASS (prov)->statement_to_sql)
+ sql = PROV_CLASS (prov)->statement_to_sql (prov, NULL, stmt, params, 0, NULL, &error);
+ else
+ sql = gda_statement_to_sql_extended (stmt, NULL, params, 0, NULL, &error);
+ if (!sql) {
+ g_print ("Failed to render as SQL: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+ if (!string_equal_to_template (sql, expected)) {
+ g_print ("Wrong rendered SQL: [%s] instead of [%s]\n", sql, expected);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ g_free (sql);
+ nfailed ++;
+ goto endtest;
+ }
+ g_free (sql);
+
+ expected = "('@@@@@@@@@@ 15:10:23', '19:09:22')";
+ if (prov && PROV_CLASS (prov)->statement_to_sql)
+ sql = PROV_CLASS (prov)->statement_to_sql (prov, NULL, stmt, params,
GDA_STATEMENT_SQL_TIMEZONE_TO_GMT, NULL, &error);
+ else
+ sql = gda_statement_to_sql_extended (stmt, NULL, params, GDA_STATEMENT_SQL_TIMEZONE_TO_GMT,
NULL, &error);
+ if (!sql) {
+ g_print ("Failed to render as SQL: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+ if (!string_equal_to_template (sql, expected)) {
+ g_print ("Wrong rendered SQL for GMT timezone: [%s] instead of [%s]\n", sql, expected);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ g_free (sql);
+ nfailed ++;
+ goto endtest;
+ }
+ g_free (sql);
+
+ /* SQL not parsed as a valid statement */
+ sql = "AAAA (##ts::timestamp, ##time::time)";
+ stmt = gda_sql_parser_parse_string (parser, sql, NULL, &error);
+ if (!stmt) {
+ g_print ("Failed to parse [%s]: %s\n", sql, error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ nfailed ++;
+ goto endtest;
+ }
+
+ g_assert (gda_statement_get_statement_type (stmt) == GDA_SQL_STATEMENT_UNKNOWN);
+ if (! gda_statement_get_parameters (stmt, ¶ms, &error)) {
+ g_print ("Failed to obtain parameters: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ nfailed ++;
+ goto endtest;
+ }
+
+ if (! gda_set_set_holder_value (params, &error, "ts", &ts)) {
+ g_print ("Failed to bind 'ts' parameter: %s\n", error && error->message ? error->message :
"No detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+
+ if (! gda_set_set_holder_value (params, &error, "time", >)) {
+ g_print ("Failed to bind 'time' parameter: %s\n", error && error->message ? error->message :
"No detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+
+ expected = "('@@@@@@@@@@ 17:10:23+2', '16:09:22-3')";
+ if (prov && PROV_CLASS (prov)->statement_to_sql)
+ sql = PROV_CLASS (prov)->statement_to_sql (prov, NULL, stmt, params, 0, NULL, &error);
+ else
+ sql = gda_statement_to_sql_extended (stmt, NULL, params, 0, NULL, &error);
+ if (!sql) {
+ g_print ("Failed to render as SQL: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+ if (!string_equal_to_template (sql, expected)) {
+ g_print ("Wrong rendered SQL: [%s] instead of [%s]\n", sql, expected);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ g_free (sql);
+ nfailed ++;
+ goto endtest;
+ }
+ g_free (sql);
+
+ expected = "('@@@@@@@@@@ 15:10:23', '19:09:22')";
+ if (prov && PROV_CLASS (prov)->statement_to_sql)
+ sql = PROV_CLASS (prov)->statement_to_sql (prov, NULL, stmt, params,
GDA_STATEMENT_SQL_TIMEZONE_TO_GMT, NULL, &error);
+ else
+ sql = gda_statement_to_sql_extended (stmt, NULL, params, GDA_STATEMENT_SQL_TIMEZONE_TO_GMT,
NULL, &error);
+ if (!sql) {
+ g_print ("Failed to render as SQL: %s\n", error && error->message ? error->message : "No
detail");
+ g_clear_error (&error);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ nfailed ++;
+ goto endtest;
+ }
+ if (!string_equal_to_template (sql, expected)) {
+ g_print ("Wrong rendered SQL for GMT timezone: [%s] instead of [%s]\n", sql, expected);
+ g_object_unref (stmt);
+ g_object_unref (params);
+ g_free (sql);
+ nfailed ++;
+ goto endtest;
+ }
+ g_free (sql);
+
+ endtest:
+ return nfailed;
+}
+
+int
+main (int argc, char** argv)
+{
+ gchar *file;
+ GError *error = NULL;
+ GdaDataModel *model;
+ guint i, nrows;
+ guint nfailed = 0;
+ gda_init ();
+
+ /* generic parser */
+ GdaSqlParser *parser;
+ parser = gda_sql_parser_new ();
+ nfailed += do_a_test (NULL, parser);
+ g_object_unref (parser);
+
+ /* test other parsers only if generic one is Ok */
+ if (nfailed == 0) {
+ model = gda_config_list_providers ();
+ nrows = gda_data_model_get_n_rows (model);
+ for (i = 0; i < nrows; i++) {
+ const GValue *cvalue;
+ cvalue = gda_data_model_get_value_at (model, 0, i, NULL);
+ g_assert (cvalue && (G_VALUE_TYPE (cvalue) == G_TYPE_STRING));
+ g_print ("Testing database provider '%s'\n", g_value_get_string (cvalue));
+
+ GdaServerProvider *prov;
+ prov = gda_config_get_provider (g_value_get_string (cvalue), NULL);
+ g_assert (prov);
+
+ GdaSqlParser *parser;
+ parser = gda_server_provider_create_parser (prov, NULL);
+ if (!parser)
+ parser = gda_sql_parser_new ();
+
+ nfailed += do_a_test (prov, parser);
+ g_object_unref (parser);
+ }
+ g_object_unref (model);
+ }
+
+ if (nfailed == 0) {
+ g_print ("Ok\n");
+ return EXIT_SUCCESS;
+ }
+ else {
+ g_print ("%u failed\n", nfailed);
+ return EXIT_FAILURE;
+ }
+}
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
