Add new columns in one table based on new entries in another table in mysql

Question

I have 2 tables: sessions and assignments. This assignments table has a column called scriptname with strings as values. The sessions table has column names equal to scriptname+ the columns id, uid, timein and timeout. As I add new instances to assignments I get new values in the scriptname column which I want to add as new columns to sessions with default values of 0. How do I do this?

What I currently do is drop the table and create a new table based on the scriptname column. The problem is of course I lose all my data.

DROP TABLE sessions;
SET SESSION group_concat_max_len = 1000000;
SELECT
  CONCAT(
    'CREATE TABLE sessions (',
    GROUP_CONCAT(DISTINCT
      CONCAT(scriptname, ' BOOL DEFAULT 0')
      SEPARATOR ','),
    ');')
FROM
  assignments
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

ALTER TABLE sessions
ADD COLUMN `timeout` timestamp not null FIRST,
ADD COLUMN `timein` timestamp not null DEFAULT CURRENT_TIMESTAMP FIRST,
ADD COLUMN `uid` VARCHAR(128) not null FIRST,
ADD COLUMN `id` INT(11) AUTO_INCREMENT PRIMARY KEY not null FIRST;

I hope somebody can help me out as I'm really not an expert on sql! Thanks in advance.

Answer 1

Consider, instead, adding rows to a table. Then, later, "pivot" to make columns while displaying the output.

Adding columns is quite clumsy, as you are finding. Adding too many columns will eventually hit some limit. Adding rows is essentially unlimited.

Pivoting is discussed many times in this forum, so I won't repeat those discussions here.

Answer 2

Weak data model here. It's not for RDBMS - there, you're going to enforce the restrictions over your dataset through relations.

E.g., in your case, through making the scriptname a foreign key in your sessions table. Thus, you'll be having multiple records in sessions table per what you consider one record of your type, each containing 1 scriptname value (which stands for column name in your model), and the associated value (the field value in your table). Then you can kind of "rotate" and assemble that two columns into a row on the client side.

Otherwise, if you're firm about keeping that denormilised non-rigid data structure at any price - you better opt out for some column oriented/multimodel DB, like Apache Cassandra. There you can alter your columnfamily/table structure right on the fly, no need to stop/drop/recreate/run again.

Answer 3

You must know the list of the newly added columns somehow. E.g you may have a column called date_added where you have recorded when the new session was added.

If you do not have such column then you can get the list of columns from INFORMATION_SCHEMA.COLUMNS. Then all you need is to create an ALTER TABLE script like this (this will add new columns to the table for the sessions added in April):

SET SESSION group_concat_max_len = 1000000;
SELECT
  CONCAT(
    'ALTER TABLE sessions ',
    GROUP_CONCAT(DISTINCT
      CONCAT('ADD COLUMN ', scriptname, ' BOOL DEFAULT 0')
      SEPARATOR ','),
    ';')
FROM
  assignments
WHERE scriptname NOT IN (
  SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = 'sessions'
  AND table_schema = '<your schema>')
INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;

-- this should generate this:
-- ALTER TABLE sessions 
--  ADD COLUMN blah1 BOOL DEFAULT 0, 
--  ADD COLUMN blah2 BOOL DEFAULT 0;

However, I must comment that you need to re-design how you store your data. The way you store it is not the optimal. You need to store the sessions in rows, not in column like you do. If you need to present the data this way you can build a query to pivot the data.

Answer 4

As others have said, dynamically generating an ever-growing list of columns is clearly not what you should be doing. SQL is just not designed for that.

Where it me, based on the limited information I have about how you're using this, I would have a third table (with two columns: "scriptname" and "value") which could be joined with the sessions table. That way you can have any number of assignment per session (which it sounds like is your objective).

Answer 5

You can set a trigger on insertion of new entries in another table.Like --

CREATE TRIGGER `ADD_COLUMN_BEFORE_INSERT` BEFORE INSERT ON `table_1`
FOR EACH ROW
 BEGIN
   ALTER TABLE table_2........;
 END