SVN upgrade procedure - for DB changes

[boran]

Hi,

I'm using the SVN version to keep up with fixes, and now need to do an upgrade.
I'd like to learn from others to see how they do it.

- The code base can be updated with "svn update" and local mods detected and blended in this way too (svn update -u, svn status and svn diff).

- DB changes seem more tricky
- The install GUI update option cannot be used because it is grayed out (because the official version in settings.inc has not changed)

My first attempt (for upgrade from SVN revision 370 of early Jan to 463):

cd install-dev/sql
svn diff -r 370:463|more

This gives a long list though, for example:

Index: db_settings_lite.sql
===================================================================
--- db_settings_lite.sql (revision 370)
+++ db_settings_lite.sql (revision 463)
[spam-filter] -42,7 +42,8 [spam-filter]
(39, 'cancelProduct', 'Product cancelled', 'This hook is called when you cancel a product in an order', 0),
(40, 'extraLeft', 'Extra actions on the product page (left column).', NULL, 0),
(41, 'productOutOfStock', 'Product out of stock', 'Make action while product is out of stock', 1),
-(42, 'updateProductAttribute', 'Product attribute update', NULL, 0);
+(42, 'updateProductAttribute', 'Product attribute update', NULL, 0),
+(43, 'extraCarrier', 'Extra carrier (module mode)', NULL, 0);

INSERT INTO `PREFIX_configuration` (`id_configuration`, `name`, `value`, `date_add`, `date_upd`) VALUES
(1, 'PS_LANG_DEFAULT', '1', NOW(), NOW()),
[spam-filter] -101,11 +102,9 [spam-filter]

INSERT INTO `PREFIX_category` VALUES
(1, 0, 0, 1, NOW(), NOW());
-INSERT INTO `PREFIX_category_lang` VALUES
-(1, 1, 'Home', '', '', NULL, NULL, NULL),
-(1, 2, 'Accueil', '', '', NULL, NULL, NULL),
-(1, 3, 'Startseite', '', '', NULL, NULL, NULL),
-(1, 4, 'Inicio', '', '', NULL, NULL, NULL);
+INSERT INTO `PREFIX_category_lang` (`id_category`, `id_lang`, `name`, `description`, `link_rewrite`, `meta_title`, `meta_keywords`, `meta_description`) V
ALUES
+(1, 1, 'Home', '', 'home', NULL, NULL, NULL),
+(1, 2, 'Accueil', '', 'home', NULL, NULL, NULL);
....

Then I restricted it to install-dev/sql/upgrade, but even then there is about 3 pages of changes starting like:

Index: 1.2.0.1.sql
===================================================================
--- 1.2.0.1.sql (revision 370)
+++ 1.2.0.1.sql (revision 463)
[spam-filter] -15,12 +15,18 [spam-filter]
ALTER TABLE PREFIX_customer
ADD deleted TINYINT(1) NOT NULL DEFAULT 0 AFTER active,
DROP INDEX `customer_email`;
-
+
ALTER TABLE PREFIX_employee
- ADD stats_date_to DATE NULL AFTER last_passwd_gen;
+ ADD stats_date_to DATE NULL DEFAULT NULL AFTER last_passwd_gen;
ALTER TABLE PREFIX_employee
- ADD stats_date_from DATE NULL AFTER last_passwd_gen;
+ ADD stats_date_from DATE NULL DEFAULT NULL AFTER last_passwd_gen;

+ALTER TABLE PREFIX_order_state ADD hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER unremovable;
+
+ALTER TABLE PREFIX_carrier ADD is_module TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER range_behavior;
+
+ALTER TABLE PREFIX_order_detail CHANGE product_quantity_cancelled product_quantity_refunded INT(10) UNSIGNED NOT NULL DEFAULT 0;
+
CREATE TABLE PREFIX_customer_group (
id_customer INTEGER UNSIGNED NOT NULL,
id_group INTEGER UNSIGNED NOT NULL,
[spam-filter] -89,8 +95,8 [spam-filter]
cache_registrations INTEGER NULL,
cache_orders INTEGER NULL,
cache_sales DECIMAL(10,2) NULL,
- cache_reg_rate DECIMAL(8,4) NULL,
- cache_order_rate DECIMAL(8,4) NULL,
+ cache_reg_rate DECIMAL(5,2) NULL,
+ cache_order_rate DECIMAL(5,2) NULL,
date_add DATETIME NOT NULL,
PRIMARY KEY (`id_referrer`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
[spam-filter] -108,10 +114,19 [spam-filter]
PRIMARY KEY (`id_module`, `id_group`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
....

q


This is not so easy to interpret and use.
Switching then to Windows with Tortoise SVN's diff window, its a bit easier to read, giving changes like this:

ALTER TABLE PREFIX_customer
DROP INDEX `customer_email`;
ALTER TABLE PREFIX_employee
ADD stats_date_to DATE NULL DEFAULT NULL AFTER last_passwd_gen;
ALTER TABLE PREFIX_employee
ADD stats_date_from DATE NULL DEFAULT NULL AFTER last_passwd_gen;

ALTER TABLE PREFIX_order_state ADD hidden TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER unremovable;

ALTER TABLE PREFIX_carrier ADD is_module TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 AFTER range_behavior;

ALTER TABLE PREFIX_order_detail CHANGE product_quantity_cancelled product_quantity_refunded INT(10) UNSIGNED NOT NULL DEFAULT 0;
...

So apart from substituting TABLE PREFIX, these would be the changes needed.


Sorry if this posting is a bit long winded but the idea was to give as much details as possible.
How are others managing such upgrades?

Thanks in advance,

Sean

[Damien Metzger]

There's no easy way to do it. You have to use your brain each time

[boran]

So what I propose using The tortoise SVN + diff + highlighting changes above makes sense?
(I didn't find an easy way of doing the same thing on the Linux cmd line).

Any quick tips on generating the SQL updates apart from copy and paste from the diff? :-)

[Damien Metzger]

It does make sense.
But be careful, we often forget to add the changes in the upgrade file. The files of reference are db.sql for the structure and db_settings_lite.sql for the content.

[Fabrice]

Hi Damien !

I just started to use the last version in production environment. As it looks very stable to me (except one or 2 things which are not dramatic).

Question about futur updates in relation with this post : will there be a problem to upgrade to 1.2 final version ?
Will the database structure be modified ? or at this level of development, there will not be any "big" modification which could cause the upgrade to be impossible ? Up now, I do make changes regarding the svn updates (no database updates, just css code, or module code)
In the case of database structure modifications, will the final 1.2 be able to update an SVN version, by detecting the right tables to add/change ?

Thanks for your answer,
I hope I did not upgrade to quickly with the svn...

[Damien Metzger]

The 1.2 updater will try to re-do all your database modifications.
And there will be more database changes imho.

[boran]

I just did some diffs,
- 1.2.0.1.sql looks ok, a coherent 1-2 diff was produced

- the formatting in db.sql must have changed alot because tortoise has big problems producing a comprehensible diff.

- perhaps a comment could be added to the top of db.sql, db_settings_extends.sql and db_settings_lite.sql explaining what they are?
- db_settings_extends.sql: this looks like default content and settings.
The diff shows many changes not in 1.2.0.1.sql (see attached)

- db_settings_lite.sql: seems similar

I've attached a combined diff fort the above except for db.sql, which was too complicated.

This is a lot of work! Perhaps one could explain the relationship between the three files? Should the diffs in 1.2.0.1.sql not cover the other three files?

Sean

[Damien Metzger]

db : database structure
lite : required content (tabs, hooks...)
extends : optionnal content (modules pre-installation, fake products...)

[boran]

Should the diffs in 1.2.0.1.sql not cover the other three files?

[Damien Metzger]

Exactly, they SHOULD

[Fabrice]

The 1.2 updater will try to re-do all your database modifications.
And there will be more database changes imho.

the question is : will that work ? (upgrade from SVN to 1.2 ?)

[boran]

I think it will, but one will have to be attentive to the errors from the SQL output.

Personally I'd suggesting running each statement in the upgrade script one by one, so that if an error happens when a new field is inserted, one can judge whether its OK because the field is already there, or not OK because maybe the field has a different type...
Where is gets complicated is when one does not wish to wait for the next release, which is what I was doing above..

[Fabrice]

ok. I think you're right boran.
but clearly, how may we proceed to run the script step by step ? an idea ?

[vins]

Helo Boran,
Very interested in your investigations...
Perhaps I'm wrong but what I understood is that it's difficult to follow the changes between different SVN versions : Easy to update files but very complicated to update the Data Base.
Two issues:
1. As Damien said before : Prestateam often forget to update de upgrade file.
2. It's difficult to compare the new .sql files with your local files.

I thought about one thing:
YOu said that the install GUI update option cannot be used because it is grayed out (because the official version in settings.inc has not changed). Do you think it could be possible to ungrayed by changing something in the prestashop code ? 2 or three month ago, I've tryed to do it but I remembre that something was'nt working fine. I can't remember today what was the problem.

By.

[boran]

I see no problem doing step-by-step DB fixes, I just paste one upgrade command at time into the MySQL command prompt. For me what is difficult was going through the SVN diffs and building the SQL update file.

>> You said that the install GUI update option cannot be used because it is grayed out (because the official version in settings.inc has not changed). Do you think it could be possible to ungrayed by changing something in the prestashop code
?

Just change the version number indicated in settings.inc, then it will compare and allow an upgrade, I think.

[Fabrice]

@ votreprestashop : do you think that this is possible ? upgrading a base (svn) with a new SVN version ? (after changing the settings.inc version number)
4 cases/options are possible : the upgrade is
- creating new field in sql base (that sound's ok) - however, is it possible that the update is adding some data in this filed ? or is it just creating the new one ?
- modifiying a field description : do not know how the sql base will react - do you have some infos about that ?
- modifying a field name
- deleting a field (that could be a problem, if the field contains information)

other option : creating a brand new sql base using fresh install, and importing all data inside. can that work ? how is PHP myadmin acting in such case ? are the different fields automaticaly syncronized ? globaly how does phpmyadmin act ? did it compar the field name, and import datas ? any idea ? I would like to understand and be able to update the SVN. tks.

[vins]

Fabrice, I'm going to try to undurstand more about upgrade and will try to answer to your questions once this done...

Boran, could you explain what you mean with your question : "Should the diffs in 1.2.0.1.sql not cover the other three files?" And can you explain the answer of Damien : "Exactly, they SHOULD :D"

[Fabrice]

Thanks ! it's very kind from you.

I am truying locally on another server to update the SVN, then another try : install fresh and importing datas. I will tell you about results.

[vins]

Boran,
I have tryed to understand the way to update the DateBase between two revisions...
What do you think to re-use the file doUpgrade.php (with of course modifications) to update the data base ?

The problem with this method is that as Damien said before, sometimes the Prestateam forget to update the 1.x.x.x.sql file in upgrade directory.
But I think that the risk is small. What do you think about this method ?
Thanks.

[boran]

Like Damien says, its not enough to just run the upgrade script, it may be incomplete (and it was for me)

I've started documenting my notes on http://boran.com/presta#Upgrading
if that helps you. Attached is an SQL script I've been testing as part of the above.

My suggestion would be to wait for a point release if at all possible (but still use SVN), otherwise prepare to have a a few days to manually check for changes in code and SQL with a fine comb.

[vins]

Why do you say that it's not enough to just run the upgrade script... I think it's enough if the Prestateam does'nt forget to update the upgrage.sql file.

Yes I completly agree with you : I think that it's safe to wait for a point release (but still use SVN).
I give some explanations on my point of view:
Imagine that the curent version is 1.1... but your interest is to work with the SVN revision 1.2.0.1 You update all your files via the SVN (with tortoise for ex.) and install your prestashop.
Your are now with revision 1.2.0.1 running on your server...
When updating your files via SVN you check if the 1.2.0.1.sql file is updated... if yes you report manualy the changes on your datebase via PHPMyAdmin (this works if Prestateam has not forgoten to update its 1.2.0.1.sql file...
The day a new release is ready on the SVN for exemple 1.2.0.2, you juste make an upgrade with classic prestashop procedure.
Tell what you think about that.
Thanks.

[boran]

> Why do you say that it’s not enough to just run the upgrade script… I think it’s enough if the Prestateam does’nt forget to update the upgrage.sql file.

I imagine that the PS team review and double check the upgrade sql file on each point release, but not for every single SQL change (i.e. SVN commit). However I understand that the other SQL files are updated for each commit.

> Imagine that the curent version is 1.1… but your interest is to work with the SVN revision 1.2.0.1 You update all your files via the SVN (with tortoise for ex.) and install your prestashop. Your are now with revision 1.2.0.1 running on your server…
> When updating your files via SVN you check if the 1.2.0.1.sql file is updated… if yes you report manualy the changes on your datebase via PHPMyAdmin (this works if Prestateam has not forgoten to update its 1.2.0.1.sql file… wink
> The day a new release is ready on the SVN for exemple 1.2.0.2, you juste make an upgrade with classic prestashop procedure.

My feeling (I'm not on the PS team, can Damien or a team member confirm?) is that if you upgrade from point release like 1.2.0.1 to 1.2.0.2, then the upgrade SQL file will be fine, and thus the GUI upgrade procedure too.

Sean

[vins]

I agree that upgrading from point release to an other point release is ok (upgrade sql file is clean). But I think also that if you work with an SVN revision and update it day to day you have also to update de DATABASE manualy.
Yes Damien can you confirm that if there is a change on the DATABASE the sql upgrade file is also updated (if you not forget it).
Thanks.
Fabrice did you catch all this explanations ? Tell me.

[Fabrice]

I have a question : If i find a soft which compares all the database structure, and implements the modifications to the current database ?
that should work no ?
I've just tried a soft which seems to work nicely. do you think that's the solution ?

[Fabrice]

alleluiah ??

"perhaps" I have a quick and nice solution to update database build with SVN. ;-)

That is what I do :
Made a fresh clean and full install of the brand new SVN.

comparing the database structure to the existing one is the problem.
so, I found out a soft which looks terrific : it's called NAVICAT.

The soft is comparing both databases, shows the differences, and operate alone for changing all the database differences. adding or deleting what is necessary, modifying structure etc...
I made a test, and looks nice. PS did not crash, and seems to work like before.

I am wondering if a such simple solution can definitely work ?
make the tests and tell me your opinion...

[Fabrice]

So i did progress.

have a look on noth screen copies.
1. the general settings gives you an idea of what the soft can check
2. the compare results : what t found and what will be applied.
looks nice no ?
and btw, you can test the soft 30 days. then it will cost about 120 $ (not to expensive regarding the time you win with this solution)

EDIT : one question - are datas (in the database) modified during the upgrade process ? I don't think so, but wanted to be sure. That's the only think we could not control with this method.

[boran]

If you want to go that way, there is also a tool for windows:
http://www.spectralcore.com/datasync/index.php
Its about $170

[Jean-Bruno]

Hi all
Fabrice Is that works well with Navicat ?
I like this thread ! Thanks for all

[Fabrice]

'lo JB !
happy to see here again :-)
lets see my site : it's working nice !! ;-)... up now.
see you.

[vins]

Hello Fabrice,
Problem with your method is that between two releases, there some contents which are added or removed in the DataBase... So I think that the probleme with your method is that it compares only the structure of database.
Please tell me what you think.

[Fabrice]

that's the question : is there some datas added in a new field for instance ? can this happen ? so, I must be careful when updating database.

the process is : updating the current (in production) database structure with a brand new one - with NO DATA inside, except demo datas.
bdw the update is very fast : 3-4 seconds - no need to stop the shop.

When I mean structure, I mean also, adding the missing/modified tables. this soft is amazing. my shop is ok up now.

And if some specific datas are added, no problem, it's possible too (as you can see on the screen copy : structure and/or datas). I just have to join the 2 database datas. Have to check some tables (essentialy products) before validating (I can cancel part of sync in the preview), sync, and delete the 3 samples which are always included in the base. With this method, I guess we have a solution.

I will continue like that. If there is a crash, you will know it :-)

[vins]

Hum hum interesting the way you process...
Please can you confirm if I well understand:
1. You install in local a brand new base with the new structure AND datas...
2. You update your production base with this brand new base... so new structure is updated and some datas are added...

Perhaps there is a problem (please confirm) : if some Prestashop datas (ex. hooks name) have been removed in the new base, is your Soft able to remove it also in your production base ?

[Fabrice]

1. yes but not necessary in local. I do it remotely.
2. yes; up now, I only updated the structure. I did not know if datas has to be transfered. I am totaly beginner in sql.
3. yes. the soft is just comparing and proposing the actions. concerning the structuer and/or the datas. I do validate all of them (except datas) this night, and today, everything is still working fine. I took a little risk I know (but have saved the original database and site folder before that to be able to come back to original datas), but with no risk, no answer.

you can test it (free 30 days demo - mac and pc even linux version)- and final coset (about 120$) looks to me "light" in regards of time saved each time.

I didn't had time to check all possibilities. but i guess the soft includes for instance a "reverse" possibility. to change back to previous version. but did not have time to go deap in the soft manual. i discovered this soft 2 years ago. but felt not concerned with mysql. I was looking for soft whih was able to erase a database : and dicovered this possibility. I think we can imagine migrating from svn to svn and to final version with minimum risk. anyway, not more that with a buggy version. that's my opinion.

[Fabrice]

one more thing... ;-)

have a look on these screen copies : it's possible to make a clean data transfer.
couldn't that be faster ?
1. create new install - empty, not an update
2. transfer datas from old database.

could that be so easy ?
I am not able to think about it now - I am at office since 40 hours now - have to sleep a few days... :-)

what is your opinion ? (about the 2 steps process, not about sleeping)

[Jean-Bruno]

Fabrice you should go to skiing ;-)

[Jean-Bruno]

hi Fabrice
IS you upgrade made on the 1.0 or 1.1 version ? Could made a little tutorial in french section to explain how to proceed with the marvellous NAVICAT soft ?

[Fabrice]

Hi JB !
I will try to find time to do it.
Please remember me if I forgot... ;-)

[Jean-Bruno]

You are very very cool Fabrice . I'll remenber you or may be can i call you ?

[Fabrice]

tuto is here