Virajp at 20:31, 12 June 2025

2025-06-12T20:31:38Z

Virajp at 20:29, 12 June 2025

2025-06-12T20:29:09Z

Virajp at 19:19, 12 June 2025

2025-06-12T19:19:51Z

Virajp: Created page with "= ZAP Integration with Database = '''ZAP (ZCubes Advanced Platform)''' is a desktop application within the ZCubes ecosystem, providing a powerful suite for computation, progr..."

2025-06-12T00:26:50Z

Created page with "= ZAP Integration with Database = '''ZAP (ZCubes Advanced Platform)''' is a desktop application within the ZCubes ecosystem, providing a powerful suite for computation, progr..."

New page

= 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., <code>pg</code> for PostgreSQL).

== Installation Steps ==

=== 1. Install ZAP Desktop ===
* Download ZAP Desktop from the [https://downloads.zcubes.com/ 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., <code>ZAPTesting</code>) 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 [https://nodejs.org nodejs.org].
* Choose or create a directory for your ZAP project, for example: <code>D:/ZAP_Testing</code>
* Open a terminal or command prompt and run:
<syntaxhighlight lang="bash">
npm install pg --prefix D:/ZAP_Testing
</syntaxhighlight>
* This will install the <code>pg</code> library and its dependencies into <code>D:/ZAP_Testing/node_modules</code>.

* (Optional) If you want to manage dependencies with a <code>package.json</code>, run:
<syntaxhighlight lang="bash">
cd D:/ZAP_Testing
npm init
npm install pg
</syntaxhighlight>

* In your ZAP Desktop code block (before requiring the npm package), add:
<syntaxhighlight lang="z^3">
AddToModuleSearchPath("D:/ZAP_Testing/node_modules")
</syntaxhighlight>
[[File:29.png|thumb]]
* 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 <code>ZAppend</code>.

<syntaxhighlight lang="javascript">
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();
}

</syntaxhighlight>

Output:
<pre>
Testing PostgreSQL connection...

Connection successful to ZAPTesting database

Connection closed
</pre>

=== 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 <code>ZAppend</code> or similar functions.

===Example for creating the database:===
[[File:24.png|thumb]]
[[File:25.png|thumb]]
[[File:26.png|thumb]]
[[File:27.png|thumb]]
[[File:28.png|thumb]]
<syntaxhighlight lang="javascript">
// --- 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();
}
</syntaxhighlight>

===Example for performing operations on the table data===
[[File:21.png|thumb]]
[[File:22.png|thumb]]
<syntaxhighlight lang="javascript">
// --- 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);
});

</syntaxhighlight>

==Output Saved in the Table==
[[File:30.png|center|600px|Output Saved in the Table]]

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

== See also ==
* [[ZCubes]]
* [[ZAP (ZCubes Advanced Platform)]]
* [[PostgreSQL]]
* [ZCubes Documentation]

[[Category:ZCubes]]
[[Category:ZAP]]
[[Category:Database Integration]]
[[Category:System integration]]

----

''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.''

← Older revision		Revision as of 20:31, 12 June 2025
Line 424:		Line 424:
	calci.display();		calci.display();

−	~~console.log('=== CALCI Operations using Global SUM ===');~~

@@ Line 575: / Line 575: @@
 == See also ==
-* [[ZCubes]]
+* [[Z3]]
-* [[ZAP (ZCubes Advanced Platform)]]
+* [[ZAP]]
-* [[PostgreSQL]]
+* https://www.postgresql.org/
 [[Category:ZCubes]]

ZAP connection with DB - Revision history

Virajp at 20:31, 12 June 2025

Virajp at 20:29, 12 June 2025

Virajp at 19:19, 12 June 2025

Virajp: Created page with "= ZAP Integration with Database = '''ZAP (ZCubes Advanced Platform)''' is a desktop application within the ZCubes ecosystem, providing a powerful suite for computation, progr..."