CREATE EXTENSION IF NOT EXISTS "postgres_fdw";
CREATE SERVER zy
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '127.0.0.1', port '5432', dbname 'postgres');
CREATE USER MAPPING FOR postgres
SERVER zy
OPTIONS (user 'postgres', password '123456');
CREATE FOREIGN TABLE zy_sys_config (
id SERIAL8,
owner text NOT NULL,
key text NOT NULL,
value jsonb NOT NULL
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'sys_config');
CREATE FOREIGN TABLE zy_enum (
id SERIAL8 ,
cate text NOT NULL,
kind text NOT NULL,
symbol text NOT NULL,
comment text,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'enum');
CREATE FOREIGN TABLE zy_group (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}',
parent_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'group');
CREATE FOREIGN TABLE zy_place (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}',
parent_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'place');
CREATE FOREIGN TABLE zy_place_group (
id serial ,
kind text NOT NULL,
place_id int8 ,
group_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'zy_place_group');
CREATE FOREIGN TABLE zy_user (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'user');
CREATE FOREIGN TABLE zy_user_fprint (
id serial ,
feature text NOT NULL,
user_id int8 NOT NULL
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'user_fprint');
CREATE FOREIGN TABLE zy_user_group (
id serial ,
kind text NOT NULL,
user_id int8 ,
group_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'user_group');
CREATE FOREIGN TABLE zy_user_place (
id serial ,
kind text NOT NULL,
user_id int8 ,
place_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'user_place');
CREATE FOREIGN TABLE zy_account (
id int8 ,
active int DEFAULT 1,
username text NOT NULL,
password text NOT NULL DEFAULT '@Fsy123456',
role text NOT NULL,
avatar text,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'account');
CREATE FOREIGN TABLE zy_account_privilege (
id int8 NOT NULL ,
oid int8 NOT NULL
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'account_privilege');
CREATE FOREIGN TABLE zy_account_src (
id int8 NOT NULL ,
src text NOT NULL
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'account_src');
CREATE FOREIGN TABLE zy_asset (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'asset');
CREATE FOREIGN TABLE zy_asset_group (
id serial ,
kind text NOT NULL,
asset_id int8 ,
group_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'asset_group');
CREATE FOREIGN TABLE zy_asset_place (
id serial ,
kind text NOT NULL,
asset_id int8 ,
place_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'asset_place');
CREATE FOREIGN TABLE zy_asset_user (
id serial ,
kind text NOT NULL,
asset_id int8 ,
user_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'asset_user');
CREATE FOREIGN TABLE zy_asset_asset (
id serial ,
kind text NOT NULL,
asset_aid int8 ,
asset_bid int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'asset_asset');
CREATE FOREIGN TABLE zy_device (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text NOT NULL,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device');
CREATE FOREIGN TABLE zy_device_version (
id serial8 ,
kind text NOT NULL,
module text NOT NULL,
version text NOT NULL,
applicant_id int8 ,
remark text,
data jsonb DEFAULT '{}',
created_at timestamp ,
updated_at timestamp
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_version');
CREATE FOREIGN TABLE zy_device_group (
id serial ,
kind text NOT NULL,
device_id int8 ,
group_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_group');
CREATE FOREIGN TABLE zy_device_place (
id serial ,
kind text NOT NULL,
device_id int8 ,
place_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_place');
CREATE FOREIGN TABLE zy_device_user (
id serial ,
kind text NOT NULL,
device_id int8 ,
user_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_user');
CREATE FOREIGN TABLE zy_device_asset (
id serial ,
kind text NOT NULL,
device_id int8 ,
asset_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_asset');
CREATE FOREIGN TABLE zy_device_device (
id serial ,
kind text NOT NULL,
device_aid int8 ,
device_bid int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_device');
CREATE FOREIGN TABLE zy_area (
id int8 ,
active int DEFAULT 1,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'area');
CREATE FOREIGN TABLE zy_form (
id int8 ,
kind text NOT NULL,
sn text,
name text DEFAULT '',
remark text DEFAULT '',
status text NOT NULL,
data jsonb DEFAULT '{}',
created_at timestamp ,
updated_at timestamp
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'form');
CREATE FOREIGN TABLE zy_form_group (
id serial ,
kind text NOT NULL,
form_id int8 ,
group_id int8
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'form_group');
CREATE FOREIGN TABLE zy_form_item (
id int8 ,
form_id int8 ,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'form_item');
CREATE FOREIGN TABLE zy_event (
ts timestamp ,
id int8 NOT NULL ,
parent_id int8,
ori int8 NOT NULL,
kind text NOT NULL,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'event');
CREATE FOREIGN TABLE zy_alarm (
ts timestamp NOT NULL DEFAULT NOW(),
id int8 NOT NULL ,
ori int8 NOT NULL,
kind text NOT NULL,
severity int DEFAULT 0,
ack_ts timestamp,
acked boolean DEFAULT FALSE,
clear_ts timestamp,
cleared boolean DEFAULT FALSE,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'alarm');
CREATE FOREIGN TABLE zy_series (
ts timestamp NOT NULL DEFAULT NOW(),
id int8 NOT NULL ,
ori int8 NOT NULL,
kind text NOT NULL,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'series');
CREATE FOREIGN TABLE zy_status (
id int8 ,
ts timestamp NOT NULL DEFAULT NOW(),
cate text NOT NULL,
kind text NOT NULL,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'status');
CREATE FOREIGN TABLE zy_device_event (
ts timestamp NOT NULL DEFAULT NOW(),
id int8 NOT NULL ,
parent_id int8,
ori int8 NOT NULL,
kind text NOT NULL,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_event');
CREATE FOREIGN TABLE zy_device_series (
ts timestamp NOT NULL DEFAULT NOW(),
id int8 NOT NULL ,
ori int8 NOT NULL,
kind text NOT NULL,
data jsonb DEFAULT '{}'
)
SERVER zy
OPTIONS (schema_name 'public', table_name 'device_series');