#
# dotproject Database Schema
#
# Use this schema for creating your database for
# a new installation of dotProject.
#
# Changed: Add in a dbprefix string to be replaced with the actual db table prefix
# Changed: Remove explicit mySQL table type which then allows for being able to use other
#			database engines

CREATE TABLE dp_company` (
  `id` INT(10) NOT NULL auto_increment,
  `module` INT(10) NOT NULL default '0',
  `name` varchar(100) default '',
  `phone_1` varchar(30) default '',
  `phone_2` varchar(30) default '',
  `fax` varchar(30) default '',
  `address_1` varchar(50) default '',
  `address_2` varchar(50) default '',
  `city` varchar(30) default '',
  `state` varchar(30) default '',
  `zip` varchar(11) default '',
  `primary_url` varchar(255) default '',
  `owner` int(11) NOT NULL default '0',
  `description` text,
  `type` int(3) NOT NULL DEFAULT '0',
  `email` varchar(255),
  `custom` LONGTEXT,
  PRIMARY KEY (`id`),
	KEY `idx_cpy1` (`owner`)
) ;

#
# New to version 1.0
#
CREATE TABLE dp_department` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `parent` int(10) unsigned NOT NULL default '0',
  `company` int(10) unsigned NOT NULL default '0',
  `name` tinytext NOT NULL,
  `phone` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `address_1` varchar(30) default NULL,
  `address_2` varchar(30) default NULL,
  `city` varchar(30) default NULL,
  `state` varchar(30) default NULL,
  `zip` varchar(11) default NULL,
  `url` varchar(25) default NULL,
  `desc` text,
  `owner` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) COMMENT='Department heirarchy under a company';

CREATE TABLE dp_contact` (
  `id` int(11) NOT NULL auto_increment,
  `first_name` varchar(30) default NULL,
  `last_name` varchar(30) default NULL,
  `order_by` varchar(30) NOT NULL default '',
  `title` varchar(50) default NULL,
  `birthday` date default NULL,
  `job` varchar(255) default NULL,
  `company` varchar(100) NOT NULL default '',
  `department` TINYTEXT,
  `type` varchar(20) default NULL,
  `email` varchar(255) default NULL,
  `email_2` varchar(255) default NULL,
  `url` varchar(255) default NULL,
  `phone` varchar(30) default NULL,
  `phone_2` varchar(30) default NULL,
  `fax` varchar(30) default NULL,
  `mobile` varchar(30) default NULL,
  `address_1` varchar(60) default NULL,
  `address_2` varchar(60) default NULL,
  `city` varchar(30) default NULL,
  `state` varchar(30) default NULL,
  `zip` varchar(11) default NULL,
  `country` varchar(30) default NULL,
  `jabber` varchar(255) default NULL,
  `icq` varchar(20) default NULL,
  `msn` varchar(255) default NULL,
  `yahoo` varchar(255) default NULL,
  `aol` varchar(30) default NULL,
  `notes` text,
  `project` int(11) NOT NULL default '0',
  `icon` varchar(20) default 'obj/contact',
  `owner` int unsigned default '0',
  `private` tinyint unsigned default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_oby` (`order_by`),
  KEY `idx_co` (`company`),
  KEY `idx_prp` (`project`)
) ;

CREATE TABLE dp_event` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `start_date` datetime default null,
  `end_date` datetime default null,
  `parent` int(11) unsigned NOT NULL default '0',
  `description` text,
  `times_recuring` int(11) unsigned NOT NULL default '0',
  `recurs` int(11) unsigned NOT NULL default '0',
  `remind` int(10) unsigned NOT NULL default '0',
  `icon` varchar(20) default 'obj/event',
  `owner` int(11) default '0',
  `project` int(11) default '0',
  `private` tinyint(3) default '0',
  `type` tinyint(3) default '0',
  `cwd` tinyint(3) default '0',
	`notify` tinyint(3) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `id_esd` (`start_date`),
  KEY `id_eed` (`end_date`),
  KEY `id_evp` (`parent`),
	KEY `idx_ev1` (`owner`),
	KEY `idx_ev2` (`project`)
) ;

# 20050303
#
CREATE TABLE dp_event_queue` (
  `id` int(11) NOT NULL auto_increment,
  `start` int(11) NOT NULL default '0',
  `type` varchar(40) NOT NULL default '',
  `repeat_interval` int(11) NOT NULL default '0',
  `repeat_count` int(11) NOT NULL default '0',
  `data` longblob NOT NULL,
  `callback` varchar(127) NOT NULL default '',
  `owner` int(11) NOT NULL default '0',
  `origin_id` int(11) NOT NULL default '0',
  `module` varchar(40) NOT NULL default '',
  `batched` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `queue_start` (`batched`,`start`),
  KEY `queue_module` (`module`),
  KEY `queue_type` (`type`),
  KEY `queue_origin_id` (`origin_id`)
) ;


CREATE TABLE dp_file` (
  `id` int(11) NOT NULL auto_increment,
  `real_filename` varchar(255) NOT NULL default '',
  `folder` int(11) NOT NULL default '0',
  `project` int(11) NOT NULL default '0',
  `task` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  `parent` int(11) default '0',
  `description` text,
  `type` varchar(100) default NULL,
  `owner` int(11) default '0',
  `date` datetime default NULL,
  `size` int(11) default '0',
  `version` float NOT NULL default '0',
  `icon` varchar(20) default 'obj/',
  `category` int(11) default '0',
	`checkout` varchar(255) not null default '',
	`co_reason` text,
	`version_id` int(11) not null default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_file_task` (`task`),
  KEY `idx_file_project` (`project`),
  KEY `idx_file_parent` (`parent`),
	KEY `idx_file_vid` (`version_id`)
) ;

CREATE TABLE dp_file_index` (
  `file_id` int(11) NOT NULL default '0',
  `word` varchar(50) NOT NULL default '',
  `word_placement` int(11) NOT NULL default '0',
  PRIMARY KEY  (`file_id`,`word`, `word_placement`),
  KEY `idx_fwrd` (`word`)
) ;

CREATE TABLE dp_forum_message` (
  `id` int(11) NOT NULL auto_increment,
  `forum` int(11) NOT NULL default '0',
  `parent` int(11) NOT NULL default '0',
  `author` int(11) NOT NULL default '0',
  `editor` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `date` datetime default '0000-00-00 00:00:00',
  `body` text,
  `published` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`id`),
  KEY `idx_mparent` (`parent`),
  KEY `idx_mdate` (`date`),
  KEY `idx_mforum` (`forum`)
) ;

#
# new field forum_last_id in Version 1.0
#
CREATE TABLE dp_forum` (
  `id` int(11) NOT NULL auto_increment,
  `project` int(11) NOT NULL default '0',
  `status` tinyint(4) NOT NULL default '-1',
  `owner` int(11) NOT NULL default '0',
  `name` varchar(50) NOT NULL default '',
  `created_date` datetime default '0000-00-00 00:00:00',
  `last_date` datetime default '0000-00-00 00:00:00',
  `last_id` INT UNSIGNED DEFAULT '0' NOT NULL,
  `message_count` int(11) NOT NULL default '0',
  `description` varchar(255) default NULL,
  `moderated` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_fproject` (`project`),
  KEY `idx_fowner` (`owner`),
  KEY `forum_status` (`status`)
) ;

#
# New to Version 1.0
#
CREATE TABLE dp_forum_watch` (
    `id` int(11) NOT NULL auto_increment,
  `user` int(10) unsigned NOT NULL default '0',
  `forum` int(10) unsigned default NULL,
  `topic` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`),
	KEY `idx_fw1` (`user`, `forum`),
	KEY `idx_fw2` (`user`, `topic`)
) COMMENT='Links users to the forums/messages they are watching';

# 20050303
# New to Version 2.0
CREATE TABLE dp_forum_visit` (
   `id` int(11) NOT NULL auto_increment,
  `user` INT(10) NOT NULL DEFAULT 0,
  `forum` INT(10) NOT NULL DEFAULT 0,
  `message` INT(10) NOT NULL DEFAULT 0,
  `date` TIMESTAMP,
   PRIMARY KEY  (`id`),
  KEY `idx_fv` (`user`, `forum`, `message`)
) ;

CREATE TABLE dp_permissions` (
  `id` int(11) NOT NULL auto_increment,
  `user` int(11) NOT NULL default '0',
  `grant_on` varchar(12) NOT NULL default '',
  `item` int(11) NOT NULL default '0',
  `value` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_pgrant_on` (`grant_on`,`item`,`user`),
  KEY `idx_puser` (`user`),
  KEY `idx_pvalue` (`value`)
) ;

CREATE TABLE dp_project` (
  `id` int(11) NOT NULL auto_increment,
  `company` int(11) NOT NULL default '0',
  `company_internal` int(11) NOT NULL default '0',
  `department` int(11) NOT NULL default '0',
  `name` varchar(255) default NULL,
  `short_name` varchar(10) default NULL,
  `owner` int(11) default '0',
  `url` varchar(255) default NULL,
  `demo_url` varchar(255) default NULL,
  `started_date` datetime default NULL,
  `end_date` datetime default NULL,
  `status` int(11) default '0',
  `percent_complete` tinyint(4) default '0',
  `color_identifier` varchar(7) default '#eeeeee',
  `description` text,
  `target_budget` decimal(10,2) default '0.00',
  `actual_budget` decimal(10,2) default '0.00',
  `creator` int(11) default '0',
  `private` tinyint(3) unsigned default '0',
  `departments` CHAR( 100 ) ,
  `contacts` CHAR( 100 ) ,
  `priority` tinyint(4) default '0',
  `type` SMALLINT DEFAULT '0' NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_project_owner` (`owner`),
  KEY `idx_sdate` (`started_date`),
  KEY `idx_edate` (`end_date`),
  KEY `project_short_name` (`short_name`),
	KEY `idx_proj1` (`company`)

) ;

CREATE TABLE dp_project_contact` (
`id` int(11) NOT NULL auto_increment,
  `project_id` INT(10) NOT NULL,
  `contact_id` INT(10) NOT NULL,
PRIMARY KEY  (`id`)
) ;

CREATE TABLE dp_project_department` (
`id` int(11) NOT NULL auto_increment,
  `project_id` INT(10) NOT NULL,
  `department_id` INT(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ;

CREATE TABLE dp_task_log` (
  `id` INT(11) NOT NULL auto_increment,
  `task` INT(11) NOT NULL default '0',
  `name` VARCHAR(255) default NULL,
  `description` TEXT,
  `creator` INT(11) NOT NULL default '0',
  `hours` FLOAT DEFAULT "0" NOT NULL,
  `date` DATETIME,
  `costcode` VARCHAR(8) NOT NULL default '',
  `problem` TINYINT( 1 ) DEFAULT '0',
  `reference` TINYINT( 4 ) DEFAULT '0',
  `related_url` VARCHAR( 255 ) DEFAULT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_log_task` (`task`)
) ;

CREATE TABLE dp_task` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `parent` int(11) default '0',
  `milestone` tinyint(1) default '0',
  `project` int(11) NOT NULL default '0',
  `owner` int(11) NOT NULL default '0',
  `started_date` datetime default NULL,
  `duration` float unsigned default '0',
  `duration_type` int(11) NOT NULL DEFAULT 1,
  `hours_worked` float unsigned default '0',
  `end_date` datetime default NULL,
  `status` int(11) default '0',
  `priority` tinyint(4) default '0',
  `percent_complete` tinyint(4) default '0',
  `description` text,
  `target_budget` decimal(10,2) default '0.00',
  `related_url` varchar(255) default NULL,
  `creator` int(11) NOT NULL default '0',
  `order` int(11) NOT NULL default '0',
  `client_publish` tinyint(1) NOT NULL default '0',
  `dynamic` tinyint(1) NOT NULL default 0,
  `access` int(11) NOT NULL default '0',
  `notify` int(11) NOT NULL default '0',
  `departments` CHAR( 100 ),
  `contacts` CHAR( 100 ),
  `custom` LONGTEXT,
  `type` SMALLINT DEFAULT '0' NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_task_parent` (`parent`),
  KEY `idx_task_project` (`project`),
  KEY `idx_task_owner` (`owner`),
  KEY `idx_task_order` (`order`),
	KEY `idx_task1` (`started_date`),
	KEY `idx_task2` (`end_date`)
) ;

CREATE TABLE dp_task_contact` (
  `task_id` INT(10) NOT NULL,
  `contact_id` INT(10) NOT NULL,
  KEY `idx_task_contacts` (`task_id`)
) ;

CREATE TABLE dp_task_department` (
  `task_id` INT(10) NOT NULL,
  `department_id` INT(10) NOT NULL,
  KEY `idx_task_departments` (`task_id`)
) ;

CREATE TABLE dp_ticket` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `company_id` int(10) NOT NULL default '0',
  `project_id` int(10) NOT NULL default '0',
  `author` varchar(100) NOT NULL default '',
  `recipient` varchar(100) NOT NULL default '',
  `subject` varchar(100) NOT NULL default '',
  `attachment` tinyint(1) unsigned NOT NULL default '0',
  `timestamp` int(10) unsigned NOT NULL default '0',
  `type` varchar(15) NOT NULL default '',
  `assignment` int(10) unsigned NOT NULL default '0',
  `parent_ticket_id` int(10) unsigned NOT NULL default '0',
  `activity` int(10) unsigned NOT NULL default '0',
  `priority` tinyint(1) unsigned NOT NULL default '1',
  `cc` varchar(255) NOT NULL default '',
  `body` text NOT NULL,
  `signature` text,
  PRIMARY KEY  (`id`),
  KEY `parent` (`parent_ticket_id`),
  KEY `type` (`type`)
) ;

CREATE TABLE dp_user_event` (
	`user_id` int(11) NOT NULL default '0',
	`event_id` int(11) NOT NULL default '0',
	KEY `uek1` (`user_id`, `event_id`),
	KEY `uek2` (`event_id`, `user_id`)
) ;

CREATE TABLE dp_user_task` (
  `user_id` int(11) NOT NULL default '0',
  `user_type` tinyint(4) NOT NULL default '0',
  `task_id` int(11) NOT NULL default '0',
  `perc_assignment` int(11) NOT NULL default '100',
  `user_task_priority` tinyint(4) default '0',
  PRIMARY KEY  (`user_id`,`task_id`),
  KEY `user_type` (`user_type`),
	KEY `idx_user_tasks` ( `task_id` )
) ;

CREATE TABLE dp_user` (
  `id` int(11) NOT NULL auto_increment,
  `contact_id` int(11) NOT NULL default '0',
  `username` varchar(255) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `parent_id` int(11) NOT NULL default '0',
  `type` tinyint(3) not null default '0',
  `company_id` int(11) default '0',
  `department_id` int(11) default '0',
  `owner` int(11) NOT NULL default '0',
  `signature` TEXT,
  PRIMARY KEY  (`id`),
  KEY `idx_uid` (`username`),
  KEY `idx_pwd` (`password`),
  KEY `idx_user_parent` (`parent_id`)
) ;

CREATE TABLE dp_task_dependency` (
    `task_id` int(11) NOT NULL,
    `req_task_id` int(11) NOT NULL,
    PRIMARY KEY (`task_id`, `req_task_id`)
);

CREATE TABLE dp_user_preference` (
  `pref_user` varchar(12) NOT NULL default '',
  `pref_name` varchar(72) NOT NULL default '',
  `pref_value` varchar(32) NOT NULL default '',
  KEY `pref_user` (`pref_user`,`pref_name`)
) ;

#
# Table structure for table 'modules'
#
CREATE TABLE dp_module` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(64) NOT NULL default '',
  `directory` varchar(64) NOT NULL default '',
  `version` varchar(10) NOT NULL default '',
  `setup_class` varchar(64) NOT NULL default '',
  `type` varchar(64) NOT NULL default '',
  `active` int(1) unsigned NOT NULL default '0',
  `ui_name` varchar(20) NOT NULL default '',
  `ui_icon` varchar(64) NOT NULL default '',
  `ui_order` tinyint(3) NOT NULL default '0',
  `ui_active` int(1) unsigned NOT NULL default '0',
  `description` varchar(255) NOT NULL default '',
  `permissions_item_table` CHAR( 100 ),
  `permissions_item_field` CHAR( 100 ),
  `permissions_item_label` CHAR( 100 ),
  PRIMARY KEY  (`id`,`directory`)
);

#
# Table structure for table 'syskeys'
#

CREATE TABLE dp_syskey` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(48) NOT NULL default '' unique,
  `label` varchar(255) NOT NULL default '',
  `type` int(1) unsigned NOT NULL default '0',
  `sep1` char(2) default '\n',
  `sep2` char(2) NOT NULL default '|',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_syskey_name` (`name`)
);

#
# Table structure for table 'sysvals'
#

CREATE TABLE dp_sysval` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `key_id` int(10) unsigned NOT NULL default '0',
  `title` varchar(48) NOT NULL default '',
  `value` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_sysval_title` (`title`)
);

#
# Table structure for table 'roles'
#

CREATE TABLE dp_role` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(24) NOT NULL default '',
  `description` varchar(255) NOT NULL default '',
  `type` int(3) unsigned NOT NULL default '0',
  `module` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

#
# Table structure for table 'user_roles'
#

CREATE TABLE dp_user_role` (
  `user_id` int(10) unsigned NOT NULL default '0',
  `role_id` int(10) unsigned NOT NULL default '0'
);

# Host: localhost
# Database: dotproject
# Table: 'common_notes'
#
CREATE TABLE dp_note` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `author` int(10) unsigned NOT NULL default '0',
  `module` int(10) unsigned NOT NULL default '0',
  `record_id` int(10) unsigned NOT NULL default '0',
  `category` int(3) unsigned NOT NULL default '0',
  `title` varchar(100) NOT NULL default '',
  `body` text NOT NULL,
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  `hours` float NOT NULL default '0',
  `code` varchar(8) NOT NULL default '',
  `created` datetime NOT NULL default '0000-00-00 00:00:00',
  `modified` timestamp,
  `modified_by` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ;



#20040823
#Added user access log
CREATE TABLE dp_user_access_log` (
`id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_id` INT( 10 ) UNSIGNED NOT NULL ,
`user_ip` VARCHAR( 15 ) NOT NULL ,
`date_time_in` DATETIME DEFAULT '0000-00-00 00:00:00',
`date_time_out` DATETIME DEFAULT '0000-00-00 00:00:00',
`date_time_last_action` DATETIME DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY ( `id` )
);

#20040910
#Pinned tasks
CREATE TABLE dp_user_task_pin` (
`user_id` int(11) NOT NULL default '0',
`task_id` int(10) NOT NULL default '0',
`task_pinned` tinyint(2) NOT NULL default '1',
PRIMARY KEY (`user_id`,`task_id`)
);

#
# Table structure for TABLE `config`
#
# Creation: Feb 23, 2005 at 01:26 PM
# Last update: Feb 24, 2005 at 02:15 AM
#

CREATE TABLE dp_config` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(127) NOT NULL default '',
  `value` varchar(255) NOT NULL default '',
  `group` varchar(255) NOT NULL default '',
  `type` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `config_name` (`name`)
);

# 20050302
# New list support for config variables
CREATE TABLE dp_config_list` (
`id` integer not null auto_increment,
`config_id` integer not null default 0,
`name` varchar(30) not null default '',
PRIMARY KEY(`id`),
KEY(`config_id`)
);


# 20050302
# new custom fields
CREATE TABLE dp_customfield` (
id integer primary key,
module varchar(30),
page varchar(30),
html_type varchar(20),
data_type varchar(20),
`order` integer,
name varchar(100),
extra_tags varchar(250),
description varchar(250)
);

CREATE TABLE dp_customfield_value` (
id integer,
module varchar(30),
object_id integer,
field_id integer,
char_value varchar(250),
int_value integer,
KEY `idx_cfv_id` (`id`)
);

CREATE TABLE dp_customfields_list` (
field_id integer,
option_id integer,
value varchar(250)
);

DROP TABLE IF EXISTS dp_billingcode`;
CREATE TABLE dp_billingcode` (
  `id` bigint(20) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL default '',
  `value` float NOT NULL default '0',
  `desc` varchar(255) NOT NULL default '',
  `status` int(1) NOT NULL default '0',
  `company_id` bigint(20) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ;


#
# Table structure for table `sessions`
#

DROP TABLE IF EXISTS dp_session`;
CREATE TABLE dp_sessions` (
	`id` varchar(60) NOT NULL default '',
	`user` INT DEFAULT '0' NOT NULL,
	`data` LONGBLOB,
	`updated` TIMESTAMP,
	`created` DATETIME NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY (`id`),
	KEY (`updated`),
	KEY (`created`)
);

# 20050304
# Version tracking table.  From here on in all updates are done via the installer,
# which uses this table to manage the upgrade process.
CREATE TABLE dot_dpversion (
	code_version varchar(10) not null default '',
	db_version integer not null default '0',
	last_db_update date not null default '0000-00-00',
	last_code_update date not null default '0000-00-00'
);

# 20070126
#
# Table structure for table `file_folders`
#

CREATE TABLE dp_file_folder` (
	`id` int(11) NOT NULL auto_increment,
	`parent` int(11) NOT NULL default '0',
	`name` varchar(255) NOT NULL default '',
	`description` text,
	PRIMARY KEY  (`id`)
);

# 20101010

CREATE TABLE dp_dotpermission` (
  `acl_id` int(11) NOT NULL DEFAULT '0',
  `user_id` varchar(80) NOT NULL DEFAULT '',
  `section` varchar(80) NOT NULL DEFAULT '',
  `axo` varchar(80) NOT NULL DEFAULT '',
  `permission` varchar(80) NOT NULL DEFAULT '',
  `allow` int(11) NOT NULL DEFAULT '0',
  `priority` int(11) NOT NULL DEFAULT '0',
  `enabled` int(11) NOT NULL DEFAULT '0',
  KEY `user_id` (`user_id`,`section`,`permission`,`axo`)
);