iEat/defs/sql/postgres/load.sql

INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'African');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'American');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Chinese');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'French');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'German');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Greek');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Indian');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Italian');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Japanese');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Mexican');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Middle Eastern');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'None');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Slavic');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (nextval('hibernate_integer_seq'),'Thai');

INSERT INTO measurement_system (systemid,value_) VALUES (1,'USA');
INSERT INTO measurement_system (systemid,value_) VALUES (2,'Metric');
--INSERT INTO system (systemid,value_) VALUES (3,'Imperial');


INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'small','sm',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'medium','med',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'large','lg',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'unit','ea',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'slice','sli',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'clove','clv',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'pinch','pn',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'package','pk',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'can','cn',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'drop','dr',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'bunch','bn',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'dash','ds',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'carton','ct',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'sprig','sprig',null,0,0,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'leaf','leaf',null,0,0,FALSE);

-- US volumes
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Teaspoon','t',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),1,5,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Tablespoon','T',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),3,15,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Ounce (fluid)','fl. oz',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),6,29.56,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Cup','c',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),48,236,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Pint','pt',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),96,473,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Quart','q',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),192,946,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Gallon','gal',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),768,3785.4,FALSE);
		
-- US mass
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Ounce','oz',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),1,28.35,TRUE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'Pound','lb',
		(SELECT systemid FROM measurement_system WHERE value_ = 'USA'),16,454,TRUE);

-- Metric volumes	
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'milliliter','ml',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),0.203,1,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'cubic centimeter','cc',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),0.203,1,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'tea spoon','tsp',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),1.015,5,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'centiliter','cl',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),2.03,10,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'table spoon','Tbsp',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),3.045,15,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'deciliter','dl',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),20.3,100,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'cup','cup',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),50.75,250,FALSE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'liter','L',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),203,1000,FALSE);

-- Metric mass
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'milligram','mg',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),3.448275862E-5,1,TRUE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'decagram','dg',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),3.448275862E-4,10,TRUE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'hectogram','hg',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),3.448275862E-3,100,TRUE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'gram','g',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),3.448275862E-2,1000,TRUE);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,mass) VALUES (nextval('hibernate_integer_seq'),'kilogram','kg',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Metric'),34.48275862,1000000,TRUE);

		
/*
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Tablespoon','Tbsp',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Teaspoon','tsp',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Pound','lb',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Ounce','oz',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Pint','pt',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Quart','q',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric) VALUES (nextval('hibernate_integer_seq'),'Gallon','gal',
		(SELECT systemid FROM measurement_system WHERE value_ = 'Imperial'));
*/

update unit set toimperial = 0 where toimperial is null;
ALTER TABLE unit ALTER COLUMN tometric SET NOT NULL;
ALTER TABLE unit ALTER COLUMN tous SET NOT NULL;
ALTER TABLE unit ALTER COLUMN toimperial SET NOT NULL;

INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Beef');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Bread');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Dairy');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Egg');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Fruit');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Grain');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Lamb');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Other');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Pasta');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Pork/Ham');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Poultry');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Seafood');
INSERT INTO base (baseid,value_) VALUES (nextval('hibernate_integer_seq'),'Vegetable');


INSERT INTO preptime (preptimeid,value_) VALUES (nextval('hibernate_integer_seq'),'0 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (nextval('hibernate_integer_seq'),'1-10 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (nextval('hibernate_integer_seq'),'10-30 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (nextval('hibernate_integer_seq'),'30-60 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (nextval('hibernate_integer_seq'),'60+ Minutes');


INSERT INTO difficulty (difficultyid,value_) VALUES (nextval('hibernate_integer_seq'),'Easy');
INSERT INTO difficulty (difficultyid,value_) VALUES (nextval('hibernate_integer_seq'),'Intermediate');
INSERT INTO difficulty (difficultyid,value_) VALUES (nextval('hibernate_integer_seq'),'Difficult');
INSERT INTO difficulty (difficultyid,value_) VALUES (nextval('hibernate_integer_seq'),'Expert');


INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Breakfast');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Snack');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Lunch');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Appetizer');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Side Dish');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Entree');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Dessert');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'Beverage');
INSERT INTO course (courseid,value_) VALUES (nextval('hibernate_integer_seq'),'N/A');