Monthly Archives: May 2014

Stripping mecurial of a bad commit

Scenario goes like this:

Assume you (me really) have a bitbucket repository containing some source code. You also have the luxury of two machines to develop with. Further assume that at this given point in time, both development machines are synchronized with your online repository.

Earlier in the day just before your first cup of morning coffee, your crank out some code and performed an Hg Commit pushing these changes to your online repository. Gave a big pat on the back for this accomplishment and went on to do something else. Later in the day, you decide to use the other development machine, crank out some code again, and attempt another Hg Commit. This time, TortoiseHg declines your push request saying something along the lines of:

“push will create a new head: XXXXX did you forget to update and merge?”

Duh, yes, I did. Veterans of DVCS will have probably stopped right here to reverse the damage but since you are not yet such a veteran, you try to hack your way out.

Not thinking clearly, you quickly do an update from your online repository, completely oblivious to the fact that you had just attempted an unsuccessful commit. Mercurial pulls the changes from remote and when you attempt a merge, it complains again saying something along the lines of

“Cannot update to local repository with uncommitted changes”

Huh? But I did not commit anything dammit, you scream in frustration. Does not help, that you are doing this at about 12:30 am way beyond normal bedtime.

Fact of the matter is, yes you did indeed commit but it was against your local repository. The changes were just not pushed to your remote repository. So now you find yourself looking at a chicken or egg problem, which in this context is defined as follows:

Changesets pulled in from your remote repository cannot be merge with the local repository if the latter contains uncommitted changes (or changes that have not been pushed to the remote repository). Also, the local commits cannot be pushed to the remote repository if changes pulled from the remote have not been merged to the local repository, that contains uncommitted changed.

I do not know if this is the case with GitHub, but this is what I have experienced with Mercurial.

This is typically a good time to stop and go grab another cup of coffee, a smoke or walk, or whatever to clear those brain cells. currently committed to this problem.  Assume you do just this.

You come back with a clearer head, read some more about DVCS, and then decide to tackle the problem with the following tasks:

  1. Eliminate the local commits.
  2. Complete the remote update.
  3. Re-apply the local commits.

So, what does this procedure really entail?  Will it even work?

Using TortoiseHg 2.7 on Windows, this is the work breakdown of the above tasks.

  1. Create a backup copy of your current local repository. This is important!
  2. Launched TortoiseHg.
  3. Identify the revision to remove. This is the bad local commit. In the attached image, this is revision 6.
  4. Right click on this revision, selected Modify History -> Strip.
  5. On the Strip dialog presented, select option Discard local changes, no backup (-f/ -force).

    This is basically telling TortoiseHg to instruct Mercurial to remove any trace of this revision, including stripping out your local change.   Hence reason for step 1.
  6. After stripping this revision, your last pull should indicate it needs to be merged as shown below.
  7. Right click on this revision and select “update”, selecting option to Discard local changes, no backup (-C/ -clean), to merge your last pull. If all goes well, you should have a single branch as shown below:
  8. Copy all your changes from the backup created in step 1, to this repository, using whatever tool works best for you. Tools like Beyond Compare work well for such tasks.
  9. Ensure your solution builds, compiles and runs, then commit these changes to your local repository and push them to the remote.
  10. Ensure that you do not have hanging branches as shown below.

    If this is the case,
  11. Go give your self a pat on the back, adding this to the list of things learned today.

There must be a way to prevent this in the future.  Before commit local changes, does Tortoise Hg have a way to pull and update then merge before committing?  Sounds like a crazy expectation.


Windows Phone: Value does not fall within expected range

Value does not fall within expected range” is one of those strange errors you could run into when developing a Windows Phone 8 application, that can mean anything.

In this case, the error was being thrown when a UIElement that was already part of the visual tree was being added again to the visual tree. One of the XAML views had this line of code:

<ContentControl Name="MapContentControl" Content="{Binding MainMapView, Mode=OneTime}">

The ViewModel serving as DataContext for this view, exposed a property called MainMapView as follows:

public object MainMapView
   get { return MapViewController.CreateMainMapView().GetMapObject(); }

Thing is the MapViewController’s CreateMainMapView creates and caches an instance of a view which derives from UIElement.  When the page containing this map view is unloaded, it does not appear as though our MapView is removed from the Visual Tree, since reloading the page causes the aforementioned exception.  Changing MapViewController’CreateMainMapView to return a new instance each time is one way to solve the problem.

Another way is to simply set Content of MapContentControl to null when view unloads.  I chose the former strategy but it required a little bit of re-architecture on how map loads layers.

TSQL script to drop foreign keys, re-create table and restore keys

I have run into several situations during database modeling where I had to drop and recreate an existing table.  But the process involved dropping foreign key relationships to this table, creating the table and then re-creating the foreign key relations.  The process felt very manual with no added value, so I decided to do something about it.

First some context.

Assume two tables: “Addresses” and “Zones” created with the following script:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Addresses]') AND type in (N'U'))
DROP TABLE[dbo].[Addresses]

CREATE TABLE [dbo].[Addresses](
[Id] int IDENTITY(1,1) NOT NULL,
[Name] varchar(50),
[UnitNumber] varchar(10),
[Street] varchar(100),
[City] varchar(100),
[Province] varchar(100),
[PostalCode] varchar(10),
[Country] varchar(50),
[ZoneId] int,
[AddedOn] datetime,
[LastModifiedOn] datetime,
[IsActive] [bit] NOT NULL DEFAULT 1,


IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Zones]') AND type in (N'U'))
DROP TABLE[dbo].[Zones]

CREATE TABLE [dbo].[Zones](
 [Id] int IDENTITY(1,1) NOT NULL,
 [Name] varchar(50),
 [CountryCode] varchar(5), -- The ISO 3166 CountryCode
 [Geometry] geometry,
 [OffPeakStartHour] int,
 [OffPeakEndHour] int,
 [OnPeakStartHour] int,
 [OnPeakEndHour] int,
 [MidPeakStartHour] int,
 [MidPeakEndHour] int,
 [MonthStart] int,
 [MonthEnd] int,
 [TimeZone] int,
 [AddedOn] datetime,
 [LastModifiedOn] datetime,
 [IsActive] [bit] NOT NULL DEFAULT 1,

Once created tables, the parent Zones table cannot simply be dropped unless all associated foreign key associations have been dropped.  If not, you get an error similar to this:

Msg 3726, Level 16, State 1, Line 7
Could not drop object 'dbo.Zones' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 9
There is already an object named 'Zones' in the database.

from Microsoft SQL Server Studio Management (SSMS).

The error does not tell us specifically which tables currently holds these FOREIGN KEY constraints.  Now, if there was just one of such constraints, as in this example, it is simple enough to find the child Addresses table, drop its foreign key relationship to the parent Zones table, re-create the Zones table, then re-create the keys, using regular means.  Of course this assumes you know that the Addresses table is the only with a child relationship to Zones.

If there are several of such relationships to the Zones table, however, you have to drop each one, create table, then recreate the relationships.  And if your model changes regularly (which it probably will during original system design), this will be a fairly regular process.

Personally, I shy away from manual labor, especially, if it involves repetitive and manual process which teaches me nothing new.   So for this specific problem, I created this simple TSQL sript that will find and drop all foreign key relationships to a table, create the table based on another TSQL script then recreate the keys again.   It goes like this:

if OBJECT_ID ( 'dbo.sp_recreate_table_using_script', 'P' ) is not null
 drop procedure dbo.sp_recreate_table_using_script;

create procedure dbo.sp_recreate_table_using_script
 @create_script_name varchar(200),
 @tablename_to_create varchar(100)
 declare @foreign_key varchar(100),
 @child_table_name nvarchar(100),
 @child_column_name varchar(100),
 @parent_table_name varchar(100),
 @parent_column_name varchar(100),
 @drop_keys_query varchar(max),
 @recreate_keys_query varchar(max)

declare foreign_key_cursor cursor for

-- Gets all foreign keys to referenced table
with [CTE_FoeignKeys]
 as (
 select as ForeignKey
 , OBJECT_NAME(f.parent_object_id) AS ChildTableName
 , COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ChildColumnName
 , OBJECT_NAME (f.referenced_object_id) AS ParentTableName
 , COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ParentColumnName
 sys.foreign_keys as f
 INNER JOIN sys.foreign_key_columns as fc on f.OBJECT_ID = fc.constraint_object_id

 where OBJECT_NAME(f.referenced_object_id) = @tablename_to_create

 select * from [CTE_FoeignKeys]

 set @drop_keys_query = '';
 set @recreate_keys_query = '';

 open foreign_key_cursor
 fetch next from foreign_key_cursor into

 while @@FETCH_STATUS = 0
 print '... Processing foreign key: ' + @foreign_key

 set @drop_keys_query = @drop_keys_query + ' alter table [dbo].[' + @child_table_name + ']' + ' drop constraint ' + @foreign_key + ';';
 set @recreate_keys_query = @recreate_keys_query +
 ' alter table [dbo].[' + @child_table_name + '] with check add constraint [' + @foreign_key + '] foreign key ([' + @child_column_name + '])' +
 ' references [dbo].[' + @parent_table_name + '] ([' + @parent_column_name +']) on delete cascade; ' +
 ' alter table [dbo].[' + @child_table_name + '] check constraint [' + @foreign_key +'];'; 

 fetch next from foreign_key_cursor into

 print '... releasing cursor resources... '
 close foreign_key_cursor;
 deallocate foreign_key_cursor;

print 'Executing ' + @drop_keys_query

print 'Executing ' + @create_script_name
set @create_script_name = 'SQLCMD -S [SQLServerInstance]-d [DatabaseName]-E -i ' + @create_script_name
exec xp_cmdshell @create_script_name 

print 'Executing ' + @recreate_keys_query


It works as follows:

  1. Look for all foreign key constraints targeting the parent table. Credit goes to this StackOverflow posting for the snippet.
  2. Temporarily drop all the foreign key relationships.
  3. Run the script at provided location to re-create our target table.
  4. Recreate all foreign keys that were dropped in the second step.

This script uses system Stored Procedure Xp_CmdShell which needs to be enabled.

After enabling “Xp_CmdShell” and adding the above Stored Procedure to the database, it can invoke it like so:

declare @create_script_location varchar(100), @table_name varchar(100)
set @create_script_location = 'C:\dev\data\scripts\create-zones-table.sql' ;
set @table_name = 'Zones';

exec sp_recreate_table_using_script @create_script_location, @table_name

Happy Coding.