def test_deletions(self):
self.load_sample(self.sample)
# Create a summary table summarizing count and total of orders by year.
self.apply_user_action(["CreateViewSection", 2, 0, 'record', [10]])
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 1, 15.0, [1]],
[2, 2013, 2, 30.0, [2, 3]],
[3, 2014, 3, 86.0, [4, 5, 6]],
[4, 2015, 4, 106.0, [7, 8, 9, 10]],
])
# Update a record so that a new line appears in the summary table.
out_actions_update = self.update_record("Orders", 1, year=2007)
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 0, 0.0, []],
[2, 2013, 2, 30.0, [2, 3]],
[3, 2014, 3, 86.0, [4, 5, 6]],
[4, 2015, 4, 106.0, [7, 8, 9, 10]],
[5, 2007, 1, 15.0, [1]],
])
# Undo and ensure that the new line is gone from the summary table.
out_actions_undo = self.apply_undo_actions(out_actions_update.undo)
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 1, 15.0, [1]],
[2, 2013, 2, 30.0, [2, 3]],
[3, 2014, 3, 86.0, [4, 5, 6]],
[4, 2015, 4, 106.0, [7, 8, 9, 10]],
])
self.assertPartialOutActions(
out_actions_undo, {
"stored": [
actions.UpdateRecord("GristSummary_6_Orders", 1,
{"group": [1]}),
actions.UpdateRecord("GristSummary_6_Orders", 1,
{"count": 1}),
actions.UpdateRecord("GristSummary_6_Orders", 1,
{"amount": 15.0}),
actions.RemoveRecord("GristSummary_6_Orders", 5),
actions.UpdateRecord("Orders", 1, {"year": 2012}),
],
"calls": {
"GristSummary_6_Orders": {
"group": 1,
"amount": 1,
"count": 1
},
"Orders": {
"#lookup##summary#GristSummary_6_Orders": 1,
"#summary#GristSummary_6_Orders": 1
}
}
})
def test_lookups_of_computed_values(self):
"""
Make sure that lookups get updated when the value getting looked up is a formula result.
"""
self.load_sample(testsamples.sample_students)
# Add a column like Schools.name, but computed, and change schoolIds to use that one instead.
self.add_column("Schools", "cname", formula="$name")
self.modify_column(
"Students",
"schoolIds",
formula=
"':'.join(str(id) for id in Schools.lookupRecords(cname=$schoolName).id)"
)
self.assertPartialData("Students", ["id", "schoolIds"], [
[1, "1:2"],
[2, "3:4"],
[3, "1:2"],
[4, "3:4"],
[5, ""],
[6, "3:4"],
])
# Check that a change to School.name, which triggers a change to School.cname, causes a change
# to the looked-up ids. The changes here should be the same as in test_lookup_key_changes
# test, even though schoolIds depends on name indirectly.
out_actions = self.update_record("Schools", 2, name="Eureka")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 2, {"name": "Eureka"}),
actions.UpdateRecord("Schools", 2, {"cname": "Eureka"}),
actions.BulkUpdateRecord("Students", [1, 3, 5], {
'schoolCities': ["New York", "New York", "Colombia"]
}),
actions.BulkUpdateRecord("Students", [1, 3, 5],
{'schoolIds': ["1", "1", "2"]}),
],
"calls": {
"Students": {
'schoolCities': 3,
'schoolIds': 3
},
"Schools": {
'#lookup#name': 1,
'#lookup#cname': 1,
"cname": 1
}
},
})
def test_lookup_dependencies(self, pre_loaded=False):
"""
Test changes to records accessed via lookup.
"""
if not pre_loaded:
self.load_sample(testsamples.sample_students)
out_actions = self.update_record("Address", 14, city="Bedford")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Address", 14, {"city": "Bedford"}),
_bulk_update("Students", ["id", "schoolCities"], [[
2, "New Haven:Bedford"
], [4, "New Haven:Bedford"], [6, "New Haven:Bedford"]])
],
"calls": {
"Students": {
"schoolCities": 3
}
}
})
out_actions = self.update_record("Schools", 4, address=13)
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 4, {"address": 13}),
_bulk_update("Students", ["id", "schoolCities"], [[
2, "New Haven:New Haven"
], [4, "New Haven:New Haven"], [6, "New Haven:New Haven"]])
],
"calls": {
"Students": {
"schoolCities": 3
}
}
})
out_actions = self.update_record("Address", 14, city="Hartford")
# No schoolCities need to be recalculatd here, since nothing depends on Address 14 any more.
self.assertPartialOutActions(out_actions, {"calls": {}})
# Confirm the final result.
self.assertPartialData("Students", ["id", "schoolIds", "schoolCities"],
[[1, "1:2", "New York:Colombia"],
[2, "3:4", "New Haven:New Haven"],
[3, "1:2", "New York:Colombia"],
[4, "3:4", "New Haven:New Haven"], [5, "", ""],
[6, "3:4", "New Haven:New Haven"]])
def migration18(tdset):
return tdset.apply_doc_actions([
add_column('_grist_DocInfo', 'timezone', 'Text'),
# all documents prior to this migration have been created in New York
actions.UpdateRecord('_grist_DocInfo', 1,
{'timezone': 'America/New_York'})
])
def migration15(tdset):
# Adds a filter JSON property to each field.
# From this version on, filterSpec in _grist_Views_section is deprecated.
doc_actions = [add_column('_grist_Views_section_field', 'filter', 'Text')]
# Get all section and field data to move section filter data to the fields
sections = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Views_section']))
fields = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Views_section_field']))
specs = {s.id: safe_parse(s.filterSpec) for s in sections}
# Move filter data from sections to fields
for f in fields:
# If the field belongs to the section and the field's colRef is in its filterSpec,
# pull the filter setting from the section.
filter_spec = specs.get(f.parentId)
if filter_spec and str(f.colRef) in filter_spec:
doc_actions.append(
actions.UpdateRecord(
'_grist_Views_section_field', f.id,
{'filter': json.dumps(filter_spec[str(f.colRef)])}))
return tdset.apply_doc_actions(doc_actions)
def migration26(tdset):
"""
Add rawViewSectionRef column to _grist_Tables
and new raw view sections for each 'normal' table.
"""
doc_actions = [
add_column('_grist_Tables', 'rawViewSectionRef',
'Ref:_grist_Views_section')
]
tables = list(
actions.transpose_bulk_action(tdset.all_tables["_grist_Tables"]))
columns = list(
actions.transpose_bulk_action(
tdset.all_tables["_grist_Tables_column"]))
views = {
view.id: view
for view in actions.transpose_bulk_action(
tdset.all_tables["_grist_Views"])
}
new_view_section_id = next_id(tdset, "_grist_Views_section")
for table in sorted(tables, key=lambda t: t.tableId):
old_view = views.get(table.primaryViewId)
if not (table.primaryViewId and old_view):
continue
table_columns = [
col for col in columns
if table.id == col.parentId and is_visible_column(col.colId)
]
table_columns.sort(key=lambda c: c.parentPos)
fields = {
"parentId": [new_view_section_id] * len(table_columns),
"colRef": [col.id for col in table_columns],
"parentPos": [col.parentPos for col in table_columns],
}
field_ids = [None] * len(table_columns)
doc_actions += [
actions.AddRecord(
"_grist_Views_section", new_view_section_id, {
"tableRef": table.id,
"parentId": 0,
"parentKey": "record",
"title": old_view.name,
"defaultWidth": 100,
"borderWidth": 1,
}),
actions.UpdateRecord("_grist_Tables", table.id, {
"rawViewSectionRef": new_view_section_id,
}),
actions.BulkAddRecord("_grist_Views_section_field", field_ids,
fields),
]
new_view_section_id += 1
return tdset.apply_doc_actions(doc_actions)
def test_lookup_key_changes(self, pre_loaded=False):
"""
Test changes to lookup values in the target table. Note that student #3 does not depend on
any records, but depends on the value "Eureka", so gets updated when this value appears.
"""
if not pre_loaded:
self.load_sample(testsamples.sample_students)
out_actions = self.update_record("Schools", 2, name="Eureka")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 2, {"name": "Eureka"}),
actions.BulkUpdateRecord("Students", [1, 3, 5], {
'schoolCities': ["New York", "New York", "Colombia"]
}),
actions.BulkUpdateRecord("Students", [1, 3, 5],
{'schoolIds': ["1", "1", "2"]}),
],
"calls": {
"Students": {
'schoolCities': 3,
'schoolIds': 3
},
"Schools": {
'#lookup#name': 1
}
},
})
# Test changes to lookup values in the table doing the lookup.
out_actions = self.update_records("Students", ["id", "schoolName"],
[[3, ""], [5, "Yale"]])
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.BulkUpdateRecord("Students", [3, 5],
{'schoolName': ["", "Yale"]}),
actions.BulkUpdateRecord(
"Students", [3, 5],
{'schoolCities': ["", "New Haven:West Haven"]}),
actions.BulkUpdateRecord("Students", [3, 5],
{'schoolIds': ["", "3:4"]}),
],
"calls": {
"Students": {
'schoolCities': 2,
'schoolIds': 2
}
},
})
# Confirm the final result.
self.assertPartialData(
"Students", ["id", "schoolIds", "schoolCities"],
[[1, "1", "New York"], [2, "3:4", "New Haven:West Haven"],
[3, "", ""], [4, "3:4", "New Haven:West Haven"],
[5, "3:4", "New Haven:West Haven"],
[6, "3:4", "New Haven:West Haven"]])
def alist():
return [
actions.BulkUpdateRecord("Table1", [1, 2, 3],
{'Foo': [10, 20, 30]}),
actions.BulkUpdateRecord("Table2", [1, 2, 3], {
'Foo': [10, 20, 30],
'Bar': ['a', 'b', 'c']
}),
actions.UpdateRecord("Table1", 17, {'Foo': 10}),
actions.UpdateRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.AddRecord("Table1", 17, {'Foo': 10}),
actions.BulkAddRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.ReplaceTableData("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
actions.AddColumn("Table1", "Foo", {"type": "Text"}),
actions.RenameColumn("Table1", "Foo", "Bar"),
actions.ModifyColumn("Table1", "Foo", {"type": "Text"}),
actions.RemoveColumn("Table1", "Foo"),
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
]
def test_lookup_formula_after_schema_change(self):
self.load_sample(testsamples.sample_students)
self.add_column("Schools", "state", type="Text")
# Make a change that causes recomputation of a lookup formula after a schema change.
# We should NOT get attribute errors in the values.
out_actions = self.update_record("Schools", 4, address=13)
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 4, {"address": 13}),
_bulk_update("Students", ["id", "schoolCities"], [[
2, "New Haven:New Haven"
], [4, "New Haven:New Haven"], [6, "New Haven:New Haven"]])
],
"calls": {
"Students": {
'schoolCities': 3
}
}
})
def migration16(tdset):
# Add visibleCol to columns and view fields, and set it from columns' and fields' widgetOptions.
doc_actions = [
add_column('_grist_Tables_column', 'visibleCol',
'Ref:_grist_Tables_column'),
add_column('_grist_Views_section_field', 'visibleCol',
'Ref:_grist_Tables_column'),
]
# Maps tableId to table, for looking up target table as listed in "Ref:*" types.
tables = list(
actions.transpose_bulk_action(tdset.all_tables['_grist_Tables']))
tables_by_id = {t.tableId: t for t in tables}
# Allow looking up columns by ref or by (tableRef, colId)
columns = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables_column']))
columns_by_ref = {c.id: c for c in columns}
columns_by_id = {(c.parentId, c.colId): c.id for c in columns}
# Helper which returns the {'visibleCol', 'widgetOptions'} update visibleCol should be set.
def convert_visible_col(col, widget_options):
if not col.type.startswith('Ref:'):
return None
# To set visibleCol, we need to know the target table. Skip if we can't find it.
target_table = tables_by_id.get(col.type[len('Ref:'):])
if not target_table:
return None
try:
parsed_options = json.loads(widget_options)
except Exception:
return None # If invalid widgetOptions, skip this column.
visible_col_id = parsed_options.pop('visibleCol', None)
if not visible_col_id:
return None
# Find visible_col_id as the column name in the appropriate table.
target_col_ref = (0 if visible_col_id == 'id' else columns_by_id.get(
(target_table.id, visible_col_id), None))
if target_col_ref is None:
return None
# Use compact separators without whitespace, to match how JS encodes JSON.
return {
'visibleCol': target_col_ref,
'widgetOptions': json.dumps(parsed_options, separators=(',', ':'))
}
for c in columns:
new_values = convert_visible_col(c, c.widgetOptions)
if new_values:
doc_actions.append(
actions.UpdateRecord('_grist_Tables_column', c.id, new_values))
fields = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Views_section_field']))
for f in fields:
c = columns_by_ref.get(f.colRef)
if c:
new_values = convert_visible_col(c, f.widgetOptions)
if new_values:
doc_actions.append(
actions.UpdateRecord('_grist_Views_section_field', f.id,
new_values))
return tdset.apply_doc_actions(doc_actions)
def test_add_remove_lookup(self):
# Verify that when we add or remove a lookup formula, we get appropriate changes.
self.load_sample(testsamples.sample_students)
# Add another lookup formula.
out_actions = self.add_column(
"Schools",
"lastNames",
formula=(
"','.join(Students.lookupRecords(schoolName=$name).lastName)"))
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.AddColumn(
"Schools", "lastNames", {
"formula":
"','.join(Students.lookupRecords(schoolName=$name).lastName)",
"isFormula": True,
"type": "Any"
}),
actions.AddRecord(
"_grist_Tables_column", 22, {
"colId": "lastNames",
"formula":
"','.join(Students.lookupRecords(schoolName=$name).lastName)",
"isFormula": True,
"label": "lastNames",
"parentId": 2,
"parentPos": 6.0,
"type": "Any",
"widgetOptions": ""
}),
_bulk_update(
"Schools", ["id", "lastNames"],
[[1, "Obama,Clinton"], [2, "Obama,Clinton"],
[3, "Bush,Bush,Ford"], [4, "Bush,Bush,Ford"]]),
],
"calls": {
"Schools": {
"lastNames": 4
},
"Students": {
"#lookup#schoolName": 6
}
},
})
# Make sure it responds to changes.
out_actions = self.update_record("Students", 5, schoolName="Columbia")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Students", 5,
{"schoolName": "Columbia"}),
_bulk_update("Schools", ["id", "lastNames"],
[[1, "Obama,Clinton,Reagan"],
[2, "Obama,Clinton,Reagan"]]),
actions.UpdateRecord(
"Students", 5, {"schoolCities": "New York:Colombia"}),
actions.UpdateRecord("Students", 5, {"schoolIds": "1:2"}),
],
"calls": {
"Students": {
'schoolCities': 1,
'schoolIds': 1,
'#lookup#schoolName': 1
},
"Schools": {
'lastNames': 2
}
},
})
# Modify the column: in the process, the LookupMapColumn on Students.schoolName becomes unused
# while the old formula column is removed, but used again when it's added. It should not have
# to be rebuilt (so there should be no calls to recalculate the LookupMapColumn.
out_actions = self.modify_column(
"Schools",
"lastNames",
formula=(
"','.join(Students.lookupRecords(schoolName=$name).firstName)"
))
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.ModifyColumn(
"Schools", "lastNames", {
"formula":
"','.join(Students.lookupRecords(schoolName=$name).firstName)"
}),
actions.UpdateRecord(
"_grist_Tables_column", 22, {
"formula":
"','.join(Students.lookupRecords(schoolName=$name).firstName)"
}),
_bulk_update(
"Schools", ["id", "lastNames"],
[[1, "Barack,Bill,Ronald"], [2, "Barack,Bill,Ronald"],
[3, "George W,George H,Gerald"],
[4, "George W,George H,Gerald"]])
],
"calls": {
"Schools": {
"lastNames": 4
}
}
})
# Remove the new lookup formula.
out_actions = self.remove_column("Schools", "lastNames")
self.assertPartialOutActions(out_actions, {}) # No calc actions
# Make sure that changes still work without errors.
out_actions = self.update_record("Students", 5, schoolName="Eureka")
self.assertPartialOutActions(
out_actions,
{
"stored": [
actions.UpdateRecord("Students", 5,
{"schoolName": "Eureka"}),
actions.UpdateRecord("Students", 5, {"schoolCities": ""}),
actions.UpdateRecord("Students", 5, {"schoolIds": ""}),
],
# This should NOT have '#lookup#schoolName' recalculation because there are no longer any
# formulas which do such a lookup.
"calls": {
"Students": {
'schoolCities': 1,
'schoolIds': 1
}
}
})
def test_group_by_one(self):
"""
Test basic summary table operation, for a table grouped by one columns.
"""
self.load_sample(self.sample)
# Create a derived table summarizing count and total of orders by year.
self.apply_user_action(["CreateViewSection", 2, 0, 'record', [10]])
# Check the results.
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 1, 15, [1]],
[2, 2013, 2, 30, [2, 3]],
[3, 2014, 3, 86, [4, 5, 6]],
[4, 2015, 4, 106, [7, 8, 9, 10]],
])
# Updating amounts should cause totals to be updated in the summary.
out_actions = self.update_records("Orders", ["id", "amount"],
[[1, 14], [2, 14]])
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.BulkUpdateRecord("Orders", [1, 2],
{'amount': [14, 14]}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 2],
{'amount': [14, 29]})
],
"calls": {
"GristSummary_6_Orders": {
"amount": 2
}
}
})
# Changing a record from one product to another should cause the two affected lines to change.
out_actions = self.update_record("Orders", 10, year=2012)
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Orders", 10, {"year": 2012}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 4],
{"amount": [31.0, 89.0]}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 4],
{"count": [2, 3]}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 4],
{"group": [[1, 10], [7, 8, 9]]}),
],
"calls": {
"GristSummary_6_Orders": {
"group": 2,
"amount": 2,
"count": 2
},
"Orders": {
"#lookup##summary#GristSummary_6_Orders": 1,
"#summary#GristSummary_6_Orders": 1
}
}
})
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 2, 31.0, [1, 10]],
[2, 2013, 2, 29.0, [2, 3]],
[3, 2014, 3, 86.0, [4, 5, 6]],
[4, 2015, 3, 89.0, [7, 8, 9]],
])
# Changing a record to a new year that wasn't in the summary should cause an add-record.
out_actions = self.update_record("Orders", 10, year=1999)
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Orders", 10, {"year": 1999}),
actions.AddRecord("GristSummary_6_Orders", 5,
{'year': 1999}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 5],
{"amount": [14.0, 17.0]}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 5],
{"count": [1, 1]}),
actions.BulkUpdateRecord("GristSummary_6_Orders", [1, 5],
{"group": [[1], [10]]}),
],
"calls": {
"GristSummary_6_Orders": {
'#lookup#year': 1,
"group": 2,
"amount": 2,
"count": 2
},
"Orders": {
"#lookup##summary#GristSummary_6_Orders": 1,
"#summary#GristSummary_6_Orders": 1
}
}
})
self.assertPartialData("GristSummary_6_Orders",
["id", "year", "count", "amount", "group"], [
[1, 2012, 1, 14.0, [1]],
[2, 2013, 2, 29.0, [2, 3]],
[3, 2014, 3, 86.0, [4, 5, 6]],
[4, 2015, 3, 89.0, [7, 8, 9]],
[5, 1999, 1, 17.0, [10]],
])
def test_lookup_formula_changes(self):
self.load_sample(testsamples.sample_students)
self.add_column("Schools", "state", type="Text")
self.update_records("Schools", ["id", "state"],
[[1, "NY"], [2, "MO"], [3, "CT"], [4, "CT"]])
# Verify that when we change a formula, we get appropriate changes.
out_actions = self.modify_column(
"Students",
"schoolCities",
formula=(
"','.join(Schools.lookupRecords(name=$schoolName).state)"))
self.assertPartialOutActions(
out_actions,
{
"stored": [
actions.ModifyColumn(
"Students", "schoolCities", {
"formula":
"','.join(Schools.lookupRecords(name=$schoolName).state)",
}),
actions.UpdateRecord(
"_grist_Tables_column", 6, {
"formula":
"','.join(Schools.lookupRecords(name=$schoolName).state)",
}),
_bulk_update("Students", ["id", "schoolCities"],
[[1, "NY,MO"], [2, "CT,CT"], [3, "NY,MO"],
[4, "CT,CT"], [6, "CT,CT"]])
],
# Note that it got computed 6 times (once for each record), but one value remained unchanged
# (because no schools matched).
"calls": {
"Students": {
'schoolCities': 6
}
}
})
# Check that we've created new dependencies, and removed old ones.
out_actions = self.update_record("Schools", 4, address=13)
self.assertPartialOutActions(out_actions, {"calls": {}})
out_actions = self.update_record("Schools", 4, state="MA")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 4, {"state": "MA"}),
_bulk_update("Students", ["id", "schoolCities"],
[[2, "CT,MA"], [4, "CT,MA"], [6, "CT,MA"]])
],
"calls": {
"Students": {
'schoolCities': 3
}
}
})
# If we change to look up uppercase values, we shouldn't find anything.
out_actions = self.modify_column(
"Students",
"schoolCities",
formula=
("','.join(Schools.lookupRecords(name=$schoolName.upper()).state)"
))
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.ModifyColumn(
"Students", "schoolCities", {
"formula":
"','.join(Schools.lookupRecords(name=$schoolName.upper()).state)"
}),
actions.UpdateRecord(
"_grist_Tables_column", 6, {
"formula":
"','.join(Schools.lookupRecords(name=$schoolName.upper()).state)"
}),
actions.BulkUpdateRecord(
"Students", [1, 2, 3, 4, 6],
{'schoolCities': ["", "", "", "", ""]})
],
"calls": {
"Students": {
'schoolCities': 6
}
}
})
# Changes to dependencies should cause appropriate recalculations.
out_actions = self.update_record("Schools",
4,
state="KY",
name="EUREKA")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Schools", 4, {
"state": "KY",
"name": "EUREKA"
}),
actions.UpdateRecord("Students", 5,
{'schoolCities': "KY"}),
actions.BulkUpdateRecord("Students", [2, 4, 6],
{'schoolIds': ["3", "3", "3"]}),
],
"calls": {
"Students": {
'schoolCities': 1,
'schoolIds': 3
},
'Schools': {
'#lookup#name': 1
}
}
})
self.assertPartialData(
"Students",
["id", "schoolIds", "schoolCities"],
[
# schoolCities aren't found here because we changed formula to lookup uppercase names.
[1, "1:2", ""],
[2, "3", ""],
[3, "1:2", ""],
[4, "3", ""],
[5, "", "KY"],
[6, "3", ""]
])
def migration23(tdset):
return tdset.apply_doc_actions([
add_column('_grist_DocInfo', 'documentSettings', 'Text'),
actions.UpdateRecord('_grist_DocInfo', 1,
{'documentSettings': '{"locale":"en-US"}'})
])
def test_prune_actions(self):
# prune_actions is in-place, so we make a new list every time.
def alist():
return [
actions.BulkUpdateRecord("Table1", [1, 2, 3],
{'Foo': [10, 20, 30]}),
actions.BulkUpdateRecord("Table2", [1, 2, 3], {
'Foo': [10, 20, 30],
'Bar': ['a', 'b', 'c']
}),
actions.UpdateRecord("Table1", 17, {'Foo': 10}),
actions.UpdateRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.AddRecord("Table1", 17, {'Foo': 10}),
actions.BulkAddRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.ReplaceTableData("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
actions.AddColumn("Table1", "Foo", {"type": "Text"}),
actions.RenameColumn("Table1", "Foo", "Bar"),
actions.ModifyColumn("Table1", "Foo", {"type": "Text"}),
actions.RemoveColumn("Table1", "Foo"),
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
]
def prune(table_id, col_id):
a = alist()
actions.prune_actions(a, table_id, col_id)
return a
self.assertEqual(
prune('Table1', 'Foo'),
[
actions.BulkUpdateRecord("Table2", [1, 2, 3], {
'Foo': [10, 20, 30],
'Bar': ['a', 'b', 'c']
}),
actions.UpdateRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.BulkAddRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.ReplaceTableData("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
# It doesn't do anything with column renames; it can be addressed if needed.
actions.RenameColumn("Table1", "Foo", "Bar"),
# It doesn't do anything with AddTable, which is expected.
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
])
self.assertEqual(prune('Table2', 'Foo'), [
actions.BulkUpdateRecord("Table1", [1, 2, 3],
{'Foo': [10, 20, 30]}),
actions.BulkUpdateRecord("Table2", [1, 2, 3],
{'Bar': ['a', 'b', 'c']}),
actions.UpdateRecord("Table1", 17, {'Foo': 10}),
actions.UpdateRecord("Table2", 18, {'Bar': 'a'}),
actions.AddRecord("Table1", 17, {'Foo': 10}),
actions.BulkAddRecord("Table2", 18, {'Bar': 'a'}),
actions.ReplaceTableData("Table2", 18, {'Bar': 'a'}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
actions.AddColumn("Table1", "Foo", {"type": "Text"}),
actions.RenameColumn("Table1", "Foo", "Bar"),
actions.ModifyColumn("Table1", "Foo", {"type": "Text"}),
actions.RemoveColumn("Table1", "Foo"),
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
])
def test_lookup_key_changes_reflist(self):
# We can't run this test case unchanged since our new column changes too in this test.
self.use_saved_lookup_results()
out_actions = self.update_record("Schools", 2, name="Eureka")
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord('Schools', 2, {'name': "Eureka"}),
actions.BulkUpdateRecord("Students", [1, 3, 5], {
'schoolCities': ["New York", "New York", "Colombia"]
}),
actions.BulkUpdateRecord("Students", [1, 3, 5],
{'schoolIds': ["1", "1", "2"]}),
actions.BulkUpdateRecord('Students', [1, 3, 5],
{'schools': [[1], [1], [2]]}),
],
"calls": {
"Students": {
'schools': 3,
'schoolCities': 3,
'schoolIds': 3
},
"Schools": {
'#lookup#name': 1
}
},
})
# Test changes to lookup values in the table doing the lookup.
out_actions = self.update_records("Students", ["id", "schoolName"],
[[3, ""], [5, "Yale"]])
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.BulkUpdateRecord("Students", [3, 5],
{'schoolName': ["", "Yale"]}),
actions.BulkUpdateRecord(
"Students", [3, 5],
{'schoolCities': ["", "New Haven:West Haven"]}),
actions.BulkUpdateRecord("Students", [3, 5],
{'schoolIds': ["", "3:4"]}),
actions.BulkUpdateRecord("Students", [3, 5],
{'schools': [[], [3, 4]]}),
],
"calls": {
"Students": {
'schools': 2,
'schoolCities': 2,
'schoolIds': 2
}
},
})
# Confirm the final result.
self.assertPartialData(
"Students", ["id", "schools", "schoolIds", "schoolCities"],
[[1, [1], "1", "New York"],
[2, [3, 4], "3:4", "New Haven:West Haven"], [3, [], "", ""],
[4, [3, 4], "3:4", "New Haven:West Haven"],
[5, [3, 4], "3:4", "New Haven:West Haven"],
[6, [3, 4], "3:4", "New Haven:West Haven"]])
def migration10(tdset):
"""
Add displayCol to all reference cols, with formula $<ref_col_id>.<visible_col_id>
(Note that displayCol field was added in the previous migration.)
"""
doc_actions = []
tables = list(
actions.transpose_bulk_action(tdset.all_tables['_grist_Tables']))
columns = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables_column']))
# Maps tableRef to tableId.
tables_map = {t.id: t.tableId for t in tables}
# Maps tableRef to sets of colIds in the tables. Used to prevent repeated colIds.
table_col_ids = {
t.id: set(tdset.all_tables[t.tableId].columns.keys())
for t in tables
}
# Get the next sequential column row id.
row_id = next_id(tdset, '_grist_Tables_column')
for c in columns:
# If a column is a reference with an unset display column, add a display column.
if c.type.startswith('Ref:') and not c.displayCol:
# Get visible_col_id. If not found, row id is used and no display col is necessary.
visible_col_id = ""
try:
visible_col_id = json.loads(c.widgetOptions).get('visibleCol')
if not visible_col_id:
continue
except Exception:
continue # If invalid widgetOptions, skip this column.
# Set formula to use the current visibleCol in widgetOptions.
formula = ("$%s.%s" % (c.colId, visible_col_id))
# Get a unique colId for the display column, and add it to the set of used ids.
used_col_ids = table_col_ids[c.parentId]
display_col_id = identifiers.pick_col_ident('gristHelper_Display',
avoid=used_col_ids)
used_col_ids.add(display_col_id)
# Add all actions to the list.
doc_actions.append(
add_column(tables_map[c.parentId],
'gristHelper_Display',
'Any',
formula=formula,
isFormula=True))
doc_actions.append(
actions.AddRecord(
'_grist_Tables_column', row_id, {
'parentPos': 1.0,
'label': 'gristHelper_Display',
'isFormula': True,
'parentId': c.parentId,
'colId': 'gristHelper_Display',
'formula': formula,
'widgetOptions': '',
'type': 'Any'
}))
doc_actions.append(
actions.UpdateRecord('_grist_Tables_column', c.id,
{'displayCol': row_id}))
# Increment row id to the next unused.
row_id += 1
return tdset.apply_doc_actions(doc_actions)
def create_migrations(all_tables, metadata_only=False):
"""
Creates and returns a list of DocActions needed to bring this document to
schema.SCHEMA_VERSION.
all_tables: all tables or just the metadata tables (those named with _grist_ prefix) as a
dictionary mapping table name to TableData.
metadata_only: should be set if only metadata tables are passed in. If ALL tables are
required to process migrations, this method will raise a "need all tables..." exception.
"""
try:
doc_version = all_tables['_grist_DocInfo'].columns["schemaVersion"][0]
except Exception:
doc_version = 0
# We create a TableDataSet, and populate it with the subset of the current schema that matches
# all_tables. For missing items, we make up tables and incomplete columns, which should be OK
# since we would not be adding new records to deprecated columns.
# Note that this approach makes it NOT OK to change column types.
tdset = table_data_set.TableDataSet()
# For each table in the provided metadata tables, create an AddTable action.
user_schema = schema.build_schema(all_tables['_grist_Tables'],
all_tables['_grist_Tables_column'],
include_builtin=False)
for t in six.itervalues(user_schema):
tdset.apply_doc_action(
actions.AddTable(t.tableId, schema.cols_to_dict_list(t.columns)))
# For each old table/column, construct an AddTable action using the current schema.
new_schema = {a.table_id: a for a in schema.schema_create_actions()}
for table_id, data in sorted(six.iteritems(all_tables)):
# User tables should already be in tdset; the rest must be metadata tables.
# (If metadata_only is true, there is simply nothing to skip here.)
if table_id not in tdset.all_tables:
new_col_info = {}
if table_id in new_schema:
new_col_info = {
c['id']: c
for c in new_schema[table_id].columns
}
# Use an incomplete default for unknown (i.e. deprecated) columns; some uses of the column
# would be invalid, such as adding a new record with missing values.
col_info = sorted([
new_col_info.get(col_id, {'id': col_id})
for col_id in data.columns
],
key=lambda c: list(six.iteritems(c)))
tdset.apply_doc_action(actions.AddTable(table_id, col_info))
# And load in the original data, interpreting the TableData object as BulkAddRecord action.
tdset.apply_doc_action(actions.BulkAddRecord(*data))
migration_actions = []
for version in xrange(doc_version + 1, schema.SCHEMA_VERSION + 1):
migration_func = all_migrations.get(version, noop_migration)
if migration_func.need_all_tables and metadata_only:
raise Exception("need all tables for migration to %s" % version)
migration_actions.extend(
all_migrations.get(version, noop_migration)(tdset))
# Note that if we are downgrading versions (i.e. doc_version is higher), then the following is
# the only action we include into the migration.
migration_actions.append(
actions.UpdateRecord('_grist_DocInfo', 1,
{'schemaVersion': schema.SCHEMA_VERSION}))
return migration_actions
class TestActions(unittest.TestCase):
action_obj1 = actions.UpdateRecord("foo", 17, {"bar": "baz"})
doc_action1 = ["UpdateRecord", "foo", 17, {"bar": "baz"}]
def test_convert(self):
self.assertEqual(actions.get_action_repr(self.action_obj1),
self.doc_action1)
self.assertEqual(actions.action_from_repr(self.doc_action1),
self.action_obj1)
with self.assertRaises(ValueError) as err:
actions.action_from_repr(["Foo", "bar"])
self.assertTrue("Foo" in str(err.exception))
def test_prune_actions(self):
# prune_actions is in-place, so we make a new list every time.
def alist():
return [
actions.BulkUpdateRecord("Table1", [1, 2, 3],
{'Foo': [10, 20, 30]}),
actions.BulkUpdateRecord("Table2", [1, 2, 3], {
'Foo': [10, 20, 30],
'Bar': ['a', 'b', 'c']
}),
actions.UpdateRecord("Table1", 17, {'Foo': 10}),
actions.UpdateRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.AddRecord("Table1", 17, {'Foo': 10}),
actions.BulkAddRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.ReplaceTableData("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
actions.AddColumn("Table1", "Foo", {"type": "Text"}),
actions.RenameColumn("Table1", "Foo", "Bar"),
actions.ModifyColumn("Table1", "Foo", {"type": "Text"}),
actions.RemoveColumn("Table1", "Foo"),
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
]
def prune(table_id, col_id):
a = alist()
actions.prune_actions(a, table_id, col_id)
return a
self.assertEqual(
prune('Table1', 'Foo'),
[
actions.BulkUpdateRecord("Table2", [1, 2, 3], {
'Foo': [10, 20, 30],
'Bar': ['a', 'b', 'c']
}),
actions.UpdateRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.BulkAddRecord("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.ReplaceTableData("Table2", 18, {
'Foo': 10,
'Bar': 'a'
}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
# It doesn't do anything with column renames; it can be addressed if needed.
actions.RenameColumn("Table1", "Foo", "Bar"),
# It doesn't do anything with AddTable, which is expected.
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
])
self.assertEqual(prune('Table2', 'Foo'), [
actions.BulkUpdateRecord("Table1", [1, 2, 3],
{'Foo': [10, 20, 30]}),
actions.BulkUpdateRecord("Table2", [1, 2, 3],
{'Bar': ['a', 'b', 'c']}),
actions.UpdateRecord("Table1", 17, {'Foo': 10}),
actions.UpdateRecord("Table2", 18, {'Bar': 'a'}),
actions.AddRecord("Table1", 17, {'Foo': 10}),
actions.BulkAddRecord("Table2", 18, {'Bar': 'a'}),
actions.ReplaceTableData("Table2", 18, {'Bar': 'a'}),
actions.RemoveRecord("Table1", 17),
actions.BulkRemoveRecord("Table2", [17, 18]),
actions.AddColumn("Table1", "Foo", {"type": "Text"}),
actions.RenameColumn("Table1", "Foo", "Bar"),
actions.ModifyColumn("Table1", "Foo", {"type": "Text"}),
actions.RemoveColumn("Table1", "Foo"),
actions.AddTable("THello", [{
"id": "Foo"
}, {
"id": "Bar"
}]),
actions.RemoveTable("THello"),
actions.RenameTable("THello", "TWorld"),
])
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_group_with_references(self):
"""
Test summary tables grouped on indirect values. In this example we want for each
customer.state, the number of customers and the total of their orders, which we can do either
as a summary on the Customers table, or a summary on the Orders table.
"""
self.load_sample(self.sample)
# Create a summary on the Customers table. Adding orders involves a lookup for each customer.
self.apply_user_action(["CreateViewSection", 1, 0, 'record', [3]])
self.add_column(
"GristSummary_9_Customers",
"totalAmount",
formula=
"sum(sum(Orders.lookupRecords(customer=c).amount) for c in $group)"
)
self.assertPartialData("GristSummary_9_Customers",
["id", "state", "count", "totalAmount"], [
[1, "NY", 2, 103.0],
[2, "CT", 2, 134.0],
[3, "NJ", 1, 0.0],
])
# # Create the same summary on the Orders table, looking up 'state' via the Customer reference.
# self.apply_user_action(["AddDerivedTableSource", "Summary4", "Orders",
# {"state": "$customer.state"}])
# self.add_column("Summary4", "numCustomers", formula="len(set($source_Orders.customer))")
# self.add_column("Summary4", "totalAmount", formula="sum($source_Orders.amount)")
# self.assertPartialData("Summary4", ["id", "state", "numCustomers", "totalAmount"], [
# [1, "CT", 2, 134.0 ],
# [2, "NY", 2, 103.0 ],
# ])
# In either case, changing an amount (from 36->37 for a CT customer) should update summaries.
out_actions = self.update_record('Orders', 9, amount=37)
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.UpdateRecord("Orders", 9, {"amount": 37}),
actions.UpdateRecord("GristSummary_9_Customers", 2,
{"totalAmount": 135.0}),
]
})
# In either case, changing a customer's state should trigger recomputation too.
# We are changing a NY customer with $51 in orders to MA.
self.update_record('Customers', 2, state="MA")
self.assertPartialData("GristSummary_9_Customers",
["id", "state", "count", "totalAmount"], [
[1, "NY", 1, 52.0],
[2, "CT", 2, 135.0],
[3, "NJ", 1, 0.0],
[4, "MA", 1, 51.0],
])
# self.assertPartialData("Summary4", ["id", "state", "numCustomers", "totalAmount"], [
# [1, "CT", 2, 135.0 ],
# [2, "NY", 1, 52.0 ],
# [3, "MA", 1, 51.0 ],
# ])
# Similarly, changing an Order to refer to a different customer should update both tables.
# Here we are changing a $17 order (#7) for a NY customer (#1) to a NJ customer (#4).
out_actions = self.update_record("Orders", 7, customer=4)
# self.assertPartialOutActions(out_actions, {
# "stored": [actions.UpdateRecord("Orders", 7, {"customer": 4}),
# actions.AddRecord("Summary4", 4, {"state": "NJ"}),
# actions.UpdateRecord("Summary4", 4, {"manualSort": 4.0})]
# })
self.assertPartialData("GristSummary_9_Customers",
["id", "state", "count", "totalAmount"], [
[1, "NY", 1, 35.0],
[2, "CT", 2, 135.0],
[3, "NJ", 1, 17.0],
[4, "MA", 1, 51.0],
])
