/* Functions and procedures, used for database conversion */

/* Returns a comma-separated list of all table columns TABLE_STR of the database DB_STR */
DROP FUNCTION IF EXISTS GET_COLUMNS;
DELIMITER $$
CREATE FUNCTION GET_COLUMNS(DB_STR TEXT, TABLE_STR TEXT)
RETURNS TEXT
BEGIN
	DECLARE STR TEXT;

	SELECT
		GROUP_CONCAT(COLUMN_NAME
			SEPARATOR ', ')
	INTO STR
	FROM
		INFORMATION_SCHEMA.COLUMNS
	WHERE
		TABLE_SCHEMA = DB_STR AND
		TABLE_NAME = TABLE_STR
	;

	RETURN STR;
END$$

/* Executes query QUERY_STR for the table TABLE_STR, having changed the key @words with the corresponding values */
DROP PROCEDURE IF EXISTS EXEC_QUERY$$
CREATE PROCEDURE EXEC_QUERY(QUERY_STR TEXT, TABLE_STR TEXT)
BEGIN
	SET @QUERY = REPLACE(QUERY_STR, '@DB1', @DB1);
	SET @QUERY = REPLACE(@QUERY,    '@DB2', @DB2);
	SET @QUERY = REPLACE(@QUERY,    '@TABLE', TABLE_STR);
	SET @QUERY = REPLACE(@QUERY,    '@COU_ID', @COU_ID);
	SET @QUERY = REPLACE(@QUERY,    '@LNG_ID', @LNG_ID);
	IF LOCATE('@COLUMNS', @QUERY) != 0 THEN
		SET @QUERY = REPLACE(@QUERY, '@COLUMNS', GET_COLUMNS(@DB2, TABLE_STR));
	END IF;

	PREPARE QUERY FROM @QUERY;
	EXECUTE QUERY;
	DEALLOCATE PREPARE QUERY;
END$$

/* Analog EXEC_QUERY, which creates before query execution the table TABLE_STR in the database @DB2 without columns,
   containing in names the line @COLUMN_FILTER */
DROP PROCEDURE IF EXISTS EXEC_QUERY2$$
CREATE PROCEDURE EXEC_QUERY2(QUERY_STR TEXT, TABLE_STR TEXT)
BEGIN
    DECLARE STR TEXT;
	
	CALL EXEC_QUERY('DROP TABLE IF EXISTS @DB2.@TABLE', TABLE_STR);
	CALL EXEC_QUERY('CREATE TABLE @DB2.@TABLE LIKE @DB1.@TABLE', TABLE_STR);

	SELECT
		GROUP_CONCAT('DROP ', COLUMN_NAME
			SEPARATOR ', ')
	INTO STR
	FROM
		INFORMATION_SCHEMA.COLUMNS
	WHERE
		TABLE_SCHEMA = @DB2 AND
		TABLE_NAME = TABLE_STR AND
		LOCATE(@COLUMN_FILTER, COLUMN_NAME) != 0
	;

	IF NOT STR IS NULL THEN
		CALL EXEC_QUERY(CONCAT('ALTER TABLE @DB2.@TABLE ', STR), TABLE_STR);
	END IF;

	CALL EXEC_QUERY(QUERY_STR, TABLE_STR);
END$$

/* Copies the table TABLE_STR from the database @DB1 into the database @DB2, having deleted from it all the columns,
 containing the line @COLUMN_FILTER */
DROP PROCEDURE IF EXISTS COPY_TABLE$$
DELIMITER $$
CREATE PROCEDURE COPY_TABLE(TABLE_STR TEXT)
BEGIN
	CALL EXEC_QUERY2('
		INSERT INTO @DB2.@TABLE
		SELECT @COLUMNS FROM @DB1.@TABLE
	', TABLE_STR);
END$$

/* Analog EXEC_QUERY2, replacing the given query with a simple tables copying,
   if @ROW_FILTER = FALSE */
DROP PROCEDURE IF EXISTS EXEC_QUERY3$$
CREATE PROCEDURE EXEC_QUERY3(QUERY_STR TEXT, TABLE_STR TEXT)
BEGIN
	IF @ROW_FILTER THEN
		CALL EXEC_QUERY2(QUERY_STR, TABLE_STR);
	ELSE
		CALL COPY_TABLE(TABLE_STR);
	END IF;
END$$

/* Analog EXEC_QUERY, executed only if @ROW_FILTER=TRUE */
DROP PROCEDURE IF EXISTS EXEC_QUERY4$$
CREATE PROCEDURE EXEC_QUERY4(QUERY_STR TEXT, TABLE_STR TEXT)
BEGIN
	IF @ROW_FILTER THEN
		CALL EXEC_QUERY(QUERY_STR, TABLE_STR);
	END IF;
END$$


/* Moves the table TABLE_STR from the database @DB2 into the database @DB1 */
DROP PROCEDURE IF EXISTS MOVE_TABLE$$
DELIMITER $$
CREATE PROCEDURE MOVE_TABLE(TABLE_STR TEXT)
BEGIN
	CALL EXEC_QUERY('DROP TABLE IF EXISTS @DB1.@TABLE', TABLE_STR);
	CALL EXEC_QUERY('ALTER TABLE @DB2.@TABLE RENAME TO @DB1.@TABLE', TABLE_STR);
END$$
DELIMITER ;