Re: [sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?
Hi Stefan, > Hi, > > I have released a new version of my task tracking software Yatt. It's > based on SQLite. > The new version adds a bunch of new features including user management > from the > html interface. You can find it on www.yatt.de. It's freeware. > > It's currently using SQLite 2.8.x. I am thinking about upgrading to 3.1.x. > Is there > an automated tool for converting a database from 2.8 to 3.1, which I could > give to > users for simplifying the upgrade? I mean a tool, which opens the old > database, reads > the scheme, creates a new database and copies all entries. Or do I have to > build > something myself? I know this can be done from the commandline, but that's > not an > option for normal users - especially on windows, I think. I don't think it would be hard to do yourself. Here's how: 1) Extract the code for the ".dump" meta-command from shell.c in SQLite 2.8.15. 2) Make a program that consists of: a) The code extracted in (1) b) SQLite 2.8.15. c) The latest SQLite3 d) Some glue code As far as I know, SQLite 2.8 is supposed to be able to co-exist in the same binary as SQLite3. Otherwise, why don't you supply a .bat file that does the job? It can, of course, be run from within your program. HTH Ulrik Petersen -- Ulrik Petersen, Denmark
Re: [sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?
Hi, I have released a new version of my task tracking software Yatt. It's based on SQLite. The new version adds a bunch of new features including user management from the html interface. You can find it on www.yatt.de. It's freeware. It's currently using SQLite 2.8.x. I am thinking about upgrading to 3.1.x. Is there an automated tool for converting a database from 2.8 to 3.1, which I could give to users for simplifying the upgrade? I mean a tool, which opens the old database, reads the scheme, creates a new database and copies all entries. Or do I have to build something myself? I know this can be done from the commandline, but that's not an option for normal users - especially on windows, I think. Thanks, Stefan
[sqlite] Re: [Bulk] Re: [sqlite] [OT] SQL: limit a query by sum(val)?
Hi I'm very interested in "runing-functions"! If the order is a problem, it seems to me that can be solved adding one more level of subqueries: SELECT val, period FROM ( SELECT val, period, running_total(val) AS runner FROM ( SELECT val, period FROM t1 ORDER BY period DESC ) AS l3 ) AS l2 WHERE runner < 40 ORDER BY period DESC As far as i know sqlite has not native runing-sum. Using custom functions i've got a kind of "one shot runing-functions". With "one shot" i mean such functions can't be used in more than one column at a time. The one shot limitation is because sqlite3_aggregate_context doesn't work from xFunc (strictly it isn't limitation, but would facilitate things...) Is there a problem in making sqlite3_aggregate_context to work from xFunc? Thank you. Marcelo At 6:50 PM +0100 2/11/05, Philipp Knüsel wrote: select * from t1 where period < '2003-1' order by period desc: -- val periodsum(val) -- --- -- 15 2002-4 15 -- 10 2002-3 25 -- 5 2002-2 30 -- 15 2002-1 45 -- 10 2001-4 -- 5 2001-3 I need only the first records to fulfill sum(val) >= 40 (or all records if sum(val) < 40) -- so the result should be limited to: -- val period -- --- -- 15 2002-4 -- 10 2002-3 -- 5 2002-2 -- 15 2002-1 It appears to me that you have a multi-part problem to solve here. The first part of the problem is that you have to calculate a "running sum", and I don't know if there is any way to do this in a simple manner. Given how SQL works, *if* there were a running_sum() function, it would need to execute after the ORDER BY clause, because a running sum only makes sense in the context of already sorted results. Perhaps then the query might look something like this: SELECT val, period FROM ( SELECT val, period, running_total(val) AS runner FROM t1 ORDER BY period DESC ) AS l2 WHERE runner < 40 ORDER BY period DESC I don't see this being a simple problem yet, in any case. I don't know if the current mechanism for writing your own functions will let you execute them at the necessary time. (The example I gave may be wrong, if the SELECT line executes prior to ORDER BY.) -- Darren Duncan
