db.txt

create table meta(
Run varchar(20),
AssayType varchar(50),
BioProject varchar(20),
BioSample varchar(20),
AvgSpotLen integer,
Bases bigint,
BioSampleModel varchar(50),
Bytes bigint,
CenterName varchar(100),
Experiment varchar(50),
geo_loc_name_country varchar(50),
geo_loc_name_country_continent varchar(50),
Host varchar(50),
Age varchar(100),
Sex varchar(100),
LibrarySource varchar(50),
Instrument varchar(50),
IsolationSource_EnvMedium varchar(50),
LibraryName varchar(100),
LibraryLayout varchar(50),
LibrarySelection varchar(20),
ReleaseDate varchar(50),
SampleName varchar(50),
SRAStudy varchar(50),
Organism varchar(100),
HostDisease varchar(100),
Description varchar(5000),
PMID varchar(20)
);

create table bioproject(
BioProject varchar(20),
SRA varchar(20),
Grp varchar(1000),
SubGroup varchar(5000),
IsolationSource varchar(100),
Biome varchar(60),
AssayType varchar(60),
ReleaseYear varchar(20),
RunCount integer,
PMID varchar(20),
DiseaseStudied varchar(100),
primary key (BioProject)
);

create table run(
Run varchar(20),
Experiment varchar(20),
BioSample varchar(20),
BioProject varchar(20),
AvgSpotLen integer,
Bases bigint,
Bytes bigint,
CenterName varchar(1000),
Country varchar(60),
Biome varchar(30),
IsolationSource varchar(100),
AssayType varchar(20),
Grp varchar(100),
SubGroup varchar(100),
Instrument varchar(100),
ReleaseDate varchar(30),
ReleaseYear integer,
Therapeutics varchar(1000),
primary key (Run),
constraint fk_bioproject foreign key (BioProject) references bioproject(BioProject),
constraint fk_subgroup foreign key (SubGroup) references disease(SubGroup)
);

create table disease(
SubGroup varchar(100),
Grp varchar(100),
primary key (SubGroup)
);

SET GLOBAL local_infile=1;
quit

sudo mysql --local-infile=1

use mdpd;

load data local infile '/home/sudipto/data/projects/mdpd/input/input.csv' into table meta ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mdpd/input/input_disease.csv' into table disease ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mdpd/input/input_bioproject.csv' into table bioproject ignore 1 lines;
load data local infile '/home/sudipto/data/projects/mdpd/input/input_run.csv' into table run ignore 1 lines;