Author Topic: How to migrate additonal images from vm 1.1 to 2.0  (Read 2196 times)

kurgan

  • Beginner
  • *
  • Posts: 1
How to migrate additonal images from vm 1.1 to 2.0
« on: September 07, 2012, 23:49:37 pm »
Ok I have just migrated virtuemart from 1.1.5 to 2.0.10 and everything goes fine.
Everything ? No ! All my additional images are not available on 2.0 !
Ok no problem, I though, just have to check on this great forum how the VM fans do to migrate them.
After some research what a surprise ! there is no way to migrate my 900 additional images automatically ! I have to do it by hand !  :o
I have only 2 hands...
but... I have a brain  ;) so I tried this method I want to share with you.
All the queries are done in Phpmyadmin.

0- First of all do a BACKUP of your database.

1- Then create a temporary work table

Code: [Select]
CREATE TABLE  `eric` (
`virtuemart_product_id` INT( 1 ) UNSIGNED DEFAULT  '0',
 `virtuemart_media_id` INT( 1 ) UNSIGNED DEFAULT  '0',
 `ordering` INT( 2 ) DEFAULT  '0',
 `product_id1` INT( 11 ) DEFAULT NULL ,
 `file_product_id1` INT( 11 ) DEFAULT NULL ,
 `file_name1` VARCHAR( 50 ) DEFAULT NULL ,
 `products_sku` VARCHAR( 50 ) DEFAULT NULL
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

2- fill in the table "eric" with the link between products and media from version 1.1 and 2.0
Code: [Select]
insert into eric
select vmp2.virtuemart_product_id , vmm2.virtuemart_media_id,
NULL,vmp1.product_id,vmpf1.file_product_id,
SUBSTRING_INDEX(vmpf1.file_name,"/",-1),vmp2.product_sku
from jos_virtuemart_products vmp2,
jos_vm_product vmp1,
jos_vm_product_files vmpf1,
jos_virtuemart_medias vmm2
where vmp1.product_sku=vmp2.product_sku
and vmpf1.file_product_id=vmp1.product_id
and vmm2.file_title=SUBSTRING_INDEX(vmpf1.file_name,"/",-1)

3- In 2.0 images are linked to products in an order describe in the field "ordering"
For each image attached to a product we have to set an order.
I chose to set this order using a while loop
In order to know how much loops we have to do, let's run this command and remember the result as "the loop number"
Code: [Select]
select max(n.nombre) from (SELECT count(`file_title`) as nombre FROM `jos_vm_product_files`
group by `file_product_id`) AS n

4- Create a stored procedure to update the order
First change the Phpmyadmin delimiter to $$,
replace in the code below the_loop_number by the result of the query above
and run:
Code: [Select]
create procedure myupdate (ordre int)
BEGIN
while ordre <= [i]the_loop_number[/i] do
update eric e1
left join ( select e2.`virtuemart_product_id`,min(e2.`virtuemart_media_id`) as media_id
   from eric e2
   where e2.`ordering` is NULL
   group by e2.`virtuemart_product_id` ) AS E2
   ON e1.`virtuemart_product_id`=E2.`virtuemart_product_id`
set e1.ordering= ordre
where e1.`virtuemart_media_id`= E2.media_id;
set ordre = ordre+1;
end while;
END$$

5- Run the stored procedure:

Code: [Select]
call myupdate(2)

6- Update virtuemart product medias table:
 
Code: [Select]
insert into jos_virtuemart_product_medias (`virtuemart_product_id`,`virtuemart_media_id`,`ordering`)
select e.`virtuemart_product_id`,e.`virtuemart_media_id`,e.`ordering` from eric e

7- Do some housework:
Code: [Select]
drop procedure myupdate
and

Code: [Select]
drop table eric


And.... that's it ! You have the additional images available  :)
I still have one question to the virtuemart developers: Why did they not include the additional images to the upgrade procedure ??
May be just to let me write my first post   ???

Eric



gain

  • Beginner
  • *
  • Posts: 47
Re: How to migrate additonal images from vm 1.1 to 2.0
« Reply #1 on: November 24, 2012, 12:42:21 pm »
Useful, thanks. Works like a charm! ^^

Before finding this I was studing how VM2 stores media products for fixing it myself. I've noticed that every media stored in #__virtuemart_medias may have, at least, a "link" in one of this  table:
#__virtuemart_category_medias
#__virtuemart_manufacturere_medias
#__virtuemart_product_medias
#__virtuemart_vendor_medias

Am I wrong?

So I've tried checking if I have any media with no links with this query
Code: [Select]
select * from j25_virtuemart_medias where
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_category_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_manufacturer_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_product_medias) and
virtuemart_media_id not in (select virtuemart_media_id from j25_virtuemart_vendor_medias)
and it returns 1,707 lines, so... have I 1,707 medias inserted in my db but not used (orphan medias)?

bytelord

  • Nikos
  • Global Moderator
  • Full Member
  • *
  • Posts: 2369
Production: Joomla 2.5.8 | VM 2.0.14 | PHP 5.3.13
Testing     : Joomla 2.5.8 | VM 2.0.16 | PHP 5.3.8
Testing     : Joomla 2.5.8 |    VM 2.1   | PHP 5.3.8

- Don't Forget to mark thread as solved when it is solved!
- Please do not PM with support questions, use the forum!

gain

  • Beginner
  • *
  • Posts: 47