Subject: Feeds Integration

From: David Costa <[email-masked]>
Date: Thu, 19 Feb 2004 16:49:11 +0100

Hello All,
I plan to move forward with a gborg and events feed. This as a nice 
addition of the existing one (beta version on dotgeek.org).

Problem, for the beta news, I had to do a separate scripts to fetch the 
news because I don't have them in a database, then add these locally 
and fetch them
for the XML feed.

(how ?

$handle = fopen("http://postgresql.dotgeek.org/", "r");
$file_handler = "";
while (!feof($handle))
	$file_handler .= fgets($handle, 4096);
if (!ereg("<!-- News -->(.+)<!-- Events -->", $file_handler, $news))
	print "error";
$news = str_replace("<tr>\n<td bgcolor=\"#6884A4\">\n<a 
name=\"news\">\n<font size=\"+2\" 
color=\"#FFFFFF\">News</font></a>\n</td>\n</tr>", "", $news[1]);
$news = ereg_replace("(\n){3,}", "\n", $news);
/*print $news;
print "\n\n\n-------------------------------------------\n\n\n\n";*/
$pattern = "<tr><td><a href=\"(news/[0-9]+\.html)\" 
style=\"font-weight: bold; font-size: medium; color: #020169\">";
$pattern .= "([^(</)]+)</a><br>\n<i>Posted on ([0-9]+-[0-9]+-[0-9]+) by 
<font color=\"#020169\">([^(</)]+)</font>";
$pattern .= "</i><br>\n([^(</)]+)</td></tr>\n";
$rinfo = array();
$i = 0;
etc etc )

Now, it would be useless to do the same exercise considered that you do 
have them in a database. So if I know the db schema, I could possibly 
have it to work locally
or upload on my space on your server (thanks Marc !!) something ready 
to go.

Ideally (this would work best for me) can someone move on my server 
space with you the tables Latest News (gborg) and (events) ? With these 
I can do a full test on my box and
upload something ready to go.

For the website integration, I can handle that too but..can I work on 
Alexey's beta ? This would allow me to add the feeds links without 
having to download
the full site with a CVS checkout and upload something which will have 
in any case be modified for Alexey's  new layout.

Of course if you want to implement the news feeds sooner, I could do a 
fresh checkout, upload, add the changes and leave it to you.

Thanks again for your time and attention ;)
Cheers
David

---

From: Chris Ryan <[email-masked]>
Date: Thu, 19 Feb 2004 09:32:36 -0800 (PST)

David,

    For GBorg specifically the news items are stored on the database. I
forget how it was setup for the main website to access the database
however I can provide a query that would give you the information you
are looking for so you can write a program. Having an RSS feed on GBorg
would be great too. If you have something I can you to generate the
files that would be great.

    Here is a query for getting the 10 latest news items for showing on
the main page of GBorg.

SELECT project_name, 
       headline,
       body,
       submit_date   -- date not datetime :(
  FROM project_news, project
 WHERE (project_news.project_id=project.project_id)
   AND (project_news.active=true)
   AND (project_news.on_homepage=true)
 ORDER BY submit_date DESC
 LIMIT 10;

    I will leave issues of access to the GBorg database up to Marc.

Chris Ryan

--- David Costa <[email-masked]> wrote:
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
>       joining column's datatypes do not match

__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools

---

From: Josh Berkus <[email-masked]>
Date: Thu, 19 Feb 2004 09:55:17 -0800

David,

> addition of the existing one (beta version on dotgeek.org).

If it makes any difference, we are currently discussing the possibility of a 
gradual migration from GBorg to GForge.   More discussion to come on -hackers 
and this list later.

Second, we have a reason to want to *import* and RSS feed.   
Opensourcexperts.com is ready to do PostgreSQL job listings, and they will 
publish them to us via RSS.   Can you help with this once you're done with 
our RSS export?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco

---

From: David Costa <[email-masked]>
Date: Thu, 19 Feb 2004 19:03:16 +0100

> David,

Hi Chris! ;)

> would be great too.

yes I am doing it, and I try to avoid the same str_replace stuff, so db 
is the way to go.

> If you have something I can you to generate the

well I have it done, will work with any database data in principle.

>  ORDER BY submit_date DESC

Umh, so what about the link ? is it part of the body ? if that is the 
case it might be a problem, can solve that, just not the fast way to
produce the xml from db data.

>     I will leave issues of access to the GBorg database up to Marc.

I can do it without access to gborg.(In my previous email I was under 
the impression that you had a separate table for news, but this is 
obviously not the case)
Without db access I can still do  the script and add some quick 
comments to set it up the right way.

For now the query is fine, but I need to know where the link is stored, 
on which row.  for example I used something like this for the main news
array("title" => $row["title"], "link" => $row["url"], "description" => 
$row['news'], "author" => $row["author"] , "date" => date("d-M-Y", 
$row["date"]));
see I keep on the beta database the url as a separate entity assigned 
to the link field in the XML generation.

For link I mean the "click here for the full story." thing.

Cheers
David Costa

>> $news = str_replace("<tr>\n<td DEFANGED_bgcolor='\"#6884A4\"'>\n<a
>> name=\"news\">\n<font DEFANGED_size='\"+2\"'
>> <font DEFANGED_color='\"#020169\"'>([^(</)]+)</font>";

---

From: Chris Ryan <[email-masked]>
Date: Thu, 19 Feb 2004 10:49:44 -0800 (PST)

> produce the xml from db data.

    Here is an updated query. The URL had slipped my mind as one I
build the URLs from knows practices on GBorg and the body is actually
the full story. You'll note as well in the query below that I added in
for getting the submitter of the news item.

SELECT headline AS title,
      
'http://gborg.postgresql.org/project/'||project_name||'/news/newsfull.php?news_id='||news_id
AS link, -- the fully built url to the news item
       body,   -- the full news text you may want to abbreviate here
       member_name AS author, -- member name on gborg of author
       submit_date   -- date not datetime :(
  FROM project_news, project, member
 WHERE (project_news.project_id=project.project_id)
   AND (project_news.member_id=member.member_id)
   AND (project_news.active=true)
   AND (project_news.on_homepage=true)
 ORDER BY submit_date DESC
 LIMIT 10;

Chris Ryan

---

From: David Costa <[email-masked]>
Date: Thu, 19 Feb 2004 19:52:59 +0100

> gradual migration from GBorg to GForge.   More discussion to come on  

Umh no difference to me unless you change the db schema. Even then, I  
will just need to move things around in the query then I am done.
> publish them to us via RSS.

Looks like a great add on, kudos

>  Can you help with this once you're done with
> our RSS export?
>

by all means. Parsing an existing XML feed is fairly simple©
Amazing how important and useful are XML feeds to open source projects.  
I suspect that many news sites like slashdot generate a huge amount of
traffic from their XML syndication.  Perhaps we might even get some of  
the major sites like O'Reilly to use our feeds. Worth a try;)

All the best
David Costa
P.S. am I right assuming that all our discussions are live online on  
the news list comp.db.postgresql.advocacy ?
seems so at
http://news.gmane.org/group/gmane.comp.db.postgresql.advocacy/last=/ 
force_load=t
just curious

---

From: David Costa <[email-masked]>
Date: Thu, 19 Feb 2004 21:29:37 +0100

>  ORDER BY submit_date DESC

Okay. I will need a sample db table table if you want me to test it.  
Else I don't physically have where to run the query :D

Or perhaps I do the script, leave it on my webspace and let you/Marc or  
Dave to try this out. That would work for me, I don't really need any  
further access. Without the db I can
still do the script ready to run but I cannot test if the query returns  
what we would expect or not.

Thanks again for your support,
cheers
David Costa

---

From: Chris Ryan <[email-masked]>
Date: Thu, 19 Feb 2004 12:43:10 -0800 (PST)

If you provide a script I can work through any specific issues
during implementation.

Chris Ryan

--- David Costa <[email-masked]> wrote:
> TIP 4: Don't 'kill -9' the postmaster

---

From: David Costa <[email-masked]>
Date: Fri, 20 Feb 2004 15:26:03 +0100

>     If you provide a script I can work through any specific issues

Okay I will email you directly re: implementation :D

---

From: David Costa <[email-masked]>
Date: Fri, 20 Feb 2004 15:41:47 +0100

Sorry Chris,
something missing on the query.
You need a title too, like pgin.tcl-2.0.0 released
title, link, description, date.

Now the link is there, description will be the body, date whatever we  
have but we need the title too. Bottom line,
I need something AS title too.

In preparation of our tests (I will send the scripts to you) you need  
to do this:

pear install XML_RPC
and then pear list to check if pear DB is there too (just in case).

Let me know when you are done ;)

Cheers
David

---

From: Chris Ryan <[email-masked]>
Date: Fri, 20 Feb 2004 06:48:53 -0800 (PST)

> title, link, description, date.

    I am confused. The first line of the query I sent you, as quoted
below, has a title in it.

> > SELECT headline AS title,

    Everything else you mentioned is completed.

Chris Ryan