package com.database_util;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class DB_Alarm {
|
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
System.out.println(" db_alarm init start at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
createDB_ALARM(pool);
|
|
createBattalarm_Data_History_Table(pool, recreate);
|
|
createBattalarm_Data_Table(pool, recreate);
|
|
createBts_Station_Event_Table(pool, recreate);
|
|
createDevalarm_Data__History_Table(pool, recreate);
|
|
createDevalarm_Data_Table(pool, recreate);
|
|
createTb_Alarm_Message(pool, recreate);
|
|
createTb_Alarm_Detail_Table(pool, recreate); //´´½¨¸æ¾¯ÏêÇé
|
|
System.out.println(" db_alarm init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
}
|
|
/**
|
* ´´½¨ db_alarm Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_ALARM(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_ALARM);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨tb_devalarm_data_history±í
|
*/
|
public static void createDevalarm_Data__History_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Devalarm_Data__History_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Devalarm_Data__History_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" record_id bigint(20) NOT NULL DEFAULT '0'," +
|
" dev_id int(11) NOT NULL DEFAULT '0'," +
|
" dev_ip varchar(24) NOT NULL DEFAULT '127.0.0.1'," +
|
" alm_type int(11) NOT NULL DEFAULT '0'," +
|
" alm_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_start_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_end_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_value float NOT NULL DEFAULT '0'," +
|
" alm_is_confirmed tinyint(1) NOT NULL DEFAULT '0'," +
|
" alm_confirmed_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_cleared_type int(11) NOT NULL DEFAULT '0'," +
|
" usr_Id int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_record_id (record_id)," +
|
" KEY index_dev_id (dev_id)," +
|
" KEY index_alm_type (alm_type)," +
|
" KEY index_alm_start_time (alm_start_time)," +
|
" KEY index_alm_cleared_type (alm_cleared_type)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄalm_valueÁÐ'¸æ¾¯Öµ'
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data_history'"
|
+ " AND column_name='alm_value'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data__History_Table
|
+ " ADD COLUMN alm_value float NOT NULL DEFAULT '0';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨tb_devalarm_data±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDevalarm_Data_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Devalarm_Data_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Devalarm_Data_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" record_id bigint(20) NOT NULL DEFAULT '0'," +
|
" dev_id int(11) NOT NULL DEFAULT '0'," +
|
" dev_ip varchar(24) NOT NULL DEFAULT '127.0.0.1'," +
|
" alm_type int(11) NOT NULL DEFAULT '0'," +
|
" alm_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_start_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_end_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_value float NOT NULL DEFAULT '0'," +
|
" alm_is_confirmed tinyint(1) NOT NULL DEFAULT '0'," +
|
" alm_confirmed_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_cleared_type int(11) NOT NULL DEFAULT '0'," +
|
" usr_Id int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_record_id (record_id)," +
|
" KEY index_dev_id (dev_id)," +
|
" KEY index_alm_type (alm_type)," +
|
" KEY index_alm_start_time (alm_start_time)," +
|
" KEY index_alm_cleared_type (alm_cleared_type)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄalm_valueÁÐ'¸æ¾¯Öµ'
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data'"
|
+ " AND column_name='alm_value'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data_Table
|
+ " ADD COLUMN alm_value float NOT NULL DEFAULT '0';");
|
}
|
res = null;
|
|
//Ìí¼ÓÖ¸¶¨µÄwork_flagÁÐ(Åɵ¥¹ÜÀí)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data'"
|
+ " AND column_name='work_flag'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data_Table
|
+ " ADD COLUMN `work_flag` tinyint NOT NULL DEFAULT 0;");
|
}
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄmsg_flagÁÐ(Åɵ¥¹ÜÀí)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data'"
|
+ " AND column_name='msg_flag'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data_Table
|
+ " ADD COLUMN `msg_flag` tinyint NOT NULL DEFAULT 0;");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨tb_bts_station_event±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createBts_Station_Event_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Bts_Station_Event_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Bts_Station_Event_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" station_id bigint(20) NOT NULL DEFAULT '0'," +
|
" dev_id bigint(20) NOT NULL DEFAULT '0'," +
|
" record_datetime varchar(20) NOT NULL DEFAULT '2015-12-02 12:10:00'," +
|
" station_event_type int(11) NOT NULL DEFAULT '0'," +
|
" station_event_trig int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_dev_id (dev_id)," +
|
" KEY index_station_id (station_id)" +
|
") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨tb_battalarm_data_history±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createBattalarm_Data_History_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Battalarm_Data_History_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Battalarm_Data_History_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" BattGroupId int(11) NOT NULL DEFAULT '0'," +
|
" MonNum int(11) NOT NULL DEFAULT '0'," +
|
" Record_Id bigint(20) NOT NULL DEFAULT '0'," +
|
" alm_id int(11) NOT NULL DEFAULT '0'," +
|
" alm_signal_id int(11) NOT NULL DEFAULT '0'," +
|
" alm_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_start_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_end_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_value float NOT NULL DEFAULT '0'," +
|
" alm_is_confirmed tinyint(1) NOT NULL DEFAULT '0'," +
|
" alm_confirmed_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_cleared_type int(11) NOT NULL DEFAULT '0'," +
|
" usr_Id int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_battgroup_id (BattGroupId)," +
|
" KEY index_Record_Id (Record_Id)," +
|
" KEY index_alm_id (alm_id)," +
|
" KEY index_alm_start_time (alm_start_time)," +
|
" KEY index_alm_cleared_type (alm_cleared_type)," +
|
" KEY index_alm_signal_id (alm_signal_id)," +
|
" KEY index_alm_level (alm_level)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨tb_battalarm_data±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createBattalarm_Data_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Battalarm_Data_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Battalarm_Data_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" BattGroupId int(11) NOT NULL DEFAULT '0'," +
|
" MonNum int(11) NOT NULL DEFAULT '0'," +
|
" Record_Id bigint(20) NOT NULL DEFAULT '0'," +
|
" alm_id int(11) NOT NULL DEFAULT '0'," +
|
" alm_signal_id int(11) NOT NULL DEFAULT '0'," +
|
" alm_level int(11) NOT NULL DEFAULT '0'," +
|
" alm_start_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_end_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_value float NOT NULL DEFAULT '0'," +
|
" alm_is_confirmed tinyint(1) NOT NULL DEFAULT '0'," +
|
" alm_confirmed_time datetime NOT NULL DEFAULT '1982-01-01 00:00:00'," +
|
" alm_cleared_type int(11) NOT NULL DEFAULT '0'," +
|
" usr_Id int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_battgroup_id (BattGroupId)," +
|
" KEY index_Record_Id (Record_Id)," +
|
" KEY index_alm_id (alm_id)," +
|
" KEY index_alm_start_time (alm_start_time)," +
|
" KEY index_alm_cleared_type (alm_cleared_type)," +
|
" KEY index_alm_signal_id (alm_signal_id)," +
|
" KEY index_alm_level (alm_level)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄwork_flagÁÐ(Åɵ¥¹ÜÀí)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_battalarm_data'"
|
+ " AND column_name='work_flag'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Battalarm_Data_Table
|
+ " ADD COLUMN `work_flag` tinyint NOT NULL DEFAULT 0;");
|
}
|
|
//Ìí¼ÓÖ¸¶¨µÄmsg_flagÁÐ(Åɵ¥¹ÜÀí)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_battalarm_data'"
|
+ " AND column_name='msg_flag'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Battalarm_Data_Table
|
+ " ADD COLUMN `msg_flag` tinyint NOT NULL DEFAULT 0;");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
/**
|
*
|
* @Title: createTb_Alarm_Message
|
* @Description: ¸æ¾¯¶ÌÐÅ
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Alarm_Message(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Alarm_Message;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Alarm_Message + " (" +
|
"`id` bigint(19) NOT NULL AUTO_INCREMENT," +
|
" `phone` varchar(15) DEFAULT NULL COMMENT '¶ÌÐÅ·¢ËÍÖÁÊÖ»úºÅ'," +
|
" `full_device_name` varchar(255) DEFAULT NULL COMMENT 'É豸ȫÃû'," +
|
" `alarm_time_category` varchar(255) DEFAULT NULL COMMENT '¸æ¾¯Ê±¼ä¶Î'," +
|
" `alarm_message` varchar(255) DEFAULT NULL COMMENT '¸æ¾¯ÐÅÏ¢'," +
|
" `alarm_ids` varchar(255) DEFAULT NULL COMMENT '¶ÌÐŶÔÓ¦µÄ¸æ¾¯id'," +
|
" `create_time` datetime DEFAULT NULL," +
|
" `update_time` datetime DEFAULT NULL COMMENT '¼Ç¼¸üÐÂʱ¼ä'," +
|
" `status` tinyint(1) DEFAULT NULL COMMENT '·¢ËÍ״̬,0-δ·¢ËÍ,1-·¢ËÍÍê³É,2-·¢ËÍʧ°Ü'," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='¸æ¾¯¶ÌÐÅ';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
/**
|
*
|
* @Title: createTb_Alarm_Message
|
* @Description: ¸æ¾¯¶ÌÐÅ
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Alarm_Detail_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Alarm_Detail_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Alarm_Detail_Table + " (" +
|
" `id` bigint(19) NOT NULL AUTO_INCREMENT," +
|
" `alm_id` varchar(15) DEFAULT NULL COMMENT '¸æ¾¯id'," +
|
" `alm_reason` text COMMENT '¸æ¾¯ÔÒò'," +
|
" `alm_advice` varchar(255) DEFAULT NULL COMMENT '¸æ¾¯½¨Òé'," +
|
" `alm_type` int(5) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯À´Ô´£º0µçÔ´£¬1É豸£¬2µç³Ø'," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='¸æ¾¯ÏêÇé';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
|
}
|
