Bridging the gap between databases and XML
By Linda Cole

On December 3, Snapbridge Software revealed its brainchild–a patent-pending technology for real-time integration of large amounts of data from multi-data sources. Snapbridge FDX, as this brain-child has been dubbed, fuses data from relational databases, flat files, web services, mainframe data, digital images from content repositories, streaming feeds, etc. into composite objects that can viewed, updated or written to, regardless of that data’s formatting, storage location or creation date.

Snapbridge FDX gives databases the power and freedom to leverage the XML and XSL Standards for structuring and expressing information. This new technology provides the way to unlock and freely acquire the data, both structured data and semi-structured content (documents and images), automatically transforming it from a traditional database format to an XML format. Just what is so impressive about this? Once that transformation occurs, it allows you to deal with that database as if it were an XML representation. Now that’s impressive. Snapbridge believes this patent pending technology is going to streamline the ability to unlock the data that enterprises might have in traditional operational data stores and data warehouses, allowing them to accelerate the sharing of information within the enterprise as well as between enterprises.

Benjamin Chen, CTO and Chairman of Snapbridge told Database Journal that Snapbridge FDX is “a revolutionary technology that allows developers to access data in multiple heterogeneous databases–Microsoft, Oracle, IBM, MySQL–and allows them to create a virtual link to multiple databases as if they were one database.” He went on to say that this was done in such a way that it is very high performance, very scalable, and very easy to do.

“Our ability to really make it easy to leverage the data that exists within corporate America in these databases and get it out in a manner that can be utilized by more departments and companies is really the key. A good way to think about Snapbridge is we are essentially a high-speed data-fusing engine. We can take data from multiple sources–databases, flat files, web services–and combine them together in real time to produce output composite results.”

Snapbridge is excited to be launching their beta version of XML Developers Edition, next week at the XML conference in Philadelphia. This is the same conference that the XML standard was released to the world.

A download of the beta will be available on Tuesday, December 9, on the Snapbridge website. The beta is being offered free of charge.

Pricing for production release has not yet been established, but will be announced in the near future. Snapbridge told Database Journal that pricing will be focused around enterprise class deployments.

About Snapbridge Software
Snapbridge provides powerful products and services for information integration, content publishing and XML development. Patent-pending Snapbridge FDX data federation technology delivers unprecedented performance, flexibility, and scalability by federating multiple data sources such as relational databases, flat files, web services, mainframe data, digital images from content repositories and streaming feeds to create composite objects that can be viewed, or updated as part of a transaction. Founded in 2001, the Snapbridge team consists of world-class engineers and management from top companies including SUN, TIBCO, BEA, SAP, iXL–including members of the teams that defined XML and Java. Snapbridge is working with a number of leading companies in financial services, telecom, travel, media and other industries. The company is headquartered in San Diego and is privately held, with investment backing from JP Morgan Partners and Mission Ventures. For more information, please visit www.snapbridge.com.

Defining cascading referential integrity constraints in SQL Server
Cascading referential integrity constraints are foreign key constraints that tell SQL Server to perform certain actions when a primary key field in a primary key-foreign key relationship is updated or deleted. By using cascading referential integrity constraints, you can define the actions that SQL Server 2005 takes when a user tries to delete or update a key to which existing foreign keys point.
SQL Server allows you to define cascading referential integrity constraints. These actions have a trickle-down or cascading effect, sometimes affecting several tables that were related to the primary key table. Let’s look at how these constraints are defined, and some situations where you can use them.
The following script sets up the tables that I will use to look at cascading referential constraints:
IF OBJECT_ID(\’SalesHistory\’) > 0
DROP TABLE SalesHistory
GO
CREATE TABLE SalesHistory
(
SaleID int IDENTITY(1,1) NOT NULL,
ProductID TINYINT,
CustomerID INT,
SaleDate datetime NULL,
SalePrice money NULL,
CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
)
GO
IF OBJECT_ID(\’Customers\’) > 0
DROP TABLE Customers
GO
CREATE TABLE Customers
(
CustomerID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
CONSTRAINT pk_CustomerID PRIMARY KEY (CustomerID)
)
GO
IF OBJECT_ID(\’Products\’) > 0
DROP TABLE Products
GO
CREATE TABLE Products
(
ProductID TINYINT,
ProductDescription VARCHAR(100),
CONSTRAINT pk_ProductID PRIMARY KEY (ProductID)
)
GO
Relationships between tables are required for cascading updates or delete statements; these relationships are defined through FOREIGN KEY constraints. (Here’s more information on defining SQL Server constraints (http://articles.techrepublic.com.com/5100-9592_11-6181836.html.) The code below defines a relationship between the SalesHistory and the Customers tables and a relationship between the SalesHistory and the Products tables.
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryCustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL ON UPDATE SET NULL
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ON UPDATE CASCASE
GO
The previous script introduces the cascading referential integrity options. In the first statement, I use the ON UPDATE SET NULL option; in the second statement, I use the ON DELETE CASCADE ON UPDATE CASCADE option. Here’s an overview of what these constraints mean.
SET NULL
If a delete statement affects rows in a foreign key table, those values will be set to NULL when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value NULL after the primary key record has been updated. The foreign key columns affected must allow NULL values.
CASCADE
If a delete statement affects one or more rows in a foreign key table, those rows will be deleted when the primary key record is deleted. If an update statement affects rows in the foreign key table, those rows will be updated with the value from the primary key record after it has been updated.
SET DEFAULT
All the values that make up the foreign key in the rows that are referenced are set to their default value. All foreign key columns in the related table must have default constraints defined on them.
NO ACTION
This is the default action. This specifies that if an update or delete statement affects rows in foreign key tables, the action will be denied and rolled back. An error message will be raised.
To see how these constraints work, I’ll add some data to my tables.
INSERT INTO Products
(
ProductID, ProductDescription
)
SELECT 1, \’BigScreen\’
UNION ALL
SELECT 2, \’Computer\’
UNION ALL
SELECT 3, \’PoolTable\’
GOINSERT INTO Customers(CustomerID , FirstName , LastName )
SELECT 1, \’Jason\’, \’Tomes\’
UNION ALL
SELECT 2, \’Chris\’, \’Robards\’
UNION ALL
SELECT 3, \’Megan\’, \’Hill\’
UNION ALL
SELECT 4, \’Wanda\’, \’Guthrie\’
UNION ALL
SELECT 5, \’Lilly\’, \’Cunningham\’
UNION ALL
SELECT 6, \’Amanda\’, \’Travis\’
UNION ALL
SELECT 7, \’Willy\’, \’Grant\’
UNION ALL
SELECT 8, \’Zach\’, \’Tacoma\’
UNION ALL
SELECT 9, \’Marty\’, \’Smith\’
UNION ALL
SELECT 10, \’Wendi\’, \’Jones\’
UNION ALL
SELECT 11, \’Angie\’ , \’Corolla\’
UNION ALL
SELECT 12, \’Shelly\’, \’Hartson\’
GO
I can load sample data into the SalesHistory table. Because I am using a numeric value to represent the customers, I can generate the CustomerID number with relative ease with the use of the modulus operator.
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(1, DATEADD(mm, @i, \’3/11/1919\’), DATEPART(ms, GETDATE()) + (@i + 57), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(2, DATEADD(mm, @i, \’3/11/1927\’), DATEPART(ms, GETDATE()) + (@i + 13), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(3, DATEADD(mm, @i, \’3/11/1908\’), DATEPART(ms, GETDATE()) + (@i + 29), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
SET @i = @i + 1
END
Now let’s see if the constraints I set up work. The script below deletes a customer record from my Customers table. Because the cascading constraint indicates ON DELETE SET NULL, the associated records in the SalesHistory table will have the value NULL for the CustomerID column where that column had the value of 6 before the update.
DELETE FROM Customers
WHERE CustomerID = 6
This script invokes the ON DELETE CASCADE constraint defined on the SalesHistory table. This means that when a record is deleted from the Products table, and it is related to a record in the SalesHistory table, those SalesHistory records will be deleted from the table.
DELETE FROM Products
WHERE ProductID = 1
Cascade with care
You can use cascading constraints in SQL Server to set related values to NULL, to set affected values to original default values, or to delete columns. While it’s great to have these capabilities in a testing or quality assurance environment (where data is not production data and consistently reused), I don’t recommend allowing these types of constraints in a production environment. The reason for this is that these constraints may lead to results that you or other programmers on your team are not aware of, and so their code may not be able to handle the actions from the cascading constraint.
If you are in a situation where you need to delete related data from different tables, it has been my experience to have defined procedures in place to do so, rather than to rely on the database to do this for you.