SQL Server 2008: Ctrl-Shift-M

by Jon Davis 13. November 2008 14:59

When creating a new stored procedure in SQL Server Management Studio, the sproc template would generate:

 

	-- Use the Specify Values for Template Parameters
	-- command (Ctrl-Shift-M) to fill in the paramter
	-- values below.
	

 

Unfortunately, while the same template is used in SQL Server Management Studio for SQL Server 2008, Ctrl-Shift-M wasn't implemented.

The one-time fix is rather trivial:

  • Tools -> Options -> Environment -> Keyboard
  • Under Keyboard scheme, choose SQL Server 2000.
  • Click OK
  • Go back, .. Tools -> Options -> Environment -> Keyboard
  • Under Keyboard scheme, choose Standard.

This info was found here: http://www.eggheadcafe.com/software/aspnet/32965600/ssms-2008-ctrlshiftm.aspx

T-SQL: Universal User Map

by Jon Davis 23. July 2008 21:20

Here's an approach I recently took with taking a reusable data component that has user identification bindings associated with the data, and making it pluggable to different applications.

There can be lots of different web application databases that involve users, and as such there are often many different ways of identifying them. The primary keys might be:

  • int - The int is fine, it's easy (i.e. terse) to administer, it scales well (so long as you don't have more than a couple billion people hammering on your site). One of the biggest problems with int is that collisions between two applications using int are guaranteed.
  • uniqueidentifier / GUID - This is almost always a value that is guaranteed to be unique between two distinct applications. Unfortunately, almost always and always aren't the same thing; once in a blue moon, collisions do occur. Furthermore, uniqueidentifiers don't scale well -- they're bulky, wordy to administer, and, being somewhat randomly generated, they don't sort in the index in a meaningful way.
  • Email / varchar(xx) - Email is an okay key, it kind of imposes a business rule of social distinction on top of record distinction, which may or may not be appropriate. I think Email is an important column to index--and IMO it's almost always appropriate to use with user credentials as an alternate username on the same login form--but I don't think it's an administrator's ideal as a PK.
  • Username / varchar(xx) - The username is fine for logging in with, but I rarely see it used for primary keys. One problem is that unless you re-index in upper/lower case, you're imposing distinction on case sensitive names, which is not normal practice.

Others might be used but these are used quite often, in that order.

So the problem I ran into was that I was building this "sub-application" that multiple web applications could use, but each web application that would use it handled user IDs differently. First I built the data round uniqueidentifier, since that is most likely to be unique across apps, but then I started asking around about what the applications are actually using. While one does use GUIDs, another uses int, and another uses a mixed choice of GUID, int, and e-mail.

This had me thinking, and so I devised a UserMap table that would accept any combination of any identifying data.

To start with, I already had web application context tables created.

CREATE TABLE [dbo].[Site]
(
    SiteId int NOT NULL PRIMARY KEY, 
    [CreateDate] datetime NOT NULL CONSTRAINT [DF_Site_CreateDate]  DEFAULT (getdate()),
    [ModifyDate] datetime NOT NULL CONSTRAINT [DF_Site_ModifyDate]  DEFAULT (getdate()),
    BaseUrl nvarchar(500) NOT NULL
); 

CREATE TABLE [dbo].[AppContext]
(
    [SiteId] [int] NOT NULL,
    [AppContextId] [int] NOT NULL,
    [CreateDate] datetime NOT NULL CONSTRAINT [DF_AppContext_CreateDate]  DEFAULT (getdate()),
    [ModifyDate] datetime NOT NULL CONSTRAINT [DF_AppContext_ModifyDate]  DEFAULT (getdate()),
    [BaseUrl] [nvarchar](500) NOT NULL,
    
    CONSTRAINT [PK__AppContext] PRIMARY KEY CLUSTERED 
    (
        [SiteId] ASC,
        [AppContextId] ASC
    )
) 

All users are associated with an application context, which is hosted on a site. I don't know anything about the site, the application, or the user, except that a) the site has a URL, 2) the web app has a relative URL, and 3) the user is going to be identified in some way. I don't know what way that will be, I don't care, but I'll guess it'll be one of the fields I listed at the top of this article.

CREATE TABLE [dbo].[UserMap]
(
    [UserId] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_UserMap_CreateDate]  DEFAULT (getdate()),
    [ModifyDate] [datetime] NOT NULL CONSTRAINT [DF_UserMap_ModifyDate]  DEFAULT (getdate()),
    [Guid] uniqueidentifier NOT NULL DEFAULT (newid()),
    [SiteId] int NULL,
    [AppContextId] int NULL,
    [Email] nvarchar(50) NULL,
    [Username] nvarchar(50) NULL,
    [AppUserId] int NULL 
); 

Note that I could also put indexes on each of the identifying columns as well. These indexes will slow INSERTs and UPDATEs down, but SELECTs will be very fast. Given that this particular application add-on I'm building is intended to be used for data collection more than anything else, I will not add these indexes.

Because this table stores multiple different kinds of identifying information, each web application can now perform a JOIN operation against this table in whatever way it deems appropriate, as long as it includes the SiteId and AppContextId in the JOIN clause.

So now when working with my data tables, I would only store the sub-app's UserId. Now I needed my INSERTing stored procedures to look that ID up, and in its absence, to INSERT the mapping record. I also needed to enforce the rule that some kind of identification is mandatory for insertion. I didn't implement a constraint, but I did implement a few stored procedures that imposed this rule.

Let's look at the first problem. I want to INSERT some data in some other table, using only a UserId. I don't know what the UserId is, but I do have an e-mail address, or a username, or a GUID, or something.

Well, how about if I just do a lookup?

CREATE PROCEDURE [dbo].[UserMap_GetUserId]
    @SiteId int,
    @AppContextId int,
    @UserGuid uniqueidentifier = NULL,
    @AppUserId int = NULL,
    @UserEmail varchar(50) = NULL,
    @AppUsername varchar(50) = NULL,
    @UserId int output
AS
    DECLARE @sql nvarchar(max)
    SET @sql = N'SELECT @UserId = (SELECT TOP 1 [UserId] FROM [dbo].[UserMap] '
        + N'WHERE SiteId=' + CONVERT(varchar(10), @SiteId)
        + N'  AND AppContextId=' + CONVERT(varchar(10), @AppContextId) + N' AND'
    IF @AppUserId    IS NOT NULL SET @sql = @sql + N' [AppUserId]    = ' +  CONVERT(nvarchar(10), @AppUserId) + N' AND'
    IF @UserGuid    IS NOT NULL SET @sql = @sql + N' [Guid]        = N''' + CONVERT(nvarchar(50), @UserGuid) + N''' AND'
    IF @UserEmail    IS NOT NULL SET @sql = @sql + N' [Email]        = N''' + CONVERT(nvarchar(50), @UserEmail) + N''' AND'
    IF @AppUsername    IS NOT NULL SET @sql = @sql + N' [Username]    = N''' + CONVERT(nvarchar(50), @AppUsername) + N''' AND'
    SET @sql = SUBSTRING(@sql, 1, LEN(@sql)-4) + N')'
    print @sql
    EXEC sp_executesql @sql, N'@UserId int output', @UserId output
    
    return @UserId
; 

 

Notice that it doesn't create the UserId if no records are returned. This is an exported sproc that actually gets used on the INSERT sprocs for the other tables.

CREATE PROCEDURE [dbo].[MyAppData_Add_GetNewID]
    @MyAppContextId int,
    @MyAppFieldId int,
    @UserId int = NULL,
    @AppUserId int = NULL,
    @UserGuid uniqueidentifier = NULL,
    @UserEmail varchar(50) = NULL,
    @AppUsername varchar(50) = NULL,
    @DataText varchar(500),
    @MyAppFieldOptionId int,
    
    @MyAppDataId int output
AS
    
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 
    BEGIN TRANSACTION
    
    DECLARE @CreateDate datetime, @ModifyDate datetime
    SET @CreateDate = getdate()
    SET @ModifyDate = @CreateDate
    
    DECLARE @MyAppId int
    DECLARE @SiteId int
    DECLARE @AppContextId int

    SELECT @SiteId = [SiteId], @AppContextId = [AppContextId], @MyAppId = [MyAppId]
    FROM [MyAppContext]
    WHERE [MyAppContextId] = @MyAppContextId
    
    IF @UserId IS NULL  
    BEGIN
        IF @AppUserId IS NOT NULL
        OR @UserGuid IS NOT NULL
        OR @UserEmail IS NOT NULL
        OR @AppUsername IS NOT NULL
        BEGIN
            EXEC [dbo].[UserMap_GetUserId] @UserGuid, @AppUserId, @UserEmail, @AppUsername, @UserId output
        END
        ELSE
        BEGIN
            RAISERROR ('Must populate a user identification field.', 16, 1)
            RETURN
        END
    END
    
    IF @UserId IS NULL --still, perhaps due to provided data but no match
    BEGIN
        print '@UserId is still null.'
        EXEC [dbo].[UserMap_Add_GetNewID] @SiteId, @AppContextId, @AppUserId, @UserGuid, @UserEmail, @AppUsername, @UserId output
    END

    INSERT INTO [dbo].[MyAppData]
    (
        [MyAppContextId],
        [SiteId],
        [AppContextId],
        [MyAppFieldId],
        [CreateDate],
        [ModifyDate],
        [MyAppId],
        [UserId],
        [DataText],
        [MyAppFieldOptionId]
    ) VALUES (
        @MyAppContextId,
        @SiteId,
        @AppContextId,
        @MyAppFieldId,
        @CreateDate,
        @ModifyDate,
        @MyAppId,
        @UserId,
        @DataText,
        @MyAppFieldOptionId
    )
    
    SET @MyAppDataId = SCOPE_IDENTITY()
    
    COMMIT TRANSACTION
    
    return @MyAppDataId; 

Now notice that if it's still null, it's invoking [dbo].[UserMap_Add_GetNewID], which is basically the INSERT sproc for the user map table. Here that is below, and note that it does a simple INSERT and then a dynamic UPDATE. I only did this for simplicity's sake, to minimize the code, I don't think it will make a huge difference performance-wise.

CREATE PROCEDURE [dbo].[UserMap_Add_GetNewID]
    @SiteId int,
    @AppContextId int,
    @AppUserId int,
    @UserGuid uniqueidentifier = NULL,
    @Email varchar(50) = NULL,
    @Username varchar(50) = NULL,
    @UserId int output
AS
    INSERT INTO [dbo].[UserMap]
    (
        SiteId,
        AppContextId
    ) VALUES (
        @SiteId,
        @AppContextId
    )
    
    SET @UserId = SCOPE_IDENTITY()
    
    EXEC [dbo].[UserMap_Update] @UserId, @SiteId, @AppContextId, @AppUserId, @UserGuid, @Email, @Username
    
    return @UserId;
    
    
CREATE PROCEDURE [dbo].[UserMap_Update]
    @UserId int,
    @SiteId int,
    @AppContextId int,
    @AppUserId int,
    @UserGuid uniqueidentifier = NULL,
    @Email varchar(50) = NULL,
    @Username varchar(50) = NULL
AS
    DECLARE @sql nvarchar(max)
    SET @sql = N'UPDATE [dbo].[UserMap] SET [SiteId] = ' +  CONVERT(varchar(10), @SiteId) + ', '
        + N'[AppContextId] = ' + CONVERT(varchar(10), @AppContextId) + N','
        + N'[ModifyDate] = getdate(),'
    IF @AppUserId    IS NOT NULL SET @sql = @sql + N' [AppUserId]    = ' +  CONVERT(varchar(10), @AppUserId) + N','
    IF @UserGuid    IS NOT NULL SET @sql = @sql + N' [Guid]        = N''' + CONVERT(varchar(50), @UserGuid) + N''','
    IF @Email        IS NOT NULL SET @sql = @sql + N' [Email]        = N''' + CONVERT(varchar(50), @Email) + N''','
    IF @Username    IS NOT NULL SET @sql = @sql + N' [Username]    = N''' + CONVERT(varchar(50), @Username) + N''','
    SET @sql = SUBSTRING(@sql, 1, LEN(@sql)-1) + N' WHERE [UserId]    = ' + CONVERT(varchar(50), @UserId)
    
    EXEC(@sql)
; 

That's it. I came up with it and implemented and tested in one late afternoon, I feel good about it, but I'm open to feedback. Does it smell bad? Am I doing something bad here? T-SQL isn't my greatest strength so fill me in.

kick it on DotNetKicks.com

Be the first to rate this post

  • Currently 0/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: , , ,


 

Powered by BlogEngine.NET 1.4.5.0
Theme by Mads Kristensen

About the author

Jon Davis (aka "stimpy77") has been a programmer, developer, and consultant for web and Windows software solutions professionally since 1997, with experience ranging from OS and hardware support to DHTML programming to IIS/ASP web apps to Java network programming to Visual Basic applications to C# desktop apps.
 
Software in all forms is also his sole hobby, whether playing PC games or tinkering with programming them. "I was playing Defender on the Commodore 64," he reminisces, "when I decided at the age of 12 or so that I want to be a computer programmer when I grow up."

Jon was previously employed as a senior .NET developer at a very well-known Internet services company whom you're more likely than not to have directly done business with. However, this blog and all of jondavis.net have no affiliation with, and are not representative of, his former employer in any way.

Contact Me 


Tag cloud

Calendar

<<  October 2018  >>
MoTuWeThFrSaSu
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234

View posts in large calendar