class Log(Base): __tablename__ = 'log' id = Column('id', Integer, primary_key=True, autoincrement=True) infomarker = Column('infomarker', types.Integer, ForeignKey('trackpoint.id')) topic = Column('topic', types.UnicodeText) content = Column('content', types.UnicodeText) #author = Column(Integer, ForeignKey('author.id',onupdate='CASCADE', ondelete='CASCADE')) etappe = Column( Integer, ForeignKey('etappe.id', onupdate='CASCADE', ondelete='CASCADE')) created = Column('created', types.TIMESTAMP(timezone=False), default=timetools.now()) published = Column('published', types.TIMESTAMP(timezone=False), default=timetools.now()) uuid = Column('uuid', postgresql.UUID, unique=True) image = relationship('Image', secondary=log_image_table, backref='logs') track = relationship('Track', secondary=log_track_table, backref='logs') def __init__(self, infomarker, topic, content, etappe, created, published, uuid): self.infomarker = infomarker self.topic = topic self.content = content self.etappe = etappe self.created = created self.published = published self.uuid = uuid
class AuthToken(Base): __tablename__ = 'auth_token' id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey('users.id', onupdate="CASCADE", \ ondelete="CASCADE")) jwt_id = Column(postgresql.UUID, unique=True) created = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) expires = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) active = Column(types.Boolean, default=True, nullable=False) client_ip = Column(types.UnicodeText) user_agent = Column(types.UnicodeText) def __init__(self, user_id, \ client_ip, \ user_agent, expire_days, \ jwt_id=str(uuidlib.uuid4()), \ created=timetools.now(), \ active=True): self.user_id = user_id self.jwt_id = jwt_id self.created = created self.expires = created + timedelta(days=expire_days) self.active = active self.client_ip = client_ip self.user_agent = user_agent
class Tour(Base): __tablename__ = 'tour' id = Column(Integer, primary_key=True) name = Column('name', Text) description = Column('description', Text) start_timestamp = Column('start_timestamp', types.TIMESTAMP(timezone=False)) end_timestamp = Column('end_timestamp', types.TIMESTAMP(timezone=False)) reduced_trackpoints = Column('reduced_trackpoints', Text) bbox = Column('bbox', Geometry) center_id = Column('center_id', types.Integer, ForeignKey('trackpoint.id')) uuid = Column('uuid', postgresql.UUID, unique=True) etappes = relationship('Etappe', order_by='desc(Etappe.start_timestamp)') center = relationship('Trackpoint', foreign_keys=center_id, uselist=False) def __init__(self, name, description, start_timestamp=timetools.now(), end_timestamp=timetools.now(), center_id=None, uuid=str(uuidlib.uuid4())): self.name = name self.description = description self.start_timestamp = start_timestamp self.end_timestamp = end_timestamp self.reduced_trackpoints = reduce_trackpoints(trackpoints, epsilon) self.bbox = bbox self.center_id = center_id self.uuid = uuid def reprGeoJSON(self): #returns own columns only start_timestamp = self.start_timestamp.strftime('%Y-%m-%d %H:%M:%S') end_timestamp = self.end_timestamp.strftime('%Y-%m-%d %H:%M:%S') children = [etappe.id for etappe in self.etappes] grandchildren = [ track.id for track in sum([etappe.tracks for etappe in self.etappes], []) ] #sum converts 2D-list to 1D center = [float(self.center.longitude), float(self.center.latitude)] return dict(id=self.id, name=self.name, description=self.description, start_timestamp=start_timestamp, end_timestamp=end_timestamp, bbox=self.bbox, center=center, uuid=self.uuid, level='tour', grandparent=dict(level=None, id_list=None), parent=dict(level=None, id_list=None), children=dict(level='etappe', id_list=children), grandchildren=dict(level='track', id_list=grandchildren))
def upgrade(): """ Create the initial tables needed by shipyard 26 character IDs are ULIDs. See: https://github.com/mdipierro/ulid """ op.create_table( 'actions', # ULID key for the action sa.Column('id', types.String(26), primary_key=True), # The name of the action invoked sa.Column('name', types.String(50), nullable=False), # The parameters passed by the user to the action sa.Column('parameters', pg.JSONB, nullable=True), # The DAG/workflow name used in airflow, if applicable sa.Column('dag_id', sa.Text, nullable=True), # The DAG/workflow execution time string from airflow, if applicable sa.Column('dag_execution_date', sa.Text, nullable=True), # The invoking user sa.Column('user', sa.Text, nullable=False), # Timestamp of when an action was invoked sa.Column('datetime', types.TIMESTAMP(timezone=True), server_default=func.now()), # The user provided or shipayrd generated context marker sa.Column('context_marker', types.String(36), nullable=False)) op.create_table( 'preflight_validation_failures', # ID (ULID) of the preflight validation failure sa.Column('id', types.String(26), primary_key=True), # The ID of action this failure is associated with sa.Column('action_id', types.String(26), nullable=False), # The common language name of the validation that failed sa.Column('validation_name', sa.Text, nullable=True), # The text indicating details of the failure sa.Column('details', sa.Text, nullable=True), ) op.create_table( 'action_command_audit', # ID (ULID) of the audit sa.Column('id', types.String(26), primary_key=True), # The ID of action this audit record sa.Column('action_id', types.String(26), nullable=False), # The text indicating command invoked sa.Column('command', sa.Text, nullable=False), # The user that invoked the command sa.Column('user', sa.Text, nullable=False), # Timestamp of when the command was invoked sa.Column('datetime', types.TIMESTAMP(timezone=True), server_default=func.now()), )
class Image(Base): __tablename__ = 'image' id = Column(Integer, primary_key=True) name = Column('name', types.UnicodeText) location = Column('location', types.UnicodeText) title = Column('title', types.UnicodeText) comment = Column('comment', types.UnicodeText) alt = Column('alt', types.UnicodeText) aperture = Column(Text) shutter = Column(Text) focal_length = Column(Text) iso = Column(Text) timestamp_original = Column(types.TIMESTAMP(timezone=False)) hash = Column('hash', types.UnicodeText) hash_large = Column('hash_large', types.UnicodeText) #hash of the image with 990px width #author = Column(Integer, ForeignKey('author.id',onupdate='CASCADE', ondelete='CASCADE')) trackpoint = Column( Integer, ForeignKey('trackpoint.id', onupdate='CASCADE', ondelete='CASCADE')) last_change = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) published = Column(types.TIMESTAMP(timezone=False)) uuid = Column('uuid', postgresql.UUID, unique=True) log = relationship('Log', secondary=log_image_table) __table_args__ = (UniqueConstraint('location', 'name', name='image_location_name'), {}) def __init__(self, name, location, title, comment, alt, aperture, shutter, focal_length, iso, \ timestamp_original, hash, hash_large, author, trackpoint, uuid, last_change=timetools.now(), published=None): self.name = name self.location = location self.title = title self.comment = comment self.alt = alt self.aperture = aperture self.shutter = shutter self.focal_length = focal_length self.iso = iso self.timestamp_original = timestamp_original self.hash = hash self.hash_large = hash_large self.author = author self.trackpoint = trackpoint self.uuid = uuid self.last_change = last_change self.published = published
class Trackpoint(Base): __tablename__ = 'trackpoint' id = Column('id', Integer, primary_key=True, autoincrement=True) track_id = Column('track_id', types.Integer, ForeignKey('track.id')) latitude = Column('latitude', types.Numeric(9, 7)) longitude = Column('longitude', types.Numeric(10, 7)) altitude = Column('altitude', types.Integer) velocity = Column('velocity', types.Integer) temperature = Column('temperature', types.Integer) direction = Column('direction', types.Integer) pressure = Column('pressure', types.Integer) timestamp = Column('timestamp', types.TIMESTAMP(timezone=False)) uuid = Column('uuid', postgresql.UUID, unique=True) images = relationship('Image', primaryjoin="Trackpoint.id==Image.trackpoint", order_by='desc(Image.timestamp_original)') __table_args__ = (UniqueConstraint('latitude', 'longitude', 'timestamp', name='trackpoint_lat_lon_timestamp'), {}) def __init__(self, track, latitude, longitude, altitude, velocity, temperature, direction, pressure, \ timestamp, uuid): self.track = track.id self.latitude = latitude self.longitude = longitude self.altitude = altitude self.velocity = velocity self.temperature = temperature self.direction = direction self.pressure = pressure self.timestamp = timestamp self.uuid = uuid @classmethod def get_trackpoint_by_lat_lon_timestamp(self, latitude, longitude, timestamp): try: trackpoint = DBSession.query(Trackpoint).filter( and_(Trackpoint.latitude == latitude, Trackpoint.longitude == longitude, Trackpoint.timestamp == timestamp)).one() return trackpoint except Exception as e: print(( 'Error retrieving trackpoint by lat({0}), lon({1}), time({2}) :\n {3} ' .format(latitude, longitude, timestamp, e))) return None @classmethod def get_trackpoint_by_uuid(self, uuid): try: trackpoint = DBSession.query(Trackpoint).filter( Trackpoint.uuid == uuid).one() return trackpoint except Exception as e: print(('Error retrieving trackpoint {0}: '.format(e))) return None
class TrackOld(Base): __tablename__ = 'track_old' id = Column("id", Integer, primary_key=True, autoincrement=True) date = Column("date", types.TIMESTAMP(timezone=False)) trkptnum = Column("trkptnum", Integer) distance = Column("distance", types.Numeric(11, 4)) timespan = Column("timespan", types.Interval) gencpoly_pts = Column("gencpoly_pts", types.UnicodeText) gencpoly_levels = Column("gencpoly_levels", types.UnicodeText) color = Column("color", types.CHAR(6), default='FF0000') maxlat = Column("maxlat", types.Numeric(9, 7)) maxlon = Column("maxlon", types.Numeric(10, 7)) minlat = Column("minlat", types.Numeric(9, 7)) minlon = Column("minlon", types.Numeric(10, 7)) json_0002 = Column("json_0002", Text) def __init__(self, date, trkptnum, distance, timespan, gencpoly_pts, gencpoly_levels, color, maxlat, maxlon, minlat, minlon, json_0002): self.date = date self.trkptnum = trkptnum self.distance = distance self.timespan = timespan self.gencpoly_pts = gencpoly_pts self.gencpoly_levels = gencpoly_levels self.color = color self.maxlat = maxlat self.maxlon = maxlon self.minlat = minlat self.minlon = minlon self.json_0002 = json_0002
def definetable(select, tablename): engine = create_engine('postgresql://*****:*****@localhost:5432/TomTom', echo=True) metadata = MetaData() if 'HD' in select: HD_Flow = Table(tablename, metadata, Column('record_time', types.TIMESTAMP(timezone=False)), Column('id', String(50), nullable=False), Column('openlr', String(64), nullable=False), Column('averageSpeed', Integer), Column('travelTime', Integer), Column('freeFlowSpeed', Integer), Column('freeFlowTravelTime', Integer)) if 'OpenLR' in select: openlrs = Table('OpenLR', metadata, Column('id', String(50), primary_key=True), Column('openlr', String(64), nullable=False), Column('source_lon', String(30), nullable=False), Column('source_lat', String(30), nullable=False), Column('target_lon', String(30), nullable=False), Column('target_lat', String(30), nullable=False), Column('bear1', Integer), Column('bear2', Integer), Column('fow1', String(25)), Column('fow2', String(25)), Column('frc1', String(4)), Column('frc2', String(4)), Column('lfrcnp1', String(4)), Column('dnp1', Integer)) metadata.create_all(engine) return 0
def _fixed_lookup_fixture(self): return [ (sqltypes.String(), sqltypes.VARCHAR()), (sqltypes.String(1), sqltypes.VARCHAR(1)), (sqltypes.String(3), sqltypes.VARCHAR(3)), (sqltypes.Text(), sqltypes.TEXT()), (sqltypes.Unicode(), sqltypes.VARCHAR()), (sqltypes.Unicode(1), sqltypes.VARCHAR(1)), (sqltypes.UnicodeText(), sqltypes.TEXT()), (sqltypes.CHAR(3), sqltypes.CHAR(3)), (sqltypes.NUMERIC, sqltypes.NUMERIC()), (sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)), (sqltypes.Numeric, sqltypes.NUMERIC()), (sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)), (sqltypes.DECIMAL, sqltypes.DECIMAL()), (sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)), (sqltypes.INTEGER, sqltypes.INTEGER()), (sqltypes.BIGINT, sqltypes.BIGINT()), (sqltypes.Float, sqltypes.FLOAT()), (sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()), (sqltypes.DATETIME, sqltypes.DATETIME()), (sqltypes.DateTime, sqltypes.DATETIME()), (sqltypes.DateTime(), sqltypes.DATETIME()), (sqltypes.DATE, sqltypes.DATE()), (sqltypes.Date, sqltypes.DATE()), (sqltypes.TIME, sqltypes.TIME()), (sqltypes.Time, sqltypes.TIME()), (sqltypes.BOOLEAN, sqltypes.BOOLEAN()), (sqltypes.Boolean, sqltypes.BOOLEAN()), ]
class ApplyPublish(Base): __tablename__ = "apply_publish" apply_id = Column(Integer, primary_key=True) stype_id = Column(Integer) version = Column(Integer) tpl_id = Column(Integer) pm_names = Column(String(128)) pm_emails = Column(String(128)) dev_names = Column(String(128)) dev_emails = Column(String(128)) publish_desc = Column(String(512)) apply_time = Column(types.TIMESTAMP()) status = Column(Integer) def __init__(self, apply_id, stype_id, version, tpl_id, pm_names, pm_emails, dev_names, dev_emails, publish_desc, status, apply_time): self.apply_id = apply_id self.stype_id = stype_id self.version = version self.tpl_id = tpl_id self.pm_names = pm_names self.pm_emails = pm_emails self.dev_names = dev_names self.dev_emails = dev_emails self.publish_desc = publish_desc self.status = status self.apply_time = apply_time def __repr__(self): return "<FeedKeys %s>" % (self.apply_id)
def insertHD(df, timestamp, tablename): engine = create_engine('postgresql://*****:*****@localhost:5432/TomTom') conn = engine.connect() metadata = MetaData() #alter table "HD_Flow_Real_Time_Traffic" add primary key (record_time, id); HD_Flow = Table(tablename, metadata, Column('record_time', types.TIMESTAMP(timezone=False)), Column('id', String(50), nullable=False), Column('openlr', String(64), nullable=False), Column('averageSpeed', Integer), Column('travelTime', Integer), Column('freeFlowSpeed', Integer), Column('freeFlowTravelTime', Integer)) for datarow in df.iterrows(): ti = datarow[1] ins = HD_Flow.insert() conn.execute(ins, record_time=timestamp, id=ti['id'], openlr=ti['openlr'], averageSpeed=empty(ti['averageSpeed']), travelTime=empty(ti['travelTime']), freeFlowSpeed=empty(ti['freeFlowSpeed']), freeFlowTravelTime=empty(ti['freeFlowTravelTime'])) conn.close() return 0
class ApplyNewTpl(Base): __tablename__ = "apply_new_tpl" apply_id = Column(Integer, primary_key=True) stype_id = Column(Integer) version = Column(Integer) from_tpl_id = Column(Integer) pm_names = Column(String(128)) pm_emails = Column(String(128)) dev_names = Column(String(128)) dev_emails = Column(String(128)) tpl_desc = Column(String(512)) tpl_text = Column(String(1024)) apply_time = Column(types.TIMESTAMP()) status = Column(Integer) def __init__(self, apply_id, stype_id, version, from_tpl_id, pm_names, pm_emails, dev_names, dev_emails, tpl_desc, tpl_text, status, apply_time): self.apply_id = apply_id self.stype_id = stype_id self.version = version self.from_tpl_id = from_tpl_id self.pm_names = pm_names self.pm_emails = pm_emails self.dev_names = dev_names self.dev_emails = dev_emails self.tpl_desc = tpl_desc self.tpl_text = tpl_text self.status = status self.apply_time = apply_time def __repr__(self): return "<FeedKeys %s>" % (self.apply_id)
class Log(Base): __tablename__ = 'log' id = Column("id", Integer, primary_key=True, autoincrement=True) infomarker = Column("infomarker", types.Integer, ForeignKey('trackpoint.id')) topic = Column("topic", types.UnicodeText) content = Column("content", types.UnicodeText) author = Column( Integer, ForeignKey('author.id', onupdate="CASCADE", ondelete="CASCADE")) etappe = Column( Integer, ForeignKey('etappe.id', onupdate="CASCADE", ondelete="CASCADE")) created = Column("created", types.TIMESTAMP(timezone=False), default=timetools.now()) published = Column("published", types.TIMESTAMP(timezone=False), default=timetools.now()) uuid = Column("uuid", postgresql.UUID, unique=True) image = relationship('Image', secondary=log_image_table, backref='logs') track = relationship('Track', secondary=log_track_table, backref='logs') def __init__(self, infomarker, topic, content, author, etappe, created, published, uuid): self.infomarker = infomarker self.topic = topic self.content = content self.author = author self.etappe = etappe self.created = created self.published = published self.uuid = uuid @classmethod def get_log_by_uuid(self, uuid): try: log = DBSession.query(Log).filter(Log.uuid == uuid).one() return log except Exception, e: print "Error retrieving log %s: ", e return None
class BookmarkTopic(Base): __tablename__ = 'bookmark_topics' __table_args__ = (sa.ForeignKeyConstraint(['bookmark_id'], [Bookmark.bookmark_id]), ) bookmark_id = sa.Column(UUIDType(), primary_key=True) topic = sa.Column(sa.String(100), nullable=False, primary_key=True) created_on = sa.Column(sa_types.TIMESTAMP(timezone=True), nullable=False, default=datetime.utcnow().replace(microsecond=0))
class Collector(Model): __tablename__ = 'collector' id = Column(types.Integer, primary_key=True) type = Column(types.String(), nullable=False) hash = Column(types.String()) last_update = Column(types.TIMESTAMP()) def __repr__(self): return f'<Collector {self.id} type={self.type} hash={self.hash}">'
class Etappe(Base): __tablename__ = 'etappe' id = Column(Integer, primary_key=True) start_date = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) end_date = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) uuid = Column("uuid", postgresql.UUID, unique=True) name = Column(Text) log = relationship('Log', backref='etappe_ref') track = relationship('Track', backref='etappe_ref') __table_args__ = (UniqueConstraint('start_date', 'end_date', name='etappe_start_end'), {}) def __init__(self, start_date=timetools.now(), end_date=timetools.now(), name=None, uuid=str(uuidlib.uuid4())): self.start_date = start_date self.end_date = end_date self.name = name self.uuid = uuid @classmethod def get_etappen(self): etappen = DBSession.query(Etappe).all() return etappen @classmethod def get_etappe_by_id(self, id): etappe = DBSession.query(Etappe).filter(Etappe.id == id).one() return etappe @classmethod def get_etappe_by_uuid(self, uuid): try: etappe = DBSession.query(Etappe).filter(Etappe.uuid == uuid).one() return etappe except Exception, e: print "Error retrieving etappe %s: ", e return None
class User(UserMixin, Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) user_name = Column(types.UnicodeText, unique=True) user_password = Column(types.UnicodeText) email = Column(types.UnicodeText) status = Column(postgresql.SMALLINT, nullable=False, default=1) security_code = Column(types.UnicodeText) last_login_date = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) registered_date = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) security_code_date = Column(types.TIMESTAMP(timezone=False), default=timetools.now()) @classmethod def get_user(self, login, request): try: query = request.dbsession.query(User) user = query.filter(User.user_name == login).one() return user except Exception as e: log.debug('Error retrieving user: "******" - {1}'.format(login, e)) return None @classmethod def get_users(self): users = DBSession.query(User).all() return users def verify_password(cls, password, request): #cls is used instead of self, see PEP-8 log.debug('verify_password with {0}, {1}'.format( password, cls.user_password)) try: if bcrypt.verify(password, cls.user_password): return True else: return False except Exception as e: log.debug('Error verifying password: {0}'.format(e)) return False
class Comment(Base): __tablename__ = 'comments' id = schema.Column(types.Integer, schema.Sequence('comment_seq_id', optional=True), primary_key=True) pageid = schema.Column(types.Integer, schema.ForeignKey('pages.id'), nullable=False) content = schema.Column(types.Text(), default=u'') name = schema.Column(types.Unicode(255)) email = schema.Column(types.Unicode(255), nullable=False) created = schema.Column(types.TIMESTAMP(), default=now)
def stdtype_to_sqltype(stdtype): import sqlalchemy.types as sqltypes if isinstance(stdtype, stdtypes.StringType): return sqltypes.VARCHAR(length=stdtype.max_len) if 0 < stdtype.max_len < 65536 else sqltypes.TEXT() if isinstance(stdtype, stdtypes.BoolType): return sqltypes.BOOLEAN() if isinstance(stdtype, stdtypes.DateType): return sqltypes.DATE() if stdtype.only_date else sqltypes.TIMESTAMP() if isinstance(stdtype, stdtypes.IntegerType): return sqltypes.BIGINT() if stdtype.length > 11 else sqltypes.INTEGER() if isinstance(stdtype, stdtypes.DecimalType): return sqltypes.DECIMAL() if isinstance(stdtype, stdtypes.ArrayType): return sqltypes.ARRAY(item_type=stdtype.item_type)
class BookmarkNote(Base): __tablename__ = 'bookmark_notes' __table_args__ = (sa.ForeignKeyConstraint(['bookmark_id'], [Bookmark.bookmark_id]), ) note_id = sa.Column(UUIDType(), primary_key=True, default=UUIDType.new_uuid) bookmark_id = sa.Column(UUIDType(), nullable=False) text = sa.Column(sa_types.Text(), nullable=False) author = sa.Column(sa.String(100), nullable=False) created_on = sa.Column(sa_types.TIMESTAMP(timezone=True), nullable=False, default=datetime.utcnow().replace(microsecond=0))
class TNote(Base): """Notes ORM class""" __tablename__ = 'notes' # These must align with the table defined using Alembic note_id = Column('note_id', types.String(26), primary_key=True) assoc_id = Column('assoc_id', types.String(128), nullable=False) subject = Column('subject', types.String(128), nullable=False) sub_type = Column('sub_type', types.String(128), nullable=False) note_val = Column('note_val', Text, nullable=False) verbosity = Column('verbosity', types.Integer, nullable=False) link_url = Column('link_url', Text, nullable=True) is_auth_link = Column('is_auth_link', types.Boolean, nullable=False) note_timestamp = Column('note_timestamp', types.TIMESTAMP(timezone=True), server_default=func.now())
class TplView(Base): __tablename__ = "tpl_view" tpl_id = Column(types.Integer, primary_key=True) view = Column(types.Integer, primary_key=True) template = Column(types.String(8192)) # update_time = Column(types.TIMESTAMP(), default=now()) update_time = Column(types.TIMESTAMP()) def __init__(self, tpl_id, view, template): self.tpl_id = tpl_id self.view = view self.template = template def __repr__(self): return "<TplView('%s')" % self.tpl_id
class LogOld(Base): __tablename__ = 'log_old' id = Column("id", Integer, primary_key=True, autoincrement=True) infomarker_id = Column("infomarker_id", types.Integer, ForeignKey('trackpoint.id')) topic = Column("topic", types.UnicodeText) content = Column("content", types.UnicodeText) createdate = Column("createdate", types.TIMESTAMP(timezone=False), default=now()) def __init__(self, topic, content, createdate): self.infomarker_id = infomarker_id self.topic = topic self.content = content self.createdate = createdate
class SwitchesState(Model): __tablename__ = 'switches' id = Column(types.Integer(), primary_key=True) key = Column(types.BINARY(length=16), nullable=False, unique=True) one = Column(types.Boolean(), nullable=False) two = Column(types.Boolean(), nullable=False) touched = Column(types.TIMESTAMP(timezone=True), nullable=False, server_default=sql.func.now(), onupdate=sql.func.now()) def __init__(self): self.key = uuid4().bytes self.one = False self.two = False
class Trackpoint(Base): __tablename__ = 'trackpoint' id = Column("id", Integer, primary_key=True, autoincrement=True) track_id = Column("track_id", types.Integer, ForeignKey('track.id')) latitude = Column("latitude", types.Numeric(9, 7)) longitude = Column("longitude", types.Numeric(10, 7)) altitude = Column("altitude", types.Integer) velocity = Column("velocity", types.Integer) temperature = Column("temperature", types.Integer) direction = Column("direction", types.Integer) pressure = Column("pressure", types.Integer) timestamp = Column("timestamp", types.TIMESTAMP(timezone=False)) uuid = Column("uuid", postgresql.UUID, unique=True) trackpoint_log = relationship('Log', backref='trackpoint_log_ref') trackpoint_img = relationship('Image', backref='trackpoint_img_ref') def __init__(self, track_id, latitude, longitude, altitude, velocity, temperature, direction, pressure, \ timestamp, uuid): self.track_id = track_id self.latitude = latitude self.longitude = longitude self.altitude = altitude self.velocity = velocity self.temperature = temperature self.direction = direction self.pressure = pressure self.timestamp = timestamp self.uuid = uuid @classmethod def get_trackpoint_by_lat_lon_time(self, latitude, longitude, timestamp): try: trackpoint = DBSession.query(Trackpoint).filter( and_(Trackpoint.latitude == latitude, Trackpoint.longitude == longitude, Trackpoint.timestamp == timestamp)).one() return trackpoint except Exception, e: print( "Error retrieving trackpoint by lat(%s), lon(%s), time(%s) :\n %s " ) % (latitude, longitude, timestamp, e) return None
class ApplyNewFeed(Base): __tablename__ = "apply_new_feed" apply_id = Column(Integer, primary_key=True) pm_names = Column(String(128)) pm_emails = Column(String(128)) dev_names = Column(String(128)) dev_emails = Column(String(128)) stype_desc = Column(String(512)) stype_text = Column(String(1024)) push_news = Column(Integer) push_mini = Column(Integer) news_merge_desc = Column(String(256)) mini_merge_desc = Column(String(256)) lifetime = Column(Integer) apply_time = Column(types.TIMESTAMP()) status = Column(Integer) def __init__(self, apply_id, pm_names, pm_emails, dev_names, dev_emails, stype_desc, stype_text, push_news, push_mini, news_merge_desc, mini_merge_desc, lifetime, status, apply_time): self.apply_id = apply_id self.pm_names = pm_names self.pm_emails = pm_emails self.dev_names = dev_names self.dev_emails = dev_emails self.stype_desc = stype_desc self.stype_text = stype_text self.push_news = push_news self.push_mini = push_mini self.news_merge_desc = news_merge_desc self.mini_merge_desc = mini_merge_desc self.lifetime = lifetime self.status = status self.apply_time = apply_time def __repr__(self): return "<FeedKeys %s>" % (self.apply_id)
class Imageinfo(Base): #old version with integrated flickrdata __tablename__ = 'imageinfo' id = Column("id", types.Integer, primary_key=True, autoincrement=True) log_id = Column("log_id", types.Integer, ForeignKey('log.id')) #photoset_id = Column("photoset_id", types.Integer, ForeignKey('photosets.id')) infomarker_id = Column("infomarker_id", types.Integer, ForeignKey('trackpoint.id')) trackpoint_id = Column("trackpoint_id", types.Integer, ForeignKey('trackpoint.id')) flickrfarm = Column("flickrfarm", types.VARCHAR(256)) flickrserver = Column("flickrserver", types.VARCHAR(256)) flickrphotoid = Column("flickrphotoid", types.VARCHAR(256)) flickrsecret = Column("flickrsecret", types.VARCHAR(256)) flickrdatetaken = Column("flickrdatetaken", types.TIMESTAMP(timezone=False)) flickrtitle = Column("flickrtitle", types.VARCHAR(256)) flickrdescription = Column("flickrdescription", types.UnicodeText) photohash = Column("photohash", types.VARCHAR(256)) photohash_990 = Column("photohash_990", types.VARCHAR(256)) imgname = Column("imgname", types.VARCHAR(128)) aperture = Column("aperture", types.VARCHAR(8)) shutter = Column("shutter", types.VARCHAR(64)) focal_length = Column("focal_length", types.VARCHAR(64)) iso = Column("iso", types.VARCHAR(8)) online = Column("online", types.Boolean, default=False, nullable=False) fullsize_online = Column("fullsize_online", types.Boolean, default=False, nullable=False) def __init__(self, flickrfarm, flickrserver, flickrphotoid, flickrsecret, flickrdatetaken, flickrtitle, flickrdescription): self.flickrfarm = flickrfarm self.flickrserver = flickrserver self.flickrphotoid = flickrphotoid self.flickrsecret = flickrsecret self.flickrdatetaken = flickrdatetaken self.flickrtitle = flickrtitle self.flickrdescription = flickrdescription
class TrackpointOld(Base): __tablename__ = 'trackpoint_old' id = Column("id", Integer, primary_key=True, autoincrement=True) track_id = Column("track_id", types.Integer, ForeignKey('track.id')) timezone_id = Column("timezone_id", types.Integer, ForeignKey('timezone.id')) country_id = Column("country_id", types.Integer, ForeignKey('country.iso_numcode')) latitude = Column("latitude", types.Numeric(9, 7)) longitude = Column("longitude", types.Numeric(10, 7)) altitude = Column("altitude", types.Integer) velocity = Column("velocity", types.Integer) temperature = Column("temperature", types.Integer) direction = Column("direction", types.Integer) pressure = Column("pressure", types.Integer) timestamp = Column("timestamp", types.TIMESTAMP(timezone=False)) infomarker = Column("infomarker", types.Boolean, default=False, nullable=False) location = Column("location", types.VARCHAR(256)) def __init__(self, track_id, timezone_id, country_id, latitude, longitude, altitude, velocity, temperature, direction, pressure, timestamp, infomarker, location): self.track_id = track_id self.timezone_id = timezone_id self.country_id = country_id self.latitude = latitude self.longitude = longitude self.altitude = altitude self.velocity = velocity self.temperature = temperature self.direction = direction self.pressure = pressure self.timestamp = timestamp self.informarker = infomarker self.location = location
def _fixed_lookup_fixture(self): return [ (sqltypes.String(), sqltypes.VARCHAR()), (sqltypes.String(1), sqltypes.VARCHAR(1)), (sqltypes.String(3), sqltypes.VARCHAR(3)), (sqltypes.Text(), sqltypes.TEXT()), (sqltypes.Unicode(), sqltypes.VARCHAR()), (sqltypes.Unicode(1), sqltypes.VARCHAR(1)), (sqltypes.UnicodeText(), sqltypes.TEXT()), (sqltypes.CHAR(3), sqltypes.CHAR(3)), (sqltypes.NUMERIC, sqltypes.NUMERIC()), (sqltypes.NUMERIC(10, 2), sqltypes.NUMERIC(10, 2)), (sqltypes.Numeric, sqltypes.NUMERIC()), (sqltypes.Numeric(10, 2), sqltypes.NUMERIC(10, 2)), (sqltypes.DECIMAL, sqltypes.DECIMAL()), (sqltypes.DECIMAL(10, 2), sqltypes.DECIMAL(10, 2)), (sqltypes.INTEGER, sqltypes.INTEGER()), (sqltypes.BIGINT, sqltypes.BIGINT()), (sqltypes.Float, sqltypes.FLOAT()), (sqltypes.TIMESTAMP, sqltypes.TIMESTAMP()), (sqltypes.DATETIME, sqltypes.DATETIME()), (sqltypes.DateTime, sqltypes.DATETIME()), (sqltypes.DateTime(), sqltypes.DATETIME()), (sqltypes.DATE, sqltypes.DATE()), (sqltypes.Date, sqltypes.DATE()), (sqltypes.TIME, sqltypes.TIME()), (sqltypes.Time, sqltypes.TIME()), (sqltypes.BOOLEAN, sqltypes.BOOLEAN()), (sqltypes.Boolean, sqltypes.BOOLEAN()), (sqlite.DATE(storage_format="%(year)04d%(month)02d%(day)02d", ), sqltypes.DATE()), (sqlite.TIME( storage_format="%(hour)02d%(minute)02d%(second)02d", ), sqltypes.TIME()), (sqlite.DATETIME(storage_format="%(year)04d%(month)02d%(day)02d" "%(hour)02d%(minute)02d%(second)02d", ), sqltypes.DATETIME()), ]
def upgrade(): op.create_table( 'notes', # ULID key for the note sa.Column('note_id', types.String(26), primary_key=True), # The supplied association id used for lookup sa.Column('assoc_id', types.String(128), nullable=False), # The supplied subject of the note (what is this note about?) sa.Column('subject', types.String(128), nullable=False), # The supplied type of the subject (what kind of thing is the subject?) sa.Column('sub_type', types.String(128), nullable=False), # The text value of the note sa.Column('note_val', sa.Text, nullable=False), # The numeric verbosity level of the note (1-5) sa.Column('verbosity', types.Integer, nullable=False), # An optional URL containing more info for the note sa.Column('link_url', sa.Text, nullable=True), # Boolean if the link requires a X-Auth-Token header sa.Column('is_auth_link', types.Boolean, nullable=False), # The creation timestamp for the note sa.Column('note_timestamp', types.TIMESTAMP(timezone=True), server_default=func.now()), )