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:

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

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

1. Phonebook
2. Services

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> 

1. 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

2. 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
   

3. 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'
       };
   

4. 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">