source: main/trunk/greenstone2/perllib/dbutil/mssql.pm@ 21411

Last change on this file since 21411 was 21411, checked in by davidb, 14 years ago

Separation of different database back-ends into individual files
File size: 9.8 KB
Line 
1###########################################################################
2#
3# dbutil::mssql -- utility functions for writing to mssql databases
4#
5# A component of the Greenstone digital library software
6# from the New Zealand Digital Library Project at the
7# University of Waikato, New Zealand.
8#
9# Copyright (C) 2009
10#
11# This program is free software; you can redistribute it and/or modify
12# it under the terms of the GNU General Public License as published by
13# the Free Software Foundation; either version 2 of the License, or
14# (at your option) any later version.
15#
16# This program is distributed in the hope that it will be useful,
17# but WITHOUT ANY WARRANTY; without even the implied warranty of
18# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
19# GNU General Public License for more details.
20#
21# You should have received a copy of the GNU General Public License
22# along with this program; if not, write to the Free Software
23# Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
24#
25###########################################################################
26
27package dbutil::mssql;
28
29use Encode;
30use strict;
31
32
33
34# -----------------------------------------------------------------------------
35# MSSQL IMPLEMENTATION
36# -----------------------------------------------------------------------------
37
38my $mssql_collection_name = "";
39my $mssql_data_table_name = "";
40my $mssql_document_metadata_table_name = "";
41
42
43sub open_infodb_write_handle
44{
45 my $infodb_file_path = shift(@_);
46
47 # You might have to install the DBD::ADO module from CPAN
48 #================================================================#
49 # Uncomment this if you want to use MSSQL!!!
50 # By the way, MSSQL only works on a Windows machine...
51 #================================================================#
52 #use DBI;
53 #use DBD::ADO;
54 #Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
55
56 # The hard coded server connection thingy which should be placed
57 # in some configuration file.
58 # If you have problem connecting to your MS SQL server:
59 # 1. Check if your MSSQL server has been started.
60 # 2. Check if the TCP/IP connection has been enabled.
61 # 3. Use telnet to the server
62 # (don't forget to specify the port, which can be found in the configuration manager)
63 # If none of the above helped, the you need to start googling then.
64 my $host = "localhost,1660"; # Need to look up your SQL server and see what port is it using.
65 my $user = "sa";
66 my $pwd = "[When installing the MSSQL, you will be asked to input a password for the sa user, use that password]";
67 my $database = "[Create a database in MSSQL and use it here]";
68
69 # Create the unique name for the table
70 # We do not want to change the database for the current running index
71 # Therefore we use timestamp and collection short name to create an unqiue name
72 my $cur_time = time();
73 my $unique_key = $mssql_collection_name . "_" . $cur_time;
74 $mssql_data_table_name = "data_" . $unique_key;
75 $mssql_document_metadata_table_name = "document_metadata_" . $unique_key;
76 print STDERR "MSSQL: Creating unique table name. Unique ID:[" . $unique_key . "]\n";
77
78 # Store these information into the infodbfile
79 open(FH, ">" . $infodb_file_path);
80 print FH "mss-host\t" . $host . "\n";
81 print FH "username\t" . $user . "\n";
82 print FH "password\t" . $pwd . "\n";
83 print FH "database\t" . $database . "\n";
84 print FH "tableid\t" . $unique_key . "\n";
85 close(FH);
86 print STDERR "MSSQL: Saving db info into :[" . $infodb_file_path . "]\n";
87
88 # Make the connection
89 my $dsn = "Provider=SQLNCLI;Server=$host;Database=$database";
90 my $infodb_handle = DBI->connect("dbi:ADO:$dsn", $user, $pwd, { RaiseError => 1, AutoCommit => 1}) || return undef;
91 print STDERR "MSSQL: Connect to MS SQL database. DSN:[" . $dsn . "]\n";
92
93 # Make sure the data table has been created.
94 my $data_table_checker_array = dbgetarray($infodb_handle, "SELECT name FROM sysobjects WHERE name = '" . $mssql_data_table_name . "' AND OBJECTPROPERTY(id,'IsUserTable') = 1");
95 if (scalar(@{$data_table_checker_array}) == 0)
96 {
97 dbquery($infodb_handle, "CREATE TABLE " . $mssql_data_table_name . " (one_key NVARCHAR(50) UNIQUE, one_value NVARCHAR(MAX))");
98 }
99 print STDERR "MSSQL: Making sure the data table(" . $mssql_data_table_name . ") exists\n";
100
101 # Make sure the document_metadata table has been created.
102 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");
103 if (scalar(@{$document_metadata_table_checker_array}) == 0)
104 {
105 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))");
106 dbquery($infodb_handle, "CREATE INDEX dmd ON " . $mssql_document_metadata_table_name . "(docOID)");
107 }
108 print STDERR "MSSQL: Making sure the document_metadata table(" . $mssql_data_table_name . ") exists.\n";
109
110 return $infodb_handle;
111}
112
113
114sub close_infodb_write_handle
115{
116 my $infodb_handle = shift(@_);
117
118 $infodb_handle->disconnect();
119}
120
121
122sub get_infodb_file_path
123{
124 my $collection_name = shift(@_);
125 my $infodb_directory_path = shift(@_);
126
127 my $infodb_file_extension = ".mssqldbinfo";
128 my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
129
130 # This will be used in the open_infodb_write_handle function
131 $mssql_collection_name = $collection_name;
132
133 return &util::filename_cat($infodb_directory_path, $infodb_file_name);
134}
135
136
137sub read_infodb_file
138{
139 my $infodb_file_path = shift(@_);
140 my $infodb_map = shift(@_);
141
142 # !! TO IMPLEMENT
143}
144
145
146sub write_infodb_entry
147{
148 my $infodb_handle = shift(@_);
149 my $infodb_key = shift(@_);
150 my $infodb_map = shift(@_);
151
152 # Add the key -> value mapping into the "data" table
153 my $infodb_entry_value = "";
154 foreach my $infodb_value_key (keys(%$infodb_map))
155 {
156 foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
157 {
158 $infodb_entry_value .= "<$infodb_value_key>" . &Encode::decode_utf8($infodb_value) . "\n";
159 }
160 }
161
162 # Prepare the query
163 my $safe_infodb_key = &mssql_safe($infodb_key);
164 my $query = "INSERT INTO " . $mssql_data_table_name . " (one_key, one_value) VALUES (N'" . $safe_infodb_key . "', N'" . &mssql_safe($infodb_entry_value) . "')";
165 dbquery($infodb_handle, $query);
166
167 # If this infodb entry is for a document, add all the interesting document metadata to the
168 # "document_metadata" table (for use by the dynamic classifiers)
169 if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
170 {
171 dbquery($infodb_handle, "DELETE FROM " . $mssql_document_metadata_table_name . " WHERE docOID=N'" . $safe_infodb_key . "'");
172
173 foreach my $infodb_value_key (keys(%$infodb_map))
174 {
175 # We're not interested in most of the automatically added document metadata
176 next if ($infodb_value_key eq "archivedir" ||
177 $infodb_value_key eq "assocfilepath" ||
178 $infodb_value_key eq "childtype" ||
179 $infodb_value_key eq "contains" ||
180 $infodb_value_key eq "docnum" ||
181 $infodb_value_key eq "doctype" ||
182 $infodb_value_key eq "Encoding" ||
183 $infodb_value_key eq "FileSize" ||
184 $infodb_value_key eq "hascover" ||
185 $infodb_value_key eq "hastxt" ||
186 $infodb_value_key eq "lastmodified" ||
187 $infodb_value_key eq "metadataset" ||
188 $infodb_value_key eq "thistype" ||
189 $infodb_value_key =~ /^metadatafreq\-/ ||
190 $infodb_value_key =~ /^metadatalist\-/);
191 foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
192 {
193 $infodb_handle->{LongReadLen} = 65535; # Added for the encoding issue
194 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)) . "')";
195 dbquery($infodb_handle, $query);
196 }
197 }
198 }
199}
200
201sub delete_infodb_entry
202{
203 my $infodb_handle = shift(@_);
204 my $infodb_key = shift(@_);
205
206 # Delete the key from the "data" table
207
208
209 # Prepare the query
210 my $safe_infodb_key = &mssql_safe($infodb_key);
211 my $query = "DELETE FROM " . $mssql_data_table_name . " WHERE one_key=N'" . $safe_infodb_key . "'";
212 dbquery($infodb_handle, $query);
213
214 # If this infodb entry is for a document, add all the interesting document metadata to the
215 # "document_metadata" table (for use by the dynamic classifiers)
216 if ($infodb_key !~ /\./)
217 {
218 # Possible for there not to be a docOID matching this infodb_key
219 # (entries are only made when <doctype> == doc
220 # Attempt to delete it, and don't complain if one isn't found
221
222 dbquery($infodb_handle, "DELETE FROM " . $mssql_document_metadata_table_name . " WHERE docOID=N'" . $safe_infodb_key . "'");
223
224 }
225}
226
227
228
229sub mssql_safe
230{
231 my $value = shift(@_);
232
233 # Escape any single quotes in the value
234 $value =~ s/\'/\'\'/g;
235
236 return $value;
237}
238
239
240sub dbquery
241{
242 my $infodb_handle = shift(@_);
243 my $sql_query = shift(@_);
244
245 # Execute the SQL statement
246 my $statement_handle = $infodb_handle->prepare($sql_query);
247 $statement_handle->execute();
248 if ($statement_handle->err)
249 {
250 print STDERR "Error:" . $statement_handle->errstr . "\n";
251 return undef;
252 }
253
254 return $statement_handle;
255}
256
257
258sub dbgetarray
259{
260 my $infodb_handle = shift(@_);
261 my $sql_query = shift(@_);
262
263 my $statement_handle = dbquery($infodb_handle, $sql_query);
264 my $return_array = [];
265
266 # Iterate through the results and push them into an array
267 if (!defined($statement_handle))
268 {
269 return [];
270 }
271
272 while ((my $temp_hash = $statement_handle->fetchrow_hashref()))
273 {
274 push(@$return_array, $temp_hash);
275 }
276
277 return $return_array;
278}
279
280
2811;
Note: See TracBrowser for help on using the repository browser.