Organization of the DB¶

GWA uses a PostgreSQL database. The structure is as follows: The structure is as follows:

Database gwa:

Table area for storage of environments

Table place for storage of places

Table ap for storage of access points

Table ap_configuration for storage of access points configurations

Table campaign for storage of measurement campaigns

Table measure for storage of measurements made during campaigns

Table disturbing_ap for storage of access points located in the radio environment when measured

Relations and the different fields are:

Complete structure of the DB¶

Table `area`¶

Primary Key: id_area

Structure of the table

CREATE TABLE area (
        id_area                 SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        name                    varchar(80)     UNIQUE  NOT NULL,       -- area name
        width                   int             NOT NULL,               -- area width
        length                  int             NOT NULL,               -- area length
        height                  int             NOT NULL,               -- area height
        x_coordinate_area       int             NOT NULL,               -- x coordinate in area orthonormal basis       - "0" by default
        y_coordinate_area       int             NOT NULL,               -- y coordinate in area orthonormal basis       - "0" by default
        z_coordinate_area       int             NOT NULL,               -- z coordinate in area orthonormal basist      - "0" by default
        description             text,                                   -- area description
        origin_info             text                                    -- information about the point of origine to remember which one is it into the place
        );

Table `place`¶

Primary Key: id_place

Foreign Key: id_area

Unique constraint: id_area/name

Structure of the table

CREATE TABLE place (
        id_place                SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        id_area                 int             NOT NULL REFERENCES area(id_area) ON UPDATE CASCADE ON DELETE CASCADE,  -- Foreign Key: id of the linked area
        name                    varchar(80)     NOT NULL,               -- place name
        width                   int             NOT NULL,               -- place width
        length                  int             NOT NULL,               -- place width
        height                  int             NOT NULL,               -- place width
        x_coordinate_area       int             NOT NULL,               -- x coordinate in area orthonormal basis
        y_coordinate_area       int             NOT NULL,               -- y coordinate in area orthonormal basis
        z_coordinate_area       int             NOT NULL,               -- z coordinate in area orthonormal basis
        angle                   int             NOT NULL,               -- angle between x axis of the place and the x axis of the area
        description             text,                                   -- place description
        origin_info             text,                                   -- information about the point of origine to remember which one is it into the
        CONSTRAINT area_and_place_name_couple UNIQUE (id_area, name)
        );

Table `campaign`¶

Primary Key: id_campaign

Foreign Key: id_place

Unique constraint: id_place/name

Structure of the table

CREATE TABLE campaign (
        id_campaign             SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        id_place                int             NOT NULL REFERENCES place(id_place) ON UPDATE CASCADE ON DELETE CASCADE,        -- Foreign Key: id of the linked ap
        name                    varchar(80)     NOT NULL UNIQUE,        -- unique name of for a campaign of measures
        x_step                  int             NOT NULL,               -- x step
        y_step                  int             NOT NULL,               -- y step
        z_step                  int             NOT NULL,               -- z step
        date                    date            NOT NULL,               -- calendar date of the campaign creation (year, month, day)
        eperf_parameters        text,                                   -- parameters of the eperf probe if used for the campaign
        tx_power                int,                                    -- tx power of the wlan ward signal
        rts_cts                 varchar(10),                            -- threshold of the rts/cts ('0' if OFF)
        fragment                varchar(10),                            -- length of the fragment ('0' if is not fragmented)
        description             text,                                   -- Campaign description
        CONSTRAINT place_and_campaign_name_couple UNIQUE (id_place, name)
        );

Table `ap`¶

Primary Key: id_ap

Foreign Key: id_place

Unique constraint: mac_address/essid

Structure of the table

CREATE TABLE ap (
        id_ap                   SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        id_place                int             REFERENCES place(id_place) ON UPDATE CASCADE ON DELETE CASCADE, -- Foreign Key: id of the linked place
        mac_address             varchar(17)     NOT NULL,               -- a MAC address linked to one or more ESSID
        essid                   varchar(32)     NOT NULL,               -- an ESSID can be linked to 1 to X MAC address
        x_coordinate_place      int             NOT NULL,               -- x coordinate in area orthonormal basis
        y_coordinate_place      int             NOT NULL,               -- y coordinate in area orthonormal basis
        z_coordinate_place      int             NOT NULL,               -- z coordinate in area orthonormal basis
        description             text,                                   -- AP description is unique to be identified as an already configured AP
        CONSTRAINT mac_essid_couple UNIQUE (mac_address, essid)
        );

Table `ap_configuration`¶

Primary Key: id_campaign/id_ap

Foreign Key: id_campaign/id_ap

Structure of the table

CREATE TABLE ap_configuration (
        id_campaign             int             NOT NULL REFERENCES campaign(id_campaign) ON UPDATE CASCADE ON DELETE CASCADE,  -- Primary Key / Foreign Key
        id_ap                   int             NOT NULL REFERENCES ap(id_ap) ON UPDATE CASCADE ON DELETE CASCADE,              -- Primary Key / Foreign Key
        channel                 int             NOT NULL,               -- channel of the AP
        encryption              varchar(10)     NOT NULL,               -- encryption of the AP
        standard                varchar(10)     NOT NULL,               -- standard use by the access point for this campaign
        PRIMARY KEY (id_campaign, id_ap)
        );

Table `measure`¶

Primary Key: id_measure

Foreign Key: id_ap/id_campaign

Unique constraint: id_campaign/x_coordinate_place/y_coordinate_place/z_coordinate_place

Structure of the table

CREATE TABLE measure (
        id_measure              SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        id_ap                   int             NOT NULL REFERENCES ap(id_ap) ON UPDATE CASCADE ON DELETE CASCADE,      -- Foreign Key: MAC address of the connected AP
        id_campaign             int             NOT NULL REFERENCES campaign(id_campaign) ON UPDATE CASCADE ON DELETE CASCADE,  -- Foreign Key: id of the linked campaign
        x_coordinate_place      int             NOT NULL,               -- x coordinate in place orthonormal basis
        y_coordinate_place      int             NOT NULL,               -- y coordinate in place orthonormal basis
        z_coordinate_place      int             NOT NULL,               -- z coordinate in place orthonormal basis
        signal_level            varchar(10)     NOT NULL,               -- signal level of the AP in dBm (iwconfig): 0 to -100 dBm
        noise_level             varchar(10)     NOT NULL,               -- noise level of the AP in dBm (iwconfig): 0 to -100 dBm
        bitrate_level2          varchar(10)     NOT NULL,               -- bitrate at level 2 of the AP in Mbits/s (iwconfig)
        rate_level4             text,                                   -- rate of the AP at level 4 (X values for each measure with separation)
        timestamp               timestamp       NOT NULL UNIQUE,        -- date of the measure
        CONSTRAINT campaign_x_y_z UNIQUE (id_campaign, x_coordinate_place, y_coordinate_place, z_coordinate_place)
        );

Table `disturbing_ap`¶

Primary Key: id_disturbing_ap

Foreign Key: id_measure

Structure of the table

CREATE TABLE disturbing_ap (
        id_disturbing_ap        SERIAL          PRIMARY KEY,            -- Primary Key that is: NOT NULL, UNIQUE, AUTO-INCREMENT
        id_measure              int             NOT NULL REFERENCES measure(id_measure) ON UPDATE CASCADE ON DELETE CASCADE,    -- Foreign Key: id of the linked measure
        essid                   varchar(32)     NOT NULL,               -- ESSID
        mac_address             varchar(17)     NOT NULL,               -- Mac address of the disturbing AP
        channel                 int             NOT NULL,               -- channel of the disturbing AP
        encryption              varchar(10)     NOT NULL,               -- encryption of the disturbing AP disturbing
        protocol                varchar(10),                            -- protocol of the disturbing AP (a/b/g/n)
        signal_level            varchar(10)     NOT NULL                -- signal level of the AP in dBm (iwconfig): 0 to -100 dBm
        );

Administration of PostgreSQL¶

PostgreSQL Installation
```
sudo apt-get install postgresql
```

Remove PostgreSQL

sudo apt-get remove --purge postgresql-8.4*

Initializing the password of postgres user in the system (administrator)
```
sudo passwd postgres
```
Database connection
- With a user X
  sudo -u postgres psql postgres
- With the postgres user
  psql postgres
Specify the password of postgres user in the database
```
\password postgres
```
To exit PostgreSQL: “Control + D” or “q”
Create a database
```
sudo -u postgres createdb mydb
```
Log into a database
```
sudo -u postgres psql mydb
```

List specific information of databases¶

List users
```
\du
```
List the databases
```
\l
```
List tables (first connect to the database associated)
- leave a base
  \q
- select a base
  psql wifi
- list a table
  \d campaign;

Organization of the DB¶

Complete structure of the DB¶

Table `area`¶

Table `place`¶

Table `campaign`¶

Table `ap`¶

Table `ap_configuration`¶

Table `measure`¶

Table `disturbing_ap`¶

Administration of PostgreSQL¶

List specific information of databases¶

Table Of Contents

Previous topic

Next topic

This Page

Navigation

Organization of the DB¶

Complete structure of the DB¶

Table area¶

Table place¶

Table campaign¶

Table ap¶

Table ap_configuration¶

Table measure¶

Table disturbing_ap¶

Administration of PostgreSQL¶

List specific information of databases¶

Table Of Contents

Previous topic

Next topic

This Page

Quick search

Navigation

Table `area`¶

Table `place`¶

Table `campaign`¶

Table `ap`¶

Table `ap_configuration`¶

Table `measure`¶

Table `disturbing_ap`¶