Forum Documentation

Old Archive Archive

Started by Michael Tanczos September 02, 2004 11:36 PM

-1 comments, last by Michael Tanczos 19 years, 10 months ago

5,681

Author

September 02, 2004 11:36 PM

This top-most post will contain documentation as it is developed: Forum Documentation Sections: Restarting the Server Permissions XSL Templating Restarting the Server The SQL server master database has a stored procedure inside it called usr_maintainglobaltables. What it does is create two global temporary tables on SQL Server startup called ##UsersLoggedIn and ##UserPermissions. Since global temporary tables are removed when the connection to the server is ended (or the last person to access the table finishes) this is the only way to create a permanent in-memory table. What you'll notice though is that in some cases when the server reboots those tables won't exist (you'll get an error message). To remedy this situation it is necessary to perform the following procedure:

Stop the Gamedev.net website in the control panel
Restart the SQL Server
Wait a few seconds to make sure it is fully started, the actual table creation comes at the very last part of SQL Server startup
Start the Gamedev.net website

Permissions Tables in Database · Usr_Domains · Usr_DomainUserList · Usr_Groups · Usr_GroupUserList · Usr_PermissionGroupList · Usr_PermissionUserList · Usr_PermissionApplication Purpose of Each Table Usr_Domains - This is used to segment the user system. Though strict adherence to domains wasn't followed during development so segmentation by domain may or may not work properly. Usr_DomainUserList - A list of users belonging to a given domain Usr_Groups - A list of groups (Administrators, Moderators, etc) Usr_GroupUserList - A list of users within each group Usr_PermissionGroupList - List of permissions held by this group Usr_PermissionUserList - List of permissions held by individual users Usr_PermissionApplication - This is for segmentation of permissions by application. An example application might be the "GD Showcase". Permissions are loaded on an as-needed basis, one application at a time. Each application has a special code that is used to identify it when loading permissions. The user system stuff is "usersys".. forums is "forums", ... "gds", "journals", .. Usr_PermissionCategory - For any given application it is possible to have dozens of potential permissions. As a result, the permission category is used purely for organizational purposes within the control panel to manage those permissions. So basically for every application there might be multiple categories for permissions. Usr_Permissions - This is basically a list of actions that can be performed by the end user. Usr_Users - Table of user logins, passwords, and whether or not they are disabled or deleted. Loading Permissions Permissions are cached in two ways. The first caching stage is performed through a stored procedure that loads permissions into an in-memory global table called ##UserPermissions.


	CREATE TABLE ##UserPermissions
	(
		u_id int, 
		papp_code nvarchar(50), 
		p_id int, 
		p_status int DEFAULT 0,
		p_static bit DEFAULT 1
	)

Each row in the table represents a single permission. If the p_status bit is set to 1 then the user has the permission, if it's set to 0 then they don't have the permission. p_static is zero if the permissions are loaded by combining all the permissions from each group and a users individual permissions. If permissions are added "by hand" they will have the p_static bit set to 1. This is primarily utilized when updating the permissions cache so the stored procedure can determine which permissions to evict from the cache (in this case, the non-static permissions). The cache is updated every 5 minutes. Upon logging in, the first caching procedure is performed (or not performed, if the load time is within 5 minutes of the last load time). The permissions themselves are then loaded into session variables by application. For this see the login.asp code stored in the root directory if you want specific names given to the session variables. The permissions system was originally intended to load in a set of permissions that was a combination of group inherited and individual permissions. It does this on a per-application basis when the application permissions are initially requested (through a call to the permission check function). When the desire to add permissions on a per forum basis came up, it became necessary to derive a solution that would be both fast and get the work done we needed. Because forum permissions can be loaded on a *very* consistent basis, even moreso than regular permissions given the number of forums, this weighed into the decision as to how they would be implemented. Also, because there are a large number of forums I did not want to have to also add to the complexity by giving individual users the ability to have per-forum permissions. While it definitely has it's pros, it has severe problems both in performance as the system scales and particularly in forum administration. Granted, forum administration problems could be resolved if a suitable interface was derived to control per-forum, per-user permissions. The performance problem still remains however. The following stored procedure will actually load permissions for a particular user based on a forum id, and user id:


CREATE PROCEDURE spForumPermissions
(
@forum_id int,
@userID int
)
AS

DECLARE @perm int, @invmask int

SET @perm = 0
SET @invmask = -1

SELECT @perm = (@perm | fp_pmask), @invmask = (@invmask & fp_inversemask) FROM forum_permissions WHERE g_id IN (SELECT g_id FROM 

Usr_GroupUserList WHERE u_id=@userID  AND forum_id=@forum_id)

SELECT @perm = @perm & @invmask

IF @@ERROR <> 0
	GOTO ErrorHandler

SELECT fp_pmask = @perm

ErrorHandler:
SELECT fp_pmask = 0
GO

For each forum there is a 32-bit integer bitfield that is used for forum permissions. This was decided to create an optimal way to concatenate forum permissions across multiple groups. What this stored procedure is actually doing is finding all of the groups a user belongs to, first and foremost. From there each group has two separate permission integers, a permission mask and a permission inverse mask. To obtain all the permissions a particular user has for a given forum, this stored procedure will perform a logical OR operation between all of the permission mask integers. What this will essentially do is set each bit in the final mask to 1 if any group they belong to has that corresponding bit set to 1. However, groups such as the "banned" group take advantage of an ability to explicity deny certain permissions. This means that if a user inherits a particular permission from any other group it will still deny the user that permission. This was useful for avoiding having to remove a user from all other groups to ban, or suspend them. A selective group could be applied that limits certain abilities without having to double up maintenance work (such as keeping the permission profile for regular users and special groups synchronized). The solution of this came in the form of an inverse permission mask. It isn't really an inverse of the regular mask, but is intended as a mask that will subtract away from the final permission mask. In this case, the inverse mask bitfield contains a ZERO for each permission that is to be explicitly denied. Again, the way the stored procedure derives the final inverse permission mask is to perform a logical AND operation between all the inverse permission mask integers. ie:


  10110
* 11011
-------
  10010

The resulting two mask integers are then combined in the stored procedure with the code:


SELECT @perm = @perm & @invmask

This logical AND operation will then leave only the permissions in the regular permission mask that aren't explicitly denied. Because of the concatenation step involving multiple groups already, I didn't want to also go through the process of including individual user permissions as well. To continue, the final permission mask is utilized to dynamically extend the staticly loaded permission set for a user. This extension is performed in the file \lib\forumpermissions.asp Shown below is a function that performs the task of setting a permission mask. This is a more descriptive function as far as determining what bitfield value represents what.



function SetPermissionMask (nPMask, intPermission)

		Select Case intPermission
			Case 	forum_view
					nPMask = nPMask OR 1
			Case 	forum_topic_view
					nPMask = nPMask OR 2
			Case 	forum_topic_start
					nPMask = nPMask OR 4
			Case 	forum_topic_reply
					nPMask = nPMask OR 8
			Case 	forum_topic_openclosed
					nPMask = nPMask OR 32
			Case 	forum_topic_close
					nPMask = nPMask OR 16
			Case 	forum_topic_delete
					nPMask = nPMask OR 64
			Case 	forum_topic_delete_permanent
					nPMask = nPMask OR 128
			Case 	forum_reply_delete
					nPMask = nPMask OR 256
			Case 	forum_topic_move
					nPMask = nPMask OR 512
			Case 	forum_topic_usehtml
					nPMask = nPMask OR 1024
			Case 	forum_topic_moderate
					nPMask = nPMask OR 2048
			Case 	forum_topic_edit
					nPMask = nPMask OR 4096
			Case 	forum_topic_editall
					nPMask = nPMask OR 8192
			'Case	next_permission_value
			'		nPMask = nPMask OR 16384
		End Select
		
		SetPermissionMask = nPMask
		
end function

So to recap, once a user visits a particular forum the stored procedure is executed to obtain their forum permission mask. Their forum permissions are then loaded by taking that permission mask and iterating through each bit to add the corresponding permission to their session-based permission cache. XSL Templating Background With most web development you're going to find that you'll be pulling some sort of data from a database and outputting HTML so they end user can view it with their favorite browser. It becomes problematic, particularly with compiled languages, to co-mingle regular code and the actual html output. I've worked on projects in the past where the process was: 1) Change HTML code 2) Compile 3) See how it looks 4) Go to 1 Luckily a very elegant solution exists in the form of XML. A number of databases today are now building in ways to pull data from a database directly in XML format. Even without that functionality, it is easy to reshape the results of a database query into an XML document.


<xml>
  <messages>
     <message>
        <id>1052</id>
        <topic>Separating html from code</topic>
     </message>
     <message>
        <id>1053</id>
        <topic>Cooking with Gas</topic>
     </message>
  </messages>
</xml>

Now XML is just another way of representing the same data you would pull from a database. So purely from a performance standpoint, we don't see any real advantages to making this conversion. What we ultimately want is a way to transform the XML into a nice, pretty format the end user can appreciate. The answer to our prayers comes in the form of a technology called XSLT [www.w3.org]. This particular technology allows us to create a template specifying precisely how we want to display the data within the XML node set. Example XSL document:


<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
>

   <xsl:template match="/">
        Topic
        <table cellpadding="3" cellspacing="0">
            <tr>
                <th>Message</th>
            </tr>
            <xsl:apply-templates select="//message"/>
        </table>
    </xsl:template>
    <xsl:template match="message">
        <tr>
            <td><xsl:value-of select="id" /></td>
        </tr>
	<tr>
	    <td><xsl:value-of select="topic" /></td>
	</tr>
    </xsl:template>
</xsl:stylesheet>

The general approach you would exercise to utilize XSL would be: 1. Perform database query 2. Convert to XML 3. Load XSL stylesheet 4. Apply XSL transformation to XML 5. Display resulting output Utilizing XSL documents gives you something of a "template" language that can work with your existing compiled or scripted code. Implementation Stored procedures are executed on the SQL Server to retrieve the necessary records for displaying topic lists or topics and replies. To use XSL I first need an XML node set. This set could be obtained in two ways: one would be to load a standard XML file that you've seen. What we want is the data structures (node set) that microsoft creates when loading that file. The other way, which is the way I do it, is to parse through each record of the query and build that set of XML nodes by hand. XML node sets are used in conjunction with XSL in two locations: forum.asp topic.asp The actual functional logic that generates the node sets is stored in: /forums/lib/topiclib.asp Each XML node set is generated from the records returned by either one of two stored procedures, depending on where XSL sheets are to be utilized. The two stored procedures are:


spForumSelection
	@forum_id int,   /* ID of forum */
	@defdate varchar(30),   /* Date you want to retrieve recent threads from */
	@orderby int,  /* Order:  1 for standard ordering, 0 for ordering by subject */
	@Page int,  /* Results may contains hundreds of records, each page contains
	@Size int,   Size records  */
	@ShowDeleted int  /* Whether or not to include deleted topics in the results */

spTopicSelection
	@ftopic_id int,  /* ID of forum */
	@RatingThreshold int,  /* Minimum user rating to include posts from */
	@Page int,  
	@Size int,
	@ShowDeleted int,
	@ShowTopic int,  /* 1 if you want to include the original topic in the result set */
	@SortOrder int,  
	@TseqEnd int,
	@ViewMode int,
	@JoinTable varchar(30)

After I have the node set it then only requires a function call to apply an XSL sheet to the node set. XSL is basically just instructions on how to format the data. Because the stylesheet has to be loaded each time you want to use it, I actually cache it in an application variable. So when you go changing it you don't see the changes. Basically all you need to do is add "&clearcache=true" to any page that uses an XSL sheet and it will reload the appropriate sheet for you.

🎉 Celebrating 25 Years of GameDev.net! 🎉

Forum Documentation

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

🎉 Celebrating 25 Years of GameDev.net! 🎉

Forum Documentation

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines