Odooメタ抽出

-- =====================================================
-- Rebuild ALL under PUBLIC with "ir_" prefix (from Odoo)
--   Creates & fills:
--     - public.ir_model_src
--     - public.ir_field_src
--     - public.ir_view_src  (action-centric per your spec)
--   Source schema: odoo_fdw.*
-- =====================================================

-- 0) clean
DROP TABLE IF EXISTS public.ir_view_src;
DROP TABLE IF EXISTS public.ir_field_src;
DROP TABLE IF EXISTS public.ir_model_src;

-- 1) create tables
CREATE TABLE public.ir_model_src (
  model        TEXT NOT NULL,
  model_table  TEXT NOT NULL,
  label_en_us  TEXT,
  label_ja_jp  TEXT,
  label_i18n   JSONB,
  notes        TEXT,
  created_at   TIMESTAMPTZ,
  updated_at   TIMESTAMPTZ
);

CREATE TABLE public.ir_field_src (
  model          TEXT NOT NULL,
  model_table    TEXT NOT NULL,
  field_name     TEXT NOT NULL,
  ttype          TEXT NOT NULL,
  label_en_us    TEXT,
  label_ja_jp    TEXT,
  label_i18n     JSONB,
  code_status    TEXT,
  notes          TEXT,
  origin         TEXT,
  show_invisible TEXT,
  pk_columns     TEXT[],
  is_pk          BOOLEAN
);

CREATE TABLE public.ir_view_src (
  action_xmlid        TEXT,
  action_id           BIGINT,
  action_name         TEXT,
  model_label         TEXT,
  model_tech          TEXT,
  model_table         TEXT,
  view_types          TEXT[],
  primary_view_type   TEXT,
  help_i18n_html      JSONB,
  help_ja_html        TEXT,
  help_ja_text        TEXT,
  help_en_html        TEXT,
  help_en_text        TEXT,
  view_mode           TEXT,
  context             TEXT,
  domain              TEXT
);

-- 2) insert: MODELS  (odoo_fdw.ir_model → public.ir_model_src)
--    * ir_model.name は通常プレーン文字列なので en_US に入れる
INSERT INTO public.ir_model_src
  (model, model_table, label_en_us, label_ja_jp, label_i18n, notes, created_at, updated_at)
SELECT
  m.model,
  replace(m.model, '.', '_')                                   AS model_table,
  NULLIF(m.name::text,'')                                      AS label_en_us,
  NULL::text                                                   AS label_ja_jp,      -- 翻訳テーブル未使用のためNULL
  jsonb_build_object('en_US', NULLIF(m.name::text,''))         AS label_i18n,
  NULL::text                                                   AS notes,
  m.create_date,
  m.write_date
FROM odoo_fdw.ir_model m;

-- 3) insert: FIELDS  (odoo_fdw.ir_model_fields → public.ir_field_src)
--    * field_description / help が JSON文字列・JSONオブジェクト・プレーン文字列の全パターンに対応
INSERT INTO public.ir_field_src
  (model, model_table, field_name, ttype, label_en_us, label_ja_jp, label_i18n,
   code_status, notes, origin, show_invisible, pk_columns, is_pk)
SELECT
  m.model,
  replace(m.model, '.', '_')                     AS model_table,
  f.name                                         AS field_name,
  f.ttype                                        AS ttype,

  -- ---- label_en_us 抽出 ----
  COALESCE(
    /* JSON文字列の中に {..} が入っている場合 → その中の en_US */
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN ((btrim(f.field_description::text,'"'))::jsonb ->> 'en_US')
    END,
    /* JSONオブジェクトならそのまま */
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN f.field_description::jsonb ->> 'en_US'
    END,
    /* プレーン文字列 */
    NULLIF(f.field_description::text,'')
  ) AS label_en_us,

  -- ---- label_ja_jp 抽出 ----
  COALESCE(
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN ((btrim(f.field_description::text,'"'))::jsonb ->> 'ja_JP')
    END,
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN f.field_description::jsonb ->> 'ja_JP'
    END,
    NULL
  ) AS label_ja_jp,

  -- ---- label_i18n 正規化 ----
  COALESCE(
    /* JSONオブジェクトはそのまま */
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN jsonb_strip_nulls(f.field_description::jsonb)
    END,
    /* JSON文字列の中に {..} が入っている場合 → 中身をjsonb化 */
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN jsonb_strip_nulls((btrim(f.field_description::text,'"'))::jsonb)
      WHEN NULLIF(f.field_description::text,'') IS NOT NULL
        THEN jsonb_build_object('en_US', f.field_description::text)
      ELSE '{}'::jsonb
    END
  ) AS label_i18n,

  'generated'::text AS code_status,

  -- ---- notes は help から（まず生文字列、なければJP/ENのどちらか）----
  COALESCE(
    CASE
      WHEN pg_typeof(f.help)::text IN ('json','jsonb') AND jsonb_typeof(f.help::jsonb)='string'
        THEN btrim(f.help::text,'"')
      WHEN pg_typeof(f.help)::text IN ('json','jsonb') AND jsonb_typeof(f.help::jsonb)='object'
        THEN COALESCE(f.help::jsonb ->> 'en_US', f.help::jsonb ->> 'ja_JP')
      ELSE f.help::text
    END,
    NULL
  ) AS notes,

  CASE f.state WHEN 'manual' THEN 'studio' ELSE 'code' END AS origin,
  'invisible'::text                                        AS show_invisible,
  ARRAY['id']::text[]                                      AS pk_columns,   -- Odooは原則 id がPK
  (f.name = 'id')                                          AS is_pk
FROM odoo_fdw.ir_model m
JOIN odoo_fdw.ir_model_fields f ON f.model_id = m.id;

-- 4) insert: VIEWS (ACTION-centric)  → public.ir_view_src
WITH
params AS (
  -- ★ 全モデルなら NULL。特定モデルだけなら 'res.partner' 等に。
  SELECT NULL::text AS target_model
),

acts AS (
  SELECT
    a.id                         AS action_id,
    a.name                       AS action_name,
    a.res_model                  AS model_tech,
    replace(a.res_model,'.','_') AS model_table,
    a.view_mode,
    a.context,
    a.domain,
    a.help                       AS help_raw,
    a.view_id                    AS main_view_id
  FROM odoo_fdw.ir_act_window a, params p
  WHERE p.target_model IS NULL OR a.res_model = p.target_model
),

act_views_raw AS (
  -- 直付けビュー（sequence=0相当）
  SELECT a.action_id, 0::int AS sequence, v.type AS view_type
  FROM acts a
  JOIN odoo_fdw.ir_ui_view v ON v.id = a.main_view_id
  UNION ALL
  -- 中間テーブルから
  SELECT av.act_window_id AS action_id, av.sequence, v.type
  FROM odoo_fdw.ir_act_window_view av
  JOIN odoo_fdw.ir_ui_view v ON v.id = av.view_id
),

act_views_agg AS (
  SELECT
    r.action_id,
    array_agg(DISTINCT r.view_type ORDER BY r.view_type) AS view_types,
    (ARRAY_AGG(r.view_type ORDER BY r.sequence, r.view_type))[1] AS primary_view_type
  FROM act_views_raw r
  GROUP BY r.action_id
),

act_viewmode_decl AS (
  SELECT
    a.action_id,
    ARRAY(
      SELECT DISTINCT trim(x)
      FROM unnest(string_to_array(COALESCE(a.view_mode,''), ',')) t(x)
      WHERE trim(x) <> ''
      ORDER BY trim(x)
    ) AS view_mode_types
  FROM acts a
),

help_norm AS (
  SELECT
    a.action_id,
    CASE
      WHEN pg_typeof(a.help_raw)::text IN ('json','jsonb')
           AND jsonb_typeof(a.help_raw::jsonb)='object'
        THEN a.help_raw::jsonb
      WHEN pg_typeof(a.help_raw)::text IN ('json','jsonb')
           AND jsonb_typeof(a.help_raw::jsonb)='string'
        THEN jsonb_build_object('en_US', btrim((a.help_raw::jsonb)::text,'"'))
      WHEN a.help_raw IS NOT NULL
        THEN jsonb_build_object('en_US', a.help_raw::text)
      ELSE '{}'::jsonb
    END AS help_i18n_html,

    CASE
      WHEN pg_typeof(a.help_raw)::text IN ('json','jsonb')
           AND jsonb_typeof(a.help_raw::jsonb)='object'
        THEN (a.help_raw::jsonb)->>'ja_JP'
      ELSE NULL
    END AS help_ja_html,

    CASE
      WHEN pg_typeof(a.help_raw)::text IN ('json','jsonb')
           AND jsonb_typeof(a.help_raw::jsonb)='object'
        THEN (a.help_raw::jsonb)->>'en_US'
      WHEN pg_typeof(a.help_raw)::text IN ('json','jsonb')
           AND jsonb_typeof(a.help_raw::jsonb)='string'
        THEN btrim((a.help_raw::jsonb)::text,'"')
      WHEN a.help_raw IS NOT NULL
        THEN a.help_raw::text
      ELSE NULL
    END AS help_en_html
  FROM acts a
),

help_with_plain AS (
  SELECT
    h.action_id,
    h.help_i18n_html,
    h.help_ja_html,
    h.help_en_html,
    NULLIF(regexp_replace(COALESCE(h.help_ja_html,''), '<[^>]*>', '', 'g'), '') AS help_ja_text,
    NULLIF(regexp_replace(COALESCE(h.help_en_html,''), '<[^>]*>', '', 'g'), '') AS help_en_text
  FROM help_norm h
),

act_xmlid AS (
  SELECT imd.res_id AS action_id, (imd.module || '.' || imd.name) AS action_xmlid
  FROM odoo_fdw.ir_model_data imd
  WHERE imd.model='ir.actions.act_window'
),

model_label AS (
  SELECT m.model AS model_tech, m.name AS model_label
  FROM odoo_fdw.ir_model m
)

INSERT INTO public.ir_view_src
  (action_xmlid, action_id, action_name, model_label, model_tech, model_table,
   view_types, primary_view_type, help_i18n_html, help_ja_html, help_ja_text, help_en_html, help_en_text,
   view_mode, context, domain)
SELECT
  ax.action_xmlid,
  a.action_id,
  a.action_name,
  ml.model_label,
  a.model_tech,
  a.model_table,
  COALESCE(av.view_types, avd.view_mode_types, ARRAY[]::text[]),
  av.primary_view_type,
  hw.help_i18n_html,
  hw.help_ja_html,
  hw.help_ja_text,
  hw.help_en_html,
  hw.help_en_text,
  a.view_mode,
  a.context,
  a.domain
FROM acts a
LEFT JOIN act_xmlid         ax  ON ax.action_id  = a.action_id
LEFT JOIN act_views_agg     av  ON av.action_id  = a.action_id
LEFT JOIN act_viewmode_decl avd ON avd.action_id = a.action_id
LEFT JOIN help_with_plain   hw  ON hw.action_id  = a.action_id
LEFT JOIN model_label       ml  ON ml.model_tech = a.model_tech
ORDER BY a.model_tech, a.action_name, a.action_id;

-- 5) quick check
SELECT
  (SELECT count(*) FROM public.ir_model_src) AS models,
  (SELECT count(*) FROM public.ir_field_src) AS fields,
  (SELECT count(*) FROM public.ir_view_src)  AS views;

Odooメタ情報Portalにインポート

-- =========================================
-- Rebuild under PUBLIC with "ir_" prefix
-- Creates: public.ir_model_src / ir_field_src / ir_view_src
-- Sources: odoo_fdw.ir_model / ir_model_fields / ir_ui_view (+ refs)
-- =========================================

-- 0) clean (drop and recreate)
DROP TABLE IF EXISTS public.ir_view_src;
DROP TABLE IF EXISTS public.ir_field_src;
DROP TABLE IF EXISTS public.ir_model_src;

-- 1) create tables (public.* so they appear under "Tables")
CREATE TABLE public.ir_model_src (
  model        TEXT NOT NULL,
  model_table  TEXT NOT NULL,
  label_en_us  TEXT,
  label_ja_jp  TEXT,
  label_i18n   JSONB,
  notes        TEXT,
  created_at   TIMESTAMPTZ,
  updated_at   TIMESTAMPTZ
);

CREATE TABLE public.ir_field_src (
  model          TEXT NOT NULL,
  model_table    TEXT NOT NULL,
  field_name     TEXT NOT NULL,
  ttype          TEXT NOT NULL,
  label_en_us    TEXT,
  label_ja_jp    TEXT,
  label_i18n     JSONB,
  code_status    TEXT,
  notes          TEXT,
  origin         TEXT,
  show_invisible TEXT,
  pk_columns     TEXT[],
  is_pk          BOOLEAN
);

CREATE TABLE public.ir_view_src (
  xmlid           TEXT,
  view_id         BIGINT,
  view_name       TEXT,
  model_table     TEXT,
  view_type       TEXT,
  view_type_ja    TEXT,
  is_root         BOOLEAN,
  parent_xmlid    TEXT,
  parent_view_id  BIGINT,
  priority_num    INT,
  action_count    INT,
  action_xmlids   TEXT[],
  create_date     TIMESTAMPTZ,
  write_date      TIMESTAMPTZ
);

-- 2) insert: models (Odoo ir_model → public.ir_model_src)
INSERT INTO public.ir_model_src
  (model, model_table, label_en_us, label_ja_jp, label_i18n, notes, created_at, updated_at)
SELECT
  m.model,
  replace(m.model, '.', '_')                                         AS model_table,
  NULLIF(m.name::text,'')                                            AS label_en_us,
  NULL::text                                                         AS label_ja_jp,  -- ir_translation 未参照のためNULL
  jsonb_build_object('en_US', NULLIF(m.name::text,''))               AS label_i18n,
  NULL::text                                                         AS notes,
  m.create_date,
  m.write_date
FROM odoo_fdw.ir_model m;

-- 3) insert: fields (Odoo ir_model_fields → public.ir_field_src)
--    field_description/help が JSON 文字列になっている場合もパースして JP/EN を展開
INSERT INTO public.ir_field_src
  (model, model_table, field_name, ttype, label_en_us, label_ja_jp, label_i18n,
   code_status, notes, origin, show_invisible, pk_columns, is_pk)
SELECT
  m.model,
  replace(m.model, '.', '_')                     AS model_table,
  f.name                                         AS field_name,
  f.ttype                                        AS ttype,

  -- label_en_us 抽出（入れ子JSON文字列 / JSONオブジェクト / 素文字列）
  COALESCE(
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN ((btrim(f.field_description::text,'"'))::jsonb ->> 'en_US')
    END,
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN f.field_description::jsonb ->> 'en_US'
    END,
    NULLIF(f.field_description::text,'')
  ) AS label_en_us,

  -- label_ja_jp 抽出
  COALESCE(
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN ((btrim(f.field_description::text,'"'))::jsonb ->> 'ja_JP')
    END,
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN f.field_description::jsonb ->> 'ja_JP'
    END,
    NULL
  ) AS label_ja_jp,

  -- label_i18n 正規化（オブジェクトはそのまま、文字列は en_US 包み、空は {}）
  COALESCE(
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='object'
        THEN jsonb_strip_nulls(f.field_description::jsonb)
    END,
    CASE
      WHEN pg_typeof(f.field_description)::text IN ('json','jsonb')
           AND jsonb_typeof(f.field_description::jsonb)='string'
           AND (f.field_description::text ~ '^\s*".*\{')
        THEN jsonb_strip_nulls((btrim(f.field_description::text,'"'))::jsonb)
      WHEN NULLIF(f.field_description::text,'') IS NOT NULL
        THEN jsonb_build_object('en_US', f.field_description::text)
      ELSE '{}'::jsonb
    END
  ) AS label_i18n,

  'generated'::text AS code_status,

  -- notes は help から（まず生文字列、なければJP/ENのどちらか）
  COALESCE(
    CASE
      WHEN pg_typeof(f.help)::text IN ('json','jsonb') AND jsonb_typeof(f.help::jsonb)='string'
        THEN btrim(f.help::text,'"')
      WHEN pg_typeof(f.help)::text IN ('json','jsonb') AND jsonb_typeof(f.help::jsonb)='object'
        THEN COALESCE(f.help::jsonb ->> 'en_US', f.help::jsonb ->> 'ja_JP')
      ELSE f.help::text
    END,
    NULL
  ) AS notes,

  CASE f.state WHEN 'manual' THEN 'studio' ELSE 'code' END AS origin,
  'invisible'::text                                        AS show_invisible,
  ARRAY['id']::text[]                                      AS pk_columns,
  (f.name = 'id')                                          AS is_pk
FROM odoo_fdw.ir_model m
JOIN odoo_fdw.ir_model_fields f ON f.model_id = m.id;

-- 4) insert: views (Odoo ir_ui_view → public.ir_view_src)
WITH base AS (
  SELECT
    v.id                       AS view_id,
    v.name                     AS view_name,
    replace(v.model,'.','_')   AS model_table,
    v.type                     AS view_type,
    v.inherit_id               AS parent_view_id,
    v.priority                 AS priority_num,
    v.create_date,
    v.write_date
  FROM odoo_fdw.ir_ui_view v
),
view_xmlid AS (
  SELECT imd.res_id AS view_id, (imd.module || '.' || imd.name) AS xmlid
  FROM odoo_fdw.ir_model_data imd WHERE imd.model='ir.ui.view'
),
parent_xmlid AS (
  SELECT imd.res_id AS parent_view_id, (imd.module || '.' || imd.name) AS parent_xmlid
  FROM odoo_fdw.ir_model_data imd WHERE imd.model='ir.ui.view'
),
act_view_map AS (
  SELECT av.view_id, av.act_window_id AS action_id
  FROM odoo_fdw.ir_act_window_view av
),
act_main_map AS (
  SELECT a.view_id, a.id AS action_id
  FROM odoo_fdw.ir_act_window a
  WHERE a.view_id IS NOT NULL
),
act_refs AS (
  SELECT view_id, action_id FROM act_view_map
  UNION ALL
  SELECT view_id, action_id FROM act_main_map
),
act_agg AS (
  SELECT
    r.view_id,
    COUNT(DISTINCT r.action_id) AS action_count,
    COALESCE(
      array_agg(DISTINCT (imd.module || '.' || imd.name)) FILTER (WHERE imd.id IS NOT NULL),
      ARRAY[]::text[]
    ) AS action_xmlids
  FROM act_refs r
  LEFT JOIN odoo_fdw.ir_model_data imd
    ON imd.model='ir.actions.act_window' AND imd.res_id=r.action_id
  GROUP BY r.view_id
)
INSERT INTO public.ir_view_src
  (xmlid, view_id, view_name, model_table, view_type, view_type_ja, is_root,
   parent_xmlid, parent_view_id, priority_num, action_count, action_xmlids, create_date, write_date)
SELECT
  vx.xmlid,
  b.view_id,
  b.view_name,
  b.model_table,
  b.view_type,
  CASE b.view_type
    WHEN 'form'     THEN 'フォーム'
    WHEN 'tree'     THEN 'リスト'
    WHEN 'search'   THEN '検索'
    WHEN 'kanban'   THEN 'カンバン'
    WHEN 'calendar' THEN 'カレンダー'
    WHEN 'graph'    THEN 'グラフ'
    WHEN 'pivot'    THEN 'ピボット'
    WHEN 'activity' THEN 'アクティビティ'
    WHEN 'gantt'    THEN 'ガント'
    ELSE b.view_type
  END,
  (b.parent_view_id IS NULL),
  px.parent_xmlid,
  b.parent_view_id,
  b.priority_num,
  COALESCE(aa.action_count,0),
  COALESCE(aa.action_xmlids, ARRAY[]::text[]),
  b.create_date,
  b.write_date
FROM base b
LEFT JOIN view_xmlid  vx ON vx.view_id=b.view_id
LEFT JOIN parent_xmlid px ON px.parent_view_id=b.parent_view_id
LEFT JOIN act_agg     aa ON aa.view_id=b.view_id;

-- 5) quick check
SELECT
  (SELECT count(*) FROM public.ir_model_src) AS models,
  (SELECT count(*) FROM public.ir_field_src) AS fields,
  (SELECT count(*) FROM public.ir_view_src)  AS views;