def _update_col(column, table, data_type, columns): """ Update the column based on the database operation. :param column: Base column. :type column: BaseColumn :param columns: Existing column names in the database for the given table. :type columns: list :returns: SQLAlchemy column object. :rtype: Column """ from stdm.data.configuration.columns import BoundsColumn alchemy_column = Column(column.name, data_type, **_base_col_attrs(column)) idx_name = None if column.index: idx_name = u'idx_{0}_{1}'.format(column.entity.name, column.name) unique_name = None if column.unique: unique_name = u'unq_{0}_{1}'.format(column.entity.name, column.name) if column.action == DbItem.CREATE: # Ensure the column does not exist otherwise an exception will be thrown if not column.name in columns: alchemy_column.create(table=table, index_name=idx_name, unique_name=unique_name) # Create check constraints accordingly if isinstance(column, BoundsColumn) and \ column.can_create_check_constraints(): # Create check constraint if need be chk_const = check_constraint(column, alchemy_column, table) if not chk_const is None: chk_const.create() elif column.action == DbItem.ALTER: # Ensure the column exists before altering if column.name in columns: col_attrs = _base_col_attrs(column) col_attrs['table'] = table alchemy_column.alter(**col_attrs) elif column.action == DbItem.DROP: # Ensure the column exists before dropping if column.name in columns: _clear_ref_in_entity_relations(column) # Use drop cascade command drop_cascade_column(column.entity.name, column.name) #alchemy_column.drop(table=table) # Ensure column is added to the table if alchemy_column.table is None: alchemy_column._set_parent(table) return alchemy_column
def _update_col(column, table, data_type, columns): """ Update the column based on the database operation. :param column: Base column. :type column: BaseColumn :returns: SQLAlchemy column object. :rtype: Column :param columns: Existing column names in the database for the given table. :type columns: list """ alchemy_column = Column(column.name, data_type, **_base_col_attrs(column)) idx_name = None if column.index: idx_name = u'idx_{0}_{1}'.format(column.entity.name, column.name) unique_name = None if column.unique: unique_name = u'unq_{0}_{1}'.format(column.entity.name, column.name) if column.action == DbItem.CREATE: #Ensure the column does not exist otherwise an exception will be thrown if not column.name in columns: alchemy_column.create(table=table, index_name=idx_name, unique_name=unique_name) elif column.action == DbItem.ALTER: #Ensure the column exists before altering if column.name in columns: col_attrs = _base_col_attrs(column) col_attrs['table'] = table alchemy_column.alter(**col_attrs) elif column.action == DbItem.DROP: #Ensure the column exists before dropping if column.name in columns: _clear_ref_in_entity_relations(column) alchemy_column.drop(table=table) #Ensure column is added to the table if alchemy_column.table is None: alchemy_column._set_parent(table) return alchemy_column
def _update_col(column, table, data_type, columns): """ Update the column based on the database operation. :param column: Base column. :type column: BaseColumn :param columns: Existing column names in the database for the given table. :type columns: list :returns: SQLAlchemy column object. :rtype: Column """ from stdm.data.configuration.columns import BoundsColumn alchemy_column = Column(column.name, data_type, **_base_col_attrs(column)) idx_name = None if column.index: idx_name = u'idx_{0}_{1}'.format(column.entity.name, column.name) unique_name = None if column.unique: unique_name = u'unq_{0}_{1}'.format(column.entity.name, column.name) if column.action == DbItem.CREATE: # Ensure the column does not exist otherwise an exception will be thrown if not column.name in columns: alchemy_column.create(table=table, unique_name=unique_name) # Create check constraints accordingly if isinstance(column, BoundsColumn) and \ column.can_create_check_constraints(): # Create check constraint if need be chk_const = check_constraint(column, alchemy_column, table) if not chk_const is None: chk_const.create() elif column.action == DbItem.ALTER: # Ensure the column exists before altering if column.name in columns: col_attrs = _base_col_attrs(column) col_attrs['table'] = table alchemy_column.alter(**col_attrs) elif column.action == DbItem.DROP: # Ensure the column exists before dropping if column.name in columns: _clear_ref_in_entity_relations(column) # Use drop cascade command drop_cascade_column(column.entity.name, column.name) # Ensure column is added to the table if alchemy_column.table is None: alchemy_column._set_parent(table) # add different type of index for columns with index if column.index: _bind_metadata(metadata) inspector = reflection.Inspector.from_engine(metadata.bind) indexes_list = inspector.get_indexes(column.entity.name) indexes = [i['name'] for i in indexes_list if not i['unique']] # get_indexes do not get gist indexes so try/ except needs to be used. try: if idx_name not in indexes: if column.TYPE_INFO == 'GEOMETRY': idx = Index(idx_name, alchemy_column, postgresql_using='gist') idx.create() else: idx = Index(idx_name, alchemy_column, postgresql_using='btree') idx.create() except Exception: pass return alchemy_column
def _update_col(column, table, data_type, columns): """ Update the column based on the database operation. :param column: Base column. :type column: BaseColumn :param columns: Existing column names in the database for the given table. :type columns: list :returns: SQLAlchemy column object. :rtype: Column """ from stdm.data.configuration.columns import BoundsColumn alchemy_column = Column(column.name, data_type, **_base_col_attrs(column)) idx_name = None if column.index: idx_name = u'idx_{0}_{1}'.format(column.entity.name, column.name) unique_name = None if column.unique: unique_name = u'unq_{0}_{1}'.format(column.entity.name, column.name) if column.action == DbItem.CREATE: # Ensure the column does not exist otherwise an exception will be thrown if not column.name in columns: alchemy_column.create( table=table, unique_name=unique_name ) # Create check constraints accordingly if isinstance(column, BoundsColumn) and \ column.can_create_check_constraints(): # Create check constraint if need be chk_const = check_constraint( column, alchemy_column, table ) if not chk_const is None: chk_const.create() elif column.action == DbItem.ALTER: # Ensure the column exists before altering if column.name in columns: col_attrs = _base_col_attrs(column) col_attrs['table'] = table alchemy_column.alter(**col_attrs) elif column.action == DbItem.DROP: # Ensure the column exists before dropping if column.name in columns: _clear_ref_in_entity_relations(column) # Use drop cascade command drop_cascade_column(column.entity.name, column.name) # Ensure column is added to the table if alchemy_column.table is None: alchemy_column._set_parent(table) # add different type of index for columns with index if column.index: _bind_metadata(metadata) inspector = reflection.Inspector.from_engine(metadata.bind) indexes_list = inspector.get_indexes(column.entity.name) indexes = [i['name'] for i in indexes_list if not i['unique']] # get_indexes do not get gist indexes so try/ except needs to be used. try: if idx_name not in indexes: if column.TYPE_INFO == 'GEOMETRY': idx = Index(idx_name, alchemy_column, postgresql_using='gist') idx.create() else: idx = Index(idx_name, alchemy_column, postgresql_using='btree') idx.create() except Exception: pass return alchemy_column