jprante/elasticsearch-river-jdbc — pull request #154 by kingster (2013-12-19 22:00:59+00:00)
Title: Support for Pre-Initialise Query | Support for Incremental Pull 

For my use case I required a transactional incremental pull from mysql

So added the feature checkpointing by introducing presql i.e initialise sql

presql command is of Update/Insert nature so as that this is executed before the river is run.

**Added Params**
```
"presql" : null,
"presqlparams" : null
```

For my purpose of incremental pull an example of it is

```sql
curl -XPUT 'localhost:9200/_river/cs_jdbc_river/_meta' -d '{
"type" : "jdbc",
"jdbc" : {
"driver" : "com.mysql.jdbc.Driver",
"url" : "jdbc:mysql://localhost:3306/messages",
"user" : "root",
"password" : "",
"max_retries" : 3,
"presql" : "INSERT INTO ES_Track (name, checkpoint , version ) SELECT ? , max(`lastUpdatedTS`), ? from `Message`",
"presqlparams" : [ "cs", "$job"] ,
"sql" : " SELECT Message.id as \"_id\" , Message.* from Message where Message.lastUpdatedTS < ( SELECT `checkpoint` from ES_Track WHERE `name`= ? AND ack = 0 ORDER by id DESC LIMIT 1 ) AND Message.lastUpdatedTS >= ( SELECT `checkpoint` FROM ES_Track WHERE `name`= ? AND ack = 1 ORDER by id DESC LIMIT 1 )",
"sqlparams" : [ "cs", "cs" ] ,
"acksql" : "UPDATE ES_Track set ack = 1 where name = ? ORDER by id DESC LIMIT 1 ",
"acksqlparams" : [ "cs" ] ,
"poll" : "300s",
"strategy" : "simple" ,
"autocommit" : true,
"index" : "cs",
"type" : "messages",
"versioning" : false
}
}'
```

And the ES_Track is the river tracker table defined as

```sql
CREATE TABLE `ES_Track` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(64) DEFAULT NULL,
`version` int(11) DEFAULT NULL,
`checkpoint` varchar(64) DEFAULT NULL,
`ack` tinyint(1) DEFAULT '0',
`lastUpdateTS` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `lastUpdate` (`checkpoint`),
KEY `lastUpdateTS` (`lastUpdateTS`),
KEY `name` (`name`)
);
```

So thus every time it runs from the last successful time the river was run.

Thus supporting for incremental pulls. Though this feature of presql can be used for variety other purpose based on persons needs like running an insert/update query before the sql select.

**Note**: ack sql is only triggered after the get result succeds ( it wont run if the river failed for some reason )

Note: This is my first pull request to this repo, let me know if I need to add any change