ffxi-trusts/load_to_db.js

/**
 * Script to load trust character data from JSON into PostgreSQL database
 */

const fs = require('fs');
const { Pool } = require('pg');
const path = require('path');

// Read database configuration
const dbConfFile = fs.readFileSync('db.conf', 'utf8');
const dbConfig = {};

// Parse the db.conf file
dbConfFile.split('\n').forEach(line => {
  if (line.trim() === '') return;
  
  const [key, value] = line.split('=');
  if (key && value) {
    // Remove quotes if present
    const cleanValue = value.replace(/^['"]|['"]$/g, '');
    dbConfig[key] = cleanValue;
  }
});

// Configure PostgreSQL connection
const pool = new Pool({
  user: dbConfig.PSQL_USER,
  host: dbConfig.PSQL_HOST,
  database: dbConfig.PSQL_DBNAME,
  password: dbConfig.PSQL_PASSWORD,
  port: dbConfig.PSQL_PORT,
});

// Read the JSON data
const trustsData = JSON.parse(fs.readFileSync('trusts.json', 'utf8'));

// Function to truncate the table and reset the sequence
async function clearTable() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('TRUNCATE TABLE trusts RESTART IDENTITY');
    await client.query('COMMIT');
    console.log('Table cleared successfully');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('Error clearing table:', e);
    throw e;
  } finally {
    client.release();
  }
}

// Function to insert data into the database
async function insertData() {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    // Insert each character
    for (let i = 0; i < trustsData.length; i++) {
      const char = trustsData[i];
      
      // Skip invalid entries
      if (!char.name || char.name === 'Tank' || 
          char.name.startsWith('Complete') || 
          char.name.startsWith('Trade') || 
          char.name.startsWith('Notice:') || 
          char.name.startsWith('Be in') ||
          char.name.startsWith('*')) {
        continue;
      }
      
      // Function to truncate text to fit in VARCHAR(255)
      const truncateText = (text, maxLength = 255) => {
        if (!text) return '';
        return text.length > maxLength ? text.substring(0, maxLength - 3) + '...' : text;
      };
      
      // Function to clean text by removing unwanted strings
      const cleanText = (text) => {
        if (!text) return '';
        // Remove "SC Icon.png" strings
        return text.replace(/SC Icon\.png/g, '');
      };
      
      // Prepare the data for insertion
      const data = {
        name: truncateText(cleanText(char.name)),
        role: truncateText(cleanText(char.role || 'Unknown')),
        job: truncateText(cleanText(char.job || '')),
        spells: truncateText(cleanText(char.spells || '')),
        abilities: truncateText(cleanText(char.abilities || '')),
        weapon_skills: truncateText(cleanText(char.weapon_skills || '')),
        invincible: char.invincible || false,
        acquisition: cleanText(char.acquisition || ''), // No truncation for TEXT columns
        special_features: cleanText(char.special_features || ''), // No truncation for TEXT columns
        trust_synergy: cleanText(char.trust_synergy || '') // No truncation for TEXT columns
      };
      
      // Insert the data
      const query = `
        INSERT INTO trusts (
          id, name, role, job, spells, abilities, weapon_skills, 
          invincible, acquisition, special_features, trust_synergy
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11
        )
      `;
      
      const values = [
        i + 1, // Use the index + 1 as the ID
        data.name,
        data.role,
        data.job,
        data.spells,
        data.abilities,
        data.weapon_skills,
        data.invincible,
        data.acquisition,
        data.special_features,
        data.trust_synergy
      ];
      
      await client.query(query, values);
      console.log(`Inserted character: ${data.name}`);
    }
    
    await client.query('COMMIT');
    console.log('All data inserted successfully');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('Error inserting data:', e);
    throw e;
  } finally {
    client.release();
  }
}

// Main function
async function main() {
  try {
    // Clear the table first
    await clearTable();
    
    // Insert the data
    await insertData();
    
    // Close the pool
    await pool.end();
    
    console.log('Database update completed successfully');
  } catch (e) {
    console.error('Error updating database:', e);
    process.exit(1);
  }
}

// Run the main function
main();