Class JoinsExamples

  • public class JoinsExamples
extends java.lang.Object
    Example of typical usage for Patterns Joined Tables.

    This provides an example of some typical usage scenarios for joined tables in Patterns. It runs through the following operations:

    • Create a set of joined tables.
    • Load the tables with an initial set of data.
    • Checkpoint the tables.
    • Perform assorted searches on the joined tables.
    • Perform joined record updates.
    • Perform single record updates.
    • Restore a set of joined tables.
    These examples make use of transactions, so this also provides an example of usage scenarios for transactions.

    Some terminology:

    • Parent Table: A table that may have other tables linked to it as child tables. A parent table may have any number of child tables linked to it. A parent table may not be the child of another table. A table must be defined as being a Parent Table when it is created. The property can't be added or removed.
    • Child Table: A table that is linked to a Parent Table as a child of that table. A child table has exactly one Parent Table. The parent table must be specified, and must exist, at the time the child table is created. Once created the parent can't be changed.
    • Standard Table: A table that is neither a parent nor a child table. Before the introduction of joins all tables were standard tables.
    • Parent Record: All records in a parent table are parent records. A parent record may have any number of child records linked to it, including zero. A parent record can't also be a child record.
    • Child Record: A record in a child table that contains a reference to a parent record. It may or may not be linked to a parent record. A child record has at most one parent record. A child record may not be a parent record. The parent record of a child record is always in the parent table of the child table containing the record.
    • Standard Record: This is any record that is not a child record. In other words a record that does not contain a reference to a parent record. A parent record is also a standard record. A child table may contain standard records or child records, a standard table may contain only standard records.
    • Orphan Record: An orphan record is any record in a child table that is not linked to a parent record. All standard records in a child table are orphans by definition. Child records may also be orphans. They can be orphaned at the time they were created, that happens if the referenced parent record doesn't exist, or they can be orphaned by the deleting of their parent record. In the context of a search an Orphan Record is a record that contains data for only one child table. As child records are linked through a parent record it is not possible to return a record from a joined search that contains two or more child records, but no parent record.
    • Joined Search: This is a search that involves one parent table and one or more child tables of that parent table.
    • Joined Record: This is a record returned by a joined search. It is a set containing at most one record from each table specified in the joined search.
    • Full Record: A joined record in which data from the parent and all child tables specified in the search is included.
    • Partial Record: This is a Joined Record that is missing data from one or more or all child tables. A partial record always contains data from the parent record.
    • Joined Set of Tables: This is a parent table and all of its child tables. A standard table can also be considered a joined set of tables consisting only of itself.
    • Joined Set of Records: In this example we use this term to refer to a set of records representing a parent record with its child records. Here we allow this grouping to have no parent record, in which case it represents a group of standard or orphan records.

    For our example we have 3 tables, one parent and two child tables.

    Parent Table: contact_person
    Fields:

    • status one of: "active-level-[1-9]" (lower being preferred), "obsolete", "potential"
    • first
    • last
    • middle
    • company
    • position
    The majority of the entries are "obsolete" or "potential", but the majority of our queries only want active entries, so we place a primary predicate index on the status field to speed these queries.

    Child Table: address
    Fields:

    • status "Active" or "Obsolete"
    • type (e.g. "work", "home")
    • preference number 0 - 9 higher being preferred
    • line1
    • line2
    • city
    • state
    • postal-code
    • company
    Address entries are only created for known contacts, so there should be no orphans. A single contact can have multiple addresses, even multiples of the same type.

    Child Table: phone
    Fields:

    • status (e.g. "assigned", "do-not-call", "open", "out-of-service")
    • type (e.g. "cell", "home", "work")
    • preference number 0 - 9 higher being preferred
    • country-code
    • area-code
    • number
    • extension
    • company
    Most of our phone numbers are unassigned numbers used in cold calling, they are unassociated with a contact person, and thus orphans.

    • Nested Class Summary

      Nested Classes 
      Modifier and Type Class Description
      static class  JoinsExamples.RecJoinSet
      This represents a joined set of records.

    • Method Summary

      All Methods Static Methods Concrete Methods 
      Modifier and Type Method Description
      static com.netrics.likeit.NetricsTransaction BeginWork​(com.netrics.likeit.NetricsConMgr con_mgr)
      Create a transaction.
      static void CheckpointTables​(com.netrics.likeit.NetricsServerInterface si)
      Checkpoint our set of tables.
      static com.netrics.likeit.NetricsTransaction CommitWork​(com.netrics.likeit.NetricsTransaction tx)
      Commit a transaction.
      static com.netrics.likeit.NetricsSearchResponse FindContact​(com.netrics.likeit.NetricsServerInterface si, java.lang.String description)
      Example of a simple query across multiple tables.
      static com.netrics.likeit.NetricsSearchResponse FindNumber​(com.netrics.likeit.NetricsServerInterface si, java.lang.String phone_number)
      Example of query on child table content only.
      static com.netrics.likeit.NetricsSearchResponse FindPerson​(com.netrics.likeit.NetricsServerInterface si, java.lang.String last_name, java.lang.String company, java.lang.String phone_num)
      Example of a single parent search with one child table, returning Full and Partial records.
      static com.netrics.likeit.NetricsSearchResponse GetContactNumbers​(com.netrics.likeit.NetricsServerInterface si, java.lang.String company, java.lang.String address)
      Example of a multi-parent search returning Full Records only with a filtering predicate.
      static JoinsExamples.RecJoinSet GetNextRecord()
      Simulate retrieving a joined set of records.
      static void JoinRecAdd​(com.netrics.likeit.NetricsTransaction tx, JoinsExamples.RecJoinSet new_rec)
      Add a joined set of records.
      static void JoinRecDel​(com.netrics.likeit.NetricsTransaction tx, JoinsExamples.RecJoinSet cur_rec)
      Delete a joined set of records.
      static JoinsExamples.RecJoinSet JoinRecUpd​(com.netrics.likeit.NetricsTransaction tx, JoinsExamples.RecJoinSet cur_rec, JoinsExamples.RecJoinSet new_rec)
      Update a joined set of records.
      static void LogMsg​(java.lang.String msg)
      Log a message with a date stamp to the standard out.
      static void main​(java.lang.String[] args)
      Run the examples.
      static void RestoreTables​(com.netrics.likeit.NetricsServerInterface si)
      Restore our set of tables.
      static void ShowSearchResponse​(com.netrics.likeit.NetricsSearchResponse sresp)
      Output the results of a search.
      static void TableSetUp​(com.netrics.likeit.NetricsTransaction tx)
      Create the initial tables.
      static com.netrics.likeit.NetricsRecord[] UnassignPhoneRecs​(com.netrics.likeit.NetricsRecord[] phone_recs)
      Select and update phone records to be "unassigned".

      • Methods inherited from class java.lang.Object

        equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

    • Method Detail

      • LogMsg

        public static void LogMsg​(java.lang.String msg)
        Log a message with a date stamp to the standard out. We use this to output information messages when running our example.
        Parameters:
        msg - the message string to be output

      • GetNextRecord

        public static JoinsExamples.RecJoinSet GetNextRecord()
        Simulate retrieving a joined set of records. This function simulates retrieving a joined set of records. In a real application this would come from some data feed or a query against a data base, or some other means. Here we just provide two statically coded records.
        Returns:
        a RecJoinSet record.

      • BeginWork

        public static com.netrics.likeit.NetricsTransaction BeginWork​(com.netrics.likeit.NetricsConMgr con_mgr)
                                                       throws com.netrics.likeit.NetricsException,
                                                              java.io.IOException
        Create a transaction.

        This adds error checking and logging to the basic beginWork of the connection manager.

        Parameters:
        con_mgr - connection manager for creating new transaction.
        Returns:
        a new transaction object.
        Throws:
        java.io.IOException - if there is a communications error.
        com.netrics.likeit.NetricsException - if the transaction could not be created.

      • CommitWork

        public static com.netrics.likeit.NetricsTransaction CommitWork​(com.netrics.likeit.NetricsTransaction tx)
                                                        throws com.netrics.likeit.NetricsException,
                                                               java.io.IOException
        Commit a transaction.

        This adds error checking and logging to the basic commitWork of the NetricsTransaction class.

        Parameters:
        tx - the transaction to be committed.
        Returns:
        the transaction that was commited
        Throws:
        java.io.IOException - if there is a communications error.
        com.netrics.likeit.NetricsException - if the transaction could not be commited.

      • TableSetUp

        public static void TableSetUp​(com.netrics.likeit.NetricsTransaction tx)
                       throws java.lang.Exception
        Create the initial tables.

        We want to either create all three tables, or no tables, so we perform all three create operations within the same transaction.

        When creating joined tables there are a number of considerations:

        • The parent table must be created before any of its child tables are created.
        Parameters:
        tx - the transaction to use for these actions. Note we require a transaction as we must perform a number of different operations as a unit.
        Throws:
        java.lang.Exception - if an error occurs.

      • JoinRecAdd

        public static void JoinRecAdd​(com.netrics.likeit.NetricsTransaction tx,
                              JoinsExamples.RecJoinSet new_rec)
                       throws java.lang.Exception
        Add a joined set of records.

        This takes in a joined sets of records representing a new set to be added.

        We want all record adds to be committed or rolled back as a unit, so we perform these adds within a single transaction.

        Parameters:
        tx - the transaction to use for these actions. Note we require a transaction as we must perform a number of different operations as a unit.
        new_rec - the joined set of records to be added.
        Throws:
        java.lang.Exception - if an error occurs.

      • JoinRecDel

        public static void JoinRecDel​(com.netrics.likeit.NetricsTransaction tx,
                              JoinsExamples.RecJoinSet cur_rec)
                       throws java.lang.Exception
        Delete a joined set of records.

        This takes in a joined sets of records representing an existing set to be deleted. Note we need the full set of records as there is no means of finding and deleting all child records of a parent.

        We want all record deletes to be committed or rolled back as a unit, so we perform these deletes within a single transaction.

        Parameters:
        tx - the transaction to use for these actions. Note we require a transaction as we must perform a number of different operations as a unit.
        cur_rec - the current state of the joined set of records to be deleted.
        Throws:
        java.lang.Exception - if an error occurs.

      • JoinRecUpd

        public static JoinsExamples.RecJoinSet JoinRecUpd​(com.netrics.likeit.NetricsTransaction tx,
                                                  JoinsExamples.RecJoinSet cur_rec,
                                                  JoinsExamples.RecJoinSet new_rec)
                                           throws java.lang.Exception
        Update a joined set of records.

        This takes in two joined sets of records, the first representing the current state, the second representing the new state.

        The previous state is needed, and must contain all child records to be updated. Currently there is no means of fetching all child records for a particular parent record, so it is up to the application to provide the list of child records.

        We want all record updates to be committed or rolled back as a unit, so we perform these updates within a single transaction. Note that "updates" may actually involve a mix of adds, deletes and updates.

        Parameters:
        tx - the transaction to use for these actions. Note we require a transaction as we must perform a number of different operations as a unit.
        cur_rec - the current state of the joined set of records to be updated.
        new_rec - the desired new state for the joined set of records.
        Returns:
        the records that were removed.
        Throws:
        java.lang.Exception - if an error occurs.

      • ShowSearchResponse

        public static void ShowSearchResponse​(com.netrics.likeit.NetricsSearchResponse sresp)
        Output the results of a search. This routine displays the results of an ibi Patterns search. In a real application the search results would be processed in some way, in our example we just output some of the basic information to the standard output.
        Parameters:
        sresp - the complete search result object.

      • FindPerson

        public static com.netrics.likeit.NetricsSearchResponse FindPerson​(com.netrics.likeit.NetricsServerInterface si,
                                                                  java.lang.String last_name,
                                                                  java.lang.String company,
                                                                  java.lang.String phone_num)
                                                           throws com.netrics.likeit.NetricsException,
                                                                  java.io.IOException
        Example of a single parent search with one child table, returning Full and Partial records.

        In this example we are looking for a particular person. We are given their first name, company name and phone #, values that are spread across a parent table and one child table.

        A search may be a "single-parent" search, where only the one best combination of child records for each parent record is returned, or a multi-parent search, where all combinations of child records that qualify are returned for each parent record. As we are looking for a particular person we don't want to fill up our results with lower scoring variations on the same person, we want each entry returned to be the one best match for that person. So we use a single parent search.

        A search may also specify a join type. The join type defines what types of joined records are returned.

        • FULL RECORDS ONLY Only Full Records are returned.
        • FULL and PARTIAL RECORDS Full Records and Partial Records are returned, but no orphans. This is probably the most commonly used join type as normally we don't care if a record has missing children as long as it matches well on the data that is there.
        • FULL and ORPHAN RECORDS Full Records and Orphans records are returned, but no Partial Records are returned. This is uncommon.
        • ALL RECORD TYPES Full Records, Partial Records and Orphans are returned.
        Note that in all cases Full Records are returned. There is no option that excludes full records.

        In our example we want the best match even if it does not have phone # info, but we must have the person info, so we request FULL and PARTIAL records only.

        Parameters:
        si - Connection to server to use for queries. Note that queries and other read-only operations are not associated with transactions, it is never necessary to perform a query using a NetricsTransaction.
        last_name - The last (family) name to search for; must be non-null.
        company - The company name to search for; must be non-null.
        phone_num - The phone number (as a string) to search for; must be non-null.
        Returns:
        The search response.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if tehre was an error searching for the person.

      • GetContactNumbers

        public static com.netrics.likeit.NetricsSearchResponse GetContactNumbers​(com.netrics.likeit.NetricsServerInterface si,
                                                                         java.lang.String company,
                                                                         java.lang.String address)
                                                                  throws com.netrics.likeit.NetricsException,
                                                                         java.io.IOException
        Example of a multi-parent search returning Full Records only with a filtering predicate.

        In this example we are looking for all active contact numbers for a particular company at a particular location. We are given a company and and an address value. We want all contact numbers. The query is across the contacts table and the addresses table, the the desired values are in the phone numbers table. So we have a join across three tables. A result without a phone number value would be useless, so we want only full records. We want all numbers for each matching entry, so we use a multi-parent search to return all the different numbers tied to a contact. See FindPerson for an explanation of join types and Single vs. Multi parent searches.

        Parameters:
        si - Interface object used to send query to server.
        company - The company name to search for. It must be non-null.
        address - The company address to search for. It must be non-null.
        Returns:
        The search response.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if there was an error searching for the contact.

      • FindContact

        public static com.netrics.likeit.NetricsSearchResponse FindContact​(com.netrics.likeit.NetricsServerInterface si,
                                                                   java.lang.String description)
                                                            throws com.netrics.likeit.NetricsException,
                                                                   java.io.IOException
        Example of a simple query across multiple tables.

        This example is similar to the FindPerson example except we are given a single description field that may contain name, address and phone numbers. We create a single simple query against all of the related fields across all three tables. Joins allows the field set in a query to span tables.

        In our example we want the best match even if it is missing information from one or more child tables. However an orphan record does us no good as we are looking for the contact person, so like the FindPerson query we request Full and Partial records and single parent.

        Parameters:
        si - Interface object used to send query to server.
        description - The description string to search for. It must be non-null.
        Returns:
        the search response.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if there was an error searching for the contact.

      • FindNumber

        public static com.netrics.likeit.NetricsSearchResponse FindNumber​(com.netrics.likeit.NetricsServerInterface si,
                                                                  java.lang.String phone_number)
                                                           throws com.netrics.likeit.NetricsException,
                                                                  java.io.IOException
        Example of query on child table content only.

        This example is a type of reverse lookup of phone numbers. We are given a phone number and want to find the contact associated with it. The number however may not be associated with a contact at all, it may be an orphan number on our cold calling list. So we want either FULL RECORDS, or ORPHAN RECORDS. A single contact may have multiple closely matching numbers, we want to see all of these, so we use a multi-parent search.

        Parameters:
        si - Interface object used to send query to server.
        phone_number - The phone number, as a string, to search for. It must be non-null.
        Returns:
        the search response.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if there was an error searching for the phone number.

      • CheckpointTables

        public static void CheckpointTables​(com.netrics.likeit.NetricsServerInterface si)
                             throws com.netrics.likeit.NetricsException,
                                    java.io.IOException
        Checkpoint our set of tables.

        This example checkpoints our set of joined tables. As there are cross linkages between parent and child tables joined sets of tables must be checkpointed as a unit to avoid inconsistencies. The server quietly enforces this by always checkpointing all tables in the joined set of tables for each table given. So if a request is made to checkpoint two tables: tableA and tableB, the server will find all tables in the joined set of tables for tableA and add them to the list of tables to checkpoint, and then find all tables in the joined set of tables for tableB and add them to the list (removing duplicate tables). So it is not possible to checkpoint just a parent table or just a child table, all are always checkpointed as a unit.

        Note that there are actually two sets of tables involved:

        • The existing tables in memory.
        • The existing checkpointed tables on disk.
        These are not necessarily the same. If the joined set of tables to be checkpointed partially overlaps a joined set of checkpointed tables on the disk the checkpoint operation fails as this would corrupt the consistency of the checkpointed tables on disk.

        An implication of the above is that all we need to do is specify one of our three joined tables. Using the parent table is most efficient.

        Parameters:
        si - the connection for the commit operation. As we perform only one command we do not enforce the use of a transaction. Note that as NetricsTransaction is an extension of NetricsServerInterface the caller could use a transaction if desired.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if there was an error check-pointing the tables.

      • RestoreTables

        public static void RestoreTables​(com.netrics.likeit.NetricsServerInterface si)
                          throws com.netrics.likeit.NetricsException,
                                 java.io.IOException
        Restore our set of tables.

        This example restores our set of joined tables from a checkpoint. As there are cross linkages between parent and child tables joined sets of tables must be restored as a unit to avoid inconsistencies. Unlike the checkpoint operation the server will enforce this, but does not automatically add all joined tables to the list. If the server did so it could wipe out an in-memory table the user did not expect to get wiped out. To avoid this the server restores only those tables the user explicitly requested. The server validates that the set of tables requested represent a complete join set and rejects the request if they do not. So it is not possible to restore a single child table, or just a parent table.

        The same issues between tables in memory verse checkpointed tables on disk as exist for checkpoint operations exist for restore. The two sets must be consistent in their structure or the restore request fails.

        Parameters:
        si - the connection for the commit operation. As we perform only one command we do not enforce the use of a transaction. Note that as NetricsTransaction is an extension of NetricsServerInterface the caller could use a transaction if desired.
        Throws:
        java.io.IOException - if there was a communications error.
        com.netrics.likeit.NetricsException - if there was an error restoring the tables.

      • UnassignPhoneRecs

        public static com.netrics.likeit.NetricsRecord[] UnassignPhoneRecs​(com.netrics.likeit.NetricsRecord[] phone_recs)
        Select and update phone records to be "unassigned".

        In our example when a contact is deleted we want all of the work numbers reassigned to our pool of numbers as "potential" contact numbers for the company. This function takes a list of phone records, and creates a new list of records representing those that should be "unassigned".

        In general Netrics API objects should be treated as immutable objects. So we create new instances of the records.

        To reassign records as orphans we set the parent key to null.

        Parameters:
        phone_recs - an array of all of the phone records to be unassigned. It is assumed that they have already been deleted from the Patterns table.
        Returns:
        As described above, an array of orphan records to be added back into the Patterns phone table.

      • main

        public static void main​(java.lang.String[] args)
                 throws java.lang.Exception
        Run the examples.

        java -jar JoinsExamples.jar [-h host] [-p port] [-d data-dir] [-D]

        -h host - Patterns Server URL or IP address.
        -p port - Port Patterns server is listening on.
        -d data-dir - directory containing data files.
        -D if present run interfaces in debug mode.

        Parameters:
        args - command line arguments.
        Throws:
        java.lang.Exception - if an error occurred.