Menu
▾
▴
[Openautomation-svn] SF.net SVN: openautomation:[703] tools/rsslog
|
From: <j-...@us...> - 2012-02-13 11:37:32
|
Revision: 703
http://openautomation.svn.sourceforge.net/openautomation/?rev=703&view=rev
Author: j-n-k
Date: 2012-02-13 11:37:23 +0000 (Mon, 13 Feb 2012)
Log Message:
-----------
Additional files for rsslog-Plugin to CometVisu. Needed since Rev 702!
Revision Links:
--------------
http://openautomation.svn.sourceforge.net/openautomation/?rev=702&view=rev
Modified Paths:
--------------
tools/rsslog/rsslog.php
Added Paths:
-----------
tools/rsslog/rsslog_correct.pl
Modified: tools/rsslog/rsslog.php
===================================================================
--- tools/rsslog/rsslog.php 2012-02-13 07:13:14 UTC (rev 702)
+++ tools/rsslog/rsslog.php 2012-02-13 11:37:23 UTC (rev 703)
@@ -1,214 +1,248 @@
-<?php
-/*****************************************************************************/
-/* rsslog.php - A simple log message reciever and sender via RSS */
-/* */
-/* (c) 2011 by Christian Mayer */
-/* Licenced under the GPLv3 */
-/*****************************************************************************/
-
-// There are diffentent modes of operation
-// 1. Creating a new log line:
-// URL parameter "c": the content of the log
-// URL parameter "t[]": a tag for later filtering. Multiple might be given
-// URL parameter "h": a header(title) for the entry; maybe empty
-// 2. Recieve the log as RSS:
-// URL parameter "f": The (optional) filter, only log lines with a tag
-// that fit this string are sent
-// 3. Dump all the content in a HTML page:
-// URL parameter "dump" - no value needed
-// 4. Remove old content:
-// URL parameter "r": the timestamp (seconds since 1970) of the oldest log
-// line to keep
-// 5. Get content as JSON:
-// URL parameter "j"
-
-// look where to store DB
-if (is_dir('/etc/wiregate/rss'))
- $dbfile = '/etc/wiregate/rss/rsslog.db';
-else
- $dbfile = 'rsslog.db';
-
-//check if the DIRECTORY is writeable by the webserver
-$dbfile_dir = dirname($dbfile);
-if (! is_writable($dbfile_dir))
- die ("Database $dbfile not writeable! make sure the file AND " .
- "the directory are writeable by the webserver!");
-
-// create database connection
-$db = sqlite_open($dbfile, 0666, $error);
-if (!$db) die ($error);
-
-// create table if it doesn't exists
-create( $db );
-
-if( isset($_GET['c']) )
-{
- // store a new log
- $store = true;
- $log_content = $_GET['c'] ? $_GET['c'] : '<no content>';
- $log_title = $_GET['h'] ? $_GET['h'] : '';
- if( mb_detect_encoding($log_content, 'UTF-8', true) != 'UTF-8' )
- $log_content = utf8_encode($log_content);
- if( mb_detect_encoding($log_title, 'UTF-8', true) != 'UTF-8' )
- $log_title = utf8_encode($log_title);
- $log_tags = $_GET['t'] ? $_GET['t'] : array();
- if(! is_array($log_tags))
- die("wrong format - use one or more t[]= for tags");
- insert( $db, $log_content, $log_title, $log_tags );
-} else if( isset($_GET['dump']) )
-{
- $result = retrieve( $db, $log_filter );
- ?>
-<html><head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /></head><body>
-<table border="1">
- <?php
- while( sqlite_has_more($result) )
- {
- $row = sqlite_fetch_array($result, SQLITE_ASSOC );
- echo '<tr>';
- echo '<td>' . date( DATE_ATOM, $row['t'] ) . '</td>';
- echo '<td>' . $row['t'] . '</td>';
- echo '<td>' . $row['title'] . '</td>';
- echo '<td>' . $row['content'] . '</td>';
- echo '<td>' . $row['tags'] . '</td>';
- echo "</tr>\n";
- }
- ?>
-</table>
-</body></html>
- <?php
-} else if( isset($_GET['r']) )
-{
- $timestamp = $_GET['r'] ? $_GET['r'] : '';
- delete( $db, $timestamp );
-} else if( isset($_GET['j']) )
-{
- ?>
-{
- "responseData" : {
- "feed" : {
- "feedUrl": "<?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?>",
- "title": "RSS supplied logs",
- "link": "<?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?>",
- "author": "",
- "description": "RSS supplied logs",
- "type": "rss20",
- "entries": [
-<?php
- $result = retrieve( $db, $log_filter );
- $first = true;
- while( sqlite_has_more($result) )
- {
- $row = sqlite_fetch_array($result, SQLITE_ASSOC );
- if( !$first ) echo ",\n";
- echo '{';
- echo '"title": "' . $row['title'] . '",';
- echo '"content": "' . $row['content'] . '",';
- echo '"publishedDate": "' . date( DATE_ATOM, $row['t'] ) . '"';
- echo '}';
- $first = false;
- }
-?>
- ]
- }
- },
- "responseDetails" : null,
- "responseStatus" : 200
-}
- <?php
-} else {
- // send logs
- $log_filter = $_GET['f'] ? $_GET['f'] : '';
-
- // retrieve data
- $result = retrieve( $db, $log_filter );
- echo '<?xml version="1.0"?>';
- ?>
-<rss version="2.0">
- <channel>
- <title>RSS supplied logs</title>
- <link><?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?></link>
- <description>RSS supplied logs</description>
- <?php
- // echo '<description>foo</description>';
- while( sqlite_has_more($result) )
- {
- $row = sqlite_fetch_array($result, SQLITE_ASSOC );
- if ($row['tags'])
- $tags = ' [' . $row['tags'] . ']';
- echo '<item>';
- echo '<title>' . $row['title'] . $tags . '</title>';
- echo '<description>' . $row['content'] . '</description>';
- echo '<pubDate>' . date( DATE_ATOM, $row['t'] ) . '</pubDate>';
- echo '</item>' . "\n";
- }
- ?>
- </channel>
-</rss>
- <?php
-}
-
-sqlite_close($db);
-
-///////////////////////////////////////////////////////////////////////////////
-// create tables if they don't exist
-function create( $db )
-{
- $q = "SELECT name FROM sqlite_master WHERE type='table' AND name='Logs';";
- $result = sqlite_query( $db, $q, SQLITE_NUM );
- if (!$result) die("Cannot execute query. $q");
- if( !sqlite_has_more($result) )
- {
- // no table found - create it
- $q = 'CREATE TABLE Logs(' .
- ' id INTEGER PRIMARY KEY,' .
- ' title TEXT,' .
- ' content TEXT NOT NULL,' .
- ' tags TEXT,' .
- ' t TIMESTAMP' .
- ');';
- $ok = sqlite_exec($db, $q, $error);
-
- if (!$ok)
- die("Cannot execute query $q. $error");
- }
-}
-
-// insert a new log line
-function insert( $db, $content, $title, $tags )
-{
- // store a new log line
- $q = 'INSERT INTO Logs(content, title, tags, t) VALUES( ' .
- " '" . sqlite_escape_string( $content ) . "'," .
- " '" . sqlite_escape_string( $title ) . "'," .
- " '" . sqlite_escape_string( implode(",",$tags) ) . "'," .
- " datetime('now')" .
- ')';
-
- $ok = sqlite_exec($db, $q, $error);
-
- if (!$ok)
- die("Cannot execute query. $error (Content: $content Tags: $tags");
-}
-
-// return a handle to all the data
-function retrieve( $db, $filter )
-{
-// $q = "SELECT content, strftime('%s', t, 'localtime') AS t FROM Logs";
- $q = "SELECT title, content, tags, strftime('%s', t) AS t FROM Logs " .
- "WHERE tags LIKE '%" . sqlite_escape_string($filter) . "%' " .
- "ORDER by t DESC";
- return sqlite_query( $db, $q, SQLITE_ASSOC );
-}
-
-// delete all log lines older than the timestamp
-function delete( $db, $timestamp )
-{
- //$q = "DELETE from Logs WHERE t < datetime($timestamp, 'unixepoch', 'localtime')";
- $q = "DELETE from Logs WHERE t < datetime($timestamp, 'unixepoch')";
- $ok = sqlite_exec($db, $q, $error);
-
- if (!$ok)
- die("Cannot execute query. $error");
-}
-?>
+<?php
+/*****************************************************************************/
+/* rsslog.php - A simple log message reciever and sender via RSS */
+/* */
+/* (c) 2011 by Christian Mayer */
+/* Licenced under the GPLv3 */
+/*****************************************************************************/
+
+// There are diffentent modes of operation
+// 1. Creating a new log line:
+// URL parameter "c": the content of the log
+// URL parameter "t[]": a tag for later filtering. Multiple might be given
+// URL parameter "h": a header(title) for the entry; maybe empty
+// 2. Receive the log as RSS:
+// URL parameter "f": The (optional) filter, only log lines with a tag
+// that fit this string are sent
+// URL parameter "state": get only rows with state=value
+// 3. Dump all the content in a HTML page:
+// URL parameter "dump" - no value needed
+// 4. Remove old content:
+// URL parameter "r": the timestamp (seconds since 1970) of the oldest log
+// line to keep
+// 5. Get content as JSON:
+// URL parameter "j"
+// 6. Update state:
+// URL parameter "u" id of row
+// URL parameter "state": new state
+
+
+// look where to store DB
+if (is_dir('/etc/wiregate/rss'))
+ $dbfile = '/etc/wiregate/rss/rsslog.db';
+else
+ $dbfile = 'rsslog.db';
+
+//check if the DIRECTORY is writeable by the webserver
+$dbfile_dir = dirname($dbfile);
+if (! is_writable($dbfile_dir))
+ die ("Database $dbfile not writeable! make sure the file AND " .
+ "the directory are writeable by the webserver!");
+
+// create database connection
+$db = sqlite_open($dbfile, 0666, $error);
+if (!$db) die ($error);
+
+// create table if it doesn't exists
+create( $db );
+
+if( isset($_GET['c']) )
+{
+ // store a new log
+ $store = true;
+ $log_content = $_GET['c'] ? $_GET['c'] : '<no content>';
+ $log_title = $_GET['h'] ? $_GET['h'] : '';
+ if( mb_detect_encoding($log_content, 'UTF-8', true) != 'UTF-8' )
+ $log_content = utf8_encode($log_content);
+ if( mb_detect_encoding($log_title, 'UTF-8', true) != 'UTF-8' )
+ $log_title = utf8_encode($log_title);
+ $log_tags = $_GET['t'] ? $_GET['t'] : array();
+ if(! is_array($log_tags))
+ die("wrong format - use one or more t[]= for tags");
+ insert( $db, $log_content, $log_title, $log_tags );
+} else if( isset($_GET['dump']) )
+{
+ $result = retrieve( $db, $log_filter, '' );
+ ?>
+<html><head><meta http-equiv="Content-Type" content="text/html;charset=utf-8" /></head><body>
+<table border="1">
+ <?php
+ while( sqlite_has_more($result) )
+ {
+ $row = sqlite_fetch_array($result, SQLITE_ASSOC );
+ echo '<tr>';
+ echo '<td>' . $row['id'] . '</td>';
+ echo '<td>' . date( DATE_ATOM, $row['t'] ) . '</td>';
+ echo '<td>' . $row['t'] . '</td>';
+ echo '<td>' . $row['title'] . '</td>';
+ echo '<td>' . $row['content'] . '</td>';
+ echo '<td>' . $row['tags'] . '</td>';
+ echo '<td>' . $row['state'] . '</td>';
+ echo "</tr>\n";
+ }
+ ?>
+</table>
+</body></html>
+ <?php
+} else if( isset($_GET['r']) )
+{
+ $timestamp = $_GET['r'] ? $_GET['r'] : '';
+ delete( $db, $timestamp );
+} else if( isset($_GET['j']) )
+{
+ ?>
+{
+ "responseData" : {
+ "feed" : {
+ "feedUrl": "<?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?>",
+ "title": "RSS supplied logs",
+ "link": "<?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?>",
+ "author": "",
+ "description": "RSS supplied logs",
+ "type": "rss20",
+ "entries": [
+<?php
+ $result = retrieve( $db, $log_filter, '' );
+ $first = true;
+ while( sqlite_has_more($result) )
+ {
+ $row = sqlite_fetch_array($result, SQLITE_ASSOC );
+ if( !$first ) echo ",\n";
+ echo '{';
+ echo '"title": "' . $row['title'] . '",';
+ echo '"content": "' . $row['content'] . '",';
+ echo '"publishedDate": "' . date( DATE_ATOM, $row['t'] ) . '"';
+ echo '}';
+ $first = false;
+ }
+?>
+ ]
+ }
+ },
+ "responseDetails" : null,
+ "responseStatus" : 200
+}
+<?php
+} else if ( isset($_GET['u']) ) {
+ $id = $_GET['u'];
+ if (!is_numeric($id))
+ die("wrong format - id has to be numeric");
+ $newstate = $_GET['state'];
+ if (!is_numeric($newstate))
+ die("wrong format - state is required and has to be numeric");
+ updatestate( $db, $id, $newstate );
+?>
+Successfully updated ID=<?php echo $id; ?>.
+<?php
+} else {
+ // send logs
+ $log_filter = $_GET['f'] ? $_GET['f'] : '';
+ $state = $_GET['state']; // ? $_GET['state'] : '';
+
+ // retrieve data
+ $result = retrieve( $db, $log_filter, $state );
+ echo '<?xml version="1.0"?>';
+ ?>
+<rss version="2.0">
+ <channel>
+ <title>RSS supplied logs <?php echo $state; echo $log_filter; ?></title>
+ <link><?php echo 'http://' . $_SERVER['HTTP_HOST'] . $_SERVER['SCRIPT_NAME']; ?></link>
+ <description>RSS supplied logs</description>
+ <?php
+ // echo '<description>foo</description>';
+ while( sqlite_has_more($result) )
+ {
+ $row = sqlite_fetch_array($result, SQLITE_ASSOC );
+ $tags = ' [ id=' . $row['id']. ',state=' . $row['state'];
+ if ($row['tags'])
+ $tags .= ',' . $row['tags'];
+ $tags .= ' ]';
+ echo '<item>';
+ echo '<title>' . $row['title'] . $tags . '</title>';
+ echo '<description>' . $row['content'] . '</description>';
+ echo '<pubDate>' . date( DATE_ATOM, $row['t'] ) . '</pubDate>';
+ echo '</item>' . "\n";
+ }
+ ?>
+ </channel>
+</rss>
+ <?php
+}
+
+sqlite_close($db);
+
+///////////////////////////////////////////////////////////////////////////////
+// create tables if they don't exist
+function create( $db )
+{
+ $q = "SELECT name FROM sqlite_master WHERE type='table' AND name='Logs';";
+ $result = sqlite_query( $db, $q, SQLITE_NUM );
+ if (!$result) die("Cannot execute query. $q");
+ if( !sqlite_has_more($result) )
+ {
+ // no table found - create it
+ $q = 'CREATE TABLE Logs(' .
+ ' id INTEGER PRIMARY KEY,' .
+ ' title TEXT,' .
+ ' content TEXT NOT NULL,' .
+ ' tags TEXT,' .
+ ' t TIMESTAMP' .
+ ' state INT' .
+ ');';
+ $ok = sqlite_exec($db, $q, $error);
+
+ if (!$ok)
+ die("Cannot execute query $q. $error");
+ }
+}
+
+// insert a new log line
+function insert( $db, $content, $title, $tags )
+{
+ // store a new log line
+ $q = 'INSERT INTO Logs(content, title, tags, t) VALUES( ' .
+ " '" . sqlite_escape_string( $content ) . "'," .
+ " '" . sqlite_escape_string( $title ) . "'," .
+ " '" . sqlite_escape_string( implode(",",$tags) ) . "'," .
+ " datetime('now')" .
+ ')';
+
+ $ok = sqlite_exec($db, $q, $error);
+
+ if (!$ok)
+ die("Cannot execute query. $error (Content: $content Tags: $tags");
+}
+
+// return a handle to all the data
+function retrieve( $db, $filter, $state )
+{
+// $q = "SELECT content, strftime('%s', t, 'localtime') AS t FROM Logs";
+ $q = "SELECT id, title, content, tags, state, strftime('%s', t) AS t FROM Logs " .
+ "WHERE tags LIKE '%" . sqlite_escape_string($filter) . "%' ";
+ if (isset($state))
+ $q .= " AND state=" . $state . " ";
+
+ $q .= "ORDER by t DESC";
+ return sqlite_query( $db, $q, SQLITE_ASSOC );
+}
+
+// delete all log lines older than the timestamp
+function delete( $db, $timestamp )
+{
+ //$q = "DELETE from Logs WHERE t < datetime($timestamp, 'unixepoch', 'localtime')";
+ $q = "DELETE from Logs WHERE t < datetime($timestamp, 'unixepoch')";
+ $ok = sqlite_exec($db, $q, $error);
+
+ if (!$ok)
+ die("Cannot execute query. $error");
+}
+
+function updatestate( $db, $id, $newstate)
+{
+ $q = 'UPDATE Logs SET state=' . $newstate . ' WHERE id=' . $id . ';';
+ $ok = sqlite_exec($db, $q, $error);
+
+ if (!$ok)
+ die("Cannot execute query. $error");
+}
+?>
Added: tools/rsslog/rsslog_correct.pl
===================================================================
--- tools/rsslog/rsslog_correct.pl (rev 0)
+++ tools/rsslog/rsslog_correct.pl 2012-02-13 11:37:23 UTC (rev 703)
@@ -0,0 +1,64 @@
+#!/usr/bin/perl
+#
+# (c) 2011 by Jan N. Klug
+# Licenced under the GPLv3
+#
+# add state-column to rsslog-database
+# use ONLY if your databse is created with rsslog.php before Rev. 703!
+#
+# usage: ./rsslog_correct.pl
+# change path to database !
+
+# config
+my $logdb = '/var/www/rsslog.db';
+
+# ab hier nichts aendern
+
+#allgemeine Deklarationen
+use strict;
+use DBI;
+
+# Datenbank \xF6fnnen
+if (! -e $logdb) {
+ die "$logdb existiert nicht! Bitte mit rsslog.php anlegen"; # FIXME: create sqlite-db
+}
+
+my $dbargs = {AutoCommit => 0, PrintError => 1};
+my $dbh = DBI->connect("dbi:SQLite2:dbname=$logdb", "", "", $dbargs)
+ or die "Couldn't open database: " . DBI->errstr;
+
+# temp db erzeugen
+my $sqlquery = 'CREATE TEMPORARY TABLE LTemp(title TEXT, content TEXT NOT NULL, tags TEXT, t TIMESTAMP);';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# copy to temp database
+$sqlquery = 'INSERT INTO LTemp SELECT title, content, tags, t FROM Logs;';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# alte db loeschen
+$sqlquery = 'DROP TABLE Logs;';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# neue db erzeugen
+my $sqlquery = 'CREATE TABLE Logs(id INTEGER PRIMARY KEY, title TEXT, content TEXT NOT NULL, tags TEXT, t TIMESTAMP, state INTEGER DEFAULT 0);';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# zurueckkopieren
+$sqlquery = 'INSERT INTO Logs (title, content, tags, t) SELECT title, content, tags, t FROM LTemp;';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# temp database loeschen
+$sqlquery = 'DROP TABLE LTemp;';
+$dbh->do($sqlquery)
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+# erst schreiben, wenn alles funktioniert hat
+$dbh->commit()
+ or die "Couldn't execute command: " . $dbh->errstr;
+
+$dbh->disconnect();
Property changes on: tools/rsslog/rsslog_correct.pl
___________________________________________________________________
Added: svn:executable
+ *
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
