How to update a many to many table

[rpoyner]

My listGrid comboBoxItem struggle continues.

I have a 2 column SQL table(spaceAssignments) that consists of foreign keys id_People and id_Rooms. The table serves to assign each person in the people table to 0 or more rooms in the rooms table. I have a listGrid that displays a person's assigned rooms by building name(pulled from the related building table) and room number. When I double click a record in the grid I can change the building and roomNumber, which is enough information to generate a new value for id_Rooms. When I press enter on my changed row I would like to run an SQL statement like:

update spaceAssignments set id_People=<current person>, id_Rooms=<new room> where id_People=<current person> and id_Rooms=<current room>;

Which would have the effect of assigning the person to a different room.

This does not seem to be what my current code is doing. So my question is can I add something to my listGrid/comboBox/datasource code to get the desired behavior? Should I write a datasource.transformRequest() function? Pursue some other solution like over-riding the save behavior of the grid?


When I hit enter to save changes to an edited record in the grid I get this in the server log:

=== 2017-06-08 15:32:01,051 [ec-8] INFO RequestContext - URL: '/isomorphic/IDACall', User-Agent: 'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.133 Safari/537.36': Safari with Accept-Encoding header
=== 2017-06-08 15:32:01,054 [ec-8] DEBUG XML - Parsed XML from (in memory stream): 2ms
=== 2017-06-08 15:32:01,055 [ec-8] DEBUG RPCManager - Processing 1 requests.
=== 2017-06-08 15:32:01,056 [ec-8] DEBUG RPCManager - Request #1 (DSRequest) payload: {
criteria:{
id_People:12331,
id_Rooms:10204
},
values:{
id_People:12331,
id_Rooms:10204,
code_Buildings:"0047",
buildingNameShort:"Chem",
roomNumber:"8333"
},
operationConfig:{
dataSource:"roomAssignments",
repo:null,
operationType:"update",
textMatchStyle:"exact"
},
componentId:"ListGrid3",
appID:"builtinApplication",
operation:"roomAssignments_update",
oldValues:{
roomTypeFull:"Lab-Instruction",
code_Buildings:"0408",
roomNumber:"B41",
buildingNotes:"",
roomNotes:"",
id_Rooms:10009,
roomProgram:"Instr",
buildingNameLong:"Engineering Hall",
id_People:12331,
buildingNameShort:"Engr Hall",
roomDescription:"Remodeling in progress - Instruction",
occupantCapacity:0,
buildingNameAbbreviated:"EH",
roomSubtype:"Wet",
roomAreaSqFt:795
}
}


Both the criteria and the values in the DSRequest have id_Rooms=<new room id>. Should the criteria have id_Rooms=<old room id> ?


The listGrid code:

isc.ListGrid.create({
ID:"ListGrid3",
autoDraw:false,
autoFetchData:true,
canEdit:true,
dataSource:"roomAssignments",
fields:[
{
name:"id_People"
},
{
name:"code_Buildings",
canEdit:true,
title:"Building",
width:80,
displayField:"buildingNameShort",
valueField:"code_Buildings",
editorType:"ComboBoxItem",
editorProperties : {
optionDataSource:"buildings"
}
},
{
name:"id_Rooms",
title:"Room #",
width:70,
editorType:"ComboBoxItem",
displayField:"roomNumber",
valueField:"id_Rooms",
canEdit:true,
editorProperties:{
optionDataSource:"rooms",
displayField:"roomNumber",
valueField:"id_Rooms",
getPickListFilterCriteria : function () {
var selectedBuilding = this.grid.getEditedRecord(this.rowNum);
console.log("building",selectedBuilding);
return {
code_Buildings:selectedBuilding.code_Buildings
};
}
}
}
]
})

[Blama]

Hi,

I assume your DS does not have a primaryKey.
Your options are:

• Create a synthetic PK field (auto increment, sequence, ...) This is the default and best solution. Your WHERE will be WHERE id=123
• Assign both fields to be PK (unlikely you want this). Your WHERE will be like you suggested.

Otherwise, please show your DS.

Best regards
Blama

[rpoyner]

Both id_People and id_Rooms are primary keys. I don't like the idea of a synthetic key. With the synthetic key you make duplicate assignments valid as illustrated in this example

synthKey | id_People | id_Rooms
1 | 1 | 1
2 | 1 | 1

Person 1 is now assigned to room 1 twice.

Thanks
RP

This is the ds.xml

<DataSource ID="roomAssignments" dataFormat="iscServer" serverType="sql" dataSourceVersion="1" dbName="personnel" tableName="spaceAssignments" quoteTableName="false" tableCode="2c03e92be01b135e99f1de9e49cd2a32" allowAdvancedCriteria="true" generatedBy="v11.0p_2017-05-15/PowerEdition Deployment 2017-05-15">
<fields>
<field name="id_People" type="integer" title="5-digit person ID of room occupant" primaryKey="true" columnCode="b2b5988d1899d969b68e44287ac95ab6">
<validators>
<Validator>
<type>isInteger</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="id_Rooms" type="integer" title="5-digit room ID" primaryKey="true" foreignKey="rooms.id_Rooms" columnCode="0d08984a04a444ba69f4db1460b4862e">
<validators>
<Validator>
<type>isInteger</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="code_Buildings" type="text" length="4" title="4-digit UW Building Code number" includeFrom="rooms.code_Buildings" columnCode="b526a7745b9a966a2b5e63a7b60d508c">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomNumber" type="text" length="10" title="Room number" includeFrom="rooms.roomNumber" columnCode="39585a036dfcafd723ea0d2b627a8060">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomTypeFull" type="text" length="20" title="Room Type" includeFrom="rooms.roomTypeFull" columnCode="c05c3e0b0782f722b576079dffada120">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomSubtype" type="text" length="20" title="COE room subtype" includeFrom="rooms.roomSubtype" columnCode="52c4fc3fcc4904d8014f039a5959a5f6">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomProgram" type="text" length="20" title="COE room program" includeFrom="rooms.roomProgram" columnCode="0a0a94eff58ad8144277a9868112b265">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomDescription" type="text" length="40" title="Room description" includeFrom="rooms.roomDescription" columnCode="098889dd07c12c32540a3c663d742e4a">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomNotes" type="text" length="65535" title="Free-form notes" includeFrom="rooms.roomNotes" columnCode="fddb3ada21ed6f4dffa88e63ff1f815e">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="roomAreaSqFt" type="float" title="Room area in square feet" includeFrom="rooms.roomAreaSqFt" columnCode="398d47987b8f3cdb80058e80b98f4e97">
<validators>
<Validator>
<type>isFloat</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="occupantCapacity" type="integer" title="Room occupant capacity (for offices)" includeFrom="rooms.occupantCapacity" columnCode="4a680291249a7b5697b4f6aeaa44c61e">
<validators>
<Validator>
<type>isInteger</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="buildingNameLong" type="text" length="40" title="Official UW building name" includeFrom="rooms.buildingNameLong" columnCode="5cc0b6c76044f611a5e49d603edf448e">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="buildingNameShort" type="text" length="20" title="Shorter building name for reports" includeFrom="rooms.buildingNameShort" columnCode="a9b4eb82f06286610255fbdfa1fda6d5">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="buildingNameAbbreviated" type="text" length="10" title="Common abbreviation for building" includeFrom="rooms.buildingNameAbbreviated" columnCode="5950630ceb33be25d01db5e72cf55f4d">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
<field name="buildingNotes" type="text" length="65535" title="Free-form notes" includeFrom="rooms.buildingNotes" columnCode="815dc9dbf01dd61f1a5a1230825230d6">
<validators>
<Validator>
<type>isString</type>
<stopIfFalse>true</stopIfFalse>
<typeCastValidator>true</typeCastValidator>
</Validator>
</validators>
</field>
</fields>
</DataSource>

[Blama]

Hi rpoyner,

please see the primaryKey docs:

Indicates either that this field holds a value unique across all records in this DataSource, or that it is one of a number of fields marked as primary keys, and the combination of the values held in all of those fields is unique across all records in the DataSource. Note that the latter usage - so-called "composite" or "multipart" keys - is intended for support of legacy databases only: if you are able to choose an approach, Isomorphic recommends the use of one primaryKey field per DataSource, and ideally this field should be of type "sequence". If you have control of the underlying tables, there is nothing to stop you from adding a field like this (a so-called "synthetic" or "surrogate" key), even for tables that already have a set of columns that could combine to make a composite key (a so-called "natural" key). Also, it is neither necessary nor correct to use a composite primaryKey because you want to enforce uniqueness across that combination of fields. You can achieve that by declaring a unique constraint in the table schema, or use an isUnique validator with validator.criteriaFields, or both; there is no need to use a composite key to enforce uniqueness

You can define unique constraints / indices in the database anyway and also isUnique-validators in the .ds.xml.
So this should not be the reason not to use synthetic keys.

Isomorphic: Out of interest: What does the tag typeCastValidator do? And is it necessary to have the validators at all, if you define type="" in the field-tag?

Best regards
Blama

[Isomorphic]

That's an internal flag that shouldn't be appearing in your .ds.xml - in fact none of those validators should be appearing at all, as they are all implicit as part of the type definition (and this is what the flag means - it's a validator that checks and/or converts to the target type, which is sometimes done without running other validators).

[rpoyner]

I'm somewhat blindly using the ds.xml generated by visual builder. I've found that removing the many empty tags and empty foo="" values helps a lot. It looks like I did not do the clean up on the code posted above.

[rpoyner]

Blama,

You are of course right about the need for a synthetic key. On thinking about the problem it occurs to me that the synthetic key prevents trouble if 2 users are updating a record at the same time. I added the unique constraint in MySQL. I like having the integrity protections at the database level when possible and smartclient seems to work fine with it. When I try to save a duplicate room assignment it returns an fairly informative error about violating uniqueness.