Lasso Soft Inc. > Home

[sortcolumns]

Linksortcolumns
AuthorJohan Solve
CategoryDatabase
Version8.x
LicensePublic Domain
Posted13 Nov 2008
Updated13 Nov 2008
More by this author...

Description

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

Sample Usage

include('sortcolumns.lasso');

Source Code

Click the "Download" button below to retrieve a copy of this tag, including the complete documentation and sample usage shown on this page. Place the downloaded ".inc" file in your LassoStartup folder, restart Lasso, and you can begin using this tag immediately.

/*

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;

Comments

No comments

Please log in to comment

Subscribe to the LassoTalk mail list

LassoSoft Inc. > Home

 

 

©LassoSoft Inc 2015 | Web Development by Treefrog Inc | PrivacyLegal terms and Shipping | Contact LassoSoft