[sqlite] Re: Combining queries

[Philippe BESSAGUET]

Sebastian Tennant <[EMAIL PROTECTED]>
wrote: 
> I have three tables; users, propositions, and subscriptions:
> 
>  users
>  -
>  user_id  user_name  user_password  user_email
> 
>  propostions
>  ---
>  prop_id proposition user_id
> 
>  subscriptions
>  -
>  sub_id  prop_id  user_id
> 
> Given a particular user, I want to return the set of 'interesting'
> propositions, i.e., those which were not proposed by the user and to
> which the user is not already subscribed...

select * from propositions p
where p.user_id != :userId and not exists (
select * from subscriptions s
where p.prop_id = s.prop_id and s.user_id = :userId
);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] version 3.3.15, is SQLITE_BUSY_RESERVED_LOCK implemented

[drh]

"Andrew Finkenstadt" <[EMAIL PROTECTED]> wrote:
> I see a reference to SQLITE_BUSY_RESERVED_LOCK in version 3.3.15 (the first
> one with the amalgamation), but there does not appear to be support for it
> in the remainder of the source file.
> 
> Is that correct?

So it appears
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] version 3.3.15, is SQLITE_BUSY_RESERVED_LOCK implemented

[Andrew Finkenstadt]

I see a reference to SQLITE_BUSY_RESERVED_LOCK in version 3.3.15 (the first
one with the amalgamation), but there does not appear to be support for it
in the remainder of the source file.

Is that correct?

Re: [sqlite] MMap On Solaris

[Mitchell Vincent]

Hi John! Thanks for the reply!

I think that makes a good point that the vm page fault is probably
faster than the overhead of copying the data to a local buffer.  So, page
fault or not, I think that's the way I'm going to do it.

Again, thanks very much for your input!

On 6/12/07, John Stanton <[EMAIL PROTECTED]> wrote:

Mitchell Vincent wrote:
> Working with some data conversion here (that will eventually go into
> an SQLite database). I'm hoping you IO wizards can offer some help on
> a question that I've been trying to get answered.
>
> I'm using Solaris 10 for this.
>
> If I mmap a large file and use madvise with MADV_SEQUENTIAL and
> MADV_WILLNEED, then start processing the file, when will the system
> discard pages that have been referenced? I guess what I'm wondering is
> if there is any retention of "back" pages?
>
> Say for example I start reading the file, and after consuming 24,576
> bytes, will the first or second pages still be in memory (assuming
> 8192 byte pages)?
>
> Thanks!
>
In general it means that the file is mapped into virtual memory.  How
much of it remains in actual memory depends upon the memory demands on
the OS at the time.  If the sequential and random advice is used by the
OS it is most likely to implement a look ahead for requential access.
Not all OS's pay attention to those advisory settings.

What you are doing is to access the file as if it were an executing
program image.  Similar rules apply.

The answer is that you cannot assume that pages you have read are in
actual memory and you cannot assume that they are not.  When you access
a page not currently in memory the OS will read it in and find space for
it somehow, maybe by discarding some other page.

This is an excellent way to read files because you avoid one level of
buffer shadowing and get cacheing adjusted to currently available memory.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What happens when a table with an active statement is changed?

[Rob Richardson]

It seems this was discussed just a few days ago.  A recent upgrade to
SQLite allows inserts, updates and deletes on tables that are also open
for selection.  The changes may or may not appear as I call
sqlite3_step(), but I can live with that.

RobR

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re: [sqlite] sqlite3_open() fails on WinCE due to utf8ToUnicode / unicode

[Nuno Lucas]

On 6/7/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

It really looks like this UTF-8 codepage is not avaiable. Is there
any WinCE developer that uses SQLite newer than version 3.3.9 on this
list? -> Did you have similiar problems since the unicode conversion
functions were changed?


I haven't used sqlite in WinCE for some time, and haven't answered
before because I was waiting to test this myself (which I haven't done
yet).

I believe this was put because UTF-8 is supported by default in WinCE
5.0 (at least that was my impression), and this is the right fix, but
maybe the code should have a fallback to the earlier behaviour if
CP_UTF8 is not supported on the device (as it happens with most WinCE
4.x and older devices).

Did you open a ticket for this?


Regards,
~Nuno Lucas

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_create_function function name when called?

[Nuno Lucas]

On 6/12/07, Omar Eljumaily <[EMAIL PROTECTED]> wrote:

When you create a function with sqlite3_create_function, the callback
function is something like:

myFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{

}

Is it possible to get the name of the function that the callback was
called because of?  I want to write just one callback that is used for
multiple functions.  The reason I want to do this is to be able to add
functions at runtime.


I don't think you can obtain the name from your function, but maybe
you can do what you want with the sqlite3_set_authorizer() method
(maybe in conjunction with the experimental sqlite3_overload_function
method to assure an empty function exists).

Regards,
~Nuno Lucas



Thanks.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] sqlite3_create_function function name when called?

[Joe Wilson]

--- Omar Eljumaily <[EMAIL PROTECTED]> wrote:
> Is it possible to get the name of the function that the callback was 
> called because of? 

If you specified a userData argument in sqlite3_create_function()
you can retrieve it in the functon itself with

  void *sqlite3_user_data(sqlite3_context*);

But you'd still have to call sqlite3_create_function() to register 
every new function anyway. Otherwise SQLite's parser wouldn't know 
about these functions' names, whether they are an aggregate function 
or not, or how many parameters they take.

I am not aware of a way to remove a previously created function using
the public API.


   

Sick sense of humor? Visit Yahoo! TV's 
Comedy with an Edge to see what's on, when. 
http://tv.yahoo.com/collections/222

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_create_function function name when called?

[Omar Eljumaily]

When you create a function with sqlite3_create_function, the callback 
function is something like:


myFunc(sqlite3_context *context, int argc, sqlite3_value **argv)
{

}

Is it possible to get the name of the function that the callback was 
called because of?  I want to write just one callback that is used for 
multiple functions.  The reason I want to do this is to be able to add 
functions at runtime.


Thanks.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] MMap On Solaris

[John Stanton]

Mitchell Vincent wrote:

Working with some data conversion here (that will eventually go into
an SQLite database). I'm hoping you IO wizards can offer some help on
a question that I've been trying to get answered.

I'm using Solaris 10 for this.

If I mmap a large file and use madvise with MADV_SEQUENTIAL and
MADV_WILLNEED, then start processing the file, when will the system
discard pages that have been referenced? I guess what I'm wondering is
if there is any retention of "back" pages?

Say for example I start reading the file, and after consuming 24,576
bytes, will the first or second pages still be in memory (assuming
8192 byte pages)?

Thanks!

In general it means that the file is mapped into virtual memory.  How 
much of it remains in actual memory depends upon the memory demands on 
the OS at the time.  If the sequential and random advice is used by the 
OS it is most likely to implement a look ahead for requential access. 
Not all OS's pay attention to those advisory settings.


What you are doing is to access the file as if it were an executing 
program image.  Similar rules apply.


The answer is that you cannot assume that pages you have read are in 
actual memory and you cannot assume that they are not.  When you access 
a page not currently in memory the OS will read it in and find space for 
it somehow, maybe by discarding some other page.


This is an excellent way to read files because you avoid one level of 
buffer shadowing and get cacheing adjusted to currently available memory.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What happens when a table with an active statement is changed?

[Rob Richardson]

Greetings!

In the ADO world, I can have a recordset object that holds data from a
table.  I can call AddNew() to add a record to the table, Update() to
change data in the current record, or Delete() to delete the current
record.  But in the SQLite world, insertions, updates and deletions can
only be done by preparing the appropriate SQL statement and calling
sqlite3_exec() on the database (or sqlite3_prepare()/step()/finalize()).


So, what happens if I need to change a record as I am walking through a
result set?  If I have one sqlite statement that is pointing to a record
and a different sqlite statement deletes that record, will
sqlite3_step() on the first statement have a problem?  What if the
second sqlite statement deletes the record that the next sqlite3_step()
on the first statement would have retrieved on its next call?  What if
the second sqlite statement adds a record?  Will the repeated
sqlite3_step() calls on the first statement eventually get the new
record?  Or do I just need to be careful that I never have two active
sqlite statements referencing the same table?

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] MMap On Solaris

[Mitchell Vincent]

Working with some data conversion here (that will eventually go into
an SQLite database). I'm hoping you IO wizards can offer some help on
a question that I've been trying to get answered.

I'm using Solaris 10 for this.

If I mmap a large file and use madvise with MADV_SEQUENTIAL and
MADV_WILLNEED, then start processing the file, when will the system
discard pages that have been referenced? I guess what I'm wondering is
if there is any retention of "back" pages?

Say for example I start reading the file, and after consuming 24,576
bytes, will the first or second pages still be in memory (assuming
8192 byte pages)?

Thanks!

--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Combining queries

[Sebastian Tennant]

Quoth "Igor Tandetnik" <[EMAIL PROTECTED]>:
> Sebastian Tennant <[EMAIL PROTECTED]>
> wrote: 
>> I have three tables; users, propositions, and subscriptions:
>>
>>  users
>>  -
>>  user_id  user_name  user_password  user_email
>>
>>  propostions
>>  ---
>>  prop_id proposition user_id
>>
>>  subscriptions
>>  -
>>  sub_id  prop_id  user_id
>>
>> Given a particular user, I want to return the set of 'interesting'
>> propositions, i.e., those which were not proposed by the user and to
>> which the user is not already subscribed...
>
> select * from propositions p
> where p.user_id != :userId and not exists (
>select * from subscriptions s
>where p.prop_id = s.prop_id and s.user_id = :userId
> );
>
> Igor Tandetnik

Thank you _very_ much Igor.  I wasn't even close!  Now I need to sit
down and understand exactly why it works.

Sebastian

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database replication question

[Joe Wilson]

> If the performance problem is with the seconday index, is there a way to 
> "pause" indexing before
> a large bulk insert and then "resume" it later without rebuilding the entire 
> index (to avoid
> doing: drop index + inserts + create index)?

No


 

The fish are biting. 
Get more visitors on your site using Yahoo! Search Marketing.
http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[Joe Wilson]

> I tried to set the cache size to 0 (after sqlite3_open0, and then query
> for pragma cache_size which returns 2000 (default cache size). Why its
> not returning 10 (according to Weiyang Wang)?

It does report 0, even though internally it is using a value of 10.

 SQLite version 3.3.17
 Enter ".help" for instructions
 sqlite> pragma cache_size;
 2000
 sqlite> pragma cache_size=0;
 sqlite> pragma cache_size;
 0

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


   

Moody friends. Drama queens. Your life? Nope! - their life, your story. Play 
Sims Stories at Yahoo! Games.
http://sims.yahoo.com/  

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] index with NULL values

[Adler, Eliedaat]

Hi,
 
I want to add an index on a sparsely populated column -
 i.e. for most rows the column is null.
 
1. Would this index be very compact? i.e. only include "set" rows.
2. Could this index also help select NULL entries?
 
Thanks,
Eliedaat Adler
*
This e-mail is confidential, the property of NDS Ltd and intended for the 
addressee only.  Any dissemination, copying or distribution of this message or 
any attachments by anyone other than the intended recipient is strictly 
prohibited.  If you have received this message in error, please immediately 
notify the [EMAIL PROTECTED] and destroy the original message.  Messages sent 
to and from NDS may be monitored.  NDS cannot guarantee any message delivery 
method is secure or error-free.  Information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses.  We do not 
accept responsibility for any errors or omissions in this message and/or 
attachment that arise as a result of transmission.  You should carry out your 
own virus checks before opening any attachment.  Any views or opinions 
presented are solely those of the author and do not necessarily represent those 
of NDS.

To protect the environment please do not print this e-mail unless necessary.
**

Re: [sqlite] Database replication question

[drh]

[EMAIL PROTECTED] wrote:
> > - Original Message 
> > From: Joe Wilson <[EMAIL PROTECTED]>
> > To: sqlite-users@sqlite.org
> > Sent: Monday, June 11, 2007 8:36:32 PM
> > Subject: Re: [sqlite] Database replication question
> > 
> > 
> > Large bulk inserts with more than one index (implicit or explicit) 
> > is not SQLite's strong suit.
> > 
> > If you search the mailing list archives you'll find a few suggestions:
> > 
> > - "BEGIN EXCLUSIVE" (or is it "BEGIN IMMEDIATE"?) on the 
> >   database file and then copy the file over - fastest way
> 
> What do you mean by "copy the file over"? A straight copy of the
> binary content of the file? If so, I can't really do that because
> the version of sqlite are potentially different on the two machines.

The binary file format for SQLite version 3 is (usually) the same
for all versions going back to version 3.0.0.  The exception is if
you use some of the newer features introduced in later versions,
then earlier versions of the library might not be able to read
the file.  But your schema does not appear to be using any advanced
features, so I think you will always be OK.

One of the key features of SQLite is that we work really hard
to preserve backwards compatibility of both API and file format.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database replication question

[John Stanton]

[EMAIL PROTECTED] wrote:

- Original Message 
From: Joe Wilson <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, June 11, 2007 8:36:32 PM
Subject: Re: [sqlite] Database replication question


Large bulk inserts with more than one index (implicit or explicit) 
is not SQLite's strong suit.


If you search the mailing list archives you'll find a few suggestions:

- "BEGIN EXCLUSIVE" (or is it "BEGIN IMMEDIATE"?) on the 
 database file and then copy the file over - fastest way



What do you mean by "copy the file over"? A straight copy of the binary content 
of the file? If so, I can't really do that because the version of sqlite are potentially 
different on the two machines.



or

- increasing cache sizes 
- pre-sorting the data in index order prior to bulk insert

- creating the other indexes after all the data is inserted

If you do not require a live backup you could use the copy trick
and augment that with a daily archive via 


sqlite3 file.db .dump | gzip etc...

in case the database file becomes corrupted.



If the performance problem is with the seconday index, is there a way to "pause" indexing before a 
large bulk insert and then "resume" it later without rebuilding the entire index (to avoid doing: 
drop index + inserts + create index)? Maybe it's a stupid question, but I am guessing that there is some sort 
of version number for the rows in the db, so playing "catchup" on an index could work?

Nicolas
If you have incompatible Sqlite versions you can still perform a 
snapshot replication by doing a file copy then running a background job 
to dump the old version database and rebuild it to the latest version.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Combining queries

[Igor Tandetnik]

Sebastian Tennant <[EMAIL PROTECTED]>
wrote: 

I have three tables; users, propositions, and subscriptions:

 users
 -
 user_id  user_name  user_password  user_email

 propostions
 ---
 prop_id proposition user_id

 subscriptions
 -
 sub_id  prop_id  user_id

Given a particular user, I want to return the set of 'interesting'
propositions, i.e., those which were not proposed by the user and to
which the user is not already subscribed...


select * from propositions p
where p.user_id != :userId and not exists (
   select * from subscriptions s
   where p.prop_id = s.prop_id and s.user_id = :userId
);

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

I tried to set the cache size to 0 (after sqlite3_open0, and then query
for pragma cache_size which returns 2000 (default cache size). Why its
not returning 10 (according to Weiyang Wang)?


Regards,
Phani



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP

[drh]

[EMAIL PROTECTED] wrote:
> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> > I've opened a new ticket 2409.
> > 
> > http://www.sqlite.org/cvstrac/tktview?tn=2409,38
> > 
> 
> I am so far unable to reproduce the problem.  Please send
> me an example corrupt database and the binaries for
> SQLiteCrush.exe.  Tnx.
> 

Never mind.  We are able to reproduce the problem now
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] PRAGMA cache_size = 0

[B V, Phanisekhar]

What exactly happens when I change the cache_size(both increase and
decrease size)? What happens to the data that's there in the result
cache at the time when the instruction PRAGMA cache_size = 0 is
executed? Will there be any memory that will be freed up when I reduce
the size of result cache?

Regards,
Phani




-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, June 12, 2007 3:40 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] PRAGMA cache_size = 0

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database malformed with SQLite3.3.17 on WindowsXP

[drh]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> I've opened a new ticket 2409.
> 
> http://www.sqlite.org/cvstrac/tktview?tn=2409,38
> 

I am so far unable to reproduce the problem.  Please send
me an example corrupt database and the binaries for
SQLiteCrush.exe.  Tnx.

You can send them to me by direct email if you want.
--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] PRAGMA cache_size = 0

[drh]

"B V, Phanisekhar" <[EMAIL PROTECTED]> wrote:
> I am yet to get answers for the following questions.
> 

Weiyang Wang correctly answered your question at

   http://www.mail-archive.com/sqlite-users%40sqlite.org/msg25290.html

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Database replication question

[drh]

[EMAIL PROTECTED] wrote:
> 
> The table I have is something like
> CREATE TABLE sn2uid(
>sn VARCHAR(100) NOT NULL,
>uid INTEGER NOT NULL,
>PRIMARY KEY (sn)
> );
> CREATE INDEX uidindex on sn2uid ( uid )
> 
> 
> is there a way to do a select or a .dump so that when inserting the 
> data on the other end, things will be faster? 

Do BEGIN EXCLUSIVE, then copy the disk file, then do COMMIT.

--
D. Richard Hipp <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] [bug]DSQLITE_OMIT_DISKIO link failure

[weiyang wang]

Hi,

With checking the source code build, i could see DSQLITE_OMIT_DISKIO does
not work properly.

the problem is that symbols, which are related to DSQLITE_OMIT_DISKIO in
btree.c and attach.c, are not properly tied to DSQLITE_OMIT_DISKIO. so
caused linker errors.

best regards,
wang

Re: [sqlite] Re: sqlite_omit_xx build failure

[weiyang wang]

thanks.

that is correct. i have just found this issue 5 mins ago. i am re-doing the
compiling now.

and .

it works.

thanks. have a nice day

wang



On 6/12/07, Dan Kennedy <[EMAIL PROTECTED]> wrote:


On Tue, 2007-06-12 at 09:56 +0200, weiyang wang wrote:
> Hi Dr.H,
> Yes, I did a clean build in a clean directory( (top)/bld ). and i had
run
> 'make clean' before i run the 'make'.
>
> i am wondering whether the lemon tool configuration in my environment is
> well done or not.
>
> thanks in advance.
>
> wang
>
> On 6/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > "weiyang wang" <[EMAIL PROTECTED]> wrote:
> > > hi,
> > >
> > > i am trying to get a smaller sqlite lib by adding sqlite_omit_xx
> > defines.
> > > but it failed at the compiler time.
> > >
> > > steps:
> > > 1, in 'Makefile.in ', omit macro is added:
> > >
> > >   TCC += -DSQLITE_OMIT_ALTERTABLE=1

Do this as well:

  OPTS += -DSQLITE_OMIT_ALTERTABLE=1

The key is that the same set of -DSQLITE_OMIT_ options have
to be passed to "lemon" as the C compiler.

Dan.

> > >
> > > 2, run '(top)/configure' and 'make' in cygwin,
> > >
> > > 3, i got the fowllowing error:
> > >
> >
> > Did you do this from a clean directory?  Did you
> > do "make clean" before doing "make"?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> >
-
> >
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> >
-
> >
> >



-
To unsubscribe, send email to [EMAIL PROTECTED]

-

Re: [sqlite] Re: sqlite_omit_xx build failure

[Dan Kennedy]

On Tue, 2007-06-12 at 09:56 +0200, weiyang wang wrote:
> Hi Dr.H,
> Yes, I did a clean build in a clean directory( (top)/bld ). and i had run
> 'make clean' before i run the 'make'.
> 
> i am wondering whether the lemon tool configuration in my environment is
> well done or not.
> 
> thanks in advance.
> 
> wang
> 
> On 6/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> > "weiyang wang" <[EMAIL PROTECTED]> wrote:
> > > hi,
> > >
> > > i am trying to get a smaller sqlite lib by adding sqlite_omit_xx
> > defines.
> > > but it failed at the compiler time.
> > >
> > > steps:
> > > 1, in 'Makefile.in ', omit macro is added:
> > >
> > >   TCC += -DSQLITE_OMIT_ALTERTABLE=1

Do this as well:

   OPTS += -DSQLITE_OMIT_ALTERTABLE=1

The key is that the same set of -DSQLITE_OMIT_ options have
to be passed to "lemon" as the C compiler.

Dan.

> > >
> > > 2, run '(top)/configure' and 'make' in cygwin,
> > >
> > > 3, i got the fowllowing error:
> > >
> >
> > Did you do this from a clean directory?  Did you
> > do "make clean" before doing "make"?
> > --
> > D. Richard Hipp <[EMAIL PROTECTED]>
> >
> >
> > -
> >
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: sqlite_omit_xx build failure

[weiyang wang]

Hi Dr.H,
Yes, I did a clean build in a clean directory( (top)/bld ). and i had run
'make clean' before i run the 'make'.

i am wondering whether the lemon tool configuration in my environment is
well done or not.

thanks in advance.

wang

On 6/11/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


"weiyang wang" <[EMAIL PROTECTED]> wrote:
> hi,
>
> i am trying to get a smaller sqlite lib by adding sqlite_omit_xx
defines.
> but it failed at the compiler time.
>
> steps:
> 1, in 'Makefile.in ', omit macro is added:
>
>   TCC += -DSQLITE_OMIT_ALTERTABLE=1
>
> 2, run '(top)/configure' and 'make' in cygwin,
>
> 3, i got the fowllowing error:
>

Did you do this from a clean directory?  Did you
do "make clean" before doing "make"?
--
D. Richard Hipp <[EMAIL PROTECTED]>


-

To unsubscribe, send email to [EMAIL PROTECTED]

-

[sqlite] Combining queries

[Sebastian Tennant]

Hi all,

I have three tables; users, propositions, and subscriptions:

  users
  -
  user_id user_name   user_password   user_email
  --- -   -   
--
  U01 mikescott   [EMAIL 
PROTECTED]
  U02 steve   wickham [EMAIL 
PROTECTED]
  U03 kevin   wilkinson   [EMAIL 
PROTECTED]
  U04 anthony thistlewaite[EMAIL 
PROTECTED]
  U05 karlwalinger[EMAIL 
PROTECTED]

  propostions
  ---
  prop_id proposition user_id
  --- ---
  P01 "Learn Chinese" U01
  P02 "Boycott U2"U02
  P03 "Come Back As A God"U03

  subscriptions
  -
  sub_id  prop_id  user_id
  --  ---  ---
  S01 P02  U01 --+
  S02 P02  U03   |--> Everyone has decided 
to boycott U2
  S03 P02  U04   |(Steve isn't listed 
because he proposed it)
  S04 P02  U05 --+
  S05 P01  U02 -> Steve has decided to 
learn Chinese
  S06 P03  U05 -> Karl wants to come 
back as a God

Given a particular user, I want to return the set of 'interesting'
propositions, i.e., those which were not proposed by the user and to
which the user is not already subscribed...

For example, if Kevin performs the query, his only 'interesting'
proposition is to "learn Chinese".

The propositions marked *Interesting* constitute the desired result
set for each user:

  "Learn Chinese" "Boycott U2" "Come Back As A God"
   -   --   --
  Mike ProposedSubscribed   *Interesting*

  SteveSubscribed  Proposed *Interesting*

  Kevin*Interesting*   Subscribed   Proposed

  Anthony  *Interesting*   Subscribed   *Interesting*

  Karl *Interesting*   Subscribed   Subscribed

In short, returning to Kevin, I want to construct a single SQL query
that effectively subtracts B from A:

A: SELECT prop_id FROM propositions WHERE ( user_id != U03 )

prop_id
---
P01Kevin can subscribe to these
P02(because he didn't propose them).

B: SELECT prop_id FROM subscriptions WHERE (user_id = U03 )

prop_id

P02 He can't subscribe to this one
(becuase he is already subscribed to it.)

Any help very much appreciated.

Sebastian

-
To unsubscribe, send email to [EMAIL PROTECTED]
-