Rich Morin :: tchotchkes

Using DBMS tables for inter-application communication

I have been thinking about ways to integrate some large applications and frameworks into an even larger system. In line with the Perl virtue of Laziness, I'd like to write as little code as possible, particularly if it means making changes to the apps themselves. At the same time, I'd like to avoid supporting a plethora of interfaces and protocols. Fortunately, I may have hit upon a useful approach.

Technorati Tags: DBI-Link, DBI-Link, DBMS, ontology, Perl DBI, PostgreSQL, RDBMS, semantic wiki, wiki

The components in question are quite diverse, including a Business Intelligence Platform (Pentaho), an Ontology Editor and Knowledge Base Framework (Protégé) and a Semantic Wiki (Semantic MediaWiki), just for starters. They are written in assorted languages (e.g., C, Java, PHP) and support wildly diverse communication protocols.

However, each of them uses an RDBMS (Relational Data Base Management System) for its persistent storage. So, if I could find a way to transform and exchange database tables, I might be able to effect the particular kinds of communication I need. However, there are two obvious problems with this approach.

First, there is no consensus on which RDBMSs to support. Some apps support more than one, but there is no "universal" choice. Modifying all of the apps to use a common DBMS (e.g., MySQL, PostgreSQL) is a non-starter, as it is insufficiently Lazy.

There is also the problem of reconciling the schemata (etc) of the different databases. Transforming complex data structures is a daunting task. Doing it inside the apps is a total non-starter. Even if I were willing to deal with different programming languages and tool sets, the learning curves and maintenance issues would be killers.

However, it may be possible to "step around" these problems, by means of an inter-DBMS "communications server" daemon. Such a program could read and write assorted databases, storing and transforming the data as needed. However, this still begs the question of how to access the databases.

DBMS Access

There are strong arguments for using each DBMS's specific interfaces. For instance, this guarantees access to all of the DBMS's features. However, this is an expensive approach in terms of development and maintenance. Each DBMS has its own flavor of SQL, specialized capabilities, etc. Writing code that can handle all of these variations is a mind-boggling prospect.

Consequently, many cross-database projects use abstract interfaces, such as ODBC (Open Database Connectivity), possibly in a language-specific form such as JDBC (Java Database Connectivity). In the Perl community, this role is commonly filled by the Perl DBI (Perl Database Interface) module, which can use JDBC, ODBC, DBMS-specific, and even flat-file "drivers":

The DBI is the standard database interface module for Perl. It defines a set of methods, variables, and conventions that provide a consistent database interface, independent of the actual database being used.

-- Tim Bunce

Unfortunately, the consistency of the Perl DBI interface is limited by the features available in the underlying databases. So, one can either use lowest common denominator approaches or pay close attention to which features are available in which DBMS. In addition, settling on Perl DBI would actively hinder language portability. What if I wanted to recode the server in Ruby?

Although none of these issues is a show-stopper for exploratory coding, I can well imagine them ganging up on me as the project develops. So, it would be nice to have a strategy that has growth potential.

Enter DBI-Link

Consequently, I'm quite pleased to know about DBI-Link, David Fetter's imaginative and powerful bit of PL/Perl hackery. By installing Perl DBI (via PL/PerlU) as a PostgreSQL extension, DBI-Link provides the wide connectivity of Perl DBI and the advanced features of PostgreSQL.

The application (e.g., my server daemon) interacts directly with PostgreSQL, so it can be written in any desired programming language. Because PostgreSQL supports a wide range of database features, the application can use simple queries to perform complicated operations on the "target" databases.

Synchronization is clearly an issue. We cannot expect a flat file (or even all RDBMSs) to inform PostgreSQL when data has been changed. If the timing requirements are loose, a polling loop may suffice. A small "flag" file or table can also be used to indicate that a particular query should be made. In the worst case, it may be necessary to make some small additions to the target application.

Layering

Like many "layered" approaches, DBI-Link can look rather convoluted in practice. However, this also contributes greatly to its flexibility. Here is an informal sketch of how my communications daemon might work, with an expanded view of the DBI-Link "chain":

Requirements, Gotchas, etc.

DBI-Link has a relatively small set of requirements:

• PostgreSQL (8.0 or better)

• PL/PerlU (Untrusted PL/Perl)

• Perl (5.8.5 or better)

• Perl DBI (aka, DBI.pm)

• DBD modules for each data source

Because DBI-Link consists of a small set of additions to Perl and PostgreSQL, it does not impose any significant maintenance burden. However, it should be noted that DBI-Link has not been written with security in mind. Indeed, the very idea of one database system making arbitrary queries into other database systems is antithetical to basic ideas of secure design.

However, it should serve very well as a way to let me create my prototype. And, if the results seem useful, a re-implementation always can be considered...

Technorati Tags: DBI-Link, DBMS, MySQL, ontology, Perl DBI, PostgreSQL, RDBMS, semantic wiki, wiki

Using DBMS tables for inter-application communication in Computers , Science , Technology - posted at Sun, 05 Mar, 13:35 Pacific | «e» | TrackBack