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); //´´½¨¸æ¾¯ÏêÇé
|
|
createTb_Alarm_Relation_Table(pool, recreate); //´´½¨¸æ¾¯¶ÔÓ¦¹Øϵ
|
|
createTb_Alarm_Manual_Clear_Table(pool,recreate);
|
|
createTb_Battalarm_Data_Verify_Table(pool, recreate); //´´½¨µç³Ø¸æ¾¯È·ÈÏ´¦Àí±í
|
|
createTb_Devalarm_Data_Verify_Table(pool, recreate); //´´½¨É豸¸æ¾¯È·ÈÏ´¦Àí±í
|
|
repairBattAlarmDataHistoryTable(pool); //µç³Ø¸æ¾¯·Ö±íÐÂÔöÊÇ·ñ½ô¼±×Ö¶Î
|
|
repairDevAlarmDataHistoryTable(pool); //É豸¸æ¾¯·Ö±íÐÂÔöÊÇ·ñ½ô¼±×Ö¶Î
|
|
System.out.println(" db_alarm init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
}
|
|
/**
|
* ÐÞ¸´É豸ÀúÊ·¸æ¾¯·Ö±íÖÐȱÉÙÐÂÔö×ֶΠ`alm_severity`-'¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'
|
* @param pool
|
*/
|
private static void repairDevAlarmDataHistoryTable(MysqlConnPool pool) {
|
String sql_str_sel = " select TABLE_NAME,CREATE_TIME from information_schema.tables " +
|
" where table_schema='db_alarm' " +
|
" AND TABLE_NAME like 'tb_devalarm_data_history_%';";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res1 = null;
|
ResultSet res2= null;
|
try {
|
res1 = sql.sqlMysqlTotalQuery(sql_str_sel);
|
while(res1.next()) {
|
String tablename = res1.getString("TABLE_NAME");
|
//System.out.println("±íÃû£º"+tablename);
|
|
res2 = null;
|
// //Ìí¼ÓÖ¸¶¨µÄalm_severityÁÐ'¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'
|
res2 = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='" + tablename + "'"
|
+ " AND column_name='alm_severity'");
|
if(false == res2.next()) {
|
sql.sqlMysqlExecute(" ALTER TABLE db_alarm." + tablename
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res1) {
|
try {
|
res1.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
if(null != res2) {
|
try {
|
res1.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* ÐÞ¸´µç³ØÀúÊ·¸æ¾¯·Ö±íÖÐȱÉÙÐÂÔö×ֶΠ`alm_severity`-'¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'
|
* @param pool
|
*/
|
private static void repairBattAlarmDataHistoryTable(MysqlConnPool pool) {
|
String sql_str_sel = " select TABLE_NAME,CREATE_TIME from information_schema.tables " +
|
" where table_schema='db_alarm' " +
|
" AND TABLE_NAME like 'tb_battalarm_data_history_%';";
|
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res1 = null;
|
ResultSet res2= null;
|
try {
|
res1 = sql.sqlMysqlTotalQuery(sql_str_sel);
|
while(res1.next()) {
|
String tablename = res1.getString("TABLE_NAME");
|
//System.out.println("±íÃû£º"+tablename);
|
|
res2 = null;
|
// //Ìí¼ÓÖ¸¶¨µÄalm_severityÁÐ'¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'
|
res2 = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='" + tablename + "'"
|
+ " AND column_name='alm_severity'");
|
if(false == res2.next()) {
|
sql.sqlMysqlExecute(" ALTER TABLE db_alarm." + tablename
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res1) {
|
try {
|
res1.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
if(null != res2) {
|
try {
|
res1.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
|
}
|
|
/**
|
* ´´½¨ 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'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" 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';");
|
}
|
//Ìí¼ÓÖ¸¶¨µÄalm_severityÁÐ'¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data_history'"
|
+ " AND column_name='alm_severity'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data__History_Table
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
} 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'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" 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;");
|
}
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄalm_severityÁÐ(¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±])
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data'"
|
+ " AND column_name='alm_severity'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Devalarm_Data_Table
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
} 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'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" 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());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄalm_severityÁÐ(¸æ¾¯ÖØÒªµÈ¼¶)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_battalarm_data_history'"
|
+ " AND column_name='alm_severity'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Battalarm_Data_History_Table
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
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'," +
|
" `work_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `msg_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" 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;");
|
}
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄ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;");
|
}
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄalm_severity ÁÐ(¸æ¾¯ÖØÒªµÈ¼¶)
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_battalarm_data'"
|
+ " AND column_name='alm_severity'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Battalarm_Data_Table
|
+ " ADD COLUMN `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
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_Detail_Table
|
* @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µç³Ø'," +
|
" `highorlow` int(5) DEFAULT '0' COMMENT '¸ßµÍ¸æ¾¯0ĬÈÏ1µÍ2¸ß'," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='¸æ¾¯ÏêÇé';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄhighorlowÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_alarm_detail'"
|
+ " AND column_name='highorlow'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Alarm_Detail_Table
|
+ " ADD COLUMN `highorlow` int(5) DEFAULT '0' COMMENT '¸ßµÍ¸æ¾¯0ĬÈÏ1µÍ2¸ß';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
*
|
* @Title: createTb_Alarm_Relation_Table
|
* @Description:¸æ¾¯¶ÔÓ¦¹Øϵ
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Alarm_Relation_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Alarm_Relation_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Alarm_Relation_Table + " (" +
|
" `id` bigint(19) NOT NULL AUTO_INCREMENT," +
|
" `alm_id` varchar(255) DEFAULT NULL COMMENT '¸æ¾¯id'," +
|
" `alm_name` varchar(255) DEFAULT NULL COMMENT '¸æ¾¯Ãû³Æ'," +
|
" `alm_type` int(5) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯À´Ô´£º0µçÔ´£¬1É豸£¬2µç³Ø'," +
|
" `alm_point` varchar(255) DEFAULT '' COMMENT '¸æ¾¯µã'," +
|
" `highorlow` int(5) DEFAULT '0' COMMENT '¸ßµÍ¸æ¾¯0ĬÈÏ1µÍ2¸ß'," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=92 DEFAULT CHARSET=utf8mb4 COMMENT='¸æ¾¯¶ÔÓ¦¹Øϵ';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
|
|
//Ìí¼ÓÖ¸¶¨µÄhighorlowÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_alarm_relation'"
|
+ " AND column_name='alm_ponit'");
|
if(res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Alarm_Relation_Table
|
+ " CHANGE alm_ponit alm_point varchar(255) DEFAULT '' COMMENT '¸æ¾¯µã';");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/**
|
*
|
* @Title: create tb_alarm_manual_clear
|
* @Description:¸æ¾¯¶ÔÓ¦¹Øϵ
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Alarm_Manual_Clear_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Alarm_Manual_Clear_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Alarm_Manual_Clear_Table + " (" +
|
" `id` int(11) NOT NULL AUTO_INCREMENT," +
|
" `station_id` varchar(10) DEFAULT NULL," +
|
" `station_name1` varchar(45) DEFAULT NULL," +
|
" `station_name2` varchar(45) DEFAULT NULL," +
|
" `station_name3` varchar(45) DEFAULT NULL," +
|
" `station_name4` varchar(45) DEFAULT NULL," +
|
" `station_name5` varchar(45) DEFAULT NULL," +
|
" `alarm_type` tinyint(4) DEFAULT NULL COMMENT '¸æ¾¯ÀàÐÍ:1-µç³Ø,2-É豸,3-µçÔ´'," +
|
" `batt_group_id` int(11) DEFAULT NULL," +
|
" `batt_group_name` varchar(45) DEFAULT NULL," +
|
" `mon_num` int(11) DEFAULT NULL," +
|
" `device_id` int(11) DEFAULT NULL," +
|
" `device_name` varchar(45) DEFAULT NULL," +
|
" `power_device_id` int(11) DEFAULT NULL," +
|
" `alm_start_time` datetime DEFAULT NULL," +
|
" `alm_id` int(11) DEFAULT NULL," +
|
" `alm_signal_id` int(11) DEFAULT NULL," +
|
" `reason` varchar(255) DEFAULT NULL COMMENT '»Ö¸´ÔÒò'," +
|
" `file_url` varchar(255) DEFAULT NULL COMMENT 'ÉÏ´«µÄ¶à¸öÎļþ·¾¶,ÓÃ;Æ´½Ó'," +
|
" `create_time` datetime DEFAULT NULL," +
|
" `deal_user_id` int(11) DEFAULT NULL," +
|
" `main_id` int(11) DEFAULT NULL COMMENT 'ÉóÅúÁ÷³ÌÖ÷±íid'," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB 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);
|
|
//Ìí¼ÓÖ¸¶¨µÄ station_name4
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_alarm_manual_clear'"
|
+ " AND column_name='station_name4'");
|
if(!res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Alarm_Manual_Clear_Table
|
+ " ADD COLUMN `station_name4` varchar(45) DEFAULT NULL;");
|
}
|
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄ file_url
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_alarm_manual_clear'"
|
+ " AND column_name='file_url'");
|
if(!res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Alarm_Manual_Clear_Table
|
+ " ADD COLUMN `file_url` varchar(255) DEFAULT NULL COMMENT 'ÉÏ´«µÄ¶à¸öÎļþ·¾¶,ÓÃ;Æ´½Ó';");
|
}
|
|
res = null;
|
//Ìí¼ÓÖ¸¶¨µÄ main_id
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_alarm_manual_clear'"
|
+ " AND column_name='main_id'");
|
if(!res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Alarm_Manual_Clear_Table
|
+ " ADD COLUMN `main_id` int(11) DEFAULT NULL COMMENT 'ÉóÅúÁ÷³ÌÖ÷±íid';");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/**
|
*
|
* @Title: create tb_battalarm_data_verify
|
* @Description:µç³Ø¸æ¾¯È·ÈÏ´¦Àí±í
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Battalarm_Data_Verify_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Battalarm_Data_Verify_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Battalarm_Data_Verify_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'," +
|
" `work_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `msg_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '0' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" `alm_solekey` int(11) NOT NULL DEFAULT '1' COMMENT 'ʵʱ¸æ¾¯Ö÷¼ü'," +
|
" 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 DEFAULT CHARSET=utf8 COMMENT='µç³Ø¸æ¾¯È·ÈÏ´¦Àí±í';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
|
//Ìí¼ÓÖ¸¶¨µÄhighorlowÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_battalarm_data_verify'"
|
+ " AND column_name='alm_solekey'");
|
if(!res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Battalarm_Data_Verify_Table
|
+ " ADD COLUMN `alm_solekey` int(11) NOT NULL DEFAULT '1' COMMENT 'ʵʱ¸æ¾¯Ö÷¼ü';");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
/**
|
*
|
* @Title: create tb_devalarm_data_verify
|
* @Description:É豸¸æ¾¯È·ÈÏ´¦Àí±í
|
* @param pool
|
* @param recreate
|
* @author author
|
* @date 2021Äê9ÔÂ6ÈÕ
|
*/
|
public static void createTb_Devalarm_Data_Verify_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Tb_Devalarm_Data_Verify_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Tb_Devalarm_Data_Verify_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'," +
|
" `work_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `msg_flag` tinyint(4) NOT NULL DEFAULT '0'," +
|
" `alm_severity` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯ÖØÒªµÈ¼¶[0:ÖØÒª£»1:½ô¼±]'," +
|
" `alm_solekey` int(11) NOT NULL DEFAULT '1' COMMENT 'ʵʱ¸æ¾¯Ö÷¼ü'," +
|
" 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=16726 DEFAULT CHARSET=utf8 COMMENT='É豸¸æ¾¯È·ÈÏ´¦Àí±í';";
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
ResultSet res = null;
|
try {
|
if(true == recreate) {
|
sql.sqlMysqlExecute(sql_str01);
|
}
|
sql.sqlMysqlExecute(sql_str02);
|
|
//Ìí¼ÓÖ¸¶¨µÄhighorlowÁÐ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_alarm'"
|
+ " AND table_name='tb_devalarm_data_verify'"
|
+ " AND column_name='alm_solekey'");
|
if(!res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Tb_Devalarm_Data_Verify_Table
|
+ " ADD COLUMN `alm_solekey` int(11) NOT NULL DEFAULT '1' COMMENT 'ʵʱ¸æ¾¯Ö÷¼ü';");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
|
}
|
