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.create()

=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()

=B2.cursor(;EID<20).fetch()

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:

B7 updates the entity table stable using the modified records in B5. B8 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.create()

=A2.attach(stable)

3

=A2.cursor().fetch()

=B2.cursor().fetch()

4

=A3.select(EID<1)

=B3.select(EID<20)

5

>A2.delete(A4)

=B2.delete@n(B4)

6

=A2.cursor().fetch()

=B2.cursor().fetch()

7

 

>B2.update(B4)

A5 selects the to-be-deleted records:

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

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

B6 uses @n option in the delete function to return the deleted record; the result is the same as B5. Then after execution B7 again retrieves records from stable:

A comparison with the retrived records in the previous example shows that the selected records in B5 are deleted.

B7 adds the deleted data back into the attached table to retain the wholeness of the data table.

If an entity table has indexes, the update or delete action over it will be automatically performed.

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

We can specify the read/write passwords when generating a composite table. They are needed whenever a user wants to access the composite table. For example:

 

A

B

1

=create(Num,Accu)

0

2

>100.run(A1.insert(0,#,B1=B1+#))

 

3

=file("D:/file/dw/numbers.ctx")

=A3.create(#Num,Accu;;"wpwd":"rpwd")

4

 

>B3.append(A1.cursor())

5

=A3.create(;;"wpwd")

=A5.cursor().fetch()

6

=create(Num,Accu).record([101,5151,102,5253])

 

7

>A5.append(A6.cursor())

=A5.cursor().fetch()

The first two lines create a simple data table consisting of a field containing positive integers and one containing cumulative sums. Below is A1’s result:

B3 uses f.create(C1,C2,…;K;pw:pr) function to specify the read/write passwords when generating the composite table. The read password is "rpwd" and the write password is "wpwd". B4 writes A1’s data to the base table. A5 uses f.create(;;p) function to enter password p to access the composite table. That is a writing password, so the composite table can be read or modified. The data B5 gets from the composite table’s base table is the same as A1’s data, as shown below:

A6 generates another two records:

A7 appends the two records to the base table. B7 fetches records from A5:

If A5 accesses the composite table with the read password, it can get the read-only permission only. In this case, write permission is denied and error will be reported if A7 tries to add data to the base table:

The passwords for a composite table can’t be modified. To change or remove them, we have to re-generate the table:

 

A

B

1

=file("F:/file/dw/numbers.ctx")

 

2

=A1.create(;;"rpwd")

=A2.cursor().fetch()

3

=A1.create@y(#Num,Accu)

>A3.append(B2.cursor())

4

=A1.create()

=A4.cursor().fetch()

A2 opens an encrypted composite table with its read password. B2 fetches data from its base table. A3 re-generates an unencrypted composite table; @y option is used to re-create the composite table file. Without the option, the program will deny re-creating an existing namesake composite file, terminate the execution and report an error.

B3 appends B2’s data to the base table of the newly-created composite table. A4 opens the composite table again without a password. B4 fetches data from A4:

 

The f.append(f', p) function adds data in an unencrypted composite table file f' to encrypted composite table file f by entering the latter’s write password p. The two coposite table files should have the same structure. For example:

 

A

B

1

=file("F:/file/dw/numbersNew.ctx")

=A1.create@y(#Num,Accu;;"wpwd":"rpwd")

2

=create(Num,Accu).record([0,0])

>B1.append(A2.cursor())

3

=file("F:/file/dw/numbers.ctx")

>A1.append(A3,"wpwd")

4

=A1.create(;;"rpwd")

=A4.cursor().fetch()

The first line create an encrypted composite table file numbersNew.ctx, which has the same structure with numbers.ctx, the unencrypted composite table file in the previous program. A2 generates a record and B2 appends it to the composite table numbersNew.ctx.

 

B3 adds data in the composite table numbers.ctx to another composite table numbersNew.ctx. A check of B4’s result tells us that the data is appended at the end of the latter table:

 

The use of @x option in f.append(f', p) function will delete composite table file f' after data is successfully appended.