Re: [sqlite] How do I add primary key on existing table?

[Mohd Radzi Ibrahim]

On 21-Jan-2008, at 3:12 PM, Dan wrote:



On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote:


Hi,

I was trying to add a primary key to existing table but could not.
"alter table custsales add constraint pk_custsales primary key (id,  
type)"


Is this supported? Is there a different syntax to do this?


Using ALTER TABLE to add a constraint or a new column marked as
PRIMARY KEY is not supported. You can get pretty much the same
effect by doing:

 CREATE UNIQUE INDEX custsales_i ON custsales(id, type);

Dan.


Thanks for the suggestion. Will it still work for the REPLACE command  
to identify indentical record?


--radzi--

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

Re: [sqlite] How do I add primary key on existing table?

[Dan]

On Jan 21, 2008, at 1:24 PM, Mohd Radzi Ibrahim wrote:


Hi,

I was trying to add a primary key to existing table but could not.
"alter table custsales add constraint pk_custsales primary key (id,  
type)"


Is this supported? Is there a different syntax to do this?


Using ALTER TABLE to add a constraint or a new column marked as
PRIMARY KEY is not supported. You can get pretty much the same
effect by doing:

  CREATE UNIQUE INDEX custsales_i ON custsales(id, type);

Dan.


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

[sqlite] How do I add primary key on existing table?

[Mohd Radzi Ibrahim]

Hi,

I was trying to add a primary key to existing table but could not. 


"alter table custsales add constraint pk_custsales primary key (id, type)"

Is this supported? Is there a different syntax to do this?


best regards,
Radzi.


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

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

I don't recall asking for a vote on the subject or asking anyone to agree with 
me, but thank you for your input anyway Jay. 
 
And I do appreciate Dr H. for adding the "COLLATE RTRIM" declaration!
 
Regards,
 
- Jeff



From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: Sun 1/20/2008 11:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQLite character comparisons



On Jan 20, 2008 10:19 AM, Fowler, Jeff <[EMAIL PROTECTED]> wrote:
>
> Not trying to be antagonistic, but I'm curious to know how many of you agree 
> with Darren's sentiments on this issue.

Sqlite is DRH's project. Voting doesn't come into the picture
anywhere. If you convince him
to fix it then he might, if not you're wasting your time asking if
people agree with you.

>Sure - we can handle this situation by writing more code looking for
spaces everywhere they might occur.

The source for the database engine is provided. It would be more
efficient to change it there.
You could also provide the code back to the community as an option for
others to use.

I'm not trying to be unpleasant, I just believe where you're headed
isn't going to be as valuable as other options.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com  

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




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

Re: [sqlite] sql optimization question

[Andy Goth]

On Sun, 20 Jan 2008 19:16:03 -0800 (PST), Ken wrote
> Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>> I'm deleting a tree of data stored in sqlite and was looking for the
>> most efficient way to do it.
>
> You'll need one trigger per table to cause the delete to cascade
> through the tree.

Watch out.  SQLite doesn't support recursive triggers, so the following
won't work.  At least, it didn't work for me. :^)

SQLite version 3.5.4
Enter ".help" for instructions
sqlite> create table x (id, parent);
sqlite> create trigger deltree after delete on x begin
   ...> delete from x where parent = old.id;
   ...> end;
sqlite> insert into x values(0, null);
sqlite> insert into x values(1, 0);
sqlite> insert into x values(2, 0);
sqlite> insert into x values(3, 1);
sqlite> delete from x where id = 0;
sqlite> select * from x;
3|1
sqlite> 

In response to the deletion of (0,null), the trigger fired, deleting
(1,0), and (2,0).  But the trigger didn't fire again in response to
either of these subsequent deletions, so (3,1) was not automatically
deleted.

If anyone knows how to get around this problem, I would like to know.

-- 
Andy Goth
<[EMAIL PROTECTED]>

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

Re: [sqlite] sql optimization question

[Ken]

Jay 
   
  I've used a trigger to do this with good success.
   
  You'll need one trigger per table to cause the delete to cascade through the 
tree.
   
  Or if you know that you will always delete from the top level tree"parent" 
then just one trigger would probably suffice.
   
  Ken

Jay Sprenkle <[EMAIL PROTECTED]> wrote:
  I'm deleting a tree of data stored in sqlite and was looking for the
most efficient way to do it.
I thought the best solution was to delete the row and then delete all
the orphaned rows the referenced it in a loop

delete from category where id = 5;

To delete the orphaned rows I repeat one of the following until
nothing more is deleted.
Which of these two forms would run faster?

delete from category where ParentId not in ( select distinct id from category );

or

delete from category A
left join category B on b.id = a.parent.id
where b.id is null;

Thanks


-- 
--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

RE: [sqlite] SQLite character comparisons

[Darren Duncan]

At 11:19 AM -0500 1/20/08, Fowler, Jeff wrote:
To restate briefly, ANSI SQL-92 specifies that when comparing two 
character fields, trailing spaces should be ignored. Correct me if 
I'm wrong Darren, but you feel this is a bad decision, and in fact 
SQLite's implementation of character comparison (respecting trailing 
spaces) is superior to ANSI's specs.


Yes, that is indeed what I am saying.

More broadly speaking, and this may already be familiar to some of 
you who remember several of my writings over the last few years, I 
believe that while SQL has a lot of good things going for it, it also 
has numerous flaws, some of which are quite severe in their 
consequences.  I am specifically addressing the ANSI/ISO SQL standard 
itself with this blame, not any implementation in particular.


I make this assessment of SQL both in respect to how much SQL is able 
to represent the relational model of data that Codd proposed to be 
used for computer databases, and in respect to how much SQL is 
constructed according to well-established principles of good language 
design.


As far as I am concerned, any quasi-implementation of SQL that 
addresses these flaws is something to applaud.


And at times that it seems SQLite is already doing things a better 
way, I am inclined to argue in support of its current status quo.


I won't address/re-address the other perceived SQL flaws in this 
thread, to stay on topic, but I'll further clarify my position on the 
space-pad thing in light of the previous paragraphs.  It may even 
appear that I changed or reversed my position, but I don't feel that 
it changed.


1.  The most important thing to have in regards to data types and 
values is to have a fully deterministic (and preferrably simple) 
concept of value identity, that is, when 2 containers are considered 
to hold identical values or not, or should I say, when 2 appearances 
of values are in fact the same one value.


2.  The same conceptual value can have multiple physical 
representations, but this distinction is meant to be abstracted away 
from the user, so for example if the definition of a data type says 
that the representations 2.0 and 2.00 are the same value, then an 
equality test on them should return true; that said, users should not 
even see the difference then; any display of either physical 
representation to the user should be normalized to the same thing, 
such as 2, so when 2 values are considered equal by the system, they 
look the same to the user, but it is still okay to store them 
differently behind the scenes.


3.  It is okay in the general case for a system's conception of value 
identity to be different than another system's as long as the rules 
are clearly documented.  In this respect, it is okay for either 
trailing spaces to be significant, or for them to be non-significant, 
for determining identity (and by extension, equality), as long as 
these rules are consistently applied everywhere that value appears. 
Eg, 2 given character strings Foo and Bar can't be considered 
identical in some contexts and non-identical in other contexts.  If 
you want to have it both ways, you need to have 2 distinct data types 
which happen to look similar, eg a CharStrSpSignif data type and a 
CharStrSpInsig data type, and then you use values of one type in one 
context and separate values of the other type in other contexts.


4.  In this respect, if I don't misunderstand, SQLite's text data 
type is the CharStrSpSignif data type, and the SQL standard has the 
CharStrSpInsig type instead; if you consider the 2 systems as having 
different data types, then this difference of behaviour is 
explainable.  Moreover, you can have your choice of the behaviour in 
different systems by having both types implemented there to choose 
from when you want, like you can choose between text and number types 
now.


5.  A more practical example of #2, ignoring the whole spaces thing, 
is in regard to Unicode codepoints vs graphemes.  Even if you are 
using a consistent byte encoding throughout, such as just UTF-8 or 
UTF-16-LE, you still have to be concerned with the fact that Unicode 
has multiple normal forms.  Depending on your normal form, such as 
normal form C vs normal form D, you may have different sequences of 
code points representing the same grapheme.  An example of a single 
grapheme being the combination of a plain roman letter plus a 
diacritical mark or accent; in NFC, that may be a single codepoint, 
in NFD, it might be a sequence of 2 code points.  So, it is important 
for a character string data type to explicitly be considered either 
as a string of code points or of graphemes, for example.  At the 
higher level abstraction, the 2 forms of letter+accent would be 
considered identical, but in the lower level abstraction, they would 
be non-identical.  Note that afaik most high-level Unicode systems 
normally work in the highest abstraction level possible (whether they 
synchronize the normal form on st

Re: [sqlite] SQLite character comparisons

[drh]

"Fowler, Jeff" <[EMAIL PROTECTED]> wrote:
> I'm curious to know how many of you agree with Darren's 
> sentiments on this issue

Changing the behavior of SQLite to ignore trailing 
spaces is not an option for SQLite version 3, since
to do so would result in a incompatible file format
All indices created before the change would be invalid 
since they would use a different collation.  There 
are multiple thousands of SQLite applications and
hundreds of millions of existing SQLite database files
that depend on this backwards compatibility.  To make 
this change would therefore require bumping the version 
number up to SQLite 4.0.

>  
> Our app creates SQLite tables dynamically based on the output
> from user-defined queries that run against data warehouses 
> (of practically any "flavor") we have no control over, and
> we insert the results into SQLite. Sure - we can handle this
> situation by writing more code looking for spaces everywhere
> they might occur. But to me (and maybe only to me?), it makes
> sense for SQLite -- where reasonably possible -- to attempt
> to follow clear ANSI guidelines
>  

Check-in [4732] implements a built-in RTRIM collating sequence
that provides the ignore-spaces comparison semantics that you
desire.  All you have to do is add "COLLATE RTRIM" to the declarations
of text columns in your SQLite schema and SQLite will thereafter
ignore trailing spaces on comparisons involving those columns.
If you do SQL comparisons that do not involve columns, you can
put "COLLATE RTRIM" after the comparison operator itself to get
this behavior.  Example (an actual screen capture):

[EMAIL PROTECTED]:~/sqlite/bld> ./sqlite3
SQLite version 3.5.4
Enter ".help" for instructions
sqlite> SELECT 'abc'='abc   ';
0
sqlite> SELECT 'abc'='abc   ' COLLATE RTRIM;
1

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



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

Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

[Alexander Batyrshin]

On Jan 20, 2008 11:32 PM,  <[EMAIL PROTECTED]> wrote:
> "Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> > Hello everyone.
> > I've discover performance degradation due to update 3.3.17 -> 3.5.4.
> > This SQL query work very slowly:
> >
> > DELETE FROM
> >   population_stamp
> > WHERE
> >   town_id IN (
> > SELECT DISTINCT town_id FROM population_stamp
> > EXCEPT
> > SELECT id FROM town
> >   );
> >
>
> I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if
> you omit the DISTINCT operator.  EXCEPT implies DISTINCT anyway,
> so you are not losing anything.  But SQLite does not optimize
> out redundant DISTINCTs so it is computing the DISTINCT twice.

It's faster only for 3.5.4, but still slowly for 3.3.17 (time in
seconds: us - user, sy - system)
3.5.4  +DISTINCT: 5.474us 0.287sy
3.5.4  -DISTINCT: 3.397us 0.259sy
3.3.17 -DISTINCT: 4.129us 0.228sy
3.3.17 +DISTINCT: 2.959us 0.180sy

These is timing example for my small testing database. With real
database difference will be more visible.

-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

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

Re: [sqlite] SQLite character comparisons

[John Stanton]

I would suggest that Sqlite is correct having regard to its universal 
variable length feature and single TEXT type.  Compatibility would be 
achieved by not storing redundant trailing spaces if a string field is 
required.  More efficient storage would be a bonus.


Fowler, Jeff wrote:

Hello All,
 
Not trying to be antagonistic, but I'm curious to know how many of you agree with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 specifies that when comparing two character fields, trailing spaces should be ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, and in fact SQLite's implementation of character comparison (respecting trailing spaces) is superior to ANSI's specs. Keep in mind this is not some obscure issue that can be subject to different interpretations by different vendors; it's very clearly stated: "The ANSI standard requires padding for the character strings used in comparisons so that their lengths match before comparing them."
 
Does anyone know of another RDBMS (ANSI or no) that respects trailing spaces when comparing character data? We have both Oracle 10g and SQL Server 2005 in house and they both work "correctly" according to the specification. Has anyone tried it with DB2 or Informix? What about PostGres and MySQL? Although I haven't asked him, I'm guessing Zbigniew's suggestion a while back for an auto-trim feature stemmed from this issue. Other than saving space, would there be a need to trim data if WHERE, HAVING clauses and joins followed the spec? Also, other than performance (which seems to be the primary concern), would anyone would be negatively impacted if the current behavior were changed?
 
Our app creates SQLite tables dynamically based on the output from user-defined queries that run against data warehouses (of practically any "flavor") we have no control over, and we insert the results into SQLite. Sure - we can handle this situation by writing more code looking for spaces everywhere they might occur. But to me (and maybe only to me?), it makes sense for SQLite -- where reasonably possible -- to attempt to follow clear ANSI guidelines, allowing developers to override it only in cases where this adherence produces undesirable results. I can't see where this is undesirable from an applications standpoint, although I can see where performance may be a concern.
 
Regards,
 
Jeff Fowler
 



From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Fri 1/18/2008 4:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite character comparisons



At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:

"Better" depends on who you ask - I'd say it's worse, and I bet most
DBA's would agree. The ANSI standard is to ignore trailing spaces when
comparing character strings in a WHERE clause, a HAVING clause, or a
join. So I can take the exact same data, run the exact same query, yet
get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
found this issue because we DID get a different answer.


And every SQL implementation already has many differences from every
other one, even with fundamentals, making them all generally
incompatible and proprietary, and this example is right in line with
the kinds of differences they have.  Other implementations have
distinct var-char and fixed-char types, while SQLite only has the
former.

Part of the problem here is that the ANSI standard is itself flawed.

(Maybe this particular case of trailing spaces is an exception, but
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of
SQL is, and they say that the semantics, even of some fundamental
operations, is left up to the implementation to decide for itself.
So what good does it do you if SQL of the same syntax will compile on
different DBMSs if it behaves differently in each one?  And the
standard considers this valid.)

How many other programming langauges besides ANSI SQL treat trailing
spaces as insignificant.


Regarding whether by extension it should be impossible to create strings
with trailing spaces; I side with the SQLite developers who say it isn't
the engine's job to trim blanks in data. Most other engines I've used do
not trim spaces either, even if the field is a varchar.


And rightly so, you should not trim spaces, because spaces are significant.

Herein lies a wider part of the problem.  The ANSI SQL is
inconsistent in how it treats trailing spaces in strings.  On one
hand it wants to preserve them, but on the other hand it wants to
ignore them in its most fundamental operation other than preserving.

(With my "it should not be possible" sentence, I was not saying that
spaces should not be trimmed in the fictional scenario where a
character string does by definition not contain trailing spaces, but
that code specifying them should produce an error rather than
succeed.  The matter is analagous to what would happen if you write
code that tries to treat the character string literal 'foo' 

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[John Stanton]

SELECT is read only.  Think of an Sqlite lock as a lock on the journal 
file.  The critical action of an Sqlite insert or update is to transfer 
the contents of the journal file to disk and verify that the write to 
disk has completed (the ACID feature).  Apart from that it is read only.


Somewhere I have code for an efficient read lock for Windows.  A write 
lock is a critical section or a mutex  I can seek it out if it would 
help you.


If you have a general purpose RPC server you probably need to parse the 
SQL to a certain degree to establish read or write ststua or have the 
RPC announce whether is it is a read or write


Skilfully implemented your Sqlite-based RPC server will have the 
capabilities of a regular RDBMS server but with the advantage that it 
can be seeded with other functions.  For example we embed Sqlite inside 
an RPC server which sits inside an HTTP web server which also embeds an 
SSL capability and a web page generator also embedding Sqlite.  Sqlite 
is the silver bullet which removes the need to have inefficient IPCs and 
CGI-like process creation and destruction.  AJAX in one process is 
realized, thanks to Sqlite.  Sendfile/TransmitFile can be used to full 
advantage.


In such a system careful partitioning into separate databases minimizes 
contentions.


[EMAIL PROTECTED] wrote:

I'd like to use reader-writer lock in the client code, but at this point
I'm not sure if I can determine at which point SQLite is not writing.

I mean, INSERT / UPDATE are most likely need a writer lock, but I
don't know if SELECT is guaranteed to be read-only in its internal
operation within SQLite when I set SQLITE_THREADSAFE=0.

Implementing an efficient RW lock on Windows XP is another challenge
anyway.

-- sword

On Sat, 19 Jan 2008 22:56:43 +0100
Jens Miltner <[EMAIL PROTECTED]> wrote:


Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]:


OK I figured out SQLITE_THREADSAFE=0 for the second question...
And it seems the answer for the first question is yes, but if you know
a simpler way please share it with us, thanks!
You could use a read-write mutex to serialize access to your database  
connection. That way you can have multiple readers, but modifying the  
database becomes an exclusive operation. This matches the sqlite  
requirements.
Alternatively, you can just retry your write queries if you get  
SQLITE_BUSY errors...




-- sword

On Sat, 19 Jan 2008 09:57:10 +0900
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:


Hello all,

I've read http://www.sqlite.org/lockingv3.html but am still not  
sure about

multithread and locking in 3.5.4.

I have a multithread application that has a single connection to a  
single
SQLite3 database. Since it's multithreaded, SQL statements are  
thrown to

a single SQLite3 object concurrently. I'm using
http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
on VC8 + WindowsXP.

Prior to this version (I was using SQLite2) I'd serialized all  
these database access
using critical sections and didn't care about SQLITE_BUSY or  
SQLITE_LOCKED
since they never happen. It was very simple as I didn't need to  
implement access

retry for a busy case.

However, I learned that SQLite 3.5 does mutexing by default. So I  
removed

all synchronization stuff in my SQLite access code, and now it seems
it's not working as I intended. Unfortunately I can't reproduce it  
in my

development environment and I've not yet implemented logging to see
if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
sqlite3_mutex_enter multiple times in the debugger though, so it's  
thread-safe

at least.

My question is,

1. Do I still have to synchronize all SQLite access in my client  
code not to

encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)

2. If so, how can I turn off all these mutexes (critical sections)  
in SQLite 3.5.4?
They are needless if I serialize all SQLite access in the client  
code.


Regards,

-- sword



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



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



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




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




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

Re: [sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

[drh]

"Alexander Batyrshin" <[EMAIL PROTECTED]> wrote:
> Hello everyone.
> I've discover performance degradation due to update 3.3.17 -> 3.5.4.
> This SQL query work very slowly:
> 
> DELETE FROM
>   population_stamp
> WHERE
>   town_id IN (
> SELECT DISTINCT town_id FROM population_stamp
> EXCEPT
> SELECT id FROM town
>   );
> 

I'll bet it will go a lot faster in both 3.3.17 and 3.5.4 if
you omit the DISTINCT operator.  EXCEPT implies DISTINCT anyway,
so you are not losing anything.  But SQLite does not optimize
out redundant DISTINCTs so it is computing the DISTINCT twice.

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


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

Re: [sqlite] SQLite character comparisons

[Jay Sprenkle]

On Jan 20, 2008 10:19 AM, Fowler, Jeff <[EMAIL PROTECTED]> wrote:
>
> Not trying to be antagonistic, but I'm curious to know how many of you agree 
> with Darren's sentiments on this issue.

Sqlite is DRH's project. Voting doesn't come into the picture
anywhere. If you convince him
to fix it then he might, if not you're wasting your time asking if
people agree with you.

>Sure - we can handle this situation by writing more code looking for
spaces everywhere they might occur.

The source for the database engine is provided. It would be more
efficient to change it there.
You could also provide the code back to the community as an option for
others to use.

I'm not trying to be unpleasant, I just believe where you're headed
isn't going to be as valuable as other options.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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

Re: [sqlite] SQLite character comparisons

[Doug Currie]

On Sunday, January 20, 2008 Fowler, Jeff wrote: 

> briefly, ANSI SQL-92 specifies that when comparing two character
> fields, trailing spaces should be ignored.

From SQL-92 (draft July 1992) section 4.6

> When values of unequal length are compared, if the collating
> sequence for the comparison has the NO PAD attribute and the shorter
> value is equal to a prefix of the longer value, then the shorter
> value is considered less than the longer value. If the collating
> sequence for the comparison has the PAD SPACE attribute, for the
> purposes of the comparison, the shorter value is effectively
> extended to the length of the longer by concatenation of s on
> the right.

section 8.2 also says

> 3) The comparison of two character strings is determined as fol-
>lows:
> 
>a) If the length in characters of X is not equal to the length
>  in characters of Y, then the shorter string is effectively
>  replaced, for the purposes of comparison, with a copy of
>  itself that has been extended to the length of the longer
>  string by concatenation on the right of one or more pad char-
>  acters, where the pad character is chosen based on CS. If
>  CS has the NO PAD attribute, then the pad character is an
>  implementation-dependent character different from any char-
>  acter in the character set of X and Y that collates less
>  than any string under CS. Otherwise, the pad character is a
>  .

So, using this terminology, the SQLite default collating sequence has
the NO PAD attribute, and the pad character is NUL.

Jeff, can you solve your problem with a custom collating sequence?

e

-- 
Doug Currie
Londonderry, NH, USA


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

RE: [sqlite] SQLite character comparisons

[Fowler, Jeff]

Hello All,
 
Not trying to be antagonistic, but I'm curious to know how many of you agree 
with Darren's sentiments on this issue. To restate briefly, ANSI SQL-92 
specifies that when comparing two character fields, trailing spaces should be 
ignored. Correct me if I'm wrong Darren, but you feel this is a bad decision, 
and in fact SQLite's implementation of character comparison (respecting 
trailing spaces) is superior to ANSI's specs. Keep in mind this is not some 
obscure issue that can be subject to different interpretations by different 
vendors; it's very clearly stated: "The ANSI standard requires padding for the 
character strings used in comparisons so that their lengths match before 
comparing them."
 
Does anyone know of another RDBMS (ANSI or no) that respects trailing spaces 
when comparing character data? We have both Oracle 10g and SQL Server 2005 in 
house and they both work "correctly" according to the specification. Has anyone 
tried it with DB2 or Informix? What about PostGres and MySQL? Although I 
haven't asked him, I'm guessing Zbigniew's suggestion a while back for an 
auto-trim feature stemmed from this issue. Other than saving space, would there 
be a need to trim data if WHERE, HAVING clauses and joins followed the spec? 
Also, other than performance (which seems to be the primary concern), would 
anyone would be negatively impacted if the current behavior were changed?
 
Our app creates SQLite tables dynamically based on the output from user-defined 
queries that run against data warehouses (of practically any "flavor") we have 
no control over, and we insert the results into SQLite. Sure - we can handle 
this situation by writing more code looking for spaces everywhere they might 
occur. But to me (and maybe only to me?), it makes sense for SQLite -- where 
reasonably possible -- to attempt to follow clear ANSI guidelines, allowing 
developers to override it only in cases where this adherence produces 
undesirable results. I can't see where this is undesirable from an applications 
standpoint, although I can see where performance may be a concern.
 
Regards,
 
Jeff Fowler
 


From: Darren Duncan [mailto:[EMAIL PROTECTED]
Sent: Fri 1/18/2008 4:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] SQLite character comparisons



At 4:11 PM -0500 1/18/08, Fowler, Jeff wrote:
>"Better" depends on who you ask - I'd say it's worse, and I bet most
>DBA's would agree. The ANSI standard is to ignore trailing spaces when
>comparing character strings in a WHERE clause, a HAVING clause, or a
>join. So I can take the exact same data, run the exact same query, yet
>get a different answer from SQLite vs. Oracle or SQL Server. In fact, we
>found this issue because we DID get a different answer.

And every SQL implementation already has many differences from every
other one, even with fundamentals, making them all generally
incompatible and proprietary, and this example is right in line with
the kinds of differences they have.  Other implementations have
distinct var-char and fixed-char types, while SQLite only has the
former.

Part of the problem here is that the ANSI standard is itself flawed.

(Maybe this particular case of trailing spaces is an exception, but
many parts of ANSI SQL, AFAIK, are more focused on what the syntax of
SQL is, and they say that the semantics, even of some fundamental
operations, is left up to the implementation to decide for itself.
So what good does it do you if SQL of the same syntax will compile on
different DBMSs if it behaves differently in each one?  And the
standard considers this valid.)

How many other programming langauges besides ANSI SQL treat trailing
spaces as insignificant.

>Regarding whether by extension it should be impossible to create strings
>with trailing spaces; I side with the SQLite developers who say it isn't
>the engine's job to trim blanks in data. Most other engines I've used do
>not trim spaces either, even if the field is a varchar.

And rightly so, you should not trim spaces, because spaces are significant.

Herein lies a wider part of the problem.  The ANSI SQL is
inconsistent in how it treats trailing spaces in strings.  On one
hand it wants to preserve them, but on the other hand it wants to
ignore them in its most fundamental operation other than preserving.

(With my "it should not be possible" sentence, I was not saying that
spaces should not be trimmed in the fictional scenario where a
character string does by definition not contain trailing spaces, but
that code specifying them should produce an error rather than
succeed.  The matter is analagous to what would happen if you write
code that tries to treat the character string literal 'foo' as a
number.)

>But - whether ANSI compliance is considered "bloat" is not really my
>place to comment upon.  I guess it's really is up to the SQLite team.

Yes it is up to the developers.  And they have already demonstrated
willingness to do some things diff

[sqlite] Re: Performance degradation after 3.3.17 -> 3.5.4 upgrade

[Alexander Batyrshin]

On Jan 20, 2008 3:32 PM, Alexander Batyrshin <[EMAIL PROTECTED]> wrote:
>  Hello everyone.
> I've discover performance degradation due to update 3.3.17 -> 3.5.4.
> This SQL query work very slowly:
>
> DELETE FROM
>   population_stamp
> WHERE
>   town_id IN (
> SELECT DISTINCT town_id FROM population_stamp
> EXCEPT
> SELECT id FROM town
>   );
>
> Some info about tables:
>
> CREATE TABLE population_stamp(
> population int,
> town_id int,
> stamp_id int
> );
> CREATE INDEX popstamp_idx on population_stamp(stamp_id, 
> town_id);


Some new info.
Exactly this query work very-very slowly:
SELECT DISTINCT town_id FROM population_stamp;
3.5.4 explain:
addr  opcode  p1  p2  p3
  --  --  --  -
0 OpenEphemeral   1   2   keyinfo(1,BINARY)
1 MemInt  0   2
2 MemInt  0   1
3 Goto0   13
4 MemInt  1   2
5 Return  0   0
6 IfMemPos1   8
7 Return  0   0
8 MemLoad 0   0
9 Callback1   0
10Return  0   0
11MemNull 0   0
12Return  0   0
13Gosub   0   11
14Goto0   41
15Integer 0   0
16OpenRead0   18
17SetNumColumns   0   2
18Rewind  0   25
19StackDepth  -1  0
20Column  0   1
21Sequence1   0
22MakeRecord  2   0
23IdxInsert   1   0
24Next0   19
25Close   0   0
26Sort1   40
27Column  1   0
28MemStore4   0
29MemLoad 3   0
30Eq  512 35  collseq(BINARY)
31MemMove 3   4
32Gosub   0   6
33IfMemPos2   40
34Gosub   0   11
35Column  1   0
36MemStore0   1
37MemInt  1   1
38Next1   27
39Gosub   0   6
40Halt0   0
41Transaction 0   0
42VerifyCookie0   1619
43TableLock   0   18  population_stamp
44Goto0   15
45Noop0   0


3.3.17 explain:
explain SELECT DISTINCT town_id FROM population_stamp;
addr  opcode  p1  p2  p3
  --  --  --  -
0 OpenEphemeral   1   0   keyinfo(1,BINARY)
1 Goto0   16
2 Integer 0   0
3 OpenRead0   18
4 SetNumColumns   0   2
5 Rewind  0   14
6 Column  0   1
7 MakeRecord  -1  0
8 Distinct1   11
9 Pop 2   0
10Goto0   13
11IdxInsert   1   0
12Callback1   0
13Next0   6
14Close   0   0
15Halt0   0
16Transaction 0   0
17VerifyCookie0   1619
18Goto0   2
19Noop0   0



-- 
Alexander Batyrshin aka bash
bash = Biomechanica Artificial Sabotage Humanoid

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

[sqlite] Performance degradation after 3.3.17 -> 3.5.4 upgrade

[Alexander Batyrshin]

 Hello everyone.
I've discover performance degradation due to update 3.3.17 -> 3.5.4.
This SQL query work very slowly:

DELETE FROM
  population_stamp
WHERE
  town_id IN (
SELECT DISTINCT town_id FROM population_stamp
EXCEPT
SELECT id FROM town
  );

Some info about tables:

CREATE TABLE population_stamp(
population int,
town_id int,
stamp_id int
);
CREATE INDEX popstamp_idx on population_stamp(stamp_id, 
town_id);


CREATE TABLE town(
id int PRIMARY KEY,
lochash int,
x int, y int,
name char(32),
player_id int,
population int
);
CREATE INDEX town_xy ON town(x,y);
CREATE INDEX town_uid ON town(player_id);
CREATE INDEX town_name ON town(name);

Explain from 3.5.4:
sqlite> explain
   ...> DELETE FROM
   ...>   population_stamp
   ...> WHERE
   ...>   town_id IN (
   ...> SELECT DISTINCT town_id FROM population_stamp
   ...> EXCEPT
   ...> SELECT id FROM town
   ...>   );
addr  opcode  p1  p2  p3
  --  --  --  -
0 Goto0   104
1 Integer 0   0
2 OpenRead0   18
3 SetNumColumns   0   2
4 Rewind  0   85
5 StackDepth  -1  0
6 MemLoad 0   0
7 If  0   72
8 MemInt  1   0
9 OpenEphemeral   4   0   keyinfo(1,BINARY)
10SetNumColumns   4   1
11OpenEphemeral   5   1   keyinfo(1,BINARY)
12OpenEphemeral   6   2   keyinfo(1,BINARY)
13MemInt  0   3
14MemInt  0   2
15Goto0   26
16MemInt  1   3
17Return  0   0
18IfMemPos2   20
19Return  0   0
20MemLoad 1   0
21MakeRecord  1   0
22IdxInsert   5   0
23Return  0   0
24MemNull 1   0
25Return  0   0
26Gosub   0   24
27Integer 0   0
28OpenRead2   18
29SetNumColumns   2   2
30Rewind  2   37
31StackDepth  -1  0
32Column  2   1
33Sequence6   0
34MakeRecord  2   0
35IdxInsert   6   0
36Next2   31
37Close   2   0
38Sort6   52
39Column  6   0
40MemStore5   0
41MemLoad 4   0
42Eq  512 47  collseq(BINARY)
43MemMove 4   5
44Gosub   0   18
45IfMemPos3   52
46Gosub   0   24
47Column  6   0
48MemStore1   1
49MemInt  1   2
50Next6   39
51Gosub   0   18
52Integer 0   0
53OpenRead1   8
54SetNumColumns   1   1
55Rewind  1   62
56StackDepth  -1  0
57Column  1   0
58MakeRecord  1   0
59NotFound5   61
60Delete  5   0
61Next1   56
62Close   1   0
63Rewind  5   71
64Column  5   0
65NotNull -1  68
66Pop 1   0
67Goto0   70
68MakeRecord  1   0   c
69IdxInsert   4   0
70Next5   64
71Close   5   0
72Integer 1   0
73Column  0   1
74NotNull -1  78
75Pop 2   0
76Null0   0
77Goto0   81
78MakeRecord  1   0   c
79Found   4   81
80AddImm  -1  0
81IfNot   1   84
82Rowid   0   0
83FifoWrite   0   0
84Next0   5
85Close   0   0
86Integer 0   0
87OpenWrite   0   18
88SetNumColumns   0   3
89Integer 0   0
90OpenWrite   1   19  keyinfo(2,BINARY,BINARY)
91FifoRead0   101
92StackDepth  -1  0
93Not

Re: [sqlite] Quick question about multithread and SQLITE_BUSY/SQLITE_LOCKED in 3.5.4

[[EMAIL PROTECTED]]

I'd like to use reader-writer lock in the client code, but at this point
I'm not sure if I can determine at which point SQLite is not writing.

I mean, INSERT / UPDATE are most likely need a writer lock, but I
don't know if SELECT is guaranteed to be read-only in its internal
operation within SQLite when I set SQLITE_THREADSAFE=0.

Implementing an efficient RW lock on Windows XP is another challenge
anyway.

-- sword

On Sat, 19 Jan 2008 22:56:43 +0100
Jens Miltner <[EMAIL PROTECTED]> wrote:

> 
> Am 19.1.08 um 03:13 schrieb [EMAIL PROTECTED]:
> 
> > OK I figured out SQLITE_THREADSAFE=0 for the second question...
> > And it seems the answer for the first question is yes, but if you know
> > a simpler way please share it with us, thanks!
> 
> You could use a read-write mutex to serialize access to your database  
> connection. That way you can have multiple readers, but modifying the  
> database becomes an exclusive operation. This matches the sqlite  
> requirements.
> Alternatively, you can just retry your write queries if you get  
> SQLITE_BUSY errors...
> 
> >
> >
> > -- sword
> >
> > On Sat, 19 Jan 2008 09:57:10 +0900
> > "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:
> >
> >> Hello all,
> >>
> >> I've read http://www.sqlite.org/lockingv3.html but am still not  
> >> sure about
> >> multithread and locking in 3.5.4.
> >>
> >> I have a multithread application that has a single connection to a  
> >> single
> >> SQLite3 database. Since it's multithreaded, SQL statements are  
> >> thrown to
> >> a single SQLite3 object concurrently. I'm using
> >> http://www.sqlite.org/sqlite-amalgamation-3_5_4.zip
> >> on VC8 + WindowsXP.
> >>
> >> Prior to this version (I was using SQLite2) I'd serialized all  
> >> these database access
> >> using critical sections and didn't care about SQLITE_BUSY or  
> >> SQLITE_LOCKED
> >> since they never happen. It was very simple as I didn't need to  
> >> implement access
> >> retry for a busy case.
> >>
> >> However, I learned that SQLite 3.5 does mutexing by default. So I  
> >> removed
> >> all synchronization stuff in my SQLite access code, and now it seems
> >> it's not working as I intended. Unfortunately I can't reproduce it  
> >> in my
> >> development environment and I've not yet implemented logging to see
> >> if it's due to SQLITE_BUSY or SQLITE_LOCKED. I saw it's entering
> >> sqlite3_mutex_enter multiple times in the debugger though, so it's  
> >> thread-safe
> >> at least.
> >>
> >> My question is,
> >>
> >> 1. Do I still have to synchronize all SQLite access in my client  
> >> code not to
> >> encounter SQLITE_BUSY or SQLITE_LOCKED? (Or is there any better way?)
> >>
> >> 2. If so, how can I turn off all these mutexes (critical sections)  
> >> in SQLite 3.5.4?
> >> They are needless if I serialize all SQLite access in the client  
> >> code.
> >>
> >> Regards,
> >>
> >> -- sword
> >>
> >>
> >>
> >> -
> >> To unsubscribe, send email to [EMAIL PROTECTED]
> >> -
> >
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -



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