dotnetnuke.schema.sqldataprovider

SharpNuke源代码

and    ( EndDate > getdate() or EndDate is null )








GO
CREATE TABLE {databaseOwner}[{objectQualifier}SiteLog]
(
[SiteLogId] [int] NOT NULL IDENTITY(1, 1),
[DateTime] [smalldatetime] NOT NULL,
[PortalId] [int] NOT NULL,
[UserId] [int] NULL,
[Referrer] [nvarchar] (255) NULL,
[Url] [nvarchar] (255) NULL,
[UserAgent] [nvarchar] (255) NULL,
[UserHostAddress] [nvarchar] (255) NULL,
[UserHostName] [nvarchar] (255) NULL,
[TabId] [int] NULL,
[AffiliateId] [int] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SiteLog] ADD CONSTRAINT [PK_{objectQualifier}SiteLog] PRIMARY KEY CLUSTERED  ([SiteLogId])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}SiteLog] ON {databaseOwner}[{objectQualifier}SiteLog] ([PortalId])
GO


create procedure {databaseOwner}{objectQualifier}GetSiteLog1

@PortalId int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select 'Date' = convert(varchar,DateTime,102),
 'Views' = count(*),
 'Visitors' = count(distinct {objectQualifier}SiteLog.UserHostAddress),
 'Users' = count(distinct {objectQualifier}SiteLog.UserId)
from {objectQualifier}SiteLog
where PortalId = @PortalId
and {objectQualifier}SiteLog.DateTime between @StartDate and @EndDate
group by convert(varchar,DateTime,102)
order by Date desc








GO
CREATE TABLE {databaseOwner}[{objectQualifier}UrlLog]
(
[UrlLogID] [int] NOT NULL IDENTITY(1, 1),
[UrlTrackingID] [int] NOT NULL,
[ClickDate] [datetime] NOT NULL,
[UserID] [int] NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}UrlLog] ADD CONSTRAINT [PK_{objectQualifier}UrlLog] PRIMARY KEY CLUSTERED  ([UrlLogID])
GO

create procedure {databaseOwner}{objectQualifier}AddUrlLog

@UrlTrackingID int,
@UserID        int

as

insert into {objectQualifier}UrlLog (
  UrlTrackingID,
  ClickDate,
  UserID
)
values (
  @UrlTrackingID,
  getdate(),
  @UserID
)








GO
CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSchedule
	@Server varchar(150)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart, S.Servers
FROM {objectQualifier}Schedule S
LEFT JOIN {objectQualifier}ScheduleHistory SH
ON S.ScheduleID = SH.ScheduleID
WHERE (SH.ScheduleHistoryID = (SELECT TOP 1 S1.ScheduleHistoryID FROM {objectQualifier}ScheduleHistory S1 WHERE S1.ScheduleID = S.ScheduleID ORDER BY S1.NextStart DESC)
OR  SH.ScheduleHistoryID IS NULL)
AND (@Server IS NULL or S.Servers LIKE ',%' + @Server + '%,' or S.Servers IS NULL)
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, SH.NextStart, S.Servers


GO
CREATE TABLE {databaseOwner}[{objectQualifier}SearchWord]
(
[SearchWordsID] [int] NOT NULL IDENTITY(1, 1),
[Word] [nvarchar] (100) NOT NULL,
[IsCommon] [bit] NULL,
[HitCount] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SearchWord] ADD CONSTRAINT [PK_{objectQualifier}SearchWord] PRIMARY KEY CLUSTERED  ([SearchWordsID])
GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchWordByID
	@SearchWordsID int
	
AS

SELECT
	[SearchWordsID],
	[Word],
	[IsCommon],
	[HitCount]
FROM
	{databaseOwner}{objectQualifier}SearchWord
WHERE
	[SearchWordsID] = @SearchWordsID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetScheduleByTypeFullName
@TypeFullName varchar(200)
AS
SELECT S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers
FROM {objectQualifier}Schedule S
WHERE S.TypeFullName = @TypeFullName
GROUP BY S.ScheduleID, S.TypeFullName, S.TimeLapse, S.TimeLapseMeasurement,  S.RetryTimeLapse, S.RetryTimeLapseMeasurement, S.ObjectDependencies, S.AttachToEvent, S.RetainHistoryNum, S.CatchUpEnabled, S.Enabled, S.Servers

GO

create procedure {databaseOwner}{objectQualifier}AddSiteLog

@DateTime                      datetime, 
@PortalId                      int,
@UserId                        int                   = null,
@Referrer                      nvarchar(255)         = null,
@Url                           nvarchar(255)         = null,
@UserAgent                     nvarchar(255)         = null,
@UserHostAddress               nvarchar(255)         = null,
@UserHostName                  nvarchar(255)         = null,
@TabId                         int                   = null,
@AffiliateId                   int                   = null

as
 
declare @SiteLogHistory int

insert into {objectQualifier}SiteLog ( 
  DateTime,
  PortalId,
  UserId,
  Referrer,
  Url,
  UserAgent,
  UserHostAddress,
  UserHostName,
  TabId,
  AffiliateId
)
values (
  @DateTime,
  @PortalId,
  @UserId,
  @Referrer,
  @Url,
  @UserAgent,
  @UserHostAddress,
  @UserHostName,
  @TabId,
  @AffiliateId
)








GO

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

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







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSchedule
@ScheduleID int
,@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
,@Servers varchar(150)
AS
UPDATE {databaseOwner}{objectQualifier}Schedule
SET TypeFullName = @TypeFullName
,TimeLapse = @TimeLapse
,TimeLapseMeasurement = @TimeLapseMeasurement
,RetryTimeLapse = @RetryTimeLapse
,RetryTimeLapseMeasurement = @RetryTimeLapseMeasurement
,RetainHistoryNum = @RetainHistoryNum
,AttachToEvent = @AttachToEvent
,CatchUpEnabled = @CatchUpEnabled
,Enabled = @Enabled
,ObjectDependencies = @ObjectDependencies
,Servers = @Servers
WHERE ScheduleID = @ScheduleID


GO


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

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







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetTabPermission
	@TabPermissionID int
AS

SELECT
	[TabPermissionID],
	[TabID],
	[PermissionID],
	[RoleID],
	[AllowAccess]
FROM
	{databaseOwner}{objectQualifier}TabPermission
WHERE
	[TabPermissionID] = @TabPermissionID








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateSearchCommonWord
	@CommonWordID int, 
	@CommonWord nvarchar(255), 
	@Locale nvarchar(10) 
AS

UPDATE {databaseOwner}{objectQualifier}SearchCommonWords SET
	[CommonWord] = @CommonWord,
	[Locale] = @Locale
WHERE
	[CommonWordID] = @CommonWordID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}AddSchedule
@TypeFullName varchar(200)
,@TimeLapse int
,@TimeLapseMeasurement varchar(2)
,@RetryTimeLapse int
,@RetryTimeLapseMeasurement varchar(2)
,@RetainHistoryNum int
,@AttachToEvent varchar(50)
,@CatchUpEnabled bit
,@Enabled bit
,@ObjectDependencies varchar(300)
,@Servers varchar(150)
AS
INSERT INTO {objectQualifier}Schedule
(TypeFullName
,TimeLapse
,TimeLapseMeasurement
,RetryTimeLapse
,RetryTimeLapseMeasurement
,RetainHistoryNum
,AttachToEvent
,CatchUpEnabled
,Enabled
,ObjectDependencies
,Servers
)
VALUES
(@TypeFullName
,@TimeLapse
,@TimeLapseMeasurement
,@RetryTimeLapse
,@RetryTimeLapseMeasurement
,@RetainHistoryNum
,@AttachToEvent
,@CatchUpEnabled
,@Enabled
,@ObjectDependencies
,@Servers
)


select SCOPE_IDENTITY()


GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteSearchCommonWord
	@CommonWordID int
AS

DELETE FROM {databaseOwner}{objectQualifier}SearchCommonWords
WHERE
	[CommonWordID] = @CommonWordID







GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}UpdateTabPermission
	@TabPermissionID int, 
	@TabID int, 
	@PermissionID int, 
	@RoleID int ,
	@AllowAccess bit
AS

UPDATE {databaseOwner}{objectQualifier}TabPermission SET
	[TabID] = @TabID,
	[PermissionID] = @PermissionID,
	[RoleID] = @RoleID,
	[AllowAccess] = @AllowAccess
WHERE
	[TabPermissionID] = @TabPermissionID








GO
CREATE TABLE {databaseOwner}[{objectQualifier}VendorClassification]
(
[VendorClassificationId] [int] NOT NULL IDENTITY(1, 1),
[VendorId] [int] NOT NULL,
[ClassificationId] [int] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}VendorClassification] ADD CONSTRAINT [PK_{objectQualifier}VendorClassification] PRIMARY KEY CLUSTERED  ([VendorClassificationId])
CREATE NONCLUSTERED INDEX [IX_{objectQualifier}VendorClassification_1] ON {databaseOwner}[{objectQualifier}VendorClassification] ([ClassificationId])
GO
CREATE TABLE {databaseOwner}[{objectQualifier}SystemMessages]
(
[MessageID] [int] NOT NULL IDENTITY(1, 1),
[PortalID] [int] NULL,
[MessageName] [nvarchar] (50) NOT NULL,
[MessageValue] [ntext] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}SystemMessages] ADD CONSTRAINT [PK_{objectQualifier}SystemMessages] PRIMARY KEY CLUSTERED  ([MessageID])
GO

create procedure {databaseOwner}{objectQualifier}AddSystemMessage

@PortalID     int,
@MessageName  nvarchar(50),
@MessageValue ntext

as

insert into {objectQualifier}SystemMessages (
  PortalID,
  MessageName,
  MessageValue
)
values (
  @PortalID,
  @MessageName,
  @MessageValue
)








GO

CREATE PROCEDURE {databaseOwner}{objectQualifier}GetSearchItemWordBySearchWord
	@SearchWordsID int
AS

SELECT
	[SearchItemWordID],
	[SearchItemID],
	[SearchWordsID],
	[Occurrences]
FROM
	{databaseOwner}{objectQualifier}SearchItemWord
WHERE
	[SearchWordsID]=@SearchWordsID







GO



CREATE procedure {databaseOwner}{objectQualifier}GetFile

@FileName  nvarchar(100),
@PortalId  int,
@Folder nvarchar(200)

as

select FileId,
       FileName,
       Extension,
       Size,
       WIdth,
       Height,
       ContentType
from {objectQualifier}Files
where  FileName = @FileName AND Folder=@Folder
and    ((PortalId = @PortalId) or (@PortalId is null and PortalId is null))







GO
CREATE TABLE {databaseOwner}[{objectQualifier}Profile]
(
[ProfileId] [int] NOT NULL IDENTITY(1, 1),
[UserId] [int] NOT NULL,
[PortalId] [int] NOT NULL,
[ProfileData] [ntext] NOT NULL,
[CreatedDate] [datetime] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Profile] ADD CONSTRAINT [PK_{objectQualifier}Profile] PRIMARY KEY CLUSTERED  ([ProfileId])
CREATE UNIQUE NONCLUSTERED INDEX [IX_{objectQualifier}Profile] ON {databaseOwner}[{objectQualifier}Profile] ([UserId], [PortalId])
GO

create procedure {databaseOwner}{objectQualifier}AddUserRole

@PortalId   int,
@UserId     int,
@RoleId     int,
@ExpiryDate datetime = null

as

declare @UserRoleId int

select @UserRoleId = null

select @UserRoleId = UserRoleId
from   {objectQualifier}UserRoles
where  UserId = @UserId
and    RoleId = @RoleId
 
if @UserRoleId is not null
begin
  update {objectQualifier}UserRoles
  set    ExpiryDate = @ExpiryDate
  where  UserRoleId = @UserRoleId

  select @UserRoleId
end
else
begin
  insert into {objectQualifier}UserRoles (
    UserId,
    RoleId,
    ExpiryDate
  )
  values (
    @UserId,
    @RoleId,
    @ExpiryDate
  )

  select SCOPE_IDENTITY()
end






GO


CREATE PROCEDURE {databaseOwner}{objectQualifier}DeleteEventLogType
	@LogTypeKey nvarchar(35)
AS
DELETE FROM {databaseOwner}{objectQualifier}EventLogTypes
WHERE	LogTypeKey = @LogTypeKey
	

GO
CREATE TABLE {databaseOwner}[{objectQualifier}Version]
(
[VersionId] [int] NOT NULL IDENTITY(1, 1),
[Major] [int] NOT NULL,
[Minor] [int] NOT NULL,
[Build] [int] NOT NULL,
[CreatedDate] [datetime] NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}Version] ADD CONSTRAINT [PK_{objectQualifier}Version] PRIMARY KEY CLUSTERED  ([VersionId])
GO

create procedure {databaseOwner}{objectQualifier}GetDatabaseVersion

as

select Major,
       Minor,
       Build
from   {objectQualifier}Version 
where  VersionId = ( select max(VersionId) from {objectQualifier}Version )








GO
CREATE TABLE {databaseOwner}[{objectQualifier}TabModuleSettings]
(
[TabModuleID] [int] NOT NULL,
[SettingName] [nvarchar] (50) NOT NULL,
[SettingValue] [nvarchar] (2000) NOT NULL
)

GO
ALTER TABLE {databaseOwner}[{objectQualifier}TabModuleSettings] ADD CONSTRAINT [PK_{objectQualifier}TabModuleSettings] PRIMARY KEY CLUSTERED  ([TabModuleID], [SettingName])
GO

create procedure {databaseOwner}{objectQualifier}GetTabModuleSetting

@TabModuleId int,
@SettingName nvarchar(50)

as

select SettingName,
       SettingValue
from   {objectQualifier}TabModuleSettings 
where  TabModuleId = @TabModuleId
and    SettingName = @SettingName








GO

create procedure {databaseOwner}{objectQualifier}GetSiteLog5

@PortalId int,
@PortalAlias nvarchar(50),
@StartDate datetime,
@EndDate datetime

as

select'UserAgent' =