def update(self):
self.buttons = button.Buttons(
self.buttons,
button.Button('catalogupdate', _(u'Catalog Update')),
button.Button('reindex', _(u'Catalog Reindex')),
button.Button('unindex', _(u'Catalog Unindex')))
super(SQLTypeOverviewForm, self).update()
class IFieldSQLBehavior(model.Schema):
sql_column = schema.Choice(
title=_(u"label_column", default=u"Column"),
description=_(
u"help_column",
default=u"Corresponding column in database source table."),
required=False,
vocabulary="collective.behavior.sql.AvailableSQLAlchemyColumns")
class ISQLContent(model.Schema):
"""Add SQL content"""
sql_id = schema.Choice(
title=_(u"label_sql_id", default=u"SQL Item ID"),
description=_(u"help_sql_id_column",
default=u"The ID of the SQL content item"),
required=False,
vocabulary="collective.behavior.sql.AvailableSQLAlchemyItemIDs")
form.omitted('sql_virtual')
sql_virtual = schema.Bool(title=_(u"label_sql_virtual",
default=u"SQL Virtual"),
default=False)
class ITestForm(form.Schema):
""" Define form fields """
connection = schema.Choice(title=_(u"label_connection",
default=u"Connection"),
vocabulary=SimpleVocabulary([]))
table = schema.Choice(title=_(u"label_table", default=u"Table"),
required=False,
values=[u'a', u'b', u'c'])
columns = schema.List(title=_(u"label_columns", default=u"Columns"),
required=False,
value_type=schema.Choice(values=[]))
class SQLTypeDataListingPage(SQLTypeFormLayout):
label = _(u'Data')
form = SQLTypeDataListingForm
@property
def tabs(self):
return super(SQLTypeDataListingPage, self).tabs
class ISQLTypeSettings(ITypeSettings):
sql_connection = schema.Choice(
title=_(u"label_connection", default=u"Connection"),
vocabulary="collective.behavior.sql.AvailableSQLAlchemyConnections"
)
sql_table = schema.Choice(
title=_(u"label_table", default=u"Table"),
required=False,
vocabulary="collective.behavior.sql.AvailableSQLAlchemyTables"
)
sql_id_column = schema.Choice(
title=_(u"label_id_column", default=u"ID Column"),
description=_(u"help_id_column", default=u"The PrimaryKey column that will give the id to the item."),
required=False,
vocabulary="collective.behavior.sql.AvailableSQLAlchemyColumnsUnique"
)
sql_WHERE = schema.TextLine(
title=_(u"label_sql_WHERE", default=u"WHERE"),
description=_(u"help_sql_WHERE", default=u"Use a custom WHERE clause to filter the sql table. You must specify only the WHERE clause and not the whole query clause. Example:type = 'public'"),
required=False,
)
sql_modification_timestamp_column = schema.Choice(
title=_(u"label_sql_modification_timestamp_column", default=u"Modification date time column"),
description=_(u"help_sql_modification_timestamp_column", default=u"The last modificaion date time column that will be taken into account to know which items have to be updated in the catalog."),
required=False,
vocabulary="collective.behavior.sql.AvailableSQLAlchemyTimestampColumns"
)
sql_modification_last_timestamp = schema.Datetime(
title=_(u"label_sql_modification_last_timestamp", default=u"Last modification date time"),
description=_(u"help_sql_modification_last_timestamp", default=u"Modification date of the last modified item in the selected table."),
required=False,
)
sql_folder_id = schema.Choice(
title=_(u'label_sql_folder_id', default=u'Folder for SQL items'),
description=_(u"help_sql_folder_id", default=u"Choose a folder where the sql items will be located. If empty, a virtual folder named 'data-'+ type.id will be registered."),
required=False,
source=CatalogSource(portal_type=['Folder'])
)
def tabs(self):
tabs = super(SQLTypeFormLayout, self).tabs
tabs += ((_('Data'), '@@data'),)
return tabs
def tabs(self):
tabs = super(SQLTypeBehaviorsPage, self).tabs
tabs += ((_('Data'), '@@data'),)
return tabs
def tabs(self):
tabs = super(SQLTypeOverviewPage, self).tabs
tabs += ((_('Data'), '@@data'), )
return tabs
def update(self):
self.buttons = button.Buttons(
self.buttons, button.Button('catalogupdate', _(u'Catalog Update')),
button.Button('reindex', _(u'Catalog Reindex')),
button.Button('unindex', _(u'Catalog Unindex')))
super(SQLTypeOverviewForm, self).update()
def tabs(self):
tabs = super(SQLTypeFieldsPage, self).tabs
tabs += ((_('Data'), '@@data'), )
return tabs
@property
def tabs(self):
tabs = super(SQLTypeFieldsPage, self).tabs
tabs += ((_('Data'), '@@data'), )
return tabs
@property
def form(self):
if self.context.fti.hasDynamicSchema:
return SQLEnhancedSchemaListing
else:
return ReadOnlySchemaListing
TupleFactory = FieldFactory(schema.Tuple,
_(u'label_tuple_field', default=u'Tuple'),
value_type=schema.Choice(values=[]))
class ITuple(schema.interfaces.ITuple, schema.interfaces.IFromUnicode):
pass
class SQLCollectionTermsVocabulary(CollectionTermsVocabulary):
"""ITerms adapter for zope.schema.ICollection based implementations using
vocabulary."""
zope.component.adapts(ISQLDexterityItem, iz3cform.IFormLayer,
zope.interface.Interface,
zope.schema.interfaces.ICollection,
zope.schema.interfaces.IBaseVocabulary,
def tabs(self):
tabs = super(SQLTypeOverviewPage, self).tabs
tabs += ((_('Data'), '@@data'),)
return tabs
class TestView(form.Form):
ignoreContext = True
template = ViewPageTemplateFile('templates/sql_test.pt')
def __init__(self, context, request):
super(TestView, self).__init__(context, request)
self.saconnect = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot))
self.Base = None
self.tablesVoc = []
self.table = []
self.columnsVoc = []
self.columns = []
self.items = []
def updateFields(self):
fields = field.Fields(ITestForm)
connections = []
for name, url in self.saconnect.items():
connections.append(SimpleTerm(url, url, name))
fields['connection'].field.vocabulary = SimpleVocabulary(connections)
if len(connections) == 1:
if self.Base == None:
self.initBase(connections[0].value)
self.tablesVoc = self.getTables()
fields['table'].field.vocabulary = SimpleVocabulary.fromValues(
self.tablesVoc)
if len(self.tablesVoc) == 1:
self.columnsVoc = self.getColumns(self.tablesVoc[0])
fields[
'columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues(
self.columnsVoc)
fields['columns'].field.default = fields[
'columns'].field.values
self.fields = fields
def update(self):
self.updateFields()
self.updateWidgets()
data, errors = self.extractData()
table = data.get('table')
if data.get('connection'):
if self.Base == None:
self.initBase(data.get('connection'))
if not self.tablesVoc:
self.tablesVoc = self.getTables()
self.fields[
'table'].field.vocabulary = SimpleVocabulary.fromValues(
self.tablesVoc)
if len(self.tablesVoc) == 1 and table == None:
table = self.tablesVoc[0]
if table:
if not self.columnsVoc:
self.columnsVoc = self.getColumns(table)
self.fields[
'columns'].field.value_type.vocabulary = SimpleVocabulary.fromValues(
self.columnsVoc)
# self.fields['columns'].field.value_type.default = self.columnsVoc
super(TestView, self).update()
def initBase(self, url):
engine = create_engine(url, echo=True)
self.Base = automap_base(bind=engine)
def getTables(self):
conn = self.Base.metadata.bind.connect()
return self.Base.metadata.bind.engine.table_names([], connection=conn)
def getColumns(self, table):
self.Base.metadata.reflect(only=[table])
return [a.name for a in self.Base.metadata.tables[table].c]
def getItems(self, table, columns):
self.Base.prepare(self.Base.metadata.bind)
Session = scoped_session(
sessionmaker(bind=self.Base.metadata.bind,
extension=ZopeTransactionExtension()))
session = Session()
item = getattr(self.Base.classes, table, None)
return session.query(item).all()
@button.buttonAndHandler(_('Apply'), name='apply')
def handleApply(self, action):
data, errors = self.extractData()
if errors:
self.status = self.formErrorsMessage
return
if data.get('table') and data.get('columns'):
self.table = data.get('table')
self.columns = data.get('columns')
self.items = self.getItems(self.table, self.columns)
# limit size of the preview for text areas
if hasattr(widget, 'rows'):
if widget.rows is None or widget.rows > 5:
widget.rows = 5
return form.Form.render(self)
class SQLTypeFieldsPage(TypeFieldsPage):
@property
def tabs(self):
tabs = super(SQLTypeFieldsPage, self).tabs
tabs += ((_('Data'), '@@data'),)
return tabs
@property
def form(self):
if self.context.fti.hasDynamicSchema:
return SQLEnhancedSchemaListing
else:
return ReadOnlySchemaListing
TupleFactory = FieldFactory(
schema.Tuple, _(u'label_tuple_field', default=u'Tuple'))
class ITuple(schema.interfaces.ITuple,
schema.interfaces.IFromUnicode):
pass
def tabs(self):
tabs = super(SQLTypeFormLayout, self).tabs
tabs += ((_('Data'), '@@data'), )
return tabs