Hi, all, (My apologies in advance for the length of this post. 90% of it is just setting the stage for a couple relatively simple questions.)
As part of the Fossil SCM, earlier this evening i did something kind of strange with sqlite and now i'm looking for feedback on whether or not this is a generically a valid implementation strategy (it's fine for this particular context, but i'd like to re-use/expand it a bit if it's not inherently evil or grossly inefficient)... In short, we have a table (called 'event') of historical data which holds everything needed by the so-called timeline view. Anyone who uses Fossil will know what the 'timeline' is, and everyone else can see examples here: http://www.fossil-scm.org/fossil/timeline http://sqlite.org/src/timeline/ We use that same data to generate the /reports pages: http://www.fossil-scm.org/fossil/reports The task at hand was the filter this report data based on event type (checkin, wiki change, ticket change, etc.). Historically speaking, we've supported such flags by generating different SQL depending on the contents of the appropriate client-provided flags. This time, however, i decided that i didn't want to touch the 3 or 4 routines which generate/run the SQL, and instead went for the proverbial Extra Level of Indirection and shoved a temporary view between the reporting code and the event table to perform the filtering. The implementation is rather trivial and, all things considered, turned out quite nicely (IMO): http://www.fossil-scm.org/fossil/vdiff?from=e3a2d8f381a1fd59&to=68e3e0a5dcc5f4ab&sbs=1 (Pedantic note: that unused return value will be used in the up-coming step 2 of this change.) The interesting parts are really the purpose ones, where "event" has been replaced by "v_reports". After reading through that once, please take special note of lines 1914-1915. Those are functionally a no-op which is _guaranteed_ to be less efficient than implementing this "the hard way," but such is the generic price of having the Extra Level of Indirection. i do not know how _much_ of a cost that entails, in terms of sqlite internals and the effect on search performance, but for this case a few extra milliseconds or a few more KB of RAM isn't a concern (maybe it will be if i run this against the core TCL repo, which is particularly large... i need to try that out later). So, now i'm curious... Let's say i want to add a similar filter for 'user' (we already have this filter, implemented without a view, but let's just pretend for a moment that we don't)... is there a tremendous efficiency penalty if i implement several layers of filtering based on views, each view filtering out one more part of the equation from the next view down the chain? e.g. for the above we have: a) event table b) v_reports view which filters event based on event.type And then, hypothetically: c) v_reports2 which filters v_reports by v_reports.user. and so on, and so forth. Obviously, this doesn't support arbitrary combinations of filters all that well because each view has to derive from some other view (for this particular case), but for this use case we're not likely to ever have/need more than about 3 or 4 filters. Why do it this way at all? Because (IMO) it's much nicer-looking (in code) than modifying the SQL for each particular filter case. e.g. compare the above diff with blocks like this one which have to be repeated across several different functions: http://www.fossil-scm.org/fossil/artifact/4a5030799da3ead5fb5f13cd49aebc8860f9e933?ln=2168-2171 So... my questions are, basically: a) is this a gross/unconscionable inefficiency or is this a viable strategy, provided the number of filters stays small (say, 2-3)? b) how much is this costing me (abstractly speaking), in terms of search performance? Is the search hit cost here linear (which should still be okay... except maybe for the core TCL repo) or is it worse? Your insights are much appreciated, -- ----- stephan beal http://wanderinghorse.net/home/stephan/ http://gplus.to/sgbeal _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
