class AbstractModel(QtCore.QAbstractTableModel):
filters_changed_signal = QtCore.pyqtSignal()
error_signal = QtCore.pyqtSignal(str)
def __init__(self, table: Table):
super(AbstractModel, self).__init__()
self.query_manager = QueryManager(table=table)
self.original_data = []
self.modified_data = []
self.visible_data = []
# variables needed for pagination
self.rows_per_page = 50
self.rows_loaded = 50
# Connect Signals
self.query_manager.query_results_signal.connect(self.update_view)
def add_row(self, ix: QtCore.QModelIndex) -> None:
dummies = {
FieldType.bool: True
, FieldType.int: 0
, FieldType.float: 0.0
, FieldType.str: ''
, FieldType.date: '1900-01-01'
}
dummy_row = [] # type: List
for fld in self.query_manager.table.fields:
dummy_row.append(dummies[fld.dtype])
for k, v in self.query_manager.table.foreign_keys.items():
dummy_row[k] = next(fk for fk in self.foreign_keys[k])
dummy_row[self.query_manager.table.primary_key_index] = uuid.uuid4().int
self.visible_data.insert(ix.row(), dummy_row)
self.modified_data.insert(0, dummy_row)
self.dataChanged.emit(ix, ix)
def canFetchMore(self, index=QtCore.QModelIndex()):
if len(self.visible_data) > self.rows_loaded:
return True
return False
@property
def changes(self) -> Dict[str, set]:
if not self.query_manager.table.editable:
return # safe guard
pk = self.query_manager.table.primary_key_index
original = set(map(tuple, self.original_data))
modified = set(map(tuple, self.modified_data))
changed_ids = set(row[pk] for row in original ^ modified)
updated = set(
row for row in modified
if row[pk] in changed_ids
and row[pk] in {row[pk] for row in original}
)
added = (modified - original) - updated
deleted = set(
row for row in (original - modified)
if row[pk] not in {row[pk] for row in updated}
)
return {
'added': added
, 'deleted': deleted
, 'updated': updated
}
def fetchMore(self, index=QtCore.QModelIndex()):
remainder = len(self.visible_data) - self.rows_loaded
rows_to_fetch = min(remainder, self.rows_per_page)
self.beginInsertRows(
QtCore.QModelIndex()
, self.rows_loaded
, self.rows_loaded + rows_to_fetch - 1
)
self.rows_loaded += rows_to_fetch
self.endInsertRows()
def field_totals(self, col_ix: ColumnIndex) -> list:
totals = []
fld = self.query_manager.table.fields[col_ix]
rows = self.rowCount()
if fld.dtype == FieldType.float:
total = sum(val[col_ix] for val in self.visible_data)
avg = total / rows if rows > 0 else 0
totals.append('{} Sum \t = {:,.2f}'.format(fld.name, float(total)))
totals.append('{} Avg \t = {:,.2f}'.format(fld.name, float(avg)))
elif fld.dtype == FieldType.date:
minimum = min(val[col_ix] for val in self.visible_data)
maximum = max(val[col_ix] for val in self.visible_data)
totals.append('{} Min \t = {}'.format(fld.name, minimum or 'Empty'))
totals.append('{} Max \t = {}'.format(fld.name, maximum or 'Empty'))
else:
totals.append('{} Distinct Count \t = {}'.format(fld.name
, len(set(val[col_ix] for val in self.visible_data))))
return totals
def columnCount(self, parent: QtCore.QModelIndex=None) -> int:
return len(self.query_manager.table.fields)
def data(self, index: QtCore.QModelIndex, role: int=QtCore.Qt.DisplayRole):
alignment = {
FieldType.bool: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter,
FieldType.date: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter,
FieldType.int: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter,
FieldType.float: QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter,
FieldType.str: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter,
}
col = index.column()
fld = self.query_manager.table.fields[col]
val = self.visible_data[index.row()][col]
try:
if not index.isValid():
return
elif role == QtCore.Qt.TextAlignmentRole:
return alignment[fld.dtype]
elif role == QtCore.Qt.DisplayRole:
if col in self.foreign_keys.keys():
return self.foreign_keys[col][val]
return fld.format_value(val)
except Exception as e:
self.error_signal.emit('Error modeling data: {}'.format(e))
def delete_row(self, ix: QtCore.QModelIndex) -> None:
row = ix.row()
pk = self.visible_data[row][self.query_manager.table.primary_key_index]
mod_row = next(
i for i, r
in enumerate(self.modified_data)
if r[self.query_manager.table.primary_key_index] == pk
)
del self.visible_data[row]
del self.modified_data[mod_row]
self.dataChanged.emit(ix, ix)
def distinct_values(self, col_ix: ColumnIndex) -> List[str]:
return SortedSet(
str(self.fk_lookup(col=col_ix, val=row[col_ix]))
for row in self.visible_data
)
def filter_equality(self, col_ix: ColumnIndex, val: SqlDataType) -> None:
self.visible_data = [
row for row in self.visible_data
if row[col_ix] == val
]
self.filters_changed_signal.emit()
def filter_greater_than(self, col_ix, val) -> None:
lkp = partial(self.fk_lookup, col=col_ix)
self.visible_data = [
row for row in self.visible_data
if lkp(row[col_ix]) >= lkp(val)
]
self.sort(col=col_ix, order=QtCore.Qt.AscendingOrder)
self.filters_changed_signal.emit()
def filter_less_than(self, col_ix, val) -> None:
lkp = partial(self.fk_lookup, col=col_ix)
self.visible_data = [
row for row in self.visible_data
if lkp(row[col_ix]) <= lkp(val)
]
self.sort(col=col_ix, order=QtCore.Qt.DescendingOrder)
self.filters_changed_signal.emit()
def filter_like(self, val: str, col_ix: Optional[ColumnIndex]=None) -> None:
self.layoutAboutToBeChanged.emit()
lkp = partial(self.fk_lookup, col=col_ix)
def normalize(value: str) -> str:
return str(value).lower()
def is_like(input_val: str, row: Tuple[SqlDataType], col: int) -> bool:
if col:
if normalize(input_val) in normalize(lkp(row[col])):
return True
else:
if normalize(input_val) in ' '.join([
normalize(self.fk_lookup(val=v, col=c))
for c, v in enumerate(row)
]):
return True
return False
self.visible_data = [
row for row in self.modified_data
if is_like(val, row, col_ix)
]
self.layoutChanged.emit()
self.filters_changed_signal.emit()
def filter_set(self, col: int, values: Set[str]) -> None:
self.visible_data = [
row for row in self.visible_data
if self.fk_lookup(col=col, val=row[col]) in values
]
self.filters_changed_signal.emit()
def flags(self, ix: QtCore.QModelIndex) -> int:
if ix.column() in self.query_manager.editable_fields_indices:
return (
QtCore.Qt.ItemIsEditable
| QtCore.Qt.ItemIsEnabled
| QtCore.Qt.ItemIsSelectable
)
return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable
def fk_lookup(self, val, col) -> SqlDataType:
if col in self.query_manager.table.foreign_keys.keys():
return self.foreign_keys[col][val]
return val
@property
def foreign_keys(self) -> Dict[ColumnIndex, Dict[int, str]]:
return {
ColumnIndex(k): cfg.foreign_keys(v.dimension)
for k, v in self.query_manager.table.foreign_keys.items()
}
def full_reset(self) -> None:
self.layoutAboutToBeChanged.emit()
self.original_data = []
self.modified_data = []
self.visible_data = []
self.layoutChanged.emit()
self.filters_changed_signal.emit()
def headerData(self, col: ColumnIndex, orientation: int, role: QtCore.Qt.DisplayRole) -> List[str]:
if orientation == QtCore.Qt.Horizontal and role == QtCore.Qt.DisplayRole:
return self.query_manager.headers[col]
def pull(self) -> None:
self.rows_loaded = self.rows_per_page
self.query_manager.pull()
def primary_key(self, row: int) -> int:
"""Return the primary key value of the specified row"""
return row[self.query_manager.table.primary_key_index]
@QtCore.pyqtSlot(str)
def query_errored(self, msg) -> None:
self.error_signal.emit(msg)
def reset(self) -> None:
"""reset filters - not pending changes"""
self.layoutAboutToBeChanged.emit()
self.visible_data = self.modified_data
self.filters_changed_signal.emit()
self.layoutChanged.emit()
def rowCount(self, index: Optional[QtCore.QModelIndex]=None) -> int:
if self.visible_data:
if len(self.visible_data) <= self.rows_loaded:
return len(self.visible_data)
return self.rows_loaded
return 0
def save(self) -> Optional[Dict[str, int]]:
chg = self.changes
if chg['added'] or chg['deleted'] or chg['updated']:
try:
# print('changes:', self.changes)
results = self.query_manager.save_changes(chg)
def update_id(old_id, new_id):
row = next(
i for i, row in enumerate(self.modified_data)
if row[self.query_manager.table.primary_key_index] == old_id
)
self.modified_data[row][self.query_manager.table.primary_key_index] = new_id
for m in results['new_rows_id_map']:
update_id(m[0], m[1])
self.original_data = deepcopy(self.modified_data)
if self.query_manager.table in cfg.dimensions:
cfg.pull_foreign_keys(self.query_manager.table.table_name)
return results
except:
raise
# else no changes to save, view displays 'no changes' when this function returns None
def setData(self, ix: QtCore.QModelIndex, value: SqlDataType, role: int=QtCore.Qt.EditRole) -> bool:
try:
pk = self.visible_data[ix.row()][self.query_manager.table.primary_key_index]
row = next(
i for i, row
in enumerate(self.modified_data)
if row[self.query_manager.table.primary_key_index] == pk
)
self.visible_data[ix.row()][ix.column()] = value
self.modified_data[row][ix.column()] = value
self.dataChanged.emit(ix, ix)
return True
except:
return False
def sort(self, col: ColumnIndex, order: int) -> None:
"""sort table by given column number col"""
try:
self.layoutAboutToBeChanged.emit()
if col in self.foreign_keys.keys():
self.visible_data = sorted(
self.visible_data
, key=lambda row: self.fk_lookup(row[col], col)
)
else:
self.visible_data = sorted(
self.visible_data
, key=operator.itemgetter(col)
)
if order == QtCore.Qt.DescendingOrder:
self.visible_data.reverse()
self.layoutChanged.emit()
except Exception as e:
err_msg = "Error sorting data: {}".format(e)
self.error_signal.emit(err_msg)
def undo(self) -> None:
self.layoutAboutToBeChanged.emit()
self.modified_data = deepcopy(self.original_data)
self.visible_data = deepcopy(self.original_data)
self.layoutChanged.emit()
@QtCore.pyqtSlot(list)
def update_view(self, results) -> None:
self.layoutAboutToBeChanged.emit()
self.original_data = results
self.visible_data = deepcopy(results)
self.modified_data = deepcopy(results)
self.layoutChanged.emit()
class AbstractModel(QtCore.QAbstractTableModel):
filters_changed_signal = QtCore.pyqtSignal()
error_signal = QtCore.pyqtSignal(str)
def __init__(self, table: Table):
super(AbstractModel, self).__init__()
self.query_manager = QueryManager(table=table)
self.original_data = []
self.modified_data = []
self.visible_data = []
# variables needed for pagination
self.rows_per_page = 50
self.rows_loaded = 50
# Connect Signals
self.query_manager.query_results_signal.connect(self.update_view)
def add_row(self, ix: QtCore.QModelIndex) -> None:
dummies = {
FieldType.bool: True,
FieldType.int: 0,
FieldType.float: 0.0,
FieldType.str: '',
FieldType.date: '1900-01-01'
}
dummy_row = [] # type: List
for fld in self.query_manager.table.fields:
dummy_row.append(dummies[fld.dtype])
for k, v in self.query_manager.table.foreign_keys.items():
dummy_row[k] = next(fk for fk in self.foreign_keys[k])
dummy_row[
self.query_manager.table.primary_key_index] = uuid.uuid4().int
self.visible_data.insert(ix.row(), dummy_row)
self.modified_data.insert(0, dummy_row)
self.dataChanged.emit(ix, ix)
def canFetchMore(self, index=QtCore.QModelIndex()):
if len(self.visible_data) > self.rows_loaded:
return True
return False
@property
def changes(self) -> Dict[str, set]:
if not self.query_manager.table.editable:
return # safe guard
pk = self.query_manager.table.primary_key_index
original = set(map(tuple, self.original_data))
modified = set(map(tuple, self.modified_data))
changed_ids = set(row[pk] for row in original ^ modified)
updated = set(row for row in modified if row[pk] in changed_ids
and row[pk] in {row[pk]
for row in original})
added = (modified - original) - updated
deleted = set(row for row in (original - modified)
if row[pk] not in {row[pk]
for row in updated})
return {'added': added, 'deleted': deleted, 'updated': updated}
def fetchMore(self, index=QtCore.QModelIndex()):
remainder = len(self.visible_data) - self.rows_loaded
rows_to_fetch = min(remainder, self.rows_per_page)
self.beginInsertRows(QtCore.QModelIndex(), self.rows_loaded,
self.rows_loaded + rows_to_fetch - 1)
self.rows_loaded += rows_to_fetch
self.endInsertRows()
def field_totals(self, col_ix: ColumnIndex) -> list:
totals = []
fld = self.query_manager.table.fields[col_ix]
rows = self.rowCount()
if fld.dtype == FieldType.float:
total = sum(val[col_ix] for val in self.visible_data)
avg = total / rows if rows > 0 else 0
totals.append('{} Sum \t = {:,.2f}'.format(fld.name, float(total)))
totals.append('{} Avg \t = {:,.2f}'.format(fld.name, float(avg)))
elif fld.dtype == FieldType.date:
minimum = min(val[col_ix] for val in self.visible_data)
maximum = max(val[col_ix] for val in self.visible_data)
totals.append('{} Min \t = {}'.format(fld.name, minimum
or 'Empty'))
totals.append('{} Max \t = {}'.format(fld.name, maximum
or 'Empty'))
else:
totals.append('{} Distinct Count \t = {}'.format(
fld.name, len(set(val[col_ix] for val in self.visible_data))))
return totals
def columnCount(self, parent: QtCore.QModelIndex = None) -> int:
return len(self.query_manager.table.fields)
def data(self,
index: QtCore.QModelIndex,
role: int = QtCore.Qt.DisplayRole):
alignment = {
FieldType.bool: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter,
FieldType.date: QtCore.Qt.AlignHCenter | QtCore.Qt.AlignVCenter,
FieldType.int: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter,
FieldType.float: QtCore.Qt.AlignRight | QtCore.Qt.AlignVCenter,
FieldType.str: QtCore.Qt.AlignLeft | QtCore.Qt.AlignVCenter,
}
col = index.column()
fld = self.query_manager.table.fields[col]
val = self.visible_data[index.row()][col]
try:
if not index.isValid():
return
elif role == QtCore.Qt.TextAlignmentRole:
return alignment[fld.dtype]
elif role == QtCore.Qt.DisplayRole:
if col in self.foreign_keys.keys():
return self.foreign_keys[col][val]
return fld.format_value(val)
except Exception as e:
self.error_signal.emit('Error modeling data: {}'.format(e))
def delete_row(self, ix: QtCore.QModelIndex) -> None:
row = ix.row()
pk = self.visible_data[row][self.query_manager.table.primary_key_index]
mod_row = next(i for i, r in enumerate(self.modified_data)
if r[self.query_manager.table.primary_key_index] == pk)
del self.visible_data[row]
del self.modified_data[mod_row]
self.dataChanged.emit(ix, ix)
def distinct_values(self, col_ix: ColumnIndex) -> List[str]:
return SortedSet(
str(self.fk_lookup(col=col_ix, val=row[col_ix]))
for row in self.visible_data)
def filter_equality(self, col_ix: ColumnIndex, val: SqlDataType) -> None:
self.visible_data = [
row for row in self.visible_data if row[col_ix] == val
]
self.filters_changed_signal.emit()
def filter_greater_than(self, col_ix, val) -> None:
lkp = partial(self.fk_lookup, col=col_ix)
self.visible_data = [
row for row in self.visible_data if lkp(row[col_ix]) >= lkp(val)
]
self.sort(col=col_ix, order=QtCore.Qt.AscendingOrder)
self.filters_changed_signal.emit()
def filter_less_than(self, col_ix, val) -> None:
lkp = partial(self.fk_lookup, col=col_ix)
self.visible_data = [
row for row in self.visible_data if lkp(row[col_ix]) <= lkp(val)
]
self.sort(col=col_ix, order=QtCore.Qt.DescendingOrder)
self.filters_changed_signal.emit()
def filter_like(self,
val: str,
col_ix: Optional[ColumnIndex] = None) -> None:
self.layoutAboutToBeChanged.emit()
lkp = partial(self.fk_lookup, col=col_ix)
def normalize(value: str) -> str:
return str(value).lower()
def is_like(input_val: str, row: Tuple[SqlDataType], col: int) -> bool:
if col:
if normalize(input_val) in normalize(lkp(row[col])):
return True
else:
if normalize(input_val) in ' '.join([
normalize(self.fk_lookup(val=v, col=c))
for c, v in enumerate(row)
]):
return True
return False
self.visible_data = [
row for row in self.modified_data if is_like(val, row, col_ix)
]
self.layoutChanged.emit()
self.filters_changed_signal.emit()
def filter_set(self, col: int, values: Set[str]) -> None:
self.visible_data = [
row for row in self.visible_data
if self.fk_lookup(col=col, val=row[col]) in values
]
self.filters_changed_signal.emit()
def flags(self, ix: QtCore.QModelIndex) -> int:
if ix.column() in self.query_manager.editable_fields_indices:
return (QtCore.Qt.ItemIsEditable
| QtCore.Qt.ItemIsEnabled
| QtCore.Qt.ItemIsSelectable)
return QtCore.Qt.ItemIsEnabled | QtCore.Qt.ItemIsSelectable
def fk_lookup(self, val, col) -> SqlDataType:
if col in self.query_manager.table.foreign_keys.keys():
return self.foreign_keys[col][val]
return val
@property
def foreign_keys(self) -> Dict[ColumnIndex, Dict[int, str]]:
return {
ColumnIndex(k): cfg.foreign_keys(v.dimension)
for k, v in self.query_manager.table.foreign_keys.items()
}
def full_reset(self) -> None:
self.layoutAboutToBeChanged.emit()
self.original_data = []
self.modified_data = []
self.visible_data = []
self.layoutChanged.emit()
self.filters_changed_signal.emit()
def headerData(self, col: ColumnIndex, orientation: int,
role: QtCore.Qt.DisplayRole) -> List[str]:
if orientation == QtCore.Qt.Horizontal and role == QtCore.Qt.DisplayRole:
return self.query_manager.headers[col]
def pull(self) -> None:
self.rows_loaded = self.rows_per_page
self.query_manager.pull()
def primary_key(self, row: int) -> int:
"""Return the primary key value of the specified row"""
return row[self.query_manager.table.primary_key_index]
@QtCore.pyqtSlot(str)
def query_errored(self, msg) -> None:
self.error_signal.emit(msg)
def reset(self) -> None:
"""reset filters - not pending changes"""
self.layoutAboutToBeChanged.emit()
self.visible_data = self.modified_data
self.filters_changed_signal.emit()
self.layoutChanged.emit()
def rowCount(self, index: Optional[QtCore.QModelIndex] = None) -> int:
if self.visible_data:
if len(self.visible_data) <= self.rows_loaded:
return len(self.visible_data)
return self.rows_loaded
return 0
def save(self) -> Optional[Dict[str, int]]:
chg = self.changes
if chg['added'] or chg['deleted'] or chg['updated']:
try:
# print('changes:', self.changes)
results = self.query_manager.save_changes(chg)
def update_id(old_id, new_id):
row = next(
i for i, row in enumerate(self.modified_data)
if row[self.query_manager.table.primary_key_index] ==
old_id)
self.modified_data[row][
self.query_manager.table.primary_key_index] = new_id
for m in results['new_rows_id_map']:
update_id(m[0], m[1])
self.original_data = deepcopy(self.modified_data)
if self.query_manager.table in cfg.dimensions:
cfg.pull_foreign_keys(self.query_manager.table.table_name)
return results
except:
raise
# else no changes to save, view displays 'no changes' when this function returns None
def setData(self,
ix: QtCore.QModelIndex,
value: SqlDataType,
role: int = QtCore.Qt.EditRole) -> bool:
try:
pk = self.visible_data[ix.row()][
self.query_manager.table.primary_key_index]
row = next(
i for i, row in enumerate(self.modified_data)
if row[self.query_manager.table.primary_key_index] == pk)
self.visible_data[ix.row()][ix.column()] = value
self.modified_data[row][ix.column()] = value
self.dataChanged.emit(ix, ix)
return True
except:
return False
def sort(self, col: ColumnIndex, order: int) -> None:
"""sort table by given column number col"""
try:
self.layoutAboutToBeChanged.emit()
if col in self.foreign_keys.keys():
self.visible_data = sorted(
self.visible_data,
key=lambda row: self.fk_lookup(row[col], col))
else:
self.visible_data = sorted(self.visible_data,
key=operator.itemgetter(col))
if order == QtCore.Qt.DescendingOrder:
self.visible_data.reverse()
self.layoutChanged.emit()
except Exception as e:
err_msg = "Error sorting data: {}".format(e)
self.error_signal.emit(err_msg)
def undo(self) -> None:
self.layoutAboutToBeChanged.emit()
self.modified_data = deepcopy(self.original_data)
self.visible_data = deepcopy(self.original_data)
self.layoutChanged.emit()
@QtCore.pyqtSlot(list)
def update_view(self, results) -> None:
self.layoutAboutToBeChanged.emit()
self.original_data = results
self.visible_data = deepcopy(results)
self.modified_data = deepcopy(results)
self.layoutChanged.emit()
