System and method for providing user defined types in a database system

• US 6,976,029 B2
• Filed: 02/10/2004
• Issued: 12/13/2005
• Est. Priority Date: 02/10/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Sezgin, Beysim, Venkatesh, Ramachandran, Fang, Jun, Blakeley, Jose A., Rathakrishnan, Balaji, Carlin, Peter A.
• Primary Examiner(s): Alam, Shahid
• Assistant Examiner(s): FLEURANTIN, JEAN B
• Application Number:
  US10/775,282
• Publication Number:
  US 20050177589A1
• Time in Patent Office: 672 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206, 717/106, 717114-115, 717/122, 717/136, 717140-141, 717/146, 717/148, 717/159
• US Class Current: 1/1
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 7/00 Methods or arrangements for processing data by operating upon the order or content of the data handled logic circuits H03K19/00
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A database system and method allows a user to write program code in a high-level programming language that implements a class that defines the structure of a user-defined type and methods that can be invoked on instances of the type. The class is then registered with the database system, which enforces a specific contract for user-defined types against the class. The contract comprises the following requirements. First, the class must specify one of a plurality of different formats for persisting instances of the user-defined type in a database store. Second, the class must be capable of returning a null value for the user-defined type. Third, the class must provide a method for converting the user-defined type to another type. Once these requirements are satisfied, the database system enables instances of the user-defined type to be created. A user-defined type can be used in all contexts where any other built-in type can be used. This includes defining columns of tables, declaring variables and parameters to functions and stored procedures, and evaluating expressions of the user-defined type in SQL queries and updates. Preferably, the database system stores metadata about the user-defined type for subsequent use in creating instances of the type.

Claims

1. A method for creating a user-defined type in a database system, comprising:

receiving code that implements a class defining the structure of a user-defined type and methods that can be invoked on instances of the user-defined type;

enforcing a contract against the class, the contract comprising;

a first requirement that the class specify one of a plurality of different formats for persisting instances of the user-defined type in a database store;

a second requirement that the class be capable of returning a null value for the user-defined type; and

a third requirement that the class provide a method for converting the user-defined type to another type;

enabling instances of the user-defined type to be created only when the class meets the requirements of the contract;

performing said receiving, enforcing, and enabling steps to create a user-defined type in the context of a first database;

performing said receiving, enforcing, and enabling steps to create a user-defined type in the context of a second database; and

determining whether the user-defined type created in the context of the first database is equivalent to the user-defined type created in the context of the second database.

2. The method of claim 1, further comprising storing metadata about the user-defined type for subsequent use by the database system in creating instances of the user-defined type.

3. The method of claim 1, wherein the plurality of different formats for persisting instances of the user-defined type comprises:

a first format in which an instance of the user-defined type is automatically serialized in accordance with a native format of the database system; and

a second format in which an instance of the user-defined type is serialized in a manner defined by the class.

4. The method of claim 3, wherein the plurality of different formats for persisting instances of the user-defined type further comprises a third format in which an instance of the user-defined type is serialized in accordance with a method provided by the MICROSOFT .NET FRAMEWORK.

5. The method of claim 1, wherein the code that implements the class comprises managed code.

6. The method of claim 1, further comprising instantiating the user-defined type as one of a column value in a table, a variable, a parameter of a routine, and a return value of a routine.

7. The method of claim 1, further comprising:

receiving an expression in the query language of the database system, wherein evaluation of the expression requires invocation of a method of an instance of the user-defined type;

translating the expression into a sequence of program code instructions that invoke the required method on the instance of the user-defined type;

invoking the method upon execution of the program code; and

returning a result of the method invocation as the evaluation of the query language expression.

8. The method of claim 7, wherein said method further comprises deserializing the instance of the user-defined type prior to invoking the method on the instance.

9. The method of claim 7, wherein the program code instructions comprise managed code.

10. The method of claim 1, wherein the class that defines the user-defined type comprises a mutator method that, when invoked, enables a value of the user-defined type to be changed, and wherein the method further comprises invoking the mutator method on an instance of the user-defined type to change the value of the instance.

11. The method of claim 10, wherein said step of invoking the mutator method comprises:

deserializing the instance of the user-defined type;

invoking the mutator method to change the value of the instance; and

serializing the instance of the user-defined type having the changed value.

12. The method of claim 1, wherein the class defining the structure and method of the user-defined type further comprises an attribute that specifies that serialized binary representations of instances of the user-defined type will be binary ordered.

13. The method of claim 12, further comprising:

serializing instances of the user defined type such that the binary representations of the instances are binary ordered;

receiving an expression in a query language of the database system that requires the comparison of a first instance of the user defined type to a second instance of the user defined type; and

comparing the serialized binary representations of the first and second instances of the user-defined type to evaluate the expression, without deserializing either instance.

14. The method of claim 12, further comprising:

creating a table in a database store in which a type of a column of the table is defined as the user-defined type; and

creating an index on the column.

15. The method of claim 12, further comprising:

serializing instances of the user defined type such that the binary representations of the instances are binary ordered;

receiving an expression in a query language of the database system, the evaluation of which requires invocation of a method on an instance of the user defined type;

generating a computed column over the expression; and

creating an index over the computed column.