Saturday, January 29, 2011

Extending CoreResultsWebPart to Handle Search Queries Written in FAST Query Language

This was one of unanswered items in my recent two presentations on Search at TSPUG and MSPUG, so I was driven to figure it out and eventually did get it to work although not without some controversial steps. In this post I chose to also describe other approaches I tried and things I learned along the way, which didn’t necessarily get me to the end goal but may still be useful in your specific scenario. If you are looking for just extending CoreResultsWebPart so that it can “understand” FQL then you may want to scroll down a bit. You can download the complete source code here. I was testing my code on a SharePoint Server 2010 with December 2010 CU installed.

As you may know search web parts in SharePoint 2010 are no longer sealed, which gives you lots of flexibility via extending them. CoreResultsWebPart is probably the most important of all and therefore it is a great candidate for being extended. I wanted to take a search phrase passed as a query string argument to a search results page and write my own FQL query using this phrase as a parameter. My FQL query would do something interesting with it, for example use XRANK to boost documents created by a particular author. I certainly wanted to leverage all the goodness of CoreResultsWebPart, just use my FQL query instead of a plain search phrase. Contrary to my expectations it turned out to be not trivial to accomplish. So let’s dive into the details.

The story started with it completely not working, so I was forced to write a custom web part that used query object model and in particular the KeywordQuery class to deal with queries written in FAST Query Language (FQL). This is the one I have demonstrated at MSPUG and (with little success) at TSPUG.  Below is a fragment showing how the query is submitted and results are rendered (BTW here is related MSDN reference with example).

using (KeywordQuery q = new KeywordQuery(ssaProxy))
{
q.QueryText = fql;
q.EnableFQL = true;
q.ResultTypes = ResultType.RelevantResults;
ResultTableCollection tables = q.Execute();

if (tables.Count == 0)
{
return;
}

using (ResultTable table = tables[ResultType.RelevantResults])
{
while (table.Read())
{
int ordinal = table.GetOrdinal("Title");
string title = table.GetString(ordinal);
TitleLabel.Controls.Add(
new LiteralControl(
String.Format("<br>{0}</br>\r\n", title)));
}

table.Close();
}
}

As you can see, rendering of results is very basic. Of course it can be made whatever it needs to be but why bother if there is CoreResultsWebPart around, which does it great already and is also highly customizable? So at this point I rolled up my sleeves. I have a book titled “Professional SharePoint 2010 Development” with a red Boeing 767 on the cover, and in Chapter 6 there is a great discussion of how to extend CoreResultsWebPart. Also Todd Carter speaks about it and shows a demo in his excellent video on Search. If you haven’t seen it I highly recommend spending an hour and 16 minutes watching it (Module 7).
Empowered with all this I wrote a web part that extended the CoreResultsWebPart and used a FixedQuery property to set a custom query, being almost one-to-one copy of Todd’s demo example. Here is the listing of this web part class (note how ConfigureDataSourceProperties() method override is used to set the FixedQuery property):

public class ExtendedCoreResultsWebPartWithKeywordSyntax : CoreResultsWebPart
{
protected override void ConfigureDataSourceProperties()
{
const string LongQueryFormat = "(FileExtension=\"doc\" OR FileExtension=\"docx\") AND (Author:\"{0}\")";
const string ShortQuery = "(FileExtension=\"doc\" OR FileExtension=\"docx\")";
string query = null;

if (String.IsNullOrEmpty(AuthorToFilterBy))
{
query = ShortQuery;
}
else
{
query = String.Format(LongQueryFormat, AuthorToFilterBy);
}

this.FixedQuery = query;
base.ConfigureDataSourceProperties();
}

[SPWebCategoryName("Custom"),
Personalizable(PersonalizationScope.Shared),
WebPartStorage(Storage.Shared),
WebBrowsable(true),
WebDisplayName("Author to filter by"),
Description("First and last name of the author to filter results by.")]
public string AuthorToFilterBy { get; set; }
}

The web part actually filters results by a given author. It uses keyword query syntax and not the FQL so we are far from being done yet. Remember how in the previous code fragment there was a line q.EnableFQL = true;? If just we could set it somewhere we would be essentially done! Well right but the KeywordQuery object is not directly accessible from the CoreResultsWebPart because it uses federation object model on top of the query object model (as do other search web parts). Purpose of the federation object model is sending same query to multiple search results providers and aggregating results later either in different spots on results page or in the same list. This is done by abstracting each search results provider by means of a Location class. Important classes in federation object model are shown on the diagram below.


 image


As you can see, CoreResultsWebPart is connected to the federation OM through CoreResultsDatasource and CoreResultsDatasourceView types with the latter actually doing all the hard work interacting with the model. And also our objective, the EnableFQL property, exists in the FASTSearchRuntime class, which in turn sets this property on the KeywordQuery class, and as I showed at the beginning, this is what’s required to get FQL query syntax accepted.
As Todd Carter and the authors of Professional SharePoint 2010 Development point out, we need to extend the CoreResultsDatasourceView class in order to be able to control how our query is handled, and wire up our own class by also extending CoreResultsDatasource class. CoreResultsDatasourceView class creates a LocationList with a single Location object and correctly determines which concrete implementation type of ILocationRuntime to wire up based on search service application configuration. In other words, federation by default is not happening for the CoreResultsWebPart. There is another web part, FederatedResultsWebPart, and another view class, FederatedResultsDatasourceView, whose purpose is to do exactly that. With that, let us get back to our objective.
If we were using SharePoint Enterprise Search then we would be almost done, because public virtual method AddSortOrder(SharePointSearchRuntime runtime) defined in the SearchResultsBaseDatasourceView class would let us get our hands on the instance of ILocationRuntime. But since we deal with FASTSearchRuntime we are sort of out of luck. Yes there exists a method overload AddSortOrder(FASTSearchRuntime runtime) but it is internal! This is where the controversy that I have mentioned at the beginning comes to play: I was not able to find a better way than to invoke an internal member via Reflection. My way works for me, but keep in mind that usually methods are made private or internal for a reason. I used Reflection to access internal property LocationRuntime of the Location object. I don’t know why this property is internal. If someone knows or has a better way to get at the FASTSearchRuntime instance or the KeywordQuery instance – please leave a comment! Here is a code fragment showing extension of the CoreResultsDatasourceView and getting an instance of FASTSearchRuntime from there.

class CoreFqlResultsDataSourceView : CoreResultsDatasourceView
{
public CoreFqlResultsDataSourceView(SearchResultsBaseDatasource dataSourceOwner, string viewName)
: base(dataSourceOwner, viewName)
{
CoreFqlResultsDataSource fqlDataSourceOwner = base.DataSourceOwner as CoreFqlResultsDataSource;

if (fqlDataSourceOwner == null)
{
throw new ArgumentOutOfRangeException();
}
}

public override void SetPropertiesOnQdra()
{
base.SetPropertiesOnQdra();
// At this point the query has not yet been dispatched to a search
// location and we can set properties on that location, which will
// let it understand the FQL syntax.
UpdateFastSearchLocation();
}

private void UpdateFastSearchLocation()
{
if (base.LocationList == null || 0 == base.LocationList.Count)
{
return;
}

foreach (Location location in base.LocationList)
{
// We examine the contents of an internal
// location.LocationRuntime property using Reflection. This is
// the key step, which is also controversial since there is
// probably a reason for not exposing the runtime publically.
Type locationType = location.GetType();
PropertyInfo info = locationType.GetProperty(
"LocationRuntime",
BindingFlags.NonPublic | BindingFlags.Instance,
null,
typeof(ILocationRuntime),
new Type[0],
null);
object value = info.GetValue(location, null);
FASTSearchRuntime runtime = value as FASTSearchRuntime;

if (null != runtime)
{
// This is a FAST Search runtime. We can now enable FQL.
runtime.EnableFQL = true;
break;
}
}
}
}

By the way, another limitation of my approach is that using Reflection requires full trust CAS policy level. That said, finally we have arrived at our objective – we can set the flag on the FASTSearchRuntime, and it will understand our FQL queries. Our extended search results web part will show results as directed by the query (in the attached source code it uses XRANK) and leverage presentation richness of the CoreResultsWebPart.


Friday, January 21, 2011

Decks and Source Code from January 19th TSPUG Meeting

Thanks to all who came to Wednesday’s TSPUG meet up. I’ve uploaded a package with presentation and source code files. I was able to resolve the first of the questions unanswered during my previous talk about SharePoint search technologies. Although I meant to show it, I didn’t get to talk much about promoting user profile properties for selection in FAST user context for visual best bets. Basically as Steve Peschka has pointed out,  the first thing that needs to be done is permissions granted to the profile store. This will let you see the list of properties in his Property Explorer tool. Secondly, to promote profile properties to be available for selection in FAST Search user context administration section you need to edit value of FASTSearchContextProperties property of FAST Query search service application (SSA). I wrote a command-line utility for this purpose, which can be invoked as follows:

FASTProfilePropertyUpdater.exe –ssaId <FAST query SSA Guid> -action Add|Remove -property <User Profile property name>

Code fragment below demonstrates how SSA reference is acquired and the “Add” operation is accomplished. Full source code is a part of the package. Ideally you want to use PowerShell script to manage this, I just wrote an executable as it was easier to get it working quickly for me.

SPFarm farm = SPFarm.Local;
var settingsService = SPFarm.Local.Services.GetValue<SearchQueryAndSiteSettingsService>();
var serviceApp = settingsService.Applications[ssaId];
var searchApp = serviceApp as SearchServiceApplication;

if (null == searchApp)
{
Console.WriteLine(
"Cannot find search service application with the ID '{0}'.",
ssaId);
return 2;
}

string properties = (String)searchApp.Properties[FASTSearchContextProperties];
Console.WriteLine(
"Updating service application properties for property key '{0}'... Value before update was: '{1}'",
FASTSearchContextProperties,
properties);
List<string> propertiesList = properties.Split(',').
Select(p => p.Trim()).ToList();

if (ContextPropertyActions.Add == action &&
properties.IndexOf(propertyName, StringComparison.Ordinal) < 0)
{
propertiesList.Add(propertyName);
properties = string.Join(",", propertiesList.ToArray());
searchApp.Properties[FASTSearchContextProperties] = properties;
searchApp.Update(true);
Console.WriteLine("Property added. Updated value is: '{0}'.", properties);
return 0;
}

Also in the package is the source code for a web part demonstrating how to dynamically boost search relevance ranking from within an FQL query by using XRANK keyword.The demo of this web part didn’t go well, but the code is actually working (I dragged the wrong web part to the page during presentation!). Also someone has asked me about proximity-based filtering of results in FAST query language. Yes this is possible  - there is a number of keywords that support this.

Saturday, January 8, 2011

Automate Internet Proxy Management with PowerShell

The solution is very simple, yet the issue was so annoying to me that I thought to share this. I have a laptop and do work on it on site at my client and when I am at home. When I am in the office connected to my client’s network I need to use a proxy to access the Internet, while usually I do not need a proxy when I am located elsewhere. You normally set up a proxy using browser settings. With Internet Explorer 8 you open a browser, then go to Tools >> Internet Options >> Connections >> LAN Settings. There you configure the proxy parameters. As a part of configuration you can turn the proxy on or off by setting or clearing a Use a proxy server for your LAN check box, and as you switch the proxy the rest of your proxy configuration settings such as exceptions is preserved for you. Great!

My problem was that on almost daily basis I had to open the browser, navigate to settings then check or uncheck that box. I would come to work and forget to go through the steps so my browser would get stuck, and then I would go “oh yeah, I turned the proxy off last night”. Of course the reverse would happen at home… I tolerated this too long because I didn’t expect that a simple solution exists, but it does and here it is:

 HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet Settings\ProxyEnable key stores a DWORD value, either 1 or 0 to indicate if the proxy is enabled.

So I wrote two 1-line PowerShell scripts and added two shortcuts to them to my Windows taskbar: Enable Proxy, Disable Proxy! Here is how the script to enable proxy looks like:

Set-ItemProperty "HKCU:\Software\Microsoft\Windows\CurrentVersion\Internet Settings" -Name ProxyEnable -Value 1

Then I’ve got excited, and wrote yet another rock star PowerShell script. Here it is:

$matches = @(ipconfig /all | findstr "myclientdomain")
if($matches.Count -gt 0){
    ./EnableProxy.ps1
}else{
    ./DisableProxy.ps1
}

That’s right I am using output of the ipconfig command to determine if I am on the client’s network and if yes then I turn on my proxy. After I have added a scheduled task running at user logon to invoke this script, the quality of my professional life has improved.

Thursday, December 23, 2010

Writing Trace Output to ULS Log in SharePoint 2010

SharePoint 2010 supports writing custom messages to ULS log. A blog post by Waldek Mastykarz of Mavention provides a good example of how to do this. UPDATE 02/24/2011: Andrew Connell's article on MSDN from December 2010 provides essential information on diagnostics logging.

Ability to send messages from System.Diagnostics.Trace.Write() and Trace.Fail() method overloads to ULS is yet another “logging novelty” I will be focusing on in this post.

There is a new type in Microsoft.SharePoint assembly, which was not there in version 12 – Microsoft.SharePoint.SPULSTraceListener. As its name suggests it allows sending trace messages to ULS.

If you are instrumenting your custom SharePoint code and need to write diagnostic traces to ULS then you can wire up SPULSTraceListener type in your web.config and use tracing infrastructure available in System.Diagnostics.Trace.  Here are the steps:

1. Add a trace listener element to web.config. For more information see description of system.diagnostics element on MSDN.

<system.diagnostics>
<trace>
<listeners>
<remove name="Default"/>
<add
name="ULSTraceListener"
type="Microsoft.SharePoint.SPULSTraceListener, Microsoft.SharePoint, version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</listeners>
</trace>
</system.diagnostics>

In this XML snippet note the <remove name=”Default”>. This is done to remove default trace listener, which is wired up by default and displays a pop-up window with the message when Trace.Fail() is called.

2. In central administration site configure diagnostic logging (Central Administration >> Diagnostic Logging). If you are using Trace.Write() method calls then they are logged as Verbose and so in order to actually see them in ULS logs you need to make sure that throttling level is set to Verbose for SharePoint Foundation > Service Connections category. If you use Trace.Fail() overloaded methods then the message will be written as a High level, which by default is on, so the message will normally end up being written to a log file. And yes, all messages will be written to the log under Service Connections category.


What About TraceContext.Write()?

UserControl, Page and HttpContext classes have a property named Trace of type System.Web.TraceContext. It allows tracing messages using ASP.NET trace infrastructure, for example: HttpContext.Current.Trace.Write(string message);. You can send ASP.NET trace messages to ULS using standard technique but with a caveat:

Add trace configuration element under system.web element in your web.config as follows:


<system.web>
<trace enabled="true" requestLimit="1000" mostRecent="true" writeToDiagnosticsTrace="true"/>
...

Here the key attribute is writeToDiagnosticsTrace instructing ASP.NET trace to use tracing infrastructure from System.Diagnostics. Now trace messages written using TraceContext.Write() will be sent to ULS.
Now the caveat: adding system.web/trace element appears to destabilize SharePoint. I have tried this on SharePoint Foundation and SharePoint Server 2010 with October 2010 CU, and although tracing does work as expected I have got a consistent error when trying to create a new web part page:
UIErrorFromTracing
Looking at the ULS log, I found these messages logged under SharePoint Foundation category:

SharePoint Foundation    General    b9y3    High    Failed to open the file 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Resources\wss.resx'.

SharePoint Foundation    General    b9y4    High    #20015: Cannot open "": no such file or folder.   
SharePoint Foundation    General    b9y4    High    (#2: Cannot open "": no such file or folder.)   
SharePoint Foundation    General    b9y9    High    Failed to read resource file "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\Resources\wss.resx" from feature id "(null)".

 
As soon as I disable the system.web/tracing the error disappears. I am not sure if this is a bug or adding <trace> element under <system.web> is not supported. In any case there is enough of rich diagnostic features available, and if you are using tracing in your application then just avoid System.Web.TraceContext.Write() in favor of System.Diagnostics.Trace.Write() or at least use it temporarily if you need to. By the way System.Diagnostics.Debug.Write() also sends messages to trace log.

UPDATE (01-16-2011): There is yet another issue caused by turning on trace that I ran into today: SharePoint designer cannot open a site in a web application for which tracing is enabled, and keeps prompting for credentials. If you check out traffic with Fiddler, you will find that SPD makes requests to http://devserver2010/_vti_bin/shtml.dll/_vti_rpc, which return a 401 UNAUTHORIZED. Not even verbose logging reveals anything useful. Well, commenting out <trace> element in web.config restores normal functionality.

Monday, December 13, 2010

Configure Links Web Part to Open URLs In a New Window With SharePoint 2010

With SharePoint 2010 (or SharePoint Foundation 2010) you can customize list views using XSLT, which is very powerful and for simple changes also quite simple. Here is an example of how to make URLs in Links list web part open up pages in a new browser window.

Links web part as well as other list-based web parts uses a predefined list view to render its content. Since views are now defined using XSLT style sheets, all that is needed to control the HTML is to override appropriate XSLT template with a custom one that has HTML markup that we need, for example <a href=”….” target=”_blank”>. List web part’s edit mode panel contains a text field titled XSL Link under Miscellaneous category where you can provide a URL of an XSLT file, which will override default rendering behavior.

Let’s say we have a Links list with a few URLs in it and we have added a Links web part to a page. We want to have all links shown by the web part open up in a new window. We need to find an XSLT template which describes rendering of the field with a link. One easy way to determine this is to sort by the URL field then take a note of the value of SortField query string value, which tells us that the field name we need is URLNoMenu. URL field is not a part of default view, default view has a URL (URL with edit menu) column, so you would need to add a column titled URL. As shown in the picture below, I’ve added my own view “Only Url” to Links list with this field only and set the web part to use this view.

LinksListExample

Now we need to find an XSLT template for the URLNoMenu field.  Out-Of-The-Box templates are stored under the 14 hive in TEMPLATE\LAYOUTS\XSL on server file system. For what we need there are 2 important files there: main.xsl and fldtypes.xsl. The former file is a default file used for formatting, which imports other XSLT files including fldtypes.xsl. The latter file is the one which contains many templates for rendering different fields. Doing search inside this file for URLNoMenu yields:

<xsl:template name="FieldRef_URLNoMenu_body" ddwrt:dvt_mode="body" match ="FieldRef[@Name='URLNoMenu']" mode="Computed_body">

Next we copy this template XML element and put it in a new XSLT file. I named mine TargetBlank.xsl. Inside the new file we change HTML anchor tag to include target=”_blank” (or anything else we need) and save the file. Once we specify URL to this new file in the XSL Link field of a web part being edited, it will override all default formatting, therefore we also need to make sure that other templates still apply. Thanks to XSL import and overriding mechanism this is easy to do, we just need to import main.xsl file, and our version of the FieldRef_URLNoMenu_body template will override the one located inside fldtypes.xsl. Here is the complete listing of the TargetBlank.xsl.

<xsl:stylesheet xmlns:x="http://www.w3.org/2001/XMLSchema" 
xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
version="1.0"
exclude-result-prefixes="xsl msxsl ddwrt"
xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"
xmlns:asp="http://schemas.microsoft.com/ASPNET/20"
xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:msxsl="urn:schemas-microsoft-com:xslt"
xmlns:SharePoint="Microsoft.SharePoint.WebControls"
xmlns:ddwrt2="urn:frontpage:internal">
<xsl:import href="/_layouts/xsl/main.xsl"/>
<xsl:output method="html" indent="no"/>
<xsl:template name="FieldRef_URLNoMenu_body"
ddwrt:dvt_mode="body"
match ="FieldRef[@Name='URLNoMenu']"
mode="Computed_body">
<xsl:param name="thisNode" select="."/>
<xsl:variable name="url" select="$thisNode/@URL" />
<xsl:variable name="desc" select="$thisNode/@URL.desc" />
<xsl:choose>
<xsl:when test="$url=''">
<xsl:value-of select="$desc" />
</xsl:when>
<xsl:otherwise>
<xsl:choose>
<xsl:when test="@Format='Image'">
<img onfocus="OnLink(this)" src="{$url}" alt="{$desc}" />
</xsl:when>
<xsl:otherwise>
<a onfocus="OnLink(this)" href="{$url}" target="_blank">
<xsl:choose>
<xsl:when test="$desc=''">
<xsl:value-of select="$url" />
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$desc" />
</xsl:otherwise>
</xsl:choose>
</a>
</xsl:otherwise>
</xsl:choose>
</xsl:otherwise>
</xsl:choose>
</xsl:template>
</xsl:stylesheet>


In this file the xsl:stylesheet element is copied from the main.xsl file. Also note the xsl:import and the xsl:output statements. Lastly we need to upload the XSLT file and reference its URL in the XSL Link field of our web part. That’s all there is to it. Our links should now open in a new browser window.

Wednesday, October 20, 2010

Decks, Source Code and Resources from October 19 MSPUG Meeting

Thanks a lot to the folks who showed up yesterday at the Mississauga User Group meeting. Also I’d like to thank Ray Outair for his efforts in running the group in celebration of its first year anniversary. I have posted the deck for my presentation “Leveraging SharePoint 2010 Search Technologies” and the source code of the demo web part demonstrating FQL capabilities on SoftForte site here.

If you are planning search deployment I highly recommend checking out technical diagrams on TechNet: http://technet.microsoft.com/en-ca/library/cc263199.aspx. There are 4 diagrams there dedicated specifically to Search. Also check out downloadable content here http://technet.microsoft.com/en-ca/library/cc262788.aspx – it has been recently updated.

Search

As I mentioned at the meeting, I also recommend a book titled Professional Microsoft Search: FAST Search, SharePoint Search, and Search Server by Mark Bennett, Jeff Fried, Miles Kehoe and Natalya Voskresenskaya. It is a great resource on planning and architecture of SharePoint Enterprise Search, and specifically FAST Search products.

Last but not least is a reference to Steve Peschka’s blog post where Steve introduces a Search Explorer tool that he wrote. Steve’s blog is an essential resource on developing Search and other SharePoint development topics.

So I’d like to re-cap on a few questions we left unanswered yesterday:

1. FAST visual best bets were displayed, but did not get filtered based on User Context’s managed properties. The question is how to create/promote properties into user context for such filtering to happen correctly. UPDATE: I figured this one out

2. I was not lucky extending CoreResultsWebPart and make it process custom FQL queries, and was forced instead to write a web part from scratch. The question is whether there is a supported way to extend CoreResultsWebPart for FAST search queries.
UPDATE: this one is now also addressed. Check it out here.

3. Somebody has mentioned that they could not get PowerPoint visual preview to work with FAST search results, suspecting that SSL encryption between FAST Search Server and query SSA was to blame. Looks like some research is needed here.

I plan to look into these and post my findings. Please feel free to comment on if you get answers sooner.

Sunday, September 19, 2010

Enumerate SharePoint 2010 Databases

If you are working with SharePoint in a lab environment and use one shared database server to store databases from multiple farms then with SharePoint 2010 you can quickly lose control of your databases since there are much more of them now. When you set up a lab farm you typically run Products Configuration Wizard which leaves you with several databases created whose names contain GUIDs, which makes it hard to distinguish between them and relate them to corresponding farms.

Let’s say you need to get rid of a farm and want to delete corresponding databases after. What do you do? If you haven’t yet disconnected from the farm and uninstalled SharePoint then PowerShell comes at help. Log in to one of the farm servers, start up SharePoint 2010 Management Shell and enter this command:

Get-SPDatabase | % {$_.Name}

It will list names of all databases utilized in the current farm. You can now create a script to drop these databases or do whatever you needed to do with them. If however it is too late and you have uninstalled your servers, you can still get the list of database names using a SQL query:

USE SharePoint_Config
select distinct name from Objects WITH(NOLOCK) where Name in (
select Name COLLATE Latin1_General_CI_AS_KS_WS from sys.databases WITH(NOLOCK))
GO

Replace SharePoint_Config with the name of configuration database for your farm. SharePoint products use Latin1_General_CI_AS_KS_WS collation so you need to do a cast. It is of course not supported to issue queries directly against SQL Server so I would use this approach for development environment only, and only when you are really cleaning it up and PowerShell isn’t an option.

Alternatively to avoid the mess in your database names from the start you can also pre-create them. Here is the guidance. Lastly, although the guidance does not suggest sharing database role between farms in production environment, it is quite practical to share a SQL server in development environment if you are not using your farm for performance and capacity testing.

UPDATE: on technet there is a list of databases used by SharePoint (a section in storage and SQL Server capacity planning article): http://technet.microsoft.com/en-us/library/cc298801.aspx#section1a