def list_instruments(offset=0, limit=0):
"""Supports a view of all 'instruments' at the company
This is an example of how a custom view can be made for a Web UI widget that is
trying to show all instrumennts. e.g. an equipment auditing page. Any subsequent
edits or deletes to entries would call the respecting freezer or computer API.
"""
q, params = db.paginate(
# show all computer "instruments" first
'(SELECT'
' s.id, s.name as site_name,'
' c.id AS computer_id,'
' NULL as freezer_id,'
' c.name AS instrument_name'
' FROM site AS s FULL JOIN computer AS c ON s.id = c.site_id'
' WHERE c.id IS NOT NULL ORDER BY s.id, c.id ASC)'
'UNION '
# show all freezer "instruments" second
'(SELECT'
' s.id, s.name as site_name,'
' NULL AS computer_id,'
' f.id AS freezer_id,'
' f.name AS freezer_name'
' FROM site AS s FULL JOIN freezer AS f ON s.id = f.site_id'
' WHERE f.id IS NOT NULL ORDER BY s.id, f.id ASC)',
offset, limit
)
# ensure no SQL injection attacks or accidental bad formatting
query = sql.SQL(q).format(**params)
# stream result without buffering in memory
for record in db.query(query):
yield record
def list_sites(offset=0, limit=0):
# build up a pagable query of sites
q, params = db.paginate('SELECT * FROM site', offset, limit)
# ensure no SQL injection attacks or accidental bad formatting
query = sql.SQL(q).format(**params)
# stream result without buffering in memory
for record in db.query(query):
yield record