Changeset 49


Ignore:
Timestamp:
Jun 20, 2009, 4:22:51 PM (15 years ago)
Author:
george
Message:
  • Opraveno: Sledování uživatelů online.
Location:
trunk/www
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • trunk/www/sql/full.sql

    r41 r49  
    44--
    55-- Počítač: localhost
    6 -- Vygenerováno: Úterý 16. června 2009, 18:24
     6-- Vygenerováno: Sobota 20. června 2009, 13:49
    77-- Verze MySQL: 5.0.77
    88-- Verze PHP: 5.2.9
    … …  
    2020--
    2121
     22DROP TABLE IF EXISTS `Backup`;
    2223CREATE TABLE IF NOT EXISTS `Backup` (
    2324  `Id` int(11) NOT NULL auto_increment,
    … …  
    2728  `Lock` int(11) NOT NULL,
    2829  `Database` int(11) NOT NULL,
    29   PRIMARY KEY  (`Id`)
    30 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     30  PRIMARY KEY  (`Id`),
     31  KEY `Server` (`Server`),
     32  KEY `Database` (`Database`)
     33) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=53 ;
    3134
    3235-- --------------------------------------------------------
    … …  
    3639--
    3740
     41DROP TABLE IF EXISTS `Client`;
    3842CREATE TABLE IF NOT EXISTS `Client` (
    3943  `Id` int(11) NOT NULL auto_increment,
    … …  
    4347  `Title` varchar(255) NOT NULL,
    4448  PRIMARY KEY  (`Id`)
    45 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     49) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=74 ;
    4650
    4751-- --------------------------------------------------------
    … …  
    5155--
    5256
     57DROP TABLE IF EXISTS `Database`;
    5358CREATE TABLE IF NOT EXISTS `Database` (
    5459  `Id` int(11) NOT NULL auto_increment,
    … …  
    6065  `ACIDRevision` int(11) NOT NULL,
    6166  `SourceFileName` varchar(255) NOT NULL,
    62   `Emulator` int(11) NOT NULL,
    63   PRIMARY KEY  (`Id`)
    64 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
    65 
    66 -- --------------------------------------------------------
    67 
    68 --
    69 -- Struktura tabulky `DatabaseUpdate`
    70 --
    71 
    72 CREATE TABLE IF NOT EXISTS `DatabaseUpdate` (
    73   `Id` int(11) NOT NULL auto_increment,
    74   `Database` int(11) NOT NULL,
    75   `Revision` int(11) NOT NULL,
    76   `Updates` text NOT NULL,
    77   PRIMARY KEY  (`Id`)
    78 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     67  `Emulator` int(11) default NULL,
     68  `Update` text NOT NULL,
     69  PRIMARY KEY  (`Id`),
     70  KEY `Emulator` (`Emulator`)
     71) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
    7972
    8073-- --------------------------------------------------------
    … …  
    8477--
    8578
     79DROP TABLE IF EXISTS `Debug`;
    8680CREATE TABLE IF NOT EXISTS `Debug` (
    8781  `Id` int(11) NOT NULL auto_increment,
    … …  
    9791  `DbErrors` text NOT NULL,
    9892  `Configuration` text NOT NULL,
    99   PRIMARY KEY  (`Id`)
    100 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     93  PRIMARY KEY  (`Id`),
     94  KEY `Server` (`Server`)
     95) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
    10196
    10297-- --------------------------------------------------------
    … …  
    106101--
    107102
     103DROP TABLE IF EXISTS `Emulator`;
    108104CREATE TABLE IF NOT EXISTS `Emulator` (
    109105  `Id` int(11) NOT NULL auto_increment,
    … …  
    111107  `Client` int(11) NOT NULL,
    112108  `Revision` int(11) NOT NULL,
    113   `CommitHash` varchar(255) NOT NULL,
     109  `CommitHash` varchar(255) NOT NULL COMMENT 'Určeno dle revize z http://github.com/mangos/mangos/commits/master',
    114110  `ScriptDev2PatchFileName` varchar(255) NOT NULL,
    115111  `ScriptDev2Revision` int(11) NOT NULL,
    116112  `Version` varchar(16) NOT NULL,
    117113  `Lock` int(11) NOT NULL,
    118   PRIMARY KEY  (`Id`)
    119 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     114  `Enable` int(11) NOT NULL,
     115  PRIMARY KEY  (`Id`),
     116  KEY `Client` (`Client`)
     117) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
    120118
    121119-- --------------------------------------------------------
    … …  
    125123--
    126124
     125DROP TABLE IF EXISTS `History`;
    127126CREATE TABLE IF NOT EXISTS `History` (
    128127  `Time` int(11) NOT NULL,
    … …  
    138137  KEY `Server` (`Server`),
    139138  KEY `Period` (`Period`)
    140 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
     139) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    141140
    142141-- --------------------------------------------------------
    … …  
    146145--
    147146
     147DROP TABLE IF EXISTS `Log`;
    148148CREATE TABLE IF NOT EXISTS `Log` (
    149149  `Time` datetime NOT NULL default '0000-00-00 00:00:00',
    … …  
    152152  `Operation` varchar(255) character set utf8 NOT NULL,
    153153  `Value` varchar(255) character set utf8 NOT NULL,
    154   KEY `Time` (`Time`)
    155 ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
     154  KEY `Time` (`Time`),
     155  KEY `User` (`User`)
     156) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;
    156157
    157158-- --------------------------------------------------------
    … …  
    161162--
    162163
     164DROP TABLE IF EXISTS `News`;
    163165CREATE TABLE IF NOT EXISTS `News` (
    164166  `Id` int(11) NOT NULL auto_increment,
    … …  
    167169  `Title` varchar(255) NOT NULL,
    168170  `Content` text NOT NULL,
    169   PRIMARY KEY  (`Id`)
    170 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     171  PRIMARY KEY  (`Id`),
     172  KEY `User` (`User`)
     173) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
    171174
    172175-- --------------------------------------------------------
    … …  
    176179--
    177180
     181DROP TABLE IF EXISTS `Server`;
    178182CREATE TABLE IF NOT EXISTS `Server` (
    179183  `Id` int(11) NOT NULL auto_increment,
    … …  
    193197  `AccountCount` int(11) NOT NULL,
    194198  `Lock` int(11) NOT NULL,
    195   PRIMARY KEY  (`Id`)
    196 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     199  PRIMARY KEY  (`Id`),
     200  KEY `User` (`User`),
     201  KEY `Database` (`Database`)
     202) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;
    197203
    198204-- --------------------------------------------------------
    … …  
    202208--
    203209
     210DROP TABLE IF EXISTS `Task`;
    204211CREATE TABLE IF NOT EXISTS `Task` (
    205212  `Id` int(11) NOT NULL auto_increment,
    … …  
    209216  `Title` varchar(255) NOT NULL,
    210217  `CommandList` text NOT NULL,
    211   `User` int(11) NOT NULL,
     218  `User` int(11) default NULL,
    212219  `State` int(11) NOT NULL,
    213220  `Output` text NOT NULL,
    214221  `Error` text NOT NULL,
    215   PRIMARY KEY  (`Id`)
    216 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     222  PRIMARY KEY  (`Id`),
     223  KEY `User` (`User`)
     224) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
    217225
    218226-- --------------------------------------------------------
    … …  
    222230--
    223231
     232DROP TABLE IF EXISTS `User`;
    224233CREATE TABLE IF NOT EXISTS `User` (
    225234  `Id` int(11) NOT NULL auto_increment,
    … …  
    234243  `Role` int(11) NOT NULL,
    235244  PRIMARY KEY  (`Id`)
    236 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     245) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
    237246
    238247-- --------------------------------------------------------
    … …  
    242251--
    243252
     253DROP TABLE IF EXISTS `UserOnline`;
    244254CREATE TABLE IF NOT EXISTS `UserOnline` (
    245255  `Id` int(11) NOT NULL auto_increment,
    … …  
    253263  PRIMARY KEY  (`Id`),
    254264  KEY `User` (`User`)
    255 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
     265) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=19 ;
     266
     267--
     268-- Omezení pro exportované tabulky
     269--
     270
     271--
     272-- Omezení pro tabulku `Database`
     273--
     274ALTER TABLE `Database`
     275  ADD CONSTRAINT `Database_ibfk_1` FOREIGN KEY (`Emulator`) REFERENCES `Emulator` (`Id`);
     276
     277--
     278-- Omezení pro tabulku `Debug`
     279--
     280ALTER TABLE `Debug`
     281  ADD CONSTRAINT `Debug_ibfk_2` FOREIGN KEY (`Server`) REFERENCES `Server` (`Id`),
     282  ADD CONSTRAINT `Debug_ibfk_1` FOREIGN KEY (`Server`) REFERENCES `Server` (`Id`);
     283
     284--
     285-- Omezení pro tabulku `Emulator`
     286--
     287ALTER TABLE `Emulator`
     288  ADD CONSTRAINT `Emulator_ibfk_1` FOREIGN KEY (`Client`) REFERENCES `Client` (`Id`);
     289
     290--
     291-- Omezení pro tabulku `History`
     292--
     293ALTER TABLE `History`
     294  ADD CONSTRAINT `History_ibfk_1` FOREIGN KEY (`Server`) REFERENCES `Server` (`Id`);
     295
     296--
     297-- Omezení pro tabulku `Log`
     298--
     299ALTER TABLE `Log`
     300  ADD CONSTRAINT `Log_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);
     301
     302--
     303-- Omezení pro tabulku `News`
     304--
     305ALTER TABLE `News`
     306  ADD CONSTRAINT `News_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);
     307
     308--
     309-- Omezení pro tabulku `Server`
     310--
     311ALTER TABLE `Server`
     312  ADD CONSTRAINT `Server_ibfk_2` FOREIGN KEY (`Database`) REFERENCES `Database` (`Id`),
     313  ADD CONSTRAINT `Server_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);
     314
     315--
     316-- Omezení pro tabulku `Task`
     317--
     318ALTER TABLE `Task`
     319  ADD CONSTRAINT `Task_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);
     320
     321--
     322-- Omezení pro tabulku `UserOnline`
     323--
     324ALTER TABLE `UserOnline`
     325  ADD CONSTRAINT `UserOnline_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);

  • trunk/www/user.php

    r46 r49  
    4545    $SID = session_id();
    4646
     47    // Lookup user record
     48    $Query = $this->Database->select('UserOnline', '*', 'SessionId="'.$SID.'"');
     49    if($Query->num_rows == 0)
     50      $this->Database->insert('UserOnline', array('SessionId' => $SID, 'User' => $this->AnonymousUserId, 'LoginTime' => 'NOW()', 'ActivityTime' => 'NOW()', 'IpAddress' => GetRemoteAddress(), 'HostName' => gethostbyaddr(GetRemoteAddress()), 'ScriptName' => $_SERVER['PHP_SELF']));
     51    //echo($this->Database->LastQuery);
     52
    4753    // Check login
    4854    $Query = $this->Database->select('UserOnline', '*', 'SessionId="'.$SID.'"');
    … …  
    7076    // Lookup user record
    7177    $Query = $this->Database->select('UserOnline', '*', 'SessionId="'.$SID.'"');
    72     if($Query->num_rows > 0)
    73     {
    74       // Refresh time of last access
    75       $this->Database->update('UserOnline', 'SessionId="'.$SID.'"', array('ActivityTime' => 'NOW()'));
    76     } else $this->Database->insert('UserOnline', array('SessionId' => $SID, 'User' => $this->AnonymousUserId, 'LoginTime' => 'NOW()', 'ActivityTime' => 'NOW()', 'IpAddress' => GetRemoteAddress(), 'HostName' => gethostbyaddr(GetRemoteAddress()), 'ScriptName' => $_SERVER['PHP_SELF']));
    77     //echo($this->Database->LastQuery);
    78 
    79     // Check login
    80     $Query = $this->Database->select('UserOnline', '*', 'SessionId="'.$SID.'"');
    81     $Row = $Query->fetch_assoc();
    82     if($Row['User'] != $this->AnonymousUserId)
    83     {
    84       $Query = $this->Database->select('User', '*', 'Id='.$Row['User']);
    85       $this->User = $Query->fetch_assoc();
    86       $Result = USER_LOGGED;
    87     } else
    88     {
    89       $Query = $this->Database->select('User', '*', 'Id='.$this->AnonymousUserId);
    90       $this->User = $Query->fetch_assoc();
    91       $Result = USER_NOT_LOGGED;
    92     }
     78    if($Query->num_rows == 0)
     79      $this->Database->insert('UserOnline', array('SessionId' => $SID, 'User' => $this->AnonymousUserId, 'LoginTime' => 'NOW()', 'ActivityTime' => 'NOW()', 'IpAddress' => GetRemoteAddress(), 'HostName' => gethostbyaddr(GetRemoteAddress()), 'ScriptName' => $_SERVER['PHP_SELF']));
     80
     81    // Refresh time of last access
     82    $this->Database->update('UserOnline', 'SessionId="'.$SID.'"', array('ActivityTime' => 'NOW()'));
    9383
    9484    //$this->LoadPermission($this->User['Role']);
Note: See TracChangeset for help on using the changeset viewer.