[imc-sf-active] script to convert features.cgi dbase to sf-active dbase

PseudoPunk bart at indymedia.org
Fri Mar 7 05:26:10 2003
Previous message: [imc-sf-active] distributed backup code
Next message: [imc-sf-active] script to convert features.cgi dbase to sf-active dbase
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
This is a multi-part message in MIME format.
--------------050503060409060706040505
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: 7bit

Hey Everyone,

Yesterday Zapata and me worked on a script to convert the database of
features.cgi to the appropriate tables of the sf-active database.

I've tested it and it works. You can see an example on
http://sfbelgium.dyndns.org/

I've written a very small manual so if people, who have only basic
skills (like me), need it, it might be possible :-)

The files are in the attachment(s).
convert.sql
images.sql
manual

Greetings,

Bart

-- 

http://wvl.indymedia.org  |     bart at indymedia.org
http://thepits.be         |     bart at thepits.be
http://pseudopunk.be      |     icq: 48682266

gebruik vrije software -- Debian GNU/Linux -- http://debian.org

gpg --keyserver keys.indymedia.org --recv-keys be196251

--------------050503060409060706040505
Content-Type: text/plain;
 name="manual"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="manual"

MANUAL FOR CONVERTING THE FEATURES.CGI DATABASE TO SF-ACTIVE FEATURES DATABASE

1. Information Gathering
2. Creating a script to download pictures
3. download the pictures
4. database conversion



1. INFORMATION GATHERING

- First of all, you'll need 
  - a dump of the features.cgi database
  - a working sf-active site

- import the features.cgi database into a new database called features. The relevant table will be FEATURES. Now you'll have to do some searching to find which SITE_ID (in table FEATURES) is used by your imc.

- Find out which template name you want to use as a default in sf-active. edit convert.sql and change italy_image_left by the one you'll use.

- We use the following defaults for columns that are new. You can change them in convert.sql.
    - modifier_id = '2'
    - creator_id = '2'
    (we choose '2', because '1' is the default one and is normally erased)

    - category_id = '3' (this is by a normal install the category for production)

    - status = 'a' (we archive everything. getting the 5/10 most recent back to 's' is less work then getting all the rest back to the archive :-)

- You don't have to worry about the extra links that are in features.cgi. The script nicely places them at the end of the summary.




2. CREATING A SCRIPT TO DOWNLOAD PICTURES

just type in:
$ mysql -B -N -u user features <images.sql  >test.sh    




3. DOWNLOAD THE PICTURES

- go to the directory you want to download the pictures to. If you choose another directory then URL/images, you'll have to change convert.sql). Then execute test.shin that directory.




4. DATABASE CONVERSION

Make sure there's nothing in the following tables in your sf-active dbase: feature, feature_sequence; they'll be dropped & recreated.

Just one command:
$ mysql -u user -p databasename < convert.sql



--------------050503060409060706040505
Content-Type: text/plain;
 name="images.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="images.sql"

select concat('wget ', IMAGE_URL)
   from FEATURES
   where SITE_ID=5693 and IMAGE_URL<>'';

--------------050503060409060706040505
Content-Type: text/plain;
 name="convert.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="convert.sql"

drop table feature ;
drop table feature_sequence ;

CREATE TABLE feature (
  feature_version_id int(11) NOT NULL auto_increment,
  feature_id int(11) NOT NULL default '0',
  summary blob,
  title1 varchar(200) default NULL,
  title2 varchar(200) default NULL,
  display_date varchar(100) default NULL,
  order_num int(5) default NULL,
  category_id int(5) default NULL,
  template_name varchar(200) NOT NULL default '0',
  creation_date timestamp(14) NOT NULL,
  creator_id int(8) NOT NULL default '0',
  status char(2) default 'c',
  tag varchar(100) default NULL,
  image varchar(100) default NULL,
  version_num int(5) default '1',
  is_current_version int(1) default '1',
  modification_date timestamp(14) NOT NULL,
  modifier_id int(11) default NULL,
  image_link varchar(200) default NULL,
  PRIMARY KEY  (feature_version_id),
  KEY feature_pk (feature_version_id),
  KEY feature_01 (order_num),
  KEY feature_02 (feature_id,is_current_version),
  KEY feature_03 (creation_date),
  KEY feature_04 (category_id,is_current_version,status),
  KEY feature_05 (category_id,status,is_current_version,creation_date),
  KEY feature_06 (creation_date,order_num),
  KEY feature_07 (feature_id),
  KEY feature_08 (version_num),
  KEY feature_09 (feature_id,version_num)
) TYPE=MyISAM;

CREATE TABLE feature_sequence (
  feature_id int(10) NOT NULL auto_increment,
  PRIMARY KEY  (feature_id)
) TYPE=MyISAM;


insert into feature (
  feature_version_id, 
  feature_id, 
  summary, 
  title1, 
  title2, 
  display_date,
  order_num, 
  category_id,
  template_name,
  creation_date,           
  creator_id,              
  status,                  
  tag,                     
  image,                   
  version_num,             
  is_current_version,      
  modification_Date,       
  modifier_id,             
  image_link
  ) 
  
select 
  ID,                                                    -- feature_version_id     
  ID,                                                    -- feature_id             
  concat(
    BODY,
    If(LINK_1<>'' || LINK_2<>'' || LINK_3<>'','<br> [ ',''),
    If(LINK_1<>'', concat('<a href="',LINK_1, '">', LINK_1_TITLE, '</a>'), ''),
    If(LINK_2<>'', concat(' | <a href="',LINK_2, '">', LINK_2_TITLE, '</a>'), ''),
    If(LINK_3<>'', concat(' | <a href="',LINK_3, '">', LINK_3_TITLE, '</a>'), ''),
    If(LINK_1<>'' || LINK_2<>'' || LINK_3<>'',']','')
  ),
                                                         -- summary                
  TITLE,                                                 -- title1                 
  SUB_TITLE,                                             -- title2                 
  date_format(TIMESTAMP, '%d/%m/%Y'),                     -- display_date           
  ID,                                                    -- order_num              
  '3',                                                   -- category_id            
  'italy_image_left',                                    -- template_name          
  TIMESTAMP,                                             -- creation_date          
  2,                -- creator_id                        -- creator_id             
  'a',                                                   -- status                 
  IMAGE_CAPTION,                                         -- tag                    
  IF(
     IMAGE_URL<>'',
     concat(
        '/images/',
        SUBSTRING(IMAGE_URL FROM LENGTH(IMAGE_URL)+2-LOCATE('/', REVERSE(IMAGE_URL)))
     ),
     ''
   ),	            -- needs to be converted             -- image                  
  1,                                                     -- version_num            
  1,                                                     -- is_current_version     
  CURRENT_TIMESTAMP,                                     -- modification_Date      
  2,                -- modified id                       -- modified_id            
  LINK_1                                                 -- image_link             

from features.FEATURES where SITE_ID = '5693' ;


insert into feature_sequence (feature_id) select ID from features.FEATURES where SITE_ID='5693';

--------------050503060409060706040505--
Previous message: [imc-sf-active] distributed backup code
Next message: [imc-sf-active] script to convert features.cgi dbase to sf-active dbase
Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]