VBGene - ODBC Visual Basic Genealogical Sample

The DBGene sample application uses a classic Prolog application idea to illustrate how the Amzi! Logic Server can be used to provide the services of Prolog in conventional application environments. Given a genealogical database of information about people, the application lets you pose queries about various relationships, such as sister, uncle, grandparent, and ancestor. This implementation was built with three tools.

Visual Basic was used for the GUI interface (other GUI tools such as Delphi or C++ could have been used instead),
Amzi! Prolog was used to define the rules for family relationships, and
Access was used for the database of people (any database with an ODBC driver could be used).

The glue for the application is the Amzi! Logic Server. The application:

Uses the Logic Server API to get easy access from Visual to the Prolog logic base, and
Uses the Logic Server ODBC extension to lets the Prolog rules reason directly over the database tables.

Using DBGene - How to use the sample, and how to put in your own family database.

Application Architecture - Details on the implementation, including annotated source code.

www.amzi.com - Additional demos, articles, freeware, evaluation copies and information about Amzi! Prolog + Logic Server.

We welcome any and all comments about this demo. Contact us at www.amzi.com

Building the Sample

Open the dbgene project in the prolog subdirectory and rebuild it. This compiles dbgene.pro and links it with the aodbc and misc libraries.
Open the odbcgene project in Visual Basic and build odbcgene.exe.

Running the Sample

To run it you need:

Install ODBC 2.0 or later.
Make sure 'aodbc.lsx' is somewhere in your path (it is in amzi\bin).
An amzi.cfg file containing the line 'lsxload=aodbc'.
Create adata source named 'gene' in your 32-bit ODBC administrator (in the Control Panel) that uses the gene.mdb database included with this demo.

Using DBGene

To run DBGene, you need:

ODBC (2.0 or later) installed
AODBC.LSX somewhere in your path (it is in amzi\bin)
a data source named 'gene' defined in your 32-bit ODBC administrator (in the control panel) that uses the gene.mdb database included with this demo.

DBGene automatically loads the ODBC database, 'gene' and the logic base, 'dbgene'. The lefthand listbox contains the names of the people in the gene database. The middle listbox contains a list of the relations the dbgene logic base knows about. If you select a relation and an individual, then press the 'query' button, the names of the people who satisfy that relation appear in the right hand box.

If you have the tools to modify the database, which was created using Microsoft Access, you can fill it with individuals from your own family tree. Or you can replace the gene database with an ODBC database of your own choosing.

DBGENE Application Architecture

The DBGene application is composed of three components

the Visual Basic user interface,
the Amzi! Prolog logic base, and
the Access database of people.

The database contains two tables, one defining people, with information such as a unique ID number, name, date-of-birth, and parents, and the other defining marriages, with the ID numbers of the individuals in each marriage.

The logic base contains rules that define relationships based on the database. These are rules that express what it means to be a sibling, uncle, ancestor, etc. At the base of each of these rules are Prolog predicates that map directly to the database, using the Logic Server ODBC extension.

The user interface contains list boxes that present the pertinent information from the logic base. That information is obtained from a Visual Basic program that uses logic base services.

Database

The database used in this example is an Access database. If you have Access, you can edit it directly to reflect your own family tree. If you have a different database with an ODBC driver, you can create tables like these for your own family and assign the ODBC database name 'gene' to your database.

There are two tables in the database defined as follows.

Person

*pid number (key)
*surname text[40] (indexed)
*midname....text[40]
*name text[40]
*gender text[1] m/f
*mother number (indexed) (a pid)
*father number (indexed) (a pid)
dob text[10] (date yyyy-mm-dd)
dod text[10] (date yyyy-mm-dd)
birthplace text[50]
nation text[40]
note text[255]

Marriage

*husband number (indexed) (a pid)
*wife number (indexed) (a pid)
married text[10] (date yyyy-mm-dd)
divorced text[10] (date yyyy-mm-dd)
note text[255]

* - Fields that are used in this demonstration. Other fields are ignored.

The Sample People

The people in the sample database have decided to propagate maternal family names through the middle name, just as paternal family names are often propagated by the last name. That is, the children of a marriage take the middle name of the mother and the last name of the father. That way the daughters carry on the maternal family name and the sons carry on the paternal family names, and you can get a better sense by looking at the names who might be related to whom.

Individuals with no middle name are at the top of the tree, also indicated by parents with PID numbers of 0.

Logic Base

This is the Prolog code used to define the genealogical logic base. To use it the host language must first open the ODBC connection by calling the db_open/1 predicate.

The code consists of these primary sections:

A list of relations, used by the host language to display the choices to the user.
Database definitions used to build SQL queries from Prolog queries.
The rules defining the primitive relationships that are based directly on the representation of people in the database. These rules make heavy use of the ODBC query predicate.
The higher-level rules for more complex relationships, all defined using the primitive rules described above, so they are not affected by changes to the representation of a person.
The query interface designed for use from a host language program. Other versions of the program have different interfaces.

User Interface

The Visual Basic user interface consists of a simple dialog box and some functions that call the Prolog services portion of the application, and populate the list boxes of the dialog.

The code included is:

dbgenevb.frm - the logic-base is initialized and closed with the form loading and unloading. Three routines fill the three list boxes via calls to Prolog for the logic server API.

''''''''''''''''''''''''''''''''''''''''''''''''
' Display All the Relations for the Highlighted
' Person and Relationship
'
Private Sub DisplayRelations()
    Dim Person As String, Relationship As String
    Dim StrVal As String
    Dim rc As Integer, tf As Integer
    Dim Term As Long
    Dim PID As Integer
    Dim PersonID, Firstname, Midname, Surname As String

    ' First clear the list of related persons
    RelatedPersonsList.Clear

    ' If we don't have both a person and a relationship highlighted
    ' then exit this routine
    If PersonList.ListIndex < 0 Or RelationshipList.ListIndex < 0 Then
        Exit Sub
    End If

    ' Get the highlighted person and relationship
    Person = PersonList.List(PersonList.ListIndex)
    Relationship = RelationshipList.List(RelationshipList.ListIndex)

    ' Build the Prolog command "query(<relationship>(X,<person_id>), PID, Surname, Midname, Name)"
    PersonID = Mid$(Person, 1, InStr(Person, ":") - 1)
    tf = CallStrLS(Term, "query(" + Relationship + "(X, " + PersonID + "), PID, Surname, Midname, Firstname)")

    If tf = False Then
        RelatedPersonsList.AddItem "No " + Relationship + " for " + Right$(Person, Len(Person) - (InStr(Person, ":") + 1))
    End If

    ' Loop getting all the people who have that relationship
    ' and add them to the related persons list
    While (tf)
        PID = GetIntArgLS(Term, 2)
        Surname = GetStrArgLS(Term, 3)
        Midname = GetStrArgLS(Term, 4)
        Firstname = GetStrArgLS(Term, 5)
        StrVal = Format$(PID, "###") + ": " + Firstname + " " + Midname + " " + Surname
        RelatedPersonsList.AddItem StrVal
        tf = RedoLS()
    Wend
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Close Down Amzi Prolog When the Exit Button is Pressed
'
Private Sub Exit_Click()
    Dim tf As Integer
    Dim Term As Long
    
    ' Close the ODBC database
    tf = ExecStrLS(Term, "db_close")
    If tf <> True Then
        MsgBox "Unable to close ODBC database 'gene'"
    End If
    
    ' Close the Prolog runtime, releasing all resources
    CloseLS
    End
End Sub

''''''''''''''''''''''''''''''''''''''''''''
' Main Form Handler Starts up Amzi! Prolog
'
Private Sub Form_Load()
    Dim rc As Integer, tf As Integer
    Dim Term As Long
    Dim xplname As String

    ' Setup our xpl and help file pathnames
    xplname = App.Path + "\DBGENE.XPL"
    App.HelpFile = App.Path + "\DBGENEVB.HLP"

    ' Initialize the runtime and load DBGENE.XPL, which contains
    ' all the rules and expertise for this application
    ' Also load the ODBC LSX
    InitLS (xplname)
    AddLSX ("ls4odbc")
    LoadLS (xplname)

    ' Turn off backslash processing in strings and atoms (so pathnames work)
    tf = CallStrLS(Term, "set_mode(string_esc, off)")
    If tf <> True Then
        MsgBox "Unable to turn of string escape processing"
    End If

    ' Open the ODBC database, gene
    tf = ExecStrLS(Term, "db_open('gene')")
    If tf <> True Then
        MsgBox "Unable to open the ODBC database 'gene'; check your ODBC setup in the control panel"
    End If

    ' Display the Persons and Relations lists
    tf = Persons()
    tf = Relations()

End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Close Down Amzi Prolog When the Main Form Closes
'
Private Sub Form_Unload(CANCEL As Integer)
    Dim tf As Integer
    Dim Term As Long
    
    ' Close the ODBC database
    tf = ExecStrLS(Term, "db_close")
    If tf <> True Then
        MsgBox "Unable to close ODBC database 'gene'"
    End If

    ' Close the Prolog runtime, releasing all resources
    Call CloseLS
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Display Help When Button is Pressed
'
Private Sub Help_Click()
    Dim retval As Integer
    retval = ShellExecute(0, ByVal "open", ByVal "dbgenevb.htm", "", "", 1)
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Display All the Family Members in the First List Box
'
Private Function Persons() As Integer
    Dim rc As Integer, tf As Integer
    Dim Term As Long
    Dim PID As Integer
    Dim StrVal, Firstname, Midname, Surname As String

    ' Clear the list first
    PersonList.Clear
    
    ' Issue the Prolog query:  person(X)
    tf = CallStrLS(Term, "fullname(Pid, Surname, Midname, Firstname)")

    ' Check if there are any people
    If (tf <> True) Then
        Persons = 0
        Exit Function
    End If
    
    ' Loop through all the family members, adding them to the list
    While (tf = True)
        PID = GetIntArgLS(Term, 1)
        Surname = GetStrArgLS(Term, 2)
        Midname = GetStrArgLS(Term, 3)
        Firstname = GetStrArgLS(Term, 4)
        StrVal = Format$(PID, "###") + ": " + Firstname + " " + Midname + " " + Surname
        PersonList.AddItem StrVal
        tf = RedoLS()
    Wend
    
    Persons = 1

End Function

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Display Relations When the Query Button is Pressed
'
Private Sub Query_Click()
    Query.Enabled = False
    Call DisplayRelations
    Query.Enabled = True
End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Display All the Possible Relationships in the Second List Box
'
Private Function Relations() As Integer
    Dim rc As Integer, tf As Integer
    Dim Term As Long, TList As Long
    Dim StrVal As String

    ' Clear the list first
    RelationshipList.Clear
    
    ' Issue the Prolog query:  relations(X)
    tf = CallStrLS(Term, "relations(X)")
    
    ' Check if there are any relationships
    If (tf <> True) Then
        Relations = 0
        Return
    End If
    
    ' Loop through all the relationships adding them to the list
    Call GetArgLS(Term, 1, bTERM, TList)
    Do
        rc = PopListLS(TList, bSTR, StrVal)
        If (rc = 0) Then
            RelationshipList.AddItem StrVal
        End If
    Loop While (rc = 0)
    
    Relations = 1

End Function