CREATE TABLE blogs (
    link varchar(255) not null,
    title varchar(255) not null,
    description text,
    author varchar(128) not null,
    cats varchar(255) not null,
    PRIMARY KEY (link)
    );

INSERT INTO blogs (link, title, description, author, cats) VALUES ('http://planet-php.org/rdf/', 'Planet PHP', 'Planet PHP', 'Misc', '');

CREATE TABLE entries (
    bloglink varchar(255) not null,
    title text,
    link  text,
    description text,
    dc_date integer,
    dc_creator varchar(128) not null,
    content_encoded text,
    changed integer,
    md5 char(32) not null,
    PRIMARY KEY (link)
    );
CREATE INDEX idx_entries_bloglink on entries(bloglink);

<?php
include 'config.php';
include 'magpierss/rss_fetch.inc';

class rss2mail_aggregator {
    private $db;

    function __construct() {
        $this->db = new PDO(RSS2MAIL_DSN, RSS2MAIL_USER, RSS2MAIL_PW);
        $this->db->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_EXCEPTION);
    }

    function aggregate() {
        $get_entry = $this->db->prepare("select * from entries where link = ?");
        $insert_entry = $this->db->prepare("insert into entries (
                bloglink, title, link, description,
                dc_date, dc_creator, content_encoded,
                changed, md5 )
            VALUES (
                :bloglink, :title, :link, :description,
                :dc_date, :dc_creator, :content_encoded,
                :changed, :md5
            )");
        $update_entry = $this->db->prepare("update entries set
                bloglink = :bloglink,
                title = :title,
                description = :description,
                dc_date = :dc_date,
                dc_creator = :dc_creator,
                content_encoded = :content_encoded,
                changed = :changed,
                md5 = :md5
            WHERE
                link = :link");

        $q = $this->db->prepare("select link, cats from blogs");
        $q->execute();

        foreach ($q->fetchAll() as $row) {
            $feed = fetch_rss($row['link']);
            echo "Considering $row[link]\n";
            if (!$feed) continue;
            if (!$feed->channel['link']) continue;
            foreach ($feed->items as $item) {
                $item['md5'] = $this->compute_md5($item);

                $get_entry->execute(array($item['link']));
                $entry = $get_entry->fetch(PDO_FETCH_ASSOC);

                $action = $update_entry;

                if (!$entry) {
                    echo "A new entry: $item[title]\n";
                    /* set up category info */
                    if (isset($item['dc']['subject'])) {
                        $item['category'] = $item['dc']['subject'];
                    }
                    if ($row['cats']) {
                        $cats = explode(',', $row['cats']);
                        $hit = false;
                        foreach ($cats as $cat) {
                            if (strpos($item['category'], $cat) !== false) {
                                $hit = true;
                                break;
                            }
                        }
                        if (!$hit) {
                            continue;
                        }
                    }
                    $action = $insert_entry;
                } else {
                    /* it already existed.  Did it change ? */
                    if ($item['md5'] == $entry['md5']) {
                        /* no, it's the same */
                        echo "unchanged entry: $item[title]\n";
                        continue;
                    }
                    echo "changed entry: $item[title]\n";
                }
                /* build up the record */
                $record = array(
                    ':bloglink' => $row['link'],
                    ':title' => $this->sanitize($item['title']),
                    ':link' => $item['link'],
                    ':description' => $this->sanitize($item['description']),
                    ':dc_date' => $this->dc_to_timestamp($item),
                    ':dc_creator' => $item['dc']['creator'],
                    ':content_encoded' => $this->sanitize($item['content']['encoded']),
                    ':changed' => 1, /* always changed */
                    ':md5' => $item['md5'],
                );
                $action->execute($record);
            }
        }
    }

    function sanitize($text) {
        return trim($text);
    }

    function compute_md5($item) {
        return md5($item['title'] . $item['description'] . $item['content']['encoded']);
    }

    function dc_to_timestamp($item) {
        static $keys = array('pubdate', 'issued', 'created', 'modified');

        if (isset($item['dc']['date'])) {
            $value = $item['dc']['date'];
        } else {
            $value = null;
            foreach ($keys as $key) {
                if (isset($item[$key])) {
                    $value = $item[$key];
                    break;
                }
            }
        }
        return $value ? strtotime($value) : time();
    }
}

$agg = new rss2mail_aggregator;
$agg->aggregate();