Re: [sqlite] detect a flaw

[Shalom Elkin]

Igor,

Well, libversion is  3003006
Ok. Now I'll start looking for a reason I am using an older library...

Thanks,

Shalom

On Thu, Mar 24, 2011 at 3:51 PM, Igor Tandetnik  wrote:

> Shalom Elkin  wrote:
> > I use 3.7.5 , and Linux (centos).
>
> Consider using sqlite3_libversion to confirm that. It's possible that more
> than one sqlite version is installed on the system, and you are linking to
> the wrong one.
>
> Your code looks OK (except that in "pragma returns " it should be %d, and
> the fact that you are not freeing any error messages with sqlite3_free). I
> don't see any reason why it shouldn't work.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Igor Tandetnik]

Shalom Elkin  wrote:
> I use 3.7.5 , and Linux (centos).

Consider using sqlite3_libversion to confirm that. It's possible that more than 
one sqlite version is installed on the system, and you are linking to the wrong 
one.

Your code looks OK (except that in "pragma returns " it should be %d, and the 
fact that you are not freeing any error messages with sqlite3_free). I don't 
see any reason why it shouldn't work.
-- 
Igor Tandetnik

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

I use 3.7.5 , and Linux (centos).


On Thu, Mar 24, 2011 at 12:56 PM, BareFeetWare wrote:

> On 24/03/2011, at 4:41 PM, Shalom Elkin  wrote:
>
> > Sorry - it doesn't work.
> > sqlite3_exec with the pragma directive returns no error, but the program
> > still agrees to insert a record that violates foreign_key constraint.
>
> What version of SQLite are you using? I think foreign key support requires
> version 3.6.19.
>
> I must admit, I haven't had to compile SQLite for a while. I mostly just
> link against the binary included in my OS (iOS).
>
> Tom
> BareFeetWare
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[BareFeetWare]

On 24/03/2011, at 4:41 PM, Shalom Elkin  wrote:

> Sorry - it doesn't work.
> sqlite3_exec with the pragma directive returns no error, but the program
> still agrees to insert a record that violates foreign_key constraint.

What version of SQLite are you using? I think foreign key support requires 
version 3.6.19.

I must admit, I haven't had to compile SQLite for a while. I mostly just link 
against the binary included in my OS (iOS).

Tom
BareFeetWare

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

I am ready to rebuild sqlite3 with the foreign_key support enabled
as default. to do this, I should UNDEFINE  SQLITE_OMIT_FOREIGN_KEY.
I can't find a decent way to do this undef. I use g++ on linux.

Thanks,

Shalom

On Thu, Mar 24, 2011 at 7:41 AM, Shalom Elkin wrote:

> Sorry - it doesn't work.
> sqlite3_exec with the pragma directive returns no error, but the program
> still agrees to insert a record that violates foreign_key constraint.
>
> Here is the tables creation
> ==
> CREATE TABLE people(
> id integer,
> nm text);
> INSERT INTO "people" VALUES(1,'Jack');
> INSERT INTO "people" VALUES(2,'Jill');
> CREATE TABLE activity(
> aid integer,
> act  text,
> foreign key (aid) references people(id)
> );
> =
> Here's the simple c++ program (it is actually c...)
> 
> #include 
> #include 
> #include 
> int main(int argc, char **argv){
> sqlite3 *db;
> char *zErrMsg = 0,*P0;
> const  char *Q0;
> const char *Z0= {"insert into activity values (1,\"play\")"};
> const char *Z1= {"insert into activity values (3,\"eat\")"};
> int rc;
> if( argc<2 ){
> fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
> return 101;
>   }
> //open
>   rc = sqlite3_open(argv[1], );
>   if( rc ){
> fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
>  return 111;
>   }
> rc = sqlite3_exec(db,"PRAGMA foreign_keys = ON;",NULL,NULL,);
> printf("pragma returns  |%s|\n",rc,P0);
> //clear
>   rc = sqlite3_exec(db,"delete from activity;",NULL,NULL,);
> //
>   rc = sqlite3_exec(db,"begin transaction;",NULL,NULL,);
>   rc = sqlite3_exec(db,Z0,NULL,NULL,);
>   Q0 =  sqlite3_errmsg(db);
>   printf("|%s| - should succeed : %d,|%s|=>|%s|\n",Z0,rc,P0,Q0);
> //
>   rc = sqlite3_exec(db,Z1,NULL,NULL,);
>   Q0 =  sqlite3_errmsg(db);
>   printf("|%s| - should fail : %d,|%s|=>|%s|\n",Z1,rc,P0,Q0);
>   rc = sqlite3_exec(db,"commit;",NULL,NULL,);
>   sqlite3_close(db);
> }
> =
> and the results:
> [shalom@pato sqlite]$ ./porta tik
> pragma returns  |(null)|
> |insert into activity values (1,"play")| - should succeed :
> 0,|(null)|=>|not an error|
> |insert into activity values (3,"eat")| - should fail : 0,|(null)|=>|not an
> error|
> [shalom@pato sqlite]$ sqlite3 tik "select * from activity"
> 1|play
> 3|eat
> =
>
> 
>
> Shalom
> On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare 
> wrote:
>
>> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>>
>> > I appreciate the input. Some of the advice comes obviously from very
>> good
>> > and talented people who find a challenge at doing things WITHOUT
>> reverting
>> > to code writing.
>>
>> Doing as much (or most often, all) of the logic in SQL (instead of
>> application code) removes a level of complexity, but is also generally
>> faster and internally consistent.
>>
>> > I did a small program. Current show -stopper :
>> >
>> > what is the API equivalent of
>> >
>> > PRAGMA foreign_keys = ON;
>>
>> You can just send each of the SQL commands, including the pragma
>> statement, in sqlite_exec (or you can get fancy with sqlite_prepare etc
>> where it makes sense).
>>
>> Tom
>> BareFeetWare
>>
>> --
>> iPhone/iPad/iPod and Mac software development, specialising in databases
>> develo...@barefeetware.com
>> --
>> Comparison of SQLite GUI tools:
>> http://www.barefeetware.com/sqlite/compare/?ml
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Shalom Elkin
> +972-544-704994
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Shalom Elkin]

Sorry - it doesn't work.
sqlite3_exec with the pragma directive returns no error, but the program
still agrees to insert a record that violates foreign_key constraint.

Here is the tables creation
==
CREATE TABLE people(
id integer,
nm text);
INSERT INTO "people" VALUES(1,'Jack');
INSERT INTO "people" VALUES(2,'Jill');
CREATE TABLE activity(
aid integer,
act  text,
foreign key (aid) references people(id)
);
=
Here's the simple c++ program (it is actually c...)

#include 
#include 
#include 
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0,*P0;
const  char *Q0;
const char *Z0= {"insert into activity values (1,\"play\")"};
const char *Z1= {"insert into activity values (3,\"eat\")"};
int rc;
if( argc<2 ){
fprintf(stderr, "Usage: %s DATABASE\n", argv[0]);
return 101;
  }
//open
  rc = sqlite3_open(argv[1], );
  if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
 return 111;
  }
rc = sqlite3_exec(db,"PRAGMA foreign_keys = ON;",NULL,NULL,);
printf("pragma returns  |%s|\n",rc,P0);
//clear
  rc = sqlite3_exec(db,"delete from activity;",NULL,NULL,);
//
  rc = sqlite3_exec(db,"begin transaction;",NULL,NULL,);
  rc = sqlite3_exec(db,Z0,NULL,NULL,);
  Q0 =  sqlite3_errmsg(db);
  printf("|%s| - should succeed : %d,|%s|=>|%s|\n",Z0,rc,P0,Q0);
//
  rc = sqlite3_exec(db,Z1,NULL,NULL,);
  Q0 =  sqlite3_errmsg(db);
  printf("|%s| - should fail : %d,|%s|=>|%s|\n",Z1,rc,P0,Q0);
  rc = sqlite3_exec(db,"commit;",NULL,NULL,);
  sqlite3_close(db);
}
=
and the results:
[shalom@pato sqlite]$ ./porta tik
pragma returns  |(null)|
|insert into activity values (1,"play")| - should succeed : 0,|(null)|=>|not
an error|
|insert into activity values (3,"eat")| - should fail : 0,|(null)|=>|not an
error|
[shalom@pato sqlite]$ sqlite3 tik "select * from activity"
1|play
3|eat
=



Shalom
On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare wrote:

> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>
> > I appreciate the input. Some of the advice comes obviously from very good
> > and talented people who find a challenge at doing things WITHOUT
> reverting
> > to code writing.
>
> Doing as much (or most often, all) of the logic in SQL (instead of
> application code) removes a level of complexity, but is also generally
> faster and internally consistent.
>
> > I did a small program. Current show -stopper :
> >
> > what is the API equivalent of
> >
> > PRAGMA foreign_keys = ON;
>
> You can just send each of the SQL commands, including the pragma statement,
> in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes
> sense).
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Shalom Elkin]

All,

I have done exactly like BareFeetWare suggested. The referential integrity
"feature" is a no brainer for "serious" systems, where you do care about
your data consistency.
SQL doesn't have clauses where you declare where do you
want Erroneous records to go. This is usually mandated to tools like
sqlite3.

Or special purpose C++ hacks.

Thanks, guys.

Shalom

On Thu, Mar 24, 2011 at 1:48 AM, BareFeetWare wrote:

> On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:
>
> > I appreciate the input. Some of the advice comes obviously from very good
> > and talented people who find a challenge at doing things WITHOUT
> reverting
> > to code writing.
>
> Doing as much (or most often, all) of the logic in SQL (instead of
> application code) removes a level of complexity, but is also generally
> faster and internally consistent.
>
> > I did a small program. Current show -stopper :
> >
> > what is the API equivalent of
> >
> > PRAGMA foreign_keys = ON;
>
> You can just send each of the SQL commands, including the pragma statement,
> in sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes
> sense).
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
> --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[BareFeetWare]

On 24/03/2011, at 2:50 AM, Shalom Elkin wrote:

> I appreciate the input. Some of the advice comes obviously from very good
> and talented people who find a challenge at doing things WITHOUT reverting
> to code writing.

Doing as much (or most often, all) of the logic in SQL (instead of application 
code) removes a level of complexity, but is also generally faster and 
internally consistent.

> I did a small program. Current show -stopper :
> 
> what is the API equivalent of
> 
> PRAGMA foreign_keys = ON;

You can just send each of the SQL commands, including the pragma statement, in 
sqlite_exec (or you can get fancy with sqlite_prepare etc where it makes sense).

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
--
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] detect a flaw

[Igor Tandetnik]

On 3/23/2011 11:50 AM, Shalom Elkin wrote:
> what is the API equivalent of
>
> PRAGMA foreign_keys = ON;

It's a SQL statement like any other. You can run it with sqlite3_exec, 
for example
-- 
Igor Tandetnik

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

All,

I appreciate the input. Some of the advice comes obviously from very good
and talented people who find a challenge at doing things WITHOUT reverting
to code writing.

I did a small program. Current show -stopper :

what is the API equivalent of

PRAGMA foreign_keys = ON;

If I can not turn foreign_key support on, the insert with the erronous
id will just slip in.


Thanks,


Shalom



On Wed, Mar 23, 2011 at 5:24 PM, BareFeetWare wrote:

> On 23/03/2011, at 9:03 PM, Shalom Elkin wrote:
>
> > I am new to SQLITE. Here's the challenge:
> >
> > sqlite> .dump
> > BEGIN TRANSACTION;
> > CREATE TABLE people(
> >id integer,
> >nm text);
> > INSERT INTO "people" VALUES(1,'Jack');
> > INSERT INTO "people" VALUES(2,'Jill');
> > CREATE TABLE activity(
> >aid integer,
> >act  text,
> >foreign key (aid) references people(id)
> > );
> > COMMIT;
> > PRAGMA foreign_keys=On;
> > sqlite> .import ac.in activity
> > Error: foreign key mismatch
> >
> > This is ac.in
> >
> > 1|eat
> > 2|sleep
> > 3|run
> > 1|drink
> > 2|dream
> >
> > id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> > ac.inhad millions of rows? I am looking for some way to get a message
> > like "error
> > in line 3: foreign key mismatch".
> > preferably, the import would go on without actually inserting, but report
> > ALL errors in one pass.
> >
> > Any ideas?
>
> I usually import into a separate table, then use SQL to process the data
> into the final destination. This way, I can use any dumb import tool (such
> as the .import command line tool) and take care of the smarts (including
> constraints, error logging etc) in SQL.
>
> How about this:
>
> pragma foreign_keys = on;
> create temp table "activity import"
> (   aid integer
> ,   act text
> )
> ;
> create table "import error" (aid integer);
>
> .import ac.in "activity import"
>
> begin immediate;
> insert into "import error" select aid from "activity import" where aid not
> in (select id from "people");
> insert into "activity" select * from "activity import" where aid in (select
> id from "people");
> commit;
>
> or, you can add the required people on the fly:
>
> begin immediate;
> insert or ignore into "people" (id) select aid from "activity import" where
> aid not in (select id from "people");
> insert into "activity" select * from "activity import";
> commit;
>
> For this to work, you probably want to define the primary key in people:
>
> CREATE TABLE people
> (   id integer primary key not null
> ,   nm text
> )
> ;
>
> Tom
> BareFeetWare
>
> --
> iPhone/iPad/iPod and Mac software development, specialising in databases
> develo...@barefeetware.com
>  --
> Comparison of SQLite GUI tools:
> http://www.barefeetware.com/sqlite/compare/?ml
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[BareFeetWare]

On 23/03/2011, at 9:03 PM, Shalom Elkin wrote:

> I am new to SQLITE. Here's the challenge:
> 
> sqlite> .dump
> BEGIN TRANSACTION;
> CREATE TABLE people(
>id integer,
>nm text);
> INSERT INTO "people" VALUES(1,'Jack');
> INSERT INTO "people" VALUES(2,'Jill');
> CREATE TABLE activity(
>aid integer,
>act  text,
>foreign key (aid) references people(id)
> );
> COMMIT;
> PRAGMA foreign_keys=On;
> sqlite> .import ac.in activity
> Error: foreign key mismatch
> 
> This is ac.in
> 
> 1|eat
> 2|sleep
> 3|run
> 1|drink
> 2|dream
> 
> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> ac.inhad millions of rows? I am looking for some way to get a message
> like "error
> in line 3: foreign key mismatch".
> preferably, the import would go on without actually inserting, but report
> ALL errors in one pass.
> 
> Any ideas?

I usually import into a separate table, then use SQL to process the data into 
the final destination. This way, I can use any dumb import tool (such as the 
.import command line tool) and take care of the smarts (including constraints, 
error logging etc) in SQL.

How about this:

pragma foreign_keys = on;
create temp table "activity import"
(   aid integer
,   act text
)
;
create table "import error" (aid integer);

.import ac.in "activity import"

begin immediate;
insert into "import error" select aid from "activity import" where aid not in 
(select id from "people");
insert into "activity" select * from "activity import" where aid in (select id 
from "people");
commit;

or, you can add the required people on the fly:

begin immediate;
insert or ignore into "people" (id) select aid from "activity import" where aid 
not in (select id from "people");
insert into "activity" select * from "activity import";
commit;

For this to work, you probably want to define the primary key in people:

CREATE TABLE people
(   id integer primary key not null
,   nm text
)
;

Tom
BareFeetWare

--
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com
 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml

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

Re: [sqlite] detect a flaw

[Shalom Elkin]

Thanks, guys. i was afraid of the "Do it yourself". Im doing a small
specialized version, to be generalized later ...

Shalom

On Wed, Mar 23, 2011 at 3:41 PM, Igor Tandetnik  wrote:

> On 3/23/2011 8:42 AM, Simon Slavin wrote:
> > On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:
> >> Shalom Elkin  wrote:
> >>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> >>> ac.inhad millions of rows? I am looking for some way to get a message
> >>> like "error
> >>> in line 3: foreign key mismatch".
> >>
> >> Write a program that parses the file, runs INSERT statement on each
> file, and reports any failures. Do it all in a transaction; at the end, roll
> it back.
> >
> > Could Shalom just type BEGIN and COMMIT around the .import command ?
> > Or does the command-line tool execute the .import command internally
> > without reference to the context of individual commands ?
>
> He wants a line-by-line error report. .import won't do that.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Shalom Elkin
+972-544-704994
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] detect a flaw

[Igor Tandetnik]

On 3/23/2011 8:42 AM, Simon Slavin wrote:
> On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:
>> Shalom Elkin  wrote:
>>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
>>> ac.inhad millions of rows? I am looking for some way to get a message
>>> like "error
>>> in line 3: foreign key mismatch".
>>
>> Write a program that parses the file, runs INSERT statement on each file, 
>> and reports any failures. Do it all in a transaction; at the end, roll it 
>> back.
>
> Could Shalom just type BEGIN and COMMIT around the .import command ?
> Or does the command-line tool execute the .import command internally
> without reference to the context of individual commands ?

He wants a line-by-line error report. .import won't do that.
-- 
Igor Tandetnik

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

Re: [sqlite] detect a flaw

[Simon Slavin]

On 23 Mar 2011, at 11:27am, Igor Tandetnik wrote:

> Shalom Elkin  wrote:
>> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
>> ac.inhad millions of rows? I am looking for some way to get a message
>> like "error
>> in line 3: foreign key mismatch".
> 
> Write a program that parses the file, runs INSERT statement on each file, and 
> reports any failures. Do it all in a transaction; at the end, roll it back.

Could Shalom just type BEGIN and COMMIT around the .import command ?  Or does 
the command-line tool execute the .import command internally without reference 
to the context of individual commands ?

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

Re: [sqlite] detect a flaw

[Igor Tandetnik]

Shalom Elkin  wrote:
> id 3 doesn't exist, the insertion fails. Now, this was easy. what if
> ac.inhad millions of rows? I am looking for some way to get a message
> like "error
> in line 3: foreign key mismatch".

Write a program that parses the file, runs INSERT statement on each file, and 
reports any failures. Do it all in a transaction; at the end, roll it back.
-- 
Igor Tandetnik

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