<commit_before>DROP TABLE IF EXISTS `pre_binding`;
CREATE TABLE `pre_binding` (
  `bind_id` varchar(20) NOT NULL AUTO_INCREMENT,
  `uid` char(255) NOT NULL,
  `code_id` varchar(255) NOT NULL,
  `block_flag` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`bind_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `airdevice`.`idle_machine` (
  `im_id` VARCHAR(20) NOT NULL,
  `uid` VARCHAR(45) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`im_id`));

CREATE SCHEMA `air_operation` DEFAULT CHARACTER SET utf8 ;

DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `air_operation`.`user_log` (
  `log_id` varchar(20) NOT NULL,
  `user_id` VARCHAR(20) NOT NULL,
  `target` VARCHAR(45) NOT NULL,
  `message` LONGTEXT NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`log_id`)
  );

##20171029
CREATE SCHEMA `air_measure` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE `air_measure`.`machine` (
  `machine_id` VARCHAR(50) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`machine_id`));

CREATE TABLE `air_measure`.`qrcode` (
  `code_id` VARCHAR(20) NOT NULL,
  `code_value` VARCHAR(45) NOT NULL,
  `code_status` TINYINT(1) NOT NULL DEFAULT 0,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`code_id`));

CREATE TABLE `air_measure`.`mq_bind` (
  `bind_id` VARCHAR(20) NOT NULL,
  `machine_id` VARCHAR(45) NOT NULL,
  `code_value` VARCHAR(45) NOT NULL,
  `bind_status` TINYINT(1) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`bind_id`));

  
##2017.11.12
ALTER TABLE `airdevice`.`goods_model` 
ADD COLUMN `model_bonus` INT NOT NULL DEFAULT 0 AFTER `model_description`;


##2017.11.13
ALTER TABLE `airdevice`.`point_record` 
ADD COLUMN `order_id` VARCHAR(20) NULL AFTER `consumer_id`;


##2017.11.16
*#create system_log
DROP TABLE IF EXISTS `system_log`;
CREATE TABLE `air_operation`.`system_log` (
  `log_id` varchar(20) NOT NULL,
  `target` VARCHAR(45) NOT NULL,
  `message` LONGTEXT NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`log_id`)
  );

##2017.11.17
  # create VIEW
CREATE VIEW point_value_view
AS
SELECT
	point_record.record_id as record_id,
	point_record.order_id as order_id,
	point_record.consumer_id as consumer_id,
	goods_model.model_code as model_code,
	goods_model.model_bonus as model_bonus,
	point_record.create_time as create_time
from point_record, taobao_order, goods_model
WHERE point_record.order_id = taobao_order.order_id
			AND SUBSTRING(taobao_order.product_serial, 1, 3) = goods_model.model_code
			AND taobao_order.block_flag = 1

##2017.11.20
ALTER TABLE `airdevice`.`goods_model` 
ADD COLUMN `min_velocity` INT NOT NULL DEFAULT 0 AFTER `model_bonus`;

##2017.12.06
CREATE VIEW machine_status_view
AS
SELECT device_id, chip_id, model_code, model_name, update_time from device_chip, goods_model
WHERE SUBSTR(device_chip.device_id FROM 1 FOR 3) = goods_model.model_code
AND device_chip.`status` = 1
ORDER BY device_chip.update_time DESC

##2017.12.10
CREATE view latest_device_info as select device_id, pm25, power, time from deviceStatus where time >= subdate(now(), interval 20 minute) group by device_id;

CREATE TABLE `airdevice`.`install_insight` (
  `insight_id` VARCHAR(20) NOT NULL,
  `code_id` VARCHAR(45) NOT NULL,
  `insight_path` VARCHAR(200) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`insight_id`));

CREATE view device_address
as
SELECT device_id,
       `owner`,
       phone,
       city_name as city,
       province_name as province,
       address
       from deviceName, location_city, location_province
       WHERE deviceName.city_id = location_city.city_id
             AND
       deviceName.province_id = location_province.province_id

##2017.12.16
CREATE view qrcode_status_view
as
select code_id,
       code_value,
       qrcode.model_id,
       model_name,
       code_delivered,
       code_occupied,
       scan_time,
       qrcode.create_time
		from qrcode, goods_model
		WHERE qrcode.model_id = goods_model.model_id<commit_msg>创建orderchannel数据表<commit_after>DROP TABLE IF EXISTS `pre_binding`;
CREATE TABLE `pre_binding` (
  `bind_id` varchar(20) NOT NULL AUTO_INCREMENT,
  `uid` char(255) NOT NULL,
  `code_id` varchar(255) NOT NULL,
  `block_flag` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`bind_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `airdevice`.`idle_machine` (
  `im_id` VARCHAR(20) NOT NULL,
  `uid` VARCHAR(45) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`im_id`));

CREATE SCHEMA `air_operation` DEFAULT CHARACTER SET utf8 ;

DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `air_operation`.`user_log` (
  `log_id` varchar(20) NOT NULL,
  `user_id` VARCHAR(20) NOT NULL,
  `target` VARCHAR(45) NOT NULL,
  `message` LONGTEXT NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`log_id`)
  );

##20171029
CREATE SCHEMA `air_measure` DEFAULT CHARACTER SET utf8 ;

CREATE TABLE `air_measure`.`machine` (
  `machine_id` VARCHAR(50) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`machine_id`));

CREATE TABLE `air_measure`.`qrcode` (
  `code_id` VARCHAR(20) NOT NULL,
  `code_value` VARCHAR(45) NOT NULL,
  `code_status` TINYINT(1) NOT NULL DEFAULT 0,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`code_id`));

CREATE TABLE `air_measure`.`mq_bind` (
  `bind_id` VARCHAR(20) NOT NULL,
  `machine_id` VARCHAR(45) NOT NULL,
  `code_value` VARCHAR(45) NOT NULL,
  `bind_status` TINYINT(1) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`bind_id`));

  
##2017.11.12
ALTER TABLE `airdevice`.`goods_model` 
ADD COLUMN `model_bonus` INT NOT NULL DEFAULT 0 AFTER `model_description`;


##2017.11.13
ALTER TABLE `airdevice`.`point_record` 
ADD COLUMN `order_id` VARCHAR(20) NULL AFTER `consumer_id`;


##2017.11.16
*#create system_log
DROP TABLE IF EXISTS `system_log`;
CREATE TABLE `air_operation`.`system_log` (
  `log_id` varchar(20) NOT NULL,
  `target` VARCHAR(45) NOT NULL,
  `message` LONGTEXT NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`log_id`)
  );

##2017.11.17
  # create VIEW
CREATE VIEW point_value_view
AS
SELECT
	point_record.record_id as record_id,
	point_record.order_id as order_id,
	point_record.consumer_id as consumer_id,
	goods_model.model_code as model_code,
	goods_model.model_bonus as model_bonus,
	point_record.create_time as create_time
from point_record, taobao_order, goods_model
WHERE point_record.order_id = taobao_order.order_id
			AND SUBSTRING(taobao_order.product_serial, 1, 3) = goods_model.model_code
			AND taobao_order.block_flag = 1

##2017.11.20
ALTER TABLE `airdevice`.`goods_model` 
ADD COLUMN `min_velocity` INT NOT NULL DEFAULT 0 AFTER `model_bonus`;

##2017.12.06
CREATE VIEW machine_status_view
AS
SELECT device_id, chip_id, model_code, model_name, update_time from device_chip, goods_model
WHERE SUBSTR(device_chip.device_id FROM 1 FOR 3) = goods_model.model_code
AND device_chip.`status` = 1
ORDER BY device_chip.update_time DESC

##2017.12.10
CREATE view latest_device_info as select device_id, pm25, power, time from deviceStatus where time >= subdate(now(), interval 20 minute) group by device_id;

CREATE TABLE `airdevice`.`install_insight` (
  `insight_id` VARCHAR(20) NOT NULL,
  `code_id` VARCHAR(45) NOT NULL,
  `insight_path` VARCHAR(200) NOT NULL,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`insight_id`));

CREATE view device_address
as
SELECT device_id,
       `owner`,
       phone,
       city_name as city,
       province_name as province,
       address
       from deviceName, location_city, location_province
       WHERE deviceName.city_id = location_city.city_id
             AND
       deviceName.province_id = location_province.province_id

##2017.12.16
CREATE view qrcode_status_view
as
select code_id,
       code_value,
       qrcode.model_id,
       model_name,
       code_delivered,
       code_occupied,
       scan_time,
       qrcode.create_time
		from qrcode, goods_model
		WHERE qrcode.model_id = goods_model.model_id

DROP TABLE IF EXISTS `orderChannel`;
CREATE TABLE `airdevice`.`orderChannel` (
  `channel_id` VARCHAR (20) NOT NULL ,
  `channel_name` VARCHAR (50) NOT NULL ,
  `block_flag` TINYINT(1) NOT NULL DEFAULT 0,
  `create_time` DATETIME NOT NULL,
  PRIMARY KEY (`channel_id`)
);
{'fraction_non_alphanumeric': 0.10247990035103613, 'fraction_numerical': 0.033744762767523494, 'mean_word_length': 4.668806161745828, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1042673', 'n_tokens_mistral': 3534, 'n_tokens_neox': 3225, 'n_words': 880}