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")); } } }
<?php /* NOT FOR RELEASE! Add content to updater and module_updater */ include_once $_SERVER["DOCUMENT_ROOT"] . "/bitrix/modules/main/classes/general/update_class.php"; global $DB, $DBType; $updater = new CUpdater(); $updater->Init($curPath = "", $DBType, $updaterName = "", $curDir = "", "sale", "DB"); $updater->CopyFiles("install/admin", "admin"); if (!$updater->TableExists("b_sale_delivery_srv")) { $updater->Query(array("MySQL" => "create table if not exists b_sale_delivery_srv\n\t\t\t(\n\t\t\t\tID int NOT NULL AUTO_INCREMENT,\n\t\t\t\tCODE varchar(50) NULL,\n\t\t\t\tPARENT_ID int NULL,\n\t\t\t\tNAME varchar(255) NOT NULL,\n\t\t\t\tACTIVE char(1) NOT NULL,\n\t\t\t\tDESCRIPTION varchar(255) NULL,\n\t\t\t\tSORT int NOT NULL,\n\t\t\t\tLOGOTIP int NULL,\n\t\t\t\tCONFIG text NULL,\n\t\t\t\tCLASS_NAME varchar(255) NOT NULL,\n\t\t\t\tCURRENCY char(3) NOT NULL,\n\t\t\t\tprimary key (ID),\n\t\t\t\tindex IX_CODE(CODE)\n\t\t\t);")); } if (!$updater->TableExists("b_sale_delivery_rstr")) { $updater->Query(array("MySQL" => "create table if not exists b_sale_delivery_rstr\n\t\t\t(\n\t\t\t\tID int NOT NULL AUTO_INCREMENT,\n\t\t\t\tDELIVERY_ID int NOT NULL,\n\t\t\t\tSORT int DEFAULT 100,\n\t\t\t\tCLASS_NAME varchar(255) NOT NULL,\n\t\t\t\tPARAMS text,\n\t\t\t\tprimary key (ID)\n\t\t\t);")); } if (!$updater->TableExists("b_sale_delivery_es")) { $updater->Query(array("MySQL" => "create table if not exists b_sale_delivery_es\n\t\t\t(\n\t\t\t\tID int NOT NULL AUTO_INCREMENT,\n\t\t\t\tCODE varchar(50) NULL,\n\t\t\t\tNAME varchar(255) NOT NULL,\n\t\t\t\tDESCRIPTION varchar(255) NULL,\n\t\t\t\tCLASS_NAME varchar(255) NOT NULL,\n\t\t\t\tPARAMS text NULL,\n\t\t\t\tRIGHTS char(3) NOT NULL,\n\t\t\t\tDELIVERY_ID int NOT NULL,\n\t\t\t\tINIT_VALUE varchar(255) NULL,\n\t\t\t\tACTIVE char(1) NOT NULL,\n\t\t\t\tSORT int DEFAULT 100,\n\t\t\t\tprimary key (ID),\n\t\t\t\tINDEX IX_DELIVERY_ID (DELIVERY_ID)\n\n\t\t\t);")); } if (!$updater->TableExists("b_sale_order_delivery_es")) { $updater->Query(array("MySQL" => "create table if not exists b_sale_order_delivery_es\n\t\t\t(\n\t\t\t\tID INT NOT NULL AUTO_INCREMENT,\n\t\t\t\tSHIPMENT_ID INT NOT NULL,\n\t\t\t\tEXTRA_SERVICE_ID INT NOT NULL,\n\t\t\t\tVALUE VARCHAR (255) NULL,\n\t\t\t\tPRIMARY KEY (ID)\n\t\t\t);")); } if ($updater->TableExists("b_sale_delivery")) { if (!$DB->Query("SELECT CONVERTED FROM b_sale_delivery WHERE 1=0", true)) { $updater->Query(array("MySQL" => "ALTER TABLE b_sale_delivery ADD CONVERTED char(1) not null default 'N'", true)); } } if ($updater->TableExists("b_sale_delivery_handler")) { if (!$DB->Query("SELECT CONVERTED FROM b_sale_delivery_handler WHERE 1=0", true)) { $updater->Query(array("MySQL" => "ALTER TABLE b_sale_delivery_handler ADD CONVERTED char(1) not null default 'N'", true));