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]