irstv/orbisgis — pull request #91 by gourlaysama (2012-08-08 12:38:19+00:00)
Title: SQL parametrized statements / scripts + persistence

This PR contains two related features:

1. Parametrized statements

Parameters are of the form `@{paramname}` and can be used anywhere one expects:
- a constant value (`SELECT @{param} AS toto;`)
- a field name (`SELECT @{field} FROM hello;`)
- a table name (`SELECT * FROM @{table};`)

Both SQLStatement and SQLScript can receive as many parameters as necessary. Parameters
are obviously validated by parameter type, by name and by value type (for fields and constants).
Leaving a parameter unset is also obviously an error.

Note that all parameters are unset after execution. Every new execution from the same statement/script
must set the required parameters.

The parameter format could probably be improved (type restrictions, default values, optional parameters...), but I'll wait on some feedback before going any further.

2. Persistence to disk in binary AST form

An SQLStatement could already be persisted to disk, but not a
whole script.
This become now as easy as:

```scala
val a = Engine.parseScript("")
// or from a script file
val b = Engine.parseScript(new File("/tmp/f.sql"))
a.save(new File("/tmp/f.bsql"))
```
and later

```scala
val b = Engine.loadScript(new File("/tmp/f.bsql"))
// set parameters if any
b.setFieldParameter("afield", "the_geom")
b.setValueParameter("constantvalue", ValueFactory.createValue(42))
b.execute
```
Same thing can be done for individual statements with `Engine.parse(...)` and `Engine.load(...)`.

The persistence format is made such that there is always upward/downward compatibility: the SQL String itself is persisted as well, alongside the AST, so that the AST can be recreated if it cannot be directly loaded for some reason.

Under the hood, what happens is that:
- the SQL String is parsed in the syntax tree, then converted to the logic query plan.
- all the passes that are DSF-independent are run on it
- this new AST is serialized. At this point it is entirely immutable.
- when reloaded later, the AST is deserialized and kept as is in memory.
- then everything happens as before (the AST is duplicated and mutated by the remaining passes).

This will allow in the future the UI to load a compiled script from a Resource, set parameters and directly use it. There is a nice speedup (at least x10 even for one-liners) associated with this: parsing and initial optimization take time. Doing it every time was quite stupid, doing it once (at startup) is better, and now we should even be able to do it 0 times during runtime (and once at compile time) :-)

/cc @agueganno