Introduction
When it comes to data storage for .NET, there are several options in addition to the well-known ones such as SQL Server and 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, etc. 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 wish to try their hand at next 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
Installation of Matisse is quick and easy. Go to the Matisse download site http://www.matisse.com/developers/downloads/, and download the next two files under "Matisse DBMS 7.0.x" section:
- Intel - MS Windows (file name is matisse70x.exe)
- .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 run as a Windows Administrator. 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 the matisseDotNet70x.exe to install the .NET interface. Choose the same directory for destination folder as the first installation (i.e., matisse70x.exe).
The documents you want to look at first are:
- Discovering Matisse Enterprise Manager (from Readme.html)
- Building reusable components with SQL PSM (from Readme.html)
- Getting Started with Matisse
A lot of documents, including programming guides, an administration guide, and an 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 that 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 since you do not have to go back and forth between superclasses and subclasses to find out properties.
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), which 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 as well.
Simple Demo
In this article, I am going to 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 on-line 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 can simply export the UML diagram into your database. Choose 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 execute them.
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. This is a big plus for maintenance and extension of the application.
We can now 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:
When you select from the class Employee
, the query returns objects from both Employee
and Manager
since Manager
is inheriting 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 class Employee
.
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 the age of an employee:
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.
Summary
In this article, I showed a quick and brief introduction to SQL programming with Matisse, which shows object features like inheritance and relationships.