1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
|
/*
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) + '<br>';
$dryrun ? '# Dry run - nothing will be changed<br>';
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 + '<br>';
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;
'<br>';
else;
// execute the ALTER TABLE statement
inline(-sql=$sql_alter);
error_code ? '# ***** ' + error_msg + '<br>';
/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<br>';
/iterate;
/inline;
|
