/* ########################################### # START DATABASE svc ########################################### */ CREATE DATABASE svc VERSION '1'; USE DATABASE svc VERSION '1'; --############ Translators ############ CREATE FOREIGN DATA WRAPPER mysql5 OPTIONS(EnableDependentJoins true); --############ Servers ############ CREATE SERVER my_nutri_diary FOREIGN DATA WRAPPER mysql5 OPTIONS ("jndi-name" 'java:/my_nutri_diary'); --############ Schemas ############ CREATE SCHEMA my_nutri_diary SERVER my_nutri_diary; --############ Roles ############ CREATE ROLE odata WITH ANY AUTHENTICATED; --############ Schema:my_nutri_diary ############ SET SCHEMA my_nutri_diary; --############ Tables ############ CREATE FOREIGN TABLE Account ( idProfile long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idProfile"', NATIVE_TYPE 'BIGINT'), uuidUser string(36) NOT NULL OPTIONS(NAMEINSOURCE '"uuidUser"', NATIVE_TYPE 'CHAR'), CONSTRAINT "PRIMARY" PRIMARY KEY(idProfile), CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser) ) OPTIONS(NAMEINSOURCE '"Account"', UPDATABLE 'TRUE', CARDINALITY '2'); CREATE FOREIGN TABLE Avatar ( fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), AvatarImg blob(65535) OPTIONS(NAMEINSOURCE '"AvatarImg"', NATIVE_TYPE 'BLOB'), CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile), CONSTRAINT fkAvatarToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"Avatar"', UPDATABLE 'TRUE'); CREATE FOREIGN TABLE BodyWeight ( idBodyWeight long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idBodyWeight"', NATIVE_TYPE 'BIGINT', ANNOTATION 'We need a surrogate key here as Teiid requires a primary key on each table. fkProfile is not unique here as we can will have multiple weight measurements per person. Moreover, combining it with WeightMeasurementDateTime makes the index to compute intensive.'), fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), WeightMeasurementDateTime timestamp NOT NULL OPTIONS(NAMEINSOURCE '"WeightMeasurementDateTime"', NATIVE_TYPE 'DATETIME'), Weight float NOT NULL OPTIONS(NAMEINSOURCE '"Weight"', NATIVE_TYPE 'FLOAT UNSIGNED'), CONSTRAINT "PRIMARY" PRIMARY KEY(idBodyWeight), CONSTRAINT fkBodyWeightToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"BodyWeight"', UPDATABLE 'TRUE', CARDINALITY '1'); CREATE FOREIGN TABLE ConfigOptions ( fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), MUnitWeight string(2) DEFAULT 'kg' OPTIONS(NAMEINSOURCE '"MUnitWeight"', NATIVE_TYPE 'ENUM'), MUnitLength string(2) DEFAULT 'cm' OPTIONS(NAMEINSOURCE '"MUnitLength"', NATIVE_TYPE 'ENUM'), MUnitEnergy string(4) DEFAULT 'kcal' OPTIONS(NAMEINSOURCE '"MUnitEnergy"', NATIVE_TYPE 'ENUM'), MUnitLiquids string(2) DEFAULT 'ml' OPTIONS(NAMEINSOURCE '"MUnitLiquids"', NATIVE_TYPE 'ENUM'), MUnitTime string(3) DEFAULT '24h' OPTIONS(NAMEINSOURCE '"MUnitTime"', NATIVE_TYPE 'ENUM'), EnableDrinkReminder boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"EnableDrinkReminder"', NATIVE_TYPE 'BIT'), PreferredCountryCodeForSearch string(2) DEFAULT 'de' OPTIONS(NAMEINSOURCE '"PreferredCountryCodeForSearch"', NATIVE_TYPE 'VARCHAR'), EnableInternationalSearch boolean DEFAULT '1' OPTIONS(NAMEINSOURCE '"EnableInternationalSearch"', NATIVE_TYPE 'BIT'), DaySpecificMealConfig boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"DaySpecificMealConfig"', NATIVE_TYPE 'BIT'), DaySpecificCalorieConfig boolean DEFAULT '0' OPTIONS(NAMEINSOURCE '"DaySpecificCalorieConfig"', NATIVE_TYPE 'BIT'), CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile), CONSTRAINT fkConfigOptionsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"ConfigOptions"', UPDATABLE 'TRUE', CARDINALITY '2'); CREATE FOREIGN TABLE Diary ( idDiaryEntry long NOT NULL AUTO_INCREMENT OPTIONS(NAMEINSOURCE '"idDiaryEntry"', NATIVE_TYPE 'BIGINT'), fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), AddedDateTime timestamp NOT NULL OPTIONS(NAMEINSOURCE '"AddedDateTime"', NATIVE_TYPE 'DATETIME'), fkIdProductCode long NOT NULL OPTIONS(NAMEINSOURCE '"fkIdProductCode"', NATIVE_TYPE 'BIGINT UNSIGNED'), DatabaseID short NOT NULL OPTIONS(NAMEINSOURCE '"DatabaseID"', NATIVE_TYPE 'TINYINT UNSIGNED'), MealNumber string(1) NOT NULL OPTIONS(NAMEINSOURCE '"MealNumber"', NATIVE_TYPE 'ENUM'), AmountInG double NOT NULL OPTIONS(NAMEINSOURCE '"AmountInG"', NATIVE_TYPE 'DOUBLE'), CONSTRAINT "PRIMARY" PRIMARY KEY(idDiaryEntry), CONSTRAINT fkDiaryToFDBProducts FOREIGN KEY(fkIdProductCode) REFERENCES FDBProducts(idCode), CONSTRAINT fkDiaryToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"Diary"', UPDATABLE 'TRUE', CARDINALITY '5000'); CREATE FOREIGN TABLE FDBProducts ( idCode long NOT NULL OPTIONS(NAMEINSOURCE '"idCode"', NATIVE_TYPE 'BIGINT UNSIGNED'), lc string(5) OPTIONS(NAMEINSOURCE '"lc"', NATIVE_TYPE 'CHAR'), product_name string(256) OPTIONS(NAMEINSOURCE '"product_name"', NATIVE_TYPE 'VARCHAR'), origins string(512) OPTIONS(NAMEINSOURCE '"origins"', NATIVE_TYPE 'VARCHAR'), brands string(384) OPTIONS(NAMEINSOURCE '"brands"', NATIVE_TYPE 'VARCHAR'), quantity string(256) OPTIONS(NAMEINSOURCE '"quantity"', NATIVE_TYPE 'VARCHAR'), nova_group char(1) OPTIONS(NAMEINSOURCE '"nova_group"', NATIVE_TYPE 'CHAR'), nutrition_grade_fr char(1) OPTIONS(NAMEINSOURCE '"nutrition_grade_fr"', NATIVE_TYPE 'CHAR'), energy_100g double OPTIONS(NAMEINSOURCE '"energy_100g"', NATIVE_TYPE 'DOUBLE'), carbohydrates_100g double OPTIONS(NAMEINSOURCE '"carbohydrates_100g"', NATIVE_TYPE 'DOUBLE'), sugars_100g double OPTIONS(NAMEINSOURCE '"sugars_100g"', NATIVE_TYPE 'DOUBLE'), proteins_100g double OPTIONS(NAMEINSOURCE '"proteins_100g"', NATIVE_TYPE 'DOUBLE'), fat_100g double OPTIONS(NAMEINSOURCE '"fat_100g"', NATIVE_TYPE 'DOUBLE'), saturated_fat_100g double OPTIONS(NAMEINSOURCE '"saturated_fat_100g"', NATIVE_TYPE 'DOUBLE'), saturated_fat_modifier string(3) OPTIONS(NAMEINSOURCE '"saturated_fat_modifier"', NATIVE_TYPE 'CHAR'), salt_100g double OPTIONS(NAMEINSOURCE '"salt_100g"', NATIVE_TYPE 'DOUBLE'), sodium_100g double OPTIONS(NAMEINSOURCE '"sodium_100g"', NATIVE_TYPE 'DOUBLE'), CONSTRAINT "PRIMARY" PRIMARY KEY(idCode) ) OPTIONS(NAMEINSOURCE '"FDBProducts"', UPDATABLE 'TRUE', CARDINALITY '626285'); CREATE FOREIGN TABLE MealConfigs ( fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), Weekday string(1) NOT NULL OPTIONS(NAMEINSOURCE '"Weekday"', NATIVE_TYPE 'ENUM'), MealNumber string(1) NOT NULL OPTIONS(NAMEINSOURCE '"MealNumber"', NATIVE_TYPE 'ENUM'), WeekdaySortOrder long NOT NULL OPTIONS(NAMEINSOURCE '"WeekdaySortOrder"', NATIVE_TYPE 'INT UNSIGNED'), MealName string(45) DEFAULT 'strMeal' OPTIONS(NAMEINSOURCE '"MealName"', NATIVE_TYPE 'VARCHAR'), MealEnabled boolean OPTIONS(NAMEINSOURCE '"MealEnabled"', NATIVE_TYPE 'BIT'), MealStartTime time OPTIONS(NAMEINSOURCE '"MealStartTime"', NATIVE_TYPE 'TIME'), CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile, Weekday, MealNumber), CONSTRAINT fkMealConfigToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"MealConfigs"', UPDATABLE 'TRUE', CARDINALITY '2'); CREATE FOREIGN TABLE NutritionGoal ( fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), Weekday string(1) NOT NULL OPTIONS(NAMEINSOURCE '"Weekday"', NATIVE_TYPE 'ENUM'), BMR_Formula short DEFAULT '2' OPTIONS(NAMEINSOURCE '"BMR_Formula"', NATIVE_TYPE 'TINYINT UNSIGNED'), BMR_Value integer DEFAULT '0' OPTIONS(NAMEINSOURCE '"BMR_Value"', NATIVE_TYPE 'SMALLINT UNSIGNED'), ActivityLevel short DEFAULT '2' OPTIONS(NAMEINSOURCE '"ActivityLevel"', NATIVE_TYPE 'TINYINT UNSIGNED'), CaloriesBurned integer DEFAULT '0' OPTIONS(NAMEINSOURCE '"CaloriesBurned"', NATIVE_TYPE 'SMALLINT UNSIGNED'), WeekGoal short DEFAULT '4' OPTIONS(NAMEINSOURCE '"WeekGoal"', NATIVE_TYPE 'TINYINT UNSIGNED'), GoalCaloriesDelta short DEFAULT '0' OPTIONS(NAMEINSOURCE '"GoalCaloriesDelta"', NATIVE_TYPE 'SMALLINT'), CarbsGoalInPercent short DEFAULT '50' OPTIONS(NAMEINSOURCE '"CarbsGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'), ProteinsGoalInPercent short DEFAULT '30' OPTIONS(NAMEINSOURCE '"ProteinsGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'), FatGoalInPercent short DEFAULT '20' OPTIONS(NAMEINSOURCE '"FatGoalInPercent"', NATIVE_TYPE 'TINYINT UNSIGNED'), CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile, Weekday), CONSTRAINT fkNutritionGoalToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"NutritionGoal"', UPDATABLE 'TRUE', CARDINALITY '1'); CREATE FOREIGN TABLE Profile ( fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'), BodyHeight float DEFAULT '0.000' OPTIONS(NAMEINSOURCE '"BodyHeight"', NATIVE_TYPE 'FLOAT UNSIGNED'), GoalWeight float DEFAULT '0.000' OPTIONS(NAMEINSOURCE '"GoalWeight"', NATIVE_TYPE 'FLOAT UNSIGNED'), Gender string(1) DEFAULT 'm' OPTIONS(NAMEINSOURCE '"Gender"', NATIVE_TYPE 'ENUM'), BirthDate date OPTIONS(NAMEINSOURCE '"BirthDate"', NATIVE_TYPE 'DATE'), ActivityLevel string(17) DEFAULT 'strActive' OPTIONS(NAMEINSOURCE '"ActivityLevel"', NATIVE_TYPE 'ENUM'), CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile), CONSTRAINT fkProfileToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile) ) OPTIONS(NAMEINSOURCE '"Profile"', UPDATABLE 'TRUE', CARDINALITY '2'); --############ Stored Procedures ############ CREATE FOREIGN PROCEDURE srcProc ( IN var_fkProfile long OPTIONS(NATIVE_TYPE 'BIGINT'), IN var_Weekday string(1) OPTIONS(NATIVE_TYPE 'ENUM') ) OPTIONS(NAMEINSOURCE '"srcProc"', UPDATECOUNT '1'); CREATE VIRTUAL PROCEDURE virtProc(customerid INTEGER) RETURNS (name varchar(25), activitydate date, amount decimal) AS BEGIN END CREATE VIRTUAL FUNCTION virtFunParam(theParam INTEGER) RETURNS INTEGER AS BEGIN SELECT theParam; END CREATE VIRTUAL FUNCTION virtFunNoParam() RETURNS INTEGER AS BEGIN SELECT COUNT(Weekday) FROM NutritionGoal WHERE fkProfile = '2'; END --############ Grants ############ GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA my_nutri_diary TO odata; REVOKE EXECUTE,ALTER ON SCHEMA my_nutri_diary FROM odata; GRANT ON TABLE "my_nutri_diary.Account" CONDITION 'Account.uuidUser = LEFT(user(), 36)' TO odata; REVOKE UPDATE,DELETE,EXECUTE ON TABLE "my_nutri_diary.Account" FROM odata; GRANT ON TABLE "my_nutri_diary.BodyWeight" CONDITION 'BodyWeight.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata; GRANT ON TABLE "my_nutri_diary.ConfigOptions" CONDITION 'ConfigOptions.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata; REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.ConfigOptions" FROM odata; GRANT ON TABLE "my_nutri_diary.Diary" CONDITION 'Diary.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata; GRANT SELECT ON TABLE "my_nutri_diary.FDBProducts" TO odata; REVOKE INSERT,UPDATE,DELETE,EXECUTE ON TABLE "my_nutri_diary.FDBProducts" FROM odata; GRANT ON TABLE "my_nutri_diary.MealConfigs" CONDITION 'MealConfigs.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36) )' TO odata; REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.MealConfigs" FROM odata; GRANT ON TABLE "my_nutri_diary.NutritionGoal" CONDITION 'NutritionGoal.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata; REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.NutritionGoal" FROM odata; GRANT ON TABLE "my_nutri_diary.Profile" CONDITION 'Profile.fkProfile in (SELECT Account.idProfile FROM Account WHERE Account.uuidUser = LEFT(user(), 36))' TO odata; REVOKE DELETE,EXECUTE ON TABLE "my_nutri_diary.Profile" FROM odata; REVOKE SELECT,INSERT,UPDATE,DELETE,EXECUTE,ALTER ON SCHEMA sysadmin FROM odata; --############ function and procedure Grants ####### GRANT EXECUTE ON PROCEDURE "my_nutri_diary.virtFunNoParam" TO odata; GRANT EXECUTE ON PROCEDURE "my_nutri_diary.virtFunParam" TO odata; GRANT EXECUTE ON PROCEDURE "my_nutri_diary.srcProc" TO odata; /* ########################################### # END DATABASE svc ########################################### */