def _changes_to_actions(self, table_id, col_id, column_delta, out_stored, out_undo):
"""
Given a column and a dict of column_deltas for it, of the form {row_id: (before_value,
after_value)}, creates DocActions and adds them to out_stored and out_undo lists.
"""
if not column_delta:
return
full_row_ids = sorted(r for r, (before, after) in six.iteritems(column_delta)
if not equal_encoding(before, after))
defunct = is_defunct(table_id) or is_defunct(col_id)
table_id = root_name(table_id)
col_id = root_name(col_id)
if not defunct:
row_ids = self.filter_out_gone_rows(table_id, full_row_ids)
if row_ids:
values = [column_delta[r][1] for r in row_ids]
out_stored.append(actions.BulkUpdateRecord(table_id, row_ids, {col_id: values}).simplify())
if self.is_created(table_id, col_id) and not defunct:
# A newly-create column, and not replacing a defunct one. Don't generate undo actions.
pass
else:
row_ids = self.filter_out_new_rows(table_id, full_row_ids)
if row_ids:
values = [column_delta[r][0] for r in row_ids]
undo_action = actions.BulkUpdateRecord(table_id, row_ids, {col_id: values}).simplify()
if defunct:
# If we deleted the column (or its containing table), then during undo, the updates for it
# should come after it's re-added. So we need to insert the undos *before*.
out_undo.insert(0, undo_action)
else:
out_undo.append(undo_action)
def test_record_removal(self):
# Remove a record, make sure that lookup maps get updated.
self.load_sample(testsamples.sample_students)
out_actions = self.remove_record("Schools", 3)
self.assertPartialOutActions(
out_actions,
{
"stored": [
actions.RemoveRecord("Schools", 3),
actions.BulkUpdateRecord("Students", [2, 4, 6], {
"schoolCities":
["West Haven", "West Haven", "West Haven"]
}),
actions.BulkUpdateRecord("Students", [2, 4, 6],
{"schoolIds": ["4", "4", "4"]}),
],
"calls": {
"Students": {
"schoolIds": 3,
"schoolCities": 3
},
# LookupMapColumn is also updated but via a different path (unset() vs method() call), so
# it's not included in the count of formula calls.
}
})
self.assertPartialData(
"Students", ["id", "schoolIds", "schoolCities"],
[[1, "1:2", "New York:Colombia"], [2, "4", "West Haven"],
[3, "1:2", "New York:Colombia"], [4, "4", "West Haven"],
[5, "", ""], [6, "4", "West Haven"]])
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 migration3(tdset):
"""
There is no longer a "Derived" type for columns, and summary tables use the type suitable for
the column being summarized. For old documents, convert "Derived" type to "Any", and adjust the
usage of "lookupOrAddDerived()" function.
"""
# Note that this is a complicated migration, and mainly acceptable because it is before our very
# first release. For a released product, a change like this should be done in a backwards
# compatible way: keep but deprecate 'Derived'; introduce a lookupOrAddDerived2() to use for new
# summary tables, but keep the old interface as well for existing ones. The reason is that such
# migrations are error-prone and may mess up customers' data.
doc_actions = []
tables = list(
actions.transpose_bulk_action(tdset.all_tables['_grist_Tables']))
tables_map = {t.id: t for t in tables}
columns = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables_column']))
# Convert columns from type 'Derived' to type 'Any'
affected_cols = [c for c in columns if c.type == 'Derived']
if affected_cols:
doc_actions.extend(
actions.ModifyColumn(tables_map[c.parentId].tableId, c.colId,
{'type': 'Any'}) for c in affected_cols)
doc_actions.append(
actions.BulkUpdateRecord('_grist_Tables_column',
[c.id for c in affected_cols],
{'type': ['Any' for c in affected_cols]}))
# Convert formulas of the form '.lookupOrAddDerived($x,$y)' to '.lookupOrAddDerived(x=$x,y=$y)'
formula_re = re.compile(r'(\w+).lookupOrAddDerived\((.*?)\)')
arg_re = re.compile(r'^\$(\w+)$')
def replace(match):
args = ", ".join(
arg_re.sub(r'\1=$\1', arg.strip())
for arg in match.group(2).split(","))
return '%s.lookupOrAddDerived(%s)' % (match.group(1), args)
formula_updates = []
for c in columns:
new_formula = c.formula and formula_re.sub(replace, c.formula)
if new_formula != c.formula:
formula_updates.append((c, new_formula))
if formula_updates:
doc_actions.extend(
actions.ModifyColumn(tables_map[c.parentId].tableId, c.colId,
{'formula': f}) for c, f in formula_updates)
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables_column', [c.id for c, f in formula_updates],
{'formula': [f for c, f in formula_updates]}))
return tdset.apply_doc_actions(doc_actions)
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 migration17(tdset):
"""
There is no longer an "Image" type for columns, as "Attachments" now serves as a
display type for arbitrary files including images. Convert "Image" columns to "Attachments"
columns.
"""
doc_actions = []
tables = list(
actions.transpose_bulk_action(tdset.all_tables['_grist_Tables']))
tables_map = {t.id: t for t in tables}
columns = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables_column']))
# Convert columns from type 'Image' to type 'Attachments'
affected_cols = [c for c in columns if c.type == 'Image']
conv = lambda val: [val] if isinstance(val, int) and val > 0 else []
if affected_cols:
# Update the types in the data tables
doc_actions.extend(
actions.ModifyColumn(tables_map[c.parentId].tableId, c.colId,
{'type': 'Attachments'})
for c in affected_cols)
# Update the values to lists
for c in affected_cols:
if c.isFormula:
# Formula columns don't have data stored in DB, should not have data changes.
continue
table_id = tables_map[c.parentId].tableId
table = tdset.all_tables[table_id]
doc_actions.append(
actions.BulkUpdateRecord(
table_id, table.row_ids,
{c.colId: [conv(val) for val in table.columns[c.colId]]}))
# Update the types in the metadata tables
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables_column', [c.id for c in affected_cols],
{'type': ['Attachments' for c in affected_cols]}))
return tdset.apply_doc_actions(doc_actions)
def migration4(tdset):
"""
Add TabPos column to TabBar table
"""
doc_actions = []
row_ids = tdset.all_tables['_grist_TabBar'].row_ids
doc_actions.append(add_column('_grist_TabBar', 'tabPos', 'PositionNumber'))
doc_actions.append(
actions.BulkUpdateRecord('_grist_TabBar', row_ids,
{'tabPos': row_ids}))
return tdset.apply_doc_actions(doc_actions)
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 BulkUpdateRecord(self, table_id, row_ids, columns):
table = self._engine.tables[table_id]
for row_id in row_ids:
assert row_id in table.row_ids, \
"docactions.[Bulk]UpdateRecord for non-existent record #%s" % row_id
# Load the updated values.
undo_values = {}
for col_id, values in six.iteritems(columns):
col = table.get_column(col_id)
undo_values[col_id] = [col.raw_get(r) for r in row_ids]
for (row_id, value) in zip(row_ids, values):
col.set(row_id, value)
# Non-formula columns may get invalidated and recalculated if they have a trigger formula.
# Prevent such recalculation if we set an explicit value for them (we want to prevent it
# even if triggered by something else within the same useraction).
if not col.is_formula():
self._engine.prevent_recalc(col.node,
row_ids,
should_prevent=True)
# Generate the undo action.
self._engine.out_actions.undo.append(
actions.BulkUpdateRecord(table_id, row_ids,
undo_values).simplify())
# Invalidate the updated rows, just for the columns that got changed (and, as always,
# anything that depends on them).
self._engine.invalidate_records(table_id,
row_ids,
col_ids=columns.keys())
# If the column update changes its trigger-formula conditions, rebuild dependencies.
if (table_id == "_grist_Tables_column"
and ("recalcWhen" in columns or "recalcDeps" in columns)):
self._engine.trigger_columns_changed()
def RemoveColumn(self, table_id, col_id):
table = self._engine.tables[table_id]
assert table.has_column(
col_id), "Column %s not in table %s" % (col_id, table_id)
# Generate (if needed) the undo action to restore the data.
undo_action = None
column = table.get_column(col_id)
if not column.is_private():
default = column.getdefault()
# Add to undo a BulkUpdateRecord for non-default values in the column being removed.
undo_values = [(r, column.raw_get(r)) for r in table.row_ids
if not strict_equal(column.raw_get(r), default)]
# Remove the specified column from the schema object.
colinfo = self._engine.schema[table_id].columns.pop(col_id)
self._engine.rebuild_usercode()
# Generate the undo action(s); if for a formula column, add them to the calc summary.
if undo_values:
if column.is_formula():
changes = [(r, v, default) for (r, v) in undo_values]
self._engine.out_actions.summary.add_changes(
table_id, col_id, changes)
else:
row_ids = [r for (r, v) in undo_values]
values = [v for (r, v) in undo_values]
undo_action = actions.BulkUpdateRecord(table_id, row_ids, {
col_id: values
}).simplify()
self._engine.out_actions.undo.append(undo_action)
self._engine.out_actions.undo.append(
actions.AddColumn(table_id, col_id,
schema.col_to_dict(colinfo, include_id=False)))
self._engine.out_actions.summary.remove_column(table_id, col_id)
def BulkUpdateRecord(self, table_id, row_ids, columns):
table = self._engine.tables[table_id]
for row_id in row_ids:
assert row_id in table.row_ids, \
"docactions.[Bulk]UpdateRecord for non-existent record #%s" % row_id
# Load the updated values.
undo_values = {}
for col_id, values in columns.iteritems():
col = table.get_column(col_id)
undo_values[col_id] = map(col.raw_get, row_ids)
for (row_id, value) in zip(row_ids, values):
col.set(row_id, value)
# Generate the undo action.
self._engine.out_actions.undo.append(
actions.BulkUpdateRecord(table_id, row_ids,
undo_values).simplify())
# Invalidate the updated rows, just for the columns that got changed (and, as always,
# anything that depends on them).
self._engine.invalidate_records(table_id,
row_ids,
col_ids=columns.keys())
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_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_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 test_multi_column_lookups(self):
"""
Check that we can do lookups by multiple columns.
"""
self.load_sample(testsamples.sample_students)
# Add a lookup formula which looks up a student matching on both first and last names.
self.add_column("Schools", "bestStudent", type="Text")
self.update_record("Schools", 1, bestStudent="Bush,George W")
self.add_column("Schools",
"bestStudentId",
formula=("""
if not $bestStudent: return ""
ln, fn = $bestStudent.split(",")
return ",".join(str(r.id) for r in Students.lookupRecords(firstName=fn, lastName=ln))
"""))
# Check data so far: only one record is filled.
self.assertPartialData("Schools",
["id", "bestStudent", "bestStudentId"], [
[1, "Bush,George W", "2"],
[2, "", ""],
[3, "", ""],
[4, "", ""],
])
# Fill a few more records and check that we find records we should, and don't find those we
# shouldn't.
out_actions = self.update_records("Schools", ["id", "bestStudent"], [
[2, "Clinton,Bill"],
[3, "Norris,Chuck"],
[4, "Bush,George H"],
])
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.BulkUpdateRecord(
"Schools", [2, 3, 4], {
"bestStudent":
["Clinton,Bill", "Norris,Chuck", "Bush,George H"]
}),
actions.BulkUpdateRecord("Schools", [2, 4],
{"bestStudentId": ["3", "4"]})
],
"calls": {
"Schools": {
"bestStudentId": 3
}
}
})
self.assertPartialData("Schools",
["id", "bestStudent", "bestStudentId"], [
[1, "Bush,George W", "2"],
[2, "Clinton,Bill", "3"],
[3, "Norris,Chuck", ""],
[4, "Bush,George H", "4"],
])
# Now add more records, first matching only some of the lookup fields.
out_actions = self.add_record("Students",
firstName="Chuck",
lastName="Morris")
self.assertPartialOutActions(
out_actions,
{
"calls": {
# No calculations of anything Schools because nothing depends on the incomplete value.
"Students": {
"#lookup#firstName:lastName": 1,
"schoolIds": 1,
"schoolCities": 1
}
},
"retValues": [7],
})
# If we add a matching record, then we get a calculation of a record in Schools
out_actions = self.add_record("Students",
firstName="Chuck",
lastName="Norris")
self.assertPartialOutActions(
out_actions, {
"calls": {
"Students": {
"#lookup#firstName:lastName": 1,
"schoolIds": 1,
"schoolCities": 1
},
"Schools": {
"bestStudentId": 1
}
},
"retValues": [8],
})
# And the data should be correct.
self.assertPartialData("Schools",
["id", "bestStudent", "bestStudentId"], [
[1, "Bush,George W", "2"],
[2, "Clinton,Bill", "3"],
[3, "Norris,Chuck", "8"],
[4, "Bush,George H", "4"],
])
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_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 create_primary_views_action(primary_views):
row_ids = sorted(primary_views.keys())
values = {'primaryViewId': [primary_views[r] for r in row_ids]}
return actions.BulkUpdateRecord('_grist_Tables', row_ids, values)
def _bulk_update(table_name, col_names, row_data):
return actions.BulkUpdateRecord(
*testutil.table_data_from_rows(table_name, col_names, row_data))
def test_group_by_two(self):
"""
Test a summary table created by grouping on two columns.
"""
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 2, 0, 'record', [10, 12]])
self.assertPartialData(
"GristSummary_6_Orders",
["id", "year", "product", "count", "amount", "group"], [
[1, 2012, "A", 1, 15.0, [1]],
[2, 2013, "A", 2, 30.0, [2, 3]],
[3, 2014, "B", 2, 70.0, [4, 5]],
[4, 2014, "A", 1, 16.0, [6]],
[5, 2015, "A", 2, 34.0, [7, 10]],
[6, 2015, "B", 2, 72.0, [8, 9]],
])
# Changing a record from one product to another should cause the two affected lines to change,
# or new lines to be created as needed.
out_actions = self.update_records("Orders", ["id", "product"], [
[2, "B"],
[6, "B"],
[7, "C"],
])
self.assertPartialOutActions(
out_actions, {
"stored": [
actions.BulkUpdateRecord("Orders", [2, 6, 7],
{"product": ["B", "B", "C"]}),
actions.AddRecord("GristSummary_6_Orders", 7, {
'year': 2013,
'product': 'B'
}),
actions.AddRecord("GristSummary_6_Orders", 8, {
'year': 2015,
'product': 'C'
}),
actions.BulkUpdateRecord(
"GristSummary_6_Orders", [2, 3, 4, 5, 7, 8],
{"amount": [15.0, 86.0, 0, 17.0, 15.0, 17.0]}),
actions.BulkUpdateRecord("GristSummary_6_Orders",
[2, 3, 4, 5, 7, 8],
{"count": [1, 3, 0, 1, 1, 1]}),
actions.BulkUpdateRecord(
"GristSummary_6_Orders", [2, 3, 4, 5, 7, 8],
{"group": [[3], [4, 5, 6], [], [10], [2], [7]]}),
],
})
# Verify the results.
self.assertPartialData(
"GristSummary_6_Orders",
["id", "year", "product", "count", "amount", "group"], [
[1, 2012, "A", 1, 15.0, [1]],
[2, 2013, "A", 1, 15.0, [3]],
[3, 2014, "B", 3, 86.0, [4, 5, 6]],
[4, 2014, "A", 0, 0.0, []],
[5, 2015, "A", 1, 17.0, [10]],
[6, 2015, "B", 2, 72.0, [8, 9]],
[7, 2013, "B", 1, 15.0, [2]],
[8, 2015, "C", 1, 17.0, [7]],
])
def migration7(tdset):
"""
Add summarySourceTable/summarySourceCol fields to metadata, and adjust existing summary tables
to correspond to the new style.
"""
# Note: this migration has some faults.
# - It doesn't delete viewSectionFields for columns it removes (if a user added some special
# columns manually.
# - It doesn't fix types of Reference columns that refer to old-style summary tables
# (if the user created some such columns manually).
doc_actions = [
action for action in [
maybe_add_column(tdset, '_grist_Tables', 'summarySourceTable',
'Ref:_grist_Tables'),
maybe_add_column(tdset, '_grist_Tables_column', 'summarySourceCol',
'Ref:_grist_Tables_column')
] if action
]
# Maps tableRef to Table object.
tables_map = {
t.id: t
for t in actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables'])
}
# Maps tableName to tableRef
table_name_to_ref = {t.tableId: t.id for t in six.itervalues(tables_map)}
# List of Column objects
columns = list(
actions.transpose_bulk_action(
tdset.all_tables['_grist_Tables_column']))
# Maps columnRef to Column object.
columns_map_by_ref = {c.id: c for c in columns}
# Maps (tableRef, colName) to Column object.
columns_map_by_table_colid = {(c.parentId, c.colId): c for c in columns}
# Set of all tableNames.
table_name_set = set(table_name_to_ref.keys())
remove_cols = [] # List of columns to remove
formula_updates = [] # List of (column, new_table_name, new_formula) pairs
table_renames = [] # List of (table, new_name) pairs
source_tables = [] # List of (table, summarySourceTable) pairs
source_cols = [] # List of (column, summarySourceColumn) pairs
# Summary tables used to be named as "Summary_<SourceName>_<ColRef1>_<ColRef2>". This regular
# expression parses that.
summary_re = re.compile(r'^Summary_(\w+?)((?:_\d+)*)$')
for t in six.itervalues(tables_map):
m = summary_re.match(t.tableId)
if not m or m.group(1) not in table_name_to_ref:
continue
# We have a valid summary table.
source_table_name = m.group(1)
source_table_ref = table_name_to_ref[source_table_name]
groupby_colrefs = [int(x) for x in m.group(2).strip("_").split("_")]
# Prepare a new-style name for the summary table. Be sure not to conflict with existing tables
# or with each other (i.e. don't rename multiple tables to the same name).
new_name = summary.encode_summary_table_name(source_table_name)
new_name = identifiers.pick_table_ident(new_name, avoid=table_name_set)
table_name_set.add(new_name)
log.warn("Upgrading summary table %s for %s(%s) to %s" %
(t.tableId, source_table_name, groupby_colrefs, new_name))
# Remove the "lookupOrAddDerived" column from the source table (which is named using the
# summary table name for its colId).
remove_cols.extend(
c for c in columns
if c.parentId == source_table_ref and c.colId == t.tableId)
# Upgrade the "group" formula in the summary table.
expected_group_formula = "%s.lookupRecords(%s=$id)" % (
source_table_name, t.tableId)
new_formula = "table.getSummarySourceGroup(rec)"
formula_updates.extend((c, new_name, new_formula) for c in columns
if (c.parentId == t.id and c.colId == "group"
and c.formula == expected_group_formula))
# Schedule a rename of the summary table.
table_renames.append((t, new_name))
# Set summarySourceTable fields on the metadata.
source_tables.append((t, source_table_ref))
# Set summarySourceCol fields in the metadata. We need to find the right summary column.
groupby_cols = set()
for col_ref in groupby_colrefs:
src_col = columns_map_by_ref.get(col_ref)
sum_col = columns_map_by_table_colid.get(
(t.id, src_col.colId)) if src_col else None
if sum_col:
groupby_cols.add(sum_col)
source_cols.append((sum_col, src_col.id))
else:
log.warn(
"Upgrading summary table %s: couldn't find column %s" %
(t.tableId, col_ref))
# Finally, we have to remove all non-formula columns that are not groupby-columns (e.g.
# 'manualSort'), because the new approach assumes ALL non-formula columns are for groupby.
remove_cols.extend(c for c in columns if c.parentId == t.id
and c not in groupby_cols and not c.isFormula)
# Create all the doc actions from the arrays we prepared.
# Process remove_cols
doc_actions.extend(
actions.RemoveColumn(tables_map[c.parentId].tableId, c.colId)
for c in remove_cols)
doc_actions.append(
actions.BulkRemoveRecord('_grist_Tables_column',
[c.id for c in remove_cols]))
# Process table_renames
doc_actions.extend(
actions.RenameTable(t.tableId, new) for (t, new) in table_renames)
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables', [t.id for t, new in table_renames],
{'tableId': [new for t, new in table_renames]}))
# Process source_tables and source_cols
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables', [t.id for t, ref in source_tables],
{'summarySourceTable': [ref for t, ref in source_tables]}))
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables_column', [t.id for t, ref in source_cols],
{'summarySourceCol': [ref for t, ref in source_cols]}))
# Process formula_updates. Do this last since recalculation of these may cause new records added
# to summary tables, so we should have all the tables correctly set up by this time.
doc_actions.extend(
actions.ModifyColumn(table_id, c.colId, {'formula': f})
for c, table_id, f in formula_updates)
doc_actions.append(
actions.BulkUpdateRecord(
'_grist_Tables_column', [c.id for c, t, f in formula_updates],
{'formula': [f for c, t, f in formula_updates]}))
return tdset.apply_doc_actions(doc_actions)
