-
1. Re: foreign keys in odata
Mirco,
This is side effect of using table name for relationship instead of using the FK name (this is a design mistake). This only affects our OData V2 version. Did you try Teiid's OData V4? I would recommend using that, as V2 is being deprecated/removed in Teiid 9.x releases. V4 also has much more extensive feature set.
The corresponding V4 URL would be
http://prdteiid01:8080/odata4/VDB.1/Entities/Table2(1234)
Ramesh..
-
2. Re: foreign keys in odata
Ramesh,
we're experimenting Odata V4 version, and as you said we solved the problem about the foreign keys.
I've one more question about this subject:
from the Odata specification I read that while defining an Entity, it's possible to add the "Partner" attribute in the "NavigationProperty" property in order to link two entities.
Is it possible with Teiid and if yes, how ?
thanks,
Mirco
-
3. Re: foreign keys in odata
Mirco,
The relationship between two tables will be modeled as NavigatoinProperty in the Entity in V4. So, if you have defined two related virtual tables, then define FK betweek them. Will that satisfy what you are looking for, or were you thinking something else?
Ramesh..
-
4. Re: foreign keys in odata
Hi Ramesh,
that's my scenario:
EmployeeMasterEntity (PK: EmployeeID) is related to EmployeeActionEntity (PK: EMployeeActionKey) as 1 to n
We have created a Foreing Key in the EmployeeActionEntity view which refers to the PK of EmployeeMasterEntity
The odata2 behaviour is correct
For EmployeeID = 3 there are 3 EmployeeActionEntities:
{
"d" : {
"results" : [
{
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140430-20120101-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -20140430-20120101-0", "EmployeeID" : 3, "ActionType" : "Hiring", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1325376000000)\/", "ValidUntil" : "\/Date(1398816000000)\/", "SequenceNumber" : 0, "ModifiedBy" : "DEMARIAM", "ModificationDate" : "\/Date(1322179200000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140430-20120101-0')/EmployeeMasterEntity"
}
}
}, {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140531-20140501-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -20140531-20140501-0", "EmployeeID" : 3, "ActionType" : "Organizational reassignment", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1398902400000)\/", "ValidUntil" : "\/Date(1401494400000)\/", "SequenceNumber" : 0, "ModifiedBy" : "ZUCCHIATTIS", "ModificationDate" : "\/Date(1398729600000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -20140531-20140501-0')/EmployeeMasterEntity"
}
}
}, {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -99991231-20140601-0')", "type" : "Entities.EmployeeActionEntity"
}, "EmployeeActionKey" : "10009- - - -99991231-20140601-0", "EmployeeID" : 3, "ActionType" : "Organizational reassignment", "EmploymentStatus" : "Active", "ValidFrom" : "\/Date(1401580800000)\/", "ValidUntil" : "\/Date(253402214400000)\/", "SequenceNumber" : 0, "ModifiedBy" : "DEMARIAM", "ModificationDate" : "\/Date(1400544000000)\/", "EmployeeMasterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeActionEntity('10009- - - -99991231-20140601-0')/EmployeeMasterEntity"
}
}
}
]
}
}
For EmployeeActionKey = '10009-%20-%20-%20-20140430-20120101-0' I can see the data of the corresponding EmployeeMasterEntity (id = 3)
{
"d" : {
"__metadata" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)", "type" : "Entities.EmployeeMasterEntity"
}, "EmployeeID" : 3, "ValidityDate" : "\/Date(1451606400000)\/", "CID" : 10009, "BadgeNumber" : 35, "BadgeCode" : null, "WindowsUser" : "GUARINO_G", "SAPUser" : "GUARINOG", "Salutation" : "ING.", "FirstName" : "Giuseppe", "LastName" : "Guarino", "Gender" : "M", "Initials" : "GG", "InternalPhone" : "0916419259", "Supervisor" : 5, "DeputySupervisor" : 0, "Delegate" : 5, "Company" : "1000", "Department" : "10000001", "Position" : "50000035", "CostCenter" : "1-BIC", "JobTitle" : "Chief Executive Officer", "AdditionalJobDescription" : "Deputy Chairman of the Board", "HiringDate" : "\/Date(457488000000)\/", "LeaveDate" : null, "EmployeeType" : "20", "SalaryCategory" : "STDIN", "EmailAddress" : "g.guarino@casale.ch", "Active" : true, "EnableTimeSheet" : false, "SendHoursToSAP" : true, "SendHoursToSAPSince" : null, "BudgetType" : "Internal", "RequiresApproval" : false, "HeadOfOrganizationalUnit" : true, "AutocompleteWorkloads" : false, "TimeSheetDelegate" : 0, "TimeSheetLockDate" : "\/Date(1062412722920)\/", "OfficeLocation" : "", "HoursPerMonth" : "173.33", "HoursPerWeek" : "40.00", "HoursPerDay" : "8.00", "DaysPerWeek" : "5.00", "HoursPerYear" : "2080.00", "BirthDate" : "\/Date(-697075200000)\/", "Nationality" : "CH", "VisitCardQuantity" : "200", "PhoneOnVisitCard" : false, "MobileOnVisitCard" : false, "PostalAddress" : "Via Ing. Luigi Casale, 7", "PostalCode" : "6932", "City" : "Breganzona", "State" : "TI", "Country" : "CH", "PrivatePhone" : " ", "FamilyStatus" : "Married", "FamilyStatusSince" : "\/Date(-2208988800000)\/", "NumberOfChildren" : "2", "Notes" : "", "thumbnailPhoto" : "..........=\r\n", "SAPData" : true, "AbsenceEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/AbsenceEntity"
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "EmployeeActionEntity" : {
"__deferred" : {
}
}, "EmployeeFamilyEntity" : {
"__deferred" : {
}
}, "CompanyEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CompanyEntity"
}
}, "CostCenterEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CostCenterEntity"
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
}
}, "EmployeeTypeEntity" : {
"__deferred" : {
}
}, "CountryEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/CountryEntity"
}
}, "PositionEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/PositionEntity"
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
}
}, "EmployeeMasterEntity" : {
"__deferred" : {
}
}, "GeneralCostEntity" : {
"__deferred" : {
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
}
}, "OrganizationalUnitEntity" : {
"__deferred" : {
}
}, "ProjectCostEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectMasterEntity" : {
"__deferred" : {
}
}, "ProjectRevenueEntity" : {
"__deferred" : {
}
}, "ProjectStructureEntity" : {
"__deferred" : {
}
}, "TimeEventEntity" : {
"__deferred" : {
"uri" : "http://casteiid01.casalelug.ch:8080/odata/CASALE.1/Entities.EmployeeMasterEntity(3)/TimeEventEntity"
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
}
}, "WorkloadsAssignmentEntity" : {
"__deferred" : {
}
}
}
}
With odata4 we get some problems. First of all we have to specify the name of the foreign key instead of the name of table as inodata2 (but this is not a real problem)
We correctly see the three records:
{"@odata.context":"$metadata#EmployeeActionEntity","value":[{"EmployeeActionKey":"10009- - - -20140430-20120101-0","EmployeeID":3,"ActionType":"Hiring","EmploymentStatus":"Active","ValidFrom":"2012-01-01","ValidUntil":"2014-04-30","SequenceNumber":0,"ModifiedBy":"DEMARIAM","ModificationDate":"2011-11-25"},{"EmployeeActionKey":"10009- - - -20140531-20140501-0","EmployeeID":3,"ActionType":"Organizational reassignment","EmploymentStatus":"Active","ValidFrom":"2014-05-01","ValidUntil":"2014-05-31","SequenceNumber":0,"ModifiedBy":"ZUCCHIATTIS","ModificationDate":"2014-04-29"},{"EmployeeActionKey":"10009- - - -99991231-20140601-0","EmployeeID":3,"ActionType":"Organizational reassignment","EmploymentStatus":"Active","ValidFrom":"2014-06-01","ValidUntil":"9999-12-31","SequenceNumber":0,"ModifiedBy":"DEMARIAM","ModificationDate":"2014-05-20"}]}
What we cannot resolve is how to get the data corresponding to the EmployeeMasterEntity having the EmployeeActionEntityKey = '10009-%20-%20-%20-20140430-20120101-0'
We cannot create a foreign key in EmployeeMasterEntity because there we have not a field related to EmployeeActionEntity
thanks,
Mirco
-
5. Re: foreign keys in odata
Micro,
Please read about $expand option in the OData V4, with that option you can issue
Ramesh..
-
6. Re: foreign keys in odata
Ramesh,
in that way I can see all the values of EmployeeActionEntity knowing the key of EmployeeMasterEntity
what I' trying to to is to see is all the values of EmployeeMasterEntity starting from the key of EmployeeActionEntity
eg: like in odata2, knowing the PK of EmployeeActionEntity, what is the data of the corresponding EmployeeMasterEntity ?
odata2
odata4
Mirco
-
7. Re: foreign keys in odata
In OData V2 it was based on relationships, it was most of time forced to use both directions with entities. With V4 is about navigability, I choose to implement one direction. I have not tried this, but how about creating a FK on EmployeeMaterEntity to the EmployeeActionEntity. I know the relationship is implied, for implementation I would need a navigation name, which would be this FK's name.
Ramesh..
-
8. Re: foreign keys in odata
Ramesh,
I cannot create a FK in that direction because I have no fields in EmployeeMasterEntity which identifies the key in EmployeeActionEntity
to make it simpler, just have a look on this public odata4 examples:
Product is a table like my EmployeeActionEntity
<EntityType Name="Product">
<Key>
<PropertyRef Name="ProductID"/>
</Key>
<Property Name="ProductID" Type="Edm.Int32" Nullable="false" p5:StoreGeneratedPattern="Identity"/>
<Property Name="ProductName" Type="Edm.String" Nullable="false" MaxLength="40"/>
<Property Name="SupplierID" Type="Edm.Int32"/>
<Property Name="CategoryID" Type="Edm.Int32"/>
<Property Name="QuantityPerUnit" Type="Edm.String" MaxLength="20"/>
<Property Name="UnitPrice" Type="Edm.Decimal" Precision="19" Scale="4"/>
<Property Name="UnitsInStock" Type="Edm.Int16"/>
<Property Name="UnitsOnOrder" Type="Edm.Int16"/><Property Name="ReorderLevel" Type="Edm.Int16"/>
<Property Name="Discontinued" Type="Edm.Boolean" Nullable="false"/>
<NavigationProperty Name="Category" Type="NorthwindModel.Category" Partner="Products">
<ReferentialConstraint Property="CategoryID" ReferencedProperty="CategoryID"/>
</NavigationProperty>
<NavigationProperty Name="Order_Details" Type="Collection(NorthwindModel.Order_Detail)" Partner="Product"/>
<NavigationProperty Name="Supplier" Type="NorthwindModel.Supplier" Partner="Products">
<ReferentialConstraint Property="SupplierID" ReferencedProperty="SupplierID"/>
</NavigationProperty>
</EntityType>
Category is like my EmployeeMasterEntity
<EntityType Name="Category">
<Key>
<PropertyRef Name="CategoryID"/>
</Key>
<Property Name="CategoryID" Type="Edm.Int32" Nullable="false" p5:StoreGeneratedPattern="Identity"/>
<Property Name="CategoryName" Type="Edm.String" Nullable="false" MaxLength="15"/>
<Property Name="Description" Type="Edm.String" MaxLength="max"/>
<Property Name="Picture" Type="Edm.Binary" MaxLength="max"/>
<NavigationProperty Name="Products" Type="Collection(NorthwindModel.Product)" Partner="Category"/>
</EntityType>
It seems that the two tables are related through NavigationProperty and Partner
In fact I can see all the Categories of a certain Product
http://services.odata.org/V4/Northwind/Northwind.svc/Products(10)/Category
or viceversa the products of a certain Category
http://services.odata.org/V4/Northwind/Northwind.svc/Categories(8)/Products
-
9. Re: foreign keys in odata
You can log enhancement JIRA for this, I will see if I can get into 9.1