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()),
)
