How to Query Heterogeneous Databases

20. How to Query Heterogeneous Databases

freeWAIS-sf is built to index only single table databases.
SFgate allows to query more tables at the same time if:

All the tables contain records having exactly the same fields
or
Fieldnames from one table can be mapped into the fieldnames of another table.

This second possiblity is what is called Heterogeneous Databases
For more information, please select this link.

Here there is a LIVE example of an heterogeneous database.
It involves two different tables:

Phonebook
Services

Some of the fields are common to both tables, while others are specific of each table.

Select one or both of these databases:

People
Services

Fields Group 1

Fields Group 2

Here you can inspect .fmt files of the phonebook and services databases.
The source code of this form is the following:

<FORM METHOD=POST ACTION="/htbin/SFgate">
<h3>Select one or both of these databases:</h3>

<INPUT NAME="database" TYPE="checkbox" VALUE="phone_attribute" CHECKED>
<B>People</B><BR>
<INPUT NAME="database" TYPE="checkbox" VALUE="services_attribute" CHECKED> <B>Services</B><P> 

<CENTER>
  <TABLE BORDER WIDTH=70%>
       <TR><TH> </TH><TH>
                 <TABLE BORDER WIDTH=100%>
                        <TR><TH COLSPAN=3 align=left>Fields Group 1</TH></TR>
                             <TR><TD><SELECT NAME="fieldsel_1_description">
                                             <OPTION SELECTED>Surname
                                             <OPTION>Name
                                             <OPTION>Department
                                             <OPTION>Service
                                             <OPTION>Responsible
                                             </SELECT></TD>
                               <TD><INPUT TYPE="text" NAME="fieldsel_1_content" VALUE="salviati" SIZE=40></TD>
 
                              <TD><SELECT NAME="fieldsel_1_tie">
                                           <OPTION>and
                                           <OPTION SELECTED>or
                                   </SELECT></TD></TR>

                                 <TR><TD><SELECT NAME="fieldsel_2_description">
                                            <OPTION>Surname
                                            <OPTION>Name
                                            <OPTION>Department
                                            <OPTION SELECTED>Service
                                            <OPTION>Responsible
                                          </SELECT></TD>

                               <TD><INPUT TYPE="text" NAME="fieldsel_2_content" VALUE="informatica" SIZE=40></TD></TR>
                              </TABLE>

                  <TR><TD><SELECT NAME="group_2_tie">
                                           <OPTION>and
                                           <OPTION SELECTED>or
                           </SELECT></TD></TR>
                    <TR><TH> </TH><TH>

                 <TABLE BORDER WIDTH=100%>
                        <TR><TH COLSPAN=3 align=left>Fields Group 2</TH></TR>
                             <TR><TD><SELECT NAME="fieldsel_3_description">
                                             <OPTION>Surname
                                             <OPTION>Name
                                             <OPTION>Department
                                             <OPTION SELECTED>Service
                                             <OPTION>Responsible
                                             </SELECT></TD>
                               <TD><INPUT TYPE="text" NAME="fieldsel_3_content" SIZE=40></TD>
 
                              <TD><SELECT NAME="fieldsel_3_tie">
                                           <OPTION>and
                                           <OPTION>or
                                   </SELECT></TD></TR>

                                 <TR><TD><SELECT NAME="fieldsel_4_description">
                                            <OPTION>Surname
                                            <OPTION>Name
                                            <OPTION>Department
                                            <OPTION>Service
                                            <OPTION SELECTED>Responsible
                                          </SELECT></TD>

                               <TD><INPUT TYPE="text" NAME="fieldsel_4_content" SIZE=40></TD></TR>
                              </TABLE>

               </TH></TR>
    </TABLE>
</CENTER>
<P>

<INPUT TYPE="hidden" NAME="fieldsel_name_surname" VALUE="Surname">
<INPUT TYPE="hidden" NAME="fieldsel_name_name" VALUE="Name">
<INPUT TYPE="hidden" NAME="fieldsel_name_department" VALUE="Department">
<INPUT TYPE="hidden" NAME="fieldsel_name_service" VALUE="Service">
<INPUT TYPE="hidden" NAME="fieldsel_name_responsible" VALUE="Responsible">

<INPUT TYPE="hidden" NAME="group_1" VALUE="fieldsel_1, fieldsel_2">
<INPUT TYPE="hidden" NAME="group_2" VALUE="fieldsel_3, fieldsel_4">

<CENTER>
<TABLE>
<TR><TH><INPUT TYPE="submit" VALUE="Start Search">
</TH><TH> </TH><TH>
<INPUT TYPE="reset"  VALUE="Reset Query">
</TR></TABLE>
</CENTER>
<P>

<INPUT TYPE="hidden" NAME="tieinternal" VALUE="and">
<INPUT TYPE="hidden" NAME="application" VALUE="phone">
<INPUT TYPE="hidden" NAME="directget" VALUE="1">
<INPUT TYPE="hidden" NAME="convert" VALUE="Table">
<INPUT TYPE="hidden" NAME="attributes" VALUE="1">
<INPUT TYPE="hidden" NAME="lattice" VALUE="phone_lattice">
</FORM>

To query heterogeneous databases you need to:

Inspect the indexed fields of the databases (tables) you want to query at the same time. In the case of the example reported above they are:

phone.fde		services.fde
surname:	Cognome	service:	Servizio
name:	Nome	responsible:	Responsabile
department:	Dipartimento	department:	Dipartimento

Create a lattice, i.e a form definition file that maps fields from different files to common ones. In the case of the example reported above the lattice file, named phone_lattice, is this:
```
TOP
  keywords
    surname
    service
  name
  department
  responsible
```

Create an attribute file for each database (table). In the case of the example reported above the attribute files, called phone_attribute and services_attributes have the following content:

phone_attribute

$server = 'local';
$port   = '210';
$name   = '/usr/local/wais-sources/phonebook/phone';

$attributes = {
    'surname:text'      => 'surname',
    'name:text'         => 'name',
    'department:text'   => 'department'
    };

services_attribute

$server  = 'local';
$port    = '210';
$name    = '/usr/local/wais-sources/phonebook/services';

$attributes = {
    'service:text'       => 'service',
    'responsible:text'   => 'responsible',
    'department:text'    => 'department'
    };

Inside the HTML form, redirect queries onto these attribute files and setup a tag named attributes to tell SFgate to use these files:

<INPUT NAME="database" TYPE="checkbox" VALUE="phone_attribute" CHECKED>
<B>People</B><BR>
<INPUT NAME="database" TYPE="checkbox" VALUE="services_attribute" CHECKED> <B>Services</B><P> 
<INPUT TYPE="hidden" NAME="attributes" VALUE="1">

© 1996-2003 BioPD - University of Padova (Italy) - Author: Leopoldo Saggin - Last version: January 28, 2003
Best efforts were made to provide correct information, however this document may contain technical inaccuracies and/or typographical errors.
The author declares that this material is provided "as is" without any warranty even in the implied warranty of merchantability or fitness for a particular purpose.
All trademarks cited inside this document are property of their respective owners