Saturday work

[scripting]

Lots of cleanup work today and tomorrow.

But first I want to get the query for the user/subscriptions page.

That is, the page for a user showing what they're subscribed to.

Here's the page for myself.

http://feedbase.io/?username=davewiner

Note the undefined values in the third column.

This is where the countSubs value is supposed to be.

It's undefined because there is no countSubs field in the object returned for the subscription. I want to fix that.

http://feedbase.io/getsubs?username=davewiner

For each feed, countSubs should be the subscriber count for the feed across all users. The same value that appears in the hotlist.

http://feedbase.io/hotlist

Naively I tried taking the query for the hotlist, and hacked it up.

SELECT subscriptions.feedurl, feeds.title, COUNT(subscriptions.feedurl) AS countSubs FROM subscriptions, feeds WHERE subscriptions.feedurl = feeds.feedurl and subscriptions.username = "davewiner" GROUP BY feedurl ORDER BY countSubs DESC;

But the countSubs value for each was 1.

Any help much appreciated! ;-)

Dave

[scripting]

Just fixed up the feed viewer page.

http://feedbase.io/?feedurl=http%3A%2F%2Finessential.com%2Fxml%2Frss.xml

No longer displaying raw JSON.

The subscribers list is part of the table, and it's just a wrapping list of links.

Also encoding URLs in links.

More to come..

[scripting]

Making good progress. Now there's a Subscribe button on the Feed Viewer page.

In a sense this is the whole reason for having feedBase.

Dave

[jystervinou]

Hi,

Your request returns 1 for the count, because COUNT(subscriptions.feedurl) is indeed 1 when the data is restricted to subscriptions.username = "davewiner".

I see at least two ways:

• Try to build a complex query that would return all the feeds, PLUS the counts, in one run.
  It would probably involve sub queries or similar, and hey, who wants to suffer on a saturday. :)
  (and the query might not be the fastest...)

• Compute the counts each time one subscription is added/updated, and store it in the feeds table in a new countSubs column.
  So then, when you get the user feeds from the database, you also get the counts as easily as you get the feed title. (you can see it as a cache)

You can do it in:

function addSubscriptionToDatabase (username, listname, feedurl, callback) {
	var now = formatDateTime (new Date ());
	var sqltext = "REPLACE INTO subscriptions (username, listname, feedurl, whenupdated) VALUES (" + encode (username) + ", " + encode (listname) + ", " + encode (feedurl) + ", " + encode (now) + ");";
	runSqltext (sqltext, function (result) {
                var getCountSQL = " SELECT count(*) AS c FROM subscriptions WHERE feedurl=" + encode(feedurl);
                runSqltext(updateCountsSQL, function(resultCount) {
                    var firstLine = resultCount[0];
                    var updateCountSQL = "UPDATE feeds SET countSubs = " + firstLine.c + " WHERE feedurl = " + encode(feedurl);
                    runSqltext(updateCountSQL, function(resultUpdate) {
  		      if (callback !== undefined) {
			      callback (result);
			      }
		      });
                    });
                });
	}

• There might be a totally easier way that i miss at the moment :-)

[scripting]

First, thank you for the advice. I think it makes sense to do it this way, at least until another better way comes along.

I have made the change you recommend.

https://github.com/scripting/feedBase/blob/master/feedbase.js#L87

However I'm getting errors of this form:

runSqltext: err.code == ER_BAD_FIELD_ERROR, err.message == ER_BAD_FIELD_ERROR: Unknown column 'countSubs' in 'field list'

This seems to be happening in the third runSqlText call.

[scripting]

Wait I think I see what the problem is.

I have to recreate the database with a countSubs field in the subscriptions table.

Yes??

[jystervinou]

You need to create the new countSubs (or whatever name you prefer) column in the feeds table:

ALTER TABLE feeds ADD countSubs BIGINT;

[jystervinou]

No need to recreate the table, you can just ALTER it.

[jystervinou]

Or do it with a UI with the great Sequel Pro ;-)

[scripting]

Good we're making progress.

I added countSubs using the ALTER TABLE command from the command line.

The error messages went away.

Good.

But I think it's storing NULL in the database in the countSubs field.

Dave

[scripting]

Do you have a copy of the software running locally?

[jystervinou]

Nope, it's all running in my brain, which is not the greatest computer ever made :-D

That's probably the var firstLine = resultCount[0]; part that i got wrong.

[jystervinou]

try to log firstLine.c after firstLine = resultCount[0]; to check what's in there. (the .c is supposed to refer to the c in count(*) AS c

[jystervinou]

Sorry, watching at the same time ;-) [Update: lost at the last second :-/ ]

[scripting]

I took the time to rebuild the database anyway. it's populating now.

[scripting]

As it's rebuilding, it turns out some of them are not NULL.

http://feedbase.io/?username=davewiner

It's still reloading. I think perhaps NULL means "no one has set this value yet."

Which can be dealt with.

I'm going out for a bit myself, enjoy your sports game whatever it is.

Dave