Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Gregory Petrosyan]

On Mon, Apr 2, 2012 at 7:51 PM, Jay A. Kreibich  wrote:
> On Mon, Apr 02, 2012 at 07:40:58PM +0400, Gregory Petrosyan scratched on the 
> wall:
>> Hello,
>>
>> I am experiancing a weird problem: sometimes (1 time in a 10-100) when
>> 2 processes try to open the same database file (and execute something
>> like 'create table foo if not exists'), one of them fails with
>> SQLITE_BUSY ??? despite 1 second (or bigger) timeout.
>>
>> Processes themselves produce almost no DB activity; they merely start
>> and initialize the database roughly at the same time.
>> sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout().
>>
>> Unfortunately I can't write a small program that reproduces this
>> reliably: my test program never crashes this way (except when timeout
>> is really small ??? say, 10ms). Yet, this behaviour is rare but
>> reproducible, both from Python (apsw) on Windows, Python (apsw) on OS
>> X and native C API on OS X (in entirely different program).
>>
>> Can you please point me at what can be the cause of this?
>
>  You may be seeing a deadlock situation.  This is most commonly
>  associated with explicit transactions (that are open for a longer
>  period of time), but it is possible with implicit transactions.
>
>  If two connections attempt to write to the DB at the same time, it is
>  possible for them to deadlock on the file locking.  SQLite recognizes
>  this and has one of the connections back off with an SQLITE_BUSY error.
>  If this happens in an explicit transaction, the program needs to
>  ROLLBACK the current transaction and start over.  In the case of an
>  implicit transaction around a statement, you can safely re-run
>  the statement.

Thanks a lot for the reply.

Shouldn't it be extremely unlikely for this situation to happen, though?

Can it be diagnosed with more certaincy? I feel uncomfortable guessing
(hoping) that the bug exists due to this race/deadlock.

As a side note: why does not SQLite automatically retry implicit
transactions after invoking busy handler? This is IMO the behaviout
most people will think SQLite has (after reading the docs); if this is
not true, it is almost impossible to rely on busy handler (timeout) at
all, and it is difficult to understand the need for it — if I need to
wrap all queries into retry/sleep on my own, why have extra strange
internal timeouts in SQLite?

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Nico Williams]

On Mon, Apr 2, 2012 at 11:39 PM, Webdude  wrote:
> I'm sure everyone thinks I'm mad, but I still haven't seen proof of "Can't
> be done".

The question is not "can this be done" but "should it be done this
way".  Relying on a sequence of SQL statements yielding exactly the
same DB file, byte for byte, is probably not a good idea: it might
work today, but there's no guarantee it will work tomorrow, at least
not to my knowledge.  What if a timestamp column were added to the
sqlite_master table, populated with the timestamp of each schema
element's creation?  Also, it depends on your application not using
timestamps!

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

/  Inserting the same data in the same order on the same platform

/>>>/  with the same (PRAGMA) settings would result in the files
/>>>/  matching identically.
//

/>>/  Do you feel that the platform - Hardware / OS / some other factor could 
influence the way SQLite performed its sequence?
/


 SQLite stores data inside its files in blocks called 'pages'.
 When you create a new database file SQLite has to pick a page size.
 The page size it picks depends on some details about the hard disk
 the file will be created on (and also on some compilation settings).
 To optimize speed it might, for instance, make pages the size of the
 disk's sectors.  So you can run code on a computer, one time writing
 your file to one hard disk, and another time writing to a hard disk
 with a different sector size, and end up with files with different
 page sizes, and these files will, of course, have different hashes.

 For details, see

 



Cool...
this still sounds promising though, the first part of the paragraph states...

"When a new database is created, SQLite assigned a default page size based on 
information received from the xSectorSize and xDeviceCharacteristics methods of 
thesqlite3_io_methods    object of the 
newly created database file.
The page_size pragma will only cause an immediate change in the page size if it is 
issued while the database is still empty, prior to the first CREATE TABLE 
statement."

...and the second paragraph talks about how it comes up with the default value, 
but doesn't imply it will re-change your value explicitly set with PRAGMA 
page_size.

So it sounds like if I create a new empty DB file, set the  PRAGMA 
page_size=myAllwaysGoingToBeTheSameInt  (before any tables are written), then 
begin the systematic sequential dump of data previously collected in the 
editing file,
that maybe, just maybe I might be able to build a finished file that will hash 
the same when created on any hardware / HDD / OS using this same program and 
SQLite version?!?!?

I may get a slight file access performance hit where the page_size is not 
optimal for the system.
And it will probably restrict me to lock in to using the same SQLite version, 
as this documentation implies that the version number is also added in the 
header starting at offset 96...

http://www.sqlite.org/fileformat2.html#usable_size

...which is probably also the only way of ensuring that SQLite doesn't change 
the way it does things in future releases, but also means I'll have to stick to 
one version that is known to be the most reliable (any suggestions?).


I will have to write up a little test case and try it on a few different 
machines / OS' before I burst into raptures or tears.

Does anyone else know of any other hidden file variables or SQLite system 
process' that would prevent a byte-for-byte perfect re-creation of an SQLite db 
file using the same data but on 2 or more different machines?

I'm sure everyone thinks I'm mad, but I still haven't seen proof of "Can't be 
done".



Cheers Simon, thanks for your time and effort, you have been very helpful.

David.



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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Simon Slavin]

On 3 Apr 2012, at 2:42am, Webdude  wrote:

> Hi Simon,
> 
> thanks for helping me with this.
> 
>> Inserting the same data in the same order on the same platform
>> with the same (PRAGMA) settings would result in the files
>> matching identically.
> 
> Do you feel that the platform - Hardware / OS / some other factor could 
> influence the way SQLite performed its sequence?

SQLite stores data inside its files in blocks called 'pages'.  When you create 
a new database file SQLite has to pick a page size.  The page size it picks 
depends on some details about the hard disk the file will be created on (and 
also on some compilation settings).  To optimize speed it might, for instance, 
make pages the size of the disk's sectors.  So you can run code on a computer, 
one time writing your file to one hard disk, and another time writing to a hard 
disk with a different sector size, and end up with files with different page 
sizes, and these files will, of course, have different hashes.  For details, see



> Here is my reply that I just sent to*Nico Williams, for more insight:
> 
> 
> *It's not important that the 2 db files are exactly the same all the time 
> that people are editing them, but only when they 'finalise' a 'package'.
> So what if some code in the 'packaging' process performed a sequence of 
> queries that read all the data from the db, table by table, and inserted it 
> into a new db.
> Would that same code process, running on the same data but on 2 different 
> machines, produce the exact same file byte for byte?
> Would hardware / OS / anything else affect the final sequence of bytes in the 
> file?

Correct question, answered as above: it's possible that the two files would be 
different on disk even if they contain identical SQL information.  That answer 
depends on the documentation being accurate and the OS returning correct 
information for your hard disks.

> I don't mind the extra coding, and reluctantly can put up with the extra time 
> taken to package at the end if need be.
> But I really need the final files to be the same so that anyone can confirm 
> the content by hashing the file itself even if they don't have the program 
> that reads it.
> Also, given a list of the contents, anyone could recreate the same exact file 
> using the program but can still prove the content just by using an 
> independent hash checker.

Sorry, I can't think of any way of checking that the data is the same without 
using real SQLite code to read the files.  The obvious thing to do would be to 
dump the SQL data the same way the SQLite3 shell tool does if you tell it to 
make a text file of the SQL commands needed to make the database.  This can 
take a long time and generate a long file.  And even then you can /still/ get 
different text out even if the data stored in the files is identical.  You'd 
have to know how the software worked to know if the differences mattered.

You'll have this problem with many different pieces of software.  So the 
situation is:

A) Hashes match: files are identical (except for the rare possibility of hash 
collision)
B) Hashes differ: files may or may not contain the same information

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

Re: [sqlite] System.Data.SQLite DLL pre-load bug

[Joe Mistachkin]

Cheetah wrote:
> 
> I went to try and file this as a ticket, but it appears that
> feature has been disabled for the "anonymous" login method.
> 

Reporting issues on this mailing list is fine.

> 
> Anyways, the new release's processor detection and native
> library pre-loading has a nearly show-stopper bug for me.  
> 

The native library pre-loading code is designed to fail without
causing any issues in the containing process.  The actual attempt
to load the native library is wrapped in a try/catch block.

>
> Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE"),
> which is supposed to reveal the architecture for the process,
> but it does not work 100% correctly.  
>

I've seen no evidence of this problem.  This issue has nothing to
do with the environment variable value, which is actually correct.

When pure managed code is compiled by Visual Studio, it should run
natively on the processor architecture of the machine (e.g. x64 in
your case).

In order to run (as opposed to just build) the System.Data.SQLite
projects on a 64-bit machine, selecting the "DebugNativeOnly" (or
"ReleaseNativeOnly") build configuration and the "x64" build
platform are both required because this will enable the necessary
post-build step(s) to copy the interop assembly to the managed
assembly output directory (where it can be located by either the
native library pre-loading code or the OS loader).

A similar setup (complete with post-build steps) will be required
if you wish to import the System.Data.SQLite projects into another
solution.

>
> The upshot is that starting any of my 32 bit applications from
> VS2010 on 64 bit windows, it tries to load the wrong DLL and
> thus throws an exception and dies.
> 

Just to clarify, the native library pre-loading code itself does
not throw any exceptions itself.  Most likely, the exception you
are seeing is a direct result of the OS loader being unable to
locate the native interop assembly because it is not being copied
by the post-build step.

--
Joe Mistachkin

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Jean-Christophe Deschamps]

Do you feel that the platform - Hardware / OS / some other factor 
could influence the way SQLite performed its sequence?


Instead of trying to compare the hashes of DB files themselves, you 
appear to want a strict comparison of sets in the contents of the DBs.


For instance, changing the schema of the DBs in different ways will 
make schema_version differ, just as many internal values (none of which 
preclude the actual (user-visible and meaningful) content to be exactly 
the same.


What would be a more robust way to compare DBs X and Y is to perform a
(select * from X.T except select * from Y.T) union (select * from Y.T 
except select * from X.T)

for each and every user table T in the resultset of
select name from sqlite_master where type like 'table'

(Note that I don't know off-hand what the exact distinction is between 
column name and tbl_name in this master table).


You get rid of row order, order of schema creation, internal encoding, 
history of row life (insert, update, delete) and many details that will 
stop you from comparing row DB files.


That may require some adjustment to work in practice, but the main idea 
is there: comparing sets and SQL is the right tool to do that.


As a sidenote, understand that the query above will silently ignore 
duplicates, but it's my understanding that it shouldn't be a problem in 
your context.


JcD 


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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

/  It's not important that the 2 db files are exactly the same all the time

/>/  that people are editing them, but only when they 'finalise' a 'package'.
/>/  So what if some code in the 'packaging' process performed a sequence of
/>/  queries that read all the data from the db, table by table, and inserted
/>/  it into a new db.
/>/
/>/  I don't mind the extra coding, and reluctantly can put up with the extra
/>/  time taken to package at the end if need be.
/
If that's the case, can't you just dump the database, say, as a text
file containing a sequence of SQL statements? Sort by table name, and
within each table by rowid.


Hi Igor,

thanks for your response.

That is another option that I may have to pursue. Although if some of the data 
is images, sounds, other programs etc,
it would all have to be re-interpreted into text which would blow out the file 
size to almost double (I would suspect ?),
then the file would have to be re-parsed and re-injected into an SQLite db by 
each person who receives the file, not just the person creating it.

It is another thought I could look into, some other way of exporting the data 
out that would be the same on any system.
Just seems a shame that a large collection of data in one file (that is 
searchable and ready to go etc.) is what databases and especially SQLite do so 
well.


Cheers,

David.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Nico Williams]

On Mon, Apr 2, 2012 at 8:37 PM, Webdude  wrote:
> It's not important that the 2 db files are exactly the same all the time
> that people are editing them, but only when they 'finalise' a 'package'.
> So what if some code in the 'packaging' process performed a sequence of
> queries that read all the data from the db, table by table, and inserted it
> into a new db.

This is normally known as canonicalization of data.  Sure, if you do
this and in particular make sure that all rows are ordered, and if you
take care of such things as ensuring that you set the page_size the
same on all hosts that might access this DB, then it's possible,
likely perhaps, that you can make this do what you want.  But, I
wouldn't rely on this without getting a guarantee from the SQLite3
team that they won't break that in the future.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Igor Tandetnik]

On 4/2/2012 9:37 PM, Webdude wrote:

It's not important that the 2 db files are exactly the same all the time
that people are editing them, but only when they 'finalise' a 'package'.
So what if some code in the 'packaging' process performed a sequence of
queries that read all the data from the db, table by table, and inserted
it into a new db.

I don't mind the extra coding, and reluctantly can put up with the extra
time taken to package at the end if need be.


If that's the case, can't you just dump the database, say, as a text 
file containing a sequence of SQL statements? Sort by table name, and 
within each table by rowid.

--
Igor Tandetnik

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

Re: [sqlite] Concurrency violation

[Agrawal, Manish]

Thanks very much for your response. I tried a simple test based on this 
suggestion as follows:

CREATE TABLE "table1" ("field1" INTEGER PRIMARY KEY NOT NULL UNIQUE, "field2" 
INTEGER NOT NULL );
INSERT INTO "table1" VALUES(1, 1);
INSERT INTO "table1" VALUES(2, 2);
INSERT INTO "table1" VALUES(3, 3);
INSERT INTO "table1" VALUES(4, 4);
INSERT INTO "table1" VALUES(5, 5);

The update command generated is:

this._adapter.UpdateCommand.CommandText = "UPDATE 
[main].[sqlite_default_schema].[table1] SET [field1] = @field1, [field2] = 
@field2 WHERE 

(([field1] = @Original_field1) AND ([field2] = @Original_field2));"

Changing field2 to REAL or TEXT made no difference. The comparison is always 
with both fields.

This is just for information for solution if possible. Fortunately it only 
requires a one-time clean-up after the dataset is created.

Thanks
Manish

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, March 30, 2012 4:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Concurrency violation: the UpdateCommand affected 0 of 
the expected 1 records


On 30 Mar 2012, at 6:27pm, "Agrawal, Manish"  wrote:

> Thanks very much. Most of our tables do have datetime fields.

Not in SQLite they don't.  There is no such datatype in SQLite.



Find out how you're storing your dates, and declare your fields as INTEGER, 
REAL or TEXT, then your problem will go away.

Simon.
___
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] Hashing 2 SQLite db files with the same data

[Webdude]

Hi Simon,

thanks for helping me with this.



 Inserting the same data in the same order on the same platform
 with the same (PRAGMA) settings would result in the files
 matching identically.



Do you feel that the platform - Hardware / OS / some other factor could 
influence the way SQLite performed its sequence?


Here is my reply that I just sent to*Nico Williams, for more insight:


*It's not important that the 2 db files are exactly the same all the time that 
people are editing them, but only when they 'finalise' a 'package'.
So what if some code in the 'packaging' process performed a sequence of queries 
that read all the data from the db, table by table, and inserted it into a new 
db.
Would that same code process, running on the same data but on 2 different 
machines, produce the exact same file byte for byte?
Would hardware / OS / anything else affect the final sequence of bytes in the 
file?

I don't mind the extra coding, and reluctantly can put up with the extra time 
taken to package at the end if need be.
But I really need the final files to be the same so that anyone can confirm the 
content by hashing the file itself even if they don't have the program that 
reads it.
Also, given a list of the contents, anyone could recreate the same exact file 
using the program but can still prove the content just by using an independent 
hash checker.

I was more concerned about timestamps etc. factors that may never be the same / 
cannot control.
But you feel that if the exact data was entered in the exact same sequence that 
the result would always be the same?


Thanks again so much for your thoughts and help.


David.



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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

Hi Nico,

thanks for the reply.



 You can't rely on two SQLite3 DBs with the same contents being
 equal files.  The sequences of INSERT/UPDATE/DELETE statements
 that created the two files with the same contents can differ
 and thus result in different b-tree layouts.



It's not important that the 2 db files are exactly the same all the time that 
people are editing them, but only when they 'finalise' a 'package'.
So what if some code in the 'packaging' process performed a sequence of queries 
that read all the data from the db, table by table, and inserted it into a new 
db.
Would that same code process, running on the same data but on 2 different 
machines, produce the exact same file byte for byte?
Would hardware / OS / anything else affect the final sequence of bytes in the 
file?

I don't mind the extra coding, and reluctantly can put up with the extra time 
taken to package at the end if need be.
But I really need the final files to be the same so that anyone can confirm the 
content by hashing the file itself even if they don't have the program that 
reads it.
Also, given a list of the contents, anyone could recreate the same exact file 
using the program but can still prove the content just by using an independent 
hash checker.

I was more concerned about timestamps etc. factors that may never be the same / 
cannot control.
But you feel that if the exact data was entered in the exact same sequence that 
the result would always be the same?


Thanks again so much for your thoughts and help.


David.


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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Nico Williams]

Also, if you were to use the running XOR of hashes method you'd also
have to not make use of auto-allocated row IDs or any INTEGER PRIMARY
KEYs, or AUTOINCREMENTed columns, or to not include any of those in
the hashes, which probably also means not using any of those in
FOREIGN KEYs.  That's... quite restrictive, but possibly good enough
for you.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Simon Slavin]

On 3 Apr 2012, at 12:27am, Webdude  wrote:

> A key part of the design requires that if another user who is using the same 
> program, (and probably would have to be using the same version of the SQLite 
> engine I suspect), if they put exactly the same items into their database and 
> the program runs a hash on their resulting file, then both hash values would 
> be the same (= both files are exactly the same).

Two identical SQLite database files will, of course, represent the same SQLite 
data.  But you can do the same INSERT commands in a different order and produce 
two different database files which represent the same data.  Most SQL commands 
will return the same results for both files, but the files on disk will have 
different contents and therefore hash differently.  And if you know what you're 
doing you can arrange it so that all SQL commands would return the same data 
but the files would hash differently.

> Does anyone know if SQLite stores additional unique internal information such 
> as timestamps etc. that would affect this, and if so could these "additional 
> to the data" variable features be disabled in any way?

SQLite files do contain some metadata like row numbers and internal page 
hashes, but nothing like timestamps unless the programmer generated them in 
software.

> Also, would they have to add the data in the same order etc?

Inserting the same data in the same order on the same platform with the same 
(PRAGMA) settings would result in the files matching identically.  But you want 
more than that.

>  Or would/could the data be encouraged to be internally ordered the same?

> 
> Parsing through the database item by item in some fixed pattern to try to 
> determine a unique content id is not an option, the two separately produced 
> files must be the exactly the same.
> 
> In short is it possible to use or tweak SQLite so that 2 identical sets of 
> data produced by 2 separate people would both file hash to the same value?

There is no simple way to modify SQLite (or any SQL engine) to do this.  The 
flexibility to, for example, enter an 'A' entry after ten thousand 'Z' entries 
are what makes SQL engines a fast way to manipulate data.  There is a process 
you can go through to prove that two SQLite files have the same data in, but it 
will take time (vaguely) proportional to the amount of data in the file, and 
for multi-megabyte databases it can be pretty long.

SQL databases aren't unusual in this.  You will find the same problem with 
anything more than a simple text file.  Take a Word file, insert an 'x', then 
delete it, then save the resulting file under a different name.  Same content 
in terms of meaning, but the files will have many bits different internally and 
their hashes will be different.

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

Re: [sqlite] Hashing 2 SQLite db files with the same data

[Nico Williams]

On Mon, Apr 2, 2012 at 6:27 PM, Webdude  wrote:
> I am building a file comparison tool that is free as in beer and speech.
> The program allows people to put certain things in an SQLite database file,
> then an MD5 or SHA hash is run on the resulting file for identity of the
> total package contents.
>
> A key part of the design requires that if another user who is using the same
> program, (and probably would have to be using the same version of the SQLite
> engine I suspect), if they put exactly the same items into their database
> and the program runs a hash on their resulting file, then both hash values
> would be the same (= both files are exactly the same).
>
> Does anyone know if SQLite stores additional unique internal information
> such as timestamps etc. that would affect this, and if so could these
> "additional to the data" variable features be disabled in any way?

You can't rely on two SQLite3 DBs with the same contents being equal
files.  The sequences of INSERT/UPDATE/DELETE statements that created
the two files with the same contents can differ and thus result in
different b-tree layouts.

> Also, would they have to add the data in the same order etc? Or would/could
> the data be encouraged to be internally ordered the same?

No.

> Parsing through the database item by item in some fixed pattern to try to
> determine a unique content id is not an option, the two separately produced
> files must be the exactly the same.
>
> In short is it possible to use or tweak SQLite so that 2 identical sets of
> data produced by 2 separate people would both file hash to the same value?

What I recommend is that you have the application (or triggers) keep a
sum (XOR) of hashes of all the rows in each table.  These running
checksums, as it were, would be updated after each
INSERT/UPDATE/DELETE, for each row.

Then to check the contents of two DB files for equality quickly you'd
compare these running XORs.  You might have a single running XOR for
the whole DB.

You might want to use SHA-1 for this, or better: SHA-256.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 02/04/12 14:34, Simon Slavin wrote:
> I wonder whether it respects languages.

A German user living in France has some Swedish names in their list to be
sorted.  Do you use Swedish sort order, German sort order, German name
order or French order?

There is a Unicode Collation Algorithm that tries to help:

  http://unicode.org/reports/tr10/

Something you may want to consider is to transliterate the text into the
closest ASCII sounds and then sort on that.  It will help when you have
multiple languages represented, some text missing accents etc.

If you search for "unidecode" you'll find a toolkit for doing just that in
various languages.  The original was written by Sean Burke and the
thinking is documented here:

  http://interglacial.com/~sburke/tpj/as_html/tpj22.html

That site appears dead so the wayback machine helps:


http://web.archive.org/web/20091014161106/http://interglacial.com/~sburke/tpj/as_html/tpj22.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk96ODwACgkQmOOfHg372QTOOgCZAQ95J0sYZZYU5LkmxA2+WtoV
8VQAoMy1FH/wiuaekSIdyspXIGt1pc+1
=ehka
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Hashing 2 SQLite db files with the same data

[Webdude]

Hi,

I am building a file comparison tool that is free as in beer and speech.
The program allows people to put certain things in an SQLite database 
file, then an MD5 or SHA hash is run on the resulting file for identity 
of the total package contents.


A key part of the design requires that if another user who is using the 
same program, (and probably would have to be using the same version of 
the SQLite engine I suspect), if they put exactly the same items into 
their database and the program runs a hash on their resulting file, then 
both hash values would be the same (= both files are exactly the same).


Does anyone know if SQLite stores additional unique internal information 
such as timestamps etc. that would affect this, and if so could these 
"additional to the data" variable features be disabled in any way?
Also, would they have to add the data in the same order etc? Or 
would/could the data be encouraged to be internally ordered the same?


Parsing through the database item by item in some fixed pattern to try 
to determine a unique content id is not an option, the two separately 
produced files must be the exactly the same.


In short is it possible to use or tweak SQLite so that 2 identical sets 
of data produced by 2 separate people would both file hash to the same 
value?



Thanks for your time guys.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 5:46 PM, Simon Slavin  wrote:
> Replace part of that routine with something which specifies the locale rather 
> than fetching it from OS settings.  And store the locale used with the index, 
> as a COLLATE setting.  Thus leaving it up to whoever writes the CREATE 
> command to decide which locale was used.  I find that acceptable.  This does 
> still give you the problem Jean-Christophe noted of sorting multilanguage 
> lists of names, but that's inherent in Unicode.  Encountering the problem 
> just means you're implementing Unicode properly.

If only it were that easy.  A plain C locale (i.e., byte-wise)
collation will result in "encountering the problem", but you won't be
"implementing Unicode properly", you won't be implementing it at all!

Even if you use some Unicode collation, if you don't handle
normalization insensitivity then you're not really doing it right
either.

Consider that HFS+ on MacOS X always normalizes to NFD on
file/directory create.  But all user input methods I've seen to date
produce NFC for all Latin-* characters!  This means that if someone
does a cut-n-paste of filenames from an HFS+ filesystem then there
will be a very difficult-to-detect conflict.

Unicode is hard.  I want a doll that says that.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 10:44pm, Igor Tandetnik  wrote:

> On 4/2/2012 5:34 PM, Simon Slavin wrote:
>> Hmm.  It's there:
>> 
>> 
>> 
>> "The following function compares two Unicode strings of possibly different 
>> lengths.
>> 
>> — Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
>> size_t n2)
>> — Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
>> size_t n2)
>> — Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
>> size_t n2)
>> Compares s1 and s2, lexicographically. Returns a negative value if s1 
>> compares smaller than s2, a positive value if s1 compares larger than s2, or 
>> 0 if they compare equal."
>> 
>> I wonder whether it respects languages.
> 
> These don't,

You know, I don't care that much.  Unicode sorting even without languages would 
be a nice plugin for SQLite3, if that makes things so much simpler.

> but u8_strcoll et al supposedly do, based on LC_COLLATE locale category. 
> Herein lies the problem: if you build an index using these functions while 
> running under locale A, then try to run queries against this database in an 
> application running with locale B, bad things happen. From the point of view 
> of the second application, the index is corrupted.

Replace part of that routine with something which specifies the locale rather 
than fetching it from OS settings.  And store the locale used with the index, 
as a COLLATE setting.  Thus leaving it up to whoever writes the CREATE command 
to decide which locale was used.  I find that acceptable.  This does still give 
you the problem Jean-Christophe noted of sorting multilanguage lists of names, 
but that's inherent in Unicode.  Encountering the problem just means you're 
implementing Unicode properly.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
>>
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?
>
> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.

Also, Unicode collation is typically orders of magnitude slower than
US-ASCII collation.  This comes up a lot in other contexts,
particularly as the various OSes have begun defaulting to Unicode
locales.  I've seen ls(1) of directories with millions of files run as
fast as the output device permits when run in the C locale (in less
than 1 second when tmpfs), but take many minutes when in a UTF-8
locale, and that's without any use of normalization.  But mostly this
is a result of Unicode collation in libc being awful.

The OpenSolaris u8_textprep code is designed to make u8_str*cmp()
really fast, though not quite as fast as the C locale strcmp(), when
strings are mostly ASCII and even when they are not because
u8_textprep does no memory allocation for normalization-insensitive
comparison and has a fast-path for comparing substrings of two or more
ASCII codepoints.  This is the main reason that I'd recommend
u8_textprep...

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 4:24 PM, Simon Slavin  wrote:
> On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:
>> Description: Unicode string library for C
>> The 'libunistring' library implements Unicode strings (in the UTF-8,
>> UTF-16, and UTF-32 encodings), together with functions for
>> Unicode characters (character names, classifications, properties) and
>> functions for string processing (formatted output, width, word
>> breaks, line breaks, normalization, case folding, regular expressions).
>
> Trying to figure out what SQLite would want from Unicode characters I don't 
> end up with any of those.  I think all it wants is sorting, so SQLite can 
> make an index properly.  And I don't really care whether it's case-sensitive 
> or not since my software can do case conversion on input.  Because they're in 
> standard functions, string length and substring substitution would be nice 
> but I can live without them working properly.

SQLite3 needs:

 - string comparison with normalization-insensitivity, unless
   SQLite3 were to normalize TEXT values on INSERT/UPDATE,
   but I don't recommend that, except that for indexes it's
   required; see below

 - string comparison with case-insensitivity as an option (for LIKE)

 - string normalization and case-folding functions, which are
   needed for computing index key prefixes for LIKE and GLOB
   patterns that use globbing, so that the index cursors can be
   positioned correctly

 - preferably a way to specify a collation for Unicode (i.e., a
   language, since collation rules may vary by language)

 - preferably a way to specify not to use locale environment
   variables (see Igor's comments)

 - functionality needed to implement SQLite3's built-in string functions
- i.e., trim(), ltrim(), rtrim(), replace(), substr(), lower(),
upper(), min(), max(), and length()

Incidentally, length() claims to return a count of characters, but it
actually counts *codepoints*.  Counting characters is a lot harder
than counting codepoints...  Codepoint counting in UTF-* is trivial;
character counting requires tables of combining codepoint ranges and
code to skip combining codepoints.  Counting graphemes is harder
still.  Getting these things right is non-trivial.  Ideally there
would be an option to the length() function to request counts of
different possible things: UTF-8 units (bytes), UTF-16 units,
codepoints, characters, glyphs, and graphemes, though just stopping at
characters would do.

Similar comments apply to string indices in functions like substr()!

In practice one should want to count characters when dealing with
sub-string operations, but storage units when dealing with
transmission.  Using codepoint counts in substr() risks breaking
combining codepoint sequences and thus producing garbage.

I think the OpenSolaris u8_textprep code is good enough for the
collation requirements, but it probably isn't sufficient for the
SQLite3 string functions, but I'd have to look carefully.  I suspect
that ICU and libunistring meet all the requirements.

> One problem is that, as someone explained to me last year, sorting of unicode 
> characters depends on which language you're using (and other things if you're 
> fussy).  So for every index you make you'd have to declare the language, and 
> SQLite would have to store it.

SQLite3 allows you to specify collations though, so that's not that
big a deal.  For a web application, say, it's very difficult to
implement sorting that satisfies all possible users because indexes
can't provide a globally sufficient collation, not unless you were
willing to have a multitude of indexes.  Sorting, then, has to be done
on result sets -- that is, without the benefit of indexes in most
cases, which means it will be slow for any queries that return large
row sets.

In practice though this is not that big a deal.  And there will be a
tendency to simplify collations.  For example the Royal Spanish
Academy no longer requires that 'ch' sort after 'c', nor that 'll'
sort after 'l' [*].  I suspect most users won't really care, but
whether they do will depend on the application and the user.

> I was trying to figure out whether SQLite could make use of the OS's unicode 
> library (using different compilation directives for each platform which 
> supports unicode) but I'm only really familiar with the Mac operating system 
> and I don't know how Windows or Linux does these things.

There's no standard C libraries that deal with Unicode in sufficient
detail.  In particular the wchar_t functions are useless for the
purposes of SQLite3 because they try to hide too much detail, and
because in some cases they attempt to hide even the codeset.

[*] http://servicios.larioja.com/romanpaladino/g02.htm claims that the
Academy changed this in 1994, and that people started noticing this in
phone books in 1996, and that they complained.

http://es.wikipedia.org/wiki/Ortograf%C3%ADa_del_espa%C3%B1ol goes
into 

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Jean-Christophe Deschamps]

At 23:44 02/04/2012, you wrote:


I wonder whether it respects languages.


These don't, but u8_strcoll et al supposedly do, based on LC_COLLATE 
locale category. Herein lies the problem: if you build an index using 
these functions while running under locale A, then try to run queries 
against this database in an application running with locale B, bad 
things happen. From the point of view of the second application, the 
index is corrupted.


That is: the collation used for this exact purpose becomes a strong 
metadata of the table but there's no easy way to deal with that.


Not only, but say you have a table of worldwide customers (I do have 
clients in 39 countries today), which exact locale are you going to 
use?  I know this question has no answer (and that's the main grief I 
have with Unicode).


A workable perspective is to come up with a "decent if not perfect" way 
to deal with unaccenting and getting rid of the locale concept.


For those ready to cut some corners and to give a rough idea of what 
can be done easily if you're ready to live with some compromises (since 
*-no-* perfect solution exists), my C shared library implementing a 
large number of string and misc. functions (with both UTF-8 & UTF-16 
interfaces) dealing with a weak form of "unaccented Unicode v5.1" is 
currently a 143Kb Win x86 DLL and runs reasonably fast.


JcD 


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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Igor Tandetnik]

On 4/2/2012 5:34 PM, Simon Slavin wrote:

Hmm.  It's there:



"The following function compares two Unicode strings of possibly different 
lengths.

— Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
size_t n2)
— Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
size_t n2)
— Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
size_t n2)
Compares s1 and s2, lexicographically. Returns a negative value if s1 compares 
smaller than s2, a positive value if s1 compares larger than s2, or 0 if they 
compare equal."

I wonder whether it respects languages.


These don't, but u8_strcoll et al supposedly do, based on LC_COLLATE 
locale category. Herein lies the problem: if you build an index using 
these functions while running under locale A, then try to run queries 
against this database in an application running with locale B, bad 
things happen. From the point of view of the second application, the 
index is corrupted.

--
Igor Tandetnik

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

Re: [sqlite] SQLITE Binding Issue when using [] brackets

[Igor Tandetnik]

On 4/1/2012 4:23 PM, ap wrote:


http://sqlite.org/c3ref/bind_blob.html

Brackets are recommended for attribute names in some cases, it seems the
binding for :VVV does not permit :[VVV]. Is this the case?


This is the case. Parameter names must be proper identifiers.


recdct

{'[c1]': 1, '[c2]': 2}


cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :[c2]
)""",recdct)


Nothing says that parameter names must match column names. You could 
name your parameters :c1 and :c2, or :x and :y, or :foo and :bar.

--
Igor Tandetnik

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 10:24pm, Simon Slavin  wrote:

> On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:
> 
>> Description: Unicode string library for C
>> The 'libunistring' library implements Unicode strings (in the UTF-8,
>> UTF-16, and UTF-32 encodings), together with functions for
>> Unicode characters (character names, classifications, properties) and
>> functions for string processing (formatted output, width, word
>> breaks, line breaks, normalization, case folding, regular expressions).
>> 
>> This package contains the shared library.
>> Homepage: http://www.gnu.org/software/libunistring/
> 
> Trying to figure out what SQLite would want from Unicode characters I don't 
> end up with any of those.  I think all it wants is sorting, so SQLite can 
> make an index properly.

Hmm.  It's there:



"The following function compares two Unicode strings of possibly different 
lengths.

— Function: int u8_cmp2 (const uint8_t *s1, size_t n1, const uint8_t *s2, 
size_t n2)
— Function: int u16_cmp2 (const uint16_t *s1, size_t n1, const uint16_t *s2, 
size_t n2)
— Function: int u32_cmp2 (const uint32_t *s1, size_t n1, const uint32_t *s2, 
size_t n2)
Compares s1 and s2, lexicographically. Returns a negative value if s1 compares 
smaller than s2, a positive value if s1 compares larger than s2, or 0 if they 
compare equal."

I wonder whether it respects languages.  I don't think so, but I can live 
without it.  Time to hack up an external function for SQLite and see how little 
of the library I need to make it work.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 9:58pm, Alexey Pechnikov  wrote:

> Description: Unicode string library for C
> The 'libunistring' library implements Unicode strings (in the UTF-8,
> UTF-16, and UTF-32 encodings), together with functions for
> Unicode characters (character names, classifications, properties) and
> functions for string processing (formatted output, width, word
> breaks, line breaks, normalization, case folding, regular expressions).

Trying to figure out what SQLite would want from Unicode characters I don't end 
up with any of those.  I think all it wants is sorting, so SQLite can make an 
index properly.  And I don't really care whether it's case-sensitive or not 
since my software can do case conversion on input.  Because they're in standard 
functions, string length and substring substitution would be nice but I can 
live without them working properly.

One problem is that, as someone explained to me last year, sorting of unicode 
characters depends on which language you're using (and other things if you're 
fussy).  So for every index you make you'd have to declare the language, and 
SQLite would have to store it.

I was trying to figure out whether SQLite could make use of the OS's unicode 
library (using different compilation directives for each platform which 
supports unicode) but I'm only really familiar with the Mac operating system 
and I don't know how Windows or Linux does these things.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 3:58 PM, Alexey Pechnikov  wrote:
> And see too:
>
> Homepage: http://www.gnu.org/software/libunistring/

Thanks!  That looks like a dream come true (even if LGPL).  It's much
more complete than the OpenSolaris u8_textprep stuff, which would be
particularly useful for SQLite3 string functions.  But for just
collations the u8_textprep stuff is good enough, and probably much
smaller (though I've not yet examined the size of libunistring).

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

And see too:

# LANG=C aptitude show libunistring0
Package: libunistring0
New: yes
State: not installed
Version: 0.9.3-3
Priority: optional
Section: libs
Maintainer: Andreas Rottmann 
Uncompressed Size: 1221 k
Depends: libc6 (>= 2.3)
Description: Unicode string library for C
 The 'libunistring' library implements Unicode strings (in the UTF-8,
UTF-16, and UTF-32 encodings), together with functions for
 Unicode characters (character names, classifications, properties) and
functions for string processing (formatted output, width, word
 breaks, line breaks, normalization, case folding, regular expressions).

 This package contains the shared library.
Homepage: http://www.gnu.org/software/libunistring/


2012/4/2 Nico Williams 

> On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> > On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin 
> wrote:
> >> I think ... a higher priority than that would be handling Unicode
> >> correctly.  And having Unicode support would be useful in writing the
> code
> >> which handles dates.
> >>
> >>
> > size of SQLite library:  approx 500 KB
> > size of ICU library: approx 21,919 KB
> >
> > The ICU library (needed to handle Unicode "correctly") is over 40x larger
> > than SQLite.  Can you understand then why we don't want to make SQLite
> > dependent upon ICU?
>
> I completely agree.  It'd be nice if SQLite3 could have an option for
> a weak dependency on ICU.  I.e., if it can be found with dlopen(),
> then use it, else not; a pragma could be used by applications to check
> whether SQLite3 found ICU, or to require that it always be found for
> any given DB file.  Along these lines it'd be nice if a DB file could
> record required loadable extensions and provide a single pragma to
> load them all, with errors returned when compiling or running
> statements other than pragmas until the required extensions are
> loaded.
>
> > If you really need correct ICU support, SQLite will optionally link with
> > ICU and use it.  But *requiring* SQLite to link against ICU is a
> > deal-breaker for many users.
>
> FYI, the OpenSolaris Unicode library is significantly smaller, most
> likely because it deals only with normalization, case conversion, and
> codepoint prohibitions -- everything needed for "stringprep" and
> normalization- and case-insensitive string comparison.  The data
> tables reside in a header file and are 1.8 MB.  The code is 55KB, +
> 3KB of headers.  The license for this is CDDL (a per-file copyleft, as
> opposed to project-wide copyleft), which is not quite as free as ICU's
> license (which is essentially a two-clause BSD license), but probably
> good enough for most projects that use SQLite3.  FreeBSD, for example,
> includes u8_textprep, probably because it also includes ZFS (which
> depends on u8_textprep).  This is still many times larger than
> SQLite3, but still more than ten times smaller than ICU.
>
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/unicode/
>
> http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/sys/
> (scroll down to u8_textprep.h and u8_textprep_data.h)
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Nico Williams]

On Mon, Apr 2, 2012 at 1:25 PM, Richard Hipp  wrote:
> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
>>
>>
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?

I completely agree.  It'd be nice if SQLite3 could have an option for
a weak dependency on ICU.  I.e., if it can be found with dlopen(),
then use it, else not; a pragma could be used by applications to check
whether SQLite3 found ICU, or to require that it always be found for
any given DB file.  Along these lines it'd be nice if a DB file could
record required loadable extensions and provide a single pragma to
load them all, with errors returned when compiling or running
statements other than pragmas until the required extensions are
loaded.

> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many users.

FYI, the OpenSolaris Unicode library is significantly smaller, most
likely because it deals only with normalization, case conversion, and
codepoint prohibitions -- everything needed for "stringprep" and
normalization- and case-insensitive string comparison.  The data
tables reside in a header file and are 1.8 MB.  The code is 55KB, +
3KB of headers.  The license for this is CDDL (a per-file copyleft, as
opposed to project-wide copyleft), which is not quite as free as ICU's
license (which is essentially a two-clause BSD license), but probably
good enough for most projects that use SQLite3.  FreeBSD, for example,
includes u8_textprep, probably because it also includes ZFS (which
depends on u8_textprep).  This is still many times larger than
SQLite3, but still more than ten times smaller than ICU.

http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/common/unicode/
http://src.opensolaris.org/source/xref/onnv/onnv-gate/usr/src/uts/common/sys/
(scroll down to u8_textprep.h and u8_textprep_data.h)

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 7:25pm, Richard Hipp  wrote:

> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
> 
>> I think ... a higher priority than that would be handling Unicode
>> correctly.  And having Unicode support would be useful in writing the code
>> which handles dates.
> 
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
> 
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?

Yep.  That's why you don't do it.  And handling other things to make data 
human-readable using Unicode characters would be wrong too.

Simon.

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

[sqlite] 1st Call For Papers, 19th Annual Tcl/Tk Conference 2012

[Andreas Kupries]

19th Annual Tcl/Tk Conference (Tcl'2012)
http://www.tcl.tk/community/tcl2012/

November 12 - 16, 2012
Holiday Inn Chicago Mart Plaza
350 West Mart Center Drive
Chicago, Illinois, USA

Important Dates:

Abstracts and proposals due   August27, 2012
Notification to authors   September 10, 2012
WIP and BOF reservations open August 6, 2012
Author materials due  October   29, 2012
Tutorials Start   November  12, 2012
Conference starts November  14, 2012

Email Contact:tclconfere...@googlegroups.com

Submission of Summaries

Tcl/Tk 2012 will be held in Chicago, Illinois, USA from November 12 -
16, 2012. The program committee is asking for papers and presentation
proposals from anyone using or developing with Tcl/Tk (and
extensions). Past conferences have seen submissions covering a wide
variety of topics including:

* Scientific and engineering applications
* Industrial controls
* Distributed applications and Network Managment
* Object oriented extensions to Tcl/Tk
* New widgets for Tk
* Simulation and application steering with Tcl/Tk
* Tcl/Tk-centric operating environments
* Tcl/Tk on small and embedded devices
* Medical applications and visualization
* Use of different programming paradigms in Tcl/Tk and proposals for new
  directions.
* New areas of exploration for the Tcl/Tk language

Submissions should consist of an abstract of about 100 words and a
summary of not more than two pages, and should be sent as plain text
to  no later than August 27,
2012. Authors of accepted abstracts will have until October 29, 2012
to submit their final paper for the inclusion in the conference
proceedings. The proceedings will be made available on digital media,
so extra materials such as presentation slides, code examples, code
for extensions etc. are encouraged.

Printed proceedings will be produced as an on-demand book at lulu.com

The authors will have 25 minutes to present their paper at the
conference.

The program committee will review and evaluate papers according to the
following criteria:

* Quantity and quality of novel content
* Relevance and interest to the Tcl/Tk community
* Suitability of content for presentation at the conference

Proposals may report on commercial or non-commercial systems, but
those with only blatant marketing content will not be accepted.

Application and experience papers need to strike a balance between
background on the application domain and the relevance of Tcl/Tk to
the application. Application and experience papers should clearly
explain how the application or experience illustrates a novel use of
Tcl/Tk, and what lessons the Tcl/Tk community can derive from the
application or experience to apply to their own development efforts.

Papers accompanied by non-disclosure agreements will be returned to
the author(s) unread. All submissions are held in the highest
confidentiality prior to publication in the Proceedings, both as a
matter of policy and in accord with the U. S. Copyright Act of 1976.

The primary author for each accepted paper will receive registration
to the Technical Sessions portion of the conference at a reduced rate.

Other Forms of Participation

The program committee also welcomes proposals for panel discussions of
up to 90 minutes. Proposals should include a list of confirmed
panelists, a title and format, and a panel description with position
statements from each panelist. Panels should have no more than four
speakers, including the panel moderator, and should allow time for
substantial interaction with attendees. Panels are not presentations
of related research papers.

Slots for Works-in-Progress (WIP) presentations and Birds-of-a-Feather
sessions (BOFs) are available on a first-come, first-served basis
starting in August 6, 2012. Specific instructions for reserving WIP
and BOF time slots will be provided in the registration information
available in June 2012. Some WIP and BOF time slots will be held open
for on-site reservation. All attendees with an interesting work in
progress should consider reserving a WIP slot.

Registration Information

More information on the conference is available the conference Web
site (http://www.tcl.tk/community/tcl2012/) and will be published on
various Tcl/Tk-related information channels.

To keep in touch with news regarding the conference and Tcl events in
general, subscribe to the tcl-announce list. See:
http://code.activestate.com/lists/tcl-announce to subscribe to the
tcl-announce mailing list.


Conference Committee

Clif Flynt  Noumena CorpGeneral 
Chair, Website Admin
Andreas Kupries ActiveState Software Inc.   Program 
Chair
Cyndy Lilagan   Nat. Museum of Health & Medicine, Chicago   
Site/Facilities Chair
Brian Griffin   Mentor Graphics
Ron Fox NSCL/FRIB Michigan State University
Arjen MarkusDeltares
Mike Doyle  National Museum 

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why you need ICU library to parse datetime strings?!! The my previous patch
to parse Russian dates has only single row without any external libs.

2012/4/2 Richard Hipp 

> On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:
>
> >
> > I think ... a higher priority than that would be handling Unicode
> > correctly.  And having Unicode support would be useful in writing the
> code
> > which handles dates.
> >
> >
> size of SQLite library:  approx 500 KB
> size of ICU library: approx 21,919 KB
>
> The ICU library (needed to handle Unicode "correctly") is over 40x larger
> than SQLite.  Can you understand then why we don't want to make SQLite
> dependent upon ICU?
>
> If you really need correct ICU support, SQLite will optionally link with
> ICU and use it.  But *requiring* SQLite to link against ICU is a
> deal-breaker for many 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
>



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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Richard Hipp]

On Mon, Apr 2, 2012 at 2:03 PM, Simon Slavin  wrote:

>
> I think ... a higher priority than that would be handling Unicode
> correctly.  And having Unicode support would be useful in writing the code
> which handles dates.
>
>
size of SQLite library:  approx 500 KB
size of ICU library: approx 21,919 KB

The ICU library (needed to handle Unicode "correctly") is over 40x larger
than SQLite.  Can you understand then why we don't want to make SQLite
dependent upon ICU?

If you really need correct ICU support, SQLite will optionally link with
ICU and use it.  But *requiring* SQLite to link against ICU is a
deal-breaker for many 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] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 6:18pm, Alexey Pechnikov  wrote:

> Please see http://www.sqlite.org/lang_datefunc.html and you will be
> frustrated because SQLite can format date/time to different string formats
> but can't read the produced date/time strings.

I am not frustrated.  That function does not require any understanding of 
locales, timezones, or Summer Time.  It doesn't 'understand' dates at all, it's 
just a version of sprintf() useful for dates.  Easy to write, easy to test.

Turning dates into arbitrary strings is easy.  Adjusting for time zones is 
difficult.  Analyzing a string to turn it into a date is difficult (some 
strings are ambiguous), and may require support for Unicode which is very 
difficult indeed.  This is a complicated thing and putting such functions on 
tiny devices like a GPS Unit would be a big waste.

If you want external functions, SQLite supplies very nice facilities which let 
you plug them in.  No need to patch the source code.  So you can write your 
functions to convert your dates however you want, with whatever understanding 
of locales you want.

I think a future version of SQLite might handle dates.  But a higher priority 
than that would be handling Unicode correctly.  And having Unicode support 
would be useful in writing the code which handles dates.

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

Re: [sqlite] Foreign Key Problems

[Pavel Ivanov]

> Also, it appears that the foreign_keys setting only applies for the life of
> a db connection and they have to be enabled every time a db is opened.  Is
> that correct?

Yes, that's correct.

> Do all PRAGMA settings work like that?

Almost. Some of exceptions are page_size, auto_vacuum, journal_mode
(at least to some extent).


Pavel


On Mon, Apr 2, 2012 at 12:56 PM, Pete  wrote:
> I have a couple of follow up questions on this.
>
> sqlite3 doesn't seem to recognise the foreign_keys PRAGMA.  If I execute
> "PRAGMA foreign_keys=1", I don't get an error.  If I then execute "PRAGMA
> foreign_keys" to get the setting, nothing is returned.  If I INSERT a
> record that violates the foreign key constraint, it is inserted without an
> error.
>
> Enabling foreign keys in my application works fine and INSERTs thast
> violate a fkey constraint fail.  Is this a know problem with sqlite3?  I'm
> using version 3.6.12 on a Mac.
>
> Also, it appears that the foreign_keys setting only applies for the life of
> a db connection and they have to be enabled every time a db is opened.  Is
> that correct?  Do all PRAGMA settings work like that?
>
> Thanks,
>
> Pete
>
> On Mon, Apr 2, 2012 at 9:00 AM,  wrote:
>
>> Message: 12
>> Date: Mon, 02 Apr 2012 15:33:20 +0700
>> From: Dan Kennedy 
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] Foreign Key Problems
>> Message-ID: <4f796450.9030...@gmail.com>
>> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>>
>> On 04/02/2012 07:22 AM, Pete wrote:
>> > I'm running into an issue with foreign keys where no matter what value I
>> > supply for a child key, I get a foreign key mismatch error.  Here are my
>> > test tables.
>>
>> "foreign key mismatch" indicates a schema problem. Usually a missing
>> index. See here:
>>
>>   http://www.sqlite.org/foreignkeys.html#fk_indexes
>>
>> It's likely you need to create a UNIQUE index on t1.RefColumn.
>>
>
>
>
> --
> Pete
> Molly's Revenge 
> ___
> 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] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

> 2012/4/2 Simon Slavin 

Please see http://www.sqlite.org/lang_datefunc.html and you will be
frustrated because SQLite can format date/time to different string formats
but can't read the produced date/time strings.

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Jay A. Kreibich]

On Mon, Apr 02, 2012 at 08:38:37PM +0400, Alexey Pechnikov scratched on the 
wall:
> Why we can't control this? As example, in Russia the date format is
> DD.MM. and is needed the patch
> http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a
> 
> I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
> internationalization. These may be used for parsing and formatting dates.

  It would make more sense to just implement a strptime() SQL function
  to compliment the existing strftime() function.  That would allow
  SQLite to understand and convert any incoming date-time format
  without depending on specific build parameters.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key Problems

[Simon Slavin]

On 2 Apr 2012, at 5:56pm, Pete  wrote:

> Enabling foreign keys in my application works fine and INSERTs thast
> violate a fkey constraint fail.  Is this a know problem with sqlite3?  I'm
> using version 3.6.12 on a Mac.



"This document describes the support for SQL foreign key constraints introduced 
in SQLite version 3.6.19."

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

Re: [sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Simon Slavin]

On 2 Apr 2012, at 5:38pm, Alexey Pechnikov  wrote:

> Why we can't control this? As example, in Russia the date format is
> DD.MM. and is needed the patch
> http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a
> 
> I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
> internationalization. These may be used for parsing and formatting dates.

I take the view that parsing and formatting data should be done by your 
software.  Your software must be aware of time zones, Summer time adjustments, 
and whether you want your months numbered or spelled.  It needs to deal with 
people entering gibberish as a date.  If needs to know, if a date was entered 
by a user in Russia, and printed by a user in Germany, whether it has to be 
adjusted for local time.

SQLite is a database system.  It's used for /storing/ information.  And that 
has nothing to do with what language you speak.  A date is a date, a time is a 
time, and you can be storing time as UTC or in your local timezone.

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

Re: [sqlite] Foreign Key Problems

[Pete]

I have a couple of follow up questions on this.

sqlite3 doesn't seem to recognise the foreign_keys PRAGMA.  If I execute
"PRAGMA foreign_keys=1", I don't get an error.  If I then execute "PRAGMA
foreign_keys" to get the setting, nothing is returned.  If I INSERT a
record that violates the foreign key constraint, it is inserted without an
error.

Enabling foreign keys in my application works fine and INSERTs thast
violate a fkey constraint fail.  Is this a know problem with sqlite3?  I'm
using version 3.6.12 on a Mac.

Also, it appears that the foreign_keys setting only applies for the life of
a db connection and they have to be enabled every time a db is opened.  Is
that correct?  Do all PRAGMA settings work like that?

Thanks,

Pete

On Mon, Apr 2, 2012 at 9:00 AM,  wrote:

> Message: 12
> Date: Mon, 02 Apr 2012 15:33:20 +0700
> From: Dan Kennedy 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign Key Problems
> Message-ID: <4f796450.9030...@gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> On 04/02/2012 07:22 AM, Pete wrote:
> > I'm running into an issue with foreign keys where no matter what value I
> > supply for a child key, I get a foreign key mismatch error.  Here are my
> > test tables.
>
> "foreign key mismatch" indicates a schema problem. Usually a missing
> index. See here:
>
>   http://www.sqlite.org/foreignkeys.html#fk_indexes
>
> It's likely you need to create a UNIQUE index on t1.RefColumn.
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Suggestion about hard-coded time string format YYYY-MM-DD

[Alexey Pechnikov]

Why we can't control this? As example, in Russia the date format is
DD.MM. and is needed the patch
http://sqlite.mobigroup.ru/fdiff?v1=288ad2e1e017565c=720cb1015e95af7a

I think the new pragmas DATEFORMAT and TIMEFORMAT will be helpful for
internationalization. These may be used for parsing and formatting dates.

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

Re: [sqlite] Foreign Key Problems

[Pete]

Thanks Pavel and Dan, that was the problem.  In the past, I've always used
a primary key as the parent of a foreign key link so hadn't run into this
issue.
Pete

On Mon, Apr 2, 2012 at 9:00 AM,  wrote:

> Message: 12
> Date: Mon, 02 Apr 2012 15:33:20 +0700
> From: Dan Kennedy 
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Foreign Key Problems
> Message-ID: <4f796450.9030...@gmail.com>
> Content-Type: text/plain; charset=ISO-8859-1; format=flowed
>
> On 04/02/2012 07:22 AM, Pete wrote:
> > I'm running into an issue with foreign keys where no matter what value I
> > supply for a child key, I get a foreign key mismatch error.  Here are my
> > test tables.
>
> "foreign key mismatch" indicates a schema problem. Usually a missing
> index. See here:
>
>   http://www.sqlite.org/foreignkeys.html#fk_indexes
>
> It's likely you need to create a UNIQUE index on t1.RefColumn.
>



-- 
Pete
Molly's Revenge 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Jay A. Kreibich]

On Mon, Apr 02, 2012 at 07:40:58PM +0400, Gregory Petrosyan scratched on the 
wall:
> Hello,
> 
> I am experiancing a weird problem: sometimes (1 time in a 10-100) when
> 2 processes try to open the same database file (and execute something
> like 'create table foo if not exists'), one of them fails with
> SQLITE_BUSY ??? despite 1 second (or bigger) timeout.
> 
> Processes themselves produce almost no DB activity; they merely start
> and initialize the database roughly at the same time.
> sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout().
> 
> Unfortunately I can't write a small program that reproduces this
> reliably: my test program never crashes this way (except when timeout
> is really small ??? say, 10ms). Yet, this behaviour is rare but
> reproducible, both from Python (apsw) on Windows, Python (apsw) on OS
> X and native C API on OS X (in entirely different program).
> 
> Can you please point me at what can be the cause of this?

  You may be seeing a deadlock situation.  This is most commonly
  associated with explicit transactions (that are open for a longer
  period of time), but it is possible with implicit transactions.
  
  If two connections attempt to write to the DB at the same time, it is
  possible for them to deadlock on the file locking.  SQLite recognizes
  this and has one of the connections back off with an SQLITE_BUSY error.
  If this happens in an explicit transaction, the program needs to
  ROLLBACK the current transaction and start over.  In the case of an
  implicit transaction around a statement, you can safely re-run
  the statement.

 http://sqlite.org/c3ref/busy_handler.html 

 The presence of a busy handler does not guarantee that it will be
 invoked when there is lock contention. If SQLite determines that
 invoking the busy handler could result in a deadlock, it will go
 ahead and return SQLITE_BUSY or SQLITE_IOERR_BLOCKED instead of
 invoking the busy handler. Consider a scenario where one process
 is holding a read lock that it is trying to promote to a reserved
 lock and a second process is holding a reserved lock that it is
 trying to promote to an exclusive lock. The first process cannot
 proceed because it is blocked by the second and the second process
 cannot proceed because it is blocked by the first. If both
 processes invoke the busy handlers, neither will make any
 progress. Therefore, SQLite returns SQLITE_BUSY for the first
 process, hoping that this will induce the first process to release
 its read lock and allow the second process to proceed.


   -j


-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Simultaneous opening of database sometimes fails even with 1 second timeout and only 2 processes

[Gregory Petrosyan]

Hello,

I am experiancing a weird problem: sometimes (1 time in a 10-100) when
2 processes try to open the same database file (and execute something
like 'create table foo if not exists'), one of them fails with
SQLITE_BUSY — despite 1 second (or bigger) timeout.

Processes themselves produce almost no DB activity; they merely start
and initialize the database roughly at the same time.
sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout().

Unfortunately I can't write a small program that reproduces this
reliably: my test program never crashes this way (except when timeout
is really small — say, 10ms). Yet, this behaviour is rare but
reproducible, both from Python (apsw) on Windows, Python (apsw) on OS
X and native C API on OS X (in entirely different program).

Can you please point me at what can be the cause of this?

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

Re: [sqlite] SQLITE Binding Issue when using [] brackets

[Simon Davies]

On 1 April 2012 21:23, ap  wrote:
>
> http://sqlite.org/c3ref/bind_blob.html
>
> Brackets are recommended for attribute names in some cases, it seems the
> binding for :VVV does not permit :[VVV]. Is this the case?

Square brackets are a quoting mechanism for identifiers supported for
compatibility with MS Access and SQL Server.
(http://www.sqlite.org/lang_keywords.html)

>
> I prefer the dictionary binding if possible. Normally no issues, however
> when I introduce brackets there may be an issue. Is this the appropriate
> syntax?  ([c1],[c2]) VALUES ( :[c1], :[c2] )

I don't think so. The column names are c1 and c2 - the square brackets
are a quoting mechanism, not part of the name.
Try  ([c1],[c2]) VALUES ( :c1, :c2 )

>
 recdct
> {'[c1]': 1, '[c2]': 2}
>
 cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :[c2]
 )""",recdct)
> Traceback (most recent call last):
>  File "", line 1, in 
>    cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :
> [c2] )""",recdct)
> OperationalError: unrecognized token: ":"

>

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

Re: [sqlite] Adding R-Tree index module to existing project

[Jay A. Kreibich]

On Sun, Apr 01, 2012 at 08:41:02PM -0700, A Gilmore scratched on the wall:
> I have a project that compiles SQLite with the following options:
> 
>   -DSQLITE_SECURE_DELETE=1
>   -DSQLITE_THREADSAFE=1
>   -DSQLITE_CORE=1
>   -DSQLITE_ENABLE_FTS3=1
>   -DSQLITE_ENABLE_UNLOCK_NOTIFY=1
>   -DSQLITE_DEFAULT_PAGE_SIZE=32768
>   -DSQLITE_MAX_DEFAULT_PAGE_SIZE=32768
>   -DSQLITE_MAX_SCHEMA_RETRY=25
> 
> and sometimes:
> 
>   -DSQLITE_ENABLE_LOCKING_STYLE=1
> 
> The project is stable and makes heavy use of SQLite.  I'd like to
> enable the R-Tree index (-DSQLITE_ENABLE_RTREE=1), can I expect any
> impact to the project beyond allowing the use of R-Tree indexes?
> Will any existing code work differently (gotchas, stability issues,
> etc)?

  R-Tree support is done via virtual table extensions, very similar
  to the FTS extension.  It doesn't alter the SQLite core, it only
  adds additional code to support the R-Tree virtual tables.  You
  should be good to go.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] System.Data.SQLite DLL pre-load bug

[Cheetah]

I went to try and file this as a ticket, but it appears that feature
has been disabled for the "anonymous" login method.

Anyways, the new release's processor detection and native library
pre-loading has a nearly show-stopper bug for me.  The processor
detection uses Environment.GetEnvironmentVariable("PROCESSOR_ARCHITECTURE"),
which is supposed to reveal the architecture for the process, but it
does not work 100% correctly.  For example, see
http://stackoverflow.com/questions/4152294/processor-architecture-returns-amd64-in-some-32-bit-processes
... I'm seeing essentially the same problem, I have a 32 bit process
but PROCESSOR_ARCHITECTURE is coming up AMD64.  This mostly seems to
have something to do with launching the application from Visual Studio
(for me at least), but that's a pretty common and essential use case.

The upshot is that starting any of my 32 bit applications from VS2010
on 64 bit windows, it tries to load the wrong DLL and thus throws an
exception and dies.

I can work around this by using No_PreLoadSQLite and using the
architecture specific LoadLibrary call I wrote myself for prior
versions, but it would be nice to fix this "for real".

There are some handy process architecture properties available in .Net
4, but for maximum compatibility, I think the most reliable thing to
do is to check for IntPtr.Size == 4 implying that the process is x86,
regardless of what PROCESSOR_ARCHITECTURE says.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] (no subject)

[Darko Filipovic]

http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html;> 
http://tourism.singapore18.com/wp-content/uploads/cache/02efpk.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLITE Binding Issue when using [] brackets

[ap]

http://sqlite.org/c3ref/bind_blob.html

Brackets are recommended for attribute names in some cases, it seems the
binding for :VVV does not permit :[VVV]. Is this the case?



I prefer the dictionary binding if possible. Normally no issues, however
when I introduce brackets there may be an issue. Is this the appropriate
syntax?  ([c1],[c2]) VALUES ( :[c1], :[c2] )

>>> recdct
{'[c1]': 1, '[c2]': 2}

>>> cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :[c2] 
>>> )""",recdct)
Traceback (most recent call last):
  File "", line 1, in 
cur.execute("""INSERT INTO merge ([c1],[c2]) VALUES ( :[c1], :
[c2] )""",recdct)
OperationalError: unrecognized token: ":"
>>>

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

[sqlite] (no subject)

[Allen Fowler]

http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html;>
 
http://avocat.prunelle.org/wp-content/plugins/extended-comment-options/02gfns.html
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign Key Problems

[Dan Kennedy]

On 04/02/2012 07:22 AM, Pete wrote:

I'm running into an issue with foreign keys where no matter what value I
supply for a child key, I get a foreign key mismatch error.  Here are my
test tables.


"foreign key mismatch" indicates a schema problem. Usually a missing
index. See here:

  http://www.sqlite.org/foreignkeys.html#fk_indexes

It's likely you need to create a UNIQUE index on t1.RefColumn.





CREATE TABLE "t1" ("RefColumn" TEXT ,"Data" TEXT )

CREATE TABLE "t2" ("FKeyColumn" TEXT  REFERENCES "t1"("RefColumn"),"Data"
TEXT )


PRAGMA foreign_keys is set to 1.


Put a couple of entries into t1:


SELECT * FROM t1


RefColumn   Data

--  --

a   aaa

b   bbb


Now insert a row into t2

INSERT INTO t2 VALUES ('a','aaa');

I get a foreign Key mismatch error.  No matter what value I supply for
FKeyColumn, even NULL, I get the same error.

I disabled foreign keys, then the INSERT worked.  Enabled foreign keys
again, it fails again.

And even more concerning "DELETE FROM t2" also produces a foreign key
mismatch error.

What am I doing wrong?




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