Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

OracleRDBMSMetadataBuilder support for keyColumn path field - to support Mongo collection creation #16

Open
mshannongit opened this issue Apr 11, 2022 · 5 comments

Comments

@mshannongit
Copy link

mshannongit commented Apr 11, 2022

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.

@mshannongit
Copy link
Author

Additionally , keyColumnAssignmentMethod("EMBEDDED_OID") needs support added.

final OracleDocument metadata = cl.createMetadataBuilder().contentColumnName("DATA").keyColumnName("ID").keyColumnType("VARCHAR2").keyColumnMaxLength(255).keyColumnAssignmentMethod("EMBEDDED_OID").versionColumnName("VERSION").versionColumnMethod("UUID").lastModifiedColumnName("LAST_MODIFIED").creationTimeColumnName("CREATED_ON").schemaName(targetUser).tableName("COMPANY").build();

Currently the following exception is seen ...

OracleException occurred : Invalid argument value "EMBEDDED_OID".

@mshannongit
Copy link
Author

createCollection() - OracleException occurred : Invalid argument value "EMBEDDED_OID".
oracle.soda.OracleException: Invalid argument value "EMBEDDED_OID".
at oracle.soda.rdbms.impl.SODAUtils.makeException(SODAUtils.java:53)
at oracle.soda.rdbms.impl.CollectionDescriptor$Builder.keyColumnAssignmentMethod(CollectionDescriptor.java:1553)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:918)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:1070)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.callCreatePLSQL(OracleDatabaseImpl.java:1669)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:703)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:638)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.createCollection(OracleDatabaseImpl.java:933)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.access$200(OracleDatabaseImpl.java:70)
at oracle.soda.rdbms.impl.OracleDatabaseImpl$OracleDatabaseAdministrationImpl.createCollection(OracleDatabaseImpl.java:2304)

@mshannongit
Copy link
Author

Looks like even PL/SQL doesn't work .. and the only hope is just using ORDS http request directly ...

createCollection() - SQLException occurred : ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

java.sql.SQLException: ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:628)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:562)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1207)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:727)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:291)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:151)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1192)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1747)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1432)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3743)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:4012)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3987)
at oracle.jdbc.driver.OracleCallableStatement.executeUpdate(OracleCallableStatement.java:4302)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:996)
at XXX.createCollection(BackupSchemaTool.java:225)

Caused by: Error : 40842, Position : 0, Sql = DECLARE
coll SODA_COLLECTION_T;
BEGIN
coll := DBMS_SODA.CREATE_COLLECTION(:1 , :2 );
END;, OriginalSql = DECLARE
coll SODA_COLLECTION_T;
BEGIN
coll := DBMS_SODA.CREATE_COLLECTION(?, ?);
END;, Error Msg = ORA-40842: unsupported value EMBEDDED_OID in the metadata for the field assignmentMethod
ORA-06512: at "SYS.DBMS_SODA", line 4
ORA-06512: at line 4

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:632)
... 16 more





        // create a mongo compatible collection

        String collectionName = "COMPANY";

        String metadataString = "{\"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\"}}";


        // workaround by calling plsql soda API directly to create_collection
        // triple quotes (introduced in JDK13) provides multi line text block
        final CallableStatement cstmt = conn.prepareCall("""
                DECLARE
                   coll SODA_COLLECTION_T;
                BEGIN
                   coll := DBMS_SODA.CREATE_COLLECTION(?, ?);
                END;""");
        cstmt.setString(1, collectionName);
        cstmt.setString(2, metadataString);
        cstmt.executeUpdate();

@morgiyan
Copy link
Member

morgiyan commented Apr 11, 2022

Right, currently we only support EMBEDDED_OID thru Mongo API (as documented here).

But we're working on adding it in the SODA drivers. For SODA Java, it's currently planned for the next release. May I ask if there's some urgency on this for you, so that we prioritize accordingly? Please feel free to email us directly with details if that's easier. My email is: maxim dot orgiyan at oracle dot com (remove the spaces, change "at" to "@", and change "dot" to ".").

Thank you!

@mshannongit
Copy link
Author

Using a SODA REST client, I can create the mongo compatible collection.

That collection though cannot be read using

        OracleRDBMSClient cl = new OracleRDBMSClient();
        OracleDatabase db = cl.getDatabase(conn);
        OracleCollection col = db.openCollection(collectionName);

It will give the error ...

oracle.soda.OracleException: Invalid argument value "EMBEDDED_OID".
at oracle.soda.rdbms.impl.SODAUtils.makeException(SODAUtils.java:53)
at oracle.soda.rdbms.impl.CollectionDescriptor$Builder.keyColumnAssignmentMethod(CollectionDescriptor.java:1553)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:918)
at oracle.soda.rdbms.impl.CollectionDescriptor.jsonToBuilder(CollectionDescriptor.java:1070)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.loadCollection(OracleDatabaseImpl.java:1790)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.openCollection(OracleDatabaseImpl.java:771)
at oracle.soda.rdbms.impl.OracleDatabaseImpl.openCollection(OracleDatabaseImpl.java:730)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants