SQL

Selamat datang di bagian kesebelas FAQ OdooCamp: SQL. Prolog ini menjadi pengantar sebelum kita membahas pertanyaan teknis tentang kapan dan bagaimana memakai SQL secara aman di Odoo 18 CE.

Mengapa SQL (Masih) Relevan?

Secara umum, ORM Odoo sudah mencukupi untuk CRUD, relasi, domain, hingga agregasi (read_group). Namun, ada kasus tertentu di mana SQL berguna atau bahkan diperlukan:

• Optimasi khusus yang sulit dicapai lewat ORM.

• Migrasi/rekonsiliasi data dalam jumlah besar.

• Report/analitik kompleks (window function, CTE) yang tidak efisien jika ditulis di Python murni.

• Model berbasis SQL View (_auto = False) yang merangkum data lintas tabel.

Tetap ingat: SQL melewati sebagian mekanisme ORM (compute, onchange, akses record rule saat query manual). Karena itu, gunakan dengan disiplin dan audit.

Mindset Kerja

ORM First → SQL Bertarget → Aman & Idempoten → Terukur

• ORM dahulu: utamakan API ORM; beralih ke SQL jika ada alasan kuat (profiling/performa/kompleksitas query).

• Bertarget: batasi ruang lingkup query; hindari perubahan massal tanpa filter jelas.

• Aman: selalu gunakan parameter binding (anti‑injeksi); hormati akses & multi‑company.

• Terukur: uji dengan EXPLAIN/ANALYZE di DB dev; tambahkan indeks bila perlu.

Ruang Lingkup yang Akan Kita Bahas di FAQ SQL

1. SQL aman di Odoo: env.cr.execute() dengan parameter.

2. _sql_constraints vs constraint Python.

3. Model SQL View (_auto = False) dan strategi refresh/materialisasi.

4. Transaksi & locking: kapan FOR UPDATE, kapan menghindarinya.

5. Multi‑company & security: implikasi query manual terhadap data terbatasi.

6. Performa: indeks, batch, dan teknik menghindari full scan.

7. Diagnostik: logging query, mogrify, dan profiling.

Anatomi Minimal — Menjalankan SQL Aman

# Contoh update terparameter
self.env.cr.execute(
    """
    UPDATE sale_order
       SET x_flag = TRUE
     WHERE id = ANY(%s)
    """,
    [ids]  # ids = list/tuple of int
)

# Query + fetch
self.env.cr.execute("SELECT id, name FROM res_partner WHERE email = %s", [email])
rows = self.env.cr.fetchall()

Prinsip:

• Selalu gunakan parameter list (%s placeholders). Jangan format string manual untuk nilai.

• Untuk identifier dinamis (nama tabel/kolom), lakukan whitelisting atau gunakan utilitas psycopg2.sql dengan hati‑hati.

Constraint SQL vs Python

class MyModel(models.Model):
    _name = "my.model"
    _description = "Contoh SQL Constraint"
    _sql_constraints = [
        ("uniq_code", "unique(code)", "Kode harus unik."),
        ("amount_non_negative", "CHECK(amount >= 0)", "Amount tidak boleh negatif."),
    ]

• SQL constraint: dievaluasi di DB, kuat & konsisten.

• Python constraint (@api.constrains): fleksibel, bisa menampilkan pesan kontekstual, tetap berjalan lewat ORM.

• Seringkali dipakai bersama untuk jaring pengaman berlapis.

Model Berbasis SQL View (_auto = False)

from odoo import api, fields, models

class MyReport(models.Model):
    _name = "my.report"
    _description = "Laporan via SQL View"
    _auto = False  # tidak membuat tabel otomatis

    partner_id = fields.Many2one("res.partner")
    total     = fields.Float()

    @api.model
    def init(self):
        self.env.cr.execute("""
            CREATE OR REPLACE VIEW my_report AS (
                SELECT so.partner_id,
                       SUM(so.amount_total) AS total
                  FROM sale_order so
                 WHERE so.state IN ('sale','done')
              GROUP BY so.partner_id
            )
        """)

Catatan:

• Tidak bisa create/write langsung; view read‑only.

• Pastikan dependensi modul yang menyuplai tabel sumber sudah ada.

• Jika butuh performa & konsistensi snapshot, pertimbangkan materialized view (kelola refresh via cron/hook).

Transaksi, Locking, dan Concurrency

• Odoo mengelola transaksi per request/RPC. Hindari commit manual kecuali di skenario khusus (mis. batch panjang di cron).

• Gunakan SELECT ... FOR UPDATE bila perlu mengunci baris yang hendak dimutakhirkan untuk mencegah race condition.

• Hindari lock jangka panjang; pertimbangkan batch kecil dan retry jika terjadi deadlock.

Contoh ringkas:

self.env.cr.execute("SELECT id FROM my_table WHERE state = 'ready' FOR UPDATE SKIP LOCKED")

Multi‑Company & Security

• Query manual tidak otomatis memfilter company_id seperti record rules. Tambahkan filter eksplisit jika relevan.

• Hindari sudo() di jalur SQL tanpa alasan jelas; audit dampak akses.

Contoh:

allowed_companies = tuple(self.env.companies.ids)
self.env.cr.execute(
    "SELECT id FROM account_move WHERE company_id = ANY(%s)",
    [list(allowed_companies)]
)

Performa & Indeks

• Profiling di DB dev: EXPLAIN (ANALYZE, BUFFERS) untuk menemukan bottleneck.

• Tambahkan index di kolom filter/join yang sering dipakai (melalui migrasi/hook).

• Pilih batching (LIMIT/OFFSET atau kunci penanda) untuk proses massal.

Contoh menambah index di hook:

def post_init(cr, registry):
    cr.execute("CREATE INDEX IF NOT EXISTS idx_so_state ON sale_order(state)")

Diagnostik & Observabilitas

• Gunakan self.env.cr.mogrify(sql, params) untuk melihat query final (debug log).

• Naikkan level log SQL di environment dev bila perlu.

• Tulis helper logging agar jejak query mudah dilacak saat incident.

Best Practices (Do/Don’t)

Do

• ORM terlebih dahulu; SQL jika ada pembenaran kuat.

• Selalu pakai parameter binding (%s) untuk nilai.

• Hormati multi‑company dan security saat query manual.

• Uji performa dengan data realistis; tambahkan index jika perlu.

• Dokumentasikan alasan teknis dan dampak dari setiap query SQL.

Don’t

• Jangan concatenate nilai ke string SQL (raw format) → rawan injeksi.

• Jangan mengubah data yang dikelola compute/constraint tanpa memahami efeknya.

• Jangan menahan lock lama; hindari full table scan di jam sibuk.

Checklist Sebelum Masuk ke FAQ Detail

• Sudah mencoba solusi ORM (domain, read_group, compute stored) terlebih dahulu.

• Query menggunakan parameter binding dan filter company_id jika relevan.

• Performa diuji dengan EXPLAIN pada data realistis.

• Dampak transaksi/locking dipahami; tidak ada lock panjang.

• Dokumentasi teknis query tersedia untuk review.

Yang Akan Kita Jawab di FAQ SQL

• Kapan memilih SQL dibanding ORM, dan contoh keputusan berbasis metrik.

• Pola aman env.cr.execute() dan manajemen parameter.

• Menulis & memelihara SQL View (_auto = False) dan opsi materialized.

• Teknik batching, locking, dan recovery saat terjadi deadlock.

• Cara mendiagnosis query lambat dan strategi indexing yang efektif.

Dengan prolog ini, kita siap melangkah ke FAQ SQL dan membahas praktik konkret yang sering muncul pada proyek Odoo 18 CE tingkat lanjut.