# CreateAieiTable_Mysql.sql

DROP database IF EXISTS aiei;
create database aiei;
use aiei;

# Table to hold raw data

DROP TABLE IF EXISTS raw;
#@ _CREATE_TABLE_
create table raw(
	id int not null,
	year int not null,
	day int not null,
	time int not null,
	mean_ta_2m_c float(24),
	min_ta_2m_c float(24),
	max_ta_2m_c float(24),
	mean_ts_10cm_c float(24),
	min_ts_10cm_c float(24),
	max_ts_10cm_c float(24),
	mean_rh_2m_pct float(24),
	min_rh_2m_pct float(24),
	max_rh_2m_pct float(24),
	mean_ws_3m_mps float(24),
	mean_wd_3m_deg float(24),
	sd_wd_deg float(24),
	min_ws_3m_mps float(24),
	max_ws_3m_mps float(24),
	rain_tot_mm float(24),
	rain_can_mm float(24),
	rad_flx_kjpm2 float(24),
	mean_dew_c float(24),
	mean_vap_kpa float(24),
	mean_vap_sat_kpa float(24),
	mean_vap_def_kpa float(24),
	tot_et_mm float(24),
	sm_10cm_pct float(24),
        sm_20cm_pct float(24),
	sm_30cm_pct float(24),
        sm_50cm_pct float(24),
	ssf_10cm_real float(24),
        ssf_20cm_real float(24),
	ssf_30cm_real float(24),
        ssf_50cm_real float(24),
	it_int int,
	bv_vot_real float(24),

        PRIMARY KEY (id, year, day, time)

) TYPE=MyISAM;  
#The possible choice for type (storage format) are isam, Myisam and heap
# float(24) means 24 bytes
#@ _CREATE_TABLE_

SHOW COLUMNS FROM raw;

	
INSERT INTO raw 
    SET id = 240,
       	year = 2004,
	day = 140,
	time = 1340,
	mean_ta_2m_c = 32.63,
	min_ta_2m_c = 2.89,
	max_ta_2m_c = 102.89,
	mean_ts_10cm_c = 29.11,
	min_ts_10cm_c = 69.11,
	max_ts_10cm_c = 9.11,
	mean_rh_2m_pct = 54.43,
	min_rh_2m_pct = 154.43,
	max_rh_2m_pct = 54.43,
	mean_ws_3m_mps = 134.7,
	mean_wd_3m_deg = 23.6,
	sd_wd_deg = 8.0,
	min_ws_3m_mps = 4.7,
	max_ws_3m_mps = 1134.7,
	rain_tot_mm = 783,
	rain_can_mm = 83,
	rad_flx_kjpm2 = 70.0,
	mean_dew_c = 20.0,
	mean_vap_kpa = 4.87,
	mean_vap_sat_kpa = 4.87,
	mean_vap_def_kpa = 4.87,
	tot_et_mm = 110,
	sm_10cm_pct = 11,
        sm_20cm_pct = 22,
	sm_30cm_pct = 33,
        sm_50cm_pct = 13,
	ssf_10cm_real = 0.3,
	ssf_20cm_real = 0.4,
	ssf_30cm_real = 0.2,
	ssf_50cm_real = 0.9,
	it_int = 65,
	bv_vot_real = 11 
;


DROP TABLE IF EXISTS location;
create table location(
	id int unsigned not null,   #Station ID number
	location char(35),
	site_location_id int unsigned not null,
	comment char(100),
	PRIMARY KEY (id),
	UNIQUE (location)

);

# Load location.txt into the location table
# in the location.txt, the column data are separated by tab
# the warning of excution is from field comment which has no data provided

DELETE FROM location;
LOAD DATA LOCAL INFILE 'location.txt' INTO TABLE location LINES TERMINATED BY '\r\n';

DROP TABLE IF EXISTS sensor;
create table sensor(
	id int unsigned not null,
	manufacturer char(10),
	model char(10),
	svc_date date,
	units char(10),
	type char(10),
	ub decimal(6,3),
	lb decimal(6,3),

) TYPE=MyISAM;


DROP TABLE IF EXISTS error_message;
create table error_message(
	id int unsigned not null,
	message TEXT NOT NULL

) TYPE=MyISAM;