Revision 246

Date:
2008/10/13 12:30:59
Author:
dpavlin
Revision Log:
convertor from SQL query to JSON for Exhibit
Files:

Legend:

 
Added
 
Removed
 
Modified

  • data/stats/exhibit/instances.html

     
    1 <html>
    2 <head>
    3 <title>All Strix instances</title>
    4
    5 <link href="instances.js" type="application/json" rel="exhibit/data" />
    6
    7 <script src="http://static.simile.mit.edu/exhibit/api-2.0/exhibit-api.js"
    8 type="text/javascript"></script>
    9
    10 <style>
    11 </style>
    12 </head>
    13 <body>
    14 <h1>All Strix instances with contact data</h1>
    15 <table width="100%">
    16 <tr valign="top">
    17 <td ex:role="viewPanel">
    18 <div ex:role="view"></div>
    19 </td>
    20 <td width="25%">
    21 browsing controls here...
    22 </td>
    23 </tr>
    24 </table>
    25 </body>
    26 </html>

  • data/stats/Makefile

     
    1 stats:
    2 psql a3c < stats.sql
    3
    4 import:
    5 ls [a-z]*.tsv | xargs -i ./import-tsv.sh {}
    6
    7
    8 json: stats
    9 ./sql2json.pl > exhibit/instances.js

  • data/stats/sql2json.pl

     
    1 #!/usr/bin/perl
    2
    3 use warnings;
    4 use strict;
    5
    6 use DBI;
    7 use Data::Dump qw/dump/;
    8 use JSON::XS;
    9
    10 my $debug = 0;
    11 my $dsn = 'DBI:Pg:dbname=a3c';
    12
    13 my $dbh = DBI->connect($dsn,"","", { 'RaiseError' => '1' }) || die $DBI::errstr;
    14 $dbh->do( qq{ set client_encoding='utf-8' } );
    15
    16 my $json = JSON::XS->new;
    17 $json->utf8( 1 );
    18 $json->pretty( 1 );
    19
    20 my $sth = $dbh->prepare( 'select * from stats.instance_organization' );
    21 $sth->execute();
    22
    23
    24 my @items;
    25
    26 while (my $row = $sth->fetchrow_hashref ) {
    27 warn "# row = ",dump( $row ) if $debug;
    28
    29 foreach my $col ( keys %$row ) {
    30 delete $row->{$col} unless defined $row->{$col};
    31 }
    32
    33 push @items, $row;
    34 }
    35
    36 print $json->encode(
    37 {
    38 items => [ @items ],
    39 }
    40 );

  • data/stats/stats.sql

     
    1 -- RT #4608: korištenje instanci po školama
    2
    3 drop schema stats cascade ;
    4 create schema stats;
    5
    6 create table stats.visits (
    7 instance text not null,
    8 visits int not null,
    9 primary key(instance)
    10 );
    11
    12 \copy stats.visits from 'visits.tsv'
    13
    14 -- \echo top 10 schools by visits
    15 -- select * from stats.visits order by visits desc limit 10 ;
    16
    17 create table stats.changes (
    18 instance text not null,
    19 changes int not null,
    20 primary key(instance)
    21 );
    22 \copy stats.changes from 'changes.tsv'
    23
    24 -- \echo top 10 schools by changes
    25 -- select * from stats.changes order by changes desc limit 10 ;
    26
    27 create view stats.instance_organization as
    28 select
    29 stats.visits.instance as id,
    30 'http://' || hrEduOrgUrl as uri,
    31 o as label,
    32 l as town,
    33 postalAddress,
    34 telephoneNumber,
    35 facsimileTelephoneNumber,
    36 stats.visits.visits,
    37 stats.changes.changes
    38 from stats.visits
    39 left outer join hr_edu_orgs on cn = instance
    40 left outer join stats.changes on stats.changes.instance = stats.visits.instance
    41 where hrEduOrgUrl is not null
    42 order by l, o
    43 ;
    44
    45 select * from stats.instance_organization limit 1;