Friday, March 23, 2007

Hibernate Shards for data partitioning across databases

Google has donated Hibernate Shards - a Java 5 add-on to Hibernate that allows application driven data partitioning with your custom or their pre-build configurations. One thing that caught my eye was that when they covered partitioned Native ID generation (aka Identity), they mentioned having Database A use a range 0-200000, Database B use range 200001 - 400000, etc. I know that MySQL cluster suggests using something like this:

Database A: Starting ID 1, Increment ID by 3 (number of databases)
Database B: Starting ID 2, Increment ID by 3
Database C: Starting ID 3, Increment ID by 3

This allows a hands-off approach, and easily lets you divide ID by 3 and use the remainder to map to a "Shard". Of course, if your data changes significantly, you may have to dump and reload to add another database. One way to avoid a dump and reload is to pick an increment that is larger than your actual database count, and just drop an extra database in an the available Starting ID. This can eat up your "keyspace" faster, but if you don't have a huge amount of data, you don't need partitioning that bad anyway.

If you have been reading my Blog, you know that I use NHibernate, but I am confident that the techniques that they are using are portable to .Net. I can see some value in using this for year-based partitioning, where archives are made available as read-only data, and the new database is created with the next available ID.

Labels: , ,

Friday, March 16, 2007

HTML Help on .Net

As I have said before, I am developing with Visual Studio 2005 on .Net 2.0. This summer, I will be creating an application with integrated html help. Of course, my first inclination is to refresh my knowledge of help systems by acquiring Microsoft's HTML Help SDK. The HTML Help Workshop looks like it will do light project management, and it even has an image cropping and screen capture program called "Flash". I did not see any full downloadable examples in the MSDN Help SDK area, but they had a link to the Help Wiki. If you browse through the wiki, you will find some good (but dated) help info about C#. DotNetJunkies has a nice step-by-step c# example for integrating help after you have a compiled help file. I am thankful that my application uses .Net 2.0, as .Net 1.x did not have much support for help systems. The .Net 2.0 HelpProvider seems like it will be very useful (here is a tutorial.

HTML Help Tidbit: Your help system can be thought of as a self-contained static web site, with Default.htm as your home page.

Labels: , , ,

Friday, March 09, 2007

ActiveRecord support for MultiQuery

I have submitted a patch to add MultiQuery support to ActiveRecord. The link is at http://support.castleproject.org/browse/AR-139. I added ActiveRecordMultiQuery.cs and updated ActiveRecordBaseQuery.cs to allow subclasses to be aggregated (added) to the MultiQuery. I did this to speed up initialization of a form (I know ... premature optimization is the root of all evil). If you like this patch, log in and vote for it!

Update: The patch has been applied. In the mean time, let me caution you that ActiveRecordCriteriaQuery from the svn repository (which I also wrote and contributed) and any other "query" that uses criteria will not correctly participate in a multi-query. But if we give the NHibernate team some time, I'm sure this will be resolved.

Regarding the implementation, the only thing that I really wondered about was the "root type". This basically selects the session to be created. So all queries in the multi-query need to be registered with the same database.

Why did I do this? Because I would like all of my application code (after various patches are accepted) to be relatively free of NHibernate references. If my application only refers to ActiveRecord instances, the implementors will be free to switch to any other O/R persistence engine (not that I want that!), and also my code will be more resilient to NHibernate upgrades. At the moment, this is impossible, because I use Criteria, DetachedCriteria, and various other NHibernate expressions. I have considered creating an ARSupport namespace to wrap NHibernate Criteria, and add Find, FindAll, FindFirst, FindOne, Count and Exists. One alternative would be to leverage the IQueryModifier interface, but I can't really see this working, since it is only used to set values for named or positional parameters. I am just TOO hooked on Criteria. Anyway, I do not want to have references to ISession, IQuery, Configuration, etc. sprinkled throughout my code.

As a side note, it looks like NHibernate is trying to consolidate their interfaces. I very much approve of this. An example is that ISession.CreateSQLQuery has 3 methods, but 2 are tagged as obsolete with a message explaining correct usage.


ActiveRecordBaseQuery

public abstract class ActiveRecordBaseQuery ...
...
/// <summary>
/// Add this query to a multiquery
/// </summary>
/// <param name="session">an <c>ISession</c> shared by all queries in the multiquery</param>
/// <param name="multiquery">the <c>IMultiQuery</c> that will receive the newly created query</param>
internal void AddQuery(ISession session, IMultiQuery multiquery)
{
IQuery query = CreateQuery(session);
multiquery.Add(query);
}



ActiveRecordMultiQuery

// Copyright 2004-2007 Castle Project - http://www.castleproject.org/
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

namespace Castle.ActiveRecord.Queries
{
using System;
using System.Collections;
#if DOTNET2
using System.Collections.Generic;
#endif

using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using Castle.ActiveRecord.Framework.Config;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Expression;

/// <summary>
/// wrapper for an IMultiQuery that executes a collection of queries.
/// </summary>
public class ActiveRecordMultiQuery : IActiveRecordQuery
{
Type _rootType;
#if DOTNET2
List<ActiveRecordBaseQuery> _queryList = new List<ActiveRecordBaseQuery>();
#else
ArrayList _queryList = new ArrayList();
#endif

/// <summary>
/// Initializes a new instance of the <see cref="ActiveRecordMultiQuery"/> class.
/// </summary>
/// <param name="RootType">the root type for all of the queries that will be included in the <c>IMultiQuery</c></param>
public ActiveRecordMultiQuery(Type RootType)
{
_rootType = RootType;
}

/// <summary>
/// Initializes a new instance of the <see cref="ActiveRecordMultiQuery"/> class.
/// </summary>
/// <param name="RootType">the root type for all of the queries that will be included in the <c>IMultiQuery</c></param>
/// <param name="activeRecordQueries">an array of <c>IActiveRecordQuery</c></param>
public ActiveRecordMultiQuery(Type RootType, ActiveRecordBaseQuery[] activeRecordQueries)
: this(RootType)
{
_queryList.AddRange(activeRecordQueries);
}

/// <summary>
/// Add an <c>IActiveRecordQuery</c> to our <see cref="ActiveRecordMultiQuery"/>
/// </summary>
/// <param name="activeRecordQuery"><c>IActiveRecordQuery</c> to be added to the MultiQuery</param>
public void Add(ActiveRecordBaseQuery activeRecordQuery)
{
_queryList.Add(activeRecordQuery);
}

#region IActiveRecordQuery Members

/// <summary>
/// Gets the target type of this query
/// </summary>
public Type RootType
{
get { return _rootType; }
}

/// <summary>
/// Executes the specified query and return the results
/// </summary>
/// <param name="session">The session to execute the query in.</param>
/// <returns>an array of results, one for each query added</returns>
public object Execute(NHibernate.ISession session)
{
// create a multi-query
IMultiQuery multiQuery = session.CreateMultiQuery();
foreach (ActiveRecordBaseQuery arQuery in _queryList)
{
// add the executable IQuery to our multi-query
arQuery.AddQuery(session, multiQuery);
}
// execute multiquery
object resultSetArray = multiQuery.List();
return resultSetArray;
}

/// <summary>
/// (Not Implemented!)
/// Enumerates over the result of the query.
/// Note: Only use if you expect most of your values to already exist in the second level cache!
/// </summary>
public System.Collections.IEnumerable Enumerate(NHibernate.ISession session)
{
throw new Exception("The method or operation is not implemented.");
}

#endregion
}
}

Labels: , ,

Thursday, March 08, 2007

Castle ActiveRecord calling a Stored Procedure

Update: I have contributed patch AR-156 that allows full integration of Insert, Update and Delete to ActiveRecord models.

If you've been reading my blog lately, you know that I have been seriously testing the Castle ActiveRecord framework out. I really love it, but I have an existing Microsoft SQL Server database with many stored procedures in it. I have tested the ActiveRecord model out, and I am sure that I will learn enough to be able to use it for standard CRUD (create, read, update, delete aka. insert, select, update, delete) functionality. BUT ... If I really want to integrate with my existing billing
procedures, etc, I will have to be able to call stored procedures. I have taken
two approaches ...
  1. write the ARHelper.ExecuteNonQuery(targetType, dmlString) method that gets a connection for the supplied type, executes dmlString, and closes it.

  2. write the ARHelper.RegisterCustomMapping(targetType, xmlString) method that allows me to add mappings that refer to my auto-generated Castle objects. Otherwise, the sql query or procedure would have to return scalars.

Any sql-query registered with the RegisterCustomMapping method below MUST return something, according to the documentation (I use Hibernate v3 documentation alot, and check NHibernate when things do not work). For calls that do not return anything (other than a count of records affected), look at my ExecuteNonQuery method.


SAMPLE USAGE CODE
Type targetType = typeof(Status);
string customMap = "<sql-query> ...";
ARHelper.RegisterCustomMapping(targetType, customMap);
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
ISession sess = holder.CreateSession(targetType);
try
{
string sqlProc = "Test_ActivationStatus_SP";
IQuery query = sess.GetNamedQuery(sqlProc);
IList list = (IList)query.List();

foreach (object obj in list)
{
Debug.WriteLine(String.Format("{0}", obj.ToString()));
}
}
finally
{
holder.ReleaseSession(sess);
}



LIBRARY CODE
using Castle.ActiveRecord;
using Castle.ActiveRecord.Framework;
using Castle.ActiveRecord.Framework.Config;

using NHibernate;
using NHibernate.Cfg;
using NHibernate.Expression;

... namespace and class declarations go here ...

// returns number of records affected
protected internal static int ExecuteNonQuery(Type targetType, string dml)
{
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
NHibernate.ISession session = holder.CreateSession(targetType);
int rowsAffected = -1;
IDbConnection conn = null;
IDbCommand cmd = null;

try
{
conn = session.Connection;
cmd = conn.CreateCommand();
cmd.CommandText = dml;
cmd.CommandType = CommandType.Text;
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ActiveRecordException("Could not perform ExecuteNonQuery for "
+ targetType.Name, ex);
}
finally
{
if (cmd != null) cmd.Dispose();
holder.ReleaseSession(session);
}

//TODO: assert statement contains "INSERT", "UPDATE" or "EXEC"
return rowsAffected;
}

/*
* Sample Custom Mapping to call a stored procedure ...
* "<sql-query name='Test_Status_SP'>" +
* "<return class='Status'/>" +
* //"select * from dbo.LStatus" +
* "EXEC dbo.MyApp_Get_KeyValue 'dbo.LStatus', 0, 0" +
* "</sql-query>*/
public static void RegisterCustomMapping(
Type targetType,
string mapping_hbm_xml
)
{
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
Configuration config = holder.GetConfiguration(holder.GetRootType(targetType));

// auto-build <hibernate-mapping> wrapper element,
// so user does not need to maintain this
mapping_hbm_xml = "<hibernate-mapping xmlns='"
+ Configuration.MappingSchemaXMLNS + "'"
+ " assembly='" + targetType.Assembly.FullName + "'"
+ " namespace='" + targetType.Namespace + "'>\n" + mapping_hbm_xml
+ "\n</hibernate-mapping>";

config.AddXmlString(mapping_hbm_xml);
}

Labels: , ,

Tuesday, March 06, 2007

Castle ActiveRecord with DetachedCriteria

My current development environment is Visual Studio Express C# Edition (read that as free), Castle ActiveRecord's latest svn trunk(usually within a few days), and NHibernate svn trunk. As of NHibernate version 1.2.0, there is a very cool new class out there ... DetachedCriteria. This class lets you set all of your Castle relational attributes like BelongsTo, HasMany, etc. as lazy fetch, and over-ride this for searches, reports, or anytime you know ahead of time that you will be touching the related classes by calling detachedCriteria.SetFetchMode(..., FetchEnum.Eager).

As a good netizen, I have tried to contribute to NHibernate and Castle ActiveRecord even if only in the smallest of ways.

Oh yeah, I tried mapping to a SQL VIEW, and it worked GREAT! I received a comment after my last post, indicating that there is a better way, and I am sure of it, but the view guaranteed that I only have one database request for my dataset. NHibernate was wanting to re-fetch my missing associated instances. They were not there 1 millisecond ago, when we did our outer join, but NHibernate wants to make sure, so it does another 25 selects or so (this depends on how bad the data is). To be fair, NHibernate is improving every day, and in a few months, this might be wrong. The view also kept me from lying about the associated table's primary key. NHibernate seemed to want my foreign key to be the primary key, not just a unique key field.

[ActiveRecord("VW_HistorySearch", Mutable=false)]
public class HistorySearch : ActiveRecordValidationBase<HistorySearch>
{
// primary key of the master table for this search
long _h_id;

[PrimaryKey(Generator=PrimaryKeyType.Assigned)]
public long h_id
{
get { return _h_id; }
set { _h_id = value; }
}

// ... standard class implementation goes here

// Builder class, used to move from search to mutable instance
public History GetHistory()
{
// return History(...) or History.Find(_h_id);
return new History(full list of properties);
}
}

class MySearchForm
{
// +++++++++ Find Method +++++++++

// Using the view (just like I would use a table)
List<ICriterion> whereClause;

//... populate whereClause ...

DetachedCriteria dc = DetachedCriteria.For(typeof(HistorySearch));
foreach (ICriterion cn in whereClause)
{
dc.Add(cn);
}
// this method is in patch NH-973
CountQuery cq = new CountQuery(typeof(HistorySearch), dc);
int count = (int)ActiveRecordMediator.ExecuteQuery(cq);
dc.SetProjection(null);

// find activation history records
IList<HistorySearch> hList = HistorySearch.SlicedFindAll(0, limit, dc,
sortOrder.OrderBy);

Debug.WriteLine(String.Format("Records found: {0} of {1}", hList.Count, count));
}


It looks like NHibernate does not support HQL update statements in queries yet, so I am forced to decide between a series of instance updates (hopefully batched), or a raw SQL call for each modification. I wrote and tested a pure (Microsoft) SQL version that works for me.

// Change [History] Records's [Status]
// parameter 1: either IList<History> or IList
private void changeStatus(IList<History> hList, Status newStatus)
{
/* HQL would be ...
* UPDATE History h SET h.Status = :newStatus where ah.ID in (:idList)
*/
IList<Int64> hIdList = new List<Int64>(hList.Count);
for (int i = 0; i < hList.Count; i++)
{
hIdList.Add(hList[i].ID);
}

StringBuilder dml = new StringBuilder(
"UPDATE dbo.CIHistory SET Status_ID = :newStatus WHERE ID IN (:idList)");
ARHelper.AddParameter<Int32>(dml, ":newStatus", newStatus.KeyID);
ARHelper.AddParameterList<Int64>(dml, ":idList", hIdList);

int rowsAffected = ARHelper.ExecuteNonQuery(typeof(History), dml.ToString());

// THE non-SQL alternative should be this ...
// it would be nice if ahList could do an HQL update
/*
foreach (History h in hList)
{
h.Status = stat;
h.Save();
}
*/
}

// a collection of methods dependent on the ActiveRecord library
class ARHelper
{
internal static void AddParameter<T>(StringBuilder dml, string param, T value)
{
dml.Replace(param, value.ToString());
}

// only works for a list of IDs, since it does not add quotes
internal static void AddParameterList<T>(StringBuilder dml, string param, IList<T> valueList)
{
// build a comma separated list of IDs
StringBuilder values = new StringBuilder(valueList.Count * 9);
for (int i = 0; i < valueList.Count; i++)
{
if (i > 0)
{
values.Append(',');
}
values.Append(valueList[i].ToString());
}
dml.Replace(param, values.ToString());
}

#region Execute Insert or Update

// returns number of records affected
protected internal static int ExecuteNonQuery(Type targetType, string dml)
{
ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
NHibernate.ISession session = holder.CreateSession(targetType);
int rowsAffected = -1;
IDbConnection conn = null;
IDbCommand cmd = null;

try
{
conn = session.Connection;
cmd = conn.CreateCommand();
cmd.CommandText = dml;
cmd.CommandType = CommandType.Text;
rowsAffected = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw new ActiveRecordException("Could not perform ExecuteNonQuery for "
+ targetType.Name, ex);
}
finally
{
if (cmd != null) cmd.Dispose();
holder.ReleaseSession(session);
}

//TODO: assert statement contains "INSERT" or "UPDATE"
return rowsAffected;
}

#endregion

}

Labels: ,