Joe Lennon

(This article was published on IBM developerWorks in October 2010)

The concept of Ajax has allowed web applications to evolve from being slow, unresponsive, and counter-intuitive to behaving more like desktop applications, delivering instant feedback, removing the need for page refreshes, and facilitating a much better user experience all around. The problem with Ajax is that it adds an additional layer of complexity to application development. In an event-driven application, you might perform an action when a user clicks on a button. In traditional application development, you can put all of your logic for what this action should do right in the event handler, but with Ajax it’s not that easy. You probably do some client-side validation, and if that is passed, you prepare your data and create a new Ajax request. You then define functions to handle a successful response and a failure. Then you must create the server-side code that will handle the Ajax requests and return a suitable response. This will probably need to be in a certain data format, such as JavaScript Object Notation (JSON) or XML, and you must provide it in such a way that the client side is expecting to receive it.

In short, Ajax development requires that a web application developer maintain a set of code both on the client side and on the server side, and there can be huge differences in how both work, making it difficult to debug and maintain. It would be great if developers had a way of calling server-side actions on the client-side, minimizing their exposure to all of the Ajax request and response handling. That’s where Ext.Direct comes in.

At first, Ext.Direct may seem like an overzealous solution to a small problem, but by investing your time and effort in it at the start, you’ll start to reap dividends later on. The initial hurdle of getting the building blocks of Ext.Direct working may seem complicated, but it typically only needs to be done once, and after that the changes needed to add new classes and methods are very straightforward. In this article, you learn how to get started and put the building blocks in place, and how to actually use Ext.Direct to call remote PHP methods from JavaScript. You then learn some of the more advanced features that Ext.Direct has to offer, and how to take full advantage of them in your own applications.

This article is available on IBM developerWorks in English and Japanese.

(This article was published on IBM developerWorks in September 2010)

In the early days of the web, maximizing the performance of a web page usually meant avoiding the use of unnecessary HTML markup, keeping the amount of JavaScript code to a minimum, and heavily reducing the file size of any images so that the typical surfer didn’t have to go away and make a cup of coffee while your page loaded.

Advancements in various aspects of the web have meant that we are now faced with a whole new set of performance considerations. Despite the fact that DSL and broadband have made high-speed Internet access far more accessible to many people, our expectations of load time and responsiveness have also evolved to a state where we look for instant results when we perform an action on a page. The emergence of Asynchronous JavaScript and XML (Ajax) allowed developers to deliver a desktop-like experience in web applications, no longer requiring entire pages to load before responding to an event. The advantages of this are obvious, but it also led to the average web user expecting this type of responsiveness from all web applications. With the recent rise of the mobile web there is a new challenge of meeting a modern web user’s expectations, and all of this on a target device with a smaller screen, less power, and slower connectivity.

The focus of this article is on informing you of the considerations you should take to maximize the performance of your JavaScript and Ajax web applications. This article offers a set of guidelines on how to best approach any new code you are writing, whether it be in a new application or an existing one. You will also learn about the various tools and techniques that are out there for measuring the performance of your application. Finally, you will learn about various methods of improving performance that do not require you to change your existing code.

This article is available on IBM developerWorks in English and Japanese.

In case you haven’t heard by now, CouchDB 1.0 was released earlier this month. One of the questions I’ve been asked most about CouchDB is how can one go about installing it on Windows. Up until recently, there were a few hacked installers available, each of which would install CouchDB and its dependencies, but these far from perfect, with most of CouchDB’s test suite failing when run under this setup. Thankfully, there is now an official Windows binary available which will have you up and running with CouchDB in no time.

Step 1: Grab the binary from http://www.couch.io/get
Step 2: Unzip to your hard drive (I unzipped to C:\ and renamed the extracted folder to couchdb).
Step 3: Go into the bin directory and run couchdb.bat. This will launch the Erlang command line and run CouchDB. You should see a DOS window with the message “CouchDB 1.0.0 – prepare to relax…”.
Step 4: Open your browser and point it to http://127.0.0.1:5984/_utils/ to launch Futon
Step 5: From the navigation bar on the right hand side, click on “Test Suite” and at the top of the test suite’s page, click the “Run All” button to start the tests. Leave your browser do its thing (it’ll lock up while it’s performing the tests) and all going well, each of 66 tests should return with a success message.
Step 6: Click on “Overview” at the top right of the browser and start creating CouchDB databases!

Enjoy.

Tags are a useful means of associating keyword metadata with a data structure such as an image, a video, audio or a piece of text. There are many ways of storing tags in a database – for example one could store a string of tags separated by a space, comma or semi-colon. Alternatively, one could store tags in a database table and then create another table to hold the list of tags for a given entity. My preferred method of storing tags, in an Oracle database at least, is to use the concept of nested tables.

Nested tables are a type of PL/SQL collection which may be compared to arrays or objects in other programming languages. One of the advantages of using nested tables over other PL/SQL collection types is that they don’t have a fixed size, and they can be stored in a database table (not just in a program unit, like regular PL/SQL tables or associative arrays). The best way to show how nested tables work is to use an example, so let’s dive right in and set up our database for the tags example.

The first thing we want to do is create the nested table type in our database. I’m going to keep it nice and simple and name my type “tags”:

CREATE TYPE tags IS TABLE OF VARCHAR2(50);

If you are familiar with PL/SQL tables, the above should look very similar to the syntax for creating a PL/SQL table, but without the typical “index by binary_integer” bit stuck at the end. With our type created, we can now go ahead and use this in a database table. I’m going to create a table named “post”, which one might use to store blog posts. This table will have three columns – post_id, post_title and post_tags.

CREATE TABLE post (
    post_id            NUMBER(11),
    post_title         VARCHAR2(50),
    post_tags          tags
) NESTED TABLE post_tags STORE AS tags_table;

Great – you now have a database table with a column of type “tags” which is itself a table. The next thing we want to do is insert some records into our new table.

INSERT INTO post(post_id, post_title, post_tags)
VALUES(1, 'First Post', tags('oracle', 'tags', 'example'));

INSERT INTO post(post_id, post_title, post_tags)
VALUES(2, 'Second Post', tags('oracle', 'tags'));

INSERT INTO post(post_id, post_title, post_tags)
VALUES(3, 'Third Post', tags('oracle'));

COMMIT;

As you can see from the above INSERT statements, inserting tags into our nested table is very straightforward, we simply tell Oracle that we are using the type “tags” and pass the VARCHAR2 values we want to store to it. If you try to insert a tag with over 50 characters you will get an error as we defined the tags table as a table of VARCHAR2(50) fields. So what does our new table look like when we go to retrieve data from it? Let’s take a look.

SELECT * FROM post;

You should see output like the following

POST_ID      POST_TITLE                                                  POST_TAGS
------------ ----------------------------------------------------------- ---------
1            First Post                                                  <Object>
2            Second Post                                                 <Object>
3            Third Post                                                  <Object>

Hardly ideal is it? Fortunately, we can use the TABLE function to easily get back a list of tags for a given post quite easily. Try the following SQL statement:

SELECT column_value
FROM TABLE(
    SELECT post_tags FROM post WHERE post_id = 1
);

This outputs the following:

COLUMN_VALUE
--------------------------------------------------
oracle
tags
example

Try the statement again, but this time pass in the post_id value for another row. This is useful, as it gives us a means of getting the tag data out of the database, but what if we want to get back all the tags when retrieving the other columns in the table? This requires a bit more work, but it’s not exactly complex:

SELECT p.post_id, p.post_title, t.column_value
FROM post p, TABLE(
    SELECT post_tags FROM post WHERE post_id = p.post_id
) t;

The above statement should produce the following:

POST_ID      POST_TITLE                                              COLUMN_VALUE
------------ ------------------------------------------------------- ---------------
1            First Post                                              oracle
1            First Post                                              tags
1            First Post                                              example
2            Second Post                                             oracle
2            Second Post                                             tags
3            Third Post                                              oracle

A step in the right direction, but the post_id and post_title are duplicated for each tag for that record. Surely there’s a way of outputting all the tags on a single row, joining them together with a delimiting character such as a comma? There are a number of ways of doing this, from using a stored function to using 11g’s WITHIN GROUP feature. The method with the least code that works on 9i and above is to use the XMLAGG function to produce the concatenated result. The SQL to achieve the desired result in this manner is as follows:

SELECT p.post_id, p.post_title,
    RTRIM(XMLAGG(XMLELEMENT(e, t.column_value || ',')).EXTRACT('//text()'), ',') tags
FROM post p, TABLE(
    SELECT post_tags FROM post WHERE post_id = p.post_id
) t
GROUP BY p.post_id, p.post_title;

This produces the following result:

POST_ID      POST_TITLE                               TAGS
------------ ---------------------------------------- -----------------------
1            First Post                               oracle,tags,example
2            Second Post                              oracle,tags
3            Third Post                               oracle

So far we haven’t seen any particular reason why one would necessarily use a nested table to store tags over any other method. For me, the primary advantage is that it makes it very simple to perform group functions on the tags – making it ridiculously easy to generate tag clouds based on how many times a tag is used. In the above example, we can easily see that the tag “oracle” is used 3 times, “tags” is used twice and “example” only once. But in a scenario where you have many posts and many, many tags, it is impossible to do this counting in your head. Let’s take a look at just how easy it is to do this counting with our nested table.

SELECT t.column_value, COUNT(*)
FROM post p, TABLE(
    SELECT post_tags FROM post WHERE post_id = p.post_id
) t
GROUP BY t.column_value;

And the output:

COLUMN_VALUE                                         COUNT(*)
-------------------------------------------------- ----------
tags                                                        2
example                                                     1
oracle                                                      3

Want to order the results? Simply add an ORDER BY clause and Bob’s your uncle.

SELECT t.column_value, COUNT(*)
FROM post p, TABLE(
    SELECT post_tags FROM post WHERE post_id = p.post_id
) t
GROUP BY t.column_value
ORDER BY 2 DESC;

The result:

COLUMN_VALUE                                         COUNT(*)
-------------------------------------------------- ----------
oracle                                                      3
tags                                                        2
example                                                     1

For me, this is the main reason I store tags in a nested table – but there are other advantages, too. What if you are editing a post and you want to change one of the tags? Nested tables make this simple:

UPDATE TABLE(SELECT post_tags FROM post WHERE post_id = 1)
SET column_value = 'sample'
WHERE column_value = 'example'

Similarly, if you wanted to delete a tag from a row, this is also very straightforward:

DELETE FROM TABLE(
    SELECT post_tags FROM post WHERE post_id = 1
)
WHERE column_value = 'tags';

Nested tables are a powerful PL/SQL collection type, and you can do even more powerful things with them if you start using PL/SQL procedures and functions. But the beauty about nested tables for me is how powerful and flexible they are using nothing but raw SQL statements. For further information about nested tables and other PL/SQL collection types, see the following resources:

• http://www.developer.com/db/article.php/10920_3379271_1/Oracle-Programming-with-PLSQL-Collections.htm
• http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm
• http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-Nested-Tables/
• http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/ch19_01.htm
• http://sql-plsql.blogspot.com/2007/05/oracle-plsql-nested-tables.html
• http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/05_colls.htm
• http://www.smart-soft.co.uk/Oracle/oracle-plsql-tutorial-part-9.htm

In this post you will learn how to get a sandboxed PL/SQL web development environment up and running on a Windows machine. You will use the VMware Player virtualization system to run an Ubuntu 9.10 server image, on which you will install Oracle XE. You will then configure a DAD using DBMS_EPG which basically acts in the same way as mod_plsql does in Oracle HTTP Server. This will allow you to create Web applications using PL/SQL. Finally, you will learn how to work with virtual directories so you can store things like images, external stylesheets and external JavaScript files.

The first step you need to follow is to download and install the free VMware Player application on your system. This can be downloaded from https://www.vmware.com/tryvmware/?p=player&lp=1. Once this has downloaded, follow the simple instructions to install VMware Player. This application allows you to run other operating systems that are packaged in virtual machine images. Head over to http://www.thoughtpolice.co.uk/vmware/#ubuntu9.10 and download the appropriate image for Ubuntu 9.10 Karmic Koala. When this has finished downloading, extract the files to a convenient location on your file system, and find the file ubuntu-server-9.10-i386.vmx, and double-click it to start the virtual machine.

At this point, VMware Player should open and your Ubuntu VM will launch and boot up. After a few moments, you should see a prompt like the one shown in Figure 1:

Figure 1 - Ubuntu Login Prompt

To login, enter the login notroot and the password thoughtpolice. You will now be logged in and should see the Ubuntu shell prompt.

By default, the VM image you are using has 905208 bytes of swap space allocated. Unfortunately, this will cause the Oracle XE installer to fail, as its minimum is 1024MB. To prevent this from happening, you first need to assign some more swap space. Use the following commands to bump up the swap space to 1058800 bytes. If you are asked for your [sudo] password for notroot (you will be), enter thoughtpolice.

$ sudo dd if=/dev/zero of=/tmp/swap bs=1M count=150
$ sudo mkswap /tmp/swap
$ sudo swapon /tmp/swap
$ free

If the commands worked correctly, the output from the free command should look similar to the results shown in Figure 2 below:

Figure 2 - Configuring swap space

With the swap space issue resolved, you can now move on to the steps required to download, install and configure Oracle XE. First, you’ll need to add the Oracle repository to your apt sources list. Issue the following command to open the list in the nano text editor:

$ sudo nano /etc/apt/sources.list

Navigate to the end of this file (use Ctrl+V to scroll down page by page) and at the bottom add the following line:

deb http://oss.oracle.com/debian unstable main non-free

Save the file by pressing Ctrl+O, and then exit using Ctrl+X.

Now you can go ahead and download Oracle XE using the following commands:

$ wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -
$ sudo apt-get update
$ sudo apt-get install oracle-xe

This process may take some time (XE is a 221MB package) and you may need to answer Y when asked if you wish to continue.

It’s worth pointing out that when I ran the above commands, I found that the download would sometimes freeze at a certain point. If this happens to you, press Ctrl+C to force exit the process and then re-issue the command to pick up from where you left off (you can press the up arrow key to quickly bring up the last command you entered).

When the process has finished, Oracle XE is now installed. Before you can use it, however, you will need to configure it. Issue the following command to start the Oracle Database configuration tool:

$ sudo /etc/init.d/oracle-xe configure

When asked for a port number for Application Express, accept the default option of 8080 by pressing Enter. Do the same when offered port 1521 for the database listener. Next, enter a system password, and confirm it when requested. Finally, accept the default setting of y when asked if you want XE to be started on boot. Oracle will now configure the Net Listener and the Database – this may take a few minutes. When it is done you should see a message like the one shown in Figure 3.

Figure 3 - Oracle install complete

The next step you need to follow is to set up the ORACLE_HOME and PATH variables on your system, which will make it easier to run the SQL*Plus client. Create your user .bash_profile file by issuing the following commands:

$ cd ~
$ nano .bash_profile

In the nano editor, insert the following two lines:

export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
export PATH=${PATH}:${ORACLE_HOME}/bin

Press Ctrl+O to save and then Ctrl+X to quit nano. Now, log out of Ubuntu using the following command:

$ logout

You’ll be returned to the login prompt. Login once again using the username notroot and password thoughtpolice. Now, issue the following command to start the SQL*Plus client as the SYSTEM user:

$ sqlplus system@xe

Enter the password you chose when configuring Oracle when requested at the prompt. You should then see a message informing you that you are connected to Oracle. Now that you are logged in to the database, it is a good time to allow remote access to the Oracle XE web server, which runs Apex and which we will be using to run our PL/SQL web applications. To do this, issue the following command:

SQL> exec dbms_xdb.setlistenerlocalaccess(false);

You should see the message “The PL/SQL procedure successfully completed.” before being returned to the SQL prompt. Next, exit SQL*Plus by entering the following command:

SQL> exit

You will now be back at the Ubuntu shell prompt. The next thing you’ll need to do  is determine the IP address of your virtual machine. To do this, use the command:

$ ifconfig

You should see output similar to the one shown below. You need to look for the entry for eth0, specifically for the value next to inet addr: – in my case it is 192.168.133.131, as shown in the screenshot in Figure 4.

Figure 4 - VM IP address

Now, back in Windows, fire up your favourite Web browser and enter the following address into the Address box, replacing my IP address with the one for your virtual machine:

http://192.168.133.131:8080/apex

You should see a screen like the one shown in Figure 5.

Figure 5 - Oracle Application Express Home Page

This interface is actually very useful, so let’s login and create a new database user, which you will later use as the schema for your PL/SQL web application. Enter the username SYSTEM and the password you entered during the Oracle configuration. From the next screen, click the arrow next to the Administration icon, and from the Database Users menu, select “Create User”. This will bring you to a form where you can enter the details for the new database user. Enter the username embosa and whatever password you like (for the sake of simplicity it would be wise to use the same password as you chose for the SYSTEM user – of course you would never do this on a production system!). Make sure that the roles CONNECT, RESOURCE and DBA are all checked, and press the “Create” button at the top right hand side of the form to create the user.

Next, hop back to your Ubuntu VM and enter the following command to login to Oracle using your new user:

$ sqlplus embosa@xe

Enter the password when asked, and hey presto, you’re logged in as the user embosa! Now, let’s go about creating a DAD so that we can publish HTML using PL/SQL. The following block of code will create, authorize and configure a DAD named embosa which will be accessible at the URL http://192.168.133.131:8080/embosa (again, replace the IP address with your own VM’s IP).

begin
dbms_epg.create_dad(dad_name=>'embosa', path=>'/embosa/*');
dbms_epg.authorize_dad(dad_name=>'embosa', user=>'EMBOSA');
dbms_epg.set_dad_attribute(dad_name=>'embosa', attr_name=>'default-page', attr_value=>'home');
dbms_epg.set_dad_attribute(dad_name=>'embosa', attr_name=>'database-username', attr_value=>'EMBOSA');
end;
/

This will create the DAD, next you need to create the “default page” by creating a procedure named home. To do so, use the following:

create or replace procedure home as
begin
htp.p('Hello, world!');
end home;
/

Now, hop back over to Windows and in your Web browser, navigate to the URL http://192.168.133.131:8080/embosa, replacing the IP address accordingly. You should see something like that shown in Figure 6.

Figure 6 - A basic PL/SQL Web Application

The final piece of configuration to do is to configure a virtual directory where you can store images, external styles and scripts and so on. Back in your Ubuntu VM, you should still be logged into SQL*Plus. In here, run the following command to open port 2100 for FTP connections:

SQL> exec dbms_xdb.setftpport('2100');

Now, enter exit to leave SQL*Plus, and create a test text file by issuing the following command:

$ echo testing! > test.txt

Next enter the command ftp to open the FTP client. At the ftp prompt, issue the following commands (you will be prompted for your username and password, these are your database username and password, so if you followed the instructions above your username will be embosa):

ftp> open localhost 2100
ftp> ls
ftp> mkdir images
ftp> cd images
ftp> send test.txt

Now, go back to your web browser, and this time enter the URL http://192.168.133.131:8080/images/test.txt (again, change the IP as required) -  you should see the message “testing!”. That’s it, your virtual directory is now created and you can use your favourite FTP client to upload images and other files to this directory. To connect to this folder from Windows, just enter the Ubuntu VM’s IP as the hostname, be sure to enter 2100 as the port number, and use the database username and password to login.

You should now have a usable alternative to mod_plsql up and running, which will allow you to create PL/SQL web applications on Oracle XE alone. I hope to post some more tutorials in the near future that will capitalise on this setup, showing you how to create useful PL/SQL Web applications. If you have any questions, comments or suggestions, feel free to leave a comment on this post.