Re: [sqlite] ICU extension not working as expected

[Jean-Christophe Deschamps]

I've downloaded and compiled icu.c according to the instructions in 
the included README (though I had to add -fPIC to the compiler options).


Now, when searching a table, I'm not getting the kind of 
diacritic-insensitive behavior I was expecting:


sqlite> .load lib/libSQLiteICU.so
sqlite> select * from owner where firstname like '%dré%';
idfirstname  last  emai  phon  netid
  -        -
2 André-Marie   Ampère  amp...@example.com  555-  ampere
sqlite> select * from owner where firstname like '%dre%';

I expected both statements to return the same result.  Am I 
overlooking something or do I misunderstand the capabilities of ICU's 
"unicode-aware LIKE operator"?


To get the diacritic-insensitive behavior you're after, you can follow 
previous advice and implement the Unicode Collation Algorithm.


Alternatively and if you're ready to accept some cut corners, you can 
try a small SQLite extension I wrote to this effect.  It contains a 
fair range of Unicode-aware string functions and contrary to ICU, 
focusses on locale-independant behavior (which was what I needed at the 
time).


It uses simplified Unicode v5.1 tries for upper, lower, title, proper 
cases, unfolding, unaccenting, various collations, fuzzy compare and 
much more (LIKE, GLOB, ...).  It works decently well with norm-C 
strings but will give surprising results with other forms of 
normalization.  It's faster than ICU/UCA and has a much smaller 
footprint (~180Kb).


It comes as an archive with public domain C source code and ready to 
use Win32 DLL so that you can try it using a third-party SQLite manager 
(e.g. SQLite Expert) without having to write a single line of code or 
compile anything tricky.


Discussion of all functions can be found at top of the source file.
Download here anytime.

I always appreciate when people report bugs and/or usefulness for 
specific languages.



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

Re: [sqlite] ADO.NET Provider, targeting any cpu

[Joe Mistachkin]

Rob Richardson wrote:
>
> This is the first I have heard of this feature or requirement or whatever
this is.
> This statement seems to be saying that the "System.Data.SQLite
managed-only assembly"
> is different from the "System.Data.SQLite assembly".  Is that true?  When
I download
> a new version of the System.Data.SQLite installation package, how will I
tell the
> difference between the two?
>

The download page for System.Data.SQLite refers to packages that contain one
of the two
kinds of System.Data.SQLite assemblies:

1. The "managed-only assembly", which contains only managed (CLR) code and
uses the
   SQLite.Interop.dll native library.

2. The "mixed-mode assembly", which contains native (x86 -OR- x64) and
managed (CLR)
   code.  This assembly does not use the SQLite.Interop.dll native library
because all
   that code is built into the mixed-mode assembly itself.

The FAQ also contains some useful information on mixed-mode assemblies:

https://system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki

--
Joe Mistachkin

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

Re: [sqlite] ICU extension not working as expected

[Courtney Grimland]

>
> As you can see, this can get arbitrarily complex.  We still don't
> have a good answer.  Your input is welcomed.
>

If only I knew enough about the subject to be able to provide any useful 
input.  Naturally, I think it should work exactly as I want it to work 
at any given moment.  ;)


>
> I have no idea what other database engines do here?  Does anybody
> else know?
>

Here is a general overview of the way MySQL handles it, with some 
interesting links to the "official" Unicode Collation Algorithm and UCA 
weight keys:


http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Change busy error message

[Shaun Seckman (Firaxis)]

I got it from http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked

"Note that an SQLITE_LOCKED error is distinct from SQLITE_BUSY (5). SQLITE_BUSY 
means that another database connection (probably in another process) is using 
the database in a way that prevents you from using it. SQLITE_LOCKED means the 
source of contention is internal and comes from the same database connection 
that received the SQLITE_LOCKED error."

Still the error codes and their equivalent messages are using confusing word 
choices

It would make sense to either have:
SQLITE_BUSY "database is busy."
SQLITE_LOCKED "database table is locked."

OR

SQLITE_LOCKED "database is locked."
SQLITE_TABLE_LOCKED "database table is locked."


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Wednesday, May 23, 2012 1:23 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Feature Request: Change busy error message

> SQLITE_LOCKED implies that the contention is on the same connection

Where did you get this from? Nothing can prevent execution of several 
statements on the same connection.

AFAIK, SQLITE_LOCKED implies that contention is from another connection using 
the same shared database cache. And it can be handled either via a busy handler 
just like SQLITE_BUSY or via sqlite3_unlock_notify().


Pavel


On Wed, May 23, 2012 at 12:39 PM, Shaun Seckman (Firaxis) 
 wrote:
> The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also 
> very different.  SQLITE_LOCKED implies that the contention is on the 
> same connection whereas SQLITE_BUSY implies that the contention is 
> from another connection and can be handled via a busy handler.
>
>
>
> The error message reported from sqlite3_errmsg is very deceiving 
> though...
>
>
>
> SQLITE_BUSY -> "database is locked."
>
> SQLITE_LOCKED -> "database table is locked."
>
>
>
> For days now, I kept receiving a "database is locked." error in my 
> logs thinking the contention was due to a single connection only to 
> just now realize the error code was indeed SQLITE_BUSY.
>
>
>
> Can we instead change the error message to read:
>
> SQLITE_BUSY -> "database is busy."
>
> SQLITE_LOCKED -> "database table is locked"
>
>
>
> -Shaun
>
>
>
>
>
> Shaun Seckman
>
> Firaxis Games
> Programmer
>
>
>
> ___
> 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] ICU extension not working as expected

[Richard Hipp]

On Wed, May 23, 2012 at 2:11 PM, Courtney Grimland <
cgriml...@cfa.harvard.edu> wrote:

> I've downloaded and compiled icu.c according to the instructions in the
> included README (though I had to add -fPIC to the compiler options).
>
> Now, when searching a table, I'm not getting the kind of
> diacritic-insensitive behavior I was expecting:
>

The ICU extension makes use of the u_foldCase() function inside of the LIKE
routine.  u_foldCase() only does "simple" case folding.  It does not do
"full" case folding (hence "'FUSSBALL' LIKE 'fußball'" is FALSE) nor does
u_foldCase() remove diacritics.  Nor does u_foldCase() handle
locale-specific case folding associated with Turkish.  Nor does it do
context-sensitive case folding as is sometimes required in Greek.

I have no idea what other database engines do here?  Does anybody else know?

One can easily see the need to do matching that ignores diacritics.  In
fact, Dan and I were both hard at work on that problems when your email
arrived.  But it is truly a hard problem.

What if the strings are not in Unicode NFC (Normal Form C)?  Should LIKE
convert them to NFC first?  (Can you say "runs slower and uses more
memory"?)

Should LIKE do full case folding, rather than just the simple case folding
that u_foldCase() provides?  Understand that full case folding will
sometimes cause single code points to be translated into three or four code
points.  This adds interesting complications when trying to match the "_"
wildcard character in LIKE.

And why stop with just diacritic removal?  Why not do full transliteration
after the fashion of unidecode?

As you can see, this can get arbitrarily complex.  We still don't have a
good answer.  Your input is welcomed.


>
>
> sqlite> .load lib/libSQLiteICU.so
> sqlite> select * from owner where firstname like '%dré%';
> idfirstname  last  emai  phon  netid
>   -        -
> 2 André-Marie   Ampère  amp...@example.com  555-  ampere
> sqlite> select * from owner where firstname like '%dre%';
> sqlite>
>
>
> I expected both statements to return the same result.  Am I overlooking
> something or do I misunderstand the capabilities of ICU's "unicode-aware
> LIKE operator"?
> __**_
> 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] ICU extension not working as expected

[Pavel Ivanov]

> I expected both statements to return the same result.  Am I overlooking
> something or do I misunderstand the capabilities of ICU's "unicode-aware
> LIKE operator"?

I believe "unicode-aware LIKE operator" means case-insensitive for
non-ASCII characters, not diacritic-insensitive.


Pavel


On Wed, May 23, 2012 at 2:11 PM, Courtney Grimland
 wrote:
> I've downloaded and compiled icu.c according to the instructions in the
> included README (though I had to add -fPIC to the compiler options).
>
> Now, when searching a table, I'm not getting the kind of
> diacritic-insensitive behavior I was expecting:
>
>
> sqlite> .load lib/libSQLiteICU.so
> sqlite> select * from owner where firstname like '%dré%';
> id    firstname      last  emai  phon  netid
>   -        -
> 2     André-Marie   Ampère  amp...@example.com  555-  ampere
> sqlite> select * from owner where firstname like '%dre%';
> sqlite>
>
>
> I expected both statements to return the same result.  Am I overlooking
> something or do I misunderstand the capabilities of ICU's "unicode-aware
> LIKE operator"?
> ___
> 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] ICU extension not working as expected

[Courtney Grimland]

I've downloaded and compiled icu.c according to the instructions in the 
included README (though I had to add -fPIC to the compiler options).


Now, when searching a table, I'm not getting the kind of 
diacritic-insensitive behavior I was expecting:



sqlite> .load lib/libSQLiteICU.so
sqlite> select * from owner where firstname like '%dré%';
idfirstname  last  emai  phon  netid
  -        -
2 André-Marie   Ampère  amp...@example.com  555-  ampere
sqlite> select * from owner where firstname like '%dre%';
sqlite>


I expected both statements to return the same result.  Am I overlooking 
something or do I misunderstand the capabilities of ICU's "unicode-aware 
LIKE operator"?

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

Re: [sqlite] Serialized + Prepared Statement Clarification

[Sander Jansen]

On Wed, May 23, 2012 at 1:05 PM, Igor Tandetnik  wrote:
> On 5/23/2012 1:51 PM, Sander Jansen wrote:
>>
>> I was always under the impression that prepared statements can only be
>> used from one thread at a time, so if 2 threads need to perform the
>> same query independently, you need to have a prepared statement for
>> each thread. Now I came across the following which seems to contradict
>> this:
>>
>> "
>> http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized
>
>
> There is no contradiction. Basically, there's a mutex associated with a
> connection, and sqlite3_step as well as other API functions acquire it on
> entry and release it on exit. So, while it is safe to call sqlite3_step on
> the same statement from multiple threads, it is rather pointless, since a)
> all these calls are going to be serialized on the mutex so you won't
> actually get any parallelism out of this, and b) the statement is traversing
> the same single resultset, so each thread will see some random subset of the
> rows, depending on how their calls happen to interleave.

Ah ok. so for 2 different result sets you need 2 statements. Thanks!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Serialized + Prepared Statement Clarification

[Igor Tandetnik]

On 5/23/2012 1:51 PM, Sander Jansen wrote:

I was always under the impression that prepared statements can only be
used from one thread at a time, so if 2 threads need to perform the
same query independently, you need to have a prepared statement for
each thread. Now I came across the following which seems to contradict
this:

"
http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized


There is no contradiction. Basically, there's a mutex associated with a 
connection, and sqlite3_step as well as other API functions acquire it 
on entry and release it on exit. So, while it is safe to call 
sqlite3_step on the same statement from multiple threads, it is rather 
pointless, since a) all these calls are going to be serialized on the 
mutex so you won't actually get any parallelism out of this, and b) the 
statement is traversing the same single resultset, so each thread will 
see some random subset of the rows, depending on how their calls happen 
to interleave.

--
Igor Tandetnik

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

[sqlite] Serialized + Prepared Statement Clarification

[Sander Jansen]

Hi,

I was always under the impression that prepared statements can only be
used from one thread at a time, so if 2 threads need to perform the
same query independently, you need to have a prepared statement for
each thread. Now I came across the following which seems to contradict
this:

"
http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized

SQLITE_CONFIG_SERIALIZED
There are no arguments to this option. This option sets the threading
mode to Serialized. In other words, this option enables all mutexes
including the recursive mutexes on database connection and prepared
statement objects. In this mode (which is the default when SQLite is
compiled with SQLITE_THREADSAFE=1) the SQLite library will itself
serialize access to database connections and prepared statements so
that the application is free to use the same database connection or
the same prepared statement in different threads at the same time. If
SQLite is compiled with the SQLITE_THREADSAFE=0 compile-time option
then it is not possible to set the Serialized threading mode and
sqlite3_config() will return SQLITE_ERROR if called with the
SQLITE_CONFIG_SERIALIZED configuration option.
"

Or is this section specifically talking about a statement (whose
multiple) row results may be processed by independent threads?


Thanks,

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

Re: [sqlite] Feature Request: Change busy error message

[Stephan Beal]

On Wed, May 23, 2012 at 7:37 PM, Pavel Ivanov  wrote:

> > #define SQLITE_BUSY 5   /* The database file is locked */
> > #define SQLITE_LOCKED   6   /* A table in the database is locked */
>
> Was this quote supposed to answer some question or clarify something?


i apologize for the brevity/confusion - it was intended only to
definitively settle any confusion (on anyone's part) about the difference
in meaning between the two macros. i should have quoted the OP to make that
clearer (the feature request is basically what the API docs already say).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Change busy error message

[Pavel Ivanov]

On Wed, May 23, 2012 at 1:25 PM, Stephan Beal  wrote:
> On Wed, May 23, 2012 at 7:22 PM, Pavel Ivanov  wrote:
>
>> AFAIK, SQLITE_LOCKED implies that contention is from another
>> connection using the same shared database cache. And it can be handled
>> either via a busy handler just like SQLITE_BUSY or via
>> sqlite3_unlock_notify().
>
> #define SQLITE_BUSY         5   /* The database file is locked */
> #define SQLITE_LOCKED       6   /* A table in the database is locked */

Was this quote supposed to answer some question or clarify something?


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

Re: [sqlite] Feature Request: Change busy error message

[Stephan Beal]

On Wed, May 23, 2012 at 7:22 PM, Pavel Ivanov  wrote:

> AFAIK, SQLITE_LOCKED implies that contention is from another
> connection using the same shared database cache. And it can be handled
> either via a busy handler just like SQLITE_BUSY or via
> sqlite3_unlock_notify().



#define SQLITE_BUSY 5   /* The database file is locked */
#define SQLITE_LOCKED   6   /* A table in the database is locked */



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Change busy error message

[Pavel Ivanov]

> SQLITE_LOCKED implies that the contention is on the
> same connection

Where did you get this from? Nothing can prevent execution of several
statements on the same connection.

AFAIK, SQLITE_LOCKED implies that contention is from another
connection using the same shared database cache. And it can be handled
either via a busy handler just like SQLITE_BUSY or via
sqlite3_unlock_notify().


Pavel


On Wed, May 23, 2012 at 12:39 PM, Shaun Seckman (Firaxis)
 wrote:
> The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also
> very different.  SQLITE_LOCKED implies that the contention is on the
> same connection whereas SQLITE_BUSY implies that the contention is from
> another connection and can be handled via a busy handler.
>
>
>
> The error message reported from sqlite3_errmsg is very deceiving
> though...
>
>
>
> SQLITE_BUSY -> "database is locked."
>
> SQLITE_LOCKED -> "database table is locked."
>
>
>
> For days now, I kept receiving a "database is locked." error in my logs
> thinking the contention was due to a single connection only to just now
> realize the error code was indeed SQLITE_BUSY.
>
>
>
> Can we instead change the error message to read:
>
> SQLITE_BUSY -> "database is busy."
>
> SQLITE_LOCKED -> "database table is locked"
>
>
>
> -Shaun
>
>
>
>
>
> Shaun Seckman
>
> Firaxis Games
> Programmer
>
>
>
> ___
> 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] Feature Request: Change busy error message

[Shaun Seckman (Firaxis)]

The two errors SQLITE_BUSY and SQLITE_LOCKED are very similar but also
very different.  SQLITE_LOCKED implies that the contention is on the
same connection whereas SQLITE_BUSY implies that the contention is from
another connection and can be handled via a busy handler.

 

The error message reported from sqlite3_errmsg is very deceiving
though...

 

SQLITE_BUSY -> "database is locked."

SQLITE_LOCKED -> "database table is locked."

 

For days now, I kept receiving a "database is locked." error in my logs
thinking the contention was due to a single connection only to just now
realize the error code was indeed SQLITE_BUSY.

 

Can we instead change the error message to read:

SQLITE_BUSY -> "database is busy."

SQLITE_LOCKED -> "database table is locked"

 

-Shaun

 

 

Shaun Seckman

Firaxis Games
Programmer

 

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

Re: [sqlite] sqlite-users Digest, Vol 53, Issue 22

[Peter Haworth]

Thanks Donald.  I have a utility that imports csv files to sqlite so just
trying to get a handle on what I need to deal with!

Pete
lcSQL Software 



On Wed, May 23, 2012 at 9:00 AM,  wrote:

> Message: 15
> Date: Wed, 23 May 2012 08:11:17 -0400
> From: Donald Griggs 
> To: General Discussion of SQLite Database 
> Subject: Re: [sqlite] csv test cases (was Details On New Features)
> Message-ID:
> >
> Content-Type: text/plain; charset=ISO-8859-1
>
> -- Regarding: *  I have a question about #9 of your test cases.  According
> to RFC 4180, #9 is an invalid record.  The RFC states
>"If fields are not enclosed with double quotes, then double quotes may
> not appear inside the fields."*
>
>
> My example #9 was:
> 9,imperial ("laughing") loon
>
>
> I'm no standards expert, but I would presume the statement was aimed mostly
> at anyone aiming to *create*  csv, rather than as a firm requirement that
> these flawed input lines not be imported by a *receiving* process -- but I
> may be wrong.
>
>
> Regarding:  *However, I imported your test cases into Open Office, Excel,
> and
> Numbers and the resulting spreadsheets all left the quotes in place in
> that record.  To confuse matters even more, if I then exported those
> spreadsheets as csv files, they all enlosed the string in quotes and
> escaped the original quotes.*
>
>
> All these programs, in addition to the current sqlite CLI, allow us to not
> only import these "loony" lines (as in #9) but export them to good csv and
> result in identical table data upon round-tripping -- Seems like a world I
> can live in;-)
>
> (BTW, I was involved in neither the design nor implementation of this
> sqlite code.)
>
> Donald
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_analyzer failing for large files

[Dan Kennedy]

On 05/23/2012 05:06 PM, Demper Delic wrote:



When trying to analyze a 3.5 GB database file (which passes PRAGMA
integrity_check) using the Windows analyzer provided in the download
page, it fails immediately with an "Empty or malformed database:
db.sqlite" error message. It works on some smaller databases I've
tested. My guess is that this is caused by a 32-bit signed integer
overflow on line 42 of tool/spaceanal.tcl


Thanks for reporting this. It is as you say.

Please try the new build up now, which uses Tcl 8.5.11 instead of
8.5.9. Make sure you really do get the new one, sha1 should be:

  11294bec98274d6f0bc99c75f537e6c1b94ad71c

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

Re: [sqlite] ADO.NET Provider, targeting any cpu

[Rob Richardson]

-Original Message-

The System.Data.SQLite managed-only assembly, when the native library 
pre-loading code is enabled (which it is by default starting with release 
1.0.80.0), will now attempt to detect the processor architecture of the process 
it is being loaded into and then it will attempt to load the native interop 
library from an appropriately named subdirectory (e.g.
"x86", "x64", etc). In order to take advantage of this feature, the 
System.Data.SQLite managed-only assembly should be deployed with the other 
managed binaries in your application and the native interop libraries should be 
deployed in platform-specific sub-directories underneath that directory, as 
follows:

\YourApp.exe
\System.Data.SQLite.dll (managed-only assembly)
\x86\SQLite.Interop.dll (x86 native-only interop library)
\x64\SQLite.Interop.dll (x64 native-only interop library)

If this feature does not work properly in your environment, it can be disabled 
by setting the "No_PreLoadSQLite" environment variable prior to loading and/or 
using the System.Data.SQLite assembly.

--
Joe Mistachkin
= end of original message

This is the first I have heard of this feature or requirement or whatever this 
is.  This statement seems to be saying that the "System.Data.SQLite 
managed-only assembly" is different from the "System.Data.SQLite assembly".  Is 
that true?  When I download a new version of the System.Data.SQLite 
installation package, how will I tell the difference between the two?

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

Re: [sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Simon Slavin]

On 22 May 2012, at 7:25pm, Marcelo Paiva  wrote:

> data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"
> 
> question/sentence: select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'

SQLite has no datatype for dates.  Your data '01/01/2012' and '22/05/2012' is 
being stored as text.  So a test

'31/12/2012' > '01/01/2013'

is true because of the first characters: '31' > '01' like 'hut' > 'house' in a 
dictionary.

I recommend that when you store dates in your database you store them in a 
sortable order.  For instance you can store the date '22/05/2012' as 
'2012/05/22'.  If you do this, then a SELECT command

SELECT * FROM tcontsis01 WHERE data >= '2012/01/01' AND data <= '2012/05/01'

will work perfectly.  An alternative would be to store dates as day numbers, 
either using unix epoch (real numbers) or Julian date numbers (integers).  In 
all these cases you search will, of course, be faster if you have the 'data' 
column in an index.

If changing the format of the data in your table is very difficult, then you 
can continue to store them in their current order and use SQLite's date 
functions in your SELECT commands:



However, this will make searches far slower because SQLite will have to convert 
each data entry as it does each search every time it does a search.

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

Re: [sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Chris Peachment]

Here are the steps you must take:

1. Understand that sqlite3 does not have a DATE type, only text.
   It does have functions that can work with text strings to
   be treated as dates, for example strftime().

2. Convert all your dates in the database and in your programmes
   to use a text format with parts in descending order. This is
   ISO 8601 (http://pt.wikipedia.org/wiki/ISO_8601)

 year - month - day
 (ano - mês - dia)

3. Use 4 digit years like 2012 and not 12.

4. Now it is possible to compare dates in the way you want to do.

On Tue, 2012-05-22 at 15:25 -0300, Marcelo Paiva wrote:
> Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I
> need help around sqlite?, my question:
> 
> How question in sqlite the sentence like  the sentence in Posthe tgreSQL:
> 
> data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"
> 
> question/sentence: select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'
> 
> in sqlite ?? not type data, only TEXT -> in sqlite 22/05/2012
> like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by
> negative,
> 
> negative -> select *from tcontsif01 where data>='01/01/2012' and
> data<='01/05/2012'
> 
> order by data -> negative -> order by ASCII in sqlite
> 
> please, one light, please solution, please example, please help me
> 
> I understand datetime, understand strtime please help me, please a example
> routine sql for sqlite3 with functions,
> 
> Marcelo Paiva
> Stante Santa Catarina - Brasil - here is not tropical here is subtropical
> here is cold, but here is Brasil ... please help 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] csv test cases (was Details On New Features)

[Donald Griggs]

-- Regarding: *  I have a question about #9 of your test cases.  According
to RFC 4180, #9 is an invalid record.  The RFC states
"If fields are not enclosed with double quotes, then double quotes may
not appear inside the fields."*


My example #9 was:
9,imperial ("laughing") loon


I'm no standards expert, but I would presume the statement was aimed mostly
at anyone aiming to *create*  csv, rather than as a firm requirement that
these flawed input lines not be imported by a *receiving* process -- but I
may be wrong.


Regarding:  *However, I imported your test cases into Open Office, Excel,
and
Numbers and the resulting spreadsheets all left the quotes in place in
that record.  To confuse matters even more, if I then exported those
spreadsheets as csv files, they all enlosed the string in quotes and
escaped the original quotes.*


All these programs, in addition to the current sqlite CLI, allow us to not
only import these "loony" lines (as in #9) but export them to good csv and
result in identical table data upon round-tripping -- Seems like a world I
can live in;-)

(BTW, I was involved in neither the design nor implementation of this
sqlite code.)

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

[sqlite] sqlite3_analyzer failing for large files

[Demper Delic]

When trying to analyze a 3.5 GB database file (which passes PRAGMA 
integrity_check) using the Windows analyzer provided in the download page, it 
fails immediately with an "Empty or malformed database: db.sqlite" error 
message. It works on some smaller databases I've tested. My guess is that this 
is caused by a 32-bit signed integer overflow on line 42 of tool/spaceanal.tcl
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug? Shell (sqlite3.exe) exits after ".read"-ing a file that contains two errors

[K. Frank]

Hello List!

I see unexpected behavior using sqlite3's .read command.

The basic problem is illustrated by the following test file:

   C:\>type test2.sql
   -- two errors, sqlite3 exits
   select count(*) from no_table_a;
   select count(*) from no_table_b;

   C:\>

Here's the result of running sqlite3 and .read-ing the test file:

   C:\>sqlite3.exe
   SQLite version 3.7.12 2012-05-14 01:41:23
   Enter ".help" for instructions
   Enter SQL statements terminated with a ";"
   sqlite> .read test2.sql
   Error: near line 2: no such table: no_table_a
   Error: near line 3: no such table: no_table_b


   C:\>

Notice that sqlite3 has exited rather than returning to the "sqlite>" prompt.

As far as I can tell this only happens when the file processed by .read
contains precisely two errors.

I am running SQLite version 3.7.12 2012-05-14 01:41:23 on 64-bit
windows 7.  My copy of sqlite3.exe came from

   sqlite-shell-win32-x86-3071200.zip

that I downloaded on 5/18/12.


Best regards.


K. Frank


P.S.

Here are some more test files that illustrate the issue:

   -- one error, sqlite3 doesn't exit
   select count(*) from no_table_a;

   -- three errors, sqlite3 doesn't exit
   select count(*) from no_table_a;
   select count(*) from no_table_b;
   select count(*) from no_table_c;

   -- four errors, sqlite3 doesn't exit
   select count(*) from no_table_a;
   select count(*) from no_table_b;
   select count(*) from no_table_c;
   select count(*) from no_table_d;

   -- two errors, sqlite3 exits
   -- note, it seems to be two errors, not just two errors in a row
   select count(*) from sqlite_master;
   select count(*) from no_table_a;
   create table t1 (c1 int, c2 int);
   select count(*) from sqlite_master;
   select count(*) from no_table_b;

   -- two errors, sqlite3 exits
   -- note, sqlite3 only exits after processing the whole file
   select count(*) from no_table_a;
   select count(*) from no_table_b;
   select count(*) from sqlite_master;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil

[Marcelo Paiva]

Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I
need help around sqlite?, my question:

How question in sqlite the sentence like  the sentence in Posthe tgreSQL:

data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012"

question/sentence: select *from tcontsif01 where data>='01/01/2012' and
data<='01/05/2012'

in sqlite ?? not type data, only TEXT -> in sqlite 22/05/2012
like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by
negative,

negative -> select *from tcontsif01 where data>='01/01/2012' and
data<='01/05/2012'

order by data -> negative -> order by ASCII in sqlite

please, one light, please solution, please example, please help me

I understand datetime, understand strtime please help me, please a example
routine sql for sqlite3 with functions,

Marcelo Paiva
Stante Santa Catarina - Brasil - here is not tropical here is subtropical
here is cold, but here is Brasil ... please help me
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Disable lock control in Sqlite (windows)

[Marco Turco]

Hi all,

I need to disable the Sqlite3 locking system for a Windows app that runs on
a Mac using the Wine/Crossover API,

this due to the fact Wine/Crossover have problem to manage the locking
status on a network shared drive.

 

My app already has a built-in locking system so it could runs also without
the Sqlite3 internal lock system.

 

So, is there a way to disable the Sqlite locking system (windows) ? As I
know this is already available in Sqlite3 unix using the unix-nolock
parameter in lib compilation.

 

Thanks in advance

 

Best Regards,

 

Marco

 

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

Re: [sqlite] ADO.NET Provider, targeting any cpu

[Damien]

2012/5/23 Joe Mistachkin 

>
> Damien wrote:
> >
> > Is there a way to have an assembly targeting "any cpu", that chooses by
> > itself the right dll to use at runtime ?
> >
>
> The System.Data.SQLite managed-only assembly, when the native library
> pre-loading code is enabled (which it is by default starting with
> release 1.0.80.0), will now attempt to detect the processor architecture
> of the process it is being loaded into and then it will attempt to load
> the native interop library from an appropriately named subdirectory (e.g.
> "x86", "x64", etc). In order to take advantage of this feature, the
> System.Data.SQLite managed-only assembly should be deployed with the
> other managed binaries in your application and the native interop
> libraries should be deployed in platform-specific sub-directories
> underneath that directory, as follows:
>
>\YourApp.exe
>\System.Data.SQLite.dll (managed-only assembly)
>\x86\SQLite.Interop.dll (x86 native-only interop library)
>\x64\SQLite.Interop.dll (x64 native-only interop library)
>
> If this feature does not work properly in your environment, it can be
> disabled by setting the "No_PreLoadSQLite" environment variable prior to
> loading and/or using the System.Data.SQLite assembly.
>
>
Tested and working.

Thanks :)

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

Re: [sqlite] SQL Query - finding rows in a table that are not present in another

[Petite Abeille]

On May 23, 2012, at 11:12 AM, Paul Sanderson wrote:

> I need to return all of the rows in table B that are not present in table A

You have at least 3 ways to express such a query:

(1) Using 'in'

select  table_b.*
fromtable_b

where   table_b.key not in ( select key from table_a )


(2) Using 'exists'

select  table_b.*
fromtable_b

where   not exist ( select 1 from table_a where table_a.key = table_b.key )


(3) Using 'join'

selecttable_b.*
from  table_b

left join table_a
ontable_a.key = table_b.key

where table_a.key is null


Additionally, you could also use 'except' to diff the two table keys: 

select  table_b.key
fromtable_b

except
select  table_a.key
fromtable_a


> What is the most efficient way of doing this?

It  mostly depends on your data distribution, relevance of indexes, etc,… check 
the various query plans and pick the one which fits best...  :)



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

Re: [sqlite] ADO.NET Provider, targeting any cpu

[Joe Mistachkin]

Damien wrote:
> 
> Is there a way to have an assembly targeting "any cpu", that chooses by
> itself the right dll to use at runtime ?
> 

The System.Data.SQLite managed-only assembly, when the native library
pre-loading code is enabled (which it is by default starting with
release 1.0.80.0), will now attempt to detect the processor architecture
of the process it is being loaded into and then it will attempt to load
the native interop library from an appropriately named subdirectory (e.g.
"x86", "x64", etc). In order to take advantage of this feature, the
System.Data.SQLite managed-only assembly should be deployed with the
other managed binaries in your application and the native interop
libraries should be deployed in platform-specific sub-directories
underneath that directory, as follows:

\YourApp.exe
\System.Data.SQLite.dll (managed-only assembly)
\x86\SQLite.Interop.dll (x86 native-only interop library)
\x64\SQLite.Interop.dll (x64 native-only interop library)

If this feature does not work properly in your environment, it can be
disabled by setting the "No_PreLoadSQLite" environment variable prior to
loading and/or using the System.Data.SQLite assembly.

--
Joe Mistachkin

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

[sqlite] SQL Query - finding rows in a table that are not present in another

[Paul Sanderson]

I have a couple of table seach of which has one column but millions of
rows, the column is a text column.

I need to return all of the rows in table B that are not present in table A

What is the most efficient way of doing this?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ADO.NET Provider, targeting any cpu

[Damien]

Hi,

I don't know if question has already been asked, and i appologize if it is
the case...

For the SQLite ADO.NET provider, there are precompiled binaries for both
x86 and x64.

Is there a way to have an assembly targeting "any cpu", that chooses by
itself the right dll to use at runtime ?

I did something like this some time ago : 2 classes, one for x86 dll, one
for x64 dll, implementing same interface. And at runtime, a function to get
the right "native methods class", by trying to load one, and catch
"BadImageFormatException", trying next...

Thanks.

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