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

• US 20050177583A1
• Filed: 12/17/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: Sezgin, Beysim, Venkatesh, Ramachandran, Fang, Jun, Blakeley, Jose A., Rathakrishnan, Balaji, Carlin, Peter A.
• Granted Patent: US 7,308,460 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-32. -32. (canceled)

33. A computer-readable medium having stored thereon computer-executable instructions that, when executed on a computer system, enable the computer system to perform the steps 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;

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.

34. The computer-readable medium of claim 33, 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.

35. The computer-readable medium of claim 33, 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.

36. The computer-readable medium of claim 35, 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.

37. The computer-readable medium of claim 33, wherein the code that implements the class comprises managed code.

38. The computer-readable medium of claim 33, 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 a return value of a routine.

39. The computer-readable medium of claim 33, 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.

40. The computer-readable medium of claim 39, 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.

41. The computer-readable medium of claim 39, wherein the program code instructions comprise managed code.

42. The computer-readable medium of claim 33, 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.

43. The computer-readable medium of claim 42, 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.

44. The computer-readable medium of claim 33, 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.

45. The computer-readable medium of claim 44, 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 representations of the first and second instances of the user-defined type to evaluate the expression, without deserializing either instance.

46. The computer-readable medium of claim 44, 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 a column of the table is defined as the user-defined type; and

creating an index on the column.

47. The computer-readable medium of claim 44, 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.