siteserver_schema.sql

一个真正的CMS系统,可以完全实现建站自动化,用.NET开发.

CREATE TABLE siteserver_Roles(
    RoleName           nvarchar(255)    NOT NULL,
    CreatorUserName    nvarchar(255)    DEFAULT '' NOT NULL,
    Description        ntext            DEFAULT '' NOT NULL,
    CONSTRAINT PK_siteserver_Roles PRIMARY KEY CLUSTERED (RoleName)
)
go



CREATE TABLE siteserver_SeoMeta(
    SeoMetaID              int              IDENTITY(1,1),
    PublishmentSystemID    int              NOT NULL,
    SeoMetaName            varchar(50)      DEFAULT '' NOT NULL,
    IsDefault              varchar(18)      DEFAULT '' NOT NULL,
    PageTitle              nvarchar(80)     DEFAULT '' NOT NULL,
    Keywords               nvarchar(100)    DEFAULT '' NOT NULL,
    Description            nvarchar(200)    DEFAULT '' NOT NULL,
    Copyright              nvarchar(255)    DEFAULT '' NOT NULL,
    Author                 nvarchar(50)     DEFAULT '' NOT NULL,
    Email                  nvarchar(50)     DEFAULT '' NOT NULL,
    Language               varchar(50)      DEFAULT '' NOT NULL,
    Charset                varchar(50)      DEFAULT '' NOT NULL,
    Distribution           varchar(50)      DEFAULT '' NOT NULL,
    Rating                 varchar(50)      DEFAULT '' NOT NULL,
    Robots                 varchar(50)      DEFAULT '' NOT NULL,
    RevisitAfter           varchar(50)      DEFAULT '' NOT NULL,
    Expires                varchar(50)      DEFAULT '' NOT NULL,
    CONSTRAINT PK_siteserver_SeoMeta PRIMARY KEY CLUSTERED (SeoMetaID)
)
go



CREATE TABLE siteserver_SeoMetasInNodes(
    NodeID                 int            NOT NULL,
    IsChannel              varchar(18)    DEFAULT '' NOT NULL,
    SeoMetaID              int            NOT NULL,
    PublishmentSystemID    int            NOT NULL,
    CONSTRAINT PK_siteserver_SeoMetasInNodes PRIMARY KEY CLUSTERED (NodeID, IsChannel, SeoMetaID)
)
go



CREATE TABLE siteserver_System(
    SystemID                    int             NOT NULL,
    ApplicationName             varchar(50)     DEFAULT '' NOT NULL,
    SystemName                  nvarchar(50)    DEFAULT '' NOT NULL,
    SystemDir                   varchar(50)     DEFAULT '' NOT NULL,
    SystemUrl                   varchar(200)    DEFAULT '' NOT NULL,
    AuxiliaryTableForContent    varchar(50)     DEFAULT '' NOT NULL,
    AuxiliaryTableForComment    varchar(50)     DEFAULT '' NOT NULL,
    SettingsXML                 ntext           DEFAULT '' NOT NULL,
    CONSTRAINT PK_siteserver_System PRIMARY KEY CLUSTERED (SystemID)
)
go



CREATE TABLE siteserver_Template(
    TemplateID             int            IDENTITY(1,1),
    PublishmentSystemID    int            NOT NULL,
    TemplateName           varchar(50)    DEFAULT '' NOT NULL,
    TemplateType           varchar(50)    DEFAULT '' NOT NULL,
    RelatedFileName        varchar(50)    DEFAULT '' NOT NULL,
    CreatedFileFullName    varchar(50)    DEFAULT '' NOT NULL,
    CreatedFileExtName     varchar(50)    DEFAULT '' NOT NULL,
    Charset                varchar(50)    DEFAULT '' NOT NULL,
    IsDefault              varchar(18)    DEFAULT '' NOT NULL,
    CONSTRAINT PK_siteserver_Template PRIMARY KEY CLUSTERED (TemplateID)
)
go



CREATE TABLE siteserver_Tracker(
    TrackerID                int         IDENTITY(1,1),
    PublishmentSystemID      int         NOT NULL,
    FormerUniqueAccessNum    int         DEFAULT 0 NOT NULL,
    TrackingCurrentMinute    int         DEFAULT 0 NOT NULL,
    AddDateTime              datetime    DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_siteserver_Tracker PRIMARY KEY CLUSTERED (TrackerID)
)
go



CREATE TABLE siteserver_TrackerAnalysis(
    TrackerAnalysisID     int             IDENTITY(1,1),
    TrackerID             int             NOT NULL,
    IsUniqueAccess        varchar(18)     DEFAULT '' NOT NULL,
    LastAccessDateTime    datetime        DEFAULT getdate() NOT NULL,
    PageUrl               varchar(200)    DEFAULT '' NOT NULL,
    Referrer              varchar(200)    DEFAULT '' NOT NULL,
    IPAddress             varchar(200)    DEFAULT '' NOT NULL,
    OperatingSystem       varchar(200)    DEFAULT '' NOT NULL,
    Browser               varchar(200)    DEFAULT '' NOT NULL,
    AccessDateTime        datetime        DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_siteserver_TrackerAnalysis PRIMARY KEY CLUSTERED (TrackerAnalysisID)
)
go



CREATE TABLE siteserver_Users(
    UserName           nvarchar(255)    NOT NULL,
    CreatorUserName    nvarchar(255)    DEFAULT '' NOT NULL,
    SettingsXML        ntext            DEFAULT '' NOT NULL,
    CONSTRAINT PK_siteserver_Users PRIMARY KEY CLUSTERED (UserName)
)
go



CREATE INDEX IX_FK_bairong_AuxiliaryTableCollection ON bairong_AuxiliaryTableMetadata(AuxiliaryTableENName)
go
CREATE INDEX IX_FK_bairong_Vote ON bairong_VoteIPAddress(VoteID)
go
CREATE INDEX IX_FK_bairong_Vote ON bairong_VoteItem(VoteID)
go
CREATE INDEX IX_FK_siteserver_Node ON siteserver_MenuDisplay(PublishmentSystemID)
go
CREATE CLUSTERED INDEX IX_Taxis ON siteserver_Node(Taxis)
go
CREATE INDEX IX_PublishmentSystemID ON siteserver_Node(PublishmentSystemID)
go
CREATE INDEX IX_FK_siteserver_Node ON siteserver_SeoMeta(PublishmentSystemID)
go
CREATE INDEX IX_FK_siteserver_Node ON siteserver_Template(PublishmentSystemID)
go
CREATE INDEX IX_TemplateType ON siteserver_Template(TemplateType)
go
ALTER TABLE bairong_AuxiliaryTableMetadata ADD CONSTRAINT FK_bairong_AuxiliaryTableMetadata_AuxiliaryTableCollection 
    FOREIGN KEY (AuxiliaryTableENName)
    REFERENCES bairong_AuxiliaryTableCollection(TableENName) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE bairong_VoteIPAddress ADD CONSTRAINT FK_bairong_VoteIPAddress_Vote 
    FOREIGN KEY (VoteID)
    REFERENCES bairong_Vote(VoteID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE bairong_VoteItem ADD CONSTRAINT FK_bairong_VoteItem_Vote 
    FOREIGN KEY (VoteID)
    REFERENCES bairong_Vote(VoteID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_AdvertisementFloat ADD CONSTRAINT FK_siteserver_AdvertisementFloat_Advertisement 
    FOREIGN KEY (AdvertisementName, PublishmentSystemID)
    REFERENCES siteserver_Advertisement(AdvertisementName, PublishmentSystemID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_AdvertisementGroupsInTemplates ADD CONSTRAINT FK_siteserver_AdvertisementGroupsInTemplates_AdvertisementGroup 
    FOREIGN KEY (GroupName, PublishmentSystemID)
    REFERENCES siteserver_AdvertisementGroup(GroupName, PublishmentSystemID) ON DELETE CASCADE ON UPDATE CASCADE
go

ALTER TABLE siteserver_AdvertisementGroupsInTemplates ADD CONSTRAINT FK_siteserver_AdvertisementGroupsInTemplates_Template 
    FOREIGN KEY (TemplateID)
    REFERENCES siteserver_Template(TemplateID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_AdvertisementNewWindow ADD CONSTRAINT FK_siteserver_AdvertisementNewWindow_Advertisement 
    FOREIGN KEY (AdvertisementName, PublishmentSystemID)
    REFERENCES siteserver_Advertisement(AdvertisementName, PublishmentSystemID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_AdvertisementsInGroups ADD CONSTRAINT FK_siteserver_AdvertisementsInGroups_Advertisement 
    FOREIGN KEY (AdvertisementName, PublishmentSystemID)
    REFERENCES siteserver_Advertisement(AdvertisementName, PublishmentSystemID) ON DELETE CASCADE ON UPDATE CASCADE
go

ALTER TABLE siteserver_AdvertisementsInGroups ADD CONSTRAINT FK_siteserver_AdvertisementsInGroups_AdvertisementGroup 
    FOREIGN KEY (GroupName, PublishmentSystemID)
    REFERENCES siteserver_AdvertisementGroup(GroupName, PublishmentSystemID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_BackgroundNode ADD CONSTRAINT FK_siteserver_BackgroundNode_Node 
    FOREIGN KEY (NodeID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_ContentRemark ADD CONSTRAINT FK_siteserver_ContentRemark_Node 
    FOREIGN KEY (NodeID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_ForegroundNode ADD CONSTRAINT FK_siteserver_ForegroundNode_Node 
    FOREIGN KEY (NodeID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_GatherDatabaseRule ADD CONSTRAINT FK_siteserver_GatherDatabaseRule_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_GatherRule ADD CONSTRAINT FK_siteserver_GatherRule_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_MenuDisplay ADD CONSTRAINT FK_siteserver_MenuDisplay_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_PublishmentSystem ADD CONSTRAINT FK_siteserver_PublishmentSystem_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_SeoMeta ADD CONSTRAINT FK_siteserver_SeoMeta_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_SeoMetasInNodes ADD CONSTRAINT KF_siteserver_SeoMetasInNodes_SeoMeta 
    FOREIGN KEY (SeoMetaID)
    REFERENCES siteserver_SeoMeta(SeoMetaID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_System ADD CONSTRAINT FK_siteserver_System_Node 
    FOREIGN KEY (SystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_Template ADD CONSTRAINT FK_siteserver_Template_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_Tracker ADD CONSTRAINT FK_siteserver_Tracker_Node 
    FOREIGN KEY (PublishmentSystemID)
    REFERENCES siteserver_Node(NodeID) ON DELETE CASCADE ON UPDATE CASCADE
go


ALTER TABLE siteserver_TrackerAnalysis ADD CONSTRAINT FK_siteserver_TrackerAnalysis_Tracker 
    FOREIGN KEY (TrackerID)
    REFERENCES siteserver_Tracker(TrackerID) ON DELETE CASCADE ON UPDATE CASCADE
go