def upgrade():
langs = ('fr', 'it', 'de', 'en', 'es', 'ca', 'eu')
lang_enum = sa.Enum(*langs, name='lang', schema='guidebook')
lang_enum.create(op.get_bind(), checkfirst=False)
op.add_column('user',
sa.Column('feed_filter_langs',
ArrayOfEnum(lang_enum),
server_default='{}',
nullable=False),
schema='users')
op.add_column('feed_document_changes',
sa.Column('langs',
ArrayOfEnum(lang_enum),
server_default='{}',
nullable=False),
schema='guidebook')
# fill the new col in
op.execute("""
with langs_for_documents as (
select document_id, array_agg(lang)::guidebook.lang[] as langs from guidebook.documents_locales group by document_id
)
update guidebook.feed_document_changes as c
set langs = dl.langs
from langs_for_documents dl
where c.document_id = dl.document_id""")
class _ImageMixin(object):
activities = Column(ArrayOfEnum(enums.activity_type))
categories = Column(ArrayOfEnum(enums.image_category))
image_type = Column(enums.image_type)
author = Column(String(100))
elevation = Column(SmallInteger)
height = Column(SmallInteger)
width = Column(SmallInteger)
file_size = Column(Integer)
@declared_attr
def filename(self):
return Column(String(30),
nullable=False,
unique=(self.__name__ == 'Image'))
date_time = Column(DateTime(timezone=True))
camera_name = Column(String(100))
exposure_time = Column(Float)
focal_length = Column(Float)
fnumber = Column(Float)
iso_speed = Column(SmallInteger)
class _ImageMixin(object):
activities = Column(ArrayOfEnum(enums.activity_type))
categories = Column(ArrayOfEnum(enums.image_category))
image_type = Column(enums.image_type)
author = Column(String(100))
has_svg = Column(Boolean)
elevation = Column(SmallInteger)
height = Column(SmallInteger)
width = Column(SmallInteger)
file_size = Column(Integer)
filename = Column(String(30))
date_time = Column(DateTime)
camera_name = Column(String(100))
exposure_time = Column(Float)
focal_length = Column(Float)
fnumber = Column(Float)
iso_speed = Column(SmallInteger)
class _BookMixin(object):
author = Column(String(100))
editor = Column(String(100))
activities = Column(ArrayOfEnum(enums.activity_type))
url = Column(String(255))
isbn = Column(String(17))
book_types = Column(ArrayOfEnum(enums.book_type))
nb_pages = Column(SmallInteger)
publication_date = Column(String(100))
langs = Column(ARRAY(String(2)))
class _OutingMixin(object):
activities = Column(ArrayOfEnum(enums.activity_type), nullable=False)
date_start = Column(Date, nullable=False)
date_end = Column(Date, nullable=False)
frequentation = Column(enums.frequentation_type)
participant_count = Column(SmallInteger)
elevation_min = Column(SmallInteger)
elevation_max = Column(SmallInteger)
elevation_access = Column(SmallInteger)
# altitude de chaussage
elevation_up_snow = Column(SmallInteger)
# altitude de dechaussage
elevation_down_snow = Column(SmallInteger)
height_diff_up = Column(SmallInteger)
height_diff_down = Column(SmallInteger)
length_total = Column(Integer)
partial_trip = Column(Boolean)
public_transport = Column(Boolean)
access_condition = Column(enums.access_condition)
lift_status = Column(enums.lift_status)
awesomeness = Column(enums.awesomeness)
duration = Column(SmallInteger)
duration_difficulties = Column(SmallInteger)
condition_rating = Column(enums.condition_rating)
snow_quantity = Column(enums.condition_rating)
snow_quality = Column(enums.condition_rating)
glacier_rating = Column(enums.glacier_rating)
avalanche_signs = Column(ArrayOfEnum(enums.avalanche_signs))
hut_status = Column(enums.hut_status)
class _XreportMixin(object):
# Altitude
elevation = Column(SmallInteger)
# date des observations
date = Column(Date)
# Type d'évènement
event_type = Column(ArrayOfEnum(enums.event_type))
activities = Column(ArrayOfEnum(enums.activity_type), nullable=False)
# Nombre de participants
nb_participants = Column(SmallInteger)
# Nombre de personnes touchées
nb_impacted = Column(SmallInteger)
# Intervention des secour
rescue = Column(Boolean)
avalanche_level = Column(enums.avalanche_level)
avalanche_slope = Column(enums.avalanche_slope)
# Proceed. of output and event-déroulement de la sortie et de l'évènement
severity = Column(enums.severity)
# PROFILE
# Involvement in the accident-Implication dans l'accident
author_status = Column(enums.author_status)
# Frequency practical activity-Fréquence de pratique de l'activité
activity_rate = Column(enums.activity_rate)
# Total trips made during the year-Nombre sorties réalisées dans l'année
nb_outings = Column(enums.nb_outings)
age = Column(SmallInteger)
gender = Column(enums.gender)
# Blessures antérieures
previous_injuries = Column(enums.previous_injuries)
autonomy = Column(enums.autonomy)
disable_comments = Column(Boolean)
anonymous = Column(Boolean)
def update_langs_of_changes(document_id):
"""Update the langs of all feed entries of the given document.
"""
langs = DBSession. \
query(cast(
func.array_agg(DocumentLocale.lang),
ArrayOfEnum(enums.lang))). \
filter(DocumentLocale.document_id == document_id). \
group_by(DocumentLocale.document_id). \
subquery('langs')
DBSession.execute(DocumentChange.__table__.update().where(
DocumentChange.document_id == document_id).values(
langs=langs.select()))
def downgrade():
# convert activity enum
activity_conversions = [
('activity_rate_w1', 'activity_rate_50'),
('activity_rate_m2', 'activity_rate_20'),
('activity_rate_y5', 'activity_rate_10'),
('activity_rate_y5', 'activity_rate_5'),
]
old_activity_type = sa.Enum('activity_rate_y5',
'activity_rate_m2',
'activity_rate_w1',
name='activity_rate',
schema='guidebook')
new_activity_type = sa.Enum('activity_rate_150',
'activity_rate_50',
'activity_rate_30',
'activity_rate_20',
'activity_rate_10',
'activity_rate_5',
'activity_rate_1',
name='activity_rate_',
schema='guidebook')
new_activity_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('activity_rate_', new_activity_type,
nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('activity_rate_', new_activity_type,
nullable=True),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('activity_rate', old_activity_type),
sa.Column('activity_rate_', new_activity_type, nullable=True),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('activity_rate', old_activity_type),
sa.Column('activity_rate_', new_activity_type, nullable=True),
schema='guidebook')
for (old_value, new_value) in activity_conversions:
op.execute(xr.update().where(
xr.c.activity_rate == op.inline_literal(old_value)).values(
activity_rate_=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.activity_rate == op.inline_literal(old_value)).values(
activity_rate_=op.inline_literal(new_value)))
op.drop_column('xreports', 'activity_rate', schema='guidebook')
op.drop_column('xreports_archives', 'activity_rate', schema='guidebook')
# op.drop_column('xreports_archives', 'avalanche_slope', schema='guidebook')
drop_enum('activity_rate', schema='guidebook')
# Rename enum
op.execute('ALTER TYPE guidebook.activity_rate_ RENAME TO activity_rate')
# Rename column
op.alter_column('xreports',
'activity_rate_',
new_column_name='activity_rate',
schema='guidebook')
op.alter_column('xreports_archives',
'activity_rate_',
new_column_name='activity_rate',
schema='guidebook')
# end of activity conversion
# convert autonomy enum
autonomy_conversions = [('non_autonomous', 'non_autonomous'),
('autonomous', 'autonomous'), ('expert', 'expert')]
old_autonomy_type = sa.Enum('non_autonomous',
'autonomous',
'expert',
name='autonomy',
schema='guidebook')
new_autonomy_type = sa.Enum('non_autonomous',
'autonomous',
'initiator',
'expert',
name='autonomy_',
schema='guidebook')
new_autonomy_type.create(op.get_bind())
# op.alter_column('xreports', 'autonomy',
# type_=new_autonomy_type,
# existing_type=old_autonomy_type,
# schema='guidebook')
# does not allow automatic casting if table not empty
op.add_column('xreports',
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('autonomy', old_autonomy_type),
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('autonomy', old_autonomy_type),
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
for (old_value, new_value) in autonomy_conversions:
op.execute(xr.update().where(
xr.c.autonomy == op.inline_literal(old_value)).values(
autonomy_=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.autonomy == op.inline_literal(old_value)).values(
autonomy_=op.inline_literal(new_value)))
op.drop_column('xreports', 'autonomy', schema='guidebook')
op.drop_column('xreports_archives', 'autonomy', schema='guidebook')
# op.drop_column('xreports_archives', 'avalanche_slope', schema='guidebook')
drop_enum('autonomy', schema='guidebook')
# Rename enum
op.execute('ALTER TYPE guidebook.autonomy_ RENAME TO autonomy')
# Rename column
op.alter_column('xreports',
'autonomy_',
new_column_name='autonomy',
schema='guidebook')
op.alter_column('xreports_archives',
'autonomy_',
new_column_name='autonomy',
schema='guidebook')
# end of autonomy conversion
op.drop_column('xreports', 'supervision', schema='guidebook')
op.drop_column('xreports_archives', 'supervision', schema='guidebook')
drop_enum('supervision_type', schema='guidebook')
op.drop_column('xreports', 'qualification', schema='guidebook')
op.drop_column('xreports_archives', 'qualification', schema='guidebook')
drop_enum('qualification_type', schema='guidebook')
nb_outing_type = sa.Enum('nb_outings_4',
'nb_outings_9',
'nb_outings_14',
'nb_outings_15',
name='nb_outings',
schema='guidebook')
nb_outing_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('nb_outings', nb_outing_type, nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('nb_outings', nb_outing_type, nullable=True),
schema='guidebook')
activity_conversions = [('other', 'hiking'), ('skitouring', 'skitouring'),
('snow_ice_mixed', 'snow_ice_mixed'),
('alpine_climbing', 'mountain_climbing'),
('sport_climbing', 'rock_climbing'),
('ice_climbing', 'ice_climbing')]
old_activity_type = sa.Enum('sport_climbing',
'multipitch_climbing',
'alpine_climbing',
'snow_ice_mixed',
'ice_climbing',
'skitouring',
'other',
name='event_activity_type',
schema='guidebook')
activities_type = ArrayOfEnum(
sa.Enum('skitouring',
'snow_ice_mixed',
'mountain_climbing',
'rock_climbing',
'ice_climbing',
'hiking',
'snowshoeing',
'paragliding',
'mountain_biking',
'via_ferrata',
'slacklining',
name='activity_type',
schema='guidebook'))
op.add_column('xreports',
sa.Column('activities', activities_type, nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('activities', activities_type, nullable=True),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('activities', activities_type, nullable=True),
sa.Column('event_activity', old_activity_type),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('activities', activities_type, nullable=True),
sa.Column('event_activity', old_activity_type),
schema='guidebook')
for (old_value, new_value) in activity_conversions:
op.execute(xr.update().where(
xr.c.event_activity == op.inline_literal(old_value)).values(
activities=sa.literal([new_value])))
op.execute(xra.update().where(
xra.c.event_activity == op.inline_literal(old_value)).values(
activities=sa.literal([new_value])))
op.alter_column('xreports',
'activities',
nullable=False,
schema='guidebook')
op.alter_column('xreports_archives',
'activities',
nullable=False,
schema='guidebook')
op.drop_column('xreports', 'event_activity', schema='guidebook')
op.drop_column('xreports_archives', 'event_activity', schema='guidebook')
drop_enum('event_activity_type', schema='guidebook')
# convert types
type_conversions = [('avalanche', 'avalanche'),
('stone_ice_fall', 'stone_fall'),
('ice_cornice_collapse', 'falling_ice'),
('person_fall', 'person_fall'),
('crevasse_fall', 'crevasse_fall'),
('physical_failure', 'physical_failure'),
('injury_without_fall', 'other'),
('blocked_person', 'other'),
('safety_operation', 'other'),
('critical_situation', 'other'),
('weather_event', 'lightning'), ('other', 'other')]
old_event_type = sa.Enum('avalanche',
'stone_ice_fall',
'ice_cornice_collapse',
'person_fall',
'crevasse_fall',
'physical_failure',
'injury_without_fall',
'blocked_person',
'weather_event',
'safety_operation',
'critical_situation',
'other',
name='event_type',
schema='guidebook')
new_event_type = sa.Enum('avalanche',
'stone_fall',
'falling_ice',
'person_fall',
'crevasse_fall',
'roped_fall',
'physical_failure',
'lightning',
'other',
name='event_type_',
schema='guidebook')
new_event_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('event_type_', ArrayOfEnum(new_event_type)),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('event_type_', ArrayOfEnum(new_event_type)),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('event_type', old_event_type),
sa.Column('event_type_', ArrayOfEnum(new_event_type)),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('event_type', old_event_type),
sa.Column('event_type_', ArrayOfEnum(new_event_type)),
schema='guidebook')
for (old_value, new_value) in type_conversions:
op.execute(xr.update().where(
xr.c.event_type == op.inline_literal(old_value)).values(
event_type_=sa.literal([new_value])))
op.execute(xra.update().where(
xra.c.event_type == op.inline_literal(old_value)).values(
event_type_=sa.literal([new_value])))
op.drop_column('xreports', 'event_type', schema='guidebook')
op.drop_column('xreports_archives', 'event_type', schema='guidebook')
drop_enum('event_type', schema='guidebook')
op.execute('ALTER TYPE guidebook.event_type_ RENAME TO event_type')
op.alter_column('xreports',
'event_type_',
new_column_name='event_type',
schema='guidebook')
op.alter_column('xreports_archives',
'event_type_',
new_column_name='event_type',
schema='guidebook')
def upgrade():
# convert activities
activity_conversions = [
('hiking', 'other'),
('snowshoeing', 'other'),
('paragliding', 'other'),
('mountain_biking', 'other'),
('via_ferrata', 'other'),
('slacklining', 'other'),
('skitouring', 'skitouring'),
('snow_ice_mixed', 'snow_ice_mixed'),
('mountain_climbing', 'alpine_climbing'),
('rock_climbing', 'sport_climbing'),
('ice_climbing', 'ice_climbing'),
]
old_activity_type = ArrayOfEnum(
sa.Enum('skitouring',
'snow_ice_mixed',
'mountain_climbing',
'rock_climbing',
'ice_climbing',
'hiking',
'snowshoeing',
'paragliding',
'mountain_biking',
'via_ferrata',
'slacklining',
name='activity_type',
schema='guidebook'))
new_activity_type = sa.Enum('sport_climbing',
'multipitch_climbing',
'alpine_climbing',
'snow_ice_mixed',
'ice_climbing',
'skitouring',
'other',
name='event_activity_type',
schema='guidebook')
new_activity_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('event_activity', new_activity_type),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('event_activity', new_activity_type),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('activities', old_activity_type),
sa.Column('event_activity', new_activity_type, nullable=True),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('activities', old_activity_type),
sa.Column('event_activity', new_activity_type, nullable=True),
schema='guidebook')
for (old_value, new_value) in activity_conversions:
op.execute(xr.update().where(
xr.c.activities.contains(
sa.literal([old_value]).cast(old_activity_type))).values(
event_activity=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.activities.contains(
sa.literal([old_value]).cast(old_activity_type))).values(
event_activity=op.inline_literal(new_value)))
op.alter_column('xreports',
'event_activity',
nullable=False,
schema='guidebook')
op.alter_column('xreports_archives',
'event_activity',
nullable=False,
schema='guidebook')
op.drop_column('xreports', 'activities', schema='guidebook')
op.drop_column('xreports_archives', 'activities', schema='guidebook')
# end of activities conversion
# convert types
type_conversions = [('avalanche', 'avalanche'),
('stone_fall', 'stone_ice_fall'),
('falling_ice', 'stone_ice_fall'),
('person_fall', 'person_fall'),
('crevasse_fall', 'crevasse_fall'),
('roped_fall', 'person_fall'),
('physical_failure', 'physical_failure'),
('lightning', 'weather_event'), ('other', 'other')]
old_event_type = ArrayOfEnum(
sa.Enum('avalanche',
'stone_fall',
'falling_ice',
'person_fall',
'crevasse_fall',
'roped_fall',
'physical_failure',
'lightning',
'other',
name='event_type',
schema='guidebook'))
new_event_type = sa.Enum('avalanche',
'stone_ice_fall',
'ice_cornice_collapse',
'person_fall',
'crevasse_fall',
'physical_failure',
'injury_without_fall',
'blocked_person',
'weather_event',
'safety_operation',
'critical_situation',
'other',
name='event_type_',
schema='guidebook')
new_event_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('event_type_', new_event_type),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('event_type_', new_event_type),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('event_type', old_event_type),
sa.Column('event_type_', new_event_type),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('event_type', old_event_type),
sa.Column('event_type_', new_event_type),
schema='guidebook')
for (old_value, new_value) in type_conversions:
op.execute(xr.update().where(
xr.c.event_type.contains(
sa.literal([old_value]).cast(old_event_type))).values(
event_type_=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.event_type.contains(
sa.literal([old_value]).cast(old_event_type))).values(
event_type_=op.inline_literal(new_value)))
op.alter_column('xreports',
'event_type',
nullable=False,
schema='guidebook')
op.alter_column('xreports_archives',
'event_type',
nullable=False,
schema='guidebook')
op.drop_column('xreports', 'event_type', schema='guidebook')
op.drop_column('xreports_archives', 'event_type', schema='guidebook')
drop_enum('event_type', schema='guidebook')
op.execute('ALTER TYPE guidebook.event_type_ RENAME TO event_type')
op.alter_column('xreports',
'event_type_',
new_column_name='event_type',
schema='guidebook')
op.alter_column('xreports_archives',
'event_type_',
new_column_name='event_type',
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('document_id', sa.types.INTEGER),
sa.Column('event_type', new_event_type),
schema='guidebook')
# xra = Table('xreports_archives', MetaData(),
# sa.Column('document_id', sa.types.INTEGER),
# sa.Column('event_type', new_event_type),
# schema='guidebook')
try:
with open(
'./alembic_migration/versions/06d2a35e39c8_improve_serac_database_data.csv'
) as f:
rr = csv.reader(f)
header = rr.__next__()
assert (header[1] == 'Document') and (header[8] == 'ENS principal')
for line in rr:
print("update {} -> {}".format(
line[1], key_map[line[8].lower().encode()]))
op.execute(
xr.update().where(xr.c.document_id == line[1]).values(
event_type=key_map[line[8].lower().encode()]))
# op.execute(xra.update()
# .where(xra.c.document_id == line[1])
# .values(event_type=key_map[line[8].lower()]))
except Exception as e:
print("EXCEPT!!! {} {}".format(type(e), e))
# end of types conversion
# convert autonomy enum
autonomy_conversions = [('non_autonomous', 'non_autonomous'),
('autonomous', 'autonomous'),
('initiator', 'autonomous'), ('expert', 'expert')]
old_autonomy_type = sa.Enum('non_autonomous',
'autonomous',
'initiator',
'expert',
name='autonomy',
schema='guidebook')
new_autonomy_type = sa.Enum('non_autonomous',
'autonomous',
'expert',
name='autonomy_',
schema='guidebook')
new_autonomy_type.create(op.get_bind())
# op.alter_column('xreports', 'autonomy',
# type_=new_autonomy_type,
# existing_type=old_autonomy_type,
# schema='guidebook')
# does not allow automatic casting if table not empty
op.add_column('xreports',
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('autonomy', old_autonomy_type),
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('autonomy', old_autonomy_type),
sa.Column('autonomy_', new_autonomy_type, nullable=True),
schema='guidebook')
for (old_value, new_value) in autonomy_conversions:
op.execute(xr.update().where(
xr.c.autonomy == op.inline_literal(old_value)).values(
autonomy_=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.autonomy == op.inline_literal(old_value)).values(
autonomy_=op.inline_literal(new_value)))
op.drop_column('xreports', 'autonomy', schema='guidebook')
op.drop_column('xreports_archives', 'autonomy', schema='guidebook')
# op.drop_column('xreports_archives', 'avalanche_slope', schema='guidebook')
drop_enum('autonomy', schema='guidebook')
# Rename enum
op.execute('ALTER TYPE guidebook.autonomy_ RENAME TO autonomy')
# Rename column
op.alter_column('xreports',
'autonomy_',
new_column_name='autonomy',
schema='guidebook')
op.alter_column('xreports_archives',
'autonomy_',
new_column_name='autonomy',
schema='guidebook')
# end of autonomy conversion
# convert activity enum
activity_conversions = [('activity_rate_150', 'activity_rate_w1'),
('activity_rate_50', 'activity_rate_w1'),
('activity_rate_30', 'activity_rate_m2'),
('activity_rate_20', 'activity_rate_m2'),
('activity_rate_10', 'activity_rate_y5'),
('activity_rate_5', 'activity_rate_y5'),
('activity_rate_1', 'activity_rate_y5')]
old_activity_type = sa.Enum('activity_rate_150',
'activity_rate_50',
'activity_rate_30',
'activity_rate_20',
'activity_rate_10',
'activity_rate_5',
'activity_rate_1',
name='activity_rate',
schema='guidebook')
new_activity_type = sa.Enum('activity_rate_y5',
'activity_rate_m2',
'activity_rate_w1',
name='activity_rate_',
schema='guidebook')
new_activity_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('activity_rate_', new_activity_type,
nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('activity_rate_', new_activity_type,
nullable=True),
schema='guidebook')
xr = Table('xreports',
MetaData(),
sa.Column('activity_rate', old_activity_type),
sa.Column('activity_rate_', new_activity_type, nullable=True),
schema='guidebook')
xra = Table('xreports_archives',
MetaData(),
sa.Column('activity_rate', old_activity_type),
sa.Column('activity_rate_', new_activity_type, nullable=True),
schema='guidebook')
for (old_value, new_value) in activity_conversions:
op.execute(xr.update().where(
xr.c.activity_rate == op.inline_literal(old_value)).values(
activity_rate_=op.inline_literal(new_value)))
op.execute(xra.update().where(
xra.c.activity_rate == op.inline_literal(old_value)).values(
activity_rate_=op.inline_literal(new_value)))
op.drop_column('xreports', 'activity_rate', schema='guidebook')
op.drop_column('xreports_archives', 'activity_rate', schema='guidebook')
# op.drop_column('xreports_archives', 'avalanche_slope', schema='guidebook')
drop_enum('activity_rate', schema='guidebook')
# Rename enum
op.execute('ALTER TYPE guidebook.activity_rate_ RENAME TO activity_rate')
# Rename column
op.alter_column('xreports',
'activity_rate_',
new_column_name='activity_rate',
schema='guidebook')
op.alter_column('xreports_archives',
'activity_rate_',
new_column_name='activity_rate',
schema='guidebook')
# end of activity conversion
op.drop_column('xreports', 'nb_outings', schema='guidebook')
op.drop_column('xreports_archives', 'nb_outings', schema='guidebook')
# op.drop_column('xreports_archives', 'avalanche_slope', schema='guidebook')
drop_enum('nb_outings', schema='guidebook')
supervision_type = sa.Enum('no_supervision',
'federal_supervision',
'professional_supervision',
name='supervision_type',
schema='guidebook')
supervision_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('supervision', supervision_type, nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('supervision', supervision_type, nullable=True),
schema='guidebook')
qualification_type = sa.Enum('federal_supervisor',
'federal_trainer',
'professional_diploma',
name='qualification_type',
schema='guidebook')
qualification_type.create(op.get_bind())
op.add_column('xreports',
sa.Column('qualification', qualification_type,
nullable=True),
schema='guidebook')
op.add_column('xreports_archives',
sa.Column('qualification', qualification_type,
nullable=True),
schema='guidebook')
class _UserProfileMixin(object):
activities = Column(ArrayOfEnum(activity_type))
categories = Column(ArrayOfEnum(user_category))
class _ArticleMixin(object):
categories = Column(ArrayOfEnum(article_category))
activities = Column(ArrayOfEnum(activity_type))
article_type = Column(enums.article_type)
class _WaypointMixin(object):
# type de WP
waypoint_type = Column(enums.waypoint_type, nullable=False)
# altitude
elevation = Column(SmallInteger)
# altitude min. (access)
elevation_min = Column(SmallInteger)
# proeminence/hauteur de culminance (summit)
prominence = Column(SmallInteger)
# hauteur max. (climbing_outdoor/indoor)
height_max = Column(SmallInteger)
# hauteur median (climbing_outdoor/indoor)
height_median = Column(SmallInteger)
# hauteur min (climbing_outdoor/indoor)
height_min = Column(SmallInteger)
# nombre de voies (climbing_outdoor/indoor)
routes_quantity = Column(SmallInteger)
# type de site (climbing_outdoor)
climbing_outdoor_types = Column(ArrayOfEnum(enums.climbing_outdoor_type))
# type de site (climbing_indoor)
climbing_indoor_types = Column(ArrayOfEnum(enums.climbing_indoor_type))
# cotation max (climbing_outdoor/indoor)
climbing_rating_max = Column(enums.climbing_rating)
# cotation min (climbing_outdoor/indoor)
climbing_rating_min = Column(enums.climbing_rating)
# cotation median (climbing_outdoor/indoor)
climbing_rating_median = Column(enums.climbing_rating)
# qualite de l'equipement (climbing_outdoor)
equipment_ratings = Column(ArrayOfEnum(enums.equipment_rating))
# styles d'escalade (climbing_outdoor/indoor)
climbing_styles = Column(ArrayOfEnum(enums.climbing_style))
# enfants (climbing_outdoor/indoor)
children_proof = Column(enums.children_proof_type)
# pluie (climbing_outdoor/indoor)
rain_proof = Column(enums.rain_proof_type)
# orientations (climbing_outdoor/indoor, paragliding_takeoff/landing)
orientations = Column(ArrayOfEnum(enums.orientation_type))
# meilleurs periodes (climbing_outdoor/indoor, paragliding_takeoff/landing)
best_periods = Column(ArrayOfEnum(enums.month_type))
# type de produits locaux (local_product)
product_types = Column(ArrayOfEnum(enums.product_type))
# longueur (lac, paragliding_takeoff/landing)
length = Column(SmallInteger)
# pente (paragliding_takeoff/landing)
slope = Column(SmallInteger)
# nature du sol (paragliding_takeoff/landing)
ground_types = Column(ArrayOfEnum(enums.ground_type))
# cotation deco/attero (paragliding_takeoff/landing)
paragliding_rating = Column(enums.paragliding_rating)
# exposition (paragliding_takeoff/landing)
exposition_rating = Column(enums.exposition_rating)
# type de rocher (summit, waterfall, cave, climbing_outdoor,
# climbing_outdoor/indoor)
rock_types = Column(ArrayOfEnum(enums.rock_type))
# grandeurs mesurees (weatherstation)
weather_station_types = Column(ArrayOfEnum(enums.weather_station_type))
# url (climbing_outdoor/indoor, gite, camp_site, hut, base_camp,
# local_product, sport_shop, paragliding_takeoff/landing, weather_station,
# webcam)
url = Column(String(255))
# cartographie (all except bivouac, local_product, sport_shop,
# paragliding_takeoff/landing, weather_station, webcam)
maps_info = Column(String(300))
# telephone (climbing_indoor, gite, camp_site, hut, local_product,
# sport_shop)
phone = Column(String(50))
# type de transport en commun (access)
public_transportation_types = Column(
ArrayOfEnum(enums.public_transportation_type))
# accessibilite en transports en commun (access)
public_transportation_rating = Column(enums.public_transportation_rating)
# deneigement (access)
snow_clearance_rating = Column(enums.snow_clearance_rating)
# servi par des remontees mecaniques (access)
lift_access = Column(Boolean)
# parking payant (access)
parking_fee = Column(enums.parking_fee_type)
# telephone gardien/gerant (gite, camp_site, hut)
phone_custodian = Column(String(50))
# gardiennage (gite, camp_site, hut, abri, bivouac, base_camp)
custodianship = Column(enums.custodianship_type)
# matelas hors gardiennage (hut, abri, bivouac, base_camp)
matress_unstaffed = Column(Boolean)
# couvertures hors gardiennage (hut, abri)
blanket_unstaffed = Column(Boolean)
# cuisiniere / gaz hors gardiennage (hut, abri)
gas_unstaffed = Column(Boolean)
# chauffage hors gardiennage (hut, abri)
heating_unstaffed = Column(Boolean)
# duree de l'approche (climbing_outdoor)
access_time = Column(enums.access_time_type)
# nb places hors gardiennage
# (gite, camping refuge, abri, bivouac, base_camp)
capacity = Column(SmallInteger)
# nb places en gardiennage (gite, camping refuge)
capacity_staffed = Column(SmallInteger)
class User(Base):
"""
Class containing the users' private and authentication data.
"""
__tablename__ = 'user'
__table_args__ = {'schema': users_schema}
# the user id is the same as the document id of the user profile
id = Column(
Integer, ForeignKey(schema + '.user_profiles.document_id'),
primary_key=True)
profile = relationship(
UserProfile, primaryjoin=id == UserProfile.document_id, uselist=False,
backref=backref('user', uselist=False))
username = Column(String(200), nullable=False, unique=True)
name = Column(String(200), nullable=False)
forum_username = Column(
String(25),
nullable=False, unique=True
)
email = Column(String(200), nullable=False, unique=True)
email_validated = Column(
Boolean, nullable=False, default=False, index=True)
email_to_validate = Column(String(200), nullable=True)
moderator = Column(Boolean, nullable=False, default=False)
validation_nonce = Column(String(200), nullable=True, unique=True)
validation_nonce_expire = Column(
DateTime(timezone=True), nullable=True, unique=False)
_password = Column('password', String(255), nullable=False)
last_modified = Column(
DateTime(timezone=True), default=func.now(), onupdate=func.now(),
nullable=False, index=True)
blocked = Column(Boolean, nullable=False, default=False)
lang = Column(
String(2), ForeignKey(schema + '.langs.lang'),
nullable=False, default='fr')
is_profile_public = Column(
Boolean, nullable=False, default=False, server_default='FALSE')
# the feed on the homepage for a user is filtered on these activities
feed_filter_activities = Column(
ArrayOfEnum(enums.activity_type), nullable=False, server_default='{}')
# the feed on the homepage for a user is filtered on these langs
feed_filter_langs = Column(
ArrayOfEnum(enums.lang), nullable=False, server_default='{}')
# only show updates from followed users in the homepage feed
feed_followed_only = Column(
Boolean, server_default='FALSE', nullable=False)
ratelimit_remaining = Column(Integer)
ratelimit_reset = Column(DateTime(timezone=True))
ratelimit_last_blocked_window = Column(DateTime(timezone=True))
ratelimit_times = Column(Integer, nullable=False, default=0)
robot = Column(Boolean, nullable=False, default=False)
def update_validation_nonce(self, purpose, days):
"""Generate and overwrite the nonce.
A nonce is a random number which is used for authentication when doing
particular actions like changing password or validating an email. It
must have a short lifespan to avoid unused nonces to become a security
risk."""
if purpose != Purpose.registration and not self.email_validated:
# An account must be validated before any other action is tried.
raise AccountNotValidated()
now = datetime.datetime.utcnow()
nonce = binascii.hexlify(os.urandom(32)).decode('ascii')
self.validation_nonce = purpose.value + '_' + nonce
self.validation_nonce_expire = now + datetime.timedelta(days=days)
def validate_nonce_purpose(self, expected_purpose):
nonce = self.validation_nonce
prefix = expected_purpose.value + '_'
return nonce is not None and nonce.startswith(prefix)
def clear_validation_nonce(self):
self.validation_nonce = None
self.validation_nonce_expire = None
def _get_password(self):
return self._password
def _set_password(self, password):
self._password = PasswordUtil.encrypt_password(password)
def validate_password(self, plain_password):
"""Check the password against existing credentials.
"""
return PasswordUtil.is_password_valid(plain_password, self._password)
password = property(_get_password, _set_password)
class DocumentChange(Base):
"""This table contains "changes" that are shown in the homepage feed and
the user profile.
For example if a user creates a document or uploads images, an entry is
added to this table.
"""
__tablename__ = 'feed_document_changes'
change_id = Column(Integer, primary_key=True)
time = Column(DateTime(timezone=True), default=func.now(), nullable=False)
# the actor: who did the change?
user_id = Column(Integer,
ForeignKey(users_schema + '.user.id'),
nullable=False)
user = relationship(User, primaryjoin=user_id == User.id)
# the action type: what did the user do? e.g. create or update a document
change_type = Column(feed_change_type, nullable=False)
# the object: what document did the user change?
document_id = Column(Integer,
ForeignKey(schema + '.documents.document_id'),
nullable=False)
document = relationship(Document,
primaryjoin=document_id == Document.document_id)
document_type = Column(String(1), nullable=False)
# activities related to the document
activities = Column(ArrayOfEnum(enums.activity_type),
nullable=False,
server_default='{}')
# langs of the document locales
langs = Column(ArrayOfEnum(enums.lang),
nullable=False,
server_default='{}')
# For performance reasons, areas and users are referenced in simple integer
# arrays in 'feed_document_changes', no PK-FK relations are set up.
# To prevent inconsistencies, triggers are used.
# ids of the areas where this change happened
area_ids = Column(ARRAY(Integer), nullable=False, server_default='{}')
# ids of the users that were involved in this change (e.g. the user that
# created a document, but also the participants of an outing)
user_ids = Column(ARRAY(Integer), nullable=False, server_default='{}')
# images
image1_id = Column(Integer, ForeignKey(schema + '.images.document_id'))
image1 = relationship(Image, primaryjoin=image1_id == Image.document_id)
image2_id = Column(Integer, ForeignKey(schema + '.images.document_id'))
image2 = relationship(Image, primaryjoin=image2_id == Image.document_id)
image3_id = Column(Integer, ForeignKey(schema + '.images.document_id'))
image3 = relationship(Image, primaryjoin=image3_id == Image.document_id)
more_images = Column(Boolean, server_default='FALSE', nullable=False)
__table_args__ = (
# the queries on the feed table always order by time (desc) and
# change_id, therefore create an index for these two columns.
Index('ix_guidebook_feed_document_changes_time_and_change_id',
time.desc(),
change_id,
postgresql_using='btree'),
Base.__table_args__)
def copy(self):
copy = DocumentChange()
copy.document_id = self.document_id
copy.document_type = self.document_type
copy.change_type = self.change_type
copy.activities = self.activities
copy.langs = self.langs
copy.area_ids = self.area_ids
if copy.document_type == OUTING_TYPE:
copy.user_ids = self.user_ids
else:
copy.user_ids = []
return copy
class _RouteMixin(object):
# activite
activities = Column(ArrayOfEnum(enums.activity_type), nullable=False)
# altitude min.
elevation_min = Column(SmallInteger)
# altitude max.
elevation_max = Column(SmallInteger)
# denivele positif du troncon dans le sens aller
height_diff_up = Column(SmallInteger)
# denivele negatif du troncon dans le sens aller
height_diff_down = Column(SmallInteger)
# longueur du troncon
route_length = Column(Integer)
# altitude du debut des difficultes
difficulties_height = Column(SmallInteger)
# denivele de l'approche
height_diff_access = Column(SmallInteger)
# Denivele des difficultes
height_diff_difficulties = Column(SmallInteger)
# type d'itineraire (aller-retour, boucle, ...)
route_types = Column(ArrayOfEnum(enums.route_type))
# orientations
orientations = Column(ArrayOfEnum(enums.orientation_type))
# temps de parcours total
durations = Column(ArrayOfEnum(enums.route_duration_type))
# crampons et materiel de securite sur glacier
glacier_gear = Column(
enums.glacier_gear_type, default='no', server_default='no',
nullable=False)
# configuration
configuration = Column(ArrayOfEnum(enums.route_configuration_type))
# approche par remontee mecanique possible
lift_access = Column(Boolean)
# cotation technique ski
ski_rating = Column(enums.ski_rating)
# exposition ski
ski_exposition = Column(enums.exposition_rating)
# cotation ponctuelle ski
labande_ski_rating = Column(enums.labande_ski_rating)
# cotation globale ski
labande_global_rating = Column(enums.global_rating)
# cotation globale
global_rating = Column(enums.global_rating)
# engagement
engagement_rating = Column(enums.engagement_rating)
# risques objectifs
risk_rating = Column(enums.risk_rating)
# qualite de l'equipement en place
equipment_rating = Column(enums.equipment_rating)
# cotation glace
ice_rating = Column(enums.ice_rating)
# cotation mixte
mixed_rating = Column(enums.mixed_rating)
# exposition rocher
exposition_rock_rating = Column(enums.exposition_rock_rating)
# cotation libre FR
rock_free_rating = Column(enums.climbing_rating)
# cotation obligatoire FR
rock_required_rating = Column(enums.climbing_rating)
# cotation escalade artificielle obligatoire
aid_rating = Column(enums.aid_rating)
# cotation via ferrata
via_ferrata_rating = Column(enums.via_ferrata_rating)
# cotation randonee
hiking_rating = Column(enums.hiking_rating)
# Exposition randonnee et VTT
hiking_mtb_exposition = Column(enums.exposition_rating)
# cotation raquette
snowshoe_rating = Column(enums.snowshoe_rating)
# cotation VTT (montee)
mtb_up_rating = Column(enums.mtb_up_rating)
# cotation VTT (descente)
mtb_down_rating = Column(enums.mtb_down_rating)
# longueur de bitume
mtb_length_asphalt = Column(Integer)
# longueur de piste
mtb_length_trail = Column(Integer)
# denivele de portage ou poussage
mtb_height_diff_portages = Column(Integer)
# type de rocher
rock_types = Column(ArrayOfEnum(enums.rock_type))
# type de voie
climbing_outdoor_type = Column(enums.climbing_outdoor_type)
class _OutingMixin(object):
activities = Column(ArrayOfEnum(enums.activity_type), nullable=False)
date_start = Column(Date, nullable=False)
date_end = Column(Date, nullable=False)
frequentation = Column(enums.frequentation_type)
participant_count = Column(SmallInteger)
elevation_min = Column(SmallInteger)
elevation_max = Column(SmallInteger)
elevation_access = Column(SmallInteger)
# altitude de chaussage
elevation_up_snow = Column(SmallInteger)
# altitude de dechaussage
elevation_down_snow = Column(SmallInteger)
height_diff_up = Column(SmallInteger)
height_diff_down = Column(SmallInteger)
length_total = Column(Integer)
partial_trip = Column(Boolean)
public_transport = Column(Boolean)
access_condition = Column(enums.access_condition)
lift_status = Column(enums.lift_status)
condition_rating = Column(enums.condition_rating)
snow_quantity = Column(enums.snow_quantity_ratings)
snow_quality = Column(enums.snow_quality_ratings)
glacier_rating = Column(enums.glacier_rating)
avalanche_signs = Column(ArrayOfEnum(enums.avalanche_signs))
hut_status = Column(enums.hut_status)
disable_comments = Column(Boolean)
hiking_rating = Column(enums.hiking_rating)
ski_rating = Column(enums.ski_rating)
labande_global_rating = Column(enums.global_rating)
snowshoe_rating = Column(enums.snowshoe_rating)
global_rating = Column(enums.global_rating)
height_diff_difficulties = Column(SmallInteger)
engagement_rating = Column(enums.engagement_rating)
equipment_rating = Column(enums.equipment_rating)
rock_free_rating = Column(enums.climbing_rating)
ice_rating = Column(enums.ice_rating)
via_ferrata_rating = Column(enums.via_ferrata_rating)
mtb_up_rating = Column(enums.mtb_up_rating)
mtb_down_rating = Column(enums.mtb_down_rating)