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

Last change on this file since 8728 was 8728, checked in by plg, 13 years ago

Happy new year 2011

Change "Piwigo - a PHP based picture gallery" into "Piwigo - a PHP based photo gallery"

  • Property svn:eol-style set to LF
File size: 9.9 KB
Line 
1<?php
2// +-----------------------------------------------------------------------+
3// | Piwigo - a PHP based photo gallery                                    |
4// +-----------------------------------------------------------------------+
5// | Copyright(C) 2008-2011 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 all the user languages
106  $all_langs=array();
107  $query='
108SELECT language, COUNT(user_id) AS count FROM '.USER_INFOS_TABLE.'
109  GROUP BY language';
110  $result = pwg_query($query);
111  while ( $row=pwg_db_fetch_assoc($result) )
112  {
113    $language = $row["language"];
114    $lang_def = explode('.', $language);
115    if ( count($lang_def)==2 )
116    {
117      $new_lang = $lang_def[0];
118      $charset = strtolower($lang_def[1]);
119    }
120    else
121    {
122      $new_lang = 'en_UK';
123      $charset = 'iso-8859-1';
124    }
125    $all_langs[$language] = array(
126      'count' => $row['count'],
127      'new_lang' => $new_lang,
128      'charset' => $charset,
129      );
130    $upgrade_log .= ">>user_lang\t".$language."\t".$row['count']."\n";
131  }
132  $upgrade_log .= "\n";
133
134
135// +-----------------------------------------------------------------------+
136// get admin charset
137  include(PHPWG_ROOT_PATH . 'include/config_default.inc.php');
138  @include(PHPWG_ROOT_PATH. 'local/config/config.inc.php');
139  $admin_charset='iso-8859-1';
140  $query='
141SELECT language FROM '.USER_INFOS_TABLE.'
142  WHERE user_id='.$conf['webmaster_id'];
143  $result = pwg_query($query);
144  if (pwg_db_num_rows($result)==0)
145  {
146    $query='
147SELECT language FROM '.USER_INFOS_TABLE.'
148  WHERE status="webmaster" and adviser="false"
149  LIMIT 1';
150    $result = pwg_query($query);
151  }
152
153  if ( $row=pwg_db_fetch_assoc($result) )
154  {
155    $admin_charset = $all_langs[$row['language']]['charset'];
156  }
157  $upgrade_log .= ">>admin_charset\t".$admin_charset."\n";
158
159
160// +-----------------------------------------------------------------------+
161// get mysql version and structure of tables
162  $mysql_version = mysql_get_server_info();
163  $upgrade_log .= ">>mysql_ver\t".$mysql_version."\n";
164
165  $all_tables = array();
166  $query = 'SHOW TABLES LIKE "'.$prefixeTable.'%"';
167  $result = pwg_query($query);
168  while ( $row=pwg_db_fetch_row($result) )
169  {
170    array_push($all_tables, $row[0]);
171  }
172
173  $all_tables_definition = array();
174  foreach( $all_tables as $table)
175  {
176    $query = 'SHOW FULL COLUMNS FROM '.$table;
177    $result = pwg_query($query);
178    $field_definitions=array();
179    while ( $row=pwg_db_fetch_assoc($result) )
180    {
181      if ( !isset($row['Collation']) or $row['Collation']=='NULL' )
182        continue;
183      array_push($field_definitions, $row);
184    }
185    $all_tables_definition[$table] = $field_definitions;
186  }
187
188// +-----------------------------------------------------------------------+
189// calculate the result and convert the tables
190
191//tables that can be converted without going through binary (they contain only ascii data)
192  $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');
193  $safe_tables=array_flip($safe_tables);
194
195  $pwg_charset = 'iso-8859-1';
196  $db_charset = 'latin1';
197  $db_collate = '';
198  if ( version_compare($mysql_version, '4.1', '<') )
199  { // below 4.1 no charset support
200    $upgrade_log .= "< conversion\tnothing\n";
201  }
202  elseif ($admin_charset=='iso-8859-1')
203  {
204    $pwg_charset = 'utf-8';
205    $db_charset = 'utf8';
206    foreach($all_tables as $table)
207    {
208      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
209      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
210      pwg_query($query);
211    }
212    $upgrade_log .= "< conversion\tchange utf8\n";
213  }
214/*ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name; (or change column character set)
215
216Warning: 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:
217
218ALTER TABLE t1 CHANGE c1 c1 BLOB;
219ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;
220*/
221  elseif ( $admin_charset=='utf-8')
222  {
223    $pwg_charset = 'utf-8';
224    $db_charset = 'utf8';
225    foreach($all_tables as $table)
226    {
227      if ( !isset($safe_tables[ substr($table, strlen($prefixeTable)) ]) )
228        upgrade65_change_table_to_blob($table, $all_tables_definition[$table] );
229      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
230      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
231      pwg_query($query);
232    }
233    $upgrade_log .= "< conversion\tchange binary\n";
234    $upgrade_log .= "< conversion\tchange utf8\n";
235  }
236  elseif ( $admin_charset=='iso-8859-2'/*Central European*/)
237  {
238    $pwg_charset = 'utf-8';
239    $db_charset = 'utf8';
240    foreach($all_tables as $table)
241    {
242      if ( !isset($safe_tables[ substr($table, strlen($prefixeTable)) ]) )
243      {
244        upgrade65_change_table_to_blob($table, $all_tables_definition[$table] );
245        upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'latin2' );
246      }
247      upgrade65_change_table_to_charset($table, $all_tables_definition[$table], 'utf8' );
248      $query = 'ALTER TABLE '.$table.' DEFAULT CHARACTER SET utf8';
249      pwg_query($query);
250    }
251    $upgrade_log .= "< conversion\tchange binary\n";
252    $upgrade_log .= "< conversion\tchange latin2\n";
253    $upgrade_log .= "< conversion\tchange utf8\n";
254  }
255
256
257// +-----------------------------------------------------------------------+
258// changes to write in database.inc.php
259  array_push($mysql_changes,
260'define(\'PWG_CHARSET\', \''.$pwg_charset.'\');
261define(\'DB_CHARSET\',  \''.$db_charset.'\');
262define(\'DB_COLLATE\',  \'\');'
263  );
264
265  foreach ($all_langs as $old_lang=>$lang_data)
266  {
267    $query='
268  UPDATE '.USER_INFOS_TABLE.' SET language="'.$lang_data['new_lang'].'"
269    WHERE language="'.$old_lang.'"';
270    pwg_query($query);
271  }
272
273  define('PWG_CHARSET', $pwg_charset);
274  define('DB_CHARSET',  $db_charset);
275  define('DB_COLLATE',  '');
276
277  if ( version_compare(mysql_get_server_info(), '4.1.0', '>=') and DB_CHARSET!='' )
278  {
279    pwg_query('SET NAMES "'.DB_CHARSET.'"');
280  }
281
282  echo $upgrade_log;
283  $fp = @fopen( PHPWG_ROOT_PATH.'upgrade65.log', 'w' );
284  if ($fp)
285  {
286    @fputs($fp, $upgrade_log, strlen($upgrade_log));
287    @fclose($fp);
288  }
289
290echo
291"\n"
292.'"'.$upgrade_description.'"'.' ended'
293."\n"
294;
295}
296else
297{
298  echo 'PWG_CHARSET already defined - nada';
299}
300?>
Note: See TracBrowser for help on using the repository browser.