Re: [sqlite] type of a value bound by sqlite3_bind_blob ()?

[Ivan Shmakov]

> Ivan Shmakov writes:
> Roger Binns writes:

[…]

 >> Consequently if you had a trigger pulling a stunt like this, your
 >> code could try to insert a blob and silently (wrongly) end up with a
 >> string.  SQLite won't even complain if the blob isn't a valid text
 >> encoding producing an invalid string.

 > I don't have any triggers (at least, it wasn't my intent to add
 > them.)  My code is roughly as shown below.

 > And I don't seem to understand where's the problem.

Silly mistake on my part, as I've just found.

Namely, I've had /two/ functions to alter the table in question.
One of them INSERT's the tuple, and uses sqlite3_bind_blob ().
The other, that UPDATE's the tuple, however, uses
sqlite3_bind_text ().

Before I saw that there's sound support for blobs in the current
SQLite, I've planned to use Base64 for these fields; after, I've
changed one of the functions, but not the other.

And yes, code duplication is clearly a bad thing.

Now that the discrepancy is fixed, the problem is gone.

Thanks.

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

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

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

[Duquette, William H (318K)]

On 8/19/11 10:44 AM, "Boris Kolpackov"  wrote:

>Hi William,
>
>"Duquette, William H (318K)"  writes:
>
>> On 8/19/11 10:18 AM, "Boris Kolpackov"  wrote:
>>
>> There's something odd here.  You have the FK constraints deferred, and
>> your code looks like this:
>>
>> BEGIN TRANSACTION;
>> DROP TABLE employer;
>> DROP TABLE employee;
>> COMMIT;
>>
>> According to the sqlite docs, dropping a table when FK constraints are
>> enabled does an implicit "DELETE FROM" first.  That DELETE FROM is
>>causing
>> the constraint violations.  But you have them deferred; they won't be
>> reported until the COMMIT, and only if the constraints are still
>>violated
>> at that time.  But by then, you've also dropped the employee table, so
>>how
>> can there still be FK constraint violations?
>
>That's exactly what I have said in my original post ;-).
>
>
>> Am I missing something?
>
>If so, then that would be the two of us. Though I think this is a bug
>in SQLite.

I think it might be.  (Richard?  Anybody?)

Will


>
>
>> Are you sure you're dropping the tables in a transaction?
>
>The SQL code I included in my original email can be copy-n-pasted into
>the sqlite3 utility to verify this behavior. E.g., do:
>
>$ cat | sqlite3 /tmp/fresh.db
>
>Then copy-n-paste the following SQL (including the last blank line):
>
>
>PRAGMA foreign_keys=ON;
>
>BEGIN TRANSACTION;
>
>CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
>
>INSERT INTO employer VALUES('Simple Tech Ltd');
>
>CREATE TABLE employee (
>  id INTEGER NOT NULL PRIMARY KEY,
>  employer TEXT NOT NULL,
>  FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY
>DEFERRED);
>
>INSERT INTO employee VALUES(1, 'Simple Tech Ltd');
>
>COMMIT;
>
>BEGIN TRANSACTION;
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>
>
>And you will get:
>
>Error: near line 21: foreign key constraint failed
>
>Boris
>-- 
>Boris Kolpackov, Code Synthesis
>http://codesynthesis.com/~boris/blog
>Compiler-based ORM system for C++
>http://codesynthesis.com/products/odb
>Open-source XML data binding for C++
>http://codesynthesis.com/products/xsd
>XML data binding for embedded systems
>http://codesynthesis.com/products/xsde
>
>___
>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] Strange foreign key constraint failed with DROP TABLE

[Boris Kolpackov]

Hi William,

"Duquette, William H (318K)"  writes:

> On 8/19/11 10:18 AM, "Boris Kolpackov"  wrote:
>
> There's something odd here.  You have the FK constraints deferred, and
> your code looks like this:
>
> BEGIN TRANSACTION;
> DROP TABLE employer;
> DROP TABLE employee;
> COMMIT;
>
> According to the sqlite docs, dropping a table when FK constraints are
> enabled does an implicit "DELETE FROM" first.  That DELETE FROM is causing
> the constraint violations.  But you have them deferred; they won't be
> reported until the COMMIT, and only if the constraints are still violated
> at that time.  But by then, you've also dropped the employee table, so how
> can there still be FK constraint violations?

That's exactly what I have said in my original post ;-).


> Am I missing something?

If so, then that would be the two of us. Though I think this is a bug
in SQLite.


> Are you sure you're dropping the tables in a transaction?

The SQL code I included in my original email can be copy-n-pasted into
the sqlite3 utility to verify this behavior. E.g., do:

$ cat | sqlite3 /tmp/fresh.db

Then copy-n-paste the following SQL (including the last blank line):


PRAGMA foreign_keys=ON;

BEGIN TRANSACTION;

CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);

INSERT INTO employer VALUES('Simple Tech Ltd');

CREATE TABLE employee (
  id INTEGER NOT NULL PRIMARY KEY,
  employer TEXT NOT NULL,
  FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY 
DEFERRED);

INSERT INTO employee VALUES(1, 'Simple Tech Ltd');

COMMIT;

BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;



And you will get:

Error: near line 21: foreign key constraint failed

Boris
-- 
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++  http://codesynthesis.com/products/odb
Open-source XML data binding for C++   http://codesynthesis.com/products/xsd
XML data binding for embedded systems  http://codesynthesis.com/products/xsde

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

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

[Duquette, William H (318K)]

On 8/19/11 10:18 AM, "Boris Kolpackov"  wrote:


>Hi William,
>
>"Duquette, William H (318K)"  writes:
>
>> What if you defined the foreign key with "ON DELETE CASCADE"?  Dropping
>> the employer table will delete the employees.
>
>That would be bad for the normal use. In other words, I don't want the
>CASCADE semantics for those references and I don't think there is a way
>to globally turn this on for all the foreign keys.

There's something odd here.  You have the FK constraints deferred, and
your code looks like this:

BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;

According to the sqlite docs, dropping a table when FK constraints are
enabled does an implicit "DELETE FROM" first.  That DELETE FROM is causing
the constraint violations.  But you have them deferred; they won't be
reported until the COMMIT, and only if the constraints are still violated
at that time.  But by then, you've also dropped the employee table, so how
can there still be FK constraint violations?

Am I missing something?

Are you sure you're dropping the tables in a transaction?

Will





>
>___
>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] Strange foreign key constraint failed with DROP TABLE

[Boris Kolpackov]

Hi William,

"Duquette, William H (318K)"  writes:

> What if you defined the foreign key with "ON DELETE CASCADE"?  Dropping
> the employer table will delete the employees.

That would be bad for the normal use. In other words, I don't want the
CASCADE semantics for those references and I don't think there is a way
to globally turn this on for all the foreign keys.

Boris
-- 
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++  http://codesynthesis.com/products/odb
Open-source XML data binding for C++   http://codesynthesis.com/products/xsd
XML data binding for embedded systems  http://codesynthesis.com/products/xsde

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

Re: [sqlite] type of a value bound by sqlite3_bind_blob ()?

[Ivan Shmakov]

> Roger Binns writes:
> On 08/17/2011 09:25 PM, Ivan Shmakov wrote:

 >> Somehow, I've assumed that sqlite3_bind_blob () will bind a
 >> parameter to a blob.

 > It does.  There are no affinity rules that will cause otherwise.

 > There are some operations that cause blobs to be silently promoted to
 > strings.  IMHO these are egregious errors in SQLite since a bucket of
 > bytes cannot be turned into characters unless you know the encoding
 > which SQLite doesn't.  Here is an example:

 > sqlite> select X'616263' || 'd';
 > abcd
 > sqlite> select typeof(X'616263' || 'd');
 > text

As a matter of personal preference, I'd be calling the code
above “an error.”  It's not meaningful to my eye, and I'd likely
avoid it in my code on the basis that even if it has some
interpretation in the language, it'd be just too much a mental
strain to remember such.

 > Consequently if you had a trigger pulling a stunt like this, your code
 > could try to insert a blob and silently (wrongly) end up with a string.
 > SQLite won't even complain if the blob isn't a valid text encoding
 > producing an invalid string.

I don't have any triggers (at least, it wasn't my intent to add
them.)  My code is roughly as shown below.

And I don't seem to understand where's the problem.

Also, I've built a simpler example following the same scheme
(except the use of sqlite3_bind_parameter_index ()), and it
doesn't seem to cast blobs to strings.

Any suggestions?

TIA.

   const char *const sql
 = ("INSERT"
" INTO \"chunk\" (\"id\", \"length\", \"sha1\", \"sha256\")"
" VALUES ($id, $length, $sha1, $sha256)");

   {
 ix_sha1
   = sqlite3_bind_parameter_index (st, "$sha1");
 assert (ix_sha1 > 0);
   }

   {
 int r
   = sqlite3_reset (st);
 assert (r == SQLITE_OK);
   }

   {
 int r
   = sqlite3_bind_blob (st, ix_sha1,
sha1, CHUNK_DB_SHA1_LEN,
SQLITE_TRANSIENT);
 assert (r == SQLITE_OK);
   }

   {
 int r
   = sqlite3_step (st);
 assert (r == SQLITE_DONE);
   }

[…]

-- 
FSF associate member #7257  Coming soon: Software Freedom Day
http://mail.sf-day.org/lists/listinfo/ planning-ru (ru), sfd-discuss (en)

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

Re: [sqlite] SQLite + unicode

[Stephan Beal]

On Fri, Aug 19, 2011 at 5:46 PM, Kees Nuyt  wrote:

> Also of interest might be:
> http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers


i've got a link addition for the JavaScript bindings, if anyone with wiki
commit access is listening...

http://code.google.com/p/v8-juice/wiki/JSPDO

JSPDO is a PHP PDO-like db access abstraction layer for the Google v8 JS
engine.

The SpiderApe link at the top of the JS list is no longer maintained (it's
mine, so i can vouch for it ;), so it can probably be removed (but SpiderApe
had  the first JS bindings i'm aware of for sqlite3, so maybe it has a byte
or two of historical value ;).

And for C/C++ wrappers:

http://fossil.wanderinghorse.net/repos/cpdo/

cpdo is a PDO-like db access abstraction layer for C. Includes a C++ wrapper
API.


Happy Hacking!

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

Re: [sqlite] SQLite + unicode

[Kees Nuyt]

On Fri, 19 Aug 2011 08:26:49 -0700 (PDT), NOCaut 
wrote:

> Say my some wrapper for i can make this query:
> "Select Value from Config
> Where Key = \"??\""

In SQL, string literals are delimited by single qoutes.
So, the statement would be:

SELECT Value FROM Config WHERE Key = '??';

Single quotes inside a string can be escaped with another single
quote. 'O'Donnell' -> 'O''Donnell'.
Of course, escaping is not needed when you sqlite3_bind*() the
values using the C programming interface.

Your question is not completely clear to me, so here are a few
pointers.

You can find some coding examples in the wiki:

http://www.sqlite.org/cvstrac/wiki?p=SampleCode

http://www.sqlite.org/cvstrac/wiki?p=SimpleCode

Also of interest might be:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

Lots of links to follow from there.

Good luck.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite + unicode

[NOCaut]

Say my some wrapper for i can make this query: "Select Value from Config
Where Key = \"بوبوبو\""

Thanks.
-- 
View this message in context: 
http://old.nabble.com/SQLite-%2B-unicode-tp32296232p32296232.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

[Duquette, William H (318K)]

What if you defined the foreign key with "ON DELETE CASCADE"?  Dropping
the employer table will delete the employees.

Will
--
Will Duquette -- william.h.duque...@jpl.nasa.gov
Athena Development Lead -- Jet Propulsion Laboratory
"It's amazing what you can do with the right tools."





On 8/19/11 6:56 AM, "Boris Kolpackov"  wrote:

>Hi,
>
>I am observing a "foreign key constraint failed" error that looks
>like an SQLite bug to me. I have tried the following using 3.7.7.1:
>
>First I create two tables:
>
>PRAGMA foreign_keys=ON;
>
>BEGIN TRANSACTION;
>
>CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
>INSERT INTO employer VALUES('Simple Tech Ltd');
>
>CREATE TABLE employee (
>  id INTEGER NOT NULL PRIMARY KEY,
>  employer TEXT NOT NULL,
>  FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY
>DEFERRED);
>INSERT INTO employee VALUES(1, 'Simple Tech Ltd');
>
>COMMIT;
>
>Now I want to drop them. If I do this:
>
>BEGIN TRANSACTION;
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>I get "Error: foreign key constraint failed" when executing COMMIT.
>
>Here is a relevant quote from the SQLite Foreign Key Support[1] page:
>
>"If foreign key constraints are enabled when it is prepared, the DROP
>TABLE
>command performs an implicit DELETE to remove all rows from the table
>before
>dropping it. The implicit DELETE does not cause any SQL triggers to fire,
>but
>may invoke foreign key actions or constraint violations. If an immediate
>foreign key constraint is violated, the DROP TABLE statement fails and the
>table is not dropped. If a deferred foreign key constraint is violated,
>then
>an error is reported when the user attempts to commit the transaction if
>the
>foreign key constraint violations still exist at that point. Any "foreign
>key
>mismatch" errors encountered as part of an implicit DELETE are ignored."
>
>So seeing that my foreign key is deferred and at the end of the
>transaction
>all the violations have been resolved (there are no more rows in either
>table and there are no other tables -- this is a fresh database), I don't
>see why I am getting the error.
>
>If we change the order of DROPs, then everything works:
>
>BEGIN TRANSACTION;
>DROP TABLE employee;
>DROP TABLE employer;
>COMMIT;
>
>It also helps if we do explicit DELETEs before DROPs:
>
>BEGIN TRANSACTION;
>DELETE FROM employer;
>DELETE FROM employee;
>
>DROP TABLE employer;
>DROP TABLE employee;
>COMMIT;
>
>This, however, does not work:
>
>BEGIN TRANSACTION;
>DELETE FROM employer;
>DROP TABLE employer;
>
>DELETE FROM employee;
>DROP TABLE employee;
>COMMIT;
>
>In addition to the above error, this transaction also issues "Error: no
>such table: main.employer" after the second DELETE.
>
>Can someone confirm if this is a bug in SQLite? If so, I would also
>appreciate any suggestions for work-arounds. I know I can disable
>constraint checking, but in my case it is not easy since I am already
>in transaction.
>
>[1] http://www.sqlite.org/foreignkeys.html
>
>Thanks,
>   Boris
>-- 
>Boris Kolpackov, Code Synthesis
>http://codesynthesis.com/~boris/blog
>Compiler-based ORM system for C++
>http://codesynthesis.com/products/odb
>Open-source XML data binding for C++
>http://codesynthesis.com/products/xsd
>XML data binding for embedded systems
>http://codesynthesis.com/products/xsde
>
>___
>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] Strange foreign key constraint failed with DROP TABLE

[Stephan Beal]

On Fri, Aug 19, 2011 at 4:19 PM, Boris Kolpackov wrote:

> In this simple case, yes. However, I need to support multiple levels
> of references which makes creating "perfect" DROP order difficult or
> even impossible (e.g., if there are cycles).
>
>
Maybe (and i'm just guessing here) disabling/re-enabling foreign keys
before/after the drop?

Ah, the docs say:

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

It is not possible to enable or disable foreign key constraints in the
middle of a multi-statement
transaction (when
SQLite is not in autocommit
mode).
Attempting to do so does not return an error; it simply has no effect.

so the pragma switch would have to come before/after the transaction, as
opposed to being part of it.

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

Re: [sqlite] Strange foreign key constraint failed with DROP TABLE

[Boris Kolpackov]

Hi Stephan,

Stephan Beal  writes:

> The workaround would be simply to switch the order of the DROP TABLEs,
> wouldn't it?

In this simple case, yes. However, I need to support multiple levels
of references which makes creating "perfect" DROP order difficult or
even impossible (e.g., if there are cycles).

Boris
-- 
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++  http://codesynthesis.com/products/odb
Open-source XML data binding for C++   http://codesynthesis.com/products/xsd
XML data binding for embedded systems  http://codesynthesis.com/products/xsde

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

Re: [sqlite] EINTR and write() in os_unix.c

[Stephan Beal]

On Fri, Aug 19, 2011 at 4:10 PM, Pavel Ivanov  wrote:

> No, it won't cause corruption. Because SQLite uses pwrite or its
> alternative with calls to lseek and write. So after interruption it
> will start writing at the same position it wrote previous time
> rewriting part of what it has already written with the same data.


i see. Just poking around a bit...

pwrite has different semantics than write:

pwrite() writes up to count bytes from the buffer starting at buf to
the file descriptor fd at offset  offset.   The  file
   offset is not changed.

whereas write(2) says: "... and the file offset is incremented by the
 number  of  bytes  actually  written"

osWrite is defined as write(2) and osPwrite is defined as pwrite().

There appears to be one block which uses osWrite instead of osPwrite but
still relies on the pwrite semantics:

  if( statbuf.st_size==0 && (pFile->fsFlags & SQLITE_FSFLAGS_IS_MSDOS)!=0 ){
do{ rc = osWrite(fd, "S", 1); }while( rc<0 && errno==EINTR );
if( rc!=1 ){

line 1159 in my copy. In that particular case, with a length of 1, they are
"probably" semantically equivalent, though.

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

Re: [sqlite] EINTR and write() in os_unix.c

[Richard Hipp]

On Fri, Aug 19, 2011 at 10:10 AM, Pavel Ivanov  wrote:

> > If that is indeed the case, the current code will cause corruption on an
> > EINTR on such platforms because the EINTR handling does not account for a
> > non-0 write.
>
> No, it won't cause corruption. Because SQLite uses pwrite or its
> alternative with calls to lseek and write. So after interruption it
> will start writing at the same position it wrote previous time
> rewriting part of what it has already written with the same data.
>

The problem comes up if SQLite is configured to not use pwrite() and instead
make separate calls to lseek() followed by write().  The do-loop that
retries after an EINTR was not rerunning the lseek().  I my testing a patch
for for this now.  http://www.sqlite.org/src/info/e59bdf6116


>
>
> Pavel
>
>
> On Fri, Aug 19, 2011 at 8:48 AM, Stephan Beal 
> wrote:
> > On Thu, Aug 18, 2011 at 6:10 PM, Stephan Beal 
> wrote:
> >
> >> On Thu, Aug 18, 2011 at 5:27 PM, David Garfield <
> >> garfi...@irving.iisd.sra.com> wrote:
> >>
> >>> I HOPE that an interrupt in a large interrupted write will NOT return
> >>
> >> EINTR, because if it does, the partial write is screwed up.
> >>>
> >>
> >> i hope so, too! :-D
> >>
> >
> > It would seem that at least one environment does allow that behaviour.
> From
> > the Linux write(2) man pages:
> >
> > 
> > CONFORMING TO
> >   SVr4, 4.3BSD, POSIX.1-2001.
> >
> >   Under SVr4 a write may be interrupted and return EINTR at any
> point,
> > not just before any data is written.
> > 
> >
> > So this just might actually (potentially) affect some sqlite3 users. i
> don't
> > know if Solaris currently uses SysV or posix semantics, but Solaris was
> once
> > known as SVr4 (SysV, v.4)?
> >
> > If that is indeed the case, the current code will cause corruption on an
> > EINTR on such platforms because the EINTR handling does not account for a
> > non-0 write.
> >
> > http://kb.iu.edu/data/agjs.html
> > http://en.wikipedia.org/wiki/System_V
> > The German page has release dates and easier-to-read inheritance details:
> > http://de.wikipedia.org/wiki/System_V
> > http://en.wikipedia.org/wiki/Solaris_(operating_system)
> >
> > --
> > - stephan beal
> > http://wanderinghorse.net/home/stephan/
> > ___
> > 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
>



-- 
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] Strange foreign key constraint failed with DROP TABLE

[Stephan Beal]

On Fri, Aug 19, 2011 at 3:56 PM, Boris Kolpackov wrote:

> Can someone confirm if this is a bug in SQLite? If so, I would also
> appreciate any suggestions for work-arounds. I know I can disable
> constraint checking, but in my case it is not easy since I am already
> in transaction.
>

The workaround would be simply to switch the order of the DROP TABLEs,
wouldn't it?

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

Re: [sqlite] EINTR and write() in os_unix.c

[Pavel Ivanov]

> If that is indeed the case, the current code will cause corruption on an
> EINTR on such platforms because the EINTR handling does not account for a
> non-0 write.

No, it won't cause corruption. Because SQLite uses pwrite or its
alternative with calls to lseek and write. So after interruption it
will start writing at the same position it wrote previous time
rewriting part of what it has already written with the same data.


Pavel


On Fri, Aug 19, 2011 at 8:48 AM, Stephan Beal  wrote:
> On Thu, Aug 18, 2011 at 6:10 PM, Stephan Beal  wrote:
>
>> On Thu, Aug 18, 2011 at 5:27 PM, David Garfield <
>> garfi...@irving.iisd.sra.com> wrote:
>>
>>> I HOPE that an interrupt in a large interrupted write will NOT return
>>
>> EINTR, because if it does, the partial write is screwed up.
>>>
>>
>> i hope so, too! :-D
>>
>
> It would seem that at least one environment does allow that behaviour. From
> the Linux write(2) man pages:
>
> 
> CONFORMING TO
>       SVr4, 4.3BSD, POSIX.1-2001.
>
>       Under SVr4 a write may be interrupted and return EINTR at any point,
> not just before any data is written.
> 
>
> So this just might actually (potentially) affect some sqlite3 users. i don't
> know if Solaris currently uses SysV or posix semantics, but Solaris was once
> known as SVr4 (SysV, v.4)?
>
> If that is indeed the case, the current code will cause corruption on an
> EINTR on such platforms because the EINTR handling does not account for a
> non-0 write.
>
> http://kb.iu.edu/data/agjs.html
> http://en.wikipedia.org/wiki/System_V
> The German page has release dates and easier-to-read inheritance details:
> http://de.wikipedia.org/wiki/System_V
> http://en.wikipedia.org/wiki/Solaris_(operating_system)
>
> --
> - stephan beal
> http://wanderinghorse.net/home/stephan/
> ___
> 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] Strange foreign key constraint failed with DROP TABLE

[Boris Kolpackov]

Hi,

I am observing a "foreign key constraint failed" error that looks
like an SQLite bug to me. I have tried the following using 3.7.7.1:

First I create two tables:

PRAGMA foreign_keys=ON;

BEGIN TRANSACTION;

CREATE TABLE employer (name TEXT NOT NULL PRIMARY KEY);
INSERT INTO employer VALUES('Simple Tech Ltd');

CREATE TABLE employee (
  id INTEGER NOT NULL PRIMARY KEY,
  employer TEXT NOT NULL,
  FOREIGN KEY (employer) REFERENCES employer (name) DEFERRABLE INITIALLY 
DEFERRED);
INSERT INTO employee VALUES(1, 'Simple Tech Ltd');

COMMIT;

Now I want to drop them. If I do this:

BEGIN TRANSACTION;
DROP TABLE employer;
DROP TABLE employee;
COMMIT;

I get "Error: foreign key constraint failed" when executing COMMIT.

Here is a relevant quote from the SQLite Foreign Key Support[1] page:

"If foreign key constraints are enabled when it is prepared, the DROP TABLE
command performs an implicit DELETE to remove all rows from the table before
dropping it. The implicit DELETE does not cause any SQL triggers to fire, but
may invoke foreign key actions or constraint violations. If an immediate
foreign key constraint is violated, the DROP TABLE statement fails and the
table is not dropped. If a deferred foreign key constraint is violated, then
an error is reported when the user attempts to commit the transaction if the
foreign key constraint violations still exist at that point. Any "foreign key
mismatch" errors encountered as part of an implicit DELETE are ignored."

So seeing that my foreign key is deferred and at the end of the transaction
all the violations have been resolved (there are no more rows in either
table and there are no other tables -- this is a fresh database), I don't
see why I am getting the error.

If we change the order of DROPs, then everything works:

BEGIN TRANSACTION;
DROP TABLE employee;
DROP TABLE employer;
COMMIT;

It also helps if we do explicit DELETEs before DROPs:

BEGIN TRANSACTION;
DELETE FROM employer;
DELETE FROM employee;

DROP TABLE employer;
DROP TABLE employee;
COMMIT;

This, however, does not work:

BEGIN TRANSACTION;
DELETE FROM employer;
DROP TABLE employer;

DELETE FROM employee;
DROP TABLE employee;
COMMIT;

In addition to the above error, this transaction also issues "Error: no
such table: main.employer" after the second DELETE.

Can someone confirm if this is a bug in SQLite? If so, I would also
appreciate any suggestions for work-arounds. I know I can disable
constraint checking, but in my case it is not easy since I am already
in transaction.

[1] http://www.sqlite.org/foreignkeys.html

Thanks,
Boris
-- 
Boris Kolpackov, Code Synthesishttp://codesynthesis.com/~boris/blog
Compiler-based ORM system for C++  http://codesynthesis.com/products/odb
Open-source XML data binding for C++   http://codesynthesis.com/products/xsd
XML data binding for embedded systems  http://codesynthesis.com/products/xsde

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

Re: [sqlite] EINTR and write() in os_unix.c

[Stephan Beal]

On Thu, Aug 18, 2011 at 6:10 PM, Stephan Beal  wrote:

> On Thu, Aug 18, 2011 at 5:27 PM, David Garfield <
> garfi...@irving.iisd.sra.com> wrote:
>
>> I HOPE that an interrupt in a large interrupted write will NOT return
>
> EINTR, because if it does, the partial write is screwed up.
>>
>
> i hope so, too! :-D
>

It would seem that at least one environment does allow that behaviour. From
the Linux write(2) man pages:


CONFORMING TO
   SVr4, 4.3BSD, POSIX.1-2001.

   Under SVr4 a write may be interrupted and return EINTR at any point,
not just before any data is written.


So this just might actually (potentially) affect some sqlite3 users. i don't
know if Solaris currently uses SysV or posix semantics, but Solaris was once
known as SVr4 (SysV, v.4)?

If that is indeed the case, the current code will cause corruption on an
EINTR on such platforms because the EINTR handling does not account for a
non-0 write.

http://kb.iu.edu/data/agjs.html
http://en.wikipedia.org/wiki/System_V
The German page has release dates and easier-to-read inheritance details:
http://de.wikipedia.org/wiki/System_V
http://en.wikipedia.org/wiki/Solaris_(operating_system)

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

Re: [sqlite] VACUUM command not working

[Tarun]

Thank you so much Richard.

I have tried WAL and then applied VACUUM, it is working fine.

Thanks again,
Tarun Thakur

On 8/19/11, Richard Hipp  wrote:
> On Thu, Aug 18, 2011 at 11:19 PM, Tarun  wrote:
>
>> Hi All,
>>
>> I am running program in which I have created SQLite DB file using:
>>
>> sqlite3async_initialize()
>>
>
> The easiest solution to your problem might be simply to not use the
> test_async.c module.  That module was created long ago to work around issues
> with fsync().  These days, using "PRAGMA journal_mode=WAL" does a better job
> of overcoming the same issue.
>
> So I suggest that you drop the test_async.c module and instead enable
> "PRAGMA journal_mode=WAL".  See if that doesn't work out better for you.
> And if not, we'll take up the problem from that point
>
>
>> sqlite3_open_v2()
>>
>> Then created one table into SQLiteDB by using sqlite3_exec() in which
>> I passed SQL command of creating table.
>>
>> Then I inserted records into table using INSERT sql command in
>> sqlite3_exec()
>>
>> Then I did sqlite3async_run()  to commit all write request from
>> pending queue to SQLite DB file on disk.
>>
>> Then I did deletion of records from table using DELETE sql query in
>> sqlite3_exec()
>>
>> Then I ran VACUUM command this way:
>>
>> sql = "VACUUM;";
>> rc = sqlite3_exec(asyncsql.pdb, sql, NULL, 0, &zErrMsg);
>>
>>
>>
>> After successful running of above command I checked size of my
>> SQLiteDB file using system ("ls -lrt");
>>
>> NOTE: No compilations issue. async IO code file and sqlite shared
>> library linked properly. All sqlite3_exec() ran successfully with
>> SQLITE_OK, no error code returned.
>>
>> Given below is output shown:
>>
>> [tarun@emu async_prg]$ ./sqlite_async_compKey vacum1.db 1000
>>
>> 1313661267 1313661269 1313661316
>> system1-> Size of file after records insertion
>> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>>
>> system2  -> Size of file after records deletion
>> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>>
>> VACUUM SQLite API SUCCESS
>> system3   -> Size of file after VACUUM command
>> -rw-r--r--. 1 tarun tarun 559104 Aug 18 15:25 vacum1.db
>>
>> My problem is why SQLiteDB file size is not reduced after VACUUM has
>> been run.  Please help to get VACUUM running in my case.
>> It would be really helpful if anyone can share working demo program
>> using VACUUM.
>> Waiting for your response, I am stuck in my work.
>> --
>> Thanks and Regards,
>> - Tarun Thakur
>> Module Lead
>> NEC HCL System Technologies, Noida
>> www.nechclst.in
>> ___
>> 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
>


-- 
Regards,
- Tarun Thakur
Module Lead
NEC HCL System Technologies, Noida
www.nechclst.in
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users