Vista's desktop index and PowerShell.

Vista's desktop index has changed the way I work. I've stopped worrying about folders, any more than I worry about URLs for Internet content.  It's either obvious or I find it with search.

So... since I have all of my stuff indexed. I should be able to tap into it ... shouldn't I ?

Time to fire up powershell and Live search and have a poke around. There's quite a good article here which explains Windows Desktop search and the same the database connection strings and SQL syntax work with both the downloadable Windows XP version and the Vista Version.

It's not rocket science. The first bit is the connection string. "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';" . Then it's the usual SQL stuff. SELECT fields FROM source WHERE conditions

The source bit is always the same: FROM SYSTEMINDEX. Most of the examples I've found aren't very expansive when it comes to the fields.  For example:
SELECT System.filename FROM SYSTEMINDEX where system.fileExtension = ".wma" .

The list of fields is huge, and the best place to start is with the shell properties on MSDN, there's a core set, a set for documents, a set for mail messages, one for music, one for recorded TV, and the list goes on. So there are are a few useful core ones.

System.kind is particularly useful for narrowing a search down - it groups file extensions into Music, Pictures, documents, etc so you can narrow a search down to Pictures or Documents or Calendar items, e.g.
SELECT system.filename, system.title FROM SYSTEMINDEX WHERE system.kind="picture"
You can see how the extensions map onto the Kinds in the registry at HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\explorer\Kindmap .

Of course the major use of search is for free text searches and there are two predicates, CONTAINS and FREETEXT. FREETEXT is a blunt instrument it give it the word "Swimming" and it will all find all it's forms "SWIMS, SWIMMING, SWAM" give it swimming pool and it will find anything with either of those words or any of their forms (put quotes round to search for a phrase). Sometimes you can't see the wood for the trees with FREETEXT it's a bit of a chainsaw. CONTAINS is like keyhole surgery. You can ask it for FORMSOF("SWIM") NEAR ("POOL" or "BATHS") AND "LESSONS". With CONTAINS the risk is you don't get the document you want.

So Lets put a query together in powershell; I'm going to look for photos of the racing driver Nigel Mansell on my PC, and I want the camera details at the same time - this isn't as daft as it sounds. It lets me sort my scanned film photos from my shot-on-digital ones. Here's the query and the IndexProvider (a smarter man than me might have that permanently in a variable)  

 $IndexProvider  = "Provider=Search.CollatorDSO;Extended Properties='Application=Windows';"
$SQL=("SELECT System.FileName , System.Photo.fnumber , System.Photo.exposureTime, system.photo.focallength, system.photo.isoSpeed, System.photo.dateTaken, system.photo.cameramodel, system.author, system.title, system.keywords,system.size,System.ItemFolderPathDisplay 
FROM SYSTEMINDEX WHERE Contains(* ,'Mansell') ")

Contains (*, 'Mansell') looks in all the fields - important if I'm searching for Pictures. I've found that when you're working with Powershell objects, the Object browser from Visual Studio (I'm using the FREE Express edition) and I've found the parameters that you can pass to the NEW method of an object can save a a few lines of code - I don't need to create connection and command objects as I did in this earlier example.

 $adapter= new-object system.data.oledb.oleDBDataadapter -argumentlist $sql,  $IndexProvider
$ds = new-object system.data.dataset
$adapter.Fill($ds)

$ds.tables[0] shows me results like this (notice the blank fields on the first one giving away it's a film scan).

 SYSTEM.FILENAME              : GP91-23.jpg
SYSTEM.PHOTO.FNUMBER         :
SYSTEM.PHOTO.EXPOSURETIME    :
SYSTEM.PHOTO.FOCALLENGTH     :
SYSTEM.PHOTO.ISOSPEED        :
SYSTEM.PHOTO.DATETAKEN       : 14/07/1991 18:48:35
SYSTEM.PHOTO.CAMERAMODEL     :
SYSTEM.AUTHOR                : {© James O'Neill}
SYSTEM.TITLE                 : Nigel Mansell Williams Renault FW14, Ayrton Senna, British GrandPrix, Silverstone
SYSTEM.KEYWORDS              : {Portfolio}
SYSTEM.SIZE                  : 2992373
SYSTEM.ITEMFOLDERPATHDISPLAY : C:\Users\Jamesone\Pictures\Motor Racing


SYSTEM.FILENAME              : GPM15834+.JPG
SYSTEM.PHOTO.FNUMBER         : 13
SYSTEM.PHOTO.EXPOSURETIME    : 0.008
SYSTEM.PHOTO.FOCALLENGTH     : 100
SYSTEM.PHOTO.ISOSPEED        : 200
SYSTEM.PHOTO.DATETAKEN       : 13/08/2006 11:31:26
SYSTEM.PHOTO.CAMERAMODEL     : PENTAX *ist D
SYSTEM.AUTHOR                : {© James O'Neill 2006}
SYSTEM.TITLE                 : Nigel Mansell, GrandPrix Masters, Silverstone
SYSTEM.KEYWORDS              : {Portfolio}
SYSTEM.SIZE                  : 2797880
SYSTEM.ITEMFOLDERPATHDISPLAY : C:\Users\Jamesone\Pictures\Motor Racing

Technorati tags: Microsoft, Desktop Search, Windows Vista, Photography