package com.sql.util;
|
|
import java.sql.SQLException;
|
import java.util.Date;
|
|
import com.base.Com;
|
import com.sql.MysqlConnPool;
|
import com.sql.Sql_Mysql;
|
|
public class Db_User {
|
public static void init(MysqlConnPool pool) {
|
System.out.println("Db_User init Start " + Com.getDateTimeFormat(new Date(),Com.DTF_YMDhms));
|
|
createDb_User(pool);
|
|
createOperation_Log_Table(pool);
|
|
createTemp_Numbers_Table(pool);
|
|
createUser_Inf_Table(pool);
|
|
|
System.out.println("Db_User init End " + Com.getDateTimeFormat(new Date(),Com.DTF_YMDhms));
|
}
|
|
|
public static void createDb_User(MysqlConnPool pool) {
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
sql.sqlMysqlExecute("CREATE SCHEMA IF NOT EXISTS " + Sql_Mysql.DB_USER + " AUTHORIZATION sysdba");
|
} catch (SQLException e) {
|
e.printStackTrace();
|
}
|
}
|
|
|
/**
|
* ´´½¨ db_user.operation_log Êý¾Ý¿â±í
|
* @param conn
|
*/
|
public static void createOperation_Log_Table(MysqlConnPool pool)
|
{
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
String sql_str_auto = "CREATE SEQUENCE IF NOT EXISTS " + Sql_Mysql.Operation_Log_Table + "_auto" +
|
" INCREMENT 1" +
|
" MINVALUE 1" +
|
" MAXVALUE 9223372036854775807" +
|
" START 1" +
|
" CACHE 1;";
|
//´´½¨×ÔÔöÐòÁÐ
|
sql.sqlMysqlExecute(sql_str_auto);
|
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Operation_Log_Table + " "
|
+ "(num integer NOT NULL DEFAULT nextval('" + Sql_Mysql.Operation_Log_Table + "_auto'::regclass)," +
|
" user_id integer NOT NULL DEFAULT 1001," +
|
" user_name character varying(50) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT 'lxw'::character varying," +
|
" type1 integer NOT NULL DEFAULT 1," +
|
" type2 integer NOT NULL DEFAULT 2," +
|
" msg character varying(1000) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT '怬'::character varying," +
|
" detail character varying(1000) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT ''::character varying," +
|
" ip character varying(50) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT '192.168.10.1'::character varying," +
|
" create_time timestamp without time zone NOT NULL," +
|
" PRIMARY KEY (num)" +
|
")";
|
sql.sqlMysqlExecute(sql_str);
|
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Operation_Log_Table + " OWNER TO sysdba;");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".num IS 'Ö÷¼ünum';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".user_id IS 'Óû§id';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".user_name IS '̞';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".type1 IS 'ʼþÀàÐÍ:1-ϵͳ¼¶,2-ÒµÎñ¼¶';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".type2 IS 'ʼþÀàÐÍ:×Ó¼¶±ð';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".msg IS '»ù´¡ÐÅÏ¢';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".detail IS 'ÏêϸÐÅÏ¢';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".ip IS '²Ù×÷µÄip';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.Operation_Log_Table + ".create_time IS '´´½¨Ê±¼ä';");
|
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ db_user.temp_numbers Êý¾Ý¿â±í
|
* @param conn
|
*/
|
public static void createTemp_Numbers_Table(MysqlConnPool pool)
|
{
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
String sql_str_auto = "CREATE SEQUENCE IF NOT EXISTS " + Sql_Mysql.Temp_Numbers_Table + "_auto" +
|
" INCREMENT 1" +
|
" MINVALUE 1" +
|
" MAXVALUE 9223372036854775807" +
|
" START 1" +
|
" CACHE 1;";
|
//´´½¨×ÔÔöÐòÁÐ
|
sql.sqlMysqlExecute(sql_str_auto);
|
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.Temp_Numbers_Table + " "
|
+ "(unumber integer" +
|
")";
|
sql.sqlMysqlExecute(sql_str);
|
|
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.Temp_Numbers_Table + " OWNER TO sysdba;");
|
|
|
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
|
/**
|
* ´´½¨ db_user.user_inf Êý¾Ý¿â±í
|
* @param conn
|
*/
|
public static void createUser_Inf_Table(MysqlConnPool pool)
|
{
|
Sql_Mysql sql = new Sql_Mysql(pool.getConn());
|
try {
|
String sql_str_auto = "CREATE SEQUENCE IF NOT EXISTS " + Sql_Mysql.User_Inf_Table + "_auto" +
|
" INCREMENT 1" +
|
" MINVALUE 1" +
|
" MAXVALUE 9223372036854775807" +
|
" START 1" +
|
" CACHE 1;";
|
//´´½¨×ÔÔöÐòÁÐ
|
sql.sqlMysqlExecute(sql_str_auto);
|
|
String sql_str = " CREATE TABLE IF NOT EXISTS " + Sql_Mysql.User_Inf_Table + " "
|
+ "(uid bigint NOT NULL DEFAULT nextval('" + Sql_Mysql.User_Inf_Table + "_auto'::regclass)," +
|
" usnid character varying(1000) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT '123456'::character varying," +
|
" uname character varying(50) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT 'lxw'::character varying," +
|
" udownload_role integer NOT NULL DEFAULT 0," +
|
" role_id character varying(32) NOT NULL COLLATE pg_catalog.\"default\" DEFAULT '1002'::character varying," +
|
" PRIMARY KEY (uid)" +
|
")";
|
sql.sqlMysqlExecute(sql_str);
|
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.User_Inf_Table + " OWNER TO sysdba;");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.User_Inf_Table + ".uid IS 'Óû§id';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.User_Inf_Table + ".usnid IS 'rsa¼ÓÃÜÃÜÂë';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.User_Inf_Table + ".uname IS '̞';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.User_Inf_Table + ".udownload_role IS 'ÏÂÔØȨÏÞ';");
|
|
sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.User_Inf_Table + " ADD COLUMN IF NOT EXISTS role_id VARCHAR not null DEFAULT '1001';");
|
sql.sqlMysqlExecute("COMMENT ON COLUMN " + Sql_Mysql.User_Inf_Table + ".role_id IS 'Óû§Éí·Ý£¨1001ÆÕͨÓû§£¬1002¹ÜÀíÔ±£©';");
|
} catch (Exception e) {
|
e.printStackTrace();
|
} finally {
|
sql.close_con();
|
}
|
}
|
}
|
