-- This is included by gregslist.sql
-- First, create a temporary table, t_temp_interests, and fill it from
-- t_gregslist.
SELECT "Create interest" AS "COMMENT";
DROP TABLE t_temp_interests;
CREATE TABLE t_temp_interests
(
	id				INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
	interests	VARCHAR(128)
) AS
	SELECT interests FROM t_gregslist WHERE interests IS NOT NULL
	GROUP BY interests
	ORDER BY interests;
SELECT * FROM t_temp_interests;


-- --------------------------------------------------------------------------
-- Then, alter the columns on the fly to add separate ones for each interest
-- (Listing the result will show NULLs.)
ALTER TABLE t_temp_interests
    ADD COLUMN interest1 VARCHAR(64),
    ADD COLUMN interest2 VARCHAR(64),
    ADD COLUMN interest3 VARCHAR(64);
SELECT id, interest1, interest2, interest3, interests FROM t_temp_interests;

-- --------------------------------------------------------------------------
-- Now, update the new columns with information from the old, single column
-- "interests", page 290 (solution page 341).
UPDATE t_temp_interests SET interest1 = SUBSTRING_INDEX( interests, ',', 1 );
SELECT interest1, interest2, interest3, interests FROM t_temp_interests;
UPDATE t_temp_interests
	SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest1 ) - 1 ) ) );
SELECT interest1, interest2, interest3, interests FROM t_temp_interests;

UPDATE t_temp_interests SET interest2 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE t_temp_interests
	SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest2 ) - 1 ) ) );
SELECT interest1, interest2, interest3, interests FROM t_temp_interests;

UPDATE t_temp_interests SET interest3 = SUBSTRING_INDEX( interests, ',', 1 );
UPDATE t_temp_interests
	SET interests = TRIM( RIGHT( interests, ( LENGTH( interests ) - LENGTH( interest3 ) - 1 ) ) );
SELECT interest1, interest2, interest3, interests FROM t_temp_interests;

-- --------------------------------------------------------------------------
-- Finally, we've got interests in a separate table (t_temp_interests) with one
-- interest per column. We can select out each interest by column and add it
-- as simply 'interest' to the new table (interests). Don't include null or
-- zero-length columns.
CREATE TABLE t_interests
(
	id         INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY,
   interest   VARCHAR(64)
) AS
	SELECT interest1 AS interest FROM t_temp_interests
		WHERE interest1 IS NOT NULL AND LENGTH( interest1 ) <> 0
	GROUP BY interest ORDER by interest;

SELECT * FROM t_interests;

INSERT INTO t_interests ( interest )
	SELECT interest2 AS interest FROM t_temp_interests
		WHERE interest2 IS NOT NULL AND LENGTH( interest2 ) <> 0
	GROUP BY interest ORDER by interest;

SELECT * FROM t_interests;

INSERT INTO t_interests ( interest )
	SELECT interest3 AS interest FROM t_temp_interests
		WHERE interest3 IS NOT NULL AND LENGTH( interest3 ) <> 0
	GROUP BY interest ORDER by interest;

SELECT * FROM t_interests;

DROP TABLE t_temp_interests;

# This is built by Java code...
#CREATE TABLE t_contact_interest
#(
#	contact_id	INTEGER,
#	interest_id	INTEGER
#);
#DESC t_contact_interest;