SQLize Online / PHPize Online  /  SQLtest Online

/****** Object: Table [dbo].[Roles] Script Date: 4/08/2021 7:23:56 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Roles]( [RoleID] [INT] IDENTITY(0,1) NOT NULL, [PortalID] [INT] NOT NULL, [RoleName] [NVARCHAR](50) NOT NULL, [Description] [NVARCHAR](1000) NULL, [ServiceFee] [MONEY] NULL, [BillingFrequency] [CHAR](1) NULL, [TrialPeriod] [INT] NULL, [TrialFrequency] [CHAR](1) NULL, [BillingPeriod] [INT] NULL, [TrialFee] [MONEY] NULL, [IsPublic] [BIT] NOT NULL, [AutoAssignment] [BIT] NOT NULL, [RoleGroupID] [INT] NULL, [RSVPCode] [NVARCHAR](50) NULL, [IconFile] [NVARCHAR](100) NULL, [CreatedByUserID] [INT] NULL, [CreatedOnDate] [DATETIME] NULL, [LastModifiedByUserID] [INT] NULL, [LastModifiedOnDate] [DATETIME] NULL, CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED ( [RoleID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_RoleName] UNIQUE NONCLUSTERED ( [PortalID] ASC, [RoleName] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[UserRoles] Script Date: 4/08/2021 7:24:28 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[UserRoles]( [UserRoleID] [INT] IDENTITY(1,1) NOT NULL, [UserID] [INT] NOT NULL, [RoleID] [INT] NOT NULL, [ExpiryDate] [DATETIME] NULL, [IsTrialUsed] [BIT] NULL, [EffectiveDate] [DATETIME] NULL, [CreatedByUserID] [INT] NULL, [CreatedOnDate] [DATETIME] NULL, [LastModifiedByUserID] [INT] NULL, [LastModifiedOnDate] [DATETIME] NULL, CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED ( [UserRoleID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[Users] Script Date: 4/08/2021 7:24:33 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Users]( [UserID] [INT] IDENTITY(1,1) NOT NULL, [Username] [NVARCHAR](100) NOT NULL, [FirstName] [NVARCHAR](50) NOT NULL, [LastName] [NVARCHAR](50) NOT NULL, [IsSuperUser] [BIT] NOT NULL, [AffiliateId] [INT] NULL, [Email] [NVARCHAR](256) NULL, [DisplayName] [NVARCHAR](128) NOT NULL, [UpdatePassword] [BIT] NOT NULL, [LastIPAddress] [NVARCHAR](50) NULL, [IsDeleted] [BIT] NOT NULL, [CreatedByUserID] [INT] NULL, [CreatedOnDate] [DATETIME] NULL, [LastModifiedByUserID] [INT] NULL, [LastModifiedOnDate] [DATETIME] NULL, CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ( [UserID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Users] UNIQUE NONCLUSTERED ( [Username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --Add constraints ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsSuperUser] DEFAULT ((0)) FOR [IsSuperUser] GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_DisplayName] DEFAULT ('') FOR [DisplayName] GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_UpdatePassword] DEFAULT ((0)) FOR [UpdatePassword] GO ALTER TABLE [dbo].[Users] ADD CONSTRAINT [DF_Users_IsDeleted] DEFAULT ((0)) FOR [IsDeleted] GO ALTER TABLE [dbo].[UserRoles] WITH CHECK ADD CONSTRAINT [FK_UserRoles_Roles] FOREIGN KEY([RoleID]) REFERENCES [dbo].[Roles] ([RoleID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Roles] GO ALTER TABLE [dbo].[UserRoles] WITH CHECK ADD CONSTRAINT [FK_UserRoles_Users] FOREIGN KEY([UserID]) REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[UserRoles] CHECK CONSTRAINT [FK_UserRoles_Users] GO ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_ServiceFee] DEFAULT ((0)) FOR [ServiceFee] GO ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_IsPublic] DEFAULT ((0)) FOR [IsPublic] GO ALTER TABLE [dbo].[Roles] ADD CONSTRAINT [DF_Roles_AutoAssignment] DEFAULT ((0)) FOR [AutoAssignment] GO SET IDENTITY_INSERT dbo.Users ON INSERT dbo.Users (UserID,Username,FirstName,LastName,DisplayName) VALUES ( 1, N'host', N'SuperUser', N'Account', N'SuperUser Account' ), ( 2, N'RegisteredUser', N'Registered', N'User', N'RegisteredUser' ), ( 6, N'507164', N'Deca', N'Rossall', N'Deca Rossall' ), ( 7, N'526720', N'Jack', N'Kent', N'Jack Kent' ), ( 8, N'507644', N'Julie', N'Durant', N'Julie Durant' ), ( 9, N'507131', N'Steven', N'Howland', N'Steven Howland' ), ( 10, N'508333', N'Noel', N'Phillips', N'Noel Phillips' ), ( 11, N'582701', N'Tim', N'Rosenberg', N'Tim Rosenberg' ), ( 12, N'559794', N'Paul', N'Beenston', N'Paul Beenston' ), ( 13, N'574230', N'Paul', N'Hunter', N'Paul Hunter' ), ( 14, N'543698', N'Paul', N'Pap', N'Paul Pap' ), ( 15, N'587200', N'Jack', N'Freeman', N'Jack Freeman' ), ( 16, N'522862', N'Maguid', N'Ibrahim', N'Red Sea Restaurant' ), ( 17, N'525252', N'Madeleine', N'Baker', N'Madeleine Baker' ), ( 18, N'558047', N'Sada', N'Osman', N'Sada Osman' ), ( 19, N'552742', N'Peter', N'Behrendt', N'Peter Behrendt' ), ( 20, N'555915', N'Badiah', N'Barakat', N'Badia Barakat' ), ( 21, N'583379', N'Ladene J', N'Williams', N'Ladene J Williams' ), ( 22, N'589976', N'Cadantog', N'Amador', N'Cadantog Amador' ), ( 23, N'561580', N'Fadi', N'Akra', N'Fadi Akra' ), ( 24, N'576803', N'Karen', N'Thompson', N'Karen Thompson' ), ( 25, N'592572', N'Hadi', N'Harton', N'Hadi Harton' ), ( 26, N'597916', N'Karen', N'Tough', N'Karen Tough' ), ( 27, N'566852', N'Karan Theresa', N'Anketell', N'Karen Theresa Anketell' ), ( 28, N'559185', N'Jade', N'Ni', N'Jade Ni' ), ( 29, N'509067', N'Jim', N'Thompson', N'Jim Thompson' ), ( 30, N'552476', N'Jim & Betty', N'Kyriakaulis', N'Jim Betty Kyriakaulis' ), ( 31, N'507053', N'Jun', N'Shu', N'Mrs Jun Zi Shu' ), ( 32, N'Lumo Marketing', N'Lumo', N'Marketing', N'Lumo Marketing' ), ( 33, N'593364', N'John', N'Cotterall', N'John Cotterall' ), ( 34, N'535096', N'C', N'Corcoran', N'Cathy Corcoran' ), ( 35, N'520093', N'Cathy', N'Lawler', N'Cathy Lawler' ), ( 36, N'508157', N'Marie', N'Mildren', N'Marie Mildren' ), ( 37, N'511498', N'Dianna', N'Brennen', N'M & T Brennen' ), ( 38, N'508620', N'Beryl', N'Morgan', N'Beryl Morgan' ), ( 39, N'588940', N'John', N'Drake', N'John Drake' ), ( 40, N'507060', N'Jeff', N'Aarons', N'Jeff Aarons' ), ( 41, N'508598', N'Edna', N'Croft', N'Edna Croft' ), ( 42, N'507075', N'David', N'Abbott', N'David Abbott' ), ( 43, N'507057', N'Marie', N'Di Pietro', N'Marie Di Pietro T/A Jump On Board Travel' ), ( 44, N'561044', N'Mr Mike', N'Bos', N'Poscorp Pty Ltd' ), ( 45, N'507094', N'James', N'Need', N'Mariner Engineering Pty Ltd' ), ( 47, N'Dummy1', N'John', N'Doe', N'John Doe') SET IDENTITY_INSERT dbo.Users OFF; SET IDENTITY_INSERT dbo.Roles ON INSERT dbo.Roles (RoleID,PortalID,RoleName,Description,ServiceFee,BillingFrequency,TrialPeriod,TrialFrequency,BillingPeriod,TrialFee,IsPublic,AutoAssignment,RoleGroupID,RSVPCode,IconFile,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate) VALUES ( 0, 0, N'Administrators', N'Portal Administration', 0.0000, 'N', -1, 'N', -1, 0.0000, 0, 0, NULL, N'', N'', -1, N'2011-11-14T15:40:07.207', -1, N'2011-11-14T15:40:07.207' ),( 1, 0, N'Registered Users', N'Registered Users', 0.0000, 'N', -1, 'N', -1, 0.0000, 0, 1, NULL, N'', N'', -1, N'2011-11-14T15:40:07.22', -1, N'2011-11-14T15:40:07.22' ),( 2, 0, N'Subscribers', N'A public role for portal subscriptions', 0.0000, 'N', -1, 'N', -1, 0.0000, 1, 1, NULL, N'', N'', -1, N'2011-11-14T15:40:07.373', -1, N'2011-11-14T15:40:07.373' ),( 7, 0, N'Lumo Advantage', N'Lumo Advantage Customer', 0.0000, 'N', -1, 'N', -1, 0.0000, 0, 0, NULL, N'', N'', -1, N'2012-03-07T16:10:32.38', -1, N'2012-03-07T16:10:32.38' ),( 8, 0, N'Contributers', N'Users who contribute content', 1.5000, 'N', -1, 'N', -1, 0.0000, 1, 0, NULL, N'', N'', -1, N'2016-07-26T08:45:48.09', -1, N'2016-07-26T08:45:48.09' ),( 9, 0, N'Editors', N'Users who edit content', 0.0000, 'N', -1, 'N', -1, 0.0000, 0, 0, NULL, N'', N'', -1, N'2019-04-08T17:37:19.36', -1, N'2019-04-08T17:37:19.36' ); SET IDENTITY_INSERT dbo.Roles OFF; SET IDENTITY_INSERT dbo.UserRoles ON INSERT dbo.UserRoles (UserRoleID,UserID,RoleID,ExpiryDate,IsTrialUsed,EffectiveDate,CreatedByUserID,CreatedOnDate,LastModifiedByUserID,LastModifiedOnDate) VALUES ( 3, 1, 0, NULL, 1, NULL, -1, N'2011-11-14T15:40:07.387', -1, N'2011-11-14T15:40:07.387' ), ( 66, 32, 0, NULL, 1, NULL, 1, N'2012-02-13T16:37:34.57', 1, N'2012-02-13T16:37:34.57' ), ( 34, 17, 1, NULL, 1, NULL, -1, N'2012-02-08T15:45:54.74', -1, N'2012-02-08T15:45:54.74' ), ( 38, 19, 1, NULL, 1, NULL, -1, N'2012-02-09T11:40:03.983', -1, N'2012-02-09T11:40:03.983' ), ( 40, 20, 1, NULL, 1, NULL, -1, N'2012-02-09T12:13:34.77', -1, N'2012-02-09T12:13:34.77' ), ( 79, 37, 1, NULL, 1, NULL, -1, N'2012-03-21T11:14:08.307', -1, N'2012-03-21T11:14:08.307' ), ( 82, 38, 1, NULL, 1, NULL, -1, N'2012-03-21T11:53:42.207', -1, N'2012-03-21T11:53:42.207' ), ( 84, 39, 1, NULL, 1, NULL, -1, N'2012-03-22T12:43:49.523', -1, N'2012-03-22T12:43:49.523' ), ( 87, 40, 1, NULL, 1, NULL, -1, N'2012-04-02T14:42:24.327', -1, N'2012-04-02T14:42:24.327' ), ( 89, 41, 1, NULL, 1, NULL, -1, N'2012-04-11T16:01:51.427', -1, N'2012-04-11T16:01:51.427' ), ( 92, 42, 1, NULL, 1, NULL, -1, N'2012-06-05T17:52:47.083', -1, N'2012-06-05T17:52:47.083' ), ( 94, 43, 1, NULL, 1, NULL, -1, N'2012-07-24T17:27:12.7', -1, N'2012-07-24T17:27:12.7' ), ( 97, 44, 1, NULL, 1, NULL, -1, N'2012-07-30T10:01:27.643', -1, N'2012-07-30T10:01:27.643' ), ( 99, 45, 1, NULL, 1, NULL, -1, N'2012-08-03T11:03:17.4', -1, N'2012-08-03T11:03:17.4' ), ( 51, 25, 2, NULL, 1, NULL, -1, N'2012-02-09T13:57:59.573', -1, N'2012-02-09T13:57:59.573' ), ( 49, 24, 2, NULL, 1, NULL, -1, N'2012-02-09T13:55:22.82', -1, N'2012-02-09T13:55:22.82' ), ( 47, 23, 2, NULL, 1, NULL, -1, N'2012-02-09T13:46:39.667', -1, N'2012-02-09T13:46:39.667' ), ( 45, 22, 2, NULL, 1, NULL, -1, N'2012-02-09T12:34:17.16', -1, N'2012-02-09T12:34:17.16' ), ( 43, 21, 2, NULL, 1, NULL, -1, N'2012-02-09T12:31:55.627', -1, N'2012-02-09T12:31:55.627' ), ( 65, 32, 2, NULL, 1, NULL, 1, N'2012-02-13T16:36:57.25', 1, N'2012-02-13T16:36:57.25' ), ( 63, 31, 2, NULL, 1, NULL, -1, N'2012-02-10T10:11:50.613', -1, N'2012-02-10T10:11:50.613' ), ( 61, 30, 2, NULL, 1, NULL, -1, N'2012-02-10T09:48:53.377', -1, N'2012-02-10T09:48:53.377' ), ( 59, 29, 2, NULL, 1, NULL, -1, N'2012-02-09T15:21:35.76', -1, N'2012-02-09T15:21:35.76' ), ( 77, 36, 2, NULL, 1, NULL, -1, N'2012-03-09T12:34:07.313', -1, N'2012-03-09T12:34:07.313' ), ( 74, 35, 2, NULL, 1, NULL, -1, N'2012-03-09T08:52:34.667', -1, N'2012-03-09T08:52:34.667' ), ( 70, 34, 2, NULL, 1, NULL, -1, N'2012-03-08T13:11:29.3', -1, N'2012-03-08T13:11:29.3' ), ( 68, 33, 2, NULL, 1, NULL, -1, N'2012-03-05T10:45:41.027', -1, N'2012-03-05T10:45:41.027' ), ( 78, 19, 7, NULL, 1, NULL, -1, N'2012-03-21T10:21:41.44', -1, N'2012-03-21T10:21:41.44' ), ( 81, 24, 7, NULL, 1, NULL, -1, N'2012-03-21T11:26:42.6', -1, N'2012-03-21T11:26:42.6' ), ( 86, 39, 7, NULL, 1, NULL, -1, N'2012-03-22T12:43:49.647', -1, N'2012-03-22T12:43:49.647' ), ( 91, 41, 7, NULL, 1, NULL, -1, N'2012-04-11T16:01:51.613', -1, N'2012-04-11T16:01:51.613' ), ( 96, 43, 7, NULL, 1, NULL, -1, N'2012-07-24T17:27:12.923', -1, N'2012-07-24T17:27:12.923' ), ( 36, 18, 8, NULL, 1, NULL, -1, N'2012-02-09T10:26:00.09', -1, N'2012-02-09T10:26:00.09' ) SET IDENTITY_INSERT dbo.UserRoles OFF; -- Write your statements below this line SELECT * FROM Users