04.03.05.sqldataprovider

完整的商业模板和强大的后台管理功能

/************************************************************/
/*****              SqlDataProvider                     *****/
/*****                                                  *****/
/*****                                                  *****/
/***** Note: To manually execute this script you must   *****/
/*****       perform a search and replace operation     *****/
/*****       for {databaseOwner} and {objectQualifier}  *****/
/*****                                                  *****/
/************************************************************/

/* Update ProfilePropertyDefinition */
/************************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}ProfilePropertyDefinition]') and OBJECTPROPERTY(id, N'IsTable') = 1)
	BEGIN
		ALTER TABLE {databaseOwner}[{objectQualifier}ProfilePropertyDefinition]
			ALTER COLUMN ValidationExpression nvarchar(2000) 
	END
GO


/* Add GetPermissionsByModuleDefID */
/***********************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}GetPermissionsByModuleDefID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}GetPermissionsByModuleDefID]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetPermissionsByModuleDefID]
	@ModuleDefID int
AS
SELECT
	P.[PermissionID],
	P.[PermissionCode],
	P.[ModuleDefID],
	P.[PermissionKey],
	P.[PermissionName]
FROM
	{objectQualifier}Permission P
WHERE
	P.ModuleDefID = @ModuleDefID
GO

/* Update Add Portal Procedure */
/*******************************/

if exists (select * from dbo.sysobjects where id = object_id(N'{databaseOwner}[{objectQualifier}AddPortalInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}[{objectQualifier}AddPortalInfo]
GO

CREATE PROCEDURE {databaseOwner}[{objectQualifier}AddPortalInfo]
	@PortalName         nvarchar(128),
	@Currency           char(3),
	@ExpiryDate         datetime,
	@HostFee            money,
	@HostSpace          int,
	@SiteLogHistory     int,
	@HomeDirectory		varchar(100)

as
DECLARE @PortalID int

insert into {objectQualifier}Portals (
  PortalName,
  ExpiryDate,
  UserRegistration,
  BannerAdvertising,
  Currency,
  HostFee,
  HostSpace,
  Description,
  KeyWords,
  SiteLogHistory,
  HomeDirectory
)
values (
  @PortalName,
  @ExpiryDate,
  0,
  0,
  @Currency,
  @HostFee,
  @HostSpace,
  @PortalName,
  @PortalName,
  @SiteLogHistory,
  @HomeDirectory
)

SET @PortalID = SCOPE_IDENTITY()

IF @HomeDirectory = ''
BEGIN
	UPDATE {objectQualifier}Portals SET HomeDirectory = 'Portals/' + convert(varchar(10), @PortalID) WHERE PortalID = @PortalID
END

SELECT @PortalID
GO

/* Add LastUpdated Column to Folders */
/*************************************/

ALTER TABLE {databaseOwner}{objectQualifier}Folders ADD
	LastUpdated datetime NOT NULL CONSTRAINT DF_{objectQualifier}Folders_LastUpdated DEFAULT getUtcDate()
GO

/* Update AddFolder */
/********************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}AddFolder') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}AddFolder
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFolder
	@PortalID int,
	@FolderPath varchar(300),
	@StorageLocation int,
	@IsProtected bit,
    @IsCached bit
AS
IF NOT EXISTS (SELECT 1 FROM {objectQualifier}Folders WHERE PortalID = @PortalID and FolderPath = @FolderPath)
	BEGIN
		INSERT INTO {objectQualifier}Folders
		(PortalID, FolderPath, StorageLocation, IsProtected, IsCached, LastUpdated)
		VALUES
		(@PortalID, @FolderPath, @StorageLocation, @IsProtected, @IsCached, getUtcDate())
		
		SELECT SCOPE_IDENTITY()
	END
GO

/* Update AddFile */
/******************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}AddFile') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}AddFile
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddFile
	@PortalId    int,
	@FileName    nvarchar(100),
	@Extension   nvarchar(100),
	@Size        int,
	@WIdth       int,
	@Height      int,
	@ContentType nvarchar(200),
	@Folder      nvarchar(200),
	@FolderID    int

AS

DECLARE @FileID int
SET @FileID = (SELECT FileId FROM {objectQualifier}Files WHERE FolderID = @FolderID and FileName = @FileName)

IF @FileID IS NULL
	BEGIN
		INSERT INTO {objectQualifier}Files ( 
		  PortalId,
		  FileName,
		  Extension,
		  Size,
		  Width,
		  Height,
		  ContentType,
		  Folder,
		  FolderID
		)
		VALUES (
		  @PortalId,
		  @FileName,
		  @Extension,
		  @Size,
		  @Width,
		  @Height,
		  @ContentType,
		  @Folder,
		  @FolderID
		)
	END
ELSE
	BEGIN
		UPDATE {objectQualifier}Files
			SET	FileName = @FileName,
				Extension = @Extension,
				Size = @Size,
				Width = @Width,
				Height = @Height,
				ContentType = @ContentType,
				Folder = @Folder,
				FolderID = @FolderID
			WHERE  FileId = @FileID
	END



UPDATE {objectQualifier}Folders
	SET LastUpdated = getUtcDate()
	WHERE FolderID = @FolderID

SELECT SCOPE_IDENTITY()
GO
/* Update DeleteFile */
/*********************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}DeleteFile') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}DeleteFile
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteFile
	@PortalId int,
	@FileName nvarchar(100),
	@FolderID int

AS
DELETE 
	FROM {objectQualifier}Files
	WHERE  FileName = @FileName
		AND    FolderID = @FolderID
		AND    ((PortalId = @PortalId) OR (@PortalId is null and PortalId is null))

UPDATE {objectQualifier}Folders
	SET LastUpdated = getUtcDate()
	WHERE FolderID = @FolderID
GO

/* Update UpdateFile */
/*********************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}UpdateFile') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFile
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateFile
	@FileId      int,
	@FileName    nvarchar(100),
	@Extension   nvarchar(100),
	@Size        int,
	@Width       int,
	@Height      int,
	@ContentType nvarchar(200),
	@Folder      nvarchar(200),
	@FolderID    int

AS
UPDATE {objectQualifier}Files
	SET	FileName = @FileName,
		Extension = @Extension,
		Size = @Size,
		Width = @Width,
		Height = @Height,
		ContentType = @ContentType,
		Folder = @Folder,
		FolderID = @FolderID
	WHERE  FileId = @FileId

UPDATE {objectQualifier}Folders
	SET LastUpdated = getUtcDate()
	WHERE FolderID = @FolderID
GO

/* Update UpdateFolder */
/***********************/

IF EXISTS ( SELECT * FROM sysobjects WHERE id = object_id(N'{databaseOwner}{objectQualifier}UpdateFolder') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE {databaseOwner}{objectQualifier}UpdateFolder
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateFolder
	@PortalID int,
	@FolderID int,
	@FolderPath varchar(300),
	@StorageLocation int,
	@IsProtected bit,
    @IsCached bit
AS
UPDATE {objectQualifier}Folders
	SET FolderPath = @FolderPath,
		StorageLocation = @StorageLocation,
	    IsProtected = @IsProtected,
        IsCached = @IsCached,
		LastUpdated = getUtcDate()
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND FolderID = @FolderID
GO

/* DefaultLanguage */
ALTER TABLE {databaseOwner}{objectQualifier}Portals ALTER COLUMN
	DefaultLanguage nvarchar(10) NOT NULL
GO

/* Update UpdatePortalInfo */
/***************************/