Amazon Technologies, Inc.

John Bair

Richard T Snodgrass

Charles M Bender

A method and apparatus accepts a query to update or delete rows in a temporal table of a database. The query may describe which rows are to be updated, a calculation for the value to be used to update each row of the table, and a period of validity, during which the changes are to take place. The method and apparatus creates a sequence of queries and SQL program elements that together can make up an SQL program that will execute the query received. The SQL program can split any row of the table to be updated into multiple rows so that each of the multiple rows of the table to be changed have an effective period during which no changes occur in the table to be changed or any other tables referenced in the query received and no effective periods span the beginning or the end of the period of validity. The specified change may then be made to the resulting rows, and rows that are adjacent in time with identical values may be collapsed into a single row. The result is inserted into the table to be changed and the corresponding original rows in the table to be changed are deleted. The method and apparatus operates for queries received to update or delete a table.

Method and apparatus for changing temporal database information

A method and apparatus converts an original query into a sequenced query that takes into account a range of values of a variable defined by a start and end point in performing the query. The start or end points are calculated if necessary and a query to collect all of the start and end points may be generated, and a query is generated that produces a constant set of start and end points defining consecutive periods, such that all the data in the tables related to the original query is constant over each of these periods. These two queries are merged into the original query to produce a sequenced query capable of execution on various database software and capable of taking into account the range of values of the variable in performing the original query.

Method and apparatus for producing sequenced queries

A system and method selects rows from a fact table in a dimensional database containing a fact table, a time dimension table and other dimension tables. The other dimension tables each contain rows containing a time invariant attribute to identify an item described by the row, an effective time attribute for the row, and other attributes. If an attribute for an item changes, a new row is added to the dimension table containing the time-invariant attribute for the item and current attributes for the item, without deleting or overwriting any existing rows for that item. Such dimension tables can be selected or used to create other tables using one or more time attributes of the dimension tables. The tables created can be selected or used to create still other tables using one or more time attributes of those tables. One or more of the resulting tables or one or more tables created from a resulting table are then joined to a table in the dimensional database or a table created from a table in the dimensional database.

Growing community of inventors

Superior, CO, United States of America

John Bair