SQLITE3利用したログ記録

ログ記録

SQLITE3利用したログ記録


<?php
//logo
session_name('log');
session_start();
error_reporting(E_ALL);
date_default_timezone_set('Asia/Tokyo');
//include_once('acclog.php');

$hi = new acclog();
if(count(get_included_files()) == 1){
$hi->viewlog();
}

class acclog{
public $db_name = "acclog.db";
// select datetime(request_time,"localtime") from log;

public function acclog(){
if(!file_exists($this->db_name)){
$this->create_acclog_db();
}

if(filesize($this->db_name) > 6438912){
$this->diet();
}

$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$temp = $db->prepare("insert into log ('remote_addr','http_user_agent','request_uri','http_accept_language') values (:a,:b,:c,:d)");
$temp->bindValue(':a',$_SERVER['REMOTE_ADDR'],SQLITE3_TEXT);
$temp->bindValue(':b',$this->user_agent($_SERVER['HTTP_USER_AGENT']),SQLITE3_TEXT);
$temp->bindValue(':c',$_SERVER['REQUEST_URI'],SQLITE3_TEXT);
$temp->bindValue(':d',$_SERVER['HTTP_ACCEPT_LANGUAGE'],SQLITE3_TEXT);
$temp->execute();
$db->close();
}

private function user_agent($agent){
$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$sql_select = sprintf("select rowid from user_agent where http_user_agent = \"%s\"",$agent);
$sql_insert = sprintf("insert into user_agent (http_user_agent) values (\"%s\")",$agent);
$temp = $db->querySingle($sql_select);
if(is_null($temp)){
$db->exec($sql_insert);
$temp = $db->querySingle($sql_select);
$db->close();
return $temp;
}else{
$db->close();
return $temp;
}

$db->close();
}

public function viewlog(){
$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$temp = $db->prepare("select rowid,http_user_agent from user_agent order by http_user_agent asc");
$temp2 = $temp->execute();
while($temp3 = $temp2->fetchArray(SQLITE3_ASSOC)){
$temp4 = $this->user_agent_count($temp3['rowid']);
if($temp4 != 0){
echo "<div>{$temp3['rowid']}::{$temp3['http_user_agent']}</div>";
echo "<div>{$temp4}</div>";
echo "<div><br></div>";
}
}
$db->close();
}

private function user_agent_count($rowid){
$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$temp = $db->prepare("select count(*) as count from log where http_user_agent = :a");
$temp->bindValue(':a',$rowid);
$temp2 = $temp->execute();

while($temp3 = $temp2->fetchArray(SQLITE3_ASSOC)){
$db->close();
return $temp3['count'];
}
$db->close();
}


private function diet(){
$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$temp = $db->prepare("delete from log where request_time < datetime('now','-7 days')");
$temp->execute();
$db->exec('VACUUM');
$db->close();
}


public function create_acclog_db(){
$db = new sqlite3($this->db_name);
$db->busyTimeout(10000);
$create_db =
"create table log(
request_time text default current_timestamp,
remote_addr text,
http_user_agent text,
request_uri text,
http_accept_language text
)";

$db->exec($create_db);

$create_db =
"create table user_agent(
http_user_agent text
)";

$db->exec($create_db);
$db->close();
}
}



//old
function mf_log($log=null){
error_reporting(0);
date_default_timezone_set('Asia/Tokyo');
$file=basename(__FILE__);
$log_server[$file]=date('c');
$log_server['ip']=$_SERVER['REMOTE_ADDR'];
$log_server['uag']=$_SERVER['HTTP_USER_AGENT'];
$log_server['lg']=$_SERVER['HTTP_ACCEPT_LANGUAGE'];
$log_server['url']=$log;

$log_str='<?php //';
foreach ($log_server as $key=>$val){
$log_str=$log_str.$key.'>'.$val.', ';
unset($val);
unset($key);
}
$log_str=$log_str.'// ?>'."\r\n";
file_put_contents('log_itunes.php', $log_str, FILE_APPEND | LOCK_EX);
}


?>