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_Pwrdev_Alarm {
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
System.out.println(" db_pwrdev_alarm start end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
createDB_Pwrdev_Alarm (pool);
|
|
createPwrdev_Alarm_Table(pool, recreate);
|
|
createPwrdev_Alarm_History_Table(pool, recreate);
|
|
createPwrdev_Alarm_Param_Table(pool, recreate);
|
|
createPwrdev_Alarm_Config_Table(pool, recreate);
|
|
createPwrdev_Time_Adj_Alarm_Table(pool, recreate);
|
|
createPwrdev_Time_Out_Alarm_Table(pool, recreate);
|
|
|
System.out.println(" db_pwrdev_alarm init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
}
|
|
private static void createPwrdev_Time_Out_Alarm_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Time_Out_Alarm_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Time_Out_Alarm_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alarm_typeId` int(11) NOT NULL DEFAULT '0'," +
|
" `alarm_index` int(11) NOT NULL DEFAULT '0'," +
|
" `timeout_tag` int(11) NOT NULL DEFAULT '0'," +
|
" `read_datetime` datetime NOT NULL DEFAULT '2005-01-01 00:00:00'," +
|
" `system_datetime` datetime NOT NULL DEFAULT '2005-01-01 00:00:00'," +
|
" `resume_datetime` datetime NOT NULL DEFAULT '2005-01-01 00:00:00'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_powerdev_id` (`PowerDeviceId`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=65 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();
|
}
|
}
|
private static void createPwrdev_Time_Adj_Alarm_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Time_Adj_Alarm_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Time_Adj_Alarm_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alarm_typeId` int(11) NOT NULL DEFAULT '0'," +
|
" `detect_datetime` datetime NOT NULL DEFAULT '2005-01-01 00:00:00'," +
|
" `record_datetime` datetime NOT NULL DEFAULT '2005-01-01 00:00:00'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_powerdev_id` (`PowerDeviceId`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=38 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();
|
}
|
}
|
private static void createPwrdev_Alarm_Config_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Alarm_Config_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Alarm_Config_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `SMS_CommPort` varchar(10) NOT NULL DEFAULT ' '," +
|
" `SMS_CommPort_ReInit` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `Beeper_CommPort` varchar(10) NOT NULL DEFAULT ' '," +
|
" `Beeper_CommPort_ReInit` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `SMS_Alarm_Text` varchar(100) NOT NULL DEFAULT ' '," +
|
" `SMS_Alarm_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `Beeper_Alarm_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `Beeper_Sound_OFF` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `AlarmDevIP` varchar(30) NOT NULL DEFAULT '192.168.10.223'," +
|
" `SMS_Alarm_ipPort` int(11) NOT NULL DEFAULT '26'," +
|
" `Beeper_Alarm_ipPort` int(11) NOT NULL DEFAULT '27'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 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();
|
}
|
}
|
private static void createPwrdev_Alarm_Param_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Alarm_Param_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Alarm_Param_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `alarm_id` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alarm_limitH` float NOT NULL DEFAULT '0'," +
|
" `alarm_limitL` float NOT NULL DEFAULT '0'," +
|
" `alarm_limitEn` int(11) NOT NULL DEFAULT '0' COMMENT 'ÊÇ·ñ¿ÉÒÔÉèÖÃÉÏÏÂÏÞ'," +
|
" `UserAlarm_EN` int(11) NOT NULL DEFAULT '1' COMMENT '¸æ¾¯¹æÔòÊÇ·ñÆô¶¯'," +
|
" `alarm_level` int(11) NOT NULL DEFAULT '0'," +
|
" `alarm_devtype` int(11) NOT NULL DEFAULT '0'," +
|
" `alarm_name` varchar(100) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)," +
|
" UNIQUE KEY `unique_alarm_id` (`alarm_id`)," +
|
" KEY `index_alarm_id` (`alarm_id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=1 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();
|
}
|
}
|
|
/**
|
* ´´½¨ db_pwrdev_alarm Êý¾Ý¿â
|
* @param pool
|
*/
|
private static void createDB_Pwrdev_Alarm(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_PWRDEV_ALARM);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrdev_alarm ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrdev_Alarm_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Alarm_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Alarm_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `record_id` bigint(20) NOT NULL DEFAULT '0'," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alm_type` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_level` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_source` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_index` 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_pwrdev_id` (`PowerDeviceId`)," +
|
" 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=44 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_source
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_alarm'"
|
+ " AND table_name='tb_pwrdev_alarm'"
|
+ " AND column_name='alm_source'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Alarm_Table
|
+ " ADD COLUMN `alm_source` int(11) NOT NULL DEFAULT '0'");
|
}
|
|
//Ìí¼Ó alm_index
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_alarm'"
|
+ " AND table_name='tb_pwrdev_alarm'"
|
+ " AND column_name='alm_index'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Alarm_Table
|
+ " ADD COLUMN `alm_index` int(11) NOT NULL DEFAULT '0'");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrdev_alarm_history ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrdev_Alarm_History_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Alarm_History_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Alarm_History_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `record_id` bigint(20) NOT NULL DEFAULT '0'," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `alm_type` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_level` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_source` int(11) NOT NULL DEFAULT '0'," +
|
" `alm_index` 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_pwrdev_id` (`PowerDeviceId`)," +
|
" 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=43 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_source
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_alarm'"
|
+ " AND table_name='tb_pwrdev_alarm_history'"
|
+ " AND column_name='alm_source'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Alarm_History_Table
|
+ " ADD COLUMN `alm_source` int(11) NOT NULL DEFAULT '0'");
|
}
|
|
//Ìí¼Ó alm_index
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_alarm'"
|
+ " AND table_name='tb_pwrdev_alarm_history'"
|
+ " AND column_name='alm_index'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Alarm_History_Table
|
+ " ADD COLUMN `alm_index` int(11) NOT NULL DEFAULT '0'");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
