Create interface for modifying a part's information

[jjones646]

There is currently no way to modify information once it is committed to the database. When adding a part, the current PHP code checks for new information and updated quantities, but lacks major abilities for any further modifications.

This will most likely have to wait for authentication, but without an interface to edit part information (part number, attributes, barcodes, etc.), the inventory system probably won't last too long once we're no longer around.

I have had to edit some production database information (2 or 3 different times), and it is very tricky fixing that stuff through the phpMyAdmin interface. Having a streamlined interface for this would be far easier.

[ryanstrat]

This has kinda been done via the add parts interface. But the implementation is buggy with regards to attributes, since it will require some temporary tables to properly implement.

[jjones646]

Yes, information can be modified through the add parts interface, but I have implemented quite a bit of validation checks when a part is submitted. Most of the end results worked out well, but there are a few quirks that are there now that I did not intend for.

I'll outline the steps that are taken when a part is submitted. It will be good to have for reference during this and will help me remember how I approached this when I did it a few months ago.

Server-side Operations for Add Part Interface:

• Basic HTTP authentication takes place for a POST to /submit/part.
• Decodes JSON content sent from client.
• A new object of class Part is created from the client's content.
  • The object's part_num variable is set directly from the given part_num.
  • The class then checks if the given part number already exists in the database.
    • If the part already exists, the object's members are filled in from database queries.
    • If the part does not exist, the object's members are left unfilled at this point.
  • The location is checked against the location from the database. If the submitted location is different from what is in the database, the object's location member is set from the given input after converting to upper case characters.
  • The description is checked against the description from the database. If the submitted description is different from what is in the database AND the submitted description is not "", the object's description member is set from the given input after converting to lower case characters.
  • The name is checked against the name from the database. If the submitted name is different from what is in the database, the object's name member is set from the given input directly.
  • The category is checked against the category from the database. If the submitted category is different from what is in the database, the object's category is set from the given input after converting to lower case characters.
  • The datasheet is checked against the datasheet from the database. If the submitted datasheet is different from what is in the database, the object's datasheet is set from the given datasheet directly.
  • Each attribute is checked against the database's data.
    • If an attribute has an identical name AND value to an entry already in the database for that part_id, it is discarded from being added.
  • Each bag is checked against the database's data.
    • If a bag has an identical barcode AND quantity to an entry already in the database for that part_id, it is discarded from being added.
• The newly created object is passed through validation checks.
  • The barcode is validated by a regular expression checks AND the database is checked to ensure the given barcode does not already exist.
  • The location is validated by a regular expression - nothing more at the moment.
  • The category is validated through a pre-defined array containing valid categories.
  • The datasheet is validated by a regular expression for a valid URL.
• At this point, the class's constructor is complete and the newly created object can call other class methods.
• The database is then opened to start tracking changes.
• Information for the database's 'parts' table is submitted to the database.
  • ( If the part is not in the database OR the part was determined to need updating ) AND ( the part produced no validation errors ), the part data is added/updated in the database.
  • The results are logged in the log file; A database error will abort the current running PHP (thus, not committing any database changes - an error message is also sent back to the client when this happens).
• Information for the database's 'barcode_lookup' table is submitted to the database
  • ( If there are new bags to add ) AND ( the part produced no validation errors ), the bag data is added/updated in the database.
  • The results are logged in the log file; A database error will abort the current running PHP.
• Information for the database's 'attributes' table is submitted to the database.
  • ( If there are new attributes ) AND ( the part produced no validation errors ), the attribute data is added/updated in the database.
  • The results are logged in the log file; A database error will abort the current running PHP.
• The database changes are then committed.
• The status code of the changes is then sent back to the client, where it is used for the popup alert.
• The database connection is closed

This brings up a few notes after typing things out:

• A part's name, location, category, and datasheet can be updated for a part through the add parts interface.
• A part's part_num can not be changed (for typos).
• Changing an attribute's value and submitting will update the attribute's value.
• Changing an attribute's value and modifying the name of the attribute results in a newly created attribute pair for the part. The original attribute remains untouched.
• The same thing happens for the barcode_lookup table (There have been a few cases where the barcode scanner has returned the wrong number that resulted in a need to change a bag's barcode number).

[ryanstrat]

Editing parts

Lets say, for example, that we build a new page for editing parts. you access it by clicking an edit button on each of the result cards. The edit interface would have to keep track of the part_ID, which is not typically handled at all client side. We could either create a new API endpoint or extend the existing one to have a different behavior if a part_ID is specified in the JSON object passed to the server.

Changing Attributes

If we want to properly update attributes, we need to take the updates set of attributes, put them into a temporary table, intersect the table with attributes. Then remove the NOT of the intersection of temp and attributes on attributes. This will leave the attributes table with only old attributes that exist in the new set. Then we find the new ones by performing the NOT of the intersection on the temp table and adding them to attributes.

[jjones646]

I like the idea of adding a new API endpoint regarding the part_ID. There's potentially other areas that could take advantage of a part_ID endpoint that does not necessarily relate to modifying database information.

In regard to changing attributes, I think you just described the exact steps needed. Do you know of any other ways that it can be done? I don't believe there is actually a way to do it outside of SQL since the database is the only all-knowing-entity of the attribute/bag insert IDs. A PHP implementation would require an extensive algorithm that is just overkill here.