YugabyteDB Distributed Connector

A modern, AI-powered C++ library for connecting to and interacting with YugabyteDB (PostgreSQL-compatible distributed database), featuring a beautiful web-based frontend with advanced AI capabilities.

🌟 Features

• Distributed Database Connectivity: Robust connection management with connection pooling
• Full CRUD Support: Execute SELECT, INSERT, UPDATE, DELETE operations with ease
• AI-Powered Query Builder: Convert natural language to SQL queries
• Query Optimization: AI-driven query optimization suggestions and performance analysis
• Beautiful Web Interface: Modern, responsive UI with real-time data visualization
• RESTful API: Clean REST API for programmatic access
• Performance Monitoring: Track query execution times and resource usage
• Transaction Support: Safe multi-query transaction handling

🏗️ Architecture

Backend Components (C++)

• YBConnector: Manages database connections with connection pooling
• YBQueryExecutor: Handles query execution and parameter binding
• YBResultSet: Provides convenient iteration over query results
• YBException: Comprehensive exception hierarchy for error handling
• APIServer: REST API server built with Poco::Net
• AIQueryGenerator: Natural language to SQL conversion
• QueryOptimizer: Query performance analysis and optimization suggestions

Frontend Components (Web)

• Modern UI: Gradient design with smooth animations
• SQL Editor: Syntax-highlighted code editor powered by CodeMirror
• AI Assistant: Natural language query interface
• Data Visualization: Interactive charts and graphs with Chart.js
• Query Optimizer Panel: Real-time optimization recommendations
• Performance Dashboard: Monitor query metrics and history

📋 Prerequisites

Software Requirements

1. CMake (3.14 or higher)

   # Windows (using Chocolatey)
   choco install cmake
   
   # Or download from https://cmake.org/download/

2. C++ Compiler with C++14 support

   • Visual Studio 2019 or later (Windows)
   • GCC 5.0+ or Clang 3.8+ (Linux/Mac)

3. Poco C++ Libraries (1.9.0 or higher)

   Installing Poco on Windows:

   Option 1: Using vcpkg (Recommended)

   # Install vcpkg if not already installed
   git clone https://github.com/Microsoft/vcpkg.git
   cd vcpkg
   .\bootstrap-vcpkg.bat
   
   # Install Poco
   .\vcpkg install poco:x64-windows
   
   # Integrate with Visual Studio
   .\vcpkg integrate install

   Option 2: Manual Installation

   1. Download from https://pocoproject.org/download.html
   2. Follow installation instructions for your platform

4. YugabyteDB (latest stable version)

   Installing YugabyteDB:

   Windows (using Docker - Recommended)

   docker run -d --name yugabytedb_node1 -p7000:7000 -p9000:9000 -p5433:5433 -p9042:9042 \
   yugabytedb/yugabyte:latest bin/yugabyted start --daemon=false

   Direct Installation

   # Download from https://download.yugabyte.com/
   # Extract and run:
   cd yugabyte-<version>
   .\bin\yugabyted.exe start

Default Connection Parameters

• Host: localhost
• Port: 5433 (PostgreSQL compatible)
• Database: yugabyte
• Username: yugabyte
• Password: yugabyte

🚀 Building the Project

Step 1: Clone or Navigate to Project Directory

cd "C:\Poco c++"

Step 2: Configure with CMake

mkdir build
cd build
cmake .. -DCMAKE_TOOLCHAIN_FILE=<path-to-vcpkg>/scripts/buildsystems/vcpkg.cmake

Without vcpkg (if Poco is installed system-wide):

cmake ..

Step 3: Build

cmake --build . --config Release

Or using Visual Studio:

cmake --build . --config Release --target ybconnector-server example-app

Step 4: Run the Example

.\Release\example-app.exe

🌐 Running the Web Server

Start the API Server

cd build\Release
.\ybconnector-server.exe

The server will start on http://localhost:8080

Access the Web Interface

1. Open a web browser
2. Navigate to: http://localhost:8080/web/index.html

Or serve the web files directly:

If you have Python installed:

cd web
python -m http.server 8000

Then access: http://localhost:8000/index.html

Note: Make sure the API server is running on port 8080

📖 Usage

C++ Library Usage

#include "YBConnector.h"
#include "YBQueryExecutor.h"

using namespace YB;

int main() {
    try {
        // 1. Create and connect to database
        YBConnector connector("localhost", "5433", "yugabyte", "yugabyte", "yugabyte");
        connector.connect();
        
        // 2. Create query executor
        YBQueryExecutor executor(connector);
        
        // 3. Execute SELECT query
        auto result = executor.executeQuery("SELECT * FROM users WHERE age > 25");
        
        while (result->next()) {
            std::cout << result->get<std::string>("name") << std::endl;
        }
        
        // 4. Execute INSERT
        size_t rowsAffected = executor.executeUpdate(
            "INSERT INTO users (name, email) VALUES ('John', 'john@example.com')"
        );
        
        // 5. Disconnect
        connector.disconnect();
        
    } catch (const YBException& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }
    
    return 0;
}

REST API Usage

Connect to Database

POST http://localhost:8080/api/connect
Content-Type: application/json

{
  "host": "localhost",
  "port": "5433",
  "database": "yugabyte",
  "username": "yugabyte",
  "password": "yugabyte"
}

Execute Query

POST http://localhost:8080/api/query/execute
Content-Type: application/json

{
  "query": "SELECT * FROM users LIMIT 10"
}

AI Query Generation

POST http://localhost:8080/api/query/ai-generate
Content-Type: application/json

{
  "query": "Show me all users over 25 years old"
}

Query Optimization

POST http://localhost:8080/api/query/optimize
Content-Type: application/json

{
  "query": "SELECT * FROM users WHERE age > 25 ORDER BY name"
}

Web Interface Features

1. Connection Manager: Enter database credentials and connect
2. AI Query Builder: Type natural language queries to generate SQL
3. SQL Editor: Write and execute SQL queries with syntax highlighting
4. Results Display: View query results in a beautiful table
5. Data Visualization: Automatically visualize query results
6. Query Optimizer: Get AI-powered optimization suggestions
7. Performance Metrics: Monitor query execution times
8. Query History: Access previously executed queries

🧪 Testing

Running Tests

cd build
cmake --build . --config Release --target tests
.\Release\tests.exe

Example Test Scenarios

The example application (example-app.exe) demonstrates:

• ✓ Database connection and authentication
• ✓ SELECT queries with various WHERE clauses
• ✓ INSERT, UPDATE, DELETE operations
• ✓ Transaction support
• ✓ Error handling
• ✓ Connection pooling

📁 Project Structure

Poco c++/
├── CMakeLists.txt          # Build configuration
├── README.md               # This file
├── include/                # Public headers
│   ├── YBConnector.h
│   ├── YBQueryExecutor.h
│   ├── YBResultSet.h
│   ├── YBException.h
│   └── AIHelpers.h
├── src/                    # Implementation files
│   ├── YBConnector.cpp
│   ├── YBQueryExecutor.cpp
│   ├── YBResultSet.cpp
│   ├── YBException.cpp
│   ├── APIServer.cpp
│   ├── AIQueryGenerator.cpp
│   └── QueryOptimizer.cpp
├── examples/               # Example applications
│   └── main.cpp
├── web/                    # Web frontend
│   ├── index.html
│   ├── css/
│   │   └ crisp styles.css
│   └── js/
│       ├── app.js
│       ├── ai-assistant.js
│       └── visualizer.js
├── tests/                  # Unit tests
└── build/                  # Build artifacts

🐛 Troubleshooting

Common Issues

1. Connection Refused

• Ensure YugabyteDB is running
• Check port 5433 is not blocked by firewall
• Verify connection credentials

2. Poco Library Not Found

# Set POCO_ROOT environment variable
$env:POCO_ROOT = "C:\path\to\poco"
cmake .. -DPOCO_ROOT="C:\path\to\poco"

3. Build Errors on Windows

• Ensure you have Visual Studio Build Tools installed
• Use Developer Command Prompt for VS
• Check CMake generator: cmake -G "Visual Studio 16 2019" ..

4. Web Interface Can't Connect to API

• Verify API server is running on port 8080
• Check browser console for CORS errors
• Ensure firewall allows connection to localhost:8080

🔧 Configuration

Connection Pool Settings

Modify connection pool size in YBConnector constructor:

YBConnector connector(host, port, db, user, pass, poolSize=5);

API Server Port

Change port in APIServer.cpp:

unsigned short port = 8080; // Change to desired port

📝 License

This project is provided as-is for educational and demonstration purposes.

🤝 Contributing

Contributions are welcome! Please ensure:

• Code follows C++14 standards
• All tests pass
• Code is properly commented
• Documentation is updated

📚 Additional Resources

• YugabyteDB Documentation
• Poco C++ Libraries
• CMake Documentation
• PostgreSQL Documentation

🎯 Future Enhancements

• Additional database backends (MySQL, SQLite)
• GraphQL API support
• Real-time query result streaming
• Advanced caching layer
• Multi-database query federation
• Enhanced AI features with machine learning models

💡 Support

For issues, questions, or suggestions, please create an issue in the project repository.

---

Made with ❤️ using C++ and Poco Libraries