# 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;