Database

The weather-data-flaskapi is based on a MariaDB database store. The following are required to created the database, create a user, grant permissions, and create the require tables and views.

Database Setup

To create the database, open /mysql/ and create the database.

CREATE DATABASE Weather CHARACTER SET utf8 COLLATE utf8_general_ci;

The database creation can be confirmed using the following command in mysql.

SHOW DATABASES;

Database Permissions

Create a user to access the database. Change weather-flaskapi-user and mypass to the username and password.

CREATE USER 'weather-flaskapi-user'@'localhost' IDENTIFIED BY 'mypass';

GRANT DELETE ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT INSERT ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT SELECT ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT UPDATE ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT LOCK TABLES ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT EXECUTE ON Weather.* TO 'weather-flaskapi-user'@'%';
GRANT CREATE TEMPORARY TABLES ON Weather.* TO 'weather-flaskapi-user'@'%';

FLUSH PRIVILEGES;

Confirm the permissions for the user.

SHOW GRANTS FOR 'weather-flaskapi-user'@'%';

Database Content

Create the database tables to record the humidity, pressure, and temperature values. Create a web service user table.

CREATE TABLE Weather.humidity
(
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value DECIMAL(8,4) NOT NULL,
  value_units NVARCHAR(16) NOT NULL,
  value_error_range DECIMAL(7,6) NOT NULL DEFAULT 0.0,
  latitude DECIMAL(8,6) NOT NULL,
  latitude_public DECIMAL(8,6) NOT NULL,
  longitude DECIMAL(9,6) NOT NULL,
  longitude_public DECIMAL(9,6) NOT NULL,
  city NVARCHAR(64) NOT NULL,
  province NVARCHAR(64) NOT NULL,
  country NVARCHAR(64) NOT NULL,
  elevation DECIMAL(8,4) NOT NULL,
  elevation_units NVARCHAR(16) NOT NULL,
  timestamp DATETIME NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8;
CREATE INDEX humidity_value_IDX ON Weather.humidity (value);
CREATE INDEX humidity_latitude_IDX ON Weather.humidity (latitude);
CREATE INDEX humidity_latitude_public_IDX ON Weather.humidity (latitude_public);
CREATE INDEX humidity_longitude_IDX ON Weather.humidity (longitude);
CREATE INDEX humidity_longitude_public_IDX ON Weather.humidity (longitude_public);
CREATE INDEX humidity_timestamp_IDX ON Weather.humidity (timestamp);
CREATE INDEX humidity_city_IDX ON Weather.humidity (city);
CREATE INDEX humidity_province_IDX ON Weather.humidity (province);
CREATE INDEX humidity_country_IDX ON Weather.humidity (country);

CREATE VIEW Weather.humidity_public_view AS SELECT 
  id,
  value,
  value_units,
  value_error_range,
  latitude_public,
  longitude_public,
  city,
  province,
  country,
  timestamp
FROM Weather.humidity;


CREATE TABLE Weather.pressure
(
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value DECIMAL(8,4) NOT NULL,
  value_units NVARCHAR(16) NOT NULL,
  value_error_range DECIMAL(7,6) NOT NULL DEFAULT 0.0,
  latitude DECIMAL(8,6) NOT NULL,
  latitude_public DECIMAL(8,6) NOT NULL,
  longitude DECIMAL(9,6) NOT NULL,
  longitude_public DECIMAL(9,6) NOT NULL,
  city NVARCHAR(64) NOT NULL,
  province NVARCHAR(64) NOT NULL,
  country NVARCHAR(64) NOT NULL,
  elevation DECIMAL(8,4) NOT NULL,
  elevation_units NVARCHAR(16) NOT NULL,
  timestamp DATETIME NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8;
CREATE INDEX pressure_value_IDX ON Weather.pressure (value);
CREATE INDEX pressure_latitude_IDX ON Weather.pressure (latitude);
CREATE INDEX pressure_latitude_public_IDX ON Weather.pressure (latitude_public);
CREATE INDEX pressure_longitude_IDX ON Weather.pressure (longitude);
CREATE INDEX pressure_longitude_public_IDX ON Weather.pressure (longitude_public);
CREATE INDEX pressure_timestamp_IDX ON Weather.pressure (timestamp);
CREATE INDEX pressure_city_IDX ON Weather.pressure (city);
CREATE INDEX pressure_province_IDX ON Weather.pressure (province);
CREATE INDEX pressure_country_IDX ON Weather.pressure (country);

CREATE VIEW Weather.pressure_public_view AS SELECT 
  id,
  value,
  value_units,
  value_error_range,
  latitude_public,
  longitude_public,
  city,
  province,
  country,
  timestamp
FROM Weather.pressure;


CREATE TABLE Weather.temperature
(
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  value DECIMAL(8,4) NOT NULL,
  value_units NVARCHAR(16) NOT NULL,
  value_error_range DECIMAL(7,6) NOT NULL DEFAULT 0.0,
  latitude DECIMAL(8,6) NOT NULL,
  latitude_public DECIMAL(8,6) NOT NULL,
  longitude DECIMAL(9,6) NOT NULL,
  longitude_public DECIMAL(9,6) NOT NULL,
  city NVARCHAR(64) NOT NULL,
  province NVARCHAR(64) NOT NULL,
  country NVARCHAR(64) NOT NULL,
  elevation DECIMAL(8,4) NOT NULL,
  elevation_units NVARCHAR(16) NOT NULL,
  timestamp DATETIME NOT NULL
) ENGINE=Aria DEFAULT CHARSET=utf8;
CREATE INDEX temperature_value_IDX ON Weather.temperature (value);
CREATE INDEX temperature_latitude_IDX ON Weather.temperature (latitude);
CREATE INDEX temperature_latitude_public_IDX ON Weather.temperature (latitude_public);
CREATE INDEX temperature_longitude_IDX ON Weather.temperature (longitude);
CREATE INDEX temperature_longitude_public_IDX ON Weather.temperature (longitude_public);
CREATE INDEX temperature_timestamp_IDX ON Weather.temperature (timestamp);
CREATE INDEX temperature_city_IDX ON Weather.temperature (city);
CREATE INDEX temperature_province_IDX ON Weather.temperature (province);
CREATE INDEX temperature_country_IDX ON Weather.temperature (country);

CREATE VIEW Weather.temperature_public_view AS SELECT 
  id,
  value,
  value_units,
  value_error_range,
  latitude_public,
  longitude_public,
  city,
  province,
  country,
  timestamp
FROM Weather.temperature;


CREATE TABLE Weather.user
(
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username NVARCHAR(64) NOT NULL,
  password NVARCHAR(120) NOT NULL,
  salt NVARCHAR(64) NOT NULL,
  enabled BIT NOT NULL,
  created_date DATETIME NOT NULL DEFAULT NOW(),
  last_login_date DATETIME NULL
) ENGINE=Aria DEFAULT CHARSET=utf8;
CREATE INDEX user_username_IDX ON Weather.user (username);

Confirm the creation of the tables and views.

USE Weather;
SHOW TABLES;