JavaScript Property Access in SQL - Object Property Navigation

[mathiasrw]

Priority: 1 (Critical)
Impact: SQL-99 Compliance & JavaScript Integration
Test File: test/test341.js
Test Location: Lines 38, 44
Test Names: "4. JavaScript way", "5. JavaScript way"

Problem Description

The tests test341.js lines 38 and 44 contain skipped tests for JavaScript property access within SQL queries. These tests verify that AlaSQL can access JavaScript object properties and methods directly in SQL statements, providing seamless JavaScript integration.

Specific Test Cases

// Test 4: JavaScript way (line 38)
it.skip('4. JavaScript way', function (done) {
    var res = alasql('SET @a = "who".length');
    assert.deepEqual(res, [6, 6, 7]);
    done();
});

// Test 5: JavaScript way (line 44)
it.skip('5. JavaScript way', function (done) {
    var res = alasql('SELECT COLUMN name.length FROM persons');
    assert.deepEqual(res, [6, 6, 7]);
    done();
});

Expected Behavior

These tests should verify:

1. JavaScript Expression Evaluation: Ability to evaluate JavaScript expressions in SQL
2. Property Access: Access object properties using dot notation
3. Method Calls: Call JavaScript methods on objects/strings
4. Variable Assignment: Set variables using JavaScript expressions
5. Column Property Access: Access properties of column values in SELECT statements

Current Status

• Test Status: Skipped (it.skip)
• Error: Unknown (test not executed)
• Root Cause: JavaScript property access in SQL not implemented

Steps to Reproduce

1. Navigate to test/test341.js
2. Change it.skip('4. JavaScript way' to it('4. JavaScript way'
3. Change it.skip('5. JavaScript way' to it('5. JavaScript way'
4. Run the test with: yarn test-only -- test/test341.js
5. Tests will fail with parse errors or unexpected results

Implementation Requirements

1. Parser Support: Add JavaScript expression parsing in SQL statements
2. Property Access: Implement dot notation for object property access
3. Method Calls: Support JavaScript method calls on expressions
4. Variable System: Enhance variable system to handle JavaScript expressions
5. Context Handling: Properly handle JavaScript context in SQL execution

SQL-99 Features Involved

• SQL/JSON path expressions (similar concept)
• SQL language extensions for JavaScript integration
• Variable assignment and expression evaluation

Dependencies

• AlaSQL parser (src/alasqlparser.jison)
• JavaScript expression evaluator
• Variable management system
• Object property access mechanism
• Test database setup with sample data

Acceptance Criteria

Test 4: JavaScript way

• Test is enabled (remove it.skip)
• Parser recognizes JavaScript expressions in SET statements
• String property access works (.length)
• Variable assignment with JavaScript expressions works
• Expected result [6, 6, 7] is returned

Test 5: JavaScript way

• Test is enabled (remove it.skip)
• Parser recognizes JavaScript expressions in SELECT statements
• Column property access works in SELECT COLUMN
• Expected result [6, 6, 7] is returned for name lengths
• Integration with COLUMN keyword works properly

Test Implementation Suggestions

// Test 4: JavaScript way
it('4. JavaScript way', function (done) {
    var res = alasql('SET @a = "who".length');
    // Should return the length of the string "who"
    assert.deepEqual(res, [3]); // Note: Expected result may need clarification
    done();
});

// Test 5: JavaScript way
it('5. JavaScript way', function (done) {
    // Setup test data
    alasql('CREATE TABLE persons (name STRING)');
    alasql('INSERT INTO persons VALUES ("Andrey"), ("Valery"), ("Michael")');
    
    var res = alasql('SELECT COLUMN name.length FROM persons');
    assert.deepEqual(res, [6, 6, 7]); // Lengths of the names
    done();
});

Parser Changes Needed

In src/alasqlparser.jison, add support for:

• JavaScript expression parsing in various contexts
• Dot notation property access
• Method call syntax
• Expression evaluation in SET and SELECT statements

Security Considerations

JavaScript expression evaluation should:

• Be sandboxed properly
• Have limited scope for security
• Not allow arbitrary code execution
• Be configurable via options

Notes

These tests are critical for AlaSQL's JavaScript integration capabilities. The ability to access JavaScript properties and methods within SQL provides powerful functionality for data manipulation and transformation.

[mathiasrw]

Are we sure this is needed?