zorba-coders team mailing list archive

[Matthias Brantner <matthias.brantner@xxxxxxxxxx>]

Matthias Brantner has proposed merging lp:~zorba-coders/zorba/feature-jdbc-tables into lp:zorba/jdbc-module.

Commit message:
- jdbc:tables function to retrieve list of tables
- implement getBoolean to be able to retrieve boolean-typed columns from the database
- slight change to tests in order to make them run independently
- allow for reading MS SQL Server timestamp columns
- optimized creation of base64Binaries avoiding encoding

Requested reviews:
  Matthias Brantner (matthias-brantner)

For more details, see:
https://code.launchpad.net/~zorba-coders/zorba/feature-jdbc-tables/+merge/168811
-- 
https://code.launchpad.net/~zorba-coders/zorba/feature-jdbc-tables/+merge/168811
Your team Zorba Coders is subscribed to branch lp:zorba/jdbc-module.

=== modified file 'include/javaids.h'
--- include/javaids.h	2013-01-29 21:33:07 +0000
+++ include/javaids.h	2013-06-11 21:05:32 +0000
@@ -53,7 +53,7 @@
   jmethodID close;
   jmethodID createStatement;
   jmethodID prepareStatement;
-
+  jmethodID getMetadata;	
 };
 class JavaStatement {
 public:
@@ -75,6 +75,7 @@
   jmethodID beforeFirst;
   jmethodID next;
   jmethodID getInt;
+  jmethodID getBoolean;
   jmethodID getDouble;
   jmethodID getString;
   jmethodID getBLOB;
@@ -128,7 +129,12 @@
   jmethodID getBytes;
   jmethodID length;
 };
-
+class JavaDatabaseMetadata {
+public:
+  bool init();
+  jclass classID;
+  jmethodID getTables;
+};
 
 }}; // namespace zorba, jdbc
 

=== modified file 'include/jdbc.h'
--- include/jdbc.h	2013-01-29 21:33:07 +0000
+++ include/jdbc.h	2013-06-11 21:05:32 +0000
@@ -49,6 +49,7 @@
 extern JavaStatement         jStatement;
 extern JavaResultSet         jResultSet;
 extern JavaResultSetMetadata jResultSetMetadata;
+extern JavaDatabaseMetadata  jDatabaseMetadata;
 extern JavaPreparedStatement jPreparedStatement;
 extern JavaParameterMetadata jParameterMetadata;
 extern JavaBlob              jBlob;
@@ -107,6 +108,9 @@
     static String 
       getStringArg(const ExternalFunction::Arguments_t& args, int index);
 
+    static bool 
+      getOptionalStringArg(const ExternalFunction::Arguments_t& args, int index, String& aRes);
+
     static Item 
       getItemArg(const ExternalFunction::Arguments_t& args, int index);
 

=== modified file 'include/sqltypes.h'
--- include/sqltypes.h	2013-01-29 21:33:07 +0000
+++ include/sqltypes.h	2013-06-11 21:05:32 +0000
@@ -73,6 +73,7 @@
     static bool isFloat(long lType);
     static bool isString(long lType);
     static bool isBLOB(long lType);
+    static bool isBoolean(long lType);
 
 };
 

=== added file 'include/tables.h'
--- include/tables.h	1970-01-01 00:00:00 +0000
+++ include/tables.h	2013-06-11 21:05:32 +0000
@@ -0,0 +1,63 @@
+/*
+ * Copyright 2006-2012 The FLWOR Foundation.
+ * 
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ * http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+#include <zorba/empty_sequence.h>
+#include <zorba/external_module.h>
+#include <zorba/item_factory.h>
+#include <zorba/zorba.h>
+
+#include "JavaVMSingleton.h"
+
+
+namespace zorba
+{
+namespace jdbc
+{
+
+class TablesFunction : public ContextualExternalFunction
+{
+  private:
+    const ExternalModule* theModule;
+    ItemFactory* theFactory;
+    XmlDataManager* theDataManager;
+
+  public:
+    TablesFunction(const ExternalModule* aModule) :
+      theModule(aModule),
+      theFactory(Zorba::getInstance(0)->getItemFactory()),
+      theDataManager(Zorba::getInstance(0)->getXmlDataManager())
+    {}
+
+    ~TablesFunction()
+    {}
+
+  public:
+    virtual String getURI() const
+    { return theModule->getURI(); }
+
+    virtual String getLocalName() const
+    { return "tables"; }
+
+    virtual ItemSequence_t 
+      evaluate(const ExternalFunction::Arguments_t& args,
+               const zorba::StaticContext*,
+               const zorba::DynamicContext*) const;
+};
+
+
+
+}}; // namespace zorba, jdbc
+

=== modified file 'src/jdbc.xq'
--- src/jdbc.xq	2013-02-02 04:36:34 +0000
+++ src/jdbc.xq	2013-06-11 21:05:32 +0000
@@ -543,3 +543,35 @@
  :)
 declare %an:sequential function jdbc:close-dataset(
                                       $dataset-id as xs:anyURI) as empty-sequence() external;
+
+(:~
+ : Return the list of tables from a connection
+ :
+ : @param $connection-id The identifier to a connection.
+ : @param $catalog A filter of the catalog name of the tables.
+ :             Send empty-sequence for all tables.
+ : @param $schema A filter of the schema name of the tables.
+ :             Send empty-sequence for all tables.
+ : @param $table A filter of the name of the tables.
+ :             Send empty-sequence for all tables.
+ : 
+ : @error SQL08000 Connection is closed.
+ : @error SQL001 Descriptive error, see error in attached message.
+ :
+ : @return Return an object with the result data rows from the query provided,
+ :  the data rows are defined as follows:
+ :   { column:value* }*
+ :  Every row is represented by an object of column-value representation of the returned SQL result.
+ :
+ :)
+declare %an:sequential function jdbc:tables(
+                                      $connection-id as xs:anyURI, 
+                                      $catalog as xs:string?, 
+                                      $schema as xs:string?, 
+                                      $table as xs:string?) as object()* external;
+
+declare %an:sequential function jdbc:tables(
+                                      $connection-id as xs:anyURI) as object()*
+{
+	jdbc:tables($connection-id, (), (), ())
+};

=== added file 'src/jdbc.xq.src/connection/tables.cpp'
--- src/jdbc.xq.src/connection/tables.cpp	1970-01-01 00:00:00 +0000
+++ src/jdbc.xq.src/connection/tables.cpp	2013-06-11 21:05:32 +0000
@@ -0,0 +1,68 @@
+/*
+ * Copyright 2006-2012 The FLWOR Foundation.
+ * 
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ * http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+#include "tables.h"
+#include "jdbc.h"
+#include "jsonitemsequence.h"
+
+namespace zorba
+{
+namespace jdbc
+{
+
+ItemSequence_t
+TablesFunction::evaluate(const ExternalFunction::Arguments_t& args,
+                           const zorba::StaticContext* aStaticContext,
+                           const zorba::DynamicContext* aDynamincContext) const
+{
+  CHECK_CONNECTION
+  jobject result=NULL;
+
+  JDBC_MODULE_TRY
+
+    String lStrUUID = JdbcModule::getStringArg(args, 0);
+
+    jobject oConnection = JdbcModule::getObject(aDynamincContext, lStrUUID, INSTANCE_MAP_CONNECTIONS);
+
+    jobject oDatabaseMetadata = env->CallObjectMethod(oConnection, jConnection.getMetadata);
+
+    String sTemp;
+    jstring jCatalog = NULL;
+    if (JdbcModule::getOptionalStringArg(args, 1, sTemp))
+    {
+      jCatalog = env->NewStringUTF(sTemp.c_str());
+    }
+    jstring jSchema = NULL;
+    if (JdbcModule::getOptionalStringArg(args, 2, sTemp))
+    {
+      jSchema = env->NewStringUTF(sTemp.c_str());
+    }
+    jstring jTable = NULL;
+    if (JdbcModule::getOptionalStringArg(args, 3, sTemp))
+    {
+      jTable = env->NewStringUTF(sTemp.c_str());
+    }
+    jobjectArray jTypes = env->NewObjectArray(1, env->FindClass("java/lang/String"), env->NewStringUTF("TABLE"));
+    
+    result = env->CallObjectMethod(oDatabaseMetadata, jDatabaseMetadata.getTables, jCatalog, jSchema, jTable, jTypes);
+    CHECK_EXCEPTION
+
+  JDBC_MODULE_CATCH
+  
+  return ItemSequence_t(new JSONItemSequence(result));
+}
+
+}}; // namespace zorba, jdbc

=== modified file 'src/jdbc.xq.src/javaids.cpp'
--- src/jdbc.xq.src/javaids.cpp	2013-01-29 21:33:07 +0000
+++ src/jdbc.xq.src/javaids.cpp	2013-06-11 21:05:32 +0000
@@ -43,6 +43,7 @@
     close = env->GetMethodID(classID, "close", "()V");
     createStatement = env->GetMethodID(classID, "createStatement", "()Ljava/sql/Statement;");
     prepareStatement = env->GetMethodID(classID, "prepareStatement", "(Ljava/lang/String;)Ljava/sql/PreparedStatement;");
+    getMetadata = env->GetMethodID(classID, "getMetaData", "()Ljava/sql/DatabaseMetaData;");
 
     TRANSACTION_NONE = env->GetStaticIntField(classID, env->GetStaticFieldID(classID, "TRANSACTION_NONE", "I"));
     TRANSACTION_READ_UNCOMMITTED = env->GetStaticIntField(classID, env->GetStaticFieldID(classID, "TRANSACTION_READ_UNCOMMITTED", "I"));
@@ -68,6 +69,7 @@
     beforeFirst = env->GetMethodID(classID, "beforeFirst", "()V");
     next = env->GetMethodID(classID, "next", "()Z");
     getInt = env->GetMethodID(classID, "getInt", "(I)I");
+    getBoolean = env->GetMethodID(classID, "getBoolean", "(I)Z");
     getDouble = env->GetMethodID(classID, "getDouble", "(I)D");
     getString = env->GetMethodID(classID, "getString", "(I)Ljava/lang/String;");
     getBLOB = env->GetMethodID(classID, "getBlob", "(I)Ljava/sql/Blob;");
@@ -87,6 +89,11 @@
     COLUMN_NULLABLE_UNKNOWN = env->GetStaticIntField(classID, env->GetStaticFieldID(classID, "columnNullableUnknown", "I"));
     return true;
   }
+  bool JavaDatabaseMetadata::init() {
+    classID = env->FindClass("java/sql/DatabaseMetaData");
+    getTables = env->GetMethodID(classID, "getTables", "(Ljava/lang/String;Ljava/lang/String;Ljava/lang/String;[Ljava/lang/String;)Ljava/sql/ResultSet;");
+    return true;
+  }
   bool JavaPreparedStatement::init() {
     classID = env->FindClass("java/sql/PreparedStatement");
     clearParameters = env->GetMethodID(classID, "clearParameters", "()V");

=== modified file 'src/jdbc.xq.src/jdbc.cpp'
--- src/jdbc.xq.src/jdbc.cpp	2013-06-04 02:17:48 +0000
+++ src/jdbc.xq.src/jdbc.cpp	2013-06-11 21:05:32 +0000
@@ -53,6 +53,7 @@
 #include "metadata.h"
 #include "resultset.h"
 #include "closedataset.h"
+#include "tables.h"
 
 
 namespace zorba
@@ -66,6 +67,7 @@
 JavaStatement         jStatement;
 JavaResultSet         jResultSet;
 JavaResultSetMetadata jResultSetMetadata;
+JavaDatabaseMetadata  jDatabaseMetadata;
 JavaPreparedStatement jPreparedStatement;
 JavaParameterMetadata jParameterMetadata;
 JavaBlob              jBlob;
@@ -181,6 +183,10 @@
     {
       lFunc = new CloseDataSetFunction(this);
     }
+  else if (localName == "tables")
+    {
+      lFunc = new TablesFunction(this);
+    }
   }
   return lFunc;
 }
@@ -263,6 +269,21 @@
   return result;
 }
 
+bool
+JdbcModule::getOptionalStringArg(const ExternalFunction::Arguments_t& args, int index, String& aRes) {
+  Iterator_t lIter = args[index]->getIterator();
+  lIter->open();
+  Item item;
+  if( lIter->next(item) )
+  {
+    aRes = item.getStringValue();
+    lIter->close();
+    return true;
+  }
+  lIter->close();
+  return false;
+}
+
 Item
 JdbcModule::getItemArg(const ExternalFunction::Arguments_t& args, int index) {
   Item item;
@@ -331,6 +352,7 @@
     jStatement.init();
     jResultSet.init();
     jResultSetMetadata.init();
+    jDatabaseMetadata.init();
     jPreparedStatement.init();
     jParameterMetadata.init();
     jBlob.init();

=== modified file 'src/jdbc.xq.src/jsonitemsequence.cpp'
--- src/jdbc.xq.src/jsonitemsequence.cpp	2013-04-12 22:41:45 +0000
+++ src/jdbc.xq.src/jsonitemsequence.cpp	2013-06-11 21:05:32 +0000
@@ -47,6 +47,16 @@
         env->ReleaseStringUTFChars(oName, cName);
         CHECK_EXCEPTION 
         columnTypes[i] = env->CallIntMethod(oMetadata, jResultSetMetadata.getColumnType, i+1);
+
+        if (columnTypes[i] == SQLTypes::BINARY)
+        {
+          jstring oType = (jstring) env->CallObjectMethod(oMetadata, jResultSetMetadata.getColumnTypeName, i+1);
+          CHECK_EXCEPTION
+          const char * cType = env->GetStringUTFChars(oType, 0);
+          CHECK_EXCEPTION
+          if (strcmp(cType, "timestamp") == 0) columnTypes[i] = SQLTypes::TIMESTAMP;
+        }
+
         CHECK_EXCEPTION
       }
     JDBC_MODULE_CATCH
@@ -71,11 +81,15 @@
         if (SQLTypes::isInt(columnTypes[i])) {
           int value = env->CallIntMethod(oResultSet, jResultSet.getInt, i+1);
           CHECK_EXCEPTION
-          aValue = itemFactory->createInt(value);
+          aValue = itemFactory->createInteger(value);
         } else if (SQLTypes::isFloat(columnTypes[i])) {
           double value = env->CallDoubleMethod(oResultSet, jResultSet.getDouble, i+1);
           CHECK_EXCEPTION
           aValue = itemFactory->createDouble(value);
+        } else if (SQLTypes::isBoolean(columnTypes[i])) {
+            bool value = env->CallBooleanMethod(oResultSet, jResultSet.getBoolean, i+1);
+          CHECK_EXCEPTION
+          aValue = itemFactory->createBoolean(value);
         } else if (SQLTypes::isString(columnTypes[i])) {
           jstring sValue = (jstring) env->CallObjectMethod(oResultSet, jResultSet.getString, i+1);
           CHECK_EXCEPTION
@@ -95,7 +109,7 @@
             CHECK_EXCEPTION
             jbyteArray bytes  = (jbyteArray) env->CallObjectMethod(oBlob, jBlob.getBytes, 1, length);
             CHECK_EXCEPTION
-            const char* byteString = (const char*)(env->GetByteArrayElements(bytes, 0));
+            const unsigned char* byteString = reinterpret_cast<const unsigned char*>(env->GetByteArrayElements(bytes, 0));
             aValue = itemFactory->createBase64Binary(byteString, length);
           } else {
             aValue = itemFactory->createJSONNull();

=== modified file 'src/jdbc.xq.src/sqltypes.cpp'
--- src/jdbc.xq.src/sqltypes.cpp	2013-02-02 04:36:34 +0000
+++ src/jdbc.xq.src/sqltypes.cpp	2013-06-11 21:05:32 +0000
@@ -118,11 +118,15 @@
 }
 
 bool SQLTypes::isInt(long lType){
-  return ( (lType == INTEGER) || (lType==BIGINT) || (lType==TINYINT) || (lType==SMALLINT) || (lType==BIT) );
+  return ( (lType == INTEGER) || (lType==BIGINT) || (lType==TINYINT) || (lType==SMALLINT) );
 }
 
 bool SQLTypes::isFloat(long lType){
   return ((lType == DECIMAL) || (lType==DOUBLE) || (lType==FLOAT) || (lType==NUMERIC) || (lType==REAL) );
 }
 
+bool SQLTypes::isBoolean(long lType){
+  return ((lType == BOOLEAN) || (lType==BIT) );
+}
+
 }}; // namespace zorba, jdbc