Re: [sqlite] Does using e.g. LTRIM function remove collation?

[Hick Gunter]

I was just refuting the claim that "the WHERE clause converts 'a' to 'A' in the 
database." It doesn't. So either the "change" was caused by a copy-paste error 
when creating the mail or by actually runing a (different) query that produces 
it.

Anyway, in the WHERE clause, the arguments to '<' are 'ltrim(col)' and 'b'. 
Neither the function ltrim() nor the literal 'b' have a collation, so the 
comparison proceeds using BINARY. Which is as documented.

-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Dienstag, 11. November 2014 00:05
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?

On Mon, 10 Nov 2014 08:43:24 +
Hick Gunter  wrote:

> I get the following results for the second select:
>
> A
> B
> a (lowercase!!!)
>
> Are you sure you ran the exact query stated?

I didn't run it.  Your mail showed 3 uppercase letters:

> > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> > A
> > B
> > A

--jkl

>
> -Ursprüngliche Nachricht-
> Von: James K. Lowden [mailto:jklow...@schemamania.org]
> Gesendet: Samstag, 08. November 2014 01:52
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?
>
> On Thu, 6 Nov 2014 17:02:26 -0500
> Richard Hipp  wrote:
>
> > > sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a'); SELECT
> > > sqlite> * FROM test;
> > > b
> > > A
> > > B
> > > a
> ...
> > > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> > > A
> > > B
> > > A
> ...
> > Works as designed.  See
> > https://www.sqlite.org/datatype3.html#collation and in particular
> > the three rules under section 6.1.
>
> Aren't you overlooking the fact that the WHERE clause is changing not
> only which rows are selected, but the *values* of those rows?
>
> I don't understand how any WHERE clause can convert 'a' to 'A' in the
> database.  I'm unable to find any suggestion of such in the collation
> documentation.
>
> --jkl
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use
> of the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you
> have received this communication in error, please immediately notify
> the sender by return e-mail message and delete all copies of the
> original communication. Thank you for your cooperation.
>
>
> ___
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. 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] Does using e.g. LTRIM function remove collation?

[James K. Lowden]

On Mon, 10 Nov 2014 08:43:24 +
Hick Gunter  wrote:

> I get the following results for the second select:
> 
> A
> B
> a (lowercase!!!)
> 
> Are you sure you ran the exact query stated?

I didn't run it.  Your mail showed 3 uppercase letters: 

> > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> > A
> > B
> > A

--jkl

> 
> -Ursprüngliche Nachricht-
> Von: James K. Lowden [mailto:jklow...@schemamania.org]
> Gesendet: Samstag, 08. November 2014 01:52
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?
> 
> On Thu, 6 Nov 2014 17:02:26 -0500
> Richard Hipp  wrote:
> 
> > > sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a');
> > > sqlite> SELECT * FROM test;
> > > b
> > > A
> > > B
> > > a
> ...
> > > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> > > A
> > > B
> > > A
> ...
> > Works as designed.  See
> > https://www.sqlite.org/datatype3.html#collation and in particular
> > the three rules under section 6.1.
> 
> Aren't you overlooking the fact that the WHERE clause is changing not
> only which rows are selected, but the *values* of those rows?
> 
> I don't understand how any WHERE clause can convert 'a' to 'A' in the
> database.  I'm unable to find any suggestion of such in the collation
> documentation.
> 
> --jkl
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
> 
> This communication (including any attachments) is intended for the
> use of the intended recipient(s) only and may contain information
> that is confidential, privileged or legally protected. Any
> unauthorized use or dissemination of this communication is strictly
> prohibited. If you have received this communication in error, please
> immediately notify the sender by return e-mail message and delete all
> copies of the original communication. Thank you for your cooperation.
> 
> 
> ___
> 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] Locking errors on network

[Eduardo Morras]

On Mon, 10 Nov 2014 20:33:04 +0200
RSmith  wrote:

> 
> On 2014/11/10 20:22, Mike McWhinney wrote:
> > So SQLite shouldn't be used at all on a network?  Aren't there any
> > other provisions to handled the locking errors if/when they occur?
> 
> It is not about SQLite, it is about the Networking systems lying
> about whether a file is locked or not. No RDBMS can trust the
> network, but the client-server types do not care since they control
> the locking and do not depend on the OS / file status. SQLite however
> depends on it and as such cannot accurately (or timeously I should
> say) verify such status via a Network. On a local drive this is never
> a problem.
> 
> If you need Networking or User-control, please use a client-server
> type database.
> 
> There is one Client-Server implementation of SQLite (SQLightening I
> think) but it is neither free nor easy to convert to. You can write
> your own server too, but the best bet is using MySQL or PostGres in
> these cases.

You can create your own sqlite server (I did and use it, with nanomsg for 
client-server communication), it's medium-hard and for tiny hardware, near 
embedded, works.

A good file to start with, as I did, is in Sqlite repository, check 
http://www.sqlite.org/src/artifact/a2615049954cbb9cfb4a62e18e2f0616e4dc38fe 
a.k.a. src/test_server.c

But, as others aim and hit, you should use a real C/S RDBMS, my preference, 
PostgreSQL server.

HTH

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

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE


Sorry for the previous noise. Clicked send instead of expanding the quotes.

Anyway, I've given a little thought in the past to creating a VFS for
SQLite that actually communicates with a "server". That server would be a
replacement for the buggy network file system implementations that plague
SQLite for networked use. Of course, it would no longer be a zero
configuration completely embedded system, but it would be a fairly minimal
shim.

Of course, it would itself require debugging, so it's not like it would be
a magical solution. Still, if the SQLite "service" just exposed a virtual
block device with accurate locking, I could see it being a useful
complement.

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

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 19:38, Mike McWhinney  wrote: 

> I am using Sqlite.NET client in C#. How would I go about defining a sqlite
> busy timeout handler?

Is the API not documented somewhere (I don't know what Sqlite.NET client or C# 
are, so can't help)? Surely you must have some doc or how else do you use the 
API at the moment?


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

Re: [sqlite] Locking errors on network

[Scott Robison]

On Mon, Nov 10, 2014 at 12:56 PM,  wrote:

> On 2014-11-10 18:33, RSmith wrote:
> 
>
>> There is one Client-Server implementation of SQLite (SQLightening I
>> think) but it is neither free nor easy to convert to.
>>
>
> Doing some Googling, this looks like the thing:
>
>   http://sqlitening.com
>
> They don't seem to sell it any more (last version was released Dec
> 2012), though the support forums are still online.
>
>
>  You can write
>> your own server too, but the best bet is using MySQL or PostGres in
>> these cases.
>>
>
> Use PostgreSQL (www.postgresql.org). :)
>
> This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
> Richard Hipp:
>
>   SQLite: Protégé of PostgreSQL
>   https://www.youtube.com/watch?v=ZvmMzI0X7fE
>
> Just saying. ;)
>
> Regards and best wishes,
>
> Justin Clift
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Locking errors on network

[justin]

On 2014-11-10 18:33, RSmith wrote:


There is one Client-Server implementation of SQLite (SQLightening I
think) but it is neither free nor easy to convert to.


Doing some Googling, this looks like the thing:

  http://sqlitening.com

They don't seem to sell it any more (last version was released Dec
2012), though the support forums are still online.



You can write
your own server too, but the best bet is using MySQL or PostGres in
these cases.


Use PostgreSQL (www.postgresql.org). :)

This is kinda interesting btw.  Keynote speaker for PGCon 2014 was
Richard Hipp:

  SQLite: Protégé of PostgreSQL
  https://www.youtube.com/watch?v=ZvmMzI0X7fE

Just saying. ;)

Regards and best wishes,

Justin Clift

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

Re: [sqlite] Locking errors on network

[Mike McWhinney]

I am using Sqlite.NET client in C#. How would I go about defining a sqlite busy 
timeout handler?  


Thanks
Mike



On Monday, November 10, 2014 1:35 PM, Tim Streater  
wrote:
 


On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

  https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


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

Re: [sqlite] Locking errors on network

[Tim Streater]

On 10 Nov 2014 at 18:22, Mike McWhinney  wrote: 

> So SQLite shouldn't be used at all on a network?  Aren't there any other
> provisions to handled the locking errors if/when
> they occur?

You tried setting a timeout as pointed to here?

   https://www.sqlite.org/faq.html#q5

(not that this would avoid any potential corruption issue).


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

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 10:22 AM, Mike McWhinney wrote:
> So SQLite shouldn't be used at all on a network?  Aren't there any
> other provisions to handled the locking errors if/when they occur?

Network filesystems do not implement locking and other operations
*exactly* the same as for (most) local filesystems.  This is done due
to the protocols involved, race conditions, performance
considerations, latencies, and various other reasons.  You are seeing
the locking errors as a symptom of this.

If you use SQLite with a network then your data will eventually end up
corrupted.  Yes it is possible to sweep some stuff under the rug but
that does not mean corruption won't happen.  SQLite won't be able to
prevent it, and often may not detect it for a while.

This page describes how SQLite does locking as well as pointing to
some newer alternatives:

  https://www.sqlite.org/lockingv3.html

See also:

  https://www.sqlite.org/whentouse.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRhBm8ACgkQmOOfHg372QTcLgCfblMaFauIRgE83WOcF9z2M6BV
BMYAnRSP1KwC+69vb5fUMsGeGbdImHU1
=1mbq
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Simon Slavin]

On 10 Nov 2014, at 6:22pm, Mike McWhinney  wrote:

> So SQLite shouldn't be used at all on a network?

SQLite is not designed for hosting a database on a server for access by lots of 
different computers at the same time.  To do that efficiently you need a 
client/server design and SQLite doesn't have it.

> Aren't there any other provisions to handled the locking errors if/when
> they occur?

The problem is at the operating system and Network File System level.  The 
required support is often just too buggy to be usable.  See "Client/Server 
Applications" on this page:



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

Re: [sqlite] Locking errors on network

[RSmith]

On 2014/11/10 20:22, Mike McWhinney wrote:

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?


It is not about SQLite, it is about the Networking systems lying about whether a file is locked or not. No RDBMS can trust the 
network, but the client-server types do not care since they control the locking and do not depend on the OS / file status. SQLite 
however depends on it and as such cannot accurately (or timeously I should say) verify such status via a Network. On a local drive 
this is never a problem.


If you need Networking or User-control, please use a client-server type 
database.

There is one Client-Server implementation of SQLite (SQLightening I think) but it is neither free nor easy to convert to. You can 
write your own server too, but the best bet is using MySQL or PostGres in these cases.



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

Re: [sqlite] Locking errors on network

[Mike McWhinney]

So SQLite shouldn't be used at all on a network?  Aren't there any other 
provisions to handled the locking errors if/when
they occur?




On Monday, November 10, 2014 12:10 PM, Roger Binns  
wrote:
 


-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
ail_
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Locking errors on network

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/10/2014 09:41 AM, Mike McWhinney wrote:
> Please let know if there are any other solutions to this database
> locking problem as used on a network.

Yes.  Do not do it.  See the FAQ:

  https://www.sqlite.org/faq.html#q5

Roger

-BEGIN PGP SIGNATURE-
Version: GnuPG v1

iEYEARECAAYFAlRg/6wACgkQmOOfHg372QQAfgCeLCZ7I4uC/3p+bNSuGQN0uTUB
6LEAoLjp4/yJzVJSWzGDq7cam8pezRma
=jie1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Locking errors on network

[Mike McWhinney]

Hello,

I continue to have "database is locked errors" when running an application 
which accesses a SQLite dabase on a network.


I have tried many of the connection string options: pooling, changing default 
time out.


I just recently tried a block of code which does a BeginTransaction and Commit. 
Before I did not use this. However it does not seem to have an effect
on the locking problem.


  string sql;

sql = "UPDATE APPOINTMENTS SET " +
 // "PatientID=" + myPatientID + "," +
  "TimeStampIsSeenByMA='" + nowString + "'" +

  " WHERE (" +
  "PatientID=" + myPatientID + " AND Date=" + "'" +
  dateString + "'" + ")";

   
SQLiteCommand command = new SQLiteCommand(sql, OMConnection.sqConn);
int numRowsAffected = 0;
SQLiteTransaction tran;
tran = OMConnection.sqConn.BeginTransaction(false);
numRowsAffected = command.ExecuteNonQuery();
command.Dispose();
tran.Commit();


Please let know if there are any other solutions to this database locking 
problem as used on a network.

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

Re: [sqlite] SQLite where clause tree

[Ward Willats]

> On Nov 10, 2014, at 9:31 AM, Richard Hipp  wrote:
> 
> On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats 
> wrote:
> 
>> 
>>> On Nov 10, 2014, at 3:11 AM, Richard Hipp  wrote:
>>> 
>>> If you recompile the SQLite command-line shell (sqlite3.exe) using the
>>> -DSQLITE_ENABLE_SELECTTRACE option, then you can enter:
>>> 
>> 
>> If I do that,
>> 
>>   gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o
>> sqlite3 sqlite3.c shell.c
>> 
>> sqlite3DebugPrintf and sqlite3TreeViewSelect are undefined at link time.
>> 
>> Must other switches be thrown?
>> 
> 
> Try adding -DSQLITE_DEBUG
> 

gcc -D SQLITE_DEBUG -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline 
-o sqlite3 sqlite3.c shell.c

Yes, that does it. Thanks.

-- Ward

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

Re: [sqlite] SQLite where clause tree

[Richard Hipp]

On Mon, Nov 10, 2014 at 12:28 PM, Ward Willats 
wrote:

>
> > On Nov 10, 2014, at 3:11 AM, Richard Hipp  wrote:
> >
> > If you recompile the SQLite command-line shell (sqlite3.exe) using the
> > -DSQLITE_ENABLE_SELECTTRACE option, then you can enter:
> >
>
> If I do that,
>
>gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o
> sqlite3 sqlite3.c shell.c
>
> sqlite3DebugPrintf and sqlite3TreeViewSelect are undefined at link time.
>
> Must other switches be thrown?
>

Try adding -DSQLITE_DEBUG



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



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

Re: [sqlite] SQLite where clause tree

[Ward Willats]

> On Nov 10, 2014, at 3:11 AM, Richard Hipp  wrote:
> 
> If you recompile the SQLite command-line shell (sqlite3.exe) using the
> -DSQLITE_ENABLE_SELECTTRACE option, then you can enter:
> 

If I do that, 

   gcc -D SQLITE_ENABLE_SELECTTRACE -D HAVE_READLINE -l readline -o sqlite3 
sqlite3.c shell.c

sqlite3DebugPrintf and sqlite3TreeViewSelect are undefined at link time.

Must other switches be thrown?

-- Ward



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

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Simon Slavin]

On 10 Nov 2014, at 4:45pm, Pavlo  wrote:

> That main problem is that we are working with managed code and .net wrapper
> packaged in System.Data.Sqlite :) 

I apologise that I missed this.  Fortunately we have several readers to the 
list who understand that wrapper very well and may be able to help.

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

Re: [sqlite] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Pavlo]

Thanks Simon for quick reply.

That main problem is that we are working with managed code and .net wrapper
packaged in System.Data.Sqlite :) 

Our Sqlite usage boils down to the following:
1. Fetch ready to use Sqlite database from server
2. Open one Sqlite connection
2. Working only with this one connection  on the device for some period of
time
3. After that we close a connection, delete db from storage
4. Fetch another database from server which may be completely different

All connections get closed correctly because we wait for them to complete
via .NET framework events. However virtual memory used by previous
connections seems not to reset.

Thanks, Pavlo.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, November 10, 2014 6:27 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Virtual memory management of Sqlite core on Windows
Mobile 6.5 with .NET CF 3.5


On 10 Nov 2014, at 3:45pm, Pavlo  wrote:

> Total Virtual memory allocations size made by Sqlite core seems to 
> grow in time in application process even though Sqlite connection get 
> closed on a regular basis.
> For example if we do search touching several tables in sqlite database 
> it seems like sqlite core uses memory-mapped IO to do its job done 
> before returning results of a query.
> There are of course other quires that touch almost all tables in the 
> database. It results in growing virtual memory footprint and
fragmentation.
> However it looks like Sqlite never releases that allocated virtual 
> memory space in application process even after we close the sqlite 
> connection and delete sqlite DB file from storage.
> So you imagine if for several days database changes and sqlite engine 
> uses more and more memory-mapped pages we will end-up with "Out-of-memory"
> situations.

The last point may not be true (unless, of course, you have demonstrated
it).  SQLite is designed to intelligently use whatever memory is available.
It can expand to fill a lot of memory, but then stop.

Some things to check:

Check the values returned from /all/ sqlite3_ calls, including ones which
finalize statements and close connections.  Anything that doesn't return
SQLITE_OK can sabotage the way future calls use memory. Tracking down a
missing _finalize() can completely change the memory footprint.

Secondly, from your above description I assume that your application doesn't
use SQLite all the time it's running.  I think you're describing something
that keeps a connection in use only part of the time.  If that's the case,
as a debugging aid you might try manually calling



sqlite3_initialize() and sqlite3_shutdown() in that code, then look at
memory usage after those calls.  This might magically fix your problem.  But
it might also allow you to figure out what your problem actually is.  If
SQLite is still using any significant resources after _shutdown(), something
is wrong.

> Is there any logic behind when sqlite engine releases VM memory?

It shouldn't do anything very unusual.  But SQLite will not close a
connection if a resource (e.g. an unfinalized statement) still exists for
the connection.  And that's the sort of thing that might trigger what you
are reporting.

Simon.

PS: Thanks for your detailed description of your setup and concerns, which
made it unnecessary to ask lots of questions before answering.
___
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] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Simon Slavin]

On 10 Nov 2014, at 3:45pm, Pavlo  wrote:

> Total Virtual memory allocations size made by Sqlite core seems to grow in
> time in application process even though Sqlite connection get closed on a
> regular basis.
> For example if we do search touching several tables in sqlite database it
> seems like sqlite core uses memory-mapped IO to do its job done before
> returning results of a query.
> There are of course other quires that touch almost all tables in the
> database. It results in growing virtual memory footprint and fragmentation.
> However it looks like Sqlite never releases that allocated virtual memory
> space in application process even after we close the sqlite connection and
> delete sqlite DB file from storage.
> So you imagine if for several days database changes and sqlite engine uses
> more and more memory-mapped pages we will end-up with "Out-of-memory"
> situations.

The last point may not be true (unless, of course, you have demonstrated it).  
SQLite is designed to intelligently use whatever memory is available.  It can 
expand to fill a lot of memory, but then stop.

Some things to check:

Check the values returned from /all/ sqlite3_ calls, including ones which 
finalize statements and close connections.  Anything that doesn't return 
SQLITE_OK can sabotage the way future calls use memory. Tracking down a missing 
_finalize() can completely change the memory footprint.

Secondly, from your above description I assume that your application doesn't 
use SQLite all the time it's running.  I think you're describing something that 
keeps a connection in use only part of the time.  If that's the case, as a 
debugging aid you might try manually calling



sqlite3_initialize() and sqlite3_shutdown() in that code, then look at memory 
usage after those calls.  This might magically fix your problem.  But it might 
also allow you to figure out what your problem actually is.  If SQLite is still 
using any significant resources after _shutdown(), something is wrong.

> Is there any logic behind when sqlite engine releases VM memory?

It shouldn't do anything very unusual.  But SQLite will not close a connection 
if a resource (e.g. an unfinalized statement) still exists for the connection.  
And that's the sort of thing that might trigger what you are reporting.

Simon.

PS: Thanks for your detailed description of your setup and concerns, which made 
it unnecessary to ask lots of questions before answering.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Simon Davies]

On 10 November 2014 16:03, Don V Nielsen  wrote:
> Isn't this the result of the results cache?  The two queries are identical.
>

The query plan changes...

.
.
.
sqlite>  explain query plan select count(*) from v wherez = 0 and
   ...>   (   y between 100 and 1001000
   ...>   or  y between 200 and 2001000
   ...>   or  y between 300 and 3001000
   ...>   or  y between 400 and 4001000);
0|0|0|SEARCH TABLE v USING COVERING INDEX w (z=?)
sqlite>
sqlite>
sqlite> analyze;
sqlite>
sqlite>
sqlite>  explain query plan select count(*) from v wherez = 0 and
   ...>   (   y between 100 and 1001000
   ...>   or  y between 200 and 2001000
   ...>   or  y between 300 and 3001000
   ...>   or  y between 400 and 4001000);
0|0|0|SEARCH TABLE v USING COVERING INDEX w (z=? AND y>? AND y? AND y? AND y? AND y

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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Don V Nielsen]

Isn't this the result of the results cache?  The two queries are identical.

On Mon, Nov 10, 2014 at 9:26 AM, Clemens Ladisch  wrote:

> RP McMurphy wrote:
> > Is there a way we can make the w index work with both queries and not
> > have to run external loops to flatten all the WHERE clauses?
>
> 
>
> sqlite> .timer on
> sqlite> select count(*) from v wherez = 0 and
>...> (   y between 100 and 1001000
>...> or  y between 200 and 2001000
>...> or  y between 300 and 3001000
>...> or  y between 400 and 4001000);
> 1334
> Run Time: real 1.100 user 1.092007 sys 0.00
> sqlite> analyze;
> sqlite> select count(*) from v wherez = 0 and
>...> (   y between 100 and 1001000
>...> or  y between 200 and 2001000
>...> or  y between 300 and 3001000
>...> or  y between 400 and 4001000);
> 1334
> Run Time: real 0.002 user 0.00 sys 0.00
>
>
> Regards,
> Clemens
> ___
> 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] Virtual memory management of Sqlite core on Windows Mobile 6.5 with .NET CF 3.5

[Pavlo]

Hi all,

Here is  what we have

1. We are using Sqlite provider from

sqlite-netFx35-binary-PocketPC-ARM-2008-1.0.94.0.zip on Windows Mobile 6.5
in several our projects written using compact framework 3.5
2.  Operation system we are targeting is Windows Mobile - famous for its
inherent restriction of virtual memory usage. Every process has the frame of
nearly 20 megabytes of RAM (declared value is 32 megabytes minus system dll
and shared libraries). 
3. We are developing software for large vessels and cruise liners.
Application uses a fairly moderate database when talking about this
environment&size - Sqlite file takes up around 3-6 megabytes depending on
liner size and people traveling in.
>From the moment user launches the application the last has to run "almost"
forever until cruise ends (approximately 2 weeks)without restarting and
always has to be ready to run as application deals with emergency cases in
particular. 

5. Application syncs data with remote web service to have up-to-date data
all the time(there is some accepted delay). Throughout the whole cruise
Database gets refreshed completely on the device several times as people
moving onboard/ashore every day.

6. Application itself was thoroughly tested for memory-leaks.

And now the main issue we are struggling with:

Total Virtual memory allocations size made by Sqlite core seems to grow in
time in application process even though Sqlite connection get closed on a
regular basis.
For example if we do search touching several tables in sqlite database it
seems like sqlite core uses memory-mapped IO to do its job done before
returning results of a query.
There are of course other quires that touch almost all tables in the
database. It results in growing virtual memory footprint and fragmentation.
However it looks like Sqlite never releases that allocated virtual memory
space in application process even after we close the sqlite connection and
delete sqlite DB file from storage.
So you imagine if for several days database changes and sqlite engine uses
more and more memory-mapped pages we will end-up with "Out-of-memory"
situations.

Is there any logic behind when sqlite engine releases VM memory?

Is there any control or configuration we can apply to sqlite core to
instruct regard memory policies?

Thank you in advance for your time. 

Thanks, Pavlo.

 

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

Re: [sqlite] Change UPDATE with JOIN sintax

[Clemens Ladisch]

dylan666 wrote:
> update Table1
> set Visibility=1
> where ConsumerID in (select * from
> Table1 join Table2
> on Table1.ConsumerID = Table2.id
> where Table1.visibility = 0 and Table2.visibility = 1)
>
> Unfortunately I get this error:
> only a single result allowed for a SELECT that is part of an expression

The subquery returns all columns, but "ConsumerID IN ..." expectes only
one column.  (Which one should be compared?)

Change the subquery to return only the column you want to use:

  update Table1
  set Visibility=1
  where ConsumerID in (select Table1.ConsumerID from
   Table1 join Table2
   on Table1.ConsumerID = Table2.id
   where Table1.visibility = 0 and Table2.visibility = 1)

But the IN makes the join superfluous:

  UPDATE Table1
  SET Visibility = 1
  WHERE ConsumerID IN (SELECT ID
   FROM Table2
   WHERE Visibility = 1)
AND Visibility = 0


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

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[Clemens Ladisch]

RP McMurphy wrote:
> Is there a way we can make the w index work with both queries and not
> have to run external loops to flatten all the WHERE clauses?



sqlite> .timer on
sqlite> select count(*) from v wherez = 0 and
   ...> (   y between 100 and 1001000
   ...> or  y between 200 and 2001000
   ...> or  y between 300 and 3001000
   ...> or  y between 400 and 4001000);
1334
Run Time: real 1.100 user 1.092007 sys 0.00
sqlite> analyze;
sqlite> select count(*) from v wherez = 0 and
   ...> (   y between 100 and 1001000
   ...> or  y between 200 and 2001000
   ...> or  y between 300 and 3001000
   ...> or  y between 400 and 4001000);
1334
Run Time: real 0.002 user 0.00 sys 0.00


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

[sqlite] Discrepancy with indexing and WHERE clause with AND/OR

[RP McMurphy]

If there is a large table and we need to select a subset of values using a 
WHERE clause with an AND/OR construct sqlite has trouble finding the answer in 
a reasonable time. Breaking the queries down into separate SELECT statements 
speeds up the process exponentially.

For example the following takes a few seconds to return the answer 1334. Note 
that the index (w) is a "low quality" index with the arguments in the wrong 
order. The reason for this is explained further down:

with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 
1000)
insert into v select x % 3,x from cnt;

create index w on v(z,y);
select count(*) from v wherez = 0 and
(   y between 100 and 1001000 
or  y between 200 and 2001000
or  y between 300 and 3001000
or  y between 400 and 4001000);

The reason the the "low quality" index is because this data is also accessed in 
a different manner. Namely like this:

select count(*) from v group by z;

Both of the above queries each take about 3 seconds to run. I don't think the 
second query can be made faster, but the first query can certainly be much 
faster even with the "low quality" index. Thus:

select 
(select count(*) from v where z = 0 and y between 100 and 1001000) +
(select count(*) from v where z = 0 and y between 200 and 2001000) +
(select count(*) from v where z = 0 and y between 300 and 3001000) +
(select count(*) from v where z = 0 and y between 400 and 4001000);

Now the query returns the result 1334 almost immediately. The only difference 
is that the WHERE clause has been manually flattened and broken into separate 
SELECT portions.

When we change the index to "high quality" (u) and put the arguments in the 
other order.

drop index w;
create index u on v(y,z);

And rerun the query:

select count(*) from v wherez = 0 and
(   y between 100 and 1001000 
or  y between 200 and 2001000
or  y between 300 and 3001000
or  y between 400 and 4001000);

The answer 1334 is returned almost immediately. And flattening this query gives 
no advantage. But now the second type of query runs terribly slow because the 
index is very poor for this type of query:

select count(*) from v group by z;

And takes more then 30 seconds to finish.

Trying to make two indices in the hope that sqlite will find the optimal one by 
itself gives back result times the same as if only the w index is present:

create index w on v(z,y);
create index u on v(y,z);

Both queries now take a few seconds to run. It is as if the u index does not 
exist.

In our application we have only created the w index (since the u index is awful 
for the grouping query) and manually generate the WHERE/AND/OR flattened 
queries in a loop. This solution is unsatisfying to us and I think we must be 
doing something wrong. Is there a way we can make the w index work with both 
queries and not have to run external loops to flatten all the WHERE clauses?

RP

PS: Below is the text in one unit that can be copied and pasted into a shell 
session running sqlite3.exe:

create table times(idx,j);
create table v(z,y);

with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 1000)
insert into v select x % 3,x from cnt;

create index w on v(z,y);
select 'With index (z,y)';
insert into times select 0,julianday('now');
select 
(select count(*) from v where z = 0 and y between 100 and 1001000) +
(select count(*) from v where z = 0 and y between 200 and 2001000) +
(select count(*) from v where z = 0 and y between 300 and 3001000) +
(select count(*) from v where z = 0 and y between 400 and 4001000);
insert into times select 1,julianday('now');
select strftime('%f',(select j from times where idx=1)-(select j from times 
where idx=0));
select count(*) from v wherez = 0 and
(   y between 100 and 1001000 
or  y between 200 and 2001000
or  y between 300 and 3001000
or  y between 400 and 4001000);
insert into times select 2,julianday('now');
select strftime('%f',(select j from times where idx=2)-(select j from times 
where idx=1));
select count(*) from v group by z;
insert into times select 3,julianday('now');
select strftime('%f',(select j from times where idx=3)-(select j from times 
where idx=2));

drop index w;
delete from times;

create index u on v(y,z);
select 'With index (y,z)';
insert into times select 0,julianday('now');
select 
(select count(*) from v where z = 0 and y between 100 and 1001000)

[sqlite] Change UPDATE with JOIN sintax

[dylan666]

Hi, I'm new with SQLite and I't trying to use an update query with a join.
The original query is this:



I tyied to chenge it in this way:



Unfortunately I get this error:
only a single result allowed for a SELECT that is part of an expression

What is wrong?
Thanks in advance



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Change-UPDATE-with-JOIN-sintax-tp79105.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] SQLite where clause tree

[Igor Tandetnik]

On 11/10/2014 4:40 AM, Prakash Premkumar wrote:

=
  /\
   salary+
  /   \
3 3
   /
  *
   /\
  5 4


This tree makes no sense whatsoever - it somehow has a literal as an 
inner node, with two children. In a correct expression tree, literals 
would be in the leaves and operators in the inner nodes, with each 
subtree representing one operand.

--
Igor Tandetnik

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

Re: [sqlite] SQLite where clause tree

[Richard Hipp]

On Mon, Nov 10, 2014 at 4:40 AM, Prakash Premkumar 
wrote:

> The where clause in sqlite is encoded as a tree
>
> Let's say I have select statement like :
>
> SELECT * from employee where salary = 3+5*4+3;
>
> The tree which takes care of operator precedence is :
>
>=
>  /\
>   salary+
>  /   \
>3 3
>   /
>  *
>   /\
>  5 4
>


No.

If you recompile the SQLite command-line shell (sqlite3.exe) using the
-DSQLITE_ENABLE_SELECTTRACE option, then you can enter:

   CREATE TABLE employee(employee_id,salary);
   .selecttrace 0x100
   SELECT * FROM employee WHERE salary = 3+5*4+3;


The ".selecttrace 0x100" line will cause SQLite to print out its parse tree
using ascii-art.  The result:

'-- SELECT
|-- result-set
|   '-- op=116
|-- FROM
|   '-- {-1,*} employee
'-- WHERE
'-- EQ
|-- ID 'salary'
'-- ADD
|-- ADD
|   |-- 3
|   '-- MUL
|   |-- 5
|   '-- 4
'-- 3



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

Re: [sqlite] SQLite where clause tree

[Simon Slavin]

On 10 Nov 2014, at 9:40am, Prakash Premkumar  wrote:

> If I am constructing this tree for where clause by myself, should i take
> the operator precedence in to account while constructing it or will sqlite
> take care of precedence,given any tree (i.e constructing it with out taking
> precedence into account) ?

SQLite takes operator precedence into account, using the precedence described 
in the section "Operators" on this page:



I found that by Googling 'SQLite precedence', selecting the top hit, then 
searching for 'precedence' on the page.

If you want any other precedence than the one this page describes, you should 
to use brackets.

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

Re: [sqlite] SQLite where clause tree

[Stephen Chrzanowski]

>From a mathematical standpoint in your example, going back to grade 4 math
(35 years ago for me. *sigh*.  I'm so sad), where clause works based
off of standard order of operations based on BEDMAS and eventually working
things down to booleans.  In your example, the math would be processed as
3+5*4+3
3+20+3
23+3
26

BEDMAS doesn't cover boolean logic, however, so FYI 'AND' is grouped as one
thing and 'OR' is grouped as another.

So:
 WHERE A = 1 AND B = 2 OR C = 3
evaluates differently to
 WHERE A = 1 OR B = 2 AND C = 3

In the first case, A and B have to meet the conditions, or C has to meet
the conditions.
In the second case, A has to meet the condition or B and C has to meet the
conditions.

I've never tried or needed XOR or NAND or any other type of evaluator so I
don't know if SQLite is aware of the other types of boolean algebra.


Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Montag, 10. November 2014 10:40
> An: General Discussion of SQLite Database
> Betreff: [sqlite] SQLite where clause tree
>
> The where clause in sqlite is encoded as a tree
>
> Let's say I have select statement like :
>
> SELECT * from employee where salary = 3+5*4+3;
>
> The tree which takes care of operator precedence is :
>
>=
>  /\
>   salary+
>  /   \
>3 3
>   /
>  *
>   /\
>  5 4
>
> If I am constructing this tree for where clause by myself, should i take
> the operator precedence in to account while constructing it or will sqlite
> take care of precedence,given any tree (i.e constructing it with out taking
> precedence into account) ?
>
> Kindly give me some pointers towards this tree construction Thanks a lot
> for your time.
>
> Regards
> Prakash
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This communication (including any attachments) is intended for the use of
> the intended recipient(s) only and may contain information that is
> confidential, privileged or legally protected. Any unauthorized use or
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, please immediately notify the sender
> by return e-mail message and delete all copies of the original
> communication. Thank you for your cooperation.
>
>
> ___
> 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] SQLite where clause tree

[Hick Gunter]

Your tree is wrong. I would expect that operator precedence is handled in the 
parser. The code generator will happily implement any tree, regardless of how 
insane it may be.

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Montag, 10. November 2014 10:40
An: General Discussion of SQLite Database
Betreff: [sqlite] SQLite where clause tree

The where clause in sqlite is encoded as a tree

Let's say I have select statement like :

SELECT * from employee where salary = 3+5*4+3;

The tree which takes care of operator precedence is :

   =
 /\
  salary+
 /   \
   3 3
  /
 *
  /\
 5 4

If I am constructing this tree for where clause by myself, should i take the 
operator precedence in to account while constructing it or will sqlite take 
care of precedence,given any tree (i.e constructing it with out taking 
precedence into account) ?

Kindly give me some pointers towards this tree construction Thanks a lot for 
your time.

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

[sqlite] SQLite where clause tree

[Prakash Premkumar]

The where clause in sqlite is encoded as a tree

Let's say I have select statement like :

SELECT * from employee where salary = 3+5*4+3;

The tree which takes care of operator precedence is :

   =
 /\
  salary+
 /   \
   3 3
  /
 *
  /\
 5 4

If I am constructing this tree for where clause by myself, should i take
the operator precedence in to account while constructing it or will sqlite
take care of precedence,given any tree (i.e constructing it with out taking
precedence into account) ?

Kindly give me some pointers towards this tree construction
Thanks a lot for your time.

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

Re: [sqlite] Does using e.g. LTRIM function remove collation?

[Hick Gunter]

I get the following results for the second select:

A
B
a (lowercase!!!)

Are you sure you ran the exact query stated?

-Ursprüngliche Nachricht-
Von: James K. Lowden [mailto:jklow...@schemamania.org]
Gesendet: Samstag, 08. November 2014 01:52
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Does using e.g. LTRIM function remove collation?

On Thu, 6 Nov 2014 17:02:26 -0500
Richard Hipp  wrote:

> > sqlite> INSERT INTO test VALUES ('b'), ('A'), ('B'), ('a'); SELECT *
> > sqlite> FROM test;
> > b
> > A
> > B
> > a
...
> > sqlite> SELECT * FROM test WHERE LTRIM(col)<'b';
> > A
> > B
> > A
...
> Works as designed.  See
> https://www.sqlite.org/datatype3.html#collation and in particular the
> three rules under section 6.1.

Aren't you overlooking the fact that the WHERE clause is changing not only 
which rows are selected, but the *values* of those rows?

I don't understand how any WHERE clause can convert 'a' to 'A' in the database. 
 I'm unable to find any suggestion of such in the collation documentation.

--jkl

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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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