Changeset 4781
- Timestamp:
- Jan 28, 2010, 12:30:36 PM (14 years ago)
- Location:
- trunk
- Files:
-
- 1 added
- 8 edited
- 1 copied
Legend:
- Unmodified
- Added
- Removed
-
trunk/include/calendar_base.class.php
r4398 r4781 272 272 return; 273 273 274 $sub_quer y = '';274 $sub_queries = array(); 275 275 $nb_elements = count($page['chronology_date']); 276 276 for ($i=0; $i<$nb_elements; $i++) … … 278 278 if ( 'any' === $page['chronology_date'][$i] ) 279 279 { 280 $sub_quer y .= '\'any\'';280 $sub_queries[] = '\'any\''; 281 281 } 282 282 else 283 283 { 284 $sub_query .= pwg_db_cast_to_text($this->calendar_levels[$i]['sql']); 285 } 286 if ($i<($nb_elements-1)) 287 { 288 $sub_query .= ','; 289 } 290 } 291 $query = 'SELECT '.pwg_db_concat_ws($sub_query, '-').' AS period'; 284 $sub_queries[] = pwg_db_cast_to_text($this->calendar_levels[$i]['sql']); 285 } 286 } 287 $query = 'SELECT '.pwg_db_concat_ws($sub_queries, '-').' AS period'; 292 288 $query .= $this->inner_sql .' 293 289 AND ' . $this->date_field . ' IS NOT NULL … … 297 293 $upper_items = array_from_query( $query, 'period'); 298 294 299 usort($upper_items, ' version_compare');295 usort($upper_items, 'date_compare'); 300 296 $upper_items_rank = array_flip($upper_items); 301 297 if ( !isset($upper_items_rank[$current]) ) 302 298 { 303 299 array_push($upper_items, $current);// just in case (external link) 304 usort($upper_items, ' version_compare');300 usort($upper_items, 'date_compare'); 305 301 $upper_items_rank = array_flip($upper_items); 306 302 } -
trunk/include/calendar_monthly.class.php
r4398 r4781 148 148 if (isset($date[CMONTH]) and $date[CMONTH]!=='any') 149 149 { 150 $b .= $date[CMONTH] . '-';151 $e .= $date[CMONTH] . '-';150 $b .= sprintf('%02d-', $date[CMONTH]); 151 $e .= sprintf('%02d-', $date[CMONTH]); 152 152 if (isset($date[CDAY]) and $date[CDAY]!=='any') 153 153 { 154 $b .= $date[CDAY];155 $e .= $date[CDAY];154 $b .= sprintf('%02d', $date[CDAY]); 155 $e .= sprintf('%02d', $date[CDAY]); 156 156 } 157 157 else -
trunk/include/dblayer/dblayers.inc.php
r4410 r4781 30 30 'function_available' => 'pg_connect' 31 31 ); 32 33 $dblayers['sqlite'] = array('engine' => 'SQLite', 34 'function_available' => 'sqlite_open' 35 ); 32 36 ?> -
trunk/include/dblayer/functions_mysql.inc.php
r4387 r4781 459 459 } 460 460 461 function pwg_db_concat_ws($string, $separaor) 462 { 463 return 'CONCAT_WS(\''.$separaor.'\','. $string.')'; 461 function pwg_db_concat_ws($array, $separator) 462 { 463 $string = implode($array, ','); 464 return 'CONCAT_WS(\''.$separator.'\','. $string.')'; 464 465 } 465 466 -
trunk/include/dblayer/functions_pgsql.inc.php
r4410 r4781 71 71 global $conf,$page,$debug,$t2; 72 72 73 // Log::getInstance()->debug($query);74 75 73 $start = get_moment(); 76 74 ($result = pg_query($query)) or die($query."\n<br>".pg_last_error()); … … 213 211 if (count($datas) == 0) 214 212 return; 215 // depending on the MySQL version, we use the multi table update or N update queries 213 216 214 if (count($datas) < 10) 217 { // MySQL is prior to version 4.0.4, multi table update feature is not available215 { 218 216 foreach ($datas as $data) 219 217 { … … 379 377 380 378 // List all tables 381 $query = 'SHOW TABLES LIKE \''.$prefixeTable.'%\''; 382 $result = pwg_query($query); 383 while ($row = pwg_db_fetch_assoc($result)) 384 { 385 array_push($all_tables, $row[0]); 386 } 387 388 // Repair all tables 389 $query = 'REPAIR TABLE '.implode(', ', $all_tables); 390 $mysql_rc = pwg_query($query); 391 392 // Re-Order all tables 393 foreach ($all_tables as $table_name) 394 { 395 $all_primary_key = array(); 396 397 $query = 'DESC '.$table_name.';'; 398 $result = pwg_query($query); 399 while ($row = pwg_db_fetch_assoc($result)) 400 { 401 if ($row['Key'] == 'PRI') 402 { 403 array_push($all_primary_key, $row['Field']); 404 } 405 } 406 407 if (count($all_primary_key) != 0) 408 { 409 $query = 'ALTER TABLE '.$table_name.' ORDER BY '.implode(', ', $all_primary_key).';'; 410 $mysql_rc = $mysql_rc && pwg_query($query); 411 } 412 } 379 $query = 'SELECT tablename FROM pg_tables 380 WHERE tablename like \''.$prefixeTable.'%\''; 381 382 $all_tables = array_from_query($query, 'tablename'); 413 383 414 384 // Optimize all tables 415 $query = 'OPTIMIZE TABLE '.implode(', ', $all_tables); 416 $mysql_rc = $mysql_rc && pwg_query($query); 417 if ($mysql_rc) 418 { 419 array_push( 420 $page['infos'], 421 l10n('Optimizations completed') 422 ); 423 } 424 else 425 { 426 array_push( 427 $page['errors'], 428 l10n('Optimizations errors') 429 ); 430 } 431 } 432 433 function pwg_db_concat_ws($string, $separaor) 434 { 435 return 'ARRAY_TO_STRING(ARRAY['.$string.'],\''.$separaor.'\')'; 385 foreach ($all_tables as $table) 386 { 387 $query = 'VACUUM FULL '.$table; 388 pwg_query($query); 389 } 390 array_push($page['infos'], 391 l10n('Optimizations completed') 392 ); 393 } 394 395 function pwg_db_concat_ws($array, $separator) 396 { 397 $string = implode($array, ','); 398 return 'ARRAY_TO_STRING(ARRAY['.$string.'],\''.$separator.'\')'; 436 399 } 437 400 … … 450 413 { 451 414 $typname = preg_replace('/'.$GLOBALS['prefixeTable'].'/', '', $table); 452 Log::getInstance()->debug($typname);453 415 $typname .= '_' . $field; 454 416 -
trunk/include/dblayer/functions_sqlite.inc.php
r4775 r4781 22 22 // +-----------------------------------------------------------------------+ 23 23 24 define('REQUIRED_ PGSQL_VERSION', '8.0');25 define('DB_ENGINE', ' PostgreSQL');26 27 define('DB_REGEX_OPERATOR', ' ~');24 define('REQUIRED_SQLITE_VERSION', '3.0.0'); 25 define('DB_ENGINE', 'SQLite'); 26 27 define('DB_REGEX_OPERATOR', 'REGEXP'); 28 28 define('DB_RANDOM_FUNCTION', 'RANDOM'); 29 29 … … 36 36 function pwg_db_connect($host, $user, $password, $database) 37 37 { 38 $connection_string = ''; 39 if (strpos($host,':') !== false) 40 { 41 list($host, $port) = explode(':', $host); 42 } 43 $connection_string = sprintf('host=%s', $host); 44 if (!empty($port)) 45 { 46 $connection_string .= sprintf(' port=%d', $port); 47 } 48 $connection_string .= sprintf(' user=%s password=%s dbname=%s', 49 $user, 50 $password, 51 $database); 52 $link = pg_connect($connection_string) or my_error('pg_connect', true); 38 global $conf; 39 40 $db_file = sprintf('%s/%s.db', $conf['local_data_dir'], $database); 41 42 $link = new SQLite3($db_file); 43 $link->createFunction('now', 'pwg_now', 0); 44 $link->createFunction('md5', 'md5', 1); 45 46 $link->createAggregate('std', 'pwg_std_step', 'pwg_std_finalize'); 47 $link->createFunction('regexp', 'pwg_regexp', 2); 53 48 54 49 return $link; … … 62 57 function pwg_get_db_version() 63 58 { 64 list($pg_version) = pwg_db_fetch_row(pwg_query('SHOW SERVER_VERSION;')); 65 66 return $pg_version; 59 global $pwg_db_link; 60 61 $versionInfos = $pwg_db_link->version(); 62 return $versionInfos['versionString']; 67 63 } 68 64 69 65 function pwg_query($query) 70 66 { 71 global $conf,$page,$debug,$t2; 72 73 // Log::getInstance()->debug($query); 67 global $conf,$page,$debug,$t2,$pwg_db_link; 74 68 75 69 $start = get_moment(); 76 ($result = pg_query($query)) or die($query."\n<br>".pg_last_error()); 70 71 $truncate_pattern = '`truncate(.*)`i'; 72 $insert_pattern = '`(INSERT INTO [^)]*\)\s*VALUES)(\([^)]*\))\s*,\s*(.*)`mi'; 73 74 if (preg_match($truncate_pattern, $query, $matches)) 75 { 76 $query = str_replace('TRUNCATE TABLE', 'DELETE FROM', $query); 77 $truncate_query = true; 78 ($result = $pwg_db_link->exec($query)) or die($query."\n<br>".$pwg_db_link->lastErrorMsg()); 79 } 80 elseif (preg_match($insert_pattern, $query, $matches)) 81 { 82 $base_query = substr($query, 0, strlen($matches[1])+1); 83 $values_pattern = '`\)\s*,\s*\(`'; 84 $values = preg_split($values_pattern, substr($query, strlen($matches[1])+1)); 85 $values[0] = substr($values[0], 1); 86 $values[count($values)-1] = substr($values[count($values)-1], 87 0, 88 strlen($values[count($values)-1])-1 89 ); 90 for ($n=0;$n<count($values);$n++) 91 { 92 $query = $base_query . '('. $values[$n] . ")\n;"; 93 ($result = $pwg_db_link->query($query)) 94 or die($query."\n<br>".$pwg_db_link->lastErrorMsg()); 95 } 96 } 97 else 98 { 99 ($result = $pwg_db_link->query($query)) 100 or die($query."\n<br>".$pwg_db_link->lastErrorMsg()); 101 } 77 102 78 103 $time = get_moment() - $start; … … 104 129 } 105 130 elseif ( $result!=null 106 and preg_match('/\s*INSERT|UPDATE|REPLACE|DELETE\s+/i',$query) ) 131 and preg_match('/\s*INSERT|UPDATE|REPLACE|DELETE\s+/i',$query) 132 and !isset($truncate_query)) 107 133 { 108 134 $output.= "\n".'(affected rows : '; … … 120 146 { 121 147 $query = ' 122 SELECT nextval(\''.$table.'_'.$column.'_seq\')'; 148 SELECT MAX('.$column.')+1 149 FROM '.$table; 123 150 list($next) = pwg_db_fetch_row(pwg_query($query)); 124 151 if (is_null($next)) 152 { 153 $next = 1; 154 } 125 155 return $next; 126 156 } … … 132 162 */ 133 163 134 function pwg_db_changes($result) 135 { 136 return pg_affected_rows($result); 164 function pwg_db_changes(SQLite3Result $result=null) 165 { 166 global $pwg_db_link; 167 168 return $pwg_db_link->changes(); 137 169 } 138 170 139 171 function pwg_db_num_rows($result) 140 { 141 return pg_num_rows($result);172 { 173 return $result->numColumns(); 142 174 } 143 175 144 176 function pwg_db_fetch_assoc($result) 145 177 { 146 return pg_fetch_assoc($result);178 return $result->fetchArray(SQLITE3_ASSOC); 147 179 } 148 180 149 181 function pwg_db_fetch_row($result) 150 182 { 151 return pg_fetch_row($result);183 return $result->fetchArray(SQLITE3_NUM); 152 184 } 153 185 154 186 function pwg_db_fetch_object($result) 155 187 { 156 return pg_fetch_object($result);188 return $result; 157 189 } 158 190 159 191 function pwg_db_free_result($result) 160 192 { 161 return pg_free_result($result);162 193 } 163 194 164 195 function pwg_db_real_escape_string($s) 165 196 { 166 return pg_escape_string($s); 197 global $pwg_db_link; 198 199 return $pwg_db_link->escapeString($s); 167 200 } 168 201 169 202 function pwg_db_insert_id() 170 203 { 171 // select currval('piwigo_user_id_seq'); 204 global $pwg_db_link; 205 206 return $pwg_db_link->lastInsertRowID(); 172 207 } 173 208 … … 191 226 192 227 $result = pwg_query($query); 193 while ($row = p g_fetch_assoc($result))228 while ($row = pwg_db_fetch_assoc($result)) 194 229 { 195 230 array_push($array, $row[$fieldname]); … … 213 248 if (count($datas) == 0) 214 249 return; 215 // depending on the MySQL version, we use the multi table update or N update queries 216 if (count($datas) < 10) 217 { // MySQL is prior to version 4.0.4, multi table update feature is not available 218 foreach ($datas as $data) 219 { 220 $query = ' 250 251 foreach ($datas as $data) 252 { 253 $query = ' 221 254 UPDATE '.$tablename.' 222 255 SET '; 256 $is_first = true; 257 foreach ($dbfields['update'] as $key) 258 { 259 $separator = $is_first ? '' : ",\n "; 260 261 if (isset($data[$key]) and $data[$key] != '') 262 { 263 $query.= $separator.$key.' = \''.$data[$key].'\''; 264 } 265 else 266 { 267 if ($flags & MASS_UPDATES_SKIP_EMPTY ) 268 continue; // next field 269 $query.= "$separator$key = NULL"; 270 } 271 $is_first = false; 272 } 273 if (!$is_first) 274 {// only if one field at least updated 275 $query.= ' 276 WHERE '; 223 277 $is_first = true; 224 foreach ($dbfields['update'] as $key) 225 { 226 $separator = $is_first ? '' : ",\n "; 227 228 if (isset($data[$key]) and $data[$key] != '') 278 foreach ($dbfields['primary'] as $key) 279 { 280 if (!$is_first) 229 281 { 230 $query.= $separator.$key.' = \''.$data[$key].'\'';231 232 else 282 $query.= ' AND '; 283 } 284 if ( isset($data[$key]) ) 233 285 { 234 if ($flags & MASS_UPDATES_SKIP_EMPTY ) 235 continue; // next field 236 $query.= "$separator$key = NULL"; 237 } 238 $is_first = false; 239 } 240 if (!$is_first) 241 {// only if one field at least updated 242 $query.= ' 243 WHERE '; 244 $is_first = true; 245 foreach ($dbfields['primary'] as $key) 286 $query.= $key.' = \''.$data[$key].'\''; 287 } 288 else 246 289 { 247 if (!$is_first) 248 { 249 $query.= ' AND '; 250 } 251 if ( isset($data[$key]) ) 252 { 253 $query.= $key.' = \''.$data[$key].'\''; 254 } 255 else 256 { 257 $query.= $key.' IS NULL'; 258 } 259 $is_first = false; 260 } 261 pwg_query($query); 262 } 263 } // foreach update 264 } // if mysql_ver or count<X 265 else 266 { 267 $all_fields = array_merge($dbfields['primary'], $dbfields['update']); 268 $temporary_tablename = $tablename.'_'.micro_seconds(); 269 $query = ' 270 CREATE TABLE '.$temporary_tablename.' 271 AS SELECT * FROM '.$tablename.' WHERE 1=2'; 272 273 pwg_query($query); 274 mass_inserts($temporary_tablename, $all_fields, $datas); 275 if ( $flags & MASS_UPDATES_SKIP_EMPTY ) 276 $func_set = create_function('$s, $t', 'return "$s = IFNULL(t2.$s, '.$tablename.'.$s)";'); 277 else 278 $func_set = create_function('$s', 'return "$s = t2.$s";'); 279 280 // update of images table by joining with temporary table 281 $query = ' 282 UPDATE '.$tablename.' 283 SET '. 284 implode( 285 "\n , ", 286 array_map($func_set, $dbfields['update']) 287 ).' 288 FROM '.$temporary_tablename.' AS t2 289 WHERE '. 290 implode( 291 "\n AND ", 292 array_map( 293 create_function('$s, $t', 'return "'.$tablename.'.$s = t2.$s";'), 294 $dbfields['primary'] 295 ) 296 ); 297 pwg_query($query); 298 $query = ' 299 DROP TABLE '.$temporary_tablename; 300 pwg_query($query); 290 $query.= $key.' IS NULL'; 291 } 292 $is_first = false; 293 } 294 pwg_query($query); 295 } 301 296 } 302 297 } … … 379 374 380 375 // List all tables 381 $query = 'SHOW TABLES LIKE \''.$prefixeTable.'%\''; 382 $result = pwg_query($query); 383 while ($row = pwg_db_fetch_assoc($result)) 384 { 385 array_push($all_tables, $row[0]); 386 } 387 388 // Repair all tables 389 $query = 'REPAIR TABLE '.implode(', ', $all_tables); 390 $mysql_rc = pwg_query($query); 391 392 // Re-Order all tables 376 $query = 'SELECT name FROM SQLITE_MASTER 377 WHERE name LIKE \''.$prefixeTable.'%\''; 378 379 $all_tables = array_from_query($query, 'name'); 393 380 foreach ($all_tables as $table_name) 394 381 { 395 $all_primary_key = array(); 396 397 $query = 'DESC '.$table_name.';'; 382 $query = 'VACUUM '.$table_name.';'; 398 383 $result = pwg_query($query); 399 while ($row = pwg_db_fetch_assoc($result)) 400 { 401 if ($row['Key'] == 'PRI') 402 { 403 array_push($all_primary_key, $row['Field']); 404 } 405 } 406 407 if (count($all_primary_key) != 0) 408 { 409 $query = 'ALTER TABLE '.$table_name.' ORDER BY '.implode(', ', $all_primary_key).';'; 410 $mysql_rc = $mysql_rc && pwg_query($query); 411 } 412 } 413 414 // Optimize all tables 415 $query = 'OPTIMIZE TABLE '.implode(', ', $all_tables); 416 $mysql_rc = $mysql_rc && pwg_query($query); 417 if ($mysql_rc) 418 { 419 array_push( 420 $page['infos'], 421 l10n('Optimizations completed') 422 ); 423 } 424 else 425 { 426 array_push( 427 $page['errors'], 428 l10n('Optimizations errors') 429 ); 430 } 431 } 432 433 function pwg_db_concat_ws($string, $separaor) 434 { 435 return 'ARRAY_TO_STRING(ARRAY['.$string.'],\''.$separaor.'\')'; 384 } 385 386 array_push($page['infos'], 387 l10n('Optimizations completed') 388 ); 389 } 390 391 function pwg_db_concat_ws($array, $separator) 392 { 393 $glue = sprintf(' || \'%s\' || ', $separator); 394 395 return implode($array, $glue); 436 396 } 437 397 438 398 function pwg_db_cast_to_text($string) 439 399 { 440 return 'CAST('.$string.' AS TEXT)';400 return $string; 441 401 } 442 402 … … 449 409 function get_enums($table, $field) 450 410 { 451 $typname = preg_replace('/'.$GLOBALS['prefixeTable'].'/', '', $table); 452 Log::getInstance()->debug($typname); 453 $typname .= '_' . $field; 454 455 $query = 'SELECT 456 enumlabel FROM pg_enum JOIN pg_type 457 ON pg_enum.enumtypid=pg_type.oid 458 WHERE typname=\''.$typname.'\' 459 '; 460 $result = pwg_query($query); 461 while ($row = pwg_db_fetch_assoc($result)) 462 { 463 $options[] = $row['enumlabel']; 464 } 465 466 return $options; 411 return array(); 467 412 } 468 413 … … 508 453 if ($date!='CURRENT_DATE') 509 454 { 510 $date = '\''.$date.'\' ::date';511 } 512 513 return ' ('.$date.' - \''.$period.' DAY\'::interval)::date';455 $date = '\''.$date.'\''; 456 } 457 458 return 'date('.$date.',\''.$period.' DAY\')'; 514 459 } 515 460 … … 524 469 function pwg_db_get_date_YYYYMM($date) 525 470 { 526 return ' TO_CHAR('.$date.', \'YYYYMM\')';471 return 'strftime(\'%Y%m\','.$date.')'; 527 472 } 528 473 529 474 function pwg_db_get_date_MMDD($date) 530 475 { 531 return ' TO_CHAR('.$date.', \'MMDD\')';476 return 'strftime(\'%m%d\','.$date.')'; 532 477 } 533 478 534 479 function pwg_db_get_year($date) 535 480 { 536 return ' EXTRACT(YEAR FROM'.$date.')';481 return 'strftime(\'%Y\','.$date.')'; 537 482 } 538 483 539 484 function pwg_db_get_month($date) 540 485 { 541 return ' EXTRACT(MONTH FROM'.$date.')';486 return 'strftime(\'%m\','.$date.')'; 542 487 } 543 488 544 489 function pwg_db_get_week($date, $mode=null) 545 490 { 546 return ' EXTRACT(WEEK FROM'.$date.')';491 return 'strftime(\'%W\','.$date.')'; 547 492 } 548 493 549 494 function pwg_db_get_dayofmonth($date) 550 495 { 551 return ' EXTRACT(DAY FROM'.$date.')';496 return 'strftime(\'%d\','.$date.')'; 552 497 } 553 498 554 499 function pwg_db_get_dayofweek($date) 555 500 { 556 return ' EXTRACT(DOW FROM '.$date.')::INTEGER -1';501 return 'strftime(\'%w\','.$date.')+1'; 557 502 } 558 503 559 504 function pwg_db_get_weekday($date) 560 505 { 561 return ' EXTRACT(ISODOW FROM '.$date.')::INTEGER - 1';506 return 'strftime(\'%w\','.$date.')'; 562 507 } 563 508 … … 566 511 function my_error($header, $die) 567 512 { 568 $error = '[pgsql error]'.pg_last_error()."\n"; 513 global $pwg_db_link; 514 515 $error = '[sqlite error]'.$pwg_db_link->lastErrorMsg()."\n"; 569 516 $error .= $header; 570 517 … … 578 525 } 579 526 580 527 // sqlite create functions 528 function pwg_now() 529 { 530 return date('Y-m-d H:i:s'); 531 } 532 533 function pwg_regexp($pattern, $string) 534 { 535 $pattern = sprintf('`%s`', $pattern); 536 return preg_match($pattern, $string); 537 } 538 539 function pwg_std_step(&$values, $rownumber, $value) 540 { 541 $values[] = $value; 542 543 return $values; 544 } 545 546 function pwg_std_finalize(&$values, $rownumber) 547 { 548 if (count($values)<=1) 549 { 550 return 0; 551 } 552 553 $total = 0; 554 $total_square = 0; 555 foreach ($values as $value) 556 { 557 $total += $value; 558 $total_square += pow($value, 2); 559 } 560 561 $mean = $total/count($values); 562 $var = $total_square/count($values) - pow($mean, 2); 563 564 return sqrt($var); 565 } 581 566 ?> -
trunk/include/functions_calendar.inc.php
r4398 r4781 290 290 pwg_debug('end initialize_calendar'); 291 291 } 292 293 /* 294 * callback to sort array with date comparaison 295 * 296 **/ 297 function date_compare(&$a, &$b) 298 { 299 $parts = explode('-', $a); 300 foreach ($parts as &$p) { 301 $p = (int)$p; 302 } 303 $a = implode('-', $parts); 304 $parts = explode('-', $b); 305 foreach ($parts as &$p) { 306 $p = (int)$p; 307 } 308 $b = implode('-', $parts); 309 310 return strcmp($a, $b); 311 } 292 312 ?> -
trunk/include/functions_session.inc.php
r4325 r4781 71 71 session_name($conf['session_name']); 72 72 session_set_cookie_params(0, cookie_path()); 73 register_shutdown_function('session_write_close'); 73 74 } 74 75 -
trunk/include/ws_functions.inc.php
r4685 r4781 876 876 WHERE id IN ('.implode(',',$params['image_id']).')'; 877 877 $result = pwg_query($query); 878 $affected_rows = pwg_db_ affected_rows();878 $affected_rows = pwg_db_changes(); 879 879 if ($affected_rows) 880 880 {
Note: See TracChangeset
for help on using the changeset viewer.