Extending database data with intended use information

Abstract

Techniques for storing metadata indicating an intended usage (IU) within a database system are disclosed herein. Once created, an IU may be assigned to a column to (a) indicate how the column is expected to be used, and (b) affect how a database server is performing when database operations involve values of the column. An IU assigned to a column supplements but does not replace the data type definition of the column. Each IU may have an IU bundle packet. The IU bundle packet of the IU indicates how the database server is performing with respect to any column assigned the IU. For example, an IU bundle package may indicate a constraint that a database server must validate during operation on a value of a column assigned to the IU. Techniques for implementing a multi-column IU and a flexible IU are also described.

Description

[0001] Claims

[0002] This application claims the benefit of U.S. Provisional Application No. 63 / 416,052, filed on Oct. 14, 2022, by Tirthankar Lahiri et al., the entire content of which is hereby incorporated by reference herein. Technical Field

[0003] This disclosure relates to database systems, and more particularly, to the ability of a database server to maintain and use metadata regarding the intended use of data in columns. Background Art

[0004] Database systems maintain certain types of information about the tables they manage. This information typically includes information about the data types of the values stored in each column of each table. For example, a table "emp" may have a column "Firstname" with a data type of "string" (or VARCHAR2, CHAR, NCHAR, NVARCHAR2, CLOB, or NCLOB), and a column "SSN" with a data type of "integer".

[0005] Based on the name of the Firstname column, one can infer that the intended use of the Firstname column is to store a string of the employee's first name. Similarly, one can infer that the intended use of the "SSN" column is to store an integer as the employee's social security number. However, from the perspective of the database server, the values in the Firstname column are merely strings without a specific intended use and are thus treated the same as the values in any other column that stores strings. Similarly, for the database server, the SSN column stores an integer without a specific intended use and is thus treated the same as the values in any other column that stores integers.

[0006] In many cases, the values of a column of a given data type need to be treated differently from the values of other columns of the same type. However, since the database server does not know the intended use of the values in these columns, the database application logic must be responsible for implementing the logic for treating columns of the same data type differently based on their intended use.

[0007] Unfortunately, the data type of a column conveys little information about the intended use of that column. For example, knowing the data type of a column does not tell the database server whether the column is used to store: email addresses, names, passwords, URLs, phone numbers, credit card numbers, social security numbers, user IDs, percentages, IP addresses, ages, dates of birth, and so on. The database has no way of distinguishing between any of these because the underlying data is stored as primitive types such as NUMBER or VARCHAR or CLOB.

[0008] Currently, usage information is typically recorded only informally or stored within the tool. The lack of a centralized standard for recording intended use results in fragmented semantic processing by the tool and often leads to inconsistencies.

[0009] One way to allow users to ensure that values in a column will receive special processing is to create user-defined data types or SQL domains corresponding to the intended use. An SQL domain is a data type with optional constraints. Information about SQL domains can be found at www.webeanswers.com / what-is-a-domain-in-sql. Once such a user-defined data type or SQL domain has been defined, the user-defined data type / domain can be declared as the data type of the column in question. For example, a user could create a "phone_number" user-defined data type or domain and declare the column "phone" as that user-defined data type or domain. While this allows values in the "phone" column to be treated differently from integers, there are several drawbacks. For example, once declared to have the type / domain "phone_number", the operations that can be performed on the values in this "phone" column are restricted to those defined for the user-defined data type / domain "phone_number". From the perspective of the database server, the values stored in such a phone column do not have the primitive type NUMBER and thus cannot be manipulated as such. Another drawback of the user-defined data type / domain approach is that it places the burden on the programmer of defining user-defined data types and domains, which can be difficult, error-prone, and time-consuming.

[0010] The methods described in this section are methods that can be adopted, but not necessarily methods that have been previously envisioned or adopted. Therefore, unless otherwise stated, no method described in this section should be assumed to be prior art merely because they are included in this section. BRIEF DESCRIPTION OF THE DRAWINGS

[0011] In the drawings:

[0012] Figure 1 is a block diagram of a table including columns to which multiple flexible IUs can be assigned; and

[0013] Figure 2 It is a block diagram of a computer system on which embodiments of the present invention can be implemented. Detailed implementation manners

[0014] In the following description, for purposes of explanation, numerous specific details are set forth in order to provide a thorough understanding of the present invention. However, it will be apparent that the present invention may be practiced without these specific details. In other instances, well-known structures and devices are shown in block diagram form to avoid unnecessarily obscuring the present invention.

[0015] General overview

[0016] Techniques are disclosed herein for providing developers with a simple and lightweight way to indicate that a column has a specific intended use (e.g., storing a phone number or a URL) without breaking existing applications that use the column. The intended use of a column or a set of columns will be referred to herein as the "IU" of the column or the set of columns. According to one embodiment, each IU is associated with an "intended use label" (or "IU label"). The IU of a column (or a set of columns) is stored as metadata, which supplements rather than replaces the underlying primitive metadata type that indicates the (one or more) columns. Thus, the IU of a column supplements but does not replace the data type definition of the column.

[0017] For example, a column with a primitive data type of NUMBER and an IU of "phone_number" is still considered a NUMBER by the database server and can thus be referenced and manipulated by any function that operates on NUMBER. However, if any database tool or application sends a DESCRIBE request for a table whose target is a column or columns associated with an IU, then the database server returns not only information about the names and primitive data types of these columns, but also information about the IU associated with these columns.

[0018] Since the IU is stored as metadata associated with the specific column or columns in question in the database, the usage information can be utilized by database tools without each tool having to maintain the intended use information separately. Additionally, the complexity of creating user-defined data types and / or domains is avoided.

[0019] According to the embodiments described below, each IU has a corresponding "IU bundle". The IU bundle specifies the IU label. In addition to the IU label, the IU bundle of an IU can also store additional information about the values in the column or columns assigned the IU. For example, in addition to the IU label, the IU bundle can include, for the column or columns assigned the IU:

[0020] · Constraints

[0021] · Default value

[0022] · Sorting strategy

[0023] · Display format

[0024] · Options.

[0025] According to some embodiments, the IU tag is the only required element of an IU bundle. Other elements (such as constraints and default values) are optional.

[0026] The additional information specified in the IU bundle can affect the behavior of the database server. For example, if column X is associated with the IU "age", and the IU "age" has an IU bundle that specifies a constraint that restricts the age value to the range 0 - 150, then if an attempt is made to store the value 998 into column X, the database server will raise an error.

[0027] As another example, if column Y is associated with the IU "salary", and the IU "salary" has an IU bundle that specifies a display format starting with "$", then the database server can automatically format the values retrieved from column Y so that they start with "$".

[0028] In addition to user - defined IU bundles, some IU bundles can be built into the database server to cover common use cases. For example, according to one embodiment, the database system can provide built - in IU bundles for common uses of numeric data types:

[0029] · Percentage, Temperature, Phone Number, Currency, Credit Card, SS#

[0030] And built - in IU bundles for common uses of string data types:

[0031] · Email, US State Name, Country Name or Code, First Name, Last Name, etc.

[0032] Since the uses are easy to specify, it will be much easier to extend this set of built - in uses than for database developers to extend on the set of built - in data types.

[0033] Declaring and Assigning IUs

[0034] According to one embodiment, an IU can be created by a CREATE statement such as the following:

[0035] · CREATE IU Temperature AS NUMBER...

[0036] After receiving this CREATE statement, the database server creates the IU "Temperature" and stores the metadata for that IU in the database server. This metadata indicates, among other things, that the IU label for this IU is "Temperature". The "AS NUMBER" statement indicates that any column with the primitive type NUMBER can be associated with the IU "Temperature". Attempting to assign the IU "Temperature" to any column that does not store the primitive data type NUMBER will result in an error.

[0037] Once an IU bundle has been created, the IU can be assigned to any column that has a primitive data type that matches any of the primitive data types listed in the IU definition. For example, the following statement can create a table "Readings" that has a column "value" whose data type is NUMBER and is associated with the IU "Temperature":

[0038] ·CREATE TABLE Readings(Value NUMBER IU Temperature)

[0039] In one implementation, the ALTER TABLE command can also be used to associate an IU with a column after creating a table that contains a single column. The following statement is an example of such a command:

[0040] ·ALTER TABLE Readings MODIFY COLUMN Value IU Temperature;

[0041] An IU can also be associated with a virtual column, as shown in the following example:

[0042] CREATE TABLE Sales(ID NUMBER,

[0043] Price NUMBER IU Currency,

[0044] Discount NUMBER IU Percent,

[0045] NetPrice AS (PRICE * (1 - DISCOUNT)) IU Currency);

[0046] In this example, a DESCRIBE of the Sales table will indicate that the virtual column "NetPrice" is associated with the IU "Currency". The constraint can be "enabled" to cause the database server to enforce it. If the constraint for the "Currency" IU is enabled to enforce at the database level, then the constraint is checked when the values of the component columns ("Price" and "Discount") in the virtual column expression are modified.

[0047] It should be noted that the keyword "IU" is just one example of a keyword that can be used to create and reference an IU. The techniques described herein can be implemented with any keyword that is considered useful. For example, the keyword DOMAIN can be used to create and assign an IU. However, even in an implementation that uses the keyword DOMAIN, an IU differs from a regular domain in that an IU supplements but does not replace the underlying data type of the column under discussion. For example, an implementation that uses the keyword DOMAIN to create and assign an IU can support the following statements:

[0048] · CREATE DOMAIN Temperature AS NUMBER...

[0049] · CREATE TABLE Readings(Value NUMBER DOMAINTemperature)

[0050] IU Bundle Overview

[0051] As mentioned above, in addition to the IU label, an IU bundle can also specify various types of information about the IU. The information specified in the IU bundle affects how the database server behaves with respect to the values of the column(s) to which the IU bundle is assigned. According to one implementation, an IU bundle can specify:

[0052] · IU label: such as percentage, email, last name, first name, etc.

[0053] · Collation (rules for determining how data is stored), default value

[0054] · Constraint(s): restrict data to values / formats allowed by the usage

[0055] · Display expression (metadata on how to display data): render data as a string, such as to_char() specific to the IU

[0056] · Sort expression: allows sorting of IU values according to IU semantics (such as day of the week) rather than alphabetical or numerical order

[0057] · Optional additional metadata: Can be used to further influence application behavior (e.g., non-default tags, IsSensitive, input / output masking, etc.).

[0058] In one implementation, the syntax for a statement to create an IU bundle is:

[0059] CREATE IU IU-Label AS Type1[OR Type2 OR Type3 OR…

[0060] TypeN]

[0061] DEFAULT expression COLLATE collation

[0062] {CONSTRAINT Name[CHECK condition|NOT NULL|NULL]

[0063] [DISABLE]}*

[0064] [DISPLAY <SQL expression>]

[0065] [ORD <SQL expression>]

[0066] [OPTIONS <free-form feature-value>]

[0067] In this example statement, the IU label is the name of the IU. Preferably, the IU label should not be an existing type name. The "AS" clause specifies the (one or more) primitive data types (e.g., NUMBER, VARCHAR2, etc.) to which the IU can be applied. As explained below, when an IU can be applied to additional compatible data types (e.g., CLOB and VARCHAR2) or NUMBER and BINARY_FLOAT, the "OR" clause is used.

[0068] The CONSTRAINT clause can be used to specify one or more IU constraints and whether these constraints are enabled or disabled. Disabled constraints record their purpose without runtime overhead. Tools can use them to perform validation when entering data.

[0069] The "DISPLAY expr" clause allows specifying an SQL expression to convert the IU value to varchar for display purposes. The display expression can indicate, for example, that for a given numeric IU, its value should be shown in a format that includes a decimal point and two digits after the decimal point.

[0070] The "ORD expr" clause can be used to specify a deterministic SQL expression that converts an IU value into a sortable value. For example, if the IU represents the days of the week, then it may be desirable to sort the values in the order in which they occur (e.g., Monday, Tuesday, Wednesday, etc.) rather than alphabetically (e.g., Friday, Monday, Thursday, etc.).

[0071] The OPTIONS section can be used to specify optional attributes such as format, security rules (encryption, editing, etc.). According to one embodiment, the options can be specified as a series of property-value pairs. The metadata in the OPTIONS section can be interpreted by and affect the behavior of: (a) the client-side validation tool; (b) the database server; (c) both the client-side validation tool and the database server; or (d) neither the client-side validation tool nor the database server. In the latter case, the metadata can only provide information, similar to an inline comment. Examples of metadata that can be specified in the OPTIONS section include:

[0072] · MIN / MAX

[0073] · REGEXP

[0074] · LIST OF VALUES

[0075] · REFERENCE TO TABLE OF LIST OF VALUES

[0076] · UPPER / LOWER (case sensitive / insensitive)

[0077] · PRECISION / SCALE

[0078] For example, the OPTIONS section can also be used to specify which operations are allowed on a column to which an IU has been assigned. For example, the OPTIONS section can indicate whether a GROUP BY or SORT operation can be performed on the column.

[0079] One-to-many relationships between primitive types

[0080] As mentioned above, the "AS" part of the IU binding package specifies one or more primitive types to which the IU binding package can be applied. However, in many cases, it may be desirable for the same IU to be used with multiple primitive data types. For example, assume that a table T is being created that has a column X whose intended use is to store credit card numbers. To indicate this intended use of column X, the IU "credit card" can be associated with column X.

[0081] However, if column X has the type NUMBER, then an error will occur if IU “credit card” is restricted to the primitive data type STRING. Similarly, if column X has the type STRING, then an error will occur if IU “credit card” is restricted to the primitive data type NUMBER. Thus, according to an embodiment, IU “credit card” can be defined to be usable with both NUMBER and STRING. By allowing a one-to-many relationship between an IU and a primitive data type, the creator of table T can independently decide how to internally represent a credit card number while still being able to utilize the IU bundle already defined for credit cards.

[0082] Multi-column IU

[0083] In the example given above, an IU bundle is assigned to a single column. However, in some cases, for IU bundle assignment, it may be desirable to treat a set of columns as a single unit. For example, an address can include a street address, apartment number, city, state, and zip code. Each of these attributes can be stored in a separate column, none of which alone constitutes a complete address. However, taken as a whole, the intended use of this set of columns is to store an address. To handle such cases, embodiments include mechanisms for declaring multi-column IUs and IU bundles.

[0084] The following is an example of how a multi-column IU can be declared according to one embodiment. A US city includes a city name, state, and zip code. Thus, the IU bundle for IU “US city” can be defined as follows:

[0085] CREATE IU US_City(name AS VARCHAR2 OR CLOB,

[0086] state AS VARCHAR2 OR CHAR(2),

[0087] zip AS NUMBER OR VARCHAR2)

[0088] CONSTRAINT City_CK CHECK(<function to check valid combination>)

[0089] PROPERTIES{DISPLAY_DEFAULT:sql_expr(“name||”,”||state||”,”||TO_CHAR(zip))};

[0090] In the above example, DISPLAY_DEFAULT indicates how the data associated with IUUS_City should be displayed by default. In this particular example, the city name is followed by a comma, then the state name, followed by another comma, and finally the postal code (converted from the internal postal code format to a string). Once multiple-column IUs have been defined, they can be applied to multiple-column groups, as in the following example:

[0091] CREATE TABLE Customer(Id NUMBER,CustName VARCHAR2,

[0092] CustCity VARCHAR2,

[0093] CustState VARCHAR2,

[0094] CustZip NUMBER,

[0095] IU US_City(CustCity,CustState,CustZip));

[0096] In one implementation, the database server supports dynamically adding or deleting IU associations after a table has been created. According to one embodiment, for multiple-column IUs, they can be dynamically enabled / disabled by using commands such as the following:

[0097] ·ALTER TABLE Customer ADDIU US_City(CustCity,

[0098] CustState,CustZip))[VALIDATE|

[0099] NOVALIDATE];(enforce / no-enforce)

[0100] ·ALTER TABLE Customer DELETE IU US_City(CustCity,

[0101] CustState,CustZip);

[0102] The content of an IU bundle assigned to a specific set of columns affects how the database server behaves during operations involving those columns in the same way as described for a single-column IU bundle. For example, in response to an operation that changes a value in any of the columns involved in an IU, the database server can check whether those changes violate any constraints defined in a multi-column IU bundle. Additionally, as described above, a multi-column IU bundle affects the display, sorting, etc. of the values of the columns associated with the IU bundle. For example, a multi-column IU can include constraints involving two or more of its columns. Thus, a multi-column JOB IU can include a column for storing a start date and a column for storing an end date, and define a constraint that the end date cannot be earlier than the start date.

[0103] IU bundles for query results and views

[0104] In the previous discussion, it was explained that an IU bundle can be associated with columns of a table, virtual columns, and even sets of columns. However, in one embodiment, an IU bundle can be associated with any set of values maintained in or returned by the database, including query results and views.

[0105] Regarding views, in one embodiment, view columns corresponding to underlying table columns are considered by the database server to be associated with any IU bundle associated with that underlying table column. Projected (untransformed) values retain the intended use of the column, as in the following example:

[0106] CREATE VIEW C500_READINGS ASSELECT temp C500 FROM Readings WHERE ID=500;

[0107] => C500 should retain the intended use of temperature

[0108] As another example:

[0109] => "create table as select Temp" will retain the appropriate IU bundle associations in the destination table.

[0110] In one embodiment, an IU is not automatically retained in expression evaluation, but can be associated with columns of a view or query result (using IU CAST), as shown in the following example:

[0111] · CREATE VIEW Contact(ID,cust_info)AS

[0112] SELECT CAST(cust_info AS[IU]Email VALIDATE)FROM

[0113] <subquery>;)

[0114] => Metadata for cust_info will have an IU of email

[0115] In one embodiment, if requested (or if a constraint is enabled), then CAST re - validates any IU constraints. That is, the execution of the view retrieves data from the underlying tables and checks the retrieved data against the constraints defined in the IU (e.g., email) to which the data is to be cast. In one implementation, the "IU" keyword is optional.

[0116] In one implementation, aggregation will only retain IU associations when explicitly requested, as in the following example:

[0117] SELECT CAST(avg(temp)AS[IU]Temperature),AvgTemp from Readings;

[0118] In one implementation, an IU bundle assignment will only be retained in the result set if it is known via static type inference that all possible elements of the result set will have that IU. For example:

[0119] ·UNION / INTERSECT[ALL]: If both operands have the same IU,

[0120] then retain the IU

[0121] ·MINUS[ALL]: Only retain the IU of the first operand

[0122] ·CASE / DECODE / NVL: If all operands have the same IU, then retain the IU

[0123] Example:

[0124] ·SELECT Score FROM Grades2020[UNION|INTERSECT]

[0125] SELECT Score FROM Grades2021:

[0126] In this example, the "Score" column in the result will only have an IU Percent if the Score columns in the Grades2020 and Grades2021 tables both have an IU Percent.

[0127] ·SELECT Score FROM Grades2020 MINUS SELECT Score

[0128] FROM Grades2021:

[0129] In this example, "Score" has an IU Percent only if the Grades2020 table has an IU Percent (since only that table contributes rows to the result).

[0130] ·NVL(score1,score2)

[0131] In this example, if both score1 and score2 have an IU Percent, then the result has that IU Percent.

[0132] According to one embodiment, the database server processes a value CAST to a particular IU bundle in the same manner as the values of the columns defined to have that particular IU bundle. For example, in response to a column referenced in a SELECT statement being cast as IUTemperature, if the values in the result set violate any constraints specified for temperature, then the database server can raise an error. Additionally, other aspects of those values, such as display and sort order, are also affected by the information in the IU bundle for temperature.

[0133] According to one embodiment, a multi-column IU can also be specified for query results and views. For example, a US city IU can be assigned to the columns of a view as follows:

[0134] ·CREATE VIEW CustCity(Id,City,State,Zip)AS

[0135] SELECT CustId,CAST((CustCity,CustState,CustZip)AS IU

[0136] US_City)

[0137] FROM Customer;

[0138] This IU can now be seen from the DESCRIBE of the view, and the text of the constraints, properties, etc. is again available for the application to use.

[0139] IU Bundles and Elastic Fields

[0140] "Flexible fields" are "spare" columns that a database application can use as needed. The use of flexible fields often varies from row to row based on the value(s) in one or more other (discriminator) columns. For example, assume a table is used to store information about expenses. The table can include a column "EXPENSE_TYPE" that indicates the type of expense, and several spare columns (ATTR_1, ATTR_2, ATTR_3, ATTR_NUMBER_1) that store information about the expense. The use of those spare columns for each row can depend on whether the EXPENSE_TYPE column for that row has the value "Flight", "Meals", or "Lodging", as follows:

[0141] · Flight: ATTR_1 is Flight No, ATTR_2 is Origin, ATTR_3 is Destination, and all other spares are NULL

[0142] · Meals: ATTR_1 is Restaurant Name, ATTR_2 is MealType

[0143] (e.g., breakfast), ATTR_NUMBER_1 is No.of diners, and all other spares are NULL

[0144] · Lodging: ATTR_1 is HotelName, ATTR_NUMBER_1 is No.

[0145] of Nights, and all other spares are NULL

[0146] In this example, the intended use of the ATTR_1 column varies based on the value in the EXPENSE_TYPE column. Specifically, Flight, Meals, or Lodging in the EXPENSE_TYPE column causes the ATTR_1 column to be used for flight number, restaurant name, and hotel name, respectively.

[0147] Reference Figure 1 , which is a block diagram illustrating a populated table that utilizes flexible fields.

[0148] According to one embodiment, a database server supports a "flexible IU" that can be used in conjunction with flexible fields. The metadata for the flexible IU includes:

[0149] · Metadata that identifies a set of candidate IUs, and

[0150] · A function that returns the IU belonging to a given row from the set of candidate IUs

[0151] The function that returns the IU for a given row is referred to in this text as the IU selection function for flexible IUs. When the database server performs an operation involving columns that have been assigned to a flexible IU, the database server executes the IU selection function for the flexible IU for each row affected by the operation to determine which IU corresponds to that row. Once the correct IU has been identified for a given row, the database server behaves according to the content of the IU bundle of the selected IU (e.g., validating constraints, etc.).

[0152] A flexible IU assigns a specific (multi-column) IU to a set of value columns based on a mapping expression consisting of one or more discriminant columns. Discriminant columns are those columns whose values, for a given row, determine the IU applicable to that given row. The format used to specify a flexible IU can be as follows:

[0153] ·CREATE FLEXIBLE IU FlexIUName(val1,val2,…valN)

[0154] [CHOOSE[IU]]USING(disc1,disc2,…discM)IN<expression returning a single IU>

[0155] In this example, val1,…valN and disc1,…discM are formal parameters that will be replaced by column names as actual parameters when the IU is applied to a table.

[0156] Discriminant columns can be independent or can overlap with value columns. For example, exp_type can be one of the value columns covered by the expense_details conditional IU. If an IU consists of a single json column, then the discriminant can be either one or more independent columns, or one or more fields within the json. For example, if all flexible fields for expenses are stored in a single details json column, then the discriminant can also be "details.exp_type".

[0157] An example of a statement used to create a flexible IU for the expense information shown in Figure 1 is as follows:

[0158] ·CREATE FLEXIBLE IU Expense_Details(val1,val2,val3,val4)

[0159] CHOOSE IU USING(typ_col)

[0160] IN DECODE(typ_col,‘Flight’,Flight_Details,‘Meals’,

[0161] Meals_Details, ‘Lodging’, Lodging_Details)

[0162] The flexible IU defined as such can be applied to the Expenses table, using the column names as arguments (attr1, attr2, attr3, attr4 are flexible field columns), as follows:

[0163] · ALTER TABLE EXPENSES ADDIU Expense_Details(attr1,

[0164] attr2, attr3, attr4)

[0165] CHOOSE IU USING(exp_type)

[0166] In an alternative embodiment, the IU selection function of the flexible IU can use a mapping table. For example, such a table can include an Exp_Type column and an IU column. In this example, a row of the mapping table will include "Flight" in the Exp_Type column and "Flight_Detail" in the IU column. Another row will include "Lodging" in the Exp_Type column and "Lodging_Detail" in the IU column. Another row will include "Meals" in the Exp_Type column and "Meals_Detail" in the IU column. When the database server needs to select an IU for a specific row in the Expense table, the database server will perform a lookup in the mapping table to determine the IU applicable to that row.

[0167] In yet another embodiment, the IU selector query can include logic for performing the IU selector function. For example, assume there is one IU for low expense amounts and another IU for high expense amounts. The query for choosing between the two can be: CREATE FLEXIBLE IU Expense_Details(val1, val2, val3, val4)

[0168] CHOOSE IU USING(typ, amt)IN

[0169] SELECT IU from Expense_IUs where

[0170] Exp_type = :typ and :amt >= exp_min and :amt < exp_max

[0171] If mapping the discriminant column to the domain logic is too complex to be expressed via a query, the most general approach for the IU selection function is to use a mapping function. This can be the case, for example, when the expense IU can depend on expense type, amount, department, employee position code, and expense date, etc.

[0172] The Expense_IU PL / SQL package can be constructed with the expense_IU_map() function to encapsulate the mapping logic. In this case, the flexible IU can be defined as follows:

[0173] ·CREATE FLEXIBLE IU Expense_Details(val1,val2,val3,val4)

[0174] CHOOSE IU USING(typ,amt,dept,jobcode,dat)IN

[0175] Expense_IU_Pkg.Expense_Domain_Map(typ,amt,dept,jobcode,dat)

[0176] Hardware Overview

[0177] According to one embodiment, the techniques described herein are implemented by one or more special-purpose computing devices. The special-purpose computing device can be hard-wired to perform the techniques, or can include digital electronic devices (such as one or more application-specific integrated circuits (ASICs) or field-programmable gate arrays (FPGAs) that are persistently programmed to perform the techniques), or can include one or more general-purpose hardware processors programmed to perform the techniques according to program instructions in firmware, memory, other storage devices, or a combination. Such special-purpose computing devices can also combine custom hard-wired logic, ASICs, or FPGAs with custom programming to implement the techniques. The special-purpose computing device can be a desktop computer system, a portable computer system, a handheld device, a networking device, or any other device that combines hard-wired and / or program logic to implement the techniques.

[0178] For example, Figure 2 is a block diagram of a computer system 200 on which embodiments of the present invention can be implemented. The computer system 200 includes a bus 202 or other communication mechanism for conveying information, and a hardware processor 204 coupled to the bus 202 for processing information. The hardware processor 204 can be, for example, a general-purpose microprocessor.

[0179] The computer system 200 also includes a main memory 206 (such as random access memory (RAM) or other dynamic storage devices) coupled to the bus 202 for storing information and instructions to be executed by the processor 204. The main memory 206 may also be used to store temporary variables or other intermediate information during the execution of instructions by the processor 204. When these instructions are stored in a non-transitory storage medium accessible by the processor 204, the computer system 200 becomes a special-purpose machine customized to perform the operations specified in the instructions.

[0180] The computer system 200 also includes a read-only memory (ROM) 208 or other static storage devices coupled to the bus 202 for storing static information and instructions for the processor 204. A storage device 210 (such as a magnetic disk, optical disk, or solid-state drive) is provided and coupled to the bus 202 for storing information and instructions.

[0181] The computer system 200 may be coupled via the bus 202 to a display 212 (such as a cathode ray tube (CRT)) for displaying information to a computer user. An input device 214 including alphanumeric keys and other keys is coupled to the bus 202 for transmitting information and command selections to the processor 204. Another type of user input device is a cursor control 216 (such as a mouse, trackball, or cursor direction keys) for transmitting direction information and command selections to the processor 204 and for controlling the movement of a cursor on the display 212. Such input devices typically have two degrees of freedom in two axes (a first axis (e.g., x) and a second axis (e.g., y)), which allows the device to specify a position in a plane.

[0182] The computer system 200 can be implemented using custom hardwired logic, one or more ASICs or FPGAs, firmware, and / or program logic that, in combination with the computer system, cause or program the computer system 200 to be a special-purpose machine. According to one embodiment, in response to the processor 204 executing one or more sequences of one or more instructions contained in the main memory 206, the computer system 200 performs the techniques described herein. These instructions can be read into the main memory 206 from another storage medium, such as the storage device 210. Execution of the instruction sequence contained in the main memory 206 causes the processor 204 to perform the processing steps described herein. In an alternative embodiment, hardwired circuitry may be used in place of or in combination with software instructions.

[0183] As used herein, the term "storage medium" refers to any non-transitory medium that stores data and / or instructions that cause a machine to operate in a particular manner. Such storage medium may include non-volatile media and / or volatile media. Non-volatile media includes, for example, optical discs, magnetic disks, or solid state drives such as storage device 210. Volatile media includes dynamic memory such as main memory 206. Common forms of storage medium include, for example, floppy disks, flexible disks, hard disks, solid state drives, magnetic tape, or any other magnetic data storage medium, CD-ROM, any other optical data storage medium, any physical medium with hole patterns, RAM, PROM, and EPROM, FLASH-EPROM, NVRAM, any other memory chip or cartridge tape.

[0184] A storage medium is different from a transmission medium but can be used in combination with a transmission medium. A transmission medium participates in transferring information between storage media. For example, a transmission medium includes coaxial cables, copper wires, and optical fibers, including the wires that comprise bus 202. A transmission medium can also take the form of acoustic waves or light waves, such as those generated during radio wave and infrared data communications.

[0185] Various forms of media can participate in carrying one or more sequences of one or more instructions to processor 204 for execution. For example, the instructions can initially be carried on a disk or solid state drive of a remote computer. The remote computer can load the instructions into its dynamic memory and send the instructions over a telephone line using a modem. A modem local to computer system 200 can receive the data over the telephone line and convert the data into an infrared signal using an infrared transmitter. An infrared detector can receive the data carried in the infrared signal, and appropriate circuitry can place the data on bus 202. Bus 202 transfers the data to main memory 206, and processor 204 retrieves and executes the instructions from main memory 206. The instructions received by main memory 206 can optionally be stored on storage device 210 before or after being executed by processor 204.

[0186] Computer system 200 also includes a communication interface 218 coupled to bus 202. Communication interface 218 provides two-way data communication coupled to network link 220, where network link 220 is connected to local network 222. For example, communication interface 218 can be an integrated services digital network (ISDN) card, cable modem, satellite modem, or a modem that provides a data communication connection to a corresponding type of telephone line. As another example, communication interface 218 can be a local area network (LAN) card to provide a data communication connection to a compatible LAN. A wireless link can also be implemented. In any such implementation, communication interface 218 sends and receives electrical, electromagnetic, or optical signals that carry digital data streams representing various types of information.

[0187] Network link 220 typically provides data communication to other data devices via one or more networks. For example, network link 220 can provide a connection via local network 222 to a main computer 224 or to a data device operated by an Internet service provider (ISP) 226. The ISP 226 in turn provides data communication services via the global packet data communication network (now commonly referred to as the "Internet" 228). Both the local network 222 and the Internet 228 use electrical, electromagnetic, or optical signals that carry digital data streams. Signals through the various networks and signals on network link 220 and through communication interface 218, which carry digital data to and from computer system 200, are example forms of transmission media.

[0188] Computer system 200 can send messages and receive data, including program code, via (one or more) networks, network link 220, and communication interface 218. In the Internet example, server 230 can send the requested code for an application program via the Internet 228, ISP 226, local network 222, and communication interface 218.

[0189] The received code can be executed by processor 204 when received, and / or stored in storage device 210 or other non-volatile memory for later execution.

[0190] In the foregoing specification, embodiments of the invention have been described with reference to numerous specific details, which may vary according to implementation. Accordingly, the specification and drawings are to be regarded in an illustrative rather than a restrictive sense. The sole and exclusive indication of the scope of the invention and what the applicant intends to be the scope of the invention is the literal and equivalent scope of the resulting claims in the specific form of the set of claims that arise from this application, including any subsequent corrections.< / subquery>

Claims

1. A method, comprising: In response to an input received by a database server, the database server stores in a database managed by the database server: Assigning metadata of a primitive data type to each column in one or more columns of one or more tables, and Assigning metadata of an intended use (IU) bundle to at least one row composed of the one or more columns; In response to assigning the primitive data type to each column in the one or more columns, the database server allows all operations supported by each corresponding primitive data type to be performed on the values stored in the corresponding one or more columns; And In response to assigning the IU bundle to at least one row composed of the one or more columns, the database server performs at least one of the following: Allowing a database application to obtain information about the intended use of the one or more columns specified in the IU bundle, or The database server enforces the constraints specified in the IU bundle with respect to the values inserted into the at least one row; Wherein the method is executed by one or more computing devices.

2. The method according to claim 1, wherein: The one or more columns consist of specific columns; The one or more tables consist of specific tables; and The at least one row includes all rows of the specific table.

3. The method according to claim 2, wherein: The IU bundle includes an IU label, and Allowing a database application to obtain the information specified in the IU bundle includes providing the IU label to the database application in response to a request from the database application to interpret the specific table.

4. The method according to claim 2, wherein: The IU bundle includes a set of IU-specific property-value pairs, and Allowing a database application to obtain the information specified in the IU bundle includes providing the set of IU-specific property-value pairs to the database application in response to a request from the database application for information about the intended use of the specific column.

5. The method according to claim 2, wherein the IU bundle specifies information that affects how the database server behaves in response to a database command involving the specific column.

6. The method according to claim 5, wherein the IU bundle specifies constraints, and when any operation involving the specific column will cause the data in the specific column to violate any of the constraints specified in the IU bundle, the database server raises an error.

7. The method according to claim 5, wherein the IU bundle specifies a display expression, and the database server causes the values from the specific column to be displayed in a manner consistent with the display expression.

8. The method according to claim 5, wherein the IU bundle specifies a sorting expression, and in response to a database command to sort the values from the specific column, the database server sorts the values from the specific column in an order based on the sorting expression.

9. The method according to claim 5, wherein the IU bundle specifies that one or more specific operations cannot be performed on the values associated with the IU bundle, and the database server raises an error in response to any command requesting the execution of any of the one or more specific operations.

10. The method according to claim 9, wherein the one or more specific operations include SORT.

11. The method according to claim 9, wherein the one or more specific operations include GROUP BY.

12. The method according to claim 5, wherein the IU bundle specifies a deterministic expression for converting the values from the specific column into sortable values.

13. The method according to claim 2, further comprising the database server receiving data defining an IU bundle and storing it in the database.

14. The method according to claim 13, wherein: The data defining the IU bundle specifies a plurality of primitive types; Wherein the plurality of primitive types includes the primitive type assigned to the specific column; And The database server is configured to raise an error in response to any attempt to assign an IU bundle to any column assigned a primitive type that does not belong to the plurality of primitive types.

15. The method according to claim 1, wherein: The one or more columns include a specific set of two or more columns; The at least one row includes all rows of the one or more tables; and The IU bundle is a single multi-column intended use (IU) bundle.

16. The method according to claim 15, further comprising: In response to a database command involving any column from the specific set of two or more columns, the database server behaves in a manner based on the information specified in the multi-column IU bundle.

17. The method according to claim 1, wherein: The one or more columns include a specific set of two or more columns; The IU bundle is a flexible intended use (IU) bundle assigned to the specific set of the one or more columns; and The flexible IU specifies a plurality of candidate IUs.

18. The method according to claim 17, further comprising: In response to a database command operating on a row including any column from the specific set of the one or more columns, the database server: Selects a specific IU applicable to the row from the plurality of candidate IUs; and Behaves in a manner based on the information specified in the specific IU bundle associated with the specific IU.

19. The method according to claim 18, wherein the flexible IU specifies the manner of selecting the specific IU from the plurality of candidate IUs based on data included in one or more discriminant columns of the row.

20. One or more non-transitory computer-readable media storing instructions that, when executed by one or more computing devices, cause the execution of the method according to any one of claims 1 to 19.

Images