oracle / soda-for-java Goto Github PK

I see the project() feature in the code in OracleOperationBuilderImpl but not made available via the interface. We want to implement something like
project().and("testfields.id").as("someid")
that can give use the testfields.id as someid(alias) in the returned query resultset.

Please suggest if there is a plan to release the project functionality soon.

Thanks
Anand

In the SODA driver the batch size used in the PreparedStatement for persisting a collection of documents is hardcoded to 100. It would be a useful addition to enable the caller to specify the batch size themselves to override the default and optimise inserts for their workloads. For example, in our tests for our workload setting the batch size to 10,000 when using raw JDBC to persist 100,000 JSON (BLOB) documents results in a massive performance increase over using 100.

Perhaps it would be useful to let the caller specify what batch size to use to optimise their inserts. Something along the lines of:

public List<OracleDocument> insertAndGet(Iterator<OracleDocument> documents, int batchSize)
    throws OracleBatchException;

alternatively allowing callers to specify the batch size to use in the "hints" options.

public List<OracleDocument> insertAndGet(Iterator<OracleDocument> documents, Map<String, ?> options)
    throws OracleBatchException;

Thoughts?

Hi,

The client only support JDBC connections, and now R2DBC would be great.

Regards

If a field stores number only, the result of $sort on that field still comes out in alphabetical. e.g. Sort order becomes 1, 100, 2, 20, 3 ... etc.
Where it should be 1,2,3,20,100 etc.

We are using SODA 1.0.4.

Are there plans to move to Java 8?

The filter method of the builder currently takes OracleDocument. Could you please add a shortcut method that takes string?

OracleCursor should be enabled to to be used in the Java for-each loop as well as the Java 8 forEach() streams implementation. The .getCursor() operation will return a cursor to self-contained OracleDocument objects. Right now the user has to write ugly boiler plate code to iterate over these documents, e.g.:

OracleCursor c = col.find().getCursor();

while (c.hasNext()) {
  OracleDocument resultDoc = c.next();
  System.out.println("Document content: " + resultDoc.getContentAsString());
}

c.close;

This should be simplified to allow the user to use the for-each construct instead:

for (OracleDocument resultDoc : col.find().getCursor()) {
  System.out.println("Document content: " + resultDoc.getContentAsString());
}

Mongo compatible collection creation on 19c using ords PUT against /ords/admin/soda/latest/< collectionname > may look similar to ...

{"contentColumn":{"name":"DATA"},"keyColumn":{"name":"ID","sqlType":"VARCHAR2","maxLength":255,"assignmentMethod":"EMBEDDED_OID","path":"_id"},"versionColumn":{"name":"VERSION","method":"UUID"},"lastModifiedColumn":{"name":"LAST_MODIFIED"},"creationTimeColumn":{"name":"CREATED_ON"}}

OracleRDBMSMetadataBuilder doesn't appear to support that path parameter override.

When trying to create a new document collection and save a new document with the 19.3 JDBC drivers, I received the following exception when calling insertAndGet:

oracle.soda.OracleException: Error occurred during conversion of the input document’s JSON content to binary. Ensure the content of the input document is valid JSON

This error message is misleading as the document was indeed valid JSON. The fix was to update to the latest 19.6 JDBC drivers, but this error message should have been more clear as to what the issue was.

Release 1.1.7 is not available at e.g. mvnrepository.com (lates available release is 1.1.4) - artifacts should be published to public maven repositories for easier access

I am working in migrating from mongo to oracle SODA, some of the operations that are supported in mongo are not available in Oracle ,so struggling to find a workaround. I tried $exists for $elematch but the query did not return similar result.
Please suggest if there is fix for this or any other strategy
A sample query from mongo
{ "$and" : [{ "$and" : [{ "$and" : [{ "$or" : [{ "somefields" : { "$all" : [{ "$elemMatch" : { "rawid" : "57ff7d764343rerer333", "value" : { "$ne" : "676dfeererefdfdfd" } } }, { "$elemMatch" : { "rawid" : "5e01e27c61450e2eab223a4a", "macrovalue" : { "$regex" : "^xyz$", "$options" : "i" } } }] } }] }, { "isdeleted" : false }] }, { "someflowid" : "57f782c4346eerer343" }] }, { "someinstanceid" : { "$in" : [] } }] }

Thanks,Anand

It's not clear what combination of driver version to use for soda 1.1.7 and 21c and collection is using the new oracle "JSON" data type. Using ver ojdbc10 - 19.8.00 throws Exceptions.

Using

<dependency>
       <groupId>com.oracle.database.jdbc</groupId>
       <artifactId>ojdbc10</artifactId>
       <version>19.8.0.0</version>
</dependency>

Json param is:

{"email":"[email protected]","externalIds":[],"firstName":"John","registeredDateUtc":"2020-12-09T21:57:21Z","role":"USER","surname":"Doe"}

Code:

String json =  ... json above...;
try (var con = dataSource.getConnection()) {
            var db = oracleClient.getDatabase(con);
            var messageDoc = db.createDocumentFromString(json);
            var collection = db.openCollection(collectionName);
            collection.insert(messageDoc);
        } catch (Exception e) {
            logger.error("An exception occurred adding entity " + entity, e);
            throw new RuntimeException(e);
        }

Exceptions thrown:

1. When ID generation for collection is set to UUID:

java.lang.RuntimeException: oracle.soda.OracleException: Error occurred during conversion of the input document's JSON content to binary. Ensure the content of the input document is valid JSON.

2. When ID generation for collection is set to CLIENT:
   Also throws an Exception.

SODA has a small dependency on java.activation:

./src/oracle/soda/rdbms/impl/OracleDocumentImpl.java:
import javax.activation.MimeType;

./src/oracle/soda/rdbms/impl/OracleDocumentImpl.java:
import javax.activation.MimeTypeParseException;

This Java EE module is deprecated for removal in Java 9 and will be removed from the jdk in Java 11.
http://openjdk.java.net/jeps/320

Can we remove this dependency from soda?

Since this is an open source project, can it be released as a Maven artifact on the Public Repository, to be easily integrated on Maven, Gradle, and SBT based projects?

It would also be nice if it could use Travis CI for continuous integration/testing/release process.

When doing
collection.find().key(key).mergeOne(documentToMerge)
where documentToMerge.contentAsString.length > 4000 the json_document column is set to null without any exception or error
The JSON_DOCUMENT column is of type BLOB, see metadata below.
Jdbc-driver used: ojdbc8

Should oracle.soda.rdbms.impl.TableCollectionImpl#setPayloadBlobWorkaround be used at https://github.com/oracle/soda-for-java/blob/master/src/oracle/soda/rdbms/impl/TableCollectionImpl.java#L2410 instead of oracle.soda.rdbms.impl.TableCollectionImpl#setPayloadBlob ?

{
  "schemaName": "OUR_SCHEMA_NAME",
  "tableName": "OurCollection",
  "keyColumn": {
    "name": "ID",
    "sqlType": "VARCHAR2",
    "maxLength": 255,
    "assignmentMethod": "CLIENT"
  },
  "contentColumn": {
    "name": "JSON_DOCUMENT",
    "sqlType": "BLOB",
    "compress": "NONE",
    "cache": true,
    "encrypt": "NONE",
    "validation": "STANDARD"
  },
  "versionColumn": {
    "name": "VERSION",
    "type": "String",
    "method": "SHA256"
  },
  "lastModifiedColumn": {
    "name": "LAST_MODIFIED"
  },
  "creationTimeColumn": {
    "name": "CREATED_ON"
  },
  "readOnly": false
}