Orphan Entries

[apmuthu]

After several iterations of creating fast forms and other dev work on nuBuilder we tend to remove tables, forms, etc and then do an Update. After such Updates, many orphan objects without associated (possibly deleted) forms remain. These orphan objects are removed with:

Code: Select all

DELETE a.* FROM `zzzzsys_object` a  LEFT JOIN `zzzzsys_form` b 
    ON `sob_all_zzzzsys_form_id` = `zzzzsys_form_id`
WHERE b.`zzzzsys_form_id` IS NULL;

There may be orphan records in other tables as well - experienced users may provide other clean up sqls.

Such cleanup constructs can be incorporated into the Update code itself.

[apmuthu]

Is this safe?

Code: Select all

DELETE a.* FROM `zzzzsys_browse` a LEFT JOIN `zzzzsys_form` b
    ON `sbr_zzzzsys_form_id` = `zzzzsys_form_id`
WHERE b.`zzzzsys_form_id` IS NULL;

[kev1n]

Does this help?

https://github.com/smalos/nuBuilder4-Co ... /README.md

[apmuthu]

@kev1n: Excellent. Very comprehensive indeed.

Can this make it into the stable and dev codebases?

[kev1n]

There's already a nuDeleteForm() function in nudata.php. However, I did not compare it with my SQL statements though.

[apmuthu]

The said function in nudata.php at lines 740 to 766 in the stable repo is:

Code: Select all

[function nuDeleteForm($f){
	
	$s		= "DELETE FROM zzzzsys_browse WHERE sbr_zzzzsys_form_id = ? ";
	$t		= nuRunQuery($s, [$f]);
	$s		= "DELETE FROM zzzzsys_tab WHERE syt_zzzzsys_form_id = ? ";
	$t		= nuRunQuery($s, [$f]);
	$s		= "DELETE FROM zzzzsys_php WHERE zzzzsys_php_id LIKE CONCAT(?, '_') ";
	$t		= nuRunQuery($s, [$f]);
	$s		= "DELETE FROM zzzzsys_object WHERE sob_all_type = 'run' AND sob_run_zzzzsys_form_id = ? ";
	$t		= nuRunQuery($s, [$f]);
	$s		= "SELECT * FROM zzzzsys_object WHERE sob_all_zzzzsys_form_id = ? ";
	$t		= nuRunQuery($s);

	while($r = db_fetch_object($t)){
		
		$i	= $r->zzzzsys_object;
		$s	= "DELETE FROM zzzzsys_event WHERE sev_zzzzsys_object_id = ? ";
		$t	= nuRunQuery($s, [$i]);
		$s	= "DELETE FROM zzzzsys_php WHERE zzzzsys_php_id  LIKE CONCAT(?, '_')";
		$t	= nuRunQuery($s, [$i]);

	}

	$s		= "DELETE FROM zzzzsys_object WHERE sob_all_type = 'run' AND sob_run_zzzzsys_form_id = ? ";
	$t		= nuRunQuery($s, [$f]);
	
}

This pretty much deletes all specific form_id in:
1. browse, tab, php and object (type = run)
2. cycles through all other objects for the form_id and deletes the event and php for each such object
3. Wonder why it tries to delete object (type = run) again
4. No where does it delete the specific form per se.

Is there a button somewhere with a safety alert to do this kind of deletion?

[kev1n]

It seems to me that the 2nd "run" deletion is redundant.

Code: Select all

nuDeleteForm('#RECORD_ID#');

is called in AD of a form. So normally all associated data should be deleted as well. The form is deleted by another query, need to investigate.