I've tried a couple of variations but none of them seems 100% bullet-proof. Here are my current intermediate results:
a) UNIQUE CONSTRAINT on database
I have created a UNIQUE CONSTRAINT on multiple columns in the database. Inserts and updates are beeing denied by the database now.
This works of course and from my point of view this should be definately be done in any case just to keep data integrity.
Create a new unique constraint:
Code: Select all
ALTER TABLE `recordpersons`
ADD UNIQUE `record_id_person_id_role_id` (`record_id`, `person_id`, `role_id`);
Drop an existing unique constraint:
Code: Select all
ALTER TABLE `recordpersons`
DROP INDEX `record_id_person_id_role_id`;
But there is a problem here: On Insert/Update in AppGini the users leave the details-view and get redirected to the table-view with an alert-danger (notification) telling that the record could not be saved. All the input is gone then.
b) BeforeInsert/BeforeUpdate-hook
I can validate in PHP and return FALSE.
Problem here: the same as above.
c) Replace the onclick event handler of the submit button(s)
I can do client side validation and perhaps cancel submitting the data. Problem here: I cannot do server side validation using AJAX because of the "A" in "AJAX". The form will be submitted and does not wait for the AJAX request to be completed. (Using the deprecated "async: false" is not an option for me due to various side effects)
d) AJAX call with server-side validation as a "pre-check"
Im my sample case I have three Lookups (dropdowns). My composite key contains those three fields. On change of any of the dropdowns I POST the selected values via AJAX, perform a database query as server-side check, return the query result as JSON to the client and - depending on the JSON-result - mark the three fields as "warning" or "success".
One thing is much better now: Users get informed about duplicate keys
BEFORE they click save-button.
Here is what (d) looks like:
- 2018-07-28_18-40-52.gif (67.47 KiB) Viewed 17975 times
If the user tries to save the record anyway, the record will
not be stored due to the UNIQUE CONSTRAINT and he will be redirected to table view by appGini.
Of course there is the old and well known problem of concurrent data access in a multiuser environment. The result of the pre-check is correct at the time of the pre-check but perhaps not a second later at the time of the insert-/update-trial. So if user B inserts/updates a record with identical keys
after the pre-check but
before the insert/update, the insert/update will fail.
Summary
1. From my point of view UNIQUE CONSTRAINTS should be created on the database in any case to keep integrity.
2. The pre-check functionality is not 100% perfect but may help users in most of the cases.
I wish users could be redirected to details-view after an insert/update failed and see their entered values there. Please feel free to give me some hints if there is a way without touching the overwritable files.
Hope this helps someone!
Kind Regards,
Jan