Well, it looks like I missed the boat on this release of CruiseControl.NET 1.2.x back in January of 07. Oops. It looks like its fairly simple to upgrade, no breaking changes from what I can see.

The new feature that got me excited is that the Subversion provider now supports checking out the source. I had normally solved this by performing an svn-update (or svn-checkout) through the NAnt Contrib tasks. Its nice that I can now have CruiseControl.NET doing this for me instead of my tasks doing it for me.

I had been wondering if this was possible as of lately. After not seeing any examples on the web I decided to tackle the task myself.

I wanted to place some files (doc, xls, ppt, htm, txt) into SQL Servers BLOB Field and then expose them to SharePoint 2007 (MOSS). This would allow me to search the BLOB data through the SharePoint seach functionality. Pretty much, I wanted expose a document stored in SQL Server to enable it for searching on MOSS.

A lot of the time applications will store the file path to the file and then store the actual file on a file server. This isnt something I wanted to do, mainly because I was only going to be storing, maybe, 30-50 documents a month. Each of these documents were maybe 1 to 2 pages long, nothing huge. Therefore I decided to keep it with my SQL data, in the datbaase as a BLOB field. This has many advantages, you can keep certain documents with your custom systems. Therefore, if you decide to move the SQL Database later on down the road, you dont have to copy the file share to another server and possibly update the database records with their new location.

So here's what I needed to do:
1. Create a BLOB field in a database table that could be full text searched.
2. Create the Full-Text Index
3. Insert some test documents
4. Create the query to search within the blob documents. (still in sql at this point).
5. Expose the SQL Table information to MOSS through the Business Data Catalog.
6. Allow the content to be searched from within the MOSS site.

After that, WE'RE DONE!

HOW TO DO THIS

Blob Table

I created the SQL Table with the following SQL Code:

CREATE TABLE [dbo].[BinaryTable](
   [Row_Id] [int] IDENTITY(1,1) NOT NULL,
   [Blob_Type] [char](3) NOT NULL,
   [Attachment] [varbinary](max) NOT NULL,
   [File_Name] [varchar](100) NULL,
CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED 
   (
      [Row_Id] ASC
   )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Please note that the Blob_Type is actually the file extension of the document.  SQL Server only supports searching the following BLOBS:
- .doc
- .txt
- .xls
- .ppt
- .htm

From BLOBS in Special Operations in MSDN:

Full-text index and search operations

You can index and search certain types of data stored in BLOB columns. When a database designer decides that a table will contain a BLOB column and the column will participate in a full-text index, the designer must create, in the same table, a separate character-based data column that will hold the file extension of the file in the corresponding BLOB field. During the full-text indexing operation, the full-text service looks at the extensions listed in the character-based column, applies the corresponding filter to interpret the binary data, and extracts the textual information needed for indexing and querying.

When a field in a BLOB column contains documents with one of the following file extensions, the full-text search service uses a filter to interpret the binary data and extract the textual information.
• .doc
• .txt
• .xls
• .ppt
• .htm

The extracted text is indexed and becomes available for querying.

Create the Full Text Index

I created a full text catalog.

Then I defined the full text index.

The table was now ready for data.

Inserting Blob Data.

I followed this example to insert a few documents into the system (just alter the connectionstring and column names. I manually altered it by hand an inserted some documents within a few moments. This was for testing only, in a real world app you'd create a robust method for inserting documents into the system.

The documents I inserted all had greeked text for testing purposes.

Querying the Blob.

Once the blob data was inserted, I was then able to query it with this command.

SELECT Row_Id, File_Name FROM BinaryTable WHERE (CONTAINS (Attachment, 'Lorem'))

This searched all the BLOBS for 'Lorem'.

After I got the results I was looking for it was then time to expose it to MOSS.

Exposing the BLOB data to MOSS/SharePoint 2007

In order to expose the data, I had to expose certain metadata entities in the database and certain methods (queries) that allowed SharePoint to understand the search request. This was done through the Business Data Catalog.

This metadata definition had to be built by hand to do enable this functionality. I'm not going to get into how and why things are where they are, all of that info can be found here (expect to spend a good day, or two, to wrap your head around this metadata model).

Here's the code:

BlobSearch.xml

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database" Version="1.0.0.6" Name="BlobSearchSample" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
- <Properties>
<Property Name="WildcardCharacter" Type="System.String">%</Property>
</Properties>
- <LobSystemInstances>
- <LobSystemInstance Name="BlobSearchSampleInstance">
- <Properties>
<Property Name="AuthenticationMode" Type="System.String">WindowsCredentials</Property>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">TESTSERVER</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">BlobTest</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
<Property Name="SsoApplicationId" Type="System.String">BlobTest</Property>
<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral,PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
- <Entities>
- <Entity EstimatedInstanceCount="10000" Name="File">
- <Properties>
<Property Name="Title" Type="System.String">File_Name</Property>
</Properties>
- <Identifiers>
<Identifier Name="Row_Id" TypeName="System.Int32" />
</Identifiers>
- <Methods>
- <Method Name="GetFiles">
- <Properties>
<Property Name="RdbCommandText" Type="System.String">SELECT Row_Id, File_Name FROM BinaryTable WHERE (CONTAINS (Attachment, @textToSearchFor))</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
- <FilterDescriptors>
- <!-- Define the filters supported by the back-end method (or sql query) here.
-->
- <FilterDescriptor Type="Wildcard" Name="TextToSearchFor">
- <Properties>
<Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
- <Parameters>
- <Parameter Direction="In" Name="@textToSearchFor">
- <TypeDescriptor TypeName="System.String" AssociatedFilter="TextToSearchFor" Name="TextToSearchFor">
- <DefaultValues>
<DefaultValue MethodInstanceName="FileFinderInstance" Type="System.String">%</DefaultValue>
<DefaultValue MethodInstanceName="FileSpecificFinderInstance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
- <Parameter Direction="Return" Name="Files">
- <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="FileDataReader">
- <TypeDescriptors>
- <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="FileDataRecord">
- <TypeDescriptors>
<TypeDescriptor TypeName="System.Int32" IdentifierName="Row_Id" Name="Row_Id" />
<TypeDescriptor TypeName="System.String" Name="File_Name" />
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
- <MethodInstances>
<MethodInstance Name="FileFinderInstance" Type="Finder" ReturnParameterName="Files" />
<MethodInstance Name="FileSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Files" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>

Now that it is exposed I was able to import the Metadata into the BDC on SharePoint, also known as Adding an Application Definition to the Business Data Catalog (click link to learn how to import it into SharePoint)

Searching within SharePoint

Now that the data is defined through the metadata model in SharePoint I can actually search it. I tested this by adding a Business Data List to a page in SharePoint.
I was then able to search for "Lorem" and results came back.

I then searched for another key word "Test" that I put in a single document, and that worked as well. See the screen shots below.

Conclusion

You could expand this functionality by adding it to the Content Sources and Search Scopes in SharePoint to allow for enterprise search too. But I think I'll save that for another day. :) Or just watch the videos provided in the links.

And that folks, is how you expose BLOB data to SharePoint Search.

I run on a 64 bit Vista machine. It has two versions of Internet Explorer installed, a 32 bit and 64 bit. When surfing the web I would notice that sometimes flash ads would display, other times they would not and I'd be prompted to install the flash player. So, I would. BUT... the player would never install.

After finally getting tired of it, I looked into the issue some more. Apparently I thought I was always running the 64 bit version of Internet Explorer, but alas, I was not.

In my task bar, I have an IE Icon that I click on sometimes, and other times I'll fire it up through the command prompt by entering "iexplore".

That was the problem. My Task Bar icon was set to open the 32 bit version of IE, while when I typed "iexplore" into the command window the 64 bit version would run. That would explain why I'm not seeing flash files on my 64 bit version. 64 Bit IE is not supported by flash. Bummer dude.

My fix:

I created a shortcut called "ie" in the system32 directory that fires up the 32 bit version (if I need flash support). Which I do when I'm watching episodes of DNRTV.

I use Subversion (SVN) for my source control and I access SVN through TortoiseSVN so that its integrated into the Windows shell. The past few companies I've worked at have all utilized SVN in one way or another, but we've always had a problem with developers submitting code without attaching a message to the commit.

Why does this matter? If you, as a developer, check in 20 files that are related to project trunk X, without a message, how am I suppossed to know why you checked those into the repository? Did you fix a bug? Did you implement a new feature? Did you complete a task? Did you add a new project to the system? What did you do? Thats the problem if you do not provide a message for each commit, others have no idea of knowing what you did. 

By not providing the message we're not keeping track of what we did and why we did it. This comes heavily into play when you need to revert back to a certain revision, but you're not sure which revision to go back to. The only thing you remember is "I know it was when we implemented feature 'ABC' into the system." If I provided a comment, I could see in the Tortoise Log that on MM/DD/YYYY at XX:XX time developer "Bob" implemented the feature. Knowing that, I could find the version number and revert back to that revision. Without this information I might spend HOURS UPON HOURS searching through lines of code looking for a key piece that helps me decide if this is the version I want. This could have been avoided if a simple message was provided.

HOWTO: Requiring Messages

By default SVN does not require a message when committing. You can tap into the hooks of SVN and provide some custom code to force your developers to provide a message. The script to do this is below. Note: I cannot take credit for creating this script, as it was found on the net and I dont have the source. So, if you wrote this script, please let me know and I'll add your name here as the author. :) 

Steps:

1. Create Script
2. Save as pre-commit.bat
3. Drop it into the hooks directory of your repository.
4. DONE

The Script (also provided at the bottom of this post as a download)

Please note: This assumes that you have Subversion installed in the C:\Program Files\

Save this script as "pre-commit.bat".

Now drop this into your hooks directory of your actual repository. SVN will now execute this batch file each time a commit is taking place. It will check that a message exists. If one does not, it will throw an error message that is highlighted in dark red (above, in the script).

If you are using TortoiseSVN, this is what you will see:

Thats it. Now users cannot commit without a message. :)

Good Message Recommendations

Even if messages are required it still does not prevent a user from typing some garbage into the screen. Here's what I recommened to my team...

SVN Messages should contain:

1. What you did. Give a quick explanation "altered file processing routine" or "created new project library" 
2. Why you did it. This could be as simple as stating "Fixed Bug 456" or "added new library to handle file conversions."

Example messages:

"Fixed bug 456 by altering file processing routine"

"Added new library to project XYZ to handle file conversions".

There are also many other plug-ins that have been built for TortoiseSVN and SVN in general. Just search for them and you should be able to find something that matches your bug tracking system (such as Gemini).

Conclusion

Forcing the developers to provide a message has enabled the team to communicate better. No longer are members constantly asking the question of "when was this fix applied?" or "What version did this happen in?" The messages make the respository easier to manage and a respository thats easy to manage means we have a healthier code base. :)

Download

pre-commit.zip (.48 KB)

I recently found a couple good videos on SharePoint so I thought I'd share the links.

Microsofts Visual How To's - Videos include, Custom Search Page, Content Sources, Search Scopes, Business Data Catalog, Excel Services and Much more

DNR TV - Videos Include, InfoPath Forms Service, Content Types, Business Data Catalog, and more. Episode 1, Episode 2, Episode 3, Episode 4

Enjoi.