ZAP connection with DB

ZAP Integration with Database

ZAP (ZCubes Advanced Platform) is a desktop application within the ZCubes ecosystem, providing a powerful suite for computation, programming, content creation, and more. Integrating database connectivity with ZAP allows users to manage, analyze, and visualize data directly from the ZAP desktop environment, leveraging ZCubes' omni-functional capabilities.

This guide provides a step-by-step approach to connecting ZAP Desktop with a database (such as PostgreSQL) on ZCubes, inspired by the integration style of other advanced computational tools.

Overview

ZAP Desktop, as part of ZCubes, supports extensibility through scripting (JavaScript, z^3, and more) and integration with external systems. By enabling database connections, users can automate data workflows, perform analytics, and connect ZAP’s content creation tools with live data sources.

Prerequisites

• ZAP Desktop installed from the ZCubes platform.
• Database server (e.g., PostgreSQL, MySQL) installed and running.
• Node.js installed for scripting and integration.
• Database client libraries (e.g., pg for PostgreSQL).

Installation Steps

1. Install ZAP Desktop

• Download ZAP Desktop from the ZCubes platform.
• Follow the installation instructions for your operating system.
• Launch ZAP Desktop and ensure it is running.

2. Set Up Your Database

• Install your preferred database (e.g., PostgreSQL).
• Create a database (e.g., ZAPTesting) and user credentials.
• Note the host, port, username, and password for use in your scripts.

3. Install Node.js and Database Client in a Specific Directory

• Download and install Node.js from nodejs.org.
• Choose or create a directory for your ZAP project, for example: D:/ZAP_Testing
• Open a terminal or command prompt and run:

npm install pg --prefix D:/ZAP_Testing

• This will install the pg library and its dependencies into D:/ZAP_Testing/node_modules.

• (Optional) If you want to manage dependencies with a package.json, run:

cd D:/ZAP_Testing
npm init
npm install pg

• In your ZAP Desktop code block (before requiring the npm package), add:

AddToModuleSearchPath("D:/ZAP_Testing/node_modules")

• This tells ZAP where to look for your installed npm modules.
• Example screenshot of usage:*

4. Configure Database Connection Script

Below is a sample JavaScript (Node.js) script for connecting ZAP Desktop to a PostgreSQL database. This script can be run inside ZAP’s scripting interface or as an external process, with output routed to ZCubes windows using ZAppend.

const windowId = "7Space"; 

const originalConsoleLog = console.log;
console.log = function(...args) {
    const message = args.map(arg => 
        typeof arg === 'object' ? JSON.stringify(arg) : String(arg)
    ).join(' ');
    originalConsoleLog.apply(console, args);
    
    if (typeof ZAppend === 'function') {
        ZAppend(windowId, message);
    } else {
        originalConsoleLog('ZAppend not available in current context');
    }
};

const fs = require("fs");
const pg = require("pg");

const config = {
    user: "postgres",
    password: "root", 
    host: "localhost",
    port: "5432",
    database: "ZAPTesting",
    ssl: false,
    connectionTimeoutMillis: 5000,
    query_timeout: 10000
};

const testConnection = async () => {
    const client = new pg.Client(config);
    
    try {
        console.log("Testing PostgreSQL connection...");
        await client.connect();
        console.log('Connection successful to ZAPTesting database');
        
        // Additional test query for verification
        const res = await client.query('SELECT $1::text as message', ['ZAP-ZCubes connection working']);
        console.log('Test query result:', res.rows[0].message);
        
    } catch (err) {
        console.error('Connection failed:', err.message);
        
    } finally {
        await client.end();
        console.log('Connection closed');
    }
};

if (typeof ZAppend === 'function') {
    ZAppend(windowId, "Starting PostgreSQL connection test...");
    testConnection();
} else {
    console.log("Running in non-ZCube environment");
    testConnection();
}

Output:

Testing PostgreSQL connection...

Connection successful to ZAPTesting database

Connection closed

5. Using Database Features in ZAP Desktop

• Open a new script or code cell in ZAP.
• Select the appropriate language (JavaScript, z^3, etc.).
• Use your database connection to run queries, insert or retrieve data, and visualize results directly in ZAP windows using ZAppend or similar functions.

Example for creating the database:

// --- ZAppend Console Log Redirect ---
const windowId = "10Space"; // Current ZCube window ID from debug log

// Save original console.log and override to use ZAppend
const originalConsoleLog = console.log;
console.log = function(...args) {
    const message = args.map(arg => 
        typeof arg === 'object' ? JSON.stringify(arg) : String(arg)
    ).join(' ');
    originalConsoleLog.apply(console, args);
    
    if (typeof ZAppend === 'function') {
        ZAppend(windowId, message);
    } else {
        originalConsoleLog('ZAppend not available in current context');
    }
};

// --- Random Data Generation Code ---
const fs = require("fs");
const pg = require("pg");

const config = {
    user: "postgres",
    password: "root", 
    host: "localhost",
    port: "5432",
    database: "ZAPTesting",
    ssl: false,
    connectionTimeoutMillis: 5000,
    query_timeout: 10000
};

async function generateRandomData() {
    const client = new pg.Client(config);
    
    try {
        await client.connect();
        console.log('Connected to ZAPTesting database');
        
        // Create random numbers table
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS random_numbers_data (
                id SERIAL PRIMARY KEY,
                value_a NUMERIC(10,2),
                value_b NUMERIC(10,2),
                value_c NUMERIC(10,2),
                value_d NUMERIC(10,2),
                value_e NUMERIC(10,2),
                is_positive BOOLEAN,
                category VARCHAR(20),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `;
        
        await client.query(createTableQuery);
        console.log('Random numbers table created successfully');
        
        // Clear existing data
        await client.query('DELETE FROM random_numbers_data');
        
        // Generate random data using PostgreSQL functions
        const insertRandomDataQuery = `
            INSERT INTO random_numbers_data (value_a, value_b, value_c, value_d, value_e, is_positive, category)
            SELECT 
                ROUND((random() * 1000)::numeric, 2) as value_a,
                ROUND((random() * 500 - 250)::numeric, 2) as value_b,
                ROUND((random() * 100)::numeric, 2) as value_c,
                ROUND((random() * 50)::numeric, 2) as value_d,
                ROUND((random() * 200)::numeric, 2) as value_e,
                (random() > 0.5) as is_positive,
                CASE 
                    WHEN random() < 0.33 THEN 'Category A'
                    WHEN random() < 0.66 THEN 'Category B'
                    ELSE 'Category C'
                END as category
            FROM generate_series(1, 20)
        `;
        
        await client.query(insertRandomDataQuery);
        console.log('Random data inserted successfully');
        
        // Display the generated data
        const selectQuery = 'SELECT * FROM random_numbers_data ORDER BY id';
        const result = await client.query(selectQuery);
        
        console.log('\n=== Generated Random Data ===');
        result.rows.forEach(row => {
            console.log(`ID: ${row.id}, A: ${row.value_a}, B: ${row.value_b}, C: ${row.value_c}, D: ${row.value_d}, E: ${row.value_e}, Positive: ${row.is_positive}, Category: ${row.category}`);
        });
        
        console.log(`\nTotal records generated: ${result.rows.length}`);
        
    } catch (err) {
        console.error('Database operation failed:', err.message);
        
    } finally {
        await client.end();
        console.log('Database connection closed');
    }
}

// Execute with ZCube environment check
if (typeof ZAppend === 'function') {
    ZAppend(windowId, "Starting random data generation...");
    generateRandomData();
} else {
    console.log("Running in non-ZCube environment");
    generateRandomData();
}

Example for performing operations on the table data

const windowId = "8Space"; 

const originalConsoleLog = console.log;

// Override console.log to also append to the ZCube window
console.log = function(...args) {
    const message = args.map(arg =>
        typeof arg === 'object' ? JSON.stringify(arg) : String(arg)
    ).join(' ');
    originalConsoleLog.apply(console, args);
    if (typeof ZAppend === "function") {
        ZAppend(windowId, message);
    }
};

const fs = require("fs");
const pg = require("pg");

const config = {
    user: "postgres",
    password: "root", 
    host: "localhost",
    port: "5432",
    database: "ZAPTesting",
    ssl: false,
    connectionTimeoutMillis: 5000,
    query_timeout: 10000
};


Array.prototype.calci = function() {
    const data = this;
    
    return {
        table: data,
        

        parseRange: function(range) {
            if (range.includes(':')) {
                const [start, end] = range.split(':');
                const startCoords = this.cellToCoords(start);
                const endCoords = this.cellToCoords(end);
                
                const result = [];
                for (let row = startCoords.row; row <= endCoords.row; row++) {
                    const rowData = [];
                    for (let col = startCoords.col; col <= endCoords.col; col++) {
                        if (data[row] && data[row][col] !== undefined) {
                            rowData.push(data[row][col]);
                        } else {
                            rowData.push(null);
                        }
                    }
                    result.push(rowData);
                }
                return result;
            } else {
                const coords = this.cellToCoords(range);
                return data[coords.row] ? data[coords.row][coords.col] : null;
            }
        },
        
        cellToCoords: function(cell) {
            const match = cell.match(/([A-Z]+)(\d+)/);
            if (!match) {
                throw new Error('Invalid cell reference: ' + cell);
            }
            
            const col = this.columnToNumber(match[1]) - 1;
            const row = parseInt(match[2]) - 1;
            
            return { row, col };
        },
        
        columnToNumber: function(column) {
            let result = 0;
            for (let i = 0; i < column.length; i++) {
                result = result * 26 + (column.charCodeAt(i) - 'A'.charCodeAt(0) + 1);
            }
            return result;
        },
        
        // Flatten range data for global SUM function
        flattenRange: function(range) {
            const rangeData = this.parseRange(range);
            const flattened = [];
            
            if (Array.isArray(rangeData[0])) {
                for (let row of rangeData) {
                    for (let cell of row) {
                        flattened.push(cell);
                    }
                }
            } else {
                flattened.push(rangeData);
            }
            
            return flattened;
        },
        
        display: function() {
            console.log('\n--- CALCI Spreadsheet ---');
            data.forEach((row, index) => {
                console.log(`Row ${index + 1}:`, row);
            });
            console.log('-------------------------\n');
        }
    };
};


const createCalciOperationsTable = async () => {
    const client = new pg.Client(config);
    
    try {
        await client.connect();
        console.log('Connected to ZAPTesting database');
        
        const createTableQuery = `
            CREATE TABLE IF NOT EXISTS calci_operations (
                id SERIAL PRIMARY KEY,
                operation_name VARCHAR(50) NOT NULL,
                formula TEXT NOT NULL,
                input_data JSONB,
                result NUMERIC,
                cell_range VARCHAR(50),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `;
        
        await client.query(createTableQuery);
        console.log('CALCI operations table created successfully');
        
        const sampleData = [
            {
                operation: 'SUM_NUMBERS',
                formula: 'SUM(1,2,3)',
                data: [1, 2, 3],
                range: 'A1:A3'
            },
            {
                operation: 'SUM_MIXED',
                formula: 'SUM(5,-9,45)',
                data: [5, -9, 45],
                range: 'B1:B3'
            },
            {
                operation: 'SUM_WITH_BOOLEAN',
                formula: 'SUM(10,TRUE,FALSE,20)',
                data: [10, true, false, 20],
                range: 'C1:C4'
            },
            {
                operation: 'SUM_WITH_TEXT',
                formula: 'SUM(15,"text",25,30)',
                data: [15, "text", 25, 30],
                range: 'D1:D4'
            }
        ];
        
        const spreadsheetData = [
            ['Value1', 'Value2', 'Value3', 'Value4'],
            [1, 5, 10, 15],
            [2, -9, true, "text"],
            [3, 45, false, 25],
            [null, null, 20, 30]
        ];
        
        const calci = spreadsheetData.calci();
        calci.display();
        
        console.log('=== CALCI Operations using Global SUM ===');
        

        for (let sample of sampleData) {
            let result;
            if (sample.operation === 'SUM_NUMBERS') {
                result = SUM(1, 2, 3);  // Global SUM
            } else if (sample.operation === 'SUM_MIXED') {
                result = SUM(5, -9, 45);  // Global SUM
            } else if (sample.operation === 'SUM_WITH_BOOLEAN') {
                result = SUM(10, true, false, 20);  // Global SUM
            } else if (sample.operation === 'SUM_WITH_TEXT') {
                result = SUM(15, "text", 25, 30);  // Global SUM
            }
            
            console.log(`${sample.formula} = ${result}`);
            
            const insertQuery = `
                INSERT INTO calci_operations (operation_name, formula, input_data, result, cell_range)
                VALUES ($1, $2, $3, $4, $5)
            `;
            
            await client.query(insertQuery, [
                sample.operation,
                sample.formula,
                JSON.stringify(sample.data),
                result,
                sample.range
            ]);
        }
        
        console.log('\n=== Range-based Operations using Global SUM ===');
        
        const rangeA2A4 = calci.flattenRange('A2:A4');
        const sumA2A4 = SUM(...rangeA2A4);  // Global SUM with spread operator
        console.log('SUM(A2:A4) =', sumA2A4);
        
        const rangeB2B4 = calci.flattenRange('B2:B4');
        const sumB2B4 = SUM(...rangeB2B4);  // Global SUM
        console.log('SUM(B2:B4) =', sumB2B4);
        
        const rangeC2C4 = calci.flattenRange('C2:C4');
        const sumC2C4 = SUM(...rangeC2C4);  // Global SUM
        console.log('SUM(C2:C4) =', sumC2C4);
        
        const rangeOperations = [
            { name: 'SUM_RANGE_A2A4', formula: 'SUM(A2:A4)', result: sumA2A4, range: 'A2:A4' },
            { name: 'SUM_RANGE_B2B4', formula: 'SUM(B2:B4)', result: sumB2B4, range: 'B2:B4' },
            { name: 'SUM_RANGE_C2C4', formula: 'SUM(C2:C4)', result: sumC2C4, range: 'C2:C4' }
        ];
        
        for (let op of rangeOperations) {
            await client.query(
                'INSERT INTO calci_operations (operation_name, formula, result, cell_range) VALUES ($1, $2, $3, $4)',
                [op.name, op.formula, op.result, op.range]
            );
        }
        
        const allOperations = await client.query('SELECT * FROM calci_operations ORDER BY id');
        
        console.log('\n=== Stored CALCI Operations ===');
        allOperations.rows.forEach(row => {
            console.log(`ID: ${row.id}, Operation: ${row.operation_name}, Formula: ${row.formula}, Result: ${row.result}`);
        });
        
        console.log('\nCALCI operations using global SUM completed successfully!');
        
    } catch (err) {
        console.error('Database operation failed:', err.message);
        throw err;
        
    } finally {
        await client.end();
        console.log('Database connection closed');
    }
};

createCalciOperationsTable()
    .then(() => {
        console.log('CALCI implementation using global SUM completed');
    })
    .catch(err => {
        console.error('Error:', err.message);
    });

Output Saved in the Table

Troubleshooting

• Connection errors: Double-check your credentials and network settings.
• Missing client libraries: Ensure pg or your chosen client is installed in the correct directory.
• Output not visible: Verify your ZCubes window ID and that ZAppend is available in your environment.
• Permissions: Ensure your database user has the necessary privileges.

See also

• Z3
• ZAP
• https://www.postgresql.org/

---

Note: ZAP in ZCubes is a desktop application and not a database itself. This guide is for integrating ZAP Desktop with external databases for enhanced data-driven workflows.