ona  18.1.1
About: OpenNetAdmin provides a database managed inventory of your IP network (with Web and CLI interface).
  Fossies Dox: ona-18.1.1.tar.gz  ("inofficial" and yet experimental doxygen-generated source code documentation)  

collate-convert.php
Go to the documentation of this file.
1 <?php
2 //
3 // MP: Provided with ONA to help users convert older databases. Use at your own risk and backup your data!
4 // If you are running multiple contexts, you must run this for each database instance.
5 // Dont forget to update the username,password settings below.
6 //
7 // original script (v1.0) by/from: http://www.phpwact.org/php/i18n/utf-8/mysql
8 // improved/modified (v1.04) by Bogdan http://bogdan.org.ua/
9 // this script will output all queries needed to change all fields/tables to a different collation
10 // it is HIGHLY suggested you take a MySQL dump/backup prior to running any of the generated queries
11 // this code is provided AS IS and without any warranty
12 // add text/plain header when used not as a CLI script; PHP CLI SAPI shouldn't output headers
13 //
15 // DB login information - *modify before use*
16 $username = 'user';
17 $password = 'pass';
18 $database = 'ona';
19 $host = 'localhost';
21 
22 //-- usually, there is nothing to modify below this line --//
23 header("Content-Type: text/plain");
24 
25 // precaution
26 //die("Make a backup of your MySQL database, then remove this line from the code!\n");
27 
28 set_time_limit(0);
29 
30 // collation you want to change to:
31 $convert_to = 'utf8_general_ci';
32 
33 // character set of new collation:
35 
36 // show TABLE alteration queries?
38 
39 // show FIELD alteration queries?
41 
42 mysql_connect($host, $username, $password);
43 mysql_select_db($database);
44 
45 $rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());
46 
47 while ($row_tables = mysql_fetch_row($rs_tables)) {
48 
49  $table = mysql_real_escape_string($row_tables[0]);
50 
51  // Alter table collation
52  // ALTER TABLE `account` DEFAULT CHARACTER SET utf8
54  echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set;\n");
55 
56  $rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
57 
58  while ( $row = mysql_fetch_assoc($rs) ) {
59  if ( $row['Collation'] == '' || $row['Collation'] == $convert_to )
60  continue;
61 
62  // Is the field allowed to be null?
63  if ( $row['Null'] == 'YES' )
64  $nullable = ' NULL ';
65  else
66  $nullable = ' NOT NULL';
67 
68  // Does the field default to null, a string, or nothing?
69  if ( $row['Default'] === NULL )
70  $default = $row['Null'] == 'YES' ? " DEFAULT NULL " : "";
71  elseif ( $row['Default'] != '' )
72  $default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
73  else
74  $default = '';
75 
76  // Don't alter int columns as they do not have a collation anyway and it buggers up the extra parameters
77  if (strpos($row[Type], 'int') !== false)
78  $show_alter_field = false;
79  else
80  $show_alter_field = true;
81 
82  // Alter field collation:
83  // ALTER TABLE `tab` CHANGE `fiel` `fiel` CHAR( 5 ) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL
84  if ($show_alter_field) {
85  $field = mysql_real_escape_string($row['Field']);
86  echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable $default;\n";
87  }
88  }
89 }
90 
91 ?>
$character_set
$character_set
Definition: collate-convert.php:34
$rs_tables
$rs_tables
Definition: collate-convert.php:45
$rs
if(isset($_REQUEST['nrows'])) else $rs
Definition: server.php:94
$username
$username
Definition: collate-convert.php:16
$convert_to
$convert_to
Definition: collate-convert.php:31
$show_alter_field
$show_alter_field
Definition: collate-convert.php:40
$database
$database
Definition: collate-convert.php:18
$password
$password
Definition: collate-convert.php:17
$host
$host
Definition: collate-convert.php:19
$show_alter_table
$show_alter_table
Definition: collate-convert.php:37