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

• US 20050177551A1
• Filed: 12/30/2004
• Published: 08/11/2005
• Est. Priority Date: 02/10/2004
• Status: Active Grant

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Stefani, Stefano, Tomic, Dragan, Yu, Wei, Sezgin, Beysim, Venkatesh, Ramachandran, Denuit, Bruno H.M., Blakeley, Jose A., Cunningham, Conor J., Churin, Denis, Rathakrishnan, Balaji, Altudov, Denis Y., Seeliger, Oliver Nicolas
• Granted Patent: US 7,437,371 B2
• Time in Patent Office: Days
• 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 method for implementing an enhanced collection as a property for an entity in a relational data store, said method comprising:

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

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

2. The method of claim 1 wherein said relational store is a extendable relational data store, and wherein said collection is both derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is also a sequential query language (SQL) scalar type.

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

4. The method of claim 3 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.

5. The method of claim 4 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.

6. The method of claim 5 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.

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

8. A computer-readable medium comprising computer-readable instructions for implementing an enhanced collection as a property for an entity in a relational data store, said computer-readable instructions comprising instructions for:

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

querying said collection as a relation when a query operation is applied to said collection.

9. The computer-readable medium of claim 8 further comprising instructions whereby said relational store is a extendable relational data store, and whereby said collection is both derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is also a sequential query language (SQL) scalar type.

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

11. The computer-readable medium of claim 10 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.

12. The computer-readable medium of claim 11 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.

13. The computer-readable medium of claim 12 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.

14. The computer-readable medium of claim 10 wherein said computer-readable medium is an electromagnetic carrier signal transmitted through a communications medium.

15. A system for implementing an enhanced collection as a property for an entity in a relational data store, said system comprising at least one subsystem for:

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

querying said collection as a relation when a query operation is applied to said collection.

16. The system of claim 15 further comprising at least one subsystem whereby said relational store is a extendable relational data store, and whereby said collection is both derived from a generic user-defined type (UDT) in a common language runtime (CLR) and is also a sequential query language (SQL) scalar type.

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

18. The system of claim 17 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.

19. The system of claim 18 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.

20. The system of claim 19 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.