iEat/defs/sql/derby/create.sql

alter table meal drop constraint meal_user_fk;
alter table mealrecipe drop constraint mealrecipe_recipe_fk;
alter table mealrecipe drop constraint mealrecipe_course_fk;
alter table mealrecipe drop constraint mealrecipe_meal_fk;
alter table recipe drop constraint recipe_ethnicity_fk;
alter table recipe drop constraint recipe_preptime_fk;
alter table recipe drop constraint recipe_base_fk;
alter table recipe drop constraint recipe_user_fk;
alter table recipe drop constraint recipe_course_fk;
alter table recipe drop constraint recipe_difficulty_fk;
alter table recipe drop constraint recipe_system_fk;
alter table recipecomment drop constraint recipecomment_recipe_fk;
alter table recipeingredient drop constraint recipeingredient_recipe_fk;
alter table recipeingredient drop constraint recipeingredient_unit_fk;
alter table recipeingredient drop constraint recipeingredient_ingredientrecipe_fk;
alter table recipeingredient drop constraint recipeingredient_ingredient_fk;
alter table reciperating drop constraint reciperating_recipe_fk;
alter table recipestep drop constraint recipestep_recipe_fk;
alter table relatedrecipe drop constraint relatedrecipe_recipe_fk;
alter table relatedrecipe drop constraint relatedrecipe_relationkind_fk;
drop table base;
drop table course;
drop table difficulty;
drop table ethnicity;
drop table ingredient;
drop table meal;
drop table mealrecipe;
drop table measurement_system;
drop table preptime;
drop table recipe;
drop table recipecomment;
drop table recipeingredient;
drop table reciperating;
drop table recipestep;
drop table relatedrecipe;
drop table relationkind;
drop table unit;
drop table users;
drop table hibernate_unique_key;
create table base (baseid integer not null, Value_ varchar(255), primary key (baseid));
create table course (courseid integer not null, Value_ varchar(255), primary key (courseid));
create table difficulty (difficultyid integer not null, Value_ varchar(255), primary key (difficultyid));
create table ethnicity (ethnicityid integer not null, Value_ varchar(255), primary key (ethnicityid));
create table ingredient (ingredientid integer not null, description varchar(512), ingr_name varchar(128) not null, primary key (ingredientid));
create table meal (mealid bigint not null, ownerid integer, meal_name varchar(128) not null, primary key (mealid));
create table mealrecipe (mealrecipeid bigint not null, recipeid bigint, courseid integer, quantity double, mealid bigint, pos integer, primary key (mealrecipeid));
create table measurement_system (systemid integer not null, Value_ varchar(255), primary key (systemid));
create table preptime (preptimeid integer not null, Value_ varchar(255), primary key (preptimeid));
create table recipe (recipeid bigint not null, ownerid integer, image varchar(255), excerpt clob(255), source varchar(128), courseid integer, systemid integer, directions clob(255), baseid integer, preptimeid integer, ethnicityid integer, createddate timestamp, servingsize integer not null, recipe_name varchar(128) not null, modifieddate timestamp, difficultyid integer, primary key (recipeid));
create table recipecomment (commentid bigint not null, Value_ varchar(255), userid integer not null, createddate timestamp not null, recipeid bigint, pos integer, primary key (commentid));
create table recipeingredient (recipeingredientid bigint not null, ingredientid integer, unitid integer, optional smallint not null, ingredientrecipeid bigint, qualifier varchar(255), quantity double not null, recipeid bigint, pos integer, primary key (recipeingredientid));
create table reciperating (ratingid bigint not null, userid integer not null, createddate timestamp not null, rating smallint not null, recipeid bigint, pos integer, primary key (ratingid));
create table recipestep (stepid bigint not null, Value_ varchar(255), recipeid bigint, pos integer, primary key (stepid));
create table relatedrecipe (relatedrecipeid bigint not null, relationkindid integer, recipeid bigint, pos integer, primary key (relatedrecipeid));
create table relationkind (relationkindid integer not null, Value_ varchar(255), primary key (relationkindid));
create table unit (unitid integer not null, Value_ varchar(255), abbreviation varchar(8) not null, mass smallint not null, tometric double, tous double, toimperial double, systemid integer, primary key (unitid));
create table users (userid integer not null, password varchar(64) not null, email varchar(128) not null, createddate timestamp not null, country varchar(3) not null, accesslevel integer, lastlogindate timestamp, user_name varchar(64) not null, login varchar(32) not null, language varchar(3) not null, primary key (userid));
alter table meal add constraint meal_user_fk foreign key (ownerid) references users;
alter table mealrecipe add constraint mealrecipe_recipe_fk foreign key (recipeid) references recipe;
alter table mealrecipe add constraint mealrecipe_course_fk foreign key (courseid) references course;
alter table mealrecipe add constraint mealrecipe_meal_fk foreign key (mealid) references meal;
alter table recipe add constraint recipe_ethnicity_fk foreign key (ethnicityid) references ethnicity;
alter table recipe add constraint recipe_preptime_fk foreign key (preptimeid) references preptime;
alter table recipe add constraint recipe_base_fk foreign key (baseid) references base;
alter table recipe add constraint recipe_user_fk foreign key (ownerid) references users;
alter table recipe add constraint recipe_course_fk foreign key (courseid) references course;
alter table recipe add constraint recipe_difficulty_fk foreign key (difficultyid) references difficulty;
alter table recipe add constraint recipe_system_fk foreign key (systemid) references measurement_system;
alter table recipecomment add constraint recipecomment_recipe_fk foreign key (recipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_recipe_fk foreign key (recipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_unit_fk foreign key (unitid) references unit;
alter table recipeingredient add constraint recipeingredient_ingredientrecipe_fk foreign key (ingredientrecipeid) references recipe;
alter table recipeingredient add constraint recipeingredient_ingredient_fk foreign key (ingredientid) references ingredient;
alter table reciperating add constraint reciperating_recipe_fk foreign key (recipeid) references recipe;
alter table recipestep add constraint recipestep_recipe_fk foreign key (recipeid) references recipe;
alter table relatedrecipe add constraint relatedrecipe_recipe_fk foreign key (recipeid) references recipe;
alter table relatedrecipe add constraint relatedrecipe_relationkind_fk foreign key (relationkindid) references relationkind;
create table hibernate_unique_key ( next_hi integer );
insert into hibernate_unique_key values ( 0 );