-- =========================================================
-- Portal schema (re)build - action-centric minimal set
--   - Keeps ir_*_src as read-only reference
--   - Creates: portal_model / portal_fields / portal_view_common
--   - Adds helper functions & triggers
-- =========================================================

BEGIN;

-- 0) extensions --------------------------------------------------------------
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 1) common trigger: touch updated_at ---------------------------------------
CREATE OR REPLACE FUNCTION public.tg_touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END
$$;

-- 2) JSON safe parser (TEXT -> JSONB; fallback to JSON string) --------------
CREATE OR REPLACE FUNCTION public.try_parse_jsonb(p_text text)
RETURNS jsonb
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
  IF p_text IS NULL OR btrim(p_text) = '' THEN
    RETURN NULL;
  END IF;
  RETURN p_text::jsonb;      -- valid JSON -> as-is
EXCEPTION WHEN others THEN
  RETURN to_jsonb(p_text);   -- invalid JSON -> keep as JSON string value
END
$$;

-- 3) core: portal_model ------------------------------------------------------
DROP TABLE IF EXISTS public.portal_model CASCADE;

CREATE TABLE public.portal_model (
  id           bigserial PRIMARY KEY,
  model        text        NOT NULL UNIQUE,  -- e.g. 'sale.order'
  model_table  text        NOT NULL,         -- e.g. 'sale_order'
  label_i18n   jsonb       NOT NULL DEFAULT '{}'::jsonb,
  notes        text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_portal_model_model_table ON public.portal_model(model_table);

CREATE TRIGGER trg_touch_portal_model
BEFORE UPDATE ON public.portal_model
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

-- 4) fields: portal_fields ---------------------------------------------------
DROP TABLE IF EXISTS public.portal_fields CASCADE;

CREATE TABLE public.portal_fields (
  id                 bigserial PRIMARY KEY,
  model_id           bigint      REFERENCES public.portal_model(id) ON DELETE CASCADE,
  model              text        NOT NULL,
  model_table        text        NOT NULL,
  field_name         text        NOT NULL,              -- technical name (EN)
  ttype              text        NOT NULL,              -- char, integer, json, many2one, ...
  label_i18n         jsonb       NOT NULL DEFAULT '{}'::jsonb,
  code_status        text,
  notes              text,
  origin             text        NOT NULL DEFAULT 'portal',
  created_at         timestamptz NOT NULL DEFAULT now(),
  updated_at         timestamptz NOT NULL DEFAULT now(),

  -- Japanese mirror columns (kept for compatibility/UX)
  "モデル技術名"       text        NOT NULL DEFAULT '',
  "モデル物理名"       text        NOT NULL DEFAULT '',
  "フィールド技術名"   text        NOT NULL DEFAULT '',
  "データ型"           text        NOT NULL DEFAULT '文字列',

  CONSTRAINT uq_portal_fields UNIQUE (model, field_name)
);

-- Japanese datatype label check (broad allowlist)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint
    WHERE conname = 'ck_ttype_jp'
      AND conrelid = 'public.portal_fields'::regclass
  ) THEN
    ALTER TABLE public.portal_fields
      ADD CONSTRAINT ck_ttype_jp CHECK (
        "データ型" IN (
          '文字列','テキスト','長文',
          '整数','数値','小数','実数',
          '真偽','ブール','論理値',
          '日付','日時','タイムスタンプ',
          'JSON','構造化',
          '参照','外部キー',
          '複数参照','1対多','多対多'
        )
      );
  END IF;
END$$;

CREATE INDEX IF NOT EXISTS idx_portal_fields_model ON public.portal_fields(model);
CREATE INDEX IF NOT EXISTS idx_portal_fields_model_id ON public.portal_fields(model_id);

CREATE TRIGGER trg_touch_portal_fields
BEFORE UPDATE ON public.portal_fields
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

-- (optional helper) choose JP datatype label from ttype (aligned with ck_ttype_jp)
CREATE OR REPLACE FUNCTION public._pick_jp_datatype_label(p_ttype text)
RETURNS text
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  allowed text[];
  cand    text[];
  v       text;
BEGIN
  SELECT array_agg(m[1]) INTO allowed
  FROM pg_constraint c
  CROSS JOIN LATERAL regexp_matches(pg_get_constraintdef(c.oid), '''([^'']+)''', 'g') AS m
  WHERE c.conname = 'ck_ttype_jp'
    AND c.conrelid = 'public.portal_fields'::regclass;

  IF allowed IS NULL OR array_length(allowed,1) IS NULL THEN
    RETURN '文字列';
  END IF;

  p_ttype := lower(coalesce(p_ttype,''));
  cand := CASE p_ttype
    WHEN 'char'      THEN ARRAY['テキスト','文字列','文字','テキスト型','文字列型']
    WHEN 'text'      THEN ARRAY['テキスト','長文','文字列']
    WHEN 'integer'   THEN ARRAY['整数','数値','整数型']
    WHEN 'float'     THEN ARRAY['小数','実数','浮動小数点']
    WHEN 'boolean'   THEN ARRAY['真偽','ブール','論理値']
    WHEN 'date'      THEN ARRAY['日付']
    WHEN 'datetime'  THEN ARRAY['日時','タイムスタンプ']
    WHEN 'json'      THEN ARRAY['JSON','構造化']
    WHEN 'many2one'  THEN ARRAY['参照','外部キー']
    WHEN 'one2many'  THEN ARRAY['複数参照','1対多']
    WHEN 'many2many' THEN ARRAY['多対多']
    ELSE ARRAY['テキスト','文字列']
  END;

  FOREACH v IN ARRAY cand LOOP
    IF v = ANY(allowed) THEN
      RETURN v;
    END IF;
  END LOOP;
  RETURN allowed[1];
END
$$;

-- 5) action-centric view header: portal_view_common -------------------------
DROP TABLE IF EXISTS public.portal_view_common CASCADE;

CREATE TABLE public.portal_view_common (
  id                   bigserial PRIMARY KEY,

  -- Action-centric keys (from ir_view_src action-centric)
  action_xmlid         text      NOT NULL,            -- business key (UNIQUE)
  action_id            bigint,
  action_name          text,

  -- Model info (normalized; 'model' added for cross-table filtering)
  model                text      NOT NULL,            -- = ir_view_src.model_tech
  model_tech           text,
  model_table          text,
  model_label          text,

  -- View types (keep as array)
  view_types           text[]    NOT NULL DEFAULT '{}'::text[],
  primary_view_type    text,

  -- Help (i18n HTML and plain)
  help_i18n_html       jsonb     NOT NULL DEFAULT '{}'::jsonb,
  help_ja_html         text,
  help_ja_text         text,
  help_en_html         text,
  help_en_text         text,

  -- Action attributes
  view_mode            text,
  context              jsonb,
  domain               jsonb,

  -- Common UI defaults (left untouched by import)
  display_fields       jsonb     NOT NULL DEFAULT '[]'::jsonb,
  sort_field           text,
  sort_dir             text      NOT NULL DEFAULT 'asc',
  default_group_by     text,
  default_filters      jsonb     NOT NULL DEFAULT '{}'::jsonb,

  -- AI / creation behaviors (initial defaults only)
  ai_purpose           text,
  ai_purpose_i18n      jsonb     NOT NULL DEFAULT '{}'::jsonb,
  creation_mode        text      NOT NULL DEFAULT 'open_new_page',
  default_values       text,
  allow_duplicate      boolean   NOT NULL DEFAULT false,
  enable_archive       boolean   NOT NULL DEFAULT true,

  -- audit
  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  -- constraints
  CONSTRAINT uq_pvc_action_xmlid UNIQUE (action_xmlid),
  CONSTRAINT ck_pvc_sort_dir CHECK (sort_dir IN ('asc','desc')),
  CONSTRAINT ck_pvc_creation_mode CHECK (creation_mode IN ('open_new_page','inline_create','modal_create','quick_create')),
  CONSTRAINT ck_pvc_primary_in_viewtypes CHECK (
    primary_view_type IS NULL OR primary_view_type = ANY(view_types)
  )
);

-- indexes for typical lookups
CREATE INDEX IF NOT EXISTS idx_pvc_model ON public.portal_view_common(model);
CREATE INDEX IF NOT EXISTS idx_pvc_model_table ON public.portal_view_common(model_table);
CREATE INDEX IF NOT EXISTS idx_pvc_action_xmlid ON public.portal_view_common(action_xmlid);
CREATE INDEX IF NOT EXISTS idx_pvc_view_types_gin ON public.portal_view_common USING GIN(view_types);

CREATE TRIGGER trg_touch_portal_view_common
BEFORE UPDATE ON public.portal_view_common
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

COMMIT;

-- =========================================================
-- Portal: smart_button / tab / menu  (action-centric)
--  - 参照元: portal_view_common (common_id / action_xmlid)
--  - 各テーブルに model 列を追加（横断検索用）
-- =========================================================
BEGIN;

-- 共通：updated_atトリガ関数（未作成なら定義）
CREATE OR REPLACE FUNCTION public.tg_touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END
$$;

-- =========================================================
-- 1) TAB
--    旧: portal_tab_portal を置き換え。ビュー種別依存を避けて common_id に紐付け
-- =========================================================
DROP TABLE IF EXISTS public.portal_tab CASCADE;

CREATE TABLE public.portal_tab (
  id                  bigserial PRIMARY KEY,
  common_id           bigint NOT NULL REFERENCES public.portal_view_common(id) ON DELETE CASCADE,
  model               text   NOT NULL,              -- ★ モデル名（例: 'sale.order'）

  tab_key             text   NOT NULL,
  page_idx            integer NOT NULL DEFAULT 10,

  -- ラベル
  label_i18n          jsonb  NOT NULL DEFAULT '{}'::jsonb,
  tab_label_ja        text,
  tab_label_en        text,

  -- 子モデル（サブリスト等）
  child_model         text,
  child_link_field    text,

  -- 生成・管理メタ
  origin              text,
  module              text,
  is_codegen_target   boolean DEFAULT false,
  notes               text,
  github_url          text,

  -- サブビュー参照や表示ポリシー
  view_mode           text,
  tree_view_xmlid     text,
  form_view_xmlid     text,
  subview_policy_tree text,
  subview_policy_form text,

  -- domain/context（そのまま文字列で保持）
  use_domain          boolean,
  domain_raw          text,
  use_context         boolean,
  context_raw         text,

  -- 編集挙動
  inline_edit         boolean,
  allow_create_rows   boolean,
  allow_delete_rows   boolean,

  options_raw         jsonb,

  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_portal_tab UNIQUE (common_id, tab_key)
);

CREATE INDEX IF NOT EXISTS idx_portal_tab_common ON public.portal_tab(common_id);
CREATE INDEX IF NOT EXISTS idx_portal_tab_model  ON public.portal_tab(model);
CREATE INDEX IF NOT EXISTS idx_portal_tab_page   ON public.portal_tab(page_idx);

CREATE TRIGGER trg_touch_portal_tab
BEFORE UPDATE ON public.portal_tab
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

-- =========================================================
-- 2) SMART BUTTON
--    旧: portal_smart_button を再作成。common_id + button_key で一意に
-- =========================================================
DROP TABLE IF EXISTS public.portal_smart_button CASCADE;

CREATE TABLE public.portal_smart_button (
  id                   bigserial PRIMARY KEY,
  common_id            bigint NOT NULL REFERENCES public.portal_view_common(id) ON DELETE CASCADE,
  model                text   NOT NULL,             -- ★ モデル名

  button_key           text   NOT NULL,
  label_i18n           jsonb  NOT NULL DEFAULT '{}'::jsonb,

  target_model         text,
  origin               text,
  action_type          text,                         -- e.g. 'ir.actions.act_window' / 'url' / 'other'
  action_ref           text,                         -- 参照名やURL等
  target               text,                         -- 'current' / 'new' など
  show_count           boolean,
  sequence             integer,

  notes                text,
  notes_i18n           jsonb,
  view_xmlid           text,
  cached_ui_view_id    bigint,
  dest_view_url        text,

  is_codegen_target    boolean,
  show_in_inspection   boolean,
  groups               jsonb,                        -- アクセス制御
  badge_count_expr     text,
  domain               jsonb,
  context              jsonb,

  created_at           timestamptz NOT NULL DEFAULT now(),
  updated_at           timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_portal_smart_button UNIQUE (common_id, button_key)
);

CREATE INDEX IF NOT EXISTS idx_psb_common   ON public.portal_smart_button(common_id);
CREATE INDEX IF NOT EXISTS idx_psb_model    ON public.portal_smart_button(model);
CREATE INDEX IF NOT EXISTS idx_psb_sequence ON public.portal_smart_button(sequence);

CREATE TRIGGER trg_touch_portal_smart_button
BEFORE UPDATE ON public.portal_smart_button
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

-- =========================================================
-- 3) MENU
--    新規: portal_menu（Odooメニューの取り込み/手動作成双方に対応）
--    - action_xmlid は portal_view_common(action_xmlid) に参照可能（UNIQUEなのでFK化OK）
--    - common_id でも参照可（両方持てる設計）
-- =========================================================
DROP TABLE IF EXISTS public.portal_menu CASCADE;

CREATE TABLE public.portal_menu (
  id                    bigserial PRIMARY KEY,

  -- 階層
  parent_id             bigint REFERENCES public.portal_menu(id) ON DELETE SET NULL,
  parent_xmlid          text,

  -- 表示
  name_i18n             jsonb  NOT NULL DEFAULT '{}'::jsonb,
  name_ja               text,
  name_en               text,
  sequence              integer,

  -- 紐付け（action-centric）
  action_xmlid          text,
  common_id             bigint REFERENCES public.portal_view_common(id) ON DELETE SET NULL,

  -- ★ モデル名
  model                 text   NOT NULL,

  -- 追加属性
  menu_xmlid            text UNIQUE,                 -- Odoo側xmlid（あれば）
  web_path              text,
  icon                  text,
  is_category           boolean NOT NULL DEFAULT false,
  is_hidden             boolean NOT NULL DEFAULT false,

  created_at            timestamptz NOT NULL DEFAULT now(),
  updated_at            timestamptz NOT NULL DEFAULT now(),

  -- action_xmlid をユニークキーに参照（存在すれば）
  CONSTRAINT fk_portal_menu_action_xmlid
    FOREIGN KEY (action_xmlid) REFERENCES public.portal_view_common(action_xmlid) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_menu_parent      ON public.portal_menu(parent_id);
CREATE INDEX IF NOT EXISTS idx_menu_model       ON public.portal_menu(model);
CREATE INDEX IF NOT EXISTS idx_menu_common      ON public.portal_menu(common_id);
CREATE INDEX IF NOT EXISTS idx_menu_actionxmlid ON public.portal_menu(action_xmlid);

CREATE TRIGGER trg_touch_portal_menu
BEFORE UPDATE ON public.portal_menu
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

COMMIT;

portal_viewの作成

BEGIN;

-- 念のため updated_at トリガ関数を用意（既存なら置換）
CREATE OR REPLACE FUNCTION public.tg_touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END
$$;

-- 既存があれば入れ替え
DROP TABLE IF EXISTS public.portal_view CASCADE;

CREATE TABLE public.portal_view (
  id                         bigserial PRIMARY KEY,
  common_id                  bigint NOT NULL REFERENCES public.portal_view_common(id) ON DELETE CASCADE,
  view_type                  text   NOT NULL,
  -- 共通（全種）
  view_name                  text,
  model                      text,
  priority_num               integer,
  enabled                    boolean NOT NULL DEFAULT true,
  is_primary                 boolean NOT NULL DEFAULT false,

  -- === Form ===
  form_show_header           boolean,
  form_show_footer           boolean,

  -- === Kanban ===
  kanban_default_group_by    text,
  kanban_quick_create        boolean,
  kanban_draggable_field     text,

  -- === List ===
  list_inline_edit           boolean,
  list_export_button         boolean,
  list_page_size             integer,

  -- === Calendar（フィールド名を格納するため text に変更）===
  calendar_start_field       text,
  calendar_end_field         text,
  calendar_color_field       text,
  calendar_default_view      text,

  -- === Search ===
  search_fields              jsonb,
  search_filters             jsonb,
  search_group_by_filters    jsonb,

  -- === Graph（暫定ドラフト） ===
  graph_type                 text,
  graph_measure_fields       jsonb,
  graph_row_group_by         text,
  graph_col_group_by         text,
  graph_stacked              boolean,

  -- === Pivot（暫定ドラフト） ===
  pivot_measures             jsonb,
  pivot_rows                 jsonb,
  pivot_cols                 jsonb,
  pivot_show_totals          boolean,

  -- === Dashboard（暫定ドラフト） ===
  dashboard_layout           jsonb,
  dashboard_widgets          jsonb,
  dashboard_refresh_secs     integer,

  -- === Tree（暫定ドラフト） ===
  tree_parent_field          text,
  tree_expand_all            boolean,

  -- === Map（暫定ドラフト） ===
  map_lat_field              text,
  map_lng_field              text,
  map_address_field          text,
  map_color_field            text,
  map_cluster                boolean,
  map_default_zoom           integer,

  -- 監査
  created_at                 timestamptz NOT NULL DEFAULT now(),
  updated_at                 timestamptz NOT NULL DEFAULT now(),

  CONSTRAINT uq_pv_common_viewtype UNIQUE (common_id, view_type),
  CONSTRAINT ck_pv_view_type CHECK (
    view_type IN (
      'form','kanban','list','calendar','search','graph','pivot','dashboard','tree','map'
    )
  ),
  CONSTRAINT ck_pv_calendar_default_view CHECK (
    calendar_default_view IS NULL OR calendar_default_view IN ('month','week','day')
  )
);

CREATE INDEX IF NOT EXISTS idx_pv_common_id ON public.portal_view(common_id);
CREATE INDEX IF NOT EXISTS idx_pv_view_type ON public.portal_view(view_type);

CREATE TRIGGER trg_touch_portal_view
BEFORE UPDATE ON public.portal_view
FOR EACH ROW EXECUTE FUNCTION public.tg_touch_updated_at();

-- グループ内の is_primary を単一化するトリガ
CREATE OR REPLACE FUNCTION public.tg_enforce_single_primary_per_common()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.is_primary THEN
    UPDATE public.portal_view
      SET is_primary = false, updated_at = now()
      WHERE common_id = NEW.common_id
        AND id <> NEW.id
        AND is_primary = true;
  END IF;
  RETURN NEW;
END
$$;

DROP TRIGGER IF EXISTS trg_single_primary_per_common ON public.portal_view;
CREATE TRIGGER trg_single_primary_per_common
BEFORE INSERT OR UPDATE OF is_primary, common_id ON public.portal_view
FOR EACH ROW EXECUTE FUNCTION public.tg_enforce_single_primary_per_common();

COMMIT;

統合ポイント（この順で rebuild に組み込み）

portal_view_common を先に作成（すでに完了）
portal_view を作成（ご提示DDLでOK）
model自動同期トリガ（任意だが推奨）
- portal_view.model を portal_view_common.model と自動一致させます（挿入・common_id変更時に同期）。
- こうしておくと、portal_view 側も “model持ち” の設計に自然にそろい、横断検索が簡単になります。
初期ブートストラップ（1行/ビュー種）
- portal_view_common.view_types[] から (common_id, view_type) の骨組み行を一括生成。
- 2回目以降も ON CONFLICT で冪等に増分作成できます。

3) model自動同期トリガ（追記推奨）

-- portal_view.common_id を基に model を自動で埋める/同期する
CREATE OR REPLACE FUNCTION public.tg_sync_portal_view_model()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_model text;
BEGIN
  SELECT model INTO v_model
  FROM public.portal_view_common
  WHERE id = NEW.common_id;

  IF v_model IS NULL THEN
    -- 参照先が無ければそのまま（FKが守る）
    RETURN NEW;
  END IF;

  -- model が未設定 or 異なる場合は共通側に合わせる
  IF NEW.model IS DISTINCT FROM v_model THEN
    NEW.model := v_model;
  END IF;

  RETURN NEW;
END
$$;

DROP TRIGGER IF EXISTS trg_sync_view_model_ins ON public.portal_view;
CREATE TRIGGER trg_sync_view_model_ins
BEFORE INSERT OR UPDATE OF common_id ON public.portal_view
FOR EACH ROW EXECUTE FUNCTION public.tg_sync_portal_view_model();

４) 初期ブートストラップ（骨組み行の生成）

— view_types[] を展開して (common_id, view_type) の行を作成
INSERT INTO public.portal_view (common_id, view_type, model, enabled, is_primary, priority_num)
SELECT
pvc.id,
vt.view_type,
pvc.model,
true,
(vt.view_type = pvc.primary_view_type),
NULL — priorityは後で必要に応じて
FROM public.portal_view_common pvc
CROSS JOIN LATERAL unnest(pvc.view_types) AS vt(view_type)
ON CONFLICT (common_id, view_type) DO NOTHING;

Re:テーブル作成

統合ポイント（この順で rebuild に組み込み）

Comments

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル