Changeset 28323


Ignore:
Timestamp:
May 2, 2014, 9:45:06 AM (10 years ago)
Author:
plg
Message:

huge speed improvement: use a dedicated short-term visit log instead of history table

Location:
extensions/AntiAspi
Files:
2 edited

Legend:

Unmodified
Added
Removed
  • extensions/AntiAspi/main.inc.php

    r21214 r28323  
    22/*
    33Plugin Name: AntiAspi
    4 Version: 2.0.b
     4Version: auto
    55Description: Bloque les aspirateurs de sites
    6 Plugin URI: http://phpwebgallery.net/ext/extension_view.php?eid=225
    7 Author: P@t
     6Plugin URI: http://piwigo.org/ext/extension_view.php?eid=225
     7Author: P@t + plg
    88Author URI: http://www.gauchon.com
    99*/
     
    1313global $prefixeTable;
    1414
     15// +-----------------------------------------------------------------------+
     16// | Define plugin constants                                               |
     17// +-----------------------------------------------------------------------+
     18
     19define('ANTIASPI_ID', basename(dirname(__FILE__)));
     20define('ANTIASPI_PATH', PHPWG_PLUGINS_PATH.ANTIASPI_ID.'/');
     21define('ANTIASPI_VERSION', 'auto');
    1522define('ANTIASPI_TABLE' , $prefixeTable . 'antiaspi_ip_ban');
     23define('ANTIASPI_LOG_TABLE' , $prefixeTable . 'antiaspi_log');
     24
     25// init the plugin
     26add_event_handler('init', 'antiaspi_init');
     27
     28/**
     29 * plugin initialization
     30 *   - check for upgrades
     31 *   - load language
     32 */
     33function antiaspi_init()
     34{
     35  // apply upgrade if needed
     36  include_once(ANTIASPI_PATH.'maintain.inc.php');
     37  $maintain = new AntiAspi_maintain(ANTIASPI_ID);
     38  $maintain->autoUpdate(ANTIASPI_VERSION, 'install');
     39}
    1640
    1741add_event_handler('loc_end_section_init', 'antiaspi');
     
    6791 
    6892  // nombre de fois ou le visiteur est passé dans les xxx dernières hh:mm:ss
    69   $query = 'SELECT COUNT(*)
    70     FROM ' . HISTORY_TABLE . '
    71     WHERE ip="' . $Vip . '"
    72     AND date = CURDATE()
    73     AND time > ADDTIME(CURTIME(), "-' . $diff_conf[1] . '")
     93  $query = '
     94SELECT
     95    COUNT(*)
     96  FROM ' . ANTIASPI_LOG_TABLE . '
     97  WHERE ip="' . $Vip . '"
     98    AND occured_on > ADDTIME(NOW(), "-' . $diff_conf[1] . '")
    7499    ' . ($antiaspi['only picture'] ? 'AND image_id IS NOT NULL' : '') . '
    75100
    76     UNION ALL
     101UNION ALL
    77102
    78     SELECT COUNT(*)
    79     FROM ' . HISTORY_TABLE . '
    80     WHERE ip="' . $Vip . '"
    81     AND date = CURDATE()
    82     AND time > ADDTIME(CURTIME(), "-' . $same_conf[1] . '")
     103SELECT
     104    COUNT(*)
     105  FROM ' . ANTIASPI_LOG_TABLE . '
     106  WHERE ip="' . $Vip . '"
     107    AND occured_on > ADDTIME(NOW(), "-' . $same_conf[1] . '")
    83108    AND category_id ' . (isset($page['category']['id']) ? '= ' . $page['category']['id'] : 'IS NULL') . '
    84     AND image_id ' . (isset($page['image_id']) ? '= ' . $page['image_id'] : 'IS NULL') . ';';
     109    AND image_id ' . (isset($page['image_id']) ? '= ' . $page['image_id'] : 'IS NULL') . '
     110;';
    85111 
    86112  $result = pwg_query($query);
     
    94120    pwg_query('INSERT INTO ' . ANTIASPI_TABLE . ' (id, ip, date) values ("", "' . $Vip . '", NOW())');
    95121  }
     122
     123  $insert = '
     124INSERT
     125  INTO '.ANTIASPI_LOG_TABLE.'
     126  SET IP = \''.$Vip.'\'
     127    , occured_on = NOW()
     128    , image_id = '.(isset($page['image_id']) ? $page['image_id'] : 'NULL').'
     129    , category_id = '.(isset($page['category']['id']) ? $page['category']['id'] : 'NULL').'
     130;';
     131  pwg_query($insert);
     132
     133  // automatic purge
     134  $query = '
     135DELETE
     136  FROM '.ANTIASPI_LOG_TABLE.'
     137  WHERE occured_on < LEAST(SUBTIME(NOW(), \''.$diff_conf[1].'\'), SUBTIME(NOW(), \''.$same_conf[1].'\'))
     138;';
     139  pwg_query($query);
    96140}
    97 
    98141?>
  • extensions/AntiAspi/maintain.inc.php

    r3609 r28323  
    11<?php
    2 
    32if (!defined('PHPWG_ROOT_PATH')) die('Hacking attempt!');
    43
    5 function plugin_install()
     4class AntiAspi_maintain extends PluginMaintain
    65{
    7   global $prefixeTable;
     6  private $installed = false;
    87
    9   $query = 'SHOW TABLES LIKE "' . $prefixeTable . 'antiaspi_ip_ban"';
    10   $result = pwg_query($query);
    11   if (!mysql_fetch_row($result))
     8  function __construct($plugin_id)
    129  {
    13     $q = "CREATE TABLE " . $prefixeTable . "antiaspi_ip_ban (
    14       id int(11) NOT NULL auto_increment,
    15       ip char(50) NOT NULL default '',
    16       date char(20) NOT NULL default '',
    17       PRIMARY KEY (id),
    18       KEY ip (ip))
    19     DEFAULT CHARACTER SET utf8";
    20     pwg_query($q);
     10    parent::__construct($plugin_id);
     11  }
     12
     13  function install($plugin_version, &$errors=array())
     14  {
     15    global $prefixeTable;
     16
     17    // table antiaspi_ip_ban to store list of banned IP addresses
     18    $result = pwg_query('SHOW TABLES LIKE "'.$prefixeTable.'antiaspi_ip_ban";');
     19    if (!pwg_db_num_rows($result))
     20    {
     21      $query = "
     22CREATE TABLE " . $prefixeTable . "antiaspi_ip_ban (
     23  id int(11) NOT NULL auto_increment,
     24  ip char(50) NOT NULL default '',
     25  date char(20) NOT NULL default '',
     26  PRIMARY KEY (id),
     27  KEY ip (ip)
     28) ENGINE=MyISAM DEFAULT CHARSET=utf8
     29;";
     30      pwg_query($query);
     31    }
     32
     33    // table antiaspi_log to store a specific "short-term" log
     34    // (ENGINE=MEMORY, specific)
     35    $result = pwg_query('SHOW TABLES LIKE "'.$prefixeTable.'antiaspi_log";');
     36    if (!pwg_db_num_rows($result))
     37    {
     38      $query = "
     39CREATE TABLE " . $prefixeTable . "antiaspi_log (
     40  IP varchar(15) NOT NULL default '',
     41  occured_on datetime NOT NULL,
     42  image_id mediumint(8) default NULL,
     43  category_id smallint(5) default NULL
     44) ENGINE=MEMORY DEFAULT CHARSET=utf8
     45;";
     46      pwg_query($query);
     47    }
     48
     49    $this->installed = true;
     50  }
     51
     52  function activate($plugin_version, &$errors=array())
     53  {
     54    if (!$this->installed)
     55    {
     56      $this->install($plugin_version, $errors);
     57    }
     58  }
     59
     60  function deactivate()
     61  {
     62  }
     63
     64  function uninstall()
     65  {
     66    global $prefixeTable;
     67
     68    pwg_query('DROP TABLE '.$prefixeTable.'antiaspi_ip_ban;');
     69    pwg_query('DROP TABLE '.$prefixeTable.'antiaspi_log;');
    2170  }
    2271}
    23 
    24 function plugin_uninstall()
    25 {
    26   global $prefixeTable;
    27 
    28         $q = 'DROP TABLE ' . $prefixeTable . 'antiaspi_ip_ban;';
    29   pwg_query($q);
    30 }
    31 
    3272?>
Note: See TracChangeset for help on using the changeset viewer.