Helicon Ape Guidance - mod_rewrite & mod_dbd

[GTN Profile]

Hello All,

I'm just looking for a little guidance at the moment - on a couple questions.

Q1

My site has search result pages & detail pages - both are in the same directory 'jobs/' the search result pages end index.asp? whereas the detail pages end detail.asp?

I have a SQL view that returns all combinations of the SEO_URL for the search results pages it returns 7,600,000 possible combinations like -

'accountancy/aberdeenshire/aberdeen/full-time/contract/' the combinations are fairly specific and unlikely to change - so to speed things up, I could index the SQL view.

On the other hand the detail.asp pages change on a monthly basis, content is only ever live for a maximum of 28 days. The view on this only returns max 50,000 results - so it is fast but I can't create an indexed view. So really can't combine the two.

Is it possible to use mod_dbd twice in the same directory? if so how do I go about doing this?

Q2

Taking the above as an example, my SQL view provides the following

SEO_URL = accountancy/aberdeenshire/aberdeen/full-time/contract/
OrginalURL = index.asp?sector=1&region=1&location=1&jobtype=1&hours=2

So in this instance the optimised URL would read

/jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/

The real URL would read

/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2

I also have other parameters that get sent, but should not be optimised, these are

keywords=
and
offset=
and
pagesize =

so using the optimised URL above - the full URL would read -

/jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/?keywords=tax&offset=25&pagesze=50

When i add these to the end of the optimised URL - i see no change, i.e instead of getting

/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2?keywords=tax&offset=25&pagesze=50

I get

/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2

How do I work around this

Finally I've not been able to get mod_bd to work, I've set it up as such -

Code: Select all

DBDriver mssql
DBDParams "Data Source=***.***.***.***,1433;Initial Catalog=DATABASENAME;User ID=DATABASEUSERNAME;Password=DATABASEPASSWORD"
DBDPrepareSQL "select OriginalURL from seo_mapping where `SEO_URL` =@KEY" seo_map_select
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

the SQL Server is on the same server - can i use LOCALHOST instead of ***.***.***.***,1433

Any idea as to why the above is not working?

Sorry - lots of questions - appreciate any support that you can offer.

Thank you

[Yaroslav]

Q1: This is example from documentation on mod_rewrite RewriteMap directive:

Code: Select all

DBDriver mssql
DBDParams "Data Source=server;Initial Catalog=database;User ID=user;Password=password"
DBDPrepareSQL "select OriginalURL from seo_mapping where `SEO_URL` =@KEY" [b]seo_map_select[/b]

RewriteEngine On
RewriteMap map_dbd dbd:[b]seo_map_select[/b]
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

As you can see each DBDPrepareSQL directive declares an SQL request and map it to a name, that can be referenced in RewriteMap directive. You just need to declare two SQL requests with different names.

[GTN Profile]

Hi Yaroslav,

Thanks for your response.

Just to confirm, the maple can include the file name and extension, like so?

SEO_URL = accountancy/aberdeenshire/aberdeen/full-time/contract/
OrginalURL = index.asp?sector=1&region=1&location=1&jobtype=1&hours=2

And I can have two SQL requests with different names, how will Ape know which one to use, if the first is like the above example and the second is like

SEO_URL = accountancy/aberdeenshire/aberdeen/12345/examplepost/
OrginalURL = detail.asp?id=12345

Also how do i add the additional parameters (Q2)

/jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/?keywords=tax&offset=25&pagesze=50
/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2

I've opened a support ticket for these items - Your ticket id: [ape-22624]

Grateful for your assistance.

Thank you

[Yaroslav]

Q2:
I suppose you are confusing a direction of the rewrite. If you wish to have SEO-friendly URLs then the customer will enter (link to) something like '/jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/?keywords=tax&offset=25&pagesze=50' and Ape should rewrite it internally to '/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2?keywords=tax&offset=25&pagesze=50' so your backend gets a required request format.
I guess your SQL view should map 'jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/' to 'jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2' and the @KEY here is the first string (note the trailing slash is omitted).
I suggest you to try using Ape debugger - in the Ape Manager select site, Tools -> Start Debugger, this tool may be very helpful when developing configuration.

To add any query string parameters to the rewritten URL add QSA flag to the RewriteRule directive.

[Yaroslav]

Q1:

A map is simply a string to string map, in theory, it can include anything you like since you can rewrite to it. However, it may be better for performance to write several rules (instead of one) that can distinguish between some groups (classes) of request by using some simple triggers, like you can make SEO URLs to look like: 'home/accountancy/aberdeenshire/aberdeen/full-time/contract/' and 'details/accountancy/aberdeenshire/aberdeen/12345/examplepost/' so the rules will match these triggers instead of mapping 7 600 000 variants each time.
If there are no triggers then you can rely on "NOT_FOUND" functionality. I.e. try to run one SQL request against the URL first (I suppose the quickest one). If request returned no result the rule will not match and will proceed to next one. If the result was satisfying 'L' flag in request processing makes sure no further rules will apply. Please note that same as with Apache each time rewrite happens the URL will be processed through entire config again, so if your rules are CPU expensive you may want to add 'NS' flag to RewriteRule directive, to prevent it from executing on sub-requests.

[GTN Profile]

Hi,

For me there are two problems here, firstly, when I try either

Code: Select all

# Helicon Ape version 3.0.0.76

DBDriver oledb
DBDParams "Driver=SQLOLEDB;Server=***.***.***.***,1433;Database=dbname;User=usname;Password=password;"

DBDPrepareSQL "select OriginalURL from dbo.seo_mapping where `SEO_URL` =@KEY" seo_map_select

RewriteEngine On

#RewriteRule ^hampshire/$ index.asp?region=30 [NC,L]

RewriteEngine On
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

or


# Helicon Ape version 3.0.0.76

Code: Select all

DBDriver mssql
DBDParams "Data Source=***.***.***.***,,1433;Initial Catalog= dbname;User ID= usname;Password= password"
DBDPrepareSQL "select OriginalURL from dbo.seo_mapping where `SEO_URL` =@KEY" seo_map_select

RewriteEngine On

#RewriteRule ^hampshire/$ index.asp?region=30 [NC,L]

RewriteEngine On
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

I get this -

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

I have checked the logs and can't see any detailed message

The SQL is correct - when i run the same statement in SSMS it returns results. the database connections is correct, it is the same settings that the site uses.

When i try the debugger i get the same 500 error - with no detailed error message.

For Q1 the urls really need to look like;

accountancy/aberdeenshire/aberdeen/full-time/contract/
accountancy/aberdeenshire/aberdeen/12345/examplepost/

So if possible the trigger could be that the second contains numbers - I could include id for instance '...deen/id12345/exam....'

Secondly I'm not sure I follow your answers to Q2?

Any chance you can jump on the server and see what is going on? I've still got 100 of my 120 minutes of support left, and happy to buy more...

[GTN Profile]

Yaroslavl,

An update for you, I have reviewed the server logs, when I use this;

Code: Select all

RewriteEngine On

#RewriteRule ^hampshire/$ index.asp?region=30 [NC,L]

and go to

/jobs/hampshire/

I get

cs-uri-stem | cs-uri-query
/jobs/index.asp | region=30

When I try this;

Code: Select all

DBDriver oledb
DBDParams "Driver=SQLOLEDB;Server=***.***.***.***,1433;Database=dbname;User=usname;Password=password;"

DBDPrepareSQL "select OriginalURL from dbo.seo_mapping where `SEO_URL` =@KEY" seo_map_select

RewriteEngine On

#RewriteRule ^hampshire/$ index.asp?region=30 [NC,L]

RewriteEngine On
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

Or this

Code: Select all

DBDriver mssql
DBDParams "Data Source=***.***.***.***,,1433;Initial Catalog= dbname;User ID= usname;Password= password"
DBDPrepareSQL "select OriginalURL from dbo.seo_mapping where `SEO_URL` =@KEY" seo_map_select

RewriteEngine On

#RewriteRule ^hampshire/$ index.asp?region=30 [NC,L]

RewriteEngine On
RewriteMap map_dbd dbd:seo_map_select
RewriteCond ${map_dbd:$1|NOT_FOUND} (.*)
RewriteCond %1 !NOT_FOUND
RewriteRule (.+) %1 [L]

I get

cs-uri-stem | cs-uri-query
/jobs/hampshire/ | -

This would indicate to me that mod_bd is not running or something.

The connection settings that I have applied are correct.

I really need your assistance with this please.

Thank you

[GTN Profile]

Yaroslavl,

I have managed to identify the issue with dad_mod. Your example code is from dbo.seo_mapping where `SEO_URL`, you can't use ` like this in SQL.

This

from dbo.seo_mapping where SEO_URL

Works.

However please can you provide guidance and examples on the following -

For Q1 the urls really need to look like;

accountancy/aberdeenshire/aberdeen/full-time/contract/
accountancy/aberdeenshire/aberdeen/12345/examplepost/

And

Also how do i add the additional parameters (Q2)

/jobs/accountancy/aberdeenshire/aberdeen/full-time/contract/?keywords=tax&offset=25&pagesze=50
/jobs/index.asp?sector=1&region=1&location=1&jobtype=1&hours=2&keywords=tax&offset=25&pagesze=50

Thanks for your help & support

[Yaroslav]

Answered by e-mail.

[Yaroslav]

Answered by e-mail.