Virtuemart database diagram/scherma does that exist?

[nkamp]

Hello,

I have seen this question more, but I have never seen a answer were I can say from ok that's it.

Does a vm 2.6.x database diagram/schema exist?
If Yes, can I download it?
If I can download it, were can I download it?
If I can't download it, why not?

If it not exist, then I'm amazed but why not? because as developer it is very usefull I think.

Can someone answer these question, so that it is once and for all clear  if there is a database diagram/schema or not!

Thanks,

Nico

[Milbo]

Actually the naming convention makes it for most request so easy, that you can directly see it.

But the real problem behind your question is your idea how to use the VirtueMart. You can spare a lot of time using the correct methods. Instead of learning how to store a product manually, use the vm API to store a product.

Instead of handling an own table for a plugin, just use the provided configuration params and internal table.

[nkamp]

Hello Milbo,

Maybe you're right that I don't understand how to use virtuemart, but the question why I'am asking this is not to create a new table or plugin.

It's migrating from joomla 1.5/vm1.1.9 to joomla2.5/vm2.6.6. I have made a new site in my evening hours for my brothers shop. This is not done in day. So in the mean time new customers have been registered and order products.
So in the comming week I want say, ok the production site goes down. then with phpmyadmin I want the empty the joomla users table with the vm users/order tables and populate them with the data of the 1.5 'old' site.

Ok, I have to write a sql script to migrate the latest data of the old site to new site. (I know the tables are different between vm 1.1.9 and vm2.0).

But before I can do this I have to know which tables are involved in this , so that I can make a script here fore. And the easiest thing for me is to this with a database schema, so that I nothing will forget.

Nico

[AH]

nkamp

You do not have to write your own sql

VM2 has a pretty decent migration option, depending on how complex your products are

You will need some custome sql to get the joomla users migrated but the VM stuff is really straightforward.


There are many ways to migrate.

It really depends on your requirements
Have you customised aspects of the VM database


Here are my thoughts:-
I have added (what I felt were missing) fields in the order tables and product tables  so I have to make sure that I don't lose such data when I migrate
If you have no such customisation then that makes things simpler.

I want a clean cut so not interested in historic orders within the new dbase:-
I will keep a local version of the old system running on xampp for referrence/reporting/customer service purposes (and I can keep this for as long as I deem it relevant)  I  do this anyway as part of our business continuity strategy (regardless of my migration strategy) so no extra effort required

My requirements:-
Migrate the following:-
Registered user data
Products (we don't use child products)
Prices
Product images
Categories
Suppliers
Don't lose any historic orders and be able to reference them for our customers if required.  Customers will not be able to back reference but we will inform the "important" ones by mail.

Keep Product and Category VM id's the same We don.t want these modifying in the migration as We have business processes and reporting based on current values   if you use the stock migration tool without mods you will not get this ALL ids prod and category get changed!
Ensure that the Category List order we have in current VM is maintained (If you have sorted your products in a category and show them in category list order VM migration does not keep this!!  If you have your items appearing in this sequence and have spent any time doing this YOU WILL lose it if you use the stock migration tool without mods

Very brief outline:-
Setup a test environment in xampp or on your servers
I start from having a fully functional Joomla 2.5 installation with all my articles etc ported over
I install VM 2.6.6 (Latest stable)
Do a quick configuration
I modify the stock migration code to meet my needs regarding id's and additional data on product file (so it gets ported over)
I modify the existing code and database to take my new product fields and stop it trying to port irrelevant tables
I extract the VM tables and joomla user table from the existing database
Copy over the VM images directories
Import these to the new dbase
Run a quick piece of custom SQL to move the registered customers into the new joomla tables  (I found this somewhere and it works so I use it!)
Run the migration until all prods are migrated


OK so what are the benefits of this approach:-

1. If you have lots of records in your dbase you can reduce the volumes of products for a "test" by some simple database deletes
2. You can truncate the core VM data and run the migration lots of times to make sure you are happy
3. When happy we can try a full test migration
4. I dont have to move a load of orders from the old dbase (which can take ages and I was never happy with the result - could not be bothered spending ages to fix when the copy system in xampp met our business needs)

Downsides - and there are quite a few more than those on this list
1. You have to be a little more technical and not just a user
2. You have to be prepared to mode core code (but dont we all have to do that somewhere along the line?)
3. It takes longer than the push the button and pray approach


This is a migration so ALWAYS USE A TEST SETUP FOR THIS FIRST




Here is the sql (needs to have your db name and prefix updated) for migrating joomla users before migrating VM

Code Select Expand



# Migrate existing users from joomla 2.5 table old_brs_users

TRUNCATE TABLE `yourprefix_users`;
TRUNCATE TABLE `yourprefix_user_usergroup_map`;

DELETE FROM `your_db_name`.`yourprefix_users`;
DELETE FROM `your_db_name`.`yourprefix_user_usergroup_map`;
ALTER TABLE `your_db_name`.`yourprefix_users` AUTO_INCREMENT = 1;
ALTER TABLE `your_db_name`.`yourprefix_categories` AUTO_INCREMENT = 1;

INSERT INTO
    `your_db_name`.`yourprefix_users`
        (
            `id`,
            `name`,
            `username`,
            `email`,
            `password`,
            `usertype`,
            `block`,
            `sendEmail`,
            `registerDate`,
            `lastvisitDate`,
            `activation`,
            `params`
        )
SELECT
`id`,
`name`,
`username`,
`email`,
`password`,
REPLACE(`usertype`, 'Super Administrator', 'Super Users'),
`block`,
`sendEmail`,
`registerDate`,
`lastvisitDate`,
`activation`,
''
FROM `your_db_name`.`old_brs_users`;

INSERT INTO
    `your_db_name`.`yourprefix_user_usergroup_map`
SELECT
    users.`id`,
newgroups.`id`
FROM
`your_db_name`.`old_brs_users` users,
`your_db_name`.`yourprefix_usergroups` newgroups
WHERE
REPLACE(users.`usertype`, 'Super Administrator', 'Super Users') = newgroups.`title`;



[Milbo]

or use this http://extensions.virtuemart.net/import-export/virtuemart-migrator-detail

[AH]

As I said, there are many ways to migrate 

I needed product and category id's to be the same along with product category sort order to be the same.

I also needed custom product fields to be kept and this tool would not achieve that.

I have read some good reviews of it though.