structure.sql

ECSHOP是一款开源免费的通用电子商务平台构建软件

-- 分类表增加样式的字段
ALTER TABLE `ecs_category` ADD `style` VARCHAR( 150 ) NOT NULL;

-- 增加办事处表
DROP TABLE IF EXISTS `ecs_agency`;
CREATE TABLE `ecs_agency` (
  `agency_id` smallint(5) unsigned NOT NULL auto_increment,
  `agency_name` varchar(255) NOT NULL,
  `agency_desc` text NOT NULL,
  PRIMARY KEY  (`agency_id`),
  KEY `agency_name` (`agency_name`)
) TYPE=MyISAM;

-- 管理员表增加字段：所属办事处
ALTER TABLE `ecs_admin_user` ADD `agency_id` SMALLINT UNSIGNED NOT NULL;
ALTER TABLE `ecs_admin_user` ADD INDEX ( `agency_id` );

-- 地区表增加字段：所属办事处
ALTER TABLE `ecs_region` ADD `agency_id` SMALLINT UNSIGNED NOT NULL;
ALTER TABLE `ecs_region` ADD INDEX ( `agency_id` );

-- 订单表增加字段：所属办事处
ALTER TABLE `ecs_order_info` ADD `agency_id` SMALLINT UNSIGNED NOT NULL;
ALTER TABLE `ecs_order_info` ADD INDEX ( `agency_id` );

-- 收藏表增加是否为关注商品的字段
ALTER TABLE `ecs_collect_goods` ADD `is_attention` TINYINT( 1 ) NOT NULL DEFAULT '0';
ALTER TABLE `ecs_collect_goods` ADD INDEX ( `is_attention` );

-- 商品类型表增加属性分组的字段
ALTER TABLE `ecs_goods_type` ADD `attr_group` VARCHAR( 255 ) NOT NULL;

-- 商品属性表增加属性分组的字段
ALTER TABLE `ecs_attribute` ADD `attr_group` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0';

-- 增加搜索引擎爬虫统计表
DROP TABLE IF EXISTS `ecs_searchengine`;
CREATE TABLE `ecs_searchengine` (
  `date` date NOT NULL default '0000-00-00',
  `searchengine` varchar(20) NOT NULL default '',
  `count` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`date`,`searchengine`)
) TYPE=MyISAM;

-- 增加搜索引擎关键字统计表
DROP TABLE IF EXISTS `ecs_keywords`;
CREATE TABLE `ecs_keywords` (
  `date` date NOT NULL default '0000-00-00',
  `searchengine` varchar(20) NOT NULL default '',
  `keyword` varchar(90) NOT NULL default '',
  `count` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`date`,`searchengine`,`keyword`)
) TYPE=MyISAM;

-- 删除统计表中的关键字字段
ALTER TABLE `ecs_stats` DROP `keywords`;

-- 调整session表
DROP TABLE IF EXISTS `ecs_sessions`;
CREATE TABLE `ecs_sessions` (
  `sesskey` char(32) binary NOT NULL default '',
  `expiry` int(10) unsigned NOT NULL default '0',
  `userid` mediumint(8) unsigned NOT NULL default '0',
  `adminid` mediumint(8) unsigned NOT NULL default '0',
  `ip` char(15) NOT NULL default '',
  `data` char(255) NOT NULL default '',
  PRIMARY KEY (`sesskey`),
  KEY `expiry` (`expiry`)
) TYPE=HEAP;

-- 增加session数据表
DROP TABLE IF EXISTS `ecs_sessions_data`;
CREATE TABLE `ecs_sessions_data` (
`sesskey` varchar( 32 ) binary NOT NULL default '',
`expiry` int( 10 ) unsigned NOT NULL default '0',
`data` longtext NOT NULL ,
PRIMARY KEY ( `sesskey` ) ,
KEY `expiry` ( `expiry` )
) TYPE = MYISAM;

-- 增加计划任务表
DROP TABLE IF EXISTS `ecs_crons`;
CREATE TABLE `ecs_crons` (
  `cron_id` tinyint(3) unsigned NOT NULL auto_increment,
  `cron_code` varchar(20) NOT NULL,
  `cron_name` varchar(120) NOT NULL,
  `cron_desc` text,
  `cron_order` tinyint(3) unsigned NOT NULL default '0',
  `cron_config` text NOT NULL,
  `thistime` int(10) NOT NULL default '0',
  `nextime` int(10) NOT NULL,
  `day` tinyint(2) NOT NULL,
  `week` varchar(1) NOT NULL,
  `hour` varchar(2) NOT NULL,
  `minute` varchar(255) NOT NULL,
  `enable` tinyint(1) NOT NULL default '1',
  `run_once` tinyint(1) NOT NULL default '0',
  `allow_ip` varchar(100) NOT NULL default '',
  `alow_files` varchar(255) NOT NULL,
  PRIMARY KEY  (`cron_id`),
  KEY `nextime` (`nextime`),
  KEY `enable` (`enable`),
  KEY `cron_code` (`cron_code`)
) TYPE=MyISAM;

-- 增加错误日志表
DROP TABLE IF EXISTS `ecs_error_log`;
CREATE TABLE `ecs_error_log` (
  `id` int(10) NOT NULL auto_increment,
  `info` varchar(255) NOT NULL,
  `file` varchar(100) NOT NULL,
  `time` int(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `time` (`time`)
) TYPE=MyISAM;

-- 改商品属性的attr_select字段
ALTER TABLE `ecs_attribute` CHANGE `attr_select` `attr_input_type` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1';

-- 增加 todolist 字段
ALTER TABLE `ecs_admin_user` ADD `todolist` LONGTEXT NULL;

-- 增加商品活动表（换名字了，原来的表不用了，导完数据就删除）
-- --------------------------------------------------------
DROP TABLE IF EXISTS `ecs_goods_activity`;
CREATE TABLE `ecs_goods_activity` (
  `act_id` mediumint(8) unsigned NOT NULL auto_increment,
  `act_name` varchar(255) NOT NULL,
  `act_desc` text NOT NULL,
  `act_type` tinyint(3) unsigned NOT NULL,
  `goods_id` mediumint(8) unsigned NOT NULL,
  `goods_name` varchar(255) NOT NULL,
  `start_time` int(10) unsigned NOT NULL,
  `end_time` int(10) unsigned NOT NULL,
  `is_finished` tinyint(3) unsigned NOT NULL,
  `ext_info` text NOT NULL,
  PRIMARY KEY  (`act_id`),
  KEY `act_name` (`act_name`,`act_type`,`goods_id`)
) TYPE=MyISAM;

-- 增加帐户变动记录表
DROP TABLE IF EXISTS `ecs_account_log`;
CREATE TABLE `ecs_account_log` (
  `log_id` mediumint(8) unsigned NOT NULL auto_increment,
  `user_id` mediumint(8) unsigned NOT NULL,
  `user_money` decimal(10,2) NOT NULL,
  `frozen_money` decimal(10,2) NOT NULL,
  `rank_points` mediumint(9) NOT NULL,
  `pay_points` mediumint(9) NOT NULL,
  `change_time` int(10) unsigned NOT NULL,
  `change_desc` varchar(255) NOT NULL,
  `change_type` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`log_id`),
  KEY `user_id` (`user_id`)
) TYPE=MyISAM;

-- 用户表增加字段：冻结资金
ALTER TABLE `ecs_users` ADD `frozen_money` DECIMAL( 10, 2 ) NOT NULL AFTER `user_money`;

-- 修改数据表中的时间格式
ALTER TABLE `ecs_ad` ADD `start_time` INT( 11 ) UNSIGNED NOT NULL AFTER `ad_code` ,
ADD `end_time` INT( 11 ) UNSIGNED NOT NULL AFTER `start_time`;

ALTER TABLE `ecs_admin_message` ADD `sent_time` INT( 11 ) UNSIGNED NOT NULL AFTER `receiver_id` ,
ADD `read_time` INT( 11 ) UNSIGNED NOT NULL AFTER `sent_time`;

ALTER TABLE `ecs_admin_user` ADD `add_time` INT( 11 ) UNSIGNED NOT NULL AFTER `password` ,
ADD `last_login` INT( 11 ) UNSIGNED NOT NULL AFTER `add_time`;

ALTER TABLE `ecs_bonus_type` ADD `send_start_date` INT( 11 ) UNSIGNED NOT NULL ,
ADD `send_end_date` INT( 11 ) UNSIGNED NOT NULL ,
ADD `use_start_date` INT( 11 ) UNSIGNED NOT NULL ,
ADD `use_end_date` INT( 11 ) UNSIGNED NOT NULL;

ALTER TABLE `ecs_collect_goods` ADD `add_time` INT( 11 ) UNSIGNED NOT NULL AFTER `goods_id`;

ALTER TABLE `ecs_goods` ADD `promote_start_date` INT( 11 ) UNSIGNED NOT NULL AFTER `promote_price` ,
ADD `promote_end_date` INT( 11 ) UNSIGNED NOT NULL AFTER `promote_start_date`;

ALTER TABLE `ecs_order_action` ADD `log_time` INT( 11 ) UNSIGNED NOT NULL;

ALTER TABLE `ecs_order_info` ADD `add_time` INT( 10 ) UNSIGNED NOT NULL AFTER `referer`;

ALTER TABLE `ecs_users` ADD `last_login` INT( 11 ) UNSIGNED NOT NULL AFTER `reg_time`;

ALTER TABLE `ecs_vote` ADD `start_time` INT( 11 ) UNSIGNED NOT NULL AFTER `vote_name` ,
ADD `end_time` INT( 11 ) UNSIGNED NOT NULL AFTER `start_time`;

-- 修改用户表，整合时记录用户状态
ALTER TABLE `ecs_users` ADD `flag` TINYINT UNSIGNED NOT NULL DEFAULT '0',
ADD `alias` VARCHAR( 60 ) NOT NULL;

ALTER TABLE `ecs_users` ADD INDEX ( `flag` );

-- 修改文章分类表，支持无级分类
ALTER TABLE `ecs_article_cat` ADD `parent_id` SMALLINT( 5 ) UNSIGNED NOT NULL DEFAULT '0';
ALTER TABLE `ecs_article_cat` ADD INDEX ( `parent_id` );

-- 文章分类名增加到255
ALTER TABLE `ecs_article_cat` CHANGE `cat_name` `cat_name` VARCHAR( 255 );

-- 删除留言的reply字段,添加order_id字段
ALTER TABLE `ecs_feedback` DROP `reply`;
ALTER TABLE `ecs_feedback` CHANGE `parend_id` `parent_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL DEFAULT '0';

-- 增加拍卖活动出价记录表
DROP TABLE IF EXISTS `ecs_auction_log`;
CREATE TABLE `ecs_auction_log` (
  `log_id` mediumint(8) unsigned NOT NULL auto_increment,
  `act_id` mediumint(8) unsigned NOT NULL,
  `bid_user` mediumint(8) unsigned NOT NULL,
  `bid_price` decimal(10,2) unsigned NOT NULL,
  `bid_time` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`log_id`),
  KEY `act_id` (`act_id`)
) TYPE=MyISAM;

-- 增加专题活动表
DROP TABLE IF EXISTS `ecs_topic`;
CREATE TABLE `ecs_topic` (
  `topic_id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '''''',
  `intro` text NOT NULL,
  `start_time` int(11) NOT NULL default '0',
  `end_time` int(10) NOT NULL default '0',
  `data` text NOT NULL,
  `template` varchar(255) NOT NULL default '''''',
  `css` text NOT NULL,
  KEY `topic_id` (`topic_id`)
) TYPE=MyISAM;



-- 商品分类增加是否启用的字段
ALTER TABLE `ecs_category` ADD `is_show` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '1';

-- 删除is_leaf
ALTER TABLE `ecs_category` DROP `is_leaf`;

-- 配置表增加排序的字段
ALTER TABLE `ecs_shop_config` ADD `sort_order` TINYINT( 3 ) UNSIGNED NOT NULL DEFAULT '1';

-- 用户表扩展用户信息
ALTER TABLE `ecs_users` ADD `msn` VARCHAR( 60 ) NOT NULL ,
ADD `qq` VARCHAR( 20 ) NOT NULL,
ADD `office_phone` VARCHAR( 20 ) NOT NULL,
ADD `home_phone` VARCHAR( 20 ) NOT NULL,
ADD `mobile_phone` VARCHAR( 20 ) NOT NULL;

-- 增加优惠活动表
DROP TABLE IF EXISTS `ecs_favourable_activity`;
CREATE TABLE `ecs_favourable_activity` (
  `act_id` smallint(5) unsigned NOT NULL auto_increment,
  `act_name` varchar(255) NOT NULL,
  `start_time` int(10) unsigned NOT NULL,
  `end_time` int(10) unsigned NOT NULL,
  `user_rank` varchar(255) NOT NULL,
  `act_range` tinyint(3) unsigned NOT NULL,
  `act_range_ext` varchar(255) NOT NULL,
  `min_amount` decimal(10,2) unsigned NOT NULL,
  `max_amount` decimal(10,2) unsigned NOT NULL,
  `act_type` tinyint(3) unsigned NOT NULL,
  `act_type_ext` decimal(10,2) unsigned NOT NULL,
  `gift` text NOT NULL,
  `sort_order` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`act_id`),
  KEY `act_name` (`act_name`)
) TYPE=MyISAM;

-- 购物车变化（对应优惠活动）
ALTER TABLE `ecs_cart` DROP `can_handsel`;
ALTER TABLE `ecs_cart` CHANGE `goods_price` `goods_price` DECIMAL( 10, 2 ) NOT NULL DEFAULT '0.00';

-- 添加评论用户ID字段
ALTER TABLE `ecs_comment` ADD `user_id` INT(11) UNSIGNED NOT NULL DEFAULT '0';

-- 添加文章外部链接字段
ALTER TABLE `ecs_article` ADD `link` VARCHAR( 255 ) NOT NULL;

-- 增加模板布局备份注释
ALTER TABLE `ecs_template` ADD `remarks` VARCHAR( 30 ) NOT NULL ;
ALTER TABLE `ecs_template` ADD INDEX ( `remarks` ) ;