class AlchemyManagerEnginesView(TableAdminView):
"""SQLAlchemy manager engines view"""
title = _("SQL engines")
table_class = AlchemyManagerEnginesTable
table_label = _("List of SQL engines")
@property
def back_url(self):
"""Form back URL getter"""
return absolute_url(self.request.root, self.request,
'admin#utilities.html') # pylint: disable=no-member
back_url_target = None
def handle_new_engine_data_extraction(event):
"""Handle new engine data"""
name = event.data['name'] or ''
engine = query_utility(IAlchemyEngineUtility, name=name)
if engine is not None:
event.form.widgets.errors += (Invalid(_("An SQLAlchemy engine is already "
"registered with this name!")),)
class IAlchemyTaskInfo(Interface):
"""SQLAlchemy scheduler task interface"""
session_name = Choice(
title=_("SQL session name"),
description=_("Name of the SQLAlchemy engine used to access database"),
vocabulary=ALCHEMY_ENGINES_VOCABULARY,
required=True)
query = Text(title=_("SQL query text"), required=True)
output_format = Choice(
title=_("Output format"),
description=_("Format into which query output should be returned"),
vocabulary=ALCHEMY_CONVERTERS_VOCABULARY,
required=True,
default='json')
class AlchemyEngineCloneColumn(ActionColumn):
"""SQLAlchemy engine clone column"""
hint = _("Clone SQL engine")
icon_class = 'far fa-clone'
href = 'clone-sql-engine.html'
weight = 100
class AlchemyEngineAddForm(AlchemyEngineBaseAddFormMixin, AdminModalAddForm): # pylint: disable=abstract-method
"""SQLAlchemy engine add form"""
legend = _("New engine properties")
fields = Fields(IAlchemyEngineUtility)
content_factory = IAlchemyEngineUtility
def add(self, obj):
oid = IUniqueID(obj).oid
self.context[oid] = obj
class AlchemyEngineCloneForm(AlchemyEngineBaseAddFormMixin, AdminModalAddForm):
"""SQLAlchemy engine clone form"""
legend = _("Clone SQL connection")
fields = Fields(IAlchemyEngineUtility).select('name')
def create(self, data):
return copy(self.context)
def add(self, obj):
oid = IUniqueID(obj).oid
self.context.__parent__[oid] = obj
def update_widgets(self, prefix=None):
"""Widgets update"""
super().update_widgets(prefix) # pylint: disable=no-member
session = self.widgets.get('session_name') # pylint: disable=no-member
if session is not None:
translate = self.request.localizer.translate # pylint: disable=no-member
session.placeholder = translate(_("Select connection name..."))
query = self.widgets.get('query') # pylint: disable=no-member
if query is not None:
query.add_class('height-100')
query.widget_css_class = "editor height-400px"
query.object_data = {
'ams-filename': 'query.sql'
}
alsoProvides(query, IObjectData)
class AlchemyEngineEditForm(AdminModalEditForm):
"""SQLAlchemy engine properties edit form"""
@property
def title(self):
translate = self.request.localizer.translate
manager = query_utility(IAlchemyManager)
return '<small>{}</small><br />{}'.format(
get_object_label(manager, self.request, self),
translate(_("SQL engine: {}")).format(self.context.name))
legend = _("SQL engine properties")
fields = Fields(IAlchemyEngineUtility)
def update_widgets(self, prefix=None):
super().update_widgets(prefix)
name = self.widgets.get('name')
if name is not None:
name.mode = DISPLAY_MODE
class AlchemyTaskFormInfo(GroupManager):
"""SQLAlchemy task form info"""
title = _("SQL task settings")
fields = Fields(IAlchemyTaskInfo)
def update_widgets(self, prefix=None):
"""Widgets update"""
super().update_widgets(prefix) # pylint: disable=no-member
session = self.widgets.get('session_name') # pylint: disable=no-member
if session is not None:
translate = self.request.localizer.translate # pylint: disable=no-member
session.placeholder = translate(_("Select connection name..."))
query = self.widgets.get('query') # pylint: disable=no-member
if query is not None:
query.add_class('height-100')
query.widget_css_class = "editor height-400px"
query.object_data = {
'ams-filename': 'query.sql'
}
alsoProvides(query, IObjectData)
class AlchemyTask(Task):
"""SQLAlchemy task"""
label = _("SQL query")
icon_class = 'fas fa-database'
session_name = FieldProperty(IAlchemyTask['session_name'])
query = FieldProperty(IAlchemyTask['query'])
output_format = FieldProperty(IAlchemyTask['output_format'])
def run(self, report, **kwargs): # pylint: disable=unused-argument
"""Run SQL query task"""
try:
session = get_user_session(self.session_name, join=False, twophase=False,
use_zope_extension=False)
try:
report.write('SQL query output\n'
'================\n')
report.write('SQL query: \n {}\n\n'.format(
self.query.replace('\r', '').replace('\n', '\n ')))
results = session.execute(self.query)
session.commit()
converter = get_utility(IAlchemyConverter, name=self.output_format)
result = converter.convert(results)
report.write('SQL output ({} records):\n\n'.format(results.rowcount))
report.write(result)
return TASK_STATUS_OK, result
except ResourceClosedError:
report.write('SQL query returned no result.\n')
return TASK_STATUS_EMPTY, None
except SQLAlchemyError:
session.rollback()
etype, value, tb = sys.exc_info() # pylint: disable=invalid-name
report.write('\n\n'
'An SQL error occurred\n'
'=====================\n')
report.write(''.join(traceback.format_exception(etype, value, tb)))
return TASK_STATUS_ERROR, None
class AlchemyTaskAddMenu(MenuItem):
"""SQLAlchemy task add menu"""
label = _("Add SQL query...")
href = 'add-sql-task.html'
modal_target = True
class AlchemyManagerEnginesListMenu(NavigationMenuItem):
"""SQLAlchemy manager engines list menu"""
label = _("SQL engines")
icon_class = 'fas fa-table'
href = '#engines-list.html'
class IAlchemyEngineUtility(IAttributeAnnotatable):
"""SQLAlchemy engine definition interface"""
name = TextLine(
title=_("Engine name"),
description=_("Keep empty if this engine is the default engine..."),
required=False,
default='')
dsn = TextLine(
title=_('DSN'),
description=_('RFC-1738 compliant URL for the database connection'),
required=True,
default=u'sqlite://')
echo = Bool(title=_('Echo SQL?'),
description=_("Log all SQL statements to system logger"),
required=True,
default=False)
use_pool = Bool(
title=_("Use connections pool?"),
description=_("If 'no', collections pooling will be disabled"),
required=True,
default=True)
pool_size = Int(title=_("Pool size"),
description=_("SQLAlchemy connections pool size"),
required=False,
default=25)
pool_recycle = Int(
title=_("Pool recycle time"),
description=_("SQLAlchemy connection recycle time (-1 for none)"),
required=False,
default=-1)
echo_pool = Bool(
title=_("Echo pool?"),
description=_("Log all pool checkouts/checkins to system logger?"),
required=True,
default=False)
encoding = Choice(title=_('Encoding'),
required=True,
vocabulary=ENCODINGS_VOCABULARY_NAME,
default='utf-8')
convert_unicode = Bool(title=_('Convert Unicode'),
required=True,
default=False)
twophase = Bool(title=_("Two-phases commit?"),
description=_(
"Disable this option if two-phases commits should be "
"disabled (for SQLite for example)"),
required=True,
default=True)
def get_engine(self, use_pool=True):
"""Get SQLAlchemy engine"""
def clear_engine(self):
"""Remove inner volatile attributes when utility properties are modified"""
class IAlchemyManagerRoles(IContentRoles):
"""SQLAlchemy manager roles"""
sql_managers = PrincipalsSetField(title=_("SQL managers"),
role_id=SQL_MANAGER_ROLE,
required=False)
class AlchemyEngineAddAction(ContextAddAction):
"""Alchemy engine add action"""
label = _("Add SQL engine")
href = 'add-sql-engine.html'
def title(self):
translate = self.request.localizer.translate
manager = query_utility(IAlchemyManager)
return '<small>{}</small><br />{}'.format(
get_object_label(manager, self.request, self),
translate(_("SQL engine: {}")).format(self.context.name))
from pyams_utils.adapter import ContextRequestViewAdapter, adapter_config
from pyams_utils.url import absolute_url
from pyams_viewlet.manager import viewletmanager_config
from pyams_zmi.helper.container import delete_container_element
from pyams_zmi.interfaces import IAdminLayer, IObjectLabel
from pyams_zmi.interfaces.table import ITableElementEditor
from pyams_zmi.interfaces.viewlet import IMenuHeader, IPropertiesMenu, ISiteManagementMenu
from pyams_zmi.table import NameColumn, Table, TableAdminView, \
TableElementEditor, TrashColumn
from pyams_zmi.zmi.viewlet.menu import NavigationMenuItem
__docformat__ = 'restructuredtext'
from pyams_alchemy import _ # pylint: disable=ungrouped-imports
ALCHEMY_MANAGER_LABEL = _("SQL connections")
@adapter_config(required=(IAlchemyManager, IPyAMSLayer, Interface),
provides=IObjectLabel)
def alchemy_manager_label(context, request, view):
"""Alchemy manager label"""
return request.localizer.translate(ALCHEMY_MANAGER_LABEL)
@adapter_config(required=(IAlchemyManager, IAdminLayer, Interface,
ISiteManagementMenu),
provides=IMenuHeader)
def alchemy_manager_menu_header(context, request, view, manager): # pylint: disable=unused-argument
"""SQLAlchemy manager menu header"""
return ALCHEMY_MANAGER_LABEL
def title(self):
translate = self.request.localizer.translate
return '<small>{}</small><br />{}'.format(
get_object_label(self.context, self.request, self),
translate(_("New SQL engine")))