Wednesday, July 31, 2013

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.

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 jsbin.com so it's nice and easy to share:http://jsbin.com/emadiv/5The 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

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 http://www.jqwidgets.com/ I'll write those up also.

Monday, July 29, 2013

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:

Friday, July 26, 2013

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 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.

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.

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

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.

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.

Tuesday, July 23, 2013

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 apex.oracle.com as the example for this.

If anyone is interested, the animated GIF is from doing this: https://gist.github.com/dergachev/4627207

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.