(As root user) Installing common "LAMP" services and some basic MySQL
---------------------------------------------------------------------

# Installing basic packages with apt
sudo bash
apt install apache2 php php-cli libapache2-mod-php php-mysql mysql-server

# Restarting Apache web server:
systemctl restart apache2

#
# NOTE: No need to run mysql_secure_installation command anymore
#
# Login to mysql (old and obsolete way):
# mysql -u root -p
#
# Or now just (ubuntu 20.04 and newer linuxes) command:
mysql

# In mysql shell:
show databases;

# Creating db and new user for it with mysql shell:
create database measurements;
show databases;
use measurements;

# Creating new table "rawdata" to measurements database:
create table rawdata (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
watts INT NOT NULL DEFAULT 0,
sensor INT NOT NULL DEFAULT 0,
temperature FLOAT NOT NULL DEFAULT 0,
location VARCHAR(256) NOT NULL DEFAULT 0
);

# Show/verify table structure:
DESCRIBE rawdata;

# Deleting whole table (just an example, not necessary now)
DROP tables rawdata;

# Creating a user "dbaccess_rw" with password "fasdjkf2389vw2c3k234vk2f3" and full granting access to the db:
CREATE USER 'dbaccess_rw'@'%' IDENTIFIED WITH mysql_native_password BY 'fasdjkf2389vw2c3k234vk2f3';
GRANT ALL ON measurements.* TO 'dbaccess_rw'@'%';
FLUSH PRIVILEGES;
quit;

# Relogin as dbaccess_rw user:
mysql -u'dbaccess_rw' -p'fasdjkf2389vw2c3k234vk2f3'

# some mysql command examples:
show databases;
use measurements;
show tables;
describe rawdata;

# Lets insert some test data to the table;
INSERT INTO rawdata (watts, sensor, temperature, location) VALUES ('1000', '1', '23', 'Oamk kotkantie 1');
INSERT INTO rawdata (watts, sensor, temperature, location) VALUES ('800', '2', '21', 'Oamk kotkantie 1');
INSERT INTO rawdata (watts, sensor, temperature, location) VALUES ('1100', '1', '22', 'Oamk kotkantie 1');

# Simple query:
select * from rawdata;

# Lets query all values from sensor 2:
select * from rawdata where sensor = '2';

# Only watts from sensor 1:
select watts from rawdata where sensor = 1;

# Counting average:
select avg(watts) from rawdata where sensor = 1;

# Changing values:
update rawdata SET watts = '1300' where id = '1';
select * from rawdata;
select avg(watts) from rawdata where sensor = 1;

# Clear table contents but don't delete the table:
truncate table rawdata;
select * from rawdata;
describe rawdata;
quit;

# Command line example (bash) data insert:
MYDATA="'1000', '1', '23', 'Oamk kotkantie 1'"
echo "INSERT INTO rawdata (watts, sensor, temperature, location) VALUES ($MYDATA);" | mysql -u dbaccess_rw -p"fasdjkf2389vw2c3k234vk2f3" measurements

# PHP select example for the data (create /var/www/html/measurement.php and try with browser):

<?php
$servername = "localhost";
$username = "dbaccess_rw";
$password = "fasdjkf2389vw2c3k234vk2f3";
$dbname = "measurements";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
        }

$sql = "SELECT timestamp, sensor, temperature, location FROM rawdata ORDER BY id DESC LIMIT 5";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      echo "Time: " . $row["timestamp"] . " - Sensor: " . $row["sensor"] . " - Temp: " . $row["temperature"] . " - Location: " . $row["location"]  ."<br>" ;
   }
}

$conn->close();
?>



#
# Bash example to fetch data from Finnish Meteorological Institute and store the data to the database
#

#!/bin/bash
fmidata=$(curl --connect-timeout 10 -s 'https://opendata.fmi.fi/wfs?request=getFeature&storedquery_id=fmi::observations::weather::timevaluepair&place=oulu&timestep=10&parameters=temperature' | grep -oP '(?<=wml2:value>)[^<]+' | tail -2 | head -1)
echo $fmidata
MYDATA="1000, 1, "$fmidata", 'Oulu vihreäsaari'"
echo "INSERT INTO rawdata (watts, sensor, temperature, location) VALUES ($MYDATA);" | mysql -u dbaccess_rw -p"fasdjkf2389vw2c3k234vk2f3" measurements


# Basic crontab example for 20 minute database updates. Edit /etc/crontab and add:
0,20,40 * * * *   root  /home/ubuntu/fmidata.bash