def test_cycle(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, "$Interest", "", ""], [32, "Interest", "Numeric", True, "$Principal", "", ""], [33, "A2", "Numeric", True, "$A", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest', 'A2'], [1, 1, circle, circle, 1], [2, 2, circle, circle, 2], [3, 3, circle, circle, 3], ])
def test_sort_by(self): self.load_sample( testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [1, "num", "Numeric", False, "", "", ""], [ 2, "lookup", "Any", True, "Table1.lookupRecords(sort_by='num').num", "", "" ], ] ]], "DATA": { "Table1": [ ["id", "num"], [1, 2], [2, 1], [3, 'foo'], [4, 3], [5, None], [6, 0], ] } })) self.assertTableData("Table1", cols="subset", rows="subset", data=[ ["id", "lookup"], [1, [None, 0, 1, 2, 3, 'foo']], ])
def test_conversion(self): # Test that values are converted to the type of the column when looking up # i.e. '123' is converted to 123 # and 'foo' is converted to AltText('foo') self.load_sample( testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [1, "num", "Numeric", False, "", "", ""], [ 2, "lookup1", "RefList:Table1", True, "Table1.lookupRecords(num='123')", "", "" ], [ 3, "lookup2", "RefList:Table1", True, "Table1.lookupRecords(num='foo')", "", "" ], ] ]], "DATA": { "Table1": [ ["id", "num"], [1, 123], [2, 'foo'], ] } })) self.assertTableData("Table1", data=[ ["id", "num", "lookup1", "lookup2"], [1, 123, [1], [2]], [2, 'foo', [1], [2]], ])
def test_reference_column(self): # There was a bug where self-references could result in a column being prematurely # considered complete. sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [[40, "Ident", "Text", False, "", "", ""], [41, "Prev", "Ref:Table1", False, "", "", ""], [ 42, "Calc", "Numeric", True, "$Prev.Calc * 1.5 if $Prev else 1", "", "" ]] ]], "DATA": { "Table1": [ ['id', 'Ident', 'Prev'], [1, 'a', 0], [2, 'b', 1], [3, 'c', 4], [4, 'd', 0], ] } }) self.load_sample(sample) self.assertTableData('Table1', data=[['id', 'Ident', 'Prev', 'Calc'], [1, 'a', 0, 1.0], [2, 'b', 1, 1.5], [3, 'c', 4, 1.5], [4, 'd', 0, 1.0]])
def test_schema_restore_on_error(self): # Simulate an error inside a DocAction, and make sure we restore the schema (don't leave it in # inconsistent with metadata). self.load_sample(testutil.parse_test_sample(self.sample1)) with self.assertRaisesRegex(AttributeError, r"'BAD'"): self.add_column('Address', 'bad', isFormula=False, type="BAD") self.engine.assert_schema_consistent()
def test_recursive_column_dependencies(self): sample = testutil.parse_test_sample(self.sample_desc) self.load_sample(sample) self.apply_user_action(['Calculate']) # The Sum column contains a cumulative total of the Value column self.assertTableData("Table1", cols="subset", rows="subset", data=[ ["id", "Value", "Sum"], [1, 1, 1], [2, 2, 3], [3, 3, 6], [3200, 3200, 5121600], ]) # Updating the first Value causes a cascade of changes to Sum, # invalidating dependencies one cell at a time. # Previously this cause a recursion error. self.update_record("Table1", 1, Value=11) self.assertTableData("Table1", cols="subset", rows="subset", data=[ ["id", "Value", "Sum"], [1, 11, 11], [2, 2, 13], [3, 3, 16], [3200, 3200, 5121610], ])
def test_loop(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$C", "", ""], [32, "C", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'B', 'C'], [1, 1, circle, circle], [2, 2, circle, circle], [3, 3, circle, circle], ])
def test_contains(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Source", [ [ 11, "choicelist1", "ChoiceList", False, "", "choicelist1", "" ], [ 12, "choicelist2", "ChoiceList", False, "", "choicelist2", "" ], [13, "text1", "Text", False, "", "text1", ""], [14, "text2", "Text", False, "", "text1", ""], [ 15, "contains1", "RefList:Source", True, "Source.lookupRecords(choicelist1=CONTAINS($text1))", "contains1", "" ], [ 16, "contains2", "RefList:Source", True, "Source.lookupRecords(choicelist2=CONTAINS($text2))", "contains2", "" ], [ 17, "contains_both", "RefList:Source", True, "Source.lookupRecords(choicelist1=CONTAINS($text1), choicelist2=CONTAINS($text2))", "contains_both", "" ], [ 17, "combined", "RefList:Source", True, "Source.lookupRecords(choicelist1=CONTAINS($text1), text2='x')", "combined", "" ], ] ]], "DATA": { "Source": [ ["id", "choicelist1", "text1", "choicelist2", "text2"], [101, ["a"], "a", ["x"], "y"], [102, ["b"], "b", ["y"], "x"], [103, ["a", "b"], "c", ["x", "y"], "c"], ] } }) self.load_sample(sample) self.assertTableData( "Source", cols="subset", data=[ ["id", "contains1", "contains2", "contains_both", "combined"], [101, [101, 103], [102, 103], [103], []], [102, [102, 103], [101, 103], [103], [102]], [103, [], [], [], []], ])
def test_no_private_fields(self): self.load_sample(testutil.parse_test_sample(self.sample1)) data = self.engine.fetch_table("_grist_Tables", private=True) self.assertIn('tableId', data.columns) self.assertIn('columns', data.columns) self.assertIn('viewSections', data.columns) data = self.engine.fetch_table("_grist_Tables") self.assertIn('tableId', data.columns) self.assertNotIn('columns', data.columns) self.assertNotIn('viewSections', data.columns)
def test_cumulative_formula(self): formula = ( "Table1.lookupOne(A=$A-1).Principal + Table1.lookupOne(A=$A-1).Interest " + "if $A > 1 else 1000") sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, formula, "", ""], [ 32, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], [4, 4], [5, 5], ] } }) self.load_sample(sample) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest'], [1, 1, 1000.0, 100.0], [2, 2, 1100.0, 110.0], [3, 3, 1210.0, 121.0], [4, 4, 1331.0, 133.0], [5, 5, 1464.0, 146.0], ]) self.update_records('Table1', ['id', 'A'], [[1, 5], [2, 3], [3, 4], [4, 2], [5, 1]]) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest'], [1, 5, 1464.0, 146.0], [2, 3, 1210.0, 121.0], [3, 4, 1331.0, 133.0], [4, 2, 1100.0, 110.0], [5, 1, 1000.0, 100.0], ])
def test_cycle_and_reference(self): sample = testutil.parse_test_sample({ "SCHEMA": [ [ 2, "ATable", [ [32, "A", "Ref:ZTable", False, "", "", ""], [33, "B", "Numeric", True, "$A.B", "", ""], ] ], [ 1, "ZTable", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$B", "", ""], ] ], ], "DATA": { "ATable": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ], "ZTable": [ ["id", "A"], [1, 6], [2, 7], [3, 8], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('ATable', data=[ ['id', 'A', 'B'], [1, 1, circle], [2, 2, circle], [3, 3, circle], ]) self.assertTableData('ZTable', data=[ ['id', 'A', 'B'], [1, 6, circle], [2, 7, circle], [3, 8, circle], ])
def test_cumulative_formula_with_references(self): top = 100 formula = "max($Prev.Principal + $Prev.Interest, 1000)" sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [41, "Prev", "Ref:Table1", True, "$id - 1", "", ""], [42, "Principal", "Numeric", True, formula, "", ""], [ 43, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], ] ], [ 2, "Readout", [ [ 46, "LastPrincipal", "Numeric", True, "Table1.lookupOne(id=%d).Principal" % top, "", "" ], ] ]], "DATA": { "Table1": [["id"]] + [[r] for r in range(1, top + 1)], "Readout": [["id"], [1]], } }) self.load_sample(sample) self.assertTableData('Readout', data=[ ['id', 'LastPrincipal'], [1, 12494908.0], ]) self.modify_column("Table1", "Prev", formula="$id - 1 if $id > 1 else 100") self.assertTableData( 'Readout', data=[ ['id', 'LastPrincipal'], [1, objtypes.RaisedException(depend.CircularRefError())], ])
def test_undo_side_effects(self): # Ensures that side-effects (i.e. generated doc actions) produced while evaluating # get_formula_errors() get reverted. sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Address", [ [11, "city", "Text", False, "", "", ""], [12, "state", "Text", False, "", "", ""], ] ], [ 2, "Foo", [ # Note: the formula below is a terrible example of a formula, which intentionally # creates a new record every time it evaluates. [ 21, "B", "Any", True, "Address.lookupOrAddDerived(city=str(len(Address.all)))", "", "" ], ] ] ], "DATA": { "Foo": [["id"], [1]] } }) self.load_sample(sample) self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ]) # Note that evaluating the formula again would add a new record (Address[2]), but when done as # part of get_formula_error(), that action gets undone. self.assertEqual(str(self.engine.get_formula_error('Foo', 'B', 1)), "Address[2]") self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ])
def test_undo_side_effects_with_reordering(self): # As for test_undo_side_effects, but now after creating a row in a # formula we try to access a cell that hasn't been recomputed yet. # That will result in the formula evalution being abandoned, the # desired cell being calculated, then the formula being retried. # All going well, we should end up with one row, not two. sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Address", [ [11, "city", "Text", False, "", "", ""], [12, "state", "Text", False, "", "", ""], ] ], [ 2, "Foo", [ # Note: the formula below is a terrible example of a formula, which intentionally # creates a new record every time it evaluates. [ 21, "B", "Any", True, "Address.lookupOrAddDerived(city=str(len(Address.all)))\nreturn $C", "", "" ], [22, "C", "Numeric", True, "42", "", ""], ] ] ], "DATA": { "Foo": [["id"], [1]] } }) self.load_sample(sample) self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ])
def test_attribute_error(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "AttrTest", [ [30, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$AA", "", ""], [32, "C", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "AttrTest": [ ["id", "A"], [1, 1], [2, 2], ] } }) self.load_sample(sample) errVal = objtypes.RaisedException(AttributeError()) self.assertTableData('AttrTest', data=[ ['id', 'A', 'B', 'C'], [1, 1, errVal, errVal], [2, 2, errVal, errVal], ]) self.assertFormulaError( self.engine.get_formula_error('AttrTest', 'B', 1), AttributeError, "Table 'AttrTest' has no column 'AA'", r"AttributeError: Table 'AttrTest' has no column 'AA'") cell_error = self.engine.get_formula_error('AttrTest', 'C', 1) self.assertFormulaError( cell_error, objtypes.CellError, "AttributeError in referenced cell AttrTest[1].B", r"CellError: AttributeError in referenced cell AttrTest\[1\].B") self.assertEqual(objtypes.encode_object(cell_error), [ 'E', 'AttributeError', "Table 'AttrTest' has no column 'AA'\n" "(in referenced cell AttrTest[1].B)", cell_error.details ])
def test_fetch_table_query(self): self.load_sample(testutil.parse_test_sample(self.sample1)) col_names = ["id", "city", "state", "amount" ] data = self.engine.fetch_table('Address', query={'state': ['NY']}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [ [ 21, "New York", "NY" , 1 ], [ 22, "Albany", "NY" , 2 ], ])}) data = self.engine.fetch_table('Address', query={'city': ['New York'], 'state': ['NY']}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [ [ 21, "New York", "NY" , 1 ], ])}) data = self.engine.fetch_table('Address', query={'amount': [2.0]}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [ [ 22, "Albany", "NY" , 2 ], ])}) data = self.engine.fetch_table('Address', query={'city': ['New York'], 'amount': [2.0]}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [])}) data = self.engine.fetch_table('Address', query={'city': ['New York'], 'amount': [1.0, 2.0]}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [ [ 21, "New York", "NY" , 1 ], ])}) # Ensure empty filter list works too. data = self.engine.fetch_table('Address', query={'city': ['New York'], 'amount': []}) self.assertEqualDocData({'Address': data}, {'Address': testutil.table_data_from_rows('Address', col_names, [])})
def test_attribute_error(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "AttrTest", [ [30, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$AA", "", ""], [32, "C", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "AttrTest": [ ["id", "A"], [1, 1], [2, 2], ] } }) self.load_sample(sample) errVal = objtypes.RaisedException(AttributeError()) self.assertTableData('AttrTest', data=[ ['id', 'A', 'B', 'C'], [1, 1, errVal, errVal], [2, 2, errVal, errVal], ]) self.assertFormulaError( self.engine.get_formula_error('AttrTest', 'B', 1), AttributeError, "Table 'AttrTest' has no column 'AA'", r"AttributeError: Table 'AttrTest' has no column 'AA'") self.assertFormulaError( self.engine.get_formula_error('AttrTest', 'C', 1), AttributeError, "Table 'AttrTest' has no column 'AA'", r"AttributeError: Table 'AttrTest' has no column 'AA'")
class TestRenames(test_engine.EngineTestCase): # Simpler cases of column renames in formulas. Here's the list of cases we support and test. # $COLUMN where NAME is a column (formula or non-formula) # $ref.COLUMN when $ref is a non-formula Reference column # $ref.column.COLUMN # $ref.COLUMN when $ref is a function with a Ref type. # $ref.COLUMN when $ref is a function with Any type but clearly returning a Ref. # Table.lookupFunc(COLUMN1=value, COLUMN2=value) and for .lookupRecords # Table.lookupFunc(...).COLUMN and for .lookupRecords # Table.lookupFunc(...).foo.COLUMN and for .lookupRecords # [x.COLUMN for x in Table.lookupRecords(...)] for different kinds of comprehensions # TABLE.lookupFunc(...) where TABLE is a user-defined table. sample = testutil.parse_test_sample({ "SCHEMA": [ [1, "Address", [ [21, "city", "Text", False, "", "", ""], ]], [2, "People", [ [22, "name", "Text", False, "", "", ""], [23, "addr", "Ref:Address", False, "", "", ""], [24, "city", "Any", True, "$addr.city", "", ""], ]] ], "DATA": { "Address": [ ["id", "city" ], [11, "New York" ], [12, "Colombia" ], [13, "New Haven" ], [14, "West Haven" ], ], "People": [ ["id", "name" , "addr" ], [1, "Bob" , 12 ], [2, "Alice" , 13 ], [3, "Doug" , 12 ], [4, "Sam" , 11 ], ], } }) def test_rename_rec_attribute(self): # Simple case: we are renaming `$COLUMN`. self.load_sample(self.sample) out_actions = self.apply_user_action(["RenameColumn", "People", "addr", "address"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "addr", "address"], ["ModifyColumn", "People", "city", {"formula": "$address.city"}], ["BulkUpdateRecord", "_grist_Tables_column", [23, 24], { "colId": ["address", "city"], "formula": ["", "$address.city"] }], ], # Things should get recomputed, but produce same results, hence no calc actions. "calc": [] }) # Make sure renames of formula columns are also recognized. self.add_column("People", "CityUpper", formula="$city.upper()") out_actions = self.apply_user_action(["RenameColumn", "People", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "city", "ciudad"], ["ModifyColumn", "People", "CityUpper", {"formula": "$ciudad.upper()"}], ["BulkUpdateRecord", "_grist_Tables_column", [24, 25], { "colId": ["ciudad", "CityUpper"], "formula": ["$address.city", "$ciudad.upper()"] }] ]}) def test_rename_reference_attribute(self): # Slightly harder: renaming `$ref.COLUMN` self.load_sample(self.sample) out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24], { "colId": ["ciudad", "city"], "formula": ["", "$addr.ciudad"] }], ]}) def test_rename_ref_ref_attr(self): # Slightly harder still: renaming $ref.column.COLUMN. self.load_sample(self.sample) self.add_column("Address", "person", type="Ref:People") self.add_column("Address", "person_city", formula="$person.addr.city") self.add_column("Address", "person_city2", formula="a = $person.addr\nreturn a.city") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "Address", "person_city", {"formula": "$person.addr.ciudad"}], ["ModifyColumn", "Address", "person_city2", {"formula": "a = $person.addr\nreturn a.ciudad"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 26, 27], { "colId": ["ciudad", "city", "person_city", "person_city2"], "formula": ["", "$addr.ciudad", "$person.addr.ciudad", "a = $person.addr\nreturn a.ciudad"] }], ]}) def test_rename_typed_ref_func_attr(self): # Renaming `$ref.COLUMN` when $ref is a function with a Ref type. self.load_sample(self.sample) self.add_column("People", "addr_func", type="Ref:Address", isFormula=True, formula="$addr") self.add_column("People", "city2", formula="$addr_func.city") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "People", "city2", {"formula": "$addr_func.ciudad"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 26], { "colId": ["ciudad", "city", "city2"], "formula": ["", "$addr.ciudad", "$addr_func.ciudad"] }], ]}) def test_rename_any_ref_func_attr(self): # Renaming `$ref.COLUMN` when $ref is a function with Any type but clearly returning a Ref. self.load_sample(self.sample) self.add_column("People", "addr_func", isFormula=True, formula="$addr") self.add_column("People", "city3", formula="$addr_func.city") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "People", "city3", {"formula": "$addr_func.ciudad"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 26], { "colId": ["ciudad", "city", "city3"], "formula": ["", "$addr.ciudad", "$addr_func.ciudad"] }], ]}) def test_rename_reflist_attr(self): # Renaming `$ref.COLUMN` where $ref is a data or function with RefList type (most importantly # applies to the $group column of summary tables). self.load_sample(self.sample) self.add_column("People", "addr_list", type="RefList:Address", isFormula=False) self.add_column("People", "addr_func", type="RefList:Address", isFormula=True, formula="[1,2]") self.add_column("People", "citysum", formula="sum($addr_func.city) + sum($addr_list.city)") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "People", "citysum", {"formula": "sum($addr_func.ciudad) + sum($addr_list.ciudad)"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 27], { "colId": ["ciudad", "city", "citysum"], "formula": ["", "$addr.ciudad", "sum($addr_func.ciudad) + sum($addr_list.ciudad)"] }], ]}) def test_rename_lookup_param(self): # Renaming `Table.lookupOne(COLUMN1=value, COLUMN2=value)` and for `.lookupRecords` self.load_sample(self.sample) self.add_column("Address", "people", formula="People.lookupOne(addr=$id, city=$city)") self.add_column("Address", "people2", formula="People.lookupRecords(addr=$id)") out_actions = self.apply_user_action(["RenameColumn", "People", "addr", "ADDRESS"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "addr", "ADDRESS"], ["ModifyColumn", "People", "city", {"formula": "$ADDRESS.city"}], ["ModifyColumn", "Address", "people", {"formula": "People.lookupOne(ADDRESS=$id, city=$city)"}], ["ModifyColumn", "Address", "people2", {"formula": "People.lookupRecords(ADDRESS=$id)"}], ["BulkUpdateRecord", "_grist_Tables_column", [23, 24, 25, 26], { "colId": ["ADDRESS", "city", "people", "people2"], "formula": ["", "$ADDRESS.city", "People.lookupOne(ADDRESS=$id, city=$city)", "People.lookupRecords(ADDRESS=$id)"] }], ]}) # Another rename that should affect the second parameter. out_actions = self.apply_user_action(["RenameColumn", "People", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "city", "ciudad"], ["ModifyColumn", "Address", "people", {"formula": "People.lookupOne(ADDRESS=$id, ciudad=$city)"}], ["BulkUpdateRecord", "_grist_Tables_column", [24, 25], { "colId": ["ciudad", "people"], "formula": ["$ADDRESS.city", "People.lookupOne(ADDRESS=$id, ciudad=$city)"] }], ]}) # This is kind of unnecessary, but checks how the values of params are affected separately. out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "city2"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "city2"], ["ModifyColumn", "People", "ciudad", {"formula": "$ADDRESS.city2"}], ["ModifyColumn", "Address", "people", {"formula": "People.lookupOne(ADDRESS=$id, ciudad=$city2)"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 25], { "colId": ["city2", "ciudad", "people"], "formula": ["", "$ADDRESS.city2", "People.lookupOne(ADDRESS=$id, ciudad=$city2)"] }], ]}) def test_rename_lookup_result_attr(self): # Renaming `Table.lookupOne(...).COLUMN` and for `.lookupRecords` self.load_sample(self.sample) self.add_column("Address", "people", formula="People.lookupOne(addr=$id, city=$city).name") self.add_column("Address", "people2", formula="People.lookupRecords(addr=$id).name") out_actions = self.apply_user_action(["RenameColumn", "People", "name", "nombre"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "name", "nombre"], ["ModifyColumn", "Address", "people", {"formula": "People.lookupOne(addr=$id, city=$city).nombre"}], ["ModifyColumn", "Address", "people2", {"formula": "People.lookupRecords(addr=$id).nombre"}], ["BulkUpdateRecord", "_grist_Tables_column", [22, 25, 26], { "colId": ["nombre", "people", "people2"], "formula": ["", "People.lookupOne(addr=$id, city=$city).nombre", "People.lookupRecords(addr=$id).nombre"] }], ]}) def test_rename_lookup_ref_attr(self): # Renaming `Table.lookupOne(...).foo.COLUMN` and for `.lookupRecords` self.load_sample(self.sample) self.add_column("Address", "people", formula="People.lookupOne(addr=$id, city=$city).addr.city") self.add_column("Address", "people2", formula="People.lookupRecords(addr=$id).addr.city") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "Address", "people", {"formula": "People.lookupOne(addr=$id, city=$ciudad).addr.ciudad"}], ["ModifyColumn", "Address", "people2", {"formula": "People.lookupRecords(addr=$id).addr.ciudad"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 25, 26], { "colId": ["ciudad", "city", "people", "people2"], "formula": ["", "$addr.ciudad", "People.lookupOne(addr=$id, city=$ciudad).addr.ciudad", "People.lookupRecords(addr=$id).addr.ciudad"] }] ]}) def test_rename_lookup_iter_attr(self): # Renaming `[x.COLUMN for x in Table.lookupRecords(...)]`. self.load_sample(self.sample) self.add_column("Address", "people", formula="','.join(x.addr.city for x in People.lookupRecords(addr=$id))") self.add_column("Address", "people2", formula="','.join([x.addr.city for x in People.lookupRecords(addr=$id)])") self.add_column("Address", "people3", formula="','.join({x.addr.city for x in People.lookupRecords(addr=$id)})") self.add_column("Address", "people4", formula="{x.addr.city:x.addr for x in People.lookupRecords(addr=$id)}") out_actions = self.apply_user_action(["RenameColumn", "People", "addr", "ADDRESS"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "addr", "ADDRESS"], ["ModifyColumn", "People", "city", {"formula": "$ADDRESS.city"}], ["ModifyColumn", "Address", "people", {"formula": "','.join(x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id))"}], ["ModifyColumn", "Address", "people2", {"formula": "','.join([x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id)])"}], ["ModifyColumn", "Address", "people3", {"formula": "','.join({x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id)})"}], ["ModifyColumn", "Address", "people4", {"formula": "{x.ADDRESS.city:x.ADDRESS for x in People.lookupRecords(ADDRESS=$id)}"}], ["BulkUpdateRecord", "_grist_Tables_column", [23, 24, 25, 26, 27, 28], { "colId": ["ADDRESS", "city", "people", "people2", "people3", "people4"], "formula": ["", "$ADDRESS.city", "','.join(x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id))", "','.join([x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id)])", "','.join({x.ADDRESS.city for x in People.lookupRecords(ADDRESS=$id)})", "{x.ADDRESS.city:x.ADDRESS for x in People.lookupRecords(ADDRESS=$id)}"], }], ]}) def test_rename_table(self): # Renaming TABLE.lookupFunc(...) where TABLE is a user-defined table. self.load_sample(self.sample) self.add_column("Address", "people", formula="People.lookupRecords(addr=$id)") self.add_column("Address", "people2", type="Ref:People", formula="People.lookupOne(addr=$id)") out_actions = self.apply_user_action(["RenameTable", "People", "Persons"]) self.assertPartialOutActions(out_actions, { "stored": [ ["ModifyColumn", "Address", "people2", {"type": "Int"}], ["RenameTable", "People", "Persons"], ["UpdateRecord", "_grist_Tables", 2, {"tableId": "Persons"}], ["ModifyColumn", "Address", "people2", { "type": "Ref:Persons", "formula": "Persons.lookupOne(addr=$id)" }], ["ModifyColumn", "Address", "people", {"formula": "Persons.lookupRecords(addr=$id)"}], ["BulkUpdateRecord", "_grist_Tables_column", [26, 25], { "type": ["Ref:Persons", "Any"], "formula": ["Persons.lookupOne(addr=$id)", "Persons.lookupRecords(addr=$id)"] }], ["BulkUpdateRecord", "Address", [11, 12, 13, 14], { "people": [["r", "Persons", [4]], ["r", "Persons", [1, 3]], ["r", "Persons", [2]], ["r", "Persons", []]] }], ]}) def test_rename_table_autocomplete(self): user = { 'Name': 'Foo', 'UserID': 1, 'LinkKey': {}, 'Origin': None, 'Email': '*****@*****.**', 'Access': 'owners' } # Renaming a table should not leave the old name available for auto-complete. self.load_sample(self.sample) names = {"People", "Persons"} self.assertEqual( names.intersection(self.engine.autocomplete("Pe", "Address", "city", user)), {"People"} ) # Rename the table and ensure that "People" is no longer present among top-level names. out_actions = self.apply_user_action(["RenameTable", "People", "Persons"]) self.assertEqual( names.intersection(self.engine.autocomplete("Pe", "Address", "city", user)), {"Persons"} ) def test_rename_to_id(self): # Check that we renaming a column to "Id" disambiguates it with a suffix. self.load_sample(self.sample) out_actions = self.apply_user_action(["RenameColumn", "People", "name", "Id"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "name", "Id2"], ["UpdateRecord", "_grist_Tables_column", 22, {"colId": "Id2"}], ]}) def test_renames_with_non_ascii(self): # Test that presence of unicode does not interfere with formula adjustments for renaming. self.load_sample(self.sample) self.add_column("Address", "CityUpper", formula=u"'Øî'+$city.upper()+'áü'") out_actions = self.apply_user_action(["RenameColumn", "Address", "city", "ciudad"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "Address", "city", "ciudad"], ["ModifyColumn", "People", "city", {"formula": "$addr.ciudad"}], ["ModifyColumn", "Address", "CityUpper", {"formula": u"'Øî'+$ciudad.upper()+'áü'"}], ["BulkUpdateRecord", "_grist_Tables_column", [21, 24, 25], { "colId": ["ciudad", "city", "CityUpper"], "formula": ["", "$addr.ciudad", u"'Øî'+$ciudad.upper()+'áü'"], }] ]}) self.assertTableData("Address", cols="all", data=[ ["id", "ciudad", "CityUpper"], [11, "New York", u"ØîNEW YORKáü"], [12, "Colombia", u"ØîCOLOMBIAáü"], [13, "New Haven", u"ØîNEW HAVENáü"], [14, "West Haven", u"ØîWEST HAVENáü"], ]) def test_rename_updates_properties(self): # This tests for the following bug: a column A of type Any with formula Table1.lookupOne(B=$B) # will return a correct reference; when column Table1.X is renamed to Y, $A.X will be changed # to $A.Y correctly. The bug was that the fixed $A.Y formula would fail incorrectly with # "Table1 has no column 'Y'". # # The cause was that Record objects created by $A were not affected by the # rename, or recomputed after it, and contained a stale list of allowed column names (the fix # removes reliance on storing column names in the Record class). self.load_sample(self.sample) self.add_column("Address", "person", formula="People.lookupOne(addr=$id)") self.add_column("Address", "name", formula="$person.name") from datetime import date # A helper for comparing Record objects below. people_table = self.engine.tables['People'] people_rec = lambda row_id: people_table.Record(row_id, None) # Verify the data and calculations are correct. self.assertTableData("Address", cols="all", data=[ ["id", "city", "person", "name"], [11, "New York", people_rec(4), "Sam"], [12, "Colombia", people_rec(1), "Bob"], [13, "New Haven", people_rec(2), "Alice"], [14, "West Haven", people_rec(0), ""], ]) # Do the rename. out_actions = self.apply_user_action(["RenameColumn", "People", "name", "name2"]) self.assertPartialOutActions(out_actions, { "stored": [ ["RenameColumn", "People", "name", "name2"], ["ModifyColumn", "Address", "name", {"formula": "$person.name2"}], ["BulkUpdateRecord", "_grist_Tables_column", [22, 26], { "colId": ["name2", "name"], "formula": ["", "$person.name2"], }] ]}) # Verify the data and calculations are correct after the rename. self.assertTableData("Address", cols="all", data=[ ["id", "city", "person", "name"], [11, "New York", people_rec(4), "Sam"], [12, "Colombia", people_rec(1), "Bob"], [13, "New Haven", people_rec(2), "Alice"], [14, "West Haven", people_rec(0), ""], ])
class TestDefaultFormulas(test_engine.EngineTestCase): sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Customers", [ [1, "Name", "Text", False, "", "", ""], [2, "Region", "Ref:Regions", False, "", "", ""], [3, "RegName", "Text", True, "$Region.Region", "", ""], [4, "SalesRep", "Text", False, "$Region.Rep", "", ""], [5, "CID", "Int", False, "$id + 1000", "", ""], ] ], [ 2, "Regions", [[11, "Region", "Text", False, "", "", ""], [12, "Rep", "Text", False, "", "", ""]] ], ], "DATA": { "Customers": [ ["id", "Name", "Region", "SalesRep", "CID"], [1, "Dolphin", 2, "Neptune", 0], ], "Regions": [ ["id", "Region", "Rep"], [1, "Pacific", "Watatsumi"], [2, "Atlantic", "Poseidon"], [3, "Indian", "Neptune"], [4, "Arctic", "Poseidon"], ], } }) def test_default_formula_plain(self): self.load_sample(self.sample) # The defaults don't affect data that's loaded self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], ]) # Defaults affect new records self.add_record("Customers", Name="Shark", Region=2) self.add_record("Customers", Name="Squid", Region=1) self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], [2, "Shark", 2, "Atlantic", "Poseidon", 1002], # New record [3, "Squid", 1, "Pacific", "Watatsumi", 1003], # New record ]) # Changed defaults don't affect previously-added records self.modify_column('Customers', 'CID', formula='$id + 2000') self.add_record("Customers", Name="Hammerhead", Region=3) self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], [2, "Shark", 2, "Atlantic", "Poseidon", 1002], [3, "Squid", 1, "Pacific", "Watatsumi", 1003], [4, "Hammerhead", 3, "Indian", "Neptune", 2004], # New record ]) # Defaults don't affect changes to existing records self.update_record("Customers", 2, Region=3) self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], [2, "Shark", 3, "Indian", "Poseidon", 1002], # Region changed [3, "Squid", 1, "Pacific", "Watatsumi", 1003], [4, "Hammerhead", 3, "Indian", "Neptune", 2004], ]) def test_default_formula_with_lookups(self): self.load_sample(self.sample) self.modify_column('Customers', 'RegName', isFormula=False, formula="") self.modify_column('Customers', 'Region', isFormula=False, formula="Regions.lookupOne(Region=$RegName)") self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], ]) # Lookup-based defaults work. self.add_record("Customers", Name="Shark", RegName="Atlantic") self.add_record("Customers", Name="Squid", RegName="Pacific") self.assertTableData( "Customers", data=[ ["id", "Name", "Region", "RegName", "SalesRep", "CID"], [1, "Dolphin", 2, "Atlantic", "Neptune", 0], [2, "Shark", 2, "Atlantic", "Poseidon", 1002], # New record [3, "Squid", 1, "Pacific", "Watatsumi", 1003], # New record ]) def test_time_defaults(self): self.load_sample(self.sample) self.add_column('Customers', 'AddTime', type="DateTime:America/Los_Angeles", isFormula=False, formula="NOW()") self.add_column('Customers', 'AddDate', type="Date", isFormula=False, formula="TODAY()") self.assertTableData( "Customers", data=[ [ "id", "Name", "Region", "RegName", "SalesRep", "CID", "AddTime", "AddDate" ], [1, "Dolphin", 2, "Atlantic", "Neptune", 0, None, None], ]) self.add_record("Customers", Name="Shark", Region=2) self.add_record("Customers", Name="Squid", Region=1) now = time.time() midnight = now - (now % (24 * 60 * 60)) # Check columns except AddTime, which we check separately below. self.assertTableData( "Customers", cols="subset", data=[ [ "id", "Name", "Region", "RegName", "SalesRep", "CID", "AddDate" ], [1, "Dolphin", 2, "Atlantic", "Neptune", 0, None], [2, "Shark", 2, "Atlantic", "Poseidon", 1002, midnight], # New record [3, "Squid", 1, "Pacific", "Watatsumi", 1003, midnight], # New record ]) # AddTime column is hard to be precise about, check it separately. Note that the timestamp # does not depend on timezone, and should not change based on the timezone in the column type. observed_data = self.engine.fetch_table('Customers') self.assertEqual(observed_data.columns['AddTime'][0], None) self.assertLessEqual(abs(observed_data.columns['AddTime'][1] - now), 2) self.assertLessEqual(abs(observed_data.columns['AddTime'][2] - now), 2)
def test_catch_all_in_formula(self): sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Table1", [ [51, "A", "Numeric", False, "", "", ""], [ 52, "B1", "Numeric", True, "try:\n return $A+$C\nexcept:\n return 42", "", "" ], [ 53, "B2", "Numeric", True, "try:\n return $D+None\nexcept:\n return 42", "", "" ], [ 54, "B3", "Numeric", True, "try:\n return $A+$B4+$D\nexcept:\n return 42", "", "" ], [ 55, "B4", "Numeric", True, "try:\n return $A+$B3+$D\nexcept:\n return 42", "", "" ], [ 56, "B5", "Numeric", True, "try:\n return $E+1\nexcept:\n raise Exception('monkeys!')", "", "" ], [ 56, "B6", "Numeric", True, "try:\n return $F+1\nexcept Exception as e:\n e.node = e.row_id = 'monkey'", "", "" ], [57, "C", "Numeric", False, "", "", ""], [58, "D", "Numeric", True, "$A", "", ""], [59, "E", "Numeric", True, "$A", "", ""], [59, "F", "Numeric", True, "$A", "", ""], ] ], ], "DATA": { "Table1": [["id", "A", "C"], [1, 1, 2], [2, 20, 10]], } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) # B4 is a subtle case. B3 and B4 refer to each other. B3 is recomputed first, # and cells evaluate to a CircularRefError. Now B3 has a value, so B4 can be # evaluated, and results in 42 when addition of an integer and an exception value # fails. self.assertTableData( 'Table1', data=[ [ 'id', 'A', 'B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'C', 'D', 'E', 'F' ], [1, 1, 3, 42, circle, 42, 2, 2, 2, 1, 1, 1], [2, 20, 30, 42, circle, 42, 21, 21, 10, 20, 20, 20], ])
class TestTypes(test_engine.EngineTestCase): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Types", [[21, "text", "Text", False, "", "", ""], [22, "numeric", "Numeric", False, "", "", ""], [23, "int", "Int", False, "", "", ""], [24, "bool", "Bool", False, "", "", ""], [25, "date", "Date", False, "", "", ""]] ], [ 2, "Formulas", [[ 30, "division", "Any", True, "Types.lookupOne(id=18).numeric / 2", "", "" ]] ]], "DATA": { "Types": [["id", "text", "numeric", "int", "bool", "date"], [11, "New York", "New York", "New York", "New York", "New York"], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, False, False, False, False, False], [14, True, True, True, True, True], [15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0, 0, 0, 0, 0], [18, 1, 1, 1, 1, 1], [19, "", "", "", "", ""], [20, None, None, None, None, None]], "Formulas": [["id"], [1]] }, }) all_row_ids = [11, 12, 13, 14, 15, 16, 17, 18, 19, 20] def test_update_typed_cells(self): """ Tests that updated typed values are set as expected in the sandbox. Types should follow the rules: - After updating a cell with a value of a type compatible to the column type, the cell value should have the column's standard type - Otherwise, the cell value should have the type AltText """ self.load_sample(self.sample) out_actions = self.apply_user_action([ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "numeric": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "int": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "bool": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "date": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"2019-01-22 00:47:39", "New York" ] } ]) self.assertPartialOutActions( out_actions, { "stored": [ [ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ None, "", "1", "0", "8.153", "1509556595", "True", "False", "Chîcágö", "New York" ], "numeric": [ None, None, 1.0, 0.0, 8.153, 1509556595.0, 1.0, 0.0, "Chîcágö", "New York" ], "int": [ None, None, 1, 0, 8, 1509556595, 1, 0, "Chîcágö", "New York" ], "bool": [ False, False, True, False, True, True, True, False, "Chîcágö", "New York" ], "date": [ None, None, 1.0, 0.0, 8.153, 1509556595.0, 1.0, 0.0, 1548115200.0, "New York" ] } ], ["UpdateRecord", "Formulas", 1, { "division": 0.0 }], ], "undo": [ [ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ "New York", "Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "numeric": [ "New York", "Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "int": [ "New York", "Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "bool": [ "New York", "Chîcágö", False, True, 1509556595, 8.153, False, True, "", None ], "date": [ "New York", "Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ] } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) self.assertTableData( "Types", data=[ ["id", "text", "numeric", "int", "bool", "date"], [11, None, None, None, False, None], [12, "", None, None, False, None], [13, "1", 1.0, 1, True, 1.0], [14, "0", 0.0, 0, False, 0.0], [15, "8.153", 8.153, 8, True, 8.153], [16, "1509556595", 1509556595, 1509556595, True, 1509556595.0], [17, "True", 1.0, 1, True, 1.0], [18, "False", 0.0, 0, False, 0.0], [19, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", 1548115200.0], [ 20, "New York", "New York", "New York", "New York", "New York" ] ]) def test_text_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be Text - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Text" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Numeric -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "numeric": [ "False", "True", "1509556595.0", "8.153", "0.0", "1.0" ] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Text" } ], [ "UpdateRecord", "Formulas", 1, { "division": ["E", "TypeError"] } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "numeric": [False, True, 1509556595, 8.153, 0, 1] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "int": ["False", "True", "1509556595", "8.153", "0", "1"] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "int": [False, True, 1509556595, 8.153, 0, 1] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Text out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "bool": [ "False", "True", "1509556595", "8.153", "False", "True" ] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "bool": [False, True, 1509556595, 8.153, False, True] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Text out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "date": [ "False", "True", "1509556595.0", "8.153", "0.0", "1.0" ] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Text" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "date": [False, True, 1509556595.0, 8.153, 0.0, 1.0] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, False, "False", "False", "False", "False"], [14, True, "True", "True", "True", "True"], [ 15, 1509556595, "1509556595.0", "1509556595", "1509556595", "1509556595.0" ], [16, 8.153, "8.153", "8.153", "8.153", "8.153"], [17, 0, "0.0", "0", "False", "0.0"], [18, 1, "1.0", "1", "True", "1.0"], [19, "", "", "", "", ""], [20, None, None, None, None, None]]) def test_numeric_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Numeric or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [False, True, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Int -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [False, True, ""] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [0.0, 1.0, 0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [False, True, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "date": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Numeric" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 19], { "date": [False, True, ""] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[ ["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, 0.0, False, 0.0, 0.0, 0.0], [14, 1.0, True, 1.0, 1.0, 1.0], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0.0, 0.0, 0.0, 0.0, 0.0], [18, 1.0, 1.0, 1.0, 1.0, 1.0], [19, None, "", None, None, None], [20, None, None, None, None, None], ]) def test_int_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Int or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "text": [0, 1, 8, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Int" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "text": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "numeric": [0, 1, 8, None] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Int" } ], ["UpdateRecord", "Formulas", 1, { "division": 0 }], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "numeric": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Int" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Bool -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 17, 18, 19], { "bool": [0, 1, 8, 0, 1, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Int" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 17, 18, 19], { "bool": [False, True, 8.153, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "date": [0, 1, 8, None] } ], ["UpdateRecord", "_grist_Tables_column", 25, { "type": "Int" }]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "date": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, 0, 0, False, 0, 0], [14, 1, 1, True, 1, 1], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8, 8, 8.153, 8, 8], [17, 0, 0, 0, 0, 0], [18, 1, 1, 1, 1, 1], [19, None, None, "", None, None], [20, None, None, None, None, None]]) def test_bool_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Bool or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "text": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Bool" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "text": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "numeric": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Bool" } ], ["UpdateRecord", "Formulas", 1, { "division": 0 }], ], "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "numeric": [1509556595.0, 8.153, 0.0, 1.0, "", None] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "int": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Bool" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "int": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Bool" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Date -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "date": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Bool" } ]], "undo": [[ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "date": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, False, False, False, False, False], [14, True, True, True, True, True], [15, True, True, True, 1509556595, True], [16, True, True, True, 8.153, True], [17, False, False, False, 0, False], [18, True, True, True, 1, True], [19, False, False, False, "", False], [20, False, False, False, None, False]]) def test_date_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Date or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Date" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [False, True, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "numeric": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Date" } ], [ "UpdateRecord", "Formulas", 1, { "division": ["E", "TypeError"] } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "numeric": [False, True, ""] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Date" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [False, True, ""] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "bool", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [0.0, 1.0, 0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Date" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [False, True, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ]] }) # Test Date -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Date" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [12, "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö", "Chîcágö"], [13, 0.0, 0.0, 0.0, 0.0, False], [14, 1.0, 1.0, 1.0, 1.0, True], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0.0, 0.0, 0.0, 0.0, 0], [18, 1.0, 1.0, 1.0, 1.0, 1], [19, None, None, None, None, ""], [20, None, None, None, None, None]]) def test_numerics_are_floats(self): """ Tests that in formulas, numeric values are floats, not integers. Important to avoid truncation. """ self.load_sample(self.sample) self.assertTableData('Formulas', data=[ ['id', 'division'], [1, 0.5], ])
def test_cumulative_efficiency(self): # Make sure cumulative formula evaluation doesn't fall over after more than a few rows. top = 250 # Compute compound interest in ascending order of A formula = ( "Table1.lookupOne(A=$A-1).Principal + Table1.lookupOne(A=$A-1).Interest " + "if $A > 1 else 1000") # Compute compound interest in descending order of A rformula = ( "Table1.lookupOne(A=$A+1).RPrincipal + Table1.lookupOne(A=$A+1).RInterest " + "if $A < %d else 1000" % top) rows = [["id", "A"]] for i in range(1, top + 1): rows.append([i, i]) sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, formula, "", ""], [ 32, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], [33, "RPrincipal", "Numeric", True, rformula, "", ""], [ 34, "RInterest", "Numeric", True, "int($RPrincipal * 0.1)", "", "" ], [ 35, "Total", "Numeric", True, "$Principal + $RPrincipal", "", "" ], ] ], [ 2, "Readout", [ [ 36, "LastPrincipal", "Numeric", True, "Table1.lookupOne(A=%d).Principal" % top, "", "" ], [ 37, "LastRPrincipal", "Numeric", True, "Table1.lookupOne(A=1).RPrincipal", "", "" ], [ 38, "FirstTotal", "Numeric", True, "Table1.lookupOne(A=1).Total", "", "" ], [ 39, "LastTotal", "Numeric", True, "Table1.lookupOne(A=%d).Total" % top, "", "" ], ] ]], "DATA": { "Table1": rows, "Readout": [["id"], [1]], } }) self.load_sample(sample) principal = 20213227788876.0 self.assertTableData( 'Readout', data=[ [ 'id', 'LastPrincipal', 'LastRPrincipal', 'FirstTotal', 'LastTotal' ], [1, principal, principal, principal + 1000, principal + 1000], ])
class TestSummaryChoiceList(EngineTestCase): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Source", [ [10, "other", "Text", False, "", "other", ""], [11, "choices1", "ChoiceList", False, "", "choices1", ""], [12, "choices2", "ChoiceList", False, "", "choices2", ""], ] ]], "DATA": { "Source": [ ["id", "choices1", "choices2", "other"], [21, ["a", "b"], ["c", "d"], "foo"], ] } }) starting_table = Table(1, "Source", primaryViewId=0, summarySourceTable=0, columns=[ Column(10, "other", "Text", isFormula=False, formula="", summarySourceCol=0), Column(11, "choices1", "ChoiceList", isFormula=False, formula="", summarySourceCol=0), Column(12, "choices2", "ChoiceList", isFormula=False, formula="", summarySourceCol=0), ]) # ---------------------------------------------------------------------- def test_summary_by_choice_list(self): self.load_sample(self.sample) # Verify the starting table; there should be no views yet. self.assertTables([self.starting_table]) self.assertViews([]) # Create a summary section, grouped by the "choices1" column. self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]]) summary_table1 = Table( 2, "GristSummary_6_Source", primaryViewId=0, summarySourceTable=1, columns=[ Column(13, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(14, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(15, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) # Create another summary section, grouped by both choicelist columns. self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]]) summary_table2 = Table( 3, "GristSummary_6_Source2", primaryViewId=0, summarySourceTable=1, columns=[ Column(16, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(17, "choices2", "Choice", isFormula=False, formula="", summarySourceCol=12), Column(18, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(19, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) # Create another summary section, grouped by the non-choicelist column self.apply_user_action(["CreateViewSection", 1, 0, "record", [10]]) summary_table3 = Table( 4, "GristSummary_6_Source3", primaryViewId=0, summarySourceTable=1, columns=[ Column(20, "other", "Text", isFormula=False, formula="", summarySourceCol=10), Column(21, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(22, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) # Create another summary section, grouped by the non-choicelist column and choices1 self.apply_user_action(["CreateViewSection", 1, 0, "record", [10, 11]]) summary_table4 = Table( 5, "GristSummary_6_Source4", primaryViewId=0, summarySourceTable=1, columns=[ Column(23, "other", "Text", isFormula=False, formula="", summarySourceCol=10), Column(24, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(25, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(26, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) self.assertTables([ self.starting_table, summary_table1, summary_table2, summary_table3, summary_table4 ]) # Verify the summarized data. self.assertTableData('GristSummary_6_Source', data=[ ["id", "choices1", "group", "count"], [1, "a", [21], 1], [2, "b", [21], 1], ]) self.assertTableData( 'GristSummary_6_Source2', data=[ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [21], 1], [2, "a", "d", [21], 1], [3, "b", "c", [21], 1], [4, "b", "d", [21], 1], ]) self.assertTableData('GristSummary_6_Source3', data=[ ["id", "other", "group", "count"], [1, "foo", [21], 1], ]) self.assertTableData('GristSummary_6_Source4', data=[ ["id", "other", "choices1", "group", "count"], [1, "foo", "a", [21], 1], [2, "foo", "b", [21], 1], ]) # Verify the optimisation works for the table without choicelists self.assertIs(self.engine.tables["Source"]._summary_simple, None) self.assertIs( self.engine.tables["GristSummary_6_Source"]._summary_simple, False) self.assertIs( self.engine.tables["GristSummary_6_Source2"]._summary_simple, False) # simple summary and lookup self.assertIs( self.engine.tables["GristSummary_6_Source3"]._summary_simple, True) self.assertIs( self.engine.tables["GristSummary_6_Source4"]._summary_simple, False) self.assertEqual( { k: type(v) for k, v in self.engine.tables["Source"]._special_cols.items() }, { '#summary#GristSummary_6_Source': column.ReferenceListColumn, "#lookup#_Contains(value='#summary#GristSummary_6_Source')": lookup.ContainsLookupMapColumn, '#summary#GristSummary_6_Source2': column.ReferenceListColumn, "#lookup#_Contains(value='#summary#GristSummary_6_Source2')": lookup.ContainsLookupMapColumn, # simple summary and lookup '#summary#GristSummary_6_Source3': column.ReferenceColumn, '#lookup##summary#GristSummary_6_Source3': lookup.SimpleLookupMapColumn, '#summary#GristSummary_6_Source4': column.ReferenceListColumn, "#lookup#_Contains(value='#summary#GristSummary_6_Source4')": lookup.ContainsLookupMapColumn, }) # Remove 'b' from choices1 self.update_record("Source", 21, choices1=["L", "a"]) self.assertTableData('Source', data=[ ["id", "choices1", "choices2", "other"], [21, ["a"], ["c", "d"], "foo"], ]) # Verify that the summary table rows containing 'b' are empty self.assertTableData('GristSummary_6_Source', data=[ ["id", "choices1", "group", "count"], [1, "a", [21], 1], [2, "b", [], 0], ]) self.assertTableData( 'GristSummary_6_Source2', data=[ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [21], 1], [2, "a", "d", [21], 1], [3, "b", "c", [], 0], [4, "b", "d", [], 0], ]) # Add 'e' to choices2 self.update_record("Source", 21, choices2=["L", "c", "d", "e"]) # First summary table unaffected self.assertTableData('GristSummary_6_Source', data=[ ["id", "choices1", "group", "count"], [1, "a", [21], 1], [2, "b", [], 0], ]) # New row added for 'e' self.assertTableData( 'GristSummary_6_Source2', data=[ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [21], 1], [2, "a", "d", [21], 1], [3, "b", "c", [], 0], [4, "b", "d", [], 0], [5, "a", "e", [21], 1], ]) # Remove record from source self.remove_record("Source", 21) # All summary rows are now empty self.assertTableData('GristSummary_6_Source', data=[ ["id", "choices1", "group", "count"], [1, "a", [], 0], [2, "b", [], 0], ]) self.assertTableData( 'GristSummary_6_Source2', data=[ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [], 0], [2, "a", "d", [], 0], [3, "b", "c", [], 0], [4, "b", "d", [], 0], [5, "a", "e", [], 0], ]) # Make rows with every combination of {a,b,ab} and {c,d,cd} self.add_records('Source', ["id", "choices1", "choices2"], [ [101, ["L", "a"], ["L", "c"]], [102, ["L", "b"], ["L", "c"]], [103, ["L", "a", "b"], ["L", "c"]], [104, ["L", "a"], ["L", "d"]], [105, ["L", "b"], ["L", "d"]], [106, ["L", "a", "b"], ["L", "d"]], [107, ["L", "a"], ["L", "c", "d"]], [108, ["L", "b"], ["L", "c", "d"]], [109, ["L", "a", "b"], ["L", "c", "d"]], ]) self.assertTableData('Source', cols="subset", data=[ ["id", "choices1", "choices2"], [101, ["a"], ["c"]], [102, ["b"], ["c"]], [103, ["a", "b"], ["c"]], [104, ["a"], ["d"]], [105, ["b"], ["d"]], [106, ["a", "b"], ["d"]], [107, ["a"], ["c", "d"]], [108, ["b"], ["c", "d"]], [109, ["a", "b"], ["c", "d"]], ]) # Summary tables now have an even distribution of combinations self.assertTableData('GristSummary_6_Source', data=[ ["id", "choices1", "group", "count"], [1, "a", [101, 103, 104, 106, 107, 109], 6], [2, "b", [102, 103, 105, 106, 108, 109], 6], ]) summary_data = [ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [101, 103, 107, 109], 4], [2, "a", "d", [104, 106, 107, 109], 4], [3, "b", "c", [102, 103, 108, 109], 4], [4, "b", "d", [105, 106, 108, 109], 4], [5, "a", "e", [], 0], ] self.assertTableData('GristSummary_6_Source2', data=summary_data) # Verify that "DetachSummaryViewSection" useraction works correctly. self.apply_user_action(["DetachSummaryViewSection", 2]) self.assertTables([ self.starting_table, summary_table1, summary_table3, summary_table4, Table( 6, "Table1", primaryViewId=5, summarySourceTable=0, columns=[ Column(27, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0), Column(28, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=0), Column(29, "choices2", "Choice", isFormula=False, formula="", summarySourceCol=0), Column(30, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), Column( 31, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula= "Source.lookupRecords(choices1=CONTAINS($choices1), choices2=CONTAINS($choices2))" ), ], ) ]) self.assertTableData('Table1', data=summary_data, cols="subset") def test_change_choice_to_choicelist(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Source", [ [10, "other", "Text", False, "", "other", ""], [11, "choices1", "Choice", False, "", "choice", ""], ] ]], "DATA": { "Source": [ ["id", "choices1", "other"], [21, "a", "foo"], [22, "b", "bar"], ] } }) starting_table = Table(1, "Source", primaryViewId=0, summarySourceTable=0, columns=[ Column(10, "other", "Text", isFormula=False, formula="", summarySourceCol=0), Column(11, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=0), ]) self.load_sample(sample) # Verify the starting table; there should be no views yet. self.assertTables([starting_table]) self.assertViews([]) # Create a summary section, grouped by the "choices1" column. self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]]) summary_table = Table( 2, "GristSummary_6_Source", primaryViewId=0, summarySourceTable=1, columns=[ Column(12, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(13, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(14, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) data = [ ["id", "choices1", "group", "count"], [1, "a", [21], 1], [2, "b", [22], 1], ] self.assertTables([starting_table, summary_table]) self.assertTableData('GristSummary_6_Source', data=data) # Change the column from Choice to ChoiceList self.apply_user_action([ "UpdateRecord", "_grist_Tables_column", 11, { "type": "ChoiceList" } ]) # Changing type in reality is a bit more complex than these actions # so we put the correct values in place directly self.apply_user_action([ "BulkUpdateRecord", "Source", [21, 22], { "choices1": [["L", "a"], ["L", "b"]] } ]) starting_table.columns[1] = starting_table.columns[1]._replace( type="ChoiceList") self.assertTables([starting_table, summary_table]) self.assertTableData('GristSummary_6_Source', data=data) def test_rename_choices(self): self.load_sample(self.sample) # Create a summary section, grouped by both choicelist columns. self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]]) summary_table = Table( 2, "GristSummary_6_Source", primaryViewId=0, summarySourceTable=1, columns=[ Column(13, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(14, "choices2", "Choice", isFormula=False, formula="", summarySourceCol=12), Column(15, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(16, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) self.assertTables([self.starting_table, summary_table]) # Rename all the choices out_actions = self.apply_user_action( ["RenameChoices", "Source", "choices1", { "a": "aa", "b": "bb" }]) self.apply_user_action( ["RenameChoices", "Source", "choices2", { "c": "cc", "d": "dd" }]) # Actions from renaming choices1 only self.assertPartialOutActions( out_actions, { 'stored': [[ 'UpdateRecord', 'Source', 21, { 'choices1': ['L', u'aa', u'bb'] } ], [ 'BulkAddRecord', 'GristSummary_6_Source', [5, 6, 7, 8], { 'choices1': [u'aa', u'aa', u'bb', u'bb'], 'choices2': [u'c', u'd', u'c', u'd'] } ], [ 'BulkUpdateRecord', 'GristSummary_6_Source', [1, 2, 3, 4, 5, 6, 7, 8], { 'count': [0, 0, 0, 0, 1, 1, 1, 1] } ], [ 'BulkUpdateRecord', 'GristSummary_6_Source', [1, 2, 3, 4, 5, 6, 7, 8], { 'group': [['L'], ['L'], ['L'], ['L'], ['L', 21], ['L', 21], ['L', 21], ['L', 21]] } ]] }) # Final Source table is essentially the same as before, just with each letter doubled self.assertTableData('Source', data=[ ["id", "choices1", "choices2", "other"], [21, ["aa", "bb"], ["cc", "dd"], "foo"], ]) # Final summary table is very similar to before, but with two empty chunks of 4 rows # left over from each rename self.assertTableData( 'GristSummary_6_Source', data=[ ["id", "choices1", "choices2", "group", "count"], [1, "a", "c", [], 0], [2, "a", "d", [], 0], [3, "b", "c", [], 0], [4, "b", "d", [], 0], [5, "aa", "c", [], 0], [6, "aa", "d", [], 0], [7, "bb", "c", [], 0], [8, "bb", "d", [], 0], [9, "aa", "cc", [21], 1], [10, "aa", "dd", [21], 1], [11, "bb", "cc", [21], 1], [12, "bb", "dd", [21], 1], ])
def test_change_choice_to_choicelist(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Source", [ [10, "other", "Text", False, "", "other", ""], [11, "choices1", "Choice", False, "", "choice", ""], ] ]], "DATA": { "Source": [ ["id", "choices1", "other"], [21, "a", "foo"], [22, "b", "bar"], ] } }) starting_table = Table(1, "Source", primaryViewId=0, summarySourceTable=0, columns=[ Column(10, "other", "Text", isFormula=False, formula="", summarySourceCol=0), Column(11, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=0), ]) self.load_sample(sample) # Verify the starting table; there should be no views yet. self.assertTables([starting_table]) self.assertViews([]) # Create a summary section, grouped by the "choices1" column. self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]]) summary_table = Table( 2, "GristSummary_6_Source", primaryViewId=0, summarySourceTable=1, columns=[ Column(12, "choices1", "Choice", isFormula=False, formula="", summarySourceCol=11), Column(13, "group", "RefList:Source", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(14, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), ], ) data = [ ["id", "choices1", "group", "count"], [1, "a", [21], 1], [2, "b", [22], 1], ] self.assertTables([starting_table, summary_table]) self.assertTableData('GristSummary_6_Source', data=data) # Change the column from Choice to ChoiceList self.apply_user_action([ "UpdateRecord", "_grist_Tables_column", 11, { "type": "ChoiceList" } ]) # Changing type in reality is a bit more complex than these actions # so we put the correct values in place directly self.apply_user_action([ "BulkUpdateRecord", "Source", [21, 22], { "choices1": [["L", "a"], ["L", "b"]] } ]) starting_table.columns[1] = starting_table.columns[1]._replace( type="ChoiceList") self.assertTables([starting_table, summary_table]) self.assertTableData('GristSummary_6_Source', data=data)
class TestTypes(test_engine.EngineTestCase): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Types", [[21, "text", "Text", False, "", "", ""], [22, "numeric", "Numeric", False, "", "", ""], [23, "int", "Int", False, "", "", ""], [24, "bool", "Bool", False, "", "", ""], [25, "date", "Date", False, "", "", ""]] ], [ 2, "Formulas", [[ 30, "division", "Any", True, "Types.lookupOne(id=18).numeric / 2", "", "" ]] ]], "DATA": { "Types": [["id", "text", "numeric", "int", "bool", "date"], [11, "New York", "New York", "New York", "New York", "New York"], [12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö"], [13, False, False, False, False, False], [14, True, True, True, True, True], [15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0, 0, 0, 0, 0], [18, 1, 1, 1, 1, 1], [19, "", "", "", "", ""], [20, None, None, None, None, None]], "Formulas": [["id"], [1]] }, }) all_row_ids = [11, 12, 13, 14, 15, 16, 17, 18, 19, 20] def test_update_typed_cells(self): """ Tests that updated typed values are set as expected in the sandbox. Types should follow the rules: - After updating a cell with a value of a type compatible to the column type, the cell value should have the column's standard type - Otherwise, the cell value should have the type AltText """ self.load_sample(self.sample) out_actions = self.apply_user_action([ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "numeric": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "int": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "bool": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"Chîcágö", "New York" ], "date": [ None, "", 1, 0, 8.153, 1509556595, True, False, u"2019-01-22 00:47:39", "New York" ] } ]) self.assertPartialOutActions( out_actions, { "stored": [ [ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ None, "", "1", "0", "8.153", "1509556595", "True", "False", u"Chîcágö", "New York" ], "numeric": [ None, None, 1.0, 0.0, 8.153, 1509556595.0, 1.0, 0.0, u"Chîcágö", "New York" ], "int": [ None, None, 1, 0, 8, 1509556595, 1, 0, u"Chîcágö", "New York" ], "bool": [ False, False, True, False, True, True, True, False, u"Chîcágö", "New York" ], "date": [ None, None, 1.0, 0.0, 8.153, 1509556595.0, 1.0, 0.0, 1548115200.0, "New York" ] } ], ["UpdateRecord", "Formulas", 1, { "division": 0.0 }], ], "undo": [ [ "BulkUpdateRecord", "Types", self.all_row_ids, { "text": [ "New York", u"Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "numeric": [ "New York", u"Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "int": [ "New York", u"Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ], "bool": [ "New York", u"Chîcágö", False, True, 1509556595, 8.153, False, True, "", None ], "date": [ "New York", u"Chîcágö", False, True, 1509556595, 8.153, 0, 1, "", None ] } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [11, None, None, None, False, None], [12, "", None, None, False, None], [13, "1", 1.0, 1, True, 1.0], [14, "0", 0.0, 0, False, 0.0], [15, "8.153", 8.153, 8, True, 8.153], [ 16, "1509556595", 1509556595, 1509556595, True, 1509556595.0 ], [17, "True", 1.0, 1, True, 1.0], [18, "False", 0.0, 0, False, 0.0], [ 19, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", 1548115200.0 ], [ 20, "New York", "New York", "New York", "New York", "New York" ]]) def test_text_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be Text - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Text" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Numeric -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "numeric": ["False", "True", "1509556595", "8.153", "0", "1"] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Text" } ], [ "UpdateRecord", "Formulas", 1, { "division": ["E", "TypeError"] } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "numeric": [False, True, 1509556595, 8.153, 0, 1] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Text conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "int": ["False", "True", "1509556595", "8.153", "0", "1"] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "int": [False, True, 1509556595, 8.153, 0, 1] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Text out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "bool": [ "False", "True", "1509556595", "8.153", "False", "True" ] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "bool": [False, True, 1509556595, 8.153, False, True] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Text out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "date": ["False", "True", "1509556595", "8.153", "0", "1"] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Text" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 15, 16, 17, 18], { "date": [False, True, 1509556595.0, 8.153, 0.0, 1.0] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [ 12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö" ], [13, False, "False", "False", "False", "False"], [14, True, "True", "True", "True", "True"], [ 15, 1509556595, "1509556595", "1509556595", "1509556595", "1509556595" ], [16, 8.153, "8.153", "8.153", "8.153", "8.153"], [17, 0, "0", "0", "False", "0"], [18, 1, "1", "1", "True", "1"], [19, "", "", "", "", ""], [20, None, None, None, None, None]]) def test_numeric_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Numeric or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [False, True, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Int -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [False, True, ""] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [0.0, 1.0, 0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Numeric" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [False, True, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Numeric conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Numeric" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Numeric" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "date": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Numeric" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 19], { "date": [False, True, ""] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[ ["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [ 12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö" ], [13, 0.0, False, 0.0, 0.0, 0.0], [14, 1.0, True, 1.0, 1.0, 1.0], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0.0, 0.0, 0.0, 0.0, 0.0], [18, 1.0, 1.0, 1.0, 1.0, 1.0], [19, None, "", None, None, None], [20, None, None, None, None, None], ]) def test_numeric_to_text_conversion(self): """ Tests text formatting of floats of different sizes. """ sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Types", [ [22, "numeric", "Numeric", False, "", "", ""], [23, "other", "Text", False, "", "", ""], ] ], ], "DATA": { "Types": [["id", "numeric"]] + [[i + 1, 1.23456789 * 10**(i - 20)] for i in range(40)] }, }) self.load_sample(sample) out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 ], { "numeric": [ "1.23456789e-20", "1.23456789e-19", "1.23456789e-18", "1.23456789e-17", "1.23456789e-16", "1.23456789e-15", "1.23456789e-14", "1.23456789e-13", "1.23456789e-12", "1.23456789e-11", "1.23456789e-10", "1.23456789e-09", "1.23456789e-08", "1.23456789e-07", "1.23456789e-06", "1.23456789e-05", "0.000123456789", "0.00123456789", "0.0123456789", "0.123456789", "1.23456789", "12.3456789", "123.456789", "1234.56789", "12345.6789", "123456.789", "1234567.89", "12345678.9", "123456789", "1234567890", "12345678900", "123456789000", "1234567890000", "12345678900000", "123456789000000", "1234567890000000", "1.23456789e+16", "1.23456789e+17", "1.23456789e+18", "1.23456789e+19" ] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 ], { "numeric": [ 1.2345678899999998e-20, 1.2345678899999999e-19, 1.23456789e-18, 1.23456789e-17, 1.2345678899999998e-16, 1.23456789e-15, 1.23456789e-14, 1.23456789e-13, 1.2345678899999998e-12, 1.2345678899999998e-11, 1.2345678899999998e-10, 1.23456789e-09, 1.2345678899999999e-08, 1.23456789e-07, 1.2345678899999998e-06, 1.23456789e-05, 0.000123456789, 0.00123456789, 0.012345678899999999, 0.123456789, 1.23456789, 12.3456789, 123.45678899999999, 1234.5678899999998, 12345.678899999999, 123456.78899999999, 1234567.89, 12345678.899999999, 123456788.99999999, 1234567890.0, 12345678899.999998, 123456788999.99998, 1234567890000.0, 12345678899999.998, 123456788999999.98, 1234567890000000.0, 1.2345678899999998e+16, 1.2345678899999998e+17, 1.23456789e+18, 1.2345678899999998e+19 ] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ] }) def test_int_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Int or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "text": [0, 1, 8, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Int" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "text": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "numeric": [0, 1, 8, None] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Int" } ], ] + six.PY2 * [["UpdateRecord", "Formulas", 1, { "division": 0 }]], # Only in Python 2 due to integer division, "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "numeric": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ] + six.PY2 * [["UpdateRecord", "Formulas", 1, { "division": 0.5 }]], # Only in Python 2 due to integer division }) # Test Int -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Int" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Bool -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "bool", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 17, 18, 19], { "bool": [0, 1, 8, 0, 1, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Int" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 16, 17, 18, 19], { "bool": [False, True, 8.153, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ], ] }) # Test Date -> Int conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Int" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Int" }], [ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "date": [0, 1, 8, None] } ], ["UpdateRecord", "_grist_Tables_column", 25, { "type": "Int" }]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 16, 19], { "date": [False, True, 8.153, ""] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [ 12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö" ], [13, 0, 0, False, 0, 0], [14, 1, 1, True, 1, 1], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8, 8, 8.153, 8, 8], [17, 0, 0, 0, 0, 0], [18, 1, 1, 1, 1, 1], [19, None, None, "", None, None], [20, None, None, None, None, None]]) def test_bool_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Bool or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "text": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Bool" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "text": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "numeric": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Bool" } ], ] + six.PY2 * [["UpdateRecord", "Formulas", 1, { "division": 0 }]], # Only in Python 2 due to integer division, "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "numeric": [1509556595.0, 8.153, 0.0, 1.0, "", None] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ] + six.PY2 * [["UpdateRecord", "Formulas", 1, { "division": 0.5 }]], # Only in Python 2 due to integer division }) # Test Int -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "int": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Bool" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "int": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Bool" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Test Date -> Bool conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Bool" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "date", { "type": "Bool" }], [ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "date": [True, True, False, True, False, False] } ], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Bool" } ]], "undo": [[ "BulkUpdateRecord", "Types", [15, 16, 17, 18, 19, 20], { "date": [1509556595, 8.153, 0, 1, "", None] } ], ["ModifyColumn", "Types", "date", { "type": "Date" }], [ "UpdateRecord", "_grist_Tables_column", 25, { "type": "Date" } ]] }) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [ 12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö" ], [13, False, False, False, False, False], [14, True, True, True, True, True], [15, True, True, True, 1509556595, True], [16, True, True, True, 8.153, True], [17, False, False, False, 0, False], [18, True, True, True, 1, True], [19, False, False, False, "", False], [20, False, False, False, None, False]]) def test_date_conversions(self): """ Tests that column type changes occur as expected in the sandbox: - Resulting cell values should all be of type Date or AltText - Only non-compatible values should appear in the resulting BulkUpdateRecord """ self.load_sample(self.sample) # Test Text -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "text", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "text", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Date" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "text": [False, True, ""] } ], ["ModifyColumn", "Types", "text", { "type": "Text" }], [ "UpdateRecord", "_grist_Tables_column", 21, { "type": "Text" } ], ] }) # Test Numeric -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "numeric": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Date" } ], [ "UpdateRecord", "Formulas", 1, { "division": ["E", "TypeError"] } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "numeric": [False, True, ""] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ["UpdateRecord", "Formulas", 1, { "division": 0.5 }], ] }) # Test Int -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "int", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "int", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Date" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [13, 14, 19], { "int": [False, True, ""] } ], ["ModifyColumn", "Types", "int", { "type": "Int" }], [ "UpdateRecord", "_grist_Tables_column", 23, { "type": "Int" } ], ] }) # Test Bool -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "bool", { "type": "Date" }]) self.assertPartialOutActions( out_actions, { "stored": [["ModifyColumn", "Types", "bool", { "type": "Date" }], [ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [0.0, 1.0, 0.0, 1.0, None] } ], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Date" } ]], "undo": [[ "BulkUpdateRecord", "Types", [13, 14, 17, 18, 19], { "bool": [False, True, False, True, ""] } ], ["ModifyColumn", "Types", "bool", { "type": "Bool" }], [ "UpdateRecord", "_grist_Tables_column", 24, { "type": "Bool" } ]] }) # Test Date -> Date conversion out_actions = self.apply_user_action( ["ModifyColumn", "Types", "date", { "type": "Date" }]) self.assertPartialOutActions(out_actions, {"stored": [], "undo": []}) # Assert that the final table is as expected self.assertTableData( "Types", data=[["id", "text", "numeric", "int", "bool", "date"], [ 11, "New York", "New York", "New York", "New York", "New York" ], [ 12, u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö", u"Chîcágö" ], [13, 0.0, 0.0, 0.0, 0.0, False], [14, 1.0, 1.0, 1.0, 1.0, True], [ 15, 1509556595, 1509556595, 1509556595, 1509556595, 1509556595 ], [16, 8.153, 8.153, 8.153, 8.153, 8.153], [17, 0.0, 0.0, 0.0, 0.0, 0], [18, 1.0, 1.0, 1.0, 1.0, 1], [19, None, None, None, None, ""], [20, None, None, None, None, None]]) def test_numerics_are_floats(self): """ Tests that in formulas, numeric values are floats, not integers. Important to avoid truncation. """ self.load_sample(self.sample) self.assertTableData('Formulas', data=[ ['id', 'division'], [1, 0.5], ])
def test_numeric_to_text_conversion(self): """ Tests text formatting of floats of different sizes. """ sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Types", [ [22, "numeric", "Numeric", False, "", "", ""], [23, "other", "Text", False, "", "", ""], ] ], ], "DATA": { "Types": [["id", "numeric"]] + [[i + 1, 1.23456789 * 10**(i - 20)] for i in range(40)] }, }) self.load_sample(sample) out_actions = self.apply_user_action( ["ModifyColumn", "Types", "numeric", { "type": "Text" }]) self.assertPartialOutActions( out_actions, { "stored": [ ["ModifyColumn", "Types", "numeric", { "type": "Text" }], [ "BulkUpdateRecord", "Types", [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 ], { "numeric": [ "1.23456789e-20", "1.23456789e-19", "1.23456789e-18", "1.23456789e-17", "1.23456789e-16", "1.23456789e-15", "1.23456789e-14", "1.23456789e-13", "1.23456789e-12", "1.23456789e-11", "1.23456789e-10", "1.23456789e-09", "1.23456789e-08", "1.23456789e-07", "1.23456789e-06", "1.23456789e-05", "0.000123456789", "0.00123456789", "0.0123456789", "0.123456789", "1.23456789", "12.3456789", "123.456789", "1234.56789", "12345.6789", "123456.789", "1234567.89", "12345678.9", "123456789", "1234567890", "12345678900", "123456789000", "1234567890000", "12345678900000", "123456789000000", "1234567890000000", "1.23456789e+16", "1.23456789e+17", "1.23456789e+18", "1.23456789e+19" ] } ], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Text" } ], ], "undo": [ [ "BulkUpdateRecord", "Types", [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40 ], { "numeric": [ 1.2345678899999998e-20, 1.2345678899999999e-19, 1.23456789e-18, 1.23456789e-17, 1.2345678899999998e-16, 1.23456789e-15, 1.23456789e-14, 1.23456789e-13, 1.2345678899999998e-12, 1.2345678899999998e-11, 1.2345678899999998e-10, 1.23456789e-09, 1.2345678899999999e-08, 1.23456789e-07, 1.2345678899999998e-06, 1.23456789e-05, 0.000123456789, 0.00123456789, 0.012345678899999999, 0.123456789, 1.23456789, 12.3456789, 123.45678899999999, 1234.5678899999998, 12345.678899999999, 123456.78899999999, 1234567.89, 12345678.899999999, 123456788.99999999, 1234567890.0, 12345678899.999998, 123456788999.99998, 1234567890000.0, 12345678899999.998, 123456788999999.98, 1234567890000000.0, 1.2345678899999998e+16, 1.2345678899999998e+17, 1.23456789e+18, 1.2345678899999998e+19 ] } ], ["ModifyColumn", "Types", "numeric", { "type": "Numeric" }], [ "UpdateRecord", "_grist_Tables_column", 22, { "type": "Numeric" } ], ] })
class TestSummary(test_engine.EngineTestCase): sample = testutil.parse_test_sample({ "SCHEMA": [ [1, "Address", [ [11, "city", "Text", False, "", "City", ""], [12, "state", "Text", False, "", "State", "WidgetOptions1"], [13, "amount", "Numeric", False, "", "Amount", "WidgetOptions2"], ]] ], "DATA": { "Address": [ ["id", "city", "state", "amount" ], [ 21, "New York", "NY" , 1. ], [ 22, "Albany", "NY" , 2. ], [ 23, "Seattle", "WA" , 3. ], [ 24, "Chicago", "IL" , 4. ], [ 25, "Bedford", "MA" , 5. ], [ 26, "New York", "NY" , 6. ], [ 27, "Buffalo", "NY" , 7. ], [ 28, "Bedford", "NY" , 8. ], [ 29, "Boston", "MA" , 9. ], [ 30, "Yonkers", "NY" , 10. ], [ 31, "New York", "NY" , 11. ], ] } }) starting_table = Table(1, "Address", primaryViewId=0, summarySourceTable=0, columns=[ Column(11, "city", "Text", isFormula=False, formula="", summarySourceCol=0), Column(12, "state", "Text", isFormula=False, formula="", summarySourceCol=0), Column(13, "amount", "Numeric", isFormula=False, formula="", summarySourceCol=0), ]) starting_table_data = [ ["id", "city", "state", "amount" ], [ 21, "New York", "NY" , 1 ], [ 22, "Albany", "NY" , 2 ], [ 23, "Seattle", "WA" , 3 ], [ 24, "Chicago", "IL" , 4 ], [ 25, "Bedford", "MA" , 5 ], [ 26, "New York", "NY" , 6 ], [ 27, "Buffalo", "NY" , 7 ], [ 28, "Bedford", "NY" , 8 ], [ 29, "Boston", "MA" , 9 ], [ 30, "Yonkers", "NY" , 10 ], [ 31, "New York", "NY" , 11 ], ] #---------------------------------------------------------------------- def test_encode_summary_table_name(self): self.assertEqual(summary.encode_summary_table_name("Foo"), "GristSummary_3_Foo") self.assertEqual(summary.encode_summary_table_name("Foo2"), "GristSummary_4_Foo2") self.assertEqual(summary.decode_summary_table_name("GristSummary_3_Foo"), "Foo") self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo2"), "Foo2") self.assertEqual(summary.decode_summary_table_name("GristSummary_3_Foo2"), "Foo") self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo2_2"), "Foo2") # Test that underscore in the name is OK. self.assertEqual(summary.decode_summary_table_name("GristSummary_5_Foo_234"), "Foo_2") self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo_234"), "Foo_") self.assertEqual(summary.decode_summary_table_name("GristSummary_6__Foo_234"), "_Foo_2") # Test that we return None for invalid values. self.assertEqual(summary.decode_summary_table_name("Foo2"), None) self.assertEqual(summary.decode_summary_table_name("GristSummary_3Foo"), None) self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo"), None) self.assertEqual(summary.decode_summary_table_name("GristSummary_3X_Foo"), None) self.assertEqual(summary.decode_summary_table_name("_5_Foo_234"), None) self.assertEqual(summary.decode_summary_table_name("_GristSummary_3_Foo"), None) self.assertEqual(summary.decode_summary_table_name("gristsummary_3_Foo"), None) self.assertEqual(summary.decode_summary_table_name("GristSummary3_Foo"), None) #---------------------------------------------------------------------- def test_create_view_section(self): self.load_sample(self.sample) # Verify the starting table; there should be no views yet. self.assertTables([self.starting_table]) self.assertViews([]) # Create a view + section for the initial table. self.apply_user_action(["CreateViewSection", 1, 0, "record", None]) # Verify that we got a new view, with one section, and three fields. self.assertTables([self.starting_table]) basic_view = View(1, sections=[ Section(1, parentKey="record", tableRef=1, fields=[ Field(1, colRef=11), Field(2, colRef=12), Field(3, colRef=13), ]) ]) self.assertViews([basic_view]) self.assertTableData("Address", self.starting_table_data) # Create a "Totals" section, i.e. a summary with no group-by columns. self.apply_user_action(["CreateViewSection", 1, 0, "record", []]) # Verify that a new table gets created, and a new view, with a section for that table, # and some auto-generated summary fields. summary_table1 = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1, columns=[ Column(14, "group", "RefList:Address", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(15, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), Column(16, "amount", "Numeric", isFormula=True, summarySourceCol=0, formula="SUM($group.amount)"), ]) summary_view1 = View(2, sections=[ Section(2, parentKey="record", tableRef=2, fields=[ Field(4, colRef=15), Field(5, colRef=16), ]) ]) self.assertTables([self.starting_table, summary_table1]) self.assertViews([basic_view, summary_view1]) # Verify the summarized data. self.assertTableData('GristSummary_7_Address', cols="subset", data=[ [ "id", "count", "amount"], [ 1, 11, 66.0 ], ]) # Create a summary section, grouped by the "State" column. self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]]) # Verify that a new table gets created again, a new view, and a section for that table. # Note that we also check that summarySourceTable and summarySourceCol fields are correct. summary_table2 = Table(3, "GristSummary_7_Address2", primaryViewId=0, summarySourceTable=1, columns=[ Column(17, "state", "Text", isFormula=False, formula="", summarySourceCol=12), Column(18, "group", "RefList:Address", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(19, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), Column(20, "amount", "Numeric", isFormula=True, summarySourceCol=0, formula="SUM($group.amount)"), ]) summary_view2 = View(3, sections=[ Section(3, parentKey="record", tableRef=3, fields=[ Field(6, colRef=17), Field(7, colRef=19), Field(8, colRef=20), ]) ]) self.assertTables([self.starting_table, summary_table1, summary_table2]) self.assertViews([basic_view, summary_view1, summary_view2]) # Verify more fields of the new column objects. self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[ ['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'], [17, 'state', 'Text', '', 'WidgetOptions1', 'State'], [20, 'amount', 'Numeric', 'SUM($group.amount)', 'WidgetOptions2', 'Amount'], ]) # Verify the summarized data. self.assertTableData('GristSummary_7_Address2', cols="subset", data=[ [ "id", "state", "count", "amount" ], [ 1, "NY", 7, 1.+2+6+7+8+10+11 ], [ 2, "WA", 1, 3. ], [ 3, "IL", 1, 4. ], [ 4, "MA", 2, 5.+9 ], ]) # Create a summary section grouped by two columns ("city" and "state"). self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) # Verify the new table and views. summary_table3 = Table(4, "GristSummary_7_Address3", primaryViewId=0, summarySourceTable=1, columns=[ Column(21, "city", "Text", isFormula=False, formula="", summarySourceCol=11), Column(22, "state", "Text", isFormula=False, formula="", summarySourceCol=12), Column(23, "group", "RefList:Address", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(24, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), Column(25, "amount", "Numeric", isFormula=True, summarySourceCol=0, formula="SUM($group.amount)"), ]) summary_view3 = View(4, sections=[ Section(4, parentKey="record", tableRef=4, fields=[ Field(9, colRef=21), Field(10, colRef=22), Field(11, colRef=24), Field(12, colRef=25), ]) ]) self.assertTables([self.starting_table, summary_table1, summary_table2, summary_table3]) self.assertViews([basic_view, summary_view1, summary_view2, summary_view3]) # Verify the summarized data. self.assertTableData('GristSummary_7_Address3', cols="subset", data=[ [ "id", "city", "state", "count", "amount" ], [ 1, "New York", "NY" , 3, 1.+6+11 ], [ 2, "Albany", "NY" , 1, 2. ], [ 3, "Seattle", "WA" , 1, 3. ], [ 4, "Chicago", "IL" , 1, 4. ], [ 5, "Bedford", "MA" , 1, 5. ], [ 6, "Buffalo", "NY" , 1, 7. ], [ 7, "Bedford", "NY" , 1, 8. ], [ 8, "Boston", "MA" , 1, 9. ], [ 9, "Yonkers", "NY" , 1, 10. ], ]) # The original table's data should not have changed. self.assertTableData("Address", self.starting_table_data) #---------------------------------------------------------------------- def test_summary_gencode(self): self.maxDiff = 1000 # If there is a discrepancy, allow the bigger diff. self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", []]) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) self.assertMultiLineEqual(self.engine.fetch_table_schema(), """import grist from functions import * # global uppercase functions import datetime, math, re # modules commonly needed in formulas @grist.UserTable class Address: city = grist.Text() state = grist.Text() amount = grist.Numeric() class _Summary: @grist.formulaType(grist.ReferenceList('Address')) def group(rec, table): return table.getSummarySourceGroup(rec) @grist.formulaType(grist.Int()) def count(rec, table): return len(rec.group) @grist.formulaType(grist.Numeric()) def amount(rec, table): return SUM(rec.group.amount) """) #---------------------------------------------------------------------- def test_summary_table_reuse(self): # Test that we'll reuse a suitable summary table when already available. self.load_sample(self.sample) # Create a summary section grouped by two columns ("city" and "state"). self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) # Verify the new table and views. summary_table = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1, columns=[ Column(14, "city", "Text", isFormula=False, formula="", summarySourceCol=11), Column(15, "state", "Text", isFormula=False, formula="", summarySourceCol=12), Column(16, "group", "RefList:Address", isFormula=True, summarySourceCol=0, formula="table.getSummarySourceGroup(rec)"), Column(17, "count", "Int", isFormula=True, summarySourceCol=0, formula="len($group)"), Column(18, "amount", "Numeric", isFormula=True, summarySourceCol=0, formula="SUM($group.amount)"), ]) summary_view = View(1, sections=[ Section(1, parentKey="record", tableRef=2, fields=[ Field(1, colRef=14), Field(2, colRef=15), Field(3, colRef=17), Field(4, colRef=18), ]) ]) self.assertTables([self.starting_table, summary_table]) self.assertViews([summary_view]) # Create twoo other views + view sections with the same breakdown (in different order # of group-by fields, which should still reuse the same table). self.apply_user_action(["CreateViewSection", 1, 0, "record", [12,11]]) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) summary_view2 = View(2, sections=[ Section(2, parentKey="record", tableRef=2, fields=[ Field(5, colRef=15), Field(6, colRef=14), Field(7, colRef=17), Field(8, colRef=18), ]) ]) summary_view3 = View(3, sections=[ Section(3, parentKey="record", tableRef=2, fields=[ Field(9, colRef=14), Field(10, colRef=15), Field(11, colRef=17), Field(12, colRef=18), ]) ]) # Verify that we have a new view, but are reusing the table. self.assertTables([self.starting_table, summary_table]) self.assertViews([summary_view, summary_view2, summary_view3]) # Verify the summarized data. self.assertTableData('GristSummary_7_Address', cols="subset", data=[ [ "id", "city", "state", "count", "amount" ], [ 1, "New York", "NY" , 3, 1.+6+11 ], [ 2, "Albany", "NY" , 1, 2. ], [ 3, "Seattle", "WA" , 1, 3. ], [ 4, "Chicago", "IL" , 1, 4. ], [ 5, "Bedford", "MA" , 1, 5. ], [ 6, "Buffalo", "NY" , 1, 7. ], [ 7, "Bedford", "NY" , 1, 8. ], [ 8, "Boston", "MA" , 1, 9. ], [ 9, "Yonkers", "NY" , 1, 10. ], ]) #---------------------------------------------------------------------- def test_summary_no_invalid_reuse(self): # Verify that if we have some summary tables for one table, they don't mistakenly get used # when we need a summary for another table. # Load table and create a couple summary sections, for totals, and grouped by "state". self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", []]) self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]]) self.assertTables([ self.starting_table, Table(2, "GristSummary_7_Address", 0, 1, columns=[ Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(15, "count", "Int", True, "len($group)", 0), Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), Table(3, "GristSummary_7_Address2", 0, 1, columns=[ Column(17, "state", "Text", False, "", 12), Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(19, "count", "Int", True, "len($group)", 0), Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), ]) # Create another table similar to the first one. self.apply_user_action(["AddTable", "Address2", [ { "id": "city", "type": "Text" }, { "id": "state", "type": "Text" }, { "id": "amount", "type": "Numeric" }, ]]) data = self.sample["DATA"]["Address"] self.apply_user_action(["BulkAddRecord", "Address2", data.row_ids, data.columns]) # Check that we've loaded the right data, and have the new table. self.assertTableData("Address", cols="subset", data=self.starting_table_data) self.assertTableData("Address2", cols="subset", data=self.starting_table_data) self.assertTableData("_grist_Tables", cols="subset", data=[ ['id', 'tableId', 'summarySourceTable'], [ 1, 'Address', 0], [ 2, 'GristSummary_7_Address', 1], [ 3, 'GristSummary_7_Address2', 1], [ 4, 'Address2', 0], ]) # Now create similar summary sections for the new table. self.apply_user_action(["CreateViewSection", 4, 0, "record", []]) self.apply_user_action(["CreateViewSection", 4, 0, "record", [23]]) # Make sure this creates new section rather than reuses similar ones for the wrong table. self.assertTables([ self.starting_table, Table(2, "GristSummary_7_Address", 0, 1, columns=[ Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(15, "count", "Int", True, "len($group)", 0), Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), Table(3, "GristSummary_7_Address2", 0, 1, columns=[ Column(17, "state", "Text", False, "", 12), Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(19, "count", "Int", True, "len($group)", 0), Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), Table(4, "Address2", primaryViewId=3, summarySourceTable=0, columns=[ Column(21, "manualSort", "ManualSortPos",False, "", 0), Column(22, "city", "Text", False, "", 0), Column(23, "state", "Text", False, "", 0), Column(24, "amount", "Numeric", False, "", 0), ]), Table(5, "GristSummary_8_Address2", 0, 4, columns=[ Column(25, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0), Column(26, "count", "Int", True, "len($group)", 0), Column(27, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), Table(6, "GristSummary_8_Address2_2", 0, 4, columns=[ Column(28, "state", "Text", False, "", 23), Column(29, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0), Column(30, "count", "Int", True, "len($group)", 0), Column(31, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), ]) #---------------------------------------------------------------------- def test_summary_updates(self): # Verify that summary tables update automatically when we change a value used in a summary # formula; or a value in a group-by column; or add/remove a record; that records get # auto-added when new group-by combinations appear. # Load sample and create a summary section grouped by two columns ("city" and "state"). self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) # Verify that the summary table respects all updates to the source table. self._do_test_updates("Address", "GristSummary_7_Address") def _do_test_updates(self, source_tbl_name, summary_tbl_name): # This is the main part of test_summary_updates(). It's moved to its own method so that # updates can be verified the same way after a table rename. # Verify the summarized data. self.assertTableData(summary_tbl_name, cols="subset", data=[ [ "id", "city", "state", "count", "amount" ], [ 1, "New York", "NY" , 3, 1.+6+11 ], [ 2, "Albany", "NY" , 1, 2. ], [ 3, "Seattle", "WA" , 1, 3. ], [ 4, "Chicago", "IL" , 1, 4. ], [ 5, "Bedford", "MA" , 1, 5. ], [ 6, "Buffalo", "NY" , 1, 7. ], [ 7, "Bedford", "NY" , 1, 8. ], [ 8, "Boston", "MA" , 1, 9. ], [ 9, "Yonkers", "NY" , 1, 10. ], ]) # Change an amount (New York, NY, 6 -> 106), check that the right calc action gets emitted. out_actions = self.update_record(source_tbl_name, 26, amount=106) self.assertPartialOutActions(out_actions, { "stored": [ actions.UpdateRecord(source_tbl_name, 26, {'amount': 106}), actions.UpdateRecord(summary_tbl_name, 1, {'amount': 1.+106+11}), ] }) # Change a groupby value so that a record moves from one summary group to another. # Bedford, NY, 8.0 -> Bedford, MA, 8.0 out_actions = self.update_record(source_tbl_name, 28, state="MA") self.assertPartialOutActions(out_actions, { "stored": [ actions.UpdateRecord(source_tbl_name, 28, {'state': 'MA'}), actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'amount': [5.0 + 8.0, 0.0]}), actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'count': [2, 0]}), actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'group': [[25, 28], []]}), ] }) # Add a record to an existing group (Bedford, MA, 108.0) out_actions = self.add_record(source_tbl_name, city="Bedford", state="MA", amount=108.0) self.assertPartialOutActions(out_actions, { "stored": [ actions.AddRecord(source_tbl_name, 32, {'city': 'Bedford', 'state': 'MA', 'amount': 108.0}), actions.UpdateRecord(summary_tbl_name, 5, {'amount': 5.0 + 8.0 + 108.0}), actions.UpdateRecord(summary_tbl_name, 5, {'count': 3}), actions.UpdateRecord(summary_tbl_name, 5, {'group': [25, 28, 32]}), ] }) # Remove a record (rowId=28, Bedford, MA, 8.0) out_actions = self.remove_record(source_tbl_name, 28) self.assertPartialOutActions(out_actions, { "stored": [ actions.RemoveRecord(source_tbl_name, 28), actions.UpdateRecord(summary_tbl_name, 5, {'amount': 5.0 + 108.0}), actions.UpdateRecord(summary_tbl_name, 5, {'count': 2}), actions.UpdateRecord(summary_tbl_name, 5, {'group': [25, 32]}), ] }) # Change groupby value to create a new combination (rowId 25, Bedford, MA, 5.0 -> Salem, MA). # A new summary record should be added. out_actions = self.update_record(source_tbl_name, 25, city="Salem") self.assertPartialOutActions(out_actions, { "stored": [ actions.UpdateRecord(source_tbl_name, 25, {'city': 'Salem'}), actions.AddRecord(summary_tbl_name, 10, {'city': 'Salem', 'state': 'MA'}), actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'amount': [108.0, 5.0]}), actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'count': [1, 1]}), actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'group': [[32], [25]]}), ] }) # Add a record with a new combination (Amherst, MA, 17) out_actions = self.add_record(source_tbl_name, city="Amherst", state="MA", amount=17.0) self.assertPartialOutActions(out_actions, { "stored": [ actions.AddRecord(source_tbl_name, 33, {'city': 'Amherst', 'state': 'MA', 'amount': 17.}), actions.AddRecord(summary_tbl_name, 11, {'city': 'Amherst', 'state': 'MA'}), actions.UpdateRecord(summary_tbl_name, 11, {'amount': 17.0}), actions.UpdateRecord(summary_tbl_name, 11, {'count': 1}), actions.UpdateRecord(summary_tbl_name, 11, {'group': [33]}), ] }) # Verify the resulting data after all the updates. self.assertTableData(summary_tbl_name, cols="subset", data=[ [ "id", "city", "state", "count", "amount" ], [ 1, "New York", "NY" , 3, 1.+106+11 ], [ 2, "Albany", "NY" , 1, 2. ], [ 3, "Seattle", "WA" , 1, 3. ], [ 4, "Chicago", "IL" , 1, 4. ], [ 5, "Bedford", "MA" , 1, 108. ], [ 6, "Buffalo", "NY" , 1, 7. ], [ 7, "Bedford", "NY" , 0, 0. ], [ 8, "Boston", "MA" , 1, 9. ], [ 9, "Yonkers", "NY" , 1, 10. ], [ 10, "Salem", "MA" , 1, 5.0 ], [ 11, "Amherst", "MA" , 1, 17.0 ], ]) #---------------------------------------------------------------------- def test_table_rename(self): # Verify that summary tables keep working and updating when source table is renamed. # Load sample and create a couple of summary sections. self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) # Check what tables we have now. self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [ [1, "Address", 0], [2, "GristSummary_7_Address", 1], ]) # Rename the table: this is what we are really testing in this test case. self.apply_user_action(["RenameTable", "Address", "Location"]) self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [ [1, "Location", 0], [2, "GristSummary_8_Location", 1], ]) # Verify that the bigger summary table respects all updates to the renamed source table. self._do_test_updates("Location", "GristSummary_8_Location") #---------------------------------------------------------------------- def test_table_rename_multiple(self): # Similar to the above, verify renames, but now with two summary tables. self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) self.apply_user_action(["CreateViewSection", 1, 0, "record", []]) self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [ [1, "Address", 0], [2, "GristSummary_7_Address", 1], [3, "GristSummary_7_Address2", 1], ]) # Verify the data in the simple totals-only summary table. self.assertTableData('GristSummary_7_Address2', cols="subset", data=[ [ "id", "count", "amount"], [ 1, 11, 66.0 ], ]) # Do a rename. self.apply_user_action(["RenameTable", "Address", "Addresses"]) self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [ [1, "Addresses", 0], [2, "GristSummary_9_Addresses", 1], [3, "GristSummary_9_Addresses2", 1], ]) self.assertTableData('GristSummary_9_Addresses2', cols="subset", data=[ [ "id", "count", "amount"], [ 1, 11, 66.0 ], ]) # Remove one of the tables so that we can use _do_test_updates to verify updates still work. self.apply_user_action(["RemoveTable", "GristSummary_9_Addresses2"]) self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [ [1, "Addresses", 0], [2, "GristSummary_9_Addresses", 1], ]) self._do_test_updates("Addresses", "GristSummary_9_Addresses") #---------------------------------------------------------------------- def test_change_summary_formula(self): # Verify that changing a summary formula affects all group-by variants, and adding a new # summary table gets the changed formula. # # (Recall that all summaries of a single table are *conceptually* variants of a single summary # table, sharing all formulas and differing only in the group-by columns.) self.load_sample(self.sample) self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]]) self.apply_user_action(["CreateViewSection", 1, 0, "record", []]) # These are the tables and columns we automatically get. self.assertTables([ self.starting_table, Table(2, "GristSummary_7_Address", 0, 1, columns=[ Column(14, "city", "Text", False, "", 11), Column(15, "state", "Text", False, "", 12), Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(17, "count", "Int", True, "len($group)", 0), Column(18, "amount", "Numeric", True, "SUM($group.amount)", 0), ]), Table(3, "GristSummary_7_Address2", 0, 1, columns=[ Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(20, "count", "Int", True, "len($group)", 0), Column(21, "amount", "Numeric", True, "SUM($group.amount)", 0), ]) ]) # Now change a formula using one of the summary tables. It should trigger an equivalent # change in the other. self.apply_user_action(["ModifyColumn", "GristSummary_7_Address", "amount", {"formula": "10*sum($group.amount)"}]) self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[ ['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'], [18, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'], [21, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'], ]) # Change a formula and a few other fields in the other table, and verify a change to both. self.apply_user_action(["ModifyColumn", "GristSummary_7_Address2", "amount", {"formula": "100*sum($group.amount)", "type": "Text", "widgetOptions": "hello", "label": "AMOUNT", "untieColIdFromLabel": True }]) self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[ ['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'], [18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'], [21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'], ]) # Check the values in the summary tables: they should reflect the new formula. self.assertTableData('GristSummary_7_Address', cols="subset", data=[ [ "id", "city", "state", "count", "amount" ], [ 1, "New York", "NY" , 3, str(100*(1.+6+11))], [ 2, "Albany", "NY" , 1, "200.0" ], [ 3, "Seattle", "WA" , 1, "300.0" ], [ 4, "Chicago", "IL" , 1, "400.0" ], [ 5, "Bedford", "MA" , 1, "500.0" ], [ 6, "Buffalo", "NY" , 1, "700.0" ], [ 7, "Bedford", "NY" , 1, "800.0" ], [ 8, "Boston", "MA" , 1, "900.0" ], [ 9, "Yonkers", "NY" , 1, "1000.0" ], ]) self.assertTableData('GristSummary_7_Address2', cols="subset", data=[ [ "id", "count", "amount"], [ 1, 11, "6600.0"], ]) # Add a new summary table, and check that it gets the new formula. self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]]) self.assertTables([ self.starting_table, Table(2, "GristSummary_7_Address", 0, 1, columns=[ Column(14, "city", "Text", False, "", 11), Column(15, "state", "Text", False, "", 12), Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(17, "count", "Int", True, "len($group)", 0), Column(18, "amount", "Text", True, "100*sum($group.amount)", 0), ]), Table(3, "GristSummary_7_Address2", 0, 1, columns=[ Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(20, "count", "Int", True, "len($group)", 0), Column(21, "amount", "Text", True, "100*sum($group.amount)", 0), ]), Table(4, "GristSummary_7_Address3", 0, 1, columns=[ Column(22, "state", "Text", False, "", 12), Column(23, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0), Column(24, "count", "Int", True, "len($group)", 0), Column(25, "amount", "Text", True, "100*sum($group.amount)", 0), ]) ]) self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[ ['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'], [18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'], [21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'], [25, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'], ]) # Verify the summarized data. self.assertTableData('GristSummary_7_Address3', cols="subset", data=[ [ "id", "state", "count", "amount" ], [ 1, "NY", 7, str(100*(1.+2+6+7+8+10+11)) ], [ 2, "WA", 1, "300.0" ], [ 3, "IL", 1, "400.0" ], [ 4, "MA", 2, str(500.+900) ], ]) #---------------------------------------------------------------------- def test_convert_source_column(self): # Verify that we can convert the type of a column when there is a summary table using that # column to group by. Since converting generates extra summary records, this may cause bugs. self.apply_user_action(["AddEmptyTable"]) self.apply_user_action(["BulkAddRecord", "Table1", [None]*3, {"A": [10,20,10], "B": [1,2,3]}]) self.apply_user_action(["CreateViewSection", 1, 0, "record", [2]]) # Verify metadata and actual data initially. self.assertTables([ Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[ Column(1, "manualSort", "ManualSortPos", False, "", 0), Column(2, "A", "Numeric", False, "", 0), Column(3, "B", "Numeric", False, "", 0), Column(4, "C", "Any", True, "", 0), ]), Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[ Column(5, "A", "Numeric", False, "", 2), Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0), Column(7, "count", "Int", True, "len($group)", 0), Column(8, "B", "Numeric", True, "SUM($group.B)", 0), ]) ]) self.assertTableData('Table1', data=[ [ "id", "manualSort", "A", "B", "C" ], [ 1, 1.0, 10, 1.0, None ], [ 2, 2.0, 20, 2.0, None ], [ 3, 3.0, 10, 3.0, None ], ]) self.assertTableData('GristSummary_6_Table1', data=[ [ "id", "A", "group", "count", "B" ], [ 1, 10, [1,3], 2, 4 ], [ 2, 20, [2], 1, 2 ], ]) # Do a conversion. self.apply_user_action(["UpdateRecord", "_grist_Tables_column", 2, {"type": "Text"}]) # Verify that the conversion's result is as expected. self.assertTables([ Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[ Column(1, "manualSort", "ManualSortPos", False, "", 0), Column(2, "A", "Text", False, "", 0), Column(3, "B", "Numeric", False, "", 0), Column(4, "C", "Any", True, "", 0), ]), Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[ Column(5, "A", "Text", False, "", 2), Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0), Column(7, "count", "Int", True, "len($group)", 0), Column(8, "B", "Numeric", True, "SUM($group.B)", 0), ]) ]) self.assertTableData('Table1', data=[ [ "id", "manualSort", "A", "B", "C" ], [ 1, 1.0, "10.0", 1.0, None ], [ 2, 2.0, "20.0", 2.0, None ], [ 3, 3.0, "10.0", 3.0, None ], ]) self.assertTableData('GristSummary_6_Table1', data=[ [ "id", "A", "group", "count", "B" ], [ 1, "10.0", [1,3], 2, 4 ], [ 2, "20.0", [2], 1, 2 ], ]) #---------------------------------------------------------------------- @test_engine.test_undo def test_remove_source_column(self): # Verify that we can remove a column when there is a summary table using that column to group # by. (Bug T188.) self.apply_user_action(["AddEmptyTable"]) self.apply_user_action(["BulkAddRecord", "Table1", [None]*3, {"A": ['a','b','c'], "B": [1,1,2], "C": [4,5,6]}]) self.apply_user_action(["CreateViewSection", 1, 0, "record", [2,3]]) # Verify metadata and actual data initially. self.assertTables([ Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[ Column(1, "manualSort", "ManualSortPos", False, "", 0), Column(2, "A", "Text", False, "", 0), Column(3, "B", "Numeric", False, "", 0), Column(4, "C", "Numeric", False, "", 0), ]), Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[ Column(5, "A", "Text", False, "", 2), Column(6, "B", "Numeric", False, "", 3), Column(7, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0), Column(8, "count", "Int", True, "len($group)", 0), Column(9, "C", "Numeric", True, "SUM($group.C)", 0), ]) ]) self.assertTableData('Table1', data=[ [ "id", "manualSort", "A", "B", "C" ], [ 1, 1.0, 'a', 1.0, 4 ], [ 2, 2.0, 'b', 1.0, 5 ], [ 3, 3.0, 'c', 2.0, 6 ], ]) self.assertTableData('GristSummary_6_Table1', data=[ [ "id", "A", "B", "group", "count", "C" ], [ 1, 'a', 1.0, [1], 1, 4 ], [ 2, 'b', 1.0, [2], 1, 5 ], [ 3, 'c', 2.0, [3], 1, 6 ], ]) # Remove column A, used for group-by. self.apply_user_action(["RemoveColumn", "Table1", "A"]) # Verify that the conversion's result is as expected. self.assertTables([ Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[ Column(1, "manualSort", "ManualSortPos", False, "", 0), Column(3, "B", "Numeric", False, "", 0), Column(4, "C", "Numeric", False, "", 0), ]), Table(3, "GristSummary_6_Table1_2", summarySourceTable=1, primaryViewId=0, columns=[ Column(10, "B", "Numeric", False, "", 3), Column(11, "count", "Int", True, "len($group)", 0), Column(12, "C", "Numeric", True, "SUM($group.C)", 0), Column(13, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0), ]) ]) self.assertTableData('Table1', data=[ [ "id", "manualSort", "B", "C" ], [ 1, 1.0, 1.0, 4 ], [ 2, 2.0, 1.0, 5 ], [ 3, 3.0, 2.0, 6 ], ]) self.assertTableData('GristSummary_6_Table1_2', data=[ [ "id", "B", "group", "count", "C" ], [ 1, 1.0, [1,2], 2, 9 ], [ 2, 2.0, [3], 1, 6 ], ])
def test_formula_reading_from_an_errored_formula(self): # There was a bug whereby if one formula (call it D) referred to # another (call it T), and that other formula was in error, the # error values of that second formula would not be passed on the # client as a BulkUpdateRecord. The bug was dependent on order of # evaluation of columns. D would be evaluated first, and evaluate # T in a nested way. When evaluating T, a BulkUpdateRecord would # be prepared correctly, and when popping back to evaluate D, # the BulkUpdateRecord for D would be prepared correctly, but since # D was an error, any nested actions would be reverted (this is # logic related to undoing potential side-effects on failure). # First, set up a table with a sequence in A, a formula to do cumulative sums in T, # and a formula D to copy T. formula = "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $A)" sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "UpdateTest", [ [20, "A", "Numeric", False, "", "", ""], [21, "T", "Numeric", True, formula, "", ""], [22, "D", "Numeric", True, "$T", "", ""], ] ]], "DATA": { "UpdateTest": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) # Check the setup is working correctly. self.load_sample(sample) self.assertTableData('UpdateTest', data=[ ['id', 'A', 'T', 'D'], [1, 1., 1., 1.], [2, 2., 3., 3.], [3, 3., 6., 6.], ]) # Now rename the data column. This rename results in a partial # update to the T formula that leaves it broken (not all the As are caught). out_actions = self.apply_user_action( ["RenameColumn", "UpdateTest", "A", "AA"]) # Make sure the we have bulk updates for both T and D, and not just D. err = ["E", "AttributeError"] self.assertPartialOutActions( out_actions, { "stored": [ ["RenameColumn", "UpdateTest", "A", "AA"], [ "ModifyColumn", "UpdateTest", "T", { "formula": "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $AA)" } ], [ "BulkUpdateRecord", "_grist_Tables_column", [20, 21], { "colId": ["AA", "T"], "formula": [ "", "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $AA)" ] } ], [ "BulkUpdateRecord", "UpdateTest", [1, 2, 3], { "D": [err, err, err] } ], [ "BulkUpdateRecord", "UpdateTest", [1, 2, 3], { "T": [err, err, err] } ], ] }) # Make sure the table is in the correct state. errVal = objtypes.RaisedException(AttributeError()) self.assertTableData('UpdateTest', data=[ ['id', 'AA', 'T', 'D'], [1, 1., errVal, errVal], [2, 2., errVal, errVal], [3, 3., errVal, errVal], ])
class TestErrorMessage(test_engine.EngineTestCase): syntax_err = \ """ if sum(3, 5) > 6: return 6 else: return: 0 """ indent_err = \ """ if sum(3, 5) > 6: return 6 """ sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Math", [ [11, "excel_formula", "Text", True, "SQRT(16, 2)", "", ""], [12, "built_in_formula", "Text", True, "max(5)", "", ""], [13, "syntax_err", "Text", True, syntax_err, "", ""], [14, "indent_err", "Text", True, indent_err, "", ""], [ 15, "other_err", "Text", True, textwrap.dedent(indent_err), "", "" ], [ 15, "custom_err", "Text", True, "raise Exception('hello')", "", "" ], ] ]], "DATA": { "Math": [ ["id"], [3], ] } }) def test_formula_errors(self): self.load_sample(self.sample) if six.PY2: self.assertFormulaError( self.engine.get_formula_error('Math', 'excel_formula', 3), TypeError, 'SQRT() takes exactly 1 argument (2 given)', r"TypeError: SQRT\(\) takes exactly 1 argument \(2 given\)") else: self.assertFormulaError( self.engine.get_formula_error('Math', 'excel_formula', 3), TypeError, 'SQRT() takes 1 positional argument but 2 were given', r"TypeError: SQRT\(\) takes 1 positional argument but 2 were given" ) self.assertFormulaError( self.engine.get_formula_error('Math', 'built_in_formula', 3), TypeError, "'int' object is not iterable", textwrap.dedent(r""" File "usercode", line \d+, in built_in_formula return max\(5\) TypeError: 'int' object is not iterable """)) self.assertFormulaError( self.engine.get_formula_error('Math', 'syntax_err', 3), SyntaxError, "invalid syntax (usercode, line 5)", textwrap.dedent(r""" File "usercode", line 5 return: 0 \^ SyntaxError: invalid syntax """)) if six.PY2: traceback_regex = textwrap.dedent(r""" File "usercode", line 2 if sum\(3, 5\) > 6: \^ IndentationError: unexpected indent """) else: traceback_regex = textwrap.dedent(r""" File "usercode", line 2 if sum\(3, 5\) > 6: IndentationError: unexpected indent """) self.assertFormulaError( self.engine.get_formula_error('Math', 'indent_err', 3), IndentationError, 'unexpected indent (usercode, line 2)', traceback_regex) self.assertFormulaError( self.engine.get_formula_error('Math', 'other_err', 3), TypeError, "'int' object is not iterable", textwrap.dedent(r""" File "usercode", line \d+, in other_err if sum\(3, 5\) > 6: TypeError: 'int' object is not iterable """)) self.assertFormulaError( self.engine.get_formula_error('Math', 'custom_err', 3), Exception, "hello") def test_lookup_state(self): # Bug https://phab.getgrist.com/T297 was caused by lookup maps getting corrupted while # re-evaluating a formula for the sake of getting error details. This test case reproduces the # bug in the old code and verifies that it is fixed. sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "LookupTest", [ [11, "A", "Numeric", False, "", "", ""], [ 12, "B", "Text", True, "LookupTest.lookupOne(A=2).x.upper()", "", "" ], ] ]], "DATA": { "LookupTest": [ ["id", "A"], [7, 2], ] } }) self.load_sample(sample) self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 2., objtypes.RaisedException(AttributeError())], ]) # Updating a dependency shouldn't cause problems. self.update_record('LookupTest', 7, A=3) self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 3., objtypes.RaisedException(AttributeError())], ]) # Fetch the error details. self.assertFormulaError( self.engine.get_formula_error('LookupTest', 'B', 7), AttributeError, "Table 'LookupTest' has no column 'x'") # Updating a dependency after the fetch used to cause the error # "AttributeError: 'Table' object has no attribute 'col_id'". Check that it's fixed. self.update_record('LookupTest', 7, A=2) # Should NOT raise an exception. self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 2., objtypes.RaisedException(AttributeError())], ]) # Add the column that will fix the attribute error. self.add_column('LookupTest', 'x', type='Text') self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 2., '', ''], ]) # And check that the dependency still works and is recomputed. self.update_record('LookupTest', 7, x='hello') self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 2., 'hello', 'HELLO'], ]) self.update_record('LookupTest', 7, A=3) self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 3., 'hello', ''], ]) def test_undo_side_effects(self): # Ensures that side-effects (i.e. generated doc actions) produced while evaluating # get_formula_errors() get reverted. sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Address", [ [11, "city", "Text", False, "", "", ""], [12, "state", "Text", False, "", "", ""], ] ], [ 2, "Foo", [ # Note: the formula below is a terrible example of a formula, which intentionally # creates a new record every time it evaluates. [ 21, "B", "Any", True, "Address.lookupOrAddDerived(city=str(len(Address.all)))", "", "" ], ] ] ], "DATA": { "Foo": [["id"], [1]] } }) self.load_sample(sample) self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ]) # Note that evaluating the formula again would add a new record (Address[2]), but when done as # part of get_formula_error(), that action gets undone. self.assertEqual(str(self.engine.get_formula_error('Foo', 'B', 1)), "Address[2]") self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ]) def test_formula_reading_from_an_errored_formula(self): # There was a bug whereby if one formula (call it D) referred to # another (call it T), and that other formula was in error, the # error values of that second formula would not be passed on the # client as a BulkUpdateRecord. The bug was dependent on order of # evaluation of columns. D would be evaluated first, and evaluate # T in a nested way. When evaluating T, a BulkUpdateRecord would # be prepared correctly, and when popping back to evaluate D, # the BulkUpdateRecord for D would be prepared correctly, but since # D was an error, any nested actions would be reverted (this is # logic related to undoing potential side-effects on failure). # First, set up a table with a sequence in A, a formula to do cumulative sums in T, # and a formula D to copy T. formula = "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $A)" sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "UpdateTest", [ [20, "A", "Numeric", False, "", "", ""], [21, "T", "Numeric", True, formula, "", ""], [22, "D", "Numeric", True, "$T", "", ""], ] ]], "DATA": { "UpdateTest": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) # Check the setup is working correctly. self.load_sample(sample) self.assertTableData('UpdateTest', data=[ ['id', 'A', 'T', 'D'], [1, 1., 1., 1.], [2, 2., 3., 3.], [3, 3., 6., 6.], ]) # Now rename the data column. This rename results in a partial # update to the T formula that leaves it broken (not all the As are caught). out_actions = self.apply_user_action( ["RenameColumn", "UpdateTest", "A", "AA"]) # Make sure the we have bulk updates for both T and D, and not just D. err = ["E", "AttributeError"] self.assertPartialOutActions( out_actions, { "stored": [ ["RenameColumn", "UpdateTest", "A", "AA"], [ "ModifyColumn", "UpdateTest", "T", { "formula": "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $AA)" } ], [ "BulkUpdateRecord", "_grist_Tables_column", [20, 21], { "colId": ["AA", "T"], "formula": [ "", "recs = UpdateTest.lookupRecords()\nsum(r.A for r in recs if r.A <= $AA)" ] } ], [ "BulkUpdateRecord", "UpdateTest", [1, 2, 3], { "D": [err, err, err] } ], [ "BulkUpdateRecord", "UpdateTest", [1, 2, 3], { "T": [err, err, err] } ], ] }) # Make sure the table is in the correct state. errVal = objtypes.RaisedException(AttributeError()) self.assertTableData('UpdateTest', data=[ ['id', 'AA', 'T', 'D'], [1, 1., errVal, errVal], [2, 2., errVal, errVal], [3, 3., errVal, errVal], ]) def test_undo_side_effects_with_reordering(self): # As for test_undo_side_effects, but now after creating a row in a # formula we try to access a cell that hasn't been recomputed yet. # That will result in the formula evalution being abandoned, the # desired cell being calculated, then the formula being retried. # All going well, we should end up with one row, not two. sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Address", [ [11, "city", "Text", False, "", "", ""], [12, "state", "Text", False, "", "", ""], ] ], [ 2, "Foo", [ # Note: the formula below is a terrible example of a formula, which intentionally # creates a new record every time it evaluates. [ 21, "B", "Any", True, "Address.lookupOrAddDerived(city=str(len(Address.all)))\nreturn $C", "", "" ], [22, "C", "Numeric", True, "42", "", ""], ] ] ], "DATA": { "Foo": [["id"], [1]] } }) self.load_sample(sample) self.assertTableData('Address', data=[ ['id', 'city', 'state'], [1, '0', ''], ]) def test_attribute_error(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "AttrTest", [ [30, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$AA", "", ""], [32, "C", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "AttrTest": [ ["id", "A"], [1, 1], [2, 2], ] } }) self.load_sample(sample) errVal = objtypes.RaisedException(AttributeError()) self.assertTableData('AttrTest', data=[ ['id', 'A', 'B', 'C'], [1, 1, errVal, errVal], [2, 2, errVal, errVal], ]) self.assertFormulaError( self.engine.get_formula_error('AttrTest', 'B', 1), AttributeError, "Table 'AttrTest' has no column 'AA'", r"AttributeError: Table 'AttrTest' has no column 'AA'") cell_error = self.engine.get_formula_error('AttrTest', 'C', 1) self.assertFormulaError( cell_error, objtypes.CellError, "AttributeError in referenced cell AttrTest[1].B", r"CellError: AttributeError in referenced cell AttrTest\[1\].B") self.assertEqual(objtypes.encode_object(cell_error), [ 'E', 'AttributeError', "Table 'AttrTest' has no column 'AA'\n" "(in referenced cell AttrTest[1].B)", cell_error.details ]) def test_cumulative_formula(self): formula = ( "Table1.lookupOne(A=$A-1).Principal + Table1.lookupOne(A=$A-1).Interest " + "if $A > 1 else 1000") sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, formula, "", ""], [ 32, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], [4, 4], [5, 5], ] } }) self.load_sample(sample) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest'], [1, 1, 1000.0, 100.0], [2, 2, 1100.0, 110.0], [3, 3, 1210.0, 121.0], [4, 4, 1331.0, 133.0], [5, 5, 1464.0, 146.0], ]) self.update_records('Table1', ['id', 'A'], [[1, 5], [2, 3], [3, 4], [4, 2], [5, 1]]) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest'], [1, 5, 1464.0, 146.0], [2, 3, 1210.0, 121.0], [3, 4, 1331.0, 133.0], [4, 2, 1100.0, 110.0], [5, 1, 1000.0, 100.0], ]) def test_trivial_cycle(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'B'], [1, 1, circle], [2, 2, circle], [3, 3, circle], ]) def test_cycle(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, "$Interest", "", ""], [32, "Interest", "Numeric", True, "$Principal", "", ""], [33, "A2", "Numeric", True, "$A", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'Principal', 'Interest', 'A2'], [1, 1, circle, circle, 1], [2, 2, circle, circle, 2], [3, 3, circle, circle, 3], ]) def test_cycle_and_copy(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$C", "", ""], [32, "C", "Numeric", True, "$C", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'B', 'C'], [1, 1, circle, circle], [2, 2, circle, circle], [3, 3, circle, circle], ]) def test_cycle_and_reference(self): sample = testutil.parse_test_sample({ "SCHEMA": [ [ 2, "ATable", [ [32, "A", "Ref:ZTable", False, "", "", ""], [33, "B", "Numeric", True, "$A.B", "", ""], ] ], [ 1, "ZTable", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$B", "", ""], ] ], ], "DATA": { "ATable": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ], "ZTable": [ ["id", "A"], [1, 6], [2, 7], [3, 8], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('ATable', data=[ ['id', 'A', 'B'], [1, 1, circle], [2, 2, circle], [3, 3, circle], ]) self.assertTableData('ZTable', data=[ ['id', 'A', 'B'], [1, 6, circle], [2, 7, circle], [3, 8, circle], ]) def test_cumulative_efficiency(self): # Make sure cumulative formula evaluation doesn't fall over after more than a few rows. top = 250 # Compute compound interest in ascending order of A formula = ( "Table1.lookupOne(A=$A-1).Principal + Table1.lookupOne(A=$A-1).Interest " + "if $A > 1 else 1000") # Compute compound interest in descending order of A rformula = ( "Table1.lookupOne(A=$A+1).RPrincipal + Table1.lookupOne(A=$A+1).RInterest " + "if $A < %d else 1000" % top) rows = [["id", "A"]] for i in range(1, top + 1): rows.append([i, i]) sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [30, "A", "Numeric", False, "", "", ""], [31, "Principal", "Numeric", True, formula, "", ""], [ 32, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], [33, "RPrincipal", "Numeric", True, rformula, "", ""], [ 34, "RInterest", "Numeric", True, "int($RPrincipal * 0.1)", "", "" ], [ 35, "Total", "Numeric", True, "$Principal + $RPrincipal", "", "" ], ] ], [ 2, "Readout", [ [ 36, "LastPrincipal", "Numeric", True, "Table1.lookupOne(A=%d).Principal" % top, "", "" ], [ 37, "LastRPrincipal", "Numeric", True, "Table1.lookupOne(A=1).RPrincipal", "", "" ], [ 38, "FirstTotal", "Numeric", True, "Table1.lookupOne(A=1).Total", "", "" ], [ 39, "LastTotal", "Numeric", True, "Table1.lookupOne(A=%d).Total" % top, "", "" ], ] ]], "DATA": { "Table1": rows, "Readout": [["id"], [1]], } }) self.load_sample(sample) principal = 20213227788876.0 self.assertTableData( 'Readout', data=[ [ 'id', 'LastPrincipal', 'LastRPrincipal', 'FirstTotal', 'LastTotal' ], [1, principal, principal, principal + 1000, principal + 1000], ]) def test_cumulative_formula_with_references(self): top = 100 formula = "max($Prev.Principal + $Prev.Interest, 1000)" sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [41, "Prev", "Ref:Table1", True, "$id - 1", "", ""], [42, "Principal", "Numeric", True, formula, "", ""], [ 43, "Interest", "Numeric", True, "int($Principal * 0.1)", "", "" ], ] ], [ 2, "Readout", [ [ 46, "LastPrincipal", "Numeric", True, "Table1.lookupOne(id=%d).Principal" % top, "", "" ], ] ]], "DATA": { "Table1": [["id"]] + [[r] for r in range(1, top + 1)], "Readout": [["id"], [1]], } }) self.load_sample(sample) self.assertTableData('Readout', data=[ ['id', 'LastPrincipal'], [1, 12494908.0], ]) self.modify_column("Table1", "Prev", formula="$id - 1 if $id > 1 else 100") self.assertTableData( 'Readout', data=[ ['id', 'LastPrincipal'], [1, objtypes.RaisedException(depend.CircularRefError())], ]) def test_catch_all_in_formula(self): sample = testutil.parse_test_sample({ "SCHEMA": [ [ 1, "Table1", [ [51, "A", "Numeric", False, "", "", ""], [ 52, "B1", "Numeric", True, "try:\n return $A+$C\nexcept:\n return 42", "", "" ], [ 53, "B2", "Numeric", True, "try:\n return $D+None\nexcept:\n return 42", "", "" ], [ 54, "B3", "Numeric", True, "try:\n return $A+$B4+$D\nexcept:\n return 42", "", "" ], [ 55, "B4", "Numeric", True, "try:\n return $A+$B3+$D\nexcept:\n return 42", "", "" ], [ 56, "B5", "Numeric", True, "try:\n return $E+1\nexcept:\n raise Exception('monkeys!')", "", "" ], [ 56, "B6", "Numeric", True, "try:\n return $F+1\nexcept Exception as e:\n e.node = e.row_id = 'monkey'", "", "" ], [57, "C", "Numeric", False, "", "", ""], [58, "D", "Numeric", True, "$A", "", ""], [59, "E", "Numeric", True, "$A", "", ""], [59, "F", "Numeric", True, "$A", "", ""], ] ], ], "DATA": { "Table1": [["id", "A", "C"], [1, 1, 2], [2, 20, 10]], } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) # B4 is a subtle case. B3 and B4 refer to each other. B3 is recomputed first, # and cells evaluate to a CircularRefError. Now B3 has a value, so B4 can be # evaluated, and results in 42 when addition of an integer and an exception value # fails. self.assertTableData( 'Table1', data=[ [ 'id', 'A', 'B1', 'B2', 'B3', 'B4', 'B5', 'B6', 'C', 'D', 'E', 'F' ], [1, 1, 3, 42, circle, 42, 2, 2, 2, 1, 1, 1], [2, 20, 30, 42, circle, 42, 21, 21, 10, 20, 20, 20], ]) def test_reference_column(self): # There was a bug where self-references could result in a column being prematurely # considered complete. sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [[40, "Ident", "Text", False, "", "", ""], [41, "Prev", "Ref:Table1", False, "", "", ""], [ 42, "Calc", "Numeric", True, "$Prev.Calc * 1.5 if $Prev else 1", "", "" ]] ]], "DATA": { "Table1": [ ['id', 'Ident', 'Prev'], [1, 'a', 0], [2, 'b', 1], [3, 'c', 4], [4, 'd', 0], ] } }) self.load_sample(sample) self.assertTableData('Table1', data=[['id', 'Ident', 'Prev', 'Calc'], [1, 'a', 0, 1.0], [2, 'b', 1, 1.5], [3, 'c', 4, 1.5], [4, 'd', 0, 1.0]]) def test_loop(self): sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "Table1", [ [31, "A", "Numeric", False, "", "", ""], [31, "B", "Numeric", True, "$C", "", ""], [32, "C", "Numeric", True, "$B", "", ""], ] ]], "DATA": { "Table1": [ ["id", "A"], [1, 1], [2, 2], [3, 3], ] } }) self.load_sample(sample) circle = objtypes.RaisedException(depend.CircularRefError()) self.assertTableData('Table1', data=[ ['id', 'A', 'B', 'C'], [1, 1, circle, circle], [2, 2, circle, circle], [3, 3, circle, circle], ])
def test_lookup_state(self): # Bug https://phab.getgrist.com/T297 was caused by lookup maps getting corrupted while # re-evaluating a formula for the sake of getting error details. This test case reproduces the # bug in the old code and verifies that it is fixed. sample = testutil.parse_test_sample({ "SCHEMA": [[ 1, "LookupTest", [ [11, "A", "Numeric", False, "", "", ""], [ 12, "B", "Text", True, "LookupTest.lookupOne(A=2).x.upper()", "", "" ], ] ]], "DATA": { "LookupTest": [ ["id", "A"], [7, 2], ] } }) self.load_sample(sample) self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 2., objtypes.RaisedException(AttributeError())], ]) # Updating a dependency shouldn't cause problems. self.update_record('LookupTest', 7, A=3) self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 3., objtypes.RaisedException(AttributeError())], ]) # Fetch the error details. self.assertFormulaError( self.engine.get_formula_error('LookupTest', 'B', 7), AttributeError, "Table 'LookupTest' has no column 'x'") # Updating a dependency after the fetch used to cause the error # "AttributeError: 'Table' object has no attribute 'col_id'". Check that it's fixed. self.update_record('LookupTest', 7, A=2) # Should NOT raise an exception. self.assertTableData( 'LookupTest', data=[ ['id', 'A', 'B'], [7, 2., objtypes.RaisedException(AttributeError())], ]) # Add the column that will fix the attribute error. self.add_column('LookupTest', 'x', type='Text') self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 2., '', ''], ]) # And check that the dependency still works and is recomputed. self.update_record('LookupTest', 7, x='hello') self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 2., 'hello', 'HELLO'], ]) self.update_record('LookupTest', 7, A=3) self.assertTableData('LookupTest', data=[ ['id', 'A', 'x', 'B'], [7, 3., 'hello', ''], ])