OK, sorry I wasn't able to get this out sooner, but as promised, here's how to get around needing Full Text support on your database:
First, under the "web/install/scripts" folder, copy the "storedprocedures.sql" file, and paste it into the same directory - renaming it to "storedprocedure_nofulltext.sql". Then open it in your favorite text editor, and find the definition for the "dashCommerce_Store_ProductSearch" stored procedure. Replace that definition with the one below and save the file:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dashCommerce_Store_ProductSearch]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
CREATE PROCEDURE [dbo].[dashCommerce_Store_ProductSearch]
@searchTerm nvarchar(100)
-- searchTerm: String of 1 or more search terms, all separated by spaces
-- Results are returned in order of relevance
AS
BEGIN
DECLARE @i1 int;
DECLARE @i2 int;
-- MatchType: 0=match any, 1 =match all, 2 = exact match of entire expression only
DECLARE @MatchType int;
SET @MatchType = 0
DECLARE @Word varchar(100);
DECLARE @Words table (Word varchar(100) not null);
DECLARE @WordCount as integer;
SET NOCOUNT ON
-- Parse the searchTerm to extract all words:
IF (@MatchType != 2)
BEGIN
SET @searchTerm = '' '' + @searchTerm + '' '';
SET @i1 = 1;
WHILE (@i1 != 0)
BEGIN
SET @i2=charindex('' '', @searchTerm, @i1+1)
IF (@i2 != 0)
BEGIN
SET @Word = RTRIM(LTRIM(SUBSTRING(@searchTerm, @i1+1, @i2-@i1)))
IF @Word != '''' INSERT INTO @Words SELECT @Word
END
SET @i1 = @i2
END
END
else
INSERT INTO @Words SELECT LTRIM(RTRIM(@searchTerm))
-- Get the total # of words:
SET @WordCount = (SELECT COUNT(*) FROM @Words)
-- Return Results in order of relevance:
SELECT
T.*, a.MatchPct
FROM
dashCommerce_Store_Product T
INNER JOIN
(
SELECT
ProductID, COUNT(*) * 1.0 / @WordCount AS MatchPct
FROM
dashCommerce_Store_Product T
INNER JOIN
@Words W ON (
'' '' + T.[Name] + '' '' LIKE ''%[^a-z]'' + Word + ''[^a-z]%''
OR
'' '' + T.[ShortDescription] + '' '' LIKE ''%[^a-z]'' + Word + ''[^a-z]%''
OR
'' '' + T.[BaseSku] + '' '' LIKE ''%[^a-z]'' + Word + ''[^a-z]%''
)
GROUP BY
ProductID
) a ON T.ProductID = a.ProductID
WHERE
MatchPct = 1 OR @MatchType <>1
ORDER BY
MatchPct
END
'
END
GO
A couple quick notes about this procedure:
- It was taken from an example found here:
- Although it looks like there are double quotes (") in this script, there isn't - rather they are apostrophes (') - but they needed to be escaped because the script was failing when called through web install. So what would normally be a single apostrophe (') becomes two ('') in this sql file.
- It supports searching on multiple words - the script splits the words apart, and performs a search on each one.
OK, now the only thing left is to add an "appSetting" to the web.config:
<appSettings>
<add key="useFullTextIndex" value="false"/>
</appSettings>
... and finally, to modify the "database.ascx.cs" file under "web/install/controls". First, within the "Constants" region, add this line:
private const string SCRIPT_SPS_NO_FULL_TEXT = "~/install/scripts/storedprocedures_nofulltext.sql";
Then, modify the RunScripts method to look like this:
private void RunScripts(string connectionString) {
//get setting
bool useFullText = Boolean.Parse(System.Web.Configuration.WebConfigurationManager.AppSettings["useFullTextIndex"]);
//Drop
string[ dropStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_DROP), new System.Text.UTF8Encoding()));
ExecuteStatements(dropStatements, connectionString);
//Tables
string[ tableStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_TABLES), new System.Text.UTF8Encoding()));
ExecuteStatements(tableStatements, connectionString);
if (useFullText)
{
//Full Text Catalog
string[ fullTextStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_FULL_TEXT_CATALOG), new System.Text.UTF8Encoding()));
ExecuteStatements(fullTextStatements, connectionString);
}
//Functions
string[ functionStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_FUNCTIONS), new System.Text.UTF8Encoding()));
ExecuteStatements(functionStatements, connectionString);
//Views
string[ viewStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_VIEWS), new System.Text.UTF8Encoding()));
ExecuteStatements(viewStatements, connectionString);
//Stored Procedures
string spScript = useFullText ? SCRIPT_SPS : SCRIPT_SPS_NO_FULL_TEXT;
string[ storedProcedureStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(spScript), new System.Text.UTF8Encoding()));
ExecuteStatements(storedProcedureStatements, connectionString);
//Base Data
string[ baseDataStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_BASE_DATA), new System.Text.UTF8Encoding()));
ExecuteStatements(baseDataStatements, connectionString);
//Membership
string[ membershipStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_MEMBERSHIP_SCHEMA), new System.Text.UTF8Encoding()));
ExecuteStatements(membershipStatements, connectionString);
//Base Membership Data
string[ baseMembershipDataStatements = GetScriptStatements(File.ReadAllText(Server.MapPath(SCRIPT_BASE_MEMBERSHIP_DATA), new System.Text.UTF8Encoding()));
ExecuteStatements(baseMembershipDataStatements, connectionString);
}
There's really only a few lines that are changed in that method, I just thought it would be easier to post the whole thing so you could copy/paste if you want.
That's it. If the "useFullTextIndex" setting is "false" - then the web install will skip executing the "fulltextcatalog.sql" file, and call our custom "storedprocedures_nofulltext.sql" file instead of the default one.
I'm pretty sure I got all the steps in there, please let me know if it helps you out.
@wlfman2k1: For me, all I had to do was set my subdirectory to be a virtual directory - and set it as the application root. See one of my previous posts in this thread for the exact steps.