/*
 * ER/Studio 8.0 SQL Code Generation
 * Company :      BaiRong Software
 * Project :      BaiRong Software Fundation Tables
 * Author :       BaiRong Software
 *
 * Date Created : Friday, October 09, 2009 07:57:09
 * Target DBMS : Microsoft SQL Server 2000
 */

CREATE TABLE bairong_Administrator(
    UserName               nvarchar(255)    NOT NULL,
    Password               nvarchar(255)    DEFAULT '' NOT NULL,
    PasswordFormat         varchar(50)      DEFAULT '' NOT NULL,
    PasswordSalt           nvarchar(128)    DEFAULT '' NOT NULL,
    CreationDate           datetime         DEFAULT getdate() NOT NULL,
    LastActivityDate       datetime         DEFAULT '' NOT NULL,
    CountOfLogin           int              DEFAULT 0 NOT NULL,
    CreatorUserName        nvarchar(255)    DEFAULT '' NOT NULL,
    IsChecked              varchar(18)      DEFAULT '' NOT NULL,
    IsLockedOut            varchar(18)      DEFAULT '' NOT NULL,
    PublishmentSystemID    int              DEFAULT 0 NOT NULL,
    DisplayName            nvarchar(255)    DEFAULT '' NOT NULL,
    Question               nvarchar(255)    DEFAULT '' NOT NULL,
    Answer                 nvarchar(255)    DEFAULT '' NOT NULL,
    Email                  nvarchar(255)    DEFAULT '' NOT NULL,
    Theme                  varchar(50)      DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_Administrator PRIMARY KEY NONCLUSTERED (UserName)
)
go



CREATE TABLE bairong_AdministratorsInRoles(
    RoleName    nvarchar(255)    NOT NULL,
    UserName    nvarchar(255)    NOT NULL,
    CONSTRAINT PK_bairong_AdministratorsInRoles PRIMARY KEY NONCLUSTERED (RoleName, UserName)
)
go



CREATE TABLE bairong_AuxiliaryTableCollection(
    TableENName                  varchar(50)    NOT NULL,
    TableCNName                  varchar(50)    DEFAULT '' NOT NULL,
    AttributeNum                 int            DEFAULT 0 NOT NULL,
    AuxiliaryTableType           varchar(50)    DEFAULT '' NOT NULL,
    IsCreatedInDB                varchar(18)    DEFAULT '' NOT NULL,
    IsChangedAfterCreatedInDB    varchar(18)    DEFAULT '' NOT NULL,
    Description                  ntext          DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_AuxiliaryTableCollection PRIMARY KEY CLUSTERED (TableENName)
)
go



CREATE TABLE bairong_AuxiliaryTableMetadata(
    TableMetadataID         int              IDENTITY(1,1),
    AuxiliaryTableENName    varchar(50)      NOT NULL,
    AttributeName           varchar(50)      DEFAULT '' NOT NULL,
    DataType                varchar(50)      DEFAULT '' NOT NULL,
    DataLength              int              DEFAULT 0 NOT NULL,
    CanBeNull               varchar(18)      DEFAULT '' NOT NULL,
    DBDefaultValue          nvarchar(255)    DEFAULT '' NOT NULL,
    Taxis                   int              DEFAULT 0 NOT NULL,
    IsSystem                varchar(18)      DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_AuxiliaryTableMetadata PRIMARY KEY CLUSTERED (TableMetadataID)
)
go



CREATE TABLE bairong_AuxiliaryTableStyle(
    TableStyleID       int              IDENTITY(1,1),
    RelatedIdentity    int              DEFAULT 0 NOT NULL,
    TableName          varchar(50)      DEFAULT '' NOT NULL,
    AttributeName      varchar(50)      DEFAULT '' NOT NULL,
    Taxis              int              DEFAULT 0 NOT NULL,
    DisplayName        nvarchar(255)    DEFAULT '' NOT NULL,
    HelpText           varchar(255)     DEFAULT '' NOT NULL,
    IsVisible          varchar(18)      DEFAULT '' NOT NULL,
    IsVisibleInList    varchar(18)      DEFAULT '' NOT NULL,
    InputType          varchar(50)      DEFAULT '' NOT NULL,
    IsRequired         varchar(18)      DEFAULT '' NOT NULL,
    DefaultValue       varchar(255)     DEFAULT '' NOT NULL,
    IsHorizontal       varchar(18)      DEFAULT '' NOT NULL,
    ExtendValues       ntext            DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_AuxiliaryTableStyle PRIMARY KEY NONCLUSTERED (TableStyleID)
)
go



CREATE TABLE bairong_AuxiliaryTableStyleItem(
    TableStyleItemID    int             IDENTITY(1,1),
    TableStyleID        int             NOT NULL,
    ItemTitle           varchar(255)    DEFAULT '' NOT NULL,
    ItemValue           varchar(255)    DEFAULT '' NOT NULL,
    IsSelected          varchar(18)     DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_AuxiliaryTableStyleItem PRIMARY KEY NONCLUSTERED (TableStyleItemID)
)
go



CREATE TABLE bairong_Cache(
    CacheKey      varchar(200)    NOT NULL,
    CacheValue    ntext           DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_Cache PRIMARY KEY NONCLUSTERED (CacheKey)
)
go



CREATE TABLE bairong_Count(
    CountID             int              IDENTITY(1,1),
    ApplicationName     varchar(50)      DEFAULT '' NOT NULL,
    RelatedTableName    nvarchar(255)    DEFAULT '' NOT NULL,
    RelatedIdentity     nvarchar(255)    DEFAULT '' NOT NULL,
    CountType           varchar(50)      DEFAULT '' NOT NULL,
    CountNum            int              DEFAULT 0 NOT NULL,
    CONSTRAINT PK_bairong_Count PRIMARY KEY CLUSTERED (CountID)
)
go



CREATE TABLE bairong_Digg(
    DiggID                 int    IDENTITY(1,1),
    PublishmentSystemID    int    DEFAULT 0 NOT NULL,
    RelatedIdentity        int    DEFAULT 0 NOT NULL,
    Good                   int    DEFAULT 0 NOT NULL,
    Bad                    int    DEFAULT 0 NOT NULL,
    CONSTRAINT PK_bairong_Digg PRIMARY KEY NONCLUSTERED (DiggID)
)
go



CREATE TABLE bairong_GeneralPermissionsInRoles(
    RoleName              nvarchar(255)    NOT NULL,
    GeneralPermissions    text             DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_GeneralPermissionsInRoles PRIMARY KEY CLUSTERED (RoleName)
)
go



CREATE TABLE bairong_IP2City(
    ID          int             IDENTITY(1,1),
    StartNum    float           DEFAULT 0 NOT NULL,
    EndNum      float           DEFAULT 0 NOT NULL,
    Province    nvarchar(50)    DEFAULT '' NOT NULL,
    City        nvarchar(50)    DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_IP2City PRIMARY KEY NONCLUSTERED (ID)
)
go



CREATE TABLE bairong_Log(
    LogID          int            IDENTITY(1,1),
    LogUserName    varchar(50)    DEFAULT '' NOT NULL,
    LogIP          varchar(50)    DEFAULT '' NOT NULL,
    OS             varchar(50)    DEFAULT '' NOT NULL,
    IsSuccessed    varchar(18)    DEFAULT '' NOT NULL,
    LoginDate      datetime       DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_bairong_Log PRIMARY KEY CLUSTERED (LogID)
)
go



CREATE TABLE bairong_Roles(
    RoleName           nvarchar(255)    NOT NULL,
    CreatorUserName    nvarchar(255)    DEFAULT '' NOT NULL,
    Description        nvarchar(255)    DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_Roles PRIMARY KEY NONCLUSTERED (RoleName)
)
go



CREATE TABLE bairong_TableMatch(
    TableMatchID               int             IDENTITY(1,1),
    ConnectionString           varchar(200)    DEFAULT '' NOT NULL,
    TableName                  varchar(200)    DEFAULT '' NOT NULL,
    ConnectionStringToMatch    varchar(200)    DEFAULT '' NOT NULL,
    TableNameToMatch           varchar(200)    DEFAULT '' NOT NULL,
    ColumnsMap                 ntext           DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_TableMatch PRIMARY KEY CLUSTERED (TableMatchID)
)
go



CREATE TABLE bairong_TrackBack(
    TrackBackID         int              IDENTITY(1,1),
    ApplicationName     varchar(50)      DEFAULT '' NOT NULL,
    RelatedTableName    nvarchar(255)    DEFAULT '' NOT NULL,
    RelatedIdentity     nvarchar(255)    DEFAULT '' NOT NULL,
    Title               nvarchar(255)    DEFAULT '' NOT NULL,
    Excerpt             ntext            DEFAULT '' NOT NULL,
    Url                 nvarchar(255)    DEFAULT '' NOT NULL,
    SiteName            nvarchar(255)    DEFAULT '' NOT NULL,
    AddDate             datetime         DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_bairong_TrackBack PRIMARY KEY CLUSTERED (TrackBackID)
)
go



CREATE TABLE bairong_UserConfig(
    CenterName      nvarchar(50)     DEFAULT '' NOT NULL,
    Copyright       nvarchar(255)    DEFAULT '' NOT NULL,
    DefaultTheme    varchar(50)      DEFAULT '' NOT NULL,
    SettingsXML     ntext            DEFAULT '' NOT NULL
)
go



CREATE TABLE bairong_UserGroups(
    GroupID            int             IDENTITY(1,1),
    GroupName          nvarchar(50)    DEFAULT '' NOT NULL,
    IsCredits          varchar(18)     DEFAULT '' NOT NULL,
    CreditsFrom        int             DEFAULT 0 NOT NULL,
    CreditsTo          int             DEFAULT 0 NOT NULL,
    Stars              int             DEFAULT 0 NOT NULL,
    Color              varchar(10)     DEFAULT '' NOT NULL,
    Rank               int             DEFAULT 0 NOT NULL,
    UserPermissions    ntext           DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_UserGroups PRIMARY KEY NONCLUSTERED (GroupID)
)
go



CREATE TABLE bairong_Users(
    UserID              nvarchar(255)    NOT NULL,
    UserName            nvarchar(255)    DEFAULT '' NOT NULL,
    Password            nvarchar(255)    DEFAULT '' NOT NULL,
    PasswordFormat      varchar(50)      DEFAULT '' NOT NULL,
    PasswordSalt        nvarchar(128)    DEFAULT '' NOT NULL,
    GroupID             int              DEFAULT 0 NOT NULL,
    Credits             int              DEFAULT 0 NOT NULL,
    CreationDate        datetime         DEFAULT getdate() NOT NULL,
    LastActivityDate    datetime         DEFAULT getdate() NOT NULL,
    IsChecked           varchar(18)      DEFAULT '' NOT NULL,
    IsLockedOut         varchar(18)      DEFAULT '' NOT NULL,
    DisplayName         nvarchar(255)    DEFAULT '' NOT NULL,
    Email               nvarchar(255)    DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_Users PRIMARY KEY NONCLUSTERED (UserID)
)
go



CREATE TABLE bairong_UserSettings(
    UserID                 nvarchar(255)    NOT NULL,
    PublishmentSystemID    int              NOT NULL,
    TypeID                 int              DEFAULT 0 NOT NULL,
    Theme                  varchar(50)      DEFAULT '' NOT NULL,
    IconUrl                varchar(200)     DEFAULT '' NOT NULL,
    ImageUrl               varchar(200)     DEFAULT '' NOT NULL,
    Signature              nvarchar(255)    DEFAULT '' NOT NULL,
    SettingsXML            ntext            DEFAULT '' NOT NULL,
    CONSTRAINT PK_bairong_UserSettings PRIMARY KEY NONCLUSTERED (UserID)
)
go



CREATE TABLE bairong_UserType(
    TypeID      int             IDENTITY(1,1),
    TypeName    nvarchar(50)    DEFAULT '' NOT NULL,
    GroupID     int             DEFAULT 0 NOT NULL,
    CONSTRAINT PK_bairong_UserType PRIMARY KEY NONCLUSTERED (TypeID)
)
go



CREATE TABLE bairong_Vote(
    VoteID                          int              IDENTITY(1,1),
    VoteName                        nvarchar(50)     DEFAULT '' NOT NULL,
    RelatedIdentity                 nvarchar(255)    DEFAULT '' NOT NULL,
    Title                           text             DEFAULT '' NOT NULL,
    VoteType                        varchar(50)      DEFAULT '' NOT NULL,
    VoteItemType                    varchar(50)      DEFAULT '' NOT NULL,
    MaxVoteItemNum                  int              DEFAULT 0 NOT NULL,
    VoteRestrictType                varchar(50)      DEFAULT '' NOT NULL,
    AuxiliaryTableForVoteComment    varchar(50)      DEFAULT '' NOT NULL,
    AddDate                         datetime         DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_bairong_Vote PRIMARY KEY CLUSTERED (VoteID)
)
go



CREATE TABLE bairong_VoteIPAddress(
    VoteIPAddressID    int              IDENTITY(1,1),
    VoteID             int              NOT NULL,
    IPAddress          varchar(50)      DEFAULT '' NOT NULL,
    AddUserID          nvarchar(255)    DEFAULT '' NOT NULL,
    AddDate            datetime         DEFAULT getdate() NOT NULL,
    CONSTRAINT PK_bairong_VoteIPAddress PRIMARY KEY NONCLUSTERED (VoteIPAddressID)
)
go



CREATE TABLE bairong_VoteItem(
    VoteItemID       int              IDENTITY(1,1),
    VoteID           int              NOT NULL,
    ItemTitle        nvarchar(255)    DEFAULT '' NOT NULL,
    ItemImageUrl     varchar(200)     DEFAULT '' NOT NULL,
    NavigationUrl    varchar(200)     DEFAULT '' NOT NULL,
    DisplayColor     varchar(50)      DEFAULT '' NOT NULL,
    VoteNum          int              DEFAULT 0 NOT NULL,
    CONSTRAINT PK_bairong_VoteItem PRIMARY KEY NONCLUSTERED (VoteItemID)
)
go



CREATE INDEX IX_FK_bairong_AuxiliaryTableCollection ON bairong_AuxiliaryTableMetadata(AuxiliaryTableENName)
go
CREATE CLUSTERED INDEX IX_FK_bairong_AuxiliaryTableStyle ON bairong_AuxiliaryTableStyleItem(TableStyleID)
go
CREATE CLUSTERED INDEX IX_FK_bairong_Vote ON bairong_VoteIPAddress(VoteID)
go
CREATE CLUSTERED INDEX IX_FK_bairong_Vote ON bairong_VoteItem(VoteID)
go
ALTER TABLE bairong_AdministratorsInRoles ADD CONSTRAINT FK_bairong_AdministratorsInRoles_Administrator 
    FOREIGN KEY (UserName)
    REFERENCES bairong_Administrator(UserName) ON DELETE CASCADE ON UPDATE CASCADE
go

ALTER TABLE bairong_AdministratorsInRoles ADD CONSTRAINT FK_bairong_AdministratorsInRoles_Roles 
    FOREIGN KEY (RoleName)
    REFERENCES bairong_Roles(RoleName) ON DELETE CASCADE ON UPDATE CASCADE
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_AuxiliaryTableStyleItem ADD CONSTRAINT FK_bairong_AuxiliaryTableStyleItem_AuxiliaryTableStyle 
    FOREIGN KEY (TableStyleID)
    REFERENCES bairong_AuxiliaryTableStyle(TableStyleID) 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


