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')
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 _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}