[Using Sakai] External statistics reports

[John Bush]

Here is the way I'm addressing this right now.  I've written a soap
webservice, that returns soap for a POST call and raw content for a
GET call.  The reason for this is complicated I can explain if you
really care.

    public String executeQuery(String sessionid, String query, String
hash, int rowCount, String format)

You essentially pass is raw sql and what format you want: csv, json, xml, etc.

GET calls will produce file content directly.

POST calls will continue to produce soap output with the formatting
included in the soap env.

The GET feature will make it easy to script calls to the report service.

To create the hash param, the sessionid is combined with the query and
a sha256 hash is made from that string, like this:

echo -n "fa645a7d-4168-4ed7-9260-4a013c20664dselect * from sakai_user"
| shasum -a 256

The purpose of the hash is security, ensures request wasn't messed
with in transit (although ssl really does that already), and that both
caller and service know the algorithm.  Probably needs a shared
private key in there as well.  But the admin logon, and adequate
firewall rules enforce security as well.

the output of that can be included in the call as follows:

curl -G "http://localhost:8080/sakai-axis/SakaiReport.jws?method=executeQuery&sessionid=fa645a7d-4168-4ed7-9260-4a01c20664d&query=select%20first_name%20as%20givenName,%20last_name%20as%20family%20from%20sakai_user&hash=a25b34f2ff78631ca610a24db07650fff72108427883fc7b9331ce546c1e2ca8&rowCount=100&format=json"

the valid format strings are : csv, json, xml, and
csv_with_header_row. An invalid format string results in xml format
returned.

the rowCount controls the maximum number of rows returned. A value <=
0 will returned all rows.

the session id is obtained via a separate call like this:
http://localhost:8080/sakai-axis/SakaiLogin.jws?method=login&id=admin&pw=admin

any error results in soap xml content being returned.

jdbc on the server side enforces that only read stmts are allowed, if
you try to modify something you'll get an error.  Its still a work in
progress but used in production currectly.   For example, I want it to
be able to use the long db connection in sakai to talk to a copy of
the production data, as to not impact production with long calls.

If you are interested in this I'm happy to share the code.

So this doesn't solve your issue of being bound to sql issue, but its
powerful as hell, and easy to call using the get method.  Really if
the sql doesn't violate some huge design problem for you, for many I'm
sure they are just cringing at the idea (the sakai database doesn't
change much now, so practically, that very low risk), it basically
eliminates having to write another service that is read only.  All you
need is this one.

On Sun, Aug 19, 2012 at 9:05 PM, Gregory Guthrie <guthrie at mum.edu> wrote:
> I just want to check if I am understanding this correctly;
>
> I want to generate some automated reports on student visits, forum usage &
> participation, faculty forum usage, and assignments submitted.
>
>
>
> I already defined the reports that have adequate data for me, but don’t want
> to have to manually click through a GUI for
> statistics/reports/Report-Visits/Export every week across all courses.
>
>
>
> One option is to write database queries, and that will work, but is a bit
> messy to setup (I did some trials), and is not recommended because database
> structures and relations are not guaranteed between releases.
>
>
>
> Another option is to write some user programmed extensions which will do the
> reports, and expose them as web-services, and then query externally using
> these. This requires some fairly detailed knowledge of the internals and how
> to write extensions, how to use the internal API to do reports, and to write
> and maintain the Java code.
>
>
>
> Since I see that Sakai already has the reports defined internally in an XML
> format definition, and they are named; is there any existing API by which I
> could tell it to run the report (and export/return the results)? I looked at
> trying to use web-automation to submit the URLs equivalent to the manual GUI
> navigation, but they seem to be stateful, and I haven’t yet figured out if
> that approach would work.
>
>
>
> Canvas has a set of web-service APIs that expose their statistics in a
> simple way either programmatically or using various web tools (curl, wget,
> …). This is the type of thing that I was hoping to find.
>
>
>
> Any hints appreciated; or to at least to confirm that this is a correct
> understanding of the situation.
>
> Thanks.(I’m not sure if this should be in this forum, or the developers
> forum.)
>
> -------------------------------------------
>
>
>
>
> _______________________________________________
> sakai-user mailing list
> sakai-user at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/sakai-user
>
> TO UNSUBSCRIBE: send email to sakai-user-unsubscribe at collab.sakaiproject.org
> with a subject of "unsubscribe"



-- 
John Bush
602-490-0470