CREATE TABLE dataset.meta (
	id serial PRIMARY KEY,
	namespace NOT NULL text DEFAULT '',  --  empty namespace is the main one, like 'public'
	name text NOT NULL, -- original dataset name or filename of the CSV

  is_canonic BOOLEAN DEFAULT false, -- for canonic or "reference datasets"
  sametypes_as text,  -- pointing to a kx_urn of its is_canonic-dataset, need same kx_types. For merge or UNION
	projection_of text, -- pointing to a kx_urn of its is_canonic-dataset, need map same(kx_name->kx_types).

	-- Cache fields generated by UPDATE or trigger.
	kx_uname text, -- the normalized name, used for  dataset.meta_id() and SQL-View labels
	kx_urn text,   -- the transparent ID for this dataset.  "$namespace:$kx_uname".
	kx_fields text[], -- field names as in info.
	kx_types text[],  -- field JSON-datatypes as in info.

	info JSONb -- all metadata (information) here!
);
CREATE TABLE dataset.big (
  id bigserial not null primary key,
  source int NOT NULL REFERENCES dataset.meta(id) ON DELETE CASCADE, -- Dataset ID and metadata.
  key text,  -- Optional. Dataset primary key (converted to text).
  c JSONb CHECK(jsonb_array_length(c)>0), -- All dataset columns here, as exact copy of CSV line!
  UNIQUE(source,key)
);

CREATE FOREIGN TABLE tmpcsv_language_codes (
 alpha2 text,
 english text
 ) SERVER csv_files OPTIONS (
    filename '/tmp/tmpcsv/language_codes.csv',
    format 'csv',
    header 'true'
 );

INSERT INTO dataset.meta(name,info) VALUES
 ('language_codes','{"name":"language-codes","path":"data/language-codes.csv","mediaty
pe":"text/csv","schema":{"fields":[{"name":"alpha2","description":"2 letter alpha-2 code","type":"string"},{"name":"English","description":"Eng
lish name of language","type":"string"}]}}'::jsonb)
;
INSERT INTO dataset.big(source, c)
  SELECT dataset.meta_id('language_codes') , jsonb_build_array( alpha2, english   )
  FROM tmpcsv_language_codes
;

CREATE VIEW dataset.vw_language_codes AS
  SELECT (c->>0)::text AS alpha2, (c->>1)::text AS english
  FROM dataset.big
  WHERE source=dataset.meta_id('language_codes') ORDER BY id;

SELECT jsonb_pretty(jsonb_agg(jmeta_fields))
FROM dataset.vjmeta_fields WHERE dataset_id IN (1,3)

SELECT i.lang, i.defs, substring(l.english,1,30) as lang_name, c.official_name_en as contry_name
FROM dataset.vw_ietf_language_tags i INNER JOIN dataset.vw_country_codes c
  ON c.iso3166_1_alpha_2=i.territory INNER JOIN dataset.vw_language_codes l
  ON i.langtype=l.alpha2
ORDER BY 1;