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)