1
2
3
4
5
6
7 package org.astrogrid.tableserver.jdbc;
8
9 import java.io.IOException;
10 import java.io.StringWriter;
11 import java.io.Writer;
12 import java.sql.Connection;
13 import java.sql.DatabaseMetaData;
14 import java.sql.ResultSet;
15 import java.sql.SQLException;
16 import java.sql.Types;
17 import javax.servlet.http.HttpServletRequest;
18 import javax.servlet.http.HttpServletResponse;
19 import org.apache.commons.logging.Log;
20 import org.apache.commons.logging.LogFactory;
21 import org.astrogrid.dataservice.queriers.DatabaseAccessException;
22 import org.astrogrid.io.xml.XmlAsciiWriter;
23 import org.astrogrid.io.xml.XmlPrinter;
24 import org.astrogrid.webapp.DefaultServlet;
25 import org.astrogrid.xml.XmlTypes;
26
27 /***
28 * Generates the table metadoc that describes a tabular dataset from the metadata
29 * provided by the JDBC connection. Comes in handy servlet form for easy web use.
30 */
31
32 public class RdbmsTableMetaDocGenerator extends DefaultServlet {
33
34 protected static Log log = LogFactory.getLog(RdbmsTableMetaDocGenerator.class);
35
36
37 public static String INT = XmlTypes.INT;
38 public static String FLOAT = XmlTypes.FLOAT;
39 public static String BOOLEAN = XmlTypes.BOOLEAN;
40 public static String STRING = XmlTypes.STRING;
41 public static String DATE = XmlTypes.DATE;
42
43 /*** Convenience routine for finding the value of a column in a result set row,
44 * but ignoring
45 * missing columns
46 */
47 protected String getColumnValue(ResultSet table, String column) {
48 try {
49 String s = table.getString(column);
50 if (s==null) {
51 return "";
52 }
53 else {
54 return s;
55 }
56 }
57 catch (SQLException e) {
58 return "(Unknown)";
59 }
60 }
61
62 /***
63 * Returns the votable datatype for the given column.
64 * @todo check these - these are made up/guessed
65 */
66 public static String getType(int sqlType) {
67
68 switch (sqlType) {
69 case Types.ARRAY : log.error("Don't know how to cope with Arrays, storing as string", new RuntimeException()); return STRING;
70 case Types.BIGINT: return INT;
71 case Types.BOOLEAN: return BOOLEAN;
72 case Types.BIT: return BOOLEAN;
73 case Types.CHAR: return STRING;
74 case Types.DATE: return DATE;
75 case Types.DECIMAL: return FLOAT;
76 case Types.DOUBLE: return FLOAT;
77 case Types.FLOAT: return FLOAT;
78 case Types.INTEGER: return INT;
79
80 case Types.REAL: return FLOAT;
81 case Types.SMALLINT: return INT;
82 case Types.TINYINT: return INT;
83 case Types.TIMESTAMP:return DATE;
84 case Types.VARCHAR: return STRING;
85 default: {
86 log.error("Don't know what SQL type "+sqlType+" should be, storing as string", new RuntimeException());
87 return STRING;
88 }
89 }
90 }
91
92 /*** Returns the metadata as a string */
93 public String getMetaDoc() throws IOException {
94 StringWriter sw = new StringWriter();
95 writeTableMetaDoc(sw);
96 return sw.toString();
97 }
98
99
100 /***
101 * Writes the metadata to the given stream. Writes just one catalog for now */
102 public void writeTableMetaDoc(Writer out) throws IOException {
103
104 out.write("<DatasetDescription targetNamespace='urn:astrogrid:schema:TableMetaDoc:v1'>\n");
105
106 Connection connection = null;
107 try {
108 connection = JdbcPlugin.getJdbcConnection();
109
110 DatabaseMetaData metadata = connection.getMetaData();
111
112 XmlAsciiWriter xw = new XmlAsciiWriter(out, false);
113
114 XmlPrinter catTag = xw.newTag("Catalog");
115
116
117 ResultSet tables = metadata.getTables(null, null, "%", null);
118
119 while (tables.next()) {
120
121
122 if (!getColumnValue(tables, "TABLE_NAME").startsWith("sys")) {
123 String tableName = getColumnValue(tables, "TABLE_NAME");
124 XmlPrinter tableTag = catTag.newTag("Table", new String[] { "ID='"+tableName+"'"} );
125 tableTag.writeTag("Name", tableName );
126 tableTag.writeTag("Description", getColumnValue(tables, "REMARKS")+" ");
127
128
129 ResultSet columns = metadata.getColumns(null, null, tables.getString("TABLE_NAME"), "%");
130
131 while (columns.next()) {
132 int sqlType = Integer.parseInt(getColumnValue(columns, "DATA_TYPE"));
133 String colName = getColumnValue(columns, "COLUMN_NAME");
134 XmlPrinter colTag = tableTag.newTag(
135 "Column",
136 new String[] { "ID='"+tableName+"."+colName+"'",
137 "indexed='false'" }
138 );
139 colTag.writeTag("Name", colName);
140 colTag.writeTag("Datatype", getType(sqlType));
141 colTag.writeTag("Description", getColumnValue(columns, "REMARKS")+" ");
142
143 colTag.writeTag("Units", " ");
144 colTag.writeTag("DimEq", " ");
145 colTag.writeTag("Scale", " ");
146 colTag.writeTag("UCD", " ");
147 colTag.writeTag("UcdPlus", " ");
148
149
150 if (colName.toLowerCase().equals("ra")) {
151 colTag.writeTag("SkyPolarCoord", "RA");
152 }
153 if (colName.toLowerCase().equals("dec")) {
154 colTag.writeTag("SkyPolarCoord", "DEC");
155 }
156
157 colTag.close();
158 }
159
160 tableTag.close();
161 }
162 }
163 catTag.close();
164 xw.close();
165
166 connection.close();
167 }
168 catch (SQLException e) {
169 throw new DatabaseAccessException("Could not get metadata: "+e,e);
170 }
171 out.write("</DatasetDescription>\n");
172 out.flush();
173 }
174
175
176 /*** Servlet implementation so we can run it nicely from a web interface */
177 public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
178
179 try {
180 response.setContentType("text/xml");
181
182 writeTableMetaDoc(response.getWriter());
183 }
184 catch (Throwable th) {
185 doError(response, "Generating Resource Metadata",th);
186 }
187 }
188
189 /*** for testing/debugging etc */
190 public static void main(String[] args) {
191
192
193 }
194
195 }
196
197