*.dannyg

Combining Database IDs

Aug
25

There was a interesting requirement recently that came up.  It was to basically modify and existing BIT field and ALTER it to be an INT field.  Essentially it was to really push the limits of an INT field. 

Traditionally in a relational database, you have a one-to-one mapping of PK-to-FK (primary key to foreign key).  The goal here is to combine values that representative of the object in that database row.  In this case we are talking about permissions.

So first we need to create the table that will generate the IDs.  We need a seed which will be used as a basis of our ID.  We want to use binary values, it will be a power of two (2) for the primary key ID.

CREATE TABLE [Permissions]
(
     [Seed] [smallint] IDENTITY(-1, 1) NOT NULL,
     [Id] AS (power((2),[Seed])) PERSISTED NOT NULL,
     [PermissionName] [varchar](255) NOT NULL,
CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ([Id] ASC),
UNIQUE NONCLUSTERED ([Seed] ASC)
)

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Now that we have a table that can generate these IDs, we need to populate.  Below is a sample script that will insert some values into the table.

INSERT INTO [Permissions] (PermissionName) VALUES ('None')
INSERT INTO [Permissions] (PermissionName) VALUES ('Read')
INSERT INTO [Permissions] (PermissionName) VALUES ('Write')
INSERT INTO [Permissions] (PermissionName) VALUES ('Execute')

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Let’s run a SELECT * and see what happened.

image

As you can see we have some nice values to work with.  I’ll show you what I mean.  Traditionally you would do some kind of join and then determine the value.  In this case, we are going to leverage the power of the bitwise operators built into SQL Server.  I’m going to write a stored procedure that sets my permissions.  Let say that I have a user table and in that table I have a field INT called PermissionId.  I can set my PermissionID using the stored proc or in a simple UPDATE statement.

UPDATE Users SET PermissionId = 3 WHERE [UserId] = 1

What I have basically said is for userid = 1, give them Read and Write permissions.  Let’s validate that. using bitwise operators.  We will exclude 0, because None will be included every time.

DECLARE @Permissions varchar(MAX)
DECLARE @PermissionId int 
 
SET @PermissionId = 3
 
SELECT @Permissions = COALESCE(@Permissions + '/', '') + PermissionName
FROM Permissions
WHERE Id = Id & @PermissionId
AND Id <> 0
 
PRINT @Permissions

 

This returns ‘Read/Write’.  Let try the same for 5.  It returns ‘Read/Execute’.  How about for 7. Yup, just what I thought, ‘Read/Write/Execute’.

Technorati Tags: ,,,

Enabling ASP Errors on Vista/Windows 7 (IIS7-7.5)

Aug
13

Pretty tricky, the title says it all

cscript %systemdrive%inetpubadminiscriptsadsutil.vbs set w3svc/AspScriptErrorSentToBrowser true      (to allow error message send to client)

On a x64 OS (I run Windows 7 RC x64)

cscript C:Windowswinsxsamd64_microsoft-windows-iis-legacyscripts_31bf3856ad364e35_6.1.7100.0_none_4b5800ce84aa413cadsutil.vbs set w3svc/AspScriptErrorSentToBrowser true

Check-In/Out Sharepoint with Office Quickly

Aug
05

I feel like I should explain this really cool idea that I had.  Basically to quickly check-in and check-out documents in Sharepoint.  I’ll show you with MSword.  First thing to do is fire up Word.  You’ll notice that in the upper left hand corner near the Office Menu, a quick menu bar.  See below.

image

Drop down the arrow to right of the bar and you’ll see an option to add “More Commands”.  From this new window that pops up, go to the drop down “Choose command from” and select “All Commands”.

image

From here find Check-In and Check-Out and add them.  (I’m adding version history too!).  You can even add keyboard shortcuts from here too.  Once you’ve completed, click OK.  You’ll see your quick access bar updated.

image

Run ASP.NET Development Server without Visual Studio

Aug
05

Ever want to run ASP.NET development server without having to fire up Visual Studio Debugger.

You can create your own scripts or commands by being able to access this.

%WINDOWS PATH%Microsoft.NETFrameworkv2.0.50727WebDev.WebServer.exe

Here is the command line options as shown without setting any parameters.  Enjoy!

image