001 /**
002 * Licensed to the Apache Software Foundation (ASF) under one or more
003 * contributor license agreements. See the NOTICE file distributed with
004 * this work for additional information regarding copyright ownership.
005 * The ASF licenses this file to You under the Apache License, Version 2.0
006 * (the "License"); you may not use this file except in compliance with
007 * the License. You may obtain a copy of the License at
008 *
009 * http://www.apache.org/licenses/LICENSE-2.0
010 *
011 * Unless required by applicable law or agreed to in writing, software
012 * distributed under the License is distributed on an "AS IS" BASIS,
013 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014 * See the License for the specific language governing permissions and
015 * limitations under the License.
016 */
017 package org.apache.geronimo.monitoring.console.util;
018
019 import java.sql.Connection;
020 import java.sql.DatabaseMetaData;
021 import java.sql.PreparedStatement;
022 import java.sql.ResultSet;
023 import java.sql.SQLException;
024
025 import javax.naming.Context;
026 import javax.naming.InitialContext;
027 import javax.naming.NamingException;
028 import javax.sql.DataSource;
029
030 public class DBManager
031 {
032 private static Connection con = null;
033 private static boolean initialized = false;
034
035 public DBManager()
036 {
037 con = createConnection();
038 if (!initialized)
039 if (this.initializeDB())
040 initialized = true;
041 }
042
043 public static Connection createConnection()
044 {
045
046 try
047 {
048 Context context = new InitialContext();
049 DataSource ds = (DataSource) context
050 .lookup("java:comp/env/MonitoringClientDS");
051 con = ds.getConnection();
052 }
053 catch (NamingException e)
054 {
055 e.printStackTrace();
056 }
057 catch (SQLException e)
058 {
059 System.err.println("SQL state: " + e.getSQLState());
060 System.err.println("SQL error: " + e.getErrorCode());
061 e.printStackTrace();
062 }
063 return con;
064 }
065
066 public Connection getConnection()
067 {
068 return con;
069 }
070
071 public boolean isInitialized()
072 {
073 return initialized;
074 }
075
076 private boolean checkTables()
077 {
078 try
079 {
080 DatabaseMetaData metadata = null;
081 metadata = con.getMetaData();
082 String[] names = { "TABLE" };
083 ResultSet tableNames = metadata.getTables(null, null, null, names);
084
085 if (tableNames.next())
086 {
087 return true;
088 }
089 else
090 {
091 return false;
092 }
093 }
094 catch (SQLException e)
095 {
096 System.err.println("SQL state: " + e.getSQLState());
097 System.err.println("SQL error: " + e.getErrorCode());
098 e.printStackTrace();
099 }
100 return false;
101 }
102
103 private boolean initializeDB()
104 {
105 boolean success = false;
106 if (checkTables())
107 return true;
108 try
109 {
110 PreparedStatement pStmt = con
111 .prepareStatement("CREATE TABLE servers("
112 + "server_id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),"
113 + "enabled SMALLINT DEFAULT 1 NOT NULL,"
114 + "name VARCHAR(128) DEFAULT NULL,"
115 + "ip VARCHAR(128) UNIQUE NOT NULL,"
116 + "port INTEGER NOT NULL,"
117 + "protocol INTEGER DEFAULT 1 NOT NULL,"
118 + "username VARCHAR(128) NOT NULL,"
119 + "password VARCHAR(1024) NOT NULL,"
120 + "added TIMESTAMP NOT NULL,"
121 + "modified TIMESTAMP NOT NULL,"
122 + "last_seen TIMESTAMP NOT NULL" + ")");
123 pStmt.executeUpdate();
124 pStmt = con
125 .prepareStatement("CREATE TABLE graphs("
126 + "graph_id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),"
127 + "enabled SMALLINT NOT NULL DEFAULT 1,"
128 + "server_id INTEGER NOT NULL DEFAULT 0,"
129 + "name VARCHAR(128) UNIQUE NOT NULL,"
130 + "description LONG VARCHAR DEFAULT NULL,"
131 + "timeframe INTEGER NOT NULL DEFAULT 60,"
132 + "mbean VARCHAR(512) NOT NULL,"
133 + "data1operation CHAR DEFAULT NULL,"
134 + "dataname1 VARCHAR(128) NOT NULL,"
135 + "operation VARCHAR(128) DEFAULT NULL,"
136 + "data2operation CHAR DEFAULT NULL,"
137 + "dataname2 VARCHAR(128) DEFAULT NULL,"
138 + "xlabel VARCHAR(128) DEFAULT NULL,"
139 + "ylabel VARCHAR(128) DEFAULT NULL,"
140 + "warninglevel1 FLOAT DEFAULT NULL,"
141 + "warninglevel2 FLOAT DEFAULT NULL,"
142 + "color VARCHAR(6) NOT NULL DEFAULT '1176c2',"
143 + "last_js LONG VARCHAR DEFAULT NULL,"
144 + "added TIMESTAMP NOT NULL,"
145 + "modified TIMESTAMP NOT NULL,"
146 + "archive SMALLINT NOT NULL DEFAULT 0,"
147 + "last_seen TIMESTAMP NOT NULL" + ")");
148 pStmt.executeUpdate();
149 pStmt = con
150 .prepareStatement("CREATE TABLE views("
151 + "view_id INTEGER PRIMARY KEY NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1),"
152 + "enabled SMALLINT NOT NULL DEFAULT 1,"
153 + "name VARCHAR(128) NOT NULL,"
154 + "description LONG VARCHAR DEFAULT NULL,"
155 + "graph_count INTEGER NOT NULL DEFAULT 0,"
156 + "added TIMESTAMP NOT NULL,"
157 + "modified TIMESTAMP NOT NULL)");
158 pStmt.executeUpdate();
159 pStmt = con.prepareStatement("CREATE TABLE views_graphs("
160 + "view_id INTEGER NOT NULL,"
161 + "graph_id INTEGER NOT NULL)");
162 pStmt.executeUpdate();
163 success = true;
164 }
165 catch (SQLException e)
166 {
167 System.err.println("SQL state: " + e.getSQLState());
168 System.err.println("SQL error: " + e.getErrorCode());
169 e.printStackTrace();
170 }
171 return success;
172 }
173 }