Tuesday, December 15, 2015

Carbonated SQLCL - aka SODA support

     Oracle Database 12.1.0.2 introduced JSON support.  This is supported in REST Data Services already and now in the latest SQLCL.  This means ORDS can serve up the access to client applications and developers can have a command line way to interact and test features/queries/... in a nut shell a shell for JSON document storage. To use this feature the database will be to have patch #20885778 applied.

KLRICE@orcl >soda

SODA
------

SODA allows schemaless application development using the JSON data model.
  SODA create  
       Create a new collection 

  SODA list 
       List all the collections 

  SODA get <collection_name> [-all | -f | -k | -klist] [{<key> | <k1> <k2> ... > | <qbe>}]
       List documents the collection 
       Optional arguments: 
      -all    list the keys of all docs in the collection 
      -k      list docs matching the specific  
      -klist  list docs matching the list of keys 
      -f      list docs matching the  

  SODA insert <collection_name> <json_str filename=""> 
       Insert a new document within a collection 

  SODA drop <collection_name> 
       Delete existing collection 

  SODA count <collection_name> [<qbe>] 
       Count # of docs inside collection. 
       Optional <qbe> returns # of matching docs

  SODA replace <collection_name> <oldkey> <new_ doc="" str=""> 
       Replace one doc for another 

  SODA remove <collection_name> [-k | -klist |-f] {<key> | <k1> <k2> ... | <qbe>} 
       Remove doc(s) from collection 
       Optional arguments: 
      -k     remove doc in collection matching the specific <key> 
      -klist remove doc in collection matching the list <key1> <key2> ... > 
      -f     remove doc in collection matching <qbe> 

KLRICE@orcl >

There's a lot in there for command so give them a try.  SODA is underpinned but tables with JSON data in them.  It's very easy to get going testing out the SODA features.  Here's a simple collection create, insert, and query.


KLRICE@orcl >soda create ODTUG
Successfully created collection: ODTUG

KLRICE@orcl >soda insert ODTUG {"content":"awesome"}
Json String inserted successfully.

KLRICE@orcl >soda insert ODTUG {"location":"chicago"}
Json String inserted successfully.

KLRICE@orcl >soda insert ODTUG {"best_of_week":"DB Symposium"}
Json String inserted successfully.

KLRICE@orcl >soda get ODTUG -f {"content":{"$startsWith":"a"}}

Key:      7A01B31EBD7C484C88F431B8871DD165
Content:  {"content":"awesome"}
-----------------------------------------
 1 row selected.


JavaScript and SODA

Here's a quick example of using the SODA libraries from javascript.  Also in here is a new feature of scripting.  The javascript can be inlined into a sql script.  Notice that it's soda.sql <<< .sql that has javascript in it, let that sink in for a minute with the possibilities.

KLRICE@orcl >@soda.sql

Caffeinated SODA 
Dropping the old
Created the old
Inserted Document
Searching...
CBFE3D919E814094954BB217A2F6916E
{ "name" : "Alex", "friends" : "50" }


KLRICE@orcl >!cat soda.sql

script
ctx.write('Caffeinated SODA \n');


var OracleRDBMSClient = Java.type("oracle.soda.rdbms.OracleRDBMSClient");
var OracleDataSource = Java.type('oracle.jdbc.pool.OracleDataSource');
var cl = new OracleRDBMSClient();
var db = cl.getDatabase(conn);

ctx.write("Dropping the old\n")
var foo = db.admin().createCollection("foo");
 foo.admin().drop();
ctx.write("Created the old\n")
    foo = db.admin().createCollection("foo"); 


var doc = db.createDocumentFromString("{ \"name\" : \"Alex\", \"friends\" : \"50\" }");
ctx.write("Inserted Document\n")
 foo.insertAndGet(doc);

conn.commit();

var f = db.createDocumentFromString(JSON.stringify( {"name" : { "$startsWith" : "A" }}));
ctx.write("Searching...\n")
var c = foo.find().filter(f).getCursor();


while (c.hasNext()) {
 cDoc = c.next();
 ctx.write(cDoc.getKey() + "\n");
 ctx.write(cDoc.getContentAsString());
 ctx.write("\n\n"); 
}

ctx.write("\n\n");
/

KLRICE@orcl>


Gory Details of the release

Someone asked about change logs for these SQLCL updates.  Here's a filtered version of the gory details



Posted by at
Labels: