From d4545979317f27c349f52018e51b8c8242855720 Mon Sep 17 00:00:00 2001 From: Andrea Zagli Date: Thu, 2 Sep 2010 11:09:56 +0200 Subject: [PATCH] Now it saves also the key. ALTER TABLE fields ADD COLUMN is_key boolean DEFAULT FALSE; --- docs/sql/audit.sql | 10 ++-- src/audit.c | 130 +++++++++++++++++++++++++++++++++++++++------ tests/test1.c | 2 + 3 files changed, 122 insertions(+), 20 deletions(-) diff --git a/docs/sql/audit.sql b/docs/sql/audit.sql index 237ff01..3c4aae3 100644 --- a/docs/sql/audit.sql +++ b/docs/sql/audit.sql @@ -1,6 +1,6 @@ CREATE TABLE datasources ( id integer NOT NULL, - name character varying(30) DEFAULT '', + name character varying(50) DEFAULT '', cnc_string character varying(255) DEFAULT '', status character varying(1) DEFAULT '', CONSTRAINT datasources_pkey PRIMARY KEY (id) @@ -8,7 +8,7 @@ CREATE TABLE datasources ( CREATE TABLE tables ( id integer NOT NULL, - name character varying(30) DEFAULT '', + name character varying(50) DEFAULT '', status character varying(1) DEFAULT '', id_datasources integer NOT NULL, CONSTRAINT tables_pkey PRIMARY KEY (id) @@ -17,7 +17,8 @@ CREATE TABLE tables ( CREATE TABLE fields ( id integer NOT NULL, id_tables integer NOT NULL, - name character varying(30) DEFAULT '', + name character varying(50) DEFAULT '', + is_key boolean DEFAULT false, status character varying(1) DEFAULT '', CONSTRAINT fields_pkey PRIMARY KEY (id) ); @@ -32,9 +33,8 @@ CREATE TABLE actions ( ); CREATE TABLE values ( - id bigint NOT NULL, id_actions bigint NOT NULL, id_fields integer NOT NULL, value character varying DEFAULT '', - CONSTRAINT valori_pkey PRIMARY KEY (id) + CONSTRAINT values_pkey PRIMARY KEY (id_actions, id_fields) ); diff --git a/src/audit.c b/src/audit.c index d9e9375..c1030d8 100644 --- a/src/audit.c +++ b/src/audit.c @@ -32,6 +32,7 @@ typedef struct { gint id; gchar *name; + gboolean is_key; } Field; typedef struct @@ -39,6 +40,9 @@ typedef struct gint id; gchar *name; GList *fields; + gchar *fields_sql; + GList *keys; + gchar *keys_sql; } Table; typedef struct @@ -153,7 +157,7 @@ load_fields (Audit *audit, return FALSE; } - sql = g_strdup_printf ("SELECT id, name FROM fields" + sql = g_strdup_printf ("SELECT id, name, is_key FROM fields" " WHERE status <> 'D' AND id_tables = %d", table->id); dm = gdaex_query (priv->gdaex, sql); @@ -179,6 +183,7 @@ load_fields (Audit *audit, cam->id = gdaex_data_model_get_field_value_integer_at (dm, row, "id"); cam->name = gdaex_data_model_get_field_value_stringify_at (dm, row, "name"); + cam->is_key = gdaex_data_model_get_field_value_boolean_at (dm, row, "is_key"); if (gda_data_model_get_column_index (dm_table, cam->name) < 0) { @@ -188,6 +193,23 @@ load_fields (Audit *audit, } table->fields = g_list_append (table->fields, (gpointer)cam); + table->fields_sql = g_strconcat (table->fields_sql == NULL ? "" : + g_strconcat (table->fields_sql, ", ", NULL), + cam->name, NULL); + if (cam->is_key) + { + table->keys = g_list_append (table->fields, (gpointer)cam); + table->keys_sql = g_strconcat (table->keys_sql == NULL ? "" : + g_strconcat (table->keys_sql, ", ", NULL), + cam->name, NULL); + } + } + + if (table->keys == NULL) + { + g_warning ("No key's fields defined for table «%s».", + table->name); + return FALSE; } return TRUE; @@ -233,6 +255,9 @@ load_tables (Audit *audit, /* for each table must be loaded fields */ tab->fields = NULL; + tab->fields_sql = NULL; + tab->keys = NULL; + tab->keys_sql = NULL; if (!load_fields (audit, datasource, tab)) { return FALSE; @@ -571,7 +596,7 @@ Audit * @username: the username that make the action. * @datasource_name: the datasource's name. * @table_name: the table's name. - * @...: a #NULL terminated list of couples field-value. + * @...: a #NULL terminated list of couples field-value, that represent the WHERE part. * * Returns: #TRUE on success. */ @@ -617,6 +642,38 @@ audit_action (Audit *audit, return ret; } +static gchar +*normalize_fields_list (const gchar *fields_list) +{ + gchar *ret; + + gchar **fields; + guint i; + guint r; + guint n_fields; + + ret = g_strdup (""); + fields = g_strsplit (fields_list, ", ", 0); + + n_fields = g_strv_length (fields); + for (i = 0; i < n_fields; i++) + { + if (i == 0) + { + ret = g_strconcat (ret, fields[i], NULL); + } + else + { + if (g_strrstr_len (ret, strlen (fields[i]), fields[i]) == NULL) + { + ret = g_strconcat (ret, ", ", fields[i], NULL); + } + } + } + + return ret; +} + /** * audit_action_v: * @audit: an #Audit object. @@ -712,8 +769,11 @@ audit_action_v (Audit *audit, switch (action) { case AUDIT_ACTION_INSERT: - /* TODO for now it saves all the fields passed */ - /* saving just key's fields */ + sql = g_strdup_printf ("SELECT %s FROM %s", + table->keys_sql, + table->name); + + sql_where = g_strdup (" WHERE TRUE"); for (strpart = 0; strpart < l; strpart++) { field_name = (gchar *)fields_values[strpart]; @@ -724,14 +784,38 @@ audit_action_v (Audit *audit, value = (gchar *)fields_values[++strpart]; if (value == NULL) break; - insert_value (audit, id, table, field_name, value); + /* TODO the db field can be other type than string */ + sql_where = g_strconcat (sql_where, " AND ", field_name, " = ", value, "", NULL); + } + + sql = g_strconcat (sql, sql_where, NULL); + dm = gdaex_query (datasource->gdaex, sql); + + if (dm == NULL) return FALSE; + + if (gda_data_model_get_n_rows (dm) == 0 || + gda_data_model_get_n_rows (dm) > 1) return FALSE; + + cols = gda_data_model_get_n_columns (dm); + for (col = 0; col < cols; col++) + { + /* find the field */ + field_name = (gchar *)gda_data_model_get_column_title (dm, col); + insert_value (audit, id, table, field_name, + gdaex_data_model_get_value_stringify_at (dm, 0, col)); } break; case AUDIT_ACTION_BEFORE_UPDATE: - /* storing the whole table to be changed */ - sql = g_strdup_printf ("SELECT * FROM %s", - table_name); + if (priv->fields_updated != NULL) + { + g_hash_table_destroy (priv->fields_updated); + priv->fields_updated = NULL; + } + + sql = g_strdup_printf ("SELECT %s FROM %s", + normalize_fields_list ((const gchar *)g_strconcat (table->keys_sql, ", ", table->fields_sql, NULL)), + table->name); sql_where = g_strdup (" WHERE TRUE"); for (strpart = 0; strpart < l; strpart++) @@ -755,7 +839,7 @@ audit_action_v (Audit *audit, if (gda_data_model_get_n_rows (dm) == 0 || gda_data_model_get_n_rows (dm) > 1) return FALSE; - priv->fields_updated = g_hash_table_new (g_str_hash, g_str_equal); + priv->fields_updated = g_hash_table_new_full (g_str_hash, g_str_equal, g_free, g_free); cols = gda_data_model_get_n_columns (dm); for (col = 0; col < cols; col++) { @@ -766,9 +850,16 @@ audit_action_v (Audit *audit, break; case AUDIT_ACTION_AFTER_UPDATE: - /* saving changed fields */ - sql = g_strdup_printf ("SELECT * FROM %s", - table_name); + if (priv->fields_updated == NULL) + { + g_warning ("You must call before an action of type AUDIT_ACTION_AFTER_UPDATE."); + return FALSE; + } + + /* saving changed fields and the key */ + sql = g_strdup_printf ("SELECT %s FROM %s", + normalize_fields_list ((const gchar *)g_strconcat (table->keys_sql, ", ", table->fields_sql, NULL)), + table->name); sql_where = g_strdup (" WHERE TRUE"); for (strpart = 0; strpart < l; strpart++) @@ -799,18 +890,27 @@ audit_action_v (Audit *audit, value = (gchar *)g_hash_table_lookup (priv->fields_updated, (gconstpointer)field_name); value_new = gdaex_data_model_get_value_stringify_at (dm, 0, col); - if (g_strcmp0 (value, value_new) != 0) + if ((get_field_from_name (audit, table, (const gchar *)field_name))->is_key) + { + /* the key is always saved with old and new values */ + insert_value (audit, id, table, field_name, g_strconcat (value, "|", value_new, NULL)); + } + else if (g_strcmp0 (value, value_new) != 0) { /* field changed: must be saved the old value */ insert_value (audit, id, table, field_name, value); } } + + g_hash_table_destroy (priv->fields_updated); + priv->fields_updated = NULL; break; case AUDIT_ACTION_DELETE: /* saving all fields */ - sql = g_strdup_printf ("SELECT * FROM %s", - table_name); + sql = g_strdup_printf ("SELECT %s FROM %s", + table->fields_sql, + table->name); sql_where = g_strdup (" WHERE TRUE"); for (strpart = 0; strpart < l; strpart++) diff --git a/tests/test1.c b/tests/test1.c index b5a3505..50cbdce 100644 --- a/tests/test1.c +++ b/tests/test1.c @@ -53,6 +53,8 @@ main (int argc, char *argv[]) return 0; } + gdaex_execute (gdaex, "DELETE FROM actions"); + gdaex_execute (gdaex, "DELETE FROM values"); gdaex_execute (gdaex, "DELETE FROM test1"); gdaex_execute (gdaex, "INSERT INTO test1 VALUES (1, 'Mary Red', 25, 1500.45)"); -- 2.49.0