OpenLink Community Blog

Microsoft's recent unveiling of the next generation of ADO.NET has pretty much crystalized a long running hunch that the era of standardized client/user level interfaces for "Object-Relational" technology is neigh. Finally, this application / problem domain is attracting the attention of industry behemoths such as Microsoft.

In an initial response to these developmentsOrri Erling, Virtuoso's Program Manager, shares valuable insights from past re. Object-Relational technology developments and deliverables challenges. As Orri notes, the Virtuoso team suspended ORM and ORDBMS work at the onset of the Kubl-Virtuoso transition due to the lack of standardized client-side functionality exposure points.

My hope is that Microsoft's efforts trigger community wide activity that result in a collection of interfaces that make scenarios such as generating .NET based Semantic Web Objects (where the S in an S-P->O RDF-Triple becomes a bona fide .NET class instance generated from OWL).

To be continued since the interface specifics re. ADO.NET 3.0 remain in flux...

Last week I put out a series of screencast style demos that sought to demonstrate the core elements of our soon to be released Javascript Toolkit called OAT (OpenLink Ajax Toolkit) and its Ajax Database Connectivity layer.

The screencasts covered the following functionality realms:

1. SQL Query By Example (basic)
2. SQL Query By Example (advanced - pivot table construction)
3. Web Form Design (basic database driven map based mashup)
4. Web Form Design (advanced database driven map based mashup)

To bring additional clarity to the screencasts demos and OAT in general, I have saved a number of documents that are the by products of activities in the screenvcasts:

1. Live XML Document produced using SQL Query By Example (basic) (you can use drag and drop columns across the grid to reorder and sort presentation)
2. Live XML Document produced using QBE and Pivot Functionality (you can drag and drop the aggregate columns and rows to create your own views etc..)
3. Basic database driven map based mashup (works with FireFox, Webkit, Camino; click on pins to see national flag)
4. Advanced database driven map based mashup (works with FireFox, Webkit, Camino; records, 36, 87, and 257 will unveil pivots via lookup pin)

Notes:

• “Advanced”, as used above, simply means that I am embedding images (employee photos and national flags) and a database driven pivot into the map pins that serve as details lookups in classic SQL master/details type scenarios.
• The “Ajax Call In Progress..” dialog is there to show live interaction with a remote database (in this case Virtuoso but this could be any ODBC, JDBC, OLEDB, ADO.NET, or XMLA accessible data source)
• The data access magic source (if you want to call it that) is XMLA - a standard that has been in place for years but completely misunderstood and as a result under utilized

You can see a full collection of saved documents at the following locations:

• My Mashups demo directory (Google and Yahoo! demo variants but note these do not work with Safari or IE at the current time. IE7 issues will be resolved in the next day or so)
• My Pivots demo directory (other Pivots will be added as I build and save them)
• My Saved Queries (a collection of saved QBE generated queries)

SPARQL with SQL (Inline)

Virtuoso extends its SQL3 implementation with syntax for integrating SPARQL into queries and subqueries.Thus, as part of a SQL SELECT query or subquery, one can write the SPARQL keyword and a SPARQL query as part of query text processed by Virtuoso's SQL Query Processor.

Example 1 (basic) :

Using Virtuoso's Command line or the Web Based ISQL utility type in the following (note: "SQL>" is the command line prompt for the native ISQL utility):

SQL> sparql select distinct ?p where { graph ?g { ?s ?p ?o } };

Which will return the following:

	  p varchar
     ----------
     http://example.org/ns#b
     http://example.org/ns#d
     http://xmlns.com/foaf/0.1/name
     http://xmlns.com/foaf/0.1/mbox
     ...   

Example 2 (a subquery variation):

SQL> select distinct subseq (p, strchr (p, '#')) as fragment
 from (sparql select distinct ?p where { graph ?g { ?s ?p ?o } } ) as all_predicates
 where p like '%#%' ;

     fragment varchar
     ----------
     #query
     #data
     #name
     #comment
     ...

Parameterized Queries:

You can pass parameters to a SPARQL query using a Virtuoso-specific syntax extension. '??' or '$?' indicates a positional parameter similar to '?' in standard SQL. '??' can be used in graph patterns or anywhere else where a SPARQL variable is accepted. The value of a parameter should be passed in SQL form, i.e. this should be a number or an untyped string. An IRI ID can not be passed, but an absolute IRI can. Using this notation, a dynamic SQL capable client (ODBC, JDBC, ADO.NET, OLEDB, XMLA, or others) can execute parametrized SPARQL queries using parameter binding concepts that are common place in dynamic SQL. Which implies that existing SQL applications and development environments (PHP, Ruby, Python, Perl, VB, C#, Java, etc.) are capable of issuing SPARQL queries via their existing SQL bound data access channels against RDF Data stored in Virtuoso.

Note: This is the Virtuoso equivalent of a recently published example using Jena (a Java based RDF Triple Store).

Example:

Create a Virtuoso Function by execting the following:

SQL> create function param_passing_demo ();
 {
 	declare stat, msg varchar;
 	declare mdata, rset any;
 	exec ('sparql select ?s where { graph ?g { ?s ?? ?? }}',
 			stat, msg,
 			vector ('http://www.w3.org/2001/sw/DataAccess/tests/data/Sorting/sort-0#int1',
 		  		   4 ),	-- Vector of two parameters 
			10,			-- Max. result-set rows
			mdata, 		-- Variable for handling result-set metadata
 		 	rset   		-- Variable for handling query result-set
		 ); 
     return rset[0][0];
 }

SQL> select param_passing_demo ();

Which returns:

callret VARCHAR
 _______________________________________________________________________________

http://www.w3.org/2001/sw/DataAccess/tests/data/Sorting/sort-0#four

1 Rows. -- 00000 msec.

Using SPARQL in SQL Predicates:

A SPARQL ASK query can be used as an argument of the SQL EXISTS predicate.

create function sparql_ask_demo () returns varchar
  {
 		if (exists (sparql ask where { graph ?g { ?s ?p 4}})) return 'YES';
 		else return 'NO';
   };

Test by executing:

SQL> select sparql_ask_demo ();

Which returns:

_________________________
YES

Cognitive dissonance is how Dare Obasanjo aptly describes the emergence of some of the Smart Tags concepts previously introduced by Microsoft and now emulated by the new google toolbar's autolink feature (Greg Linden explains the problem with clarity).

Anyway, back to cognitive dissonance. Could this be the reason for the following?

1. Open Source products are increasingly database specific even though they could be database independent via Open Source ODBC SDK efforts such as iODBC and unixODBC. We increasingly narrowing our choices down to database specific "Closed Source" or database specific "Open Source" solutions and somehow deem this to be progress
   
2. The prevalent use of free standards compliant data access drivers (ODBC, JDBC, and ADO.NET) or their native counterparts that remain vulnerable to simple password hacks (there are databases behind those dynamic web sites!!) as none of these have any notion of "rules based" authentication and data access policy
   
3. The time-tested fallacy that: "select * from table" defines a viable RDBMS engine since Transaction Atomicity, Concurrency, Isolation, and Durability (ACID) mean zip! Ditto scrollable cursors, stored procedures, and other presumably useless aspects of any marginably decent RDBMS engine
   
4. Failing to comprehend that a Weblog is your property (if you have a personal blog) not the property of the vendor hosting your service (that important issue of separating data ownership and data storage again). You may have heard about, or experienced, total loss of weblog and/or weblog archives arising from weblog engine or blog service provider changeovers
   
5. Failing to see the synergy between personal/group/corporate information stores (aka infobase) such as Wikis, Weblogs, and the burgeoning semantic web. Jon Udell for instance, is trying to get the point across via his tireless collection of XQuery/XPath based queries aimed at the blogosphere section of the burgeoning semantic web. Here are some of mine (scoped to this weblog):
   
• Security related posts to date (XPath query)
  
• Infobase related posts to date (Free Text search)
  

And more...

Email As A Platform It looks like more people are starting to realize that email is more than it seems. Especially given the drastic increase in storage size of web-based email applications, more people are realizing that email is basically a personal database. People simply store information in their email, from contact information that was emailed to them to schedule information to purchase tracking from emailed receipts. Lots of people email messages to themselves, realizing that email is basically the best "permanent" filing system they have. That's part of the reason why good email search is so important. Of course, what the article doesn't discuss is the next stage of this evolution. If you have a database of important information, the next step is to build useful applications on top of it. In other words, people are starting to realize that email, itself, is a platform for personal information management.

Here are some excerpts (inlined) with my comments (outlined) from an interesting article on SQL DBMS exploits and vulnerabilities by Aaron C. Newman, for DB2 Magazine titled "6 Security Secrets Attackers don't want You To Know".

How secure is your data? Looking at your information management resources through a would-be intruder's eyes can help you find (and fix) vulnerabilities.

Naturally :-)

When E. F. Codd developed his relational data model in 1970, the business world was a different place. Almost 35 years after his seminal work appeared, RDBMSs that sprung from Codd's ideas are the standard for storing corporate information. And, with government and industry regulations dictating what kinds of information companies have to store, manage, and audit (and for how long), protecting this information is more important than ever. Unfortunately, it's also more challenging

Even in 1985, when Dr. Codd published 12 guidelines for RDBMSs, there was little concern for data security. In those days, gaining access to a database was so difficult that advanced security features on the database were irrelevant.

Today, RDBMSs carry the lifeblood of every organization. Note the use of the plural: Organizations now have many databases that are decentralized in terms of use and security controls. E-business demands that data access be extended to customers, partners, suppliers, and other parties who were rarely considered in the early data management days. With all this availability ? not to mention pressure from an array of government and industry regulations (see the sidebar, "Security and Compliance") ? the need to control exactly who can access or modify data is becoming paramount.

Absolute facts, that are still partially understood at best. For instance we are still in a so called "Information Age" in which standards based data access remains an issue of contempt instead of absolute necessity.

There are a number of prevailing myths about standards based data access that continue to cloak reality:

1. ODBC, JDBC, ADO.NET, OLEDB all deliver poor performance (compared to their native, proprietary, and database specific counterparts; native interfaces)
   
2. You can't really right generic database applications with these standards due to inconsistencies in the DBMS implementations of SQL (not true! there are many aspects of the specs that address these concerns if only a majority of driver vendors would implement these features, and the application developers actually used them by seeking drivers with full implementations).

Even if the above were true (which I refute strongly), how about the general security vulnerabilities that affect both Native, and Standards compliant, data access interfaces?

Aaron's article does a good job of highlighting 6 areas of vulnerability:

1. DBMS Defaults (usernames and passwords)
2. Authentication (at connect time)
3. Database Privileges
4. Fixpaks
5. Buffer Overflows
6. SQL Injection

What I have been able to do very quickly (thanks to blogging, and the power of a blog engine that supports WebDAV), is write a tabulated response to each of the items (bar Fixpaks) indicating how the OpenLink Multi-Tier Data Access Drivers (for ODBC, JDBC, ADO.NET, and OLEDB) protect corporate databases from each of these vulnerabilities.

To cut a long story short, we are increasingly living a contradiction where the terms "simple" and "free" are supposed to lead us to products that can adequately handle the challenges of an increasingly sophisticated grid of inter-connecting point.

I have been asked on numerous occassions, "How can you build a company and business based on data access technology?". My reply is the same as usual, "because everything comes down to data". If the data is compromised in anyway, then kiss Information, Knowledge, and everything else goodbye!

My little addition to the observation below re. InfoPath: when will this tool actually make use of ADO.NET or ODBC in a manner reflective of these data access APIs? There are supposed to facilitate database independence, but InfoPath simply does not want to know anything other than SQL Server or ACCESS?

So we all buy and deploy copies of InfoPath, and then get rid of our non SQL Server and ACCESS databases? Wow!

How about InfoPath emitting XForms compliant forms? Even better, what about

My little addition to the observation below re. InfoPath: when will this tool actually make use of ADO.NET or ODBC in a manner reflective of these data access APIs? There are supposed to facilitate database independence, but InfoPath simply does not want to know anything other than SQL Server or ACCESS?

So we all buy and deploy copies of InfoPath, and then get rid of our non SQL Server and ACCESS databases? Wow!

How about InfoPath emitting XForms compliant forms? Even better, what about

This piece links to a great Mono presentation (bar the reference placement of MySQL/PostgreSQL in a box somewhat adjacent to ADO.NET (see slide 7). When ADO.NET should have be associated with Data Providers for ODBC, MySQL, PostgresSQL, and others for clarity (the natural goal of the presentation).

We have got to take time to understand the Data Access Layer, if we don't we will utlimately pay a hefty price (IMHO).

This blog post is also hillarious, especially if you have encountered the mercurial "Murphy" during live product demos.

So, today I went to hell. And then I came back. It was a short trip.

This year, I am giving a presentation on Mono at Brainshare in Salt Lake City, an intro to Mono for developers. I got a pretty good turnout with a few ximian people in the back (including Joe whom I saw for the first time without a hat).

So I plug in my PowerBook 12" as I always do but for some reason I have a hard time getting the projector to display its output. After struggling a little I resort to using the desktop provided by Novell, running Ximian Desktop 2 (and some version Suse Linux).

So I upload my presentation to www.frenchguys.com from my mac and then download it back to the desktop. Now I can make my presentation, which goes well. Then I get to a slide that just says : DEMO. Hmmm. Demo. I don't have Mono installed on that generic machine I was just given. I am going to need magic. So to magic I resort.

[via Monologue]