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_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_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], ])