2 #-----------------------------------
3 # Script Name: build_marc_Tword.pl
4 # Script Version: 0.1.0
7 # script to build a marc_Tword table.
9 # CREATE TABLE `marc_Tword` (
10 # `word` varchar(80) NOT NULL default '',
11 # `usedin` text NOT NULL,
12 # `tagsubfield` varchar(4) NOT NULL default '',
13 # PRIMARY KEY (`word`,`tagsubfield`)
15 # just to test the idea of a reversed index searching.
16 # reversed index for searchs on Title.
17 # the marc_Tword table contains for each word & marc field/subfield, the list of biblios using it, with the title
18 # reminder : the inverted index is only done to search on a "contain". For a "=" or "start by", the marc_subfield_table is perfect & correctly indexed.
19 # if this POC becomes more than a POC, then I think we will have to build 1 table for each sorting (marc_Tword for title, Aword for author, Cword for callnumber...)
22 # * indexes empty words too (it's just a proof of concept)
23 # * maybe it would be OK to store only 20 char of the title.
29 my $dbh=C4::Context->dbh;
30 use Time::HiRes qw(gettimeofday);
32 # fields & subfields to ignore
33 # in real situation, we should add a marc constraint on this.
34 # ideally, we should not inde isbn, as every would be different, so it makes the table very big.
35 # but in this case we have to find a way to automatically search "isbn = XXX" in marc_subfield_table
52 my $starttime = gettimeofday;
54 $dbh->do("delete from marc_Tword");
57 my $query="SELECT biblio.biblionumber,tag,subfieldcode,subfieldvalue,biblio.title FROM marc_subfield_table left join marc_biblio on marc_biblio.bibid=marc_subfield_table.bibid left join biblio on marc_biblio.biblionumber=biblio.biblionumber where tag=?";
58 my $sth=$dbh->prepare($query);
60 for (my $looptag=0;$looptag<=999;$looptag++) {
61 print "******** SELECTING ".(sprintf "%03s",$looptag)."\n";
62 $sth->execute(sprintf "%03s",$looptag);
63 print "******** DONE \n";
64 $|=1; # flushes output
66 my $sthT=$dbh->prepare("select usedin from marc_Tword where tagsubfield=? and word=?");
67 my $updateT=$dbh->prepare("update marc_Tword set usedin=? where tagsubfield=? and word=?");
68 my $insertT=$dbh->prepare("insert into marc_Tword (tagsubfield,word,usedin) values (?,?,?)");
71 # 1st version, slower, but less RAM consumming
72 # while (my ($biblionumber, $tag, $subfieldcode, $subfieldvalue, $title) = $sth->fetchrow) {
73 # next if $ignore_list{"$tag.$subfieldcode"};
74 # $subfieldvalue =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g;
75 # # remove useless chars in the title.
76 # $title =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g;
77 # my @words = split / /, $subfieldvalue;
78 # # and retrieve the reversed entry
79 # foreach my $word (@words) {
80 # $sthT->execute($tag.$subfieldcode,$word);
81 # if (my ($usedin) = $sthT->fetchrow) {
82 # # add the field & save it once again.
83 # $usedin.=",$biblionumber-$title";
84 # $updateT->execute($usedin,$tag.$subfieldcode,$word);
86 # $insertT->execute($tag.$subfieldcode,$word,",$title-$biblionumber");
89 # $timeneeded = gettimeofday - $starttime unless ($i % 100);
90 # print "$i in $timeneeded s\n" unless ($i % 100);
95 # 2nd version : faster (about 100 times !), bug maybe too much RAM consumming...
98 $timeneeded = gettimeofday - $starttime unless ($i % 30000);
99 print "READING $timeneeded s\n";
100 while (my ($biblionumber, $tag, $subfieldcode, $subfieldvalue, $title) = $sth->fetchrow) {
101 next unless $subfieldvalue;
102 next if $ignore_list{$tag.$subfieldcode};
103 $subfieldvalue =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g if $subfieldvalue;
104 # remove useless chars in the title.
105 $title =~ s/(\.|\?|\:|\!|\'|,|\-|\"|\(|\)|\[|\]|\{|\}|\/)/ /g if $title;
106 my @words = split / /, $subfieldvalue;
107 # and retrieve the reversed entry
108 foreach my $word (@words) {
109 my $localkey = $tag.$subfieldcode.'|'.uc($word);
110 $largehash{$localkey}.=",".substr($title,0,15)."-$biblionumber";
112 $timeneeded = gettimeofday - $starttime unless ($i % 30000);
113 print "$i in $timeneeded s\n" unless ($i % 30000);
114 print "." unless ($i % 500);
119 foreach my $k (keys %largehash) {
121 $insertT->execute($1,$2,$largehash{$k});
122 $timeneeded = gettimeofday - $starttime unless ($i % 30000);
123 print "$i in $timeneeded s\n" unless ($i % 30000);
124 print "." unless ($i % 500);