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:

Thursday, October 15, 2015

SQLCL - Blob loading ALL the files


The github repo for all the things the DB Tools team does will be posted here https://github.com/oracle/Oracle_DB_Tools This will include sqlcl , sqldev, modeler, ords , and who knows what other things we have up our sleeves to post.  This repo is going to be made up of  examples and getting started things like a new extention for sqldev, custom transformation for modeler, ords integration with handsontable, or sqlcl scripting.


Now onto more BLOB loading.

I'm slowly building out a helper.js which is now posted and I'll continue to update it on github here: https://github.com/oracle/Oracle_DB_Tools/tree/master/sqlcl/lib

There's a new function in there to exec host commands and capture the stdout, stderr, and return code of the command.  Now coupling that with the BLOB loading and you can load an entire directory of files into a table with a small amount of javascript.




New helper function

/*
*  Runs the passed in command and returns an Object with
*   .rc      - the return code
*   .stdout  - STDOUT
*   .stderr  - STDERR
*
*/
helpers.exec=function(cmd){
    var RunTime  = Java.type("java.lang.Runtime");
    var Scanner  = Java.type("java.util.Scanner");
    var p = RunTime.getRuntime().exec(cmd);

    var ret={};
        s = new Scanner(p.getInputStream()).useDelimiter("\\A");
        ret.stdout = s.hasNext() ? s.next().trim() : "";
        s = new Scanner(p.getErrorStream()).useDelimiter("\\A");
        ret.stderr = s.hasNext() ? s.next().trim() : "";

        p.waitFor();
        ret.rc = p.exitValue();
      p.destroy();
    return ret;
}


Putting it together with the BLOB loading example

load('https://raw.githubusercontent.com/oracle/Oracle_DB_Tools/master/sqlcl/lib/helpers.js');

/* Clean out the table just to prove it's not a baked demo */
sqlcl.setStmt("truncate table k");
sqlcl.run();

/* File name */
var files= helpers.exec('find . -maxdepth 1 -type f ').stdout.split('\n');

/* bind map for reuse */
var binds = helpers.getBindMap();

for(f in files ) {
   //  ctx.write("Loading : " + files[f] + "\n");
  /* load the blob */
  blob = helpers.getBlobFromFile(files[f]);

  /* assign the binds */
  binds.put("path",files[f]);
  binds.put("b",blob);

 /* Just do it */
  var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);
}

/* print the results to see if it worked */
sqlcl.setStmt("select path,dbms_lob.getlength(blob_content) from k order by when desc;");
sqlcl.run();



Removing the truncate and select at the end, loading the entire directory of client side files is 7 lines of javascript.



Posted by at
Labels:

Wednesday, October 14, 2015

AskTom's print_table - SQLCL version

There was an idea logged over on community.oracle.com to add AskTom style print_table to sqlcl.

Here's how easy it is now that there's scripting in sqlcl.  This is a simple js file that loops over the rows and columns and prints them.  When using the executeReturnListofList the first row is the name of the columns for reference.  With that data there it's quite simple to customize any output.



/* rebuild the sql passed in since args is split on spaces */

var sql="";
for(var i=1;i<args.length;i++){
  sql = sql + " " + args[i];
}
ctx.write(sql + "\n\n");


var ret = util.executeReturnListofList(sql,null);

/* loop the rows */
for (var i = 0; i < ret.size(); i++) {
    ctx.write('>ROW \n'); 
    /*loop the cols */
    for( var ii=0;ii<ret[i].size();ii++) {
        ctx.write("\t" + ret[0][ii] + " : " + ret[i][ii] + "\n");
    } 
}

ctx.write('\n\n');

Now running it is very simple.  Script printtab select......



Posted by at
Labels:

SQLCL - Blob loading from a file

There is a ton of ways having new scripting in sqlcl can help.  One example is a simpler way to load a local file into a BLOB in a column.

Prior to this the only options I know of would be to write custom code or move the files to the server and use utl_file or xdb or some combination of things like this.

Enter SQLCL and javascript method #1.


Here's a simple script which loads a local file into a temp BLOB and uses that as a bind variable in the insert statement

/*
/*
*  Function to take in a filename and add or create it to a map
*  with bind variables
*/
function addBindToMap(map,bindName,fileName){
     /*  conn is the actual JDBC connection */
     var b = conn.createBlob();

     var out = b.setBinaryStream(1);

     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);

     /* slurp the file over to the blob */
     java.nio.file.Files.copy(path, out);
     out.flush();

     if ( map == null ) {
         /* java objects as binds needs a hashmap */
         var HashMap = Java.type("java.util.HashMap");
         map = new HashMap();
     }
     /* put the bind into the map */
     map.put("b",b);
 return map;
}


/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = addBindToMap(null,"b",file);

/* add more binds */
binds.put("path",file);

/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);

/* print the results */
sqlcl.setStmt("select path,dbms_lob.getlength(blob_content) from k order by when desc;");

sqlcl.run();



Running it is as expected.  One new row and showing the blob size.  The only difference here is that you can avoid typing s-c-r-i-p-t and save 2 letters with just scri.




Method #2 shared library

I'll be making a library intended to make some things easier such as file loading and creating the hashmap.  So taking out this blob loading as an example there first 2 things to put into such a library in helpers.js


/*
    This is a javascript library to make some of the common things
    in SQLCL simplier.
*/

var helpers = {} ;

/* for complex type a java hashmap is needed for binds */
helpers.getBindMap = function(){
   var HashMap = Java.type("java.util.HashMap");
   map = new HashMap();
   return map;
};

/* create a temp blob and load it from a local to sqlcl file */
helpers.getBlobFromFile=function (fileName){
     var b = conn.createBlob();
     var out = b.setBinaryStream(1);
     var path = java.nio.file.FileSystems.getDefault().getPath(fileName);
     java.nio.file.Files.copy(path, out);
     out.flush();
 return b;
};

Now the blob loading script can be reduced to something like this

load('helpers.js');

/* File name */
var file = "/Users/klrice/workspace/raptor_common/10_5.log";

/* load binds */
binds = helpers.getBindMap();

/* add more binds */
binds.put("path",file);

blob = helpers.getBlobFromFile(file);

binds.put("b",blob);


/* exec the insert and pass binds */
var ret = util.execute("insert into k(path,blob_content,when) values(:path , :b, sysdate)",binds);



I'll be cleaning up this helpers.js and hosting it soon with frequent additions to new functions.
Posted by at
Labels:

Tuesday, October 13, 2015

SQLcl - Oct 13th Edition

Another build of SQLCL is out.  This one has lots of great things in it.  There's fix in there to work with Cygwin for the unlucky people that are still on windows.

Now the new stuff:  SCRIPT

This new command can radically change things you can do in the tool.  The script command runs a script that can be any language that is JSR-223 enabled.  The best list I can find is here: https://en.wikipedia.org/wiki/List_of_JVM_languages  So you want to program sql scripting in groovy,perl,javascript,pascal,....  All doable now.

Javascript is the only one built into Java by default so most example will be focused on that although I do have some Jython ones also to post.

To run a script simple type: script <script name>  If there is no file extension passed along it assume a .js  The language is loaded based on this extension.  If the language can't be loaded you get a message saying as much "Could not load Scripting Engine for :py"  More on how to add the other languages later.

Here's a simple script:

ctx.write("Hold my beer, I'm going to try something\n\n");

ctx.write('hi from javascript\n');

ctx.write('This is a count of my objects');
sqlcl.setStmt('select count(1) from user_objects; ');
sqlcl.run();

var ps1= 'set sqlprompt "@|blue  _USER|@@@|green _CONNECT_IDENTIFIER|@@|blue 🍼 >|@"'

ctx.write('Now javascript will change the sqlprompt\n');
sqlcl.setStmt(ps1);
sqlcl.run();

sqlcl.setStmt('@numbers.sql');

sqlcl.run();

This example while simple show you can run commands from inside the script that affects sqlcl itself like changing the prompt.  The simple example is that I connected to production so a baby bottle it swapped out for beer




There's a few globals available for use.  There'll be more formal doc to follow

    sqlcl
        Methods:  
             setStmt(<String of stuff to run>)
                 This can be a single statement, an entire script of stuff, or any sqlcl command such as "@numbers.sql"
             run()
                 Runs whatever is in the stmts

   ctx ( this has tons of methods but this is the single most important )
       Methods:
             write(<String>)

   util ( again tons of methods ) 
       Methods:
              execute(<string>,binds)
                   executes whatever is passed in with a boolean return for success/failure
              executeReturnOneCol(<string>,binds)
                   executes and returns the first row , first column
              executeReturnListofList(<string>,binds)
                   executes and returns an array(rows) of arrays(row).  
              executeReturnList(<string>,binds)
                   execute and returns and array ( rows ) of objects ( row )
              

Here is an example using some of these methods with the expectable output shown below.


/* Look up a single value to use in a bind later */
var user = util.executeReturnOneCol('select user from dual');

var binds = {};
binds.name = 'EMP';
binds.who = user;

ctx.write('Using Binds:'+ binds + '\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF LIST                ***********\n');
ctx.write('***************************************************************\n');


var ret = util.executeReturnListofList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (var i = 0; i < ret.length; i++) {
    ctx.write( ret[i][1]  + "\t" + ret[i][0] + "\n");
}

ctx.write('\n\n');

ctx.write('***************************************************************\n');
ctx.write('*****    SIMPLE LOOP OF LIST OF NAMES WITH BINDS     **********\n');
ctx.write('***************************************************************\n');


ret = util.executeReturnList('select object_name,object_type from  all_objects where object_name = :name and owner = :who ',binds);

for (i = 0; i < ret.length; i++) {
    ctx.write( ret[i].OBJECT_TYPE  + "\t" + ret[i].OBJECT_NAME+ "\n");
}





Posted by at
Labels:
Subscribe to: Posts (Atom)