Import subscriptions from Joomlacomment - Help

[thejackal]

Hi. I recently migrated my site to the newest joomla version and decided to migrate my joomlacomment system to Jcomments, because it looks great. I migrated 3,000 comments to Jcomment two weeks ago. Just today I realized that none of the "subscription" info was migrated. So now I have 3,000 comments and two weeks of new comments mixed in there, and no subscriptions.  What can I do here? I consider this an INTEGRAL piece of migration. 

[thejackal]

I looked and with some work I can do the sql map myself. BUT I need to understand what the HASH field in jos_jcomments_subscriptions table is used for. If I map my comment subscriptions from the old system (didn't have any hash) to the new, what do I need to put into the HASH field for the new comments to make sure it will work ok? Thanks.

[smart]

You could use next script to migrate (it will be included in upcoming version). It is quite easy so I think will understand how to fill this field. The field 'hash' used to manage subscriptions from e-mails.

1. Alter table and add new field

Code: (sql)

ALTER TABLE `jos_jcomments_subscriptions` ADD `source` VARCHAR(255) NOT NULL DEFAULT '';
ALTER TABLE `jos_jcomments_subscriptions` ADD INDEX `idx_source`(`source`);

2. Import comments subscriptions

Code: (sql)

INSERT INTO jos_jcomments_subscriptions (`object_id`, `object_group`, `lang`, `userid`, `name`, `email`, `hash`, `published`, `source`)"
SELECT DISTINCT contentid
, CASE WHEN component = '' THEN 'com_content' ELSE component END
, 'en-GB'
, userid
, name
, email
, md5(concat(contentid,CASE WHEN component = '' THEN 'com_content' ELSE component END,userid,email,'en-GB'))
, 1
, 'joomlacomment'
FROM #__comment c
WHERE `notify` = 1
AND NOT EXISTS
(
   SELECT * FROM #__jcomments_subscriptions s
   WHERE s.object_id = c.contentid
   AND s.object_group = CASE WHEN c.component = '' THEN 'com_content' ELSE c.component END
   AND s.userid = c.userid
   AND s.email = c.email
);

[thejackal]

Thanks for the fast response! I already spent a couple hours and learned the code and wrote my own sql. And it's all set. I think your post will be very useful for others who encounter the same problem.