codeiq-shufflers

perl convert.pl < shufflers.txt > shufflers-for-mysql.txt
mysql -N test < solve.sql > result.txt

convert.pl

$, = "\t";
$\ = "\n";

while (<>) {
	my ($ids, $names) = map [ split ], split /=/;
	foreach (0 .. $#$ids) {
		print $., $ids->[$_], $names->[$_];
	}
}

solve.sql

CREATE TABLE rules (
	number INT NOT NULL,
	id VARCHAR(32) NOT NULL,
	name VARCHAR(32) NOT NULL,
	INDEX (number),
	INDEX (id),
	INDEX (name),
	UNIQUE (number, id),
	UNIQUE (number, name)
);

LOAD DATA LOCAL INFILE 'shufflers-for-mysql.txt'
INTO TABLE rules
LINES TERMINATED BY '\r\n';

SELECT CONCAT(GROUP_CONCAT(id ORDER BY id SEPARATOR ' '), ' = ', name) AS result FROM (
SELECT id, GROUP_CONCAT(name ORDER BY name SEPARATOR ' ') AS name FROM (
-- ----------------------------------------------------------------------------
SELECT
	X.id, X.name
FROM
	-- Intersects (Possibles)
	(
		SELECT DISTINCT XI.id, XN.name
		FROM rules AS XI INNER JOIN rules AS XN ON XI.number = XN.number
	) AS X
WHERE
	NOT EXISTS (
		SELECT
			*
		FROM
			-- Rule numbers
			(SELECT DISTINCT number FROM rules) AS R
		WHERE
			-- Exclusives
			X.id IN (SELECT RI.id FROM rules AS RI WHERE RI.number = R.number)
			XOR
			X.name IN (SELECT RN.name FROM rules AS RN WHERE RN.number = R.number)
	)
-- ----------------------------------------------------------------------------
) AS A GROUP BY id
) AS A GROUP BY name ORDER BY id, name;

DROP TABLE rules;