Overview
Knowing DNN’s database schema and having the ability to create effective SQL queries are essential tools in the success of an agent supporting DNN. A majority of DNN issues require the agent to dive into the database, gather all the information, and even resolve the issue by modifying a corrupt piece of data.
Prerequisites
- Basic knowledge of SQL
- Access to the SQL Server
- Access to a Superuser account
Introduction
What Is a Database Schema?
In short, a database schema is a way to logically group objects such as tables, views, stored procedures, etc. Think of a schema as a container of objects.
Finding the DNN Database Schema Documentation
Normally, as for the database schema documentation, DNN does not produce database schema documents as this would encourage users to perform direct database modifications that are not supported.
- All changes should go via the relevant APIs, which we ensure retain binary compatibility and modifications to any dependant objects/data.
However, a general idea of which objects interact with each other can be found in the DNN 6.1.2 document.
You can also view this information in the SQL Server Management Studio by manually expanding the table.
Description
Finding the Correct DNN Table
The naming convention for DNN is pretty straightforward. If you are looking for any database table that deals with a specific page, you would look for any table with the name Tab while if you were looking for a table that dealt with the Portal, you would look for tables named Portal.
An easy way to filter this list is via SQL Server Management Studio:
- Right-click on the tables folder > Filter > Filter by the keyword (such as Tab in the Name property).
Reviewing how each table interacts with the other is crucial in understanding how the DNN components fully work. It is beneficial to practice and memorize what kind of data each table stores.
Common SQL Queries
Once you have found the correct DNN table, your next step would be to gather the correct data.
Normally, if you have access to SQL Server Management Studio, you can right-click on the specific table and select the top 1000 rows. If you only have access to the DNN UI, you will need to learn some basic SQL Queries.
SELECT * FROM Tabs WHERE TabID = #- This is to find information regarding a particular page.
SELECT * FROM PortalAlias WHERE PortalID = #- This query will give you the most information regarding a portal.
SELECT * FROM Users WHERE UserID = #- This query will give you the most information regarding a user.
You will (at some point) need to create a SQL script that will bulk update a piece of data as the above queries would be tedious to perform via the UI.
- For example, a suggestion on how to update the messaging notification frequencies for a user is provided. You would like to perform this for all users in the particular Portal. Since an update script would not be enough to resolve this (as the filter for the update script would have to change for every single user one at a time in the Portal), you would need to create a cursor.
The script below would be how this is accomplished:
DECLARE @PortalID AS INT = 0 /*Set PortalID*/
DECLARE @UserID AS INT
DECLARE @MessagesEmailFrequency AS INT = 0 /*Set Frequency*/
DECLARE @NotificationsEmailFrequency AS INT = 0 /*Set Frequency*/
DECLARE @EmailCursor AS CURSOR;
SET @EmailCursor = CURSOR FOR
SELECT [UserID]
FROM UserPortals
WHERE PortalID = 0; /*Set PortalID*/
OPEN @EmailCursor;
FETCH NEXT FROM @EmailCursor INTO @UserID;
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT * FROM [CoreMessaging_UserPreferences] WHERE UserId = @UserID and PortalId = @PortalID)
BEGIN
INSERT INTO [dbo].[CoreMessaging_UserPreferences]
([PortalId]
,[UserId]
,[MessagesEmailFrequency]
,[NotificationsEmailFrequency])
VALUES
(@PortalID
,@UserID
,@MessagesEmailFrequency
,@NotificationsEmailFrequency)
END
ELSE
BEGIN
UPDATE [CoreMessaging_UserPreferences]
SET MessagesEmailFrequency = @MessagesEmailFrequency
, [NotificationsEmailFrequency] = @NotificationsEmailFrequency
WHERE [UserId] = @UserID AND [PortalId] = @PortalID
END
FETCH NEXT FROM @EmailCursor INTO @UserID;
END
CLOSE @EmailCursor;
DEALLOCATE @EmailCursor;
This script might be a lot to take in, but it is simpler than you think. Essentially, a cursor would be the equivalent of what a loop would do in programming.
- You would declare the necessary variables needed to set the cursor to the result set that will be iterated over.
- Next, the fetch statement would iterate each row based on the UserID from the result set and execute the values inside the while statement.
- After the insert/update has been completed, the fetch would grab the next UserID.
A cursor should be used for anything that needs to be updated in bulk but cannot be accomplished with an update query because the data for the filter is gathered in a separate table.
Here are some articles to get better acquainted with cursors:
Backup Strategies
- Please review the On-Premises DNN Database Backup KB to learn how to create a backup for a database of a DNN environment.
Comments
0 comments
Please sign in to leave a comment.