/*

This script will sort the column names alphabetically for all tables (or a specific table) of the specified MySQL database.
The tables are modified without affecting any data in the tables.
The primary key (if any) will be placed at the top, followed by any columns that match the criteria for prio1 and then prio2, followed by all other columns in alphabetical order.

Use this at your own risk, and always make sure to have proper backups of the MySQL database before using this script!

For MySQL only.

This is not a custom tag but a Lasso script. Rename the downloaded file to sortcolumns.lasso, edit the configuration parameters and run it in your browser

Johan Sölve 2008-11-13 revision 2

*/



auth_admin;

// Main switch
// If $dryrun is set to true, the script will output the sql statements needed to sort the columns, if you prefer to execute the sql yourself. 
// Set $dryrun to false to perform the actual sorting in the database and just output a summary (along with any errors).
var('dryrun'=true);


// configuration
var('db'='mydatabase',
	'tb'='', // set a specific table name to only sort that table
	'columnnames_prio1'=array('^moddate$'), // array of regular expressions for column names that we want sorted first, after the primary key
	'columnnames_prio2'=array, // array of regular expressions for column names that we want sorted first, after prio 1 columns
	); 

// Variable initialization
var('tables'=array,
	'columns'=array,
	'columns_prio1'=array,
	'columns_prio2'=array,
	'columns_sorted'=array,
	'columnname'=string,
	'isprio1'=false,
	'isprio2'=false,
	'create'=string,
	'create_column',
	'first_column'=string,
	'column'=string,
	'column_prev'=string,
	'sql_alter'=string,
	);

inline(-database=$db);
	'# Database name: ' + database_realname(database_name) + '
';
	$dryrun ? '# Dry run - nothing will be changed
';

	if($tb -> size);
		$tables = array($tb);
	else;
		// get table names
		inline(-maxrecords=all, 
			-sql='SHOW TABLES');
			records;
				$tables -> insert(field('Tables_in_' + database_realname(database_name)));
			/records;
		/inline;
	/if;
	
	iterate($tables, $tb);	
	'# Sorting table ' + $tb + '
';
		
		inline(-sql='SHOW CREATE TABLE `' + $tb + '`');
			$columns=array;
			$columns_prio1=array;
			$columns_prio2=array;
			$first_column=string;
			$create=field('create table');
			if($create >> 'CREATE TABLE'); // sanity check
				iterate($create -> split('\n'), $create_column);
					$create_column -> trim; 
					if($create_column -> beginswith('`'));
						// this looks like a column definition
						$create_column -> removeleading('`') & removetrailing(',');
						// extract the column name
						$columnname = ($create_column -> split('`') -> first);
						// the remainder is the column definition
						$create_column -> removeleading($columnname + '`');
						// make a pair out of the column name and column definition
						$create_column = pair($columnname = $create_column);
						// look for columns to sort at the top
						$isprio1 = false;
						$isprio2 = false;
						iterate($columnnames_prio1, $columnname);
							if($create_column -> name -> contains(regexp($columnname)));
								$isprio1=true;
								loop_abort;
							/if;
						/iterate;
						if(!$isprio1);
							// only look for prio2 if it wasn't porio1
							iterate($columnnames_prio2, $columnname);
								if($create_column -> name -> contains(regexp($columnname)));
									$isprio2=true;
									loop_abort;
								/if;
							/iterate;
						/if;
						// put the column name in one of three arrays depending on prio status
						if($isprio1);
							$columns_prio1 -> insert($create_column);
						else($isprio2);
							$columns_prio2 -> insert($create_column);
						else;
							$columns -> insert($create_column);
						/if;
					else($create_column -> beginswith('PRIMARY KEY'));
						// this is the primary key definition, keep the column name so we put it at the very top
						$first_column = $create_column -> split('`') -> get(2);
						// skip the rest of the create table
						loop_abort;
					/if;
				/iterate;
				// remove the primary key from the columns to sort
				$columns_prio1 -> removeall($first_column);
				$columns_prio2 -> removeall($first_column);
				$columns -> removeall($first_column);
				// sort the column names
				$columns_prio1 -> sort;
				$columns_prio2 -> sort;
				$columns -> sort;
				
				$columns_sorted = $columns_prio1;
				$columns_sorted -> merge($columns_prio2);
				$columns_sorted -> merge($columns);
				// initialize the sorting so the primary key will be the first
				$column_prev=$first_column;
				// loop through the rest of the column names to create alter statements
				iterate($columns_sorted, $column);
					$sql_alter = 'ALTER TABLE `' + $tb + '` MODIFY `' + $column -> name + '` ' + $column -> value 
						+ ($column_prev -> size
							? ' AFTER `' + $column_prev + '`' 
							| ' FIRST')  // if there is no primary key, the first column has no field to be placed after so put it FIRST instead
							+ ';';
					if($dryrun);
						// just show the result
						$sql_alter;
						'
';
					else;
						// execute the ALTER TABLE statement
						inline(-sql=$sql_alter);
							error_code ? '# ***** ' + error_msg + '
';
						/inline;
					/if;
					// keep track of the current column name so we know how to place the next column
					$column_prev = $column -> name;
				/iterate;
			/if;
		/inline;
		// show the result
		'# ' + ($columns_prio1 -> size + $columns_prio2 -> size + $columns -> size) + ' columns sorted
';
	/iterate;
/inline;