.. doctest docs/specs/noi/sql.rst
.. include:: /../docs/shared/include/defs.rst
.. _specs.noi.sql:

==================================
Exploring SQL activity in Lino Noi
==================================

This document helps us to inspect and visualize some performance problems
encountered on Jane.


.. contents::
  :local:

.. include:: /../docs/shared/include/tested.rst

We use the :mod:`lino_book.projects.noi1e` demo database.

>>> from lino import startup
>>> startup('lino_book.projects.noi1e.settings.demo')
>>> from lino.api.doctest import *



Dashboard is slow after Django upgrade from 1 to 2
==================================================

The following helped us to fix :ticket:`2782`. When rendering the |insert|
button of the insert action of MyTickets in the dashboard, Lino needs to create
a temporary ticket object because get_status() must specify the known values
for the insert window that will potentially open when you click on the +
button. But Lino created a data_record with *all* detail fields, including e.g.
DuplicatesByTicket (a slave table that shows allduplicates of this ticket).
These slave tables make no sense on a master without id (which has not . For
some reason Django2 now executued a db query for each of them. Django1 disdn't,
but the problem was was alos there. We now changed the core so that
BaseRequest.elem2rec_insert() specifies only the fields needed by the window.


>>> lh = tickets.MyTickets.insert_layout.get_layout_handle(settings.SITE.kernel.default_ui)
>>> print(" ".join([f.name for f in lh._store_fields]))
summary ticket_type priority end_user site description
>>> ba = tickets.MyTickets.insert_action
>>> ba
<BoundAction(tickets.MyTickets, <lino.core.actions.ShowInsert insert ('New')>)>
>>> ses = rt.login("robin")
>>> ar = tickets.MyTickets.request_from(ses)
>>> st = ba.action.get_status(ar)
>>> print(' '.join(st.keys()))
data_record
>>> print(' '.join(st['data_record'].keys()))  #doctest: +SKIP
phantom data title
>>> st['data_record']['phantom']
True
>>> st['data_record']['data']  #doctest: +SKIP
>>> 'DuplicatesByTicket' in st['data_record']['data']
False

>>> st['data_record']['data']['DuplicatesByTicket']  #doctest: +ELLIPSIS +SKIP
<Element table at ...>

Viewing the generated SQL
=========================

The following is an example of you you can print the SQL generated by some
query.

>>> import sqlparse
>>> qs = tickets.Tickets.request().get_data_iterator()
>>> sql = str(qs.query).replace('"', '')
>>> print(sqlparse.format(sql, reindent=True, keyword_case='upper'))
... #doctest: +ELLIPSIS +NORMALIZE_WHITESPACE +REPORT_UDIFF
SELECT tickets_ticket.id,
       tickets_ticket.modified,
       tickets_ticket.created,
       tickets_ticket.ref,
       tickets_ticket.user_id,
       tickets_ticket.assigned_to_id,
       tickets_ticket.priority,
       tickets_ticket.closed,
       tickets_ticket.planned_time,
       tickets_ticket.site_id,
       tickets_ticket.private,
       tickets_ticket.summary,
       tickets_ticket.description,
       tickets_ticket.upgrade_notes,
       tickets_ticket.ticket_type_id,
       tickets_ticket.duplicate_of_id,
       tickets_ticket.end_user_id,
       tickets_ticket.state,
       tickets_ticket.deadline,
       tickets_ticket.reporter_id,
       tickets_ticket.waiting_for,
       tickets_ticket.feedback,
       tickets_ticket.standby,
       tickets_ticket.fixed_since,
       tickets_ticket.last_commenter_id,
       tickets_ticket.regular_hours,
       tickets_ticket.extra_hours,
       tickets_ticket.free_hours,
       contacts_partner.id,
       contacts_partner.email,
       contacts_partner.language,
       contacts_partner.url,
       contacts_partner.phone,
       contacts_partner.gsm,
       contacts_partner.fax,
       contacts_partner.country_id,
       contacts_partner.city_id,
       contacts_partner.zip_code,
       contacts_partner.region_id,
       contacts_partner.addr1,
       contacts_partner.street_prefix,
       contacts_partner.street,
       contacts_partner.street_no,
       contacts_partner.street_box,
       contacts_partner.addr2,
       contacts_partner.prefix,
       contacts_partner.name,
       contacts_partner.remarks,
       contacts_partner.payment_term_id,
       contacts_partner.vat_regime,
       contacts_partner.vat_id,
       contacts_partner.purchase_account_id,
       contacts_person.partner_ptr_id,
       contacts_person.title,
       contacts_person.first_name,
       contacts_person.middle_name,
       contacts_person.last_name,
       contacts_person.gender,
       contacts_person.birth_date,
       users_user.person_ptr_id,
       users_user.modified,
       users_user.created,
       users_user.start_date,
       users_user.end_date,
       users_user.password,
       users_user.last_login,
       users_user.username,
       users_user.user_type,
       users_user.initials,
       users_user.time_zone,
       users_user.callme_mode,
       users_user.verification_code,
       users_user.user_state,
       users_user.access_class,
       users_user.event_type_id,
       users_user.open_session_on_new_ticket,
       users_user.notify_myself,
       users_user.mail_mode,
       users_user.github_username,
       T8.id,
       T8.email,
       T8.language,
       T8.url,
       T8.phone,
       T8.gsm,
       T8.fax,
       T8.country_id,
       T8.city_id,
       T8.zip_code,
       T8.region_id,
       T8.addr1,
       T8.street_prefix,
       T8.street,
       T8.street_no,
       T8.street_box,
       T8.addr2,
       T8.prefix,
       T8.name,
       T8.remarks,
       T8.payment_term_id,
       T8.vat_regime,
       T8.vat_id,
       T8.purchase_account_id,
       T7.partner_ptr_id,
       T7.title,
       T7.first_name,
       T7.middle_name,
       T7.last_name,
       T7.gender,
       T7.birth_date,
       T6.person_ptr_id,
       T6.modified,
       T6.created,
       T6.start_date,
       T6.end_date,
       T6.password,
       T6.last_login,
       T6.username,
       T6.user_type,
       T6.initials,
       T6.time_zone,
       T6.callme_mode,
       T6.verification_code,
       T6.user_state,
       T6.access_class,
       T6.event_type_id,
       T6.open_session_on_new_ticket,
       T6.notify_myself,
       T6.mail_mode,
       T6.github_username,
       T9.id,
       T9.modified,
       T9.created,
       T9.ref,
       T9.user_id,
       T9.assigned_to_id,
       T9.priority,
       T9.closed,
       T9.planned_time,
       T9.site_id,
       T9.private,
       T9.summary,
       T9.description,
       T9.upgrade_notes,
       T9.ticket_type_id,
       T9.duplicate_of_id,
       T9.end_user_id,
       T9.state,
       T9.deadline,
       T9.reporter_id,
       T9.waiting_for,
       T9.feedback,
       T9.standby,
       T9.fixed_since,
       T9.last_commenter_id,
       T9.regular_hours,
       T9.extra_hours,
       T9.free_hours,
       T11.id,
       T11.email,
       T11.language,
       T11.url,
       T11.phone,
       T11.gsm,
       T11.fax,
       T11.country_id,
       T11.city_id,
       T11.zip_code,
       T11.region_id,
       T11.addr1,
       T11.street_prefix,
       T11.street,
       T11.street_no,
       T11.street_box,
       T11.addr2,
       T11.prefix,
       T11.name,
       T11.remarks,
       T11.payment_term_id,
       T11.vat_regime,
       T11.vat_id,
       T11.purchase_account_id,
       T10.partner_ptr_id,
       T10.title,
       T10.first_name,
       T10.middle_name,
       T10.last_name,
       T10.gender,
       T10.birth_date
FROM tickets_ticket
INNER JOIN tickets_site ON (tickets_ticket.site_id = tickets_site.id)
LEFT OUTER JOIN users_user ON (tickets_ticket.user_id = users_user.person_ptr_id)
LEFT OUTER JOIN contacts_person ON (users_user.person_ptr_id = contacts_person.partner_ptr_id)
LEFT OUTER JOIN contacts_partner ON (contacts_person.partner_ptr_id = contacts_partner.id)
LEFT OUTER JOIN users_user T6 ON (tickets_ticket.assigned_to_id = T6.person_ptr_id)
LEFT OUTER JOIN contacts_person T7 ON (T6.person_ptr_id = T7.partner_ptr_id)
LEFT OUTER JOIN contacts_partner T8 ON (T7.partner_ptr_id = T8.id)
LEFT OUTER JOIN tickets_ticket T9 ON (tickets_ticket.duplicate_of_id = T9.id)
LEFT OUTER JOIN contacts_person T10 ON (tickets_ticket.end_user_id = T10.partner_ptr_id)
LEFT OUTER JOIN contacts_partner T11 ON (T10.partner_ptr_id = T11.id)
WHERE (NOT tickets_ticket.private
       AND NOT tickets_site.private)
ORDER BY tickets_ticket.id DESC

During startup there were two SQL queries:

>>> show_sql_queries()  #doctest: +ELLIPSIS +NORMALIZE_WHITESPACE
SELECT excerpts_excerpttype.id, excerpts_excerpttype.name, excerpts_excerpttype.build_method, excerpts_excerpttype.template, excerpts_excerpttype.attach_to_email, excerpts_excerpttype.email_template, excerpts_excerpttype.certifying, excerpts_excerpttype.remark, excerpts_excerpttype.body_template, excerpts_excerpttype.content_type_id, excerpts_excerpttype.primary, excerpts_excerpttype.backward_compat, excerpts_excerpttype.print_recipient, excerpts_excerpttype.print_directly, excerpts_excerpttype.shortcut, excerpts_excerpttype.name_de, excerpts_excerpttype.name_fr FROM excerpts_excerpttype ORDER BY excerpts_excerpttype.id ASC
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ...
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ...


Now we do a single request to :class:`Tickets`. And look at all the
SQL that poor Django must do in order to return a single row.

>>> reset_sql_queries()
>>> r = demo_get('robin','api/tickets/Tickets', fmt='json')

>> r = demo_get('robin','api/tickets/Tickets', fmt='json', limit=1)
>> res = test_client.get('/api/tickets/Tickets?fmt=json&limit=1')
>> res = check_json_result(res)
>> rmu(res.keys())
['count', 'rows', 'no_data_text', 'success', 'title', 'param_values']
>> len(res['rows'])
1

>>> show_sql_summary()
================= =========== =======
 table             stmt_type   count
----------------- ----------- -------
                   UNKNOWN     1
                   UPDATE      1
 django_session    SELECT      1
 tickets_site      SELECT      13
 tickets_ticket    SELECT      2
 users_user        SELECT      1
 working_session   SELECT      15
================= =========== =======
<BLANKLINE>

>>> show_sql_queries()
... #doctest: +ELLIPSIS +NORMALIZE_WHITESPACE +REPORT_UDIFF +SKIP


To verify whether the slave summary panels are being computed:

>>> for f in sorted([str(f) for f in rt.models.tickets.Tickets.wildcard_data_elems()]):
...     print(f)  #doctest: +REPORT_UDIFF
lino_noi.lib.tickets.models.Ticket.created_natural
lino_noi.lib.tickets.models.Ticket.name_column
lino_noi.lib.tickets.models.Ticket.overview
lino_noi.lib.tickets.models.Ticket.quick_assign_to
lino_noi.lib.tickets.models.Ticket.workflow_buttons
tickets.Ticket.assigned_to
tickets.Ticket.closed
tickets.Ticket.comments
tickets.Ticket.created
tickets.Ticket.deadline
tickets.Ticket.description
tickets.Ticket.duplicate_of
tickets.Ticket.end_user
tickets.Ticket.extra_hours
tickets.Ticket.feedback
tickets.Ticket.fixed_since
tickets.Ticket.free_hours
tickets.Ticket.id
tickets.Ticket.last_commenter
tickets.Ticket.modified
tickets.Ticket.planned_time
tickets.Ticket.priority
tickets.Ticket.private
tickets.Ticket.ref
tickets.Ticket.regular_hours
tickets.Ticket.reporter
tickets.Ticket.site
tickets.Ticket.standby
tickets.Ticket.state
tickets.Ticket.summary
tickets.Ticket.ticket_type
tickets.Ticket.upgrade_notes
tickets.Ticket.user
tickets.Ticket.waiting_for