def test_group_concat_sqlite_one_arg(db_session):
"""
It should use SQLite's deafult arguments (comma delimiter)
"""
from sqlalchemy import literal_column
from occams_datastore.utils.sql import group_concat
if db_session.bind.url.drivername != 'sqlite':
pytest.skip('Not using SQLite')
data = (
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'foo'").label('value'))
.union(
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'bar'").label('value')))
.subquery())
query = (
db_session.query(group_concat(data.c.value))
.select_from(data)
.group_by(data.c.name))
result, = query.one()
assert sorted(['foo', 'bar']) == sorted(result.split(','))
def test_group_concat_postgresql_invalid_args(db_session):
"""
It should only support at least two arguments in PostgreSQL
"""
from sqlalchemy import literal_column
from occams_datastore.utils.sql import group_concat
if db_session.bind.url.drivername != 'postgresql':
pytest.skip('Not using PostgreSQL')
data = (
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'foo'").label('value'))
.union(
db_session.query(
literal_column("'myitem'").label('name'),
literal_column("'bar'").label('value')))
.subquery())
query = (
db_session.query(group_concat(data.c.value))
.select_from(data)
.group_by(data.c.name))
with pytest.raises(TypeError):
result, = query.one()
def data(self,
use_choice_labels=False,
expand_collections=False,
ignore_private=True):
session = self.db_session
query = (
session.query(
models.Patient.id.label('id'),
models.Site.name.label('site'),
models.Patient.pid.label('pid'))
.join(models.Site))
# BBB 2014-02-20 (Marco): AEH needs Early Test
EarlyTest = aliased(models.Enrollment)
subquery = (
session.query(EarlyTest.patient_id, EarlyTest.reference_number)
.filter(EarlyTest.study.has(
models.Study.code.in_([literal_column("'ET'"),
literal_column("'LTW'"),
literal_column("'CVCT'")])))
.subquery())
query = (
query
.outerjoin(subquery, subquery.c.patient_id == models.Patient.id)
.add_column(subquery.c.reference_number.label('early_id')))
# Add every known reference number
for reftype in self.reftypes:
query = query.add_column(
session.query(
group_concat(
models.PatientReference.reference_number, ';'))
.filter(
models.PatientReference.patient_id == models.Patient.id)
.filter(
models.PatientReference.reference_type_id == reftype.id)
.group_by(models.PatientReference.patient_id)
.correlate(models.Patient)
.as_scalar()
.label(reftype.name))
CreateUser = aliased(datastore.User)
ModifyUser = aliased(datastore.User)
query = (
query
.join(CreateUser, models.Patient.create_user)
.join(ModifyUser, models.Patient.modify_user)
.add_columns(
models.Patient.create_date,
CreateUser.key.label('create_user'),
models.Patient.modify_date,
ModifyUser.key.label('modify_user'))
.order_by(models.Patient.id))
return query
def _list_schemata_info(db_session):
InnerSchema = orm.aliased(datastore.Schema)
OuterSchema = orm.aliased(datastore.Schema)
schemata_query = (
db_session.query(OuterSchema.name.label('name'))
.add_column(literal_column("'schema'").label('type'))
.add_column(
db_session.query(datastore.Attribute)
.filter(datastore.Attribute.is_private)
.join(InnerSchema)
.filter(InnerSchema.name == OuterSchema.name)
.correlate(OuterSchema)
.exists()
.label('has_private'))
.add_column(
db_session.query(datastore.Entity)
.join(datastore.Entity.contexts)
.filter(datastore.Context.external == 'stratum')
.join(models.Stratum, datastore.Context.key == models.Stratum.id)
.join(InnerSchema, datastore.Entity.schema)
.filter(InnerSchema.name == OuterSchema.name)
.correlate(OuterSchema)
.exists()
.label('has_rand'))
.add_column(
db_session.query(InnerSchema.title)
.select_from(InnerSchema)
.filter(InnerSchema.name == OuterSchema.name)
.filter(InnerSchema.publish_date != null())
.filter(InnerSchema.retract_date == null())
.order_by(InnerSchema.publish_date.desc())
.limit(1)
.correlate(OuterSchema)
.as_scalar()
.label('title'))
.add_column(
db_session.query(
group_concat(to_date(InnerSchema.publish_date), ';'))
.filter(InnerSchema.name == OuterSchema.name)
.filter(InnerSchema.publish_date != null())
.filter(InnerSchema.retract_date == null())
.group_by(InnerSchema.name)
.correlate(OuterSchema)
.as_scalar()
.label('versions'))
.filter(OuterSchema.publish_date != null())
.filter(OuterSchema.retract_date == null()))
schemata_query = (
schemata_query
.group_by(OuterSchema.name)
.from_self())
return schemata_query
def data(self,
use_choice_labels=False,
expand_collections=False,
ignore_private=True):
session = self.db_session
query = (session.query(models.Patient.id.label('id'),
models.Site.name.label('site'),
models.Patient.pid.label('pid')).join(
models.Site))
# BBB 2014-02-20 (Marco): AEH needs Early Test
EarlyTest = aliased(models.Enrollment)
subquery = (session.query(EarlyTest.patient_id,
EarlyTest.reference_number).filter(
EarlyTest.study.has(
models.Study.code.in_([
literal_column("'ET'"),
literal_column("'LTW'"),
literal_column("'CVCT'")
]))).subquery())
query = (query.outerjoin(
subquery, subquery.c.patient_id == models.Patient.id).add_column(
subquery.c.reference_number.label('early_id')))
# Add every known reference number
for reftype in self.reftypes:
query = query.add_column(
session.query(
group_concat(
models.PatientReference.reference_number,
';')).filter(models.PatientReference.patient_id ==
models.Patient.id).
filter(models.PatientReference.reference_type_id ==
reftype.id).group_by(
models.PatientReference.patient_id).correlate(
models.Patient).as_scalar().label(reftype.name))
CreateUser = aliased(datastore.User)
ModifyUser = aliased(datastore.User)
query = (query.join(CreateUser, models.Patient.create_user).join(
ModifyUser, models.Patient.modify_user).add_columns(
models.Patient.create_date,
CreateUser.key.label('create_user'),
models.Patient.modify_date,
ModifyUser.key.label('modify_user')).order_by(
models.Patient.id))
return query
def test_group_concat_single_value(db_session):
"""
It should be able to handle a single value
"""
from sqlalchemy import literal_column, cast, Unicode
from occams_datastore.utils.sql import group_concat
data = (
db_session.query(
cast(literal_column("'myitem'"), Unicode).label('name'),
cast(literal_column("'foo'"), Unicode).label('value'))
.subquery())
query = (
db_session.query(group_concat(data.c.value, ';'))
.select_from(data)
.group_by(data.c.name))
result, = query.one()
assert sorted(['foo']) == sorted(result.split(';'))
def data(self,
use_choice_labels=False,
expand_collections=False,
ignore_private=True):
session = self.db_session
ids_query = (
session.query(datastore.Schema.id)
.filter(datastore.Schema.publish_date.in_(self.versions)))
ids = [id for id, in ids_query]
report = build_report(
session,
self.name,
ids=ids,
expand_collections=expand_collections,
use_choice_labels=use_choice_labels,
ignore_private=ignore_private)
query = (
session.query(report.c.id.label('id'))
.add_column(
session.query(models.Patient.pid)
.join(datastore.Context,
(datastore.Context.external == u'patient')
& (datastore.Context.key == models.Patient.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('pid'))
.add_column(
session.query(models.Site.name)
.select_from(models.Patient)
.join(models.Site)
.join(datastore.Context,
(datastore.Context.external == u'patient')
& (datastore.Context.key == models.Patient.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('site'))
.add_column(
session.query(group_concat(models.Study.name, ';'))
.select_from(models.Enrollment)
.join(models.Study)
.join(datastore.Context,
(datastore.Context.external == u'enrollment')
& (datastore.Context.key == models.Enrollment.id))
.filter(datastore.Context.entity_id == report.c.id)
.group_by(datastore.Context.entity_id)
.correlate(report)
.as_scalar()
.label('enrollment'))
.add_column(
session.query(group_concat(models.Enrollment.id, ';'))
.select_from(models.Enrollment)
.join(datastore.Context,
(datastore.Context.external == u'enrollment')
& (datastore.Context.key == models.Enrollment.id))
.filter(datastore.Context.entity_id == report.c.id)
.group_by(datastore.Context.entity_id)
.correlate(report)
.as_scalar()
.label('enrollment_ids'))
)
if self._is_aeh_partner_form:
PartnerPatient = orm.aliased(models.Patient)
query = (
query
.add_column(
session.query(models.Partner.id)
.select_from(models.Partner)
.join(datastore.Context,
(datastore.Context.external == u'partner')
& (datastore.Context.key == models.Partner.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('partner_id'))
.add_column(
session.query(PartnerPatient.pid)
.select_from(models.Partner)
.join(PartnerPatient, models.Partner.enrolled_patient)
.join(datastore.Context,
(datastore.Context.external == u'partner')
& (datastore.Context.key == models.Partner.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('partner_pid')))
if self.has_rand:
query = (
query
.add_column(
session.query(models.Stratum.block_number)
.select_from(models.Stratum)
.join(datastore.Context,
(datastore.Context.external == u'stratum')
& (datastore.Context.key == models.Stratum.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('block_number'))
.add_column(
session.query(models.Stratum.randid)
.select_from(models.Stratum)
.join(datastore.Context,
(datastore.Context.external == u'stratum')
& (datastore.Context.key == models.Stratum.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('randid'))
.add_column(
session.query(models.Arm.title)
.select_from(models.Stratum)
.join(datastore.Context,
(datastore.Context.external == u'stratum')
& (datastore.Context.key == models.Stratum.id))
.filter(datastore.Context.entity_id == report.c.id)
.join(models.Stratum.arm)
.correlate(report)
.as_scalar()
.label('arm_name')))
query = (
query
.add_column(
session.query(group_concat(models.Study.title
+ literal_column(u"'('")
+ cast(models.Cycle.week, String)
+ literal_column(u"')'"),
literal_column(u"';'")))
.select_from(models.Visit)
.join(models.Visit.cycles)
.join(models.Cycle.study)
.join(datastore.Context,
(datastore.Context.external == u'visit')
& (datastore.Context.key == models.Visit.id))
.filter(datastore.Context.entity_id == report.c.id)
.group_by(datastore.Context.entity_id)
.correlate(report)
.as_scalar()
.label('visit_cycles'))
.add_column(
session.query(models.Visit.id)
.select_from(models.Visit)
.join(datastore.Context,
(datastore.Context.external == u'visit')
& (datastore.Context.key == models.Visit.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('visit_id'))
.add_column(
session.query(models.Visit.visit_date)
.select_from(models.Visit)
.join(datastore.Context,
(datastore.Context.external == u'visit')
& (datastore.Context.key == models.Visit.id))
.filter(datastore.Context.entity_id == report.c.id)
.correlate(report)
.as_scalar()
.label('visit_date'))
)
query = query.add_columns(
*[c for c in report.columns if c.name != 'id'])
return query