View Javadoc

1   /*
2    * $Id: RdbmsTableMetaDocGenerator.java,v 1.8 2006/09/26 15:34:42 clq2 Exp $
3    *
4    * (C) Copyright Astrogrid...
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.dataservice.metadata.XmlTypes;
26  import org.astrogrid.dataservice.metadata.StdDataTypes;
27  import org.astrogrid.tableserver.test.SampleStarsPlugin;
28  import org.astrogrid.cfg.ConfigFactory;
29  
30  
31  /***
32   * Generates the table metadoc that describes a tabular dataset from the metadata
33   * provided by the JDBC connection.  Comes in handy servlet form for easy web use.
34   */
35  
36  public class RdbmsTableMetaDocGenerator extends DefaultServlet {
37     
38     protected static Log log = LogFactory.getLog(RdbmsTableMetaDocGenerator.class);
39     
40     /*
41     //should match the xml schema types
42     //public static String INT = XmlTypes.INT;
43     //public static String FLOAT = XmlTypes.FLOAT;
44     //public static String BOOLEAN = XmlTypes.BOOLEAN;
45     //public static String STRING = XmlTypes.STRING;
46     //public static String DATE = XmlTypes.DATE;
47     public static String INT = StdDataTypes.INT;
48     public static String FLOAT = StdDataTypes.FLOAT;
49     public static String BOOLEAN = StdDataTypes.BOOLEAN;
50     public static String STRING = StdDataTypes.STRING;
51     public static String DATE = StdDataTypes.DATE;
52     */
53     
54     /*** Convenience routine for finding the value of a column in a result set row,
55      * but ignoring
56      * missing columns
57      */
58     protected String getColumnValue(ResultSet table, String column) {
59        try {
60           String s = table.getString(column);
61           if (s==null) {
62              return "";
63           }
64           else {
65              return s;
66           }
67        }
68        catch (SQLException e) {
69           return "(Unknown)";
70        }
71     }
72     
73     /***
74      * Returns the votable datatype for the given column.
75      * @todo check these - these are made up/guessed
76      */
77     public static String getType(int sqlType, int typeSize) {
78        
79        switch (sqlType) {
80           case Types.ARRAY    : log.error("Don't know how to cope with Arrays, storing as string", new RuntimeException()); return StdDataTypes.STRING;
81          /*
82           case Types.BIGINT:   return INT;
83           case Types.BOOLEAN:  return BOOLEAN;
84           case Types.BIT:      return BOOLEAN;
85           case Types.CHAR:     return STRING;
86           case Types.DATE:     return DATE;
87           case Types.DECIMAL:   return FLOAT;
88           case Types.DOUBLE:   return FLOAT;
89           case Types.FLOAT:    return FLOAT;
90           case Types.INTEGER:  return INT;
91  //         case Types.NUMERIC:  return STRING;  ?tel nums?
92           case Types.REAL:     return FLOAT;
93           case Types.SMALLINT: return INT;
94           case Types.TINYINT:  return INT;
95           case Types.TIMESTAMP:return DATE;
96           case Types.VARCHAR:  return STRING;
97           */
98           /* KEA NOTE: Erring on the side of excess precision here */
99           case Types.BIGINT:   return StdDataTypes.LONG;
100          case Types.BOOLEAN:  return StdDataTypes.BOOLEAN;
101          case Types.BIT:      return StdDataTypes.SHORT;
102          case Types.CHAR:     
103             if (typeSize == 1) {
104               return StdDataTypes.CHAR;
105             }
106             else {
107               return StdDataTypes.STRING;
108             }
109          case Types.DATE:     return StdDataTypes.DATE;
110          case Types.DECIMAL:   return StdDataTypes.DOUBLE;
111          case Types.DOUBLE:   return StdDataTypes.DOUBLE;
112          case Types.FLOAT:    return StdDataTypes.FLOAT;
113          case Types.INTEGER:  return StdDataTypes.INT;
114 //         case Types.NUMERIC:  return StdDataTypes.STRING;  ?tel nums?
115          case Types.REAL:     return StdDataTypes.DOUBLE;
116          case Types.SMALLINT: return StdDataTypes.INT;
117          case Types.TINYINT:  return StdDataTypes.SHORT;
118          case Types.TIMESTAMP:return StdDataTypes.DATE;
119          case Types.VARCHAR:  return StdDataTypes.STRING;
120          default: {
121             log.error("Don't know what SQL type "+sqlType+" should be, storing as string", new RuntimeException()); //add runtime exception so we get a stack trace
122             return StdDataTypes.STRING;
123          }
124       }
125    }
126    
127    /*** Returns the metadata as a string */
128    public String getMetaDoc() throws IOException {
129       StringWriter sw = new StringWriter();
130       writeTableMetaDoc(sw);
131       return sw.toString();
132    }
133    
134    
135    /***
136     * Writes the metadata to the given stream.  Writes just one catalog for now */
137    public void writeTableMetaDoc(Writer out) throws IOException {
138 
139       // Initialise SampleStars plugin if required (may not be initialised
140       // if admin has not run the self-tests)
141       String plugin = ConfigFactory.getCommonConfig().getString(
142             "datacenter.querier.plugin");
143       if (plugin.equals("org.astrogrid.tableserver.test.SampleStarsPlugin")) {
144          // This has no effect if the plugin is already initialised
145          SampleStarsPlugin.initialise();  // Just in case
146       }
147 //
148 // ZRQ
149 // Moved this to an XML tag.      
150 //    out.write("<DatasetDescription xmlns='urn:astrogrid:schema:TableMetaDoc:v1'>\n");
151       Connection connection = null;
152       try {
153          connection = JdbcPlugin.getJdbcConnection();
154          
155          DatabaseMetaData metadata = connection.getMetaData();
156 
157          XmlAsciiWriter xw = new XmlAsciiWriter(out, false);
158 //
159 // ZRQ
160 // Added the root tag to the XML writer. 
161          XmlPrinter rootTag = xw.newTag("DatasetDescription", new String[] {"xmlns='urn:astrogrid:schema:TableMetaDoc:v1'"});
162          XmlPrinter catTag = rootTag.newTag("Catalog");
163          // Below gives proper Name and Description tag pairs
164          XmlPrinter catNameTag = catTag.newTag("Name");
165          catNameTag.close();
166          XmlPrinter catDescTag = catTag.newTag("Description");
167          catDescTag.close();
168 
169          //get all tables
170          ResultSet tables = metadata.getTables(null, null, "%", null);
171 
172          while (tables.next()) {
173             //ignore all tables beginning with 'sys' as these are standard system tables
174             //and we don't want to make these public.  I believe
175             // KEA: HSQLDB 8.0 uses SYSTEM_XXX for sys table names
176             if (
177                 (!getColumnValue(tables, "TABLE_NAME").startsWith("sys")) &&
178                 (!getColumnValue(tables, "TABLE_NAME").startsWith("SYSTEM"))
179             ) {
180                String tableName = getColumnValue(tables, "TABLE_NAME");
181                XmlPrinter tableTag = catTag.newTag("Table", new String[] { "ID='"+tableName+"'"} );
182                tableTag.writeTag("Name", tableName );
183                tableTag.writeTag("Description", getColumnValue(tables, "REMARKS")+" "); //add space so we don't get an empty tag <Description/> which is a pain to fill in
184                //tableTag.writeComment("schema='"+getColumnValue(tables, "TABLE_SCHEM")+"'");
185                
186                ResultSet columns = metadata.getColumns(null, null, tables.getString("TABLE_NAME"), "%");
187                
188                while (columns.next()) {
189                   // This is the actual data type of the column
190                   int sqlType = Integer.parseInt(getColumnValue(columns, "DATA_TYPE"));
191 
192                   // The size of the column: max width for char and date types,
193                   // precision for other types
194                   // NB We only use typesize value for char columns at 
195                   // the moment (to decide if they're really strings)
196                   int typeSize;
197                   try {
198                      typeSize = Integer.parseInt(getColumnValue(columns, "COLUMN_SIZE"));
199                   }
200                   catch (java.lang.NumberFormatException e) {
201                     typeSize = 1;   // A sane default if unspecified?
202                   }
203                   String colName = getColumnValue(columns, "COLUMN_NAME");
204                   XmlPrinter colTag = tableTag.newTag(
205                      "Column",
206                      new String[] { "ID='"+tableName+"."+colName+"'",
207                                     "indexed='false'" }
208                   );
209                   colTag.writeTag("Name", colName);
210                   colTag.writeTag("Datatype", getType(sqlType, typeSize));  //duplicate of attribute above, which includes width where nec,
211                   colTag.writeTag("Description", getColumnValue(columns, "REMARKS")+" "); //add space so we don't get an empty tag <Description/> which is a pain to fill in
212 //                  colTag.writeTag("Link", new String[] { "text=''" }, " ");
213                   colTag.writeTag("Units", " "); //for humans
214                   colTag.writeTag("DimEq", " "); //Dimension Equation
215                   colTag.writeTag("Scale", " "); //Scaling Factor for dimension equation
216 //                colTag.writeTag("UCD", " ");
217 //                colTag.writeTag("UcdPlus", " ");
218 // ZRQ Needs version="..."
219                   colTag.writeTag("UCD", new String[] {"version='1'"} ," ");
220                   colTag.writeTag("UCD", new String[] {"version='1+'"} ," ");
221 
222                   colTag.writeTag("ErrorColumn", " ");
223                   //botch look for spatial coordinates
224                   if (colName.toLowerCase().equals("ra")) {
225                      colTag.writeTag("SkyPolarCoord", "RA");
226                   }
227                   if (colName.toLowerCase().equals("dec")) {
228                      colTag.writeTag("SkyPolarCoord", "DEC");
229                   }
230                   
231                   colTag.close();
232                }
233                
234                tableTag.close();
235             }
236          }
237          catTag.close();
238          rootTag.close();
239          xw.close();
240          
241          connection.close();
242       }
243       catch (SQLException e) {
244          throw new DatabaseAccessException("Could not get metadata: "+e,e);
245       }
246 //
247 // ZRQ root tag should get closed now.
248 //    finally {
249 //       out.write("</DatasetDescription>\n");
250 //       out.flush();
251 //    }
252    }
253    
254    
255    /*** Servlet implementation so we can run it nicely from a web interface */
256    public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
257 
258       try {
259          response.setContentType("text/xml");
260 
261          writeTableMetaDoc(response.getWriter());
262       }
263       catch (Throwable th) {
264          doError(response, "Generating Resource Metadata",th);
265       }
266    }
267 
268    /*** for testing/debugging etc */
269    public static void main(String[] args) {
270             
271       
272    }
273    
274 }
275 
276