413 lines
15 KiB
JavaScript
413 lines
15 KiB
JavaScript
const { Pool } = require('pg');
|
|
const bcrypt = require('bcryptjs');
|
|
const path = require('path');
|
|
require('dotenv').config({ path: path.join(__dirname, '../.env') });
|
|
|
|
const pool = new Pool({
|
|
user: process.env.DB_USER,
|
|
host: process.env.DB_HOST,
|
|
database: process.env.DB_DATABASE || process.env.DB_NAME,
|
|
password: process.env.DB_PASSWORD,
|
|
port: process.env.DB_PORT,
|
|
});
|
|
|
|
const USER_COUNT = 20000;
|
|
const ACHIEVEMENT_COUNT = 500000;
|
|
const BATCH_SIZE = 1000;
|
|
const RUN_CLEAN = process.argv.includes('--clean');
|
|
|
|
const dictTables = [
|
|
'dict_organizations',
|
|
'dict_award_types',
|
|
'dict_award_levels',
|
|
'dict_paper_types',
|
|
'dict_standard_types',
|
|
'dict_project_categories',
|
|
];
|
|
|
|
const achievementTypes = [
|
|
'paper',
|
|
'award',
|
|
'project',
|
|
'standard',
|
|
'monograph',
|
|
'report',
|
|
'plan',
|
|
'patent',
|
|
'transformation',
|
|
'software',
|
|
];
|
|
const USERNAME_PREFIX = '176';
|
|
const SEED_REMARK = 'seed_500k';
|
|
const STATUSES = ['pending', 'approved', 'rejected'];
|
|
const PATENT_TYPES = ['invention', 'utility', 'design'];
|
|
const REPORT_TYPES = ['technical', 'inspection', 'analysis', 'feasibility'];
|
|
const TRANS_METHODS = ['transfer', 'license', 'cooperation', 'self-use'];
|
|
const ACQUISITION_METHODS = ['independent', 'co-development', 'transfer'];
|
|
const SOFTWARE_SCOPES = ['national', 'industry', 'department'];
|
|
|
|
const randomInt = (min, max) => Math.floor(Math.random() * (max - min + 1)) + min;
|
|
const randomChoice = (arr) => arr[randomInt(0, arr.length - 1)];
|
|
const pad = (num, size) => String(num).padStart(size, '0');
|
|
const randomDateInYears = (years) =>
|
|
new Date(randomChoice(years), randomInt(0, 11), randomInt(1, 28));
|
|
|
|
function buildInsert(table, columns, rows) {
|
|
const values = [];
|
|
const params = [];
|
|
let paramIndex = 1;
|
|
|
|
for (const row of rows) {
|
|
const placeholders = [];
|
|
for (const value of row) {
|
|
params.push(value);
|
|
placeholders.push(`$${paramIndex++}`);
|
|
}
|
|
values.push(`(${placeholders.join(', ')})`);
|
|
}
|
|
|
|
const sql = `INSERT INTO ${table} (${columns.join(', ')}) VALUES ${values.join(', ')}`;
|
|
return { sql, params };
|
|
}
|
|
|
|
async function fetchNames(client, table) {
|
|
const res = await client.query(`SELECT name FROM ${table} ORDER BY id`);
|
|
return res.rows.map((row) => row.name);
|
|
}
|
|
|
|
async function seedUsers(client, departments) {
|
|
const passwordHash = await bcrypt.hash('123456', 10);
|
|
let created = 0;
|
|
|
|
for (let offset = 0; offset < USER_COUNT; offset += BATCH_SIZE) {
|
|
const batchSize = Math.min(BATCH_SIZE, USER_COUNT - offset);
|
|
const rows = [];
|
|
|
|
for (let i = 0; i < batchSize; i++) {
|
|
const index = offset + i;
|
|
const username = `${USERNAME_PREFIX}${pad(index, 8)}`; // 11 digits, deterministic
|
|
const realName = `Perf User ${index + 1}`;
|
|
const department = randomChoice(departments);
|
|
rows.push([username, passwordHash, 'user', realName, department, 'verified']);
|
|
}
|
|
|
|
const { sql, params } = buildInsert(
|
|
'users',
|
|
['username', 'password_hash', 'role', 'real_name', 'department', 'dept_status'],
|
|
rows
|
|
);
|
|
|
|
await client.query(
|
|
`${sql} ON CONFLICT (username) DO UPDATE SET
|
|
password_hash = EXCLUDED.password_hash,
|
|
role = EXCLUDED.role,
|
|
real_name = EXCLUDED.real_name,
|
|
department = EXCLUDED.department,
|
|
dept_status = EXCLUDED.dept_status`
|
|
, params);
|
|
|
|
created += batchSize;
|
|
if (created % 5000 === 0) {
|
|
console.log(`[seed-data-500k] users upserted: ${created}`);
|
|
}
|
|
}
|
|
}
|
|
|
|
async function seedAchievements(client, departments, dicts, userIds) {
|
|
let inserted = 0;
|
|
const dateYears = [2023, 2024, 2026];
|
|
|
|
for (let offset = 0; offset < ACHIEVEMENT_COUNT; offset += BATCH_SIZE) {
|
|
const batchSize = Math.min(BATCH_SIZE, ACHIEVEMENT_COUNT - offset);
|
|
await client.query('BEGIN');
|
|
try {
|
|
const rows = [];
|
|
const achievementDates = [];
|
|
|
|
for (let i = 0; i < batchSize; i++) {
|
|
const index = offset + i;
|
|
const userId = randomChoice(userIds);
|
|
const type = randomChoice(achievementTypes);
|
|
const name = `Perf Achievement ${index + 1}`;
|
|
const assignedDept = randomChoice(departments);
|
|
const date = randomDateInYears(dateYears);
|
|
achievementDates.push(date);
|
|
const contributors = [
|
|
{ name: `Contributor ${index + 1}A`, phone: `130${pad(index % 100000000, 8)}`, isMain: true },
|
|
{ name: `Contributor ${index + 1}B`, phone: `131${pad(index % 100000000, 8)}`, isMain: false },
|
|
];
|
|
rows.push([
|
|
userId,
|
|
type,
|
|
name,
|
|
JSON.stringify(contributors),
|
|
[assignedDept],
|
|
date,
|
|
randomChoice(STATUSES),
|
|
SEED_REMARK,
|
|
date,
|
|
]);
|
|
}
|
|
|
|
const { sql, params } = buildInsert(
|
|
'achievements',
|
|
[
|
|
'user_id',
|
|
'type',
|
|
'name',
|
|
'contributors',
|
|
'assigned_departments',
|
|
'achievement_date',
|
|
'status',
|
|
'remarks',
|
|
'created_at',
|
|
],
|
|
rows
|
|
);
|
|
|
|
const res = await client.query(`${sql} RETURNING id, type`, params);
|
|
|
|
const attachmentsRows = [];
|
|
const paperRows = [];
|
|
const awardRows = [];
|
|
const projectRows = [];
|
|
const standardRows = [];
|
|
const monographRows = [];
|
|
const reportRows = [];
|
|
const planRows = [];
|
|
const patentRows = [];
|
|
const transformationRows = [];
|
|
const softwareRows = [];
|
|
|
|
for (let i = 0; i < res.rows.length; i++) {
|
|
const { id, type } = res.rows[i];
|
|
const date = achievementDates[i];
|
|
const idx = offset + i + 1;
|
|
attachmentsRows.push([id, `attachment_${idx}.pdf`, 'uploads/mock_file.pdf']);
|
|
|
|
if (type === 'paper') {
|
|
paperRows.push([id, randomChoice(dicts.dict_paper_types), 'Perf Journal', randomDateInYears(dateYears)]);
|
|
} else if (type === 'award') {
|
|
awardRows.push([
|
|
id,
|
|
randomChoice(dicts.dict_award_types),
|
|
randomChoice(dicts.dict_award_levels),
|
|
randomChoice(dicts.dict_organizations),
|
|
]);
|
|
} else if (type === 'project') {
|
|
projectRows.push([id, randomChoice(dicts.dict_project_categories), 'Perf Project Source']);
|
|
} else if (type === 'standard') {
|
|
const standardYear = date.getFullYear();
|
|
standardRows.push([
|
|
id,
|
|
randomChoice(dicts.dict_standard_types),
|
|
`STD-${standardYear}-${pad(idx, 6)}`,
|
|
date,
|
|
]);
|
|
} else if (type === 'monograph') {
|
|
monographRows.push([id, 'Perf Press', `ISBN-${pad(idx, 9)}`]);
|
|
} else if (type === 'report') {
|
|
reportRows.push([id, randomChoice(REPORT_TYPES), 'Recipient Unit', null, `Approver ${idx}`]);
|
|
} else if (type === 'plan') {
|
|
planRows.push([id]);
|
|
} else if (type === 'patent') {
|
|
patentRows.push([id, `PAT-${pad(idx, 8)}`, randomChoice(PATENT_TYPES), 'Perf Assignee']);
|
|
} else if (type === 'transformation') {
|
|
transformationRows.push([
|
|
id,
|
|
randomChoice(TRANS_METHODS),
|
|
randomInt(50, 5000) * 1000 + randomInt(0, 99) / 100,
|
|
randomChoice(dicts.dict_project_categories),
|
|
]);
|
|
} else if (type === 'software') {
|
|
softwareRows.push([
|
|
id,
|
|
`SW-${pad(idx, 8)}`,
|
|
randomChoice(ACQUISITION_METHODS),
|
|
randomChoice(SOFTWARE_SCOPES),
|
|
'Perf Owner Unit',
|
|
]);
|
|
}
|
|
}
|
|
|
|
if (attachmentsRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_attachments',
|
|
['achievement_id', 'file_name', 'file_path'],
|
|
attachmentsRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
|
|
if (paperRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_paper',
|
|
['achievement_id', 'paper_type', 'journal_name', 'publish_date'],
|
|
paperRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (awardRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_award',
|
|
['achievement_id', 'award_type', 'award_level', 'award_unit'],
|
|
awardRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (projectRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_project',
|
|
['achievement_id', 'project_category', 'source'],
|
|
projectRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (standardRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_standard',
|
|
['achievement_id', 'standard_type', 'standard_no', 'implement_date'],
|
|
standardRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (monographRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_monograph',
|
|
['achievement_id', 'publisher', 'isbn'],
|
|
monographRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (reportRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_report',
|
|
['achievement_id', 'report_type', 'recipient', 'approver_id', 'approver_name'],
|
|
reportRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (planRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_plan',
|
|
['achievement_id'],
|
|
planRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (patentRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_patent',
|
|
['achievement_id', 'patent_no', 'patent_type', 'assignee'],
|
|
patentRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (transformationRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_transformation',
|
|
['achievement_id', 'trans_method', 'trans_amount', 'project_category'],
|
|
transformationRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
if (softwareRows.length) {
|
|
const insert = buildInsert(
|
|
'achievement_software',
|
|
['achievement_id', 'reg_no', 'acquisition_method', 'scope', 'owner_unit'],
|
|
softwareRows
|
|
);
|
|
await client.query(insert.sql, insert.params);
|
|
}
|
|
|
|
await client.query('COMMIT');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
}
|
|
inserted += batchSize;
|
|
if (inserted % 10000 === 0) {
|
|
console.log(`[seed-data-500k] achievements inserted: ${inserted}`);
|
|
}
|
|
}
|
|
}
|
|
|
|
async function cleanSeedData(client) {
|
|
console.log('[seed-data-500k] cleaning existing seeded data...');
|
|
await client.query('BEGIN');
|
|
try {
|
|
const usersRes = await client.query(
|
|
'SELECT id FROM users WHERE username LIKE $1',
|
|
[`${USERNAME_PREFIX}%`]
|
|
);
|
|
const userIds = usersRes.rows.map((row) => row.id);
|
|
|
|
await client.query(
|
|
'DELETE FROM achievements WHERE remarks = $1 OR user_id = ANY($2)',
|
|
[SEED_REMARK, userIds.length ? userIds : [0]]
|
|
);
|
|
|
|
await client.query(
|
|
'DELETE FROM users WHERE username LIKE $1',
|
|
[`${USERNAME_PREFIX}%`]
|
|
);
|
|
|
|
await client.query('COMMIT');
|
|
console.log('[seed-data-500k] clean done');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
throw err;
|
|
}
|
|
}
|
|
|
|
async function main() {
|
|
const client = await pool.connect();
|
|
try {
|
|
console.log('[seed-data-500k] start');
|
|
|
|
if (RUN_CLEAN) {
|
|
await cleanSeedData(client);
|
|
}
|
|
|
|
const departments = await fetchNames(client, 'departments');
|
|
if (departments.length === 0) {
|
|
throw new Error('No departments found. Run "npm run init-db" first.');
|
|
}
|
|
|
|
const dicts = {};
|
|
for (const table of dictTables) {
|
|
const values = await fetchNames(client, table);
|
|
if (values.length === 0) {
|
|
throw new Error(`No data in ${table}. Run "npm run init-db" first.`);
|
|
}
|
|
dicts[table] = values;
|
|
}
|
|
|
|
await client.query('BEGIN');
|
|
await seedUsers(client, departments);
|
|
await client.query('COMMIT');
|
|
|
|
const userIdsRes = await client.query(
|
|
'SELECT id FROM users WHERE username LIKE $1 ORDER BY id',
|
|
[`${USERNAME_PREFIX}%`]
|
|
);
|
|
const userIds = userIdsRes.rows.map((row) => row.id);
|
|
if (userIds.length === 0) {
|
|
throw new Error('No seeded users found.');
|
|
}
|
|
|
|
await seedAchievements(client, departments, dicts, userIds);
|
|
|
|
console.log('[seed-data-500k] done');
|
|
} catch (err) {
|
|
await client.query('ROLLBACK');
|
|
console.error('[seed-data-500k] failed:', err.message || err);
|
|
process.exitCode = 1;
|
|
} finally {
|
|
client.release();
|
|
await pool.end();
|
|
}
|
|
}
|
|
|
|
main();
|