[libgda] Initial GdaDataPivot implementation
- From: Vivien Malerba <vivien src gnome org>
- To: commits-list gnome org
- Cc:
- Subject: [libgda] Initial GdaDataPivot implementation
- Date: Sun, 18 Sep 2011 20:10:37 +0000 (UTC)
commit 277ea0f873112c424749874dd9d11be8301ee270
Author: Vivien Malerba <malerba gnome-db org>
Date: Sun Sep 18 22:08:11 2011 +0200
Initial GdaDataPivot implementation
doc/C/libgda-5.0-docs.sgml | 1 +
doc/C/libgda-sections.txt | 19 +
libgda/Makefile.am | 6 +-
libgda/gda-data-pivot.c | 1629 +++++++++++++++++++++++++++++++++++++++
libgda/gda-data-pivot.h | 125 +++
libgda/libgda.h.in | 1 +
libgda/libgda.symbols | 5 +
tests/data-models/.gitignore | 1 +
tests/data-models/Makefile.am | 15 +-
tests/data-models/check_pivot.c | 201 +++++
tests/data-models/pivot.db | Bin 0 -> 3072 bytes
tools/gda-sql.1.in | 25 +
tools/gda-sql.c | 236 +++++-
13 files changed, 2221 insertions(+), 43 deletions(-)
---
diff --git a/doc/C/libgda-5.0-docs.sgml b/doc/C/libgda-5.0-docs.sgml
index 3134cdb..9760fd9 100644
--- a/doc/C/libgda-5.0-docs.sgml
+++ b/doc/C/libgda-5.0-docs.sgml
@@ -529,6 +529,7 @@
<xi:include href="xml/gda-column.xml"/>
<xi:include href="xml/gda-data-model-iter.xml"/>
<xi:include href="xml/gda-data-model-import.xml"/>
+ <xi:include href="xml/gda-data-pivot.xml"/>
<xi:include href="xml/gda-data-access-wrapper.xml"/>
<xi:include href="xml/gda-data-model-array.xml"/>
<xi:include href="xml/gda-row.xml"/>
diff --git a/doc/C/libgda-sections.txt b/doc/C/libgda-sections.txt
index 96b67e1..e43e90e 100644
--- a/doc/C/libgda-sections.txt
+++ b/doc/C/libgda-sections.txt
@@ -336,6 +336,25 @@ gda_data_access_wrapper_get_type
</SECTION>
<SECTION>
+<FILE>gda-data-pivot</FILE>
+<TITLE>GdaDataPivot</TITLE>
+GdaDataPivot
+GdaDataPivotClass
+GdaDataPivotPrivate
+gda_data_pivot_new
+GdaDataPivotFieldType
+gda_data_pivot_add_field
+gda_data_pivot_populate
+<SUBSECTION Standard>
+GDA_DATA_PIVOT
+GDA_DATA_PIVOT_CLASS
+GDA_IS_DATA_PIVOT
+GDA_IS_DATA_PIVOT_CLASS
+GDA_TYPE_DATA_PIVOT
+gda_data_pivot_get_type
+</SECTION>
+
+<SECTION>
<FILE>gda-data-model</FILE>
<TITLE>GdaDataModel</TITLE>
GdaDataModel
diff --git a/libgda/Makefile.am b/libgda/Makefile.am
index bce3d9d..432fc73 100644
--- a/libgda/Makefile.am
+++ b/libgda/Makefile.am
@@ -103,7 +103,8 @@ gda_headers = \
gda-util.h \
gda-value.h \
gda-xa-transaction.h \
- libgda-global-variables.h
+ libgda-global-variables.h \
+ gda-data-pivot.h
gda_built_sources= \
$(builddir)/libgda.h
@@ -160,7 +161,8 @@ gda_sources= \
gda-tree-mgr-select.c \
gda-util.c \
gda-value.c \
- gda-xa-transaction.c
+ gda-xa-transaction.c \
+ gda-data-pivot.c
libgda_sources = \
csv.h \
diff --git a/libgda/gda-data-pivot.c b/libgda/gda-data-pivot.c
new file mode 100644
index 0000000..c0e3c13
--- /dev/null
+++ b/libgda/gda-data-pivot.c
@@ -0,0 +1,1629 @@
+/*
+ * Copyright (C) 2011 Vivien Malerba <malerba gnome-db org>
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2 of the License, or (at your option) any later version.
+ *
+ * This library 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with this library; if not, write to the
+ * Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
+ * Boston, MA 02110-1301, USA.
+ */
+
+#undef GDA_DISABLE_DEPRECATED
+#include "gda-data-pivot.h"
+
+#include <string.h>
+#include <glib/gi18n-lib.h>
+#include <libgda/gda-enums.h>
+#include <libgda/gda-holder.h>
+#include <libgda/gda-data-model.h>
+#include <libgda/gda-data-model-array.h>
+#include <libgda/gda-data-model-iter.h>
+#include <libgda/gda-row.h>
+#include <libgda/gda-util.h>
+#include <libgda/gda-blob-op.h>
+#include <libgda/gda-sql-builder.h>
+#include <virtual/libgda-virtual.h>
+#include <sql-parser/gda-sql-parser.h>
+#include <libgda/sql-parser/gda-sql-statement.h>
+
+typedef struct {
+ GValue *value;
+ gint column_fields_index;
+ gint data_pos; /* index in pivot->priv->data_fields, or -1 if no index */
+} ColumnData;
+static guint column_data_hash (gconstpointer key);
+static gboolean column_data_equal (gconstpointer a, gconstpointer b);
+static void column_data_free (ColumnData *cdata);
+
+
+typedef struct {
+ gint row;
+ gint col;
+ GArray *values; /* array of #GValue, none will be GDA_TYPE_NULL, and there is at least always 1 GValue */
+ GdaDataPivotAggregate aggregate;
+ GValue *computed_value;
+} CellData;
+static guint cell_data_hash (gconstpointer key);
+static gboolean cell_data_equal (gconstpointer a, gconstpointer b);
+static void cell_data_free (CellData *cdata);
+static void cell_data_compute_aggregate (CellData *cdata);
+
+static GValue *aggregate_get_empty_value (GdaDataPivotAggregate aggregate);
+
+
+struct _GdaDataPivotPrivate {
+ GdaDataModel *model; /* data to analyse */
+
+ GdaConnection *vcnc; /* to use data in @model for row and column fields */
+
+ GArray *row_fields; /* array of (gchar *) field specifications */
+ GArray *column_fields; /* array of (gchar *) field specifications */
+ GArray *data_fields; /* array of (gchar *) field specifications */
+ GArray *data_aggregates; /* array of GdaDataPivotAggregate, corresponding to @data_fields */
+
+ /* computed data */
+ GArray *columns; /* Array of GdaColumn objects, for ALL columns! */
+ GdaDataModel *results;
+};
+
+/* properties */
+enum
+{
+ PROP_0,
+ PROP_MODEL,
+};
+
+#define TABLE_NAME "data"
+
+static void gda_data_pivot_class_init (GdaDataPivotClass *klass);
+static void gda_data_pivot_init (GdaDataPivot *model,
+ GdaDataPivotClass *klass);
+static void gda_data_pivot_dispose (GObject *object);
+static void gda_data_pivot_finalize (GObject *object);
+
+static void gda_data_pivot_set_property (GObject *object,
+ guint param_id,
+ const GValue *value,
+ GParamSpec *pspec);
+static void gda_data_pivot_get_property (GObject *object,
+ guint param_id,
+ GValue *value,
+ GParamSpec *pspec);
+
+static guint _gda_value_hash (gconstpointer key);
+static guint _gda_row_hash (gconstpointer key);
+static gboolean _gda_row_equal (gconstpointer a, gconstpointer b);
+static gboolean create_vcnc (GdaDataPivot *pivot, GError **error);
+static gboolean bind_source_model (GdaDataPivot *pivot, GError **error);
+static void clean_previous_population (GdaDataPivot *pivot);
+
+/* GdaDataModel interface */
+static void gda_data_pivot_data_model_init (GdaDataModelIface *iface);
+static gint gda_data_pivot_get_n_rows (GdaDataModel *model);
+static gint gda_data_pivot_get_n_columns (GdaDataModel *model);
+static GdaColumn *gda_data_pivot_describe_column (GdaDataModel *model, gint col);
+static GdaDataModelAccessFlags gda_data_pivot_get_access_flags(GdaDataModel *model);
+static const GValue *gda_data_pivot_get_value_at (GdaDataModel *model, gint col, gint row, GError **error);
+
+static GObjectClass *parent_class = NULL;
+static GStaticMutex provider_mutex = G_STATIC_MUTEX_INIT;
+static GdaVirtualProvider *virtual_provider = NULL;
+
+/**
+ * gda_data_pivot_get_type
+ *
+ * Returns: the #GType of GdaDataPivot.
+ */
+GType
+gda_data_pivot_get_type (void)
+{
+ static GType type = 0;
+
+ if (G_UNLIKELY (type == 0)) {
+ static GStaticMutex registering = G_STATIC_MUTEX_INIT;
+ static const GTypeInfo info = {
+ sizeof (GdaDataPivotClass),
+ (GBaseInitFunc) NULL,
+ (GBaseFinalizeFunc) NULL,
+ (GClassInitFunc) gda_data_pivot_class_init,
+ NULL,
+ NULL,
+ sizeof (GdaDataPivot),
+ 0,
+ (GInstanceInitFunc) gda_data_pivot_init,
+ 0
+ };
+
+ static const GInterfaceInfo data_model_info = {
+ (GInterfaceInitFunc) gda_data_pivot_data_model_init,
+ NULL,
+ NULL
+ };
+
+ g_static_mutex_lock (®istering);
+ if (type == 0) {
+ type = g_type_register_static (G_TYPE_OBJECT, "GdaDataPivot", &info, 0);
+ g_type_add_interface_static (type, GDA_TYPE_DATA_MODEL, &data_model_info);
+ }
+ g_static_mutex_unlock (®istering);
+ }
+ return type;
+}
+
+static void
+gda_data_pivot_class_init (GdaDataPivotClass *klass)
+{
+ GObjectClass *object_class = G_OBJECT_CLASS (klass);
+
+ parent_class = g_type_class_peek_parent (klass);
+
+ /* properties */
+ object_class->set_property = gda_data_pivot_set_property;
+ object_class->get_property = gda_data_pivot_get_property;
+ g_object_class_install_property (object_class, PROP_MODEL,
+ g_param_spec_object ("model", NULL, "Data model from which data is analysed",
+ GDA_TYPE_DATA_MODEL,
+ G_PARAM_READABLE | G_PARAM_WRITABLE));
+
+ /* virtual functions */
+ object_class->dispose = gda_data_pivot_dispose;
+ object_class->finalize = gda_data_pivot_finalize;
+}
+
+static void
+gda_data_pivot_data_model_init (GdaDataModelIface *iface)
+{
+ iface->i_get_n_rows = gda_data_pivot_get_n_rows;
+ iface->i_get_n_columns = gda_data_pivot_get_n_columns;
+ iface->i_describe_column = gda_data_pivot_describe_column;
+ iface->i_get_access_flags = gda_data_pivot_get_access_flags;
+ iface->i_get_value_at = gda_data_pivot_get_value_at;
+ iface->i_get_attributes_at = NULL;
+
+ iface->i_create_iter = NULL;
+ iface->i_iter_at_row = NULL;
+ iface->i_iter_next = NULL;
+ iface->i_iter_prev = NULL;
+
+ iface->i_set_value_at = NULL;
+ iface->i_iter_set_value = NULL;
+ iface->i_set_values = NULL;
+ iface->i_append_values = NULL;
+ iface->i_append_row = NULL;
+ iface->i_remove_row = NULL;
+ iface->i_find_row = NULL;
+
+ iface->i_set_notify = NULL;
+ iface->i_get_notify = NULL;
+ iface->i_send_hint = NULL;
+}
+
+static void
+gda_data_pivot_init (GdaDataPivot *model, G_GNUC_UNUSED GdaDataPivotClass *klass)
+{
+ g_return_if_fail (GDA_IS_DATA_PIVOT (model));
+ model->priv = g_new0 (GdaDataPivotPrivate, 1);
+ model->priv->model = NULL;
+}
+
+static void
+clean_previous_population (GdaDataPivot *pivot)
+{
+ if (pivot->priv->results) {
+ g_object_unref ((GObject*) pivot->priv->results);
+ pivot->priv->results = NULL;
+ }
+
+ if (pivot->priv->columns) {
+ guint i;
+ for (i = 0; i < pivot->priv->columns->len; i++) {
+ GObject *obj;
+ obj = g_array_index (pivot->priv->columns, GObject*, i);
+ g_object_unref (obj);
+ }
+ g_array_free (pivot->priv->columns, TRUE);
+ pivot->priv->columns = NULL;
+ }
+}
+
+static void
+gda_data_pivot_dispose (GObject *object)
+{
+ GdaDataPivot *model = (GdaDataPivot *) object;
+
+ g_return_if_fail (GDA_IS_DATA_PIVOT (model));
+
+ /* free memory */
+ if (model->priv) {
+ clean_previous_population (model);
+
+ if (model->priv->row_fields) {
+ guint i;
+ for (i = 0; i < model->priv->row_fields->len; i++) {
+ gchar *tmp;
+ tmp = g_array_index (model->priv->row_fields, gchar*, i);
+ g_free (tmp);
+ }
+ g_array_free (model->priv->row_fields, TRUE);
+ model->priv->row_fields = NULL;
+ }
+
+ if (model->priv->column_fields) {
+ guint i;
+ for (i = 0; i < model->priv->column_fields->len; i++) {
+ gchar *tmp;
+ tmp = g_array_index (model->priv->column_fields, gchar*, i);
+ g_free (tmp);
+ }
+ g_array_free (model->priv->column_fields, TRUE);
+ model->priv->column_fields = NULL;
+ }
+
+ if (model->priv->data_fields) {
+ guint i;
+ for (i = 0; i < model->priv->data_fields->len; i++) {
+ gchar *tmp;
+ tmp = g_array_index (model->priv->data_fields, gchar*, i);
+ g_free (tmp);
+ }
+ g_array_free (model->priv->data_fields, TRUE);
+ model->priv->data_fields = NULL;
+ }
+
+ if (model->priv->data_aggregates) {
+ g_array_free (model->priv->data_aggregates, TRUE);
+ model->priv->data_aggregates = NULL;
+ }
+
+ if (model->priv->vcnc) {
+ g_object_unref (model->priv->vcnc);
+ model->priv->vcnc = NULL;
+ }
+
+ if (model->priv->model) {
+ g_object_unref (model->priv->model);
+ model->priv->model = NULL;
+ }
+ }
+
+ /* chain to parent class */
+ parent_class->dispose (object);
+}
+
+static void
+gda_data_pivot_finalize (GObject *object)
+{
+ GdaDataPivot *model = (GdaDataPivot *) object;
+
+ g_return_if_fail (GDA_IS_DATA_PIVOT (model));
+
+ /* free memory */
+ if (model->priv) {
+ g_free (model->priv);
+ model->priv = NULL;
+ }
+
+ /* chain to parent class */
+ parent_class->finalize (object);
+}
+
+/* module error */
+GQuark gda_data_pivot_error_quark (void)
+{
+ static GQuark quark;
+ if (!quark)
+ quark = g_quark_from_static_string ("gda_data_pivot_error");
+ return quark;
+}
+
+static void
+gda_data_pivot_set_property (GObject *object,
+ guint param_id,
+ const GValue *value,
+ GParamSpec *pspec)
+{
+ GdaDataPivot *model;
+
+ model = GDA_DATA_PIVOT (object);
+ if (model->priv) {
+ switch (param_id) {
+ case PROP_MODEL: {
+ GdaDataModel *mod = g_value_get_object (value);
+
+ clean_previous_population (model);
+
+ if (mod) {
+ g_return_if_fail (GDA_IS_DATA_MODEL (mod));
+
+ if (model->priv->model) {
+ if (model->priv->vcnc)
+ gda_vconnection_data_model_remove (GDA_VCONNECTION_DATA_MODEL (model->priv->vcnc),
+ TABLE_NAME, NULL);
+ g_object_unref (model->priv->model);
+ }
+
+ model->priv->model = mod;
+ g_object_ref (mod);
+ }
+ break;
+ }
+ default:
+ G_OBJECT_WARN_INVALID_PROPERTY_ID (object, param_id, pspec);
+ break;
+ }
+ }
+}
+
+static void
+gda_data_pivot_get_property (GObject *object,
+ guint param_id,
+ GValue *value,
+ GParamSpec *pspec)
+{
+ GdaDataPivot *model;
+
+ model = GDA_DATA_PIVOT (object);
+ if (model->priv) {
+ switch (param_id) {
+ case PROP_MODEL:
+ g_value_set_object (value, G_OBJECT (model->priv->model));
+ break;
+ default:
+ G_OBJECT_WARN_INVALID_PROPERTY_ID (object, param_id, pspec);
+ break;
+ }
+ }
+}
+
+/**
+ * gda_data_pivot_new:
+ * @model: (allow-none): a #GdaDataModel to analyse data from, or %NULL
+ *
+ * Creates a new #GdaDataModel which will contain analysed data from @model.
+ *
+ * Returns: a pointer to the newly created #GdaDataModel.
+ */
+GdaDataModel *
+gda_data_pivot_new (GdaDataModel *model)
+{
+ GdaDataPivot *retmodel;
+
+ g_return_val_if_fail (!model || GDA_IS_DATA_MODEL (model), NULL);
+
+ retmodel = g_object_new (GDA_TYPE_DATA_PIVOT,
+ "model", model, NULL);
+
+ return GDA_DATA_MODEL (retmodel);
+}
+
+/*
+ * GdaDataModel interface implementation
+ */
+static gint
+gda_data_pivot_get_n_rows (GdaDataModel *model)
+{
+ GdaDataPivot *pivot;
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (model), -1);
+ pivot = GDA_DATA_PIVOT (model);
+ g_return_val_if_fail (pivot->priv, -1);
+
+ if (pivot->priv->results)
+ return gda_data_model_get_n_rows (pivot->priv->results);
+ else
+ return -1;
+}
+
+static gint
+gda_data_pivot_get_n_columns (GdaDataModel *model)
+{
+ GdaDataPivot *pivot;
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (model), 0);
+ pivot = GDA_DATA_PIVOT (model);
+ g_return_val_if_fail (pivot->priv, 0);
+
+ if (pivot->priv->columns)
+ return (gint) pivot->priv->columns->len;
+ else
+ return 0;
+}
+
+static GdaColumn *
+gda_data_pivot_describe_column (GdaDataModel *model, gint col)
+{
+ GdaDataPivot *pivot;
+ GdaColumn *column = NULL;
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (model), NULL);
+ pivot = GDA_DATA_PIVOT (model);
+ g_return_val_if_fail (pivot->priv, NULL);
+
+ if (pivot->priv->columns && (col < (gint) pivot->priv->columns->len))
+ column = g_array_index (pivot->priv->columns, GdaColumn*, col);
+ else {
+ if (pivot->priv->columns->len > 0)
+ g_warning ("Column %d out of range (0-%d)", col,
+ (gint) pivot->priv->columns->len);
+ else
+ g_warning ("No column defined");
+ }
+
+ return column;
+}
+
+static GdaDataModelAccessFlags
+gda_data_pivot_get_access_flags (GdaDataModel *model)
+{
+ GdaDataPivot *pivot;
+
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (model), 0);
+ pivot = GDA_DATA_PIVOT (model);
+ g_return_val_if_fail (pivot->priv, 0);
+
+ return GDA_DATA_MODEL_ACCESS_RANDOM;
+}
+
+static const GValue *
+gda_data_pivot_get_value_at (GdaDataModel *model, gint col, gint row, GError **error)
+{
+ GdaDataPivot *pivot;
+
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (model), NULL);
+ pivot = GDA_DATA_PIVOT (model);
+ g_return_val_if_fail (pivot->priv, NULL);
+ g_return_val_if_fail (pivot->priv->model, NULL);
+ g_return_val_if_fail (row >= 0, NULL);
+ g_return_val_if_fail (col >= 0, NULL);
+
+ if (! pivot->priv->results) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_USAGE_ERROR,
+ "%s", _("Pivot model not populated"));
+ return NULL;
+ }
+ return gda_data_model_get_value_at (pivot->priv->results, col, row, error);
+}
+
+static GValue *
+aggregate_get_empty_value (GdaDataPivotAggregate aggregate)
+{
+ GValue *value;
+ switch (aggregate) {
+ case GDA_DATA_PIVOT_MIN:
+ case GDA_DATA_PIVOT_MAX:
+ case GDA_DATA_PIVOT_AVG:
+ return gda_value_new_null ();
+ case GDA_DATA_PIVOT_SUM:
+ case GDA_DATA_PIVOT_COUNT:
+ value = gda_value_new (G_TYPE_INT);
+ g_value_set_int (value, 0);
+ return value;
+ break;
+ default:
+ return gda_value_new_null ();
+ }
+}
+
+static gint64
+compute_for_gint (GdaDataPivotAggregate type, GArray *values)
+{
+ guint i;
+ gint64 res = 0;
+ for (i = 0; i < values->len; i++) {
+ GValue *v;
+ v = g_array_index (values, GValue*, i);
+ if (type == GDA_DATA_PIVOT_SUM)
+ res += g_value_get_int (v);
+ else {
+ if (i == 0)
+ res = g_value_get_int (v);
+ else if (type == GDA_DATA_PIVOT_MIN)
+ res = MIN (res, g_value_get_int (v));
+ else
+ res = MAX (res, g_value_get_int (v));
+ }
+ gda_value_free (v);
+ }
+ return res;
+}
+
+static gfloat
+compute_for_gfloat (GdaDataPivotAggregate type, GArray *values)
+{
+ guint i;
+ gfloat res = 0;
+ for (i = 0; i < values->len; i++) {
+ GValue *v;
+ v = g_array_index (values, GValue*, i);
+ if (type == GDA_DATA_PIVOT_SUM)
+ res += g_value_get_float (v);
+ else {
+ if (i == 0)
+ res = g_value_get_float (v);
+ else if (type == GDA_DATA_PIVOT_MIN)
+ res = MIN (res, g_value_get_float (v));
+ else
+ res = MAX (res, g_value_get_float (v));
+ }
+ gda_value_free (v);
+ }
+ return res;
+}
+
+static gdouble
+compute_for_gdouble (GdaDataPivotAggregate type, GArray *values)
+{
+ guint i;
+ gdouble res = 0;
+ for (i = 0; i < values->len; i++) {
+ GValue *v;
+ v = g_array_index (values, GValue*, i);
+ if (type == GDA_DATA_PIVOT_SUM)
+ res += g_value_get_double (v);
+ else {
+ if (i == 0)
+ res = g_value_get_double (v);
+ else if (type == GDA_DATA_PIVOT_MIN)
+ res = MIN (res, g_value_get_double (v));
+ else
+ res = MAX (res, g_value_get_double (v));
+ }
+ gda_value_free (v);
+ }
+ return res;
+}
+
+/*
+ * Sets cdata->computed_value to a #GValue
+ * if an error occurs then cdata->computed_value is set to %NULL
+ *
+ * Also frees cdata->values.
+ */
+static void
+cell_data_compute_aggregate (CellData *cdata)
+{
+ guint i;
+ switch (cdata->aggregate) {
+ case GDA_DATA_PIVOT_AVG:
+ case GDA_DATA_PIVOT_SUM: {
+ GValue *v;
+ g_assert (cdata->values && (cdata->values->len > 0));
+ v = g_array_index (cdata->values, GValue*, 0);
+ if ((G_VALUE_TYPE (v) == G_TYPE_INT) || (G_VALUE_TYPE (v) == G_TYPE_INT64) ||
+ (G_VALUE_TYPE (v) == G_TYPE_CHAR) || (G_VALUE_TYPE (v) == GDA_TYPE_SHORT)) {
+ gint64 sum;
+ sum = compute_for_gint (GDA_DATA_PIVOT_SUM, cdata->values);
+ if (cdata->aggregate == GDA_DATA_PIVOT_AVG) {
+ cdata->computed_value = gda_value_new (G_TYPE_DOUBLE);
+ g_value_set_double (cdata->computed_value,
+ sum / cdata->values->len);
+ }
+ else {
+ cdata->computed_value = gda_value_new (G_TYPE_INT64);
+ g_value_set_int64 (cdata->computed_value, sum);
+ }
+ }
+ else if (G_VALUE_TYPE (v) == G_TYPE_FLOAT) {
+ gfloat sum;
+ sum = compute_for_gfloat (GDA_DATA_PIVOT_SUM, cdata->values);
+ cdata->computed_value = gda_value_new (G_TYPE_FLOAT);
+ if (cdata->aggregate == GDA_DATA_PIVOT_AVG)
+ g_value_set_float (cdata->computed_value,
+ sum / cdata->values->len);
+ else
+ g_value_set_float (cdata->computed_value, sum);
+ }
+ else if (G_VALUE_TYPE (v) == G_TYPE_DOUBLE) {
+ gdouble sum;
+ sum = compute_for_gdouble (GDA_DATA_PIVOT_SUM, cdata->values);
+ cdata->computed_value = gda_value_new (G_TYPE_DOUBLE);
+ if (cdata->aggregate == GDA_DATA_PIVOT_AVG)
+ g_value_set_double (cdata->computed_value,
+ sum / cdata->values->len);
+ else
+ g_value_set_double (cdata->computed_value, sum);
+ }
+ else {
+ /* error: can't compute aggregate */
+ cdata->computed_value = NULL;
+ }
+ break;
+ }
+ case GDA_DATA_PIVOT_COUNT: {
+ guint64 count = 0;
+ for (i = 0; i < cdata->values->len; i++) {
+ GValue *value;
+ value = g_array_index (cdata->values, GValue*, i);
+ count += 1;
+ gda_value_free (value);
+ }
+ cdata->computed_value = gda_value_new (G_TYPE_UINT64);
+ g_value_set_uint64 (cdata->computed_value, count);
+ break;
+ }
+ case GDA_DATA_PIVOT_MAX:
+ case GDA_DATA_PIVOT_MIN: {
+ GValue *v;
+ g_assert (cdata->values && (cdata->values->len > 0));
+ v = g_array_index (cdata->values, GValue*, 0);
+ if (G_VALUE_TYPE (v) == G_TYPE_INT) {
+ gint64 res = 0;
+ res = compute_for_gint (cdata->aggregate, cdata->values);
+ cdata->computed_value = gda_value_new (G_TYPE_INT);
+ g_value_set_int (cdata->computed_value, (gint) res);
+ }
+ else if (G_VALUE_TYPE (v) == G_TYPE_FLOAT) {
+ gfloat res;
+ res = compute_for_gfloat (cdata->aggregate, cdata->values);
+ cdata->computed_value = gda_value_new (G_TYPE_FLOAT);
+ g_value_set_float (cdata->computed_value, res);
+ }
+ else if (G_VALUE_TYPE (v) == G_TYPE_DOUBLE) {
+ gdouble res;
+ res = compute_for_gdouble (cdata->aggregate, cdata->values);
+ cdata->computed_value = gda_value_new (G_TYPE_DOUBLE);
+ g_value_set_double (cdata->computed_value, res);
+ }
+ else {
+ /* error: can't compute aggregate */
+ cdata->computed_value = NULL;
+ }
+ break;
+ }
+ default:
+ TO_IMPLEMENT;
+ }
+
+ /* free tmp data (don't free each value, it has already been done) */
+ g_array_free (cdata->values, TRUE);
+ cdata->values = NULL;
+}
+
+static GdaSqlStatement *
+parse_field_spec (GdaDataPivot *pivot, const gchar *field, const gchar *alias, GError **error)
+{
+ GdaSqlParser *parser;
+ gchar *sql;
+ GdaStatement *stmt;
+ const gchar *remain;
+ GError *lerror = NULL;
+
+ g_return_val_if_fail (field, FALSE);
+
+ if (! bind_source_model (pivot, error))
+ return NULL;
+
+ parser = gda_connection_create_parser (pivot->priv->vcnc);
+ g_assert (parser);
+ if (alias)
+ sql = g_strdup_printf ("SELECT %s AS %s FROM " TABLE_NAME, field, alias);
+ else
+ sql = g_strdup_printf ("SELECT %s FROM " TABLE_NAME, field);
+ stmt = gda_sql_parser_parse_string (parser, sql, &remain, &lerror);
+ g_free (sql);
+ g_object_unref (parser);
+ if (!stmt || remain) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format error: %s"),
+ lerror && lerror->message ? lerror->message : _("No detail"));
+ g_clear_error (&lerror);
+ return NULL;
+ }
+
+ /* test syntax: a valid SQL expression is required */
+ GdaSqlStatement *sqlst = NULL;
+ g_object_get ((GObject*) stmt, "structure", &sqlst, NULL);
+
+ if (sqlst->stmt_type != GDA_SQL_STATEMENT_SELECT) {
+ g_object_unref (stmt);
+ gda_sql_statement_free (sqlst);
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ "%s", _("Wrong field format"));
+ return NULL;
+ }
+
+ /*
+ gchar *tmp = gda_sql_statement_serialize (sqlst);
+ g_print ("SQLST [%p, %s]\n", sqlst, tmp);
+ g_free (tmp);
+ */
+
+ /* further tests */
+ GdaDataModel *model;
+ model = gda_connection_statement_execute_select (pivot->priv->vcnc, stmt, NULL, &lerror);
+ g_object_unref (stmt);
+ if (!model) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format error: %s"),
+ lerror && lerror->message ? lerror->message : _("No detail"));
+ g_clear_error (&lerror);
+ return NULL;
+ }
+ /*
+ g_print ("================= Pivot source:\n");
+ gda_data_model_dump (model, NULL);
+ */
+
+ g_object_unref (model);
+ return sqlst;
+}
+
+/**
+ * gda_data_pivot_add_field:
+ * @pivot: a #GdaDataPivot object
+ * @field_type: the type of field to add
+ * @field: the field description, see below
+ * @alias: (allow-none): the field alias, or %NULL
+ * @error: (allow-none): ta place to store errors, or %NULL
+ *
+ * Specifies that @field has to be included in the analysis.
+ * @field is a field specification with the following accepted syntaxes:
+ * <itemizedlist>
+ * <listitem><para>a column name in the source data model (see <link linkend="gda-data-model-get-column-index">gda_data_model_get_column_index()</link>); or</para></listitem>
+ * <listitem><para>an SQL expression involving a column name in the source data model, for example:
+ * <programlisting>
+ * price
+ * firstname || ' ' || lastname
+ * nb BETWEEN 5 AND 10</programlisting>
+ * </para></listitem>
+ * </itemizedlist>
+ *
+ * It is also possible to specify several fields to be added, while separating them by a comma (in effect
+ * still forming a valid SQL syntax).
+ *
+ * Returns: %TRUE if no error occurred
+ *
+ * Since: 4.2.10
+ */
+gboolean
+gda_data_pivot_add_field (GdaDataPivot *pivot, GdaDataPivotFieldType field_type,
+ const gchar *field, const gchar *alias, GError **error)
+{
+ gchar *tmp;
+ GError *lerror = NULL;
+
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (pivot), FALSE);
+ g_return_val_if_fail (field, FALSE);
+
+ GdaSqlStatement *sqlst;
+ sqlst = parse_field_spec (pivot, field, alias, error);
+ if (! sqlst)
+ return FALSE;
+
+ GArray *array;
+ if (field_type == GDA_DATA_PIVOT_FIELD_ROW) {
+ if (! pivot->priv->row_fields)
+ pivot->priv->row_fields = g_array_new (FALSE, FALSE, sizeof (gchar*));
+ array = pivot->priv->row_fields;
+ }
+ else {
+ if (! pivot->priv->column_fields)
+ pivot->priv->column_fields = g_array_new (FALSE, FALSE, sizeof (gchar*));
+ array = pivot->priv->column_fields;
+ }
+
+ GdaSqlStatementSelect *sel;
+ GSList *sf_list;
+ sel = (GdaSqlStatementSelect*) sqlst->contents;
+ for (sf_list = sel->expr_list; sf_list; sf_list = sf_list->next) {
+ GdaSqlBuilder *b;
+ GdaSqlBuilderId bid;
+ GdaSqlSelectField *sf;
+ sf = (GdaSqlSelectField*) sf_list->data;
+ b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+ gda_sql_builder_select_add_target_id (b,
+ gda_sql_builder_add_id (b, "T"),
+ NULL);
+ bid = gda_sql_builder_import_expression (b, sf->expr);
+
+ if (bid == 0) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format"));
+ gda_sql_statement_free (sqlst);
+ return FALSE;
+ }
+ gda_sql_builder_add_field_value_id (b, bid, 0);
+ gchar *sql;
+ GdaStatement *stmt;
+ stmt = gda_sql_builder_get_statement (b, &lerror);
+ g_object_unref (b);
+ if (!stmt) {
+ gda_sql_statement_free (sqlst);
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format error: %s"),
+ lerror && lerror->message ? lerror->message : _("No detail"));
+ g_clear_error (&lerror);
+ return FALSE;
+ }
+ sql = gda_statement_to_sql (stmt, NULL, NULL);
+ g_object_unref (stmt);
+ if (!sql) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format"));
+ gda_sql_statement_free (sqlst);
+ return FALSE;
+ }
+ /*g_print ("PART [%s][%s]\n", sql, sf->as);*/
+ tmp = sql + 7; /* remove the "SELECT " start */
+ tmp [strlen (tmp) - 7] = 0; /* remove the " FROM T" end */
+ if (sf->as && *(sf->as)) {
+ gchar *tmp2;
+ tmp2 = g_strdup_printf ("%s AS %s", tmp, sf->as);
+ g_array_append_val (array, tmp2);
+ }
+ else {
+ tmp = g_strdup (tmp);
+ g_array_append_val (array, tmp);
+ }
+ g_free (sql);
+ }
+
+ gda_sql_statement_free (sqlst);
+
+ clean_previous_population (pivot);
+
+ return TRUE;
+}
+
+/**
+ * gda_data_pivot_add_data:
+ * @pivot: a #GdaDataPivot object
+ * @aggregate_type: the type of aggregate operation to perform
+ * @field: the field description, see below
+ * @alias: (allow-none): the field alias, or %NULL
+ * @error: (allow-none): ta place to store errors, or %NULL
+ *
+ * Specifies that @field has to be included in the analysis.
+ * @field is a field specification with the following accepted syntaxes:
+ * <itemizedlist>
+ * <listitem><para>a column name in the source data model (see <link linkend="gda-data-model-get-column-index">gda_data_model_get_column_index()</link>); or</para></listitem>
+ * <listitem><para>an SQL expression involving a column name in the source data model, for examples:
+ * <programlisting>
+ * price
+ * firstname || ' ' || lastname
+ * nb BETWEEN 5 AND 10</programlisting>
+ * </para></listitem>
+ * </itemizedlist>
+ *
+ * It is also possible to specify several fields to be added, while separating them by a comma (in effect
+ * still forming a valid SQL syntax).
+ *
+ * Returns: %TRUE if no error occurred
+ *
+ * Since: 4.2.10
+ */
+gboolean
+gda_data_pivot_add_data (GdaDataPivot *pivot, GdaDataPivotAggregate aggregate_type,
+ const gchar *field, const gchar *alias, GError **error)
+{
+ gchar *tmp;
+ GError *lerror = NULL;
+
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (pivot), FALSE);
+ g_return_val_if_fail (field, FALSE);
+
+ GdaSqlStatement *sqlst;
+ sqlst = parse_field_spec (pivot, field, alias, error);
+ if (! sqlst)
+ return FALSE;
+
+ if (! pivot->priv->data_fields)
+ pivot->priv->data_fields = g_array_new (FALSE, FALSE, sizeof (gchar*));
+ if (! pivot->priv->data_aggregates)
+ pivot->priv->data_aggregates = g_array_new (FALSE, FALSE, sizeof (GdaDataPivotAggregate));
+
+ GdaSqlStatementSelect *sel;
+ GSList *sf_list;
+ sel = (GdaSqlStatementSelect*) sqlst->contents;
+ for (sf_list = sel->expr_list; sf_list; sf_list = sf_list->next) {
+ GdaSqlBuilder *b;
+ GdaSqlBuilderId bid;
+ GdaSqlSelectField *sf;
+ sf = (GdaSqlSelectField*) sf_list->data;
+ b = gda_sql_builder_new (GDA_SQL_STATEMENT_SELECT);
+ gda_sql_builder_select_add_target_id (b,
+ gda_sql_builder_add_id (b, "T"),
+ NULL);
+ bid = gda_sql_builder_import_expression (b, sf->expr);
+
+ if (bid == 0) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format"));
+ gda_sql_statement_free (sqlst);
+ return FALSE;
+ }
+ gda_sql_builder_add_field_value_id (b, bid, 0);
+ gchar *sql;
+ GdaStatement *stmt;
+ stmt = gda_sql_builder_get_statement (b, &lerror);
+ g_object_unref (b);
+ if (!stmt) {
+ gda_sql_statement_free (sqlst);
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format error: %s"),
+ lerror && lerror->message ? lerror->message : _("No detail"));
+ g_clear_error (&lerror);
+ return FALSE;
+ }
+ sql = gda_statement_to_sql (stmt, NULL, NULL);
+ g_object_unref (stmt);
+ if (!sql) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ _("Wrong field format"));
+ gda_sql_statement_free (sqlst);
+ return FALSE;
+ }
+ /*g_print ("PART [%s][%s]\n", sql, sf->as);*/
+ tmp = sql + 7; /* remove the "SELECT " start */
+ tmp [strlen (tmp) - 7] = 0; /* remove the " FROM T" end */
+ if (sf->as && *(sf->as)) {
+ gchar *tmp2;
+ tmp2 = g_strdup_printf ("%s AS %s", tmp, sf->as);
+ g_array_append_val (pivot->priv->data_fields, tmp2);
+ }
+ else {
+ tmp = g_strdup (tmp);
+ g_array_append_val (pivot->priv->data_fields, tmp);
+ }
+ g_array_append_val (pivot->priv->data_aggregates, aggregate_type);
+ g_free (sql);
+ }
+
+ gda_sql_statement_free (sqlst);
+
+ clean_previous_population (pivot);
+
+ return TRUE;
+}
+
+/**
+ * gda_data_pivot_populate:
+ * @pivot: a #GdaDataPivot object
+ * @error: (allow-none): ta place to store errors, or %NULL
+ *
+ * Acutally populates @pivot by analysing the data from the provided data model.
+ *
+ * Returns: %TRUE if no error occurred.
+ *
+ * Since: 4.2.10
+ */
+gboolean
+gda_data_pivot_populate (GdaDataPivot *pivot, GError **error)
+{
+ gboolean retval = FALSE;
+ g_return_val_if_fail (GDA_IS_DATA_PIVOT (pivot), FALSE);
+
+ if (!pivot->priv->column_fields || (pivot->priv->column_fields->len == 0)) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_USAGE_ERROR,
+ "%s", _("No column field defined"));
+ return FALSE;
+ }
+
+ if (!pivot->priv->row_fields || (pivot->priv->row_fields->len == 0)) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_USAGE_ERROR,
+ "%s", _("No row field defined"));
+ return FALSE;
+ }
+
+ clean_previous_population (pivot);
+
+ /*
+ * create data model extracted using the virtual connection.
+ * The resulting data model's columns are:
+ * - for pivot->priv->row_fields: 0 to pivot->priv->row_fields->len - 1
+ * - for pivot->priv->column_fields:
+ * pivot->priv->row_fields->len to pivot->priv->row_fields->len + pivot->priv->column_fields->len - 1
+ * - for pivot->priv->data_fields: pivot->priv->row_fields->len + pivot->priv->column_fields->len to pivot->priv->row_fields->len + pivot->priv->column_fields->len + pivot->priv->data_fields->len - 1
+ */
+ GString *string;
+ guint i;
+ string = g_string_new ("SELECT ");
+ for (i = 0; i < pivot->priv->row_fields->len; i++) {
+ gchar *part;
+ part = g_array_index (pivot->priv->row_fields, gchar *, i);
+ if (i != 0)
+ g_string_append (string, ", ");
+ g_string_append (string, part);
+ }
+ for (i = 0; i < pivot->priv->column_fields->len; i++) {
+ gchar *part;
+ part = g_array_index (pivot->priv->column_fields, gchar *, i);
+ g_string_append (string, ", ");
+ g_string_append (string, part);
+ }
+ if (pivot->priv->data_fields) {
+ for (i = 0; i < pivot->priv->data_fields->len; i++) {
+ gchar *part;
+ part = g_array_index (pivot->priv->data_fields, gchar *, i);
+ g_string_append (string, ", ");
+ g_string_append (string, part);
+ }
+ }
+ g_string_append (string, " FROM " TABLE_NAME);
+
+ GdaStatement *stmt;
+ stmt = gda_connection_parse_sql_string (pivot->priv->vcnc, string->str, NULL, NULL);
+ g_string_free (string, TRUE);
+ if (!stmt) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_INTERNAL_ERROR,
+ "%s", _("Could not get information from source data model"));
+ return FALSE;
+ }
+
+ GdaDataModel *model;
+ model = gda_connection_statement_execute_select_full (pivot->priv->vcnc, stmt, NULL,
+ GDA_STATEMENT_MODEL_CURSOR_FORWARD,
+ //GDA_STATEMENT_MODEL_RANDOM_ACCESS,
+ NULL, NULL);
+ g_object_unref (stmt);
+ if (!model) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_INTERNAL_ERROR,
+ "%s", _("Could not get information from source data model"));
+ return FALSE;
+ }
+
+ /*
+ g_print ("========== Iterable data model\n");
+ gda_data_model_dump (model, NULL);
+ */
+
+ /* iterate through the data model */
+ GdaDataModelIter *iter;
+ gint col;
+ iter = gda_data_model_create_iter (model);
+ if (!iter) {
+ g_object_unref (model);
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_INTERNAL_ERROR,
+ "%s", _("Could not get information from source data model"));
+ return FALSE;
+ }
+
+ pivot->priv->columns = g_array_new (FALSE, FALSE, sizeof (GdaColumn*));
+ for (col = 0; col < (gint) pivot->priv->row_fields->len; col++) {
+ GdaColumn *column;
+ GdaHolder *holder;
+ column = gda_column_new ();
+ holder = GDA_HOLDER (g_slist_nth_data (GDA_SET (iter)->holders, col));
+
+ gda_column_set_name (column, gda_holder_get_id (holder));
+ gda_column_set_description (column, gda_holder_get_id (holder));
+ gda_column_set_g_type (column, gda_holder_get_g_type (holder));
+ gda_column_set_position (column, col);
+ g_array_append_val (pivot->priv->columns, column);
+ }
+
+ /*
+ * actual computation
+ */
+ GHashTable *column_values_index; /* key = a #GValue (ref held in @column_values),
+ * value = pointer to a guint containing the position
+ * in @columns of the column */
+
+ GArray *first_rows; /* Array of GdaRow objects, the size of each GdaRow is
+ * the number of row fields defined */
+ GHashTable *first_rows_index; /* key = a #GdaRow (ref held in @first_rows),
+ * value = pointer to a guint containing the position
+ * in @first_rows of the row. */
+ GHashTable *data_hash; /* key = a CellData pointer, value = The CellData pointer
+ * as ref'ed in @data */
+
+ first_rows = g_array_new (FALSE, FALSE, sizeof (GdaRow*));
+ first_rows_index = g_hash_table_new_full (_gda_row_hash, _gda_row_equal, NULL, g_free);
+ column_values_index = g_hash_table_new_full (column_data_hash, column_data_equal,
+ (GDestroyNotify) column_data_free, g_free);
+ data_hash = g_hash_table_new_full (cell_data_hash, cell_data_equal,
+ (GDestroyNotify) cell_data_free, NULL);
+
+ for (;gda_data_model_iter_move_next (iter);) {
+ /*
+ * Row handling
+ */
+ GdaRow *nrow;
+#ifdef GDA_DEBUG_ROWS_HASH
+ g_print ("read from iter: ");
+#endif
+ nrow = gda_row_new ((gint) pivot->priv->row_fields->len);
+ for (col = 0; col < (gint) pivot->priv->row_fields->len; col++) {
+ const GValue *ivalue;
+ GValue *rvalue;
+ GError *lerror = NULL;
+ ivalue = gda_data_model_iter_get_value_at_e (iter, col, &lerror);
+ if (!ivalue || lerror) {
+ clean_previous_population (pivot);
+ g_object_unref (nrow);
+ g_propagate_error (error, lerror);
+ goto out;
+ }
+ rvalue = gda_row_get_value (nrow, col);
+ gda_value_reset_with_type (rvalue, G_VALUE_TYPE (ivalue));
+ g_value_copy (ivalue, rvalue);
+#ifdef GDA_DEBUG_ROWS_HASH
+ gchar *tmp;
+ tmp = gda_value_stringify (rvalue);
+ g_print ("[%s]", tmp);
+ g_free (tmp);
+#endif
+ }
+
+ gint *rowindex; /* *rowindex will contain the actual row of the resulting data mode */
+ rowindex = g_hash_table_lookup (first_rows_index, nrow);
+ if (rowindex)
+ g_object_unref (nrow);
+ else {
+ g_array_append_val (first_rows, nrow);
+ rowindex = g_new (gint, 1);
+ *rowindex = first_rows->len - 1;
+ g_hash_table_insert (first_rows_index, nrow,
+ rowindex);
+ }
+#ifdef GDA_DEBUG_ROWS_HASH
+ g_print (" => @row %d\n", *rowindex);
+#endif
+
+ /*
+ * Column handling
+ */
+ for (col = 0; col < (gint) pivot->priv->column_fields->len;
+ col++) {
+ const GValue *ivalue;
+ GError *lerror = NULL;
+ ivalue = gda_data_model_iter_get_value_at_e (iter,
+ col + pivot->priv->row_fields->len,
+ &lerror);
+ if (!ivalue || lerror) {
+ clean_previous_population (pivot);
+ g_propagate_error (error, lerror);
+ goto out;
+ }
+
+ gint di, dimax;
+ if (pivot->priv->data_fields && pivot->priv->data_fields->len > 0) {
+ di = 0;
+ dimax = pivot->priv->data_fields->len;
+ }
+ else {
+ di = -1;
+ dimax = 0;
+ }
+ for (; di < dimax; di++) {
+ ColumnData coldata;
+ gint *colindex;
+ GdaDataPivotAggregate aggregate;
+ coldata.value = (GValue*) ivalue;
+ coldata.column_fields_index = col;
+ coldata.data_pos = di;
+ colindex = g_hash_table_lookup (column_values_index, &coldata);
+ if (di >= 0)
+ aggregate = g_array_index (pivot->priv->data_aggregates,
+ GdaDataPivotAggregate, di);
+ else
+ aggregate = GDA_DATA_PIVOT_SUM;
+
+ if (!colindex) {
+ /* create new column */
+ GdaColumn *column;
+ GString *name;
+ gchar *tmp;
+
+ name = g_string_new ("");
+ if (pivot->priv->column_fields->len > 1) {
+ GdaColumn *column;
+ column = gda_data_model_describe_column (model,
+ pivot->priv->row_fields->len + col);
+ g_string_append_printf (name, "[%s]",
+ gda_column_get_name (column));
+ }
+ tmp = gda_value_stringify (ivalue);
+ g_string_append (name, tmp);
+ g_free (tmp);
+ if ((di >= 0) && (dimax > 0)) {
+ GdaColumn *column;
+ column = gda_data_model_describe_column (model,
+ pivot->priv->row_fields->len + pivot->priv->column_fields->len + di);
+ g_string_append_printf (name, "[%s]",
+ gda_column_get_name (column));
+ }
+
+ column = gda_column_new ();
+ g_object_set_data ((GObject*) column, "agg",
+ GINT_TO_POINTER (aggregate));
+ gda_column_set_name (column, name->str);
+ gda_column_set_description (column, name->str);
+ g_array_append_val (pivot->priv->columns, column);
+ gda_column_set_position (column, pivot->priv->columns->len - 1);
+ /* don't set the column's type now */
+ /*g_print ("New column [%s] @real column %d, type %s\n", name->str, pivot->priv->columns->len - 1, gda_g_type_to_string (gda_column_get_g_type (column)));*/
+ g_string_free (name, TRUE);
+
+ ColumnData *ncoldata;
+ ncoldata = g_new (ColumnData, 1);
+ ncoldata->value = gda_value_copy ((GValue*) ivalue);
+ ncoldata->column_fields_index = col;
+ ncoldata->data_pos = di;
+ colindex = g_new (gint, 1);
+ *colindex = pivot->priv->columns->len - 1;
+ g_hash_table_insert (column_values_index, ncoldata,
+ colindex);
+ }
+
+ /* compute value to take into account */
+ GValue *value = NULL;
+ if (di >= 0) {
+ const GValue *cvalue;
+ GError *lerror = NULL;
+ cvalue = gda_data_model_iter_get_value_at_e (iter,
+ pivot->priv->row_fields->len + pivot->priv->column_fields->len + di,
+ &lerror);
+ if (!cvalue || lerror) {
+ g_object_unref (nrow);
+ g_propagate_error (error, lerror);
+ goto out;
+ }
+ if (G_VALUE_TYPE (cvalue) != GDA_TYPE_NULL)
+ value = gda_value_copy (cvalue);
+ }
+ else {
+ value = gda_value_new (G_TYPE_INT);
+ g_value_set_int (value, 1);
+ }
+
+ if (value) {
+ /* accumulate data */
+ CellData ccdata, *pcdata;
+ ccdata.row = *rowindex;
+ ccdata.col = *colindex;
+ ccdata.values = NULL;
+ ccdata.computed_value = NULL;
+ pcdata = g_hash_table_lookup (data_hash, &ccdata);
+ if (!pcdata) {
+ pcdata = g_new (CellData, 1);
+ pcdata->row = *rowindex;
+ pcdata->col = *colindex;
+ pcdata->values = NULL;
+ pcdata->computed_value = NULL;
+ pcdata->aggregate = aggregate;
+ g_hash_table_insert (data_hash, pcdata, pcdata);
+ }
+ if (!pcdata->values)
+ pcdata->values = g_array_new (FALSE, FALSE, sizeof (GValue*));
+ g_array_append_val (pcdata->values, value);
+ /*g_print ("row %d col %d => [%s]\n", pcdata->row, pcdata->col,
+ gda_value_stringify (value));*/
+ }
+ }
+ }
+ }
+ if (gda_data_model_iter_get_row (iter) != -1) {
+ /* an error occurred! */
+ g_print ("////////\n");
+ goto out;
+ }
+
+ /* compute real data model's values from all the collected data */
+ GdaDataModel *results;
+ gint ncols, nrows;
+ ncols = pivot->priv->columns->len;
+ nrows = first_rows->len;
+ results = gda_data_model_array_new (ncols);
+ for (i = 0; i < pivot->priv->row_fields->len; i++) {
+ GdaColumn *acolumn, *ecolumn;
+ ecolumn = g_array_index (pivot->priv->columns, GdaColumn*, i);
+ acolumn = gda_data_model_describe_column (results, i);
+ gda_column_set_g_type (acolumn, gda_column_get_g_type (ecolumn));
+
+ gint j;
+ for (j = 0; j < nrows; j++) {
+ GdaRow *arow, *erow;
+ erow = g_array_index (first_rows, GdaRow*, j);
+ arow = gda_data_model_array_get_row ((GdaDataModelArray*) results, j, NULL);
+ if (!arow) {
+ g_assert (gda_data_model_append_row (results, NULL) == j);
+ arow = gda_data_model_array_get_row ((GdaDataModelArray*) results, j,
+ NULL);
+ g_assert (arow);
+ }
+ GValue *av, *ev;
+ av = gda_row_get_value (arow, i);
+ ev = gda_row_get_value (erow, i);
+ gda_value_reset_with_type (av, G_VALUE_TYPE (ev));
+ g_value_copy (ev, av);
+ }
+ }
+ for (; i < (guint) ncols; i++) {
+ GdaColumn *acolumn, *ecolumn;
+ ecolumn = g_array_index (pivot->priv->columns, GdaColumn*, i);
+ acolumn = gda_data_model_describe_column (results, i);
+
+ gint j;
+ for (j = 0; j < nrows; j++) {
+ GdaRow *arow, *erow;
+ GValue *av;
+ erow = g_array_index (first_rows, GdaRow*, j);
+ arow = gda_data_model_array_get_row ((GdaDataModelArray*) results, j, NULL);
+ av = gda_row_get_value (arow, i);
+
+ CellData ccdata, *pcdata;
+ ccdata.row = j;
+ ccdata.col = i;
+ ccdata.values = NULL;
+ ccdata.computed_value = NULL;
+ pcdata = g_hash_table_lookup (data_hash, &ccdata);
+ if (pcdata) {
+ cell_data_compute_aggregate (pcdata);
+ if (pcdata->computed_value) {
+ gda_value_reset_with_type (av,
+ G_VALUE_TYPE (pcdata->computed_value));
+ g_value_copy (pcdata->computed_value, av);
+ }
+ else
+ gda_row_invalidate_value (arow, av);
+ }
+ else {
+ GValue *empty;
+ GdaDataPivotAggregate agg;
+ agg = GPOINTER_TO_INT (g_object_get_data ((GObject*) ecolumn, "agg"));
+ empty = aggregate_get_empty_value (agg);
+ gda_value_reset_with_type (av, G_VALUE_TYPE (empty));
+ g_value_copy (empty, av);
+ }
+ }
+ }
+ pivot->priv->results = results;
+
+ retval = TRUE;
+
+ out:
+ if (!retval)
+ clean_previous_population (pivot);
+
+ if (data_hash)
+ g_hash_table_destroy (data_hash);
+
+ if (first_rows) {
+ guint i;
+ for (i = 0; i < first_rows->len; i++) {
+ GObject *obj;
+ obj = g_array_index (first_rows, GObject*, i);
+ g_object_unref (obj);
+ }
+ g_array_free (first_rows, TRUE);
+ }
+
+ if (first_rows_index)
+ g_hash_table_destroy (first_rows_index);
+
+ g_hash_table_destroy (column_values_index);
+
+ g_object_unref (iter);
+ g_object_unref (model);
+
+ return retval;
+}
+
+static guint
+_gda_value_hash (gconstpointer key)
+{
+ GValue *v;
+ GType vt;
+ guint res = 0;
+ v = (GValue *) key;
+ vt = G_VALUE_TYPE (v);
+ if ((vt == G_TYPE_BOOLEAN) || (vt == G_TYPE_INT) || (vt == G_TYPE_UINT) ||
+ (vt == G_TYPE_FLOAT) || (vt == G_TYPE_DOUBLE) ||
+ (vt == G_TYPE_INT64) || (vt == G_TYPE_INT64) ||
+ (vt == GDA_TYPE_SHORT) || (vt == GDA_TYPE_USHORT) ||
+ (vt == G_TYPE_CHAR) || (vt == G_TYPE_UCHAR) ||
+ (vt == GDA_TYPE_NULL) || (vt == G_TYPE_GTYPE) ||
+ (vt == G_TYPE_LONG) || (vt == G_TYPE_ULONG)) {
+ const signed char *p, *data;
+ data = (signed char*) v;
+ res = 5381;
+ for (p = data; p < data + sizeof (GValue); p++)
+ res = (res << 5) + res + *p;
+ }
+ else if (vt == G_TYPE_STRING) {
+ const gchar *tmp;
+ tmp = g_value_get_string (v);
+ if (tmp)
+ res += g_str_hash (tmp);
+ }
+ else if ((vt == GDA_TYPE_BINARY) || (vt == GDA_TYPE_BLOB)) {
+ const GdaBinary *bin;
+ if (vt == GDA_TYPE_BLOB) {
+ GdaBlob *blob;
+ blob = (GdaBlob*) gda_value_get_blob ((GValue *) v);
+ bin = (GdaBinary *) blob;
+ if (blob->op &&
+ (bin->binary_length != gda_blob_op_get_length (blob->op)))
+ gda_blob_op_read_all (blob->op, blob);
+ }
+ else
+ bin = gda_value_get_binary ((GValue *) v);
+ if (bin) {
+ glong l;
+ for (l = 0; l < bin->binary_length; l++)
+ res += (guint) bin->data [l];
+ }
+ }
+ else {
+ gchar *tmp;
+ tmp = gda_value_stringify (v);
+ res += g_str_hash (tmp);
+ g_free (tmp);
+ }
+ return res;
+}
+
+static guint
+_gda_row_hash (gconstpointer key)
+{
+ gint i, len;
+ GdaRow *r;
+ guint res = 0;
+ r = (GdaRow*) key;
+ len = gda_row_get_length (r);
+ for (i = 0; i < len ; i++) {
+ GValue *v;
+ v = gda_row_get_value (r, i);
+ res = (res << 5) + res + _gda_value_hash (v);
+ }
+ return res;
+}
+
+static gboolean
+_gda_row_equal (gconstpointer a, gconstpointer b)
+{
+ gint i, len;
+ GdaRow *ra, *rb;
+ ra = (GdaRow*) a;
+ rb = (GdaRow*) b;
+ len = gda_row_get_length (ra);
+ g_assert (len == gda_row_get_length (rb));
+ for (i = 0; i < len ; i++) {
+ GValue *va, *vb;
+ va = gda_row_get_value (ra, i);
+ vb = gda_row_get_value (rb, i);
+ if (gda_value_differ (va, vb))
+ return FALSE;
+ }
+ return TRUE;
+}
+
+/*
+ * Create a new virtual connection for @pivot
+ */
+static gboolean
+create_vcnc (GdaDataPivot *pivot, GError **error)
+{
+ GdaConnection *vcnc;
+ GError *lerror = NULL;
+ if (pivot->priv->vcnc)
+ return TRUE;
+
+ g_static_mutex_lock (&provider_mutex);
+ if (!virtual_provider)
+ virtual_provider = gda_vprovider_data_model_new ();
+ g_static_mutex_unlock (&provider_mutex);
+
+ vcnc = gda_virtual_connection_open (virtual_provider, &lerror);
+ if (! vcnc) {
+ g_print ("Virtual ERROR: %s\n", lerror && lerror->message ? lerror->message : "No detail");
+ if (lerror)
+ g_error_free (lerror);
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_INTERNAL_ERROR,
+ "%s", _("Could not create virtual connection"));
+ return FALSE;
+ }
+
+ pivot->priv->vcnc = vcnc;
+ return TRUE;
+}
+
+/*
+ * Bind @pivot->priv->model as a table named TABLE_NAME in @pivot's virtual connection
+ */
+static gboolean
+bind_source_model (GdaDataPivot *pivot, GError **error)
+{
+ if (! pivot->priv->model) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_SOURCE_MODEL_ERROR,
+ "%s", _("No source defined"));
+ return FALSE;
+ }
+ if (! create_vcnc (pivot, error))
+ return FALSE;
+
+ if (gda_vconnection_data_model_get_model (GDA_VCONNECTION_DATA_MODEL (pivot->priv->vcnc),
+ TABLE_NAME) == pivot->priv->model) {
+ /* already bound */
+ return TRUE;
+ }
+
+ GError *lerror = NULL;
+ if (!gda_vconnection_data_model_add_model (GDA_VCONNECTION_DATA_MODEL (pivot->priv->vcnc),
+ pivot->priv->model,
+ TABLE_NAME, &lerror)) {
+ g_set_error (error, GDA_DATA_PIVOT_ERROR, GDA_DATA_PIVOT_SOURCE_MODEL_ERROR,
+ "%s",
+ _("Invalid source data model (may have incompatible column names)"));
+ g_clear_error (&lerror);
+ return FALSE;
+ }
+
+ return TRUE;
+}
+
+static
+guint column_data_hash (gconstpointer key)
+{
+ ColumnData *cd;
+ cd = (ColumnData*) key;
+ return _gda_value_hash (cd->value) + cd->column_fields_index + cd->data_pos;
+}
+
+static gboolean
+column_data_equal (gconstpointer a, gconstpointer b)
+{
+ ColumnData *cda, *cdb;
+ cda = (ColumnData*) a;
+ cdb = (ColumnData*) b;
+ if ((cda->column_fields_index != cdb->column_fields_index) ||
+ (cda->data_pos != cdb->data_pos))
+ return FALSE;
+ return gda_value_differ (cda->value, cdb->value) ? FALSE : TRUE;
+}
+
+static void
+column_data_free (ColumnData *cdata)
+{
+ gda_value_free (cdata->value);
+ g_free (cdata);
+}
+
+static guint
+cell_data_hash (gconstpointer key)
+{
+ CellData *cd;
+ cd = (CellData*) key;
+ return g_int_hash (&(cd->row)) + g_int_hash (&(cd->col));
+}
+
+static gboolean
+cell_data_equal (gconstpointer a, gconstpointer b)
+{
+ CellData *cda, *cdb;
+ cda = (CellData*) a;
+ cdb = (CellData*) b;
+ if ((cda->row == cdb->row) && (cda->col == cdb->col))
+ return TRUE;
+ else
+ return FALSE;
+}
+
+static void
+cell_data_free (CellData *cdata)
+{
+ if (cdata->values) {
+ guint i;
+ for (i = 0; i < cdata->values->len; i++) {
+ GValue *value;
+ value = g_array_index (cdata->values, GValue*, i);
+ gda_value_free (value);
+ }
+ g_array_free (cdata->values, TRUE);
+ }
+ gda_value_free (cdata->computed_value);
+ g_free (cdata);
+}
diff --git a/libgda/gda-data-pivot.h b/libgda/gda-data-pivot.h
new file mode 100644
index 0000000..f4650ad
--- /dev/null
+++ b/libgda/gda-data-pivot.h
@@ -0,0 +1,125 @@
+/*
+ * Copyright (C) 2011 Vivien Malerba <malerba gnome-db org>
+ *
+ * This library is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 2 of the License, or (at your option) any later version.
+ *
+ * This library 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
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public
+ * License along with this library; if not, write to the
+ * Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
+ * Boston, MA 02110-1301, USA.
+ */
+
+#ifndef __GDA_DATA_PIVOT_H__
+#define __GDA_DATA_PIVOT_H__
+
+#include <libgda/gda-data-model.h>
+
+G_BEGIN_DECLS
+
+#define GDA_TYPE_DATA_PIVOT (gda_data_pivot_get_type())
+#define GDA_DATA_PIVOT(obj) (G_TYPE_CHECK_INSTANCE_CAST (obj, GDA_TYPE_DATA_PIVOT, GdaDataPivot))
+#define GDA_DATA_PIVOT_CLASS(klass) (G_TYPE_CHECK_CLASS_CAST (klass, GDA_TYPE_DATA_PIVOT, GdaDataPivotClass))
+#define GDA_IS_DATA_PIVOT(obj) (G_TYPE_CHECK_INSTANCE_TYPE(obj, GDA_TYPE_DATA_PIVOT))
+#define GDA_IS_DATA_PIVOT_CLASS(klass) (G_TYPE_CHECK_CLASS_TYPE((klass), GDA_TYPE_DATA_PIVOT))
+
+typedef struct _GdaDataPivot GdaDataPivot;
+typedef struct _GdaDataPivotClass GdaDataPivotClass;
+typedef struct _GdaDataPivotPrivate GdaDataPivotPrivate;
+
+
+struct _GdaDataPivot {
+ GObject object;
+ GdaDataPivotPrivate *priv;
+};
+
+struct _GdaDataPivotClass {
+ GObjectClass parent_class;
+
+ /*< private >*/
+ /* Padding for future expansion */
+ void (*_gda_reserved1) (void);
+ void (*_gda_reserved2) (void);
+ void (*_gda_reserved3) (void);
+ void (*_gda_reserved4) (void);
+};
+
+/* error reporting */
+extern GQuark gda_data_pivot_error_quark (void);
+#define GDA_DATA_PIVOT_ERROR gda_data_pivot_error_quark ()
+
+/**
+ * GdaDataPivotError:
+ *
+ * Possible #GdaDataPivot related errors.
+ */
+typedef enum {
+ GDA_DATA_PIVOT_INTERNAL_ERROR,
+ GDA_DATA_PIVOT_SOURCE_MODEL_ERROR,
+ GDA_DATA_PIVOT_FIELD_FORMAT_ERROR,
+ GDA_DATA_PIVOT_USAGE_ERROR
+} GdaDataPivotError;
+
+/**
+ * GdaDataPivotAggregate:
+ * @GDA_DATA_PIVOT_AVG:
+ * @GDA_DATA_PIVOT_COUNT:
+ * @GDA_DATA_PIVOT_MAX:
+ * @GDA_DATA_PIVOT_MIN:
+ * @GDA_DATA_PIVOT_SUM:
+ *
+ * Possible operations for the data fields.
+ */
+typedef enum {
+ GDA_DATA_PIVOT_AVG,
+ GDA_DATA_PIVOT_COUNT,
+ GDA_DATA_PIVOT_MAX,
+ GDA_DATA_PIVOT_MIN,
+ GDA_DATA_PIVOT_SUM
+} GdaDataPivotAggregate;
+
+/**
+ * SECTION:gda-data-pivot
+ * @short_description: A data model for data summarisation
+ * @title: GdaDataPivot
+ * @stability: Stable
+ *
+ * The #GdaDataPivot data model allows one to do some analysis and summarisation on the contents
+ * of a data model.
+ *
+ *
+ */
+
+/**
+ * GdaDataPivotFieldType:
+ * @GDA_DATA_PIVOT_FIELD_ROW:
+ * @GDA_DATA_PIVOT_FIELD_COLUMN:
+ *
+ * Define types of field to be used when defining a #GdaDataPivot analysis.
+ */
+typedef enum {
+ GDA_DATA_PIVOT_FIELD_ROW,
+ GDA_DATA_PIVOT_FIELD_COLUMN
+} GdaDataPivotFieldType;
+
+GType gda_data_pivot_get_type (void) G_GNUC_CONST;
+GdaDataModel *gda_data_pivot_new (GdaDataModel *model);
+
+gboolean gda_data_pivot_add_field (GdaDataPivot *pivot, GdaDataPivotFieldType field_type,
+ const gchar *field, const gchar *alias, GError **error);
+gboolean gda_data_pivot_add_data (GdaDataPivot *pivot, GdaDataPivotAggregate aggregate_type,
+ const gchar *field, const gchar *alias, GError **error);
+
+gboolean gda_data_pivot_populate (GdaDataPivot *pivot, GError **error);
+
+
+G_END_DECLS
+
+#endif
diff --git a/libgda/libgda.h.in b/libgda/libgda.h.in
index b212c57..2880a27 100644
--- a/libgda/libgda.h.in
+++ b/libgda/libgda.h.in
@@ -47,6 +47,7 @@
#include <libgda/gda-data-access-wrapper.h>
#include <libgda/gda-data-proxy.h>
#include <libgda/gda-data-select.h>
+#include <libgda/gda-data-pivot.h>
#include <libgda/gda-lockable.h>
#include <libgda/gda-log.h>
#include <libgda/gda-quark-list.h>
diff --git a/libgda/libgda.symbols b/libgda/libgda.symbols
index 6875350..24df4dc 100644
--- a/libgda/libgda.symbols
+++ b/libgda/libgda.symbols
@@ -283,6 +283,11 @@
gda_data_model_set_value_at
gda_data_model_set_values
gda_data_model_thaw
+ gda_data_pivot_add_data
+ gda_data_pivot_add_field
+ gda_data_pivot_get_type
+ gda_data_pivot_new
+ gda_data_pivot_populate
gda_data_proxy_alter_value_attributes
gda_data_proxy_apply_all_changes
gda_data_proxy_apply_row_changes
diff --git a/tests/data-models/.gitignore b/tests/data-models/.gitignore
index 1629cb3..26ad01b 100644
--- a/tests/data-models/.gitignore
+++ b/tests/data-models/.gitignore
@@ -6,4 +6,5 @@ check_pmodel
check_empty_rs
check_model_errors
check_vcnc
+check_pivot
*.db
diff --git a/tests/data-models/Makefile.am b/tests/data-models/Makefile.am
index 1533af6..e2fd816 100644
--- a/tests/data-models/Makefile.am
+++ b/tests/data-models/Makefile.am
@@ -4,11 +4,12 @@ AM_CPPFLAGS = \
-I$(top_builddir) \
$(COREDEPS_CFLAGS) \
$(COREDEPS_WFLAGS) \
- -DCHECK_FILES=\""$(top_srcdir)"\"
+ -DCHECK_FILES=\""$(top_srcdir)"\" \
+ -DROOT_DIR=\""$(top_srcdir)"\"
TESTS_ENVIRONMENT = GDA_TOP_SRC_DIR="$(abs_top_srcdir)" GDA_TOP_BUILD_DIR="$(abs_top_builddir)"
-check_PROGRAMS = check_model_import check_virtual check_data_proxy check_model_copy check_pmodel check_empty_rs check_model_errors check_vcnc
-TESTS = check_model_import check_virtual check_data_proxy check_model_copy check_pmodel check_empty_rs check_model_errors check_vcnc
+check_PROGRAMS = check_model_import check_virtual check_data_proxy check_model_copy check_pmodel check_empty_rs check_model_errors check_vcnc check_pivot
+TESTS = check_model_import check_virtual check_data_proxy check_model_copy check_pmodel check_empty_rs check_model_errors check_vcnc check_pivot
common_sources =
@@ -64,6 +65,14 @@ check_vcnc_LDADD = \
$(top_builddir)/libgda/libgda-5.0.la \
$(COREDEPS_LIBS)
+check_pivot_SOURCES = $(common_sources) check_pivot.c
+check_pivot_CFLAGS = \
+ -I$(top_srcdir)/libgda/sqlite \
+ -I$(top_srcdir)/libgda/sqlite/pivot
+check_pivot_LDADD = \
+ $(top_builddir)/libgda/libgda-5.0.la \
+ $(COREDEPS_LIBS)
+
EXTRA_DIST = \
check_virtual.csv \
diff --git a/tests/data-models/check_pivot.c b/tests/data-models/check_pivot.c
new file mode 100644
index 0000000..89c4dba
--- /dev/null
+++ b/tests/data-models/check_pivot.c
@@ -0,0 +1,201 @@
+/*
+ * Copyright (C) 2011 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 <stdlib.h>
+#include <string.h>
+#include <glib.h>
+#include <libgda/libgda.h>
+
+#define fail(x) g_warning (x)
+#define fail_if(x,y) if (x) g_warning (y)
+#define fail_unless(x,y) if (!(x)) g_warning (y)
+
+static GdaDataModel *get_source_model (GdaConnection *cnc);
+
+static gint test_field_formats (GdaDataPivot *pivot);
+static gint test_column_formats (GdaDataPivot *pivot);
+static gint test_on_data (GdaConnection *cnc);
+
+int
+main (int argc, char **argv)
+{
+ int number_failed = 0;
+ GdaDataModel *source;
+ GdaDataPivot *pivot;
+ gchar *fname;
+ GdaConnection *cnc;
+
+ gda_init ();
+
+ /* open connection */
+ gchar *cnc_string;
+ fname = g_build_filename (ROOT_DIR, "tests", "data-models", NULL);
+ cnc_string = g_strdup_printf ("DB_DIR=%s;DB_NAME=pivot", fname);
+ g_free (fname);
+ cnc = gda_connection_open_from_string ("SQLite", cnc_string, NULL,
+ GDA_CONNECTION_OPTIONS_READ_ONLY, NULL);
+ if (!cnc) {
+ g_print ("Failed to open connection, cnc_string = %s\n", cnc_string);
+ exit (1);
+ }
+ g_free (cnc_string);
+
+ source = get_source_model (cnc);
+ pivot = GDA_DATA_PIVOT (gda_data_pivot_new (source));
+ g_object_unref (source);
+ number_failed += test_field_formats (pivot);
+ number_failed += test_column_formats (pivot);
+ g_object_unref (pivot);
+
+ number_failed += test_on_data (cnc);
+
+ /* end of tests */
+ g_object_unref (cnc);
+ if (number_failed == 0)
+ g_print ("Ok.\n");
+ else
+ g_print ("%d failed\n", number_failed);
+
+ return (number_failed == 0) ? EXIT_SUCCESS : EXIT_FAILURE;
+}
+
+static GdaDataModel *
+get_source_model (GdaConnection *cnc)
+{
+ GdaDataModel *model;
+ GdaStatement *stmt;
+ stmt = gda_connection_parse_sql_string (cnc,
+ "select * from food",
+ NULL, NULL);
+ g_assert (stmt);
+
+ model = gda_connection_statement_execute_select (cnc, stmt, NULL, NULL);
+ g_object_unref (stmt);
+ g_print ("==== Source data:\n");
+ gda_data_model_dump (model, NULL);
+ return model;
+}
+
+typedef struct {
+ gchar *field;
+ gchar *alias;
+ gboolean result;
+} AFieldFormat;
+
+AFieldFormat fields_test [] = {
+ {"person", "alias", TRUE},
+ {"person", NULL, TRUE},
+ {"name", NULL, FALSE},
+ {"person)", NULL, FALSE},
+ {"person || qty", NULL, TRUE},
+ {"person, qty", NULL, TRUE},
+};
+
+static gint
+test_field_formats (GdaDataPivot *pivot)
+{
+ guint i, nfailed = 0;
+ GError *error = NULL;
+ for (i = 0; i < sizeof (fields_test) / sizeof (AFieldFormat); i++) {
+ AFieldFormat *test = &(fields_test [i]);
+ gboolean res;
+ res = gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_ROW,
+ test->field, test->alias, &error);
+ if (res != test->result) {
+ if (test->result)
+ g_print ("Error: field setting failed for [%s][%s] when "
+ "it should have succedded, error: [%s]\n",
+ test->field, test->alias,
+ error && error->message ? error->message : "no detail");
+ else
+ g_print ("Error: field setting succedded for [%s][%s] when "
+ "it should have failed\n",
+ test->field, test->alias);
+ nfailed ++;
+ }
+ g_clear_error (&error);
+ }
+ return nfailed;
+}
+
+static gint
+test_column_formats (GdaDataPivot *pivot)
+{
+ guint i, nfailed = 0;
+ GError *error = NULL;
+ for (i = 0; i < sizeof (fields_test) / sizeof (AFieldFormat); i++) {
+ AFieldFormat *test = &(fields_test [i]);
+ gboolean res;
+ res = gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_COLUMN,
+ test->field, test->alias, &error);
+ if (res != test->result) {
+ if (test->result)
+ g_print ("Error: field setting failed for [%s][%s] when "
+ "it should have succedded, error: [%s]\n",
+ test->field, test->alias,
+ error && error->message ? error->message : "no detail");
+ else
+ g_print ("Error: field setting succedded for [%s][%s] when "
+ "it should have failed\n",
+ test->field, test->alias);
+ nfailed ++;
+ }
+ g_clear_error (&error);
+ }
+ return nfailed;
+}
+
+static gint
+test_on_data (GdaConnection *cnc)
+{
+ GdaDataModel *source;
+ GdaDataPivot *pivot;
+ GError *error = NULL;
+ gint number_failed = 0;
+
+ source = get_source_model (cnc);
+ pivot = GDA_DATA_PIVOT (gda_data_pivot_new (source));
+ g_object_unref (source);
+
+ gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_ROW,
+ //"person,week",
+ "person",
+ NULL, &error);
+ gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_COLUMN,
+ "food",
+ //"CASE food WHEN 'Car' THEN 'Not food' ELSE 'food' END",
+ NULL, &error);
+
+ gda_data_pivot_add_data (pivot, GDA_DATA_PIVOT_SUM,
+ "qty", "sumqty", &error);
+
+ gda_data_pivot_add_data (pivot, GDA_DATA_PIVOT_COUNT,
+ "food", "thefood", &error);
+
+ if (! gda_data_pivot_populate (pivot, &error)) {
+ g_print ("Failed to populate pivot: %s\n",
+ error && error->message ? error->message : "no detail");
+ number_failed ++;
+ goto out;
+ }
+
+ gda_data_model_dump (GDA_DATA_MODEL (pivot), NULL);
+
+ out:
+ g_object_unref (pivot);
+ return number_failed;
+}
diff --git a/tests/data-models/pivot.db b/tests/data-models/pivot.db
new file mode 100644
index 0000000..2d57137
Binary files /dev/null and b/tests/data-models/pivot.db differ
diff --git a/tools/gda-sql.1.in b/tools/gda-sql.1.in
index e18619b..2a6c2e3 100644
--- a/tools/gda-sql.1.in
+++ b/tools/gda-sql.1.in
@@ -270,6 +270,31 @@ Updates the current connection's meta data (use this command after having modifi
database's schema).
.IP \fB.o\fP
Sends output to a file or |pipe. Full syntax is: \fB.o <FILE_NAME>\fP or \fB.o |<COMMAND>\fP.
+.IP \fB.pivot\fP
+Performs data summarization on a data set. Full syntax is: \fB.pivot <SELECT> <ROW_FIELDS> <COLUMN_FIELDS> [<DATA_FIELDS> [...]]\fP.
+
+The \fI<SELECT>\fP defines the data set to perform summarization on.
+
+The \fI<ROW_FIELDS>\fP defines the fields from the data set from which each individual value will
+yield to a row in the analysis (it can be any valid selectable SQL expression on the data set's
+fields); multiple expressions can be provided, separated by commas (forming a valid SQL expression).
+
+The \fI<COLUMN_FIELDS>\fP defines the fields from the data set from which each individual value will
+yield to a column in the analysis. Its syntax is similar to the \fI<ROW_FIELDS>\fP one.
+
+The \fI<DATA_FIELDS>\fP arguments are entirely optional and indicates the way data summarization
+is done for each pair of (row,column) values (the default is to count occurrences). The syntax
+for each \fI<DATA_FIELDS>\fP argument is: \fB[aggregate]<SQL_expression>\fP, where the aggregate
+part is optional and, if present must be among [SUM], [COUNT], [AVG], [MIN] or [MAX], and
+the SQL expression is a valid selectable SQL expression of the data set's fields.
+
+Examples:
+
+\fB.pivot "SELECT * FROM food" person food\fP
+
+\fB.pivot "SELECT * FROM products" category "CASE WHEN price < 15 THEN 'low' ELSE 'high' END" [AVG]price \fP
+
+
.IP \fB.q\fP
Quits the application.
.IP \fB.qecho\fP
diff --git a/tools/gda-sql.c b/tools/gda-sql.c
index 34462f5..82337ee 100644
--- a/tools/gda-sql.c
+++ b/tools/gda-sql.c
@@ -154,7 +154,8 @@ static const char *prompt_func (void);
static GdaInternalCommandsList *build_internal_commands_list (void);
static gboolean command_is_complete (const gchar *command);
static GdaInternalCommandResult *command_execute (SqlConsole *console,
- const gchar *command, GError **error);
+ const gchar *command,
+ GdaStatementModelUsage usage, GError **error);
static gchar *result_to_string (SqlConsole *console, GdaInternalCommandResult *res);
static void display_result (GdaInternalCommandResult *res);
@@ -492,7 +493,8 @@ treat_line_func (const gchar *cmde, gboolean *out_cmde_exec_ok)
to_stream = main_data->output_stream;
else
to_stream = stdout;
- res = command_execute (NULL, main_data->partial_command->str, &error);
+ res = command_execute (NULL, main_data->partial_command->str,
+ GDA_STATEMENT_MODEL_RANDOM_ACCESS, &error);
if (!res) {
if (!error ||
@@ -861,10 +863,13 @@ command_is_complete (const gchar *command)
* command_execute
*/
static GdaInternalCommandResult *execute_internal_command (SqlConsole *console, GdaConnection *cnc,
- const gchar *command_str, GError **error);
-static GdaInternalCommandResult *execute_external_command (SqlConsole *console, const gchar *command, GError **error);
+ const gchar *command_str,
+ GError **error);
+static GdaInternalCommandResult *execute_external_command (SqlConsole *console, const gchar *command,
+ GdaStatementModelUsage usage,
+ GError **error);
static GdaInternalCommandResult *
-command_execute (SqlConsole *console, const gchar *command, GError **error)
+command_execute (SqlConsole *console, const gchar *command, GdaStatementModelUsage usage, GError **error)
{
ConnectionSetting *cs;
@@ -896,7 +901,7 @@ command_execute (SqlConsole *console, const gchar *command, GError **error)
return NULL;
}
- return execute_external_command (console, command, error);
+ return execute_external_command (console, command, usage, error);
}
}
@@ -1058,7 +1063,8 @@ execute_internal_command (SqlConsole *console, GdaConnection *cnc, const gchar *
* Executes an SQL statement as understood by the DBMS
*/
static GdaInternalCommandResult *
-execute_external_command (SqlConsole *console, const gchar *command, GError **error)
+execute_external_command (SqlConsole *console, const gchar *command,
+ GdaStatementModelUsage usage, GError **error)
{
GdaInternalCommandResult *res = NULL;
GdaBatch *batch;
@@ -1168,8 +1174,7 @@ execute_external_command (SqlConsole *console, const gchar *command, GError **er
res = g_new0 (GdaInternalCommandResult, 1);
res->was_in_transaction_before_exec = gda_connection_get_transaction_status (cs->cnc) ? TRUE : FALSE;
res->cnc = g_object_ref (cs->cnc);
- obj = gda_connection_statement_execute (cs->cnc, stmt, params,
- GDA_STATEMENT_MODEL_RANDOM_ACCESS, NULL, error);
+ obj = gda_connection_statement_execute (cs->cnc, stmt, params, usage, NULL, error);
if (!obj) {
g_free (res);
res = NULL;
@@ -1875,7 +1880,7 @@ data_model_to_string (SqlConsole *console, GdaDataModel *model)
gchar *tmp2, *tmp3;
gdouble etime;
g_object_get ((GObject*) model, "execution-delay", &etime, NULL);
- tmp2 = g_strdup_printf (_("Execution delay: %.03f"), etime);
+ tmp2 = g_strdup_printf ("%s: %.03f s", _("Execution delay"), etime);
tmp3 = g_strdup_printf ("%s\n%s", tmp, tmp2);
g_free (tmp);
g_free (tmp2);
@@ -2127,6 +2132,9 @@ static GdaInternalCommandResult *extra_command_export (SqlConsole *console, GdaC
static GdaInternalCommandResult *extra_command_set2 (SqlConsole *console, GdaConnection *cnc,
const gchar **args,
GError **error, gpointer data);
+static GdaInternalCommandResult *extra_command_pivot (SqlConsole *console, GdaConnection *cnc,
+ const gchar **args,
+ GError **error, gpointer data);
static GdaInternalCommandResult *extra_command_declare_fk (SqlConsole *console, GdaConnection *cnc,
const gchar **args,
@@ -2542,30 +2550,6 @@ build_internal_commands_list (void)
commands->commands = g_slist_prepend (commands->commands, c);
c = g_new0 (GdaInternalCommand, 1);
- c->group = _("Query buffer");
- c->name = g_strdup_printf (_("%s [NAME [VALUE|_null_]]"), "set");
- c->description = _("Set or show internal parameter, or list all if no parameters");
- c->args = NULL;
- c->command_func = (GdaInternalCommandFunc) extra_command_set;
- c->user_data = NULL;
- c->arguments_delimiter_func = args_as_string_set;
- c->unquote_args = TRUE;
- c->limit_to_main = FALSE;
- commands->commands = g_slist_prepend (commands->commands, c);
-
- c = g_new0 (GdaInternalCommand, 1);
- c->group = _("Query buffer");
- c->name = g_strdup_printf (_("%s [NAME]"), "unset");
- c->description = _("Unset (delete) internal named parameter (or all parameters)");
- c->args = NULL;
- c->command_func = (GdaInternalCommandFunc) extra_command_unset;
- c->user_data = NULL;
- c->arguments_delimiter_func = NULL;
- c->unquote_args = TRUE;
- c->limit_to_main = FALSE;
- commands->commands = g_slist_prepend (commands->commands, c);
-
- c = g_new0 (GdaInternalCommand, 1);
c->group = _("Formatting");
c->name = "H [HTML|XML|CSV|DEFAULT]";
c->description = _("Set output format");
@@ -2603,7 +2587,31 @@ build_internal_commands_list (void)
commands->commands = g_slist_prepend (commands->commands, c);
c = g_new0 (GdaInternalCommand, 1);
- c->group = _("Query buffer");
+ c->group = _("Execution context");
+ c->name = g_strdup_printf (_("%s [NAME [VALUE|_null_]]"), "set");
+ c->description = _("Set or show internal parameter, or list all if no parameters");
+ c->args = NULL;
+ c->command_func = (GdaInternalCommandFunc) extra_command_set;
+ c->user_data = NULL;
+ c->arguments_delimiter_func = args_as_string_set;
+ c->unquote_args = TRUE;
+ c->limit_to_main = FALSE;
+ commands->commands = g_slist_prepend (commands->commands, c);
+
+ c = g_new0 (GdaInternalCommand, 1);
+ c->group = _("Execution context");
+ c->name = g_strdup_printf (_("%s [NAME]"), "unset");
+ c->description = _("Unset (delete) internal named parameter (or all parameters)");
+ c->args = NULL;
+ c->command_func = (GdaInternalCommandFunc) extra_command_unset;
+ c->user_data = NULL;
+ c->arguments_delimiter_func = NULL;
+ c->unquote_args = TRUE;
+ c->limit_to_main = FALSE;
+ commands->commands = g_slist_prepend (commands->commands, c);
+
+ c = g_new0 (GdaInternalCommand, 1);
+ c->group = _("Execution context");
c->name = g_strdup_printf (_("%s NAME [FILE|TABLE COLUMN ROW_CONDITION]"), "setex");
c->description = _("Set internal parameter as the contents of the FILE file or from an existing table's value");
c->args = NULL;
@@ -2614,6 +2622,19 @@ build_internal_commands_list (void)
c->limit_to_main = TRUE;
commands->commands = g_slist_prepend (commands->commands, c);
+ c = g_new0 (GdaInternalCommand, 1);
+ c->group = _("Execution context");
+ c->name = g_strdup_printf (_("%s SELECT ROW_FIELDS COLUMN_FIELDS [DATA_FIELDS]"), "pivot");
+ c->description = _("Performs a statistical analysis on the data from SELECT, "
+ "using ROW_FIELDS and COLUMN_FIELDS criteria and optionally DATA_FIELDS for the data");
+ c->args = NULL;
+ c->command_func = (GdaInternalCommandFunc) extra_command_pivot;
+ c->user_data = NULL;
+ c->arguments_delimiter_func = NULL;
+ c->unquote_args = TRUE;
+ c->limit_to_main = TRUE;
+ commands->commands = g_slist_prepend (commands->commands, c);
+
/* comes last */
c = g_new0 (GdaInternalCommand, 1);
c->group = _("General");
@@ -3674,7 +3695,8 @@ extra_command_exec_buffer (SqlConsole *console, GdaConnection *cnc, const gchar
if (!main_data->current->query_buffer)
main_data->current->query_buffer = g_string_new ("");
if (*main_data->current->query_buffer->str != 0)
- res = command_execute (NULL, main_data->current->query_buffer->str, error);
+ res = command_execute (NULL, main_data->current->query_buffer->str,
+ GDA_STATEMENT_MODEL_RANDOM_ACCESS, error);
else {
res = g_new0 (GdaInternalCommandResult, 1);
res->type = GDA_INTERNAL_COMMAND_RESULT_EMPTY;
@@ -4499,7 +4521,7 @@ get_table_value_at_cell (GdaConnection *cnc, GError **error, G_GNUC_UNUSED MainD
/* execute statement */
GdaInternalCommandResult *tmpres;
- tmpres = execute_external_command (NULL, sql, error);
+ tmpres = execute_external_command (NULL, sql, GDA_STATEMENT_MODEL_RANDOM_ACCESS, error);
g_free (sql);
if (!tmpres)
return NULL;
@@ -4614,6 +4636,143 @@ extra_command_set2 (SqlConsole *console, GdaConnection *cnc, const gchar **args,
}
static GdaInternalCommandResult *
+extra_command_pivot (SqlConsole *console, GdaConnection *cnc, const gchar **args,
+ GError **error, gpointer data)
+{
+ GdaInternalCommandResult *res = NULL;
+ ConnectionSetting *cs;
+ cs = get_current_connection_settings (console);
+ if (!cs) {
+ g_set_error (error, 0, 0, "%s",
+ _("No connection specified"));
+ return NULL;
+ }
+
+ const gchar *select = NULL;
+ const gchar *row_fields = NULL;
+ const gchar *column_fields = NULL;
+
+ if (args[0] && *args[0]) {
+ select = args[0];
+ if (args[1] && *args[1]) {
+ row_fields = args [1];
+ if (args[2] && *args[2])
+ column_fields = args[2];
+ }
+ }
+
+ if (!select) {
+ g_set_error (error, 0, 0, "%s",
+ _("Missing data on which to operate"));
+ return NULL;
+ }
+ if (!row_fields) {
+ g_set_error (error, 0, 0, "%s",
+ _("Missing row fields specifications"));
+ return NULL;
+ }
+ if (!column_fields) {
+ g_set_error (error, 0, 0, "%s",
+ _("Missing column fields specifications"));
+ return NULL;
+ }
+
+ /* execute SELECT */
+ gboolean was_in_trans;
+ GdaInternalCommandResult *tmpres;
+
+ was_in_trans = gda_connection_get_transaction_status (cs->cnc) ? TRUE : FALSE;
+
+ tmpres = command_execute (console, select, GDA_STATEMENT_MODEL_CURSOR_FORWARD, error);
+ if (!tmpres)
+ return NULL;
+ if (tmpres->type != GDA_INTERNAL_COMMAND_RESULT_DATA_MODEL) {
+ gda_internal_command_exec_result_free (tmpres);
+ g_set_error (error, 0, 0, "%s",
+ _("Wrong SELECT argument"));
+ return NULL;
+ }
+
+ GdaDataPivot *pivot;
+ gdouble etime = 0.;
+ //g_object_get ((GObject*) tmpres->u.model, "execution-delay", &etime, NULL);
+ pivot = (GdaDataPivot*) gda_data_pivot_new (tmpres->u.model);
+ gda_internal_command_exec_result_free (tmpres);
+
+ if (! gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_ROW,
+ row_fields, NULL, error)) {
+ g_object_unref (pivot);
+ return NULL;
+ }
+
+ if (! gda_data_pivot_add_field (pivot, GDA_DATA_PIVOT_FIELD_COLUMN,
+ column_fields, NULL, error)) {
+ g_object_unref (pivot);
+ return NULL;
+ }
+
+ GTimer *timer;
+ timer = g_timer_new ();
+
+ gint i;
+ for (i = 3; args[i] && *args[i]; i++) {
+ const gchar *df = args[i];
+ GdaDataPivotAggregate agg = GDA_DATA_PIVOT_COUNT;
+ if (*df == '[') {
+ const gchar *tmp;
+ for (tmp = df; *tmp && *tmp != ']'; tmp++);
+ if (!*tmp) {
+ g_timer_destroy (timer);
+ g_object_unref (pivot);
+ g_set_error (error, 0, 0, "%s",
+ _("Wrong data field argument"));
+ return NULL;
+ }
+ df++;
+ if (! g_ascii_strncasecmp (df, "sum", 3) && (df[3] == ']'))
+ agg = GDA_DATA_PIVOT_SUM;
+ else if (! g_ascii_strncasecmp (df, "avg", 3) && (df[3] == ']'))
+ agg = GDA_DATA_PIVOT_AVG;
+ else if (! g_ascii_strncasecmp (df, "max", 3) && (df[3] == ']'))
+ agg = GDA_DATA_PIVOT_MAX;
+ else if (! g_ascii_strncasecmp (df, "min", 3) && (df[3] == ']'))
+ agg = GDA_DATA_PIVOT_MIN;
+ else if (! g_ascii_strncasecmp (df, "count", 5) && (df[5] == ']'))
+ agg = GDA_DATA_PIVOT_COUNT;
+ else {
+ g_timer_destroy (timer);
+ g_object_unref (pivot);
+ g_set_error (error, 0, 0, "%s",
+ _("Wrong data field argument"));
+ return NULL;
+ }
+ df = tmp+1;
+ }
+ if (! gda_data_pivot_add_data (pivot, agg, df, NULL, error)) {
+ g_timer_destroy (timer);
+ g_object_unref (pivot);
+ return NULL;
+ }
+ }
+
+ if (! gda_data_pivot_populate (pivot, error)) {
+ g_timer_destroy (timer);
+ g_object_unref (pivot);
+ return NULL;
+ }
+
+ etime += g_timer_elapsed (timer, NULL);
+ g_timer_destroy (timer);
+ //g_object_set (pivot, "execution-delay", etime, NULL);
+
+ res = g_new0 (GdaInternalCommandResult, 1);
+ res->type = GDA_INTERNAL_COMMAND_RESULT_DATA_MODEL;
+ res->was_in_transaction_before_exec = was_in_trans;
+ res->u.model = (GdaDataModel*) pivot;
+ return res;
+}
+
+static GdaInternalCommandResult *
extra_command_export (SqlConsole *console, GdaConnection *cnc, const gchar **args,
GError **error, gpointer data)
{
@@ -5133,7 +5292,8 @@ gda_sql_console_execute (SqlConsole *console, const gchar *command, GError **err
/* execute command */
GdaInternalCommandResult *res;
- res = command_execute (console, loc_cmde, error);
+ res = command_execute (console, loc_cmde,
+ GDA_STATEMENT_MODEL_RANDOM_ACCESS, error);
if (res) {
OutputFormat of = console->output_format;
[
Date Prev][
Date Next] [
Thread Prev][
Thread Next]
[
Thread Index]
[
Date Index]
[
Author Index]
