Using Oracle with Fluent NHibernate

Summary

So far I’ve written a lot of posts about Fluent NHibernate using MS SQL Server.  I wanted to demonstrate how to connect to Oracle and what the differences were between SQL and Oracle.  Getting the connection to work is probably the most difficult part.  After that, the mappings are a bit different, but not complicated.


Installing Oracle

Oracle has a free version of it’s database for development purposes.  This is an excellent strategy for a company to get it’s product into smaller companies.  One of the strongest influences on which database engine is used at a company is based on what the developers are familiar with.  Once a database server has been established, it will be there for a long time (it’s very difficult and expensive to change database servers once a system goes live).  As a developer, I like to keep my options open, so I’ve experimented with many databases in my career.  I’ve also used Oracle and SQL Server on major projects, and I converted a major project from Oracle to SQL Server.

Back to the free version of Oracle that I was talking about…  If you go to Oracle’s website and create an account, you can download the windows version of Oracle XE (Express Edition).  This is an easy to use version of Oracle that you can install on your PC and experiment with.  I’m currently working with 11g Express:


Just download the x64 (I’m assuming you are on a 64 bit machine) and install it.  Once Oracle is installed you’ll have to create your own workspace and create some tables.  Go to the “Get Started” application.  It should startup a browser and show an interface like this:


Next, you’ll need to click on “Application Express”.  This will allow you to setup a new workspace.  You’ll need to use your “SYSTEM” id and password to get to the express application (which you have to set when you install the application).  You should now see this screen:

This is where you’ll setup your workspace.  You can choose a name for your “Database Username” which is not used in the connection string for this sample.  Your Application Express Username and password is important.  You’ll need these for the NHibernate connection string.  Once you have created a workspace you will have access to a lot of tools to administer your database.  

The easiest way to create your tables is to go to the SQL workshop section under Object Browser.  There is a “Create” button that can be used to create new tables.  Click that button, and select “Table”:


Now you can create one table at a time.  For this demo, you’ll need three tables: store, product and producttype.  Start with producttype and use this:


Make sure you set the primary key on each of these tables to populate from new sequence.  This is the same as setting an identity on a primary key in MS SQL:

Then add product:

Then add store:

If you’re feeling brave, you can add in the relational integrity constraints (foreign keys) while you create your tables.  Otherwise, you can dig around for the raw queries to create such constraints.




The Oracle Database Connection

You’ll need the same 3 dlls used in any of my previous NHibernate samples: FluentNHibernate.dll, Iesi.Collections.dll and NHibernate.dll.  You can get these using the NuGet package manager (or just download my sample and grab them from the 3rdPartyDLLs directory.

I created a session factory to connect to my default Oracle.Instance.  Here’s the code:

public class OracleSessionFactory
{
  private static ISessionFactory _sessionFactory;
  private static ISessionFactory SessionFactory
  {
    get
    {
      if (_sessionFactory == null)
      {
        _sessionFactory = Fluently.Configure()
        .Database(OracleClientConfiguration.Oracle10
        .ConnectionString(“DATA SOURCE=XE;USER ID=username;PASSWORD=pass;“)
        .Driver<NHibernate.Driver.OracleClientDriver>())
        .Mappings(m => m.FluentMappings.Add<ProductTypeMap>())
        .Mappings(m => m.FluentMappings.Add<ProductMap>())
        .Mappings(m => m.FluentMappings.Add<StoreMap>())
        .ExposeConfiguration(config =>
        {
          SchemaExport schemaExport = new SchemaExport(config);
        })
        .BuildSessionFactory();
      }
      return _sessionFactory;
    }
  }
  public static ISession OpenSession()
  {
      return SessionFactory.OpenSession();
  }
}


You’ll need to modify your mappings to something like this:

public class ProductType
{
  public virtual int Id { get; set; }
  public virtual string Description { get; set; }
}

public class ProductTypeMap : ClassMap<ProductType>
{
  public ProductTypeMap()
  {
    Table(“ProductType“);
    Id(u => u.Id).GeneratedBy.Sequence(“PRODUCTTYPE_SEQ“).Not.Nullable();
    Map(u => u.Description).Length(50).Nullable();
  }
}

The “Sequence” attribute is necessary to declare that the “Id” field is a sequence field.  This is similar to the SQL Server identity declaration.

If you were to build this program in a console application, set your user name and password correct and run the program, you’ll get an error like this:


This is an annoying side affect that can be solved by setting the bitness of the project itself.  Right-click on your project and select “Properties”.  Choose “Build” and change the “Platform Target” from “Any” to “x64” (if you’re using the 64 bit version of Oracle).  Now you can successfully run your application.

From this point on, everything about Fluent NHibernate is the same.  I have included a select, insert, delete and update query in my sample application so you can see all the CRUD operations against an Oracle server.


Testing the Connection Outside Visual Studio

If you’re having difficulty getting your Oracle connection to work you can use the “Data Link Properties” window to test your connection parameters.  Create a text file on your desktop and name it “conn.udl”, then double-click on that file and you’ll see this window:


Select “Oracle Provider for OLE DB” and click the “Next >>” button.  Now fill in your connection information in this window.  Oracle uses “XE” as the default for the Data Source:


Once you click the “Test Connection” button you should get confirmation of a good connection.  Otherwise, you’ll get an error.  If you can get your settings to work in this window, then you’re one step closer to making your Oracle session work.


Sample Application

You can download this sample application and run it on your PC (after you install Oracle XE).  Be sure and set your user name and password in the SessionFactory.cs file.


FluentNHibernateOracleDemo.zip



 

SQL Server Secrets…

Summary

If you’re an old-hat with databases, this blog post will not be new to you.  I’m going to show some capabilities of SQL Server that will reveal how powerful a database server can be.  These “secrets” (OK, they’re really not secret to anybody with a browser and Google) actually apply to other data servers as well.

SQL Has a Database of Your Databases

That’s right.  You can open up the master database (under “System Databases”) and inside the “Views -> System Views” you’ll see a whole bunch of views to all the databases in the server.  You can query things like the list of tables in your database:

SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.TABLES


Both views and tables will be listed here.  If you want to know the columns in your tables you can query from this view:

SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.COLUMNS

This view will tell you which field is nullable, what the data type is, the character length, etc.

You can list all stored procedures with this query:

SELECT * FROM yourdatabasename.information_schema.routines WHERE routine_type = ‘PROCEDURE

You’ll need to use another query if you want the full text of the stored procedure:

USE [yourdatabasename]
SELECT OBJECT_DEFINITION(OBJECT_ID(‘stored_proc_name‘)) AS code

You might think you can use the ROUTINE_DEFINITION field from the previous query, but it has a limited string size and will cut off long stored procedure code.

Here is how you can find a list of primary keys, foreign keys and unique keys:

SELECT * FROM yourdatabasename.INFORMATION_SCHEMA.TABLE_CONSTRAINTS

You should see something like this:


The constraint type field will tell you which constraint you are looking at but this table will not tell you which table is connected to any foreign keys.  For that information you’ll have to run this query (the holy grail of foreign key queries):

SELECT PKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       PKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O1.SCHEMA_ID)),
       PKTABLE_NAME = CONVERT(SYSNAME,O1.NAME),
       PKCOLUMN_NAME = CONVERT(SYSNAME,C1.NAME),
       FKTABLE_QUALIFIER = CONVERT(SYSNAME,DB_NAME()),
       FKTABLE_OWNER = CONVERT(SYSNAME,SCHEMA_NAME(O2.SCHEMA_ID)),
       FKTABLE_NAME = CONVERT(SYSNAME,O2.NAME),
       FKCOLUMN_NAME = CONVERT(SYSNAME,C2.NAME),
       — Force the column to be non-nullable (see SQL BU 325751)
       –KEY_SEQ             = isnull(convert(smallint,k.constraint_column_id), sysconv(smallint,0)),

       UPDATE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsUpdateCascade‘)
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       DELETE_RULE = CONVERT(SMALLINT,CASE OBJECTPROPERTY(F.OBJECT_ID,’CnstIsDeleteCascade‘)
                                        WHEN 1 THEN 0
                                        ELSE 1
                                      END),
       FK_NAME = CONVERT(SYSNAME,OBJECT_NAME(F.OBJECT_ID)),
       PK_NAME = CONVERT(SYSNAME,I.NAME),
       DEFERRABILITY = CONVERT(SMALLINT,7)   — SQL_NOT_DEFERRABLE
FROM   SYS.ALL_OBJECTS O1,
       SYS.ALL_OBJECTS O2,
       SYS.ALL_COLUMNS C1,
       SYS.ALL_COLUMNS C2,
       SYS.FOREIGN_KEYS F
       INNER JOIN SYS.FOREIGN_KEY_COLUMNS K
         ON (K.CONSTRAINT_OBJECT_ID = F.OBJECT_ID)
       INNER JOIN SYS.INDEXES I
         ON (F.REFERENCED_OBJECT_ID = I.OBJECT_ID
             AND F.KEY_INDEX_ID = I.INDEX_ID)
WHERE  O1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND O2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.OBJECT_ID = F.REFERENCED_OBJECT_ID
       AND C2.OBJECT_ID = F.PARENT_OBJECT_ID
       AND C1.COLUMN_ID = K.REFERENCED_COLUMN_ID
       AND C2.COLUMN_ID = K.PARENT_COLUMN_ID


which can be found on this blog post: Identify all of your foreign keys in a SQL Server database. 

This query can be used to generate foreign keys through code.  The code necessary to create a foreign key looks like this:

ALTER TABLE product ADD CONSTRAINT FK_ProductType FOREIGN KEY (producttype) REFERENCES producttype(Id)

So you’ll need variables for the two tables, the foreign key name and the two fields (producttype and id).

There are other views in the master database you can use to lookup your database configuration.  Just poke around and see what these views reveal.


So What’s the Purpose?

This information can be used to create a utility to compare the configuration of two databases.  For example, you might have a production database (or customer facing database) and a development database.  Many times these databases can get out of sync with each other.  A utility can be created to match tables, stored procedures, foreign keys, indexes, etc. to produce a report of differences.  By extension, a utility can be created to match one database to the other.  For instance, you might want to synchronize your stored procedures.  Or you might want to create a master template database and use it to generate indexes and foreign keys on your production and development databases.

I’ve used this information to create my ORM mapping code for Fluent NHibernate.  You could create another format to store your database configuration (i.e. store your tables, indexes and foreign keys in an excel spreadsheet, or xml or json, etc.).  This would be your master template that you can use to generate multiple databases.  In turn, this generating capability can be used in an installer to generate a clean start-up database that you can then pre-populate with your static information.

As I mentioned earlier, this is not limited to SQL Server.  You can store your database template in a format that is independent of the database you want to generate, then create multiple programs to generate the database type you want to use (like an Oracle database generator using the same tables, indexes and foreign key constraints).

One other application is to store your database configuration information in a format that can be easily copied and changed (i.e. like excel or text file format).  Then you can keep multiple versions of your database handy.  This can be linked with your software version so you can specify which database is needed with your latest software and a verification of your data structures can be executed by your deployment/install program before the software is upgraded.



 

 

More Database Basics

So I just completed a post about database basics and at the end I showed how students (and faculty members) can download Oracle or MS SQL Server, install it on their PC and create a database to match the book they are using in database class.  Then I thought about the fact that I haven’t really used Oracle since 2008 and maybe, just maybe I’m out of practice.  There’s nothing worse than creating a post containing untested code or information that might not be valid, so I went out and downloaded Oracle 11g XE (express).  First, I noticed that there was only a windows x32 version and a warning that it doesn’t work on x64.  Being a skillful developer, I ignored that warning and tried it for myself.  There was some sort of error during the installation but it was more of a warning that something was missing.  This did not affect the capabilities of the installed program from what I’ve used already.  I’m sure it involves stored procedures or maybe some capability that I’m not going to use for an entry level class.  So I dragged out my “Database Systems: principles, design, & implementation” book from the early 1800’s (OK, it’s copyrighted in 1990).

Here’s the starting sample database in the book:

and here’s the matching tables with data:


So, I installed Oracle (which took a very long time), then I jumped right into the control panel, which is all new.  In fact, it’s a web interface now instead of a java application.  No problem, I clicked around the menu options until I saw a table designer interface (I’ll leave it up to the student to read up and figure out how to create a table, it’s easy).  So I created all three tables, two foreign key constraints, some not-null constraints and primary keys for the student and class tables.  Yes, I was cringing as I typed the data in and my mind was just screaming (that should be broken out in a separate table!).  But that’s the purpose of this exercise in the book is to start with a somewhat functional but incorrectly designed database and work through the normalization rules to make this into a properly designed database.

So here are the three final tables and their data in Oracle:

Student Table
 
Class Table
Enrollment Table

As you can see, learning Oracle is like riding a bicycle.  Technically, all databases function similarly.  They all have tables, fields, foreign key constraints, etc.  There are some nuances in the way different databases handle cascade deletes and triggers and other capabilities, but the basic functions are nearly identical.

Any student should be able to download the express edition of Oracle and create the three tables above in an afternoon.  It only took me two hours (one and a half of that was install time).  Warning: If you’re an MS SQL Server user like me, try not to hit the F5 key to execute your query in Oracle.  It just refreshes the browser and erases the query you typed in.

Now you should be able to join the student table to the enrollment table to get a list of students and their grades:

Student Grades

 
Now this is learning!

 

Database Basics or The School of Hard Knocks

This blog post is directed to students who are in college taking a database class for computer science or those who are just starting out in database design.  I’m going to give a little background story of my own early experience in database systems and I’m going to talk about how you can get beyond the eye-glazing boredom of learning databases and get into the hands-on world of actually learning about databases.

Some History

I took a database class at the University of Michigan.  I needed about 33 credits of 300 and above classes to complete my BS in Computer Science, so I filled those credits primarily with computer science classes.  As many as I could take.  Most of the specialized classes I took, like modeling and simulation, I blew through without studying.  Technically, I was hungry for information on simulation techniques and it was a fun class for me to take.  When I arrived at database class, our instructor was working from class notes that he developed from the main-frame era (which really wasn’t that long ago from when I took that class, but it was still a bit outdated).  He spent a lot of time on the theory of database design, foreign keys, candidate keys, First Normal Form, etc.  I understood what he was talking about as he was talking in class, but I really had to make up some flash cards for each concept and practice memorizing them to pass the test.  I assumed that databases were just not my thing.  Boy was I wrong!

So MS Access, version 1.0, arrived right around the time I graduated from college and I had a friend that wanted a database built for his business.  It was relatively easy to build, until I discovered that some things didn’t quite work right.  What compounded my problems was the bugginess of version 1.  Still, I assumed that databases were just not my thing.  When I arrived at Pirelli, I was tasked with building a real-time data collection system.  My database was again MS Access.  This was due to restrictions put on me by Pirelli’s IT department, not my choice.  By then, MS Access was up to version 3 and it was a more mature product.  Still, I made the decision up front to avoid using most of Access and just use the tables for storage and use VB version 5 to read and write to the tables.  This database was rather simple since it only collected basic information from the tire building machines in the factory.  The difficult part of that project was keeping up with real-time data.  If I had my choice today, with my current level of experience, I would insist on Oracle or MS SQL server and I might have even suggested experimenting with MapReduce (though, I don’t think they had that much data to store).

So I was recruited into Building Technology Associates by a former professor that just happened to be working as the IT manager at that company.  BTA had a problem.  OK, they had a few problems.  First, they consisted of 3 companies in the same building and two of the three companies had their own database development team that operating independent of each other.  Their second and most critical problem was that nobody in either team had any formal database knowledge when they built either database and both database were built on the premise that a database is just a collection of spreadsheets in one location.  Arrrgghh!! 

This is where I really learned how to properly design a database.  This is where reality met up with database theory.  Let me describe what I had to start with:

Database 1:
– Built with MS SQL version 6.5 on a PC running Windows NT 3, unpatched.
– Many foreign key fields were nullable and contained text.
– One foreign key field contained letters, each letter represented a different record from a lookup table (called subsystems) and the programmers used substring in their program to figure out which subsystem applied to each record.
– No relational integrity constraints.

Database 2:
– Built with MS FoxPro 5.0 hosted on the file server.
– Some tables contained old foreign keys that consisted of a unique number, but nullable fields.
– Some tables contained a new foreign key that was based on the millisecond clock and contained a string of characters that could contain an ASCII value between 0 to 255.  Also, nullable.
– No relational integrity constraints.


What I described above was just the beginning of my problems, but sometimes when things are really bad you have to Triage.  So the most important problem to fix first was the foreign keys and the relational integrity.  At first, I wanted to refactor everything (this was before the word “refactor” became as common as it is today).  Unfortunately, things were so bad that we had people dedicated to fixing data issues that was caused by the above database integrity problems coupled with bugs in the software. 

So I set into place a plan to build a new database in Oracle.  The first task I needed to do was determine how the primary keys would be produced.  I needed to make sure that all tables had a primary key and they had to be unique.  The problem was that we had a lot of external software that generated records that would be imported and renumbering keys was a nightmare.  So I invented a scheme involving a 12 character key that consisted of a 4 character unique prefix, issued by the master system and an 8 character locally generated key.  Each piece of software, when first installed would be missing the registry entry that contained the prefix.  So the software would ask for a prefix disk (floppies back then), and we distributed prefix disks with 10 prefixes on each.  We used 0-9, A-Z and the underscore as legal characters in this key scheme (uppercase only).  I had to analyze if we would run out of keys and when would we run out if data was generated continuously 24/7/365.  Since a digit consists of 37 possible characters and there are 8 digits, that gave us 37^8 number of combinations for each user (that’s 3.5 x 10^12 keys total).  If you generate 100 keys every second of every minute of every hour of every day each year, you’ll use up 3.7×10^10 keys.  Which comes out to about 92 years before burning up all the keys and requiring to get a new prefix.  So it’s safe to say that we’re not going to run out of keys soon.  Anyway, I check every few years to see who’s account has burned up the most keys (all the prefixes and keys are stored in the database for web users) and keys are not being used up anywhere near that speed.  My fallback plan is to expand the key size to 32 digits, but that’s only if data increases in speed.

Database 2 had a clever scheme by generating keys using the millisecond timer and just converting to an 8 character key.  Unfortunately, by the time I came on the scene, computers were becoming fast enough to generate keys faster than 1 millisecond.  So importing data that required new keys was causing duplicate keys to be generated.  Also, it was impossible to type a particular key into a query because many of the characters were unprintable. 

My second strict rule was NO NULLABLE FOREIGN KEYS!  Wow.  That caused a couple of ugly data problems to occur.  The person who designed database 2 was still working for the company when I was investigating these databases and I asked him why he didn’t use the integrity constraint capabilities available to FoxPro.  He claimed (I’m not kidding) that he tried it once but it just broke his software, so he deleted all the constraints that he set up.  AHHHHH!!!!!  So my third strict rule was to put the constraints in place and fix the software.  Much of the software had to be re-written to make it work with the new database, but we managed to make some of it work with the new database.  Then we created a converter to convert the old databases into the new Oracle database.  Oh, that was fun.  My determination paid dividends, because after we rolled out the new database, over half of our software problems vanished and our database integrity problems went to near zero.

So How Do you Learn the Lessons Before Meeting a Disaster?

If you are in database class, download MS SQL server express (it’s free) from here.  Install it, and play around with it.  Create some tables by hand.  Setup foreign keys.  Follow your book.  If you’re a professor teaching database class, create some sample problems containing violations of various normalization rules.

When you’re satisfied with your ability to perform basic database tasks with MS SQL, I would recommend putting in the extra effort to learn Oracle.  Once you learn one database, any other database is just a little different from the one you learned.  It’s best to see it up close a personal.  Click here to download the Oracle express version (free for developers).  There is a windows version that you can install that gets you up and running without installing a server.  If you’re familiar with Linux and you happen to have a Linux machine setup already, you can experiment with the Linux version of Oracle and learn all the nuances of connecting to an Oracle server.

If you install these databases on your machine and practice with them as your professor teaches the class, you’ll learn database theory much easier than just memorizing terms out of the book.  Trust me, I know all about foreign keys, Functional Dependencies, Candidate keys and various other terms, now that I know what they really are.

You can also practice with MS Access if you bought the professional version of Office that comes with MS Access.  Or you can purchase MS Access, but I would recommend sticking with one of the most commonly used database servers that I mentioned above.  There are also other servers like NoSQL and MySQL, but they are a bit looser with their rules.  Keep to a database system that has strict rules and learn it.  Then it’s a piece of cake to use one that doesn’t have strict rules.

That’s all for now, I’ll make a note to do a follow-up post with some example MS SQL and Oracle practice databases to help you learn more about database design.