iEat/defs/sql/mysql/create.sql

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