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");