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', ''],
])
