2.2 DataBase subsystem - COMMON-Sense Net Home - EPFL

assistantashamedΔιαχείριση Δεδομένων

29 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

344 εμφανίσεις



16
-
03
-
2013

11
-
1









COMMON
-
Sense
Net

DataBase



Jacques Panchard (EPFL)




October 31st
, 2004

Updated April 17
th
, 2007


16
-
03
-
2013

11
-
2



Web server

DB

Java
Application

Base station

commands

Display

node

node

node

node

sensor

sensor

Wireless link


1

The COMMON Sense
Network architecture

The COMMON Sense
wireless
net
work

is built as a tree.

Every mote has
one single

parent, and
communicates with

it. The root of the tree is the base station, the only module which is directly linked to
the java application. Each mote collects its sensors’ informations and sends them towards the root. They
don’t exchange data between them. The packets are routed fro
m the originating node to its parent in a
multihop fashion up to the sink (base station).


The Java application receives data from the motes network and puts them in a database. A web server can
access these data and display them. From the web interface, c
ommands can be sent to the motes.



































16
-
03
-
2013

11
-
3


2

Software
System design

2.1

Block diagram



Figure
1

Application Block Diagram



16
-
03
-
2013

11
-
4


2.2

DataBase subsystem

2.2.1

DBLogger

It uses the java.sql primitives to connect to the database a
nd insert, update or delete tables and rows.

MoteInfo
.

It implements a ResultListener, which it uses to retrieves the motes’ data

from ICMonNetwork.

initDBConn()

: Initializes the connection to the Database Subsystem

logQuery(ICMonQuery queryToLog)

: Does
the setup work for starting to log a query

createTableStmt(ICMonQuery query, String tableName)

: Returns a query string which can create a
table

insertStmt(QueryResult qr, String tableName)

: returns a query string which can insert a new element
in the DB.



2.2.2

MoteInfo

Using it own DBLogger, it updates the information relative to the individual motes and the network (not
the sensor data)

2.2.3

Database


The database is a PostgreSQL.


The logged tables with their parameters are listed below:



2.2.3.1

Log_Cmd_Msg

This
table

is used
to log command messages

sent to the network. The command is stored as a string, with
its associated address, value and
cluster id

create table icmon_log_cmd_msg (


id SERIAL PRIMARY KEY,


seqno INT NOT NULL,


time TIMESTAMP NOT NULL,


cmd VARCHAR(255) NOT NULL,


addr INT,


value INT
,


clusterid INT REFERENCES icmon_cluster ON DELETE CASCADE ON UPDATE CASCADE



);

2.2.3.2

Cluster

This table is used to store i
nformation about clusters
. A cluster usually refers to a physical instance o
f a
sensor network. A cluster is associated with the name of a data table (which are created dynamically)

and
a unique

TOS_GROUP_ID

.
Duplicates for data tables and tos_group_ids must be looked for.

tos_group_ids are unique but reusable.

Data table names
are unique.

create table icmon_cluster (


clusterid SERIAL PRIMARY KEY,


tosgroupid INT NOT NULL,


datatablename varchar(30),


hostname varchar(100),


description VARCHAR(1000),



16
-
03
-
2013

11
-
5



long INT,


lat INT


);



2.2.3.3

Mote_Info

This
table
is used

to store information about motes
, such as their associated cluster_id, their location, and
the platform type.


create table icmon_mote_info (


miid SERIAL PRIMARY KEY,


nodeid INT NOT NULL,


clusterid INT REFERENCES icmon_cluster ON DELETE CASCAD
E ON UPDATE CASCADE,


location_x INT,


location_y INT,


type varchar(30),


location VARCHAR(255),


comment VARCHAR(1000),

lastbatchange TIMESTAMP


);


2.2.3.4

Application_Group

This
table
is
to store information about application group
s, namely i
ndividual experiments conducted on a
set or subset of nodes from one or several clusters.


create table icmon_application_group (


groupid SERIAL PRIMARY KEY,


name varchar(30),


description VARCHAR(1000),


date_from TIMESTAMP NOT NULL,


dat
e_to TIMESTAMP NOT NULL


);


2.2.3.5

Node_Group

This is a
joint table to associate a mote to an application grou
p
.

create table icmon_node_group (


groupid INT REFERENCES icmon_application_group ON DELETE CASCADE ON UPDATE
CASCADE,


miid INT REFERENCES ic
mon_mote_info ON DELETE CASCADE ON UPDATE CASCADE


);


2.2.3.6

Routing_Parent

This
table
is used
to store parent statistics
.

CREATE TABLE icmon_routing_parent (


id SERIAL PRIMARY KEY,


time TIMESTAMP NOT NULL,


miid INT REFERENCES icmon_mote_info ON D
ELETE CASCADE ON UPDATE CASCADE,


parent INT NOT NULL,


quality INT DEFAULT
-
1,


depth INT DEFAULT
-
1,


occupancy INT DEFAULT
-
1


);



16
-
03
-
2013

11
-
6



2.2.3.7

NeighborTable

This
table
is used
to store neighbor information

for each node.


CREATE TABLE icmon_neighbort
able (


id SERIAL PRIMARY KEY,


time TIMESTAMP NOT NULL,


miid INT REFERENCES icmon_mote_info ON DELETE CASCADE ON UPDATE CASCADE,


neighborid INT NOT NULL,


missed INT NOT NULL,


received INT NOT NULL,


lastSeqno INT NOT NULL,


hop

INT NOT NULL,


receiveEst INT NOT NULL,


sendEst INT NOT NULL


);



The table
used to store the sensor data is created at the first invocation of the java application.


3

Notes on database design


We chose a design in which
every sensor network bas
e station must be connected to a general datalogger.
Another option would be

to use
simply
a SerialForwarder
in order to connect
to a single machine where
all the incoming messages would be logged into the database based on their tos_group_id and node_id.

This would require also the possibility to create a cluster name (data table name) from the web interface.


Using model
-
based user views as described in
Deshpande & Madden 2006, we could add an abstraction
layer useful to render the data retrieved by the u
sers usable for modeling and prediction.


3.1

Regression/interpolation of soil moisture

We retrieve data at 2 depths. Can we have a simple regression/interpolation model to assess the soil
moisture in between? This would provide a tool to indicate how water p
ercolates over time.


Similarly, we deploy n sensors per homogenous parcel, and expect to be able to use statistics to represent
the parcel accurately. In between parcels, there is a solution of continuity due to geographical change or
cultivation diversit
y (type of crop, irrigated versus non
-
irrigated etc.).


It would be very useful to know what
kinds of statistics are relevant for a

homogenous parcel.


3.2

Granularity

The optimal sampling period is unknown at this point. Determining the level of redundancy,
the point at
which valuable information is lost in the process is a key factor for optimization, and for the determination
of the usefulness of a sensor network.




16
-
03
-
2013

11
-
7


The same problematic applies to the spatial diversity.
The goal is to determine h
ow many sens
ors are
necessary, what is the necessary sampling period, so that we can determine if a wireless sensor network is
a suitable solution in our case.


For this, analysis of the data in the database is
necessary

in order to find out the level of spatial and t
ime
redundancy of the data we retrieve. How to design such data analysis tools?


If a higher than necessary number of sensors is deployed, and these sensors are sampling the data at a
higher rate than mandatory, what is the critical set of data to retrieve
?


3.3

Data retrieval optimization

How do we optimize data retrieval based on possible database data
projections?

Possible projections
include:

1.

application
-
groups, that can span several clusters

2.

cluster or cluster sub
-
set

3.

Retrieve at time intervals with differ
ent time granularities.


If we under
-
sample the data for a given time
-
window (example, if we take daily values, whereas data is
retrieved every 5 minutes), how do we choose the data from the DB?


What are the most relevant statistics?






16
-
03
-
2013

11
-
8


Appendix A.

Managing PostGreSQ
L

Download and unpack the current version of PostGresSQL from

http://www.postgresql.org/


Then

do the following (we assume you installed the version 7.4.6 of the DBMS)

cd postgresql
-
7.4.6


./configure
--
without
-
r
eadline



Even though
you have GNU Readline installed on your

system,
Add

the "
--
without
-
readline" option.

The

PostgreSQL installation directory will be
/usr/local/pgsql. To change it
, use the "
--
prefix" option and
spec
ify a different directory (
"
--
prefix=
/export/home/postgresql"
)
.

Then b
uild PostgreSQL (make sure you
are l
ogged in as user "root"
):

gmake


gmake install



Create the "postgres" user:



adduser postgres



Create the PostreSQL data directory:


mkdir /usr/local/pgsql/data


chown postgres /u
sr/local/pgsql/data



As
"icmon" user, initialize the database server and start it:


su


icmon


/usr/local/pgsql/bin/initdb
-
D /usr/local/pgsql/data


/usr/local/pgsql/bin/postmaster
-
D /usr/local/pgsql/data >logfile 2>&1 &



Recommended, but optiona
l, is to set the $POSTGRESQL_HOME variable in /etc/profile to point to the
installation directory:


export POSTGRESQL_HOME=/usr/local/pgsql



Change the location as necessary.

Make sure the $POSTGRESQL_HOME/bin directory is in your $PATH
:


export PATH=
$PATH:$POSTGRESQL_HOME/bin

A
dd in the PATH:
PATH=$PATH:/usr/local/pgsql/bin/



Start/Stop server


Don’t forget to be logged in as icmon user:

[icmon@lca2
-
s1
-
pc2 icmon]$ pg_ctl start
-
D /usr
/local/pgsql/data
-
l logfile

[icmon@lca2
-
s1
-
pc2 icmon]$ pg_ctl st
op
-
D /usr/local/pgsql/data/
-
m fast




Plot graphs


To plot
graph
s

w
ith get_graph.py, access

the database which isn't on the same computer.


[icmon@lca2
-
s1
-
pc1 sensor_data]$ python get_graph.py testdb voltage "2004
-
12
-
03 10:20:54.200138"
"2004
-
12
-
03 10:20
:54.200138" icmondb




16
-
03
-
2013

11
-
9



Traceback (most recent call last):

File "get_graph.py", line 84, in ?

conn = DB(db, dbserver,
-
1, '', '', username, password)

File "/usr/lib/python2.3/site
-
packages/pg.py", line 49, in __init__

self.db = connect(*args, **kw)

_pg.
error: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "icmon", database "icmondb", SSL off


Acceed database from another host


To access

the data
base from another host,
modify the pg_hba.conf file in the

/u
sr/local/pgsql/data/ folder. Add the fo
llowing lines
:


host all all 128.178.156.101 255.255.255.255 trust

host all all 127.0.0.1 255.255.255.255 trust


At this point a

database connexion error can occur

(pg_connect(...) in the php

files). To correct this install
the following package: php
-
pgsql


$ yum install php
-
pgsql.i386






16
-
03
-
2013

11
-
10


Appendix B.

CSN Configuration file

(/projects/ICMon/etc/icmon.conf)


%format param:value

%postgres server settings

postgres
-
user:icmon

postgres
-
passwd:donttell

postgres
-
db:icmondb

postgres
-
host:128.178.156.102

java
-
host:128.178.156.10
2

table
-
log
-
queries:icmon_log_queries

table
-
mote
-
info:icmon_mote_info

table
-
neighbortable:icmon_neighbortable

table
-
statisticstable:icmon_stattable

table
-
routing
-
parent:icmon_routing_parent

table
-
log
-
cmd:icmon_log_cmd_msg

%map file

map
-
file:/projects
/icmon
/images/map_bc_3floor.png

%parameters for the map file

map
-
file
-
ulx:532701

map
-
file
-
uly:152324

map
-
file
-
lrx:532751

map
-
file
-
lry:152259

voltage
-
threshold:2.2


%image file names

on the web server

(…)

%sensor ids for sensorflag (have to be powers of two)

sens
or
-
voltage:1

sensor
-
temp:2

sensor
-
light:4

sensor
-
mic:8

sensor
-
accelx:16

sensor
-
accely:32

sensor
-
magx:64

sensor
-
magy:128

sensor
-
goodmessages:256

sensor
-
badmessages:512




16
-
03
-
2013

11
-
11


4

Bibliography


Rao 2004

PR Seshagiri Rao, Madhav Gadgil, Ramakrishnappa, M

Gangadhar,

Re
port of user
requirement survey
,
CES, CAOS, IISc. BANGALORE, 2004

Schmid 2004

Thomas Schmid,
Sensorscope
, MICS Summer Internship, EPFL, 2004
,
http://sensorscope.epfl.ch

Panchard 2004

Jacques Panchard,
COMMON
-
Sens
e Net System Requirements and High
-
Level Design
,

EPFL, 2004

CEDT2004

CEDT
, COMMON
-
Sense Net, Working draft for MICA2 motes (Phase 1)
, Technical
Report,
November 17, 2004

Deshpande & Madden 2006,
MauveDB: Supporting Modelbased

User Views in

Database Systems
,
SIGMOD 2006