Converting Data Attribute to Table Attribute Part II

Converting Data Attribute to Table Attribute Part II


As promised from my previous blog “Converting Data Attribute to Table Attribute Part I”; here is the MySQL code demonstration.

First, let us create the two tables and populate them with some data

DROP TABLE IF EXISTS users;

DROP TABLE IF EXISTS user_attribute;

CREATE TABLE users (

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(128),

first_name VARCHAR(255),

last_name VARCHAR(255),

PRIMARY KEY (`id`));

CREATE TABLE user_attribute (

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

attribute_name VARCHAR(255),

attribute_value VARCHAR(255),

user_id INT,

PRIMARY KEY (`id`));

INSERT INTO users

SELECT 1000,'Jkhar','Jasim','Khar' UNION

SELECT 2000,'Lmoor','Leda','Moor';

INSERT INTO user_attribute

SELECT 1, 'job title', 'Software Engineer', 1000 UNION

SELECT 2, 'Salary', '55000', 1000 UNION

SELECT 3, 'Department', 'R&D', 1000 UNION

SELECT 4, 'job title', 'System Administrator', 2000 UNION

SELECT 5, 'Salary', '60000', 2000 UNION

SELECT 6, 'Department', 'On Demand', 2000;

SELECT * FROM users;


SELECT * FROM user_attribute;

sp_user_attribute_fields() stored procedure;This stored procedure collects all user_attribute table data attributes (job title, Department, Salary) and add them to a holding table for later use; you will notice that I’m utilizing temp tables for this task; you could also do it with JOINs and anonymous queries without issues, but I found it easier to do it this way. dm_tm_cua_fields is the key table used here to do this task.

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_user_attribute_fields`$$

CREATE PROCEDURE `sp_user_attribute_fields`()
BEGIN

SELECT '' INTO @cols;

DROP TABLE IF EXISTS dm_tm_cua_fields;

CREATE TABLE dm_tm_cua_fields (
user_id BIGINT
,user_attribute_id BIGINT
,PRIMARY KEY (user_attribute_id));

ALTER TABLE dm_tm_cua_fields ADD INDEX `ix_user_id` (`user_id`);

INSERT INTO dm_tm_cua_fields
SELECT DISTINCT cua.user_id, cua.id
FROM user_attribute cua;

SELECT GROUP_CONCAT(DISTINCT CONCAT('`',REPLACE(attribute_name,' ','_'),'`')) INTO @cols
FROM user_attribute;

IF (CHAR_LENGTH(@cols) > 0) THEN

SET @cols = REPLACE(@cols, ',', ' varchar(255),');
SET @cols = REPLACE(@cols, ' varchar(255),', ' varchar(255), add column ');
SET @cols = CONCAT('alter table dm_tm_cua_fields add column ',@cols,' varchar(255);');

PREPARE sql_stmt FROM @cols;
EXECUTE sql_stmt;
DEALLOCATE PREPARE sql_stmt;

END IF;

SELECT '' INTO @cols;

END$$

DELIMITER ; Let’s execute the stored procedure and see its output CALL sp_user_attribute_fields(); SELECT * FROM dm_tm_cua_fields; sp_user_attribute_values() stored procedure; This stored procedure collects the values for assigned to each data attributes and user; dm_tm_cua_field_values is the key table used here for this task. DELIMITER $$ DROP PROCEDURE IF EXISTS `sp_user_attribute_values`$$ CREATE PROCEDURE `sp_user_attribute_values`() BEGIN DECLARE _column_name VARCHAR(512); DECLARE done BOOLEAN DEFAULT 0; DECLARE column_cursor CURSOR FOR SELECT column_name FROM information_schema.COLUMNS WHERE table_schema = SCHEMA() AND table_name = 'dm_tm_cua_fields' AND column_name NOT IN('user_id', 'user_attribute_id'); OPEN column_cursor; BEGIN DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END; LOOP FETCH column_cursor INTO _column_name; SET @sql_stmt = 'drop table if exists dm_tm_cua_field_values;'; PREPARE sql_stmt FROM @sql_stmt; EXECUTE sql_stmt; DEALLOCATE PREPARE sql_stmt; SET @sql_stmt = 'CREATE TABLE dm_tm_cua_field_values ( user_attribute_id int ,user_id int ,`attribute_value` varchar(255) ,key `ix_user_attribute_id` (`user_attribute_id`) ,key `ix_user_id` (`user_id`));'; PREPARE sql_stmt FROM @sql_stmt; EXECUTE sql_stmt; DEALLOCATE PREPARE sql_stmt; SET @sql_stmt = CONCAT('insert into dm_tm_cua_field_values SELECT id AS user_attribute_id, user_id, attribute_value AS `value` FROM user_attribute WHERE REPLACE(attribute_name,\' \',\'_\') = \'',_column_name,'\';'); PREPARE sql_stmt FROM @sql_stmt; EXECUTE sql_stmt; DEALLOCATE PREPARE sql_stmt; SET @sql_stmt = CONCAT('UPDATE dm_tm_cua_field_values fv INNER JOIN dm_tm_cua_fields f ON fv.user_attribute_id = f.user_attribute_id AND fv.user_id = f.user_id SET f.',_column_name,' = fv.attribute_value;'); PREPARE sql_stmt FROM @sql_stmt; EXECUTE sql_stmt; DEALLOCATE PREPARE sql_stmt; END LOOP; END; END$$ DELIMITER ; CALL sp_user_attribute_values(); SELECT * FROM dm_tm_cua_field_values;

NOTE: This table will be dropped/created for each data attribute type.

Now let’s do some messaging for the fields so we can put it all together; clone dm_tm_cua_fields into dm_tm_cua_fields_final; this will be our final holding table that will be using to join with the user table to output the required structure; after cloning it, let’s put all user_ids in it, user_id will be out key field to JOIN on in order to populate attribute values

DROP TABLE IF EXISTS dm_tm_cua_fields_final;

CREATE TABLE dm_tm_cua_fields_final

SELECT *

FROM dm_tm_cua_fields

WHERE 1 = 0;

INSERT INTO dm_tm_cua_fields_final (user_id)

SELECT DISTINCT user_id

FROM dm_tm_cua_fields;

SELECT * FROM dm_tm_cua_fields_final;

sp_organize_attribute_values() stored procedure; This procedure does the actual work of messaging fields and their values

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_organize_attribute_values`$$

CREATE PROCEDURE `sp_organize_attribute_values`()

BEGIN

DECLARE _column_name VARCHAR(512);

DECLARE done BOOLEAN DEFAULT 0;

DECLARE column_cursor CURSOR FOR

SELECT column_name

FROM information_schema.COLUMNS

WHERE table_schema = SCHEMA()

AND table_name = 'dm_tm_cua_fields'

AND column_name NOT IN('user_id', 'user_attribute_id');

OPEN column_cursor;

BEGIN

DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;

LOOP

FETCH column_cursor INTO _column_name;

SET @sql_stmt = CONCAT('

UPDATE dm_tm_cua_fields_final uff

INNER JOIN dm_tm_cua_fields uf

ON uf.user_id = uff.user_id

SET uff.',_column_name,' = uf.',_column_name,'

WHERE uf.',_column_name,' IS NOT NULL;');

PREPARE sql_stmt FROM @sql_stmt;

EXECUTE sql_stmt;

DEALLOCATE PREPARE sql_stmt;

END LOOP;

END;

END$$

DELIMITER ;

CALL sp_organize_attribute_values();

SELECT * FROM dm_tm_cua_fields_final;

Now this is our final table that shows all combined columns in one single view; this can be done in many different ways; but for the sake of this demo, I doing it the easy way.

DROP TABLE IF EXISTS new_user_attribute;

CREATE TABLE new_user_attribute

SELECT DISTINCT u.*, v.*

FROM users u

INNER JOIN dm_tm_cua_fields_final v

ON v.user_id = u.id;

ALTER TABLE new_user_attribute

DROP COLUMN user_id,

DROP COLUMN user_attribute_id;

And here is the output

SELECT * FROM new_user_attribute;

 

Have any Question or Comment?

Leave a Reply

Your email address will not be published. Required fields are marked *