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 migration14(tdset):
# Create the ACL table AND also the default ACL groups, default resource, and the default rule.
# These match the actions applied to new document by 'InitNewDoc' useraction (as of v14).
return tdset.apply_doc_actions([
actions.AddTable('_grist_ACLMemberships', [
schema.make_column('parent', 'Ref:_grist_ACLPrincipals'),
schema.make_column('child', 'Ref:_grist_ACLPrincipals'),
]),
actions.AddTable('_grist_ACLPrincipals', [
schema.make_column('userName', 'Text'),
schema.make_column('groupName', 'Text'),
schema.make_column('userEmail', 'Text'),
schema.make_column('instanceId', 'Text'),
schema.make_column('type', 'Text'),
]),
actions.AddTable('_grist_ACLResources', [
schema.make_column('colIds', 'Text'),
schema.make_column('tableId', 'Text'),
]),
actions.AddTable('_grist_ACLRules', [
schema.make_column('aclFormula', 'Text'),
schema.make_column('principals', 'Text'),
schema.make_column('resource', 'Ref:_grist_ACLResources'),
schema.make_column('aclColumn', 'Ref:_grist_Tables_column'),
schema.make_column('permissions', 'Int'),
]),
# Set up initial ACL data.
actions.BulkAddRecord(
'_grist_ACLPrincipals', [1, 2, 3, 4], {
'type': ['group', 'group', 'group', 'group'],
'groupName': ['Owners', 'Admins', 'Editors', 'Viewers'],
}),
actions.AddRecord('_grist_ACLResources', 1, {
'tableId': '',
'colIds': ''
}),
actions.AddRecord('_grist_ACLRules', 1, {
'resource': 1,
'permissions': 0x3F,
'principals': '[1]'
}),
])
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 schema_version0():
# This is the initial version of the schema before the very first migration. It's a historical
# snapshot, and thus should not be edited. The test verifies that starting with this v0,
# migrations bring the schema to the current version.
def make_column(col_id, col_type, formula='', isFormula=False):
return {
"id": col_id,
"type": col_type,
"isFormula": isFormula,
"formula": formula
}
return [
actions.AddTable("_grist_DocInfo", [
make_column("docId", "Text"),
make_column("peers", "Text"),
make_column("schemaVersion", "Int"),
]),
actions.AddTable("_grist_Tables", [
make_column("tableId", "Text"),
]),
actions.AddTable("_grist_Tables_column", [
make_column("parentId", "Ref:_grist_Tables"),
make_column("parentPos", "PositionNumber"),
make_column("colId", "Text"),
make_column("type", "Text"),
make_column("widgetOptions", "Text"),
make_column("isFormula", "Bool"),
make_column("formula", "Text"),
make_column("label", "Text")
]),
actions.AddTable("_grist_Imports", [
make_column("tableRef", "Ref:_grist_Tables"),
make_column("origFileName", "Text"),
make_column("parseFormula",
"Text",
isFormula=True,
formula="grist.parseImport(rec, table._engine)"),
make_column("delimiter", "Text", formula="','"),
make_column("doublequote", "Bool", formula="True"),
make_column("escapechar", "Text"),
make_column("quotechar", "Text", formula="'\"'"),
make_column("skipinitialspace", "Bool"),
make_column("encoding", "Text", formula="'utf8'"),
make_column("hasHeaders", "Bool"),
]),
actions.AddTable("_grist_External_database", [
make_column("host", "Text"),
make_column("port", "Int"),
make_column("username", "Text"),
make_column("dialect", "Text"),
make_column("database", "Text"),
make_column("storage", "Text"),
]),
actions.AddTable("_grist_External_table", [
make_column("tableRef", "Ref:_grist_Tables"),
make_column("databaseRef", "Ref:_grist_External_database"),
make_column("tableName", "Text"),
]),
actions.AddTable("_grist_TabItems", [
make_column("tableRef", "Ref:_grist_Tables"),
make_column("viewRef", "Ref:_grist_Views"),
]),
actions.AddTable("_grist_Views", [
make_column("name", "Text"),
make_column("type", "Text"),
make_column("layoutSpec", "Text"),
]),
actions.AddTable("_grist_Views_section", [
make_column("tableRef", "Ref:_grist_Tables"),
make_column("parentId", "Ref:_grist_Views"),
make_column("parentKey", "Text"),
make_column("title", "Text"),
make_column("defaultWidth", "Int", formula="100"),
make_column("borderWidth", "Int", formula="1"),
make_column("theme", "Text"),
make_column("chartType", "Text"),
make_column("layoutSpec", "Text"),
make_column("filterSpec", "Text"),
make_column("sortColRefs", "Text"),
make_column("linkSrcSectionRef", "Ref:_grist_Views_section"),
make_column("linkSrcColRef", "Ref:_grist_Tables_column"),
make_column("linkTargetColRef", "Ref:_grist_Tables_column"),
]),
actions.AddTable("_grist_Views_section_field", [
make_column("parentId", "Ref:_grist_Views_section"),
make_column("parentPos", "PositionNumber"),
make_column("colRef", "Ref:_grist_Tables_column"),
make_column("width", "Int"),
make_column("widgetOptions", "Text"),
]),
actions.AddTable("_grist_Validations", [
make_column("formula", "Text"),
make_column("name", "Text"),
make_column("tableRef", "Int")
]),
actions.AddTable("_grist_REPL_Hist", [
make_column("code", "Text"),
make_column("outputText", "Text"),
make_column("errorText", "Text")
]),
actions.AddTable("_grist_Attachments", [
make_column("fileIdent", "Text"),
make_column("fileName", "Text"),
make_column("fileType", "Text"),
make_column("fileSize", "Int"),
make_column("timeUploaded", "DateTime")
]),
actions.AddRecord("_grist_DocInfo", 1, {})
]
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_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 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 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_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_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]],
])
