Add support for nested queries

[skylardarkfox]

Allow for subsequent queries to reference prior queries by nesting them within the JSON. This will need an adjustment of the Diff structure and API.

[skylardarkfox]

This behavior should take advantage of the new Transaction structure by automatically generating the linking functions. This will also need special placeholders for the autoincrement ids generated from the parent query.

[skylardarkfox]

The most sensible way to handle this is with nested Models. References can be stored and the methods called recursively from the parent Model. This recursion could be pretty processor-intensive if done row-by-row, so ideally each Model should be refreshed once per update, and filtered per row according to simulated foreign keys.

[skylardarkfox]

Model performance optimized by removing initial select call for API calls. Select now only happens after DML queries, and Diff generation for these is skipped. This functionality remains for the purpose of WebSocket interaction, to be developed later.

[skylardarkfox]

Prequerying may be needed for DML queries on nested Models (hereafter referred to as "submodels"). There is no convenient SQL mechanism to grab all affected IDs from a prior query - only the most recent. This is acceptable if only one row is affected by each individual DML statement, but it will break if more rows are involved (as when a broader WHERE statement is used). Because SQL doesn't provide for this, Velox will have to cover the gap, but performance has to be considered. Either a full select needs to be used on the parent Model (cascading through all submodels), which is a big performance hit to start, and could run into memory issues; or a filtered query needs to be run for each individual row (which is faster for short tables but could be even worse as table sizes increase). One way or another, performance will be impacted by this. Overall, SQL joins will be faster owing to the database engine's built-in optimizer, but then the developer would have to write the whole joined query set into a single Model, which results in the typical redundant data from the one side of one-to-many joins. SQL joins are allowed by default, since Velox doesn't care what the overall query looks like as long as placeholders can be provided, but DML on multiple related tables is complicated, and that's what we're trying to avoid here.

[skylardarkfox]

The API endpoint code and query definition format are going to have to be adjusted to account for submodels. As it stands, one query definition corresponds to one Model.

Edit: This has been taken care of. Syntax has been updated, and incoming data is parsed with json_decode in the Diff constructor rather than being pre-parsed by the endpoint. Arbitrary properties are now allowed, which is necessary for submodel syntax.

[skylardarkfox]

Current status: added recursive function in endpoint code to generate submodels from $QUERIES as needed. DML methods still need work.

[skylardarkfox]

Incidental feature added: IN / NOT IN operators. These work similarly to the IN / NOT IN operators in standard SQL, and compare the value of the column against an array of values.

This has been put here for the sake of filtering for a set of primary keys based on each element of the where clause, but is also available for use in general.

[skylardarkfox]

PreparedStatements used as insert and delete for the parent Model of a nested Model are problematic, since the placeholders don't necessarily correspond to column names, so the necessary pre-filtering for primary keys can't be done. For now, these are disallowed in such cases (with exceptions set). There may be a way to accomplish this with post-query comparison on the Model dataset, but this could be computationally pricey since this would have to be done after execution of each PreparedStatement. StatementSets are strongly preferred for nested Model use, as the necessary column names are already included.

Maybe something to revisit later.

[skylardarkfox]

Currently looking into insert optimization. Currently, StatementSet inserts are done with batched PreparedStatements, but research indicates that using multi-row inserts can improve performance by 10x or better, up to a point (somewhere between 20-40 rows per statement). Might be able to build and batch dynamically to take advantage of this.

Edit: This is a useful line of thought, but the resolution of the nested Models issue doesn't depend on it. As far as Model is concerned, it doesn't need to know how StatementSet works - just that it does. New issue has been opened for this.

Edit 2: Such insert optimization will not apply to inserts on any parent Models in a nested Model set, mainly because it's impossible to identify the relevant insert ids if a multi-row parent Model insert is done. The only such optimization that can take place is on the final child Models.

[skylardarkfox]

A point of common sense: all DML queries on Models will now be wrapped in a Transaction. This will allow DML queries on nested Models to be rolled back if any one of them fails. Partial data changes would be unexpected and potentially troublesome behavior.

[skylardarkfox]

Submodel inserts will probably have to be done sequentially between parent Model inserts. Added __clone method to help with this.

[skylardarkfox]

Another incidental feature added: ArrayAccess, Iterable, and Countable implementation. A Model can now be accessed and iterated as if it was an array (though inserts still have to be done through the insert() method). This will later be adjusted to allow submodels to be referenced by parent row.

*Note: Model iteration through these implementations acts on the raw data and ignores any defined filters and sorts. To access the filtered and sorted data, use the Model->data() method.

[skylardarkfox]

Yet another incidental feature: Model->invertFilter(). This method changes the return value of Model->data() such that all rows are returned that don't match the defined filter. This has no effect if no filter is defined.