|
| 1 | +#!/usr/bin/perl |
| 2 | +# |
| 3 | +# This script substracts all substrings out of a specific column in a table |
| 4 | +# and generates output that can be loaded into a new table with the |
| 5 | +# psql '\copy' command. The new table should have the following structure: |
| 6 | +# |
| 7 | +#create table tab ( |
| 8 | +#string text, |
| 9 | +#id oid |
| 10 | +#); |
| 11 | +# |
| 12 | +# Note that you cannot use 'copy' (the SQL-command) directly, because |
| 13 | +# there's no '\.' included at the end of the output. |
| 14 | +# |
| 15 | +# The output can be fed through the UNIX commands 'uniq' and 'sort' |
| 16 | +# to generate the smallest and sorted output to populate the fti-table. |
| 17 | +# |
| 18 | +# Example: |
| 19 | +# |
| 20 | +# fti.pl -u -d mydb -t mytable -c mycolumn -f myfile |
| 21 | +#sort -o myoutfile myfile |
| 22 | +#uniq myoutfile sorted-file |
| 23 | +# |
| 24 | +# psql -u mydb |
| 25 | +# |
| 26 | +#\copy my_fti_table from myfile |
| 27 | +# |
| 28 | +# create index fti_idx on my_fti_table (string,id); |
| 29 | +# |
| 30 | +#create function fti() returns opaque as |
| 31 | +#'/path/to/fti/file/fti.so' |
| 32 | +#language 'C'; |
| 33 | +# |
| 34 | +#create trigger my_fti_trigger after update or insert or delete |
| 35 | +#on mytable |
| 36 | +#for each row execute procedure fti(my_fti_table, mycolumn); |
| 37 | +# |
| 38 | +# Make sure you have an index on mytable(oid) to be able to do somewhat |
| 39 | +# efficient substring searches. |
| 40 | + |
| 41 | +#use lib '/usr/local/pgsql/lib/perl5/'; |
| 42 | +use lib'/mnt/web/guide/postgres/lib/perl5/site_perl'; |
| 43 | +use Pg; |
| 44 | +use Getopt::Std; |
| 45 | + |
| 46 | +$PGRES_EMPTY_QUERY = 0 ; |
| 47 | +$PGRES_COMMAND_OK = 1 ; |
| 48 | +$PGRES_TUPLES_OK = 2 ; |
| 49 | +$PGRES_COPY_OUT = 3 ; |
| 50 | +$PGRES_COPY_IN = 4 ; |
| 51 | +$PGRES_BAD_RESPONSE = 5 ; |
| 52 | +$PGRES_NONFATAL_ERROR = 6 ; |
| 53 | +$PGRES_FATAL_ERROR = 7 ; |
| 54 | + |
| 55 | +$[ = 0;# make sure string offsets start at 0 |
| 56 | + |
| 57 | +subbreak_up { |
| 58 | +my$string =pop@_; |
| 59 | + |
| 60 | +@strings =split(/\W+/,$string); |
| 61 | +@subs = (); |
| 62 | + |
| 63 | +foreach$s (@strings) { |
| 64 | +$len =length($s); |
| 65 | +nextif ($len < 4); |
| 66 | + |
| 67 | +$lpos =$len-1; |
| 68 | +while ($lpos >= 3) { |
| 69 | +$fpos =$lpos - 3; |
| 70 | +while ($fpos >= 0) { |
| 71 | +$sub =substr($s,$fpos,$lpos -$fpos + 1); |
| 72 | +push(@subs,$sub); |
| 73 | +$fpos =$fpos - 1; |
| 74 | +} |
| 75 | +$lpos =$lpos - 1; |
| 76 | +} |
| 77 | +} |
| 78 | + |
| 79 | +return@subs; |
| 80 | +} |
| 81 | + |
| 82 | +subconnect_db { |
| 83 | +my$dbname =shift@_; |
| 84 | +my$user =shift@_; |
| 85 | +my$passwd =shift@_; |
| 86 | + |
| 87 | +if (!defined($dbname) ||$dbnameeq"") { |
| 88 | +return 1; |
| 89 | +} |
| 90 | +$connect_string ="dbname=$dbname"; |
| 91 | + |
| 92 | +if ($userne"") { |
| 93 | +if ($passwdeq"") { |
| 94 | +return 0; |
| 95 | +} |
| 96 | +$connect_string ="$connect_string user=$user password=$passwd". |
| 97 | +"authtype=password"; |
| 98 | +} |
| 99 | + |
| 100 | +$PG_CONN = PQconnectdb($connect_string); |
| 101 | + |
| 102 | +if (PQstatus($PG_CONN)) { |
| 103 | +printSTDERR"Couldn't make connection with database!\n"; |
| 104 | +printSTDERR PQerrorMessage($PG_CONN),"\n"; |
| 105 | +return 0; |
| 106 | +} |
| 107 | + |
| 108 | +return 1; |
| 109 | +} |
| 110 | + |
| 111 | +subquit_prog { |
| 112 | +close(OUT); |
| 113 | +unlink$opt_f; |
| 114 | +if (defined($PG_CONN)) { |
| 115 | +PQfinish($PG_CONN); |
| 116 | +} |
| 117 | +exit 1; |
| 118 | +} |
| 119 | + |
| 120 | +subget_username { |
| 121 | +print"Username:"; |
| 122 | +chop($n = <STDIN>); |
| 123 | + |
| 124 | +return$n;; |
| 125 | +} |
| 126 | + |
| 127 | +subget_password { |
| 128 | +print"Password:"; |
| 129 | + |
| 130 | +system("stty -echo < /dev/tty"); |
| 131 | +chop($pwd = <STDIN>); |
| 132 | +print"\n"; |
| 133 | +system("stty echo < /dev/tty"); |
| 134 | + |
| 135 | +return$pwd; |
| 136 | +} |
| 137 | + |
| 138 | +submain { |
| 139 | +getopts('d:t:c:f:u'); |
| 140 | + |
| 141 | +if (!$opt_d || !$opt_t || !$opt_c || !$opt_f) { |
| 142 | +printSTDERR"usage:$0 [-u] -d database -t table -c column". |
| 143 | +"-f output-file\n"; |
| 144 | +return 1; |
| 145 | +} |
| 146 | + |
| 147 | +if (defined($opt_u)) { |
| 148 | +$uname = get_username(); |
| 149 | +$pwd = get_password(); |
| 150 | +}else { |
| 151 | +$uname =""; |
| 152 | +$pwd =""; |
| 153 | +} |
| 154 | + |
| 155 | +$SIG{'INT'} ='quit_prog'; |
| 156 | +if (!connect_db($opt_d,$uname,$pwd)) { |
| 157 | +printSTDERR"Connecting to database failed!\n"; |
| 158 | +return 1; |
| 159 | +} |
| 160 | + |
| 161 | +if (!open(OUT,">$opt_f")) { |
| 162 | +printSTDERR"Couldnt' open file '$opt_f' for output!\n"; |
| 163 | +return 1; |
| 164 | +} |
| 165 | + |
| 166 | +PQexec($PG_CONN,"begin"); |
| 167 | + |
| 168 | +$query ="declare C cursor for select$opt_c, oid from$opt_t"; |
| 169 | +$res = PQexec($PG_CONN,$query); |
| 170 | +if (!$res || (PQresultStatus($res) !=$PGRES_COMMAND_OK)) { |
| 171 | +printSTDERR"Error declaring cursor!\n"; |
| 172 | +printSTDERR PQerrorMessage($PG_CONN),"\n"; |
| 173 | +PQfinish($PG_CONN); |
| 174 | +return 1; |
| 175 | +} |
| 176 | +PQclear($res); |
| 177 | + |
| 178 | +$query ="fetch in C"; |
| 179 | +while (($res = PQexec($PG_CONN,$query)) && |
| 180 | + (PQresultStatus($res) ==$PGRES_TUPLES_OK) && |
| 181 | + (PQntuples($res) == 1)) { |
| 182 | +$col = PQgetvalue($res, 0, 0); |
| 183 | +$oid = PQgetvalue($res, 0, 1); |
| 184 | + |
| 185 | +@subs = break_up($col); |
| 186 | +foreach$i (@subs) { |
| 187 | +print OUT"$i\t$oid\n"; |
| 188 | +} |
| 189 | +} |
| 190 | + |
| 191 | +if (!$res || (PQresultStatus($res) != PGRES_TUPLES_OK)) { |
| 192 | +printSTDERR"Error retrieving data from backend!\n"; |
| 193 | +printSTDERR PQerrorMEssage($PG_CONN),"\n"; |
| 194 | +PQfinish($PG_CONN); |
| 195 | +return 1; |
| 196 | +} |
| 197 | + |
| 198 | +PQclear($res); |
| 199 | +PQfinish($PG_CONN); |
| 200 | + |
| 201 | +return 0; |
| 202 | +} |
| 203 | + |
| 204 | +exit main(); |