[Deploying Sakai] [Building Sakai] rest all 'My Workspaces'

[Adams, David]

VT recently did a wipe of all existing user workspaces with something
similar to Matthew's script (although we just deleted from the various
tables with "where site_id like '~%' and site_id != '~admin'" instead of
using a temp table to store the list) for about 40,000 sites, and we
were happy with the results.

A couple of further items from our experience with doing this on Oracle
(YMMV on MySQL):

We also wiped out all the realm information for these sites, as part of
what we wanted to do was to fix permissions as well as site structure.
The sakai_realm_rl_fn table is so big, though, that we found it was
faster to do the deletes in blocks based on first unique character of
the realm_id (eg '/site/~0%' etc), and to do commits at each step of the
way to avoid running out of Oracle undo space. Of course, that makes
things somewhat unsafe (no way to rollback) but that's between you and
your DBAs.

One more thing we found was that several foreign keys were missing
which, if present, sped up the process immensely, namely the following
(with obviously localized names):

create index VT_SAKAI_SITE_PROPERTY_SITE on SAKAI_SITE_PROPERTY (
SITE_ID ASC );
create index VT_SAKAI_SITE_PAGE_PROP_PAGE on SAKAI_SITE_PAGE_PROPERTY (
PAGE_ID ASC );
create index VT_SAKAI_SITE_TOOL_PROP_TOOL on SAKAI_SITE_TOOL_PROPERTY (
TOOL_ID ASC );
create index VT_SAKAI_SITE_PAGE_PROP_SITE on SAKAI_SITE_PAGE_PROPERTY (
SITE_ID ASC );
create index VT_SAKAI_SITE_TOOL_PROP_SITE on SAKAI_SITE_TOOL_PROPERTY (
SITE_ID ASC );
create index VT_SAKAI_SITE_USER_SITE on SAKAI_SITE_USER ( SITE_ID ASC );

I don't know enough about how the auto.ddl/hibernate stuff works to know
if the lack of these indices was a local problem or not--I assume so. It
would be nice to have a definitive DDL file and/or validation script to
catch these kinds of things, as I'm sure our
upgraded-since-Sakai-2.0-database has a ton of them.

-dave

> -----Original Message-----
> From: sakai-dev-bounces at collab.sakaiproject.org [mailto:sakai-dev-
> bounces at collab.sakaiproject.org] On Behalf Of Matthew Buckett
> Sent: Monday, July 06, 2009 3:58 PM
> To: Steve Swinsburg
> Cc: Sakai Developers
> Subject: Re: [Building Sakai] rest all 'My Workspaces'
> 
> 2009/7/6 Steve Swinsburg <s.swinsburg at lancaster.ac.uk>:
> > Hi Jon,
> > If you delete all My Workspaces their resources will remain intact
:)
> The
> > sites will be re-created when they next login with the changes
picked
> up
> > from the !user.template.
> > For doing it en-masse, you could use the web services and iterate
> over every
> > userId.
> 
> If your ok writing web service client I'd use that as it's less likely
> to leave your database in a inconsistent state, however if you can't
> easily do that then this may help. It some  SQL (for MySQL) we
> recently ran to remove everyone's MyWorkspace, so that they would all
> get the latest template.
> 
> Attached is the SQL. This doesn't delete anything outside the site
> service (eg authz).
> 
> --
>   Matthew Buckett