You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
735 lines
33 KiB
735 lines
33 KiB
import copy
|
|
import logging
|
|
import time
|
|
|
|
import six
|
|
|
|
import objtypes
|
|
import testutil
|
|
import test_engine
|
|
from schema import RecalcWhen
|
|
|
|
# pylint: disable=line-too-long
|
|
|
|
log = logging.getLogger(__name__)
|
|
|
|
def column_error(table, column, user_input):
|
|
return objtypes.RaisedException(
|
|
AttributeError("Table '%s' has no column '%s'" % (table, column)),
|
|
user_input=user_input
|
|
)
|
|
div_error = lambda value: objtypes.RaisedException(ZeroDivisionError("float division by zero"), user_input=value)
|
|
|
|
class TestTriggerFormulas(test_engine.EngineTestCase):
|
|
col = testutil.col_schema_row
|
|
sample_desc = {
|
|
"SCHEMA": [
|
|
[1, "Creatures", [
|
|
col(1, "Name", "Text", False),
|
|
col(2, "Ocean", "Ref:Oceans", False),
|
|
col(3, "OceanName", "Text", True, "$Ocean.Name"),
|
|
col(4, "BossDef", "Text", False, "$Ocean.Head"),
|
|
col(5, "BossNvr", "Text", False, "$Ocean.Head", recalcWhen=RecalcWhen.NEVER),
|
|
col(6, "BossUpd", "Text", False, "$Ocean.Head", recalcDeps=[2]),
|
|
col(7, "BossAll", "Text", False, "$Ocean.Head", recalcWhen=RecalcWhen.MANUAL_UPDATES),
|
|
]],
|
|
[2, "Oceans", [
|
|
col(11, "Name", "Text", False),
|
|
col(12, "Head", "Text", False)
|
|
]],
|
|
],
|
|
"DATA": {
|
|
"Creatures": [
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur"],
|
|
],
|
|
"Oceans": [
|
|
["id", "Name", "Head"],
|
|
[1, "Pacific", "Watatsumi"],
|
|
[2, "Atlantic", "Poseidon"],
|
|
[3, "Indian", "Neptune"],
|
|
[4, "Arctic", "Poseidon"],
|
|
],
|
|
}
|
|
}
|
|
sample = testutil.parse_test_sample(sample_desc)
|
|
|
|
def test_no_recalc_on_load(self):
|
|
# Trigger formulas don't affect data that's loaded.
|
|
self.load_sample(self.sample)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
def test_recalc_on_new_records(self):
|
|
# Trigger formulas affect new records.
|
|
self.load_sample(self.sample)
|
|
self.add_record("Creatures", Name="Shark", Ocean=2)
|
|
self.add_record("Creatures", Name="Squid", Ocean=1)
|
|
|
|
# Check that BossNvr ("never") wasn't affected by the default formula, but the rest were.
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
[2, "Shark", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic" ],
|
|
[3, "Squid", 1, "Watatsumi", "", "Watatsumi", "Watatsumi", "Pacific" ],
|
|
])
|
|
|
|
def test_no_recalc_on_noop_change(self):
|
|
# A no-op change shouldn't trigger any updates.
|
|
self.load_sample(self.sample)
|
|
self.update_record("Creatures", 1, Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
def test_recalc_on_update(self):
|
|
# Changes should trigger recalc of certain trigger formulas.
|
|
self.load_sample(self.sample)
|
|
self.add_record("Creatures", Name="Shark", Ocean=2)
|
|
self.add_record("Creatures", Name="Squid", Ocean=1)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
[2, "Shark", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic" ],
|
|
[3, "Squid", 1, "Watatsumi", "", "Watatsumi", "Watatsumi", "Pacific" ],
|
|
])
|
|
self.update_records("Creatures", ["id", "Ocean"], [
|
|
[1, 3], # Ocean for 1: Atlantic -> Indian
|
|
[3, 4], # Ocean for 3: Pacific -> Arctic
|
|
])
|
|
# Only BossUpd and BossAll columns should be affected, not BossDef or BossNvr
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
[2, "Shark", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic"],
|
|
[3, "Squid", 4, "Watatsumi", "", "Poseidon", "Poseidon", "Arctic" ],
|
|
])
|
|
|
|
def test_recalc_with_direct_update(self):
|
|
# Check that an update that changes both a dependency and the trigger-formula column itself
|
|
# respects the latter value.
|
|
self.load_sample(self.sample)
|
|
|
|
out_actions = self.update_record("Creatures", 1, Ocean=3, BossUpd="Bob")
|
|
self.assertTableData("Creatures", rows="subset", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Bob", "Neptune", "Indian" ],
|
|
])
|
|
# Check that the needed recalcs are the only ones that happened.
|
|
self.assertPartialOutActions(out_actions, {
|
|
"calls": {"Creatures": {"BossAll": 1, "OceanName": 1}}
|
|
})
|
|
|
|
out_actions = self.update_record("Creatures", 1, Ocean=4, BossUpd="", BossAll="Chuck")
|
|
self.assertTableData("Creatures", rows="subset", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 4, "Arthur", "Arthur", "", "Chuck", "Arctic" ],
|
|
])
|
|
# Check that the needed recalcs are the only ones that happened.
|
|
self.assertPartialOutActions(out_actions, {
|
|
"calls": {"Creatures": {"OceanName": 1}}
|
|
})
|
|
|
|
def test_no_recalc_on_reopen(self):
|
|
# Change that a reopen does not recalc at all.
|
|
|
|
# Load a sample with a few more rows. Only the one true formula should be calculated
|
|
sample_desc = copy.deepcopy(self.sample_desc)
|
|
sample_desc["DATA"]["Creatures"] = [
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll" ],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur" ],
|
|
[2, "Shark", 2, "", "", "Poseidon", "Poseidon"],
|
|
[3, "Squid", 4, "Watatsumi", "", "Poseidon", "" ],
|
|
]
|
|
sample = testutil.parse_test_sample(sample_desc)
|
|
|
|
self.assertEqual(self.call_counts, {})
|
|
self.load_sample(sample)
|
|
self.assertEqual(self.call_counts, {
|
|
'Creatures': {'#lookup#': 3, 'OceanName': 3},
|
|
'Oceans': {'#lookup#': 4},
|
|
})
|
|
|
|
|
|
def test_recalc_undo(self):
|
|
self.load_sample(self.sample)
|
|
data0 = [
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
]
|
|
self.assertTableData("Creatures", data=data0)
|
|
|
|
# Plain update
|
|
out_actions1 = self.update_record("Creatures", 1, Ocean=1)
|
|
data1 = [
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 1, "Arthur", "Arthur", "Watatsumi", "Watatsumi", "Pacific" ],
|
|
]
|
|
self.assertTableData("Creatures", data=data1)
|
|
self.assertEqual(out_actions1.calls, {"Creatures": {"BossUpd": 1, "BossAll": 1, "OceanName": 1}})
|
|
|
|
# Update with a manual update to one of the trigger columns
|
|
out_actions2 = self.update_record("Creatures", 1, Ocean=3, BossUpd="Bob")
|
|
data2 = [
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Bob", "Neptune", "Indian" ],
|
|
]
|
|
self.assertTableData("Creatures", rows="subset", data=data2)
|
|
self.assertEqual(out_actions2.calls, {"Creatures": {"BossAll": 1, "OceanName": 1}})
|
|
|
|
# Undo, one at a time. It should not cause recalc of trigger columns, because an undo sets
|
|
# those explicitly.
|
|
out_actions2_undo = self.apply_undo_actions(out_actions2.undo)
|
|
self.assertTableData("Creatures", data=data1)
|
|
self.assertEqual(out_actions2_undo.calls, {"Creatures": {"OceanName": 1}})
|
|
|
|
out_actions1_undo = self.apply_undo_actions(out_actions1.undo)
|
|
self.assertTableData("Creatures", data=data0)
|
|
self.assertEqual(out_actions1_undo.calls, {"Creatures": {"OceanName": 1}})
|
|
|
|
|
|
def test_recalc_triggers(self):
|
|
# A trigger that depends on some columns should not be triggered by other ones.
|
|
self.load_sample(self.sample)
|
|
|
|
# BossUpd and BossAll both depend on the "Ocean" column, so both get updated.
|
|
out_actions = self.update_record("Creatures", 1, Ocean=3)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossUpd": 1, "BossAll": 1, "OceanName": 1}})
|
|
|
|
# Undo, then check that a change that doesn't touch Ocean only triggers BossAll recalc.
|
|
self.apply_undo_actions(out_actions.undo)
|
|
out_actions = self.update_record("Creatures", 1, Name="Whale")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Whale", 2, "Arthur", "Arthur", "Arthur", "Poseidon", "Atlantic" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossAll": 1}})
|
|
|
|
|
|
def test_recalc_trigger_changes(self):
|
|
# After changing a trigger formula dependencies, changes to the old dependency should no
|
|
# longer cause a recalc.
|
|
self.load_sample(self.sample)
|
|
|
|
# Change column BossUpd to depend on column Name rather than on column Ocean.
|
|
self.update_record("_grist_Tables_column", 6, recalcDeps=['L', 1])
|
|
|
|
# Make a change to Ocean. It should not cause an update to BossUpd, only BossAll.
|
|
out_actions = self.update_record("Creatures", 1, Ocean=3)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Arthur", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossAll": 1, "OceanName": 1}})
|
|
|
|
# But changes to the new dependency should trigger recalc.
|
|
out_actions = self.update_record("Creatures", 1, Name="Whale")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossUpd": 1, "BossAll": 1}})
|
|
|
|
# If dependencies are changed to empty, only new records should cause BossUpd recalc.
|
|
self.update_record("_grist_Tables_column", 6, recalcDeps=['L'])
|
|
out_actions = self.update_record("Creatures", 1, Name="Porpoise", Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Porpoise", 2, "Arthur", "Arthur", "Neptune", "Poseidon", "Atlantic" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossAll": 1, "OceanName": 1}})
|
|
|
|
out_actions = self.add_record("Creatures", None, Name="Manatee", Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Porpoise", 2, "Arthur", "Arthur", "Neptune", "Poseidon", "Atlantic" ],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic" ],
|
|
])
|
|
self.assertEqual(out_actions.calls,
|
|
{"Creatures": {"BossDef": 1, "BossUpd": 1, "BossAll": 1, "OceanName": 1, "#lookup#": 1}})
|
|
|
|
|
|
def test_recalc_trigger_off(self):
|
|
# Change BossUpd dependency to never, and check that neither changes nor new records cause
|
|
# recalc.
|
|
self.load_sample(self.sample)
|
|
self.update_record("_grist_Tables_column", 6, recalcWhen=RecalcWhen.NEVER)
|
|
|
|
# Check a change
|
|
out_actions = self.update_record("Creatures", 1, Name="Whale", Ocean=3)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Arthur", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossAll": 1, "OceanName": 1}})
|
|
|
|
# Check a new record -- doesn't affect BossUpd any more.
|
|
out_actions = self.add_record("Creatures", None, Name="Manatee", Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Arthur", "Neptune", "Indian" ],
|
|
[2, "Manatee", 2, "Poseidon", "", "", "Poseidon", "Atlantic" ],
|
|
])
|
|
self.assertEqual(out_actions.calls,
|
|
{"Creatures": {"BossDef": 1, "BossAll": 1, "OceanName": 1, "#lookup#": 1}})
|
|
|
|
|
|
def test_renames(self):
|
|
# After renaming tables or columns, trigger formulas should still be triggered the same way.
|
|
self.load_sample(self.sample)
|
|
|
|
# Do some renamings: they shouldn't trigger updates to trigger formulas.
|
|
self.apply_user_action(["RenameColumn", "Creatures", "Ocean", "Sea"])
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
self.apply_user_action(["RenameColumn", "Creatures", "BossUpd", "foo"])
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "foo", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
self.apply_user_action(["RenameTable", "Creatures", "Critters"])
|
|
self.assertTableData("Critters", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "foo", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
self.apply_user_action(["RenameColumn", "Critters", "BossAll", "bar"])
|
|
self.assertTableData("Critters", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "foo", "bar", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" ],
|
|
])
|
|
|
|
# After renames, correct trigger formulas continue getting triggered.
|
|
out_actions = self.update_record("Critters", 1, Sea=3)
|
|
self.assertTableData("Critters", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "foo", "bar", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Critters": {"foo": 1, "bar": 1, "OceanName": 1}})
|
|
|
|
# After renames, changes shouldn't trigger unnecessary recalcs (foo, formerly BossUpd, should
|
|
# not be triggered by a change to Name).
|
|
out_actions = self.update_record("Critters", 1, Name="Whale")
|
|
self.assertTableData("Critters", data=[
|
|
["id","Name", "Sea", "BossDef", "BossNvr", "foo", "bar", "OceanName"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Critters": {"bar": 1}})
|
|
|
|
|
|
def test_schema_changes(self):
|
|
# Schema changes like add/modify column should not cause trigger-formulas to recalculate.
|
|
self.load_sample(self.sample)
|
|
|
|
# Adding a column doesn't trigger recalcs.
|
|
out_actions = self.apply_user_action(["AddColumn", "Creatures", "Size", {"type": "Text", "isFormula": False}])
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Size"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic", ""],
|
|
])
|
|
self.assertEqual(out_actions.calls, {})
|
|
|
|
# Only BossAll should recalc since the record changed.
|
|
out_actions = self.update_record("Creatures", 1, Size="Big")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Size"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Poseidon", "Atlantic", "Big"],
|
|
])
|
|
self.assertEqual(out_actions.calls, {"Creatures": {"BossAll": 1}})
|
|
|
|
# New records trigger recalc as usual.
|
|
out_actions = self.add_record("Creatures", None, Name="Manatee", Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Size"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Poseidon", "Atlantic", "Big"],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic", ""],
|
|
])
|
|
|
|
# ModifyColumn doesn't trigger recalcs.
|
|
out_actions = self.apply_user_action(["ModifyColumn", "Creatures", "Size", {"type": 'Numeric'}])
|
|
self.assertEqual(out_actions.calls, {})
|
|
|
|
|
|
def test_changing_trigger_formula(self):
|
|
self.load_sample(self.sample)
|
|
|
|
# Modifying trigger formula doesn't trigger recalc.
|
|
out_actions = self.apply_user_action(["ModifyColumn", "Creatures", "BossAll", {"formula": 'UPPER($Ocean.Head)'}])
|
|
self.assertEqual(out_actions.calls, {})
|
|
|
|
# But when it runs, recalc uses the new formula.
|
|
out_actions = self.update_record("Creatures", 1, Name="Whale")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Whale", 2, "Arthur", "Arthur", "Arthur", "POSEIDON", "Atlantic" ],
|
|
])
|
|
|
|
|
|
def test_remove_dependency(self):
|
|
# Remove a dependency column, and check that recalcDeps list is updated.
|
|
self.load_sample(self.sample)
|
|
|
|
def get_recalc_deps(col_ref):
|
|
data = self.engine.fetch_table('_grist_Tables_column', col_ref, query={'id': [col_ref]})
|
|
return data.columns['recalcDeps'][0]
|
|
|
|
self.assertEqual(get_recalc_deps(6), [2])
|
|
|
|
# Add another dependency, so that we can test partial removal.
|
|
self.update_record("_grist_Tables_column", 6, recalcDeps=['L', 2, 3])
|
|
self.assertEqual(get_recalc_deps(6), [2, 3])
|
|
|
|
# Remove a column that it's a Dependency of BossUpd
|
|
self.apply_user_action(["RemoveColumn", "Creatures", "Ocean"])
|
|
self.assertEqual(get_recalc_deps(6), [3])
|
|
self.apply_user_action(["RemoveColumn", "Creatures", "OceanName"])
|
|
self.assertEqual(get_recalc_deps(6), None)
|
|
|
|
# None of these operations should have changed trigger-formula columns.
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "BossDef", "BossNvr", "BossUpd", "BossAll"],
|
|
[1, "Dolphin", "Arthur", "Arthur", "Arthur", "Arthur" ],
|
|
])
|
|
|
|
# Check that it still responds to suitable triggers.
|
|
# Make a change to some other column. BossUpd doesn't get updated.
|
|
out_actions = self.update_record("Creatures", 1, Name="Whale")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "BossDef", "BossNvr", "BossUpd", "BossAll" ],
|
|
[1, "Whale", "Arthur", "Arthur", "Arthur", column_error("Creatures", "Ocean", "Arthur")],
|
|
])
|
|
|
|
# Add a record. BossUpd's formula still runs, though with an error.
|
|
no_column = column_error("Creatures", "Ocean", "")
|
|
no_column_value = column_error("Creatures", "Ocean", "Arthur")
|
|
out_actions = self.add_record("Creatures", None, Name="Manatee")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "BossDef", "BossNvr", "BossUpd", "BossAll" ],
|
|
[1, "Whale", "Arthur", "Arthur", "Arthur", no_column_value],
|
|
[2, "Manatee", no_column, "", no_column, no_column],
|
|
])
|
|
|
|
|
|
def test_no_trigger_by_formulas(self):
|
|
# A column that depends on any record update ("allupdates") should not be affected by formula
|
|
# recalculations.
|
|
self.load_sample(self.sample)
|
|
|
|
# Name of Ocean affects a formula column; Head affects calculation; neither triggers recalc.
|
|
self.update_record('Oceans', 2, Head="POSEIDON", Name="ATLANTIC")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "ATLANTIC" ],
|
|
])
|
|
self.add_record("Creatures", None, Name="Manatee", Ocean=2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "ATLANTIC" ],
|
|
[2, "Manatee", 2, "POSEIDON", "", "POSEIDON", "POSEIDON", "ATLANTIC" ],
|
|
])
|
|
|
|
# On the other hand, an explicit dependency on a formula column WILL be triggered.
|
|
self.update_record("_grist_Tables_column", 6, recalcDeps=['L', 2, 3])
|
|
self.update_record('Oceans', 2, Name="atlantic")
|
|
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "POSEIDON", "Arthur", "atlantic" ],
|
|
[2, "Manatee", 2, "POSEIDON", "", "POSEIDON", "POSEIDON", "atlantic" ],
|
|
])
|
|
|
|
|
|
def test_no_auto_dependencies(self):
|
|
# Evaluating a trigger formula should not create dependencies on cells used during
|
|
# evaluation.
|
|
self.load_sample(self.sample)
|
|
self.update_record("Creatures", 1, Ocean=3)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
# Update a value that trigger-cells used during calculation; it should not cause a recalc.
|
|
self.update_record('Oceans', 3, Head="NEPTUNE")
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian" ],
|
|
])
|
|
|
|
|
|
def test_self_trigger(self):
|
|
# A trigger formula may be triggered by changes to the column itself.
|
|
# Check that it gets recalculated.
|
|
sample_desc = copy.deepcopy(self.sample_desc)
|
|
creatures_table = sample_desc["SCHEMA"][0]
|
|
creatures_columns = creatures_table[-1]
|
|
|
|
# Set BossUpd column to depend on Ocean and itself.
|
|
# Append something to ensure we are testing a case without a fixed point, to ensure
|
|
# that doesn't cause an infinite update loop.
|
|
self.assertEqual(creatures_columns[5][1], "BossUpd")
|
|
creatures_columns[5] = testutil.col_schema_row(
|
|
6, "BossUpd", "Text", False, "UPPER(value or $Ocean.Head) + '+'", recalcDeps=[2, 6]
|
|
)
|
|
|
|
# Previously there were various bugs with trigger formulas in columns involved in lookups:
|
|
# 1. They did not recalculate their trigger formulas after changes to themselves
|
|
# 2. They calculated the formula twice for new records
|
|
# 3. The lookups returned incorrect results
|
|
creatures_columns.append(testutil.col_schema_row(
|
|
21, "Lookup", "Any", True, "Creatures.lookupRecords(BossUpd=$BossUpd).id"
|
|
))
|
|
|
|
sample = testutil.parse_test_sample(sample_desc)
|
|
self.load_sample(sample)
|
|
|
|
self.assertTableData("Creatures", cols="subset", data=[
|
|
["id","Name", "Ocean", "BossDef","BossNvr", "BossUpd", "BossAll", "OceanName", "Lookup"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic" , [1]],
|
|
])
|
|
|
|
self.update_record('Creatures', 1, Ocean=3)
|
|
self.assertTableData("Creatures", cols="subset", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Lookup"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "ARTHUR+", "Neptune", "Indian" , [1]],
|
|
])
|
|
self.update_record('Creatures', 1, BossUpd="None")
|
|
self.assertTableData("Creatures", cols="subset", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Lookup"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "NONE+", "Neptune", "Indian" , [1]],
|
|
])
|
|
self.update_record('Creatures', 1, BossUpd="")
|
|
self.assertTableData("Creatures", cols="subset", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "Lookup"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "NEPTUNE+","Neptune", "Indian" , [1]],
|
|
])
|
|
|
|
# Ensuring trigger formula isn't called twice for new records
|
|
self.add_record('Creatures', BossUpd="Zeus")
|
|
self.assertTableData("Creatures", cols="subset", rows="subset", data=[
|
|
["id", "BossUpd", "Lookup"],
|
|
[2, "ZEUS+" , [2]],
|
|
])
|
|
|
|
|
|
def test_last_update_recipe(self):
|
|
# Use a formula to store time of last-update. Check that it works as expected.
|
|
# Check that times don't update on reload.
|
|
self.load_sample(self.sample)
|
|
self.add_column('Creatures', 'LastChange',
|
|
type='DateTime:UTC', isFormula=False, formula="NOW()", recalcWhen=RecalcWhen.MANUAL_UPDATES)
|
|
|
|
# To compare times, use actual times after checking approximately.
|
|
now = time.time()
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastChange"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic", None],
|
|
])
|
|
|
|
self.add_record("Creatures", None, Name="Manatee", Ocean=2)
|
|
self.update_record("Creatures", 1, Ocean=3)
|
|
|
|
now = time.time()
|
|
[time1, time2] = self.engine.fetch_table('Creatures').columns['LastChange']
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastChange"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian", time1],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic", time2],
|
|
])
|
|
self.assertLessEqual(abs(time1 - now), 1)
|
|
self.assertLessEqual(abs(time2 - now), 1)
|
|
|
|
# An indirect change doesn't affect the time, but a direct change does.
|
|
self.update_record("Oceans", 2, Name="ATLANTIC")
|
|
self.update_record("Creatures", 1, Name="Whale")
|
|
[time3, time4] = self.engine.fetch_table('Creatures').columns['LastChange']
|
|
self.assertGreater(time3, time1)
|
|
self.assertEqual(time4, time2)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastChange"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian", time3],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "ATLANTIC", time2],
|
|
])
|
|
|
|
def test_last_modified_by_recipe(self):
|
|
user1 = {
|
|
'Name': 'Foo Bar',
|
|
'UserID': 1,
|
|
'UserRef': '1',
|
|
'StudentInfo': ['Students', 1],
|
|
'LinkKey': {},
|
|
'Origin': None,
|
|
'Email': 'foo.bar@getgrist.com',
|
|
'Access': 'owners',
|
|
'SessionID': 'u1',
|
|
'IsLoggedIn': True,
|
|
'ShareRef': None
|
|
}
|
|
user2 = {
|
|
'Name': 'Baz Qux',
|
|
'UserID': 2,
|
|
'UserRef': '2',
|
|
'StudentInfo': ['Students', 1],
|
|
'LinkKey': {},
|
|
'Origin': None,
|
|
'Email': 'baz.qux@getgrist.com',
|
|
'Access': 'owners',
|
|
'SessionID': 'u2',
|
|
'IsLoggedIn': True,
|
|
'ShareRef': None
|
|
}
|
|
# Use formula to store last modified by data (user name and email). Check that it works as expected.
|
|
self.load_sample(self.sample)
|
|
self.add_column('Creatures', 'LastModifiedBy', type='Text', isFormula=False,
|
|
formula="user.Name + ' <' + user.Email + '>'", recalcWhen=RecalcWhen.MANUAL_UPDATES
|
|
)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastModifiedBy"],
|
|
[1, "Dolphin", 2, "Arthur", "Arthur", "Arthur", "Arthur", "Atlantic", ""],
|
|
])
|
|
|
|
self.apply_user_action(
|
|
['AddRecord', "Creatures", None, {"Name": "Manatee", "Ocean": 2}],
|
|
user=user1
|
|
)
|
|
self.apply_user_action(
|
|
['UpdateRecord', "Creatures", 1, {"Ocean": 3}],
|
|
user=user2
|
|
)
|
|
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastModifiedBy"],
|
|
[1, "Dolphin", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian", "Baz Qux <baz.qux@getgrist.com>"],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "Atlantic", "Foo Bar <foo.bar@getgrist.com>"],
|
|
])
|
|
|
|
# An indirect change doesn't affect the user, but a direct change does.
|
|
self.apply_user_action(
|
|
['UpdateRecord', "Oceans", 2, {"Name": "ATLANTIC"}],
|
|
user=user2
|
|
)
|
|
self.apply_user_action(
|
|
['UpdateRecord', "Creatures", 1, {"Name": "Whale"}],
|
|
user=user1
|
|
)
|
|
self.assertTableData("Creatures", data=[
|
|
["id","Name", "Ocean", "BossDef", "BossNvr", "BossUpd", "BossAll", "OceanName", "LastModifiedBy"],
|
|
[1, "Whale", 3, "Arthur", "Arthur", "Neptune", "Neptune", "Indian", "Foo Bar <foo.bar@getgrist.com>"],
|
|
[2, "Manatee", 2, "Poseidon", "", "Poseidon", "Poseidon", "ATLANTIC", "Foo Bar <foo.bar@getgrist.com>"],
|
|
])
|
|
|
|
sample_desc_math = {
|
|
"SCHEMA": [
|
|
[1, "Math", [
|
|
col(1, "A", "Numeric", False),
|
|
col(2, "B", "Numeric", False),
|
|
col(3, "C", "Numeric", False, "1/$A + 1/$B", recalcDeps=[1]),
|
|
]],
|
|
],
|
|
"DATA": {
|
|
}
|
|
}
|
|
sample_math = testutil.parse_test_sample(sample_desc_math)
|
|
|
|
def test_triggers_on_error(self):
|
|
# In case of an error in a trigger formula can be reevaluated when new value is provided
|
|
self.load_sample(self.sample_math)
|
|
self.add_record("Math", A=0, B=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 0, 1, div_error(0)],
|
|
])
|
|
self.update_record("Math", 1, A=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 1, 1, 2],
|
|
])
|
|
# When the error is cased by external column, formula is not reevaluated
|
|
self.update_record("Math", 1, A=2, B=0)
|
|
self.update_record("Math", 1, A=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 1, 0, div_error(2)],
|
|
])
|
|
self.update_record("Math", 1, B=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 1, 1, div_error(2)],
|
|
])
|
|
|
|
|
|
def test_traceback_available_for_trigger_formula(self):
|
|
# In case of an error engine is able to retrieve a traceback.
|
|
self.load_sample(self.sample_math)
|
|
self.add_record("Math", A=0, B=0)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 0, 0, div_error(0)],
|
|
])
|
|
message = 'float division by zero'
|
|
if six.PY3:
|
|
message += """
|
|
|
|
A `ZeroDivisionError` occurs when you are attempting to divide a value
|
|
by zero either directly or by using some other mathematical operation.
|
|
|
|
You are dividing by the following term
|
|
|
|
rec.A
|
|
|
|
which is equal to zero."""
|
|
self.assertFormulaError(self.engine.get_formula_error('Math', 'C', 1),
|
|
ZeroDivisionError, message,
|
|
r"1/rec\.A \+ 1/rec\.B")
|
|
self.update_record("Math", 1, A=1)
|
|
|
|
# Updating B should remove the traceback from an error, but the error should remain.
|
|
self.update_record("Math", 1, B=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 1, 1, div_error(0)],
|
|
])
|
|
error = self.engine.get_formula_error('Math', 'C', 1)
|
|
self.assertFormulaError(error, ZeroDivisionError, 'float division by zero')
|
|
self.assertEqual(error.details, objtypes.RaisedException(ZeroDivisionError()).no_traceback().details)
|
|
|
|
|
|
def test_undo_should_restore_dependencies(self):
|
|
"""
|
|
Test case for a bug. Undo wasn't restoring trigger formula dependencies.
|
|
"""
|
|
self.load_sample(self.sample_math)
|
|
self.add_record("Math", A=1, B=1)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 1, 1, 1/1 + 1/1],
|
|
])
|
|
|
|
# Remove deps from C.
|
|
out_actions = self.update_record("_grist_Tables_column", 3, recalcDeps=None)
|
|
# Make sure that trigger is not fired.
|
|
self.update_record("Math", 1, A=0.5)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 0.5, 1, 1/1 + 1/1], # C is not recalculated
|
|
])
|
|
|
|
# Apply undo action.
|
|
self.apply_undo_actions(out_actions.undo)
|
|
# Invoke trigger by updating A, and make sure C is updated.
|
|
self.update_record("Math", 1, A=0.2)
|
|
self.assertTableData("Math", data=[
|
|
["id", "A", "B", "C"],
|
|
[1, 0.2, 1, 1/0.2 + 1/1], # C is recalculated
|
|
])
|