--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/src/fschmidt/db/pool/PooledConnection.java	Sun Oct 05 17:24:15 2025 -0600
@@ -0,0 +1,291 @@
+/*
+Copyright (c) 2008  Franklin Schmidt <fschmidt@gmail.com>
+
+Permission is hereby granted, free of charge, to any person obtaining a copy
+of this software and associated documentation files (the "Software"), to deal
+in the Software without restriction, including without limitation the rights
+to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
+copies of the Software, and to permit persons to whom the Software is
+furnished to do so, subject to the following conditions:
+
+The above copyright notice and this permission notice shall be included in
+all copies or substantial portions of the Software.
+
+THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
+IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
+FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
+AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
+LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
+OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
+THE SOFTWARE.
+*/
+
+package fschmidt.db.pool;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import java.util.List;
+import java.util.ArrayList;
+import java.util.Set;
+import java.util.HashSet;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+import fschmidt.db.DbDatabase;
+import fschmidt.db.DbObject;
+import fschmidt.db.DbKey;
+import fschmidt.db.DbObjectFactory;
+import fschmidt.db.SQLRuntimeException;
+import fschmidt.db.extend.DbDatabaseExt;
+import fschmidt.db.extend.DbTableExt;
+import fschmidt.db.extend.DbTransaction;
+import fschmidt.db.extend.DbRecordExt;
+import fschmidt.db.extend.FilterDatabase;
+import fschmidt.util.java.Stack;
+import fschmidt.util.java.ArrayStack;
+
+
+public final class PooledConnection {
+	private static final Logger logger = LoggerFactory.getLogger(PooledConnection.class);
+
+	private final Pool pool;
+	private Connection con;
+	private int trans = 0;
+	final List<Runnable> beforeCommitList = new ArrayList<Runnable>();
+	final List<Runnable> afterCommitList = new ArrayList<Runnable>();
+	DbTransaction dbTrans = null;
+	long lastUsed;
+	boolean ignoreRunnablesInThisTransaction = false;
+	private final Stack<NestedConnection> nesting = new ArrayStack<NestedConnection>();
+	private volatile String user = null;
+
+	PooledConnection(DbDatabaseImpl db)
+		throws SQLException
+	{
+		this.pool = db.pool;
+		this.con = db.database().getConnection();
+	}
+
+	protected void finalize() throws Throwable {
+		super.finalize();
+		if( con == null )
+			return;
+		if( !nesting.isEmpty() )
+			logger.error("connection lost from pool: opened="+nesting.size()+" trans="+trans,nesting.peek().initException);
+		try {
+			this.con.close();
+		} catch(SQLException e) {
+		} finally {
+			this.con = null;
+		}
+	}
+
+	void setUser(String user) throws SQLException {
+		if( this.user == user ) {
+/*
+			Statement stmt = con.createStatement();
+			ResultSet rs = stmt.executeQuery("select CURRENT_USER");
+			rs.next();
+			String s = rs.getString("CURRENT_USER");
+			rs.close();
+			stmt.close();
+			if( s.equals(user) )
+				return;
+			logger.error("setUser error, user should be "+user+" but is "+s,new Exception());
+*/
+			return;
+		}
+		this.user = null;
+		Statement stmt = con.createStatement();
+		stmt.executeUpdate(
+			"set role " + user
+		);
+		stmt.close();
+		this.user = user;
+	}
+
+	Connection nest(String user) {
+		NestedConnection nc = new NestedConnection(this,user);
+		nesting.push(nc);
+		return nc.proxyCon;
+	}
+
+	private void transOver()
+		throws SQLException
+	{
+		con.setAutoCommit(true);
+		trans = 0;
+		dbTrans = null;
+		ignoreRunnablesInThisTransaction = false;
+		beforeCommitList.clear();
+		afterCommitList.clear();
+	}
+
+	void setAutoCommit(boolean autoCommit)
+		throws SQLException
+	{
+		int opened = nesting.size();
+		if( autoCommit==true ) {
+			//logger.warn("setAutoCommit true not well supported");
+			if( trans > 0 ) {
+				if( trans != opened )
+					throw new IllegalStateException("setAutoCommit to true with unclosed connections");
+				transOver();
+				return;
+			}
+		} else if( trans == 0 ) {
+			trans = opened;
+			dbTrans = new DbTransaction();
+		}
+		con.setAutoCommit(autoCommit);
+	}
+
+	void commit()
+		throws SQLException
+	{
+		int opened = nesting.size();
+		if( trans != opened )
+			throw new IllegalStateException("commit failed: trans="+trans+" opened="+opened);
+		final int opened2 = opened;
+		while( !beforeCommitList.isEmpty() ) {
+			Runnable[] a = beforeCommitList.toArray(new Runnable[0]);
+			beforeCommitList.clear();
+			for( int i=0; i<a.length; i++ ) {
+				a[i].run();
+				if( opened != opened2 ) {
+					logger.error("before commit opened="+opened+" opened2="+opened2+" runnable="+a[i]);
+					opened = opened2;
+				}
+				a[i] = null;
+			}
+		}
+		{	// for now, to catch aborted transactions
+			Statement stmt = con.createStatement();
+			stmt.executeQuery("select 1");
+			stmt.close();
+		}
+		con.commit();
+		if( afterCommitList.isEmpty() ) {
+			dbTrans = new DbTransaction();
+		} else {
+			Runnable[] a = afterCommitList.toArray(new Runnable[0]);
+			setAutoCommit(true);
+			for( int i=0; i<a.length; i++ ) {
+				a[i].run();
+				if( opened != opened2 ) {
+					logger.error("after commit opened="+opened+" opened2="+opened2+" runnable="+a[i]);
+					opened = opened2;
+				}
+				a[i] = null;
+			}
+			setAutoCommit(false);
+		}
+	}
+
+	void rollback()
+		throws SQLException
+	{
+		int opened = nesting.size();
+		if( trans != opened )
+			logger.warn("rollback called in nested transaction");
+		con.rollback();
+		beforeCommitList.clear();
+		afterCommitList.clear();
+	}
+
+	void close(NestedConnection nestedCon)
+		throws SQLException
+	{
+		int i = nesting.indexOf(nestedCon);
+		if( trans==nesting.size() ) {
+			if( i != trans - 1 )
+				logger.error("closing connection outside of transaction: i="+i+" trans="+trans,new Exception(nestedCon.initException));
+			con.rollback();  // rollback everything since last commit
+			transOver();
+		}
+		if( i < trans )
+			logger.error("closing connection outside of transaction: i="+i+" trans="+trans,new Exception(nestedCon.initException));
+		nesting.remove(nestedCon);
+		if( !nesting.isEmpty() ) {
+			nesting.get(nesting.size()-1).setUser();  // best guess
+			return;
+		}
+		if( trans != 0 )
+			logger.error("trans = "+trans,new Exception());
+		pool.localCon.remove();
+		if( !beforeCommitList.isEmpty() ) {
+			logger.error("beforeCommitList = "+beforeCommitList,new Exception());
+			beforeCommitList.clear();
+		}
+		if( !afterCommitList.isEmpty() ) {
+			logger.error("afterCommitList = "+afterCommitList,new Exception());
+			afterCommitList.clear();
+		}
+		lastUsed = System.currentTimeMillis();
+		synchronized(pool) {
+			if( pool.isExpired(lastUsed) ) {
+				con.close();
+				con = null;
+			} else {
+				if( con.getAutoCommit() == false ) {
+					logger.error("autoCommit is false at close",new Exception());
+					con.setAutoCommit(true);
+				}
+				pool.stack.push(this);
+			}
+		}
+	}
+
+	Connection con() {
+		return con;
+	}
+
+	boolean isInTransaction() {
+		return dbTrans!=null;
+	}
+
+	void commitTransaction() {
+		if( !isInTransaction() )
+			throw new IllegalStateException("commitTransaction called outside of transaction"); 
+		try {
+			if( con.getAutoCommit()==true )
+				throw new IllegalStateException("commitTransaction called outside of transaction"); 
+			int opened = nesting.size();
+			if( opened < trans )
+				throw new IllegalStateException(); 
+			if( opened > trans )
+				throw new IllegalStateException("commitTransaction called with unclosed connections"); 
+			commit();
+			setAutoCommit(true);
+		} catch(SQLException e) {
+			throw new SQLRuntimeException(e);
+		}
+	}
+
+	void endTransaction() {
+		try {
+			if( con.getAutoCommit()==false && nesting.size() > trans ) {
+				logger.error("endTransaction called with unclosed connections, closing them now",new Exception(nesting.peek().initException));
+				while( nesting.size() > trans ) {
+					nesting.peek().close();
+				}
+			}
+			nesting.peek().close();
+		} catch(SQLException e) {
+			throw new SQLRuntimeException(e);
+		}
+	}
+
+	void forceClose() {
+		logger.error("connection never closed: opened="+nesting.size()+" trans="+trans+" user="+user,nesting.peek().initException);
+		pool.localCon.remove();
+		try {
+			con.close();
+		} catch(SQLException e) {
+			logger.error("",e);
+		}
+		con = null;
+	}
+
+}