Minde/SQL/xml import

/*
2009-03-03, Mindaugas Jackunas, minde@mysql.lt

naudojimas:
shell> mysql -u user veyton_db_pavadinimas < xt_lt_import.sql

Viskas veikia su salyga, kad
a) lenteles turi prefiksa "xt_", pvz "xt_languages" ir pan. -> cia TODO kad buti galima konfiginti ateityje;
b) reikia pagal poreikius pamodifikuoti kelia iki xml failo, kuris nusistato is karto po sio komentaro;

Egzistuoja tam tikri reikalavimai MySQL serveriui,
kuris turi buti nezemesnes negu 5.1 versijos (kad palaikyti SP)
ir leisti naudotis LOAD_FILE() funkcija (cia vartotojo privilegijos).

Taip pat reikia atkreipti demesi, kad scripto pradzioje yra dropinama SP pavadinimu "tmp_do_import"
ir visi duomenys is lenteliu `xt_languages` bei `xt_language_content` kur kalba yra 'lt'.
*/

SET @path_to_xml := '/home/mindjack/lt.xml';

DROP PROCEDURE IF EXISTS tmp_do_import;

DELETE
FROM `xt_languages`
WHERE code = 'lt';

DELETE
FROM `xt_language_content`
WHERE language_code = 'lt';

DELIMITER //

CREATE PROCEDURE tmp_do_import(IN xml_filename TEXT)
BEGIN
	SET @xml_file := LOAD_FILE(xml_filename);
	INSERT INTO `xt_languages`(
		`name`,
		`code`,
		`image`,
		`sort_order`,
		`language_charset`,
		`default_currency`,
		`font`,
		`font_size`,
		`font_position`
	)VALUES(
		ExtractValue(@xml_file, 'xtcommerce_language/name/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/code/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/image/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/sort_order/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/language_charset/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/default_currency/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/font/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/font_size/text()'),
		ExtractValue(@xml_file, 'xtcommerce_language/font_position/text()')
	);
	
	SET @language_row_count := ExtractValue(@xml_file, 'COUNT(xtcommerce_language/phrase)');
	SET @language_row_index := 0;
	
	WHILE @language_row_index < @language_row_count DO
		
		SET @language_row_index := @language_row_index + 1;
		
		SET @language_row := CONCAT('xtcommerce_language/phrase[' , @language_row_index , ']');
		
		INSERT INTO `xt_language_content`(
				`language_code`,
				`language_key`,
				`language_value`,
				`class`,
				`plugin_key`
			)VALUES(
				'lt',
				ExtractValue(@xml_file, CONCAT(@language_row, '/language_key/text()')),
				ExtractValue(@xml_file, CONCAT(@language_row, '/language_value/text()')),
				ExtractValue(@xml_file, CONCAT(@language_row, '/class/text()')),
				ExtractValue(@xml_file, CONCAT(@language_row, '/plugin_key/text()'))
		);
	END WHILE;
END;

//

DELIMITER ;

CALL tmp_do_import(@path_to_xml);

DROP PROCEDURE IF EXISTS tmp_do_import;