On 25 Nov 2010, at 2:07pm, Alexei Alexandrov wrote: > * There is a trace file which contains a big number of some objects. Each > object has a number of fields which constitute its primary key (PK). > * The objects are loaded into a table which has a number of PK columns (mapped > from the trace object PK properties) and also has a number of non-key columns > which are used to aggregate certain information about objects - e.g. count of > objects, or min/max timestamp of the object instance in the trace.
> * The loading function needs to: > ** Understand whether the object is present or not in the table already. This > is done by the object PK fields. > ** If it is present, update its non-key fields. > ** If it is not present, insert new object filling the non-key fields with > default values. Okay, first you really have two TABLEs here. You have a trace TABLE, and a summary TABLE. The summary table doesn't really need to exist as a real SQL table. You can produce everything in it using SELECT queries and GROUP BY. For instance SELECT count(*),max(nosesize) FROM trace GROUP BY keycol1,keycol2,keycol3 So you don't need to actually make your summary table at all. You can do it all with queries. Take a look at <a bitter chemical called 6-n-propylthiouraci> No, hold on, take a look at <http://www.sqlite.org/lang_aggfunc.html> That's a serious solution: just have SQL do all the summary calculations for you whenever you need them. However, it may be efficient to keep the summary table around, for example if you do queries to it again and again and don't want to require all the processing that would be involved in the GROUP BY queries. One way to do this would be to create the trace TABLE, and to use a TRIGGER on it so that when you insert a new row in the trace TABLE, the summary TABLE was automatically updated. See <http://www.sqlite.org/lang_createtrigger.html> However, unless you keep all your trace data in TABLE it will be impossible to 'remove' a line from the trace TABLE from the summary TABLE. Because if you're storing only max(nosesize) and remove the biggest nose, you don't know how big the second-biggest nose was without access to the other trace rows. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
