def test_view_remove(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Remove a view. Ensure related items, sections, fields get removed.
self.apply_user_action(["BulkRemoveRecord", "_grist_Views", [2,3]])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
# Note that the summary table is gone.
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(4, sections=[
Section(5, parentKey='record', tableRef=3, fields=[
Field(12, colRef=10),
Field(13, colRef=11),
Field(14, colRef=12),
]),
]),
])
self.assertTableData('_grist_TableViews', data=[
["id", "tableRef", "viewRef"],
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
def test_create_section_existing_view(self):
# Test that CreateViewSection works for an existing view.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
# Create a view + section for the initial table.
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Verify that we got a new view, with one section, and three fields.
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
])
]) ])
# Create a new section for the same view, check that only a section is added.
self.apply_user_action(["CreateViewSection", 1, 1, "record", None])
self.assertTables([self.starting_table])
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
])
]) ])
# Create another section for the same view, this time summarized.
self.apply_user_action(["CreateViewSection", 1, 1, "record", [21]])
summary_table = Table(2, "GristSummary_7_Address", 0, summarySourceTable=1, columns=[
Column(22, "city", "Text", isFormula=False, formula="", summarySourceCol=21),
Column(23, "group", "RefList:Address", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(24, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
self.assertTables([self.starting_table, summary_table])
# Check that we still have one view, with sections for different tables.
view = View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
]),
Section(3, parentKey="record", tableRef=2, fields=[
Field(3, colRef=22),
Field(4, colRef=24),
]),
])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
# Try to create a summary table for an invalid column, and check that it fails.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 1, 1, "record", [23]])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
def test_inserts(self):
# Test the insert() method. We do this on the columns metadata table, so that we can sort by
# a PositionNumber column.
self.load_sample(testsamples.sample_students)
student_columns = self.engine.docmodel.tables.lookupOne(tableId='Students').columns
school_columns = self.engine.docmodel.tables.lookupOne(tableId='Schools').columns
# Should go at the end of the Students table.
cols = self.engine.docmodel.insert(student_columns, None, colId=["a", "b"], type="Text")
# Should go at the start of the Schools table.
self.engine.docmodel.insert_after(school_columns, None, colId="foo", type="Int")
# Should go before the new "a", "b" columns of the Students table.
self.engine.docmodel.insert(student_columns, cols[0].parentPos, colId="bar", type="Date")
# Verify that the right columns were added to the right tables. This doesn't check positions.
self.assertTables([
Table(1, "Students", 0, 0, columns=[
Column(1, "firstName", "Text", False, "", 0),
Column(2, "lastName", "Text", False, "", 0),
Column(4, "schoolName", "Text", False, "", 0),
Column(5, "schoolIds", "Text", True,
"':'.join(str(id) for id in Schools.lookupRecords(name=$schoolName).id)", 0),
Column(6, "schoolCities", "Text", True,
"':'.join(r.address.city for r in Schools.lookupRecords(name=$schoolName))", 0),
Column(22, "a", "Text", False, "", 0),
Column(23, "b", "Text", False, "", 0),
Column(25, "bar", "Date", False, "", 0),
]),
Table(2, "Schools", 0, 0, columns=[
Column(10, "name", "Text", False, "", 0),
Column(12, "address", "Ref:Address",False, "", 0),
Column(24, "foo", "Int", False, "", 0),
]),
Table(3, "Address", 0, 0, columns=[
Column(21, "city", "Text", False, "", 0),
])
])
# Verify that positions are set such that the order is what we asked for.
student_columns = self.engine.docmodel.tables.lookupOne(tableId='Students').columns
self.assertEqual(list(map(int, student_columns)), [1,2,4,5,6,25,22,23])
school_columns = self.engine.docmodel.tables.lookupOne(tableId='Schools').columns
self.assertEqual(list(map(int, school_columns)), [24,10,12])
def init_sample_data(self):
# Add a new view with a section, and a new table to that view, and a summary table.
self.load_sample(self.sample2)
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
self.apply_user_action(["CreateViewSection", 0, 1, "record", None])
self.apply_user_action(["CreateViewSection", 1, 1, "record", [12]])
self.apply_user_action([
"BulkAddRecord", "Table1", [None] * 3, {
"A": ["a", "b", "c"],
"B": ["d", "e", "f"],
"C": ["", "", ""]
}
])
# Verify the new structure of tables and views.
self.assertTables([
Table(1,
"Address",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(11, "city", "Text", False, "", 0),
Column(12, "state", "Text", False, "", 0),
Column(13, "amount", "Numeric", False, "", 0),
]),
Table(2,
"Table1",
2,
0,
columns=[
Column(14, "manualSort", "ManualSortPos", False, "", 0),
Column(15, "A", "Text", False, "", 0),
Column(16, "B", "Text", False, "", 0),
Column(17, "C", "Text", False, "", 0),
]),
Table(3,
"GristSummary_7_Address",
0,
1,
columns=[
Column(18,
"state",
"Text",
False,
"",
summarySourceCol=12),
Column(19,
"group",
"RefList:Address",
True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(20,
"count",
"Int",
True,
summarySourceCol=0,
formula="len($group)"),
Column(21,
"amount",
"Numeric",
True,
summarySourceCol=0,
formula="SUM($group.amount)"),
]),
])
self.assertViews([
View(1,
sections=[
Section(1,
parentKey="record",
tableRef=1,
fields=[
Field(1, colRef=11),
Field(2, colRef=12),
Field(3, colRef=13),
]),
Section(4,
parentKey="record",
tableRef=2,
fields=[
Field(10, colRef=15),
Field(11, colRef=16),
Field(12, colRef=17),
]),
Section(5,
parentKey="record",
tableRef=3,
fields=[
Field(13, colRef=18),
Field(14, colRef=20),
Field(15, colRef=21),
]),
]),
View(2,
sections=[
Section(2,
parentKey="record",
tableRef=2,
fields=[
Field(4, colRef=15),
Field(5, colRef=16),
Field(6, colRef=17),
]),
])
])
self.assertTableData('Address', data=self.address_table_data)
self.assertTableData('Table1',
data=[
["id", "A", "B", "C", "manualSort"],
[1, "a", "d", "", 1.0],
[2, "b", "e", "", 2.0],
[3, "c", "f", "", 3.0],
])
self.assertTableData("GristSummary_7_Address",
cols="subset",
data=[
["id", "state", "count", "amount"],
[1, "NY", 7, 1. + 2 + 6 + 7 + 8 + 10 + 11],
[2, "WA", 1, 3.],
[3, "IL", 1, 4.],
[4, "MA", 2, 5. + 9],
])
def test_remove_source_column(self):
# Verify that we can remove a column when there is a summary table using that column to group
# by. (Bug T188.)
self.apply_user_action(["AddEmptyTable"])
self.apply_user_action(["BulkAddRecord", "Table1", [None]*3,
{"A": ['a','b','c'], "B": [1,1,2], "C": [4,5,6]}])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [2,3]])
# Verify metadata and actual data initially.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Text", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Numeric", False, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Text", False, "", 2),
Column(6, "B", "Numeric", False, "", 3),
Column(7, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(8, "count", "Int", True, "len($group)", 0),
Column(9, "C", "Numeric", True, "SUM($group.C)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, 'a', 1.0, 4 ],
[ 2, 2.0, 'b', 1.0, 5 ],
[ 3, 3.0, 'c', 2.0, 6 ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "B", "group", "count", "C" ],
[ 1, 'a', 1.0, [1], 1, 4 ],
[ 2, 'b', 1.0, [2], 1, 5 ],
[ 3, 'c', 2.0, [3], 1, 6 ],
])
# Remove column A, used for group-by.
self.apply_user_action(["RemoveColumn", "Table1", "A"])
# Verify that the conversion's result is as expected.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Numeric", False, "", 0),
]),
Table(3, "GristSummary_6_Table1_2", summarySourceTable=1, primaryViewId=0, columns=[
Column(10, "B", "Numeric", False, "", 3),
Column(11, "count", "Int", True, "len($group)", 0),
Column(12, "C", "Numeric", True, "SUM($group.C)", 0),
Column(13, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "B", "C" ],
[ 1, 1.0, 1.0, 4 ],
[ 2, 2.0, 1.0, 5 ],
[ 3, 3.0, 2.0, 6 ],
])
self.assertTableData('GristSummary_6_Table1_2', data=[
[ "id", "B", "group", "count", "C" ],
[ 1, 1.0, [1,2], 2, 9 ],
[ 2, 2.0, [3], 1, 6 ],
])
def test_change_summary_formula(self):
# Verify that changing a summary formula affects all group-by variants, and adding a new
# summary table gets the changed formula.
#
# (Recall that all summaries of a single table are *conceptually* variants of a single summary
# table, sharing all formulas and differing only in the group-by columns.)
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
# These are the tables and columns we automatically get.
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "city", "Text", False, "", 11),
Column(15, "state", "Text", False, "", 12),
Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(17, "count", "Int", True, "len($group)", 0),
Column(18, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(20, "count", "Int", True, "len($group)", 0),
Column(21, "amount", "Numeric", True, "SUM($group.amount)", 0),
])
])
# Now change a formula using one of the summary tables. It should trigger an equivalent
# change in the other.
self.apply_user_action(["ModifyColumn", "GristSummary_7_Address", "amount",
{"formula": "10*sum($group.amount)"}])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'],
[21, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'],
])
# Change a formula and a few other fields in the other table, and verify a change to both.
self.apply_user_action(["ModifyColumn", "GristSummary_7_Address2", "amount",
{"formula": "100*sum($group.amount)",
"type": "Text",
"widgetOptions": "hello",
"label": "AMOUNT",
"untieColIdFromLabel": True
}])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
])
# Check the values in the summary tables: they should reflect the new formula.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, str(100*(1.+6+11))],
[ 2, "Albany", "NY" , 1, "200.0" ],
[ 3, "Seattle", "WA" , 1, "300.0" ],
[ 4, "Chicago", "IL" , 1, "400.0" ],
[ 5, "Bedford", "MA" , 1, "500.0" ],
[ 6, "Buffalo", "NY" , 1, "700.0" ],
[ 7, "Bedford", "NY" , 1, "800.0" ],
[ 8, "Boston", "MA" , 1, "900.0" ],
[ 9, "Yonkers", "NY" , 1, "1000.0" ],
])
self.assertTableData('GristSummary_7_Address2', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, "6600.0"],
])
# Add a new summary table, and check that it gets the new formula.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "city", "Text", False, "", 11),
Column(15, "state", "Text", False, "", 12),
Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(17, "count", "Int", True, "len($group)", 0),
Column(18, "amount", "Text", True, "100*sum($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(20, "count", "Int", True, "len($group)", 0),
Column(21, "amount", "Text", True, "100*sum($group.amount)", 0),
]),
Table(4, "GristSummary_7_Address3", 0, 1, columns=[
Column(22, "state", "Text", False, "", 12),
Column(23, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(24, "count", "Int", True, "len($group)", 0),
Column(25, "amount", "Text", True, "100*sum($group.amount)", 0),
])
])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[25, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address3', cols="subset", data=[
[ "id", "state", "count", "amount" ],
[ 1, "NY", 7, str(100*(1.+2+6+7+8+10+11)) ],
[ 2, "WA", 1, "300.0" ],
[ 3, "IL", 1, "400.0" ],
[ 4, "MA", 2, str(500.+900) ],
])
def test_summary_table_reuse(self):
# Test that we'll reuse a suitable summary table when already available.
self.load_sample(self.sample)
# Create a summary section grouped by two columns ("city" and "state").
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Verify the new table and views.
summary_table = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1,
columns=[
Column(14, "city", "Text", isFormula=False, formula="", summarySourceCol=11),
Column(15, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(16, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(17, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(18, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view = View(1, sections=[
Section(1, parentKey="record", tableRef=2, fields=[
Field(1, colRef=14),
Field(2, colRef=15),
Field(3, colRef=17),
Field(4, colRef=18),
])
])
self.assertTables([self.starting_table, summary_table])
self.assertViews([summary_view])
# Create twoo other views + view sections with the same breakdown (in different order
# of group-by fields, which should still reuse the same table).
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12,11]])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
summary_view2 = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(5, colRef=15),
Field(6, colRef=14),
Field(7, colRef=17),
Field(8, colRef=18),
])
])
summary_view3 = View(3, sections=[
Section(3, parentKey="record", tableRef=2, fields=[
Field(9, colRef=14),
Field(10, colRef=15),
Field(11, colRef=17),
Field(12, colRef=18),
])
])
# Verify that we have a new view, but are reusing the table.
self.assertTables([self.starting_table, summary_table])
self.assertViews([summary_view, summary_view2, summary_view3])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+6+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 5. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 1, 8. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
])
def test_summary_by_choice_list(self):
self.load_sample(self.sample)
# Verify the starting table; there should be no views yet.
self.assertTables([self.starting_table])
self.assertViews([])
# Create a summary section, grouped by the "choices1" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]])
summary_table1 = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(13,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(14,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(15,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by both choicelist columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]])
summary_table2 = Table(
3,
"GristSummary_6_Source2",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(16,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(17,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=12),
Column(18,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(19,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by the non-choicelist column
self.apply_user_action(["CreateViewSection", 1, 0, "record", [10]])
summary_table3 = Table(
4,
"GristSummary_6_Source3",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(20,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=10),
Column(21,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(22,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by the non-choicelist column and choices1
self.apply_user_action(["CreateViewSection", 1, 0, "record", [10, 11]])
summary_table4 = Table(
5,
"GristSummary_6_Source4",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(23,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=10),
Column(24,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(25,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(26,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
self.assertTables([
self.starting_table, summary_table1, summary_table2,
summary_table3, summary_table4
])
# Verify the summarized data.
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [21], 1],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [21], 1],
[4, "b", "d", [21], 1],
])
self.assertTableData('GristSummary_6_Source3',
data=[
["id", "other", "group", "count"],
[1, "foo", [21], 1],
])
self.assertTableData('GristSummary_6_Source4',
data=[
["id", "other", "choices1", "group", "count"],
[1, "foo", "a", [21], 1],
[2, "foo", "b", [21], 1],
])
# Verify the optimisation works for the table without choicelists
self.assertIs(self.engine.tables["Source"]._summary_simple, None)
self.assertIs(
self.engine.tables["GristSummary_6_Source"]._summary_simple, False)
self.assertIs(
self.engine.tables["GristSummary_6_Source2"]._summary_simple,
False)
# simple summary and lookup
self.assertIs(
self.engine.tables["GristSummary_6_Source3"]._summary_simple, True)
self.assertIs(
self.engine.tables["GristSummary_6_Source4"]._summary_simple,
False)
self.assertEqual(
{
k: type(v)
for k, v in self.engine.tables["Source"]._special_cols.items()
},
{
'#summary#GristSummary_6_Source':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source')":
lookup.ContainsLookupMapColumn,
'#summary#GristSummary_6_Source2':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source2')":
lookup.ContainsLookupMapColumn,
# simple summary and lookup
'#summary#GristSummary_6_Source3':
column.ReferenceColumn,
'#lookup##summary#GristSummary_6_Source3':
lookup.SimpleLookupMapColumn,
'#summary#GristSummary_6_Source4':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source4')":
lookup.ContainsLookupMapColumn,
})
# Remove 'b' from choices1
self.update_record("Source", 21, choices1=["L", "a"])
self.assertTableData('Source',
data=[
["id", "choices1", "choices2", "other"],
[21, ["a"], ["c", "d"], "foo"],
])
# Verify that the summary table rows containing 'b' are empty
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [], 0],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
])
# Add 'e' to choices2
self.update_record("Source", 21, choices2=["L", "c", "d", "e"])
# First summary table unaffected
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [], 0],
])
# New row added for 'e'
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "a", "e", [21], 1],
])
# Remove record from source
self.remove_record("Source", 21)
# All summary rows are now empty
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [], 0],
[2, "b", [], 0],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [], 0],
[2, "a", "d", [], 0],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "a", "e", [], 0],
])
# Make rows with every combination of {a,b,ab} and {c,d,cd}
self.add_records('Source', ["id", "choices1", "choices2"], [
[101, ["L", "a"], ["L", "c"]],
[102, ["L", "b"], ["L", "c"]],
[103, ["L", "a", "b"], ["L", "c"]],
[104, ["L", "a"], ["L", "d"]],
[105, ["L", "b"], ["L", "d"]],
[106, ["L", "a", "b"], ["L", "d"]],
[107, ["L", "a"], ["L", "c", "d"]],
[108, ["L", "b"], ["L", "c", "d"]],
[109, ["L", "a", "b"], ["L", "c", "d"]],
])
self.assertTableData('Source',
cols="subset",
data=[
["id", "choices1", "choices2"],
[101, ["a"], ["c"]],
[102, ["b"], ["c"]],
[103, ["a", "b"], ["c"]],
[104, ["a"], ["d"]],
[105, ["b"], ["d"]],
[106, ["a", "b"], ["d"]],
[107, ["a"], ["c", "d"]],
[108, ["b"], ["c", "d"]],
[109, ["a", "b"], ["c", "d"]],
])
# Summary tables now have an even distribution of combinations
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [101, 103, 104, 106, 107, 109], 6],
[2, "b", [102, 103, 105, 106, 108, 109], 6],
])
summary_data = [
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [101, 103, 107, 109], 4],
[2, "a", "d", [104, 106, 107, 109], 4],
[3, "b", "c", [102, 103, 108, 109], 4],
[4, "b", "d", [105, 106, 108, 109], 4],
[5, "a", "e", [], 0],
]
self.assertTableData('GristSummary_6_Source2', data=summary_data)
# Verify that "DetachSummaryViewSection" useraction works correctly.
self.apply_user_action(["DetachSummaryViewSection", 2])
self.assertTables([
self.starting_table, summary_table1, summary_table3,
summary_table4,
Table(
6,
"Table1",
primaryViewId=5,
summarySourceTable=0,
columns=[
Column(27,
"manualSort",
"ManualSortPos",
isFormula=False,
formula="",
summarySourceCol=0),
Column(28,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
Column(29,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
Column(30,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
Column(
31,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula=
"Source.lookupRecords(choices1=CONTAINS($choices1), choices2=CONTAINS($choices2))"
),
],
)
])
self.assertTableData('Table1', data=summary_data, cols="subset")
def test_change_choice_to_choicelist(self):
sample = testutil.parse_test_sample({
"SCHEMA": [[
1, "Source",
[
[10, "other", "Text", False, "", "other", ""],
[11, "choices1", "Choice", False, "", "choice", ""],
]
]],
"DATA": {
"Source": [
["id", "choices1", "other"],
[21, "a", "foo"],
[22, "b", "bar"],
]
}
})
starting_table = Table(1,
"Source",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(10,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=0),
Column(11,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
])
self.load_sample(sample)
# Verify the starting table; there should be no views yet.
self.assertTables([starting_table])
self.assertViews([])
# Create a summary section, grouped by the "choices1" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]])
summary_table = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(12,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(13,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(14,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
data = [
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [22], 1],
]
self.assertTables([starting_table, summary_table])
self.assertTableData('GristSummary_6_Source', data=data)
# Change the column from Choice to ChoiceList
self.apply_user_action([
"UpdateRecord", "_grist_Tables_column", 11, {
"type": "ChoiceList"
}
])
# Changing type in reality is a bit more complex than these actions
# so we put the correct values in place directly
self.apply_user_action([
"BulkUpdateRecord", "Source", [21, 22], {
"choices1": [["L", "a"], ["L", "b"]]
}
])
starting_table.columns[1] = starting_table.columns[1]._replace(
type="ChoiceList")
self.assertTables([starting_table, summary_table])
self.assertTableData('GristSummary_6_Source', data=data)
def init_views_sample(self):
# Add a new table and a view, to get some Views/Sections/Fields, and TableView/TabBar items.
self.apply_user_action(['AddTable', 'Schools', [
{'id': 'city', 'type': 'Text'},
{'id': 'state', 'type': 'Text'},
{'id': 'size', 'type': 'Numeric'},
]])
self.apply_user_action(['BulkAddRecord', 'Schools', [1,2,3,4], {
'city': ['New York', 'Colombia', 'New York', ''],
'state': ['NY', 'NY', 'NY', ''],
'size': [1000, 2000, 3000, 4000],
}])
# Add a new view; a second section (summary) to it; and a third view.
self.apply_user_action(['CreateViewSection', 1, 0, 'detail', None])
self.apply_user_action(['CreateViewSection', 1, 2, 'record', [3]])
self.apply_user_action(['CreateViewSection', 1, 0, 'chart', None])
self.apply_user_action(['CreateViewSection', 0, 2, 'record', None])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
Table(2, "GristSummary_7_Schools", 0, 1, columns=[
Column(5, "state", "Text", False, "", 3),
Column(6, "group", "RefList:Schools", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "size", "Numeric", True, "SUM($group.size)", 0),
]),
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2, sections=[
Section(2, parentKey="detail", tableRef=1, fields=[
Field(4, colRef=2),
Field(5, colRef=3),
Field(6, colRef=4),
]),
Section(3, parentKey="record", tableRef=2, fields=[
Field(7, colRef=5),
Field(8, colRef=7),
Field(9, colRef=8),
]),
Section(6, parentKey='record', tableRef=3, fields=[
Field(15, colRef=10),
Field(16, colRef=11),
Field(17, colRef=12),
]),
]),
View(3, sections=[
Section(4, parentKey="chart", tableRef=1, fields=[
Field(10, colRef=2),
Field(11, colRef=3),
]),
]),
View(4, sections=[
Section(5, parentKey='record', tableRef=3, fields=[
Field(12, colRef=10),
Field(13, colRef=11),
Field(14, colRef=12),
]),
]),
])
self.assertTableData('_grist_TableViews', data=[
["id", "tableRef", "viewRef"],
[1, 1, 2],
[2, 1, 3],
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4]
])
def test_creates_section_new_table(self):
# Test that CreateViewSection works for adding a new table.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
self.assertViews([])
# When we create a section/view for new table, we get both a primary view, and the new view we
# are creating.
self.apply_user_action(["CreateViewSection", 0, 0, "record", None])
new_table = Table(2, "Table1", primaryViewId=1, summarySourceTable=0, columns=[
Column(22, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(23, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(24, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(25, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view = View(1, sections=[
Section(1, parentKey="record", tableRef=2, fields=[
Field(1, colRef=23),
Field(2, colRef=24),
Field(3, colRef=25),
])
])
new_view = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(4, colRef=23),
Field(5, colRef=24),
Field(6, colRef=25),
])
])
self.assertTables([self.starting_table, new_table])
self.assertViews([primary_view, new_view])
# Create another section in an existing view for a new table.
self.apply_user_action(["CreateViewSection", 0, 2, "record", None])
new_table2 = Table(3, "Table2", primaryViewId=3, summarySourceTable=0, columns=[
Column(26, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(27, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(28, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(29, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view2 = View(3, sections=[
Section(3, parentKey="record", tableRef=3, fields=[
Field(7, colRef=27),
Field(8, colRef=28),
Field(9, colRef=29),
])
])
new_view.sections.append(
Section(4, parentKey="record", tableRef=3, fields=[
Field(10, colRef=27),
Field(11, colRef=28),
Field(12, colRef=29),
])
)
# Check that we have a new table, only the primary view as new view; and a new section.
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# Check that we can't create a summary of a table grouped by a column that doesn't exist yet.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 0, 2, "record", [31]])
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# But creating a new table and showing totals for it is possible though dumb.
self.apply_user_action(["CreateViewSection", 0, 2, "record", []])
# We expect a new table.
new_table3 = Table(4, "Table3", primaryViewId=4, summarySourceTable=0, columns=[
Column(30, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(31, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(32, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(33, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
# A summary of it.
summary_table = Table(5, "GristSummary_6_Table3", 0, summarySourceTable=4, columns=[
Column(34, "group", "RefList:Table3", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(35, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
# The primary view of the new table.
primary_view3 = View(4, sections=[
Section(5, parentKey="record", tableRef=4, fields=[
Field(13, colRef=31),
Field(14, colRef=32),
Field(15, colRef=33),
])
])
# And a new view section for the summary.
new_view.sections.append(Section(6, parentKey="record", tableRef=5, fields=[
Field(16, colRef=35)
]))
self.assertTables([self.starting_table, new_table, new_table2, new_table3, summary_table])
self.assertViews([primary_view, new_view, primary_view2, primary_view3])
class TestUserActions(test_engine.EngineTestCase):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "Address", [
[21, "city", "Text", False, "", "", ""],
]]
],
"DATA": {
"Address": [
["id", "city" ],
[11, "New York" ],
[12, "Colombia" ],
[13, "New Haven" ],
[14, "West Haven" ]],
}
})
starting_table = Table(1, "Address", primaryViewId=0, summarySourceTable=0, columns=[
Column(21, "city", "Text", isFormula=False, formula="", summarySourceCol=0)
])
#----------------------------------------------------------------------
def test_conversions(self):
# Test the sequence of user actions as used for transform-based conversions. This is actually
# not exactly what the client emits, but more like what the client should ideally emit.
# Our sample has a Schools.city text column; we'll convert it to Ref:Address.
self.load_sample(self.sample)
# Add a new table for Schools so that we get the associated views and fields.
self.apply_user_action(['AddTable', 'Schools', [{'id': 'city', 'type': 'Text'}]])
self.apply_user_action(['BulkAddRecord', 'Schools', [1,2,3,4], {
'city': ['New York', 'Colombia', 'New York', '']
}])
self.assertPartialData("_grist_Tables", ["id", "tableId"], [
[1, "Address"],
[2, "Schools"],
])
self.assertPartialData("_grist_Tables_column",
["id", "colId", "parentId", "parentPos", "widgetOptions"], [
[21, "city", 1, 1.0, ""],
[22, "manualSort", 2, 2.0, ""],
[23, "city", 2, 3.0, ""],
])
self.assertPartialData("_grist_Views_section_field", ["id", "colRef", "widgetOptions"], [
[1, 23, ""]
])
self.assertPartialData("Schools", ["id", "city"], [
[1, "New York" ],
[2, "Colombia" ],
[3, "New York" ],
[4, "" ],
])
# Our sample has a text column city.
out_actions = self.add_column('Schools', 'grist_Transform',
isFormula=True, formula='return $city', type='Text')
self.assertPartialOutActions(out_actions, { "stored": [
['AddColumn', 'Schools', 'grist_Transform', {
'type': 'Text', 'isFormula': True, 'formula': 'return $city',
}],
['AddRecord', '_grist_Tables_column', 24, {
'widgetOptions': '', 'parentPos': 4.0, 'isFormula': True, 'parentId': 2, 'colId':
'grist_Transform', 'formula': 'return $city', 'label': 'grist_Transform',
'type': 'Text'
}],
["AddRecord", "_grist_Views_section_field", 2, {
"colRef": 24, "parentId": 1, "parentPos": 2.0
}],
["BulkUpdateRecord", "Schools", [1, 2, 3],
{"grist_Transform": ["New York", "Colombia", "New York"]}],
]})
out_actions = self.update_record('_grist_Tables_column', 24,
type='Ref:Address',
formula='return Address.lookupOne(city=$city).id')
self.assertPartialOutActions(out_actions, { "stored": [
['ModifyColumn', 'Schools', 'grist_Transform', {
'formula': 'return Address.lookupOne(city=$city).id', 'type': 'Ref:Address'}],
['UpdateRecord', '_grist_Tables_column', 24, {
'formula': 'return Address.lookupOne(city=$city).id', 'type': 'Ref:Address'}],
["BulkUpdateRecord", "Schools", [1, 2, 3, 4], {"grist_Transform": [11, 12, 11, 0]}],
]})
# It seems best if TypeTransform sets widgetOptions on grist_Transform column, so that they
# can be copied in CopyFromColumn; rather than updating them after the copy is done.
self.update_record('_grist_Views_section_field', 1, widgetOptions="hello")
self.update_record('_grist_Tables_column', 24, widgetOptions="world")
out_actions = self.apply_user_action(
['CopyFromColumn', 'Schools', 'grist_Transform', 'city', None])
self.assertPartialOutActions(out_actions, { "stored": [
['ModifyColumn', 'Schools', 'city', {'type': 'Ref:Address'}],
['UpdateRecord', 'Schools', 4, {'city': 0}],
['UpdateRecord', '_grist_Views_section_field', 1, {'widgetOptions': ''}],
['UpdateRecord', '_grist_Tables_column', 23, {
'type': 'Ref:Address', 'widgetOptions': 'world'
}],
['BulkUpdateRecord', 'Schools', [1, 2, 3], {'city': [11, 12, 11]}],
["BulkUpdateRecord", "Schools", [1, 2, 3], {"grist_Transform": [0, 0, 0]}],
]})
out_actions = self.update_record('_grist_Tables_column', 23,
widgetOptions='{"widget":"Reference","visibleCol":"city"}')
self.assertPartialOutActions(out_actions, { "stored": [
['UpdateRecord', '_grist_Tables_column', 23, {
'widgetOptions': '{"widget":"Reference","visibleCol":"city"}'}],
]})
out_actions = self.remove_column('Schools', 'grist_Transform')
self.assertPartialOutActions(out_actions, { "stored": [
['RemoveRecord', '_grist_Views_section_field', 2],
['RemoveRecord', '_grist_Tables_column', 24],
['RemoveColumn', 'Schools', 'grist_Transform'],
]})
#----------------------------------------------------------------------
def test_create_section_existing_view(self):
# Test that CreateViewSection works for an existing view.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
# Create a view + section for the initial table.
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Verify that we got a new view, with one section, and three fields.
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
])
]) ])
# Create a new section for the same view, check that only a section is added.
self.apply_user_action(["CreateViewSection", 1, 1, "record", None])
self.assertTables([self.starting_table])
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
])
]) ])
# Create another section for the same view, this time summarized.
self.apply_user_action(["CreateViewSection", 1, 1, "record", [21]])
summary_table = Table(2, "GristSummary_7_Address", 0, summarySourceTable=1, columns=[
Column(22, "city", "Text", isFormula=False, formula="", summarySourceCol=21),
Column(23, "group", "RefList:Address", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(24, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
self.assertTables([self.starting_table, summary_table])
# Check that we still have one view, with sections for different tables.
view = View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
]),
Section(3, parentKey="record", tableRef=2, fields=[
Field(3, colRef=22),
Field(4, colRef=24),
]),
])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
# Try to create a summary table for an invalid column, and check that it fails.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 1, 1, "record", [23]])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
#----------------------------------------------------------------------
def test_creates_section_new_table(self):
# Test that CreateViewSection works for adding a new table.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
self.assertViews([])
# When we create a section/view for new table, we get both a primary view, and the new view we
# are creating.
self.apply_user_action(["CreateViewSection", 0, 0, "record", None])
new_table = Table(2, "Table1", primaryViewId=1, summarySourceTable=0, columns=[
Column(22, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(23, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(24, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(25, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view = View(1, sections=[
Section(1, parentKey="record", tableRef=2, fields=[
Field(1, colRef=23),
Field(2, colRef=24),
Field(3, colRef=25),
])
])
new_view = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(4, colRef=23),
Field(5, colRef=24),
Field(6, colRef=25),
])
])
self.assertTables([self.starting_table, new_table])
self.assertViews([primary_view, new_view])
# Create another section in an existing view for a new table.
self.apply_user_action(["CreateViewSection", 0, 2, "record", None])
new_table2 = Table(3, "Table2", primaryViewId=3, summarySourceTable=0, columns=[
Column(26, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(27, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(28, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(29, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view2 = View(3, sections=[
Section(3, parentKey="record", tableRef=3, fields=[
Field(7, colRef=27),
Field(8, colRef=28),
Field(9, colRef=29),
])
])
new_view.sections.append(
Section(4, parentKey="record", tableRef=3, fields=[
Field(10, colRef=27),
Field(11, colRef=28),
Field(12, colRef=29),
])
)
# Check that we have a new table, only the primary view as new view; and a new section.
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# Check that we can't create a summary of a table grouped by a column that doesn't exist yet.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 0, 2, "record", [31]])
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# But creating a new table and showing totals for it is possible though dumb.
self.apply_user_action(["CreateViewSection", 0, 2, "record", []])
# We expect a new table.
new_table3 = Table(4, "Table3", primaryViewId=4, summarySourceTable=0, columns=[
Column(30, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(31, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(32, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(33, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
# A summary of it.
summary_table = Table(5, "GristSummary_6_Table3", 0, summarySourceTable=4, columns=[
Column(34, "group", "RefList:Table3", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(35, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
# The primary view of the new table.
primary_view3 = View(4, sections=[
Section(5, parentKey="record", tableRef=4, fields=[
Field(13, colRef=31),
Field(14, colRef=32),
Field(15, colRef=33),
])
])
# And a new view section for the summary.
new_view.sections.append(Section(6, parentKey="record", tableRef=5, fields=[
Field(16, colRef=35)
]))
self.assertTables([self.starting_table, new_table, new_table2, new_table3, summary_table])
self.assertViews([primary_view, new_view, primary_view2, primary_view3])
#----------------------------------------------------------------------
def init_views_sample(self):
# Add a new table and a view, to get some Views/Sections/Fields, and TableView/TabBar items.
self.apply_user_action(['AddTable', 'Schools', [
{'id': 'city', 'type': 'Text'},
{'id': 'state', 'type': 'Text'},
{'id': 'size', 'type': 'Numeric'},
]])
self.apply_user_action(['BulkAddRecord', 'Schools', [1,2,3,4], {
'city': ['New York', 'Colombia', 'New York', ''],
'state': ['NY', 'NY', 'NY', ''],
'size': [1000, 2000, 3000, 4000],
}])
# Add a new view; a second section (summary) to it; and a third view.
self.apply_user_action(['CreateViewSection', 1, 0, 'detail', None])
self.apply_user_action(['CreateViewSection', 1, 2, 'record', [3]])
self.apply_user_action(['CreateViewSection', 1, 0, 'chart', None])
self.apply_user_action(['CreateViewSection', 0, 2, 'record', None])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
Table(2, "GristSummary_7_Schools", 0, 1, columns=[
Column(5, "state", "Text", False, "", 3),
Column(6, "group", "RefList:Schools", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "size", "Numeric", True, "SUM($group.size)", 0),
]),
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2, sections=[
Section(2, parentKey="detail", tableRef=1, fields=[
Field(4, colRef=2),
Field(5, colRef=3),
Field(6, colRef=4),
]),
Section(3, parentKey="record", tableRef=2, fields=[
Field(7, colRef=5),
Field(8, colRef=7),
Field(9, colRef=8),
]),
Section(6, parentKey='record', tableRef=3, fields=[
Field(15, colRef=10),
Field(16, colRef=11),
Field(17, colRef=12),
]),
]),
View(3, sections=[
Section(4, parentKey="chart", tableRef=1, fields=[
Field(10, colRef=2),
Field(11, colRef=3),
]),
]),
View(4, sections=[
Section(5, parentKey='record', tableRef=3, fields=[
Field(12, colRef=10),
Field(13, colRef=11),
Field(14, colRef=12),
]),
]),
])
self.assertTableData('_grist_TableViews', data=[
["id", "tableRef", "viewRef"],
[1, 1, 2],
[2, 1, 3],
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4]
])
#----------------------------------------------------------------------
def test_view_remove(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Remove a view. Ensure related items, sections, fields get removed.
self.apply_user_action(["BulkRemoveRecord", "_grist_Views", [2,3]])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
# Note that the summary table is gone.
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(4, sections=[
Section(5, parentKey='record', tableRef=3, fields=[
Field(12, colRef=10),
Field(13, colRef=11),
Field(14, colRef=12),
]),
]),
])
self.assertTableData('_grist_TableViews', data=[
["id", "tableRef", "viewRef"],
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
#----------------------------------------------------------------------
def test_view_rename(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Verify the new structure of tables and views.
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'Table1', 4],
])
self.assertTableData('_grist_Views', cols="subset", data=[
[ 'id', 'name', 'primaryViewTable' ],
[ 1, 'Schools', 1],
[ 2, 'New page', 0],
[ 3, 'New page', 0],
[ 4, 'Table1', 3],
])
# Update the names in a few views, and ensure that primary ones cause tables to get renamed.
self.apply_user_action(['BulkUpdateRecord', '_grist_Views', [2,3,4],
{'name': ['A', 'B', 'C']}])
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'C', 4],
])
self.assertTableData('_grist_Views', cols="subset", data=[
[ 'id', 'name', 'primaryViewTable' ],
[ 1, 'Schools', 1],
[ 2, 'A', 0],
[ 3, 'B', 0],
[ 4, 'C', 3]
])
#----------------------------------------------------------------------
def test_section_removes(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Remove a couple of sections. Ensure their fields get removed.
self.apply_user_action(['BulkRemoveRecord', '_grist_Views_section', [3,6]])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2, sections=[
Section(2, parentKey="detail", tableRef=1, fields=[
Field(4, colRef=2),
Field(5, colRef=3),
Field(6, colRef=4),
]),
]),
View(3, sections=[
Section(4, parentKey="chart", tableRef=1, fields=[
Field(10, colRef=2),
Field(11, colRef=3),
]),
]),
View(4, sections=[
Section(5, parentKey='record', tableRef=3, fields=[
Field(12, colRef=10),
Field(13, colRef=11),
Field(14, colRef=12),
]),
]),
])
#----------------------------------------------------------------------
def test_schema_consistency_check(self):
# Verify that schema consistency check actually runs, but only when schema is affected.
self.init_views_sample()
# Replace the engine's assert_schema_consistent() method with a mocked version.
orig_method = self.engine.assert_schema_consistent
count_calls = [0]
def override(self): # pylint: disable=unused-argument
count_calls[0] += 1
# pylint: disable=not-callable
orig_method()
self.engine.assert_schema_consistent = types.MethodType(override, self.engine)
# Do a non-sschema action to ensure it doesn't get called.
self.apply_user_action(['UpdateRecord', '_grist_Views', 2, {'name': 'A'}])
self.assertEqual(count_calls[0], 0)
# Do a schema action to ensure it gets called: this causes a table rename.
self.apply_user_action(['UpdateRecord', '_grist_Views', 4, {'name': 'C'}])
self.assertEqual(count_calls[0], 1)
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'C', 4],
])
# Do another schema and non-schema action.
self.apply_user_action(['UpdateRecord', 'Schools', 1, {'city': 'Seattle'}])
self.assertEqual(count_calls[0], 1)
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 2, {'colId': 'city2'}])
self.assertEqual(count_calls[0], 2)
#----------------------------------------------------------------------
def test_new_column_conversions(self):
self.init_views_sample()
self.apply_user_action(['AddColumn', 'Schools', None, {}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Any", True, ""],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A"],
[1, "New York", None],
[2, "Colombia", None],
[3, "New York", None],
[4, "", None],
])
# Check that typing in text into the column produces a text column.
out_actions = self.apply_user_action(['UpdateRecord', 'Schools', 3, {"A": "foo"}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Text", False, ""],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", "" ],
[2, "Colombia", "" ],
[3, "New York", "foo" ],
[4, "", "" ],
])
# Undo, and check that typing in a number produces a numeric column.
self.apply_undo_actions(out_actions.undo)
out_actions = self.apply_user_action(['UpdateRecord', 'Schools', 3, {"A": " -17.6"}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Numeric", False, ""],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 0.0 ],
[2, "Colombia", 0.0 ],
[3, "New York", -17.6 ],
[4, "", 0.0 ],
])
# Undo, and set a formula for the new column instead.
self.apply_undo_actions(out_actions.undo)
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 13, {'formula': 'len($city)'}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Any", True, "len($city)"],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 8 ],
[2, "Colombia", 8 ],
[3, "New York", 8 ],
[4, "", 0 ],
])
# Convert the formula column to non-formula.
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 13, {'isFormula': False}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Numeric", False, "len($city)"],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 8 ],
[2, "Colombia", 8 ],
[3, "New York", 8 ],
[4, "", 0 ],
])
# Add some more formula columns of type 'Any'.
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "1"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "'x'"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "$city == 'New York'"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "$city=='New York' or '-'"}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Numeric", False, "len($city)"],
[14, "B", "Any", True, "1"],
[15, "C", "Any", True, "'x'"],
[16, "D", "Any", True, "$city == 'New York'"],
[17, "E", "Any", True, "$city=='New York' or '-'"],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A", "B", "C", "D", "E"],
[1, "New York", 8, 1, "x", True, True],
[2, "Colombia", 8, 1, "x", False, '-' ],
[3, "New York", 8, 1, "x", True, True],
[4, "", 0, 1, "x", False, '-' ],
])
# Convert all these formulas to non-formulas, and see that their types get guessed OK.
# TODO: We should also guess Int, Bool, Reference, ReferenceList, Date, and DateTime.
# TODO: It is possibly better if B became Int, and D became Bool.
self.apply_user_action(['BulkUpdateRecord', '_grist_Tables_column', [14,15,16,17],
{'isFormula': [False, False, False, False]}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Numeric", False, "len($city)"],
[14, "B", "Numeric", False, "1"],
[15, "C", "Text", False, "'x'"],
[16, "D", "Text", False, "$city == 'New York'"],
[17, "E", "Text", False, "$city=='New York' or '-'"],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A", "B", "C", "D", "E"],
[1, "New York", 8, 1.0, "x", "True", 'True'],
[2, "Colombia", 8, 1.0, "x", "False", '-' ],
[3, "New York", 8, 1.0, "x", "True", 'True'],
[4, "", 0, 1.0, "x", "False", '-' ],
])
#----------------------------------------------------------------------
def test_useraction_failures(self):
# Verify that when a useraction fails, we revert any changes already applied.
self.load_sample(self.sample)
# Simple failure: bad action (last argument should be a dict). It shouldn't cause any actions
# in the first place, just raise an exception about the argument being an int.
with self.assertRaisesRegexp(AttributeError, r"'int'"):
self.apply_user_action(['AddColumn', 'Address', "A", 17])
# Do some successful actions, just to make sure we know what they look like.
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['AddColumn', 'Address', "B", {"isFormula": True}],
['UpdateRecord', 'Address', 11, {"city": "New York2"}],
)])
# More complicated: here some actions should succeed, but get reverted when a later one fails.
with self.assertRaisesRegexp(AttributeError, r"'int'"):
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['UpdateRecord', 'Address', 11, {"city": "New York3"}],
['AddColumn', 'Address', "C", {"isFormula": True}],
['AddColumn', 'Address', "D", 17]
)])
with self.assertRaisesRegexp(Exception, r"non-existent record #77"):
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['UpdateRecord', 'Address', 11, {"city": "New York4"}],
['UpdateRecord', 'Address', 77, {"city": "Chicago"}],
)])
# Make sure that no columns got added except the intentionally successful one.
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[21, "city", "Text", False, ""],
[22, "B", "Any", True, ""],
], rows=lambda r: r.parentId.id == 1)
# Make sure that no columns got added here either, and the only change to "New York" is the
# one in the successful user-action.
self.assertTableData('Address', cols="all", data=[
["id", "city" , "B" ],
[11, "New York2" , None ],
[12, "Colombia" , None ],
[13, "New Haven" , None ],
[14, "West Haven", None ],
])
#----------------------------------------------------------------------
def test_pages_remove(self):
# Test that orphan pages get fixed after removing a page
self.init_views_sample()
# Moves page 2 to children of page 1.
self.apply_user_action(['BulkUpdateRecord', '_grist_Pages', [2], {'indentation': [1]}])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 1, 0],
[ 2, 1],
[ 3, 0],
[ 4, 0],
])
# Verify that removing page 1 fixes page 2 indentation.
self.apply_user_action(['RemoveRecord', '_grist_Pages', 1])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 2, 0],
[ 3, 0],
[ 4, 0],
])
# Removing last page should not fail
# Verify that removing page 1 fixes page 2 indentation.
self.apply_user_action(['RemoveRecord', '_grist_Pages', 4])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 2, 0],
[ 3, 0],
])
# Removing a page that has no children should do nothing
self.apply_user_action(['RemoveRecord', '_grist_Pages', 2])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 3, 0],
])
def test_summary_column_removals(self):
# Verify that when we remove a column used for summary-table group-by, it updates summary
# tables appropriately.
self.init_sample_data()
# Test that we cannot remove group-by columns from summary tables directly.
with self.assertRaisesRegex(ValueError, "cannot remove .* group-by"):
self.apply_user_action(
["BulkRemoveRecord", '_grist_Tables_column', [20, 18]])
# Test that group-by columns in summary tables get removed.
self.apply_user_action(
["BulkRemoveRecord", '_grist_Tables_column', [11, 12, 16]])
# Verify the new structure of tables and views.
self.assertTables([
Table(1,
"Address",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(13, "amount", "Numeric", False, "", 0),
]),
Table(2,
"Table1",
2,
0,
columns=[
Column(14, "manualSort", "ManualSortPos", False, "", 0),
Column(15, "A", "Text", False, "", 0),
Column(17, "C", "Text", False, "", 0),
]),
# Note that the summary table here switches to a new one, without the deleted group-by.
Table(4,
"GristSummary_7_Address2",
0,
1,
columns=[
Column(22,
"count",
"Int",
True,
summarySourceCol=0,
formula="len($group)"),
Column(23,
"amount",
"Numeric",
True,
summarySourceCol=0,
formula="SUM($group.amount)"),
Column(24,
"group",
"RefList:Address",
True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
]),
])
self.assertViews([
View(1,
sections=[
Section(1,
parentKey="record",
tableRef=1,
fields=[
Field(3, colRef=13),
]),
Section(4,
parentKey="record",
tableRef=2,
fields=[
Field(10, colRef=15),
Field(12, colRef=17),
]),
Section(5,
parentKey="record",
tableRef=4,
fields=[
Field(14, colRef=22),
Field(15, colRef=23),
]),
]),
View(2,
sections=[
Section(2,
parentKey="record",
tableRef=2,
fields=[
Field(4, colRef=15),
Field(6, colRef=17),
]),
])
])
# Verify the data itself.
self.assertTableData('Address',
data=[
["id", "amount"],
[21, 1.],
[22, 2.],
[23, 3.],
[24, 4.],
[25, 5.],
[26, 6.],
[27, 7.],
[28, 8.],
[29, 9.],
[30, 10.],
[31, 11.],
])
self.assertTableData('Table1',
data=[
["id", "A", "C", "manualSort"],
[1, "a", "", 1.0],
[2, "b", "", 2.0],
[3, "c", "", 3.0],
])
self.assertTableData("GristSummary_7_Address2",
cols="subset",
data=[
["id", "count", "amount"],
[
1, 7 + 1 + 1 + 2, 1. + 2 + 6 + 7 + 8 +
10 + 11 + 3 + 4 + 5 + 9
],
])
def test_column_removals(self):
# Verify removal of fields when columns are removed.
self.init_sample_data()
# Add link{Src,Target}ColRef to ViewSections. These aren't actually meaningful links, but they
# should still get cleared automatically when columns get removed.
self.apply_user_action([
'UpdateRecord', '_grist_Views_section', 2, {
'linkSrcSectionRef': 1,
'linkSrcColRef': 11,
'linkTargetColRef': 16
}
])
self.assertTableData('_grist_Views_section',
cols="subset",
rows="subset",
data=[
[
"id", "linkSrcSectionRef",
"linkSrcColRef", "linkTargetColRef"
],
[2, 1, 11, 16],
])
# Test that we can remove multiple columns using BulkUpdateRecord.
self.apply_user_action(
["BulkRemoveRecord", '_grist_Tables_column', [11, 16]])
# Test that link{Src,Target}colRef back-references get unset.
self.assertTableData('_grist_Views_section',
cols="subset",
rows="subset",
data=[
[
"id", "linkSrcSectionRef",
"linkSrcColRef", "linkTargetColRef"
],
[2, 1, 0, 0],
])
# Test that columns and section fields got removed.
self.assertTables([
Table(1,
"Address",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(12, "state", "Text", False, "", 0),
Column(13, "amount", "Numeric", False, "", 0),
]),
Table(2,
"Table1",
2,
0,
columns=[
Column(14, "manualSort", "ManualSortPos", False, "", 0),
Column(15, "A", "Text", False, "", 0),
Column(17, "C", "Text", False, "", 0),
]),
Table(3,
"GristSummary_7_Address",
0,
1,
columns=[
Column(18,
"state",
"Text",
False,
"",
summarySourceCol=12),
Column(19,
"group",
"RefList:Address",
True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(20,
"count",
"Int",
True,
summarySourceCol=0,
formula="len($group)"),
Column(21,
"amount",
"Numeric",
True,
summarySourceCol=0,
formula="SUM($group.amount)"),
]),
])
self.assertViews([
View(1,
sections=[
Section(1,
parentKey="record",
tableRef=1,
fields=[
Field(2, colRef=12),
Field(3, colRef=13),
]),
Section(4,
parentKey="record",
tableRef=2,
fields=[
Field(10, colRef=15),
Field(12, colRef=17),
]),
Section(5,
parentKey="record",
tableRef=3,
fields=[
Field(13, colRef=18),
Field(14, colRef=20),
Field(15, colRef=21),
]),
]),
View(2,
sections=[
Section(2,
parentKey="record",
tableRef=2,
fields=[
Field(4, colRef=15),
Field(6, colRef=17),
]),
])
])
def test_ref_list_relation(self):
# Create two tables, the second referring to the first using a RefList and a Ref column.
self.apply_user_action(
["AddTable", "TableA", [{
"id": "ColA",
"type": "Text"
}]])
self.apply_user_action([
"AddTable", "TableB",
[
{
"id": "ColB",
"type": "Text"
},
{
"id": "group",
"type": "RefList:TableA",
"isFormula": True,
"formula": "TableA.lookupRecords(ColA=$ColB)"
},
{
"id": "ref",
"type": "Ref:TableA",
"isFormula": True,
"formula": "TableA.lookupOne(ColA=$ColB)"
},
]
])
# Populate the tables with some data.
self.apply_user_action([
"BulkAddRecord", "TableA", [None] * 4, {
"ColA": ["a", "b", "c", "d"]
}
])
self.apply_user_action(
["BulkAddRecord", "TableB", [None] * 3, {
"ColB": ["d", "b", "a"]
}])
# Rename the second table. This causes some Column objects to be re-created and copied from
# the previous table instance. This logic had a bug.
self.apply_user_action(["RenameTable", "TableB", "TableC"])
# Let's see what we've set up here.
self.assertTables([
Table(1,
"TableA",
1,
0,
columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "ColA", "Text", False, "", 0),
]),
Table(2,
"TableC",
2,
0,
columns=[
Column(3, "manualSort", "ManualSortPos", False, "", 0),
Column(4, "ColB", "Text", False, "", 0),
Column(5, "group", "RefList:TableA", True,
"TableA.lookupRecords(ColA=$ColB)", 0),
Column(6, "ref", "Ref:TableA", True,
"TableA.lookupOne(ColA=$ColB)", 0),
]),
])
self.assertTableData('TableA',
cols="subset",
data=[
["id", "ColA"],
[
1,
"a",
],
[
2,
"b",
],
[
3,
"c",
],
[
4,
"d",
],
])
self.assertTableData('TableC',
cols="subset",
data=[
["id", "ColB", "group", "ref"],
[1, "d", [4], 4],
[2, "b", [2], 2],
[3, "a", [1], 1],
])
# Now when the logic was buggy, this sequence of action, as emitted by a user-initiated column
# conversion, triggered an internal exception. Ensure it no longer happens.
self.apply_user_action([
'AddColumn', 'TableC', 'gristHelper_Transform', {
"type": 'Ref:TableA',
"isFormula": True,
"formula":
"grist.Reference.typeConvert($ColB, TableA, 'ColA')",
"visibleCol": 2,
}
])
self.apply_user_action([
'SetDisplayFormula', 'TableC', None, 7,
'$gristHelper_Transform.ColA'
])
self.apply_user_action([
'CopyFromColumn', 'TableC', 'gristHelper_Transform', 'ColB',
'{"widget":"Reference"}'
])
self.apply_user_action(
['RemoveColumn', 'TableC', 'gristHelper_Transform'])
# Check what we have now.
self.assertTables([
Table(1,
"TableA",
1,
0,
columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "ColA", "Text", False, "", 0),
]),
Table(2,
"TableC",
2,
0,
columns=[
Column(3, "manualSort", "ManualSortPos", False, "", 0),
Column(4, "ColB", "Ref:TableA", False, "", 0),
Column(5, "group", "RefList:TableA", True,
"TableA.lookupRecords(ColA=$ColB)", 0),
Column(6, "ref", "Ref:TableA", True,
"TableA.lookupOne(ColA=$ColB)", 0),
Column(9, "gristHelper_Display2", "Any", True,
"$ColB.ColA", 0),
]),
])
self.assertTableData('TableA',
cols="subset",
data=[
["id", "ColA"],
[
1,
"a",
],
[
2,
"b",
],
[
3,
"c",
],
[
4,
"d",
],
])
self.assertTableData(
'TableC',
cols="subset",
data=[
["id", "ColB", "gristHelper_Display2", "group", "ref"],
[1, 4, "d", [], 0],
[2, 2, "b", [], 0],
[3, 1, "a", [], 0],
])
class TestSummaryChoiceList(EngineTestCase):
sample = testutil.parse_test_sample({
"SCHEMA": [[
1, "Source",
[
[10, "other", "Text", False, "", "other", ""],
[11, "choices1", "ChoiceList", False, "", "choices1", ""],
[12, "choices2", "ChoiceList", False, "", "choices2", ""],
]
]],
"DATA": {
"Source": [
["id", "choices1", "choices2", "other"],
[21, ["a", "b"], ["c", "d"], "foo"],
]
}
})
starting_table = Table(1,
"Source",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(10,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=0),
Column(11,
"choices1",
"ChoiceList",
isFormula=False,
formula="",
summarySourceCol=0),
Column(12,
"choices2",
"ChoiceList",
isFormula=False,
formula="",
summarySourceCol=0),
])
# ----------------------------------------------------------------------
def test_summary_by_choice_list(self):
self.load_sample(self.sample)
# Verify the starting table; there should be no views yet.
self.assertTables([self.starting_table])
self.assertViews([])
# Create a summary section, grouped by the "choices1" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]])
summary_table1 = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(13,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(14,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(15,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by both choicelist columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]])
summary_table2 = Table(
3,
"GristSummary_6_Source2",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(16,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(17,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=12),
Column(18,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(19,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by the non-choicelist column
self.apply_user_action(["CreateViewSection", 1, 0, "record", [10]])
summary_table3 = Table(
4,
"GristSummary_6_Source3",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(20,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=10),
Column(21,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(22,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
# Create another summary section, grouped by the non-choicelist column and choices1
self.apply_user_action(["CreateViewSection", 1, 0, "record", [10, 11]])
summary_table4 = Table(
5,
"GristSummary_6_Source4",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(23,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=10),
Column(24,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(25,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(26,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
self.assertTables([
self.starting_table, summary_table1, summary_table2,
summary_table3, summary_table4
])
# Verify the summarized data.
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [21], 1],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [21], 1],
[4, "b", "d", [21], 1],
])
self.assertTableData('GristSummary_6_Source3',
data=[
["id", "other", "group", "count"],
[1, "foo", [21], 1],
])
self.assertTableData('GristSummary_6_Source4',
data=[
["id", "other", "choices1", "group", "count"],
[1, "foo", "a", [21], 1],
[2, "foo", "b", [21], 1],
])
# Verify the optimisation works for the table without choicelists
self.assertIs(self.engine.tables["Source"]._summary_simple, None)
self.assertIs(
self.engine.tables["GristSummary_6_Source"]._summary_simple, False)
self.assertIs(
self.engine.tables["GristSummary_6_Source2"]._summary_simple,
False)
# simple summary and lookup
self.assertIs(
self.engine.tables["GristSummary_6_Source3"]._summary_simple, True)
self.assertIs(
self.engine.tables["GristSummary_6_Source4"]._summary_simple,
False)
self.assertEqual(
{
k: type(v)
for k, v in self.engine.tables["Source"]._special_cols.items()
},
{
'#summary#GristSummary_6_Source':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source')":
lookup.ContainsLookupMapColumn,
'#summary#GristSummary_6_Source2':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source2')":
lookup.ContainsLookupMapColumn,
# simple summary and lookup
'#summary#GristSummary_6_Source3':
column.ReferenceColumn,
'#lookup##summary#GristSummary_6_Source3':
lookup.SimpleLookupMapColumn,
'#summary#GristSummary_6_Source4':
column.ReferenceListColumn,
"#lookup#_Contains(value='#summary#GristSummary_6_Source4')":
lookup.ContainsLookupMapColumn,
})
# Remove 'b' from choices1
self.update_record("Source", 21, choices1=["L", "a"])
self.assertTableData('Source',
data=[
["id", "choices1", "choices2", "other"],
[21, ["a"], ["c", "d"], "foo"],
])
# Verify that the summary table rows containing 'b' are empty
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [], 0],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
])
# Add 'e' to choices2
self.update_record("Source", 21, choices2=["L", "c", "d", "e"])
# First summary table unaffected
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [], 0],
])
# New row added for 'e'
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [21], 1],
[2, "a", "d", [21], 1],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "a", "e", [21], 1],
])
# Remove record from source
self.remove_record("Source", 21)
# All summary rows are now empty
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [], 0],
[2, "b", [], 0],
])
self.assertTableData(
'GristSummary_6_Source2',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [], 0],
[2, "a", "d", [], 0],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "a", "e", [], 0],
])
# Make rows with every combination of {a,b,ab} and {c,d,cd}
self.add_records('Source', ["id", "choices1", "choices2"], [
[101, ["L", "a"], ["L", "c"]],
[102, ["L", "b"], ["L", "c"]],
[103, ["L", "a", "b"], ["L", "c"]],
[104, ["L", "a"], ["L", "d"]],
[105, ["L", "b"], ["L", "d"]],
[106, ["L", "a", "b"], ["L", "d"]],
[107, ["L", "a"], ["L", "c", "d"]],
[108, ["L", "b"], ["L", "c", "d"]],
[109, ["L", "a", "b"], ["L", "c", "d"]],
])
self.assertTableData('Source',
cols="subset",
data=[
["id", "choices1", "choices2"],
[101, ["a"], ["c"]],
[102, ["b"], ["c"]],
[103, ["a", "b"], ["c"]],
[104, ["a"], ["d"]],
[105, ["b"], ["d"]],
[106, ["a", "b"], ["d"]],
[107, ["a"], ["c", "d"]],
[108, ["b"], ["c", "d"]],
[109, ["a", "b"], ["c", "d"]],
])
# Summary tables now have an even distribution of combinations
self.assertTableData('GristSummary_6_Source',
data=[
["id", "choices1", "group", "count"],
[1, "a", [101, 103, 104, 106, 107, 109], 6],
[2, "b", [102, 103, 105, 106, 108, 109], 6],
])
summary_data = [
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [101, 103, 107, 109], 4],
[2, "a", "d", [104, 106, 107, 109], 4],
[3, "b", "c", [102, 103, 108, 109], 4],
[4, "b", "d", [105, 106, 108, 109], 4],
[5, "a", "e", [], 0],
]
self.assertTableData('GristSummary_6_Source2', data=summary_data)
# Verify that "DetachSummaryViewSection" useraction works correctly.
self.apply_user_action(["DetachSummaryViewSection", 2])
self.assertTables([
self.starting_table, summary_table1, summary_table3,
summary_table4,
Table(
6,
"Table1",
primaryViewId=5,
summarySourceTable=0,
columns=[
Column(27,
"manualSort",
"ManualSortPos",
isFormula=False,
formula="",
summarySourceCol=0),
Column(28,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
Column(29,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
Column(30,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
Column(
31,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula=
"Source.lookupRecords(choices1=CONTAINS($choices1), choices2=CONTAINS($choices2))"
),
],
)
])
self.assertTableData('Table1', data=summary_data, cols="subset")
def test_change_choice_to_choicelist(self):
sample = testutil.parse_test_sample({
"SCHEMA": [[
1, "Source",
[
[10, "other", "Text", False, "", "other", ""],
[11, "choices1", "Choice", False, "", "choice", ""],
]
]],
"DATA": {
"Source": [
["id", "choices1", "other"],
[21, "a", "foo"],
[22, "b", "bar"],
]
}
})
starting_table = Table(1,
"Source",
primaryViewId=0,
summarySourceTable=0,
columns=[
Column(10,
"other",
"Text",
isFormula=False,
formula="",
summarySourceCol=0),
Column(11,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=0),
])
self.load_sample(sample)
# Verify the starting table; there should be no views yet.
self.assertTables([starting_table])
self.assertViews([])
# Create a summary section, grouped by the "choices1" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11]])
summary_table = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(12,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(13,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(14,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
data = [
["id", "choices1", "group", "count"],
[1, "a", [21], 1],
[2, "b", [22], 1],
]
self.assertTables([starting_table, summary_table])
self.assertTableData('GristSummary_6_Source', data=data)
# Change the column from Choice to ChoiceList
self.apply_user_action([
"UpdateRecord", "_grist_Tables_column", 11, {
"type": "ChoiceList"
}
])
# Changing type in reality is a bit more complex than these actions
# so we put the correct values in place directly
self.apply_user_action([
"BulkUpdateRecord", "Source", [21, 22], {
"choices1": [["L", "a"], ["L", "b"]]
}
])
starting_table.columns[1] = starting_table.columns[1]._replace(
type="ChoiceList")
self.assertTables([starting_table, summary_table])
self.assertTableData('GristSummary_6_Source', data=data)
def test_rename_choices(self):
self.load_sample(self.sample)
# Create a summary section, grouped by both choicelist columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]])
summary_table = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(13,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(14,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=12),
Column(15,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(16,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
self.assertTables([self.starting_table, summary_table])
# Rename all the choices
out_actions = self.apply_user_action(
["RenameChoices", "Source", "choices1", {
"a": "aa",
"b": "bb"
}])
self.apply_user_action(
["RenameChoices", "Source", "choices2", {
"c": "cc",
"d": "dd"
}])
# Actions from renaming choices1 only
self.assertPartialOutActions(
out_actions, {
'stored':
[[
'UpdateRecord', 'Source', 21, {
'choices1': ['L', u'aa', u'bb']
}
],
[
'BulkAddRecord', 'GristSummary_6_Source', [5, 6, 7, 8], {
'choices1': [u'aa', u'aa', u'bb', u'bb'],
'choices2': [u'c', u'd', u'c', u'd']
}
],
[
'BulkUpdateRecord', 'GristSummary_6_Source',
[1, 2, 3, 4, 5, 6, 7, 8], {
'count': [0, 0, 0, 0, 1, 1, 1, 1]
}
],
[
'BulkUpdateRecord', 'GristSummary_6_Source',
[1, 2, 3, 4, 5, 6, 7, 8], {
'group': [['L'], ['L'], ['L'], ['L'], ['L', 21],
['L', 21], ['L', 21], ['L', 21]]
}
]]
})
# Final Source table is essentially the same as before, just with each letter doubled
self.assertTableData('Source',
data=[
["id", "choices1", "choices2", "other"],
[21, ["aa", "bb"], ["cc", "dd"], "foo"],
])
# Final summary table is very similar to before, but with two empty chunks of 4 rows
# left over from each rename
self.assertTableData(
'GristSummary_6_Source',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [], 0],
[2, "a", "d", [], 0],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "aa", "c", [], 0],
[6, "aa", "d", [], 0],
[7, "bb", "c", [], 0],
[8, "bb", "d", [], 0],
[9, "aa", "cc", [21], 1],
[10, "aa", "dd", [21], 1],
[11, "bb", "cc", [21], 1],
[12, "bb", "dd", [21], 1],
])
def test_display_cols(self):
# Test the implementation of display columns which adds a column modified by
# a formula as a display version of the original column.
self.load_sample(self.ref_sample)
# Add a new table for People so that we get the associated views and fields.
self.apply_user_action([
'AddTable', 'Favorites',
[{
'id': 'favorite',
'type': 'Ref:Television'
}]
])
self.apply_user_action([
'BulkAddRecord', 'Favorites', [1, 2, 3, 4, 5], {
'favorite': [2, 4, 1, 4, 3]
}
])
self.assertTables([
Table(1,
"Television",
0,
0,
columns=[
Column(21, "show", "Text", False, "", 0),
Column(22, "network", "Text", False, "", 0),
Column(23, "viewers", "Int", False, "", 0),
]),
Table(2,
"Favorites",
1,
0,
columns=[
Column(24, "manualSort", "ManualSortPos", False, "", 0),
Column(25, "favorite", "Ref:Television", False, "", 0),
]),
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 0],
[2, 25, 0],
])
self.assertTableData("Favorites",
cols="subset",
data=[["id", "favorite"], [1, 2], [2, 4], [3, 1],
[4, 4], [5, 3]])
# Add an extra view for the new table to test multiple fields at once
self.apply_user_action(
['AddView', 'Favorites', 'raw_data', 'Extra View'])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 0],
[2, 25, 0],
[3, 25, 0],
])
# Set display formula for 'favorite' column.
# A "gristHelper_Display" column with the requested formula should be added and set as the
# displayCol of the favorite column.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', None, 25, '$favorite.show'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"]])
# Set display formula for 'favorite' column fields.
# A single "gristHelper_Display2" column should be added with the requested formula, since both
# require the same formula. The fields' colRefs should be set to the new column.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 1, None, '$favorite.network'])
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 3, None, '$favorite.network'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[27, "gristHelper_Display2", 2, 0, "$favorite.network"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 27],
[2, 25, 0],
[3, 25, 27],
])
# Change display formula for a field.
# Since the field is changing to use a formula not yet held by a display column,
# a new display column should be added with the desired formula.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 3, None, '$favorite.viewers'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[27, "gristHelper_Display2", 2, 0, "$favorite.network"],
[28, "gristHelper_Display3", 2, 0, "$favorite.viewers"]])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 27],
[2, 25, 0],
[3, 25, 28],
])
# Remove a field.
# This should also remove the display column used by that field, since it is not used
# by any other fields.
self.apply_user_action(
['RemoveRecord', '_grist_Views_section_field', 3])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[27, "gristHelper_Display2", 2, 0, "$favorite.network"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 27],
[2, 25, 0],
])
# Add a new column with a formula.
self.apply_user_action([
'AddColumn', 'Favorites', 'fav_viewers', {
'formula': '$favorite.viewers'
}
])
# Add a field back for the favorites table and set its display formula to the
# same formula that the new column has. Make sure that the new column is NOT used as
# the display column.
self.apply_user_action([
'AddRecord', '_grist_Views_section_field', None, {
'parentId': 3,
'colRef': 25
}
])
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 6, None, '$favorite.viewers'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[27, "gristHelper_Display2", 2, 0, "$favorite.network"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"],
[29, "gristHelper_Display3", 2, 0, "$favorite.viewers"]])
self.assertTableData(
"_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 27],
[2, 25, 0],
[3, 28, 0], # fav_viewers field
[4, 28, 0], # fav_viewers field
[5, 28, 0], # fav_viewers field
[6, 25, 29] # re-added field w/ display col
])
# Change the display formula for a field to be the same as the other field, then remove
# the field.
# The display column should not be removed since it is still in use.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 6, None, '$favorite.network'])
self.apply_user_action(
['RemoveRecord', '_grist_Views_section_field', 6])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[27, "gristHelper_Display2", 2, 0, "$favorite.network"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 27],
[2, 25, 0],
[3, 28, 0],
[4, 28, 0],
[5, 28, 0],
])
# Clear field display formula, then set it again.
# Clearing the display formula should remove the display column, since it is no longer
# used by any column or field.
self.apply_user_action(['SetDisplayFormula', 'Favorites', 1, None, ''])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 0],
[2, 25, 0],
[3, 28, 0],
[4, 28, 0],
[5, 28, 0],
])
# Setting the display formula should add another display column.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', 1, None, '$favorite.viewers'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.show"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"],
[29, "gristHelper_Display2", 2, 0, "$favorite.viewers"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 29],
[2, 25, 0],
[3, 28, 0],
[4, 28, 0],
[5, 28, 0],
])
# Change column display formula.
# This should re-use the current display column since it is only used by the column.
self.apply_user_action(
['SetDisplayFormula', 'Favorites', None, 25, '$favorite.network'])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[
["id", "colId", "parentId", "displayCol", "formula"],
[25, "favorite", 2, 26, ""],
[26, "gristHelper_Display", 2, 0, "$favorite.network"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"],
[29, "gristHelper_Display2", 2, 0, "$favorite.viewers"],
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[1, 25, 29],
[2, 25, 0],
[3, 28, 0],
[4, 28, 0],
[5, 28, 0],
])
# Remove column.
# This should remove the display column used by the column.
self.apply_user_action(['RemoveColumn', "Favorites", "favorite"])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.id >= 25),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[28, "fav_viewers", 2, 0, "$favorite.viewers"]])
self.assertTableData("_grist_Views_section_field",
cols="subset",
data=[
["id", "colRef", "displayCol"],
[3, 28, 0],
[4, 28, 0],
[5, 28, 0],
])
def test_rename_choices(self):
self.load_sample(self.sample)
# Create a summary section, grouped by both choicelist columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11, 12]])
summary_table = Table(
2,
"GristSummary_6_Source",
primaryViewId=0,
summarySourceTable=1,
columns=[
Column(13,
"choices1",
"Choice",
isFormula=False,
formula="",
summarySourceCol=11),
Column(14,
"choices2",
"Choice",
isFormula=False,
formula="",
summarySourceCol=12),
Column(15,
"group",
"RefList:Source",
isFormula=True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(16,
"count",
"Int",
isFormula=True,
summarySourceCol=0,
formula="len($group)"),
],
)
self.assertTables([self.starting_table, summary_table])
# Rename all the choices
out_actions = self.apply_user_action(
["RenameChoices", "Source", "choices1", {
"a": "aa",
"b": "bb"
}])
self.apply_user_action(
["RenameChoices", "Source", "choices2", {
"c": "cc",
"d": "dd"
}])
# Actions from renaming choices1 only
self.assertPartialOutActions(
out_actions, {
'stored':
[[
'UpdateRecord', 'Source', 21, {
'choices1': ['L', u'aa', u'bb']
}
],
[
'BulkAddRecord', 'GristSummary_6_Source', [5, 6, 7, 8], {
'choices1': [u'aa', u'aa', u'bb', u'bb'],
'choices2': [u'c', u'd', u'c', u'd']
}
],
[
'BulkUpdateRecord', 'GristSummary_6_Source',
[1, 2, 3, 4, 5, 6, 7, 8], {
'count': [0, 0, 0, 0, 1, 1, 1, 1]
}
],
[
'BulkUpdateRecord', 'GristSummary_6_Source',
[1, 2, 3, 4, 5, 6, 7, 8], {
'group': [['L'], ['L'], ['L'], ['L'], ['L', 21],
['L', 21], ['L', 21], ['L', 21]]
}
]]
})
# Final Source table is essentially the same as before, just with each letter doubled
self.assertTableData('Source',
data=[
["id", "choices1", "choices2", "other"],
[21, ["aa", "bb"], ["cc", "dd"], "foo"],
])
# Final summary table is very similar to before, but with two empty chunks of 4 rows
# left over from each rename
self.assertTableData(
'GristSummary_6_Source',
data=[
["id", "choices1", "choices2", "group", "count"],
[1, "a", "c", [], 0],
[2, "a", "d", [], 0],
[3, "b", "c", [], 0],
[4, "b", "d", [], 0],
[5, "aa", "c", [], 0],
[6, "aa", "d", [], 0],
[7, "bb", "c", [], 0],
[8, "bb", "d", [], 0],
[9, "aa", "cc", [21], 1],
[10, "aa", "dd", [21], 1],
[11, "bb", "cc", [21], 1],
[12, "bb", "dd", [21], 1],
])
def test_display_col_and_field_removal(self):
# When there are different displayCols associated with the column and with the field, removal
# takes more steps, and order of produced actions matters.
self.load_sample(self.ref_sample)
# Add a table for people, which includes an associated view.
self.apply_user_action([
'AddTable', 'People',
[
{
'id': 'name',
'type': 'Text'
},
{
'id':
'favorite',
'type':
'Ref:Television',
'widgetOptions':
'\"{\"alignment\":\"center\",\"visibleCol\":\"show\"}\"'
},
]
])
self.apply_user_action([
'BulkAddRecord', 'People', [1, 2, 3], {
'name': ['Bob', 'Jim', 'Don'],
'favorite': [12, 11, 13]
}
])
# Add a display formula for the 'favorite' column. A "gristHelper_Display" column with the
# requested formula should be added and set as the displayCol of the favorite column.
self.apply_user_action(
['SetDisplayFormula', 'People', None, 26, '$favorite.show'])
# Set display formula for 'favorite' column field.
# A single "gristHelper_Display2" column should be added with the requested formula.
self.apply_user_action(
['SetDisplayFormula', 'People', 2, None, '$favorite.network'])
expected_tables1 = [
Table(1,
"Television",
0,
0,
columns=[
Column(21, "show", "Text", False, "", 0),
Column(22, "network", "Text", False, "", 0),
Column(23, "viewers", "Int", False, "", 0),
]),
Table(2,
"People",
1,
0,
columns=[
Column(24, "manualSort", "ManualSortPos", False, "", 0),
Column(25, "name", "Text", False, "", 0),
Column(26, "favorite", "Ref:Television", False, "", 0),
Column(27, "gristHelper_Display", "Any", True,
"$favorite.show", 0),
Column(28, "gristHelper_Display2", "Any", True,
"$favorite.network", 0)
]),
]
expected_data1 = [[
"id", "name", "favorite", "gristHelper_Display",
"gristHelper_Display2"
], [1, "Bob", 12, "Narcos", "Netflix"],
[2, "Jim", 11, "Game of Thrones", "HBO"],
[3, "Don", 13, "Today", "NBC"]]
self.assertTables(expected_tables1)
self.assertTableData("People", cols="subset", data=expected_data1)
self.assertTableData("_grist_Views_section_field",
cols="subset",
rows=lambda r: r.parentId.parentId,
data=[
["id", "parentId", "colRef", "displayCol"],
[1, 1, 25, 0],
[2, 1, 26, 28],
])
# Now remove the 'favorite' column.
out_actions = self.apply_user_action(
['RemoveColumn', 'People', 'favorite'])
# The associated field and both displayCols should be gone.
self.assertTables([
expected_tables1[0],
Table(2,
"People",
1,
0,
columns=[
Column(24, "manualSort", "ManualSortPos", False, "", 0),
Column(25, "name", "Text", False, "", 0),
]),
])
self.assertTableData("_grist_Views_section_field",
cols="subset",
rows=lambda r: r.parentId.parentId,
data=[
["id", "parentId", "colRef", "displayCol"],
[1, 1, 25, 0],
])
# Verify that the resulting actions don't include any extraneous calc actions.
# pylint:disable=line-too-long
self.assertOutActions(
out_actions, {
"stored": [
["BulkRemoveRecord", "_grist_Views_section_field", [2, 4]],
["BulkRemoveRecord", "_grist_Tables_column", [26, 27]],
["RemoveColumn", "People", "favorite"],
["RemoveColumn", "People", "gristHelper_Display"],
["RemoveRecord", "_grist_Tables_column", 28],
["RemoveColumn", "People", "gristHelper_Display2"],
],
"direct": [True, True, True, True, True, True],
"undo": [
[
"BulkUpdateRecord", "People", [1, 2, 3], {
"gristHelper_Display2": ["Netflix", "HBO", "NBC"]
}
],
[
"BulkUpdateRecord", "People", [1, 2, 3], {
"gristHelper_Display":
["Narcos", "Game of Thrones", "Today"]
}
],
[
"BulkAddRecord", "_grist_Views_section_field", [2, 4],
{
"colRef": [26, 26],
"displayCol": [28, 0],
"parentId": [1, 2],
"parentPos": [2.0, 4.0]
}
],
[
"BulkAddRecord", "_grist_Tables_column", [26, 27], {
"colId": ["favorite", "gristHelper_Display"],
"displayCol": [27, 0],
"formula": ["", "$favorite.show"],
"isFormula": [False, True],
"label": ["favorite", "gristHelper_Display"],
"parentId": [2, 2],
"parentPos": [6.0, 7.0],
"type": ["Ref:Television", "Any"],
"widgetOptions": [
"\"{\"alignment\":\"center\",\"visibleCol\":\"show\"}\"",
""
]
}
],
[
"BulkUpdateRecord", "People", [1, 2, 3], {
"favorite": [12, 11, 13]
}
],
[
"AddColumn", "People", "favorite", {
"formula": "",
"isFormula": False,
"type": "Ref:Television"
}
],
[
"AddColumn", "People", "gristHelper_Display", {
"formula": "$favorite.show",
"isFormula": True,
"type": "Any"
}
],
[
"AddRecord", "_grist_Tables_column", 28, {
"colId": "gristHelper_Display2",
"formula": "$favorite.network",
"isFormula": True,
"label": "gristHelper_Display2",
"parentId": 2,
"parentPos": 8.0,
"type": "Any"
}
],
[
"AddColumn", "People", "gristHelper_Display2", {
"formula": "$favorite.network",
"isFormula": True,
"type": "Any"
}
],
],
})
# Now undo; expect the structure and values restored.
stored_actions = out_actions.get_repr()["stored"]
undo_actions = out_actions.get_repr()["undo"]
out_actions = self.apply_user_action(
['ApplyUndoActions', undo_actions])
self.assertTables(expected_tables1)
self.assertTableData("People", cols="subset", data=expected_data1)
self.assertTableData("_grist_Views_section_field",
cols="subset",
rows=lambda r: r.parentId.parentId,
data=[
["id", "parentId", "colRef", "displayCol"],
[1, 1, 25, 0],
[2, 1, 26, 28],
])
self.assertPartialOutActions(out_actions, {
"stored": reversed(undo_actions),
})
class TestSummary(test_engine.EngineTestCase):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "Address", [
[11, "city", "Text", False, "", "City", ""],
[12, "state", "Text", False, "", "State", "WidgetOptions1"],
[13, "amount", "Numeric", False, "", "Amount", "WidgetOptions2"],
]]
],
"DATA": {
"Address": [
["id", "city", "state", "amount" ],
[ 21, "New York", "NY" , 1. ],
[ 22, "Albany", "NY" , 2. ],
[ 23, "Seattle", "WA" , 3. ],
[ 24, "Chicago", "IL" , 4. ],
[ 25, "Bedford", "MA" , 5. ],
[ 26, "New York", "NY" , 6. ],
[ 27, "Buffalo", "NY" , 7. ],
[ 28, "Bedford", "NY" , 8. ],
[ 29, "Boston", "MA" , 9. ],
[ 30, "Yonkers", "NY" , 10. ],
[ 31, "New York", "NY" , 11. ],
]
}
})
starting_table = Table(1, "Address", primaryViewId=0, summarySourceTable=0, columns=[
Column(11, "city", "Text", isFormula=False, formula="", summarySourceCol=0),
Column(12, "state", "Text", isFormula=False, formula="", summarySourceCol=0),
Column(13, "amount", "Numeric", isFormula=False, formula="", summarySourceCol=0),
])
starting_table_data = [
["id", "city", "state", "amount" ],
[ 21, "New York", "NY" , 1 ],
[ 22, "Albany", "NY" , 2 ],
[ 23, "Seattle", "WA" , 3 ],
[ 24, "Chicago", "IL" , 4 ],
[ 25, "Bedford", "MA" , 5 ],
[ 26, "New York", "NY" , 6 ],
[ 27, "Buffalo", "NY" , 7 ],
[ 28, "Bedford", "NY" , 8 ],
[ 29, "Boston", "MA" , 9 ],
[ 30, "Yonkers", "NY" , 10 ],
[ 31, "New York", "NY" , 11 ],
]
#----------------------------------------------------------------------
def test_encode_summary_table_name(self):
self.assertEqual(summary.encode_summary_table_name("Foo"), "GristSummary_3_Foo")
self.assertEqual(summary.encode_summary_table_name("Foo2"), "GristSummary_4_Foo2")
self.assertEqual(summary.decode_summary_table_name("GristSummary_3_Foo"), "Foo")
self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo2"), "Foo2")
self.assertEqual(summary.decode_summary_table_name("GristSummary_3_Foo2"), "Foo")
self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo2_2"), "Foo2")
# Test that underscore in the name is OK.
self.assertEqual(summary.decode_summary_table_name("GristSummary_5_Foo_234"), "Foo_2")
self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo_234"), "Foo_")
self.assertEqual(summary.decode_summary_table_name("GristSummary_6__Foo_234"), "_Foo_2")
# Test that we return None for invalid values.
self.assertEqual(summary.decode_summary_table_name("Foo2"), None)
self.assertEqual(summary.decode_summary_table_name("GristSummary_3Foo"), None)
self.assertEqual(summary.decode_summary_table_name("GristSummary_4_Foo"), None)
self.assertEqual(summary.decode_summary_table_name("GristSummary_3X_Foo"), None)
self.assertEqual(summary.decode_summary_table_name("_5_Foo_234"), None)
self.assertEqual(summary.decode_summary_table_name("_GristSummary_3_Foo"), None)
self.assertEqual(summary.decode_summary_table_name("gristsummary_3_Foo"), None)
self.assertEqual(summary.decode_summary_table_name("GristSummary3_Foo"), None)
#----------------------------------------------------------------------
def test_create_view_section(self):
self.load_sample(self.sample)
# Verify the starting table; there should be no views yet.
self.assertTables([self.starting_table])
self.assertViews([])
# Create a view + section for the initial table.
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Verify that we got a new view, with one section, and three fields.
self.assertTables([self.starting_table])
basic_view = View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=11),
Field(2, colRef=12),
Field(3, colRef=13),
])
])
self.assertViews([basic_view])
self.assertTableData("Address", self.starting_table_data)
# Create a "Totals" section, i.e. a summary with no group-by columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
# Verify that a new table gets created, and a new view, with a section for that table,
# and some auto-generated summary fields.
summary_table1 = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1,
columns=[
Column(14, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(15, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(16, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view1 = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(4, colRef=15),
Field(5, colRef=16),
])
])
self.assertTables([self.starting_table, summary_table1])
self.assertViews([basic_view, summary_view1])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, 66.0 ],
])
# Create a summary section, grouped by the "State" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
# Verify that a new table gets created again, a new view, and a section for that table.
# Note that we also check that summarySourceTable and summarySourceCol fields are correct.
summary_table2 = Table(3, "GristSummary_7_Address2", primaryViewId=0, summarySourceTable=1,
columns=[
Column(17, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(18, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(19, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(20, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view2 = View(3, sections=[
Section(3, parentKey="record", tableRef=3, fields=[
Field(6, colRef=17),
Field(7, colRef=19),
Field(8, colRef=20),
])
])
self.assertTables([self.starting_table, summary_table1, summary_table2])
self.assertViews([basic_view, summary_view1, summary_view2])
# Verify more fields of the new column objects.
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[17, 'state', 'Text', '', 'WidgetOptions1', 'State'],
[20, 'amount', 'Numeric', 'SUM($group.amount)', 'WidgetOptions2', 'Amount'],
])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address2', cols="subset", data=[
[ "id", "state", "count", "amount" ],
[ 1, "NY", 7, 1.+2+6+7+8+10+11 ],
[ 2, "WA", 1, 3. ],
[ 3, "IL", 1, 4. ],
[ 4, "MA", 2, 5.+9 ],
])
# Create a summary section grouped by two columns ("city" and "state").
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Verify the new table and views.
summary_table3 = Table(4, "GristSummary_7_Address3", primaryViewId=0, summarySourceTable=1,
columns=[
Column(21, "city", "Text", isFormula=False, formula="", summarySourceCol=11),
Column(22, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(23, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(24, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(25, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view3 = View(4, sections=[
Section(4, parentKey="record", tableRef=4, fields=[
Field(9, colRef=21),
Field(10, colRef=22),
Field(11, colRef=24),
Field(12, colRef=25),
])
])
self.assertTables([self.starting_table, summary_table1, summary_table2, summary_table3])
self.assertViews([basic_view, summary_view1, summary_view2, summary_view3])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address3', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+6+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 5. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 1, 8. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
])
# The original table's data should not have changed.
self.assertTableData("Address", self.starting_table_data)
#----------------------------------------------------------------------
def test_summary_gencode(self):
self.maxDiff = 1000 # If there is a discrepancy, allow the bigger diff.
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
self.assertMultiLineEqual(self.engine.fetch_table_schema(),
"""import grist
from functions import * # global uppercase functions
import datetime, math, re # modules commonly needed in formulas
@grist.UserTable
class Address:
city = grist.Text()
state = grist.Text()
amount = grist.Numeric()
class _Summary:
@grist.formulaType(grist.ReferenceList('Address'))
def group(rec, table):
return table.getSummarySourceGroup(rec)
@grist.formulaType(grist.Int())
def count(rec, table):
return len(rec.group)
@grist.formulaType(grist.Numeric())
def amount(rec, table):
return SUM(rec.group.amount)
""")
#----------------------------------------------------------------------
def test_summary_table_reuse(self):
# Test that we'll reuse a suitable summary table when already available.
self.load_sample(self.sample)
# Create a summary section grouped by two columns ("city" and "state").
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Verify the new table and views.
summary_table = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1,
columns=[
Column(14, "city", "Text", isFormula=False, formula="", summarySourceCol=11),
Column(15, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(16, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(17, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(18, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view = View(1, sections=[
Section(1, parentKey="record", tableRef=2, fields=[
Field(1, colRef=14),
Field(2, colRef=15),
Field(3, colRef=17),
Field(4, colRef=18),
])
])
self.assertTables([self.starting_table, summary_table])
self.assertViews([summary_view])
# Create twoo other views + view sections with the same breakdown (in different order
# of group-by fields, which should still reuse the same table).
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12,11]])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
summary_view2 = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(5, colRef=15),
Field(6, colRef=14),
Field(7, colRef=17),
Field(8, colRef=18),
])
])
summary_view3 = View(3, sections=[
Section(3, parentKey="record", tableRef=2, fields=[
Field(9, colRef=14),
Field(10, colRef=15),
Field(11, colRef=17),
Field(12, colRef=18),
])
])
# Verify that we have a new view, but are reusing the table.
self.assertTables([self.starting_table, summary_table])
self.assertViews([summary_view, summary_view2, summary_view3])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+6+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 5. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 1, 8. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
])
#----------------------------------------------------------------------
def test_summary_no_invalid_reuse(self):
# Verify that if we have some summary tables for one table, they don't mistakenly get used
# when we need a summary for another table.
# Load table and create a couple summary sections, for totals, and grouped by "state".
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(15, "count", "Int", True, "len($group)", 0),
Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(17, "state", "Text", False, "", 12),
Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(19, "count", "Int", True, "len($group)", 0),
Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
])
# Create another table similar to the first one.
self.apply_user_action(["AddTable", "Address2", [
{ "id": "city", "type": "Text" },
{ "id": "state", "type": "Text" },
{ "id": "amount", "type": "Numeric" },
]])
data = self.sample["DATA"]["Address"]
self.apply_user_action(["BulkAddRecord", "Address2", data.row_ids, data.columns])
# Check that we've loaded the right data, and have the new table.
self.assertTableData("Address", cols="subset", data=self.starting_table_data)
self.assertTableData("Address2", cols="subset", data=self.starting_table_data)
self.assertTableData("_grist_Tables", cols="subset", data=[
['id', 'tableId', 'summarySourceTable'],
[ 1, 'Address', 0],
[ 2, 'GristSummary_7_Address', 1],
[ 3, 'GristSummary_7_Address2', 1],
[ 4, 'Address2', 0],
])
# Now create similar summary sections for the new table.
self.apply_user_action(["CreateViewSection", 4, 0, "record", []])
self.apply_user_action(["CreateViewSection", 4, 0, "record", [23]])
# Make sure this creates new section rather than reuses similar ones for the wrong table.
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(15, "count", "Int", True, "len($group)", 0),
Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(17, "state", "Text", False, "", 12),
Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(19, "count", "Int", True, "len($group)", 0),
Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(4, "Address2", primaryViewId=3, summarySourceTable=0, columns=[
Column(21, "manualSort", "ManualSortPos",False, "", 0),
Column(22, "city", "Text", False, "", 0),
Column(23, "state", "Text", False, "", 0),
Column(24, "amount", "Numeric", False, "", 0),
]),
Table(5, "GristSummary_8_Address2", 0, 4, columns=[
Column(25, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0),
Column(26, "count", "Int", True, "len($group)", 0),
Column(27, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(6, "GristSummary_8_Address2_2", 0, 4, columns=[
Column(28, "state", "Text", False, "", 23),
Column(29, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0),
Column(30, "count", "Int", True, "len($group)", 0),
Column(31, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
])
#----------------------------------------------------------------------
def test_summary_updates(self):
# Verify that summary tables update automatically when we change a value used in a summary
# formula; or a value in a group-by column; or add/remove a record; that records get
# auto-added when new group-by combinations appear.
# Load sample and create a summary section grouped by two columns ("city" and "state").
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Verify that the summary table respects all updates to the source table.
self._do_test_updates("Address", "GristSummary_7_Address")
def _do_test_updates(self, source_tbl_name, summary_tbl_name):
# This is the main part of test_summary_updates(). It's moved to its own method so that
# updates can be verified the same way after a table rename.
# Verify the summarized data.
self.assertTableData(summary_tbl_name, cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+6+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 5. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 1, 8. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
])
# Change an amount (New York, NY, 6 -> 106), check that the right calc action gets emitted.
out_actions = self.update_record(source_tbl_name, 26, amount=106)
self.assertPartialOutActions(out_actions, {
"stored": [
actions.UpdateRecord(source_tbl_name, 26, {'amount': 106}),
actions.UpdateRecord(summary_tbl_name, 1, {'amount': 1.+106+11}),
]
})
# Change a groupby value so that a record moves from one summary group to another.
# Bedford, NY, 8.0 -> Bedford, MA, 8.0
out_actions = self.update_record(source_tbl_name, 28, state="MA")
self.assertPartialOutActions(out_actions, {
"stored": [
actions.UpdateRecord(source_tbl_name, 28, {'state': 'MA'}),
actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'amount': [5.0 + 8.0, 0.0]}),
actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'count': [2, 0]}),
actions.BulkUpdateRecord(summary_tbl_name, [5,7], {'group': [[25, 28], []]}),
]
})
# Add a record to an existing group (Bedford, MA, 108.0)
out_actions = self.add_record(source_tbl_name, city="Bedford", state="MA", amount=108.0)
self.assertPartialOutActions(out_actions, {
"stored": [
actions.AddRecord(source_tbl_name, 32,
{'city': 'Bedford', 'state': 'MA', 'amount': 108.0}),
actions.UpdateRecord(summary_tbl_name, 5, {'amount': 5.0 + 8.0 + 108.0}),
actions.UpdateRecord(summary_tbl_name, 5, {'count': 3}),
actions.UpdateRecord(summary_tbl_name, 5, {'group': [25, 28, 32]}),
]
})
# Remove a record (rowId=28, Bedford, MA, 8.0)
out_actions = self.remove_record(source_tbl_name, 28)
self.assertPartialOutActions(out_actions, {
"stored": [
actions.RemoveRecord(source_tbl_name, 28),
actions.UpdateRecord(summary_tbl_name, 5, {'amount': 5.0 + 108.0}),
actions.UpdateRecord(summary_tbl_name, 5, {'count': 2}),
actions.UpdateRecord(summary_tbl_name, 5, {'group': [25, 32]}),
]
})
# Change groupby value to create a new combination (rowId 25, Bedford, MA, 5.0 -> Salem, MA).
# A new summary record should be added.
out_actions = self.update_record(source_tbl_name, 25, city="Salem")
self.assertPartialOutActions(out_actions, {
"stored": [
actions.UpdateRecord(source_tbl_name, 25, {'city': 'Salem'}),
actions.AddRecord(summary_tbl_name, 10, {'city': 'Salem', 'state': 'MA'}),
actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'amount': [108.0, 5.0]}),
actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'count': [1, 1]}),
actions.BulkUpdateRecord(summary_tbl_name, [5,10], {'group': [[32], [25]]}),
]
})
# Add a record with a new combination (Amherst, MA, 17)
out_actions = self.add_record(source_tbl_name, city="Amherst", state="MA", amount=17.0)
self.assertPartialOutActions(out_actions, {
"stored": [
actions.AddRecord(source_tbl_name, 33, {'city': 'Amherst', 'state': 'MA', 'amount': 17.}),
actions.AddRecord(summary_tbl_name, 11, {'city': 'Amherst', 'state': 'MA'}),
actions.UpdateRecord(summary_tbl_name, 11, {'amount': 17.0}),
actions.UpdateRecord(summary_tbl_name, 11, {'count': 1}),
actions.UpdateRecord(summary_tbl_name, 11, {'group': [33]}),
]
})
# Verify the resulting data after all the updates.
self.assertTableData(summary_tbl_name, cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+106+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 108. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 0, 0. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
[ 10, "Salem", "MA" , 1, 5.0 ],
[ 11, "Amherst", "MA" , 1, 17.0 ],
])
#----------------------------------------------------------------------
def test_table_rename(self):
# Verify that summary tables keep working and updating when source table is renamed.
# Load sample and create a couple of summary sections.
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Check what tables we have now.
self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [
[1, "Address", 0],
[2, "GristSummary_7_Address", 1],
])
# Rename the table: this is what we are really testing in this test case.
self.apply_user_action(["RenameTable", "Address", "Location"])
self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [
[1, "Location", 0],
[2, "GristSummary_8_Location", 1],
])
# Verify that the bigger summary table respects all updates to the renamed source table.
self._do_test_updates("Location", "GristSummary_8_Location")
#----------------------------------------------------------------------
def test_table_rename_multiple(self):
# Similar to the above, verify renames, but now with two summary tables.
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [
[1, "Address", 0],
[2, "GristSummary_7_Address", 1],
[3, "GristSummary_7_Address2", 1],
])
# Verify the data in the simple totals-only summary table.
self.assertTableData('GristSummary_7_Address2', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, 66.0 ],
])
# Do a rename.
self.apply_user_action(["RenameTable", "Address", "Addresses"])
self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [
[1, "Addresses", 0],
[2, "GristSummary_9_Addresses", 1],
[3, "GristSummary_9_Addresses2", 1],
])
self.assertTableData('GristSummary_9_Addresses2', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, 66.0 ],
])
# Remove one of the tables so that we can use _do_test_updates to verify updates still work.
self.apply_user_action(["RemoveTable", "GristSummary_9_Addresses2"])
self.assertPartialData("_grist_Tables", ["id", "tableId", "summarySourceTable"], [
[1, "Addresses", 0],
[2, "GristSummary_9_Addresses", 1],
])
self._do_test_updates("Addresses", "GristSummary_9_Addresses")
#----------------------------------------------------------------------
def test_change_summary_formula(self):
# Verify that changing a summary formula affects all group-by variants, and adding a new
# summary table gets the changed formula.
#
# (Recall that all summaries of a single table are *conceptually* variants of a single summary
# table, sharing all formulas and differing only in the group-by columns.)
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
# These are the tables and columns we automatically get.
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "city", "Text", False, "", 11),
Column(15, "state", "Text", False, "", 12),
Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(17, "count", "Int", True, "len($group)", 0),
Column(18, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(20, "count", "Int", True, "len($group)", 0),
Column(21, "amount", "Numeric", True, "SUM($group.amount)", 0),
])
])
# Now change a formula using one of the summary tables. It should trigger an equivalent
# change in the other.
self.apply_user_action(["ModifyColumn", "GristSummary_7_Address", "amount",
{"formula": "10*sum($group.amount)"}])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'],
[21, 'amount', 'Numeric', '10*sum($group.amount)', 'WidgetOptions2', 'Amount'],
])
# Change a formula and a few other fields in the other table, and verify a change to both.
self.apply_user_action(["ModifyColumn", "GristSummary_7_Address2", "amount",
{"formula": "100*sum($group.amount)",
"type": "Text",
"widgetOptions": "hello",
"label": "AMOUNT",
"untieColIdFromLabel": True
}])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
])
# Check the values in the summary tables: they should reflect the new formula.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, str(100*(1.+6+11))],
[ 2, "Albany", "NY" , 1, "200.0" ],
[ 3, "Seattle", "WA" , 1, "300.0" ],
[ 4, "Chicago", "IL" , 1, "400.0" ],
[ 5, "Bedford", "MA" , 1, "500.0" ],
[ 6, "Buffalo", "NY" , 1, "700.0" ],
[ 7, "Bedford", "NY" , 1, "800.0" ],
[ 8, "Boston", "MA" , 1, "900.0" ],
[ 9, "Yonkers", "NY" , 1, "1000.0" ],
])
self.assertTableData('GristSummary_7_Address2', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, "6600.0"],
])
# Add a new summary table, and check that it gets the new formula.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "city", "Text", False, "", 11),
Column(15, "state", "Text", False, "", 12),
Column(16, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(17, "count", "Int", True, "len($group)", 0),
Column(18, "amount", "Text", True, "100*sum($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(19, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(20, "count", "Int", True, "len($group)", 0),
Column(21, "amount", "Text", True, "100*sum($group.amount)", 0),
]),
Table(4, "GristSummary_7_Address3", 0, 1, columns=[
Column(22, "state", "Text", False, "", 12),
Column(23, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(24, "count", "Int", True, "len($group)", 0),
Column(25, "amount", "Text", True, "100*sum($group.amount)", 0),
])
])
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[18, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[21, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
[25, 'amount', 'Text', '100*sum($group.amount)', 'hello', 'AMOUNT'],
])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address3', cols="subset", data=[
[ "id", "state", "count", "amount" ],
[ 1, "NY", 7, str(100*(1.+2+6+7+8+10+11)) ],
[ 2, "WA", 1, "300.0" ],
[ 3, "IL", 1, "400.0" ],
[ 4, "MA", 2, str(500.+900) ],
])
#----------------------------------------------------------------------
def test_convert_source_column(self):
# Verify that we can convert the type of a column when there is a summary table using that
# column to group by. Since converting generates extra summary records, this may cause bugs.
self.apply_user_action(["AddEmptyTable"])
self.apply_user_action(["BulkAddRecord", "Table1", [None]*3, {"A": [10,20,10], "B": [1,2,3]}])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [2]])
# Verify metadata and actual data initially.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Numeric", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Any", True, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Numeric", False, "", 2),
Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "B", "Numeric", True, "SUM($group.B)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, 10, 1.0, None ],
[ 2, 2.0, 20, 2.0, None ],
[ 3, 3.0, 10, 3.0, None ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "group", "count", "B" ],
[ 1, 10, [1,3], 2, 4 ],
[ 2, 20, [2], 1, 2 ],
])
# Do a conversion.
self.apply_user_action(["UpdateRecord", "_grist_Tables_column", 2, {"type": "Text"}])
# Verify that the conversion's result is as expected.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Text", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Any", True, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Text", False, "", 2),
Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "B", "Numeric", True, "SUM($group.B)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, "10.0", 1.0, None ],
[ 2, 2.0, "20.0", 2.0, None ],
[ 3, 3.0, "10.0", 3.0, None ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "group", "count", "B" ],
[ 1, "10.0", [1,3], 2, 4 ],
[ 2, "20.0", [2], 1, 2 ],
])
#----------------------------------------------------------------------
@test_engine.test_undo
def test_remove_source_column(self):
# Verify that we can remove a column when there is a summary table using that column to group
# by. (Bug T188.)
self.apply_user_action(["AddEmptyTable"])
self.apply_user_action(["BulkAddRecord", "Table1", [None]*3,
{"A": ['a','b','c'], "B": [1,1,2], "C": [4,5,6]}])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [2,3]])
# Verify metadata and actual data initially.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Text", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Numeric", False, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Text", False, "", 2),
Column(6, "B", "Numeric", False, "", 3),
Column(7, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(8, "count", "Int", True, "len($group)", 0),
Column(9, "C", "Numeric", True, "SUM($group.C)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, 'a', 1.0, 4 ],
[ 2, 2.0, 'b', 1.0, 5 ],
[ 3, 3.0, 'c', 2.0, 6 ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "B", "group", "count", "C" ],
[ 1, 'a', 1.0, [1], 1, 4 ],
[ 2, 'b', 1.0, [2], 1, 5 ],
[ 3, 'c', 2.0, [3], 1, 6 ],
])
# Remove column A, used for group-by.
self.apply_user_action(["RemoveColumn", "Table1", "A"])
# Verify that the conversion's result is as expected.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Numeric", False, "", 0),
]),
Table(3, "GristSummary_6_Table1_2", summarySourceTable=1, primaryViewId=0, columns=[
Column(10, "B", "Numeric", False, "", 3),
Column(11, "count", "Int", True, "len($group)", 0),
Column(12, "C", "Numeric", True, "SUM($group.C)", 0),
Column(13, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "B", "C" ],
[ 1, 1.0, 1.0, 4 ],
[ 2, 2.0, 1.0, 5 ],
[ 3, 3.0, 2.0, 6 ],
])
self.assertTableData('GristSummary_6_Table1_2', data=[
[ "id", "B", "group", "count", "C" ],
[ 1, 1.0, [1,2], 2, 9 ],
[ 2, 2.0, [3], 1, 6 ],
])
def test_display_col_table_rename(self):
self.load_sample(self.ref_sample)
# Add a table for people to get an associated view.
self.apply_user_action([
'AddTable', 'People',
[{
'id': 'name',
'type': 'Text'
}, {
'id':
'favorite',
'type':
'Ref:Television',
'widgetOptions':
'\"{\"alignment\":\"center\",\"visibleCol\":\"show\"}\"'
}, {
'id': 'network',
'type': 'Any',
'isFormula': True,
'formula':
'Television.lookupOne(show=rec.favorite.show).network'
}]
])
self.apply_user_action([
'BulkAddRecord', 'People', [1, 2, 3], {
'name': ['Bob', 'Jim', 'Don'],
'favorite': [12, 11, 13]
}
])
# Add a display formula for the 'favorite' column.
# A "gristHelper_Display" column with the requested formula should be added and set as the
# displayCol of the favorite column.
self.apply_user_action(
['SetDisplayFormula', 'People', None, 26, '$favorite.show'])
# Set display formula for 'favorite' column field.
# A single "gristHelper_Display2" column should be added with the requested formula.
self.apply_user_action(
['SetDisplayFormula', 'People', 1, None, '$favorite.network'])
# Check that the tables are set up as expected.
self.assertTables([
Table(1,
"Television",
0,
0,
columns=[
Column(21, "show", "Text", False, "", 0),
Column(22, "network", "Text", False, "", 0),
Column(23, "viewers", "Int", False, "", 0),
]),
Table(
2,
"People",
1,
0,
columns=[
Column(24, "manualSort", "ManualSortPos", False, "", 0),
Column(25, "name", "Text", False, "", 0),
Column(26, "favorite", "Ref:Television", False, "", 0),
Column(
27, "network", "Any", True,
"Television.lookupOne(show=rec.favorite.show).network",
0),
Column(28, "gristHelper_Display", "Any", True,
"$favorite.show", 0),
Column(29, "gristHelper_Display2", "Any", True,
"$favorite.network", 0)
]),
])
self.assertTableData("People",
cols="subset",
data=[["id", "name", "favorite", "network"],
[1, "Bob", 12, "Netflix"],
[2, "Jim", 11, "HBO"],
[3, "Don", 13, "NBC"]])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.parentId.id == 2),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[24, "manualSort", 2, 0, ""], [25, "name", 2, 0, ""],
[26, "favorite", 2, 28, ""],
[
27, "network", 2, 0,
"Television.lookupOne(show=rec.favorite.show).network"
], [28, "gristHelper_Display", 2, 0, "$favorite.show"],
[29, "gristHelper_Display2", 2, 0, "$favorite.network"]])
self.assertTableData("_grist_Views_section_field",
cols="subset",
rows=lambda r: r.parentId.parentId,
data=[["id", "colRef", "displayCol"], [1, 25, 29],
[2, 26, 0], [3, 27, 0]])
# Rename the referenced table.
out_actions = self.apply_user_action(
['RenameTable', 'Television', 'Television2'])
# Verify the resulting actions.
# This tests a bug fix where table renames would cause widgetOptions and displayCols
# of columns referencing the renamed table to be unset. See https://phab.getgrist.com/T206.
# Ensure that no actions are generated to unset the widgetOptions and the displayCols of the
# field or column.
self.assertPartialOutActions(
out_actions, {
"stored":
[["ModifyColumn", "People", "favorite", {
"type": "Int"
}], ["RenameTable", "Television", "Television2"],
[
"UpdateRecord", "_grist_Tables", 1, {
"tableId": "Television2"
}
],
[
"ModifyColumn", "People", "favorite", {
"type": "Ref:Television2"
}
],
[
"ModifyColumn", "People", "network", {
"formula":
"Television2.lookupOne(show=rec.favorite.show).network"
}
],
[
"BulkUpdateRecord", "_grist_Tables_column", [26, 27], {
"formula": [
"",
"Television2.lookupOne(show=rec.favorite.show).network"
],
"type": ["Ref:Television2", "Any"]
}
]],
"calc": []
})
# Verify that the tables have responded as expected to the change.
self.assertTables([
Table(1,
"Television2",
0,
0,
columns=[
Column(21, "show", "Text", False, "", 0),
Column(22, "network", "Text", False, "", 0),
Column(23, "viewers", "Int", False, "", 0),
]),
Table(
2,
"People",
1,
0,
columns=[
Column(24, "manualSort", "ManualSortPos", False, "", 0),
Column(25, "name", "Text", False, "", 0),
Column(26, "favorite", "Ref:Television2", False, "", 0),
Column(
27, "network", "Any", True,
"Television2.lookupOne(show=rec.favorite.show).network",
0),
Column(28, "gristHelper_Display", "Any", True,
"$favorite.show", 0),
Column(29, "gristHelper_Display2", "Any", True,
"$favorite.network", 0)
]),
])
self.assertTableData("People",
cols="subset",
data=[["id", "name", "favorite", "network"],
[1, "Bob", 12, "Netflix"],
[2, "Jim", 11, "HBO"],
[3, "Don", 13, "NBC"]])
self.assertTableData(
"_grist_Tables_column",
cols="subset",
rows=(lambda r: r.parentId.id == 2),
data=[["id", "colId", "parentId", "displayCol", "formula"],
[24, "manualSort", 2, 0, ""], [25, "name", 2, 0, ""],
[26, "favorite", 2, 28, ""],
[
27, "network", 2, 0,
"Television2.lookupOne(show=rec.favorite.show).network"
], [28, "gristHelper_Display", 2, 0, "$favorite.show"],
[29, "gristHelper_Display2", 2, 0, "$favorite.network"]])
self.assertTableData("_grist_Views_section_field",
cols="subset",
rows=lambda r: r.parentId.parentId,
data=[["id", "colRef", "displayCol"], [1, 25, 29],
[2, 26, 0], [3, 27, 0]])
def test_summary_no_invalid_reuse(self):
# Verify that if we have some summary tables for one table, they don't mistakenly get used
# when we need a summary for another table.
# Load table and create a couple summary sections, for totals, and grouped by "state".
self.load_sample(self.sample)
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(15, "count", "Int", True, "len($group)", 0),
Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(17, "state", "Text", False, "", 12),
Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(19, "count", "Int", True, "len($group)", 0),
Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
])
# Create another table similar to the first one.
self.apply_user_action(["AddTable", "Address2", [
{ "id": "city", "type": "Text" },
{ "id": "state", "type": "Text" },
{ "id": "amount", "type": "Numeric" },
]])
data = self.sample["DATA"]["Address"]
self.apply_user_action(["BulkAddRecord", "Address2", data.row_ids, data.columns])
# Check that we've loaded the right data, and have the new table.
self.assertTableData("Address", cols="subset", data=self.starting_table_data)
self.assertTableData("Address2", cols="subset", data=self.starting_table_data)
self.assertTableData("_grist_Tables", cols="subset", data=[
['id', 'tableId', 'summarySourceTable'],
[ 1, 'Address', 0],
[ 2, 'GristSummary_7_Address', 1],
[ 3, 'GristSummary_7_Address2', 1],
[ 4, 'Address2', 0],
])
# Now create similar summary sections for the new table.
self.apply_user_action(["CreateViewSection", 4, 0, "record", []])
self.apply_user_action(["CreateViewSection", 4, 0, "record", [23]])
# Make sure this creates new section rather than reuses similar ones for the wrong table.
self.assertTables([
self.starting_table,
Table(2, "GristSummary_7_Address", 0, 1, columns=[
Column(14, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(15, "count", "Int", True, "len($group)", 0),
Column(16, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(3, "GristSummary_7_Address2", 0, 1, columns=[
Column(17, "state", "Text", False, "", 12),
Column(18, "group", "RefList:Address", True, "table.getSummarySourceGroup(rec)", 0),
Column(19, "count", "Int", True, "len($group)", 0),
Column(20, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(4, "Address2", primaryViewId=3, summarySourceTable=0, columns=[
Column(21, "manualSort", "ManualSortPos",False, "", 0),
Column(22, "city", "Text", False, "", 0),
Column(23, "state", "Text", False, "", 0),
Column(24, "amount", "Numeric", False, "", 0),
]),
Table(5, "GristSummary_8_Address2", 0, 4, columns=[
Column(25, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0),
Column(26, "count", "Int", True, "len($group)", 0),
Column(27, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
Table(6, "GristSummary_8_Address2_2", 0, 4, columns=[
Column(28, "state", "Text", False, "", 23),
Column(29, "group", "RefList:Address2", True, "table.getSummarySourceGroup(rec)", 0),
Column(30, "count", "Int", True, "len($group)", 0),
Column(31, "amount", "Numeric", True, "SUM($group.amount)", 0),
]),
])
def init_sample_data(self):
# Add a couple of tables, including references.
self.apply_user_action([
"AddTable", "Address",
[
{
"id": "city",
"type": "Text"
},
{
"id": "state",
"type": "Text"
},
{
"id": "amount",
"type": "Numeric"
},
]
])
self.apply_user_action([
"AddTable", "People",
[{
"id": "name",
"type": "Text"
}, {
"id": "address",
"type": "Ref:Address"
}, {
"id": "city",
"type": "Any",
"formula": "$address.city"
}]
])
# Populate some data.
d = testutil.table_data_from_rows("Address",
self.address_table_data[0],
self.address_table_data[1:])
self.apply_user_action(
["BulkAddRecord", "Address", d.row_ids, d.columns])
d = testutil.table_data_from_rows("People", self.people_table_data[0],
self.people_table_data[1:])
self.apply_user_action(
["BulkAddRecord", "People", d.row_ids, d.columns])
# Add a view with several sections, including a summary table.
self.apply_user_action(["CreateViewSection", 1, 0, 'record', None])
self.apply_user_action(["CreateViewSection", 1, 3, 'record', [3]])
self.apply_user_action(["CreateViewSection", 2, 3, 'record', None])
# Verify the new structure of tables and views.
self.assertTables([
Table(1,
"Address",
primaryViewId=1,
summarySourceTable=0,
columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "amount", "Numeric", False, "", 0),
]),
Table(2,
"People",
primaryViewId=2,
summarySourceTable=0,
columns=[
Column(5, "manualSort", "ManualSortPos", False, "", 0),
Column(6, "name", "Text", False, "", 0),
Column(7, "address", "Ref:Address", False, "", 0),
Column(8, "city", "Any", True, "$address.city", 0),
]),
Table(3,
"GristSummary_7_Address",
0,
1,
columns=[
Column(9, "state", "Text", False, "",
summarySourceCol=3),
Column(10,
"group",
"RefList:Address",
True,
summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(11,
"count",
"Int",
True,
summarySourceCol=0,
formula="len($group)"),
Column(12,
"amount",
"Numeric",
True,
summarySourceCol=0,
formula="SUM($group.amount)"),
]),
])
self.assertViews([
View(1,
sections=[
Section(1,
parentKey="record",
tableRef=1,
fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2,
sections=[
Section(2,
parentKey="record",
tableRef=2,
fields=[
Field(4, colRef=6),
Field(5, colRef=7),
Field(6, colRef=8),
]),
]),
View(3,
sections=[
Section(3,
parentKey="record",
tableRef=1,
fields=[
Field(7, colRef=2),
Field(8, colRef=3),
Field(9, colRef=4),
]),
Section(4,
parentKey="record",
tableRef=3,
fields=[
Field(10, colRef=9),
Field(11, colRef=11),
Field(12, colRef=12),
]),
Section(5,
parentKey="record",
tableRef=2,
fields=[
Field(13, colRef=6),
Field(14, colRef=7),
Field(15, colRef=8),
]),
]),
])
# Verify the data we've loaded.
self.assertTableData('Address',
cols="subset",
data=self.address_table_data)
self.assertTableData('People',
cols="subset",
data=self.people_table_data)
self.assertTableData("GristSummary_7_Address",
cols="subset",
data=[
["id", "state", "count", "amount"],
[1, "NY", 7, 1. + 2 + 6 + 7 + 8 + 10 + 11],
[2, "WA", 1, 3.],
[3, "IL", 1, 4.],
[4, "MA", 2, 5. + 9],
])
def test_convert_source_column(self):
# Verify that we can convert the type of a column when there is a summary table using that
# column to group by. Since converting generates extra summary records, this may cause bugs.
self.apply_user_action(["AddEmptyTable"])
self.apply_user_action(["BulkAddRecord", "Table1", [None]*3, {"A": [10,20,10], "B": [1,2,3]}])
self.apply_user_action(["CreateViewSection", 1, 0, "record", [2]])
# Verify metadata and actual data initially.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Numeric", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Any", True, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Numeric", False, "", 2),
Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "B", "Numeric", True, "SUM($group.B)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, 10, 1.0, None ],
[ 2, 2.0, 20, 2.0, None ],
[ 3, 3.0, 10, 3.0, None ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "group", "count", "B" ],
[ 1, 10, [1,3], 2, 4 ],
[ 2, 20, [2], 1, 2 ],
])
# Do a conversion.
self.apply_user_action(["UpdateRecord", "_grist_Tables_column", 2, {"type": "Text"}])
# Verify that the conversion's result is as expected.
self.assertTables([
Table(1, "Table1", summarySourceTable=0, primaryViewId=1, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "A", "Text", False, "", 0),
Column(3, "B", "Numeric", False, "", 0),
Column(4, "C", "Any", True, "", 0),
]),
Table(2, "GristSummary_6_Table1", summarySourceTable=1, primaryViewId=0, columns=[
Column(5, "A", "Text", False, "", 2),
Column(6, "group", "RefList:Table1", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "B", "Numeric", True, "SUM($group.B)", 0),
])
])
self.assertTableData('Table1', data=[
[ "id", "manualSort", "A", "B", "C" ],
[ 1, 1.0, "10.0", 1.0, None ],
[ 2, 2.0, "20.0", 2.0, None ],
[ 3, 3.0, "10.0", 3.0, None ],
])
self.assertTableData('GristSummary_6_Table1', data=[
[ "id", "A", "group", "count", "B" ],
[ 1, "10.0", [1,3], 2, 4 ],
[ 2, "20.0", [2], 1, 2 ],
])
def test_table_removes(self):
# Verify table removals triggered by UpdateRecord actions, and related behavior.
# Same setup as previous test.
self.init_sample_data()
# Add one more table, and one more view for tables #1 and #4 (those we are about to delete).
self.apply_user_action(["AddEmptyTable"])
out_actions = self.apply_user_action(
["CreateViewSection", 1, 0, 'detail', None])
self.assertEqual(out_actions.retValues[0]["viewRef"], 5)
self.apply_user_action(["CreateViewSection", 4, 5, 'detail', None])
# See what's in TableViews and TabBar tables, to verify after we remove a table.
self.assertTableData('_grist_TableViews',
data=[
["id", "tableRef", "viewRef"],
[1, 1, 3],
[2, 1, 5],
])
self.assertTableData('_grist_TabBar',
cols="subset",
data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4],
[5, 5],
])
# Remove two tables, ensure certain views get removed.
self.apply_user_action(["BulkRemoveRecord", "_grist_Tables", [1, 4]])
# See that some TableViews/TabBar entries disappear, or tableRef gets unset.
self.assertTableData('_grist_TableViews',
data=[
["id", "tableRef", "viewRef"],
[1, 0, 3],
])
self.assertTableData('_grist_TabBar',
cols="subset",
data=[
["id", "viewRef"],
[2, 2],
[3, 3],
])
# Check that reference columns to this table get removed, with associated fields.
self.assertTables([
Table(2,
"People",
primaryViewId=2,
summarySourceTable=0,
columns=[
Column(5, "manualSort", "ManualSortPos", False, "", 0),
Column(6, "name", "Text", False, "", 0),
Column(8, "city", "Any", True, "$address.city", 0),
]),
# Note that the summary table is also gone.
])
self.assertViews([
View(2,
sections=[
Section(2,
parentKey="record",
tableRef=2,
fields=[
Field(4, colRef=6),
Field(6, colRef=8),
]),
]),
View(3,
sections=[
Section(5,
parentKey="record",
tableRef=2,
fields=[
Field(13, colRef=6),
Field(15, colRef=8),
]),
]),
])
def test_create_view_section(self):
self.load_sample(self.sample)
# Verify the starting table; there should be no views yet.
self.assertTables([self.starting_table])
self.assertViews([])
# Create a view + section for the initial table.
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Verify that we got a new view, with one section, and three fields.
self.assertTables([self.starting_table])
basic_view = View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=11),
Field(2, colRef=12),
Field(3, colRef=13),
])
])
self.assertViews([basic_view])
self.assertTableData("Address", self.starting_table_data)
# Create a "Totals" section, i.e. a summary with no group-by columns.
self.apply_user_action(["CreateViewSection", 1, 0, "record", []])
# Verify that a new table gets created, and a new view, with a section for that table,
# and some auto-generated summary fields.
summary_table1 = Table(2, "GristSummary_7_Address", primaryViewId=0, summarySourceTable=1,
columns=[
Column(14, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(15, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(16, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view1 = View(2, sections=[
Section(2, parentKey="record", tableRef=2, fields=[
Field(4, colRef=15),
Field(5, colRef=16),
])
])
self.assertTables([self.starting_table, summary_table1])
self.assertViews([basic_view, summary_view1])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address', cols="subset", data=[
[ "id", "count", "amount"],
[ 1, 11, 66.0 ],
])
# Create a summary section, grouped by the "State" column.
self.apply_user_action(["CreateViewSection", 1, 0, "record", [12]])
# Verify that a new table gets created again, a new view, and a section for that table.
# Note that we also check that summarySourceTable and summarySourceCol fields are correct.
summary_table2 = Table(3, "GristSummary_7_Address2", primaryViewId=0, summarySourceTable=1,
columns=[
Column(17, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(18, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(19, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(20, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view2 = View(3, sections=[
Section(3, parentKey="record", tableRef=3, fields=[
Field(6, colRef=17),
Field(7, colRef=19),
Field(8, colRef=20),
])
])
self.assertTables([self.starting_table, summary_table1, summary_table2])
self.assertViews([basic_view, summary_view1, summary_view2])
# Verify more fields of the new column objects.
self.assertTableData('_grist_Tables_column', rows="subset", cols="subset", data=[
['id', 'colId', 'type', 'formula', 'widgetOptions', 'label'],
[17, 'state', 'Text', '', 'WidgetOptions1', 'State'],
[20, 'amount', 'Numeric', 'SUM($group.amount)', 'WidgetOptions2', 'Amount'],
])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address2', cols="subset", data=[
[ "id", "state", "count", "amount" ],
[ 1, "NY", 7, 1.+2+6+7+8+10+11 ],
[ 2, "WA", 1, 3. ],
[ 3, "IL", 1, 4. ],
[ 4, "MA", 2, 5.+9 ],
])
# Create a summary section grouped by two columns ("city" and "state").
self.apply_user_action(["CreateViewSection", 1, 0, "record", [11,12]])
# Verify the new table and views.
summary_table3 = Table(4, "GristSummary_7_Address3", primaryViewId=0, summarySourceTable=1,
columns=[
Column(21, "city", "Text", isFormula=False, formula="", summarySourceCol=11),
Column(22, "state", "Text", isFormula=False, formula="", summarySourceCol=12),
Column(23, "group", "RefList:Address", isFormula=True, summarySourceCol=0,
formula="table.getSummarySourceGroup(rec)"),
Column(24, "count", "Int", isFormula=True, summarySourceCol=0,
formula="len($group)"),
Column(25, "amount", "Numeric", isFormula=True, summarySourceCol=0,
formula="SUM($group.amount)"),
])
summary_view3 = View(4, sections=[
Section(4, parentKey="record", tableRef=4, fields=[
Field(9, colRef=21),
Field(10, colRef=22),
Field(11, colRef=24),
Field(12, colRef=25),
])
])
self.assertTables([self.starting_table, summary_table1, summary_table2, summary_table3])
self.assertViews([basic_view, summary_view1, summary_view2, summary_view3])
# Verify the summarized data.
self.assertTableData('GristSummary_7_Address3', cols="subset", data=[
[ "id", "city", "state", "count", "amount" ],
[ 1, "New York", "NY" , 3, 1.+6+11 ],
[ 2, "Albany", "NY" , 1, 2. ],
[ 3, "Seattle", "WA" , 1, 3. ],
[ 4, "Chicago", "IL" , 1, 4. ],
[ 5, "Bedford", "MA" , 1, 5. ],
[ 6, "Buffalo", "NY" , 1, 7. ],
[ 7, "Bedford", "NY" , 1, 8. ],
[ 8, "Boston", "MA" , 1, 9. ],
[ 9, "Yonkers", "NY" , 1, 10. ],
])
# The original table's data should not have changed.
self.assertTableData("Address", self.starting_table_data)
class TestUserActions(test_engine.EngineTestCase):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "Address", [
[21, "city", "Text", False, "", "", ""],
]]
],
"DATA": {
"Address": [
["id", "city" ],
[11, "New York" ],
[12, "Colombia" ],
[13, "New Haven" ],
[14, "West Haven" ]],
}
})
starting_table = Table(1, "Address", primaryViewId=0, summarySourceTable=0, columns=[
Column(21, "city", "Text", isFormula=False, formula="", summarySourceCol=0)
])
#----------------------------------------------------------------------
def test_conversions(self):
# Test the sequence of user actions as used for transform-based conversions. This is actually
# not exactly what the client emits, but more like what the client should ideally emit.
# Our sample has a Schools.city text column; we'll convert it to Ref:Address.
self.load_sample(self.sample)
# Add a new table for Schools so that we get the associated views and fields.
self.apply_user_action(['AddTable', 'Schools', [{'id': 'city', 'type': 'Text'}]])
self.apply_user_action(['BulkAddRecord', 'Schools', [1,2,3,4], {
'city': ['New York', 'Colombia', 'New York', '']
}])
self.assertPartialData("_grist_Tables", ["id", "tableId"], [
[1, "Address"],
[2, "Schools"],
])
self.assertPartialData("_grist_Tables_column",
["id", "colId", "parentId", "parentPos", "widgetOptions"], [
[21, "city", 1, 1.0, ""],
[22, "manualSort", 2, 2.0, ""],
[23, "city", 2, 3.0, ""],
])
self.assertPartialData("_grist_Views_section_field", ["id", "colRef", "widgetOptions"], [
[1, 23, ""],
[2, 23, ""],
])
self.assertPartialData("Schools", ["id", "city"], [
[1, "New York" ],
[2, "Colombia" ],
[3, "New York" ],
[4, "" ],
])
# Our sample has a text column city.
out_actions = self.add_column('Schools', 'grist_Transform',
isFormula=True, formula='return $city', type='Text')
self.assertPartialOutActions(out_actions, { "stored": [
['AddColumn', 'Schools', 'grist_Transform', {
'type': 'Text', 'isFormula': True, 'formula': 'return $city',
}],
['AddRecord', '_grist_Tables_column', 24, {
'widgetOptions': '', 'parentPos': 4.0, 'isFormula': True, 'parentId': 2, 'colId':
'grist_Transform', 'formula': 'return $city', 'label': 'grist_Transform',
'type': 'Text'
}],
["AddRecord", "_grist_Views_section_field", 3, {
"colRef": 24, "parentId": 1, "parentPos": 3.0
}],
["AddRecord", "_grist_Views_section_field", 4, {
"colRef": 24, "parentId": 2, "parentPos": 4.0
}],
["BulkUpdateRecord", "Schools", [1, 2, 3],
{"grist_Transform": ["New York", "Colombia", "New York"]}],
]})
out_actions = self.update_record('_grist_Tables_column', 24,
type='Ref:Address',
formula='return Address.lookupOne(city=$city).id')
self.assertPartialOutActions(out_actions, { "stored": [
['ModifyColumn', 'Schools', 'grist_Transform', {
'formula': 'return Address.lookupOne(city=$city).id', 'type': 'Ref:Address'}],
['UpdateRecord', '_grist_Tables_column', 24, {
'formula': 'return Address.lookupOne(city=$city).id', 'type': 'Ref:Address'}],
["BulkUpdateRecord", "Schools", [1, 2, 3, 4], {"grist_Transform": [11, 12, 11, 0]}],
]})
# It seems best if TypeTransform sets widgetOptions on grist_Transform column, so that they
# can be copied in CopyFromColumn; rather than updating them after the copy is done.
self.update_record('_grist_Views_section_field', 1, widgetOptions="hello")
self.update_record('_grist_Tables_column', 24, widgetOptions="world")
out_actions = self.apply_user_action(
['CopyFromColumn', 'Schools', 'grist_Transform', 'city', None])
self.assertPartialOutActions(out_actions, { "stored": [
['ModifyColumn', 'Schools', 'city', {'type': 'Ref:Address'}],
['UpdateRecord', 'Schools', 4, {'city': 0}],
['UpdateRecord', '_grist_Views_section_field', 1, {'widgetOptions': ''}],
['UpdateRecord', '_grist_Tables_column', 23, {
'type': 'Ref:Address', 'widgetOptions': 'world'
}],
['BulkUpdateRecord', 'Schools', [1, 2, 3], {'city': [11, 12, 11]}],
["BulkUpdateRecord", "Schools", [1, 2, 3], {"grist_Transform": [0, 0, 0]}],
]})
out_actions = self.update_record('_grist_Tables_column', 23,
widgetOptions='{"widget":"Reference","visibleCol":"city"}')
self.assertPartialOutActions(out_actions, { "stored": [
['UpdateRecord', '_grist_Tables_column', 23, {
'widgetOptions': '{"widget":"Reference","visibleCol":"city"}'}],
]})
out_actions = self.remove_column('Schools', 'grist_Transform')
self.assertPartialOutActions(out_actions, { "stored": [
["BulkRemoveRecord", "_grist_Views_section_field", [3, 4]],
['RemoveRecord', '_grist_Tables_column', 24],
['RemoveColumn', 'Schools', 'grist_Transform'],
]})
#----------------------------------------------------------------------
def test_create_section_existing_view(self):
# Test that CreateViewSection works for an existing view.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
# Create a view + section for the initial table.
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Verify that we got a new view, with one section, and three fields.
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
])
]) ])
# Create a new section for the same view, check that only a section is added.
self.apply_user_action(["CreateViewSection", 1, 1, "record", None])
self.assertTables([self.starting_table])
self.assertViews([View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
])
]) ])
# Create another section for the same view, this time summarized.
self.apply_user_action(["CreateViewSection", 1, 1, "record", [21]])
summary_table = Table(2, "GristSummary_7_Address", 0, summarySourceTable=1, columns=[
Column(22, "city", "Text", isFormula=False, formula="", summarySourceCol=21),
Column(23, "group", "RefList:Address", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(24, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
self.assertTables([self.starting_table, summary_table])
# Check that we still have one view, with sections for different tables.
view = View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=21),
]),
Section(2, parentKey="record", tableRef=1, fields=[
Field(2, colRef=21),
]),
Section(3, parentKey="record", tableRef=2, fields=[
Field(3, colRef=22),
Field(4, colRef=24),
]),
])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
# Try to create a summary table for an invalid column, and check that it fails.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 1, 1, "record", [23]])
self.assertTables([self.starting_table, summary_table])
self.assertViews([view])
#----------------------------------------------------------------------
def test_creates_section_new_table(self):
# Test that CreateViewSection works for adding a new table.
self.load_sample(self.sample)
self.assertTables([self.starting_table])
self.assertViews([])
# When we create a section/view for new table, we get both a primary view, and the new view we
# are creating.
self.apply_user_action(["CreateViewSection", 0, 0, "record", None])
new_table = Table(2, "Table1", primaryViewId=1, summarySourceTable=0, columns=[
Column(22, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(23, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(24, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(25, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view = View(1, sections=[
Section(1, parentKey="record", tableRef=2, fields=[
Field(1, colRef=23),
Field(2, colRef=24),
Field(3, colRef=25),
])
])
new_view = View(2, sections=[
Section(3, parentKey="record", tableRef=2, fields=[
Field(7, colRef=23),
Field(8, colRef=24),
Field(9, colRef=25),
])
])
self.assertTables([self.starting_table, new_table])
self.assertViews([primary_view, new_view])
# Create another section in an existing view for a new table.
self.apply_user_action(["CreateViewSection", 0, 2, "record", None])
new_table2 = Table(3, "Table2", primaryViewId=3, summarySourceTable=0, columns=[
Column(26, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(27, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(28, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(29, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
primary_view2 = View(3, sections=[
Section(4, parentKey="record", tableRef=3, fields=[
Field(10, colRef=27),
Field(11, colRef=28),
Field(12, colRef=29),
])
])
new_view.sections.append(
Section(6, parentKey="record", tableRef=3, fields=[
Field(16, colRef=27),
Field(17, colRef=28),
Field(18, colRef=29),
])
)
# Check that we have a new table, only the primary view as new view; and a new section.
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# Check that we can't create a summary of a table grouped by a column that doesn't exist yet.
with self.assertRaises(ValueError):
self.apply_user_action(["CreateViewSection", 0, 2, "record", [31]])
self.assertTables([self.starting_table, new_table, new_table2])
self.assertViews([primary_view, new_view, primary_view2])
# But creating a new table and showing totals for it is possible though dumb.
self.apply_user_action(["CreateViewSection", 0, 2, "record", []])
# We expect a new table.
new_table3 = Table(4, "Table3", primaryViewId=4, summarySourceTable=0, columns=[
Column(30, "manualSort", "ManualSortPos", isFormula=False, formula="", summarySourceCol=0),
Column(31, "A", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(32, "B", "Any", isFormula=True, formula="", summarySourceCol=0),
Column(33, "C", "Any", isFormula=True, formula="", summarySourceCol=0),
])
# A summary of it.
summary_table = Table(5, "GristSummary_6_Table3", 0, summarySourceTable=4, columns=[
Column(34, "group", "RefList:Table3", isFormula=True,
formula="table.getSummarySourceGroup(rec)", summarySourceCol=0),
Column(35, "count", "Int", isFormula=True, formula="len($group)", summarySourceCol=0),
])
# The primary view of the new table.
primary_view3 = View(4, sections=[
Section(7, parentKey="record", tableRef=4, fields=[
Field(19, colRef=31),
Field(20, colRef=32),
Field(21, colRef=33),
])
])
# And a new view section for the summary.
new_view.sections.append(Section(9, parentKey="record", tableRef=5, fields=[
Field(25, colRef=35)
]))
self.assertTables([self.starting_table, new_table, new_table2, new_table3, summary_table])
self.assertViews([primary_view, new_view, primary_view2, primary_view3])
#----------------------------------------------------------------------
def init_views_sample(self):
# Add a new table and a view, to get some Views/Sections/Fields, and TabBar items.
self.apply_user_action(['AddTable', 'Schools', [
{'id': 'city', 'type': 'Text'},
{'id': 'state', 'type': 'Text'},
{'id': 'size', 'type': 'Numeric'},
]])
self.apply_user_action(['BulkAddRecord', 'Schools', [1,2,3,4], {
'city': ['New York', 'Colombia', 'New York', ''],
'state': ['NY', 'NY', 'NY', ''],
'size': [1000, 2000, 3000, 4000],
}])
# Add a new view; a second section (summary) to it; and a third view.
self.apply_user_action(['CreateViewSection', 1, 0, 'detail', None])
self.apply_user_action(['CreateViewSection', 1, 2, 'record', [3]])
self.apply_user_action(['CreateViewSection', 1, 0, 'chart', None])
self.apply_user_action(['CreateViewSection', 0, 2, 'record', None])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
Table(2, "GristSummary_7_Schools", 0, 1, columns=[
Column(5, "state", "Text", False, "", 3),
Column(6, "group", "RefList:Schools", True, "table.getSummarySourceGroup(rec)", 0),
Column(7, "count", "Int", True, "len($group)", 0),
Column(8, "size", "Numeric", True, "SUM($group.size)", 0),
]),
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2, sections=[
Section(3, parentKey="detail", tableRef=1, fields=[
Field(7, colRef=2),
Field(8, colRef=3),
Field(9, colRef=4),
]),
Section(4, parentKey="record", tableRef=2, fields=[
Field(10, colRef=5),
Field(11, colRef=7),
Field(12, colRef=8),
]),
Section(8, parentKey='record', tableRef=3, fields=[
Field(21, colRef=10),
Field(22, colRef=11),
Field(23, colRef=12),
]),
]),
View(3, sections=[
Section(5, parentKey="chart", tableRef=1, fields=[
Field(13, colRef=2),
Field(14, colRef=3),
]),
]),
View(4, sections=[
Section(6, parentKey='record', tableRef=3, fields=[
Field(15, colRef=10),
Field(16, colRef=11),
Field(17, colRef=12),
]),
]),
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[2, 2],
[3, 3],
[4, 4]
])
#----------------------------------------------------------------------
def test_view_remove(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Remove a view. Ensure related items, sections, fields get removed.
self.apply_user_action(["BulkRemoveRecord", "_grist_Views", [2,3]])
# Verify the new structure of tables and views.
self.assertTables([
Table(1, "Schools", 1, 0, columns=[
Column(1, "manualSort", "ManualSortPos", False, "", 0),
Column(2, "city", "Text", False, "", 0),
Column(3, "state", "Text", False, "", 0),
Column(4, "size", "Numeric", False, "", 0),
]),
# Note that the summary table is gone.
Table(3, 'Table1', 4, 0, columns=[
Column(9, "manualSort", "ManualSortPos", False, "", 0),
Column(10, "A", "Any", True, "", 0),
Column(11, "B", "Any", True, "", 0),
Column(12, "C", "Any", True, "", 0),
]),
])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(4, sections=[
Section(6, parentKey='record', tableRef=3, fields=[
Field(15, colRef=10),
Field(16, colRef=11),
Field(17, colRef=12),
]),
]),
])
self.assertTableData('_grist_TabBar', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
self.assertTableData('_grist_Pages', cols="subset", data=[
["id", "viewRef"],
[1, 1],
[4, 4],
])
#----------------------------------------------------------------------
def test_view_rename(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Verify the new structure of tables and views.
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'Table1', 4],
])
self.assertTableData('_grist_Views', cols="subset", data=[
[ 'id', 'name', 'primaryViewTable' ],
[ 1, 'Schools', 1],
[ 2, 'New page', 0],
[ 3, 'New page', 0],
[ 4, 'Table1', 3],
])
# Update the names in a few views, and ensure that primary ones cause tables to get renamed.
self.apply_user_action(['BulkUpdateRecord', '_grist_Views', [2,3,4],
{'name': ['A', 'B', 'C']}])
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'C', 4],
])
self.assertTableData('_grist_Views', cols="subset", data=[
[ 'id', 'name', 'primaryViewTable' ],
[ 1, 'Schools', 1],
[ 2, 'A', 0],
[ 3, 'B', 0],
[ 4, 'C', 3]
])
#----------------------------------------------------------------------
def test_section_removes(self):
# Add a couple of tables and views, to trigger creation of some related items.
self.init_views_sample()
# Remove a couple of sections. Ensure their fields get removed.
self.apply_user_action(['BulkRemoveRecord', '_grist_Views_section', [4, 8]])
self.assertViews([
View(1, sections=[
Section(1, parentKey="record", tableRef=1, fields=[
Field(1, colRef=2),
Field(2, colRef=3),
Field(3, colRef=4),
]),
]),
View(2, sections=[
Section(3, parentKey="detail", tableRef=1, fields=[
Field(7, colRef=2),
Field(8, colRef=3),
Field(9, colRef=4),
]),
]),
View(3, sections=[
Section(5, parentKey="chart", tableRef=1, fields=[
Field(13, colRef=2),
Field(14, colRef=3),
]),
]),
View(4, sections=[
Section(6, parentKey='record', tableRef=3, fields=[
Field(15, colRef=10),
Field(16, colRef=11),
Field(17, colRef=12),
]),
]),
])
#----------------------------------------------------------------------
def test_schema_consistency_check(self):
# Verify that schema consistency check actually runs, but only when schema is affected.
self.init_views_sample()
# Replace the engine's assert_schema_consistent() method with a mocked version.
orig_method = self.engine.assert_schema_consistent
count_calls = [0]
def override(self): # pylint: disable=unused-argument
count_calls[0] += 1
# pylint: disable=not-callable
orig_method()
self.engine.assert_schema_consistent = types.MethodType(override, self.engine)
# Do a non-sschema action to ensure it doesn't get called.
self.apply_user_action(['UpdateRecord', '_grist_Views', 2, {'name': 'A'}])
self.assertEqual(count_calls[0], 0)
# Do a schema action to ensure it gets called: this causes a table rename.
self.apply_user_action(['UpdateRecord', '_grist_Views', 4, {'name': 'C'}])
self.assertEqual(count_calls[0], 1)
self.assertTableData('_grist_Tables', cols="subset", data=[
[ 'id', 'tableId', 'primaryViewId' ],
[ 1, 'Schools', 1],
[ 2, 'GristSummary_7_Schools', 0],
[ 3, 'C', 4],
])
# Do another schema and non-schema action.
self.apply_user_action(['UpdateRecord', 'Schools', 1, {'city': 'Seattle'}])
self.assertEqual(count_calls[0], 1)
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 2, {'colId': 'city2'}])
self.assertEqual(count_calls[0], 2)
#----------------------------------------------------------------------
def test_new_column_conversions(self):
self.init_views_sample()
self.apply_user_action(['AddColumn', 'Schools', None, {}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Any", True, ""],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A"],
[1, "New York", None],
[2, "Colombia", None],
[3, "New York", None],
[4, "", None],
])
# Check that typing in text into the column produces a text column.
out_actions = self.apply_user_action(['UpdateRecord', 'Schools', 3, {"A": "foo"}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Text", False, ""],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", "" ],
[2, "Colombia", "" ],
[3, "New York", "foo" ],
[4, "", "" ],
])
# Undo, and check that typing in a number produces a numeric column.
self.apply_undo_actions(out_actions.undo)
out_actions = self.apply_user_action(['UpdateRecord', 'Schools', 3, {"A": " -17.6"}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Numeric", False, ""],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 0.0 ],
[2, "Colombia", 0.0 ],
[3, "New York", -17.6 ],
[4, "", 0.0 ],
])
# Undo, and set a formula for the new column instead.
self.apply_undo_actions(out_actions.undo)
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 13, {'formula': 'len($city)'}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Any", True, "len($city)"],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 8 ],
[2, "Colombia", 8 ],
[3, "New York", 8 ],
[4, "", 0 ],
])
# Convert the formula column to non-formula.
self.apply_user_action(['UpdateRecord', '_grist_Tables_column', 13, {'isFormula': False}])
self.assertTableData('_grist_Tables_column', cols="subset", rows="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[13, "A", "Numeric", False, "len($city)"],
])
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A" ],
[1, "New York", 8 ],
[2, "Colombia", 8 ],
[3, "New York", 8 ],
[4, "", 0 ],
])
# Add some more formula columns of type 'Any'.
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "1"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "'x'"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "$city == 'New York'"}])
self.apply_user_action(['AddColumn', 'Schools', None, {"formula": "$city=='New York' or '-'"}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Numeric", False, "len($city)"],
[14, "B", "Any", True, "1"],
[15, "C", "Any", True, "'x'"],
[16, "D", "Any", True, "$city == 'New York'"],
[17, "E", "Any", True, "$city=='New York' or '-'"],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A", "B", "C", "D", "E"],
[1, "New York", 8, 1, "x", True, True],
[2, "Colombia", 8, 1, "x", False, '-' ],
[3, "New York", 8, 1, "x", True, True],
[4, "", 0, 1, "x", False, '-' ],
])
# Convert all these formulas to non-formulas, and see that their types get guessed OK.
# TODO: We should also guess Int, Bool, Reference, ReferenceList, Date, and DateTime.
# TODO: It is possibly better if B became Int, and D became Bool.
self.apply_user_action(['BulkUpdateRecord', '_grist_Tables_column', [14,15,16,17],
{'isFormula': [False, False, False, False]}])
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[1, "manualSort", "ManualSortPos",False, ""],
[2, "city", "Text", False, ""],
[3, "state", "Text", False, ""],
[4, "size", "Numeric", False, ""],
[13, "A", "Numeric", False, "len($city)"],
[14, "B", "Numeric", False, "1"],
[15, "C", "Text", False, "'x'"],
[16, "D", "Text", False, "$city == 'New York'"],
[17, "E", "Text", False, "$city=='New York' or '-'"],
], rows=lambda r: r.parentId.id == 1)
self.assertTableData('Schools', cols="subset", data=[
["id", "city", "A", "B", "C", "D", "E"],
[1, "New York", 8, 1.0, "x", "True", 'True'],
[2, "Colombia", 8, 1.0, "x", "False", '-' ],
[3, "New York", 8, 1.0, "x", "True", 'True'],
[4, "", 0, 1.0, "x", "False", '-' ],
])
#----------------------------------------------------------------------
def test_useraction_failures(self):
# Verify that when a useraction fails, we revert any changes already applied.
self.load_sample(self.sample)
# Simple failure: bad action (last argument should be a dict). It shouldn't cause any actions
# in the first place, just raise an exception about the argument being an int.
with self.assertRaisesRegex(AttributeError, r"'int'"):
self.apply_user_action(['AddColumn', 'Address', "A", 17])
# Do some successful actions, just to make sure we know what they look like.
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['AddColumn', 'Address', "B", {"isFormula": True}],
['UpdateRecord', 'Address', 11, {"city": "New York2"}],
)])
# More complicated: here some actions should succeed, but get reverted when a later one fails.
with self.assertRaisesRegex(AttributeError, r"'int'"):
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['UpdateRecord', 'Address', 11, {"city": "New York3"}],
['AddColumn', 'Address', "C", {"isFormula": True}],
['AddColumn', 'Address', "D", 17]
)])
with self.assertRaisesRegex(Exception, r"non-existent record #77"):
self.engine.apply_user_actions([useractions.from_repr(ua) for ua in (
['UpdateRecord', 'Address', 11, {"city": "New York4"}],
['UpdateRecord', 'Address', 77, {"city": "Chicago"}],
)])
# Make sure that no columns got added except the intentionally successful one.
self.assertTableData('_grist_Tables_column', cols="subset", data=[
["id", "colId", "type", "isFormula", "formula"],
[21, "city", "Text", False, ""],
[22, "B", "Any", True, ""],
], rows=lambda r: r.parentId.id == 1)
# Make sure that no columns got added here either, and the only change to "New York" is the
# one in the successful user-action.
self.assertTableData('Address', cols="all", data=[
["id", "city" , "B" ],
[11, "New York2" , None ],
[12, "Colombia" , None ],
[13, "New Haven" , None ],
[14, "West Haven", None ],
])
#----------------------------------------------------------------------
def test_pages_remove(self):
# Test that orphan pages get fixed after removing a page
self.init_views_sample()
# Moves page 2 to children of page 1.
self.apply_user_action(['BulkUpdateRecord', '_grist_Pages', [2], {'indentation': [1]}])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 1, 0],
[ 2, 1],
[ 3, 0],
[ 4, 0],
])
# Verify that removing page 1 fixes page 2 indentation.
self.apply_user_action(['RemoveRecord', '_grist_Pages', 1])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 2, 0],
[ 3, 0],
[ 4, 0],
])
# Removing last page should not fail
# Verify that removing page 1 fixes page 2 indentation.
self.apply_user_action(['RemoveRecord', '_grist_Pages', 4])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 2, 0],
[ 3, 0],
])
# Removing a page that has no children should do nothing
self.apply_user_action(['RemoveRecord', '_grist_Pages', 2])
self.assertTableData('_grist_Pages', cols='subset', data=[
['id', 'indentation'],
[ 3, 0],
])
#----------------------------------------------------------------------
def test_rename_choices(self):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "ChoiceTable", [
[1, "ChoiceColumn", "Choice", False, "", "ChoiceColumn", ""],
]],
[2, "ChoiceListTable", [
[2, "ChoiceListColumn", "ChoiceList", False, "", "ChoiceListColumn", ""],
]],
],
"DATA": {
"ChoiceTable": [
["id", "ChoiceColumn"],
[1, "a"],
[2, "b"],
[3, "c"],
[4, "d"],
[5, None],
[6, 5],
[7, [[]]],
],
"ChoiceListTable": [
["id", "ChoiceListColumn"],
[1, ["a"]],
[2, ["b"]],
[3, ["c"]],
[4, ["d"]],
[5, None],
[7, ["a", "b"]],
[8, ["b", "c"]],
[9, ["a", "c"]],
[10, ["a", "b", "c"]],
[11, 5],
[12, [[]]],
],
}
})
self.load_sample(sample)
# Renames go in a loop to make sure that works correctly
# a -> b -> c -> a -> b -> ...
renames = {"a": "b", "b": "c", "c": "a"}
out_actions_choice = self.apply_user_action(
["RenameChoices", "ChoiceTable", "ChoiceColumn", renames])
out_actions_choice_list = self.apply_user_action(
["RenameChoices", "ChoiceListTable", "ChoiceListColumn", renames])
self.assertPartialOutActions(
out_actions_choice,
{'stored':
[['BulkUpdateRecord',
'ChoiceTable',
[1, 2, 3],
{'ChoiceColumn': [u'b', u'c', u'a']}]]})
self.assertPartialOutActions(
out_actions_choice_list,
{'stored':
[['BulkUpdateRecord',
'ChoiceListTable',
[1, 2, 3, 7, 8, 9, 10],
{'ChoiceListColumn': [['L', u'b'],
['L', u'c'],
['L', u'a'],
['L', u'b', u'c'],
['L', u'c', u'a'],
['L', u'b', u'a'],
['L', u'b', u'c', u'a']]}]]})
self.assertTableData('ChoiceTable', data=[
["id", "ChoiceColumn"],
[1, "b"],
[2, "c"],
[3, "a"],
[4, "d"],
[5, None],
[6, 5],
[7, [[]]],
])
self.assertTableData('ChoiceListTable', data=[
["id", "ChoiceListColumn"],
[1, ["b"]],
[2, ["c"]],
[3, ["a"]],
[4, ["d"]],
[5, None],
[7, ["b", "c"]],
[8, ["c", "a"]],
[9, ["b", "a"]],
[10, ["b", "c", "a"]],
[11, 5],
[12, [[]]],
])
# Test filters rename
# Create new view section
self.apply_user_action(["CreateViewSection", 1, 0, "record", None])
# Filter it by first column
self.apply_user_action(['BulkAddRecord', '_grist_Filters', [None], {
"viewSectionRef": [1],
"colRef": [1],
"filter": [json.dumps({"included": ["b", "c"]})]
}])
# Add the same filter for second column (to make sure it is not renamed)
self.apply_user_action(['BulkAddRecord', '_grist_Filters', [None], {
"viewSectionRef": [1],
"colRef": [2],
"filter": [json.dumps({"included": ["b", "c"]})]
}])
# Rename choices
renames = {"b": "z", "c": "b"}
self.apply_user_action(
["RenameChoices", "ChoiceTable", "ChoiceColumn", renames])
# Test filters
self.assertTableData('_grist_Filters', data=[
["id", "colRef", "filter", "setAutoRemove", "viewSectionRef"],
[1, 1, json.dumps({"included": ["z", "b"]}), None, 1],
[2, 2, json.dumps({"included": ["b", "c"]}), None, 1]
])
def test_add_or_update(self):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "Table1", [
[1, "first_name", "Text", False, "", "first_name", ""],
[2, "last_name", "Text", False, "", "last_name", ""],
[3, "pet", "Text", False, "", "pet", ""],
[4, "color", "Text", False, "", "color", ""],
[5, "formula", "Text", True, "''", "formula", ""],
[6, "date", "Date", False, None, "date", ""],
]],
],
"DATA": {
"Table1": [
["id", "first_name", "last_name"],
[1, "John", "Doe"],
[2, "John", "Smith"],
],
}
})
self.load_sample(sample)
def check(require, values, options, stored):
self.assertPartialOutActions(
self.apply_user_action(["AddOrUpdateRecord", "Table1", require, values, options]),
{"stored": stored},
)
# Exactly one match, so on_many=none has no effect
check(
{"first_name": "John", "last_name": "Smith"},
{"pet": "dog", "color": "red"},
{"on_many": "none"},
[["UpdateRecord", "Table1", 2, {"color": "red", "pet": "dog"}]],
)
# Look for a record with pet=dog and change it to pet=cat
check(
{"first_name": "John", "pet": "dog"},
{"pet": "cat"},
{},
[["UpdateRecord", "Table1", 2, {"pet": "cat"}]],
)
# Two records match first_name=John, by default we only update the first
check(
{"first_name": "John"},
{"color": "blue"},
{},
[["UpdateRecord", "Table1", 1, {"color": "blue"}]],
)
# Update all matching records
check(
{"first_name": "John"},
{"color": "green"},
{"on_many": "all"},
[
["UpdateRecord", "Table1", 1, {"color": "green"}],
["UpdateRecord", "Table1", 2, {"color": "green"}],
],
)
# Update all records with empty require and allow_empty_require
check(
{},
{"color": "greener"},
{"on_many": "all", "allow_empty_require": True},
[
["UpdateRecord", "Table1", 1, {"color": "greener"}],
["UpdateRecord", "Table1", 2, {"color": "greener"}],
],
)
# Missing allow_empty_require
with self.assertRaises(ValueError):
check(
{},
{"color": "greenest"},
{},
[],
)
# Don't update any records when there's several matches
check(
{"first_name": "John"},
{"color": "yellow"},
{"on_many": "none"},
[],
)
# Invalid value of on_many
with self.assertRaises(ValueError):
check(
{"first_name": "John"},
{"color": "yellow"},
{"on_many": "other"},
[],
)
# Since there's at least one matching record and update=False, do nothing
check(
{"first_name": "John"},
{"color": "yellow"},
{"update": False},
[],
)
# Since there's no matching records and add=False, do nothing
check(
{"first_name": "John", "last_name": "Johnson"},
{"first_name": "Jack", "color": "yellow"},
{"add": False},
[],
)
# No matching record, make a new one.
# first_name=Jack in `values` overrides first_name=John in `require`
check(
{"first_name": "John", "last_name": "Johnson"},
{"first_name": "Jack", "color": "yellow"},
{},
[
["AddRecord", "Table1", 3,
{"color": "yellow", "first_name": "Jack", "last_name": "Johnson"}]
],
)
# Specifying a row ID in `require` is allowed
check(
{"first_name": "Bob", "id": 100},
{"pet": "fish"},
{},
[["AddRecord", "Table1", 100, {"first_name": "Bob", "pet": "fish"}]],
)
# Now the row already exists
check(
{"first_name": "Bob", "id": 100},
{"pet": "fish"},
{},
[],
)
# Nothing matches this `require`, but the row ID already exists
with self.assertRaises(AssertionError):
check(
{"first_name": "Alice", "id": 100},
{"pet": "fish"},
{},
[],
)
# Formula columns in `require` can't be used as values when creating records
check(
{"formula": "anything"},
{"first_name": "Alice"},
{},
[["AddRecord", "Table1", 101, {"first_name": "Alice"}]],
)
with self.assertRaises(ValueError):
# Row ID too high
check(
{"first_name": "Alice", "id": 2000000},
{"pet": "fish"},
{},
[],
)
# Check that encoded objects are decoded correctly
check(
{"date": ['d', 950400]},
{},
{},
[["AddRecord", "Table1", 102, {"date": 950400}]],
)
check(
{"date": ['d', 950400]},
{"date": ['d', 1900800]},
{},
[["UpdateRecord", "Table1", 102, {"date": 1900800}]],
)
def test_reference_lookup(self):
sample = testutil.parse_test_sample({
"SCHEMA": [
[1, "Table1", [
[1, "name", "Text", False, "", "name", ""],
[2, "ref", "Ref:Table1", False, "", "ref", ""],
[3, "reflist", "RefList:Table1", False, "", "reflist", ""],
]],
],
"DATA": {
"Table1": [
["id", "name"],
[1, "a"],
[2, "b"],
],
}
})
self.load_sample(sample)
self.update_record("_grist_Tables_column", 2, visibleCol=1)
# Normal case
out_actions = self.apply_user_action(
["UpdateRecord", "Table1", 1, {"ref": ["l", "b", {"column": "name"}]}])
self.assertPartialOutActions(out_actions, {'stored': [
["UpdateRecord", "Table1", 1, {"ref": 2}]]})
# Use ref.visibleCol (name) as default lookup column
out_actions = self.apply_user_action(
["UpdateRecord", "Table1", 2, {"ref": ["l", "a"]}])
self.assertPartialOutActions(out_actions, {'stored': [
["UpdateRecord", "Table1", 2, {"ref": 1}]]})
# No match found, generate alttext from value
out_actions = self.apply_user_action(
["UpdateRecord", "Table1", 2, {"ref": ["l", "foo", {"column": "name"}]}])
self.assertPartialOutActions(out_actions, {'stored': [
["UpdateRecord", "Table1", 2, {"ref": "foo"}]]})
# No match found, use provided alttext
out_actions = self.apply_user_action(
["UpdateRecord", "Table1", 2, {"ref": ["l", "foo", {"column": "name", "raw": "alt"}]}])
self.assertPartialOutActions(out_actions, {'stored': [
["UpdateRecord", "Table1", 2, {"ref": "alt"}]]})
# Normal case, adding instead of updating
out_actions = self.apply_user_action(
["AddRecord", "Table1", 3,
{"ref": ["l", "b", {"column": "name"}],
"name": "c"}])
self.assertPartialOutActions(out_actions, {'stored': [
["AddRecord", "Table1", 3,
{"ref": 2,
"name": "c"}]]})
# Testing reflist and bulk action
out_actions = self.apply_user_action(
["BulkUpdateRecord", "Table1", [1, 2, 3],
{"reflist": [
["l", "c", {"column": "name"}], # value gets wrapped in list automatically
["l", ["a", "b"], {"column": "name"}], # normal case
# "a" matches but "foo" doesn't so the whole thing fails
["l", ["a", "foo"], {"column": "name", "raw": "alt"}],
]}])
self.assertPartialOutActions(out_actions, {'stored': [
["BulkUpdateRecord", "Table1", [1, 2, 3],
{"reflist": [
["L", 3],
["L", 1, 2],
"alt",
]}]]})
self.assertTableData('Table1', data=[
["id", "name", "ref", "reflist"],
[1, "a", 2, [3]],
[2, "b", "alt", [1, 2]],
[3, "c", 2, "alt"],
])
# 'id' is used as the default visibleCol
out_actions = self.apply_user_action(
["BulkUpdateRecord", "Table1", [1, 2],
{"reflist": [
["l", 2],
["l", 999], # this row ID doesn't exist
]}])
self.assertPartialOutActions(out_actions, {'stored': [
["BulkUpdateRecord", "Table1", [1, 2],
{"reflist": [
["L", 2],
"999",
]}]]})
def test_raw_view_section_restrictions(self):
# load_sample handles loading basic metadata, but doesn't create any view sections
self.load_sample(self.sample)
# Create a new table which automatically gets a raw view section
self.apply_user_action(["AddEmptyTable"])
# Note the row IDs of the raw view section (2) and fields (4, 5, 6)
self.assertTableData('_grist_Views_section', cols="subset", data=[
["id", "parentId", "tableRef"],
[1, 1, 2],
[2, 0, 2], # the raw view section
])
self.assertTableData('_grist_Views_section_field', cols="subset", data=[
["id", "parentId"],
[1, 1],
[2, 1],
[3, 1],
# the raw view section
[4, 2],
[5, 2],
[6, 2],
])
# Test that the records cannot be removed by normal user actions
with self.assertRaisesRegex(ValueError, "Cannot remove raw view section$"):
self.apply_user_action(["RemoveRecord", '_grist_Views_section', 2])
with self.assertRaisesRegex(ValueError, "Cannot remove raw view section field$"):
self.apply_user_action(["RemoveRecord", '_grist_Views_section_field', 4])
# and most of their column values can't be changed
with self.assertRaisesRegex(ValueError, "Cannot modify raw view section$"):
self.apply_user_action(["UpdateRecord", '_grist_Views_section', 2, {"parentId": 1}])
with self.assertRaisesRegex(ValueError, "Cannot modify raw view section fields$"):
self.apply_user_action(["UpdateRecord", '_grist_Views_section_field', 5, {"parentId": 1}])
# Confirm that the records are unchanged
self.assertTableData('_grist_Views_section', cols="subset", data=[
["id", "parentId", "tableRef"],
[1, 1, 2],
[2, 0, 2], # the raw view section
])
self.assertTableData('_grist_Views_section_field', cols="subset", data=[
["id", "parentId"],
[1, 1],
[2, 1],
[3, 1],
# the raw view section
[4, 2],
[5, 2],
[6, 2],
])
