GNOME.org
 

[libgda: 1/3] DB: Table constraint was added.

commit d83257054fab44f60d51c51cdce82d035392b787
Author: Pavlo Solntsev <p sun fun gmail com>
Date:   Tue Oct 29 08:51:14 2019 -0500

    DB: Table constraint was added.
    
    * Added corresponding code to GdaDbCatallog and GdaDbTable
    to handle constraints
    * Implemented constraint code for sqlite provider
    * Added test to test the implementation

 libgda/gda-db-table.c          |  36 ++++++++++++-
 libgda/gda-db-table.h          |   4 ++
 libgda/libgda-db-catalog.dtd   |   4 +-
 libgda/sqlite/gda-sqlite-ddl.c |  31 +++++++++--
 tests/db/check-db-catalog.c    | 120 +++++++++++++++++++++++++++++++++++++++++
 5 files changed, 188 insertions(+), 7 deletions(-)
---
diff --git a/libgda/gda-db-table.c b/libgda/gda-db-table.c
index 2e4eb13a0..35d24229e 100644
--- a/libgda/gda-db-table.c
+++ b/libgda/gda-db-table.c
@@ -38,6 +38,7 @@ typedef struct
   gboolean m_istemp;
   GList *mp_columns; /* Type GdaDbColumn*/
   GList *mp_fkeys; /* List of all fkeys, GdaDbFkey */
+  GSList *mp_constraint; /* A list of constraints for the table as strings, a la gchar* */
 } GdaDbTablePrivate;
 
 /**
@@ -77,6 +78,7 @@ enum {
     GDA_DB_TABLE_TEMP,
     GDA_DB_TABLE_SPACE,
     GDA_DB_TABLE_COMMENT,
+    GDA_DB_TABLE_CONSTRAINT,
 
     GDA_DB_TABLE_N_NODES
 };
@@ -86,7 +88,8 @@ static const gchar *gdadbtablenodes[GDA_DB_TABLE_N_NODES] = {
     "name",
     "temptable",
     "space",
-    "comment"
+    "comment",
+    "constraint"
 };
 
 /**
@@ -123,6 +126,8 @@ gda_db_table_dispose (GObject *object)
     g_list_free_full (priv->mp_fkeys, (GDestroyNotify) g_object_unref);
   if (priv->mp_columns)
     g_list_free_full (priv->mp_columns, (GDestroyNotify)g_object_unref);
+  if (priv->mp_constraint)
+    g_slist_free_full (priv->mp_constraint, (GDestroyNotify)g_free);
 
   G_OBJECT_CLASS (gda_db_table_parent_class)->dispose (object);
 }
@@ -203,6 +208,7 @@ gda_db_table_init (GdaDbTable *self)
   priv->m_istemp = FALSE;
   priv->mp_comment = NULL;
   priv->mp_fkeys = NULL;
+  priv->mp_constraint = NULL;
 }
 
 /**
@@ -533,6 +539,18 @@ gda_db_table_prepare_create (GdaDbTable *self,
                                           "/TABLE_DEF_P/TABLE_IFNOTEXISTS"))
     return FALSE;
 
+  GSList *jt = NULL;
+  int indexsec = 0; /* Index for sequence */
+
+  for (jt = priv->mp_constraint; jt != NULL; jt = jt->next, indexsec++)
+    {
+      if (!gda_server_operation_set_value_at (op,
+                                              (const gchar *)jt->data,
+                                              error,
+                                              "/TABLE_CONSTRAINTS_S/%d/CONSTRAINT_STRING", indexsec))
+        return FALSE;
+    }
+
   GList *it = NULL;
   gint i = 0; /* column order counter */
 
@@ -765,3 +783,19 @@ gda_db_table_append_fkey (GdaDbTable *self,
   priv->mp_fkeys = g_list_append (priv->mp_fkeys, g_object_ref (fkey));
 }
 
+/**
+ * gda_db_table_append_constraint:
+ * @self: a #GdaDbTable instance
+ * @constr a constraint string to append
+ *
+ * Since: 6.0
+ *
+ */
+void
+gda_db_table_append_constraint (GdaDbTable *self,
+                                const gchar *constr)
+{
+  GdaDbTablePrivate *priv = gda_db_table_get_instance_private (self);
+
+  priv->mp_constraint = g_slist_append (priv->mp_constraint, g_strdup (constr));
+}
diff --git a/libgda/gda-db-table.h b/libgda/gda-db-table.h
index d8bc4720d..1085ce9ad 100644
--- a/libgda/gda-db-table.h
+++ b/libgda/gda-db-table.h
@@ -80,6 +80,10 @@ gboolean        gda_db_table_create          (GdaDbTable *self,
 
 void            gda_db_table_append_fkey (GdaDbTable *self,
                                           GdaDbFkey *fkey);
+
+void            gda_db_table_append_constraint (GdaDbTable *self,
+                                                const gchar *constraint);
+
 G_END_DECLS
 
 #endif /* end of include guard: GDA-DB-TABLE_H */
diff --git a/libgda/libgda-db-catalog.dtd b/libgda/libgda-db-catalog.dtd
index 9954797f3..53d885584 100644
--- a/libgda/libgda-db-catalog.dtd
+++ b/libgda/libgda-db-catalog.dtd
@@ -7,7 +7,7 @@
 <!ELEMENT schema (table+, view*)>
 <!ATTLIST schema name           CDATA   #IMPLIED>
 
-<!ELEMENT table (comment?,column+, fkey*)>
+<!ELEMENT table (comment?,column+, fkey*, constraint*)>
 <!ATTLIST table temptable       (TRUE|FALSE)    "FALSE">
 <!ATTLIST table name            CDATA           #REQUIRED>
 <!ATTLIST table space           CDATA           #IMPLIED>
@@ -27,6 +27,8 @@
 
 <!ELEMENT check         (#PCDATA)>
 
+<!ELEMENT constraint    (#PCDATA)>
+
 <!ELEMENT fkey (fk_field+)>
 <!ATTLIST fkey reftable CDATA #IMPLIED>
 <!ATTLIST fkey onupdate (RESTRICT|CASCADE|SET_NULL|NO_ACTION|SET_DEFAULT)       #IMPLIED>
diff --git a/libgda/sqlite/gda-sqlite-ddl.c b/libgda/sqlite/gda-sqlite-ddl.c
index 6712bdef1..6f2bef20e 100644
--- a/libgda/sqlite/gda-sqlite-ddl.c
+++ b/libgda/sqlite/gda-sqlite-ddl.c
@@ -322,13 +322,34 @@ _gda_sqlite_render_CREATE_TABLE (GdaServerProvider *provider, GdaConnection *cnc
                        if (value && G_VALUE_HOLDS (value, G_TYPE_STRING) && g_value_get_string (value))
                                g_string_append_printf (string, " ON DELETE %s", g_value_get_string (value));
                }
-       }
+  }
 
-       g_free (conflict_algo);
-       g_string_append (string, ")");
+  /* CONSTRAINT can be inserted without specifying the keyword CONSTRAINT for sqlite.
+   * Here, we just insert what we have saved in the GdaServerOperation object. Since
+   * we are moving towards new implementation of how DDL operations should be handled,
+   * we don't need to put more effort here. This is just a temporary working solution
+   */
+  node = gda_server_operation_get_node_info (op, "/TABLE_CONSTRAINTS_S");
+
+  if (node)
+    {
+      nrows = gda_server_operation_get_sequence_size (op, "/TABLE_CONSTRAINTS_S");
+
+      for (i = 0; i < nrows; i++)
+        {
+          g_string_append (string, ", ");
+
+          const GValue *tval = gda_server_operation_get_value_at (op, 
"/TABLE_CONSTRAINTS_S/%d/CONSTRAINT_STRING", i);
+
+          g_string_append (string, g_value_get_string (tval));
+        }
+    }
+
+  g_free (conflict_algo);
+  g_string_append (string, ")");
 
-       if (!hasfields) {
-               allok = FALSE;
+  if (!hasfields) {
+    allok = FALSE;
                g_set_error (error, GDA_SERVER_OPERATION_ERROR,
                             GDA_SERVER_OPERATION_INCORRECT_VALUE_ERROR,
                             "%s", _("Table to create must have at least one field"));
diff --git a/tests/db/check-db-catalog.c b/tests/db/check-db-catalog.c
index ce3bdc394..9315c9c5b 100644
--- a/tests/db/check-db-catalog.c
+++ b/tests/db/check-db-catalog.c
@@ -56,6 +56,14 @@ typedef struct {
     GdaDbTable *table;
 } DbCatalogCnc;
 
+typedef struct {
+  GdaDbCatalog *catalog;
+  GdaConnection *cnc;
+  GdaDbColumn *column_a;
+  GdaDbColumn *column_b;
+  GdaDbTable *table;
+} DbCheckCatallog;
+
 static void
 test_db_catalog_start (CheckDbObject *self,
                      G_GNUC_UNUSED gconstpointer user_data)
@@ -403,6 +411,111 @@ test_db_catalog_parse_cnc (DbCatalogCnc *self,
   g_object_unref (model);
 }
 
+static void
+test_db_catalog_constraint_start (DbCheckCatallog *self,
+                                  G_GNUC_UNUSED gconstpointer user_data)
+{
+  self->catalog = NULL;
+  self->cnc = NULL;
+  self->column_a = NULL;
+  self->column_b = NULL;
+  self->table = NULL;
+
+  gda_init();
+
+  self->cnc = gda_connection_new_from_string ("SQLite",
+                                              "DB_DIR=.;DB_NAME=db_constraint",
+                                              NULL,
+                                              GDA_CONNECTION_OPTIONS_NONE,
+                                              NULL);
+  g_assert_nonnull (self->cnc);
+
+  gboolean open_res = gda_connection_open (self->cnc, NULL);
+
+  g_assert_true (open_res);
+
+  self->catalog = gda_connection_create_db_catalog (self->cnc);
+
+  g_assert_nonnull (self->catalog);
+
+  self->table = gda_db_table_new ();
+  gda_db_base_set_name (GDA_DB_BASE(self->table),"tconstraint");
+
+  self->column_a = gda_db_column_new ();
+  gda_db_column_set_name (self->column_a,"columna");
+  gda_db_column_set_type (self->column_a, G_TYPE_INT);
+  gda_db_column_set_autoinc (self->column_a, FALSE);
+  gda_db_column_set_pkey (self->column_a, TRUE);
+
+  gda_db_table_append_column (self->table,self->column_a);
+
+  self->column_b = gda_db_column_new ();
+  gda_db_column_set_name (self->column_b, "columnb");
+  gda_db_column_set_type (self->column_b, G_TYPE_INT);
+  gda_db_column_set_autoinc (self->column_b, FALSE);
+  gda_db_column_set_pkey (self->column_b, FALSE);
+
+  gda_db_table_append_column (self->table, self->column_b);
+
+  gda_db_table_append_constraint (self->table, "CHECK (columna = columnb)");
+
+  gda_db_catalog_append_table (self->catalog, self->table);
+
+  open_res = gda_db_catalog_perform_operation (self->catalog,NULL);
+
+  g_assert_true (open_res);
+}
+
+static void
+test_db_catalog_constraint_run (DbCheckCatallog *self,
+                                G_GNUC_UNUSED gconstpointer user_data)
+{
+  GValue *val_columna = NULL;
+  GValue *val_columnb = NULL;
+  gboolean res;
+
+  val_columna = gda_value_new (G_TYPE_INT);
+  val_columnb = gda_value_new (G_TYPE_INT);
+
+  g_assert_nonnull (val_columna);
+  g_assert_nonnull (val_columnb);
+
+  g_value_set_int (val_columna, 1);
+  g_value_set_int (val_columnb, 1);
+
+  res = gda_connection_insert_row_into_table (self->cnc, "tconstraint", NULL,
+                                              "columna", val_columna,
+                                              "columnb", val_columnb,
+                                              NULL);
+
+  /* Two column must have the same values as we restricted. res is true. */
+  g_assert_true (res);
+
+  g_value_set_int (val_columna, 1);
+  g_value_set_int (val_columnb, 2);
+
+  res = gda_connection_insert_row_into_table (self->cnc, "tconstraint", NULL,
+                                              "columna", val_columna,
+                                              "columnb", val_columnb,
+                                              NULL);
+
+  /* Two column must have the same values as we restricted. res should be false, since
+   * we are inserting different numbers.
+   */
+  g_assert_false (res);
+
+  gda_value_free (val_columna);
+  gda_value_free (val_columnb);
+}
+
+static void
+test_db_catalog_constraint_finish (DbCheckCatallog *self,
+                                   G_GNUC_UNUSED gconstpointer user_data)
+{
+  g_object_unref (self->catalog);
+  gda_connection_close (self->cnc, NULL);
+}
+
 gint
 main (gint   argc,
       gchar *argv[])
@@ -445,5 +558,12 @@ main (gint   argc,
               test_db_catalog_parse_cnc,
               test_db_catalog_finish_db);
 
+  g_test_add ("/test-db/catalog-constraint",
+              DbCheckCatallog,
+              NULL,
+              test_db_catalog_constraint_start,
+              test_db_catalog_constraint_run,
+              test_db_catalog_constraint_finish);
+
   return g_test_run();
 }
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]