public class JoinsExamples
extends java.lang.Object
This provides an example of some typical usage scenarios for joined tables in Patterns. It runs through the following operations:
Some terminology:
For our example we have 3 tables, one parent and two child tables.
Parent Table: contact_person
Fields:
Child Table: address
Fields:
Child Table: phone
Fields:
| Modifier and Type | Class and Description |
|---|---|
static class |
JoinsExamples.RecJoinSet
This represents a joined set of records.
|
| Modifier and Type | Method and Description |
|---|---|
static com.netrics.likeit.NetricsTransaction |
BeginWork(com.netrics.likeit.NetricsConMgr con_mgr,
java.lang.String layout)
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".
|
public static void LogMsg(java.lang.String msg)
msg - the message string to be outputpublic static JoinsExamples.RecJoinSet GetNextRecord()
public static com.netrics.likeit.NetricsTransaction BeginWork(com.netrics.likeit.NetricsConMgr con_mgr,
java.lang.String layout)
throws com.netrics.likeit.NetricsException,
java.io.IOException
This adds error checking and logging, plus setting the layout, to the basic beginWork of the connection manager.
con_mgr - connection manager for creating new transaction.layout - layout to use, may be null for none or default layout.java.io.IOException - if there is a communications error.com.netrics.likeit.NetricsException - if the transaction could not be created.public static com.netrics.likeit.NetricsTransaction CommitWork(com.netrics.likeit.NetricsTransaction tx)
throws com.netrics.likeit.NetricsException,
java.io.IOException
This adds error checking and logging to the basic commitWork of the NetricsTransaction class.
tx - the transaction to be committed.java.io.IOException - if there is a communications error.com.netrics.likeit.NetricsException - if the transaction could not be commited.public static void TableSetUp(com.netrics.likeit.NetricsTransaction tx)
throws java.lang.Exception
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:
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.java.lang.Exception - if an error occurs.public static void JoinRecAdd(com.netrics.likeit.NetricsTransaction tx,
JoinsExamples.RecJoinSet new_rec)
throws java.lang.Exception
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.
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.java.lang.Exception - if an error occurs.public static void JoinRecDel(com.netrics.likeit.NetricsTransaction tx,
JoinsExamples.RecJoinSet cur_rec)
throws java.lang.Exception
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.
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.java.lang.Exception - if an error occurs.public static JoinsExamples.RecJoinSet JoinRecUpd(com.netrics.likeit.NetricsTransaction tx, JoinsExamples.RecJoinSet cur_rec, JoinsExamples.RecJoinSet new_rec) throws java.lang.Exception
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.
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.java.lang.Exception - if an error occurs.public static void ShowSearchResponse(com.netrics.likeit.NetricsSearchResponse sresp)
sresp - the complete search result object.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
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.
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.
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.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if tehre was an error searching for the person.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
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.
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.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if there was an error searching for the contact.public static com.netrics.likeit.NetricsSearchResponse FindContact(com.netrics.likeit.NetricsServerInterface si,
java.lang.String description)
throws com.netrics.likeit.NetricsException,
java.io.IOException
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.
si - Interface object used to send query to server.description - The description string to search for.
It must be non-null.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if there was an error searching for the contact.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
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.
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.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if there was an error searching for the phone number.public static void CheckpointTables(com.netrics.likeit.NetricsServerInterface si)
throws com.netrics.likeit.NetricsException,
java.io.IOException
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:
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.
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.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if there was an error check-pointing the tables.public static void RestoreTables(com.netrics.likeit.NetricsServerInterface si)
throws com.netrics.likeit.NetricsException,
java.io.IOException
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.
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.java.io.IOException - if there was a communications error.com.netrics.likeit.NetricsException - if there was an error restoring the tables.public static com.netrics.likeit.NetricsRecord[] UnassignPhoneRecs(com.netrics.likeit.NetricsRecord[] phone_recs)
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.
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.public static void main(java.lang.String[] args)
throws java.lang.Exception
java -jar JoinsExamples.jar [-h host] [-p port] [-l layout] [-d data-dir] [-D]
-h host - Patterns Server URL or IP address.
-p port - Port Patterns server is listening on.
-l layout - name of layout for tables.
-d data-dir - directory containing data files.
-D if present run interfaces in debug mode.
args - command line arguments.java.lang.Exception - if an error occurred.