nHibernate “not in” join table query

Posted by & filed under nHibernate, SQL Server.

I’ve recently been working on a project where it was a client’s requirement that we use nHibernate as the data access layer. Although I wasn’t keen at first, I actually quite like the control you have over the domain model. It also worked extremely well with WCF contracts, simply by adding DataMember decoration to the properties.

Example

Some of the queries however were a little tricky once I had the datamodel set up. Let’s take the following example. A car rental company gets some new cars delivered. These are all registered in the vehicle table in the database. Customers are also registered when they rent a vehicle, and an entry in the join table for CustomerVehicle is created. nHibernate handles all this for us using the XML mapping files that are marked as embedded resources for the project. We therefore have the following schema…

nHibernate Schema Example

So far everything looks good. The object mapping creates a Customer which has a Vehicles property which can contain one or more vehicles that the customer has rented. We can get a customer using the following…

var customers = s.CreateCriteria()
.Add(Restrictions.Eq("Surname", "Pont"))
.List();

And for example, the first vehicle make…

string firstRentalMake = customers[0].Vehicles[0].Make;

The trickiness comes when we now want to find out which vehicles haven’t ever been rented. We haven’t got our objects mapped in a way that would make this easy. After a fair bit of googling, I found I had two options.

Firstly, I could create a new set of objects that could map from the vehicles point of view. Each vehicle would have a customers property that would then allow me to query for vehicles where the number of customers is zero.

Secondly, I could map the join table (so that nHibernate knows how the join table is represented in the database) and use the following query…

DetachedCriteria rentedVehicleList = DetachedCriteria.For(typeof(CustomerVehicle)).SetProjection(Projections.Property("VehicleId"));

DetachedCriteria vehicles = DetachedCriteria.For(typeof(Vehicle)).Add(Subqueries.PropertyNotIn("Id", rentedVehicleList));

vehicleList = vehicles.GetExecutableCriteria(session).List<Vehicle>();

This effectively runs a query that gets all vehicles where the vehicleId is not in the join table.

I’m still getting to grips with the ins and outs of nHibernate, so if anyone has any alternatives to this problem I’d be glad to hear them.

Using Subversion with iPhone app development

Posted by & filed under iPhone Development.

I came across an interesting problem whilst trying to build an app with ad-hoc distribution for testing.

I use subversion for version control and as a central storage for all our source code and general documents etc. and using Versions as my tool to check out files (Versions is awesome by the way!) We were building an app on the mac that had the distribution profile installed and putting it on an iPhone. Things worked fine.

The problem came when I was then checking the .app file into the respository, checking it out on another mac and trying to install on another iPhone using iTunes. iTunes kept showing the message “An unexpected error has occurred”.

I was stumped for a good few hours assuming this was something to do with the certificate signing until I had a brainstorm – .app files are like folders and so subversion was treating them as such and placing a .svn hidden directory within it. This was screwing with iTunes and so it would refuse to install the application.

The Solution

The solution was to use .ipa files – basically a zip file with a certain structure that iTunes understands as an application. To create an ipa file do the following…

1. Create a folder called payload and place your app file in it.

2. Zip the folder

3. Rename the file with the name of your application and give it an ipa extension.

Subversion will treat these as a single file (just like it does any other zip file) and so these are left intact. Once I did this the application installed correctly on whatever machine checked the file out.

Search Everything Script :-)

Posted by & filed under SQL Server.

This is a useful script I use for searching (almost) everything in SQL Server – object names, column names, contents etc. It’s been improved on over the years, although I’m not sure of the original source. It’s been very useful though, particularly for finding table references in stored procs etc.

Hope it helps someone.

Chris

DROP TABLE #RESULTS
GO

DECLARE @SearchStr VARCHAR(500)
SET @SearchStr = 'Custom%2'
SET @SearchStr = '%' + @SearchStr + '%'

-- Table Columns
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name like @SearchStr ) Order by Name

-- Stored Procedures
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
--AND SO.Type = 'P'
AND SC.Text LIKE @SearchStr
ORDER BY SO.Name

-- Table Column Contents
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME(@SearchStr,'''')

WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END

SELECT ColumnName, ColumnValue FROM #Results

Allow startup daemons on StorCenter IX2-200 NAS (and other Soho based devices)

Posted by & filed under StorCenter IX2-200 NAS.

It’s useful once you’ve added packages to the StorCenter IX2-200 to have certain daemons start when the device starts up. I know from having a dodgy dishwasher that trips the power on certain cycles (yes I know I should have a UPS :-)) that it becomes annoying pretty fast to have to SSH onto your device to startup SABnzbd+ etc.

What became apparent is that adding services as normal by creating a script in init.d and running update-rc.d doesn’t work and the service is not persistent. From a bit of reading on the web, it seems that the StorCenter overwrites certain volumes at startup.

To set up your own services / daemons, you need to list a script in /mnt/apps/usr/local/cfg/sohoProcs.xml. The problem is that this exists in a read-only partition, so to edit this file, you need to make it writable.

DISCLAIMER!: I’ve run these commands myself with no ill affects, but I take no responsibility if you brick your device. Run at your own risk!

To make the partition writable:
mknod -m0660 /dev/loop3 b 7 3
chown root.disk /dev/loop3
mkdir /tmp/apps
mount -o loop /boot/images/apps /tmp/apps

Make a backup copy of the config:
cp /tmp/apps/usr/local/cfg/sohoProcs.xml /tmp/apps/usr/local/cfg/sohoProcs.xml.bk

Edit the config – I use nano as I’ve installed it using ipkg (yeah, I’m not hardcode 🙂 ), but substitute this for vi or similar if that’s what you use:
nano /tmp/apps/usr/local/cfg/sohoProcs.xml

I replaced the thumbnails program declaration (which was disabled) with a new script I’d added in /opt/etc/init.d/start_init_scripts.sh. I guess you could add your own <Program> declaration if you wanted.

<Program Name="thumbnails" Path="/opt/etc/init.d/start_init_scripts.sh">
<SysOption MaxMem="96M" Nice="15" Restart="-1"/>
</Program>

Unmount:
umount /tmp/apps
rm /dev/loop3

Then it’s a case of making the script do what you need. Mine starts any script in the /opt/etc/init.d/ directory that starts with “S”…
#!/bin/sh

rm /opt/var/log/boot

if [ -d /opt/etc/init.d ]; then
for f in /opt/etc/init.d/S* ; do
#[ -x $f ] && $f start
$f start | tee -a /opt/var/log/boot
done
fi

#this will start the original thumbnails
#/usr/local/thumbnails/thumbnails &

Hope this helps someone.

Cheers

Chris