# Linea21 DB patch - UTF-8 format
# patch v1.0/v1.1 To v1.2

#Insert here query for patch update to v1.2

# For Windows default config only
SET SESSION sql_mode='';

-- 
-- Modifications on **Theme** table
--
ALTER TABLE `theme` ADD `theme_desc_project` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL AFTER `theme_sd_theme` ;
ALTER TABLE `theme` ADD `theme_range_project` TINYINT NOT NULL DEFAULT '0' AFTER `theme_desc_workshop` ;

-- 
-- Renaming  **Resources** tables
--
RENAME TABLE `bibliography_ressources`  TO `bibliography_resources` ;
RENAME TABLE `multimedia_ressources`  TO `multimedia_resources` ;
RENAME TABLE `link_ressources`  TO `link_resources` ;


-- 
-- Modifications on **Rights** table
--
ALTER TABLE `rights` ADD `rights_project` ENUM( 'A', 'U' ) NOT NULL AFTER `rights_workshop` ;
UPDATE `rights` SET rights_project = 'U';
UPDATE `rights` SET rights_project = 'A' WHERE rights_theme = 'A';

-- 
-- Creating and populating the **Priority** table
--
CREATE TABLE `priority` (
  `priority_id` int(11) NOT NULL auto_increment,
  `priority_name` varchar(255) NOT NULL,
  `priority_date_crea` datetime default NULL,
  `priority_last_modify` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`priority_id`)
);


INSERT INTO `priority` VALUES (1, 'bassa', NOW(), NOW());
INSERT INTO `priority` VALUES (2, 'media', NOW(), NOW());
INSERT INTO `priority` VALUES (3, 'alta', NOW(), NOW());


-- 
-- Creating the **Project** table
--
CREATE TABLE `project` (
  `project_id` int(11) NOT NULL auto_increment,
  `project_parent_id` int(11) NOT NULL,
  `project_priority_id` int(11) NOT NULL,
  `project_scale_id` int(11) NOT NULL,
  `project_name` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  `project_description` text character set utf8 collate utf8_bin,
  `project_body` text character set utf8 collate utf8_bin NOT NULL,
  `project_begin_date` date NOT NULL default '0001-01-01',
  `project_estimated_date` date NOT NULL default '0001-01-01',
  `project_end_date` date NOT NULL default '0001-01-01',
  `project_posted_by` int(11) NOT NULL,
  `project_published_date` datetime NOT NULL default '0001-01-01 00:00:00',
  `project_date_crea` datetime NOT NULL default '0001-01-01 00:00:00',
  `project_last_modify` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `project_statut` enum('D','P','AA','PA','E') character set utf8 collate utf8_bin NOT NULL,
  PRIMARY KEY  (`project_id`),
  FULLTEXT KEY `project_name` (`project_name`,`project_description`,`project_body`)
)  ENGINE=MyISAM DEFAULT CHARACTER SET utf8 COLLATE utf8_bin ;
