#!/opt/ruby187/bin/ruby

require 'rubygems'
require 'sqlite3'

class App

TABLE_A = <<EOT
T1	T2	A1	A2	ii	Priority	DataType
A	B1	C1	C1	(2)	3 	Analyses
B	(3)	(3)	(3)	(3)	1/2/4(1) 	Addressed message
C	B1	C1	C1	(2)	4 	Climatic data
D	B2	C3	C4	D2	3 	Grid point information (GRID)
E	B5	C1	C1	(2)	3 	Satellite imagery
F	B1	C1	C1	(2)	3 	Forecast
G	B2	C3	C4	D2	3 	Grid point information (GRID)
H	B2	C3	C4	D2	3 	Grid point information (GRIB)
I	B3	C6	C3	(2)	2 	Observational data (BUFR)
J	B3	C6	C4	D2	3 	Forecast information (BUFR)
K	C7 	C7	C3	(2)	2 	CREX
L						-
M						-
N	B1	C1	C1	(2)	4 	Notices
O	B4	C3	C4	D1	3 	Oceanographic information (GRIB)
P	B6	C3	C4	D2	3 	Pictorial information (Binary coded)
Q	B6	C3	C5	D2	3 	Pictorial information regional (Binary coded)
R						-
S	B1	C1/C2	C1/C2	(2)	2/4(1) 	Surface data
T	B1	C3	C4	(2)	2 	Satellite data
U	B1	C1/C2	C1/C2	(2)	2 	Upper air data
V	(4)	C1	C1	(2)	(5) 	National data
W	B1	C1	C1	(2)	1 	Warnings
X	B2	C3	C5	D2	3 	GRID regional use
Y	B2	C3	C5	D2	3 	GRIB regional use
Z						-
EOT

TABLE_B1 = <<EOT
T1	T2	DataType	CodeForm
A	C	Cyclone	[TEXT]
A	G	Hydrological/Marine	[TEXT]
A	H	Thickness	[TEXT]
A	I	Ice	FM 44 (ICEAN)
A	O	Ozone layer	[TEXT]
A	R	Radar	[TEXT]
A	S	Surface	FM 45 (IAC)/FM 46 (IAC FLEET)
A	U	Upper air	FM 45 (IAC)
A	W	Weather Summary	[TEXT]
A	X	Miscellaneous	[TEXT]
C	A	Climatic anomalies	[TEXT]
C	E	Monthly means (upper air)	FM 76 (CLIMAT TEMP SHIP)
C	H	Monthly means (surface)	FM 72 (CLIMAT SHIP)
C	O	Monthly means (ocean areas)	FM 73 (NACLI,CLINP,SPCLI,CLISA,INCLI)
C	S	Monthly means (surface)	FM 71 (CLIMAT)
C	U	Monthly means (upper air)	FM 75 (CLIMAT TEMP)
#F	A	Aviation area /GAMET/advisories	FM 53 (ARFOR)/[TEXT]
F	B	Upper winds and temperatures	FM 50 (WINTEM)
F	C	Aerodrome (VT < 12 hours)	FM 51 (TAF)
F	D	Radiological trajectory dose	FM 57 (RADOF)
F	E	Extended	[TEXT]
F	F	Shipping	FM 46 (IAC FLEET)
F	G	Hydrological	FM 68 (HYFOR)
F	H	Upper air thickness	[TEXT]
F	I	Iceberg	[TEXT]
F	J	Radio warning service (including IUWDS data)	[TEXT]
F	K	Tropical cyclone advisories	[TEXT]
F	L	Local/Area	[TEXT]
F	M	Temperature extremes	[TEXT]
F	O	Guidance	[TEXT]
F	P	Public	[TEXT]
F	Q	Other shipping	[TEXT]
F	R	Aviation route	FM 54 (ROFOR)
F	S	Surface	FM 45 (IAC)/FM 46 (IAC FLEET)
F	T	Aerodrome (VT >= 12 hours)	FM 51 (TAF)
F	U	Upper air	FM 45 (IAC)
F	V	Volcanic ash advisories	[TEXT]
F	W	Winter sports	[TEXT]
F	X	Miscellaneous	[TEXT]
F	Z	Shipping area	FM 61 (MAFOR)
N	G	Hydrological	[TEXT]
N	H	Marine	[TEXT]
N	N	Nuclear emergency response	[TEXT]
N	O	METNO/WIFMA	[TEXT]
N	P	Product generation delay	[TEXT]
N	T	TEST MSG [System related]	[TEXT]
N	W	Warning related and/or cancellation	[TEXT]
S	A	Aviation routine reports	FM 15 (METAR)
S	B	Radar reports (Part A)	FM 20 (RADOB)
S	C	Radar reports (Part B)	FM 20 (RADOB)
S	D	Radar reports (Parts A & B)	FM 20 (RADOB)
S	E	Seismic data	*(SEISMIC)
S	F	Atmospherics reports	FM 81 (SFAZI)/FM 82 (SFLOC)/FM 83 (SFAZU)
S	G	Radiological data report	FM 22 (RADREP)
S	I	Intermediate synoptic hour	FM 12 (SYNOP)/FM 13 (SHIP)
S	L	-	-
S	M	Main synoptic hour	FM 12 (SYNOP)/FM 13 (SHIP)
S	N	Non-standard synoptic hour	FM 12 (SYNOP)/FM 13 (SHIP)
S	O	Oceanographic data	FM 63 (BATHY)/FM 64 (TESAC)/FM 62 (TRACKOB)
S	P	Special aviation weather reports	FM 16 (SPECI)
S	R	Hydrological (river) reports	FM 67 (HYDRA)
S	S	Drifting buoy reports	FM 18 (BUOY)
S	T	Sea Ice	[TEXT]
S	U	Snow depth	[TEXT]
S	V	Lake ice	[TEXT]
S	X	Miscellaneous	[TEXT]
S	W	Wave information	FM 65 (WAVEOB)
S	Z	Sea level data and deep-ocean tsunami data	[any alphanumeric format]
S	Y	Seismic wave form data	[any format]
T	B	Satellite orbit parameters	[TEXT]
T	C	Satellite cloud interpretations	FM 85 (SAREP)
T	H	Satellite remote upper air soundings	FM 86 (SATEM)
T	R	Clear radiance observations	FM 87 (SARAD)
T	T	Sea surface temperatures	FM 88 (SATOB)
T	W	Winds and cloud temperatures	FM 88 (SATOB)
T	X	Miscellaneous	[TEXT]
U	L	Upper level pressure, temperature, humidity and wind (Part C)	FM 35 (TEMP)/FM 36 (TEMP SHIP)/FM 38 (TEMP MOBIL)
U	M	Upper level pressure, temperature, humidity and wind (Parts A and B) [National and bilateral option]	FM 35 (TEMP)/FM 36 (TEMP SHIP)/FM 38 (TEMP MOBIL)
U	N	Rocketsonde reports	FM 39 (ROCOB)/FM 40 (ROCOB SHIP)
U	P	Upper wind (Part A)	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	Q	Upper wind (Part D)	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	R	Aircraft report	[NATIONAL**] (RECCO)
U	S	Upper level pressure, temperature, humidity and wind (Part A)	FM 35 (TEMP)/FM 36 (TEMP SHIP)FM 38 (TEMP MOBIL)
U	T	Aircraft report	FM 41 (CODAR)
U	X	Miscellaneous	[TEXT]
U	Y	Upper wind (Parts C and D) [National and bilateral option]	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	Z	Upper level pressure, temperature, humidity and wind from a sonde released by carrier balloon or aircraft (Parts A, B, C, D)	FM 37 (TEMP DROP)
#U	A	Aircraft reports	FM 41 (CODAR), ICAO (AIREP)
U	D	Aircraft reports	FM 42 (AMDAR)
U	E	Upper level pressure, temperature, humidity and wind (Part D)	FM 35 (TEMP)/FM 36 (TEMP SHIP)/FM 38 (TEMP MOBIL)
U	F	Upper level pressure, temperature, humidity and wind (Parts C and D) [National and bilateral option]	FM 35 (TEMP)/FM 36 (TEMP SHIP)/FM 38 (TEMP MOBIL)
U	G	Upper wind (Part B)	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	H	Upper wind (Part C)	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	I	Upper wind (Parts A and B) [National and bilateral option]	FM 32 (PILOT)/FM 33 (PILOT SHIP)/FM 34 (PILOT MOBIL)
U	K	Upper level pressure, temperature, humidity and wind(Part B)	FM 35 (TEMP)/FM 36 (TEMP SHIP)/FM 38 (TEMP MOBIL)
W	A	AIRMET	[TEXT]
W	C	Tropical cyclone (SIGMET)	[TEXT]
W	E	Tsunami	[TEXT]
W	F	Tornado	[TEXT]
W	G	Hydrological/River flood	[TEXT]
W	H	Marine/Coastal flood	[TEXT]
W	O	Other	[TEXT]
W	S	SIGMET	[TEXT]
W	T	Tropical cyclone (Typhoon/Hurricane)	[TEXT]
W	U	Severe thunderstorm	[TEXT]
W	V	Volcanic ash clouds (SIGMET)	[TEXT]
W	W	Warnings and weather summary	[TEXT]
EOT

TABLE_B2 = <<EOT
T1	T2	DataType
D, G, H, X, Y	A	Radar data
D, G, H, X, Y	B	Cloud
D, G, H, X, Y	C	Vorticity
D, G, H, X, Y	D	Thickness (relative topography)
D, G, H, X, Y	E	Precipitation
D, G, H, X, Y	F	-
D, G, H, X, Y	G	Divergence
D, G, H, X, Y	H	Height
D, G, H, X, Y	I	-
D, G, H, X, Y	J	Wave height + combinations
D, G, H, X, Y	K	Swell height + combinations
D, G, H, X, Y	L	-
D, G, H, X, Y	M	For national use
D, G, H, X, Y	N	Radiation
D, G, H, X, Y	O	Vertical velocity
D, G, H, X, Y	P	Pressure
D, G, H, X, Y	Q	Wet bulb potential temperature
D, G, H, X, Y	R	Relative humidity
D, G, H, X, Y	S	-
D, G, H, X, Y	T	Temperature
D, G, H, X, Y	U	Eastward wind component
D, G, H, X, Y	V	Northward wind component
D, G, H, X, Y	W	Wind
D, G, H, X, Y	X	Lifted index
D, G, H, X, Y	Y	Observational plotted chart
D, G, H, X, Y	Z	Not assigned 
#I, J	N	Satellite data
#I, J	O	Oceanographic/limnographic (water properties)
#I, J	P	Pictorial
#I, J	S	Surface/sea level
#I, J	T	Text (plain language information)
#I, J	U	Upper air
#I, J	X	Other data types
O	D	Depth
O	E	Ice concentration
O	F	Ice thickness
O	G	Ice drift
O	H	Ice growth
O	I	Ice convergence/divergence
O	Q	Temperature anomaly
O	R	Depth anomaly
O	S	Salinity
O	T	Temperature
O	U	Current component
O	V	Current component
O	W	Temperature warming
O	X	Mixed data
E	C	Cloud top temperature
E	F	Fog
E	I	Infrared
E	S	Surface temperature
E	V	Visible
E	W	Water vapour
E	Y	User specified
E	Z	Unspecified
P, Q	A	Radar data
P, Q	B	Cloud
P, Q	C	Clear air turbulence
P, Q	D	Thickness (relative topography)
P, Q	E	Precipitation
P, Q	F	Aerological diagrams (Ash Cloud)
P, Q	G	Significant weather
P, Q	H	Height
P, Q	I	Ice flow
P, Q	J	Wave height + combinations
P, Q	K	Swell height + combinations
P, Q	L	Plain language
P, Q	M	For national use
P, Q	N	Radiation
P, Q	O	Vertical velocity
P, Q	P	Pressure
P, Q	Q	Wet bulb potential temperature
P, Q	R	Relative humidity
P, Q	S	Snow cover
P, Q	T	Temperature
P, Q	U	Eastward wind component
P, Q	V	Northward wind component
P, Q	W	Wind
P, Q	X	Lifted index
P, Q	Y	Observational plotted chart
P, Q	Z	Not assigned
EOT

TABLE_C6 = <<EOT
T1	T2	A1	ii	TAC correspondence	Data Category Sub Category (Common Table C13)	DataType
I	N	A			3003	Satellite data (AMSUA)
I	N	B			3004	Satellite data (AMSUB)
I	N	H			3005	Satellite data (HIRS)
I	N	M			3006	Satellite data (MHS)
I	O	B		BUOY	001/025	Buoy observations
I	O	I				Sea ice
I	O	P		TESAC	031/004	Sub-surface profiling floats
I	O	R		TRACKOB	031/001	Sea surface observations
I	O	S		BATHY, TESAC	031/005	Sea surface and below soundings
I	O	T				Sea surface temperature
I	O	W		WAVEOB	031/002	Sea surface waves
I	O	X				Other sea environmetal
I	P	C				Radar composite imagery data
I	P	I				Satellite imagery data
I	P	R				Radar imagery data
I	P	X				Not defined
I	S	A	01-29	n/a	000/006	Routinely scheduled observations for distribution from automatic (fixed or mobile) land stations (e.g. 0000, 0100, … or 0220, 0240, 0300, …, or 0715, 0745, ... UTC)
I	S	A	30-59	n/a	000/007	N-minute observations from automatic (fixed or mobile) land stations 
I	S	B		RADOB	006/003	Radar reports (parts A and B)
I	S	C	01-45	CLIMAT	000/020	Climatic observations from land stations
I	S	C	46-59	CLIMAT SHIP	001/020	Climatic observations from marine stations
I	S	D		RADREP	010/001	Radiological observation
I	S	E		n/a	008/000	Ozone measurement at surface
I	S	F		SFAZI, SFLOC, SFAZU	000/030	Source of atmospherics
I	S	I	01-45	SYNOP (SIxx)	000/001 000/051	Intermediate synoptic observations from fixed land stations
I	S	I	46-59	SYNOP MOBIL 	000/004	Intermediate synoptic observations from mobile land stations
I	S	M	01-45	SYNOP (SMxx)	000/002 000/052	Main synoptic observations from fixed land stations
I	S	M	46-59	SYNOP MOBIL 	000/005	Main synoptic observations from mobile land stations
I	S	N	01-45	SYNOP (SNxx)	000/000 000/050	Synoptic observations from fixed land stations at non-standard time (i.e. 01, 02, 04, 05, ... UTC)
I	S	N	46-59	SYNOP MOBIL 	000/003	Synoptic observations from mobile land stations at non-standard time (i.e. 01, 02, 04, 05, ... UTC)
I	S	R		HYDRA	000/040	Hydrologic reports
I	S	S	01-19	SHIP 	001/000	Synoptic observations from marine stations
I	S	S	20-39	n/a 	001/006	One-hour observations from automatic marine stations
I	S	S	40-59	n/a 	001/007	N-minute observations from automatic marine stations
I	S	T	01-19	n/a 	001/030	Tide gauge observations
I	S	T	20-39	n/a 	001/031	Observed water level time series
I	S	V		SPECI	000/011	Special aeronautical observations (SPECI)
I	S	W		METAR	000/010	Aviation routine weather observations (METAR)
I	S	X		IAC, IAC FLEET		Other surface data
I	T	A				Administrative message
I	T	B				Service message
I	T	R				Request for data (inclusive of type)
I	T	X				Other text messages of information
I	U	A		AMDAR	004/000	Single level aircraft reports (automatic)
I	U	A		AIREP/PIREP	004/001	Single level aircraft reports (manual)
I	U	B		n/a		Single level balloon reports
I	U	C		SAREP/SATOB	005/000	(used for single level satellite-derived reports ? see Note 3)
I	U	D		TEMP DROP	002/007	Dropsonde/Dropwindsondes
I	U	E		n/a	008/001	Ozone vertical sounding
I	U	I		n/a	009/000	Dispersal and transport analysis
I	U	J	01-19	PILOT (parts A, B, C, D)	002/001	Upper wind from fixed land stations (entire sounding)
I	U	J	20-39	PILOT MOBIL (parts A, B, C, D)	002/003	Upper wind from mobile land stations (entire sounding)
I	U	J	40-59	PILOT SHIP (parts A, B, C, D)	002/002	Upper wind from marine stations (entire sounding)
I	U	K	01-19	TEMP (parts A, B)	002/004	Radio soundings from fixed land stations (up to 100 hPa)
I	U	K	20-39	TEMP MOBIL (parts A, B)	002/006	Radio soundings from mobile land stations (up to 100 hPa)
I	U	K	40-59	TEMP SHIP (parts A, B)	002/005	Radio soundings from marine stations (up to 100 hPa)
I	U	M				Model derived sondes
I	U	N				Rocketsondes
I	U	O		AMDAR	002/020	Profiles of aircraft observations in ascending /descending
I	U	P		PILOT	002/010	Profilers
I	U	Q		TEMP	002/011	RASS temperature profilers
I	U	R				(used for radiance data ? see Note 3)
I	U	S	01-19	TEMP (parts A, B, C, D)	002/004	Radiosondes/pibal reports from fixed land stations (entire sounding)
I	U	S	20-39	TEMP MOBIL (parts A, B, C, D)	002/006	Radio soundings from mobile land stations (entire sounding)
I	U	S	40-59	TEMP SHIP (parts A, B, C, D)	002/005	Radio soundings from marine stations (entire sounding)
I	U	T		SATEM, SARAD, SATOB		(used for satellite-derived sondes ? see Note 3)
I	U	U	01-45	CLIMAT TEMP 	002/025	Monthly statistics of data from upper-air stations
I	U	U	46-59	CLIMAT TEMP, SHIP	002/026	Monthly statistics of data from marine stations
I	U	W	01-19	PILOT (parts A, B)	002/001	Upper wind from fixed land stations (up to 100 hPa)
I	U	W	20-39	PILOT MOBIL (parts A, B)	002/003	Upper wind from mobile land stations (up to 100 hPa)
I	U	W	40-59	PILOT SHIP (parts A, B)	002/002	Upper wind from marine stations (up to 100 hPa)
I	U	X				Other upper air reports
J	O	I				Sea ice
J	O	S				Sea surface and below soundings
J	O	T				Sea surface temperature
J	O	W				Sea surface waves
J	O	X				Other sea environmetal data
J	S	A				Surface area forecast (e.g. airways)
J	S	D		RADOF		Radiological forecast
J	S	M				Surface forecasts (e.g. MOS)
J	S	O		MAFOR		Maritime forecast
J	S	P				Forecast amendments (airways)
J	S	R		HYFOR		Hydrologic forecast
J	S	S				Forecast amendments (TAF)
J	S	T				Aerodrome forecast (TAF)
J	S	X				Other surface forecasts
J	T	E				Tsunami
J	T	H				Hurricane, typhoon, tropical storm warning
J	T	S				Severe weather, SIGMET
J	T	T				Tornado warning
J	T	X				Other warnings
J	U	C				Binary coded SIGWX, Clear air turbulence
J	U	F				Binary coded SIGWX, Fronts
J	U	N				Binary coded SIGWX, Other SIGWX parameters
J	U	O				Binary coded SIGWX, Turbulence
J	U	S				Forecast soundings
J	U	T				Binary coded SIGWX, Icing/Tropopause
J	U	V				Binary coded SIGWX, Tropical storms, sandstorms, volcanoes
J	U	W				Binary coded SIGWX, High-level winds
J	U	X				Other upper air forecasts
K	F	A				Surface area forecast (e.g. airways)
K	F	D		RADOF		Radiological forecast
K	F	M				Surface forecasts (eg MOS)
K	F	O		MAFOR		Maritime forecast 
K	F	P				Forecast amendments (airways)
K	F	R		HYFOR		Hydrologic forecast
K	F	S				Forecast amendments (TAF)
K	F	T				Aerodrome forecast (TAF)
K	F	X				Other surface forecasts
K	O	B		BUOY	001/025	Buoy observations 
K	O	I				Sea ice
K	O	P		TESAC 	031/004	Sub-surface profiling floats 
K	O	R		TRACKOB	031/001	Sea surface observations 
K	O	S		BATHY, TESAC 	031/005	Sea surface and below soundings
K	O	T				Sea surface temperature
K	O	W		WAVEOB 	031/002	Sea surface waves 
K	O	X		WAVEOB 	031/002	Other sea environmental
K	P	I				Sea ice
K	P	S				Sea surface and below soundings
K	P	T				Sea surface temperature
K	P	W				Sea surface waves
K	P	X				Other sea environmental
K	S	A	01-29	n/a 	000/006	Routinely scheduled observations for distribution from automatic (fixed or mobile) land stations (e.g. 0000, 0100, … or 0220, 0240, 0300, …, or 0715, 0745, … UTC) 
K	S	A	30-59	n/a 	000/007	N-minute observations from automatic (fixed or mobile) land stations
K	S	B		RADOB 	006/003	Radar reports (parts A and B) 
K	S	C	01-45	CLIMAT 	000/020	Climatic observations from land stations
K	S	C	46-59	CLIMAT SHIP 	001/020	Climatic observations from marine stations
K	S	D		RADREP	010/001	Radiological observation 
K	S	E		n/a 	008/000	Ozone measurement at surface 
K	S	F		SFAZI, SFLOC, SFAZU	000/030	Source of atmospherics
K	S	I	01-45	SYNOP (SIxx)	000/001 000/051	Intermediate synoptic observations from fixed land stations
K	S	I	46-59	SYNOP MOBIL 	000/004	Intermediate synoptic observations from mobile land stations
K	S	M	01-45	SYNOP (SMxx)	000/002 000/052	Main synoptic observations from fixed land stations
K	S	M	46-59	SYNOP MOBIL 	000/005	Main synoptic observations from mobile land stations
K	S	N	01-45	SYNOP (SNxx)	000/000 000/050	Synoptic observations from fixed land stations at non-standard time (i.e. 01, 02, 04, 05, ... UTC)
K	S	N	46-59	SYNOP MOBIL 	000/003	Synoptic observations from mobile land stations at non-standard time (i.e. 01, 02, 04, 05, 07, 08, 10, 11, 13, ..UTC)
K	S	R		HYDRA 	000/040	Hydrologic reports 
K	S	S	01-19	SHIP	001/000	Synoptic observations from marine stations
K	S	S	20-39	n/a 	001/006	One-hour observations from automatic marine stations
K	S	S	40-59	n/a 	001/007	N-minute observations from automatic marine stations
K	S	V		SPECI 	000/011	Special aeronautical observations (SPECI)
K	S	W		METAR 	000/010	Aviation routine weather observations (METAR)
K	S	X		IAC, IAC FLEET		Other surface data
K	T	E				Tsunami
K	T	H				Hurricane, typhoon, tropical storm warning
K	T	S				Severe weather, SIGMET
K	T	T				Tornado warning
K	T	X				Other warnings
K	U	A		AMDAR 	004/000	Single level aircraft reports (automatic)
K	U	A		AIREP/PIREP 	004/001	Single level aircraft reports (manual)
K	U	B		n/a		Single level balloon reports 
K	U	C		SAREP 	005/000	Single level satellite-derived reports
K	U	D		TEMP DROP	002/007	Dropsonde/dropwindsondes 
K	U	I		n/a	009/000	Dispersal and transport analysis 
K	U	J	01-19	PILOT 	002/001	Upper wind from fixed land stations (parts A, B, C and D)
K	U	J	20-39	PILOT MOBIL	002/003	Upper wind from mobile land stations (parts A, B, C and D)
K	U	J	40-59	PILOT SHIP	002/002	Upper wind from marine stations (parts A, B, C and D)
K	U	K	01-19	TEMP	002/004	Radio soundings from fixed land stations (parts A and B)
K	U	K	20-39	TEMP MOBIL 	002/006	Radio soundings from mobile land stations (parts A and B)
K	U	K	40-59	TEMP SHIP	002/005	Radio soundings from marine stations (parts A and B)
K	U	L		n/a 	008/001	Ozone vertical profile 
K	U	M				Model derived sondes
K	U	N				Rocketsondes
K	U	O		AMDAR 	002/020	Profiles of aircraft observations in ascending /descending
K	U	P		PILOT 	002/010	Profilers 
K	U	Q		TEMP 	002/011	RASS temperature profilers 
K	U	S	01-19	TEMP 	002/004	Radiosondes/pibal reports from fixed land stations (parts A, B, C and D)
K	U	S	20-39	TEMP MOBIL	002/006	Radio soundings from mobile land stations (parts A, B, C and D)
K	U	S	40-59	TEMP SHIP	002/005	Radio soundings from marine stations (parts A, B, C and D)
K	U	T				Satellite derived sondes
K	U	U	01-45	CLIMAT TEMP	002/025	Monthly statistics of data from upper-air stations
K	U	U	01-45	CLIMAT TEMP	002/025	Monthly statistics of data from upper-air stations
K	U	U	46-59	CLIMAT TEMP, SHIP	002/026	Monthly statistics of data from marine stations
K	U	W	01-19	PILOT 	002/001	Upper wind from fixed land stations (parts A and B)
K	U	W	20-39	PILOT MOBIL 	002/003	Upper wind from mobile land stations (parts A and B)
K	U	W	40-59	PILOT SHIP 	002/002	Upper wind from marine stations (parts A and B)
K	U	X				Other upper air reports
K	V	A				Forecast at single levels
K	V	B				Coded SIGWX, Embedded Cumulonimbus
K	V	C				CREX coded SIGWX, Clear air turbulence
K	V	F				CREX coded SIGWX, Fronts
K	V	N				CREX coded SIGWX, Other SIGWX parameters
K	V	O				CREX coded SIGWX, Turbulence
K	V	S				Forecast soundings
K	V	T				CREX coded SIGWX, Icing/Tropopause
K	V	V				CREX coded SIGWX, Tropical storms, sandstorms, volcanoes
K	V	W				CREX coded SIGWX, High-level winds
K	V	X				Other upper air forecasts
F	A	?	01-49	FM 53 (ARFOR)/[text]		Aviation area /advisories
F	A	?	50-59	[TEXT]		GAMET
U	A	?	01-59	ICAO AIREP		Routine aircraft reports
U	A	?	60-69	ICAO AIREP		Special aircraft reports, except for volcanic ash
U	A	?	70-79	ICAO AIREP		Special aircraft reports related to volcanic ash
EOT

# source: http://isithackday.com/geoplanet-explorer/index.php?woeid=23424975

TABLE_C1 = <<EOT
A1A2	SNWE	AREA
AB	-	Albania
AG	-	Argentina
AH	25,40,60,75	Afghanistan
AI	-	Ascension Island
AJ	-	Azerbaijan, Republic of
AK	50,75,-180,-125	Alaska
AL	-	Algeria
AN	-	Angola
AT	-	Antigua and Barbuda, Saint Kitts and other British Islands in the vicinity
AU	-55,-5,110,160	Australia
AY	-	Armenia
AZ	-	Azores
BA	-	Bahamas
BC	-	Botswana
BD	-	Brunei Darussalam
BE	-	Bermuda
BH	-	Belize
BI	-	Burundi
BJ	-	Benin
BK	-	Banks Islands
BM	-	Myanmar
BN	-	Bahrain
BO	-	Bolivia
BR	-	Barbados
BU	-	Bulgaria
BV	-	Bouvet Island
BW	-	Bangladesh
BX	-	Belgium, Luxembourg
BY	-	Belarus
BZ	-	Brazil
CD	-	Chad
CE	-	Central African Republic
CG	-	Congo
CH	-	Chile
CI	-	China
CM	-	Cameroon
CN	-	Canada
CO	-	Colombia
CR	-	Canary Islands (Spain)
CS	-	Costa Rica
CT	-	Canton Island
CU	-	Cuba
CV	-	Cape Verde
CY	-	Cyprus
CZ	-	Czech Republic
DJ	10,15,40,45	Djibouti
DL	45,60,5,20	Germany
DN	50,60,5,20	Denmark
DO	-	Dominica
DR	-	Dominican Republic
EG	-	Egypt
EI	-	Erithrea
EO	55,60,20,30	Estonia
EQ	-	Ecuador
ER	-	United Arab Emirates
ES	-	El Salvador
ET	-	Ethiopia
FA	-	Faroe Islands
FG	-	French Guiana
FI	55,75,20,35	Finland
FJ	-25,-10,170,-175	Fiji
FK	-	Falkland Island (Malvinas)
FP	-	Saint Pierre and Miquelon
FR	-	France
FW	-	Wallis and Futuna Islands
GB	-	Gambia
GC	-	Cayman Islands
GD	-	Grenada
GE	-	Gough Island
GG	-	Georgia
GH	-	Ghana
GI	-	Gibraltar
GL	-	Greenland
GM	-	Guam
GN	-	Guinea
GO	-	Gabon
GQ	-	Equatorial Guinea
GR	-	Greece
GU	-	Guatemala
GW	-	Guinea-Bissau
GY	-	Guyana
HA	-	Haiti
HE	-	St. Helena
HK	-	Hong Kong, China
HO	-	Honduras
HU	45,50,15,25	Hungary
HV	-	Burkina Faso
HW	-	Hawaiian Islands
IC	-	Comoros
ID	-15,10,90,145	Indonesia
IE	-	Ireland
IL	-	Iceland
IN	-	India
IQ	-	Iraq
IR	-	Iran, Islamic Republic of
IS	25,35,30,40	Israel
IV	-	Cote d'Ivoire
IY	-	Italy
JD	-	Jordan
JM	-	Jamaica
JP	-	Japan
KA	-	Caroline Islands (Federated State of Micronesia) 
KB	-15,5,155,-150	Kiribati
KI	-	Christmas Island
KK	-	Cocos Islands
KN	-	Kenya
KO	-	Korea, Republic of
KP	-	Cambodia
KR	-	Democratic People's Republic of Korea
KU	-25,-5,-170,-155	Cook Island
KW	-	Kuwait
KY	-	Kyrgyzstan
KZ	-	Kazakhstan
LA	10,25,100,110	Lao People's Democratic Republic
LB	-	Lebanon
LC	-	Saint Lucia
LI	-	Liberia
LJ	-	Slovenia
LN	-	Southern Line Islands
LS	-	Lesotho
LT	50,60,20,30	Lithuania
LV	55,60,20,30	Latvia
LY	-	Libyan Arab Jamahiriya
MA	-	Mauritius
MB	-	Marion Island
MC	-	Morocco
MD	-	Madeira
MF	-	Saint-Martin, Saint-Barthelemy, Guadeloupe and other French islands in the vicinity
MG	-	Madagascar
MH	-	Marshall Islands
MI	-	Mali
MJ	-	Former Yugoslov Republic of Macedonia
MK	-	Montenegro
ML	-	Malta
MN	-	St. Maarten, St.Eustatius and Saba
MO	-	Mongolia
MR	-	Martinique
MS	0,10,95,120	Malaysia
MT	-	Mauritania
MU	-	Macao, China 
MV	-	Maldives Islands
MW	-	Malawi
MX	-	Mexico
MY	-	Mariana Islands
MZ	-	Mozambique
NC	-	New Caledonia
NG	-	Papua New Guinea
NI	-	Nigeria
NK	-	Nicaragua
NL	-	Netherlands
NM	-	Namibia
NO	55,75,0,35	Norway
NP	-	Nepal
NR	-	Niger
NU	-	Netherlands Antilles (Bonaire, Curacao) & Aruba 
NV	-	Vanuatu
NW	-	Nauru Island
NZ	-55,-25,165,-175	New Zealand
OM	-	Oman
OR	-	South Orkney Islands
OS	45,50,5,20	Austria
PF	-	French Polynesia
PH	-	Philippines
PI	-	Phoenix Islands
PK	-	Pakistan
PL	-	Poland
PM	-	Panama
PO	-	Portugal
PP	-	Republic of Palau 
PR	-	Peru
PT	-	Pitcairn
PU	-	Puerto Rico
PY	-	Paraguay
QB	-	Bosnia and Herzegovina
QT	-	Qatar
RA	-	Russian Federation (East)
RE	-25,-20,55,60	Reunion and associated Islands
RH	40,50,10,20	Croatia
RM	-	Republic of Moldova
RO	-	Romania
RS	-	Russian Federation (West)
RW	-	Rwanda
SB	-	Sri Lanka
SC	-	Seychelles
SD	-	Saudi Aabia
SG	10,20,-20,-10	Senegal
SI	-	Somalia
SK	-	Sarawak
SL	-	Sierra Leone
SM	-	Suriname
SN	55,70,10,25	Sweden
SO	-	Solomon Islands
SP	-	Spain
SQ	45,50,15,25	Slovakia
SR	0,5,100,105	Singapore
SU	-	Sudan
SV	-	Swaziland
SW	45,50,5,15	Switzerland
SX	-	Santa Cruz Islands
SY	-	Syrian Arab Republic
SZ	-	Spitzbergen Islands
TA	-	Tajikistan
TC	-	Tristan da Cunha
TD	-	Trinidad and Tobago
TG	-	Togo
TH	-	Thailand
TI	-	Turks and Caicos Islands
TK	-	Tokelau
TM	-	Timor-Leste
TN	-	Tanzania, United Republic of
TO	-	Tonga
TP	-	Sao Tome and Principe
TR	-	Turkmenistan
TS	-	Tunisia
TU	-	Turkey
TV	-	Tuvalu
UG	-	Uganda
UK	-	United Kingdom of Great Britain and Northern Ireland
UR	-	Ukraine
US	-	United States of America
UY	-	Uruguay
UZ	-	Uzbekistan
VG	-	Saint Vincent and the Grenadines
VI	-	Virgin Islands
VN	-	Venezuela
VS	-	Vietnam
YE	-	Yemen
YG	-	Serbia
ZA	-	South Africa
ZB	-	Zambia
ZM	-15,-10,-175,-170	Western Samoa
ZR	-	Democratic Republic of the Congo 
ZW	-	Zimbabwe
AA	-90,-60,-180,180	Antarctic
AC	-	Arctic
AE	-	South-East Asia
AF	-50,40,-30,65	Africa
AM	-	Central Africa
AO	-	West Africa
AP	-	Southern Africa
AS	-	Asia
AW	-	Near East
AX	-	Arabian Sea area
BQ	-	Baltic Sea area
CA	-	Caribbean and Central America
EA	-	East Africa
EC	-	East China Sea area
EE	-	Eastern Europe
EM	-	Middle Europe
EN	-	Northern Europe
EU	-	Europe
EW	-	Western Europe
FE	-	Far East
GA	-	Gulf of Alaska area
GX	-	Gulf of Mexico area
IO	-60,25,20,150	Indian Ocean area
ME	-	Eastern Mediterranean area
MM	-	Mediterranean area
MP	-	Central Mediterranean area
MQ	-	Western Mediterranean area
NA	-	North America
NT	-	North Atlantic area
OC	-	Oceania
OH	-	Sea of Okhotsk
PA	-60,60,105,-65	Pacific area
PE	-	Persian Gulf area
PN	0,60,105,-65	North Pacific area
PQ	0,60,105,180	Western North Pacific
PS	-60,0,105,-65	South Pacific area
PW	-60,60,105,180	Western Pacific area
PZ	-60,60,-180,-65	Eastern Pacific area
SA	-	South America
SE	-	Southern Ocean area
SJ	-	Sea of Japan area
SS	-	South China Sea area
ST	-	South Atlantic area
XE	-	Eastern hemisphere
XN	-	Northern hemisphere
XS	-	Southern hemisphere
XT	-	Tropical belt
XW	-	Western hemisphere
EOT

TABLE_C3 = <<EOT
A1	SNWE	AREA
A	0,90,-90,0	0° - 90°W northern hemisphere
B	0,90,-180,-90	90°W - 180° northern hemisphere
C	0,90,90,180	180° - 90°E northern hemisphere
D	0,90,0,90	90°E - 0° northern hemisphere
E	-30,30,-90,0	0° - 90°W tropical belt
F	-30,30,-180,-90	90°W - 180° tropical belt
G	-30,30,90,180	180° - 90°E tropical belt
H	-30,30,0,90	90°E - 0° tropical belt
I	-90,0,-90,0	0° - 90°W southern hemisphere
J	-90,0,-180,-90	90°W - 180° southern hemisphere
K	-90,0,90,180	180° - 90°E southern hemisphere
L	-90,0,0,90	90°E - 0° southern hemisphere
N	0,90,-180,180	Northern hemisphere
S	-90,0,-180,180	Southern hemisphere
T	0,90,-180,-45	45°W - 180° northern hemisphere
X	-90,90,-180,180	Global Area (area not definable)
EOT

TABLE_C4 = <<EOT
T1	A2	FT	RefTime
D,G,H,J,O,P,T	A	0	Analysis (00 hour)
D,G,H,J,O,P,T	B	6	6 hours forecast
D,G,H,J,O,P,T	C	12	12 hours forecast
D,G,H,J,O,P,T	D	18	18 hours forecast
D,G,H,J,O,P,T	E	24	24 hours forecast
D,G,H,J,O,P,T	F	30	30 hours forecast
D,G,H,J,O,P,T	G	36	36 hours forecast
D,G,H,J,O,P,T	H	42	42 hours forecast
D,G,H,J,O,P,T	I	48	48 hours forecast
D,G,H,J,O,P,T	J	60	60 hours forecast
D,G,H,J,O,P,T	K	72	72 hours forecast
D,G,H,J,O,P,T	L	84	84 hours forecast
D,G,H,J,O,P,T	M	96	96 hours forecast
D,G,H,J,O,P,T	N	108	108 hours forecast
D,G,H,J,O,P,T	O	120	120 hours forecast (5 days)
D,G,H,J,O,P,T	P	132	132 hours forecast
D,G,H,J,O,P,T	Q	144	144 hours forecast
D,G,H,J,O,P,T	R	156	156 hours forecast (7 days)
D,G,H,J,O,P,T	S	168	168 hours forecast
D,G,H,J,O,P,T	T	240	10 days forecast
D,G,H,J,O,P,T	U	360	15 days forecast
D,G,H,J,O,P,T	V	720	30 days forecast
Q,X,Y	A	0	Analysis (00 hour)
Q,X,Y	B	3	3 hours forecast
Q,X,Y	C	6	6 hours forecast
Q,X,Y	D	9	9 hours forecast
Q,X,Y	E	12	12 hours forecast
Q,X,Y	F	15	15 hours forecast
Q,X,Y	G	18	18 hours forecast
Q,X,Y	H	21	21 hours forecast
Q,X,Y	I	24	24 hours forecast
Q,X,Y	J	27	27 hours forecast
Q,X,Y	K	30	30 hours forecast
Q,X,Y	L	33	33 hours forecast
Q,X,Y	M	36	36 hours forecast
Q,X,Y	N	39	39 hours forecast
Q,X,Y	O	42	42 hours forecast
Q,X,Y	P	45	45 hours forecast
Q,X,Y	Q	48	48 hours forecast
EOT

TABLE_D1 = <<EOT
T1	ii	Level
O	98	Surface
O	96	2.5 m
O	94	5.0 m
O	92	7.5 m
O	90	12.5 m
O	88	17.5 m
O	86	25.0 m
O	84	32.5 m
O	82	40.0 m
O	80	50.0 m
O	78	62.5 m
O	76	75.0 m
O	74	100 m
O	72	125 m
O	70	150 m
O	68	200 m
O	66	300 m
O	64	400 m
O	62	500 m
O	60	600 m
O	58	700 m
O	56	800 m
O	54	900 m
O	52	1000 m
O	50	1100 m
O	48	1200 m
O	46	1300 m
O	44	1400 m
O	42	1500 m
O	40	1750 m
O	38	2000 m
O	36	2500 m
O	34	3000 m
O	32	4000 m
O	30	5000 m
O	01	Primary layer depth
D,G,H,J,P,Q,X,Y	99	1000 hPa
D,G,H,J,P,Q,X,Y	98	Air priorities for the Earth's surface
D,G,H,J,P,Q,X,Y	97	Level of the tropopause
D,G,H,J,P,Q,X,Y	96	Level of maximum wind
D,G,H,J,P,Q,X,Y	95	950 hPa
D,G,H,J,P,Q,X,Y	94	Level of 0 Degrees C isotherm
D,G,H,J,P,Q,X,Y	93	975 hPa
D,G,H,J,P,Q,X,Y	92	925 hPa
D,G,H,J,P,Q,X,Y	91	875 hPa
D,G,H,J,P,Q,X,Y	90	900 hPa
D,G,H,J,P,Q,X,Y	89	Reduced sea level
D,G,H,J,P,Q,X,Y	88	Ground or water properties for the Earth's surface
D,G,H,J,P,Q,X,Y	87	1000-500 hPa thickness
D,G,H,J,P,Q,X,Y	86	Boundary level
D,G,H,J,P,Q,X,Y	85	850 hPa
D,G,H,J,P,Q,X,Y	84	840 hPa
D,G,H,J,P,Q,X,Y	83	830 hPa
D,G,H,J,P,Q,X,Y	82	825 hPa
D,G,H,J,P,Q,X,Y	81	810 hPa
D,G,H,J,P,Q,X,Y	80	800 hPa
D,G,H,J,P,Q,X,Y	79	790 hPa
D,G,H,J,P,Q,X,Y	78	780 hPa
D,G,H,J,P,Q,X,Y	77	775 hPa
D,G,H,J,P,Q,X,Y	76	760 hPa
D,G,H,J,P,Q,X,Y	75	750 hPa
D,G,H,J,P,Q,X,Y	74	740 hPa
D,G,H,J,P,Q,X,Y	73	730 hPa
D,G,H,J,P,Q,X,Y	72	725 hPa
D,G,H,J,P,Q,X,Y	71	710 hPa
D,G,H,J,P,Q,X,Y	70	700 hPa
D,G,H,J,P,Q,X,Y	69	690 hPa
D,G,H,J,P,Q,X,Y	68	680 hPa
D,G,H,J,P,Q,X,Y	67	675 hPa
D,G,H,J,P,Q,X,Y	66	660 hPa
D,G,H,J,P,Q,X,Y	65	650 hPa
D,G,H,J,P,Q,X,Y	64	640 hPa
D,G,H,J,P,Q,X,Y	63	630 hPa
D,G,H,J,P,Q,X,Y	62	625 hPa
D,G,H,J,P,Q,X,Y	61	610 hPa
D,G,H,J,P,Q,X,Y	60	600 hPa
D,G,H,J,P,Q,X,Y	59	590 hPa
D,G,H,J,P,Q,X,Y	58	580 hPa
D,G,H,J,P,Q,X,Y	57	570 hPa
D,G,H,J,P,Q,X,Y	56	560 hPa
D,G,H,J,P,Q,X,Y	55	550 hPa
D,G,H,J,P,Q,X,Y	54	540 hPa
D,G,H,J,P,Q,X,Y	53	530 hPa
D,G,H,J,P,Q,X,Y	52	520 hPa
D,G,H,J,P,Q,X,Y	51	510 hPa
D,G,H,J,P,Q,X,Y	50	500 hPa
D,G,H,J,P,Q,X,Y	49	490 hPa
D,G,H,J,P,Q,X,Y	48	480 hPa
D,G,H,J,P,Q,X,Y	47	470 hPa
D,G,H,J,P,Q,X,Y	46	460 hPa
D,G,H,J,P,Q,X,Y	45	450 hPa
D,G,H,J,P,Q,X,Y	44	440 hPa
D,G,H,J,P,Q,X,Y	43	430 hPa
D,G,H,J,P,Q,X,Y	42	420 hPa
D,G,H,J,P,Q,X,Y	41	410 hPa
D,G,H,J,P,Q,X,Y	40	400 hPa
D,G,H,J,P,Q,X,Y	39	390 hPa
D,G,H,J,P,Q,X,Y	38	380 hPa
D,G,H,J,P,Q,X,Y	37	370 hPa
D,G,H,J,P,Q,X,Y	36	360 hPa
D,G,H,J,P,Q,X,Y	35	350 hPa
D,G,H,J,P,Q,X,Y	34	340 hPa
D,G,H,J,P,Q,X,Y	33	330 hPa
D,G,H,J,P,Q,X,Y	32	320 hPa
D,G,H,J,P,Q,X,Y	31	310 hPa
D,G,H,J,P,Q,X,Y	30	300 hPa
D,G,H,J,P,Q,X,Y	29	290 hPa
D,G,H,J,P,Q,X,Y	28	280 hPa
D,G,H,J,P,Q,X,Y	27	270 hPa
D,G,H,J,P,Q,X,Y	26	260 hPa
D,G,H,J,P,Q,X,Y	25	250 hPa
D,G,H,J,P,Q,X,Y	24	240 hPa
D,G,H,J,P,Q,X,Y	23	230 hPa
D,G,H,J,P,Q,X,Y	22	220 hPa
D,G,H,J,P,Q,X,Y	21	210 hPa
D,G,H,J,P,Q,X,Y	20	200 hPa
D,G,H,J,P,Q,X,Y	19	190 hPa
D,G,H,J,P,Q,X,Y	18	180 hPa
D,G,H,J,P,Q,X,Y	17	170 hPa
D,G,H,J,P,Q,X,Y	16	160 hPa
D,G,H,J,P,Q,X,Y	15	150 hPa
D,G,H,J,P,Q,X,Y	14	140 hPa
D,G,H,J,P,Q,X,Y	13	130 hPa
D,G,H,J,P,Q,X,Y	12	120 hPa
D,G,H,J,P,Q,X,Y	11	110 hPa
D,G,H,J,P,Q,X,Y	10	100 hPa
D,G,H,J,P,Q,X,Y	09	90 hPa
D,G,H,J,P,Q,X,Y	08	80 hPa
D,G,H,J,P,Q,X,Y	07	70 hPa
D,G,H,J,P,Q,X,Y	06	60 hPa
D,G,H,J,P,Q,X,Y	05	50 hPa
D,G,H,J,P,Q,X,Y	04	40 hPa
D,G,H,J,P,Q,X,Y	03	30 hPa
D,G,H,J,P,Q,X,Y	02	20 hPa
D,G,H,J,P,Q,X,Y	01	10 hPa
D,G,H,J,P,Q,X,Y	00	Entire atmosphere
EOT

def tsvsplit str
  title = nil
  for line in str
    words = line.chomp.split(/\t/)
    if title.nil? then
      title = words
    else
      yield Hash[*title.zip(words).flatten]
    end
  end
end

def initialize
  @db = SQLite3::Database.new('wmo9.sqlite')
end

def datatype_t1 t1
  r = @db.execute(<<-ENDSQL, t1)
    SELECT text FROM datatype
    WHERE t1 = ? AND t2 = '?' AND a1 = '?'
  ENDSQL
  r.flatten.first
end

def datatype
  @db.execute_batch(<<-ENDSQL)
    CREATE TABLE IF NOT EXISTS datatype(
      t1	CHAR(1) NOT NULL,
      t2	CHAR(1) NOT NULL,
      a1	CHAR(1) NOT NULL,
      iimin	INTEGER NOT NULL DEFAULT(0),
      iimax	INTEGER NOT NULL DEFAULT(99),
      text	STRING NOT NULL,
      CONSTRAINT tta UNIQUE (t1, t2, a1, iimin, iimax),
      CONSTRAINT ii CHECK (iimin >= 0 AND iimax <= 99 AND iimin <= iimax)
    );
  ENDSQL
  tsvsplit(TABLE_A) do |row|
    next if row['DataType'] == '-'
    @db.execute(<<-ENDSQL, row['T1'], row['DataType'])
      REPLACE INTO datatype(t1, t2, a1, text)
      VALUES(?, '?', '?', ?)
    ENDSQL
  end
  tsvsplit(TABLE_B1) do |row|
    next if /^#/ =~ row['T1']
    dtype = row['DataType']
    next if dtype == '-'
    dtype = "#{datatype_t1(row['T1'])} - #{dtype}"
    ('A'..'Z').each { |a1|
      @db.execute(<<-ENDSQL, row['T1'], row['T2'], a1, dtype)
	REPLACE INTO datatype(t1, t2, a1, text) VALUES(?, ?, ?, ?)
      ENDSQL
    }
  end
  tsvsplit(TABLE_B2) do |row|
    next if /^-$/ =~ row['DataType']
    next if /^#/ =~ row['T1']
    for t1 in row['T1'].split(/,\s*/)
      dtype = "#{datatype_t1(t1)} - #{row['DataType']}"
      ('A'..'Z').each do |a1|
	@db.execute(<<-ENDSQL, t1, row['T2'], a1, dtype)
	  REPLACE INTO datatype(t1, t2, a1, text) VALUES(?, ?, ?, ?)
	ENDSQL
      end
    end
  end
  tsvsplit(TABLE_C6) do |row|
    next if /^-$/ =~ row['DataType']
    next if /^#/ =~ row['T1']
    t1 = row['T1']
    a1a = case row['A1']
    when '?'
      'A'..'Z'
    else
      [row['A1']]
    end
    dtype = "#{datatype_t1(t1)} - #{row['DataType']}"
    case row['ii']
    when /(\d+)-(\d+)/
      iimin, iimax = $1.to_i, $2.to_i
    else
      iimin, iimax = 0, 99
    end
    for a1 in a1a
      @db.execute(<<-ENDSQL, t1, row['T2'], a1, iimin, iimax, dtype)
	REPLACE INTO datatype(t1, t2, a1, iimin, iimax, text)
	VALUES(?, ?, ?, ?, ?, ?)
      ENDSQL
    end
  end
end

def place
  @db.execute_batch(<<-ENDSQL)
    CREATE TABLE IF NOT EXISTS place(
      t1	CHAR(1) NOT NULL,
      a1	CHAR(1) NOT NULL,
      a2	CHAR(1) NOT NULL,
      southbc	FLOAT,
      northbc	FLOAT,
      westbc	FLOAT,
      eastbc	FLOAT,
      text	STRING NOT NULL,
      CONSTRAINT taa UNIQUE (t1, a1, a2)
    );
  ENDSQL
  tsvsplit(TABLE_C1) do |row|
    a1 = row['A1A2'][0,1]
    a2 = row['A1A2'][1,1]
    if row['SNWE'] != '-'
      sb, nb, wb, eb = row['SNWE'].split(/,/).map{|s| s.to_f}
    else
      sb, nb, wb, eb = nil
    end
    text = row['AREA']
    %w(A C E F N S U V W).each do |t1|
      @db.execute(<<-ENDSQL, t1, a1, a2, sb, nb, wb, eb, text)
	REPLACE INTO place(t1, a1, a2, southbc, northbc, westbc, eastbc, text)
	VALUES(?, ?, ?, ?, ?, ?, ?, ?)
      ENDSQL
    end
  end
  tsvsplit(TABLE_C3) do |row|
    a1 = row['A1']
    sb, nb, wb, eb = row['SNWE'].split(/,/).map{|s| s.to_f}
    text = row['AREA']
    %w(D G H O P Q T X Y).each do |t1|
      @db.execute(<<-ENDSQL, t1, a1, sb, nb, wb, eb, text)
	REPLACE INTO place(t1, a1, a2, southbc, northbc, westbc, eastbc, text)
	VALUES(?, ?, '?', ?, ?, ?, ?, ?)
      ENDSQL
    end
    %w(I K).each do |t1|
      @db.execute(<<-ENDSQL, t1, a1, sb, nb, wb, eb, text)
	REPLACE INTO place(t1, a1, a2, southbc, northbc, westbc, eastbc, text)
	VALUES(?, '?', ?, ?, ?, ?, ?, ?)
      ENDSQL
    end
  end
end

def fcsttime
  @db.execute_batch(<<-ENDSQL)
    CREATE TABLE IF NOT EXISTS fcsttime(
      t1	CHAR(1) NOT NULL,
      a2	CHAR(1) NOT NULL,
      fcsthour	FLOAT,
      text	STRING NOT NULL,
      CONSTRAINT taa UNIQUE (t1, a2)
    );
  ENDSQL
  tsvsplit(TABLE_C4) do |row|
    a2 = row['A2']
    ft = row['FT'].to_f
    text = row['RefTime']
    for t1 in row['T1'].split(/,\s*/)
      @db.execute(<<-ENDSQL, t1, a2, ft, text)
	REPLACE INTO fcsttime(t1, a2, fcsthour, text)
	VALUES(?, ?, ?, ?)
      ENDSQL
    end
  end
end

def level
  @db.execute_batch(<<-ENDSQL)
    CREATE TABLE IF NOT EXISTS level(
      t1	CHAR(1) NOT NULL,
      ii	CHAR(2) NOT NULL,
      text	STRING NOT NULL,
      CONSTRAINT tii UNIQUE (t1, ii)
    );
  ENDSQL
  tsvsplit(TABLE_D1) do |row|
    ii = row['ii']
    text = row['Level']
    for t1 in row['T1'].split(/,\s*/)
      @db.execute(<<-ENDSQL, t1, ii, text)
	REPLACE INTO level(t1, ii, text)
	VALUES(?, ?, ?)
      ENDSQL
    end
  end
end

def run
  @db.transaction do
    datatype
    place
    fcsttime
    level
  end
  @db.close
end

end #class

App.new.run