Squidhead

Effects of various database features on Squidhead

Figure 1. Foreign key relationship.

If a foreign key relationship is defined between two tables the following will occur:

Stored Procedures
- Procs for table with foreign key in relationship will be created named “usp_[tableName]_select_with_fk” and “usp_[tableName]_list_with_fk” will be created
- Proc for table with primary key in relationship will be created named “usp_[tableName]_list_foreign_key_labels” which lists the primary key and the second column in the table for use in the UI layer. Also a proc will be created named “usp_[tableName]_list_by_foreign_key_field for selecting all items from related field where they match the primary key of the primary key table.

Figure 2. Example of form custom tag showing foreign keys as a select box.

Business Objects
- Read method for object will return results from the “usp_[tableName]_select_with_fk” procedure if the “withLinks” option is selected
- * Methods named add_[foriegnTable]_by_[foreignkeycolumn] are added.
Custom Tags
- Form for item will present a select field instead of a text input box for foreign keyed field.
UI Layer

Figure 3. Example of additional components added to UI.
- Read method will call “withLinks” to display the item with the foreign key labels instead of the foreign key.
- * Read method will display a list of all related items in the foreign table
- * Read method will display a form that will allow one to create record in related tables.
- List method will call “usp_[tableName]_list_with_fk” through the gateway

Figure 4. Example of many to many relationship as setup for use in Squidhead.

If a foreign key relationships are setup such that they define a many to many relationship through a linking table the following will occur:

Proc for table will be created named “usp_[tableName]_select_by_unique_join” which will return the record that has the primary key values of both linked tables.

Business Objects
- * Business objects for each linked table will have an add and remove method for the other table which will create relationships for them in the liking table.
UI

* Read method will list records in linked table through the linking table.
* Read method displays form that allows for created related records in the linked table through the linking table.

In order for this to happen:

The linking table needs to have the primary key of each other table set as a foreign key
The foreign keys need to have the same name
The table needs to be named tableToTable.

When a unique constraint is set for a column the following occurs:

* The init method read in a record based on its unique record by setting the Keyfield and keyValue arguments to the appropriate item

* Ui will allow you to link records by just passing in the unique column, rather than the whole value

* - Only occurs in fkCrazy template.