JP4728675B2 - System and method for building unordered and ordered collections in a data store - Google Patents

Description

  The present invention relates to data storage in computer systems and systems that support user-defined types (UDTs) in database systems. A UDT is a scalar type system (s) for a data store by registering a managed type that implements a specific contract. )) Is an extensibility mechanism used in conjunction with a relational database engine and file system to extend. More particularly, the present invention relates to the implementation of multisets and ordered collections in a database system with UDTs that are both scalar and queriable.

  User-defined type

  As known and understood by those skilled in the art, the MICROSOFT SQL Server is a rich management and development tool, ETL (extraction-transformation-loading) tool, business intelligence and analysis services, and It is a comprehensive database management platform that provides other functions. Further, MICROSOFT WINDOWS (registered trademark). The NET framework's common language runtime (CLR) has recently been integrated into the SQL server database.

  CLR is a MICROSOFT. It is the core of the NET framework. Provides an execution environment for NET code. The code executed in the CLR is called “managed code”. CLR is required for program execution, including just-in-time (JIT) compilation, memory allocation and management, type safety enforcement, exception handling, thread management, and security. Functions and services. CLR is currently. It is loaded by the SQL server when the NET routine is called for the first time.

  In previous versions of the SQL server, database programmers could only use Transact-SQL when writing code on the server side. Transact-SQL is an extension of the Structured Query Language (“SQL”) defined by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI). With Transact-SQL, database developers can create, modify, and delete databases and tables, and can insert, retrieve, modify, and delete data stored in databases . Transact-SQL is specifically designed for direct structural data access and processing. Although Transact-SQL is superior to others in structural data access and management, Visual BASIC. It is not a mature programming language like NET and C #. For example, Transact-SQL does not support arrays, collections, for-each loops, bit shifts, or classes.

  By using CLR integrated into the SQL server database, database developers can now perform tasks that were impossible or difficult to accomplish with Transact-SQL alone. VISUAL BASIC. Both NET and C # are modern programming languages with full support for arrays, structured exception handling, and collections. Developers can take advantage of CLR integration and the logic is more complex, and Visual BASIC. Code more suitable for computational tasks using languages such as NET and C # can be written. Such programming languages provide object-oriented capabilities such as encapsulation, inheritance, and polymorphism. Related code can be easily organized into classes and namespaces.

  Managed code is more suitable for transactional and complex execution logic than Transact-SQL and features extended support for many complex tasks including string processing and regular expressions. Database developers: It can be used easily from any stored procedure, trigger, or user-defined function using the functions in the base class library (BCL) of the NET framework (.NET Framework). Thousands of built classes and routines can be used.

  Another benefit of managed code is type safety. Before the managed code is executed, the CLR verifies that the code is secure. This process is known as “verification”. During verification, the CLR performs several checks to ensure that the code is safe to execute. For example, the code is examined to ensure that a memory that has not been written does not receive a read. CLR also prevents buffer overflow.

  When writing managed code, the deployment unit is called an assembly. The assembly is packaged as a dynamic link library (DLL). A managed DLL assembly can be loaded and hosted by an SQL server. The CREATE ASSEMBLY statement is used to register the assembly with the server. Examples are given below.

  In this example, the FROM clause specifies the path name of the assembly to load.

  SQL servers have traditionally supported "built-in" scalar types such as integers, floats, dates, times, and strings. Such built-in types also involve a set of built-in operations such as +, −, *, /, as well as built-in functions for these types. These types, operations, and functions are “built-in” in the sense that they are built and packaged by the product and users cannot define their own types.

  Database systems, such as SQL Server, act as scalar types in database systems, but users create new types that include more complex structures and operations, such as creating "point" types consisting of X and Y coordinates, among other things It would be desirable to allow the user to extend the type system of the database system so that it can. In this regard, the SQL standard and some database management system (DBMS) products use the term “user-defined type” to describe some form of extensibility. Is used. For example, the SQL-99 standard describes a “distinct type”, a type that can be defined by a user to have an internal representation that is the value of an existing SQL built-in data type. A distinction type can optionally share comparison and arithmetic operators, type conversions, and aggregation (column) functions (eg, max, min, average) with existing scalar types. A distinction type can have a constraint defined on its value. Furthermore, a distinction type can expose behavior beyond that of existing scalar types by defining a new function specific to the distinction type with a user-defined function. With respect to type checking, distinct types and existing scalar types are considered to be different types.

  The advantage of the distinct type is that it can be easily defined. If the new type's internal representation has one data member that can be described with an existing built-in type, and the built-in type has already built most of the behavior required for the new type: The distinction type is an attractive alternative. The user is responsible for managing the storage of types, constructors, comparison operators (used for ordering and indexing), arithmetic operators, and type conversion (cast) operators on disk. There is no need to worry about construction. The user only needs to select what functionality of the underlying built-in type needs to be exposed in the distinct type and optionally add constraints on values or additional functions in the new type. Another advantage of the distinction type is that all query processing available for built-in types, such as histogram calculations, can be easily used for distinction type columns. However, the disadvantage of distinct types is that they cannot be used easily to create more complex types.

  The SQL-99 standard is also a type that can be defined by the user and is a collection of data members, each of which may be a member of a different SQL built-in type or a member of a user-defined type. structured type) ”. This type is similar to the concept of struct in C and C ++. SQL-99 describes a style that defines structure types. By using this style, the user need only define the structure type for the internal structure. The system automatically generates access and mutator functions in its data members, constructors, and functions to manage the representation on disk of instances of structured types.

  The unique advantages of structure types defined in SQL are (a) the ease of defining basic behavior of types and (b) the flexibility to define more complex types. However, a significant drawback of structured types is the complexity of defining type-specific methods, usually defined by external functions written in general-purpose programming languages such as C and C ++. In order to fully define a structure type, the person defining the type needs to seek a compromise between SQL and some other programming language.

  The distinct type and structured type features of SQL99 make it possible for the user to extend the existing scalar type system of the SQL database to some extent by allowing the user to extend the existing scalar type system. There is a need for improved systems and methods that provide advantages, but allow the user to extend the scalar type system of the database system with user-defined types that act as scalar types but include more complex structures and operations.

  US Patent Application No. 10 / 775,282 filed February 10, 2004 (reference number MSFT-3029 / 307007.01) “SYSTEM AND METHOD FOR PROVIDING USER DEFINED TYPES IN A DATABASE SYSTEM” (hereinafter UDT patent) The invention disclosed in this application is a system and method that allows a user to extend the scalar type system of a database system by creating user-defined types that act as scalar types but include more complex structures and operations. Is targeted. According to the present invention, a user writes program code in an advanced programming language that builds classes that define the structure of user-defined types and methods that can be invoked on instances of user-defined types. As used herein (and herein), the term “structure” when referring to a user-defined type encompasses the set of fields or properties that make up that type. The type of each field in the UDT patent application may be a scalar SQL type or any predefined user-defined type. The class that defines the user-defined type is then compiled and registered with the database system. Specifically, a CLR class that defines a user-defined type can be compiled into an assembly, which is then registered in the database system with a CREATE ASSEMBLY data definition statement. After the assembly is registered, the user can register the class with the assembly that defines the user-defined type using the CREATE TYPE data definition statement.

  In the case of the UDT patent application invention, the database system enforces certain contracts that the class must construct to allow user-defined types to act as scalars in the SQL type system. The term “contract” as used in this invention (and herein) is used to check that the code being executed meets certain prerequisites or requirements to ensure that it executes correctly. A technology used at runtime in an object-oriented programming environment. According to the present invention, a contract that is compared to a class that defines a user-defined type includes several requirements. First, the class must specify one of several different formats that hold instances that are user-defined types in the database store. Second, the class must be able to return a null value for user-defined types. Third, the class must provide a way to convert between user-defined types and other types such as string types. When these requirements are met, the database system allows to create instances that are user-defined types. In one embodiment of this application, user-defined types can be instantiated as table column values, variables, routine parameters, or routine return values. The database system stores metadata about a class that defines a user-defined type for later use in creating an instance of that type. In another embodiment of the invention, the validation of a user-defined type contract is performed using metadata describing the class that defines the type.

  Different formats that hold instances of user-defined types are the first format in which user-defined type instances are automatically serialized according to the native format of the database system, and in a manner defined by user-created classes. A second format in which instances of the definition type are serialized. Furthermore, the invention of the UDT patent application is MICROSOFT. When NET CLR is implemented in the integrated MICROSOFT SQL server, MICROSOFT. A third format is available in which instances of user-defined types are serialized according to the methods provided by the NET framework.

  In the case of the invention of the UDT patent application, the representation in the query language of the database system can include one or more references to the instance (s) of the user-defined type, so that the evaluation of the representation is user-defined. Requires a method call on the type instance. When the database system receives such a query language expression, it translates the expression into a series of program code instructions. When executed, these instructions call the required methods on the user-defined type instance. The database system then returns the result of the method call as a result of evaluating the query language expression. In one embodiment, an instance of a user-defined type is deserialized prior to invoking a method on the instance.

  Another feature of the present invention is that the value of an instance of a user-defined type can be changed by calling a mutator method. Specifically, the author of a class that defines a user-defined type includes a mutator method as part of the class. When called on an instance of a user-defined type, the mutator method allows the value of the user-defined type to be changed. This process deserializes an instance of a user-defined type, calls a mutator method to change the value of the instance's deserialized data, and then modifies the modified user-defined type to hold the change Can be serialized.

  Another feature of the present invention is that the class defining the user-defined type can further include an attribute specifying that the serialized binary representation of the user-defined type instance is in binary order. This feature allows binary comparisons to be performed on user-defined type instances and indexing to be performed on user-defined type instances. Specifically, for instances of user-defined types that are in binary order, a query language expression that requires some comparison (eg,>, <, or =) of two instances of that type is received by the database system. And the serialized binary representation of the two instances can be used to evaluate the representation without deserializing either instance. Further, for user-defined types that are in binary order, a table in the database store can be created with columns defined as user-defined types. An index can then be created on the column. An index can also be created for query language expressions that reference user-defined types. In this case, a calculated column for the representation is first generated, and then an index is created for the calculated column.

Collection
Applications with large amounts of data use object data models to capture real-world parts (also known as “object data modeling”). Entities (eg, “items” in item-based hardware / software interface systems) and relationships are two common data model constructs. For example, if the author and the document are two types of entities, between the author and the document, “all documents were written by one author” and “all There can be several relationships, such as “a document has been reviewed by an author”.

  An entity includes a set of properties that can be scalar values and / or collection values (or sets of values). For example, the creator entity may include a scalar value property for the creator's name and date of birth, and a collection value property for the creator's set of addresses and a set of phone numbers. These collection value properties, such as scalar value properties, are built into entities. That is, it is considered part of that entity, not just a relationship defined from that entity to another entity. Each of these collections (aka “embedded collections”) typically includes a small number of members (or “elements”), and in some examples, the members of these collections are expressed in importance. Relative order (for example, if the creator has a primary address, secondary address, etc.). As used herein, an unordered collection is referred to as a “multiset” and an ordered collection is a multi-set that further reveals the relative order of the members in the collection. It is called an extension “list”.

Extendable relational data store
Collections are implemented in existing database products, but these products generally have values such as the concept of primary key-foreign key. A collection is modeled using relations based on and thus cannot be treated as a relational queryable object. Furthermore, since collections are not relational queryable objects, queries to collections, such as “return the contacts of all people living in X City”, generally need to go through the entire collection path.

  Extensible Relational Data Store (ERDS) is a US patent application Ser. No. 10 / 775,282 filed Feb. 10, 2004 that describes the implementation of UDTs. Relational that allows entities to be modeled as user-defined types (UDTs) as described in the issue (reference number MSFT-3029 / 307007.01) "SYSTEM AND METHOD FOR PROVIDING USER DEFINED TYPES IN A DATABASE SYSTEM" It is a data store. In ERDS, it would be more natural and convenient to model these collections as part of a user-defined type (UDT). In UDT, the collection UDT could be treated as both a scalar and a relation where it is appropriate to do so.

  Therefore, there is a need in the art for a way to model a collection as part of a UDT in ERDS, as well as a collection that can be processed as a scalar or queried as a relation.

  Some embodiments of the present invention extend the extended relational data store (ERDS) UDT framework to include support for unordered collections (multiple sets) and ordered collections (lists). Intended for systems and methods. More particularly, in some embodiments of the present invention, a UDT infrastructure, a CLR generic, and a new UNNEST operator are simultaneously scalar and relational. Create and use a special type of collection abstraction, which is a (relation). As a scalar, this collection type can be processed by any part of the data store engine that understands the scalar (including but not limited to the client stack), and as a relation this collection type Is queriable like any other type of relation.

  Various embodiments of the present invention are directed to a data store that includes a collection type that is a scalar and a relation. In some of these embodiments, the method includes (a) an abstraction UDT for a collection type that is registered as a scalar essentially in the data store engine's scalar type system. Creating a (UDT abstraction), and (b) optionally unnesting the collection to query the collection as a relational queriable object including.

  Some embodiments of the present invention are treated as scalars for scalar operations applied to the collection and queried as relations for query operations applied to the collection. Target an extended collection for entities in a relational data store. In one such embodiment, the relational store is an extendable relational data store, and the collection is a generic user-defined type (generic) in a common language runtime (CLR). It is derived from user-defined type and is also a SQL scalar type (in the SQL scalar type system). Some of these embodiments require that both the collection type and user-defined type corresponding to the collection be registered in the relational data store. Further, in a particular embodiment, specifically, the collection is made into a relational queriable object that includes a first column using an unnesting operation on the collection. And each row of the first column corresponds to a single member of the collection (ie, a collection for a multiset). Other embodiments further include a second column, wherein each row of the second column corresponds to a single member of the collection, and the ordered collection (for lists). It has a value corresponding to the order of each said member in it. For some specific embodiments, both multisets and lists use the same two-column relational queryable object (the second column is related to multiple sets if order is not important) Is simply ignored).

  The foregoing summary, as well as the following detailed description of the preferred embodiments, will be better understood when read in conjunction with the appended drawings. However, the invention is not limited to the specific methods and instrumentalities disclosed.

  This application is a US Provisional Application No. 60 / 566,740 filed Apr. 30, 2004 (Docket No. MSFT-3940 / 307228.01), the entire contents of which are incorporated herein by reference. Insist on the benefits of “SYSTEMS AND METHODS FOR THE IMPLEMENTATION OF MULTISETS IN A DATABASE SYSTEM”. Further, this application is a US patent application Ser. No. 10 / 775,282 filed Feb. 10, 2004 (Docket No. MSFT-3029 / 307007.01), the contents of which are incorporated herein by reference. No. 10 / 837,929 (reference number MSFT-3843 / 307227. Filed on May 3, 2004), which is a continuation-in-part of "SYSTEM AND METHOD FOR PROVIDING USER DEFINED TYPES IN A DATABASE SYSTEM". 01) It is also a partial continuation application of “SYSTEMS AND METHODS FOR SUPPORTING INHERITANCE FOR USER-DEFINED TYPES”.

  This application is assigned to the assignee of the present invention, the entire contents of which are incorporated herein by reference, ie, US patent application Ser. No. 10 / 821,687 filed on Apr. 9, 2004 ( (Reference number MSFT-2955 / 307006.01) "SYSTEMS AND METHODS FOR FRAGMENT-BASED SERIALIZATION", US patent application Ser. No. 10 / 692,225 filed on Oct. 23, 2003 (reference number MSFT-2852 / 3068.19.01). ) “SYSTEM AND METHOD FOR OBJECT PERSISTENCE IN A DATABASE STORE” and US patent application Ser. No. 10 / 795,623 filed Mar. 8, 2004 (reference number MSFT-2960 / 307232.01) “STRUCTURED INDEXES ON RESULTS The object relates to the invention disclosed in “OF FUNCTION APPLICATIONS OVER DATA”.

  For the purpose of illustrating the invention, there are shown in the drawings exemplary constructions of the invention. However, the invention is not limited to the specific methods and instrumentalities disclosed.

  In order to meet legal requirements, the subject matter of the present invention is clearly described. However, the description itself is not intended to limit the scope of this patent. The inventor contemplates that the claimed subject matter may be implemented in other ways, along with other technology now or in the future, to include different steps or elements similar to those described in this document. is doing. Furthermore, the term “step” may be used herein to mean different elements of the utilized method, but unless the order of the individual steps is explicitly stated, the term Any specific order between the various steps disclosed herein should not be construed as implying.

  In the embodiments described below, the above features of the present invention will be described as being built in the MICROSOFT SQL server database system. As described above, the SQL server is MICROSOFT.NET to allow managed code to be written and executed to operate against the data store of the SQL server database. Incorporates the NET Common Language Runtime (CLR). Although the embodiments described below work in this context, it should be understood that the present invention is in no way limited to being implemented in a SQL server product. Rather, the present invention can be implemented in any database system that supports execution of object-oriented programming code that operates against a database store, such as object-oriented database systems with object-relational extensions and relational database systems. . Accordingly, the invention is not limited to the specific embodiments described below, but is intended to encompass all modifications that are within the spirit and scope of the invention as defined in the claims. I want you to understand.

Computer environment
Many embodiments of the invention can be executed on a computer. FIG. 1 and the following description are intended to provide a concise and general description of a suitable computing environment in which the invention may be implemented. Although not required to do so, the invention is described in the general context of computer-executable instructions, such as program modules, being executed by a computer such as a client workstation or server. Generally, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular abstract data types. Further, those skilled in the art will appreciate that the present invention can be implemented with other computer system configurations such as handheld devices, multiprocessor systems, microprocessor-based or programmable home appliances, network PCs, minicomputers, mainframe computers, and the like. . The invention may also be practiced in distributed computing environments where certain tasks are performed by remote processing devices that are linked through a communications network. In a distributed computing environment, program modules can be located in both local and remote memory storage devices.

  As shown in FIG. 1, an exemplary general purpose computing system includes a conventional personal computer 20 or the like, which places various system components such as a processing unit 21, system memory 22, and system memory into the processing unit 21. A system bus 23 to be coupled is included. The system bus 23 may be any of several types of bus structures such as a memory bus or memory controller, a peripheral bus, and a local bus using any of a variety of bus architectures. The system memory includes a ROM (Read Only Memory) 24 and a RAM (Random Access Memory) 25. The BIOS 26 (basic input / output system) includes basic routines that help transfer information between elements inside the personal computer 20 during startup, for example, and is stored in the ROM 24. The personal computer 20 includes a hard disk drive 27 that reads from and writes to a hard disk (not shown), a magnetic disk drive 28 that reads from or writes to a removable magnetic disk 29, and a CD ROM, for example, It may further include an optical disk drive 30 that reads from or writes to a removable optical disk 31 such as other optical media. The hard disk drive 27, magnetic disk drive 28, and optical disk drive 30 are connected to the system bus 23 by a hard disk drive interface 32, a magnetic disk drive interface 33, and an optical drive interface 34, respectively. Such drives and associated computer readable media provide non-volatile storage of computer readable instructions, data structures, program modules, and other data to the personal computer 20. The exemplary environment described herein uses a hard disk, a removable magnetic disk 29, and a removable optical disk 31, but other types that are computer readable that can store data accessible by a computer. Those skilled in the art will appreciate that these media can also be used in an exemplary operating environment. As such other types of media, magnetic cassettes, flash memory cards, digital video disks, Bernoulli cartridges, RAM (Random Access Memory), ROM (Read Only Memory), etc. can also be used in an exemplary operating environment. .

  Several program modules, such as operating system 35, one or more application programs 36, other program modules 37, and program data 38, can be stored on the hard disk, magnetic disk 29, optical disk 31, ROM 24, or RAM 25. . A user can input commands and information into the personal computer 20 via an input device such as a keyboard 40 and a pointing device 42. Other input devices (not shown) may include a microphone, joystick, game pad, satellite dish, scanner, and the like. These and other input devices are often connected to the processing unit 21 via a serial port interface 46 coupled to the system bus, but connected by other interfaces such as parallel ports, game ports, USB (Universal Serial Bus). You can also A monitor 47 or other type of display device is also connected to the system bus 23 via an interface, such as a video adapter 48. In addition to the monitor 47, personal computers typically include other peripheral output devices (not shown), such as speakers and printers. The exemplary system of FIG. 1 also includes a host adapter 55, a SCSI (Small Computing System Interface) bus 56, and an external storage device 62 connected to the SCSI bus 56.

  Personal computer 20 can operate in a networked environment using logical connections to one or more remote computers, such as remote computer 49. The remote computer 49 may be another personal computer, server, router, network PC, peer device or other common network node, and typically includes many or all of the elements described above in connection with the personal computer 20, although FIG. Only the memory storage device 50 is shown. The logical connection shown in FIG. 1 includes a LAN (Local Area Network) 51 and a WAN (Wide Area Network) 52. Such network environments are common in companies, enterprise-wide computer networks, intranets and the Internet.

  When used in a LAN network environment, the personal computer 20 is connected to the LAN 51 via a network interface or adapter 53. When used in a WAN network environment, the personal computer 20 typically includes a modem 54 or other means of establishing communications over a wide area network 52 such as the Internet. The modem 54 may be internal or external and is connected to the system bus 23 via the serial port interface 46. In a networked environment, the program modules illustrated in connection with the personal computer 20 or portions thereof may be stored in a remote memory storage device. It will be appreciated that the network connections shown are exemplary and other means of establishing a communications link between the computers can be used.

  Although many embodiments of the present invention are envisioned to be particularly compatible with computer-based systems, no part of this document is intended to limit the present invention to such embodiments. Conversely, the term “computer system” as used herein can store and process information regardless of whether the device is electronic, mechanical, logical, or virtual in nature, and / or It is intended to encompass any device that can use stored information to control the operation or execution of such a device itself.

UDT and managed code
FIG. 2 is a block diagram illustrating serialization and deserialization of an instance of a user defined type (UDT). As shown, an instance of UDT is maintained on disk via serialization of an object representing the instance in memory. When an application generates a query that includes a predicate or expression that references a method of an instance of UDT, the remaining form of the instance is deserialized (a process also called “hydration”), and the CLR Allocate memory for the complete object to receive the stored value. The CLR then invokes the appropriate method on the object that constructs the behavior desired by the application or user.

  As explained hereinabove, UDT is a managed type that is built with assemblies registered with an SQL server. UDT can be used in most contexts where native types including table definitions, variables, and parameters can be used. Methods, properties, and fields defined in UDT that conform to the UDT contract can be called from T-SQL. In essence, the UDT is assumed to be a simple scalar with motion. This assumption is reflected in the simple programming model and serialization layout supported by UDT. This structure can be used for abstracted UDTs to create a “structured type” that can be a type with complex structure and behavior rather than a simple scalar.

Collections--multisets
Various embodiments of the present invention are scalars and relations at the same time, that is, all parts of a data store engine that understand scalars (including client stacks) as scalars. For data store abstractions (or types) that flow through and can be queried as relations. In some of these embodiments, UDT infrastructure, CLR generics, and a new UNNEST operator are used simultaneously to enable such dualistic types.

  FIG. 3 is a process flow diagram illustrating a general method that allows an extensible relational data store (ERDS) to provide a dual collection type. To construct such a type of collection, at step 302, the generic features of the CLR are used to define a generic type, eg, collection <T>. This type is created as a "system UDT" (i.e., a UDT that automatically becomes available in all databases when defined at some location) and is the first class part of the SQL scalar type system. As such, this type can be used in all contexts where scalar types are supported, such as column definitions, variables, function arguments, and stored procedure parameters. These types are also supported in a scalar expression evaluation context.

  In step 304, the generic type is then a concrete collection type as a derived type of the generic collection type, i.e., the member is no longer generic, but a scalar type or other complex type. ) Can be used to create a type defined as In this example, the concrete type is for addresses and the individual members are scalar string types. In an alternative embodiment of the invention, the individual members are not of the scalar string type, but a first string (for street address), a second string (for city name), two characters (for state), and It may be a complex type containing a 5-digit integer (for zip codes).

  When a collection is declared to contain an element of a particular type, any value of that UDT subtype is stored in that collection. (Referred to as “substitutability”). The relationship between a collection type and its element type is recorded in metadata when creating the type (type creation type) and is queryable catalog view This allows the application to construct a query to determine the range of possible replacements for a particular collection type.

  In step 306, when an entity itself is instantiated, the new concrete collection type can be used as a property in that entity. At this point, the order collection is essentially treated as a scalar property unless the query is applied, and when the query is applied, the data store engine Recognize special features of, and treat the collection as a relation.

  In order to navigate the collection, the query typically needs to traverse the paths in the collection to answer questions such as "Return for all people in X City" . However, in some embodiments of the present invention, the query engine is responsible for the special collection system type, eg, “Collection <T>”, in relation semantics and scalar semantics associated with UDT. A new relational operator “UNNEST” with both (scalar semantics) is used to transform this scalar into a relational queryable object as needed. This operator is supported in all contexts where table-valued functions are supported, and view definitions that include UNEST operators can be indexed.

  FIG. 4 is a process flow diagram illustrating a method for processing a query applied to a collection at an entity. In step 402, if a query is created for the collection and the collection is not a collection, the query generates an SQL error for the data store engine, but if it is a collection, the data store engine Recognize that the collection is a special data type, and in step 406 ununnes the collection to create a queryable object. This object is for a multiset, which in this example is a single column table. In step 408, the data store engine redirects the query to this queryable object.

  For example, FIG. 5 shows a structured UDT (structured) having multiple sets of addresses, among other things, as an embedded field 510 that is unnested in a queryable object (single column table) 510 ′ when queried. 2 is a block diagram illustrating a “Person” entity 500 that is UDT). The managed type definition for Person is as follows:

  To create this type, a built-in collection type is first registered in the store as follows:

  Next, format. The structured UDT is registered as follows.

  You can then use the following command to return the address set as a scalar:

  Similarly, the following commands can be used to invoke methods in UDTs and embedded collections.

  To query the collection to find all people whose address is Washington, you can use the following command:

  You can also use the following command to update Person to change the state of all the addresses for that person to Washington:

  Similarly, to call a mutator method on a collection, you can use the following command:

  For certain embodiments of the present invention, the implementation framework uses the system UDT, CLR generics, and UNEST operations that utilize the stream table value function infrastructure.

  The system UDT is a system type that is built using a user-defined type (UDT) infrastructure. The system UDT differs from the UDT created by the end user as follows. (A) The system UDT is available in all databases and accessible to all users. (B) The system UDT cannot be created or interrupted by the end user. (C) A system UDT can be used across multiple databases as if it were one such type in the system (ie, an instance of a system type in one database can be ("Type identity"). (D) The system UDT can be built with a “system” assembly (like System.Data.dll), which is the base CLR infrastructure loaded from a location other than the database (eg, disk). Some may be sufficient. (E) The system UDT is part of the SYS schema, so the user cannot create his own UDT with the same name as the system UDT.

  Generics are parameterized type definitions that can be used for collection class libraries (such as MultiSet <T>), and globally "generic multiset" Once defined, it is then possible to perform a type-safe implementation for each “concrete instantiation” (such as MultiSet <Address> and MultiSet <PhoneNumber>). In other words, MultiSet <T> is, for example, UDExtensions. A generic system UDT defined once in dll.

  The UNNEST operator takes a UDT of type MultiSet <T> (or its SQL type is another concrete multi-set type) and transforms this UDT into a single column table, where each row of the column is a member of the collection Thus allowing on-the-fly query and navigation of members of the collection. In one embodiment of the present invention, during the binding process, a special namespace is created for the columns generated by the UNEST operation, and the UNEST operation itself is a streaming table value function (streaming table valued function). This optimization allows collection transformations from scalars to relations to occur without intervening data copies. This approach also provides a stream model for the results so that the results can be used without redundant materialization. (The streaming table conversion function is described in U.S. Patent Application No. 10 / 821,687 filed on April 9, 2004 (reference number MSFT-2955 / 307706.01) "SYSTEMS AND METHODS FOR FRAGMENT-BASED SERIALIZATION". Has been.

Collection--List A list is a collection that has been extended to capture the order of the members of the collection. Similar to multisets, this type of value is used in all contexts where scalar UDT values are allowed, including variables, parameters, return values, and column definitions. Can be used. Lists work with UNNEST functions that transform collection values that are essentially scalars into relations, but the relational schema provided by UNNEST has at least two columns, one of which contains members. (One member per row), the other column (or pseudo-column or virtual column) is a value that points to the relative order of the members in the list, eg , Including the order of a given element. FIG. 6 is a partial block diagram illustrating the queryable object 510 ″ from FIG. 5 that is created when a query is directed to a list rather than multiple sets. Also, as with multiple sets, TSQL DML is , Extended to support array access operations on list type values.

  For the rest of the orthogonality matrix (including the transformation matrix and use in an inter-database context), the contract for this type is Format. (Used later in this document). Assume that it is the same as for Structured UDT. Furthermore, a “list” can also be referred to as an “array” as used in the SQL99 standard.

  The following example shows an alternative UDT Person class that includes two list-valued properties for addresses and phone numbers.

  The above SqlList <Address> and SqlList <Phone> types are defined to the SQL server using new data definition language (DDL) statements.

  The following statement then creates a table with columns from the Person table.

  Once this statement is complete, the following statement can be used to create a Person instance with two addresses and three phone numbers as follows:

  Thereafter, the secondary addresses and cell phones in the Persons table for all persons whose primary address is in Seattle can be queried as follows:

  Again, UNNEST is a construct that takes a list as an argument and produces a set of rows where each row is for each element of the list. UNNEST yields two columns, one column for member elements and the other column for the relative order of each member in the list (eg, an order value corresponding to the priority of the elements).

CONCLUSION The various systems, methods, and techniques described herein may be implemented using hardware or software, or a combination of both where appropriate. Thus, the method and apparatus of the present invention, or certain aspects or portions of the present invention, may be on a tangible medium, such as a floppy diskette, CD-ROM, hard drive, or any other machine-readable storage medium. It may take the form of implemented program code (ie, instructions) that, when loaded and executed on a machine, such as a computer, the machine becomes an apparatus for implementing the invention. When executing program code on a programmable computer, the computer generally includes a processor, a processor-readable storage medium (such as volatile, non-volatile memory and / or storage elements), at least one input device, and at least one Includes one output device. The one or more programs are preferably implemented in a sophisticated procedural or object oriented programming language to communicate with the computer system. However, the program (s) can be built in either assembly or machine language if it is desired to do so. In either case, the language can be a compiled or interpreted language and can be combined with hardware construction.

  The method and apparatus of the present invention can also be implemented in the form of program code transmitted over, for example, an electrical connection or cabling, optical fiber, or some other transmission medium that can be in the form of any transmission, When program code is received and loaded into and executed by a machine such as an EPROM, gate array, PLD (programmable logic circuit), client computer, video recorder, etc., the machine implements the present invention. It becomes. When implemented on a general-purpose processor, the program code combines with the processor to provide a unique apparatus that operates to implement the indexing functionality of the present invention.

  Although the invention has been described with reference to preferred embodiments in the various drawings, other similar embodiments can be used and described to implement the same functions of the invention without departing from the invention. It should be understood that modifications and additions can be made to the described embodiments. For example, while an exemplary embodiment of the present invention has been described in the context of a digital device that emulates the functionality of a personal computer, the present invention is not limited to such a digital device, as described in this application, This also applies to any number of existing or emerging computing devices or environments, whether wired or wireless, such as gaming consoles, handheld computers, portable computers, etc., connected via a communications network and connected via a network Those skilled in the art will appreciate that it can be applied to any number of such computing devices that work. Further, various computer platforms including handheld device operating systems and other application specific hardware / software interface systems are contemplated herein, particularly emphasizing that the number of wireless networked devices continues to increase. Keep it. Accordingly, the invention should not be limited to any one embodiment, but should be construed in accordance with the scope of the claims.

It is a block diagram which shows the computer system for implementing this invention. FIG. 6 is a block diagram illustrating serialization and deserialization of a user-defined type instance instantiated with managed code. FIG. 5 is a process flow diagram illustrating a general method that allows an extensible relational data store (ERDS) to provide a binary (scalar and relation) collection type. FIG. 4 is a process flow diagram illustrating a method for processing a query applied to a collection at an entity. FIG. 5 is a block diagram illustrating an entity that is a structured UDT with multiple sets as built-in fields that are unnested in a queryable object when queried. FIG. 6 is a partial block diagram illustrating the queryable object of FIG. 5 being unnested for a list rather than multiple sets.

Explanation of symbols

20 Computer 21 Processing Unit 22 System Memory 23 System Bus 27 Hard Drive 28 Floppy (registered trademark) Drive 29 Removable Storage Device 30 Optical Drive 32 Hard Disk Drive I / F
33 Magnetic disk drive I / F
34 Optical drive I / F
36 Application program 36 'Application 37 Other program 38 Program data 40 Keyboard 42 Mouse 46 Serial port I / F
47 monitor 48 video adapter 49 remote computer (s)
50 Floppy (registered trademark) drive 53 Network I / F
54 Modem 55 Host Adapter 56 SCSI Bus 62 Storage Device

Claims (9)

A method for processing a query against a table in a database having a column defined as a collection type in a system comprising a processor, a memory, and a database, wherein the collection type is a specific type Contains nested elements,
The processor storing the collection type and the specific type of information in the memory;
The processor receiving a query applied to a column defined as the collection type in the table;
When the query is a scalar operation, the processor executes the scalar operation on the set of elements stored in the column defined as the collection type, and returns a result of the operation;
If the query is a query operation, the processor unnestes the collection to generate an object that includes a first column defined as the specific type, and executes the query on the generated object Returning the queried result, wherein the first column stores each element of the set of elements in each row.   The method of claim 1, wherein the specific type is a user-defined type. The collection type maintains the relative order of each element,
2. The object of claim 1, wherein the object includes a second column, and each row of the second column has a value corresponding to the relative order of elements stored in the first column. the method of. In a system comprising a processor, a memory, and a database, a computer-readable recording medium recording a program for processing a query against a table in the database having a column defined as the collection type, wherein the collection type is specified Including one or more nested elements of the type
Storing the collection type and the specific type of information in the memory;
Receiving a query applied to a column defined as the collection type in the table;
If the query is a scalar operation, executing the scalar operation on the set of elements stored in the column defined as the collection type, and returning the result of the operation;
When the query is a query operation, the collection is unnested to generate an object including a first column defined as the specific type, and the query is executed on the generated object, and the result of the query Wherein the first column stores each element of the set of elements in each row.   The computer-readable recording medium according to claim 4, wherein the specific type is a user-defined type. The collection type maintains the relative order of each element,
5. The object of claim 4, wherein the object includes a second column, and each row of the second column has a value corresponding to the relative order of elements stored in the first column. Computer-readable recording medium. A database storing a table having columns defined as collection types, wherein the collection type includes one or more nested elements of a particular type;
A memory for storing the collection type and the information of the specific type;
Receiving a query applied to a column defined as the collection type in the table and, if the query is a scalar operation, for the set of elements stored in the column defined as the collection type; Perform a scalar operation, return the result of the operation, and if the query is a query operation, uncollect the collection to generate an object that includes the first column defined as the specific type A processor that executes a query on the object and returns a result of the query, wherein the first column comprises: a processor that stores each element of the set of elements in each row; system.   The system of claim 7, wherein the specific type is a user-defined type. The collection type maintains the relative order of each element,
8. The object of claim 7, wherein the object includes a second column, and each row of the second column has a value corresponding to the relative order of elements stored in the first column. System.

Images