A Practical Guide to a Post-Relational Database for .NET: Matisse

Share |

When it comes to databases for .NET, there are several options in addition to the well-known ones such as SQL Server or MySQL. One of them is Matisse, a post-relational database by Matisse Software.

Why Matisse? It is the only SQL database that I know of that is extended with complete object capabilities and natively supports .NET. It features user-defined types, inheritance, polymorphism, and a simple data model with declarative constraints, and so forth. Over the last two years, I have acquired practical knowledge of Matisse while working on several .NET projects that required complex data modeling.

While one can find press articles that provide high-level comparisons between mainstream relational databases and other products, I have not seen yet a simple step-by-step tutorial to help developers who want to try their hand at new-generation databases. I therefore resolved to publish a series of short articles to help address this void. Here is the first one.

This first article covers a quick overview of SQL programming with Matisse. The subsequent articles will show how to develop database applications with .NET and ASP.NET in more detail.

Installation

Installing Matisse is quick and easy. Go to the Matisse download site http://www.matisse.com/developers/downloads/, and download the next two files under the "Matisse DBMS 7.0.x" section:

1. Intel-MS Windows (file name is matisse70x.exe)
2. .NET binding (file name is matisseDotNet70x.exe)

The first file installs the database server, administration and development tools, and a common client library shared by diverse language interfaces for Matisse including .NET (i.e., C# and VB.NET). The second file contains a .NET assembly that provides object persistence services and a native ADO.NET data provider.

To install Matisse, you need to have the Windows Administrator privilege. System requirements are Windows NT, 2000, or XP, 64MB of RAM, and 100 MB of disk space. First, start the matisse70x.exe file, follow the instructions, and select "Typical/Full" as the type of setup. The installation will be completed within a couple of minutes. Then, start matisseDotNet70x.exe to install the .NET interface. Choose the same directory for the destination folder as the first installation (i.e., matisse70x.exe).

The documents you want to look at first are:

1. Discovering Matisse Enterprise Manager (from Readme.html)
2. Building reusable components with SQL PSM (from Readme.html)
3. Getting Started with Matisse

A lot of documents, including programming guides, administration guide, and installation guide are also available at http://www.matisse.com/developers/documentation/.

If you have the Rational Rose modeling tool, you can download Matisse Rose Link (matisseRoseLink70x.exe). You can define and maintain database schema using UML with Rational Rose.

Note: You can run the database server on Linux when deploying your .NET application on Windows. Download the Linux version of Matisse (matisse-7.0-x.i386.rpm) and install it using rpm. If you are using RedHat 8, you need to set the environment variable RPM_INSTALL_PREFIX to /usr/local/matisse before running rpm.

> rpm -ihv matisse-7.0-x.i386.rpm

What You Can Do with the Matisse Enterprise Manager

Before writing a simple demo program using SQL, let us visit some interesting features of the Enterprise Manager.

1. You can browse classes, attributes, relationships, and SQL methods in a database just like any other vendors' tools. An interesting feature is that a class can show all its properties (i.e., attributes, relationships, and methods) including its superclasses' properties. So, when you write an SQL statement on a class, this feature is useful because you do not have to go back and forth between superclasses and subclasses to find out about properties.
   

   
   (Full Size Image)

   
   
2. Data Import (CSV)
   

   You can import data from your relational database using CSV (Comma-Separated Value) files. When you import a CSV file, each line (row) in the file is stored as a data object in the Matisse database. After importing all the CSV files, you specify an XRD file (XML Relationship Definition) that describes how to establish links between objects in the database. Then, objects in the database are inter-related to each other, building a meaningful semantic network that matches your UML description. Relationships between objects also provide a significant performance benefit on SQL queries.

Simple Demo

In this article, I will show a simple demo application that demonstrates how you can use SQL to define a schema and manipulate data objects with Matisse. More detailed discussions will follow in the subsequent articles.

First of all, you need to start a database. Start the Enterprise Manager, select a database, and select the Start menu. The database will be online within a couple of seconds:

The data model that we are going to use is for project management, in which we define three classes—Project, Employee, and Manager—as depicted using UML in the next figure.

If you have Rational Rose, you simply can export the UML diagram into your database. Choose the Export to Database... menu under Tools/Matisse:

If you do not have Rational Rose, you can use SQL DDL or ODL (Object Definition Language). The following DDL statements are equivalent to the above UML diagram.

CREATE TABLE Project (
   ProjectName STRING,
   Budget NUMERIC(19,2),
   Members REFERENCES (Employee)
      CARDINALITY (1, -1)
      INVERSE Employee.WorksIn,
   ManagedBy REFERENCES (Manager)
      CARDINALITY (1, 1)
      INVERSE Manager.Manages
);

CREATE TABLE Employee (
   Name STRING,
   BirthDate DATE,
   WorksIn REFERENCES (Project)
      INVERSE Project.Members
);

CREATE TABLE Manager UNDER Employee (
   Title STRING,
   Manages REFERENCES (Project)
      INVERSE Project.ManagedBy
);

To execute the above DDL statements, copy and paste them into the SQL Query Analyzer window, and then execute them.

(Full Size Image)

Here, you see an advantage in database modeling with Matisse. You do not need any transformation of your model, and all the semantic information about associations between classes and their constraints are kept in the database schema as they are. This is a big plus for maintenance and extension of the application.

We now can create objects in the database. Execute the following SQL statements in the SQL Query Analyzer window as shown above:

INSERT INTO Employee (Name, BirthDate)
   VALUES ('John Venus', DATE '1955-10-01')
   RETURNING INTO emp1;
INSERT INTO Employee (Name, BirthDate)
   VALUES ('Amy Mars', DATE '1965-09-25')
   RETURNING INTO emp2;
INSERT INTO Manager (Name, BirthDate, Title)
   VALUES ('Ken Jupiter', DATE '1952-12-15', 'Director')
   RETURNING INTO mgr1;
INSERT INTO Project (ProjectName, Budget, ManagedBy, Members)
   VALUES ('Campaign Spring 04', 10000.00, mgr1,
   SELECTION(emp1, emp2));

The above statements create two Employee objects, a Manager object and a Project object, and then assign the two employees to the project as its members and the manager as the project manager.

To view the inserted objects, execute "SELECT * FROM Employee"; for example:

(Full Size Image)

When you select from the Employee class, the query returns objects from both Employee and Manager because Manager inherits from Employee. However, the result table does not include the properties specific to Manager, e.g., Title, because the attribute Title is not visible from the Employee class.

You can define SQL methods for classes. The syntax follows SQL PSM (Persistent Stored Module). For example, let us define an instance method Age() that returns an employee's age:

CREATE METHOD Age()
RETURNS INTEGER
FOR Employee
BEGIN
   RETURN EXTRACT(YEAR FROM CURRENT_DATE) -
          EXTRACT(YEAR FROM SELF.BirthDate);
END;

Execute the statement in the SQL Query Analyzer window, and then try the next SELECT query:

SELECT * FROM Employee emp WHERE emp.Age() > 40;

The Age() method works for both Employee and Manager, of course. You can override the method for Manager, and enjoy the polymorphic behavior just as you do with .NET.