[Building Sakai] OSP matrix slow loading

[Maurer, Christopher Wayne]

If you want to delete a user's matrix, I'd suggest trying to do it in java.  You could write some custom code to call the MatrixManager's deleteMatrix method, or, IU developed a web service…
https://source.sakaiproject.org/svn/msub/iu.edu/oncourse/trunk/webservices/axis/src/webapp/OspMatrix.jws

Again, I'd recommend making sure that you really want to delete that user's matrix (and matrix data) before doing this.

Chris

From: Noah Botimer <botimer at umich.edu<mailto:botimer at umich.edu>>
Date: Monday, May 20, 2013 10:20 AM
To: prabhu <prabhu142003 at gmail.com<mailto:prabhu142003 at gmail.com>>
Cc: sakai-dev <sakai-dev at collab.sakaiproject.org<mailto:sakai-dev at collab.sakaiproject.org>>
Subject: Re: [Building Sakai] OSP matrix slow loading

These explain it pretty well:

http://stackoverflow.com/questions/4688599/how-can-i-add-on-delete-constraint-on-the-table

http://dev.mysql.com/doc/refman/5.5/en/alter-table.html


For example, osp_matrix_cell has one of these constraints:

show create table osp_matrix_cell;

| osp_matrix_cell | CREATE TABLE `osp_matrix_cell` (
  `id` varchar(36) NOT NULL,
  `matrix_id` varchar(36) NOT NULL,
  `wizard_page_id` varchar(36) DEFAULT NULL,
  `scaffolding_cell_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `wizard_page_id` (`wizard_page_id`),
  KEY `FK8C1D366DCD99D2B1` (`scaffolding_cell_id`),
  KEY `FK8C1D366DE4E7E6D3` (`wizard_page_id`),
  KEY `FK8C1D366D2D955C` (`matrix_id`),
  CONSTRAINT `FK8C1D366D2D955C` FOREIGN KEY (`matrix_id`) REFERENCES `osp_matrix` (`id`),
  CONSTRAINT `FK8C1D366DCD99D2B1` FOREIGN KEY (`scaffolding_cell_id`) REFERENCES `osp_scaffolding_cell` (`id`),
  CONSTRAINT `FK8C1D366DE4E7E6D3` FOREIGN KEY (`wizard_page_id`) REFERENCES `osp_wizard_page` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

Here, the matrix_id key is the one to change.

You will have to study the table structure quite well to know which to adjust. The relationships between the scaffolding, scaffolding cells, wizard pages, matrices, and matrix cells are rather complex. Do be careful -- keep a backup and test on a copy if you can.

Thanks,
-Noah

On May 20, 2013, at 9:19 AM, prabhu wrote:

Can you guide be how to change to Cascade?


On Mon, May 20, 2013 at 6:47 PM, Noah Botimer <botimer at umich.edu<mailto:botimer at umich.edu>> wrote:
Prabhu,

There is no way in the UI to delete individual users' matrices. If you have real data for that matrix (definition, scaffolding), you will have to delete rows in the database. The easiest way to do that would be to update the foreign key constraints to cascade deletes. There are a bunch of them, but they will be easy to find (try to delete and if it fails, update the constraint).

Because these are not usually set to cascade, they should be set back to restrict when done.

You could also do queries to identify the user cells, etc., and delete them by hand rather than using the cascade. This would certainly help you familiarize more with the tables.

Note that you will have to be quite careful to not delete data you want to keep. I would consider other options like profiling to see if you can identify the performance issue -- whether it is from missing users, retrieving extra data, excessive looping, or something else.

Thanks,
-Noah

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/sakai-dev/attachments/20130520/ffad4b7a/attachment.html