src/motedata/MoteData.java

   1 package motedata;
   2
   3 import java.sql.ResultSet;
   4 import util.SQLHelper;
   5
   6 /** Service class for getting mote data.
   7  *
   8  * The mote data can be anything from availability information, mote
   9  * attributes such as MAC and net address, as well as site attributes
  10  * such as position.
  11  */
  12 public class MoteData {
  13
  14         /** Get status information about all motes in the testbed.
  15          *
  16          * Builds a query to extract all info about the testbed motes
  17          * including mote and site attributes into a single table.
  18          *
  19          * @param session_id The client session.
  20          * @return A serialized SimpleTable with mote data.
  21          * @throws Exception
  22          */
  23         public SimpleTable getMoteData(String session_id) throws Exception
  24         {
  25                 String query,select;
  26                 SQLHelper sql = null;
  27                 ResultSet rs = null;
  28                 SimpleTable result = null;
  29                 Object[] param = { session_id };
  30
  31                 try {
  32                         sql = new SQLHelper();
  33                         sql.openDB();
  34                         query = "select m.id mote_id, m.site_id site_id,s.sitename site, " +
  35                                 "case when m.curr_session_id=? then 'controlled' " +
  36                                 "when isnull(m.curr_session_id) then 'available' else 'occupied' end mote_usage ";
  37
  38                         select = "select id, name from moteattrtype order by sortseq";
  39                         rs = sql.retrieve(select);
  40                         while (rs.next()) {
  41                                 query+=getMoteAttrSubquery(rs.getString("name"),rs.getString("id"));
  42                         }
  43                         select = "select id, name from siteattrtype order by sortseq";
  44                         rs = sql.retrieve(select);
  45                         while (rs.next()) {
  46                                 query+=getSiteAttrSubquery(rs.getString("name"),rs.getString("id"));
  47                         }
  48                         query+=" from mote m, site s " +
  49                                " where s.id=m.site_id";
  50                         rs = sql.retrieve(query, param);
  51                         rs.last();
  52                         int rows = rs.getRow();
  53                         int cols = rs.getMetaData().getColumnCount();
  54                         result = new SimpleTable(rows,cols);
  55                         rs.beforeFirst();
  56
  57                         for (int i=0;i<cols;i++){
  58                                 String title = rs.getMetaData().getColumnLabel(i+1);
  59                                 String name = rs.getMetaData().getColumnName(i+1);
  60                                 String classname = rs.getMetaData().getColumnClassName(i+1);
  61                                 result.getColumnHeaders()[i] = new ColumnHeader(title,name,true,classname);
  62                         }
  63
  64                         int i = 0;
  65                         while (rs.next()) {
  66                                 for (int j=0;j<cols;j++)
  67                                 {
  68                                         result.getData()[i][j] = rs.getObject(j+1);
  69                                 }
  70                                 i++;
  71                         }
  72
  73                         return result;
  74
  75                 } finally {
  76                         sql.closeDB();
  77                 }
  78         }
  79
  80         private String getMoteAttrSubquery(String colname,String type_id)
  81         {
  82                 return ", (select ma.val from moteattr ma, mote_moteattr mma" +
  83                         " where ma.moteattrtype_id="+type_id+
  84                         " and ma.id = mma.moteattr_id" +
  85                         " and mma.mote_id=m.id) "+colname+" ";
  86         }
  87
  88         private String getSiteAttrSubquery(String colname,String type_id)
  89         {
  90                 return ", (select sa.val from siteattr sa, site_siteattr ssa" +
  91                         " where sa.siteattrtype_id="+type_id+
  92                         " and sa.id = ssa.siteattr_id" +
  93                         " and ssa.site_id=s.id) "+colname+" ";
  94         }
  95 }