Systems and methods for the implementation of unordered and ordered collections in a data store

• US 7,437,371 B2
• Filed: 12/30/2004
• Issued: 10/14/2008
• Est. Priority Date: 02/10/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Seeliger, Oliver Nicholas, Stefani, Stefano, Tomic, Dragan, Yu, Wei, Denuit, Bruno H. M., Sezgin, Beysim, Venkatesh, Ramachandran, Blakeley, Jose A., Cunningham, Conor J., Rathakrishnan, Balaji, Altudov, Denis Y., Churin, Denis
• Primary Examiner(s): Fleurantin; Jean B
• Application Number:
  US11/027,416
• Publication Number:
  US 20050177551A1
• Time in Patent Office: 1,384 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206, 717/104, 717/108, 717/165
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases
• CPC Class Codes: G06F 16/289 Object oriented databases
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

Several embodiments of the present invention are directed to systems and methods for extending the UDT framework of an extended relational data store (ERDS) to include support for unordered collections (multisets) and ordered collection (lists). More specifically, several embodiments of the present invention use an UDT infrastructure, CLR generics, and a new UNNEST operator to create and utilize a special type abstraction for collections that is simultaneously a scalar and a relation. As a scalar, this collection type can be processed by all parts of the data store engine that understand scalars (including but not limited to the client stack) and, as a relation, this collection type is queriable like any other type of relation.

Claims

1. A relational data store method, said method comprising:

receiving an operation to be applied to a collection in an extendable relational data store, the collection is derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is a structured query language (SQL) scalar type, the collection being processable as a scalar and the same collection being queriable as a relation, wherein the operation is a scalar operation or a query operation;

for scalar operations applied to said collection, processing said collection as a scalar;

for query operations applied to said collection, querying said collection as a relation,

wherein querying said collection as a relation comprises;

recognizing said collection as a queriable relation;

unnesting said collection to create a queriable object; and

performing said query operations on said queriable object; and

storing the results of the processing or querying.

2. The method of claim 1 wherein said generic user-defined type is a system user-defined type.

3. The method of claim 2 further comprising:

registering said collection type with said relational data store; and

registering a user-defined type corresponding to said collection with said relational data store.

4. The method of claim 3 wherein said element of querying said collection as a relation comprises:

unnesting said collection into a relational queriable object comprising a first column wherein each row in said first column corresponds to a single member of said collection; and

querying said relational queriable object.

5. The method of claim 4 wherein said relational queriable object comprises a second column, each row in said second column corresponds to a single member of said collection, each row having a value corresponding to an order of each said member in said ordered collection.

6. The method of claim 2 implemented by a hardware control device, said hardware control device comprising means for implementing each element of said method.

7. A computer-readable storage medium comprising computer-readable instructions for implementing a relational data store method, said computer-readable instructions comprising instructions for:

receiving an operation to be applied to a collection in an extendable relational data store, the collection derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is a structured query language (SQL) scalar type, the collection being processable as a scalar and the same collection being queriable as a relation, wherein the operation is a scalar operation or a query operation;

processing said collection as a scalar when the scalar operation is applied to said collection;

querying said collection as a relation when the query operation is applied to said collection, wherein querying said collection as a relation comprises;

recognizing said collection as a queriable relation;

unnesting said collection to create a queriable object; and

performing said query operation on said queriable object; and

storing the results of the processing or querying.

8. The computer-readable storage medium of claim 7 further comprising instructions whereby said generic user-defined type is a system user-defined type.

9. The computer-readable storage medium of claim 8 further comprising instructions for:

registering said collection type with said relational data store; and

registering a user-defined type corresponding to said collection with said relational data store.

10. The computer-readable storage medium of claim 9 further comprising instructions whereby said element of querying said collection as a relation comprises:

unnesting said collection into a relational queriable object comprising a first column wherein each row in said first column corresponds to a single member of said collection; and

querying said relational queriable object.

11. The computer-readable storage medium of claim 10 further comprising instructions whereby said relational queriable object comprises a second column, each row in said second column corresponds to a single member of said collection, each row having a value corresponding to an order of each said member in said ordered collection.

12. A system for implementing an extendable relational data store, said system comprising a processor and a storage for:

receiving an operation to be applied to a collection in the extendable relational data store, the collection derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is a structured query language (SQL) scalar type, the collection being processable as a scalar and the same collection being queriable as a relation, wherein the operation is a scalar operation or a query operation;

processing said collection as a scalar when the scalar operation is applied to said collection

queying said collection as a relation when the query operation is applied to said collection, wherein querying said collection as a relation comprises;

recognizing said collection as a queriable relation;

unnesting said collection to create a queriable object; and

performing said query operation on said queriable object; and

storing the results of the processing or queuing.

13. The system of claim 12 further comprising at least one subsystem whereby said generic user-defined type is a system user-defined type.

14. The system of claim 13 further comprising at least one subsystem for:

registering said collection type with said relational data store; and

registering a user-defined type corresponding to said collection with said relational data store.

15. The system of claim 14 further comprising at least one subsystem for:

unnesting said collection into a relational queriable object comprising a first column wherein each row in said first column corresponds to a single member of said collection; and

querying said relational queriable object.

16. The system of claim 15 further comprising at least one subsystem whereby said relational queriable object comprises a second column, each row in said second column corresponds to a single member of said collection, each row having a value corresponding to an order of each said member in said ordered collection.