anyway/alembic/versions/312c9eb92e40_add_cbs_locations_table.py at 5a5a4a0e9a9ebcbdeaea53c26904347fe2b8c588 · data-for-change/anyway · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
"""add cbs_locations table

Revision ID: 312c9eb92e40
Revises: d2dfd0ce5a7e
Create Date: 2021-08-20 15:46:37.063756

"""

# revision identifiers, used by Alembic.
revision = '312c9eb92e40'
down_revision = 'd2dfd0ce5a7e'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('cbs_locations',
    sa.Column('id', sa.Integer(), nullable=False),
    sa.Column('road1', sa.Integer(), nullable=True),
    sa.Column('road2', sa.Integer(), nullable=True),
    sa.Column('non_urban_intersection_hebrew', sa.Text(), nullable=True),
    sa.Column('yishuv_name', sa.Text(), nullable=True),
    sa.Column('street1_hebrew', sa.Text(), nullable=True),
    sa.Column('street2_hebrew', sa.Text(), nullable=True),
    sa.Column('district_hebrew', sa.Text(), nullable=True),
    sa.Column('region_hebrew', sa.Text(), nullable=True),
    sa.Column('road_segment_name', sa.Text(), nullable=True),
    sa.Column('longitude', sa.Float(), nullable=True),
    sa.Column('latitude', sa.Float(), nullable=True),
    sa.PrimaryKeyConstraint('id')
    )
    conn = op.get_bind()
    conn.execute(sa.text("""INSERT INTO cbs_locations
                        (SELECT ROW_NUMBER() OVER (ORDER BY road1) as id, LOCATIONS.*
                        FROM
                        (SELECT DISTINCT road1,
                            road2,
                            non_urban_intersection_hebrew,
                            yishuv_name,
                            street1_hebrew,
                            street2_hebrew,
                            district_hebrew,
                            region_hebrew,
                            road_segment_name,
                            longitude,
                            latitude
                        FROM markers_hebrew
                        WHERE (provider_code=1
                               OR provider_code=3)
                          AND (longitude is not null
                               AND latitude is not null)) LOCATIONS)"""))
    # ### end Alembic commands ###


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_table('cbs_locations')
    # ### end Alembic commands ###