ZAP connection with DB

From ZCubes Wiki
Revision as of 13:19, 12 June 2025 by Virajp (talk | contribs)
Jump to navigation Jump to search

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")
29.png
  • 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:

24.png
25.png
26.png
27.png
28.png 
// --- 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

21.png
22.png 
// --- ZAppend Console Log Redirect ---
const windowId = "8Space"; // <-- Set your ZCube window ID here

// Save the original console.log
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);
    }
};

// --- Your CALCI SUM code starts here ---
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
};

// Enhanced Array prototype with CALCI SUM functionality
Array.prototype.calci = function() {
    const data = this;
    
    return {
        table: data,
        
        // CALCI SUM function implementation
        SUM: function(...args) {
            let total = 0;
            
            for (let arg of args) {
                if (typeof arg === 'string' && arg.includes(':')) {
                    // Handle range like A1:A3
                    const rangeValues = this.parseRange(arg);
                    total += this.sumArray(rangeValues);
                } else if (typeof arg === 'string') {
                    // Handle single cell like A1
                    const cellValue = this.parseRange(arg);
                    total += this.convertToNumber(cellValue);
                } else if (Array.isArray(arg)) {
                    // Handle array input
                    total += this.sumArray(arg);
                } else {
                    // Handle direct number
                    total += this.convertToNumber(arg);
                }
            }
            
            return total;
        },
        
        // Helper function to sum array values
        sumArray: function(arr) {
            let sum = 0;
            if (Array.isArray(arr[0])) {
                // 2D array (range result)
                for (let row of arr) {
                    for (let cell of row) {
                        sum += this.convertToNumber(cell);
                    }
                }
            } else {
                // 1D array
                for (let cell of arr) {
                    sum += this.convertToNumber(cell);
                }
            }
            return sum;
        },
        
        // Convert value to number following CALCI rules
        convertToNumber: function(value) {
            if (value === null || value === undefined || value === '') {
                return 0; // Empty cells ignored
            }
            if (value === true) return 1; // TRUE counts as 1
            if (value === false) return 0; // FALSE counts as 0
            if (typeof value === 'string') {
                const num = parseFloat(value);
                return isNaN(num) ? 0 : num; // Text ignored (returns 0)
            }
            return typeof value === 'number' ? value : 0;
        },
        
        // Parse range notation (A1:B3) and return 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;
            }
        },
        
        // Convert cell notation (A1) to coordinates
        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 };
        },
        
        // Convert column letters to numbers (A=1, B=2, etc.)
        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;
        },
        
        // Display table
        display: function() {
            console.log('\n--- CALCI Spreadsheet ---');
            data.forEach((row, index) => {
                console.log(`Row ${index + 1}:`, row);
            });
            console.log('-------------------------\n');
        }
    };
};

// Main function to create CALCI operations table
const createCalciOperationsTable = async () => {
    const client = new pg.Client(config);
    
    try {
        await client.connect();
        console.log('Connected to ZAPTesting database');
        
        // Create CALCI operations table
        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');
        
        // Insert sample CALCI data for SUM operations
        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'
            }
        ];
        
        // Create spreadsheet data and perform CALCI operations
        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 SUM Operations ===');
        
        for (let sample of sampleData) {
            // Perform CALCI SUM operation
            let result;
            if (sample.operation === 'SUM_NUMBERS') {
                result = calci.SUM(1, 2, 3);
            } else if (sample.operation === 'SUM_MIXED') {
                result = calci.SUM(5, -9, 45);
            } else if (sample.operation === 'SUM_WITH_BOOLEAN') {
                result = calci.SUM(10, true, false, 20);
            } else if (sample.operation === 'SUM_WITH_TEXT') {
                result = calci.SUM(15, "text", 25, 30);
            }
            
            console.log(`${sample.formula} = ${result}`);
            
            // Insert operation result into database
            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
            ]);
        }
        
        // Demonstrate range-based SUM operations
        console.log('\n=== Range-based SUM Operations ===');
        
        // SUM(A2:A4) - Sum first column numbers
        const sumA2A4 = calci.SUM('A2:A4');
        console.log('SUM(A2:A4) =', sumA2A4);
        
        // SUM(B2:B4) - Sum second column (including negative)
        const sumB2B4 = calci.SUM('B2:B4');
        console.log('SUM(B2:B4) =', sumB2B4);
        
        // SUM(C2:C4) - Sum with boolean values
        const sumC2C4 = calci.SUM('C2:C4');
        console.log('SUM(C2:C4) =', sumC2C4);
        
        // Insert range operations
        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]
            );
        }
        
        // Query and display all operations
        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 SUM operations completed successfully!');
        
    } catch (err) {
        console.error('Database operation failed:', err.message);
        throw err;
        
    } finally {
        await client.end();
        console.log('Database connection closed');
    }
};

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

Output Saved in the Table

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

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.

Retrieved from "http://wiki.zcubes.com/index.php?title=ZAP_connection_with_DB&oldid=217772"