esp_garden git · master

-- CREATE TABLE FOR ESP DATA
CREATE TABLE moisture_2 (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    time INT UNSIGNED DEFAULT NULL,
    value VARCHAR(255) NOT NULL,
    pump_reached TINYINT(1) NOT NULL DEFAULT 0
);
INSERT INTO test (value) VALUES ('817273');

CREATE TABLE `data_tables` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `description` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- CREATE USER TABLE
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ESP TYPES (pump, sensor, server..)
CREATE TABLE esp_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(32) NOT NULL UNIQUE
);
-- INSERT INTO esp_types (name) VALUES ('sensor'), ('pump');

-- ESP TABLES
CREATE TABLE esps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(64) NOT NULL UNIQUE,
    mac VARCHAR(17) NOT NULL UNIQUE,
    type_id INT UNSIGNED NOT NULL,
    FOREIGN KEY (type_id) REFERENCES esp_types(id)
);


-- ESP CONNECTIONS (pump -> [sensor, server])
CREATE TABLE esp_connections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    esp_id INT UNSIGNED NOT NULL,
    connected_esp_id INT UNSIGNED NOT NULL,
    UNIQUE KEY (esp_id, connected_esp_id),
    FOREIGN KEY (esp_id) REFERENCES esps(id) ON DELETE CASCADE,
    FOREIGN KEY (connected_esp_id) REFERENCES esps(id) ON DELETE CASCADE
);

-- ESP SENSORS (what when why)
CREATE TABLE esp_sensors (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    esp_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    info TEXT,
    pin INT NOT NULL,
    use_time BOOLEAN NOT NULL DEFAULT 0,
    delay_ms INT DEFAULT 0,
    min_moisture INT DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT 1,
    FOREIGN KEY (esp_id) REFERENCES esps(id) ON DELETE CASCADE,
    UNIQUE KEY (esp_id, name)
);

-- ESP SENSORS CHANGES (if config changed)
CREATE TABLE sensor_changes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT UNSIGNED NOT NULL,
    column_name VARCHAR(64) NOT NULL,
    update_sent TINYINT(1) NOT NULL DEFAULT 0,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sensor_id) REFERENCES esp_sensors(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ESP TIMES (10:00, 12:00,..)
CREATE TABLE sensor_times (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT UNSIGNED NOT NULL,
    time TIME NOT NULL,
    FOREIGN KEY (sensor_id) REFERENCES esp_sensors(id) ON DELETE CASCADE
);

-- ESP NOTIFY (Sensor sends pump_tomatoe to Pump)
CREATE TABLE sensor_notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT UNSIGNED NOT NULL,
    send_to_esp_id INT UNSIGNED NOT NULL,
    notification_name VARCHAR(64) NOT NULL,
    FOREIGN KEY (sensor_id) REFERENCES esp_sensors(id) ON DELETE CASCADE,
    FOREIGN KEY (send_to_esp_id) REFERENCES esps(id) ON DELETE CASCADE,
    UNIQUE KEY (sensor_id, send_to_esp_id, notification_name)
);

-- ESP PUMP
CREATE TABLE esp_pumps (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    esp_id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    info TEXT DEFAULT NULL,
    pin INT NOT NULL,
    pumping_delay_sec INT UNSIGNED DEFAULT 0,
    pumping_duration INT UNSIGNED DEFAULT 0,
    UNIQUE KEY ux_esp_pumps_esp_name (esp_id, name),
    CONSTRAINT fk_esp_pumps_esp_id FOREIGN KEY (esp_id) REFERENCES esps(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- SENSOR NOTIFICATIONS
CREATE TABLE sensor_notifications (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sensor_id INT UNSIGNED NOT NULL,
    send_to_esp_id INT UNSIGNED NOT NULL,
    notification_name VARCHAR(64) NOT NULL,
    UNIQUE KEY ux_sensor_notifications (sensor_id, send_to_esp_id, notification_name),
    KEY fk_sensor_notifications_pump_name (send_to_esp_id, notification_name),
    CONSTRAINT fk_sensor_notifications_sensor_id FOREIGN KEY (sensor_id) REFERENCES esp_sensors(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_sensor_notifications_pump_name FOREIGN KEY (send_to_esp_id, notification_name)
        REFERENCES esp_pumps(esp_id, name) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;