Exemplo n.º 1
0
def _downgrade_v3(op):
    """
    Downgrade assets db by adding a not null constraint on
    ``equities.first_traded``
    """
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ),
        sa.Column('symbol', sa.Text),
        sa.Column('company_symbol', sa.Text),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text),
        sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer, nullable=False),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text),
    )
    op.execute(
        """
        insert into _new_equities
        select * from equities
        where equities.first_traded is not null
        """,
    )
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indicies have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )
def _downgrade_v3(op):
    """
    Downgrade assets db by adding a not null constraint on
    ``equities.first_traded``
    """
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ),
        sa.Column('symbol', sa.Text),
        sa.Column('company_symbol', sa.Text),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text),
        sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer, nullable=False),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text),
    )
    op.execute(
        """
        insert into _new_equities
        select * from equities
        where equities.first_traded is not null
        """, )
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indices have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )
def _downgrade_v5(op):
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ), sa.Column('symbol', sa.Text), sa.Column('company_symbol', sa.Text),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text), sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text), sa.Column('exchange_full', sa.Text))

    op.execute(
        """
        insert into _new_equities
        select
            equities.sid as sid,
            sym.symbol as symbol,
            sym.company_symbol as company_symbol,
            sym.share_class_symbol as share_class_symbol,
            sym.company_symbol || sym.share_class_symbol as fuzzy_symbol,
            equities.asset_name as asset_name,
            equities.start_date as start_date,
            equities.end_date as end_date,
            equities.first_traded as first_traded,
            equities.auto_close_date as auto_close_date,
            equities.exchange as exchange,
            equities.exchange_full as exchange_full
        from
            equities
        inner join
            -- Nested select here to take the most recently held ticker
            -- for each sid. The group by with no aggregation function will
            -- take the last element in the group, so we first order by
            -- the end date ascending to ensure that the groupby takes
            -- the last ticker.
            (select
                 *
             from
                 (select
                      *
                  from
                      equity_symbol_mappings
                  order by
                      equity_symbol_mappings.end_date asc)
             group by
                 sid) sym
        on
            equities.sid == sym.sid
        """, )
    op.drop_table('equity_symbol_mappings')
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indicies have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )
Exemplo n.º 4
0
def _downgrade_v4(op):
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ),
        sa.Column('symbol', sa.Text),
        sa.Column('company_symbol', sa.Text, index=True),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text, index=True),
        sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text),
    )

    op.execute(
        """
        insert into _new_equities
        select
            equities.sid as sid,
            sym.symbol as symbol,
            sym.company_symbol as company_symbol,
            sym.share_class_symbol as share_class_symbol,
            sym.company_symbol || sym.share_class_symbol as fuzzy_symbol,
            equities.asset_name as asset_name,
            equities.start_date as start_date,
            equities.end_date as end_date,
            equities.first_traded as first_traded,
            equities.auto_close_date as auto_close_date,
            equities.exchange as exchange
        from
            equities
        inner join
            (select
                 *
             from
                 equity_symbol_mappings
             group by
                 equity_symbol_mappings.sid
             order by
                 equity_symbol_mappings.end_date desc) sym
        on
            equities.sid == sym.sid
        """,
    )
    op.drop_table('equity_symbol_mappings')
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indicies have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )
Exemplo n.º 5
0
def _downgrade_v5(op):
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ),
        sa.Column('symbol', sa.Text),
        sa.Column('company_symbol', sa.Text),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text),
        sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text),
        sa.Column('exchange_full', sa.Text)
    )

    op.execute(
        """
        insert into _new_equities
        select
            equities.sid as sid,
            sym.symbol as symbol,
            sym.company_symbol as company_symbol,
            sym.share_class_symbol as share_class_symbol,
            sym.company_symbol || sym.share_class_symbol as fuzzy_symbol,
            equities.asset_name as asset_name,
            equities.start_date as start_date,
            equities.end_date as end_date,
            equities.first_traded as first_traded,
            equities.auto_close_date as auto_close_date,
            equities.exchange as exchange,
            equities.exchange_full as exchange_full
        from
            equities
        inner join
            -- Nested select here to take the most recently held ticker
            -- for each sid. The group by with no aggregation function will
            -- take the last element in the group, so we first order by
            -- the end date ascending to ensure that the groupby takes
            -- the last ticker.
            (select
                 *
             from
                 (select
                      *
                  from
                      equity_symbol_mappings
                  order by
                      equity_symbol_mappings.end_date asc)
             group by
                 sid) sym
        on
            equities.sid == sym.sid
        """,
    )
    op.drop_table('equity_symbol_mappings')
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indicies have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )
Exemplo n.º 6
0
def _downgrade_v5(op):
    op.create_table(
        '_new_equities',
        sa.Column(
            'sid',
            sa.Integer,
            unique=True,
            nullable=False,
            primary_key=True,
        ), sa.Column('symbol', sa.Text), sa.Column('company_symbol', sa.Text),
        sa.Column('share_class_symbol', sa.Text),
        sa.Column('fuzzy_symbol', sa.Text), sa.Column('asset_name', sa.Text),
        sa.Column('start_date', sa.Integer, default=0, nullable=False),
        sa.Column('end_date', sa.Integer, nullable=False),
        sa.Column('first_traded', sa.Integer),
        sa.Column('auto_close_date', sa.Integer),
        sa.Column('exchange', sa.Text), sa.Column('exchange_full', sa.Text))

    op.execute(
        """
        insert into _new_equities
        select
            equities.sid as sid,
            sym.symbol as symbol,
            sym.company_symbol as company_symbol,
            sym.share_class_symbol as share_class_symbol,
            sym.company_symbol || sym.share_class_symbol as fuzzy_symbol,
            equities.asset_name as asset_name,
            equities.start_date as start_date,
            equities.end_date as end_date,
            equities.first_traded as first_traded,
            equities.auto_close_date as auto_close_date,
            equities.exchange as exchange,
            equities.exchange_full as exchange_full
        from
            equities
        inner join
            (select
                 *
             from
                 equity_symbol_mappings
             group by
                 equity_symbol_mappings.sid
             order by
                 equity_symbol_mappings.end_date desc) sym
        on
            equities.sid == sym.sid
        """, )
    op.drop_table('equity_symbol_mappings')
    op.drop_table('equities')
    op.rename_table('_new_equities', 'equities')
    # we need to make sure the indicies have the proper names after the rename
    op.create_index(
        'ix_equities_company_symbol',
        'equities',
        ['company_symbol'],
    )
    op.create_index(
        'ix_equities_fuzzy_symbol',
        'equities',
        ['fuzzy_symbol'],
    )