Integration of DAPHNE with database systems: sql() on an existing database

**Motivation:** Users often store their data in relational database management systems (DBMSs). In order to process this DBMS-resident data in DAPHNE, one would currently have to extract some data through SQL queries on the database, store it in a file in some open format (e.g., CSV), and read that file and continue the processing in DAPHNE. This approach is (1) cumbersome as it requires the manual or scripted invocation of the DBMS and DAPHNE, and (2) inefficient as the query results are exchanged via secondary storage. DAPHNE already supports SQL queries on data frames. However, these queries can only be executed over data residing in DAPHNE (or imported from Python libraries like pandas through DaphneLib, DAPHNE’s Python API).

**Task:** The goal of this project is to enable DAPHNE to efficiently run SQL queries specified in DaphneDSL over existing database instances of widely-used DBMSs and to evaluate the efficiency.

- DaphneDSL’s `sql()` built-in function currently accepts an SQL query over previously registered DAPHNE frames. The query gets lowered to DaphneIR operations from relational and linear algebra and the processing takes place in DAPHNE. For this project, the `sql()` built-in function should be extended such that additional information on the existing database can be specified. If such information is given, `sql()` should not be lowered to individual operations, but ultimately to a pre-compiled C++ kernel that connects to the specified database, submits the query, receives the result, and makes it available as a DAPHNE frame for further processing in DAPHNE.
- Target widely-used open-source DBMSs such as MonetDB, DuckDB, PostreSQL, MySQL, and SQLite.
- Existing DBMSs can be integrated in different ways. Ideally, we would have a generic solution that can support various DBMS, e.g., by using ODBC. Alternative approaches include (a) invoking the client applications of the respective DBMS as an external program (and perhaps capture the query results through pipes or other forms of inter-process communication), (b) using DBMS-specific C/C++ APIs, or (c) linking the DBMS itself into DAPHNE (for embedded DBMSs like DuckDB and SQLite). These approaches can have different trade-offs regarding genericity, implementation complexity, and performance of the data transfer.
- Errors in the existing DBMS should be handled gracefully. For instance, if the SQL query refers to non-existing tables, DAPHNE should throw an exception, but not crash in an uncontrolled way.
- Design and conduct experiments that investigate the performance of query processing in various DBMSs through DAPHNE’s `sql()` built-in function. The efficiency of the query processing itself and that of the result data transfer are of particular interest. If you devised different techniques for making these steps efficient, show their effectiveness in the experiments.

**Hints:**
- Set up your DAPHNE development environment and run the test suite.
- Read and follow the contribution guidelines.
- Understand how DaphneDSL’s `sql()` built-in functions currently work. To that end, have a look at the [online documentation](https://daphne-eu.github.io/daphne/) and the relevant source code (e.g., in the DaphneDSL parser `src/parser/daphnedsl/DaphneDSLBuiltins.cpp`, to compiler pass that rewrites DaphneIR’s `SqlOp` to individual operations from relational/linear algebra `src/compiler/lowering/RewriteSqlOpPass.cpp`, DAPHNE’s SQL parser `src/parser/sql/SQLParser.cpp`)
- Understand the DAPHNE compilation chain (see DAPHNE EU-project deliverables [D3.4 "Compiler Design and Overview"](http://daphne-eu.eu/wp-content/uploads/2023/12/D3.4-Compiler-Design-and-Overview-.pdf) and [D3.3 "Extended Compiler Prototype"](http://daphne-eu.eu/wp-content/uploads/2023/05/DAPHNE_D3.3_ExtendedCompilerPrototype_v1.1.pdf)).
- Read up on the DBMSs you decide to support, especially on their client interfaces.
- Further processing the query results in DAPHNE may require type inference at compile-time. As the database schema in the existing DBMS will be a black box to the DAPHNE compiler, you could consider running SQL `EXPLAIN` queries on the existing DBMS in the DAPHNE compiler to find out the schema (column names and types) of the query result before executing the query at DAPHNE run-time.
- If you want to use third-party software (libraries etc.), note that their software license must be compatible with DAPHNE’s.
- All new features should be easily maintainable, i.e., they should be (1) covered by meaningful test cases, and (2) documented (developer docs page explaining the overall design and source code comments).
- The contributions made in the context of this project can be split up in multiple meaningful pull requests.

Start by reading the current sql() handling in src/parser/daphnedsl/DaphneDSLBuiltins.cpp, src/compiler/lowering/RewriteSqlOpPass.cpp, and src/parser/sql/SQLParser.cpp, then run the test suite. Define the database connection and result-flow design, add meaningful tests and developer documentation, and evaluate query and result-transfer performance across the selected DBMSs.

Written by the indexing model from the issue text.