-
Notifications
You must be signed in to change notification settings - Fork 11
/
usda_nndsr_abbr_mysql.sql
89 lines (83 loc) · 5.58 KB
/
usda_nndsr_abbr_mysql.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- ===============================================================================================================================
-- USDA National Nutrient Database for Standard Reference (Abbreviated Version), Release 27 (http://www.ars.usda.gov/ba/bhnrc/ndl)
-- This file was generated by http://github.com/m5n/nutriana
-- Run this SQL with an account that has admin priviledges, e.g.: mysql --local_infile=1 -v -u root < file.sql
-- ===============================================================================================================================
drop database if exists usda_nndsr_abbr;
create database usda_nndsr_abbr;
use usda_nndsr_abbr;
grant all on usda_nndsr_abbr.* to 'food'@'localhost' identified by 'food';
-- Abbreviated
create table ABBREV (
NDB_No varchar(5) not null, -- 5-digit Nutrient Databank number.
Shrt_Desc varchar(60), -- 60-character abbreviated description of food item. (For a 200-character description and other descriptive information, link to the Food Description file.)
Water dec(10, 2) unsigned, -- Water (g/100 g)
Energ_Kcal bigint(10) unsigned, -- Food energy (kcal/100 g)
Protein dec(10, 2) unsigned, -- Protein (g/100 g)
Lipid_Tot dec(10, 2) unsigned, -- Total lipid (fat)(g/100 g)
Ash dec(10, 2) unsigned, -- Ash (g/100 g)
Carbohydrt dec(10, 2) unsigned, -- Carbohydrate, by difference (g/100 g)
Fiber_TD dec(10, 1) unsigned, -- Total dietary fiber (g/100 g)
Sugar_Tot dec(10, 2) unsigned, -- Total sugars (g/100 g)
Calcium bigint(10) unsigned, -- Calcium (mg/100 g)
Iron dec(10, 2) unsigned, -- Iron (mg/100 g)
Magnesium bigint(10) unsigned, -- Magnesium (mg/100 g)
Phosphorus bigint(10) unsigned, -- Phosphorus (mg/100 g)
Potassium bigint(10) unsigned, -- Potassium (mg/100 g)
Sodium bigint(10) unsigned, -- Sodium (mg/100 g)
Zinc dec(10, 2) unsigned, -- Zinc (mg/100 g)
Copper dec(10, 3) unsigned, -- Copper (mg/100 g)
Manganese dec(10, 3) unsigned, -- Manganese (mg/100 g)
Selenium dec(10, 1) unsigned, -- Selenium (mcg/100 g)
Vit_C dec(10, 1) unsigned, -- Vitamin C (mg/100 g)
Thiamin dec(10, 3) unsigned, -- Thiamin (mg/100 g)
Riboflavin dec(10, 3) unsigned, -- Riboflavin (mg/100 g)
Niacin dec(10, 3) unsigned, -- Niacin (mg/100 g)
Panto_acid dec(10, 3) unsigned, -- Pantothenic acid (mg/100 g)
Vit_B6 dec(10, 3) unsigned, -- Vitamin B6 (mg/100 g)
Folate_Tot bigint(10) unsigned, -- Folate, total (mcg/100 g)
Folic_acid bigint(10) unsigned, -- Folic acid (mcg/100 g)
Food_Folate bigint(10) unsigned, -- Food folate (mcg/100 g)
Folate_DFE bigint(10) unsigned, -- Folate (mcg dietary folate equivalents/100 g)
Choline_Tot bigint(10) unsigned, -- Choline, total (mg/100 g)
Vit_B12 dec(10, 2) unsigned, -- Vitamin B12 (mcg/100 g)
Vit_A_IU bigint(10) unsigned, -- Vitamin A (IU/100 g)
Vit_A_RAE bigint(10) unsigned, -- Vitamin A (mcg retinol activity equivalents/100g)
Retinol bigint(10) unsigned, -- Retinol (mcg/100 g)
Alpha_Carot bigint(10) unsigned, -- Alpha-carotene (mcg/100 g)
Beta_Carot bigint(10) unsigned, -- Beta-carotene (mcg/100 g)
Beta_Crypt bigint(10) unsigned, -- Beta-cryptoxanthin (mcg/100 g)
Lycopene bigint(10) unsigned, -- Lycopene (mcg/100 g)
Lut_Zea bigint(10) unsigned, -- Lutein+zeazanthin (mcg/100 g)
Vit_E dec(10, 2) unsigned, -- Vitamin E (alpha-tocopherol) (mg/100 g)
Vit_D_mcg dec(10, 1) unsigned, -- Vitamin D (mcg/100 g)
Vit_D_IU bigint(10) unsigned, -- Vitamin D (IU/100 g)
Vit_K dec(10, 1) unsigned, -- Vitamin K (phylloquinone) (mcg/100 g)
FA_Sat dec(10, 3) unsigned, -- Saturated fatty acid (g/100 g)
FA_Mono dec(10, 3) unsigned, -- Monounsaturated fatty acids (g/100 g)
FA_Poly dec(10, 3) unsigned, -- Polyunsaturated fatty acids (g/100 g)
Cholestrl dec(10, 3) unsigned, -- Cholesterol (mg/100 g)
GmWt_1 dec(9, 2) unsigned, -- First household weight for this item from the Weight file. (For the complete list and description of the measure, link to the Weight file.)
GmWt_Desc1 varchar(120), -- Description of household weight number 1.
GmWt_2 dec(9, 2) unsigned, -- Second household weight for this item from the Weight file. (For the complete list and description of the measure, link to the Weight file.)
GmWt_Desc2 varchar(120), -- Description of household weight number 2.
Refuse_Pct tinyint(2) unsigned -- Percent refuse. (For a description of refuse, link to the Food Description file.)
);
-- Load data into ABBREV
load data local infile './usda_nndsr_abbr/data/ABBREV.txt'
into table ABBREV
fields terminated by '^' optionally enclosed by '~'
lines terminated by '\r\n'
(NDB_No, Shrt_Desc, Water, Energ_Kcal, Protein, Lipid_Tot, Ash, Carbohydrt, Fiber_TD, Sugar_Tot, Calcium, Iron, Magnesium, Phosphorus, Potassium, Sodium, Zinc, Copper, Manganese, Selenium, Vit_C, Thiamin, Riboflavin, Niacin, Panto_acid, Vit_B6, Folate_Tot, Folic_acid, Food_Folate, Folate_DFE, Choline_Tot, Vit_B12, Vit_A_IU, Vit_A_RAE, Retinol, Alpha_Carot, Beta_Carot, Beta_Crypt, Lycopene, Lut_Zea, Vit_E, Vit_D_mcg, Vit_D_IU, Vit_K, FA_Sat, FA_Mono, FA_Poly, Cholestrl, GmWt_1, GmWt_Desc1, GmWt_2, GmWt_Desc2, Refuse_Pct)
;
-- Assert all 8618 records were loaded
create table tmp (c int unique key);
insert into tmp (c) values (2);
insert into tmp (select count(*) from ABBREV);
delete from tmp where c = 8618;
insert into tmp (select count(*) from tmp);
drop table tmp;
-- Correct data inconsistencies, if any
-- Add primary keys
alter table ABBREV add primary key (NDB_No);
-- Add foreign keys