Fix custom field creation trigger

[AdamVig]

When custom fields are created, a database trigger automatically generates empty rows for possible values for every single item to have that custom field. The trigger should only create rows for items that are in the categories that the custom field applies to. With #301, custom fields can have multiple categories, so the trigger must be updated with more complex logic for checking categories.

Alternatively, the approach of creating empty rows for custom fields could be replaced with a better method.

• [x] Remove trigger and empty custom field values
• [x] Figure out the best method to replace this trigger with
• [x] Implement the solution

[AdamVig]

The current trigger, which no longer works:

insert into itemCustomFieldValue 
  (barcode, customFieldID)
  -- Get column values from item and new custom field
  select barcode, NEW.customFieldID
  from item
  -- Only create field value rows for items in the same organization
  where item.organizationID = NEW.organizationID
    -- Check that item category matches category of new custom field
    -- If no category provided, check that item matches its own category (noop)
    and item.categoryID = if(NEW.categoryID is not null, NEW.categoryID, item.categoryID)

[AdamVig]

Instead of creating empty rows for each possible custom field value, it would be better to allow creation, deletion, and update of values for custom fields (currently, only update is allowed).

When a custom field value does not exist for an item, it will not be returned.

GET /item/:barcode/custom-field will return the available custom fields for an item, with values (if they are set). GET /item/:barcode/custom-field/:customFieldID will return the value (if set). PUT /item/:barcode/custom-field/:customFieldID with body {value: ''} will set the value. DELETE /item/:barcode/custom-field/:customFieldID will remove the value.

[emroussel]

That would work, but it would create the same problem than we had for rentals and still have for kit models: if one request fails, the others will still pass, and that can create data that is inconsistent.

It would also make the logic for item custom fields more complex on the front end (it would be similar to kit models in that we'll have to keep track of what entity to create, delete and modify, and then send the appropriate request for each).

But it can still be a viable solution for now, and we can do it more as a transaction later on.

[AdamVig]

Building support for "get all custom fields for this item" was more difficult than expected. This is because a custom field with no categories specified applies to all categories, whereas a custom field with at least one categories specified applies to only the specified categories.

The best way I could figure out to do this is a two-part query that is unioned together. The first part gets the custom fields that apply to the item's category, and the second part gets the custom fields that have no category specified.

-- Get custom fields for the item's category
select customField.*, customFieldCategory.*
  from item

  -- First, join with custom field categories to get all custom fields that match the category
  join customFieldCategory on item.categoryID = customFieldCategory.categoryID
  -- Second, get the custom fields that match the category
  join customField on customFieldCategory.customFieldID = customField.customFieldID

  where customField.organizationID = 0
    and item.barcode = 123
-- Add all custom fields for this organization without any categories
-- Note: the columns included in the two `select` statements must match exactly or the `union` will fail
union
select customField.*, customFieldCategory.*
  from customField
  -- Use left join so that custom fields without any categories will be in result
  natural left join customFieldCategory
  -- Only keep custom fields without any categories
  where customFieldCategory.categoryID is NULL
    and customField.organizationID = 0