"Bert Huijben" <b...@vmoo.com> writes: > How would this handle deleted nodes in one layer (then some overlays) and > then calling _read_children(). I think that would become a union/select over > multiple layers? We already had some performance issues there in the past > and I hope this only makes this query easier. (SELECT DISTINCT name where > parent_relpath=? or something) > > Before this new idea I expected that we didn't have to query the NODE_DATA > if you were just querying _read_info() for kind and status. So for those two > most common fields I didn't expect any slowdown over the current model. > With moving everything in one table we will need the sqlite index for > optimization in a few more cases to keep the same speed. (I think SQLite can > handle this for us as one of the nice features of using a real database, but > nevertheless, I think we should try to verify this before moving everything > into one table)
I'm not an SQL expert, much less an SQLite expert, however BASE_NODE is still available by adding op_depth=0 to the query. WORKING_NODE is a bit more complicated as one needs to get the biggest op_depth>0, so select op_depth>0, order by op_depth and limit to 1. Obviously we will have to include op_depth in the SQLite index. In cases such as _read_info where both BASE_NODE and WORKING_NODE are required we can ask for the biggest op_depth first and if this turns out to be zero then we find out that there is no WORKING_NODE and get the BASE_NODE with one query. For unmodified nodes this might be faster than separate BASE/WORKING. I'm not sure how _read_children would be affected. SELECT DISTINCT probably allows us to count them, but I don't know how to construct the query to return the greatest op_depth for each name. -- Philip
