1
2
3
4
5
6
7 package org.astrogrid.tableserver.jdbc;
8
9 import org.astrogrid.dataservice.queriers.*;
10
11 import java.io.IOException;
12 import java.lang.reflect.InvocationTargetException;
13 import java.security.Principal;
14 import java.sql.Connection;
15 import java.sql.ResultSet;
16 import java.sql.SQLException;
17 import java.sql.Statement;
18 import java.util.Date;
19 import org.astrogrid.cfg.ConfigFactory;
20 import org.astrogrid.tableserver.metadata.TableMetaDocInterpreter;
21 import org.astrogrid.dataservice.queriers.status.QuerierComplete;
22 import org.astrogrid.dataservice.queriers.status.QuerierError;
23 import org.astrogrid.dataservice.queriers.status.QuerierQuerying;
24 import org.astrogrid.query.Query;
25 import org.astrogrid.query.QueryException;
26
27 /***
28 * A general purpose SQL Querier that will (hopefully) produce bog standard
29 * realbasic SQL from the ADQL, throwing an exception if it can't be done
30 *
31 * <p>
32 * forms a basis for oter implementations for different db flavours
33 * <p>
34 * NWW: altered to delay creating jdbcConnection until required by {@link #queryDatabase}. DatabaseQueriers are one-shot
35 * beasts anyhow, so this isn't a problem, but it fixes problems of moving jdbcConnection across threads when non-blocking querying is done.
36 * <p>
37 * * @author M Hill
38 */
39
40 public class JdbcPlugin extends DefaultPlugin {
41
42
43
44
45 /*** Adql -> SQL translator class */
46 public static final String SQL_TRANSLATOR = "datacenter.querier.plugin.sql.translator";
47
48 /*** execute timeout */
49 public static final String TIMEOUT = "datacenter.sql.timeout";
50
51 /*** Connection manager */
52 private static JdbcConnections connectionManager = null;
53
54
55 /*** performs a synchronous call to the database, submitting the given query
56 * in sql form and retiirning the results as a SqlResults wrapper arond the JDBC result set.
57 * @param o a string
58 */
59 public void askQuery(Principal user, Query query, Querier querier) throws IOException {
60
61 validateQuery(query);
62
63 String sql = "(not set)";
64 Connection jdbcConnection = null;
65
66 try {
67
68 log.debug("Making SQL");
69 SqlMaker sqlMaker = makeSqlMaker();
70 sql = sqlMaker.makeSql(query);
71
72 querier.setStatus(new QuerierQuerying(querier.getStatus(), sql));
73
74 if ((sql == null) || (sql.length() == 0)) {
75 throw new QueryException("SqlMaker returned empty SQL string for query "+query);
76 }
77
78
79
80
81 log.debug("Connecting to the database");
82 jdbcConnection = getJdbcConnection();
83 Statement statement = jdbcConnection.createStatement();
84
85 if (query.getLocalLimit() >0) { statement.setMaxRows((int) query.getLocalLimit()); }
86
87
88 querier.getStatus().addDetail("Submitted to JDBC at "+new Date());
89
90
91 log.info("Performing Query: " + sql);
92 statement.execute(sql);
93 querier.getStatus().addDetail("JDBC execution complete at "+new java.util.Date());
94
95 ResultSet results = statement.getResultSet();
96
97 if (!aborted) {
98
99 if (results == null) {
100 throw new QueryException("SQL '"+sql+"' returned null results");
101 }
102
103
104 TableResults qResults = makeSqlResults(querier, results);
105 qResults.send(query.getResultsDef(), querier.getUser());
106
107 }
108
109
110
111 }
112 catch (SQLException e) {
113 querier.setStatus(new QuerierError(querier.getStatus(), "JDBC Query Failed",e));
114
115 throw new DatabaseAccessException(e+" using '" + sql + "': ",e);
116 }
117 finally {
118
119 try {
120 if (jdbcConnection != null) { jdbcConnection.close(); }
121 } catch (SQLException e) { }
122 }
123
124 }
125
126 /*** Returns just the number of matches rather than the list of matches */
127 public long getCount(Principal user, Query query, Querier querier) throws IOException {
128
129 validateQuery(query);
130
131 String sql = "(not set)";
132 Connection jdbcConnection = null;
133
134 try {
135
136 SqlMaker sqlMaker = makeSqlMaker();
137
138 sql = sqlMaker.makeCountSql(query);
139
140 querier.setStatus(new QuerierQuerying(querier.getStatus(), sql));
141
142 if ((sql == null) || (sql.length() == 0)) {
143 throw new QueryException("SqlMaker returned empty SQL string for query "+query);
144 }
145
146
147 log.debug("Connecting to the database");
148 jdbcConnection = getJdbcConnection();
149 Statement statement = jdbcConnection.createStatement();
150
151 querier.getStatus().addDetail("Submitted to JDBC at "+new Date());
152
153
154 log.info("Performing Query: " + sql);
155 statement.execute(sql);
156 querier.getStatus().addDetail("JDBC execution complete at "+new java.util.Date());
157
158 ResultSet results = statement.getResultSet();
159
160
161 results.next();
162 long count = results.getLong(1);
163 results.close();
164
165 querier.getStatus().addDetail("Count="+count);
166 querier.setStatus(new QuerierComplete(querier.getStatus()));
167
168 return count;
169 }
170 catch (SQLException e) {
171 querier.setStatus(new QuerierError(querier.getStatus(), "JDBC Query Failed",e));
172
173 throw new DatabaseAccessException(e+" using '" + sql + "': ",e);
174 }
175 finally {
176
177 try {
178 if (jdbcConnection != null) { jdbcConnection.close(); }
179 } catch (SQLException e) { }
180 }
181
182 }
183
184 /*** Throws an IllegalArgumentException if the query is not appropriate to this site */
185 public void validateQuery(Query query) {
186 try {
187 TableMetaDocInterpreter reader = new TableMetaDocInterpreter();
188 RdbmsQueryValidator validator = new RdbmsQueryValidator(reader);
189 query.acceptVisitor(validator);
190 }
191 catch (IOException ioe) {
192 log.warn("No RDBMS Resource found, not validating query");
193 }
194
195 }
196
197 /*** Returns the formats that this plugin can provide. Asks the results class; override in subclasse if nec */
198 public String[] getFormats() {
199 return SqlResults.listFormats();
200 }
201
202 /*** Makes SqlResults for the resultset. This means subclasses can override it
203 * to make an easy way to transform the results */
204 public TableResults makeSqlResults(Querier querier, ResultSet results) {
205 return new SqlResults(querier, results);
206 }
207
208 /***
209 * Makes the right SqlQueryMaker for this database
210 */
211 public SqlMaker makeSqlMaker() throws QuerierPluginException {
212 String makerClass = ConfigFactory.getCommonConfig().getString(SQL_TRANSLATOR, "org.astrogrid.datacenter.queriers.sql.StdSqlMaker");
213
214 try {
215 Object o = QuerierPluginFactory.instantiate(makerClass);
216 if (o == null) {
217 throw new QuerierPluginException("Could not create the SQL plugin translator '"+makerClass+"'");
218 }
219 return (SqlMaker) o;
220 }
221 catch (ClassCastException cce) {
222 throw new QuerierPluginException("SQL plugin maker given in config ("+makerClass+") is not a "+SqlMaker.class.getName()+" subclass ");
223 }
224 catch (Throwable th) {
225 if (th instanceof InvocationTargetException) {
226 th = th.getCause();
227 }
228 String msg = "Instantiating SQL Maker "+makerClass+", config key="+SQL_TRANSLATOR;
229 log.error(msg, th);
230 throw new QuerierPluginException(msg, th);
231 }
232 }
233
234 /*** Creates a connection to the database */
235 protected static synchronized Connection getJdbcConnection() throws IOException, SQLException {
236
237 if (connectionManager == null) {
238 connectionManager = JdbcConnections.makeFromConfig();
239 }
240 return connectionManager.createConnection();
241
242 }
243
244
245
246
247 }
248
249
250
251