-- MySQL commands to install the UMLS Lexical files to a MySQL database

-- This assumes that you will already have created the database and
-- connected to it, and that you invoke this as
--  \. mysql_lex_tables.sql
-- where this file resides in the directory that contains the LEX tables in relational form
-- and where you started mysql from that same directory.
--
-- We index most fields of most tables, to speed retrieval.  The
-- script below defines no indexes on tables SM, DM, NC, NRNUM, or
-- NRVAR; you may wish to define additional ones depending how you use
-- the database. Also, the MySQL types are determined empirically;
-- e.g., SCA (syntactic category seems to never be longer than 5
-- chars, and most entries are shorter than 256 chars, hence our
-- choice of representations as char(5) for SCA and varchar(255) for most
-- others.  These are not principled choices.  For fields that may be
-- empty, we follow the conventions in the META and SEM load scripts
-- and turn '' into NULL.

-- See http://www.ncbi.nlm.nih.gov/books/NBK9680/#ch06.I64_Lexicon_Relational_Tables

-- That documentation has a number of errors, discovered by trying to
-- use it as a basis for building these load scripts:

-- 1. In the definition of LRMOD, the FEA column is implied to be one
-- of the options of the PSN/MOD column in virtue of the typography
-- into a single table. In fact, it is a separate column.

-- 2. In the definition of LRPRP, the order of the SCA and STR columns
-- is interchanged. In the data, the columns are in the order EUI,
-- BAS, STR, SCA, FEA, contrary to what is documented.

-- 3. In the definition of LRABR, the names EUI and BAS are used
-- twice, once for the abbreviation, and once for the expansion. We
-- call the latter EUI2 and BAS2. Also, the order of the second BAS
-- and EUI is incorrect; the data has them in the order EUI2 followed
-- by BAS2.

-- 4. The documentation for SM.DB states that the data contains four
-- columns, but in reality it contains five. The first and second are
-- most often the same, but not always. For example, in the entry that
-- maps A.S.A. to aspirin, the first column is asa. We name the first
-- column TERM, and the others are as documented: TERM1, SCA1, TERM2,
-- SCA2.

-- 5. The table SP.DB, which is described in the documentation, does
-- not exist in the 2011AB distribution.  The files DM.DB, SM.DB and
-- NC.DB are in the LEX_DB subdirectory of LEX, which isn't mentioned.

-- 6. The NUMBERS subdirectory of LEX also contains |-delimited data
-- in NRVAR and NRNUM, but these are not described.  We load them,
-- with our own naming conventions.

-- 7. The MISC subdirectory of LEX also contains |-delimited
-- inflection data in the files inflection.table and inflVars.data,
-- but these are not described at all.  We do NOT load these because I
-- don't understand them.


create table LRAGR(
       EUI char(8) not null,
       STR varchar(255) not null,
       SCA char(5) not null,
       AGR varchar(255) not null,
       CIT varchar(255) not null,
       BAS varchar(255) not null);

load data local infile 'LRAGR' into table LRAGR fields terminated by '|' lines terminated by '\n';
create index LRAGR_EUI on LRAGR(EUI);
create index LRAGR_STR on LRAGR(STR);
create index LRAGR_SCA on LRAGR(SCA);


create table LRTYP(
       EUI char(8) not null,
       BAS varchar(255) not null,
       SCA char(5) not null,
       TYP varchar(255) not null);

load data local infile 'LRTYP' into table LRTYP fields terminated by '|' lines terminated by '\n';
create index LRTYP_EUI on LRTYP(EUI);
create index LRTYP_BAS on LRTYP(BAS);
create index LRTYP_SCA on LRTYP(SCA);


create table LRCMP(
       EUI char(8) not null,
       BAS varchar(255) not null,
       SCA char(5) not null,
       COM varchar(255) not null);

load data local infile 'LRCMP' into table LRCMP fields terminated by '|' lines terminated by '\n';
create index LRCMP_EUI on LRCMP(EUI);
create index LRCMP_BAS on LRCMP(BAS);
create index LRCMP_SCA on LRCMP(SCA);


create table LRPRN(
       EUI char(8) not null,
       BAS varchar(255) not null,
       AGR varchar(255) not null,
       GND varchar(255),
       CAS varchar(255),
       POS varchar(255),
       QNT varchar(255),
       FEA varchar(255));

load data local infile 'LRPRN' into table LRPRN fields terminated by '|' lines terminated by '\n'
(@eui, @bas, @agr, @gnd, @cas, @pos, @qnt, @fea)
SET EUI = @eui,
BAS = @bas,
AGR = @agr,
GND = NULLIF(@gnd,''),
CAS = NULLIF(@cas,''),
POS = NULLIF(@pos,''),
QNT = NULLIF(@qnt,''),
FEA = NULLIF(@fea,'');



create table LRMOD(
       EUI char(8) not null,
       BAS varchar(255) not null,
       SCA char(5) not null,
-- Note: in the documentation, this last field can be PSN/position, MOD/modification-type
-- We select PSN as the column name:
       PSN varchar(255) not null,
       FEA varchar(255));

load data local infile 'LRMOD' into table LRMOD fields terminated by '|' lines terminated by '\n'
(@eui, @bas, @sca, @psn, @fea)
SET EUI = @eui,
BAS = @bas,
SCA = @SCA,
PSN = @psn,
FEA = NULLIF(@fea,'');
create index LRMOD_EUI on LRMOD(EUI);
create index LRMOD_BAS on LRMOD(BAS);
create index LRMOD_SCA on LRMOD(SCA);
create index LRMOD_FEA on LRMOD(FEA);


create table LRPRP(
       EUI char(8) not null,
       BAS varchar(255) not null,
       STR varchar(255) not null,
       SCA char(5) not null,
       FEA varchar(255));

load data local infile 'LRPRP' into table LRPRP fields terminated by '|' lines terminated by '\n';
create index LRPRP_EUI on LRPRP(EUI);
create index LRPRP_BAS on LRPRP(BAS);
create index LRPRP_STR on LRPRP(STR);
create index LRPRP_SCA on LRPRP(SCA);
create index LRPRP_FEA on LRPRP(FEA);


create table LRABR(
       EUI char(8) not null,
       BAS varchar(255) not null,
       ABR varchar(255) not null,
       EUI2 char(8) not null,
       BAS2 varchar(255) not null);

load data local infile 'LRABR' into table LRABR fields terminated by '|' lines terminated by '\n';
create index LRABR_EUI on LRABR(EUI);
create index LRABR_BAS on LRABR(BAS);
create index LRABR_ABR on LRABR(ABR);
create index LRABR_EUI2 on LRABR(EUI2);
create index LRABR_BAS2 on LRABR(BAS2);


create table LRSPL(
       EUI char(8) not null,
       SPV varchar(255) not null,
       BAS varchar(255) not null);

load data local infile 'LRSPL' into table LRSPL fields terminated by '|' lines terminated by '\n';
create index LRSPL_EUI on LRSPL(EUI);
create index LRSPL_SPV on LRSPL(SPV);
create index LRSPL_BAS on LRSPL(BAS);


create table LRNOM(
       EUI char(8) not null,
       BAS varchar(255) not null,
       SCA char(5) not null,
       EUI2 char(8) not null,
       BAS2 varchar(255) not null,
       SCA2 char(5) not null);

load data local infile 'LRNOM' into table LRNOM fields terminated by '|' lines terminated by '\n';
create index LRNOM_EUI on LRNOM(EUI);
create index LRNOM_BAS on LRNOM(BAS);
create index LRNOM_SCA on LRNOM(SCA);
create index LRNOM_EUI2 on LRNOM(EUI2);
create index LRNOM_BAS2 on LRNOM(BAS2);
create index LRNOM_SCA2 on LRNOM(SCA2);


create table LRTRM(
       EUI char(8) not null,
       BAS varchar(255) not null,
-- Documentation claims that GENeric may not be present, but it always is.
       GEN varchar(255));

load data local infile 'LRTRM' into table LRTRM fields terminated by '|' lines terminated by '\n'
(@eui, @bas, @gen)
SET EUI = @eui,
BAS = @bas,
GEN = NULLIF(@gen,'');
create index LRTRM_EUI on LRTRM(EUI);
create index LRTRM_BAS on LRTRM(BAS);
create index LRTRM_GEN on LRTRM(GEN);


create table LRFIL(
       FIL varchar(255) not null,
       DES varchar(255) not null,
       FMT varchar(255) not null,
       CLS tinyint not null,
       RWS integer not null,
       BTR integer not null);

load data local infile 'LRFIL' into table LRFIL fields terminated by '|' lines terminated by '\n';


create table LRFLD (
       COL char(3) not null,
       DES varchar(255) not null,
       REF varchar(255) not null,
       FIL varchar(255) not null);

load data local infile "LRFLD" into table lrfld fields terminated by '|' lines terminated by '\n';


create table LRWD(
       WRD varchar(255) not null,
       EUI char(8) not null);

load data local infile 'LRWD' into table LRWD fields terminated by '|' lines terminated by '\n';
create index LRWD_WRD on LRWD(WRD);
create index LRWD_EUI on LRWD(EUI);


create table SM(
       TERM varchar(255) not null,
       TERM1 varchar(255) not null,
       SCA1 char(5) not null,
       TERM2 varchar(255) not null,
       SCA2 char(5) not null);

load data local infile 'LEX_DB/SM.DB' into table SM fields terminated by '|' lines terminated by '\n';


create table DM(
       TERM1 varchar(255) not null,
       SCA1 char(5) not null,
       TERM2 varchar(255) not null,
       SCA2 char(5) not null);

load data local infile 'LEX_DB/DM.DB' into table DM fields terminated by '|' lines terminated by '\n';


create table NC(
     MORPHEME varchar(255) not null,
     MEANING varchar(255) not null,
     ROOT varchar(255) not null);

load data local infile 'LEX_DB/NC.DB' into table NC fields terminated by '|' lines terminated by '\n';


create table NRNUM(
       NUI char(8) not null,
       STR varchar(255) not null,
       UNIT varchar(255) not null,
       VAL integer,
       BASE integer,
       MAG integer,
       AGR varchar(255));

load data local infile 'NUMBERS/NRNUM' into table NRNUM fields terminated by '|' lines terminated by '\n'
(@nui, @str, @unit, @val, @base, @mag, @agr)
SET NUI = @nui,
STR = @str,
UNIT = @unit,
VAL = NULLIF(@val,''),
BASE = NULLIF(@base,''),
MAG = NULLIF(@mag,''),
AGR = @agr;


create table NRVAR(
       STR varchar(255) not null,
       NUI char(8) not null,
       AGR varchar(255) not null,
       BAS varchar(255) not null);

load data local infile 'NUMBERS/NRVAR' into table NRVAR fields terminated by '|' lines terminated by '\n';
