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

• US 7,596,576 B2
• Filed: 02/14/2005
• Issued: 09/29/2009
• Est. Priority Date: 02/10/2004
• Status: Expired due to Fees

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Rathakrishnan, Balaji, Sezgin, Beysim, Venkatesh, Ramachandran, Fang, Jun, Blakeley, Jose A., Carlin, Peter A.
• Primary Examiner(s): Fleurantin; Jean B
• Application Number:
  US11/057,475
• Publication Number:
  US 20050177585A1
• Time in Patent Office: 1,688 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206, 717/106, 717/114, 717/115, 717/122, 717/136, 717/140, 717/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-implemented method for creating a user-defined type in a database system, comprising:

receiving code that executes on a processing unit and 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 to ensure that the user-defined type will act like any scalar type of the database system, 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, the plurality of different formats comprising at least 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;

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;

storing metadata about the user-defined type for subsequent use by the database system in creating instances of the user-defined type; and

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

2. The method of claim 1, 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.

3. 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.

4. 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.

5. 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.

6. 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.

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

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

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

10. A computer-readable storage medium having program code stored thereon, the program code, when executed by a computer, causing the computer to perform the steps of the method recited in claim 1.

11. A computer system that implements a database system, the computer system comprising:

a processing unit;

a memory;

a runtime that executes on the processing unit and that provides code execution within the database system; and

a database server executing on the processing unit, the database server enabling instances of user-defined types to be persisted in a database store in a plurality of different formats including at least a first format in which instances of user-defined types are automatically serialized in accordance with a native format of the database server and a second format in which instances of user-defined types are serialized in a manner defined by the user-defined types themselves,

the database server 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 and that enforces a contract against the class to ensure that the user-defined type will act like any scalar type of the database system, the contract comprising;

a first requirement that the class specify one of said plurality of different formats for persisting instances of the user-defined type in the 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,

the database server storing metadata about the user-defined type for subsequent use by the database server in creating instances of the user-defined type; and

the database server creating instances of the user-defined type only when the class meets the requirements of the contract.

12. The computer system of claim 11, wherein the database server also enables instances of user-defined types to be stored in a third format in which instances of the user-defined types are serialized in accordance with a method provided by the MICROSOFT .NET FRAMEWORK, and wherein said first requirement of the contract requires that the class specify one of at least said first format, said second format or said third format for persisting instances of the user-defined type in the database store.

13. The computer system of claim 11, wherein a user of the database system can instantiate 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.

14. The computer system of claim 11, further comprising a query processor that (i) receives 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, (ii) translates the expression into a sequence of program code instructions that invoke the required method on the instance of the user-defined type, (iii) invokes the method upon execution of the program code, and (iv) returns a result of the method invocation as the evaluation of the query language expression.

15. The computer system of claim 11, 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 database server invokes the mutator method on an instance of the user-defined type to change the value of the instance.

16. The computer system of claim 11, wherein the class defining the structure and methods 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.

17. The computer system of claim 16, wherein the database server serializes instances of the user defined type such that binary representations of the instances are binary ordered, and wherein when the database server receives 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, the database server compares the serialized binary representations of the first and second instances of the user-defined type to evaluate the expression, without deserializing either instance.

18. The computer system recited in claim 16, wherein the database server creates a table in a database store in which a type of a column of the table is defined as the user-defined type, and wherein the database server creates an index on the column.

19. The computer system of claim 16, wherein the database server serializes instances of the user defined type such that the binary representations of the instances are binary ordered, and wherein when the database server receives 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, the database server generates a computed column over the expression and then creates an index over the computed column.