Maintaining composite tables

Like handling normal table sequences, we can add, modify or delete records to/in/from a composite table’s entity table. An example will tell you how to modify records in an entity table:

	A	B
1	=file("D:/file/dw/employees.ctx")
2	=A1.open()	=A2.attach(stable)
3	=A2.cursor(;EID==3).fetch()	=B2.cursor(EID,OCount,OAmount;EID<20).fetch()
4	=A3.derive()	=B3.derive()
5	>A4.run(EID=0)	>B4.run(OCount=0)
6	=A2.update@n(A4)	>B2.update(B4)
7	=A2.cursor().fetch(100)	=B2.cursor(;EID<20).fetch()
8	>A2.close()

First open the composite table employees.ctx used in Generating a composite table. A2 and B2 respectively open the base table and the attached table stable. A4 gets the record of the employee whose ID is 3:

For the convenience of comparison, A4 copies A3’s data. Then A5 changes the EID and here’s A4’s result:

A6 calls the T.update(P) function to update the entity table T. Here @n option is used to return the original version of the modified record, which is the record in A4. A7 re-gets data from the base table:

During execution, T.update(P) function updates the modified record by comparing the key values of entity table T and the record P. As we can see from the above result, the update doesn’t delete the original version of the record whose EID is changed, it appends the modified record to the entity table instead while the EID keeps its order. Use @u option in the function to retun only the modified record; and use @i option to add only the modified record.

Next, let’s look at how to update a batch of modified records. B4 retrieves seller records where EID is less than 20. In order to keep a consistent structure, the function should get stable’s own field. Here’s the result:

 

B5 copies these records and B6 modifies Ocount values:

B6 updates the entity table stable using the modified records in B5. B7 re-gets the seller records where EID is less than 20:

The records have been modified. Use T.delete(P) function to delete records:

	A	B
1	=file("D:/file/dw/employees.ctx")
2	=A1.open()	=A2.attach(stable)
3	=A2.cursor().fetch(100)	=B2.cursor().fetch(100)
4	=A3.select(EID<1)	=B3.select(EID<20)
5	>A2.delete(A4)	=B2.delete@n(B4)
6	=A2.cursor().fetch(100)	=B2.cursor().fetch()
7	>A2.close()

A4 selects the to-be-deleted records:

A5 deletes the selected records and A6 retrieves records in the base table:

B4 selects to-be-deleted records where EID is less than 20 from the attached table stable:

B6’s call of delete() function will delete certain records from the attached table without changing the composite table’s other records.

 

When performing update or delete action on an entity table that has indexes and that the current operation involves related records, the indexes will be automatically updated.

When a composite table is retrieved, use T.close() function to close it.