iEat/defs/sql/derby/load.sql

INSERT INTO ethnicity (ethnicityid,value_) VALUES (1,'African');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (2,'American');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (3,'Chinese');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (4,'French');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (5,'German');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (6,'Greek');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (7,'Indian');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (8,'Italian');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (9,'Japanese');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (10,'Mexican');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (11,'Middle Eastern');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (12,'None');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (13,'Slavic');
INSERT INTO ethnicity (ethnicityid,value_) VALUES (14,'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,toimperial,mass) VALUES (1,'small','sm',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (2,'medium','med',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (3,'large','lg',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (4,'unit','ea',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (5,'slice','sli',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (6,'clove','clv',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (7,'pinch','pn',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (8,'package','pk',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (9,'can','cn',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (10,'drop','dr',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (11,'bunch','bn',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (12,'dash','ds',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (13,'carton','ct',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (14,'sprig','sprig',null,0,0,0);
INSERT INTO unit (unitid,value_,abbreviation,systemid,tous,tometric,toimperial,mass) VALUES (15,'leaf','leaf',null,0,0,0);

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

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

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

INSERT INTO base (baseid,value_) VALUES (1,'Beef');
INSERT INTO base (baseid,value_) VALUES (2,'Bread');
INSERT INTO base (baseid,value_) VALUES (3,'Dairy');
INSERT INTO base (baseid,value_) VALUES (4,'Egg');
INSERT INTO base (baseid,value_) VALUES (5,'Fruit');
INSERT INTO base (baseid,value_) VALUES (6,'Grain');
INSERT INTO base (baseid,value_) VALUES (7,'Lamb');
INSERT INTO base (baseid,value_) VALUES (8,'Other');
INSERT INTO base (baseid,value_) VALUES (9,'Pasta');
INSERT INTO base (baseid,value_) VALUES (10,'Pork/Ham');
INSERT INTO base (baseid,value_) VALUES (11,'Poultry');
INSERT INTO base (baseid,value_) VALUES (12,'Seafood');
INSERT INTO base (baseid,value_) VALUES (13,'Vegetable');


INSERT INTO preptime (preptimeid,value_) VALUES (1,'0 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (2,'1-10 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (3,'10-30 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (4,'30-60 Minutes');
INSERT INTO preptime (preptimeid,value_) VALUES (5,'60+ Minutes');


INSERT INTO difficulty (difficultyid,value_) VALUES (1,'Easy');
INSERT INTO difficulty (difficultyid,value_) VALUES (2,'Intermediate');
INSERT INTO difficulty (difficultyid,value_) VALUES (3,'Difficult');
INSERT INTO difficulty (difficultyid,value_) VALUES (4,'Expert');


INSERT INTO course (courseid,value_) VALUES (1,'Breakfast');
INSERT INTO course (courseid,value_) VALUES (2,'Snack');
INSERT INTO course (courseid,value_) VALUES (3,'Lunch');
INSERT INTO course (courseid,value_) VALUES (4,'Appetizer');
INSERT INTO course (courseid,value_) VALUES (5,'Side Dish');
INSERT INTO course (courseid,value_) VALUES (6,'Entree');
INSERT INTO course (courseid,value_) VALUES (7,'Dessert');
INSERT INTO course (courseid,value_) VALUES (8,'Beverage');
INSERT INTO course (courseid,value_) VALUES (9,'N/A');

UPDATE HIBERNATE_UNIQUE_KEY SET NEXT_HI = 1 + (select max(id) from (
	select baseid as id from base union 
	select courseid as id from course union 
	select difficultyid as id from difficulty union 
	select ethnicityid as id from ethnicity union 
	select ingredientid as id from ingredient union 
	select preptimeid as id from preptime union 
	select relationkindid as id from relationkind union 
	select systemid as id from measurement_system union 
	select unitid as id from unit union 
	select userid as id from users
	) as sub);