View Javadoc

1   /*
2    * $Id: SampleStarsPlugin.java,v 1.4 2005/03/23 17:33:21 mch Exp $
3    *
4    * (C) Copyright Astrogrid...
5    */
6   
7   package org.astrogrid.tableserver.test;
8   import java.io.IOException;
9   import java.net.URL;
10  import java.sql.Connection;
11  import java.sql.SQLException;
12  import java.text.SimpleDateFormat;
13  import java.util.Date;
14  import org.astrogrid.cfg.ConfigReader;
15  import org.astrogrid.cfg.ConfigFactory;
16  import org.astrogrid.dataservice.metadata.VoDescriptionServer;
17  import org.astrogrid.dataservice.metadata.queryable.ConeConfigQueryableResource;
18  import org.astrogrid.dataservice.queriers.DatabaseAccessException;
19  import org.astrogrid.dataservice.queriers.QuerierPluginFactory;
20  import org.astrogrid.tableserver.jdbc.JdbcConnections;
21  import org.astrogrid.tableserver.jdbc.JdbcPlugin;
22  import org.astrogrid.tableserver.jdbc.StdSqlMaker;
23  import org.astrogrid.tableserver.metadata.TableMetaDocInterpreter;
24  import org.astrogrid.tableserver.metadata.TabularDbResources;
25  import org.astrogrid.tableserver.metadata.TabularSkyServiceResources;
26  
27  /***
28   * This plugin works with a 'fixed' set of values in an HSQL database.  So
29   * SQL statements can be run against it.
30   *
31   * @author M Hill
32   */
33  
34  public class SampleStarsPlugin extends JdbcPlugin
35  {
36     
37     private static boolean initialised = false;
38  
39     private static final SimpleDateFormat sqlDateFormat = new SimpleDateFormat("dd/MM/yy HH:mm:ss");
40     
41     public SampleStarsPlugin() throws IOException
42     {
43        if (!initialised ) { initialise(); }
44        
45     }
46  
47     /*** Sets up the configuration etc for accessing this database */
48     public static void initConfig() {
49        
50        ConfigFactory.getCommonConfig().setProperty(QuerierPluginFactory.QUERIER_PLUGIN_KEY, SampleStarsPlugin.class.getName());
51  
52        ConfigFactory.getCommonConfig().setProperty(JdbcPlugin.SQL_TRANSLATOR, StdSqlMaker.class.getName());
53  
54        ConfigFactory.getCommonConfig().setProperty("datacenter.name", "Sample");
55        
56        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_RA_COL_KEY, "RA");
57        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_DEC_COL_KEY,"DEC");
58        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_TABLE_KEY,  "SampleStars");
59        
60  //      ConfigFactory.getCommonConfig().setProperty(SqlWriter.DB_TRIGFUNCS_IN_RADIANS, "True");
61  
62        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_COL_UNITS_KEY, "deg");
63        
64        //set up properties so we connect to the db
65        ConfigFactory.getCommonConfig().setProperty(JdbcConnections.JDBC_DRIVERS_KEY, "org.hsqldb.jdbcDriver");
66  //      ConfigFactory.getCommonConfig().setProperty(JdbcConnections.JDBC_URL_KEY, "jdbc:hsqldb:."); //in memory db - doesn't seem to persist between calls...
67        ConfigFactory.getCommonConfig().setProperty(JdbcConnections.JDBC_URL_KEY, "jdbc:hsqldb:dummydb"); //db on disk
68        ConfigFactory.getCommonConfig().setProperty(JdbcConnections.JDBC_USER_KEY, "sa");
69        ConfigFactory.getCommonConfig().setProperty(JdbcConnections.JDBC_PASSWORD_KEY, "");
70        
71        //it's a bit naughty setting this, but it sorts out most tests
72        ConfigFactory.getCommonConfig().setProperty("datacenter.url", ConfigFactory.getCommonConfig().getProperty("datacenter.url", "http://localhost:8080/pal-Sample/"));
73        
74        //set where to find the data description meta document
75        //this works OK for unit test, but not deployment...
76        URL url = SampleStarsPlugin.class.getResource("samplestars.metadoc.xml");
77        if (url == null) {
78           //this works OK for deployment, but not unit tests...
79           try {
80              url = ConfigReader.resolveFilename("samplestars.metadoc.xml");
81           }
82           catch (IOException e) {
83              throw new RuntimeException(e);
84           }
85        }
86        ConfigFactory.getCommonConfig().setProperty(TableMetaDocInterpreter.TABLE_METADOC_URL_KEY, url.toString());
87  
88        //configure which resources to produce
89        ConfigFactory.getCommonConfig().setProperties(VoDescriptionServer.RESOURCE_PLUGIN_KEY, new Object[] {
90                 TabularSkyServiceResources.class.getName(),
91                 TabularDbResources.class.getName(),
92              });
93  
94        
95        //set up the properties for the authority bit
96        ConfigFactory.getCommonConfig().setProperty("datacenter.name", "SampleStars AstroGrid Datacenter");
97        ConfigFactory.getCommonConfig().setProperty("datacenter.shortname", "PAL-Sample");
98        ConfigFactory.getCommonConfig().setProperty("datacenter.publisher", "AstroGrid");
99        ConfigFactory.getCommonConfig().setProperty("datacenter.description", "An unconfigured datacenter; it contains two tables of sample stars and galaxies for testing and demonstration purposes.");
100       ConfigFactory.getCommonConfig().setProperty("datacenter.contact.name", "Martin Hill");
101       ConfigFactory.getCommonConfig().setProperty("datacenter.contact.email", "mch@roe.ac.uk");
102 
103       ConfigFactory.getCommonConfig().setProperty("datacenter.authorityId", "astrogrid.org");
104       ConfigFactory.getCommonConfig().setProperty("datacenter.resourceKey", "pal-sample");
105     }
106    
107     
108     
109 
110    public static synchronized void initialise() throws DatabaseAccessException {
111       
112       if (initialised) return;
113       
114       initConfig();
115 
116       populateDb();
117     }
118        
119    /*** Creates & populates the in-memory database.
120     * Static so we can reach it from our test harness
121     */
122    public static synchronized void populateDb() throws DatabaseAccessException {
123        
124       //connect
125       Connection connection = null;
126       try {
127          connection = JdbcConnections.makeFromConfig().createConnection();
128       }
129       catch (SQLException se) {
130          throw new DatabaseAccessException("Could not connect to JDBC: "+se);
131       }
132       
133       //first remove in case there in memory still from previous test
134       try {
135          connection.createStatement().execute("DROP TABLE SampleStars"  );
136          connection.createStatement().execute("DROP TABLE SampleGalaxies"  );
137          connection.createStatement().execute("DROP TABLE Plates"  );
138       }
139       catch (SQLException se) {
140          //ignore - may not exist
141       }
142       
143       log.info("Populating Database");
144 
145    
146       try {
147          //populate stars
148          //create table
149          connection.createStatement().execute(
150             "CREATE TABLE SampleStars (Id INTEGER IDENTITY,  Name VARCHAR(30), Ra DOUBLE,  Dec DOUBLE,  Mag DOUBLE)  "
151          );
152 
153          //create index on table
154          connection.createStatement().execute(
155             "CREATE INDEX ssIndex ON SampleStars (Ra, Dec)  "
156          );
157          
158          
159          //add some stars
160          for (int i=0;i<20;i++) {
161             connection.createStatement().execute(
162                "INSERT INTO SampleStars VALUES ("+i+", 'A star', "+(30+i*2)+", "+(30-i*2)+", "+i+")"
163             );
164          }
165 
166          //add false pleidies.  These are stars grouped < 0.3 degree across on ra=56.75, dec=23.867
167          int id=21;
168          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies LE', 56.6, 23.65, 10)"); id++;
169          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies RE', 56.9, 23.65, 10)"); id++;
170          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Nose', 56.75, 23.87, 8)"); id++;
171          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.5, 23.9, 12)"); id++;
172          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.7, 24.0, 12)"); id++;
173          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.8, 24.0, 12)"); id++;
174          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 57.0, 23.9, 12)"); id++;
175 
176          //add stars that are outside the above group but nearby
177          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56.6, 23.6, 10)"); id++;
178          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56, 23, 5)"); id++;
179          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 58, 24.5, 5)"); id++;
180          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56, 24.5, 5)"); id++;
181          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 58, 23, 5)"); id++;
182          
183          //add even spread (in coordinate space) of background stars
184          for (double ra=0;ra<360;ra=ra+2) {
185 //            StringBuffer sql = new StringBuffer("INSERT INTO SampleStars VALUES ");
186             for (double dec=-90;dec<90;dec=dec+2) {
187 //               sql.append(" ("+id+", 'Background', "+ra+", "+dec+", 20) "); id++;
188                connection.createStatement().execute("INSERT INTO SampleStars VALUES  ("+id+", 'Background', "+ra+", "+dec+", 20)"); id++;
189             }
190 //           connection.createStatement().execute(sql.toString());
191             System.out.print(".");
192          }
193          
194          //populate galaxies
195          //create table
196          connection.createStatement().execute(
197             "CREATE TABLE SampleGalaxies (Id INTEGER IDENTITY,  Ra DOUBLE,  Dec DOUBLE,  Shape VARCHAR(20)) "
198          );
199          
200          //add individual galaxies
201          String[] shapes = new String[] {"ELLIPTICAL", "SPIRAL", "IRREGULAR" };
202          for (int i=0;i<20;i++) {
203             
204             connection.createStatement().execute(
205                "INSERT INTO SampleGalaxies VALUES ("+i+", "+(200+i*2)+", "+(200-i*2)+", '"+shapes[i % 2]+"')"
206             );
207          }
208    
209          //populate plate tables
210          connection.createStatement().execute(
211             "CREATE TABLE Plates (Id INTEGER IDENTITY,  CenterRa DOUBLE,  CenterDec DOUBLE,  ObsTime DATETIME) "
212          );
213          long todayTime = new Date().getTime();
214          for (int i=0;i<5;i++) {
215             connection.createStatement().execute(
216 //               "INSERT INTO Plates VALUES ("+i+", "+(100+i*20)+", "+(100-i*20)+", "+sqlDateFormat.format(new java.sql.Date(todayTime-i*1000))+")"
217                "INSERT INTO Plates VALUES ("+i+", "+(100+i*20)+", "+(100-i*20)+", '"+new java.sql.Timestamp(todayTime-i*1000)+"')"
218             );
219          }
220          
221          
222          connection.commit();
223          connection.close();
224       }
225       catch (SQLException se) {
226          log.error("Populating demo db",se);
227       }
228 
229       initialised = true;
230       
231       log.info("...database populated"); //so that we can mark how long it took
232       //check metadata
233       /*
234       try {
235          JdbcPlugin plugin = new SampleStarsPlugin(null);
236          Document metadata = plugin.getMetadata();
237          log.info(DomHelper.DocumentToString(metadata));
238       } catch (IOException ioe) {
239          throw new RuntimeException(ioe);
240       }
241        */
242    }
243 
244    /* Sample SQL statemetns to help with above:
245    
246    CREATE TABLE Customer (Customer_no INTEGER IDENTITY, firstname VARCHAR(15), lastname VARCHAR(50), address VARCHAR(150), postalcode VARCHAR(7));
247 
248    CREATE TABLE Movies (Movie_id INTEGER IDENTITY, title VARCHAR(50), classification VARCHAR(5), star_1 VARCHAR(35), star_2 VARCHAR(35), release_date DATETIME);
249 
250    CREATE TABLE Rentals (Customer_no INTEGER, Movie_id INTEGER, date_out DATETIME, date_in DATETIME);
251    
252    INSERT INTO Customer VALUES (null, 'Joe','Smith','100 Main St','T5T 1A1');
253    INSERT INTO Customer VALUES (null, 'Joe','Smith','100 Main St','T5T 1A1');
254    INSERT INTO Customer VALUES (null, 'Jane','Jones','100 Main St','T5T 1A1');
255    INSERT INTO Customer VALUES (null, 'Bill','Black','100 Main St','T5T 1A1');
256 
257 
258    INSERT INTO Movies VALUES (null, 'Return of the Jedi', 'G','star 1', 'star 2', 'now');
259    INSERT INTO Movies VALUES (null, 'MASH', 'A','star 1', 'star 2', 'now');
260    INSERT INTO Movies VALUES (null, 'Signs', 'PG','star 1', 'star 2', 'now');
261     */
262    
263 }
264    /*
265    $Log: SampleStarsPlugin.java,v $
266    Revision 1.4  2005/03/23 17:33:21  mch
267    removed rbmsresours
268 
269    Revision 1.3  2005/03/21 18:45:55  mch
270    Naughty big lump of changes
271 
272    Revision 1.2  2005/03/10 20:19:21  mch
273    Fixed tests more metadata fixes
274 
275    Revision 1.1  2005/03/10 16:42:55  mch
276    Split fits, sql and xdb
277 
278    Revision 1.3  2005/03/10 13:49:52  mch
279    Updating metadata
280 
281    Revision 1.2  2005/03/01 17:51:59  mch
282    fixes to tests
283 
284    Revision 1.1.1.1  2005/02/17 18:37:35  mch
285    Initial checkin
286 
287    Revision 1.1.1.1  2005/02/16 17:11:24  mch
288    Initial checkin
289 
290    Revision 1.8.6.5  2005/01/24 12:14:30  mch
291    Fixes to VizieR proxy and resource stuff
292 
293    Revision 1.8.6.4  2004/12/10 12:37:13  mch
294    Cone searches to look in metadata, lots of metadata interpreterrs
295 
296    Revision 1.8.6.3  2004/12/08 23:23:37  mch
297    Made SqlWriter and AdqlWriter implement QueryVisitor
298 
299    Revision 1.8.6.2  2004/12/08 18:36:40  mch
300    Added Vizier, rationalised SqlWriters etc, separated out TableResults from QueryResults
301 
302    Revision 1.8.6.1  2004/12/03 11:56:43  mch
303    switched from using stylesheet to dedicated SQL maker
304 
305    Revision 1.8  2004/11/09 17:42:22  mch
306    Fixes to tests after fixes for demos, incl adding closable to targetIndicators
307 
308    Revision 1.7  2004/11/03 00:17:56  mch
309    PAL_MCH Candidate 2 merge
310 
311    Revision 1.3.6.1  2004/10/19 17:26:27  mch
312    Odd fixes
313 
314    Revision 1.3  2004/10/18 13:11:30  mch
315    Lumpy Merge
316 
317    Revision 1.2.2.1  2004/10/15 19:59:06  mch
318    Lots of changes during trip to CDS to improve int test pass rate
319 
320    Revision 1.2  2004/10/08 17:14:22  mch
321    Clearer separation of metadata and querier plugins, and improvements to VoResource plugin mechanisms
322 
323    Revision 1.1  2004/09/28 15:02:13  mch
324    Merged PAL and server packages
325 
326    Revision 1.3  2004/09/08 17:51:49  mch
327    Fixes to log and metadata views
328 
329    Revision 1.2  2004/09/06 20:23:00  mch
330    Replaced metadata generators/servers with plugin mechanism. Added Authority plugin
331 
332    Revision 1.1  2004/09/01 13:40:32  mch
333    Renamed dummy to samplestars and added metadata server
334 
335    Revision 1.10  2004/08/18 22:28:45  mch
336    Switched to search on table (index search fails..) and background stars less dense
337 
338    Revision 1.9  2004/08/18 18:44:12  mch
339    Created metadata plugin service and added helper methods
340 
341    Revision 1.8  2004/08/06 12:04:19  mch
342    Added unit description to conesearch columns to cope with ESO milliarcseconds (& others in future)
343 
344    Revision 1.3.10.1  2004/08/05 17:57:08  mch
345    Merging Itn06 fixes into Itn05
346 
347    Revision 1.7  2004/08/05 15:58:19  mch
348    Added background stars
349 
350    Revision 1.6  2004/07/12 23:26:51  mch
351    Fixed (somewhat) SQL for cone searches, added tests to Dummy DB
352 
353    Revision 1.5  2004/07/07 19:33:59  mch
354    Fixes to get Dummy db working and xslt sheets working both for unit tests and deployed
355 
356    Revision 1.4  2004/07/06 18:48:34  mch
357    Series of unit test fixes
358 
359    Revision 1.3  2004/03/13 23:38:46  mch
360    Test fixes and better front-end JSP access
361 
362    Revision 1.2  2004/03/12 20:04:57  mch
363    It05 Refactor (Client)
364 
365    Revision 1.1  2004/03/12 04:45:26  mch
366    It05 MCH Refactor
367 
368     */