function DBCreateSiteTimeTable()
{
$c = DBConnect();
$conf = globalconf();
if ($conf["dbuser"] == "") {
$conf["dbuser"] = "******";
}
$r = DBExec($c, "\nCREATE TABLE \"sitetimetable\" (\n \"contestnumber\" int4 NOT NULL, -- (id do concurso)\n \"sitenumber\" int4 NOT NULL, -- (id do local)\n \"sitestartdate\" int4 NOT NULL, -- (hora que o local comecou)\n \"siteenddate\" int4 NOT NULL, -- (hora que o local deve terminar, zero nao terminado)\n \"updatetime\" int4 DEFAULT EXTRACT(EPOCH FROM now()) NOT NULL, -- (indica a ultima atualizacao no registro)\n-- (esta tabela contem uma linha para cada reinicio de um site do contest)\n CONSTRAINT \"sitetime_pkey\" PRIMARY KEY (\"contestnumber\", \"sitenumber\", \"sitestartdate\"),\n CONSTRAINT \"site_fk\" FOREIGN KEY (\"contestnumber\", \"sitenumber\")\n REFERENCES \"sitetable\" (\"contestnumber\", \"sitenumber\")\n ON DELETE CASCADE ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE\n)", "DBCreateSiteTimeTable(create table)");
$r = DBExec($c, "REVOKE ALL ON \"sitetimetable\" FROM PUBLIC", "DBCreateSiteTimeTable(revoke public)");
$r = DBExec($c, "GRANT ALL ON \"sitetimetable\" TO \"" . $conf["dbuser"] . "\"", "DBCreateSiteTimeTable(grant bocauser)");
$r = DBExec($c, "CREATE UNIQUE INDEX \"sitetime_index\" ON \"sitetimetable\" USING btree " . "(\"contestnumber\" int4_ops, \"sitenumber\" int4_ops, \"sitestartdate\" int4_ops)", "DBCreateSiteTimeTable(create index)");
$r = DBexec($c, "CREATE INDEX \"sitetimesite_index\" ON \"sitetimetable\" USING btree " . "(\"contestnumber\" int4_ops, \"sitenumber\" int4_ops)", "DBCreateSiteTimeTable(create site_index)");
}
function logEvent($db_handler, $eventType, $message)
{
$sql = "INSERT INTO LOG(EVENT_ID, DATE, EVENT_DESC) VALUES({$eventType}, DATETIME('NOW'), '{$message}')";
DBexec($db_handler, $sql);
}
DBexec($db_handler, $sql);
logEvent($db_handler, TYPE_OUT_ON, "Output {$out_id} turned ON by a schedule");
logMessage("IrrighinoTask - OUT_ID {$out_id} turned ON and event logged in the DB");
} else {
logEvent($db_handler, TYPE_ERROR, "Unable to turn ON output {$out_id}");
logMessage("IrrighinoTask - Error while calling Arduino to turn ON OUT_ID {$out_id}, response: {$response}");
}
}
} else {
logMessage("IrrighinoTask - No events found for this OUT_ID");
// if the output is ON, turn it OFF
if ($out_status == STATUS_ON) {
$response = callArduino("off/{$out_id}");
if (strpos($response, "OK") !== false) {
$sql = "UPDATE OUTPUTS SET OUT_STATUS = " . STATUS_OFF . " WHERE OUT_ID = {$out_id}";
DBexec($db_handler, $sql);
logEvent($db_handler, TYPE_OUT_OFF, "Output {$out_id} turned OFF by a schedule");
logMessage("IrrighinoTask - OUT_ID {$out_id} turned OFF and event logged in the DB");
} else {
logEvent($db_handler, TYPE_ERROR, "Unable to turn OFF output {$out_id}");
logMessage("IrrighinoTask - Error while calling Arduino to turn ON OUT_ID {$out_id}, response: {$response}");
}
}
}
}
// the query command returned false (= error)
} else {
logMessage("IrrighinoTask - Error while querying the DB: " . $db_handler->errorInfo()[2]);
}
DBdisconnect();
logMessage("IrrighinoTask - Completed");
<?php
require 'include.php';
logMessage("purgeOldEvents - started");
$db_handler = DBconnect();
$sql = "DELETE FROM LOG WHERE DATE <= date('now','-" . RETENTION_DAYS . " day')";
$count = DBexec($db_handler, $sql);
logMessage("purgeOldEvents - completed, deleted {$count} old events");