def upgrade():
op.create_table(
"challenge",
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("created",
sa.DateTime,
nullable=False,
server_default=sa.sql.func.now()),
sa.Column("finished", sa.DateTime, nullable=True),
sa.Column("num_games", sa.Integer, default=0, nullable=False),
sa.Column("status",
sa.Enum("created", "playing_game", "finished"),
default="created",
nullable=False),
sa.Column("most_recent_game_task", sa.DateTime, nullable=True),
sa.Column("issuer",
mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=False),
sa.Column("winner",
mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=True),
sa.ForeignKeyConstraint(['issuer'], ['user.id'],
name='challenge_issuer_fk',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['winner'], ['user.id'],
name='challenge_winner_fk',
ondelete='CASCADE'),
)
op.create_table(
"challenge_participant",
sa.Column("challenge_id", sa.Integer, primary_key=True),
sa.Column("user_id",
mysql.MEDIUMINT(display_width=8, unsigned=True),
primary_key=True),
sa.Column("points", sa.Integer(), default=0, nullable=False),
sa.Column("ships_produced", sa.Integer(), default=0, nullable=False),
sa.Column("attacks_made", sa.Integer(), default=0, nullable=False),
sa.ForeignKeyConstraint(['challenge_id'], ['challenge.id'],
name='challenge_participant_fk',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='challenge_participant_ibfk_2',
ondelete='CASCADE'),
)
op.add_column(
"game",
sa.Column("challenge_id", sa.Integer, nullable=True),
)
op.create_foreign_key('game_challenge_fk',
'game',
'challenge', ['challenge_id'], ['id'],
ondelete='CASCADE')
class UserByPointModel(DatetimeMixin, Base):
__tablename__ = "app_user_by_point"
id = Column(Integer, autoincrement=True, primary_key=True)
point = Column(mysql.MEDIUMINT(7, unsigned=True),
index=True,
nullable=False)
user_count = Column(mysql.MEDIUMINT(7, unsigned=True), server_default="0")
match_id = Column(
CHAR(15),
ForeignKey("app_match.match_id",
onupdate="RESTRICT",
ondelete="RESTRICT"),
)
class MyBeatmaps(db.Model, Serializer):
beatmap_id = db.Column(mysql.INTEGER(10, unsigned=True), primary_key=True)
beatmapset_id = db.Column(mysql.INTEGER(10, unsigned=True))
artist = db.Column(mysql.VARCHAR(80))
title = db.Column(mysql.VARCHAR(80))
version = db.Column(mysql.VARCHAR(80))
bpm = db.Column(mysql.FLOAT(unsigned=True))
total_length = db.Column(mysql.MEDIUMINT(11, unsigned=True))
hit_length = db.Column(mysql.MEDIUMINT(11, unsigned=True))
difficulty_rating = db.Column(mysql.FLOAT(unsigned=True))
def serialize(self):
res = Serializer.serialize(self)
return res
class MatchChoiceModel(DatetimeMixin, Base):
__tablename__ = "app_match_choice"
id = Column(Integer, autoincrement=True, primary_key=True)
match_id = Column(
CHAR(15),
ForeignKey("app_match.match_id", onupdate="RESTRICT", ondelete="RESTRICT"),
)
choice = Column(mysql.TINYINT(2, unsigned=True), index=True, server_default="0")
user_count = Column(mysql.MEDIUMINT(7, unsigned=True), server_default="0")
class UserModel(DatetimeMixin, Base):
__tablename__ = "app_user"
user_id = Column(CHAR(32), primary_key=True)
name = Column(VARCHAR(10), index=True, nullable=False)
icon_id = Column(VARCHAR(255), nullable=False)
number = Column(VARCHAR(4), nullable=False)
total_point = Column(mysql.MEDIUMINT(7, unsigned=True), server_default="0")
forcastes = relationship("Forcast", backref="user")
inning_points = relationship("UserInningPoint", backref="user")
def downgrade():
op.drop_constraint('game_stat_ibfk_1', 'game_stat', type_='foreignkey')
op.drop_constraint('game_view_stat_ibfk_1',
'game_view_stat',
type_='foreignkey')
op.drop_constraint('game_bot_stat_ibfk_1',
'game_bot_stat',
type_='foreignkey')
op.drop_constraint('game_participant_ibfk_4',
'game_participant',
type_='foreignkey')
op.alter_column('game',
'id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=True)
op.alter_column('game_stat',
'game_id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=False)
op.alter_column('game_view_stat',
'game_id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=False)
op.alter_column('game_stat',
'game_id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=False)
op.alter_column('game_bot_stat',
'game_id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=False)
op.alter_column('game_participant',
'game_id',
type_=mysql.MEDIUMINT(display_width=8, unsigned=True),
existing_nullable=False,
existing_autoincrement=False)
op.create_foreign_key('game_stat_ibfk_1',
'game_stat',
'game', ['game_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('game_view_stat_ibfk_1',
'game_view_stat',
'game', ['game_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('game_bot_stat_ibfk_1',
'game_bot_stat',
'game', ['game_id'], ['id'],
ondelete='CASCADE')
op.create_foreign_key('game_participant_ibfk_4',
'game_participant',
'game', ['game_id'], ['id'],
ondelete='CASCADE')
def upgrade():
# Autogenerated from existing database, then reformatted and fixed a few
# things that were missed by the autogeneration. It could be further
# cleaned up, but probably not worth it as all future changes should go in
# new revisions.
op.create_table(
'badge',
sa.Column('id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('name', mysql.VARCHAR(length=256), nullable=False),
sa.PrimaryKeyConstraint('id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('name', 'badge', ['name'], unique=True)
op.create_table(
'game',
sa.Column('id', mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=False),
sa.Column('replay_name', mysql.VARCHAR(length=128), nullable=False),
sa.Column('map_width', mysql.SMALLINT(display_width=5),
autoincrement=False, nullable=False),
sa.Column('map_height', mysql.SMALLINT(display_width=5),
autoincrement=False, nullable=False),
sa.Column('map_seed', mysql.INTEGER(display_width=10, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('map_generator', mysql.VARCHAR(length=128), nullable=False),
sa.Column('time_played', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('replay_bucket', mysql.SMALLINT(display_width=5),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.PrimaryKeyConstraint('id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('game_time_played', 'game', ['time_played'], unique=False)
op.create_table(
'halite_1_user',
sa.Column('userID', mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=False),
sa.Column('oauthID', mysql.INTEGER(display_width=12, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('oauthProvider', mysql.TINYINT(display_width=1,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('username', mysql.VARCHAR(length=32), nullable=False),
sa.Column('email', mysql.VARCHAR(length=64), nullable=True),
sa.Column('isRunning', mysql.TINYINT(display_width=1, unsigned=True),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('compileStatus', mysql.TINYINT(display_width=1,
unsigned=True),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('organization', mysql.VARCHAR(length=64), nullable=False),
sa.Column('language', mysql.VARCHAR(length=16), nullable=True),
sa.Column('mu', mysql.FLOAT(), server_default=sa.text("'25'"),
nullable=False),
sa.Column('sigma', mysql.FLOAT(unsigned=True),
server_default=sa.text("'8.333'"), nullable=False),
sa.Column('rank', mysql.SMALLINT(display_width=5), autoincrement=False,
nullable=True),
sa.Column('numSubmissions', mysql.SMALLINT(display_width=5),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('numGames', mysql.SMALLINT(display_width=5),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('creationTime', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'),
nullable=True),
sa.Column('updateTime', mysql.DATETIME(), nullable=True,
server_default=sa.text('NULL ON UPDATE CURRENT_TIMESTAMP')),
sa.Column('onEmailList', mysql.TINYINT(display_width=1),
server_default=sa.text("'1'"), autoincrement=False,
nullable=False),
sa.Column('githubEmail', mysql.VARCHAR(length=64), nullable=True),
sa.Column('verificationCode', mysql.VARCHAR(length=64), nullable=True),
sa.Column('isEmailGood', mysql.TINYINT(display_width=1, unsigned=True),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('level', mysql.ENUM('High School', 'Undergraduate',
'Graduate', 'Professional'),
server_default=sa.text("'Professional'"), nullable=False),
sa.PrimaryKeyConstraint('userID'),
mysql_auto_increment='5350',
mysql_default_charset='latin1',
mysql_engine='InnoDB'
)
op.create_table(
'organization',
sa.Column('id', mysql.INTEGER(display_width=11), nullable=False),
sa.Column('organization_name', mysql.VARCHAR(length=64),
nullable=False),
sa.Column('kind', mysql.ENUM('High School', 'University',
'Professional School', 'Company',
'Other'),
server_default=sa.text("'Other'"), nullable=False),
sa.Column('verification_code', mysql.VARCHAR(length=32),
nullable=True),
sa.PrimaryKeyConstraint('id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('verification_code', 'organization', ['verification_code'],
unique=True)
op.create_table(
'game_stat',
sa.Column('game_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('turns_total', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('planets_destroyed', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('ships_produced', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('ships_destroyed', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['game_id'], ['game.id'],
name='game_stat_ibfk_1', ondelete='CASCADE'),
sa.PrimaryKeyConstraint('game_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'game_view_stat',
sa.Column('game_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('views_total', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['game_id'], ['game.id'],
name='game_view_stat_ibfk_1',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('game_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'hackathon',
sa.Column('id', mysql.INTEGER(display_width=10, unsigned=True),
nullable=False),
sa.Column('title', mysql.VARCHAR(length=256), nullable=False),
sa.Column('description', mysql.VARCHAR(length=4096), nullable=False),
sa.Column('start_date', mysql.DATETIME(), nullable=False),
sa.Column('end_date', mysql.DATETIME(), nullable=False),
sa.Column('verification_code', mysql.VARCHAR(length=32),
nullable=False),
sa.Column('organization_id', mysql.INTEGER(display_width=11),
autoincrement=False, nullable=True),
sa.Column('location', mysql.VARCHAR(length=256), nullable=True),
sa.Column('thumbnail', mysql.VARCHAR(length=512), nullable=True),
sa.Column('is_open', mysql.TINYINT(display_width=4),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
name='hackathon_ibfk_1'),
sa.PrimaryKeyConstraint('id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('organization_id', 'hackathon', ['organization_id'],
unique=False)
op.create_index('verification_code', 'hackathon', ['verification_code'],
unique=True)
op.create_table(
'organization_email_domain',
sa.Column('organization_id', mysql.INTEGER(display_width=11),
autoincrement=False, nullable=False),
sa.Column('domain', mysql.VARCHAR(length=64), nullable=False),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
name='organization_email_domain_ibfk_1'),
sa.PrimaryKeyConstraint('organization_id', 'domain'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'user',
sa.Column('id', mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=False),
sa.Column('oauth_id', mysql.INTEGER(display_width=12, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('oauth_provider', mysql.TINYINT(display_width=1,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('username', mysql.VARCHAR(length=32), nullable=False),
sa.Column('email', mysql.VARCHAR(length=64), nullable=True),
sa.Column('github_email', mysql.VARCHAR(length=64), nullable=True),
sa.Column('verification_code', mysql.VARCHAR(length=64),
nullable=True),
sa.Column('is_active', mysql.TINYINT(display_width=1),
server_default=sa.text("'1'"), autoincrement=False,
nullable=False),
sa.Column('on_email_list', mysql.TINYINT(display_width=1),
server_default=sa.text("'1'"), autoincrement=False,
nullable=False),
sa.Column('is_email_good', mysql.TINYINT(display_width=1),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('player_level', mysql.ENUM('High School', 'University',
'Professional'),
server_default=sa.text("'Professional'"), nullable=False),
sa.Column('organization_id', mysql.INTEGER(display_width=11),
autoincrement=False, nullable=True),
sa.Column('country_code', mysql.VARCHAR(length=3), nullable=True),
sa.Column('country_subdivision_code', mysql.VARCHAR(length=10),
nullable=True),
sa.Column('creation_time', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('update_time', mysql.DATETIME(), nullable=True,
server_default=sa.text('NULL ON UPDATE CURRENT_TIMESTAMP')),
sa.Column('api_key_hash', mysql.VARCHAR(length=255), nullable=True),
sa.Column('is_admin', mysql.TINYINT(display_width=1),
server_default=sa.text("'0'"), autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(['organization_id'], ['organization.id'],
name='user_ibfk_1'),
sa.PrimaryKeyConstraint('id'),
mysql_auto_increment='1000',
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('organization_id', 'user', ['organization_id'],
unique=False)
op.create_table(
'bot',
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('compile_status', mysql.ENUM('Uploaded', 'InProgress',
'Successful', 'Failed',
'Disabled'),
nullable=False),
sa.Column('compile_start', mysql.DATETIME(), nullable=True),
sa.Column('language', mysql.VARCHAR(length=16), nullable=True),
sa.Column('version_number', mysql.SMALLINT(display_width=5),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('games_played', mysql.BIGINT(display_width=20),
server_default=sa.text("'0'"), autoincrement=False,
nullable=False),
sa.Column('mu', mysql.FLOAT(), server_default=sa.text("'25'"),
nullable=False),
sa.Column('sigma', mysql.FLOAT(unsigned=True),
server_default=sa.text("'8.333'"), nullable=False),
sa.Column('score', mysql.FLOAT(), server_default=sa.text("'0'"),
nullable=False),
sa.Column('creation_time', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('update_time', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('timeout_sent', mysql.TINYINT(display_width=1),
server_default=sa.text("'0'"), autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], name='bot_ibfk_2',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'hackathon_participant',
sa.Column('hackathon_id', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['hackathon_id'], ['hackathon.id'],
name='hackathon_participant_ibfk_4',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='hackathon_participant_ibfk_3',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('hackathon_id', 'user_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('user_id', 'hackathon_participant', ['user_id'],
unique=False)
op.create_table(
'user_badge',
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('badge_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('is_enabled', mysql.TINYINT(display_width=1),
server_default=sa.text("'1'"), autoincrement=False,
nullable=False),
sa.Column('creation_time', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('update_time', mysql.DATETIME(), nullable=True,
server_default=sa.text('NULL ON UPDATE CURRENT_TIMESTAMP')),
sa.ForeignKeyConstraint(['badge_id'], ['badge.id'],
name='user_badge_ibfk_2', ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='user_badge_ibfk_1', ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'badge_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('fk_badge_id_idx', 'user_badge', ['badge_id'],
unique=False)
op.create_table(
'user_notification',
sa.Column('id', mysql.MEDIUMINT(display_width=8, unsigned=True),
nullable=False),
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('title', mysql.VARCHAR(length=64), nullable=False),
sa.Column('body', mysql.VARCHAR(length=2048), nullable=False),
sa.Column('mood', mysql.ENUM('error', 'neutral', 'success'),
server_default=sa.text("'neutral'"), nullable=False),
sa.Column('creation_time', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='user_notification_ibfk_2',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('user_id', 'user_notification', ['user_id'], unique=False)
op.create_table(
'user_tier_history',
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('tier', mysql.VARCHAR(length=256), nullable=False),
sa.Column('last_in_tier', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.Column('total_time_in_tier', mysql.INTEGER(display_width=10,
unsigned=True),
server_default=sa.text("'0'"), autoincrement=False,
nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='user_tier_history_ibfk_2',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'tier'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'bot_history',
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('bot_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('version_number', mysql.SMALLINT(display_width=5),
autoincrement=False, nullable=False),
sa.Column('last_rank', mysql.SMALLINT(display_width=5),
autoincrement=False, nullable=False),
sa.Column('last_score', mysql.FLOAT(), nullable=False),
sa.Column('last_num_players', mysql.SMALLINT(display_width=5),
autoincrement=False, nullable=False),
sa.Column('last_games_played', mysql.BIGINT(display_width=20),
autoincrement=False, nullable=True),
sa.Column('language', mysql.VARCHAR(length=16), nullable=False),
sa.Column('when_retired', mysql.DATETIME(),
server_default=sa.text('CURRENT_TIMESTAMP'), nullable=True),
sa.ForeignKeyConstraint(['user_id', 'bot_id'], ['bot.user_id',
'bot.id'],
name='bot_history_ibfk_4', ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='bot_history_ibfk_3', ondelete='CASCADE'),
sa.PrimaryKeyConstraint('user_id', 'bot_id', 'version_number'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_table(
'game_bot_stat',
sa.Column('game_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('bot_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('planets_controlled', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('ships_produced', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('ships_alive', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('ships_alive_ratio', mysql.FLOAT(), nullable=False),
sa.Column('ships_relative_ratio', mysql.FLOAT(), nullable=False),
sa.Column('planets_destroyed', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('attacks_total', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['game_id'], ['game.id'],
name='game_bot_stat_ibfk_1',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id', 'bot_id'],
['bot.user_id', 'bot.id'],
name='fkcompid', ondelete='NO ACTION',
onupdate='NO ACTION'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='fkuserid', ondelete='NO ACTION',
onupdate='NO ACTION'),
sa.PrimaryKeyConstraint('game_id', 'user_id', 'bot_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('fkcompid_idx', 'game_bot_stat', ['user_id', 'bot_id'],
unique=False)
op.create_table(
'game_participant',
sa.Column('game_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('bot_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('version_number', mysql.MEDIUMINT(display_width=8,
unsigned=True), autoincrement=False, nullable=False),
sa.Column('log_name', mysql.VARCHAR(length=64), nullable=True),
sa.Column('rank', mysql.SMALLINT(display_width=5, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('player_index', mysql.SMALLINT(display_width=5,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('timed_out', mysql.TINYINT(display_width=1),
autoincrement=False, nullable=False),
sa.ForeignKeyConstraint(['game_id'], ['game.id'],
name='game_participant_ibfk_4',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id', 'bot_id'],
['bot.user_id', 'bot.id'],
name='game_participant_ibfk_3'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='game_participant_ibfk_2'),
sa.PrimaryKeyConstraint('game_id', 'user_id', 'bot_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('user_id', 'game_participant', ['user_id', 'bot_id'],
unique=False)
op.create_table(
'hackathon_snapshot',
sa.Column('hackathon_id', mysql.INTEGER(display_width=10,
unsigned=True),
autoincrement=False, nullable=False),
sa.Column('user_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('bot_id', mysql.MEDIUMINT(display_width=8, unsigned=True),
autoincrement=False, nullable=False),
sa.Column('games_played', mysql.INTEGER(display_width=10,
unsigned=True),
server_default=sa.text("'0'"), autoincrement=False,
nullable=True),
sa.Column('score', mysql.FLOAT(), nullable=False),
sa.Column('mu', mysql.FLOAT(), nullable=False),
sa.Column('sigma', mysql.FLOAT(), nullable=False),
sa.Column('version_number', mysql.INTEGER(display_width=11),
autoincrement=False, nullable=True),
sa.Column('language', mysql.VARCHAR(length=16), nullable=True),
sa.ForeignKeyConstraint(['hackathon_id'], ['hackathon.id'],
name='hackathon_snapshot_ibfk_6',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id', 'bot_id'],
['bot.user_id', 'bot.id'],
name='hackathon_snapshot_ibfk_5',
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['user_id'], ['user.id'],
name='hackathon_snapshot_ibfk_4',
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('hackathon_id', 'user_id', 'bot_id'),
mysql_default_charset='utf8',
mysql_engine='InnoDB'
)
op.create_index('user_id', 'hackathon_snapshot', ['user_id', 'bot_id'],
unique=False)
class TestEndToEnd(object):
timeout_seconds = 60
@pytest.fixture
def table_name(self, replhandler):
return '{0}_biz'.format(replhandler)
@pytest.fixture
def avro_schema(self, table_name):
return {
u'fields': [{
u'type': u'int',
u'name': u'id',
u'pkey': 1
}, {
u'default': None,
u'maxlen': 64,
u'type': [u'null', u'string'],
u'name': u'name'
}],
u'namespace':
u'',
u'name':
table_name,
u'type':
u'record',
u'pkey': [u'id']
}
@pytest.fixture(params=[{
'table_name':
'test_complex_table',
'test_schema': [
# test_bit
# ColumnInfo('BIT(8)', mysql.BIT, 3),
# test_tinyint
ColumnInfo('TINYINT', mysql.TINYINT(), 127),
ColumnInfo('TINYINT(3) SIGNED',
mysql.TINYINT(display_width=3, unsigned=False), -128),
ColumnInfo('TINYINT(3) UNSIGNED',
mysql.TINYINT(display_width=3, unsigned=True), 255),
ColumnInfo(
'TINYINT(3) UNSIGNED ZEROFILL',
mysql.TINYINT(display_width=3, unsigned=True, zerofill=True),
5),
ColumnInfo('BOOL', mysql.BOOLEAN(), 1),
ColumnInfo('BOOLEAN', mysql.BOOLEAN(), 1),
# test_smallint
ColumnInfo('SMALLINT', mysql.SMALLINT(), 32767),
ColumnInfo('SMALLINT(5) SIGNED',
mysql.SMALLINT(display_width=5, unsigned=False),
-32768),
ColumnInfo('SMALLINT(5) UNSIGNED',
mysql.SMALLINT(display_width=5, unsigned=True), 65535),
ColumnInfo(
'SMALLINT(3) UNSIGNED ZEROFILL',
mysql.SMALLINT(display_width=3, unsigned=True, zerofill=True),
5),
# test_mediumint
ColumnInfo('MEDIUMINT', mysql.MEDIUMINT(), 8388607),
ColumnInfo('MEDIUMINT(7) SIGNED',
mysql.MEDIUMINT(display_width=7, unsigned=False),
-8388608),
ColumnInfo('MEDIUMINT(8) UNSIGNED',
mysql.MEDIUMINT(display_width=8, unsigned=True),
16777215),
ColumnInfo(
'MEDIUMINT(3) UNSIGNED ZEROFILL',
mysql.MEDIUMINT(display_width=3, unsigned=True, zerofill=True),
5),
# test_int
ColumnInfo('INT', mysql.INTEGER(), 2147483647),
ColumnInfo('INT(10) SIGNED',
mysql.INTEGER(display_width=10, unsigned=False),
-2147483648),
ColumnInfo('INT(11) UNSIGNED',
mysql.INTEGER(display_width=11, unsigned=True),
4294967295),
ColumnInfo(
'INT(3) UNSIGNED ZEROFILL',
mysql.INTEGER(display_width=3, unsigned=True, zerofill=True),
5),
ColumnInfo('INTEGER(3)', mysql.INTEGER(display_width=3), 3),
# test_bigint
ColumnInfo('BIGINT(19)', mysql.BIGINT(display_width=19),
23372854775807),
ColumnInfo('BIGINT(19) SIGNED',
mysql.BIGINT(display_width=19, unsigned=False),
-9223372036854775808),
# ColumnInfo('BIGINT(20) UNSIGNED', mysql.INTEGER(display_width=20, unsigned=True), 18446744073709551615),
ColumnInfo(
'BIGINT(3) UNSIGNED ZEROFILL',
mysql.BIGINT(display_width=3, unsigned=True, zerofill=True),
5),
# test_decimal
ColumnInfo('DECIMAL(9, 2)', mysql.DECIMAL(precision=9, scale=2),
101.41),
ColumnInfo('DECIMAL(12, 11) SIGNED',
mysql.DECIMAL(precision=12, scale=11, unsigned=False),
-3.14159265359),
ColumnInfo('DECIMAL(2, 1) UNSIGNED',
mysql.DECIMAL(precision=2, scale=1, unsigned=True),
0.0),
ColumnInfo(
'DECIMAL(9, 2) UNSIGNED ZEROFILL',
mysql.DECIMAL(precision=9,
scale=2,
unsigned=True,
zerofill=True), 5.22),
ColumnInfo('DEC(9, 3)', mysql.DECIMAL(precision=9, scale=3),
5.432),
ColumnInfo('FIXED(9, 3)', mysql.DECIMAL(precision=9, scale=3),
45.432),
# test_float
ColumnInfo('FLOAT', mysql.FLOAT(), 3.14),
ColumnInfo('FLOAT(5, 3) SIGNED',
mysql.FLOAT(precision=5, scale=3, unsigned=False),
-2.14),
ColumnInfo('FLOAT(5, 3) UNSIGNED',
mysql.FLOAT(precision=5, scale=3, unsigned=True), 2.14),
ColumnInfo(
'FLOAT(5, 3) UNSIGNED ZEROFILL',
mysql.FLOAT(precision=5, scale=3, unsigned=True,
zerofill=True), 24.00),
ColumnInfo('FLOAT(5)', mysql.FLOAT(5), 24.01),
ColumnInfo('FLOAT(30)', mysql.FLOAT(30), 24.01),
# test_double
ColumnInfo('DOUBLE', mysql.DOUBLE(), 3.14),
ColumnInfo('DOUBLE(5, 3) SIGNED',
mysql.DOUBLE(precision=5, scale=3, unsigned=False),
-3.14),
ColumnInfo('DOUBLE(5, 3) UNSIGNED',
mysql.DOUBLE(precision=5, scale=3, unsigned=True),
2.14),
ColumnInfo(
'DOUBLE(5, 3) UNSIGNED ZEROFILL',
mysql.DOUBLE(precision=5,
scale=3,
unsigned=True,
zerofill=True), 24.00),
ColumnInfo('DOUBLE PRECISION', mysql.DOUBLE(), 3.14),
ColumnInfo('REAL', mysql.DOUBLE(), 3.14),
# test_date_time
ColumnInfo('DATE', mysql.DATE(), datetime.date(1901, 1, 1)),
ColumnInfo('DATE', mysql.DATE(), datetime.date(2050, 12, 31)),
ColumnInfo('DATETIME', mysql.DATETIME(),
datetime.datetime(1970, 1, 1, 0, 0, 1, 0)),
ColumnInfo('DATETIME', mysql.DATETIME(),
datetime.datetime(2038, 1, 19, 3, 14, 7, 0)),
ColumnInfo('DATETIME(6)', mysql.DATETIME(fsp=6),
datetime.datetime(1970, 1, 1, 0, 0, 1, 111111)),
ColumnInfo('DATETIME(6)', mysql.DATETIME(fsp=6),
datetime.datetime(2038, 1, 19, 3, 14, 7, 999999)),
ColumnInfo('TIMESTAMP', mysql.TIMESTAMP(),
datetime.datetime(1970, 1, 1, 0, 0, 1, 0)),
ColumnInfo('TIMESTAMP', mysql.TIMESTAMP(),
datetime.datetime(2038, 1, 19, 3, 14, 7, 0)),
ColumnInfo('TIMESTAMP(6)', mysql.TIMESTAMP(fsp=6),
datetime.datetime(1970, 1, 1, 0, 0, 1, 111111)),
ColumnInfo('TIMESTAMP(6)', mysql.TIMESTAMP(fsp=6),
datetime.datetime(2038, 1, 19, 3, 14, 7, 999999)),
ColumnInfo('TIME', mysql.TIME(), datetime.timedelta(0, 0, 0)),
ColumnInfo('TIME', mysql.TIME(),
datetime.timedelta(0, 23 * 3600 + 59 * 60 + 59, 0)),
ColumnInfo('TIME(6)', mysql.TIME(fsp=6),
datetime.timedelta(0, 0, 111111)),
ColumnInfo('TIME(6)', mysql.TIME(fsp=6),
datetime.timedelta(0, 23 * 3600 + 59 * 60 + 59,
999999)),
ColumnInfo('YEAR', mysql.YEAR(), 2000),
ColumnInfo('YEAR(4)', mysql.YEAR(display_width=4), 2000),
# test_char
ColumnInfo('CHAR', mysql.CHAR(), 'a'),
ColumnInfo('CHARACTER', mysql.CHAR(), 'a'),
ColumnInfo('NATIONAL CHAR', mysql.CHAR(), 'a'),
ColumnInfo('NCHAR', mysql.CHAR(), 'a'),
ColumnInfo('CHAR(0)', mysql.CHAR(length=0), ''),
ColumnInfo('CHAR(10)', mysql.CHAR(length=10), '1234567890'),
ColumnInfo('VARCHAR(1000)', mysql.VARCHAR(length=1000), 'asdasdd'),
ColumnInfo('CHARACTER VARYING(1000)', mysql.VARCHAR(length=1000),
'test dsafnskdf j'),
ColumnInfo('NATIONAL VARCHAR(1000)', mysql.VARCHAR(length=1000),
'asdkjasd'),
ColumnInfo('NVARCHAR(1000)', mysql.VARCHAR(length=1000),
'asdkjasd'),
ColumnInfo('VARCHAR(10000)', mysql.VARCHAR(length=10000),
'1234567890'),
# test_binary
ColumnInfo('BINARY(5)', mysql.BINARY(length=5), 'hello'),
ColumnInfo('VARBINARY(100)', mysql.VARBINARY(length=100), 'hello'),
ColumnInfo('TINYBLOB', mysql.TINYBLOB(), 'hello'),
ColumnInfo('TINYTEXT', mysql.TINYTEXT(), 'hello'),
ColumnInfo('BLOB', mysql.BLOB(), 'hello'),
ColumnInfo('BLOB(100)', mysql.BLOB(length=100), 'hello'),
ColumnInfo('TEXT', mysql.TEXT(), 'hello'),
ColumnInfo('TEXT(100)', mysql.TEXT(length=100), 'hello'),
ColumnInfo('MEDIUMBLOB', mysql.MEDIUMBLOB(), 'hello'),
ColumnInfo('MEDIUMTEXT', mysql.MEDIUMTEXT(), 'hello'),
ColumnInfo('LONGBLOB', mysql.LONGBLOB(), 'hello'),
ColumnInfo('LONGTEXT', mysql.LONGTEXT(), 'hello'),
# test_enum
ColumnInfo("ENUM('ONE', 'TWO')", mysql.ENUM(['ONE', 'TWO']),
'ONE'),
# test_set
ColumnInfo("SET('ONE', 'TWO')", mysql.SET(['ONE', 'TWO']),
set(['ONE', 'TWO']))
]
}])
def complex_table(self, request):
return request.param
@pytest.fixture
def complex_table_name(self, replhandler, complex_table):
return "{0}_{1}".format(replhandler, complex_table['table_name'])
@pytest.fixture
def complex_table_schema(self, complex_table):
return complex_table['test_schema']
def _build_sql_column_name(self, complex_column_name):
return 'test_{}'.format(complex_column_name)
def _build_complex_column_create_query(self, complex_column_name,
complex_column_schema):
return '`{0}` {1}'.format(complex_column_name, complex_column_schema)
@pytest.fixture
def complex_table_create_query(self, complex_table_schema):
return ", ".join([
self._build_complex_column_create_query(
self._build_sql_column_name(indx), complex_column_schema.type)
for indx, complex_column_schema in enumerate(complex_table_schema)
])
@pytest.fixture
def sqla_objs(self, complex_table_schema):
return [
complex_column_schema.sqla_obj
for complex_column_schema in complex_table_schema
]
@pytest.fixture
def create_complex_table(self, containers, rbrsource, complex_table_name,
complex_table_create_query):
if complex_table_create_query.strip():
complex_table_create_query = ", {}".format(
complex_table_create_query)
query = """CREATE TABLE {complex_table_name}
(
`id` int(11) NOT NULL PRIMARY KEY
{complex_table_create_query}
) ENGINE=InnoDB DEFAULT CHARSET=utf8
""".format(complex_table_name=complex_table_name,
complex_table_create_query=complex_table_create_query)
execute_query_get_one_row(containers, rbrsource, query)
@pytest.fixture
def ComplexModel(self, complex_table_name, create_complex_table,
complex_table_schema):
class Model(Base):
__tablename__ = complex_table_name
id = Column('id', Integer, primary_key=True)
for indx, complex_column_schema in enumerate(complex_table_schema):
col_name = self._build_sql_column_name(indx)
setattr(Model, col_name,
Column(col_name, complex_column_schema.sqla_obj))
return Model
@pytest.fixture
def actual_complex_data(self, complex_table_schema):
res = {'id': 1}
for indx, complex_column_schema in enumerate(complex_table_schema):
if isinstance(complex_column_schema.sqla_obj, mysql.DATE):
data = complex_column_schema.data.strftime('%Y-%m-%d')
elif isinstance(complex_column_schema.sqla_obj, mysql.DATETIME):
data = complex_column_schema.data.strftime(
'%Y-%m-%d %H:%M:%S.%f')
elif isinstance(complex_column_schema.sqla_obj, mysql.TIMESTAMP):
data = complex_column_schema.data.strftime(
'%Y-%m-%d %H:%M:%S.%f')
elif isinstance(complex_column_schema.sqla_obj, mysql.TIME):
time = datetime.time(
complex_column_schema.data.seconds / 3600,
(complex_column_schema.data.seconds / 60) % 60,
complex_column_schema.data.seconds % 60,
complex_column_schema.data.microseconds)
data = time.strftime('%H:%M:%S.%f')
else:
data = complex_column_schema.data
res.update({self._build_sql_column_name(indx): data})
return res
@pytest.fixture
def expected_complex_data(self, actual_complex_data, complex_table_schema):
expected_complex_data_dict = {'id': 1}
for indx, complex_column_schema in enumerate(complex_table_schema):
column_name = self._build_sql_column_name(indx)
if isinstance(complex_column_schema.sqla_obj, mysql.SET):
expected_complex_data_dict[column_name] = \
sorted(actual_complex_data[column_name])
elif isinstance(complex_column_schema.sqla_obj, mysql.DATETIME):
date_time_obj = \
complex_column_schema.data.isoformat()
expected_complex_data_dict[column_name] = date_time_obj
elif isinstance(complex_column_schema.sqla_obj, mysql.TIMESTAMP):
date_time_obj = \
complex_column_schema.data.replace(tzinfo=pytz.utc)
expected_complex_data_dict[column_name] = date_time_obj
elif isinstance(complex_column_schema.sqla_obj, mysql.TIME):
number_of_micros = transform_timedelta_to_number_of_microseconds(
complex_column_schema.data)
expected_complex_data_dict[column_name] = number_of_micros
else:
expected_complex_data_dict[column_name] = \
complex_column_schema.data
return expected_complex_data_dict
def test_complex_table(self, containers, rbrsource, complex_table_name,
ComplexModel, actual_complex_data,
expected_complex_data, schematizer, namespace,
rbr_source_session, gtid_enabled):
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
complex_instance = ComplexModel(**actual_complex_data)
rbr_source_session.add(complex_instance)
rbr_source_session.commit()
messages = _fetch_messages(containers, schematizer, namespace,
complex_table_name, 1)
expected_messages = [
{
'message_type': MessageType.create,
'payload_data': expected_complex_data
},
]
_verify_messages(messages, expected_messages)
def test_create_table(self, containers, rbrsource, schematracker,
create_table_query, avro_schema, table_name,
namespace, schematizer, rbr_source_session,
gtid_enabled):
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
execute_query_get_one_row(
containers, rbrsource,
create_table_query.format(table_name=table_name))
# Need to poll for the creation of the table
_wait_for_table(containers, schematracker, table_name)
# Check the schematracker db also has the table.
verify_create_table_query = "SHOW CREATE TABLE {table_name}".format(
table_name=table_name)
verify_create_table_result = execute_query_get_one_row(
containers, schematracker, verify_create_table_query)
expected_create_table_result = execute_query_get_one_row(
containers, rbrsource, verify_create_table_query)
self.assert_expected_result(verify_create_table_result,
expected_create_table_result)
# It's necessary to insert data for the topic to actually be created.
Biz = _generate_basic_model(table_name)
rbr_source_session.add(Biz(id=1, name='insert'))
rbr_source_session.commit()
_wait_for_schematizer_topic(schematizer, namespace, table_name)
# Check schematizer.
self.check_schematizer_has_correct_source_info(table_name=table_name,
avro_schema=avro_schema,
namespace=namespace,
schematizer=schematizer)
def test_create_table_with_row_format(self, containers, rbrsource,
schematracker, replhandler,
gtid_enabled):
table_name = '{0}_row_format_tester'.format(replhandler)
create_table_stmt = """
CREATE TABLE {name}
( id int(11) primary key) ROW_FORMAT=COMPRESSED ENGINE=InnoDB
""".format(name=table_name)
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
execute_query_get_one_row(containers, rbrsource, create_table_stmt)
_wait_for_table(containers, schematracker, table_name)
# Check the schematracker db also has the table.
verify_create_table_query = "SHOW CREATE TABLE {table_name}".format(
table_name=table_name)
verify_create_table_result = execute_query_get_one_row(
containers, schematracker, verify_create_table_query)
expected_create_table_result = execute_query_get_one_row(
containers, rbrsource, verify_create_table_query)
self.assert_expected_result(verify_create_table_result,
expected_create_table_result)
def test_alter_table(self, containers, rbrsource, schematracker,
alter_table_query, table_name, gtid_enabled):
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
execute_query_get_one_row(
containers, rbrsource,
alter_table_query.format(table_name=table_name))
execute_query_get_one_row(
containers, rbrsource,
"ALTER TABLE {name} ROW_FORMAT=COMPRESSED".format(name=table_name))
time.sleep(2)
# Check the schematracker db also has the table.
verify_describe_table_query = "DESCRIBE {table_name}".format(
table_name=table_name)
verify_alter_table_result = execute_query_get_all_rows(
containers, schematracker, verify_describe_table_query)
expected_alter_table_result = execute_query_get_all_rows(
containers, rbrsource, verify_describe_table_query)
if 'address' in verify_alter_table_result[0].values():
actual_result = verify_alter_table_result[0]
elif 'address' in verify_alter_table_result[1].values():
actual_result = verify_alter_table_result[1]
else:
raise AssertionError('The alter table query did not succeed')
if 'address' in expected_alter_table_result[0].values():
expected_result = expected_alter_table_result[0]
else:
expected_result = expected_alter_table_result[1]
self.assert_expected_result(actual_result, expected_result)
def test_basic_table(self, containers, replhandler, rbrsource,
create_table_query, namespace, schematizer,
rbr_source_session, gtid_enabled):
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
source = "{0}_basic_table".format(replhandler)
execute_query_get_one_row(containers, rbrsource,
create_table_query.format(table_name=source))
BasicModel = _generate_basic_model(source)
model_1 = BasicModel(id=1, name='insert')
model_2 = BasicModel(id=2, name='insert')
rbr_source_session.add(model_1)
rbr_source_session.add(model_2)
rbr_source_session.commit()
model_1.name = 'update'
rbr_source_session.delete(model_2)
rbr_source_session.commit()
messages = _fetch_messages(containers, schematizer, namespace, source,
4)
expected_messages = [
{
'message_type': MessageType.create,
'payload_data': {
'id': 1,
'name': 'insert'
}
},
{
'message_type': MessageType.create,
'payload_data': {
'id': 2,
'name': 'insert'
}
},
{
'message_type': MessageType.update,
'payload_data': {
'id': 1,
'name': 'update'
},
'previous_payload_data': {
'id': 1,
'name': 'insert'
}
},
{
'message_type': MessageType.delete,
'payload_data': {
'id': 2,
'name': 'insert'
}
},
]
_verify_messages(messages, expected_messages)
def test_table_with_contains_pii(self, containers, replhandler, rbrsource,
create_table_query, namespace,
schematizer, rbr_source_session,
gtid_enabled):
with reconfigure(encryption_type='AES_MODE_CBC-1',
key_location='acceptance/configs/data_pipeline/'):
if not gtid_enabled:
increment_heartbeat(containers, rbrsource)
source = "{}_secret_table".format(replhandler)
execute_query_get_one_row(
containers, rbrsource,
create_table_query.format(table_name=source))
BasicModel = _generate_basic_model(source)
model_1 = BasicModel(id=1, name='insert')
model_2 = BasicModel(id=2, name='insert')
rbr_source_session.add(model_1)
rbr_source_session.add(model_2)
rbr_source_session.commit()
messages = _fetch_messages(containers, schematizer, namespace,
source, 2)
expected_messages = [{
'message_type': MessageType.create,
'payload_data': {
'id': 1,
'name': 'insert'
}
}, {
'message_type': MessageType.create,
'payload_data': {
'id': 2,
'name': 'insert'
}
}]
_verify_messages(messages, expected_messages)
def check_schematizer_has_correct_source_info(self, table_name,
avro_schema, namespace,
schematizer):
sources = schematizer.get_sources_by_namespace(namespace)
source = next(src for src in reversed(sources)
if src.name == table_name)
topic = schematizer.get_topics_by_source_id(source.source_id)[-1]
schema = schematizer.get_latest_schema_by_topic_name(topic.name)
assert schema.topic.source.name == table_name
assert schema.topic.source.namespace.name == namespace
assert schema.schema_json == avro_schema
def assert_expected_result(self, result, expected):
for key, value in expected.iteritems():
assert result[key] == value
class UserByDatetimeModel(DatetimeMixin, Base):
__tablename__ = "app_user_by_datetime"
aggregated_at = Column(DateTime, primary_key=True)
user_count = Column(mysql.MEDIUMINT(7, unsigned=True), server_default="0")
def upgrade():
# ### commands auto generated by Alembic - please adjust! ###
op.create_table(
'greylisting',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('priority',
mysql.TINYINT(display_width=2),
server_default='0',
nullable=False),
sa.Column('sender',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('sender_priority',
mysql.TINYINT(display_width=2),
server_default='0',
nullable=False),
sa.Column('comment',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('active', sa.Boolean(), server_default='1', nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_greylisting_account'),
'greylisting', ['account'],
unique=False)
op.create_index(op.f('ix_greylisting_comment'),
'greylisting', ['comment'],
unique=False)
op.create_index(op.f('ix_greylisting_created_at'),
'greylisting', ['created_at'],
unique=False)
op.create_index(op.f('ix_greylisting_sender'),
'greylisting', ['sender'],
unique=False)
op.create_index(op.f('ix_greylisting_updated_at'),
'greylisting', ['updated_at'],
unique=False)
op.create_table(
'greylisting_tracking',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('sender', sa.String(length=191), nullable=False),
sa.Column('recipient', sa.String(length=191), nullable=False),
sa.Column('client_address', sa.String(length=40), nullable=False),
sa.Column('sender_domain',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('rcpt_domain',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('init_time',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('block_expired',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('record_expired',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('blocked_count',
sa.BigInteger(),
server_default='0',
nullable=False),
sa.Column('passed', sa.Boolean(), server_default='0', nullable=False),
sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_greylisting_tracking_client_address'),
'greylisting_tracking', ['client_address'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_created_at'),
'greylisting_tracking', ['created_at'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_rcpt_domain'),
'greylisting_tracking', ['rcpt_domain'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_recipient'),
'greylisting_tracking', ['recipient'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_sender'),
'greylisting_tracking', ['sender'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_sender_domain'),
'greylisting_tracking', ['sender_domain'],
unique=False)
op.create_index(op.f('ix_greylisting_tracking_updated_at'),
'greylisting_tracking', ['updated_at'],
unique=False)
op.create_table(
'greylisting_whitelist_domain',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('domain',
sa.String(length=191),
server_default='',
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_greylisting_whitelist_domain_created_at'),
'greylisting_whitelist_domain', ['created_at'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_domain'),
'greylisting_whitelist_domain', ['domain'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_updated_at'),
'greylisting_whitelist_domain', ['updated_at'],
unique=False)
op.create_table(
'greylisting_whitelist_domain_spf',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('sender',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('comment',
sa.String(length=191),
server_default='',
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_greylisting_whitelist_domain_spf_account'),
'greylisting_whitelist_domain_spf', ['account'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_spf_comment'),
'greylisting_whitelist_domain_spf', ['comment'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_spf_created_at'),
'greylisting_whitelist_domain_spf', ['created_at'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_spf_sender'),
'greylisting_whitelist_domain_spf', ['sender'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelist_domain_spf_updated_at'),
'greylisting_whitelist_domain_spf', ['updated_at'],
unique=False)
op.create_table(
'greylisting_whitelists',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('sender',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('comment',
sa.String(length=191),
server_default='',
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_greylisting_whitelists_account'),
'greylisting_whitelists', ['account'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelists_comment'),
'greylisting_whitelists', ['comment'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelists_created_at'),
'greylisting_whitelists', ['created_at'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelists_sender'),
'greylisting_whitelists', ['sender'],
unique=False)
op.create_index(op.f('ix_greylisting_whitelists_updated_at'),
'greylisting_whitelists', ['updated_at'],
unique=False)
op.create_table(
'throttle',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('account',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('kind',
sa.String(length=10),
server_default='outbound',
nullable=False),
sa.Column('priority',
mysql.TINYINT(display_width=1, unsigned=True),
server_default='0',
nullable=False),
sa.Column('period',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('msg_size',
sa.BigInteger(),
server_default='-1',
nullable=False),
sa.Column('max_msgs',
sa.BigInteger(),
server_default='-1',
nullable=False),
sa.Column('max_quota',
sa.BigInteger(),
server_default='-1',
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_throttle_account'),
'throttle', ['account'],
unique=False)
op.create_index(op.f('ix_throttle_created_at'),
'throttle', ['created_at'],
unique=False)
op.create_index(op.f('ix_throttle_kind'),
'throttle', ['kind'],
unique=False)
op.create_index(op.f('ix_throttle_updated_at'),
'throttle', ['updated_at'],
unique=False)
op.create_table(
'throttle_tracking',
sa.Column('created_at',
sa.DateTime(),
server_default=sa.text(u'now()'),
nullable=True),
sa.Column('updated_at', sa.DateTime(), nullable=True),
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('tid',
mysql.BIGINT(display_width=20, unsigned=True),
server_default='0',
nullable=False),
sa.Column('account',
sa.String(length=191),
server_default='',
nullable=False),
sa.Column('period',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('cur_msgs',
mysql.MEDIUMINT(display_width=8, unsigned=True),
server_default='0',
nullable=False),
sa.Column('cur_quota',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('init_time',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False),
sa.Column('last_time',
mysql.INTEGER(display_width=10, unsigned=True),
server_default='0',
nullable=False), sa.PrimaryKeyConstraint('id'))
op.create_index(op.f('ix_throttle_tracking_account'),
'throttle_tracking', ['account'],
unique=False)
op.create_index(op.f('ix_throttle_tracking_created_at'),
'throttle_tracking', ['created_at'],
unique=False)
op.create_index(op.f('ix_throttle_tracking_tid'),
'throttle_tracking', ['tid'],
unique=False)
op.create_index(op.f('ix_throttle_tracking_updated_at'),
'throttle_tracking', ['updated_at'],
unique=False)
