In Lightroom Classic, select photos without a flag

The other day I was judging pictures in Lightroom. As usual, most of them were not as good as expected and could be thrown away. Problem: I had only flagged the ones to keep (using 'P'). 

If I had used 'X' for the ones to reject, it would have been easy to actually delete them. 'Ctrl+Backspace' removes all rejected photos. But what to do now? 

 Then I saw the solution: ordering. 

1. In the toolbar below, select ordering on 'Choice' - this is based on the flag you assigned. 
2. Then scroll to the first non-flagged picture. 
3. Shift-select until the end. 
4. Delete.

How to express an expected call on a Moq mock in the memberdata for an XUnit Theory.

In XUnit you can use the [Theory] attribute to run the same test against a set of different parameters. Moq allows you to specify expectations on which methods of a mocked interface are called, with the Verify method. Recently, I wanted to combine these two techniques to be able to convey the expectation along with the other test parameters to the XUnit Theory. It took me a little thought to get this right. The trick is in casting the expected call (usually an Action<T>) to an Expression<Action<T>>. That is done in the gist below.

Simply check logged messages in .NET Core and Moq

The Microsoft.Extensions.Logging framework in ASP.NET Core is very useful for logging, but it can be somewhat verbose to check logged messages in your unit tests. Because the methods you use for logging (LogInformation, LogWarning etc.) are all extension methods, you cannot verify calls on them. You can verify calls to the Log method. But it took me some time to find out what the exact check should be. So I wrapped that knowledge into a utility method VerifyLog, in this Gist: Happy logging!

ASP.NET Core Middleware pipeline 'Status code cannot be set, response has already started.'

ASP.NET Core has this beautiful concept of a pipeline of Middleware. See the introduction if you're not familiar with it.

In this tutorial you see you can always expect a 'next' to be available to invoke. But what if you have only one piece of middleware, or you are at the end of the pipeline? What is 'next' then?

The magic is in ApplicationBuilder, in the Build method:

1:      public RequestDelegate Build()  
2:      {  
3:        RequestDelegate app = context =>  
4:        {  
5:          context.Response.StatusCode = 404;  
6:          return Task.FromResult(0);  
7:        };  
8:        foreach (var component in _components.Reverse())  
9:        {  
10:          app = component(app);  
11:        }  
12:        return app;  
13:  }  

As you can see, a default RequestDelegate is appended to the end of the pipeline. It sets the StatusCode to 404 (meaning 'not found').

Now, where does the error in the title come from? It happens when you write to the httpContext.Response, and call next.Invoke(context). If none of the middleware components short-circuits the pipeline, eventually the 404 RequestDelegate above will be called. And that is where the problem starts: because you already started a response (leading to Response.HasStarted = true), you are not allowed to set the StatusCode anymore.

And what is the solution?

General rule of thumb: if you write to the Response in middleware X, end the pipeline (don't call next).

If you think about it this makes sense: X apparently knows the right response, so the request can be considered handled. Of course, the previous middleware components still get called on the way back through the pipeline. They also should not try to set the StatusCode.

Should you set the StatusCode yourself?

Not when the response is OK, because that is the default value of StatusCode. If your middleware decides that another response is appropriate, it should:

• first set the StatusCode to an appropriate value (for example 201, Created)
• then write to the Response
• end the pipeline (don't call next)

Advanced pattern matching in SQL Server

This week I had the privilege of extracting times from a varchar column. This had to be done in T-SQL, which as you probably know does not support regular expressions. So I could only use PATINDEX, which is a lousy replacement for real regexes. Nevertheless, I found a solution that you may find useful.

The code is below, I will start with an explanation of the idea.

The problem at hand:

1. A varchar column that contains a start time and end time, separated by a dash or a space.
2. Times can be at the start, the middle or the end.
3. The text may contain no time, just a start time, just an end time or both.
4. Time can be denoted with three or four digits, with or without a leading zero for times before 10am.
5. Time can be denoted with or without a dot as separator between hours and minutes.
6. The dash may be surrounded by spaces, either before, after or both.

This is a lot of variation, that leads to many patterns to check. This could be done in a very ugly case statement. I don't like ugly statements so I came up with a Common Table Expression (CTE) holding all the possible patterns. This CTE contains the patterns from longest to shortest, so the first match that is found is always the most specific. Beside the pattern, the CTE contains a decription (just for human readability) and two numbers (startLenght and endLength) that help in finding the actual time piece from the start of the pattern. (PATINDEX just returns the position where the pattern is found. Nothing like multiple matching groups that you like so much about regex...)

Then I join the patterns CTE with the table that contains the text to be searched, and perform a PATINDEX for every combination. That results in zero, one or more matches per text. If there are more than one, the most specific will have row number 1. So then I filter on ROW_NUMBER() = 1.

From there is it a matter of smart substringing and converting.

I resolved the variations following from problems steps 5 and 6 by applying the REPLACE function for each of the variations. This could also have been solved by adding separate patterns. If you decide to do that, you will probably also need an extra index number for the position of the end-time in the pattern.

The CTE 'UnitTests' is what the name suggests: a list of test notes to retrieve the times from, including the correct answers.

The environment

SQL Server 11.0 SP2, aka SQL Server 2012

The code

 DECLARE @ThreeDigitTimePattern nvarchar(20) = '[0-9][0-5][0-9]';  
 DECLARE @FourDigitTimePattern nvarchar(20) = '[0-2]' + @ThreeDigitTimePattern;   
   
 WITH   
 StartEndTimePatterns AS  
 (  
     -- Patterns in order of matching length. This makes sure that the longest match is the first match.  
     SELECT patternID, startLength, endLength, pattern, [description]  
     FROM  
     (  
         VALUES   
          (1, 4, 4, '%[^0-9,-]' + @FourDigitTimePattern + '[- ]' + @FourDigitTimePattern + '[^0-9]%', 'FourDigitTime/FourDigitTime/MiddleOfSentence: blabla 1135-1345 blabla')  
   
         , (2, 3, 4, '%[^0-9,-]' + @ThreeDigitTimePattern + '[- ]' + @FourDigitTimePattern + '[^0-9]%', 'ThreeDigitTime/FourDigitTime/MiddleOfSentence: blabla 735-1145 blabla')  
   
         , (3, 4, 3, '%[^0-9,-]' + @FourDigitTimePattern + '[- ]' + @ThreeDigitTimePattern + '[^0-9]%', 'FourDigitTime/ThreeDigitTime/MiddleOfSentence: blabla 735-945 blabla')  
   
         , (4, 3, 3, '%[^0-9,-]' + @ThreeDigitTimePattern + '[- ]' + @ThreeDigitTimePattern + '[^0-9]%', 'ThreeDigitTime/ThreeDigitTime/MiddleOfSentence: blabla 735-945 blabla')  
   
         , (5, 4, 0, '%[^0-9,-]' + @FourDigitTimePattern + '[^0-9]%', 'FourDigitTime/null/MiddleOfSentence: blabla 1135 blabla')  
   
         , (6, 3, 0, '%[^0-9,-]' + @ThreeDigitTimePattern + '[^0-9]%', 'ThreeDigitTime/null/MiddleOfSentence: blabla 735 blabla')  
   
         , (7, 0, 3, '%-' + @ThreeDigitTimePattern + '[^0-9]%', 'null/ThreeDigitTime/MiddleOfSentence: blabla -735 blabla')  
   
         , (8, 0, 4, '%-' + @FourDigitTimePattern + '[^0-9]%', 'null/ThreeDigitTime/MiddleOfSentence: blabla -735 blabla')  
     ) as T(patternID, startLength, endLength, pattern, [description])  
 )  
 , UnitTests AS   
 (  
     -- A list of examples for unittesting.  
     -- First step: remove '.', and extra spaces around the dash.  
     SELECT testID, [description], '__' + Replace(Replace(Replace(NoteText, '.', ''), '- ', '-'), ' -', '-') + '__' as NormalizedNoteText, CAST(startTime_ref as time) as startTime_ref, CAST(endTime_ref as time) as endTime_ref  
     FROM  
     (  
         VALUES (1, 'ThreeDigitTime/ThreeDigitTime/WholeSentence', '735-945', '7:35', '9:45')  
         , (2, 'ThreeDigitTime/FourDigitTime/WholeSentence', '735-1145', '7:35', '11:45')  
         , (3, 'FourDigitTime/FourDigitTime/WholeSentence', '1135-1345', '11:35', '13:45')  
         , (4, 'ThreeDigitTime/ThreeDigitTime/StartOfSentence', '735-945 blabla', '7:35', '9:45')  
         , (5, 'ThreeDigitTime/FourDigitTime/StartOfSentence', '735-1145 blabla', '7:35', '11:45')  
         , (6, 'FourDigitTime/FourDigitTime/StartOfSentence', '1135-1345 blabla', '11:35', '13:45')  
         , (7, 'ThreeDigitTime/ThreeDigitTime/MiddleOfSentence', 'blabla 735-945 blabla', '7:35', '9:45')  
         , (8, 'ThreeDigitTime/FourDigitTime/MiddleOfSentence', 'blabla 735-1145 blabla', '7:35', '11:45')  
         , (9, 'FourDigitTime/FourDigitTime/MiddleOfSentence', 'blabla 1135-1345 blabla', '11:35', '13:45')  
         , (10, 'ThreeDigitTime/ThreeDigitTime/EndOfSentence', 'blabla 735-945', '7:35', '9:45')  
         , (11, 'ThreeDigitTime/FourDigitTime/EndOfSentence', 'blabla 735-1145', '7:35', '11:45')  
         , (12, 'FourDigitTime/FourDigitTime/EndOfSentence', 'blabla 1135-1345', '11:35', '13:45')  
         , (13, 'ThreeDigitTime/null/WholeSentence', '735', '7:35', null)  
         , (14, 'FourDigitTime/null/WholeSentence', '1135', '11:35', null)  
         , (15, 'ThreeDigitTime/null/StartOfSentence', '735 blabla', '7:35', null)  
         , (16, 'FourDigitTime/null/StartOfSentence', '1135 blabla', '11:35', null)  
         , (17, 'ThreeDigitTime/null/MiddleOfSentence', 'blabla 735 blabla', '7:35', null)  
         , (18, 'FourDigitTime/null/MiddleOfSentence', 'blabla 1135 blabla', '11:35', null)  
         , (17, 'ThreeDigitTime/null/EndOfSentence', 'blabla 735', '7:35', null)  
         , (18, 'FourDigitTime/null/EndOfSentence', 'blabla 1135', '11:35', null)  
         , (19, 'TooLongNumber/null/WholeSentence', '73545', null, null)  
         , (20, 'TooLongNumber/null/StartOfSentence', '73545 blabla', null, null)  
         , (21, 'TooLongNumber/null/MiddleOfSentence', 'blabla 73545 blabla', null, null)  
         , (22, 'TooLongNumber/null/EndOfSentence', 'blabla 73545', null, null)  
         , (23, 'LeadingZero/ThreeDigitTime/StartOfSentence', '0852-912 blabla', '08:52', '9:12')  
         , (24, 'LeadingZero/ThreeDigitTime/MiddleOfSentence', 'blabla 0852-912 blabla', '08:52', '9:12')  
         , (25, 'LeadingZero/ThreeDigitTime/EndOfSentence', 'blabla 0852-912', '08:52', '9:12')  
         , (26, 'Point/ThreeDigitTime/StartOfSentence', '8.52-912 blabla', '8:52', '9:12')  
         , (27, 'Point/Point/StartOfSentence', '8.52-10.12 blabla', '8:52', '10:12')  
         , (28, 'null/ThreeDigitTime/StartOfSentence', '-912 blabla', null, '9:12')  
         , (29, 'null/ThreeDigitTime/MiddleOfSentence', 'blabla -912 blabla', null, '9:12')  
         , (30, 'null/ThreeDigitTime/EndOfSentence', 'blabla -912', null, '9:12')  
         , (31, 'null/FourDigitTime/StartOfSentence', '-1012 blabla', null, '10:12')  
         , (32, 'null/FourDigitTime/MiddleOfSentence', 'blabla -1012 blabla', null, '10:12')  
         , (33, 'null/FourDigitTime/EndOfSentence', 'blabla -1012', null, '10:12')  
         , (34, 'ThreeDigitTimeWithSpace/FourDigitTime/MiddleOfSentence', 'blabla 735 -1145 blabla', '7:35', '11:45')  
         , (35, 'PointAndLeadingZero/ThreeDigitTime/StartOfSentence', '08.52-912 blabla', '8:52', '9:12')  
         , (36, 'PointAndLeadingZero/Point/StartOfSentence', '08.52-10.12 blabla', '8:52', '10:12')  
         , (37, 'Point/ThreeDigitTime/MiddleOfSentence', 'blabla 08.52-912 blabla', '8:52', '9:12')  
         , (38, 'Point/Point/MiddleOfSentence', 'blabla 08.52-10.12 blabla', '8:52', '10:12')  
         , (39, 'ThreeDigitTime/LeadingZero/StartOfSentence', '852-0912 blabla', '08:52', '9:12')  
         , (40, 'ThreeDigitTime/LeadingZero/MiddleOfSentence', 'blabla 852-0912 blabla', '08:52', '9:12')  
         , (41, 'ThreeDigitTime/LeadingZero/EndOfSentence', 'blabla 852-0912', '08:52', '9:12')  
         , (50, 'PracticeTest', 'pkno 09.20-10.00 ASA 2', '9:20', '10:00')  
         , (51, 'PracticeTest', 'PVAT 08.09-9.08/2', '8:09', '9:08')  
         , (52, 'PracticeTest', 'PVVO 08.14-8.52/1', '8:14', '8:52')  
         , (53, 'PracticeTest', 'ppch 0847-0915', '8:47', '9:15')  
         , (54, 'PracticeTest', 'pver 856-0945', '8:56', '9:45')  
         , (55, 'PracticeTest', 'PNCH 0900-0945', '9:00', '9:45')  
         , (56, 'PracticeTest', 'PKCH ASA 2 9.53-10.10%0D%0A', '9:53', '10:10')  
         , (57, 'PracticeTest', 'pgyn -1609 +', null, '16:09')  
   
     ) as T(testID, [description], NoteText, startTime_ref, endTime_ref)  
 )  
   
 , StartEndTimeIndices as   
 (  
     -- Second step: find the position where the pattern is found  
     select t.testID, t.[description] as TestDescription, NormalizedNoteText, p.patternID, p.startLength, p.endLength, PATINDEX(p.pattern, NormalizedNoteText) as patternFoundAt, startTime_ref, endTime_ref  
     from UnitTests t, StartEndTimePatterns p  
 )  
 , StartEndTimeOrderedIndices as  
 (  
     -- Third step: filter the rows where any pattern matched, add a row_number  
     select testID, TestDescription, NormalizedNoteText, patternID, startLength, endLength, patternFoundAt, startTime_ref, endTime_ref  
     , ROW_NUMBER() OVER (PARTITION BY testID ORDER BY testID, patternID asc, patternFoundAt asc) as rownumber  
     from StartEndTimeIndices  
     where patternFoundAt > 0  
 )  
 , StartEndTimeStrings as  
 (  
     -- Fourth step: use only the first matches (those are the longest); extract the start- and endTime based on their lengths  
     select testID, patternID, patternFoundAt, NormalizedNoteText,   
         startLength, IIF(startLength > 0, SUBSTRING(NormalizedNoteText, patternFoundAt + 1, startLength), null) as startTime, startTime_ref,   
         endLength, IIF(endLength > 0, SUBSTRING(NormalizedNoteText, patternFoundAt + 1 + CAST(startLength as BIT) + startLength, endLength), null) as endTime, endTime_ref  
         from StartEndTimeOrderedIndices  
     where rownumber = 1  
 )  
 , StartEndTimeIntegers as  
 (  
     -- Fifth step: cast the start- end endTime to integers if possible; makes for easy calculation of the time in the next step.  
     select testID, patternID, patternFoundAt, NormalizedNoteText,   
         startLength, IIF(ISNUMERIC(startTime) = 1, CAST(startTime as INT), null) as startTime, startTime_ref,   
         endLength, IIF(ISNUMERIC(endTime) = 1, CAST(endTime as INT), null) as endTime, endTime_ref  
         from StartEndTimeStrings  
 )  
 , StartEndTimes as  
 (  
     -- Sixth step: calculate time values from the integers.  
     select testID, patternID, patternFoundAt, NormalizedNoteText, startLength, endLength  
     , IIF(startTime is not null AND startTime/100 < 24 AND startTime % 100 < 60, TIMEFROMPARTS(startTime / 100, startTime % 100, 0, 0, 0), null) as startTime, startTime_ref  
     , IIF(endTime is not null AND endTime/100 < 24 AND endTime % 100 < 60, TIMEFROMPARTS(endTime / 100, endTime % 100, 0, 0, 0), null) as endTime, endTime_ref  
     from StartEndTimeIntegers  
 )  
 -- Check whether the computed times are equal to the reference times from the unittests.  
 -- Ideally, this yields no results :-)  
 select * from StartEndTimes  
 where   
 (  
     (startTime is null and startTime_ref is not null)  
 or    (startTime is not null and startTime_ref is null)  
 or    (startTime != startTime_ref)  
 or    (endTime is null and endTime_ref is not null)  
 or    (endTime is not null and endTime_ref is null)  
 or    (endTime != endTime_ref))  
   

Typical Open Source Java experience

Today I decided to give Jaspersoft a go. Read about it, seems to be good, all new ‘Jaspersoft Studio’ waiting to be tested. Installation is a breeze (that is not so typical, that’s excellent).

So I started it. Nice splash screen. And a welcome screen. With a cheat sheet for setting up your first data connection. There comes the familiar part. It first tells me where to find the repository with data connections and how to create a new one. And then – we’re still in the very first cheat sheet, should be a simple start:

“Set the correct JDBC driver name. You will have to add the jar(s) of the driver in order to be able to connect to your database. When done, press Test to test your connection.” Besides that you have to figure out the correct JDBC URL, which is not even mentioned in the cheat sheet.

What????? What is the correct JDBC driver name for my SQL Server 2008R2 database? What jar(s) do I need, and where should I add them?

OK, I’m a programmer (albeit not in Java) so I will probably figure it out. Especially since I had to do the same for Talend Open Studio for Data Integration a while ago. So I also know that in order to make the jtds SQL Server driver work with Windows Integrated Security, I should (and did) install the Studio in a path without spaces (so the default ‘Program Files’ is not good).

But hey, was it too difficult to put some frequently used drivers (SQL Server is hardly exotic, is it?) in the installer and be able to construct the right driver name with some clever dropdowns? That’s what I’m used to in Visual Studio, even in the (also free) express editions.

So, tip to all the major open source providers to flatten the learning curve: If you need a connection to a database to do anything useful, pre-configure the most used ones (Oracle, SQL Server, MySQL – should cover many cases already), so the first time user only needs to fill in the servername, databasename, username and password. When he has played around a little, he will be much more forgiving if he has to configure a JDBC driver for his more exotic databases.

[… trying out further …]

Allright, established a data connection to my database, works. Created a new report (based on ‘Coffee’), specified a query on a simple table: works. Preview the report: ClassDefNotFound, apparently on some class having to do with Groovy.

Selected Java as the language for the report, instead of Groovy. Preview the report: NullPointerException. How am I supposed to make this work, if I get two exceptions out of the box? I have not even programmed a statement of Java myself. I’m disappointed. Yet, I’ll give iReport a try to see if that is more mature.

[… trying out iReport …]

OK, works. I still had to add the jtds driver and specify the right JDBC URL, but by now I know what to do. This time, the report still didn’t work, but iReport is indeed more mature and robust. It stated: report has no pages.

Aha, could it be that the table that I query is empty? Check, yes it is. Could that be the cause of the nullpointerreference in JasperSoft Studio? Fill the table, start JSS – no error anymore!

OK. First thing to do with new code: Create unittests for the regular and the edge cases. A report with no data is an edge case. Should have been tested.

To end positively: Two report designers, both free and I managed to squeeze out simple reports on my own table in one day. Not bad after all – for free products.

IUpdatable, what is it supposed to do?

I’m setting up an OData service by means of WCF Data Services. The DataContext publishes a list of BloodPressureMeasurements. These are queried from a more generic model in a lower tier, which in turn is persisted somewhere. So in essence I work with a model specific for the service, hence we call it the ServiceModel.

To publish the BPM’s, the DataContext contains a

public IQueryable<BloodPressureMeasurement> BloodPressureMeasurements

and in the get method I take care of the mapping from the generic model to the ServiceModel. That was the relatively easy part.

Now I want to enable adding new BPM’s. For the service to accept that, the DataContext has to implement IUpdatable. I have found several examples using either Entity Framework or LINQ to SQL. In both cases, most of the actual ‘IUpdatable-work’ is offloaded to the underlying techniques. And neither the official documentation nor the examples explain why IUpdatable is even there, and what the implementation should accomplish.

I’ve started implementing it nevertheless, and that made me discover exactly that: what should it do? In essence, when the data has to be updatable, WCF Data  Services needs a place to collect all the changes, whereafter it can ask to save all those changes (or discard them). So the IUpdatable implementation should provide some sort of delta-collection. If you get that, it becomes more clear what each of the methods should do.

And while experimenting with IUpdatable and writing this blog, I finally found a very valuable resource on the Astoria Team Blog (now WCF Data Service Team Blog, why didn’t I look there earlier?) Read, it’s a good explanation.

I chose a very simple solution: two List<BloodPressureMeasurement> variables, one for additions, one for deletions (editing is not permitted in our case). Since BPM objects are small POCO’s, I can send the objects themselves around. If you instead want to move around references to the objects, you have to implement ResetResource and ResolveResource.

Furthermore, there are no master-detail collections to be taken care of, so I could also leave out SetReference, Add- & RemoveReferenceToCollection.

After implementing I discovered one more thing that might be handy to know: If you implement a ChangeInterceptor on your service, I will be called in this sequence:

1. all the methods on IUpdatable (as implemented by the context) for assembling the delta
2. your ChangeInterceptor
3. IUpdatable.SaveChanges

Happy Data Servicing!