Re: [sqlite] Dumb Newbie Question - Comparison if sqlite index to STL
--- Ben Supnik <[EMAIL PROTECTED]> wrote: > I am porting my app's data model from C++/STL to sqlite. My goal is > scalability - even if I implement my data model in C++ using data > structures that give me good O(N) time (e.g. use trees, hash tables, > etc.) the whole thing has to be in memory, and adding indexing means a > pretty big code churn. > > My question is: > > If I take all of the sqlite optimizations I should be taking (saving my > SQL query statements in compiled form to avoid recompiling over and > over, having enough pages in memory to avoid disk thrash, using > transactions to limit disk I/O) > > Does anyone have sqlite and STL experience to tel me how the speed of an > indexed column of integers in sqlite would compare to a set in C++? > (I believe my set implementation uses a red-black tree.) An STL set would be around 1,000 times faster than SQLite. It's not a fair comparison, though. STL does not have to be persistant, nor does it have to worry about marshalling and unmarshalling, different byte order machine words, locking, concurrent reads and writes and thousands of other things that a database must do. It's here! Your new message! Get new email alerts with the free Yahoo! Toolbar. http://tools.search.yahoo.com/toolbar/features/mail/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: selecting a random record from a table
On Fri, Jan 26, 2007 at 01:38:07PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >But I read that as "goto to offset 2 and return the first row after > >offset 2." > > Why offset 2, when the clause reads, say, OFFSET 500? Also, there are > just two rows, at offset 0 and offset 1. What do you mean by "return > first row after offset 2"? There are none. Actually, a query that does that would be: SELECT rowid,* FROM foo WHERE rowid >= (abs(random()) % (SELECT rowid FROM foo ORDER BY rowid DESC LIMIT 1)) ORDER BY rowid ASC LIMIT 1; And yes, if the table is sparse then this will not necessarily select rows entirely randomly. Depending on the distribution of your rowids this may work well enough and be fast. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing operations
Hi Alberto, On Fri, 26 Jan 2007 19:58:20 +, you wrote: >Hi > >I am trying to create indexes on some tables. I know the database is >just being used by me, and I have memory to optimize things. I am >trying to optimize things doing > >PRAGMA temp_store = MEMORY >PRAGMA cache_size = 100 >PRAGMA synchrinous = OFF >PRAGMA count_changes = 0 > >Is there any other pragma I can use for efficiency? PRAGMA page_size = 4096; Helps to store longish rows without having to overflow to another page. This pragma should be the first statement to execute when a new database is created. Experiment with various sizes for the best result. PRAGMA auto_vacuum = 0; This is the default, but I'm used to set important options explicitly all the time. To avoid surprises and to remind myself what I'm doing. This pragma should be issued before the first table is created. PRAGMA default_cache_size = 100; Will make the cache size stick to the database, so you don't have to repeat it every time it is opened. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last call for bugs...
Heh. I asked for that on a previous mail as well. It would be really nice. Probably it would be the way for more people using it, and thus more bugs being reported about it. Thanks for SQLite! Alberto On 1/26/07, Jason Jobe <[EMAIL PROTECTED]> wrote: How about including using FullTextSearch as a config / make option? FTS Still not working on OSX (for me). -jason On Jan 26, 2007, at 2:33 PM, [EMAIL PROTECTED] wrote: > I plan to release 3.3.12 later today or tomorrow. > If you know about any unreported problems, please > get those bug reports in quickly. Tnx. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > - To unsubscribe, send email to [EMAIL PROTECTED] - -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Last call for bugs...
How about including using FullTextSearch as a config / make option? FTS Still not working on OSX (for me). -jason On Jan 26, 2007, at 2:33 PM, [EMAIL PROTECTED] wrote: I plan to release 3.3.12 later today or tomorrow. If you know about any unreported problems, please get those bug reports in quickly. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Last call for bugs...
I plan to release 3.3.12 later today or tomorrow. If you know about any unreported problems, please get those bug reports in quickly. Tnx. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Indexing on multiple columns
Hi, Fascinating, thanks!! :-) Indeed this query does use the optimizer. :-) If I understand, by rewriting the query to have an AND statement at the top level and getting a simple comparison over to the left we can utilize the index. *cheers* Ben [EMAIL PROTECTED] wrote: SELECT name FROM airports WHERE name>='boston' AND (name>'boston' OR id>421) ORDER BY name, id LIMIT 100 -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - . -- Scenery Home Page: http://scenery.x-plane.com/ Scenery blog: http://xplanescenery.blogspot.com/ Plugin SDK: http://www.xsquawkbox.net/xpsdk/ Scenery mailing list: [EMAIL PROTECTED] Developer mailing list: [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: selecting a random record from a table
Nicolas Williams wrote: On Fri, Jan 26, 2007 at 12:58:13PM -0500, Igor Tandetnik wrote: Nicolas Williams <[EMAIL PROTECTED]> wrote: I can't see why this doesn't work reliably, but if it did it would be O(1). Imagine that you have just two records in your table, with ROWIDs of 1 and 1000. So the inner select retrieves 1000, then you produce a random number X between 0 and 999, then you execute "SELECT ... OFFSET X" against a two-row table. It is very likely that X>=2, in which case you end up with an empty set. But I read that as "goto to offset 2 and return the first row after offset 2." In my test the two rows were rowid==-5 and rowid==5 and offsets -1, 0 and 1 all worked, but offset 2 didn't. I still don't understand that. Moreover, even if it worked it wouldn't be O(1). "OFFSET X" clause works in O(X) time, by actually retrieving and throwing away the first X records. Actually, it should be O(log N) (binary search through the btree). Is that a bug? Is what a bug? What precisely do you feel is wrong with this picture? In a cut-n-paste error I neglected to show what I describe above: that some offsets work and some don't, even though in all cases there are no rows at those offsets and even though in all cases there are rows with rowids higher and lower than the offset I was trying. Check this out: sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -5; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -4; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -3; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -2; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -1; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 0; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 1; bar x sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 2; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 3; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 4; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 5; sqlite> sqlite> SELECT rowid, * FROM foo; rowi bar -- -5x 5 y sqlite> That definitely looks like a bug to me. Nico Nicolas, You need to re-read the documentation for LIMIT and OFFSET at http://www.sqlite.org/lang_select.html In particular this sentence should clarify what is happening: "The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set." HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: Re: selecting a random record from a table
Nicolas Williams <[EMAIL PROTECTED]> wrote: On Fri, Jan 26, 2007 at 12:58:13PM -0500, Igor Tandetnik wrote: Nicolas Williams <[EMAIL PROTECTED]> wrote: I can't see why this doesn't work reliably, but if it did it would be O(1). Imagine that you have just two records in your table, with ROWIDs of 1 and 1000. So the inner select retrieves 1000, then you produce a random number X between 0 and 999, then you execute "SELECT ... OFFSET X" against a two-row table. It is very likely that X>=2, in which case you end up with an empty set. But I read that as "goto to offset 2 and return the first row after offset 2." Why offset 2, when the clause reads, say, OFFSET 500? Also, there are just two rows, at offset 0 and offset 1. What do you mean by "return first row after offset 2"? There are none. You seem to be confusing offset with rowid. These are two very different things, e.g. in a sparse table, or when OFFSET clause is applied to a SELECT statement involving more than one table where the resultset does not have a meaningful concept of a ROWID in the first place. You appear to believe the query you show works like this one: select * from table1 where rowid >= X order by rowid limit 1; That is, selecting a row with the smallest rowid greater than or equal to X (where X is that random number; I didn't repeat the subquery for the sake of clarity). The problem with this statement is that the probability distribution becomes non-uniform. In my example, the row with rowid=1000 is 999 times more likely to be picked than the row with rowid=1. In my test the two rows were rowid==-5 and rowid==5 and offsets -1, 0 and 1 all worked, but offset 2 didn't. I still don't understand that. Offset 0 retrieved the first row. Offset 1 retrieved the second. Offset -1 is meaningless: I suspect any negative offset is treated as offset 0. There is no row at offset 2, so you get none. Moreover, even if it worked it wouldn't be O(1). "OFFSET X" clause works in O(X) time, by actually retrieving and throwing away the first X records. Actually, it should be O(log N) (binary search through the btree). I'm not sure how binary search helps you pick a record at a particular offset, as opposed to a record with a particular key. Again, you seem to be confusing the two. That definitely looks like a bug to me. No, it's a case of false expectations. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Indexing on multiple columns
Hi Y'all,
Is there a compact way (or is it even possible) to use multiple columns
(that I have in my order-by clause) for an operator like > or >=?
I have a database of airports, something like this:
create table airports(
id integer primary key,
name varchar not null);
create index table_idx on table(name,id);
Name isn't necessarily unique...I'd like to do something like
select name from airports where (name,id) > ("boston",421) order by
name, id limit 100;
In other words, Id like to use my index on the key "name/id" for both
the initial start of the query and sorted output.
Thanks!
ben
--
Scenery Home Page: http://scenery.x-plane.com/
Scenery blog: http://xplanescenery.blogspot.com/
Plugin SDK: http://www.xsquawkbox.net/xpsdk/
Scenery mailing list: [EMAIL PROTECTED]
Developer mailing list: [EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Re: Re: selecting a random record from a table
On Fri, Jan 26, 2007 at 12:58:13PM -0500, Igor Tandetnik wrote: > Nicolas Williams <[EMAIL PROTECTED]> wrote: > >I can't see why this doesn't work reliably, but if it did it would be > >O(1). > > Imagine that you have just two records in your table, with ROWIDs of 1 > and 1000. So the inner select retrieves 1000, then you produce a random > number X between 0 and 999, then you execute "SELECT ... OFFSET X" > against a two-row table. It is very likely that X>=2, in which case you > end up with an empty set. But I read that as "goto to offset 2 and return the first row after offset 2." In my test the two rows were rowid==-5 and rowid==5 and offsets -1, 0 and 1 all worked, but offset 2 didn't. I still don't understand that. > Moreover, even if it worked it wouldn't be O(1). "OFFSET X" clause works > in O(X) time, by actually retrieving and throwing away the first X > records. Actually, it should be O(log N) (binary search through the btree). > >Is that a bug? > > Is what a bug? What precisely do you feel is wrong with this picture? In a cut-n-paste error I neglected to show what I describe above: that some offsets work and some don't, even though in all cases there are no rows at those offsets and even though in all cases there are rows with rowids higher and lower than the offset I was trying. Check this out: sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -5; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -4; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -3; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -2; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET -1; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 0; bar y sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 1; bar x sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 2; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 3; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 4; sqlite> SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET 5; sqlite> sqlite> SELECT rowid, * FROM foo; rowi bar -- -5x 5 y sqlite> That definitely looks like a bug to me. Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Re: selecting a random record from a table
Nicolas Williams <[EMAIL PROTECTED]> wrote: Sparse tables are a problem. I've tried this sort of thing but it doesn't work every time for sparse tables: SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET (abs(random()) % (SELECT rowid FROM foo ORDER BY rowid DESC LIMIT 1)) - 1; I can't see why this doesn't work reliably, but if it did it would be O(1). Imagine that you have just two records in your table, with ROWIDs of 1 and 1000. So the inner select retrieves 1000, then you produce a random number X between 0 and 999, then you execute "SELECT ... OFFSET X" against a two-row table. It is very likely that X>=2, in which case you end up with an empty set. Moreover, even if it worked it wouldn't be O(1). "OFFSET X" clause works in O(X) time, by actually retrieving and throwing away the first X records. Can someone explain this: sqlite> select rowid, * from foo; rowi bar -- -5x 5 y sqlite> select * from foo order by rowid limit 1 offset 0; bar x sqlite> select * from foo order by rowid limit 1 offset -1; bar x sqlite> select * from foo order by rowid limit 1 offset 1; bar y sqlite> Is that a bug? Is what a bug? What precisely do you feel is wrong with this picture? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: selecting a random record from a table
On Fri, Jan 26, 2007 at 09:16:41AM -0700, Dennis Cote wrote: > The offset mechanism proposed by Igor earlier is far more efficient as > long as you know the size of the table. You can always get the size from > a count query, which also requires a table scan, but even that is less > expensive than duplicating the table since it is only reading not > writing. On average the offset mechanism will scan half the table to > find the random record. > > count O(N) + select O(N/2) > > If your table is large this will be a lot faster. You can go faster still by using querying the last rowid rather than the count of rows. That's O(1) instead of O(N). And this works because you need the max rowid not the row count, and OP_Last is O(1). SELECT rowid FROM foo ORDER BY rowid DESC LIMIT 1; You should take negative rowids into account too though (switch DESC to ASC to get the first rowid). And use Joe's scheme for quickly selecting a random row. Sparse tables are a problem. I've tried this sort of thing but it doesn't work every time for sparse tables: SELECT * FROM foo ORDER BY rowid DESC LIMIT 1 OFFSET (abs(random()) % (SELECT rowid FROM foo ORDER BY rowid DESC LIMIT 1)) - 1; I can't see why this doesn't work reliably, but if it did it would be O(1). Can someone explain this: sqlite> select rowid, * from foo; rowi bar -- -5x 5 y sqlite> select * from foo order by rowid limit 1 offset 0; bar x sqlite> select * from foo order by rowid limit 1 offset -1; bar x sqlite> select * from foo order by rowid limit 1 offset 1; bar y sqlite> Is that a bug? Nico -- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Segfault when querying deeply nested view
I reproduced this as well on Suse 10 and 3.3.7
Using the following data:
insert into records values ( date('NOW'), 'D/D', 'NPOWER','20','test
acc','123456') ;
insert into records values(date('2006-01-12'), 'D/D', 'NPOWER','20','test
acc','123456') ;
insert into records values(date('2006-11-15'),'D/D', 'NPOWER','20','test
acc','123456') ;
sqlite> select * from summary;
Segmentation fault
-
Running against 3.3.10 appears to function !!!
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> .read segfault.sql
SQL error near line 4: table records already exists
jan|Energy|-40|nov|Energy|-40
jan|Energy|-40|nov|Energy|-40
Andy Chambers <[EMAIL PROTECTED]> wrote: Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the attached sql script to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from energy
union
select 'Phone/Internet' as expense, date, value
from phone_internet
union
select 'Credit Card' as expense, date, value
from credit_cards;
drop view jan;
create view jan as
select 'jan', expense, value
from expense_overview
where date like '%-01';
drop view nov;
create view nov as
select 'nov', expense, value
from expense_overview
where date like '%-11';
drop view summary;
create view summary as
select *
from jan join nov
on (jan.expense = nov.expense);
-- This causes a segfault
select *
from
summary;-
To unsubscribe, send email to [EMAIL PROTECTED]
---
Andy Chambers <[EMAIL PROTECTED]> wrote: Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the attached sql script to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from
[sqlite] sqlite3_column_blob and memory allocation
Hi, I have got two questions concerning the function "sqlite3_column_blob". As sqlite3_column_blob returns "void const*": 1) I assume that the returned pointer will be released by sqlite3, right? So, I do not have to call free() on this pointer. 2) How long is this pointer valid? Till the next call of a sqlite3 function, till a next query etc.? Hartwig - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: selecting a random record from a table
Igor Tandetnik wrote: P Kishor <[EMAIL PROTECTED]> wrote: On 1/25/07, Artem Yankovskiy select * from table1 order by random(id) limit 1 Yes, very nice, thank you. I am not familiar with the "ORDER BY random(col)" idiom. How does this work? (It does work alright). random(anything) produces a random number (the parameter apparently doesn't matter). The query works by associating a random number with every row, then picking whichever one happens to end up with the smallest number. This does work but it requires duplicating the entire table into a temporary table which also has the random number assigned to each row, and then sorting it. This is very expensive for a large table. duplicate O(N) + sort O(N log N) + select O(1) The offset mechanism proposed by Igor earlier is far more efficient as long as you know the size of the table. You can always get the size from a count query, which also requires a table scan, but even that is less expensive than duplicating the table since it is only reading not writing. On average the offset mechanism will scan half the table to find the random record. count O(N) + select O(N/2) If your table is large this will be a lot faster. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Segfault with deeply nested views
Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the sql listed below to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from energy
union
select 'Phone/Internet' as expense, date, value
from phone_internet
union
select 'Credit Card' as expense, date, value
from credit_cards;
drop view jan;
create view jan as
select 'jan', expense, value
from expense_overview
where date like '%-01';
drop view nov;
create view nov as
select 'nov', expense, value
from expense_overview
where date like '%-11';
drop view summary;
create view summary as
select *
from jan join nov
on (jan.expense = nov.expense);
-- This causes a segfault
select *
from summary;
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?
[EMAIL PROTECTED] wrote: "Jef Driesen" <[EMAIL PROTECTED]> wrote: I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not for other cases. So I guess sqlite3_close is still needed. But then it shouldn't return an error, or am I wrong? I don't think any error other than SQLITE_NOMEM is possible for sqlite3_open(). Are you seeing some other kind of error come up? I get SQLITE_CANTOPEN for a non-existing file (and no write permissions to create it). Using sqlite3_close immediately afterwards returns the same value. And sqlite3_errcode returns SQLITE_MISUSE. I think this indicates there is definitely something wrong here. Either the documentation is incorrect (with regards to the usage of sqlite3_close after a failed sqlite3_open), or there is a bug in sqlite3_open/close. I'm using sqlite version 3.3.5 (Ubuntu Edgy package) if that matters. _ Did you know that Windows Live Messenger is accesible on your mobile as from now? http://get.live.com/messenger/mobile - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Do I need to use sqlite3_close after a failed sqlite3_open?
Jay Sprenkle wrote: On 1/24/07, Jef Driesen <[EMAIL PROTECTED]> wrote: Do I need to use sqlite3_close if the call to sqlite3_open indicated an error? The documentation for sqlite3_open says "An sqlite3* handle is returned in *ppDb, even if an error occurs." So I assumed the answer is yes. I never do, since if open fails I assumed the handle wasn't valid. Almost never is still possible... It almost never happens so the consequences of being wrong are pretty small. Did you look at the source for sqlite_open()? I did. sqlite3_close is called automatically for SQLITE_NOMEM, but not for other cases. So I guess sqlite3_close is still needed. But then it shouldn't return an error, or am I wrong? _ All things trendy for Windows Live Messenger ... http://entertainment.msn.be/funwithmessenger - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Segfault when querying deeply nested view
Hi List,
I've found a situation which causes sqlite to segfault.
System Info
-
OS: Debian Etch
Sqlite Version: 3.3.8
Run the attached sql script to see segfault.
I'd be interested to hear of a workaround if anybody knows of one. Of
course, I'll post to the list if I find one.
-- Raw data (RBS)
create table records (
date real,
type text,
description text,
value integer,
acc_name text,
acc_notext
);
-- Direct Debits
drop view direct_debits;
create view direct_debits as
select *
from records
where type = 'D/D';
drop view monthly_direct_debits;
create view monthly_direct_debits as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
group by strftime('%Y-%m', date);
-- Expense Categories ---
drop view energy;
create view energy as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%NPOWER%'
group by strftime('%Y-%m', date);
drop view phone_internet;
create view phone_internet as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%BT DIRECT%'
or description like '%SUPANET%'
or description like '%ORANGE%'
group by strftime('%Y-%m', date);
drop view credit_cards;
create view credit_cards as
select strftime('%Y-%m', date) as date, (-1 * sum(value)) as value
from direct_debits
where description like '%VISA%'
group by strftime('%Y-%m', date);
-- Overview -
drop view expense_overview;
create view expense_overview as
select 'Energy' as expense, date, value
from energy
union
select 'Phone/Internet' as expense, date, value
from phone_internet
union
select 'Credit Card' as expense, date, value
from credit_cards;
drop view jan;
create view jan as
select 'jan', expense, value
from expense_overview
where date like '%-01';
drop view nov;
create view nov as
select 'nov', expense, value
from expense_overview
where date like '%-11';
drop view summary;
create view summary as
select *
from jan join nov
on (jan.expense = nov.expense);
-- This causes a segfault
select *
from summary;-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] selecting a random record from a table
On 1/25/07, P Kishor <[EMAIL PROTECTED]> wrote: 1. given a non-sequential id, select all the ids 2. grab a random id 3. select the row with that id. is there a better way of accomplishing this, one that requires a single round-trip to the db? There's always the random shuffle method. add a column to the table called 'sortorder' assign a random number to that column each time you want the list reordered. Then use Select * from mytable order by sortorder limit 1 That method is useful where you need a specific list in random order, like for card shuffles. If you use a hash you usually get repeats of some elements and some left out.
