root/gsdl/trunk/perllib/dbutil.pm @ 18441

Revision 18441, 22.1 KB (checked in by davidb, 11 years ago)

Modifications for incremental building to support files that need to be deleted

Line 
1###########################################################################
2#
3# dbutil.pm -- utility functions for writing to different databases
4# Copyright (C) 2008  DL Consulting Ltd
5#
6# A component of the Greenstone digital library software
7# from the New Zealand Digital Library Project at the
8# University of Waikato, New Zealand.
9#
10# This program is free software; you can redistribute it and/or modify
11# it under the terms of the GNU General Public License as published by
12# the Free Software Foundation; either version 2 of the License, or
13# (at your option) any later version.
14#
15# This program is distributed in the hope that it will be useful,
16# but WITHOUT ANY WARRANTY; without even the implied warranty of
17# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18# GNU General Public License for more details.
19#
20# You should have received a copy of the GNU General Public License
21# along with this program; if not, write to the Free Software
22# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
23#
24###########################################################################
25
26package dbutil;
27
28use Encode;
29use strict;
30
31
32sub open_infodb_write_handle
33{
34  my $infodb_type = shift(@_);
35  my $infodb_file_path = shift(@_);
36 
37  if ($infodb_type eq "sqlite")
38  {
39    return &open_infodb_write_handle_sqlite($infodb_file_path);
40  }
41  elsif ($infodb_type eq "gdbm-txtgz")
42  {
43    return &open_infodb_write_handle_gdbm_txtgz($infodb_file_path);
44  }
45  elsif ($infodb_type eq "mssql")
46  {
47    return &open_infodb_write_handle_mssql($infodb_file_path);
48  }
49
50  # Use GDBM if the infodb type is empty or not one of the values above
51  return &open_infodb_write_handle_gdbm($infodb_file_path);
52}
53
54
55sub close_infodb_write_handle
56{
57  my $infodb_type = shift(@_);
58  my $infodb_handle = shift(@_);
59
60  if ($infodb_type eq "sqlite")
61  {
62    return &close_infodb_write_handle_sqlite($infodb_handle);
63  }
64  elsif ($infodb_type eq "gdbm-txtgz")
65  {
66    return &close_infodb_write_handle_gdbm_txtgz($infodb_handle);
67  }
68  elsif ($infodb_type eq "mssql")
69  {
70    return &close_infodb_write_handle_mssql($infodb_handle);
71  }
72   
73  # Use GDBM if the infodb type is empty or not one of the values above
74  return &close_infodb_write_handle_gdbm($infodb_handle);
75}
76
77
78sub get_default_infodb_type
79{
80  # The default is GDBM so everything works the same for existing collections
81  # To use something else, specify the "infodbtype" in the collection's collect.cfg file
82  return "gdbm";
83}
84
85
86sub get_infodb_file_path
87{
88  my $infodb_type = shift(@_);
89  my $collection_name = shift(@_);
90  my $infodb_directory_path = shift(@_);
91
92  if ($infodb_type eq "sqlite")
93  {
94    return &get_infodb_file_path_sqlite($collection_name, $infodb_directory_path);
95  }
96  elsif ($infodb_type eq "gdbm-txtgz")
97  {
98    return &get_infodb_file_path_gdbm_txtgz($collection_name, $infodb_directory_path);
99  }
100  elsif ($infodb_type eq "mssql")
101  {
102    #==================================================================================================#
103    # Updated by Jeffrey (2008/08/25 Monday)
104    # After look into the run-time code, it seems we should still create a database file.
105    # Since the run-time code is always try to read a database file, the easiest way here is not
106    # to change the whole structure, but to give whatever the system is looking for.
107    #==================================================================================================#
108    # Added by Jeffrey (2008/08/15 Friday)
109    # No file path required for MS SQL, it is a server-client connection.
110    # At the moment the information is hard coded in open_infodb_write_handle_mssql
111    # the this might need some tidy up sometime.
112    #==================================================================================================#
113    return &get_infodb_file_path_mssql($collection_name, $infodb_directory_path);
114  }
115 
116  # Use GDBM if the infodb type is empty or not one of the values above
117  return &get_infodb_file_path_gdbm($collection_name, $infodb_directory_path);
118}
119
120
121sub read_infodb_file
122{
123  my $infodb_type = shift(@_);
124  my $infodb_file_path = shift(@_);
125  my $infodb_map = shift(@_);
126
127  if ($infodb_type eq "sqlite")
128  {
129    return &read_infodb_file_sqlite($infodb_file_path, $infodb_map);
130  }
131  elsif ($infodb_type eq "gdbm-txtgz")
132  {
133    return &read_infodb_file_gdbm_txtgz($infodb_file_path, $infodb_map);
134  }
135  elsif ($infodb_type eq "mssql")
136  {
137    return &read_infodb_file_mssql($infodb_file_path, $infodb_map);
138  }
139 
140  # Use GDBM if the infodb type is empty or not one of the values above
141  return &read_infodb_file_gdbm($infodb_file_path, $infodb_map);
142}
143
144
145sub write_infodb_entry
146{
147  my $infodb_type = shift(@_);
148  my $infodb_handle = shift(@_);
149  my $infodb_key = shift(@_);
150  my $infodb_map = shift(@_);
151
152  if ($infodb_type eq "sqlite")
153  {
154    return &write_infodb_entry_sqlite($infodb_handle, $infodb_key, $infodb_map);
155  }
156  elsif ($infodb_type eq "gdbm-txtgz")
157  {
158    return &write_infodb_entry_gdbm_txtgz($infodb_handle, $infodb_key, $infodb_map);
159  }
160  elsif ($infodb_type eq "mssql")
161  {
162    return &write_infodb_entry_mssql($infodb_handle, $infodb_key, $infodb_map);
163  }
164
165  # Use GDBM if the infodb type is empty or not one of the values above
166  return &write_infodb_entry_gdbm($infodb_handle, $infodb_key, $infodb_map);
167}
168
169
170
171# -----------------------------------------------------------------------------
172#   GDBM TXT-GZ IMPLEMENTATION
173# -----------------------------------------------------------------------------
174
175sub open_infodb_write_handle_gdbm_txtgz
176{
177  # Keep infodb in GDBM neutral form => save data as compressed text file,
178  # read for txt2db to be run on it later (i.e. by the runtime system,
179  # first time the collection is ever accessed).  This makes it easier
180  # distribute pre-built collections to various architectures.
181  #
182  # NB: even if two architectures are little endian (e.g. Intel and
183  # ARM procesors) GDBM does *not* guarantee that the database generated on
184  # one will work on the other
185
186  my $infodb_file_path = shift(@_);
187
188  # Greenstone ships with gzip for windows, on $PATH
189
190  my $infodb_file_handle = undef;
191  if (!open($infodb_file_handle, "| gzip - > \"$infodb_file_path\""))
192  {
193    return undef;
194  }
195
196  return $infodb_file_handle;
197}
198
199
200sub close_infodb_write_handle_gdbm_txtgz
201{
202  my $infodb_handle = shift(@_);
203
204  close($infodb_handle);
205}
206
207
208sub get_infodb_file_path_gdbm_txtgz
209{
210  my $collection_name = shift(@_);
211  my $infodb_directory_path = shift(@_);
212
213  my $infodb_file_name = &util::get_dirsep_tail($collection_name).".txt.gz";
214  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
215}
216
217
218sub read_infodb_file_gdbm_txtgz
219{
220  my $infodb_file_path = shift(@_);
221  my $infodb_map = shift(@_);
222
223  my $cmd = "gzip --decompress \"$infodb_file_path\"";
224
225  open (PIPEIN, "$cmd |")
226  || die "Error: Couldn't open pipe from gzip: $!\n  $cmd\n";
227
228  my $infodb_line = "";
229  my $infodb_key = "";
230  my $infodb_value = "";
231  while (defined ($infodb_line = <PIPEIN>))
232  {
233    if ($infodb_line =~ /^\[([^\]]+)\]$/)
234    {
235      $infodb_key = $1;
236    }
237    elsif ($infodb_line =~ /^-{70}$/)
238    {
239      $infodb_map->{$infodb_key} = $infodb_value;
240      $infodb_key = "";
241      $infodb_value = "";
242    }
243    else
244    {
245      $infodb_value .= $infodb_line;
246    }
247  }
248
249  close (PIPEIN);
250}
251
252   
253sub write_infodb_entry_gdbm_txtgz
254{
255
256  my $infodb_handle = shift(@_);
257  my $infodb_key = shift(@_);
258  my $infodb_map = shift(@_);
259 
260  print $infodb_handle "[$infodb_key]\n";
261  foreach my $infodb_value_key (keys(%$infodb_map))
262  {
263    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
264    {
265      if ($infodb_value =~ /-{70,}/)
266      {
267        # if value contains 70 or more hyphens in a row we need to escape them
268        # to prevent txt2db from treating them as a separator
269        $infodb_value =~ s/-/&\#045;/gi;
270      }
271      print $infodb_handle "<$infodb_value_key>" . $infodb_value . "\n";
272    }
273  }
274  print $infodb_handle '-' x 70, "\n";
275}
276
277
278
279# -----------------------------------------------------------------------------
280#   GDBM IMPLEMENTATION
281# -----------------------------------------------------------------------------
282
283sub open_infodb_write_handle_gdbm
284{
285  my $infodb_file_path = shift(@_);
286
287  my $txt2db_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "txt2db" . &util::get_os_exe());
288  my $infodb_file_handle = undef;
289  if (!-e "$txt2db_exe" || !open($infodb_file_handle, "| \"$txt2db_exe\" \"$infodb_file_path\""))
290  {
291    return undef;
292  }
293
294  return $infodb_file_handle;
295}
296
297
298sub close_infodb_write_handle_gdbm
299{
300  my $infodb_handle = shift(@_);
301
302  close($infodb_handle);
303}
304
305
306sub get_infodb_file_path_gdbm
307{
308  my $collection_name = shift(@_);
309  my $infodb_directory_path = shift(@_);
310
311  my $infodb_file_extension = (&util::is_little_endian() ? ".ldb" : ".bdb");
312  my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
313  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
314}
315
316
317sub read_infodb_file_gdbm
318{
319  my $infodb_file_path = shift(@_);
320  my $infodb_map = shift(@_);
321
322  open (PIPEIN, "db2txt \"$infodb_file_path\" |") || die "couldn't open pipe from db2txt \$infodb_file_path\"\n";
323  my $infodb_line = "";
324  my $infodb_key = "";
325  my $infodb_value = "";
326  while (defined ($infodb_line = <PIPEIN>))
327  {
328    if ($infodb_line =~ /^\[([^\]]+)\]$/)
329    {
330      $infodb_key = $1;
331    }
332    elsif ($infodb_line =~ /^-{70}$/)
333    {
334      $infodb_map->{$infodb_key} = $infodb_value;
335      $infodb_key = "";
336      $infodb_value = "";
337    }
338    else
339    {
340      $infodb_value .= $infodb_line;
341    }
342  }
343
344  close (PIPEIN);
345}
346
347sub read_infodb_keys_gdbm
348{
349  my $infodb_file_path = shift(@_);
350  my $infodb_map = shift(@_);
351
352  open (PIPEIN, "gdbmkeys \"$infodb_file_path\" |") || die "couldn't open pipe from gdbmkeys \$infodb_file_path\"\n";
353  my $infodb_line = "";
354  my $infodb_key = "";
355  my $infodb_value = "";
356  while (defined ($infodb_line = <PIPEIN>))
357  {
358      chomp $infodb_line; # remove end of line
359
360      $infodb_map->{$infodb_line} = 1;
361  }
362
363  close (PIPEIN);
364}
365
366   
367sub write_infodb_entry_gdbm
368{
369  # With infodb_handle already set up, works the same as _gdbm_txtgz version
370  write_infodb_entry_gdbm_txtgz(@_);
371}
372
373
374
375# -----------------------------------------------------------------------------
376#   SQLITE IMPLEMENTATION
377# -----------------------------------------------------------------------------
378
379sub open_infodb_write_handle_sqlite
380{
381  my $infodb_file_path = shift(@_);
382
383  my $sqlite3_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "sqlite3" . &util::get_os_exe());
384  my $infodb_handle = undef;
385  if (!-e "$sqlite3_exe" || !open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\""))
386  {
387    return undef;
388  }
389
390  print $infodb_handle "CREATE TABLE IF NOT EXISTS data (key TEXT PRIMARY KEY, value TEXT);\n";
391  print $infodb_handle "CREATE TABLE IF NOT EXISTS document_metadata (id INTEGER PRIMARY KEY, docOID TEXT, element TEXT, value TEXT);\n";
392
393  # This is crucial for efficiency when importing large amounts of data
394  print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n";
395
396  # This is very important for efficiency, otherwise each command will be actioned one at a time
397  print $infodb_handle "BEGIN TRANSACTION;\n";
398
399  return $infodb_handle;
400}
401
402
403sub close_infodb_write_handle_sqlite
404{
405  my $infodb_handle = shift(@_);
406
407  # Close the transaction we began after opening the file
408  print $infodb_handle "END TRANSACTION;\n";
409
410  # This is crucial for efficient queries on the database!
411  print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";
412
413  close($infodb_handle);
414}
415
416
417sub get_infodb_file_path_sqlite
418{
419  my $collection_name = shift(@_);
420  my $infodb_directory_path = shift(@_);
421
422  my $infodb_file_extension = ".db";
423  my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
424  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
425}
426
427
428sub read_infodb_file_sqlite
429{
430  my $infodb_file_path = shift(@_);
431  my $infodb_map = shift(@_);
432
433  # !! TO IMPLEMENT
434}
435
436
437sub write_infodb_entry_sqlite
438{
439  my $infodb_handle = shift(@_);
440  my $infodb_key = shift(@_);
441  my $infodb_map = shift(@_);
442
443  # Add the key -> value mapping into the "data" table
444  my $infodb_entry_value = "";
445  foreach my $infodb_value_key (keys(%$infodb_map))
446  {
447    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
448    {
449      $infodb_entry_value .= "<$infodb_value_key>" . $infodb_value . "\n";
450    }
451  }
452
453  my $safe_infodb_key = &sqlite_safe($infodb_key);
454  print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n";
455
456  # If this infodb entry is for a document, add all the interesting document metadata to the
457  #   "document_metadata" table (for use by the dynamic classifiers)
458  if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
459  {
460    print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n";
461
462    foreach my $infodb_value_key (keys(%$infodb_map))
463    {
464      # We're not interested in most of the automatically added document metadata
465      next if ($infodb_value_key eq "archivedir" ||
466               $infodb_value_key eq "assocfilepath" ||
467               $infodb_value_key eq "childtype" ||
468               $infodb_value_key eq "contains" ||
469               $infodb_value_key eq "docnum" ||
470               $infodb_value_key eq "doctype" ||
471               $infodb_value_key eq "Encoding" ||
472               $infodb_value_key eq "FileSize" ||
473               $infodb_value_key eq "hascover" ||
474               $infodb_value_key eq "hastxt" ||
475               $infodb_value_key eq "lastmodified" ||
476               $infodb_value_key eq "metadataset" ||
477               $infodb_value_key eq "thistype" ||
478               $infodb_value_key =~ /^metadatafreq\-/ ||
479               $infodb_value_key =~ /^metadatalist\-/);
480
481      foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
482      {
483        print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
484      }
485    }
486  }
487}
488
489
490sub sqlite_safe
491{
492  my $value = shift(@_);
493
494  # Escape any single quotes in the value
495  $value =~ s/\'/\'\'/g;
496
497  return $value;
498}
499
500
501
502# ----------------------------------------------------------------------------------------
503#   MSSQL IMPLEMENTATION
504# ----------------------------------------------------------------------------------------
505
506my $mssql_collection_name = "";
507my $mssql_data_table_name = "";
508my $mssql_document_metadata_table_name = "";
509
510
511sub open_infodb_write_handle_mssql
512{
513  my $infodb_file_path = shift(@_);
514
515  # You might have to install the DBD::ADO module from CPAN
516  #================================================================#
517  # Uncomment this if you want to use MSSQL!!!
518  # By the way, MSSQL only works on a Windows machine...
519  #================================================================#
520  #use DBI;
521  #use DBD::ADO;
522  #Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
523
524  # The hard coded server connection thingy which should be placed
525  # in some configuration file.
526  # If you have problem connecting to your MS SQL server:
527  # 1. Check if your MSSQL server has been started.
528  # 2. Check if the TCP/IP connection has been enabled.
529  # 3. Use telnet to the server
530  # (don't forget to specify the port, which can be found in the configuration manager)
531  # If none of the above helped, the you need to start googling then.
532  my $host = "localhost,1660"; # Need to look up your SQL server and see what port is it using.
533  my $user = "sa";
534  my $pwd = "[When installing the MSSQL, you will be asked to input a password for the sa user, use that password]";
535  my $database = "[Create a database in MSSQL and use it here]";
536 
537  # Create the unique name for the table
538  # We do not want to change the database for the current running index
539  # Therefore we use timestamp and collection short name to create an unqiue name
540  my $cur_time = time();
541  my $unique_key = $mssql_collection_name . "_" . $cur_time;
542  $mssql_data_table_name = "data_" . $unique_key;
543  $mssql_document_metadata_table_name = "document_metadata_" . $unique_key;
544  print STDERR "MSSQL: Creating unique table name. Unique ID:[" . $unique_key . "]\n";
545     
546  # Store these information into the infodbfile 
547  open(FH, ">" . $infodb_file_path);
548  print FH "mss-host\t" . $host . "\n";
549  print FH "username\t" . $user . "\n";
550  print FH "password\t" . $pwd . "\n";
551  print FH "database\t" . $database . "\n";
552  print FH "tableid\t" . $unique_key . "\n";
553  close(FH);
554  print STDERR "MSSQL: Saving db info into :[" . $infodb_file_path . "]\n";
555
556  # Make the connection
557  my $dsn = "Provider=SQLNCLI;Server=$host;Database=$database";
558  my $infodb_handle = DBI->connect("dbi:ADO:$dsn", $user, $pwd, { RaiseError => 1, AutoCommit => 1}) || return undef;
559  print STDERR "MSSQL: Connect to MS SQL database. DSN:[" . $dsn . "]\n";
560
561  # Make sure the data table has been created.
562  my $data_table_checker_array = dbgetarray($infodb_handle, "SELECT name FROM sysobjects WHERE name = '" . $mssql_data_table_name . "' AND OBJECTPROPERTY(id,'IsUserTable') = 1");
563  if (scalar(@{$data_table_checker_array}) == 0)
564  {
565    dbquery($infodb_handle, "CREATE TABLE " . $mssql_data_table_name . " (one_key NVARCHAR(50) UNIQUE, one_value NVARCHAR(MAX))");
566  }
567  print STDERR "MSSQL: Making sure the data table(" . $mssql_data_table_name . ") exists\n";
568   
569  # Make sure the document_metadata table has been created.
570  my $document_metadata_table_checker_array = dbgetarray($infodb_handle, "SELECT name FROM sysobjects WHERE name = '" . $mssql_document_metadata_table_name . "' AND OBJECTPROPERTY(id,'IsUserTable') = 1");
571  if (scalar(@{$document_metadata_table_checker_array}) == 0)
572  {
573    dbquery($infodb_handle, "CREATE TABLE " . $mssql_document_metadata_table_name . " (id INTEGER IDENTITY(1,1) PRIMARY KEY, docOID NVARCHAR(50), element NVARCHAR(MAX), value NVARCHAR(MAX))");
574    dbquery($infodb_handle, "CREATE INDEX dmd ON " . $mssql_document_metadata_table_name . "(docOID)");
575  }
576  print STDERR "MSSQL: Making sure the document_metadata table(" . $mssql_data_table_name . ") exists.\n";
577 
578  return $infodb_handle;
579}
580
581
582sub close_infodb_write_handle_mssql
583{
584  my $infodb_handle = shift(@_);   
585 
586  $infodb_handle->disconnect();
587}
588
589
590sub get_infodb_file_path_mssql
591{
592  my $collection_name = shift(@_);
593  my $infodb_directory_path = shift(@_);
594
595  my $infodb_file_extension = ".mssqldbinfo";
596  my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
597 
598  # This will be used in the open_infodb_write_handle_mssql function
599  $mssql_collection_name =  $collection_name;
600
601  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
602}
603
604
605sub read_infodb_file_mssql
606{
607  my $infodb_file_path = shift(@_);
608  my $infodb_map = shift(@_);
609
610  # !! TO IMPLEMENT
611}
612
613
614sub write_infodb_entry_mssql
615{
616  my $infodb_handle = shift(@_);
617  my $infodb_key = shift(@_);
618  my $infodb_map = shift(@_);
619 
620  # Add the key -> value mapping into the "data" table
621  my $infodb_entry_value = "";
622  foreach my $infodb_value_key (keys(%$infodb_map))
623  {
624    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
625    {
626      $infodb_entry_value .= "<$infodb_value_key>" . &Encode::decode_utf8($infodb_value) . "\n";
627    }
628  }
629 
630  # Prepare the query
631  my $safe_infodb_key = &mssql_safe($infodb_key);
632  my $query = "INSERT INTO " . $mssql_data_table_name . " (one_key, one_value) VALUES (N'" . $safe_infodb_key . "', N'" . &mssql_safe($infodb_entry_value) . "')";
633  dbquery($infodb_handle, $query);
634 
635  # If this infodb entry is for a document, add all the interesting document metadata to the
636  # "document_metadata" table (for use by the dynamic classifiers)
637  if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
638  {
639    dbquery($infodb_handle, "DELETE FROM " . $mssql_document_metadata_table_name . " WHERE docOID=N'" . $safe_infodb_key . "'");
640   
641    foreach my $infodb_value_key (keys(%$infodb_map))
642    {           
643      # We're not interested in most of the automatically added document metadata
644      next if ($infodb_value_key eq "archivedir" ||
645               $infodb_value_key eq "assocfilepath" ||
646               $infodb_value_key eq "childtype" ||
647               $infodb_value_key eq "contains" ||
648               $infodb_value_key eq "docnum" ||
649               $infodb_value_key eq "doctype" ||
650               $infodb_value_key eq "Encoding" ||
651               $infodb_value_key eq "FileSize" ||
652               $infodb_value_key eq "hascover" ||
653               $infodb_value_key eq "hastxt" ||
654               $infodb_value_key eq "lastmodified" ||
655               $infodb_value_key eq "metadataset" ||
656               $infodb_value_key eq "thistype" ||
657               $infodb_value_key =~ /^metadatafreq\-/ ||
658               $infodb_value_key =~ /^metadatalist\-/);
659      foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
660      {
661        $infodb_handle->{LongReadLen} = 65535; # Added for the encoding issue
662        my $query = "INSERT INTO " . $mssql_document_metadata_table_name . " (docOID, element, value) VALUES (N'" . $safe_infodb_key . "', N'" . &mssql_safe(&Encode::decode_utf8($infodb_value_key)) . "', N'" . &mssql_safe(&Encode::decode_utf8($infodb_value)) . "')";
663        dbquery($infodb_handle, $query);
664      }
665    }
666  }
667}
668
669
670sub mssql_safe
671{
672  my $value = shift(@_);
673 
674  # Escape any single quotes in the value
675  $value =~ s/\'/\'\'/g;
676 
677  return $value;
678}
679
680
681sub dbquery
682{
683  my $infodb_handle = shift(@_);
684  my $sql_query = shift(@_);
685
686  # Execute the SQL statement
687  my $statement_handle = $infodb_handle->prepare($sql_query);
688  $statement_handle->execute();
689  if ($statement_handle->err)
690  {
691    print STDERR "Error:" . $statement_handle->errstr . "\n";
692    return undef;
693  }
694 
695  return $statement_handle;
696}
697
698
699sub dbgetarray
700{
701  my $infodb_handle = shift(@_);
702  my $sql_query = shift(@_);
703 
704  my $statement_handle = dbquery($infodb_handle, $sql_query);
705  my $return_array = [];
706 
707  # Iterate through the results and push them into an array
708  if (!defined($statement_handle))
709  {
710    return [];
711  }
712 
713  while ((my $temp_hash = $statement_handle->fetchrow_hashref()))
714  {
715    push(@$return_array, $temp_hash);
716  }
717
718  return $return_array;
719}
720
721
7221;
Note: See TracBrowser for help on using the browser.