/[pg-zoom]/zoom.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /zoom.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 9 - (show annotations)
Thu Apr 26 22:19:40 2007 UTC (16 years, 11 months ago) by dpavlin
File size: 3319 byte(s)
move maximum number of results fetched from upsrtream server to servers table
and maxres column. This allows fine-tuning of query lenght to upstream server.
1 create table servers (
2 name text not null,
3 host text not null,
4 port int not null default 210,
5 database text default 'Default',
6 maxres int not null default 100,
7 primary key(name)
8 );
9
10 -- insert sample
11 insert into servers values ( 'nsk', '161.53.240.27', 8090, 'voyager', 0 );
12 insert into servers values ( 'nsk-en', '161.53.240.27', 8190, 'voyager', 300 );
13 insert into servers values ( 'loc', 'z3950.loc.gov', 7090, 'Voyager', 300 );
14 insert into servers values ( 'ucs', 'library.usc.edu', 2200, 'unicorn', 100 );
15 --insert into servers values ( '', '', , '' );
16
17 create type item as (
18 title text,
19 author text,
20 edition text,
21 date text
22 );
23
24 create or replace function search(text,text)
25 returns setof item
26 language plperlu
27 as $$
28
29 use strict;
30
31 my $debug = 0;
32
33 my ( $server, $query ) = @_;
34
35 my $rv = spi_exec_query(qq{
36 select host,port,database,maxres from servers where name ilike '$server'
37 },1);
38
39 die "can't find server $server in table servers\n" unless ( $rv->{processed} == 1 );
40
41 my ( $host, $port, $database, $max_res ) = (
42 $rv->{rows}[0]->{host},
43 $rv->{rows}[0]->{port},
44 $rv->{rows}[0]->{database},
45 $rv->{rows}[0]->{maxres},
46 );
47
48 use ZOOM;
49 use MARC::Record;
50 use Encode qw/encode decode/;
51
52 my $pqf = {
53 isbn => '@attr 1=7 @attr 4=1 "%s"',
54 title => '@attr 1=4 @attr 4=1 "%s"',
55 author => '@attr 1=1003 @attr 4=1 "%s"',
56 issn => '@attr 1=8 @attr 4=1 "%s"',
57 };
58
59 sub q2cqf {
60 my $q = shift;
61 if ($q =~ m/^(\w+):\s*(.*)$/) {
62 my ( $k,$v ) = ( $1,$2 );
63 return sprintf( $pqf->{ $k }, $v ) if ( defined( $pqf->{ $k } ) );
64 }
65 return $q;
66 }
67
68 my $conn = new ZOOM::Connection($host, $port,
69 databaseName => $database) or
70 die "can't connect to ${host}:${port}/${database}\n";
71
72 $conn->option(preferredRecordSyntax => "usmarc");
73
74 my $rs;
75
76 my $notice;
77
78 if ( $query =~ m/[\s="]|(and|or|not)/ ) {
79 $rs = $conn->search( new ZOOM::Query::CQL( $query ) );
80 $notice = 'CQL';
81 } else {
82 my $cqf = q2cqf( $query );
83 $notice = "CQF: $cqf";
84 $rs = $conn->search_pqf( $cqf );
85 }
86
87 my $n = $rs->size();
88 if ( $n > $max_res ) {
89 warn "query returned $n results, fetching first $max_res\n";
90 $n = $max_res;
91 }
92 # fetch all results
93 $rs->records(0, $n - 1, 0);
94
95 warn "$n results for '$query' $notice\n";
96
97 sub strip_non_digit {
98 my $d = shift;
99 $d =~ s/^\D+//;
100 $d =~ s/\D+$//;
101 return $d;
102 }
103
104 # fix encoding
105 sub e {
106 my $t = shift;
107 $t =~ s/éc/è/g;
108 $t =~ s/âc/æ/g;
109 $t =~ s/éz/¾/g;
110 $t =~ s/és/¹/g;
111 $t =~ s/³/ð/g;
112 $t =~ s/éC/È/g;
113 $t =~ s/âC/Æ/g;
114 $t =~ s/éZ/®/g;
115 $t =~ s/éS/©/g;
116 $t =~ s/£/Ð/g;
117 warn "## $t\n" if $debug;
118 # $t = decode('iso-8859-2', $t);
119 # return encode('utf-8',$t);
120 return $t;
121 }
122
123 foreach my $i ( 1 .. $n ) {
124 my $marc = new_from_usmarc MARC::Record( $rs->record( $i - 1 )->raw() );
125
126 return_next({
127 title => e( $marc->title ),
128 author => e( $marc->author ),
129 edition => e( $marc->edition ),
130 date => strip_non_digit( $marc->publication_date ),
131 });
132 }
133
134 return undef;
135
136 $$;
137
138 -- if your terminal isn't iso-8859-2, change this!
139 -- set client_encoding = 'iso-8859-2';
140
141 -- example
142 -- select * from search('title:djece');
143 -- select * from search('osman');
144
145 --select * from search('nsk','title:mor');
146 --select * from search('nsk','grada');
147 --select * from search('nsk-en','restrictions');
148
149 --select * from search('ucs','human');
150
151 SELECT date,count(date)
152 FROM search('loc','human and computer and interaction')
153 GROUP BY date

  ViewVC Help
Powered by ViewVC 1.1.26