Re: [sqlite] Proposed removal of (mis-)feature

[Mihai Limbasan]

Yes, please remove #3. Now is a particularly good time for it seeing as 
the 3.6 line is quite new.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about sqlite database

[janard]

Thanks i will 


Kees Nuyt wrote:
> 
> On Wed, 6 Aug 2008 23:57:51 -0700 (PDT), you wrote:
> 
>>
>>In sqlite whether can we rotate content in database .
>>That means i want to restirct my database to some size,if it exceeds that
>>size it has to replace first entry.
>>Can anybody help.Thanks in advance
> 
> I had a similar requirement some time ago.
> This was my solution (it limits the number of rows in the
> table, not the size on disk, but in many implementations
> that is more or less equivalent):
> 
> CREATE TABLE jobs (
>   jobid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
>   jobprio   INTEGER  DEFAULT 9 
>   CONSTRAINT jobs_valid_prio
>   CHECK (jobprio > 0 AND jobprio < 10),
>   statusCHAR(1)  DEFAULT 'W' 
>   CONSTRAINT jobs_valid_status 
>   CHECK (status IN ('W','I','R','T','A','C')),
>   useridVARCHAR(8) NOT NULL,
>   :
>   :
> );
> CREATE INDEX idx_jobs_tsn ON jobs(TSN);
> 
> CREATE TRIGGER jobs_ins AFTER INSERT ON jobs
> FOR EACH ROW BEGIN
>   DELETE FROM jobs WHERE jobid < (NEW.jobid - );
> END;
> -- 
>   (  Kees Nuyt
>   )
> c[_]
> ___
> 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/how-to-rotate-content-in-database-in-sqlite-tp18865116p18885473.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] Possible Error in 3.6.0?

[Peter Holmes]

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

Re: [sqlite] Possible Error in 3.6.0?

[Peter Holmes]

Shane wrote:

 > This was previously reported, and couldn't be reproduced.
 >
 > http://www.sqlite.org/cvstrac/tktview?tn=3262
 >
 > Any help in recreating it would be appreciated.
 >
 > -Shane

I did the following:

  1. Installed vanilla Ubuntu 7.10 + software development
  2. Downloaded, unzipped sqlite-3.6.0.tar.gz, and cd'ed to sqlite-3.6.0
  3. mkdir bld
  4. cd bld
  5. ../configure
  6. make
  7. Noted that tclsh couldn't be found, so used Synaptic Package
 Manager to grab Tcl 8.4 + development
  8. ../configure
  9. Got a complaint about tclsh (I think...don't quote me on it)
10. ../configure --with-tcl=/usr/lib/tcl8.4
11. make
12. make fulltest
13. At that point I got the error noted in my original post

Haven't tried it with sqlite-3.6.1.tar.gz yet, but would be glad to if 
it would help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Darren Duncan]

D. Richard Hipp wrote:
> 3.  Double-quoted names fall back to being string literals if  
> there is no matching table or column name.
> 
> So I'm giving some thought to removing feature (3) above and  
> disallowing double-quoted string literals.  My concern is, though,  
> that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?

I think you should go ahead and make the change you propose, and soon as
possible.

But release that change as version 3.7.0 so it is more clear to people that
this is a significant change as far as the user experience goes, that they 
should realize this might break something if they were relying on the old 
broken behaviour, and that they need to fix their own code.

I don't agree with having a pragma to turn on the old behavior, but I do 
like the idea of issuing warnings when it appears that a user may be 
relying on the old behavior; these warnings can help them track down what 
areas of their code need to be fixed.

Or alternately make a 3.6.x series release first that adds warnings but 
keeps the old behaviour, then make a 3.7.0 release that keeps the warnings 
(modified if needed) but drops the bad behaviour.

Note that any warnings like this should be possible to turn off when the 
user knows that their code is compliant and their string literals shouldn't 
be checked anymore for similarity to column names etc.

-- Darren Duncan

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

Re: [sqlite] Proposed removal of (mis-)feature

[palmer ristevski]

He is my vote.
I like the fact that conventions #1 and #2 make things compatible with other 
databases.
Does #3, by chance, do the same with another database not mentioned
(eg. Oracle).

If it does not have any compatibility with any other database,
ONLY then I say that one should drop #3 quoting convention.

Palmer

> From: [EMAIL PROTECTED]
> To: sqlite-users@sqlite.org
> Date: Thu, 7 Aug 2008 13:26:07 -0400
> Subject: [sqlite] Proposed removal of (mis-)feature
> 
> String literals in SQL are suppose to be enclosed in single-quotes -  
> Pascal-style.  Double-quotes are used around table and/or column names  
> in cases where the name would otherwise be a keyword or when the name  
> contains non-standard characters.
> 
> But SQLite tries to be flexible and accommodating.  To this end, it  
> accepts some non-standard quoting mechanisms:
> 
> 1.  Names can be enclosed in [...] for compatibility with Access  
> and SQLServer.
> 2.  Names can be enclosed in grave accents for compatibility with  
> MySQL.
> 3.  Double-quoted names fall back to being string literals if  
> there is no matching table or column name.
> 
> In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
> leads to all kinds of confusion.  For example, if double-quotes are  
> being used correctly (which is to say to quote table or column names)  
> but a misspelling occurs in the name, the token reverts to being a  
> string literal rather than throwing an error.  Or if a double-quoted  
> string really is being used as a string literal, but later a new  
> column is added to a table that has the same name as the string text,  
> the string literal will suddenly take on the value of the column.  It  
> seems like we have one or two problem reports per month on this  
> mailing list that involve people using double-quoted names where they  
> should be using single-quoted string literals instead.
> 
> So I'm giving some thought to removing feature (3) above and  
> disallowing double-quoted string literals.  My concern is, though,  
> that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Get more from your digital life.  Find out how.
http://www.windowslive.com/default.html?ocid=TXT_TAGLM_WL_Home2_082008
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Wilson, Ron P]

+1 for removal of quote munging.

RW

Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453

sqlite>select level from sqlGuruOMeter where name="Ron Wilson";
2

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 1:26 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Problem building on Solaris

[Larry Martell]

I'm trying to build the amalgamation on Solaris, and it's failing:

> make
cd . && /bin/bash
/home/martella/sqlite3/sqlite-amalgamation-3.6.1/missing --run
aclocal-1.9
/usr/local/share/aclocal/winsz.m4:5: warning: underquoted definition
of AM_HEADER_TIOCGWINSZ_NEEDS_SYS_IOCTL
 run info '(automake)Extending aclocal'
 or see http://sources.redhat.com/automake/automake.html#Extending%20aclocal
/usr/local/share/aclocal/vorbis.m4:9: warning: underquoted definition
of XIPH_PATH_VORBIS
/usr/local/share/aclocal/termios.m4:5: warning: underquoted definition
of AM_SYS_POSIX_TERMIOS
/usr/local/share/aclocal/strtod.m4:30: warning: underquoted definition
of AM_FUNC_STRTOD
aclocal: file `/usr/local/share/aclocal/sdl.m4' does not exist
make: *** [aclocal.m4] Error 1

Anyone know how to fix this?

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

Re: [sqlite] Proposed removal of (mis-)feature

[Clark Christensen]

+1 in favor of removing non-standard quoting mechanism #3.



- Original Message 
From: D. Richard Hipp <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Thursday, August 7, 2008 10:26:07 AM
Subject: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] SPAM: Proposed removal of (mis-)feature

[Hynes, Tom]

Ditto.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Robert Simpson
Sent: Thursday, August 07, 2008 1:44 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] SPAM: Proposed removal of (mis-)feature

I have never really liked #3 and agree wholeheartedly with its demise.
Removing it in a subsequent release will force all those people using
them
for string literals to fix their code -- which isn't a bad idea at all!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 10:26 AM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Proposed removal of (mis-)feature

[Fred Williams]

Henceforth and forward:  Let the standards violator suffer his just
rewards!" :-)

Dr., your just trying to supply the worlds greatest database, not take a
bunch of slovenly coders to raise...

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 12:26 PM
To: General Discussion of SQLite Database
Subject: [sqlite] Proposed removal of (mis-)feature


String literals in SQL are suppose to be enclosed in single-quotes -
Pascal-style.  Double-quotes are used around table and/or column names
in cases where the name would otherwise be a keyword or when the name
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with
MySQL.
3.  Double-quoted names fall back to being string literals if
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and
leads to all kinds of confusion.  For example, if double-quotes are
being used correctly (which is to say to quote table or column names)
but a misspelling occurs in the name, the token reverts to being a
string literal rather than throwing an error.  Or if a double-quoted
string really is being used as a string literal, but later a new
column is added to a table that has the same name as the string text,
the string literal will suddenly take on the value of the column.  It
seems like we have one or two problem reports per month on this
mailing list that involve people using double-quoted names where they
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and
disallowing double-quoted string literals.  My concern is, though,
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Proposed removal of (mis-)feature

[Jeffrey Becker]

On Thu, Aug 7, 2008 at 2:13 PM, Kees Nuyt <[EMAIL PROTECTED]> wrote:
>
> On Thu, 7 Aug 2008 13:26:07 -0400, drh wrote:
>
> [snip]
>
>>So I'm giving some thought to removing feature (3) above and
>>disallowing double-quoted string literals.  My concern is, though,
>>that this might break many existing applications.
>>
>>What opinion do you, gentle users, have of this matter?
>
> I agree (3) should be removed. If it would break any of my
> applications (which I doubt), I'll gladly repair it, because
> I would have done a bad job, not you.
> --
>  (  Kees Nuyt
>  )
> c[_]
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

I cant count how many times I've run into this because I mis-typed a
double quoted table name and had it interpreted as a string.  I vote
for removal because it 1) creates confusion about the right way to do
stuff and 2) Creates error messages which are utterly meaningless for
debugging.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Noah Hart]

+3 on removal of #3

Noah 

--- On Thu, 8/7/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
From: D. Richard Hipp <[EMAIL PROTECTED]>
Subject: [sqlite] Proposed removal of (mis-)feature
To: "General Discussion of SQLite Database" 
Date: Thursday, August 7, 2008, 12:26 PM

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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



CONFIDENTIALITY NOTICE: 
This message may contain confidential and/or privileged information. If you are 
not the addressee or authorized to receive this for the addressee, you must not 
use, copy, disclose, or take any action based on this message or any 
information herein. If you have received this message in error, please advise 
the sender immediately by reply e-mail and delete this message. Thank you for 
your cooperation.


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

Re: [sqlite] about sqlite database

[Jardar Maatje]

My solution was as follows (this limits to specific size on disk):

Every time I insert stuff I do the following:

ReadSizeOfDBFile
If ( size > MAXDBSIZE ){
If ( freeDBPages < 10 ){
RemoveSomeRecords
}   
}

Jardar

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Kees Nuyt
Sent: 7. august 2008 20:07
To: General Discussion of SQLite Database
Subject: Re: [sqlite] about sqlite database

On Wed, 6 Aug 2008 23:57:51 -0700 (PDT), you wrote:

>
>In sqlite whether can we rotate content in database .
>That means i want to restirct my database to some size,if it exceeds that
>size it has to replace first entry.
>Can anybody help.Thanks in advance

I had a similar requirement some time ago.
This was my solution (it limits the number of rows in the
table, not the size on disk, but in many implementations
that is more or less equivalent):

CREATE TABLE jobs (
jobid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
jobprio   INTEGER  DEFAULT 9 
CONSTRAINT jobs_valid_prio
CHECK (jobprio > 0 AND jobprio < 10),
statusCHAR(1)  DEFAULT 'W' 
CONSTRAINT jobs_valid_status 
CHECK (status IN ('W','I','R','T','A','C')),
useridVARCHAR(8) NOT NULL,
:
:
);
CREATE INDEX idx_jobs_tsn ON jobs(TSN);

CREATE TRIGGER jobs_ins AFTER INSERT ON jobs
FOR EACH ROW BEGIN
DELETE FROM jobs WHERE jobid < (NEW.jobid - );
END;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

No virus found in this incoming message.
Checked by AVG - http://www.avg.com 
Version: 8.0.138 / Virus Database: 270.5.12/1596 - Release Date: 8/6/2008
16:55


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

Re: [sqlite] Proposed removal of (mis-)feature

[Kees Nuyt]

On Thu, 7 Aug 2008 13:26:07 -0400, drh wrote:

[snip]

>So I'm giving some thought to removing feature (3) above and  
>disallowing double-quoted string literals.  My concern is, though,  
>that this might break many existing applications.
>
>What opinion do you, gentle users, have of this matter?

I agree (3) should be removed. If it would break any of my
applications (which I doubt), I'll gladly repair it, because
I would have done a bad job, not you.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Michael Ruck]

Remove it. Better now than never.

Mike 

> -Ursprüngliche Nachricht-
> Von: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] Im Auftrag von D. 
> Richard Hipp
> Gesendet: Donnerstag, 7. August 2008 19:26
> An: General Discussion of SQLite Database
> Betreff: [sqlite] Proposed removal of (mis-)feature
> 
> String literals in SQL are suppose to be enclosed in 
> single-quotes - Pascal-style.  Double-quotes are used around 
> table and/or column names in cases where the name would 
> otherwise be a keyword or when the name contains non-standard 
> characters.
> 
> But SQLite tries to be flexible and accommodating.  To this 
> end, it accepts some non-standard quoting mechanisms:
> 
> 1.  Names can be enclosed in [...] for compatibility with 
> Access and SQLServer.
> 2.  Names can be enclosed in grave accents for 
> compatibility with MySQL.
> 3.  Double-quoted names fall back to being string 
> literals if there is no matching table or column name.
> 
> In retrospect, (3) seems to be a bad idea.  It is 
> accident-prone and leads to all kinds of confusion.  For 
> example, if double-quotes are being used correctly (which is 
> to say to quote table or column names) but a misspelling 
> occurs in the name, the token reverts to being a string 
> literal rather than throwing an error.  Or if a double-quoted 
> string really is being used as a string literal, but later a 
> new column is added to a table that has the same name as the 
> string text, the string literal will suddenly take on the 
> value of the column.  It seems like we have one or two 
> problem reports per month on this mailing list that involve 
> people using double-quoted names where they should be using 
> single-quoted string literals instead.
> 
> So I'm giving some thought to removing feature (3) above and 
> disallowing double-quoted string literals.  My concern is, 
> though, that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] Proposed removal of (mis-)feature

[peter]

On Thu, Aug 07, 2008 at 01:26:07PM -0400, D. Richard Hipp wrote:
> 3.  Double-quoted names fall back to being string literals if  
> there is no matching table or column name.
> 
[..]
> So I'm giving some thought to removing feature (3) above and  
> disallowing double-quoted string literals.  My concern is, though,  
> that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?

A major part of the traffic on the #sqlite IRC channel is about
explaining to people why using "" is a really really bad idea.

Any application doing "" now is prone to breakage as described
in your post.

Consider this a vote for removal :)

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

Re: [sqlite] about sqlite database

[Kees Nuyt]

On Wed, 6 Aug 2008 23:57:51 -0700 (PDT), you wrote:

>
>In sqlite whether can we rotate content in database .
>That means i want to restirct my database to some size,if it exceeds that
>size it has to replace first entry.
>Can anybody help.Thanks in advance

I had a similar requirement some time ago.
This was my solution (it limits the number of rows in the
table, not the size on disk, but in many implementations
that is more or less equivalent):

CREATE TABLE jobs (
jobid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
jobprio   INTEGER  DEFAULT 9 
CONSTRAINT jobs_valid_prio
CHECK (jobprio > 0 AND jobprio < 10),
statusCHAR(1)  DEFAULT 'W' 
CONSTRAINT jobs_valid_status 
CHECK (status IN ('W','I','R','T','A','C')),
useridVARCHAR(8) NOT NULL,
:
:
);
CREATE INDEX idx_jobs_tsn ON jobs(TSN);

CREATE TRIGGER jobs_ins AFTER INSERT ON jobs
FOR EACH ROW BEGIN
DELETE FROM jobs WHERE jobid < (NEW.jobid - );
END;
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Stephen Oberholtzer]

On Thu, Aug 7, 2008 at 1:26 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

> String literals in SQL are suppose to be enclosed in single-quotes -
> Pascal-style.  Double-quotes are used around table and/or column names
> in cases where the name would otherwise be a keyword or when the name
> contains non-standard characters.



>3.  Double-quoted names fall back to being string literals if
> there is no matching table or column name.
>
> In retrospect, (3) seems to be a bad idea.  It is accident-prone and



The first time I saw an issue related to this on the mailing list, I thought
that it should be fixed somehow.

The main reason it's been kept was for backward compatibility reasons. I
believe that one of the more recent proposed solutions was to make it a
PRAGMA or something, which was struck down because it would only make the
already-complex parser code *more* complicated.

Is there some way to have SQLite's parser take note of when condition #3
results in a literal, and output a warning or something?  Something like

fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). I am assuming it should have been a
string literal delimited by single quotes ('). This behavior will be removed
in the future; please update your SQL statements.");

Or perhaps, to be shorter,

fprintf(stderr, "Warning: The query ''%s' has an invalid table/column name
'%s' delimited by double quotes (\"). See
http://www.sqlite.org/blah/blah/blah.;);

Warnings could be allowed for "properly" by e.g. having a callback
registered, or adding a new void(*xWarning)(char*str) entry in the VFS.



-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed removal of (mis-)feature

[Ken]

+2 on removal of #3.


--- On Thu, 8/7/08, D. Richard Hipp <[EMAIL PROTECTED]> wrote:
From: D. Richard Hipp <[EMAIL PROTECTED]>
Subject: [sqlite] Proposed removal of (mis-)feature
To: "General Discussion of SQLite Database" 
Date: Thursday, August 7, 2008, 12:26 PM

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Proposed removal of (mis-)feature

[Stephen Woodbridge]

I'm +1 on this change.

While I have not run into it as a feature or mis-feature, I would concur 
that it is ambiguous and problematic. A simple clear syntax benefits 
everyone.

BTW, Thanks for all you effort to produce this wonder software.

Best regards,
   -Steve W

D. Richard Hipp wrote:
> String literals in SQL are suppose to be enclosed in single-quotes -  
> Pascal-style.  Double-quotes are used around table and/or column names  
> in cases where the name would otherwise be a keyword or when the name  
> contains non-standard characters.
> 
> But SQLite tries to be flexible and accommodating.  To this end, it  
> accepts some non-standard quoting mechanisms:
> 
> 1.  Names can be enclosed in [...] for compatibility with Access  
> and SQLServer.
> 2.  Names can be enclosed in grave accents for compatibility with  
> MySQL.
> 3.  Double-quoted names fall back to being string literals if  
> there is no matching table or column name.
> 
> In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
> leads to all kinds of confusion.  For example, if double-quotes are  
> being used correctly (which is to say to quote table or column names)  
> but a misspelling occurs in the name, the token reverts to being a  
> string literal rather than throwing an error.  Or if a double-quoted  
> string really is being used as a string literal, but later a new  
> column is added to a table that has the same name as the string text,  
> the string literal will suddenly take on the value of the column.  It  
> seems like we have one or two problem reports per month on this  
> mailing list that involve people using double-quoted names where they  
> should be using single-quoted string literals instead.
> 
> So I'm giving some thought to removing feature (3) above and  
> disallowing double-quoted string literals.  My concern is, though,  
> that this might break many existing applications.
> 
> What opinion do you, gentle users, have of this matter?
> 
> D. Richard Hipp
> [EMAIL PROTECTED]
> 
> 
> 
> ___
> 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] SPAM: Proposed removal of (mis-)feature

[Robert Simpson]

I have never really liked #3 and agree wholeheartedly with its demise.
Removing it in a subsequent release will force all those people using them
for string literals to fix their code -- which isn't a bad idea at all!

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp
Sent: Thursday, August 07, 2008 10:26 AM
To: General Discussion of SQLite Database
Subject: SPAM: [sqlite] Proposed removal of (mis-)feature

String literals in SQL are suppose to be enclosed in single-quotes -  
Pascal-style.  Double-quotes are used around table and/or column names  
in cases where the name would otherwise be a keyword or when the name  
contains non-standard characters.

But SQLite tries to be flexible and accommodating.  To this end, it  
accepts some non-standard quoting mechanisms:

1.  Names can be enclosed in [...] for compatibility with Access  
and SQLServer.
2.  Names can be enclosed in grave accents for compatibility with  
MySQL.
3.  Double-quoted names fall back to being string literals if  
there is no matching table or column name.

In retrospect, (3) seems to be a bad idea.  It is accident-prone and  
leads to all kinds of confusion.  For example, if double-quotes are  
being used correctly (which is to say to quote table or column names)  
but a misspelling occurs in the name, the token reverts to being a  
string literal rather than throwing an error.  Or if a double-quoted  
string really is being used as a string literal, but later a new  
column is added to a table that has the same name as the string text,  
the string literal will suddenly take on the value of the column.  It  
seems like we have one or two problem reports per month on this  
mailing list that involve people using double-quoted names where they  
should be using single-quoted string literals instead.

So I'm giving some thought to removing feature (3) above and  
disallowing double-quoted string literals.  My concern is, though,  
that this might break many existing applications.

What opinion do you, gentle users, have of this matter?

D. Richard Hipp
[EMAIL PROTECTED]



___
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] Loading a existing database 100% into memory

[Brown, Daniel]

What I am wanting to work out is how much memory SQLite uses if we have
the entire database in RAM only, the reason I'd like to find this out is
that there is some concern here about SQLite paging to disc at
inopportune moments (we're on an embedded system with a lot of other
random disc access going on) so ideally I'd like the option to be able
to run completely from RAM after the initial load from disc, so that if
paging proves too costly we can sacrifice RAM to escape the paging
issue.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen
Oberholtzer
Sent: Wednesday, August 06, 2008 5:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Loading a existing database 100% into memory

On Wed, Aug 6, 2008 at 6:26 PM, Brown, Daniel <[EMAIL PROTECTED]> wrote:

> Good afternoon list,
>
> I would like to load my current database file completely into memory,
> mostly as an experiment to check SQLite's maximum memory footprint,
> however searching through the documentation I can only find references
> about how to create new databases that are completely memory resident.
> Is there a way to do this?  I'm currently using the SQlite console
> application for my testing if that makes a difference.


What, exactly, is it you're after?  I can load a SQLite database 100%
into
memory quite quickly:

int fd = open("sqlitedb.dat");
struct stat info;
fstat(fd, );
char *buf = malloc(info.st_size);
read(fd, buf, info.st_size);

I find it extremely unlikely that this is what you want (although it
might
be an interesting academic exercise to make a VFS port of SQLite that
uses
memory arrays for read/write ops.)

At the other end of the spectrum, you could just dump the entire
database on
disk and then insert all the data into a :memory: database.  However,
this
doesn't seem like it would be very useful, either.

This sounds like an XY problem.  What are you really trying to
accomplish?
What constraints are preventing you from simply using an on-disk
database?

-- 
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
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] Possible Error in 3.6.0?

[Shane Harrelson]

This was previously reported, and couldn't be reproduced.

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

Any help in recreating it would be appreciated.

-Shane


On 8/6/08, Peter Holmes <[EMAIL PROTECTED]> wrote:
>
> FWIW, on a Dell Inspiron 530 running Ubuntu 7.10 with Tcl8.4 installed,
>
> ../configure --with-tcl=/usr/lib/tcl8.4
> make
>
> works fine, but
>
> make fulltest
>
> generates the following:
>
> perm-memsubsys1.tcl-1.1...
> Expected: [1 {wrong # args: should be "sqlite3 HANDLE FILENAME
> ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN?"}]
>  Got: [1 {wrong # args: should be "really_sqlite3 HANDLE FILENAME
> ?-vfs VFSNAME? ?-readonly BOOLEAN? ?-create BOOLEAN? ?-nomutex BOOLEAN?"}]
>   .
>   .
>   .
> 1 errors out of 600511 tests
> Failures on these tests: perm-memsubsys1.tcl-1.1
> All memory allocations freed - no leaks
> Memory used:  now -8  max 911680
> Page-cache used:  now  0  max  0
> Page-cache overflow:  now  0  max 769024
> Scratch memory used:  now  0  max  0
> Scratch overflow: now  0  max   6936
> Maximum alloc size:   6936
> Maximum memory usage: 911680 bytes
> Current memory usage: -8 bytes
> Number of malloc()  : -1 calls
>
> If you need any more info, feel free to contact me.
>
>
> ___
> 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] Realtime Backup of Database

[Till Steinbach]

Just in case anybody else is interessted. I created triggers for my  
database that look like:

SELECT CASE
WHEN (SELECT redundance('') !=0)
THEN RAISE (ABORT, 'backup on redudant database was not successfull')
END;

My custom function redundance then executes the statements on the  
redundant database (by sending the statements over a socket to the  
second device) When it failes it returns something not equal 0 so the  
statement has no effect.

I think that is fine for my purpose.

So last question. Is it difficult to deploy my custom function  
(redundance()) with the shared library so that it is accessable from  
all applications including the PHP scripts?
Is there a tutorial for building the librarys with custom functions?
I bought the SQLite Guide Book, but found nothing yet.

Thanks for your support so far!
Till
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Clone SQLite databases

[csabi81]

I think I have DONE :)
I miss one more PRAGMA to copy, the schema_version, which creates the
conflict with Firefox, and results a corrupt database. Now everythings work
fine.
I have only one question: how can I query all the PRAGMAS? I am useing an
SQLite library in Delphi, I think that I should use Sqlite3_Prepare_v2, it
has a parameter Stmt:pointer; but I dont know how to work with this and
obtain for example: PRAGMA page_size; ---> 26

Again, thanks to everyone for help, and if someone can help me in this final
problem I will appreciate.


csabi81 wrote:
> 
> I have done again a test, its nice to work with you guys :)
> 
> So, I query all the PRAGMAs from the original database, and note them,
> temporary on a sheet of paper;
> then after dooing a .dump obtain the hole schema of the database, CREATE
> TABLE, INDEX, INSERT stc..., saved in a file. I append from the begining
> the PRAGMAS, and than create the datanase file based on this schema file.
> Good and bad news: the good one is that the size of the file is exactly
> the same as the original one, the bad news is that firefox3 still cannot
> work with the cloned database.
> Is there a priority on how to apply PRAGMAs? this is the only thing I can
> imagine that can affect the cloned database file. :(
> 
> Thanks to all
> 
> 
> 
> csabi81 wrote:
>> 
>> Thanks Steve, I think I am on the right way.
>> It is a difference between the page sizes of the two databases. I have
>> opened each database with Firefox SQLite Manager, and at the DB Settings
>> I have seen a lot of differences, such as page size, Cache size, max page
>> count etc. How can I Copy all these settings from the original DB to the
>> clone. Is there a safe way, or I can only copy one by one, by querying
>> and setting these Settings?
>> 
>> Thanks
>> 
>> 
>> Steve Kallenborn wrote:
>>> 
>>> Could there be a different page_size, resulting in differing amounts of 
>>> 'wasted' space?
>>> 
>>> Check with "PRAGMA page_size;" or sqlite3_analyzer program.
>>> 
>>> Thanks
>>>Steve
>>> 
>>> csabi81 wrote:
 I have made .dump for each database, original and clone, and saved into
 separated files. Than compare them.
 Differences are in INSERT INTO entries, in the clone database those
 entries
 are missing which I did not copy from the original. But the CREATE
 TABLE,
 CREATE INDEX, CREATE TRIGGER Commands are the same 100%
 
>>> ___
>>> 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/Clone-SQLite-databases-tp18827472p18869124.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] about sqlite database

[Igor Tandetnik]

"janard" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> In sqlite whether can we rotate content in database .
> That means i want to restirct my database to some size,if it exceeds
> that size it has to replace first entry.
> Can anybody help.Thanks in advance

If you are asking whether there's some magic setting that would do that 
for you automatically, then the answer is no. If you are asking whether 
you can implement such a scheme manually - of course you can. SQL lets 
you insert records, delete records and count records - this should be 
sufficient.
-- 
With best wishes,
Igor Tandetnik

With sufficient thrust, pigs fly just fine. However, this is not 
necessarily a good idea. It is hard to be sure where they are going to 
land, and it could be dangerous sitting under them as they fly 
overhead. -- RFC 1925 



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

Re: [sqlite] Loading a existing database 100% into memory

[Szomraky, Stefan]

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Brown, Daniel
> Sent: Thursday, August 07, 2008 12:27 AM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Loading a existing database 100% into memory
> 
> Good afternoon list,
> 
> I would like to load my current database file completely into 
> memory, mostly as an experiment to check SQLite's maximum 
> memory footprint, however searching through the documentation 
> I can only find references about how to create new databases 
> that are completely memory resident.
> Is there a way to do this?  I'm currently using the SQlite 
> console application for my testing if that makes a difference.
> 

What do you mean by loading it into memory?
If you want to dump the on-disk tables into memory and also want to
check the memory footprint used for caching try this:

Open the :memory: database and attach the on-disk database with
ATTACH filename.db AS filename

Then do a 
CREATE TABLE tableName AS SELECT * FROM filename.tableName
On each table in the file, thus creating an in-memory copy of the DB and
having done a select on each table (i.e. you'll see how much cache in
memory will be used, etc.)

You can enumerate all tables in a your on-disk-file in the mentioned
scenario by doing a "SELECT tbl_name FROM filename.sqlite_master WHERE
type = 'table'".

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

[sqlite] about sqlite database

[janard]

In sqlite whether can we rotate content in database .
That means i want to restirct my database to some size,if it exceeds that
size it has to replace first entry.
Can anybody help.Thanks in advance

-- 
View this message in context: 
http://www.nabble.com/about-sqlite-database-tp18865116p18865116.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