Re: [Phpwiki-talk] Two new queries on search engine activity for AnalyseAccessLog plugin

[Charles C. <ch...@ru...>]

On Mon, February 7, 2005 18:39, Reini Urban said:
> Charles Corrigan schrieb:
>> Here are two new queries that I wrote to analyse search engine activity
>> from the SQL access log. These can be inserted into the
>> AnalyseAccessLog.inc file that I uploaded last week - the insertion
>> point
>> should be just before the final "}".
>
> I see now why you wanted to exclude the queries, but I still don't feel
> comfortable with the seperate .inc file.

The main reason that I wanted to put the queries in a separate file is
that different database engines have different dialects of SQL and I know
that some queries that I have written will only work with MySQL.

The secondary reason for a separate file was to document the query file's
API simply and completely enough to allow non-PhpWiki or even non-PHP
programmers to alter the existing queries to make them work with their
database engine or to add new queries.

Can you suggest an alternative mechanism to achieve what I am getting at?

> That would be the first and only case, and there are several file-based
> methods to list all our plugins (checking for .php, well).
> it violates our current (unwritten) plugin policies.
>
> Also with the name change from mode to queryName.
> I think we want to keep "mode" for consistency.

I thought long and hard about that before making the change as it really
is a change in API. Again, to help people that are not familiar with
programming PHP, I wanted
a) the API name to match the parameter name and
b) the API name to make sense

>
>> The queries contain a list of DNS
>> names for search engine crawlers/spiders/whatever - but this is based
>> only
>> on what I saw in my sites' accesslog. Any additions are welcome.
>
>> } elseif ($queryName=="Search Bots") {
> ...
>>         ."FROM $accesslog "
>>         ."WHERE (remote_host LIKE '%googlebot.com' "
>>         ."OR remote_host LIKE '%alexa.com' "
>>         ."OR remote_host LIKE '%inktomisearch.com' "
>>         ."OR remote_host LIKE '%msnbot.msn.com') "
>>         .($whereConditions ? 'AND '.$whereConditions : '')
>>         ."GROUP BY 'Time Scale', 'Remote Host'";
>
> For search bots you can constrcut the hosts string from
> lib/ExternalReferrer.php

I will look into this, thanks.


>>     $now = time();
>>     $query = "SELECT "
>>         ."IF($now-time_stamp<60, '0 - last minute', IF($now
>> -time_stamp<3600, '1 - 1 minute to 1 hour',
>> IF($now-time_stamp<86400, '2 - 1 hour to 1 day',
>> IF($now-time_stamp<604800, '3 - 1 day to 1 week',
>> IF($now-time_stamp<2629800, '4 - 1 week to 1 month',
>> IF($now-time_stamp<31557600, '5 - 1 month to 1 year', '6 - more
>
> All these are untranslated strings.

Good point, I will see what I can do - to get it "right" may require some
support in the main php file.

regards,
Charles