Changeset 507

Timestamp:

Feb 15, 2013, 10:12:08 PM (11 years ago)

Author:

chronos

Message:

• Upraveno: Použita novější databázová třída Database, která běží přes PDO.
• Přidáno: Pokud databáze nesouhlasí k verzi systému, je zobrazeno chybové hlášení.
• Přidáno: Instalační skript admin/install.php, přes který je možno nainstalovat čistou databázi a provádět automatickou aktualizaci databáze.
• Upraveno: Konkrétní rozdílové SQL aktualizace jsou nyní zapsány přímo v kódu v souboru UpdateTrace namísto textových SQL skriptů. To umožňuje provádět také dynamickou aktualizaci dle obsahu dat v tabulkách. Zde pro získání seznamu překladových tabulek.
• Opraveno: Některé části kódu nepodporovaly nulový počet překladových skupin nebo jazyků.

Location:

trunk

Files:

• action.php (modified) (2 diffs)
• admin/install.php (added)
• dictionary.php (modified) (2 diffs)
• img_level.php (modified) (2 diffs)
• includes/Database.php (modified) (6 diffs)
• includes/Page.php (modified) (1 diff)
• includes/Update.php (added)
• includes/error.php (modified) (2 diffs)
• includes/global.php (modified) (3 diffs)
• includes/system.php (modified) (2 diffs)
• index.php (modified) (2 diffs)
• log.php (modified) (3 diffs)
• serverlist.php (modified) (1 diff)
• sql/UpdateTrace.php (added)
• sql/updates/506.sql (added)
• statistic.php (modified) (4 diffs)
• team.php (modified) (4 diffs)

trunk/action.php

@@ -269 +269 @@
 
   $Output = '<h3>Novinky</h3>'.$PageList['Output']; 
-  if($User->Licence(LICENCE_USER)) $Output .= ' <a href="admin.php?action=addnew">Vložit</a>';
+  if($User->Licence(LICENCE_ADMIN)) $Output .= ' <a href="admin/?action=addnew">Vložit</a>';
   $Output .= '<div class="shoutbox">';
   $DbResult = $System->Database->query('SELECT `News`.`Time`, `News`.`Text`, `User`.`Name` AS `User` FROM `News` JOIN `User` ON `User`.`Id`=`News`.`User` ORDER BY `News`.`Time` DESC '.$PageList['SQLLimit']);
@@ -278 +278 @@
 }
 
-$GroupId = LoadGroupIdParameter();
-$Table = $TranslationTree[$GroupId]['TablePrefix'];
+if(array_key_exists('group', $_GET)) $GroupId = LoadGroupIdParameter();
+  else $GroupId = 1;
+//$Table = $TranslationTree[$GroupId]['TablePrefix'];
 $Action = '';
 if(array_key_exists('action', $_GET)) $Action = $_GET['action'];

trunk/dictionary.php

@@ -53 +53 @@
   {
     $_SESSION['language'] = $User->Language;
-  } else $_SESSION['language'] = 1;
+  } else $_SESSION['language'] = 2;
 }
 if(array_key_exists('language', $_GET)) $_SESSION['language'] = LoadLanguageIdParameter();
@@ -81 +81 @@
       foreach($TranslationTree[$GroupId]['Items'] as $Index => $TextItem)
         $Text .= ' '.$Line[$TextItem['Column']];
-          $Output .= WriteTranslatNames($Text,$mode);
+          $Output .= WriteTranslatNames($Text, $mode);
     }
   }

trunk/img_level.php

@@ -111 +111 @@
     // Build TranslatedCount query
     $TranslatedCount = '(';
-    foreach($TranslationTree as $Group)
+    if(count($TranslationTree) > 0)
+    {
+          foreach($TranslationTree as $Group)
       if($Group['TablePrefix'] != '')
       {
@@ -117 +119 @@
         $TranslatedCount .= 'COALESCE('.$Count.', 0) + ';
       }
-    $TranslatedCount = substr($TranslatedCount, 0, -3).')';
+      $TranslatedCount = substr($TranslatedCount, 0, -3).')';
+    } else $TranslatedCount = 0;
     
     $xp = GetXPFromTranslation($LineUser['ID']); 

trunk/includes/Database.php

@@ -2 +2 @@
 
 // Extended database class
-// Date: 2010-01-29
+// Date: 2011-11-25
 
-class Database extends mysqli
+
+class DatabaseResult
 {
-  var $HostName = 'localhost';
-  var $UserName;
-  var $Password;
-  var $Schema;
-  var $Charset = 'utf8';
-  var $Prefix = '';
-  var $ShowSQLQuery = false;
-  var $ShowSQLError = false;
-
-  function open()
+  var $PDOStatement;
+  var $num_rows = 0;
+  
+  function fetch_assoc()
   {
-    parent::connect($this->HostName, $this->UserName, $this->Password, $this->Schema);
-    $this->charset($this->Charset);
+    return($this->PDOStatement->fetch(PDO::FETCH_ASSOC));
+  }
+  
+  function fetch_array()
+  {
+    return($this->PDOStatement->fetch(PDO::FETCH_BOTH));
   }
 
+  function fetch_row()
+  {
+    return($this->PDOStatement->fetch(PDO::FETCH_NUM));
+  }
+}
+
+class Database
+{
+  var $Prefix = '';
+  var $Functions = array('NOW()', 'CURDATE()', 'CURTIME()', 'UUID()');
+  var $Type = 'mysql';  // mysql, pgsql
+  var $PDO;
+  var $Error = '';
+  var $insert_id;
+  var $LastQuery = '';
+  var $ShowSQLError = false;
+  var $ShowSQLQuery = false;
+  
+  function __construct($Host, $User, $Password, $Database)
+  {    
+    if($this->Type == 'mysql') $ConnectionString = 'mysql:host='.$Host.';dbname='.$Database;
+      else if($this->Type == 'pgsql') $ConnectionString = 'pgsql:dbname='.$Database.';host='.$Host;
+      else $ConnectionString = '';
+    $this->PDO = new PDO($ConnectionString, $User, $Password);
+  }
+  
+  function select_db($Database)
+  {
+    $this->query('USE `'.$Database.'`');
+  }
+  
   function query($Query)
   {
-  
-    if($this->ShowSQLQuery)
+    $this->LastQuery = $Query;
+    if($this->ShowSQLQuery == true) 
+      echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.'</div>'."\n");
+    $Result = new DatabaseResult();
+    $Result->PDOStatement = $this->PDO->query($Query);
+    if($Result->PDOStatement)
     {
-      if(isset($_SERVER['REMOTE_ADDR'])) echo('<div style="border-bottom-width: 1px; border-bottom-style: solid; padding-bottom: 3px; padding-top: 3px; font-size: 12px; font-family: Arial;">'.$Query.'</div>'); 
-      else echo($Query."\n");
+      $Result->num_rows = $Result->PDOStatement->rowCount();
+    } else
+    {
+      $this->Error = $this->PDO->errorInfo();
+      $this->Error = $this->Error[2];
+      if(($this->Error != '') and ($this->ShowSQLError == true)) 
+        echo('<div><strong>SQL Error: </strong>'.$this->Error.'<br />'.$Query.'</div>');
     }
-    $Result = parent::query($Query);
-    if(($this->error != '') and ($this->ShowSQLError))
-    {
-      if(isset($_SERVER['REMOTE_ADDR'])) echo('<div><strong>SQL Error: </strong>'.$this->error.'<br />'.$Query.'</div>');
-      echo('SQL Error: '.$this->error.' '.$Query."\n");
-    }
-
+    $this->insert_id = $this->PDO->lastInsertId();
     return($Result);  
   }
 
   function select($Table, $What = '*', $Condition = 1)
-  {
+  {   
     return($this->query('SELECT '.$What.' FROM `'.$this->Prefix.$Table.'` WHERE '.$Condition));  
   }
@@ -56 +89 @@
     {
       $Name .= ',`'.$Key.'`';
+      if(!in_array($Value, $this->Functions)) 
+      {
+        if(is_null($Value)) $Value = 'NULL';
+        else $Value = $this->PDO->quote($Value);
+      }
       $Values .= ','.$Value;
     }
@@ -61 +99 @@
     $Values = substr($Values, 1);
     $this->query('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')'); 
+    $this->insert_id = $this->PDO->lastInsertId();
   }
   
@@ -68 +107 @@
     foreach($Data as $Key => $Value)
     {
+      if(!in_array($Value, $this->Functions)) 
+      {
+        if(is_null($Value)) $Value = 'NULL';
+        else $Value = $this->PDO->quote($Value);
+      }
       $Values .= ', `'.$Key.'`='.$Value;
     }
@@ -80 +124 @@
     foreach($Data as $Key => $Value)
     {
+      if(!in_array($Value, $this->Functions)) 
+      {
+        if(is_null($Value)) $Value = 'NULL';
+        else $Value = $this->PDO->quote($Value);
+      }
       $Name .= ',`'.$Key.'`';
       $Values .= ','.$Value;
@@ -94 +143 @@
     $this->query('SET NAMES "'.$Charset.'"');
   }
+  
+  function real_escape_string($Text)
+  {
+    return(addslashes($Text));
+  }
+  
+}
 
-  function TimeToMysqlDateTime($Time)
-  {
-    return(date('Y-m-d H:i:s', $Time));
-  }
+function TimeToMysqlDateTime($Time)
+{
+  if($Time == NULL) return(NULL);
+    else return(date('Y-m-d H:i:s', $Time));  
+}
 
-  function MysqlDateTimeToTime($Time)
-  {
-    $Parts = explode(' ', $Time);
-    $DateParts = explode('-', $Parts[0]);
-    $TimeParts = explode(':', $Parts[1]);
-    $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]);
-    return($Result);
-  }
+function TimeToMysqlDate($Time)
+{
+  if($Time == NULL) return(NULL);
+    else return(date('Y-m-d', $Time));  
+}
 
-  function MysqlDateToTime($Time)
-  {
-    return($this->MysqlDateTimeToTime($Time.' 0:0:0'));
-  }
+function TimeToMysqlTime($Time)
+{
+  if($Time == NULL) return(NULL);
+    else return(date('H:i:s', $Time));  
+}
+
+function MysqlDateTimeToTime($DateTime)
+{
+  if($DateTime == '') return(0);      
+  $Parts = explode(' ', $DateTime);
+  $DateParts = explode('-', $Parts[0]); 
+  $TimeParts = explode(':', $Parts[1]);
+  $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]); 
+  return($Result);  
+}
+
+function MysqlDateToTime($Date)
+{
+  if($Date == '') return(0);
+  return(MysqlDateTimeToTime($Date.' 0:0:0'));  
+}
+
+function MysqlTimeToTime($Time)
+{
+  if($Time == '') return(0);
+  return(MysqlDateTimeToTime('0000-00-00 '.$Time));  
 }
 

trunk/includes/Page.php

@@ -189 +189 @@
   if(isset($RSSChannels)) 
   foreach($RSSChannels as $Channel)
+  {
     $Output .= ' <link rel="alternate" title="'.$Channel['Title'].'" href="'.
       $System->Link('/rss.php?channel='.$Channel['Channel']).'" type="application/rss+xml" />';
+  }
   $Output .= '<title>'.$System->Config['Web']['Title'].'</title>
 </head>

trunk/includes/error.php

@@ -1 +1 @@
 <?php
-
-include_once('global_function.php');
 
 function EmptyErrorHandler($Number, $Message, $Filename, $LineNumber, $Variables)
@@ -84 +82 @@
 }
 
-set_error_handler('CustomErrorHandler');
+//set_error_handler('CustomErrorHandler');
 
 ?>

trunk/includes/global.php

@@ -1 +1 @@
 <?php
 
-$ScriptStartTime = GetMicrotime();
-
-if(isset($_SERVER['REMOTE_ADDR'])) session_start();
-
-// SQL injection hack protection
-foreach($_POST as $Index => $Item)
-{
-  if(is_array($_POST[$Index])) 
-    foreach($_POST[$Index] as $Index2 => $Item2) $_POST[$Index][$Index2] = addslashes($Item2);
-  else $_POST[$Index] = addslashes($_POST[$Index]);
-}
-foreach($_GET as $Index => $Item) $_GET[$Index] = addslashes($_GET[$Index]);
-
-if(file_exists(dirname(__FILE__).'/config.php')) include_once(dirname(__FILE__).'/config.php');
-  else die('Nenalezen konfigurační soubor config.php ve složce includes. '.
-        'Vytvořte jej zkopírováním vzoru config.sample.php.');
-date_default_timezone_set($Config['Web']['Timezone']);
 include_once(dirname(__FILE__).'/Database.php');
+include_once(dirname(__FILE__).'/system.php');
+include_once(dirname(__FILE__).'/Update.php');
 include_once(dirname(__FILE__).'/rss.php');
-include_once(dirname(__FILE__).'/system.php');
 include_once(dirname(__FILE__).'/user.php');
 include_once(dirname(__FILE__).'/Page.php');
-
-$System = new System();
-$System->Init();
-$User = new User($System);
-
 include_once(dirname(__FILE__).'/error.php');
 
-$TranslationTree = GetTranslationTree();
-
-LogReferrer();
+GlobalInit();
+
+function GlobalInit()
+{
+        global $System, $ScriptStartTime, $TranslationTree, $User, $StopAfterUpdateManager,
+          $UpdateManager, $Config;
+
+  $ScriptStartTime = GetMicrotime();
+
+  if(isset($_SERVER['REMOTE_ADDR'])) session_start();
+
+  if(file_exists(dirname(__FILE__).'/config.php')) include_once(dirname(__FILE__).'/config.php');
+    else die('Nenalezen konfigurační soubor config.php ve složce includes. '.
+          'Vytvořte jej zkopírováním vzoru config.sample.php.');
+  date_default_timezone_set($Config['Web']['Timezone']);
+  
+  $Revision = 506; // Subversion revision
+  $ReleaseTime = '2013-02-15';  
+  
+  $System = new System();
+  $System->Init();
+
+  // Check database persistence structure
+  $UpdateManager = new UpdateManager();
+  $UpdateManager->Database = $System->Database;
+  $UpdateManager->Revision = $Revision;
+  if(isset($StopAfterUpdateManager)) return;
+  if(!$UpdateManager->IsInstalled()) die('Systém vyžaduje instalaci databáze.');
+  if(!$UpdateManager->IsUpToDate()) die('Systém vyžaduje aktualizaci databáze.');
+
+  // SQL injection hack protection
+  foreach($_POST as $Index => $Item)
+  {
+        if(is_array($_POST[$Index]))
+                foreach($_POST[$Index] as $Index2 => $Item2) $_POST[$Index][$Index2] = addslashes($Item2);
+        else $_POST[$Index] = addslashes($_POST[$Index]);
+  }
+  foreach($_GET as $Index => $Item) $_GET[$Index] = addslashes($_GET[$Index]);
+  
+  $User = new User($System);
+  
+  set_error_handler('CustomErrorHandler');
+
+  // TODO: Global initialized variable should be removed
+  $TranslationTree = GetTranslationTree();
+
+  LogReferrer();
+}
 
 $RSSChannels = array(
@@ -298 +322 @@
 }
 
-function MysqlDateTimeToTime($Time)
-{
-  $Parts = explode(' ', $Time);
-  $DateParts = explode('-', $Parts[0]); 
-  $TimeParts = explode(':', $Parts[1]);
-  $Result = mktime($TimeParts[0], $TimeParts[1], $TimeParts[2], $DateParts[1], $DateParts[2], $DateParts[0]); 
-  return($Result);  
-}
-
 function GetLanguageList()
 { 
@@ -429 +444 @@
   
   if(array_key_exists('language', $_GET)) $LanguageId = $_GET['language'] * 1; 
-    else $LanguageId = 1;
+    else $LanguageId = 2;
   
   if(isset($LanguageList[$LanguageId]) == false)

trunk/includes/system.php

@@ -24 +24 @@
   function __construct()
   {
-    $this->Database = new Database();
     $this->Config = array();
   }
@@ -30 +29 @@
   function Init()
   {
-        global $Config;    
+        global $Config;
+            
     $this->Config = $Config;
-    $this->Database->HostName = $this->Config['Database']['Host'];
-    $this->Database->UserName = $this->Config['Database']['User'];
-    $this->Database->Password = $this->Config['Database']['Password'];
-    $this->Database->Schema = $this->Config['Database']['Database'];
-    $this->Database->Charset = $this->Config['Database']['Charset'];
+    $this->Database = new Database($this->Config['Database']['Host'],
+      $this->Config['Database']['User'], $this->Config['Database']['Password'],
+      $this->Config['Database']['Database']);
+    $this->Database->charset($this->Config['Database']['Charset']);
     $this->Database->ShowSQLQuery = $this->Config['Web']['ShowSQLQuery'];
     $this->Database->ShowSQLError = $this->Config['Web']['ShowSQLError'];
-    $this->Database->open();
   }
   

trunk/index.php

@@ -28 +28 @@
   $UnionItems = array();
   $DbResult = $System->Database->query($GroupListQuery);
+  if($DbResult->num_rows > 0)
+  {
   while($DbRow = $DbResult->fetch_assoc())
   {
@@ -51 +53 @@
   }
   $Output .= '</table>';
+  } 
   return($Output);
 }

trunk/log.php

@@ -27 +27 @@
   $Output .= GenerateRSS(array
   (
-    'Title' => 'WoWpřeklad',
+    'Title' => $Config['Web']['Title'],
     'Link' => 'http://'.$Config['Web']['Host'].$System->Link('/'),
     'Description' => $Config['Web']['Title'],
@@ -44 +44 @@
     else $WhereType = '1=1';
     
-  $RSSChannels = array(array('Title' => 'Záznamy změn', 'URL' => 'log.php?rss&type='.$_SESSION['type']));
+  $RSSChannels = array(
+        array('Title' => 'Záznamy změn', 'Channel' => 'log&type='.$_SESSION['type'])
+  );
   
   if($User->Licence(LICENCE_MODERATOR))
@@ -88 +90 @@
   while($Line = $DbResult->fetch_assoc()) 
   {
-    $Output .= '<tr><td>'.$Line['Date'].'</td><td><span style="color: '.$Line['LogColor'].'">'.$Line['Text'].'</span></td><td>'.$Line['UserName'].'</td><td>'.$Line['IP'].'</td></tr>';
+    //$Output .= '<tr><td>'.$Line['Date'].'</td><td><span style="color: '.$Line['LogColor'].'">'.$Line['Text'].'</span></td><td>'.$Line['UserName'].'</td><td>'.$Line['IP'].'</td></tr>';
   }
   $Output .= '</table>'.

trunk/serverlist.php

@@ -21 +21 @@
 
 
-$Query = 'SELECT * FROM `ServerList` '.$Order['SQL'].$PageList['SQLLimit'];
+$Query = 'SELECT `URL`, `Name` FROM `ServerList` '.$Order['SQL'].$PageList['SQLLimit'];
 
 $DbResult = $System->Database->query($Query);

trunk/statistic.php

@@ -12 +12 @@
   global $System;
   
-  $LanguageId = LoadLanguageIdParameter();
+  $Output = '';
+  if(!isset($_SESSION['language'])) 
+  {
+    if($User->Licence(LICENCE_USER)) 
+    {
+      $_SESSION['language'] = $User->Language;
+    } else $_SESSION['language'] = 2;
+  }
+  if(array_key_exists('language', $_GET)) $_SESSION['language'] = LoadLanguageIdParameter();
+  
   $BuildNumber = GetBuildNumber($_SESSION['StatVersion']);
   
@@ -18 +27 @@
   $Query = '';
   $DbResult = $System->Database->query($GroupListQuery);
+  if($DbResult->num_rows > 0)
+  {  
   while($DbRow = $DbResult->fetch_assoc())
   {
     $Query .= 'SELECT (SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
     ' SELECT `T`.`Id`, `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T`'.
-    ' WHERE (`Complete` = 1) AND (`Language`='.$LanguageId.') AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
+    ' WHERE (`Complete` = 1) AND (`Language`='.$_SESSION['language'].') AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
     ') AS `C1`) AS `Translated`, '.
     '(SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
@@ -34 +45 @@
   $DbRow = $DbResult->fetch_row();
   $PageList = GetPageList($DbRow[0]);  
-  $Output = $PageList['Output'];
+  $Output .= $PageList['Output'];
   
   $Output .= '<table class="BaseTable">';
@@ -60 +71 @@
   $Output .= '<tr><td><strong>Celkem</strong></td><td><strong>'.$Translated.'</strong></td><td><strong>'.$Total.'</strong></td><td><strong>'.ProgressBar(150, $TotalCount).'</strong></td></tr>'.
     '</table>';
+  }
   return($Output);
 }

trunk/team.php

@@ -97 +97 @@
       if(($Count == 0) and ($_POST['Name'] != ''))
       {
-        $System->Database->query('INSERT INTO `Team` (`Name` ,`Description`, `URL`, `TimeCreate`,`Leader`) VALUES ("'.trim($_POST['Name']).'", "'.trim($_POST['Description']).'", "'.$_POST['URL'].'", NOW(), '.$User->Id.')');  
+        $System->Database->query('INSERT INTO `Team` (`Name` ,`Description`, `URL`, `TimeCreate`, `Leader`)'.
+        ' VALUES ("'.trim($_POST['Name']).'", "'.trim($_POST['Description']).'", "'.
+        $_POST['URL'].'", NOW(), '.$User->Id.')');  
         $System->Database->query('UPDATE `User` SET `Team` = '.$System->Database->insert_id.' WHERE `ID` = '.$User->Id); 
         $Output .= ShowMessage('Překladatelský tým vytvořen.');
@@ -220 +222 @@
   $Query = '';
   $DbResult = $System->Database->query($GroupListQuery);
+  if($DbResult->num_rows > 0)
+  {
   while($DbRow = $DbResult->fetch_assoc())
   {
@@ -247 +251 @@
 
   $Order = GetOrderTableHeader($TableColumns, 'Name', 0);
-  $Output .=$Order['Output'];
+  $Output .= $Order['Output'];
 
   $Translated = 0;
@@ -260 +264 @@
   $Output .='<tr><td><strong>Celkem</strong></td><td><strong>'.$Translated.'</strong></td><td><strong>'.$Total.'</strong></td><td><strong>'.ProgressBar(150, round($Translated / $Total * 100, 2)).'</strong></td></tr>';
   $Output .='</table>';
-  
+  }
       $Output .='</fieldset>';    
     } else $Output .=ShowMessage('Tým nenalezen', MESSAGE_CRITICAL);