The miscellaneous ramblings and thoughts of Dan G. Switzer, II

SVN reporting "Working copy text base is corrupt"

This morning started off to a rough start. I came in to realize the commit of a merge I made at the end of the day yesterday had failed. Whenever I would attempt to commit the merge changes, it would go through the entire process and then finally fail with a error message of:

org.tigris.subversion.javahl.ClientException: Working copy text base is corrupt

After trying a "Cleanup" and several other steps to rectify the problem, I finally did hit Google to try and find a solution. I came across chris' subversion checksum mismatch - easy workaround which offered several various solutions to the problem.

I tried several of the solutions, but wasn't haven't much success until I came across Michael Sparer's comment:

Thanks for the explanation, which shed some light on how svn manages its working copy. My problem seemed to stem from a bogus file under .svn/text-base, which didn't match the file actually on the server.
- server file (.../x.java): OK
- orig copy from server (.svn/text-base/x.java.svn-base): BOGUS (not same as server)
- checksum (in .svn/entries): matches server, but not server copy

If I can make the bogus copy match what's on the server, then it will also match the checksum, and everyone will be happy.
What I did (i'll call the working dir with the corrupt file "orig-dir":
1. Fresh checkout of svn dir matching orig-dir into /tmp/blah
2. Copy /tmp/blah/.svn/text-base/x.java.svn-base into orig-dir/.svn/text-base
3. Check in successfully

I wish svn would let you refresh a given file from the server...maybe there's a command and I just haven't found it or my svn is too old.

In the end, this ended up being my problem as well. The copy of me .svn/text-base/filename.ext.svn-base was out of sync with the actually copy on the server.

To resolve this, I checked out a clean copy from the server to a tmp folder, then I just replaced the copy in my working folder. After doing this, I was able to check in the file without incident.

I also noticed I had a copy of the template in the .svn/tmp/text-base/ folder. I made a copy of this file and then removed the file this directory before committing. I'm not sure if this step is necessary, but I wanted my local working copy to mirror as closely as possible a fresh working copy.
I could have just wiped my local copy altogether, but I wanted to know the root problem incase it every happens again. Since I have a pretty large repository it takes a while to checkout from SVN, so at least know I have an option to try if I ever run into the issue again that won't require me checking out the entire working directory structure.

Using MSSQL to output time in days, hours and minutes

I was working on some code today where I wanted to output a time span in the format 1d 4h 36m (i.e. "1 day, 4 hours and 36 minutes.") I wanted a pure SQL solution so that I didn't have to worry about formatting later. Here's some example SQL that will format a time span by days, hours and minutes.

declare @startTime datetime
set @startTime = '2009-07-11 14:19:40.000'

declare @endTime datetime
set @endTime = getUtcDate()

    convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd '
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm' as Format1
  , case
      when (((dateDiff(mi, @startTime, @endTime)/(24*60))) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
      when (((dateDiff(mi, @startTime, @endTime)%(24*60)/60)) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
    end as Format2
  , convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + ':'
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60), 2) + ':'
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60), 2) as Format3
Make sure to keep the dateDiff() time in minutes. If you switch to hours (or days) you'll have rounding issues you'll have to work around. By sticking to using minutes for the equations, you avoid the rounding issues.

When you run this query in Microsoft SQL Server, you'll see that it outputs 3 columns—Format1, Format2 and Format3.

Format1 always outputs the time span in format 0d 0h 0m, even when the days or hours are 0. In my case, I really wanted to ignore days if no days have passed and hours if we're still under 60 minutes. This lead me to create Format2.

Format2 still uses the 0d 0h 0m format, but it will drop off days if less than 24 hours old and drop hours if less than 60 minutes has passed. This leaves you strings like "6d 4h 52m", "4h 10m" or "3m". In my case, this was the best formatting for my uses.

Format3 is an alternative format that places the time span in the format d:hh:mm. While I'm not using that formatting currently, some people may find it useful. This also shows off how you can force the hours and minutes to output as a digit value (i.e. 0:02:01 = 0 days, 2 hours and 1 minute.)

Hopefully this will prove useful to some of you.

CF9 can protect SQL statements stored as string from SQL injection

One of the many new features in ColdFusion 9 is the ability to use every tag within a <cfscript /> block. Developers seem to either love or hate <cfscript /> and I've always falling into the love (well, maybe not love but I'm extremely fond of it.) I just find <cfscript /> a better method for writing business logic. So for me, the addition of being able to access any CF tag via <cfscript /> will be really nice. However, I wanted to point out one specific use case where the addition of being able to script any tag really comes in handy.

One of issue I've run into in the past with ColdFusion is that there's no good way to protect a SQL statement from SQL injections if your SQL is coming from a string variable. In most cases you're going to build dynamic SQL statements within a <cfquery /> block, occasionally I've found situations were this isn't ideal.

In these cases I've wanted to use a UDF to generate a block of SQL for me, because I can easily re-use the function to re-use common blocks of SQL.

The problem with building the statement via a UDF is that when your SQL is generated as a string, there's you can't use <cfqueryparam /> to secure your data (unless you were to save the string to disk, include it in the query and then clean up the temp file.)

This is where CF9's new functionality of being able to script any tag will become handy. ColdFusion 9 introduces a way to bind a token to a SQL parameter. This would allow you to build a SQL statement completely from a string and then bind tokens in the string to parameters.

The following example code comes from John Whish's cfquery in cfml with parameters post:

// create a query
q = new Query();

// don't need to set datasource if using this.datasource in Application.cfc 
q.setDatasource( "cfartgallery" );

// build the SQL statement 
q.setSQL( "select * from Art where artistid = :artistid and issold = :issold" );

// this is the equivalent of cfqueryparam 
q.addParam( name="artistid", value=2, cfsqltype="CF_SQL_INTEGER" );
q.addParam( name="issold", value=1, cfsqltype="CF_SQL_BIT" );

// run the query and get a query object
result = q.execute();

// dump query object
writeDump( result );

As you can see from the code, the setSQL() method takes a string of SQL to execute. You'll also notice two unique tokens in the SQL ":artistid" and ":issold". When you invoke the q.addParam() method, you're replacing the tokens with a binding SQL variable—which protects you from SQL injections.

So, the addition to the new Query() object ends up solving a problem that I've run into in the past to which there's never been a really good solution for in the past.

I've been meaning to post this since the CF9 beta became public, but work kept getting in the way. :)