Generation of SQL statements in FWD¶

There are several kind of SQL statements which FWD generates when accessing the database server

1. DDLs used to create database structure (tables/indexes/table constraints).
   1. the most obvious part of these are generated at conversion time for permanent tables. They can be found in ddl directory of the working conversion project, one set for each active dialect in p2j.cfg.xml;
   2. the temp-table DDLs are generated at runtime, at first access to a specific table, be it static or dynamic. These are always in H2 dialect.
2. statements used for record management, including creation, update and deletion of records, either for single records and bulk batches. These are created at runtime and cached at different levels. See RecordMeta *Sql array field; (TODO: this needs to be expanded and sub-classified)
3. the vast majority of statements an application will use is formed of SELECT queries. We do not generate SQL queries all at once, instead the convert the ABL query in multiple stages:
   1. at conversion time the query predicate is converted to FQL. This is a dialect-independent language based on Hibernate HQL. You can find them in the generated code (usually the 2nd argument of a FindQuery c'tor);
   2. at runtime the FQL is preprocessed by HQLPreprocessor. At this stage the predicate is enhanced to make sure the semantics are honoured (for example when comparing to null/unknown values). There are also a few optimizations which will also transform/simplify the query;
   3. for temporary tables the multiplexer component is added to FQL;
   4. for normalized fields, the link to parent table is also added to join part;
   5. optionally, in a more complex query, multiple components are optimized in a single one. At this time the FQL predicates are merged and additional joining clauses are AND-ed to it;
   6. we are almost done: when the query is about to be executed, the FQL is converted by FqlToSqlConverted to a full SQL query. At this moment the paging (offset/limit options) support is added if requested. At this time a special map with query structure expected to be returned is constructed. Only at this stage the statement becomes dialect dependant;
   7. and finally, the SQL is executed and the result is analysed based on the current session cache and map from above step.