GNOME.org
 

[libgda] Brought the Oracle provider up to date

commit 59752c39afc506204ce57dd920eaf4825ce58e25
Author: Vivien Malerba <malerba gnome-db org>
Date:   Thu Jul 23 20:50:42 2009 +0200

    Brought the Oracle provider up to date
    
    * with SQL reserved keywords for v8 and V9
    * with the implementation of the identifier_quote() virtual method
    * with an update test-identifiers-quotes test

 providers/oracle/Makefile.am           |   16 ++-
 providers/oracle/gda-oracle-provider.c |  213 +++++++++++++++++++++++++++++++-
 providers/oracle/gda-oracle-util.c     |   28 ++++
 providers/oracle/gda-oracle-util.h     |    4 +
 providers/oracle/keywords_8.list       |   29 +++++
 providers/oracle/keywords_9.list       |  109 ++++++++++++++++
 tests/test-identifiers-quotes.c        |   11 ++
 7 files changed, 406 insertions(+), 4 deletions(-)
---
diff --git a/providers/oracle/Makefile.am b/providers/oracle/Makefile.am
index d73a8ac..88911b1 100644
--- a/providers/oracle/Makefile.am
+++ b/providers/oracle/Makefile.am
@@ -11,6 +11,15 @@ AM_CPPFLAGS = \
 	$(LIBGDA_CFLAGS) \
 	$(ORACLE_CFLAGS) 
 
+keyword_files=keywords_8.list keywords_9.list
+pkeyword_files=$(addprefix $(top_srcdir)/providers/oracle/,$(keyword_files))
+mkkeywordhash$(EXEEXT_FOR_BUILD): $(top_srcdir)/libgda/sqlite/mkkeywordhash.c
+	$(CC_FOR_BUILD) -g -o mkkeywordhash$(EXEEXT_FOR_BUILD) $(GDA_DEBUG_FLAGS) $<
+
+keywords_hash.c: mkkeywordhash$(EXEEXT_FOR_BUILD) $(pkeyword_files)
+	./mkkeywordhash$(EXEEXT_FOR_BUILD) $(top_srcdir)/providers/oracle/keywords_8.list V8 > keywords_hash.c
+	./mkkeywordhash$(EXEEXT_FOR_BUILD) $(top_srcdir)/providers/oracle/keywords_9.list V9 >> keywords_hash.c
+
 # parser generation
 parser.c parser.h: parser.y $(top_builddir)/libgda/sql-parser/lemon$(EXEEXT_FOR_BUILD)
 	- $(top_builddir)/libgda/sql-parser/lemon$(EXEEXT_FOR_BUILD) -q -d $(srcdir)/parser.y $(top_srcdir)/libgda/sql-parser/lempar.c
@@ -21,7 +30,7 @@ gen_def$(EXEEXT_FOR_BUILD): gen_def.c
 oracle_token_types.h: gen_def$(EXEEXT_FOR_BUILD) parser.h
 	./gen_def$(EXEEXT_FOR_BUILD) > oracle_token_types.h
 
-$(OBJECTS) $(libgda_oracle_la_OBJECTS): oracle_token_types.h
+$(OBJECTS) $(libgda_oracle_la_OBJECTS): oracle_token_types.h keywords_hash.c
 
 libgda_oracle_la_SOURCES = \
 	gda-oracle-blob-op.c \
@@ -63,7 +72,8 @@ xml_DATA = $(xml_in_files:.xml.in=.xml)
 pkgconfigdir = $(libdir)/pkgconfig
 pkgconfig_DATA = libgda-oracle-4.0.pc
 
-EXTRA_DIST = $(xml_in_files) libgda-oracle-4.0.pc.in parser.y gen_def.c
+EXTRA_DIST = $(xml_in_files) libgda-oracle-4.0.pc.in parser.y gen_def.c $(keyword_files)
 DISTCLEANFILES = $(xml_DATA)
 
-CLEANFILES = parser.h parser.c parser.out oracle_token_types.h gen_def$(EXEEXT_FOR_BUILD)
+CLEANFILES = parser.h parser.c parser.out oracle_token_types.h gen_def$(EXEEXT_FOR_BUILD) \
+	mkkeywordhash$(EXEEXT_FOR_BUILD) keywords_hash.c
diff --git a/providers/oracle/gda-oracle-provider.c b/providers/oracle/gda-oracle-provider.c
index 5799185..dd80ea9 100644
--- a/providers/oracle/gda-oracle-provider.c
+++ b/providers/oracle/gda-oracle-provider.c
@@ -117,6 +117,11 @@ static GObject             *gda_oracle_provider_statement_execute (GdaServerProv
 								 guint *task_id, GdaServerProviderExecCallback async_cb, 
 								 gpointer cb_data, GError **error);
 
+/* Quoting */
+static gchar               *gda_oracle_identifier_quote    (GdaServerProvider *provider, GdaConnection *cnc,
+							    const gchar *id,
+							    gboolean meta_store_convention, gboolean force_quotes);
+
 /* distributed transactions */
 static gboolean gda_oracle_provider_xa_start    (GdaServerProvider *provider, GdaConnection *cnc, 
 						   const GdaXaTransactionId *xid, GError **error);
@@ -197,6 +202,7 @@ gda_oracle_provider_class_init (GdaOracleProviderClass *klass)
 	provider_class->is_busy = NULL;
 	provider_class->cancel = NULL;
 	provider_class->create_connection = NULL;
+	provider_class->identifier_quote = gda_oracle_identifier_quote;
 
 	memset (&(provider_class->meta_funcs), 0, sizeof (GdaServerProviderMeta));
 	provider_class->meta_funcs._info = _gda_oracle_meta__info;
@@ -727,7 +733,6 @@ static const gchar *
 gda_oracle_provider_get_server_version (GdaServerProvider *provider, GdaConnection *cnc)
 {
 	OracleConnectionData *cdata;
-	static guchar version[512];
 
 	g_return_val_if_fail (GDA_IS_CONNECTION (cnc), NULL);
 	g_return_val_if_fail (gda_connection_get_provider (cnc) == provider, NULL);
@@ -2107,6 +2112,212 @@ gda_oracle_provider_xa_recover (GdaServerProvider *provider, GdaConnection *cnc,
 	return NULL;
 }
 
+static gchar *
+identifier_add_quotes (const gchar *str)
+{
+        gchar *retval, *rptr;
+        const gchar *sptr;
+        gint len;
+
+        if (!str)
+                return NULL;
+
+        len = strlen (str);
+        retval = g_new (gchar, 2*len + 3);
+        *retval = '"';
+        for (rptr = retval+1, sptr = str; *sptr; sptr++, rptr++) {
+                if (*sptr == '"') {
+                        *rptr = '\\';
+                        rptr++;
+                        *rptr = *sptr;
+                }
+                else
+                        *rptr = *sptr;
+        }
+        *rptr = '"';
+        rptr++;
+        *rptr = 0;
+        return retval;
+}
+
+static gboolean
+_sql_identifier_needs_quotes (const gchar *str)
+{
+	const gchar *ptr;
+
+	g_return_val_if_fail (str, FALSE);
+	for (ptr = str; *ptr; ptr++) {
+		/* quote if 1st char is a number */
+		if ((*ptr <= '9') && (*ptr >= '0')) {
+			if (ptr == str)
+				return TRUE;
+			continue;
+		}
+		if (((*ptr >= 'A') && (*ptr <= 'Z')) ||
+		    ((*ptr >= 'a') && (*ptr <= 'z')))
+			continue;
+
+		if ((*ptr != '$') && (*ptr != '_') && (*ptr != '#'))
+			return TRUE;
+	}
+	return FALSE;
+}
+
+/* Returns: @str */
+static gchar *
+ora_remove_quotes (gchar *str)
+{
+        glong total;
+        gchar *ptr;
+        glong offset = 0;
+	char delim;
+	
+	if (!str)
+		return NULL;
+	delim = *str;
+	if ((delim != '\'') && (delim != '"'))
+		return str;
+
+
+        total = strlen (str);
+        if (str[total-1] == delim) {
+		/* string is correclty terminated */
+		g_memmove (str, str+1, total-2);
+		total -=2;
+	}
+	else {
+		/* string is _not_ correclty terminated */
+		g_memmove (str, str+1, total-1);
+		total -=1;
+	}
+        str[total] = 0;
+
+        ptr = (gchar *) str;
+        while (offset < total) {
+                /* we accept the "''" as a synonym of "\'" */
+                if (*ptr == delim) {
+                        if (*(ptr+1) == delim) {
+                                g_memmove (ptr+1, ptr+2, total - offset);
+                                offset += 2;
+                        }
+                        else {
+                                *str = 0;
+                                return str;
+                        }
+                }
+                if (*ptr == '\\') {
+                        if (*(ptr+1) == '\\') {
+                                g_memmove (ptr+1, ptr+2, total - offset);
+                                offset += 2;
+                        }
+                        else {
+                                if (*(ptr+1) == delim) {
+                                        *ptr = delim;
+                                        g_memmove (ptr+1, ptr+2, total - offset);
+                                        offset += 2;
+                                }
+                                else {
+                                        *str = 0;
+                                        return str;
+                                }
+                        }
+                }
+                else
+                        offset ++;
+
+                ptr++;
+        }
+
+        return str;
+}
+
+static gchar *
+gda_oracle_identifier_quote (GdaServerProvider *provider, GdaConnection *cnc,
+			     const gchar *id,
+			     gboolean for_meta_store, gboolean force_quotes)
+{
+        GdaSqlReservedKeywordsFunc kwfunc;
+        OracleConnectionData *cdata = NULL;
+
+        if (cnc) {
+                cdata = (OracleConnectionData*) gda_connection_internal_get_provider_data (cnc);
+                if (!cdata)
+                        return NULL;
+        }
+
+        kwfunc = _gda_oracle_get_reserved_keyword_func (cdata);
+
+	if (for_meta_store) {
+		gchar *tmp, *ptr;
+		tmp = ora_remove_quotes (g_strdup (id));
+		if (kwfunc (tmp)) {
+			ptr = gda_sql_identifier_add_quotes (tmp);
+			g_free (tmp);
+			return ptr;
+		}
+		else if (force_quotes) {
+			/* quote if non UC characters or digits at the 1st char or non allowed characters */
+			for (ptr = tmp; *ptr; ptr++) {
+				if (((*ptr >= 'A') && (*ptr <= 'Z')) ||
+				    ((*ptr >= '0') && (*ptr <= '9') && (ptr != tmp)) ||
+				    (*ptr == '_'))
+					continue;
+				else {
+					ptr = gda_sql_identifier_add_quotes (tmp);
+					g_free (tmp);
+					return ptr;
+				}
+			}
+			for (ptr = tmp; *ptr; ptr++) {
+				if ((*ptr >= 'A') && (*ptr <= 'Z'))
+					*ptr += 'a' - 'A';
+			}
+			return tmp;
+		}
+		else {
+			for (ptr = tmp; *ptr; ptr++) {
+				if (*id == '"') {
+					if (((*ptr >= 'A') && (*ptr <= 'Z')) ||
+					    ((*ptr >= '0') && (*ptr <= '9') && (ptr != tmp)) ||
+					    (*ptr == '_'))
+						continue;
+					else {
+						ptr = gda_sql_identifier_add_quotes (tmp);
+						g_free (tmp);
+						return ptr;
+					}
+				}
+				else if ((*ptr >= 'A') && (*ptr <= 'Z'))
+					*ptr += 'a' - 'A';
+				else if ((*ptr >= '0') && (*ptr <= '9') && (ptr == tmp)) {
+					ptr = gda_sql_identifier_add_quotes (tmp);
+					g_free (tmp);
+					return ptr;
+				}
+			}
+			if (*id == '"') {
+				for (ptr = tmp; *ptr; ptr++) {
+					if ((*ptr >= 'A') && (*ptr <= 'Z'))
+						*ptr += 'a' - 'A';
+				}
+			}
+			return tmp;
+		}
+	}
+	else {
+		if (*id == '"') {
+			/* there are already some quotes */
+			return g_strdup (id);
+		}
+		if (kwfunc (id) || _sql_identifier_needs_quotes (id) || force_quotes)
+			return identifier_add_quotes (id);
+
+		/* nothing to do */
+		return g_strdup (id);
+	}
+}
+
+
 /*
  * Free connection's specific data
  */
diff --git a/providers/oracle/gda-oracle-util.c b/providers/oracle/gda-oracle-util.c
index 2930efc..ba743ba 100644
--- a/providers/oracle/gda-oracle-util.c
+++ b/providers/oracle/gda-oracle-util.c
@@ -33,6 +33,9 @@
 #include "gda-oracle-util.h"
 #include "gda-oracle-blob-op.h"
 
+#include <libgda/sqlite/keywords_hash.h>
+#include "keywords_hash.c" /* this one is dynamically generated */
+
 /* 
  * _gda_oracle_make_error
  * This function uses OCIErrorGet to get a description of the error to
@@ -715,3 +718,28 @@ gda_g_type_to_static_type (GType type)
 	g_error ("Missing type '%s' in GDA static types", g_type_name (type));
 	return 0;
 }
+
+#ifdef GDA_DEBUG
+void
+_gda_oracle_test_keywords (void)
+{
+        V8test_keywords();
+        V9test_keywords();
+}
+#endif
+
+GdaSqlReservedKeywordsFunc
+_gda_oracle_get_reserved_keyword_func (OracleConnectionData *cdata)
+{
+        if (cdata) {
+                switch (cdata->major_version) {
+                case 8:
+                        return V8is_keyword;
+		case 9:
+                default:
+                        return V9is_keyword;
+                break;
+                }
+        }
+	return V9is_keyword;
+}
diff --git a/providers/oracle/gda-oracle-util.h b/providers/oracle/gda-oracle-util.h
index e381553..8764b0c 100644
--- a/providers/oracle/gda-oracle-util.h
+++ b/providers/oracle/gda-oracle-util.h
@@ -107,6 +107,10 @@ void                _gda_oracle_set_value (GValue *value,
 					   GdaConnection *cnc);
 void                _gda_oracle_value_free (GdaOracleValue *ora_value);
 
+#ifdef GDA_DEBUG
+void                _gda_oracle_test_keywords (void);
+#endif
+GdaSqlReservedKeywordsFunc _gda_oracle_get_reserved_keyword_func (OracleConnectionData *cdata);
 
 
 G_END_DECLS
diff --git a/providers/oracle/keywords_8.list b/providers/oracle/keywords_8.list
new file mode 100644
index 0000000..7ce5e56
--- /dev/null
+++ b/providers/oracle/keywords_8.list
@@ -0,0 +1,29 @@
+ACCESS 	ELSE 	MODIFY 	START
+ADD 	EXCLUSIVE 	NOAUDIT 	SELECT
+ALL 	EXISTS 	NOCOMPRESS 	SESSION
+ALTER 	FILE 	NOT 	SET
+AND 	FLOAT 	NOTFOUND 	SHARE
+ANY 	FOR 	NOWAIT 	SIZE
+ARRAYLEN 	FROM 	NULL 	SMALLINT
+AS 	GRANT 	NUMBER 	SQLBUF
+ASC 	GROUP 	OF 	SUCCESSFUL
+AUDIT 	HAVING 	OFFLINE 	SYNONYM
+BETWEEN 	IDENTIFIED 	ON 	SYSDATE
+BY 	IMMEDIATE 	ONLINE 	TABLE
+CHAR 	IN 	OPTION 	THEN
+CHECK 	INCREMENT 	OR 	TO
+CLUSTER 	INDEX 	ORDER 	TRIGGER
+COLUMN 	INITIAL 	PCTFREE 	UID
+COMMENT 	INSERT 	PRIOR 	UNION
+COMPRESS 	INTEGER 	PRIVILEGES 	UNIQUE
+CONNECT 	INTERSECT 	PUBLIC 	UPDATE
+CREATE 	INTO 	RAW 	USER
+CURRENT 	IS 	RENAME 	VALIDATE
+DATE 	LEVEL 	RESOURCE 	VALUES
+DECIMAL 	LIKE 	REVOKE 	VARCHAR
+DEFAULT 	LOCK 	ROW 	VARCHAR2
+DELETE 	LONG 	ROWID 	VIEW
+DESC 	MAXEXTENTS 	ROWLABEL 	WHENEVER
+DISTINCT 	MINUS 	ROWNUM 	WHERE
+DROP 	MODE 	ROWS 	WITH
+
diff --git a/providers/oracle/keywords_9.list b/providers/oracle/keywords_9.list
new file mode 100644
index 0000000..9c8a856
--- /dev/null
+++ b/providers/oracle/keywords_9.list
@@ -0,0 +1,109 @@
+ACCESS
+ADD 
+ALL 
+ALTER 
+AND 
+ANY 
+AS 
+ASC 
+AUDIT
+BETWEEN 
+BY 
+CHAR 
+CHECK 
+CLUSTER
+COLUMN
+COMMENT
+COMPRESS
+CONNECT 
+CREATE 
+CURRENT 
+DATE 
+DECIMAL 
+DEFAULT 
+DELETE 
+DESC 
+DISTINCT 
+DROP 
+ELSE 
+EXCLUSIVE
+EXISTS
+FILE
+FLOAT 
+FOR 
+FROM 
+GRANT 
+GROUP 
+HAVING 
+IDENTIFIED
+IMMEDIATE 
+IN 
+INCREMENT
+INDEX
+INITIAL
+INSERT 
+INTEGER 
+INTERSECT 
+INTO 
+IS 
+LEVEL 
+LIKE 
+LOCK
+LONG
+MAXEXTENTS
+MINUS
+MLSLABEL
+MODE
+MODIFY
+NOAUDIT
+NOCOMPRESS
+NOT 
+NOWAIT
+NULL 
+NUMBER
+OF 
+OFFLINE
+ON 
+ONLINE
+OPTION 
+OR 
+ORDER 
+PCTFREE
+PRIOR 
+PRIVILEGES 
+PUBLIC 
+RAW
+RENAME
+RESOURCE
+REVOKE 
+ROW
+ROWID
+ROWNUM
+ROWS 
+SELECT 
+SESSION 
+SET 
+SHARE
+SIZE 
+SMALLINT 
+START
+SUCCESSFUL
+SYNONYM
+SYSDATE
+TABLE 
+THEN 
+TO 
+TRIGGER
+UID
+UNION 
+UNIQUE 
+UPDATE 
+USER 
+VALIDATE
+VALUES 
+VARCHAR 
+VARCHAR2
+VIEW 
+WHENEVER 
+WHERE
+WITH 
diff --git a/tests/test-identifiers-quotes.c b/tests/test-identifiers-quotes.c
index 79f6792..4193fa8 100644
--- a/tests/test-identifiers-quotes.c
+++ b/tests/test-identifiers-quotes.c
@@ -112,6 +112,17 @@ ATest tests[] = {
 	{"SQLite", "[5ive]", "\"5ive\"", "\"5ive\"", "\"5ive\"", "\"5ive\""},
 	{"SQLite", "`5ive`", "\"5ive\"", "\"5ive\"", "\"5ive\"", "\"5ive\""},
 
+	{"Oracle", "\"double word\"", "\"double word\"", "\"double word\"", "\"double word\"", "\"double word\""},
+	{"Oracle", "\"CapitalTest\"", "\"CapitalTest\"", "\"CapitalTest\"", "\"CapitalTest\"", "\"CapitalTest\""},
+	{"Oracle", "CapitalTest", "CapitalTest", "capitaltest", "\"CapitalTest\"", "\"CapitalTest\""},
+	{"Oracle", "\"mytable\"", "\"mytable\"", "\"mytable\"", "\"mytable\"", "\"mytable\""},
+	{"Oracle", "mytable", "mytable", "mytable", "\"mytable\"", "\"mytable\""},
+	{"Oracle", "MYTABLE", "MYTABLE", "mytable", "\"MYTABLE\"", "mytable"},
+	{"Oracle", "\"MYTABLE\"", "\"MYTABLE\"", "mytable", "\"MYTABLE\"", "mytable"},
+	{"Oracle", "desc", "\"desc\"", "\"desc\"", "\"desc\"", "\"desc\""},
+	{"Oracle", "5ive", "\"5ive\"", "\"5ive\"", "\"5ive\"", "\"5ive\""},
+	{"Oracle", "\"5ive\"", "\"5ive\"", "\"5ive\"", "\"5ive\"", "\"5ive\""},
+
 };
 
 static gboolean
[Date Prev][Date Next]   [Thread Prev][Thread Next]   [Thread Index] [Date Index] [Author Index]