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)