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);
|
|
System.out.println(" db_pwrdev_alarm init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
}
|
|
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();
|
}
|
}
|
}
|
