Workaround for HQL SELECT TOP 1 in a subquery

HQL doesn’t seem to support clauses like “SELECT TOP N…”, which can cause headaches when for example you need to get the data for the newest record from a table. One way to resolve this would be to do something like “SELECT * FROM X WHERE ID in (SELECT ID FROM X WHERE Date IN (SELECT MAX(Date) FROM X))”, a doubly nested query which looks complicated even in this simple example and gets out of control when query conditions need to be more complex.

What is the alternative? Use EXISTS – as in “a newer record doesn’t exist”. It still looks a bit ugly but at least it’s manageable. The above query would then look like this: “SELECT * FROM X AS X1 WHERE NOT EXISTS(SELECT * FROM X AS X2 WHERE X2.Date > X1.Date)”

Note that this works only for “SELECT TOP 1”. For a greater number there doesn’t seem to be a solution at all.

How to create a temporary table that can be seen by different SqlCommands on the same connection?

The unexpected answer (that I learned the hard way) is: well, it depends on whether you have parameters on your command or not. The point being, if you execute a parameterless SqlCommand, the sql gets executed directly, the same way as if you entered it into the query analyzer. If you add a parameter, the things change in that a call to sp_execsql stored procedure gets inserted in the executed sql. The difference here is the scope: if you create a temporary table from within the sp_execsql, it's scope will be the stored procedure call and it will be dropped once the stored procedure finishes. In that case, you cannot use different commands to access it. If you execute a parameterless command, the temporary table will be connection-scoped and will be left alive for other commands to access. In that case, the other commands can have parameters because their sp_execsql call will be a child scope and will have access to parent scope's temporary table.

As to why they did it this way, I can't say I understand.

Lessons learned: migrating a complicated repository from subversion to mercurial

Migrating from SVN to Mercurial is a simple process only if the SVN repository has a straight-and-square structure - that is, there are trunk, branches and tags folders in the root and nothing else, not in its present state or ever before. If you used your SVN repository in a way that was convenient in SVN but not in Mercurial – for example, you created branches in various subdirectories, it still shouldn’t be too hard to migrate. But if you, like me, decided late in the game to create the mentioned folders in SVN and then moved an renamed your folders, you will need to invest serious time if you don’t want to lose parts of your history. You need to plot your migration very thoroughly and do a lot of test runs.

The reason for this is that Mercurial’s ConvertExtension is somewhat of a low-level tool. (In other words, although reliable it is not too bright). Browsing the internet you may get the impression that it’s an automated conversion system: it isn’t. It does fully automated migration only for straight SVN repositories, but for the rest it’s more like something to use in your migration script. It seems to do its primary purpose – converting revisions from one repository format to another – quite well but the rest of the tool is not so intelligent and it needs help. So, lesson number one: if you have a complex repository, don’t take the migration lightly.

A small disclaimer is in order: this post is not intended to be a complete step-by-step guide to migration. Rather, it’s something to fill in the blanks left by what little is available on the internet. I’ve done a complex migration and I want to do a brain dump for my future reference or for “whomeverother it may concern”.

Between the two alternatives I perceived as most promising, hgsubversion and the convert extension, i chose the latter. Hgsubversion was claimed by some to be the better tool for this job, but it was somewhat troublesome. The problem with hgsubversion was that it had a memory leak and broke easily in the middle of the conversion (note that this happened a couple of months ago: things may have changed in the meantime). The solution, they say, was to do hg pull repeatedly until it finishes. I wanted to do a hg clone with a filemap, but when the import broke I was in trouble because hg pull doesn’t accept filemaps. (It could be that the filemap was cached somewhere inside of the new repository and my worries were unfounded, I don’t really know). I may try that in the future. One other way around it would be to do a straight clone of SVN – no branches or anything – into an intermediate mercurial repository and then split that into separate final repositories. In that case, hgsubversion could be a viable solution, maybe even better than the conversion extension. I had more success with the conversion extension so this is what we’ll talk about here.

How to fix comment character encoding in TortoiseHG

I imported a couple of repositories from SVN into Mercurial and discovered that characters not present in the standard ASCII table have become mangled in the comments… Or at least they looked mangled in the console output as well as in TortoiseHG – now, the console is not that important, but how to fix this in Tortoise?

I tried searching for a solution on how to modify the import process and found nothing. Tried to add a new comment to the repository with a non-ASCII character and got a Python error (“expected string, QString found”). Some said that I should change my Windows’ default system encoding (which is English(US)), and that solved the problem but I would have liked a simpler solution, since changing the default encoding used to cause other problems in the past. I managed to find a couple of workarounds that solve the problem of console display and involve setting environment variables… Would it work for Tortoise? Actually: it does. The solution is simple: go to (this is on Windows 7) Control Panel – System – Advanced System Settings – Environment Variables, add a new user variable called HGENCODING and set it’s value to either “utf-8” or your code page (mine is “cp1250”). TortoiseHg respects this. There’s a slight difference in the two values, though, because the diff viewer doesn’t really like “utf-8”, it prefers the concrete code page. There may be other components that behave like this, so I suppose that setting the code page is the optimal solution.

Subscribe to this RSS feed