davsclaus opened a new pull request, #3215:
URL: https://github.com/apache/camel-kamelets/pull/3215

   Part of #3170: the SQL sources can limit the rows of a poll, mark a failed 
row, run a statement after each poll, and send a whole poll as one JSON array. 
The SQL sinks can write a JSON array of rows from one message. All of them can 
size their connection pool.
   
   Kamelets: `postgresql-*`, `mysql-*`, `mariadb-*`, `sqlserver-*` and 
`oracle-database-*`. `snowflake-*` is left out, because #3208 changes it.
   
   ### The options
   
   Sources:
   
   | Option | Template mapping | What it does |
   |---|---|---|
   | `maxMessagesPerPoll` | `&maxMessagesPerPoll={{?maxMessagesPerPoll}}` | The 
most rows one poll sends. The rest are read by the next polls, when 
`consumedQuery` marks the rows that were sent. |
   | `onConsumeFailed` | `&onConsumeFailed=RAW({{?onConsumeFailed}})` | An SQL 
statement run instead of `consumedQuery` when the next step fails for a row, 
such as `UPDATE accounts SET failed = 1 WHERE id = :#id`. |
   | `onConsumeBatchComplete` | 
`&onConsumeBatchComplete=RAW({{?onConsumeBatchComplete}})` | An SQL statement 
run once after all the rows of a poll are sent. It cannot take parameters. |
   | `initialDelay` | `&initialDelay={{?initialDelay}}` | Milliseconds before 
the first poll. One second when not set. |
   | `useIterator` | `&useIterator={{?useIterator}}` | `false` sends all the 
rows of a poll as one message, a JSON array. |
   | `routeEmptyResultSet` | `&routeEmptyResultSet={{?routeEmptyResultSet}}` | 
With `useIterator: false`, also sends `[]` from a poll that finds no rows. |
   | `delay` (`oracle-database-source` only) | `&delay={{delay}}`, `default: 
500` | Already on the other four sources. Oracle was fixed at the component's 
500 ms. |
   
   Sinks:
   
   | Option | Template mapping | What it does |
   |---|---|---|
   | `batch` | `&batch={{?batch}}` | The body can be a JSON array of objects. 
`query` runs once for each object, in one transaction: if one row fails, none 
is written. A single object still works. `CamelSqlUpdateCount` holds the total. 
|
   
   All ten, on the commons-dbcp2 `BasicDataSource` bean, next to 
`connectionProperties` from #3176:
   
   | Option | Bean mapping | What it does |
   |---|---|---|
   | `maxTotal` | `maxTotal: "{{?maxTotal}}"` | The most connections open at 
once. 8 when not set. |
   | `maxIdle` | `maxIdle: "{{?maxIdle}}"` | The most unused connections kept 
open. 8 when not set. |
   | `maxWaitMillis` | `maxWaitMillis: "{{?maxWaitMillis}}"` | How long a 
message waits for a free connection before it fails. Unlimited when not set. |
   
   - **Defaults:** every new option is optional with no `default`, as in #3174. 
When unset, the optional placeholders drop them from the URI, and an empty bean 
property is not set. The endpoint URIs are then byte-identical to `main`'s.
     - The exception is `oracle-database-source`. It gets `delay` with 
`default: 500`, like the other sources, so its URI now has `delay=500`. That 
was already its delay.
   - **Names:** the options keep the names of the Camel `sql` component, so the 
Camel documentation still applies. The titles say what they do: "Failed Row 
Query", "Poll Complete Query", "One Message Per Row", "Send Empty Result".
   - **`RAW()`:** the two SQL options are wrapped in `RAW()`, as 
`consumedQuery` already is.
   - **Descriptions:**
     - Source "Output" says what the body is with `useIterator: false`.
     - Source "Good to know" points to `onConsumeFailed` and 
`maxMessagesPerPoll`.
     - Sink "Input", "Output" and "Good to know" cover `batch`, and its 
all-or-nothing transaction.
     - On the sources, `maxTotal` warns that the source holds one connection 
while it sends the rows of a poll. `consumedQuery`, `onConsumeFailed` and 
`onConsumeBatchComplete` each need a second one.
     - `onConsumeBatchComplete` warns that a row added after the query ran is 
not in the poll, so the statement must not mark it.
     - `useIterator` says that `consumedQuery` and `onConsumeFailed` cannot use 
`:#column` in that mode.
   - **Partial pages:**
     - Each source page has "Reading Rows in Batches", "All the Rows of a Poll 
in One Message" and "Connection Pool".
     - Each sink page has "Writing Several Rows from One Message" and 
"Connection Pool".
     - The Oracle source page now names `delay`.
   
   ### Left out
   
   - **`transacted`:** without a Spring transaction manager bean and a 
transacted route, which a Kamelet cannot take, the consumer starts no 
transaction. A test with `transacted=true` on PostgreSQL showed:
     - The `consumedQuery` of earlier rows was committed.
     - When a row failed, the poll stopped at that row, and `onConsumeFailed` 
was not run.
     - Every later poll stopped at the same row, so the rows after it were 
never read.
   - **`outputType`:** `SelectOne` and `StreamList` do not fit a Kamelet that 
sends JSON. `useIterator` covers "the whole result as one message".
   - **`minIdle`:** commons-dbcp2 keeps it only when the evictor runs 
(`timeBetweenEvictionRuns`), which is off by default. It would add an option 
that does nothing on its own.
   
   ### Verification
   
   Tested with Camel JBang 4.24.0-SNAPSHOT against PostgreSQL 17, MySQL 8.4, 
MariaDB 11, SQL Server 2022 (`mcr.microsoft.com/mssql/server:2022-latest`, 
amd64 under emulation) and Oracle 23ai Free 
(`gvenzl/oracle-free:23-slim-faststart`, arm64). Each test reached the endpoint 
through the Kamelet, and `camel get endpoint` showed the URI.
   
   | Case | PostgreSQL | MySQL | MariaDB | SQL Server | Oracle |
   |---|---|---|---|---|---|
   | Options unset, `main` and this branch: `camel get endpoint`, source and 
sink | Identical | Identical | Identical | Identical | Sink identical; source 
adds `delay=500` |
   | Source with `maxMessagesPerPoll: 3`, 10 rows | Polls of 3, 3, 3, 1 
(`CamelBatchSize`) | Same | Same | Same | Same |
   | `onConsumeFailed`, the step throws for rows 2 and 5 | Rows 2 and 5 
`failed=1`, the other 8 `processed=1` | Same | Same | Same | Same |
   | `onConsumeBatchComplete` inserting into a log table | 4 rows, one for each 
poll that found rows | Same | Same | Same | Same |
   | `initialDelay: 4000` | First row 4.06 s after the start | 4.13 s | 4.09 s 
| 4.14 s | 4.23 s |
   | `delay: 1000` | | | | | Polls 1 s apart (was fixed at 0.5 s) |
   | `useIterator: false`, 3 rows | One message 
`[{"id":1,...},{"id":2,...},{"id":3,...}]`, `Content-Type: application/json` | 
Same | Same | Same | Same, keys `ID` and `USERNAME` |
   | ... with `routeEmptyResultSet: true`, then no rows | `[]` on every later 
poll | Same | Same | Same | Same |
   | ... without `routeEmptyResultSet`, no rows | Nothing sent | Same | Same | 
Same | Same |
   | Sink, `batch` unset, one object | 1 row | 1 row | 1 row | 1 row | 1 row |
   | Sink, `batch` unset, JSON array | Fails: `Cannot find key [username]` (as 
on `main`) | Same | Same | Same | Same |
   | Sink, `batch: true`, array of 5 | 5 rows, `CamelSqlUpdateCount=5` | Same | 
Same | Same | Same |
   | `batch: true`, one object | 1 row, count 1 | Same | Same | Same | Same |
   | `batch: true`, row 2 of 3 too long for its column | Fails; no row written 
(row 1 rolled back) | Same | Same | Same | Same |
   | `batch: true`, row 2 of 2 lacks a field | Fails before writing; no row | 
Same | Same | Same | Same |
   | `batch: true`, `[]` | OK, count 0 | Same | Same | Same | Same |
   | `batch: false`, one object | 1 row | Same | Same | Same | Same |
   | Sink, pool unset, 4 messages at once, query sleeps 2 s | All 4 OK at the 
same time | Same | Same | Same | Same |
   | `maxTotal: 1`, `maxWaitMillis: 500`, the same 4 messages | 3 fail after 
0.5 s with `Failed to obtain JDBC Connection`; 1 OK after 2 s | Same | Same | 
Same | Same |
   
   PostgreSQL only:
   
   | Case | Result |
   |---|---|
   | Pool unset, 10 messages at once | 8 connections in `pg_stat_activity` 
during the burst; 8 messages finish at 2 s, the other 2 at 4 s |
   | `maxIdle: 1`, 4 messages that finish one second apart | After each one 
finishes, `pg_stat_activity` shows 1 idle connection; with `maxIdle` unset, 1, 
2, 3, then 4 idle |
   | `maxIdle: 0` | Every connection is closed once given back |
   | Source, `maxTotal: 1`, `maxWaitMillis: 1000`, `consumedQuery` | 
`consumedQuery` fails for each row with `Timeout waiting for idle object`; no 
row is marked |
   | The same without `maxWaitMillis` | Blocks after the first row until the 
integration is stopped |
   | `useIterator: false` with `consumedQuery ... :#id` | `consumedQuery` fails 
on every poll with `Cannot find key [id]`; the rows are sent again |
   | `useIterator: false`, `onConsumeBatchComplete: UPDATE ... WHERE processed 
= 0`, a row inserted while the poll's message is processed | The row is marked 
processed but never sent |
   | `transacted=true` (template copy, not in this PR), a row fails | See "Left 
out" |
   
   `./mvnw clean verify -DskipTests` and then `./mvnw clean verify` passed from 
the root. The first run caught lower-case "per" in two titles, which are now 
"Max Rows Per Poll" and "One Message Per Row". Before building, the library 
copies of the ten Kamelets were refreshed with `./mvnw -pl 
library/camel-kamelets resources:copy-resources@copy-resource-one`. The build 
regenerated `kamelets-index.json`. `KameletsCatalogTest` passed with no count 
changes.
   
   _Claude Code on behalf of Claus Ibsen_
   
   🤖 Generated with [Claude Code](https://claude.com/claude-code)
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to