Please login or register.

Login with username, password and session length
Advanced search  

News:

Latest sNews - sNews 1.7 - with its own forums - for discussion and user mods.

Pages: [1] 2

Author Topic: [UPGRADE] database 1.4 to 1.5 SQL upgrade  (Read 17061 times)

Mika

  • Hero Member
  • *****
  • Karma: 9
  • Posts: 1377
    • http://www.ni5ni6.com/
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« on: November 23, 2006, 06:48:50 PM »

BACKUP YOUR EXISTING DATABASE FIRST

sql updated:
bugxfixes
- added id modification (primary index was missing in all tables)
- added url field in comments table
- removed time modification in comments table


There are two SQL routines; first one is default/basic/minimal command routine which will upgrade your 1.4 database to version 1.5
Quote
ALTER TABLE articles
MODIFY id int(11) primary key auto_increment,
DROP textlimit,
MODIFY position int(6),
DROP image,
ADD description_meta varchar(255) default NULL,
ADD keywords_meta varchar(255) default NULL;

ALTER TABLE categories
MODIFY id int(11) primary key auto_increment,
MODIFY name varchar(100),
MODIFY description varchar(255),
ADD catorder smallint(6) NOT NULL default '0';

ALTER TABLE comments
MODIFY id int(11) primary key auto_increment,
ADD url varchar(100) NOT NULL,
ADD approved varchar(5) NOT NULL default 'True';

CREATE TABLE settings (
    id int(8) primary key auto_increment,
    name varchar(20) NOT NULL,
    `value` varchar(60) NOT NULL
);

   INSERT INTO settings VALUES (1, 'website_title', 'sNews 1.5');
   INSERT INTO settings VALUES (2, 'home_sef', 'home');
   INSERT INTO settings VALUES (3, 'website_description', 'sNews CMS');
   INSERT INTO settings VALUES (4, 'website_keywords', 'new, site, snews');
   INSERT INTO settings VALUES (5, 'website_email', 'info@mydomain.com');
   INSERT INTO settings VALUES (6, 'contact_subject', 'Contact Form');
   INSERT INTO settings VALUES (7, 'language', 'EN');
   INSERT INTO settings VALUES (8, 'charset', 'UTF-8');
   INSERT INTO settings VALUES (9, 'date_format', 'd.m.Y. H:i');
   INSERT INTO settings VALUES (10, 'article_limit', '3');
   INSERT INTO settings VALUES (11, 'rss_limit', '5');
   INSERT INTO settings VALUES (12, 'display_page', '');
   INSERT INTO settings VALUES (13, 'display_new_on_home', '');
   INSERT INTO settings VALUES (14, 'display_pagination', '');
   INSERT INTO settings VALUES (15, 'num_categories', 'on');
   INSERT INTO settings VALUES (16, 'approve_comments', '');
   INSERT INTO settings VALUES (17, 'comments_order', 'ASC');
   INSERT INTO settings VALUES (18, 'comment_limit', '30');
   INSERT INTO settings VALUES (19, 'word_filter_enable', '');
   INSERT INTO settings VALUES (20, 'word_filter_file', '');
   INSERT INTO settings VALUES (21, 'word_filter_change', '');
   INSERT INTO settings VALUES (22, 'username', 'test');
   INSERT INTO settings VALUES (23, 'password', '098f6bcd4621d373cade4e832627b4f6');
The second one is slightly complicated; it uses prefixes (in case someone need that version), and performs table duplication as a form of backup.

Prefixes used here are examples only and should be changed to fit your database setup:
- "14_" (version 1.4)
- "15_" (version 1.5)
Quote
-- copying 14_articles structure and data to a new 15_articles table
-- old 14_articles table (version 1.4) will be preserved
CREATE TABLE 15_articles SELECT * FROM 14_articles;

-- upgrading 15_articles table to a new 1.5 version
-- textlimit values will be truncated and should be replaced manually
-- by adding [break] tag into article body (via admin panel in sNews)
-- image values will be lost and should be updated (via admin panel in sNews)
-- description_meta and keywords_meta are empty and should be updated as well (via admin panel in sNews)
ALTER TABLE 15_articles
MODIFY id int(11) primary key auto_increment,
DROP textlimit,
MODIFY position int(6),
DROP image,
ADD description_meta varchar(255) default NULL,
ADD keywords_meta varchar(255) default NULL;

-- copying 14_categories structure and data to a new 15_categories table
-- old 14_categories table (version 1.4) will be preserved
CREATE TABLE 15_categories SELECT * FROM 14_categories;

-- upgrading 15_categories table to a new 1.5 version
-- catorder is empty and should be updated (via admin panel in sNews)
ALTER TABLE 15_categories
MODIFY id int(11) primary key auto_increment,
MODIFY name varchar(100),
MODIFY description varchar(255),
ADD catorder smallint(6) NOT NULL default '0';

-- copying 14_comments structure and data to a new 15_comments table
-- old 14_comments table (version 1.4) will be preserved
CREATE TABLE 15_comments SELECT * FROM 14_comments;

-- upgrading 15_comments table to a new 1.5 version
ALTER TABLE 15_comments
MODIFY id int(11) primary key auto_increment,
ADD url varchar(100) NOT NULL,
ADD approved varchar(5) NOT NULL default 'True';

-- creating new 15_settings table
-- user: test
-- password: test
CREATE TABLE 15_settings (
    id int(8) primary key auto_increment,
    name varchar(20) NOT NULL,
    `value` varchar(60) NOT NULL
);

   INSERT INTO 15_settings VALUES (1, 'website_title', 'sNews 1.5');
   INSERT INTO 15_settings VALUES (2, 'home_sef', 'home');
   INSERT INTO 15_settings VALUES (3, 'website_description', 'sNews CMS');
   INSERT INTO 15_settings VALUES (4, 'website_keywords', 'new, site, snews');
   INSERT INTO 15_settings VALUES (5, 'website_email', 'info@mydomain.com');
   INSERT INTO 15_settings VALUES (6, 'contact_subject', 'Contact Form');
   INSERT INTO 15_settings VALUES (7, 'language', 'EN');
   INSERT INTO 15_settings VALUES (8, 'charset', 'UTF-8');
   INSERT INTO 15_settings VALUES (9, 'date_format', 'd.m.Y. H:i');
   INSERT INTO 15_settings VALUES (10, 'article_limit', '3');
   INSERT INTO 15_settings VALUES (11, 'rss_limit', '5');
   INSERT INTO 15_settings VALUES (12, 'display_page', '');
   INSERT INTO 15_settings VALUES (13, 'display_15_on_home', '');
   INSERT INTO 15_settings VALUES (14, 'display_pagination', '');
   INSERT INTO 15_settings VALUES (15, 'num_categories', 'on');
   INSERT INTO 15_settings VALUES (16, 'approve_comments', '');
   INSERT INTO 15_settings VALUES (17, 'comments_order', 'ASC');
   INSERT INTO 15_settings VALUES (18, 'comment_limit', '30');
   INSERT INTO 15_settings VALUES (19, 'word_filter_enable', '');
   INSERT INTO 15_settings VALUES (20, 'word_filter_file', '');
   INSERT INTO 15_settings VALUES (21, 'word_filter_change', '');
   INSERT INTO 15_settings VALUES (22, 'username', 'test');
   INSERT INTO 15_settings VALUES (23, 'password', '098f6bcd4621d373cade4e832627b4f6');
choose the code that fit your needs best and run it in your phpmyadmin sql window.

IMPORTANT: backup, check and re-check again all the steps before you proceed - I cannot be held responsible for any kind of damage caused using this code.
Logged
http://www.ni5ni6.com/ - Tutorials, Mods and How-To's about sNews CMS
sNews 1.6 Developers Edition - commented sNews 1.6 version

Tehy

  • Jr. Member
  • **
  • Karma: 0
  • Posts: 65
    • http://www.sefanatics.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #1 on: November 23, 2006, 07:52:34 PM »

So then I just upload the snews.php file to my server and everything should work fine or do I need to anything more that just the update? I guess I need to chance thes values to suite for my site:

INSERT INTO settings VALUES (1, 'website_title', 'sNews 1.5');
INSERT INTO settings VALUES (2, 'home_sef', 'home');

and so on... am I right?

Patric Ahlqvist

  • Nobodys perfect, but Im pretty effing close
  • ULTIMATE member
  • ******
  • Karma: 65
  • Posts: 4867
  • I'm a self-made man and worships my creator.
    • p-ahlqvist.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #2 on: November 24, 2006, 07:39:07 AM »

Well, yes... Tehy, those settings and everything else in the settings area but not password and username I think... Better safe than sorry, I changed everything but those things to my liking and then imported the file, and WHUUUUSH - Updated database... Works great, after that there will prolly be some CSS remake/fiddudeling for you to take care of, but database is update.

Mika has made a really nice script here, works wonders. Nice, kitten, nice... *said in baby voice* - Soooo, goooood, Miiiiiiika, you're soooooo gooooood, yeeeeeees youuuuuu are :lol:


Patric.
Logged
"It's only dead fish that goes with the flow... "
Updated

Tehy

  • Jr. Member
  • **
  • Karma: 0
  • Posts: 65
    • http://www.sefanatics.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #3 on: November 24, 2006, 08:31:50 AM »

I probably did something wrong because this didn't work :( My site didn't work right anymore.... I just restored my backup, so now eveything works now...

Patric Ahlqvist

  • Nobodys perfect, but Im pretty effing close
  • ULTIMATE member
  • ******
  • Karma: 65
  • Posts: 4867
  • I'm a self-made man and worships my creator.
    • p-ahlqvist.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #4 on: November 24, 2006, 09:15:15 AM »

Mhm, yes I figure you are doing something wrong here. Mika have tested this on his server lots, and I had no problem at all.

Are you using a prefix on your current database ? If you do you need to get every prefix change right in the second SQL upgrade script I'll try and give you a hint: Do change every occurance of the RED YOUR PREFIX_ to your current prefix_ . This will make a copy of your current database with a prefix, it will then change the prefix on that copy, and you'll have a duplicate but with another prefix:

Quote
-- copying YOUR PREFIX_articles structure and data to a new 15_articles table
-- old YOUR PREFIX_articles table (version 1.4) will be preserved
CREATE TABLE 15_articles SELECT * FROM YOUR PREFIX_articles;

-- upgrading 15_articles table to a new 1.5 version
-- textlimit values will be truncated and should be replaced manually
-- by adding [break] tag into article body (via admin panel in sNews)
-- image values will be lost and should be updated (via admin panel in sNews)
-- description_meta and keywords_meta are empty and should be updated as well (via admin panel in sNews)
ALTER TABLE 15_articles
DROP textlimit,
MODIFY position int(6),
DROP image,
ADD description_meta varchar(255) default NULL,
ADD keywords_meta varchar(255) default NULL;

-- copying YOUR PREFIX_categories structure and data to a new 15_categories table
-- old YOUR PREFIX_categories table (version 1.4) will be preserved
CREATE TABLE 15_categories SELECT * FROM YOUR PREFIX_categories;

-- upgrading 15_categories table to a new 1.5 version
-- catorder is empty and should be updated (via admin panel in sNews)
ALTER TABLE 15_categories
MODIFY name varchar(100),
MODIFY description varchar(255),
ADD catorder smallint(6) NOT NULL default '0';

-- copying YOUR PREFIX_comments structure and data to a new 15_comments table
-- old YOUR PREFIX_comments table (version 1.4) will be preserved
CREATE TABLE 15_comments SELECT * FROM YOUR PREFIX_comments;

-- upgrading 15_comments table to a new 1.5 version
ALTER TABLE 15_comments
MODIFY time datetime NOT NULL default '0000-00-00 00:00:00',
ADD approved varchar(5) NOT NULL default 'True';

-- creating new 15_settings table
-- user: test
-- password: test
CREATE TABLE 15_settings (
    id int(8) primary key auto_increment,
    name varchar(20) NOT NULL,
    `value` varchar(60) NOT NULL
);

   INSERT INTO 15_settings VALUES (1, 'website_title', 'sNews 1.5');
   INSERT INTO 15_settings VALUES (2, 'home_sef', 'home');
   INSERT INTO 15_settings VALUES (3, 'website_description', 'sNews CMS');
   INSERT INTO 15_settings VALUES (4, 'website_keywords', 'new, site, snews');
   INSERT INTO 15_settings VALUES (5, 'website_email', 'info@mydomain.com');
   INSERT INTO 15_settings VALUES (6, 'contact_subject', 'Contact Form');
   INSERT INTO 15_settings VALUES (7, 'language', 'EN');
   INSERT INTO 15_settings VALUES (8, 'charset', 'UTF-8');
   INSERT INTO 15_settings VALUES (9, 'date_format', 'd.m.Y. H:i');
   INSERT INTO 15_settings VALUES (10, 'article_limit', '3');
   INSERT INTO 15_settings VALUES (11, 'rss_limit', '5');
   INSERT INTO 15_settings VALUES (12, 'display_page', '');
   INSERT INTO 15_settings VALUES (13, 'display_new_on_home', '');
   INSERT INTO 15_settings VALUES (14, 'display_pagination', '');
   INSERT INTO 15_settings VALUES (15, 'num_categories', 'on');
   INSERT INTO 15_settings VALUES (16, 'approve_comments', '');
   INSERT INTO 15_settings VALUES (17, 'comments_order', 'ASC');
   INSERT INTO 15_settings VALUES (18, 'comment_limit', '30');
   INSERT INTO 15_settings VALUES (19, 'word_filter_enable', '');
   INSERT INTO 15_settings VALUES (20, 'word_filter_file', '');
   INSERT INTO 15_settings VALUES (21, 'word_filter_change', '');
   INSERT INTO 15_settings VALUES (22, 'username', 'test');
   INSERT INTO 15_settings VALUES (23, 'password', '098f6bcd4621d373cade4e832627b4f6');
Logged
"It's only dead fish that goes with the flow... "
Updated

Patric Ahlqvist

  • Nobodys perfect, but Im pretty effing close
  • ULTIMATE member
  • ******
  • Karma: 65
  • Posts: 4867
  • I'm a self-made man and worships my creator.
    • p-ahlqvist.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #5 on: November 24, 2006, 11:06:57 AM »

There is however somethings odd going on here... I'm no DB expert, but the categories and comments are behaving odd. Might be about adding a primary key or something. I've mailed Mika and will get an answer when he's back, so if there is something wrong (and not just me :lol:) it will be corrected here later...
Logged
"It's only dead fish that goes with the flow... "
Updated

Mika

  • Hero Member
  • *****
  • Karma: 9
  • Posts: 1377
    • http://www.ni5ni6.com/
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #6 on: November 24, 2006, 04:59:10 PM »

sql code is updated with bugfixes (see first post)
Logged
http://www.ni5ni6.com/ - Tutorials, Mods and How-To's about sNews CMS
sNews 1.6 Developers Edition - commented sNews 1.6 version

BBBB

  • Newbie
  • *
  • Karma: 0
  • Posts: 32
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #7 on: November 25, 2006, 01:25:40 AM »

Just tested part 1 of the sql!
got this from phpmyadmin
ALTER TABLE articles MODIFY id int( 11 ) PRIMARY KEY AUTO_INCREMENT ,
DROP textlimit,
MODIFY position int( 6 ) ,
DROP image,
ADD description_meta varchar( 255 ) default NULL ,
ADD keywords_meta varchar( 255 ) default NULL ;

MySQL say:  

#1068 - Multiple primary key defined
Logged

Mika

  • Hero Member
  • *****
  • Karma: 9
  • Posts: 1377
    • http://www.ni5ni6.com/
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #8 on: November 25, 2006, 06:22:21 AM »

try to comment that line out
Quote
ALTER TABLE articles
-- some versions of mysql* don't support this method of primary key/index setup very well
-- MODIFY id int( 11 ) PRIMARY KEY AUTO_INCREMENT,

DROP textlimit,
MODIFY position int( 6 ),
DROP image,
ADD description_meta varchar( 255 ) default NULL ,
ADD keywords_meta varchar( 255 ) default NULL;
and see what happens.

another option would be using these commands:
DROP PRIMARY KEY id (removing index)
and recreating another one
ADD PRIMARY KEY id (adding index)
on the same column

but I'll be able to tell you more about it when you find out what mysql version do you use

EDIT: more info on altering mysql tables can be found here: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
________________
* I've tested it on
- Server versions: 4.1.21-standard and 5.0.24a-community-nt
- Client versions: 4.1.10 and 5.0.24a
- phpMyAdmin - 2.9.0.2
 and in some cases upgrade routine had no affect on primary key
Logged
http://www.ni5ni6.com/ - Tutorials, Mods and How-To's about sNews CMS
sNews 1.6 Developers Edition - commented sNews 1.6 version

Tehy

  • Jr. Member
  • **
  • Karma: 0
  • Posts: 65
    • http://www.sefanatics.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #9 on: November 25, 2006, 06:40:19 AM »

Ok. I'm really don't know anything about databases. I don't even unerstand what is the difference between those two updates :P

I tried to use the first one... Update went fine but the site didn't work right or it did work but there was some big problems...

If someone could make more detailed updating instructions I would love it :)

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6019
  • Semantically Challenged
    • snews.ca
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #10 on: November 25, 2006, 03:30:00 PM »

De-mystifying the sNews Database
I have not tried to update a 1.4 database to 1.5 using Mika's updater file (above). Most of my sNews 1.4 databases are still quite small and... since they are all running fine as they are... I see no reason to upgrade any of them to the 1.5 version. However, this will help you understand what's going on here in layman's terms.

To give you an idea of what the differences are... a 1.4 dbase has 3 tables in it - articles, categories and comments - and each table has rows in which information is stored. A 1.5 dbase differs in that it has 4 tables in it - articles, categories, comments and settings. The 4th table - settings - allows you to define, change and save certain settings without having to open the snews.php file and change them manually as we used to do in sNews 1.4. There are differences within the same-named tables too... changes made to store different information in the new ones than what was stored in the earlier ones.

The idea behine Mika's file is to change the existing 1.4 tables into 1.5 tables and add the new 4th table for settings. To make the changes, the file is designed to alter the existing tables by removing rows no longer needed and adding the rows needed to store the information required in 1.5. So... once you run the upgrade file, the dbase will no longer function for a 1.4 site... only for a 1.5 site.

When I want to upgrade an existing sNews 1.4 site to run as a 1.5 site, I make a new project folder for it and I put a complete 1.5 package (all files and folders) into it. I then re-name the index.php file as XX_index.php so that the snews engine no longer recognizes it... then I copy the index.php file from the site I am upgrading into the same folder. I then create a NEW dbase for it. And then... I convert/modify the index.php file by adding in the 1.5 php function statements where required so it will run with the 1.5 file-set. When... and only when... I have fully tested it to ensure it is working properly... I will then attempt to update the database.

By following these steps, you still have your 1.4 dbase with your existing data in place and untouched. Before using the upgrader file, it is a good idea to make a back-up .sql file of the 1.4 dbase and store it in your dbase back-up directory on your computer... so you can always clone another to start over when you have problems.
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU

Tehy

  • Jr. Member
  • **
  • Karma: 0
  • Posts: 65
    • http://www.sefanatics.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #11 on: November 25, 2006, 06:21:35 PM »

Thanks keyrocks! That cleared things up for me :)

Vorsoron

  • Newbie
  • *
  • Karma: 0
  • Posts: 25
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #12 on: November 30, 2006, 06:29:16 PM »

I got this: Error

SQL query:

ALTER TABLE articles MODIFY id int( 11 ) PRIMARY KEY AUTO_INCREMENT ,
DROP textlimit,
MODIFY position int( 6 ) ,
DROP image,
ADD description_meta varchar( 255 ) default NULL ,
ADD keywords_meta varchar( 255 ) default NULL ;

MySQL said: Documentation
#1091 - Can't DROP 'textlimit'; check that column/key exists

I have phpMyAdmin 2.8.2.4 with MySQL client version: 5.0.16
Logged

Patric Ahlqvist

  • Nobodys perfect, but Im pretty effing close
  • ULTIMATE member
  • ******
  • Karma: 65
  • Posts: 4867
  • I'm a self-made man and worships my creator.
    • p-ahlqvist.com
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #13 on: November 30, 2006, 06:42:09 PM »

Oooookay:

I don't know if this is due to my upgrade or not, I think not as Brauck also experiences this although on localhost, my problem is online...

A possible bug, solved or not, in version 1.5:

When creating a category, and then entering an article in that, in order to make it visible to viewers, it doesn't show anything. Through archives and/or "rescent articles" both B-man and I can view said article, but not by clicking the category...

You can view my example here (the article):
http://p-ahlqvist.com/next/css/blahblahblah/
The category:
http://p-ahlqvist.com/next/css/
Logged
"It's only dead fish that goes with the flow... "
Updated

Keyrocks

  • Doug
  • ULTIMATE member
  • ******
  • Karma: 449
  • Posts: 6019
  • Semantically Challenged
    • snews.ca
[UPGRADE] database 1.4 to 1.5 SQL upgrade
« Reply #14 on: November 30, 2006, 10:57:22 PM »

So let's decipher what this section of the sql upgrade script is trying to do.
Quote
ALTER TABLE articles
MODIFY id int( 11 ) PRIMARY KEY AUTO_INCREMENT ,
DROP textlimit,
MODIFY position int( 6 ) ,
DROP image,
ADD description_meta varchar( 255 ) default NULL ,
ADD keywords_meta varchar( 255 ) default NULL ;

MySQL said: Documentation
#1091 - Can't DROP 'textlimit'; check that column/key exists
First line says to alter the articles table.
Then to modify the id column...
Then drop the textlimit column from the table... because we don't need it in 1.5.

The last line basically says that phpMyAdmin was not able to drop the textlimit column from the table, and suggests that you check that column in your database (using phpMyAdmin) to be sure that this column exists in the table that you are trying to update.

So - do that - check and see what's there.
Logged
Do it now... later may not come.
-------------------------------------------------------------------------------------------------
sNews 1.6 MESU | sNews 1.6 MEMU
Pages: [1] 2