Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Installation completed.. but now what?
#1
Hi Forum, Snowy Saturday perfect time to start playing with my Robotank.

I followed the RoboTank App installer document (thank you) and think it all setup a-ok
can login via PuttySSH after the install and reboot.. but how do I launch the actual GUI for the Robotank software?

Thanks much!
 
 John
[-] The following 1 user Likes Jory88's post:
  • Rob F
Reply to top
#2
Hi John, thanks for trying out the app, to access the interface type the IP address of the Pi in your web browser, it should then load the login screen.
Reply to top
#3
Thanks Rob, I get the following when using the IP address:


Quote:You don't have permission to access this resource.Server unable to read htaccess file, denying access to be safe


But I can login to the device via SSH.

Is there a setting I need to update ?

Best,

John
Reply to top
#4
Interesting, someone else is having the same issue. About an hour ago I did a fresh install and it worked so I'm a little baffled right now. I do include an htaccess file to manage files being cached but even when I remove mine it still loads.

What browser are you using? Any chance it's not Chrome or Firefox?

I'm looking into this more now, hopefully I have an answer soon.

One other thing, when you installed the Pi OS did you select "Raspberry Pi OS Lite (Legacy)" this is the Debian Buster version?
Reply to top
#5
Is it possible to take a screenshot of the error?
Reply to top
#6
(03-11-2023, 03:25 PM)Rob F Wrote: Interesting, someone else is having the same issue. About an hour ago I did a fresh install and it worked so I'm a little baffled right now. I do include an htaccess file to manage files being cached but even when I remove mine it still loads.

What browser are you using? Any chance it's not Chrome or Firefox?

I'm looking into this more now, hopefully I have an answer soon.

One other thing, when you installed the Pi OS did you select "Raspberry Pi OS Lite (Legacy)" this is the Debian Buster version?


Chrome: Version 111.0.5563.64 (Official Build) (64-bit)

Raspberry Pi OS Lite (Legacy)

J.
Reply to top
#7
Can you try running the following commands, I'm wondering if permissions aren't set correctly.

sudo chmod 755 /var/www/html/backup/
sudo chmod 755 /var/www/html/chartData/
sudo chmod 755 /var/www/html/cpp/
sudo chmod 755 /var/www/html/css/
sudo chmod 755 /var/www/html/images/
sudo chmod 755 /var/www/html/install/
sudo chmod 755 /var/www/html/js/
sudo chmod 755 /var/www/html/libaries/
sudo chmod 755 /var/www/html/php/
sudo chmod 755 /var/www/html/updates/
Reply to top
#8
(03-11-2023, 03:32 PM)Rob F Wrote: Can you try running the following commands, I'm wondering if permissions aren't set correctly.

sudo chmod 755 /var/www/html/backup/
sudo chmod 755 /var/www/html/chartData/
sudo chmod 755 /var/www/html/cpp/
sudo chmod 755 /var/www/html/css/
sudo chmod 755 /var/www/html/images/
sudo chmod 755 /var/www/html/install/
sudo chmod 755 /var/www/html/js/
sudo chmod 755 /var/www/html/libaries/
sudo chmod 755 /var/www/html/php/
sudo chmod 755 /var/www/html/updates/


Thanks Rob, I've tried the install script several times but keep getting the same issue with the systems not being accessible via Web Browser.

I ran the commands above tonight and all went well EXCEPT for :
sudo chmod 755 /var/www/html/libaries/
chmod: cannot access '/var/www/html/libaries/': No such file or directory

Thanks for your help
Reply to top
#9
Sorry this isn't going so well, it's hard to find the problem as I can't reproduce it but I know it's an issue as someone else is experiencing the same. I've been a little busy these last few days but definitely going to get this solved as soon as possible. I'll report back soon.

As for that error it looks like I misspelled libraries but either way that won't solve it.
Reply to top
#10
No worries... Not in a rush.

Appreciate the help.

J.
Reply to top
#11
I had the same issue, I was able to fix on my side using this:

Code:
sudo usermod -a -G www-data username
sudo chown -R -f www-data:www-data /var/www/html
Where username is my user.

For reference I tried this per an Apache setup guide here:
https://pimylifeup.com/raspberry-pi-apache/

I get the login screen now but cannot login, I took at look at the phpmyadmin and I don't have the 4th database, I only have information_schema, mysql, and performance_schema so I suspect something went wrong during the install script. I'll take a closer look or maybe try to install again tomorrow.
[-] The following 1 user Likes crlanglois's post:
  • Rob F
Reply to top
#12
Hi crlanglois, thanks for sharing that, much appreciate it!

I wonder if the database wasn't added due to no permissions. Here's the SQL to run from the phpmyadmin SQL tab, this should add it.

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 */;
[-] The following 1 user Likes Rob F's post:
  • Jory88
Reply to top
#13
It's working well so far for me now, thanks! There's a moon on the dashboard that says "starting in" "is coming in" next to it. What is that for?
Reply to top
#14
Great, thanks for confirming. At some point that moon section will be used to show the current light mode so nothing today. Before that I'm going to redo the light modes to something better.
Reply to top
#15
Thank you! I can get to the Robo-Tank login screen now.

BUT. The user name password combo (Robo-Tank) don't seem to work.
No errors, just resets the page and asks for 'password' adain and again...

Suggestions?
Reply to top
#16
Right on, one more step and you should be in. It's likely the database wasn't created so you have to add it manually. To do this you need to log into phpmyadmin, here's how.

Add /phpmyadmin/ to your Pi IP address like below and log in.

http://192.168.1.45/phpmyadmin/

Username = root
Password  = robotank

At the top of the page you should see a bunch of tabs, the second is "SQL", click on that tab and the page should look similar to this with a large text box.

   

Now copy and paste the following into that text box and press the "Go" button on bottom right corner. It'll take about 20 seconds and should say success. Now refresh the login and screen and hopefully it works.

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 */;
Reply to top
#17
Eureka!

Thanks Rob and others for all your help

Can login and start playing around now.

Merci Beaucoup!
Reply to top
#18
Yes it was great others jumped in otherwise it would have been a while for me to figure it out. Hopefully after addressing these issues the install can go smooth as it certainly doesn't look good if it can't even be installed without issue. If you have any problems let me know.
Reply to top


Forum Jump:

Current time: 03-28-2024, 06:37 AM