Have a Question?
Table of Contents
< All Topics
Print

004 Setting up MariaDB

Setting up the Database

Creating a database is not too bad, but it is involved, and there is a bunch to type. We are using the command line to do everything from Mission control.

Installing MariaDB

Installing is straight forward.  from a terminal prompt type:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get auto-remove
sudo apt install mariadb-server

 

Creating the database to track uptime.

  1. Terminal and type
    sudo mysql
  2. Once inside MySQL/MariaDB, we will need to make a database. A database is a container that contains tables. Tables contain rows, and rows contain the data.
    create database RoverData;
    Query OK, 1 row affected (0.00 sec)
    
  3. Let’s see if the database was created in MariaDB.
    show databases;
    +--+ | Database | +--+ | RoverData | | information_schema | | mysql | | performance_schema | +--------------------+ 4 rows in set (0.00 sec)

    This command will list all the databases it has loaded. You will see RoverData and three other databases: information_schema, MySQL, and performance_schema. We are not going to touch these other databases.

  4. After creating a database, we need to create a user for our PHP Web app. Even though there is nothing in this database to be concerned about, it’s a good idea not to use the superuser or root with our web app.
    CREATE USER 'webuser'@localhost' IDENTIFIED BY 'Password1!';
    GRANT SELECT, INSERT, UPDATE ON gmr.* TO 'webuser'@'localhost' ;;
    Query OK, 0 rows affected (0.00 sec)
  5.  Let’s check our work and see if there is a user created.
    SELECT User, Host, Password FROM mysql.user;
    
    
    +———+———–+——————————————-+
    | User | Host | Password |
    +———+———–+——————————————-+
    | root | localhost | |
    | webuser | localhost | *7EE969BBE0A3985C8BFF9FA65A06345C67FE434A |
    +———+———–+——————————————-+
    2 rows in set (0.00 sec)
  6. The database is made, the user is created now’s the time to get the table created. First, we need to use the correct database.
    use RoverData;
    
    
    Database changed
  7.  Now create the table.
    CREATE TABLE `Uptime` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `HostName` varchar(65) DEFAULT NULL,
    `IpAddress` varchar(45) DEFAULT NULL,
    `DateTime` datetime DEFAULT NULL,
    `Uptime` varchar(65) DEFAULT NULL,
    PRIMARY KEY (`id`),UNIQUE KEY `id_UNIQUE` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
    
    
    Query OK, 0 rows affected (0.09 sec)
     

We got a table now we are ready to try to put some stuff in it from PHP

There is a bit of code we need to write to accept input from the Rover. What we want to do is accept an HTTP request to Mission Control (Pi 3B+ in my case) that request will look like this:

http://172.16.42.99/RecordUptime.php?Hostname=RoverPi&IP=123.456.789&UpTime=100

Breaking down this request:

http://172.16.42.99/RecordUptime.php  This is the IP address of the server and the document name to open.
?Hostname=RoverPi&IP=123.456.789&UpTime=100 are parameters sent to the page. These parameters will be crafted from a python script on the rover.

When Mission Control gets a request for RecordUptime.php it takes in the parameters, writes them to the database and returns information that the data has been written. The reason we are doing it this way is we can keep all the database connection information local to mission control and to also minimize the amount of information sent from the Rover.

Here is the PHP script that will live in /var/www/html and called RecordUptime.php  Download link

<?php
  $Hostname=$_GET['Hostname'];
  $IP=$_GET['IP'];
  $UpTime=$_GET['UpTime'];
?>
<?php echo "Hostname: $Hostname" ?><br>
<?php echo "IP: $IP" ?><br>
<?php echo date(date('m/d/Y H:i:s')) ?><br>
<?php echo "Uptime: $UpTime" ?><br>
<?php
     $servername = "localhost";
     $username = "webuser";
     $password = "Password1";
     $dbname = "RoverData";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
     die("Connection failed: ".$conn->connect_error);
}
$sql = "INSERT INTO Uptime (HostName, IPAddress, DateTime, Uptime)
VALUES ('$Hostname', '$IP', NOW(), '$UpTime')";
if ($conn->query($sql) === TRUE) {
  echo"New record created successfully";
} else {
  echo"Error: ".$sql."<br>".$conn->error;
}
$conn->close();
?>

Now lest check the database to see if the data was written.

  1. Open terminal on your Pi
  2. Type:
    sudo mysql
  3. Type:
    use RoverData;
    
    
    Database changed
    Select * from Uptime;
    
    +----+----------+-------------+---------------------+--------+
    | id | HostName | IpAddress   | DateTime            | Uptime |
    +----+----------+-------------+---------------------+--------+
    | 1  | RoverPi  | 123.456.789 | 2018-03-25 13:02:10 | 100    |
    +----+----------+-------------+---------------------+--------+
    1 row in set (0.00 sec)
    
    

    We did it, we created a database and created a PHP program that record time based on parameters in the URL.  Now to get the RoverPi setup

    Check out the last post on how to setup Mission Control
    Check out the next post on how to setup the RoverPI