structure.sql

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

-- 增加用户户是否已验证
ALTER TABLE `ecs_users` ADD `is_validated` TINYINT UNSIGNED NOT NULL DEFAULT '0';

-- 将虚拟卡做为标准功能
CREATE TABLE IF NOT EXISTS `ecs_virtual_card` (
    `card_id` mediumint(8) NOT NULL auto_increment,
    `goods_id` mediumint(8) unsigned NOT NULL default '0',
    `card_sn` varchar(60) NOT NULL default '',
    `card_password` varchar(60) NOT NULL default '',
    `add_date` int(11) NOT NULL default '0',
    `end_date` int(11) NOT NULL default '0',
    `is_saled` tinyint(1) NOT NULL default '0',
    `order_sn` varchar(20) NOT NULL default '',
    `crc32` int(11) NOT NULL default '0',
    PRIMARY KEY  (`card_id`),
    KEY `goods_id` (`goods_id`),
    KEY `car_sn` (`card_sn`),
    KEY `is_saled` (`is_saled`)
    ) TYPE=MyISAM ;

-- 支付方式加入是否支持在线支付
ALTER TABLE `ecs_payment` ADD `is_online` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0';

-- 增加留言关联的订单ID字段
ALTER TABLE `ecs_feedback` ADD `order_id` INT( 11 ) UNSIGNED NOT NULL DEFAULT '0';

-- 红包类型表增加字段：订单最低金额
ALTER TABLE `ecs_bonus_type` ADD `min_goods_amount` DECIMAL( 10, 2 ) UNSIGNED NOT NULL ;

-- 解决多个管理员同时添加商品时冲突的问题
ALTER TABLE `ecs_link_goods` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_group_goods` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_goods_article` ADD `admin_id` TINYINT UNSIGNED NOT NULL ;
ALTER TABLE `ecs_group_goods` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `parent_id` , `goods_id` , `admin_id` );
ALTER TABLE `ecs_link_goods` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `goods_id` , `link_goods_id` , `admin_id` );
ALTER TABLE `ecs_goods_article` DROP PRIMARY KEY ,
ADD PRIMARY KEY ( `goods_id` , `article_id` , `admin_id` );

-- 会员表增加信用额度字段
ALTER TABLE `ecs_users` ADD `credit_line` DECIMAL( 10, 2 ) UNSIGNED NOT NULL ;

-- 商品表增加商品名称样式字段
ALTER TABLE `ecs_goods` ADD `goods_name_style` VARCHAR( 255 ) NOT NULL DEFAULT '+' AFTER `goods_name` ;

-- 订单表增加发票类型及税额字段
ALTER TABLE `ecs_order_info` ADD `inv_type` VARCHAR( 60 ) NOT NULL ,
ADD `tax` DECIMAL( 10, 2 ) NOT NULL ;

-- 商品表增加送多少积分字段
ALTER TABLE `ecs_goods` ADD `give_integral` INT UNSIGNED NOT NULL;

-- 商品表删除字段
ALTER TABLE `ecs_goods` DROP `can_handsel`;
ALTER TABLE `ecs_goods` DROP `fitting_price`;
ALTER TABLE `ecs_goods` DROP `is_linked`;
ALTER TABLE `ecs_goods` DROP `is_basic`;
ALTER TABLE `ecs_goods` DROP `is_gift`;

-- 增加批发商品表
DROP TABLE IF EXISTS `ecs_wholesale`;
CREATE TABLE `ecs_wholesale` (
  `act_id` mediumint(8) unsigned NOT NULL auto_increment,
  `goods_id` mediumint(8) unsigned NOT NULL,
  `goods_name` varchar(255) NOT NULL,
  `rank_ids` varchar(255) NOT NULL,
  `prices` text NOT NULL,
  `enabled` tinyint(3) unsigned NOT NULL,
  PRIMARY KEY  (`act_id`),
  KEY `goods_id` (`goods_id`)
) TYPE=MYISAM ;

-- 增加发送队列表
DROP TABLE IF EXISTS `ecs_email_sendlist`;
CREATE TABLE  `ecs_email_sendlist` (
 `id` MEDIUMINT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
 `email` VARCHAR( 100 ) NOT NULL ,
 `template_id` MEDIUMINT( 8 ) NOT NULL ,
 `email_content` TEXT NOT NULL ,
 `error` TINYINT( 1 ) NOT NULL DEFAULT  '0' ,
 `pri` TINYINT( 10 ) NOT NULL ,
 `last_send` INT( 10 ) NOT NULL
) TYPE = MYISAM ;

-- 增加电子杂志订阅表
DROP TABLE IF EXISTS `ecs_email_list`;
CREATE TABLE `ecs_email_list` (
  `id` mediumint(8) NOT NULL auto_increment,
  `email` varchar(60) NOT NULL,
  `stat` tinyint(1) NOT NULL default '0',
  `hash` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

ALTER TABLE `ecs_mail_templates` ADD `type` VARCHAR( 10 ) NOT NULL ;
ALTER TABLE `ecs_mail_templates` ADD INDEX ( `type` ) ;
UPDATE  `ecs_mail_templates` SET type='template';

ALTER TABLE `ecs_mail_templates` DROP INDEX `template_code`;
ALTER TABLE `ecs_mail_templates` ADD INDEX ( `template_code` );

-- 增加自动处理的表
DROP TABLE IF EXISTS `ecs_auto_manage`;
CREATE TABLE `ecs_auto_manage` (
  `item_id` mediumint(8) NOT NULL,
  `type` varchar(10) NOT NULL,
  `starttime` int(10) NOT NULL,
  `endtime` int(10) NOT NULL,
  PRIMARY KEY  (`item_id`,`type`)
) TYPE=MyISAM;

ALTER TABLE `ecs_order_info` ADD `parent_id` mediumint(8) unsigned NOT NULL default '0';
ALTER TABLE `ecs_order_info` ADD `is_separate` tinyint(1) NOT NULL default '0';
ALTER TABLE `ecs_order_info` ADD INDEX ( `is_separate` ) ;

-- 增加分成信息纪录
DROP TABLE IF EXISTS `ecs_affiliate_log`;
CREATE TABLE  `ecs_affiliate_log` (
 `log_id` MEDIUMINT( 8 ) NOT NULL auto_increment,
 `order_id` MEDIUMINT( 8 ) NOT NULL ,
 `time` INT( 10 ) NOT NULL ,
 `user_id` MEDIUMINT( 8 ) NOT NULL,
 `user_name` varchar(60),
 `money` DECIMAL(10,2) NOT NULL DEFAULT '0',
 `point` INT(10) NOT NULL DEFAULT '0',
 `separate_type` TINYINT(1) NOT NULL DEFAULT '0',
PRIMARY KEY ( `log_id` )
) TYPE = MYISAM ;
ALTER TABLE `ecs_affiliate_log` ADD `separate_by` tinyint(1) NOT NULL default '0';

-- 增加价格分级及过滤属性
ALTER TABLE `ecs_category` ADD `grade` tinyint(4) NOT NULL default '0';
ALTER TABLE `ecs_category` ADD `filter_attr` smallint(6) NOT NULL default 0;

-- 增加`goods_attr_id`
ALTER TABLE `ecs_cart` ADD `goods_attr_id` mediumint(8) NOT NULL;
ALTER TABLE `ecs_cart` ADD `can_handsel` tinyint(3) unsigned NOT NULL default '0';

-- 增加 自定义导航栏 表
DROP TABLE IF EXISTS `ecs_nav`;
CREATE TABLE `ecs_nav` (
  `id` mediumint(8) NOT NULL auto_increment,
  `ctype` VARCHAR( 10 ) NULL,
  `cid` SMALLINT( 5 ) UNSIGNED NULL,
  `name` varchar(255) NOT NULL,
  `ifshow` tinyint(1) NOT NULL,
  `vieworder` tinyint(1) NOT NULL,
  `opennew` tinyint(1) NOT NULL,
  `url` varchar(255) NOT NULL,
  `type` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `type` (`type`),
  KEY `ifshow` (`ifshow`)
) TYPE=MyISAM;
--  ALTER TABLE `ecs_nav` ADD `ctype` VARCHAR( 10 ) NULL AFTER `id` ,
--  ADD `cid` SMALLINT( 5 ) UNSIGNED NULL AFTER `ctype` ;

-- 用户表增加上级id
ALTER TABLE `ecs_users` ADD `parent_id` mediumint(8) unsigned NOT NULL;

-- 用户id的类型加大
ALTER TABLE `ecs_admin_user` CHANGE `user_id` `user_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;

-- 广告字段增加默认值
ALTER TABLE `ecs_ad` CHANGE `ad_link` `ad_link` VARCHAR( 255 ) NOT NULL default '';
ALTER TABLE `ecs_ad` CHANGE `start_time` `start_time` int(11 ) NOT NULL default '0';
ALTER TABLE `ecs_ad` CHANGE `end_time` `end_time` int( 11 ) NOT NULL default '0';
ALTER TABLE `ecs_ad` CHANGE `link_phone` `link_phone` VARCHAR( 60 ) NOT NULL default '';

-- admin_message设定字段默认值
ALTER TABLE `ecs_admin_message` CHANGE `sent_time` `sent_time` int(11 ) NOT NULL default '0';
ALTER TABLE `ecs_admin_message` CHANGE `read_time` `read_time` int(11 ) NOT NULL default '0';

-- admin_user设定字段默认值
ALTER TABLE `ecs_admin_user` CHANGE `add_time` `add_time` int(11 ) NOT NULL default '0';
ALTER TABLE `ecs_admin_user` CHANGE `last_login` `last_login` int(11 ) NOT NULL default '0';

-- 文章链接设定默认值
ALTER TABLE `ecs_article` CHANGE `link` `link` varchar(255 ) NOT NULL default '';
 
-- 文章分类名称设定默认值
ALTER TABLE `ecs_article_cat` CHANGE `cat_name` `cat_name` varchar(255 ) NOT NULL default '';

-- bounds_type设定默认值
ALTER TABLE `ecs_bonus_type` CHANGE `send_start_date` `send_start_date` int(11) NOT NULL default '0';
ALTER TABLE `ecs_bonus_type` CHANGE `send_end_date` `send_end_date` int(11) NOT NULL default '0';
ALTER TABLE `ecs_bonus_type` CHANGE `use_start_date` `use_start_date` int(11) NOT NULL default '0';
ALTER TABLE `ecs_bonus_type` CHANGE `use_end_date` `use_end_date` int(11) NOT NULL default '0';
ALTER TABLE `ecs_bonus_type` CHANGE `min_goods_amount` `min_goods_amount` decimal(10,2) unsigned NOT NULL default '0.00';

-- booking_goods设定默认值
ALTER TABLE `ecs_booking_goods` CHANGE `tel` `tel` varchar(60) NOT NULL default '';

-- 购物车设定默认值
ALTER TABLE `ecs_cart` CHANGE `session_id` `session_id` char(32) binary NOT NULL default '';
ALTER TABLE `ecs_cart` CHANGE `is_gift` `is_gift` smallint(5) unsigned NOT NULL default '0';
ALTER TABLE `ecs_cart` CHANGE `can_handsel` `can_handsel` tinyint(3) unsigned NOT NULL default '0';

-- 分类表设定默认值
ALTER TABLE `ecs_category` CHANGE `cat_name` `cat_name` varchar(90) NOT NULL default '';

-- collect_goods设定默认值
ALTER TABLE `ecs_collect_goods` CHANGE `add_time` `add_time` int(11) unsigned NOT NULL default '0';

-- 商品表字段添加默认值
ALTER TABLE `ecs_goods` CHANGE `promote_start_date` `promote_start_date` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_goods` CHANGE `promote_end_date` `promote_end_date` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_goods` CHANGE `give_integral` `give_integral` int(10) NOT NULL default '-1';
ALTER TABLE `ecs_goods` DROP INDEX `promote_start`;
ALTER TABLE `ecs_goods` DROP INDEX `promote_end`;
ALTER TABLE `ecs_goods` ADD INDEX (`promote_start_date`) ;
ALTER TABLE `ecs_goods` ADD INDEX (`promote_end_date`) ;

-- 商品文章表字段添加默认值
ALTER TABLE `ecs_goods_article` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';

-- 配件表字段添加默认值
ALTER TABLE `ecs_group_goods` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';

-- link_goods表添加默认值
ALTER TABLE `ecs_link_goods` CHANGE `admin_id` `admin_id` tinyint(3) unsigned NOT NULL default '0';

-- 邮件模板表添加唯一索引
ALTER TABLE `ecs_mail_templates` ADD UNIQUE (`template_code`);

-- 订单操作表字段添加默认值
ALTER TABLE `ecs_order_action` CHANGE `log_time` `log_time` int(11) unsigned NOT NULL default '0';

-- 订单商品表添加默认值
ALTER TABLE `ecs_order_goods` CHANGE `is_gift` `is_gift` smallint(5) unsigned NOT NULL default '0';

-- 订单信息表添加默认值
ALTER TABLE `ecs_order_info` CHANGE `address` `address` varchar(255) NOT NULL default '';
ALTER TABLE `ecs_order_info` CHANGE `zipcode` `zipcode` varchar(60) NOT NULL default '';
ALTER TABLE `ecs_order_info` CHANGE `tel` `tel` varchar(60) NOT NULL default '';
ALTER TABLE `ecs_order_info` CHANGE `mobile` `mobile` varchar(60) NOT NULL default '';
ALTER TABLE `ecs_order_info` CHANGE `add_time` `add_time` int(10) unsigned NOT NULL default '0';
ALTER TABLE `ecs_order_info` CHANGE `pay_note` `pay_note` varchar(255) NOT NULL default '';

-- 支付日志表添加默认值
ALTER TABLE `ecs_pay_log` CHANGE `order_amount` `order_amount` decimal(10,2) unsigned NOT NULL;

-- 支付方式添加默认值
ALTER TABLE `ecs_payment` CHANGE `pay_fee` `pay_fee` varchar(10) NOT NULL default '0';
ALTER TABLE `ecs_payment` CHANGE `is_online` `is_online` tinyint(1) unsigned NOT NULL default '0';

-- 配送表修改默认值
ALTER TABLE `ecs_shipping` CHANGE `insure` `insure` varchar(10) NOT NULL default '0';

-- 模板设置默认值
ALTER TABLE `ecs_template` CHANGE `remarks` `remarks` varchar(30) NOT NULL default '';

-- 用户地址设置默认值
ALTER TABLE `ecs_user_address` CHANGE `zipcode` `zipcode` varchar(60) NOT NULL default '';
ALTER TABLE `ecs_user_address` CHANGE `tel` `tel` varchar(60) NOT NULL default '';
ALTER TABLE `ecs_user_address` CHANGE `mobile` `mobile` varchar(60) NOT NULL default '';

-- 用户表设置默认值
ALTER TABLE `ecs_users` CHANGE `frozen_money` `frozen_money` decimal(10,2) NOT NULL default '0.00';
ALTER TABLE `ecs_users` CHANGE `last_login` `last_login` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_users` CHANGE `parent_id` `parent_id` mediumint(9) NOT NULL default '0';
ALTER TABLE `ecs_users` DROP INDEX `user_name`;
ALTER TABLE `ecs_users` ADD UNIQUE (`user_name`);
ALTER TABLE `ecs_users` ADD INDEX (`parent_id`);

-- 投票表设置默认值
ALTER TABLE `ecs_vote` CHANGE `start_time` `start_time` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_vote` CHANGE `end_time` `end_time` int(11) unsigned NOT NULL default '0';
ALTER TABLE `ecs_admin_user` CHANGE `user_id` `user_id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT;

-- 支持负id
ALTER TABLE `ecs_article_cat` CHANGE `cat_id` `cat_id` SMALLINT( 5 ) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ecs_article` CHANGE `cat_id` `cat_id` SMALLINT( 5 ) NOT NULL DEFAULT '0';

-- 订单表增加“折扣”字段
ALTER TABLE `ecs_order_info` ADD `discount` DECIMAL( 10, 2 ) UNSIGNED NOT NULL DEFAULT '0';

--Mail最后发送
ALTER TABLE `ecs_mail_templates` ADD `last_send` INT( 10 ) UNSIGNED NOT NULL DEFAULT '0' AFTER `last_modify` ;

-- 修改红包序号类型
ALTER TABLE `ecs_user_bonus` CHANGE `bonus_sn` `bonus_sn` BIGINT( 20 ) UNSIGNED NOT NULL DEFAULT '0';