79 lines
4.2 KiB
Transact-SQL
79 lines
4.2 KiB
Transact-SQL
IF NOT EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[bas_station_point]') AND type IN ('U'))
|
||
BEGIN
|
||
CREATE TABLE [dbo].[bas_station_point] (
|
||
[id] bigint IDENTITY(1,1) NOT NULL,
|
||
[point_id] varchar(64) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
[point_name] varchar(128) COLLATE Chinese_PRC_CI_AS NULL,
|
||
[warehouse_id] varchar(64) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
[roadway_id] varchar(64) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
[point_type] varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL,
|
||
[point_size] varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '1',
|
||
[tray_type] varchar(128) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '',
|
||
[status] varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '0',
|
||
[point_status] varchar(8) COLLATE Chinese_PRC_CI_AS NOT NULL DEFAULT '0',
|
||
[remark] varchar(500) COLLATE Chinese_PRC_CI_AS NULL,
|
||
[create_time] datetime2(7) NULL,
|
||
[update_time] datetime2(7) NULL,
|
||
CONSTRAINT [PK_bas_station_point] PRIMARY KEY CLUSTERED ([id]),
|
||
CONSTRAINT [UK_bas_station_point_point_id] UNIQUE ([point_id])
|
||
)
|
||
END
|
||
GO
|
||
|
||
IF COL_LENGTH('dbo.bas_station_point', 'point_name') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD point_name varchar(128) COLLATE Chinese_PRC_CI_AS NULL;
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'warehouse_id') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD warehouse_id varchar(64) COLLATE Chinese_PRC_CI_AS NULL;
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'roadway_id') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD roadway_id varchar(64) COLLATE Chinese_PRC_CI_AS NULL;
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'point_type') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD point_type varchar(8) COLLATE Chinese_PRC_CI_AS NULL;
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'point_size') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD point_size varchar(8) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT DF_bas_station_point_point_size DEFAULT '1';
|
||
GO
|
||
-- point_size：1=大点位，2=小点位，3=通用点位。
|
||
IF COL_LENGTH('dbo.bas_station_point', 'tray_type') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD tray_type varchar(128) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT DF_bas_station_point_tray_type DEFAULT '';
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'status') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD status varchar(8) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT DF_bas_station_point_status DEFAULT '0';
|
||
GO
|
||
IF COL_LENGTH('dbo.bas_station_point', 'point_status') IS NULL
|
||
ALTER TABLE dbo.bas_station_point ADD point_status varchar(8) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT DF_bas_station_point_point_status DEFAULT '0';
|
||
GO
|
||
|
||
UPDATE dbo.bas_station_point SET point_size = '1' WHERE point_size IS NULL OR point_size = '';
|
||
GO
|
||
UPDATE dbo.bas_station_point SET tray_type = '' WHERE tray_type IS NULL;
|
||
GO
|
||
UPDATE dbo.bas_station_point SET point_status = '0' WHERE point_status IS NULL OR point_status = '';
|
||
GO
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_bas_station_point_warehouse_type_status' AND object_id = OBJECT_ID(N'[dbo].[bas_station_point]'))
|
||
CREATE INDEX [IX_bas_station_point_warehouse_type_status] ON [dbo].[bas_station_point] ([warehouse_id], [point_type], [status]);
|
||
GO
|
||
|
||
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_bas_station_point_bind_filter' AND object_id = OBJECT_ID(N'[dbo].[bas_station_point]'))
|
||
CREATE INDEX [IX_bas_station_point_bind_filter] ON [dbo].[bas_station_point] ([warehouse_id], [status], [point_status], [point_type]) INCLUDE ([roadway_id], [tray_type]);
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.extended_properties
|
||
WHERE major_id = OBJECT_ID(N'[dbo].[bas_station_point]')
|
||
AND minor_id = 0
|
||
AND name = N'MS_Description'
|
||
)
|
||
BEGIN
|
||
EXEC sp_addextendedproperty 'MS_Description', N'点位配置', 'SCHEMA', N'dbo', 'TABLE', N'bas_station_point';
|
||
END
|
||
GO
|
||
|
||
-- 菜单建议：在系统菜单中新增“点位配置”，路由 path 为 stationpoint，组件路径为 bas/stationpoint/index。
|
||
-- 权限标识建议：bas:stationpoint:list/add/edit/remove/export/query。
|
||
-- 这里不直接写入 sys_menu，是为了避免不同环境菜单 ID、父级目录 ID 不一致导致冲突。
|