38 replies to this topic

[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:

Quote

Index: db_settings_lite.sql
===================================================================
--- db_settings_lite.sql (revision 370)
+++ db_settings_lite.sql (revision 463)
@@ -42,7 +42,8 @@
(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()),
@@ -101,11 +102,9 @@

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:

Quote

Index: 1.2.0.1.sql
===================================================================
--- 1.2.0.1.sql (revision 370)
+++ 1.2.0.1.sql (revision 463)
@@ -15,12 +15,18 @@
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,
@@ -89,8 +95,8 @@
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;
@@ -108,10 +114,19 @@
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:

Quote

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 :D

[Fabrice]

From 1234852632:

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 ?

[votreprestashop]

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.

[votreprestashop]

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.

[votreprestashop]

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.