BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `utensil_materialObject` (
    `utensil_id`    INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL DEFAULT 0,
    PRIMARY KEY(`utensil_id`,`materialObject_id`),
    FOREIGN KEY(`utensil_id`) REFERENCES `utensil` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `utensilType` (
    `id`    INTEGER NOT NULL,
    `utensilText`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `utensilLangAttribute` (
    `id`    INTEGER NOT NULL,
    `name`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `utensilLang` (
    `utensil_id`    INTEGER NOT NULL,
    `utensilLangAttribute_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`utensil_id`,`utensilLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`utensilLangAttribute_id`) REFERENCES `utensilLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`utensil_id`) REFERENCES `utensil` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `utensil` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `utensilType_id`    INTEGER NOT NULL,
    `version`   DECIMAL ( 8 , 2 ) NOT NULL,
    FOREIGN KEY(`utensilType_id`) REFERENCES `utensilType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `unit_unitType` (
    `unit_id`   INTEGER NOT NULL,
    `unitType_id`   INTEGER NOT NULL,
    FOREIGN KEY(`unit_id`) REFERENCES `unit` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`unit_id`,`unitType_id`),
    FOREIGN KEY(`unitType_id`) REFERENCES `unitType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `unitType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `unitNotationPriority` (
    `id`    INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `unitNotationLangAttribute` (
    `id`    INTEGER NOT NULL,
    `name`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `unitNotationLang` (
    `unitNotation_id`   INTEGER NOT NULL,
    `unitNotationLangAttribute_id`  INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`unitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`unitNotation_id`,`unitNotationLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`unitNotationLangAttribute_id`) REFERENCES `unitNotationLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `unitNotation` (
    `id`    INTEGER NOT NULL,
    `unit_id`   INTEGER NOT NULL,
    `unitNotationPriority_id`   INTEGER NOT NULL,
    FOREIGN KEY(`unit_id`) REFERENCES `unit` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`unitNotationPriority_id`) REFERENCES `unitNotationPriority` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `unit` (
    `id`    INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `version`   INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmVersion` (
    `id`    INTEGER NOT NULL,
    `version`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlTurboType` (
    `id`    INTEGER NOT NULL,
    `value` VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlTimeType` (
    `id`    INTEGER NOT NULL,
    `value` VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlTimeRange` (
    `tmControl_id`  INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `time`  INTEGER NOT NULL,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`tmControl_id`,`rangeType_id`),
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControlTime` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControlTime` (
    `tmControl_id`  INTEGER NOT NULL,
    `tmControlTimeType_id`  INTEGER NOT NULL,
    PRIMARY KEY(`tmControl_id`),
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControl` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`tmControlTimeType_id`) REFERENCES `tmControlTimeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `tmControlTemperatureType` (
    `id`    INTEGER NOT NULL,
    `value` INTEGER NOT NULL,
    `description`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlSpeedType` (
    `id`    INTEGER NOT NULL,
    `value` FLOAT NOT NULL,
    `description`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlSpeedRange` (
    `tmControlSpeed_id` INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `tmControlSpeedType_id` INTEGER NOT NULL,
    FOREIGN KEY(`tmControlSpeed_id`) REFERENCES `tmControlSpeed` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`tmControlSpeed_id`,`rangeType_id`),
    FOREIGN KEY(`tmControlSpeedType_id`) REFERENCES `tmControlSpeedType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `tmControlSpeed` (
    `id`    INTEGER NOT NULL,
    `tmControlRotationDirectionType_id` INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`id`) REFERENCES `tmControl` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`tmControlRotationDirectionType_id`) REFERENCES `tmControlRotationDirectionType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `tmControlRotationDirectionType` (
    `id`    INTEGER NOT NULL,
    `value` VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlProgramType` (
    `id`    INTEGER NOT NULL,
    `program`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlProgramTurbo` (
    `tmControl_id`  INTEGER NOT NULL,
    `tmControlTurboType_id` INTEGER NOT NULL,
    `impulseCount`  INTEGER NOT NULL,
    FOREIGN KEY(`tmControlTurboType_id`) REFERENCES `tmControlTurboType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`tmControl_id`),
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControlProgram` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControlProgramSoftBlendingSpeedRangeType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlProgramSoftBlendingSpeedRange` (
    `tmControlProgramSoftBlending_id`   INTEGER NOT NULL,
    `tmControlProgramSoftBlendingSpeedRangeType_id` INTEGER NOT NULL,
    `tmControlSpeedType_id` INTEGER NOT NULL,
    FOREIGN KEY(`tmControlProgramSoftBlendingSpeedRangeType_id`) REFERENCES `tmControlProgramSoftBlendingSpeedRangeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`tmControlProgramSoftBlending_id`,`tmControlProgramSoftBlendingSpeedRangeType_id`),
    FOREIGN KEY(`tmControlSpeedType_id`) REFERENCES `tmControlSpeedType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`tmControlProgramSoftBlending_id`) REFERENCES `tmControlProgramSoftBlending` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControlProgramSoftBlending` (
    `tmControl_id`  INTEGER NOT NULL,
    `duration`  INTEGER NOT NULL,
    PRIMARY KEY(`tmControl_id`),
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControlProgram` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControlProgram` (
    `id`    INTEGER NOT NULL,
    `tmControlProgramType_id`   INTEGER NOT NULL,
    FOREIGN KEY(`tmControlProgramType_id`) REFERENCES `tmControlProgramType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`id`) REFERENCES `tmControl` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControlLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `tmControlLang` (
    `tmControl_id`  INTEGER NOT NULL,
    `tmControlLangAttribute_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  CLOB NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`tmControlLangAttribute_id`) REFERENCES `tmControlLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`tmControl_id`,`tmControlLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControl` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `tmControl` (
    `id`    INTEGER NOT NULL,
    `tmControlTemperatureType_id`   INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    FOREIGN KEY(`tmControlTemperatureType_id`) REFERENCES `tmControlTemperatureType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `shoppingCategoryLangAttribute` (
    `id`    INTEGER NOT NULL,
    `name`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `shoppingCategoryLang` (
    `shoppingCategory_id`   INTEGER NOT NULL,
    `shoppingCategoryLangAttribute_id`  INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`shoppingCategory_id`) REFERENCES `shoppingCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`shoppingCategory_id`,`shoppingCategoryLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`shoppingCategoryLangAttribute_id`) REFERENCES `shoppingCategoryLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `shoppingCategory` (
    `id`    INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipe_utensil` (
    `recipe_id` INTEGER NOT NULL,
    `utensil_id`    INTEGER NOT NULL,
    `position`  INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY(`recipe_id`,`utensil_id`),
    FOREIGN KEY(`utensil_id`) REFERENCES `utensil` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipe_tmVersion` (
    `recipe_id` INTEGER NOT NULL,
    `tmVersion_id`  INTEGER NOT NULL,
    PRIMARY KEY(`recipe_id`,`tmVersion_id`),
    FOREIGN KEY(`tmVersion_id`) REFERENCES `tmVersion` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipe_recipeCategory` (
    `recipe_id` INTEGER NOT NULL,
    `recipeCategory_id` INTEGER NOT NULL,
    PRIMARY KEY(`recipe_id`,`recipeCategory_id`),
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeCategory_id`) REFERENCES `recipeCategory` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipe_materialObject` (
    `recipe_id` INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipe_id`,`materialObject_id`),
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipe_company` (
    `recipe_id` INTEGER NOT NULL,
    `company_id`    INTEGER NOT NULL,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`company_id`) REFERENCES `company` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipe_id`,`company_id`)
);
CREATE TABLE IF NOT EXISTS `recipeTimeTypeLang` (
    `recipeTimeType_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`recipeTimeType_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeTimeType_id`) REFERENCES `recipeTimeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipeTimeType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeTimeRange` (
    `recipeTime_id` INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `secondsValue`  INTEGER NOT NULL,
    FOREIGN KEY(`recipeTime_id`) REFERENCES `recipeTime` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeTime_id`,`rangeType_id`)
);
CREATE TABLE IF NOT EXISTS `recipeTimeLang` (
    `recipeTime_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeTime_id`,`locale_id`),
    FOREIGN KEY(`recipeTime_id`) REFERENCES `recipeTime` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeTime` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `recipeTimeType_id` INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipeTimeType_id`) REFERENCES `recipeTimeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeSyncStateType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeSyncState` (
    `recipeSyncStateType_id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`recipeSyncStateType_id`),
    FOREIGN KEY(`recipeSyncStateType_id`) REFERENCES `recipeSyncStateType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipeStep_utensil` (
    `recipeStep_id` INTEGER NOT NULL,
    `utensil_id`    INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    FOREIGN KEY(`utensil_id`) REFERENCES `utensil` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeStep_id`,`utensil_id`)
);
CREATE TABLE IF NOT EXISTS `recipeStep_tmControl` (
    `recipeStep_id` INTEGER NOT NULL,
    `tmControl_id`  INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `tmVersion_id`  INTEGER NOT NULL,
    PRIMARY KEY(`recipeStep_id`,`tmControl_id`),
    FOREIGN KEY(`tmVersion_id`,`recipe_id`) REFERENCES `recipe_tmVersion`(`tmVersion_id`,`recipe_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`tmControl_id`) REFERENCES `tmControl` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeStep_recipeIngredientRange` (
    `recipeStep_id` INTEGER NOT NULL,
    `recipeIngredient_id`   INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `ingredientAmountValue` DECIMAL ( 8 , 3 ) NOT NULL,
    FOREIGN KEY(`recipeIngredient_id`,`recipeStep_id`) REFERENCES `recipeStep_recipeIngredient`(`recipeIngredient_id`,`recipeStep_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeStep_id`,`recipeIngredient_id`,`rangeType_id`),
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipeStep_recipeIngredient` (
    `recipeStep_id` INTEGER NOT NULL,
    `recipeIngredient_id`   INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeIngredient_id`) REFERENCES `recipeIngredient` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeStep_id`,`recipeIngredient_id`)
);
CREATE TABLE IF NOT EXISTS `recipeStep_materialObject` (
    `recipeStep_id` INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL DEFAULT 0,
    PRIMARY KEY(`recipeStep_id`,`materialObject_id`),
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeStepLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeStepLang` (
    `recipeStep_id` INTEGER NOT NULL,
    `recipeStepLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`recipeStepLangAttribute_id`) REFERENCES `recipeStepLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeStep_id`,`recipeStepLangAttribute_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `recipeStep` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `recipeGroup_id`    INTEGER,
    `position`  INTEGER NOT NULL,
    `displayNo` INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipeGroup_id`) REFERENCES `recipeGroup` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeSignatureType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeSignature` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `recipeSignatureType_id`    INTEGER NOT NULL,
    `recipeSignature`   CLOB NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipeSignatureType_id`) REFERENCES `recipeSignatureType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeRecentlySyncedType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeRecentlySynced` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `timestamp` INTEGER NOT NULL,
    `recipeRecentlySyncedType_id`   INTEGER NOT NULL,
    FOREIGN KEY(`recipeRecentlySyncedType_id`) REFERENCES `recipeRecentlySyncedType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipePriceLang` (
    `recipePrice_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`recipePrice_id`,`locale_id`),
    FOREIGN KEY(`recipePrice_id`) REFERENCES `recipePrice` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipePrice` (
    `id`    INTEGER NOT NULL,
    `pricelevel`    VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipePathType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipePathLang` (
    `recipePath_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  CLOB NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`recipePath_id`,`locale_id`),
    FOREIGN KEY(`recipePath_id`) REFERENCES `recipePath` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipePathEntry` (
    `id`    INTEGER NOT NULL,
    `recipePath_id` INTEGER NOT NULL,
    `recipeStep_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `displayNo` INTEGER NOT NULL,
    `isForceDisplay`    BOOLEAN NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipePath_id`) REFERENCES `recipePath` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipePath` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `recipePathType_id` INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipePathType_id`) REFERENCES `recipePathType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeNutritionalValueTypeLang` (
    `recipeNutritionalValueType_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`recipeNutritionalValueType_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeNutritionalValueType_id`) REFERENCES `recipeNutritionalValueType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipeNutritionalValueType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeNutritionalValuePortionRange` (
    `recipeNutritionalValue_id` INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `portionAmountValue`    DECIMAL ( 8 , 3 ) NOT NULL,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeNutritionalValue_id`) REFERENCES `recipeNutritionalValue` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeNutritionalValue_id`,`rangeType_id`)
);
CREATE TABLE IF NOT EXISTS `recipeNutritionalValueData` (
    `recipeNutritionalValue_id` INTEGER NOT NULL,
    `recipeNutritionalValueType_id` INTEGER NOT NULL,
    `nutritionalValue`  DECIMAL ( 8 , 3 ) NOT NULL,
    `unitNotation_id`   INTEGER NOT NULL,
    FOREIGN KEY(`unitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeNutritionalValue_id`) REFERENCES `recipeNutritionalValue` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeNutritionalValueType_id`) REFERENCES `recipeNutritionalValueType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeNutritionalValue_id`,`recipeNutritionalValueType_id`)
);
CREATE TABLE IF NOT EXISTS `recipeNutritionalValue` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `servingUnitNotation_id`    INTEGER NOT NULL,
    FOREIGN KEY(`servingUnitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeNutritionLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeNutritionLang` (
    `recipeNutrition_id`    INTEGER NOT NULL,
    `recipeNutritionLangAttribute_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`recipeNutritionLangAttribute_id`) REFERENCES `recipeNutritionLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeNutrition_id`) REFERENCES `recipeNutritionalValue` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeNutrition_id`,`recipeNutritionLangAttribute_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `recipeLinkType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeLink` (
    `targetRecipe_id`   INTEGER NOT NULL,
    `sourceRecipe_id`   INTEGER NOT NULL,
    `recipeLinkType_id` INTEGER NOT NULL,
    FOREIGN KEY(`sourceRecipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`targetRecipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`targetRecipe_id`,`sourceRecipe_id`,`recipeLinkType_id`),
    FOREIGN KEY(`recipeLinkType_id`) REFERENCES `recipeLinkType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `recipeLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR ( 50 ) NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeLang` (
    `recipe_id` INTEGER NOT NULL,
    `recipeLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipe_id`,`recipeLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`recipeLangAttribute_id`) REFERENCES `recipeLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeIngredientRange` (
    `recipeIngredient_id`   INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `ingredientAmountValue` DECIMAL ( 8 , 3 ) NOT NULL,
    FOREIGN KEY(`recipeIngredient_id`) REFERENCES `recipeIngredient` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeIngredient_id`,`rangeType_id`)
);
CREATE TABLE IF NOT EXISTS `recipeIngredient` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `ingredientNotation_id` INTEGER NOT NULL,
    `ingredientPreparation_id`  INTEGER,
    `isCommaSeparated`  BOOLEAN NOT NULL,
    `recipeGroup_id`    INTEGER,
    `position`  INTEGER NOT NULL,
    `priority`  INTEGER NOT NULL,
    `unitNotation_id`   INTEGER,
    `isOptional`    BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY(`ingredientPreparation_id`) REFERENCES `ingredientPreparation` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`unitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`ingredientNotation_id`) REFERENCES `ingredientNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeGroup_id`) REFERENCES `recipeGroup` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeImport` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    `insertStatement`   CLOB NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeGroupLang` (
    `recipeGroup_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`recipeGroup_id`,`locale_id`),
    FOREIGN KEY(`recipeGroup_id`) REFERENCES `recipeGroup` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeGroup` (
    `id`    INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeDifficultyLang` (
    `recipeDifficulty_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`recipeDifficulty_id`) REFERENCES `recipeDifficulty` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeDifficulty_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `recipeDifficulty` (
    `id`    INTEGER NOT NULL,
    `difficulty`    VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeCategory_materialObject` (
    `recipeCategory_id` INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeCategory_id`) REFERENCES `recipeCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`recipeCategory_id`,`materialObject_id`)
);
CREATE TABLE IF NOT EXISTS `recipeCategoryLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipeCategoryLang` (
    `recipeCategory_id` INTEGER NOT NULL,
    `recipeCategoryLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`recipeCategoryLangAttribute_id`) REFERENCES `recipeCategoryLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`recipeCategory_id`,`recipeCategoryLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeCategory_id`) REFERENCES `recipeCategory` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `recipeCategory` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL,
    `lft`   INTEGER NOT NULL,
    `rgt`   INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `recipe` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `originalTitle` VARCHAR NOT NULL,
    `year`  INTEGER NOT NULL,
    `servingQuantity`   DECIMAL ( 8 , 3 ) NOT NULL,
    `servingUnitNotation_id`    INTEGER,
    `recipeDifficulty_id`   INTEGER NOT NULL DEFAULT 0,
    `recipePrice_id`    INTEGER NOT NULL DEFAULT 0,
    `version`   DECIMAL ( 8 , 2 ) NOT NULL,
    `createdts` INTEGER NOT NULL,
    `lastchangedts` INTEGER NOT NULL,
    `isDeleted` BOOLEAN NOT NULL,
    FOREIGN KEY(`recipePrice_id`) REFERENCES `recipePrice` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`recipeDifficulty_id`) REFERENCES `recipeDifficulty` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`servingUnitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `rangeType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `project_projectCategory` (
    `project_id`    INTEGER NOT NULL,
    `projectCategory_id`    INTEGER NOT NULL,
    FOREIGN KEY(`projectCategory_id`) REFERENCES `projectCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`project_id`,`projectCategory_id`),
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `project_materialObject` (
    `project_id`    INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL,
    PRIMARY KEY(`project_id`,`materialObject_id`),
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `project_company` (
    `project_id`    INTEGER NOT NULL,
    `company_id`    INTEGER NOT NULL,
    PRIMARY KEY(`project_id`,`company_id`),
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`company_id`) REFERENCES `company` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `projectLinkType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `projectLink` (
    `targetProject_id`  INTEGER NOT NULL,
    `sourceProject_id`  INTEGER NOT NULL,
    `projectLinkType_id`    INTEGER NOT NULL,
    FOREIGN KEY(`projectLinkType_id`) REFERENCES `projectLinkType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`targetProject_id`,`sourceProject_id`,`projectLinkType_id`),
    FOREIGN KEY(`targetProject_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`sourceProject_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `projectLang` (
    `project_id`    INTEGER NOT NULL,
    `projectLangAttribute_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`project_id`,`projectLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`projectLangAttribute_id`) REFERENCES `projectLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectEntry_materialObject` (
    `projectEntry_id`   INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`projectEntry_id`,`materialObject_id`),
    FOREIGN KEY(`projectEntry_id`) REFERENCES `projectEntry` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectEntryTitleLang` (
    `projectEntryTitle_id`  INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `title` VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`projectEntryTitle_id`) REFERENCES `projectEntryTitle` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`projectEntryTitle_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `projectEntryTitle` (
    `id`    INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`id`) REFERENCES `projectEntry` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectEntryRecipe` (
    `id`    INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`id`) REFERENCES `projectEntry` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectEntry` (
    `id`    INTEGER NOT NULL,
    `project_id`    INTEGER NOT NULL,
    `lft`   INTEGER NOT NULL,
    `rgt`   INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectCategoryLang` (
    `projectCategory_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`projectCategory_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`projectCategory_id`) REFERENCES `projectCategory` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `projectCategory` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `position`  INTEGER NOT NULL,
    `lft`   INTEGER NOT NULL,
    `rgt`   INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `project` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `description`   VARCHAR NOT NULL,
    `projectType_id`    INTEGER NOT NULL,
    `nofRecipesPlanned` INTEGER NOT NULL,
    `isInternal`    BOOLEAN NOT NULL,
    `isInternational`   BOOLEAN NOT NULL,
    `isDeleted` BOOLEAN NOT NULL,
    `createdts` INTEGER NOT NULL,
    `lastchangedts` INTEGER NOT NULL,
    FOREIGN KEY(`projectType_id`) REFERENCES `projectType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `material_materialStorageLocation` (
    `material_id`   INTEGER NOT NULL,
    `materialStorageLocation_id`    INTEGER NOT NULL,
    PRIMARY KEY(`material_id`,`materialStorageLocation_id`),
    FOREIGN KEY(`material_id`) REFERENCES `material` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`materialStorageLocation_id`) REFERENCES `materialStorageLocation` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `material_materialFlag` (
    `material_id`   INTEGER NOT NULL,
    `materialFlag_id`   INTEGER NOT NULL,
    FOREIGN KEY(`material_id`) REFERENCES `material` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`materialFlag_id`) REFERENCES `materialFlag` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`material_id`,`materialFlag_id`)
);
CREATE TABLE IF NOT EXISTS `material_company` (
    `material_id`   INTEGER NOT NULL,
    `company_id`    INTEGER NOT NULL,
    PRIMARY KEY(`material_id`,`company_id`),
    FOREIGN KEY(`company_id`) REFERENCES `company` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`material_id`) REFERENCES `material` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `materialValueType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialValue` (
    `materialObject_id` INTEGER NOT NULL,
    `materialType_id`   INTEGER NOT NULL,
    `materialValueType_id`  INTEGER NOT NULL,
    `value` VARCHAR NOT NULL,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`materialObject_id`,`materialType_id`,`materialValueType_id`),
    FOREIGN KEY(`materialValueType_id`,`materialType_id`) REFERENCES `materialType_materialValueType`(`materialValueType_id`,`materialType_id`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `materialType_materialValueType` (
    `materialType_id`   INTEGER NOT NULL,
    `materialValueType_id`  INTEGER NOT NULL,
    FOREIGN KEY(`materialValueType_id`) REFERENCES `materialValueType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`materialType_id`) REFERENCES `materialType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`materialType_id`,`materialValueType_id`)
);
CREATE TABLE IF NOT EXISTS `materialType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialStorageLocationType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialStorageLocation` (
    `id`    INTEGER NOT NULL,
    `materialStorageLocationType_id`    INTEGER NOT NULL,
    `source`    VARCHAR NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`materialStorageLocationType_id`) REFERENCES `materialStorageLocationType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `materialOrphans` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialObject_materialObjectFlag` (
    `materialObject_id` INTEGER NOT NULL,
    `materialObjectFlag_id` INTEGER NOT NULL,
    FOREIGN KEY(`materialObjectFlag_id`) REFERENCES `materialObjectFlag` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`materialObject_id`,`materialObjectFlag_id`)
);
CREATE TABLE IF NOT EXISTS `materialObjectLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialObjectLang` (
    `materialObject_id` INTEGER NOT NULL,
    `materialObjectLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`materialObject_id`,`materialObjectLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`materialObjectLangAttribute_id`) REFERENCES `materialObjectLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `materialObjectFlag` (
    `id`    INTEGER NOT NULL,
    `flag`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialObject` (
    `id`    INTEGER NOT NULL,
    `material_id`   INTEGER NOT NULL,
    FOREIGN KEY(`material_id`) REFERENCES `material` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `materialFlag` (
    `id`    INTEGER NOT NULL,
    `flag`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `material` (
    `id`    INTEGER NOT NULL,
    `materialType_id`   INTEGER NOT NULL,
    `mdbId` INTEGER,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`materialType_id`) REFERENCES `materialType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `maintenanceRecipeHash` (
    `maintenanceCloudSyncHash_id`   INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    PRIMARY KEY(`maintenanceCloudSyncHash_id`),
    FOREIGN KEY(`maintenanceCloudSyncHash_id`) REFERENCES `maintenanceCloudSyncHash` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `maintenanceRecipeCollectionHash` (
    `maintenanceCloudSyncHash_id`   INTEGER NOT NULL,
    `project_id`    INTEGER NOT NULL,
    FOREIGN KEY(`project_id`) REFERENCES `project` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`maintenanceCloudSyncHash_id`),
    FOREIGN KEY(`maintenanceCloudSyncHash_id`) REFERENCES `maintenanceCloudSyncHash` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `maintenanceMediaHash` (
    `maintenanceCloudSyncHash_id`   INTEGER NOT NULL,
    `fileURI`   CLOB NOT NULL,
    PRIMARY KEY(`maintenanceCloudSyncHash_id`),
    FOREIGN KEY(`maintenanceCloudSyncHash_id`) REFERENCES `maintenanceCloudSyncHash` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `maintenanceHashType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `maintenanceCloudSyncHash` (
    `id`    INTEGER NOT NULL,
    `hash`  CLOB NOT NULL,
    `maintenanceHashType_id`    INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`maintenanceHashType_id`) REFERENCES `maintenanceHashType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `locale` (
    `id`    INTEGER NOT NULL,
    `country_id`    INTEGER NOT NULL,
    `language_id`   INTEGER NOT NULL,
    `lft`   INTEGER NOT NULL,
    `rgt`   INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`language_id`) REFERENCES `language` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`country_id`) REFERENCES `country` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `languageLang` (
    `language_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR ( 50 ) NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`language_id`,`locale_id`),
    FOREIGN KEY(`language_id`) REFERENCES `language` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `language` (
    `id`    INTEGER NOT NULL,
    `code`  VARCHAR ( 2 ) NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredient_materialObject` (
    `ingredient_id` INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    `isPrimary` BOOLEAN NOT NULL DEFAULT 0,
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`ingredient_id`,`materialObject_id`)
);
CREATE TABLE IF NOT EXISTS `ingredient_ingredientFoodCategory` (
    `ingredient_id` INTEGER NOT NULL,
    `ingredientFoodCategory_id` INTEGER NOT NULL,
    FOREIGN KEY(`ingredientFoodCategory_id`) REFERENCES `ingredientFoodCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`ingredient_id`,`ingredientFoodCategory_id`),
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `ingredient_country_ingredientFlag` (
    `ingredient_id` INTEGER NOT NULL,
    `country_id`    INTEGER NOT NULL,
    `ingredientFlag_id` INTEGER NOT NULL,
    FOREIGN KEY(`country_id`) REFERENCES `country` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`ingredientFlag_id`) REFERENCES `ingredientFlag` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`ingredient_id`,`country_id`,`ingredientFlag_id`)
);
CREATE TABLE IF NOT EXISTS `ingredient_baseRecipe` (
    `ingredient_id` INTEGER NOT NULL,
    `recipe_id` INTEGER NOT NULL,
    FOREIGN KEY(`recipe_id`) REFERENCES `recipe` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`ingredient_id`,`recipe_id`),
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `ingredientPreparationLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientPreparationLang` (
    `ingredientPreparation_id`  INTEGER NOT NULL,
    `ingredientPreparationLangAttribute_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`ingredientPreparation_id`) REFERENCES `ingredientPreparation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`ingredientPreparationLangAttribute_id`) REFERENCES `ingredientPreparationLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`ingredientPreparation_id`,`ingredientPreparationLangAttribute_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `ingredientPreparation` (
    `id`    INTEGER NOT NULL,
    `ingredient_id` INTEGER NOT NULL,
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientNotationPriority` (
    `id`    INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientNotationLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientNotationLang` (
    `ingredientNotation_id` INTEGER NOT NULL,
    `ingredientNotationLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`ingredientNotation_id`,`ingredientNotationLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`ingredientNotation_id`) REFERENCES `ingredientNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`ingredientNotationLangAttribute_id`) REFERENCES `ingredientNotationLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `ingredientNotation` (
    `id`    INTEGER NOT NULL,
    `ingredient_id` INTEGER NOT NULL,
    `ingredientNotationPriority_id` INTEGER NOT NULL,
    FOREIGN KEY(`ingredientNotationPriority_id`) REFERENCES `ingredientNotationPriority` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientLang` (
    `ingredient_id` INTEGER NOT NULL,
    `ingredientLangAttribute_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    `isInternal`    BOOLEAN NOT NULL,
    PRIMARY KEY(`ingredient_id`,`ingredientLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`ingredient_id`) REFERENCES `ingredient` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`ingredientLangAttribute_id`) REFERENCES `ingredientLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `ingredientFoodCategoryLang` (
    `ingredientFoodCategory_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`ingredientFoodCategory_id`) REFERENCES `ingredientFoodCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`ingredientFoodCategory_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `ingredientFoodCategory` (
    `id`    INTEGER NOT NULL,
    `lft`   INTEGER NOT NULL,
    `rgt`   INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredientFlag` (
    `id`    INTEGER NOT NULL,
    `flagName`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `ingredient` (
    `id`    INTEGER NOT NULL,
    `uid`   VARCHAR NOT NULL,
    `shoppingCategory_id`   INTEGER NOT NULL,
    `version`   DECIMAL ( 8 , 2 ) NOT NULL,
    `isDeleted` BOOLEAN NOT NULL,
    `createdts` INTEGER NOT NULL,
    FOREIGN KEY(`shoppingCategory_id`) REFERENCES `shoppingCategory` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedUtensilStep` (
    `guidedStep_id` INTEGER NOT NULL,
    `guidedUtensilActionType_id`    INTEGER NOT NULL,
    `guidedUtensilActionText_id`    INTEGER NOT NULL,
    PRIMARY KEY(`guidedStep_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`guidedUtensilActionText_id`) REFERENCES `guidedUtensilActionText` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`guidedUtensilActionType_id`) REFERENCES `guidedUtensilActionType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedUtensilActionType` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedUtensilActionTextLang` (
    `guidedUtensilActionText_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`guidedUtensilActionText_id`,`locale_id`),
    FOREIGN KEY(`guidedUtensilActionText_id`) REFERENCES `guidedUtensilActionText` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedUtensilActionText` (
    `id`    INTEGER NOT NULL,
    `isPredefined`  BOOLEAN NOT NULL DEFAULT 0,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedTmSettingStepLangAttribute` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedTmSettingStepLang` (
    `guidedTmSettingStep_id`    INTEGER NOT NULL,
    `guidedTmSettingStepLangAttribute_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`guidedTmSettingStep_id`,`guidedTmSettingStepLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`guidedTmSettingStep_id`) REFERENCES `guidedTmSettingStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`guidedTmSettingStepLangAttribute_id`) REFERENCES `guidedTmSettingStepLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedTmSettingStep` (
    `guidedStep_id` INTEGER NOT NULL,
    `recipeStep_id` INTEGER NOT NULL,
    `tmControl_id`  INTEGER NOT NULL,
    PRIMARY KEY(`guidedStep_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`tmControl_id`,`recipeStep_id`) REFERENCES `recipeStep_tmControl`(`tmControl_id`,`recipeStep_id`) ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedStep_materialObject` (
    `guidedStep_id` INTEGER NOT NULL,
    `guidedStepMaterialObjectType_id`   INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    FOREIGN KEY(`guidedStepMaterialObjectType_id`) REFERENCES `guidedStepMaterialObjectType` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`guidedStep_id`,`guidedStepMaterialObjectType_id`,`materialObject_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedStepType` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedStepMaterialObjectType` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedStep` (
    `id`    INTEGER NOT NULL,
    `recipeStep_id` INTEGER NOT NULL,
    `guidedStepType_id` INTEGER NOT NULL,
    `position`  INTEGER NOT NULL,
    FOREIGN KEY(`recipeStep_id`) REFERENCES `recipeStep` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`guidedStepType_id`) REFERENCES `guidedStepType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepWeighingAttribute` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepTextLang` (
    `guidedIngredientStepText_id`   INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`guidedIngredientStepText_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`guidedIngredientStepText_id`) REFERENCES `guidedIngredientStepText` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepTextAttribute` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepText` (
    `id`    INTEGER NOT NULL,
    `guidedIngredientStepTextAttribute_id`  INTEGER NOT NULL,
    `isPredefined`  BOOLEAN NOT NULL,
    FOREIGN KEY(`guidedIngredientStepTextAttribute_id`) REFERENCES `guidedIngredientStepTextAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepIngredientAmountRange` (
    `guidedIngredientStep_id`   INTEGER NOT NULL,
    `recipeStep_id` INTEGER NOT NULL,
    `recipeIngredient_id`   INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `amount`    DECIMAL ( 8 , 3 ) NOT NULL,
    PRIMARY KEY(`guidedIngredientStep_id`,`recipeStep_id`,`recipeIngredient_id`,`rangeType_id`),
    FOREIGN KEY(`recipeStep_id`,`recipeIngredient_id`,`guidedIngredientStep_id`) REFERENCES `guidedIngredientStepIngredient`(`recipeStep_id`,`recipeIngredient_id`,`guidedIngredientStep_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStepIngredient` (
    `guidedIngredientStep_id`   INTEGER NOT NULL,
    `recipeStep_id` INTEGER NOT NULL,
    `recipeIngredient_id`   INTEGER NOT NULL,
    `ingredientNotation_id` INTEGER NOT NULL,
    `guidedIngredientStepWeighingAttribute_id`  INTEGER NOT NULL,
    `guidedIngredientStepText_id`   INTEGER NOT NULL,
    `unitNotation_id`   INTEGER DEFAULT NULL,
    FOREIGN KEY(`guidedIngredientStep_id`) REFERENCES `guidedIngredientStep` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`guidedIngredientStep_id`,`recipeStep_id`,`recipeIngredient_id`),
    FOREIGN KEY(`guidedIngredientStepText_id`) REFERENCES `guidedIngredientStepText` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`unitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`recipeIngredient_id`,`recipeStep_id`) REFERENCES `recipeStep_recipeIngredient`(`recipeIngredient_id`,`recipeStep_id`) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`guidedIngredientStepWeighingAttribute_id`) REFERENCES `guidedIngredientStepWeighingAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`ingredientNotation_id`) REFERENCES `ingredientNotation` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedIngredientStep` (
    `guidedStep_id` INTEGER NOT NULL,
    PRIMARY KEY(`guidedStep_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedFreetextType` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedFreetextStepLang` (
    `guidedFreetextStep_id` INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`guidedFreetextStep_id`,`locale_id`),
    FOREIGN KEY(`guidedFreetextStep_id`) REFERENCES `guidedFreetextStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedFreetextStep` (
    `guidedStep_id` INTEGER NOT NULL,
    `guidedFreetextType_id` INTEGER NOT NULL,
    FOREIGN KEY(`guidedFreetextType_id`) REFERENCES `guidedFreetextType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`guidedStep_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientStepTextLang` (
    `guidedCreatedIngredientStepText_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`guidedCreatedIngredientStepText_id`) REFERENCES `guidedCreatedIngredientStepText` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`guidedCreatedIngredientStepText_id`,`locale_id`),
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientStepTextAttribute` (
    `id`    INTEGER NOT NULL,
    `value` CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientStepText` (
    `id`    INTEGER NOT NULL,
    `guidedCreatedIngredientStepTextAttribute_id`   INTEGER NOT NULL,
    `isPredefined`  BOOLEAN NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`guidedCreatedIngredientStepTextAttribute_id`) REFERENCES `guidedCreatedIngredientStepTextAttribute` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientStep` (
    `guidedStep_id` INTEGER NOT NULL,
    `guidedCreatedIngredientNotation_id`    INTEGER NOT NULL,
    `guidedCreatedIngredientStepText_id`    INTEGER NOT NULL,
    `guidedIngredientStepWeighingAttribute_id`  INTEGER NOT NULL,
    `unitNotation_id`   INTEGER DEFAULT NULL,
    FOREIGN KEY(`unitNotation_id`) REFERENCES `unitNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(`guidedStep_id`),
    FOREIGN KEY(`guidedStep_id`) REFERENCES `guidedStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`guidedIngredientStepWeighingAttribute_id`) REFERENCES `guidedIngredientStepWeighingAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`guidedCreatedIngredientNotation_id`) REFERENCES `guidedCreatedIngredientNotation` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`guidedCreatedIngredientStepText_id`) REFERENCES `guidedCreatedIngredientStepText` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientNotationLang` (
    `guidedCreatedIngredientNotation_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`guidedCreatedIngredientNotation_id`,`locale_id`),
    FOREIGN KEY(`guidedCreatedIngredientNotation_id`) REFERENCES `guidedCreatedIngredientNotation` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientNotation` (
    `id`    INTEGER NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `guidedCreatedIngredientAmountRange` (
    `guidedCreatedIngredientStep_id`    INTEGER NOT NULL,
    `rangeType_id`  INTEGER NOT NULL,
    `amount`    DECIMAL ( 8 , 3 ) NOT NULL,
    PRIMARY KEY(`guidedCreatedIngredientStep_id`,`rangeType_id`),
    FOREIGN KEY(`guidedCreatedIngredientStep_id`) REFERENCES `guidedCreatedIngredientStep` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`rangeType_id`) REFERENCES `rangeType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `dataExtType` (
    `id`    INTEGER NOT NULL,
    `type`  VARCHAR NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `dataExt` (
    `dataExtType_id`    INTEGER NOT NULL,
    `value` VARCHAR NOT NULL,
    PRIMARY KEY(`dataExtType_id`),
    FOREIGN KEY(`dataExtType_id`) REFERENCES `dataExtType` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `countryLang` (
    `country_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR ( 50 ) NOT NULL,
    `sortKey`   BLOB NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`country_id`) REFERENCES `country` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`country_id`,`locale_id`)
);
CREATE TABLE IF NOT EXISTS `country` (
    `id`    INTEGER NOT NULL,
    `code`  VARCHAR ( 2 ) NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `cookstickMaterialObjectType` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `cookstickMaterialObject` (
    `id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `cookstickMaterialObjectType_id`    INTEGER NOT NULL,
    `materialObject_id` INTEGER NOT NULL,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`cookstickMaterialObjectType_id`) REFERENCES `cookstickMaterialObjectType` ON DELETE CASCADE ON UPDATE RESTRICT,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`materialObject_id`) REFERENCES `materialObject` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `cookstickExtLangAttribute` (
    `id`    INTEGER NOT NULL,
    `type`  CLOB NOT NULL,
    PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `cookstickExtLang` (
    `cookstickExtLangAttribute_id`  INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `text`  VARCHAR NOT NULL,
    `sortKey`   BLOB NOT NULL,
    PRIMARY KEY(`cookstickExtLangAttribute_id`,`locale_id`),
    FOREIGN KEY(`cookstickExtLangAttribute_id`) REFERENCES `cookstickExtLangAttribute` ON DELETE CASCADE ON UPDATE RESTRICT,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `companyLocale` (
    `id`    INTEGER NOT NULL,
    `company_id`    INTEGER NOT NULL,
    `locale_id` INTEGER NOT NULL,
    `priority`  INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`company_id`) REFERENCES `company` ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY(`locale_id`) REFERENCES `locale` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS `company` (
    `id`    INTEGER NOT NULL,
    `name`  VARCHAR NOT NULL,
    `address_id`    INTEGER NOT NULL,
    `phone` VARCHAR NOT NULL,
    `website`   VARCHAR NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`address_id`) REFERENCES `address` ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE IF NOT EXISTS `address` (
    `id`    INTEGER NOT NULL,
    `addressField`  CLOB NOT NULL,
    `street`    VARCHAR NOT NULL,
    `houseNo`   VARCHAR NOT NULL,
    `plz`   INTEGER NOT NULL,
    `city`  VARCHAR NOT NULL,
    `country_id`    INTEGER NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`country_id`) REFERENCES `country` ON DELETE CASCADE ON UPDATE RESTRICT
);
CREATE INDEX IF NOT EXISTS `recipe_recipeCategory_recipeCategory_id_idx` ON `recipe_recipeCategory` (
    `recipeCategory_id` ASC
);
CREATE INDEX IF NOT EXISTS `recipeStep_idx` ON `recipeStep` (
    `recipe_id`
);
CREATE UNIQUE INDEX IF NOT EXISTS `recipeSignature_idx` ON `recipeSignature` (
    `recipe_id`,
    `recipeSignatureType_id`
);
CREATE INDEX IF NOT EXISTS `recipeRecentlySynced_idx` ON `recipeRecentlySynced` (
    `timestamp` ASC
);
CREATE INDEX IF NOT EXISTS `recipeLang_langAttr_localeId_idx` ON `recipeLang` (
    `recipeLangAttribute_id`,
    `locale_id`
);
CREATE INDEX IF NOT EXISTS `recipeIngredient_recipe_id_idx` ON `recipeIngredient` (
    `recipe_id` ASC
);
CREATE INDEX IF NOT EXISTS `recipeImport_idx` ON `recipeImport` (
    `recipe_id`
);
CREATE INDEX IF NOT EXISTS `recipeCategory_isPrimary_idx` ON `recipeCategory` (
    `isPrimary` DESC
);
CREATE INDEX IF NOT EXISTS `projectEntry_project_id_idx` ON `projectEntry` (
    `project_id`    ASC
);
CREATE UNIQUE INDEX IF NOT EXISTS `maintenanceRecipeHash_idx` ON `maintenanceRecipeHash` (
    `recipe_id`
);
CREATE UNIQUE INDEX IF NOT EXISTS `maintenanceRecipeCollectionHash_idx` ON `maintenanceRecipeCollectionHash` (
    `project_id`
);
CREATE UNIQUE INDEX IF NOT EXISTS `maintenanceMediaHash_idx` ON `maintenanceMediaHash` (
    `fileURI`   ASC
);
CREATE INDEX IF NOT EXISTS `maintenanceCloudSyncHash_maintenanceHashType_idx` ON `maintenanceCloudSyncHash` (
    `maintenanceHashType_id`    ASC
);
CREATE INDEX IF NOT EXISTS `guidedStep_idx` ON `guidedStep` (
    `recipeStep_id`
);
CREATE UNIQUE INDEX IF NOT EXISTS `companyLocale_company_locale_idx` ON `companyLocale` (
    `company_id`,
    `locale_id`
);
COMMIT;