Replacing default module titles in search results with the corresponding tab’s title

How many times has it happened to you? You put some Text/HTML module here, a Links module there, maybe a third-party module and you forget to change its title, mostly because you’re using a container that doesn’t utilize it, or for any other reason.

 

This can lead to ugly search results, since DNN’s indexer stores the module’s title in the SearchItem table and uses it as the title for each one of your search results.

 

On the other hand, you’ve got some module titles you’ve explicitly set and you need to preserve for your search results. So, you’ve only got to replace the DEFAULT titles with something – in my script, I chose to replace them with the corresponding tab’s title, but you could alter it and make it display anything – or even delete the record if you like.

 

So here’s a trigger that checks whether the row being written in the SearchItem table is for a module having the default title, and if so, changes the title to the corresponding tab’s title.

 

CREATE TRIGGER tr_FixSearchItemTitle 
   ON  dbo.SearchItem
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
--These three variables will come from the
--searchitem table.
declare @searchitemid int
declare @moduleid int
declare @title nvarchar(200)

--These two will come from the 
--modules table.
declare @moduletitle nvarchar(200)
declare @moduledefid int

--This will come from the 
--moduledefinitions table
declare @friendlyname nvarchar(200)

--This will come from the
--tabs table
declare @tabtitle nvarchar(200)

--Get inserted values
select 
      @searchitemid = searchitemid
    , @title = title
    , @moduleid = moduleid 
from 
    inserted 

--Find moduletitle and definition id of the 
--module being inserted in searchitems table.
select 
      @moduletitle = moduletitle
    , @moduledefid=moduledefid 
from 
    modules 
where 
    moduleid = @moduleid

--Find the friendly name from the
--moduledefinitions table
select 
    @friendlyname = friendlyname 
from 
    moduledefinitions 
where 
    moduledefid = @moduledefid

--If the title of the module in the searchitem table
--is equal to the module definition's friendly name
--then we can safely suppose that the module has 
--the default title.
if @friendlyname = @title 
begin
    
    --Get the tab's title
    select 
        @tabtitle = title 
    from 
        tabs 
    where tabid in 
        (
        --If we have multiple instances
        --of modules in several pages,
        --just get the first page. I know,
        --this might be ugly but I've not found
        --any other way.
        select 
            top 1 (tabid) 
        from 
            tabmodules 
        where 
            moduleid = @moduleid
        )

--Replace the default title with the page's title.
update 
    searchitem 
set 
    title = @tabtitle 
where 
    searchitemid = @searchitemid

end

END
GO

 

This trigger checks each entry in the SearchItem table at the time it’s inserted or updated and determines whether the module title being inserted is a default title. It achieves that by comparing the module’s title with the FriendlyName field of the ModuleDefinitions table – all core modules and all third-party modules I know use this value as the default title. This means the trigger will probably work with any combination of modules you’ve installed in your site.

 

There’s a catch, though: If you have the same module instance (not the same module, the exact same instance – that means you’ve used the “add existing module” option) in more than one page, it’ll get only one title for it – that is, if you’ve “added an existing module” to several pages, each search result that corresponds to such a module may have the wrong title – no problem if you’re not using multiple instances of modules.

 

Another catch is that you have to have recursive triggers disabled for your database, or this will execute forever – it will run itself again and again since it alters a newly inserted or updated record.

 

In order to see how this trigger handle things, you should delete everything from your SearchItem table and then do a reindex via the Host->Search Admin page, or else the trigger will run only for new or updated entries.

 

As always, use at your own risk!

Read more...

Resolving "fileid=xxx" field values to actual file paths

Following a previous article of mine, The dbo.tabs.IconFile field issue, I would like to share an easy way to resolve any field that holds values of the type "FileID=xxx" (where xxx is the primary key of a record in the Files table) into the actual file path. This is especially useful when you deal with the IconFile field in the Tabs table, as well as with fields of type Image in a User Defined Table, and probably in a lot more places too.

 

I have created a UDF (User Defined Function) which accepts a string value and looks whether it's of type "FileID=xxx". If it is, it constructs the full path (including the portal number) and returns that, otherwise it just returns the initial string with no modification at all. You can use this scalar-valued function inline, in your own SELECT statements, like this:

 

EXAMPLE

SELECT 
tabid, 
tabname, 
dbo.ResolveFileField(iconfile) 
FROM tabs 

 

CODE

CREATE FUNCTION 
dbo.ResolveFileField
(
    @file varchar(1000)
)
RETURNS varchar(1000)
AS
BEGIN
    
declare @retval varchar(1000)
declare @portalid int

if patindex ('FileID=%', @file) >0

begin

    declare @fileid int
    set @fileid = 
        convert(
                int, 
                replace
                    (
                    @file
                    , 'fileid='
                    , ''
                    )
                )   

    select 
        @retval = 
            'Portals/' 
            + convert
                    (
                      varchar(10)
                    , files.portalid
                    ) 
            + '/' 
            + folders.folderpath 
            + files.filename 
    from 
        files
    left outer join folders
    on files.folderid = folders.folderid
    where 
        files.fileid=@fileid
    
end

else

begin
    set @retval = @file
end

return @retval

END
GO

Read more...

How to promote a regular DotNetNuke user to a superuser

I found this in Kevin Southworth's blog and I think it's worth mentioning since it may save your life in case you have forgotten your superuser's password. Using SQL, you can promote a regular DNN user to a superuser like this:

 

-- Promote regular user to SuperUser

DECLARE @username varchar(50)

SET @username = 'theUsernameToPromote'

UPDATE 
    Users 
SET 
    IsSuperUser = 1 
WHERE 
    username = @username

DELETE FROM 
    UserPortals 
WHERE 
    UserId = 
    (SELECT UserID 
    FROM Users 
    WHERE username = @username)

 

Simply replace 'theUsernameToPromote' with the user name you need.

 

Thanks Kevin!

Read more...

Finding a tab's parent by level

This is a simple but useful user defined function I have implemented in order to recursively find a tab's parent on a specific level. Syntax is as follows:

select dbo.udfGetParentByLevel (level, tabid)

where level is the level you need (0,1,2 etc.) and tabid is the id of the tab for which you need to find the parent.

Nothing much, but can save you in certain scenarios.

CREATE FUNCTION udfGetParentTabByLevel
(
-- Add the parameters for the function here
@parentlevel int, @initialtabid int
)
RETURNS int
AS
BEGIN

declare @level int
declare @tabid int
declare @parentid int

select @level = level, @tabid=tabid, @parentid=parentid from tabs where tabid=@initialtabid

while @level > @parentlevel
begin
select @level = level, @tabid=tabid, @parentid=parentid from tabs where tabid=@parentid
end
-- Return the result of the function
RETURN @tabid

END
GO

Read more...

Copying settings between DNNArticle modules

ZLDNN's DNNArticle is a really cool article module, loaded with a ton of features. Unfortunately, sometimes you'll have to add more than one modules on your site, and a ton of features typically comes with a ton of settings. In order not to have a hard time setting all the options from scratch, here is a script you can use to copy all those nifty settings you'll find in the "DNNArticle Settings" area from an already existing module.

You need to know three things: The source and destination module ids (NOT the tab ids, the actual module ids) and the tab id which will be used for the presentation of an article.

In the script given, I'm copying settings from module id 430 to module id 1232, with a view tab id of 986. Be sure to put your own numbers there.

Also, before running the script, make sure you have visited the settings area of the (newly added) DNNArticle module to be updated and have pressed "update" there (even if you haven't changed anything) so that the corresponding records are created in the ModuleSettings table.

The script is fairly straightforward, use at your own risk as always. Enjoy.

/* 
------------------------------------------------------ 
Start of values to be changed each run 
------------------------------------------------------ 
*/ 

-- This is the module id we are copying settings FROM 
declare @originalmodule int 
set @originalmodule=430 

-- This is the module id we are copying settings TO 
declare @moduletobeupdated int 
set @moduletobeupdated = 1232 

-- This is the view tab id for the module we are updating, in case it is 
-- different than the one of the source module. 
declare @viewtab int 
set @viewtab=986 

/* 
------------------------------------------------------ 
End of values to be changed each run 
------------------------------------------------------ 
*/ 

-- Some variables to hold table data 
declare @settingname nvarchar(50) 
declare @settingvalue nvarchar(2000) 

-- Get a cursor and start updating 
declare cur cursor fast_forward for 
select 
     settingname 
    ,settingvalue 
from 
    modulesettings 
where 
    moduleid=@originalmodule 

open cur 
fetch next from cur into @settingname, @settingvalue 

while @@fetch_status=0 
begin 
    if @settingname='ViewTab' 
    begin 
        update 
            modulesettings 
        set 
            settingvalue=@viewtab 
        where 
            settingname=@settingname and moduleid=@moduletobeupdated 

    end 

else 

begin 

        update 
            modulesettings 
        set 
            settingvalue=@settingvalue 
        where 
            settingname=@settingname and moduleid=@moduletobeupdated 

end 

fetch next from cur into @settingname, @settingvalue 

end 

close cur 
deallocate cur

Read more...

Retrieving tabs using SQL, security-aware

There are times when you like to go deeper with DNN and retrieve certain information by means of an SQL Query or a stored procedure. That's what happened to me when I needed to create a custom combo box which would redirect the user to specific pages.

In order to achieve that functionality, I had to use a module which would permit me to issue SQL queries to DotNetNuke and then format the results as I wanted. As I've stated many times before, I find the ListX module pretty useful in these situations.

One thing I discovered, though, is that you need to have a deeper understanding on what's going on with security in order to issue an SQL query that selects ONLY the tabs that a registered user has the right to see. You see, DNN tabs are not always visible to all user roles, so when using SQL you must be careful not to retrieve tab records that lead nowhere for the current user.

I'm not going to go into details on what I've done with the ListX module, unless someone asks specifically for it. Instead, on this post, I'm going to focus on how to issue a SQL query that retrieves records from the Tabs table that are visible for the current user.

For this post, it is assumed that you already know the ID of the current user in some way.

If you issue something like:

SELECT tabid, tabtitle FROM Tabs

Then you'll have all tabs, even those that are deleted. So we first need to have a WHERE clause to exclude deleted tabs, like this:

SELECT tabid,tabtitle FROM Tabs WHERE isDeleted=0

That leaves as with all active tabs, even those that are accessible by the administrator. In order to make our query security-aware, we have to see what tables the Tabs table is related to, in the context of security permissions.

The related tables are the following:

• TabPermission: A table which holds role permissions for every tab. Remember, DNN security works with roles. (We assume that we're not using the special "specific userid" feature that was introduced in later version of DNN).
• UserRoles: A table which holds the participations of DNN users to DNN security roles
• Users: A table which holds the actual DNN users.

The table that interests us more is the TabPermission table. Let's see the fields that interest us the most from this table:

TabId:

The tab id the record is referring to. There may be more than one records per tab id in the TabPermissions table, if the tab is available to multiple roles or even to one role with multiple permissions (DNN grants VIEW and EDIT permissions to tabs).

PermissionID:

The number you see here is the type of permission specified, and is related to the Permission.PermissionId field. If you do a SELECT * FROM Permissions you'll get, among other records, the following two records:

3    SYSTEM_TAB    -1    VIEW    View Tab
4    SYSTEM_TAB    -1    EDIT    Edit Tab

For our purposes, we will need permission id 3, i.e. VIEW permissions. Remember, we need the tabs that our user is allowed to see.

RoleID:

This is where things get a bit complicated. The RoleID field is related to the Roles.RoleId field (try a SELECT * FROM Roles to see what you'll get). But here, we don't need all the roles but just the roles our user is participating in. So we need to relate this field to the UserRoles table, which holds that information. If you do a SELECT * FROM UserRoles WHERE UserId=xxx (put your user id in the place of xxx), you'll get all role participations for the specified user.

(You'll notice that the UserRoles table also has the fields ExpiryDate, IsTrialUsed and EffectiveDate. For the sake of simplicity, we'll suppose that we don't have any roles with dates set, although it would be very easy to extend our final query to include only roles that are effective in the current time period.)

In the TabPermissions table, you'll notice two additional role ids that DO NOT exist on the Roles table: -1 and -2. -2 stands for "Host", and concerns pages accessible by the host user. -1 stands for "All Users" and concerns all non-administrative pages that are accessible from the "All Users" virtual role (this role actually does not exist anywhere).

Try doing the following:

--Fetch only admin pages accessed by the Administrators role 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=0

--Fetch only non-admin pages accessed by all users 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-1

--Fetch only host pages 
select * from tabpermission 
inner join tabs 
on tabs.tabid = tabpermission.tabid 
where roleid=-2

And observe the different results.


So what do we need in order to get the tabs that are accessible from a single, specific user? We'll take our initial query:

SELECT 
    tabid,tabtitle 
FROM 
    Tabs 
WHERE 
    isDeleted=0

And we'll amend it as follows:

declare @userid int 
select @userid = 2 

    select 
        tabs.tabid, tabs.title 
    from 
        tabs 

inner join ( 
    select 
        distinct tabpermission.tabid 
    from 
        tabpermission 
        left outer join 
        userroles on tabpermission.roleid = userroles.roleid 
        left outer join 
        users on users.userid = userroles.userid 
    where 
        tabpermission.permissionid=3 
        and (users.userid = @userid 
        or tabpermission.roleid=-1 or @userid=1) 
    ) as perm 
on perm.tabid=tabs.tabid 

where 
    tabs.isdeleted=0 
order by tabs.tabid ASC

What we did was to initially declare a @userid variable and give it a value (4 for our example, whatever suits you for your DNN installation - if you're making an SP out of this you can just provide the user's ID value as a parameter to the SP).

We're using the @userid variable inside our JOIN statement, which actually joins the Tabs table with the TabPermission table, but restricts the TabPermission table results to where the permission type is VIEW (id 3) and our user id is equal to @userid (users.userid = @userid) OR there are view rights for all users (tabpermission.roleid=-1)  OR our user is the administrator. (@userid=1), which overrides everything else. The DISTINCT clause is used to ommit propable multiple results for the same tab.

This will essentially get us the tab records that are accessible with VIEW permissions for the given user.

As I've mentioned before, some things are not covered in this query:

• Permissions to a specific user id



• Roles with start and expiry dates

But in my opinion it's a good starting point for anyone who needs to create a security-aware tab retrieval query in DNN.

Please let me know what you think.

Read more...

The dbo.tabs.IconFile field issue

The case is fairly simple: In a DNN installation, we needed the user to use the IconFile field (i.e. the "Icon" field in the Advanced Settings section of the Page Properties page to add a custom icon to each page they made. We would then use a query created in Bi4Ce's ListX module to present a list of pages in the form of an in-page navigation menu for a specific portion of the portal.

To our horror, we discovered (using DNN version 4.8.2), that the IconFile field in the Tabs table takes two types of values: Either a value like "FileID=xxx", where xxx is the id of a record in the Files table, or the full path of the image file (Portals/0 is implied, so the file path is starting with the next sub folder).

That is, if you've set the icon to be in portals/0/myimages/myimage.jpg, you either get an entry in the Tabs table like "FileId=2312", or an entry like "myimages/myimage.jpg". At this point we are not sure why this is happening, but the truth is we don't care. We just want the path to the actual file either way.

In the case of the fileid thing, you can get the path by joining the tabs table with the files table and then the files table with the folders table (the folder path itself is in the folders table) and voila. In the case where the full path exists in the IconFile field, you obviously don't need to do anything else.

We created a query which decides on what to do and produces a result set with two fields: tabid and img where img ALWAYS contains the full path to the image file (provided that one is set for the page), regardless of whether it's in the form "FileID=xxx" or in the full path form, and I'd like to share it with you.

You can easily make a view out of it and use it where appropriate. It hasn't been tested on multi-portal installations, maybe things will differ a bit there. Comments are always welcome. Here goes:

select 
 tabid
 , case 
  when ltrim(rtrim(iconfilepath))='' 
  then folderpath+filename
  else iconfilepath
  end 
 as img

from 
(
select 

   tabid
 , taborder
 , case
  when patindex ('FileID=%', iconfile) >0
  then convert(int, replace(iconfile, 'fileid=', '')) 
  else 0
  end 
 as iconfileid
 , case
  when patindex ('FileID=%', iconfile) =0
  then iconfile
  else ''
  end 
 as iconfilepath
 
from 
 tabs 
where 
 isdeleted=0 


) mytabs

left outer join files
on files.fileid = iconfileid
left outer join folders
on files.folderid = folders.folderid

order by 
 taborder asc

Read more...