165 lines
6.7 KiB
SQL
165 lines
6.7 KiB
SQL
-- shop_user_trade 数据库初始化脚本
|
|
|
|
-- 背包表
|
|
CREATE TABLE IF NOT EXISTS knapsack (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
user_id BIGINT NOT NULL,
|
|
asset_id BIGINT NOT NULL,
|
|
asset_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
sku_id BIGINT NOT NULL DEFAULT 0,
|
|
sku_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
spec_values JSONB,
|
|
image_url VARCHAR(512) NOT NULL DEFAULT '',
|
|
type VARCHAR(32) NOT NULL,
|
|
stock_detail_id BIGINT NOT NULL DEFAULT 0,
|
|
batch_id BIGINT NOT NULL DEFAULT 0,
|
|
batch_no VARCHAR(64) NOT NULL DEFAULT '',
|
|
stock_mode SMALLINT NOT NULL DEFAULT 1,
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
used_at BIGINT,
|
|
expire_at BIGINT,
|
|
physical_asset_config JSONB,
|
|
service_asset_config JSONB,
|
|
virtual_asset_config JSONB
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_knapsack_user_id ON knapsack(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_knapsack_asset_id ON knapsack(asset_id);
|
|
CREATE INDEX IF NOT EXISTS idx_knapsack_status ON knapsack(status);
|
|
CREATE INDEX IF NOT EXISTS idx_knapsack_tenant_id ON knapsack(tenant_id);
|
|
|
|
-- 背包日志表
|
|
CREATE TABLE IF NOT EXISTS knapsack_log (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
knapsack_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
action VARCHAR(32) NOT NULL,
|
|
operator_id BIGINT NOT NULL DEFAULT 0,
|
|
operator_name VARCHAR(64) NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_knapsack_log_knapsack_id ON knapsack_log(knapsack_id);
|
|
|
|
-- 市场表
|
|
CREATE TABLE IF NOT EXISTS market (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
knapsack_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
asset_id BIGINT NOT NULL,
|
|
asset_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
sku_id BIGINT NOT NULL DEFAULT 0,
|
|
sku_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
image_url VARCHAR(512) NOT NULL DEFAULT '',
|
|
type VARCHAR(32) NOT NULL,
|
|
stock_detail_id BIGINT NOT NULL DEFAULT 0,
|
|
batch_id BIGINT NOT NULL DEFAULT 0,
|
|
batch_no VARCHAR(64) NOT NULL DEFAULT '',
|
|
stock_mode SMALLINT NOT NULL DEFAULT 1,
|
|
price BIGINT NOT NULL DEFAULT 0,
|
|
original_price BIGINT NOT NULL DEFAULT 0,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
list_expire_at BIGINT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_market_user_id ON market(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_market_knapsack_id ON market(knapsack_id);
|
|
CREATE INDEX IF NOT EXISTS idx_market_status ON market(status);
|
|
CREATE INDEX IF NOT EXISTS idx_market_tenant_id ON market(tenant_id);
|
|
|
|
-- 市场日志表
|
|
CREATE TABLE IF NOT EXISTS market_log (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
market_id BIGINT NOT NULL,
|
|
knapsack_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
action VARCHAR(32) NOT NULL,
|
|
operator_id BIGINT NOT NULL DEFAULT 0,
|
|
operator_name VARCHAR(64) NOT NULL DEFAULT '',
|
|
description TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_market_log_market_id ON market_log(market_id);
|
|
|
|
-- 钱包表
|
|
CREATE TABLE IF NOT EXISTS wallet (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
user_id BIGINT NOT NULL UNIQUE,
|
|
balance BIGINT NOT NULL DEFAULT 0,
|
|
currency VARCHAR(16) NOT NULL DEFAULT 'CNY',
|
|
status SMALLINT NOT NULL DEFAULT 1,
|
|
version BIGINT NOT NULL DEFAULT 1
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_user_id ON wallet(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_tenant_id ON wallet(tenant_id);
|
|
|
|
-- 钱包日志表
|
|
CREATE TABLE IF NOT EXISTS wallet_log (
|
|
id BIGINT PRIMARY KEY,
|
|
tenant_id BIGINT NOT NULL DEFAULT 0,
|
|
creator VARCHAR(64) NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updater VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
deleted_at TIMESTAMPTZ,
|
|
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
|
|
|
|
user_id BIGINT NOT NULL,
|
|
wallet_id BIGINT NOT NULL,
|
|
order_no VARCHAR(64) NOT NULL DEFAULT '',
|
|
transaction_no VARCHAR(64) NOT NULL DEFAULT '',
|
|
type VARCHAR(16) NOT NULL,
|
|
amount BIGINT NOT NULL DEFAULT 0,
|
|
balance_before BIGINT NOT NULL DEFAULT 0,
|
|
balance_after BIGINT NOT NULL DEFAULT 0,
|
|
currency VARCHAR(16) NOT NULL DEFAULT 'CNY',
|
|
description TEXT NOT NULL DEFAULT '',
|
|
extra_data JSONB
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_log_user_id ON wallet_log(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_log_wallet_id ON wallet_log(wallet_id);
|
|
CREATE INDEX IF NOT EXISTS idx_wallet_log_order_no ON wallet_log(order_no);
|