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

• US 20050177585A1
• Filed: 02/14/2005
• 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: Sezgin, Beysim, Venkatesh, Ramachandran, Fang, Jun, Blakeley, Jose A., Rathakrishnan, Balaji, Carlin, Peter A.
• Granted Patent: US 7,596,576 B2
• Time in Patent Office: Days
• 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; and

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

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.

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.

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.

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.

32. A computer-readable 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.

5. (canceled)

8. (canceled)

9. (canceled)

11. (canceled)

16. (canceled)

17. A database system comprising:

a runtime that provides code execution within the database system; and

a database server that receives 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, 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,

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

18. The database system of claim 17, wherein the database server stores metadata about the user-defined type for subsequent use by the database server in creating instances of the user-defined type.

19. The database system of claim 17, 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.

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

22. The database system of claim 17, 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.

23. The database system of claim 17, 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.

26. The database system of claim 17, 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.

28. The database system of claim 17, 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.

29. The database system of claim 28, 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.

30. The database system recited in claim 28, 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.

31. The database system of claim 28, 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.

21. (canceled)

24. (canceled)

25. (canceled)

27. (canceled)