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
Re: [sqlite] [OT] SQL: limit a query by sum(val)?
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
[sqlite] [OT] SQL: limit a query by sum(val)?
Hello SQLite-Users This is offtopic, but as I would like to use it within SQLite... so let's try: I would like to reduce the resultset of a query like using limit. But instead of using the number of records by the sum of values. A small example: create table t1 (val int, period text); insert into t1 values ( 5, '2001-3'); insert into t1 values (10, '2001-4'); insert into t1 values (15, '2002-1'); insert into t1 values ( 5, '2002-2'); insert into t1 values (10, '2002-3'); insert into t1 values (15, '2002-4'); insert into t1 values (10, '2003-1'); insert into t1 values ( 5, '2003-2'); insert into t1 values (25, '2003-3'); insert into t1 values ( 5, '2003-4'); 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 Sure, there is always the way to reduce the resultset within an application. Is there a way of using a subselect? I asked google, the nearest term that comes to my mind is "sliding window", but maybe someone of you knows much better. Your time is truly appreciated. Thank you! Kind regards Philipp
[sqlite] [OT] SQL: limit a query by sum(val)?
Hello SQLite-Users This is offtopic, but as I would like to use it within SQLite... so let's try: I would like to reduce the resultset of a query like using limit. But instead of using the number of records by the sum of values. A small example: create table t1 (val int, period text); insert into t1 values ( 5, '2001-3'); insert into t1 values (10, '2001-4'); insert into t1 values (15, '2002-1'); insert into t1 values ( 5, '2002-2'); insert into t1 values (10, '2002-3'); insert into t1 values (15, '2002-4'); insert into t1 values (10, '2003-1'); insert into t1 values ( 5, '2003-2'); insert into t1 values (25, '2003-3'); insert into t1 values ( 5, '2003-4'); 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 Sure, there is always the way to reduce the resultset within an application. Is there a way of using a subselect? I asked google, the nearest term that comes to my mind is "sliding window", but maybe someone of you knows much better. Your time is truly appreciated. Thank you! Kind regards Philipp
