| import sqlalchemy as sa |
| from slavealloc.data import model |
| |
| |
| def denormalized_slaves(environments=None, purposes=None, pools=None, enabled=None): |
| locked_masters = model.masters.alias('locked_masters') |
| current_masters = model.masters.alias('current_masters') |
| q = sa.select([ |
| model.slaves, |
| model.distros.c.name.label('distro'), |
| model.bitlengths.c.name.label('bitlength'), |
| model.speeds.c.name.label('speed'), |
| model.purposes.c.name.label('purpose'), |
| model.datacenters.c.name.label('datacenter'), |
| model.trustlevels.c.name.label('trustlevel'), |
| model.environments.c.name.label('environment'), |
| model.pools.c.name.label('pool'), |
| locked_masters.c.nickname.label('locked_master'), |
| current_masters.c.nickname.label('current_master'), |
| ], whereclause=( |
| (model.distros.c.distroid == model.slaves.c.distroid) & |
| (model.bitlengths.c.bitsid == model.slaves.c.bitsid) & |
| (model.speeds.c.speedid == model.slaves.c.speedid) & |
| (model.purposes.c.purposeid == model.slaves.c.purposeid) & |
| (model.datacenters.c.dcid == model.slaves.c.dcid) & |
| (model.trustlevels.c.trustid == model.slaves.c.trustid) & |
| (model.environments.c.envid == model.slaves.c.envid) & |
| (model.pools.c.poolid == model.slaves.c.poolid) & |
| (model.slaves.c.envid != 5) |
| ), from_obj=[ |
| # note that the other tables will be joined automatically |
| model.slaves.outerjoin( |
| locked_masters, onclause= |
| locked_masters.c.masterid == model.slaves.c.locked_masterid |
| ).outerjoin( |
| current_masters, onclause= |
| current_masters.c.masterid == model.slaves.c.current_masterid |
| ) |
| ]) |
| if environments: |
| q = q.where(model.environments.c.name.in_(environments)) |
| if purposes: |
| q = q.where(model.purposes.c.name.in_(purposes)) |
| if pools: |
| q = q.where(model.pools.c.name.in_(pools)) |
| if enabled: |
| q = q.where(model.slaves.c.enabled == enabled) |
| return q |
| |
| denormalized_masters = sa.select([ |
| model.masters, |
| model.datacenters.c.name.label('datacenter'), |
| model.pools.c.name.label('pool'), |
| ], whereclause=( |
| (model.datacenters.c.dcid == model.masters.c.dcid) & |
| (model.pools.c.poolid == model.masters.c.poolid) |
| )) |
| |
| # this is one query, but it's easier to build it in pieces. Use bind parameter |
| # 'slaveid' to specify the new slave. There will be exactly one result row, |
| # unless there are no masters for this slave. That result row is from the |
| # masters table |
| |
| |
| def best_master(): |
| # we need two copies of slaves: one to find this slave's info, and one |
| # enumerating its peers |
| me = model.slaves.alias('me') |
| peers = model.slaves.alias('peers') |
| |
| # all of the peers of this slave, *not* including the slave itself |
| me_and_peers = me.join(peers, onclause=( |
| # include only slaves matching our pool |
| (me.c.poolid == peers.c.poolid) & |
| # .. and matching our silo |
| (me.c.distroid == peers.c.distroid) & |
| (me.c.bitsid == peers.c.bitsid) & |
| (me.c.purposeid == peers.c.purposeid) & |
| (me.c.dcid == peers.c.dcid) & |
| (me.c.trustid == peers.c.trustid) & |
| (me.c.envid == peers.c.envid) & |
| # .. but do not include the slave itself |
| (me.c.slaveid != peers.c.slaveid) |
| )) |
| |
| # find the peers' masters |
| peers_masters = me_and_peers.join(model.masters, onclause=( |
| (peers.c.current_masterid == model.masters.c.masterid) |
| )) |
| |
| # query all masterids with nonzero counts |
| nonzero_mastercounts = sa.select( |
| [model.masters.c.masterid, sa.func.count().label('slavecount')], |
| from_obj=peers_masters, |
| whereclause=(me.c.slaveid == sa.bindparam('slaveid')), |
| group_by=[model.masters.c.masterid], |
| ).alias('nonzero_mastercounts') |
| |
| # and join that as a subquery to get matching masters with no slaves |
| pool_masters = model.slaves.join(model.masters, |
| onclause=(model.slaves.c.poolid == model.masters.c.poolid)) |
| joined_masters = pool_masters.outerjoin(nonzero_mastercounts, |
| onclause=(model.masters.c.masterid == nonzero_mastercounts.c.masterid)) |
| |
| # the slave counts in joined_masters are None where we'd like 0, so fix |
| # that |
| numeric_slavecount = sa.case( |
| [(nonzero_mastercounts.c.slavecount == None, 0)], |
| else_=nonzero_mastercounts.c.slavecount) |
| numeric_slavecount = numeric_slavecount.label('numeric_slavecount') |
| |
| best_master = sa.select([model.masters], |
| from_obj=joined_masters, |
| whereclause=( |
| (model.slaves.c.slaveid == sa.bindparam('slaveid')) & |
| (model.masters.c.enabled) |
| ), |
| order_by=[ |
| numeric_slavecount, model.masters.c.masterid], |
| limit=1) |
| return best_master |
| best_master = best_master() |
| |
| slave_password = sa.select([model.slave_passwords.c.password], |
| from_obj=[model.slaves, model.slave_passwords], |
| whereclause=( |
| (model.slaves.c.slaveid == sa.bindparam('slaveid')) & |
| (model.slave_passwords.c.poolid == model.slaves.c.poolid) & |
| ((model.slave_passwords.c.distroid == None) | |
| (model.slave_passwords.c.distroid == model.slaves.c.distroid)))) |
