def upgrade():
op.create_table(
'aim_statuses',
sa.Column('id', sa.String(255), primary_key=True),
sa.Column('resource_type', sa.String(255), nullable=False),
sa.Column('resource_id', sa.Integer, nullable=False),
sa.Column('sync_status', sa.String(50), nullable=True),
sa.Column('sync_message', sa.TEXT, default=''),
sa.Column('health_score', sa.Integer),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('resource_type', 'resource_id',
name='uniq_aim_statuses_identity'),
sa.Index('idx_aim_statuses_identity', 'resource_type', 'resource_id'))
op.create_table(
'aim_faults',
sa.Column('status_id', sa.String(length=255), nullable=False),
sa.Column('fault_code', sa.String(25), nullable=False),
sa.Column('severity', sa.String(25), nullable=False),
sa.Column('description', sa.String(255), default=''),
sa.Column('cause', sa.String(255), default=''),
sa.Column('last_update_timestamp', sa.TIMESTAMP,
server_default=func.now(), onupdate=func.now()),
sa.Column('external_identifier', sa.String(255), primary_key=True),
sa.PrimaryKeyConstraint('external_identifier'),
sa.ForeignKeyConstraint(['status_id'],
['aim_statuses.id'],
ondelete='CASCADE'),
sa.Index('idx_aim_faults_status_id', 'status_id'))
def add(cls, **kwargs):
# Clean up the version.
version = parse_modversion(kwargs['version'])
# Grab a session
session = DBSession()
# Grab device record, if it already exists.
try:
obj = session.query(cls).filter(cls.hash == kwargs['hash']).one()
except:
obj = cls()
obj.date_added = func.now()
# Flag this as a KANG if necessary.
if version == None:
version = kwargs['version']
obj.kang = True
else:
obj.kang = False
# Populate the rest of the records.
obj.hash = kwargs['hash']
obj.name = kwargs['name']
obj.version = version
obj.version_raw = kwargs['version']
obj.country = kwargs['country']
obj.carrier_id = kwargs['carrier_id']
obj.date_updated = func.now()
logging.info("Saving: %s" % obj)
session.add(obj)
session.commit()
def upgrade():
op.create_table('locale',
sa.Column('id', sa.Integer(), nullable=False, server_default=sa.text("nextval('core_seq_general')")),
sa.Column('created', sa.DateTime(), nullable=False, server_default=func.now()),
sa.Column('version', sa.DateTime(), nullable=False, server_default=func.now()),
sa.Column('enabled', sa.Boolean(), nullable=False),
sa.Column('type', sa.String(), nullable=False),
sa.Column('label', sa.String(), nullable=False),
sa.Column('value', sa.String(), nullable=False),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('type', 'value')
)
connection = op.get_bind()
t = sa.text('''
INSERT INTO locale(enabled, type, label, value) VALUES
(true, 'lang', 'English', 'en'),
(true, 'lang', 'Français', 'fr'),
(true, 'lang', '中国', 'cn'),
(true, 'tz', 'Europe/Paris', 'Europe/Paris')
''')
connection.execute(t)
op.add_column('contact', sa.Column('lang_id', sa.Integer(), sa.ForeignKey('locale.id'), nullable=True))
op.add_column('contact', sa.Column('tz_id', sa.Integer(), sa.ForeignKey('locale.id'), nullable=True))
def upgrade():
op.create_table(
'aim_agents',
sa.Column('id', sa.String(255), primary_key=True),
sa.Column('agent_type', sa.String(255), nullable=False),
sa.Column('host', sa.String(255), nullable=False),
sa.Column('binary_file', sa.String(255), nullable=False),
sa.Column('admin_state_up', sa.Boolean, default=True, nullable=False),
sa.Column('heartbeat_timestamp', sa.TIMESTAMP,
server_default=func.now(), onupdate=func.now()),
sa.Column('beat_count', sa.Integer, default=0),
sa.Column('description', sa.String(255)),
sa.Column('version', sa.String(10)),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('agent_type', 'host',
name='uniq_agents0agent_type0host'))
op.create_table(
'aim_agent_to_tree_associations',
sa.Column('agent_id', sa.String(length=255), nullable=True),
sa.Column('tree_tenant_rn', sa.String(length=64), nullable=True),
sa.ForeignKeyConstraint(['tree_tenant_rn'],
['aim_tenant_trees.tenant_rn'],
ondelete='CASCADE'),
sa.ForeignKeyConstraint(['agent_id'], ['aim_agents.id'],
ondelete='CASCADE'),
sa.PrimaryKeyConstraint('agent_id', 'tree_tenant_rn'))
def upgrade_2(session, metadata, tables):
"""
Version 2 upgrade.
This upgrade adds a create_date and last_modified date to the songs table
"""
Column(u'create_date', types.DateTime(), default=func.now()).create(table=tables[u'songs'])
Column(u'last_modified', types.DateTime(), default=func.now()).create(table=tables[u'songs'])
class Agent(model_base.Base, model_base.HasId, model_base.AttributeMixin):
"""Represents agents running in aim deployments."""
__table_args__ = (sa.UniqueConstraint('agent_type',
'host',
name='uniq_agents0agent_type0host'),
model_base.Base.__table_args__)
__tablename__ = 'aim_agents'
agent_type = sa.Column(sa.String(255), nullable=False)
host = sa.Column(sa.String(255), nullable=False)
binary_file = sa.Column(sa.String(255), nullable=False)
admin_state_up = sa.Column(sa.Boolean, default=True, nullable=False)
heartbeat_timestamp = sa.Column(sa.TIMESTAMP,
server_default=func.now(),
onupdate=func.now())
description = sa.Column(sa.String(255))
beat_count = sa.Column(sa.Integer, default=0)
version = sa.Column(sa.String, nullable=False)
hash_trees = orm.relationship(tree_model.AgentToHashTreeAssociation,
backref='agents',
cascade='all, delete-orphan',
lazy="joined")
def set_hash_trees(self, session, trees, **kwargs):
if trees is None:
return
keep = []
trees = set(trees)
for curr in self.hash_trees:
if curr.tree_root_rn in trees:
keep.append(curr)
trees.remove(curr.tree_root_rn)
self.hash_trees = keep
if trees:
LOG.debug("Adding trees for agent %s: %s" % (self.id, trees))
with session.begin(subtransactions=True):
for tree in trees:
self.tree_exists(session, tree)
# Check whether the current object already has an ID, use
# the one passed in the getter otherwise.
db_obj = tree_model.AgentToHashTreeAssociation(
agent_id=self.id or kwargs.get('id'), tree_root_rn=tree)
self.hash_trees.append(db_obj)
def get_hash_trees(self, session):
# Only return the trees' identifier
return [getattr(x, 'tree_root_rn') for x in self.hash_trees or []]
def tree_exists(self, session, root_rn):
try:
session.query(tree_model.ConfigTree).filter(
tree_model.ConfigTree.root_rn == root_rn).one()
except sql_exc.NoResultFound:
raise exc.HashTreeNotFound(root_rn=root_rn)
def upgrade():
op.create_table(
'aim_apic_assignment',
sa.Column('apic_host', sa.String(128), nullable=False),
sa.Column('aim_aid_id', sa.String(64), nullable=False),
sa.Column('last_update_timestamp',
sa.TIMESTAMP,
server_default=func.now(),
onupdate=func.now()),
sa.Column('epoch', sa.BigInteger(), nullable=False,
server_default='0'), sa.PrimaryKeyConstraint('apic_host'))
class M_FileServers(Base):
__tablename__ = 'fileservers'
FileServer_ID = Column(INTEGER, primary_key=True, index=True, autoincrement=True)
FileServer_Name = Column(VARCHAR(200), nullable=True)
FileServer_IpAddress = Column(VARCHAR(15), nullable=True)
FileServer_OS = Column(VARCHAR(200), nullable=True)
FileServer_CreateDate = Column(TIMESTAMP, nullable=True, server_default = func.now())
FileServer_ModifyDate = Column(TIMESTAMP, nullable=True, server_default = func.now())
FileServer_ExpireDate = Column(TIMESTAMP, nullable=True, server_default = func.now())
FileServer_IsValid = Column(INTEGER(11), nullable=True)
FileServer_Group_ID = Column(INTEGER(11), nullable=True)
FileServer_IsMainGroup = Column(INTEGER(11), nullable=True)
def upgrade_2(session, metadata):
"""
Version 2 upgrade.
This upgrade adds a create_date and last_modified date to the songs table
"""
op = get_upgrade_op(session)
songs_table = Table('songs', metadata, autoload=True)
if 'create_date' not in [col.name for col in songs_table.c.values()]:
op.add_column('songs', Column('create_date', types.DateTime(), default=func.now()))
op.add_column('songs', Column('last_modified', types.DateTime(), default=func.now()))
else:
log.warning('Skipping upgrade_2 step of upgrading the song db')
def upgrade_2(session, metadata):
"""
Version 2 upgrade.
This upgrade adds a create_date and last_modified date to the songs table
"""
op = get_upgrade_op(session)
songs_table = Table('songs', metadata, autoload=True)
if 'create_date' not in [col.name for col in songs_table.c.values()]:
op.add_column('songs', Column('create_date', types.DateTime(), default=func.now()))
op.add_column('songs', Column('last_modified', types.DateTime(), default=func.now()))
else:
log.warning('Skipping upgrade_2 step of upgrading the song db')
class Game(Base):
__tablename__ = "game"
id_game = Column(Integer, primary_key=True)
version_game = Column(Integer, nullable=False)
info_game = Column(JSON, nullable=False)
created_game = Column(DateTime, default=func.now(), nullable=False)
modified_game = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
def __init__(self, info, version):
self.info_game = info
self.version_game = version
class Base(object):
""" Declarative base for ORM. """
# Don't set timezone=True on DateTime column, the DB should be running as UTC as is the API.
# This way we don't have to deal with aware datetime objects
modified_at = sa.Column(sa.DateTime, default=sa_func.now(), nullable=False,
onupdate=sa_func.now())
# server_default=sa.text('NULL ON UPDATE CURRENT_TIMESTAMP')) # MySQL
_cached_tablename = None
def __repr__(self):
"""
Custom representation for this Model. Data that isn't currently loaded won't be fetched
from DB, shows <not loaded> instead.
Based on:
https://github.com/kvesteri/sqlalchemy-utils/blob/0.32.14/sqlalchemy_utils/models.py#L41
Customized to use our __str__ for fully qualified class name.
:return str: python representation for creating this model.
"""
state = sa.inspect(self)
field_reprs = []
fields = state.mapper.columns.keys()
for key in fields:
value = state.attrs[key].loaded_value
if value == NO_VALUE:
value = '<not loaded>'
else:
value = repr(value)
field_reprs.append('='.join((key, value)))
return "{0}({1})".format(self, ', '.join(field_reprs))
def __str__(self):
"""
Custom informal string representation with complete model path
:return str: *fully qualified* name of this model.
"""
return self.__class__.__module__ + '.' + self.__class__.__name__
@declared_attr
def __tablename__(cls): # pylint: disable=no-self-argument
"""
Convert the TitleCase class names to lower underscore names for the database table name.
:return str: name for table
"""
if cls._cached_tablename is None:
cls._cached_tablename = utilities.camel_to_delimiter_separated(cls.__name__) # pylint: disable=no-member
return cls._cached_tablename
def add_vehicle_txn(session, vehicle_type, longitude, latitude, battery):
"""
Insert a row into the vehicles table.
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_type {String} -- The vehicle's type.
Returns:
vehicle_id {UUID} -- The vehicle's new UUID
"""
vehicle_id = uuid4() # Generate new uuid
current_time = func.now() # Current time on database
new_row = Vehicle(id=str(vehicle_id),
last_longitude=longitude,
last_latitude=latitude,
last_checkin=current_time,
in_use=False,
vehicle_type=vehicle_type,
battery=battery)
# TO COMPLETE THE "ADD VEHICLES" LAB, WRITE THE COMMAND TO INSERT THE NEW
# ROW HERE.
# YOU WILL NEED TO USE THE `session` OBJECT.
# YOU MAY FIND THIS LINK IN THE SQLALCHEMY DOCS USEFUL:
# https://docs.sqlalchemy.org/en/13/orm/session_api.html#sqlalchemy.orm.session.Session.add
session.add(new_row)
return str(vehicle_id) # Return the new id.
class Indicator(Base):
__tablename__ = "indicators"
id = Column(Integer, primary_key=True)
indicator = Column(UnicodeText, index=True)
group = Column(Text)
provider = Column(Text)
firsttime = Column(DateTime)
lasttime = Column(DateTime)
tags = Column(Text)
created_at = Column(DateTime, default=func.now())
def __init__(self, indicator=None, group='everyone', provider=None, firsttime=None, lasttime=None, tags=None):
self.indicator = indicator
self.group = group
self.provider = provider
self.firsttime = firsttime
self.lasttime = lasttime
self.tags = tags
if isinstance(group, list):
self.group = group[0]
if isinstance(self.tags, list):
self.tags.sort()
self.tags = ','.join(self.tags)
if self.lasttime and isinstance(self.lasttime, basestring):
self.lasttime = arrow.get(self.lasttime).datetime
if self.firsttime and isinstance(self.firsttime, basestring):
self.firsttime = arrow.get(self.firsttime).datetime
class LoginSessionTable(Base):
__tablename__ = 'LoginSessionTable'
LoginSessionID = Column(Integer, primary_key=True)
SpecSysN = Column(Integer, ForeignKey('machinetable.SpecSysN'))
analyses = relationship('AnalysesTable', backref='login_session')
UserID = Column(Integer, default=1)
SessionStart = Column(DateTime, default=func.now())
def stock_checkpullthru(stockid, maxtime):
"""Did this stock item require pulling through?"""
return s.execute(
select([func.now() - func.max(StockOut.time) > maxtime]).\
where(StockOut.stockid == stockid).\
where(StockOut.removecode_id.in_(['sold', 'pullthru']))
).scalar()
class Trial(ORMBase):
__tablename__ = 'trials'
id = Column(Integer, primary_key=True)
experiment_id = Column(Integer, ForeignKey('experiments.id'), nullable=False)
run_number = Column(Integer, nullable=False)
outcome = Column(Float, nullable=False)
parameter_configs = relationship('ParameterConfig')
timestamp = Column(TIMESTAMP, server_default=func.now(), onupdate=func.current_timestamp())
obj_parameters = Column(JSON, nullable=False)
def __repr__(self):
return (
f'Trial('
f'experiment_id={self.experiment_id}, run_number={self.run_number}, outcome={self.outcome}, '
f'timestamp={self.timestamp!r}, parameter_configs={self.parameter_configs!r}), '
f'objective_parameters={self.obj_parameters!r}'
)
def asdict(self):
return {
'experiment_id': self.experiment_id,
'run_number': self.run_number,
'outcome': self.outcome,
'parameter_configs': [config.asdict() for config in self.parameter_configs],
'timestamp': self.timestamp,
'obj_parameters': self.obj_parameters
}
class TimestampMixin(object):
"""
Model MixIn to add created and modified fields.
"""
created = Column(DateTime, default=func.now())
modified = Column(DateTime, onupdate=datetime.now)
def get_article_query(cls, cids=[], limit=10):
query = (cls.query
.filter(cls.cid.in_(cids))
.filter(cls.date_published <= func.now())
.order_by(ArticleModel.date_published.desc())
.limit(limit))
return query
class AttachedTag(BaseMeta):
__table_args__ = (UniqueConstraint('target_id', 'tag_id'), )
id = Column(Integer, primary_key=True, autoincrement=True)
tag_id = Column(Integer, ForeignKey('Tag.id'), nullable=False)
target_id = Column(Integer,
ForeignKey('Base.uid', ondelete='cascade'),
nullable=False)
creator_id = Column(Integer,
ForeignKey('User.uid'),
nullable=True,
default=None)
created_at = Column(DateTime, nullable=False, default=func.now())
area = Column(JSONEncodedDict, nullable=True, default=None)
tag = relationship('Tag', backref='attached_list')
target = relationship('Base',
backref=backref(
'tags',
order_by=tag_id,
cascade='all, delete, delete-orphan'))
creator = relationship('User', primaryjoin=(creator_id == User.uid))
@property
def name(self):
return self.tag.name
def add_vehicle_txn(session, longitude, latitude, battery, vehicle_info):
"""
Insert a row into the vehicles table, and one into location_history.
Arguments:
session {.Session} -- The active session for the database connection.
longitude {Float} -- Longitude of the vehicle.
Latitude {Float} -- Latitude of the vehicle.
battery {Int} -- Battery percantage remaining.
vehicle_info {dict} -- Information on the vehicle's info.
Returns:
{dict} -- The vehicle's new UUID and the location_history row's new
UUID as {'vehicle_id': <UUID>, 'location_history_id': <UUID>}
"""
vehicle_id = uuid4()
current_time = func.now()
location_history_id = uuid4()
new_vehicle_row = Vehicle(id=str(vehicle_id),
in_use=False,
vehicle_info=vehicle_info,
battery=battery)
new_location_history_row = LocationHistory(id=str(location_history_id),
vehicle_id=str(vehicle_id),
longitude=longitude,
latitude=latitude,
ts=current_time)
session.add(new_vehicle_row)
session.flush() # can't let the next row get inserted first.
session.add(new_location_history_row)
return {"vehicle_id": str(vehicle_id),
"location_history_id": str(location_history_id)}
def stockcheck(request, info, session):
buylist = []
depts = session.query(Department).order_by(Department.id).all()
if request.method == "POST":
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd["weeks_ahead"] * 7)
behind = datetime.timedelta(days=cd["months_behind"] * 30.4)
min_sale = cd["minimum_sold"]
dept = int(cd["department"])
q = (
session.query(StockType, func.sum(StockOut.qty) / behind.days)
.join(StockItem)
.join(StockOut)
.options(lazyload(StockType.department))
.options(lazyload(StockType.unit))
.options(undefer(StockType.instock))
.filter(StockOut.removecode_id == "sold")
.filter((func.now() - StockOut.time) < behind)
.filter(StockType.dept_id == dept)
.having(func.sum(StockOut.qty) / behind.days > min_sale)
.group_by(StockType)
)
r = q.all()
buylist = [(st, "{:0.1f}".format(sold), "{:0.1f}".format(sold * ahead.days - st.instock)) for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ("stockcheck.html", {"form": form, "buylist": buylist})
class KahaResource(db.Model):
__tablename__ = 'kaharesource'
__table_args__ = (
Index('created_kr_idx', 'created'),
Index('updated_kr_idx', 'updated'),
Index('district_kr_idx', 'district'),
)
resource_id = Column(Integer, primary_key=True, autoincrement=True)
uuid = Column(String(50), unique=True)
resource_for = Column(String(10))
title = Column(String(500))
district = Column(String(150))
district_code = Column(Integer())
vdc_code = Column(Integer())
tole = Column(String(150))
contactname = Column(String(200))
contactnumber = Column(String(100))
description = Column(String)
is_active = Column(Integer)
is_deleted = Column(Integer)
location_id = Column(Integer, ForeignKey('kaha_location.location_id'))
created = Column(DateTime, default=func.now())
updated = Column(DateTime, onupdate=func.utc_timestamp())
data_source = relationship('KahaResourceSource',
backref='resource',
lazy='dynamic')
props = relationship('KahaResourceProperty',
backref='resource',
lazy='dynamic')
types = relationship('KahaResourceType',
backref='resource',
lazy='dynamic')
class Log(Base, JSONSerializable):
__tablename__ = 'log'
id = Column(Integer, primary_key=True)
hardware_id = Column(Integer, ForeignKey('device.id'))
timestamp = Column(DateTime, default=func.now())
value = Column(String(40))
def __init__(self, value, hardware_id):
'''initialize the row in log table with value and device id'''
#never expect the user to directly call this function, the add_value function in devices class should be the only place this is called from
self.hardware_id = hardware_id
self.value = value
def __repr__(self):
return "<Log(id:%s, hardware_id:%s, value:%s, timestamp%s)>" % (str(
self.id), str(self.hardware_id), self.value, str(self.timestamp))
def json(self):
return {
'id': self.id,
'hardware_id': str(self.hardware_id),
'timestamp': str(self.timestamp),
'value': self.value
}
def val_json(self):
'''custom json wrapper that just returns the value and timestamp in dict format'''
return {'value': self.value, 'timestamp': self.timestamp}
def start_ride_txn(session, vehicle_id):
"""
Start a vehicle ride (or continue if the vehicle is already in use).
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_id {String} -- The vehicle's `id` column.
"""
# find the row where we want to start the ride.
# SELECT * FROM vehicles WHERE id = <vehicle_id> AND in_use = false
# LIMIT 1;
vehicle = session.query(Vehicle).filter(Vehicle.id == vehicle_id). \
filter(Vehicle.in_use == False).first()
if vehicle is None:
return None
# perform the update
# UPDATE vehicles SET in_use = true, last_checkin = now()
# WHERE id = <vehicle_id> AND in_use = false
# LIMIT 1;
vehicle.in_use = True
vehicle.last_checkin = func.now()
return True # Just making it explicit that this worked.
class proc_ActionTable(Base, BaseMixin):
create_date = Column(DateTime, default=func.now())
session = stringcolumn()
user_id = foreignkey('gen_UserTable')
action = Column(BLOB)
blank_histories = relationship('proc_BlanksHistoryTable', backref='action')
fit_histories = relationship('proc_FitHistoryTable', backref='action')
class Patient(Base):
__tablename__ = 'patient'
# default= is equivalent to ColumnDefault()
# server_default= is equivalent to DefaultClause()
id = Column(
BigInteger(),
default=text("round(3.14159)"),
primary_key=True,
autoincrement=False
)
received_timestamp = Column(
TIMESTAMP,
server_default=func.now(),
nullable=False
)
some_number = Column(
Integer,
DefaultClause('3'),
nullable=False
)
scalar_number = Column(
Integer,
default=3,
nullable=False
)
pyfunc_test = Column(
Integer,
default=give_me_three,
nullable=False
)
def stockcheck(request, info, session):
buylist = []
depts = session\
.query(Department)\
.order_by(Department.id)\
.all()
if request.method == 'POST':
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd['weeks_ahead'] * 7)
behind = datetime.timedelta(days=cd['months_behind'] * 30.4)
min_sale = cd['minimum_sold']
dept = int(cd['department'])
r = session\
.query(StockType, func.sum(StockOut.qty) / behind.days)\
.join(StockItem)\
.join(StockOut)\
.options(lazyload(StockType.department),
lazyload(StockType.unit),
undefer(StockType.instock))\
.filter(StockOut.removecode_id == 'sold')\
.filter((func.now() - StockOut.time) < behind)\
.filter(StockType.dept_id == dept)\
.having(func.sum(StockOut.qty) / behind.days > min_sale)\
.group_by(StockType)\
.all()
buylist = [(st, '{:0.1f}'.format(sold),
'{:0.1f}'.format(sold * ahead.days - st.instock))
for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ('stockcheck.html', {'form': form, 'buylist': buylist})
class Share(db.Model):
"""
Represents a Post being displayed to a Contact, for example in their feed
or on their wall.
Fields:
contact - the Contact the Post is being displayed to
contact_id - the database primary key of the above
post - the Post that is being shared with <contact>
post_id - the database primary key of the above
public - whether the post is shown on the user's public wall
hidden - whether the author has hidden the post from their feed
shared_at - the DateTime the Post was shared with the Contact (that
is, the Share creation date)
"""
__tablename__ = 'shares'
contact_id = Column(Integer, ForeignKey('contacts.id'), primary_key=True)
post_id = Column(Integer,
ForeignKey('posts.id'),
primary_key=True,
index=True)
public = Column(Boolean, nullable=False)
hidden = Column(Boolean, nullable=False, default=False)
shared_at = Column(DateTime(timezone=True),
nullable=False,
default=func.now())
contact = relationship(Contact, backref="feed", order_by='Share.shared_at')
@classmethod
def get_for_posts(cls, post_ids):
return db.session.query(cls).filter(cls.post_id.in_(post_ids))
class flux_HistoryTable(Base, BaseMixin):
irradiation_position_id = foreignkey('irrad_PositionTable')
selected = relationship('gen_LabTable',
backref='selected_flux_history',
uselist=False)
flux = relationship('flux_FluxTable', backref='history', uselist=False)
create_date = Column(DateTime, default=func.now())
def stock_checkpullthru(stockid, maxtime):
"""Did this stock item require pulling through?"""
return s.execute(
select([func.now() - func.max(StockOut.time) > maxtime]).\
where(StockOut.stockid == stockid).\
where(StockOut.removecode_id.in_(['sold', 'pullthru']))
).scalar()
def _generate_mapping(table):
"""Generate an ORM mapping class from an entry in table_schemas.
"""
name = table.capitalize()
schema = table_schemas[table]
table_args = {}
if isinstance(schema[0], str):
table_args['comment'] = schema[0]
schema = schema[1:]
props = {
'__tablename__': table,
'__table_args__': table_args,
'id': Column(Integer, primary_key=True),
'time_created': Column(DateTime, default=func.now()),
'time_modified': Column(DateTime, onupdate=func.current_timestamp()),
'meta': Column(JSONB),
}
for column in schema:
colname, coltype = column[:2]
kwds = {} if len(column) < 4 else column[3]
kwds['comment'] = None if len(column) < 3 else column[2]
if coltype not in _coltypes:
if not coltype.endswith('.id'):
raise ValueError("Unrecognized column type %s" % coltype)
props[colname] = Column(Integer, ForeignKey(coltype), **kwds)
else:
ctyp = _coltypes[coltype]
props[colname] = Column(ctyp, **kwds)
return type(name, (ORMBase, ), props)
def login_perform(request, user, location=None, remember_me=False):
"""
Perform login action.
:param pyramid.request.Request request: a request object
:param pyramid_fullauth.models.User user: a user object
:param str location: where user should be redirected after login
:param bool remember_me: if True set cookie max_age to one month (60 * 60 * 24 * 30 seconds)
:returns: redirect exception
:rtype: pyramid.httpexceptions.HTTPFound
"""
user.logged_at = func.now()
if remember_me: # if remember in POST set cookie timeout to one from configure
headers = remember(
request,
user.id,
max_age=request.registry['config'].fullauth.login.cookie_max_age)
else:
headers = remember(request, user.id)
if not location:
location = '/'
# this remembers user immediately, without the need to redirect (see below)
request.response.headers.extend(headers)
return HTTPFound(location=location, headers=request.response.headers)
class proc_InterpretedAgeGroupHistoryTable(Base, BaseMixin):
project_id = foreignkey('gen_ProjectTable')
name = stringcolumn(80)
create_date = Column(DateTime, default=func.now())
interpreted_ages = relationship('proc_InterpretedAgeGroupSetTable',
backref='group')
class History(object):
@declared_attr
def analysis_id(self):
return foreignkey('meas_AnalysisTable')
create_date = Column(DateTime, default=func.now())
user = stringcolumn()
def start_ride_txn(session, vehicle_id, user_email):
"""
Start a vehicle ride (or continue if the vehicle is already in use).
Arguments:
session {.Session} -- The active session for the database connection.
vehicle_id {String} -- The vehicle's `id` column.
"""
# find the row where we want to start the ride.
# SELECT * FROM vehicles WHERE id = <vehicle_id> AND in_use = false
# LIMIT 1;
vehicle = session.query(Vehicle).filter(Vehicle.id == vehicle_id). \
filter(Vehicle.in_use == False).first()
if vehicle is None:
return None
# SELECT * FROM location_history WHERE vehicle_id = <vehicle_id>
# ORDER BY ts DESC LIMIT 1;
last_chx = session.query(LocationHistory). \
filter(LocationHistory.vehicle_id ==
vehicle_id). \
order_by(LocationHistory.ts.desc()). \
first()
new_location_history_id = str(uuid4())
new_timestamp = func.now()
new_location_history_row = LocationHistory(id=new_location_history_id,
vehicle_id=vehicle_id,
longitude=last_chx.longitude,
latitude=last_chx.latitude,
ts=new_timestamp)
new_ride_id = str(uuid4())
new_ride_row = Ride(id=new_ride_id,
vehicle_id=vehicle_id,
user_email=user_email,
start_ts=new_timestamp,
end_ts=None)
# UPDATE vehicles SET in_use = true WHERE vehicles.id = <vehicle_id>
vehicle.in_use = True
vehicle.last_checkin = func.now()
session.add(new_location_history_row)
session.flush()
session.add(new_ride_row)
return True # Just making it explicit that this worked.
class proc_DataReductionTagTable(Base, BaseMixin):
name = stringcolumn(140)
create_date = Column(TIMESTAMP, default=func.now())
comment = Column(BLOB)
user_id = foreignkey('gen_UserTable')
analyses = relationship('proc_DataReductionTagSetTable', backref='tag')
analysis = relationship('meas_AnalysisTable', backref='data_reduction_tag')
class gen_SensitivityTable(Base, BaseMixin):
mass_spectrometer_id = foreignkey('gen_MassSpectrometerTable')
sensitivity = Column(Float(32))
create_date = Column(DateTime, default=func.now())
user = stringcolumn()
note = Column(BLOB)
extractions = relationship('meas_ExtractionTable', backref='sensitivity')
class SampleLoadingTable(Base):
__tablename__ = 'sampleloadingtable'
SampleLoadingID = Column(Integer, primary_key=True)
SampleHolder = Column(String(40))
SpecSysN = Column(Integer)
LoadingDate = Column(DateTime, default=func.now())
analyses = relationship('AnalysesTable', backref='sample_loading')
def thread_modified(self):
"""
Mark this thread as having been modified. This makes it "bubble up" in
contact feeds. Requires the caller commit the session.
"""
post = self.root()
post.thread_modified_at = func.now()
if post.id != self.id:
db.session.add(post)
def __init__(cls, name, bases, dct):
print("cls: {}, name: {}".format(str(cls), str(name)))
print(cls.__dict__.keys())
# if '_use_pow_schema_attrs' not in cls.__dict__:
# print("has _use_pow_schema_attrs = No")
# else:
# print("has _use_pow_schema_attrs = Yes")
from sqlalchemy.sql.expression import func
if hasattr(cls, '_use_pow_schema_attrs'):
if getattr(cls,'_use_pow_schema_attrs'):
setattr(cls,"id", Column(Integer, primary_key=True))
setattr(cls,"created_at",Column(DateTime, default=func.now()))
setattr(cls, "last_updated", Column(DateTime, onupdate=func.now(), default=func.now()))
else:
print("not adding pow schema attrs")
else:
setattr(cls,"id", Column(Integer, primary_key=True))
setattr(cls,"created_at",Column(DateTime, default=func.now()))
setattr(cls, "last_updated", Column(DateTime, onupdate=func.now(), default=func.now()))
super().__init__(name, bases, dct)
def update_ed_stats_for_precached(rec_id):
'''
update current timestamp to last_pre_cached field
:param string tenant: name of the tenant
:param string state_code: stateCode of the state
'''
with StatsDBConnection() as connector:
udl_stats = connector.get_table(UdlStatsConstants.UDL_STATS)
stmt = udl_stats.update(values={udl_stats.c.last_pre_cached: func.now()}).where(udl_stats.c.rec_id == rec_id)
connector.execute(stmt)
def upgrade():
op.alter_column('product_order', 'date',
existing_type=sa.DATE(),
nullable=False)
op.create_table('retailer_contact',
sa.Column('id', sa.Integer(), sa.Sequence('core_seq_general'), nullable=False),
sa.Column('created', sa.DateTime, nullable=False, server_default=func.now()),
sa.Column('version', sa.DateTime, nullable=False, server_default=func.now(), onupdate=func.now()),
sa.Column('enabled', sa.Boolean, nullable=False, server_default=sa.text('true')),
sa.Column('retailer_id', sa.Integer(), nullable=False),
sa.Column('contact_id', sa.Integer(), nullable=False),
sa.ForeignKeyConstraint(['contact_id'], ['contact.id'], ),
sa.ForeignKeyConstraint(['retailer_id'], ['retailer.id'], ),
sa.PrimaryKeyConstraint('id')
)
op.alter_column('retailer_contact', 'id', server_default=sa.text("nextval('core_seq_general')"))
connection = op.get_bind()
t = text('SELECT id, contact_firstname, contact_lastname FROM retailer')
for retailer_id, contact_firstname, contact_lastname in connection.execute(t):
t = text('''
INSERT INTO contact(id, created, version, enabled, firstname, lastname, email)
VALUES (nextval('core_seq_general'), now(), now(), true, :contact_firstname, :contact_lastname, '')
RETURNING id
''').bindparams(
contact_firstname=contact_firstname,
contact_lastname=contact_lastname
)
result = connection.execute(t).first()
t = text('''
INSERT INTO retailer_contact(retailer_id, contact_id)
VALUES (:retailer_id, :contact_id)
''').bindparams(
contact_id=result[0],
retailer_id=retailer_id
)
connection.execute(t)
op.drop_column('retailer', 'contact_lastname')
op.drop_column('retailer', 'contact_firstname')
def update_ed_stats_for_prepdf(rec_id):
'''
Sets current timestamp to the `last_pdf_generated` field for row with
specified `rec_id`
:param rec_id: record id
'''
with StatsDBConnection() as connector:
udl_stats = connector.get_table(UdlStatsConstants.UDL_STATS)
connector.execute(udl_stats
.update(values={udl_stats.c.last_pdf_task_requested: func.now()})
.where(udl_stats.c.rec_id == rec_id))
def render_latest(language='cn'):
query = ArticleModel.query.join(ChannelModel,
ChannelModel.id==ArticleModel.cid)
if language == 'cn':
query = query.filter("channel.name !~ '[a-zA-Z\b]+'")
else:
query = query.filter("channel.name ~ '[a-zA-Z\b]+'")
articles = (query.filter(ArticleModel.date_published<=func.now())
.order_by(ArticleModel.date_published.desc())
.limit(10))
return Markup(render_template_string(latest_str,
language=language,
articles=articles))
def upgrade():
op.create_table(
'aim_action_logs',
sa.Column('id', sa.BigInteger().with_variant(sa.Integer(), 'sqlite'),
autoincrement=True),
sa.Column('uuid', sa.String(64)),
sa.Column('root_rn', sa.String(64), nullable=False),
sa.Column('action', sa.String(25), nullable=False),
sa.Column('object_type', sa.String(50), nullable=False),
sa.Column('object_dict', sa.LargeBinary(length=2 ** 24),
nullable=True),
sa.Column('timestamp', sa.TIMESTAMP, server_default=func.now()),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('uuid', name='uniq_aim_alogs_uuid'),
sa.Index('idx_aim_action_logs_rn', 'root_rn'),
sa.Index('idx_aim_action_logs_uuid', 'uuid'))
op.add_column('aim_tenant_trees',
sa.Column('needs_reset', sa.Boolean,
server_default=sa.literal(False)))
def stockcheck(request, info, session):
buylist = []
depts = session\
.query(Department)\
.order_by(Department.id)\
.all()
if request.method == 'POST':
form = StockCheckForm(depts, request.POST)
if form.is_valid():
cd = form.cleaned_data
ahead = datetime.timedelta(days=cd['weeks_ahead'] * 7)
behind = datetime.timedelta(days=cd['months_behind'] * 30.4)
min_sale = cd['minimum_sold']
dept = int(cd['department'])
r = session\
.query(StockType, func.sum(StockOut.qty) / behind.days)\
.join(StockItem)\
.join(StockOut)\
.options(lazyload(StockType.department),
lazyload(StockType.unit),
undefer(StockType.instock))\
.filter(StockOut.removecode_id == 'sold')\
.filter((func.now() - StockOut.time) < behind)\
.filter(StockType.dept_id == dept)\
.having(func.sum(StockOut.qty) / behind.days > min_sale)\
.group_by(StockType)\
.all()
buylist = [(st, '{:0.1f}'.format(sold),
'{:0.1f}'.format(sold * ahead.days - st.instock))
for st, sold in r]
buylist.sort(key=lambda l: float(l[2]), reverse=True)
else:
form = StockCheckForm(depts)
return ('stockcheck.html', {
'nav': [("Buying list", info.reverse("tillweb-stockcheck"))],
'form': form,
'buylist': buylist,
})
def login_perform(request, user, location=None, remember_me=False):
'''
Performs login action
:param pyramid.request.Request request: a request object
:param pyramid_fullauth.models.User user: a user object
:param str location: where user should be redirected after login
:param bool remember_me: if True set cookie max_age to one month (60 * 60 * 24 * 30 seconds)
:returns: redirect exception
:rtype: pyramid.httpexceptions.HTTPFound
'''
user.logged_at = func.now()
if remember_me: # if remember in POST set cookie timeout to one from configure
headers = remember(request, user.id, max_age=request.config.fullauth.login.cookie_max_age)
else:
headers = remember(request, user.id)
if not location:
location = '/'
# this remembers user immediately, without the need to redirect (see below)
request.response.headers.extend(headers)
return HTTPFound(location=location, headers=headers)
def run(self):
if self.experiment is None: return
experiment = self.experiment
db = self.db
while True:
job = None
try:
job = db.session.query(db.ExperimentResult) \
.filter_by(experiment=self.experiment) \
.filter_by(status=-1) \
.order_by(func.rand()).limit(1).first()
job.status = 0
db.session.commit()
except:
db.session.rollback()
if job:
job.startTime = func.now()
db.session.commit()
client_line = '/usr/bin/time -f ";time=%U;mem=%M;" '
client_line += os.path.join(PACKAGE_DIR, 'solvers', launch_command(job.solver_configuration, os.path.join(PACKAGE_DIR, 'instances', str(job.instance.idInstance)+ '_' + job.instance.name), str(job.seed))[2:])
print "running job", job.idJob, client_line
stdout = open(os.path.join(TMP, str(job.idJob) + 'stdout~'), 'w')
stderr = open(os.path.join(TMP, str(job.idJob) + 'stderr~'), 'w')
start = time.time()
p = subprocess.Popen(shlex.split(str(client_line)), preexec_fn=setlimits(self.experiment.CPUTimeLimit), stdout = stdout, stderr = stderr)
returncode = p.wait()
print "Job", job.idJob, "done .. Realtime:", str(time.time() - start), "s"
stdout.close()
stderr.close()
stdout = open(os.path.join(TMP, str(job.idJob) + 'stdout~'), 'r')
stderr = open(os.path.join(TMP, str(job.idJob) + 'stderr~'), 'r')
time_output = stderr.read().split(';')
runtime = float([d.split('=')[1] for d in time_output if d.startswith('time=')][0])
memory = int([d.split('=')[1] for d in time_output if d.startswith('mem=')][0])
job.solverOutput = stdout.read()
stdout.close()
stderr.close()
os.remove(os.path.join(TMP, str(job.idJob) + 'stdout~'))
os.remove(os.path.join(TMP, str(job.idJob) + 'stderr~'))
job.resultTime = runtime
#cpuinfo = open('/proc/cpuinfo')
#job.solverOutput = cpuinfo.read()
#cpuinfo.close()
print " retcode", returncode
if returncode != 10 and returncode != 0: # CPU Time limit exceeded exit code guess
job.status = 21
else:
job.status = 1
if 's SATISFIABLE' in job.solverOutput:
job.resultCode = 11
print " CPU time:", runtime, "s", "Memory used:", memory, "kB"
job.computeQueue = self.experiment.grid_queue[0].idgridQueue
db.session.commit()
else:
if db.session.query(db.ExperimentResult) \
.filter_by(experiment=self.experiment) \
.filter_by(status=-1) \
.order_by(func.rand()).count() == 0: break # no more jobs
def render(self):
settings = self.request.registry.settings
username = authenticated_userid(self.request)
if not username:
raise exc.HTTPForbidden()
remote_user = User.by_login(self.session, username)
if not remote_user:
raise exc.HTTPForbidden()
params = self.request.params
if (asbool(settings['pyshop.upload.satanize'])
and not re.match(settings['pyshop.upload.satanize.regex'],
params['version']
)):
raise exc.HTTPForbidden()
pkg = Package.by_name(self.session, params['name'])
if pkg:
auth = [user for user in pkg.owners + pkg.maintainers
if user == remote_user]
if not auth:
raise exc.HTTPForbidden()
else:
pkg = Package(name=params['name'], local=True)
pkg.owners.append(remote_user)
content = self.request.POST['content']
input_file = content.file
# rewrite the filename, do not use the posted one for security
filename = u'%s-%s.%s' % (params['name'], params['version'],
{u'sdist': u'tar.gz',
u'bdist_egg': u'egg',
u'bdist_msi': u'msi',
u'bdist_dmg': u'zip', # XXX or gztar ?
u'bdist_rpm': u'rpm',
u'bdist_dumb': u'msi',
u'bdist_wininst': u'exe',
}[params['filetype']])
dir_ = os.path.join(settings['pyshop.repository'],
filename[0].lower())
if not os.path.exists(dir_):
os.mkdir(dir_, 0750)
filepath = os.path.join(dir_, filename)
while os.path.exists(filepath):
log.warn('File %s exists but new upload self.request, deleting'
% filepath)
os.unlink(filepath)
size = 0
with open(filepath, 'wb') as output_file:
input_file.seek(0)
while True:
data = input_file.read(2<<16)
if not data:
break
size += len(data)
output_file.write(data)
release = Release.by_version(self.session, pkg.name,
params['version'])
if not release:
release = Release(package=pkg,
version=params['version'],
summary=params.get('summary'),
author=remote_user,
home_page=params.get('home_page'),
license=params.get('license'),
description=params.get('description'),
keywords=params.get('keywords'),
platform=params.get('platform'),
download_url=params.get('download_url'),
docs_url=params.get('docs_url'),
)
classifiers = params.getall('classifiers')
for name in classifiers:
classifier = Classifier.by_name(self.session, name)
while classifier:
if classifier not in release.classifiers:
release.classifiers.append(classifier)
if classifier not in pkg.classifiers:
pkg.classifiers.append(classifier)
classifier = classifier.parent
rfile = ReleaseFile.by_filename(self.session, release, filename)
if not rfile:
rfile = ReleaseFile(release=release,
filename=filename,
size=size,
md5_digest=params.get('md5_digest'),
package_type=params['filetype'],
python_version=params.get('pyversion'),
comment_text=params.get('comment'),
)
self.session.add(rfile)
self.session.add(release)
pkg.update_at = func.now()
self.session.add(pkg)
return {'release_file': rfile}
def render(self):
api = pypi.proxy
settings = self.request.registry.settings
satanize = asbool(settings['pyshop.mirror.satanize'])
package_name = self.request.matchdict['package_name']
pkg = Package.by_name(self.session, package_name)
refresh = True
if pkg:
if pkg.local:
refresh = False
else:
if pkg.update_at:
td = datetime.now() - pkg.update_at
refresh = td.days > 0 or td.seconds > 10800
if refresh:
pypi_versions = api.package_releases(package_name, True)
# XXX package_releases is case sensitive
# but dependancies declaration not...
if not pypi_versions:
package_name = package_name.lower()
search_result = api.search({'name': package_name}, True)
search_result = [p for p in search_result
if p['name'].lower() == package_name]
if search_result:
package_name = search_result[0]['name']
pypi_versions = api.package_releases(package_name, True)
else:
pypi_versions = []
if not pkg:
if not pypi_versions:
log.info('package %s has no versions' % package_name)
return {'package': None,
'package_name': package_name}
if satanize:
re_satanize = re.compile(settings['pyshop.mirror.'
'satanize.regex'])
pypi_versions = [v for v in pypi_versions
if re_satanize.match(v)]
# mirror the package now
log.info('mirror package %s now' % package_name)
pkg = Package(name=package_name, local=False)
roles = api.package_roles(package_name)
for role, login in roles:
user = User.by_login(self.session, login, local=False)
if not user:
user = User(login=login, local=False)
self.session.add(user)
if role == 'Owner':
pkg.owners.append(user)
elif role == 'Maintainer':
pkg.maintainers.append(user)
self.session.flush()
refresh = True
if not pkg.local and refresh:
pkg_versions = pkg.versions
for version in pypi_versions:
if version not in pkg_versions:
release_data = api.release_data(package_name, version)
release = self._create_release(pkg, release_data)
release_files = api.release_urls(package_name, version)
for data in release_files:
rf = ReleaseFile.by_filename(self.session, release,
data['filename'])
if not rf:
rf = self._create_release_file(release, data)
pkg.update_at = func.now()
self.session.add(pkg)
log.info('package %s mirrored' % package_name)
return {'package': pkg}
# -*- coding: utf-8 -*-
from sqlalchemy.sql.expression import func
from sqlalchemy.orm import aliased, relationship
from tabapp.models import db
RoleLineage = db.Table('role_lineage',
db.Column('id', db.Integer, db.Sequence('core_seq_general'), primary_key=True),
db.Column('created', db.DateTime, nullable=False, default=func.now()),
db.Column('version', db.DateTime, nullable=False, default=func.now(), onupdate=func.now()),
db.Column('enabled', db.Boolean, nullable=False, default=True),
db.Column('parent_id', db.Integer, db.ForeignKey('role.id', ondelete='CASCADE'), nullable=False),
db.Column('child_id', db.Integer, db.ForeignKey('role.id', ondelete='CASCADE'), nullable=False)
)
class Role(db.Model):
id = db.Column(db.Integer, db.Sequence('core_seq_general'), primary_key=True)
created = db.Column(db.DateTime, nullable=False, default=func.now())
version = db.Column(db.DateTime, nullable=False, default=func.now(), onupdate=func.now())
enabled = db.Column(db.Boolean, nullable=False, default=True)
name = db.Column(db.String, nullable=False)
key = db.Column(db.String, nullable=False)
roles = relationship('Role', secondary='role_lineage', primaryjoin='Role.id==role_lineage.c.parent_id', secondaryjoin='Role.id==role_lineage.c.child_id')
def _get_descendants(self):
descendants = db.session.query(
RoleLineage.c.child_id
).filter(
RoleLineage.c.parent_id == self.id
).cte(name="descendants", recursive=True)
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
:param url: The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_songbooks
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
* author_type
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* title
* alternate_title
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* theme_name
* search_title
* search_lyrics
**songs_songsbooks Table**
This is a mapping table between the *songs* and the *song_books* tables. It has the following columns:
* songbook_id
* song_id
* entry # The song number, like 120 or 550A
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(
'authors', metadata,
Column('id', types.Integer(), primary_key=True),
Column('first_name', types.Unicode(128)),
Column('last_name', types.Unicode(128)),
Column('display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(
'media_files', metadata,
Column('id', types.Integer(), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), default=None),
Column('file_name', types.Unicode(255), nullable=False),
Column('type', types.Unicode(64), nullable=False, default='audio'),
Column('weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(
'song_books', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), nullable=False),
Column('publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(
'songs', metadata,
Column('id', types.Integer(), primary_key=True),
Column('title', types.Unicode(255), nullable=False),
Column('alternate_title', types.Unicode(255)),
Column('lyrics', types.UnicodeText, nullable=False),
Column('verse_order', types.Unicode(128)),
Column('copyright', types.Unicode(255)),
Column('comments', types.UnicodeText),
Column('ccli_number', types.Unicode(64)),
Column('theme_name', types.Unicode(128)),
Column('search_title', types.Unicode(255), index=True, nullable=False),
Column('search_lyrics', types.UnicodeText, nullable=False),
Column('create_date', types.DateTime(), default=func.now()),
Column('last_modified', types.DateTime(), default=func.now(), onupdate=func.now()),
Column('temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(
'topics', metadata,
Column('id', types.Integer(), primary_key=True),
Column('name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(
'authors_songs', metadata,
Column('author_id', types.Integer(), ForeignKey('authors.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('author_type', types.Unicode(255), primary_key=True, nullable=False, server_default=text('""'))
)
# Definition of the "songs_songbooks" table
songs_songbooks_table = Table(
'songs_songbooks', metadata,
Column('songbook_id', types.Integer(), ForeignKey('song_books.id'), primary_key=True),
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('entry', types.Unicode(255), primary_key=True, nullable=False)
)
# Definition of the "songs_topics" table
songs_topics_table = Table(
'songs_topics', metadata,
Column('song_id', types.Integer(), ForeignKey('songs.id'), primary_key=True),
Column('topic_id', types.Integer(), ForeignKey('topics.id'), primary_key=True)
)
mapper(Author, authors_table, properties={
'songs': relation(Song, secondary=authors_songs_table, viewonly=True)
})
mapper(AuthorSong, authors_songs_table, properties={
'author': relation(Author)
})
mapper(SongBookEntry, songs_songbooks_table, properties={
'songbook': relation(Book)
})
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table, properties={
# Use the authors_songs relation when you need access to the 'author_type' attribute
# or when creating new relations
'authors_songs': relation(AuthorSong, cascade="all, delete-orphan"),
# Use lazy='joined' to always load authors when the song is fetched from the database (bug 1366198)
'authors': relation(Author, secondary=authors_songs_table, viewonly=True, lazy='joined'),
'media_files': relation(MediaFile, backref='songs', order_by=media_files_table.c.weight),
'songbook_entries': relation(SongBookEntry, backref='song', cascade='all, delete-orphan'),
'topics': relation(Topic, backref='songs', secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
def render(self):
api = pypi.proxy
settings = self.request.registry.settings
sanitize = asbool(settings['pyshop.mirror.sanitize'])
package_name = self.request.matchdict['package_name']
pkg = Package.by_name(self.session, package_name)
if pkg is None:
pkg = Package.by_name(self.session, package_name.replace('-', '_'))
refresh = True
session_users = {}
if pkg:
if pkg.local:
refresh = False
else:
if pkg.update_at:
log.debug('validating cache interval')
current_td = datetime.now() - pkg.update_at
max_td = timedelta(
hours=int(settings.get('pyshop.mirror.cache.ttl',
'24')))
refresh = current_td > max_td
log.debug('"%s" > "%s": %s',
current_td, max_td, refresh)
if refresh:
log.info('refresh package %s', package_name)
pypi_versions = api.package_releases(package_name, True)
# XXX package_releases is case sensitive
# but dependencies declaration not...
if not pypi_versions:
pkg_info = self._search_package(package_name)
if not pkg_info and '-' in package_name:
tmp_name = package_name.replace('-', '_')
pkg_info = self._search_package(tmp_name)
if not pkg_info and '_' in package_name:
tmp_name = package_name.replace('_', '-')
pkg_info = self._search_package(tmp_name)
if pkg_info:
package_name, pypi_versions = pkg_info
pypi_versions = [ver.decode('utf-8') for ver in pypi_versions]
else:
pypi_versions = []
if not pkg:
if not pypi_versions:
log.info('package %s has no versions', package_name)
return {'package': None,
'package_name': package_name}
if sanitize:
re_sanitize = re.compile(settings['pyshop.mirror.'
'sanitize.regex'])
pypi_versions = [v for v in pypi_versions
if re_sanitize.match(v)]
# mirror the package now
log.info('mirror package %s now', package_name)
pkg = Package.by_name(self.session, package_name)
if not pkg:
pkg = Package(name=package_name, local=False)
self.session.add(pkg)
self.session.flush()
roles = api.package_roles(package_name)
for role, login in roles:
login = login.decode('utf-8') # XMLRPC should return utf-8
log.info('Looking for non local user %s', login)
if _sanitize(login) in session_users:
user = session_users[_sanitize(login)]
else:
user = User.by_login(self.session, login, local=False)
if not user:
log.info('Not found. creating user {0}'.format(login))
user = User(login=login, local=False)
self.session.add(user)
if role == 'Owner':
pkg.owners.append(user)
self.session.add(pkg)
elif role == 'Maintainer':
pkg.maintainers.append(user)
self.session.add(pkg)
session_users[_sanitize(login)] = user
self.session.flush()
self.session.flush()
if not pkg.local and refresh:
log.debug('refreshing %s package', package_name)
pkg_versions = set(pypi_versions).difference(pkg.versions)
if not pkg_versions:
log.info('No new version to mirror')
log.debug('pypi versions: %s', pypi_versions)
log.debug('mirrored versions: %s', pkg.versions)
for version in pkg_versions:
log.info('Mirroring version %s', version)
release_data = api.release_data(package_name, version)
release = self._create_release(pkg, release_data,
session_users)
release_files = api.release_urls(package_name, version)
for data in release_files:
filename = data['filename'].decode('utf-8')
rf = ReleaseFile.by_filename(self.session, release,
filename)
if not rf:
rf = self._create_release_file(release, data)
pkg.update_at = func.now()
self.session.add(pkg)
log.info('package %s mirrored' % package_name)
return {'package': pkg,
'whlify': asbool(settings.get('pyshop.mirror.wheelify', '0'))}
def init_schema(url):
"""
Setup the songs database connection and initialise the database schema.
``url``
The database to setup
The song database contains the following tables:
* authors
* authors_songs
* media_files
* media_files_songs
* song_books
* songs
* songs_topics
* topics
**authors** Table
This table holds the names of all the authors. It has the following
columns:
* id
* first_name
* last_name
* display_name
**authors_songs Table**
This is a bridging table between the *authors* and *songs* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* author_id
* song_id
**media_files Table**
* id
* file_name
* type
**song_books Table**
The *song_books* table holds a list of books that a congregation gets
their songs from, or old hymnals now no longer used. This table has the
following columns:
* id
* name
* publisher
**songs Table**
This table contains the songs, and each song has a list of attributes.
The *songs* table has the following columns:
* id
* song_book_id
* title
* alternate_title
* lyrics
* verse_order
* copyright
* comments
* ccli_number
* song_number
* theme_name
* search_title
* search_lyrics
**songs_topics Table**
This is a bridging table between the *songs* and *topics* tables, which
serves to create a many-to-many relationship between the two tables. It
has the following columns:
* song_id
* topic_id
**topics Table**
The topics table holds a selection of topics that songs can cover. This
is useful when a worship leader wants to select songs with a certain
theme. This table has the following columns:
* id
* name
"""
session, metadata = init_db(url)
# Definition of the "authors" table
authors_table = Table(u'authors', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'first_name', types.Unicode(128)),
Column(u'last_name', types.Unicode(128)),
Column(u'display_name', types.Unicode(255), index=True, nullable=False)
)
# Definition of the "media_files" table
media_files_table = Table(u'media_files', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'song_id', types.Integer(), ForeignKey(u'songs.id'),
default=None),
Column(u'file_name', types.Unicode(255), nullable=False),
Column(u'type', types.Unicode(64), nullable=False, default=u'audio'),
Column(u'weight', types.Integer(), default=0)
)
# Definition of the "song_books" table
song_books_table = Table(u'song_books', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'name', types.Unicode(128), nullable=False),
Column(u'publisher', types.Unicode(128))
)
# Definition of the "songs" table
songs_table = Table(u'songs', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'song_book_id', types.Integer(),
ForeignKey(u'song_books.id'), default=None),
Column(u'title', types.Unicode(255), nullable=False),
Column(u'alternate_title', types.Unicode(255)),
Column(u'lyrics', types.UnicodeText, nullable=False),
Column(u'verse_order', types.Unicode(128)),
Column(u'copyright', types.Unicode(255)),
Column(u'comments', types.UnicodeText),
Column(u'ccli_number', types.Unicode(64)),
Column(u'song_number', types.Unicode(64)),
Column(u'theme_name', types.Unicode(128)),
Column(u'search_title', types.Unicode(255), index=True, nullable=False),
Column(u'search_lyrics', types.UnicodeText, nullable=False),
Column(u'create_date', types.DateTime(), default=func.now()),
Column(u'last_modified', types.DateTime(), default=func.now(),
onupdate=func.now()),
Column(u'temporary', types.Boolean(), default=False)
)
# Definition of the "topics" table
topics_table = Table(u'topics', metadata,
Column(u'id', types.Integer(), primary_key=True),
Column(u'name', types.Unicode(128), index=True, nullable=False)
)
# Definition of the "authors_songs" table
authors_songs_table = Table(u'authors_songs', metadata,
Column(u'author_id', types.Integer(),
ForeignKey(u'authors.id'), primary_key=True),
Column(u'song_id', types.Integer(),
ForeignKey(u'songs.id'), primary_key=True)
)
# Definition of the "songs_topics" table
songs_topics_table = Table(u'songs_topics', metadata,
Column(u'song_id', types.Integer(),
ForeignKey(u'songs.id'), primary_key=True),
Column(u'topic_id', types.Integer(),
ForeignKey(u'topics.id'), primary_key=True)
)
mapper(Author, authors_table)
mapper(Book, song_books_table)
mapper(MediaFile, media_files_table)
mapper(Song, songs_table,
properties={
'authors': relation(Author, backref='songs',
secondary=authors_songs_table, lazy=False),
'book': relation(Book, backref='songs'),
'media_files': relation(MediaFile, backref='songs',
order_by=media_files_table.c.weight),
'topics': relation(Topic, backref='songs',
secondary=songs_topics_table)
})
mapper(Topic, topics_table)
metadata.create_all(checkfirst=True)
return session
def insert_db(self,item,conn=None):
service_id=str(uuid.uuid4())
if get_service_by_name(item['name'], conn):
print '%s\t409'%item['name'].upper()
else:
ins=Service.insert().values(service_id=service_id,service_name=item['name'],service_title=item['title'],service_image_url=item['img_url'],
service_description=item['description'],service_type='saas',category_name='SAAS_SERVICE',created_at=func.now())
conn.execute(ins)
for reason in item['reason'].items():
s=ServiceReason.insert().values(service_id=service_id,service_reason_name=reason[0],service_reason_count=reason[1],created_at=func.now())
conn.execute(s)
print '%s\t201'%item['name'].upper()
def render(self):
api = pypi.proxy
settings = self.request.registry.settings
sanitize = asbool(settings['pyshop.mirror.sanitize'])
package_name = self.request.matchdict['package_name']
pkg = Package.by_name(self.session, package_name)
refresh = True
if pkg:
if pkg.local:
refresh = False
else:
if pkg.update_at:
current_td = datetime.now() - pkg.update_at
max_td = timedelta(hours=int(settings.get('pyshop.mirror.cache.ttl',
'24')))
refresh = current_td > max_td
log.debug('"{cdt}" > "{max}": {refr}'.format(cdt=current_td,
max=max_td,
refr=refresh))
if refresh:
log.info('refresh package {pkg}'.format(pkg=package_name))
pypi_versions = api.package_releases(package_name, True)
# XXX package_releases is case sensitive
# but dependancies declaration not...
if not pypi_versions:
pkg_info = self._search_package(package_name)
if not pkg_info and '-' in package_name:
tmp_name = package_name.replace('-', '_')
pkg_info = self._search_package(tmp_name)
if not pkg_info and '_' in package_name:
tmp_name = package_name.replace('_', '-')
pkg_info = self._search_package(tmp_name)
if pkg_info:
package_name, pypi_versions = pkg_info
else:
pypi_versions = []
if not pkg:
if not pypi_versions:
log.info('package %s has no versions' % package_name)
return {'package': None,
'package_name': package_name}
if sanitize:
re_sanitize = re.compile(settings['pyshop.mirror.'
'sanitize.regex'])
pypi_versions = [v for v in pypi_versions
if re_sanitize.match(v)]
# mirror the package now
log.info('mirror package %s now' % package_name)
pkg = Package.by_name(self.session, package_name)
if not pkg:
pkg = Package(name=package_name, local=False)
self.session.add(pkg)
self.session.flush()
roles = api.package_roles(package_name)
for role, login in roles:
login = login.decode('utf-8') # XMLRPC should return utf-8
user = User.by_login(self.session, login, local=False)
if not user:
user = User(login=login, local=False)
self.session.add(user)
if role == 'Owner':
pkg.owners.append(user)
elif role == 'Maintainer':
pkg.maintainers.append(user)
self.session.flush()
refresh = True
if not pkg.local and refresh:
pkg_versions = pkg.versions
for version in pypi_versions:
if version not in pkg_versions:
release_data = api.release_data(package_name, version)
release = self._create_release(pkg, release_data)
release_files = api.release_urls(package_name, version)
for data in release_files:
filename = data['filename'].decode('utf-8')
rf = ReleaseFile.by_filename(self.session, release,
filename)
if not rf:
rf = self._create_release_file(release, data)
pkg.update_at = func.now()
self.session.add(pkg)
log.info('package %s mirrored' % package_name)
return {'package': pkg,
'whlify': asbool(settings.get('pyshop.mirror.wheelify', '0'))}
def render(self):
api = pypi.proxy
settings = self.request.registry.settings
sanitize = asbool(settings['pyshop.mirror.sanitize'])
package_name = self.request.matchdict['package_name']
pkg = Package.by_name(self.session, package_name)
refresh = True
if pkg:
if pkg.local:
refresh = False
else:
if pkg.update_at:
td = datetime.now() - pkg.update_at
# refresh if the package has not been update today
# XXX should be configurable
refresh = td.days > 0
if refresh:
pypi_versions = api.package_releases(package_name, True)
# XXX package_releases is case sensitive
# but dependancies declaration not...
if not pypi_versions:
search_result = api.search({'name': package_name}, True)
search_count = len(search_result)
search_result = [p for p in search_result
if p['name'].lower() == package_name
or p['name'].lower().replace('-', '_') == package_name]
log.debug('Found {sc}, matched {mc}'.format(sc=search_count, mc=len(search_result)))
if search_result:
package_name = search_result[0]['name']
pypi_versions = api.package_releases(package_name, True)
else:
pypi_versions = []
if not pkg:
if not pypi_versions:
log.info('package %s has no versions' % package_name)
return {'package': None,
'package_name': package_name}
if sanitize:
re_sanitize = re.compile(settings['pyshop.mirror.'
'sanitize.regex'])
pypi_versions = [v for v in pypi_versions
if re_sanitize.match(v)]
# mirror the package now
log.info('mirror package %s now' % package_name)
pkg = Package(name=package_name, local=False)
roles = api.package_roles(package_name)
for role, login in roles:
login = login.decode('utf-8') # XMLRPC should return utf-8
user = User.by_login(self.session, login, local=False)
if not user:
user = User(login=login, local=False)
self.session.add(user)
if role == 'Owner':
pkg.owners.append(user)
elif role == 'Maintainer':
pkg.maintainers.append(user)
self.session.flush()
refresh = True
if not pkg.local and refresh:
pkg_versions = pkg.versions
for version in pypi_versions:
if version not in pkg_versions:
release_data = api.release_data(package_name, version)
release = self._create_release(pkg, release_data)
release_files = api.release_urls(package_name, version)
for data in release_files:
filename = data['filename'].decode('utf-8')
rf = ReleaseFile.by_filename(self.session, release,
filename)
if not rf:
rf = self._create_release_file(release, data)
pkg.update_at = func.now()
self.session.add(pkg)
log.info('package %s mirrored' % package_name)
return {'package': pkg}
def main():
sql = text("SELECT CURRENT_TIMESTAMP as now_1")
print run_sql(sql).fetchall()
sql = select([func.now()])
print run_sql(sql).fetchall()
def render(self):
settings = self.request.registry.settings
if not self.user:
raise exc.HTTPForbidden()
params = self.request.params
if (asbool(settings['pyshop.upload.sanitize'])
and not re.match(settings['pyshop.upload.sanitize.regex'],
params['version']
)):
raise exc.HTTPForbidden()
pkg = Package.by_name(self.session, params['name'])
if pkg and pkg.local:
auth = [user for user in pkg.owners + pkg.maintainers
if user == self.user]
if not auth:
raise exc.HTTPForbidden()
elif not pkg:
pkg = Package(name=params['name'], local=True)
pkg.owners.append(self.user)
content = self.request.POST['content']
input_file = content.file
if asbool(settings.get('pyshop.upload.rewrite_filename', '1')):
# rewrite the filename, do not use the posted one for security
filename = self._guess_filename(params, content.filename)
else:
filename = content.filename
dir_ = os.path.join(settings['pyshop.repository'],
filename[0].lower())
if not os.path.exists(dir_):
os.makedirs(dir_, 0o750)
filepath = os.path.join(dir_, filename)
while os.path.exists(filepath):
log.warning('File %s exists but new upload self.request, deleting'
% filepath)
os.unlink(filepath)
size = 0
with open(filepath, 'wb') as output_file:
input_file.seek(0)
while True:
data = input_file.read(2 << 16)
if not data:
break
size += len(data)
output_file.write(data)
release = Release.by_version(self.session, pkg.name,
params['version'])
if not release:
release = Release(package=pkg,
version=params['version'],
summary=params.get('summary'),
author=self.user,
home_page=params.get('home_page'),
license=params.get('license'),
description=params.get('description'),
keywords=params.get('keywords'),
platform=params.get('platform'),
download_url=params.get('download_url'),
docs_url=params.get('docs_url'),
)
classifiers = params.getall('classifiers')
for name in classifiers:
classifier = Classifier.by_name(self.session, name)
while classifier:
if classifier not in release.classifiers:
release.classifiers.append(classifier)
if classifier not in pkg.classifiers:
pkg.classifiers.append(classifier)
classifier = classifier.parent
rfile = ReleaseFile.by_filename(self.session, release, filename)
if not rfile:
rfile = ReleaseFile(release=release,
filename=filename,
size=size,
md5_digest=params.get('md5_digest'),
package_type=params['filetype'],
python_version=params.get('pyversion'),
comment_text=params.get('comment'),
)
self.session.add(rfile)
self.session.add(release)
pkg.update_at = func.now()
self.session.add(pkg)
return {'release_file': rfile}
