Changeset 539


Ignore:
Timestamp:
May 15, 2013, 12:44:32 AM (11 years ago)
Author:
chronos
Message:
  • Upraveno: Sloupce LastIP, LastLogin, LastLogout odděleny z tabulky User do samostatné nové tabulky UserTrace. Tato tabulka slouží pro uchování údajů aktualizovaných při každém zobrazení stránky. Dokud byly tyto údaje aktualizovány přímo v tabulce User, tak docházelo k přegenerování všech indexů tabulky User a tedy výraznému zpomalení provádění SQL dotazů.
  • Upraveno: Zobrazení klikacího odkazu na uživatele ve výpisu dalších tabulek.
  • Upraveno: Optimalizováno načítání obecného a týmového stavu dokončení překladu.
  • Upraveno: Při registraci se volá nově metoda User->Register namísto přímého vkládání položky do databáze.
Location:
trunk
Files:
11 edited

Legend:

Unmodified
Added
Removed

  • trunk/admin/UpdateTrace.php

    r537 r539  
    25422542}
    25432543
     2544function UpdateTo539($Manager)
     2545{
     2546  $Manager->Execute("CREATE TABLE IF NOT EXISTS `UserTrace` (
     2547    `Id` int(11) NOT NULL AUTO_INCREMENT,
     2548    `User` int(11) NOT NULL,
     2549    `LastLogin` datetime NOT NULL,
     2550    `LastLogout` datetime NOT NULL,
     2551    `LastIP` varchar(16) NOT NULL,
     2552    `UserAgent` varchar(255) NOT NULL,
     2553    PRIMARY KEY (`Id`),
     2554    KEY `User` (`User`)
     2555) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
     2556  $Manager->Execute("ALTER TABLE `UserTrace`
     2557  ADD CONSTRAINT `UserTrace_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`ID`);");
     2558  $Manager->Execute("INSERT INTO `UserTrace` (".
     2559    "SELECT NULL AS `Id` , `User`.`Id` AS `User` , `User`.`LastLogin` AS `LastLogin` , `User`.`LastLogout` AS `LastLogout` , `User`.`LastIP` AS `LastIP` , '' AS `UserAgent` ".
     2560    "FROM `User` WHERE 1 ) ");
     2561  $Manager->Execute("ALTER TABLE `User` DROP `LastIP`, DROP `LastLogin`, DROP `LastLogout`;");
     2562}
     2563
    25442564$Updates = array(
    25452565        498 => array('Revision' => 506, 'Function' => 'UpdateTo506'),
    … …  
    25472567        510 => array('Revision' => 524, 'Function' => 'UpdateTo524'),
    25482568        524 => array('Revision' => 537, 'Function' => 'UpdateTo537'),
     2569        537 => array('Revision' => 539, 'Function' => 'UpdateTo539'),
    25492570);
    25502571

  • trunk/admin/index.php

    r538 r539  
    8585}
    8686
     87function ShowPHPInfo()
     88{
     89  return(phpinfo()); 
     90}
     91
    8792function Testing()
    8893{
    8994  global $System;
    9095 
    91   $Output = '<a href="'.$System->Link('/admin/?action=error').'">Testovací chyba</a><br/>'.
     96  $Output = '<a href="'.$System->Link('/admin/?action=phpinfo').'">PHP info</a><br/>'.
     97  '<small>PHP informace</small><br/><br/>'.
     98  '<a href="'.$System->Link('/admin/?action=error').'">Testovací chyba</a><br/>'.
    9299  '<small>Vyvolá testovací chybu</small><br/><br/>'.
    93100  '<a href="'.$System->Link('/admin/?action=exception').'">Testovací výjimka</a><br/>'.
    … …  
    109116    else if($_GET['action'] == 'sqlerror') $Output .= TestSQLError('SELECT dads FROM sdas');
    110117    else if($_GET['action'] == 'testing') $Output .= Testing();
     118    else if($_GET['action'] == 'phpinfo') $Output .= ShowPHPInfo();
    111119    else if($_GET['action'] == 'addnew')
    112120    {

  • trunk/export/index.php

    r526 r539  
    4949    $Action = '<a href="?Action=View&amp;ExportId='.$Export['Id'].'&amp;Tab=0">Zobrazit</a> <a href="?Action=View&amp;ExportId='.$Export['Id'].'&amp;Tab=7">Exportovat</a>';
    5050    if($Export['User'] == $User->Id) $Action .= ' <a href="?Action=Delete&amp;ExportId='.$Export['Id'].'" onclick="return confirmAction(\'Opravdu smazat položku?\');">Smazat</a>';
    51     $Output .= '<tr><td>'.HumanDate($Export['TimeCreate']).'</td><td>'.$Export['UserName'].'</td><td>'.$Export['Title'].'</td><td>'.$Export['OutputType'].'</td><td>'.$Export['ClientVersion'].'</td><td>'.$Export['UsedCount'].'</td><td>'.$Action.'</td></tr>';
     51    $Output .= '<tr><td>'.HumanDate($Export['TimeCreate']).'</td>'.
     52      '<td><a href="'.$System->Link('/user.php?user='.$Export['User']).'">'.$Export['UserName'].'</a></td>'.
     53      '<td>'.$Export['Title'].'</td>'.
     54      '<td>'.$Export['OutputType'].'</td>'.
     55      '<td>'.$Export['ClientVersion'].'</td>'.
     56      '<td>'.$Export['UsedCount'].'</td>'.
     57      '<td>'.$Action.'</td></tr>';
    5258  }
    5359  $Output .= '</table>'.

  • trunk/includes/Page.php

    r538 r539  
    259259 
    260260  $Output = 'Online překladatelé:<br />';
    261   $DbResult = $System->Database->query('SELECT `Name`, `GM`, `ID` FROM `User` WHERE `LastLogin` >= NOW() - 300 AND ((`LastLogout` < `LastLogin`) OR (ISNULL(`LastLogout`)))');
     261  $DbResult = $System->Database->query('SELECT `Name`, `GM`, `User`.`ID` AS `ID` FROM `User` '.
     262    'LEFT JOIN `UserTrace` ON `UserTrace`.`User` = `User`.`Id` '.
     263    'WHERE (`LastLogin` >= NOW() - 300) AND ((`LastLogout` < `LastLogin`) OR (ISNULL(`LastLogout`)))');
    262264  while($DbUser = $DbResult->fetch_assoc())
    263265  {

  • trunk/includes/Version.php

    r538 r539  
    11<?php
    22
    3 $Revision = 538; // Subversion revision
    4 $DatabaseRevision = 537; // Database structure revision
     3$Revision = 539; // Subversion revision
     4$DatabaseRevision = 539; // Database structure revision
    55$ReleaseTime = '2013-05-14';
    66

  • trunk/includes/user.php

    r524 r539  
    4646  {
    4747    if($this->Role != LICENCE_ANONYMOUS)
    48       $this->System->Database->query('UPDATE `User` SET `LastLogout` = NOW() WHERE `ID` = '.$this->Id);
     48      $this->System->Database->query('UPDATE `UserTrace` SET '.
     49        '`LastLogout` = NOW() WHERE `User` = '.$this->Id);
    4950    $this->SetAnonymous();
    5051  }
    … …  
    5354  {
    5455    $DbResult = $this->System->Database->query('SELECT * FROM `User` WHERE `ID` = '.$this->Id);
    55     $User = $DbResult->fetch_assoc();
    56     // Security: Password and Salt hash should not be loaded to variables
    57     $this->Id = $User['ID'];
    58     $this->Team = $User['Team'];
    59     $this->Redirecting = $User['Redirecting'];
    60     $this->Language = $User['Language'];
    61     $this->Name = $User['Name'];
    62     $this->Role = $User['GM'];
    63     $this->Email = $User['Email'];
    64     $this->Info = $User['Info'];
    65     $this->PreferredVersion = $User['PreferredVersion'];
     56    if($DbResult->num_rows > 0)
     57    {
     58      $User = $DbResult->fetch_assoc();
     59      // Security: Password and Salt hash should not be loaded to variables
     60      $this->Id = $User['ID'];
     61      $this->Team = $User['Team'];
     62      $this->Redirecting = $User['Redirecting'];
     63      $this->Language = $User['Language'];
     64      $this->Name = $User['Name'];
     65      $this->Role = $User['GM'];
     66      $this->Email = $User['Email'];
     67      $this->Info = $User['Info'];
     68      $this->PreferredVersion = $User['PreferredVersion'];
     69    } else $this->SetAnonymous();
    6670  }
    6771 
    … …  
    113117  function UpdateState()
    114118  {
    115     if(array_key_exists('REMOTE_ADDR', $_SERVER))
    116       $this->System->Database->query('UPDATE `User` SET `LastIP` = "'.$_SERVER['REMOTE_ADDR'].'", `LastLogin` = NOW() WHERE `ID` = '.$this->Id);
     119    if(array_key_exists('REMOTE_ADDR', $_SERVER) and ($this->Role != LICENCE_ANONYMOUS))
     120      $this->System->Database->query('UPDATE `UserTrace` SET '.
     121        '`LastIP` = "'.$_SERVER['REMOTE_ADDR'].'", '.
     122        '`LastLogin` = NOW(), '.
     123        '`UserAgent` = "'.$this->System->Database->real_escape_string($_SERVER['HTTP_USER_AGENT']).'" '.
     124        'WHERE `User` = '.$this->Id);
     125  }
     126 
     127  function Register($UserName, $Password, $Email, $Language, $Team, $PreferredVersion)
     128  {
     129    $Salt = $this->GetPasswordSalt();
     130    $this->System->Database->query('INSERT INTO `User` '.
     131      '(`Name` , `Pass` , `Salt`, `Email` , `Language` , `Team` , `NeedUpdate`, `RegistrationTime`, `PreferredVersion` ) '.
     132      'VALUES ("'.$UserName.'", '.$this->CryptPasswordSQL('"'.$Password.'"', '"'.$Salt.'"').
     133      ', "'.$Salt.'", "'.$Email.'", '.$Language.', '.$Team.', 1, NOW(), '.$PreferredVersion.')');
     134    $UserId = $this->System->Database->insert_id;
     135    $this->System->Database->query('INSERT INTO `UserTrace` (`User`, `LastIP`, `UserAgent`) '.
     136        'VALUES ('.$UserId.', "'.$_SERVER['REMOTE_ADDR'].'", '.
     137        '"'.$this->System->Database->real_escape_string($_SERVER['HTTP_USER_AGENT']).'")');
    117138  }
    118139}

  • trunk/registrace.php

    r524 r539  
    101101          if(!$Line)
    102102          {
    103             $Salt = $User->GetPasswordSalt();
    104103            if($Team == 0) $Team = 'NULL';
    105             $System->Database->query('INSERT INTO `User` (`Name` , `Pass` , `Salt`, `LastIP` , `LastLogin` , `Email` , `Language` , `Team` , `NeedUpdate`, `RegistrationTime`, `PreferredVersion` ) '.
    106               'VALUES ("'.$UserName.'", '.$User->CryptPasswordSQL('"'.$Pass.'"', '"'.$Salt.'"').', "'.$Salt.'", "'.$_SERVER['REMOTE_ADDR'].'", NOW(), "'.$Email.'", '.$Language.', '.$Team.', 1, NOW(), '.$PreferredVersion.')');
     104            $User->Register($UserName, $Pass, $Email, $Language, $Team, $PreferredVersion);
    107105            $Output .= ShowMessage('Registrace proběhla úspěšně.');
    108106            $Output .= 'Přečtěte si pozorně <a href="'.$System->Link('/info.php').'">pokyny pro překladání</a> a můžete pak hned začít překládat.';

  • trunk/statistic.php

    r536 r539  
    4242  {
    4343    $Query .= 'SELECT (SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
    44     ' SELECT `T`.`Id`, `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T`'.
    45     ' WHERE (`Complete` = 1) '.$LanguageFilter.' AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
     44    'SELECT `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T` '.
     45    'WHERE (`Complete` = 1) '.$LanguageFilter.' '.
     46    'AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
    4647    ') AS `C1`) AS `Translated`, '.
    4748    '(SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
    48     ' SELECT `T`.`Id`, `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T`'.
    49     ' WHERE (`Language` = '.$Config['OriginalLanguage'].') AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
     49    'SELECT `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T` '.
     50    'WHERE (`Language` = '.$Config['OriginalLanguage'].') '.
     51    'AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
    5052    ') AS `C2`) AS `Total`, "'.$DbRow['Name'].'" AS `Name` UNION ';
    5153  }

  • trunk/team.php

    r525 r539  
    3939        '<td><a href="?action=team&amp;id='.$Team['Id'].'">'.$Team['Name'].'</a></td>'.
    4040        '<td><a href="http://'.$Team['URL'].'">'.$Team['URL'].'</a></td>'.
    41         '<td>'.$Team['LeaderName'].'</td>'.
     41        '<td><a href="'.$System->Link('/user.php?user='.$Team['Leader']).'">'.$Team['LeaderName'].'</a></td>'.
    4242        '<td><a href="userlist.php?team='.$Team['Id'].'" title="Zobrazit členy týmu">'.$Team['NumberUser'].'</a></td>'.
    4343        '<td>'.HumanDate($Team['TimeCreate']).'</td>';
    … …  
    193193    ImgLevelUpdate();
    194194     
    195     $DbResult = $System->Database->query('SELECT *, (SELECT COUNT(*) FROM `User` WHERE `Team` = `Team`.`Id`) AS `NumberUser`, (SELECT SUM(`TranslatedCount`) FROM `User` WHERE `team` = `Team`.`Id`) AS `NumberTranslate` FROM `Team` WHERE `Id`='.$_GET['id']);
     195    $DbResult = $System->Database->query('SELECT `Id`, `Name`, `Description`, `URL`, `Leader`, '.
     196      '(SELECT COUNT(*) FROM `User` WHERE '.
     197      '`Team` = `Team`.`Id`) AS `NumberUser`, (SELECT SUM(`TranslatedCount`) FROM `User` WHERE '.
     198      '`Team` = `Team`.`Id`) AS `NumberTranslate` FROM '.
     199      '`Team` WHERE `Id`='.($_GET['id'] * 1));
    196200    if($DbResult->num_rows > 0)
    197201    {
    198202      $Team = $DbResult->fetch_assoc();
    199       $DbResult2 = $System->Database->query('SELECT `Name` FROM `User` WHERE `ID`='.$Team['Leader']);
     203      $DbResult2 = $System->Database->query('SELECT `Name`, `Id` FROM `User` WHERE `ID`='.$Team['Leader']);
    200204      if($DbResult2->num_rows > 0)
    201205      {
    202206        $Leader = $DbResult2->fetch_assoc();
    203         $Leader = $Leader['Name'];
    204       } else $Leader = '';
     207      } else $Leader = array('Name' => '', 'Id' => 0);
    205208   
    206209      $Output .='<strong>Překladatelský tým '.$Team['Name'].'</strong><br />'.
    207210        'Webové stránky: <a href="http://'.$Team['URL'].'">'.$Team['URL'].'</a><br/>'.
    208         'Vedoucí: <strong>'.$Leader.'</strong><br/>'.
     211        'Vedoucí: <a href="'.$System->Link('/user.php?user='.$Leader['Id']).'">'.$Leader['Name'].'</a><br/>'.
    209212        'Popis: '.$Team['Description'].'<br /><br />';
    210213      //$Output .= '<a href="export/?team='.$Team['Id'].'">Exportovat překlad týmu</a> ';
    … …  
    227230  {
    228231    $Query .= 'SELECT (SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
    229     ' SELECT `T`.* FROM `'.$DbRow['TablePrefix'].'` AS `T` '.
     232    'SELECT `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T` '.
    230233    'WHERE (`User` IN (SELECT `ID` FROM `User` WHERE `Team` = '.$Team['Id'].')) '.
    231234    'AND (`Complete` = 1) AND (`T`.`Language`!='.$Config['OriginalLanguage'].') '.
    … …  
    233236    ') AS `C1`) AS `Translated`, '.
    234237    '(SELECT COUNT(DISTINCT(`Entry`)) FROM ('.
    235     ' SELECT `T`.* FROM `'.$DbRow['TablePrefix'].'` AS `T`'.
    236     ' WHERE (`Language` = '.$Config['OriginalLanguage'].') AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
     238    'SELECT `T`.`Entry` FROM `'.$DbRow['TablePrefix'].'` AS `T` '.
     239    'WHERE (`Language` = '.$Config['OriginalLanguage'].') '.
     240    'AND (`VersionStart` <= '.$BuildNumber.') AND (`VersionEnd` >= '.$BuildNumber.')'.
    237241    ') AS `C2`) AS `Total`, "'.$DbRow['Name'].'" AS `Name` UNION ';
    238242  }

  • trunk/user.php

    r524 r539  
    2525       
    2626  $Output = '';
    27   $Query = 'SELECT `User`.`Name`, `User`.`LastLogin`, `User`.`LastIP`, `User`.`Email`, '.
     27  $Query = 'SELECT `User`.`Name`, `UserTrace`.`LastLogin`, `UserTrace`.`LastIP`, '.
     28    '`User`.`Email`, `UserTrace`.`UserAgent`, '.
    2829    '`User`.`TranslatedCount`, `User`.`Team`, `User`.`ID`, `User`.`Info`, '.
    2930    '`Team`.`Name` AS `TeamName`, `Language`.`Name` AS `LanguageName`, '.
    3031    '`ClientVersion`.`Version` AS `Version` FROM `User` '.
     32    'LEFT JOIN `UserTrace` ON `UserTrace`.`User` = `User`.`Id` '.
    3133    'LEFT JOIN `Language` ON `Language`.`Id` = `User`.`Language` '.
    3234    'LEFT JOIN `Team` ON `Team`.`Id` = `User`.`Team` '.
    … …  
    5052        $Output .= '<fieldset><legend>Moderování</legend>';
    5153        $Output .= 'Poslední IP: <strong>'.$UserLine['LastIP'].'</strong><br />'.
     54          'Prohlížeč: <strong>'.$UserLine['UserAgent'].'</strong><br />'.
    5255        'Email: <strong>'.$UserLine['Email'].'</strong><br />';
    5356        $Output .= '<br/><form action="user.php" method="post"><div>'.

  • trunk/userlist.php

    r524 r539  
    5656
    5757
    58 $Query = 'SELECT `ID`, `User`.`Name`, `LastLogin`, `GM`, `XP`, `TranslatedCount`, `RegistrationTime` FROM `User` '.$_SESSION['Where'].$Order['SQL'].$PageList['SQLLimit'];
     58$Query = 'SELECT `User`.`ID`, `User`.`Name`, `LastLogin`, `GM`, `XP`, `TranslatedCount`, `RegistrationTime` '.
     59  'FROM `User` '.
     60  'LEFT JOIN `UserTrace` ON `UserTrace`.`User` = `User`.`Id` '.
     61  $_SESSION['Where'].$Order['SQL'].$PageList['SQLLimit'];
    5962
    6063$DbResult = $System->Database->query($Query);
Note: See TracChangeset for help on using the changeset viewer.