Add a Custom Table
Previous: Modify the Spec Header and Adding Parameters
Geocode values are stored within the latitude and longitude columns of the AddressCoordinates table. Our example will require an additional custom USR_ADDRESSCOORDINATETIMEZONE table to be created to store one time zone record for one AddressCoordinates record. The custom USR_ADDRESSCOORDINATETIMEZONE table will have a one to one relationship with AddressCoordinates and will store a foreign key value to the TIMEZONEENTRY table. Our global change code will utilize a web service to determine the time zone name using the Geocode stored within the AddressCoordinate table, retrieve the appropriate TIMEZONEENTRY.ID value for the given time zone name and store the TIMEZONEENTRY.ID as a foreign key value within our custom table's TIMEZONEENTRYID column.
Take some time to review the entity relationship diagram and notes.
Figure: AddressCoordinates (Geocodes) table and the relationship to the custom USR_ADDRESSCOORDINATETIMEZONE table
Tip: To familiarize yourself with the data model, see Constituent Biographical Subject Area ERD, and to learn more about creating custom tables via the SDK, see Infinity Data Model Definition..
Step 1 - Add the custom table to hold the time zone value
Tip: The source code is included with this example and includes a Visual Studio 2010 solution and server side assembly project containing the XML specs/catalog items and vb class file. Grab the source from the repo in GitHub.
Select and right click the Blackbaud.CustomFx.Address.Catalog project created earlier. Select Add\New Item.. (CTRL+ SHIFT + A) to open the Add New Item screen. Select the Blackbaud AppFx Catalog from the list on the left hand side of the dialog screen followed by selecting the Table Spec catalog item. Name the file USR_ADDRESSCOORDINATETIMEZONE.Table.xml.
Tip: For more details, see Creating a New TableSpec.
Figure: Add the catalog item to the Visual Studio project
Step 2 - Complete the TableSpec element
Provide a name that is guaranteed to be unique and safe from future upgrades by appending "(Custom)" or some other identifier like to the end of the Name attribute value. The catalog item template will provide a fresh GUID for the ID attribute. The Name attribute provides a friendly name for the table within the system and creates a new Record Type. Provide a Description, Author, and the Tablename (which is the database table name). Provide the database table name of "ADDRESSCOORDINATES" for the PrimaryKeyAsForeignKeyTablename attribute. This indicates the primary key will also be a foreign key and specifies the name of the foreign table. This will establish a 1 to 1 relationship between the USR_ADDRESSCOORDINATETIMEZONE table and ADDRESSCOORDINATE table. The foreign key will have a cascade delete set to true, therefore, when a row is deleted from the ADDRESSCOORDINATE table any corresponding row within USR_ADDRESSCOORDINATETIMEZONE will also be deleted.
Tip: Don't forget about the PrimaryKeyAsForeignKeyTablename attribute! For more details, see TableSpec Element Attributes.
Figure: The top portion of our TableSpec
Step 3 - Add a Foreign Key to the TIMEZONEENTRY table
Within the Fields element, use a ForeignKeyField element to add a foreign key to our table that points to the TIMEZONEENTRY table. The Name attribute value must end with "ID." The value within the ForeignKeyField field will not be required. Many time zone entry values can exist within this table. We will elect to raise an error from the database if a corresponding row within the TIMEZONEENTRY table is deleted.
Figure: Add the foreign key field
Tip: For more details, see Fields and Foreign Key Fields.
Step 4 - Add a EnumField to capture the response of the Time Zone API call
The EnumField element should have a Name attribute value of "RESPONSESTATUSCODE" and will have the following values and translations.
Figure: Add the EnumField
Tip: For more details, see Enumeration Fields.
Step 5 - Add a TextField to provide a detailed description of the Time Zone API response
Step 6 - Add a non-clustered index to support the TIMEZONEENTRYID foreign key field
Figure: Add the Index for the foreign key field
Tip: For more details, see Indexes.
Step 7 - Load the TableSpec
Review the completed TableSpec below and load into the database via LoadSpec.
Completed TableSpec
<TableSpec
xmlns="bb_appfx_table"
xmlns:common="bb_appfx_commontypes"
ID="1f27dabf-1ecf-4585-8e2d-e7044c50e7ce"
Name="ADDRESS COORDINATE TIMEZONE (Custom)"
Description="Used to store one time zone record for one AddressCoordinates record.
The custom USR_ADDRESSCOORDINATETIMEZONE table will have a one to one relationship
with AddressCoordinates and will store a foreign key value to the TIMEZONEENTRY table.
See GlobalChange definition with display name of Address Coordinate TimeZone (Custom)"
Author="Technical Training"
Tablename="USR_ADDRESSCOORDINATETIMEZONE"
PrimaryKeyAsForeignKeyTablename="ADDRESSCOORDINATES"
WarnOnMissingIndexes="true"
>
<!-- define fields on the table -->
<Fields>
<ForeignKeyField Name="TIMEZONEENTRYID"
ForeignTable="TIMEZONEENTRY"
Required="false"
Cardinality="ManyToOne"
OnDelete="RaiseError"
Description="FK value to the TIMEZONEENTRY.ID value for the given TIMEZONEENTRY.NAME column"/>
<EnumField Name="RESPONSESTATUSCODE"
Description="The Google Time Zone API provides a simple interface
to request the time zone for a location on the earth.
The time zone web service API response status."
DefaultValue="1">
<EnumValues>
<EnumValue ID="1" Translation="OK"/>
<EnumValue ID="2" Translation="Invalid API Request"/>
<EnumValue ID="3" Translation="Over API Query Limit"/>
<EnumValue ID="4" Translation="API Request Denied"/>
<EnumValue ID="5" Translation="Unknown Error"/>
<EnumValue ID="6" Translation="Zero Results"/>
<EnumValue ID="7" Translation="General Error"/>
<EnumValue ID="8" Translation="TimeZoneEntry Foreign Key Not Found in DB"/>
</EnumValues>
</EnumField>
<!--OK - The request was successful.
INVALID_REQUEST - The request was malformed
OVER_QUERY_LIMIT - The requestor has exceeded quota.
REQUEST_DENIED - The API did not complete the request. Confirm that the request was sent over http instead of https.
UNKNOWN_ERROR - indicates an unknown error.
ZERO_RESULTS - indicates that no time zone data could be found for the specified position or time.
Confirm that the request is for a location on land, and not over water.-->
<TextField Name="RESPONSESTATUSDESC"
Length="300"
Description="The time zone web service API response status description"/>
</Fields>
<!-- define any indexes on the table -->
<Indexes>
<Index>
<IndexFields>
<IndexField Name="TIMEZONEENTRYID" Direction="ASC"/>
</IndexFields>
</Index>
</Indexes>
</TableSpec>