View Javadoc

1   /*
2    * $Id: SampleStarsPlugin.java,v 1.8 2006/09/26 15:34:42 clq2 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.AdqlSqlMaker;
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      * NOTE:   */
49     public static void initConfig() {
50        
51        ConfigFactory.getCommonConfig().setProperty(QuerierPluginFactory.QUERIER_PLUGIN_KEY, SampleStarsPlugin.class.getName());
52  
53        ConfigFactory.getCommonConfig().setProperty(JdbcPlugin.SQL_TRANSLATOR, AdqlSqlMaker.class.getName());
54  
55        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_RA_COL_KEY, "RA");
56        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_DEC_COL_KEY,"DEC");
57        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_TABLE_KEY,  "SampleStars");
58  
59        ConfigFactory.getCommonConfig().setProperty(ConeConfigQueryableResource.CONE_SEARCH_COL_UNITS_KEY, "deg");
60        ConfigFactory.getCommonConfig().setProperty(
61            "db.trigfuncs.in.radians","true");
62  
63        //set up properties so we connect to the db and translate to the correct
64        //flavour of SQL
65        //
66        ConfigFactory.getCommonConfig().setProperty(
67            JdbcConnections.JDBC_DRIVERS_KEY, "org.hsqldb.jdbcDriver");
68        //in memory db - doesn't seem to persist between calls...
69        //ConfigFactory.getCommonConfig().setProperty(
70        //       JdbcConnections.JDBC_URL_KEY, "jdbc:hsqldb:."); 
71        
72        // This creates db on disk
73        // Need "shutdown=true" to force the DB to shut down when no 
74        // active connections exist (required from v1.7.2 onwards) 
75        ConfigFactory.getCommonConfig().setProperty(
76            JdbcConnections.JDBC_URL_KEY, "jdbc:hsqldb:dummydb;shutdown=true"); 
77        ConfigFactory.getCommonConfig().setProperty(
78            JdbcConnections.JDBC_USER_KEY, "sa");
79        ConfigFactory.getCommonConfig().setProperty(
80            JdbcConnections.JDBC_PASSWORD_KEY, "");
81  
82        ConfigFactory.getCommonConfig().setProperty(
83            "datacenter.sqlmaker.xslt","HSQLDB-1.8.0.xsl" );
84        
85        //it's a bit naughty setting this, but it sorts out most tests
86        ConfigFactory.getCommonConfig().setProperty("datacenter.url", ConfigFactory.getCommonConfig().getProperty("datacenter.url", "http://localhost:8080/pal-Sample/"));
87        
88        //set where to find the data description meta document
89        //this works OK for unit test, but not deployment...
90        URL url = SampleStarsPlugin.class.getResource("samplestars.metadoc.xml");
91        if (url == null) {
92           //this works OK for deployment, but not unit tests...
93           try {
94              url = ConfigReader.resolveFilename("samplestars.metadoc.xml");
95           }
96           catch (IOException e) {
97              throw new RuntimeException(e);
98           }
99        }
100       ConfigFactory.getCommonConfig().setProperty(TableMetaDocInterpreter.TABLE_METADOC_URL_KEY, url.toString());
101 
102       //configure which resources to produce
103       ConfigFactory.getCommonConfig().setProperties(VoDescriptionServer.RESOURCE_PLUGIN_KEY, new Object[] {
104                //TabularSkyServiceResources.class.getName(),
105                TabularDbResources.class.getName()
106             });
107 
108 
109       //set up the properties for the authority bit
110       ConfigFactory.getCommonConfig().setProperty("datacenter.name", "Default Astrogrid DSA/Catalog running test database");
111       ConfigFactory.getCommonConfig().setProperty("datacenter.publisher", "AstroGrid");
112       ConfigFactory.getCommonConfig().setProperty("datacenter.description", "This is a default (unconfigured) DSA/catalog installation.  It accesses a small HSQLDB database containing fictitious tables of stars and galaxies for testing and demonstration purposes.");
113       ConfigFactory.getCommonConfig().setProperty("datacenter.contact.name", "The AstroGrid Team");
114       ConfigFactory.getCommonConfig().setProperty("datacenter.contact.email", "astrogrid_dsa@star.le.ac.uk");
115 
116       ConfigFactory.getCommonConfig().setProperty("default.table","SampleStars");
117       // Conesearch and self-test properties
118       ConfigFactory.getCommonConfig().setProperty("datacenter.self-test.table","SampleStars");
119       ConfigFactory.getCommonConfig().setProperty("datacenter.self-test.column1","RA");
120       ConfigFactory.getCommonConfig().setProperty("datacenter.self-test.column2","DEC");
121 
122       ConfigFactory.getCommonConfig().setProperty("conesearch.table","SampleStars");
123       ConfigFactory.getCommonConfig().setProperty("conesearch.ra.column","RA");
124       ConfigFactory.getCommonConfig().setProperty("conesearch.dec.column","DEC");
125       ConfigFactory.getCommonConfig().setProperty("conesearch.column.units","deg");
126 
127       //
128       ConfigFactory.getCommonConfig().setProperty("datacenter.authorityId", "astrogrid.org");
129       ConfigFactory.getCommonConfig().setProperty("datacenter.resourceKey", "test-dsa-catalog");
130     }
131     
132    public static synchronized void initialise() throws DatabaseAccessException {
133       
134       if (initialised) return;
135       
136       initConfig();
137 
138       populateDb();
139     }
140        
141    /*** Creates & populates the in-memory database.
142     * Static so we can reach it from our test harness
143     */
144    public static synchronized void populateDb() throws DatabaseAccessException {
145        
146       //connect
147       Connection connection = null;
148       try {
149          connection = JdbcConnections.makeFromConfig().createConnection();
150       }
151       catch (SQLException se) {
152          throw new DatabaseAccessException("Could not connect to JDBC: "+se);
153       }
154       
155       //first remove in case there in memory still from previous test
156       try {
157          connection.createStatement().execute("DROP TABLE SampleStars"  );
158          connection.createStatement().execute("DROP TABLE SampleGalaxies"  );
159          connection.createStatement().execute("DROP TABLE Plates"  );
160       }
161       catch (SQLException se) {
162          //ignore - may not exist
163       }
164       
165       log.info("Populating Database");
166 
167    
168       try {
169          //populate stars
170          //create table
171          connection.createStatement().execute(
172             "CREATE TABLE SampleStars (Id INTEGER IDENTITY,  Name VARCHAR(30), Ra DOUBLE,  Dec DOUBLE,  Mag DOUBLE, Flag BOOLEAN)  "
173          );
174 
175          //create index on table
176          connection.createStatement().execute(
177             "CREATE INDEX ssIndex ON SampleStars (Ra, Dec)  "
178          );
179          
180          
181          //add some stars
182          for (int i=0;i<20;i++) {
183             String flag;
184             if (i/2 == 0) {
185                flag="true";
186             }
187             else {
188                flag="false";
189             }
190             connection.createStatement().execute(
191                "INSERT INTO SampleStars VALUES ("+i+", 'A star', "+(30+i*2)+", "+(30-i*2)+", "+i+","+flag+")"
192             );
193          }
194 
195          //add false pleidies.  These are stars grouped < 0.3 degree across on ra=56.75, dec=23.867
196          int id=21;
197          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies LE', 56.6, 23.65, 10, false)"); id++;
198          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies RE', 56.9, 23.65, 10, true)"); id++;
199          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Nose', 56.75, 23.87, 8, false)"); id++;
200          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.5, 23.9, 12, true)"); id++;
201          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.7, 24.0, 12, false)"); id++;
202          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 56.8, 24.0, 12, true)"); id++;
203          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Pleidies Grin', 57.0, 23.9, 12, false)"); id++;
204 
205          //add stars that are outside the above group but nearby
206          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56.6, 23.6, 10, true)"); id++;
207          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56, 23, 5, false)"); id++;
208          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 58, 24.5, 5, true)"); id++;
209          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 56, 24.5, 5, false)"); id++;
210          connection.createStatement().execute("INSERT INTO SampleStars VALUES ("+id+", 'Not Pleidies', 58, 23, 5, true)"); id++;
211          
212          //add even spread (in coordinate space) of background stars
213          for (double ra=0;ra<360;ra=ra+2) {
214 //            StringBuffer sql = new StringBuffer("INSERT INTO SampleStars VALUES ");
215             for (double dec=-90;dec<90;dec=dec+2) {
216 //               sql.append(" ("+id+", 'Background', "+ra+", "+dec+", 20) "); id++;
217                connection.createStatement().execute("INSERT INTO SampleStars VALUES  ("+id+", 'Background', "+ra+", "+dec+", 20, false)"); id++;
218             }
219 //           connection.createStatement().execute(sql.toString());
220             System.out.print(".");
221          }
222          
223          //populate galaxies
224          //create table
225          connection.createStatement().execute(
226             "CREATE TABLE SampleGalaxies (Id INTEGER IDENTITY,  Ra DOUBLE,  Dec DOUBLE,  Shape VARCHAR(20)) "
227          );
228          
229          //add individual galaxies
230          String[] shapes = new String[] {"ELLIPTICAL", "SPIRAL", "IRREGULAR" };
231          for (int i=0;i<20;i++) {
232             
233             connection.createStatement().execute(
234                "INSERT INTO SampleGalaxies VALUES ("+i+", "+(200+i*2)+", "+(200-i*2)+", '"+shapes[i % 2]+"')"
235             );
236          }
237    
238          //populate plate tables
239          connection.createStatement().execute(
240             "CREATE TABLE Plates (Id INTEGER IDENTITY,  CenterRa DOUBLE,  CenterDec DOUBLE,  ObsTime DATETIME) "
241          );
242          long todayTime = new Date().getTime();
243          for (int i=0;i<5;i++) {
244             connection.createStatement().execute(
245 //               "INSERT INTO Plates VALUES ("+i+", "+(100+i*20)+", "+(100-i*20)+", "+sqlDateFormat.format(new java.sql.Date(todayTime-i*1000))+")"
246                "INSERT INTO Plates VALUES ("+i+", "+(100+i*20)+", "+(100-i*20)+", '"+new java.sql.Timestamp(todayTime-i*1000)+"')"
247             );
248          }
249          
250          
251          connection.commit();
252          connection.close();
253       }
254       catch (SQLException se) {
255          log.error("Populating demo db",se);
256       }
257 
258       initialised = true;
259       
260       log.info("...database populated"); //so that we can mark how long it took
261       //check metadata
262       /*
263       try {
264          JdbcPlugin plugin = new SampleStarsPlugin(null);
265          Document metadata = plugin.getMetadata();
266          log.info(DomHelper.DocumentToString(metadata));
267       } catch (IOException ioe) {
268          throw new RuntimeException(ioe);
269       }
270        */
271    }
272 
273    /* Sample SQL statemetns to help with above:
274    
275    CREATE TABLE Customer (Customer_no INTEGER IDENTITY, firstname VARCHAR(15), lastname VARCHAR(50), address VARCHAR(150), postalcode VARCHAR(7));
276 
277    CREATE TABLE Movies (Movie_id INTEGER IDENTITY, title VARCHAR(50), classification VARCHAR(5), star_1 VARCHAR(35), star_2 VARCHAR(35), release_date DATETIME);
278 
279    CREATE TABLE Rentals (Customer_no INTEGER, Movie_id INTEGER, date_out DATETIME, date_in DATETIME);
280    
281    INSERT INTO Customer VALUES (null, 'Joe','Smith','100 Main St','T5T 1A1');
282    INSERT INTO Customer VALUES (null, 'Joe','Smith','100 Main St','T5T 1A1');
283    INSERT INTO Customer VALUES (null, 'Jane','Jones','100 Main St','T5T 1A1');
284    INSERT INTO Customer VALUES (null, 'Bill','Black','100 Main St','T5T 1A1');
285 
286 
287    INSERT INTO Movies VALUES (null, 'Return of the Jedi', 'G','star 1', 'star 2', 'now');
288    INSERT INTO Movies VALUES (null, 'MASH', 'A','star 1', 'star 2', 'now');
289    INSERT INTO Movies VALUES (null, 'Signs', 'PG','star 1', 'star 2', 'now');
290     */
291    
292 }
293