import ballerina/io;
import ballerina/sql;
import ballerinax/java.jdbc;

sql:ConnectionPool pool = { 
    maxOpenConnections: 2, // extra small connection pool to see issues faster
    maxConnectionLifeTime: 18,
    minIdleConnections: 1
};

final jdbc:Client testDB = check new jdbc:Client("jdbc:sqlite:test.db", connectionPool = pool);

type Test record {int number;};

public isolated transactional function queryTest(int number) returns int|error {
    var result = testDB->query(`SELECT ${number} AS number;`, Test);
    var count = result.next();
    check result.close();  // closing the stream has no effect on this problem
    if !(count is error) {
        return count.value.number;
    } else {
        return count;
    }
}

public isolated function testFailingTransactions() returns error? {
    foreach var i in 1...5 { // fails after 2 iterations (both connections exhausted)
        transaction {
            io:println("Attempt nr. ", i);
            var result = queryTest(i);
            io:println(result);
            'transaction:setRollbackOnly(error("Abort")); // comment this out to check normal execution
            check commit;
        } on fail error e {
            io:println(e);
        }
    }
}

public function main() {
    var err = testFailingTransactions();
    io:println(err);
    runtime:sleep(60); // wait for connection pool to recover
    err = testFailingTransactions();
    io:println(err);
}

import ballerina/io;
import ballerina/sql;
import ballerinax/java.jdbc;
import ballerina/task;
import ballerina/http;

sql:ConnectionPool pool = { 
    maxOpenConnections: 1, 
    maxConnectionLifeTime: 30,
    minIdleConnections: 0
};

final jdbc:Client testDB = check new jdbc:Client("jdbc:sqlite:test.db", connectionPool = pool);

type WorldRecord record {|
    string|() world;
|};

// set simulationError to true to get different stacktrace
public isolated function dbTest(boolean simulateError = false) returns error? {
    transaction {
        stream<WorldRecord, error?> result = testDB->query(`SELECT world FROM hello;`);
        check result.close();
        var _ = check testDB->execute(`INSERT INTO hello VALUES ('test')`);
        result = testDB->query(`SELECT world FROM hello;`);
        check result.close();
        if (simulateError) {
            rollback;
        } else {
            check commit;
        }
    }
    transaction {
        var _ = check testDB->execute(`DELETE FROM hello WHERE world='test'`);
        check commit;
    }
}

public isolated class TestTask {

    *task:Job;
    private int counter = 0;
    private final string name;

    function init(string name) {
        self.name = name;
    }

    public isolated function execute() {
        int count = 0;
        lock {
            count = self.counter.cloneReadOnly();
            self.counter += 1;
        }
        io:println(string`Task: ${self.name},  Invocation:  ${count}`);

        var err = trap dbTest();

        if ! (err is ()) {
            io:println("Error in task " + self.name);
            io:println(err);
            io:println(err.stackTrace());
            panic err; // remove task after first error
        }
    }

    public isolated function schedule(decimal interval) {
        do {
            var _ = check task:scheduleJobRecurByFrequency(self, interval);
        } on fail error err {
            io:println(err);
        }
    }
}


service / on new http:Listener(9097) {
    resource function get .() returns json {
        return {
            hello: "world"
        };
    }
}


public function main() {
    var task1 = new TestTask("a    ");
    var task2 = new TestTask(" b   ");
    var task3 = new TestTask("  c  ");
    var task4 = new TestTask("   d ");
    var task5 = new TestTask("    e");

    decimal interval = 0.1; // adjust to change error occurrence speed
    task1.schedule(interval);
    task2.schedule(interval);
    task3.schedule(interval);
    task4.schedule(interval);
    task5.schedule(interval);
}