﻿/* Фильтрование данных в таблицах по языку @LNG_ID */

SET @DB1 = 'tecdoc_2012_2q_russia'; -- Конечная база данных
SET @DB2 = 'tecdoc_2012_2q_temp'; -- Временная база данных
SET @LNG_ID = 16; -- Код языка: 16 - руский; 1 - немецкий
SET @COLUMN_FILTER = NULL; -- Удалять все колонки, содержащие в названии эту строку (NULL без кавычек = не удалять)
SET @ROW_FILTER = TRUE; -- TRUE - Фильтровать строки таблиц по языку; FALSE - Просто скопировать все строки

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		ACO_GRA_LNG_ID=@LNG_ID
', 'ALI_COORDINATES'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (ACO_GRA_ID, ACO_ALI_ART_ID, ACO_ALI_SORT, ACO_SORT)
', 'ALI_COORDINATES'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		ACO_GRA_LNG_ID=255
', 'ALI_COORDINATES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		CDS_LNG_ID=@LNG_ID
', 'COUNTRY_DESIGNATIONS'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP KEY CDS_ID,
	ADD PRIMARY KEY (CDS_ID)
', 'COUNTRY_DESIGNATIONS'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		CDS_LNG_ID=255
', 'COUNTRY_DESIGNATIONS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		DES_LNG_ID=@LNG_ID
', 'DESIGNATIONS'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (DES_ID)
', 'DESIGNATIONS'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		DES_LNG_ID=255
', 'DESIGNATIONS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT DISTINCT
		@COLUMNS
	FROM @DB1.@TABLE, @DB2.DESIGNATIONS
	WHERE
		TEX_ID=DES_TEX_ID
', 'DES_TEXTS'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT DISTINCT
		@COLUMNS
	FROM @DB1.@TABLE, @DB2.COUNTRY_DESIGNATIONS
	WHERE
		TEX_ID=CDS_TEX_ID
', 'DES_TEXTS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		ETK_LNG_ID=@LNG_ID
', 'ERR_TRACK_KEY_VALUES'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (ETK_TAB_NR,ETK_KEY)
', 'ERR_TRACK_KEY_VALUES'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		ETK_LNG_ID=255
', 'ERR_TRACK_KEY_VALUES'
);

SET @COLUMN_FILTER_TEMP = @COLUMN_FILTER;
SET @COLUMN_FILTER = NULL;

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		GRA_LNG_ID=@LNG_ID
', 'GRAPHICS'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP PRIMARY KEY,
	ADD PRIMARY KEY(GRA_ID)
', 'GRAPHICS'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		GRA_LNG_ID=255
', 'GRAPHICS'
);

SET @COLUMN_FILTER = @COLUMN_FILTER_TEMP;

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		LNG_ID=@LNG_ID OR LNG_ID=255
', 'LANGUAGES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		MTL_LNG_ID=@LNG_ID
', 'MOD_TYP_LOOKUP'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP KEY MTL_TYP_ID,
	ADD PRIMARY KEY (MTL_TYP_ID, MTL_SEARCH_TEXT)
', 'MOD_TYP_LOOKUP'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		MTL_LNG_ID=255
', 'MOD_TYP_LOOKUP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		STL_LNG_ID=@LNG_ID
', 'STR_LOOKUP'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP KEY STL_LNG_ID,
	ADD PRIMARY KEY(STL_STR_ID, STL_GA_ID)
', 'STR_LOOKUP'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		STL_LNG_ID=255
', 'STR_LOOKUP'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SLO_LNG_ID=@LNG_ID
', 'SUPPLIER_LOGOS'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP KEY SLO_SUP_ID,
	ADD UNIQUE KEY SLO_SUP_ID (SLO_SUP_ID)
', 'SUPPLIER_LOGOS'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		SLO_LNG_ID=255
', 'SUPPLIER_LOGOS'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		TMO_LNG_ID=@LNG_ID
', 'TEXT_MODULES'
);

CALL EXEC_QUERY('
	ALTER TABLE @DB2.@TABLE
	DROP PRIMARY KEY,
	ADD PRIMARY KEY (TMO_ID)
', 'TEXT_MODULES'
);

CALL EXEC_QUERY('
	INSERT IGNORE INTO @DB2.@TABLE
	SELECT
		@COLUMNS
	FROM @DB1.@TABLE
	WHERE
		TMO_LNG_ID=255
', 'TEXT_MODULES'
);

CALL EXEC_QUERY3('
	INSERT INTO @DB2.@TABLE
	SELECT DISTINCT
		@COLUMNS
	FROM @DB1.@TABLE, @DB2.TEXT_MODULES
	WHERE
		TMT_ID=TMO_TMT_ID
', 'TEXT_MODULE_TEXTS'
);

/* Заменяем одноимённые таблицы в исходной базе @DB1 новыми таблицами из базы @DB2 */

CALL MOVE_TABLE('ALI_COORDINATES');
CALL MOVE_TABLE('COUNTRY_DESIGNATIONS');
CALL MOVE_TABLE('DESIGNATIONS');
CALL MOVE_TABLE('DES_TEXTS');
CALL MOVE_TABLE('ERR_TRACK_KEY_VALUES');
CALL MOVE_TABLE('GRAPHICS');
CALL MOVE_TABLE('LANGUAGES');
CALL MOVE_TABLE('MOD_TYP_LOOKUP');
CALL MOVE_TABLE('STR_LOOKUP');
CALL MOVE_TABLE('SUPPLIER_LOGOS');
CALL MOVE_TABLE('TEXT_MODULE_TEXTS');
CALL MOVE_TABLE('TEXT_MODULES');
