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. A3 gets the record of the employee whose ID is 4:

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 return only the modified record; and use @i option to add only the modified record. Actually, primary key values must not be changed during composite table data updating, otherwise the old records will still be retained while new records are added.

Next, let’s look at how to update a batch of modified records. B3 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:

B4 copies these records and B5 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.

 

 

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.

Records in a composite table are ordered by primary key values. They should not be deleted at will. When a composite table is retrieved, use T.close() function to close it.

Different from the case with a composite table, we can use update mark field in multizone composite table to delete records. The update mark field is a field after the key field(s). It represents the update action type during composite table update execution – null representing to-be-added, true representing to-be-deleted and false representing to-be-modified. With an update mark field, we can consider retrieving data from a multizone composite table by first updating the update marks. Here we use an example to illustrate this:

	A	B
1	=file("d:/file/dw/employeesMark.ctx":[1])	=A1.create@yd(#EID,Mark,Dept,Gender,Name;1)
2	=file("d:/file/dw/employees.ctx")	=A2.open().cursor()
3	=B2.new(EID,Mark,Dept,Gender,Name)	>B1.append(A3)
4	=A1.open().cursor().fetch()
5	[0,true,null,null,null,2,false,Technology,M,Joe Smith]
6	=create(EID,Mark,Dept,Gender,Name).record(A5)	=A6.cursor()
7	=file("g:/file/dw/employeesMark.ctx":[2])	=A7.create@yd(#EID,Mark,Dept,Gender,Name;2)
8	>B7.append@i(B6)
9	=A7.open().cursor().fetch()
10	=file("d:/file/dw/employeesMark.ctx":[1,2])
11	=A10.open().cursor@w().fetch()	=A10.open().cursor().fetch()

A1 creates a zone table for the multizone composite table. B1 specifies the zone table number to create a composite table. Different from employees.ctx, an update mark field named Mark is added after key field EID. A3 adds Mark field to the cursor generated based on composite table employees.ctx. The Mark field plays a role when B3 stores data in zone table 1 of the multizone composite table. According to A4, below is data in the zone table:

Compared with the previous employees.ctx, Mark field is added, whose values are nulls.

A6 generates two update records that store data update information, as shown below:

Where the record whose EID is 0 is Marked as ture, which means to-be-deleted; the record whose EID is 2 is Marked as false, which means to-be-modified. A8 stores the update records in zone table 2, which has the same strucre as zone table 1. We can view the update records in A9:

A11 retrieves data from the two zone tables at the same time through a multizone composite table. The cursor() function works with @w option when generating a cursor to take into account the update mark field Mark. Now A11 has data as follows:

If the cursor() function does not work with @w option, it will still retrieve fields from zone tables as usual but it will merge-sorts records according to the key field. In the result, we can view the original status of the update mark field: