EPSA crop saves its data in table epsacrop_files. File ID (fid) is key and 'coords' is data.
So far, so good.

But the EPSA crop data gets saved in an array at array element key = file ID.
In other words, if your image file ID is 1894, then, the actual EPSA crop data gets preceded by 1893 times 'null, '.

Imagine what a waste of database space that would be if you have thousands of files...

$coord = $coords[$fid][$preset];
in epsacrop_crop_image() proves this construction.

The solution probably lies within epsacrop.js which calls 'crop/ajax/put/' ...
But all the other module files will need to be adjusted accordingly.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

davidwhthomas’s picture

Status: Active » Needs review
FileSize
4.32 KB

I noticed the same issue occurring sporadically, large null padded js arrays being created, and stored in db.

It's related to how the "presets" variable is treated in javascript - as an array or as an object.

e.g var presets[1234] = {}

creates an array with 1234 empty elements.

Generally, as per the code it should be treated as an object.

I've attached a small patch that ensures the variable is initialized as an object in cases where it might be treated as an array, if empty.

Attached.

Thanks for the neat module.

yvmarques’s picture

Status: Needs review » Fixed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

jsst’s picture

This patch removes the bogus 'null' values from the preset object before saving it back to the database. Fixes from #2 are also included.

jsst’s picture

Both #1 and #4 did not fix the issue completely.

Attached patch fixes this in the ajax handling, not client-side. Previous patches should not be applied. I ended up creating an update task that converts all arrays in epsacrop_files.coords to objects (that saved gigabytes of useless 'null, null, null'!).

Toraih’s picture

If you have thousands of cropped images like us, you probably want to cleanup the epsacrop-table from null-values.


/* add a link to admin-config-menu */
/* (adjust 'basics_' to your module name) */
function basics_menu() {
  $items = array();
  $items['admin/config/clean_epsacrop_tbl'] = array(
      'title' => 'Cleanup the Epsacrop-Table (removes NULLs)',
      'page callback' => '_basics_clean_epsacrop_tbl',
      'type' => MENU_CALLBACK,
      'access arguments' => array('access administration pages'),
  );
  return $items;
}

/**
 * Cleanup Epsacrop-table
 * 
 * removes NULL-data
 *
 * @return string amount cleaned records as text
 */
function _basics_clean_epsacrop_tbl() {
	$count = 0;
	$result = db_query("SELECT * FROM {epsacrop_files}");
	if ($result) {
		while ($row = $result->fetchAssoc()) {
			$flag = false;
			$coords = unserialize($row['coords']);
			$coords = json_decode($coords);
			foreach ($coords as $k=>$coord) {
				if ($coord==null) {unset($coords[$k]); $flag = true;}
			}
			if ($flag) {
				$coords = json_encode($coords);
				$data = serialize($coords);
				db_update('epsacrop_files')->fields(array(
						'coords' => $data
					))->condition('fid', $row['fid'], '=')
					->execute();
				$count++;
			}
		}
	}
	return 'done... cleaned '.$count.' records.';
}

or adding this to a cron job running weekly or monthly is a fine way to keep your table clean automatically...

PieterDC’s picture

It would be cool if that functionality went in an update function so it automatically gets executed when upgrading.

itamair’s picture

Issue summary: View changes

We are experiencing huge drupal db dimension, caused by Epsa Crop table.
Does the #5 patch solve the matter properly? As it claims ...
Why is not yet been pushed in the stable version of this module?
Any update on this would be really appreciated ... thanks

itamair’s picture

Status: Closed (fixed) » Patch (to be ported)
yvmarques’s picture

Status: Patch (to be ported) » Fixed

  • yvmarques committed 6ff364d on 7.x-2.x
    Issue #1823940 by jsst, davidwhthomas, Toraih: Simplify data storage to...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.