Is there a way to only soft delete uploaded files?
The PHP events "postUpload" and "preUpload" don't seem to work for that. Any ideas on how to do this and how to detect whether or not a file will be deleted on the server are welcome.
I think I have a way how to do this "manually" but I wonder whether there is a better way to do this.
My idea is to do the following:
on "validatedEdit": Check whether the submission in $values does not contain one or more files that are still in the files table with custom SQL. If there are files that are no longer submitted: Take these files and insert them into the soft-deleted-files table so that they are still there. Then let Editor make the hard deletes of the "rescued" files in the "regular" files table.
The fields are actually soft deleted by default - i.e. the file and their database reference are left in place. It is only if you use the dbClean option to clean up orphaned files will they actually be deleted from the database.
I don't really understand I'm afraid - there is no reference to the file that was deleted in the files table (or whatever the table name you are using is). Which is just as good as marking it as deleted: true, since there is no way to access it, is it not? If that isn't suitable, then you could use dbClean to make any such files as deleted.
Of course, this assumes you aren't using on delete cascade in your database. If you are, then yes, it would delete the child referenced row. You'd need to remove that in the db if you don't want that to happen.
If I delete a file with Editor the link table entry is being deleted. But I just want it to be soft deleted. If the link table is deleted the file is orphaned. That is not a soft delete because the file is effectively gone since there is no way to retrieve it again.
It took me a couple of hours to develop a work around for this. But I haven't done the front end changes yet. Will post this asap.
About the work around for Editor PHP:
on validatedEdit: I compare the files submission from the client with the files read from the database to create the array of deleted files. This array is written to a session variable. Subsequently Editor deletes the link table entries to be deleted so that the files are orphaned.
on writeEdit: I loop through the array of deleted files, copy the files before they get deleted in orphaned files clean up and reinsert the file table and the link table.
afterwards: My clean up routine deletes orphaned files and file table entries
Here is the PHP code for this:
->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
//get all the submitted file ids into a simple array
$clientFiles = [];
if ( $values['file-many-count'] > 0 ) {
$clientFiles = array_column($values['file'], 'id');
sort($clientFiles);
}
//read all file_ids of the contract
$statement = ('SELECT file_id FROM ctr_has_file
WHERE ctr_id = :ctr_id');
$result = $db->raw()
->bind(':ctr_id', $id)
->exec($statement);
$row = $result->fetchAll(PDO::FETCH_ASSOC);
$dbFiles = [];
if ( (bool)$row ) {
$dbFiles = array_column($row, 'file_id');
sort($dbFiles);
}
//get files in db but not in client submission
//array_diff returns the values from the first array that
//are not contained in the second
//if the result is empty nothing was deleted!
$deletedFiles = array_diff( $dbFiles, $clientFiles );
$_SESSION['deletedFiles'] = [];
if ( $deletedFiles != [] && $deletedFiles != null ) {
$_SESSION['deletedFiles'] = $deletedFiles;
}
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
foreach ( $_SESSION['deletedFiles'] as $delId ) {
//insert an empty record into the file table to obtain an id
$res = $db->insert( 'file', array (
'about' => "Z"
), array ( 'id' ) );
$fileId = $res->insertId();
//insert link table between ctr and file
$res = $db->insert( 'ctr_has_file', array (
'ctr_id' => $id,
'file_id' => $fileId
) );
//read further fields of the file to be deleted
$statement = ('SELECT `name`, `system_path`
FROM `file`
WHERE id = :id');
$result = $db->raw()
->bind(':id', $delId)
->exec($statement);
$tbd = $result->fetch(PDO::FETCH_ASSOC);
//save the file type with period first, e.g. .xlsx or .pdf etc.
$fileType = substr($tbd["name"], strrpos($tbd["name"], '.'));
//set fields to update the file table and to write the file
$webPath = '/lgfuploads/contract_management/' . $fileId . $fileType;
$systemPath = $_SERVER['DOCUMENT_ROOT'] . $webPath;
//copy the file to be deleted to the new system path
copy($tbd["system_path"], $systemPath);
//finally update the file record with the new data
$db->raw()
->bind( ':softDeleted', 1 )
->bind( ':name', $tbd["name"] )
->bind( ':size', filesize($systemPath) )
->bind( ':webPath', $webPath )
->bind( ':systemPath', $systemPath )
->bind( ':creatorId', $_SESSION['id'] )
->bind( ':fileId', $fileId )
->exec( 'UPDATE `file`
SET `soft_deleted` = :softDeleted,
`name` = :name,
`size` = :size,
`web_path` = :webPath,
`system_path` = :systemPath,
`creator_id` = :creatorId
WHERE `id` = :fileId' );
//update potential pdf_has_conversion records to keep the link
//between non-machine readable pdfs and their OCR-converted copies
$db->raw()
->bind( ':fileId', $fileId )
->bind( ':delId', $delId )
->exec( 'UPDATE `pdf_has_conversion`
SET `upload_pdf_id` = :fileId
WHERE `upload_pdf_id` = :delId' );
$db->raw()
->bind( ':fileId', $fileId )
->bind( ':delId', $delId )
->exec( 'UPDATE `pdf_has_conversion`
SET `conv_pdf_id` = :fileId
WHERE `conv_pdf_id` = :delId' );
}
$_SESSION['deletedFiles'] = [];
} )
I have my documentation in a regular data table column and the (soft) deleted documentation (e.g. older versions of existing documents or simply deleted stuff) in a child row.
And the details about the solution:
PHP code is simpler:
Initially I did all the additional processing on "validatedEdit". That didn't work because Editor deleted my new table entries as well. Hence I pulled it apart and did the database inserts on "writeEdit" using a session variable to save the deleted file ids.
Now it is simpler: Same logic on "validatedEdit": I save the deleted file ids.
Then on "writeEdit" I just re-insert the deleted file ids into the link table and update the file table to be soft_deleted. That is still a work around because you can't tell Editor not to delete the link table when you delete a file at the front end.
->on( 'validatedEdit', function ( $editor, $id, $values ) use ( $db ) {
//get all the submitted file ids into a simple array
$clientFiles = [];
if ( $values['file-many-count'] > 0 ) {
$clientFiles = array_column($values['file'], 'id');
sort($clientFiles);
}
//read all file_ids of the contract
$statement = ('SELECT file_id FROM ctr_has_file
WHERE ctr_id = :ctr_id');
$result = $db->raw()
->bind(':ctr_id', $id)
->exec($statement);
$row = $result->fetchAll(PDO::FETCH_ASSOC);
$dbFiles = [];
if ( (bool)$row ) {
$dbFiles = array_column($row, 'file_id');
sort($dbFiles);
}
//get files in db but not in client submission
//array_diff returns the values from the first array that
//are not contained in the second
//if the result is empty nothing was deleted!
$deletedFiles = array_diff( $dbFiles, $clientFiles );
$_SESSION['deletedFiles'] = [];
if ( $deletedFiles != [] && $deletedFiles != null ) {
$_SESSION['deletedFiles'] = $deletedFiles;
}
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
// we reinsert the deleted link table record and update the file table
// entry to be soft_deleted
foreach ( $_SESSION['deletedFiles'] as $delId ) {
//re-insert link table between ctr and file
$res = $db->insert( 'ctr_has_file', array (
'ctr_id' => $id,
'file_id' => $delId
) );
// update the file record to be soft_deleted
$db->raw()
->bind( ':softDeleted', 1 )
->bind( ':creatorId', $_SESSION['id'] )
->bind( ':fileId', $delId )
->exec( 'UPDATE `file`
SET `soft_deleted` = :softDeleted,
`creator_id` = :creatorId
WHERE `id` = :fileId' );
}
$_SESSION['deletedFiles'] = [];
} )
My audit proof solution is live now by the way:
- Nothing ever gets deleted: Neither table records nor documents (using the work around above that reinserts the link table entries on "write edit")
- Full change and document history in my log dialogues with color coded reporting showing the changes in table entries and documents.
@allan, thanks for showing me how to build the soft delete button.
For those who care, here is the code for the soft delete and recover buttons.
Replies
I think I have a way how to do this "manually" but I wonder whether there is a better way to do this.
My idea is to do the following:
on "validatedEdit": Check whether the submission in $values does not contain one or more files that are still in the files table with custom SQL. If there are files that are no longer submitted: Take these files and insert them into the soft-deleted-files table so that they are still there. Then let Editor make the hard deletes of the "rescued" files in the "regular" files table.
The fields are actually soft deleted by default - i.e. the file and their database reference are left in place. It is only if you use the dbClean option to clean up orphaned files will they actually be deleted from the database.
Allan
I know that but I want to soft delete the file reference as well, not only the file itself.
I don't really understand I'm afraid - there is no reference to the file that was deleted in the
filestable (or whatever the table name you are using is). Which is just as good as marking it asdeleted: true, since there is no way to access it, is it not? If that isn't suitable, then you could usedbCleanto make any such files as deleted.Of course, this assumes you aren't using
on delete cascadein your database. If you are, then yes, it would delete the child referenced row. You'd need to remove that in the db if you don't want that to happen.Allan
If I delete a file with Editor the link table entry is being deleted. But I just want it to be soft deleted. If the link table is deleted the file is orphaned. That is not a soft delete because the file is effectively gone since there is no way to retrieve it again.
It took me a couple of hours to develop a work around for this. But I haven't done the front end changes yet. Will post this asap.
About the work around for Editor PHP:
on validatedEdit: I compare the files submission from the client with the files read from the database to create the array of deleted files. This array is written to a session variable. Subsequently Editor deletes the link table entries to be deleted so that the files are orphaned.
on writeEdit: I loop through the array of deleted files, copy the files before they get deleted in orphaned files clean up and reinsert the file table and the link table.
afterwards: My clean up routine deletes orphaned files and file table entries
Here is the PHP code for this:
The solution is ready by now.
I have my documentation in a regular data table column and the (soft) deleted documentation (e.g. older versions of existing documents or simply deleted stuff) in a child row.
And the details about the solution:
PHP code is simpler:
Initially I did all the additional processing on "validatedEdit". That didn't work because Editor deleted my new table entries as well. Hence I pulled it apart and did the database inserts on "writeEdit" using a session variable to save the deleted file ids.
Now it is simpler: Same logic on "validatedEdit": I save the deleted file ids.
Then on "writeEdit" I just re-insert the deleted file ids into the link table and update the file table to be soft_deleted. That is still a work around because you can't tell Editor not to delete the link table when you delete a file at the front end.
My audit proof solution is live now by the way:
- Nothing ever gets deleted: Neither table records nor documents (using the work around above that reinserts the link table entries on "write edit")
- Full change and document history in my log dialogues with color coded reporting showing the changes in table entries and documents.
@allan, thanks for showing me how to build the soft delete button.
For those who care, here is the code for the soft delete and recover buttons.
Excellent - many thanks for sharing this with us!
Allan