Getting Custom Rules 'pages' into Sitemap?

Oct 23, 2014 at 2:59 AM
How should we go about getting the pages defined by Custom Rules to list in the sitemap.aspx listing (using the URL Sitemap provider)

Is there a simple way to configure this, or does something that has to be created?

I'm using the custom rules to rewrite a list of articles currently displayed with url parameters.
url.com/library/article?id=123
(becomes)
url.com/article/concierge-medicine-services

What I want to have is the sitemap listing: 'url.com/article/concierge-medicine-services'
Coordinator
Oct 23, 2014 at 2:09 PM
Hi,

You mean custom rules NOT generated by a provider ?

Custom rules are calculated on the fly, so they can't generate a sitemap.

You need to creata a open url procvider.
Then all url's generated by the provider are integrated in the sitemap.

Regrds,
Sacha
Oct 24, 2014 at 4:00 AM
I've studied the Providers documentation, and have a question...

Do you think it's possible to pull the re-written URL from a query of a table which provides the page name?

For example:

I have a DNN SQL view which has the articleID and the ArticleTitle in it. For the page parameter article?a=123 the articleID 123 has a title of 'this-article-title'

I'd like the provider to rewrite the ?a=123 as /this-article-title

Do you think that is possible?

(you can tell I dont write C#)
Coordinator
Oct 24, 2014 at 6:58 AM
Yes, that the objectif of the providers.
The only thing you need more, to write a provider, is the tabid on whith the URL need to ne rewriten. Tipically it is the page on which the module is installed .
Which module are you using ?

Yes need to know c# or VB.net
Oct 30, 2014 at 12:55 AM
OK. I know some VB and can struggle through this. What might you like as a contract fee to write this for us?
Coordinator
Oct 30, 2014 at 1:28 PM
Hi,

Can we have a skype session, so you can show me more in details want you need ?

I send you a private message with my skype.

Regards,
Sacha
Nov 1, 2014 at 8:15 PM
Odd - I replied to your email from codeplex (here) and it bounced with a message that my email isn't a 'registered' email. In any case, yes, lets schedule a skype.

I'll send along a request.
Nov 10, 2014 at 10:44 PM
Are you available for a skype session this week?
Coordinator
Nov 11, 2014 at 9:20 AM
Yes,

Where are you from ? In the europe timezone ?

We can do this today if you want or tell me when it's good for you.

Reagrds,
Sacha
Nov 11, 2014 at 3:42 PM
Right now it's 5:39 PM for you and 8:39 AM for me. I'm near Portland Oregon, PST. You're 9 hours ahead of me. So we can connect after your workday should you be available before the end of the (any) day.

Daniel
Coordinator
Nov 11, 2014 at 5:43 PM
Is it ok for you in 1h and 20 min (9 pm for me) ?
Nov 11, 2014 at 6:12 PM
Sure - that is 12PM here. There's quite a delay in the emails being sent. I just got it and it's now 8:09 PM for you. (30 minute email batches?)

I'm on Skype - just ring me.
Nov 11, 2014 at 7:18 PM
danielcomp
Coordinator
Nov 16, 2014 at 7:53 PM
Hi,

I just release a generic SQL Provider for OpenUrlRewriter wich can do your job.

Download

More info

Regards,
Sacha
Nov 17, 2014 at 2:09 AM
Awesome.

I've set it up and have good results in the sitemap, and after some fiddling with the 'Destination' field and the site cache, I get the URL re-written quite nicely.

My SQL vw :
SELECT DISTINCT CAST('687' AS INT) AS TabId, Title AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters
FROM dbo.vw_ArticleList

The URL re-writer Rule View lists the Destination as 'a/284' or 'a/285'
Is this correct?

HOWEVER the final path contains the page name (/Articles/...) since I rewrote the path in the URL Rewriter for that page to remove the 'library' parent.

Is there a way to remove the entire path 'http://url.com/LIBRARY/ARTICLE/ that comes before the NEWTITLEURL. I tried a query with the Destination URL in the query as '../' and variations of that without luck. Going to the root seems to not be allowed (maybe because I have 'remove home page' also selected?)

We can do another SKYPE and I can demonstrate.

Now to conquer the DNN Token replacement in the title!
Coordinator
Nov 17, 2014 at 8:38 AM
The URL re-writer Rule View lists the Destination as 'a/284' or 'a/285'
Is this correct?
yes, be default openurlrewriter create a redirection rule for rewrite rules based on the content of the destination.
www.mysite.com/page/a/285 is automatically redirected to www.mysite.com/page/url
Is there a way to remove the entire path ?
You have to set RemoveTab to true in you SQL Query
Look at the "more info"
you must be sure the urls of your articles can not have the same url as a page of your website.
So, to use witch many attention
DNN Token replace
Maybe you can handle this in the SQL query ?
query the site settings and do a sql replace ?
let me know
Nov 17, 2014 at 11:08 PM
1) Thanks for the confirmation
2) yes, article IDs are in similar ranges that tabs are - so no go on that one.
3) SQL query now handles the token replacement

Case closed - well done Sacha!

I'll email the rest.
Nov 18, 2014 at 12:03 AM
Edited Nov 18, 2014 at 1:19 AM
Applying a similar setup to a video rather than an article, I'm getting pages re-written correctly, but on the destination tab a video module requires a parameter (videoid=123, or video/123) in order to filter and play the correct video. Is that any way you can think of to still get the video title into the sitemap, and yet build a string into the URL?

http://url.com/video/the-title-of-the-video-from-openurlrewriter/videoid/123

I can get the string into the query, but the / is taken out. Maybe I dont understand the RedirectDestination which seems to be hinting at this -

"Remark : For Rewrite rules, the RedirectDestination field can contain a url. This generate automaticaly a redirection from this url to the url field (automatic reverse redirection). If it is emply, a redirect rule is automaticaly generated from the parameters (for exemple aricleID=12 -> articleID/12)"

maybe we could use a 'special' character that gets replaced with a '/' or one for both an '?' and a '='

Your thoughts?
Nov 18, 2014 at 5:40 AM
Edited Nov 18, 2014 at 5:52 AM
This might be an issue to look at.

I have 2 DNN SQL queries.

One has 48 (from the local DNN DB) records including these:
tabid title
687 CITYNAME General Care Screenings a=238
687 CITYNAME Brain Wellness Screening a=239
687 CITYNAME Blood Pressure Screening a=241
687 CITYNAME Concierge Medicine Services a=242

The other (from a remote database) has 79 results including these:

761 MD CITYNAME, ST General Care Screenings md=238
761 MD CITYNAME, ST Brain Wellness Screening md=239
761 MD CITYNAME, ST Blood Pressure Screening md=241
761 MD CITYNAME, ST Concierge Medicine Services md=242

HOWEVER

The Rules View shows many less records (96 total) and all the titles start with the same prefix 'md-'

Module 687 a=242 Rewrite md-cityname-concierge-medicine-services a/242 0
Module 687 a=241 Rewrite md-cityname-blood-pressure-screening a/241
Module 687 a=239 Rewrite md-cityname-brain-wellness-screening a/239 0
Module 687 a=238 Rewrite md-cityname-general-care-screenings a/238 0

Module 761 md=242 Rewrite md-cityname-st-concierge-medicine-services md/242 0
Module 761 md=241 Rewrite md-cityname-st-blood-pressure-screening md/241 0
Module 761 md=239 Rewrite md-cityname-st-brain-wellness-screening md/239 0
Module 761 md=238 Rewrite md-cityname-st-general-care-screenings md/238 0

I deleted all the host sql queries and checked that the rewriter database table was clean, I added the first query again, and checked the 79 returned records did not have the md- prefix. As soon as open the url rewriter, the first set of titles (79) only show 46 of the 127 (79 plus 48) and they get renamed with the 'md-' prefix. I have no idea where that is coming from. It's not in the query.
Coordinator
Nov 18, 2014 at 10:51 AM
Hi,

I can't really good understand what's hapen.

Do you include the @portalid parameter in the queries ?
Coordinator
Nov 18, 2014 at 10:52 AM
intelligent wrote:
Applying a similar setup to a video rather than an article, I'm getting pages re-written correctly, but on the destination tab a video module requires a parameter (videoid=123, or video/123) in order to filter and play the correct video. Is that any way you can think of to still get the video title into the sitemap, and yet build a string into the URL?

http://url.com/video/the-title-of-the-video-from-openurlrewriter/videoid/123

I can get the string into the query, but the / is taken out. Maybe I dont understand the RedirectDestination which seems to be hinting at this -

"Remark : For Rewrite rules, the RedirectDestination field can contain a url. This generate automaticaly a redirection from this url to the url field (automatic reverse redirection). If it is emply, a redirect rule is automaticaly generated from the parameters (for exemple aricleID=12 -> articleID/12)"

maybe we could use a 'special' character that gets replaced with a '/' or one for both an '?' and a '='

Your thoughts?
Yes, a will look for a solution to not replace '/' in the urls.
Nov 19, 2014 at 2:42 AM
I've tried "and need one parameter @PortalId to return only the record corresponding to one portal" in my query several variations. Each time I get an error "Must declare the scalar variable "@PortalId". The query I'm using pulls data from a single moduleID, so the records are unique. however, the ArticleIDs are similar to articles in other modules/portals.

I'm missing something... any pointers?

maybe a skype session together again?
Nov 20, 2014 at 5:45 PM
Are you available for a skype session so i can show you the results I have?
Nov 20, 2014 at 7:28 PM
Fabulous results - thanks for the session and the on-the-spot adjustment for the additional connection string. Well done!

I also appreciate the heads'up on the double server trips to process the link - rewrite + redirect. I'm fixing that in the templates.

Again - thank you for the expertise and the fabulous outcome.
Nov 22, 2014 at 6:29 AM
Edited Nov 22, 2014 at 6:50 AM
I've run into another small issue.

I'm adding a OpenUrlRewriter SQL query to each portal, and as I add them, all the sites compound the rewrite rules - so that every site has the TOTAL or the SUM of ALL SQL query records as rules. There are 79 articles in each portal - so the Module Rewrite shows an additional 79 rewrites for each portal I add.

When I 'View Rules' I see 2268 records (28 queries x 79 records) - having added 28 queries so far.
The Url rules cache has a list of 28 Type Culture TabId Url s

The links to the page seem to work, but I'm not sure if the compounding will show the rewriting process...?

Thanks for looking into this.
Coordinator
Nov 22, 2014 at 7:05 PM
Hi,

If you have one query for each portal you have to include the portal parameter on the query, so that open url rewriter generates only the url rules for the corresponding portal.

In the View Rules and Rules cache , normally you see only the rules of the current portal.

Regards,
Sacha
Nov 23, 2014 at 12:26 AM
I've changed my query to use @PortalID
This is my query:

DECLARE @PortalID INT;
SET @PortalID = '1';
SELECT DISTINCT CAST('687' AS INT) AS TabId, PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)

When I run this query, I get the 79 articles (as expected)

However - the View Rules and Rules Cache now show no results

How would you write the query to include the @PortalID?
Coordinator
Nov 23, 2014 at 8:34 AM
Hi,

I think you just can add
0 as portalid
to your select :

SELECT DISTINCT CAST('687' AS INT) AS TabId, 1 as PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)
Nov 23, 2014 at 6:22 PM
Edited Nov 23, 2014 at 6:49 PM
The SQL requires
DECLARE @PortalID INT;
ERROR WITHOUT IT
"Must declare the scalar variable "@PortalID"." BUT - getting around that error - I get 0 data results from these queries.

DECLARE @PortalID INT;
SELECT DISTINCT CAST('687' AS INT) AS TabId, 0 AS PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)

or

DECLARE @PortalID INT;
SELECT DISTINCT CAST('687' AS INT) AS TabId, 1 AS PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)

or

DECLARE @PortalID varchar(20);
SELECT DISTINCT CAST('687' AS INT) AS TabId, 0 AS PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)

AND by adding WHERE OR (ModuleID = '') I get the SQL to have results (79) but the URL rewriter still shows SqlUrlRuleProvider (0)

DECLARE @PortalID INT;
SELECT DISTINCT CAST('687' AS INT) AS TabId, 0 AS PortalID, REPLACE (Title, '[Portal:Keywords]', 'CITYNAME') AS Url, 'a=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_ArticleList WHERE (portalID = @PortalID)
OR (ModuleID = '2146')

Maybe a TAB inside the URLrewriter module to select the SQL query - rather than '@PortalID'?
Nov 23, 2014 at 8:45 PM
Edited Nov 23, 2014 at 9:37 PM
The PortalID or PortalId seems to be case sensitive - yes?

The only combination of SQL query (that returns data) and the number of records in the SqlUrlRuleProvider > (0) is when I completely leave out the WHERE (PortalId = @PortalId). All other attempts (across 150 portals) to include the @PortalID produce either no data from the query, - or - (if data from query then) no records in SqlUrlRuleProvider (0)

When I leave the @PortalID out - I see that the Sitemap lists the correct (79 added articles) query results, but the SqlUrlRuleProvider has (2987). It lists ALL articles in ALL queries COMBINED. And, the links in the SQL reports modules are only created from the first query in the list. - not the proper query for the portal.

That's why I'm wondering if we can SELECT the query for each portal rather than trying to grab it dynamically from the @PortalId parameter (unless we find a flaw like the case for PortalID or PortalId)

"Maybe a TAB inside the URLrewriter module to select the SQL query - rather than '@PortalID'?"
Nov 25, 2014 at 11:50 PM
Does this error provide any clues?

A SQl Reporst module on the article page returns this the first time I visit the page.

There was an error while executing the configured data source: An error occurred while executing the data source (Code: -2146232060): The parameterized query '(@PortalID int,@TabID int,@ModuleID int,@UserID int,@url_a nvarc' expects the parameter '@url_a', which was not supplied.
Nov 27, 2014 at 4:06 AM
Here's the observation from another day of testing:

THIS QUERY PRODUCES CORRECT DATA (57 records) >
DECLARE @PortalId AS INT;
SET @PortalId = 2 (note: or any number)
SELECT DISTINCT TabId, PortalId, Url, 'md=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_OpenURLrewriter
WHERE PortalId = @PortalId

IT DOES NOT HAVE ANY RECORDS IN THE open-url-rewriter
SqlUrlRuleProvider (0)


THIS QUERY PRODUCES ALL DATA (4788 records) >
SELECT DISTINCT TabId, PortalId, Url, 'md=' + CAST(NA AS varchar(20)) AS Parameters FROM dbo.vw_NA_OpenURLrewriter

IT HAS ALL RECORDS IN THE open-url-rewriter
SqlUrlRuleProvider (4788)

Image