source: gsdl/trunk/perllib/dbutil.pm@ 18441

Last change on this file since 18441 was 18441, checked in by davidb, 15 years ago

Modifications for incremental building to support files that need to be deleted
File size: 22.1 KB
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 repository browser.