On Thu, Jun 26, 2014 at 1:45 PM, Mike Bayer <mike...@zzzcomputing.com>
wrote:
> minimal mapping + the query against that mapping.
>
> Okay, for the other method I needed to change, I think I've put together
complete enough examples and have attached the files. The 'before'
file is what we have currently (and doesn't quite do the right thing as
it's only using the environment_id), and the 'after' file is what seemingly
worked after my coworker and I tried a different route to avoid having
to use a subquery. One thing to note is there's a reciprocating rela-
tionship added on the Deployment class to the Package class which
initially caused a lovely stack overflow in Python until the joinedload
options were added; this may be a bad idea, but it was the only way
we knew how to get things working the way we needed. :)
I tried to add all the imports as well in case there were any questions
that might come from the code itself; hopefully I didn't miss any, but
if there's still confusion, let me know.
- Ken
>
>
> On 6/26/14, 4:11 PM, Ken Lareau wrote:
>
> On Thu, Jun 26, 2014 at 5:57 AM, Mike Bayer <mike...@zzzcomputing.com>
> wrote:
>
>>
>> On 6/25/14, 8:06 PM, Ken Lareau wrote:
>>
>> On Wed, Jun 25, 2014 at 6:28 AM, Mike Bayer <mike...@zzzcomputing.com>
>> wrote:
>>
>>>
>>> On 6/25/14, 2:26 AM, Ken Lareau wrote:
>>>
>>> On Tue, Jun 24, 2014 at 3:35 PM, Mike Bayer <mike...@zzzcomputing.com>
>>> wrote:
>>>
>>>>
>>>> On 6/23/14, 8:09 PM, Ken Lareau wrote:
>>>> >
>>>> > if apptier:
>>>> > subq = (
>>>> > Session.query(
>>>> > Package.pkg_name,
>>>> > Package.version,
>>>> > Package.revision,
>>>> > AppDefinition.app_type,
>>>> > AppDeployment.environment
>>>> > ).join(Deployment)
>>>> > .join(AppDeployment)
>>>> > .join(AppDefinition)
>>>> > .filter(Package.pkg_name == package_name)
>>>> > .filter(AppDeployment.environment == env)
>>>> > .filter(AppDeployment.status != 'invalidated'))
>>>> >
>>>> > [...]
>>>> >
>>>> > # The actual column name must be used in the subquery
>>>> > # usage below; DB itself should be corrected
>>>> > versions = (Session.query(subq.c.appType,
>>>> > subq.c.version,
>>>> > subq.c.revision)
>>>> > .group_by(subq.c.appType, subq.c.environment)
>>>> > .all())
>>>> >
>>>> > The parts in the subquery work fine, but the access of the 'column'
>>>> > in the final query leads to this:
>>>> >
>>>> > Traceback (most recent call last):
>>>> > File "<stdin>", line 1, in <module>
>>>> > File "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py",
>>>> > line 234, in find_deployed_version
>>>> > .group_by(subq.c.appType, subq.c.environment)
>>>> > File
>>>> >
>>>> "/home/klareau/.virtualenvs/tds/lib/python2.6/site-packages/sqlalchemy/util/_collections.py",
>>>> > line 174, in __getattr__
>>>> > raise AttributeError(key)
>>>> > AttributeError: environment
>>>> >
>>>> > This is not completely surprising, but I'm uncertain as the best way
>>>> > to fix this... help? :)
>>>> nothing is obviously wrong, assuming you are calling "subquery()" on
>>>> subq at the end. It will be a select() construct which will have a
>>>> column called ".environment" on the .c. collection because
>>>> AppDeployment.environment is present. Can't say why this attribute is
>>>> not here without full details.
>>>>
>>>>
>>> Oops there were some additional filters I left out, but this is added
>>> before the final query:
>>>
>>> subq = (subq.order_by(AppDeployment.realized.desc())
>>> .subquery(name='t_ordered'))
>>>
>>> Not sure why it isn't working, but today my coworker an I massively
>>> rewrote one of the other methods to avoid this same issue, so maybe
>>> we should try the same for this one... though it would be nice to know
>>> what I goofed here, but not sure what additional information I can
>>> give that would help offhand...
>>>
>>>
>>> what are the names that are actually present on subq.c ? "print
>>> list(subq.c)" should give an indication.
>>>
>>>
>> Adding that in to the method gave this:
>>
>> >>> deploy.find_deployed_version('tdstest', 'development', version='9',
>> apptier=True)
>> [Column('pkg_name', String(length=255), table=<t_ordered>,
>> nullable=False), Column('version', String(length=63), table=<t_ordered>,
>> nullable=False), Column('revision', String(length=63), table=<t_ordered>,
>> nullable=False), Column('appType', String(length=100), table=<t_ordered>,
>> nullable=False), <sqlalchemy.sql.elements.ColumnClause at 0x1d20e10;
>> %(30521360 anon)s>]
>> Traceback (most recent call last):
>> File "<stdin>", line 1, in <module>
>> File "/home/klareau/repos/git/tagopsdb/tagopsdb/deploy/deploy.py", line
>> 237, in find_deployed_version
>> .group_by(subq.c.appType, subq.c.environment)
>> File
>> "/home/klareau/.virtualenvs/tds/lib/python2.6/site-packages/sqlalchemy/util/_collections.py",
>> line 174, in __getattr__
>> raise AttributeError(key)
>> AttributeError: environment
>>
>> you need to put .label('environment') on that column before it finds
>> its way into subq. I dont have the mappings here to review.
>>
>> for the next go around please just provide a one-file example of the
>> mapping plus the query, thanks.
>>
>> Do you desire the example to be fully functional, or just have the
> relevant
> mapping and query? We ran into the same issue with another query and
> ended up completely reworking the code to avoid the subquery, but it in-
> volves quite a bit of various mappings and I'm uncertain if it's the proper
> way to move forward. I could try to post the before/after here, but if it
> needs to be able to to run standalone it may take me a bit of time to get
> it into a fully workable state (given how extensive it is).
>
>
> --
> - Ken Lareau
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
- Ken Lareau
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from datetime import datetime, timedelta
from sqlalchemy import ForeignKey, func
from sqlalchemy.dialects.mysql import INTEGER, SMALLINT, TIMESTAMP
from sqlalchemy.ext.hybrid import hybrid_method, hybrid_property
from sqlalchemy.orm import joinedload, relationship
from sqlalchemy.sql.expression import func, select
from tagopsdb.model import AppDefinition, AppDeployment, \
Deployment, Environment, Package
from .meta import Base, Column, String # Minor customizations for 'Column'
# and 'String', consider them like
# the standard defines in SQLA
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
app_type = Column(u'appType', String(length=100), nullable=False)
status = Column(
Enum('active', 'inactive'),
nullable=False,
server_default='active'
)
app_deployments = relationship('AppDeployment')
class AppDeployment(Base):
__tablename__ = 'app_deployments'
id = Column(u'AppDeploymentID', INTEGER(), primary_key=True)
deployment_id = Column(
u'DeploymentID',
INTEGER(),
ForeignKey('deployments.DeploymentID', ondelete='cascade'),
nullable=False
)
app_id = Column(
u'AppID',
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
nullable=False
)
user = Column(String(length=32), nullable=False)
status = Column(
Enum(
'complete',
'incomplete',
'inprogress',
'invalidated',
'validated',
),
nullable=False
)
environment_id = Column(
u'environment_id',
INTEGER(),
ForeignKey('environments.environmentID', ondelete='cascade'),
nullable=False
)
realized = Column(
TIMESTAMP(),
nullable=False,
server_default=func.current_timestamp()
)
application = relationship('AppDefinition')
deployment = relationship('Deployment')
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).as_scalar()
@hybrid_property
def needs_validation(self):
return (self.status == 'complete') | (self.status == 'incomplete')
@hybrid_method
def realized_before(self, dt):
return self.realized < dt
@realized_before.expression
def realized_before(cls, dt):
return cls.realized < dt
class Deployment(Base):
__tablename__ = 'deployments'
id = Column(u'DeploymentID', INTEGER(), primary_key=True)
package_id = Column(
INTEGER(),
ForeignKey('packages.package_id', ondelete='cascade'),
nullable=False
)
app_deployments = relationship('AppDeployment')
package = relationship('Package')
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
class Package(Base):
__tablename__ = 'packages'
id = Column(u'package_id', INTEGER(), primary_key=True)
pkg_name = Column(String(length=255), nullable=False)
version = Column(String(length=63), nullable=False)
revision = Column(String(length=63), nullable=False)
deployments = relationship('Deployment')
def find_unvalidated_versions(time_delta, environment):
"""Find the latest deployments that are not validated in a given
environment for a given amount of time
"""
validation_threshold = datetime.now() - timedelta(seconds=time_delta)
AD = AppDeployment # Alias to make typing a bit shorter
app_deployments = AD.query(
).options(
joinedload(AD.deployment),
joinedload(AD.deployment).joinedload(Deployment.package),
joinedload(AD.application)
).filter(
AD.realized_before(validation_threshold),
AD.environment == environment,
AD.needs_validation
).order_by(
AD.realized.desc()
).group_by(
AD.environment_id, AD.app_id
).all()
found = set()
for app_dep in app_deployments:
package = app_dep.deployment.package
app_def = app_dep.application
key = app_dep.environment, app_def.app_type, package.pkg_name
if key in found:
continue
found.add(key)
yield (
package.pkg_name,
package.version,
package.revision,
app_def.app_type,
app_dep.environment,
app_dep.realized,
app_dep.user,
app_dep.status
)
from sqlalchemy import ForeignKey, func
from sqlalchemy.dialects.mysql import INTEGER, SMALLINT, TIMESTAMP
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.orm import relationship
from sqlalchemy.sql import and_
from sqlalchemy.sql.expression import func, select
from tagopsdb.model import Session
from tagopsdb.model import AppDefinition, AppDeployment, \
Deployment, Environment, Package
from .meta import Base, Column, String # Minor customizations for 'Column'
# and 'String', consider them like
# the standard defines in SQLA
class AppDefinition(Base):
__tablename__ = 'app_definitions'
id = Column(u'AppID', SMALLINT(display_width=2), primary_key=True)
app_type = Column(u'appType', String(length=100), nullable=False)
status = Column(
Enum('active', 'inactive'),
nullable=False,
server_default='active'
)
app_deployments = relationship('AppDeployment')
class AppDeployment(Base):
__tablename__ = 'app_deployments'
id = Column(u'AppDeploymentID', INTEGER(), primary_key=True)
deployment_id = Column(
u'DeploymentID',
INTEGER(),
ForeignKey('deployments.DeploymentID', ondelete='cascade'),
nullable=False
)
app_id = Column(
u'AppID',
SMALLINT(display_width=6),
ForeignKey('app_definitions.AppID', ondelete='cascade'),
nullable=False
)
user = Column(String(length=32), nullable=False)
status = Column(
Enum(
'complete',
'incomplete',
'inprogress',
'invalidated',
'validated',
),
nullable=False
)
environment_id = Column(
u'environment_id',
INTEGER(),
ForeignKey('environments.environmentID', ondelete='cascade'),
nullable=False
)
realized = Column(
TIMESTAMP(),
nullable=False,
server_default=func.current_timestamp()
)
application = relationship('AppDefinition')
deployment = relationship('Deployment')
environment_obj = relationship('Environment')
@hybrid_property
def environment(self):
return self.environment_obj.environment
@environment.expression
def environment(cls):
return select(
[Environment.environment]
).where(
Environment.id == cls.environment_id
).correlate(cls).as_scalar()
class Deployment(Base):
__tablename__ = 'deployments'
id = Column(u'DeploymentID', INTEGER(), primary_key=True)
package_id = Column(
INTEGER(),
ForeignKey('packages.package_id', ondelete='cascade'),
nullable=False
)
app_deployments = relationship('AppDeployment')
class Environment(Base):
__tablename__ = 'environments'
id = Column(u'environmentID', INTEGER(), primary_key=True)
environment = Column(String(length=15), nullable=False, unique=True)
class Package(Base):
__tablename__ = 'packages'
id = Column(u'package_id', INTEGER(), primary_key=True)
pkg_name = Column(String(length=255), nullable=False)
version = Column(String(length=63), nullable=False)
revision = Column(String(length=63), nullable=False)
deployments = relationship('Deployment')
def find_unvalidated_versions(time_delta, environment):
"""Find the latest deployments that are not validated in a given
environment for a given amount of time
"""
subq = (Session.query(Package.pkg_name, Package.version,
Package.revision, AppDefinition.app_type,
AppDeployment.environment_id,
AppDeployment.environment,
AppDeployment.realized, AppDeployment.user,
AppDeployment.status)
.join(Deployment)
.join(AppDeployment)
.join(AppDefinition)
.filter(AppDeployment.status!='invalidated')
.filter(AppDeployment.environment==environment)
.order_by(AppDeployment.realized.desc())
.subquery(name='t_ordered'))
return (Session.query(subq.c.pkg_name, subq.c.version, subq.c.revision,
subq.c.appType, subq.c.environment_id,
subq.c.realized, subq.c.user, subq.c.status)
.group_by(subq.c.appType, subq.c.environment_id,
subq.c.pkg_name)
.having(and_(subq.c.status.like('%complete'),
func.unix_timestamp(subq.c.realized) <
func.unix_timestamp(func.now()) - time_delta))
.all())
