def db_upgrade_002_003(): engine = anchore_engine.db.entities.common.get_engine() try: table_name = 'images' column = Column('size', BigInteger) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ALTER COLUMN %s TYPE %s' % (table_name, cn, ct)) except Exception as e: raise Exception( 'failed to perform DB upgrade on images.size field change from int to bigint - exception: {}' .format(str(e))) try: table_name = 'feed_data_gem_packages' column = Column('id', BigInteger) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ALTER COLUMN %s TYPE %s' % (table_name, cn, ct)) except Exception as e: raise Exception( 'failed to perform DB upgrade on feed_data_gem_packages.id field change from int to bigint - exception: {}' .format(str(e))) return True
def db_upgrade_001_002(): global engine from anchore_engine.db import db_anchore, db_users, db_registries, db_policybundle, db_catalog_image try: table_name = 'registries' column = Column('registry_type', String, primary_key=False) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s' % (table_name, cn, ct)) with session_scope() as dbsession: registry_records = db_registries.get_all(session=dbsession) for registry_record in registry_records: try: if not registry_record['registry_type']: registry_record['registry_type'] = 'docker_v2' db_registries.update_record(registry_record, session=dbsession) except Exception as err: pass except Exception as err: raise Exception( "failed to perform DB registry table upgrade - exception: " + str(err)) try: table_name = 'policy_bundle' column = Column('policy_source', String, primary_key=False) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s' % (table_name, cn, ct)) with session_scope() as dbsession: policy_records = db_policybundle.get_all(session=dbsession) for policy_record in policy_records: try: if not policy_record['policy_source']: policy_record['policy_source'] = 'local' db_policybundle.update_record(policy_record, session=dbsession) except Exception as err: pass except Exception as err: raise Exception( "failed to perform DB policy_bundle table upgrade - exception: " + str(err)) return (True)
def alter_topic_data_table(topic): topic_dict: dict = convert_to_dict(topic) if topic_dict.get("type") == "raw": pass else: topic_name = topic_dict.get('name') table_name = 'topic_' + topic_name ''' table = Table(table_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table = get_topic_table_by_name(table_name) factors = topic_dict.get('factors') existed_cols = [] for col in table.columns: existed_cols.append(col.name) for factor in factors: factor_name = factor.get('name').lower() factor_type = get_datatype_by_factor_type(factor.get('type')) if factor_name in existed_cols: continue else: column = Column(factor_name, factor_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) stmt = 'ALTER TABLE %s ADD %s %s' % (table_name, column_name, column_type) with engine.connect() as conn: conn.execute(text(stmt))
def add_index(engine: Engine, table_name: str, column: sa.Column): """Create an index based on the column index definition calling the compiled SQL statement: CREATE INDEX index_name ON table_name (column_name) :param engine: the bound sql database engine :param table_name: the name of the table with the column :param column: the instantiated column definition :return: - nothing - """ c_table_name = _compile_name(table_name, dialect=engine.dialect) c_column_name = column.compile(dialect=engine.dialect) index_name = "ix_%s_%s" % (table_name, column.name) c_index_name = _compile_name(index_name, dialect=engine.dialect) engine.execute('CREATE INDEX %s ON %s ( %s )' % (c_index_name, c_table_name, c_column_name))
def _add_column( self, connection: Connection, table: SqlaTable, column_name: str, column_type: str, ) -> None: """ Add new column to table :param connection: The connection to work with :param table: The SqlaTable :param column_name: The name of the column :param column_type: The type of the column """ column = Column(column_name, column_type) name = column.compile(column_name, dialect=table.database.get_sqla_engine().dialect) col_type = column.type.compile( table.database.get_sqla_engine().dialect) sql = text( f"ALTER TABLE {self._get_from_clause(table)} ADD {name} {col_type}" ) connection.execute(sql) table.columns.append( TableColumn(column_name=column_name, type=col_type))
def test_citext_compare_compare_w_literal_bind() -> None: comparison = Column(CITEXT, name="field") == "NON_EXISTING" # type: ignore compiled_comparison = comparison.compile( dialect=postgresql.dialect(), # type: ignore compile_kwargs={"literal_binds": True}, ) assert str(compiled_comparison) == "field = 'NON_EXISTING'"
def create_column(cls, table_object, field_name, field_type): db_session = DBSession() engine = db_session.get_bind() column = Column(field_name, field_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) engine.execute('ALTER TABLE "%s"."%s" ADD COLUMN %s %s' % (table_object.schema or 'public', table_object.name, column_name, column_type))
def __change_column_datatype(table_uid, field_uid, new_column_type): db_session = DBSession() engine = db_session.get_bind() column = Column('fld_%s' % field_uid, new_column_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) engine.execute('ALTER TABLE "vector_layer"."layer_%s" ALTER COLUMN %s TYPE %s' % (table_uid, column_name, column_type))
def __drop_column(table_uid, field_uid): # еще не юзал! db_session = DBSession() engine = db_session.get_bind() column = Column('fld_%s' % field_uid) column_name = column.compile(dialect=engine.dialect) engine.execute('ALTER TABLE "vector_layer"."layer_%s" DROP COLUMN %s' % (table_uid, column_name))
def add_column(self, engine, table_name, column: Column, dry_run=True): column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) column_nullable = "NULL" if column.nullable else "NOT NULL" query_to_execute = f'ALTER TABLE \"{table_name}\" ADD COLUMN {column_name} {column_type} {column_nullable}' if dry_run: logging.getLogger().info( f"Query to execute in DB: {query_to_execute}") else: engine.execute(query_to_execute)
def add_column(column_name, database_engine, tablename='core_dataset'): column = Column(column_name, Float, primary_key=True) column_name = column.compile(dialect=database_engine.dialect) column_type = column.type.compile(database_engine.dialect) database_engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % (tablename, column_name, column_type)) # Refresh the table details held in metadata by reflecting from database Table(tablename, Base.metadata, autoload=True, autoload_with=server.database_engine, keep_existing=False, extend_existing=True)
def do_upgrade(inplace, incode): global engine if StrictVersion(inplace['db_version']) > StrictVersion(incode['db_version']): raise Exception("DB downgrade not supported") if inplace['db_version'] != incode['db_version']: print ("upgrading DB: from=" + str(inplace['db_version']) + " to=" + str(incode['db_version'])) if True: # set up possible upgrade chain db_upgrade_map = [ ('0.0.1', '0.0.2') ] db_current = inplace['db_version'] db_target = incode['db_version'] for db_from, db_to in db_upgrade_map: # finish if we've reached the target version if StrictVersion(db_current) >= StrictVersion(db_target): # done break # this is just example code for now - have a clause for each possible from->to in the upgrade chain if db_current == '0.0.1' and db_to == '0.0.2': print ("upgrade from 0.0.1 to 0.0.2") try: rc = db_upgrade_001_002() except Exception as err: raise err elif db_current == '0.0.2' and db_to == '0.0.3': print ("upgrade from 0.0.2 to 0.0.3") try: table_name = 'subscriptions' column = Column('foobar', String, primary_key=False) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s DROP COLUMN IF EXISTS %s' % (table_name, cn)) except Exception as err: raise err db_current = db_to if inplace['service_version'] != incode['service_version']: print ("upgrading service: from=" + str(inplace['service_version']) + " to=" + str(incode['service_version'])) ret = True return (ret)
def drop_column(engine: Engine, table_name: str, column: sa.Column): """ calling the compiled SQL statement ALTER TABLE table_name drop COLUMN column :param engine: the bound sql database engine :param table_name: the name of the table with the column :param column: the instantiated column defintion :return: - nothing - """ c_table_name = _compile_name(table_name, dialect=engine.dialect) c_column_name = column.compile(dialect=engine.dialect) engine.execute('ALTER TABLE %s drop COLUMN %s ' % (c_table_name, c_column_name))
def db_upgrade_001_002(): global engine table_name = 'catalog_image_docker' column = Column('created_at', Integer, primary_key=False) cn = column.compile(dialect=engine.dialect) ct = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s' % (table_name, cn, ct)) import db.db_users, db.db_catalog_image_docker with session_scope() as dbsession: all_users = db.db_users.get_all(session=dbsession) for user in all_users: userId = user['userId'] all_records = db.db_catalog_image_docker.get_all(userId, session=dbsession) for record in all_records: if not record['created_at']: record['created_at'] = time.time() db.db_catalog_image_docker.update_record(record, session=dbsession) return (True)
def _UpdInstanceAddColumn(self, col_dict, table_name = None, schema_name = None): '''Two use-cases: self can be a base_object or a table_obj''' if schema_name: self.set_cur_schema(schema_name) if 'BaseInstance' in str(self.__class__): loc_engine_obj = self.engine if not table_name: loc_table_name = self._cur_table.__tablename__ else: loc_table_name = table_name else: loc_table_name = self.__tablename__ loc_engine_obj = self._engine_link_ if not schema_name: loc_schema_name = self.get_schema()['schema_name'] col_type = col_dict['col_type'] if type(col_type) is str: if not _UpdInstanceTypeMapping(loc_engine_obj, col_type): print('TYPE DOES NOT FOUND %s' % col_type) return None col_type = _UpdInstanceTypeMapping(loc_engine_obj, col_type) column_obj = Column(col_dict['col_name'], col_type, nullable=True) qs ='ALTER TABLE {}."{}" ADD COLUMN {} {};' qs = qs.format(self._cur_schema.schema, #One shall not add a quotes around column name loc_table_name, column_obj.compile(dialect=loc_engine_obj.dialect), column_obj.type.compile(loc_engine_obj.dialect)) try: loc_engine_obj.execute(qs) except SQLAlchemyError as se: #TODO: log there pass else: self._cur_schema.reflect(only=[loc_table_name], extend_existing=True)
def add_column(engine: Engine, table_name: str, column: sa.Column): """Create an index based on the column index definition. calling the compiled SQL statement: ALTER TABLE table_name ADD COLUMN column_name column_type :param engine: the bound sql database engine :param table_name: the name of the table with the column :param column: the instantiated column defintion :return: - nothing - """ c_table_name = _compile_name(table_name, dialect=engine.dialect) c_column_name = column.compile(dialect=engine.dialect) c_column_type = column.type.compile(engine.dialect) engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % (c_table_name, c_column_name, c_column_type))
def createTables(self): championship = Table( 'championship', self.meta, Column('id_championship', Integer, primary_key=True, autoincrement=True), Column('name', String(100)), Column('start_date', DateTime), Column('award_money', Integer), Column('award', String(100))) player = Table( 'player', self.meta, Column('id_player', Integer, primary_key=True, autoincrement=True), Column('name', String(60)), Column('nick', String(60)), Column('age', Integer)) team = Table( 'team', self.meta, Column('id_team', Integer, primary_key=True, autoincrement=True), Column('name', String(50)), Column('homepage', String(100)), Column('actual_rank', Integer), Column('hltv_id', Integer)) game = Table( 'game', self.meta, Column('id_game', Integer, primary_key=True, autoincrement=True), Column('id_championship', Integer), Column('id_team1', Integer), Column('id_team2', Integer), Column('id_winner_team', Integer), Column('id_predic_winner', Integer), Column('date', DateTime), Column('team1_score', Integer), Column('team2_score', Integer), Column('best_of', Integer), Column('team1_picks_maps', String(100)), Column('team2_picks_maps', String(100)), Column('team1_removed_maps', String(100)), Column('team2_removed_maps', String(100)), Column('team1_rank', Integer), Column('team2_rank', Integer)) map = Table( 'map', self.meta, Column('id_map_game', Integer, primary_key=True, autoincrement=True), Column('id_game', Integer), Column('map_name', String(20)), Column('team1_tr_rounds', Integer), Column('team2_tr_rounds', Integer), Column('team1_ct_rounds', Integer), Column('team2_ct_rounds', Integer), Column('overtime_team1_rounds', Integer), Column('overtime_team2_rounds', Integer), Column('team1_total_rounds', Integer), Column('team2_total_rounds', Integer)) player_map_statistic = Table( 'player_map_statistic', self.meta, Column('id_player_map_statistic', Integer, primary_key=True, autoincrement=True), Column('id_team_player', Integer), Column('id_map_game', Integer), Column('id_player', Integer), Column('kills', Integer), Column('deaths', Integer), Column('plus_minos', Integer), Column('adr', Float(precision=2)), Column('kast', Float(precision=2)), Column('rating2', Float(precision=2))) player_team_property = Table( 'player_team_property', self.meta, Column('id_player_team_property', Integer, primary_key=True, autoincrement=True), Column('id_player', Integer), Column('id_team', Integer), Column('date_hire', DateTime), Column('date_fire', DateTime), Column('active', Boolean)) self.meta.create_all(self.engine) column = Column('map_left', String(100)) column_name = column.compile(dialect=self.engine.dialect) column_type = column.type.compile(self.engine.dialect) self.engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % ('game', column_name, column_type))
def add_field(self, collection, name, field_type, description, visibility, origin, unit, default_value, index=False, flush=True): """Add a field to the database, if it does not already exist :param collection: field collection (str) :param name: field name (str) :param field_type: field type (string, int, float, boolean, date, datetime, time, list_string, list_int, list_float, list_boolean, list_date, list_datetime, or list_time) :param description: field description (str or None) :param visibility: Bool to know if the field is visible in the databrowser :param origin: To know the origin of a field, in [TAG_ORIGIN_BUILTIN, TAG_ORIGIN_USER] :param unit: Origin of the field, in [TAG_UNIT_MS, TAG_UNIT_MM, TAG_UNIT_DEGREE, TAG_UNIT_HZPIXEL, TAG_UNIT_MHZ] :param default_value: Default_value of the field, can be str or None :param flush: bool to know if the table classes must be updated (put False if in the middle of filling fields) => True by default """ # Checks collection_row = self.get_collection(collection) if collection_row is None: raise ValueError("The collection " + str(collection) + " does not exist") field_row = self.get_field(collection, name) if field_row is not None: raise ValueError("A field with the name " + str(name) + " already exists in the collection " + collection) if not isinstance(name, str): raise ValueError("The field name must be of type " + str(str) + ", but field name of type " + str(type(name)) + " given") if field_type not in ALL_TYPES: raise ValueError("The field type must be in " + str(ALL_TYPES) + ", but " + str(field_type) + " given") if not isinstance(description, str) and description is not None: raise ValueError("The field description must be of type " + str(str) + " or None, but field description of type " + str(type(description)) + " given") # Adding the field in the field table field_row = self.table_classes[FIELD_TABLE]( field_name=name, collection_name=collection, type=field_type, description=description, visibility=visibility, origin=origin, unit=unit, default_value=default_value) if self._DatabaseSession__caches: self._DatabaseSession__fields[collection][name] = field_row self.session.add(field_row) # Fields creation if field_type in LIST_TYPES: if self.list_tables: table = 'list_%s_%s' % (self.name_to_valid_column_name( collection), self.name_to_valid_column_name(name)) list_table = Table( table, self.metadata, Column('document_id', String, primary_key=True), Column('i', Integer, primary_key=True), Column('value', TYPE_TO_COLUMN[field_type[5:]])) list_query = CreateTable(list_table) self.session.execute(list_query) # Creating the class associated collection_dict = { '__tablename__': table, '__table__': list_table } collection_class = type(table, (self.base, ), collection_dict) mapper(collection_class, list_table) self.table_classes[table] = collection_class # String columns if it list type, as the str representation of the # lists will be stored field_type = String else: field_type = self._DatabaseSession__field_type_to_column_type( field_type) column = Column(self.name_to_valid_column_name(name), field_type, index=index) column_str_type = column.type.compile(self.database.engine.dialect) column_name = column.compile(dialect=self.database.engine.dialect) # Column created in document table, and in initial table if initial # values are used document_query = str('ALTER TABLE "%s" ADD COLUMN %s %s' % (self.name_to_valid_column_name(collection), column_name, column_str_type)) self.session.execute(document_query) self.table_classes[self.name_to_valid_column_name( collection)].__table__.append_column(column) # Redefinition of the table classes if flush: self.session.flush() # Classes reloaded in order to add the new column attribute self._DatabaseSession__update_table_classes() if self._DatabaseSession__caches: self._DatabaseSession__refresh_cache_documents(collection) self.unsaved_modifications = True
def add_column(engine, table_name, column: Column): column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) engine.execute( "ALTER TABLE %s ADD COLUMN %s %s" % (table_name, column_name, column_type) )
def add_column(self): column = Column('content_text', String) column_name = column.compile(dialect=self.engine.dialect) column_type = column.type.compile(self.engine.dialect) self.engine.execute('ALTER TABLE %s ADD COLUMN %s %s' % ("pages", column_name, column_type))
def _add_column(self, table: Any, column: Column) -> None: column_name = column.compile(dialect=self.db_engine.dialect) column_type = column.type.compile(self.db_engine.dialect) self.db_engine.execute("ALTER TABLE {} ADD COLUMN {} {}".format( table.__tablename__, column_name, column_type))