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.lydia demo database.
>>> from lino import startup
>>> startup('lino_book.projects.lydia.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
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
------------------------- ----------- -------
INSERT 90
UNKNOWN 4
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
ledger_account SELECT 19
ledger_accountingperiod SELECT 2
memo_mention SELECT 180
notes_note SELECT 90
sales_invoiceitem SELECT 90
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_interest 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'
