[sqlite] SQLITE build for Linux + ARM

[Shishir . Rawat]

Hello Paul Bohme,

Could u please send me across the patch for cross compiling configure 
script sqlite for ARM + Linux, which u might hav given to ben.

it would be a great help.
thanks and awaiting for the reply.

cheers,
shishir
[EMAIL PROTECTED]

Re: [sqlite] seeking answers for a few questions about indexes

[Joe Wilson]

> The right way to do this query is:
> 
>   SELECT * FROM vals2d WHERE rowid IN
> (SELECT rowid FROM vals2d WHERE x=1
>  UNION ALL SELECT rowid FROM vals2d WHERE y=1);

Unfortunately this tranform does not work on views 
or subqueries due to NULL rowids.

sqlite> create table abc(a,b,c);
sqlite> insert into abc values(4,5,6);
sqlite> insert into abc values(3,2,1);

sqlite> select rowid, * from abc;
rowid|a|b|c
1|4|5|6
2|3|2|1

sqlite> select rowid, * from (select * from abc);
rowid|a|b|c
|4|5|6
|3|2|1

sqlite> create view vv as select * from abc;

sqlite> select rowid, * from vv;
rowid|a|b|c
|4|5|6
|3|2|1


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] Re: LIMIT and paging records

[Lindsay]

Igor Tandetnik wrote:
>
> By the way, for most queries you will find that the execution time
> grows as the OFFSET grows. To implement OFFSET X, the engine would
> enumerate records from the beginning and simply ignore the first X-1.
> Retrieving the last pageful often takes almost as long as retrieving
> all the records.

I have noticed exactly that behaviour recently in MySQL and MSSQL (where
it had to be emulated). This was when implementing a web paging access
as the original poster is doing.



-- 
Lindsay

Re: [sqlite] LIMIT and paging records

[John Stanton]

This problem is as old as data processing.  The "page N of M" situation 
which requires that the entire data set be read to get M.  Once you have 
read the entire set you have many options on how you handle your window 
on that dataset.  You could use local storage for it all or take 
advantage of a suitable B-Tree key to step define the current window and 
the last key in the window to define the next window.  The second method 
uses no additional local storage.

JS

Mikey C wrote:

I don't think you really understand what I'm trying to say.

Web based systems require paging that does not iterate through all records.

What is required is a means to LIMIT the results read from the database but
at the same time know how many records WOULD have been returned if the query
was not LIMITed.

In this way it is then possible to display results to the user:

Displaying Page 15 of 25788 pages.

Record 1
Record 2
Record ...
Record 10

Previous Page Link | Next Page Link

LIMIT gives this great power (ie. I only want to read 10 record starting
from record 151) BUT I do need to know how many records there are in total
for this query in order to page correctly and display how many pages there
are to the user.

I'd rather SQLite reads on 10 records and not 100 million records into
memory, just to discard them all except the 10 the user needs for that page
of results?

Does this make sense?  Do you imagine Google loads 8 billions records into
memory when the user is just viewing 10 results in page 5 after a broad
search?





--
View this message in context: 
http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4612492
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[John Stanton]

[EMAIL PROTECTED] wrote:

"Fred Williams" <[EMAIL PROTECTED]> wrote:


How about some form of automated(?) sequence where:

New subscriber submits subscription request.

System sends "query" message to subscriber address.

New subscriber sends "confirmation" message within reasonable time
period.

List access granted on receipt of confirmation.

At least the damn spammer would have to do a little work to be a bottom
feeder.  Too bad there must be an endless number of idiots out there (P
T Barnum postulation) or the problem would self regulate.




Such a system is already in place.  Please recall when you
signed up that you got a confirmation message that you had
to reply to before you were added to the mailing list.

And yet somehow, the spammer still managed to get signed up
using a "paypal.com" address.  How did they do that?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Is it possible to spoof the reply message?  Even if it is lost the 
villain sends what you expect the response would be, including the 
spoofed header.


It could be a classic "Man in the Middle" attack where the culprits are 
actually intercepting mail by some corrupt practice.  Spammers have 
become very cunning and a lot of money is involved.


Could you implement the type of challenge that is becoming common where 
you send an image with distorted text and require a visual decoding of 
the text in the reply for validation?  That would eliminate valid 
machine-generated responses.  Does pose a text-only problem, solved with 
a WWW link.


One other idea might be to have a regular challenge email requiring a 
reply to maintain the account.  A rule such as "fail three consecutive 
challenges and you are deleted" would be necessary to avoid eventually 
deleting all users.  A gentler variation might be to not remove the user 
but to make that user read-only by removing posting rights, preserving 
the rights of casual readers but denying access to spam.

JS

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Paul Nash]

1200 is a modest number for a mailing list . Expect it to go up to 3-4000 as 
SQLite gets more popular. One reason for the lists popularity is the 
friendliness of the list members and their willingness to respond to basic 
SQL queries. At some stage the list could be split into two. One for wrapper 
users (tcl perl) and one for those using the C API.


Regards, Paul Nash 

Re: [sqlite] where I can download sqlite 3.2.8

[rbundy]

All the filenames you need to modify are on the download page.

Regards.

rayB



** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING
*
*** Confidentiality and Privilege Notice
***

This e-mail is intended only to be read or used by the addressee. It is 
confidential and may contain legally privileged information. If you are not the 
addressee indicated in this message (or responsible for delivery of the message 
to such person), you may not copy or deliver this message to anyone, and you 
should destroy this message and kindly notify the sender by reply e-mail. 
Confidentiality and legal privilege are not waived or lost by reason of 
mistaken delivery to you.

Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

Re: [sqlite] LIMIT and paging records

[John Stanton]

To perform a count one has to read the entire dataset regardless.  Why
not implement your logic within your own program, using prepare and step 
and ceasing to unload data from the columns when you hit your predefined 
limit?

JS
Mikey C wrote:

Hi,

I think this has been discussed before, but I can't find a good solution so
I'll post it again to see what people think.

Here's the problem.  I have a large number of records in a table, which
contains many columns.  Hence a large amount of data.

I have a SQL query that filters the results by search criteria across many
of these columns.

I am using a web front end to display paged results.  I need to tell the
user how many records there are in total, how many pages and which page they
are viewing.

I would like to use the LIMIT keyword to restrict the result using the two
parameters (offset and limit count) so that I do not waste resources loading
up 1000's of records just to discard the ones not on the current page.

However if LIMIT is added to the SQL, I do not get a count of the records
that the SQL select would have produced if I had not limited the query with
LIMIT.

I could do two selects with the same WHERE restriction, one with SELECT
COUNT(*) and the other with SELECT field1, field2, etc but this seems
wasteful, especially if the query is expensive in resources.

Perhaps a new function could be added to SQLite that returns the record
count regardless of any LIMIT applied? 


e.g.

SELECT field1, field2, field3, fieldN, count_rows()
FROM table1
WHERE field99 LIKE 'G%'
AND field66 = 7
OR field18 <= 5.7
LIMIT 341, 10

will return a maximum of 10 records but the count_rows() will return how
many rows the query would return if the LIMIT was not in place?

This would make paging of results very straight foreward and efficient.

Anyone agree/disagree this would be useful?


--
View this message in context: 
http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4609360
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Alex Roston]

This is an excellent idea.

Alex


René Tegel wrote:


Hi,

Seen the popularity of sqlite, i think 1200 subscribers is very 
reasonable. Lots of people track mailing lists, only contributing 
rarely but nevertheless are interested.


You could consider a system that requires moderation by the list 
administrator for each first message a newly subscribed user posts. 
This is a small inconvenience for new members (may take some hours or 
a day before his/hers message appears on the list) but is effective 
against list spam. Several mailing list systems include such feature, 
and it appears a reasonable trade-off.


regards,

Rene

[EMAIL PROTECTED] schreef:


"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
 

dilettantes remain rude.Where we can almost borrow money from our 
earring.Hugo, the friend of Hugo and earns frequent flier miles with 
power drill near.




In order to be able to send messages to this mailing list,
the spammer above had to subscribe.  To subscribe means that
he had to respond to an email that was sent to the subscription
address.  Since his email address does not exist, I'm wondering
how he managed to pull this off.  Any ideas?

I have unsubscribed every account from "paypal.com" and "ebay.com".
All such accounts were of the form "[EMAIL PROTECTED]" or
"[EMAIL PROTECTED]", etc.  There were 7 such accounts.

After purging the accounts above, we are still left with 1217
active subscribers.  This seems like a lot to me.  I'm wondering
if some fraction of these might be inactive accounts, or accounts
belonging to people who have spam filters turned on to delete
incoming email from sqlite.org.  Does anybody have any ideas on
how we might remove people from the mailing list that do not
actually read messages from the mailing list?  When email bounces,
the user is removed automatically.  But email addresses that silently
absorb messages and never deliver them to a real human can linger
on the mailing list indefinitely. 
I wonder if I need to implement some kind of mechanism that requires

you to either send a message to the mailing list or else renew your
subscription every 3 months.  Does anybody have any experience with
other mailing lists that require such measures?

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


  

[sqlite] Re: LIMIT and paging records

[Igor Tandetnik]

Mikey C  wrote:

I don't think you really understand what I'm trying to say.

Web based systems require paging that does not iterate through all
records.


I understand perfectly what you want. All I'm saying is there are solid 
technical reasons why SQLite (and really any other SQL engine) cannot 
deliver.


Finding out how many records there are in the recordset is precisely as 
computationally intensive as running select count(*) query, which you 
can as well do yourself. Presumably, you want to apply LIMIT clause to 
speed up execution, but if you at the same time require that the query 
know the exact number of rows in a full resultset (in a hypothetical SQL 
engine that supports such a feature), you would lose all benefits of 
LIMIT because the engine would have to enumerate all records anyway.



LIMIT gives this great power (ie. I only want to read 10 record
starting from record 151)


By the way, for most queries you will find that the execution time grows 
as the OFFSET grows. To implement OFFSET X, the engine would enumerate 
records from the beginning and simply ignore the first X-1. Retrieving 
the last pageful often takes almost as long as retrieving all the 
records.



I'd rather SQLite reads on 10 records and not 100 million records into
memory, just to discard them all except the 10 the user needs for
that page of results?


But if it does not look at all the records, how precisely do you propose 
it should know how many there are? Built-in crystal ball?


Igor Tandetnik 

RE: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Robert Simpson]

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Monday, May 29, 2006 7:25 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Purging the mailing list roles. Was: 
> Please Restore Your Account Access
>
> Such a system is already in place.  Please recall when you
> signed up that you got a confirmation message that you had
> to reply to before you were added to the mailing list.
> 
> And yet somehow, the spammer still managed to get signed up
> using a "paypal.com" address.  How did they do that?

If the validation utility validates on the From header (faked addr) and
replies honoring the "ReplyTo" header (real addr), then that'd be one way to
skim through.

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Markus Hoenicka]

Clay Dowling <[EMAIL PROTECTED]> was heard to say:

> You might want to consider reworking the check mechanism, using the
> capture mechanism that a lot of web forums and blogs use.  Users must
> type in text presented on a web page in the form of a graphic.  That
> will put a stop to the automated signup pretty quickly.
>

...and it will exclude the visually impaired just as quickly.

regards,
Markus

-- 
Markus Hoenicka
[EMAIL PROTECTED]
(Spam-protected email: replace the quadrupeds with "mhoenicka")
http://www.mhoenicka.de

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Clay Dowling]

[EMAIL PROTECTED] wrote:

"Fred Williams" <[EMAIL PROTECTED]> wrote:

How about some form of automated(?) sequence where:

New subscriber submits subscription request.

System sends "query" message to subscriber address.

New subscriber sends "confirmation" message within reasonable time
period.

List access granted on receipt of confirmation.

At least the damn spammer would have to do a little work to be a bottom
feeder.  Too bad there must be an endless number of idiots out there (P
T Barnum postulation) or the problem would self regulate.



Such a system is already in place.  Please recall when you
signed up that you got a confirmation message that you had
to reply to before you were added to the mailing list.

And yet somehow, the spammer still managed to get signed up
using a "paypal.com" address.  How did they do that?


[EMAIL PROTECTED] at least does have an auto-responder.  The message 
contains the text of any message sent to it, appended to the bottom of 
the email.  That would serve to automatically validate the check email.


You might want to consider reworking the check mechanism, using the 
capture mechanism that a lot of web forums and blogs use.  Users must 
type in text presented on a web page in the form of a graphic.  That 
will put a stop to the automated signup pretty quickly.


Clay
--
CeaMuS, Simple Content Management
http://www.ceamus.com

Re: [sqlite] LIMIT and paging records

[drh]

"A. Pagaltzis" <[EMAIL PROTECTED]> wrote:
> 
> At the same time, to my knowledge, a query which has an `ORDER
> BY` clause always has to produce all results before it can apply
> a `LIMIT` to the result set, so at least in that case, what you
> want should be possible.
> 

Depends.  SQLite will use an index to satisfy the ORDER BY
clause if it can, and in that case it is *not* necessary to produce
all results before applying the LIMIT.

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

Re: [sqlite] LIMIT and paging records

[A. Pagaltzis]

* Mikey C <[EMAIL PROTECTED]> [2006-05-29 17:10]:
> Do you imagine Google loads 8 billions records into memory when
> the user is just viewing 10 results in page 5 after a broad
> search?

You can’t ask Google for more than the first 1,000 hits on any
search. (Go ahead and try.) There is a reason for that. Likewise
the number Google presents for the total is just an estimate.
There is a reason for that too.

At the same time, to my knowledge, a query which has an `ORDER
BY` clause always has to produce all results before it can apply
a `LIMIT` to the result set, so at least in that case, what you
want should be possible.

Regards,
-- 
Aristotle Pagaltzis // 

Re: [sqlite] LIMIT and paging records

[Mikey C]

I don't think you really understand what I'm trying to say.

Web based systems require paging that does not iterate through all records.

What is required is a means to LIMIT the results read from the database but
at the same time know how many records WOULD have been returned if the query
was not LIMITed.

In this way it is then possible to display results to the user:

Displaying Page 15 of 25788 pages.

Record 1
Record 2
Record ...
Record 10

Previous Page Link | Next Page Link

LIMIT gives this great power (ie. I only want to read 10 record starting
from record 151) BUT I do need to know how many records there are in total
for this query in order to page correctly and display how many pages there
are to the user.

I'd rather SQLite reads on 10 records and not 100 million records into
memory, just to discard them all except the 10 the user needs for that page
of results?

Does this make sense?  Do you imagine Google loads 8 billions records into
memory when the user is just viewing 10 results in page 5 after a broad
search?





--
View this message in context: 
http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4612492
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Re: Check for duplicate values in database

[Derrell . Lipman]

"Anish Enos Mathew" <[EMAIL PROTECTED]> writes:

> Igor, I know that it will show error when we try to insert a value which
> is already there in the database when that field is a primary key.  But
> I want to check it out and I don't want that number to be inserted into
> the database. I was asking about a method which finds this. I don't want
> to terminate the program and have other numbers in a loop to be inserted
> into the database.

Instead of using INSERT which will generate an error if the primary key is
already in use, you can use INSERT OR IGNORE to ignore the error and simply
not insert the new record.  If you wanted instead to replace the old record
with the new one, you could use INSERT OR REPLACE.

Derrell

RE: [sqlite] Re: Check for duplicate values in database

[Anish Enos Mathew]

Igor, I know that it will show error when we try to insert a value which
is already there in the database when that field is a primary key.  But
I want to check it out and I don't want that number to be inserted into
the database. I was asking about a method which finds this. I don't want
to terminate the program and have other numbers in a loop to be inserted
into the database.

Original Message-
From: Igor Tandetnik [mailto:[EMAIL PROTECTED]
Sent: Monday, May 29, 2006 7:52 PM
To: SQLite
Subject: [sqlite] Re: Check for duplicate values in database

Anish Enos Mathew <[EMAIL PROTECTED]>
wrote:
>  I created a table as follows.
>
> create table data_table(seq_number integer primary key,data
> text,curr_date text)",NULL, NULL, NULL);
>
> I want to insert 100 records into the database randomly. Here
> "seq_number" is the primary key. Is there any method by which I can
> check whether a randomly selected number which I am going to insert in
> the database already exists in the database or not?

Being a primary key, the database engine will enforce its uniqueness.
Just try to insert and check for errors, the operation will fail if this

key already exists.

Igor Tandetnik



The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Jalil Vaidya]

--- [EMAIL PROTECTED] wrote:

> And yet somehow, the spammer still managed to get
> signed up
> using a "paypal.com" address.  How did they do that?

Its probably very easy to screen scrape the message to
harvest every link in the confirmation message and do
a wget on it! I must admit that I do not remember what
the confirmation message looks like when signing up
for sqlite but most of such mails have a link to click
on or just need to reply to the mail. Both of the ways
are very easy automate. 

I am wondering if paypal supports Domain Keys (or some
other such mechanism) like yahoo does that ensures
that the mail actually came from that domain. If they
do then it would help to implement such checks for
"blacklisted" (sic) domains such as paypal or ebay.

Like other members mentioned before, I respond once in
a blue moon too but don't mind having to confirm my
membership every month or three. It is just going to
be a little bit of hassle...

Regards,

Jalil Vaidya

01001010
0111
01101100
01101001
01101100

RE: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Fred Williams]

Here's an example:
---
Hi Fred Willisams,

Please click on the following link to activate your account. Then start
Worksite CD and click 'Yes' to get current prices at your local Home
Depot:

Please Click Here!

Thank you for using The Home Depot Worksite CD. If you need help with
Worksite CD or have a question about getting daily price updates, please
call 760-438-3254 between 8 AM and 5 PM (Pacific) Monday through Friday.
We'll be glad to help.

The Worksite CD Team
[EMAIL PROTECTED]

The link above points to:
://vf110.probookcd.com/[EMAIL PROTECTED]&Ke
y=bf3y9e9txka

--


> -Original Message-
> From: Fred Williams [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 29, 2006 9:07 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Purging the mailing list roles. Was: Please
> Restore Your Account Access
>
>
> How about some form of automated(?) sequence where:
>
>   New subscriber submits subscription request.
>
>   System sends "query" message to subscriber address.
>
>   New subscriber sends "confirmation" message within
> reasonable time
> period.
>
>   List access granted on receipt of confirmation.
>
> At least the damn spammer would have to do a little work to
> be a bottom
> feeder.  Too bad there must be an endless number of idiots
> out there (P
> T Barnum postulation) or the problem would self regulate.
>
> Fred
>
> > -Original Message-
> > From: René Tegel [mailto:[EMAIL PROTECTED]
> > Sent: Monday, May 29, 2006 7:13 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Purging the mailing list roles. Was: Please
> > Restore Your Account Access
> >
> >
> > Hi,
> >
> > Seen the popularity of sqlite, i think 1200 subscribers is very
> > reasonable. Lots of people track mailing lists, only
> > contributing rarely
> > but nevertheless are interested.
> >
> > You could consider a system that requires moderation by the list
> > administrator for each first message a newly subscribed user
> > posts. This
> > is a small inconvenience for new members (may take some hours
> > or a day
> > before his/hers message appears on the list) but is
> effective against
> > list spam. Several mailing list systems include such feature, and it
> > appears a reasonable trade-off.
> >
> > regards,
> ...
>

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[drh]

"Fred Williams" <[EMAIL PROTECTED]> wrote:
> How about some form of automated(?) sequence where:
> 
>   New subscriber submits subscription request.
> 
>   System sends "query" message to subscriber address.
> 
>   New subscriber sends "confirmation" message within reasonable time
> period.
> 
>   List access granted on receipt of confirmation.
> 
> At least the damn spammer would have to do a little work to be a bottom
> feeder.  Too bad there must be an endless number of idiots out there (P
> T Barnum postulation) or the problem would self regulate.
> 

Such a system is already in place.  Please recall when you
signed up that you got a confirmation message that you had
to reply to before you were added to the mailing list.

And yet somehow, the spammer still managed to get signed up
using a "paypal.com" address.  How did they do that?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

Re: [sqlite] Check for duplicate values in database

[Dennis Cote]

Anish Enos Mathew wrote:


I want to insert 100 records into the database randomly. Here
"seq_number" is the primary key. Is there any method by which I can
check whether a randomly selected number which I am going to insert in
the database already exists in the database or not?
  

Anish,

You can simply do a select before the insert to see if your new primary 
key already exists. Since seq_number is a primary key, the query below 
will return either 0 if it doesn't exist, or 1 if it does.


 select count(*) from data_table where seq_number = :my_random_number;

Or you can just go ahead and execute the insert. You will get an error 
if you try to insert a record with a duplicate primary key. Catch the 
error, generate a new random number and try again.


HTH
Dennis Cote

[sqlite] Re: Check for duplicate values in database

[Igor Tandetnik]

Anish Enos Mathew <[EMAIL PROTECTED]>
wrote:

 I created a table as follows.

create table data_table(seq_number integer primary key,data
text,curr_date text)",NULL, NULL, NULL);

I want to insert 100 records into the database randomly. Here
"seq_number" is the primary key. Is there any method by which I can
check whether a randomly selected number which I am going to insert in
the database already exists in the database or not?


Being a primary key, the database engine will enforce its uniqueness. 
Just try to insert and check for errors, the operation will fail if this 
key already exists.


Igor Tandetnik 

[sqlite] Re: LIMIT and paging records

[Igor Tandetnik]

Mikey C  wrote:

However if LIMIT is added to the SQL, I do not get a count of the
records that the SQL select would have produced if I had not limited
the query with LIMIT.

I could do two selects with the same WHERE restriction, one with
SELECT COUNT(*) and the other with SELECT field1, field2, etc but
this seems wasteful, especially if the query is expensive in
resources.

Perhaps a new function could be added to SQLite that returns the
record count regardless of any LIMIT applied?


More often than not, the only way to find out the number of records in 
the resultset is to fully execute the query and step through all 
records. Which is precisely what select count(*) is doing. Your 
hypothetical function would completely defeat the purpose of the LIMIT 
clause.


Igor Tandetnik 

RE: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Fred Williams]

How about some form of automated(?) sequence where:

New subscriber submits subscription request.

System sends "query" message to subscriber address.

New subscriber sends "confirmation" message within reasonable time
period.

List access granted on receipt of confirmation.

At least the damn spammer would have to do a little work to be a bottom
feeder.  Too bad there must be an endless number of idiots out there (P
T Barnum postulation) or the problem would self regulate.

Fred

> -Original Message-
> From: René Tegel [mailto:[EMAIL PROTECTED]
> Sent: Monday, May 29, 2006 7:13 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Purging the mailing list roles. Was: Please
> Restore Your Account Access
>
>
> Hi,
>
> Seen the popularity of sqlite, i think 1200 subscribers is very
> reasonable. Lots of people track mailing lists, only
> contributing rarely
> but nevertheless are interested.
>
> You could consider a system that requires moderation by the list
> administrator for each first message a newly subscribed user
> posts. This
> is a small inconvenience for new members (may take some hours
> or a day
> before his/hers message appears on the list) but is effective against
> list spam. Several mailing list systems include such feature, and it
> appears a reasonable trade-off.
>
> regards,
...

RE: [sqlite] SQLite as R data store and spatial data maybe ?

[Eric Franks]

How do I unsubscribe?  I don't want to keep getting these emails.



-Original Message-
From: Noel Frankinet [mailto:[EMAIL PROTECTED] 
Sent: Monday, May 29, 2006 9:23 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite as R data store and spatial data maybe ?

[EMAIL PROTECTED] wrote:

>Noel Frankinet <[EMAIL PROTECTED]> wrote:
>  
>
>>>You might want to take a look at DRH's proposal, 
>>>http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
>>>which looks like it would make it easier for the R community to 
>>>implement the sort of interface you're talking about.
>>>  
>>>
>>I've read the proposed virtualTable schema.
>>Mr Hipp could you elaborate on how you envision spatial indexing ?
>>
>>
>
>I've not thought about it.  The VirtualTable proposal was
>developped to support full-text search and as something to
>replace the (goofy) pragmas currently used to find schema
>information.  I would imagine that virtualtables would also
>be useful for spatial indexing, but I have not looked closely
>into the matter.
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>
>
>
>
>  
>
Thank you for your reply,
I suppose that whatever indexing technique, it boils down to " I have 
that bunch of keys please return the corresponding records".
I can then use IN or BETWEEN statement, but I suspect that a UNION 
between a TABLE(query_quadtree(interesting_window)) (is this a virtual 
table?) and my target table would do better.
Am I right ? maybe a little bit confused ?

Best regards




-- 
Noël Frankinet
Gistek Software SA
http://www.gistek.net

Re: [sqlite] SQLite as R data store and spatial data maybe ?

[Noel Frankinet]

[EMAIL PROTECTED] wrote:


Noel Frankinet <[EMAIL PROTECTED]> wrote:
 

You might want to take a look at DRH's proposal, 
http://www.sqlite.org/cvstrac/wiki?p=VirtualTables
which looks like it would make it easier for the R community to 
implement the sort of interface you're talking about.
 


I've read the proposed virtualTable schema.
Mr Hipp could you elaborate on how you envision spatial indexing ?
   



I've not thought about it.  The VirtualTable proposal was
developped to support full-text search and as something to
replace the (goofy) pragmas currently used to find schema
information.  I would imagine that virtualtables would also
be useful for spatial indexing, but I have not looked closely
into the matter.
--
D. Richard Hipp   <[EMAIL PROTECTED]>



 


Thank you for your reply,
I suppose that whatever indexing technique, it boils down to " I have 
that bunch of keys please return the corresponding records".
I can then use IN or BETWEEN statement, but I suspect that a UNION 
between a TABLE(query_quadtree(interesting_window)) (is this a virtual 
table?) and my target table would do better.

Am I right ? maybe a little bit confused ?

Best regards




--
Noël Frankinet
Gistek Software SA
http://www.gistek.net

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Clay Dowling]

[EMAIL PROTECTED] wrote:


In order to be able to send messages to this mailing list,
the spammer above had to subscribe.  To subscribe means that
he had to respond to an email that was sent to the subscription
address.  Since his email address does not exist, I'm wondering
how he managed to pull this off.  Any ideas?

I have unsubscribed every account from "paypal.com" and "ebay.com".
All such accounts were of the form "[EMAIL PROTECTED]" or
"[EMAIL PROTECTED]", etc.  There were 7 such accounts.


I would start by putting a filter in front of the list that won't accept 
anything with @paypal.com or @ebay.com, not just at signup time but when 
the messages are received.


As to how he pulled it off, there's probably an auto-responder on those 
addresses.


Clay Dowling
--
http://www.lazarusid.com/notes/
Lazarus Notes
Articles and Commentary on Web Development

Re: [sqlite] Purging the mailing list roles.

[A. Pagaltzis]

* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-05-29 12:50]:
> I wonder if I need to implement some kind of mechanism that
> requires you to either send a message to the mailing list or
> else renew your subscription every 3 months. Does anybody have
> any experience with other mailing lists that require such
> measures?

I've never seen that anywhere else, probably because none of the
popular mailing list managers include such a feature.

Mailman tries to deal with the problem by sending membership
reminder mails on every first of the month, but that's hardly
ideal. I am subscribed to lists managed on so many different
hosts that I used to get flooded at the start of every month
(two-dozen-odd reminders), so eventually I wrote a recipe to
trash these reminders before I even see them.

Your scheme is not airtight either: writing a filter recipe to
autorespond to renewal mails is a pretty easy task. But not many
people are likely to actually do that. You'd want to check
whether it's actually unsubscribing anyone after several months
of running it, though, to make sure you aren't just bugging
lurkers for no benefit.

Regards,
-- 
Aristotle Pagaltzis // 

AW: Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[michael . ruck]

I second this. I don't mind having to post or refresh my subscription every 
once in a while. It would however be important for me to receive a notification 
to refresh my subscription instead of just silently removing my subscription.

HTH,
Mike

>Hi,
>
>I think many of the 1217 active subscribers are people like me who tune in to 
>
>the list but only contribute once in a blue moon.
>
>I do not have any objection to a "send email to keep your subscription active" 
>
>idea, but I have never seen that used in the other mailing lists that I 
>subscribe to.
>
>Regards,
>Eugene Wee
>
>[EMAIL PROTECTED] wrote:
>> "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
>>> dilettantes remain rude.Where we can almost borrow money from our 
>>> earring.Hugo, the friend of Hugo and earns frequent flier miles 
>>> with power drill near.
>> 
>> In order to be able to send messages to this mailing list,
>> the spammer above had to subscribe.  To subscribe means that
>> he had to respond to an email that was sent to the subscription
>> address.  Since his email address does not exist, I'm wondering
>> how he managed to pull this off.  Any ideas?
>> 
>> I have unsubscribed every account from "paypal.com" and "ebay.com".
>> All such accounts were of the form "[EMAIL PROTECTED]" or
>> "[EMAIL PROTECTED]", etc.  There were 7 such accounts.
>> 
>> After purging the accounts above, we are still left with 1217
>> active subscribers.  This seems like a lot to me.  I'm wondering
>> if some fraction of these might be inactive accounts, or accounts
>> belonging to people who have spam filters turned on to delete
>> incoming email from sqlite.org.  Does anybody have any ideas on
>> how we might remove people from the mailing list that do not
>> actually read messages from the mailing list?  When email bounces,
>> the user is removed automatically.  But email addresses that silently
>> absorb messages and never deliver them to a real human can linger
>> on the mailing list indefinitely.  
>> 
>> I wonder if I need to implement some kind of mechanism that requires
>> you to either send a message to the mailing list or else renew your
>> subscription every 3 months.  Does anybody have any experience with
>> other mailing lists that require such measures?
>> 
>> --
>> D. Richard Hipp   <[EMAIL PROTECTED]>
>> 
>

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[René Tegel]

Hi,

Seen the popularity of sqlite, i think 1200 subscribers is very 
reasonable. Lots of people track mailing lists, only contributing rarely 
but nevertheless are interested.


You could consider a system that requires moderation by the list 
administrator for each first message a newly subscribed user posts. This 
is a small inconvenience for new members (may take some hours or a day 
before his/hers message appears on the list) but is effective against 
list spam. Several mailing list systems include such feature, and it 
appears a reasonable trade-off.


regards,

Rene

[EMAIL PROTECTED] schreef:

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
  
dilettantes remain rude.Where we can almost borrow money from our 
earring.Hugo, the friend of Hugo and earns frequent flier miles 
with power drill near.



In order to be able to send messages to this mailing list,
the spammer above had to subscribe.  To subscribe means that
he had to respond to an email that was sent to the subscription
address.  Since his email address does not exist, I'm wondering
how he managed to pull this off.  Any ideas?

I have unsubscribed every account from "paypal.com" and "ebay.com".
All such accounts were of the form "[EMAIL PROTECTED]" or
"[EMAIL PROTECTED]", etc.  There were 7 such accounts.

After purging the accounts above, we are still left with 1217
active subscribers.  This seems like a lot to me.  I'm wondering
if some fraction of these might be inactive accounts, or accounts
belonging to people who have spam filters turned on to delete
incoming email from sqlite.org.  Does anybody have any ideas on
how we might remove people from the mailing list that do not
actually read messages from the mailing list?  When email bounces,
the user is removed automatically.  But email addresses that silently
absorb messages and never deliver them to a real human can linger
on the mailing list indefinitely.  


I wonder if I need to implement some kind of mechanism that requires
you to either send a message to the mailing list or else renew your
subscription every 3 months.  Does anybody have any experience with
other mailing lists that require such measures?

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


  

Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[Eugene Wee]

Hi,

I think many of the 1217 active subscribers are people like me who tune in to 
the list but only contribute once in a blue moon.


I do not have any objection to a "send email to keep your subscription active" 
idea, but I have never seen that used in the other mailing lists that I 
subscribe to.


Regards,
Eugene Wee

[EMAIL PROTECTED] wrote:

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
dilettantes remain rude.Where we can almost borrow money from our 
earring.Hugo, the friend of Hugo and earns frequent flier miles 
with power drill near.


In order to be able to send messages to this mailing list,
the spammer above had to subscribe.  To subscribe means that
he had to respond to an email that was sent to the subscription
address.  Since his email address does not exist, I'm wondering
how he managed to pull this off.  Any ideas?

I have unsubscribed every account from "paypal.com" and "ebay.com".
All such accounts were of the form "[EMAIL PROTECTED]" or
"[EMAIL PROTECTED]", etc.  There were 7 such accounts.

After purging the accounts above, we are still left with 1217
active subscribers.  This seems like a lot to me.  I'm wondering
if some fraction of these might be inactive accounts, or accounts
belonging to people who have spam filters turned on to delete
incoming email from sqlite.org.  Does anybody have any ideas on
how we might remove people from the mailing list that do not
actually read messages from the mailing list?  When email bounces,
the user is removed automatically.  But email addresses that silently
absorb messages and never deliver them to a real human can linger
on the mailing list indefinitely.  


I wonder if I need to implement some kind of mechanism that requires
you to either send a message to the mailing list or else renew your
subscription every 3 months.  Does anybody have any experience with
other mailing lists that require such measures?

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

[sqlite] Re: SQLite minimum RAM requirements?

[Andreas Schwarz]

[EMAIL PROTECTED] schrieb:
> Andreas Schwarz <[EMAIL PROTECTED]> wrote:
>> Hi,
>>
>> I would like to use SQLite in an embedded device (MP3 player), but I
>> didn't find any information on minimum RAM requirements. Can anyone give
>> me a rough estimate of how much RAM I will need? 1k/10k/100k/1M?
>>
> 
> You can configure it in various ways.
> 
> Embedded device manufactures tell me that they usually
> get away with a 4K or 8K stack and 50-60K of heap space.

Unfortunately I can only spare about 10 kB. Thanks for your reply.

RE: [sqlite] Possible bug in sqlite3_prepare

[Michael B. Hansen]

Hi!

Regarding my weird bug in regards to sqlite3_prepare. I have changed
from Finisar.SQLite to Mono.Data.SqliteClient - and this seems to be
working better.

I cannot reproduce the error with Mono.Data.SqliteClient - so I suspect
somekind of memory allocation error in either Finisar.SQLite or the .NET
framework itself. However - where the error in Finisar.SQLite may be I
cannot tell, everything looks to be allocated and deallocated correctly.
None the less - the error isn't there in Mono.Data.SqliteClient. The two
libraries do allocate and deallocate unmanaged memory on the heap
differently, so I cannot tell whether or not the error is in
Finisar.SQLite or in the .NET framework itself.

Thank you for your help - those of you who responded :)


/Michael



-Original Message-
From: Michael B. Hansen [mailto:[EMAIL PROTECTED] 
Sent: 26. maj 2006 21:19
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Possible bug in sqlite3_prepare

For whom it may concern.
 
I have done some quick testing - and it seems Mono.Data.SQLite is fully
compatibale with MS.Net framework 1.1 - great job you guys at Mono :)
 
On monday I'll convert my project to use Mono's wrapper - and see if I
can reproduce the error here.
 
 
/Michael



From: Michael B. Hansen [mailto:[EMAIL PROTECTED]
Sent: Fri 2006-05-26 19:08
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Possible bug in sqlite3_prepare



Hi Robert,

My project doesn't work well with .NET 2.0 - so that's not an option
(believe me, I have thought about it) :(

However, does anybody know if the Mono SQLite wrapper work with MS .NET
Framework 1.1?


/Michael




From: Robert Simpson [mailto:[EMAIL PROTECTED]
Sent: Fri 2006-05-26 17:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Possible bug in sqlite3_prepare



> - Original Message -
> From: "Michael B. Hansen" <[EMAIL PROTECTED]>
> To: 
> Sent: Friday, May 26, 2006 8:23 AM
> Subject: [sqlite] Possible bug in sqlite3_prepare
>
>
> Hi,
>
> For the last month I've been troubled by an apparent bug in SQLite 
> 3.3.5 which has been very hard to track down. After extensive 
> debugging and bug-tracking it seems there must be a bug/feature 
> somewhere in SQLite - propably in sqlite3_prepare because this is 
> where the symptom of the bug appears every time. It escalated when I 
> updated the SQLite from 3.3.4 to
> 3.3.5 - although I'm not sure how often the problem occured in 3.3.4.
>
> I'm using SQLite 3.3.5 in a threaded .NET environment - although all 
> communication with SQLite happens synchronized manner (eg. only one 
> thread at a time).

It's possible it could be a compatibility issue relating to the Finisar
library which was last released and tested against an older SQLite
version.

Do you happen to have access to Visual Studio 2005 and will your program
compile on .NET 2.0?  If so, give the ADO.NET 2.0 provider a try and see
if it still fails.  Compiling with a different provider might help
isolate whether it's a wrapper issue or a SQLite issue.

http://sqlite.phxsoftware.com 


Robert

[sqlite] LIMIT and paging records

[Mikey C]

Hi,

I think this has been discussed before, but I can't find a good solution so
I'll post it again to see what people think.

Here's the problem.  I have a large number of records in a table, which
contains many columns.  Hence a large amount of data.

I have a SQL query that filters the results by search criteria across many
of these columns.

I am using a web front end to display paged results.  I need to tell the
user how many records there are in total, how many pages and which page they
are viewing.

I would like to use the LIMIT keyword to restrict the result using the two
parameters (offset and limit count) so that I do not waste resources loading
up 1000's of records just to discard the ones not on the current page.

However if LIMIT is added to the SQL, I do not get a count of the records
that the SQL select would have produced if I had not limited the query with
LIMIT.

I could do two selects with the same WHERE restriction, one with SELECT
COUNT(*) and the other with SELECT field1, field2, etc but this seems
wasteful, especially if the query is expensive in resources.

Perhaps a new function could be added to SQLite that returns the record
count regardless of any LIMIT applied? 

e.g.

SELECT field1, field2, field3, fieldN, count_rows()
FROM table1
WHERE field99 LIKE 'G%'
AND field66 = 7
OR field18 <= 5.7
LIMIT 341, 10

will return a maximum of 10 records but the count_rows() will return how
many rows the query would return if the LIMIT was not in place?

This would make paging of results very straight foreward and efficient.

Anyone agree/disagree this would be useful?


--
View this message in context: 
http://www.nabble.com/LIMIT+and+paging+records-t1698512.html#a4609360
Sent from the SQLite forum at Nabble.com.

[sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access

[drh]

"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> dilettantes remain rude.Where we can almost borrow money from our 
> earring.Hugo, the friend of Hugo and earns frequent flier miles 
> with power drill near.

In order to be able to send messages to this mailing list,
the spammer above had to subscribe.  To subscribe means that
he had to respond to an email that was sent to the subscription
address.  Since his email address does not exist, I'm wondering
how he managed to pull this off.  Any ideas?

I have unsubscribed every account from "paypal.com" and "ebay.com".
All such accounts were of the form "[EMAIL PROTECTED]" or
"[EMAIL PROTECTED]", etc.  There were 7 such accounts.

After purging the accounts above, we are still left with 1217
active subscribers.  This seems like a lot to me.  I'm wondering
if some fraction of these might be inactive accounts, or accounts
belonging to people who have spam filters turned on to delete
incoming email from sqlite.org.  Does anybody have any ideas on
how we might remove people from the mailing list that do not
actually read messages from the mailing list?  When email bounces,
the user is removed automatically.  But email addresses that silently
absorb messages and never deliver them to a real human can linger
on the mailing list indefinitely.  

I wonder if I need to implement some kind of mechanism that requires
you to either send a message to the mailing list or else renew your
subscription every 3 months.  Does anybody have any experience with
other mailing lists that require such measures?

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

Re: [sqlite] where I can download sqlite 3.2.8

[yuyen]

Hi, rayB

Thank you very much for the information about download previous version. I 
have tried that and I've got the zip file which contains only sqlite3.exe. 
How about the sqlite3.dll, what kind of file name we can download the dll 
file.


Jack
- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Monday, May 29, 2006 3:09 PM
Subject: Re: [sqlite] where I can download sqlite 3.2.8




Using the filenames on the downloads page
(http://www.sqlite.org/download.html) as skeletons, substitute the file
version number you require, i.e.

- the current Windows command line program is
http://www.sqlite.org/sqlite-3_3_5.zip. This becomes
http://www.sqlite.org/sqlite-3_2_8.zip to download the 3.2.8 version.

Regards.

rayB



|-+>
| |   "yuyen"  |
| |   <[EMAIL PROTECTED]|
| |   >|
| ||
| |   29/05/2006 14:46 |
| |   Please respond to|
| |   sqlite-users |
| ||
|-+>

 
>--|
 | 
|
 |   To:
|
 |   cc: 
|
 |   Subject:  [sqlite] where I can download sqlite 3.2.8 
|


 
>--|




Hi, all

I need sqlite3.exe and sqlite3.dll of version 3.2.8. Just can't find any
previous version on sqlite dfficial site. Please advise how to get the
previous verson of sqlite3 binary files or sources files.


Jack



** PLEASE CONSIDER OUR ENVIRONMENT BEFORE PRINTING
*
*** Confidentiality and Privilege Notice
***

This e-mail is intended only to be read or used by the addressee. It is 
confidential and may contain legally privileged information. If you are 
not the addressee indicated in this message (or responsible for delivery 
of the message to such person), you may not copy or deliver this message 
to anyone, and you should destroy this message and kindly notify the 
sender by reply e-mail. Confidentiality and legal privilege are not waived 
or lost by reason of mistaken delivery to you.


Qantas Airways Limited
ABN 16 009 661 901

Visit Qantas online at http://qantas.com

[sqlite] Check for duplicate values in database

[Anish Enos Mathew]

Hi all,
 I created a table as follows.

create table data_table(seq_number integer primary key,data
text,curr_date text)",NULL, NULL, NULL);

I want to insert 100 records into the database randomly. Here
"seq_number" is the primary key. Is there any method by which I can
check whether a randomly selected number which I am going to insert in
the database already exists in the database or not?


Regards,
Anish Enos Mathew



The information contained in, or attached to, this e-mail, contains 
confidential information and is intended solely for the use of the individual 
or entity to whom they are addressed and is subject to legal privilege. If you 
have received this e-mail in error you should notify the sender immediately by 
reply e-mail, delete the message from your system and notify your system 
manager. Please do not copy it for any purpose, or disclose its contents to any 
other person. The views or opinions presented in this e-mail are solely those 
of the author and do not necessarily represent those of the company. The 
recipient should check this e-mail and any attachments for the presence of 
viruses. The company accepts no liability for any damage caused, directly or 
indirectly, by any virus transmitted in this email.

www.aztecsoft.com