Wednesday, July 31, 2013

Using REST to search in JQuery

Building on yesterday's post,  here's adding a search to the same JQuery list: SQL Developer Exchange Requests

SQL Developer Exchange Requests

The REST Template is only different by adding in a bind variable and adjusting the select some:

The other thing I added into the JQuery list is the weight of the searched items.  Which can be seen in the blue bar here:

The javascript changes are also fairly small. I made a function named search which takes in what the text is to search on. Then few lines to bind the function and the listviewbeforefilter event together.

// attach to the before filter in the list
     $( "#results" ).on( "listviewbeforefilter", function ( e, data ) {
          // grab the value of the search field
          var $ul = $( this ),
  $input = $( data.input ),
  value = $input.val();
          // if blanked go back to the recent list
          if ( value == '' ) {
          } else {
            // debugging
              console.log("Loading Search:"+value);

 function search(search){
   console.log("searching for '" + search + "'");
    // out with the old

    // in with the new
      url: "" + search ,
      type: "GET",
      success: function (data) {
        if ( data.items.length == 0 ){ noResults();}
        ko.applyBindings(data, document.getElementById("searchPage"));

Tuesday, July 30, 2013

Combining RESTful, JQuery ,and KnockoutJS

Here's a very easy example of how to join RESTful services with JQuery and some data binding from KnockoutJS.  This is a page that shows the last 100 requested features in the SQL Developer Exchange.

The REST service is very simple as it just strips out a <p> that the editor injects and a little format on the date.

I did this all on so it's nice and easy to share:  The knockoutJS template is the key.  When JSON is returned from the APEX Listener it has a parent node named "items"  The template is bound to the list with this line:

           data-bind="template: { name: 'overview', foreach: items}"

Then for each child under items it will loop and apply the template.  The template is just html inside a script table with the type set to text/html

<!-- Knockout syntax for the template which is the list items -->  
<script id="overview" type="text/html">
<li data-role="list-divider"><!--/ko--></li>
<!-- bind in the link to drill down to the exchange-->
<li> <a data-bind="attr: { href: ''+ id }"  alt="">
<!--ko text: title --><!--/ko--></h2>
<span data-bind="text: description"></span>

Then the Javascript which loads the data and applies the template

var requests = "";

  // When the page init's go load it up.
$(document).on("pageinit", "#recentRequests", function() {
      url: requests ,
      type: "GET",
      success: function (data) {
        // go apply the template to what was retreived
        ko.applyBindings(data, document.getElementById("recentRequests"));

This barely touches the surface of what can be done with just getting the data out of the database via the APEX Listener.  For example,  the query in this REST is returning 100 rows but pagination is set to 25.  I'll get that hooked up and expand this demo later.  As I tinker with more widgets such as I'll write those up also.

Monday, July 29, 2013

Who's on first? Adding Who columns in the Modeler

   The SQL Developer Datamodeler has some great built in transformation.  Let's take a look at the Table Template one.

  I'm going to start with the plain ol' HR schema and we're going to add in the standard who columns to it.

Step 1 is to create a table named "table_template"  There's plenty of options since it's just a normal table in the model.  The two interesting checkboxes which are Engineer and Generate in DDL.  This will make it so this table is not generated when you ask for the DDL or Engineer into a Logical Model.

Step 2 is to add in the who columns we want to propagate to all tables in the model.

Step 3 is to do the transformation.  

Step 4 is to press the Apply button.

The final result is that all the tables in the model now have every column from the table_template.

The transformation itself is fairly straight forward and it's all Javascript:

 /* name of the table to template from */
var t_name = "table_template";
var p_name = "ctemplateID";

template = model.getTableSet().getByName(t_name);
    tcolumns = template.getElements();
    tables = model.getTableSet().toArray();
    /* walk all tables in the model */
    for (var t = 0; t<tables.length;t++){
       table = tables[t];
      // compare name ignoring the case
         for (var i = 0; i < tcolumns.length; i++) {
            column = tcolumns[i];
            col = table.getColumnByProperty(p_name,column.getObjectID());
             col = table.createColumn();
            //set property after copy otherwise it'll be cleared

Friday, July 26, 2013

No DB access? Use REST

The problem.

   There's an arbitrary sized image in a database which needed to be made square, sized into 4 sizes, and have a border to color match the picture ( like iTunes albums ) .  Also, do it in batches so as not to cause unneeded load on the database.  Then a minor detail of there's no direct sqlnet access to the db.

  Here's the current result.  The original picture was an now quite square at 490x604

The result is much more uniform

The list of work.

  Subsetting the list to be done was the simplest part.  A RESTful service defined which ties who has images to what to do produced this list.  This was my test on myself but it can obvious be opened up to do any subset or lower the pagination and process one page at a time.

The original image.

  There's not much to say here since I covered how to get an image out over REST here.

The processing.

  I read a lot of what people were doing to color match in various ways.  The actual code is in java and I can post it if anyone is interested.  However, here's what I basically did.

      Step 1,  Processes EVERY pixel and find the most common Hue ( HSV
      Step 2,  Rewalk the pixels and find every one of that Hue and get the most common Saturation and Brightness
      Step 3,  I found a Java Library named Scalr to do the resize
      Step 4,  Center the resized image and pad to make it square using the computed HSV as the padding color.

  Sounds simple but too me a while to figure the way to do this.  For this test image, it took ~300ms to process and make all the resized versions.  There's probably better/faster ways to do this but this seems to be working for me.
    For the image of me above the dominate color happened to be black which is why there are black bars.  However to prove it works, here's another example.  The color match is on the left and right and I put red lines around the seem on the right.

Putting it back into the Database.

  Now back to using REST.  Now there's 3 images that have been processed and need be updated into the database.  Most of what I've shown has been GET requests of the Apex Listener.  This will require a POST URI handler.  The URI template shows I'll post in the person email address and the size of the image.  This is so I know which column to place the updated image into.

The part not in the template anywhere is where the image will be.  The Apex Listener makes an automatic bind named :body.  This is the body of the file upload.  The other thing the listener does is print out anything in the htp.p buffer.  Which is why calling F on this post works.  I'm using htp.p in this block simply for debugging progress to see what's going where.

  p_id number;
  img BLOB;
  /* bring the LOB to a local variable */
  img := :body;

  /* get the id */
  select person_id into p_id
    from people p
    where email = :email;

  /* print the size for debugging */
  htp.p('Lob size:'|| dbms_lob.getlength(img));

  /* based on which size stuff the image into different columns */
  if ( :size = 80 ) then 

     htp.p('Updating 80');
     update  photos
        set scaled_photo = img
      where person_id = p_id;
  elsif ( :size = 180 ) then 

    htp.p('Updating 180');
     update  photos
        set thumb_photo = img
      where person_id = p_id;
  elsif ( :size = 436 ) then

    htp.p('Updating 436'); 
      update  photos
         set large_thumb_photo = img
       where person_id = p_id;
  end if;


exception when others then

Putting it all together.
   The actual java code that runs it all is not that interesting.  I'm using Jackson to process the JSON of the people's images to process.  There's a few libraries out there for JSON processing this one just seemed easy for me to pick up.
  The posting of the images back is using Apache's HttpClient.  Here's that code.

 HttpClient httpclient = new DefaultHttpClient();
    try {
      // Create the POST client
      HttpPost httppost = new HttpPost(""+ email+"&size="+size);
      // The image scaling is done locally to a file then posted
      InputStreamEntity reqEntity = new InputStreamEntity(new FileInputStream(file), -1);
      // set the POST to be binary content

      System.out.println("executing request " + httppost.getRequestLine());
      HttpResponse response = httpclient.execute(httppost);
      HttpEntity resEntity = response.getEntity();

      String line = null;
      // now print the text from the htp.p debug messages.      
      BufferedReader in = new BufferedReader(new InputStreamReader(resEntity.getContent()));
      while( ( line = in.readLine() ) != null ){
    } finally {
      // shut it down

Thursday, July 25, 2013

Nicer URLs for APEX, yet another option, Part 2

   Here's taking the nicer URLs one more step and I think I have 2 more that will follow next week. I have been doing with some testing with the Online Learning Library .

In case the screenshot is too small, the URL is

The URI Template is easy to follow and fairly portable.  I'm working on a generic way to do this in any app but here's the one we're using for this specific page.


Then the Apex Listener translates those things in  { }  into binds which can be used in this plsql block to run the F procedure and get a page out of Apex.

/* buffer to build up the value */
 p varchar2(32767);


  /* add in app,page, and session */

  /* move along nothing to see here */
  p:= p||'::::';

  /* add in item names */
  p:= p|| :item1;
  if ( :item2 is not null ) Then
    p:= p|| ','||:item2;
  end if;

 /* addin item values */
  p:= p|| :item1v;

  if ( :item2 is not null ) Then
    p:= p|| ','||:item2v;
  end if;


   I have to mention things could be rendered badly if anything is reading owa_util package to get assumptions from the URL.  One thing that I know didn't work for me was the use of apex_util.get_blob_file but that's easy to fix from yesterday's post.

Wednesday, July 24, 2013

Using RESTful to deliver Images for APEX

   Who doesn't want things to be faster.  Here's a very easy way to get images in the database out faster.  The normal way to get images embedded into and Application Express app is to use apex_util.get_blob.  It's tried and test and optimized for doing just that so you may assume it doesn't get faster to get images served out.  Here's a quick screenshot of loading a test page in chrome of 2 identical images and by identical, I mean the same BLOB from the same table with the same ID.

The shorter timeline is the RESTful delivery.  Mileage will of course vary by load and latency but with what I've tested REST delivery is always faster.

For 5 reloads this was the timings I got:

apex_util REST % Faster
259             137     89.05%
362             311     16.40%
250             129     93.80%
245             130     88.46%
269             126     113.49%

Now introducing RESTful type of Media Resource which makes this very easy to do.  Colm initially blogged about it a while back.  In a nutshell, it's a query that wants 2 columns the first being the content type , mime, and the second being a lob or anything to be delivered.  Colm showed how to deliver XML in his post.  This is how to serve up images.

In the URI Template, you'll notice {id} which is the ID of the image to be served and used as a bind to query.  The select is quite simple to follow.

The table for this example is quite simple but obviously could be anything as long as there's a mime and lob to deliver.

CREATE TABLE  myimages

Tuesday, July 23, 2013

Nicer URLs for APEX, yet another option

Nicer URL for Application Express is something that has had a few options for an is discussed a lot.  Here's yet another option using nothing but the RESTful services inside the APEX Listener.

First is to setup up the workspace prefix in the Administration

Now that's it's enabled back to the RESTful setup screens to make a new Module.  I left the URI prefix empty to make the URL have 1 less path but you could put in anything and that will just have to be in the path.  I'll show an example at the end.

Now to create the actual URI template that will answer for the URIs.  The key things here are the bind variables APP and PAGE.  In a later post, I'll build from here on passing parameters which will need a different template.

Now the actual 7 lines of code which could obviously be condensed.  This needs to be a GET of PL/SQL type with Secure turned off.  Then all that happens is the APEX Listener splits the URI's application and page up already for us to rejoin them into an actual apex URI.  That needs to be an outbound bind to the Location header.  Then there needs to be an accompanying status for 3xx to tell the browser to go follow it.  

The end result is what you'd expect.  There's a TON more that can be done with this same basic concept.  For example, use the apex views such as apex_applications to perform some validation before the redirect then handle the 404s or other statuses directly,  looking up page items and setting those from a more robust URI template,  or even adding in vanity domain support with RESTful mappings. 

Here's the SQL Developer Exchange which is on as the example for this.

If anyone is interested, the animated GIF is from doing this:

The steps are pretty simple once ffmpeg and gifscale are installed via Mac Ports.  The only change I made was to set the delay to 15 instead of 5 in the example.