Exploring SQL activity in Lino Tera¶

This document explores some SQL requests in Lino Tera. It is also a demo of the show_sql_queries function.

We use the lino_book.projects.tera1 demo database.

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

Startup¶

During startup there are a few SQL queries caused by lino_xl.lib.excerpts.models.set_excerpts_actions(), which is called during startup as a lino.core.signals.pre_analyze handler:

>>> show_sql_queries()
... 
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 = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT django_content_type.id, django_content_type.app_label, django_content_type.model FROM django_content_type WHERE django_content_type.id = ... LIMIT 21
SELECT invoicing_task.id, invoicing_task.seqno, invoicing_task.start_date, invoicing_task.start_time, invoicing_task.end_date, invoicing_task.end_time, invoicing_task.every_unit, invoicing_task.every, invoicing_task.positions, invoicing_task.monday, invoicing_task.tuesday, invoicing_task.wednesday, invoicing_task.thursday, invoicing_task.friday, invoicing_task.saturday, invoicing_task.sunday, invoicing_task.max_events, invoicing_task.user_id, invoicing_task.log_level, invoicing_task.disabled, invoicing_task.last_start_time, invoicing_task.last_end_time, invoicing_task.message, invoicing_task.procedure, invoicing_task.name, invoicing_task.target_journal_id, invoicing_task.max_date_offset, invoicing_task.today_offset FROM invoicing_task WHERE NOT invoicing_task.disabled
SELECT accounting_journal.id, accounting_journal.ref, accounting_journal.seqno, accounting_journal.name, accounting_journal.build_method, accounting_journal.template, accounting_journal.trade_type, accounting_journal.voucher_type, accounting_journal.journal_group, accounting_journal.auto_check_clearings, accounting_journal.auto_fill_suggestions, accounting_journal.force_sequence, accounting_journal.preliminary, accounting_journal.make_ledger_movements, accounting_journal.account_id, accounting_journal.partner_id, accounting_journal.printed_name, accounting_journal.dc, accounting_journal.yearly_numbering, accounting_journal.must_declare, accounting_journal.uploads_volume_id, accounting_journal.printed_name_de, accounting_journal.printed_name_fr, accounting_journal.name_de, accounting_journal.name_fr, accounting_journal.sepa_account_id FROM accounting_journal WHERE accounting_journal.id = 1 LIMIT 21

TODO: explain why django_content_type.id is not always the same.

>>> reset_sql_queries()

Now we run some action and look at the SQL queries resulting from it.

We run the run_update_plan() action of an accounting report (sheets.Report). You might want to read the Django documentation about Using aggregates within a Subquery expression.

>>> ses = rt.login("robin")
>>> obj = rt.models.sheets.Report.objects.get(pk=1)

>>> reset_sql_queries()
>>> obj.run_update_plan(ses)
>>> show_sql_summary()
... 
======================== =========== =======
 table                    stmt_type   count
------------------------ ----------- -------
                          COMMIT      4
                          INSERT      90
                          UNKNOWN     4
 accounting_account       SELECT      19
 ana_account              SELECT      16
 cal_event                SELECT      90
 cal_task                 SELECT      90
 checkdata_message        SELECT      90
 contacts_partner         SELECT      53
 django_content_type      SELECT      14
 excerpts_excerpt         SELECT      90
 invoicing_item           SELECT      90
 memo_mention             SELECT      180
 notes_note               SELECT      90
 periods_storedperiod     SELECT      2
 sheets_accountentry      DELETE      1
 sheets_accountentry      SELECT      1
 sheets_anaaccountentry   DELETE      1
 sheets_anaaccountentry   SELECT      1
 sheets_item              SELECT      29
 sheets_itementry         DELETE      1
 sheets_itementry         SELECT      2
 sheets_partnerentry      DELETE      1
 sheets_partnerentry      SELECT      1
 sheets_report            SELECT      90
 topics_tag               SELECT      90
 trading_invoiceitem      SELECT      90
 uploads_upload           SELECT      90
======================== =========== =======

TODO: above output shows some bug with parsing the statements, and then we must explain why there are so many select statements in unrelated tables (e.g. notes_note).

Here is an untested simplified log of the full SQL queries:

>>> show_sql_queries()
... 
SELECT ... FROM django_session WHERE (...)
SELECT users_user.id, ... FROM users_user WHERE users_user.id = 1
SELECT ... FROM ledger_accountingperiod WHERE ledger_accountingperiod.id = 1
SELECT ... FROM ledger_accountingperiod WHERE ledger_accountingperiod.id = 3
SELECT accounts_account.id, ...,
  (SELECT CAST(SUM(V0.amount) AS NUMERIC) AS total FROM ledger_movement V0
    INNER JOIN ledger_voucher V2 ON (V0.voucher_id = V2.id)
      WHERE (V0.account_id = (accounts_account.id)
        AND V2.accounting_period_id IN (SELECT U0.id AS Col1 FROM ledger_accountingperiod U0 WHERE U0.ref < '2015-01')
        AND V0.dc = 0)
        GROUP BY V0.account_id)
   AS old_c,
   (SELECT ...) AS during_d,
   (SELECT ...) AS during_c,
   (SELECT ...) AS old_d
   FROM accounts_account
     LEFT OUTER JOIN accounts_group ON (accounts_account.group_id = accounts_group.id)
       WHERE NOT ((SELECT CAST(SUM(V0.amount) AS NUMERIC) AS total FROM ledger_movement V0
         INNER JOIN ledger_voucher V2 ON (V0.voucher_id = V2.id)
         WHERE (V0.account_id = (accounts_account.id)
           AND V2.accounting_period_id IN (SELECT U0.id AS Col1 FROM ledger_accountingperiod U0 WHERE U0.ref < '2015-01')
           AND V0.dc = 0)
           GROUP BY V0.account_id) = '0'
       AND (...) = '0' AND (... = '0' AND (...) = '0')
   ORDER BY accounts_group.ref ASC, accounts_account.ref ASC
SELECT ... FROM system_siteconfig WHERE system_siteconfig.id = 1
SELECT ... FROM accounts_account WHERE accounts_account.id = 1
SELECT contacts_partner.id, ...,
  (SELECT CAST(SUM(V0.amount) AS NUMERIC) AS total
      FROM ledger_movement V0 INNER JOIN ledger_voucher V3 ON (V0.voucher_id = V3.id)
        WHERE (V0.partner_id = (contacts_partner.id) AND V0.account_id = 1
        AND V3.accounting_period_id IN (...) AND V0.dc = 0)
        GROUP BY V0.partner_id) AS old_c,
  (SELECT ...) AS during_d,
  (SELECT ...) AS during_c,
  (SELECT ...) AS old_d
  FROM contacts_partner
  WHERE NOT (...)
  ORDER BY contacts_partner.name ASC, contacts_partner.id ASC
SELECT ... FROM accounts_account WHERE accounts_account.id = 2
SELECT contacts_partner.id, contacts_partner.email, ...
  (SELECT CAST(SUM(V0.amount) AS NUMERIC) AS total
     FROM ledger_movement V0
     INNER JOIN ledger_voucher V3 ON (V0.voucher_id = V3.id)
       WHERE (V0.partner_id = (contacts_partner.id) AND V0.account_id = 2
         AND V3.accounting_period_id IN (...) AND V0.dc = 0)
       GROUP BY V0.partner_id)
    AS old_c,
  (SELECT ...) AS during_c,
  (SELECT ...) AS old_d
  FROM contacts_partner
  WHERE NOT (...)
  ORDER BY contacts_partner.name ASC, contacts_partner.id ASC
SELECT ... FROM users_user WHERE users_user.username = 'robin'