MySQL Bugs: #8381: Reordering columns would be nice

Bug #8381	Reordering columns would be nice
Submitted:	9 Feb 2005 1:15	Modified:	6 Mar 2009 0:44
Reporter:	Justin Patrin	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S4 (Feature request)
Version:	1.1.5, 1.2.12	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
When editing a table's structure it would be nice to be able to recorder the columns and insert a new column in any place. I miss this funcitonality from Mysql CC already (although Mysql CC had its own problem of not allowing a new column at the end which was also bad).

When I drag a column in the Edit Table window it looks like it's moving (I can see a blue bar which moves between the other columns) but it doesn't do anything.

If you could add "Insert column" to the right mouse button menu and have it insert the column before the clicked on column this would also be nice. However, allowing for moving columns would be enough.

How to repeat:
Try to drag a column in the edit table screen.

I can second this. Can anyone confirm if this functionality is coming? It's annoying to have to add a column in the 'edit table window' and then copy the SQL, modify the AFTER 'column' clause and run the SQL in QUERY browser manually.

Yummy feature.  1 vote from me.

I submit to this feature request too. It's a very simple thing to add and it's very useful.

Thank you for a reasonable feature request.

I would also like to have this feature, if only I could edit my tables within Mysql-query-browser... But it's still impossible to edit my tables as they are all uppercase named :(

Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Query Browser into MySQL Workbench. We won't add this feature request anymore.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/

That's great....if the workbench *ran* in OSX. It's quite frustrating for these programs to be continually rewritten and abandoned instead of polished so that they are useful and bug free.

procedure\function to generate ALTER TABLE sql from QB..

DELIMITER $$

DROP PROCEDURE IF EXISTS `SwapCol` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SwapCol`(IN xDatabase TEXT, IN xTable TEXT, IN xColumn TEXT, IN xIndex INT)
BEGIN

-- USAGE SwapCol('MySchema', 'MyTable', 'MyColumn', NewPos)
-- WEBLINK http://bugs.mysql.com/bug.php?id=8381

DECLARE zCOUNT INT DEFAULT 0;
DECLARE zSTATE INT DEFAULT 0;

DECLARE zCOL00 VARCHAR(64); #column
DECLARE zCOL01 VARCHAR(64); #type
DECLARE zCOL02 VARCHAR(64); #collation
DECLARE zCOL03 VARCHAR(64); #null
DECLARE zCOL04 VARCHAR(64); #key
DECLARE zCOL05 VARCHAR(64); #default
DECLARE zCOL06 VARCHAR(64); #extra
DECLARE zCOL07 VARCHAR(64); #priviledges
DECLARE zCOL08 VARCHAR(64); #comment
DECLARE zCOL09 VARCHAR(64); #ordinal
DECLARE zLASTF VARCHAR(64); #

DECLARE zSQL LONGTEXT DEFAULT '';

  #USE xDatabase;
	DECLARE zCURSA CURSOR FOR
  SELECT COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, PRIVILEGES, COLUMN_COMMENT,
  IF(COLUMN_NAME = xColumn, xIndex + 0.1, ORDINAL_POSITION) AS Ex1
  FROM information_schema.`COLUMNS`
  WHERE TABLE_SCHEMA = xDatabase AND TABLE_NAME = xTable
  ORDER BY Ex1;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET zSTATE = 1;
	OPEN zCURSA;

	SET zSQL = 'alter table `';
  # 0- 1st line
  SET zSQL = CONCAT(zSQL, xDatabase);
  SET zSQL = CONCAT(zSQL, '`.`');
  SET zSQL = CONCAT(zSQL, xTable);
  SET zSQL = CONCAT(zSQL, '`\r\n');

	REPEAT
	FETCH zCURSA INTO zCOL00, zCOL01, zCOL02, zCOL03, zCOL04, zCOL05, zCOL06, zCOL07, zCOL08, zCOL09;
	
		IF NOT zSTATE THEN

    # 1 - field
	  SET zSQL = CONCAT(zSQL, 'CHANGE `');
	  SET zSQL = CONCAT(zSQL, zCOL00);
	  SET zSQL = CONCAT(zSQL, '` `');
	  SET zSQL = CONCAT(zSQL, zCOL00);
	  SET zSQL = CONCAT(zSQL, '` ');

    # 2 - type
	  SET zSQL = CONCAT(zSQL, zCOL01);

      # 3 - null
			IF zCOL03 = 'NO' THEN
			SET zSQL = CONCAT(zSQL, ' NOT NULL ');
			ELSE
			SET zSQL = CONCAT(zSQL, ' NULL ');
			END IF;

      # 4 - auto_increment\extra
			IF zCOL06 <> '' THEN
			SET zSQL = CONCAT(zSQL, zCOL06);
			SET zSQL = CONCAT(zSQL, ' ');
			END IF;

      # 5 - collation
			IF zCOL02 <> '' THEN
			SET zSQL = CONCAT(zSQL, 'COLLATE ');
			SET zSQL = CONCAT(zSQL, zCOL02);
			SET zSQL = CONCAT(zSQL, ' ');
			END IF;

      # 6 - default
			IF zCOL05 <> null THEN
			SET zSQL = CONCAT(zSQL, 'DEFAULT  ');
			SET zSQL = CONCAT(zSQL, zCOL05);
			SET zSQL = CONCAT(zSQL, ' ');
			END IF;

      # 7 - comment
			IF zCOL08 <> '' THEN
			SET zSQL = CONCAT(zSQL, 'COMMENT  ');
			SET zSQL = CONCAT(zSQL, zCOL08);
			SET zSQL = CONCAT(zSQL, ' ');
			END IF;

      # 8 - after
			IF zLASTF <> '' THEN
			SET zSQL = CONCAT(zSQL, 'AFTER `');
			SET zSQL = CONCAT(zSQL, zLASTF);
			SET zSQL = CONCAT(zSQL, '`,\r\n');
      ELSE
			SET zSQL = CONCAT(zSQL, 'FIRST,\r\n');
			END IF;

		END IF;

  SET zCOUNT = zCOUNT + 1;
	SET zLASTF = zCOL00;
	UNTIL zSTATE END REPEAT;
  # 9 - truncate last comma
	SET zSQL = LEFT(zSQL, LENGTH(zSQL)-3);
	SELECT zSQL;

END $$

DELIMITER ;