Dropping database fields on update

[balai]

Hi

I had some extra fields in my #__virtuemart_order_items  table.

I noticed that after the update to 2.0.6 these have gone.

Is this necessary to happen on every update?

[John2400]

I'm not really going to solve your issue but I notice the drop and in Extension Manager warnings _

under Further warnings - It told me that the data base needed fixing -  I did this with no real issue.

I sould have said tha the fix button is on that page as well.

[Milbo]

you can disable it balai, there are already posts about this. The secret is to use the virtuemart.cfg file
write it in it upDelCols=false. go to your tools, update config by file, then reconfigure your store, ....

upDelCols prevent deleting columns.

[franzpeter]

@Milbo,

I did post that question too some time ago. You did tell me to set upDelCols = 0. Did update today from 2.04 to 2.06 and the installer did delete all additional rows with the whole data inside. So what is the correct way:
upDelCols=false or upDelCols = 0? Obviously upDelCols = 0 does delete those additional fields! I am really happy that I do not use VM 2.xx as life shop. That function, which deletes additional rows and data is a really nasty thing. Sorry to say that!

It is possible to update database tables without dropping additional fields as VM 1.1.xx did without any problems!

[balai]

@Milbo

Thanks for the feedback.
Wouldn't be better to be set this to false by default?

Its hard for the user to imagine that such a functionality exist.
So anyone can loose possibly  "important" and big amount of data this way.

[Milbo]

No
what you do is hacking the store. That is the problem. I am sure you can easily do a plugin for it. Or you wrote a plugin and you dont know that they can have their own tables. But I will change the if to use as default true, so anyother value then true should work then for you.

[balai]

@Milbo

Maybe i can solve this through a plugin or extra tables.
But this is not something that regards me.

Usually a shop is used together with CRM and ERP systems which may generate or need additional fields in some database tables to connect each other.
This feature can become really disastrous in these cases as people can loose their data in a minute without even getting any notification or something.

Also since the core remains unmodified this cannot be considered a "hack".

[franzpeter]

@balai,

I completely agree with you. CRM and ERP tools are a good example for the synchronizing issues, which VM 2 produce with every update. It does not make any sense to delete every row with all the data from a database table, which does not belong by default to the VM table. It is indeed sometimes better to use an own created specific row, to do the synchronize task. For example: VM produces product_id or order_id. If you have an online shop with virtuemart and you have a local store too, the order_id or the product_id may differ. Those so called unique things like product_sku or even EAN codes are not unique. It can happen that different products have the same sku or the same EAN. As far as I know, only pharmaceutical products or books have those unique numbers.
So you need something inside the database, which can synchronize for example between an ERP system and VM. If that task is destroyed with every VM update, VM is not the right software to use with those ERP systems!

For me a database hack is if someone changes the default rows in a table, additional rows are not a hack!

[e-cows]

I followed the suggestion of  Milbo:

• I manually added upDelCols=0 at the end of administrator/components/com_virtuemart/virtuemart.cfg
• I reset virtuemart configuration using the button available in utility menu
• I upgraded Virtuemart from 2.0. 2 to 2.0.6

All is good: my additional fileds in virtuemart where preserved. 

Even if i do not touch php, IMHO the fields addition is a Virtuemart hack. I think that an elegant solution should be to write a new module component and create a new table to store additional data in its install procedure, but how to be sure that my data will be deleted when user delete an item from Virtuemart interface (ie db inegrity)? There is  hook to attach my delete procedure to the virtuemart one? Could you suggest me a module that do something similar to use as an example?

[franzpeter]

@e-cows,
so it seems that sometimes adding upDelCols=0 works. In my case I can confirm that I did the same steps and it did not work.
I do not understand what you mean: when user delete an item. If a user deletes an item, the item has gone from the database. So the synchronization will detect that and delete it in the ERP too. If that does not work, it would be a VM bug. For example: If a user delete a product or an order, those products or orders are removed from the tables. So for example if you remove a product VM removes it by using the product_id or order_id: delete from table xyz where product_id = 'zxy'. VM does delete that, I hope so, from all tables, which relate to that product_id. So if a product with the product_id zyx has gone, the synchronizing row is removed too, automatically. The ERP connecting script can detect that and remove it, if desired, from the ERP database too. I do not see any problem with that.

[e-cows]

Hi @franzpeter,
sorry, I m a newbie of Virtuemart programming. What do you mean with "synchronization"? A process in Virtuemart? A standard behavior of Joomla? a feature of MySQL?

Note that, in my case,  I added extra fields to some VM  tables just to create a link with external resource, nothing to do with ERP synchronization:  I use extra fields to support an import  process and to trace the source of  information. I would like to write a Joomla component that do this not adding fields to standard VM tables but creating its own tables related to VM ones.

I do not see any foreign key definition in Virtuemart DB structure. It seams to me the the db integrity is maintained at application level only  if you use methods from proper php classes. in other words, if I delete a row in a  table in Virtuemart DB (using phpmyadmin for instance) , related rows in other tables with stay untouched.

So, what is the right way to maintains DB integrity if a new component creates new tables/objects with relations with standards Virtuemart objects? Could someone suggest some resource to learn about this topic?

But probably this is another topic...

[franzpeter]

@e-cows,
yes, VM does not use foreign key constraints (Tienda, another e-commerce Joomla component does for example) would need an InnoDB for example. It is no problem, if a new component creates new fields in a database table, because the way VM treats it would remove those fields too if something gets deleted. If a user deletes for example a product, VM deletes it using the product_id. It first deletes those entries in the pseudo related tables and finally in the main product table.
Your are right, if you use phpmyadmin, say to delete the content of the product price table, the database itself does not care about. All the other tables remain as they are. So you have to take care about deleting those other data in the other tables too. But that has nothing to do with additional fields in a table. It is the way a MYISAM database works. You can delete everything with phpmyadmin, the database does not care about. MYISAM does not use table relations, only the php code, which interacts with the database can provide that. The advantage of MYISAM is, that it is faster than InnoDB, while InnoDB takes care about the integrity.
But I do not understand what you exactly mean with DB integrity in that case. A faulty default VM code can break the integrity in the same way as a faulty self written script. Maybe a nearly perfect solution would be to use own tables, fill them with the necessary data, write the result to VM tables and synchronize it with something from the untouched VM table, by writing back the result of the insert in the VM default tables with something unique, for example the product_id, category_id, manufacturer_id, order_id a.s.o. to that additional table. But what kind of sense would it make? It would mean another time consuming step only because the VM delevopers did decide to erase additional fields with every update by default.
The VM 2 database may look a little bit different but it is the same thing as the VM 1 database. There are MYISAM tables and a php coder has to take care about the integrity of the database. Why should an additional field break the integrity?

[Milbo]

Our plugins can have their own tables and store stuff. Maybe the best is to add a trigger to the product store (maybe there is already something like this there). So you cna store the extra information "natively".

Why should an additional field break the integrity?

Interesting question. In fact I realise myself that I did that to prevent other programmers to use non used fields.

[balai]

@Milbo

The db integrity can break only using tables that are not part of the Virtuemart db schema.

For example if you have a table named "product_external_code" when you delete a product in VM, the contents of that table will remain untouched. This obviosuly breaks the integrity.

There are 2 solutions to that:
1. Virtuemart to add plugin hooks for product update/delete/publish etc.
2. To let the users add their own database fields to the existing VM tables.

In my opinion the behavior of Virtuemart during the update is too "authoritarian". It would be much better if the users would be able to decide on their own and take the responisbility for that.

A good way to do that is to display a notification during the installation, asking them if they agree or not with that process.

[e-cows]

@balai
you got the point.
Now the question is clear:

how to extend Virtuemart data model?

I think that this is a general question. I'm not a Joomla/Virtuemart programmer but this question seams apply to Joomla also.
How Joomla solve this problem?

For what I read, it seams that the only solution is to write "plugins" that trigger content events.
There is something similar for Virtuemart Objects? Could I write a virtuemart plugin to trigger (as example) a category deletion event?

Thankyou @balai, @Milbo and @ franzpeter  for your precious support.