On Sun, 2010-03-07 at 14:08 +0100, Bruno Friedmann wrote:
> Hi Guys,
>
> I've got here (due to super slow sunbird perf) a question about
> putting old events from a collection to
> another one.
>
> I imagine that's better to do this directly in postgresql as normal
> ical client ask event by event (there's 9'000 to move :-)
>
> I would have done some research on my own yesterday and today, but
> I've crash my car yesterday on ice. And been busy to organize
> fixing, doing insurance things etc ..
>
> So If someone has some shortcuts to help me building a good sql, it
> will be really appreciate.
Hi Bruno,
You need to update caldav_data table (calendar_item will be maintained
with a trigger) with the new path, and the new collection_id.
Here's an example moving non-repeating events, prior to the 1st of
January, from /user1/home/ (collection_id 10 for me) to /user1/archive/
(collection_id 1429 for me):
UPDATE caldav_data
SET dav_name = replace( caldav_data.dav_name, '/user1/home/', '/user1/archive/'),
collection_id = 1429
FROM calendar_item
WHERE caldav_data.dav_id = calendar_item.dav_id
AND caldav_data.collection_id = 10
AND rrule IS NULL
AND dtstart < '2010-01-01';
For that to work you would need to have created the 'archive' calendar
first, of course, and then something like:
SELECT dav_name, collection_id FROM collection
WHERE dav_name IN ( '/user1/home/', '/user1/archive/');
Obviously you could replace that in above, but makes the SQL a bit more
obscure...
UPDATE caldav_data
SET dav_name = replace( caldav_data.dav_name, '/user1/home/', '/user1/archive/'),
collection_id = (SELECT collection_id FROM collection
WHERE dav_name = '/user1/archive/')
FROM calendar_item
WHERE caldav_data.dav_id = calendar_item.dav_id
AND caldav_data.collection_id = (SELECT collection_id FROM collection
WHERE dav_name = '/user1/home/')
AND rrule IS NULL
AND dtstart < '2010-01-01';
Unfortunately repeating events in this way is *a lot* more complicated,
because you only want to move the ones that have finished, which means
expanding all the instances and working out whether they have
finished... It's probably easier to just manually move them in the
calendar itself.
Cheers,
Andrew.
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You will be run over by a beer truck.
------------------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100308/850de111/attachment.pgp>
-------------- next part --------------
> Hi Guys,
>
> I've got here (due to super slow sunbird perf) a question about
> putting old events from a collection to
> another one.
>
> I imagine that's better to do this directly in postgresql as normal
> ical client ask event by event (there's 9'000 to move :-)
>
> I would have done some research on my own yesterday and today, but
> I've crash my car yesterday on ice. And been busy to organize
> fixing, doing insurance things etc ..
>
> So If someone has some shortcuts to help me building a good sql, it
> will be really appreciate.
Hi Bruno,
You need to update caldav_data table (calendar_item will be maintained
with a trigger) with the new path, and the new collection_id.
Here's an example moving non-repeating events, prior to the 1st of
January, from /user1/home/ (collection_id 10 for me) to /user1/archive/
(collection_id 1429 for me):
UPDATE caldav_data
SET dav_name = replace( caldav_data.dav_name, '/user1/home/', '/user1/archive/'),
collection_id = 1429
FROM calendar_item
WHERE caldav_data.dav_id = calendar_item.dav_id
AND caldav_data.collection_id = 10
AND rrule IS NULL
AND dtstart < '2010-01-01';
For that to work you would need to have created the 'archive' calendar
first, of course, and then something like:
SELECT dav_name, collection_id FROM collection
WHERE dav_name IN ( '/user1/home/', '/user1/archive/');
Obviously you could replace that in above, but makes the SQL a bit more
obscure...
UPDATE caldav_data
SET dav_name = replace( caldav_data.dav_name, '/user1/home/', '/user1/archive/'),
collection_id = (SELECT collection_id FROM collection
WHERE dav_name = '/user1/archive/')
FROM calendar_item
WHERE caldav_data.dav_id = calendar_item.dav_id
AND caldav_data.collection_id = (SELECT collection_id FROM collection
WHERE dav_name = '/user1/home/')
AND rrule IS NULL
AND dtstart < '2010-01-01';
Unfortunately repeating events in this way is *a lot* more complicated,
because you only want to move the ones that have finished, which means
expanding all the instances and working out whether they have
finished... It's probably easier to just manually move them in the
calendar itself.
Cheers,
Andrew.
------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com +64(272)DEBIAN
You will be run over by a beer truck.
------------------------------------------------------------------------
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 198 bytes
Desc: This is a digitally signed message part
URL: <http://lists.morphoss.com/pipermail/davical-users/attachments/20100308/850de111/attachment.pgp>
-------------- next part --------------