root/trunk/gregarius/schema.php

<?php
###############################################################################
# Gregarius - A PHP based RSS aggregator.
# Copyright (C) 2003 - 2006 Marco Bonetti
#
###############################################################################
# This program is free software and open source software; you can redistribute
# it and/or modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of the License,
# or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
# FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License for
# more details.
#
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA  or visit
# http://www.gnu.org/licenses/gpl.html
#
###############################################################################
# E-mail:      mbonetti at gmail dot com
# Web page:    http://gregarius.net/
#
###############################################################################

define('DBSTRUCT', dirname(__FILE__) . '/dbstruct.sql');

require_once('util.php');
require_once('cls/l10n.php');
/**
 * Checks the db schema for the for all required tables, adds those which are missing.
 * Returns the number of added tables;
 */ 
function checkSchema() {
    
    $missing_tables = array();
    $actual_tables=array();
    $expected_tables = getExpectedTables();
    
    $rs = rss_query( "show tables", true, true );
    while(list($tbl) = rss_fetch_row($rs)) {
        $actual_tables[]=$tbl;
    }
    
    foreach ($expected_tables as $base => $tbl) {
        $exists = array_search($tbl,$actual_tables);
        if ($exists === FALSE || $exists === NULL) {
            $missing_tables[]=$base;
        }
    }
    
    $updated  = 0;
    if (count($missing_tables) > 0) {
        $msg = (count($actual_tables)?"Updating":"Creating")
            .' your database schema! This should be a one-time operation,'
            .' if you see this message over and over again please import your database schema'
            .' manually.';
        rss_error($msg, RSS_ERROR_WARNING);

        foreach($missing_tables as $table) {
            $updated += call_user_func("_init_$table"); 
        }
        
        if ($updated == count($missing_tables)) {
            rss_error(__("Successfully created $updated of $updated database tables!"), RSS_ERROR_NOTICE);
        } else {
            rss_error(
                (count($missing_tables) - $updated) . " out of "
            . count($missing_tables) ." tables could not be created!",RSS_ERROR_ERROR);
        }
    }
    
    if ($updated) {
        rss_invalidate_cache();
    }
    return $updated;
}

function getExpectedTables() {
$expected_tables = array (
        "channels" => trim(getTable("channels")),
        "config" => trim(getTable("config")),
        "folders" => trim(getTable("folders")),
        "item" => trim(getTable("item")),
        "metatag" => trim(getTable("metatag")),
        "tag" => trim(getTable("tag")),
        "rating" => trim(getTable("rating")),
        "cache" => trim(getTable("cache")),
        "users" => trim(getTable("users")),        
        "dashboard" => trim(getTable("dashboard")),
        "properties" => trim(getTable("properties")),

    );
    return $expected_tables;
}
function rss_query_wrapper($query, $dieOnError=true, $preventRecursion=false) {
    if (defined('DUMP_SCHEMA')) {
        global $out;
        $out .= ($query . ";\n");
    } else {
        rss_query(trim($query),$dieOnError,$preventRecursion);
    }
}

/**
 * this function handles specific schema updates that occurred 
 * during version updates.
 *
 * @return the number of updated tables
 */
function checkSchemaColumns($column) {
    $updated = 0;
    switch($column) {
        case 'c.mode':
        case 'mode':
            // default feed mode, added in 0.4.1
            rss_query('alter table ' .getTable('channels') .' add column mode int(16) not null default 1');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error("updated schema for table " . getTable('channels'), RSS_ERROR_NOTICE);
            } else {
                rss_error("Failed updating schema for table " . getTable('channels')
                .": " . rss_sql_error_message(), RSS_ERROR_ERROR
                );
            }
        break;
        case 'c.itemsincache':
        case 'itemsincache':
            // date feed was last refreshed, added in 0.5.3
            rss_query('alter table ' .getTable('channels') .' add column itemsincache text null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error("updated schema for table " . getTable('channels'), RSS_ERROR_NOTICE);
            } else {
                rss_error("Failed updating schema for table " . getTable('channels')
                .": " . rss_sql_error_message(), RSS_ERROR_ERROR
                );
            }
        break;
        case 'c.daterefreshed':
        case 'daterefreshed':
            // date feed was last refreshed, added in 0.5.3
            rss_query('alter table ' .getTable('channels') .' add column daterefreshed datetime null default 1');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error("updated schema for table " . getTable('channels'), RSS_ERROR_NOTICE);
            } else {
                rss_error("Failed updating schema for table " . getTable('channels')
                .": " . rss_sql_error_message(), RSS_ERROR_ERROR
                );
            }
        // break; - fallthrough allowed on purpose because these are added at the same time
        case 'c.etag':
        case 'etag':
            // etag of the feed, (from HTTP header) added in 0.5.3
            rss_query('alter table ' .getTable('channels') .' add column etag varchar(255) default null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error("updated schema for table " . getTable('channels'), RSS_ERROR_NOTICE);
            } else {
                rss_error("Failed updating schema for table " . getTable('channels')
                .": " . rss_sql_error_message(), RSS_ERROR_ERROR
                );
            }
        // break; - fallthrough allowed on purpose because these are added at the same time
        case 'c.lastmodified':
        case 'lastmodified':
            // last modified code returned by the feed (from HTTP header), added in 0.5.3
            rss_query('alter table ' .getTable('channels') .' add column lastmodified varchar(255) default null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error("updated schema for table " . getTable('channels'), RSS_ERROR_NOTICE);
            } else {
                rss_error("Failed updating schema for table " . getTable('channels')
                .": " . rss_sql_error_message(), RSS_ERROR_ERROR
                );
            }
        break;
        case 'i.author':
        case 'author':
            // item's author
            rss_query('alter table ' . getTable('item') . ' add column author varchar(255) null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error('updated schema for table ' . getTable('item'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('item') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        break;
        
        case 'm.tdate':
        case 'tdate':
            // tag date
            rss_query('alter table ' . getTable('metatag') . ' add column tdate datetime null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                rss_query('update ' . getTable('metatag') . ' set tdate=now()');
                $updated++;
                rss_error('updated schema for table ' . getTable('metatag'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('metatag') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        break;
        case 'i.enclosure':
        case 'enclosure':
            // enclosure for an item
            rss_query('alter table ' . getTable('item') . ' add column enclosure varchar(255) null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error('updated schema for table ' . getTable('item'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('item') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        break;
        
        case 'userips':
        case 'i.userips':
        // users.userips: list of valid IP subnets the user has logged in from
        rss_query('alter table ' . getTable('users') . ' add column userips text default \'\'');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error('updated schema for table ' . getTable('users'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('users') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        break;
        
        
        case 'i.md5sum':
        case 'md5sum':
            // md5check on an item - added in 0.5.3
            rss_query('alter table ' . getTable('item') . ' add column md5sum varchar(32) null');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error('updated schema for table ' . getTable('item'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('item') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        // break; - fallthrough allowed on purpose because these are added at the same time
        case 'i.guid':
        case 'guid':
            // guid of an item - added in 0.5.3
            rss_query('alter table ' . getTable('item') . ' add column guid text null');
            rss_query('alter table ' . getTable('item') . ' add index `guid` (`guid`(10))');
            if (rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
                $updated++;
                rss_error('updated schema for table ' . getTable('item'), RSS_ERROR_NOTICE);
            } else {
                rss_error('Failed updating schema for table ' . getTable('item') . ': '
                    . rss_sql_error_message(), RSS_ERROR_ERROR);
            }
        
        break;
    }
    return $updated;
}

///////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////

function _init_channels() {
    $table = getTable('channels');
    rss_query_wrapper ('DROP TABLE IF EXISTS ' . $table, true, true);
    $sql_create = str_replace('__table__',$table, <<< _SQL_
        CREATE TABLE __table__ (
            id bigint(11) NOT NULL auto_increment,
              title varchar(255) NOT NULL default '',
              url varchar(255) NOT NULL default '',
              siteurl varchar(255) default NULL,
              parent tinyint(4) default '0',
              descr varchar(255) default NULL,
              dateadded datetime default NULL,
            daterefreshed datetime default NULL,
            refreshinterval int(16) NOT NULL default '60',
            itemsincache text default NULL,
            etag varchar(255) default NULL,
            lastmodified varchar(255) default NULL,
              icon varchar(255) default NULL,
              position int(11) NOT NULL default '0',
            mode int(16) NOT NULL default '1',
              PRIMARY KEY  (id),
            KEY url (url)
        ) TYPE=MyISAM;    
_SQL_
);

    rss_query_wrapper($sql_create, false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The ' . $table . 'table doesn\'t exist and I couldn\'t create it! Please create it manually.', RSS_ERROR_ERROR);
        return 0;
    } else {
        return 1;
    }
}
///////////////////////////////////////////////////////////////////////////////

function _init_dashboard() {
    $table = getTable('dashboard');
    rss_query_wrapper ('DROP TABLE IF EXISTS ' . $table, true, true);
    $sql_create = str_replace('__table__',$table, <<< _SQL_
        CREATE TABLE __table__ (
            id bigint(11) NOT NULL auto_increment,
              title text NOT NULL default '',
              url text NOT NULL default '',
              position tinyint(1) NOT NULL default 0,
              obj text not NULL default '',
              daterefreshed datetime default NULL,
              itemcount tinyint(1) NOT NULL default 3,
              PRIMARY KEY  (id)
        ) TYPE=MyISAM;    
_SQL_
);

    rss_query_wrapper($sql_create, false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The ' . $table . 'table doesn\'t exist and I couldn\'t create it! Please create it manually.', RSS_ERROR_ERROR);
        return 0;
    }
    
    
    $baseData = array(
        array ('Latest Gregarius News','http://devlog.gregarius.net/feed/?db=',0, 3),
        array ('Latest Plugins','http://plugins.gregarius.net/rss.php?db=',1, 5),
        array ('Latest Themes','http://themes.gregarius.net/rss.php?db=',1, 5),
        array ('Latest Forum posts','http://forums.gregarius.net/feeds/?Type=rss2&db=',1, 5),
//        array ('Technorati','http://www.technorati.com/watchlists/rss.html?wid=59610&db=',1, 5)
    );

    foreach ($baseData as $feed) {
        list($title,$url,$pos, $cnt) = $feed;
        rss_query_wrapper (
            "INSERT INTO $table (title, url, position, obj, daterefreshed, itemcount) VALUES "
            ." ('$title', '$url', '$pos', '', null, $cnt)"    , false, true);    
        if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
            rss_error('The '  . $table .  ' table was created successfully, but I couldn\'t insert the default values. Please do so manually!', RSS_ERROR_ERROR);
            return 0;
        }    
    }
    
    return 1;
    
    
}


///////////////////////////////////////////////////////////////////////////////

function _init_folders() {
    $table = getTable('folders');
    rss_query_wrapper ('DROP TABLE IF EXISTS ' . $table, true, true);
    $sql_create = str_replace('__table__',$table, <<< _SQL_
        CREATE TABLE __table__ (
          id tinyint(11) NOT NULL auto_increment,
          name varchar(127) NOT NULL default '',
          position int(11) NOT NULL default '0',    
          PRIMARY KEY  (id),
          UNIQUE KEY name (name)
        ) TYPE=MyISAM;    
_SQL_
);

    rss_query_wrapper($sql_create, false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The ' . $table . 'table doesn\'t exist and I couldn\'t create it! Please create it manually.', RSS_ERROR_ERROR);
        return 0;
    }
    
    
    rss_query_wrapper ("INSERT INTO $table (id,name) VALUES (0,'')", false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The '  . $table .  ' table was created successfully, but I couldn\'t insert the default values. Please do so manually!', RSS_ERROR_ERROR);
        return 0;
    }
    rss_query_wrapper ("update $table set id=0 where id=1", false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The '  . $table .  ' table was created successfully, but I couldn\'t insert the default values. Please do so manually!', RSS_ERROR_ERROR);
        return 0;
    }

    return 1;
}

///////////////////////////////////////////////////////////////////////////////

/** Config table */
function _init_config() {
    $cfg_table = getTable('config');
    
    rss_query_wrapper ('DROP TABLE IF EXISTS ' . $cfg_table, true, true);
    
    $sql_create = str_replace('__config__',$cfg_table, <<< _SQL_
        CREATE TABLE __config__ (
                 key_ varchar(127) NOT NULL default '',
                 value_ text NOT NULL,
                 default_ text NOT NULL,
                 type_ enum('string','num','boolean','array','enum') NOT NULL default 'string',
                 desc_ text,
                 export_ varchar(127) default NULL,
                 PRIMARY KEY  (key_)
                 ) TYPE=MyISAM;
_SQL_
);

    rss_query_wrapper($sql_create, false, true);
    if (!rss_is_sql_error(RSS_SQL_ERROR_NO_ERROR)) {
        rss_error('The ' .getTable('config') . 'table doesn\'t exist and I couldn\'t create it! Please create it manually.', RSS_ERROR_ERROR);
        return 0;
    }

    
    return (setDefaults(null)?1:0);
}


function setDefaults($key) {
      rss_error('inserting some default config values...', RSS_ERROR_NOTICE);
    $defaults = array (
        "rss.output.encoding"        =>        array("UTF-8","UTF-8","string","Output encoding for the PHP XML parser.","MAGPIE_OUTPUT_ENCODING"),
        "rss.output.itemsinchannelview"=>array("10","10","num","Number of read items shown on for a single channel.",NULL),
        "rss.output.showfavicons"    =>        array("true","true","boolean","Display the favicon for the channels that have one. Due to a IE bug, some icons do not render correctly. You can either change the URL to the icon in the admin screen, or turn the display of favicons off globally here.",NULL),
        "rss.output.usemodrewrite"    =>        array("true","true","boolean","Make use of apache's mod_rewrite module to return sexy urls. Turn this off if your host doesn't allow you to change this apache setting.",NULL),
        "rss.config.dateformat"        =>        array("F jS, Y, g:ia T","F jS, Y, g:ia T","string","Format to use when displaying dates. See here for help on the format: http://php.net/date Note that direct access to a given feed's month and day archives more or less depends on the fact that this date format contains the  \"F\" (Month) and \"jS\" (day) elements in this form. So feel free to change the order of the elements, but better leave those two tokens in :)",NULL),
        "rss.meta.debug"            =>        array("false","false","boolean"," When in debug mode some extra debug info is shown and the error reporting is a bit more verbose.",NULL),
        "rss.output.compression"    =>        array("true","true","boolean","This variable turns output compression on and off. Output compression is handled by most browsers.",NULL),
        "rss.output.channelcollapse"=>    array("true","true","boolean","Allow collapsing of channels on the main page. ",NULL),
        "rss.output.channelcollapsedefault"=>    array("false","false","boolean","Collapse the channels on the main page by default",NULL),
        "rss.output.usepermalinks"    =>        array("true","true","boolean","Display a permalink icon and allow linking a given item directly.",NULL),
        "rss.config.markreadonupdate"=>    array("false","false","boolean","Mark all old unread feeds as read when updating if new unread feeds are found.",NULL),
        "rss.output.lang"            =>        array("en_US,zh_CN,de,da,es,fr,he,it,ja,pt_BR,pt,ru,sv,0","en_US,zh_CN,de,da,es,fr,he,it,ja,pt_BR,pt,ru,sv,0","enum","Language pack to use.",NULL),
        "rss.output.lang.force"        =>        array("false","false",'boolean',"When false, Gregarius will negotiate the display language with the browser and will fall back to the language defined in rss.output.lang if the negotiation fails. When true, Gregarius won't negotiate and will always use the language defined in rss.output.lang.",NULL),
        "rss.config.absoluteordering"=>    array("true","true","boolean","Allow feeds and folders to be ordered by their order in the admin section. If this option is set to false, channels and folders will be organized alphabetically by their titles.",NULL),
        "rss.config.robotsmeta"        =>        array("noindex,follow","noindex,follow","string","How should spiders crawl us? (see http://www.robotstxt.org/wc/meta-user.html for more info).",NULL),
        "rss.config.serverpush"        =>        array("true","true","boolean","Use the server push method when updating your feeds in the browser. The browsers that support this (Mozilla and Opera) will be autodetected. Turn this option off if you do not use one of these browsers or if you would like to use the Ajax update method",NULL),
        "rss.config.refreshafter"    =>        array("45","45","num","If this option is set the feeds will be updated after keeping the browser open for x minutes. Please respect the feed providers by not setting this value to anything lower than thirty minutes. Set this variable to 0 turn this option off.",NULL),
        "rss.input.allowed"            =>        array('a:21:{s:1:"a";a:2:{s:4:"href";i:1;s:5:"title";i:1;}s:1:"b";a:0:{}s:10:"blockquote";a:0:{}s:2:"br";a:0:{}s:4:"code";a:0:{}s:3:"del";a:0:{}s:2:"em";a:0:{}s:1:"i";a:0:{}s:3:"img";a:2:{s:3:"src";i:1;s:3:"alt";i:1;}s:3:"ins";a:0:{}s:2:"li";a:0:{}s:2:"ol";a:0:{}s:1:"p";a:0:{}s:3:"pre";a:0:{}s:3:"sup";a:0:{}s:5:"table";a:0:{}s:2:"td";a:0:{}s:2:"th";a:0:{}s:2:"tr";a:0:{}s:2:"tt";a:0:{}s:2:"ul";a:0:{}}','a:21:{s:1:"a";a:2:{s:4:"href";i:1;s:5:"title";i:1;}s:1:"b";a:0:{}s:10:"blockquote";a:0:{}s:2:"br";a:0:{}s:4:"code";a:0:{}s:3:"del";a:0:{}s:2:"em";a:0:{}s:1:"i";a:0:{}s:3:"img";a:2:{s:3:"src";i:1;s:3:"alt";i:1;}s:3:"ins";a:0:{}s:2:"li";a:0:{}s:2:"ol";a:0:{}s:1:"p";a:0:{}s:3:"pre";a:0:{}s:3:"sup";a:0:{}s:5:"table";a:0:{}s:2:"td";a:0:{}s:2:"th";a:0:{}s:2:"tr";a:0:{}s:2:"tt";a:0:{}s:2:"ul";a:0:{}}',"array","This variable controls input filtering. HTML tags and their attributes, which are not in this list, get filtered out when new RSS items are imported.",NULL),
        "rss.output.showfeedmeta"    =>        array('false','false','boolean','Display meta-information (like a web- and rss/rdf/xml url) about each feed in the feed side-column.',NULL),
        //"rss.input.tags.delicious"    =>        array('false','false','boolean','Look up tag suggestions on del.icio.us when editing item tags.',NULL),
        "rss.output.frontpage.numitems"    =>        array("100","100","num","Maximum number of items displayed on the main page. Set this variable to 0 to show no items on the main page.",NULL),
        "rss.output.frontpage.mixeditems"    => array('true','true','boolean','Show read items along with unread items on the front page?',NULL),
        "rss.output.frontpage.numreaditems"    => array(-1,-1,'num','If there are no unread items then how many items to show on the frontpage. Set this to -1 if you want it to be the same as rss.output.numitemsonmainpage',NULL),
        "rss.output.theme"            =>        array('default','default','string','The theme to use. Download more themes from the <a href="http://themes.gregarius.net/">Gregarius Themes Repository</a>.',NULL),
        "rss.output.cachecontrol"    =>        array('false','false','boolean','If true, Gregarius will negotiate with the browser and check whether it should get a fresh document or not.',NULL),
        "rss.config.plugins"        =>        array('a:2:{i:0;s:13:"urlfilter.php";i:1;s:18:"roundedcorners.php";}','a:2:{i:0;s:13:"urlfilter.php";i:1;s:18:"roundedcorners.php";}','array','Plugins are third-party scripts that offer extended functionalities. More plugins can be found at the <a href="http://plugins.gregarius.net/">Plugin Repository</a>.' , NULL),
        "rss.input.allowupdates"    =>        array('true','true'