03.02.03.sqldataprovider

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

        Content image NULL
GO

/* make FolderPath values consistent with Files table Folder column */
UPDATE {objectQualifier}Folders
	SET    FolderPath = FolderPath + '/'
WHERE  FolderPath <> '' AND RIGHT(FolderPath, 1) <> '/'
GO

/* Handle Updating Files table with new FolderID column based on Folders table */
UPDATE {objectQualifier}Files
	SET    FolderID = FO.FolderID
FROM   {objectQualifier}Files F, {objectQualifier}Folders FO
WHERE F.Folder = FO.FolderPath
	AND ( ( F.PortalID = FO.PortalID ) OR ( F.PortalID is NULL AND  FO.PortalID is NULL ) )
GO

/* This deletes the files with no valid folder Ids */
/***************************************************/

DELETE {objectQualifier}Files
	WHERE FolderID Is NULL
GO

ALTER TABLE {databaseOwner}{objectQualifier}Files
	DROP CONSTRAINT DF_{objectQualifier}Files_FolderID
GO

ALTER TABLE {databaseOwner}{objectQualifier}Files ADD CONSTRAINT
	FK_{objectQualifier}Files_{objectQualifier}Folders FOREIGN KEY
	(
	FolderID
	) REFERENCES {databaseOwner}{objectQualifier}Folders
	(
	FolderID
	) 

GO

ALTER TABLE {databaseOwner}{objectQualifier}Folders ADD
	StorageLocation int NOT NULL CONSTRAINT DF_{objectQualifier}Folders_StorageLocation DEFAULT 0,
	IsProtected bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsProtected DEFAULT 0,
	IsCached bit NOT NULL CONSTRAINT DF_{objectQualifier}Folders_IsCached DEFAULT 0
GO

/* This sets all portal root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = ''
GO

/* This sets all containers root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = 'Containers/'
GO

/* This sets all skins root folders to protected */
update {objectQualifier}Folders
Set     IsProtected = 1
WHERE
	FolderPath = 'Skins/'
GO

/* This sets all cache root folders to protected */
UPDATE {objectQualifier}Folders
	SET     IsProtected = 1
WHERE
	FolderPath = 'Cache/'
GO

if exists (select * from dbo.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

insert into {objectQualifier}Files ( 
  PortalId,
  FileName,
  Extension,
  Size,
  WIdth,
  Height,
  ContentType,
  Folder,
  FolderID
)
values (
  @PortalId,
  @FileName,
  @Extension,
  @Size,
  @WIdth,
  @Height,
  @ContentType,
  @Folder,
  @FolderID
)

select SCOPE_IDENTITY()

GO

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

CREATE procedure {databaseOwner}[{objectQualifier}GetAllFiles]

AS

SELECT
	FileId,
             FO.PortalId,
             FileName,
             Extension,
             Size,
             Width,
             Height,
             ContentType,
             F.FolderID,
             'Folder' = FolderPath,
	     StorageLocation,
             IsCached
FROM 
	{objectQualifier}Files F

INNER JOIN 
	{objectQualifier}Folders FO on F.FolderID = FO.FolderID
GO

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

CREATE procedure {databaseOwner}{objectQualifier}GetFile

@FileName  nvarchar(100),
@PortalId  int,
@FolderID  int

as

select FileId,
       {objectQualifier}Folders.PortalId,
       FileName,
       Extension,
       Size,
       Width,
       Height,
       ContentType,
       {objectQualifier}Files.FolderID,
       'Folder' = FolderPath,
       StorageLocation,
       IsCached
from {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where  FileName = @FileName 
and    {objectQualifier}Files.FolderID = @FolderID
and    (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))

GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFiles]

@PortalId   int,
@FolderID   int

AS

SELECT 
	FileId,
             FO.PortalId,
             FileName,
             Extension,
             Size,
             Width,
             Height,
             ContentType,
             F.FolderID,
	     'Folder' = FolderPath,
             StorageLocation,
             IsCached
FROM 
	{objectQualifier}Files F
INNER JOIN 
	{objectQualifier}Folders FO on F.FolderID = FO.FolderID
WHERE   
	F.FolderID = @FolderID
AND     
	((FO.PortalId = @PortalId) or (@PortalId is NULL AND FO.PortalId is NULL))
ORDER BY FileName
GO


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

create procedure {databaseOwner}{objectQualifier}GetFileById

@FileId   int,
@PortalId int

as

select FileId,
       {objectQualifier}Folders.PortalId,
       FileName,
       Extension,
       Size,
       Width,
       Height,
       ContentType,
       {objectQualifier}Files.FolderID,
       'Folder' = FolderPath,
       StorageLocation,
       IsCached
from   {objectQualifier}Files
inner join {objectQualifier}Folders on {objectQualifier}Files.FolderID = {objectQualifier}Folders.FolderID
where  FileId = @FileId
and    (({objectQualifier}Folders.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Folders.PortalId is null))

GO

if exists (select * from dbo.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

GO

if exists (select * from dbo.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))

GO

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

CREATE procedure {databaseOwner}{objectQualifier}UpdateFileContent

@FileId      int,
@Content     image

as

update {objectQualifier}Files
set    Content = @Content
where  FileId = @FileId

GO

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

CREATE procedure {databaseOwner}{objectQualifier}GetFileContent

@FileId   int,
@PortalId int

as

select Content
from   {objectQualifier}Files
where  FileId = @FileId
and    (({objectQualifier}Files.PortalId = @PortalId) or (@PortalId is null and {objectQualifier}Files.PortalId is null))

GO

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}GetFolders]
	@PortalID int,
	@FolderID int,
	@FolderPath nvarchar(300)
AS
SELECT *
	FROM {objectQualifier}Folders
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
		AND (FolderID = @FolderID or @FolderID = -1)
		AND (FolderPath = @FolderPath or @FolderPath = '')
	ORDER BY FolderPath
GO

if exists (select * from dbo.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)
		VALUES
		(@PortalID, @FolderPath, @StorageLocation, @IsProtected, @IsCached)
		
		SELECT SCOPE_IDENTITY()
	END

GO

if exists (select * from dbo.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
	WHERE ((PortalID = @PortalID) or (PortalID is null and @PortalID is null))
	AND FolderID = @FolderID

GO

/* Add Transfer Profile Support Procedure */
/******************************************/

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

CREATE PROCEDURE {databaseOwner}[{objectQualifier}TransferUsersFromFlatProfile]

	@PropertyName	nvarchar(100)

AS
DECLARE @sql varchar(8000)
	SELECT @sql = 'INSERT INTO {objectQualifier}UserProfile (UserID, PropertyDefinitionID, PropertyValue, LastUpdatedDate) '
	SELECT @sql = @sql + 
				'SELECT fp.UserID, 
					pd.PropertyDefinitionID, 
					Convert(varchar(50),' + column_name + ') AS PropertyValue, 
					fp.LastUpdatedDate 
				FROM {objectQualifier}FlatProfile fp 
					INNER JOIN {objectQualifier}ProfilePropertyDefinition pd 
						ON fp.PortalID = pd.PortalID 
						AND pd.PropertyName = ''' + column_name + '''
				WHERE fp.' + column_name + ' IS NOT NULL 
				UNION '
	FROM information_schema.columns
	WHERE table_name='{objectQualifier}FlatProfile' AND column_name = @PropertyName 
	SELECT @sql = Left(@sql,Le