Versioning in editable tables

Versioning in editable tables including receiving, by a table manager via a client computing system, an instruction to edit a first row in an editable table in a cloud-based data warehouse, wherein the instruction comprises a column value; determining, by the table manager, a version value for the edit to the first row; and generating, by the table manager, a database statement instructing the cloud-based data warehouse to create, in the editable table, a second row including the version value and the column value.

BACKGROUND

Field of the Invention

The field of the invention is data processing, or, more specifically, methods, apparatus, and products for versioning in editable tables.

Description of Related Art

Modern businesses may store large amounts of data in remote databases within cloud-based data warehouses. This data may be accessed using database statement languages, such as structured query language (SQL). Manipulating the data stored in the database may require constructing complex queries beyond the abilities of most users. Further, composing and issuing database queries efficiently may also be beyond the abilities of most users.

SUMMARY

Methods, systems, and apparatus for versioning in editable tables including receiving, by a table manager via a client computing system, an instruction to edit a first row in an editable table in a cloud-based data warehouse, wherein the instruction comprises a column value; determining, by the table manager, a version value for the edit to the first row; and generating, by the table manager, a database statement instructing the cloud-based data warehouse to create, in the editable table, a second row including the version value and the column value.

DETAILED DESCRIPTION

Exemplary methods, apparatus, and products for versioning in editable tables in accordance with the present invention are described with reference to the accompanying drawings, beginning withFIG.1.FIG.1sets forth a block diagram of automated computing machinery comprising an exemplary intermediary computing system152configured for versioning in editable tables according to embodiments of the present invention. The intermediary computing system152ofFIG.1includes at least one computer processor156or ‘CPU’ as well as random access memory168(‘RAM’) which is connected through a high speed memory bus166and bus adapter158to processor156and to other components of the intermediary computing system152.

Stored in RAM168is an operating system154. Operating systems useful in computers configured for versioning in editable tables according to embodiments of the present invention include UNIX™, Linux™, Microsoft Windows™, AIX™, and others as will occur to those of skill in the art. The operating system154in the example ofFIG.1is shown in RAM168, but many components of such software typically are stored in non-volatile memory also, such as, for example, on data storage170, such as a disk drive. Also stored in RAM is the table manager126, a module for versioning in editable tables according to embodiments of the present invention.

The intermediary computing system152ofFIG.1includes disk drive adapter172coupled through expansion bus160and bus adapter158to processor156and other components of the intermediary computing system152. Disk drive adapter172connects non-volatile data storage to the intermediary computing system152in the form of data storage170. Disk drive adapters useful in computers configured for versioning in editable tables according to embodiments of the present invention include Integrated Drive Electronics (‘IDE’) adapters, Small Computer System Interface (‘SCSI’) adapters, and others as will occur to those of skill in the art. Non-volatile computer memory also may be implemented for as an optical disk drive, electrically erasable programmable read-only memory (so-called ‘EEPROM’ or ‘Flash’ memory), RAM drives, and so on, as will occur to those of skill in the art.

The example intermediary computing system152ofFIG.1includes one or more input/output (‘I/O’) adapters178. I/O adapters implement user-oriented input/output through, for example, software drivers and computer hardware for controlling output to display devices such as computer display screens, as well as user input from user input devices181such as keyboards and mice. The example intermediary computing system152ofFIG.1includes a video adapter209, which is an example of an I/O adapter specially designed for graphic output to a display device180such as a display screen or computer monitor. Video adapter209is connected to processor156through a high speed video bus164, bus adapter158, and the front side bus162, which is also a high speed bus.

The communications adapter167is communicatively coupled to a wide area network190that also includes a cloud-based data warehouse192and a client computing system194. The cloud-based data warehouse192is a computing system or group of computing systems that hosts a database or databases for access over the wide area network190. The client computing system194is a computing system that accesses the database using the table manager126.

FIG.2shows an exemplary system for versioning in editable tables according to embodiments of the present invention. As shown inFIG.2, the system includes a client computing system194, an intermediary computing system152, and a cloud-based data warehouse192. The client computing system194includes a graphical user interface (GUI)202. The intermediary computing system152includes a table manager126. The cloud-based data warehouse192includes a database204. The client computing system194may access the cloud-based data warehouse192and database204via the table manager on the intermediary computing system152.

The GUI202is a visual presentation configured to present data sets in the form of worksheets, workbooks, and graphical elements to a user. The GUI202also receives requests from a user for data sets from the database204. The GUI202may also present to the user the ability to add a new row into a data set or table and enter values for each column of the new row. The GUI202may be presented, in part, by the table manager126and displayed on a client computing system194(e.g., on a system display or mobile touchscreen). The GUI202may be part of an Internet application that includes the table manager126and is hosted on the intermediary computing system152.

The database204is a collection of data and a management system for the data. A data set is a collection of data (such as a table) from the database204. Data sets may be organized into columns and rows (also referred to as records). The particular columns, rows, and organization of the columns and rows that make up a data set may be specified in the database statement requesting the data set. Data sets may be sent from the cloud-based data warehouse192in response to a database statement (also referred to as a query). Accordingly, data sets retrieved in response to a database statement may be referred to as query results.

The table manager126is hardware, software, or an aggregation of hardware and software configured to receive instructions in the form of state specifications from the client computing system194, via the GUI202. The table manager126is also configured to generate database statements in response to manipulations of the GUI202described in the state specification.

The state specification is a collection of data describing inputs into the GUI202. The state specification may include manipulations of GUI elements within the GUI202along with data entered into the GUI202by a user of the client computing system194. Such manipulations and data may indicate requests for and manipulations of data sets. Such manipulations and data may also indicate requests to create a new row and values for that new row. The state specification may be a standard file format used to exchange data in asynchronous browser-server communication. For example, the state specification may be a JavaScript Object Notation specification. The state specification may include descriptions of elements that are used to apply changes to the data set. Such elements may include filters applied to the worksheet, the hierarchical level of the worksheet, joins performed within the worksheet, exposable parameters in the worksheet, and security for the worksheet.

The table manager126uses the state specification as input to generate a database statement. This database statement generation process may begin with state specification being converted into an abstract syntax tree. The abstract syntax tree may then be canonicalized into a canonicalized hierarchy. The canonicalized hierarchy may then be linearized into the worksheet algebra. The worksheet algebra may then be lowered into a relational algebra, which may then be lowered into the database statement.

The table manager126may use the database statement to fetch query results (i.e., a data set) from the database204. The table manager126may then present the query results to a user via the GUI202. The table manager126may further manage tables on the database202to which a user has made edits, such as adding new rows or editing existing rows. Further, the table manager126may add columns to new rows and create new tables on the database202.

FIG.3shows an exemplary system for versioning in editable tables according to embodiments of the present invention. As shown inFIG.3, the exemplary GUI202includes a spreadsheet structure302and a list structure304. The spreadsheet structure302includes a worksheet (shown as empty rows) with six columns (column A306A, column B306B, column C306C, column D306D, column E306E, column F306F). The combination of a worksheet and dynamic graphic elements may be referred to as a workbook.

The spreadsheet structure302is a graphical element and organizing mechanism for a worksheet that presents a data set. A worksheet is a presentation of a data set (such as a table) from a database on a data warehouse. The spreadsheet structure302displays the worksheet as rows of data organized by columns (column A306A, column B306B, column C306C, column D306D, column E306E, column F306F). The columns delineate different categories of the data in each row of the worksheet. The columns may also be calculation columns that include calculation results using other columns in the worksheet.

The list structure304is a graphical element used to define and organize the hierarchical relationships between the columns (column A306A, column B306B, column C306C, column D306D, column E306E, column F306F) of the data set. The term “hierarchical relationship” refers to subordinate and superior groupings of columns. For example, a database may include rows for an address book, and columns for state, county, city, and street. A data set from the database may be grouped first by state, then by county, and then by city. Accordingly, the state column would be at the highest level in the hierarchical relationship, the county column would be in the second level in the hierarchical relationship, and the city column would be at the lowest level in the hierarchical relationship.

The list structure304presents a dimensional hierarchy to the user. Specifically, the list structure304presents levels arranged hierarchically across at least one dimension. Each level within the list structure304is a position within a hierarchical relationship between columns (column A306A, column B306B, column C306C, column D306D, column E306E, column F306F). The keys within the list structure304identify the one or more columns that are the participants in the hierarchical relationship. Each level may have more than one key.

One of the levels in the list structure304may be a base level. Columns selected for the base level provide data at the finest granularity. One of the levels in the list structure304may be a totals or root level. Columns selected for the totals level provide data at the highest granular level. For example, the totals level may include a field that calculates the sum of each row within a single column of the entire data set (i.e., not partitioned by any other column).

The GUI202may enable a user to drag and drop columns (column A306A, column B306B, column C306C, column D306D, column E306E, column F306F) into the list structure304. The order of the list structure304may specify the hierarchy of the columns relative to one another. A user may be able to drag and drop the columns in the list structure304at any time to redefine the hierarchical relationship between columns. The hierarchical relationship defined using the columns selected as keys in the list structure304may be utilized in charts such that drilling down (e.g., double click on a bar), enables a new chart to be generated based on a level lower in the hierarchy.

The GUI202may also include a mechanism for a user to request a table from a database to be presented as a worksheet in the GUI202. Such a mechanism may be part of the interactivity of the worksheet. Specifically, a user may manipulate a worksheet (e.g., by dragging and dropping columns or rows, resorting columns or rows, etc.) and, in response, the GUI202may generate a request (e.g., in the form of a state specification) for a data set and send the request to the table manager126. Such a mechanism may also include a direct identification of the rows and columns of a database table that a user would like to access (e.g., via a selection of the rows and columns in a dialog box). The GUI202may also include a mechanism for a user to create a new table on the database, add rows to a table, and move rows within the table.

For further explanation,FIG.4sets forth a flow chart illustrating an exemplary method for versioning in editable tables according to embodiments of the present invention that includes receiving402, by a table manager126via a client computing system, an instruction420to edit a first row in an editable table in a cloud-based data warehouse192, wherein the instruction420comprises a column value. Receiving402the instruction420to edit a first row in an editable table in a cloud-based data warehouse192may be carried out by detecting that a user has manipulated elements of the GUI and/or submitted data using the GUI such that the generation of the instruction420is triggered, and the instruction420is sent to the table manager126.

The editable table is a table on the cloud-based data warehouse192within which the first row is being edited. The editable table may be any table on the cloud-base data warehouse that the user and/or the table manager has authorization to edit. The editable table may also be a table that the user and/or the table manager does not have authorization to edit or does not desire to edit, but may be added to using a companion table. The editable table may include any number of columns and rows. When presented on the GUI on the client computing system, only a portion of the columns may be displayed while others are hidden and not presented. Such hidden columns may include the version value, primary key value, and sequence value.

The instruction420may be received in the form of a state specification from the GUI. Specifically, each change to the GUI may result in a new or updated state specification420that operates as an instruction to the table manager126. The initial presentation of the editable table may be in response to a table request made through the GUI. For example, a user of the client computing system may select the editable table from a group of tables presented for display on the GUI. The instruction420to edit the first row may be received without presenting402the editable table.

The instruction420to edit the first row may include column values for at least one column in the row. A column value is a value for a particular column. For example, “LG”, “Denon”, and “HTD” are values for column B in Table 1. The new column values may be received in response to the user overwriting an existing column value presented in the GUI with the new column value. The instruction420to edit the first row may also be an instruction to move the first row to a different location within the editable table. A move instruction may include an indication of the location in the editable table for the first row. The location may be received as an identifier of one or more adjacent rows. The instruction420to edit the first row may also include the primary key of the first row.

The method ofFIG.4also includes determining404, by the table manager126, a version value for the edit to the first row. A version value is an identifier of a particular state within the evolution of the editable table. Each change made to the editable table, including edits to rows, may be associated with a particular version value. Changes made to the editable table that share a particular characteristic may be grouped together by the same or similar version numbers. Such a particular characteristic of the edits may include edits made during a particular period of time and/or by a particular user or group of users. For example, edits made to the editable table during the same week may each have the same version value. Version values may increase with time (or decrease if negative numbers are used).

Determining404the version value for the edit to the first row may be carried out by retrieving a current version value from a version engine. A version engine is a mechanism that generates the version value for a particular edit. For example, the version engine may store the version value associated with the current time period. The version engine may generate the version value for the current edit based on, for example, date and time, user making the edit, characteristics of the user making the edit (such as title or geographical location of the user), and type of edit being made (such as addition or replacement or location within the editable table of the edit). Determining404the version value for the edit to the first row may also be carried out by calculating a current version based on current conditions of the system. For example, the version value may be calculated by truncating a portion of the current time and date.

The method ofFIG.4also includes generating406, by the table manager126, a database statement424instructing the cloud-based data warehouse192to create, in the editable table, a second row including the version value and the column value. Instead of changing the first row in the editable table on the cloud-based data warehouse192, the second row is created to be presented instead of the first row for requests for the version of the editable table greater than or equal to the version value for the second row and less than any greater version value of the same row created at a later date. For example, if the first row includes a version value of 2.0, the second row may include a version value of 2.1. Any request for the editable table with a version of 2.1 or greater will be presented with the second row (until a later version of the second row is created). Similarly, any request for the editable table with a version between 2.0 (inclusive) and 2.1 (exclusive), will be presented with the first row.

The first row and the second row may both include the same primary key value. A primary key value is a value that uniquely identifies a row across edits made to that row. Having the same primary key value indicates that the first row and the second row are different versions of a same row. Different versions of the same row may share the same primary key value. Because the second row is an edited version of the first row, both the first row and the second row will have the same primary key value but different version values.

Generating406the database statement424may be carried out by converting the column value, the version value, and a primary key value into a database statement targeting the database on the cloud-based data warehouse192. Specifically, the column value, version value, and primary key value may be used to populate a database statement that creates the second row on the editable table. Generating406the database statement424may also be carried out by generating an intermediate representation from which the updated editable table may be presented to a user while the changes to the editable table on the database in the cloud-based data warehouse are pending. The table manager126may intercept subsequent requests for the editable table and include the second row in the query results for the editable table by incorporating the edits from the intermediate representation.

Generating406the database statement424may also include various other intermediate forms, including an abstract syntax tree, a canonicalized hierarchy, a worksheet algebra, and a relational algebra. During each of these intermediate forms, the table manager126may optimize the database statement to efficiently retrieve the initial results from the database. The resulting database statement may be a structured query language statement (SQL).

The method ofFIG.4also includes presenting408, on the client computing system by the table manager126, the editable table comprising the second row, wherein the first row is not presented. Presenting408, on the client computing system, the editable table may be carried out by sending another database statement requesting the latest version of the editable table. The query results426of the database statement from the cloud-based data warehouse may be organized to arrange the rows of the data set to display the editable table in the GUI of the client computing system. If the second row is part of the latest version of the editable table, then the second row will be presented. The first row will be hidden and not presented as the first row includes an earlier version value than the second row.

The editable table on the cloud-base data warehouse192may include a sequence column. The sequence column may dictate the order that the rows appear. Different versions of the same row may include the same sequence number unless an edit is made to the row that moves the row to a different location in the editable table. Consequently, the second row will include the same sequence number as the first row unless the edit to the first row included a change to the location of the first row.

Table 1 depicts an example editable table as it may exist on the cloud-based data warehouse192. Using the terms above, assume the instruction420is to edit the row “Front Speakers—Monitor Audio—C150”. The edit changes column B to “HTD” and column C to “Level Three”. Once the edit is received, the table manager126determines the version value for the edit as “23”. The table manager126then generates the database statement424as an instruction to create a new row with the same primary key value and sequence value of the “Front Speakers—Monitor Audio—C150” row. The new row is to include the column values “Front Speakers—HTD—Level Three” for columns A, B, and C respectively. Table 2 depicts the same table as Table 1 but after the addition of the new row.

Finally, Table 3 depicts the editable table as presented on the GUI of the client computing system in response to requesting the latest version of the editable table. The version column, primary key column, and sequence column are hidden from the display.

The above limitations improve the operation of the computer system by creating a single editable table that includes multiple viewable versions of the same editable table. This is accomplished by creating a new row each time an edit is made. This is further accomplished by storing, invisible to the user, a version number in the row with a primary key indicating the same row across edits.

For further explanation,FIG.5sets forth a flow chart illustrating a further exemplary method for versioning in editable tables according to embodiments of the present invention that includes receiving402, by a table manager126via a client computing system, an instruction420to edit a first row in an editable table in a cloud-based data warehouse192, wherein the instruction420comprises a column value; determining404, by the table manager126, a version value for the edit to the first row; generating406, by the table manager126, a database statement424instructing the cloud-based data warehouse192to create, in the editable table, a second row including the version value and the column value; and presenting408, on the client computing system by the table manager126, the editable table comprising the second row, wherein the first row is not presented.

The method ofFIG.5differs from the method ofFIG.4, however, in thatFIG.5further includes receiving502, by the table manager126from the client computing system, a request520for a particular version of the editable table. Receiving502the request520for a particular version of the editable table may be carried out by detecting that a user has manipulated elements of the GUI and/or submitted data using the GUI such that the generation of the request520is triggered, and the request520is sent to the table manager126. The request520may be received in the form of a state specification from the GUI. The request520may include an identifier of the requested version.

The method ofFIG.5also includes generating504, by the table manager126, a subsequent database statement522to retrieve each row in the particular version of the editable table. Generating504the subsequent database statement522to retrieve each row in the particular version of the editable table may be carried out by generating the subsequent database statement522to select, for each primary key value, a row with a highest version value that does not exceed the requested particular version of the editable table. Specifically, the subsequent database statement522may be orchestrated to iterate through each row in the editable table and select one row for each primary key. The row for each primary key is selected by determining the row with the highest version number that does not exceed the requested particular version number. Each row that has a version number that is lower than the highest version number that does not exceed the requested particular version number is not included in the query results. Similarly, any row that has a version number higher than the requested particular version number is also not included in the query results.

If a row is deleted from the editable table, a new row with the same primary key as the deleted row may be created. The new row may be assigned the current version value that is higher than the deleted row. The new row may have a special indicator (e.g., a value in an additional column) that indicates that no row with that primary key should be selected for the particular version indicated by the version value in the new row.

The method ofFIG.5also includes presenting506, by the table manager126, query results524of the subsequent database statement522on the client computing system. Presenting506the query results524of the subsequent database statement522on the client computing system may be carried out by organizing the rows of the particular version of the editable table in the GUI of the client computing system. The rows may be organized according to a sequence value for each row.

For example, assume that the request520is for version 2.0 of the table depicted in Table 2. The table manager126generates a database statement to retrieve each row in version 2.0 of the table. The cloud-based data warehouse, upon receiving the database statement, iterates through the rows of the editable table and selects, for each primary key value, the row with the highest version value that does not exceed the requested version value. Table 4 depicts the example query results from the subsequent database statement522. Note that the version column, primary key column, and sequence column would be hidden from display on the GUI of the client computing system.

For further explanation,FIG.6sets forth a flow chart illustrating a further exemplary method for versioning in editable tables according to embodiments of the present invention that includes receiving402, by a table manager126via a client computing system, an instruction420to edit a first row in an editable table in a cloud-based data warehouse192, wherein the instruction420comprises a column value; determining404, by the table manager126, a version value for the edit to the first row; generating406, by the table manager126, a database statement424instructing the cloud-based data warehouse192to create, in the editable table, a second row including the version value and the column value; and presenting408, on the client computing system by the table manager126, the editable table comprising the second row, wherein the first row is not presented.

The method ofFIG.6differs from the method ofFIG.4, however, in that generating406, by the table manager126, a database statement424instructing the cloud-based data warehouse192to create, in the editable table, a second row including the version value and the column value includes adding602a version column to existing rows in the editable table by joining the editable table to a companion table comprising the version column. Adding602a version column to existing rows in the editable table by joining the editable table to a companion table may be carried out by creating the companion table on the cloud-based data warehouse. The companion table is database table to which edits are made and the versioning column is added to avoid directly editing the editable table on the cloud-based data warehouse192. The versioning row in the companion table may apply a version value to existing rows in the editable table. The database statement424may create the second row on the companion table within the database on the cloud-based data warehouse192. When retrieving the editable table from the cloud-based data warehouse192, the database statement combines the editable table with the companion table and presents the resulting combination on the GUI. When utilizing a companion table, the user need not have authorization to edit the editable table. The relationship between the companion table and editable table is based on the primary key values, such that a row from the original table is “version 1” and all rows in the companion are “version 2” or beyond.

In view of the explanations set forth above, readers will recognize that the benefits of versioning in editable tables according to embodiments of the present invention include:Improving the operation of the computer system by creating a single editable table that includes multiple viewable versions of the same editable table, increasing system utility and efficiency. This is accomplished by creating a new row each time an edit is made. This is further accomplished byImproving the operation of a computing system by creating a new row each time an edit is made, preserving overwritten data and increasing system stability.Improving the operation of a computing system by storing, invisible to the user, a version number in the row with a primary key indicating the same row across edits, increasing system robustness.