def do_mining(self):
"""Do the parallelized mining."""
self.logger.info(
f"Starting mining with {self.workers_per_model} workers per model."
)
# Flags to let the workers know there won't be any new tasks.
can_finish: Dict[str, mp.synchronize.Event] = {
etype: mp.Event()
for etype in self.ee_models_paths
}
# Prepare the task queues for the workers - one task queue per model.
task_queues: Dict[str, mp.Queue] = {
etype: mp.Queue()
for etype in self.ee_models_paths
}
# Spawn the workers according to `workers_per_model`.
self.logger.info("Spawning the worker processes")
worker_processes = []
workers_by_queue: Dict[str, List[mp.Process]] = {
queue_name: []
for queue_name in task_queues
}
for etype, model_path in self.ee_models_paths.items():
for i in range(self.workers_per_model):
worker_name = f"{etype}_{i}"
worker_process = mp.Process(
name=worker_name,
target=Miner.create_and_mine,
kwargs={
"database_url": self.engine.url,
"model_path": model_path,
"target_table": self.target_table,
"task_queue": task_queues[etype],
"can_finish": can_finish[etype],
},
)
worker_process.start()
worker_processes.append(worker_process)
workers_by_queue[etype].append(worker_process)
# Create tasks
self.logger.info("Creating tasks")
self.create_tasks(task_queues, workers_by_queue)
# Monitor the queues and the workers to decide when we're finished.
# For a given model the work is finished when the corresponding queue
# is empty. But it can be that all workers stop/crash before all
# tasks are done. Therefore we need to check if anyone is still
# working on a given queue, and if not empty we will empty it.
while not all(flag.is_set() for flag in can_finish.values()):
for queue_name, task_queue in task_queues.items():
if can_finish[queue_name].is_set():
# This queue is already empty we've let the workers know
continue
if not any(worker.is_alive()
for worker in workers_by_queue[queue_name]):
self.logger.debug(f"Emptying the {queue_name} queue")
while not task_queue.empty():
article_id = task_queue.get(timeout=1)
self.logger.debug(f"Got non-done task {article_id}")
if task_queue.empty():
self.logger.debug(
f"Setting the can finish flag for the {queue_name} queue."
)
can_finish[queue_name].set()
self.logger.info("Closing all task queues")
for queue_name, task_queue in task_queues.items():
self.logger.debug(
f"Closing the reading end of the queue {queue_name}")
# Note that this is only safe when the queue is empty. This is
# because there's a background thread putting buffered data
# in the queue. If the queue is not empty it might be that the
# background thread is still transferring the data from the
# buffer. Closing the reading end of the internal pipe actually
# also closes the writing end, and therefore the background
# thread will throw a BrokenPipeError as it will fail to write
# to the closed pipe.
task_queue.close()
self.logger.debug(
f"Joining the buffering thread of queue {queue_name}")
task_queue.join_thread()
# Wait for the processes to finish.
self.logger.info(
"No more new tasks, just waiting for the workers to finish")
# We'll transfer finished workers from `worker_processes`
# to `finished_workers`. We're done when `worker_processes` is empty.
finished_workers: List[mp.Process] = []
while len(worker_processes) > 0:
self.logger.debug(
f"Status: {len(worker_processes)} workers still alive, "
f"{len(finished_workers)} finished.")
# Loop through all living workers and try to join
for process in worker_processes:
# Don't need to wait forever - others might finish before
process.join(timeout=1.0)
# If the current process did finish then put it in the
# `finished_workers` queue and do some cleaning up.
if not process.is_alive():
self.logger.info(f"Worker {process.name} finished.")
finished_workers.append(process)
if process.exitcode != 0:
self.logger.error(
f"Worker {process.name} terminated with exit "
f"code {process.exitcode}!")
# Remove all workers that are already in the `finished_workers`
# list from the `worker_processes` list.
for process in finished_workers:
if process in worker_processes:
worker_processes.remove(process)
self.logger.info("Finished mining.")
# Create index on (article_id, paragraph_pos_in_article, start_char)
# to speed up ORDER BY clause.
self.logger.info("Start creating index on (par, art, char)...")
sqlalchemy.Index(
"index_art_par_char",
self.mining_cache_table.c.article_id,
self.mining_cache_table.c.paragraph_pos_in_article,
self.mining_cache_table.c.start_char,
).create(bind=self.engine)
self.logger.info("Done creating index on (par, art, char).")
def build_metadata():
"""Build schema def with SqlAlchemy"""
metadata = sa.MetaData()
sa.Table('hive_blocks',
metadata,
sa.Column('num',
sa.Integer,
primary_key=True,
autoincrement=False),
sa.Column('hash', CHAR(40), nullable=False),
sa.Column('prev', CHAR(40)),
sa.Column('txs', SMALLINT, server_default='0', nullable=False),
sa.Column('ops', SMALLINT, server_default='0', nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.UniqueConstraint('hash', name='hive_blocks_ux1'),
sa.ForeignKeyConstraint(['prev'], ['hive_blocks.hash'],
name='hive_blocks_fk1'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_accounts',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('name', VARCHAR(16), nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
#sa.Column('block_num', sa.Integer, nullable=False),
sa.Column('reputation',
sa.Float(precision=6),
nullable=False,
server_default='25'),
sa.Column('display_name', sa.String(20)),
sa.Column('about', sa.String(160)),
sa.Column('location', sa.String(30)),
sa.Column('website', sa.String(100)),
sa.Column('profile_image',
sa.String(1024),
nullable=False,
server_default=''),
sa.Column('cover_image',
sa.String(1024),
nullable=False,
server_default=''),
sa.Column('followers', sa.Integer, nullable=False, server_default='0'),
sa.Column('following', sa.Integer, nullable=False, server_default='0'),
sa.Column('proxy', VARCHAR(16), nullable=False, server_default=''),
sa.Column('post_count', sa.Integer, nullable=False,
server_default='0'),
sa.Column('proxy_weight',
sa.Float(precision=6),
nullable=False,
server_default='0'),
sa.Column('vote_weight',
sa.Float(precision=6),
nullable=False,
server_default='0'),
sa.Column('kb_used', sa.Integer, nullable=False, server_default='0'),
sa.Column('rank', sa.Integer, nullable=False, server_default='0'),
sa.Column('active_at',
sa.DateTime,
nullable=False,
server_default='1970-01-01 00:00:00'),
sa.Column('cached_at',
sa.DateTime,
nullable=False,
server_default='1970-01-01 00:00:00'),
sa.Column('raw_json', sa.Text),
sa.UniqueConstraint('name', name='hive_accounts_ux1'),
sa.Index('hive_accounts_ix1', 'vote_weight',
'id'), # core: quick ranks
sa.Index('hive_accounts_ix2', 'name', 'id'), # core: quick id map
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_posts',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('parent_id', sa.Integer),
sa.Column('author', VARCHAR(16), nullable=False),
sa.Column('permlink', VARCHAR(255), nullable=False),
sa.Column('community', VARCHAR(16), nullable=False),
sa.Column('category', VARCHAR(255), nullable=False, server_default=''),
sa.Column('depth', SMALLINT, nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.Column('is_deleted', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_pinned', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_muted', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_valid', BOOLEAN, nullable=False, server_default='1'),
sa.Column('promoted',
sa.types.DECIMAL(10, 3),
nullable=False,
server_default='0'),
sa.ForeignKeyConstraint(['author'], ['hive_accounts.name'],
name='hive_posts_fk1'),
sa.ForeignKeyConstraint(['community'], ['hive_accounts.name'],
name='hive_posts_fk2'),
sa.ForeignKeyConstraint(['parent_id'], ['hive_posts.id'],
name='hive_posts_fk3'),
sa.UniqueConstraint('author', 'permlink', name='hive_posts_ux1'),
sa.Index('hive_posts_ix1', 'parent_id'), # API
sa.Index('hive_posts_ix2', 'is_deleted', 'depth'), # API
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
#sa.Table(
# 'hive_tags', metadata,
# sa.Column('id', sa.Integer, primary_key=True),
# sa.Column('name', CHAR(64), nullable=False),
# sa.UniqueConstraint('name', name='hive_tags_ux1'),
# mysql_engine='InnoDB',
# mysql_default_charset='utf8mb4'
#)
sa.Table(
'hive_post_tags',
metadata,
sa.Column('post_id', sa.Integer, nullable=False),
sa.Column('tag', sa.String(32), nullable=False),
sa.UniqueConstraint('tag', 'post_id',
name='hive_post_tags_ux1'), # core
sa.Index('hive_post_tags_ix1', 'post_id'), # core
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_follows',
metadata,
sa.Column('follower', sa.Integer, nullable=False),
sa.Column('following', sa.Integer, nullable=False),
sa.Column('state', SMALLINT, nullable=False, server_default='1'),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.UniqueConstraint('following', 'follower',
name='hive_follows_ux3'), # core
sa.Index('hive_follows_ix2',
'following',
'follower',
postgresql_where=sql_text("state = 1")), # API
sa.Index('hive_follows_ix3',
'follower',
'following',
postgresql_where=sql_text("state = 1")), # API
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_reblogs',
metadata,
sa.Column('account', VARCHAR(16), nullable=False),
sa.Column('post_id', sa.Integer, nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'],
name='hive_reblogs_fk1'),
sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'],
name='hive_reblogs_fk2'),
sa.UniqueConstraint('account', 'post_id',
name='hive_reblogs_ux1'), # core
sa.Index('hive_reblogs_ix1', 'post_id', 'account',
'created_at'), # API -- TODO: seemingly unused
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_payments',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('block_num', sa.Integer, nullable=False),
sa.Column('tx_idx', SMALLINT, nullable=False),
sa.Column('post_id', sa.Integer, nullable=False),
sa.Column('from_account', sa.Integer, nullable=False),
sa.Column('to_account', sa.Integer, nullable=False),
sa.Column('amount', sa.types.DECIMAL(10, 3), nullable=False),
sa.Column('token', VARCHAR(5), nullable=False),
sa.ForeignKeyConstraint(['from_account'], ['hive_accounts.id'],
name='hive_payments_fk1'),
sa.ForeignKeyConstraint(['to_account'], ['hive_accounts.id'],
name='hive_payments_fk2'),
sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'],
name='hive_payments_fk3'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_communities',
metadata,
sa.Column('name', VARCHAR(16), primary_key=True),
sa.Column('title', sa.String(32), nullable=False),
sa.Column('about',
sa.String(255),
nullable=False,
server_default=''),
sa.Column('description',
sa.String(5000),
nullable=False,
server_default=''),
sa.Column('lang', CHAR(2), nullable=False, server_default='en'),
sa.Column('settings', TEXT, nullable=False),
sa.Column('type_id', SMALLINT, nullable=False,
server_default='0'),
sa.Column('is_nsfw', BOOLEAN, nullable=False, server_default='0'),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.ForeignKeyConstraint(['name'], ['hive_accounts.name'],
name='hive_communities_fk1'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_members',
metadata,
sa.Column('community', VARCHAR(16), nullable=False),
sa.Column('account', VARCHAR(16), nullable=False),
sa.Column('is_admin', BOOLEAN, nullable=False),
sa.Column('is_mod', BOOLEAN, nullable=False),
sa.Column('is_approved', BOOLEAN, nullable=False),
sa.Column('is_muted', BOOLEAN, nullable=False),
sa.Column('title',
sa.String(255),
nullable=False,
server_default=''),
sa.ForeignKeyConstraint(['community'], ['hive_communities.name'],
name='hive_members_fk1'),
sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'],
name='hive_members_fk2'),
sa.UniqueConstraint('community',
'account',
name='hive_members_ux1'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_flags',
metadata,
sa.Column('account', VARCHAR(16), nullable=False),
sa.Column('post_id', sa.Integer, nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.Column('notes', sa.String(255), nullable=False),
sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'],
name='hive_flags_fk1'),
sa.ForeignKeyConstraint(['post_id'], ['hive_posts.id'],
name='hive_flags_fk2'),
sa.UniqueConstraint('account', 'post_id', name='hive_flags_ux1'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_modlog',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('community', VARCHAR(16), nullable=False),
sa.Column('account', VARCHAR(16), nullable=False),
sa.Column('action', sa.String(32), nullable=False),
sa.Column('params', sa.String(1000), nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.ForeignKeyConstraint(['community'], ['hive_communities.name'],
name='hive_modlog_fk1'),
sa.ForeignKeyConstraint(['account'], ['hive_accounts.name'],
name='hive_modlog_fk2'),
sa.Index('hive_modlog_ix1', 'community', 'created_at'),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_feed_cache',
metadata,
sa.Column('post_id', sa.Integer, nullable=False),
sa.Column('account_id', sa.Integer, nullable=False),
sa.Column('created_at', sa.DateTime, nullable=False),
sa.UniqueConstraint('post_id',
'account_id',
name='hive_feed_cache_ux1'), # core
sa.Index('hive_feed_cache_ix1', 'account_id', 'post_id',
'created_at'), # API (and rebuild?)
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table(
'hive_posts_cache',
metadata,
sa.Column('post_id', sa.Integer, primary_key=True),
sa.Column('author', VARCHAR(16), nullable=False),
sa.Column('permlink', VARCHAR(255), nullable=False),
sa.Column('category', VARCHAR(255), nullable=False, server_default=''),
# important/index
sa.Column('depth', SMALLINT, nullable=False, server_default='0'),
sa.Column('children', SMALLINT, nullable=False, server_default='0'),
# basic/extended-stats
sa.Column('author_rep',
sa.Float(precision=6),
nullable=False,
server_default='0'),
sa.Column('flag_weight',
sa.Float(precision=6),
nullable=False,
server_default='0'),
sa.Column('total_votes',
sa.Integer,
nullable=False,
server_default='0'),
sa.Column('up_votes', sa.Integer, nullable=False, server_default='0'),
# basic ui fields
sa.Column('title', sa.String(255), nullable=False, server_default=''),
sa.Column('preview',
sa.String(1024),
nullable=False,
server_default=''),
sa.Column('img_url',
sa.String(1024),
nullable=False,
server_default=''),
# core stats/indexes
sa.Column('payout',
sa.types.DECIMAL(10, 3),
nullable=False,
server_default='0'),
sa.Column('promoted',
sa.types.DECIMAL(10, 3),
nullable=False,
server_default='0'),
sa.Column('created_at',
sa.DateTime,
nullable=False,
server_default='1990-01-01'),
sa.Column('payout_at',
sa.DateTime,
nullable=False,
server_default='1990-01-01'),
sa.Column('updated_at',
sa.DateTime,
nullable=False,
server_default='1990-01-01'),
sa.Column('is_paidout', BOOLEAN, nullable=False, server_default='0'),
# ui flags/filters
sa.Column('is_nsfw', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_declined', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_full_power', BOOLEAN, nullable=False,
server_default='0'),
sa.Column('is_hidden', BOOLEAN, nullable=False, server_default='0'),
sa.Column('is_grayed', BOOLEAN, nullable=False, server_default='0'),
# important indexes
sa.Column('rshares', sa.BigInteger, nullable=False,
server_default='0'),
sa.Column('sc_trend',
sa.Float(precision=6),
nullable=False,
server_default='0'),
sa.Column('sc_hot',
sa.Float(precision=6),
nullable=False,
server_default='0'),
# bulk data
sa.Column('body', TEXT),
sa.Column('votes', TEXT),
sa.Column('json', sa.Text),
sa.Column('raw_json', sa.Text),
sa.Index('hive_posts_cache_ix2',
'promoted',
postgresql_where=sql_text(
"is_paidout = '0' AND promoted > 0")), # API
sa.Index('hive_posts_cache_ix3',
'payout_at',
'post_id',
postgresql_where=sql_text("is_paidout = '0'")), # core
sa.Index('hive_posts_cache_ix6', 'sc_trend', 'post_id'), # API
sa.Index('hive_posts_cache_ix7', 'sc_hot', 'post_id'), # API
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
sa.Table('hive_state',
metadata,
sa.Column('block_num',
sa.Integer,
primary_key=True,
autoincrement=False),
sa.Column('db_version', sa.Integer, nullable=False),
sa.Column('dpay_per_mvest',
sa.types.DECIMAL(8, 3),
nullable=False),
sa.Column('usd_per_dpay', sa.types.DECIMAL(8, 3), nullable=False),
sa.Column('bbd_per_dpay', sa.types.DECIMAL(8, 3), nullable=False),
sa.Column('dgpo', sa.Text, nullable=False),
mysql_engine='InnoDB',
mysql_default_charset='utf8mb4')
return metadata
make = sa.Column(sa.Unicode(255), nullable=False)
model = sa.Column(sa.Unicode(255), nullable=False)
@transaction
def add(cls, make, model):
o = cls(make=make, model=model)
db.sess.add(o)
return o
@ignore_unique
def add_iu(cls, make, model):
cls.add(make, model)
sa.Index('uidx_sabwp_truck_makemodel', Truck.make, Truck.model, unique=True)
class CustomerType(Base, LookupMixin):
__tablename__ = 'sabwp_customer_types'
class HasUniqueValidation(Base, DefaultMixin):
__tablename__ = 'sabwp_has_unique_val'
name = sa.Column(sa.String(255), nullable=False, unique=True)
email = sa.Column(sa.String(255), nullable=False, unique=True)
validates_unique('name', 'email')
ActionExecution.task_execution_id = sa.Column(sa.String(36),
sa.ForeignKey(
TaskExecution.id,
ondelete='CASCADE'),
nullable=True)
TaskExecution.action_executions = relationship(
ActionExecution,
backref=backref('task_execution', remote_side=[TaskExecution.id]),
cascade='all, delete-orphan',
foreign_keys=ActionExecution.task_execution_id,
lazy='select',
passive_deletes=True)
sa.Index('%s_task_execution_id' % ActionExecution.__tablename__,
'task_execution_id')
# Many-to-one for 'WorkflowExecution' and 'TaskExecution'.
WorkflowExecution.task_execution_id = sa.Column(sa.String(36),
sa.ForeignKey(
TaskExecution.id,
ondelete='CASCADE'),
nullable=True)
TaskExecution.workflow_executions = relationship(
WorkflowExecution,
backref=backref('task_execution', remote_side=[TaskExecution.id]),
cascade='all, delete-orphan',
foreign_keys=WorkflowExecution.task_execution_id,
lazy='select',
else:
return today.year - self.birthday.year - 1
class CompetitorWeight(Base):
__tablename__ = 'competitor_weight'
id = sa.Column(sa.Integer, primary_key=True)
competitor_id = sa.Column(sa.Integer,
sa.ForeignKey('competitor.id'),
index=True)
weightDate = sa.Column(sa.Date, nullable=False)
weight = sa.Column(sa.Float, nullable=False)
sa.Index("competitor_weight_idx00",
CompetitorWeight.competitor_id,
CompetitorWeight.weightDate,
unique=True)
class Tournament(Base):
__tablename__ = 'tournament'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(200), nullable=False)
start_date = sa.Column(sa.Date)
end_date = sa.Column(sa.Date)
competitors = orm.relationship(
'TournamentCompetitor',
cascade="all, delete-orphan",
order_by="TournamentCompetitor.competitor_id",
lazy="joined",
)
def upgrade():
table_name = 'export'
audit_name = table_name + '_audit'
for name in (table_name, audit_name):
op.create_table(
name,
sa.Column('id',
sa.Integer,
primary_key=True,
autoincrement=True,
nullable=False),
sa.Column('name', sa.Unicode, nullable=False),
sa.Column('owner_user_id', sa.Integer, nullable=False),
sa.Column('expand_collections', sa.Boolean, nullable=False),
sa.Column('use_choice_labels', sa.Boolean, nullable=False),
sa.Column('notify', sa.Boolean, nullable=False),
sa.Column('status',
sa.Enum('failed',
'pending',
'complete',
name='export_status'),
nullable=False,
default='pending'),
sa.Column('contents', JSON, nullable=False),
sa.Column('create_user_id', sa.Integer, nullable=False),
sa.Column('create_date',
sa.DateTime,
nullable=False,
server_default=sql.func.now()),
sa.Column('modify_user_id', sa.Integer, nullable=False),
sa.Column('modify_date',
sa.DateTime,
nullable=False,
server_default=sql.func.now()),
sa.Column('revision',
sa.Integer,
primary_key=('audit' in name),
nullable=False),
sa.Index('ix_%s_create_user_id' % name, 'create_user_id'),
sa.Index('ix_%s_modify_user_id' % name, 'modify_user_id'),
# Both main/audit tables keep the same check constraint names
sa.CheckConstraint('create_date <= modify_date',
name='ck_%s_valid_timeline' % table_name))
# The live table will have some extra data integrity constraints
op.create_unique_constraint('uq_%s_name' % table_name, 'export', ['name'])
op.create_index('ix_%s_owner_user_id' % table_name, table_name,
['owner_user_id'])
op.create_foreign_key('fk_%s_owner_user_id' % table_name,
table_name,
'user', ['create_user_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('fk_%s_create_user_id' % table_name,
table_name,
'user', ['create_user_id'], ['id'],
ondelete='RESTRICT')
op.create_foreign_key('fk_%s_modify_user_id' % table_name,
table_name,
'user', ['modify_user_id'], ['id'],
ondelete='RESTRICT')
def downgrade(migrate_engine):
meta = sa.MetaData(bind=migrate_engine)
user = sa.Table(
'user',
meta,
sa.Column('id', sa.String(255), primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
project = sa.Table(
'project',
meta,
sa.Column('id', sa.String(255), primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8',
)
tables = [project, user]
for i in sorted(tables):
i.create()
load_tables = dict((table_name, sa.Table(table_name, meta, autoload=True))
for table_name in TABLES)
# Restore the sourceassoc columns and constraints
sourceassoc = load_tables['sourceassoc']
user_id = sa.Column('user_id', sa.String(255))
project_id = sa.Column('project_id', sa.String(255))
sourceassoc.create_column(user_id)
sourceassoc.create_column(project_id)
if migrate_engine.name != 'sqlite':
params = {}
if migrate_engine.name == "mysql":
params = {'name': 'uniq_sourceassoc0sample_id0user_id'}
uc = UniqueConstraint('sample_id',
'user_id',
table=sourceassoc,
**params)
uc.create()
params = {}
if migrate_engine.name == "mysql":
params = {'name': 'uniq_sourceassoc0sample_id'}
uc = UniqueConstraint('sample_id', table=sourceassoc, **params)
uc.drop()
idx = sa.Index('idx_su', sourceassoc.c.source_id,
sourceassoc.c.user_id)
idx.create(bind=migrate_engine)
idx = sa.Index('idx_sp', sourceassoc.c.source_id,
sourceassoc.c.project_id)
idx.create(bind=migrate_engine)
# Restore the user/project columns and constraints in all tables
for table_name, indexes in INDEXES.items():
table = load_tables[table_name]
for column, ref_table_name, ref_column_name in indexes:
ref_table = load_tables[ref_table_name]
c = getattr(Alias(table).c, column)
except_q = exists([getattr(ref_table.c, ref_column_name)])
q = select([c]).where(and_(c != sa.null(), not_(except_q)))
q = q.distinct()
# NOTE(sileht): workaround for
# https://bitbucket.org/zzzeek/sqlalchemy/
# issue/3044/insert-from-select-union_all
q.select = lambda: q
sql_ins = ref_table.insert().from_select(
[getattr(ref_table.c, ref_column_name)], q)
try:
migrate_engine.execute(sql_ins)
except TypeError:
# from select is empty
pass
if migrate_engine.name != 'sqlite':
params = {
'columns': [table.c[column]],
'refcolumns': [ref_table.c[ref_column_name]]
}
if (migrate_engine.name == "mysql"
and table_name != 'alarm_history'):
params['name'] = "_".join(('fk', table_name, column))
elif (migrate_engine.name == "postgresql"
and table_name == "sample"):
# The fk contains the old table name
params['name'] = "_".join(('meter', column, 'fkey'))
fkey = ForeignKeyConstraint(**params)
fkey.create()
class Questions(SqlAlchemyBase):
__tablename__ = 'questions'
id = sqlalchemy.Column(sqlalchemy.Integer,
primary_key=True,
autoincrement=True)
inner_id = sqlalchemy.Column(sqlalchemy.Integer)
text = sqlalchemy.Column(sqlalchemy.UnicodeText)
indexed_text = sqlalchemy.Column(sqlalchemy.UnicodeText)
subject_id = sqlalchemy.Column(sqlalchemy.Integer,
sqlalchemy.ForeignKey("subjects.id"),
nullable=False)
subject = orm.relation('Subjects', backref='questions')
type_id = sqlalchemy.Column(sqlalchemy.Integer,
sqlalchemy.ForeignKey("type_question.id"),
nullable=False,
default=1)
type = orm.relation('TypeQuestion', backref='questions')
ordered = sqlalchemy.Column(sqlalchemy.Boolean,
nullable=False,
default=False)
completed = sqlalchemy.Column(sqlalchemy.Boolean,
nullable=False,
default=False)
text_index = sqlalchemy.Index('text_index', indexed_text)
def __init__(self,
text,
subject_id,
type_id=1,
ordered=False,
completed=False,
inner_id=None):
self.text = text.strip()
self.indexed_text = get_prepare_text(text)
self.subject_id = subject_id
self.type_id = type_id
self.ordered = ordered
self.completed = completed
self.inner_id = inner_id
@property
def answers(self):
return [
x for x in self.distractors if x.question == self and x.correct
]
@property
def name(self):
p = re.compile(r'<.*?>')
text = p.sub('', self.text)
max_len = 50
if len(text) <= max_len:
return text
text = text[:max_len]
if ' ' in text:
text = text[:text.rindex(' ')]
return text + ' ...'
def __str__(self):
return self.name
def __repr__(self):
return f'<Questions {self.id} "{self.name}">'
def upgrade(migrate_engine):
metadata = sa.MetaData()
metadata.bind = migrate_engine
sautils.Table(
'builds', metadata,
sa.Column('id', sa.Integer, primary_key=True),
# ...
)
sautils.Table(
'builders', metadata,
sa.Column('id', sa.Integer, primary_key=True),
# ...
)
sautils.Table(
'steps', metadata,
sa.Column('id', sa.Integer, primary_key=True),
# ...
)
test_result_sets = sautils.Table(
'test_result_sets', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('builderid', sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('buildid', sa.Integer,
sa.ForeignKey('builds.id', ondelete='CASCADE'),
nullable=False),
sa.Column('stepid', sa.Integer,
sa.ForeignKey('steps.id', ondelete='CASCADE'),
nullable=False),
sa.Column('description', sa.Text, nullable=True),
sa.Column('category', sa.Text, nullable=False),
sa.Column('value_unit', sa.Text, nullable=False),
sa.Column('tests_passed', sa.Integer, nullable=True),
sa.Column('tests_failed', sa.Integer, nullable=True),
sa.Column('complete', sa.SmallInteger, nullable=False),
)
test_results = sautils.Table(
'test_results', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('builderid', sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('test_result_setid', sa.Integer,
sa.ForeignKey('test_result_sets.id', ondelete='CASCADE'),
nullable=False),
sa.Column('test_nameid', sa.Integer,
sa.ForeignKey('test_names.id', ondelete='CASCADE'),
nullable=True),
sa.Column('test_code_pathid', sa.Integer,
sa.ForeignKey('test_code_paths.id', ondelete='CASCADE'),
nullable=True),
sa.Column('line', sa.Integer, nullable=True),
sa.Column('duration_ns', sa.Integer, nullable=True),
sa.Column('value', sa.Text, nullable=False),
)
test_names = sautils.Table(
'test_names', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('builderid', sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('name', sa.Text, nullable=False),
)
test_code_paths = sautils.Table(
'test_code_paths', metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('builderid', sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('path', sa.Text, nullable=False),
)
# create the tables
test_result_sets.create()
test_names.create()
test_code_paths.create()
test_results.create()
# create indexes
idx = sa.Index('test_names_name', test_names.c.builderid, test_names.c.name,
mysql_length={'name': 255})
idx.create()
idx = sa.Index('test_code_paths_path', test_code_paths.c.builderid, test_code_paths.c.path,
mysql_length={'path': 255})
idx.create()
sa.Column('name', sa.String(255), unique=True),
sa.Column('group', sa.String(255)),
sa.Column('groups', postgresql.ARRAY(sa.String(255))),
sa.Column('version', sa.Integer, nullable=True),
sa.Column('comment', sa.Text),
sa.Column('created_at',
AwareDateTime,
default=lambda: datetime.datetime.utcnow(),
nullable=True),
sa.Column(
'updated_at',
AwareDateTime,
default=lambda: datetime.datetime.utcnow(),
onupdate=lambda: datetime.datetime.utcnow(),
nullable=True,
),
)
sa.Index('idx_name_group_is_active', switches.c.name, switches.c.group,
switches.c.is_active)
sa.Index('idx_name_group_version_is_active', switches.c.name, switches.c.group,
switches.c.version, switches.c.is_active)
user_switches = sa.Table(
'user_switches',
metadata,
sa.Column('user_id', sa.Integer, sa.ForeignKey(models.users.c.id)),
sa.Column('switch_id', sa.Integer, sa.ForeignKey(switches.c.id)),
sa.UniqueConstraint('switch_id', 'user_id', name='user_switch_unique'),
)
def upgrade():
op.create_table(
'applications',
sa.Column('id',
UUID(),
primary_key=True,
server_default=sa.text("gen_random_uuid()")),
sa.Column('name', sa.String(255), nullable=False),
sa.Column('description', sa.Unicode(), nullable=True),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), onupdate=func.now()))
op.create_table(
'endpoints',
sa.Column('id',
UUID(),
primary_key=True,
server_default=sa.text("gen_random_uuid()")),
sa.Column('account_id', sa.String(50), nullable=False, index=True),
sa.Column('endpoint_type', sa.Integer(), nullable=False),
sa.Column('enabled', sa.Boolean(), nullable=False, default=False),
sa.Column('name', sa.String(255), nullable=False),
sa.Column('description', sa.Unicode(), nullable=True),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('updated', sa.DateTime(timezone=True), onupdate=func.now()))
# Could use JSON field in the endpoints table also for properties..
op.create_table(
'endpoint_webhooks',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('endpoint_id', UUID(), nullable=False, index=True),
sa.Column('url', sa.Unicode(), nullable=False),
sa.Column('method', sa.String(10),
nullable=False), # We could use enum numbering
sa.Column('disable_ssl_verification',
sa.Boolean(),
nullable=False,
default=False),
sa.Column('secret_token', sa.String(255), nullable=True),
sa.Column('payload_transformer', sa.String(50), nullable=True))
op.create_table(
'endpoint_email_subscriptions',
sa.Column('account_id',
sa.String(50),
nullable=False,
primary_key=True),
sa.Column('user_id', sa.String(50), nullable=False, primary_key=True),
sa.Column('event_type',
sa.String(50),
nullable=False,
primary_key=True)
# sa.UniqueConstraint('account_id', 'user_id', 'event_type', name='unique_subscription_IX')
)
op.create_table(
'email_aggregation',
sa.Column('id', sa.Integer(), primary_key=True),
sa.Column('account_id', sa.String(50), nullable=False),
sa.Column('insight_id', sa.String(50), nullable=False),
sa.Column('created',
sa.DateTime(timezone=True),
server_default=func.now(),
nullable=False),
sa.Column('payload', JSONB, nullable=False),
# Needs to be an index of account_id + created as that's the search query
sa.Index('IX_time_search_account_mails', 'account_id', 'created'))
def rel(table: str,
populates: Optional[str] = None,
secondary: Optional[sa.Table] = None):
"""Simplifies building a relation between tables"""
kw = dict()
if populates:
kw['back_populates'] = populates
if secondary is not None:
kw['secondary'] = secondary
return orm.relationship(table, **kw)
@alchemy(sa.UniqueConstraint('email', 'name', 'type'),
sa.Index('idx_name', 'name'))
class Address(Base):
"""Represents an email address"""
__tablename__ = "address"
id = sa.Column(sa.Integer, primary_key=True)
type = sa.Column(sa.Integer, nullable=True)
email = sa.Column(sa.String(320), nullable=True)
name = sa.Column(sa.String(320), nullable=True)
messages = rel('Email', 'contacts', _email_contact)
# pylint: disable=dangerous-default-value
@classmethod
def fromdict(cls, email_address, cache=dict()):
"""Build an Address from a OML Address dict"""
name = email_address.get('@OPFContactEmailAddressName', None)
def _create_tables_thd(self, conn):
metadata = sa.MetaData()
metadata.bind = conn
self._define_old_tables(metadata)
metadata.create_all()
sa.Index('builds_buildrequestid',
self.builds.c.buildrequestid).create()
sa.Index('builds_number',
self.builds.c.builderid,
self.builds.c.number,
unique=True).create()
sa.Index('builds_buildslaveid', self.builds.c.buildslaveid).create()
sa.Index('builds_masterid', self.builds.c.masterid).create()
sa.Index('buildslaves_name', self.buildslaves.c.name,
unique=True).create()
sa.Index('configured_slaves_buildmasterid',
self.configured_buildslaves.c.buildermasterid).create()
sa.Index('configured_slaves_slaves',
self.configured_buildslaves.c.buildslaveid).create()
sa.Index('configured_slaves_identity',
self.configured_buildslaves.c.buildermasterid,
self.configured_buildslaves.c.buildslaveid,
unique=True).create()
sa.Index('connected_slaves_masterid',
self.connected_buildslaves.c.masterid).create()
sa.Index('connected_slaves_slaves',
self.connected_buildslaves.c.buildslaveid).create()
sa.Index('connected_slaves_identity',
self.connected_buildslaves.c.masterid,
self.connected_buildslaves.c.buildslaveid,
unique=True).create()
def setup_thd(conn):
metadata = sa.MetaData()
metadata.bind = conn
builders = sa.Table(
'builders',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('name', sa.Text, nullable=False),
sa.Column('name_hash', sa.String(40), nullable=False),
)
builders.create()
buildsets = sa.Table(
'buildsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('external_idstring', sa.String(256)),
sa.Column('reason', sa.String(256)),
sa.Column('submitted_at', sa.Integer, nullable=False),
sa.Column('complete',
sa.SmallInteger,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('complete_at', sa.Integer),
sa.Column('results', sa.SmallInteger),
sa.Column('parent_buildid', sa.Integer),
sa.Column('parent_relationship', sa.Text),
)
buildsets.create()
buildrequests = sa.Table(
'buildrequests',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey("buildsets.id"),
nullable=False),
sa.Column('buildername', sa.String(length=256),
nullable=False),
sa.Column('priority',
sa.Integer,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('complete',
sa.Integer,
server_default=sa.DefaultClause("0")),
sa.Column('results', sa.SmallInteger),
sa.Column('submitted_at', sa.Integer, nullable=False),
sa.Column('complete_at', sa.Integer),
sa.Column('waited_for',
sa.SmallInteger,
server_default=sa.DefaultClause("0")),
)
buildrequests.create()
idx = sa.Index('buildrequests_buildsetid',
buildrequests.c.buildsetid)
idx.create()
idx = sa.Index('buildrequests_buildername',
buildrequests.c.buildername)
idx.create()
idx = sa.Index('buildrequests_complete', buildrequests.c.complete)
idx.create()
idx = sa.Index('buildsets_complete', buildsets.c.complete)
idx.create()
idx = sa.Index('buildsets_submitted_at', buildsets.c.submitted_at)
idx.create()
brargs = dict(buildsetid=10, priority=1, submitted_at=1234)
conn.execute(buildsets.insert(), id=10, submitted_at=1233)
conn.execute(builders.insert(),
id=20,
name='bldr1',
name_hash='88103b2fbeb05bdd81c066b58a11bcf9b0d29300')
conn.execute(buildrequests.insert(),
id=30,
buildername='bldr1',
**brargs)
conn.execute(buildrequests.insert(),
id=31,
buildername='bldr1',
**brargs)
conn.execute(buildrequests.insert(),
id=32,
buildername='bldr2',
**brargs)
self.assertTrue(hasattr(buildrequests.c, 'buildername'))
self.assertFalse(hasattr(buildrequests.c, 'builderid'))
class Workload(BASE, RallyBase):
__tablename__ = "workloads"
__table_args__ = (sa.Index("workload_uuid", "uuid", unique=True), )
id = sa.Column(sa.Integer, primary_key=True, autoincrement=True)
uuid = sa.Column(sa.String(36), default=UUID, nullable=False)
task_uuid = sa.Column(
sa.String(36),
sa.ForeignKey(Task.uuid),
nullable=False,
)
subtask_uuid = sa.Column(
sa.String(36),
sa.ForeignKey(Subtask.uuid),
nullable=False,
)
subtask = sa.orm.relationship(
Subtask,
backref=sa.orm.backref("workloads"),
foreign_keys=subtask_uuid,
primaryjoin=(subtask_uuid == Subtask.uuid),
)
name = sa.Column(sa.String(64), nullable=False)
description = sa.Column(sa.Text, default="")
position = sa.Column(sa.Integer, default=0, nullable=False)
runner = sa.Column(sa_types.JSONEncodedDict, default={}, nullable=False)
runner_type = sa.Column(sa.String(64), nullable=False)
contexts = sa.Column(sa_types.JSONEncodedDict, default={}, nullable=False)
contexts_results = sa.Column(sa_types.MutableJSONEncodedList,
default=[],
nullable=False)
sla = sa.Column(sa_types.JSONEncodedDict, default={}, nullable=False)
sla_results = sa.Column(sa_types.MutableJSONEncodedDict,
default={},
nullable=False)
args = sa.Column(sa_types.JSONEncodedDict, default={}, nullable=False)
hooks = sa.Column(sa_types.JSONEncodedList, default=[], nullable=False)
start_time = sa.Column(sa_types.TimeStamp)
load_duration = sa.Column(sa.Float, default=0.0)
full_duration = sa.Column(sa.Float, default=0.0)
min_duration = sa.Column(sa.Float)
max_duration = sa.Column(sa.Float)
total_iteration_count = sa.Column(sa.Integer, default=0)
failed_iteration_count = sa.Column(sa.Integer, default=0)
statistics = sa.Column(sa_types.MutableJSONEncodedDict,
default={},
nullable=False)
pass_sla = sa.Column(sa.Boolean, default=True)
_profiling_data = sa.orm.deferred(sa.Column(sa.Text, default=""))
foreign_keys=[filter_id],
back_populates="candidates",
doc="The filter that the Candidate passed",
)
passed_at = sa.Column(
sa.DateTime,
nullable=False,
index=True,
doc="ISO UTC time when the Candidate passed the Filter.",
)
passing_alert_id = sa.Column(
sa.BigInteger,
index=True,
doc="ID of the latest Stream alert that passed the Filter.",
)
uploader_id = sa.Column(
sa.ForeignKey("users.id", ondelete="CASCADE"),
nullable=False,
index=True,
doc="ID of the user that posted the candidate",
)
Candidate.__table_args__ = (sa.Index(
"candidates_main_index",
Candidate.obj_id,
Candidate.filter_id,
Candidate.passed_at,
unique=True,
), )
ScheduleStorageSqlMetadata = db.MetaData()
JobTable = db.Table(
"jobs",
ScheduleStorageSqlMetadata,
db.Column("id", db.Integer, primary_key=True, autoincrement=True),
db.Column("job_origin_id", db.String(255), unique=True),
db.Column("repository_origin_id", db.String(255)),
db.Column("status", db.String(63)),
db.Column("job_type", db.String(63), index=True),
db.Column("job_body", db.Text),
db.Column("create_timestamp", db.DateTime, server_default=get_current_timestamp()),
db.Column("update_timestamp", db.DateTime, server_default=get_current_timestamp()),
)
JobTickTable = db.Table(
"job_ticks",
ScheduleStorageSqlMetadata,
db.Column("id", db.Integer, primary_key=True, autoincrement=True),
db.Column("job_origin_id", db.String(255), index=True),
db.Column("status", db.String(63)),
db.Column("type", db.String(63)),
db.Column("timestamp", db.types.TIMESTAMP),
db.Column("tick_body", db.Text),
db.Column("create_timestamp", db.DateTime, server_default=get_current_timestamp()),
db.Column("update_timestamp", db.DateTime, server_default=get_current_timestamp()),
)
db.Index("idx_job_tick_status", JobTickTable.c.job_origin_id, JobTickTable.c.status)
db.Index("idx_job_tick_timestamp", JobTickTable.c.job_origin_id, JobTickTable.c.timestamp)
def upgrade(migrate_engine):
meta = sql.MetaData()
meta.bind = migrate_engine
if migrate_engine.name == 'mysql':
# In Folsom we explicitly converted migrate_version to UTF8.
migrate_engine.execute(
'ALTER TABLE migrate_version CONVERT TO CHARACTER SET utf8')
# Set default DB charset to UTF8.
migrate_engine.execute(
'ALTER DATABASE %s DEFAULT CHARACTER SET utf8' %
migrate_engine.url.database)
credential = sql.Table(
'credential', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('user_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('type', sql.String(length=255), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
domain = sql.Table(
'domain', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=64), nullable=False),
sql.Column('enabled', sql.Boolean, default=True, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
endpoint = sql.Table(
'endpoint', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('legacy_endpoint_id', sql.String(length=64)),
sql.Column('interface', sql.String(length=8), nullable=False),
sql.Column('region', sql.String(length=255)),
sql.Column('service_id', sql.String(length=64), nullable=False),
sql.Column('url', sql.Text, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group = sql.Table(
'group', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('domain_id', sql.String(length=64), nullable=False),
sql.Column('name', sql.String(length=64), nullable=False),
sql.Column('description', sql.Text),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group_domain_metadata = sql.Table(
'group_domain_metadata', meta,
sql.Column('group_id', sql.String(length=64), primary_key=True),
sql.Column('domain_id', sql.String(length=64), primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
group_project_metadata = sql.Table(
'group_project_metadata', meta,
sql.Column('group_id', sql.String(length=64), primary_key=True),
sql.Column('project_id', sql.String(length=64), primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
policy = sql.Table(
'policy', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('type', sql.String(length=255), nullable=False),
sql.Column('blob', ks_sql.JsonBlob, nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
project = sql.Table(
'project', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=64), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('description', sql.Text),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id', sql.String(length=64), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
role = sql.Table(
'role', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
service = sql.Table(
'service', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('type', sql.String(length=255)),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
token = sql.Table(
'token', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('expires', sql.DateTime, default=None),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('valid', sql.Boolean, default=True, nullable=False),
sql.Column('trust_id', sql.String(length=64)),
sql.Column('user_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust = sql.Table(
'trust', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('trustor_user_id', sql.String(length=64), nullable=False),
sql.Column('trustee_user_id', sql.String(length=64), nullable=False),
sql.Column('project_id', sql.String(length=64)),
sql.Column('impersonation', sql.Boolean, nullable=False),
sql.Column('deleted_at', sql.DateTime),
sql.Column('expires_at', sql.DateTime),
sql.Column('extra', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
trust_role = sql.Table(
'trust_role', meta,
sql.Column('trust_id', sql.String(length=64), primary_key=True,
nullable=False),
sql.Column('role_id', sql.String(length=64), primary_key=True,
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8')
user = sql.Table(
'user', meta,
sql.Column('id', sql.String(length=64), primary_key=True),
sql.Column('name', sql.String(length=255), nullable=False),
sql.Column('extra', ks_sql.JsonBlob.impl),
sql.Column('password', sql.String(length=128)),
sql.Column('enabled', sql.Boolean),
sql.Column('domain_id', sql.String(length=64), nullable=False),
sql.Column('default_project_id', sql.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_domain_metadata = sql.Table(
'user_domain_metadata', meta,
sql.Column('user_id', sql.String(length=64), primary_key=True),
sql.Column('domain_id', sql.String(length=64), primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_group_membership = sql.Table(
'user_group_membership', meta,
sql.Column('user_id', sql.String(length=64), primary_key=True),
sql.Column('group_id', sql.String(length=64), primary_key=True),
mysql_engine='InnoDB',
mysql_charset='utf8')
user_project_metadata = sql.Table(
'user_project_metadata', meta,
sql.Column('user_id', sql.String(length=64), primary_key=True),
sql.Column('project_id', sql.String(length=64), primary_key=True),
sql.Column('data', ks_sql.JsonBlob.impl),
mysql_engine='InnoDB',
mysql_charset='utf8')
# create all tables
tables = [credential, domain, endpoint, group, group_domain_metadata,
group_project_metadata, policy, project, role, service,
token, trust, trust_role, user, user_domain_metadata,
user_group_membership, user_project_metadata]
for table in tables:
try:
table.create()
except Exception:
LOG.exception('Exception while creating table: %r', table)
raise
# Unique Constraints
migrate.UniqueConstraint(user.c.domain_id,
user.c.name,
name='ixu_user_name_domain_id').create()
migrate.UniqueConstraint(group.c.domain_id,
group.c.name,
name='ixu_group_name_domain_id').create()
migrate.UniqueConstraint(role.c.name,
name='ixu_role_name').create()
migrate.UniqueConstraint(project.c.domain_id,
project.c.name,
name='ixu_project_name_domain_id').create()
migrate.UniqueConstraint(domain.c.name,
name='ixu_domain_name').create()
# Indexes
sql.Index('ix_token_expires', token.c.expires).create()
sql.Index('ix_token_expires_valid', token.c.expires,
token.c.valid).create()
fkeys = [
{'columns': [user_project_metadata.c.project_id],
'references': [project.c.id],
'name': 'fk_user_project_metadata_project_id'},
{'columns': [user_domain_metadata.c.domain_id],
'references': [domain.c.id],
'name': 'fk_user_domain_metadata_domain_id'},
{'columns': [group_project_metadata.c.project_id],
'references': [project.c.id],
'name': 'fk_group_project_metadata_project_id'},
{'columns': [group_domain_metadata.c.domain_id],
'references': [domain.c.id],
'name': 'fk_group_domain_metadata_domain_id'},
{'columns': [endpoint.c.service_id],
'references': [service.c.id]},
{'columns': [user_group_membership.c.group_id],
'references': [group.c.id],
'name': 'fk_user_group_membership_group_id'},
{'columns': [user_group_membership.c.user_id],
'references':[user.c.id],
'name': 'fk_user_group_membership_user_id'},
{'columns': [user.c.domain_id],
'references': [domain.c.id],
'name': 'fk_user_domain_id'},
{'columns': [group.c.domain_id],
'references': [domain.c.id],
'name': 'fk_group_domain_id'},
{'columns': [project.c.domain_id],
'references': [domain.c.id],
'name': 'fk_project_domain_id'}
]
for fkey in fkeys:
migrate.ForeignKeyConstraint(columns=fkey['columns'],
refcolumns=fkey['references'],
name=fkey.get('name')).create()
# Create the default domain.
session = orm.sessionmaker(bind=migrate_engine)()
domain.insert(migration_helpers.get_default_domain()).execute()
session.commit()
def upgrade():
op.create_table(
'aim_vmm_policies', sa.Column('type', sa.String(64), nullable=False),
sa.Column('aim_id', sa.Integer, autoincrement=True),
sa.Column('display_name', sa.String(256), nullable=False, default=''),
sa.Column('monitored', sa.Boolean, nullable=False, default=False),
sa.PrimaryKeyConstraint('aim_id'))
session = api.get_session(expire_on_commit=True)
old_vmm_table = sa.Table('aim_vmm_domains', sa.MetaData(),
sa.Column('type', sa.String(64), nullable=False),
sa.Column('name', sa.String(64), nullable=False))
old_phys_table = sa.Table('aim_physical_domains', sa.MetaData(),
sa.Column('name', sa.String(64), nullable=False))
mgr = aim_manager.AimManager()
ctx = context.AimContext(db_session=session)
new_vmms = []
new_phys = []
with session.begin(subtransactions=True):
for vmm in session.query(old_vmm_table).all():
new_vmms.append(
resource.VMMDomain(type=vmm.type,
name=vmm.name,
monitored=True))
for phys in session.query(old_phys_table).all():
new_phys.append(
resource.PhysicalDomain(name=phys.name, monitored=True))
op.drop_table('aim_vmm_domains')
op.drop_table('aim_physical_domains')
op.create_table(
'aim_vmm_domains', sa.Column('type', sa.String(64), nullable=False),
sa.Column('name', sa.String(64), nullable=False),
sa.Column('aim_id', sa.Integer, autoincrement=True),
sa.Column('display_name', sa.String(256), nullable=False, default=''),
sa.Column('monitored', sa.Boolean, nullable=False, default=False),
sa.Column('enforcement_pref', sa.Enum('sw', 'hw', 'unknown')),
sa.Column('mode', sa.Enum('default', 'n1kv', 'unknown', 'ovs', 'k8s')),
sa.Column('mcast_address', sa.String(64)),
sa.Column('encap_mode', sa.Enum('unknown', 'vlan', 'vxlan')),
sa.Column('pref_encap_mode', sa.Enum('unspecified', 'vlan', 'vxlan')),
sa.Column('vlan_pool_name', sa.String(64)),
sa.Column('vlan_pool_type', sa.Enum('static', 'dynamic')),
sa.Column('mcast_addr_pool_name', sa.String(64)),
sa.PrimaryKeyConstraint('aim_id'),
sa.UniqueConstraint('type',
'name',
name='uniq_aim_vmm_domains_identity'),
sa.Index('idx_aim_vmm_domains_identity', 'type', 'name'))
op.create_table(
'aim_physical_domains', sa.Column('name',
sa.String(64),
nullable=False),
sa.Column('aim_id', sa.Integer, autoincrement=True),
sa.Column('display_name', sa.String(256), nullable=False, default=''),
sa.Column('monitored', sa.Boolean, nullable=False, default=False),
sa.PrimaryKeyConstraint('aim_id'))
with session.begin(subtransactions=True):
for obj in new_vmms + new_phys:
mgr.create(ctx, obj)
class Metric(Base, GnocchiBase, storage.Metric):
__tablename__ = 'metric'
__table_args__ = (
sqlalchemy.Index('ix_metric_status', 'status'),
sqlalchemy.UniqueConstraint("resource_id",
"name",
name="uniq_metric0resource_id0name"),
COMMON_TABLES_ARGS,
)
id = sqlalchemy.Column(sqlalchemy_utils.UUIDType(), primary_key=True)
archive_policy_name = sqlalchemy.Column(
sqlalchemy.String(255),
sqlalchemy.ForeignKey(
'archive_policy.name',
ondelete="RESTRICT",
name="fk_metric_archive_policy_name_archive_policy_name"),
nullable=False)
archive_policy = sqlalchemy.orm.relationship(ArchivePolicy, lazy="joined")
created_by_user_id = sqlalchemy.Column(sqlalchemy.String(255))
created_by_project_id = sqlalchemy.Column(sqlalchemy.String(255))
resource_id = sqlalchemy.Column(
sqlalchemy_utils.UUIDType(),
sqlalchemy.ForeignKey('resource.id',
ondelete="SET NULL",
name="fk_metric_resource_id_resource_id"))
name = sqlalchemy.Column(sqlalchemy.String(255))
status = sqlalchemy.Column(sqlalchemy.Enum('active',
'delete',
name="metric_status_enum"),
nullable=False,
server_default='active')
def jsonify(self):
d = {
"id": self.id,
"created_by_user_id": self.created_by_user_id,
"created_by_project_id": self.created_by_project_id,
"name": self.name,
}
unloaded = sqlalchemy.inspect(self).unloaded
if 'resource' in unloaded:
d['resource_id'] = self.resource_id
else:
d['resource'] = self.resource
if 'archive_policy' in unloaded:
d['archive_policy_name'] = self.archive_policy_name
else:
d['archive_policy'] = self.archive_policy
return d
def __eq__(self, other):
# NOTE(jd) If `other` is a SQL Metric, we only compare
# archive_policy_name, and we don't compare archive_policy that might
# not be loaded. Otherwise we fallback to the original comparison for
# storage.Metric.
return ((isinstance(other, Metric) and self.id == other.id
and self.archive_policy_name == other.archive_policy_name
and self.created_by_user_id == other.created_by_user_id
and self.created_by_project_id == other.created_by_project_id
and self.name == other.name
and self.resource_id == other.resource_id)
or (storage.Metric.__eq__(self, other)))
__hash__ = storage.Metric.__hash__
def __table_args__(self):
return sa.Index('subs_uri_idx_%s' % self.__tablename__, 'uri'),
def join_model(
join_table,
model_1,
model_2,
column_1=None,
column_2=None,
fk_1="id",
fk_2="id",
base=Base,
):
"""Helper function to create a join table for a many-to-many relationship.
Parameters
----------
join_table : str
Name of the new table to be created.
model_1 : str
First model in the relationship.
model_2 : str
Second model in the relationship.
column_1 : str, optional
Name of the join table column corresponding to `model_1`. If `None`,
then {`table1`[:-1]_id} will be used (e.g., `user_id` for `users`).
column_2 : str, optional
Name of the join table column corresponding to `model_2`. If `None`,
then {`table2`[:-1]_id} will be used (e.g., `user_id` for `users`).
fk_1 : str, optional
Name of the column from `model_1` that the foreign key should refer to.
fk_2 : str, optional
Name of the column from `model_2` that the foreign key should refer to.
base : sqlalchemy.ext.declarative.api.DeclarativeMeta
SQLAlchemy model base to subclass.
Returns
-------
sqlalchemy.ext.declarative.api.DeclarativeMeta
SQLAlchemy association model class
"""
table_1 = model_1.__tablename__
table_2 = model_2.__tablename__
if column_1 is None:
column_1 = f"{table_1[:-1]}_id"
if column_2 is None:
column_2 = f"{table_2[:-1]}_id"
forward_ind_name = f"{join_table}_forward_ind"
reverse_ind_name = f"{join_table}_reverse_ind"
model_attrs = {
"__tablename__":
join_table,
"id":
sa.Column(sa.Integer,
primary_key=True,
doc="Unique object identifier."),
column_1:
sa.Column(
column_1,
sa.ForeignKey(f"{table_1}.{fk_1}", ondelete="CASCADE"),
nullable=False,
),
column_2:
sa.Column(
column_2,
sa.ForeignKey(f"{table_2}.{fk_2}", ondelete="CASCADE"),
nullable=False,
),
}
model_attrs.update({
model_1.__name__.lower():
relationship(
model_1,
cascade="save-update, merge, refresh-expire, expunge",
foreign_keys=[model_attrs[column_1]],
),
model_2.__name__.lower():
relationship(
model_2,
cascade="save-update, merge, refresh-expire, expunge",
foreign_keys=[model_attrs[column_2]],
),
forward_ind_name:
sa.Index(
forward_ind_name,
model_attrs[column_1],
model_attrs[column_2],
unique=True,
),
reverse_ind_name:
sa.Index(reverse_ind_name, model_attrs[column_2],
model_attrs[column_1]),
})
model = type(model_1.__name__ + model_2.__name__, (base, JoinModel),
model_attrs)
model.read = model.create = AccessibleIfRelatedRowsAreAccessible(
**{
model_1.__name__.lower(): "read",
model_2.__name__.lower(): "read"
})
return model
def create_tables_thd(self, conn):
metadata = sa.MetaData()
metadata.bind = conn
self.changes = sa.Table(
'changes',
metadata,
sa.Column('changeid', sa.Integer, primary_key=True),
sa.Column('author', sa.String(256), nullable=False),
sa.Column('comments', sa.String(1024), nullable=False),
sa.Column('is_dir', sa.SmallInteger, nullable=False),
sa.Column('branch', sa.String(256)),
sa.Column('revision', sa.String(256)),
sa.Column('revlink', sa.String(256)),
sa.Column('when_timestamp', sa.Integer, nullable=False),
sa.Column('category', sa.String(256)),
sa.Column('repository',
sa.String(length=512),
nullable=False,
server_default=''),
sa.Column('project',
sa.String(length=512),
nullable=False,
server_default=''),
)
self.changes.create(bind=conn)
self.schedulers = sa.Table(
"schedulers",
metadata,
sa.Column('schedulerid', sa.Integer, primary_key=True),
sa.Column('name', sa.String(128), nullable=False),
sa.Column('class_name', sa.String(128), nullable=False),
)
self.schedulers.create(bind=conn)
self.users = sa.Table(
"users",
metadata,
sa.Column("uid", sa.Integer, primary_key=True),
sa.Column("identifier", sa.String(256), nullable=False),
sa.Column("bb_username", sa.String(128)),
sa.Column("bb_password", sa.String(128)),
)
self.users.create(bind=conn)
self.objects = sa.Table(
"objects",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column('name', sa.String(128), nullable=False),
sa.Column('class_name', sa.String(128), nullable=False),
)
self.objects.create()
self.object_state = sa.Table(
"object_state",
metadata,
sa.Column("objectid",
sa.Integer,
sa.ForeignKey('objects.id'),
nullable=False),
sa.Column("name", sa.String(length=256), nullable=False),
sa.Column("value_json", sa.Text, nullable=False),
)
self.object_state.create()
# these indices should already exist everywhere but on sqlite
if conn.dialect.name != 'sqlite':
sa.Index('name_and_class', self.schedulers.c.name,
self.schedulers.c.class_name).create()
sa.Index('changes_branch', self.changes.c.branch).create()
sa.Index('changes_revision', self.changes.c.revision).create()
sa.Index('changes_author', self.changes.c.author).create()
sa.Index('changes_category', self.changes.c.category).create()
sa.Index('changes_when_timestamp',
self.changes.c.when_timestamp).create()
# create this index without the unique attribute
sa.Index('users_identifier', self.users.c.identifier).create()
def upgrade(migrate_engine):
metadata = sa.MetaData()
metadata.bind = migrate_engine
# autoload the tables that are only referenced here
changes = sa.Table('changes', metadata, autoload=True)
objects = sa.Table('buildsets', metadata, autoload=True)
sa.Table("objects", metadata, autoload=True)
# drop all tables. Schedulers will re-populate on startup
scheduler_changes_tbl = sa.Table(
'scheduler_changes',
metadata,
sa.Column('schedulerid', sa.Integer),
# ...
)
scheduler_changes_tbl.drop()
metadata.remove(scheduler_changes_tbl)
scheduler_upstream_buildsets_tbl = sa.Table(
'scheduler_upstream_buildsets',
metadata,
sa.Column('buildsetid', sa.Integer),
# ...
)
scheduler_upstream_buildsets_tbl.drop()
metadata.remove(scheduler_upstream_buildsets_tbl)
schedulers_tbl = sa.Table(
"schedulers",
metadata,
sa.Column('schedulerid', sa.Integer),
# ...
)
schedulers_tbl.drop()
metadata.remove(schedulers_tbl)
# schedulers and scheduler_upstream_buildsets aren't coming back, but
# scheduler_changes is -- along with its indexes
scheduler_changes_tbl = sa.Table(
'scheduler_changes',
metadata,
sa.Column('objectid', sa.Integer, sa.ForeignKey('objects.id')),
sa.Column('changeid', sa.Integer, sa.ForeignKey('changes.changeid')),
sa.Column('important', sa.Integer),
)
scheduler_changes_tbl.create()
cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.objectid],
[objects.c.id])
cons.create()
cons = constraint.ForeignKeyConstraint([scheduler_changes_tbl.c.changeid],
[changes.c.changeid])
cons.create()
idx = sa.Index('scheduler_changes_objectid',
scheduler_changes_tbl.c.objectid)
idx.create()
idx = sa.Index('scheduler_changes_changeid',
scheduler_changes_tbl.c.changeid)
idx.create()
idx = sa.Index('scheduler_changes_unique',
scheduler_changes_tbl.c.objectid,
scheduler_changes_tbl.c.changeid,
unique=True)
idx.create()
class Model(base.DBConnectorComponent):
#
# schema
#
metadata = sa.MetaData()
# NOTES
# * server_defaults here are included to match those added by the migration
# scripts, but they should not be depended on - all code accessing these
# tables should supply default values as necessary. The defaults are
# required during migration when adding non-nullable columns to existing
# tables.
#
# * dates are stored as unix timestamps (UTC-ish epoch time)
#
# * sqlalchemy does not handle sa.Boolean very well on MySQL or Postgres;
# use sa.SmallInteger instead
#
# * BuildRequest.canBeCollapsed() depends on buildrequest.id being auto-incremented which is
# sqlalchemy default.
# Tables related to build requests
# --------------------------------
# A BuildRequest is a request for a particular build to be performed. Each
# BuildRequest is a part of a Buildset. BuildRequests are claimed by
# masters, to avoid multiple masters running the same build.
buildrequests = sautils.Table(
'buildrequests',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey('buildsets.id', ondelete='CASCADE'),
nullable=False),
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('priority',
sa.Integer,
nullable=False,
server_default=sa.DefaultClause("0")),
# if this is zero, then the build is still pending
sa.Column('complete', sa.Integer,
server_default=sa.DefaultClause("0")),
# results is only valid when complete == 1; 0 = SUCCESS, 1 = WARNINGS,
# etc - see master/buildbot/status/builder.py
sa.Column('results', sa.SmallInteger),
# time the buildrequest was created
sa.Column('submitted_at', sa.Integer, nullable=False),
# time the buildrequest was completed, or NULL
sa.Column('complete_at', sa.Integer),
# boolean indicating whether there is a step blocking, waiting for this
# request to complete
sa.Column('waited_for',
sa.SmallInteger,
server_default=sa.DefaultClause("0")),
)
# Each row in this table represents a claimed build request, where the
# claim is made by the master referenced by masterid.
buildrequest_claims = sautils.Table(
'buildrequest_claims',
metadata,
sa.Column('brid',
sa.Integer,
sa.ForeignKey('buildrequests.id', ondelete='CASCADE'),
nullable=False),
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
index=True,
nullable=False),
sa.Column('claimed_at', sa.Integer, nullable=False),
)
# Tables related to builds
# ------------------------
# This table contains the build properties
build_properties = sautils.Table(
'build_properties',
metadata,
sa.Column('buildid',
sa.Integer,
sa.ForeignKey('builds.id', ondelete='CASCADE'),
nullable=False),
sa.Column('name', sa.String(256), nullable=False),
# JSON encoded value
sa.Column('value', sa.Text, nullable=False),
sa.Column('source', sa.String(256), nullable=False),
)
# This table contains transient build state.
build_data = sautils.Table(
'build_data',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildid',
sa.Integer,
sa.ForeignKey('builds.id', ondelete='CASCADE'),
nullable=False),
sa.Column('name', sa.String(256), nullable=False),
sa.Column('value',
sa.LargeBinary().with_variant(sa.dialects.mysql.LONGBLOB,
"mysql"),
nullable=False),
sa.Column('length', sa.Integer, nullable=False),
sa.Column('source', sa.String(256), nullable=False),
)
# This table contains basic information about each build.
builds = sautils.Table(
'builds',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('number', sa.Integer, nullable=False),
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
# note that there is 1:N relationship here.
# In case of worker loss, build has results RETRY
# and buildrequest is unclaimed.
# We use use_alter to prevent circular reference
# (buildrequests -> buildsets -> builds).
sa.Column('buildrequestid',
sa.Integer,
sa.ForeignKey('buildrequests.id',
use_alter=True,
name='buildrequestid',
ondelete='CASCADE'),
nullable=False),
# worker which performed this build
# keep nullable to support worker-free builds
sa.Column('workerid',
sa.Integer,
sa.ForeignKey('workers.id', ondelete='SET NULL'),
nullable=True),
# master which controlled this build
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
nullable=False),
# start/complete times
sa.Column('started_at', sa.Integer, nullable=False),
sa.Column('complete_at', sa.Integer),
sa.Column('state_string', sa.Text, nullable=False),
sa.Column('results', sa.Integer),
)
# Tables related to steps
# -----------------------
steps = sautils.Table(
'steps',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('number', sa.Integer, nullable=False),
sa.Column('name', sa.String(50), nullable=False),
sa.Column('buildid',
sa.Integer,
sa.ForeignKey('builds.id', ondelete='CASCADE'),
nullable=False),
sa.Column('started_at', sa.Integer),
sa.Column('complete_at', sa.Integer),
sa.Column('state_string', sa.Text, nullable=False),
sa.Column('results', sa.Integer),
sa.Column('urls_json', sa.Text, nullable=False),
sa.Column('hidden',
sa.SmallInteger,
nullable=False,
server_default='0'),
)
# Tables related to logs
# ----------------------
logs = sautils.Table(
'logs',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('name', sa.Text, nullable=False),
sa.Column('slug', sa.String(50), nullable=False),
sa.Column('stepid',
sa.Integer,
sa.ForeignKey('steps.id', ondelete='CASCADE'),
nullable=False),
sa.Column('complete', sa.SmallInteger, nullable=False),
sa.Column('num_lines', sa.Integer, nullable=False),
# 's' = stdio, 't' = text, 'h' = html, 'd' = deleted
sa.Column('type', sa.String(1), nullable=False),
)
logchunks = sautils.Table(
'logchunks',
metadata,
sa.Column('logid',
sa.Integer,
sa.ForeignKey('logs.id', ondelete='CASCADE'),
nullable=False),
# 0-based line number range in this chunk (inclusive); note that for
# HTML logs, this counts lines of HTML, not lines of rendered output
sa.Column('first_line', sa.Integer, nullable=False),
sa.Column('last_line', sa.Integer, nullable=False),
# log contents, including a terminating newline, encoded in utf-8 or,
# if 'compressed' is not 0, compressed with gzip, bzip2 or lz4
sa.Column('content', sa.LargeBinary(65536)),
sa.Column('compressed', sa.SmallInteger, nullable=False),
)
# Tables related to buildsets
# ---------------------------
# This table contains input properties for buildsets
buildset_properties = sautils.Table(
'buildset_properties',
metadata,
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey('buildsets.id', ondelete='CASCADE'),
nullable=False),
sa.Column('property_name', sa.String(256), nullable=False),
# JSON-encoded tuple of (value, source)
sa.Column('property_value', sa.Text, nullable=False),
)
# This table represents Buildsets - sets of BuildRequests that share the
# same original cause and source information.
buildsets = sautils.Table(
'buildsets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# a simple external identifier to track down this buildset later, e.g.,
# for try requests
sa.Column('external_idstring', sa.String(256)),
# a short string giving the reason the buildset was created
sa.Column('reason', sa.String(256)),
sa.Column('submitted_at', sa.Integer, nullable=False),
# if this is zero, then the build set is still pending
sa.Column('complete',
sa.SmallInteger,
nullable=False,
server_default=sa.DefaultClause("0")),
sa.Column('complete_at', sa.Integer),
# results is only valid when complete == 1; 0 = SUCCESS, 1 = WARNINGS,
# etc - see master/buildbot/status/builder.py
sa.Column('results', sa.SmallInteger),
# optional parent build, we use use_alter to prevent circular reference
# http://docs.sqlalchemy.org/en/latest/orm/relationships.html#rows-that-point-to-themselves-mutually-dependent-rows
sa.Column('parent_buildid',
sa.Integer,
sa.ForeignKey('builds.id',
use_alter=True,
name='parent_buildid',
ondelete='SET NULL'),
nullable=True),
# text describing what is the relationship with the build
# could be 'triggered from', 'rebuilt from', 'inherited from'
sa.Column('parent_relationship', sa.Text),
)
# Tables related to change sources
# --------------------------------
# The changesources table gives a unique identifier to each ChangeSource. It
# also links to other tables used to ensure only one master runs each
# changesource
changesources = sautils.Table(
'changesources',
metadata,
sa.Column("id", sa.Integer, primary_key=True),
# name for this changesource, as given in the configuration, plus a hash
# of that name used for a unique index
sa.Column('name', sa.Text, nullable=False),
sa.Column('name_hash', sa.String(40), nullable=False),
)
# This links changesources to the master where they are running. A changesource
# linked to a master that is inactive can be unlinked by any master. This
# is a separate table so that we can "claim" changesources on a master by
# inserting; this has better support in database servers for ensuring that
# exactly one claim succeeds.
changesource_masters = sautils.Table(
'changesource_masters',
metadata,
sa.Column('changesourceid',
sa.Integer,
sa.ForeignKey('changesources.id', ondelete='CASCADE'),
nullable=False,
primary_key=True),
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
nullable=False),
)
# Tables related to workers
# -------------------------
workers = sautils.Table(
"workers",
metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("name", sa.String(50), nullable=False),
sa.Column("info", JsonObject, nullable=False),
sa.Column("paused",
sa.SmallInteger,
nullable=False,
server_default="0"),
sa.Column("graceful",
sa.SmallInteger,
nullable=False,
server_default="0"),
)
# link workers to all builder/master pairs for which they are
# configured
configured_workers = sautils.Table(
'configured_workers',
metadata,
sa.Column('id', sa.Integer, primary_key=True, nullable=False),
sa.Column('buildermasterid',
sa.Integer,
sa.ForeignKey('builder_masters.id', ondelete='CASCADE'),
nullable=False),
sa.Column('workerid',
sa.Integer,
sa.ForeignKey('workers.id', ondelete='CASCADE'),
nullable=False),
)
# link workers to the masters they are currently connected to
connected_workers = sautils.Table(
'connected_workers',
metadata,
sa.Column('id', sa.Integer, primary_key=True, nullable=False),
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
nullable=False),
sa.Column('workerid',
sa.Integer,
sa.ForeignKey('workers.id', ondelete='CASCADE'),
nullable=False),
)
# Tables related to changes
# ----------------------------
# Files touched in changes
change_files = sautils.Table(
'change_files',
metadata,
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid', ondelete='CASCADE'),
nullable=False),
sa.Column('filename', sa.String(1024), nullable=False),
)
# Properties for changes
change_properties = sautils.Table(
'change_properties',
metadata,
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid', ondelete='CASCADE'),
nullable=False),
sa.Column('property_name', sa.String(256), nullable=False),
# JSON-encoded tuple of (value, source)
sa.Column('property_value', sa.Text, nullable=False),
)
# users associated with this change; this allows multiple users for
# situations where a version-control system can represent both an author
# and committer, for example.
change_users = sautils.Table(
"change_users",
metadata,
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid', ondelete='CASCADE'),
nullable=False),
# uid for the author of the change with the given changeid
sa.Column('uid',
sa.Integer,
sa.ForeignKey('users.uid', ondelete='CASCADE'),
nullable=False),
)
# Changes to the source code, produced by ChangeSources
changes = sautils.Table(
'changes',
metadata,
# changeid also serves as 'change number'
sa.Column('changeid', sa.Integer, primary_key=True),
# author's name (usually an email address)
sa.Column('author', sa.String(255), nullable=False),
# committer's name
sa.Column('committer', sa.String(255), nullable=True),
# commit comment
sa.Column('comments', sa.Text, nullable=False),
# The branch where this change occurred. When branch is NULL, that
# means the main branch (trunk, master, etc.)
sa.Column('branch', sa.String(255)),
# revision identifier for this change
sa.Column('revision', sa.String(255)), # CVS uses NULL
sa.Column('revlink', sa.String(256)),
# this is the timestamp of the change - it is usually copied from the
# version-control system, and may be long in the past or even in the
# future!
sa.Column('when_timestamp', sa.Integer, nullable=False),
# an arbitrary string used for filtering changes
sa.Column('category', sa.String(255)),
# repository specifies, along with revision and branch, the
# source tree in which this change was detected.
sa.Column('repository',
sa.String(length=512),
nullable=False,
server_default=''),
# codebase is a logical name to specify what is in the repository
sa.Column('codebase',
sa.String(256),
nullable=False,
server_default=sa.DefaultClause("")),
# project names the project this source code represents. It is used
# later to filter changes
sa.Column('project',
sa.String(length=512),
nullable=False,
server_default=''),
# the sourcestamp this change brought the codebase to
sa.Column('sourcestampid',
sa.Integer,
sa.ForeignKey('sourcestamps.id', ondelete='CASCADE'),
nullable=False),
# The parent of the change
# Even if for the moment there's only 1 parent for a change, we use plural here because
# somedays a change will have multiple parent. This way we don't need
# to change the API
sa.Column('parent_changeids',
sa.Integer,
sa.ForeignKey('changes.changeid', ondelete='SET NULL'),
nullable=True),
)
# Tables related to sourcestamps
# ------------------------------
# Patches for SourceStamps that were generated through the try mechanism
patches = sautils.Table(
'patches',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# number of directory levels to strip off (patch -pN)
sa.Column('patchlevel', sa.Integer, nullable=False),
# base64-encoded version of the patch file
sa.Column('patch_base64', sa.Text, nullable=False),
# patch author, if known
sa.Column('patch_author', sa.Text, nullable=False),
# patch comment
sa.Column('patch_comment', sa.Text, nullable=False),
# subdirectory in which the patch should be applied; NULL for top-level
sa.Column('subdir', sa.Text),
)
# A sourcestamp identifies a particular instance of the source code.
# Ideally, this would always be absolute, but in practice source stamps can
# also mean "latest" (when revision is NULL), which is of course a
# time-dependent definition.
sourcestamps = sautils.Table(
'sourcestamps',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# hash of the branch, revision, patchid, repository, codebase, and
# project, using hashColumns.
sa.Column('ss_hash', sa.String(40), nullable=False),
# the branch to check out. When branch is NULL, that means
# the main branch (trunk, master, etc.)
sa.Column('branch', sa.String(256)),
# the revision to check out, or the latest if NULL
sa.Column('revision', sa.String(256)),
# the patch to apply to generate this source code
sa.Column('patchid',
sa.Integer,
sa.ForeignKey('patches.id', ondelete='CASCADE'),
nullable=True),
# the repository from which this source should be checked out
sa.Column('repository',
sa.String(length=512),
nullable=False,
server_default=''),
# codebase is a logical name to specify what is in the repository
sa.Column('codebase',
sa.String(256),
nullable=False,
server_default=sa.DefaultClause("")),
# the project this source code represents
sa.Column('project',
sa.String(length=512),
nullable=False,
server_default=''),
# the time this sourcetamp was first seen (the first time it was added)
sa.Column('created_at', sa.Integer, nullable=False),
)
# a many-to-may relationship between buildsets and sourcestamps
buildset_sourcestamps = sautils.Table(
'buildset_sourcestamps',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('buildsetid',
sa.Integer,
sa.ForeignKey('buildsets.id', ondelete='CASCADE'),
nullable=False),
sa.Column('sourcestampid',
sa.Integer,
sa.ForeignKey('sourcestamps.id', ondelete='CASCADE'),
nullable=False),
)
# Tables related to schedulers
# ----------------------------
# The schedulers table gives a unique identifier to each scheduler. It
# also links to other tables used to ensure only one master runs each
# scheduler, and to track changes that a scheduler may trigger a build for
# later.
schedulers = sautils.Table(
'schedulers',
metadata,
sa.Column("id", sa.Integer, primary_key=True),
# name for this scheduler, as given in the configuration, plus a hash
# of that name used for a unique index
sa.Column('name', sa.Text, nullable=False),
sa.Column('name_hash', sa.String(40), nullable=False),
sa.Column('enabled',
sa.SmallInteger,
server_default=sa.DefaultClause("1")),
)
# This links schedulers to the master where they are running. A scheduler
# linked to a master that is inactive can be unlinked by any master. This
# is a separate table so that we can "claim" schedulers on a master by
# inserting; this has better support in database servers for ensuring that
# exactly one claim succeeds. The ID column is present for external users;
# see bug #1053.
scheduler_masters = sautils.Table(
'scheduler_masters',
metadata,
sa.Column('schedulerid',
sa.Integer,
sa.ForeignKey('schedulers.id', ondelete='CASCADE'),
nullable=False,
primary_key=True),
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
nullable=False),
)
# This table references "classified" changes that have not yet been
# "processed". That is, the scheduler has looked at these changes and
# determined that something should be done, but that hasn't happened yet.
# Rows are deleted from this table as soon as the scheduler is done with
# the change.
scheduler_changes = sautils.Table(
'scheduler_changes',
metadata,
sa.Column('schedulerid',
sa.Integer,
sa.ForeignKey('schedulers.id', ondelete='CASCADE'),
nullable=False),
sa.Column('changeid',
sa.Integer,
sa.ForeignKey('changes.changeid', ondelete='CASCADE'),
nullable=False),
# true (nonzero) if this change is important to this scheduler
sa.Column('important', sa.Integer),
)
# Tables related to builders
# --------------------------
builders = sautils.Table(
'builders',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# builder's name
sa.Column('name', sa.Text, nullable=False),
# builder's description
sa.Column('description', sa.Text, nullable=True),
# sha1 of name; used for a unique index
sa.Column('name_hash', sa.String(40), nullable=False),
)
# This links builders to the master where they are running. A builder
# linked to a master that is inactive can be unlinked by any master. Note
# that builders can run on multiple masters at the same time.
builder_masters = sautils.Table(
'builder_masters',
metadata,
sa.Column('id', sa.Integer, primary_key=True, nullable=False),
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('masterid',
sa.Integer,
sa.ForeignKey('masters.id', ondelete='CASCADE'),
nullable=False),
)
# Tables related to tags
# ----------------------
tags = sautils.Table(
'tags',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# tag's name
sa.Column('name', sa.Text, nullable=False),
# sha1 of name; used for a unique index
sa.Column('name_hash', sa.String(40), nullable=False),
)
# a many-to-may relationship between builders and tags
builders_tags = sautils.Table(
'builders_tags',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('tagid',
sa.Integer,
sa.ForeignKey('tags.id', ondelete='CASCADE'),
nullable=False),
)
# Tables related to test results
# ------------------------------
# Represents a single test result set. A step can any number of test result sets,
# each of which may contain any number of test results.
test_result_sets = sautils.Table(
'test_result_sets',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# In the future we will want to rearrange the underlying data in the database according
# to (builderid, buildid) tuple, so that huge number of entries in the table does not
# reduce the efficiency of retrieval of data for a particular build.
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('buildid',
sa.Integer,
sa.ForeignKey('builds.id', ondelete='CASCADE'),
nullable=False),
sa.Column('stepid',
sa.Integer,
sa.ForeignKey('steps.id', ondelete='CASCADE'),
nullable=False),
# The free-form description of the source of the test data that represent the test result
# set.
sa.Column('description', sa.Text, nullable=True),
sa.Column('category', sa.Text, nullable=False),
sa.Column('value_unit', sa.Text, nullable=False),
# The number of passed tests in cases when the pass or fail criteria depends only on how
# that single test runs.
sa.Column('tests_passed', sa.Integer, nullable=True),
# The number of failed tests in cases when the pass or fail criteria depends only on how
# that single test runs.
sa.Column('tests_failed', sa.Integer, nullable=True),
# true when all test results associated with test result set have been generated.
sa.Column('complete', sa.SmallInteger, nullable=False),
)
# Represents a test result. A single test result set will represent thousands of test results
# in any significant codebase that's tested.
#
# A common table is used for all tests results regardless of what data they carry. Most serious
# database engines will be able to optimize nullable fields out, so extra columns are almost
# free when not used in such cases.
test_results = sautils.Table(
'test_results',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# The builder ID of the test result set that the test result belongs to.
# This is included for future partitioning support.
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('test_result_setid',
sa.Integer,
sa.ForeignKey('test_result_sets.id', ondelete='CASCADE'),
nullable=False),
sa.Column('test_nameid',
sa.Integer,
sa.ForeignKey('test_names.id', ondelete='CASCADE'),
nullable=True),
sa.Column('test_code_pathid',
sa.Integer,
sa.ForeignKey('test_code_paths.id', ondelete='CASCADE'),
nullable=True),
# The code line that the test originated from
sa.Column('line', sa.Integer, nullable=True),
# The duration of the test execution itself
sa.Column('duration_ns', sa.Integer, nullable=True),
# The result of the test converted to a string.
sa.Column('value', sa.Text, nullable=False),
)
# Represents the test names of test results.
test_names = sautils.Table(
'test_names',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# The builder ID of the test result set that the test result belongs to.
# This is included for future partitioning support and also for querying all test names
# for a builder.
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('name', sa.Text, nullable=False),
)
# Represents the file paths of test results.
test_code_paths = sautils.Table(
'test_code_paths',
metadata,
sa.Column('id', sa.Integer, primary_key=True),
# The builder ID of the test result set that the test result belongs to.
# This is included for future partitioning support
sa.Column('builderid',
sa.Integer,
sa.ForeignKey('builders.id', ondelete='CASCADE'),
nullable=False),
sa.Column('path', sa.Text, nullable=False),
)
# Tables related to objects
# -------------------------
# This table uniquely identifies objects that need to maintain state across
# invocations.
objects = sautils.Table(
"objects",
metadata,
# unique ID for this object
sa.Column("id", sa.Integer, primary_key=True),
# object's user-given name
sa.Column('name', sa.String(128), nullable=False),
# object's class name, basically representing a "type" for the state
sa.Column('class_name', sa.String(128), nullable=False),
)
# This table stores key/value pairs for objects, where the key is a string
# and the value is a JSON string.
object_state = sautils.Table(
"object_state",
metadata,
# object for which this value is set
sa.Column('objectid',
sa.Integer,
sa.ForeignKey('objects.id', ondelete='CASCADE'),
nullable=False),
# name for this value (local to the object)
sa.Column("name", sa.String(length=255), nullable=False),
# value, as a JSON string
sa.Column("value_json", sa.Text, nullable=False),
)
# Tables related to users
# -----------------------
# This table identifies individual users, and contains buildbot-specific
# information about those users.
users = sautils.Table(
"users",
metadata,
# unique user id number
sa.Column("uid", sa.Integer, primary_key=True),
# identifier (nickname) for this user; used for display
sa.Column("identifier", sa.String(255), nullable=False),
# username portion of user credentials for authentication
sa.Column("bb_username", sa.String(128)),
# password portion of user credentials for authentication
sa.Column("bb_password", sa.String(128)),
)
# This table stores information identifying a user that's related to a
# particular interface - a version-control system, status plugin, etc.
users_info = sautils.Table(
"users_info",
metadata,
# unique user id number
sa.Column('uid',
sa.Integer,
sa.ForeignKey('users.uid', ondelete='CASCADE'),
nullable=False),
# type of user attribute, such as 'git'
sa.Column("attr_type", sa.String(128), nullable=False),
# data for given user attribute, such as a commit string or password
sa.Column("attr_data", sa.String(128), nullable=False),
)
# Tables related to masters
# -------------------------
masters = sautils.Table(
"masters",
metadata,
# unique id per master
sa.Column('id', sa.Integer, primary_key=True),
# master's name (generally in the form hostname:basedir)
sa.Column('name', sa.Text, nullable=False),
# sha1 of name; used for a unique index
sa.Column('name_hash', sa.String(40), nullable=False),
# true if this master is running
sa.Column('active', sa.Integer, nullable=False),
# updated periodically by a running master, so silently failed masters
# can be detected by other masters
sa.Column('last_active', sa.Integer, nullable=False),
)
# Indexes
# -------
sa.Index('buildrequests_buildsetid', buildrequests.c.buildsetid)
sa.Index('buildrequests_builderid', buildrequests.c.builderid)
sa.Index('buildrequests_complete', buildrequests.c.complete)
sa.Index('build_properties_buildid', build_properties.c.buildid)
sa.Index('build_data_buildid_name',
build_data.c.buildid,
build_data.c.name,
unique=True)
sa.Index('builds_buildrequestid', builds.c.buildrequestid)
sa.Index('buildsets_complete', buildsets.c.complete)
sa.Index('buildsets_submitted_at', buildsets.c.submitted_at)
sa.Index('buildset_properties_buildsetid',
buildset_properties.c.buildsetid)
sa.Index('workers_name', workers.c.name, unique=True)
sa.Index('changes_branch', changes.c.branch)
sa.Index('changes_revision', changes.c.revision)
sa.Index('changes_author', changes.c.author)
sa.Index('changes_category', changes.c.category)
sa.Index('changes_when_timestamp', changes.c.when_timestamp)
sa.Index('change_files_changeid', change_files.c.changeid)
sa.Index('change_properties_changeid', change_properties.c.changeid)
sa.Index('changes_sourcestampid', changes.c.sourcestampid)
sa.Index('changesource_name_hash', changesources.c.name_hash, unique=True)
sa.Index('scheduler_name_hash', schedulers.c.name_hash, unique=True)
sa.Index('scheduler_changes_schedulerid', scheduler_changes.c.schedulerid)
sa.Index('scheduler_changes_changeid', scheduler_changes.c.changeid)
sa.Index('scheduler_changes_unique',
scheduler_changes.c.schedulerid,
scheduler_changes.c.changeid,
unique=True)
sa.Index('builder_name_hash', builders.c.name_hash, unique=True)
sa.Index('builder_masters_builderid', builder_masters.c.builderid)
sa.Index('builder_masters_masterid', builder_masters.c.masterid)
sa.Index('builder_masters_identity',
builder_masters.c.builderid,
builder_masters.c.masterid,
unique=True)
sa.Index('tag_name_hash', tags.c.name_hash, unique=True)
sa.Index('builders_tags_builderid', builders_tags.c.builderid)
sa.Index('builders_tags_unique',
builders_tags.c.builderid,
builders_tags.c.tagid,
unique=True)
sa.Index('configured_workers_buildmasterid',
configured_workers.c.buildermasterid)
sa.Index('configured_workers_workers', configured_workers.c.workerid)
sa.Index('configured_workers_identity',
configured_workers.c.buildermasterid,
configured_workers.c.workerid,
unique=True)
sa.Index('connected_workers_masterid', connected_workers.c.masterid)
sa.Index('connected_workers_workers', connected_workers.c.workerid)
sa.Index('connected_workers_identity',
connected_workers.c.masterid,
connected_workers.c.workerid,
unique=True)
sa.Index('users_identifier', users.c.identifier, unique=True)
sa.Index('users_info_uid', users_info.c.uid)
sa.Index('users_info_uid_attr_type',
users_info.c.uid,
users_info.c.attr_type,
unique=True)
sa.Index('users_info_attrs',
users_info.c.attr_type,
users_info.c.attr_data,
unique=True)
sa.Index('change_users_changeid', change_users.c.changeid)
sa.Index('users_bb_user', users.c.bb_username, unique=True)
sa.Index('object_identity',
objects.c.name,
objects.c.class_name,
unique=True)
sa.Index('name_per_object',
object_state.c.objectid,
object_state.c.name,
unique=True)
sa.Index('master_name_hashes', masters.c.name_hash, unique=True)
sa.Index('buildrequest_claims_brids',
buildrequest_claims.c.brid,
unique=True)
sa.Index('sourcestamps_ss_hash_key', sourcestamps.c.ss_hash, unique=True)
sa.Index('buildset_sourcestamps_buildsetid',
buildset_sourcestamps.c.buildsetid)
sa.Index('buildset_sourcestamps_unique',
buildset_sourcestamps.c.buildsetid,
buildset_sourcestamps.c.sourcestampid,
unique=True)
sa.Index('builds_number', builds.c.builderid, builds.c.number, unique=True)
sa.Index('builds_workerid', builds.c.workerid)
sa.Index('builds_masterid', builds.c.masterid)
sa.Index('steps_number', steps.c.buildid, steps.c.number, unique=True)
sa.Index('steps_name', steps.c.buildid, steps.c.name, unique=True)
sa.Index('steps_started_at', steps.c.started_at)
sa.Index('logs_slug', logs.c.stepid, logs.c.slug, unique=True)
sa.Index('logchunks_firstline', logchunks.c.logid, logchunks.c.first_line)
sa.Index('logchunks_lastline', logchunks.c.logid, logchunks.c.last_line)
sa.Index('test_names_name',
test_names.c.builderid,
test_names.c.name,
mysql_length={'name': 255})
sa.Index('test_code_paths_path',
test_code_paths.c.builderid,
test_code_paths.c.path,
mysql_length={'path': 255})
# MySQL creates indexes for foreign keys, and these appear in the
# reflection. This is a list of (table, index) names that should be
# expected on this platform
implied_indexes = [
('change_users', dict(unique=False, column_names=['uid'], name='uid')),
('sourcestamps',
dict(unique=False, column_names=['patchid'], name='patchid')),
('scheduler_masters',
dict(unique=False, column_names=['masterid'], name='masterid')),
('changesource_masters',
dict(unique=False, column_names=['masterid'], name='masterid')),
('buildset_sourcestamps',
dict(unique=False,
column_names=['sourcestampid'],
name='sourcestampid')),
('buildsets',
dict(unique=False,
column_names=['parent_buildid'],
name='parent_buildid')),
('builders_tags',
dict(unique=False, column_names=['tagid'], name='tagid')),
('changes',
dict(unique=False,
column_names=['parent_changeids'],
name='parent_changeids')),
('test_result_sets', {
'name': 'builderid',
'column_names': ['builderid'],
'unique': False,
}),
('test_result_sets', {
'name': 'buildid',
'column_names': ['buildid'],
'unique': False,
}),
('test_result_sets', {
'name': 'stepid',
'column_names': ['stepid'],
'unique': False,
}),
('test_results', {
'name': 'test_result_setid',
'column_names': ['test_result_setid'],
'unique': False,
}),
('test_results', {
'name': 'test_code_pathid',
'column_names': ['test_code_pathid'],
'unique': False,
}),
('test_results', {
'name': 'builderid',
'column_names': ['builderid'],
'unique': False,
}),
('test_results', {
'name': 'test_nameid',
'column_names': ['test_nameid'],
'unique': False,
}),
]
# Migration support
# -----------------
# this is a bit more complicated than might be expected because the first
# seven database versions were once implemented using a homespun migration
# system, and we need to support upgrading masters from that system. The
# old system used a 'version' table, where SQLAlchemy-Migrate uses
# 'migrate_version'
repo_path = util.sibpath(__file__, "migrate")
@defer.inlineCallbacks
def is_current(self):
if ControlledSchema is None:
# this should have been caught earlier by enginestrategy.py with a
# nicer error message
raise ImportError("SQLAlchemy/SQLAlchemy-Migrate version conflict")
def thd(engine):
# we don't even have to look at the old version table - if there's
# no migrate_version, then we're not up to date.
repo = migrate.versioning.repository.Repository(self.repo_path)
repo_version = repo.latest
try:
# migrate.api doesn't let us hand in an engine
schema = ControlledSchema(engine, self.repo_path)
db_version = schema.version
except exceptions.DatabaseNotControlledError:
return False
return db_version == repo_version
ret = yield self.db.pool.do_with_engine(thd)
return ret
# returns a Deferred that returns None
def create(self):
# this is nice and simple, but used only for tests
def thd(engine):
self.metadata.create_all(bind=engine)
return self.db.pool.do_with_engine(thd)
@defer.inlineCallbacks
def upgrade(self):
# here, things are a little tricky. If we have a 'version' table, then
# we need to version_control the database with the proper version
# number, drop 'version', and then upgrade. If we have no 'version'
# table and no 'migrate_version' table, then we need to version_control
# the database. Otherwise, we just need to upgrade it.
def table_exists(engine, tbl):
try:
r = engine.execute("select * from {} limit 1".format(tbl))
r.close()
return True
except Exception:
return False
# http://code.google.com/p/sqlalchemy-migrate/issues/detail?id=100
# means we cannot use the migrate.versioning.api module. So these
# methods perform similar wrapping functions to what is done by the API
# functions, but without disposing of the engine.
def upgrade(engine):
schema = ControlledSchema(engine, self.repo_path)
changeset = schema.changeset(None)
with sautils.withoutSqliteForeignKeys(engine):
for version, change in changeset:
log.msg('migrating schema version {} -> {}'.format(
version, version + 1))
schema.runchange(version, change, 1)
def check_sqlalchemy_migrate_version():
# sqlalchemy-migrate started including a version number in 0.7; we
# support back to 0.6.1, but not 0.6. We'll use some discovered
# differences between 0.6.1 and 0.6 to get that resolution.
version = getattr(migrate, '__version__', 'old')
if version == 'old':
try:
from migrate.versioning import schemadiff
if hasattr(schemadiff, 'ColDiff'):
version = "0.6.1"
else:
version = "0.6"
except Exception:
version = "0.0"
version_tup = tuple(map(int, version.split('-', 1)[0].split('.')))
log.msg("using SQLAlchemy-Migrate version {}".format(version))
if version_tup < (0, 6, 1):
raise RuntimeError(
("You are using SQLAlchemy-Migrate {}. "
"The minimum version is 0.6.1.").format(version))
def version_control(engine, version=None):
ControlledSchema.create(engine, self.repo_path, version)
# the upgrade process must run in a db thread
def thd(engine):
# if the migrate_version table exists, we can just let migrate
# take care of this process.
if table_exists(engine, 'migrate_version'):
r = engine.execute(
"select version from migrate_version limit 1")
old_version = r.scalar()
if old_version < 40:
raise EightUpgradeError()
try:
upgrade(engine)
except sa.exc.NoSuchTableError as e: # pragma: no cover
if 'migration_tmp' in str(e):
log.err(
'A serious error has been encountered during the upgrade. The '
'previous upgrade has been likely interrupted. The database has '
'been damaged and automatic recovery is impossible.'
)
log.err(
'If you believe this is an error, please submit a bug to the '
'Buildbot project.')
raise
# if the version table exists, then we can version_control things
# at that version, drop the version table, and let migrate take
# care of the rest.
elif table_exists(engine, 'version'):
raise EightUpgradeError()
# otherwise, this db is new, so we don't bother using the migration engine
# and just create the tables, and put the version directly to
# latest
else:
# do some tests before getting started
test_unicode(engine)
log.msg("Initializing empty database")
Model.metadata.create_all(engine)
repo = migrate.versioning.repository.Repository(self.repo_path)
version_control(engine, repo.latest)
check_sqlalchemy_migrate_version()
yield self.db.pool.do_with_engine(thd)
def append_index(self, cls, column):
sa.Index(
'_'.join(('ix', column.table.name, column.name)),
column,
postgresql_using='gin'
)
def upgrade(migrate_engine):
meta = sqlalchemy.MetaData()
meta.bind = migrate_engine
raw_template = sqlalchemy.Table(
'raw_template', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('template', types.LongText),
sqlalchemy.Column('files', types.Json),
sqlalchemy.Column('environment', types.Json),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
user_creds = sqlalchemy.Table(
'user_creds', meta,
sqlalchemy.Column('id', sqlalchemy.Integer,
primary_key=True, nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('username', sqlalchemy.String(255)),
sqlalchemy.Column('password', sqlalchemy.String(255)),
sqlalchemy.Column('region_name', sqlalchemy.String(length=255)),
sqlalchemy.Column('decrypt_method', sqlalchemy.String(length=64)),
sqlalchemy.Column('tenant', sqlalchemy.String(1024)),
sqlalchemy.Column('auth_url', sqlalchemy.Text),
sqlalchemy.Column('tenant_id', sqlalchemy.String(256)),
sqlalchemy.Column('trust_id', sqlalchemy.String(255)),
sqlalchemy.Column('trustor_user_id', sqlalchemy.String(64)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
stack = sqlalchemy.Table(
'stack', meta,
sqlalchemy.Column('id', sqlalchemy.String(36),
primary_key=True, nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('deleted_at', sqlalchemy.DateTime),
sqlalchemy.Column('name', sqlalchemy.String(255)),
sqlalchemy.Column('raw_template_id',
sqlalchemy.Integer,
sqlalchemy.ForeignKey('raw_template.id'),
nullable=False),
sqlalchemy.Column('prev_raw_template_id',
sqlalchemy.Integer,
sqlalchemy.ForeignKey('raw_template.id')),
sqlalchemy.Column('user_creds_id', sqlalchemy.Integer,
sqlalchemy.ForeignKey('user_creds.id')),
sqlalchemy.Column('username', sqlalchemy.String(256)),
sqlalchemy.Column('owner_id', sqlalchemy.String(36)),
sqlalchemy.Column('action', sqlalchemy.String(255)),
sqlalchemy.Column('status', sqlalchemy.String(255)),
sqlalchemy.Column('status_reason', types.LongText),
sqlalchemy.Column('timeout', sqlalchemy.Integer),
sqlalchemy.Column('tenant', sqlalchemy.String(256)),
sqlalchemy.Column('disable_rollback', sqlalchemy.Boolean,
nullable=False),
sqlalchemy.Column('stack_user_project_id',
sqlalchemy.String(length=64)),
sqlalchemy.Column('backup', sqlalchemy.Boolean, default=False),
sqlalchemy.Column('nested_depth', sqlalchemy.Integer, default=0),
sqlalchemy.Column('convergence', sqlalchemy.Boolean, default=False),
sqlalchemy.Column('current_traversal', sqlalchemy.String(36)),
sqlalchemy.Column('current_deps', types.Json),
sqlalchemy.Column('parent_resource_name', sqlalchemy.String(255)),
sqlalchemy.Index('ix_stack_name', 'name', mysql_length=255),
sqlalchemy.Index('ix_stack_tenant', 'tenant', mysql_length=255),
sqlalchemy.Index('ix_stack_owner_id', 'owner_id', mysql_length=36),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
resource = sqlalchemy.Table(
'resource', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('uuid', sqlalchemy.String(36), unique=True,
default=lambda: str(uuid.uuid4())),
sqlalchemy.Column('nova_instance', sqlalchemy.String(255)),
sqlalchemy.Column('name', sqlalchemy.String(255)),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('action', sqlalchemy.String(255)),
sqlalchemy.Column('status', sqlalchemy.String(255)),
sqlalchemy.Column('status_reason', types.LongText),
sqlalchemy.Column('stack_id', sqlalchemy.String(36),
sqlalchemy.ForeignKey('stack.id'), nullable=False),
sqlalchemy.Column('rsrc_metadata', types.LongText),
sqlalchemy.Column('properties_data', types.Json),
sqlalchemy.Column('engine_id', sqlalchemy.String(length=36)),
sqlalchemy.Column('atomic_key', sqlalchemy.Integer),
sqlalchemy.Column('needed_by', types.List),
sqlalchemy.Column('requires', types.List),
sqlalchemy.Column('replaces', sqlalchemy.Integer),
sqlalchemy.Column('replaced_by', sqlalchemy.Integer),
sqlalchemy.Column('current_template_id', sqlalchemy.Integer,
sqlalchemy.ForeignKey('raw_template.id')),
sqlalchemy.Column('properties_data_encrypted',
sqlalchemy.Boolean,
default=False),
sqlalchemy.Column('root_stack_id', sqlalchemy.String(36)),
sqlalchemy.Index('ix_resource_root_stack_id',
'root_stack_id',
mysql_length=36),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
resource_data = sqlalchemy.Table(
'resource_data', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('key', sqlalchemy.String(255)),
sqlalchemy.Column('value', sqlalchemy.Text),
sqlalchemy.Column('redact', sqlalchemy.Boolean),
sqlalchemy.Column('decrypt_method', sqlalchemy.String(length=64)),
sqlalchemy.Column('resource_id',
sqlalchemy.Integer,
sqlalchemy.ForeignKey('resource.id'),
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
event = sqlalchemy.Table(
'event', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('uuid', sqlalchemy.String(36),
default=lambda: str(uuid.uuid4()), unique=True),
sqlalchemy.Column('stack_id', sqlalchemy.String(36),
sqlalchemy.ForeignKey('stack.id'), nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('resource_action', sqlalchemy.String(255)),
sqlalchemy.Column('resource_status', sqlalchemy.String(255)),
sqlalchemy.Column('resource_name', sqlalchemy.String(255)),
sqlalchemy.Column('physical_resource_id', sqlalchemy.String(255)),
sqlalchemy.Column('resource_status_reason', sqlalchemy.String(255)),
sqlalchemy.Column('resource_type', sqlalchemy.String(255)),
sqlalchemy.Column('resource_properties', sqlalchemy.PickleType),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
watch_rule = sqlalchemy.Table(
'watch_rule', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('name', sqlalchemy.String(255)),
sqlalchemy.Column('state', sqlalchemy.String(255)),
sqlalchemy.Column('rule', types.LongText),
sqlalchemy.Column('last_evaluated', sqlalchemy.DateTime),
sqlalchemy.Column('stack_id', sqlalchemy.String(36),
sqlalchemy.ForeignKey('stack.id'), nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
watch_data = sqlalchemy.Table(
'watch_data', meta,
sqlalchemy.Column('id', sqlalchemy.Integer, primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('data', types.LongText),
sqlalchemy.Column('watch_rule_id', sqlalchemy.Integer,
sqlalchemy.ForeignKey('watch_rule.id'),
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
stack_lock = sqlalchemy.Table(
'stack_lock', meta,
sqlalchemy.Column('stack_id', sqlalchemy.String(length=36),
sqlalchemy.ForeignKey('stack.id'),
primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('engine_id', sqlalchemy.String(length=36)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
software_config = sqlalchemy.Table(
'software_config', meta,
sqlalchemy.Column('id', sqlalchemy.String(36),
primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('name', sqlalchemy.String(255)),
sqlalchemy.Column('group', sqlalchemy.String(255)),
sqlalchemy.Column('config', types.LongText),
sqlalchemy.Column('tenant', sqlalchemy.String(64),
nullable=False,
index=True),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
software_deployment = sqlalchemy.Table(
'software_deployment', meta,
sqlalchemy.Column('id', sqlalchemy.String(36),
primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime,
index=True),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('server_id', sqlalchemy.String(36),
nullable=False,
index=True),
sqlalchemy.Column('config_id',
sqlalchemy.String(36),
sqlalchemy.ForeignKey('software_config.id'),
nullable=False),
sqlalchemy.Column('input_values', types.Json),
sqlalchemy.Column('output_values', types.Json),
sqlalchemy.Column('action', sqlalchemy.String(255)),
sqlalchemy.Column('status', sqlalchemy.String(255)),
sqlalchemy.Column('status_reason', types.LongText),
sqlalchemy.Column('tenant', sqlalchemy.String(64),
nullable=False,
index=True),
sqlalchemy.Column('stack_user_project_id',
sqlalchemy.String(length=64)),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
snapshot = sqlalchemy.Table(
'snapshot', meta,
sqlalchemy.Column('id', sqlalchemy.String(36),
primary_key=True,
nullable=False),
sqlalchemy.Column('stack_id',
sqlalchemy.String(36),
sqlalchemy.ForeignKey('stack.id'),
nullable=False),
sqlalchemy.Column('name', sqlalchemy.String(255)),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('status', sqlalchemy.String(255)),
sqlalchemy.Column('status_reason', sqlalchemy.String(255)),
sqlalchemy.Column('data', types.Json),
sqlalchemy.Column('tenant', sqlalchemy.String(64),
nullable=False,
index=True),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
service = sqlalchemy.Table(
'service', meta,
sqlalchemy.Column('id', sqlalchemy.String(36), primary_key=True,
default=lambda: str(uuid.uuid4())),
sqlalchemy.Column('engine_id', sqlalchemy.String(36), nullable=False),
sqlalchemy.Column('host', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('hostname', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('binary', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('topic', sqlalchemy.String(255), nullable=False),
sqlalchemy.Column('report_interval', sqlalchemy.Integer,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('deleted_at', sqlalchemy.DateTime),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
stack_tag = sqlalchemy.Table(
'stack_tag', meta,
sqlalchemy.Column('id',
sqlalchemy.Integer,
primary_key=True,
nullable=False),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.Column('tag', sqlalchemy.Unicode(80)),
sqlalchemy.Column('stack_id',
sqlalchemy.String(36),
sqlalchemy.ForeignKey('stack.id'),
nullable=False),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
sync_point = sqlalchemy.Table(
'sync_point', meta,
sqlalchemy.Column('entity_id', sqlalchemy.String(36)),
sqlalchemy.Column('traversal_id', sqlalchemy.String(36)),
sqlalchemy.Column('is_update', sqlalchemy.Boolean),
sqlalchemy.Column('atomic_key', sqlalchemy.Integer,
nullable=False),
sqlalchemy.Column('stack_id', sqlalchemy.String(36),
nullable=False),
sqlalchemy.Column('input_data', types.Json),
sqlalchemy.Column('created_at', sqlalchemy.DateTime),
sqlalchemy.Column('updated_at', sqlalchemy.DateTime),
sqlalchemy.PrimaryKeyConstraint('entity_id',
'traversal_id',
'is_update'),
sqlalchemy.ForeignKeyConstraint(['stack_id'], ['stack.id'],
name='fk_stack_id'),
mysql_engine='InnoDB',
mysql_charset='utf8'
)
tables = (
raw_template,
user_creds,
stack,
resource,
resource_data,
event,
watch_rule,
watch_data,
stack_lock,
software_config,
software_deployment,
snapshot,
service,
stack_tag,
sync_point,
)
for index, table in enumerate(tables):
try:
table.create()
except Exception:
# If an error occurs, drop all tables created so far to return
# to the previously existing state.
meta.drop_all(tables=tables[:index])
raise
backref='ports',
order_by='IPAddress.allocated_at')
@declarative.declared_attr
def security_groups(cls):
primaryjoin = cls.id == port_group_association_table.c.port_id
secondaryjoin = (port_group_association_table.c.group_id ==
SecurityGroup.id)
return orm.relationship(SecurityGroup, primaryjoin=primaryjoin,
secondaryjoin=secondaryjoin,
secondary=port_group_association_table,
backref="ports")
# Indices tailored specifically to get_instance_nw_info calls from nova
sa.Index("idx_ports_1", Port.__table__.c.device_id, Port.__table__.c.tenant_id)
sa.Index("idx_ports_2", Port.__table__.c.device_owner,
Port.__table__.c.network_id)
sa.Index("idx_ports_3", Port.__table__.c.tenant_id)
class MacAddress(BASEV2, models.HasTenant):
__tablename__ = "quark_mac_addresses"
address = sa.Column(sa.BigInteger(), primary_key=True)
mac_address_range_id = sa.Column(
sa.String(36),
sa.ForeignKey("quark_mac_address_ranges.id", ondelete="CASCADE"),
nullable=False)
deallocated = sa.Column(sa.Boolean(), index=True)
deallocated_at = sa.Column(sa.DateTime(), index=True)
orm.relationship(Port, backref="mac_address")
def create_index(s, column, name=None, unique=False):
if name is None:
raise ValueError('SQL indexes must have a name')
sa.Index(name, getattr(s.c, column), unique=unique).create(s.bind)
ret.append(a)
return ret
def add_note(self, short, long=None):
n = Note()
n.short = short
n.long = long
self.notes.append(n)
self.cur_note = n.short
Session.add(n)
Session.add(self)
Session.commit()
return n
sa.Index('addr_uptime', Alert.addr, Alert.uptime, unique=True)
class Host(Base):
__tablename__ = 'hosts'
id = sa.Column(sa.types.Integer, primary_key=True)
addr = sa.Column(sa.types.String(255), nullable=False)
name = sa.Column(sa.types.String(255), nullable=False)
active = sa.Column(sa.types.Boolean, nullable=False, default=True)
alerts = orm.relation('Alert',
backref='host',
lazy='dynamic',
order_by=sa.desc(Alert.time))
