Integrate PostgreSQL (cont'd)
Now you need to set up the "mytestdb" database. Make sure that PostgreSQL is running and that your user account has superuser privileges (or at least privileges to create a database). Create the mytestdb database with the following command:
If you do not have sufficient privileges to create the database, have someone else create the database for you. You also have to create some sample tables and insert some sample data so that you can test the integration of Cocoon with PostgreSQL. To save on typing, the downloadable code provides create_table.sql and sample_data.sql. It also provides drop_table.sql, in the event you make a mistake or just want to start fresh again with the database tables. Issue the following commands to create the tables and populate them with data:
- psql -d mytestdb < /path/to/create_table.sql
- psql -d mytestdb < /path/to/sample_data.sql
At this point, you are ready to start up Tomcat again. Type the following to start Tomcat:
- cd $TOMCAT_HOME/bin
You need to create the Cocoon code to access the database. One of the nice things about Tomcat is that it supports live reloading of code. Cocoon also checks for updated code. This enables you to add new XSPs and incrementally develop your application. Find the myapp.tgz file in the downloadable code. Perform the following steps:
- cd $COCOON_HOME
- tar xzf /path/to/cocoon_myapp.tgz
This article assumes you have familiarity with basic Cocoon concepts such as sitemaps, XSPs, and pipelines. If you have no idea what those concepts are, the Cocoon site is a good resource: http://cocoon.apache.org/2.1/. The tar command you just executed reads the archive file and creates a myapp directory in $COCOON_HOME. It then unpacks a few files: index.xsp, person_table.xslt, sitemap.xmap, and xml2html.xslt. The meat of the application is in index.xsp and person_table.xslt, which you should look over. It is a fairly simple application, but it does provide you with evidence that Cocoon is communicating with the PostgreSQL database named mytestdb.
Figure 2. RDBMS Table Data Wrapped in XML Tags
Point your Web browser at http://$SERVER_HOSTNAME:8080/cocoon/myapp/index.xml. You should see something like Figure 2.
Figure 2 shows the data from the relational database tables, only it's wrapped in XML tags. You can verify this by looking through the data in the mytestdb database directly. Type "psql -d mytestdb" and then issue SQL commands such as "select * from person_tbl;" or "select * from address_tbl;". For another view on the same data, point your Web browser at http://$SERVER_HOSTNAME:8080/cocoon/myapp/index.html. You should see something like Figure 3.
Figure 3. Index.html HTML View of the RDBMS Table Data
The index.html HTML view simply goes through a different stylesheet than the index.xml XML view, which you can verify by examining sitemap.xmap and reviewing the pipelines.
Put PostgreSQL and Cocoon to Good Use
As you can see, accessing PostgreSQL (or anther relational database) from Cocoon is useful. Even in XML-driven application development, or perhaps I should say especially in XML-driven application development, data from a relational backend is important. XML is highly structured data. Relational databases also store highly structured data. It's a natural fit. Almost any significant XML-driven project is still going to involve a relational backend, and now that you know how to access PostgreSQL data from your Cocoon XSPs, you can develop rich data-driven applications with one of the leading open source relational databases.
Cocoon lets you access databases through XSPs and SQLTransformer, as well as in actions. For further details on writing code to access databases via these three methods, consult the following resources from the Cocoon Web site: