Changeset 17476


Ignore:
Timestamp:
2008-10-03T16:00:50+13:00 (16 years ago)
Author:
mdewsnip
Message:

Support for using MSSQL for infodb databases, many thanks to Jeffrey Ke from DL Consulting Ltd. (​http://www.dlconsulting.com). Please note that MSSQL only runs on Windows, and requires some setup before use. Documentation will be added to the Greenstone Wiki explaining this.

Location:
gsdl/trunk
Files:
2 added
7 edited

Legend:

Unmodified
Added
Removed

  • gsdl/trunk/common-src/src/lib/win32.mak

    r16577 r17476  
    3535DLLDEBUG = 0
    3636USE_SQLITE = 0
     37USE_MSSQL = 0
    3738
    3839
    … …  
    4950CPPFLAGS = $(CPPFLAGS) -MDd
    5051!ENDIF
     52!ENDIF
     53
     54!IF $(USE_MSSQL)
     55MSSQL_OBJECTS = mssqldbclass.obj
     56MSSQL_SOURCES = mssqldbclass.cpp
     57!ELSE
     58MSSQL_OBJECTS =
     59MSSQL_SOURCES =
    5160!ENDIF
    5261
    … …  
    102111    phrases.cpp \
    103112    text_t.cpp \
    104     $(SQLITE_SOURCES)
     113    $(SQLITE_SOURCES) \
     114    $(MSSQL_SOURCES)
    105115
    106116OBJECTS = \
    … …  
    121131    phrases.obj \
    122132    text_t.obj \
    123     $(SQLITE_OBJECTS)
     133    $(SQLITE_OBJECTS) \
     134        $(MSSQL_OBJECTS)
    124135
    125136LIBRARY = gsdllib.lib   

  • gsdl/trunk/common-src/win32.mak

    r16903 r17476  
    3131DLLDEBUG = 0
    3232USE_SQLITE = 0
     33USE_MSSQL = 0
    3334
    3435MDEFINES = /f win32.mak
    … …  
    5960MAKECMD = $(MAKECMD) USE_SQLITE=1
    6061PACKAGEDIRS = $(PACKAGEDIRS) $(SQLITE_DIR)
     62!ENDIF
     63!IF $(USE_MSSQL)
     64MAKECMD = $(MAKECMD) USE_MSSQL=1
    6165!ENDIF
    6266

  • gsdl/trunk/perllib/dbutil.pm

    r17105 r17476  
    2626package dbutil;
    2727
     28use Encode;
    2829use strict;
    2930
    … …  
    3132sub open_infodb_write_handle
    3233{
    33     my $infodb_type = shift(@_);
    34     my $infodb_file_path = shift(@_);
    35 
    36     if ($infodb_type eq "sqlite")
    37     {
    38     return &open_infodb_write_handle_sqlite($infodb_file_path);
    39     }
    40     elsif ($infodb_type eq "gdbm-txtgz")
    41     {
    42     return &open_infodb_write_handle_gdbm_txtgz($infodb_file_path);
    43     }
    44 
    45     # Use GDBM if the infodb type is empty or not one of the values above
    46     return &open_infodb_write_handle_gdbm($infodb_file_path);
     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);
    4752}
    4853
    … …  
    5055sub close_infodb_write_handle
    5156{
    52     my $infodb_type = shift(@_);
    53     my $infodb_handle = shift(@_);
    54 
    55     if ($infodb_type eq "sqlite")
    56     {
    57     return &close_infodb_write_handle_sqlite($infodb_handle);
    58     }
    59     elsif ($infodb_type eq "gdbm-txtgz")
    60     {
    61     return &close_infodb_write_handle_gdbm_txtgz($infodb_handle);
    62     }
    63 
    64     # Use GDBM if the infodb type is empty or not one of the values above
    65     &close_infodb_write_handle_gdbm($infodb_handle);
     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);
    6675}
    6776
    … …  
    6978sub get_default_infodb_type
    7079{
    71     # The default is GDBM so everything works the same for existing collections
    72     # To use something else, specify the "infodbtype" in the collection's collect.cfg file
    73     return "gdbm";
     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";
    7483}
    7584
    … …  
    7786sub get_infodb_file_path
    7887{
    79     my $infodb_type = shift(@_);
    80     my $collection_name = shift(@_);
    81     my $infodb_directory_path = shift(@_);
    82 
    83     if ($infodb_type eq "sqlite")
    84     {
    85     return &get_infodb_file_path_sqlite($collection_name, $infodb_directory_path);
    86     }
    87     elsif ($infodb_type eq "gdbm-txtgz")
    88     {
    89     return &get_infodb_file_path_gdbm_txtgz($collection_name, $infodb_directory_path);
    90     }
    91 
    92     # Use GDBM if the infodb type is empty or not one of the values above
    93     return &get_infodb_file_path_gdbm($collection_name, $infodb_directory_path);
     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);
    94118}
    95119
    … …  
    97121sub read_infodb_file
    98122{
    99     my $infodb_type = shift(@_);
    100     my $infodb_file_path = shift(@_);
    101     my $infodb_map = shift(@_);
    102 
    103     if ($infodb_type eq "sqlite")
    104     {
    105     return &read_infodb_file_sqlite($infodb_file_path, $infodb_map);
    106     }
    107     elsif ($infodb_type eq "gdbm-txtgz")
    108     {
    109     return &read_infodb_file_gdbm_txtgz($infodb_file_path, $infodb_map);
    110     }
    111 
    112     # Use GDBM if the infodb type is empty or not one of the values above
    113     return &read_infodb_file_gdbm($infodb_file_path, $infodb_map);
     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);
    114142}
    115143
    … …  
    117145sub write_infodb_entry
    118146{
    119     my $infodb_type = shift(@_);
    120     my $infodb_handle = shift(@_);
    121     my $infodb_key = shift(@_);
    122     my $infodb_map = shift(@_);
    123 
    124     if ($infodb_type eq "sqlite")
    125     {
    126     return &write_infodb_entry_sqlite($infodb_handle, $infodb_key, $infodb_map);
    127     }
    128     elsif ($infodb_type eq "gdbm-txtgz")
    129     {
    130     return &write_infodb_entry_gdbm_txtgz($infodb_handle, $infodb_key, $infodb_map);
    131     }
    132 
    133     # Use GDBM if the infodb type is empty or not one of the values above
    134     return &write_infodb_entry_gdbm($infodb_handle, $infodb_key, $infodb_map);
     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);
    135167}
    136168
    … …  
    143175sub open_infodb_write_handle_gdbm_txtgz
    144176{
    145     # Keep infodb in GDBM neutral form => save data as compressed text file,
    146     # read for txt2db to be run on it later (i.e. by the runtime system,
    147     # first time the collection is ever accessed).  This makes it easier
    148     # distribute pre-built collections to various architectures.
    149     #
    150     # NB: even if two architectures are little endian (e.g. Intel and
    151     # ARM procesors) GDBM does *not* guarantee that the database generated on
    152     # one will work on the other
    153 
    154     my $infodb_file_path = shift(@_);
    155 
    156     # Greenstone ships with gzip for windows, on $PATH
    157 
    158     my $infodb_file_handle = undef;
    159     if (!open($infodb_file_handle, "| gzip - > \"$infodb_file_path\""))
    160     {
    161     return undef;
    162     }
    163 
    164     return $infodb_file_handle;
     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;
    165197}
    166198
    … …  
    168200sub close_infodb_write_handle_gdbm_txtgz
    169201{
    170     my $infodb_handle = shift(@_);
    171 
    172     close($infodb_handle);
     202  my $infodb_handle = shift(@_);
     203
     204  close($infodb_handle);
    173205}
    174206
    … …  
    176208sub get_infodb_file_path_gdbm_txtgz
    177209{
    178     my $collection_name = shift(@_);
    179     my $infodb_directory_path = shift(@_);
    180 
    181     my $infodb_file_name = &util::get_dirsep_tail($collection_name).".txt.gz";
    182     return &util::filename_cat($infodb_directory_path, $infodb_file_name);
     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);
    183215}
    184216
    … …  
    186218sub read_infodb_file_gdbm_txtgz
    187219{
    188     my $infodb_file_path = shift(@_);
    189     my $infodb_map = shift(@_);
    190 
    191     my $cmd = "gzip --decompress \"$infodb_file_path\"";
    192 
    193     open (PIPEIN, "$cmd |")
    194     || die "Error: Couldn't open pipe from gzip: $!\n  $cmd\n";
    195 
    196     my $infodb_line = "";
    197     my $infodb_key = "";
    198     my $infodb_value = "";
    199     while (defined ($infodb_line = <PIPEIN>))
    200     {
    201     if ($infodb_line =~ /^\[([^\]]+)\]$/)
    202     {
    203         $infodb_key = $1;
    204     }
    205     elsif ($infodb_line =~ /^-{70}$/)
    206     {
    207         $infodb_map->{$infodb_key} = $infodb_value;
    208         $infodb_key = "";
    209         $infodb_value = "";
    210     }
    211     else
    212     {
    213         $infodb_value .= $infodb_line;
    214     }
    215     }
    216 
    217     close (PIPEIN);
     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);
    218250}
    219251
    … …  
    222254{
    223255
    224     my $infodb_handle = shift(@_);
    225     my $infodb_key = shift(@_);
    226     my $infodb_map = shift(@_);
    227 
    228     print $infodb_handle "[$infodb_key]\n";
    229     foreach my $infodb_value_key (keys(%$infodb_map))
    230     {
    231     foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
    232     {
    233         if ($infodb_value =~ /-{70,}/)
    234         {
    235         # if value contains 70 or more hyphens in a row we need to escape them
    236         # to prevent txt2db from treating them as a separator
    237         $infodb_value =~ s/-/&\#045;/gi;
    238         }
    239         print $infodb_handle "<$infodb_value_key>" . $infodb_value . "\n";
    240     }
    241     }
    242     print $infodb_handle '-' x 70, "\n";
     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";
    243275}
    244276
    … …  
    251283sub open_infodb_write_handle_gdbm
    252284{
    253     my $infodb_file_path = shift(@_);
    254 
    255     my $txt2db_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "txt2db" . &util::get_os_exe());
    256     my $infodb_file_handle = undef;
    257     if (!-e "$txt2db_exe" || !open($infodb_file_handle, "| \"$txt2db_exe\" \"$infodb_file_path\""))
    258     {
    259     return undef;
    260     }
    261 
    262     return $infodb_file_handle;
     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;
    263295}
    264296
    … …  
    266298sub close_infodb_write_handle_gdbm
    267299{
    268     my $infodb_handle = shift(@_);
    269 
    270     close($infodb_handle);
     300  my $infodb_handle = shift(@_);
     301
     302  close($infodb_handle);
    271303}
    272304
    … …  
    274306sub get_infodb_file_path_gdbm
    275307{
    276     my $collection_name = shift(@_);
    277     my $infodb_directory_path = shift(@_);
    278 
    279     my $infodb_file_extension = (&util::is_little_endian() ? ".ldb" : ".bdb");
    280     my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
    281     return &util::filename_cat($infodb_directory_path, $infodb_file_name);
     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);
    282314}
    283315
    … …  
    285317sub read_infodb_file_gdbm
    286318{
    287     my $infodb_file_path = shift(@_);
    288     my $infodb_map = shift(@_);
    289 
    290     open (PIPEIN, "db2txt \"$infodb_file_path\" |") || die "couldn't open pipe from db2txt\n";
    291     my $infodb_line = "";
    292     my $infodb_key = "";
    293     my $infodb_value = "";
    294     while (defined ($infodb_line = <PIPEIN>))
    295     {
    296     if ($infodb_line =~ /^\[([^\]]+)\]$/)
    297     {
    298         $infodb_key = $1;
    299     }
    300     elsif ($infodb_line =~ /^-{70}$/)
    301     {
    302         $infodb_map->{$infodb_key} = $infodb_value;
    303         $infodb_key = "";
    304         $infodb_value = "";
    305     }
    306     else
    307     {
    308         $infodb_value .= $infodb_line;
    309     }
    310     }
    311 
    312     close (PIPEIN);
     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\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);
    313345}
    314346
    … …  
    316348sub write_infodb_entry_gdbm
    317349{
    318     # With infodb_handle already set up, works the same as _gdbm_txtgz version
    319     write_infodb_entry_gdbm_txtgz(@_);
     350  # With infodb_handle already set up, works the same as _gdbm_txtgz version
     351  write_infodb_entry_gdbm_txtgz(@_);
    320352}
    321353
    … …  
    328360sub open_infodb_write_handle_sqlite
    329361{
    330     my $infodb_file_path = shift(@_);
    331 
    332     my $sqlite3_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "sqlite3" . &util::get_os_exe());
    333     my $infodb_handle = undef;
    334     if (!-e "$sqlite3_exe" || !open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\""))
    335     {
    336     return undef;
    337     }
    338 
    339     print $infodb_handle "CREATE TABLE IF NOT EXISTS data (key TEXT PRIMARY KEY, value TEXT);\n";
    340     print $infodb_handle "CREATE TABLE IF NOT EXISTS document_metadata (id INTEGER PRIMARY KEY, docOID TEXT, element TEXT, value TEXT);\n";
    341 
    342     # This is crucial for efficiency when importing large amounts of data
    343     print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n";
    344 
    345     # This is very important for efficiency, otherwise each command will be actioned one at a time
    346     print $infodb_handle "BEGIN TRANSACTION;\n";
    347 
    348     return $infodb_handle;
     362  my $infodb_file_path = shift(@_);
     363
     364  my $sqlite3_exe = &util::filename_cat($ENV{'GSDLHOME'},"bin",$ENV{'GSDLOS'}, "sqlite3" . &util::get_os_exe());
     365  my $infodb_handle = undef;
     366  if (!-e "$sqlite3_exe" || !open($infodb_handle, "| \"$sqlite3_exe\" \"$infodb_file_path\""))
     367  {
     368    return undef;
     369  }
     370
     371  print $infodb_handle "CREATE TABLE IF NOT EXISTS data (key TEXT PRIMARY KEY, value TEXT);\n";
     372  print $infodb_handle "CREATE TABLE IF NOT EXISTS document_metadata (id INTEGER PRIMARY KEY, docOID TEXT, element TEXT, value TEXT);\n";
     373
     374  # This is crucial for efficiency when importing large amounts of data
     375  print $infodb_handle "CREATE INDEX IF NOT EXISTS dmd ON document_metadata(docOID);\n";
     376
     377  # This is very important for efficiency, otherwise each command will be actioned one at a time
     378  print $infodb_handle "BEGIN TRANSACTION;\n";
     379
     380  return $infodb_handle;
    349381}
    350382
    … …  
    352384sub close_infodb_write_handle_sqlite
    353385{
    354     my $infodb_handle = shift(@_);
    355 
    356     # Close the transaction we began after opening the file
    357     print $infodb_handle "END TRANSACTION;\n";
    358 
    359     # This is crucial for efficient queries on the database!
    360     print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";
    361 
    362     close($infodb_handle);
     386  my $infodb_handle = shift(@_);
     387
     388  # Close the transaction we began after opening the file
     389  print $infodb_handle "END TRANSACTION;\n";
     390
     391  # This is crucial for efficient queries on the database!
     392  print $infodb_handle "CREATE INDEX IF NOT EXISTS dme ON document_metadata(element);\n";
     393
     394  close($infodb_handle);
    363395}
    364396
    … …  
    366398sub get_infodb_file_path_sqlite
    367399{
    368     my $collection_name = shift(@_);
    369     my $infodb_directory_path = shift(@_);
    370 
    371     my $infodb_file_extension = ".db";
    372     my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
    373     return &util::filename_cat($infodb_directory_path, $infodb_file_name);
     400  my $collection_name = shift(@_);
     401  my $infodb_directory_path = shift(@_);
     402
     403  my $infodb_file_extension = ".db";
     404  my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
     405  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
    374406}
    375407
    … …  
    377409sub read_infodb_file_sqlite
    378410{
    379     my $infodb_file_path = shift(@_);
    380     my $infodb_map = shift(@_);
    381 
    382     # !! TO IMPLEMENT
     411  my $infodb_file_path = shift(@_);
     412  my $infodb_map = shift(@_);
     413
     414  # !! TO IMPLEMENT
    383415}
    384416
    … …  
    386418sub write_infodb_entry_sqlite
    387419{
    388     my $infodb_handle = shift(@_);
    389     my $infodb_key = shift(@_);
    390     my $infodb_map = shift(@_);
    391 
    392     # Add the key -> value mapping into the "data" table
    393     my $infodb_entry_value = "";
     420  my $infodb_handle = shift(@_);
     421  my $infodb_key = shift(@_);
     422  my $infodb_map = shift(@_);
     423
     424  # Add the key -> value mapping into the "data" table
     425  my $infodb_entry_value = "";
     426  foreach my $infodb_value_key (keys(%$infodb_map))
     427  {
     428    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
     429    {
     430      $infodb_entry_value .= "<$infodb_value_key>" . $infodb_value . "\n";
     431    }
     432  }
     433
     434  my $safe_infodb_key = &sqlite_safe($infodb_key);
     435  print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n";
     436
     437  # If this infodb entry is for a document, add all the interesting document metadata to the
     438  #   "document_metadata" table (for use by the dynamic classifiers)
     439  if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
     440  {
     441    print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n";
     442
    394443    foreach my $infodb_value_key (keys(%$infodb_map))
    395444    {
    396     foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
    397     {
    398         $infodb_entry_value .= "<$infodb_value_key>" . $infodb_value . "\n";
    399     }
    400     }
    401 
    402     my $safe_infodb_key = &sqlite_safe($infodb_key);
    403     print $infodb_handle "INSERT OR REPLACE INTO data (key, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_entry_value) . "');\n";
    404 
    405     # If this infodb entry is for a document, add all the interesting document metadata to the
    406     #   "document_metadata" table (for use by the dynamic classifiers)
    407     if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
    408     {
    409     print $infodb_handle "DELETE FROM document_metadata WHERE docOID='" . $safe_infodb_key . "';\n";
    410 
    411     foreach my $infodb_value_key (keys(%$infodb_map))
    412     {
    413         # We're not interested in most of the automatically added document metadata
    414         next if ($infodb_value_key eq "archivedir" ||
    415              $infodb_value_key eq "assocfilepath" ||
    416              $infodb_value_key eq "childtype" ||
    417              $infodb_value_key eq "contains" ||
    418              $infodb_value_key eq "docnum" ||
    419              $infodb_value_key eq "doctype" ||
    420              $infodb_value_key eq "Encoding" ||
    421              $infodb_value_key eq "FileSize" ||
    422              $infodb_value_key eq "hascover" ||
    423              $infodb_value_key eq "hastxt" ||
    424              $infodb_value_key eq "lastmodified" ||
    425              $infodb_value_key eq "metadataset" ||
    426              $infodb_value_key eq "thistype" ||
    427              $infodb_value_key =~ /^metadatafreq\-/ ||
    428              $infodb_value_key =~ /^metadatalist\-/);
    429 
    430         foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
    431         {
    432         print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
    433         }
    434     }
    435     }
     445      # We're not interested in most of the automatically added document metadata
     446      next if ($infodb_value_key eq "archivedir" ||
     447               $infodb_value_key eq "assocfilepath" ||
     448               $infodb_value_key eq "childtype" ||
     449               $infodb_value_key eq "contains" ||
     450               $infodb_value_key eq "docnum" ||
     451               $infodb_value_key eq "doctype" ||
     452               $infodb_value_key eq "Encoding" ||
     453               $infodb_value_key eq "FileSize" ||
     454               $infodb_value_key eq "hascover" ||
     455               $infodb_value_key eq "hastxt" ||
     456               $infodb_value_key eq "lastmodified" ||
     457               $infodb_value_key eq "metadataset" ||
     458               $infodb_value_key eq "thistype" ||
     459               $infodb_value_key =~ /^metadatafreq\-/ ||
     460               $infodb_value_key =~ /^metadatalist\-/);
     461
     462      foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
     463      {
     464        print $infodb_handle "INSERT INTO document_metadata (docOID, element, value) VALUES ('" . $safe_infodb_key . "', '" . &sqlite_safe($infodb_value_key) . "', '" . &sqlite_safe($infodb_value) . "');\n";
     465      }
     466    }
     467  }
    436468}
    437469
    … …  
    439471sub sqlite_safe
    440472{
    441     my $value = shift(@_);
    442 
    443     # Escape any single quotes in the value
    444     $value =~ s/\'/\'\'/g;
    445 
    446     return $value;
     473  my $value = shift(@_);
     474
     475  # Escape any single quotes in the value
     476  $value =~ s/\'/\'\'/g;
     477
     478  return $value;
     479}
     480
     481
     482
     483# ----------------------------------------------------------------------------------------
     484#   MSSQL IMPLEMENTATION
     485# ----------------------------------------------------------------------------------------
     486
     487my $mssql_collection_name = "";
     488my $mssql_data_table_name = "";
     489my $mssql_document_metadata_table_name = "";
     490
     491
     492sub open_infodb_write_handle_mssql
     493{
     494  my $infodb_file_path = shift(@_);
     495
     496  # You might have to install the DBD::ADO module from CPAN
     497  #================================================================#
     498  # Uncomment this if you want to use MSSQL!!!
     499  # By the way, MSSQL only works on a Windows machine...
     500  #================================================================#
     501  #use DBI;
     502  #use DBD::ADO;
     503  #Win32::OLE->Option(CP => Win32::OLE::CP_UTF8);
     504
     505  # The hard coded server connection thingy which should be placed
     506  # in some configuration file.
     507  # If you have problem connecting to your MS SQL server:
     508  # 1. Check if your MSSQL server has been started.
     509  # 2. Check if the TCP/IP connection has been enabled.
     510  # 3. Use telnet to the server
     511  # (don't forget to specify the port, which can be found in the configuration manager)
     512  # If none of the above helped, the you need to start googling then.
     513  my $host = "localhost,1660"; # Need to look up your SQL server and see what port is it using.
     514  my $user = "sa";
     515  my $pwd = "[When installing the MSSQL, you will be asked to input a password for the sa user, use that password]";
     516  my $database = "[Create a database in MSSQL and use it here]";
     517 
     518  # Create the unique name for the table
     519  # We do not want to change the database for the current running index
     520  # Therefore we use timestamp and collection short name to create an unqiue name
     521  my $cur_time = time();
     522  my $unique_key = $mssql_collection_name . "_" . $cur_time;
     523  $mssql_data_table_name = "data_" . $unique_key;
     524  $mssql_document_metadata_table_name = "document_metadata_" . $unique_key;
     525  print STDERR "MSSQL: Creating unique table name. Unique ID:[" . $unique_key . "]\n";
     526     
     527  # Store these information into the infodbfile 
     528  open(FH, ">" . $infodb_file_path);
     529  print FH "mss-host\t" . $host . "\n";
     530  print FH "username\t" . $user . "\n";
     531  print FH "password\t" . $pwd . "\n";
     532  print FH "database\t" . $database . "\n";
     533  print FH "tableid\t" . $unique_key . "\n";
     534  close(FH);
     535  print STDERR "MSSQL: Saving db info into :[" . $infodb_file_path . "]\n";
     536
     537  # Make the connection
     538  my $dsn = "Provider=SQLNCLI;Server=$host;Database=$database";
     539  my $infodb_handle = DBI->connect("dbi:ADO:$dsn", $user, $pwd, { RaiseError => 1, AutoCommit => 1}) || return undef;
     540  print STDERR "MSSQL: Connect to MS SQL database. DSN:[" . $dsn . "]\n";
     541
     542  # Make sure the data table has been created.
     543  my $data_table_checker_array = dbgetarray($infodb_handle, "SELECT name FROM sysobjects WHERE name = '" . $mssql_data_table_name . "' AND OBJECTPROPERTY(id,'IsUserTable') = 1");
     544  if (scalar(@{$data_table_checker_array}) == 0)
     545  {
     546    dbquery($infodb_handle, "CREATE TABLE " . $mssql_data_table_name . " (one_key NVARCHAR(50) UNIQUE, one_value NVARCHAR(MAX))");
     547  }
     548  print STDERR "MSSQL: Making sure the data table(" . $mssql_data_table_name . ") exists\n";
     549   
     550  # Make sure the document_metadata table has been created.
     551  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");
     552  if (scalar(@{$document_metadata_table_checker_array}) == 0)
     553  {
     554    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))");
     555    dbquery($infodb_handle, "CREATE INDEX dmd ON " . $mssql_document_metadata_table_name . "(docOID)");
     556  }
     557  print STDERR "MSSQL: Making sure the document_metadata table(" . $mssql_data_table_name . ") exists.\n";
     558 
     559  return $infodb_handle;
     560}
     561
     562
     563sub close_infodb_write_handle_mssql
     564{
     565  my $infodb_handle = shift(@_);   
     566 
     567  $infodb_handle->disconnect();
     568}
     569
     570
     571sub get_infodb_file_path_mssql
     572{
     573  my $collection_name = shift(@_);
     574  my $infodb_directory_path = shift(@_);
     575
     576  my $infodb_file_extension = ".mssqldbinfo";
     577  my $infodb_file_name = &util::get_dirsep_tail($collection_name) . $infodb_file_extension;
     578 
     579  # This will be used in the open_infodb_write_handle_mssql function
     580  $mssql_collection_name =  $collection_name;
     581
     582  return &util::filename_cat($infodb_directory_path, $infodb_file_name);
     583}
     584
     585
     586sub read_infodb_file_mssql
     587{
     588  my $infodb_file_path = shift(@_);
     589  my $infodb_map = shift(@_);
     590
     591  # !! TO IMPLEMENT
     592}
     593
     594
     595sub write_infodb_entry_mssql
     596{
     597  my $infodb_handle = shift(@_);
     598  my $infodb_key = shift(@_);
     599  my $infodb_map = shift(@_);
     600 
     601  # Add the key -> value mapping into the "data" table
     602  my $infodb_entry_value = "";
     603  foreach my $infodb_value_key (keys(%$infodb_map))
     604  {
     605    foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
     606    {
     607      $infodb_entry_value .= "<$infodb_value_key>" . &Encode::decode_utf8($infodb_value) . "\n";
     608    }
     609  }
     610 
     611  # Prepare the query
     612  my $safe_infodb_key = &mssql_safe($infodb_key);
     613  my $query = "INSERT INTO " . $mssql_data_table_name . " (one_key, one_value) VALUES (N'" . $safe_infodb_key . "', N'" . &mssql_safe($infodb_entry_value) . "')";
     614  dbquery($infodb_handle, $query);
     615 
     616  # If this infodb entry is for a document, add all the interesting document metadata to the
     617  # "document_metadata" table (for use by the dynamic classifiers)
     618  if ($infodb_key !~ /\./ && $infodb_entry_value =~ /\<doctype\>doc\n/)
     619  {
     620    dbquery($infodb_handle, "DELETE FROM " . $mssql_document_metadata_table_name . " WHERE docOID=N'" . $safe_infodb_key . "'");
     621   
     622    foreach my $infodb_value_key (keys(%$infodb_map))
     623    {           
     624      # We're not interested in most of the automatically added document metadata
     625      next if ($infodb_value_key eq "archivedir" ||
     626               $infodb_value_key eq "assocfilepath" ||
     627               $infodb_value_key eq "childtype" ||
     628               $infodb_value_key eq "contains" ||
     629               $infodb_value_key eq "docnum" ||
     630               $infodb_value_key eq "doctype" ||
     631               $infodb_value_key eq "Encoding" ||
     632               $infodb_value_key eq "FileSize" ||
     633               $infodb_value_key eq "hascover" ||
     634               $infodb_value_key eq "hastxt" ||
     635               $infodb_value_key eq "lastmodified" ||
     636               $infodb_value_key eq "metadataset" ||
     637               $infodb_value_key eq "thistype" ||
     638               $infodb_value_key =~ /^metadatafreq\-/ ||
     639               $infodb_value_key =~ /^metadatalist\-/);
     640      foreach my $infodb_value (@{$infodb_map->{$infodb_value_key}})
     641      {
     642        $infodb_handle->{LongReadLen} = 65535; # Added for the encoding issue
     643        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)) . "')";
     644        dbquery($infodb_handle, $query);
     645      }
     646    }
     647  }
     648}
     649
     650
     651sub mssql_safe
     652{
     653  my $value = shift(@_);
     654 
     655  # Escape any single quotes in the value
     656  $value =~ s/\'/\'\'/g;
     657 
     658  return $value;
     659}
     660
     661
     662sub dbquery
     663{
     664  my $infodb_handle = shift(@_);
     665  my $sql_query = shift(@_);
     666
     667  # Execute the SQL statement
     668  my $statement_handle = $infodb_handle->prepare($sql_query);
     669  $statement_handle->execute();
     670  if ($statement_handle->err)
     671  {
     672    print STDERR "Error:" . $statement_handle->errstr . "\n";
     673    return undef;
     674  }
     675 
     676  return $statement_handle;
     677}
     678
     679
     680sub dbgetarray
     681{
     682  my $infodb_handle = shift(@_);
     683  my $sql_query = shift(@_);
     684 
     685  my $statement_handle = dbquery($infodb_handle, $sql_query);
     686  my $return_array = [];
     687 
     688  # Iterate through the results and push them into an array
     689  if (!defined($statement_handle))
     690  {
     691    return [];
     692  }
     693 
     694  while ((my $temp_hash = $statement_handle->fetchrow_hashref()))
     695  {
     696    push(@$return_array, $temp_hash);
     697  }
     698
     699  return $return_array;
    447700}
    448701

  • gsdl/trunk/runtime-src/src/colservr/collectset.cpp

    r16895 r17476  
    4848#endif
    4949
     50#ifdef USE_MSSQL
     51#include "mssqldbclass.h"
     52#endif
     53
     54
    5055collectset::collectset (text_t& gsdlhome, text_t& collecthome)
    5156{
    … …  
    251256  }
    252257#endif
    253 
     258 
     259#ifdef USE_MSSQL
     260  if (infodbtype == "mssql")
     261  {
     262    mssqldbclass *mssql_db_ptr = new mssqldbclass();
     263    db_ptr = mssql_db_ptr;
     264
     265    // add a sql browse filter
     266    sqlbrowsefilterclass *sqlbrowsefilter = new sqlbrowsefilterclass();
     267    sqlbrowsefilter->set_sql_db_ptr(mssql_db_ptr);
     268    cserver->add_filter (sqlbrowsefilter); 
     269  }
     270#endif
     271 
    254272  // Use GDBM if the infodb type is empty or not one of the values above
    255273  if (db_ptr == NULL)

  • gsdl/trunk/runtime-src/src/colservr/win32.mak

    r16577 r17476  
    3636DLLDEBUG = 0
    3737USE_SQLITE = 0
     38USE_MSSQL = 0
    3839
    3940
    … …  
    6061!ENDIF
    6162
     63!IF $(USE_MSSQL)
     64MSSQL_DEFINES = -DUSE_MSSQL
     65!ELSE
     66MSSQL_DEFINES =
     67!ENDIF
     68
    6269
    6370AR = lib
    6471CC = cl
    6572DEFS = -D__WIN32__ -DHAVE_CONFIG_H -DPARADOCNUM -D_LITTLE_ENDIAN -DSHORT_SUFFIX -D_CRT_SECURE_NO_DEPRECATE \
    66         -DXML_STATIC $(SQLITE_DEFINES)
     73        -DXML_STATIC $(SQLITE_DEFINES) $(MSSQL_DEFINES)
    6774# Do NOT add the "recpt" directory here: the colserver should be independent of the receptionist!
    6875INCLUDES = -I"$(GSDL_DIR)" -I"$(COMMON_DIR)\src\lib" \

  • gsdl/trunk/runtime-src/win32.mak

    r16734 r17476  
    3333DLLDEBUG = 0
    3434USE_SQLITE = 0
     35USE_MSSQL = 0
    3536USE_Z3950 = 0
    3637
    … …  
    6162!IF $(USE_SQLITE)
    6263MAKECMD = $(MAKECMD) USE_SQLITE=1
     64!ENDIF
     65!IF $(USE_MSSQL)
     66MAKECMD = $(MAKECMD) USE_MSSQL=1
    6367!ENDIF
    6468!IF $(USE_Z3950)

  • gsdl/trunk/win32.mak

    r16925 r17476  
    3131DLLDEBUG = 0
    3232USE_SQLITE = 0
     33USE_MSSQL = 0
    3334USE_Z3950 = 0
    3435
    … …  
    5455!IF $(USE_SQLITE)
    5556MAKECMD = $(MAKECMD) USE_SQLITE=1
     57!ENDIF
     58!IF $(USE_MSSQL)
     59MAKECMD = $(MAKECMD) USE_MSSQL=1
    5660!ENDIF
    5761!IF $(USE_Z3950)
Note: See TracChangeset for help on using the changeset viewer.