[Sommaire] [Imprimer]

   1  <?xml version="1.0" encoding="UTF-8"?>
   2  <!-- ***** BEGIN LICENSE BLOCK *****
   3     - This file is part of DotClear.
   4     - Copyright (c) 2004 Olivier Meunier and contributors. All rights
   5     - reserved.
   6     -
   7     - DotClear is free software; you can redistribute it and/or modify
   8     - it under the terms of the GNU General Public License as published by
   9     - the Free Software Foundation; either version 2 of the License, or
  10     - (at your option) any later version.
  11     -
  12     - DotClear is distributed in the hope that it will be useful,
  13     - but WITHOUT ANY WARRANTY; without even the implied warranty of
  14     - MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  15     - GNU General Public License for more details.
  16     -
  17     - You should have received a copy of the GNU General Public License
  18     - along with DotClear; if not, write to the Free Software
  19     - Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
  20     -
  21     - ***** END LICENSE BLOCK ***** -->
  22  <database>
  23    
  24    <!--
  25    Remove tables, FOR TESTING PURPOSE ONLY!
  26    
  27    <action>
  28    DROP TABLE IF EXISTS
  29    `{{PREFIX}}spamrule`,
  30    `{{PREFIX}}comment`,
  31    `{{PREFIX}}ping`,
  32    `{{PREFIX}}log`,
  33    `{{PREFIX}}meta`,
  34    `{{PREFIX}}post_media`,
  35    `{{PREFIX}}media`,
  36    `{{PREFIX}}post`,
  37    `{{PREFIX}}permissions`,
  38    `{{PREFIX}}user`,
  39    `{{PREFIX}}setting`,
  40    `{{PREFIX}}session`,
  41    `{{PREFIX}}link`,
  42    `{{PREFIX}}category`,
  43    `{{PREFIX}}blog`,
  44    `{{PREFIX}}version`
  45    </action>
  46    -->
  47    
  48    <!-- BLOG -->
  49    <test type="table" name="{{PREFIX}}blog" label="Table %s already exists." eq="neq" alert="1">
  50      <action>
  51        create table `{{PREFIX}}blog` (
  52        blog_id              varchar(32)          binary not null,
  53        blog_uid             varchar(32)          binary not null,
  54        blog_creadt          datetime             not null default '1970-01-01 00:00:00',
  55        blog_upddt           datetime             not null default '1970-01-01 00:00:00',
  56        blog_url             varchar(255)         binary not null,
  57        blog_name            varchar(255)         binary not null,
  58        blog_desc            text                 null,
  59        blog_status          int(1)               not null default 1,
  60        
  61        constraint {{PREFIX}}pk_blog primary key (blog_id)
  62        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
  63      </action>
  64    </test>
  65    
  66    <!-- CATEGORY -->
  67    <test type="table" name="{{PREFIX}}category" label="Table %s already exists." eq="neq" alert="1">
  68      <action>
  69        create table `{{PREFIX}}category` (
  70        cat_id               bigint(20)           not null,
  71        blog_id              varchar(32)          binary not null,
  72        cat_title            varchar(255)         binary not null,
  73        cat_url              varchar(255)         binary not null,
  74        cat_desc             text                 null,
  75        cat_position         int(11)              null default 0,
  76        
  77        constraint {{PREFIX}}pk_category primary key (cat_id),
  78        
  79        constraint {{PREFIX}}uk_cat_title unique (cat_title, blog_id),
  80        
  81        constraint {{PREFIX}}uk_cat_url unique (cat_url, blog_id)
  82        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
  83      </action>
  84    </test>
  85    
  86    <!-- LINK -->
  87    <test type="table" name="{{PREFIX}}link" label="Table %s already exists." eq="neq" alert="1">
  88      <action>
  89        create table `{{PREFIX}}link` (
  90        link_id              bigint(20)           not null,
  91        blog_id              varchar(32)          binary not null,
  92        link_href            varchar(255)         binary not null,
  93        link_title           varchar(255)         binary not null,
  94        link_desc            varchar(255)         binary null,
  95        link_lang            varchar(5)           binary null,
  96        link_xfn             varchar(255)         binary null,
  97        link_position        int(11)              not null default 0,
  98        
  99        constraint {{PREFIX}}pk_link primary key (link_id)
 100        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 101      </action>
 102    </test>
 103    
 104    <!-- SESSION -->
 105    <test type="table" name="{{PREFIX}}session" label="Table %s already exists." eq="neq" alert="1">
 106      <action>
 107        create table `{{PREFIX}}session` (
 108        ses_id               varchar(40)          binary not null,
 109        ses_time             int(14)              not null default 0,
 110        ses_start            int(14)              not null default 0,
 111        ses_value            text                 not null,
 112        
 113        constraint {{PREFIX}}pk_session primary key (ses_id)
 114        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 115      </action>
 116    </test>
 117    
 118    <!-- SETTING -->
 119    <test type="table" name="{{PREFIX}}setting" label="Table %s already exists." eq="neq" alert="1">
 120      <action>
 121        create table `{{PREFIX}}setting` (
 122        setting_id           varchar(255)         binary not null,
 123        blog_id              varchar(32)          binary null,
 124        setting_ns           varchar(32)          binary not null default 'system',
 125        setting_value        text                 null default null,
 126        setting_type         varchar(8)           binary not null default 'string',
 127        setting_label        text                 null,
 128        
 129        constraint {{PREFIX}}uk_setting unique (setting_id,blog_id)
 130        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 131      </action>
 132    </test>
 133    
 134    <!-- USER -->
 135    <test type="table" name="{{PREFIX}}user" label="Table %s already exists." eq="neq" alert="1">
 136      <action>
 137        create table `{{PREFIX}}user` (
 138        user_id              varchar(32)          binary not null,
 139        user_super           int(1)               null,
 140        user_status          int(1)               not null default 1,
 141        user_pwd             varchar(40)          binary not null,
 142        user_recover_key     varchar(32)          binary null default null,
 143        user_name            varchar(255)         binary null default null,
 144        user_firstname       varchar(255)         binary null default null,
 145        user_displayname     varchar(255)         binary null default null,
 146        user_email           varchar(255)         binary null default null,
 147        user_url             varchar(255)         binary null default null,
 148        user_desc            text                 null,
 149        user_default_blog    varchar(32)          binary default null,
 150        user_options         text                 null,
 151        user_lang            varchar(5)           binary null default null,
 152        user_tz              varchar(128)         binary not null default 'UTC',
 153        user_post_status     int(1)               not null default -2,
 154        user_creadt          datetime             not null default '1970-01-01 00:00:00',
 155        user_upddt           datetime             not null default '1970-01-01 00:00:00',
 156        
 157        constraint {{PREFIX}}pk_user primary key (user_id)
 158        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 159      </action>
 160    </test>
 161    
 162    <!-- PERMISSIONS -->
 163    <test type="table" name="{{PREFIX}}permissions" label="Table %s already exists." eq="neq" alert="1">
 164      <action>
 165        create table `{{PREFIX}}permissions` (
 166        user_id              varchar(32)          binary not null,
 167        blog_id              varchar(32)          binary not null,
 168        permissions          text                 null,
 169        
 170        constraint {{PREFIX}}pk_permissions primary key (user_id, blog_id)
 171        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 172      </action>
 173    </test>
 174    
 175    <!-- POST -->
 176    <test type="table" name="{{PREFIX}}post" label="Table %s already exists." eq="neq" alert="1">
 177      <action>
 178        create table `{{PREFIX}}post` (
 179        post_id              bigint(20)           not null,
 180        blog_id              varchar(32)          binary not null,
 181        user_id              varchar(32)          binary not null,
 182        cat_id               bigint(20)           null,
 183        post_dt              datetime             not null default '1970-01-01 00:00:00',
 184        post_tz              varchar(128)         binary not null default 'UTC',
 185        post_creadt          datetime             not null default '1970-01-01 00:00:00',
 186        post_upddt           datetime             not null default '1970-01-01 00:00:00',
 187        post_password        varchar(32)          binary null default null,
 188        post_type            varchar(32)          binary not null default 'post',
 189        post_format          varchar(32)          binary not null default 'xhtml',
 190        post_url             varchar(255)         binary not null,
 191        post_lang            varchar(5)           binary null default null,
 192        post_title           varchar(255)         binary null default null,
 193        post_excerpt         longtext             null default null,
 194        post_excerpt_xhtml   longtext             null default null,
 195        post_content         longtext             null default null,
 196        post_content_xhtml   longtext             not null,
 197        post_notes           longtext             null default null,
 198        post_words           longtext             null default null,
 199        post_meta            text                 null default null,
 200        post_status          int(1)               not null default 0,
 201        post_selected        int(1)               not null default 0,
 202        post_open_comment    int(1)               not null default 0,
 203        post_open_tb         int(1)               not null default 0,
 204        nb_comment           int(11)              not null default 0,
 205        nb_trackback         int(11)              not null default 0,
 206        
 207        constraint {{PREFIX}}pk_post primary key (post_id),
 208        
 209        constraint {{PREFIX}}uk_post_url unique (post_url, post_type, blog_id)
 210        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 211      </action>
 212    </test>
 213    
 214    <!-- META -->
 215    <test type="table" name="{{PREFIX}}meta" label="Table %s already exists." eq="neq" alert="1">
 216      <action>
 217        create table `{{PREFIX}}meta` (
 218        meta_id            varchar(255)           binary not null,
 219        meta_type          varchar(64)            binary not null,
 220        post_id            bigint(20)             not null,
 221        
 222        constraint {{PREFIX}}pk_meta primary key (meta_id, meta_type, post_id)
 223        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 224      </action>
 225    </test>
 226    
 227    <!-- MEDIA -->
 228    <test type="table" name="{{PREFIX}}media" label="Table %s already exists." eq="neq" alert="1">
 229      <action>
 230        create table `{{PREFIX}}media` (
 231        media_id             bigint(20)           not null,
 232        user_id              varchar(32)          binary not null,
 233        media_path           varchar(255)         binary not null,
 234        media_title          varchar(255)         binary not null,
 235        media_file           varchar(255)         binary not null,
 236        media_dir            varchar(255)         binary not null default '.',
 237        media_meta           text                 null default null,
 238        media_dt             datetime             not null default '1970-01-01 00:00:00',
 239        media_creadt         datetime             not null default '1970-01-01 00:00:00',
 240        media_upddt          datetime             not null default '1970-01-01 00:00:00',
 241        media_private        int(1)               not null default 0,
 242        
 243        constraint {{PREFIX}}pk_media primary key (media_id)
 244        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 245      </action>
 246    </test>
 247    
 248    <!-- POST_MEDIA -->
 249    <test type="table" name="{{PREFIX}}post_media" label="Table %s already exists." eq="neq" alert="1">
 250      <action>
 251        create table `{{PREFIX}}post_media` (
 252        media_id             bigint(20)          not null,
 253        post_id              bigint(20)          not null,
 254        
 255        constraint {{PREFIX}}pk_post_media primary key (media_id, post_id)
 256        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 257      </action>
 258    </test>
 259    
 260    <!-- LOG -->
 261    <test type="table" name="{{PREFIX}}log" label="Table %s already exists." eq="neq" alert="1">
 262      <action>
 263        create table `{{PREFIX}}log` (
 264        log_id               bigint(20)           not null,
 265        user_id              varchar(32)          binary null,
 266        log_table            varchar(255)         binary not null,
 267        log_dt               datetime             not null default '1970-01-01 00:00:00',
 268        log_ip               varchar(39)          binary not null,
 269        log_msg              varchar(255)         binary not null,
 270        
 271        constraint {{PREFIX}}pk_log primary key (log_id)
 272        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 273      </action>
 274    </test>
 275    
 276    <!-- VERSION -->
 277    <test type="table" name="{{PREFIX}}version" label="Table %s already exists." eq="neq" alert="1">
 278      <action>
 279        create table `{{PREFIX}}version` (
 280        module               varchar(64)         binary not null,
 281        version              varchar(32)         binary not null,
 282        
 283        constraint {{PREFIX}}pk_version primary key (module)
 284        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 285      </action>
 286    </test>
 287    
 288    <!-- PING -->
 289    <test type="table" name="{{PREFIX}}ping" label="Table %s already exists." eq="neq" alert="1">
 290      <action>
 291        create table `{{PREFIX}}ping` (
 292        post_id              bigint(20)           not null,
 293        ping_url             varchar(255)         binary not null,
 294        ping_dt              datetime             not null default '1970-01-01 00:00:00',
 295        
 296        constraint {{PREFIX}}pk_ping primary key (post_id,ping_url)
 297        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 298      </action>
 299    </test>
 300    
 301    <!-- COMMENT -->
 302    <test type="table" name="{{PREFIX}}comment" label="Table %s already exists." eq="neq" alert="1">
 303      <action>
 304        create table `{{PREFIX}}comment` (
 305        comment_id           bigint(20)           not null,
 306        post_id              bigint(20)           not null,
 307        comment_dt           datetime             not null default '1970-01-01 00:00:00',
 308        comment_tz           varchar(128)         binary not null default 'UTC',
 309        comment_upddt        datetime             not null default '1970-01-01 00:00:00',
 310        comment_author       varchar(255)         binary null default null,
 311        comment_email        varchar(255)         binary null default null,
 312        comment_site         varchar(255)         binary null default null,
 313        comment_content      longtext             null,
 314        comment_words        longtext             null default null,
 315        comment_ip           varchar(39)          binary null default null,
 316        comment_status       int(1)               null default 0,
 317        comment_spam_status  varchar(128)         binary null default 0,
 318        comment_spam_filter  varchar(32)          binary null default null,
 319        comment_trackback    int(1)               not null default 0,
 320        
 321        constraint {{PREFIX}}pk_comment primary key (comment_id)
 322        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 323      </action>
 324    </test>
 325    
 326    <!-- SPAMRULE -->
 327    <test type="table" name="{{PREFIX}}spamrule" label="Table %s already exists." eq="neq" alert="1">
 328      <action>
 329        create table `{{PREFIX}}spamrule` (
 330        rule_id              bigint(20)           not null,
 331        blog_id              varchar(32)          binary null,
 332        rule_type            varchar(16)          binary not null default 'word',
 333        rule_content         varchar(128)         binary not null,
 334        
 335        constraint {{PREFIX}}pk_spamrule primary key (rule_id)
 336        ) ENGINE=InnoDB CHARACTER SET utf8 COLLATE utf8_bin
 337      </action>
 338    </test>
 339    
 340    
 341    <!-- INDEXES
 342    ====================================================== -->
 343    <action>create index {{PREFIX}}idx_category_blog_id
 344    on {{PREFIX}}category (blog_id)</action>
 345    
 346    <action>create index {{PREFIX}}idx_link_blog_id
 347    on {{PREFIX}}link (blog_id)</action>
 348    
 349    <action>create index {{PREFIX}}idx_setting_blog_id
 350    on {{PREFIX}}setting (blog_id)</action>
 351    
 352    <action>create index {{PREFIX}}idx_user_user_default_blog
 353    on {{PREFIX}}user (user_default_blog)</action>
 354    
 355    <action>create index {{PREFIX}}idx_permissions_blog_id
 356    on {{PREFIX}}permissions (blog_id)</action>
 357    
 358    <action>create index {{PREFIX}}idx_post_cat_id
 359    on {{PREFIX}}post (cat_id)</action>
 360    
 361    <action>create index {{PREFIX}}idx_post_user_id
 362    on {{PREFIX}}post (user_id)</action>
 363    
 364    <action>create index {{PREFIX}}idx_post_blog_id
 365    on {{PREFIX}}post (blog_id)</action>
 366    
 367    <action>create index {{PREFIX}}idx_meta_post_id
 368    on {{PREFIX}}meta (post_id)</action>
 369    
 370    <action>create index {{PREFIX}}idx_media_user_id
 371    on {{PREFIX}}media (user_id)</action>
 372    
 373    <action>create index {{PREFIX}}idx_post_media_post_id
 374    on {{PREFIX}}post_media (post_id)</action>
 375    
 376    <action>create index {{PREFIX}}idx_log_user_id
 377    on {{PREFIX}}log (user_id)</action>
 378    
 379    <action>create index {{PREFIX}}idx_comment_post_id
 380    on {{PREFIX}}comment (post_id)</action>
 381    
 382    <action>create index {{PREFIX}}idx_spamrule_blog_id
 383    on {{PREFIX}}spamrule (blog_id)</action>
 384    
 385    <!-- Performances -->
 386    <action>create index {{PREFIX}}idx_post_post_dt
 387    on {{PREFIX}}post (post_dt)</action>
 388    
 389    <action>create index {{PREFIX}}idx_post_post_dt_post_id
 390    on {{PREFIX}}post (post_dt, post_id)</action>
 391    
 392    <action>create index {{PREFIX}}idx_blog_blog_upddt
 393    on {{PREFIX}}blog (blog_upddt)</action>
 394    
 395    <action>create index {{PREFIX}}idx_user_user_super
 396    on {{PREFIX}}user (user_super)</action>
 397    
 398    <action>create index {{PREFIX}}idx_blog_post_post_dt_post_id
 399    on {{PREFIX}}post (blog_id, post_dt, post_id)</action>
 400    
 401    <!-- FOREIGN KEYS
 402    ====================================================== -->
 403    <action>
 404    alter table `{{PREFIX}}category` add constraint {{PREFIX}}fk_category_blog
 405    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 406      on delete cascade
 407      on update cascade
 408    </action>
 409    
 410    <action>
 411    alter table `{{PREFIX}}link` add constraint {{PREFIX}}fk_link_blog
 412    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 413      on delete cascade
 414      on update cascade
 415    </action>
 416    
 417    <action>
 418    alter table `{{PREFIX}}setting` add constraint {{PREFIX}}fk_setting_blog
 419    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 420      on delete cascade
 421      on update cascade
 422    </action>
 423    
 424    <action>
 425    alter table `{{PREFIX}}user` add constraint {{PREFIX}}fk_user_default_blog
 426    foreign key (user_default_blog) references `{{PREFIX}}blog` (blog_id)
 427      on delete set null
 428      on update cascade
 429    </action>
 430    
 431    <action>
 432    alter table `{{PREFIX}}permissions` add constraint {{PREFIX}}fk_permission_blog
 433    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 434      on delete cascade
 435      on update cascade
 436    </action>
 437    
 438    <action>
 439    alter table `{{PREFIX}}permissions` add constraint {{PREFIX}}fk_permission_user
 440    foreign key (user_id) references `{{PREFIX}}user` (user_id)
 441      on delete cascade
 442      on update cascade
 443    </action>
 444    
 445    <action>
 446    alter table `{{PREFIX}}post` add constraint {{PREFIX}}fk_post_category
 447    foreign key (cat_id) references `{{PREFIX}}category` (cat_id)
 448      on delete set null
 449      on update cascade
 450    </action>
 451    
 452    <action>
 453    alter table `{{PREFIX}}post` add constraint {{PREFIX}}fk_post_user
 454    foreign key (user_id) references `{{PREFIX}}user` (user_id)
 455      on delete cascade
 456      on update cascade
 457    </action>
 458    
 459    <action>
 460    alter table `{{PREFIX}}post` add constraint {{PREFIX}}fk_post_blog
 461    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 462      on delete cascade
 463      on update cascade
 464    </action>
 465    
 466    <action>
 467    alter table `{{PREFIX}}meta` add constraint {{PREFIX}}fk_meta_post
 468    foreign key (post_id) references `{{PREFIX}}post` (post_id)
 469      on delete cascade
 470      on update cascade
 471    </action>
 472    
 473    <action>
 474    alter table `{{PREFIX}}media` add constraint {{PREFIX}}fk_media_user
 475    foreign key (user_id) references `{{PREFIX}}user` (user_id)
 476      on delete cascade
 477      on update cascade
 478    </action>
 479    
 480    <action>
 481    alter table `{{PREFIX}}post_media` add constraint {{PREFIX}}fk_media
 482    foreign key (media_id) references `{{PREFIX}}media` (media_id)
 483      on delete cascade
 484      on update cascade
 485    </action>
 486    
 487    <action>
 488    alter table `{{PREFIX}}post_media` add constraint {{PREFIX}}fk_media_post
 489    foreign key (post_id) references `{{PREFIX}}post` (post_id)
 490      on delete cascade
 491      on update cascade
 492    </action>
 493    
 494    <action>
 495    alter table `{{PREFIX}}ping` add constraint {{PREFIX}}fk_ping_post
 496    foreign key (post_id) references `{{PREFIX}}post` (post_id)
 497      on delete cascade
 498      on update cascade
 499    </action>
 500    
 501    <action>
 502    alter table `{{PREFIX}}comment` add constraint {{PREFIX}}fk_comment_post
 503    foreign key (post_id) references `{{PREFIX}}post` (post_id)
 504      on delete cascade
 505      on update cascade
 506    </action>
 507    
 508    <action>
 509    alter table `{{PREFIX}}spamrule` add constraint {{PREFIX}}fk_spamrule_blog
 510    foreign key (blog_id) references `{{PREFIX}}blog` (blog_id)
 511      on delete cascade
 512      on update cascade
 513    </action>
 514  </database>