Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID NHibernate Support project


Contents

CUBRID Data types testing

Standard data types

Overview

For testing of the standard data types support in NHibernate with CUBRID we implemented a suite of test cases that perform the basic SQL operations (Create, Read, Update and Delete - CRUD): 

svn.png

A generic table was created with columns of all the standard SQL data types available in CUBRID: 

create table TestDataTypes (
c_integer INTEGER NOT NULL AUTO_INCREMENT,
c_smallint SMALLINT,
c_bigint BIGINT,
c_numeric NUMERIC(10,2),
c_float FLOAT,
c_decimal DECIMAL(19,5),
c_double DOUBLE,
c_char CHAR(1),
c_varchar STRING,
c_time TIME,
c_date DATE,
c_timestamp TIMESTAMP,
c_datetime DATETIME,
c_monetary MONETARY,
c_string STRING,
c_bit BIT(1),
c_varbit BIT VARYING(1),
primary key (c_integer))

 

Note: All test cases were developed in C#, using MS Visual Studio

 StandardDataTypes1.jpg

Setup

To map NHibernate objects to this table the following files where created:

  • Mappings\TestDataTypesStandard.cs: the mapping class for the table
  • Mappings\TestDataTypesStandard.hbm.xml: the xml mapping file for the table
  • TestDataTypesStandard.cs: the implementation of the data types test cases

StandardDataTypes2.jpg

Test cases

The test cases where developed to test all the CRUD operations on all the standard data types. Each test case performs one type of operation on all the above standard data types. For every test case, we create the database table above, we perform the tested operation and we test if the operation was performed correctly.

Test case Name

Purpose

Test_DataTypesStandard_Insert

Test data insertion of all the standard data types

Test_DataTypesStandard_Select

Test retrieving data of all the standard data types

Test_DataTypesStandard_Update

Test updating data of all the standard data types

Test_DataTypesStandard_Delete

Test deleting records from a table with columns of all the standard data types

 

Results

The results of the test cases show that all the basic SQL operations can be performed on all the standard SQL data types supported by CUBRID.

The following table depicts the results of the testing performed: 

CUBRID Data type

Test type

NHibernate compatibility status

Notes

INTEGER

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

SMALLINT

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

BIGINT

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

NUMERIC

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

FLOAT

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

DECIMAL

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

DOUBLE

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

CHAR

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

VARCHAR

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

TIME

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

DATE

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

TIMESTAMP

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

DATETIME

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

MONETARY

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

STRING

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

BIT

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

BIT VARYING

C,R,U,D

Supported

No modifications to the ADO.NET driver or NHibernate required

StandardDataTypesTestCases.jpg  

Special CUBRID data types

 

BLOB/CLOB

The purpose of testing the support for CUBRID BLOB and CLOB data types was to find out if the basic CRUD operations (INSERT, SELECT, UPDATE, DELETE) can be performed from NHibernate on a BLOB or CLOB column in a CUBRID table and what are the code adjustments required to support this in the best way.

For this purpose two tables where created in the demodb CUBRID database: one with a column of type BLOB and one with a column of type CLOB.

The files used in the tests are:

  • TestCUBRIDBLOBType.cs: the mapping class for the table with a BLOB column
  • TestCUBRIDCLOBType.cs: the mapping class for the table with a CLOB column
  • TestCUBRIDBLOBType.hbm.xml: the xml mapping file for the table with a BLOB column
  • TestCUBRIDCLOBType.hbm.xml: the xml mapping file for the table with a CLOB column
  • TestDataTypesCUBRID.cs: the implementation of the test cases;

Preliminary tests results showed that these custom CUBRID types are not supported by NHibernate out of the box.

 The CUBRID BLOB and CLOB types are custom types and are implemented as custom classes in the ADO.NET driver so they are not recognized by NHibernate.

 

Additional modifications to the ADO.NET driver and NHibernate Core

The solution to this problem was to implement an adapter for the CUBRID BLOB and CLOB types in the NHibernate Core.

To do that, two classes (CUBRIDBLOBType.csand CUBRIDCLOBType.cs) that implement the PrimitiveType interface where created in NHibernate.Type namespace. These classes are responsible for transferring a CUBRID BLOB or CLOB object from the ADO.NET driver into NHibernate and map this object to a property of the corresponding mapping class.

In order to implement the PrimitiveType interface, we need to implement a number of methods and we will see how to implement the most important ones.

First of all let's see the constructor method: 

public CUBRIDBLOBType() : base(SqlTypeFactory.GetSqlType(DbType.Binary, 0, 0))
{
}

public CUBRIDCLOBType() : base(SqlTypeFactory.GetSqlType(DbType.AnsiString, 0, 0))
{
}

The constructor method inherits the base class constructor and passes as parameter the SQLType to which the ADO.NET driver maps BLOB and CLOB objects. In our case, the ADO.NET driver maps a BLOB object to DbType.Binary and a CLOB object to DbType.AnsiString.

Another important component of the PrimitiveType interface we need to implement is the Name property. This tells NHibernate the name that the user will use in the xml mapping file when using CUBRIDBLOB or CLOB data types: 

public override string Name
{
    get { return "CUBRIDBlobType"; }
}

public override string Name
{
    get { return "CUBRIDClobType"; }
}

Now comes the most important methods that we need to implement; the Get and Set methods form the PrimitiveType interface. Because CUBRID BLOB and CUBRID CLOB objects are very similar these methods are implemented the same for both CUBRIDBLOB and CUBRIDCLOB classes.

public override object Get(IDataReader rs, int index)
{
    return rsindex;
}

public override object Get(IDataReader rs, string name)
{
    return rsname;
}

/// <summary>
///Sets a <see cref="System.Data.IDataParameter" /> parameter with the value of the BLOB object
/// </summary>
/// <param name="st"></param>
/// <param name="value"></param>
/// <param name="index"></param>

public override void Set(IDbCommand st, object value, int index)
{
    if (value == null)
    {
        ((IDataParameter)st.Parametersindex).Value = DBNull.Value;
    }
    else
    {
        ((IDataParameter)st.Parametersindex).Value = value;
    }
}

The Get method receives as parameters a DataReader object from the ADO.NET driver and the index of the column that is read or the name of the column that is read. The method's purpose is to pass the CUBRIDBLOB or CUBRIDCLOB object from the driver into NHibernate. The Set method is null-safe. It receives a DbCommand from the ADO.NET driver, the value to be passed to the driver and the index of the column that is written. The purpose of the method is to set the value of the command parameter corresponding to the column that is being written. If the value is null, we write a DBNull value to the parameter.

After implementing these classes, the new types need to be registered in the TypeFactory class: 

RegisterType(NHibernateUtil.CUBRIDBLOB, new[] { "CUBRIDBLOB" });
RegisterType(NHibernateUtil.CUBRIDCLOB, new[] { "CUBRIDCLOB" });

The last modification needed is to tell NHibernate how to create an internal object of the new type. We do that in the NHibernateUtil class:

///<summary>
/// NHibernate CUBRIDBLOB type
///</summary>
public static readonly NullableType CUBRIDBLOB = new CUBRIDBLOBType();

///<summary>
/// NHibernate CUBRIDCLOB type
///</summary>
public static readonly NullableType CUBRIDCLOB = new CUBRIDCLOBType();

After these modifications, we can map a NHibernate object property to a CUBRID BLOB or CLOB column. The xml mapping looks like this: 

SpecialDataTypesMapping.jpg 

Test results

After performing these modifications the test results show that all the basic operations can be performed on CUBRID BLOB and CLOB columns:

Operation/Type

BLOB

CLOB

Insert

Supported

Supported

Select

Supported

Supported

Update

Supported

Supported

Delete

Supported

Supported

 

SET, MULTISET, SEQUENCE

The purpose of testing support for CUBRID Collection data types (Set/Multiset/Sequence) is to see if the CRUD operations (INSERT, SELECT, UPDATE, DELETE) can be performed from NHibernate on a Set, Multiset or Sequence column in a CUBRID table. For this purpose a table was created in the demodb CUBRID database with three columns: one of type Set, one of type Multiset and one of type Sequence.

The files used in the tests are:

  • TestCUBRIDCollectionType.cs: the mapping class for the table with a CUBRID Set, Multiset and Sequence columns
  • TestCUBRIDCollectionType.hbm.xml:the xml mapping file for the table with a CUBRID Set, Multiset and Sequence columns
  • TestDataTypesCUBRID.cs: the implementation of the test cases;

Preliminary tests results showed that these custom CUBRID types are not supported by NHibernate

The CUBRID Set, Multiset and Sequence types are custom types and are handled in a special way by the CUBRID ADO.Net Driver.

 

Additional modifications to the ADO.NET driver and NHibernate Core

The solution to this problem was to implement an adapter class for these types in the NHibernate Core. 

The approach is very similar to the one taken for the CLOB/BLOB data types!

We will not go into many details – please take a look at the previous CLOB/BLOB section to understand the approach.

So we will list in the next section only the key steps, starting with the new class creation:

public CUBRIDCollectionType() : base(SqlTypeFactory.GetSqlType(DbType.VarNumeric, 0, 0))
{
}

The constructor method inherits the base class constructor and passes as parameter the SQL type to which the ADO.NET driver maps SET, MULTISET and SEQUENCE objects. In our case, the ADO.NET driver maps these types of objects to DbType.VarNumeric

Next, we added the Name property: 

public override string Name
{
    get { return "CUBRIDCollection"; }
}

Next, we implemented the Get and Set methods from the PrimitiveType interface:

public override object Get(IDataReader rs, int index)
{
    return rsindex;
}

public override object Get(IDataReader rs, string name)
{
    return rsname;
}

/// <summary>
/// Sets a <see cref="System.Data.IDataParameter" /> parameter with the value of the CUBRIDCollection object
/// </summary>
/// <param name="st"></param>
/// <param name="value"></param>
/// <param name="index"></param>

public override void Set(IDbCommand st, object value, int index)
{
    if (value == null)
    {
        ((IDataParameter)st.Parametersindex).Value = DBNull.Value;
    }
    else
    {
        ((IDataParameter)st.Parametersindex).Value = value;
    }
}

After implementing all these above, the new types need to be registered in the TypeFactory class:

RegisterType(NHibernateUtil.CUBRIDCollections, new[] { "CUBRIDCollection" });

The last modification needed was to tell NHibernate how to create an internal object of the new type. We do that in the NHibernateUtil class:

///<summary>
/// NHibernate CUBRIDCollection type
///</summary>
public static readonly NullableType CUBRIDCollection = new CUBRIDCollectionType();

After these modifications, we can map a NHibernate object property to a CUBRID SET, MULTISET and SEQUENCE column.

The xml mapping looks like this: 

SpecialDataTypesMapping2.jpg  

Test results

After performing these modifications the test results show that all the basic operations can be performed on CUBRID SET, MULTISET and SEQUENCE columns: 

Operation/Type

Set

Multiset

Sequence

Insert

Supported

Supported

Supported

Select

Supported

Supported

Supported

Update

Supported

Supported

Supported

Delete

Supported

Supported

Supported

 

SpecialDataTypesTestCases.jpg

Relationships testing

 

1:N Relationship tests

For testing 1:N relationship in NHibernate we used the Nation and Athlete tables in the demodb database. The tests consist in performing the basic SQL CRUD operations (INSERT, SELECT, UPDATE, DELETE) on the relationship between the two tables.

The files used in the tests are:

  • Mappings\AthleteOneToMany.cs: the mapping class for the Athlete table.
  • Mappings\AthleteOneToMany.hbm.xml: the xml mapping file for the Athlete table.
  • Mappings\Nation.cs: the mapping class for the Nation table.
  • Mappings\Nation.hbm.xml: the xml mapping file for the Nation table.
  • TestRelationships.cs: the implementation of the test cases.

Test results

 

Operation

Test Result

Notes

Insert

OK

Insertion of a new Nation triggers an insertion of a new Athlete

Select

OK

Selection of a Nation triggers the selection of the corresponding Athletes

Update

OK

Updating a Nation also updates the corresponding Athletes

Delete

OK

Deleting a Nation triggers the deletion of the orphan Athletes

 

M:N Relationship tests

For testing M:N relationship in NHibernate we used the Event and Athlete tables in the demodb database. Because in NHibernate the columns referenced by the foreign keys must be the primary key in the reference table, we could not use the Record table as reference between Event and Athlete.

So for testing, a custom reference table was created:

create table AthleteEvent(
    event_code int,
    athlete_code int,
    primary key(event_code,athlete_code)
)

The tests consist in performing the basic SQL operations (INSERT, SELECT, UPDATE, DELETE) on the relationship between the two tables.

The files used in the tests are:

  • Mappings\AthleteManyToMany.cs: the mapping class for the Athlete table.
  • Mappings\AthleteManyToMany.hbm.xml: the xml mapping file for the Athlete table.
  • Mappings\Event.cs: the mapping class for the Event table.
  • Mappings\Event.hbm.xml: the xml mapping file for the Event table.
  • TestRelationships.cs: the implementation of the test cases.

 

Test results

 

Operation

Test Result

Notes

Insert

OK

Insertion of a new Athlete triggers an insertion of a new Event and the insertion of a new record in the reference table

Select

OK

Selection of an Athlete triggers the selection of the corresponding Event and selection of an Event triggers the selection of the corresponding Athletes

Update

OK

Updating an Athlete or Event also updates the corresponding the other part of the relationship and the reference table.

Delete

OK

Deleting a Athlete triggers the deletion of the orphan Events and deletes the corresponding record in the reference table

  

1:1 Relationship test

1:1 relationships where tested using a more complex schema from the demodb database. We used the Game table as the center table and we linked the Nation, Athlete, Stadium and Event tables to it in 1:1 relationships.

As usual, the tests consist in performing the basic SQL operations (INSERT, SELECT, UPDATE, DELETE) on the relationship between the tables. 

The files used in the tests are:

  • Mappings\AthleteOneToOne.cs: the mapping class for the Athlete table.
  • Mappings\AthleteOneToOne.hbm.xml: the xml mapping file for the Athlete table.
  • Mappings\Event.cs: the mapping class for the Event table.
  • Mappings\Event.hbm.xml: the xml mapping file for the Event table.
  • Mappings\Game.cs: the mapping class for the Game table.
  • Mappings\Game.hbm.xml: the xml mapping file for the Game table.
  • Mappings\Stadium.cs: the mapping class for the Stadium table.
  • Mappings\Stadium.hbm.xml: the xml mapping file for the Stadium table.
  • Mappings\Nation.cs: the mapping class for the Nation table.
  • Mappings\Nation.hbm.xml: the xml mapping file for the Nation table.
  • TestRelationships.cs: the implementation of the test cases. 

Test results 

Operation

Test Result

Notes

Insert

OK

Inserting a new Game triggers the insertion of new entries in all the other tables in the relationship

Select

OK

Selecting a Game triggers the selection of the corresponding records in the other tables

Update

OK

Updating a Game updates the corresponding information in the other tables in the relationship

Delete

OK

Deleting a Game triggers the deletion of the orphan records in the other tables

 

RelationshipsTestCases.jpg  

 References 

Resource

Address

CUBRID ADO.NET Driver download from cubrid.org

http://www.cubrid.org/?mid=downloads&item=ado_dot_net_driver&os=windows&cubrid=9.1.0

CUBRID NHibernate source code

http://svn.cubrid.org/cubridapis/NHibernate

CUBRID NHibernate Wiki page

http://www.cubrid.org/wiki_apis/entry/using-cubrid-and-nhibernate

Test cases suite source code

http://svn.cubrid.org/cubridapis/adodotnet/branches/RB-9.1.0-p1/Code/Test/NHibernate/

comments powered by Disqus
Page info
viewed 1181 times
translations en
Author
posted last year by
CUBRID
Contributors
updated last year by
View revisions
tagged
Share this article