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

• US 7,308,460 B2
• Filed: 12/17/2004
• Issued: 12/11/2007
• Est. Priority Date: 02/10/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Venkatesh, Ramachandran, Fang, Jun, Blakeley, Jose A., Sezgin, Beysim, Rathakrishnan, Balaji, Carlin, Peter A.
• Primary Examiner(s): FLEURANTIN, JEAN B
• Application Number:
  US11/016,240
• Publication Number:
  US 20050177583A1
• Time in Patent Office: 1,089 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 computer-readable storage medium having stored thereon computer-executable instructions that, when executed on a computer system, causes the computer system to perform the step of:

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;

creating instances of the user-defined type only when the class meets the requirements of the contract;

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

performing said receiving, enforcing, and creating 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 computer-readable storage medium of claim 1, wherein the instructions further cause the computer system to perform the step of storing metadata about the user-defined type for subsequent use by the database system in creating instances of the user-defined type.

3. The computer-readable storage medium 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 computer-readable storage medium 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 computer-readable storage medium of claim 1, wherein the code that implements the class comprises managed code.

6. The computer-readable storage medium of claim 1, wherein the instructions further cause the computer system to perform the step of instantiating the user-defined type as one of a column value in a table, a variable, a parameter of a routine, and return value of a routine.

7. The computer-readable storage medium of claim 1, wherein the instructions further cause the computer system to perform the steps of:

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 computer-readable storage medium of claim 7, wherein the instructions further cause the computer system to perform the step of deserializing the instance of the user-defined type prior to invoking the method on the instance.

9. The computer-readable storage medium of claim 7, wherein the program code instructions comprise managed code.

10. The computer-readable storage medium 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 instructions further cause the computer system to perform the step of invoking the mutator method on an instance of the user-defined type to change the value of the instance.

11. The computer-readable storage medium of claim 10, wherein said step of invoking the mutator method comprising:

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 computer-readable storage medium 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 computer-readable storage medium of claim 12, wherein the instructions further cause the computer system to perform the steps of:

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 representation of the first and second instances of the user-defined type to evaluate the expression, without deserializing either instance.

14. The computer-readable storage medium of claim 12, wherein the instructions further cause the computer system to perform the step of:

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

creating an index on the column.

15. The computer-readable storage medium of claim 12, wherein the instructions further cause the computer system to perform the step of:

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.