dotProject 2.1 RC1 : /db/upgrade_102_to

[Sommaire] [Imprimer]
   1  # $Id: upgrade_102_to_20alpha.sql,v 1.2.4.2 2007/02/17 04:48:22 ajdonnison Exp $
   2  #
   3  # Upgrade dotProject DB Schema
   4  # From Version 1.0.2 to Current CVS Version
   5  #
   6  # NOTE: This will NOT upgrade older releases to release 1.0.2
   7  #       You must apply older upgrade script first
   8  #
   9  # !                  W A R N I N G                !
  10  # !BACKUP YOU DATABASE BEFORE APPLYING THIS SCRIPT!
  11  # !                  W A R N I N G                !
  12  #
  13  # 00000000
  14  # add task_departments and contacts to projects table
  15  ALTER TABLE `projects` ADD `project_departments` CHAR( 100 ) ;
  16  ALTER TABLE `projects` ADD `project_contacts` CHAR( 100 ) ;
  17  ALTER TABLE `projects` ADD `project_priority` tinyint(4) default '0';
  18  ALTER TABLE `projects` ADD `project_type` SMALLINT DEFAULT '0' NOT NULL;
  19  
  20  #
  21  #Add permissions selection criteria for each module.  
  22  #
  23  ALTER TABLE `modules` ADD `permissions_item_table` CHAR( 100 ) ;
  24  ALTER TABLE `modules` ADD `permissions_item_field` CHAR( 100 ) ;
  25  ALTER TABLE `modules` ADD `permissions_item_label` CHAR( 100 ) ;
  26  UPDATE modules SET permissions_item_table='files', permissions_item_field='file_id', permissions_item_label='file_name' WHERE mod_directory='files';
  27  UPDATE modules SET permissions_item_table='users', permissions_item_field='user_id', permissions_item_label='user_username' WHERE mod_directory='users';
  28  UPDATE modules SET permissions_item_table='projects', permissions_item_field='project_id', permissions_item_label='project_name' WHERE mod_directory='projects';
  29  UPDATE modules SET permissions_item_table='tasks', permissions_item_field='task_id', permissions_item_label='task_name' WHERE mod_directory='tasks';
  30  UPDATE modules SET permissions_item_table='companies', permissions_item_field='company_id', permissions_item_label='company_name' WHERE mod_directory='companies';
  31  UPDATE modules SET permissions_item_table='forums', permissions_item_field='forum_id', permissions_item_label='forum_name' WHERE mod_directory='forums';
  32  
  33  #
  34  #add percentage resource allocation
  35  #
  36  ALTER TABLE `user_tasks` ADD COLUMN perc_assignment int(11) NOT NULL default '100';
  37  
  38  ALTER TABLE `users` ADD `user_contact` int(11) NOT NULL default '0';
  39  ALTER TABLE `contacts` ADD `contact_fax` varchar(30) NOT NULL default '0';
  40  ALTER TABLE `contacts` ADD `contact_aol` varchar(30) NOT NULL default '0';
  41  
  42  ALTER TABLE `tasks` ADD `task_type` SMALLINT DEFAULT '0' NOT NULL ;
  43  
  44  ALTER TABLE `files` ADD `file_category` int(11) NOT NULL default '0';
  45  INSERT INTO `sysvals` VALUES (null, 1, 'FileType', '0|Unknown\n1|Document\n2|Application');
  46  
  47  # Just some TaskTypes examples
  48  INSERT INTO `sysvals` VALUES (null, 1, 'TaskType', '0|Unknown\n1|Administrative\n2|Operative');
  49  INSERT INTO `sysvals` VALUES (null, 1, 'ProjectType', '0|Unknown\n1|Administrative\n2|Operative');
  50  INSERT INTO `syskeys` VALUES (2, 'CustomField', 'Serialized array in the following format:\r\n<KEY>|<SERIALIZED ARRAY>\r\n\r\nSerialized Array:\r\n[type] => text | checkbox | select | textarea | label\r\n[name] => <Field\'s name>\r\n[options] => <html capture options>\r\n[selects] => <options for select and checkbox>', 0, '\n', '|');
  51  INSERT INTO `syskeys` VALUES("3", "ColorSelection", "Hex color values for type=>color association.", "0", "\n", "|");
  52  INSERT INTO `sysvals` (`sysval_key_id`,`sysval_title`,`sysval_value`) VALUES("3", "ProjectColors", "Web|FFE0AE\nEngineering|AEFFB2\nHelpDesk|FFFCAE\nSystem Administration|FFAEAE");
  53  
  54  CREATE TABLE `task_contacts` (
  55    `task_id` INT(10) NOT NULL,
  56    `contact_id` INT(10) NOT NULL
  57  ) TYPE=MyISAM;
  58  
  59  CREATE TABLE `task_departments` (
  60    `task_id` INT(10) NOT NULL,
  61    `department_id` INT(10) NOT NULL
  62  ) TYPE=MyISAM;
  63  
  64  CREATE TABLE `project_contacts` (
  65    `project_id` INT(10) NOT NULL,
  66    `contact_id` INT(10) NOT NULL
  67  ) TYPE=MyISAM;
  68  
  69  CREATE TABLE `project_departments` (
  70    `project_id` INT(10) NOT NULL,
  71    `department_id` INT(10) NOT NULL
  72  ) TYPE=MyISAM;
  73  
  74  # 20040727
  75  # add user specific task priority
  76  #
  77  ALTER TABLE `user_tasks` ADD `user_task_priority` tinyint(4) default '0';
  78  
  79  # 20040728
  80  # converted taskstatus to sysvals
  81  #
  82  INSERT INTO `sysvals` VALUES (null, 1, 'TaskStatus', '0|Active\n-1|Inactive');
  83  
  84  # 20040808
  85  # do not show events on non-working days
  86  #
  87  ALTER TABLE `events` ADD `events_cwd` tinyint(3) default '0';
  88  
  89  # 20040815
  90  # increase various field lengths
  91  #
  92  ALTER TABLE `contacts` CHANGE `contact_address1` `contact_address1` varchar(60) default null ;
  93  ALTER TABLE `contacts` CHANGE `contact_address2` `contact_address2` varchar(60) default null ;
  94  ALTER TABLE `users` CHANGE `user_username` `user_username` varchar(255) default null ;
  95  
  96  # 20040819
  97  # invent task assign maximum
  98  #
  99  ALTER TABLE `user_preferences` CHANGE `pref_name` `pref_name` VARCHAR( 72 ) NOT NULL;
 100  INSERT INTO `user_preferences` VALUES("0", "TASKASSIGNMAX", "100");
 101  
 102  #20040820
 103  # added ProjectStatus of Template
 104  #
 105  UPDATE `sysvals` SET `sysval_value` = '0|Not Defined\n1|Proposed\n2|In Planning\n3|In Progress\n4|On Hold\n5|Complete\n6|Template' WHERE `sysval_title` = 'ProjectStatus' LIMIT 1 ;
 106  
 107  #20040823
 108  # changed over to dynamic project end date
 109  #
 110  ALTER TABLE `projects` DROP `project_actual_end_date`;
 111  
 112  #20040823
 113  #Added user access log
 114  CREATE TABLE `user_access_log` (
 115  `user_access_log_id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
 116  `user_id` INT( 10 ) UNSIGNED NOT NULL ,
 117  `date_time_in` DATETIME DEFAULT '0000-00-00 00:00:00',
 118  `date_time_out` DATETIME DEFAULT '0000-00-00 00:00:00',
 119  `date_time_last_action` DATETIME DEFAULT '0000-00-00 00:00:00',
 120  PRIMARY KEY ( `user_access_log_id` )
 121  );
 122  
 123  #20040823
 124  # Task Priority, Project Priority are now sysvals
 125  #
 126  INSERT INTO `sysvals` ( `sysval_key_id` , `sysval_title` , `sysval_value` )
 127  VALUES ('1', 'TaskPriority', '-1|low\n0|normal\n1|high');
 128  INSERT INTO `sysvals` ( `sysval_key_id` , `sysval_title` , `sysval_value` )
 129  VALUES ('1', 'ProjectPriority', '-1|low\n0|normal\n1|high');
 130  INSERT INTO `sysvals` ( `sysval_key_id` , `sysval_title` , `sysval_value` )
 131  VALUES ('1', 'ProjectPriorityColor', '-1|#E5F7FF\n0|\n1|#FFDCB3');
 132  
 133  #20040823
 134  # Task Log is now sysvals, some additional fields
 135  #
 136  INSERT INTO `sysvals` ( `sysval_key_id` , `sysval_title` , `sysval_value` )
 137  VALUES ('1', 'TaskLogReference', '0|Not Defined\n1|Email\n2|Helpdesk\n3|Phone Call\n4|Fax');
 138  INSERT INTO `sysvals` ( `sysval_key_id` , `sysval_title` , `sysval_value` )
 139  VALUES ('1', 'TaskLogReferenceImage', '0| 1|./images/obj/email.gif 2|./modules/helpdesk/images/helpdesk.png 3|./images/obj/phone.gif 4|./images/icons/stock_print-16.png');
 140  
 141  ALTER TABLE `task_log` ADD `task_log_problem` TINYINT( 1 ) DEFAULT '0';
 142  ALTER TABLE `task_log` ADD `task_log_reference` TINYINT( 4 ) DEFAULT '0';
 143  ALTER TABLE `task_log` ADD `task_log_related_url` VARCHAR( 255 ) DEFAULT NULL;
 144  
 145  #20040910
 146  # Pinned tasks
 147  
 148  CREATE TABLE `user_task_pin` (
 149  `user_id` int(11) NOT NULL default '0',
 150  `task_id` int(10) NOT NULL default '0',
 151  `task_pinned` tinyint(2) NOT NULL default '1',
 152  PRIMARY KEY (`user_id`,`task_id`)
 153  ) TYPE=MyISAM;
 154  
 155  # 20041022
 156  # Permissions, files, resources
 157  #
 158  # Table structure for table `gacl_acl`
 159  #
 160  # Creation: Jul 22, 2004 at 01:00 PM
 161  # Last update: Jul 28, 2004 at 02:15 PM
 162  # Last check: Jul 22, 2004 at 01:00 PM
 163  #
 164  
 165  CREATE TABLE `gacl_acl` (
 166    `id` int(11) NOT NULL default '0',
 167    `section_value` varchar(230) NOT NULL default 'system',
 168    `allow` int(11) NOT NULL default '0',
 169    `enabled` int(11) NOT NULL default '0',
 170    `return_value` longtext,
 171    `note` longtext,
 172    `updated_date` int(11) NOT NULL default '0',
 173    PRIMARY KEY  (`id`),
 174    KEY `gacl_enabled_acl` (`enabled`),
 175    KEY `gacl_section_value_acl` (`section_value`),
 176    KEY `gacl_updated_date_acl` (`updated_date`)
 177  ) TYPE=MyISAM;
 178  # --------------------------------------------------------
 179  
 180  #
 181  # Table structure for table `gacl_acl_sections`
 182  #
 183  # Creation: Jul 22, 2004 at 01:00 PM
 184  # Last update: Jul 22, 2004 at 01:04 PM
 185  # Last check: Jul 22, 2004 at 01:00 PM
 186  #
 187  
 188  CREATE TABLE `gacl_acl_sections` (
 189    `id` int(11) NOT NULL default '0',
 190    `value` varchar(230) NOT NULL default '',
 191    `order_value` int(11) NOT NULL default '0',
 192    `name` varchar(230) NOT NULL default '',
 193    `hidden` int(11) NOT NULL default '0',
 194    PRIMARY KEY  (`id`),
 195    UNIQUE KEY `gacl_value_acl_sections` (`value`),
 196    KEY `gacl_hidden_acl_sections` (`hidden`)
 197  ) TYPE=MyISAM;
 198  # --------------------------------------------------------
 199  
 200  #
 201  # Table structure for table `gacl_aco`
 202  #
 203  # Creation: Jul 22, 2004 at 01:00 PM
 204  # Last update: Jul 28, 2004 at 11:23 AM
 205  # Last check: Jul 22, 2004 at 01:00 PM
 206  #
 207  
 208  CREATE TABLE `gacl_aco` (
 209    `id` int(11) NOT NULL default '0',
 210    `section_value` varchar(240) NOT NULL default '0',
 211    `value` varchar(240) NOT NULL default '',
 212    `order_value` int(11) NOT NULL default '0',
 213    `name` varchar(255) NOT NULL default '',
 214    `hidden` int(11) NOT NULL default '0',
 215    PRIMARY KEY  (`id`),
 216    UNIQUE KEY `gacl_section_value_value_aco` (`section_value`,`value`),
 217    KEY `gacl_hidden_aco` (`hidden`)
 218  ) TYPE=MyISAM;
 219  # --------------------------------------------------------
 220  
 221  #
 222  # Table structure for table `gacl_aco_map`
 223  #
 224  # Creation: Jul 22, 2004 at 01:00 PM
 225  # Last update: Jul 28, 2004 at 02:15 PM
 226  #
 227  
 228  CREATE TABLE `gacl_aco_map` (
 229    `acl_id` int(11) NOT NULL default '0',
 230    `section_value` varchar(230) NOT NULL default '0',
 231    `value` varchar(230) NOT NULL default '',
 232    PRIMARY KEY  (`acl_id`,`section_value`,`value`)
 233  ) TYPE=MyISAM;
 234  # --------------------------------------------------------
 235  
 236  #
 237  # Table structure for table `gacl_aco_sections`
 238  #
 239  # Creation: Jul 22, 2004 at 01:00 PM
 240  # Last update: Jul 23, 2004 at 08:14 AM
 241  # Last check: Jul 22, 2004 at 01:00 PM
 242  #
 243  
 244  CREATE TABLE `gacl_aco_sections` (
 245    `id` int(11) NOT NULL default '0',
 246    `value` varchar(230) NOT NULL default '',
 247    `order_value` int(11) NOT NULL default '0',
 248    `name` varchar(230) NOT NULL default '',
 249    `hidden` int(11) NOT NULL default '0',
 250    PRIMARY KEY  (`id`),
 251    UNIQUE KEY `gacl_value_aco_sections` (`value`),
 252    KEY `gacl_hidden_aco_sections` (`hidden`)
 253  ) TYPE=MyISAM;
 254  # --------------------------------------------------------
 255  
 256  #
 257  # Table structure for table `gacl_aro`
 258  #
 259  # Creation: Jul 22, 2004 at 01:00 PM
 260  # Last update: Jul 29, 2004 at 11:38 AM
 261  # Last check: Jul 22, 2004 at 01:00 PM
 262  #
 263  
 264  CREATE TABLE `gacl_aro` (
 265    `id` int(11) NOT NULL default '0',
 266    `section_value` varchar(240) NOT NULL default '0',
 267    `value` varchar(240) NOT NULL default '',
 268    `order_value` int(11) NOT NULL default '0',
 269    `name` varchar(255) NOT NULL default '',
 270    `hidden` int(11) NOT NULL default '0',
 271    PRIMARY KEY  (`id`),
 272    UNIQUE KEY `gacl_section_value_value_aro` (`section_value`,`value`),
 273    KEY `gacl_hidden_aro` (`hidden`)
 274  ) TYPE=MyISAM;
 275  # --------------------------------------------------------
 276  
 277  #
 278  # Table structure for table `gacl_aro_groups`
 279  #
 280  # Creation: Jul 22, 2004 at 01:00 PM
 281  # Last update: Jul 28, 2004 at 12:12 PM
 282  # Last check: Jul 22, 2004 at 01:00 PM
 283  #
 284  
 285  CREATE TABLE `gacl_aro_groups` (
 286    `id` int(11) NOT NULL default '0',
 287    `parent_id` int(11) NOT NULL default '0',
 288    `lft` int(11) NOT NULL default '0',
 289    `rgt` int(11) NOT NULL default '0',
 290    `name` varchar(255) NOT NULL default '',
 291    `value` varchar(255) NOT NULL default '',
 292    PRIMARY KEY  (`id`,`value`),
 293    KEY `gacl_parent_id_aro_groups` (`parent_id`),
 294    KEY `gacl_value_aro_groups` (`value`),
 295    KEY `gacl_lft_rgt_aro_groups` (`lft`,`rgt`)
 296  ) TYPE=MyISAM;
 297  # --------------------------------------------------------
 298  
 299  #
 300  # Table structure for table `gacl_aro_groups_map`
 301  #
 302  # Creation: Jul 22, 2004 at 01:00 PM
 303  # Last update: Jul 28, 2004 at 12:26 PM
 304  #
 305  
 306  CREATE TABLE `gacl_aro_groups_map` (
 307    `acl_id` int(11) NOT NULL default '0',
 308    `group_id` int(11) NOT NULL default '0',
 309    PRIMARY KEY  (`acl_id`,`group_id`)
 310  ) TYPE=MyISAM;
 311  # --------------------------------------------------------
 312  
 313  #
 314  # Table structure for table `gacl_aro_map`
 315  #
 316  # Creation: Jul 22, 2004 at 01:00 PM
 317  # Last update: Jul 29, 2004 at 11:33 AM
 318  #
 319  
 320  CREATE TABLE `gacl_aro_map` (
 321    `acl_id` int(11) NOT NULL default '0',
 322    `section_value` varchar(230) NOT NULL default '0',
 323    `value` varchar(230) NOT NULL default '',
 324    PRIMARY KEY  (`acl_id`,`section_value`,`value`)
 325  ) TYPE=MyISAM;
 326  # --------------------------------------------------------
 327  
 328  #
 329  # Table structure for table `gacl_aro_sections`
 330  #
 331  # Creation: Jul 22, 2004 at 01:00 PM
 332  # Last update: Jul 22, 2004 at 03:04 PM
 333  # Last check: Jul 22, 2004 at 01:00 PM
 334  #
 335  
 336  CREATE TABLE `gacl_aro_sections` (
 337    `id` int(11) NOT NULL default '0',
 338    `value` varchar(230) NOT NULL default '',
 339    `order_value` int(11) NOT NULL default '0',
 340    `name` varchar(230) NOT NULL default '',
 341    `hidden` int(11) NOT NULL default '0',
 342    PRIMARY KEY  (`id`),
 343    UNIQUE KEY `gacl_value_aro_sections` (`value`),
 344    KEY `gacl_hidden_aro_sections` (`hidden`)
 345  ) TYPE=MyISAM;
 346  # --------------------------------------------------------
 347  
 348  #
 349  # Table structure for table `gacl_axo`
 350  #
 351  # Creation: Jul 22, 2004 at 01:00 PM
 352  # Last update: Jul 26, 2004 at 06:23 PM
 353  # Last check: Jul 22, 2004 at 01:00 PM
 354  #
 355  
 356  CREATE TABLE `gacl_axo` (
 357    `id` int(11) NOT NULL default '0',
 358    `section_value` varchar(240) NOT NULL default '0',
 359    `value` varchar(240) NOT NULL default '',
 360    `order_value` int(11) NOT NULL default '0',
 361    `name` varchar(255) NOT NULL default '',
 362    `hidden` int(11) NOT NULL default '0',
 363    PRIMARY KEY  (`id`),
 364    UNIQUE KEY `gacl_section_value_value_axo` (`section_value`,`value`),
 365    KEY `gacl_hidden_axo` (`hidden`)
 366  ) TYPE=MyISAM;
 367  # --------------------------------------------------------
 368  
 369  #
 370  # Table structure for table `gacl_axo_groups`
 371  #
 372  # Creation: Jul 22, 2004 at 01:00 PM
 373  # Last update: Jul 26, 2004 at 11:00 AM
 374  # Last check: Jul 22, 2004 at 01:00 PM
 375  #
 376  
 377  CREATE TABLE `gacl_axo_groups` (
 378    `id` int(11) NOT NULL default '0',
 379    `parent_id` int(11) NOT NULL default '0',
 380    `lft` int(11) NOT NULL default '0',
 381    `rgt` int(11) NOT NULL default '0',
 382    `name` varchar(255) NOT NULL default '',
 383    `value` varchar(255) NOT NULL default '',
 384    PRIMARY KEY  (`id`,`value`),
 385    KEY `gacl_parent_id_axo_groups` (`parent_id`),
 386    KEY `gacl_value_axo_groups` (`value`),
 387    KEY `gacl_lft_rgt_axo_groups` (`lft`,`rgt`)
 388  ) TYPE=MyISAM;
 389  # --------------------------------------------------------
 390  
 391  #
 392  # Table structure for table `gacl_axo_groups_map`
 393  #
 394  # Creation: Jul 22, 2004 at 01:00 PM
 395  # Last update: Jul 28, 2004 at 11:24 AM
 396  #
 397  
 398  CREATE TABLE `gacl_axo_groups_map` (
 399    `acl_id` int(11) NOT NULL default '0',
 400    `group_id` int(11) NOT NULL default '0',
 401    PRIMARY KEY  (`acl_id`,`group_id`)
 402  ) TYPE=MyISAM;
 403  # --------------------------------------------------------
 404  
 405  #
 406  # Table structure for table `gacl_axo_map`
 407  #
 408  # Creation: Jul 22, 2004 at 01:00 PM
 409  # Last update: Jul 28, 2004 at 02:15 PM
 410  #
 411  
 412  CREATE TABLE `gacl_axo_map` (
 413    `acl_id` int(11) NOT NULL default '0',
 414    `section_value` varchar(230) NOT NULL default '0',
 415    `value` varchar(230) NOT NULL default '',
 416    PRIMARY KEY  (`acl_id`,`section_value`,`value`)
 417  ) TYPE=MyISAM;
 418  # --------------------------------------------------------
 419  
 420  #
 421  # Table structure for table `gacl_axo_sections`
 422  #
 423  # Creation: Jul 22, 2004 at 01:00 PM
 424  # Last update: Jul 23, 2004 at 03:50 PM
 425  # Last check: Jul 22, 2004 at 01:00 PM
 426  #
 427  
 428  CREATE TABLE `gacl_axo_sections` (
 429    `id` int(11) NOT NULL default '0',
 430    `value` varchar(230) NOT NULL default '',
 431    `order_value` int(11) NOT NULL default '0',
 432    `name` varchar(230) NOT NULL default '',
 433    `hidden` int(11) NOT NULL default '0',
 434    PRIMARY KEY  (`id`),
 435    UNIQUE KEY `gacl_value_axo_sections` (`value`),
 436    KEY `gacl_hidden_axo_sections` (`hidden`)
 437  ) TYPE=MyISAM;
 438  # --------------------------------------------------------
 439  
 440  #
 441  # Table structure for table `gacl_groups_aro_map`
 442  #
 443  # Creation: Jul 22, 2004 at 01:00 PM
 444  # Last update: Jul 29, 2004 at 11:38 AM
 445  #
 446  
 447  CREATE TABLE `gacl_groups_aro_map` (
 448    `group_id` int(11) NOT NULL default '0',
 449    `aro_id` int(11) NOT NULL default '0',
 450    PRIMARY KEY  (`group_id`,`aro_id`)
 451  ) TYPE=MyISAM;
 452  # --------------------------------------------------------
 453  
 454  #
 455  # Table structure for table `gacl_groups_axo_map`
 456  #
 457  # Creation: Jul 22, 2004 at 01:00 PM
 458  # Last update: Jul 26, 2004 at 11:01 AM
 459  #
 460  
 461  CREATE TABLE `gacl_groups_axo_map` (
 462    `group_id` int(11) NOT NULL default '0',
 463    `axo_id` int(11) NOT NULL default '0',
 464    PRIMARY KEY  (`group_id`,`axo_id`)
 465  ) TYPE=MyISAM;
 466  # --------------------------------------------------------
 467  
 468  #
 469  # Table structure for table `gacl_phpgacl`
 470  #
 471  # Creation: Jul 22, 2004 at 01:00 PM
 472  # Last update: Jul 22, 2004 at 01:03 PM
 473  #
 474  
 475  CREATE TABLE `gacl_phpgacl` (
 476    `name` varchar(230) NOT NULL default '',
 477    `value` varchar(230) NOT NULL default '',
 478    PRIMARY KEY  (`name`)
 479  ) TYPE=MyISAM;
 480  
 481  
 482  INSERT INTO `gacl_phpgacl` (name, value) VALUES ('version', '3.3.2');
 483  INSERT INTO `gacl_phpgacl` (name, value) VALUES ('schema_version', '2.1');
 484  
 485  INSERT INTO `gacl_acl_sections` (id, value, order_value, name) VALUES (1, 'system', 1, 'System');
 486  INSERT INTO `gacl_acl_sections` (id, value, order_value, name) VALUES (2, 'user', 2, 'User');
 487  
 488  #
 489  # Indexes to speed up collation of data
 490  #
 491  ALTER TABLE `companies` ADD INDEX (`company_owner`);
 492  ALTER TABLE `events` ADD INDEX (`event_owner`);
 493  ALTER TABLE `events` ADD INDEX (`event_project`);
 494  ALTER TABLE `projects` ADD INDEX (`project_company`);
 495  ALTER TABLE `tasks` ADD INDEX (`task_start_date`);
 496  ALTER TABLE `tasks` ADD INDEX (`task_end_date`);
 497  
 498  # Changes to support assignment of events to users
 499  ALTER TABLE `events` ADD `event_notify` TINYINT NOT NULL default '0';
 500  
 501  CREATE TABLE `user_events` (
 502    `user_id` int(11) NOT NULL default '0',
 503    `event_id` int(11) NOT NULL default '0',
 504    KEY `uek1` (`user_id`, `event_id`),
 505    KEY `uek2` (`event_id`, `user_id`)
 506  ) TYPE=MyISAM;
 507  
 508  
 509  # Changes to handle file checkin/checkout support
 510  ALTER TABLE `files`
 511    ADD `file_checkout` VARCHAR(255) NOT NULL DEFAULT '',
 512    ADD `file_co_reason` TEXT,
 513    ADD `file_version_id` INT NOT NULL DEFAULT 0,
 514    ADD INDEX (`file_version_id`);
 515  
 516  # Move any old files into the new format
 517  UPDATE `files` SET `file_version_id` = `file_id` WHERE `file_version_id` = 0;
 518  
 519  # 20041027 cyberhorse
 520  # done to fix double enries in sysvals table
 521  # won't be possible until values are manually pruned first ...
 522  ALTER TABLE `sysvals` ADD UNIQUE (
 523  `sysval_title`
 524  );
 525  
 526  ALTER TABLE `syskeys` ADD UNIQUE (
 527  `syskey_name`
 528  );
 529  
 530  # 20041103
 531  # fixed naming conevntion for the following
 532  # do not show events on non-working days
 533  # see 20040808
 534  ALTER TABLE `events` DROP `events_cwd`;
 535  ALTER TABLE `events` ADD `event_cwd` tinyint(3) default '0';
 536  
 537  # 20041110
 538  # Fix for stripping of decimals in budget figures
 539  #
 540  ALTER TABLE `projects` CHANGE `project_target_budget` `project_target_budget` DECIMAL(10,2) default '0.00';
 541  ALTER TABLE `projects` CHANGE `project_actual_budget` `project_actual_budget` DECIMAL(10,2) default '0.00';
 542  ALTER TABLE `tasks` CHANGE `task_target_budget` `task_target_budget` DECIMAL(10,2) default '0.00';
 543  
 544  # 20041204
 545  # Added new fields for contacts
 546  #
 547  ALTER TABLE `contacts` ADD `contact_job` VARCHAR( 255 ) NOT NULL ,
 548  ADD `contact_jabber` VARCHAR( 255 ) NOT NULL ,
 549  ADD `contact_msn` VARCHAR( 255 ) NOT NULL ,
 550  ADD `contact_yahoo` VARCHAR( 255 ) NOT NULL;
 551  
 552  # 20041209
 553  # table used for billing.
 554  CREATE TABLE `billingcode` (
 555    `billingcode_id` bigint(20) NOT NULL auto_increment,
 556    `billingcode_name` varchar(25) NOT NULL default '',
 557    `billingcode_value` float NOT NULL default '0',
 558    `billingcode_desc` varchar(255) NOT NULL default '',
 559    `billingcode_status` int(1) NOT NULL default '0',
 560    `company_id` bigint(20) NOT NULL default '0',
 561    PRIMARY KEY  (`billingcode_id`)
 562  ) TYPE=MyISAM;
 563  
 564  # 20050125
 565  # Session handling table.
 566  CREATE TABLE `sessions` (
 567      `session_id` varchar(40) NOT NULL default '',
 568      `session_data` LONGBLOB,
 569      `session_updated` TIMESTAMP,
 570      `session_created` DATETIME NOT NULL default '0000-00-00 00:00:00',
 571      PRIMARY KEY (`session_id`),
 572      KEY (`session_updated`),
 573      KEY (`session_created`)
 574  ) TYPE=MyISAM;
 575  
 576  # 20050216
 577  # Added logging the IP of a user
 578  ALTER TABLE `user_access_log` ADD `user_ip` VARCHAR( 15 ) NOT NULL AFTER `user_id` ;
 579  
 580  # 20050216
 581  # Added URL for contacts
 582  ALTER TABLE `contacts` ADD `contact_url` VARCHAR( 255 ) NOT NULL AFTER `contact_icq` ;
 583  
 584  # 20050222
 585  # moved many config variables from config-php to a new table
 586  CREATE TABLE `config` (
 587    `config_id` int(11) NOT NULL auto_increment,
 588    `config_name` varchar(255) NOT NULL default '',
 589    `config_value` varchar(255) NOT NULL default '',
 590    `config_group` varchar(255) NOT NULL default '',
 591    `config_type` varchar(255) NOT NULL default '',
 592    PRIMARY KEY  (`config_id`),
 593    UNIQUE KEY `config_name` (`config_name`)
 594  ) TYPE=MyISAM AUTO_INCREMENT=47 ;
 595  
 596  #
 597  # Dumping data for table `config`
 598  #
 599  
 600  INSERT INTO `config` VALUES ('', 'check_legacy_password', 'false', '', 'checkbox');
 601  INSERT INTO `config` VALUES ('', 'host_locale', 'en', '', 'text');
 602  INSERT INTO `config` VALUES ('', 'check_overallocation', 'false', '', 'checkbox');
 603  INSERT INTO `config` VALUES ('', 'currency_symbol', '$', '', 'text');
 604  INSERT INTO `config` VALUES ('', 'host_style', 'default', '', 'text');
 605  INSERT INTO `config` VALUES ('', 'company_name', 'My Company', '', 'text');
 606  INSERT INTO `config` VALUES ('', 'page_title', 'dotProject', '', 'text');
 607  INSERT INTO `config` VALUES ('', 'site_domain', 'dotproject.net', '', 'text');
 608  INSERT INTO `config` VALUES ('', 'email_prefix', '[dotProject]', '', 'text');
 609  INSERT INTO `config` VALUES ('', 'admin_username', 'admin', '', 'text');
 610  INSERT INTO `config` VALUES ('', 'username_min_len', '4', '', 'text');
 611  INSERT INTO `config` VALUES ('', 'password_min_len', '4', '', 'text');
 612  INSERT INTO `config` VALUES ('', 'show_all_tasks', 'true', '', 'checkbox');
 613  INSERT INTO `config` VALUES ('', 'enable_gantt_charts', 'true', '', 'checkbox');
 614  INSERT INTO `config` VALUES ('', 'jpLocale', '', '', 'text');
 615  INSERT INTO `config` VALUES ('', 'log_changes', 'false', '', 'checkbox');
 616  INSERT INTO `config` VALUES ('', 'check_tasks_dates', 'true', '', 'checkbox');
 617  INSERT INTO `config` VALUES ('', 'locale_warn', 'false', '', 'checkbox');
 618  INSERT INTO `config` VALUES ('', 'locale_alert', '^', '', 'text');
 619  INSERT INTO `config` VALUES ('', 'daily_working_hours', '8.0', '', 'text');
 620  INSERT INTO `config` VALUES ('', 'display_debug', 'false', '', 'checkbox');
 621  INSERT INTO `config` VALUES ('', 'link_tickets_kludge', 'false', '', 'checkbox');
 622  INSERT INTO `config` VALUES ('', 'show_all_task_assignees', 'false', '', 'checkbox');
 623  INSERT INTO `config` VALUES ('', 'direct_edit_assignment', 'false', '', 'checkbox');
 624  INSERT INTO `config` VALUES ('', 'restrict_color_selection', 'false', '', 'checkbox');
 625  INSERT INTO `config` VALUES ('', 'cal_day_start', '8', '', 'text');
 626  INSERT INTO `config` VALUES ('', 'cal_day_end', '17', '', 'text');
 627  INSERT INTO `config` VALUES ('', 'cal_day_increment', '15', '', 'text');
 628  INSERT INTO `config` VALUES ('', 'cal_working_days', '1,2,3,4,5', '', 'text');
 629  INSERT INTO `config` VALUES ('', 'cal_day_view_show_minical', 'true', '', 'checkbox');
 630  INSERT INTO `config` VALUES ('', 'restrict_task_time_editing', 'false', '', 'checkbox');
 631  INSERT INTO `config` VALUES ('', 'default_view_m', 'calendar', '', 'text');
 632  INSERT INTO `config` VALUES ('', 'default_view_a', 'day_view', '', 'text');
 633  INSERT INTO `config` VALUES ('', 'default_view_tab', '1', '', 'text');
 634  INSERT INTO `config` VALUES ('', 'index_max_file_size', '-1', '', 'text');
 635  INSERT INTO `config` VALUES ('', 'session_handling', 'app', '', 'text');
 636  INSERT INTO `config` VALUES ('', 'session_idle_time', '2d', '', 'text');
 637  INSERT INTO `config` VALUES ('', 'session_max_lifetime', '1m', '', 'text');
 638  INSERT INTO `config` VALUES ('', 'debug', '1', '', 'text');
 639  INSERT INTO `config` VALUES ('', 'auto_fields_creation', 'false', '', 'checkbox');
 640  INSERT INTO `config` VALUES ('', 'parser_default', '/usr/bin/strings', '', 'text');
 641  INSERT INTO `config` VALUES ('', 'parser_application/msword', '/usr/bin/strings', '', 'text');
 642  INSERT INTO `config` VALUES ('', 'parser_text/html', '/usr/bin/strings', '', 'text');
 643  INSERT INTO `config` VALUES ('', 'parser_application/pdf', '/usr/bin/pdftotext', '', 'text');
 644  
 645  
 646  # 20050222
 647  # moved new config variables by cyberhorse from config-php to a new table
 648  INSERT INTO `config` VALUES ('', 'files_ci_preserve_attr', 'true', '', 'checkbox');
 649  INSERT INTO `config` VALUES ('', 'files_show_versions_edit', 'false', '', 'checkbox');
 650  
 651  # 20050225
 652  # forum variable
 653  INSERT INTO `config` VALUES('', 'forum_descendent_order', 'true', '', 'checkbox');
 654  
 655  # 20050302
 656  # new custom fields
 657  CREATE TABLE custom_fields_struct (
 658  field_id integer primary key,
 659  field_module varchar(30),
 660  field_page varchar(30),
 661  field_htmltype varchar(20),
 662  field_datatype varchar(20),
 663  field_order integer,
 664  field_name varchar(100),
 665  field_extratags varchar(250),
 666  field_description varchar(250)
 667  );
 668  
 669  CREATE TABLE custom_fields_values (
 670  value_id integer,
 671  value_module varchar(30),
 672  value_object_id integer,
 673  value_field_id integer,
 674  value_charvalue varchar(250),
 675  value_intvalue integer
 676  );
 677  
 678  CREATE TABLE custom_fields_lists (
 679  field_id integer,
 680  list_option_id integer,
 681  list_value varchar(250)
 682  );
 683  
 684  # 20050302
 685  # ldap system config variables
 686  INSERT INTO config VALUES ('', 'auth_method', 'sql', 'auth', 'select'); 
 687  INSERT INTO config VALUES ('', 'ldap_host', 'localhost', 'ldap', 'text'); 
 688  INSERT INTO config VALUES ('', 'ldap_port', '389', 'ldap', 'text'); 
 689  INSERT INTO config VALUES ('', 'ldap_version', '3', 'ldap', 'text'); 
 690  INSERT INTO config VALUES ('', 'ldap_base_dn', 'dc=saki,dc=com,dc=au', 'ldap', 'text'); 
 691  INSERT INTO config VALUES ('', 'ldap_user_filter', '(uid=%USERNAME%)', 'ldap', 'text'); 
 692  
 693  # 20050302
 694  # PostNuke authentication variables
 695  INSERT INTO config VALUES ('', 'postnuke_allow_login', 'true', 'auth', 'checkbox');
 696  
 697  # 20050302
 698  # New list support for config variables
 699  CREATE TABLE `config_list` (
 700  `config_list_id` integer not null auto_increment,
 701  `config_id` integer not null default 0,
 702  `config_list_name` varchar(30) not null default '',
 703  PRIMARY KEY(`config_list_id`),
 704  KEY(`config_id`)
 705  );
 706  
 707  INSERT INTO config_list (`config_id`, `config_list_name`)
 708    SELECT config_id, 'sql'
 709      FROM config
 710      WHERE config_name = 'auth_method';
 711  
 712  INSERT INTO config_list (`config_id`, `config_list_name`)
 713    SELECT config_id, 'ldap'
 714      FROM config
 715      WHERE config_name = 'auth_method';
 716  
 717  INSERT INTO config_list (`config_id`, `config_list_name`)
 718    SELECT config_id, 'pn'
 719      FROM config
 720      WHERE config_name = 'auth_method';
 721  
 722  # change the session management to a list
 723  UPDATE config SET config_group = 'session' WHERE config_name like 'session%';
 724  
 725  UPDATE config SET config_type = 'select' WHERE config_name = 'session_handling';
 726  
 727  INSERT INTO config_list (`config_id`, `config_list_name`)
 728    SELECT config_id, 'app'
 729      FROM config
 730      WHERE config_name = 'session_handling';
 731  
 732  INSERT INTO config_list (`config_id`, `config_list_name`)
 733    SELECT config_id, 'php'
 734      FROM config
 735      WHERE config_name = 'session_handling';
 736  
 737  # 20050303
 738  # dropped legacy passwords support
 739  DELETE FROM config WHERE config_name = 'check_legacy_password' LIMIT 1;
 740  
 741  # 20050303
 742  # Added new forum indictator
 743  CREATE TABLE `forum_visits` (
 744    `visit_user` INT(10) NOT NULL DEFAULT 0,
 745    `visit_forum` INT(10) NOT NULL DEFAULT 0,
 746    `visit_message` INT(10) NOT NULL DEFAULT 0,
 747    `visit_date` TIMESTAMP,
 748    KEY `idx_fv` (`visit_user`, `visit_forum`, `visit_message`)
 749  ) TYPE=MyISAM;
 750  
 751  # 20050303
 752  #
 753  CREATE TABLE `event_queue` (
 754    `queue_id` int(11) NOT NULL auto_increment,
 755    `queue_start` int(11) NOT NULL default '0',
 756    `queue_type` varchar(40) NOT NULL default '',
 757    `queue_repeat_interval` int(11) NOT NULL default '0',
 758    `queue_repeat_count` int(11) NOT NULL default '0',
 759    `queue_data` longblob NOT NULL,
 760    `queue_callback` varchar(127) NOT NULL default '',
 761    `queue_owner` int(11) NOT NULL default '0',
 762    `queue_origin_id` int(11) NOT NULL default '0',
 763    `queue_module` varchar(40) NOT NULL default '',
 764    `queue_module_type` varchar(20) NOT NULL default '',
 765    PRIMARY KEY  (`queue_id`),
 766    KEY `queue_start` (`queue_start`),
 767    KEY `queue_module` (`queue_module`),
 768    KEY `queue_type` (`queue_type`),
 769    KEY `queue_origin_id` (`queue_origin_id`)
 770  ) TYPE=MyISAM;
 771  
 772  
 773  # 20050303
 774  # New mail handling options
 775  INSERT INTO config VALUES (NULL, 'mail_transport', 'php', 'mail', 'select');
 776  INSERT INTO config VALUES (NULL, 'mail_host', 'localhost', 'mail', 'text');
 777  INSERT INTO config VALUES (NULL, 'mail_port', '25', 'mail', 'text');
 778  INSERT INTO config VALUES (NULL, 'mail_auth', 'false', 'mail', 'checkbox');
 779  INSERT INTO config VALUES (NULL, 'mail_user', '', 'mail', 'text');
 780  INSERT INTO config VALUES (NULL, 'mail_pass', '', 'mail', 'password');
 781  INSERT INTO config VALUES (NULL, 'mail_defer', 'false', 'mail', 'checkbox');
 782  INSERT INTO config VALUES (NULL, 'mail_timeout', '30', 'mail', 'text');
 783  
 784  INSERT INTO config_list (`config_id`, `config_list_name`)
 785    SELECT config_id, 'php'
 786      FROM config
 787      WHERE config_name = 'mail_transport';
 788  
 789  INSERT INTO config_list (`config_id`, `config_list_name`)
 790    SELECT config_id, 'smtp'
 791      FROM config
 792      WHERE config_name = 'mail_transport';
 793  
 794  # 20050303
 795  # Queue scanning on garbage collection
 796  INSERT INTO config VALUES (NULL, 'session_gc_scan_queue', 'false', 'session', 'checkbox');
 797  
 798  # 20050303
 799  # Shorten permissions fields to get over MySQL index size problem.
 800  ALTER TABLE `gacl_acl` CHANGE `section_value` `section_value` varchar(80) NOT NULL default 'system';
 801  ALTER TABLE `gacl_acl_sections` CHANGE `value` `value` varchar(80) NOT NULL default '';
 802  ALTER TABLE `gacl_aco` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 803  ALTER TABLE `gacl_aco` CHANGE `value` `value` varchar(80) NOT NULL default '';
 804  ALTER TABLE `gacl_aco_map` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 805  ALTER TABLE `gacl_aco_map` CHANGE `value` `value` varchar(80) NOT NULL default '';
 806  ALTER TABLE `gacl_aco_sections` CHANGE `value` `value` varchar(80) NOT NULL default '';
 807  ALTER TABLE `gacl_aro` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 808  ALTER TABLE `gacl_aro` CHANGE `value` `value` varchar(80) NOT NULL default '';
 809  ALTER TABLE `gacl_aro_groups` CHANGE `value` `value` varchar(80) NOT NULL default '';
 810  ALTER TABLE `gacl_aro_map` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 811  ALTER TABLE `gacl_aro_sections` CHANGE `value` `value` varchar(80) NOT NULL default '';
 812  ALTER TABLE `gacl_axo` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 813  ALTER TABLE `gacl_axo` CHANGE `value` `value` varchar(80) NOT NULL default '';
 814  ALTER TABLE `gacl_axo_groups` CHANGE `value` `value` varchar(80) NOT NULL default '';
 815  ALTER TABLE `gacl_axo_map` CHANGE `section_value` `section_value` varchar(80) NOT NULL default '0';
 816  ALTER TABLE `gacl_axo_sections` CHANGE `value` `value` varchar(80) NOT NULL default '';
 817  
 818  # 20050304
 819  # Version tracking table.  From here on in all updates are done via the installer,
 820  # which uses this table to manage the upgrade process.
 821  CREATE TABLE dpversion (
 822      code_version varchar(10) not null default '',
 823      db_version integer not null default '0',
 824      last_db_update date not null default '0000-00-00',
 825      last_code_update date not null default '0000-00-00'
 826  );
 827  
 828  INSERT INTO dpversion VALUES ('2.0-alpha', 2, '2005-03-04', '2005-03-04');
 829  
 830  # 20050307
 831  # Additional LDAP search user and search password fields for Active Directory compatible LDAP authentication
 832  INSERT INTO config VALUES ('', 'ldap_search_user', 'Manager', 'ldap', 'text'); 
 833  INSERT INTO config VALUES ('', 'ldap_search_pass', 'secret', 'ldap', 'password'); 
 834  INSERT INTO config VALUES ('', 'ldap_allow_login', 'true', 'ldap', 'checkbox');
 835  
 836  # 20050311
 837  # removed auto fields cfg options
 838  #
 839  DELETE FROM config WHERE config_name = 'auto_fields_creation' LIMIT 1;
 840  
 841  # 20050311
 842  # Added indices to forum watch to speed up queries
 843  #
 844  CREATE INDEX `idx_fw1` ON `forum_watch` (`watch_user`, `watch_forum`);
 845  CREATE INDEX `idx_fw2` ON `forum_watch` (`watch_user`, `watch_topic`);
Code source de dotProject 2.1 RC1

/db/ -> upgrade_102_to_20alpha.sql (source)
