Generating NHibernate ClassMap in PL/SQL

In the previous post I showed how to generate C# classes from Oracle table definitions using a PL/SQL stored procedure. This post deals with the ClassMap generation.

I reuse the original two cursors for tables and columns, and replace the field/getter/setting declaration by Fluent NHibernate calls to Id(), Map(), and Reference():

begin
  dbms_output.enable(1000000);

  for obj in cObjects loop
    csclass := replace(initcap(obj.object_name), '_', '');

    dbms_output.put_line('public partial class ' || csclass
      || 'Map : ClassMap<' || csclass || '>');
    dbms_output.put_line('{');
    dbms_output.put_line('    public ' || csclass || 'Map()');
    dbms_output.put_line('    {');
    dbms_output.put_line('        Table("' || obj.object_name || '");');

    for col in cColumns(obj.object_name) loop
      csname := replace(initcap(col.column_name), '_', '');

      case col.data_type
        when 'CHAR' then
          dbms_output.put_line('        Map(x => x.' || csname || ', "' 
            || col.column_name || '");');
        when 'NUMBER' then
          if col.column_name = 'OID' then
            dbms_output.put_line('        Id(x => x.' || csname || ', "' 
              || col.column_name 
              || '").GeneratedBy.Custom("trigger-identity");');
          elsif col.column_name like '%\_OID' escape '\' 
              and col.table_name is not null then
            dbms_output.put_line('        References(x => x.'
              || replace(initcap(replace(col.column_name, '_OID', '')), '_', '')
              || ', "' || col.column_name || '");');
          else
            dbms_output.put_line('        Map(x => x.' || csname || ', "' 
              || col.column_name || '");');
          end if;
        else
          dbms_output.put_line('        Map(x => x.' || csname || ', "' 
            || col.column_name || '");');
      end case;
    end loop;

    dbms_output.put_line('    }');
    dbms_output.put_line('}');
  end loop;
end;

Generating NHibernate Class in PL/SQL

Data access classes for use in NHibernate take the form

public class MyFoo
{
  public virtual int MyBar { get; set; }
  ...
}

i.e. they are public classes and their member variables are declared public virtual with get and set accessors.

Due to this regular pattern and the fact that tables and columns can easily be retrieved in Oracle using the DBA_, ALL_, or USER_ views, we can thus write a stored procedure to generate the data access classes. The stored procedure can then be invoked during the build process to reflect changes in the data model

First, define cursors for tables and views and their columns:

create or replace
procedure dev_generate_dal_classes
is
  csdatatype varchar2(50);
  csname varchar2(50);
  csaccessor varchar2(50);

  cursor cObjects is
  select object_type, object_name
  from all_objects
  where owner = 'MYUSER'
  and object_type in ('TABLE', 'VIEW')
  order by 1, 2;

  cursor cColumns(oname in varchar2) is
  select tc.column_name, tc.data_type, tc.char_length, tc.data_length,
    tc.data_precision, tc.data_scale, tc.nullable,
    p.table_name
  from all_tab_columns tc
  left outer join all_cons_columns cc
    inner join all_constraints c on c.owner = cc.owner 
      and c.constraint_name = cc.constraint_name 
      and c.constraint_type = 'R'
    inner join all_constraints p on c.r_owner = p.owner 
      and c.r_constraint_name = p.constraint_name
  on tc.owner = cc.owner and tc.table_name = cc.table_name 
    and tc.column_name = cc.column_name
  where tc.owner = 'MYUSER'
  and tc.table_name = oname
  order by tc.column_id;

Next, we loop through all tables and views (outer cursor):

begin
  dbms_output.enable(1000000);

  for obj in cObjects loop
    dbms_output.put_line('public partial class ' || 
      replace(initcap(obj.object_name), '_', ''));
    dbms_output.put_line('{');

    for col in cColumns(obj.object_name) loop

      csname := replace(initcap(col.column_name), '_', '');
      csdatatype := col.data_type;
      csaccessor := '{ get; set; }';

For the inner loop we use a parameterized cursor. Each column needs to have its data type mapped onto a C# data type:

      case col.data_type
        when 'NVARCHAR2' then
          csdatatype := 'string';
        when 'VARCHAR2' then
          csdatatype := 'string';
        when 'NUMBER' then
          if col.column_name = 'OID' then
            csdatatype := 'long';
            csaccessor := '{ get; private set; }';
          elsif col.column_name like '%\_OID' escape '\' 
              and col.table_name is not null then
            csdatatype := replace(initcap(col.table_name), '_', '');
            csname := replace(initcap(replace(col.column_name, '_OID', '')), '_', '');
          elsif col.data_precision is null then
            csdatatype := 'long';
          elsif col.data_precision < 10 then
            csdatatype := 'int';
          else
            csdatatype := 'long';
          end if;

        else
          csdatatype := col.data_type;

      end case;

      dbms_output.put_line('    public virtual ' || 
        csdatatype || ' ' || csname || ' ' || csaccessor);

    end loop;

    dbms_output.put_line('}');
  end loop;

end;

Restrictions:

  • The example only shows string and int data type conversion.
  • Primary key columns are always named OID, and foreign key columns end in _OID.
  • Table and column names are generated calling INITCAP() and then removing underscores.

Oracle Stored Procedures with NHibernate

Setting up Oracle access in NHibernate (using Fluent NHibernate) was pretty straight-forward (with the exception of some nasty error messages). (See part 1, part 2)

The last missing item so far is executing Oracle stored procedures via NHibernate.

I found this blog most useful on mapping stored procedures to queries in NHibernate. The idea is to encapsulate the stored procedure call inside a named SQL query.

In Visual Studio, the .hbm.xml files need to have a Build Action of “Embedded Resource”, and the option “Copy To Output Directory” set to “Do Not Copy”. Thus they end up inside the compiled assembly and can be retrieved by the mapper.

Since my NHibernate setup is configured using Fluent, I needed to merge the Fluent configuration with the .hbm.xml files that the stored procedure declarations require. (As far as I know, Fluent does not support stored procedure declarations)

Thanks to this blog, it’s quite simple to merge various mapping mechanisms (in this case Fluent and Hbm), and the final code to create an NHibernate SessionFactory looks like this:

public class ConfigureDAL
{
  public static ISessionFactory Configure()
  {
    var c = Fluently.Configure();
    c.Database(OracleDataClientConfiguration.Oracle10
     .ConnectionString(x => x.FromConnectionStringWithKey("default"))
     .DefaultSchema("cpa"));
    c.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ConfigureDAL>());
    c.Mappings(m => m.HbmMappings.AddFromAssemblyOf<ConfigureDAL>());
    return cfg.BuildSessionFactory();
  }
}

Let’s take a simple stored procedure as example:

create or replace procedure cpa.test_store_int2(a number, b number) is
begin
  insert into cpa.dev_int (i10, i11) values (a, b);
end;

This procedure inserts a record of two integers into a table, and returns no values. This is the .hbm.xml mapping file:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
  assembly="myassembly" namespace="myassembly.mynamespace">
  <sql-query name="TestStoreInt2" callable="true">
  <query-param name="Value1" type="int" />
  <query-param name="Value2" type="int" />
begin cpa.test_store_int2(:Value1, :Value2); end;
  </sql-query>
</hibernate-mapping>

There are several things to note:

  • The parameters must be listed in <query-param> nodes
  • Nothing is returned, so we do not need a <return> declaration
  • The stored procedure call must include the parameters using :name notation
  • The stored procedure call must be wrapped inside a BEGIN END; block

If you omit the BEGIN/END, Oracle will raise the error ORA-00900 (invalid SQL statement).

The stored procedure is executed via the IQuery.List() method:

using (var session = sessionFactory.OpenSession())
{
  IQuery sp = session.GetNamedQuery("TestStoreInt2");
  sp.SetParameter<int>("Value1", 101);
  sp.SetParameter<int>("Value2", 202);
  sp.List();
}

This pattern calls for encapsulation into a (generated) method, of course. (to be continued)

Database Command-Line Tools: MS SQL Server vs. Oracle

SQL Server provides a command-line tool called sqlcmd (2005, 2008) (deprecating osql (up to 2000), see this blog for comparison) to access a database from the command line and execute SQL scripts.

It implements a number of parameters, among them -q and -Q to pass a SQL statement to  be executed. Any results generated by the script are redirected to stdout, and can be written to file using standard redirection mechanism ( >, | ).

The equivalent in the Oracle world is sqlplus (the product name is historically written as SQL*Plus).

Sqlplus does not provide a -q parameter. It rather accepts a (.sql) file name parameter using the notation @path\to\filename.sql (The <<EOF notation to pass a SQL script directly does not work under Windows), and output formating has to be set up in that file. File output is typically configured using the SPOOL command.

I found that the minimum requirement for output formating and spooling is:

set serveroutput on format wrapped;
spool c:\path\to\output.txt
... SQL statements ...
spool off
exit

If you plan to output results using dbms_output.put_line(), you probably need to call dbms_output.enable(1000000);

Oracle-specific issues in NHibernate

After managing to get NHibernate connect to an Oracle database, the brave developer needs to cope with two specialties of Oracle databases, namely handling Boolean values, and Identity columns generated by INSERT triggers.

Boolean values

Oracle databases do not implement a Boolean data type (while PL/SQL does), as opposed to MS SQL Server’s data type Bit. Instead, the standard way is to have a CHAR(1) with allowed values “Y” and “N”:

CREATE TABLE "FOO"
(
  ...
  "IS_ACTIVE" CHAR(1 BYTE) DEFAULT 'Y' NOT NULL ENABLE,
  ...
  CONSTRAINT "CHK_FOO_IS_ACTIVE" CHECK (IS_ACTIVE IN ('Y', 'N')) ENABLE,
  ...
)

To access such a column in NHibernate in a type-safe way (i.e. C# bool), you need to map it to the YesNoType. In Fluent, this is done using .Map().CustomType<>() in the ClassMap constructor:

  Map(x => x.IsActive, "IS_ACTIVE").CustomType<YesNoType>();

Trigger-generated Identities

Oracle databases do not implement an Auto-Increment or Identity mechanism. Instead, sequences are used to provide incremental values for primary key columns.

The sequence values are either provided in the INSERT statement reading the sequence’s NEXTVAL property, or generated by a BEFORE INSERT FOR EACH ROW trigger.

If you declare a primary key column in Fluent using the Id() method, NHibernate will generate an INSERT statement like this:

INSERT INTO cpa.FOO (OID, ID, IS_ACTIVE)
VALUES (cpa.hibernate_sequence.nextval, ?, ?)
returning OID into :nhIdOutParam

implementing the first variant. The trigger-generated method is supported by specifying GeneratedBy.Custom(“trigger-identity”) (works in Fluent build 636):

Id(x => x.Oid).GeneratedBy.Custom("trigger-identity");

The trigger-identity generator will generate an INSERT statement like this:

INSERT INTO cpa.FOO (ID, DESCRIPTION) 
VALUES (?, ?) 
returning OID into :nhIdOutParam

See this blog for other generators in NHibernate.

Not Oracle-specific, but probably interesting:

Table name aliases

Table name aliases are specified using the Table(“tablename”) method in the ClassMap constructor.

Column name aliases

Column name aliases can be specified by passing a second argument to the Id(), Map(), and References() methods:

Table("FOO");
Id(x => x.Oid, "OID").GeneratedBy.Custom("trigger-identity");
Map(x => x.Id, "ID");
References(x => x.Bar, "BAR_OID");

First Steps with NHibernate, Fluent, Oracle, C#, and Visual Studio 2010 RC

A few days into a new project: C#, Oracle, ASP.Net, .Net 4, VS 2010. After creating a (very!) basic data model with a couple of tables, the first task was to check whether the intended tools (C# 4, NHibernate+Fluent, ODP.Net, Oracle 11g) can be persuaded to co-operate.

Versions of tools and libraries used:

After I had defined my classes and maps as described in Fluent’s Getting Started wiki page, the first major hurdle was to get the session factory builder to execute without exception:

public static ISessionFactory Configure()
{
  var c = Fluently.Configure();
  c.Database(OracleDataClientConfiguration.Oracle10
    .ConnectionString(x => x.FromConnectionStringWithKey("default"))
    .DefaultSchema("cpa"));
  c.Mappings(m => m.FluentMappings.AddFromAssemblyOf<ConfigureDAL>());
  return c.BuildSessionFactory();
}

Creating an NHibernate session factory with Fluent, connecting to an Oracle database via ODP.Net.

Fluent provides 3 classes for Oracle access in the FluentNHibernate.Cfg.Db namespace:

  • OracleClientConfiguration for the MS Oracle Client (System.Data.OracleClient, deprecated)
  • OracleConfiguration, obsolete and replaced by:
  • OracleDataClientConfiguration for use with ODP

The connection string is stored in app.config under the name “default”:

<connectionStrings>
  <add name="default"
    connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
      (HOST=orasrv)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)
      (SERVICE_NAME=orcl)));User Id=cpa;Password=the_password;" />
</connectionStrings>

Trying to execute the BuildSessionFactory() method raised the first exception:

The element ‘class’ in namespace ‘urn:nhibernate-mapping-2.2’ has invalid child element ‘property’ in namespace ‘urn:nhibernate-mapping-2.2’. List of possible elements expected: ‘meta, subselect, cache, synchronize, comment, tuplizer, id, composite-id’

I tried to step through the source code, and noticed that the downloaded source code version did not match the installed 1.0 RTM of Fluent. So I updated to build 636, which solved this problem, but raised a new one:

The IDbCommand and IDbConnection implementation in the assembly Oracle.DataAccess could not be found.
Ensure that the assembly Oracle.DataAccess is located in the application directory or in the Global Assembly Cache.
If the assembly is in the GAC, use <qualifyAssembly/> element in the application configuration file to specify the full name of the assembly.

Thanks to the interweb I found this blog which provided the solution: Force .Net to load a specific version of ODP (2.111.7.20 in my case), instead of looking for any version:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <qualifyAssembly partialName="Oracle.DataAccess"
      fullName="Oracle.DataAccess, Version=2.111.7.20, Culture=neutral,
                PublicKeyToken=89b483f429c47342"/>
  </assemblyBinding>
</runtime>

Start the Visual Studio prompt and type

gacutil /l | find "Oracle"

to find out which version of ODP you have installed. The assemblies come in a version for .Net 1 and for .Net 2.

This finally caused the BuildSessionFactory to execute successfully, let me create a session and query a table in the database.

I had feared that ODP would not work using the .Net 4 profile, and had thus switched to .Net 2.0 and .Net 3.5 to debug. However, you don’t have Linq in .Net 2, and the Oracle assembly cannot be referenced by projects targeting .Net 3.5 Client Profile and .Net 4.0 Client Profile. Adding the qualified assembly worked in .Net 4, after all.

Full version of Forms Tool Kit

A couple of years ago I developed a program called Forms Toolkit which was designed to be a more comfortable tool than Oracle Forms Developer for batch manipulation and batch search of .fmb source files.

Even though the application was never marketed in big style, the evaluation version seems to be available from several download sites, and has been downloaded from my site alone nearly 200 times during the last year.

The main features of Forms Toolkit include:

Searching, Replacing, and Setting Property Values

  • Search:   Search for exact value or value range
  • Replace:   Replace search results by fixed or calculated value
  • Set:   Assign fixed value

Adding and Deleting Objects

  • Add:   Copy and reference (subclass) objects
  • Delete:   Delete matching objects

Filtering Objects

  • Restrict the objects to be search and modified by defining filter criteria:
  • Object type, object name
  • Property values
    of the object or the parent object

More Functionality

  • Define projects containing all Forms files of an application
  • Save the definition of a search or replace operation to file
  • Combine several single operations as a batch operation
  • Object and property browser
  • Extract quoted texts and display text properties
  • Detailed protocol
  • Files supported by Forms Open API: fmb, pll, mmb, olb

Forms Toolkit supports Oracle Forms Developer versions 6i, 9i, and 10g.

oraddlscript 0.16

Version 0.16 of oraddlscript includes two fixes:

  • Database Links are now scripted
  • Reduced memory consumption

Scripting database links required translating the OBJECT_TYPE “DATABASE LINK” into the GET_DDL() parameter value “DB_LINK”.

Increased memory consumption was caused by a DataReader that was not closed.

Thanks to Thomas for pointing out the problems.

The latest version of oraddlscript is available for download.

Integrated Database Versioning and Documentation with dbscript 1.01

I have written about the planned features for dbscript 1.01 in recent posts, and the new version was released yesterday.

This is the updated table of Documentation Generators in dbscript 1.01:

Generated Documentation

MS SQL Server Oracle PostgreSql
dbscript HTML view
dbscript Single HTML view
HTML (single file) view view view
MediaWiki view view view
ScrewTurn Wiki (V2) view view view
ScrewTurn Wiki (V3) view view view
Word HTML view

The Documentation Generators in the first two lines are part of the dbscript web application (see demo links above).

This means that a developer can import a database schema (or upload a SQL DDL script) which will be stored as a version of a schema or database, define a Documentation Generator, and immediately view all information on this schema inside dbscript without requiring a third party application.

Developers are free to adjust the shipped XSLT style sheets according to their layout and content needs.

Documentation Contents

All XSLTs have been cleaned up to include information about table constraints and indexes, view indexes and triggers, and database triggers.

The information about table indexes is now displayed in separate tables, rather than inside the columns table.

Documentation also includes descriptions of database objects and columns.

Generic Projects

Users can create generic (i.e. database-independent) projects, if they just require the Versioning, UpdateNotifications and Installations features.

Other features

  • Administrators can now delete a whole project with all its information in one go.
  • Acknowledgements page
  • Creating a Documentation Generator adds default Generator Parts for easier setup

dbscript is available for download here.