OBiCMS SQL Tables

The following SQL script will create all the tables that the OBiCMS Framework needs to operate.

Table Name Usage
CMSBasePage This page holds a pointer to the current version of the page that's been requested by the user.
CMSBasePageSettings Each CMSBasePage has versions and all of them should be stored in this table so you can move to and from old and new versions of the page. Each row has all the information needed to generate a web page, Document type, Title, Meta data, Fav Icons and also the settings file needed to load the Templates, Layouts and Widgets on the page.
CMSCssSnippet Following the same theme as the BasePage each CSSSnippet has versioning and this table has rows that point to versions in the CMSBasePageSettings table.
CMSCSSSnippetSettings The CMSCSSSnippetSettings table holds the information about the CSS snippet, the media type it should be in (for example Screen), the actual CSS body etc.
CMSTemplate See the ideas behind CMSCssSnippet and BasePage.
CMSTemplateSettings This table contains the Body and any JavaScript methods that make up a template.
URLRewriteJob The URLRewriteJob table is the link between the URL the user has requested and the BasePage that gets displayed. The pattern is what we use to match the URL. In the query string column we put a Regex replace string so we can construct a new query string to pass to the BasePage. Finally the CMSBasePageIDC is the IDC of the Basepage that should be loaded.


CREATE TABLE [dbo].[CMSBasePage](
	[CMSBasePageIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSBasePageID] [varchar](50) NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Online] [bit] NOT NULL,
 CONSTRAINT [PK_CMSBasePage] PRIMARY KEY CLUSTERED 
(
	[CMSBasePageIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CMSBasePageSettings](
	[CMSBasePageSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSBasePageSettingsID] [varchar](50) NOT NULL,
	[Version] [int] NOT NULL,
	[doctype] [int] NOT NULL,
	[Title] [varchar](8000) NOT NULL,
	[MetaDescription] [varchar](8000) NOT NULL,
	[MetaKeyWords] [varchar](8000) NOT NULL,
	[FavIcon] [varchar](1000) NOT NULL,
	[ShortcutIcon] [varchar](1000) NOT NULL,
	[SettingsXML] [text] NOT NULL CONSTRAINT [DF_CMSBasePageSettings_SettingsXML]  DEFAULT (''),
 CONSTRAINT [PK_CMSPage] PRIMARY KEY CLUSTERED 
(
	[CMSBasePageSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[CMSCSSSnippet](
	[CMSCSSSnippetIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSCSSSnippetID] [varchar](50) NOT NULL,
	[Name] [varchar](200) NOT NULL,
 CONSTRAINT [PK_CMSCSSSnippet] PRIMARY KEY CLUSTERED 
(
	[CMSCSSSnippetIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CMSCSSSnippetSettings](
	[CMSCSSSnippetSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSCSSSnippetSettingsID] [varchar](50) NOT NULL,
	[Version] [varchar](50) NOT NULL,
	[Body] [varchar](max) NOT NULL,
	[Type] [int] NOT NULL,
	[Online] [bit] NOT NULL,
 CONSTRAINT [PK_CMSCSSSnippetSettings] PRIMARY KEY CLUSTERED 
(
	[CMSCSSSnippetSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CMSTemplate](
	[CMSTemplateIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSTemplateID] [varchar](50) NOT NULL,
	[Name] [varchar](200) NOT NULL,
 CONSTRAINT [PK_CMSTemplate] PRIMARY KEY CLUSTERED 
(
	[CMSTemplateIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[CMSTemplateSettings](
	[CMSTemplateSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[CMSTemplateSettingsID] [varchar](50) NOT NULL,
	[Version] [int] NOT NULL,
	[Body] [varchar](max) NOT NULL,
	[Javascript] [varchar](max) NOT NULL,
	[Online] [bit] NOT NULL,
 CONSTRAINT [PK_CMSTemplateSettings] PRIMARY KEY CLUSTERED 
(
	[CMSTemplateSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[URLRewriteJob](
	[URLRewriteJobIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[URLRewriteJobID] [varchar](50) NOT NULL,
	[CMSBasePageIDC] [bigint] NOT NULL,
	[Pattern] [varchar](1000) NOT NULL,
	[QueryString] [varchar](50) NOT NULL CONSTRAINT [DF_URLRewriteJob_QueryString]  DEFAULT (''),
 CONSTRAINT [PK_URLRewriteJob] PRIMARY KEY CLUSTERED 
(
	[URLRewriteJobIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[URLRewriteJob]  WITH CHECK ADD  CONSTRAINT [FK_URLRewriteJob_URLRewriteJob] FOREIGN KEY([CMSBasePageIDC])
REFERENCES [dbo].[CMSBasePage] ([CMSBasePageIDC])
GO
ALTER TABLE [dbo].[URLRewriteJob] CHECK CONSTRAINT [FK_URLRewriteJob_URLRewriteJob]

CREATE TABLE [dbo].[LK_CMSBasePage_CMSBasePageSettings](
	[LK_CMSBasePage_CMSBasePageSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[LK_CMSBasePage_CMSBasePageSettingsID] [varchar](50) NOT NULL,
	[CMSBasePageIDC] [bigint] NOT NULL,
	[CMSBasePageSettingsIDC] [bigint] NOT NULL,
 CONSTRAINT [PK_LK_CMSBasePage_CMSBasePageSettings] PRIMARY KEY CLUSTERED 
(
	[LK_CMSBasePage_CMSBasePageSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[LK_CMSBasePage_CMSBasePageSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSBasePage_CMSBasePageSettings_CMSBasePage] FOREIGN KEY([CMSBasePageIDC])
REFERENCES [dbo].[CMSBasePage] ([CMSBasePageIDC])
GO
ALTER TABLE [dbo].[LK_CMSBasePage_CMSBasePageSettings] CHECK CONSTRAINT [FK_LK_CMSBasePage_CMSBasePageSettings_CMSBasePage]
GO
ALTER TABLE [dbo].[LK_CMSBasePage_CMSBasePageSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSBasePage_CMSBasePageSettings_CMSBasePageSettings] FOREIGN KEY([CMSBasePageSettingsIDC])
REFERENCES [dbo].[CMSBasePageSettings] ([CMSBasePageSettingsIDC])
GO
ALTER TABLE [dbo].[LK_CMSBasePage_CMSBasePageSettings] CHECK CONSTRAINT [FK_LK_CMSBasePage_CMSBasePageSettings_CMSBasePageSettings]

CREATE TABLE [dbo].[LK_CMSCSSSnippet_CMSCSSSnippetSettings](
	[LK_CMSCSSSnippet_CMSCSSSnippetSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[LK_CMSCSSSnippet_CMSCSSSnippetSettingsID] [varchar](50) NOT NULL,
	[CMSCSSSnippetIDC] [bigint] NOT NULL,
	[CMSCSSSnippetSettingsIDC] [bigint] NOT NULL,
 CONSTRAINT [PK_LK_CMSCSSSnippet_CMSCSSSnippetSettings] PRIMARY KEY CLUSTERED 
(
	[LK_CMSCSSSnippet_CMSCSSSnippetSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[LK_CMSCSSSnippet_CMSCSSSnippetSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSCSSSnippet_CMSCSSSnippetSettings_CMSCSSSnippet] FOREIGN KEY([CMSCSSSnippetIDC])
REFERENCES [dbo].[CMSCSSSnippet] ([CMSCSSSnippetIDC])
GO
ALTER TABLE [dbo].[LK_CMSCSSSnippet_CMSCSSSnippetSettings] CHECK CONSTRAINT [FK_LK_CMSCSSSnippet_CMSCSSSnippetSettings_CMSCSSSnippet]
GO
ALTER TABLE [dbo].[LK_CMSCSSSnippet_CMSCSSSnippetSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSCSSSnippet_CMSCSSSnippetSettings_CMSCSSSnippetSettings] FOREIGN KEY([CMSCSSSnippetSettingsIDC])
REFERENCES [dbo].[CMSCSSSnippetSettings] ([CMSCSSSnippetSettingsIDC])
GO
ALTER TABLE [dbo].[LK_CMSCSSSnippet_CMSCSSSnippetSettings] CHECK CONSTRAINT [FK_LK_CMSCSSSnippet_CMSCSSSnippetSettings_CMSCSSSnippetSettings]

CREATE TABLE [dbo].[LK_CMSTemplate_CMSTemplateSettings](
	[LK_CMSTemplate_CMSTemplateSettingsIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[LK_CMSTemplate_CMSTemplateSettingsID] [varchar](50) NOT NULL,
	[CMSTemplateIDC] [bigint] NOT NULL,
	[CMSTemplateSettingsIDC] [bigint] NOT NULL,
 CONSTRAINT [PK_LK_CMSTemplate_CMSTemplateSettings] PRIMARY KEY CLUSTERED 
(
	[LK_CMSTemplate_CMSTemplateSettingsIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[LK_CMSTemplate_CMSTemplateSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSTemplate_CMSTemplateSettings_CMSTemplate] FOREIGN KEY([CMSTemplateIDC])
REFERENCES [dbo].[CMSTemplate] ([CMSTemplateIDC])
GO
ALTER TABLE [dbo].[LK_CMSTemplate_CMSTemplateSettings] CHECK CONSTRAINT [FK_LK_CMSTemplate_CMSTemplateSettings_CMSTemplate]
GO
ALTER TABLE [dbo].[LK_CMSTemplate_CMSTemplateSettings]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSTemplate_CMSTemplateSettings_CMSTemplateSettings] FOREIGN KEY([CMSTemplateSettingsIDC])
REFERENCES [dbo].[CMSTemplateSettings] ([CMSTemplateSettingsIDC])
GO
ALTER TABLE [dbo].[LK_CMSTemplate_CMSTemplateSettings] CHECK CONSTRAINT [FK_LK_CMSTemplate_CMSTemplateSettings_CMSTemplateSettings]

CREATE TABLE [dbo].[LK_CMSTemplateSettings_CMSCSSSnippet](
	[LK_CMSTemplateSettings_CMSCSSSnippetIDC] [bigint] IDENTITY(1,1) NOT NULL,
	[LK_CMSTemplateSettings_CMSCSSSnippetID] [varchar](50) NOT NULL,
	[CMSTemplateSettingstIDC] [bigint] NOT NULL,
	[CMSCSSSnippetIDC] [bigint] NOT NULL,
 CONSTRAINT [PK_LK_CMSTemplateSettings_CMSCSSSnippet] PRIMARY KEY CLUSTERED 
(
	[LK_CMSTemplateSettings_CMSCSSSnippetIDC] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[LK_CMSTemplateSettings_CMSCSSSnippet]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSTemplateSettings_CMSCSSSnippet_CMSCSSSnippet] FOREIGN KEY([CMSCSSSnippetIDC])
REFERENCES [dbo].[CMSCSSSnippet] ([CMSCSSSnippetIDC])
GO
ALTER TABLE [dbo].[LK_CMSTemplateSettings_CMSCSSSnippet] CHECK CONSTRAINT [FK_LK_CMSTemplateSettings_CMSCSSSnippet_CMSCSSSnippet]
GO
ALTER TABLE [dbo].[LK_CMSTemplateSettings_CMSCSSSnippet]  WITH CHECK ADD  CONSTRAINT [FK_LK_CMSTemplateSettings_CMSCSSSnippet_CMSTemplateSettings] FOREIGN KEY([CMSTemplateSettingstIDC])
REFERENCES [dbo].[CMSTemplateSettings] ([CMSTemplateSettingsIDC])
GO
ALTER TABLE [dbo].[LK_CMSTemplateSettings_CMSCSSSnippet] CHECK CONSTRAINT [FK_LK_CMSTemplateSettings_CMSCSSSnippet_CMSTemplateSettings]

Last edited Jun 28, 2010 at 5:16 PM by Cadey, version 4

Comments

No comments yet.