Catalogs built once. In each of the 2 full-text searches below, only one
database column is indexed and searched in each of the 2 tables. Tables
indexed every day.
Typical full-text query template follows.
best, paul
=============================================================
<!--- Revised Dec 4, 2002 Rev 7 --->
<!--- Replace [ with space --->
<cfset variables.searchterm = replace(variables.searchterm,"["," ","ALL")>
<!--- Replace ] with space --->
<cfset variables.searchterm = replace(variables.searchterm,"]"," ","ALL")>
<!--- Replace commas with space --->
<cfset variables.searchterm = rereplace(variables.searchterm,","," ","ALL")>
<!--- Remove some troublesome characters --->
<cfset variables.searchterm = rereplace(variables.searchterm, "[.]+", "",
"ALL")>
<cfset variables.searchterm = rereplace(variables.searchterm,
"[-()|&!@_+=;:<>?,{}/\]+", " ", "ALL")>
<!--- Remove any spaces at the edge --->
<cfset variables.searchterm = trim(variables.searchterm)>
<!--- Pound down multiple spaces to 1 between words --->
<cfset variables.searchterm =
rereplace(variables.searchterm,"[[:space:]]+"," ","ALL")>
<!--- Escape " ' " --->
<cfset variables.searchterm = rereplace(variables.searchterm,"'","''","ALL")>
<!--- Replace each space with "~" to create a character-delimited list --->
<cfif refindnocase(" ","#VARIABLES.SEARCHTERM#")>
<cfset variables_searchterm = rereplace(variables.searchterm,"
","~","ALL")>
<cfelse>
<cfset variables_searchterm = variables.searchterm>
</cfif>
<!--- Remove noise words, including "AND" and "OR" --->
<cfif listlen(variables_searchterm,"~") ge 1>
<cfloop list="#VARIABLES_SEARCHTERM#" index="III" delimiters="~">
<cfquery name="RemoveNoise" datasource="#REQUEST.DSN#"
username="#REQUEST.USER#" password="#REQUEST.PASS#">
SELECT N_Word FROM NoiseWords (nolock)
WHERE N_Word = '#III#'
</cfquery>
<cfif removenoise.recordcount>
<cfset variables_searchterm =
listdeleteat(variables_searchterm,listfindnocase(variables_searchterm,iii,"~"),"~")>
</cfif>
</cfloop>
<cfif listlen(variables_searchterm,"~") is 0>
<cflocation addtoken="No"
url="/IgnoredWords/index.cfm/IN.#VARIABLES.INIT#/CD.#URL.CFI#/CN.#URL.CFT#">
</cfif>
</cfif>
<!--- Replace "~" with "AND" or "OR" according to user's choice --->
<cfif refindnocase("~","#VARIABLES_SEARCHTERM#")>
<cfset variables_searchterm =
rereplace(variables_searchterm,"~","#LogicTerm#","ALL")>
<cfelse>
<cfset variables_searchterm = variables_searchterm>
</cfif>
<cfif len(variables_searchterm) gt 0>
<!--- Perform full-text search of main table --->
<cfquery name="SEARCHLISTINGS" datasource="#REQUEST.DSN#"
username="#REQUEST.USER#" password="#REQUEST.PASS#" blockfactor="100"
cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT ID
FROM afulltable (nolock)
<cfif
comparenocase("#REQUEST.GetClientDataCity.City#","All Cities")>
WHERE City = '#REQUEST.GetClientDataCity.City#'
AND ( CONTAINS(fulltext,'#VARIABLES_SEARCHTERM#'))
<cfelse>
WHERE CONTAINS(fulltext,'#VARIABLES_SEARCHTERM#')
</cfif>
</cfquery>
<!--- Set Main Table ID Result List for later retrieval --->
<cfif searchlistings.recordcount gt 0>
<cfquery datasource="#REQUEST.CLIENT_DSN#"
username="#REQUEST.CLIENT_USER#" password="#REQUEST.CLIENT_PASS#">
UPDATE ClientData
SET SearchList = '#ValueList(VeritySearchListings.ID)#'
WHERE CFI = #VAL(URL.CFI)#
AND CFT = #VAL(URL.CFT)#
</cfquery>
</cfif>
<!--- Perform full-text search of Yellow Page Headings table --->
<cfquery name="REQUEST.GetHeadings" datasource="#REQUEST.DSN#"
username="#REQUEST.USER#" password="#REQUEST.PASS#"
cachedwithin="#CreateTimeSpan(0,0,5,0)#">
SELECT ID,Class_ID,Class_Name,Pages
FROM YellowPages (nolock)
WHERE CONTAINS(Class_Name,'#VARIABLES_SEARCHTERM#')
ORDER BY Class_Name
</cfquery>
<cfset variables.hend = variables.hstart + request.headings.maxrows - 1 >
<cfif variables.hend gt request.getheadings.recordcount>
<cfset variables.hend = request.getheadings.recordcount>
</cfif>
<!--- Set Yellow Page Heading ID Result List for later retrieval --->
<cfif request.getheadings.recordcount gt 0>
<cfquery datasource="#REQUEST.CLIENT_DSN#"
username="#REQUEST.CLIENT_USER#" password="#REQUEST.CLIENT_PASS#">
UPDATE ClientData
SET HeadingList = '#ValueList(REQUEST.GetHeadings.ID)#'
WHERE CFI = #VAL(URL.CFI)#
AND CFT = #VAL(URL.CFT)#
</cfquery>
</cfif>
<cfelse>
<cfset searchlistings.recordcount = 0>
<cflocation addtoken="No"
url="/IgnoredWords/index.cfm/IN.#VARIABLES.INIT#/CD.#URL.CFI#/CN.#URL.CFT#">
</cfif>
==================================================
At 08:26 PM 12/16/02 -0600, you wrote:
>hehe... maybe - can you post some of your code? How are your catalogs built
>and how often?
>
>-Mark
>
>-----Original Message-----
>From: paul smith [mailto:[EMAIL PROTECTED]]
>Sent: Monday, December 16, 2002 8:10 PM
>To: CF-Talk
>Subject: RE: mySQL & CF
>
>
>Thanks!
>
>CFQUERYPARAM enabled me to reduce that pesky overhead using client vars in
>a database to maintain state from 32ms to 16ms.....
>
>Anything up your sleeve to reduce my SQL7 1,000ms-2,000ms full-text
>searches by 50% ?
>
>best, paul
>
>At 11:52 AM 12/16/02 -0600, you wrote:
> >While it's true that CFQUERYPARAM escapes characters, there's a bit more
> >going on under the covers. When you use CFQUERYPARAM you are assigning a
> >"type" to the variable that's passed to the RDBMS. This type information
> >insulates the item from such things as the infamous SQL query injection
> >attack. If the code that is rendered from using cfqueryparam where written
> >out in SQL it would look like this:
> >
> >-------------------------------
> >--declarations
> >DECLARE @item1 char(50)
> >DECLARE @item2 char(15)
> >
> >--assignments (these actually come from the "input")
> >SELECT @itme1 = 'blah'
> >SELECT @item2 = ') truncate table Mytable'
> >
> >-- insert
> >Insert into MyTable (item1, item2)
> > values (@item1, @item2)
> >--------------------------------------
> >
> >Note that in item2, the hacker has tried to pass a command to kill your
> >table. But because the variable @item2 is predefined as a character string
> >it can't be done. SQL will treat whatever is in @item2 as character data -
> >no matter how pernicious <g>. Incidentally, this is also why CFQUERYPARAM
> >is so much faster on MS SQL. the SQL server caches execution plans for
> >re-use. When you pass in a query that looks typical:
> >
> >insert into mytable (item1, item2)
> > values ('blah','blah')
> >
> >the execution plan is more likely to be unique - and therefore not in the
> >cache - because the 2 "value" items are part of the plan. Additionaly, SQL
> >must "lookup" the type to create the execution plan. So every insert
> >requires a new execution plan. When you use cfqeuryparam however, sql can
> >find an execution plan that is cached. That's because the actual plan will
> >not contain specific values but placeholders that are typed:
> >
> >insert into mytable (item1,item2)
> > values(@item1 char(10),@item2 char(40))
> >
> >This use of a saved execution plan reduces the "prepare" part of the SQL
> >process saving overhead. On a busy server this can cause an increase in
> >performance that is exponential. At least that's been my experience.
> >
> >-Mark
> >
> >
> >-----Original Message-----
> >From: Craig Dudley [mailto:[EMAIL PROTECTED]]
> >Sent: Monday, December 16, 2002 11:05 AM
> >To: CF-Talk
> >Subject: RE: mySQL & CF
> >
> >
> >cfqueryparam will escape potentially dangerous characters for you. This
> >is ESSENTIAL if you don't want to have your database dropped by some
> >nasty hacker type person.
> >
> >Search through the archives for SQL injection attacks, you'll soon see
> >why.
> >
> >It will also negate the need to escape quotes and other things manually,
> >which is quite handy too.
> >
> >Trust me, cfqueryparam is your friend ;-)
> >
> >Craig.
> >
> >-----Original Message-----
> >From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
> >Sent: 16 December 2002 16:51
> >To: CF-Talk
> >Subject: RE: mySQL & CF
> >
> >Hi, Craig, and thanks for the reply.
> >
> >When you say they "make things a lot more secure."
> >What exactly do you mean? Boy, that's a lot of extra typing
> >over the typical CFINSERT syntax...
> >
> >Rick
> >
> >
> >-----Original Message-----
> >From: Craig Dudley [mailto:[EMAIL PROTECTED]]
> >Sent: Monday, December 16, 2002 11:10 AM
> >To: CF-Talk
> >Subject: RE: mySQL & CF
> >
> >
> >Standard SQL inserts will work fine on most if not all RDBMS's
> >
> >Eg.
> >
> >insert into tablename
> >(int_col1,varchar_col2)
> >values
> >(<cfqueryparam cfsqltype="CF_SQL_INTEGER"
> >value="#form.val1#">,<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> >value="#form.val2#">)
> >
> >Do try to use the cfqueryparams, they make things a lot more secure.
> >
> >-----Original Message-----
> >From: Rick Faircloth [mailto:[EMAIL PROTECTED]]
> >Sent: 16 December 2002 16:02
> >To: CF-Talk
> >Subject: RE: mySQL & CF
> >
> >Hi, Matt.
> >
> >I, too, learned about the CFUPDATE problem from personal experience
> >and from the Allaire forums when I first started using CF (with Access
> >at
> >that time).
> >I stopped using it and went to the CFQUERY...Set... approach. That's
> >worked
> >fine.
> >
> >I haven't had any problems with CFINSERT, but if that may be problematic
> >in
> >the future
> >I may as well go ahead and change my coding habits now.
> >
> >How is the INSERT coded for mySQL and CF?
> >Example?
> >
> >Thanks,
> >
> >Rick
> >
> >
> >-----Original Message-----
> >From: Matt Robertson [mailto:[EMAIL PROTECTED]]
> >Sent: Monday, December 16, 2002 1:11 AM
> >To: CF-Talk
> >Subject: RE: mySQL & CF
> >
> >
> >Rick,
> >
> >I noticed you mention that you use CFINSERT. From your earlier postings
> >I
> >know you are on CF 4.5x, as I am.
> >
> >You're likely to discover that CFINSERT and/or CFUPDATE sometimes blow
> >sky-high on CF 4.5x (at least) when working with mySQL (3.23 for sure,
> >and
> >maybe 4.0x). In threads on the subject over at the (then) Allaire
> >forums no
> >specific cause was ever traced. It just happens. Sometimes.
> >
> >In one of those threads I believe it was Paul Hastings who advised me to
> >'just say no to cfinsert/cfupdate' and it ranks as some of the best CF
> >advice I ever got. On the surface those tags appear to be handy
> >shortcuts,
> >but they black-box your SQL, take away the otherwise granular control
> >you
> >should have and make debugging ... difficult.
> >
> >I suggest you follow the same advice -- you'll probably find out you
> >have
> >to, anyway. While you're at it go for the double bonus and implement
> >cfqueryparam.
> >
> >Happy Monday (early) :)
> >
> >---------------------------------------
> >Matt Robertson, MSB Designs, Inc.
> >http://mysecretbase.com - Retail
> >http://foohbar.org - ColdFusion Tools
> >---------------------------------------
> >
> >
> >
> >
> >
> >
> >
> >
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
