Robo-Tank v6.6 is Ready

[TheYear2525]

(02-23-2023, 02:58 AM)Rob F Wrote: [url=https://www.robo-tank.ca/forum/Thread-Robo-Tank-v6-6-is-Ready?pid=8338#pid8338][/url]If you have Chrome, right click on the page and click "inspect", a window will open in the browser. In that window there are tabs at the top, click the "console" tab. Then reload the page again, this time click and hold the refresh button and a drop down will appear. Then click "empty cache and hard reload". If it's still blank send me the info in the "console" you opened. That should tell me where it's getting stuck.

Sorry for taking so long.

This look like an issue xD .

[Rob F]

Right on, thank-you, that's definitely the problem but I'm not sure how. I've been doing some testing and it all seems good and would make sense nothing is wrong in the code or I would think I should experience it as well. When I change a field name in the database table I get the same error so maybe something is wrong with the table and the query is failing. 

Can you log into the database and see if the "schedules" table looks like this when you click the "structure" tab?

Here's how you access the database.

Add /phpmyadmin/ to your Pi IP address like below.

http://192.168.1.89/phpmyadmin/

Username = root
Password  = robotank

   

[Rob F]

I may have found the problem, in the error the link to call the php file is as follows.

http://192.168.179.34/php/schedulesSort.php?s=schName&a=1& =1678987612522

I didn't expect to see =1678987612522 at the end, that's the unix timestamp and it lines up close to when you posted. I don't send that to the php file so not sure why it's there, only the sort column (in this case schName) and ascending (1) or descending(0) sort is sent. But when I run that link in my browser the query runs so it doesn't seem to be affecting it. However that space before the = sign gets replaced with %20 as spaces aren't allowed. I'm wondering if that's what's breaking the php query as it likely won't auto replace the space. 

So I need to figure out why that's being included and why it's not for me, arggg. Can you run that link in your browser, I suspect you'll see [] as there are no records to return but that'll verify the database and query are ok.

[Rob F]

Turns out that timestamp is being sent because I have cache turned to off on the ajax call so it's not the problem. Here's something to try, this will update the schedulesSort.php file, I removed the variables from the query, maybe somehow it's stumbling over that. Run these 5 commands and try reloading the page.

sudo wget https://app.robo-tank.ca/public/updates/schedulesSort.zip

sudo unzip -o schedulesSort.zip

sudo cp schedulesSort.php /var/www/html/cpp/schedulesSort.php

sudo rm -r schedulesSort.php

sudo rm -r schedulesSort.zip

[gandalf]

if i Remember properly, schedulesort was one of the file i had to fix to make it working. i could check when i'm back from work

[Rob F]

if i Remember properly, schedulesort was one of the file i had to fix to make it working. i could check when i'm back from work

If you could check that would be awesome, I do have something different going on with the Order By clause for this query but can't remember why, I think some columns weren't sorting properly and this fixed it but not sure what the * 1 means now.

PHP Code:

ORDER BY `{$sort}` ASC, `{$sort}` * 1 ASC 


In my last post with the updated schedulesSort.php file I changed it to this just to test.

PHP Code:

ORDER BY schName ASC 


If this is the problem or there's a different problem in this file can you explain why it works for me and others yet some it doesn't?

This is the whole file to compare with yours.

PHP Code:

<?php

    header('Content-Type: application/json');
    header("Access-Control-Allow-Origin: *");
    
    $sort = $_GET["s"];  // get sort type
    $asc = $_GET["a"];  // get sort type

    require '../php/database.php';

    if ($asc == 1) {
        $query = sprintf("SELECT schedules.scheduleID, schedules.scheduleTypeID, schedules.pinID, schedules.offOn, schedules.schName, schedules.start, schedules.sun, schedules.mon, schedules.tue, schedules.wed, schedules.thu, schedules.fri, schedules.sat, schedules.repeatDays, schedules.enabled, schedules.schEmail, schedules.schAlert, scheduleType.type, pinsAvailable.name, pinsAvailable.iconID, dosingSchedules.mL
        FROM schedules
        LEFT JOIN scheduleType
        ON scheduleType.scheduleTypeID = schedules.scheduleTypeID
        LEFT JOIN pinsAvailable
        ON pinsAvailable.pinID = schedules.pinID
        LEFT JOIN dosingSchedules
        ON dosingSchedules.scheduleID = schedules.scheduleID
        ORDER BY `{$sort}` ASC, `{$sort}` * 1 ASC");
        // ORDER BY LENGTH(`{$sort}`), `{$sort}` ASC");
        // ORDER BY binray_not_needed_column ASC , `{$sort}` ASC");
    }
    else {
        $query = sprintf("SELECT schedules.scheduleID, schedules.scheduleTypeID, schedules.pinID, schedules.offOn, schedules.schName, schedules.start, schedules.sun, schedules.mon, schedules.tue, schedules.wed, schedules.thu, schedules.fri, schedules.sat, schedules.repeatDays, schedules.enabled, schedules.schEmail, schedules.schAlert, scheduleType.type, pinsAvailable.name, pinsAvailable.iconID, dosingSchedules.mL
        FROM schedules
        LEFT JOIN scheduleType
        ON scheduleType.scheduleTypeID = schedules.scheduleTypeID
        LEFT JOIN pinsAvailable
        ON pinsAvailable.pinID = schedules.pinID
        LEFT JOIN dosingSchedules
        ON dosingSchedules.scheduleID = schedules.scheduleID
        ORDER BY `{$sort}` DESC, `{$sort}` * 1 DESC");
        // ORDER BY LENGTH(`{$sort}`), `{$sort}` DESC");
        // ORDER BY binray_not_needed_column DESC , `{$sort}` DESC");
    } 
    //execute query
    $result = $mysqli->query($query);

    //loop through the returned data
    $dataWindow = array();
    foreach ($result as $row) {
        $dataWindow[] = $row;
    }

    print json_encode($dataWindow);
    //free memory associated with result
    $result->close();

    //close connection
    $mysqli->close(); 


[gandalf]

meanwhile look at the php/server log on the server to see which error is triggering

[Rob F]

Is this the log you refer to?

/var/log/apache2/error.log

Problem is I don't have any error related to this, scheduleSorts.php runs fine for me and others. Not long ago, after v6.6, someone pointed out a bunch of errors in this log, I think I've got those fixed now at least my log is showing clean. Those changes will be in v6.7.

[gandalf]

It should be that. if you have an error 500 loading the php page, you should have an entry in the log

probably you have to enable the log_errors in php ini

[Rob F]

Yeah but I don't get any 500 errors, I did see a bunch of errors for variables defined but not used as you mentioned but that's unrelated.

I don't know for sure but it seems for 75% it works fine 25% can't load the page and probably for the same reason, that's why it doesn't make sense as we all use the same code, php version etc. I get it if there's a problem with the code but I don't get why this code works for some and not others.

[gandalf]

I was referring to the screenshot posted by TheYear2525

What i know for sure is that my install didn't started and I had to customize some php file fixing the variable issues (mostly variables used as constants)
Also the mysql instance had an issue but i don't remember what i've changed.

---

Just an example:

checkStartupWizard.php

You have this:

Code:

$query = sprintf("SELECT `userID`, `username`, `firstRun`, `adminEmail`, `email1`, `email2`, `email2Enabled`, `email3`, `email3Enabled` FROM `users` ORDER BY ".userID." ASC");

this breaks for sure, as the "userID" is considered a constant, not a database field thus the resulting query would be :

Code:

SELECT `userID`, `username`, `firstRun`, `adminEmail`, `email1`, `email2`, `email2Enabled`, `email3`, `email3Enabled` FROM `users` ORDER BY ASC

that is not valid (missing field after the ORDER BY)
This breaks for sure.

Same error in many other files, i had to fix all of these to make it working

something similiar in getLightModesData.php , lightPorts.php

[Rob F]

Thanks, funny you mention these files as I actually just fixed them last week, when someone told me to check the php log I saw errors from these files and more so was able to fix them. As I'm going I'm learning, php I don't know much about so that's why I had those mistakes. I'm going to go though all the php files to make sure I didn't miss any.

Now can you answer this?

Why did those coding issues break your system and some others, yet me and a majority they had no effect other than lots of log errors? I promise you I can install v6.6 on a fresh SD card all day long and it works and I know one other person for sure was able to upgrade without issue.

Can you recommend any other error logs I should be paying attention to?

[gandalf]

as starting point you should set display_errors to E_ALL and see what happens.

Aldo, check the php version you have.
with the reported issues it's impossible to have a working system because the query will be broken

[Rob F]

That's I'll check that out.

This is the version I have. Does it match up with yours?

PHP 7.3.31-1~deb10u1 (cli) (built: Oct 24 2021 15:18:08) ( NTS )

100% those queries were working for a lot of people including myself as some of them have been there basically from day 1. It's obvious just from forum posts, guaranteed people using it didn't make all the changes you did. I'm really hoping I can find out why this is the case, I'm hoping you have a different PHP version but if you used my install script then I can't see how yours would be different.

What do you think of this updated php code vs what I'm currently doing? 

Switched from mysqli to PDO to access the database, using prepared statement so it's safer, try catch block and error handling.

This file is the new dcGetPorts.php

PHP Code:

<?php
    header('Content-Type: application/json');
    header("Access-Control-Allow-Origin: *");

    try {
        require '../php/database.php';

        $query = "SELECT `dcSortID` FROM `systemSettings` WHERE `userID` = 1";
        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $sortID = $row['dcSortID'];

        if ($sortID == 1) {
            $s = "name";
        } else if ($sortID == 2) {
            $s = "iconID";
        } else if ($sortID == 3) {
            $s = "gpio";
        }

        $query = "SELECT `pinID`, `gpio`, `assigned`, `name`, `iconID`, `invert`, `manual` FROM `pinsAvailable` WHERE `pinTypeID` = 2 AND `assigned` = 1 ORDER BY `$s` ASC";
        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $dataWindow = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo json_encode($dataWindow);
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }

    $pdo = null; 


[matt]

Is this the log you refer to?

/var/log/apache2/error.log

Problem is I don't have any error related to this, scheduleSorts.php runs fine for me and others. Not long ago, after v6.6, someone pointed out a bunch of errors in this log, I think I've got those fixed now at least my log is showing clean. Those changes will be in v6.7.

My graphs were also randomly disappearing before you patched the files for me.  Usually if I'd left the page open for a while.

[gandalf]

That's I'll check that out.

This is the version I have. Does it match up with yours?

PHP 7.3.31-1~deb10u1 (cli) (built: Oct 24 2021 15:18:08) ( NTS )

100% those queries were working for a lot of people including myself as some of them have been there basically from day 1. It's obvious just from forum posts, guaranteed people using it didn't make all the changes you did. I'm really hoping I can find out why this is the case, I'm hoping you have a different PHP version but if you used my install script then I can't see how yours would be different.

What do you think of this updated php code vs what I'm currently doing? 

Switched from mysqli to PDO to access the database, using prepared statement so it's safer, try catch block and error handling.

This file is the new dcGetPorts.php

PHP Code:

<?php
    header('Content-Type: application/json');
    header("Access-Control-Allow-Origin: *");

    try {
        require '../php/database.php';

        $query = "SELECT `dcSortID` FROM `systemSettings` WHERE `userID` = 1";
        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        $sortID = $row['dcSortID'];

        if ($sortID == 1) {
            $s = "name";
        } else if ($sortID == 2) {
            $s = "iconID";
        } else if ($sortID == 3) {
            $s = "gpio";
        }

        $query = "SELECT `pinID`, `gpio`, `assigned`, `name`, `iconID`, `invert`, `manual` FROM `pinsAvailable` WHERE `pinTypeID` = 2 AND `assigned` = 1 ORDER BY `$s` ASC";
        $stmt = $pdo->prepare($query);
        $stmt->execute();
        $dataWindow = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo json_encode($dataWindow);
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }

    $pdo = null; 


Mine is different:

Code:

$ php -v
PHP 7.4.33 (cli) (built: Nov  8 2022 11:40:37) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
    with Zend OPcache v7.4.33, Copyright (c), by Zend Technologies

Which Debian do you have in your Pi ? Probaby not the 11:

Code:

$ cat /etc/debian_version
11.6

the code above looks good to me

---

It should work anyway, but triggering a huge warning:

Code:

$ php -r "echo 'HELLO '.World.'';"
PHP Warning:  Use of undefined constant World - assumed 'World' (this will throw an Error in a future version of PHP) in Command line code on line 1
HELLO World

in php8 this this is fixed and the undefined constant is not considered a string anymore.
Probably on some environment this didn't worked because the error is outputting some unxepceted data (the error message) that break the AJAX call if you expect a json or something similar.

[Rob F]

My graphs were also randomly disappearing before you patched the files for me.  Usually if I'd left the page open for a while.

Thanks for letting me know, if it happens again let me know. Now I have the graphs loading from the database instead of CSV files so that might help, this will be coming in v6.7.

[Rob F]

Gandalf thanks for checking, my debian version is 10.12. This and my php version is from my main system which has been running a long time so might explain why the versions are so old, I'll have to see what versions I get on a fresh install. A different php version could be the reason it works for some and not others but yeah either way those queries weren't well written so my fault. I updated all the php files to PDO etc so hopefully that helps as well. When I ran php -r "echo 'HELLO '.World.'';" I got the same as you.

[Enigma79]

Hey Rob, I tried to go through a fresh install since my old SD card died. Followed the instructions, got message that the installation completed successfully. When I tried to access the site I kept getting error, see screen shot. Tried the steps twice with the same results.

[Rob F]

Hi Enigma79, sorry about that, someone had this problem the other day and found the fix, try running the following two commands in the SSH terminal. 

sudo usermod -a -G www-data username

sudo chown -R -f www-data:www-data /var/www/html

After this you should get the login screen but you likely won't be able to log in. If so you'll have to add the database manually, here's how you do that.

Add /phpmyadmin/ to your Pi IP address like below and log in.
 
http://192.168.1.145/phpmyadmin/
 
Username = root
Password  = robotank

At the top there should be a bunch of tabs, click on the 2nd tab "SQL" and you should see a large text box. Copy and paste the following in the text box and click the "Go" button on the right side. After this you should be able to log in.

Code:

-- phpMyAdmin SQL Dump
-- version 4.6.6deb5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Feb 13, 2023 at 11:13 PM
-- Server version: 10.3.34-MariaDB-0+deb10u1
-- PHP Version: 7.3.31-1~deb10u1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `robotank`
--

CREATE DATABASE robotank;
USE robotank;

-- --------------------------------------------------------

--
-- Table structure for table `button_labels`
--

CREATE TABLE `button_labels` (
  `buttonID` int(11) NOT NULL,
  `label` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `button_labels`
--

INSERT INTO `button_labels` (`buttonID`, `label`) VALUES
(1, 'Configure Ports'),
(2, 'Dosing Pumps'),
(3, 'Email'),
(4, 'Calibrate Probes'),
(5, 'System'),
(6, 'Customize'),
(7, 'Hardware'),
(8, 'Schedules'),
(9, 'System'),
(10, 'Set Clock'),
(11, 'Equipment Connected'),
(12, 'Temperature Sensors'),
(13, 'Analog Sensors'),
(14, 'DC Accessories'),
(15, 'Dosing Pumps'),
(16, 'Flow Meter'),
(17, 'Automatic Top Off'),
(18, 'Sensor Probes'),
(19, 'Energy Power Bar');

-- --------------------------------------------------------

--
-- Table structure for table `dosingPumps`
--

CREATE TABLE `dosingPumps` (
  `doseID` int(11) NOT NULL,
  `dosePinID` int(11) NOT NULL COMMENT 'connected to pinID in pinsAvailable',
  `doseStirrerID` int(11) NOT NULL,
  `doseName` varchar(55) NOT NULL DEFAULT 'Add Custom Name',
  `doseAmount` int(11) NOT NULL DEFAULT 10 COMMENT 'milliliters',
  `reservoirRemain` float NOT NULL DEFAULT 250 COMMENT 'milliliters',
  `reservoirSize` int(11) NOT NULL DEFAULT 250 COMMENT 'milliliters',
  `dosePumpRate` int(11) NOT NULL DEFAULT 600 COMMENT 'ms/ml',
  `doseStirrer` int(11) NOT NULL DEFAULT 0,
  `stirrerRuntime` mediumint(9) NOT NULL DEFAULT 10 COMMENT 'seconds',
  `settlingRuntime` mediumint(9) NOT NULL DEFAULT 5 COMMENT 'seconds',
  `stirrerSpeed` smallint(6) NOT NULL DEFAULT 4095 COMMENT 'percent',
  `calibrateAmount` int(11) NOT NULL DEFAULT 5 COMMENT 'milliliters',
  `reservoirColor` varchar(12) NOT NULL DEFAULT '#FF0000',
  `doseInterval` int(11) NOT NULL DEFAULT 1 COMMENT 'min delay between doses - minutes',
  `disableRes` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Reservoir Disabled ',
  `pumpSpeed` smallint(6) NOT NULL DEFAULT 4095 COMMENT 'PWM value',
  `resAlert` tinyint(1) NOT NULL DEFAULT 1,
  `resEmail` tinyint(1) NOT NULL DEFAULT 0,
  `resAlertML` mediumint(9) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `dosingSchedules`
--

CREATE TABLE `dosingSchedules` (
  `doseSchID` int(11) NOT NULL,
  `scheduleID` int(11) NOT NULL,
  `mL` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `drivers`
--

CREATE TABLE `drivers` (
  `driverID` int(11) NOT NULL,
  `driverName` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `drivers`
--

INSERT INTO `drivers` (`driverID`, `driverName`) VALUES
(1, 'Dummy'),
(2, 'Raspberry Pi'),
(3, 'PCA9685');

-- --------------------------------------------------------

--
-- Table structure for table `ds18b20`
--

CREATE TABLE `ds18b20` (
  `ds18b20ID` int(11) NOT NULL,
  `id` int(11) NOT NULL DEFAULT 0,
  `address` varchar(30) NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `plugged` tinyint(1) NOT NULL DEFAULT 0,
  `graphVisible` tinyint(1) NOT NULL DEFAULT 1,
  `graphRange` tinyint(4) NOT NULL DEFAULT 0,
  `graphAverage` smallint(6) NOT NULL DEFAULT 10,
  `min` float DEFAULT 0,
  `max` float DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `errors`
--

CREATE TABLE `errors` (
  `errorID` int(11) NOT NULL,
  `errorSourceID` int(11) NOT NULL,
  `sensor` varchar(50) NOT NULL,
  `message` varchar(200) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `errorsSource`
--

CREATE TABLE `errorsSource` (
  `errorSourceID` int(11) NOT NULL,
  `source` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `errorsSource`
--

INSERT INTO `errorsSource` (`errorSourceID`, `source`) VALUES
(1, 'DS18B20 Sensors'),
(2, 'AC Outlet'),
(3, 'PCA9685'),
(4, 'GPIO');

-- --------------------------------------------------------

--
-- Table structure for table `i2cDevices`
--

CREATE TABLE `i2cDevices` (
  `i2cID` int(11) NOT NULL,
  `i2cDeviceTypeID` int(11) NOT NULL DEFAULT 0,
  `i2cDriverID` int(11) DEFAULT 1,
  `decAddress` int(11) NOT NULL,
  `enabled` tinyint(1) NOT NULL DEFAULT 1,
  `new` tinyint(1) NOT NULL DEFAULT 1,
  `running` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `i2cDevices`
--

INSERT INTO `i2cDevices` (`i2cID`, `i2cDeviceTypeID`, `i2cDriverID`, `decAddress`, `enabled`, `new`, `running`) VALUES
(1, 0, 1, 112, 0, 0, 0),
(2, 2, 3, 64, 1, 0, 1),
(3, 0, 1, 104, 0, 0, 0),
(4, 0, 1, 87, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `i2cDeviceTypes`
--

CREATE TABLE `i2cDeviceTypes` (
  `i2cDeviceTypeID` int(11) NOT NULL,
  `type` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `i2cDeviceTypes`
--

INSERT INTO `i2cDeviceTypes` (`i2cDeviceTypeID`, `type`) VALUES
(0, 'not used'),
(1, 'pH'),
(2, 'PCA9685'),
(3, 'Conductivity'),
(4, 'ORP'),
(5, 'Dissolved Oxygen');

-- --------------------------------------------------------

--
-- Table structure for table `lightModes`
--

CREATE TABLE `lightModes` (
  `modeID` int(11) NOT NULL,
  `pinID` int(11) NOT NULL,
  `start` int(11) NOT NULL DEFAULT 86399,
  `startLevel` int(11) NOT NULL DEFAULT 0,
  `enabled` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

-- --------------------------------------------------------

--
-- Table structure for table `log`
--

CREATE TABLE `log` (
  `logID` int(11) NOT NULL,
  `logSourceID` int(11) NOT NULL,
  `logTypeID` int(11) NOT NULL,
  `logPinID` int(11) NOT NULL,
  `logActionID` int(11) NOT NULL,
  `logTime` bigint(20) DEFAULT NULL,
  `note` varchar(100) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `logAction`
--

CREATE TABLE `logAction` (
  `logActionID` int(11) NOT NULL,
  `logAction` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logAction`
--

INSERT INTO `logAction` (`logActionID`, `logAction`) VALUES
(0, 'Turn Off'),
(1, 'Turn On'),
(2, 'Rebooted Controller');

-- --------------------------------------------------------

--
-- Table structure for table `logSource`
--

CREATE TABLE `logSource` (
  `logSourceID` int(11) NOT NULL,
  `logSource` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logSource`
--

INSERT INTO `logSource` (`logSourceID`, `logSource`) VALUES
(1, 'Manual'),
(2, 'Light Mode'),
(3, 'Schedule'),
(4, 'Custom Rule'),
(5, 'Entered Manual Mode'),
(6, 'Resume Auto Mode'),
(7, 'Smart Startup'),
(8, 'Changed Controller Version'),
(9, 'Enabled DS18B20 Port'),
(10, 'Disable DS18B20 Port');

-- --------------------------------------------------------

--
-- Table structure for table `logType`
--

CREATE TABLE `logType` (
  `logTypeID` int(11) NOT NULL,
  `logType` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `logType`
--

INSERT INTO `logType` (`logTypeID`, `logType`) VALUES
(1, 'AC Outlet'),
(2, 'DC Port'),
(3, 'Dosing Pump'),
(4, 'Auto Feeder'),
(5, 'Light'),
(6, 'Sensor Port');

-- --------------------------------------------------------

--
-- Table structure for table `pinsAvailable`
--

CREATE TABLE `pinsAvailable` (
  `pinID` int(11) NOT NULL,
  `driverID` int(11) NOT NULL,
  `gpio` int(11) NOT NULL,
  `assigned` tinyint(1) NOT NULL DEFAULT 0,
  `pinTypeID` int(11) DEFAULT NULL,
  `name` varchar(70) DEFAULT NULL,
  `portName` varchar(70) DEFAULT NULL COMMENT 'DO NOT CHANGE',
  `iconID` int(11) DEFAULT NULL,
  `invert` tinyint(1) NOT NULL DEFAULT 0,
  `manual` tinyint(1) NOT NULL DEFAULT 0,
  `manualStatus` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `pinsAvailable`
--

INSERT INTO `pinsAvailable` (`pinID`, `driverID`, `gpio`, `assigned`, `pinTypeID`, `name`, `portName`, `iconID`, `invert`, `manual`, `manualStatus`) VALUES
(1, 2, 9, 0, 1, 'Power Bar 2 - Outlet 3', 'Power Bar 2 - Outlet 3', 0, 0, 0, 0),
(2, 2, 11, 0, 1, 'Power Bar 2 - Outlet 2', 'Power Bar 2 - Outlet 2', 0, 0, 0, 0),
(3, 2, 10, 0, 1, 'Power Bar 2 - Outlet 4', 'Power Bar 2 - Outlet 4', 0, 0, 0, 0),
(4, 2, 25, 0, 1, 'Power Bar 2 - Outlet 6', 'Power Bar 2 - Outlet 6', 0, 0, 0, 0),
(5, 2, 21, 0, 1, 'Power Bar 1 - Outlet 1', 'Power Bar 1 - Outlet 1', 0, 0, 0, 0),
(6, 2, 20, 0, 1, 'Power Bar 1 - Outlet 2', 'Power Bar 1 - Outlet 2', 0, 0, 0, 0),
(7, 2, 24, 0, 1, 'Power Bar 2 - Outlet 5', 'Power Bar 2 - Outlet 5', 0, 0, 0, 0),
(8, 2, 8, 0, 1, 'Power Bar 2 - Outlet 7', 'Power Bar 2 - Outlet 7', 0, 0, 0, 0),
(9, 2, 7, 0, 1, 'Power Bar 2 - Outlet 8', 'Power Bar 2 - Outlet 8', 0, 0, 0, 0),
(10, 2, 12, 0, 1, 'Power Bar 1 - Outlet 5', 'Power Bar 1 - Outlet 5', 0, 0, 0, 0),
(11, 2, 6, 0, 1, 'Power Bar 1 - Outlet 4', 'Power Bar 1 - Outlet 4', 0, 0, 0, 0),
(12, 2, 5, 0, 1, 'Power Bar 2 - Outlet 1', 'Power Bar 2 - Outlet 1', 0, 0, 0, 0),
(13, 2, 14, 0, 8, 'Sensor Port 1', 'Sensor Port 1', 0, 0, 0, 0),
(14, 2, 22, 0, 5, 'DS18B20', 'DS18B20', 0, 0, 0, 0),
(15, 2, 17, 0, 8, 'Sensor Port 3', 'Sensor Port 3', 0, 0, 0, 0),
(16, 2, 18, 0, 8, 'Sensor Port 4', 'Sensor Port 4', 0, 0, 0, 0),
(17, 2, 23, 0, 8, 'Sensor Port 6', 'Sensor Port 6', 0, 0, 0, 0),
(18, 2, 16, 0, 1, 'Power Bar 1 - Outlet 3', 'Power Bar 1 - Outlet 3', 0, 0, 0, 0),
(19, 2, 13, 0, 1, 'Power Bar 1 - Outlet 6', 'Power Bar 1 - Outlet 6', 0, 0, 0, 0),
(20, 2, 19, 0, 1, 'Power Bar 1 - Outlet 7', 'Power Bar 1 - Outlet 7', 0, 0, 0, 0),
(21, 2, 26, 0, 1, 'Power Bar 1 - Outlet 8', 'Power Bar 1 - Outlet 8', 0, 0, 0, 0),
(22, 2, 27, 0, 8, 'Sensor Port 5', 'Sensor Port 5', 0, 0, 0, 0),
(23, 2, 15, 0, 8, 'Sensor Port 2', 'Sensor Port 2', 0, 0, 0, 0),
(24, 3, 9, 0, 11, 'PWM / Analog Port 1', 'PWM / Analog Port 1', 0, 0, 0, 0),
(25, 3, 8, 0, 11, 'PWM / Analog Port 2', 'PWM / Analog Port 2', 0, 0, 0, 0),
(26, 3, 10, 0, 11, 'PWM / Analog Port 3', 'PWM / Analog Port 3', 0, 0, 0, 0),
(27, 3, 11, 0, 11, 'PWM / Analog Port 4', 'PWM / Analog Port 4', 0, 0, 0, 0),
(28, 3, 12, 0, 11, 'PWM / Analog Port 5', 'PWM / Analog Port 5', 0, 0, 0, 0),
(29, 3, 13, 0, 11, 'PWM / Analog Port 6', 'PWM / Analog Port 6', 0, 0, 0, 0),
(30, 3, 14, 0, 11, 'PWM / Analog Port 7', 'PWM / Analog Port 7', 0, 0, 0, 0),
(31, 3, 15, 0, 11, 'PWM / Analog Port 8', 'PWM / Analog Port 8', 0, 0, 0, 0),
(32, 3, 0, 0, 13, 'Feeder Port', 'Feeder Port', 0, 0, 0, 0),
(33, 3, 5, 0, 2, 'DC Port 7', 'Internal Fan', 0, 0, 0, 0),
(34, 3, 6, 0, 2, 'DC Port 6', 'DC Port 6', 0, 0, 0, 0),
(35, 3, 7, 0, 2, 'DC Port 5', 'DC Port 5', 0, 0, 0, 0),
(36, 3, 4, 0, 2, 'DC Port 4', 'DC Port 4', 0, 0, 0, 0),
(37, 3, 3, 0, 2, 'DC Port 3', 'DC Port 3', 0, 0, 0, 0),
(38, 3, 2, 0, 2, 'DC Port 2', 'DC Port 2', 0, 0, 0, 0),
(39, 3, 1, 0, 2, 'DC Port 1', 'DC Port 1', 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `pinsType`
--

CREATE TABLE `pinsType` (
  `pinTypeID` int(11) NOT NULL,
  `pinTypeName` varchar(75) NOT NULL,
  `pinType` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT;

--
-- Dumping data for table `pinsType`
--

INSERT INTO `pinsType` (`pinTypeID`, `pinTypeName`, `pinType`) VALUES
(1, 'AC Outlet', 0),
(2, 'DC Port', 0),
(3, 'pH Probe', 1),
(4, 'DHT22', 1),
(5, 'DS18B20', 1),
(6, 'Non-Contact Sensor', 1),
(7, 'Optical Sensor', 1),
(8, 'Float Switch', 1),
(9, 'Other Sensor', 1),
(10, 'Flow Meter', 1),
(11, 'Light', 0),
(12, 'Dosing Pump', 0),
(13, 'Auto Feeder', 0),
(14, 'Toggle Switch', 0),
(15, 'Dosing Pump Stirrer', 0);

-- --------------------------------------------------------

--
-- Table structure for table `probes`
--

CREATE TABLE `probes` (
  `probeID` int(11) NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `address` int(11) NOT NULL,
  `plugged` tinyint(4) NOT NULL,
  `graphVisible` tinyint(4) NOT NULL DEFAULT 1,
  `graphRange` tinyint(4) NOT NULL DEFAULT 2,
  `graphAverage` smallint(6) NOT NULL DEFAULT 10,
  `min` float NOT NULL DEFAULT 0,
  `max` float NOT NULL DEFAULT 0,
  `buffer1` double NOT NULL DEFAULT 7,
  `buffer2` double NOT NULL DEFAULT 10,
  `cal1` double NOT NULL DEFAULT 7,
  `cal2` double NOT NULL DEFAULT 10,
  `calType` tinyint(4) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rules`
--

CREATE TABLE `rules` (
  `ruleID` int(11) NOT NULL,
  `ruleTypeID` int(11) NOT NULL,
  `ruleName` varchar(255) DEFAULT NULL,
  `email` tinyint(1) DEFAULT 0,
  `alert` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Dashboard Alerts',
  `enabled` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesEquipment`
--

CREATE TABLE `rulesEquipment` (
  `rulesEquipmentID` int(11) NOT NULL,
  `ruleID` int(11) NOT NULL,
  `equipmentPinID` int(11) NOT NULL COMMENT 'pinID from pinsAvailable',
  `equipmentOffOn` tinyint(1) NOT NULL,
  `switchDelay` int(11) NOT NULL DEFAULT 0 COMMENT 'delay until device is switched',
  `nextDoseDelay` int(11) NOT NULL DEFAULT 0 COMMENT 'For Dosing Pumps - minutes',
  `equipIgnore` tinyint(4) NOT NULL DEFAULT 0 COMMENT 'ignore equipment if schedule is running',
  `doseML` float NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesSensors`
--

CREATE TABLE `rulesSensors` (
  `rulesSensorID` int(11) NOT NULL,
  `ruleID` int(11) NOT NULL,
  `sensorPinID` int(11) DEFAULT NULL COMMENT 'pinID from pinsAvailable',
  `sensorAddress` varchar(18) DEFAULT NULL,
  `sensorDataType` smallint(6) NOT NULL DEFAULT 0,
  `sensorValue` double NOT NULL,
  `lessGreater` smallint(1) NOT NULL,
  `stabilitySeconds` int(11) NOT NULL DEFAULT 1,
  `logic` smallint(6) NOT NULL DEFAULT 0 COMMENT 'None, AND, OR'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `rulesType`
--

CREATE TABLE `rulesType` (
  `ruleTypeID` int(11) NOT NULL,
  `ruleType` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `rulesType`
--

INSERT INTO `rulesType` (`ruleTypeID`, `ruleType`) VALUES
(1, 'Schedule'),
(2, 'Sensor'),
(3, 'Manual');

-- --------------------------------------------------------

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `scheduleID` int(11) NOT NULL,
  `scheduleTypeID` int(11) NOT NULL,
  `ruleID` int(11) DEFAULT NULL,
  `pinID` int(11) DEFAULT NULL,
  `offOn` tinyint(1) NOT NULL,
  `schName` varchar(120) NOT NULL,
  `start` int(11) NOT NULL,
  `sun` tinyint(1) NOT NULL DEFAULT 1,
  `mon` tinyint(1) NOT NULL DEFAULT 1,
  `tue` tinyint(1) NOT NULL DEFAULT 1,
  `wed` tinyint(1) NOT NULL DEFAULT 1,
  `thu` tinyint(1) NOT NULL DEFAULT 1,
  `fri` tinyint(1) NOT NULL DEFAULT 1,
  `sat` tinyint(1) NOT NULL DEFAULT 1,
  `repeatDays` smallint(6) NOT NULL DEFAULT 0,
  `disableMaint` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'disabled for maintenance',
  `enabled` int(11) NOT NULL DEFAULT 1,
  `schEmail` tinyint(4) NOT NULL DEFAULT 0,
  `schAlert` tinyint(4) NOT NULL DEFAULT 0,
  `dateCreated` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --------------------------------------------------------

--
-- Table structure for table `scheduleType`
--

CREATE TABLE `scheduleType` (
  `scheduleTypeID` int(11) NOT NULL,
  `type` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `scheduleType`
--

INSERT INTO `scheduleType` (`scheduleTypeID`, `type`) VALUES
(1, 'AC Outlet'),
(2, 'DC Port'),
(3, 'Dosing'),
(4, 'Feeder'),
(5, 'Custom Rule'),
(6, 'Custom Mode');

-- --------------------------------------------------------

--
-- Table structure for table `stirrers`
--

CREATE TABLE `stirrers` (
  `stirrerID` int(11) NOT NULL,
  `stirrerPinID` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- --------------------------------------------------------

--
-- Table structure for table `systemSettings`
--

CREATE TABLE `systemSettings` (
  `systemSettingID` int(11) NOT NULL,
  `userID` int(11) NOT NULL,
  `configView` tinyint(4) NOT NULL DEFAULT 1 COMMENT 'Configure Ports View',
  `tempCorF` tinyint(1) NOT NULL DEFAULT 0,
  `timeFormat` tinyint(4) NOT NULL DEFAULT 1,
  `outletSortID` tinyint(4) NOT NULL DEFAULT 1,
  `dcSortID` tinyint(4) NOT NULL DEFAULT 1,
  `scheduleTabSelected` tinyint(4) NOT NULL DEFAULT 0,
  `scheduleSortID` smallint(6) NOT NULL DEFAULT 0,
  `scheduleSortOrder` tinyint(1) NOT NULL DEFAULT 0,
  `emailEnabled` tinyint(4) NOT NULL DEFAULT 0,
  `smtpServer` varchar(60) DEFAULT NULL,
  `smtpPort` smallint(6) DEFAULT NULL,
  `smtpUser` varchar(60) DEFAULT NULL,
  `smtpPass` varchar(75) DEFAULT NULL,
  `smtpProvider` smallint(6) NOT NULL DEFAULT 1,
  `autoUpdate` tinyint(4) NOT NULL DEFAULT 1,
  `controllerVersion` tinyint(4) NOT NULL DEFAULT 0,
  `version` float NOT NULL,
  `versionLatest` float NOT NULL,
  `updateRan` tinyint(4) NOT NULL DEFAULT 0,
  `supportID` int(11) DEFAULT 0,
  `db9_1` tinyint(4) NOT NULL DEFAULT 0,
  `db9_2` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `systemSettings`
--

INSERT INTO `systemSettings` (`systemSettingID`, `userID`, `configView`, `tempCorF`, `timeFormat`, `outletSortID`, `dcSortID`, `scheduleTabSelected`, `scheduleSortID`, `scheduleSortOrder`, `emailEnabled`, `smtpServer`, `smtpPort`, `smtpUser`, `smtpPass`, `smtpProvider`, `autoUpdate`, `controllerVersion`, `version`, `versionLatest`, `updateRan`, `supportID`, `db9_1`, `db9_2`) VALUES
(1, 1, 0, 1, 1, 1, 1, 0, 2, 1, 0, 'smtp.office365.com', 587, '', '', 0, 0, 2, 6.6, 6.6, 0, 0, 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userID` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `adminEmail` varchar(80) DEFAULT NULL,
  `email1` varchar(80) DEFAULT NULL,
  `email2` varchar(80) DEFAULT 'NULL',
  `email2Enabled` tinyint(1) NOT NULL DEFAULT 0,
  `email3` varchar(80) DEFAULT 'NULL',
  `email3Enabled` tinyint(1) NOT NULL DEFAULT 0,
  `firstRun` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`userID`, `username`, `password`, `adminEmail`, `email1`, `email2`, `email2Enabled`, `email3`, `email3Enabled`, `firstRun`) VALUES
(1, 'Robo-Tank', '$2y$10$prs2O2elGMJHCEIAgOCs.uvMJ9UObZc/HHtw/TJJ0/q59zbZOGy2C', '', '', '', 0, '', 0, 0);

-- --------------------------------------------------------

--
-- Table structure for table `version`
--

CREATE TABLE `version` (
  `versionID` int(11) NOT NULL,
  `version` float NOT NULL,
  `versionDate` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `version`
--

INSERT INTO `version` (`versionID`, `version`, `versionDate`) VALUES
(1, 6.1, '2021-10-14'),
(2, 6.2, '2021-12-18'),
(3, 6.3, '2021-12-25'),
(4, 6.4, '2022-03-17'),
(5, 6.5, '2022-04-09'),
(6, 6.6, '2023-02-12');

-- --------------------------------------------------------

--
-- Table structure for table `versionInfo`
--

CREATE TABLE `versionInfo` (
  `versionInfoID` int(11) NOT NULL,
  `versionID` int(11) NOT NULL,
  `versionTypeID` int(11) NOT NULL,
  `change` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `versionInfo`
--

INSERT INTO `versionInfo` (`versionInfoID`, `versionID`, `versionTypeID`, `change`) VALUES
(1, 1, 2, 'Configure Ports - Added Robo-Tank extensions to DB9 accessory types'),
(2, 1, 2, 'Configure Ports - Added all pin functions to advanced tab'),
(3, 1, 3, 'Program - Sensors were being checked when disabled'),
(4, 1, 1, 'Added a toggle switch as a sensor type'),
(5, 1, 2, 'Dashboard - Added sensor status to info boxes'),
(6, 1, 3, 'Custom Rules - Off rules were not saved correctly'),
(8, 2, 3, 'Custom Rules - Added ability to disable/enable dosing pumps'),
(9, 2, 3, 'pH Calibration - Couldn\'t adjust 2nd point value'),
(10, 2, 2, 'Light Modes - Change how modes are created and work'),
(11, 2, 1, 'Added Auto Logout'),
(12, 3, 3, 'Could not access Advanced view on Configure Ports Page'),
(13, 3, 3, 'Could not disable schedules'),
(14, 4, 3, 'DC Ports - When put in manual mode the red dot on icon would disappear after a page refresh although it still remained in manual mode'),
(15, 4, 2, 'System - removed wiringPi library to control GPIOs, now reading/writing GPIOs using sysfs'),
(16, 4, 2, 'Dashboard - changed bottom dock to snap to bottom of browser'),
(17, 4, 1, 'System - Added v2 and v3 controller compatibility'),
(18, 4, 1, 'AC Outlets - Added ability to Invert output logic'),
(19, 4, 1, 'DC Ports - Added ability to Invert output logic'),
(20, 4, 3, 'Setup Wizard - Fixed form to configure new pca9685'),
(21, 4, 3, 'PCA9685 - Fixed bug that wouldn\'t allowing more than 2 PCA9685 modules'),
(22, 4, 1, 'RTC - Added support for DS1307 and DS3231 real time clocks'),
(23, 4, 2, 'Interface - Date and time on webpage now gets updated from backend instead of using computer date and time'),
(24, 4, 2, 'Startup Wizard - Fixed DS18B20 scan and added controller version option'),
(25, 5, 1, 'PCA9685 - Added verification, when pca9685 is updated it is immediately read to verify it updated correctly, if not it tries again'),
(26, 5, 1, 'GPIO - Added verification, when GPIO is updated it is immediately read to verify it updated correctly, if not it tries again'),
(27, 5, 3, 'Browser DateTime - Clock would not show 24 hour format after adding RTC'),
(28, 5, 1, 'System Logs - Added logging system to record all actions'),
(29, 5, 3, 'Schedules - Date added was saved wrong in database after RTC was added'),
(30, 5, 3, 'I2C Scanner - Removed auto scanner as it is not required'),
(31, 5, 3, 'Charts - pH chart table in database wasn\'t being updated'),
(32, 5, 2, 'Charts - charts were still getting timestamp from internet time, changed to controller system time'),
(33, 6, 2, 'Schedules - Updated schedules table for user interface'),
(34, 6, 3, 'Schedules - If schedule was disabled when schedule list was filtered it would not appear disabled in list, only visual error'),
(35, 6, 2, 'Schedules - List of schedules is now updated after confirmation is received from backend, previously it waited 250ms before reloading table'),
(36, 6, 2, 'Schedules - Removed Off option for dosing pumps when creating a schedule'),
(37, 6, 3, 'Schedules - If schedule was created using Repeat Every 1 Day setting the first days schedule would not run'),
(38, 6, 3, 'Schedules - It was possible to add a new schedule without any days selected'),
(39, 6, 1, 'Schedules - Added validation when editing a schedule, was possible to crash program without this'),
(40, 6, 3, 'Custom Rules - If custom rule was for a pH probe it failed to run or disply correctly in UI'),
(42, 6, 2, 'System Logs - Added port custom name to source field'),
(43, 6, 1, 'Version Logs - Added version log to system settings page'),
(46, 6, 1, 'Dosing Pumps - Added ability to create a group of schedules'),
(48, 6, 1, 'Dosing Pumps - Added system for dosing pump stirrers'),
(49, 6, 1, 'Dosing Pumps - Speed of dosing pump can now be adjusted during calibration'),
(50, 6, 1, 'Dosing Pumps - User can opt out using the reservior feature, pump can still run if reservior is empty'),
(51, 6, 1, 'Dosing Pumps - Added alert for low reservior, user can set the low limit in milliliters'),
(52, 6, 2, 'Real Time Clock - Raspberry Pi is now managing the real time clock'),
(53, 6, 3, 'Configure Ports - When saving settings from a DB9 it was likely the program would crash as it was repeating save 8 times'),
(54, 6, 2, 'Custom Rules - Updated custom rules to allow user to specify how many milliliters to dose');

-- --------------------------------------------------------

--
-- Table structure for table `versionType`
--

CREATE TABLE `versionType` (
  `versionTypeID` int(11) NOT NULL,
  `versionType` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `versionType`
--

INSERT INTO `versionType` (`versionTypeID`, `versionType`) VALUES
(1, 'Added'),
(2, 'Updated'),
(3, 'Fixed'),
(4, 'Changed');

--
-- Indexes for dumped tables
--


--
-- Indexes for table `button_labels`
--
ALTER TABLE `button_labels`
  ADD PRIMARY KEY (`buttonID`);

--
-- Indexes for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  ADD PRIMARY KEY (`doseID`),
  ADD KEY `pinID` (`dosePinID`),
  ADD KEY `doseStirrerID` (`doseStirrerID`);

--
-- Indexes for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  ADD PRIMARY KEY (`doseSchID`),
  ADD KEY `scheduleID` (`scheduleID`);

--
-- Indexes for table `drivers`
--
ALTER TABLE `drivers`
  ADD PRIMARY KEY (`driverID`);

--
-- Indexes for table `ds18b20`
--
ALTER TABLE `ds18b20`
  ADD PRIMARY KEY (`ds18b20ID`),
  ADD UNIQUE KEY `address` (`address`);

--
-- Indexes for table `errors`
--
ALTER TABLE `errors`
  ADD PRIMARY KEY (`errorID`),
  ADD KEY `errorSourceID` (`errorSourceID`);

--
-- Indexes for table `errorsSource`
--
ALTER TABLE `errorsSource`
  ADD PRIMARY KEY (`errorSourceID`);

--
-- Indexes for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  ADD PRIMARY KEY (`i2cID`),
  ADD UNIQUE KEY `decAddress` (`decAddress`),
  ADD KEY `i2cDeviceTypeID` (`i2cDeviceTypeID`),
  ADD KEY `i2cDriverID` (`i2cDriverID`);

--
-- Indexes for table `i2cDeviceTypes`
--
ALTER TABLE `i2cDeviceTypes`
  ADD PRIMARY KEY (`i2cDeviceTypeID`);

--
-- Indexes for table `lightModes`
--
ALTER TABLE `lightModes`
  ADD PRIMARY KEY (`modeID`),
  ADD KEY `pinID` (`pinID`);

--
-- Indexes for table `log`
--
ALTER TABLE `log`
  ADD PRIMARY KEY (`logID`),
  ADD KEY `logSourceID` (`logSourceID`),
  ADD KEY `logTypeID` (`logTypeID`),
  ADD KEY `logPinID` (`logPinID`),
  ADD KEY `logActionID` (`logActionID`);

--
-- Indexes for table `logAction`
--
ALTER TABLE `logAction`
  ADD PRIMARY KEY (`logActionID`);

--
-- Indexes for table `logSource`
--
ALTER TABLE `logSource`
  ADD PRIMARY KEY (`logSourceID`);

--
-- Indexes for table `logType`
--
ALTER TABLE `logType`
  ADD PRIMARY KEY (`logTypeID`);

--
-- Indexes for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  ADD PRIMARY KEY (`pinID`),
  ADD KEY `driverID` (`driverID`),
  ADD KEY `equipmentID` (`pinTypeID`);

--
-- Indexes for table `pinsType`
--
ALTER TABLE `pinsType`
  ADD PRIMARY KEY (`pinTypeID`);

--
-- Indexes for table `probes`
--
ALTER TABLE `probes`
  ADD PRIMARY KEY (`probeID`),
  ADD KEY `address` (`address`);

--
-- Indexes for table `rules`
--
ALTER TABLE `rules`
  ADD PRIMARY KEY (`ruleID`),
  ADD KEY `ruleTypeID` (`ruleTypeID`);

--
-- Indexes for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  ADD PRIMARY KEY (`rulesEquipmentID`),
  ADD KEY `ruleID` (`ruleID`),
  ADD KEY `equipmentPinID` (`equipmentPinID`);

--
-- Indexes for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  ADD PRIMARY KEY (`rulesSensorID`),
  ADD KEY `ruleID` (`ruleID`),
  ADD KEY `sensorPinID` (`sensorPinID`);

--
-- Indexes for table `rulesType`
--
ALTER TABLE `rulesType`
  ADD PRIMARY KEY (`ruleTypeID`);

--
-- Indexes for table `schedules`
--
ALTER TABLE `schedules`
  ADD PRIMARY KEY (`scheduleID`),
  ADD KEY `scheduleTypeID` (`scheduleTypeID`),
  ADD KEY `pinID` (`pinID`),
  ADD KEY `ruleID` (`ruleID`);

--
-- Indexes for table `scheduleType`
--
ALTER TABLE `scheduleType`
  ADD PRIMARY KEY (`scheduleTypeID`);

--
-- Indexes for table `stirrers`
--
ALTER TABLE `stirrers`
  ADD PRIMARY KEY (`stirrerID`),
  ADD KEY `stirrerPinID` (`stirrerPinID`);

--
-- Indexes for table `systemSettings`
--
ALTER TABLE `systemSettings`
  ADD PRIMARY KEY (`systemSettingID`),
  ADD KEY `userID` (`userID`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userID`),
  ADD UNIQUE KEY `username` (`username`);

--
-- Indexes for table `version`
--
ALTER TABLE `version`
  ADD PRIMARY KEY (`versionID`);

--
-- Indexes for table `versionInfo`
--
ALTER TABLE `versionInfo`
  ADD PRIMARY KEY (`versionInfoID`),
  ADD KEY `versionID` (`versionID`),
  ADD KEY `versionTypeID` (`versionTypeID`);

--
-- Indexes for table `versionType`
--
ALTER TABLE `versionType`
  ADD PRIMARY KEY (`versionTypeID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `button_labels`
--
ALTER TABLE `button_labels`
  MODIFY `buttonID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;
--
-- AUTO_INCREMENT for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  MODIFY `doseID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  MODIFY `doseSchID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `drivers`
--
ALTER TABLE `drivers`
  MODIFY `driverID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `ds18b20`
--
ALTER TABLE `ds18b20`
  MODIFY `ds18b20ID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `errors`
--
ALTER TABLE `errors`
  MODIFY `errorID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `errorsSource`
--
ALTER TABLE `errorsSource`
  MODIFY `errorSourceID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  MODIFY `i2cID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `i2cDeviceTypes`
--
ALTER TABLE `i2cDeviceTypes`
  MODIFY `i2cDeviceTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
--
-- AUTO_INCREMENT for table `lightModes`
--
ALTER TABLE `lightModes`
  MODIFY `modeID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `log`
--
ALTER TABLE `log`
  MODIFY `logID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `logAction`
--
ALTER TABLE `logAction`
  MODIFY `logActionID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `logSource`
--
ALTER TABLE `logSource`
  MODIFY `logSourceID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
--
-- AUTO_INCREMENT for table `logType`
--
ALTER TABLE `logType`
  MODIFY `logTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  MODIFY `pinID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=40;
--
-- AUTO_INCREMENT for table `pinsType`
--
ALTER TABLE `pinsType`
  MODIFY `pinTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;
--
-- AUTO_INCREMENT for table `probes`
--
ALTER TABLE `probes`
  MODIFY `probeID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rules`
--
ALTER TABLE `rules`
  MODIFY `ruleID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  MODIFY `rulesEquipmentID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  MODIFY `rulesSensorID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `rulesType`
--
ALTER TABLE `rulesType`
  MODIFY `ruleTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT for table `schedules`
--
ALTER TABLE `schedules`
  MODIFY `scheduleID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `scheduleType`
--
ALTER TABLE `scheduleType`
  MODIFY `scheduleTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `stirrers`
--
ALTER TABLE `stirrers`
  MODIFY `stirrerID` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `systemSettings`
--
ALTER TABLE `systemSettings`
  MODIFY `systemSettingID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `version`
--
ALTER TABLE `version`
  MODIFY `versionID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `versionInfo`
--
ALTER TABLE `versionInfo`
  MODIFY `versionInfoID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;
--
-- AUTO_INCREMENT for table `versionType`
--
ALTER TABLE `versionType`
  MODIFY `versionTypeID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `dosingPumps`
--
ALTER TABLE `dosingPumps`
  ADD CONSTRAINT `dosingPumps_ibfk_1` FOREIGN KEY (`dosePinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `dosingSchedules`
--
ALTER TABLE `dosingSchedules`
  ADD CONSTRAINT `dosingSchedules_ibfk_1` FOREIGN KEY (`scheduleID`) REFERENCES `schedules` (`scheduleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `errors`
--
ALTER TABLE `errors`
  ADD CONSTRAINT `errors_ibfk_1` FOREIGN KEY (`errorSourceID`) REFERENCES `errorsSource` (`errorSourceID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `i2cDevices`
--
ALTER TABLE `i2cDevices`
  ADD CONSTRAINT `i2cDevices_ibfk_1` FOREIGN KEY (`i2cDeviceTypeID`) REFERENCES `i2cDeviceTypes` (`i2cDeviceTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `i2cDevices_ibfk_2` FOREIGN KEY (`i2cDriverID`) REFERENCES `drivers` (`driverID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `lightModes`
--
ALTER TABLE `lightModes`
  ADD CONSTRAINT `lightModes_ibfk_1` FOREIGN KEY (`pinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `log`
--
ALTER TABLE `log`
  ADD CONSTRAINT `log_ibfk_1` FOREIGN KEY (`logSourceID`) REFERENCES `logSource` (`logSourceID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_2` FOREIGN KEY (`logTypeID`) REFERENCES `logType` (`logTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_3` FOREIGN KEY (`logPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `log_ibfk_4` FOREIGN KEY (`logActionID`) REFERENCES `logAction` (`logActionID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `pinsAvailable`
--
ALTER TABLE `pinsAvailable`
  ADD CONSTRAINT `pinsAvailable_ibfk_1` FOREIGN KEY (`driverID`) REFERENCES `drivers` (`driverID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `pinsAvailable_ibfk_2` FOREIGN KEY (`pinTypeID`) REFERENCES `pinsType` (`pinTypeID`);

--
-- Constraints for table `probes`
--
ALTER TABLE `probes`
  ADD CONSTRAINT `probes_ibfk_1` FOREIGN KEY (`address`) REFERENCES `i2cDevices` (`decAddress`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rules`
--
ALTER TABLE `rules`
  ADD CONSTRAINT `rules_ibfk_1` FOREIGN KEY (`ruleTypeID`) REFERENCES `rulesType` (`ruleTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rulesEquipment`
--
ALTER TABLE `rulesEquipment`
  ADD CONSTRAINT `rulesEquipment_ibfk_1` FOREIGN KEY (`equipmentPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `rulesEquipment_ibfk_2` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `rulesSensors`
--
ALTER TABLE `rulesSensors`
  ADD CONSTRAINT `rulesSensors_ibfk_1` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `rulesSensors_ibfk_2` FOREIGN KEY (`sensorPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `schedules`
--
ALTER TABLE `schedules`
  ADD CONSTRAINT `schedules_ibfk_1` FOREIGN KEY (`scheduleTypeID`) REFERENCES `scheduleType` (`scheduleTypeID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `schedules_ibfk_2` FOREIGN KEY (`pinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `schedules_ibfk_3` FOREIGN KEY (`ruleID`) REFERENCES `rules` (`ruleID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `stirrers`
--
ALTER TABLE `stirrers`
  ADD CONSTRAINT `stirrers_ibfk_1` FOREIGN KEY (`stirrerPinID`) REFERENCES `pinsAvailable` (`pinID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `systemSettings`
--
ALTER TABLE `systemSettings`
  ADD CONSTRAINT `systemSettings_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `users` (`userID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `versionInfo`
--
ALTER TABLE `versionInfo`
  ADD CONSTRAINT `versionInfo_ibfk_1` FOREIGN KEY (`versionID`) REFERENCES `version` (`versionID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `versionInfo_ibfk_2` FOREIGN KEY (`versionTypeID`) REFERENCES `versionType` (`versionTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;