date:20080708

Re: [sqlite] regarding internal design of SQLite

2008-07-08 Thread Nicolas Williams

On Tue, Jul 08, 2008 at 07:44:03PM -0400, Alex Katebi wrote:
> No I don't mean offline use. I mean it would be nice to have links on the
> sqlite.org for all documents and resources.

I thought there was:

http://sqlite.org/docs.html

(which is linked to from the home page).

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

Re: [sqlite] regarding internal design of SQLite

2008-07-08 Thread Alex Katebi

No I don't mean offline use. I mean it would be nice to have links on the
sqlite.org for all documents and resources.

On Tue, Jul 8, 2008 at 1:42 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Alex Katebi wrote:
> > Is there any way to get to all of these docs you mentioned from the home
> > page of the sqlite.org?
> > Thanks,
> > -Alex
> You mean, for offline use? If yes, then I'd click on Download, then
> scroll down to Documentation, then I'd clock on sqlite_docs_3_5_9.zip:
> http://sqlite.org/sqlite_docs_3_5_9.zip
> ___
> 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] View with Dynamic Fields ?

2008-07-08 Thread Dennis Cote

Andrea Connell wrote:
> 
> I am still holding a shred of hope for a trigger that can
> recreate the view whenever the questions table is modified 
> 

I wouldn't hold much hope for that. Triggers can only execute insert, 
update, delete, or select SQL statements. There is no way to execute a 
create table or create view command.

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

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Tom Epperly

Using Mihai's suggestion, I determined the list of options turned on for 
-O2 that aren't turned on for -O.  Trying each of the additional flags 
one by one showed that only -finline-small-functions causes the bug to 
appear.  Here is sqlite3 compiled with '-g -O2 -fno-inline-small-functions".
$ make CFLAGS="-g -O2 -fno-inline-small-functions" clean sqlite3 > 
/dev/null ; ./sqlite3 foo "select 1.0 / 3.0; "
0.333
$
Here is sqlite3 compiled with "-g -O -finline-small-functions".
$ make CFLAGS="-g -O -finline-small-functions" clean sqlite3 > /dev/null 
; ./sqlite3 foo "select 1.0 / 3.0; "

$


Mihai Limbasan wrote:
>
> You can check exactly which flags gcc turns on by -O2 (or any other flag 
> combination, for that matter, including the machine-specific -m flag 
> family) by following this procedure:
>
> 1. Create an empty source file:
>
> touch dummy.c
>
> 2. Run it though the compiler pass, adding -Q -v to the command line:
>
> gcc -c -Q -v dummy.c
>
>
> Another option is checking gcc's specs file, but I think that's much 
> more cumbersome.
>
> Hope this helps.
>
> Mihai Limbasan
> ___
> 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] Clear Screen Command

2008-07-08 Thread Fred J. Stephens

Mihai Limbasan wrote:

> I don't think it's too OS-specific - I think it's really outside the 
> scope of the sqlite3 shell. The shell is really not intended to be a 
> presentation layer, it's supposed to be a demo application, debugging 
> helper, and quick-and-dirty manipulation tool. If a clear screen command 
> were to be implemented, then what next? Cursor positioning? Perhaps 
> output coloring? And so on :)
I asked this question a while back and was told of the Ctrl-L command. 
That works fine, but I think regarding the comments above, a "clear 
screen" command would help with all those uses without overly 
complicating the sqlite3 shell.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Harold Wood

if sqlite supported the pivot command

Woody

--- On Tue, 7/8/08, Andrea Connell <[EMAIL PROTECTED]> wrote:

From: Andrea Connell <[EMAIL PROTECTED]>
Subject: Re: [sqlite] View with Dynamic Fields ?
To: "General Discussion of SQLite Database" 
Date: Tuesday, July 8, 2008, 3:27 PM

Thanks Chris & Dennis for the group_concat tip... It is an interesting
idea but I don't think it makes sense to use this time. If I have to
parse the results in my code, I might as well just get the answers in
separate rows and group them together in code without having to worry
about returning values for the non-answered questions. 

I knew this wouldn't be an easy thing to solve, but appreciate the
input. I am still holding a shred of hope for a trigger that can
recreate the view whenever the questions table is modified but I haven't
put much thought into it yet and I'm sure it's just as challenging. 

I'm going to keep working on this and see if I can come up with
anything. If anybody thinks of something, let me know.

I know that I could do this in code, and at this point it would probably
take less time, but I'd really like to see if this is possible more than
anything.

Thanks

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 08, 2008 1:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] View with Dynamic Fields ?

This gives the same result set you got and does not need to be edited:

select
   applicantid,
   group_concat(answer, '|')
   from
 (select applicantid, answer from tblanswers order by questionid)
   group by applicantid;

The group_concat() function is part of recent versions of SQLite. It is
returning a single string rather than columns, but it can be parsed. The
subquery ordering by questionid is needed to ensure that the columns
(answers) from all respondents are in the same order.

However, this will only work if tblanswers will always have one record
for every question. That is, if your applicants skip one or more
questions, you will still need to insert a record for skipped
questionids, perhaps with a default answer like 'NOT ANSWERED'.
Similarly, if you add new questions to tblquestions later, you will need
to insert 'NO ANSWER' 
values into tblanswers for existing applicantids, othewise the columns
(answers) will not align.

Chris

On Tue, 8 Jul 2008, Andrea Connell wrote:

> I'm not sure if what I want is possible to do in SQL, but I -am- sure 
> that one of you will know.
> Given two tables - one with questions to ask applicants and one with 
> an applicant's answer to a particular question - I want to make a 
> flattened view with all of an applicant's answers in one row. This is 
> easy enough to do when I know ahead of time which questions are in the

> first table, but I don't like the hard-coded approach.
>
> My schema is as follows:
>
> CREATE TABLE tblquestions (questionid int, question varchar(100)); 
> INSERT INTO "tblquestions" VALUES(1,'whats up'); INSERT
INTO 
> "tblquestions" VALUES(2,'how are you'); INSERT INTO
"tblquestions" 
> VALUES(3,'whats your name'); CREATE TABLE tblanswers (questionid
int, 
> applicantid int, answer varchar(2500)); INSERT INTO "tblanswers"

> VALUES(1,100,'stuff for answer one'); INSERT INTO
"tblanswers" 
> VALUES(2,100,'stuff for answer two'); INSERT INTO
"tblanswers" 
> VALUES(3,100,'stuff for answer three'); INSERT INTO
"tblanswers" 
> VALUES(1,200,'random text one'); INSERT INTO
"tblanswers" 
> VALUES(2,200,'random text two'); INSERT INTO
"tblanswers" 
> VALUES(3,200,'random text three');
>
>
> Here is the view I have come up with so far, which would require 
> editing whenever an insert or delete is done on tblQuestions.
>
> CREATE VIEW allanswers as
> SELECT applicantid,
>  (select answer from tblanswers Z where questionid = 1 and

> Z.applicantid = A.applicantid) As Answer1,
>  (select answer from tblanswers Z where questionid = 2 and

> Z.applicantid = A.applicantid) As Answer2,
>  (select answer from tblanswers Z where questionid = 3 and

> Z.applicantid = A.applicantid) As Answer3 FROM tblanswers A group by 
> applicantid;
>
> sqlite> select * from allanswers;
> 100|stuff for answer one|stuff for answer two|stuff for answer three
> 200|random text one|random text two|random text three
>
>
> Has anybody come across a problem like this and found a reasonable 
> dynamic solution? Even something like a trigger on tblQuestions to 
> change the View would be great, I'm just not sure about the SQL 
> involved. If it helps, I don't really care what the columns end up 
> being named. Also I can't guarantee that the questionids will be 
> consecutive or in any order.
>
> Thanks,
> Andrea
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

Re: [sqlite] Clear Screen Command

2008-07-08 Thread Samuel Neff

I've wished there was a ".clear" command often.

And I'm sure it's much easier to implement than output coloring.  :-)

Sam

-
We're Hiring! Seeking passionate Flex, C#, or C++ (RTSP, H264) developer in
the Washington D.C. Contact [EMAIL PROTECTED]

On Tue, Jul 8, 2008 at 3:35 PM, Mihai Limbasan <[EMAIL PROTECTED]> wrote:

> Keith Goodman wrote:
>
>> My guess is that that is too OS specific to add to sqlite. If you are
>> using linux you could try control-l to clear the screen.
>>
>
> I don't think it's too OS-specific - I think it's really outside the scope
> of the sqlite3 shell. The shell is really not intended to be a presentation
> layer, it's supposed to be a demo application, debugging helper, and
> quick-and-dirty manipulation tool. If a clear screen command were to be
> implemented, then what next? Cursor positioning? Perhaps output coloring?
> And so on :)
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Igor Tandetnik

Andrea Connell <[EMAIL PROTECTED]> wrote:
> Here is the view I have come up with so far, which would require
> editing whenever an insert or delete is done on tblQuestions.
>
> CREATE VIEW allanswers as
> SELECT applicantid,
>  (select answer from tblanswers Z where questionid = 1 and
> Z.applicantid = A.applicantid) As Answer1,
>  (select answer from tblanswers Z where questionid = 2 and
> Z.applicantid = A.applicantid) As Answer2,
>  (select answer from tblanswers Z where questionid = 3 and
> Z.applicantid = A.applicantid) As Answer3
> FROM tblanswers A
> group by applicantid;
>
> sqlite> select * from allanswers;
> 100|stuff for answer one|stuff for answer two|stuff for answer three
> 200|random text one|random text two|random text three

SQL is designed to handle recordsets with fixed number of columns and 
variable number of rows. It is not set up to handle variable number of 
columns.

What do you feel you need such a beast for? Imagine you somehow have 
it - what do you plan to do with it?

Igor Tandetnik



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

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Nicolas Williams

On Tue, Jul 08, 2008 at 09:09:05AM -0700, Andrea Connell wrote:
> I'm not sure if what I want is possible to do in SQL, but I -am- sure
> that one of you will know. 
> Given two tables - one with questions to ask applicants and one with an
> applicant's answer to a particular question - I want to make a flattened
> view with all of an applicant's answers in one row. This is easy enough
> to do when I know ahead of time which questions are in the first table,
> but I don't like the hard-coded approach. 
>  
> My schema is as follows:
>  
> CREATE TABLE tblquestions (questionid int, question varchar(100));
> INSERT INTO "tblquestions" VALUES(1,'whats up');
> INSERT INTO "tblquestions" VALUES(2,'how are you');
> INSERT INTO "tblquestions" VALUES(3,'whats your name');
> CREATE TABLE tblanswers (questionid int, applicantid int, answer
> varchar(2500));
> INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer one');
> INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer two');
> INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer three');
> INSERT INTO "tblanswers" VALUES(1,200,'random text one');
> INSERT INTO "tblanswers" VALUES(2,200,'random text two');
> INSERT INTO "tblanswers" VALUES(3,200,'random text three');

I think what you want is a join that produces rows with applicantid,
question, and answer columns:

SELECT a.applicantid AS applicantid, q.question AS question,
a.answer AS answer FROM tblanswers a JOIN tblquestions q ON
questionid;

At least that's the right approach if you want to do anything besides
displaying the data.  If you just want to display the data as
applicantid, questions, and answers, then you should try the
group_concat() approach others posted about.

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

Re: [sqlite] Clear Screen Command

2008-07-08 Thread Mihai Limbasan


Keith Goodman wrote:

My guess is that that is too OS specific to add to sqlite. If you are
using linux you could try control-l to clear the screen.


I don't think it's too OS-specific - I think it's really outside the 
scope of the sqlite3 shell. The shell is really not intended to be a 
presentation layer, it's supposed to be a demo application, debugging 
helper, and quick-and-dirty manipulation tool. If a clear screen command 
were to be implemented, then what next? Cursor positioning? Perhaps 
output coloring? And so on :)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Andrea Connell

Thanks Chris & Dennis for the group_concat tip... It is an interesting
idea but I don't think it makes sense to use this time. If I have to
parse the results in my code, I might as well just get the answers in
separate rows and group them together in code without having to worry
about returning values for the non-answered questions. 

I knew this wouldn't be an easy thing to solve, but appreciate the
input. I am still holding a shred of hope for a trigger that can
recreate the view whenever the questions table is modified but I haven't
put much thought into it yet and I'm sure it's just as challenging. 

I'm going to keep working on this and see if I can come up with
anything. If anybody thinks of something, let me know.

I know that I could do this in code, and at this point it would probably
take less time, but I'd really like to see if this is possible more than
anything.

Thanks

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, July 08, 2008 1:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] View with Dynamic Fields ?

This gives the same result set you got and does not need to be edited:

select
   applicantid,
   group_concat(answer, '|')
   from
 (select applicantid, answer from tblanswers order by questionid)
   group by applicantid;

The group_concat() function is part of recent versions of SQLite. It is
returning a single string rather than columns, but it can be parsed. The
subquery ordering by questionid is needed to ensure that the columns
(answers) from all respondents are in the same order.

However, this will only work if tblanswers will always have one record
for every question. That is, if your applicants skip one or more
questions, you will still need to insert a record for skipped
questionids, perhaps with a default answer like 'NOT ANSWERED'.
Similarly, if you add new questions to tblquestions later, you will need
to insert 'NO ANSWER' 
values into tblanswers for existing applicantids, othewise the columns
(answers) will not align.

Chris

On Tue, 8 Jul 2008, Andrea Connell wrote:

> I'm not sure if what I want is possible to do in SQL, but I -am- sure 
> that one of you will know.
> Given two tables - one with questions to ask applicants and one with 
> an applicant's answer to a particular question - I want to make a 
> flattened view with all of an applicant's answers in one row. This is 
> easy enough to do when I know ahead of time which questions are in the

> first table, but I don't like the hard-coded approach.
>
> My schema is as follows:
>
> CREATE TABLE tblquestions (questionid int, question varchar(100)); 
> INSERT INTO "tblquestions" VALUES(1,'whats up'); INSERT INTO 
> "tblquestions" VALUES(2,'how are you'); INSERT INTO "tblquestions" 
> VALUES(3,'whats your name'); CREATE TABLE tblanswers (questionid int, 
> applicantid int, answer varchar(2500)); INSERT INTO "tblanswers" 
> VALUES(1,100,'stuff for answer one'); INSERT INTO "tblanswers" 
> VALUES(2,100,'stuff for answer two'); INSERT INTO "tblanswers" 
> VALUES(3,100,'stuff for answer three'); INSERT INTO "tblanswers" 
> VALUES(1,200,'random text one'); INSERT INTO "tblanswers" 
> VALUES(2,200,'random text two'); INSERT INTO "tblanswers" 
> VALUES(3,200,'random text three');
>
>
> Here is the view I have come up with so far, which would require 
> editing whenever an insert or delete is done on tblQuestions.
>
> CREATE VIEW allanswers as
> SELECT applicantid,
>  (select answer from tblanswers Z where questionid = 1 and

> Z.applicantid = A.applicantid) As Answer1,
>  (select answer from tblanswers Z where questionid = 2 and

> Z.applicantid = A.applicantid) As Answer2,
>  (select answer from tblanswers Z where questionid = 3 and

> Z.applicantid = A.applicantid) As Answer3 FROM tblanswers A group by 
> applicantid;
>
> sqlite> select * from allanswers;
> 100|stuff for answer one|stuff for answer two|stuff for answer three
> 200|random text one|random text two|random text three
>
>
> Has anybody come across a problem like this and found a reasonable 
> dynamic solution? Even something like a trigger on tblQuestions to 
> change the View would be great, I'm just not sure about the SQL 
> involved. If it helps, I don't really care what the columns end up 
> being named. Also I can't guarantee that the questionids will be 
> consecutive or in any order.
>
> Thanks,
> Andrea
> ___
> 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 mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clear Screen Command

2008-07-08 Thread Keith Goodman

On Mon, Jul 7, 2008 at 7:07 PM, Akbar Anderson <[EMAIL PROTECTED]> wrote:
> Is there a command to clear the screen, or will there be one?
> when line wrapping happens at the bottom of the screen, it looks as
> if it wraps over on the same line. just  would like to see a .clear
> or .windex command.

My guess is that that is too OS specific to add to sqlite. If you are
using linux you could try control-l to clear the screen.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Clear Screen Command

2008-07-08 Thread Akbar Anderson

Is there a command to clear the screen, or will there be one?
when line wrapping happens at the bottom of the screen, it looks as  
if it wraps over on the same line. just  would like to see a .clear  
or .windex command.

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

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread cmartin


This gives the same result set you got and does not need to be edited:

select
   applicantid,
   group_concat(answer, '|')
   from
 (select applicantid, answer from tblanswers order by questionid)
   group by applicantid;

The group_concat() function is part of recent versions of SQLite. It is 
returning a single string rather than columns, but it can be parsed. The 
subquery ordering by questionid is needed to ensure that the columns 
(answers) from all respondents are in the same order.

However, this will only work if tblanswers will always have one record for 
every question. That is, if your applicants skip one or more questions, 
you will still need to insert a record for skipped questionids, perhaps 
with a default answer like 'NOT ANSWERED'. Similarly, if you add new 
questions to tblquestions later, you will need to insert 'NO ANSWER' 
values into tblanswers for existing applicantids, othewise the columns 
(answers) will not align.

Chris

On Tue, 8 Jul 2008, Andrea Connell wrote:

> I'm not sure if what I want is possible to do in SQL, but I -am- sure
> that one of you will know.
> Given two tables - one with questions to ask applicants and one with an
> applicant's answer to a particular question - I want to make a flattened
> view with all of an applicant's answers in one row. This is easy enough
> to do when I know ahead of time which questions are in the first table,
> but I don't like the hard-coded approach.
>
> My schema is as follows:
>
> CREATE TABLE tblquestions (questionid int, question varchar(100));
> INSERT INTO "tblquestions" VALUES(1,'whats up');
> INSERT INTO "tblquestions" VALUES(2,'how are you');
> INSERT INTO "tblquestions" VALUES(3,'whats your name');
> CREATE TABLE tblanswers (questionid int, applicantid int, answer
> varchar(2500));
> INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer one');
> INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer two');
> INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer three');
> INSERT INTO "tblanswers" VALUES(1,200,'random text one');
> INSERT INTO "tblanswers" VALUES(2,200,'random text two');
> INSERT INTO "tblanswers" VALUES(3,200,'random text three');
>
>
> Here is the view I have come up with so far, which would require editing
> whenever an insert or delete is done on tblQuestions.
>
> CREATE VIEW allanswers as
> SELECT applicantid,
>  (select answer from tblanswers Z where questionid = 1 and
> Z.applicantid = A.applicantid) As Answer1,
>  (select answer from tblanswers Z where questionid = 2 and
> Z.applicantid = A.applicantid) As Answer2,
>  (select answer from tblanswers Z where questionid = 3 and
> Z.applicantid = A.applicantid) As Answer3
> FROM tblanswers A
> group by applicantid;
>
> sqlite> select * from allanswers;
> 100|stuff for answer one|stuff for answer two|stuff for answer three
> 200|random text one|random text two|random text three
>
>
> Has anybody come across a problem like this and found a reasonable
> dynamic solution? Even something like a trigger on tblQuestions to
> change the View would be great, I'm just not sure about the SQL
> involved. If it helps, I don't really care what the columns end up being
> named. Also I can't guarantee that the questionids will be consecutive
> or in any order.
>
> Thanks,
> Andrea
> ___
> 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] View with Dynamic Fields ?

2008-07-08 Thread Dennis Cote

Andrea Connell wrote:
> 
> I want one row with all answers for an applicant, and I want it to work
> for an arbitrary number of rows in tblQuestions or be able to update
> itself on an insert or delete. Any ideas?
> 

If you want each answer in it's own column, I don't think it can be done 
without dynamically creating the view for each set of questions. If the 
answers can all be combined into one column you should be able to use 
the group_concat() function to combine all the answers into a single 
string with an arbitrary separator string.

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

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Andrea Connell


Harold Wood wrote:
>my approah would be a table for applicants, then the table for answers
would have the applicantid and the questionid as well as the answer.
>
>CREATE TABLE tblApplicants (applicantid int, applicantname
varchar(100)); 
>now just do a select joining the tables
>
>select ap.applicantname, qu.question, an.answer from tblanswers an
inner join tblApplicants ap on ap.applicantid = an.applicantid inner
join tblquestions qu on qu.questionid = an.questionid
>-- optional where clause to select just 1 applicant where
ap.applicantid = 1
>-- optional order by clause to make it neat order by applicantname asc,
qu.questionid asc
>
>

I am planning on having an applicant table (I guess I just left that to
be assumed in the first post - sorry) but that isn't going to help my
need at all.

Your query gives me these results:

andrea|whats up|stuff for answer one
andrea|how are you|stuff for answer two
andrea|whats your name|stuff for answer three
todd|whats up|random text one
todd|how are you|random text two
todd|whats your name|random text three

When what I want is this:

100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three


I want one row with all answers for an applicant, and I want it to work
for an arbitrary number of rows in tblQuestions or be able to update
itself on an insert or delete. Any ideas?

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

Re: [sqlite] Date and time in RFC 822

2008-07-08 Thread Dennis Cote

Wojciech wrote:
> I would ask, if there is any possibility to sort data in sqlite tables by
> date, which is stores in RFC 822 format. I have data in this format, which
> comes from RSS channels - in RSS specification RFC 822 it's required.
> 
> Sample date looks like that: Sat, 07 Sep 2002 00:00:01 GMT
> 

I think you will have to create a custom function that converts your RFC 
822 date strings into ISO 8601 date strings. The ISO 8601 strings will 
be sorted into date order by a simply lexical sort.

   SELECT MAX(RFC822toISO8601(date_field)) FROM table

User defined functions are described at http://www.sqlite.org/capi3.html

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

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Mihai Limbasan

Tom Epperly wrote:
> [EMAIL PROTECTED]/tmp/sqlite-amalgamation-3.5.9]>gcc --version
> gcc (Debian 4.3.1-4) 4.3.1
> Copyright (C) 2008 Free Software Foundation, Inc.
> This is free software; see the source for copying conditions.  There is NO
> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
>   


You can check exactly which flags gcc turns on by -O2 (or any other flag 
combination, for that matter, including the machine-specific -m flag 
family) by following this procedure:

1. Create an empty source file:

touch dummy.c

2. Run it though the compiler pass, adding -Q -v to the command line:

gcc -c -Q -v dummy.c


Another option is checking gcc's specs file, but I think that's much 
more cumbersome.

Hope this helps.

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

[sqlite] Date and time in RFC 822

2008-07-08 Thread Wojciech

Hi,

I would ask, if there is any possibility to sort data in sqlite tables by
date, which is stores in RFC 822 format. I have data in this format, which
comes from RSS channels - in RSS specification RFC 822 it's required.

Sample date looks like that: Sat, 07 Sep 2002 00:00:01 GMT

I tried with something like 

SELECT MAX(date(date_field)) FROM table

...but it isn't working. I readed
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions and searched a
couple of web resources, but I didn't find any working solution.

Thank You kindly for any help,

WK


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

Re: [sqlite] regarding internal design of SQLite

2008-07-08 Thread Mihai Limbasan

Alex Katebi wrote:
> Is there any way to get to all of these docs you mentioned from the home
> page of the sqlite.org?
> Thanks,
> -Alex
You mean, for offline use? If yes, then I'd click on Download, then 
scroll down to Documentation, then I'd clock on sqlite_docs_3_5_9.zip: 
http://sqlite.org/sqlite_docs_3_5_9.zip
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] regarding internal design of SQLite

2008-07-08 Thread Alex Katebi

Is there any way to get to all of these docs you mentioned from the home
page of the sqlite.org?
Thanks,
-Alex


On Tue, Jul 8, 2008 at 2:31 AM, Roger Binns <[EMAIL PROTECTED]> wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Aditya Bhave (adbhave) wrote:
> > I am interested in learning about the internals of SQLite.
>
> The SQLite documentation page at http://sqlite.org/docs.html contains
> almost all the information you could possibly want.
>
> > How it parses
> > SQL strings, what goes on the parsing stack,
>
> A parser generator named Lemon is used which is documented at
> http://www.hwaci.com/sw/lemon/
>
> The file containing the SQLite grammar and what actions are performed is
> src/parse.y
>
> > how it executes SQL statements etc.
>
> A virtual machine is used since it provides an easy way to encode state
> (eg between calls to sqlite3_step).  The broad principles are documented
> at http://sqlite.org/vdbe.html and the machine opcodes are documented at
> http://sqlite.org/opcode.html
>
> This paper contrasts stack based vs register based virtual machines.
> SQLite used to be stack based and moved to register based a few
> revisions ago.  (Note that the virtual machine is not exposed to users
> of SQLite so the backend is irrelevant to the user, as long as it works)
>
>  http://www.usenix.org/events/vee05/full_papers/p153-yunhe.pdf
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIcwm1mOOfHg372QQRAuVbAJ9cli9pD9enCHZr8yDDWgJ9ghsvfACgyjXe
> zIcyPCpKe1zu5TPKOFefbvY=
> =+mJS
> -END PGP SIGNATURE-
> ___
> 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] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Shane Harrelson

I'm not a very knowledgeable on Debian distributions, but
http://packages.qa.debian.org/g/gcc-4.3.html
indicates that gcc (Debian 4.3.1-4) which you posted that you are using was
only made available 2008-07-01,
is marked as "unstable", and hasn't even made it to their testing stage
yet.  Is there a way you could test with an
earlier, stable, GCC package?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] multithreaded app and reload from disk for second db handle?

2008-07-08 Thread xu123

 
hi,

Despite the threads-are-evil claim... 

What happens with respect to the page cache if I have an application (= one 
multi-threaded process) opening two DB handles (sqlite3_open()) - one DB handle 
dedicated to one thread. Do multilpe db handles in the same process cause 
sqlite to reload the whole data set from disk after another db handle in the 
same process has written to the database. 

Note: Semantically, I always translate DB handle in the context of sqlite to 
connection. However, lets use the term DB handle to make 
things maybe a little less confusing. I hope I stick to db handle for the rest 
of the e-mail. Also assume standard Posix threads (pthreads) 
semantics under Unix/Linux.

thread t1 - db_handle1
thread t2 - db_handle2

Now I start a normal write transaction (BEGIN, not BEGIN IMMEDIATE, 
not BEGIN EXCLUSIVE) on the db_handle1:

thread 1{
 sqlite3_open (/mnt/ext3/mydatabase, dbhandle1)
 sqlite3_exec (dbhandle1, BEGIN, ..)
 sqlite3_exec (dbhandle1, insert into table1 values (1), ..)
 sqlite3_exec (dbhandle1, COMMIT, ..) 
}

and read transactions in thread 2

thread 2{
 sqlite3_open (/mnt/ext3/mydatabase, dbhandle2)
 sqlite3_exec (dbhandle2, BEGIN, ..)
 sqlite3_exec (dbhandle2, select * from table1, ..)
 sqlite3_exec (dbhandle2, COMMIT, ..) 
}

and they get eventually executed like this:

1: t1: sqlite3_open (/mnt/ext3/mydatabase, dbhandle1)
2: t2: sqlite3_open (/mnt/ext3/mydatabase, dbhandle2)
3: t2: sqlite3_exec (dbhandle2, BEGIN, ..)
4: t2: sqlite3_exec (dbhandle2, select * from table1, ..)
5: t2: sqlite3_exec (dbhandle2, COMMIT, ..) 
6: t1: sqlite3_exec (dbhandle1, BEGIN, ..)
7: t1: sqlite3_exec (dbhandle1, insert into table1 values 
(1), ..)
8: t1: sqlite3_exec (dbhandle1, COMMIT, ..) 
9: t2: sqlite3_exec (dbhandle2, BEGIN, ..)
10: t2: sqlite3_exec (dbhandle2, select * from table1, ..)
11: t2: sqlite3_exec (dbhandle2, COMMIT, ..) 

My understanding of the documentation regarding the locking protocol 
(http://www.sqlite.org/lockingv3.html) is that threads are not treated 
differently than processes. Which would mean that eventhough both threads could 
have access to same page cache (in the same process) they dont use it. 
However when running the test as described below against sqlite 3.5.9 
(amalgamation), noweher between step 8 and 11 does it do any file access (not 
even fcntl()s). Which makes me suspect that thread1s db_handle1 and 
thread2s db_handle2 use the same page cache as thread2 clearly sees the 
changes done in steps 6-8.

So the question is: is the page cache actually shared even with an explicit 
sqlite3_enable_shared_cache(0) before any other sqlite3 operation? Naturally 
with sqlite3_enable_shared_cache(1) sqlite3 goes into table-locking mode which 
is not want Im loooking for. My bigger concern here is the reloading of 
the page cache from disk (or OSs buffer cache) after write transactions in 
the same process. Do read transactions on different db handles in the same 
process context cause a reload of the page cache?

I tried to dig through the source code, but thats, eventhough nicely 
documented, still to confusing to easily follow up whats going on. Could 
you point out some key points in the source code to help in understanding 
whats going on.

Markus

PS: Below some sample code. Simply compile and run with sampleapp 
db-file. Note that it deletes the file sfirst if it exists. Run the 
application with e.g. strace to see system calls. The application 
creates two db handles to the same db (one in the main thread and one in a 
newly started thread). The first one write and the second one reads only. 

#include stdio.h
#include stdlib.h
#include sqlite3.h
#include pthread.h
#include unistd.h

/*
* Sample code. No guarantees for correctness
*/

/*
* some global variables that are quickndirty but do the job
*/
char** argv_global;

/*
* A callback function needed for sqlite3_exec. Copied from sample code.
*/
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
 int i;
 for(i=0; iargc; i++){
 printf(%s = %s\n, azColName[i], argv[i] ? 
argv[i] : NULL);
 }
 printf(\n);
 return 0;
}

/*
* A function that serves as thread entry point
*/
void * thread_body(void * x)
{
 char *zErrMsg = 0;
 int rc;
 int status;
 sqlite3 *db_thread;

 printf (%d: entered into thread\n, pthread_self());

 //
 // open DB
 //
 rc = sqlite3_open(argv_global[1], db_thread);
 if( rc ){
 fprintf(stderr, Cant open database: %s\n, 
sqlite3_errmsg(db_thread));
 sqlite3_close(db_thread);
 exit(1);
 }

 //
 // double check that table1 is there. This is executed still in 
autocommit mode
 //
 rc = sqlite3_exec(db_thread, select count(*) from table1, 
callback, 0, zErrMsg);
 if( rc!=SQLITE_OK ){
 fprintf(stderr, BEGIN TRANSACTION: SQL error: 
%s\n, zErrMsg);
 sqlite3_free(zErrMsg);
 }

 while (1)
 {
 printf (\n%d: reader to sleep before start 
transactions\n,pthread_self());
 sleep(6);
 printf(\n%d: reader woke up and starts 
transaction\n,pthread_self());

 //
 // Begin

Re: [sqlite] View with Dynamic Fields ?

2008-07-08 Thread Harold Wood

my approah would be a table for applicants, then the table for answers would 
have the applicantid and the questionid as well as the answer.
 
CREATE TABLE tblApplicants (applicantid int, applicantname varchar(100));
 
now just do a select joining the tables
 
select ap.applicantname, qu.question, an.answer
from tblanswers an
inner join tblApplicants ap
on ap.applicantid = an.applicantid 
inner join tblquestions qu
on qu.questionid = an.questionid 
-- optional where clause to select just 1 applicant
where ap.applicantid = 1
-- optional order by clause to make it neat
order by applicantname asc, qu.questionid asc

 


--- On Tue, 7/8/08, Andrea Connell <[EMAIL PROTECTED]> wrote:

From: Andrea Connell <[EMAIL PROTECTED]>
Subject: [sqlite] View with Dynamic Fields ?
To: sqlite-users@sqlite.org
Date: Tuesday, July 8, 2008, 12:09 PM

I'm not sure if what I want is possible to do in SQL, but I -am- sure
that one of you will know. 
Given two tables - one with questions to ask applicants and one with an
applicant's answer to a particular question - I want to make a flattened
view with all of an applicant's answers in one row. This is easy enough
to do when I know ahead of time which questions are in the first table,
but I don't like the hard-coded approach. 
 
My schema is as follows:
 
CREATE TABLE tblquestions (questionid int, question varchar(100));
INSERT INTO "tblquestions" VALUES(1,'whats up');
INSERT INTO "tblquestions" VALUES(2,'how are you');
INSERT INTO "tblquestions" VALUES(3,'whats your name');
CREATE TABLE tblanswers (questionid int, applicantid int, answer
varchar(2500));
INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer
one');
INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer
two');
INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer
three');
INSERT INTO "tblanswers" VALUES(1,200,'random text one');
INSERT INTO "tblanswers" VALUES(2,200,'random text two');
INSERT INTO "tblanswers" VALUES(3,200,'random text three');

 
Here is the view I have come up with so far, which would require editing
whenever an insert or delete is done on tblQuestions.

CREATE VIEW allanswers as
SELECT applicantid, 
  (select answer from tblanswers Z where questionid = 1 and
Z.applicantid = A.applicantid) As Answer1,
  (select answer from tblanswers Z where questionid = 2 and
Z.applicantid = A.applicantid) As Answer2,
  (select answer from tblanswers Z where questionid = 3 and
Z.applicantid = A.applicantid) As Answer3
FROM tblanswers A
group by applicantid;

sqlite> select * from allanswers;
100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three
 
 
Has anybody come across a problem like this and found a reasonable
dynamic solution? Even something like a trigger on tblQuestions to
change the View would be great, I'm just not sure about the SQL
involved. If it helps, I don't really care what the columns end up being
named. Also I can't guarantee that the questionids will be consecutive
or in any order.
 
Thanks,
Andrea
___
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] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Tom Epperly

Shane Harrelson wrote:
> Try
> make CFLAGS="-g -O2 -fno-fast-math"
> and see if that fails like your test case 2.   I *think* this will turn on
> the -O2 optimizations and disable fast-math.
> Other than that, I don't have any other suggestions.
>   
Thanks for the suggestion.  The results are below. In my particular 
case, I don't believe the issue is -ffast-math.

Tom

$ make CFLAGS="-g -O2 -fno-fast-math" clean sqlite3 ; ./sqlite3 foo 
"select 1.0 / 3.0; "
 rm -f sqlite3 sqlite3
test -z "libsqlite3.la" || rm -f libsqlite3.la
rm -f "./so_locations"
rm -rf .libs _libs
rm -f *.o
rm -f *.lo
if gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.5.9\" -DPACKAGE_STRING=\"sqlite\ 3.5.9\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE=\"sqlite\" 
-DVERSION=\"3.5.9\" -D_FILE_OFFSET_BITS=64 -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 
-DHAVE_GMTIME_R=1 -DHAVE_READLINE=1 -I. -I.-DSQLITE_THREADSAFE=1  -g 
-O2 -fno-fast-math -MT shell.o -MD -MP -MF ".deps/shell.Tpo" -c -o 
shell.o shell.c; \
then mv -f ".deps/shell.Tpo" ".deps/shell.Po"; else rm -f 
".deps/shell.Tpo"; exit 1; fi
if /bin/sh ./libtool --tag=CC --mode=compile gcc 
-DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.5.9\" -DPACKAGE_STRING=\"sqlite\ 3.5.9\" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE=\"sqlite\" 
-DVERSION=\"3.5.9\" -D_FILE_OFFSET_BITS=64 -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 
-DHAVE_GMTIME_R=1 -DHAVE_READLINE=1 -I. -I.-DSQLITE_THREADSAFE=1  -g 
-O2 -fno-fast-math -MT sqlite3.lo -MD -MP -MF ".deps/sqlite3.Tpo" -c -o 
sqlite3.lo sqlite3.c; \
then mv -f ".deps/sqlite3.Tpo" ".deps/sqlite3.Plo"; else rm -f 
".deps/sqlite3.Tpo"; exit 1; fi
 gcc -DPACKAGE_NAME=\"sqlite\" -DPACKAGE_TARNAME=\"sqlite\" 
-DPACKAGE_VERSION=\"3.5.9\" "-DPACKAGE_STRING=\"sqlite 3.5.9\"" 
-DPACKAGE_BUGREPORT=\"http://www.sqlite.org\; -DPACKAGE=\"sqlite\" 
-DVERSION=\"3.5.9\" -D_FILE_OFFSET_BITS=64 -DSTDC_HEADERS=1 
-DHAVE_SYS_TYPES_H=1 -DHAVE_SYS_STAT_H=1 -DHAVE_STDLIB_H=1 
-DHAVE_STRING_H=1 -DHAVE_MEMORY_H=1 -DHAVE_STRINGS_H=1 
-DHAVE_INTTYPES_H=1 -DHAVE_STDINT_H=1 -DHAVE_UNISTD_H=1 -DHAVE_DLFCN_H=1 
-DHAVE_FDATASYNC=1 -DHAVE_USLEEP=1 -DHAVE_LOCALTIME_R=1 
-DHAVE_GMTIME_R=1 -DHAVE_READLINE=1 -I. -I. -DSQLITE_THREADSAFE=1 -g -O2 
-fno-fast-math -MT sqlite3.lo -MD -MP -MF .deps/sqlite3.Tpo -c sqlite3.c 
-o sqlite3.o
/bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1  -g -O2 
-fno-fast-math   -o libsqlite3.la -rpath /usr/local/lib -no-undefined 
-version-info 8:6:8 sqlite3.lo  -ldl -lpthread
mkdir .libs
ar cru .libs/libsqlite3.a  sqlite3.o
ranlib .libs/libsqlite3.a
creating libsqlite3.la
(cd .libs && rm -f libsqlite3.la && ln -s ../libsqlite3.la libsqlite3.la)
/bin/sh ./libtool --tag=CC --mode=link gcc -DSQLITE_THREADSAFE=1  -g -O2 
-fno-fast-math   -o sqlite3  shell.o ./libsqlite3.la -lreadline 
-lcurses  -ldl -lpthread
gcc -DSQLITE_THREADSAFE=1 -g -O2 -fno-fast-math -o sqlite3 shell.o  
./.libs/libsqlite3.a -lreadline -lcurses -ldl -lpthread

$ ./sqlite3 foo "select 1.0 / 3.0 ; "

$


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

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Shane Harrelson

Try
make CFLAGS="-g -O2 -fno-fast-math"
and see if that fails like your test case 2.   I *think* this will turn on
the -O2 optimizations and disable fast-math.
Other than that, I don't have any other suggestions.

HTH.
-Shane

On 7/8/08, Tom Epperly <[EMAIL PROTECTED]> wrote:
>
> Shane Harrelson wrote:
> > You can find the same issue reported for Fedora from a few weeks ago:
> >
> > http://www.sqlite.org/cvstrac/tktview?tn=3186
> >
> > Here's the original thread from the mailing list discussion:
> >
> >
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-dev/2008-June/000172.html
> >
> > -ffast_math was the culprit in this case as well.
> >
> [EMAIL PROTECTED]/tmp/sqlite-amalgamation-3.5.9]>gcc --version
> gcc (Debian 4.3.1-4) 4.3.1
> Copyright (C) 2008 Free Software Foundation, Inc.
> This is free software; see the source for copying conditions.  There is NO
> warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
>
> I ran some tests.  I configured with --disable-shared to avoid picking
> up the Debian packages shared libsqlite3.so.
>
>   1. make CFLAGS="-g -O" clean sqlite3 ; ./sqlite3 foo "select 1.0 /
>  3.0 ; " yields 0. as expected.
>   2. make CFLAGS="-g -O2" clean sqlite3 ; ./sqlite3 foo "select 1.0 /
>  3.0 ; " yields "" -- the bad situation.
>   3. The GCC 4.3 manual shows the
>
> http://gcc.gnu.org/onlinedocs/gcc-4.3.0/gcc/Optimize-Options.html#Optimize-Options
>  gives an explicit list of which optimizations -O2 turns on. I
>  wrote a simple "foreach" loop to try each additional optimization
>  by itself.  -fschedule-insns seems to cause an internal GCC error.
>  $ foreach i (`cat opt_options `)
>  foreach? echo "Optimization: $i"
>  foreach? make CFLAGS="-g -O $i" clean sqlite3 > /dev/null
>  foreach? ./sqlite3 foo "select 1.0 / 3.0 ; "
>  foreach? end
>  Optimization: -fthread-jumps
>  0.333
>  Optimization: -falign-functions
>  0.333
>  Optimization: -falign-jumps
>  0.333
>  Optimization: -falign-loops
>  0.333
>  Optimization: -falign-labels
>  0.333
>  Optimization: -fcaller-saves
>  0.333
>  Optimization: -fcrossjumping
>  0.333
>  Optimization: -fcse-follow-jumps
>  0.333
>  Optimization: -fcse-skip-blocks
>  0.333
>  Optimization: -fdelete-null-pointer-checks
>  0.333
>  Optimization: -fexpensive-optimizations
>  0.333
>  Optimization: -fgcse
>  0.333
>  Optimization: -fgcse-lm
>  0.333
>  Optimization: -foptimize-sibling-calls
>  0.333
>  Optimization: -fpeephole2
>  0.333
>  Optimization: -fregmove
>  0.333
>  Optimization: -freorder-blocks
>  0.333
>  Optimization: -freorder-functions
>  0.333
>  Optimization: -frerun-cse-after-loop
>  0.333
>  Optimization: -fsched-interblock
>  0.333
>  Optimization: -fsched-spec
>  0.333
>  Optimization: -fschedule-insns
>  shell.c: In function 'appendText':
>  shell.c:755: error: unable to find a register to spill in class 'AREG'
>  shell.c:755: error: this is the insn:
>  (insn 12 150 2 2 shell.c:723 (parallel [
>  (set (reg:SI 1 dx [78])
>  (unspec:SI [
>  (mem:BLK (reg/f:SI 5 di [orig:80 zAppend ]
>  [80]) [0 A8])(reg:QI 3 bx [82])
>  (const_int 1 [0x1])
>  (reg:SI 1 dx [81])
>  ] 30))
>  (clobber (reg/f:SI 5 di [orig:80 zAppend ] [80]))
>  (clobber (reg:CC 17 flags))
>  ]) 810 {*strlenqi_1} (expr_list:REG_DEAD (reg:QI 3 bx [82])
>  (expr_list:REG_DEAD (reg:SI 1 dx [81])
>  (expr_list:REG_DEAD (reg/f:SI 5 di [orig:80 zAppend ]
>  [80])
>  (expr_list:REG_UNUSED (reg/f:SI 5 di [orig:80
>  zAppend ] [80])
>  (expr_list:REG_UNUSED (reg:CC 17 flags)
>  (nil)))
>  shell.c:755: confused by earlier errors, bailing out
>  Preprocessed source stored into /tmp/ccl8qsNd.out file, please
>  attach this to your bugreport.
>  make: *** [shell.o] Error 1
>  ./sqlite3: Command not found.
>  Optimization: -fschedule-insns2
>  0.333
>  Optimization: -fstrict-aliasing
>  0.333
>  Optimization: -fstrict-overflow
>  0.333
>  Optimization: -ftree-pre
>  0.333
>  Optimization: -ftree-vrp
>  0.333
>  $
>   4. I tried -ffast-math too with make CFLAGS="-g -O -ffast-math" clean
>  sqlite3 > /dev/null ; ./sqlite3 foo

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Tom Epperly

Shane Harrelson wrote:
> You can find the same issue reported for Fedora from a few weeks ago:
>
> http://www.sqlite.org/cvstrac/tktview?tn=3186
>
> Here's the original thread from the mailing list discussion:
>
> http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-dev/2008-June/000172.html
>
> -ffast_math was the culprit in this case as well.
>   
[EMAIL PROTECTED]/tmp/sqlite-amalgamation-3.5.9]>gcc --version
gcc (Debian 4.3.1-4) 4.3.1
Copyright (C) 2008 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

I ran some tests.  I configured with --disable-shared to avoid picking 
up the Debian packages shared libsqlite3.so.

   1. make CFLAGS="-g -O" clean sqlite3 ; ./sqlite3 foo "select 1.0 /
  3.0 ; " yields 0. as expected.
   2. make CFLAGS="-g -O2" clean sqlite3 ; ./sqlite3 foo "select 1.0 /
  3.0 ; " yields "" -- the bad situation.
   3. The GCC 4.3 manual shows the
  
http://gcc.gnu.org/onlinedocs/gcc-4.3.0/gcc/Optimize-Options.html#Optimize-Options
  gives an explicit list of which optimizations -O2 turns on. I
  wrote a simple "foreach" loop to try each additional optimization
  by itself.  -fschedule-insns seems to cause an internal GCC error.
  $ foreach i (`cat opt_options `)
  foreach? echo "Optimization: $i"
  foreach? make CFLAGS="-g -O $i" clean sqlite3 > /dev/null
  foreach? ./sqlite3 foo "select 1.0 / 3.0 ; "
  foreach? end
  Optimization: -fthread-jumps
  0.333
  Optimization: -falign-functions
  0.333
  Optimization: -falign-jumps
  0.333
  Optimization: -falign-loops
  0.333
  Optimization: -falign-labels
  0.333
  Optimization: -fcaller-saves
  0.333
  Optimization: -fcrossjumping
  0.333
  Optimization: -fcse-follow-jumps
  0.333
  Optimization: -fcse-skip-blocks
  0.333
  Optimization: -fdelete-null-pointer-checks
  0.333
  Optimization: -fexpensive-optimizations
  0.333
  Optimization: -fgcse
  0.333
  Optimization: -fgcse-lm
  0.333
  Optimization: -foptimize-sibling-calls
  0.333
  Optimization: -fpeephole2
  0.333
  Optimization: -fregmove
  0.333
  Optimization: -freorder-blocks
  0.333
  Optimization: -freorder-functions
  0.333
  Optimization: -frerun-cse-after-loop
  0.333
  Optimization: -fsched-interblock
  0.333
  Optimization: -fsched-spec
  0.333
  Optimization: -fschedule-insns
  shell.c: In function 'appendText':
  shell.c:755: error: unable to find a register to spill in class 'AREG'
  shell.c:755: error: this is the insn:
  (insn 12 150 2 2 shell.c:723 (parallel [
  (set (reg:SI 1 dx [78])
  (unspec:SI [
  (mem:BLK (reg/f:SI 5 di [orig:80 zAppend ]
  [80]) [0 A8])(reg:QI 3 bx [82])
  (const_int 1 [0x1])
  (reg:SI 1 dx [81])
  ] 30))
  (clobber (reg/f:SI 5 di [orig:80 zAppend ] [80]))
  (clobber (reg:CC 17 flags))
  ]) 810 {*strlenqi_1} (expr_list:REG_DEAD (reg:QI 3 bx [82])
  (expr_list:REG_DEAD (reg:SI 1 dx [81])
  (expr_list:REG_DEAD (reg/f:SI 5 di [orig:80 zAppend ]
  [80])
  (expr_list:REG_UNUSED (reg/f:SI 5 di [orig:80
  zAppend ] [80])
  (expr_list:REG_UNUSED (reg:CC 17 flags)
  (nil)))
  shell.c:755: confused by earlier errors, bailing out
  Preprocessed source stored into /tmp/ccl8qsNd.out file, please
  attach this to your bugreport.
  make: *** [shell.o] Error 1
  ./sqlite3: Command not found.
  Optimization: -fschedule-insns2
  0.333
  Optimization: -fstrict-aliasing
  0.333
  Optimization: -fstrict-overflow
  0.333
  Optimization: -ftree-pre
  0.333
  Optimization: -ftree-vrp
  0.333
  $
   4. I tried -ffast-math too with make CFLAGS="-g -O -ffast-math" clean
  sqlite3 > /dev/null ; ./sqlite3 foo "select 1.0 / 3.0; "
  0.333
  it yielded the correct answer
   5. According to the GCC 4.3 documentation, the following should be
  equivalent to "-g -O2" but it also dies with an internal GCC error.
  $ make CFLAGS="-g -O -fthread-jumps -falign-functions
  -falign-jumps -falign-loops -falign-labels -fcaller-saves
  -fcrossjumping -fcse-follow-jumps -fcse-skip-blocks

[sqlite] View with Dynamic Fields ?

2008-07-08 Thread Andrea Connell

I'm not sure if what I want is possible to do in SQL, but I -am- sure
that one of you will know. 
Given two tables - one with questions to ask applicants and one with an
applicant's answer to a particular question - I want to make a flattened
view with all of an applicant's answers in one row. This is easy enough
to do when I know ahead of time which questions are in the first table,
but I don't like the hard-coded approach. 
 
My schema is as follows:
 
CREATE TABLE tblquestions (questionid int, question varchar(100));
INSERT INTO "tblquestions" VALUES(1,'whats up');
INSERT INTO "tblquestions" VALUES(2,'how are you');
INSERT INTO "tblquestions" VALUES(3,'whats your name');
CREATE TABLE tblanswers (questionid int, applicantid int, answer
varchar(2500));
INSERT INTO "tblanswers" VALUES(1,100,'stuff for answer one');
INSERT INTO "tblanswers" VALUES(2,100,'stuff for answer two');
INSERT INTO "tblanswers" VALUES(3,100,'stuff for answer three');
INSERT INTO "tblanswers" VALUES(1,200,'random text one');
INSERT INTO "tblanswers" VALUES(2,200,'random text two');
INSERT INTO "tblanswers" VALUES(3,200,'random text three');

 
Here is the view I have come up with so far, which would require editing
whenever an insert or delete is done on tblQuestions.

CREATE VIEW allanswers as
SELECT applicantid, 
  (select answer from tblanswers Z where questionid = 1 and
Z.applicantid = A.applicantid) As Answer1,
  (select answer from tblanswers Z where questionid = 2 and
Z.applicantid = A.applicantid) As Answer2,
  (select answer from tblanswers Z where questionid = 3 and
Z.applicantid = A.applicantid) As Answer3
FROM tblanswers A
group by applicantid;

sqlite> select * from allanswers;
100|stuff for answer one|stuff for answer two|stuff for answer three
200|random text one|random text two|random text three
 
 
Has anybody come across a problem like this and found a reasonable
dynamic solution? Even something like a trigger on tblQuestions to
change the View would be great, I'm just not sure about the SQL
involved. If it helps, I don't really care what the columns end up being
named. Also I can't guarantee that the questionids will be consecutive
or in any order.
 
Thanks,
Andrea
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

2008-07-08 Thread Bob Dennis


I can also get it to work with a small application on my machine, against the
same database/table.
Also it works sometimes in the main application.
I thought I had it working last night but it was just a fluke compile
success I think.
I suspect it might be the size of the application that is causing a problem.
I have sent it all to George at NSBasic, he asked to look at it. It is too
big to paste. (175K plus db)


Harold Wood  Meyuni Gani wrote:
> 
> similar statements work fine on my pda, ipaq 210 with min mobile6.  can
> you paste your code?
> 
> --- On Tue, 7/8/08, Bob Dennis <[EMAIL PROTECTED]> wrote:
> 
> From: Bob Dennis <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] Bad UPDATE Problems in Mobile6 device
> To: sqlite-users@sqlite.org
> Date: Tuesday, July 8, 2008, 4:50 AM
> 
> Bob Dennis wrote:
>> 
>> I have tried with and without the single quotes(Saw them in an example
>> somewhere),
>> makes no difference(Why do I not get an error if it is wrong?)
>> Yes I want to set all flags in this VERY SIMPLE test , just to get
>> something to happen.
>> 
>> Can things be left in a locked state somehow? 
>> 
>> Bob
>> 
>>  
>> 
>> 
>> 
>>  
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>> 
> 
> -- 
> View this message in context:
> http://www.nabble.com/Bad-UPDATE-Problems-in-Mobile6-device-tp18314650p18334687.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-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Bad-UPDATE-Problems-in-Mobile6-device-tp18314650p18342031.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] Setting empty blob data (not NULL)

2008-07-08 Thread D. Richard Hipp


On Jul 8, 2008, at 11:21 AM, John Petrangelo wrote:

> I am using the C/C++ API for sqlite. I am using sqlite3_bind_blob() to
> insert data into the DB. The call typically looks like the following:
>
>
>
>rv = sqlite3_bind_blob(pStmt, 3, pBuffer, bufLen,  
> SQLITE_TRANSIENT);
>
>
>
> For my purposes, I prefer that these empty blobs be stored in the DB  
> as
> "" (i.e. zero length data) rather than NULL. However, if pBuffer NULL
> and bufLen is 0, then the bind assigns the NULL value. I have found  
> that
> if pBuffer is non-NULL and bufLen is zero that the DB will store the
> empty value we want. As a result, I've resorted to coding as follows:
>
>
>
>if (pBuffer != NULL) {
>
>rv = sqlite3_bind_blob(pStmt, 3, pBuffer, bufLen,
> SQLITE_TRANSIENT);
>
>} else {
>
>rv = sqlite3_bind_blob(pStmt, 3, (void*)1, 0,  
> SQLITE_TRANSIENT);
>
>}
>


Perhaps the statement would read better as follows:

  rv = sqlite3_bind_blob(pStmt, 3, "", 0, SQLITE_TRANSIENT);

D. Richard Hipp
[EMAIL PROTECTED]



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

[sqlite] Setting empty blob data (not NULL)

2008-07-08 Thread John Petrangelo

I am using the C/C++ API for sqlite. I am using sqlite3_bind_blob() to
insert data into the DB. The call typically looks like the following:

 

rv = sqlite3_bind_blob(pStmt, 3, pBuffer, bufLen, SQLITE_TRANSIENT);

 

For my purposes, I prefer that these empty blobs be stored in the DB as
"" (i.e. zero length data) rather than NULL. However, if pBuffer NULL
and bufLen is 0, then the bind assigns the NULL value. I have found that
if pBuffer is non-NULL and bufLen is zero that the DB will store the
empty value we want. As a result, I've resorted to coding as follows:

 

if (pBuffer != NULL) {

rv = sqlite3_bind_blob(pStmt, 3, pBuffer, bufLen,
SQLITE_TRANSIENT);

} else {

rv = sqlite3_bind_blob(pStmt, 3, (void*)1, 0, SQLITE_TRANSIENT);

}

 

I'm left feeling a little dirty after making that cast. Is there a more
appropriate idiom others are using for this purpose? Is there any
guarantee provided by the API that if the length is zero that it will
not dereference the pointer? I decided to use (void*)1 instead of a
valid pointer to inappropriate data (a pointer to another stack
variable, for instance) specifically so that the program would choke and
die quickly if the API tried to use the pointer.

 

Any suggestions will be appreciated.

 

Thanks,

-John

 

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

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

2008-07-08 Thread Harold Wood

similar statements work fine on my pda, ipaq 210 with min mobile6.  can you 
paste your code?

--- On Tue, 7/8/08, Bob Dennis <[EMAIL PROTECTED]> wrote:

From: Bob Dennis <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Bad UPDATE Problems in Mobile6 device
To: sqlite-users@sqlite.org
Date: Tuesday, July 8, 2008, 4:50 AM

Bob Dennis wrote:
> 
> I have tried with and without the single quotes(Saw them in an example
> somewhere),
> makes no difference(Why do I not get an error if it is wrong?)
> Yes I want to set all flags in this VERY SIMPLE test , just to get
> something to happen.
> 
> Can things be left in a locked state somehow? 
> 
> Bob
> 
>  
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context:
http://www.nabble.com/Bad-UPDATE-Problems-in-Mobile6-device-tp18314650p18334687.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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can i use sqlite's fts3 to support full text search of gb2312(chinese)?

2008-07-08 Thread Dennis Cote

wenhm wrote:
> Anyone can give me a hint, thanks so much.

Hint: Read http://www.sqlite.org/cvstrac/wiki?p=FullTextIndex and search 
the archives.

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

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread Shane Harrelson

You can find the same issue reported for Fedora from a few weeks ago:

http://www.sqlite.org/cvstrac/tktview?tn=3186

Here's the original thread from the mailing list discussion:

http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-dev/2008-June/000172.html

-ffast_math was the culprit in this case as well.

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

Re: [sqlite] sqlite3 3.5.9 floating point division seems odd

2008-07-08 Thread D. Richard Hipp

On Jul 8, 2008, at 1:13 AM, Cory Nelson wrote:

> On Mon, Jul 7, 2008 at 9:06 PM, Shane Harrelson <[EMAIL PROTECTED]>  
> wrote:
>> Make sure SQLite isn't being compiled with -ffast_math on the the  
>> Debian
>> side.  That might cause problems.
>
> -ffast-math would not cause sqlite to bug out like described.
>
> it lets the compiler reorder floating point expressions in a way that
> might trivially change the output while being faster.  so (a * b + a *
> c) would be transformed into (a*(b+c)), which could give different
> results due to rounding.

 From the GCC manpage:

This option should never be turned on by any -O option  
since it can
result in incorrect output for programs which depend on an  
exact
implementation of IEEE or ISO rules/specifications for math
functions.

In SQLite, we have observed that NaN detection does not work with - 
ffast-math.  This problem cascades whenever NULL values and floating  
point values are intermixed.

D. Richard Hipp
[EMAIL PROTECTED]

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

Re: [sqlite] Bad UPDATE Problems in Mobile6 device

2008-07-08 Thread Bob Dennis



Bob Dennis wrote:
> 
> I have tried with and without the single quotes(Saw them in an example
> somewhere),
> makes no difference(Why do I not get an error if it is wrong?)
> Yes I want to set all flags in this VERY SIMPLE test , just to get
> something to happen.
> 
> Can things be left in a locked state somehow? 
> 
> Bob
> 
>  
> 
> 
> 
>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Bad-UPDATE-Problems-in-Mobile6-device-tp18314650p18334687.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] regarding internal design of SQLite

2008-07-08 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Aditya Bhave (adbhave) wrote:
> I am interested in learning about the internals of SQLite.

The SQLite documentation page at http://sqlite.org/docs.html contains
almost all the information you could possibly want.

> How it parses
> SQL strings, what goes on the parsing stack,

A parser generator named Lemon is used which is documented at
http://www.hwaci.com/sw/lemon/

The file containing the SQLite grammar and what actions are performed is
src/parse.y

> how it executes SQL statements etc.

A virtual machine is used since it provides an easy way to encode state
(eg between calls to sqlite3_step).  The broad principles are documented
at http://sqlite.org/vdbe.html and the machine opcodes are documented at
http://sqlite.org/opcode.html

This paper contrasts stack based vs register based virtual machines.
SQLite used to be stack based and moved to register based a few
revisions ago.  (Note that the virtual machine is not exposed to users
of SQLite so the backend is irrelevant to the user, as long as it works)

  http://www.usenix.org/events/vee05/full_papers/p153-yunhe.pdf

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIcwm1mOOfHg372QQRAuVbAJ9cli9pD9enCHZr8yDDWgJ9ghsvfACgyjXe
zIcyPCpKe1zu5TPKOFefbvY=
=+mJS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

37 matches

Mail list logo