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 = []
class SQLAlchemyConnectionsForm(crud.CrudForm):
label = _(u'label_saconnectform', u'Manage SQLAlchemy connection strings')
update_schema = IConnectionLine
def __init__(self, context, request):
crud.CrudForm.__init__(self, context, request)
self.storage = ISQLAlchemyConnectionStrings(
getUtility(ISiteRoot)
)
def get_items(self):
names = self.storage.keys()
names.sort()
return [(name, ConnectionLine(name, self.storage[name], self))
for name in names]
def add(self, data):
name = data['connname'].strip()
if str(name) in self.storage:
msg = _(u'error_name_already_registered',
u'The connection name is already registered')
msg = translate(msg, self.request)
raise schema.ValidationError(msg)
self.storage[name] = data['connstring'].strip()
return ConnectionLine(name, data['connstring'].strip(), self)
def remove(self, (id, item)):
del self.storage[item.connname]
def __call__(self, context):
# we take the fti type settings to get the selected connections and table
if ISQLTypeSchemaContext.providedBy(context):
context = ISQLTypeSettings(context.fti)
elif IField.providedBy(context):
context = ISQLTypeSettings(aq_parent(context.context).fti)
urls = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot)).values()
if not getattr(context, 'sql_connection', None) or not getattr(
context, 'sql_table', None):
return SimpleVocabulary([])
columns = []
if not ISQLTypeSettings.providedBy(context):
context = ISQLTypeSettings(context)
sql_url = context.sql_url
sql_table = context.sql_table
engine = create_engine(sql_url)
insp = reflection.Inspector.from_engine(engine)
columns = insp.get_columns(sql_table)
columns.sort(lambda x, y: cmp(x.get('name'), y.get('name')))
items = [
SimpleTerm(a.get('name'), a.get('name'),
a.get('name') + ' (' + str(a.get('type')) + ')')
for a in columns
if a.get('name') and 'TIMESTAMP' in str(a.get('type'))
]
return SimpleVocabulary(items)
class SQLAlchemyConnectionsForm(crud.CrudForm):
label = _(u'label_saconnectform', u'Manage SQLAlchemy connection strings')
update_schema = IConnectionLine
def __init__(self, context, request):
crud.CrudForm.__init__(self, context, request)
self.storage = ISQLAlchemyConnectionStrings(getUtility(ISiteRoot))
def get_items(self):
names = self.storage.keys()
names.sort()
return [(name, ConnectionLine(name, self.storage[name], self))
for name in names]
def add(self, data):
name = data['connname'].strip()
if str(name) in self.storage:
msg = _(u'error_name_already_registered',
u'The connection name is already registered')
msg = translate(msg, self.request)
raise schema.ValidationError(msg)
self.storage[name] = data['connstring'].strip()
return ConnectionLine(name, data['connstring'].strip(), self)
def remove(self, (id, item)):
del self.storage[item.connname]
def __call__(self, context):
# we take the fti type settings to get the selected connections and table
if ISQLTypeSchemaContext.providedBy(context):
context = ISQLTypeSettings(context.fti)
elif IField.providedBy(context):
context = ISQLTypeSettings(aq_parent(context.context).fti)
urls = ISQLAlchemyConnectionStrings(component.getUtility(ISiteRoot)).values()
if not getattr(context, 'sql_table', None):
return SimpleVocabulary([])
items = []
connection = queryUtility(ISQLConnectionsUtility, name=context.id, default=None)
if not connection:
connection = registerConnectionUtilityForFTI(context.context)
columns = []
for a in inspect(connection.tableClass).columns:
if a.name:
items.append(SimpleTerm(a.name, a.name, a.name+' ('+str(a.type)+')'))
columns.append(a.name)
for a in getattr(inspect(connection.tableClass), 'relationships', []):
if a.key in columns:
continue
items.append(SimpleTerm(a.key, a.key, a.key+' (Relation)'))
for b in inspect(a.table).columns:
if b.name:
items.append(SimpleTerm(a.key+'.'+b.name, a.key+'.'+b.name, a.key+'.'+b.name+' ('+str(b.type)+')'))
columns.append(a.key+'.'+b.name)
# for b in getattr(inspect(connection.tableClass), 'relationships', []):
# if a.key+'.'+b.key in columns:
# continue
# items.append(SimpleTerm(a.key+'.'+b.key, a.key+'.'+b.key, a.key+'.'+b.key+' (Relation)'))
items.sort( lambda x, y: cmp(x.value, y.value ) )
return SimpleVocabulary(items)
def __call__(self, context):
# we take the fti type settings to get the selected connections and table
if ISQLTypeSchemaContext.providedBy(context):
context = ISQLTypeSettings(context.fti)
elif IField.providedBy(context):
context = ISQLTypeSettings(aq_parent(context.context).fti)
urls = ISQLAlchemyConnectionStrings(component.getUtility(ISiteRoot)).values()
if not getattr(context, 'sql_table', None):
return SimpleVocabulary([])
columns = []
sql_connection = context.sql_connection
sql_table = context.sql_table
engine = create_engine(sql_connection)
insp = reflection.Inspector.from_engine(engine)
base_columns = insp.get_columns(sql_table)
columns = set()
for constraint in insp.get_unique_constraints(sql_table):
if len(constraint.get('column_names')) == 1:
columns.update(constraint.get('column_names')[0])
columns = columns.union(set(insp.get_pk_constraint(sql_table).get('constrained_columns')))
columns = list(columns)
final = []
for col in base_columns:
if col['name'] not in columns:
continue
final.append(col.copy())
final.sort( lambda x, y: cmp(x.get('name'), y.get('name') ) )
items = [SimpleTerm(a.get('name'), a.get('name'), a.get('name')+' ('+str(a.get('type'))+')') for a in final if a.get('name')]
return SimpleVocabulary(items)
def exportConnections(context):
storage = ISQLAlchemyConnectionStrings(context.getSite())
exporter = component.queryMultiAdapter((storage, context), IBody)
if exporter:
data = exporter.body
if data is not None:
context.writeDataFile(FILENAME, data, exporter.mime_type)
def __call__(self, context):
if not getattr(context, 'sql_connection', None):
return SimpleVocabulary([])
urls = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot)).values()
if not urls:
return SimpleVocabulary([])
tables = []
if ISQLTypeSchemaContext.providedBy(context):
context = ISQLTypeSettings(context.fti)
if not ISQLTypeSettings.providedBy(context):
context = ISQLTypeSettings(context)
sql_url = context.sql_url and context.sql_url or urls[0]
engine = create_engine(sql_url)
# Base = declarative_base(bind=engine)
insp = reflection.Inspector.from_engine(engine)
tables = insp.get_table_names()
views = insp.get_view_names()
# conn = Base.metadata.bind.contextual_connect(close_with_result=True)
# table_names = Base.metadata.bind.engine.table_names([], connection=conn)
items = [
SimpleTerm(name, name, name + ' (table)')
for name in sorted(list(set(tables))) if name
]
items += [
SimpleTerm(name, name, name + ' (view)')
for name in sorted(list(set(views)))
if name and name not in list(tables)
]
return SimpleVocabulary(items)
def configuration(self):
urltool = getToolByName(getSite(), 'portal_url')
portal = urltool.getPortalObject()
try:
saconnect = ISQLAlchemyConnectionStrings(portal)
dsn = saconnect['alchemyform']
except (ComponentLookupError, KeyError), e:
dsn = DEFAULT_DSN
def importConnections(context):
data = context.readDataFile(FILENAME)
if not data:
return
storage = ISQLAlchemyConnectionStrings(context.getSite())
importer = component.queryMultiAdapter((storage, context), IBody)
if importer:
importer.body = data
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 configuration(self):
urltool = getToolByName(getSite(), 'portal_url')
portal = urltool.getPortalObject()
try:
saconnect = ISQLAlchemyConnectionStrings(portal)
dsn = saconnect['spdo']
logger(u"Utilizando configuração local: " + unicode(dsn, 'utf-8'))
except (ComponentLookupError, KeyError), e:
dsn = DEFAULT_DSN
logger(u"Utilizando configuração padrão: " + unicode(dsn, 'utf-8'))
def __call__(self, context):
# we take the fti type settings to get the selected connections and table
# on adding, guess the portal_type
request = getattr(context, 'REQUEST', None)
request_url = hasattr(request, 'get') and request.get('URL') or ''
if '++add++' in request_url:
portal_type = context.REQUEST.get('URL').split('++add++')[1]
fti = queryUtility(IDexterityFTI, name=portal_type, default=None)
if fti:
context = ISQLTypeSettings(fti)
elif ISQLTypeSchemaContext.providedBy(context):
context = ISQLTypeSettings(context.fti)
elif ISQLDexterityItem.providedBy(context):
fti = queryUtility(IDexterityFTI,
name=context.portal_type,
default=None)
if fti:
context = ISQLTypeSettings(fti)
elif IField.providedBy(context):
context = ISQLTypeSettings(aq_parent(context.context).fti)
urls = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot)).values()
if not urls or not getattr(
context, 'sql_connection', None) or not getattr(
context, 'sql_table', None) or not getattr(
context, 'sql_id_column', None):
return SimpleVocabulary([])
ids = []
sql_table = context.sql_table
sql_id_column = context.sql_id_column
s = text('SELECT ' + sql_id_column + ' FROM ' + sql_table)
connection = queryUtility(ISQLConnectionsUtility,
name=context.id,
default=None)
if not connection:
connection = registerConnectionUtilityForFTI(context.context)
try:
res = connection.conn.execute(s).fetchall()
except:
connection.reinit()
res = connection.conn.execute(s).fetchall()
items = []
for a in res:
title = a[0]
try:
title = str(a[0])
except:
try:
title = str(a[0].encode('utf-8'))
except:
title = str(a[0].decode('utf-8'))
items.append(SimpleTerm(title, title, title))
return SimpleVocabulary(items)
def fields(self):
if 'collective.behavior.sql.behavior.behaviors.ISQLContent' not in self.context.fti.behaviors:
return super(SQLTypeOverviewForm, self).fields
# if this type's class is not a container,
# remove the field for filtering contained content types
klass = resolveDottedName(self.context.fti.klass)
fields = field.Fields(ISQLTypeSettings)
fti_adapted = ISQLTypeSettings(self.context.fti)
to_omit = []
if not fti_adapted.sql_connection:
to_omit = [
'sql_table', 'sql_id_column', 'sql_WHERE',
'sql_modification_timestamp_column',
'sql_modification_last_timestamp', 'sql_folder_id'
]
fields = fields.omit('sql_table', 'sql_id_column', 'sql_WHERE',
'sql_modification_timestamp_column',
'sql_modification_last_timestamp',
'sql_folder_id')
elif not fti_adapted.sql_table:
to_omit = [
'sql_id_column', 'sql_WHERE',
'sql_modification_timestamp_column',
'sql_modification_last_timestamp', 'sql_folder_id'
]
fields = fields.omit('sql_id_column', 'sql_WHERE',
'sql_modification_timestamp_column',
'sql_modification_last_timestamp',
'sql_folder_id')
else:
engine = create_engine(fti_adapted.sql_connection)
insp = reflection.Inspector.from_engine(engine)
tables = insp.get_table_names()
views = insp.get_view_names()
if fti_adapted.sql_table in views and fti_adapted.sql_table not in tables:
fields['sql_id_column'].field.vocabulary = None
fields[
'sql_id_column'].field.vocabularyName = "collective.behavior.sql.AvailableSQLAlchemyColumns"
names = [a for a in ISQLTypeSettings.names() if a not in to_omit]
filtered = fields.select('title', 'description',
'allowed_content_types',
'filter_content_types', *names)
if not IFolderish.implementedBy(klass):
del filtered['filter_content_types']
urls = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot)).values()
if len(urls) == 1:
filtered['sql_connection'].field.default = urls[0]
return filtered
def __init__(self, context, request):
crud.CrudForm.__init__(self, context, request)
self.storage = ISQLAlchemyConnectionStrings(
getUtility(ISiteRoot)
)
def sql_url(self):
saStorage = ISQLAlchemyConnectionStrings(getUtility(ISiteRoot))
if '://' in self.sql_connection:
return self.sql_connection
return saStorage.get(self.sql_connection)
def __call__(self, context):
saconnect = ISQLAlchemyConnectionStrings(component.getUtility(ISiteRoot))
items = [SimpleTerm(url, url, name) for name, url in saconnect.items()]
return SimpleVocabulary(items)
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)
def configuration(self):
siteroot = getUtility(ISiteRoot)
connections = ISQLAlchemyConnectionStrings(siteroot)
url = connections[self.name]
return [url], dict(convert_unicode=True)
def __call__(self, context):
saconnect = ISQLAlchemyConnectionStrings(
component.getUtility(ISiteRoot))
items = [SimpleTerm(name, name, name) for name in saconnect.keys()]
return SimpleVocabulary(items)
def __init__(self, context, request):
crud.CrudForm.__init__(self, context, request)
self.storage = ISQLAlchemyConnectionStrings(getUtility(ISiteRoot))
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)
