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))
