import enum
import time
import datetime
import logging
import contextlib
import sqlalchemy as sa
import PyQt6.QtCore as qc
import PyQt6.QtGui as qg
import PyQt6.QtWidgets as qw
from .. import wildcard
from .. import mathdb
from . import nulldatetime as zd
from . import qt
from . import guires
from . import checkboxgroup
logger = logging.getLogger(__name__)
[docs]class MatchType(enum.Enum):
EXACT = 1
WILDCARD = 2
REGEXP = 3
[docs]class ViewControlField(qw.QWidget):
"""Base class for fields in TableViewControl widget."""
def __init__(self, model=None, **kwargs):
self._name = ''
self.labelName = qw.QLabel()
super().__init__(**kwargs)
self._model = model
self._settingsGroup = None
self._settingsApplication = None
layout = qw.QGridLayout(self)
layout.addWidget(self.labelName, 0, 0)
@qc.pyqtProperty(str)
def name(self):
return self._name
@name.setter
def name(self, text):
self._name = text
self.labelName.setText(text)
changed = qc.pyqtSignal(object)
activated = qc.pyqtSignal(object)
def sqlCondition(self): # pylint: disable=no-self-use
return None
def saveSettings(self, settings):
settings.setValue('enabled', self.isEnabled())
def restoreSettings(self, settings):
self._settingsGroup = settings.group()
self._settingsApplication = settings.applicationName()
with contextlib.suppress(Exception):
if settings.contains('enabled'):
isEnabled = settings.value('enabled', type=bool)
self.setEnabled(isEnabled)
self.setVisible(isEnabled)
class TextEditField: # pylint: disable=too-few-public-methods
def __init__(self, **kwargs):
# pylint: disable=no-member
super().__init__(**kwargs)
self.editor = qw.QLineEdit()
self.layout().addWidget(self.editor, 1, 0)
self.editor.textChanged.connect(
lambda: self.changed.emit(self))
self.editor.returnPressed.connect(
lambda: self.activated.emit(self))
@property
def text(self):
return self.editor.text()
@text.setter
def text(self, s):
self.editor.setText(s)
[docs]class SearchField(ViewControlField):
def __init__(self, field=None, **kwargs):
self._columnIndex = None
super().__init__(**kwargs)
self._field = field
if not self.name:
self.name = self._field.name
@qc.pyqtProperty(int)
def columnIndex(self):
return self._columnIndex
@columnIndex.setter
def columnIndex(self, index):
self._columnIndex = index
@staticmethod
def new(field, **kwargs):
if field.pytype is not None and issubclass(
field.pytype, datetime.date):
return DateSearchField(field=field, **kwargs)
return TextSearchField(field=field, **kwargs)
[docs]class TextSearchField(TextEditField, SearchField):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self._hasDefault = False
self._caseSensitivity = False
self._matchType = MatchType.EXACT
self.editor.customContextMenuRequested.connect(self.onContextMenu)
self.editor.setContextMenuPolicy(qc.Qt.ContextMenuPolicy.CustomContextMenu)
@qc.pyqtProperty(bool)
def caseSensitivity(self):
return self._caseSensitivity
@caseSensitivity.setter
def caseSensitivity(self, b):
if b != self._caseSensitivity:
self._caseSensitivity = b
self.changed.emit(self)
self.activated.emit(self)
@qc.pyqtProperty(MatchType)
def matchType(self):
return self._matchType
@matchType.setter
def matchType(self, t):
if t != self._matchType:
self._matchType = t
self.changed.emit(self)
self.activated.emit(self)
@staticmethod
def patternSQLCondition(expr, pattern, matchType, caseSensitive):
if not caseSensitive:
pattern = pattern.lower()
if matchType == MatchType.EXACT:
pattern = wildcard.exact_like(pattern)
elif matchType == MatchType.WILDCARD:
pattern = wildcard.wildcard_like(pattern)
expr = sa.func.binary(expr) if caseSensitive else sa.func.lower(expr)
if matchType in (MatchType.EXACT, MatchType.WILDCARD):
return expr.like(pattern)
return expr.op('REGEXP')(pattern)
def sqlCondition(self):
if not self.isEnabled() or not self.text:
return None
return self.patternSQLCondition(
self._field.textexpr, self.text,
self.matchType, self.caseSensitivity)
def onContextMenu(self, pos):
menu = self.editor.createStandardContextMenu()
menu.setParent(self.editor)
menu.addSeparator()
# Case sensitivity menu item
menu.addAction(qg.QAction(
'Case sensitive', parent=menu, checkable=True,
checked=self.caseSensitivity,
triggered=self.onCaseSensitivity))
menu.addSeparator()
# Pattern type menu items
mt = self.matchType
actionExact = qg.QAction(
'Exact match', checkable=True,
checked=mt == MatchType.EXACT,
triggered=lambda: self.onMatchType(MatchType.EXACT))
actionWildcard = qg.QAction(
'Wildcard match', checkable=True,
checked=mt == MatchType.WILDCARD,
triggered=lambda: self.onMatchType(MatchType.WILDCARD))
actionRegExp = qg.QAction(
'Regular expression', checkable=True,
checked=mt == MatchType.REGEXP,
triggered=lambda: self.onMatchType(MatchType.REGEXP))
menu.addAction(actionExact)
menu.addAction(actionWildcard)
menu.addAction(actionRegExp)
menu.matchGroup = qg.QActionGroup(self)
menu.matchGroup.addAction(actionExact)
menu.matchGroup.addAction(actionWildcard)
menu.matchGroup.addAction(actionRegExp)
menu.addSeparator()
# Set/clear default menu items
menu.addAction(qg.QAction(
'Set as default', parent=menu,
triggered=lambda: self.onSetDefault(True)))
menu.addAction(qg.QAction(
'Clear default', parent=menu, enabled=self._hasDefault,
triggered=lambda: self.onSetDefault(False)))
menu.popup(self.editor.mapToGlobal(pos))
def onCaseSensitivity(self, b):
self.caseSensitivity = b
def onMatchType(self, t):
self.matchType = t
def saveSearchData(self, settings):
settings.setValue('hasDefault', self._hasDefault)
settings.setValue('text', self.text if self._hasDefault else '')
settings.setValue('caseSensitivity', self.caseSensitivity)
settings.setValue('matchType', self.matchType)
def onSetDefault(self, b):
self._hasDefault = b
settings = guires.settings(self._settingsApplication)
settings.beginGroup(self._settingsGroup)
self.saveSearchData(settings)
def saveSettings(self, settings):
super().saveSettings(settings)
if not self._hasDefault:
self.saveSearchData(settings)
def restoreSettings(self, settings):
super().restoreSettings(settings)
with contextlib.suppress(Exception):
self._hasDefault = settings.value('hasDefault', type=bool)
self.text = settings.value('text')
self.caseSensitivity = settings.value(
'caseSensitivity', type=bool)
self.matchType = settings.value('matchType', type=MatchType)
[docs]class AllSearchField(TextSearchField):
def sqlCondition(self):
if not self.isEnabled() or not self.text:
return None
exprList = [
sa.func.coalesce(self._model.field(i).textexpr, '')
for i in range(self._model.columnCount())
# if not self.view.isColumnHidden(i)
]
if not exprList:
return None
exprBarList = ['|'] * (2 * len(exprList) - 1)
exprBarList[::2] = exprList
expr = sa.func.replace(
sa.func.concat(*exprBarList), sa.text("b'0'"), '')
return self.patternSQLCondition(
expr, self.text, self.matchType, self.caseSensitivity)
[docs]class DateSearchField(SearchField):
def __init__(self, **kwargs):
self.columnIndex = kwargs.pop('columnIndex')
super().__init__(**kwargs)
labelTo = qw.QLabel('To:')
self.editFrom = zd.NullableDateEdit(calendarPopup=True)
self.editTo = zd.NullableDateEdit(calendarPopup=True)
self.layout().addWidget(labelTo, 0, 1)
self.layout().addWidget(self.editFrom, 1, 0)
self.layout().addWidget(self.editTo, 1, 1)
self.labelName.setText(self.name + ' From:')
self.editFrom.dateChanged.connect(
lambda: self.changed.emit(self))
self.editFrom.dateChanged.connect(
lambda: self.activated.emit(self))
self.editTo.dateChanged.connect(
lambda: self.changed.emit(self))
self.editTo.dateChanged.connect(
lambda: self.activated.emit(self))
# Default dates
self._needs_default_dates = True
self._default_dates = []
self.defaultDatesAvailable.connect(self.set_default_dates)
defaultDatesAvailable = qc.pyqtSignal()
[docs] def showEvent(self, event):
if self._needs_default_dates:
self._needs_default_dates = False
if self._model.rowCount() == 0:
# Default dates should be queried after table is populated.
self._model.dataChanged.connect(self.schedule_default_dates)
else:
qt.app.updater.schedule(
time.time() - 0.1, 0, self.query_default_dates)
super().showEvent(event)
def schedule_default_dates(self):
self._model.dataChanged.disconnect(self.schedule_default_dates)
qt.app.updater.schedule(
time.time() - 0.1, 0, self.query_default_dates)
# Called from updater thread
def query_default_dates(self):
try:
query = self._model.query.with_session(mathdb.Session()).filter(
self._field.expr.isnot(None))
first_record = query.order_by(self._field.expr).first()
last_record = query.order_by(self._field.expr.desc()).first()
first_date = first_record and first_record[self._field.index]
last_date = last_record and last_record[self._field.index]
logger.debug(
'DateSearchField.query_default_dates() %s %s',
first_date, last_date)
self._default_dates.extend([first_date, last_date])
self.defaultDatesAvailable.emit()
except Exception as e:
logger.exception('DateSearchField.query_default_dates(): %s', e)
def set_default_dates(self):
if self._default_dates[0]:
self.editFrom.defaultDateTime = qc.QDateTime(
self._default_dates[0])
if self._default_dates[1]:
self.editTo.defaultDateTime = qc.QDateTime(self._default_dates[1])
def sqlCondition(self):
if not self.isEnabled():
return None
dateFrom = self.editFrom.date()
dateTo = self.editTo.date()
exprFrom = None if not dateFrom.isValid() else (
self._field.expr >= dateFrom.toPyDate())
exprTo = None if not dateTo.isValid() else (
self._field.expr <= dateTo.toPyDate())
if exprFrom is None:
return exprTo
if exprTo is None:
return exprFrom
return sa.and_(exprFrom, exprTo)
[docs]class LimitField(TextEditField, ViewControlField):
def __init__(self, **kwargs):
super().__init__(**kwargs)
self.name = 'Limit'
self.editor.setText('100')
self.editor.setValidator(qg.QIntValidator(0, 9999999))
self.setFixedSize(self.minimumSizeHint())
[docs]class TableViewControl(qw.QWidget):
"""Search area for database table view."""
def __init__(self, model, view, **kwargs):
super().__init__(**kwargs)
self.model = model
self.view = view
# Add menu button
layout = qw.QHBoxLayout(self)
menu = qw.QMenu()
menu.addAction(qg.QAction(
'Select Search Columns...', triggered=self.selectSearchColumns,
parent=menu))
menu.addAction(qg.QAction(
'Select Display Columns...', triggered=self.selectDisplayColumns,
parent=menu))
buttonMenu = guires.IconToolButton(
'Images/Common/menu.png',
popupMode=qw.QToolButton.ToolButtonPopupMode.InstantPopup)
buttonMenu.setMenu(menu)
layout.addWidget(buttonMenu)
# Add search fields
self.splitterSearch = qw.QSplitter(qc.Qt.Orientation.Horizontal, parent=self)
layout.addWidget(self.splitterSearch)
self.splitterSearch.addWidget(
AllSearchField(model=self.model, name='Any Column'))
for index in range(model.columnCount()):
field = model.field(index)
self.splitterSearch.addWidget(
SearchField.new(
field=field, columnIndex=index, model=model,
enabled=False, visible=False,))
# Add matches field
self.widgetMatches = InformationField(name='Matches')
self.widgetMatches.setFixedSize(self.widgetMatches.minimumSizeHint())
self.model.modelReset.connect(self.setMatches)
self.model.layoutChanged.connect(self.setMatches)
self.model.rowsInserted.connect(self.setMatches)
self.model.rowsRemoved.connect(self.setMatches)
self.splitterSearch.addWidget(self.widgetMatches)
# Set view options
self.view.setSortingEnabled(True)
self.view.setEditTriggers(qw.QAbstractItemView.EditTrigger.NoEditTriggers)
self.view.setWordWrap(False)
self.view.horizontalHeader().setDefaultAlignment(qc.Qt.AlignmentFlag.AlignLeft)
self.view.verticalHeader().setVisible(False)
self.view.setModel(self.model)
# Limit field
# self.widgetLimit = LimitField()
# self.splitterSearch.insertWidget(
# self.splitterSearch.count() - 1, self.widgetLimit)
# Set filter actions
for idx in range(self.splitterSearch.count()):
self.splitterSearch.widget(idx).activated.connect(
self.onSearchChanged)
def selectSearchColumns(self):
nameList = [self.splitterSearch.widget(idx).name
for idx in range(self.splitterSearch.count())]
valueList = [self.splitterSearch.widget(idx).isEnabled()
for idx in range(self.splitterSearch.count())]
dialog = checkboxgroup.CheckBoxDialog(
windowTitle='Select Search Columns', parent=self,
nameList=nameList, valueList=valueList)
if dialog.exec() != qw.QDialog.DialogCode.Accepted:
return
for idx in range(self.splitterSearch.count()):
val = dialog.isChecked(idx)
self.splitterSearch.widget(idx).setEnabled(val)
self.splitterSearch.widget(idx).setVisible(val)
self.onSearchChanged()
def selectDisplayColumns(self):
nameList = [self.model.headerData(idx, qc.Qt.Orientation.Horizontal)
for idx in range(self.model.columnCount())]
valueList = [not self.view.isColumnHidden(idx)
for idx in range(self.model.columnCount())]
dialog = checkboxgroup.CheckBoxDialog(
windowTitle='Select Display Columns', parent=self,
nameList=nameList, valueList=valueList)
checkResize = qw.QCheckBox('Resize columns')
dialog.layout().insertWidget(2, checkResize)
if dialog.exec() != qw.QDialog.DialogCode.Accepted:
return
for idx in range(self.model.columnCount()):
self.view.setColumnHidden(idx, not dialog.isChecked(idx))
self.onSearchChanged()
if checkResize.isChecked():
self.view.horizontalHeader().resizeSections(
qw.QHeaderView.ResizeMode.ResizeToContents)
def onSearchChanged(self):
conditionList = list(filter(lambda cond: cond is not None, [
self.splitterSearch.widget(i).sqlCondition()
for i in range(self.splitterSearch.count())
]))
self.model.setFilter(conditionList)
self.setMatches()
def setMatches(self):
nMatches = self.view.model().rowCount()
self.widgetMatches.information = str(nMatches)
def saveSettings(self, settings):
for idx in range(self.splitterSearch.count()):
field = self.splitterSearch.widget(idx)
settings.beginGroup(field.name)
field.saveSettings(settings)
settings.endGroup()
settings.setValue(
'splitterSearch', self.splitterSearch.saveState())
self.view.saveSettings(settings)
def restoreSettings(self, settings):
with contextlib.suppress(Exception):
self.splitterSearch.restoreState(settings.value('splitterSearch'))
for idx in range(self.splitterSearch.count()):
field = self.splitterSearch.widget(idx)
settings.beginGroup(field.name)
field.restoreSettings(settings)
settings.endGroup()
self.view.restoreSettings(settings)
self.onSearchChanged()