Patent Description:
As an application is updated, it is often shut down while its corresponding database is updated, e.g., using data definition language (DDL) statements. For example, the schema of the database may be altered for the updated application. This often introduces downtime for the application, which may impede the performance of the application and, in turn, user experience. Additionally, the owner or developer of the application may wish to maintain prior versions of the application alongside the new, updated version of the application. Such a multi-version implementation may introduce errors and/or delays during implementation of the application by a database management system (DBMS) that is handling the database for the multiple versions of the application. For example, database indexes for a new application version may interfere with the implementation of a prior version of the application. Specifically, when an index corresponding to the prior version of the application is dropped in the new version, the DBMS needs to prevent the newly created indexes from interfering with the prior version. If this prevention is unsuccessful, this may introduce delays in processing queries from the prior version of the application.

<CIT>discloses a hardware and/or software translation system to automatically apply user-specific configuration settings to a computer irrespective of the application version or operation system version for which the settings were originally established. By automatically applying user-specific configuration settings to a version of an application or operating system, the translation system eliminates any post-installation reconfiguration, thereby simplifying the process of changing or upgrading the version of an application or operating system.

This disclosure includes references to "one embodiment" or "an embodiment. " The appearances of the phrases "in one embodiment" or "in an embodiment" do not necessarily refer to the same embodiment. Particular features, structures, or characteristics may be combined in any suitable manner consistent with this disclosure.

Within this disclosure, different entities (which may variously be referred to as "units," "circuits," other components, etc.) may be described or claimed as "configured" to perform one or more tasks or operations. This formulation-[entity] configured to [perform one or more tasks]-is used herein to refer to structure (i.e., something physical, such as an electronic circuit). More specifically, this formulation is used to indicate that this structure is arranged to perform the one or more tasks during operation. A structure can be said to be "configured to" perform some task even if the structure is not currently being operated. A "computer system configured to receive a database query for database" is intended to cover, for example, a computer system having a processor, network interface, memory having program instructions, etc. to performs this function during operation, even if the computer system in question is not currently being used (e.g., a power supply is not connected to it). Thus, an entity described or recited as "configured to" perform some task refers to something physical, such as a device, circuit, memory storing program instructions executable to implement the task, etc. This phrase is not used herein to refer to something intangible.

The term "configured to" is not intended to mean "configurable to. " An unprogrammed FPGA, for example, would not be considered to be "configured to" perform some specific function, although it may be "configurable to" perform that function and may be "configured to" perform the function after programming.

As used herein, the terms "first," "second," etc. are used as labels for nouns that they precede, and do not imply any type of ordering (e.g., spatial, temporal, logical, etc.) unless specifically stated. For example, in a computing system having multiple user accounts, the terms "first" and "second" user accounts can be used to refer to any users. In other words, the "first" and "second" user accounts are not limited to the initial two created user accounts, for example.

As used herein, the term "based on" is used to describe one or more factors that affect a determination. This term does not foreclose the possibility that additional factors may affect a determination. That is, a determination may be solely based on specified factors or based on the specified factors as well as other, unspecified factors. Consider the phrase "determine A based on B. " This phrase specifies that B is a factor used to determine A or that affects the determination of A. This phrase does not foreclose that the determination of A may also be based on some other factor, such as C. This phrase is also intended to cover an embodiment in which A is determined based solely on B. As used herein, the phrase "based on" is thus synonymous with the phrase "based at least in part on.

In prior DBMS implementations, one approach for handling multiple application versions included a new version of an application accessing a database management system (DBMS) to retrieve data for processing user requests. In this implementation, the DBMS may load a new database schema for that application into the database with a new schema name. In this example, the new database schema is simply a duplication of the schema of the previous version of the application, but with alterations corresponding to the execution of the new application. Although this implementation works well for database objects that are easily copied (non-persistent objects such as triggers, indexes, statistics, etc.), it introduces problems for persistent database objects such as tables. For example, copying the entire contents of database tables multiple times for multiple different schemas is costly and inefficient.

The present disclosure describes techniques for maintaining data for multiple different application versions in a single database. For example, the disclosed techniques handle queries from multiple application versions at a given time, by implementing application versioning within the system catalog of the database for different objects in the database. The system catalog holds metadata describing database objects stored in the database. For example, an application may be associated with a particular schema in the database and this schema includes a name and a version (such that the schema version corresponds to a given application version). The new versioning techniques involve adding additional columns to database catalog tables to specify an application version associated with various database objects stored in the database. For example, these columns may specify a range of application versions using a start version and a stop version or may specify a particular application version for objects in the database. These database objects may include tables, columns, triggers, indexes, statistics, procedures, views, etc..

In embodiments described herein, a new version of the client application may add a new column "currency" to the "sales" database table. In this example, the database management system adds a new row to a table in the system catalog that includes metadata for database columns. This column catalog table specifies the name of the database table (sales), the name of the added column (currency), and a start version and a stop version of the application associated with this added database column. The database management techniques described in this disclosure may advantageously allow for servicing of multiple different versions of an application at the same time while reducing or avoiding duplication of data stored in the database.

<FIG> is a block diagram illustrating an example system configured to process database queries from different application versions. In the illustrated embodiment, system <NUM> includes a user computing system <NUM> and a DBMS <NUM> configured to communicate for processing application requests.

User computing system <NUM>, in the illustrated embodiment, includes an application <NUM>. User computing system <NUM> may be an application server configured to facilitate use of application <NUM> by various different users. For example, in some embodiments, system <NUM> may be a customer relationship management system that manages applications for various different customers. In some situations, different users (of the same or different customers) use the same application, but implement different versions of this application. For example, system <NUM> may manage a human resources application for two different users, but each user implements a different version of the human resources application with access to different data maintained by DBMS. In some embodiments, computing systems other than system <NUM> include different versions of application <NUM> and are configured to communicate with DBMS <NUM> for processing queries from these applications. In the illustrated embodiment, user computing system <NUM> sends query <NUM> to DBMS <NUM> for application <NUM>. This query <NUM> specifies a particular application version.

DBMS <NUM>, in the illustrated embodiment, includes a system catalog <NUM> and a database <NUM>. As used herein, the term "system catalog" is intended to be construed according to its well-understood meaning, which includes a portion of a database which stores metadata about the data stored in the database such as schemas defining database objects. For example, the system catalog may include various catalog tables and/or catalog views. These catalog tables may include metadata describing data stored in various objects included in the database managed by the DBMS, such as database: tables, columns, triggers, indexes, statistics, procedures, views, etc. These catalog tables may be used to determine the best way to evaluate a structured query language (SQL) query received from an application, for example. The terms "catalog" and "catalog table" may be used interchangeably. In the present disclosure, the catalog tables specify subsets of a dataset stored in the database that are accessible to different applications versions, including which database tables are visible to certain applications.

System catalog <NUM>, in the illustrated embodiment, is collection of tables that contain information about database objects stored in the database <NUM>. For example, system catalog <NUM> may include a list of schema identifiers that correspond to different versions of an application (or different applications). System catalog <NUM> includes various catalog tables 142A-N that each include metadata for different versions. For example, in <FIG>, catalog tables 142A-N includes metadata <NUM> for versions A and B of application <NUM>. Example catalog tables are discussed below with reference to <FIG>.

Similarly, in the illustrated embodiment, database <NUM> includes various database tables 152A-N, which each include data <NUM> for both version A and version B of application <NUM>. In some embodiments, catalog tables 142A-N and database tables 152A-N include metadata and data for different applications. For example, these tables may include metadata and data for both a human resources application and an accounting application. Additionally, these tables may include metadata and data for different applications as well as different versions of the same application. For example, application version A may be an older version of a particular application and may not see data in columns that were added to the database for application version B, a newer version of the particular application. In this example, version A, however, may still be able to see data in columns that were removed or retired in version B. Said another way, both application versions may see the same rows of data with different sets of columns. DBMS <NUM> may be any of various database management systems, including: PostgreSQL, MySQL, ORACLE, etc..

The metadata stored in catalog tables <NUM> may specify locations of database objects stored in database <NUM> as well as version access information for these objects. That is, the metadata included in catalog tables <NUM> may indicate, for a particular application version, database objects stored in the database that are accessible to that application version and may be used to respond to queries from the particular application version.

In the illustrated embodiment, DBMS <NUM> accesses system catalog <NUM> based on query <NUM> to determine database objects stored in database <NUM> that is accessible to the application version specified in the query. Based on this determination, DBMS <NUM> may use a query optimizer to generate a query plan of execution for database <NUM>. Then, DBMS <NUM> accesses database <NUM> based on this plan to retrieve data responsive to query <NUM>. DBMS <NUM> sends query results <NUM> to user computing system <NUM> for application <NUM>. These results are associated with the application version specified in query <NUM>.

As used herein, the term "application version" refers to a particular form of an application that differs from earlier or later forms of the application. For example, an application version may be assigned to a new or updated version of an application using a monotonically increasing numbering scheme which governs the accessibility of various database content to different versions of an application. As one specific example, a first version of an HR application may be assigned version <NUM>. <NUM>, while a second version of the HR application is assigned version <NUM>. As used herein the term "metadata" is intended to be construed according to its well-understood meaning, which includes a set of data that describes other data. For example, metadata may be included in catalog tables of the system catalog. Specifically, this metadata may specify an application version associated with a particular schema in the database. Example metadata stored in the system catalog is discussed in detail below with reference to <FIG> and <FIG>.

<FIG> is a block diagram illustrating an example system catalog <NUM> of DBMS <NUM>. In the illustrated embodiment, system catalog <NUM> includes the following catalog tables: version catalog table <NUM>, schema catalog table <NUM>, column catalog table <NUM>, database table catalog table <NUM>, index catalog table <NUM>, trigger catalog table <NUM>, procedure catalog table <NUM>, and statistics catalog table <NUM>. Note that any of various other catalog tables for various other types of database objects may be included in system catalog <NUM> and managed by DBMS <NUM>.

The catalog tables shown in <FIG> may include entries (rows) for different objects in the database <NUM>. For example, column catalog table <NUM> may include entries for various database columns, where these entries include fields with values specifying locations of columns in the database <NUM> as well as version access information. Further details for different catalog tables are discussed below with reference to <FIG> and <FIG>.

<FIG> is a block diagram illustrating example version and schema catalog tables. In the illustrated embodiment, system catalog <NUM> includes a version catalog table <NUM> and a schema catalog table <NUM>.

Version catalog table <NUM> is a table that allows DBMS <NUM> to look up a particular version ID for a name associated with a particular version of an application and to determine the status of that particular version (whether the application version is active, retired, etc.). Version catalog table <NUM>, in the illustrated embodiment, includes three columns: version identifier (ID) <NUM>, version name <NUM>, and version status <NUM>. Within the table, three different entries (rows) are shown for three different versions of an application. Specifically, the first entry in version catalog table <NUM> includes schema information for application version <NUM>. <NUM>, including a version name, Summer <NUM>, and a version status indicating that this application version is retired. Similarly, application version <NUM>. <NUM> is named Fall <NUM> and is active, while application version <NUM>. <NUM> is named Spring <NUM> and is currently under construction. Version catalog table <NUM> may include any number of entries with applications at various versions and in various states with unique version IDs and application names.

System catalog <NUM>, in the illustrated embodiment, includes a schema catalog table <NUM> that is usable to look up which particular schema (based on its schema ID) is applicable for a given schema name and a given application version. Specifically, schema catalog table <NUM> includes three columns specifying: schema ID <NUM>, schema name <NUM>, and version ID <NUM>. The version ID <NUM> column of schema catalog table <NUM> is the same as the version ID column of version catalog table <NUM>. A first schema (indicated by schema ID <NUM>) is named HR (as indicated by schema name <NUM> in <FIG>) and corresponds to version ID <NUM>. This pattern continues for other schema IDs, with each new schema ID corresponding to a new version ID. Schema ID <NUM>, however, corresponds to an un-versioned schema. An un-versioned schema is a schema that is visible to existing versions of applications. In some situations, certain database objects (such as views or functions) may be placed in an un-versioned schema because these database objects are expected to be persistent across application versions. Other database objects, such a database tables, may be placed in an un-versioned schema so that the data stored in these objects may be available across various versions of an application, for example. In some cases, a version ID of <NUM> indicates that a database object has been retired or dropped. <FIG> shows an example of dropping a database column and a corresponding version ID of <NUM> for a start version column. Schema may be defined on a per-application basis (using a particular name), but a given schema may be shared by multiple instances of the same application. Accordingly, the name "HR" may be the name of a schema for a particular application, which may also be called "HR" in some embodiments.

Although <FIG> shows entries for the schema named HR, this table may include entries for schemas with different names, such as Accounting, Hiring, Process, etc. A particular application may use multiple different schemas. As one specific example, the application associated with version <NUM>. <NUM> and generated in summer of <NUM> is a human resources application that may use schemas "HR" and "Hiring.

<FIG> is a block diagram illustrating an example database <NUM> that includes an employee table <NUM> for a human resources (HR) application. In the illustrated embodiment, database <NUM> includes hr. employee table <NUM>, hr. emp_name index <NUM>, hr. emp_name_comp index <NUM>, and hr. totalcomp procedure <NUM>. The tables shown in <FIG> depict, for example, a superset of data that may be stored for multiple versions of the same application (or for different applications) within DBMS <NUM>. As will be discussed in greater detail below with reference to subsequent figures, a given application may be permitted to query only a subset of the information stored in database <NUM> based on the version information defined in metadata <NUM> of system catalog <NUM>.

employee table <NUM>, in the illustrated embodiment, includes four different columns: employee ID <NUM>, employee name <NUM>, salary <NUM>, and bonus <NUM>. employee table <NUM> includes three entries, one for each of Michael Smith, John Doe, and Jane Doe. For example, Jane Doe is associated with employee ID <NUM>, has a salary of $<NUM>,<NUM> and a bonus of $<NUM>. In some embodiments, database <NUM> includes a second table named client. This client table, along with hr. employee table <NUM>, may include data for different versions of an HR application. In some situations, the employee and client tables include data for different applications (e.g., an HR application and an accounting application). As will be discussed below with reference to <FIG> and <FIG>, certain application versions may not be able to access some of the columns included in hr. employee table <NUM>.

emp_name index <NUM> includes the employee name <NUM> and salary <NUM> columns of employee table <NUM>, while hr. emp_name_comp index <NUM> includes the employee name <NUM> column of hr. employee table <NUM> as well as a sum <NUM> column that includes the summed values of the salary and bonus of each employee. For example, the values stored in sum column <NUM> may be determined by executing hr. totalcomp procedure <NUM>, which is a function that calculates the sum of two different parameters: salary and bonus. totalcomp procedure <NUM> receives an employee ID or employee name as input and outputs the total compensation for that employee.

In some embodiments, database <NUM> includes additional database objects. For example, database <NUM> may include triggers and statistics as well as any of various other database objects. In particular, database <NUM> may include a statistic for the salary <NUM> column of hr. employee table <NUM> that tracks the average salary of all employees listed in table <NUM>. Information stored in database <NUM> may be accessible to application versions via queries specifying respective application versions, as discussed below with reference to <FIG>. For example, these database indexes may not be visible to a particular application version, but are visible to other versions of the particular application.

<FIG> are block diagrams illustrating example alteration of a column catalog table <NUM> based on adding, retiring, unretiring, and dropping a column for a database table, respectively. In <FIG>, application <NUM> sends database commands to DBMS <NUM> specifying updates based on a new version of the application. These database commands may include data definition language (DDL) operations, for example. In many situations, a new version of application <NUM> is sending DDL operations to DBMS <NUM> based on new information being used by this application during operation.

In <FIG>, application <NUM> generates a database command <NUM> for database <NUM> and transmits this command to DBMS <NUM>. A database command sent from application <NUM> to DBMS <NUM> may be an SQL, Postgres, MySQL, etc. command. Database command <NUM> includes two different operations. The first operation specifies that a current application version is <NUM>. For example, application version <NUM>. <NUM> is the application version associated with the second operation. The second operation specifies to update hr. employee table <NUM> by adding a bonus <NUM> column (e.g., where employee table does not yet include bonus column <NUM>). Based on this database command, DBMS <NUM> updates metadata stored in system catalog <NUM> and database objects stored in database <NUM> (not shown).

DBMS <NUM>, in <FIG>, includes a column catalog table <NUM> that stores metadata for database columns included in database <NUM>. For example, turning back to <FIG>, database <NUM> includes hr. employee table <NUM> with four database columns: employee ID <NUM>, employee name <NUM>, salary <NUM>, and bonus <NUM>. Returning to <FIG>, column catalog table <NUM> includes five different columns: table ID <NUM>, column number <NUM>, column name <NUM>, start version <NUM> and stop version <NUM>. These five columns specify metadata for the database columns included in database <NUM>. For example, column catalog table <NUM> includes an entry specifying that the database column named salary <NUM> is included in table ID <NUM> (hr. employee table <NUM>), is associated with schema ID <NUM>, and is available to applications starting at version <NUM>. As one specific example, in the PostgreSQL context, column catalog table <NUM> is called pg_attribute, where the term "attribute" is equivalent to column and is used for historical reasons.

In the illustrated embodiment, the start version <NUM> and stop version <NUM> columns included in column catalog table <NUM> specifying, for different database columns the application version at which this column is accessible (start version <NUM>) as well as the application version at which these database columns are no longer accessible (stop version <NUM>). For example, the start and stop version columns included in column catalog table <NUM> specify a range of application versions that are aware of data stored in a particular database column. For example, if application version <NUM>. <NUM> attempts to access all columns of hr. employee table <NUM>, the bonus column data will not be listed among the column data returned to the application. In addition, if this application version attempts to directly reference the bonus column in a query, then the query response message will specify "column bonus not found".

Implementation of the start and stop version columns may advantageously prevent older application versions from becoming slow due to these versions relying on indexes that have been dropped in newer application versions. This may also prevent newer application versions from violating service level agreements (SLAs) (e.g., queries from the client application need to be satisfied in under a threshold amount of time). In addition, use of start and stop version columns may allow for storage of information about multiple application versions in a single table as opposed to multiple tables, each dedicated to a particular version. This, in turn, may reduce or remove the need for the DBMS to keep data synchronized between multiple tables. Versioning of column data may reduce or remove the risk of semantically breaking an application due to a single copy of a database table including columns that are visible to all application versions, for example. In this example situations, the query "SELECT*FROM employee table" might return extra columns. The disclosed versioning techniques may also reduce or prevent data stored in a column that is meant to be retired from a database table in a newer version from accidently being accessed by the newer application version in the situation where an application developer was not aware that this data stored in an object that is retired.

DBMS <NUM> receives database command <NUM> from application <NUM> and performs a DDL operation that is executable to add a column (bonus <NUM> in <FIG>) to database <NUM> based on this command. In addition to and concurrently with the DDL operation for database <NUM>, DBMS <NUM> updates column catalog table <NUM> by performing a column addition operation <NUM>. For example, column addition <NUM> operation is executable to add an entry to column catalog table <NUM>. Specifically, DBMS <NUM> adds a row to column catalog table <NUM> with values specifying that the new column is named "bonus" and is included in the employee table corresponding to table ID <NUM> (this new row is shown in bold in the figure). In the depicted example, the bonus database column would be accessible to application version <NUM>. <NUM> or newer versions, but would not be accessible to application version <NUM>. <NUM> or older versions.

In some embodiments, system catalog <NUM> includes primary keys and unique constraints for different catalog tables. A primary key may include a set of columns with unique values within a table that are used to identify a row when referenced from another table. For example, column catalog table <NUM> may include a primary key that is made up of the table ID <NUM> and column number <NUM> columns. This primary key is usable to identify a particular row within column catalog table <NUM>. Similarly, a unique constraint may include a set of columns with unique values. For example, column catalog table <NUM> may include a unique constraint made up of the table ID <NUM> and column name <NUM> columns.

In some embodiments, a database command includes multiple updates for a given application version. For example, database command <NUM> may include multiple different operations (such as the second operation "ALTER TABLE") that indicate differences between database objects that are accessible to prior versions and database objects that are accessible to version <NUM>.

In <FIG>, application <NUM> sends a database command <NUM> to DBMS <NUM> that specifies the application version <NUM>. <NUM> and includes an operation for retiring a database column. Based on database command <NUM>, DBMS <NUM> performs column retiring operation <NUM> to alter column catalog table <NUM> by adding a stop version <NUM> (<NUM>. <NUM>) for the row of table <NUM> that includes metadata for the bonus database column. In this example, the bonus column included in the employee database table is only accessible to version <NUM>. <NUM> of the human resources application.

In some situations, the retirement of the bonus column may be made in error during development (e.g., prior to exposing the retired column via a new application release) of application version <NUM>. This retiring can be reversed within the same application version. In order to mitigate this error, application <NUM> may unretire the bonus column. For example, in <FIG>, application <NUM> sends a database command <NUM> to DBMS <NUM> specifying application version <NUM>. Database command <NUM> includes an operation for unretiring the bonus column in database <NUM>. Based on this command, DBMS <NUM> performs column unretiring operation <NUM> to update column catalog table <NUM> to specify a stop version <NUM> of "<NUM>" for the bonus database column.

In <FIG>, application <NUM> sends a database command <NUM> to DBMS <NUM> that includes an operation specifying to drop the bonus column in database <NUM>. Based on database command <NUM>, DBMS <NUM> performs column dropping operation <NUM> to update column catalog table <NUM> such that its start version column <NUM> specifies a version of "<NUM>" for the bonus database column. In some situations, an application <NUM> will only be allowed to drop a column (or any other database object) when that column is no longer available to other application versions. That is, stop version <NUM> column for the bonus column must specify an application version that is older than or the same as the oldest version of an application. For example, a column may be dropped when it has been retired in an application version that is older than the last supported version of the application.

<FIG> is a block diagram illustrating an example database table catalog table during creation of a database table. In the illustrated embodiment, database catalog table <NUM> includes the following columns including metadata specifying data stored in tables in database <NUM>: table ID <NUM>, schema ID <NUM>, table name <NUM>, start version <NUM>, and stop version <NUM>. Database catalog table <NUM> includes metadata specifying whether information stored in database tables in database <NUM> are visible to different application versions. For example, the first row of database table catalog table <NUM> includes table ID of <NUM>, a schema ID of <NUM>, is named "Employee," and is accessible to applications beginning at application version <NUM>. Schema ID <NUM> refers to an un-versioned schema and, therefore, the employee table (table ID <NUM>) may be visible to multiple different application versions. In some situations, it may be desirable to have database tables be visible across application versions and, as such, these database tables may live in an un-versioned schema.

In the illustrated embodiment, application <NUM> sends database command <NUM> to DBMS <NUM>. This database command includes an operation for creating a table in database <NUM> called "client. " After generating a client table in database <NUM>, DBMS <NUM> performs database table creation operation <NUM> to add an entry to database table catalog table <NUM> in system catalog <NUM>. In the illustrated embodiment, the second row of database table catalog table <NUM> specifies application version <NUM>. <NUM> as the start version <NUM> for the client database table. In this example, both the employee and client database tables are accessible to applications associated with versions <NUM>. <NUM> and later.

<FIG> is a block diagram illustrating an example index catalog table during creation of a database index. In the illustrated embodiment, index catalog table <NUM> includes the following columns including metadata specifying data stored in indexes in database <NUM>: table ID <NUM>, index name <NUM>, start version <NUM>, and stop version <NUM>. Index name <NUM> column, for example, stores values specifying the names of various different database indexes.

DBMS <NUM>, in the illustrated embodiment, receives a database command <NUM> from application <NUM> specifying to create a new index in database <NUM>. Based on this command, DBMS <NUM> generates a new index hr. emp_name_comp <NUM>. After generating the new index, DBMS <NUM> performs index creation operation <NUM> to add an entry to index catalog table <NUM> for index <NUM> indicating that this index is accessible to application version <NUM>. <NUM> (and newer versions) and, therefore, this index is not usable by application version <NUM>. <NUM> and older versions. DBMS <NUM>, however, will maintain indexes that are present within the database, even in situations where these indexes are not usable (cannot be exploited) by certain application versions (such as versions <NUM>. <NUM> and earlier).

<FIG> is a block diagram illustrating an example trigger catalog table during addition of a database trigger. In the illustrated embodiment, trigger catalog table <NUM> includes the following columns with metadata specifying triggers stored in database <NUM>: table ID <NUM>, trigger ID <NUM>, trigger name <NUM>, start version <NUM>, and stop version <NUM>. Trigger ID <NUM> and trigger name <NUM>, for example, store values specifying identifiers and names, respectively, for various database triggers.

DBMS <NUM>, in the illustrated embodiment, receives a database command <NUM> from application <NUM> specifying to add a trigger to database <NUM>. Based on this command, DBMS <NUM> generates a new trigger on the bonus column of the employee table in database <NUM>. After adding the trigger to database <NUM>, DBMS performs trigger addition operation <NUM> to add an entry to trigger catalog table <NUM> for the new "bonus_trigger" for application version <NUM>. In this example, the "bonus_trigger" will only execute for applications associated with version <NUM>. <NUM> or later.

<FIG> is a block diagram illustrating an example procedure catalog table during addition of a database procedure. In the illustrated embodiment, procedure catalog table <NUM> includes the following columns with metadata specifying procedures stored in database <NUM>: procedure ID <NUM>, schema ID <NUM>, and procedure name <NUM>. Procedure ID <NUM> and procedure name <NUM> columns, for example, store values specifying identifiers and names, respectively, of various database procedures.

DBMS <NUM>, in the illustrated embodiment, receives a database command <NUM> from application <NUM> specifying to add a procedure to database <NUM>. Based on this command, DBMS <NUM> generates a new procedure called "retirement" for a particular schema (corresponding to application version <NUM>. <NUM> according to schema catalog table <NUM> in <FIG>) in database <NUM>. After adding the new procedure to database <NUM>, DBMS <NUM> performs procedure addition operation <NUM> to add an entry to procedure catalog table <NUM> for the new "retirement" procedure for schema ID <NUM>. In this example, the procedure "retirement" will only be visible to queries for applications associated with version <NUM>.

<FIG> is a block diagram illustrating an example statistic catalog table during addition of a database statistic. In the illustrated embodiment, statistics catalog table <NUM> includes the following columns with metadata specifying statistics for different database table stored in database <NUM>: table ID <NUM>, width statistic <NUM>, null statistic <NUM>, start version <NUM>, and stop version <NUM>. Width statistic <NUM> and null statistic <NUM> are non-limiting examples of the types of statistics that may be generated for a given database table. Width statistic <NUM> may specify, for example, the average stored width, in bytes, of nonnull entries in a given database table.

Database command <NUM>, in the illustrated embodiment, includes an operation for loading statistics for hr. employee database table <NUM> into database <NUM>. After executing this operation, DBMS <NUM> performs statistic addition operation <NUM> to add an entry to statistics catalog table <NUM> that specifies width and null statistics for human resources application version <NUM>. In this example, a query optimizer of the DBMS <NUM> will only be able to use the null statistic for queries from an application associated with version <NUM>. <NUM> or later.

<FIG> is a block diagram illustrating an example database query <NUM>. In the illustrated embodiment, system <NUM> includes application <NUM> and DBMS <NUM>. Application <NUM> sends query <NUM> to DBMS <NUM>. Query <NUM> specifies that application <NUM> is currently operating under version <NUM>. <NUM> and, therefore, this query is requesting to access database objects that are accessible to this version. In addition, query <NUM> specifies to select the data generated by the hr. totalcomp procedure <NUM> for the employee named "John Doe.

The following example description includes references to database objects shown in <FIG>. Application <NUM>, shown in <FIG>, may be a human resources application requesting compensation information for a particular employee (John Doe). In order to respond to query <NUM>, DBMS <NUM> accesses system catalog <NUM> to determine whether the procedure "totalcomp" belongs to schema ID <NUM>. Based on determining that it does indeed belong to schema <NUM>, DBMS <NUM> verifies: <NUM>) schema ID <NUM> is a schema named "HR" and <NUM>) schema ID <NUM> is a schema that matches application version <NUM>. Based on this information, DBMS <NUM> accesses database <NUM> to obtain the results of calculating the total compensation (salary + bonus) for John Doe.

Note that query <NUM> may be satisfied either by accessing hr. employee table <NUM> to obtain salary and bonus information for John Doe, accessing the Hr. totalcomp procedure <NUM> and then plugging these values into the procedure <NUM>, or by accessing the hr. emp_name_comp index <NUM> (if this index is accessible to application version <NUM>. <NUM>---which in this case it is) and obtaining the sum <NUM> of salary <NUM> and bonus <NUM> columns for John Doe. If DBMS <NUM> were to use hr. emp_name_comp index to respond to query <NUM>, this system would first access index catalog table <NUM> to determine that the hr. emp_name_comp index is accessible starting at application version <NUM>. In this example scenario, DBMS <NUM> then checks that the salary, bonus, and name columns of the hr. employee table <NUM> are visible to applications at version <NUM>. Then, DBMS determines that the emp_name and emp name_comp indexes are visible to applications at version <NUM>. Based on this information, DBMS <NUM> would then access database index <NUM> to obtain the data requested by application <NUM>. Had the emp_name_comp index been added in application version <NUM>. <NUM> instead of <NUM>. <NUM>, then DBMS <NUM> would have had to forgo using this index. In the illustrated embodiment, after accessing database <NUM> to obtain the total compensation for John Doe, DBMS <NUM> sends query results <NUM> to application <NUM> specifying that the total compensation for John Doe is $<NUM>,<NUM>.

Turning now to <FIG>, a flow diagram illustrating an example method <NUM> for handling database queries from multiple different application versions, according to some embodiments. The method shown in <FIG> may be used in conjunction with any of the computer circuitry, systems, devices, elements, or components disclosed herein, among other devices. In various embodiments, some of the method elements shown may be performed concurrently, in a different order than shown, or may be omitted. Additional method elements may also be performed as desired.

At <NUM>, in the illustrated embodiment, a computer system executing a database management system (DBMS) receives a database query for a database, where the database query is received from a particular version of an application having a plurality of versions, and wherein the database query specifies the particular version. In some embodiments, prior to receiving the database query for the database, the computer system receives, from a particular version of the application, updates for the application, where the updates are associated with the particular version. In some embodiments, the computer system alters, based on the received updates, the one or more catalog tables, where the altering includes altering metadata stored in the catalog tables that specifies one or more database objects stored in the database that are accessible to the particular version. In some embodiments, the updates indicate one or more differences between database obj ects that are accessible to the particular version and database obj ects that are accessible to an earlier version of the application.

In some embodiments, the DBMS may receive database queries that do not specify a particular application version. For example, in some situations a current application version may be known to the DBMS and this known application version is used for receives queries, unless and until the DBMS receives a SET command (such as the SET commands shown in <FIG> and <NUM>-<NUM>) specifying a different application version.

At <NUM>, the computer system identifies one or more catalog tables, where the one or more catalog tables include one or more version information columns storing version access information. In some embodiments, the one or more version information columns include a start version column and a stop version column that specify a range of application versions for which one or more database objects stored in the database are accessible. For example, the start version column may indicate an application version at which database objects begin to be available and the stop version column may indicate an application version at which database objects are no longer accessible. Said another way, the stop version column may specify an application version (and any later application version) that cannot access the database objects. In some embodiments, the version information columns specify version access information for the one or more database objects. In some embodiments, the one or more database objects include one or more of the following types of objects: schema, table, column, index, trigger, procedure and statistics. The one or more database objects may include any of various combinations of the object types included in this list or any of various other types of database object not explicitly recited in the above list. For example, database objects stored in a database may include a schema object and a database object, a schema object or a table object but not both, all seven types of database objects, or any combination thereof. In some embodiments, one of the catalog tables is a schema catalog table that specifies, for at least one application, a schema that is applicable to that one application. In some embodiments, the schema catalog table specifies schema names that correspond to different applications.

In some embodiments, a database object is accessible to a single application version. In other embodiments, a database object may have a lifetime that spans multiple versions of an application. The lifetime of a database object may be specified by start and stop version columns. In other situations, however, the lifetime of a database objects may be specified by a single column that uses an interval datatype. In this situation, the single column specifies a range of application versions that have access to the database object.

In some embodiments, the version information columns specify version access information for at least a schema object and a database table object of the database, where the schema object is a named collection of tables and the database table object is a particular type of table that stores data in the database for the application having the plurality of versions. In some embodiments, the identified catalog tables include a first catalog table that defines version access information in a first format and a second catalog table that defines version access information in a second format. In some embodiments, the first format specifies version access information as a particular application version and the second format specifies version access information as a range of application versions.

At <NUM>, the computer system determines that version access information for the database query indicates that one or more database objects corresponding to the database query are accessible to the particular version of the application.

At <NUM>, the computer system accesses, based on the determining, one or more database objects that are responsive to the database query and are accessible to the particular version of the application. In some embodiments, the version access information for the database query permits, for the particular version of the application, access to a first portion of data included in the one or more database objects. For example, information stored in the salary column <NUM> included in hr. employee table <NUM> (one example of a database object) may be accessible to the particular version of the application. In some embodiments, in response to receiving an additional database query from a different version of the application, the computing system accesses, based on version access information for the different version of the application, a second portion of the data included in the one or more database objects. For example, information stored in both the salary column <NUM> and bonus column <NUM> included in hr. employee table may be accessible to the different version of the application. In some embodiments, based on receiving database queries from two different versions of an application and their respective version access information, the computer system accesses different database objects. For example, the computing system may determine that hr. employee table <NUM> (one example database object) is accessible to a first application version, while a client table (another example database object) is accessible to a second application version.

In some embodiments, the computer system adds an index table to the database. In some embodiments, based on adding the index table, the computer system adds a row to an index catalog table that includes metadata for one or more index tables in the database, where the row includes respective values for a start version column and a stop version column specifying information indicative of one or more versions of the application for which one or more database objects stored in the database are accessible.

In some embodiments, a particular version of an application transmits a database query for a database to a DBMS, where the database query specifies the particular version of the application. In some embodiments, the database maintains version access information in one or more version information columns of one or more catalog tables. In some embodiments, the particular version of the application receives, from the DBMS, data that is responsive to the database query. In some embodiments, the data is retrieved from the database based on the version access information specifying that the data is accessible to the particular version of the application. In some embodiments, the particular version of the application transmits, to the DBMS, updates for one or more database objects, where the updates are associated with the particular version. In some embodiments, the updates include alterations for metadata stored in one or more catalog tables.

Turning now to <FIG>, a block diagram of a computing device (which may also be referred to as a computing system) <NUM> is depicted, according to some embodiments. Computing device <NUM> may be used to implement various portions of this disclosure. Computing device <NUM> is one example of a device that may be used as a mobile device, a server computer system, a client computer system, or any other computing system implementing portions of this disclosure. For example, computing device <NUM> may implement computing system <NUM> and/or DBMS <NUM>. In some situations, computing device <NUM> executes application <NUM>.

Computing device <NUM> may be any suitable type of device, including, but not limited to, a personal computer system, desktop computer, laptop or notebook computer, mobile phone, mainframe computer system, web server, workstation, or network computer. As shown, computing device <NUM> includes processing unit <NUM>, storage subsystem <NUM>, and input/output (I/O) interface <NUM> coupled via interconnect <NUM> (e.g., a system bus). I/O interface <NUM> may be coupled to one or more I/O devices <NUM>. Computing device <NUM> further includes network interface <NUM>, which may be coupled to network <NUM> for communications with, for example, other computing devices.

Processing unit <NUM> includes one or more processors and, in some embodiments, includes one or more coprocessor units. In some embodiments, multiple instances of processing unit <NUM> may be coupled to interconnect <NUM>. Processing unit <NUM> (or each processor within processing unit <NUM>) may contain a cache or other form of on-board memory. In some embodiments, processing unit <NUM> may be implemented as a general-purpose processing unit, and in other embodiments it may be implemented as a special purpose processing unit (e.g., an ASIC). In general, computing device <NUM> is not limited to any particular type of processing unit or processor subsystem.

As used herein, the term "processing unit" refers to circuitry configured to perform operations. Accordingly, a processing unit may be implemented as a hardware circuit implemented in a variety of ways. The hardware circuit may include, for example, custom very-large-scale integration (VLSI) circuits or gate arrays, off-the-shelf semiconductors such as logic chips, transistors, or other discrete components. A processing unit may also be implemented in programmable hardware devices such as field programmable gate arrays, programmable array logic, programmable logic devices, or the like.

Storage subsystem <NUM> is usable by processing unit <NUM> (e.g., to store instructions executable by and data used by processing unit <NUM>). Storage subsystem <NUM> may be implemented by any suitable type of physical memory media, including hard disk storage, floppy disk storage, removable disk storage, flash memory, random access memory (RAM-SRAM, EDO RAM, SDRAM, DDR SDRAM, RDRAM, etc.), ROM (PROM, EEPROM, etc.), and so on. Storage subsystem <NUM> may consist solely of volatile memory in some embodiments. Storage subsystem <NUM> may store program instructions executable by computing device <NUM> using processing unit <NUM>, including program instructions executable to cause computing device <NUM> to implement the various techniques disclosed herein.

I/O interface <NUM> may represent one or more interfaces and may be any of various types of interfaces configured to couple to and communicate with other devices, according to various embodiments. In some embodiments, I/O interface <NUM> is a bridge chip from a front-side to one or more back-side buses. I/O interface <NUM> may be coupled to one or more I/O devices <NUM> via one or more corresponding buses or other interfaces. Examples of I/O devices include storage devices (hard disk, optical drive, removable flash drive, storage array, SAN, or an associated controller), network interface devices, user interface devices or other devices (e.g., graphics, sound, etc.).

It is noted that the computing device of <FIG> is one embodiment for demonstrating disclosed concepts. In other embodiments, various aspects of the computing device may be different. For example, in some embodiments, additional components, or multiple instances of the illustrated components may be included.

Although specific embodiments have been described above, these embodiments are not intended to limit the scope of the present disclosure, even where only a single embodiment is described with respect to a particular feature. Examples of features provided in the disclosure are intended to be illustrative rather than restrictive unless stated otherwise.

Claim 1:
A method, comprising:
receiving (<NUM>), by a computer system executing a database management system, DBMS, a database query for a database, wherein the database query is received from a particular version of an application having a plurality of versions, and wherein the database query specifies the particular version;
identifying (<NUM>), by the computer system, one or more catalog tables, wherein the one or more catalog tables include version information columns storing version access information, wherein the version information columns comprise a start version column and a stop version column, and wherein the start version column and the stop version column for a given database object specify a range of application versions for which the given database object is accessible;
determining (<NUM>), by the computer system, based on the version access information stored in the start version column and the stop version column that one or more database objects corresponding to the database query are accessible to the particular version of the application; and
accessing (<NUM>), by the computer system based on the determining, one or more database objects that are responsive to the database query and are accessible to the particular version of the application.