static function updateDBSchema()
{
global $DB;
$updater = new CUpdater();
$updater->Init($curPath = "", 'mysql', $updaterName = "", $curDir = "", "sale", "DB");
// table existence check
$locationTableExists = $updater->TableExists("b_sale_location");
if($locationTableExists) // module might not be installed, but tables may exist
{
$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", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL", //OK // oracle allows to add not-null column only with default specified
));
}
// 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", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LEFT_MARGIN NUMBER(18)", // OK
));
}
// 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", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD RIGHT_MARGIN NUMBER(18)", // OK
));
}
$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'", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD PARENT_ID NUMBER(18) DEFAULT '0'", // OK
));
}
// 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'", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD DEPTH_LEVEL NUMBER(18) DEFAULT '1'", // OK
));
}
// 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", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD TYPE_ID NUMBER(18)", // OK
));
}
// 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)", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LATITUDE NUMBER(8,6)", // OK
));
}
// 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)", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION ADD LONGITUDE NUMBER(9,6)", // OK
));
}
// dropping not-nulls
$DB->query("ALTER TABLE b_sale_location MODIFY COUNTRY_ID int NULL");
// 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", // OK
"Oracle" => "ALTER TABLE B_SALE_LOCATION_GROUP ADD CODE VARCHAR2(100 CHAR) default '' NOT NULL", //OK // oracle allows to add not-null column only with default specified
));
}
// 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 (
ID int not null auto_increment,
LANGUAGE_ID char(2) not null,
LOCATION_ID int not null,
NAME varchar(100) not null,
NAME_UPPER varchar(100) not null,
SHORT_NAME varchar(100),
primary key (ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_NAME (
ID int NOT NULL IDENTITY (1, 1),
LANGUAGE_ID char(2) NOT NULL,
LOCATION_ID int NOT NULL,
NAME varchar(100) NOT NULL,
NAME_UPPER varchar(100) NOT NULL,
SHORT_NAME varchar(100)
CONSTRAINT PK_B_SALE_LOC_NAME PRIMARY KEY (ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_NAME(
ID NUMBER(18) NOT NULL,
LANGUAGE_ID CHAR(2 CHAR) NOT NULL,
LOCATION_ID NUMBER(18) NOT NULL,
NAME VARCHAR2(100 CHAR) NOT NULL,
NAME_UPPER VARCHAR2(100 CHAR) NOT NULL,
SHORT_NAME VARCHAR2(100 CHAR),
PRIMARY KEY (ID)
)", // OK
));
$locationNameTableExists = true;
}
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(
ID int not null auto_increment,
CODE varchar(100) not null,
primary key (ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_EXT_SRV(
ID int NOT NULL IDENTITY (1, 1),
CODE varchar(100) NOT NULL
CONSTRAINT PK_B_SALE_LOC_EXT_SRV PRIMARY KEY (ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_EXT_SRV(
ID NUMBER(18) NOT NULL,
CODE VARCHAR2(100 CHAR) NOT NULL,
PRIMARY KEY (ID)
)", // OK
));
$locationExternalServiceTableExists = true;
}
if (!$locationExternalTableExists)
{
$updater->query(array(
"MySQL" => "create table b_sale_loc_ext(
ID int not null auto_increment,
SERVICE_ID int not null,
LOCATION_ID int not null,
XML_ID varchar(100) not null,
primary key (ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_EXT(
ID int NOT NULL IDENTITY (1, 1),
SERVICE_ID int NOT NULL,
LOCATION_ID int NOT NULL,
XML_ID varchar(100) NOT NULL
CONSTRAINT PK_B_SALE_LOC_EXT PRIMARY KEY (ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_EXT(
ID NUMBER(18) NOT NULL,
SERVICE_ID NUMBER(18) NOT NULL,
LOCATION_ID NUMBER(18) NOT NULL,
XML_ID VARCHAR2(100 CHAR) NOT NULL,
PRIMARY KEY (ID)
)", // OK
));
$locationExternalTableExists = true;
}
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(
ID int not null auto_increment,
CODE varchar(30) not null,
SORT int default '100',
primary key (ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE(
ID int NOT NULL IDENTITY (1, 1),
CODE varchar(30) NOT NULL,
SORT int
CONSTRAINT PK_B_SALE_LOC_TYPE PRIMARY KEY (ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_TYPE(
ID NUMBER(18) NOT NULL,
CODE VARCHAR2(30 CHAR) NOT NULL,
SORT NUMBER(18) DEFAULT '100',
PRIMARY KEY (ID)
)", // OK
));
$updater->query(array(
"MSSQL" => "ALTER TABLE B_SALE_LOC_TYPE ADD CONSTRAINT DF_B_SALE_LOC_TYPE_SORT DEFAULT '100' FOR SORT", // OK
));
$locationTypeTableExists = true;
}
if(!$locationTypeNameTableExists)
{
$updater->query(array(
"MySQL" => "create table b_sale_loc_type_name(
ID int not null auto_increment,
LANGUAGE_ID char(2) not null,
NAME varchar(100) not null,
TYPE_ID int not null,
primary key (ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(
ID int NOT NULL IDENTITY (1, 1),
LANGUAGE_ID char(2) NOT NULL,
NAME varchar(100) NOT NULL,
TYPE_ID int NOT NULL
CONSTRAINT PK_B_SALE_LOC_TYPE_NAME PRIMARY KEY (ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_TYPE_NAME(
ID NUMBER(18) NOT NULL,
LANGUAGE_ID CHAR(2 CHAR) NOT NULL,
NAME VARCHAR2(100 CHAR) NOT NULL,
TYPE_ID NUMBER(18) NOT NULL,
PRIMARY KEY (ID)
)", // OK
));
$locationTypeNameTableExists = true;
}
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(
LOCATION_ID int not null,
SITE_ID char(2) not null,
LOCATION_TYPE char(1) not null default 'L',
primary key (SITE_ID, LOCATION_ID, LOCATION_TYPE)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_2SITE(
LOCATION_ID int NOT NULL,
SITE_ID char(2) NOT NULL,
LOCATION_TYPE char(1) NOT NULL
CONSTRAINT PK_B_SALE_LOC_2SITE PRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_2SITE(
LOCATION_ID NUMBER(18) NOT NULL,
SITE_ID CHAR(2 CHAR) NOT NULL,
LOCATION_TYPE CHAR(1 CHAR) DEFAULT 'L' NOT NULL,
PRIMARY KEY (SITE_ID, LOCATION_ID, LOCATION_TYPE)
)", // OK
));
$updater->query(array(
"MSSQL" => "ALTER TABLE B_SALE_LOC_2SITE ADD CONSTRAINT DF_B_SALE_LOC_2SITE DEFAULT 'L' FOR LOCATION_TYPE", // OK
));
}
if (!$locationDefaul2SiteTableExists)
{
$updater->query(array(
"MySQL" => "create table b_sale_loc_def2site(
LOCATION_CODE varchar(100) not null,
SITE_ID char(2) not null,
SORT int default '100',
primary key (LOCATION_CODE, SITE_ID)
)",
"MSSQL" => "CREATE TABLE B_SALE_LOC_DEF2SITE(
LOCATION_CODE varchar(100) NOT NULL,
SITE_ID char(2) NOT NULL,
SORT int
CONSTRAINT PK_B_SALE_LOC_DEF2SITE PRIMARY KEY (LOCATION_CODE, SITE_ID)
)", // OK
"Oracle" => "CREATE TABLE B_SALE_LOC_DEF2SITE(
LOCATION_CODE VARCHAR2(100 CHAR) NOT NULL,
SITE_ID CHAR(2 CHAR) NOT NULL,
SORT NUMBER(18) DEFAULT '100',
PRIMARY KEY (LOCATION_CODE, SITE_ID)
)", // OK
));
$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)) // OK: oracle, mssql
{
$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", // OK // oracle allows to add not-null column only with default specified
));
}
$DB->query('update b_sale_tax2location set LOCATION_CODE = LOCATION_ID'); // OK: oracle, mssql
$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)) // OK: oracle
{
$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", // OK
"Oracle" => "ALTER TABLE B_SALE_DELIVERY2LOCATION ADD LOCATION_CODE VARCHAR2(100 CHAR) default '' NOT NULL", // OK // oracle allows to add not-null column only with default specified
));
}
$DB->query('update b_sale_delivery2location set LOCATION_CODE = LOCATION_ID'); // OK: oracle, mssql
$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') // CSaleLocation::isLocationProMigrated()
{
CAdminNotify::Add(
array(
"MESSAGE" => Loc::getMessage('SALE_LOCATION_MIGRATION_PLZ_MIGRATE_NOTIFIER', array(
'#ANCHOR_MIGRATE#' => '',
'#ANCHOR_END#' => ''
)),
"TAG" => "SALE_LOCATIONPRO_PLZ_MIGRATE",
"MODULE_ID" => "SALE",
"ENABLE_CLOSE" => "Y"
)
);
}
}
}