GNOME.org
 

[gnome-db] Patch for postgres provider

	Hi!

	The patch attached make the postgres provider not to use *OID
	#define's any more. So it will compile against PostgreSQL 7.2
	version. 
	
	I'm in doubt about one thing: the recordset is getting
	information from the private data of the connection and I don't
	know if the way I do is the 'correct' one. Suggestions for doing
	it in a smarter way?

	Also includes part of the SCHEMA_FIELD implementation (not yet
	functional).

	Any comments appreciated.
	

Index: providers/postgres/gda-postgres-provider.c
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/gda-postgres-provider.c,v
retrieving revision 1.15
diff -u -r1.15 gda-postgres-provider.c
--- providers/postgres/gda-postgres-provider.c	25 Feb 2002 23:39:03 -0000	1.15
+++ providers/postgres/gda-postgres-provider.c	8 Mar 2002 02:22:35 -0000
@@ -21,9 +21,9 @@
  * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
  */
 
+#include <stdlib.h>
 #include "gda-postgres.h"
 #include "gda-postgres-provider.h"
-#include <stdlib.h>
 
 static void gda_postgres_provider_class_init (GdaPostgresProviderClass *klass);
 static void gda_postgres_provider_init       (GdaPostgresProvider *provider,
@@ -69,16 +69,6 @@
 							     GNOME_Database_Connection_Schema schema,
 							     GdaParameterList *params);
 
-typedef struct {
-	Oid oid;
-	gchar *name;
-} GdaPostgresTypeDataPrivate;
-
-typedef struct {
-	PGconn *pconn;	
-	GList *type_list; // list of GdaPostgresTypeDataPrivate
-} GdaPostgresConnectionPrivate;
-
 static GObjectClass *parent_class = NULL;
 
 /*
@@ -143,14 +133,12 @@
 	return type;
 }
 
-static GList *
-get_connection_type_list (PGconn *pconn)
+static int
+get_connection_type_list (PGconn *pconn, GdaPostgresConnectionPrivate *priv_td)
 {
+	GdaPostgresTypeOid *td;
 	PGresult *pg_res;
-	GList *list;
-	GdaPostgresTypeDataPrivate *priv_td;
-	gint llen;
-	gint i;
+	gint nrows, i;
 
 	pg_res = PQexec (pconn, "SELECT oid, typname FROM pg_type "
 				"WHERE typrelid = 0 AND typname !~ '^_' "
@@ -161,21 +149,21 @@
 
 	if (pg_res == NULL || PQresultStatus (pg_res) != PGRES_TUPLES_OK) {
 		if (pg_res) PQclear (pg_res);
-		return NULL;
+		return -1;
 	}
 
-	list = NULL;
-	llen = PQntuples (pg_res);
-	for (i = 0; i < llen; i++) {
-		priv_td = g_new (GdaPostgresTypeDataPrivate, 1);	
-		priv_td->oid = atoi (PQgetvalue (pg_res, i, 0));
-		priv_td->name = g_strdup (PQgetvalue (pg_res, i, 1));
-		list = g_list_append (list, priv_td);
+	nrows = PQntuples (pg_res);
+	td = g_new0 (GdaPostgresTypeOid, nrows);
+	for (i = 0; i < nrows; i++) {
+		td[i].name = g_strdup (PQgetvalue (pg_res, i, 1));
+		td[i].oid = atoi (PQgetvalue (pg_res, i, 0));
 	}
 
 	PQclear (pg_res);
+	priv_td->ntypes = nrows;
+	priv_td->type_data = td;
 
-	return list;
+	return 0;
 }
 
 /* open_connection handler for the GdaPostgresProvider class */
@@ -197,7 +185,6 @@
 	const gchar *pq_requiressl;
 	gchar *conn_string;
 	GdaPostgresConnectionPrivate *priv_data;
-	GList *type_list;
 	PGconn *pconn;
 	PGresult *pg_res;
 
@@ -256,36 +243,26 @@
 	pg_res = PQexec (pconn, "SET DATESTYLE TO 'ISO'");
 	PQclear (pg_res);
 
-	type_list = get_connection_type_list (pconn);
-	if (type_list == NULL) {
+	priv_data = g_new0 (GdaPostgresConnectionPrivate, 1);
+	priv_data->pconn = pconn;
+	if (get_connection_type_list (pconn, priv_data) != 0) {
 		gda_server_connection_add_error (
 				cnc, gda_postgres_make_error (pconn));
 		PQfinish(pconn);
+		g_free (priv_data);
 		return FALSE;
 	}
 	
-	priv_data = g_new0 (GdaPostgresConnectionPrivate, 1);
-	priv_data->pconn = pconn;
-	priv_data->type_list = type_list;
 	g_object_set_data (G_OBJECT (cnc), OBJECT_DATA_POSTGRES_HANDLE, priv_data);
 	return TRUE;
 }
 
-static void
-free_type_list (gpointer data, gpointer user_data)
-{
-	GdaPostgresTypeDataPrivate *priv_td;
-
-	priv_td = data;
-	g_free (priv_td->name);
-	g_free (priv_td);
-}
-
 /* close_connection handler for the GdaPostgresProvider class */
 static gboolean
 gda_postgres_provider_close_connection (GdaServerProvider *provider, GdaServerConnection *cnc)
 {
 	GdaPostgresConnectionPrivate *priv_data;
+	gint i;
 
 	GdaPostgresProvider *pg_prv = (GdaPostgresProvider *) provider;
 
@@ -297,8 +274,11 @@
 		return FALSE;
 
 	PQfinish (priv_data->pconn);
-	g_list_foreach (priv_data->type_list, free_type_list, NULL);
-	g_list_free (priv_data->type_list);
+
+	for (i = 0; i < priv_data->ntypes; i++)
+		g_free (priv_data->type_data[i].name);
+	
+	g_free (priv_data->type_data);
 	g_free (priv_data);
 
 	g_object_set_data (G_OBJECT (cnc), OBJECT_DATA_POSTGRES_HANDLE, NULL);
@@ -539,14 +519,14 @@
 	GdaValue *value;
 	GList list;
 	GdaServerRecordsetModel *recset;
-	GdaPostgresTypeDataPrivate *priv_td;
+	GdaPostgresTypeOid *td;
 	
-	priv_td = data;
+	td = data;
 	recset = user_data;
 
 	g_return_if_fail (GDA_IS_SERVER_RECORDSET_MODEL (recset));
 
-	value = gda_value_new_string (priv_td->name);
+	value = gda_value_new_string (td->name);
 	list.data = value;
 	list.next = NULL;
 	list.prev = NULL;
@@ -561,6 +541,7 @@
 {
 	GdaServerRecordsetModel *recset;
 	GdaPostgresConnectionPrivate *priv_data;
+	gint i;
 
 	g_return_val_if_fail (GDA_IS_SERVER_CONNECTION (cnc), NULL);
 
@@ -574,7 +555,8 @@
 	/* fill the recordset */
 	priv_data = g_object_get_data (G_OBJECT (cnc), OBJECT_DATA_POSTGRES_HANDLE);
 
-	g_list_foreach (priv_data->type_list, add_string_row, recset);
+	for (i = 0; i < priv_data->ntypes; i++)
+		add_string_row (&priv_data->type_data[i], recset);
 
 	return GDA_SERVER_RECORDSET (recset);
 }
@@ -669,6 +651,106 @@
 	return recset;
 }
 
+static GdaServerRecordset *
+get_postgres_fields_metadata (GdaServerConnection *cnc, GdaParameterList *params)
+{
+	GdaPostgresConnectionPrivate *priv_data;
+	GdaServerRecordsetModel *recset;
+	gchar *query;
+	GdaParameter *par;
+	const gchar *tblname;
+	gint i, k, row_count, field_count;
+	PGresult *pg_res;
+
+	g_return_val_if_fail (GDA_IS_SERVER_CONNECTION (cnc), NULL);
+	g_return_val_if_fail (params != NULL, NULL);
+
+	par = gda_parameter_list_find (params, "name");
+	g_return_val_if_fail (par != NULL, NULL);
+
+	tblname = gda_value_get_string (gda_parameter_get_value (par));
+
+	g_return_val_if_fail (tblname != NULL, NULL);
+	
+	/* Gets:
+	 * 	column name
+	 * 	column type oid
+	 * 	column type modifier
+	 * 	not null indicator
+	 * 	has definition indicator
+	 * 	number of element in the table for the column
+	 */
+	query = g_strdup_printf (
+			"SELECT a.attname, a.atttypid, a.atttypmod, a.attnotnull, "
+			" 	a.atthasdef, a.attnumelname"
+			"FROM pg_class c, pg_attribute a "
+			"WHERE c.relname = '%s' and a.attnum > 0 and "
+			"      a.attrelid = c.oid "
+			"ORDER BY a.attnumelname", tblname);
+
+	priv_data = g_object_get_data (G_OBJECT (cnc), OBJECT_DATA_POSTGRES_HANDLE);
+
+	pg_res = PQexec(priv_data->pconn, query);
+	g_free (query);
+	if (pg_res == NULL)
+		return NULL;
+
+	row_count = PQntuples (pg_res);
+	field_count = PQnfields (pg_res);
+
+	/* 
+	 * The columns for the metadata recordset are:
+	 * 	name of the field: string
+	 * 	data type: integer (one of GDA_TYPE_*)
+	 * 	size: integer
+	 * 	scale: integer
+	 * 	primary key: boolean
+	 * 	unique key: boolean
+	 * 	references (name of table.field referenced): string
+	 * 	not null: boolean
+	 */
+	recset = GDA_SERVER_RECORDSET_MODEL (gda_server_recordset_model_new (cnc, 8));
+	for (i = 0; i < 8; i++) {
+		gint defined_size;
+		typedef struct {
+			gchar *col_name;
+			GdaType data_type;
+		} col_data;
+
+		col_data cols[8] = {
+			{ N_("Field name")	, GDA_TYPE_STRING  },
+			{ N_("Data type")	, GDA_TYPE_INTEGER },
+			{ N_("Size")		, GDA_TYPE_INTEGER },
+			{ N_("Scale")		, GDA_TYPE_INTEGER },
+			{ N_("Primary key?")	, GDA_TYPE_BOOLEAN },
+			{ N_("Unique index?")	, GDA_TYPE_BOOLEAN },
+			{ N_("References")	, GDA_TYPE_STRING  },
+			{ N_("Not null?")	, GDA_TYPE_BOOLEAN }
+			};
+
+		switch (cols[i].data_type) {
+			case GDA_TYPE_STRING:
+				defined_size = NAMEDATALEN; // defined by PostgresSQL
+				break;
+			case GDA_TYPE_INTEGER:
+				defined_size = 4;
+				break;
+			default:
+				defined_size = 1;
+		}
+
+		gda_server_recordset_model_set_field_defined_size (recset, i, defined_size);
+		gda_server_recordset_model_set_field_name (recset, i, cols[i].col_name);
+		gda_server_recordset_model_set_field_scale (recset, i, 0);
+		gda_server_recordset_model_set_field_gdatype (recset, i, cols[i].data_type);
+		// TODO: store the data for each row
+		for (k = 0; k < row_count; k++){
+		}
+	}
+
+	return GDA_SERVER_RECORDSET (recset);
+}
+
 /* get_schema handler for the GdaPostgresProvider class */
 static GdaServerRecordset *
 gda_postgres_provider_get_schema (GdaServerProvider *provider,
@@ -694,6 +776,8 @@
 		return get_postgres_aggregates (cnc, params);
 	case GNOME_Database_Connection_SCHEMA_TRIGGERS :
 		return get_postgres_triggers (cnc, params);
+	case GNOME_Database_Connection_SCHEMA_FIELDS :
+		return get_postgres_fields_metadata (cnc, params);
 	}
 
 	return NULL;
Index: providers/postgres/gda-postgres-provider.h
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/gda-postgres-provider.h,v
retrieving revision 1.2
diff -u -r1.2 gda-postgres-provider.h
--- providers/postgres/gda-postgres-provider.h	7 Jan 2002 23:15:45 -0000	1.2
+++ providers/postgres/gda-postgres-provider.h	8 Mar 2002 02:22:35 -0000
@@ -26,6 +26,7 @@
 
 #include <libgda/gda-server-provider.h>
 #include <libgda/gda-server-recordset-model.h>
+#include <libpq-fe.h>
 
 #define GDA_TYPE_POSTGRES_PROVIDER            (gda_postgres_provider_get_type())
 #define GDA_POSTGRES_PROVIDER(obj)            (G_TYPE_CHECK_INSTANCE_CAST (obj, GDA_TYPE_POSTGRES_PROVIDER, GdaPostgresProvider))
@@ -47,6 +48,19 @@
 struct _GdaPostgresProviderClass {
 	GdaServerProviderClass parent_class;
 };
+
+// Private connection data.
+typedef struct {
+	gchar *name;
+	Oid oid;
+} GdaPostgresTypeOid;
+
+typedef struct {
+	PGconn *pconn;
+	gint ntypes;
+	GdaPostgresTypeOid *type_data;
+} GdaPostgresConnectionPrivate;
+
 
 G_BEGIN_DECLS
 
Index: providers/postgres/gda-postgres-recordset.c
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/gda-postgres-recordset.c,v
retrieving revision 1.7
diff -u -r1.7 gda-postgres-recordset.c
--- providers/postgres/gda-postgres-recordset.c	14 Jan 2002 23:41:08 -0000	1.7
+++ providers/postgres/gda-postgres-recordset.c	8 Mar 2002 02:22:35 -0000
@@ -24,14 +24,66 @@
  */
 
 #include <bonobo/bonobo-i18n.h>
+#include <string.h>
 #include "gda-postgres.h"
 
 #define OBJECT_DATA_RECSET_HANDLE "GDA_Postgres_RecsetHandle"
 
+typedef struct {
+	PGresult *pg_res;
+	gint ntypes;
+	GdaPostgresTypeOid *type_data;
+} GdaPostgresRecordsetPrivate;
+
 /*
  * Private functions
  */
 
+static GdaType
+gda_postgres_type_to_gda (GdaServerRecordset *recset, Oid postgres_type)
+{
+	GdaPostgresRecordsetPrivate *priv_data;
+	gint i;
+
+	priv_data = g_object_get_data (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE);
+
+	for (i = 0; i < priv_data->ntypes; i++)
+		if (priv_data->type_data[i].oid == postgres_type) 
+			break;
+
+	if (priv_data->type_data[i].oid == postgres_type) {
+		gchar *name = priv_data->type_data[i].name;
+
+		if (!strcmp (name, "boolean"))
+			return GDA_TYPE_BOOLEAN;
+		if (!strcmp (name, "int8"))
+			return GDA_TYPE_BIGINT;
+		if (!strcmp (name, "int4") || !strcmp (name, "abstime") || !strcmp (name, "oid"))
+			return GDA_TYPE_INTEGER;
+		if (!strcmp (name, "int2"))
+			return GDA_TYPE_SMALLINT;
+		if (!strcmp (name, "float4"))
+			return GDA_TYPE_SINGLE;
+		// TODO: when we have a numeric type, move "numeric".
+		if (!strcmp (name, "float8") || !strcmp (name, "numeric"))
+			return GDA_TYPE_DOUBLE;
+		if (!strncmp (name, "timestamp", 9))
+			return GDA_TYPE_TIMESTAMP;
+		if (!strcmp (name, "date"))
+			return GDA_TYPE_DATE;
+		if (!strncmp (name, "time", 4))
+			return GDA_TYPE_TIME;
+		/*TODO: by now, this one not supported
+		if (!strncmp (name, "bit", 3))
+			return GDA_TYPE_BINARY;
+		*/
+		if (!strcmp (name, "point"))
+			return GDA_TYPE_GEOMETRIC_POINT;
+	}
+
+	return GDA_TYPE_STRING;
+}
+
 static void
 free_postgres_res (gpointer data)
 {
@@ -44,6 +96,7 @@
 static GdaRow *
 fetch_func (GdaServerRecordset *recset, gulong rownum)
 {
+	GdaPostgresRecordsetPrivate *priv_data;
 	GdaRow *row;
 	gint field_count;
 	gint row_count;
@@ -52,7 +105,8 @@
 
 	g_return_val_if_fail (GDA_IS_SERVER_RECORDSET (recset), NULL);
 
-	pg_res = g_object_get_data (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE);
+	priv_data = g_object_get_data (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE);
+	pg_res = priv_data->pg_res;
 	if (!pg_res) {
 		gda_server_connection_add_error_string (
 			gda_server_recordset_get_connection (recset),
@@ -82,7 +136,7 @@
 		field = gda_row_get_field (row, i);
 		thevalue = PQgetvalue(pg_res, rownum, i);
 
-		ftype = gda_postgres_type_to_gda (PQftype (pg_res, i));
+		ftype = gda_postgres_type_to_gda (recset, PQftype (pg_res, i));
 		isNull = *thevalue != '\0' ? 
 				FALSE : PQgetisnull (pg_res, rownum, i);
 
@@ -96,6 +150,7 @@
 static GdaRowAttributes *
 describe_func (GdaServerRecordset *recset)
 {
+	GdaPostgresRecordsetPrivate *priv_data;
 	PGresult *pg_res;
 	gint field_count;
 	gint i;
@@ -103,7 +158,8 @@
 
 	g_return_val_if_fail (GDA_IS_SERVER_RECORDSET (recset), NULL);
 
-	pg_res = g_object_get_data (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE);
+	priv_data = g_object_get_data (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE);
+	pg_res = priv_data->pg_res;
 	if (!pg_res) {
 		gda_server_connection_add_error_string (
 			gda_server_recordset_get_connection (recset),
@@ -123,7 +179,7 @@
 		field_attrs = gda_row_attributes_get_field (attrs, i);
 		gda_field_attributes_set_name (field_attrs, PQfname (pg_res, i));
 
-		ftype = gda_postgres_type_to_gda (PQftype (pg_res, i));
+		ftype = gda_postgres_type_to_gda (recset, PQftype (pg_res, i));
 		scale = (ftype == GDA_TYPE_DOUBLE) ? DBL_DIG :
 			(ftype == GDA_TYPE_SINGLE) ? FLT_DIG : 0;
 
@@ -146,13 +202,23 @@
 gda_postgres_recordset_new (GdaServerConnection *cnc, PGresult *pg_res)
 {
 	GdaServerRecordset *recset;
+	GdaPostgresRecordsetPrivate *priv_data;
+	GdaPostgresConnectionPrivate *cnc_priv_data;
 
 	g_return_val_if_fail (GDA_IS_SERVER_CONNECTION (cnc), NULL);
 	g_return_val_if_fail (pg_res != NULL, NULL);
 
 	recset = gda_server_recordset_new (cnc, fetch_func, describe_func);
+
+	cnc_priv_data = g_object_get_data (G_OBJECT (cnc), OBJECT_DATA_POSTGRES_HANDLE);
+
+	priv_data = g_new0 (GdaPostgresRecordsetPrivate, 1);
+	priv_data->pg_res = pg_res;
+	priv_data->ntypes = cnc_priv_data->ntypes;
+	priv_data->type_data = cnc_priv_data->type_data;
+
 	g_object_set_data_full (G_OBJECT (recset), OBJECT_DATA_RECSET_HANDLE,
-				pg_res, (GDestroyNotify) free_postgres_res);
+				priv_data, (GDestroyNotify) free_postgres_res);
 
 	return recset;
 }
Index: providers/postgres/gda-postgres.h
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/gda-postgres.h,v
retrieving revision 1.5
diff -u -r1.5 gda-postgres.h
--- providers/postgres/gda-postgres.h	10 Jan 2002 00:14:10 -0000	1.5
+++ providers/postgres/gda-postgres.h	8 Mar 2002 02:22:35 -0000
@@ -31,7 +31,6 @@
 #include <glib/gmacros.h>
 #include <bonobo/bonobo-i18n.h>
 #include <libgda/gda-server.h>
-#include <libpq-fe.h>
 #include "gda-postgres-provider.h"
 #include "gda-postgres-recordset.h"
 
@@ -45,7 +44,6 @@
  */
 
 GdaError *gda_postgres_make_error (PGconn *cnc);
-GdaType gda_postgres_type_to_gda (Oid postgres_type);
 void gda_postgres_set_field_data (GdaField *field, const gchar *fname,
 				GdaType type, const gchar *value, 
 				gint dbsize, gboolean isNull);
Index: providers/postgres/utils.c
===================================================================
RCS file: /cvs/gnome/libgda/providers/postgres/utils.c,v
retrieving revision 1.9
diff -u -r1.9 utils.c
--- providers/postgres/utils.c	15 Jan 2002 01:53:25 -0000	1.9
+++ providers/postgres/utils.c	8 Mar 2002 02:22:35 -0000
@@ -22,8 +22,8 @@
  * Boston, MA 02111-1307, USA.
  */
 
-#include <postgres.h>
-#include <catalog/pg_type.h>
+#include <stdlib.h>
+#include <string.h>
 #include "gda-postgres.h"
 
 GdaError *
@@ -44,48 +44,6 @@
 	gda_error_set_sqlstate (error, _("Not available"));
 
 	return error;
-}
-
-GdaType
-gda_postgres_type_to_gda (Oid postgres_type)
-{
-	switch (postgres_type) {
-	case BOOLOID :
-		return GDA_TYPE_BOOLEAN;
-	case BYTEAOID :
-	case CHAROID :
-	case NAMEOID :
-	case TEXTOID :
-	case BPCHAROID :
-	case VARCHAROID :
-		return GDA_TYPE_STRING;
-	case INT8OID :
-		return GDA_TYPE_BIGINT;
-	case INT4OID :
-	case RELTIMEOID :
-		return GDA_TYPE_INTEGER;
-	case INT2OID :
-		return GDA_TYPE_SMALLINT;
-	case FLOAT4OID :
-		return GDA_TYPE_SINGLE;
-	case FLOAT8OID :
-	case NUMERICOID :
-		return GDA_TYPE_DOUBLE;
-	case ABSTIMEOID :
-	case TIMESTAMPOID :
-		return GDA_TYPE_TIMESTAMP;
-	case DATEOID :
-		return GDA_TYPE_DATE;
-	case TIMEOID :
-	case TIMETZOID :
-		return GDA_TYPE_TIME;
-	case VARBITOID :
-		return GDA_TYPE_BINARY;
-	case POINTOID :
-		return GDA_TYPE_GEOMETRIC_POINT;
-	}
-
-	return GDA_TYPE_STRING;
 }
 
 /* Makes a point from a string like "(3.2,5.6)" */
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]