SQL script for setting up a MySql db in preparation for ASP.Net MVC 4 SimpleMembership authentication

Since this was a little annoying to figure out:

/*
drop TABLE `DancingRobots`.`webpages_UsersInRoles`;
drop TABLE `DancingRobots`.`webpages_Roles`;
drop TABLE `DancingRobots`.`webpages_OAuthMembership`;
drop TABLE `DancingRobots`.`webpages_Membership`;
drop table `DancingRobots`.`UserProfile`;
*/

CREATE TABLE `DancingRobots`.`UserProfile`(
`UserId` int NOT NULL AUTO_INCREMENT,
`UserName` nvarchar(100) NULL,
PRIMARY KEY ( `UserId` ) );

CREATE TABLE `DancingRobots`.`webpages_Membership`(
`UserId` int NOT NULL,
`CreateDate` datetime NULL,
`ConfirmationToken` nvarchar(128) NULL,
`IsConfirmed` bit NULL default 0,
`LastPasswordFailureDate` datetime NULL,
`PasswordFailuresSinceLastSuccess` int NOT NULL default 0,
`Password` nvarchar(128) NOT NULL,
`PasswordChangedDate` datetime NULL,
`PasswordSalt` nvarchar(128) NOT NULL,
`PasswordVerificationToken` nvarchar(128) NULL,
`PasswordVerificationTokenExpirationDate` datetime NULL,
PRIMARY KEY ( `UserId` ) );

CREATE TABLE `DancingRobots`.`webpages_OAuthMembership`(
`Provider` nvarchar(30) NOT NULL,
`ProviderUserId` nvarchar(100) NOT NULL,
`UserId` int NOT NULL,
PRIMARY KEY ( `Provider`, `ProviderUserId`) );

CREATE TABLE `DancingRobots`.`webpages_Roles`(
`RoleId` int NOT NULL,
`RoleName` nvarchar(256) NOT NULL,
PRIMARY KEY ( `RoleId` ) );

CREATE TABLE `DancingRobots`.`webpages_UsersInRoles`(
`UserId` int NOT NULL,
`RoleId` int NOT NULL,
PRIMARY KEY ( `UserId`, `RoleId` ),
FOREIGN KEY (`RoleId`) REFERENCES `DancingRobots`.`webpages_Roles` (`RoleId`), 
FOREIGN KEY(`UserId`) REFERENCES `DancingRobots`.`UserProfile` (`UserId`));

Popular posts from this blog

Goose Game

the Gunks

Vacation Week