aspnet45 auth table defintions - postgresql.sql

CREATE TABLE webpages_Membership (
    UserId                                  INT            NOT NULL,
    CreateDate                              TIMESTAMP       NULL,
    ConfirmationToken                       VARCHAR (128) NULL,
    IsConfirmed                             BIT            DEFAULT (('0')) NULL,
    LastPasswordFailureDate                 TIMESTAMP       NULL,
    PasswordFailuresSinceLastSuccess        INT            DEFAULT ((0)) NOT NULL,
    Password                                VARCHAR (128) NOT NULL,
    PasswordChangedDate                     TIMESTAMP       NULL,
    PasswordSalt                            VARCHAR (128) NOT NULL,
    PasswordVerificationToken               VARCHAR (128) NULL,
    PasswordVerificationTokenExpirationDate TIMESTAMP       NULL,
    PRIMARY KEY (UserId)
);
 
 
CREATE TABLE webpages_OAuthMembership (
    Provider       VARCHAR (30)  NOT NULL,
    ProviderUserId VARCHAR (100) NOT NULL,
    UserId         INT            NOT NULL,
    PRIMARY KEY (Provider, ProviderUserId)
);
 
CREATE TABLE webpages_Roles (
    RoleId   INT           NOT NULL,
    RoleName VARCHAR (256) NOT NULL,
    PRIMARY KEY (RoleId),
    UNIQUE (RoleName)
);
 
CREATE TABLE webpages_UsersInRoles (
    UserId INT NOT NULL,
    RoleId INT NOT NULL,
    PRIMARY KEY (UserId, RoleId),
    FOREIGN KEY (UserId) REFERENCES "UserProfile" ("Id"),
    FOREIGN KEY (RoleId) REFERENCES webpages_Roles (RoleId)
);

aspnet45 auth table defintions.sql

CREATE TABLE [dbo].[UserProfile] (
    [UserId]   INT            IDENTITY (1, 1) NOT NULL,
    [UserName] NVARCHAR (MAX) NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
);

CREATE TABLE [dbo].[webpages_Membership] (
    [UserId]                                  INT            NOT NULL,
    [CreateDate]                              DATETIME       NULL,
    [ConfirmationToken]                       NVARCHAR (128) NULL,
    [IsConfirmed]                             BIT            DEFAULT ((0)) NULL,
    [LastPasswordFailureDate]                 DATETIME       NULL,
    [PasswordFailuresSinceLastSuccess]        INT            DEFAULT ((0)) NOT NULL,
    [Password]                                NVARCHAR (128) NOT NULL,
    [PasswordChangedDate]                     DATETIME       NULL,
    [PasswordSalt]                            NVARCHAR (128) NOT NULL,
    [PasswordVerificationToken]               NVARCHAR (128) NULL,
    [PasswordVerificationTokenExpirationDate] DATETIME       NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC)
);


CREATE TABLE [dbo].[webpages_OAuthMembership] (
    [Provider]       NVARCHAR (30)  NOT NULL,
    [ProviderUserId] NVARCHAR (100) NOT NULL,
    [UserId]         INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Provider] ASC, [ProviderUserId] ASC)
);

CREATE TABLE [dbo].[webpages_Roles] (
    [RoleId]   INT            IDENTITY (1, 1) NOT NULL,
    [RoleName] NVARCHAR (256) NOT NULL,
    PRIMARY KEY CLUSTERED ([RoleId] ASC),
    UNIQUE NONCLUSTERED ([RoleName] ASC)
);

CREATE TABLE [dbo].[webpages_UsersInRoles] (
    [UserId] INT NOT NULL,
    [RoleId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [fk_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[UserProfile] ([UserId]),
    CONSTRAINT [fk_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[webpages_Roles] ([RoleId])
);