<commit_before>DROP DATABASE LMS;

CREATE DATABASE LMS;

USE LMS;

/*CREATE TABLE t_Sensor_Metadata(
	Sensor_ID		int,
    Name 			varchar(30),
    Manufacturer 	varchar(30),
    Accuracy 		float(5,2),
    Units			varchar(30),
    Min_value 		float(4,2),
    Max_value 		float(4,2),
    Description		blob,
    PRIMARY KEY (Sensor_ID));*/

CREATE TABLE t_Sensor_Data(
	time_recorded 	datetime,
    #Sensor_ID		int,
	wind_speed 		float(4,2),
	wind_direction 	int,
	rainfall 		float(4,2), 
	water_level 	float(4,2),
	water_temp 		float(5,2), 
	ambient_temp 	float(5,2), 
	humidity 		int, 
	flow_rate 		float(4,2), 
		PRIMARY KEY (Time_Recorded));/*,
		FOREIGN KEY (Sensor_ID)
			REFERENCES t_Sensor_Metadata(Sensor_ID));*/
		
CREATE TABLE t_Contact_Info (
    first_name VARCHAR(30),
    last_name VARCHAR(30),
    phone_number CHAR(11),
    email VARCHAR(40),
    #admin TINYINT(1),
    PRIMARY KEY (First_Name , Last_Name)
);
    
/*CREATE TABLE t_Contact_Conditions(
	First_Name 			varchar(30), 
	Last_Name 			varchar(30),
	Max_Wind_Speed 		float(4,2), 
	Max_Rainfall 		float(4,2), 
	Max_Water_Level 	float(4,2), 
	Max_Water_Temp 		float(5,2), 
	Max_Ambient_Temp 	float(5,2), 
	Max_Humidity 		int, 
	Max_Flow_Rate 		float(4,2), 
    FOREIGN KEY (First_Name,Last_Name)
		REFERENCES t_Contact_Info(First_Name, Last_Name));*/
    
/*CREATE TABLE t_Admin_Info(
	First_Name 	varchar(30),
    Last_Name	varchar(30),
	Username 	varchar(20),
    Password 	varchar(20),
    PRIMARY KEY (username),
	FOREIGN KEY (First_Name,Last_Name)
		REFERENCES t_Contact_Info(First_Name, Last_Name, Admin));*/

INSERT INTO t_Sensor_Data Values
	('2015-10-31 11:45:24', 30.22, 1, 5.2, 22, 69, 55, 10, 5);

INSERT INTO t_Sensor_Data Values
	('2015-11-2 1:23:45', 1.333, 8, 2.444, 22.567, 80.4321, 41.1324, 40, 10.314);
INSERT INTO t_Sensor_Data Values
	('2015-11-3 11:23:45', 1.444, 7, 1.444, 12.567, 81.4321, 43.1324, 20, 5.314);
INSERT INTO t_Sensor_Data Values
	('2015-11-4 2:23:45', 1.555, 9, 3.444, 14.567, 82.4321, 47.1324, 11, 6.314);

INSERT INTO t_Contact_Info Values 
	('Jeffrey', 'Magina', 1234567890, '<EMAIL>');

INSERT INTO t_Contact_Info Values 
	('Joshua', 'Tombs', 987654321, '<EMAIL>');
    
/*INSERT INTO t_Sensor_Data Values
	( '2015-11-4 1:25:45', 120, 30, 5.2, 22, 69, 55, 10, 5);
<commit_msg>Change old DB file to new, and add new field for location<commit_after>DROP DATABASE LMS_v2;

CREATE DATABASE LMS_v2;

USE LMS_v2;

CREATE TABLE sensors(
	sensor_id   	INT,
	sensor_type		VARCHAR(15),
	update_rate 	INT,
	location        VARCHAR(20),
	PRIMARY KEY (sensor_id)
);

CREATE TABLE sensordata(
	time_recorded 	DATETIME,
	sensor_id       INT,
	value 			FLOAT(5,2),
	PRIMARY KEY (time_recorded, sensor_id),
	FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id)
);

INSERT INTO sensors VALUES (1, 'wind speed', 15, 'first');
INSERT INTO sensors VALUES (2, 'wind direction', 30, 'second');
INSERT INTO sensors VALUES (3, 'rainfall', 60, 'third');
INSERT INTO sensors VALUES (4, 'water level', 20, 'fourth');
INSERT INTO sensors VALUES (5, 'water temp', 60, 'fifth');
INSERT INTO sensors VALUES (6, 'ambient temp', 20, 'sixth');
INSERT INTO sensors VALUES (7, 'humidity', 60, 'seventh');
INSERT INTO sensors VALUES (8, 'flow rate', 30, 'eighth');
INSERT INTO sensors VALUES (9, 'wind speed', 20, 'ninth');

INSERT INTO sensordata VALUES ('2015-10-31 11:45:24', 1, 30.22);
INSERT INTO sensordata VALUES ('2015-11-2 12:00:24', 1, 32.22);
INSERT INTO sensordata VALUES ('2015-11-3 12:15:24', 1, 31.54);
INSERT INTO sensordata VALUES ('2015-11-4 12:00:24', 2, 32.22);
INSERT INTO sensordata VALUES ('2015-11-4 12:00:25', 1, 35);
INSERT INTO sensordata VALUES ('2015-11-4 12:00:27', 1, 37);
INSERT INTO sensordata VALUES ('2015-11-4 12:02:27', 9, 41);
INSERT INTO sensordata VALUES ('2015-11-4 14:03:09', 3, 21.21);
INSERT INTO sensordata VALUES ('2015-11-4 11:40:34', 4, 45.7);

CREATE TABLE contact (
    first_name 		VARCHAR(30),
    last_name 		VARCHAR(30),
    phone_number 	CHAR(11),
    email 			VARCHAR(40),
    PRIMARY KEY (first_name , last_name)
);
    
CREATE TABLE admin(
	username 		VARCHAR(30),
    password_digest	VARCHAR(60),
	PRIMARY KEY (username));

DELIMITER $$
CREATE TRIGGER limit_admin 
BEFORE INSERT
ON admin
FOR EACH ROW
BEGIN
	SELECT COUNT(*) INTO @cnt FROM Admin;
	IF @cnt > 0 THEN
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "You can not have more than one admin.";
	END IF;
END
$$
DELIMITER ;

INSERT INTO contact VALUES 
	('Jeffrey', 'Magina', 1234567890, '<EMAIL>');

INSERT INTO contact VALUES 
	('Joshua', 'Tombs', 987654321, '<EMAIL>');

INSERT INTO admin VALUES
	('admin', '$2a$10$bdqXZ8ZdyKO7NP7ERmgEC.ZSkJL16HesTat63QmRTPcPlIXYiOwcK');
{'fraction_non_alphanumeric': 0.13617842590606904, 'fraction_numerical': 0.10872828651082994, 'mean_word_length': 4.287981859410431, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4752744', 'n_tokens_mistral': 2289, 'n_tokens_neox': 1848, 'n_words': 500}