Utility Table List and code reuse ideas

[edarsow]

These are "Utility tables" that populate various drop-down and menu select buttons system wide. Ideally, they can be edited using a somewhat unified interface.

The List

• blobtype
• bobsource
• ceactionrequestissuetype
• ceactionrequeststatus
• citationstatus
• citationfilingtype
• contactphonetype
• courtentity (not quite a utility table)
• improvementtype
• linkedobjectrole
• logcategory
• moneypaymenttype
• occperiodtype
• occinspectioncause
• occinspectiondetermination
• propertyusetype
• taxstatus
• textblockcategory
• intensityclass

Table versions

Since they have been built at various times throughout the development of codeNforce, some are more "modern" than others, meaning some include active/inactive flags as boolean type, others are more modern and use a timestamp for deactivation, and include a deactivation userID as well. To the extent deemed necessary, DB patches could be written to bring these tables into a unified form for ease of sharing UI elements.

So perhaps we should unify on field names such as title, description, deactivatedts, createdts, etc.

Remember we don't delete, we deactivate and only select active records

Don't let the user run a DELETE command on a table, just let them write to the deactivatedts or active field

Icons

In the long run, we'd like most of the utility tables to include a FK to the icon table for a jazzy UI; integrating this on utility table normalization would be great.

Code reuse organs

The ability to reuse integration methods is really handy. Eric's approach is to create an enum that contains attributes like table name and primary key of the object. For example, there are lots of objects that hold lists of addresses. We can use the same code for linking and unlinking them by specifying an enum and an interface:

This enum stores things like the linking table name, the primary key field of the linking table, and the PK field name of the target object.

public enum LinkedObjectSchemaEnum {
 CECaseHuman         (   
                            "Code Enf. Case",
                            "public.humancecase", 
                            "CECaseHuman", 
                            "linkid", 
                            "cecase_caseid",
                            null,
                            "humancecase_linkid_seq",
                            LinkedObjectFamilyEnum.HUMAN,
                            true

                        ), 

...
  private final String TARGET_OBJECT_FRIENDLY_NAME;
    private final String LINKING_TABLE_NAME;
    private final String LINK_ROLE_SCHEMA_TYPE_STRING;
    private final String LINKING_TABLE_PK_FIELD;
    private final String TARGET_TABLE_FK_FIELD;
    private final String LINKED_OBJECT_FK_FIELD;
    private final String LINKING_TABLE_SEQ_ID;
    private final LinkedObjectFamilyEnum FAMILY;
    private final boolean ACTIVELINK;

The interface

The interface says: if you are an object that holds an address, then I can ask you for your enum, which I can unpack in the integrator and in the UI for specific writes to the correct table. The inteface also specifies a getter for the objects PK, so combined with the info from the enum, I can make a write to the correct record in the correct utility table.

/**
 * Declares a setter and getter for a List of AddressLinks
 * And LinkedObjectRoles objects for building SQL to work with
 * links in the correct tables.
 * 
 * @author Ellen Bascomb of Apartment 31Y
 */
public interface IFace_addressListHolder {
    public List<MailingAddressLink> getMailingAddressLinkList();
    public void setMailingAddressLinkList(List<MailingAddressLink> ll);
    public LinkedObjectSchemaEnum getLinkedObjectSchemaEnum();
    public int getTargetObjectPK();

}

On integration

We can then code the integration methods to take an instance of the interface, and then we'll build the SQL strategically by asking the object for its ID, and its enum, which builds the correct SQL:

Here's the top bits of programatically building SQL based on the interface called linkMailingAddress on the PropertyIntegrator:

 /**
     * Writes a new record in a table that links a mailing address to a target
     * @param alh
     * @param madLink
     * @return
     * @throws BObStatusException 
     * @throws com.tcvcog.tcvce.domain.IntegrationException 
     */
    public int linkMailingAddress(IFace_addressListHolder alh, MailingAddressLink madLink) throws BObStatusException, IntegrationException{
        if(alh == null 
                || madLink == null 
                || madLink.getLinkedObjectRole() == null 
                || madLink.getLinkedObjectRole().getSchema() == null){
            throw new BObStatusException("Cannot link a mailing address with null link, or role, or schema enum"); 
        }        

        Connection con = getPostgresCon();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int freshID = 0;

        try {

            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO public.");
            sb.append(madLink.getLinkedObjectRole().getSchema().getLinkingTableName());
            sb.append(" (");
            sb.append(madLink.getLinkedObjectRole().getSchema().getTargetTableFKField());
            sb.append(",");
            sb.append(madLink.getLinkedObjectRole().getSchema().getLINKED_OBJECT_FK_FIELD());
            sb.append(", source_sourceid, createdts, createdby_userid, lastupdatedts, lastupdatedby_userid, " );
            sb.append("            deactivatedts, deactivatedby_userid, notes, linkid, linkedobjectrole_lorid," );
            sb.append("            priority)" );
            sb.append("    VALUES (?, ?, ?," );
            sb.append("            now(), ?, now(), ?," );
            sb.append("            NULL, NULL, ?, DEFAULT, ?," );
            sb.append("            ?);");
            stmt = con.prepareStatement(sb.toString());
            System.out.println("PropertyIntegrator.writeLink: " + sb.toString());
            stmt.setInt(1, alh.getTargetObjectPK());
            stmt.setInt(2, madLink.getAddressID());

Objects hold a static member for the enum

So a person holds addresses, so I can ask it for its enum, since it implements our interface that specifies a getter for that enum. If I had to write codeNforce from scratch, I'd build an ORM that uses this pattern system-wide: each table would have its own enum or instance of an enum in a family, and then I'd reuse oodles of code on the integration side.

public  class       Person 
        extends     Human
        implements  IFace_Loggable,
                    IFace_addressListHolder,
                    IFace_ActivatableBOB{

    final static LinkedObjectSchemaEnum PERSON_ADDRESS_LOSE = LinkedObjectSchemaEnum.MailingaddressHuman;
    ...

UI IDeas Dumped

• If you have an enum that specifies all the instances of your interface, then you have a drop at the top of a page and when the selection occurs, then the backing bean goes and loads all of the objects of that Enum type, and displays its.
• An intermediate level of code reuse is to share a access table with title and description and creation date and then when the user clicks on an edit link, a specific dialog customized to that object. You could use the update and insert methods on the BB because they only need to know about the interface.
• Perhaps in a coordinator, it uses instanceof to figure out exactly what class it is if there is specific logic or methods that need to be called.

For example, when working with FieldInspections, I have a method that takes in the interface but I need the underlying case or occ period, so in a coordinator I say:

 if(inspectable instanceof OccPeriodPropertyUnitHeavy){
            rpt.setOccPeriod((OccPeriodPropertyUnitHeavy) inspectable);
        } else if(inspectable instanceof CECasePropertyUnitHeavy){
            rpt.setCeCase((CECasePropertyUnitHeavy) inspectable);
        }

[DuskXua]

So I'm playing around and this seems like the easiest way to do this. Have a Super Class with these variables in it. (and some methods) public class Managed { private int ID; private int iconID; private String title; private LocalDateTime deactivatedts; private final ManagedSchemaEnum MANAGABLE_SCHEMA; }

and use the super as a base to populate the tables. I use the Enum to cache the table names and such. I looked at the interface, and I might have to switch to them. As of right now, I haven't looked at all the classes.

This is my plan for it unless there you have a better idea.

*Changed to use the interface. I was not aware that I could pass objects by their interface.

Since you said each manageable table would have an icon, I was planning on putting that on the manageable table and adding it to each table.

• I have looked a little and so far I haven't found a naming convention for the deactivation ts, so I'm going to assign it as deactivatedts unless told otherwise.
• I am planning to redo the get lists to pull ids and have coordinators? (pretty sure its suppose to be the coordinators but not sure.) make the objs
• Is there a plan for ceactionrequeststatus? it is just a subclass of status. Nothing added on...