Hornet SQL Bridge

Hornet provides a bridge between traditional SQL datasources and the ZODB.

Current release

No stable release available yet.

Project Description

Overview

Once installed and configured an SQL database will be accessible in the ZMI as a folderish object, its tables and views will show as folderish objects and records in these tables as standard Zopeish objects. All are traversable, provide acquisition and can be skinned in a CMF and Plone Environment. Base View and Edit templates are dynamically generated for all record objects from schemas but can be extended and customised providing an extremely rapid solution for making SQL content available in Zope, CMF or Plone

Objectives

The current SQL connectors that exist are relatively adequate, however by their nature developers are generally forced to fall back to non object-oriented scripting: building SQL statements, passing queries to the database then iterating over dictionaries or record sets, and manually coding view and edit forms. This approach is frustrating when compared to the simplicity and beauty of CMF and Archetype solutions.

Hornet SQL Bridge is intended as a solution to this problem. The objective is to bridge the gap between SQL and ZODB by making the SQL database and its tables function very much like standard ZODB container objects, and making the records in a table function like standard ZODB objects.

When placed in a CMF environment Hornet even takes on CMF features allowing the browsing, viewing and editing of SQL records within a CMF site as if they were any standard CMF Type object.

For a developer this means that you can take advantage of the speed of development and ease of skinning provided by Zope, CMF and Plone and still store your data in a traditional SQL database.

Hornet vs Other ZODB-SQL Solutions

vs APE

Hornet differs from ZODB-SQL storage solutions such as APE as it does not use an object abstraction for data storage. This means that the data maintains a regular SQL structure and is readily viewable and editable in the SQL database from other sources.

vs Archetypes SQLStorage

Hornet differs from Archetypes SQLStorage in that Hornet objects are not actually persistent ZODB objects and do not fill/bloat the ZODB. A Hornet storage of 1,000,000 objects will add no more size to the ZODB than one with 10 objects so it is ideal for tasks were you need to store truly large numbers of records. This is not true of Archetypes, where each record still has a corresponding ZODB object. Despite this, Hornet objects are can be indexed into ZCatalog and browsed and called using standard ZODB calls as if they were persistent ZODB objects.

Naturally this makes Hornet less flexible in some ways. It is not so easy to rapidly modify schemas as this requires a change to the underlying SQL database. It also means that the types of objects that can be placed in the Hornet Folders are controlled and ZODB features such as copy/paste are not possible. However for most applications where Hornet is useful, these are usually not an issue.

It is also important to understand that Hornet is not an alternative to ZODB and is not a replacement for Archetypes. It is a hybrid solution that will be handy for developers in some situations, most notably where complex relational data is required or extremely large numbers of objects are used. Perhaps also, very handy where Zope projects need to access existing SQL databases seamlessly.

In the same way that Archetypes provides a simple and fast solution for building ZODB stored CMF and Plone Types, Hornet provides a rapid solution for developing SQL stored Zope, CMF and Plone Types.

Reasons for using Hornet

Mooball.com has been developing Zope, CMF and Plone sites for many years now. One of the most common dilemmas we face is how to handle projects that specifically require access to relational data structures. We have often managed to develop solutions that store 10,000 or more items in the ZODB and with wise use of ZCatalog they perform adequately. However, there are many cases where the relationships between tables are simply too complex or the numbers of objects too large to effectively use the ZODB.

Existing SQL Data

If you already have an SQL database and you wish to access and manipulate that data from within a Zope environment, then Hornet is an ideal solution. You can build a custom ZODB-SQL object mapping which will make your SQL records available inside Zope. This is much more elegant than using the traditional ZSQL methods as it allows you to continue developing with a truly Object Oriented framework with data from outside the ZODB.

Large numbers of records

If you have to deal with truly large numbers of records, there really is a practical limit to ZODB storage. Particularly if the records are changing rapidly or coming from an alternative source. Hornet will provide seamless access to the SQL data without ZODB bloat.

Complex Relational Structures

Sometimes the problem simply requires a complex relational data structure that can't be achieved solely through and object database such as ZODB. But you may prefer to develop in a Zope environment or you may wish to integrate the solution into an existing Zope/Plone environment. In this case you can use Hornet to make the data and objects available in your Zope/Plone environment but continue to push much of the relational work out to the SQL Database.

SQL Data Sources

Currently Hornet only works well with MySQL due to optimisations in the database connector. We are currently working on making the connection more generic so that it should work with more databases soon.

License

We are planning to release Hornet under the ZPL or similar license.

Status

Hornet is currently in an alpha state. We are using it in production on several sites with great success but the code is not refined and APIs are not stable yet.

We are keen to get feedback from anybody who has the time and is interested in evaluating the product so please contact us at hornet@mooball.com if you want more information.