def setModelData(self, editor, model, index):
"""Emits the data_committed signal with new data."""
if isinstance(editor, NumberParameterInlineEditor):
self.data_committed.emit(index, to_database(editor.data()))
return
value = self._str_to_int_or_float(editor.data())
self.data_committed.emit(index, to_database(value))
def test_time_series_in_tool_tip_role(self):
value = TimeSeriesFixedResolution("2019-07-12T08:00", ["7 hours", "12 hours"], [1.1, 2.2, 3.3], False, False)
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.ToolTipRole)
self.assertEqual(formatted, "Start: 2019-07-12 08:00:00, resolution: [7h, 12h], length: 3")
value = TimeSeriesVariableResolution(["2019-07-12T08:00", "2019-07-12T16:00"], [0.0, 100.0], False, False)
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.ToolTipRole)
self.assertEqual(formatted, "Start: 2019-07-12T08:00:00, resolution: variable, length: 2")
def test_time_series_in_edit_role(self):
value = TimeSeriesFixedResolution("2019-07-12T08:00", "7 hours", [1.1, 2.2, 3.3], False, False)
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, to_database(value))
value = TimeSeriesVariableResolution(["2019-07-12T08:00", "2019-07-12T16:00"], [0.0, 100.0], False, False)
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, to_database(value))
def _check_parent_model_updated_when_closed(self, value):
model = _MockParentModel()
model_index = model.index(1, 1)
model.setData(model_index, to_database(value))
editor = ParameterValueEditor(model_index)
# Reset model data to check that the value is written back from the editor
model.setData(model_index, None)
editor.accept()
editor.deleteLater()
self.assertEqual(model.data(model_index), to_database(value))
def save_positions(self, checked=False):
items_per_class_id = {}
for item in self.selected_items:
items_per_class_id.setdefault(item.entity_class_id, []).append(item)
pos_def_class_ids = {
p["entity_class_id"]
for p in self.db_mngr.get_items_by_field(
self.db_map, "parameter definition", "parameter_name", self._POS_PARAM_NAME
)
}
defs_to_add = [
{"name": self._POS_PARAM_NAME, "entity_class_id": class_id}
for class_id in items_per_class_id.keys() - pos_def_class_ids
]
if defs_to_add:
self.db_mngr.add_parameter_definitions({self.db_map: defs_to_add})
pos_def_id_lookup = {
p["entity_class_id"]: p["id"]
for p in self.db_mngr.get_items_by_field(
self.db_map, "parameter definition", "parameter_name", self._POS_PARAM_NAME
)
}
pos_val_id_lookup = {
(p["entity_class_id"], p["entity_id"]): p["id"]
for p in self.db_mngr.get_items_by_field(
self.db_map, "parameter value", "parameter_name", self._POS_PARAM_NAME
)
}
vals_to_add = list()
vals_to_update = list()
for class_id, items in items_per_class_id.items():
for item in items:
pos_val_id = pos_val_id_lookup.get((class_id, item.entity_id), None)
value = {"type": "map", "index_type": "str", "data": [["x", item.pos().x()], ["y", item.pos().y()]]}
if pos_val_id is None:
vals_to_add.append(
{
"name": "pos_x",
"entity_class_id": class_id,
"entity_id": item.entity_id,
"parameter_definition_id": pos_def_id_lookup[class_id],
"value": to_database(value),
}
)
else:
vals_to_update.append({"id": pos_val_id, "value": to_database(value)})
if vals_to_add:
self.db_mngr.add_parameter_values({self.db_map: vals_to_add})
if vals_to_update:
self.db_mngr.update_parameter_values({self.db_map: vals_to_update})
def data(self, index, role=Qt.DisplayRole):
"""Returns model's data for given role."""
if not index.isValid() or not self._data:
return None
if role == Qt.DisplayRole:
element = self._data[index.row()]
if isinstance(element, (float, str)):
return element
if isinstance(element, _ErrorCell):
return "Error"
return str(element)
if role == Qt.EditRole:
element = self._data[index.row()]
if isinstance(element, _ErrorCell):
return element.edit_value
return to_database(self._data[index.row()])
if role == Qt.ToolTipRole:
element = self._data[index.row()]
if isinstance(element, _ErrorCell):
return element.tooltip
return str(element)
if role == Qt.BackgroundColorRole:
element = self._data[index.row()]
if isinstance(element, _ErrorCell):
return QColor(255, 128, 128)
return None
return None
def copy(self):
"""Copy current selection to clipboard in Excel compatible csv format."""
selection = self.selectionModel().selection()
if not selection:
return False
top, bottom, left, right = _range(selection)
model = self.model()
out_table = list()
for y in range(top, bottom + 1):
row = (right - left + 1) * [None]
for x in range(left, right + 1):
index = model.index(y, x)
if not selection.contains(index):
continue
data = index.data(Qt.EditRole)
try:
number = float(data)
str_data = locale.str(number)
except ValueError:
str_data = str(data)
except TypeError:
if isinstance(data, IndexedValue):
str_data = to_database(data)
else:
str_data = str(data)
row[x - left] = str_data
out_table.append(row)
with io.StringIO() as output:
writer = csv.writer(output, delimiter="\t", quotechar="'")
writer.writerows(out_table)
QApplication.clipboard().setText(output.getvalue())
return True
def update_value_list_in_db(self, child, value):
value_list = self.compile_value_list()
value = to_database(value)
try:
value_list[child.child_number()] = value
except IndexError:
value_list.append(value)
db_item = dict(id=self.id, value_list=value_list)
self.db_mngr.update_parameter_value_lists({self.db_map: [db_item]})
def _set_data(self, value):
"""See base class."""
try:
value = to_database(value)
except ParameterValueFormatError as error:
message = f"Cannot set value: {error}"
QMessageBox.warning(self, "Parameter Value error", message)
return False
self.set_data_delayed(value)
return True
def accept(self):
"""Saves the parameter value shown in the currently selected editor widget to the database manager."""
editor = self._ui.editor_stack.currentWidget()
try:
value = to_database(editor.value())
except ParameterValueFormatError as error:
message = "Cannot set value: {}".format(error)
QMessageBox.warning(self, "Parameter Value error", message)
return
self.set_data_delayed(value)
self.close()
def accept(self):
"""Saves the parameter value shown in the currently selected editor widget back to the parent model."""
editor = self._ui.editor_stack.currentWidget()
try:
self._parent_model.setData(self._parent_index,
to_database(editor.value()))
except ParameterValueFormatError as error:
message = "Cannot set value: {}".format(error)
QMessageBox.warning(self, "Parameter Value error", message)
return
self.close()
def _write_relationships_to_xlsx(wb, relationship_data):
"""Writes Classes, parameter and parameter values for relationships.
Writes one sheet per relationship class.
Args:
wb (openpyxl.Workbook): excel workbook to write too.
relationship_data (List[List]): List of lists containing relationship
data give by function get_unstacked_relationships
"""
for rel in relationship_data:
ws = wb.create_sheet()
# try setting the sheet name to relationship class name
# sheet name can only be 31 chars log
title = "rel_" + rel[0]
if len(title) < 32:
ws.title = title
ws['A1'] = "Sheet type"
ws['A2'] = "relationship"
ws['B1'] = "Data type"
ws['B2'] = "Parameter"
ws['C1'] = "relationship class name"
ws['C2'] = rel[0]
ws['D1'] = "Number of relationship dimensions"
ws['D2'] = len(rel[2])
ws['E1'] = "Number of pivoted relationship dimensions"
ws['E2'] = 0
for c, val in enumerate(rel[2]):
ws.cell(row=4, column=c + 1).value = val
for c, val in enumerate(rel[3]):
ws.cell(row=4, column=len(rel[2]) + 1 + c).value = val
start_row = 5
start_col = 1
for r, line in enumerate(rel[1]):
for c, val in enumerate(line):
if isinstance(val, (Duration, DateTime)):
val = to_database(val)
ws.cell(row=start_row + r, column=start_col + c).value = val
def data(self, index, role=Qt.DisplayRole):
"""Returns the data associated with the given role."""
if role not in (Qt.DisplayRole, Qt.EditRole) or not index.isValid():
return None
row_index = index.row()
column_index = index.column()
row = self._rows[row_index]
if (role == Qt.DisplayRole and column_index < len(row) - 1
and row[column_index + 1] is not None and row_index > 0):
indexes_above = self._rows[row_index - 1][:column_index + 1]
current_indexes = self._rows[row_index][:column_index + 1]
if current_indexes == indexes_above:
return None
data = row[column_index]
if role == Qt.EditRole:
return to_database(data if data is not None else "")
if isinstance(data, DateTime):
return str(data.value)
if isinstance(data, Duration):
return str(data)
return data
def _write_objects_to_xlsx(wb, object_data):
"""Writes Classes, parameter and parameter values for objects.
Writes one sheet per relationship/object class.
Args:
wb (openpyxl.Workbook): excel workbook to write too.
object_data (List[List]): List of lists containing relationship data give by function get_unstacked_objects
"""
for i, obj in enumerate(object_data):
ws = wb.create_sheet()
# try setting the sheet name to object class name
# sheet name can only be 31 chars log
title = "obj_" + obj[0]
if len(title) < 32:
ws.title = title
else:
ws.title = "object_class{}".format(i)
ws['A1'] = "Sheet type"
ws['A2'] = "object"
ws['B1'] = "Data type"
ws['B2'] = "Parameter"
ws['C1'] = "object class name"
ws['C2'] = obj[0]
for c, val in enumerate(obj[2]):
ws.cell(row=4, column=c + 1).value = val
for c, val in enumerate(obj[3]):
ws.cell(row=4, column=len(obj[2]) + 1 + c).value = val
start_row = 5
start_col = 1
for r, line in enumerate(obj[1]):
for c, val in enumerate(line):
if isinstance(val, (Duration, DateTime)):
val = to_database(val)
ws.cell(row=start_row + r, column=start_col + c).value = val
def test_date_time_in_tool_tip_role(self):
value = DateTime("2019-07-12T16:00")
self.db_mngr.get_item.return_value = {"value": to_database(value)}
self.assertIsNone(self.get_value(Qt.ToolTipRole))
def test_time_pattern_in_edit_role(self):
value = TimePattern(["1-12m"], [5.0])
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, to_database(value))
def _make_pivot_proxy_model():
"""Returns a prefilled PivotTableModel."""
db_mngr = MagicMock()
db_mngr.get_value.side_effect = lambda db_map, item_type, id_, role: from_database(
id_)
mock_db_map = MagicMock()
mock_db_map.codename = "codename"
db_mngr.undo_action.__getitem__.side_effect = lambda key: QAction()
db_mngr.redo_action.__getitem__.side_effect = lambda key: QAction()
with patch.object(SpineDBEditor,
"restore_ui"), patch.object(SpineDBEditor, "show"):
spine_db_editor = SpineDBEditor(db_mngr, mock_db_map)
spine_db_editor.create_header_widget = lambda *args, **kwargs: None
simple_map = Map(["a", "b"], [-1.1, -2.2])
nested_map = Map(
["a", "b"],
[
Map([DateTime("2020-11-13T11:00"),
DateTime("2020-11-13T12:00")], [-1.1, -2.2]),
Map([DateTime("2020-11-13T11:00"),
DateTime("2020-11-13T12:00")], [-3.3, -4.4]),
],
)
nested_map_with_time_series = Map(
["a", "b"],
[
Map(
[DateTime("2020-11-13T11:00"),
DateTime("2020-11-13T12:00")],
[
TimeSeriesVariableResolution(
["2020-11-13T11:00", "2020-11-13T12:00"], [-1.1, -2.2],
False, False),
TimeSeriesVariableResolution(
["2020-11-13T12:00", "2020-11-13T13:00"], [-3.3, -4.4],
False, False),
],
),
Map(
[DateTime("2020-11-13T11:00"),
DateTime("2020-11-13T12:00")],
[
TimeSeriesVariableResolution(
["2020-11-13T11:00", "2020-11-13T12:00"], [-5.5, -6.6],
False, False),
TimeSeriesVariableResolution(
["2020-11-13T12:00", "2020-11-13T13:00"], [-7.7, -8.8],
False, False),
],
),
],
)
data = {
('1', 'int_col', 'base_alternative'):
'-3',
('2', 'int_col', 'base_alternative'):
'-1',
('3', 'int_col', 'base_alternative'):
'2',
('1', 'float_col', 'base_alternative'):
'1.1',
('2', 'float_col', 'base_alternative'):
'1.2',
('3', 'float_col', 'base_alternative'):
'1.3',
(
'1',
'time_series_col',
'base_alternative',
):
'{"type": "time_series", "data": {"2019-07-10T13:00": 2.3, "2019-07-10T13:20": 5.0}}',
(
'2',
'time_series_col',
'base_alternative',
):
'{"type": "time_series", "index": {"start": "2019-07-10T13:00", "resolution": "20 minutes"}, "data": [3.3, 4.0]}',
(
'3',
'time_series_col',
'base_alternative',
):
'{"type": "time_series", "data": {"2019-07-10T13:00": 4.3, "2019-07-10T13:20": 3.0}}',
("1", "map_col", "base_alternative"):
to_database(simple_map),
("2", "map_col", "base_alternative"):
to_database(nested_map),
("3", "map_col", "base_alternative"):
to_database(nested_map_with_time_series),
}
data = {
tuple((db, k) for k in key) + (db, ): (db, value)
for key, value in data.items()
}
spine_db_editor.load_parameter_value_data = lambda: data
spine_db_editor.pivot_table_model = model = ParameterValuePivotTableModel(
spine_db_editor)
with patch.object(
SpineDBEditor, "current_object_class_ids",
new_callable=PropertyMock) as mock_current_object_class_ids:
mock_current_object_class_ids.return_value = {"object": {db: 1}}
model.call_reset_model(pivot=(['object'], ['parameter', 'alternative'],
['database'], (db, )))
model.start_fetching()
spine_db_editor.pivot_table_model = model
spine_db_editor.pivot_table_proxy.setSourceModel(model)
return spine_db_editor.pivot_table_proxy
def setModelData(self, editor, model, index):
"""Sends signal."""
self.data_committed.emit(index, to_database(editor.data()))
def test_time_pattern_in_tool_tip_role(self):
value = TimePattern(["1-12m"], [5.0])
self.db_mngr.get_item.return_value = {"value": to_database(value)}
self.assertIsNone(self.get_value(Qt.ToolTipRole))
def test_plain_number_in_edit_role(self):
value = 2.3
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, "2.3")
def compile_value_list(self):
return [to_database(child.value) for child in self.children[:-1]]
def test_plain_number_in_tool_tip_role(self):
value = 2.3
self.db_mngr.get_item.return_value = {"value": to_database(value)}
self.assertIsNone(self.get_value(Qt.ToolTipRole))
def test_date_time_in_edit_role(self):
value = DateTime("2019-07-12T16:00")
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, to_database(value))
def setModelData(self, editor, model, index):
"""Send signal."""
data = editor.data()
if isinstance(editor, ParameterValueLineEditor):
data = to_database(data)
self.data_committed.emit(index, data)
def test_duration_in_display_role(self):
value = Duration("3Y")
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.DisplayRole)
self.assertEqual(formatted, "3Y")
def test_duration_in_edit_role(self):
value = Duration("2M")
self.db_mngr.get_item.return_value = {"value": to_database(value)}
formatted = self.get_value(Qt.EditRole)
self.assertEqual(formatted, to_database(value))
def set_data(self, column, value, role=Qt.EditRole):
if role != Qt.EditRole:
return False
value = to_database(value)
return self.set_data_in_db(value)
def test_duration_in_tool_tip_role(self):
value = Duration("13D")
self.db_mngr.get_item.return_value = {"value": to_database(value)}
self.assertIsNone(self.get_value(Qt.ToolTipRole))
def setModelData(self, editor, model, index):
"""Send signal."""
display_value = editor.data()
db_value = self._db_value_list_lookup.get(display_value,
to_database(display_value))
self.data_committed.emit(index, db_value)
def _create_database(directory):
"""Creates a database with objects, relationship, parameters and values."""
url = TestExcelIntegration._sqlite_url(_TEMP_SQLITE_FILENAME,
directory)
create_new_spine_database(url)
db_map = DiffDatabaseMapping(url,
username='******',
upgrade=True)
# create empty database for loading excel into
url = TestExcelIntegration._sqlite_url(_TEMP_SQLITE_TEST_FILENAME,
directory)
create_new_spine_database(url)
db_map_test = DiffDatabaseMapping(url,
username='******',
upgrade=True)
# delete all object_classes to empty database
oc = set(oc.id for oc in db_map_test.object_class_list().all())
if oc:
db_map_test.remove_items(object_class_ids=oc)
db_map_test.commit_session('empty database')
oc = set(oc.id for oc in db_map.object_class_list().all())
if oc:
db_map.remove_items(object_class_ids=oc)
db_map.commit_session('empty database')
# create object classes
oc_1 = db_map.add_object_class(**{'name': 'object_class_1'})
oc_2 = db_map.add_object_class(**{'name': 'object_class_2'})
oc_3 = db_map.add_object_class(**{'name': 'object_class_3'})
# create relationship classes
relc1 = db_map.add_wide_relationship_class(
**{
'name': 'relationship_class',
'object_class_id_list': [oc_1.id, oc_2.id]
})
relc2 = db_map.add_wide_relationship_class(
**{
'name': 'relationship_class2',
'object_class_id_list': [oc_1.id, oc_2.id]
})
# create objects
oc1_obj1 = db_map.add_object(**{
'name': 'oc1_obj1',
'class_id': oc_1.id
})
oc1_obj2 = db_map.add_object(**{
'name': 'oc1_obj2',
'class_id': oc_1.id
})
oc2_obj1 = db_map.add_object(**{
'name': 'oc2_obj1',
'class_id': oc_2.id
})
oc2_obj2 = db_map.add_object(**{
'name': 'oc2_obj2',
'class_id': oc_2.id
})
oc3_obj1 = db_map.add_object(**{
'name': 'oc3_obj1',
'class_id': oc_3.id
})
# add relationships
rel1 = db_map.add_wide_relationship(
**{
'name': 'rel1',
'class_id': relc1.id,
'object_id_list': [oc1_obj1.id, oc2_obj1.id]
})
rel2 = db_map.add_wide_relationship(
**{
'name': 'rel2',
'class_id': relc1.id,
'object_id_list': [oc1_obj2.id, oc2_obj2.id]
})
# create parameters
p1 = db_map.add_parameter_definitions(*[{
'name': 'parameter1',
'object_class_id': oc_1.id
}])[0].first()
p2 = db_map.add_parameter_definitions(*[{
'name': 'parameter2',
'object_class_id': oc_1.id
}])[0].first()
p3 = db_map.add_parameter_definitions(*[{
'name': 'parameter3',
'object_class_id': oc_2.id
}])[0].first()
p4 = db_map.add_parameter_definitions(*[{
'name': 'parameter4',
'object_class_id': oc_2.id
}])[0].first()
p5 = db_map.add_parameter_definitions(*[{
'name': 'parameter5',
'object_class_id': oc_3.id
}])[0].first()
p6 = db_map.add_parameter_definitions(*[{
'name': 'parameter6',
'object_class_id': oc_3.id
}])[0].first()
rel_p1 = db_map.add_parameter_definitions(
*[{
'name': 'rel_parameter1',
'relationship_class_id': relc1.id
}])[0].first()
rel_p2 = db_map.add_parameter_definitions(
*[{
'name': 'rel_parameter2',
'relationship_class_id': relc1.id
}])[0].first()
rel_p3 = db_map.add_parameter_definitions(
*[{
'name': 'rel_parameter3',
'relationship_class_id': relc1.id
}])[0].first()
rel_p4 = db_map.add_parameter_definitions(
*[{
'name': 'rel_parameter4',
'relationship_class_id': relc1.id
}])[0].first()
# add parameter values
db_map.add_parameter_value(
**{
'parameter_definition_id': p1.id,
'object_id': oc1_obj1.id,
'object_class_id': oc_1.id,
'value': '0'
})
db_map.add_parameter_value(
**{
'parameter_definition_id': p2.id,
'object_id': oc1_obj2.id,
'object_class_id': oc_1.id,
'value': '3.5'
})
db_map.add_parameter_value(
**{
'parameter_definition_id': p3.id,
'object_id': oc2_obj1.id,
'object_class_id': oc_2.id,
'value': '[1, 2, 3, 4]',
})
db_map.add_parameter_value(
**{
'parameter_definition_id': p4.id,
'object_id': oc2_obj2.id,
'object_class_id': oc_2.id,
'value': '[5, 6, 7]',
})
db_map.add_parameter_value(
**{
'parameter_definition_id': rel_p1.id,
'relationship_id': rel1.id,
'relationship_class_id': relc1.id,
'value': '0',
})
db_map.add_parameter_value(
**{
'parameter_definition_id': rel_p2.id,
'relationship_id': rel2.id,
'relationship_class_id': relc1.id,
'value': '4',
})
db_map.add_parameter_value(
**{
'parameter_definition_id': rel_p3.id,
'relationship_id': rel1.id,
'relationship_class_id': relc1.id,
'value': '[5, 6, 7]',
})
db_map.add_parameter_value(
**{
'parameter_definition_id': rel_p4.id,
'relationship_id': rel2.id,
'relationship_class_id': relc1.id,
'value': '[1, 2, 3, 4]',
})
time = [
np.datetime64('2005-02-25T00:00'),
np.datetime64('2005-02-25T01:00'),
np.datetime64('2005-02-25T02:00')
]
value = [1, 2, 3]
ts_val = to_database(
TimeSeriesVariableResolution(time, value, False, False))
db_map.add_parameter_value(
**{
'parameter_definition_id': p5.id,
'object_id': oc3_obj1.id,
'object_class_id': oc_3.id,
'value': ts_val
})
timepattern = ['m1', 'm2', 'm3']
value = [1.1, 2.2, 3.3]
ts_val = to_database(TimePattern(timepattern, value))
db_map.add_parameter_value(
**{
'parameter_definition_id': p6.id,
'object_id': oc3_obj1.id,
'object_class_id': oc_3.id,
'value': ts_val
})
# commit
db_map.commit_session('test')
return db_map, db_map_test