02.02.00.sqldataprovider

SharpNuke源代码

select SCOPE_IDENTITY()

GO

ALTER  procedure {databaseOwner}{objectQualifier}GetModuleControlsByKey

@ControlKey        nvarchar(50),
@ModuleDefId       int

as

select {objectQualifier}ModuleDefinitions.*,
       ControlTitle,
       ControlSrc,
       IconFile,
       ControlType,
       HelpUrl
from   {objectQualifier}ModuleControls
left outer join {objectQualifier}ModuleDefinitions on {objectQualifier}ModuleControls.ModuleDefId = {objectQualifier}ModuleDefinitions.ModuleDefId
where  ((ControlKey is null and @ControlKey is null) or (ControlKey = @ControlKey))
and    (({objectQualifier}ModuleControls.ModuleDefId is null and @ModuleDefId is null) or ({objectQualifier}ModuleControls.ModuleDefId = @ModuleDefId))
and    ControlType <> -2
order by ViewOrder

GO



alter  procedure {databaseOwner}{objectQualifier}GetPortalTabModules

@PortalId int, 
@TabId int

as

select M.*,
       MC.ControlSrc,
       MC.ControlType,
       MC.ControlTitle,
       MC.HelpURL,
       DM.*
from {databaseOwner}{objectQualifier}Modules M
inner join {databaseOwner}{objectQualifier}Tabs T on M.TabId = T.TabId
inner join {databaseOwner}{objectQualifier}ModuleDefinitions MD on M.ModuleDefId = MD.ModuleDefId
inner join {databaseOwner}{objectQualifier}ModuleControls MC on MD.ModuleDefId = MC.ModuleDefId
inner join {databaseOwner}{objectQualifier}DesktopModules DM on MD.DesktopModuleId = DM.DesktopModuleId
where  (M.TabId = @TabId or (M.AllTabs = 1 and T.PortalId = @PortalId))
and    ControlKey is null
order by ModuleOrder


GO

create procedure {databaseOwner}{objectQualifier}GetRoleByName

@PortalId int,
@RoleName nvarchar(50)

as

select RoleId,
       PortalId,
       RoleName,
       Description,
       ServiceFee,
       BillingPeriod,
       BillingFrequency,
       TrialFee,
       TrialPeriod,
       TrialFrequency,
       IsPublic,
       AutoAssignment
from {objectQualifier}Roles
where  PortalId = @PortalId and RoleName = @RoleName

GO

delete from {databaseOwner}{objectQualifier}Files
where portalid not in (select portalid from {databaseOwner}{objectQualifier}Portals)
	and portalid is not null

GO

ALTER TABLE {databaseOwner}{objectQualifier}Files WITH NOCHECK ADD CONSTRAINT
	FK_Files_Portals FOREIGN KEY
	(
	PortalID
	) REFERENCES {databaseOwner}{objectQualifier}Portals
	(
	PortalID
	) ON DELETE CASCADE
	 NOT FOR REPLICATION

GO

insert into {objectQualifier}ModuleControls ( ControlKey, ControlTitle, ModuleDefID, ControlSrc, IconFile, ControlType, ViewOrder )
values ( 'ACTIONBUTTON', null, null, 'Admin/Containers/ActionButton.ascx', null, -2, null )
GO


alter procedure {databaseOwner}{objectQualifier}GetModuleDefinitions

@DesktopModuleId int

as

select *
from   {databaseOwner}{objectQualifier}ModuleDefinitions
where  DesktopModuleId = @DesktopModuleId or @DesktopModuleId = -1


GO




create procedure {databaseOwner}{objectQualifier}GetAllModules

as

select *
from   {databaseOwner}{objectQualifier}Modules

GO


create procedure {databaseOwner}{objectQualifier}GetRoles

as

select *
from   {databaseOwner}{objectQualifier}Roles

GO

CREATE TABLE {databaseOwner}{objectQualifier}FolderPermission (
	[FolderPermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[FolderID] [int] NOT NULL ,
	[PermissionID] [int] NOT NULL ,
	[RoleID] [int] NOT NULL ,
	[AllowAccess] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}Folders (
	[FolderID] [int] IDENTITY (1, 1) NOT NULL ,
	[PortalID] [int] NULL ,
	[FolderPath] [varchar] (300) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}ModulePermission (
	[ModulePermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[ModuleID] [int] NOT NULL ,
	[PermissionID] [int] NOT NULL ,
	[RoleID] [int] NOT NULL ,
	[AllowAccess] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}Permission (
	[PermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[PermissionCode] [varchar] (50)  NOT NULL ,
	[ModuleDefID] [int] NOT NULL ,
	[PermissionKey] [varchar] (20)  NOT NULL ,
	[PermissionName] [varchar] (50)  NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE {databaseOwner}{objectQualifier}TabPermission (
	[TabPermissionID] [int] IDENTITY (1, 1) NOT NULL ,
	[TabID] [int] NOT NULL ,
	[PermissionID] [int] NOT NULL ,
	[RoleID] [int] NOT NULL ,
	[AllowAccess] [bit] NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE {databaseOwner}{objectQualifier}FolderPermission WITH NOCHECK ADD 
	CONSTRAINT [PK_FolderPermission] PRIMARY KEY  CLUSTERED 
	(
		[FolderPermissionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Folders WITH NOCHECK ADD 
	CONSTRAINT [PK_Folders] PRIMARY KEY  CLUSTERED 
	(
		[FolderID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}ModulePermission WITH NOCHECK ADD 
	CONSTRAINT [PK_ModulePermission] PRIMARY KEY  CLUSTERED 
	(
		[ModulePermissionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Permission WITH NOCHECK ADD 
	CONSTRAINT [PK_Permission] PRIMARY KEY  CLUSTERED 
	(
		[PermissionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}TabPermission WITH NOCHECK ADD 
	CONSTRAINT [PK_TabPermission] PRIMARY KEY  CLUSTERED 
	(
		[TabPermissionID]
	)  ON [PRIMARY] 
GO

ALTER TABLE {databaseOwner}{objectQualifier}FolderPermission ADD 
	CONSTRAINT [FK_FolderPermission_Folders] FOREIGN KEY 
	(
		[FolderID]
	) REFERENCES {databaseOwner}{objectQualifier}Folders (
		[FolderID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_FolderPermission_Permission] FOREIGN KEY 
	(
		[PermissionID]
	) REFERENCES {databaseOwner}{objectQualifier}Permission (
		[PermissionID]
	) ON DELETE CASCADE 
GO

ALTER TABLE {databaseOwner}{objectQualifier}Folders ADD 
	CONSTRAINT [FK_Folders_Portals] FOREIGN KEY 
	(
		[PortalID]
	) REFERENCES {databaseOwner}{objectQualifier}Portals (
		[PortalID]
	) ON DELETE CASCADE 
GO

ALTER TABLE {databaseOwner}{objectQualifier}ModulePermission ADD 
	CONSTRAINT [FK_ModulePermission_Modules] FOREIGN KEY 
	(
		[ModuleID]
	) REFERENCES {databaseOwner}{objectQualifier}Modules (
		[ModuleID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_ModulePermission_Permission] FOREIGN KEY 
	(
		[PermissionID]
	) REFERENCES {databaseOwner}{objectQualifier}Permission (
		[PermissionID]
	) ON DELETE CASCADE 
GO

ALTER TABLE {databaseOwner}{objectQualifier}TabPermission ADD 
	CONSTRAINT [FK_TabPermission_Permission] FOREIGN KEY 
	(
		[PermissionID]
	) REFERENCES {databaseOwner}{objectQualifier}Permission (
		[PermissionID]
	) ON DELETE CASCADE ,
	CONSTRAINT [FK_TabPermission_Tabs] FOREIGN KEY 
	(
		[TabID]
	) REFERENCES {databaseOwner}{objectQualifier}Tabs (
		[TabID]
	) ON DELETE CASCADE 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeletePermission
	@PermissionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}Permission
WHERE
	[PermissionID] = @PermissionID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetPermission
	@PermissionID int
AS

SELECT
	[PermissionID],
	[PermissionCode],
	[ModuleDefID],
	[PermissionKey],
	[PermissionName]
FROM
	{databaseOwner}{objectQualifier}Permission
WHERE
	[PermissionID] = @PermissionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}AddPermission
	@ModuleDefID int,
	@PermissionCode varchar(50),
	@PermissionKey varchar(20),
	@PermissionName varchar(50)
AS

INSERT INTO {databaseOwner}{objectQualifier}Permission (
	[ModuleDefID],
	[PermissionCode],
	[PermissionKey],
	[PermissionName]
) VALUES (
	@ModuleDefID,
	@PermissionCode,
	@PermissionKey,
	@PermissionName
)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdatePermission
	@PermissionID int, 
	@PermissionCode varchar(50),
	@ModuleDefID int, 
	@PermissionKey varchar(20), 
	@PermissionName varchar(50) 
AS

UPDATE {databaseOwner}{objectQualifier}Permission SET
	[ModuleDefID] = @ModuleDefID,
	[PermissionCode] = @PermissionCode,
	[PermissionKey] = @PermissionKey,
	[PermissionName] = @PermissionName
WHERE
	[PermissionID] = @PermissionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteModulePermissionsByModuleID
	@ModuleID int
AS

DELETE FROM {databaseOwner}{objectQualifier}ModulePermission
WHERE
	[ModuleID] = @ModuleID
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteModulePermission
	@ModulePermissionID int
AS

DELETE FROM {databaseOwner}{objectQualifier}ModulePermission
WHERE
	[ModulePermissionID] = @ModulePermissionID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}GetModulePermission
	@ModulePermissionID int
AS

SELECT
	M.[ModulePermissionID],
	M.[ModuleID],
	P.[PermissionID],
	M.[RoleID],
	case M.RoleID
		when -1 then 'All Users'
		when -2 then 'Superuser'
		when -3 then 'Unauthenticated Users'
		else 	R.RoleName
	end
	'RoleName',
	M.[AllowAccess],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{databaseOwner}{objectQualifier}ModulePermission M
LEFT JOIN
	{databaseOwner}{objectQualifier}Permission P
ON	M.PermissionID = P.PermissionID
LEFT JOIN
	{databaseOwner}{objectQualifier}ModuleDefinitions MD
ON	P.ModuleDefID = MD.ModuleDefID
LEFT JOIN
	{databaseOwner}{objectQualifier}Roles R
ON	M.RoleID = R.RoleID
WHERE
	M.ModulePermissionID = @ModulePermissionID


GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddModulePermission
	@ModuleID int,
	@PermissionID int,
	@RoleID int,
	@AllowAccess bit
AS

INSERT INTO {databaseOwner}{objectQualifier}ModulePermission (
	[ModuleID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
) VALUES (
	@ModuleID,
	@PermissionID,
	@RoleID,
	@AllowAccess
)

select SCOPE_IDENTITY()

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateModulePermission
	@ModulePermissionID int, 
	@ModuleID int, 
	@PermissionID int, 
	@RoleID int ,
	@AllowAccess bit
AS

UPDATE {databaseOwner}{objectQualifier}ModulePermission SET
	[ModuleID] = @ModuleID,
	[PermissionID] = @PermissionID,
	[RoleID] = @RoleID,
	[AllowAccess] = @AllowAccess
WHERE
	[ModulePermissionID] = @ModulePermissionID

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE {databaseOwner}{objectQualifier}GetModulePermissionsByModuleID
	@ModuleID int, 
	@PermissionID int
AS

SELECT
	M.[ModulePermissionID],
	M.[ModuleID],
	P.[PermissionID],
	M.[RoleID],