Synchronize Kindle metadata between 2 Macs for 3rd party books

I try hard to stick to e-books for my developer resources for the obvious benefits of search and electronic notes/bookmarks.  I have several Macs and I use Dropbox to sync content and 3rd party e-books but I couldn't figure out how to sync my Kindle metadata for those 3rd party books.  Today I finally figured out a solution that works for Macs.

1. Created a Kindle_3rd_Party_Content folder within my Dropbox folder.
2. Moved all 3rd party .mobi and .mbp files to that folder.
3. Created symbolic links to the 3rd party .mobi and .mbp files within my Kindle application's Content Folder on one of my Macs
4. Duplicated step 3 on my other Macs

Now when I open my Kindle application on any of my Macs, Kindle recognizes the symbolic links as being within my Downloaded Items, and when I add notes/bookmarks or change my location, my changes are saved via the symbolic link to the original .mbp file stored in Dropbox.

The key to this solution is to use symbolic links instead of aliases, because the Kindle application does not recognize aliases.

The Dropbox Wiki provided some great suggestions on how to sync folders outside of the Dropbox folder.  Tom Nelson gave a nice explanation of the differences between aliases and symbolic links.

How to reuse a SQL statement

I needed to create a stored procedure that would call a somewhat complex SELECT statement using T-SQL full text search (CONTAINS/CONTAINSTABLE). The result set would be filtered differently depending on parameter values passed into the stored procedure.  I didn't want to copy and paste the full SELECT statement into multiple IF/THEN/ELSE blocks.

I considered two options:

1. Create a shared stored procedure to return a "base" recordset, and then call that stored procedure from other stored procedures that would further filter the "base" recordset
2. Generate dynamic SQL within an IF/THEN/ELSE structure and then execute the dynamic SQL at the end.

I found this link which provided great information on how to pass a result set from one stored procedure to another:
http://www.sommarskog.se/share_data.html

I decided against Option #1 because the best option involves the creation of a temporary table.  I was worried about performance and also maintaining the declaration of the temporary table in the calling stored procedures in the event of a design change to the source table.

This link provided enough information for me to generate and execute dynamic SQL:
http://stackoverflow.com/questions/7992837/sub-query-in-dynamic-query-sql-server-2008

Here is the final design of my stored procedure:

   1: USE [MyDatabase]

   2: GO

   3: /****** Object:  StoredProcedure [dbo].[SearchMessages]    Script Date: 12/05/2013 14:37:41 ******/

   4: SET ANSI_NULLS ON

   5: GO

   6: SET QUOTED_IDENTIFIER ON

   7: GO

   8:  

   9: CREATE PROCEDURE [dbo].[SearchMessages]

  10:     @limit INT = -1

  11:     ,@and_search_terms NVARCHAR(4000) = ''

  12:     ,@or_search_terms NVARCHAR(4000) = ''

  13:     ,@xml_include_message_types XML = ''

  14:     ,@xml_exclude_message_types XML = ''

  15: AS

  16: BEGIN

  17:     SET NOCOUNT ON;

  18:     

  19:     DECLARE @idoc int;

  20:     DECLARE @select_msg_type_prefix NVARCHAR(40) = 'SELECT A.* FROM ( ';

  21:     DECLARE @select_msg_type_suffix NVARCHAR(4) = ') A ';

  22:     DECLARE @select_msg_type_where NVARCHAR(150);

  23:     DECLARE @select_msg_type_orderby NVARCHAR(100) = ' ORDER BY A.select_rank, A.rank DESC, A.title';

  24:     DECLARE @select_final NVARCHAR(MAX);

  25:  

  26:     DECLARE @select_rnk_msg NVARCHAR(1000) =

  27:         '

  28:         SELECT RNK_MSG.* 

  29:         FROM (

  30:             SELECT m.*, 1 as select_rank, km.rank

  31:             FROM dbo.MY_TABLE m

  32:             INNER JOIN

  33:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @and_search_terms) as km

  34:                 ON m.msg_id = km.[KEY]

  35:             UNION

  36:             SELECT m.*, 2 as select_rank, km.rank

  37:             FROM dbo.MY_TABLE m

  38:             INNER JOIN

  39:                 CONTAINSTABLE(dbo.MY_TABLE, (msg_text, location, title), @or_search_terms) as km

  40:                 ON m.msg_id = km.[KEY]

  41:             WHERE m.msg_id NOT IN (SELECT msg_id FROM dbo.MY_TABLE WHERE CONTAINS((msg_text, location, title), @and_search_terms))

  42:         ) RNK_MSG

  43:         WHERE RNK_MSG.status != ''N'' AND RNK_MSG.delete_ind != 1

  44:         '

  45:         ;

  46:         

  47:     IF @limit >= 0

  48:     BEGIN

  49:         set @select_msg_type_prefix = 'SELECT TOP (@limit) A.* FROM ( ';

  50:     END

  51:         

  52:     IF @xml_include_message_types IS NOT NULL AND @xml_include_message_types.exist('/MessageTypes') = 1

  53:     BEGIN

  54:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_include_message_types

  55:  

  56:         SET @select_msg_type_where =

  57:         'WHERE A.message_type_id IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  58:     END

  59:     ELSE IF @xml_exclude_message_types IS NOT NULL AND @xml_exclude_message_types.exist('/MessageTypes') = 1

  60:     BEGIN

  61:         EXEC sp_xml_preparedocument @idoc OUTPUT, @xml_exclude_message_types

  62:  

  63:         SET @select_msg_type_where =

  64:         'WHERE A.message_type_id NOT IN (SELECT * FROM OPENXML (@idoc, ''/MessageTypes/Type'', 1) WITH (ID char(1)))';

  65:     END

  66:     ELSE

  67:     BEGIN

  68:         SET @select_msg_type_where = '';

  69:     END

  70:  

  71:     SET @select_final = @select_msg_type_prefix + @select_rnk_msg + @select_msg_type_suffix + @select_msg_type_where + @select_msg_type_orderby;

  72:     EXEC sp_executesql @select_final

  73:         ,N'@idoc INT,@limit INT,@and_search_terms NVARCHAR(4000),@or_search_terms NVARCHAR(4000)'

  74:         ,@idoc=@idoc,@limit=@limit,@and_search_terms=@and_search_terms,@or_search_terms=@or_search_terms;

  75:  

  76: END

NUnit TestCase vs. TestCaseSource attributes

NUnit is my favorite framework for unit testing C#.NET code.  I have long been a fan of the TestCase attribute for testing multiple scenarios where the test logic is identical.  For example, TestCase is fantastic for checking edge cases.

Here is a simplistic class for calculating the difference between 2 dates.  If the dates represent the same day, the result should be a TimeSpan of 0, otherwise calculate the actual difference.

1. public class Calculator
2. {
3.     public static TimeSpan SubtractDates(DateTime firstDate, DateTime secondDate)
4.     {
5.         return firstDate.Date == secondDate.Date
6.             ? new TimeSpan(0)
7.             : secondDate - firstDate;
8.     }
9. }

I can use the TestCase attribute to confirm that dates from previous or subsequent days return a TimeSpan with an Hours value that is not 0, but dates from the same day always return a TimeSpan with an Hours value of 0.

1. [TestCase(1, 0, -9)]
2. [TestCase(2, 8, 0)]
3. [TestCase(2, 9, 0)]
4. [TestCase(2, 10, 0)]
5. [TestCase(3, 0, 15)]
6. public static void Test_Calculator_SubtractDates_Using_TestCase(int day, int hour, int expectedHours)
7. {
8.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
9.     var secondDate = new DateTime(2000, 6, day, hour, 0, 0);
10.  
11.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
12.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
13. }

TestCase is limited in that the constructor will accept only constant, typeof, or array creation expressions.  This can be a pain if you want to test various scenarios with a range of objects.  For example, I am not able to do this with TestCase:

1. [TestCase(new DateTime(2000, 6, 1, 0, 0, 0), -9)]
2. [TestCase(new DateTime(2000, 6, 2, 8, 0, 0), 0)]
3. [TestCase(new DateTime(2000, 6, 2, 9, 0, 0), 0)]
4. [TestCase(new DateTime(2000, 6, 2, 10, 0, 0), 0)]
5. [TestCase(new DateTime(2000, 6, 3, 0, 0, 0), 15)]
6. public static void Test_Calculator_SubtractDates_Using_TestCase(DateTime secondDate, int expectedHours)
7. {
8.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
9.  
10.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
11.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
12. }

If I attempt to run these tests, I get an error “An attribute argument must be a constant expression, typeof expression or array creation expression of an attribute parameter type.”

I just recently discovered the TestCaseSource attribute which can be used to work around the limitations of TestCase while providing the same functionality.  Here is the same set of unit tests using TestCaseSource:

1. [Test, TestCaseSource("SecondDateCases")]
2. public static void Test_Calculator_SubtractDates_Using_TestCaseSource(DateTime secondDate, int expectedHours)
3. {
4.     var temp = SecondDateCases.ToString();
5.     var firstDate = new DateTime(2000, 6, 2, 9, 0, 0);
6.  
7.     TimeSpan actual = Calculator.SubtractDates(firstDate, secondDate);
8.     Assert.That(actual.Hours, Is.EqualTo(expectedHours));
9. }
10. private static object[] SecondDateCases =
11. {
12.     new object[] { new DateTime(2000, 6, 1, 0, 0, 0), -9 },
13.     new object[] { new DateTime(2000, 6, 2, 8, 0, 0), 0 },
14.     new object[] { new DateTime(2000, 6, 2, 9, 0, 0), 0 },
15.     new object[] { new DateTime(2000, 6, 2, 10, 0, 0), 0 },
16.     new object[] { new DateTime(2000, 6, 3, 0, 0, 0), 15 }
17. };

For more information, consult the NUnit 2.6.3 Documentation for TestCase and TestCaseSource.