OBiCMS SQL Functions

Here are the functions that are needed by the OBiCMS Framework, they are a collection of select queries which return the required rows.

CREATE FUNCTION [dbo].[getBasePage]
(	
	@CMSBasePageID varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(

	SELECT Version,
		doctype, 
		Title, 
		MetaDescription,
		MetaKeyWords, 
		FavIcon, 
		ShortcutIcon,
		[CMSBasePage].[Online],
		SettingsXML   
	FROM 
		CMSBasePage INNER JOIN
        LK_CMSBasePage_CMSBasePageSettings ON CMSBasePage.CMSBasePageIDC = LK_CMSBasePage_CMSBasePageSettings.CMSBasePageIDC INNER JOIN
        CMSBasePageSettings ON 
        LK_CMSBasePage_CMSBasePageSettings.LK_CMSBasePage_CMSBasePageSettingsIDC = CMSBasePageSettings.CMSBasePageSettingsIDC
	Where 
		[CMSBasePage].CMSBasePageID = @CMSBasePageID
)
GO
CREATE FUNCTION [dbo].[getCMSCssSnippet]
(	
	@CMSCssSnippetID varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT
		CMSCSSSnippetSettings.Body, 
		CMSCSSSnippetSettings.Type, 
		CMSCSSSnippetSettings.Version
	FROM
		CMSCSSSnippet INNER JOIN
		LK_CMSCSSSnippet_CMSCSSSnippetSettings ON 
		CMSCSSSnippet.CMSCSSSnippetIDC = LK_CMSCSSSnippet_CMSCSSSnippetSettings.CMSCSSSnippetIDC INNER JOIN
		CMSCSSSnippetSettings ON LK_CMSCSSSnippet_CMSCSSSnippetSettings.CMSCSSSnippetSettingsIDC = CMSCSSSnippetSettings.CMSCSSSnippetSettingsIDC
	Where 
		CMSCssSnippet.CMSCssSnippetID = @CMSCssSnippetID and 
		CMSCssSnippetSettings.[Online] = 1
)
GO
CREATE FUNCTION [dbo].[getTemplate]
(	
	@CMSTemplateID varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(

	SELECT
		CMSTemplateSettings.Version,
		CMSTemplateSettings.Body,
		CMSTemplateSettings.Javascript
	FROM
		CMSTemplate INNER JOIN
        LK_CMSTemplate_CMSTemplateSettings ON CMSTemplate.CMSTemplateIDC = LK_CMSTemplate_CMSTemplateSettings.CMSTemplateIDC INNER JOIN
        CMSTemplateSettings ON LK_CMSTemplate_CMSTemplateSettings.CMSTemplateSettingsIDC = CMSTemplateSettings.CMSTemplateSettingsIDC
	Where
		CMSTemplate.CMSTemplateID = @CMSTemplateID and
		CMSTemplateSettings.[Online] = 1
)
GO
CREATE FUNCTION [dbo].[getTemplatesCssSnippets]
(	
	@CMSTemplateID varchar(50)
)
RETURNS TABLE 
AS
RETURN 
(

	SELECT
		CMSCSSSnippet.CMSCSSSnippetID, 
		CMSCSSSnippetSettings.Version
	FROM
		CMSTemplate INNER JOIN
        LK_CMSTemplate_CMSTemplateSettings ON CMSTemplate.CMSTemplateIDC = LK_CMSTemplate_CMSTemplateSettings.CMSTemplateIDC INNER JOIN
        CMSTemplateSettings ON LK_CMSTemplate_CMSTemplateSettings.CMSTemplateSettingsIDC = CMSTemplateSettings.CMSTemplateSettingsIDC INNER JOIN
		LK_CMSTemplateSettings_CMSCSSSnippet ON 
		CMSTemplateSettings.CMSTemplateSettingsIDC = LK_CMSTemplateSettings_CMSCSSSnippet.CMSTemplateSettingstIDC INNER JOIN
		CMSCSSSnippet ON LK_CMSTemplateSettings_CMSCSSSnippet.CMSCSSSnippetIDC = CMSCSSSnippet.CMSCSSSnippetIDC INNER JOIN
		LK_CMSCSSSnippet_CMSCSSSnippetSettings ON 
		CMSCSSSnippet.CMSCSSSnippetIDC = LK_CMSCSSSnippet_CMSCSSSnippetSettings.CMSCSSSnippetIDC INNER JOIN
		CMSCSSSnippetSettings ON LK_CMSCSSSnippet_CMSCSSSnippetSettings.CMSCSSSnippetSettingsIDC = CMSCSSSnippetSettings.CMSCSSSnippetSettingsIDC
	WHERE 
		(CMSTemplate.CMSTemplateID = @CMSTemplateID) and
		CMSCSSSnippetSettings.Online = 1

)
GO
CREATE FUNCTION [dbo].[getURLRewriteJobs] 
()
RETURNS TABLE 
AS
RETURN 
(
	SELECT	
		CMSBasePage.CMSBasePageID,
		URLRewriteJob.Pattern,
		URLRewriteJob.QueryString
	FROM
		URLRewriteJob INNER JOIN CMSBasePage ON 
		URLRewriteJob.CMSBasePageIDC = CMSBasePage.CMSBasePageIDC
)

Last edited Jun 28, 2010 at 2:52 PM by Cadey, version 1

Comments

No comments yet.