postgresql.js - master - kalugin66/FileBridge

postgresql.js
185 строк6 KB
Калугин Олег Александрович
upload files
24 янв 2026, 15:45
24 янв 2026, 15:4522a3baa
const { Pool } = require('pg');
const dotenv = require('dotenv');

dotenv.config();

class Database {
    constructor() {
        this.pool = new Pool({
            host: process.env.DB_HOST,
            port: process.env.DB_PORT,
            database: process.env.DB_NAME,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            max: 20,
            idleTimeoutMillis: 30000,
            connectionTimeoutMillis: 2000,
        });
    }

    async query(text, params) {
        const client = await this.pool.connect();
        try {
            const result = await client.query(text, params);
            return result;
        } finally {
            client.release();
        }
    }

    async init() {
        // Проверяем соединение
        await this.query('SELECT 1');
        return true;
    }

    async getUserByLogin(login) {
        const result = await this.query(
            'SELECT * FROM users WHERE login = $1',
            [login]
        );
        return result.rows[0];
    }

    async createUser(login, password, email, isAdmin = false) {
        const result = await this.query(
            `INSERT INTO users (login, password, email, is_admin) 
             VALUES ($1, $2, $3, $4) 
             RETURNING id, login, email, is_admin`,
            [login, password, email, isAdmin]
        );
        return result.rows[0];
    }

    async updateUser(id, data) {
        const updates = [];
        const values = [];
        let index = 1;

        if (data.login !== undefined) {
            updates.push(`login = $${index++}`);
            values.push(data.login);
        }
        if (data.email !== undefined) {
            updates.push(`email = $${index++}`);
            values.push(data.email);
        }
        if (data.is_admin !== undefined) {
            updates.push(`is_admin = $${index++}`);
            values.push(data.is_admin);
        }
        if (data.password !== undefined) {
            updates.push(`password = $${index++}`);
            values.push(data.password);
        }

        values.push(id);
        const query = `UPDATE users SET ${updates.join(', ')} WHERE id = $${index} RETURNING *`;
        const result = await this.query(query, values);
        return result.rows[0];
    }

    async deleteUser(id) {
        await this.query('DELETE FROM users WHERE id = $1', [id]);
        return true;
    }

    async getAllUsers() {
        const result = await this.query(
            'SELECT id, login, email, is_admin, created_at, last_login FROM users ORDER BY id'
        );
        return result.rows;
    }

    async saveFile(fileData) {
        const result = await this.query(
            `INSERT INTO files 
             (service_name, login, description, file_number, original_filename, extension, file_size, upload_date) 
             VALUES ($1, $2, $3, $4, $5, $6, $7, $8) 
             RETURNING *`,
            [
                fileData.service_name,
                fileData.login,
                fileData.description,
                fileData.file_number,
                fileData.original_filename,
                fileData.extension,
                fileData.file_size,
                fileData.upload_date
            ]
        );
        return result.rows[0];
    }

    async getFilesByLogin(login) {
        const result = await this.query(
            `SELECT * FROM files WHERE login = $1 ORDER BY uploaded_at DESC`,
            [login]
        );
        return result.rows;
    }

    async getFileByNumber(fileNumber) {
        const result = await this.query(
            `SELECT * FROM files WHERE file_number = $1`,
            [fileNumber]
        );
        return result.rows[0];
    }

    async getAllFiles(serviceName = null, date = null, extension = null, limit = 100, offset = 0) {
        let query = 'SELECT * FROM files WHERE 1=1';
        const params = [];
        let paramIndex = 1;

        if (serviceName) {
            query += ` AND service_name = $${paramIndex++}`;
            params.push(serviceName);
        }
        if (date) {
            query += ` AND upload_date = $${paramIndex++}`;
            params.push(date);
        }
        if (extension) {
            query += ` AND extension = $${paramIndex++}`;
            params.push(extension);
        }

        query += ` ORDER BY uploaded_at DESC LIMIT $${paramIndex++} OFFSET $${paramIndex++}`;
        params.push(limit, offset);

        const result = await this.query(query, params);
        return result.rows;
    }

    async countFiles(serviceName = null, date = null, extension = null) {
        let query = 'SELECT COUNT(*) FROM files WHERE 1=1';
        const params = [];
        let paramIndex = 1;

        if (serviceName) {
            query += ` AND service_name = $${paramIndex++}`;
            params.push(serviceName);
        }
        if (date) {
            query += ` AND upload_date = $${paramIndex++}`;
            params.push(date);
        }
        if (extension) {
            query += ` AND extension = $${paramIndex++}`;
            params.push(extension);
        }

        const result = await this.query(query, params);
        return parseInt(result.rows[0].count);
    }

    async updateLastLogin(login) {
        await this.query(
            'UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE login = $1',
            [login]
        );
    }
}

module.exports = new Database();