CAllSupportUpdate::AlterTables

function AlterTables($dbType)
{
	
	global $DB;
	$err_mess = (CAllSupportUpdate::err_mess())."
Function: AlterTables
Line: ";
	
	//add tables
	$addTables = array(
	
		"b_ticket_timetable" => array(
			"MySQL" =>	"
CREATE TABLE b_ticket_timetable
(
	ID INT(18) not null auto_increment,
	NAME varchar(255) not null,
	DESCRIPTION text,
	PRIMARY KEY (ID)
)
",
			"MSSQL" =>	"
CREATE TABLE b_ticket_timetable
(
	ID int NOT NULL IDENTITY (1, 1),
	NAME varchar(255) NOT NULL,
	DESCRIPTION TEXT NULL
)
GO
ALTER TABLE b_ticket_timetable ADD CONSTRAINT PK_b_ticket_timetable PRIMARY KEY (ID)
GO
",
			"Oracle" =>	"
CREATE TABLE b_ticket_timetable
(
	ID NUMBER(18) NOT NULL,
	NAME VARCHAR2(255 CHAR) NULL,
	DESCRIPTION CLOB NULL,
	PRIMARY KEY (ID)
)
/

CREATE SEQUENCE SQ_b_ticket_timetable START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"				
		),
		
		"b_ticket_holidays" => array(
			"MySQL" =>	"
CREATE TABLE b_ticket_holidays
(
	ID INT(18) not null auto_increment,
	NAME varchar(255) not null,
	DESCRIPTION text,
	OPEN_TIME  varchar(10) not null default 'HOLIDAY',
	DATE_FROM datetime not null,
	DATE_TILL datetime not null,
	PRIMARY KEY (ID)
)
			",
			"MSSQL" =>	"
CREATE TABLE b_ticket_holidays
(
	ID int NOT NULL IDENTITY (1, 1),
	NAME varchar(255) NOT NULL,
	DESCRIPTION TEXT NULL,
	OPEN_TIME varchar(255) NOT NULL,
	DATE_FROM datetime NOT NULL,
	DATE_TILL datetime NOT NULL
)
GO
ALTER TABLE b_ticket_holidays ADD CONSTRAINT PK_b_ticket_holidays PRIMARY KEY (ID)
GO
ALTER TABLE b_ticket_holidays ADD CONSTRAINT DF_b_ticket_holidays_OPEN_TIME DEFAULT 'HOLIDAY' FOR OPEN_TIME
GO
",
			"Oracle" =>	"
CREATE TABLE b_ticket_holidays
(
	ID NUMBER(18) NOT NULL,
	NAME VARCHAR2(255 CHAR) NULL,
	DESCRIPTION CLOB NULL,
	OPEN_TIME VARCHAR2(10 CHAR) DEFAULT ('HOLIDAY') NOT NULL,
	DATE_FROM DATE NOT NULL,
	DATE_TILL DATE NOT NULL,
	PRIMARY KEY (ID)
)
/

CREATE SEQUENCE SQ_b_ticket_holidays START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"
		),
		
		"b_ticket_sla_2_holidays" => array(
			"MySQL" =>	"
CREATE TABLE b_ticket_sla_2_holidays
(
	SLA_ID INT(18) not null,
	HOLIDAYS_ID INT(18) not null
)
",
			"MSSQL" =>	"
CREATE TABLE b_ticket_sla_2_holidays
(
	SLA_ID int NOT NULL,
	HOLIDAYS_ID int NOT NULL
)
",
			"Oracle" =>	"
CREATE TABLE b_ticket_sla_2_holidays
(
	SLA_ID NUMBER(18) NOT NULL,
	HOLIDAYS_ID NUMBER(18) NOT NULL
)
/
"
		),
		
		"b_ticket_search" => array(
			"MySQL" =>	"
CREATE TABLE b_ticket_search
(
	MESSAGE_ID INT(18) not null,
	SEARCH_WORD varchar(70) not null
);

ALTER TABLE b_ticket_search ADD INDEX UX_b_ticket_search(SEARCH_WORD)
",
			"MSSQL" =>	"
CREATE TABLE b_ticket_search
(
	MESSAGE_ID int NOT NULL,
	SEARCH_WORD varchar(70) NOT NULL
)
GO
CREATE INDEX UX_b_ticket_search ON b_ticket_search (SEARCH_WORD)
GO
",
			"Oracle" =>	"
CREATE TABLE b_ticket_search
(
	MESSAGE_ID NUMBER(18) NOT NULL,
	SEARCH_WORD VARCHAR2(70 CHAR) NULL
)
/

CREATE INDEX UX_b_ticket_search ON b_ticket_search(SEARCH_WORD)
/
"
		),
		
		"b_ticket_timetable_cache" => array(
			"MySQL" =>	"
CREATE TABLE b_ticket_timetable_cache
(
	ID INT(18) not null auto_increment,
	SLA_ID INT(18) not null,
	DATE_FROM datetime not null,
	DATE_TILL datetime not null,
	W_TIME INT(18) not null,
	W_TIME_INC INT(18) not null,
	PRIMARY KEY (ID)
)
			",
			"MSSQL" =>	"
CREATE TABLE b_ticket_timetable_cache
(
	ID int NOT NULL IDENTITY (1, 1),
	SLA_ID int NOT NULL,
	DATE_FROM datetime NOT NULL,
	DATE_TILL datetime NOT NULL,
	W_TIME int NOT NULL,
	W_TIME_INC int NOT NULL
)
GO
ALTER TABLE b_ticket_timetable_cache ADD CONSTRAINT PK_b_ticket_timetable_cache PRIMARY KEY (ID)
GO
",
			"Oracle" =>	"
CREATE TABLE b_ticket_timetable_cache
(
	ID NUMBER(18) NOT NULL,
	SLA_ID NUMBER(18) NOT NULL,
	DATE_FROM DATE NOT NULL,
	DATE_TILL DATE NOT NULL,
	W_TIME NUMBER(18) NOT NULL,
	W_TIME_INC NUMBER(18) NOT NULL,
	PRIMARY KEY (ID)
)
/

CREATE SEQUENCE SQ_b_ticket_timetable_cache START WITH 1 INCREMENT BY 1 NOMINVALUE NOMAXVALUE NOCYCLE NOCACHE NOORDER
/
"
		),
		
		
	);
	
	
	//delete fields
	$deleteFields = array(
		"b_ticket" => array(
			"DATE_OF_FIRST_USER_MSG_AFTER_SUP_MSG",
			"ID_OF_FIRST_USER_MSG_AFTER_SUP_MSG",
			"DATE_FIRST_USER_M_AFTER_SUP_M",
			"ID_FIRST_USER_M_AFTER_SUP_M",
		),
	);
	
	
	//add fields
	$addFields = array(
		"b_ticket" => array(
			array("FIELD" => "SUPPORT_DEADLINE", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
			array("FIELD" => "SUPPORT_DEADLINE_NOTIFY", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
			array("FIELD" => "D_1_USER_M_AFTER_SUP_M", "MySQL" => "datetime null", "MSSQL" => "datetime NULL", "Oracle" => "DATE NULL",),
			array("FIELD" => "ID_1_USER_M_AFTER_SUP_M", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
		),
		
		"b_ticket_sla"=> array(
			array("FIELD" => "TIMETABLE_ID", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
		),
		
		"b_ticket_sla_shedule" => array(
			array("FIELD" => "TIMETABLE_ID", "MySQL" => "int(18) null", "MSSQL" => "int NULL", "Oracle" => "NUMBER(18) NULL",),
		),
		
		"b_ticket_user_ugroup" => array(
			array("FIELD" => "CAN_MAIL_UPDATE_GROUP_MESSAGES", "MySQL" => "char(1) NOT NULL default 'N'", "MSSQL" => "char(1) NOT NULL DEFAULT 'N'", "Oracle" => "CHAR(1 CHAR) DEFAULT 'N' NOT NULL",),	
		),
	);
	
	if($DB->TableExists("b_ticket"))
	{
		foreach($addTables as $table => $arr)
		{
			if(!$DB->TableExists($table))
			{
				$arQuery = $DB->ParseSQLBatch(str_replace("r", "", $arr[$dbType]));
				foreach($arQuery as $i => $sql)
				{
					$res = $DB->Query($sql, true);
					if(!$res) return false;
				}					
			}
		}
	}
	
	foreach($deleteFields as $table => $arr)
	{
		if($DB->TableExists($table))
		{
			foreach($arr as $n => $FN)
			{
				if($DB->Query("select $FN from $table WHERE 1=0", true))
				{
					$res = $DB->Query("ALTER TABLE $table DROP $FN", true);
					if(!$res) return false;
				}					
			}
		}
	}
	
	foreach($addFields as $table => $arr)
	{
		if($DB->TableExists($table))
		{
			foreach($arr as $n => $arrF)
			{
				$FN = $arrF["FIELD"];
				$FT = $arrF[$dbType];
				if(!$DB->Query("select $FN from $table WHERE 1=0", true))
				{
					$res = $DB->Query("ALTER TABLE $table ADD $FN $FT", true);
					if(!$res) return false;
				}
			}
		}
	}
	return true;			
}