212 lines
10 KiB
Transact-SQL
212 lines
10 KiB
Transact-SQL
USE [JSDataMiddlePlatform];
|
||
GO
|
||
|
||
IF OBJECT_ID(N'dbo.ai_ImageFeatureLibrary', N'U') IS NULL
|
||
BEGIN
|
||
CREATE TABLE dbo.ai_ImageFeatureLibrary
|
||
(
|
||
id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
|
||
bizType VARCHAR(30) NOT NULL,
|
||
sourceSystem VARCHAR(50) NULL,
|
||
sourceTable VARCHAR(100) NULL,
|
||
sourceId VARCHAR(100) NULL,
|
||
skuId VARCHAR(100) NULL,
|
||
goodsId VARCHAR(100) NULL,
|
||
productName NVARCHAR(500) NULL,
|
||
categoryName NVARCHAR(200) NULL,
|
||
imageUrl NVARCHAR(1000) NOT NULL,
|
||
imageHash VARCHAR(64) NULL,
|
||
imageStatus VARCHAR(20) NOT NULL CONSTRAINT DF_ai_ImageFeatureLibrary_imageStatus DEFAULT ('pending'),
|
||
rawTagsJson NVARCHAR(MAX) NULL,
|
||
normalizedTagsJson NVARCHAR(MAX) NULL,
|
||
categoryTag NVARCHAR(100) NULL,
|
||
spaceTag NVARCHAR(50) NULL,
|
||
sceneTag NVARCHAR(50) NULL,
|
||
styleTag NVARCHAR(100) NULL,
|
||
colorTag NVARCHAR(100) NULL,
|
||
materialTag NVARCHAR(100) NULL,
|
||
visionProvider VARCHAR(50) NULL,
|
||
visionModel VARCHAR(100) NULL,
|
||
visionPromptVersion VARCHAR(50) NULL,
|
||
visionPromptTokens INT NULL,
|
||
visionCompletionTokens INT NULL,
|
||
visionTotalTokens INT NULL,
|
||
embeddingProvider VARCHAR(50) NULL,
|
||
embeddingModel VARCHAR(100) NULL,
|
||
embeddingVectorJson NVARCHAR(MAX) NULL,
|
||
embeddingDimension INT NULL,
|
||
embeddingPromptTokens INT NULL,
|
||
embeddingTotalTokens INT NULL,
|
||
processedAt DATETIME NULL,
|
||
errorMessage NVARCHAR(2000) NULL,
|
||
createdAt DATETIME NOT NULL CONSTRAINT DF_ai_ImageFeatureLibrary_createdAt DEFAULT (GETDATE()),
|
||
updatedAt DATETIME NOT NULL CONSTRAINT DF_ai_ImageFeatureLibrary_updatedAt DEFAULT (GETDATE())
|
||
);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_bizType'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_bizType
|
||
ON dbo.ai_ImageFeatureLibrary (bizType);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_source'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_source
|
||
ON dbo.ai_ImageFeatureLibrary (sourceSystem, sourceTable, sourceId);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_skuId'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_skuId
|
||
ON dbo.ai_ImageFeatureLibrary (skuId);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_goodsId'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_goodsId
|
||
ON dbo.ai_ImageFeatureLibrary (goodsId);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_status'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_status
|
||
ON dbo.ai_ImageFeatureLibrary (imageStatus, processedAt);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'IX_ai_ImageFeatureLibrary_tagSearch'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE INDEX IX_ai_ImageFeatureLibrary_tagSearch
|
||
ON dbo.ai_ImageFeatureLibrary (categoryTag, spaceTag, sceneTag, styleTag);
|
||
END
|
||
GO
|
||
|
||
IF NOT EXISTS (
|
||
SELECT 1
|
||
FROM sys.indexes
|
||
WHERE name = N'UX_ai_ImageFeatureLibrary_imageHash'
|
||
AND object_id = OBJECT_ID(N'dbo.ai_ImageFeatureLibrary')
|
||
)
|
||
BEGIN
|
||
CREATE UNIQUE INDEX UX_ai_ImageFeatureLibrary_imageHash
|
||
ON dbo.ai_ImageFeatureLibrary (imageHash)
|
||
WHERE imageHash IS NOT NULL;
|
||
END
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty
|
||
@name = N'MS_Description',
|
||
@value = N'AI图片标签与向量特征库',
|
||
@level0type = N'SCHEMA', @level0name = N'dbo',
|
||
@level1type = N'TABLE', @level1name = N'ai_ImageFeatureLibrary';
|
||
GO
|
||
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'主键ID', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'id';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'业务类型：internal=内部图库，upload=运营上传，market=市场素材', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'bizType';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'来源系统', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'sourceSystem';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'来源表名', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'sourceTable';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'来源记录ID', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'sourceId';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'SKU编码', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'skuId';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'商品ID', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'goodsId';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'商品名称', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'productName';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'原始类目名称', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'categoryName';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'图片地址', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'imageUrl';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'图片哈希，用于去重', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'imageHash';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'图片处理状态：pending/processing/success/failed', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'imageStatus';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'模型原始标签JSON', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'rawTagsJson';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化标签JSON', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'normalizedTagsJson';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化类目标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'categoryTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化空间标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'spaceTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化场景标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'sceneTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化风格标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'styleTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化颜色标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'colorTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标准化材质标签', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'materialTag';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签识别服务商', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionProvider';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签识别模型', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionModel';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签提示词版本', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionPromptVersion';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签识别输入Token数', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionPromptTokens';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签识别输出Token数', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionCompletionTokens';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签识别总Token数', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'visionTotalTokens';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'向量服务商', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingProvider';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'向量模型', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingModel';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'图片向量JSON，保存完整向量数组', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingVectorJson';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'向量维度', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingDimension';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'向量生成输入Token数', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingPromptTokens';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'向量生成总Token数', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'embeddingTotalTokens';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'标签和向量处理完成时间', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'processedAt';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'处理失败时的错误信息', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'errorMessage';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'创建时间', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'createdAt';
|
||
GO
|
||
EXEC sys.sp_addextendedproperty N'MS_Description', N'更新时间', N'SCHEMA', N'dbo', N'TABLE', N'ai_ImageFeatureLibrary', N'COLUMN', N'updatedAt';
|
||
GO
|