[SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

StudioZ · Post by **StudioZ** » Sat 10. May 2008, 22:44

If anyones' willing to test and confirm.
If it works… let's hope this time the updated files will follow in the distribution.

TESTED. IT WORKS FOR ME!
Although… As usual… Use at your own risks.
Always make a backup of your data first.

My Only Wish now…
Why not use all that sweeping I did and swap the wrong files for these ones?

Here's the 3 updated SQL files for upgrading to the latest 1.3.9 r170 :

19__1.3.4_to_1.3.5.sql

Code: Select all

#####################################################
#
#  PHPWCMS SQL Update
#  
#  Upgrade release 1.3.4 to 1.3.5
#
#####################################################

# ------- Added by StudioZ / -------------------------
DROP TABLE IF EXISTS `phpwcms_categories` ;

CREATE TABLE `phpwcms_categories` (
  `cat_id` int(10) unsigned NOT NULL auto_increment,
  `cat_type` varchar(255) NOT NULL default '',
  `cat_pid` int(11) NOT NULL default '0',
  `cat_status` int(1) NOT NULL default '0',
  `cat_createdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `cat_changedate` datetime NOT NULL default '0000-00-00 00:00:00',
  `cat_name` varchar(255) NOT NULL default '',
  `cat_info` text NOT NULL,
  PRIMARY KEY  (`cat_id`),
  KEY `cat_type` (`cat_type`,`cat_status`),
  KEY `cat_pid` (`cat_pid`)
) ENGINE=MyISAM ;
# ------- / Added by StudioZ -------------------------

# ------- Removed by StudioZ / -------------------------
#  ALTER TABLE `phpwcms_articlecat` ADD `acat_overwrite` VARCHAR( 255 ) NOT NULL ;
#  ALTER TABLE `phpwcms_articlecontent` ADD `acontent_paginate_page` INT( 5 ) UNSIGNED NOT NULL ;
#  ALTER TABLE `phpwcms_articlecontent` ADD `acontent_paginate_title` VARCHAR( 255 ) NOT NULL ;
#  ALTER TABLE `phpwcms_articlecontent` ADD INDEX ( `acontent_paginate_page` ) ;
# ------- / Removed by StudioZ -------------------------

20__1.3.5_to_1.3.5.1.sql

Code: Select all

#####################################################
#
#  PHPWCMS SQL Update
#  
#  Upgrade release 1.3.5 to 1.3.5.1
#
#####################################################

# ------- Added by StudioZ / -------------------------
DROP TABLE IF EXISTS `phpwcms_log` ;
# ------- / Added by StudioZ -------------------------

# ------- Removed by StudioZ / -------------------------
# ALTER TABLE `phpwcms_categories` ADD `cat_pid` INT( 11 ) NOT NULL AFTER `cat_type` ;
# ------- / Removed by StudioZ -------------------------

ALTER TABLE `phpwcms_categories` ADD INDEX ( `cat_pid` ) ;

CREATE TABLE `phpwcms_log` (
  `log_id` int(11) NOT NULL auto_increment,
  `log_type` varchar(255) NOT NULL,
  `log_timestamp` timestamp NOT NULL,
  `log_message` text NOT NULL,
  `log_ip` varchar(50) NOT NULL,
  `log_userid` varchar(255) NOT NULL,
  PRIMARY KEY  (`log_id`)
) TYPE=MyISAM ;

ALTER TABLE `phpwcms_guestbook` CHANGE `guestbook_created` `guestbook_created` INT(11) NOT NULL ;
ALTER TABLE `phpwcms_userlog` CHANGE `logged_change` `logged_change` INT(11) DEFAULT '0' NOT NULL ;
ALTER TABLE `phpwcms_userlog` CHANGE `logged_start` `logged_start` INT(11) DEFAULT '0' NOT NULL ;
ALTER TABLE `phpwcms_articlecat` CHANGE `acat_alias` `acat_alias` VARCHAR(255) NOT NULL ;
ALTER TABLE `phpwcms_articlecat` DROP INDEX `acat_alias`, ADD INDEX `acat_alias` (`acat_alias`);
ALTER TABLE `phpwcms_file` DROP INDEX `f_name`, ADD INDEX `f_name` (`f_name`);
ALTER TABLE `phpwcms_file` DROP INDEX `f_shortinfo`, ADD INDEX `f_shortinfo` (`f_shortinfo`);

21__1.3.5_to_1.5.sql
ATTENTION ! RUN THIS ONE ONLY FROM INSIDE PhpMyAdmin
or else you will get an SQL error

Code: Select all

    #####################################################
    #
    #  PHPWCMS SQL Update
    # 
    #  Upgrade release 1.3.5 to 1.5
    #
    #####################################################

    # 2008-02-24
    ALTER TABLE `phpwcms_articlecat` ADD `acat_archive` INT( 1 ) NOT NULL DEFAULT '0';
    ALTER TABLE `phpwcms_articlecat` ADD INDEX ( `acat_archive` ) ;
    ALTER TABLE `phpwcms_article` ADD `article_archive_status` INT( 1 ) NOT NULL DEFAULT '1';
    ALTER TABLE `phpwcms_article` ADD INDEX ( `article_archive_status` ) ;
    ALTER TABLE `phpwcms_articlecontent` ADD `acontent_category` VARCHAR( 255 ) NOT NULL;

    # 2008-02-25
    ALTER TABLE `phpwcms_file` CHANGE `f_cat` `f_cat` VARCHAR( 255 ) NOT NULL;
    ALTER TABLE `phpwcms_file` CHANGE `f_changed` `f_changed` INT NOT NULL DEFAULT '0';
    ALTER TABLE `phpwcms_file` CHANGE `f_created` `f_created` INT NOT NULL DEFAULT '0';
    ALTER TABLE `phpwcms_file` DROP `f_log`;
    ALTER TABLE `phpwcms_file` CHANGE `f_longinfo` `f_longinfo` TEXT NOT NULL;
    ALTER TABLE `phpwcms_file` ADD `f_copyright` VARCHAR( 255 ) NOT NULL;
    ALTER TABLE `phpwcms_file` ADD `f_tags` VARCHAR( 255 ) NOT NULL;
    ALTER TABLE `phpwcms_file` CHANGE `f_size` `f_size` INT( 15 ) UNSIGNED NOT NULL DEFAULT '0';

    # 2008-03-06
    ALTER TABLE `phpwcms_file` ADD `f_granted` INT NOT NULL DEFAULT '0';
    ALTER TABLE `phpwcms_file` ADD INDEX ( `f_granted` ) ;
    ALTER TABLE `phpwcms_articlecontent` ADD `acontent_granted` INT NOT NULL DEFAULT '0';
    ALTER TABLE `phpwcms_articlecontent` ADD INDEX ( `acontent_granted` ) ;


    # ------- Added by StudioZ / -------------------------
    DROP TABLE IF EXISTS `phpwcms_calendar` ;
    # ------- / Added by StudioZ -------------------------

    # ------- Added by StudioZ / -------------------------
    CREATE TABLE IF NOT EXISTS `phpwcms_calendar` (
      `calendar_id` int(11) NOT NULL auto_increment,
      `calendar_created` datetime NOT NULL default '0000-00-00 00:00:00',
      `calendar_changed` datetime NOT NULL default '0000-00-00 00:00:00',
      `calendar_status` int(1) NOT NULL default '0',
      `calendar_start` datetime NOT NULL default '0000-00-00 00:00:00',
      `calendar_end` datetime NOT NULL default '0000-00-00 00:00:00',
      `calendar_allday` int(1) NOT NULL default '0',
      `calendar_range` int(1) NOT NULL default '0',
      `calendar_range_start` date NOT NULL default '0000-00-00',
      `calendar_range_end` date NOT NULL default '0000-00-00',
      `calendar_title` varchar(255) NOT NULL default '',
      `calendar_where` varchar(255) NOT NULL default '',
      `calendar_teaser` text NOT NULL,
      `calendar_text` mediumtext NOT NULL,
      `calendar_tag` varchar(255) NOT NULL default '',
      `calendar_object` longtext NOT NULL,
      `calendar_refid` int(11) NOT NULL default '0',
      `calendar_lang` varchar(255) NOT NULL,
      PRIMARY KEY  (`calendar_id`),
      KEY `calendar_status` (`calendar_status`),
      KEY `calendar_start` (`calendar_start`),
      KEY `calendar_end` (`calendar_end`),
      KEY `calendar_tag` (`calendar_tag`),
      KEY `calendar_refid` (`calendar_refid`),
      KEY `calendar_range` (`calendar_range`),
      KEY `calendar_lang` (`calendar_lang`)
    ) ENGINE=MyISAM ;
    # ------- / Added by StudioZ -------------------------

    # ------- Moved down by StudioZ / -------------------------
    # 2008-03-03
    # ALTER TABLE `phpwcms_calendar` ADD `calendar_lang` VARCHAR( 255 ) NOT NULL ;
    # ALTER TABLE `phpwcms_calendar` ADD INDEX ( `calendar_lang` ) ;
    # ------- / Moved down by StudioZ  -------------------------

    # 2008-03-09
    # ALTER TABLE `phpwcms_calendar` ADD `calendar_teaser` TEXT NOT NULL AFTER `calendar_where`;

    # 2008-05-09
    CREATE TABLE `phpwcms_content` (
  `cnt_id` int(11) NOT NULL auto_increment,
  `cnt_pid` int(11) NOT NULL default '0',
  `cnt_created` int(11) NOT NULL default '0',
  `cnt_changed` int(11) NOT NULL default '0',
  `cnt_status` int(1) NOT NULL default '0',
  `cnt_type` varchar(255) NOT NULL,
  `cnt_module` varchar(255) NOT NULL,
  `cnt_group` int(11) NOT NULL default '0',
  `cnt_owner` int(11) NOT NULL default '0',
  `cnt_livedate` datetime NOT NULL default '0000-00-00 00:00:00',
  `cnt_killdate` datetime NOT NULL default '0000-00-00 00:00:00',
  `cnt_archive_status` int(11) NOT NULL default '0',
  `cnt_alias` varchar(255) NOT NULL default '',
  `cnt_name` varchar(255) NOT NULL default '',
  `cnt_title` varchar(255) NOT NULL default '',
  `cnt_subtitle` varchar(255) NOT NULL default '',
  `cnt_editor` varchar(255) NOT NULL default '',
  `cnt_place` varchar(255) NOT NULL default '',
  `cnt_teasertext` text NOT NULL,
  `cnt_text` text NOT NULL,
  `cnt_lang` varchar(10) NOT NULL default '',
  `cnt_object` text NOT NULL,
  PRIMARY KEY  (`cnt_id`),
  KEY `cnt_livedate` (`cnt_livedate`),
  KEY `cnt_killdate` (`cnt_killdate`),
  KEY `cnt_module` (`cnt_module`),
  KEY `cnt_type` (`cnt_type`),
  KEY `cnt_group` (`cnt_group`),
  KEY `cnt_owner` (`cnt_owner`),
  KEY `cnt_alias` (`cnt_alias`),
  KEY `cnt_pid` (`cnt_pid`)
) ENGINE=MyISAM ;

 ALTER TABLE `phpwcms_content` ADD `cnt_sort` INT NOT NULL DEFAULT '0' AFTER `cnt_archive_status`;
 ALTER TABLE `phpwcms_content` ADD `cnt_prio` INT NOT NULL DEFAULT '0' AFTER `cnt_sort`;
 ALTER TABLE `phpwcms_content` ADD INDEX ( `cnt_sort` );
 ALTER TABLE `phpwcms_content` ADD INDEX ( `cnt_prio` );

Upgrade from 1.3.3. to the latest, should now go smoothly.
Would appreciate if anyone could confirm.

Thanks in advance.

Yves

Post by **Oliver Georgi** » Sun 11. May 2008, 08:04

Thanks Yves,

I will check to integrate it.

Oliver

StudioZ · Post by **StudioZ** » Mon 12. May 2008, 05:22

Hello Oliver,

Thank you for integrating so quick.
I checked and tested your updated SQL to make sure all was fine.

19__1.3.3_to_1.3.4.sql OK
19__1.3.4_to_1.3.5.sql OK
20__1.3.5_to_1.3.5.1.sql ERROR (Below)
21__1.3.5_to_1.5.sql OK

I get an SQL error when upgrading with 20__1.3.5_to_1.3.5.1.sql
both, at :
/setup/upgrade.php
and through PhpMyAdmin:

Error

SQL query:

ALTER TABLE `phpwcms_file`DROP INDEX `f_name` ,
ADD INDEX `f_name` ( `f_name` ) ALTER TABLE `phpwcms_file` DROP INDEX `f_shortinfo` ,
ADD INDEX `f_shortinfo` ( `f_shortinfo` )

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE `phpwcms_file` DROP INDEX `f_shortinfo`, ADD INDEX `f_shortinfo` (`f' at line 2

Kind Regards,

Yves

StudioZ · Post by **StudioZ** » Mon 12. May 2008, 05:30

Just found the typo :

You had two missing semi-colons [;], at the end of the two last lines :
Corrected code :

20__1.3.5_to_1.3.5.1.sql

Code: Select all

#####################################################
#
#  PHPWCMS SQL Update
#  
#  Upgrade release 1.3.5 to 1.3.5.1
#
#####################################################

DROP TABLE IF EXISTS `phpwcms_log` ;
CREATE TABLE `phpwcms_log` (
  `log_id` int(11) NOT NULL auto_increment,
  `log_type` varchar(255) NOT NULL,
  `log_timestamp` timestamp NOT NULL,
  `log_message` text NOT NULL,
  `log_ip` varchar(50) NOT NULL,
  `log_userid` varchar(255) NOT NULL,
  PRIMARY KEY  (`log_id`)
) TYPE=MyISAM ;

ALTER TABLE `phpwcms_guestbook` CHANGE `guestbook_created` `guestbook_created` INT(11) NOT NULL ;
ALTER TABLE `phpwcms_userlog` CHANGE `logged_change` `logged_change` INT(11) DEFAULT '0' NOT NULL ;
ALTER TABLE `phpwcms_userlog` CHANGE `logged_start` `logged_start` INT(11) DEFAULT '0' NOT NULL ;

ALTER TABLE `phpwcms_articlecat` CHANGE `acat_alias` `acat_alias` VARCHAR(255) NOT NULL ;
ALTER TABLE `phpwcms_articlecat` DROP INDEX `acat_alias`, ADD INDEX `acat_alias` (`acat_alias`);

ALTER TABLE `phpwcms_file` DROP INDEX `f_name`, ADD INDEX `f_name` (`f_name`);
ALTER TABLE `phpwcms_file` DROP INDEX `f_shortinfo`, ADD INDEX `f_shortinfo` (`f_shortinfo`);

Cheers,

Yves

Post by **Oliver Georgi** » Mon 12. May 2008, 07:57

Oh yeah, added and SVN updated

Oliver

baklavoni · Post by **baklavoni** » Thu 12. Jun 2008, 09:27

I have 1.3.3 installed.
How to upgrade to the latest 1.3.9 r182?

What steps do I need to complete?
My hosting is on Windows server with Plesk control panel.

Cipolla · Post by **Cipolla** » Thu 12. Jun 2008, 09:47

baklavoni wrote:I have 1.3.3 installed.
How to upgrade to the latest 1.3.9 r182?

What steps do I need to complete?
My hosting is on Windows server with Plesk control panel.

Don´t be afraid of the update. Just make it similar to the steps shwon in the docu. I did it in the past from 1.3.5 to r179, but the steps from 1.3.3 are the same, i mean only one additional sql update.

Maybe this summary is helpfull:

http://forum.phpwcms.org/viewtopic.php? ... a&start=16

AND make a backup of your installation and the database.

baklavoni · Post by **baklavoni** » Mon 23. Jun 2008, 10:42

Cipolla wrote: Don´t be afraid of the update. Just make it similar to the steps shwon in the docu. I did it in the past from 1.3.5 to r179, but the steps from 1.3.3 are the same, i mean only one additional sql update.

AND make a backup of your installation and the database.

Thanks, will try it

[SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

[SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED

Re: [SQL] Upgrade from 1.3.3 to 1.3.9 r170 -> TESTED