[gnome-db] mysql 4.x patch ++

[Stian Skjelstad <stian nixia no>]

Added a bunch of different stuff into one patch (Yeah, I'm a bad lazy
dude, with too little time) :-p


It contains:

libgda/gda-value.c:

handle timestamps that aren't fully compliant. Really, the patch could
have been done further, checking for valid length on all of the spots.

Remove a compile warning




gda-mysql-provider.c:

mysql 4.x GET_CONSTRAINTS support

mysql 5.x should now give full data on complex foreign keys (it was
missing the 2nd, 3rd, 4th etc column from the source table.




Stian Skjelstad

Files libgda-org/libgda/.libs/gda-server-provider.o and libgda/libgda/.libs/gda-server-provider.o differ
Files libgda-org/libgda/.libs/gda-value.o and libgda/libgda/.libs/gda-value.o differ
Files libgda-org/libgda/.libs/libgda-3.a and libgda/libgda/.libs/libgda-3.a differ
Files libgda-org/libgda/.libs/libgda-3.so and libgda/libgda/.libs/libgda-3.so differ
Files libgda-org/libgda/.libs/libgda-3.so.3 and libgda/libgda/.libs/libgda-3.so.3 differ
Files libgda-org/libgda/.libs/libgda-3.so.3.0.0 and libgda/libgda/.libs/libgda-3.so.3.0.0 differ
Files libgda-org/libgda/.libs/libgda-3.so.3.0.0T and libgda/libgda/.libs/libgda-3.so.3.0.0T differ
diff -ur libgda-org/libgda/gda-server-provider.c libgda/libgda/gda-server-provider.c
--- libgda-org/libgda/gda-server-provider.c	2006-11-06 18:11:52.000000000 +0100
+++ libgda/libgda/gda-server-provider.c	2006-11-06 18:24:01.000000000 +0100
@@ -810,7 +810,6 @@
 	if (model)
 		/* test model validity */
 		gda_server_provider_test_schema_model (model, schema, error);
-
 	return model;
 }
 
Files libgda-org/libgda/gda-server-provider.o and libgda/libgda/gda-server-provider.o differ
diff -ur libgda-org/libgda/gda-value.c libgda/libgda/gda-value.c
--- libgda-org/libgda/gda-value.c	2006-11-06 18:11:52.000000000 +0100
+++ libgda/libgda/gda-value.c	2006-11-06 18:47:14.000000000 +0100
@@ -759,11 +759,16 @@
 	timestamp->minute = atoi (as_string);
 	as_string += 3;
 	timestamp->second = atoi (as_string);
-	as_string += 3;
-	timestamp->fraction = atol (as_string) * 10; /* I have only hundredths of second */
-	as_string += 3;
-	timestamp->timezone = atol (as_string) * 60 * 60;
-
+	if (strlen(as_string)>=3)
+	{
+		as_string += 3;
+		timestamp->fraction = atol (as_string) * 10; /* I have only hundredths of second */
+		if (strlen(as_string)>=3)
+		{
+			as_string += 3;
+			timestamp->timezone = atol (as_string) * 60 * 60;
+		}
+	}
 	gda_value_set_timestamp (dest, timestamp);
 	g_free (timestamp);
 }
@@ -1767,7 +1772,7 @@
 	
 	const gchar *as_string;
 	long int lvalue;
-	gchar *endptr[1];
+	gchar *endptr;
 
 	g_return_if_fail (G_VALUE_HOLDS_STRING (src) &&
 			  (GDA_VALUE_HOLDS_SHORT (dest) || GDA_VALUE_HOLDS_USHORT (dest)));
@@ -1776,7 +1781,7 @@
 	
 	lvalue = strtol (as_string, &endptr, 10);
 	
-	if (*as_string != '\0' && **endptr == '\0') {
+	if (*as_string != '\0' && *endptr == '\0') {
 		if (GDA_VALUE_HOLDS_SHORT (dest))
 			gda_value_set_short (dest, (gshort) lvalue);
 		else
diff -ur libgda-org/providers/mysql/gda-mysql-provider.c libgda/providers/mysql/gda-mysql-provider.c
--- libgda-org/providers/mysql/gda-mysql-provider.c	2006-11-06 18:12:00.000000000 +0100
+++ libgda/providers/mysql/gda-mysql-provider.c	2006-11-10 20:07:43.000000000 +0100
@@ -1666,10 +1666,201 @@
 	}
 
 	mysql_free_result (mysql_res);
-
+	
 	return GDA_DATA_MODEL (recset);
 }
 
+static void
+mysql_get_constraints_form_create_table_line (GdaDataModelArray *recset, gchar *pos)
+{
+	/* try to parse a line like this:
+	 * "  CONSTRAINT `name` FOREIGN KEY (`fid1`,`fid2`) REFERENCES othertable(`id1`,`id2`)"
+	 */
+	gchar *cname = 0, *start, *stop;
+	GString *kname;
+	gchar *tname = 0;
+	GString *fname;
+	GList *value_list;
+	gchar *temp;
+	GValue *tmpval;
+
+	/* remove prefix spaces */
+	while ( *pos==' ')
+		pos++;
+
+	/* "CONSTRAINT "? */
+	if ( strncmp (pos, "CONSTRAINT ", 11) )
+		return;
+
+	pos += 11; /* strlen("CONSTRAINT ") */ 
+
+	/* find the start of the constraint name.. it should be a ` char */
+	if ( ! ( start = strchr (pos, '`') ) )
+		return;
+	/* we want the first character from the name */
+	start++;
+
+	/* find the ending constraint name.. it should be a ` char */
+	if ( ! ( stop = strchr (start, '`') ) )
+		return;
+
+	/* allocate cname, and store the constraint name */
+	cname = g_malloc ( stop - start  + 1);
+	g_strlcpy (cname, start, stop - start + 1);
+
+	pos = stop + 1;
+
+	/* remove spaces again */
+	while ( *pos == ' ' )
+		pos++;
+
+	/* "FOREIGN KEY "? */
+	if ( strncmp (pos, "FOREIGN KEY ", 12) )
+		goto cname_out;
+	/* this is a foreign key constraint */
+	pos += 12; /* strlen("FOREIGN KEY ") */
+
+	/* we should be able to locate a ( inside our string */
+	if ( ! ( pos = strchr (pos, '(') ) )
+		goto cname_out;
+	pos++;
+
+	/* we should now be at the character behind the ( */
+
+	kname = g_string_new (NULL);
+
+	while (1) {
+		/* find all the entries.. they are in this format "`a`, `b`, `c`".
+		 * The list is terminated by a ) character */
+		if ( ! ( start = strchr (pos, '`') ) )
+			goto kname_out;
+		start++;
+		if ( ! ( stop = strchr (start, '`') ) )
+			goto kname_out;
+		if ( kname->len )
+			g_string_append_c (kname, ',');
+		g_string_append_len (kname, start, stop - start);
+
+		pos = stop + 1;
+
+		while ( *pos == ' ')
+			pos++;
+		if ( *pos == ',' )
+			pos++;
+		else /* if (*pos==')')*/
+			break;
+	}
+	/* pass by the ) character.. but if we had an unexpected \0 character, don't crash */
+	if (*pos)
+		pos++;
+
+	/* remove spaces again */
+	while ( *pos == ' ' )
+		pos++;
+
+	/* we now expected the REFERENCES text */
+	if ( strncmp (pos, "REFERENCES ", 11) )
+		goto kname_out;
+
+	pos += 11;
+
+	/* and yeah... remove spaces */
+	while ( *pos == ' ' )
+		pos++;
+
+	/* we should now hit the name of the foreign table. It is embedded inside ` characters */
+	if ( ! ( start = strchr (pos, '`') ) )
+		goto kname_out;
+	start++;
+
+	if ( ! ( stop = strchr (start, '`') ) )
+		goto kname_out;
+	/* store it in tname */
+	tname = g_malloc ( stop - start + 1);
+	g_strlcpy (tname, start, stop - start + 1);
+
+	pos = stop + 1;
+
+	/* we now should find a ( character */
+	if ( ! ( start = strchr (pos, '(') ) )
+		goto tname_out;
+	pos = start + 1;
+
+	fname = g_string_new (NULL);
+
+	while (1) {
+		/* and after this character we find a list like above with keys */
+		if ( ! ( start = strchr (pos, '`') ) )
+			goto kname_out;
+		start++;
+		if ( ! ( stop = strchr (start, '`') ) )
+			goto kname_out;
+		if (fname->len)
+			g_string_append_c (fname, ',');
+		g_string_append_len (fname, start, stop - start);
+
+		pos = stop + 1;
+		while ( *pos == ' ')
+			pos++;
+		if ( *pos == ',' )
+			pos++;
+		else /* if (*pos==')')*/
+			break;
+	}
+	/* fill in the result into the result table */
+	g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), cname);
+	value_list = g_list_append (NULL, tmpval);
+
+	g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), "FOREIGN_KEY");
+	value_list = g_list_append (value_list, tmpval);
+
+	g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), kname->str);
+	value_list = g_list_append (value_list, tmpval);
+	
+	temp = g_strdup_printf ("%s(%s)", tname, fname->str);
+	g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), temp);
+	value_list = g_list_append (value_list, tmpval);
+	
+	value_list = g_list_append (value_list, gda_value_new_null ());
+
+	gda_data_model_append_values (GDA_DATA_MODEL (recset),
+	                              (const GList *) value_list, NULL);
+		
+	g_list_foreach (value_list, (GFunc) gda_value_free, NULL);
+	g_list_free (value_list);
+
+fname_out:
+	g_string_free (fname, TRUE);
+tname_out:
+	g_free (tname);
+kname_out:
+	g_string_free (kname, TRUE);
+cname_out:
+	g_free (cname);
+}
+
+static void
+mysql_get_constraints_from_create_table (GdaDataModelArray *recset, gchar *pos)
+{
+	/* skip the first line. nothing of interrest there */
+	if (pos=strchr(pos, '\n'))
+		pos++;
+	else
+		return;
+	while (*pos)
+	{
+		gchar *next;
+		if ( next = strchr (pos, '\n') )
+		{
+			*next=0;
+			next++;
+		} else
+			next = pos + strlen (pos);
+		mysql_get_constraints_form_create_table_line (recset, pos);
+		pos=next;
+	}
+}
+
 static GdaDataModel *
 get_mysql_constraints (GdaConnection *cnc, GdaParameterList *params)
 {
@@ -1716,7 +1907,7 @@
 
 	/* 
 	 * Obtaining list of columns 
-	 */	
+	 */
 	cmd_str = g_strdup_printf ("SHOW COLUMNS FROM %s", table_name);
 	rc = mysql_real_query (mysql, cmd_str, strlen (cmd_str));
 	g_free (cmd_str);
@@ -1831,6 +2022,7 @@
 	if (atoi (mysql->server_version) >= 5) {
 		gchar *current_cname = NULL;
 		GString *ref_string = NULL;
+		GString *src_string = NULL;
 		GList *value_list = NULL;
 		
 		cmd_str = g_strdup_printf ("SELECT CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION, REFERENCED_TABLE_SCHEMA, "
@@ -1861,12 +2053,16 @@
 
 				return NULL;
 			}
-		
-	
+
+
 			if ((! current_cname) || strcmp (current_cname, mysql_row[0])) {
 				/* new constraint */
 				if (value_list) {
 					/* complete and store current row */
+					g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), src_string->str);
+					value_list = g_list_append (value_list, tmpval);
+					g_string_free (src_string, TRUE);
+
 					g_string_append_c (ref_string, ')');
 					g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), ref_string->str);
 					value_list = g_list_append (value_list, tmpval);
@@ -1876,13 +2072,13 @@
 
 					gda_data_model_append_values (GDA_DATA_MODEL (recset),
 								      (const GList *) value_list, NULL);
-				
+
 					g_list_foreach (value_list, (GFunc) gda_value_free, NULL);
 					g_list_free (value_list);
 				}
 				if (current_cname) 
 					g_free (current_cname);
-			
+
 				/* prepare new constraint */
 				g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), mysql_row[0]);
 				value_list = g_list_append (NULL, tmpval);
@@ -1890,9 +2086,8 @@
 				g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), "FOREIGN_KEY");
 				value_list = g_list_append (value_list, tmpval);
 
-				g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), mysql_row[1]);
-				value_list = g_list_append (value_list, tmpval);
-			
+				src_string = g_string_new (mysql_row[1]);
+
 				ref_string = g_string_new (mysql_row[4]);
 				g_string_append_c (ref_string, '(');
 				g_string_append (ref_string, mysql_row[5]);
@@ -1901,11 +2096,17 @@
 			else {
 				g_string_append_c (ref_string, ',');
 				g_string_append (ref_string, mysql_row[5]);
+				g_string_append_c (src_string, ',');
+				g_string_append (src_string, mysql_row[1]);
 			}
 		}
 
 		if (value_list) {
 			/* complete and store current row */
+			g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), src_string->str);
+			value_list = g_list_append (value_list, tmpval);
+			g_string_free (src_string, TRUE);
+
 			g_string_append_c (ref_string, ')');
 			g_value_set_string (tmpval = gda_value_new (G_TYPE_STRING), ref_string->str);
 			value_list = g_list_append (value_list, tmpval);
@@ -1919,6 +2120,33 @@
 			g_list_foreach (value_list, (GFunc) gda_value_free, NULL);
 			g_list_free (value_list);
 		}
+		if (current_cname)
+			g_free(current_cname);
+		mysql_free_result (mysql_res);
+	} else {
+		gchar *temp;
+		MYSQL_ROW mysql_row;
+		cmd_str = g_strdup_printf ("SHOW CREATE TABLE %s",
+					   table_name);
+		rc = mysql_real_query (mysql, cmd_str, strlen (cmd_str));
+		g_free (cmd_str);
+		if (rc != 0) {
+			gda_connection_add_event (cnc, gda_mysql_make_error (mysql));
+			return NULL;
+		}
+		mysql_res = mysql_store_result (mysql);
+		
+		mysql_row = mysql_fetch_row (mysql_res);
+		if (!mysql_row) {
+			mysql_free_result (mysql_res);
+			g_object_unref (G_OBJECT (recset));
+
+			return NULL;
+		}
+		temp = g_strdup ( mysql_row[1] );
+		mysql_get_constraints_from_create_table (recset, mysql_row[1]);
+		g_free (temp);
+		mysql_free_result (mysql_res);
 	}
 
 	return GDA_DATA_MODEL (recset);