source: trunk/install/db/65-database.php @ 2299

Last change on this file since 2299 was 2299, checked in by plg, 16 years ago

Bug fixed: as rvelices notified me by email, my header replacement script was
bugged (r2297 was repeating new and old header).

By the way, I've also removed the replacement keywords. We were using them
because it was a common usage with CVS but it is advised not to use them with
Subversion. Personnaly, it is a problem when I search differences between 2
Piwigo installations outside Subversion.

  • Property svn:eol-style set to LF
  • Property svn:keywords set to Author Date Id Revision
File size: 10.5 KB
Line 
1<?php
2// +-----------------------------------------------------------------------+
3// | Piwigo - a PHP based picture gallery                                  |
4// +-----------------------------------------------------------------------+
5// | Copyright(C) 2008      Piwigo Team                  http://piwigo.org |
6// | Copyright(C) 2003-2008 PhpWebGallery Team    http://phpwebgallery.net |
7// | Copyright(C) 2002-2003 Pierrick LE GALL   http://le-gall.net/pierrick |
8// +-----------------------------------------------------------------------+
9// | This program is free software; you can redistribute it and/or modify  |
10// | it under the terms of the GNU General Public License as published by  |
11// | the Free Software Foundation                                          |
12// |                                                                       |
13// | This program is distributed in the hope that it will be useful, but   |
14// | WITHOUT ANY WARRANTY; without even the implied warranty of            |
15// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU      |
16// | General Public License for more details.                              |
17// |                                                                       |
18// | You should have received a copy of the GNU General Public License     |
19// | along with this program; if not, write to the Free Software           |
20// | Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, |
21// | USA.                                                                  |
22// +-----------------------------------------------------------------------+
23
24if (!defined('PHPWG_ROOT_PATH'))
25{
26  die('Hacking attempt!');
27}
28
29function upgrade65_change_table_to_blob($table, $field_definitions)
30{
31  $types = array('varchar'  => 'varbinary',
32    'text' => 'blob',
33    'mediumtext' => 'mediumblob',
34    'longtext' => 'longblob');
35
36  $changes=array();
37  foreach( $field_definitions as $row)
38  {
39    if ( !isset($row['Collation']) or $row['Collation']=='NULL' )
40      continue;
41    list ($type) = explode('(', $row['Type']);
42    if (!isset($types[$type]))
43      continue; // no need
44    $binaryType = preg_replace('/'. $type .'/i', $types[$type], $row['Type'] );
45    $changes[] = 'MODIFY COLUMN '.$row['Field'].' '.$binaryType;
46  }
47  if (count($changes))
48  {
49    $query = 'ALTER TABLE '.$table.' '.implode(', ', $changes);
50    pwg_query($query);
51  }
52}
53
54function upgrade65_change_table_to_charset($table, $field_definitions, $db_charset)
55{
56  $changes=array();
57  foreach( $field_definitions as $row)
58  {
59    if ( !isset($row['Collation']) or $row['Collation']=='NULL' )
60      continue;
61    $query = $row['Field'].' '.$row['Type'];
62    $query .= ' CHARACTER SET '.$db_charset;
63    if (strpos($row['Collation'],'_bin')!==false)
64    {
65      $query .= ' BINARY';
66    }
67    if ($row['Null']!='YES')
68    {
69      $query.=' NOT NULL';
70      if (isset($row['Default']))
71        $query.=' DEFAULT "'.addslashes($row['Default']).'"';
72    }
73    else
74    {
75      if (!isset($row['Default']))
76        $query.=' DEFAULT NULL';
77      else
78        $query.=' DEFAULT "'.addslashes($row['Default']).'"';
79    }
80
81    if ($row['Extra']=='auto_increment')
82    {
83      $query.=' auto_increment';
84    }
85    $changes[] = 'MODIFY COLUMN '.$query;
86  }
87
88  if (count($changes))
89  {
90    $query = 'ALTER TABLE `'.$table.'` '.implode(', ', $changes);
91    pwg_query($query);
92  }
93}
94
95
96$upgrade_description = 'PWG charset migration';
97// +-----------------------------------------------------------------------+
98// |                            Upgrade content                            |
99// +-----------------------------------------------------------------------+
100if ( !defined('PWG_CHARSET') )
101{
102  $upgrade_log = '';
103
104// +-----------------------------------------------------------------------+
105// load the config file
106  $config_file = PHPWG_ROOT_PATH.'include/mysql.inc.php';
107  $config_file_contents = @file_get_contents($config_file);
108  if ($config_file_contents === false)
109  {
110    die('CANNOT LOAD '.$config_file);
111  }
112  $php_end_tag = strrpos($config_file_contents, '?'.'>');
113  if ($php_end_tag === false)
114  {
115    die('CANNOT FIND PHP END TAG IN '.$config_file);
116  }
117  if (!is_writable($config_file))
118  {
119    die('FILE NOT WRITABLE '.$config_file);
120  }
121
122
123// +-----------------------------------------------------------------------+
124// load all the user languages
125  $all_langs=array();
126  $query='
127SELECT language, COUNT(user_id) AS count FROM '.USER_INFOS_TABLE.'
128  GROUP BY language';
129  $result = pwg_query($query);
130  while ( $row=mysql_fetch_assoc($result) )
131  {
132    $lang = $row["language"];
133    $lang_def = explode('.', $lang);
134    if ( count($lang_def)==2 )
135    {
136      $new_lang = $lang_def[0];
137      $charset = strtolower($lang_def[1]);
138    }
139    else
140    {
141      $new_lang = 'en_UK';
142      $charset = 'iso-8859-1';
143    }
144    $all_langs[$lang] = array(
145      'count' => $row['count'],
146      'new_lang' => $new_lang,
147      'charset' => $charset,
148      );
149    $upgrade_log .= ">>user_lang\t".$lang."\t".$row['count']."\n";
150  }
151  $upgrade_log .= "\n";
152
153
154// +-----------------------------------------------------------------------+
155// get admin charset
156  include(PHPWG_ROOT_PATH . 'include/config_default.inc.php');
157  @include(PHPWG_ROOT_PATH. 'include/config_local.inc.php');
158  $admin_charset='iso-8859-1';
159  $query='
160SELECT language FROM '.USER_INFOS_TABLE.'
161  WHERE user_id='.$conf['webmaster_id'];
162  $result = pwg_query($query);
163  if (mysql_num_rows($result)==0)
164  {
165    $query='
166SELECT language FROM '.USER_INFOS_TABLE.'
167  WHERE status="webmaster" and adviser="false"
168  LIMIT 1';
169    $result = pwg_query($query);
170  }
171
172  if ( $row=mysql_fetch_assoc($result) )
173  {
174    $admin_charset = $all_langs[$row['language']]['charset'];
175  }
176  $upgrade_log .= ">>admin_charset\t".$admin_charset."\n";
177
178
179// +-----------------------------------------------------------------------+
180// get mysql version and structure of tables
181  $mysql_version = mysql_get_server_info();
182  $upgrade_log .= ">>mysql_ver\t".$mysql_version."\n";
183
184  $all_tables = array();
185  $query = 'SHOW TABLES LIKE "'.$prefixeTable.'%"';
186  $result = pwg_query($query);
187  while ( $row=mysql_fetch_array($result) )
188  {
189    array_push($all_tables, $row[0]);
190  }
191
192  $all_tables_definition = array();
193  foreach( $all_tables as $table)
194  {
195    $query = 'SHOW FULL COLUMNS FROM '.$table;
196    $result = pwg_query($query);
197    $field_definitions=array();
198    while ( $row=mysql_fetch_array($result) )
199    {
200      if ( !isset($row['Collation']) or $row['Collation']=='NULL' )
201        continue;
202      array_push($field_definitions, $row);
203    }
204    $all_tables_definition[$table] = $field_definitions;
205  }
206
207// +-----------------------------------------------------------------------+
208// calculate the result and convert the tables
209
210//tables that can be converted without going through binary (they contain only ascii data)
211  $safe_tables=array('history','history_backup','history_summary','old_permalinks','plugins','rate','upgrade','user_cache','user_feed','user_infos','user_mail_notification', 'users', 'waiting','ws_access');
212  $safe_tables=array_flip($safe_tables);
213
214  $pwg_charset = 'iso-8859-1';
215  $db_charset = 'latin1';
216  $db_collate = '';
217  if ( version_compare($mysql_version, '4.1', '<') )
218  { // below 4.1 no charset support
219    $upgrade_log .= "< conversion\tnothing\n";
220  }
221  elseif ($admin_charset=='iso-8859-1')
222  {
223    $pwg_charset = 'utf-8';
224    $db_charset = 'utf8';
225    foreach($all_tables as $table)
226    {
227      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
228      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
229      pwg_query($query);
230    }
231    $upgrade_log .= "< conversion\tchange utf8\n";
232  }
233/*ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; (or change column character set)
234
235Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:
236
237ALTER TABLE t1 CHANGE c1 c1 BLOB;
238ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
239*/
240  elseif ( $admin_charset=='utf-8')
241  {
242    $pwg_charset = 'utf-8';
243    $db_charset = 'utf8';
244    foreach($all_tables as $table)
245    {
246      if ( !isset($safe_tables[ substr($table, strlen($prefixeTable)) ]) )
247        upgrade65_change_table_to_blob($table, $all_tables_definition[$table] );
248      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
249      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
250      pwg_query($query);
251    }
252    $upgrade_log .= "< conversion\tchange binary\n";
253    $upgrade_log .= "< conversion\tchange utf8\n";
254  }
255  elseif ( $admin_charset=='iso-8859-2'/*Central European*/)
256  {
257    $pwg_charset = 'utf-8';
258    $db_charset = 'utf8';
259    foreach($all_tables as $table)
260    {
261      if ( !isset($safe_tables[ substr($table, strlen($prefixeTable)) ]) )
262      {
263        upgrade65_change_table_to_blob($table, $all_tables_definition[$table] );
264        upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'latin2' );
265      }
266      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
267      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
268      pwg_query($query);
269    }
270    $upgrade_log .= "< conversion\tchange binary\n";
271    $upgrade_log .= "< conversion\tchange latin2\n";
272    $upgrade_log .= "< conversion\tchange utf8\n";
273  }
274
275
276// +-----------------------------------------------------------------------+
277// write the result to file and update #user_infos.language
278  $config_file_contents =
279    substr($config_file_contents, 0, $php_end_tag).'
280define(\'PWG_CHARSET\', \''.$pwg_charset.'\');
281define(\'DB_CHARSET\',  \''.$db_charset.'\');
282define(\'DB_COLLATE\',  \'\');
283'.substr($config_file_contents, $php_end_tag);
284
285  $fp = @fopen( $config_file, 'w' );
286  @fputs($fp, $config_file_contents, strlen($config_file_contents));
287  @fclose($fp);
288
289  foreach ($all_langs as $old_lang=>$lang_data)
290  {
291    $query='
292  UPDATE '.USER_INFOS_TABLE.' SET language="'.$lang_data['new_lang'].'"
293    WHERE language="'.$old_lang.'"';
294    pwg_query($query);
295  }
296
297  define('PWG_CHARSET', $pwg_charset);
298  define('DB_CHARSET',  $db_charset);
299  define('DB_COLLATE',  '');
300
301  echo $upgrade_log;
302  $fp = @fopen( PHPWG_ROOT_PATH.'upgrade65.log', 'w' );
303  if ($fp)
304  {
305    @fputs($fp, $upgrade_log, strlen($upgrade_log));
306    @fclose($fp);
307  }
308
309echo
310"\n"
311.'"'.$upgrade_description.'"'.' ended'
312."\n"
313;
314}
315else
316{
317  echo 'PWG_CHARSET already defined - nada';
318}
319?>
Note: See TracBrowser for help on using the repository browser.