jprante/elasticsearch-river-jdbc — pull request #137 by psliwa (2013-11-22 08:52:22+00:00)
Title: New strategy: "column"

I have written new strategy for this river because two existing strategies didn't meet my requirements. Additional options for this strategy are:

* columnCreatedAt (default: created_at)
* columnUpdatedAt (default: updated_at)
* columnDeletedAt (default: null)
* columnEscape (default: true)

I have provided tests for all db engines that you are using in this project except Oracle db.

There are some encyclopedic info about this strategy:

Updates with additional columns
-------------------------------

This strategy depends on additional columns that you have to add to your table with data that you want to index:

* column with creation time
* column with last update time
* column with deletion time (optionally) - to support document deletion, you have to use soft delete pattern

Strategy based on versioning is inefficient and every time whole table is reindexed even when only few documents have been updated. Strategy based on table in other hand forces your application to populate additional table, so you have to make changes in your application code.

`column` strategy on single "river run" indexes only that documents that should be indexed (new, updated or deleted). If document didn't changed since previous river run, it will no be even processed by the river. Column strategy also doesn't force you to change you application code, except when your table that you want to index has no lifecycle timestamps (creation, last update or optionally deletion times). The main limitation is that document deletion is supported only when you use soft delete pattern.

River rememers time of last run, so on current run are performed 3 queries: for new, updated and deleted documents. Proper where clause conditions for each for this queries are created and appended to `sql` option. When you have already WHERE in your sql, new conditions will be added to the begining of the WHERE clause. In other way WHERE clause will be created on the query end.

Example:

**
sql: "SELECT * FROM products" #it will be changed to SELECT * FROM products WHERE "auto generated where"
**

**
sql: "SELECT * FROM products WHERE category=5" #it will be changed to SELECT * FROM products WHERE "auto generated where" AND category=5
**

Be cerfuly when you are using subqueries with where clause, because algorithm that change you query is very simple and it can break your query in that case.