322 lines
12 KiB
JavaScript
322 lines
12 KiB
JavaScript
|
const mysql = require('mysql2/promise');
|
|||
|
|
|
|||
|
|
// 数据库连接配置
|
|||
|
|
const dbConfig = {
|
|||
|
|
host: 'gz-cynosdbmysql-grp-d26pzce5.sql.tencentcdb.com',
|
|||
|
|
port: 24936,
|
|||
|
|
user: 'root',
|
|||
|
|
password: '!Rjb12191',
|
|||
|
|
database: 'pronode_db',
|
|||
|
|
charset: 'utf8mb4'
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
async function createTables() {
|
|||
|
|
let connection;
|
|||
|
|
|
|||
|
|
try {
|
|||
|
|
console.log('🔌 正在连接腾讯云数据库...');
|
|||
|
|
|
|||
|
|
// 创建连接
|
|||
|
|
connection = await mysql.createConnection(dbConfig);
|
|||
|
|
|
|||
|
|
console.log('✅ 数据库连接成功！');
|
|||
|
|
|
|||
|
|
// 使用数据库
|
|||
|
|
await connection.execute('USE pronode_db');
|
|||
|
|
console.log('✅ 已切换到 pronode_db 数据库');
|
|||
|
|
|
|||
|
|
// 创建表
|
|||
|
|
console.log('🏗️ 开始创建数据表...');
|
|||
|
|
|
|||
|
|
// 1. 用户表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_users (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|||
|
|
name VARCHAR(255),
|
|||
|
|
avatar VARCHAR(500),
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_email (email),
|
|||
|
|
INDEX idx_created_at (createdAt)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 用户表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 用户表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 2. 提示词模板表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_templates (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
title VARCHAR(255) NOT NULL,
|
|||
|
|
description TEXT,
|
|||
|
|
category VARCHAR(100) NOT NULL,
|
|||
|
|
tags JSON,
|
|||
|
|
role TEXT NOT NULL,
|
|||
|
|
task TEXT NOT NULL,
|
|||
|
|
context TEXT,
|
|||
|
|
constraints JSON,
|
|||
|
|
outputFormat VARCHAR(100) NOT NULL,
|
|||
|
|
variables JSON,
|
|||
|
|
examples JSON,
|
|||
|
|
authorId VARCHAR(255) NOT NULL,
|
|||
|
|
isPublic BOOLEAN DEFAULT TRUE,
|
|||
|
|
isFeatured BOOLEAN DEFAULT FALSE,
|
|||
|
|
usageCount INT DEFAULT 0,
|
|||
|
|
rating DECIMAL(3,2) DEFAULT 0.00,
|
|||
|
|
ratingCount INT DEFAULT 0,
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_category (category),
|
|||
|
|
INDEX idx_author (authorId),
|
|||
|
|
INDEX idx_public (isPublic),
|
|||
|
|
INDEX idx_featured (isFeatured),
|
|||
|
|
INDEX idx_rating (rating),
|
|||
|
|
INDEX idx_usage (usageCount),
|
|||
|
|
INDEX idx_created_at (createdAt)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 模板表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 模板表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 3. 收藏表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_favorites (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
userId VARCHAR(255) NOT NULL,
|
|||
|
|
templateId VARCHAR(255) NOT NULL,
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
UNIQUE KEY unique_user_template (userId, templateId),
|
|||
|
|
INDEX idx_user (userId),
|
|||
|
|
INDEX idx_template (templateId),
|
|||
|
|
INDEX idx_created_at (createdAt)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 收藏表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 收藏表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 4. 评论表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_comments (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
content TEXT NOT NULL,
|
|||
|
|
rating INT,
|
|||
|
|
userId VARCHAR(255) NOT NULL,
|
|||
|
|
templateId VARCHAR(255) NOT NULL,
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_user (userId),
|
|||
|
|
INDEX idx_template (templateId),
|
|||
|
|
INDEX idx_rating (rating),
|
|||
|
|
INDEX idx_created_at (createdAt)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 评论表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 评论表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 5. 测试记录表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_tests (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
templateId VARCHAR(255) NOT NULL,
|
|||
|
|
input JSON,
|
|||
|
|
output TEXT,
|
|||
|
|
model VARCHAR(100),
|
|||
|
|
parameters JSON,
|
|||
|
|
status VARCHAR(20) DEFAULT 'pending',
|
|||
|
|
duration INT,
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_template (templateId),
|
|||
|
|
INDEX idx_status (status),
|
|||
|
|
INDEX idx_model (model),
|
|||
|
|
INDEX idx_created_at (createdAt)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 测试记录表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 测试记录表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 6. 系统配置表
|
|||
|
|
try {
|
|||
|
|
await connection.execute(`
|
|||
|
|
CREATE TABLE IF NOT EXISTS promptforge_configs (
|
|||
|
|
id VARCHAR(255) PRIMARY KEY,
|
|||
|
|
config_key VARCHAR(255) UNIQUE NOT NULL,
|
|||
|
|
value TEXT NOT NULL,
|
|||
|
|
type VARCHAR(50) DEFAULT 'string',
|
|||
|
|
createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|||
|
|
updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|||
|
|
INDEX idx_key (config_key),
|
|||
|
|
INDEX idx_type (type)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
|
|||
|
|
`);
|
|||
|
|
console.log('✅ 系统配置表创建成功');
|
|||
|
|
} catch (error) {
|
|||
|
|
console.log('⚠️ 系统配置表已存在或创建失败:', error.message);
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 验证表是否创建成功
|
|||
|
|
console.log('🔍 验证表创建结果...');
|
|||
|
|
const [tables] = await connection.execute("SHOW TABLES LIKE 'promptforge_%'");
|
|||
|
|
console.log('📋 PromptForge 相关表:');
|
|||
|
|
tables.forEach(table => {
|
|||
|
|
console.log(` - ${Object.values(table)[0]}`);
|
|||
|
|
});
|
|||
|
|
|
|||
|
|
// 插入初始数据
|
|||
|
|
console.log('🌱 插入初始数据...');
|
|||
|
|
await insertInitialData(connection);
|
|||
|
|
|
|||
|
|
console.log('🎉 数据库表创建完成！');
|
|||
|
|
return true;
|
|||
|
|
|
|||
|
|
} catch (error) {
|
|||
|
|
console.error('❌ 数据库操作失败:', error.message);
|
|||
|
|
return false;
|
|||
|
|
} finally {
|
|||
|
|
if (connection) {
|
|||
|
|
await connection.end();
|
|||
|
|
console.log('🔌 数据库连接已关闭');
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
async function insertInitialData(connection) {
|
|||
|
|
try {
|
|||
|
|
// 创建管理员用户
|
|||
|
|
const adminUser = {
|
|||
|
|
id: 'admin_' + Date.now(),
|
|||
|
|
email: 'admin@promptforge.com',
|
|||
|
|
name: 'PromptForge Admin',
|
|||
|
|
avatar: 'https://avatars.githubusercontent.com/u/1234567?v=4'
|
|||
|
|
};
|
|||
|
|
|
|||
|
|
await connection.execute(
|
|||
|
|
'INSERT IGNORE INTO promptforge_users (id, email, name, avatar) VALUES (?, ?, ?, ?)',
|
|||
|
|
[adminUser.id, adminUser.email, adminUser.name, adminUser.avatar]
|
|||
|
|
);
|
|||
|
|
console.log('✅ 管理员用户创建成功');
|
|||
|
|
|
|||
|
|
// 创建示例模板
|
|||
|
|
const sampleTemplates = [
|
|||
|
|
{
|
|||
|
|
id: 'template_1_' + Date.now(),
|
|||
|
|
title: 'API设计文档生成器',
|
|||
|
|
description: '生成完整的API设计文档，包含端点、参数、响应格式等',
|
|||
|
|
category: 'programming',
|
|||
|
|
tags: JSON.stringify(['api', 'documentation', 'backend']),
|
|||
|
|
role: '你是一位资深软件架构师，拥有丰富的API设计经验。',
|
|||
|
|
task: '我的任务是生成一个关于{{topic}}的API设计文档。',
|
|||
|
|
context: '这个API将用于{{useCase}}场景，需要支持{{features}}功能。',
|
|||
|
|
constraints: JSON.stringify([
|
|||
|
|
{ id: '1', text: '使用RESTful设计原则', category: 'quality' },
|
|||
|
|
{ id: '2', text: '包含完整的错误处理', category: 'safety' },
|
|||
|
|
{ id: '3', text: '提供OpenAPI 3.0规范', category: 'format' }
|
|||
|
|
]),
|
|||
|
|
outputFormat: 'markdown',
|
|||
|
|
variables: JSON.stringify([
|
|||
|
|
{ name: 'topic', type: 'text', required: true, description: 'API主题' },
|
|||
|
|
{ name: 'useCase', type: 'text', required: true, description: '使用场景' },
|
|||
|
|
{ name: 'features', type: 'text', required: false, description: '核心功能' }
|
|||
|
|
]),
|
|||
|
|
authorId: adminUser.id,
|
|||
|
|
isPublic: true,
|
|||
|
|
isFeatured: true,
|
|||
|
|
usageCount: 156,
|
|||
|
|
rating: 4.8,
|
|||
|
|
ratingCount: 23
|
|||
|
|
},
|
|||
|
|
{
|
|||
|
|
id: 'template_2_' + Date.now(),
|
|||
|
|
title: '营销文案优化器',
|
|||
|
|
description: '优化营销文案，提升转化率和用户 engagement',
|
|||
|
|
category: 'marketing',
|
|||
|
|
tags: JSON.stringify(['marketing', 'copywriting', 'conversion']),
|
|||
|
|
role: '你是一位经验丰富的营销文案专家，擅长AIDA模型和情感营销。',
|
|||
|
|
task: '请优化以下营销文案，使其更具吸引力和转化力：{{originalCopy}}',
|
|||
|
|
context: '目标受众是{{targetAudience}}，产品是{{product}}，主要卖点是{{valueProposition}}。',
|
|||
|
|
constraints: JSON.stringify([
|
|||
|
|
{ id: '1', text: '保持品牌调性一致', category: 'quality' },
|
|||
|
|
{ id: '2', text: '包含明确的行动号召', category: 'format' },
|
|||
|
|
{ id: '3', text: '字数控制在{{wordLimit}}字以内', category: 'performance' }
|
|||
|
|
]),
|
|||
|
|
outputFormat: 'plain-text',
|
|||
|
|
variables: JSON.stringify([
|
|||
|
|
{ name: 'originalCopy', type: 'text', required: true, description: '原始文案' },
|
|||
|
|
{ name: 'targetAudience', type: 'text', required: true, description: '目标受众' },
|
|||
|
|
{ name: 'product', type: 'text', required: true, description: '产品名称' },
|
|||
|
|
{ name: 'valueProposition', type: 'text', required: true, description: '价值主张' },
|
|||
|
|
{ name: 'wordLimit', type: 'number', required: false, defaultValue: 200, description: '字数限制' }
|
|||
|
|
]),
|
|||
|
|
authorId: adminUser.id,
|
|||
|
|
isPublic: true,
|
|||
|
|
isFeatured: true,
|
|||
|
|
usageCount: 89,
|
|||
|
|
rating: 4.6,
|
|||
|
|
ratingCount: 15
|
|||
|
|
}
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
for (const template of sampleTemplates) {
|
|||
|
|
await connection.execute(
|
|||
|
|
`INSERT IGNORE INTO promptforge_templates (
|
|||
|
|
id, title, description, category, tags, role, task, context,
|
|||
|
|
constraints, outputFormat, variables, authorId, isPublic,
|
|||
|
|
isFeatured, usageCount, rating, ratingCount
|
|||
|
|
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|||
|
|
[
|
|||
|
|
template.id, template.title, template.description, template.category,
|
|||
|
|
template.tags, template.role, template.task, template.context,
|
|||
|
|
template.constraints, template.outputFormat, template.variables,
|
|||
|
|
template.authorId, template.isPublic, template.isFeatured,
|
|||
|
|
template.usageCount, template.rating, template.ratingCount
|
|||
|
|
]
|
|||
|
|
);
|
|||
|
|
}
|
|||
|
|
console.log('✅ 示例模板创建成功');
|
|||
|
|
|
|||
|
|
// 创建系统配置
|
|||
|
|
const configs = [
|
|||
|
|
{ key: 'site_name', value: 'PromptForge', type: 'string' },
|
|||
|
|
{ key: 'site_description', value: '专为大模型提示词系统优化的平台', type: 'string' },
|
|||
|
|
{ key: 'max_templates_per_user', value: '100', type: 'number' },
|
|||
|
|
{ key: 'enable_registration', value: 'true', type: 'boolean' }
|
|||
|
|
];
|
|||
|
|
|
|||
|
|
for (const config of configs) {
|
|||
|
|
await connection.execute(
|
|||
|
|
'INSERT IGNORE INTO promptforge_configs (id, config_key, value, type) VALUES (?, ?, ?, ?)',
|
|||
|
|
['config_' + Date.now() + Math.random(), config.key, config.value, config.type]
|
|||
|
|
);
|
|||
|
|
}
|
|||
|
|
console.log('✅ 系统配置创建成功');
|
|||
|
|
|
|||
|
|
} catch (error) {
|
|||
|
|
console.error('❌ 初始数据插入失败:', error.message);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
// 运行创建表脚本
|
|||
|
|
createTables().then(success => {
|
|||
|
|
if (success) {
|
|||
|
|
console.log('🎉 数据库表创建和初始化完成！');
|
|||
|
|
} else {
|
|||
|
|
console.log('💥 数据库表创建失败！');
|
|||
|
|
}
|
|||
|
|
process.exit(success ? 0 : 1);
|
|||
|
|
});
|