Posted by Jon Sacks on October 28, 2005 at 10:40:42:
In Reply to: Sub Keys are NOT unique posted by Jim Guerber on October 23, 2005 at 09:36:54:
I may have missed the gist of this thread, but want to throw in my 2 cents.
The first problem we are dealing with is we don't have an integrated database schema system with Comet. In a SQL enviornment alternate keys are simple since you just tell the database to create a index by a field name, either manually or via a trigger.
This allows a SQL engine to be smart when a users says "select customer_name, salesman_id from customer where salesman = "Jon".
The system looks at the request and sees that in the table customer it has an index called salesman and on it's own decides to use the salesman index to complete the request.
Since the primary keys in the SQL database are by nature unique, the system knows how to store alternate indexes as the alternate index field + the primary key. I don't think that there is any reason in the Comet enviorment to duplicate the specification in the secondary index of the primary key. By nature each alternate index is unique.
The secondary key write should only contain the data of the secondary key with the primary key appended. So if your primary key was customer number and you secondary key was salesman_id the salesman_is is all you should have to specify in the seconadary key write since you will append customer_number (your primary key) to the record anyway.
My original vision that I communicated was not to put the requirment for maintaining secondary keys on the programmer but to have the database manage the secondary key with a trigger.
Using a database management tool you would specify the offset in the table for the creation of the secondary key(s) for a file.
The table would look something like this.
Primary Table: C1A
1st Alternate Key: SALESMAN_ID POS,LEN: 32,2
2nd Alternate Key: CUST_CITY POS,LEN: 36,30
3rd Alternate Key: CUST_STATE POS,LEN: 37,2
When the system sees a write to C1A it creates the alternate keys, when the system sees a read on C1A and the like:
Read (C1A,xxxxx),KEY="J1", ALTKEY="SALESMAN_ID"
The system would know what alternate key table to use, this would get you very close to being able to create a SQL type approach to the database.
select * from C1A where SALESMAN_ID = "J1";
JSACKS<<