[sqlite] upgrading DB from 3.6.23 to 3.7.5

[Sam Carleton]

I am in the process of upgrading my app from using SQLite.net w/ 3.6.23 to
SQLite.net w/ 3.7.5.  When the .Net program starts in a fresh install state,
aka no system db exists and it builds one up via SQL script all works fine.
When it opens an existing 3.6.23 system db, it gets a database lock. and
fails.  But when I open it from the sqlite.exe compiled with 3.7.5, it opens
fine.

Generally speaking, how do I convert the 3.6.23 db to 3.7.5?

I am sure it is bad form, but attached is one of the 3.6.23 DB, it is only
12K.

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Simon Slavin]

On 11 Feb 2011, at 2:37am, Yuzem wrote:

> Simon Slavin-3 wrote:
>> 
> 
>> By looking at the file on disk ?  Are you taking into account the journal
>> file ? 
>> 
> 
> Yes, I do all the counts

So if I deleted one record and created another you wouldn't spot it ?

> and save the data to a file and then if the file is
> newer then the database I use the file else I count again.

Your process is trustworthy only when you are certain that the database file is 
not currently open.  If there's a chance that some application may be modifying 
the file when you check these things then the results you get may not be 
up-to-date.

The only way to correctly tell if the data in the database has changed is to 
use SQLite calls.  Also, your operating system does not update the file 
attributes every time the contents of the file changes.  It will often wait for 
a change of sector or a buffer to be flushed.

> No, I am not taking the journal file into account, I don't know what you
> mean by that.

SQLite uses a journal file to temporarily store change to the database.  You 
will find this journal file appearing in the same directory as the database 
file if you have the database open and have made changes.  These changes will 
eventually be saved in the database file -- definitely when you close all the 
SQLite handles to that file -- but until then you have no way of knowing what 
data SQLite considers is actually in the database apart from using SQLite calls.

Read here, especially sections 2.1 to 2.3, about the temporary files SQLite 
makes:

http://www.sqlite.org/tempfiles.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

On 11/02/2011, at 1:37 PM, Yuzem wrote:

> For example lets say I have the following data:
> 1|director|1
> 2|director|2
> 3|director|1
> 
> In this example the total count for directors is 2, I have two distinct 
> directors.
> In the table "Capacity Statistics" I will have:
> director|2
> 
> The triggers you made add/subtract 1 from "Capacity Statistics" on
> insert/delete on "movies people"
> What happens if I add the following to "movies people"?
> 4|director|2
> 
> The trigger should add 1 to "Capacity Statistics":
> director|3
> 
> But there are still 2 directors:
> 1|director|1
> 2|director|2
> 3|director|1
> 4|director|2

Oh, I see. I was counting the total number of each unique capacity (including 
director, writers), but you want the number of each unique (capacity, person). 
No problem. We'll just add a People column to the statistics, and change the 
uniqueness constraint.

Replace my earlier "Capacity Statistics" and triggers with this:

begin immediate
;
create table "Capacity People Statistics"
(   ID integer primary key references "Capacity" (ID) on delete cascade
,   People_ID integer not null references "People" (ID) on delete cascade
,   Count integer not null
,   unique (ID, People_ID)
)
;
insert into "Capacity People Statistics" (ID, People_ID, Count)
select Capacity_ID, People_ID, count(*) from "Movie People"
group by Capacity_ID, People_ID having Count > 0
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
new.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
create trigger "Movie People delete"
on "Movie People"
after delete
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where ID = old.Capacity_ID and People_ID = old.People_ID
)
;
end
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID, People_ID
begin
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
old.Capacity_ID
,   old.People_ID
,   (   select coalesce(Count, 0) - 1 from "Capacity People Statistics"
where ID = old.Capacity_ID and People_ID = old.People_ID
)
;
insert or replace into "Capacity People Statistics" (ID, People_ID, Count)
select
new.Capacity_ID
,   new.People_ID
,   (   select coalesce(Count, 0) + 1 from "Capacity People Statistics"
where ID = new.Capacity_ID and People_ID = new.People_ID
)
;
end
;
commit
;


Tom
BareFeetWare

--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Yuzem]

Simon Slavin-3 wrote:
> 
> By looking at the file on disk ?  Are you taking into account the journal
> file ? 
> 
Yes, I do all the counts and save the data to a file and then if the file is
newer then the database I use the file else I count again.
No, I am not taking the journal file into account, I don't know what you
mean by that.


BareFeetWare-2 wrote:
> 
> Does tis answer your needs? If not, please explain further, but it will
> probably only require modifying a constraint in the schema I proposed,
> rather than denormalizing or partitioning. 
> 
I will try to explain it.
I have the table "movies people" with columns "movie_ID, capacity_ID,
people_ID"
I understand that all rows are unique but people_ID isn't unique.
For example lets say I have the following data:
1|director|1
2|director|2
3|director|1

In this example the total count for directors is 2, I have two distinct
directors.
In the table "Capacity Statistics" I will have:
director|2

The triggers you made add/subtract 1 from "Capacity Statistics" on
insert/delete on "movies people"
What happens if I add the following to "movies people"?
4|director|2

The trigger should add 1 to "Capacity Statistics":
director|3

But there are still 2 directors:
1|director|1
2|director|2
3|director|1
4|director|2


BareFeetWare-2 wrote:
> 
> Erm ... there are also movies which have more than one person directing. 
> You need to copy across the rowid from the MoviePeople table, and delete
> just based on that particular record. 
> 
Yes, of-course.
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30898156.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] bind or column index out of range

[Jay A. Kreibich]

On Thu, Feb 10, 2011 at 11:24:34PM -0200, Fabr?cio Cruz Casarini scratched on 
the wall:
> Guys,
> 
> I am developing an application in php-gtk and I'm using sqlite3.
> 
> I'm accessing the database using adodb class.
> 
> Whenever I try to add a record to the table empty get the error "bind or
> column index out of range".
> 
> What is the solution to this problem?

  Check to be sure you're not trying to bind something to index 0.

  Bind indexes start at 1, not 0.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] bind or column index out of range

[Fabrício Cruz Casarini]

Guys,

I am developing an application in php-gtk and I'm using sqlite3.

I'm accessing the database using adodb class.

Whenever I try to add a record to the table empty get the error "bind or
column index out of range".

What is the solution to this problem?

[]'s

Fabrício Cruz Casarini
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

On 11/02/2011, at 11:30 AM, Simon Slavin wrote:

> Come to think of it, what's really happening here is a FOREIGN KEY situation.

Yes, that's why I have the foreign keys (ie "references") in the schema. So, 
for instance, if you delete a movie, all of the actors, directors etc 
associated with that movie are automatically deleted, but the same people are 
kept for other movies.

SQL takes care of all the nitty gritty stuff, so we don't have to reinvent the 
wheel in application code.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Jay A. Kreibich]

On Thu, Feb 10, 2011 at 06:02:01PM -0500, Samuel Adam scratched on the wall:
> On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich  wrote:

>>   SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;
>
> s/'days'/' days'/

  Indeed.  3.6.x does not require the space, but the newer 3.7 verions do.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Simon Slavin]

On 11 Feb 2011, at 12:26am, BareFeetWare wrote:

> On 11/02/2011, at 11:11 AM, Simon Slavin wrote:
> 
>> Erm ... there are also movies which have more than one person directing.  
>> You need to copy across the rowid from the MoviePeople table, and delete 
>> just based on that particular record.
> 
> Adding to what Simon said:
> 
> The schema I posted allows for multiple directors for each movie, the same 
> person having multiple capacities in the same movie (eg writer, director and 
> actor) etc. Any changes to the model and what's allowed (ie constraints) 
> should be done there and not in your application layer, IMNSHO.
> 
> If you want to delete or insert, based on text (eg person's name), I suggest 
> NOT getting your application to get the matching rowid, then reinjected it 
> into a second SQL call. Do it all in one SQL transaction

Come to think of it, what's really happening here is a FOREIGN KEY situation.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

On 11/02/2011, at 11:11 AM, Simon Slavin wrote:

> Erm ... there are also movies which have more than one person directing.  You 
> need to copy across the rowid from the MoviePeople table, and delete just 
> based on that particular record.

Adding to what Simon said:

The schema I posted allows for multiple directors for each movie, the same 
person having multiple capacities in the same movie (eg writer, director and 
actor) etc. Any changes to the model and what's allowed (ie constraints) should 
be done there and not in your application layer, IMNSHO.

If you want to delete or insert, based on text (eg person's name), I suggest 
NOT getting your application to get the matching rowid, then reinjected it into 
a second SQL call. Do it all in one SQL transaction, such as:

delete from "Movie People"
where Movie_ID = (select Movie_ID from Movies where Title = 'Back to the 
Future')
and People_ID = (select ID from People where Name = 'Eric Stoltz')
and Capacity_ID = (select ID from Capacity where Name = 'actor')

or:

update "Movie People"
set People_ID = (select ID from People where Name = 'Michael J Fox')
where Movie_ID = (select Movie_ID from Movies where Title = 'Back to the 
Future')
and People_ID = (select ID from People where Name = 'Eric Stoltz')
and Capacity_ID = (select ID from Capacity where Name = 'actor')

By the way, how are you getting the raw data? What URL or RSS feed or whatever?

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Simon Slavin]

On 10 Feb 2011, at 11:55pm, Yuzem wrote:

> That's the beauty of using a additional table. I make the column unique in
> the the extra table and then on any insert in "movies people" I insert in
> for example the directors table and any duplicate will be automatically
> rejected.
> 
> The same problem exist on delete, the deleted entry may not be unique.
> 
> For example:
> movie1|director|people1
> movie2|director|people1

Erm ... there are also movies which have more than one person directing.  You 
need to copy across the rowid from the MoviePeople table, and delete just based 
on that particular record.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

> BareFeetWare-2 wrote:
>> 
>> In that case, you should cache the counts in a separate table or two. That
>> has a negligible overhead when you add a movie (which is infrequent), and
>> basically no overhead when viewing (which is frequent).
> I am doing that but in the application level, the down side is that I am
> updating the cache on any change, I check the modified time of the database.

I strongly suggest doing as much as you can at the SQL level, especially where 
it concerns the integrity of your data. You should find it faster, more 
reliable, self contained and logical.

> BareFeetWare-2 wrote:
>> 
>> Do that with triggers (which are a good thing in this context), to change
>> the relevant count when an insert, delete or update occurs in the "Movie
>> People" table. Something like:
>> 
> I think, not sure, that there will be a problem with that code.
> I am not an expert so I had to stare that code some time to understand it.
> If I am correct you are adding and subtracting 1 on every insert or delete.

Yes, and doing a delete/insert combo for an update (ie if a person's capacity 
in a movie changes).

> The problem is that an insert may not be unique so before augmenting the
> counter you have to check if it is unique and therefore you would have to
> run a count(distinct col) on every insert which would be overkill.

If I understand you correctly, the non-unique criteria is already handled by 
the schema design. The "Movie People" table allows the same person to be listed 
as the director for multiple movies. The constraints on the "Movie People" only 
require no nulls and unique combinations of Movie, Capacity, People (ie the 
same person can't be listed as director for the same movie twice, but can be 
listed as director for two movies). Because it uses the "unique" constraint for 
this, SQLite automatically builds an index "Movie People"(Movie_ID, 
Capacity_ID, People_ID), so that whenever you try to insert a new row, it 
quickly checks if it already exists. You can throw new non-unique rows at it 
with "insert or ignore" if you just want it to ignore duplicates, which then 
won't trigger the count increase because nothing was inserted. Or use plain old 
"insert" if you want to be alerted to any attempted unique violations.

Does tis answer your needs? If not, please explain further, but it will 
probably only require modifying a constraint in the schema I proposed, rather 
than denormalizing or partitioning.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Simon Slavin]

On 10 Feb 2011, at 11:55pm, Yuzem wrote:

> I check the modified time of the database.

By looking at the file on disk ?  Are you taking into account the journal file ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] wal file size

[Richard Hipp]

On Thu, Feb 10, 2011 at 6:45 PM, Michael Barton wrote:

> Is there something I need to do to keep my sqlite WAL files from
> getting huge with 3.7.5?
>
> -rw--- 1 swift swift 152M 2011-02-10 23:43
> b7fa56688b61c70ef29ed2ad94b7beeb.db
> -rw--- 1 swift swift  19M 2011-02-10 23:43
> b7fa56688b61c70ef29ed2ad94b7beeb.db-shm
> -rw--- 1 swift swift 2.4G 2011-02-10 23:43
> b7fa56688b61c70ef29ed2ad94b7beeb.db-wal
>
> There the database is 152MB and the wal file is 2.4GB.  This happens
> when we're benchmarking, so we have like 8 processes trying to write
> concurrently or whatever.  Write transactions also slow down as the
> wal file gets bigger.  I've read through the couple of threads on
> this, but never saw if there was a solution.
>

There needs to be a time when there are no read or write transactions using
the WAL so that it can reset, and you need to run PRAGMA wal_checkpoint
during that time.  Otherwise the WAL will grow without bound.

See also the RESET option on PRAGMA wal_checkpoint that will appear in
version 3.7.6:

http://www.sqlite.org/draft/pragma.html#pragma_wal_checkpoint
http://www.sqlite.org/draft/c3ref/wal_checkpoint_v2.html




>
> -- Mike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Yuzem]

BareFeetWare-2 wrote:
> 
> In that case, you should cache the counts in a separate table or two. That
> has a negligible overhead when you add a movie (which is infrequent), and
> basically no overhead when viewing (which is frequent).
I am doing that but in the application level, the down side is that I am
updating the cache on any change, I check the modified time of the database.


BareFeetWare-2 wrote:
> 
> Do that with triggers (which are a good thing in this context), to change
> the relevant count when an insert, delete or update occurs in the "Movie
> People" table. Something like:
> 
I think, not sure, that there will be a problem with that code.
I am not an expert so I had to stare that code some time to understand it.
If I am correct you are adding and subtracting 1 on every insert or delete.

The problem is that an insert may not be unique so before augmenting the
counter you have to check if it is unique and therefore you would have to
run a count(distinct col) on every insert which would be overkill.

That's the beauty of using a additional table. I make the column unique in
the the extra table and then on any insert in "movies people" I insert in
for example the directors table and any duplicate will be automatically
rejected.

The same problem exist on delete, the deleted entry may not be unique.

For example:
movie1|director|people1
movie2|director|people1

-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30897526.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] wal file size

[Michael Barton]

Is there something I need to do to keep my sqlite WAL files from
getting huge with 3.7.5?

-rw--- 1 swift swift 152M 2011-02-10 23:43
b7fa56688b61c70ef29ed2ad94b7beeb.db
-rw--- 1 swift swift  19M 2011-02-10 23:43
b7fa56688b61c70ef29ed2ad94b7beeb.db-shm
-rw--- 1 swift swift 2.4G 2011-02-10 23:43
b7fa56688b61c70ef29ed2ad94b7beeb.db-wal

There the database is 152MB and the wal file is 2.4GB.  This happens
when we're benchmarking, so we have like 8 processes trying to write
concurrently or whatever.  Write transactions also slow down as the
wal file gets bigger.  I've read through the couple of threads on
this, but never saw if there was a solution.

-- Mike
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

Oops, I should have said old instead of new in a couple of places:

> begin immediate
> ;
> create table "Capacity Statistics"
> ( ID integer primary key unique references "Capacity" (ID) on delete 
> cascade
> , Count integer not null
> )
> ;
> insert into "Capacity Statistics" (ID, Count) select Capacity_ID, count(*) 
> from "Movie People" group by Capacity_ID having Count > 0
> ;
> create trigger "Movie People insert"
> on "Movie People"
> after insert
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
> Statistics" where ID = new.Capacity_ID)
> ;
> end
> ;
> create trigger "Movie People delete"
> on "Movie People"
> after insert
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
> Statistics" where ID = old.Capacity_ID)

Should be:

select old.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)

> ;
> end
> ;
> create trigger "Movie People update"
> on "Movie People"
> after update of Capacity_ID
> begin
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
> Statistics" where ID = new.Capacity_ID)
> ;
> insert or replace into "Capacity Statistics" (ID, Count)
> select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
> Statistics" where ID = old.Capacity_ID)

Should be:

select old.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)

> ;
> end
> ;
> commit
> ;


Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[fearless_fool]

@igor, @jay (and a good assist from @samuel):

Cool beans -- that works and is cleaner than my hack.  Thank you.  

FYA, the final form of this query (to be used as a sub-query throughout much
of our system) is:


> SELECT DATE('#{start_time.to_s(:db)}', (thousands.digit * 1000 +
> hundreds.digit * 100 + tens.digit * 10 + units.digit) || ' days') AS date
>   FROM digits AS units
> INNER JOIN digits AS tens
> INNER JOIN digits AS hundreds
> INNER JOIN digits AS thousands
>  WHERE (thousands.digit * 1000 + hundreds.digit * 100 + tens.digit *
> 10 + units.digit) < #{ndays}
>   ORDER BY date
> 
I'll leave the actual result as an exercise to the reader, but it's a handy
function to have around.

- ff
-- 
View this message in context: 
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897388.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

On 11/02/2011, at 9:40 AM, Yuzem wrote:

> Yes, in my application I have in the sidebar all those sections (movies,
> years, tags, keywords, actors, directors, writers, etc...) and I count each
> one, how many movies, how many years, etc...
> It isn't very slow if I update only one item but the problem is when I have
> to update the entire list, this uses a lot of cpu and takes some time and I
> have to do it every time a movie is added.
> http://3.bp.blogspot.com/_EbNPUgfUDXs/TIpif4U1III/A2A/YFSGOAdpOGA/s1600/nested-brosers.jpg
> Here there is an old screenshot from the sidebar.

In that case, you should cache the counts in a separate table or two. That has 
a negligible overhead when you add a movie (which is infrequent), and basically 
no overhead when viewing (which is frequent).

Do that with triggers (which are a good thing in this context), to change the 
relevant count when an insert, delete or update occurs in the "Movie People" 
table. Something like:

begin immediate
;
create table "Capacity Statistics"
(   ID integer primary key unique references "Capacity" (ID) on delete 
cascade
,   Count integer not null
)
;
insert into "Capacity Statistics" (ID, Count) select Capacity_ID, count(*) from 
"Movie People" group by Capacity_ID having Count > 0
;
create trigger "Movie People insert"
on "Movie People"
after insert
begin
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
Statistics" where ID = new.Capacity_ID)
;
end
;
create trigger "Movie People delete"
on "Movie People"
after insert
begin
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)
;
end
;
create trigger "Movie People update"
on "Movie People"
after update of Capacity_ID
begin
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) + 1 from "Capacity 
Statistics" where ID = new.Capacity_ID)
;
insert or replace into "Capacity Statistics" (ID, Count)
select new.Capacity_ID, (select coalesce(Count, 0) - 1 from "Capacity 
Statistics" where ID = old.Capacity_ID)
;
end
;
commit
;

By contrast, I suggest it would be a bad idea to denormalize and partition your 
data (ie separate directors and writers tables) just for the sake of tracking 
the count. The solution above maintains a normalized database, gives you the 
flexibility to add or remove Capacities in data rather than having to change 
the schema, and better reflects the real data model.


Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Samuel Adam]

On Thu, 10 Feb 2011 17:55:57 -0500, Jay A. Kreibich  wrote:

> On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the  
> wall:
>>
>> Meh.  I have a solution, but I don't like it very much because it feels
>> convoluted:
>>
>> > sqlite> select strftime('%Y-%m-%d', julianday('2011-01-01') + digit)
>> > as d from digits;
>
>> This takes advantage that JULIANDAY is in units of days.  I'll go with  
>> this
>> unless some guru suggests something cleaner.
>
>
>   SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;

s/'days'/' days'/

(Thanks for the tip on quotes; I should have added  
http://www.sqlite.org/lang_expr.html and  
http://www.sqlite.org/lang_keywords.html .)

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Igor Tandetnik]

On 2/10/2011 5:17 PM, fearless_fool wrote:
> I'd like to write a query that generates ten consecutive days starting at
> "2011-02-05" (for example), but I believe I'm having trouble with quoting.
> Assume I have a table of ten digits such as:
>
> CREATE TABLE "digits" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
> "digit" integer)
> INSERT INTO "digits" ("digit") VALUES (0)
> INSERT INTO "digits" ("digit") VALUES (1)
> INSERT INTO "digits" ("digit") VALUES (2)
> INSERT INTO "digits" ("digit") VALUES (3)
> INSERT INTO "digits" ("digit") VALUES (4)
> INSERT INTO "digits" ("digit") VALUES (5)
> INSERT INTO "digits" ("digit") VALUES (6)
> INSERT INTO "digits" ("digit") VALUES (7)
> INSERT INTO "digits" ("digit") VALUES (8)
> INSERT INTO "digits" ("digit") VALUES (9)
>
> A query that does NOT work is:
>
> sqlite>  SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM
> digits AS units;

select date('2011-02-05 21:42:20', units.digit || ' days') as d from 
digits as units;

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Jay A. Kreibich]

On Thu, Feb 10, 2011 at 02:47:29PM -0800, fearless_fool scratched on the wall:
> 
> Meh.  I have a solution, but I don't like it very much because it feels
> convoluted: 
> 
> > sqlite> select strftime('%Y-%m-%d', julianday('2011-01-01') + digit)
> > as d from digits;

> This takes advantage that JULIANDAY is in units of days.  I'll go with this
> unless some guru suggests something cleaner.


  SELECT date( '2011-01-01', digit || 'days' ) AS d FROM digits;


-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[fearless_fool]

Meh.  I have a solution, but I don't like it very much because it feels
convoluted: 


> sqlite> select strftime('%Y-%m-%d', julianday('2011-01-01') + digit) as d
> from digits;
> 2011-01-01
> 2011-01-02
> 2011-01-03
> 2011-01-04
> 2011-01-05
> 2011-01-06
> 2011-01-07
> 2011-01-08
> 2011-01-09
> 2011-01-10
> 
This takes advantage that JULIANDAY is in units of days.  I'll go with this
unless some guru suggests something cleaner.

-- 
View this message in context: 
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897133.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Stephan Beal]

On Thu, Feb 10, 2011 at 11:22 PM, Samuel Adam  wrote:

> Thanks for actually looking this up.


i didn't look THAT closely, as you found out:


> Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable:
> http://www.php.net/manual/en/pdo.lobs.php


Doh!

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Yuzem]

Simon Slavin-3 wrote:
> 
> How much slower.  Did you make an index SQLite could use for that query ?
> 
Using distinct isn't slow, it is what I would expect but count(*) is
incredibly fast, it is instantaneous no matter how large is the table.
Yes, I tried with an index.


BareFeetWare-2 wrote:
> 
> Or you can try this:
> 
> select count(*) from People
> where ID in
> ( select People_ID from "Movie People" where Capacity_ID =
> (select ID from Capacity where Name = 'director')
> );
> 
> But I expect you'll get the same performance. 
> 
Yes, almost the same performance.


BareFeetWare-2 wrote:
> 
> You don't want to denormalize and have separate director and writer tables
> etc. That will get ugly. 
> 
That's what I thought.


Petite Abeille-2 wrote:
> 
> Right... looking at something like the casting information in IMdb (actor,
> director, writer, etc), there are about 25M movie + role + person
> combinations. Which, while not huge, starts to get taxing when queried on
> a lowly laptop.
> 
> For example, out of these 25M rows, about 1.2M represent directors (~217K)
> in movies (~1M). 
> 

Yes, in my application I have in the sidebar all those sections (movies,
years, tags, keywords, actors, directors, writers, etc...) and I count each
one, how many movies, how many years, etc...
It isn't very slow if I update only one item but the problem is when I have
to update the entire list, this uses a lot of cpu and takes some time and I
have to do it every time a movie is added.
http://3.bp.blogspot.com/_EbNPUgfUDXs/TIpif4U1III/A2A/YFSGOAdpOGA/s1600/nested-brosers.jpg
Here there is an old screenshot from the sidebar. 


Petite Abeille-2 wrote:
> 
> Assuming a movie_cast table [1] and a cast dimension [2], you could record
> the distinct count for each cast in the dimension once, and store it. That
> way you don't have to recompute it over and over.
> 
Yes, I am caching all counts in the application level but I have to update
it every time the database changes so I have to update it at least every
time I add a movie.


Petite Abeille-2 wrote:
> 
> Alternatively, you could indeed partition that bulky movie_cast table by
> its cast type, which will in effect reduce the search space to at most ~9M
> (movies by actors, ~1.3M movies, ~1.3M actors). 
> 
> That said, even partitioning will not help you much here as you still have
> to search through ~9M records to figure out how many distinct actors you
> have.
> 
Actually, I already have different tables for directors, writers, etc...


Petite Abeille-2 wrote:
> 
> So... perhaps best to cheat :)
> 
> One way to cheat is to precompute the answer by, for example, adding a
> is_* flag on your person table:
> 
> update person set is_actor = ( select count( distinct person_id ) from
> movie_actor where movie_actor.person_id = person.id );
> 
> The person table is much smaller (~3M) and easier to query in bulk:
> 
> explain query plan
> select count( * ) from person where is_actor = 1;
> 0|0|0|SEARCH TABLE person USING COVERING INDEX person_is_actor
> (is_actor=?) (~1596808 rows)
> 
> 1310119
> CPU Time: user 0.256286 sys 0.000257
> 
> There you go :)
> 
That's a good idea, I wonder if it isn't better to use a different table to
store only the ids for the directors, etc, as I said before.
I would end up with these tables:
movies 
people
movies_directors (movieId, peopleId)
movies_writers (movieId, peopleId)
directors (peopleId)
writers (peopleId)

Then I can count directors with:
SELECT count(*) FROM directors;


Petite Abeille-2 wrote:
> 
> This is where I wish SQLite could have bitmap indices:
> http://en.wikipedia.org/wiki/Bitmap_index
> 
That seems what I need.
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30897078.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[fearless_fool]

Samuel Adam-2 wrote:
> 
> 
> http://www.sqlite.org/lang_datefunc.html
> http://www.sqlite.org/lang_select.html
> (and a few others)
> Very truly,
> 
Hi SA:

So I've been reading those very pages carefully.  And since the docs say


> Note that "±NNN months" works by rendering the original date into the
> -MM-DD format, adding the ±NNN to the MM month value, then normalizing
> the result.
> 
I'm even more perplexed when the following one-liner isn't properly
normalized:


> sqlite> SELECT DATE("2011-01-32");
> 
> 
It returns a blank record rather than giving "2011-02-01", contrary to what
the documentation suggests.  What am I missing?
-- 
View this message in context: 
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30897057.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Jay A. Kreibich]

On Thu, Feb 10, 2011 at 05:27:02PM -0500, Samuel Adam scratched on the wall:
> On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool  wrote:
> 
> [snip]
> > A query that does NOT work is:
> >
> > sqlite> SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM
> > digits AS units;
> 
> http://www.sqlite.org/lang_datefunc.html
> 
> http://www.sqlite.org/lang_select.html

  ...and quote your strings correctly.  SQL string literals (such
  as the two in this select) use (').  (") is reserved for labels
  (table names and column names).

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] generating mini-calendar: DATE and quoting...

[Samuel Adam]

On Thu, 10 Feb 2011 17:17:29 -0500, fearless_fool  wrote:

[snip]
> A query that does NOT work is:
>
> sqlite> SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM
> digits AS units;

http://www.sqlite.org/lang_datefunc.html

http://www.sqlite.org/lang_select.html

(and a few others)

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Thu, 10 Feb 2011 16:38:40 -0500, Stephan Beal   
wrote:

> On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam  wrote:
>
>>
>>* Make sure the binding is done as BLOB and not TEXT.  PDO  
>> probably
>> has
>> its own flags defined for this.  This is the part that tells SQLite
>> whether you are inserting TEXT or BLOB.
>>
>
> http://www.php.net/manual/en/pdostatement.bindparam.php
>
> When inserting the GIF, the 3rd argument to bindParam() must be
> PDO::PARAM_LOB  (not BLOB, interestingly). The default is PDO::PARAM_STR,
> which almost certainly results in the OP's frustration.

Thanks for actually looking this up.  But on Sun, 06 Feb 2011 10:53:05  
-0500, Yves Goergen  actually said he was  
using “a PDO method to execute a prepared statement with an array of  
values to be used as parameters”.  That appears to be this:

http://www.php.net/manual/en/pdostatement.execute.php

input_parameters

An array of values with as many elements as there are bound
parameters in the SQL statement being executed. All values are
treated as PDO::PARAM_STR.

In other words, he is using a PHPism which hands off only one type and  
then blaming SQLite for treating his data as the wrong type.  (That  
interface is bizarre, by the way:  Either you can bind the parameters  
yourself and then call it with no arguments, or bind nothing yourself and  
pass it an array of values which will be treated as text.  Mr. Goergen  
appears to be doing the latter.)

Worse, PDO::PARAM_LOB is for binding a stream and not a regular variable:
http://www.php.net/manual/en/pdo.lobs.php
And it is for “objects [] either textual or binary in nature” (Id.).  As  
usual with PHP, the docs are very imprecise on what the database will  
actually get.  “Example #14[:] Inserting an image into a database” thereby  
(based on an fopen() stream) looks promising, but still does not document  
the type told to the database.  If there is a way anywhere in PDO to  
explicitly hand SQLite a BLOB type and/or so do from a variable, the PDO  
constants list
http://www.php.net/manual/en/pdo.constants.php
fails to disclose it.

PHP’s SQLite3 class does have a SQLITE3_BLOB flag.  But that is not what  
Mr. Goergen is using.

Easiest solution:  The UPDATE and triggers suggested on Mon, 07 Feb 2011  
03:16:54 -0500 by Philip Graham Willoughby  
.  Best solution:  Reading and  
understanding the docs, particularly as to SQLite’s type system, and then  
making a few thoughtful decisions as to architecture.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] generating mini-calendar: DATE and quoting...

[fearless_fool]

I'd like to write a query that generates ten consecutive days starting at
"2011-02-05" (for example), but I believe I'm having trouble with quoting. 
Assume I have a table of ten digits such as:

CREATE TABLE "digits" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"digit" integer) 
INSERT INTO "digits" ("digit") VALUES (0)
INSERT INTO "digits" ("digit") VALUES (1)
INSERT INTO "digits" ("digit") VALUES (2)
INSERT INTO "digits" ("digit") VALUES (3)
INSERT INTO "digits" ("digit") VALUES (4)
INSERT INTO "digits" ("digit") VALUES (5)
INSERT INTO "digits" ("digit") VALUES (6)
INSERT INTO "digits" ("digit") VALUES (7)
INSERT INTO "digits" ("digit") VALUES (8)
INSERT INTO "digits" ("digit") VALUES (9)

A query that does NOT work is:

sqlite> SELECT DATE("2011-02-05 21:42:20", "units.digit DAY") AS d FROM
digits AS units;








... which returns ten blank records.  Using units.digit as the sole argument
shows that the counting is working:

sqlite> SELECT DATE(units.digit) AS d FROM digits AS units;
-4713-11-24
-4713-11-25
-4713-11-26
-4713-11-27
-4713-11-28
-4713-11-29
-4713-11-30
-4713-12-01
-4713-12-02
-4713-12-03

... but of course I'd like something more current.  Is there some obvious
quoting that I'm missing?

Thanks in advance.

- ff

PS: I know you can do this with stored functions, but this is a small
snippet of a larger system in which a stored function isn't appropriate.
-- 
View this message in context: 
http://old.nabble.com/generating-mini-calendar%3A-DATE-and-quoting...-tp30896927p30896927.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Stephan Beal]

On Sun, Feb 6, 2011 at 2:36 PM, Samuel Adam  wrote:

>
>* Make sure the binding is done as BLOB and not TEXT.  PDO probably
> has
> its own flags defined for this.  This is the part that tells SQLite
> whether you are inserting TEXT or BLOB.
>

http://www.php.net/manual/en/pdostatement.bindparam.php

When inserting the GIF, the 3rd argument to bindParam() must be
PDO::PARAM_LOB  (not BLOB, interestingly). The default is PDO::PARAM_STR,
which almost certainly results in the OP's frustration.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Samuel Adam]

On Thu, 10 Feb 2011 15:21:57 -0500, Yves Goergen  
 wrote:

> On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote:
>> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby
>>  wrote:
>>> What about:
>>>
>>> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);
>>
>> Y’know the urban legend about the folks at the restaurant who design a
>> complicated plan for switching the contents of salt and pepper shakers
>> which have their hats juxtaposed, and then a passer-by suggests just
>> switching the caps?
>
> I don't know that story nor do I understand it. But would that trigger
> thing work? That would be something I could set up easily.

Off-topic not-quite-analogy by way of jest at self.  I suggested a  
temporary workaround which was a hair’s breadth from permanently fixing  
your existing data; Mr. Willoughby pointed that out with the above-quoted  
UPDATE line.  As to Mr. Willoughby’s triggers (which you snipped), yes,  
they look quite correct and you should try them.

> It's not that important issue for my anymore. I've simply decided to not
> store binary data in the SQLite database in this application anymore but
> instead write it to disk. Storing files in the database should only be
> of interest here if you cannot write to disk, but when using SQLite you
> obviously can do that.

For the archives, I emphasize hereby again that SQLite is not 8-bit  
crippled.  I insert and retrieve ordinary-sized images, audio, PDFs,   
 from SQLite on a regular basis; I don’t have problems, because I bind such  
things as BLOB.  I also oft use core function length() on BLOB values,  
including those containing NUL bytes, again without problems.

If I saw a bug in SQLite, I’d call it that point-blank.  Indeed, I did  
take the opportunity to raise what I argue is a documented design flaw  
(which only affects corner cases and is absolutely irrelevant to your  
problem).  But your problem simply was not caused by any incorrectness in  
SQLite.  As amply explained with reference to docs, you told SQLite you  
were inserting TEXT; so length() complied with its documented behavior for  
measuring the length of TEXT:  It counted Unicode characters, *not bytes*,  
up to and not including the first U+.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

[Stephan Beal]

On Thu, Feb 10, 2011 at 9:59 PM, Zaryab M. Munir wrote:

> [zm]: No there is no other function read() and I did compile glibc.  I want
> to check if it is static or dynamic.  Is static linking a requirement ?
>  Thanks.
>

If you're linking to libXXX.so then it's dynamically linked. sqlite3 can be
statically linked just fine (i know of projects which do that).

To see which libs your app is linked to use 'ldd':

[stephan@cheyenne:~/cvs/fossil/cpdo]$ ldd ./certify
linux-vdso.so.1 =>  (0x7fff6850a000)
libcpdo.so => ./libcpdo.so (0x7fbeb3c62000)
libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x7fbeb393f000)
libm.so.6 => /lib/libm.so.6 (0x7fbeb36bb000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x7fbeb34a5000)
libc.so.6 => /lib/libc.so.6 (0x7fbeb3122000)
libpthread.so.0 => /lib/libpthread.so.0 (0x7fbeb2f04000)
libsqlite3.so.0 => /usr/lib/libsqlite3.so.0 (0x7fbeb2c6f000)
libmysqlclient.so.16 => /usr/lib/libmysqlclient.so.16 (0x7fbeb2861000)
/lib64/ld-linux-x86-64.so.2 (0x7fbeb3e8)
libdl.so.2 => /lib/libdl.so.2 (0x7fbeb265c000)
libcrypt.so.1 => /lib/libcrypt.so.1 (0x7fbeb2423000)
libnsl.so.1 => /lib/libnsl.so.1 (0x7fbeb2209000)
libz.so.1 => /lib/libz.so.1 (0x7fbeb1ff)

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Outer query returning results not found in subquery

[Shane Harrelson]

On Thu, Feb 10, 2011 at 2:29 PM, Igor Tandetnik  wrote:
> On 2/10/2011 2:17 PM, Dan Kubb wrote:
>> Database setup:
>>
>>      CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER 
>> NOT NULL);
>>
>>      INSERT INTO "test" ("letter", "number") VALUES('b', 1);
>>      INSERT INTO "test" ("letter", "number") VALUES('a', 2);
>>      INSERT INTO "test" ("letter", "number") VALUES('c', 2);
>>
>> Initial query:
>>
>>      SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 
>> 1;
>>
>> This returns "a|2", the second row from the results as you would
>> expect given that we're sorting on the letter then the number.
>> However, here's what I did not expect:
>>
>> Initial query as a subquery:
>>
>>      SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" 
>> ORDER BY "letter", "number" LIMIT 1) AS "test";
>>
>> This returns "1"
>
> Yes, looks like a bug. If you drop DISTINCT, it returns a single row
> with the value 2.
>
> My guess is, DISTINCT is internally implemented as ORDER BY, and that
> overrules ORDER BY found in the subquery.
> --
> Igor Tandetnik
>


Thanks for the report and recreate.   A ticket was opened here:

http://www.sqlite.org/src/tktview/752e1646fcc7b649184e49783577a7feb5f7fc9c

I was able to recreate the problem.   Additionally, if query
optimizations are disabled, then the correct result is returned, so
this is most likely and issue with the query flattener.

-Shane
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

[Zaryab M. Munir]

--- On Thu, 2/10/11, Pavel Ivanov  wrote:

> From: Pavel Ivanov 
> Subject: Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )
> To: "General Discussion of SQLite Database" 
> Cc: "Zaryab Munir (zmunir)" 
> Date: Thursday, February 10, 2011, 12:28 PM
> > [zm]:  Based on the
> documentation, applications can have multiple connections to
> a file database by calling sqlite3_open() repeatedly.  Is
> there a way to have multiple connections to the ":memory:"
> database.
> 
> No. In-memory databases are special, see 
> http://www.sqlite.org/inmemorydb.html.
> If you want several connections to the same database that
> will be
> completely in-memory you should consider placing your
> database file
> into /dev/shm.
> 
> > [zm]:  I did not change sqlite3 sources.
>  libaimdb.so is a library that I have compiled.  This
> includes my (application) code and links with
> libsqlite3.so.
> 
> Did you compile the whole glibc into your application
> statically? Or
> do you have some other completely unrelated function called
> read()? If
> the latter then you screwed up your application. Rename
> your function
> and I guess everything will work fine.
[zm]: No there is no other function read() and I did compile glibc.  I want to 
check if it is static or dynamic.  Is static linking a requirement ?  Thanks.
> 
> 
> Pavel
> 
> On Thu, Feb 10, 2011 at 2:22 PM, Zaryab M. Munir 
> wrote:
> > Thanks,  my reply inline:
> > Sincerely,
> > Zaryab
> >
> >
> > --- On Thu, 2/10/11, Pavel Ivanov 
> wrote:
> >
> >> From: Pavel Ivanov 
> >> Subject: Re: [sqlite] SIGSEGV Error when using
> sqlite3_exec( )
> >> To: "General Discussion of SQLite Database" 
> >> Cc: "Zaryab Munir (zmunir)" 
> >> Date: Thursday, February 10, 2011, 5:11 AM
> >> Zaryab,
> >>
> >> There's no need to repeat your email several
> times.
> >>
> >> >    Question1:  Can I have multiple
> connections
> >> opened for each thread to
> >> >    the same in-memory dbase.
> >>
> >> No. Each connection to ":memory:" creates unique
> in-memory
> >> database
> >> which will be deleted when that connection is
> closed.
> > [zm]:  Based on the documentation, applications can
> have multiple connections to a file database by calling
> sqlite3_open() repeatedly.  Is there a way to have multiple
> connections to the ":memory:" database.
> >>
> >> >    Question2:  Why is sqlite3_exec( )
> giving
> >> Segmentation Fault error:
> >>
> >> Did you change you SQLite sources? I can't find
> any
> >> information on
> >> what libaimdb.so is and why it is used in your
> >> environment.
> > [zm]:  I did not change sqlite3 sources.
>  libaimdb.so is a library that I have compiled.  This
> includes my (application) code and links with
> libsqlite3.so.
> >>
> >>
> >> Pavel
> >>
> >> On Wed, Feb 9, 2011 at 8:07 PM, Zaryab Munir
> (zmunir)
> >> 
> >> wrote:
> >> > Hi,
> >> >
> >> > I am using an in-memory dbase in a
> multi-threaded
> >> application and have
> >> >    the following two questions:
> >> >    I create  dbase connections by each
> thread using
> >> the API:
> >> >    {
> >> >        Sqlite3 *db =3D NULL;
> >> >        Sqlite3_open(":memory:",
> );
> >> >    When I try to use sqlite3_exec( )   I
> get
> >> segmentation faults.
> >> >    }
> >> >    Question1:  Can I have multiple
> connections
> >> opened for each thread
> >> > to
> >> >    the same in-memory dbase.  According to
> the
> >> documentation, the
> >> >    sqlite3_open( ) API can be used multiple
> times to
> >> open multiple
> >> >    connections to the same database, but
> its not
> >> clear is same applies
> >> > to
> >> >    in-memory databases too.
> >> >    Question2:  Why is sqlite3_exec( )
> giving
> >> Segmentation Fault error:
> >> >    segfault at 2b90153ac905 rip
> 2b90153ac905
> >> rsp
> >> > 7fff95b1d938
> >> >    error 15
> >> >    Segmentation fault
> >> >
> >> >
> >> > Below is my code:
> >> >    uint32  aim_sql_open (char *ptr,
> sqlite3 **db)
> >> >    {
> >> >
> >> >        int n;
> >> >        n =3D sqlite3_open(ptr,db);
> >> >
> >> >        if( n !=3D SQLITE_OK )
> >> >        {
> >> >            /* zz get the wt_id */
> >> >            printf("Error opening
> database for
> >> thread =3D %d.\n",n);
> >> >            return (0);
> >> >        }
> >> >        else {
> >> >            /* zz get the wt_id */
> >> >            printf("Database open for
> thread =3D
> >> %d ok.\n",n);
> >> >        }
> >> >    }
> >> >    NOTE:  sqlite3_open( ) returns
> SQLITE_OK and the
> >> value of db is
> >> > updated.
> >> >
> >> >    uint32 aim_db_init( ) {
> >> >        char dbname[10];
> >> >        char tbname[13];
> >> >        char statement[760], *db_err;
> >> >        sqlite3 *db =3D NULL;
> >> >        int n;
> >> >        bzero(dbname,10);
> >> >        bzero(tbname,13);
> >> >        

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

[Pavel Ivanov]

> [zm]:  Based on the documentation, applications can have multiple connections 
> to a file database by calling sqlite3_open() repeatedly.  Is there a way to 
> have multiple connections to the ":memory:" database.

No. In-memory databases are special, see http://www.sqlite.org/inmemorydb.html.
If you want several connections to the same database that will be
completely in-memory you should consider placing your database file
into /dev/shm.

> [zm]:  I did not change sqlite3 sources.  libaimdb.so is a library that I 
> have compiled.  This includes my (application) code and links with 
> libsqlite3.so.

Did you compile the whole glibc into your application statically? Or
do you have some other completely unrelated function called read()? If
the latter then you screwed up your application. Rename your function
and I guess everything will work fine.


Pavel

On Thu, Feb 10, 2011 at 2:22 PM, Zaryab M. Munir  wrote:
> Thanks,  my reply inline:
> Sincerely,
> Zaryab
>
>
> --- On Thu, 2/10/11, Pavel Ivanov  wrote:
>
>> From: Pavel Ivanov 
>> Subject: Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )
>> To: "General Discussion of SQLite Database" 
>> Cc: "Zaryab Munir (zmunir)" 
>> Date: Thursday, February 10, 2011, 5:11 AM
>> Zaryab,
>>
>> There's no need to repeat your email several times.
>>
>> >    Question1:  Can I have multiple connections
>> opened for each thread to
>> >    the same in-memory dbase.
>>
>> No. Each connection to ":memory:" creates unique in-memory
>> database
>> which will be deleted when that connection is closed.
> [zm]:  Based on the documentation, applications can have multiple connections 
> to a file database by calling sqlite3_open() repeatedly.  Is there a way to 
> have multiple connections to the ":memory:" database.
>>
>> >    Question2:  Why is sqlite3_exec( ) giving
>> Segmentation Fault error:
>>
>> Did you change you SQLite sources? I can't find any
>> information on
>> what libaimdb.so is and why it is used in your
>> environment.
> [zm]:  I did not change sqlite3 sources.  libaimdb.so is a library that I 
> have compiled.  This includes my (application) code and links with 
> libsqlite3.so.
>>
>>
>> Pavel
>>
>> On Wed, Feb 9, 2011 at 8:07 PM, Zaryab Munir (zmunir)
>> 
>> wrote:
>> > Hi,
>> >
>> > I am using an in-memory dbase in a multi-threaded
>> application and have
>> >    the following two questions:
>> >    I create  dbase connections by each thread using
>> the API:
>> >    {
>> >        Sqlite3 *db =3D NULL;
>> >        Sqlite3_open(":memory:", );
>> >    When I try to use sqlite3_exec( )   I get
>> segmentation faults.
>> >    }
>> >    Question1:  Can I have multiple connections
>> opened for each thread
>> > to
>> >    the same in-memory dbase.  According to the
>> documentation, the
>> >    sqlite3_open( ) API can be used multiple times to
>> open multiple
>> >    connections to the same database, but its not
>> clear is same applies
>> > to
>> >    in-memory databases too.
>> >    Question2:  Why is sqlite3_exec( ) giving
>> Segmentation Fault error:
>> >    segfault at 2b90153ac905 rip 2b90153ac905
>> rsp
>> > 7fff95b1d938
>> >    error 15
>> >    Segmentation fault
>> >
>> >
>> > Below is my code:
>> >    uint32  aim_sql_open (char *ptr, sqlite3 **db)
>> >    {
>> >
>> >        int n;
>> >        n =3D sqlite3_open(ptr,db);
>> >
>> >        if( n !=3D SQLITE_OK )
>> >        {
>> >            /* zz get the wt_id */
>> >            printf("Error opening database for
>> thread =3D %d.\n",n);
>> >            return (0);
>> >        }
>> >        else {
>> >            /* zz get the wt_id */
>> >            printf("Database open for thread =3D
>> %d ok.\n",n);
>> >        }
>> >    }
>> >    NOTE:  sqlite3_open( ) returns SQLITE_OK and the
>> value of db is
>> > updated.
>> >
>> >    uint32 aim_db_init( ) {
>> >        char dbname[10];
>> >        char tbname[13];
>> >        char statement[760], *db_err;
>> >        sqlite3 *db =3D NULL;
>> >        int n;
>> >        bzero(dbname,10);
>> >        bzero(tbname,13);
>> >        bzero(statement,760);
>> >        memcpy(dbname,DB_NAME,9);
>> >        /* no need for dbname for inmemory dbase
>> */
>> >        n =3D aim_sql_open(":memory:", );
>> >        memcpy(tbname,AIM_TABLE_NAME,12);
>> >        if(!strcmp(tbname,""))
>> >            return(FALSE);
>> >        sprintf(statement, "CREATE TABLE %s (%s
>> INTEGER PRIMARY KEY,%s
>> >    DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s
>> TEXT,%s TEXT,%s
>> >    INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s
>> DATE,%s INTEGER,%s
>> > DATE,%s
>> >    INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s
>> INTEGER,%s DATE);",
>> >
>> >
>> tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
>> >
>>  L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
>> >        n =3D sqlite3_exec(db, statement, NULL, 0,
>> 

Re: [sqlite] LENGTH on a BLOB field stops at NUL byte

[Yves Goergen]

On 07.02.2011 23:47 CE(S)T, Samuel Adam wrote:
> On Mon, 07 Feb 2011 03:16:54 -0500, Philip Graham Willoughby  
>  wrote:
>> What about:
>>
>> UPDATE "message_revision" SET "Data" = CAST ("Data" AS BLOB);
> 
> Y’know the urban legend about the folks at the restaurant who design a  
> complicated plan for switching the contents of salt and pepper shakers  
> which have their hats juxtaposed, and then a passer-by suggests just  
> switching the caps?

I don't know that story nor do I understand it. But would that trigger
thing work? That would be something I could set up easily.

It's not that important issue for my anymore. I've simply decided to not
store binary data in the SQLite database in this application anymore but
instead write it to disk. Storing files in the database should only be
of interest here if you cannot write to disk, but when using SQLite you
obviously can do that.

-- 
Yves Goergen "LonelyPixel" 
Visit my web laboratory at http://beta.unclassified.de
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Petite Abeille]

On Feb 10, 2011, at 5:00 PM, Yuzem wrote:

> The only thing I can think of is to have additional tables for the ids of
> all directors, all writers, etc...
> Tables "movies", "people", "capacity" and then tables "directors",
> "writers", etc.. with only the IDs so I can count using count(*) which is
> super fast.

Right... looking at something like the casting information in IMdb (actor, 
director, writer, etc), there are about 25M movie + role + person combinations. 
Which, while not huge, starts to get taxing when queried on a lowly laptop.

For example, out of these 25M rows, about 1.2M represent directors (~217K) in 
movies (~1M). 

Assuming a movie_cast table [1] and a cast dimension [2], you could record the 
distinct count for each cast in the dimension once, and store it. That way you 
don't have to recompute it over and over.

Alternatively, you could indeed partition that bulky movie_cast table by its 
cast type, which will in effect reduce the search space to at most ~9M (movies 
by actors, ~1.3M movies, ~1.3M actors). 

That said, even partitioning will not help you much here as you still have to 
search through ~9M records to figure out how many distinct actors you have.

So... perhaps best to cheat :)

One way to cheat is to precompute the answer by, for example, adding a is_* 
flag on your person table:

update person set is_actor = ( select count( distinct person_id ) from 
movie_actor where movie_actor.person_id = person.id );

The person table is much smaller (~3M) and easier to query in bulk:

explain query plan
select count( * ) from person where is_actor = 1;
0|0|0|SEARCH TABLE person USING COVERING INDEX person_is_actor (is_actor=?) 
(~1596808 rows)

1310119
CPU Time: user 0.256286 sys 0.000257

There you go :)

This is where I wish SQLite could have bitmap indices:

http://en.wikipedia.org/wiki/Bitmap_index

Oh, well...

[1] http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L161
[2] http://dev.alt.textdrive.com/browser/IMDB/IMDB.ddl#L146




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Outer query returning results not found in subquery

[Igor Tandetnik]

On 2/10/2011 2:17 PM, Dan Kubb wrote:
> Database setup:
>
>  CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER 
> NOT NULL);
>
>  INSERT INTO "test" ("letter", "number") VALUES('b', 1);
>  INSERT INTO "test" ("letter", "number") VALUES('a', 2);
>  INSERT INTO "test" ("letter", "number") VALUES('c', 2);
>
> Initial query:
>
>  SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 
> 1;
>
> This returns "a|2", the second row from the results as you would
> expect given that we're sorting on the letter then the number.
> However, here's what I did not expect:
>
> Initial query as a subquery:
>
>  SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" 
> ORDER BY "letter", "number" LIMIT 1) AS "test";
>
> This returns "1"

Yes, looks like a bug. If you drop DISTINCT, it returns a single row 
with the value 2.

My guess is, DISTINCT is internally implemented as ORDER BY, and that 
overrules ORDER BY found in the subquery.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

[Zaryab M. Munir]

Thanks,  my reply inline:
Sincerely,
Zaryab


--- On Thu, 2/10/11, Pavel Ivanov  wrote:

> From: Pavel Ivanov 
> Subject: Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )
> To: "General Discussion of SQLite Database" 
> Cc: "Zaryab Munir (zmunir)" 
> Date: Thursday, February 10, 2011, 5:11 AM
> Zaryab,
> 
> There's no need to repeat your email several times.
> 
> >    Question1:  Can I have multiple connections
> opened for each thread to
> >    the same in-memory dbase.
> 
> No. Each connection to ":memory:" creates unique in-memory
> database
> which will be deleted when that connection is closed.
[zm]:  Based on the documentation, applications can have multiple connections 
to a file database by calling sqlite3_open() repeatedly.  Is there a way to 
have multiple connections to the ":memory:" database. 
> 
> >    Question2:  Why is sqlite3_exec( ) giving
> Segmentation Fault error:
> 
> Did you change you SQLite sources? I can't find any
> information on
> what libaimdb.so is and why it is used in your
> environment.
[zm]:  I did not change sqlite3 sources.  libaimdb.so is a library that I have 
compiled.  This includes my (application) code and links with libsqlite3.so. 
> 
> 
> Pavel
> 
> On Wed, Feb 9, 2011 at 8:07 PM, Zaryab Munir (zmunir)
> 
> wrote:
> > Hi,
> >
> > I am using an in-memory dbase in a multi-threaded
> application and have
> >    the following two questions:
> >    I create  dbase connections by each thread using
> the API:
> >    {
> >        Sqlite3 *db =3D NULL;
> >        Sqlite3_open(":memory:", );
> >    When I try to use sqlite3_exec( )   I get
> segmentation faults.
> >    }
> >    Question1:  Can I have multiple connections
> opened for each thread
> > to
> >    the same in-memory dbase.  According to the
> documentation, the
> >    sqlite3_open( ) API can be used multiple times to
> open multiple
> >    connections to the same database, but its not
> clear is same applies
> > to
> >    in-memory databases too.
> >    Question2:  Why is sqlite3_exec( ) giving
> Segmentation Fault error:
> >    segfault at 2b90153ac905 rip 2b90153ac905
> rsp
> > 7fff95b1d938
> >    error 15
> >    Segmentation fault
> >
> >
> > Below is my code:
> >    uint32  aim_sql_open (char *ptr, sqlite3 **db)
> >    {
> >
> >        int n;
> >        n =3D sqlite3_open(ptr,db);
> >
> >        if( n !=3D SQLITE_OK )
> >        {
> >            /* zz get the wt_id */
> >            printf("Error opening database for
> thread =3D %d.\n",n);
> >            return (0);
> >        }
> >        else {
> >            /* zz get the wt_id */
> >            printf("Database open for thread =3D
> %d ok.\n",n);
> >        }
> >    }
> >    NOTE:  sqlite3_open( ) returns SQLITE_OK and the
> value of db is
> > updated.
> >
> >    uint32 aim_db_init( ) {
> >        char dbname[10];
> >        char tbname[13];
> >        char statement[760], *db_err;
> >        sqlite3 *db =3D NULL;
> >        int n;
> >        bzero(dbname,10);
> >        bzero(tbname,13);
> >        bzero(statement,760);
> >        memcpy(dbname,DB_NAME,9);
> >        /* no need for dbname for inmemory dbase
> */
> >        n =3D aim_sql_open(":memory:", );
> >        memcpy(tbname,AIM_TABLE_NAME,12);
> >        if(!strcmp(tbname,""))
> >            return(FALSE);
> >        sprintf(statement, "CREATE TABLE %s (%s
> INTEGER PRIMARY KEY,%s
> >    DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s
> TEXT,%s TEXT,%s
> >    INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s
> DATE,%s INTEGER,%s
> > DATE,%s
> >    INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s
> INTEGER,%s DATE);",
> >
> >
> tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
> >  
>  L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
> >        n =3D sqlite3_exec(db, statement, NULL, 0,
> _err);
> >        if( n !=3D SQLITE_OK )
> >        {
> >            printf("Error executing:
> %s\n",db_err);
> >            sqlite3_free(db_err);
> >            return (FALSE);
> >        }
> >        =20
> >
> >    The backtrace from GDB is as follows:
> >    Program received signal SIGSEGV, Segmentation
> fault.
> >    [Switching to Thread 0x2b7070afe1a0 (LWP 699)]
> >    0x2b706fcc6905 in read () from
> /usr/lib64/libaimdb.so
> >    (gdb) bt
> >    #0  0x2b706fcc6905 in read () from
> /usr/lib64/libaimdb.so
> >    #1  0x2b70703a4120 in unixRandomness
> (NotUsed=3D > =
> >    out>,
> >        nBuf=3D,
> zBuf=3D0x7fff3b203050 "") at
> >    ../sqlite3.c:27932
> >    #2  0x2b707035b0f5 in sqlite3_randomness
> (N=3D4, pBuf=3D >    optimized
> >    out>)
> >        at ../sqlite3.c:13876
> >    #3  0x2b707035b28f in writeJournalHdr
> (pPager=3D0x62b788)
> >        at ../sqlite3.c:36096
> >    #4  0x2b707035b4fb in pager_write
> (pPg=3D0x639e08) at
> >    ../sqlite3.c:39815
> >    #5  0x2b7070366195 in sqlite3PagerWrite
> (pDbPage=3D0x7) at

[sqlite] Outer query returning results not found in subquery

[Dan Kubb]

Hi,

I have a query that when executed stand-alone returns different results than 
when it's a subquery. At first I wasn't sure if it was just me, so I submitted 
a question to Stackoverflow with some detail and someone else replied that they 
could reproduce what I was seeing:

  
http://stackoverflow.com/questions/4870293/sqlite-outer-query-is-returning-results-not-found-in-inner-query

I'll post the question I asked below since I think it explains what I'm seeing 
relatively clearly, and provides instructions on how to reproduce:

--

I just wondered if anyone has run into a case in SQLite (3.7.4) where a query 
would return one set of results, and when it becomes a subquery the results are 
completely different? I found the problem in a more complex query, but here's a 
simpler example that demonstrates the same behaviour:

Database setup:

CREATE TABLE "test" ("letter" VARCHAR(1) PRIMARY KEY, "number" INTEGER NOT 
NULL);

INSERT INTO "test" ("letter", "number") VALUES('b', 1);
INSERT INTO "test" ("letter", "number") VALUES('a', 2);
INSERT INTO "test" ("letter", "number") VALUES('c', 2);

Initial query:

SELECT "letter", "number" FROM "test" ORDER BY "letter", "number" LIMIT 1;

This returns "a|2", the second row from the results as you would expect given 
that we're sorting on the letter then the number. However, here's what I did 
not expect:

Initial query as a subquery:

SELECT DISTINCT "number" FROM (SELECT "letter", "number" FROM "test" ORDER 
BY "letter", "number" LIMIT 1) AS "test";

This returns "1", which is not at all what I expected. What I expected to see 
is "2". My understanding of how a subquery works is that it should return the 
same results *as if* the inner query was materialized, and the outer query was 
applied against those results (even though I realize that databases go to 
extreme lengths not to materialize results until necessary).

Is my assumption incorrect? I tested the same query in PostgreSQL and MySQL and 
it worked as I expected (i.e. it returned "2"). What it looks like to me is 
that I've hit a bug in how SQLite collapses subqueries, but I'm not sure.

Just to reiterate, the above example is simplified from what I'm actually 
doing. I'm not just using DISTINCT on a subquery that returns a single row, but 
rather it returns many rows, some of which have the same value for a column 
hence my need for DISTINCT. The above example is the simplest way I could think 
of to demonstrate what's happening.

-- 

Thanks,

Dan Kubb
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

> Thanks you but what I want to do is to count without using count(distinct 
> col) because it is much slower than count(*).

I think you'll find the schema I posted very fast, since it's running 
everything, including distinct, on primary key columns.

Or you can try this:

select count(*) from People
where ID in
(   select People_ID from "Movie People" where Capacity_ID =
(select ID from Capacity where Name = 'director')
);

But I expect you'll get the same performance.

You don't want to denormalize and have separate director and writer tables etc. 
That will get ugly.

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Simon Slavin]

On 10 Feb 2011, at 4:00pm, Yuzem wrote:

> Thanks you but what I want to do is to count without using count(distinct
> col) because it is much slower than count(*).

How much slower.  Did you make an index SQLite could use for that query ?  A 
good index for that might be

People_ID,Capacity_ID

but it might be faster with those two the other way around.  Try them both out 
and see which gives you faster results.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Jay A. Kreibich]

On Thu, Feb 10, 2011 at 09:35:04AM -0600, Puneet Kishor scratched on the wall:

> that code would be very complex to cover all the possible cases. The
> simplest solution is to depend upon AS aliasing

  To be clear, that's not an excuse the development team is using
  to avoid writing a hard bit of code.  The SQL standard leaves column
  names undefined in the absence of a column alias (e.g. "AS" phrase).
  In other words, the database is free to do its best, but it is really
  up to the developer to strictly define names, via AS, if the names are
  relevant (i.e. used in code).  (The wisdom of using names as column
  identifiers is a whole different argument.)

  Consider your own example.  Is "a" really the correct output?  What
  about "x.a"?  Or "main.x.a"?  If you feel the need to quote a column
  name, such as "[a]", why shouldn't the database feel it is proper to
  quote it back at you? 
  
  What if there is both an "x.a" and a "y.a" column from an "x JOIN y"
  operation?  Should the columns be "a" and "a", or should they
  promoted to be more specific?  What about a sub-select that has an
  "a AS a" output specification, where it is an alias that just
  happens to be the same as a column, but it is no longer a
  source-column reference?  What about "a+1 AS a" where any
  source-column association (and therefore table and database
  association) is specifically broken?

  For almost any naming scheme one can come up with, it is fairly
  easy to find odd edge cases that add dozens of extra "but",
  "unless", "except" rules to your naming convention.  Your rule set
  quickly becomes so huge and fragile, you might as well treat the
  naming convention as undefined.  And, of course, the naming rules
  would be product-specific (Some DBs have schema name-spaces, some
  don't.  Some have table-spaces, some don't.  Some can access multiple
  databases, some can't.), meaning every database is going to do it
  differently anyways-- which is exactly why it isn't in the standard.

   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed up count(distinct col)

[Yuzem]

BareFeetWare-2 wrote:
> 
> Then you can count the directors like this:
> 
> select count(distinct People_ID) from "Movie People" join Capacity on
> "Movie People".Capacity_ID = Capacity.ID where Capacity.Name = 'director';
> 
> or:
> 
> select count(distinct People_ID) from "Movie People" where Capacity_ID =
> (select ID from Capacity where Name = 'director');
> 
> or you can create a view [...]
> 

Thanks you but what I want to do is to count without using count(distinct
col) because it is much slower than count(*).
In the previous examples about tags I can do it, I have "movies" and "tags"
related by "movies_tags"
and I can do:
SELECT count(*) FROM tags;
I want to know if there is any standard approach to do that.
The only thing I can think of is to have additional tables for the ids of
all directors, all writers, etc...
Tables "movies", "people", "capacity" and then tables "directors",
"writers", etc.. with only the IDs so I can count using count(*) which is
super fast.
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30893712.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[David Bicking]

--- On Thu, 2/10/11, Puneet Kishor  wrote:
> Date: Thursday, February 10, 2011, 10:35 AM
> 
> On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard
> wrote: 
> > Hi,
> > 
> > I'm sorry Pavel, I think you've got me wrong.
> > 
> > > It's not "buggy". Name of the column in result
> set is not defined
> > > unless you use "as".
> > 
> Pavel is not wrong. SQLite is not buggy. Your expectation
> of what it should do is at fault here.
> > ..
> 
> 
> I used to think as you too, but then learned otherwise. The
> developers simply didn't add the code to dependably
> determine what the column should be called, because,
> surprisingly (or not surprisingly, if you think about it a
> bit), that code would be very complex to cover all the
> possible cases. The simplest solution is to depend upon AS
> aliasing, else, do something undependable (to us).
> 
> Puneet. 
> 

Not only is it complex, but as I recall, back when the developers did try to be 
consistent in how column names were presented, no matter how they coded it, 
someone would pipe up with an opinion that it should be done another way. There 
were pragmas to try to get the column names to match different expectations.

In the end, they decided to just call it undefined and let the user use AS to 
get what they wanted. I think that was the right decision.

David

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Puneet Kishor]

On Thursday, February 10, 2011 at 9:22 AM, Nißl Reinhard wrote: 
> Hi,
> 
> I'm sorry Pavel, I think you've got me wrong.
> 
> > It's not "buggy". Name of the column in result set is not defined
> > unless you use "as".
> 
Pavel is not wrong. SQLite is not buggy. Your expectation of what it should do 
is at fault here.
> ..


I used to think as you too, but then learned otherwise. The developers simply 
didn't add the code to dependably determine what the column should be called, 
because, surprisingly (or not surprisingly, if you think about it a bit), that 
code would be very complex to cover all the possible cases. The simplest 
solution is to depend upon AS aliasing, else, do something undependable (to us).

Puneet. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Pavel Ivanov]

> But why do I get different column names but the same result for these 
> statements?
>        select  a  from (select a from x);
>        select [a] from (select a from x);
>        select "a" from (select a from x);

Please show us documentation that says you should get the same column
name for each of these statements. I can understand that this is kind
of counter-intuitive for you but it behaves exactly how it's
documented, i.e. column name is undefined and can change from version
to version.

> I consider this a bug.

You can consider it a bug. But until SQLite developers consider it a
bug it won't be fixed. I'm not one of SQLite developers but I know how
they feel about this problem because it was raised on this list
numerous times.


Pavel

On Thu, Feb 10, 2011 at 10:22 AM, Nißl Reinhard  wrote:
> Hi,
>
> I'm sorry Pavel, I think you've got me wrong.
>
>> It's not "buggy". Name of the column in result set is not defined
>> unless you use "as".
>
> But why do I get different column names but the same result for these 
> statements?
>
>        select  a  from (select a from x);
>        select [a] from (select a from x);
>        select "a" from (select a from x);
>
> For all three statements the column name should be just >>a<<, as it is for 
> these statements:
>
>        select  a  from x;
>        select [a] from x;
>        select "a" from x;
>
> Why should I have to write to the statements like below to get what I want?
>
>        select  a   a  from (select a from x);
>        select  a  [a] from (select a from x);
>        select  a  "a" from (select a from x);
>        select [a]  a  from (select a from x);
>        select [a] [a] from (select a from x);
>        select [a] "a" from (select a from x);
>        select "a"  a  from (select a from x);
>        select "a" [a] from (select a from x);
>        select "a" "a" from (select a from x);
>
> I consider this a bug.
>
> Bye.
> --
> Reinhard Nißl
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
> Im Auftrag von Pavel Ivanov
> Gesendet: Donnerstag, 10. Februar 2011 13:48
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part 
> of column names for certain statements
>
>>        select [a] from (select * from x);
>> You'll get the following "buggy" output:
>>        [a]
>>        1
>
> It's not "buggy". Name of the column in result set is not defined
> unless you use "as".
>
>>        CREATE TABLE y("[a]" INT);
>> I came across this issue as statements like the following failed with the 
>> below mentioned error due to incorrect column names in the created tables:
>>        create index [y.a] on y ([a]);
>> Output in version 3.7.5:
>>        Error: table y has no column named a
>
> Because symbols [] have special meaning - quoting identifiers. So your
> statement basically looked the same as the following:
>
> create index "y.a" on y("a");
>
> And indeed table y doesn't have such column. The following statement
> should work:
>
> create index "y.a" on y("[a]");
>
>
> But best of all use "as" clause in your queries and never use "create
> ... as select ..." in any application (it can be useful only in some
> quick-and-dirty debugging). And also I would suggest not using
> confusing names for any table, index or column (e.g. as your "y.a").
>
>
> Pavel
>
> On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard  wrote:
>> Hi,
>>
>> identifier quotation characters ("[]) get part of column names for certain 
>> statements, i. e. it depends on the complexity of the statement to trigger 
>> this bug.
>>
>> To reproduce the bug, type the following in sqlite3:
>>
>>        .headers ON
>>        create table x(a int);
>>        insert into x values (1);
>>        select [a] from (select * from x);
>>
>> You'll get the following "buggy" output:
>>
>>        [a]
>>        1
>>
>> The correct output is returned for this statement:
>>
>>        select [a] from x;
>>
>> You'll get:
>>
>>        a
>>        1
>>
>> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even 
>> the following statement returned an incorrect column name:
>>
>>        select [a] from x group by a;
>>
>> The 3.3.6 result was:
>>
>>        [a]
>>        1
>>
>> The 3.7.5 correct result is:
>>
>>        a
>>        1
>>
>> While I knew this bug for some years already it didn't matter much in my 
>> software. In 3.7.5 it hurts me due to the corrected behavior for "create 
>> table ... as select ..." statements. In 3.3.6 the column names were 
>> implicitly dequoted (which in my current opinion was incorrect) so the below 
>> statement created the table as shown:
>>
>>        create table y as select [a] from (select * from x);
>>        .schema y
>>
>> Output in version 3.3.6:
>>
>>        CREATE TABLE y(a int);
>>
>> In 3.7.5 with corrected behavior, the output looks like that (and is correct 

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Nißl Reinhard]

Hi,

I'm sorry Pavel, I think you've got me wrong.

> It's not "buggy". Name of the column in result set is not defined
> unless you use "as".

But why do I get different column names but the same result for these 
statements?

select  a  from (select a from x);
select [a] from (select a from x);
select "a" from (select a from x);

For all three statements the column name should be just >>a<<, as it is for 
these statements:

select  a  from x;
select [a] from x;
select "a" from x;

Why should I have to write to the statements like below to get what I want?
 
select  a   a  from (select a from x);
select  a  [a] from (select a from x);
select  a  "a" from (select a from x);
select [a]  a  from (select a from x);
select [a] [a] from (select a from x);
select [a] "a" from (select a from x);
select "a"  a  from (select a from x);
select "a" [a] from (select a from x);
select "a" "a" from (select a from x);

I consider this a bug.

Bye.
--
Reinhard Nißl

-Ursprüngliche Nachricht-
Von: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
Im Auftrag von Pavel Ivanov
Gesendet: Donnerstag, 10. Februar 2011 13:48
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of 
column names for certain statements

>        select [a] from (select * from x);
> You'll get the following "buggy" output:
>        [a]
>        1

It's not "buggy". Name of the column in result set is not defined
unless you use "as".

>        CREATE TABLE y("[a]" INT);
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>        create index [y.a] on y ([a]);
> Output in version 3.7.5:
>        Error: table y has no column named a

Because symbols [] have special meaning - quoting identifiers. So your
statement basically looked the same as the following:

create index "y.a" on y("a");

And indeed table y doesn't have such column. The following statement
should work:

create index "y.a" on y("[a]");


But best of all use "as" clause in your queries and never use "create
... as select ..." in any application (it can be useful only in some
quick-and-dirty debugging). And also I would suggest not using
confusing names for any table, index or column (e.g. as your "y.a").


Pavel

On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard  wrote:
> Hi,
>
> identifier quotation characters ("[]) get part of column names for certain 
> statements, i. e. it depends on the complexity of the statement to trigger 
> this bug.
>
> To reproduce the bug, type the following in sqlite3:
>
>        .headers ON
>        create table x(a int);
>        insert into x values (1);
>        select [a] from (select * from x);
>
> You'll get the following "buggy" output:
>
>        [a]
>        1
>
> The correct output is returned for this statement:
>
>        select [a] from x;
>
> You'll get:
>
>        a
>        1
>
> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
> following statement returned an incorrect column name:
>
>        select [a] from x group by a;
>
> The 3.3.6 result was:
>
>        [a]
>        1
>
> The 3.7.5 correct result is:
>
>        a
>        1
>
> While I knew this bug for some years already it didn't matter much in my 
> software. In 3.7.5 it hurts me due to the corrected behavior for "create 
> table ... as select ..." statements. In 3.3.6 the column names were 
> implicitly dequoted (which in my current opinion was incorrect) so the below 
> statement created the table as shown:
>
>        create table y as select [a] from (select * from x);
>        .schema y
>
> Output in version 3.3.6:
>
>        CREATE TABLE y(a int);
>
> In 3.7.5 with corrected behavior, the output looks like that (and is correct 
> according to the buggy select statement):
>
>        CREATE TABLE y("[a]" INT);
>
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>
>        create index [y.a] on y ([a]);
>
> Output in version 3.7.5:
>
>        Error: table y has no column named a
>
> I really would like to get that fixed in 3.7.6. At least a patch would be 
> welcome during the next week.
>
> Attached you'll find some statements to test with and the outputs of sqlite3 
> for versions 3.7.5 and 3.3.6.
>
> Bye.
> --
> Reinhard Nißl
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users 

Re: [sqlite] Speed up count(distinct col)

[BareFeetWare]

On 08/02/2011, at 10:19 AM, Yuzem wrote:

> I have the tables "movies" and "people"
> Those two tables are related by tables "directors", "writers", etc...
> movies: id, title
> people: id, name
> 
> directors: movieId, peopleId
> writers: movieId, peopleId
> etc...
> 
> How can I normalize that so I can count directors and writers using
> count(*)?
> Should I create another table for "directors" and another for "writers",
> etc... with only the ids?
> I will end up having:
> movies > movies_directors < (directors people)

After implementing the schema in my previous post, add this:

begin immediate;
create table People
(   ID integer primary key not null
,   Name text collate no case not null unique
);
create table Capacity
(   ID integer primary key not null
,   Name text collate no case not null unique
);
create table "Movie People"
(   ID integer primary key not null
,   Movie_ID integer not null references Movies (Movie_ID) on delete cascade
,   Capacity_ID integer not null references Capacity (ID) on delete cascade
,   People_ID integer not null references People (ID) on delete cascade
,   unique (Movie_ID, Capacity_ID, People_ID)
);
commit;

Then you can count the directors like this:

select count(distinct People_ID) from "Movie People" join Capacity on "Movie 
People".Capacity_ID = Capacity.ID where Capacity.Name = 'director';

or:

select count(distinct People_ID) from "Movie People" where Capacity_ID = 
(select ID from Capacity where Name = 'director');

or you can create a view to alphabetically list each director with a CSV list 
of their movies, like this:

create view "Directors"
as
select People.Name, group_concat (Movies.Title, ', ')
from "Movie People"
join Movies on "Movie People".Movie_ID = Movies.Movie_ID
join Capacity on "Movie People".Capacity_ID = Capacity.ID
join People on "Movie People".People_ID = People.ID
where Capacity.Name = 'director';
group by People_ID
order by People.Name
;

Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] EXT : Speed up count(distinct col)

[Yuzem]

Hi, thanks a lot for the helpful replies.
Sorry to bother again, but there is still something that hasn't been
answered.

Simon Slavin-3 has addressed my question but not exactly what I was asking.
Suppose I have two tables "movies" and "people" and other tables to relate
both tables: "directors", "writers", etc...

Simon Slavin-3 told me to create one single table to relate "movies" and
"people":
MoviePeople: id, movie, person, capacity

But with that solution I can't perform the fast count(*) that I want.
I would have to do:
SELECT count(distinct person) FROM MoviePeople WHERE capacity = "director";

I want to know if there is any standard solution for normalizing this
database so I can do count(*) to count all directors or writers...

Thanks in advance for your help and patience.
-- 
View this message in context: 
http://old.nabble.com/Speed-up-count%28distinct-col%29-tp30864622p30892423.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SIGSEGV Error when using sqlite3_exec( )

[Pavel Ivanov]

Zaryab,

There's no need to repeat your email several times.

>    Question1:  Can I have multiple connections opened for each thread to
>    the same in-memory dbase.

No. Each connection to ":memory:" creates unique in-memory database
which will be deleted when that connection is closed.

>    Question2:  Why is sqlite3_exec( ) giving Segmentation Fault error:

Did you change you SQLite sources? I can't find any information on
what libaimdb.so is and why it is used in your environment.


Pavel

On Wed, Feb 9, 2011 at 8:07 PM, Zaryab Munir (zmunir)  wrote:
> Hi,
>
> I am using an in-memory dbase in a multi-threaded application and have
>    the following two questions:
>    I create  dbase connections by each thread using the API:
>    {
>        Sqlite3 *db =3D NULL;
>        Sqlite3_open(":memory:", );
>    When I try to use sqlite3_exec( )   I get segmentation faults.
>    }
>    Question1:  Can I have multiple connections opened for each thread
> to
>    the same in-memory dbase.  According to the documentation, the
>    sqlite3_open( ) API can be used multiple times to open multiple
>    connections to the same database, but its not clear is same applies
> to
>    in-memory databases too.
>    Question2:  Why is sqlite3_exec( ) giving Segmentation Fault error:
>    segfault at 2b90153ac905 rip 2b90153ac905 rsp
> 7fff95b1d938
>    error 15
>    Segmentation fault
>
>
> Below is my code:
>    uint32  aim_sql_open (char *ptr, sqlite3 **db)
>    {
>
>        int n;
>        n =3D sqlite3_open(ptr,db);
>
>        if( n !=3D SQLITE_OK )
>        {
>            /* zz get the wt_id */
>            printf("Error opening database for thread =3D %d.\n",n);
>            return (0);
>        }
>        else {
>            /* zz get the wt_id */
>            printf("Database open for thread =3D %d ok.\n",n);
>        }
>    }
>    NOTE:  sqlite3_open( ) returns SQLITE_OK and the value of db is
> updated.
>
>    uint32 aim_db_init( ) {
>        char dbname[10];
>        char tbname[13];
>        char statement[760], *db_err;
>        sqlite3 *db =3D NULL;
>        int n;
>        bzero(dbname,10);
>        bzero(tbname,13);
>        bzero(statement,760);
>        memcpy(dbname,DB_NAME,9);
>        /* no need for dbname for inmemory dbase */
>        n =3D aim_sql_open(":memory:", );
>        memcpy(tbname,AIM_TABLE_NAME,12);
>        if(!strcmp(tbname,""))
>            return(FALSE);
>        sprintf(statement, "CREATE TABLE %s (%s INTEGER PRIMARY KEY,%s
>    DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s TEXT,%s TEXT,%s
>    INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s DATE,%s INTEGER,%s
> DATE,%s
>    INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s INTEGER,%s DATE);",
>
> tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
>    L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
>        n =3D sqlite3_exec(db, statement, NULL, 0, _err);
>        if( n !=3D SQLITE_OK )
>        {
>            printf("Error executing: %s\n",db_err);
>            sqlite3_free(db_err);
>            return (FALSE);
>        }
>        =20
>
>    The backtrace from GDB is as follows:
>    Program received signal SIGSEGV, Segmentation fault.
>    [Switching to Thread 0x2b7070afe1a0 (LWP 699)]
>    0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
>    (gdb) bt
>    #0  0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
>    #1  0x2b70703a4120 in unixRandomness (NotUsed=3D =
>    out>,
>        nBuf=3D, zBuf=3D0x7fff3b203050 "") at
>    ../sqlite3.c:27932
>    #2  0x2b707035b0f5 in sqlite3_randomness (N=3D4, pBuf=3D    optimized
>    out>)
>        at ../sqlite3.c:13876
>    #3  0x2b707035b28f in writeJournalHdr (pPager=3D0x62b788)
>        at ../sqlite3.c:36096
>    #4  0x2b707035b4fb in pager_write (pPg=3D0x639e08) at
>    ../sqlite3.c:39815
>    #5  0x2b7070366195 in sqlite3PagerWrite (pDbPage=3D0x7) at
>    ../sqlite3.c:40151
>    #6  0x2b7070368466 in newDatabase (pBt=3D0x62b6d8) at
>    ../sqlite3.c:47668
>    #7  0x2b707037a18c in sqlite3BtreeBeginTrans (p=3D0x62b678, =
>    wrflag=3D1)
>        at ../sqlite3.c:47798
>    #8  0x2b707037fa78 in sqlite3Step (p=3D0x63a988) at =
>    ../sqlite3.c:63062
>    #9  0x2b7070383803 in sqlite3_step (pStmt=3D out>)
>        at ../sqlite3.c:58768
>    #10 0x2b7070386592 in sqlite3_exec (db=3D0x62b078,
>        zSql=3D0x7fff3b203b10 "CREATE TABLE aim_db_table (hash4ADandDC =
>    INTEGER
>    PRIMARY KEY,entrytime DATE,state INTEGER,bwgain INTEGER,cctimestamp
>    INTEGER,foreignkeyAD TEXT,popclass TEXT,deviceclass TEXT,vdoBitrate
>    INTEGER,qualityI"...,
>        xCallback=3D0, pArg=3D0x0, pzErrMsg=3D0x7fff3b203e38) at
>    ../sqlite3.c:84165
>    #11 0x2b706fac52ac in aim_db_init () at aim_db_main.c:645
>    #12 0x0040566d in main ()
>
>
> Regards,
> Zaryab
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org

Re: [sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Pavel Ivanov]

>        select [a] from (select * from x);
> You'll get the following "buggy" output:
>        [a]
>        1

It's not "buggy". Name of the column in result set is not defined
unless you use "as".

>        CREATE TABLE y("[a]" INT);
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>        create index [y.a] on y ([a]);
> Output in version 3.7.5:
>        Error: table y has no column named a

Because symbols [] have special meaning - quoting identifiers. So your
statement basically looked the same as the following:

create index "y.a" on y("a");

And indeed table y doesn't have such column. The following statement
should work:

create index "y.a" on y("[a]");


But best of all use "as" clause in your queries and never use "create
... as select ..." in any application (it can be useful only in some
quick-and-dirty debugging). And also I would suggest not using
confusing names for any table, index or column (e.g. as your "y.a").


Pavel

On Thu, Feb 10, 2011 at 6:25 AM, Nißl Reinhard  wrote:
> Hi,
>
> identifier quotation characters ("[]) get part of column names for certain 
> statements, i. e. it depends on the complexity of the statement to trigger 
> this bug.
>
> To reproduce the bug, type the following in sqlite3:
>
>        .headers ON
>        create table x(a int);
>        insert into x values (1);
>        select [a] from (select * from x);
>
> You'll get the following "buggy" output:
>
>        [a]
>        1
>
> The correct output is returned for this statement:
>
>        select [a] from x;
>
> You'll get:
>
>        a
>        1
>
> I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
> following statement returned an incorrect column name:
>
>        select [a] from x group by a;
>
> The 3.3.6 result was:
>
>        [a]
>        1
>
> The 3.7.5 correct result is:
>
>        a
>        1
>
> While I knew this bug for some years already it didn't matter much in my 
> software. In 3.7.5 it hurts me due to the corrected behavior for "create 
> table ... as select ..." statements. In 3.3.6 the column names were 
> implicitly dequoted (which in my current opinion was incorrect) so the below 
> statement created the table as shown:
>
>        create table y as select [a] from (select * from x);
>        .schema y
>
> Output in version 3.3.6:
>
>        CREATE TABLE y(a int);
>
> In 3.7.5 with corrected behavior, the output looks like that (and is correct 
> according to the buggy select statement):
>
>        CREATE TABLE y("[a]" INT);
>
> I came across this issue as statements like the following failed with the 
> below mentioned error due to incorrect column names in the created tables:
>
>        create index [y.a] on y ([a]);
>
> Output in version 3.7.5:
>
>        Error: table y has no column named a
>
> I really would like to get that fixed in 3.7.6. At least a patch would be 
> welcome during the next week.
>
> Attached you'll find some statements to test with and the outputs of sqlite3 
> for versions 3.7.5 and 3.3.6.
>
> Bye.
> --
> Reinhard Nißl
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature suggestion for the Tcl interface : ability to use a list variable in combination with IN

[Alexey Pechnikov]

See
http://sqlite.mobigroup.ru/wiki?name=ext_intarray_tcl

09.02.2011 17:49 пользователь "Fredrik Karlsson" 
написал:
> Dear list,
>
> I find the IN operator quite useful for selecting a set number of things.
> However, I often have a Tcl list with the things I want to match
> already when I get to the stage there I should issue a SELECT on the
> database.
> I then paste all the elements of the list together with ',' or just ,
> chars and supply that inside a () to the sqlite3 database command.
>
> Would it not be a nice, and safer, addition to the interface to be
> able to do something like this (i.e. sot that there would be no syntax
> error at the end):
>
> --
> package require sqlite3
> sqlite3 db :memory:
> db eval {create table a (id INTEGER);}
> db eval {insert into a values (1);}
> db eval {insert into a values (2);}
> db eval {select * from a where id in (1,3);} vals {parray vals}
> vals(*) = id
> vals(id) = 1
> set alist [list 1 3]
> 1 3
> db eval {select * from a where id in $alist;} vals {parray vals}
> near "$alist": syntax error
> --
>
> Also seems much safer to have a proper binding of values here..
>
> /Fredrik
>
>
>
> --
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SIGSEGV Error when using sqlite3_exec( )

[Zaryab Munir (zmunir)]

Hi,

I am using an in-memory dbase in a multi-threaded application and have
the following two questions:
I create  dbase connections by each thread using the API:
{
Sqlite3 *db =3D NULL;
Sqlite3_open(":memory:", );
When I try to use sqlite3_exec( )   I get segmentation faults.
}
Question1:  Can I have multiple connections opened for each thread
to
the same in-memory dbase.  According to the documentation, the
sqlite3_open( ) API can be used multiple times to open multiple
connections to the same database, but its not clear is same applies
to
in-memory databases too.
Question2:  Why is sqlite3_exec( ) giving Segmentation Fault error:
segfault at 2b90153ac905 rip 2b90153ac905 rsp
7fff95b1d938
error 15
Segmentation fault


Below is my code:
uint32  aim_sql_open (char *ptr, sqlite3 **db)
{

int n;
n =3D sqlite3_open(ptr,db);

if( n !=3D SQLITE_OK )
{
/* zz get the wt_id */
printf("Error opening database for thread =3D %d.\n",n);
return (0);
}
else {
/* zz get the wt_id */
printf("Database open for thread =3D %d ok.\n",n);
}
}
NOTE:  sqlite3_open( ) returns SQLITE_OK and the value of db is
updated.

uint32 aim_db_init( ) {
char dbname[10];
char tbname[13];
char statement[760], *db_err;
sqlite3 *db =3D NULL;
int n;
bzero(dbname,10);
bzero(tbname,13);
bzero(statement,760);
memcpy(dbname,DB_NAME,9);
/* no need for dbname for inmemory dbase */
n =3D aim_sql_open(":memory:", );
memcpy(tbname,AIM_TABLE_NAME,12);
if(!strcmp(tbname,""))
return(FALSE);
sprintf(statement, "CREATE TABLE %s (%s INTEGER PRIMARY KEY,%s
DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s TEXT,%s TEXT,%s
INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s DATE,%s INTEGER,%s
DATE,%s
INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s INTEGER,%s DATE);",
 
tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
n =3D sqlite3_exec(db, statement, NULL, 0, _err);
if( n !=3D SQLITE_OK )
{
printf("Error executing: %s\n",db_err);
sqlite3_free(db_err);
return (FALSE);
}
=20

The backtrace from GDB is as follows:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x2b7070afe1a0 (LWP 699)]
0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
(gdb) bt
#0  0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
#1  0x2b70703a4120 in unixRandomness (NotUsed=3D,
nBuf=3D, zBuf=3D0x7fff3b203050 "") at
../sqlite3.c:27932
#2  0x2b707035b0f5 in sqlite3_randomness (N=3D4, pBuf=3D)
at ../sqlite3.c:13876
#3  0x2b707035b28f in writeJournalHdr (pPager=3D0x62b788)
at ../sqlite3.c:36096
#4  0x2b707035b4fb in pager_write (pPg=3D0x639e08) at
../sqlite3.c:39815
#5  0x2b7070366195 in sqlite3PagerWrite (pDbPage=3D0x7) at
../sqlite3.c:40151
#6  0x2b7070368466 in newDatabase (pBt=3D0x62b6d8) at
../sqlite3.c:47668
#7  0x2b707037a18c in sqlite3BtreeBeginTrans (p=3D0x62b678, =
wrflag=3D1)
at ../sqlite3.c:47798
#8  0x2b707037fa78 in sqlite3Step (p=3D0x63a988) at =
../sqlite3.c:63062
#9  0x2b7070383803 in sqlite3_step (pStmt=3D)
at ../sqlite3.c:58768
#10 0x2b7070386592 in sqlite3_exec (db=3D0x62b078,
zSql=3D0x7fff3b203b10 "CREATE TABLE aim_db_table (hash4ADandDC =
INTEGER
PRIMARY KEY,entrytime DATE,state INTEGER,bwgain INTEGER,cctimestamp
INTEGER,foreignkeyAD TEXT,popclass TEXT,deviceclass TEXT,vdoBitrate
INTEGER,qualityI"...,
xCallback=3D0, pArg=3D0x0, pzErrMsg=3D0x7fff3b203e38) at
../sqlite3.c:84165
#11 0x2b706fac52ac in aim_db_init () at aim_db_main.c:645
#12 0x0040566d in main ()


Regards,
Zaryab


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] help: sigsegv error using sqlite3_exec( )

[Zaryab Munir (zmunir)]

Hi,

I am using an in-memory dbase in a multi-threaded application and have
the following two questions:

I create  dbase connections by each thread using the API:

{
Sqlite3 *db = NULL;

Sqlite3_open(":memory:", );

When I try to use sqlite3_exec( )   I get segmentation faults.
}

Question1:  Can I have multiple connections opened for each thread to
the same in-memory dbase.  According to the documentation, the
sqlite3_open( ) API can be used multiple times to open multiple
connections to the same database, but its not clear is same applies to
in-memory databases too.

Question2:  Why is sqlite3_exec( ) giving Segmentation Fault error:
segfault at 2b90153ac905 rip 2b90153ac905 rsp 7fff95b1d938
error 15
Segmentation fault

Below is my code:
uint32  aim_sql_open (char *ptr, sqlite3 **db)
{

int n;
n = sqlite3_open(ptr,db);

if( n != SQLITE_OK )
{
/* zz get the wt_id */
printf("Error opening database for thread = %d.\n",n);
return (0);
}
else {
/* zz get the wt_id */
printf("Database open for thread = %d ok.\n",n);
}
}
NOTE:  sqlite3_open( ) returns SQLITE_OK and the value of db is updated.

uint32 aim_db_init( ) {
char dbname[10];
char tbname[13];
char statement[760], *db_err;
sqlite3 *db = NULL;
int n;
bzero(dbname,10);
bzero(tbname,13);
bzero(statement,760);
memcpy(dbname,DB_NAME,9);
/* no need for dbname for inmemory dbase */
n = aim_sql_open(":memory:", );
memcpy(tbname,AIM_TABLE_NAME,12);
if(!strcmp(tbname,""))
return(FALSE);
sprintf(statement, "CREATE TABLE %s (%s INTEGER PRIMARY KEY,%s
DATE,%s INTEGER,%s INTEGER,%s INTEGER,%s TEXT,%s TEXT,%s TEXT,%s
INTEGER,%s INTEGER,%s INTEGER,%s INTEGER,%s DATE,%s INTEGER,%s DATE,%s
INTEGER,%s DATE,%s INTEGER,%s DATE,%s INTEGER,%s INTEGER,%s DATE);",
tbname,COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,CO
L13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22);
n = sqlite3_exec(db, statement, NULL, 0, _err);
if( n != SQLITE_OK )
{
printf("Error executing: %s\n",db_err);
sqlite3_free(db_err);
return (FALSE);
}
 

The backtrace from GDB is as follows:
Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 0x2b7070afe1a0 (LWP 699)]
0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
(gdb) bt
#0  0x2b706fcc6905 in read () from /usr/lib64/libaimdb.so
#1  0x2b70703a4120 in unixRandomness (NotUsed=,
nBuf=, zBuf=0x7fff3b203050 "") at
../sqlite3.c:27932
#2  0x2b707035b0f5 in sqlite3_randomness (N=4, pBuf=)
at ../sqlite3.c:13876
#3  0x2b707035b28f in writeJournalHdr (pPager=0x62b788)
at ../sqlite3.c:36096
#4  0x2b707035b4fb in pager_write (pPg=0x639e08) at
../sqlite3.c:39815
#5  0x2b7070366195 in sqlite3PagerWrite (pDbPage=0x7) at
../sqlite3.c:40151
#6  0x2b7070368466 in newDatabase (pBt=0x62b6d8) at
../sqlite3.c:47668
#7  0x2b707037a18c in sqlite3BtreeBeginTrans (p=0x62b678, wrflag=1)
at ../sqlite3.c:47798
#8  0x2b707037fa78 in sqlite3Step (p=0x63a988) at ../sqlite3.c:63062
#9  0x2b7070383803 in sqlite3_step (pStmt=)
at ../sqlite3.c:58768
#10 0x2b7070386592 in sqlite3_exec (db=0x62b078,
zSql=0x7fff3b203b10 "CREATE TABLE aim_db_table (hash4ADandDC INTEGER
PRIMARY KEY,entrytime DATE,state INTEGER,bwgain INTEGER,cctimestamp
INTEGER,foreignkeyAD TEXT,popclass TEXT,deviceclass TEXT,vdoBitrate
INTEGER,qualityI"...,
xCallback=0, pArg=0x0, pzErrMsg=0x7fff3b203e38) at
../sqlite3.c:84165
#11 0x2b706fac52ac in aim_db_init () at aim_db_main.c:645
#12 0x0040566d in main ()


Regards,
Zaryab


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bi-directional unique

[Samuel Adam]

On Wed, 09 Feb 2011 20:14:19 -0500, Igor Tandetnik   
wrote:

> On 2/9/2011 7:29 PM, Samuel Adam wrote:
[…snip garbage…]
>
> It seems (1, 2) and (2, 1) would result in distinct xk values, thus
> defeating the point of the exercise. It is again possible to insert two
> pairs that differ only in order.

You are right.  I’ve now devised two different back-of-the-envelope proofs  
that I was trying to achieve the mathematically impossible.  (Now watch  
this:  For strike three, somebody will show that it is not only possible,  
but trivial.)

Incidentally, I believe I just provided an unintentional object lesson in  
the merit of being just a bit formal sometimes.  What I was trying to do  
(which may or may not have been obvious to Mr. Tandetnik, et al.) is to  
find

k = F(x, y) = F′(y, x)

such that k would retain the information of whether F or F′ was used.   
Q.E.D. (and affix palm to forehead).

On the bright side, this bungling on my part led me into an interesting  
general problem with sets and permutations.  That’s not topical, however,  
as I have already showed for Mr. Black’s purpose that extra information  
cannot be stored in k without breaking the equality.  Plus as I’ve said  
before[1] and yesterday quite well demonstrated, I am bad at math.

Very truly,

Samuel Adam ◊ 
763 Montgomery Road ◊ Hillsborough, NJ  08844-1304 ◊ United States
Legal advice from a non-lawyer: “If you are sued, don’t do what the
Supreme Court of New Jersey, its agents, and its officers did.”
http://www.youtube.com/watch?v=iT2hEwBfU1g

[1] http://www.mail-archive.com/sqlite-users@sqlite.org/msg56438.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regarding "Manual Control Of Query Plans"

[Sven L]

I remember why I added the sqlite_stat2 flag together with the ANALYZE command. 
It makes certain illformed queries efficient.
 
CREATE TABLE IF NOT EXISTS TC -- 10 rows
(
C INTEGER PRIMARY KEY,
Y CHAR(255) NOT NULL UNIQUE,
);

CREATE TABLE IF NOT EXISTS TB -- 100 rows
(
B INTEGER PRIMARY KEY, -- primary key!
X CHAR(255) NOT NULL UNIQUE
);
 
CREATE TABLE IF NOT EXISTS TA -- 10 rows
(
A INTEGER PRIMARY KEY,
C INTEGER NOT NULL,
B INTEGER NOT NULL,

UNIQUE (C, B) -- unique together with C!
);

Now, consider this query:
SELECT * FROM TB NATURAL JOIN TA; -- slow
vs
SELECT * FROM TA NATURAL JOIN TB; -- fast
 
That's why I needed the statistics. Otherwise, SQLite failed to select the best 
index. I understand it would choose the primary key in this case, which is of 
course wrong.
 
(I haven't tested the above for a while now, but I remember the problem I 
encountered had something to do with those tables.)
 
Regards

 
> From: slav...@bigfraud.org
> Date: Tue, 8 Feb 2011 16:53:52 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Regarding "Manual Control Of Query Plans"
> 
> 
> On 8 Feb 2011, at 4:22pm, Sven L wrote:
> 
> > Thank you very much for your detailed explanation!
> 
> You're welcome. I've actually never had to think out this feature of SQLite 
> before, so it was interesting for me too. I hope Richard or the rest of the 
> team will correct me if I got anything wrong.
> 
> > I will comment out my calls to ANALYZE, and see how my software performs.
> > 
> > The reason why I added it in the first place is that users are allowed to 
> > create their own queries, and since not all of them are SQL experts, I 
> > wanted the engine to be as tolerant as possible. Perhaps it's a better idea 
> > to simply crave decent SQL!
> 
> Allowing users to make up their own queries on the fly does make things a 
> little more difficult as you worked out: you can't pre-make good indexes. The 
> big server/client databases cache temporary indexes (and share them between 
> users), so they handle unexpected queries far better: if any query comes in 
> that doesn't suit any indexes it simply makes up a new temporary index and 
> keeps it in case it's needed later. SQLite can't do this because it's 
> designed for a tiny footprint and can't chew up lots of memory or disk space 
> without a good reason.
> 
> But your initial questions did sound a little like premature optimisation and 
> I think you'll get decent results without worrying too much about it. I'm 
> sure the overwhelming number of SQLite users have never used ANALYZE even 
> once.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG (3.7.5): identifier quotation characters get part of column names for certain statements

[Nißl Reinhard]

Hi,

identifier quotation characters ("[]) get part of column names for certain 
statements, i. e. it depends on the complexity of the statement to trigger this 
bug.

To reproduce the bug, type the following in sqlite3:

.headers ON
create table x(a int);
insert into x values (1);
select [a] from (select * from x);

You'll get the following "buggy" output:

[a]
1

The correct output is returned for this statement:

select [a] from x;

You'll get:

a
1

I've run into this bug after updating from 3.3.6 to 3.7.5. In 3.3.6, even the 
following statement returned an incorrect column name:

select [a] from x group by a;

The 3.3.6 result was:

[a]
1

The 3.7.5 correct result is:

a
1

While I knew this bug for some years already it didn't matter much in my 
software. In 3.7.5 it hurts me due to the corrected behavior for "create table 
... as select ..." statements. In 3.3.6 the column names were implicitly 
dequoted (which in my current opinion was incorrect) so the below statement 
created the table as shown:

create table y as select [a] from (select * from x);
.schema y

Output in version 3.3.6:

CREATE TABLE y(a int);

In 3.7.5 with corrected behavior, the output looks like that (and is correct 
according to the buggy select statement):

CREATE TABLE y("[a]" INT);

I came across this issue as statements like the following failed with the below 
mentioned error due to incorrect column names in the created tables:

create index [y.a] on y ([a]);

Output in version 3.7.5:

Error: table y has no column named a

I really would like to get that fixed in 3.7.6. At least a patch would be 
welcome during the next week.

Attached you'll find some statements to test with and the outputs of sqlite3 
for versions 3.7.5 and 3.3.6.

Bye.
--
Reinhard Nißl
.headers ON
create table x(a int);
insert into x values (1);
select [a] from x;
select [a] from x group by a;
select [a] from (select a from x);
select [a] from (select a from x) group by a;
create table y as select [a] from (select a from x) group by a;
create index [y.a] on y([a]);
select "a" from x;
select "a" from x group by a;
select "a" from (select a from x);
select "a" from (select a from x) group by a;
create table z as select "a" from (select a from x) group by a;
create index "z.a" on z("a");
.schema
drop table x;
drop table y;
drop table z;
create table x([a.b] int);
insert into x values (1);
select [a.b] from x;
select [a.b] from x group by [a.b];
select [a.b] from (select [a.b] from x);
select [a.b] from (select [a.b] from x) group by [a.b];
create table y as select [a.b] from (select [a.b] from x) group by [a.b];
create index [y.a.b] on y([a.b]);
select "a.b" from x;
select "a.b" from x group by "a.b";
select "a.b" from (select "a.b" from x);
select "a.b" from (select "a.b" from x) group by "a.b";
create table z as select "a.b" from (select "a.b" from x) group by "a.b";
create index "z.a.b" on z("a.b");
.schema
.quit
a
1
a
1
[a]
1
[a]
1
a
1
a
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y("[a]" INT);
CREATE TABLE z("""a""" INT);
a.b
1
a.b
1
[a.b]
1
[a.b]
1
a.b
1
a.b
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("[a.b]" INT);
CREATE TABLE z("""a.b""" INT);
Error: near line 9: table y has no column named a
Error: near line 15: table z has no column named a
Error: near line 27: table y has no column named a.b
Error: near line 33: table z has no column named a.b
a
1
[a]
1
[a]
1
[a]
1
a
1
"a"
1
"a"
1
"a"
1
CREATE TABLE x(a int);
CREATE TABLE y(a int);
CREATE TABLE z(a int);
CREATE INDEX [y.a] on y([a]);
CREATE INDEX "z.a" on z("a");
a.b
1
[a.b]
1
[a.b]
1
[a.b]
1
a.b
1
"a.b"
1
"a.b"
1
"a.b"
1
CREATE TABLE x([a.b] int);
CREATE TABLE y("a.b" int);
CREATE TABLE z("a.b" int);
CREATE INDEX [y.a.b] on y([a.b]);
CREATE INDEX "z.a.b" on z("a.b");
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Gorshkov]

I'm having the same problem with my application.

Basically, it's a combination of jukebox/music management app I've been 
developing myself over the last few years. I had always used the dump 
commands to back up & restore the database, given that I develop on both 
linux and windows. When the backup commands were introduced, I switched 
them them. Things have been working fine.

General procedure is:
- backup database under windows, to my server
- reboot to linux
- restore from windows backup
- work
- backup database to my server
- reboot to windows
- restore
- work

and so on.
Until a week or so ago, both linux and windows were running 3.6.14. 
Recent upgrade of my gentoo system, however, upgraded sqlite there to 3.7.2

Now what I get is the following:

-backup database under windows to my server
- reboot to linux
- restore from windows backup
when I try to update or do anything in the database, my app crashes with 
the malformed database error.

If I nuke the linux database file, re-create from scratch instead of 
backup restore and load the virgin file from a dump, everything is ok.

On 2011-02-09 13:25, Dan Kennedy wrote:
>
>> I didn't find a way yet to reproduce the issue with a "clean" database.
>> Only way I can reproduce it is with some of the database, like the
>> test.db3. So I'm running out of ideas.
>
> This is the theory. test.db3 is an auto-vacuum database.
>
> http://www.sqlite.org/src/info/89b8c9ac54
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: woensdag 9 februari 2011 19:26
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
> 
> 
> > I didn't find a way yet to reproduce the issue with a "clean"
> database.
> > Only way I can reproduce it is with some of the database, like the
> > test.db3. So I'm running out of ideas.
> 
> This is the theory. test.db3 is an auto-vacuum database.
> 
>http://www.sqlite.org/src/info/89b8c9ac54
> 
> Dan.
> 

You are correct, the test.db3 is the only database with the PRAGMA
AUTO_VACUUM 1. Looking at the ticket, it looks likely that this issue is
indeed triggered. 

Only thing I don't know, is how this PRAGMA changed. Can't remember that
this is set in any way, maybe a viewer tool changed it... 

Thanks for helping!
Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] determine SQLITE_MAX_LENGTH or sql_limit/sqlite3_limit

[Vannus]

That should do the trick, thanks for pointing it out.

On 10 February 2011 06:18, Dan Kennedy  wrote:

> On 02/10/2011 01:56 AM, Vannus wrote:
> > Zeoslib is reading sqlite field lengths incorrectly, as it checks for
> > brackets after the field typename ie. CHAR(123)
> > presumably this is only affecting me because I haven't defined field
> lengths
> > in my sqlite3 db.
> >
> > I don't want to hard-code 1,000,000,000 or 2147483647 in as the field
> length
> > - but how do I check what SQLITE_MAX_LENGTH or the limit imposed by
> > sql_limit or sqlite3_limit is?
>
> At http://www.sqlite.org/c3ref/limit.html the third paragraph
> has:
>
>   Regardless of whether or not the limit was changed, the
>   sqlite3_limit() interface returns the prior value of the limit.
>   Hence, to find the current value of a limit without changing it,
>   simply invoke this interface with the third parameter set to -1.
>
> Maybe you can use that.
>
> Dan.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users