API vs. database

[Tillmann Eben]

To generate some pics for RationalWiki (I'm the user larron), I tried to do some api inquiries.

While  an inquiry like:

http://en.citizendium.org/api.php?action=query&list=usercontribs&uclimit=10&ucuser=Derek_Hodges&format=xml

works, the follow-up

http://en.citizendium.org/api.php?action=query&list=usercontribs&uclimit=10&ucuser=Derek_Hodges&format=xml&ucstart=2009-02-04T02%3A05%3A35Z

creates this error:

<error code="internal_api_error_DBUnexpectedError" info="Exception Caught: A database error has occurred
Query: SELECT  rev_timestamp,page_namespace,page_title,rev_user_text,rev_page,rev_id,page_latest,rev_comment,rev_minor_edit,page_is_new  FROM revision,page  WHERE (page_id=rev_page) AND rev_deleted = '0' AND rev_user_text = 'Derek Hodges' AND (rev_timestamp<='20090204020535')  ORDER BY rev_user_text DESC, rev_timestamp DESC LIMIT 11 
Function: ApiQueryContributions::execute
Error: 1 ERROR:  invalid input syntax for type timestamp with time zone: "20090204020535"
"/>

Somehow, the database doesn't like the format of the timestamp - and I can't find out which format it should be....

Any help out there?

[Matt Innis]

I'm not sure if this will help (or that I remember correctly), but it seems that I was trying to import a wikipedia page with a history last year and it wouldn't import until I renamed the default name of the file to remove the hyphen "-".  Hope that helps somehow?

[Tillmann Eben]

Renaming doesn't help - the timestamp seemed to be processed correctly, as the query says:
rev_timestamp<='20090204020535'
but then, somehow the information on the time-zone is lost...

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

[Daniel Mietchen]

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

I left a note there.

[Hayford Peirce]

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

It can't be discussed there because you're not a Citizendium member, at least not that I have been able to verify.

I moved the discussion here to the non-members' Forum.  Why don't you become a member?

[Tillmann Eben]

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

It can't be discussed there because you're not a Citizendium member, at least not that I have been able to verify.

Please excuse my poor choice of words: it shouldn't be a discussion, I'd rather hoped for an answer to my question. There seems to be something amiss with the way the SQL query is produced.

I hoped that posting it in the Technical Forum would spike the interest of those with technical expertise, the webmaster or whoever. IMO, as it seems to be a bug, it should be handled though I'm not a member.

I moved the discussion here to the non-members' Forum.  Why don't you become a member?

Because I'm not interested in becoming a member? It should be possible for an outsider to hint to bugs without learning the secret hand-shakes. :-)

 I appreciate some of the work at CZ. But I strongly  dislike  your Health Sciences/Healing Arts. I don't want to have anything to do with this kind of stuff, so I stick to the anonymity of wikipedia ....

[Tillmann Eben]

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

I left a note there.

Thanks!

[Howard C. Berkowitz]

P.S.: I'd rather preferred this to be discussed in the Technical Forum...

It can't be discussed there because you're not a Citizendium member, at least not that I have been able to verify.

Please excuse my poor choice of words: it shouldn't be a discussion, I'd rather hoped for an answer to my question. There seems to be something amiss with the way the SQL query is produced.

I hoped that posting it in the Technical Forum would spike the interest of those with technical expertise, the webmaster or whoever. IMO, as it seems to be a bug, it should be handled though I'm not a member.

I moved the discussion here to the non-members' Forum.  Why don't you become a member?

Because I'm not interested in becoming a member? It should be possible for an outsider to hint to bugs without learning the secret hand-shakes. :-)

 I appreciate some of the work at CZ. But I strongly  dislike  your Health Sciences/Healing Arts. I don't want to have anything to do with this kind of stuff, so I stick to the anonymity of wikipedia ....

Well, for nonmembers, we basically support the browser interface.  Yes, I'm perfectly aware that there's no block to public APIs, but I'm personally a little hesitant to offer out-of-the-ordinary technical support to someone that does not want to join the community.  There are limited resources available to Citizens for the API, SQL, etc. level of query.

For the record, I think the Healing Arts problem, especially with respect to Homeopathy, is awful, but I'm also aware of both efforts to fix the specific article, and a growing consensus that the split is basically dysfunctional.

[Tillmann Eben]

Well, for nonmembers, we basically support the browser interface.  Yes, I'm perfectly aware that there's no block to public APIs, but I'm personally a little hesitant to offer out-of-the-ordinary technical support to someone that does not want to join the community.  There are limited resources available to Citizens for the API, SQL, etc. level of query.

Clearly, I'd profit from a solution, i.e., a removal of the bug in your software: It's my hobby-horse to generate graphs of various wikis, and the best way to gather data for all parties involved :-) is via the APIs.
BUT: as far as I can see, it is a bug in your software. One would think that it is in your own interest to remove it, as otherwise, sometimes some member will run into it.
I'm not asking for out-of-the-ordinary technical support, but I would appreciate it if you could fix your malfunction.

[Howard C. Berkowitz]

Clearly, I'd profit from a solution, i.e., a removal of the bug in your software: It's my hobby-horse to generate graphs of various wikis, and the best way to gather data for all parties involved :-) is via the APIs.
BUT: as far as I can see, it is a bug in your software. One would think that it is in your own interest to remove it, as otherwise, sometimes some member will run into it.
I'm not asking for out-of-the-ordinary technical support, but I would appreciate it if you could fix your malfunction.

Bugs should be fixed, but there's an issue of priorities. I can think of several known bugs for which there are awkward workarounds, which we constantly encounter when creating articles. Nevertheless, there have been more critical things to be fixed, or specifically needed enhancements to be added.

If the bug is affecting only your hobbyhorse, speaking personally only, I just don't see why resources should be expended on it, when you have said that you don't plan to contribute content to CZ.

[Tillmann Eben]

Fair enough - I didn't know that your resources were tied up  fixing critical things.
My best wishes for your efforts - and then, perhaps, sometimes you'll be able to tend to this menial task...

[Russell D. Jones]

I'm not so sure it's our software.  The historywiki API also has trouble processing requests using timestamps for any time other than the present.  In other words, I can query the DB for the most recent this or that, but can not query the DB for the 500 or so this or that starting January 1, 2009, 00:00:00.  I've assumed this was some sort of query issue on my part (since I was not all that familiar with API or DB's), but I took some time last winter to really learn how to query the DB about a particular time and came to the conclusion that my mediawiki software just couldn't process such a query.  It's still on my list of issues to have my tech guru look at.

[Tillmann Eben]

Thanks, Russel D. Jones

• According to Citizendium's API documentation page , enumerating recent changes is limited to the last 500 (resp. 5000 for bots) entries. There is no similar restriction for user contributions.
  
• In fact, the example I gave above should look up the last 20 entries, so it would be within the limits of such a restriction
• I get similar errors of the database when I use other queries involving timestamps.

I really think it's the way the timestamp is processed for the use of the SQL query which causes the problem.

[Drew R. Smith]

I don't know anything about API's or SQL's or any of that, but for what its worth your query doesn't work with wikipedia either.

The best I could find would be

http://en.citizendium.org/wiki?limit=50&title=Special%3AContributions&contribs=user&target=Derek+Hodges&namespace=&year=2009&month=7

But you cant get specific dates, times, or that fancy XML output.

[Tillmann Eben]

I don't know anything about API's or SQL's or any of that, but for what its worth your query doesn't work with wikipedia either.

The best I could find would be

http://en.citizendium.org/wiki?limit=50&title=Special%3AContributions&contribs=user&target=Derek+Hodges&namespace=&year=2009&month=7

But you cant get specific dates, times, or that fancy XML output.

• mutatis mutandis, it works fine at wp:
  http://en.wikipedia.org/w/api.php?action=query&list=usercontribs&uclimit=10&ucuser=Ed_Poor&format=xml
  http://en.wikipedia.org/w/api.php?action=query&list=usercontribs&uclimit=10&ucuser=Ed_Poor&format=xml&ucstart=2009-02-04T02%3A05%3A35Z
  
• the idea to use the API is to reduce unwelcome traffic for Citizendium: I could crawl the html-pages but that would be, well, inelegant and waste resources.