Referencing Thoughts in esProc
1 Foreign key referencing
The foreign key is a common concept for various relational databases. In a relational table, you can define one or multiple fields as the foreign key. The foreign key field values can be associated with the data in another table. For example, in the game scoring report, player scores are usually recorded with the player ID. To get the details of this player from another player profile table using the player ID, you can query in the player profile table according to the player ID. In this case, the player ID field can be set as the foreign key.
The foreign key can associate two relational tables and help ensure the data consistency, so as to facilitate some cascade operations. Take the above game scoring report for example. If defining the player ID as the foreign key field, then the player name, country, age, and other data can be queried at the same time while getting the game scoring record of the player.
In this example of foreign key definition, it is actually equal to referencing the records n the player data table from the game scoring record table. The core idea of foreign key concept is to reference another record.
In esProc, unlike the database, there is no particular requirement on the data type of record fields. In other words, esProc users can get the value at will. Thanks to this convenience, in the esProc TSeq, fields can be directly assigned with record for referencing. The foreign key associating can thus be established naturally and straightforwardly.
For example, create an employee education background table comprising the employee ID field and education field. This example is designed to illustrate the way to reference the esProc foreign key, and the education field is simply set through the looping in proper order:
In esProc, not only the record of other TSeq but also that of the current TSeq can be assigned to the foreign key field.
In esProc, besides the function A.derive() for assign a certain field of record with the objects of another entire record when adding fields to the newly-generated TSeq; the function A.run() can be used to assign value to the existing fields. For example:As can be seen, the referencing foreign key of records can make the query syntax of multi-table association more concise and readable with an improved computing speed.
2 Referencing set
In assignment, esProc fields can be assigned with records or sets in a similar way - referencing the set in the foreign key field. In most cases, what being referenced in the foreign key fields is the RSeq composed of records from other TSeqs. Please notice that the “Foreign Key Field†is not the same thing as the foreign key field in the database. It is much more like the query to reference the sub table data from the primary table with SQL.
Like the foreign key referencing, fields can be added when generating the TSeq. With the function derive, the foreign key field can be assigned with RSeq, for example:By referencing sets in foreign key fields, the syntax of multi-table-associated querying can be more clear and intuitive. In the TSeq of A4, the sole purpose of introducing the field Count is to have a more clear view of the result. This field is not used when filtering records in A5.
When referencing the set in the TSeq field, the switch function cannot be used, but the function A.run() can be used to assign value to the existing fields. For example: