tdcmeehan
commented
2 months ago @nikhilsinghkushwah would it be possible to include end to end tests with the introduction of this connector?
Open nikhilsinghkushwah opened 2 months ago
tdcmeehan
commented
2 months ago @nikhilsinghkushwah would it be possible to include end to end tests with the introduction of this connector?
elharo
commented
2 months ago I agree this would be useful. Sybase is important in many organizations.
Does this have to be in the core repo for some reason or can it be published as an independent project? The latter could be much faster since it wouldn't require constant code reviews and approvals from Presto maintainers.
nikhilsinghkushwah
commented
2 months ago @nikhilsinghkushwah would it be possible to include end to end tests with the introduction of this connector?
@tdcmeehan i have added standard test cases from connector perspective. Let me know if any other details are required.
tdcmeehan
commented
2 months ago @nikhilsinghkushwah If we want to include this in the main repo, we'll need to include end to end tests that run against some version of Sybase. Unit test coverage of the various components won't be enough, we'll need integration style tests that prove query execution works end to end. Are you able to add such tests?
nikhilsinghkushwah
commented
2 months ago @tdcmeehan Added integration test for end to end connector functionality.
Feature Request
Feature Description: A connector for Sybase (SAP ASE) database in PrestoDB to enable querying data from Sybase catalogs directly using Presto.
Use Case: Sybase is widely used in many legacy and enterprise applications. Adding a connector would allow users to query data stored in Sybase using Presto, benefiting organizations with mixed database environments, and providing seamless integration for data analysis alongside other supported databases (e.g., MySQL, PostgreSQL, Oracle).
Proposed Solution: The connector would utilize the Sybase JDBC driver to interact with the database. It would support basic querying, metadata retrieval (schemas, tables, columns), and split management for parallel query execution. Key components would include:
Potential Challenges:
Alternatives:
Additional Context: The Presto community would greatly benefit from the inclusion of this connector to enhance the platform’s ability to work with Sybase. We would be happy to contribute to its development or collaborate with maintainers to bring this feature to fruition.
Related Issues or Discussions: N/A
Test cases These test cases aim to cover the critical functionalities of the connector without delving into too much technical detail:
Connector Initialization and Configuration Test Case: Validate Successful Initialization Description: Ensure that the Sybase connector initializes correctly with valid configuration parameters (JDBC URL, credentials, etc.). Expected Outcome: Connector is initialized without errors, and queries can be executed. Test Case: Invalid Configuration Handling Description: Test the behavior of the connector when provided with incorrect JDBC URL, username, or password. Expected Outcome: The connector fails to initialize, and appropriate error messages are logged.
Schema and Table Metadata Retrieval Test Case: Retrieve Available Schemas Description: Ensure that the connector retrieves the list of available schemas from the Sybase database. Expected Outcome: A list of schemas is returned. Test Case: Retrieve Tables in a Schema Description: Validate that the connector can list all tables within a specific schema. Expected Outcome: A list of tables is returned. Test Case: Retrieve Table Column Metadata Description: Ensure that the connector retrieves column names and data types for a given table. Expected Outcome: Column names and their respective data types are returned.
Basic Data Querying Test Case: Execute Basic SELECT Query Description: Test if the connector can retrieve data from a Sybase table using a simple SELECT * query. Expected Outcome: All rows from the table are returned successfully. Test Case: Filter Data Using WHERE Clause Description: Validate that the connector can apply filtering conditions in queries. Expected Outcome: Only rows that match the WHERE clause are returned. Test Case: Aggregate Data (COUNT, SUM, AVG) Description: Ensure the connector supports aggregate functions such as COUNT, SUM, and AVG. Expected Outcome: Aggregated results are returned as expected.
Join Operations Test Case: Join Between Two Tables Description: Ensure that the connector can perform joins between two Sybase tables. Expected Outcome: The correct result set with data from both tables is returned.
Data Type Handling Test Case: Handle Various Data Types Description: Test the connector’s ability to handle various data types (e.g., INTEGER, VARCHAR, DATE, FLOAT, DECIMAL). Expected Outcome: Data of all types is retrieved correctly without type mismatch or errors.
Error Handling Test Case: Invalid SQL Query Handling Description: Test the connector's response to invalid SQL queries. Expected Outcome: The connector returns an appropriate error message. Test Case: Querying Non-Existent Table Description: Validate that the connector properly handles querying a non-existent table. Expected Outcome: An error message indicating that the table does not exist is returned.
Performance and Scalability Test Case: Query Large Tables Description: Test the connector’s ability to handle large tables with millions of rows. Expected Outcome: The query should complete successfully within a reasonable time. Test Case: Parallel Query Execution Description: Ensure that the connector supports parallel query execution to optimize performance for large datasets. Expected Outcome: The query is executed in parallel, and results are returned faster compared to a non-parallel execution.
Security and Permissions Test Case: Access Control Based on User Permissions Description: Test that the connector respects user-level permissions in Sybase. Expected Outcome: Users are only able to access tables and schemas they have permissions for, and unauthorized access attempts result in error messages.
Transactional Behavior Test Case: Transaction Support (BEGIN, COMMIT, ROLLBACK) Description: Test the connector’s handling of transactions. Expected Outcome: Transactions are correctly committed or rolled back based on user operations.
Timeout and Resource Management Test Case: Handle Long-Running Queries Description: Validate the connector’s handling of long-running queries and its ability to time out appropriately. Expected Outcome: The query times out after the configured time period, and an appropriate message is returned.
Integration Tests: Test Case: Aggregation Query with SUM and COUNT Description: Validate that the connector can handle aggregation functions such as SUM and COUNT. Ensure the table salary_table contains salary data with a department column. Query: SELECT department, SUM(salary), COUNT(*) FROM sybase.test_schema.salary_table GROUP BY department; Expected Outcome: The query should return the total salary and the number of rows per department.
Test Case: Join Between Two Tables Description: Test that the connector can handle JOIN operations between two tables in Sybase. Create two tables, employees and departments, with a relationship between them via the department_id. Query: SELECT e.id, e.name, d.department_name FROM sybase.test_schema.employees e JOIN sybase.test_schema.departments d ON e.department_id = d.department_id; Expected Outcome: The query should return joined data with id, name, and department_name.
Test Case: Handling Different Data Types Description: Ensure the connector correctly retrieves data of different types, including INTEGER, VARCHAR, DATE, and DECIMAL. Create a table data_type_table in Sybase with different data types (id, name, salary, joining_date). Query: SELECT id, name, salary, joining_date FROM sybase.test_schema.data_type_table; Expected Outcome: The query should return all data with correct formatting, ensuring that each data type is properly handled (e.g., salary as a DECIMAL, date as DATE).
Test Case: Insert Data and Verify Description: Test that the connector allows data insertion into Sybase and that the inserted data can be retrieved. Ensure the target table test_table exists with appropriate columns. Query: INSERT INTO sybase.test_schema.test_table (id, name, age) VALUES (100, 'John Doe', 40); Follow-up Query: SELECT * FROM sybase.test_schema.test_table WHERE id = 100; Expected Outcome: The SELECT query should return the newly inserted row (100, John Doe, 40), proving that the insertion and retrieval work end-to-end.
Test Case: Transaction Handling (COMMIT and ROLLBACK) Description: Validate that transactions are handled correctly, including BEGIN, COMMIT, and ROLLBACK. Ensure a table transaction_test exists. Query: Begin a transaction: START TRANSACTION; Insert a new row: INSERT INTO sybase.test_schema.transaction_test (id, value) VALUES (200, 'Test'); Rollback the transaction: ROLLBACK; Verify that the row was not inserted: SELECT * FROM sybase.test_schema.transaction_test WHERE id = 200; Expected Outcome: The SELECT query should return no rows, confirming that the rollback was successful, and the insertion was not committed.
Test Case: Handle NULL Values Description: Ensure that the connector properly handles and returns NULL values in the result set. Insert rows with some NULL values into the test_table. Query: SELECT id, name, age FROM sybase.test_schema.test_table WHERE age IS NULL; Expected Outcome: The query should return only the rows where the age column is NULL.
Test Case: Query Large Data Set Description: Validate that the connector can handle querying large tables with millions of rows. Populate a large table large_table in Sybase with a substantial number of rows (e.g., 1 million). Query: SELECT COUNT(*) FROM sybase.test_schema.large_table; Expected Outcome: The query should successfully return the count of rows, proving that large result sets are handled efficiently.
Test Case: Error Handling for Invalid Queries Description: Ensure that the connector appropriately handles invalid queries and returns relevant error messages. Query: SELECT * FROM sybase.test_schema.non_existent_table; Expected Outcome: The query should return an error indicating that the table does not exist, and the error message should be clear and descriptive.
Test Case: Query with LIMIT Clause Description: Verify that the LIMIT clause works correctly and limits the number of returned rows. Ensure test_table contains multiple rows. Query: SELECT * FROM sybase.test_schema.test_table LIMIT 5; Expected Outcome: The query should return exactly 5 rows, regardless of how many rows are in the table.
Test Case: Handling Case-Sensitivity Description: Ensure that the connector properly handles case sensitivity for table and column names as per Sybase's rules. Create a table with mixed-case column names, e.g., MixedCaseTable. Query: SELECT "MixedColumn" FROM sybase.test_schema.MixedCaseTable; Expected Outcome: The query should return the data from the column with the exact case specified, confirming that case sensitivity is handled correctly.
Test Case: Delete Data Description: Ensure that the connector supports DELETE operations and the deleted data is no longer accessible. Ensure that test_table has data for deletion. Query: DELETE FROM sybase.test_schema.test_table WHERE id = 100; Follow-up Query: SELECT * FROM sybase.test_schema.test_table WHERE id = 100; Expected Outcome: The DELETE query should remove the specified row, and the follow-up SELECT query should return no results for that id.