Tuesday work -- generate OPML from info in database

[scripting]

Next mini project is to generate a user's OPML file from the info in the database.

Recall there are two tables, subscriptions and feeds.

Each record in subscriptions has three bits of info: feedurl, username and listname.

Each record in feeds has a bunch of info about the feed, for now what I want is title, htmlurl and description.

So for each unique feedurl that the user is subscrbed to, I want the title, htmlurl and description.

From that I can easily generate the OPML.

I tried formulating a query based on the hotlist query.

SELECT subscriptions.feedurl, feeds.title, feeds.htmlurl, COUNT(subscriptions.feedurl) AS countSubs FROM subscriptions, feeds WHERE subscriptions.feedurl = feeds.feedurl GROUP BY feedurl ORDER BY countSubs DESC LIMIT 100;

But I haven't gotten anything that works yet.

Help appreciated. ;-)

[jystervinou]

Based on:

create table subscriptions (username varchar (255), listname varchar (255), feedurl varchar (512), whenupdated datetime, PRIMARY KEY (feedurl, username));

create table feeds (feedurl varchar (512), title varchar (255), htmlurl varchar (512), description varchar (512), whenupdated datetime, PRIMARY KEY (feedurl));

Let's try this:

SELECT
  s.feedurl,
  f.title,
  f.description
FROM
  subscriptions AS s,
  feeds AS f
WHERE
  s.feedurl = f.feedurl
AND
  s.username = 'mickeymouse'
ORDER BY s.whenupdated DESC

Note: not sure what listname is for and how it's used (ie: if there is only one listname per (username, feedurl), that data ("listname") will be repeated in each line?)

[scripting]

Thank you. It worked.

SELECT s.feedurl, f.title FROM subscriptions AS s, feeds AS f WHERE s.feedurl = f.feedurl AND s.username = 'davewiner' ORDER BY s.whenupdated DESC;

Re listname, initially I designed it to have multiple files per user, then decided one file per user. But I didn't take out the ability to have multiple files. Maybe at some point we will have them. ;-)

PS: Did you listen to the audio message I sent Andrew?

[scripting]

Also the listname will be used for the initial import. The first version of SYO had multiple files. So I have a bunch of OPML files from people, in many cases, people with multiple files. Might as well record which file the subscription came from. Doesn't use up much space, I guess.

BTW, when you create a table, I assume if a field only has 28 characters it doesn't allocate 255 (if that's the max field size). I hope! Maybe they do???

[scripting]

I'm preparing a source code release.

It's time to ask for code reviews. There's still a bunch of work to do, and thinking about how this should work. I don't want to over-think, but I don't want to under-think either.

Also I've started a doc called Groundrules.

[scripting]

The source is up. Version 0.4.10.

[scripting]

You can try the app here.

http://dev.opml.org/share2/

Warning: The database will be wiped before long, anything you upload is strictly for experimenting.

[jystervinou]

PS: Did you listen to the audio message I sent Andrew?

Yes I did :-)

Regarding the OPML include support, it could be implemented transparently anytime later on. (for an OPML file import at least, not sure if you are also building an editor, like Andrew said.)

BTW, when you create a table, I assume if a field only has 28 characters it doesn't allocate 255 (if that's the max field size). I hope! Maybe they do???

With VARCHAR yes indeed, it doesn't allocate 255.

https://dev.mysql.com/doc/refman/5.7/en/char.html

You should still use the minimum number of chars required (255 if ok, not 1000 everywhere), as i think there is an impact on the memory used (not disk space).