Odoo　View 既存データ流し込み

WITH params AS (
  SELECT 'sale.order'::text AS model_name
),
src AS (
  SELECT
    v.id AS odoo_view_id,
    v.model,
    CASE v.type
      WHEN 'tree'     THEN 'list'
      WHEN 'form'     THEN 'form'
      WHEN 'kanban'   THEN 'kanban'
      WHEN 'calendar' THEN 'calendar'
      WHEN 'search'   THEN 'search'
      WHEN 'graph'    THEN 'graph'
      WHEN 'pivot'    THEN 'pivot'
      WHEN 'gantt'    THEN 'gantt'
      ELSE 'other'
    END AS view_type_norm,

    /* name が jsonb でも text でも安全に取得 */
    CASE
      WHEN v.name IS NULL THEN NULL
      WHEN v.name::text LIKE '{%' THEN
        COALESCE( (v.name::jsonb)->>'ja_JP'
                , (v.name::jsonb)->>'en_US'
                , v.name::text )
      ELSE
        v.name::text
    END AS view_name,

    v.priority AS priority_num,
    CASE
      WHEN v.priority <= 8  THEN 'high'
      WHEN v.priority >= 25 THEN 'low'
      ELSE 'standard'
    END AS priority_ui_txt,

    v.arch_db,
    v.inherit_id,
    v.write_date,

    -- 出自判定に使う model_data
    imd.module AS origin_module,
    (imd.module || '.' || imd.name) AS xmlid,

    -- Studio由来の簡易判定（web_studio が付与するXMLID）
    (imd.module = 'web_studio') AS is_studio
  FROM ir_ui_view v
  JOIN params p ON v.model = p.model_name
  LEFT JOIN ir_model_data imd
    ON imd.model = 'ir.ui.view' AND imd.res_id = v.id
)
INSERT INTO portal_view (
  source_view_id, source_xmlid,
  model, view_type, view_name,
  priority_ui, priority_num, enabled, ai_purpose,
  arch_db_snapshot,
  is_custom,               -- ← ここは Studio 由来フラグとして扱う
  origin,                  -- standard / module / studio / portal / unknown
  origin_module
)
SELECT
  s.odoo_view_id,
  s.xmlid,
  s.model,
  s.view_type_norm::view_type,
  s.view_name,
  s.priority_ui_txt::view_priority,
  s.priority_num,
  TRUE,
  NULL,
  s.arch_db,
  s.is_studio,                                        -- is_custom
  CASE
    WHEN s.is_studio                 THEN 'studio'
    WHEN s.origin_module IS NOT NULL THEN 'module'
    ELSE 'standard'
  END::view_origin,
  s.origin_module
FROM src s;


-- ============================================
-- 3) portal_view_common を arch_db から自動生成
--    ・display_fields：<field name="..."> を順序抽出
--    ・list の default_order を抽出
-- ============================================
WITH params AS (
  SELECT 'sale.order'::text AS model_name
),
pv AS (
  SELECT id, arch_db_snapshot, view_type
  FROM portal_view
  JOIN params p ON portal_view.model = p.model_name
),

-- 1) <field name="..."> を順序付きで全行抽出
fields AS (
  SELECT
    pv.id AS view_id,
    (regexp_matches(pv.arch_db_snapshot, '<field\s+name="([a-zA-Z0-9_\.]+)"', 'g'))[1] AS fname,
    row_number() OVER (PARTITION BY pv.id ORDER BY ordinality) AS rn
  FROM pv,
       regexp_matches(pv.arch_db_snapshot, '<field\s+name="([a-zA-Z0-9_\.]+)"', 'g') WITH ORDINALITY
),

-- 2) 同一フィールド名の最初の出現位置だけ残す
first_pos AS (
  SELECT view_id, fname, MIN(rn) AS first_rn
  FROM fields
  GROUP BY view_id, fname
),

-- 3) first_rn で並べ替えたリストを jsonb_agg
field_agg AS (
  SELECT
    view_id,
    jsonb_agg(fname ORDER BY first_rn) AS display_fields
  FROM first_pos
  GROUP BY view_id
),

-- 4) list（tree）ビューの default_order を抽出（無ければ NULL）
order_raw AS (
  SELECT
    pv.id AS view_id,
    (regexp_matches(pv.arch_db_snapshot, 'default_order="([a-zA-Z0-9_]+)\s+(asc|desc)"'))[1] AS sort_field,
    (regexp_matches(pv.arch_db_snapshot, 'default_order="([a-zA-Z0-9_]+)\s+(asc|desc)"'))[2] AS sort_dir_txt
  FROM pv
  WHERE pv.view_type = 'list'
),
order_norm AS (
  SELECT
    view_id,
    sort_field,
    CASE WHEN lower(sort_dir_txt) = 'asc' THEN 'asc'::sort_order ELSE 'desc'::sort_order END AS sort_dir
  FROM order_raw
)

INSERT INTO portal_view_common (view_id, display_fields, sort_field, sort_dir, default_group_by, default_filters)
SELECT
  pv.id,
  COALESCE(fa.display_fields, '[]'::jsonb) AS display_fields, -- 見つからない場合は空配列
  onm.sort_field,
  onm.sort_dir,
  NULL,
  NULL
FROM (SELECT id FROM portal_view pv JOIN params p ON pv.model=p.model_name) pv
LEFT JOIN field_agg fa ON fa.view_id = pv.id
LEFT JOIN order_norm onm ON onm.view_id = pv.id
ON CONFLICT (view_id) DO UPDATE
  SET display_fields = EXCLUDED.display_fields,
      sort_field     = EXCLUDED.sort_field,
      sort_dir       = EXCLUDED.sort_dir;

-- 確認　select * from portal_view p

--下記は出自の追加（標準ORカスタム）



-- 1. portal_view にカラムを追加

ALTER TABLE portal_view
    ADD COLUMN IF NOT EXISTS is_custom BOOLEAN DEFAULT FALSE,                 -- Studioなどカスタムフラグ
    ADD COLUMN IF NOT EXISTS origin view_origin DEFAULT 'unknown',            -- 出自: standard/module/studio/portal/unknown
    ADD COLUMN IF NOT EXISTS origin_module TEXT,                              -- モジュール名
    ADD COLUMN IF NOT EXISTS is_portal_generated BOOLEAN DEFAULT FALSE,       -- ポータルで生成したもの
    ADD COLUMN IF NOT EXISTS is_modified BOOLEAN;                             -- Odoo本体との差分有無

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'view_origin') THEN
        CREATE TYPE view_origin AS ENUM ('standard','studio','module','portal','unknown');
    END IF;
END$$;

--2. 既存データの origin を埋める

WITH src AS (
    SELECT
        v.id AS view_id,
        CASE
            WHEN imd.module = 'web_studio' THEN 'studio'
            WHEN imd.module IS NOT NULL   THEN 'module'
            ELSE 'standard'
        END::view_origin AS origin,
        (imd.module = 'web_studio') AS is_custom,
        imd.module AS origin_module
    FROM ir_ui_view v
    LEFT JOIN ir_model_data imd
      ON imd.model = 'ir.ui.view'
     AND imd.res_id = v.id
)
UPDATE portal_view pv
SET
    origin        = src.origin,
    is_custom    = src.is_custom,
    origin_module = src.origin_module
FROM src
WHERE pv.source_view_id = src.view_id;

SELECT id, model, view_type, view_name, origin, origin_module, is_custom
FROM portal_view
ORDER BY model, view_type, id;

2. 既存データの origin を埋める

Odooのビューの出自は以下で判定します：

• Studioビュー → ir_model_data.module = 'web_studio'
• モジュール定義 → ir_model_data に module が入っている
• それ以外 → standard

3. 動作確認

SELECT id, model, view_type, view_name, origin, origin_module, is_custom
FROM portal_view
ORDER BY model, view_type, id;

4. 今後の運用

• 新規ビューをポータルで生成した場合 → is_portal_generated = true, origin = 'portal'
• Odoo標準との差分監査 → is_modified を arch_db_snapshot と Odoo本体の ir_ui_view.arch_db を比較して更新
• Studio作成かどうかは is_custom でも origin = 'studio' でも判定可能

次のステップとして、
ポータルの画面で 標準 / Studio / モジュール / ポータル生成 を簡単にフィルタできるようにしたいと思いますが、
この origin フィールドを使えばすぐ対応できます。