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_Inf {
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
System.out.println(" db_pwrdev_inf init start at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
|
createDB_PWRDEV_INF (pool);
|
|
createPwrdev_Inf_Table(pool, recreate);
|
|
createPwrapp_Sys_Table(pool, recreate);
|
|
createPwrapp_Stainf_Table(pool, recreate);
|
|
createPwrapp_Dcinf_Table(pool, recreate);
|
|
createPwrapp_Acinf_Table(pool, recreate);
|
|
createPwrapp_Acdcinf_Table(pool, recreate);
|
|
createDisplay_Config_Table(pool, recreate);
|
|
System.out.println(" db_pwrdev_inf init end at " + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms));
|
}
|
|
/**
|
* ´´½¨ db_pwrdev_inf Êý¾Ý¿â
|
* @param pool
|
*/
|
private static void createDB_PWRDEV_INF(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_PWRDEV_INF);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrdev_inf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrdev_Inf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrdev_Inf_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrdev_Inf_Table + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `StationId` varchar(20) NOT NULL DEFAULT '0'," +
|
" `StationName` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName1` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName2` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName3` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName4` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName5` varchar(100) NOT NULL DEFAULT ' '," +
|
" `StationName6` varchar(100) DEFAULT ' '," +
|
" `StationName7` varchar(100) DEFAULT ' '," +
|
" `StationName8` varchar(100) DEFAULT ' '," +
|
" `StationName9` varchar(100) DEFAULT ' '," +
|
" `StationIP` varchar(20) NOT NULL DEFAULT '0'," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `PowerDeviceIp` varchar(50) NOT NULL DEFAULT '192.168.0.88'," +
|
" `PowerDeviceName` varchar(100) NOT NULL DEFAULT '0'," +
|
" `ProtocolName` varchar(100) NOT NULL DEFAULT '0'," +
|
" `DeviceId` varchar(20) DEFAULT '0'," +
|
" `DeviceName` varchar(100) DEFAULT '0'," +
|
" `model` varchar(45) DEFAULT NULL COMMENT 'ÐͺÅ'," +
|
" `PowerProducer` varchar(45) NOT NULL DEFAULT '0'," +
|
" `PowerProductDate` date NOT NULL DEFAULT '2006-07-06'," +
|
" `PowerInUseDate` date NOT NULL DEFAULT '2006-07-06'," +
|
" `runCompany` varchar(45) NOT NULL DEFAULT ''," +
|
" `mId` bigint(20) DEFAULT '0' COMMENT '¶þάÂëÐÅÏ¢±êʶλ'," +
|
" `route_num` tinyint(4) DEFAULT '1' COMMENT '½»Á÷ABC¼¸Â·ÉèÖÃ'," +
|
" `exception_cause` varchar(45) DEFAULT NULL COMMENT 'Òì³£ÔÒò'," +
|
" `exception_cause_analysis` varchar(255) DEFAULT NULL COMMENT 'Òì³£ÔÒò·ÖÎö'," +
|
" `acvol_high_limit` float NOT NULL DEFAULT '200' COMMENT '½»Á÷ÉÏÏÞãÐÖµ'," +
|
" `acvol_low_limit` float NOT NULL DEFAULT '200' COMMENT '½»Á÷ÏÂÏÞãÐÖµ'," +
|
" `dcoutvol_low_limit` float NOT NULL DEFAULT '43.2' COMMENT 'Ö±Á÷Êä³öµçѹÏÂÏÞãÐÖµ'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_station_id` (`StationId`)," +
|
" KEY `index_powerdev_id` (`PowerDeviceId`)," +
|
" KEY `index_station_name` (`StationName`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=141 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);
|
|
//Ìí¼Ó×Ö¶ÎrunCompany
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='runCompany'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `runCompany` varchar(45) NOT NULL DEFAULT '';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶ÎmId ----- ¶þάÂëÐÅÏ¢±êʶλ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='mId'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `mId` bigint(20) DEFAULT '0' COMMENT '¶þάÂëÐÅÏ¢±êʶλ';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îroute_num ----- ½»Á÷ABC¼¸Â·ÉèÖÃ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='route_num'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `route_num` tinyint(4) DEFAULT '1' COMMENT '½»Á÷ABC¼¸Â·ÉèÖÃ';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îexception_cause ----- Òì³£ÔÒò
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='exception_cause'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `exception_cause` varchar(45) DEFAULT NULL COMMENT 'Òì³£ÔÒò';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îexception_cause_analysis ----- Òì³£ÔÒò·ÖÎö
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='exception_cause_analysis'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `exception_cause_analysis` varchar(255) DEFAULT NULL COMMENT 'Òì³£ÔÒò·ÖÎö';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îacvol_high_limit ----- ½»Á÷ÉÏÏÞãÐÖµ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='acvol_high_limit'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `acvol_high_limit` float NOT NULL DEFAULT '200' COMMENT '½»Á÷ÉÏÏÞãÐÖµ';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îacvol_low_limit ----- ½»Á÷ÏÂÏÞãÐÖµ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='acvol_low_limit'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `acvol_low_limit` float NOT NULL DEFAULT '200' COMMENT '½»Á÷ÏÂÏÞãÐÖµ';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îdcoutvol_low_limit ----- Ö±Á÷Êä³öµçѹÏÂÏÞãÐÖµ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='dcoutvol_low_limit'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `dcoutvol_low_limit` float NOT NULL DEFAULT '43.2' COMMENT 'Ö±Á÷Êä³öµçѹÏÂÏÞãÐÖµ';");
|
}
|
res = null;
|
//Ìí¼Ó×Ö¶Îmodel ----- µçÔ´ÐͺÅ
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrdev_inf'"
|
+ " AND column_name='model'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrdev_Inf_Table
|
+ " ADD COLUMN `model` varchar(45) DEFAULT NULL COMMENT 'ÐͺÅ' AFTER `DeviceName`;");
|
}
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
if(null != res) {
|
try {
|
res.close();
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrapp_sys ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrapp_Sys_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrapp_Sys_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrapp_Sys_Table + " (" +
|
" `num` int(11) NOT NULL AUTO_INCREMENT," +
|
" `SqlDB_Version` float NOT NULL DEFAULT '1'," +
|
" `SqlDB_BackUpTime` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," +
|
" `SqlDB_BackUpManual_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `AppServer_Reinit_PowerData_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `AppServer_Reinit_BattGroupData_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `AppServer_Reinit_Config_EN` tinyint(1) NOT NULL DEFAULT '0'," +
|
" `AppServer_Version` float NOT NULL DEFAULT '1'," +
|
" `AppClient_Version` float NOT NULL DEFAULT '1'," +
|
" `AppName` varchar(50) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=2 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);
|
|
//Ìí¼Ó×Ö¶ÎAppServer_Reinit_PowerData_Charger_EN
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_pwrdev_inf'"
|
+ " AND table_name='tb_pwrapp_sys'"
|
+ " AND column_name='AppServer_Reinit_BattGroupData_EN'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Pwrapp_Sys_Table
|
+ " ADD COLUMN `AppServer_Reinit_BattGroupData_EN` tinyint(1) 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_pwrapp_stainf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrapp_Stainf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrapp_Stainf_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrapp_Stainf_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `RunCompany` varchar(100) NOT NULL DEFAULT ' '," +
|
" `HistoryAlarmRelatRecord` varchar(100) NOT NULL DEFAULT ' '," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`)" +
|
") 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_pwrapp_dcinf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrapp_Dcinf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrapp_Dcinf_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrapp_Dcinf_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `InputSWCount` int(11) NOT NULL DEFAULT '0'," +
|
" `OutputSWCount` int(11) NOT NULL DEFAULT '0'," +
|
" `Cap` int(11) NOT NULL DEFAULT '0'," +
|
" `UsePurPose` varchar(100) NOT NULL DEFAULT ' '," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`)" +
|
") 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();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrapp_acinf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrapp_Acinf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrapp_Acinf_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrapp_Acinf_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `InputSWCount` int(11) NOT NULL DEFAULT '0'," +
|
" `OutputSWCount` int(11) NOT NULL DEFAULT '0'," +
|
" `Cap` int(11) NOT NULL DEFAULT '0'," +
|
" `UsePurPose` varchar(100) NOT NULL DEFAULT ' '," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`)" +
|
") 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();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_pwrapp_acdcinf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPwrapp_Acdcinf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Pwrapp_Acdcinf_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Pwrapp_Acdcinf_Table + " (" +
|
" `num` bigint(20) NOT NULL AUTO_INCREMENT," +
|
" `PowerDeviceId` bigint(20) NOT NULL DEFAULT '0'," +
|
" `ModuleInUseCount` int(11) NOT NULL DEFAULT '0'," +
|
" `ModuleCap` int(11) NOT NULL DEFAULT '0'," +
|
" `ModuleSlotCount` int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (`num`)," +
|
" KEY `index_pwrdev_id` (`PowerDeviceId`)" +
|
") 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();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_display_config ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDisplay_Config_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Display_Config_Table;
|
String sql_str02 = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Display_Config_Table + " (" +
|
" `id` int(11) NOT NULL AUTO_INCREMENT," +
|
" `name` varchar(45) NOT NULL COMMENT '±êʶ,Ãû³Æ'," +
|
" `type` int(11) NOT NULL COMMENT 'ͼÐÎÀàÐÍ'," +
|
" `user_id` int(11) NOT NULL," +
|
" PRIMARY KEY (`id`)" +
|
") ENGINE=InnoDB AUTO_INCREMENT=29 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();
|
}
|
}
|
|
}
|
