public static function updateDBSchema()
{
global $DB, $DBType;
$updater = new \CUpdater();
$updater->Init($curPath = "", $DBType, $updaterName = "", $curDir = "", "sale", "DB");
// table existence check
$locationTableExists = $updater->TableExists("b_sale_location");
if ($locationTableExists) {
$locationGroupTableExists = $updater->TableExists("b_sale_location_group");
$locationGroupNameTableExists = $updater->TableExists("b_sale_location_group_lang");
$locationNameTableExists = $updater->TableExists("b_sale_loc_name");
$locationExternalServiceTableExists = $updater->TableExists("b_sale_loc_ext_srv");
$locationExternalTableExists = $updater->TableExists("b_sale_loc_ext");
$locationTypeTableExists = $updater->TableExists("b_sale_loc_type");
$locationTypeNameTableExists = $updater->TableExists("b_sale_loc_type_name");
$locationLoc2SiteTableExists = $updater->TableExists("b_sale_loc_2site");
$locationDefaul2SiteTableExists = $updater->TableExists("b_sale_loc_def2site");
$tax2LocationTableExists = $updater->TableExists("b_sale_tax2location");
$delivery2LocationTableExists = $updater->TableExists("b_sale_delivery2location");
// adding columns to B_SALE_LOCATION
// if CODE not exists, add it
if (!$DB->query("select CODE from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD CODE varchar(100) not null", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD CODE varchar(100) default '' NOT NULL", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL"));
}
// if CODE exists, copy values from ID and add index
if ($DB->query("select CODE from b_sale_location WHERE 1=0", true)) {
if (!$DB->IndexExists('b_sale_location', array('CODE'))) {
$DB->query("update b_sale_location set CODE = ID");
// OK: oracle, mssql
$DB->query("CREATE UNIQUE INDEX IX_B_SALE_LOC_CODE ON b_sale_location (CODE)");
// OK: oracle, mssql
}
}
// create LEFT_MARGIN
if (!$DB->query("select LEFT_MARGIN from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD LEFT_MARGIN int", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LEFT_MARGIN int", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LEFT_MARGIN NUMBER(18)"));
}
// create RIGHT_MARGIN
if (!$DB->query("select RIGHT_MARGIN from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD RIGHT_MARGIN int", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD RIGHT_MARGIN int", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD RIGHT_MARGIN NUMBER(18)"));
}
$lMarginExists = $DB->query("select LEFT_MARGIN from b_sale_location WHERE 1=0", true);
$rMarginExists = $DB->query("select RIGHT_MARGIN from b_sale_location WHERE 1=0", true);
// add indexes if margins exist, but indexes not
if ($lMarginExists && $rMarginExists) {
if (!$DB->IndexExists('b_sale_location', array('LEFT_MARGIN', 'RIGHT_MARGIN'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_MARGINS ON b_sale_location (LEFT_MARGIN, RIGHT_MARGIN)");
// OK: oracle, mssql
}
if (!$DB->IndexExists('b_sale_location', array('RIGHT_MARGIN', 'LEFT_MARGIN'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_MARGINS_REV ON b_sale_location (RIGHT_MARGIN, LEFT_MARGIN)");
// OK: oracle, mssql
}
}
// add PARENT_ID
if (!$DB->query("select PARENT_ID from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD PARENT_ID int DEFAULT '0'", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD PARENT_ID int DEFAULT '0'", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD PARENT_ID NUMBER(18) DEFAULT '0'"));
}
// add index, if not exist for PARENT_ID, that exists
if ($DB->query("select PARENT_ID from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('PARENT_ID'))) {
$DB->query('CREATE INDEX IX_B_SALE_LOC_PARENT ON b_sale_location (PARENT_ID)');
// OK: oracle, mssql
}
// add DEPTH_LEVEL
if (!$DB->query("select DEPTH_LEVEL from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD DEPTH_LEVEL int default '1'", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD DEPTH_LEVEL int DEFAULT '1'", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD DEPTH_LEVEL NUMBER(18) DEFAULT '1'"));
}
// add index, if not exist for DEPTH_LEVEL, that exists
if ($DB->query("select DEPTH_LEVEL from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('DEPTH_LEVEL'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_DL ON b_sale_location (DEPTH_LEVEL)");
// OK: oracle, mssql
}
// add TYPE_ID
if (!$DB->query("select TYPE_ID from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD TYPE_ID int", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD TYPE_ID int", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD TYPE_ID NUMBER(18)"));
}
// add index, if not exist for TYPE_ID, that exists
if ($DB->query("select TYPE_ID from b_sale_location WHERE 1=0", true) && !$DB->IndexExists('b_sale_location', array('TYPE_ID'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_TYPE ON b_sale_location (TYPE_ID)");
// OK: oracle, mssql
}
// add LATITUDE
if (!$DB->query("select LATITUDE from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD LATITUDE decimal(8,6)", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LATITUDE decimal(8,6)", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LATITUDE NUMBER(8,6)"));
}
// add LONGITUDE
if (!$DB->query("select LONGITUDE from b_sale_location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location ADD LONGITUDE decimal(9,6)", "MSSQL" => "ALTER TABLE B_SALE_LOCATION ADD LONGITUDE decimal(9,6)", "Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LONGITUDE NUMBER(9,6)"));
}
// dropping not-nulls
if ($DBType == 'mysql') {
$DB->query("ALTER TABLE b_sale_location MODIFY COUNTRY_ID int NULL");
}
if ($DBType == 'mssql') {
$DB->query("ALTER TABLE B_SALE_LOCATION ALTER COLUMN COUNTRY_ID int NULL");
}
if ($DBType == 'oracle') {
// dropping not-nulls
if ($DB->query("SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'B_SALE_LOCATION' and COLUMN_NAME = 'COUNTRY_ID' and NULLABLE = 'N'")->fetch()) {
//if ($DB->IndexExists('b_sale_location', array('COUNTRY_ID')))
// $DB->query('drop index IXS_LOCATION_COUNTRY_ID');
$DB->query("ALTER TABLE B_SALE_LOCATION MODIFY ( COUNTRY_ID NUMBER(18) NULL)");
}
//if (!$DB->IndexExists('b_sale_location', array('COUNTRY_ID')))
// $DB->query('CREATE INDEX IXS_LOCATION_COUNTRY_ID ON B_SALE_LOCATION(COUNTRY_ID)');
// altering sequences for oracle
// new sequence for b_sale_location
if ($DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_SALE_LOCATION'", true)->fetch()) {
$DB->query("RENAME SQ_SALE_LOCATION TO SQ_B_SALE_LOCATION");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOCATION_INSERT\n\t\t\t\t\t\tBEFORE INSERT\n\t\t\t\t\t\tON B_SALE_LOCATION\n\t\t\t\t\t\tFOR EACH ROW\n\t\t\t\t\t\tBEGIN\n\t\t\t\t\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\t\t\t\t\tSELECT SQ_B_SALE_LOCATION.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND;");
// OK
}
// new sequence for b_sale_location_group
if ($locationGroupTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOCATION_GROUP'", true)->fetch()) {
$DB->query("RENAME SQ_SALE_LOCATION_GROUP TO SQ_B_SALE_LOCATION_GROUP");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOCATION_GROUP_INSERT\n\t\t\t\t\t\tBEFORE INSERT\n\t\t\t\t\t\tON B_SALE_LOCATION_GROUP\n\t\t\t\t\t\tFOR EACH ROW\n\t\t\t\t\t\tBEGIN\n\t\t\t\t\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\t\t\t\t\tSELECT SQ_B_SALE_LOCATION_GROUP.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND;");
// OK
}
// new sequence for b_sale_location_group_lang
if ($locationGroupNameTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOCATION_GROUP_LANG'", true)->fetch()) {
$DB->query("RENAME SQ_SALE_LOCATION_GROUP_LANG TO SQ_B_SALE_LOCATION_GROUP_LANG");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOCGR_LANG_INSERT\n\t\t\t\t\t\tBEFORE INSERT\n\t\t\t\t\t\tON B_SALE_LOCATION_GROUP_LANG\n\t\t\t\t\t\tFOR EACH ROW\n\t\t\t\t\t\tBEGIN\n\t\t\t\t\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\t\t\t\t\tSELECT SQ_B_SALE_LOCATION_GROUP_LANG.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\t\t\t\t\tEND IF;\n\t\t\t\t\t\tEND;");
// OK
}
}
// adding columns to B_SALE_LOCATION_GROUP
if ($locationGroupTableExists) {
if (!$DB->query("select CODE from b_sale_location_group WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_location_group ADD CODE varchar(100) NOT NULL", "MSSQL" => "ALTER TABLE B_SALE_LOCATION_GROUP ADD CODE varchar(100) default '' NOT NULL", "Oracle" => "ALTER TABLE B_SALE_LOCATION_GROUP ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL"));
}
// if CODE exists, copy values from ID and add index
if ($DB->query("select CODE from b_sale_location_group WHERE 1=0", true)) {
if (!$DB->IndexExists('b_sale_location_group', array('CODE'))) {
$DB->query("update b_sale_location_group set CODE = ID");
// OK: oracle, mssql
$DB->query("CREATE UNIQUE INDEX IX_B_SALE_LOC_GROUP_CODE ON b_sale_location_group (CODE)");
// OK: oracle, mssql
}
}
}
if (!$locationNameTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_name (\n\t\t\t\t\t\t\t\t\tID int not null auto_increment,\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID char(2) not null,\n\t\t\t\t\t\t\t\t\tLOCATION_ID int not null,\n\t\t\t\t\t\t\t\t\tNAME varchar(100) not null,\n\t\t\t\t\t\t\t\t\tNAME_UPPER varchar(100) not null,\n\t\t\t\t\t\t\t\t\tSHORT_NAME varchar(100),\n\n\t\t\t\t\t\t\t\t\tprimary key (ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_NAME (\n\t\t\t\t\t\t\t\t\tID int NOT NULL IDENTITY (1, 1),\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID char(2) NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_ID int NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME varchar(100) NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME_UPPER varchar(100) NOT NULL,\n\t\t\t\t\t\t\t\t\tSHORT_NAME varchar(100)\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_NAME PRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_NAME(\n\t\t\t\t\t\t\t\t\tID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID CHAR(2 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_ID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME VARCHAR2(100 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME_UPPER VARCHAR2(100 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tSHORT_NAME VARCHAR2(100 CHAR),\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)"));
$locationNameTableExists = true;
}
if ($DBType == 'oracle' && $locationNameTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOC_NAME'", true)->fetch()) {
$DB->query("CREATE SEQUENCE SQ_B_SALE_LOC_NAME INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOC_NAME_INSERT\n\t\tBEFORE INSERT\n\t\tON B_SALE_LOC_NAME\n\t\tFOR EACH ROW\n\t\tBEGIN\n\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\tSELECT SQ_B_SALE_LOC_NAME.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\tEND IF;\n\t\tEND;");
// OK
}
if ($locationNameTableExists) {
if (!$DB->IndexExists('b_sale_loc_name', array('NAME_UPPER'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_NAME_NAME_U ON b_sale_loc_name (NAME_UPPER)");
// OK: oracle, mssql
}
if (!$DB->IndexExists('b_sale_loc_name', array('LOCATION_ID', 'LANGUAGE_ID'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_NAME_LI_LI ON b_sale_loc_name (LOCATION_ID, LANGUAGE_ID)");
// OK: oracle, mssql
}
}
if (!$locationExternalServiceTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_ext_srv(\n\t\t\t\t\t\t\t\t\tID int not null auto_increment,\n\t\t\t\t\t\t\t\t\tCODE varchar(100) not null,\n\n\t\t\t\t\t\t\t\t\tprimary key (ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_EXT_SRV(\n\t\t\t\t\t\t\t\t\tID int NOT NULL IDENTITY (1, 1),\n\t\t\t\t\t\t\t\t\tCODE varchar(100) NOT NULL\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_EXT_SRV PRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_EXT_SRV(\n\t\t\t\t\t\t\t\t\tID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tCODE VARCHAR2(100 CHAR) NOT NULL,\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)"));
$locationExternalServiceTableExists = true;
}
if ($DBType == 'oracle' && $locationExternalServiceTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOC_EXT_SRV'", true)->fetch()) {
$DB->query("CREATE SEQUENCE SQ_B_SALE_LOC_EXT_SRV INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOC_EXT_SRV_INSERT\n\t\tBEFORE INSERT\n\t\tON B_SALE_LOC_EXT_SRV\n\t\tFOR EACH ROW\n\t\tBEGIN\n\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\tSELECT SQ_B_SALE_LOC_EXT_SRV.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\tEND IF;\n\t\tEND;");
// OK
}
if (!$locationExternalTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_ext(\n\t\t\t\t\t\t\t\t\tID int not null auto_increment,\n\t\t\t\t\t\t\t\t\tSERVICE_ID int not null,\n\t\t\t\t\t\t\t\t\tLOCATION_ID int not null,\n\t\t\t\t\t\t\t\t\tXML_ID varchar(100) not null,\n\n\t\t\t\t\t\t\t\t\tprimary key (ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_EXT(\n\t\t\t\t\t\t\t\t\tID int NOT NULL IDENTITY (1, 1),\n\t\t\t\t\t\t\t\t\tSERVICE_ID int NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_ID int NOT NULL,\n\t\t\t\t\t\t\t\t\tXML_ID varchar(100) NOT NULL\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_EXT PRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_EXT(\n\t\t\t\t\t\t\t\t\tID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tSERVICE_ID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_ID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tXML_ID VARCHAR2(100 CHAR) NOT NULL,\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)"));
$locationExternalTableExists = true;
}
if ($DBType == 'oracle' && $locationExternalTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOC_EXT'", true)->fetch()) {
$DB->query("CREATE SEQUENCE SQ_B_SALE_LOC_EXT INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOC_EXT_INSERT\n\t\tBEFORE INSERT\n\t\tON B_SALE_LOC_EXT\n\t\tFOR EACH ROW\n\t\tBEGIN\n\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\tSELECT SQ_B_SALE_LOC_EXT.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\tEND IF;\n\t\tEND;");
// OK
}
if ($locationExternalTableExists && !$DB->IndexExists('b_sale_loc_ext', array('LOCATION_ID', 'SERVICE_ID'))) {
$DB->query("CREATE INDEX IX_B_SALE_LOC_EXT_LID_SID ON b_sale_loc_ext (LOCATION_ID, SERVICE_ID)");
// OK: oracle, mssql
}
if (!$locationTypeTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_type(\n\t\t\t\t\t\t\t\t\tID int not null auto_increment,\n\t\t\t\t\t\t\t\t\tCODE varchar(30) not null,\n\t\t\t\t\t\t\t\t\tSORT int default '100',\n\n\t\t\t\t\t\t\t\t\tprimary key (ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE(\n\t\t\t\t\t\t\t\t\tID int NOT NULL IDENTITY (1, 1),\n\t\t\t\t\t\t\t\t\tCODE varchar(30) NOT NULL,\n\t\t\t\t\t\t\t\t\tSORT int\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_TYPE PRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_TYPE(\n\t\t\t\t\t\t\t\t\tID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tCODE VARCHAR2(30 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tSORT NUMBER(18) DEFAULT '100',\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)"));
$updater->query(array("MSSQL" => "ALTER TABLE B_SALE_LOC_TYPE ADD CONSTRAINT DF_B_SALE_LOC_TYPE_SORT DEFAULT '100' FOR SORT"));
$locationTypeTableExists = true;
}
if ($DBType == 'oracle' && $locationTypeTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOC_TYPE'", true)->fetch()) {
$DB->query("CREATE SEQUENCE SQ_B_SALE_LOC_TYPE INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOC_TYPE_INSERT\n\t\tBEFORE INSERT\n\t\tON B_SALE_LOC_TYPE\n\t\tFOR EACH ROW\n\t\tBEGIN\n\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\tSELECT SQ_B_SALE_LOC_TYPE.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\tEND IF;\n\t\tEND;");
// OK
}
if (!$locationTypeNameTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_type_name(\n\t\t\t\t\t\t\t\t\tID int not null auto_increment,\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID char(2) not null,\n\t\t\t\t\t\t\t\t\tNAME varchar(100) not null,\n\t\t\t\t\t\t\t\t\tTYPE_ID int not null,\n\n\t\t\t\t\t\t\t\t\tprimary key (ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(\n\t\t\t\t\t\t\t\t\tID int NOT NULL IDENTITY (1, 1),\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID char(2) NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME varchar(100) NOT NULL,\n\t\t\t\t\t\t\t\t\tTYPE_ID int NOT NULL\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_TYPE_NAME PRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(\n\t\t\t\t\t\t\t\t\tID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tLANGUAGE_ID CHAR(2 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tNAME VARCHAR2(100 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tTYPE_ID NUMBER(18) NOT NULL,\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (ID)\n\t\t\t\t\t\t\t\t)"));
$locationTypeNameTableExists = true;
}
if ($DBType == 'oracle' && $locationTypeNameTableExists && !$DB->query("select * from USER_OBJECTS where OBJECT_TYPE = 'SEQUENCE' and OBJECT_NAME = 'SQ_B_SALE_LOC_TYPE_NAME'", true)->fetch()) {
$DB->query("CREATE SEQUENCE SQ_B_SALE_LOC_TYPE_NAME INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE NOORDER");
// OK
$DB->query("CREATE OR REPLACE TRIGGER B_SALE_LOC_TYPE_NAME_INSERT\n\t\tBEFORE INSERT\n\t\tON B_SALE_LOC_TYPE_NAME\n\t\tFOR EACH ROW\n\t\tBEGIN\n\t\t\tIF :NEW.ID IS NULL THEN\n\t\t\t\tSELECT SQ_B_SALE_LOC_TYPE_NAME.NEXTVAL INTO :NEW.ID FROM dual;\n\t\t\tEND IF;\n\t\tEND;");
// OK
}
if ($locationTypeNameTableExists) {
if (!$DB->IndexExists('b_sale_loc_type_name', array('TYPE_ID', 'LANGUAGE_ID'))) {
$DB->query('CREATE INDEX IX_B_SALE_LOC_TYPE_NAME_TI_LI ON b_sale_loc_type_name (TYPE_ID, LANGUAGE_ID)');
// OK: oracle, mssql
}
}
if (!$locationLoc2SiteTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_2site(\n\t\t\t\t\t\t\t\t\tLOCATION_ID int not null,\n\t\t\t\t\t\t\t\t\tSITE_ID char(2) not null,\n\t\t\t\t\t\t\t\t\tLOCATION_TYPE char(1) not null default 'L',\n\n\t\t\t\t\t\t\t\t\tprimary key (SITE_ID, LOCATION_ID, LOCATION_TYPE)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_2SITE(\n\t\t\t\t\t\t\t\t\tLOCATION_ID int NOT NULL,\n\t\t\t\t\t\t\t\t\tSITE_ID char(2) NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_TYPE char(1) NOT NULL\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_2SITE PRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_2SITE(\n\t\t\t\t\t\t\t\t\tLOCATION_ID NUMBER(18) NOT NULL,\n\t\t\t\t\t\t\t\t\tSITE_ID CHAR(2 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tLOCATION_TYPE CHAR(1 CHAR) DEFAULT 'L' NOT NULL,\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)\n\t\t\t\t\t\t\t\t)"));
$updater->query(array("MSSQL" => "ALTER TABLE B_SALE_LOC_2SITE ADD CONSTRAINT DF_B_SALE_LOC_2SITE DEFAULT 'L' FOR LOCATION_TYPE"));
}
if (!$locationDefaul2SiteTableExists) {
$updater->query(array("MySQL" => "create table b_sale_loc_def2site(\n\t\t\t\t\t\t\t\t\tLOCATION_CODE varchar(100) not null,\n\t\t\t\t\t\t\t\t\tSITE_ID char(2) not null,\n\t\t\t\t\t\t\t\t\tSORT int default '100',\n\n\t\t\t\t\t\t\t\t\tprimary key (LOCATION_CODE, SITE_ID)\n\t\t\t\t\t\t\t\t)", "MSSQL" => "CREATE TABLE B_SALE_LOC_DEF2SITE(\n\t\t\t\t\t\t\t\t\tLOCATION_CODE varchar(100) NOT NULL,\n\t\t\t\t\t\t\t\t\tSITE_ID char(2) NOT NULL,\n\t\t\t\t\t\t\t\t\tSORT int\n\n\t\t\t\t\t\t\t\t\tCONSTRAINT PK_B_SALE_LOC_DEF2SITE PRIMARY KEY (LOCATION_CODE, SITE_ID)\n\t\t\t\t\t\t\t\t)", "Oracle" => "CREATE TABLE B_SALE_LOC_DEF2SITE(\n\t\t\t\t\t\t\t\t\tLOCATION_CODE VARCHAR2(100 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tSITE_ID CHAR(2 CHAR) NOT NULL,\n\t\t\t\t\t\t\t\t\tSORT NUMBER(18) DEFAULT '100',\n\n\t\t\t\t\t\t\t\t\tPRIMARY KEY (LOCATION_CODE, SITE_ID)\n\t\t\t\t\t\t\t\t)"));
$updater->query(array("MSSQL" => "ALTER TABLE B_SALE_LOC_DEF2SITE ADD CONSTRAINT DF_B_SALE_LOC_DEF2SITE_SORT DEFAULT '100' FOR SORT"));
}
// move tax and delivery to the new relation field: code
if ($tax2LocationTableExists && $DB->query("select LOCATION_ID from b_sale_tax2location WHERE 1=0", true)) {
$DB->query('delete from b_sale_tax2location where LOCATION_ID is null');
// OK: oracle, mssql // useless records to be deleted
if (!$DB->query("select LOCATION_CODE from b_sale_tax2location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_tax2location ADD LOCATION_CODE varchar(100) NOT NULL", "MSSQL" => "ALTER TABLE B_SALE_TAX2LOCATION ADD LOCATION_CODE varchar(100) default '' NOT NULL", "Oracle" => "ALTER TABLE B_SALE_TAX2LOCATION ADD LOCATION_CODE VARCHAR2(100 CHAR) default '' NOT NULL"));
}
$DB->query('update b_sale_tax2location set LOCATION_CODE = LOCATION_ID');
// OK: oracle, mssql
if ($DBType == 'mssql') {
$DB->query('ALTER TABLE b_sale_tax2location DROP CONSTRAINT PK_B_SALE_TAX2LOCATION');
} else {
$DB->query('ALTER TABLE b_sale_tax2location DROP PRIMARY KEY');
}
// OK: oracle
$DB->query('ALTER TABLE b_sale_tax2location DROP COLUMN LOCATION_ID');
// OK: oracle, mssql
$DB->query('ALTER TABLE b_sale_tax2location ADD CONSTRAINT PK_B_SALE_TAX2LOCATION PRIMARY KEY (TAX_RATE_ID, LOCATION_CODE, LOCATION_TYPE)');
// OK: oracle, mssql
}
if ($delivery2LocationTableExists && $DB->query("select LOCATION_ID from b_sale_delivery2location WHERE 1=0", true)) {
$DB->query('delete from b_sale_delivery2location where LOCATION_ID is null');
// OK: oracle, mssql // useless records to be deleted
if (!$DB->query("select LOCATION_CODE from b_sale_delivery2location WHERE 1=0", true)) {
$updater->query(array("MySQL" => "ALTER TABLE b_sale_delivery2location ADD LOCATION_CODE varchar(100) NOT NULL", "MSSQL" => "ALTER TABLE B_SALE_DELIVERY2LOCATION ADD LOCATION_CODE varchar(100) default '' NOT NULL", "Oracle" => "ALTER TABLE B_SALE_DELIVERY2LOCATION ADD LOCATION_CODE VARCHAR2(100 CHAR) default '' NOT NULL"));
}
$DB->query('update b_sale_delivery2location set LOCATION_CODE = LOCATION_ID');
// OK: oracle, mssql
if ($DBType == 'mssql') {
$DB->query('ALTER TABLE b_sale_delivery2location DROP CONSTRAINT PK_B_SALE_DELIVERY2LOCATION');
} else {
$DB->query('ALTER TABLE b_sale_delivery2location DROP PRIMARY KEY');
}
// OK: oracle
$DB->query('ALTER TABLE b_sale_delivery2location DROP COLUMN LOCATION_ID');
// OK: oracle, mssql
$DB->query('ALTER TABLE b_sale_delivery2location ADD CONSTRAINT PK_B_SALE_DELIVERY2LOCATION PRIMARY KEY (DELIVERY_ID, LOCATION_CODE, LOCATION_TYPE)');
// OK: oracle, mssql
}
if (\COption::GetOptionString('sale', 'sale_locationpro_migrated', '') != 'Y') {
\CAdminNotify::Add(array("MESSAGE" => Loc::getMessage('SALE_LOCATION_MIGRATION_PLZ_MIGRATE_NOTIFIER', array('#ANCHOR_MIGRATE#' => '<a href="/bitrix/admin/sale_location_migration.php">', '#ANCHOR_END#' => '</a>')), "TAG" => "SALE_LOCATIONPRO_PLZ_MIGRATE", "MODULE_ID" => "SALE", "ENABLE_CLOSE" => "Y"));
}
}
}
