DigitalFriend Blog

October 2007

Releasing SQL+PaWS: SQL and People as Web Services

Mon, 22 Oct 2007 22:45:36 +1000

By: gosh'at'DigitalFriend.org (Steve Goschnick )

The Motivation for IT

I'm going to let you in on a little secret or two about software opportunities if you're not already aware of them: when a technology is really hot, really at a point in time when it could change things substantially in the way that people use computers, and/or develop software and services: the Big companies throw free versions at you as soon as possible. I'll give you an example:

  1. When XML came out, both IBM and Microsoft quickly made free XML parsers available to all-comers.
  2. Many more examples out there, once you know this principle ...

What they are doing is psychologically removing the value for anyone else who is/is-thinking-of writing such tools. E.g. A good software developer worth their salt in 1998 when those two XML parsers became freely available, was supposed to then think: "Well, an XML parser would be a powerful tool to write right now, but hey, now that the big guys have done it and are handing it out for free, it has no value, so I won't bother wasting my valuable time!" - thus leaving the large established companies in their onwardly powerful and commanding positions in that sphere of the marketplace, no matter how the standard/tool/language evolves thereafter.

The other thing big companies do regarding innovation is that they deliver it in a 'drip-feed' timeframe - rather than releasing innovation in great splashes. I.e. They like to draw it out, extracting as much money as possible from all and sundry, while at the same time preventing it from also swamping the market niches of their other products that will be adversely affected.

Web Services standards and tools fits not one, but both of those corporate-release profiles. Web Services has such a promising marketplace, and it has had it for 5+ years now! It has been morphed or diverted into SOA (Service Oriented Architecture) and SaaS (Software as a Service).

SOAP and WSDL web services have often failed to allow us to cross boundaries between work, home, school and play, partly because of the complexity of setting them up, and partly because of snail-paced evolving and burgeoning standards involved (e.g. SOAP 1.1, WSDL 1.1, WSDL 2.0, WS-Adressing, WS-Security, WS-ReliableMessaging, WS-Policy, etc.) – i.e. they still represent a moving and technically expanding target, not to mention the ongoing incompatibility problems between tool vendors versions.

Enter SQL+PaWS: SQL and People as Web Services

SQL+PaWS came about because of frustration with all of the above. SQL+PaWS is an open source, simple technical solution to web services that is also, direct, powerful, based on the two most widely available standardised languages in the world (SQL and HTML), and is free of red-tape and crap. Figure 1 below shows the results from an SQL+PaWS web service that returns information on standard country codes and country currency codes. In this case, the web service user wanted a service that will return all countries in the world, where the US dollar is the main currency used. The information is returned in the form of a regular 2D HTML table, with the data's metadata up at the top of the table.

Results table from an SQL+PaWS service

Figure 1 : HTML table produced with data plus, data item names, types, sizes and other metadata

The user may simply view this table in their favourite web browser, or they may consume it via an application program including a mashup (a program that combines multiple distributed data sources - such as this SQL+PaWS web service - in a synergy of new usefulness). The table is located via a relative tag placed in the HTML.

Figure 2 shows how the web service user accessed and customised the web service. They accessed a URL where the example service is hosted, inputting a single SQL Select command in an input form. That's it, all done!

Input Form of URL of an SQL+PaWS

Figure 2: The simple HTML input form with the SQL Select statement that produced the table in figure 1, via the example web services URL.

So, 'programming' the web service simply requires placing a straight-forward SQL Select command in the single HTML input field. It uses an SQL DBMS (database management system) server-side.

The person who implemented the SQL+PaWS web service needed to supply only three things: the database name, and a server-side user-name and password combination, that the SQL engine recognised. That's it. Its the same for writing any other SQL+PaWS web service: database-name, user-name, password. Done!

Most existing WSDL web services are using an SQL-oriented DBMS server-side, be it: Oracle, DB2, MS SQL Server, MySQL, Sybase, etc. These conventional web services are going from data sets at the source end, through various XML (hierarchical-structure) files representing protocols and interchange standards (SOAP, WSDL, etc), and then presented once again as sets at the consumer end, in spreadsheets or lists or similar data sets and data vectors (variable/value pairs).

SQL+PaWS goes from data sets at the source end (relation DBMS), and goes directly to a data set at the consumer end (<table>), using two of the most widely used computer languages available - SQL and HTML. Often the adoption and application of web services is hindered by all of the encumbering hierarchically structured XML-specifications - see the September post regarding how this has come about.

Note 1: There is a version of the SQL+PaWS implementing code, that adds a user-name and a password to the input form in figure 2, to restrict web service use and abuse.
Note 2: We are not against the XML language - its just in the way some times. Indeed, we will be making available soon, a companion product under the SQL+PaWS banner, called SQL-2-XML-RPC web services - which use the same SQL command approach to calling and specifying a web service, but instead delivers it as an XML-RPC feed. And making available another companion product called SQL-2-RSS web services - which uses the same SQL command approach to calling and specifying a web service, but delivers the resulting <table> within an RSS feed item. Both of them are also Open Source software.

What about the 'People' Part of SQL+PaWS?

The ubiquitous web browser generally requires a human reader to retrieve information from the web. In contrast, Web services were developed to be a client-side programmatic interface (computer programs as the readers) to the world of servers out there. However, SQL+PaWS web services, as well as being used by such application programs, can also be viewed in a web browser by people, both at web service prototype time, or in general (see the overview in figure 3 below). In addition, it allows people to very simply author web services that are directly readable by applications and mashups (by producing HTML <table>s that conform to the SQL+PaWS convention of writing such a table. See the official link: SQL+PaWS Specification).

Overview of SQL and People as Web Services

Figure 3: Overview of SQL and People as Web Services (SQL+PaWS)

The world is becoming increasingly wired (and wirelessed?) with sensors, but, the world's best all-round sensor (and analyst) is still the Human Being. The use of SQL+PaWS is potentially very generic and includes a convention that allows people to post their own data in a particular HTML format - see Listing 1 below. I.e. Individuals who routinely collect data, can simply post it on the Internet as a Web Service to other people and/or organizations, without the need to use an SQL database at all. Figure 1 shows that either people and/or SQL-oriented databases can be the source of an SQL+PaWS web service, and either people and/or software clients (including the growing field of mashup applications) can also be the consumers of SQL+PaWS web services.

Listing 1: HTML code fragment with essential insertions
<a name="START-SQL+PaWS"></a>
<table>
<caption align="bottom">Date Created: Sep 28, 2007 1:27:12 +1000 </caption>
<thead valign="top">
<tr>
<th>CountryName</th>
<th>CountryCode</th>
<th>CurrencyCode</th>
<th>CurrencyName</th>
</tr>
<tr>
<th>VARCHAR(60)</th>
<th>CHAR(2)</th>
<th>CHAR(3)</th>
<th>VARCHAR(50)</th>
</tr> ...
</thead>
<tbody>
<tr>
<td>AMERICAN SAMOA</td>
<td>AS</td> ...
</tr> ...
</tbody>
</table>
<a name="END-SQL+PaWS"></a>

Links for further interest:

1. The SQL+PaWS Official Specification page: PaWS HomePage
2. Download of the JSP (Java Server Pages) version of SQL+PaWS source code, here: SQL+PaWSviaJSP.zip (350 KBytes)
3. Downloading the PHP version of SQL+PaWS here: 'coming soon'
4. A Directory of SQL+PaWS Web Services here: 'coming soon'
5. A brief Press Release in .pdf format, here: PressRelease2007-10-22
6. An example SQL+PaWS web service - Country Codes and Currency Codes. Give it a try (copy'n'paste is best):
1st e.g. In your Web Browser go to the URL: http://idealab.dis.unimelb.edu.au:8080/sqlPaWS/countryCurrencyCodesWS.jsp
Try entering the SQL command: SELECT DISTINCT CurrencyName FROM Currency WHERE CurrencyName LIKE '%Dollar%';
... that's a Web Service that returns the names of all world currencies with the word 'Dollar' in them.

2nd e.g. At the same URL: http://idealab.dis.unimelb.edu.au:8080/sqlPaWS/countryCurrencyCodesWS.jsp
Try entering the SQL command: SELECT CountryName, Country.CountryCode, CurrencyCode, CurrencyName FROM Country, Currency WHERE Country.CountryCode = Currency.CountryCode AND CurrencyCode = 'EUR';
... that's a Web Service that returns all countries that use the Euro as their primary currency, done!

Acknowledgments: SQL+PaWS is a by-product of teaching and research activity at the University of Melbourne (IDEA Lab, Dept. of Information Systems)

Home | Site Map | Privacy Policy | Contact Us | ©2007 Solid Software Pty Ltd