package com.database_util;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class DB_App_Sys {
|
|
public static void init(MysqlConnPool pool, boolean recreate) {
|
createDB_AppSys(pool);
|
|
createPage_Param_Table(pool, recreate);
|
|
createFbsdev_Statechange_Inf_Table(pool, recreate);
|
|
createDashboard_Module_Chart_Table(pool, recreate);
|
|
createDashboard_Module_Table(pool, recreate);
|
|
createDashboard_Chart_Type_Table(pool, recreate);
|
|
createApp_Sys_Table(pool, recreate);
|
|
createApp_Bts_Comm_Task_Table(pool, recreate);
|
|
createApp_Bts_Battgroup_Table(pool, recreate);
|
|
createMenu_Table(pool, recreate);
|
|
}
|
|
/**
|
* ´´½¨ db_app_sys Êý¾Ý¿â
|
* @param pool
|
*/
|
public static void createDB_AppSys(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_AppSys);
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_page_param ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createPage_Param_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Page_Param_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Page_Param_Table + " (" +
|
" id int(11) NOT NULL AUTO_INCREMENT," +
|
" param varchar(30) DEFAULT NULL COMMENT '¾ßÌå²ÎÊý'," +
|
" status int(1) DEFAULT NULL COMMENT '²ÎÊýÊÇ·ñÒ³ÃæÏÔʾ'," +
|
" categoryId int(1) DEFAULT NULL COMMENT '²ÎÊý·ÖÀà,ÀàÄ¿1,ÀàÄ¿2'," +
|
" PRIMARY KEY (id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=21 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_fbsdev_statechange_inf ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createFbsdev_Statechange_Inf_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Fbsdev_Statechange_Inf_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Fbsdev_Statechange_Inf_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id int(11) NOT NULL DEFAULT '0'," +
|
" rec_time datetime NOT NULL DEFAULT '1980-01-01 00:00:00'," +
|
" last_stat int(11) NOT NULL DEFAULT '0'," +
|
" now_stat int(11) NOT NULL DEFAULT '0'," +
|
" state_change_reason int(11) NOT NULL DEFAULT '0'," +
|
" dev_alarm int(11) NOT NULL DEFAULT '0'," +
|
" eve_type int(11) NOT NULL DEFAULT '0'," +
|
" module_num int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_dev_id (dev_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();
|
}
|
}
|
|
|
/**
|
* ´´½¨ tb_dashboard_module_chart ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDashboard_Module_Chart_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Dashboard_Module_Chart_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Dashboard_Module_Chart_Table + " (" +
|
" id int(11) NOT NULL AUTO_INCREMENT," +
|
" module_id int(11) NOT NULL DEFAULT '0' COMMENT 'Ä£¿éid'," +
|
" title varchar(255) NOT NULL DEFAULT ''," +
|
" label varchar(20) NOT NULL DEFAULT ''," +
|
" type_id int(11) NOT NULL DEFAULT '1' COMMENT 'ͼ±êÀàÐÍ:1.ºáÏòÖù״ͼ;2.ÊúÏòÖù״ͼ;3.ÕÛÏßͼ;4.±ýͼ'," +
|
" page_id int(11) NOT NULL DEFAULT '1' COMMENT 'Ò³Ãæid'," +
|
" PRIMARY KEY (id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=24 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_dashboard_module ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDashboard_Module_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Dashboard_Module_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Dashboard_Module_Table + " (" +
|
" id int(11) NOT NULL AUTO_INCREMENT," +
|
" x int(11) DEFAULT NULL DEFAULT '1' COMMENT 'x×ø±ê'," +
|
" y int(11) DEFAULT NULL DEFAULT '0' COMMENT 'y×ø±ê'," +
|
" w int(11) DEFAULT NULL DEFAULT '0' COMMENT '¿í¶È'," +
|
" h int(11) DEFAULT NULL DEFAULT '0' COMMENT '¸ß¶È'," +
|
" module_id int(11) DEFAULT '0' COMMENT 'Ä£¿éId'," +
|
" title varchar(255) DEFAULT '' COMMENT 'Ä£¿é±êÌâ'," +
|
" label varchar(255) DEFAULT '' COMMENT 'Ó¢ÎÄ'," +
|
" type varchar(20) DEFAULT '' COMMENT 'ͼ±êÀàÐÍ:1.ºáÏòÖù״ͼ;2.ÊúÏòÖù״ͼ;3.ÕÛÏßͼ;4.±ýͼ'," +
|
" page_id int(11) DEFAULT '0' COMMENT 'Ò³ÃæId'," +
|
" user_id int(11) DEFAULT '0' COMMENT 'ËùÊôÓû§'," +
|
" PRIMARY KEY (id) USING BTREE" +
|
") 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_dashboard_chart_type ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createDashboard_Chart_Type_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Dashboard_Chart_Type_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Dashboard_Chart_Type_Table + " (" +
|
" id int(11) NOT NULL AUTO_INCREMENT," +
|
" type_id int(11) DEFAULT '1'," +
|
" type_name varchar(20) DEFAULT ''," +
|
" PRIMARY KEY (id) USING BTREE" +
|
") ENGINE=InnoDB AUTO_INCREMENT=6 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_app_sys ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createApp_Sys_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.App_Sys_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.App_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_BattGroupData_EN tinyint(1) NOT NULL DEFAULT '0'," +
|
" AppServer_Reinit_BattGroupData_LD9_EN tinyint(1) NOT NULL DEFAULT '0'," +
|
" AppServer_Reinit_BattGroupData_A059_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);
|
//Ìí¼Ó³ä·ÅµçÒ»Ìå»úÐîµç³Ø×é¼Èë״̬
|
res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
|
+ " WHERE table_schema='db_app_sys'"
|
+ " AND table_name='tb_app_sys'"
|
+ " AND column_name='AppServer_Reinit_BattGroupData_A059_EN'");
|
if(false == res.next()) {
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.App_Sys_Table
|
+ " ADD COLUMN AppServer_Reinit_BattGroupData_A059_EN tinyint(1) NOT NULL DEFAULT '0';");
|
}
|
|
} catch (SQLException e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_app_bts_comm_task ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createApp_Bts_Comm_Task_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.App_Bts_Comm_Task_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.App_Bts_Comm_Task_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" thread_id bigint(20) NOT NULL DEFAULT '0'," +
|
" dev_id bigint(20) NOT NULL DEFAULT '0'," +
|
" dev_mcu_uid varchar(48) NOT NULL DEFAULT 'null'," +
|
" battgroup_cnt int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_id1 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_index1 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_id2 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_index2 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_id3 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_index3 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_id4 int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_index4 int(11) NOT NULL DEFAULT '0'," +
|
" connet_time varchar(20) NOT NULL DEFAULT '1980-01-01 01:01:00'," +
|
" dev_comm_runtime varchar(20) NOT NULL DEFAULT '1980-01-01 01:01:00'," +
|
" break_type int(11) NOT NULL DEFAULT '0'," +
|
" PRIMARY KEY (num)," +
|
" KEY index_dev_id_uniq (dev_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_app_bts_battgroup ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createApp_Bts_Battgroup_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.App_Bts_Battgroup_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.App_Bts_Battgroup_Table + " (" +
|
" num bigint(20) NOT NULL AUTO_INCREMENT," +
|
" dev_id bigint(20) NOT NULL DEFAULT '0'," +
|
" battgroup_id int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_index int(11) NOT NULL DEFAULT '-1'," +
|
" battgroup_mon_cnt int(11) NOT NULL DEFAULT '0'," +
|
" battgroup_float_vol float NOT NULL DEFAULT '0'," +
|
" battgroup_float_curr float NOT NULL DEFAULT '0'," +
|
" battgroup_register_code varchar(48) NOT NULL DEFAULT 'null'," +
|
" 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();
|
}
|
}
|
|
/**
|
* ´´½¨ tb_menu ±í
|
* @param pool
|
* @param recreate
|
*/
|
public static void createMenu_Table(MysqlConnPool pool, boolean recreate) {
|
String sql_str01 = " DROP TABLE IF EXISTS "+Sql_Mysql.Menu_Table;
|
String sql_str02 = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Menu_Table + " (" +
|
" `id` int(11) NOT NULL," +
|
" `label` varchar(50) DEFAULT NULL COMMENT 'ÖÐÎÄÃû³Æ'," +
|
" `name` varchar(100) DEFAULT NULL COMMENT 'Ãû³Æ'," +
|
" `src` varchar(100) DEFAULT NULL COMMENT '·¾¶'," +
|
" `icon` varchar(100) DEFAULT NULL COMMENT 'ͼ±ê'," +
|
" `closable` tinyint(4) DEFAULT NULL COMMENT 'ÊÇ·ñ¿É¹Ø±Õ'," +
|
" `enableduse` tinyint(4) DEFAULT NULL COMMENT 'ÊÇ·ñÆôÓÃ'," +
|
" `permitName` varchar(100) DEFAULT NULL COMMENT 'ȨÏÞ'," +
|
" `level` tinyint(4) DEFAULT NULL COMMENT '²Ëµ¥µÈ¼¶'," +
|
" `ord` tinyint(4) DEFAULT NULL COMMENT 'ÅÅÐò'," +
|
" `menuId` int(11) DEFAULT NULL COMMENT 'Éϼ¶²Ëµ¥id'," +
|
" PRIMARY KEY (`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();
|
}
|
}
|
}
|
