• Home

  • Custom Ecommerce
  • Application Development
  • Database Consulting
  • Cloud Hosting
  • Systems Integration
  • Legacy Business Systems
  • Security & Compliance
  • GIS

  • Expertise

  • About Us
  • Our Team
  • Clients
  • Blog
  • Careers

  • CasePointer

  • VisionPort

  • Contact
  • Our Blog

    Ongoing observations by End Point Dev people

    Interchange table hacking

    Jeff Boes

    By Jeff Boes
    March 11, 2014

    Interchange has a powerful but terribly obscure table administration tool called the Table Editor. You can create, update, and delete rows, and even upload whole spreadsheets of data, but the Table Editor isn’t the most flexible thing in the world, so sometimes it just flat-out refuses to do what you want.

    So you trick it.

    A client wanted to upload data to a table that had a single-column primary key (serial), but also had a unique three-column key that was only used in the upload process (because the uploaded data was intended to replace rows with identical three-column combinations). Example:

    In the table:

    code: 243
    field1: AAA
    field2: BBB
    field3: CCC
    data-fields: ...

    In the spreadsheet:

    field1  field2  field3  data-fields...
     AAA     BBB     CCC     ...

    In the database definition for this table, I had to add a secondary key definition for Interchange’s use:

    Database  my_table  COMPOSITE_KEY  field1 field2 field3

    in addition to the original key:

    Database  my_table  KEY  code

    Here’s the problem this presents: when you add a COMPOSITE_KEY to a table, the table editor refuses to show per-row checkboxes that allow you to delete rows. I thought I might have to write a custom admin page to carry this off, but then I had an inspiration—​the REAL_NAME attribute of the Database directive:

    Database  my_table_edit my_table_edit.txt __SQLDSN__
    Database  my_table_edit REAL_NAME my_table
    Database  my_table_edit KEY code
    Database  my_table_edit  PREFER_NULL code
    Database  my_table_edit  AUTO_SEQUENCE 

    This chunk of config-code tells Interchange that a second table can be accessed in the database, which is in fact the same table as the first (not a view, not a copy, but the actual table), but it doesn’t have the COMPOSITE_KEY. When Interchange is restarted with this new definition in place, the Table Editor will show this second table with the familiar per-row checkboxes, even as it refuses to show them for the original table.

    Phew. I dodged a bullet with that, as I didn’t want to have to write a special page just to mimic the Table Editor.