[kooboo] 使用 SQL Server 进行持久化方法

创建一个名为的数据库
去wiki.kooboo.com 找一下有两个sql 语句，

/****** Object:  Table [dbo].[Pages]    Script Date: 02/22/2012 10:28:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Pages]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[Pages](

    [SiteName] [nvarchar](128) NOT NULL,

    [FullName] [nvarchar](128) NOT NULL,

    [ParentPage] [nvarchar](128) NULL,

    [IsDefault] [bit] NOT NULL,

    [ObjectXml] [nvarchar](MAX) NULL,

 CONSTRAINT [PK_Pages] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [FullName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[PageDrafts]    Script Date: 02/22/2012 10:28:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PageDrafts]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[PageDrafts](

    [SiteName] [nvarchar](128) NOT NULL,

    [FullName] [nvarchar](128) NOT NULL,

    [ParentPage] [nvarchar](128) NULL,

    [IsDefault] [bit] NOT NULL,

    [ObjectXml] [nvarchar](MAX) NULL,

 CONSTRAINT [PK_PageDrafts] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [FullName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[Labels]    Script Date: 02/22/2012 10:28:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Labels]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[Labels](

    [SiteName] [nvarchar](128) NOT NULL,

    [Name] [nvarchar](128) NOT NULL,

    [Category] [nvarchar](128) NOT NULL,

    [VALUE] [nvarchar](MAX) NULL,

 CONSTRAINT [PK_Labels] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [Name] ASC,

    [Category] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[LabelCategories]    Script Date: 02/22/2012 10:28:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LabelCategories]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[LabelCategories](

    [SiteName] [nvarchar](128) NOT NULL,

    [CategoryName] [nvarchar](128) NOT NULL,

 CONSTRAINT [PK_LabelCategories] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [CategoryName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[HtmlBlocks]    Script Date: 02/22/2012 10:28:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HtmlBlocks]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[HtmlBlocks](

    [SiteName] [nvarchar](128) NOT NULL,

    [Name] [nvarchar](128) NOT NULL,

    [Body] [nvarchar](MAX) NULL,

 CONSTRAINT [PK_HtmlBlocks] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

/****** Object:  Table [dbo].[SiteUsers]    Script Date: 05/15/2012 14:04:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[SiteUsers](

    [SiteName] [nvarchar](128) NOT NULL,

    [UserName] [nvarchar](128) NOT NULL,

    [ObjectXml] [nvarchar](MAX) NULL,

 CONSTRAINT [PK_SiteUsers] PRIMARY KEY CLUSTERED

    [SiteName] ASC,

    [UserName] 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

/****** Object:  Table [dbo].[Connect_User]    Script Date: 05/26/2011 15:32:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Connect_User]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[Connect_User](

    [Name] [nvarchar](50) NOT NULL,

    [Email] [nvarchar](100) NOT NULL,

    [FirstName] [nvarchar](20) NULL,

    [MiddleName] [nvarchar](20) NULL,

    [LastName] [nvarchar](20) NULL,

    [Gender] [SMALLINT] NOT NULL,

    [Birthday] [datetime] NULL,

    [Country] [nvarchar](50) NULL,

    [City] [nvarchar](50) NULL,

    [Address] [nvarchar](150) NULL,

    [Postcode] [nvarchar](30) NULL,

    [Telphone] [VARCHAR](50) NULL,

    [Mobile] [VARCHAR](50) NULL,

    [CreateDate] [datetime] NOT NULL,

    [FailedPasswordAnswerAttemptCount] [INT] NOT NULL,

    [FailedPasswordAnswerAttemptWindowStart] [datetime] NULL,

    [FailedPasswordAttemptCount] [INT] NOT NULL,

    [FailedPasswordAttemptWindowStart] [datetime] NULL,

    [IsApproved] [bit] NOT NULL,

    [IsLockedOut] [bit] NOT NULL,

    [LastLockoutDate] [datetime] NULL,

    [LastLoginDate] [datetime] NULL,

    [LastPasswordChangedDate] [datetime] NULL,

    [Password] [nvarchar](128) NULL,

    [PasswordAnswer] [nvarchar](128) NULL,

    [PasswordQuestion] [nvarchar](256) NULL,

    [PasswordSalt] [nvarchar](128) NULL,

    [CustomerId] [CHAR](36) NULL,

    [Comment] [nvarchar](2000) NULL,

 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED

    [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Connect_User] ([Name], [Email], [FirstName], [MiddleName], [LastName], [Gender], [Birthday], [Country], [City], [Address], [Postcode], [Telphone], [Mobile], [CreateDate], [FailedPasswordAnswerAttemptCount], [FailedPasswordAnswerAttemptWindowStart], [FailedPasswordAttemptCount], [FailedPasswordAttemptWindowStart], [IsApproved], [IsLockedOut], [LastLockoutDate], [LastLoginDate], [LastPasswordChangedDate], [Password], [PasswordAnswer], [PasswordQuestion], [PasswordSalt], [CustomerId], [Comment]) VALUES (N'admin', N'admin@cc.cc', NULL, NULL, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, CAST(0x00009E3300FC20CD AS DateTime), 0, NULL, 0, NULL, 1, 0, NULL, NULL, NULL, N'FYOL44w+oWRo53/9NQCnqa8RS00=', NULL, NULL, N'aKEwnA2Riwo4SCzyYFfzFw==', NULL, NULL)

/****** Object:  Table [dbo].[CMS_Account_Users]    Script Date: 05/26/2011 15:32:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Users]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[CMS_Account_Users](

    [Email] [nvarchar](128) NOT NULL,

    [UserName] [nvarchar](MAX) NULL,

    [IsAdministrator] [bit] NOT NULL,

    [UICulture] [nvarchar](50) NULL,

    [CustomFieldsXml] [text] NULL,

PRIMARY KEY CLUSTERED

    [Email] 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]

END

GO

INSERT [dbo].[CMS_Account_Users] ([Email], [UserName], [IsAdministrator], [CustomFieldsXml]) VALUES (N'admin@cc.cc', N'admin', 1, N'')

/****** Object:  Table [dbo].[CMS_Account_Roles]    Script Date: 05/26/2011 15:32:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Roles]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[CMS_Account_Roles](

    [Name] [nvarchar](128) NOT NULL,

PRIMARY KEY CLUSTERED

    [Name] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

INSERT [dbo].[CMS_Account_Roles] ([Name]) VALUES (N'Content manager')

INSERT [dbo].[CMS_Account_Roles] ([Name]) VALUES (N'Developer')

/****** Object:  Table [dbo].[CMS_Account_Permissions]    Script Date: 05/26/2011 15:32:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]') AND TYPE IN (N'U'))

BEGIN

CREATE TABLE [dbo].[CMS_Account_Permissions](

    [Id] [nvarchar](128) NOT NULL,

    [RoleName] [nvarchar](128) NOT NULL,

    [Name] [nvarchar](MAX) NULL,

    [AreaName] [nvarchar](MAX) NULL,

    [GROUP] [nvarchar](MAX) NULL,

    [DisplayName] [nvarchar](MAX) NULL,

PRIMARY KEY CLUSTERED

    [Id] ASC,

    [RoleName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Contents__Content', N'Content manager', N'Content', N'Contents', N'', N'Content')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Contents__Content', N'Developer', N'Content', N'Contents', N'', N'Content')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Contents__Folder', N'Developer', N'Folder', N'Contents', N'', N'Folder')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Contents__Schema', N'Developer', N'Schema', N'Contents', N'', N'Schema')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Contents__Setting', N'Developer', N'Setting', N'Contents', N'', N'Setting')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Extensions_Plugin', N'Developer', N'Plugin', N'Sites', N'Extensions', N'Plugin')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Pages_Inline Editing', N'Content manager', N'Inline Editing', N'Sites', N'Pages', N'Inline Editing')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Pages_Inline Editing', N'Developer', N'Inline Editing', N'Sites', N'Pages', N'Inline Editing')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Pages_Page', N'Developer', N'Page', N'Sites', N'Pages', N'Page')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_Custom error', N'Developer', N'Custom error', N'Sites', N'Settings', N'Custom error')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_Page url', N'Developer', N'Page url', N'Sites', N'Settings', N'Page url')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_Robots.txt', N'Developer', N'Robots.txt', N'Sites', N'Settings', N'Robots.txt')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_System', N'Developer', N'System', N'Sites', N'Settings', N'System')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_Url redirect', N'Developer', N'Url redirect', N'Sites', N'Settings', N'Url redirect')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Settings_User', N'Developer', N'User', N'Sites', N'Settings', N'User')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_File', N'Developer', N'File', N'Sites', N'Templates', N'File')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_Label', N'Developer', N'Label', N'Sites', N'Templates', N'Label')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_Layout', N'Developer', N'Layout', N'Sites', N'Templates', N'Layout')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_Script', N'Developer', N'Script', N'Sites', N'Templates', N'Script')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_Theme', N'Developer', N'Theme', N'Sites', N'Templates', N'Theme')

INSERT [dbo].[CMS_Account_Permissions] ([Id], [RoleName], [Name], [AreaName], [GROUP], [DisplayName]) VALUES (N'Sites_Templates_View', N'Developer', N'View', N'Sites', N'Templates', N'View')

/****** Object:  ForeignKey [Role_Permissions]    Script Date: 05/26/2011 15:32:09 ******/

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Role_Permissions]') AND parent_object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]'))

ALTER TABLE [dbo].[CMS_Account_Permissions]  WITH CHECK ADD  CONSTRAINT [Role_Permissions] FOREIGN KEY([RoleName])

REFERENCES [dbo].[CMS_Account_Roles] ([Name])

ON DELETE CASCADE

GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[Role_Permissions]') AND parent_object_id = OBJECT_ID(N'[dbo].[CMS_Account_Permissions]'))

ALTER TABLE [dbo].[CMS_Account_Permissions] CHECK CONSTRAINT [Role_Permissions]

GO

数据库如下。

3，添加对SQL 类库的引用

4，修改 web.config

<connectionStrings>

    <add name="Kooboo.Connect.Providers.SqlServer.Properties.Settings.KoobooConnect" connectionString="Server=.\SQLExpress;Database=Kooboo_CMS; Trusted_Connection=Yes;"/>

    <add name="Kooboo_CMS" connectionString="Server=.\SQLExpress;Database=Kooboo_CMS; Trusted_Connection=Yes;" providerName="System.Data.SqlClient"/>

    <!--<add name="Kooboo_CMS" connectionString="Server=.\SQLExpress;Database=Kooboo_CMS; Trusted_Connection=Yes;" providerName="System.Data.SqlClient"/>-->

  </connectionStrings>

5，其它的也没什么了，

我们在创建一个新站点以后，增加关于新站点的数据表

6 ，系统内置表说明

CMS_Account_Permissions ：帐户权限对应

CMS_Account_Roles ：用户角色

CMS_Account_Users：管理帐户，注意一下这个与 Connect_User 微妙的关联。[CustomFieldsXml]，注意这个字段。记录了用到哪些字段。

Connect_User：整个系统的用户相当于 UC，记录用户基本的信息。

HtmlBlocks：用户自定义html代码块

LabelCategories：标签分类，这个与站点及多语言有关的。

Labels：标签 key – value 嘛，支持多语言

PageDrafts：页面草稿

Pages：页面

SiteUsers：站点用户，每个站点下的用户及与 Connect_User中的用户对应关系，【ObjectXml】，这个字段暂时没有研究过。

7，项目功能

Kooboo.CMS.Account.Persistence.SqlSever：

Kooboo.CMS.Content.Persistence.SqlServer：

Kooboo.CMS.Sites.Providers.SqlServer：

Kooboo.Connect.Providers.SqlServer：

上面这个项目是对系统的功能的 SQLServer版本的支持实现。

posted @ 2013-01-15 18:04 张保维阅读(566) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

张保维

[kooboo] 使用 SQL Server 进行持久化 方法

公告

[kooboo] 使用 SQL Server 进行持久化方法